Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как выполнить ВПР и вернуть несколько соответствующих значений по горизонтали в Excel?

Как выполнить ВПР и вернуть несколько соответствующих значений по горизонтали в Excel?

Допустим, вы пытаетесь найти расширение сотрудника или размер его комиссионных, используя номер его эмблемы. Поиск данных может осуществляться легко и эффективно, а данные могут быть автоматически проверены на правильность. Поиск данных позволяет получить результаты, которые можно рассчитать или отобразить с возвращаемым значением. Существует несколько способов поиска значений в списке данных и отображения результатов.

Как сделать выборку по всех одинаковых значениях столбца в Excel

В столбце E находится формула массива, для ввода которой требуется CTRL+SHIFT+Enter. Сначала формула проверяет, находится ли имя в ячейке, соседней с именем в столбце D. Если ячейка пуста, она возвращает пустую строку. В первом случае используется функция ИНДЕКС, которая возвращает количество повторений имени в исходной таблице. Во втором формула:

0;ИНДЕКС($B$1:$B$17;НАИМЕНЬШИЙ(ЕСЛИ(($A$2:$A$17=D2);СТРОКА($A$2:$A$17);»»);СТРОКА()-1));»»)’ >сделать выборку по всех одинаковых значениях.

Приведенная выше формула выполняет несколько функций, а именно: INDEX, LOWEST и STRING. LOWEST находит наименьшую строку, которая передается в качестве аргумента в INDEX, который возвращает значение, найденное из этой строки.

L OWEST — это аргумент для INDEX. Его первый аргумент состоит из таблицы строк (в данном случае таблицы пустых значений), а второй аргумент — это порядковый номер наименьшего значения. При втором аргументе, равном 2, функция возвращает второе наименьшее значение. Вторым аргументом является формула СТРОКА-1 — в данном случае она выступает в качестве счетчика порядкового номера наименьших значений. Число 1 вычитается, так как данные в исходной таблице записаны, начиная со второй строки. Формула во второй строке рабочего листа Excel возвращает первое наименьшее значение, в третьей строке — второе наименьшее значение и т.д.

Функция LOWEST игнорирует текстовые строки и работает только с числами. Чтобы получить номер строки ячейки в диапазоне A2:A17, содержащей оригинальные имена поставщиков, необходимо использовать функцию ЕСЛИ. В результате будет возвращена пустая строка, которую функция СЧЁТЕСЛИ просто игнорирует, если текущее имя не соответствует искомому. Как только в функции ЕСЛИ будет использовано искомое имя «Засядько Петр», таблица числовых значений будет передана в функцию СЧЁТЕСЛИ. Схематическое представление таблицы массивов в памяти программы выглядит следующим образом:

Читайте так же:
Как выделить строку, если ячейка содержит дату в Excel?

Таблица массива состоит из 16 элементов. Текущий номер ячейки в таблице вводится в таблицу, если в проверяемой ячейке есть строка с именем Засядько Петр, а функция LOWEST возвращает наименьшее по порядку число. В данном случае 4-я формула ячейки E5 возвращает номер ячейки 12. В таблице массива этот элемент является четвертым непустым элементом. Кроме того, функция ИНДЕКС возвращает значение из соседней ячейки в этом ряду — 136,41.

Что такое формула массива в Excel

Табличные формулы — это особый тип формул, которые работают с массивами чисел. Для ввода обычной формулы требуется одно нажатие клавиши Enter. Чтобы ввести формулу массива, нажмите клавишу Enter, одновременно удерживая клавиши CTRL и SHIFT. Как показано на рисунке, комбинация клавиш — CTRL+Shift+Enter.

Чтобы убедиться, что в ячейке используется формула массива, проверьте строку формул. Excel помещает формулу массива в скобки. Нет смысла вводить эти скобки самостоятельно, так как это не сработает. Просто комбинация клавиш!

Стоит отметить, что некоторые функции, такие как SUMMPROIZV, работают по тому же принципу, что и формула массива, но для них не нужны сочетания клавиш, поскольку они, по сути, являются просто формулами. UMMPROIZV вычисляет только сумму значений в массивах данных. Если вы хотите выполнить другие операции с массивами данных, а не только суммирование, например, использовать формулы с функциями LARGE или LARGE, то вы должны использовать формулу массива с этими функциями. Вам необходимо нажать CTRL+SHIFT+Enter!

Поиск приблизительного значения ВПР в Excel

В примере (снимок 2) UPR используется для расчета повышения на основе текущей заработной платы. Критерием выбора является зарплата, хранящаяся в поле F3. Матрица представляет собой диапазон $I$3:$J$10 (весь массив).

Читайте так же:
Как добавить / вычесть недели до даты в Excel?

Адрес является абсолютным (обозначается знаком $), поэтому можно скопировать всю результирующую структуру без изменения области видимости. Col_index_num равен 2 (процентное приращение находится в соседнем массиве).

Параметр порядка сортировки Range_lookup установлен в 1, поскольку вы хотите, чтобы формула выдавала результат, даже если не будет найдено идеальное совпадение. При значении Lookup_value, равном 35 850, VPR не может найти идеальное совпадение в массиве, но ближайшее минимальное число равно 35 000, поэтому ответ равен 4%. На этом учебник «VPR для чайников» закончен.

Lookup

Двумерный поиск в таблице (ВПР 2D)

При работе с VLOOKUP или ее горизонтальным эквивалентом HLOOKUP следует помнить, что эти замечательные функции могут искать информацию только по одному параметру, то есть по строке или столбцу одномерного массива. Как бы вы выбирали данные из двумерной таблицы, сопоставляя сразу и строку, и столбец? Давайте рассмотрим несколько реальных примеров таких задач и их решений.

Пример 1. Найти значение по товару и городу

Предположим, что у нас есть двумерный массив данных о городах и товарах:

vlookup2d1.png

Пользователь вводит (или выбирает из выпадающих списков) в желтых ячейках нужный продукт и город. В зеленой ячейке нужно найти и вывести с помощью формулы число из таблицы, соответствующее выбранным параметрам. Фактически, мы хотим найти значение ячейки из пересечения определенной строки и столбца таблицы. Для наглядности давайте разделим задачу на три этапа.

  • Сначала необходимо определить номер строки, соответствующий продукту, выбранному пользователем в желтой ячейке. Для этого используется функция MATCH из категории Lookup and Reference. Формула MATCH(J2; A2:A10; 0) дает нам нужный результат (в случае с яблоком это будет число 6). Первый аргумент этой функции — искомое значение (яблоко из желтой ячейки J2), второй — диапазон ячеек, в которых мы ищем продукт (столбец с продуктами в таблице — A2:A10), третий аргумент определяет тип поиска (0 — точное совпадение названия продукта, приблизительный поиск запрещен).
  • Во-вторых, очень похожим образом нам нужно указать порядковый номер столбца в таблице с нужным городом. Функция ПОИСК(J3; B1:F1; 0) сделает это и выведет, например, значение 4 для Киева, выбранное пользователем в желтой ячейке J3.
  • И, наконец, в-третьих, нам нужна функция, которая умеет выводить содержимое ячейки из таблицы, используя номера строк и столбцов — функция ИНДЕКС из той же категории ПОИСК и ССЫЛКА. Первый аргумент этой функции — диапазон ячеек (в нашем случае это вся таблица, т.е. B2:F10), второй — номер строки, третий — номер столбца (их мы определим с помощью функции ПОИСКПОЗ).
Читайте так же:
Как вставить или отобразить именованный диапазон на другом листе?

Объединив все приведенные выше формулы в одну, мы получим решение для зеленой клетки:

=ИНДЕКС(B2:F10; ПОИСКПОЗ(J2;A2:A10;0) ; ПОИСКПОЗ(J3;B1:F1;0) )

Или на английском языке

Пример 2. Приблизительный двумерный поиск

Давайте немного изменим предыдущий пример. Давайте предположим, что мы имеем дело с такой ситуацией:

vlookup2d2.png

Цель состоит в том, чтобы пользователь ввел в желтые ячейки высоту и ширину двери, например, для шкафа, который он хочет заказать у производителя. Стоимость отображается в серой ячейке. Важно, что значения нестандартных размеров округляются до ближайшего доступного значения в таблице, а стоимость изготовления этих округленных стандартных размеров отображается в серой ячейке, если пользователь ввел их.

Почти точно такое же, как и в предыдущем примере, решение для серой ячейки:

=ИНДЕКС(C7:K16; ПОИСКПОЗ(D3;B7:B16;1) ; ПОИСКПОЗ(G3;C6:K6;1) )

=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))

Единственным отличием является последний аргумент в обеих функциях MATCH — Match Type (здесь это минус 1). Это соответствует четвертому аргументу функции VLOOKUP — Range Lookup. В общем, он имеет три возможных значения:

  • 1 — Поиск ближайшего минимального числа, т.е. размеры ворот, введенные пользователем, округляются до ближайшего подходящего минимального размера из таблицы. В нашем случае высота 500 будет округлена до 450, а ширина 480 — до 300, и цена двери составит 135.
  • -1 — Найти ближайшее наибольшее число, например, подходящую высоту 500 округлить до 700, а ширину 480 — до 600, и цена составит 462. Это гораздо интереснее для бизнеса! 🙂
  • 0 — поиск точного совпадения без округления. Используется для нахождения 100% совпадения искомого значения с одним из значений в таблице. Используется, конечно, при поиске текстовых параметров (как в предыдущем примере), поскольку для них округление невозможно.

При использовании приблизительного поиска с округлением необходимо отсортировать диапазон поиска — а значит, и всю таблицу — в порядке возрастания (для Match Type = 1) или убывания (для Match Type = -1) по строкам и по столбцам. В противном случае приблизительный поиск не будет работать!

Читайте так же:
Как заменить последнюю запятую на и в ячейках в Excel?

Сортировка не нужна для точного поиска (Match Type = 0) и не имеет эффекта.

P.S. Обратная задача

Многие комментаторы задавались вопросом — а как сделать обратную операцию, т.е. как определить по таблице город и товар, если мы знаем стоимость? Для этого потребуются две небольшие формулы массива (вводить их нужно не Enter, а Ctrl+Shift+Enter):

ИНДЕКС и МАТЧ

Функции ПОИСК редко используются в одиночку. Они часто комбинируются с другими функциями для создания эффективных формул. Когда функция ПОИСК сочетается с функцией ИНДЕКС, она позволяет осуществлять расширенный поиск. Многие люди по-прежнему предпочитают использовать WRP для поиска значений, потому что это проще, но функция ПОИСК ИНДЕКСА более гибкая и быстрая, чем WRP.

В отличие от функции VLOOKUP, которая действует только по вертикали, функция INDEX MATCH действует и по вертикали, и по горизонтали.

Функция ИНДЕКС используется для получения значения в определенном месте таблицы или диапазона. Функция ПОИСКПОЗ возвращает относительное положение значения в столбце или строке. В сочетании функция ПОИСК находит номер строки или столбца (местоположение) определенного значения, а функция ИНДЕКС извлекает значение на основе номера строки и столбца.

Синтаксис функции ИНДЕКС:

Так или иначе, давайте рассмотрим пример того, как работает INDEX MATCH.

В приведенном ниже примере мы хотим получить оценку”Quiz2″для ученицы”Anne”. Для этого воспользуемся следующей формулой:

Для ИНДЕКСА необходимо указать номер строки и столбца. Как показано в приведенной выше формуле, вложенная функция ПОИСКПОЗ находит номер строки значения «Анна» (H2). С этим номером строки мы передаем диапазон B2:F20 и номер столбца (3) в функцию ИНДЕКС. И функция ИНДЕКС возвращает результат «91».

Двусторонний поиск с помощью INDEX и MATCH

Вы также можете использовать функции ИНДЕКС и ПОИСК для поиска значения в двумерном диапазоне (двусторонний поиск). В примере выше мы использовали функцию ПОИСКПОЗ, чтобы найти номер строки значения, но номер столбца мы ввели вручную. Но мы можем найти и строку, и столбец, вложив две функции SEARCH, одну в аргумент row_num и одну в аргумент column_num функции INDEX.

Читайте так же:
Как выделить строки на основе раскрывающегося списка в Excel?

Используйте эту формулу для двустороннего поиска с INDEX и MATCH :

В функции ПОИСКПОЗ можно искать как по горизонтали, так и по вертикали. В этой формуле вторая функция ПОИСКПОЗ в аргументе colum_num находит позицию Quiz2 (4) и передает ее функции ИНДЕКС. А INDEX извлекает результат.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector