Двумерный поиск по таблице Excel
Скажу с самого начала: готовой встроенной функции для решения этой задачи в Excel нет. Зато есть другие инструменты, которые позволят получить формулу, чтобы успешно справиться с проблемой. При этом мы будет использовать исключительно стандартные средства. Работать они будут в любой версии MS Excel и не только — слегка изменив названия функций, предложенный способ можно с успехом адаптировать, например, для программы Calc пакетов LibreOffice, OpenOffice и т. п. Приступим.
Исходные данные
В качестве примера я воспользовался таблицей, фрагмент которой показан на рис. 1. В этой таблице в заголовках колонок перечислены наименования контрагентов. В области заголовков строк расположены названия товаров (ТМЦ). Внутри таблицы на пересечении строки и колонки записаны объемы реализации каждого товара по конкретному контрагенту.
Наша задача будет такой. В верхней части таблицы мы создадим рабочую область, а в этой области — два параметра для поиска по таблице: наименование организации и название товара. После чего в отдельной ячейке мы должны написать формулу, которая по таблице на рис. 1 найдет объем реализации по указанному товару для конкретного контрагента. Но вначале выполним несколько предварительных действий, чтобы привести таблицу к подходящей форме. Делаем так:
1. Удерживая левую кнопку мышки, выделяем первые три строки документа.
2. На выделенном блоке щелкаем правой кнопкой мышки, из контекстного меню выбираем «Вставить». В верхней части таблицы Excel добавит три строки.
3. В ячейки «A1», «A2» пишем заголовки «ТМЦ:» и «Контрагент:». Выбор этих значений мы организуем из списка.
4. Становимся на ячейку «B1».
5. Вызываем меню «Данные».
6. В группе «Работа с данными» щелкаем на значке раскрывающегося списка «Проверка данных».
7. Из предложенных вариантов выбираем пункт «Проверка данных…» (рис. 2). Откроется окно «Проверка вводимых значений», как на рис. 3.
8. В этом окне щелкаем на списке «Тип данных:». Откроется перечень возможных значений.
9. Из него выбираем вариант «Список».
10. Щелкаем мышкой в области параметра «Источник:».
11. Удерживая левую кнопку мышки, обводим на рабочем листе блок «=$A$6:$A$65536» (от ячейки «A6» до конца на всю высоту таблицы). В этой части документа у нас расположены наименования товаров. Блок мы выделили с запасом, чтобы таблицу можно было дополнять данными вниз.
12. Включаем флажок «Игнорировать пустые ячейки». В результате список будет сформирован только по тем ячейкам из блока «=$A$6:$A$65536», которые реально содержат данные.
13. В окне «Проверка вводимых значений» нажимаем «ОК». В ячейке «B1» появится значок выпадающего списка. Щелчок левой кнопкой на этом значке откроет список товаров, которые перечислены в первой колонке исходной таблицы.
14. Становимся на ячейку «B2».
15. Через меню «Данные» открываем окно «Проверка вводимых значений» (рис. 3).
16. Параметр «Тип данных:» снова ставим в положение «Список», в области «Источник:» указываем блок «=$B$5:$IV$5».
17. Включаем флажок «Игнорировать пустые ячейки».
18. В окне «Проверка вводимых значений» нажимаем «ОК». В ячейке «B2» появится значок выпадающего списка. Щелчок на этом значке откроет список контрагентов, которые перечислены в блоке «=$B$5:$IV$5».
19. В ячейку «A3» пишем заголовок «Сумма:». Фрагмент базы с результатами нашей работы показан на рис. 4. Таблица готова для дальнейшей работы.
Что мы получили на самом деле? В верхней части таблицы (ячейки «B1» и «B2») расположены два выпадающих списка. С их помощью мы сможем выбрать конкретный товар и контрагента, которые есть в исходной базе. Теперь наша задача — найти в таблице соответствующее значение о сумме реализации и подставить его в ячейку «B3».
Формула для поиска в двумерном списке
По сути в «B3» мы должны ввести формулу, которая подставит значение из ячейки, расположенной на пересечении определенной строки и столбца в исходной таблице. Создание формулы я предлагаю разделить на три этапа. Как говорится, будем «есть слона по частям»… Для начала мы определим номер строки, который соответствует наименованию товара из ячейки «B1» (его мы выберем из раскрывающегося списка). Справиться с такой проблемой нам поможет функция «ПОИСКПОЗ()», которая выглядит так: «ПОИСКПОЗ(Знач;Массив;ТипСр)». У этой функции три параметра: «Знач» — искомое значение, «Массив» — область для поиска (блок ячеек на рабочем листе), «ТипСр» — тип сопоставления (число или логическое выражение). Функция просматривает список «Массив», находит в нем элемент «Знач» и возвращает номер позиции этого элемента в массиве. Параметр «ТипСр» может принимать три значения: «0» — функция находит точное совпадение, «1» — функция ищет позицию наибольшего элемента, который не превышает «Знач», «-1» — функция ищет позицию наименьшего элемента, большего чем «Знач».
Важно! Если параметр «ТипСр» равен «1» или «-1», список для поиска должен быть отсортирован.
Для наших целей подходит вариант, когда тип сопоставления равен «0». Тогда работа функции будет выглядеть так. Если в ячейку «B1» ввести наименование «Бум."XEROX" TCF A4», то выражение «=ПОИСКПОЗ(B1;A6:A65536;0)» вернет значение «2». Это означает, что товар «Бум."XEROX" TCF A4» находится на второй позиции в области ячеек «A6:A65536»».
Аналогичным способом мы можем определить порядковый номер колонки, которая соответствует наименованию контрагента из ячейки «B2». Формула в этом случае будет выглядеть так: «=ПОИСКПОЗ(B2;B5:IV5;0)». Допустим, что в ячейку «B2» мы ввели название «ЧП "Plaza"». Тогда результат работы формулы будет равен «3», т. е. указанный контрагент находится в третьей колонке таблицы на рис. 4.
С номерами мы разобрались. Теперь нужно получить содержимое ячейки, которая расположена на пересечении соответствующей строки и колонки. Сделать это нам поможет функция «ИНДЕКС(БлокЗн;НомСтр;НомКол)». Первый аргумент функции — диапазон ячеек, где расположены данные. В нашем случае это будет фрагмент таблицы «/font>B6:IV65536». Второй и третий параметры функции — это номера строки и колонки в указанном диапазоне. Их мы уже умеем определять при помощи функции «ПОИСКПОЗ()». Все, что нам осталось сделать, — это объединить все компоненты формулы в одно целое. Формула получится длинной, потому я опишу процесс ее создания подробно. Делаем так:
1. Становимся на ячейку «B3»».
2. Щелкаем левой кнопкой на значке «fx» (он расположен в области строки формул). Откроется окно «Вставка функции», как на рис. 5.
3. В этом окне щелкаем на списке «Категория:», выбираем вариант «Ссылки и массивы».
4. В перечне «Выберите функцию:» щелкаем левой кнопкой на элементе с названием «ИНДЕКС».
5. В окне «Вставка функции» нажимаем «ОК». Откроется окно, изображенное на рис. 6.
6. Здесь выбираем вариант «массив;номер_строки; номер_столбца».
7. Нажимаем «ОК». Откроется окно «Аргументы функции», изображенное на рис. 7.
8. Щелкаем левой кнопкой в области «Массив».
9. Удерживая левую кнопку мышки, обводим блок «B6:IV65536» на рабочем листе.
10. В поля параметров «Номер_строки» и «Номер_столбца» вводим «1» и «2». Пока это фиктивные значения, со временем мы их изменим.
11. В окне «Аргументы функции» нажимаем «ОК». В результате наших действий в ячейке «B3» появится формула «=ИНДЕКС(B6:IV65536; 1;1)».
12. Переходим на ближайшую свободную ячейку. Я для этой цели выбрал «C3».
13. Щелчком на значке «fx» открываем окно «Вставка функции» (рис. 5).
14. Список «Категория:» оставляем в положении «Ссылки и массивы».
15. В перечне «Выберите функцию:» щелкаем левой кнопкой на элементе «ПОИСКПОЗ».
16. В окне «Вставка функции» нажимаем «ОК». Откроется окно «Аргументы функции», но теперь оно выглядит, как на рис. 8.
17. Щелкаем левой кнопкой в области «Искомое значение».
18. Чтобы не вводить адрес ячейки с наименованием ТМЦ вручную, щелкаем левой кнопкой на ячейке «B1» рабочего листа.
19. Щелкаем левой кнопкой внутри области «Просматриваемый_массив».
20. Удерживая левую кнопку (или при помощи клавиатуры) выделяем на рабочем листе блок «A6:A65536».
21. В поле «Тип_сопоставления» вводим «0».
22. В окне «Аргументы функции нажимаем «ОК». В результате наших действий в «С3» появится формула «=ПОИСКПОЗ(B1;A6:A65536;0)».
23. Аналогичным способом в свободной ячейке «D3» строим формулу для поиска контрагента. Соответствующее выражение будет таким: «ПОИСКПОЗ(B2;B5:IV5;0)». Все компоненты формулы у нас есть. Остается собрать их в одной ячейке.
24. Переходим к ячейке «C3».
25. Нажимаем клавишу «F2», станет доступно для редактирования содержимое ячейки.
26. Выделяем текст формулы без знака «=» и копируем его в буфер обмена (комбинация «Ctrl+C»).
27. Нажимаем «Esc» (выходим из режима редактирования ячейки).
28. Становимся на ячейку «B3».
29. Нажимаем клавишу «F2».
30. Выделяем в тексте функции второй параметр (сейчас он равен «1») и вместо него вставляем содержимое буфера обмена (комбинация «Ctrl+V»). В результате в ячейке «В3» мы получим выражение «=ИНДЕКС(B6:IV65536;ПОИСКПОЗ(B1;A6:A65536;0);2)» (изменения выделены полужирным начертанием).
31. Таким же способом переносим текст из «D3» на место третьего параметра функции «ИНДЕКС()». Результат в «B3» будет такой: «=ИНДЕКС(B6:IV65536;ПОИСКПОЗ(B1;A6:A65536;0); ПОИСКПОЗ(B2;B5:IV5;0))».
Формула готова. Последнее, что я предлагаю сделать, — применить к ячейкам «B6:IV65536» условное форматирование, чтобы показать результат поиска в таблице хорошо заметным цветом. Для этого делаем так:
1. Выделяем блок «B2;B5:IV5».
2. Переходим в меню «Главная».
3. В группе «Стили» щелкаем на иконке «Условное форматирование». Откроется меню, как на рис. 9.
4. Из этого меню выбираем «Создать правило…». Откроется окно «Создать правило форматирования» (рис. 10).
5. В списке «Тип правила» выбираем «Использовать формулу для определения форматируемых ячеек».
6. В область «Измените описание правила:» вводим формулу «=B6=$B$3». В области «Применяется к» должна быть ссылка на блок «B6:IV65536».
7. Щелкаем на кнопке «Формат…». Откроется окно для форматирования ячеек. В нем выбираем неяркий, но хорошо заметный цвет заливки (например, «светло-серый»).
8. В окне создания правила форматирования нажимаем «ОК».
Посмотрим, что мы получили на самом деле:
1. Щелкаем на значке выпадающего списка в ячейке «B1». Откроется список ТМЦ, которые есть в колонке «A».
2. Из списка выбираем, например, вариант «Бум."XEROX" TCF A4».
3. Щелкаем на списке в ячейке «B2». Откроется список контрагентов, которые указаны в пятой строке исходной таблицы.
4. Выбираем из списка вариант «ТОВ "Sotel"». В ячейке «B3» видим объем реализации «770,4», а в основной таблице это значение выделено цветом (рис. 11).
Итак, мы создали таблицу с удобным инструментом поиска по двум координатам. Исходные значения выбираются из списка. Это гарантирует, что они будут введены правильно, без лишних пробелов и т. п. Результат поиска находится в ячейке «B3». Теперь это значение можно использовать в дальнейших расчетах в зависимости от конкретной ситуации.
Кстати, у нашей формулы есть альтернативный вариант, где вместо функции «ИНДЕКС()» можно применить вариант «ВПР()». Для примера на рис. 11 формула выглядит так: «=ВПР(B1;6:65536;ПОИСКПОЗ(B2;B5:IV5;0)+1;0)». В ней основной поиск по видам товаров делает функция «ВПР()». Для определения смещения (третий параметр «ВПР()») я применил функцию «ИНДЕКС()».
Как организовать «нечеткий» поиск
На практике поиск в таблице иногда нужно делать не по точному, а по приблизительному совпадению исходных величин. Такая ситуация характерна, например, при подготовке смет или сложных прайсов. Элементарный пример. Есть таблица, изображенная на рис. 12. В ней записаны данные для расчета себестоимости изделия (блок «C7: K16»). Себестоимость зависит от площади изделия, т. е. от его высоты и ширины, которые указаны в заголовках таблицы. Причем в блоках «B7:B16» и «C6:K6» перечислены только стандартные значения размеров. Внутри таблицы находится стоимость для изделия соответствующей площади.
В верхней части таблицы предусмотрены два поля для расчета себестоимости — ячейки «D3» (высота) и «G3» (ширина изделия). Принципиальным является тот факт, что эти величины могут отличаться от стандартных значений! И тогда себестоимость нужно определить как ближайшую большую стандартную величину. Например, если пользователь указал ширину «320 мм» и высоту «710 мм», то значение себестоимости должно быть посчитано для изделия размером «350х800 мм». Проблема заключается в том, что округлить ширину и высоту по правилам обычной арифметики (при помощи функции «ОКРУГЛ()») мы не можем. Это нужно делать с учетом шкалы допустимых значений, и никак иначе. А таких средств у MS Excel нет. Здесь нужен другой подход, который использует параметры функции «ПОИСК ПОЗ()». Чтобы разобраться с ним, делаем так:
1. Открываем документ. Заполняем таблицу, как показано на рис. 12.
Важно! Значения высоты в блоке «B7:B16» должны идти в порядке убывания. Стандартные значения ширины (блок «C6:K6») должны убывать в направлении «слева направо». Если исходная таблица не удовлетворяет этим требованиям, ее нужно отсортировать.
2. В ячейки «D3» и «G3» вводим значения для расчета себестоимости. Мы решили, что это будет ширина «320 мм» и высота «710 мм» (рис. 12).
3. В ячейку «K3» вводим формулу «=ИНДЕКС(C7:K16;ПОИСКПОЗ(D3;B7:B16;-1); ПОИСКПОЗ(G3;C6:K6;-1))». Результат ее работы — значение себестоимости «2700» (грн.).
Все работает, как мы и предполагали. Результат «2700» соответствует себестоимости изделия шириной «350 мм» и высотой «800 мм». То есть исходную ширину «320 мм» формула «округлила» до ближайшего большего стандартного значения. То же самое она сделала и с высотой.
Пару слов о том, чем отличается наша формула из предыдущего примера. Разница только в последнем аргументе обеих функций «ПОИСК ПОЗ ()». В последнем случае параметр «Тип сопоставления» мы указали равным «-1». В этом случае функция «ПОИСКПОЗ ()» будет искать в списке не точное, а приблизительное совпадение элемента с указанным значением для поиска. А в результате — для нестандартных «320х710 мм» мы получим себестоимость изделия размером «350х800 мм». Что и требовалось для решения задачи.
Округлять в большую сторону, это конечно хорошо. А как поступить, если нужно выбрать ближайший меньший элемент из таблицы значений? Кстати, на практике такой вариант встречается довольно часто. Решить проблему можно тем же самым способом. Только для этого имеет смысл реорганизовать таблицу: отсортировать значения в заголовках строк и колонок по возрастанию. Применительно к таблице на рис. 12 делаем так:
1. Открываем документ, выделяем блок «B7:K16».
2. Вызываем меню «Главная», в группе «Редактирование» щелкаем на иконке «Сортировка и фильтр».
3. Из предложенного меню выбираем «Настраиваемая сортировка…» (рис. 13). Откроется окно «Сортировка», как на рис. 14.
4. В этом окне список «Сортировать по» ставим в положение «Столбец B».
5. Параметр «Порядок» оставляем в положении «По возрастанию».
6. В окне «Сортировка» нажимаем «ОК». Excel упорядочит таблицу по строкам. Но остались еще колонки…
7. В таблице выделяем блок «С6:K16».
8. Щелкаем на иконке «Сортировка и фильтр», выбираем «Настраиваемая сортировка…».
9. В окне «Сортировка» щелкаем на кнопке «Параметры…». Откроется окно «Параметры сортировки», как на рис. 15.
10. Здесь переключатель «Сортировать» ставим в положение «столбцы диапазона».
11. В окне «Параметры сортировки» нажимаем «ОК». Снова станет доступным окно «Сортировка».
12. В нем щелкаем на списке «Сортировать по», из предложенных вариантов выбираем «Строка 6» — в ней у нас расположены стандартные значения ширины изделий (рис. 16).
13. Параметр «Порядок» оставляем в положении «По возрастанию».
14. В окне «Сортировка» нажимаем «ОК». Таблица примет вид, как на рис. 17.
15. Становимся в ячейку «K3» и вводим формулу: «=ИНДЕКС(C7:K16;ПОИСКПОЗ(D3;B7:B16;1); ПОИСКПОЗ(G3;C6:K6;1))».
Важно! Обратите внимание, что параметр «Тип сопоставления» в обеих функциях «ПОИСКПОЗ()» теперь равен «1».
Результат работы формулы равен «1650 грн.» (рис. 17). То есть для изделия высотой «710 мм» и шириной «320 мм» она посчитала себестоимость, исходя из размеров «300х700 мм». А это и есть ближайший меньший вариант из стандартного набора значений.
На сегодня все. Удачной работы! Жду ваших вопросов, замечаний и предложений на bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum.
