Застосування возможностейExcelв реальному бізнесі
Мета роботи: навчитися працювати з базами даних, використовуючи для цього операції з отримання підсумкових даних, фільтрації даних, а також по об'єднанню даних.
4.1. Отримання підсумкових даних
Зведена таблиця - потужний інструмент отримання підсумкових даних, але часто вона надає значно більше інформації, ніж потрібно.
Функ-ції для роботи з базами даних дозволяють легко по-лучити відповіді на будь-які питання «звітності» без побудови зведеної таблиці.
Якщо до функціями СУМ, СРЗНАЧ, РАХУНОК, МАКС і МІН і до деяких інших додати префікси Д, БД і Б (які походять від терміна База Даних), отримаємо функції для роботи з базами даних.
Так, наприклад, в той час як функція СУММ підсумовує все осередки заданого діапазону, функція БДСУММ дозволяє задати (за допомогою критерію) подмн-дружність рядків діапазону комірок для складання.
Припустимо у нас є база дан-них продажів невеликої компанії, яка випускає декоративну косметику, ко-торая містить наступну інформацію про кожну транзакції продажу:
найменування проданої продукції;
кількість проданої продукції;
дохід від кожної транзакції;
регіон країни, де була продана продукція.
Ці дані знаходяться у файлі makeupdb.xls, фрагмент якого поки-зан на рис. 4-1.
Мал. 4-1 Дані, що використовуються для роботи з функціями баз даних
Використовуючи функцію БДСУММ (DSUM) з відповідним критерієм, ми можемо відібрати ті рядки, які ми хочемо включити в підсумкову суму. З цими рядками функція БДСУММ працює, як звичайна функція СУММ.
Синтаксис функції БДСУММ наступний:
БДСУММ (база_данних; поле; критерій).
База_данних - діапазон комірок, що становить базу даних. Перша будів-ка діапазону містить заголовки кожного стовпчика.
Поле - стовпець, значення якого має підсумувати функція. Можна вказати поле, уклавши заголовок стовпця в лапки. (Напри-заходів, ми могли б вказати стовпець Сума, ввівши «Сума»). Поле також може бути зазначено як номер стовпця в базі даних, порахований сле-ва направо. Наприклад, якщо в нашу базу даних входять стовпці з H до М, ми можемо вказати стовпець H як поле 1, а стовпець М як поле 6.
Критерій - вказує на діапазон комірок, де визначені строки, кото-які має опрацювати функція. Перший рядок діапазону критеріїв повинна містити один або кілька заголовків стовпців. (З цього правила є один виняток - який вираховується критерій, про який, буде розказано в прикладах 7 і 8)
Запам'ятайте. Ключ до створення діапазону критеріїв такий: кілька критеріїв, зазначених в одному рядку, об'єднуються за допомогою логічного І, тоді як критерії, зазначені в різних рядках об'єднуються за допомогою логічного АБО.
Розглянемо кілька прикладів, які демонстраційних-ють міць і гнучкість функцій баз даних.
ПРИКЛАД 1. На яку суму Джен продала блиску для губ?
Попередньо визначимо Базу Даних
виділимо діапазон H4: M4, потім одночасним натисканням клавіш Ctrl Shift виділимо всі дані;
визначимо заголовки полів БД (виконаємо Вставка → Ім'я → Створити → в рядку вище);
дамо ім'я нашої БД «дані» (Вставка → Ім'я → Присвоїти наберемо дані)
Застосуємо функцію БДСУММ до колонку 5 нашої бази дан-них. Стовпець 5 містить дохід від кожної транзакції.
Наш кри-терій, розташований в діапазоні О4: Р5, позначає всі рядки бази даних, для яких Ім'я одно Джен і Продукт - блиск для губ.
Таким чином, вве-дя в осередок N5 (рис. 4-2) формулу