Функції баз даних Excel, наприклад, БДСУММ (DSUM), БСЧЁТ (DCOUNT) та інші можуть працювати замість, можливо, тисяч функцій, скорочуючи, таким чином, і час перерахунку, і обсяг робочої книги. При роботі з функціями баз даних Excel можна вказати до 256 різних критеріїв.
Щоб побачити, як це працює, задайте дані. Вкажіть ті ж заголовки стовпців, але в самих шпальтах можуть бути будь-які фіктивні дані. Дайте цій таблиці даних, включаючи всі заголовки стовпців, ім'я AllData. Назвіть лист Data. Додайте ще один робочий лист і назвіть його Results. В осередку А2 введіть наступну формулу: = Data! A1.
Скопіюйте її до комірки F2, щоб отримати дзеркальне відображення заголовків вашої таблиці. В осередку A3 введіть будь-яке ім'я, яке у таблиці на аркуші з даними, наприклад, John D. Потім в осередку ВЗ введіть формулу = DGET (AllData; В2; $ А $ 2: $ А $ 3), в російській версії Excel = БІЗВЛЕЧЬ (AllData ; В2; $ А $ 2: $ А $ 3). Скопіюйте цю формулу до комірки F3 і відформатуйте осередки C3: F3 потрібним чином.
Щоб швидко скопіювати осередки, подібні цим, без форматування, виділіть осередок, правою кнопкою миші маркер заповнення і, утримуючи праву кнопку миші, перетягніть маркер на потрібну позицію. Потім в контекстному меню виберіть команду Заповнити значення (Fill Without Formatting).
Відповідні дані для імені, введеного в осередку A3, повинні бути вилучені з таблиці з даними. Це простий приклад того, як можна отримувати пов'язану інформацію за допомогою функції БІЗВЛЕЧЬ (DGET).
Якщо ви отримаєте помилку # числі! (#NUM!), Це означає, що в стовпці Name є два або більше однакових імені.
Зараз більшість користувачів пішли б тієї ж концепції для всіх імен, для яких необхідно отримати інформацію з таблиці. Однак ці зусилля не потрібні.
Так як ви завжди посилаєтеся на осередок A3, отримуючи звідти ім'я, було б набагато зручніше, щоб в цьому осередку знаходився список, що містить всі імена з таблиці даних. Для створення такого списку можна скористатися стандартною можливістю перевірки Excel. Однак, так як вихідний список імен знаходиться на іншому робочому аркуші, не можна послатися на нього як на список на тому ж аркуші, тобто вказати стандартну посилання на діапазон. Це обмеження можна легко обійти, присвоївши ім'я колонки Name вихідної таблиці, а потім вказавши це ім'я як джерело для списку перевірки.
Знову клацніть лист Data і, виділивши будь-який осередок, виберіть команду Формули → Ім'я → Присвоїти (Formulas → Name → Define name). В поле Ім'я (Names in workbook) введіть Names. В поле Формула (Refers to) введіть наступну формулу і клацніть на кнопці Додати (Add): = OFFSET ($ A $ 2; 0; 0; COUNTA ($ A $ 2: $ A $ 1000); 1), в російській версії Excel = зміщений ($ А $ 2; 0; 0; СЧЁТЗ ($ А $ 2: $ А $ 1000); 1). Клацніть робочий лист Results, виділіть осередок A3 і виберіть команду Дані → Перевірка (Data → Validation). В поле Тип даних (Allow) виберіть Список (List), а в полі Джерело (Source) введіть формулу = Name. Переконайтеся, що увімкнено Архів допустимих значень (In-Cell), і клацніть на кнопці ОК. Тепер в списку в осередку A3 можна вибрати будь-яке ім'я, і справа будуть автоматично виведені відповідні йому дані.
Можна просунутися ще далі і використовувати функцію БСЧЁТ (DCOUNT), щоб дізнатися кількість осіб, для яких значення Full Cost більше одного зазначеного числа, а значення Percent Paid менше іншого. Для цього спочатку необхідно створити динамічний іменований діапазон для стовпців Full Cost і Percent Paid. В поле Ім'я (Names in workbook) введіть FullCost. В поле Формула (Refers to) введіть наступну формулу і клацніть на кнопці Додати (Add): = OFFSET ($ З $ 2; 0; 0; COUNTA ($ З $ 2: $ З $ 1000); 1), в російській версії Excel = зміщений ($ З $ 2; 0; 0; СЧЁТЗ ($ З $ 2: $ З $ 1000); 1). Після цього в поле Ім'я (Names in workbook) введіть PercentPaid. В поле Формула (Refers to) введіть наступну формулу і клацніть на кнопці Додати (Add): = OFFSET ($ E $ 2; 0; 0; COUNTA ($ E $ 2: $ E $ 1000); 1), в російській версії Excel = зміщений ($ Е $ 2; 0; 0; СЧЁТЗ ($ Е $ 2: $ Е $ 1000); 1).
Перейдіть на лист Results, виділіть осередок АІ і виберіть команду Дані → Перевірка (Data → Validation). В поле Тип даних (Allow) виберіть Список (List), а в полі Джерело (Source) введіть = Full_Cost. Клацніть на кнопці ОК. Виділіть клітинку АІ і виберіть команду Дані → Перевірка (Data → Validation). В поле Тип даних (Allow) виберіть Список (List), а в полі Джерело (Source) введіть = Percent_Paid. Клацніть на кнопці ОК.
В осередку А12 введіть формулу = Data! C1. Виділіть клітинку В12 і введіть наступну формулу: = Data! E1. Виділіть клітинку А13 і введіть наступну формулу: = »>» # 038; А11. Виділіть клітинку В13 і введіть наступну формулу: = »<«&ТЕХТ(В11;»0%»), в русской версии Excel =»<«&ТЕКСТ(В11;»0%»). В ячейке А15 введите следующее: =DCOUNT(AllData;$A$12;$A$12:$B$13), в русской версии Excel =БCЧET(AllData;$A$12;$A$12:$B$13).
Виберіть будь-яке значення Full Cost в осередку АІ і будь-яке значення Percent Paid в осередку В11, і функція БСЧЁТ (DCOUNT) повідомить кількість людей, що відповідають цим критеріям. Наприклад, якщо ви виберете 65 і 100%, то дізнаєтеся кількість людей, для яких значення Full Cost більше 65, а значення Percent Paid менше 100.
Як видно, за допомогою функції БСЧЁТ (DCOUNT) можна витягувати будь-яку комбінацію критеріїв для стовпців Full Cost і Percent Paid. Приклавши ще трохи зусиль, можна перевести це на черговий рівень і зробити оператори, які використовуються в критерії, взаємозамінними.
Спочатку необхідно створити список операторів порівняння, які можна використовувати в списку перевірки. Перейдіть до будь-якого невикористовуваних колонки на аркуші Results і в будь-якому рядку цього стовпця введіть заголовок Operators. Під заголовком введіть оператори =,> =,>, Бази даних • Формули • Функції