Обчислення узагальнюючих показників за довільним критерієм

Звичайно, далеко не завжди можна знайти об'єкти за ключовими значеннями. Зокрема, це не вдасться зробити, якщо вам знадобиться, скажімо, дізнатися, який учень отримав максимальний бал з другої тематичної роботи (продовжимо розглядати приклад з класним журналом). Для виконання подібних операцій у Microsoft Excel використовують окрему категорію функцій – функції для роботи з базою даних.

Функція DGET: простий критерій

Найпростіша із зазначених функцій, DGET (рос. БИЗВЛЕЧЬ), подібна до функції VLOOKUP, але дозволяє здійснювати пошук об'єктів за складним критерієм, а не лише за значенням у першому стовпці таблиці. Наведемо її формат:

DGЕТ(бaзa_ даних;поле;критерій)

Призначення аргументів функції таке:

Зазначимо, що функції для роботи з базою даних розпізнають стовпці за їхніми назвами, які мають міститися у клітинках першого рядка таблиці та критерію, а не за номерами.

Існують певні правила заповнення діапазону критерію. У його першому рядку записують назви стовпців, на значення яких накладають умови, у наступних рядках – самі умови. У найпростішому випадку діапазон критерію складається з двох клітинок, розташованих одна під одною: у верхній записують назву стовпця, а в нижній – умову, якій мають відповідати його значення.

Наведемо приклад, звернувшись до тієї самої таблиці з відомостями про працівників, яку ми розглядали в попередньому підрозділі. Припустимо, потрібно визначити прізвище за введеною користувачем посадою. У цьому разі діапазоном критерію будуть клітинки А8:А9 (рис. 5.4).

Рис. 5.4. Визначення прізвища за посадою

Тут функцію для визначення прізвища працівника введено в клітинку D8:

=DGЕТ(А1:D6;А1;А8:А9)

Опишемо аргументи цієї функції: А1:D6 – діапазон всієї таблиці; А1 – клітинка з назвою стовпця, з якого вибираються дані; А8:А9 – діапазон критерію.

Тепер, коли ми у клітинку А9 вводитимемо назву посади, у клітинці D8 автоматично визначатиметься прізвище працівника, який цю посаду обіймає. Проте якщо ввести посаду продавець, то у клітинці D8 програма виведе повідомлення про помилку #NUМ!. Річ у тім, що продавців двоє, і тому функція DGET не може визначити, прізвище якого саме продавця відображати. Таким чином, для коректної роботи функції DGET необхідно, щоб критерію пошуку задовольняв лише один рядок таблиці.

Функція DGEТ: складений критерій

У критерії пошуку за допомогою логічних сполучників «і» та «або» можуть з'єднуватися умови, накладені на значення різних параметрів. Умови зі сполучником «і» записуються в одному рядку критерію, а з'єднані сполучником «або» – у різних. Наприклад, ми можемо шукати прізвища тих працівників, які обіймають зазначену користувачем посаду і оклади яких перевищують 3400 грн. У такому разі критерій запишеться так, як показано на рис. 5.5, а. Якщо ж ми шукатимемо прізвища працівників, які обіймають певну посаду або оклади яких перевищують 3400 грн, то критерій буде таким, як показано на рис. 5.5, б.

Рис. 5.5. Складені критерії пошуку за умовами, з'єднаними: а – сполучником «і»; б – сполучником «або»

Якщо розглядати дані, наведені на рис. 5.4, то за першим критерієм буде знайдено прізвище Лялецький, а за другим знову відобразиться повідомлення про помилку #NUM!, оскільки цьому критерію задовольняють усі працівники.

Можна створювати критерії, що відповідають умовам з кількома сполучниками «і» та «або». Наприклад, нарис. 5.6, а зображено діапазон критерію для вибору тих працівників, які є продавцями і відповідають принаймні одній із умов: мають оклад понад 3400 грн або народилися після 1 січня 1980 року, а на рис. 5.6, б – тих, які є продавцями або задовольняють двом умовам: мають оклад понад 3400 грн і народилися після 1 січня 1980 року.

Рис. 5.6. Складені критерії пошуку: а – за умовою типу а і (b або с); б – за умовою типу а або (b і с)

Інші функції для роботи з базою даних

Функція DGET знаходить значення деякого параметра об'єкта, який задовольняє певному критерію. Проте якщо таких об'єктів кілька, ця функція поверне помилку. Але є й інші функції з категорії Робота з базою даних, які дають змогу виконувати зі значеннями того самого параметра кількох об'єктів різноманітні узагальнюючі операції: підсумовувати їх, рахувати кількість, обчислювати середнє арифметичне тощо.

Найуживаніші з них такі:

Ці функції мають такі самі параметри, як і функція DGET. Під час їх обчислення вказані операції виконуються тільки для об'єктів, що відповідають критерію. Правила формування критерію аналогічні правилам для функції DGET. Приклади застосування перелічених функцій наведено на рис. 5.7.

Рис. 5.7. Застосування функцій для роботи з базами даних

Згідно з критерієм, записаним у клітинках А10:В11, відбиралися працівники, які народилися після 1 січня 1974 року та до 1 січня 1985 року. їх троє: Мотиль, Кузнєцов і Лялецький (кількість працівників обчислено у клітинці В14 за допомогою функції DCOUNT). Оклади цих працівників становлять 5500 грн, 6200 грн та 3500 грн. Саме за вказаними сумами обчислювалися середнє, максимальне, мінімальне та сумарне значення – вони відображені у клітинках В15:В18. Функції, які при цьому використовувалися, зазначені у клітинках С14:С18.