Как улучшить ВПР()
Уважаемые сотрудники «Б & К»! Мой вопрос связан с поиском значений в таблицах Excel. Задача на первый взгляд простая. В программе
Excel есть очень удобная функция ВПР(). Она позволяет найти в таблице заданное значение и подставить его в формулу или скопировать на рабочий лист. Однако у нее есть существенное ограничение. Функция ВПР() находит только первое вхождение элемента в список и на этом завершает свою работу. А как можно отобрать все значения из списка? Можно ли в Excel 2003 решить такую задачу? Заранее благодарен.Валентин Пивоваров, г. Полтава
Отвечает
Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
В программе Excel есть разные инструменты для выбора информации из базы данных. Например, автоматический или расширенный фильтр. Однако судя по вопросу речь идет о выборе данных при помощи формул. Здесь ситуация такова. Для работы с базами данных в Excel есть несколько функций. Например, выборку данных из списка может сделать «
БИЗВЛЕЧЬ()». Функция «ВПР()» находит заданное значение в списке. Однако обе эти функции не позволяют выбрать все вхождения интересующего нас элемента. Решить такую задачу можно двумя способами — обратиться к языку Visual Basic или воспользоваться массивом формул. Я предлагаю остановиться на втором варианте. Он проще, не требует навыков программирования и потому легко реализуем на практике. Работу с массивом формул я покажу на примере таблицы, изображенной на рис. 1. В ней на листе «Тбл» собраны сведения о движении денежных средств в разрезе контрагентов (или физических лиц). В ней есть такие поля: «Дата» — дата проводки, «СчД», «СчК» — счета дебета и кредита проводки, «Д», «К» — суммы по кредиту и дебету проводки, «Контрагент/ФЛ» — название организации или Ф.И.О сотрудника. 
Наша задача. Нужно построить таблицу, которая будет работать так. В отдельную ячейку этой таблицы мы введем данные для поиска. Пусть это будет название контрагента. Затем мы должны создать формулы, которые проанализируют исходную базу, выберут из нее все данные по указанному контрагенту и перенесут их в отдельную область листа. Например, мы хотим для контрагента увидеть отчет о движении денежный средств — дату, сумму по дебету и кредиту проводки.
Создаем массив из формул
На первом этапе (чтобы излишне не усложнять формулы) мы будет строить таблицу на одном листе с базой данных. Делаем так:
1) перед первой записью базы данных добавляем рабочую строку рис. 2.
2) в ячейку «
H1» вводим заголовок «Выборка». В ячейку «I1» нашей таблицы мы введем данные для отбора значений;3) в ячейки «
H2», «I2», «J2» вводим заголовки: «Дата», «Д», «К». Ниже этих заголовков будут расположены результаты отбора значений из базы данных;4) в ячейку «
I1» пишем название контрагента, для которого нужно сделать выборку информации из базы данных. На рис. 2 — это «Петров В.И.»;5) в ячейку «
H3» вводим формулу: «=ИНДЕКС($A$3:$A$65536;НАИМЕНЬШИЙ( ЕСЛИ($I$1=F3:F65536;СТРОКА(A3:A65536)-2;””);СТРОКА()-2))», нажимаем «Enter»;6) выделяем фрагмент рабочего листа, куда нужно записать отобранные значения. Например, блок «
H3:H65536» (с третьей строки и до конца рабочего листа);7) не снимая выделения (!), нажимаем «
F2», входим в режим редактирования ячейки «H3»;8) нажимаем комбинацию «
Ctrl+Shift+ Enter», заполняем блок массивом формул;9) выделяем колонку «
H», назначаем ей формат даты;10) в ячейку «
I3» вводим формулу: «=ИНДЕКС($D$3:$D$65536;НАИМЕНЬШИЙ( ЕСЛИ($I$1=F3:F65536;СТРОКА(D3:D65536)-2;””);СТРОКА()-2))», нажимаем «Enter»;11) выделяем фрагмент «
I3:I65536»;12) не снимая выделения, нажимаем клавишу «
F2», затем «Ctrl+Shift+Enter»;13) в ячейку «
J3» вводим формулу: «=ИНДЕКС($E$3:$E$65536;НАИМЕНЬШИЙ( ЕСЛИ($I$1=F3:F65536;СТРОКА(E3:E65536)-2;””);СТРОКА()-2))», нажимаем «Enter»;14) выделяем фрагмент «
J3:J65536», нажимаем «F2», затем комбинацию «Ctrl+Shift+Enter». Результат показан на рис. 2;В колонке «
H» (ячейки «H3:H8») появились даты, где есть операции с контрагентом «Петров В.И.». В колонках «I» и «J» видны суммы по дебету и кредиту проводок. Всего в блоке с результатами заполнено шесть строк. Начиная с ячейки «H9» и до конца таблицы идут значения «#ЧИСЛО». Это означает, что других сведений по элементу «Петров В.И.» в базе данных нет.
Улучшаем результат
В целом формула работает правильно. Но у нее есть один недостаток — для пустых ячеек она возвращает значение «
#ЧИСЛО». Это легко исправить при помощи функций «ЕСЛИ()» и «ЕОШ()». И в окончательном варианте формулы будут выглядеть, как показано в таблице.
| Адрес | Формула |
| H3 | =ЕСЛИ(ЕОШ(ИНДЕКС($A$3:$A$65536;НАИМЕНЬШИЙ(ЕСЛИ($I$1=F3:F65536;СТРОКА(A3:A65536)-2;””);СТРОКА()-2)));””;ИНДЕКС($A$3:$A$65536;НАИМЕНЬШИЙ(ЕСЛИ($I$1=F3:F65536; СТРОКА(A3:A65536)-2;””);СТРОКА()-2))) |
| I3 | =ЕСЛИ(ЕОШ(ИНДЕКС($D$3:$D$65536;НАИМЕНЬШИЙ(ЕСЛИ($I$1=F3:F65536;СТРОКА(D3:D65536)-2;””);СТРОКА()-2)));””;ИНДЕКС($D$3:$D$65536;НАИМЕНЬШИЙ(ЕСЛИ($I$1=F3:F65536; СТРОКА(D3:D65536)-2;””);СТРОКА()-2))) |
| J3 | =ЕСЛИ(ЕОШ(ИНДЕКС($E$3:$E$65536;НАИМЕНЬШИЙ(ЕСЛИ($I$1=F3:F65536;СТРОКА(E3:E65536)-2;””);СТРОКА()-2)));””;ИНДЕКС($E$3:$E$65536;НАИМЕНЬШИЙ(ЕСЛИ($I$1=F3:F65536; СТРОКА(E3:E65536)-2;””);СТРОКА()-2))) |
Формулы для ячеек «
H3», «I3», «J3» практически одинаковые. Отличия между ними выделены полужирным начертанием. Чтобы получить окончательный вариант таблицы, делаем так:1) в ячейки «
H3», «I3», «J3» вводим формулы из таблицы;2) становимся на ячейку «
H3», выделяем фрагмент «H3:H65536»;3) не снимая выделения, нажимаем «
F2», входим в режим редактирования ячейки «H3»;4) нажимаем комбинацию «
Ctrl+Shift+ Enter»;5) повторяем эти действия для блоков «
I3:I65536», «J3:J65536». Результат не изменится, но вместо значений «#ЧИСЛО» в ячейках будут пустые строки.Задачу мы решили. Выбор значений из базы данных работает. Но есть один момент, который обязательно нужно учитывать при работе с массивами формул.
Важно! Из области листа с массивом формул нельзя удалить отдельное значение. Все, что можно сделать, — стереть все элементы массива целиком.
В примере на рис. 2 массив с формулами расположен на одном листе справа от основной базы данных (колонки «H», «I», «J»). Этот выбор был сделан не случайно — так проще подставлять адреса при создании формул. Однако работать с таким вариантом таблицы неудобно. Например, мы не сможем добавить или удалить строку в основную базу данных. Ведь при этом мы будем добавлять строки и в область массива с формулами! А этого делать нельзя.
Совет Работая с массивами формул, старайтесь располагать их на отдельном листе рабочей книги.
Следуя этому совету, делаем так:
1) на листе «Тбл» (рис. 2) выделяем колонки «H», «I», «J»;
2) вырезаем данные в буфер обмена (меню «Правка → Вырезать», можно воспользоваться иконкой «Вырезать» на панели инструментов или контекстным меню);
3) переходим на новый рабочий лист, я назвал его «Отчет»;
4) вставляем данные из буфера обмена. Excel автоматически откорректирует адреса в формулах, проставит ссылки на рабочий лист с базой данных, а отчет примет вид, как на рис. 3.
Все готово к работе. В ячейку «B1» вводим параметр отбора значений (например, текст «Петров В.И.»). На листе появится отчет о движении денежных средств по указанному контрагенту.
Вкратце о формуле
Я поясню логику работы формул на примере выражения из ячейки «
H3» (рис. 2). В нашем примере база данных начинается с третьей строки рабочего листа. То есть сами данные, не считая строки заголовков, находятся на две строки ниже начала рабочего листа. Для определения номеров записей в базе мы применили выражение «=СТРОКА(A3:A65536)-2». Здесь значение «-2» указывает смещение базы данных на две строки от начала листа. Выражение «ЕСЛИ($J$1=F3:F65536;СТРОКА(A3:A65536)-2;””)» сравнивает искомое значение (ячейка «$J$1» ) с данными в блоке «F3:F65536». При совпадении значений результатом будет номер записи в базе данных (т. е. «СТРОКА(A3:A65536)-2»). Если результат сравнения ошибочный, выражение вернет пустую строку («””»). Таким образом, для строк, которые попадают под условие поиска, формула определит их порядковые номера в базе данных. Например, для записи «Петров В.И.» от «01/03/11» формула вернет номер «2» — соответствующая запись находится в базе на второй позиции. Чтобы собрать все найденные значения в один блок, мы применили функцию «НАИМЕНЬШИЙ()». У нее два параметра — массив и номер наименьшего элемента в этом массиве. Номерами наименьших элементов в формуле являются значения «СТРОКА(A3:A65536)-2». Напомню, что значения этого выражения — это номера записей в базе данных начиная с «1». Тогда выражение «НАИМЕНЬШИЙ(ЕСЛИ($J$1=F3:F65536;СТРОКА (A3:A65536)-2;””);СТРОКА()-2)» вернет в качестве результата первый наименьший элемент (значение «2»). При копировании формулы вниз на одну строку выражение вернет в качестве результата второй наименьший элемент (значение «5») и т. д. Таким образом, в результате работы функции «НАИМЕНЬШИЙ(…)» нам будет известна строка и колонка в базе данных, где находятся все искомые элементы. Подставить конкретные значения, расположенные на пересечении строки и колонки, позволяет функция «ИНДЕКС()».
Как применить ее к своей задаче
Чтобы воспользоваться формулой, необязательно понимать логику ее работы во всех деталях. Достаточно знать, что нужно в ней откорректировать и для чего. Сам текст формулы вы можете набрать самостоятельно или скачать готовый пример на сайте редакции. А дальше делаем так:
1) копируем текст формулы в ячейку таблицы;
2) находим смещение первой строки базы данных от начала рабочего листа, заносим соответствующее значение в формулу. В примере на рис. 2 это значение равно «
-2»;3) выбираем блок данных, в пределах которого Excel будет делать поиск значений. В примере на рис. 3 мы отбирали данные по колонке «
Контрагент/ФЛ». Соответственно в нашем случае блок данных для поиска был «F3:F65536». Вместо этого выражения поставьте свое значение;4) определите блок данных, из которого нужно вернуть результат. Например, формула для ячейки «
H3» возвращает дату операции. Даты в основной базе находятся в колонке «A». Соответственно в формуле для «H3» проставлены ссылки на блок «$A$3:$A$65536». В ячейке «I3» формула должна вернуть сумму по дебету проводки. Эти значения записаны в колонке «D», а соответствующая ссылка будет «$D$3:$D$65536». Откорректируйте в формулах ссылки исходя из своей задачи. Все, что остается сделать, — создать массив формул и пользоваться результатом.
Удачной работы! Жду ваших вопросов, замечаний и предложений на
bk@id.factor.ua или nictomkar@rambler.ru .
