Трюк №80. Трюк с одной функцией баз данных Excel, чтобы она работала вместо нескольких функций
Функции баз данных Excel, например, БДСУММ (DSUM), БСЧЁТ (DCOUNT) и другие могут работать вместо, возможно, тысяч функций, сокращая, таким образом, и время пересчета, и объем рабочей книги. При работе с функциями баз данных Excel можно указать до 256 различных критериев.
Может понадобиться, например, суммировать значения в столбце А, только если соответствующие значения в столбце В больше 100, а соответствующий возраст в столбце С меньше 40. Если, однако, вы хотите суммировать те значения, для которых соответствующие значения в столбце В меньше 50, понадобится использовать другую функцию и другой диапазон критерия. Было бы намного проще, если бы была только одна функция, для которой можно быстро и просто изменить критерий! Если вы раньше никогда не работали с функциями баз данных Excel, настоятельно рекомендуем познакомиться с ними, так как они прекрасно подходят для выделения статистической информации из базы данных или таблицы Excel.
Чтобы увидеть, как это работает, задайте данные. Укажите те же заголовки столбцов, но в самих столбцах могут быть любые фиктивные данные. Присвойте этой таблице данных, включая все заголовки столбцов, имя AllData. Назовите лист Data. Добавьте еще один рабочий лист и назовите его Results. В ячейке А2 введите следующую формулу: =Data!A1.
Скопируйте ее до ячейки F2, чтобы получить зеркальное отображение заголовков вашей таблицы. В ячейке A3 введите любое имя, присутствующее в таблице на листе с данными, например, John D. Затем в ячейке ВЗ введите формулу =DGET(AllData;В2;$А$2:$А$3), в русской версии Excel =БИЗВЛЕЧЬ(AllData;В2;$А$2:$А$3). Скопируйте эту формулу до ячейки F3 и отформатируйте ячейки C3:F3 нужным образом.
[stextbox id=»warning»]Чтобы быстро скопировать ячейки, подобные этим, без форматирования, выделите ячейку, правой кнопкой мыши щелкните маркер заполнения и, удерживая правую кнопку мыши, перетащите маркер на нужную позицию. Затем в контекстном меню выберите команду Заполнить значения (Fill Without Formatting).[/stextbox]
Соответствующие данные для имени, введенного в ячейке A3, должны быть извлечены из таблицы с данными. Это простой пример того, как можно извлекать связанную информацию при помощи функции БИЗВЛЕЧЬ (DGET).
[stextbox id=»alert»]Если вы получите ошибку #ЧИСЛ! (#NUM!), это означает, что в столбце Name есть два или более одинаковых имени.[/stextbox]
Сейчас большинство пользователей последовали бы той же концепции для всех имен, для которых необходимо получить информацию из таблицы. Однако эти усилия не нужны.
Так как вы всегда ссылаетесь на ячейку A3, получая оттуда имя, было бы намного удобнее, чтобы в этой ячейке находился раскрывающийся список, содержащий все имена из таблицы данных. Для создания такого списка можно воспользоваться стандартной возможностью проверки Excel. Однако, так как исходный список имен находится на другом рабочем листе, нельзя сослаться на него как на список на том же листе, то есть указать стандартную ссылку на диапазон. Это ограничение можно легко обойти, присвоив имя столбцу Name исходной таблицы, а затем указав это имя как источник для списка проверки.
[stextbox id=»warning»]Так как большинство таблиц не являются статическими, другими словами, данные обычно постоянно добавляются и удаляются, лучше для столбца Names использовать динамический именованный диапазон. Подробнее об этом — в разделе «Трюк № 42. Создание увеличивающихся и уменьшающихся диапазонов».[/stextbox]
Снова щелкните лист 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 и введите следующую формулу: =»>»&А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. Под заголовком введите операторы =, >=, >, < и <=. Чтобы присвоить диапазону имя, выделите заголовок и все операторы ниже его и выберите команду Формулы → Присвоить имя (Formulas → Define Name). Удостоверьтесь, что выбран переключатель В строке выше (Top Row Only), и щелкните на кнопке ОК. Excel автоматически присвоит этому диапазону имя, основываясь на его заголовке, — в данном случае, Operators. Выделите ячейку G7 и введите заголовок Select a Criteria. Выделив ячейки G7 и Н7, выровняйте их по центру, выбрав команду Главная → Ячейки → Выравнивание (Home → Format Cells → Alignment) и в поле По горизонтали (Horizontal Text Alignment) выбрав вариант По центру выделения (Center Across Selection). Выделите ячейки G8 и Н8, выберите команду Данные → Проверка (Data → Validation) и в списке Тип данных (Allow) выберите Список (List). В поле Источник (Source) введите =0perators. Удостоверьтесь, что установлен флажок Список допустимых значений (In-Cell), и щелкните на кнопке ОК. Перейдите обратно на лист Data и создайте динамический именованный диапазон для столбца Dates. Выберите команду Формулы → Присвоить имя (Formulas → Define Name) и в поле Имя (Names in workbook) введите Dates. В поле Формула (Refers to) введите следующую формулу и щелкните на кнопке Добавить (Add): =OFFSET($B$2;0;0;COUNTA($B$2:$B$1000);1), в русской версии Excel =СМЕЩ($В$2;0;0;СЧЁТЗ($В$2:$В$1000);1). Выделите ячейку G7, скопируйте ее и вставьте в ячейку G9. Измените слово Criteria на слово Date. Выделите ячейки G10:H10 и выберите команду Данные → Проверка (Data → Validation), а затем в поле Тип данных (Allow) введите Список (List). В поле Источник (Source) введите =Dates. Удостоверьтесь, что установлен флажок Список допустимых значений (In-Cell), и щелкните на кнопке ОК. Выделите ячейку G11 и введите следующую формулу: =Data!$B$1. Скопируйте ее в ячейку НИ. Выделите ячейку G12, введите следующую формулу (используйте местный формат даты для вашего региона) и скопируйте ее в ячейку Н12: =G8&TEXT(G10;"dd/mm/yy"), в русской версии Excel =G8&ТЕКСТ(G10;"ДД.ММ.ГГ"). В ячейке F13 введите слово Result и, выделив ячейки F13 и G13, выровняйте их по центру выделения. В ячейке Н13 введите следующую функцию: =DSUM(AllData;Data!$С$1;$G$11:$Н$12), в русской версии Excel =БДСУММ(AllData;Data!$С$1;$G$11:$Н$12). Скройте строки И и 12, так как их видеть не нужно. Вы получите простую в использовании таблицу — к этой таблице мы применили форматирование для упрощения восприятия. Применяя этот принцип, можно легко заставить одну или несколько функций баз данных выполнять работу, для которой обычно требуются сотни функций. Рабочий пример этого упражнения, а также схожие примеры можно загрузить с нашей страницы загрузок. Открыв страницу, загрузите файл DFunctionsWithValidation.zip.