Как найти значение по двум критериям

Поиск и подстановка по нескольким условиям

Постановка задачи

Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно — без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?

Предположим, что у нас есть база данных по ценам товаров за разные месяцы:

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

Способ 1. Дополнительный столбец с ключом поиска

Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы: Простой способ, знакомая функция, работает с любыми данными.

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

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS), появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

Плюсы: Не нужен дополнительный столбец, решение легко масштабируется на большее количество условий (до 127), быстро считает.

Минусы: Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

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

1. Выделите пустую зеленую ячейку, где должен быть результат.
2. Введите в строке формул в нее следующую формулу:

   

3. Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы: Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы: Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

Ссылки по теме

• Как искать и подставлять данные с помощью функции ВПР (VLOOKUP)
• Что такое формулы массива и как их использовать
• Как использовать связку функций ИНДЕКС и ПОИСКПОЗ вместо ВПР
• Как извлечь сразу все значения, а не только первое с помощью ВПР

ВПР с несколькими условиями: 5 примеров.

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

Мы предложим вам несколько вариантов решения проблемы поиска по нескольким условиям.

1. ВПР по нескольким условиям с использованием дополнительного столбца.
2. ВПР по двум условиям при помощи формулы массива.
3. ВПР по нескольким критериям с применением массивов — способ 2.
4. Двойной ВПР при помощи ИНДЕКС + ПОИСКПОЗ
5. Достойная замена – функция СУММПРОИЗВ.

ВПР по нескольким условиям с использованием дополнительного столбца.

Задачу, рассмотренную в предыдущем примере, можно решить и другим способом – без использования формулы массива. Ведь работа с массивами многим представляется сложной и недоступной для понимания. Дополнительный столбец для поиска по нескольким условиям будет в определенном отношении более простым вариантом.

Итак, необходимо выбрать значение выручки за определенный месяц, год и по нужному магазину. В итоге имеем 3 условия отбора.

Сразу по трем столбцам функция ВПР искать не может. Поэтому нам нужно объединить их в один. И, поскольку поиск производится всегда в крайнем левом (первом) столбце, то нужно добавить его в нашу таблицу тоже слева.

Вставляем перед таблицей с данными дополнительный столбец A. Затем при помощи оператора & объединяем в нем содержимое B,C и D. Записываем в А7

=B7&C7&D7

и копируем в находящиеся ниже ячейки.

Формула поиска в D4 будет выглядеть:

=ВПР(D1&D2&D3;A7:E20;5;0)

В диапазон поиска включаем и наш дополнительный столбец. Критерий поиска – также объединение 3 значений. И извлекаем результат из 5 колонки.

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

Вид станет приятнее, а на результаты это никак не повлияет.

ВПР по двум условиям при помощи формулы массива.

У нас есть таблица, в которой записана выручка по каждому магазину за день. Мы хотим быстро найти сумму продаж по конкретному магазину за определенный день.

Для этого в верхней части нашего листа запишем критерии поиска: дата и магазин. В ячейке B3 будем выводить сумму выручки.

Формула в B3 выглядит следующим образом:

{=ВПР(B1;ЕСЛИ(B6:B19=B2;A6:C19;»»);3;0)}

Обратите внимание на фигурные скобки, которые означают, что это формула массива. То есть наша функция ВПР работает не с отдельными значениями, а разу с массивами данных.

Разберем процесс подробно.

Мы ищем дату, записанную в ячейке B1. Но вот только разыскивать мы ее будем не в нашем исходном диапазоне данных, а в немного видоизмененном. Для этого используем условие

ЕСЛИ(B6:B19=B2;A6:C19;»»)

То есть, в том случае, если наименование магазина совпадает с критерием в ячейке B2, мы оставляем исходные значения из нашего диапазона. А если нет – заменяем их на пробелы. И так по каждой строке.

В результате получим вот такой виртуальный массив данных на основе нашей исходной таблицы:

Как видите, строки, в которых ранее был «Магазин 1», заменены на пустые. И теперь искать нужную дату мы будем только среди информации по «Магазин 2». И извлекать значения выручки из третьей колонки.

С такой работой функция ВПР вполне справится.

Такой ход стал возможен путем применения формулы массива. Поэтому обратите особое внимание: круглые скобки в формуле писать руками не нужно! В ячейке B3 вы записываете  формулу

=ВПР(B1;ЕСЛИ(B6:B19=B2;A6:C19;»»);3;0)

И затем нажимаете комбинацию клавиш CTRL+Shift+Enter.  При этом Excel поймет, что вы хотите ввести формулу массива и сам подставит скобки.

Таким образом, функция ВПР поиск по двум столбцам производит в 2 этапа: сначала мы очищаем диапазон данных от строк, не соответствующих одному из условий, при помощи функции ЕСЛИ и формулы массива. А затем уже в этой откорректированной информации производим обычный поиск по одному только второму критерию при помощи ВПР.

Чтобы упростить работу в будущем и застраховать себя от возможных ошибок при добавлении новой информации о продажах, мы рекомендуем использовать «умную» таблицу. Она автоматически подстроит свой размер с учетом добавленных строк, и никакие ссылки в формулах не нужно будет менять.

Вот как это будет выглядеть.

ВПР по нескольким критериям с применением массивов — способ 2.

Выше мы уже рассматривали, как при помощи формулы массива можно организовать поиск ВПР с несколькими условиями. Предлагаем еще один способ.

Условия возьмем те же, что и в предыдущем примере.

Формулу в С4 введем такую:

=ВПР(C1&C2&C3;ВЫБОР({1;2};A7:A20&B7:B20&C7:C20;D7:D20);2;0)

Естественно, не забываем нажать CTRL+Shift+Enter.

Теперь давайте пошагово разберем, как это работает.

Наше задача здесь – также создать дополнительный столбец для работы функции ВПР. Только теперь мы создаем его не на листе рабочей книги Excel, а виртуально.

Как и в предыдущем примере, мы ищем текст из объединенных в одно целое условий поиска.

Далее определяем данные, среди которых будем искать.

ВЫБОР({1;2};A7:A20&B7:B20&C7:C20;D7:D20)

Конструкция вида  A7:A20&B7:B20&C7:C20;D7:D20  создает 2 элемента. Первый – это объединение колонок A, B и C из исходных данных. Если помните, то же самое мы делали в нашем дополнительном столбце. Второй D7:D20 – это значения, одно из которых нужно в итоге выбрать.

Функция ВЫБОР  позволяет из этих элементов создать массив. {1,2} как раз и означает, что нужно взять сначала первый элемент, затем второй, и объединить их в виртуальную таблицу – массив.

В первой колонке этой виртуальной таблицы мы будем искать, а из второй – извлекать результат.

Таким образом, для работы функции ВПР с несколькими условиями мы вновь используем дополнительный столбец. Только создаем его не реально, а виртуально.

Двойной ВПР при помощи ИНДЕКС + ПОИСКПОЗ

Далее речь у нас пойдет уже не о функции ВПР, но задачу мы будем решать ту же самую. В качестве критерия поиска нам опять нужно использовать несколько условий.

Существуют, пожалуй, даже более гибкие решения, нежели функция ВПР. Это комбинация функций ИНДЕКС + ПОИСКПОЗ.

Область их применения очень велика, о чем бы также будем рассказывать на сайте mister-office.ru.

А пока вернемся вновь к нашей задаче.

Формула в С4 теперь выглядит так:

=ИНДЕКС(D7:D20;ПОИСКПОЗ(1;(A7:A20=C1)*(B7:B20=C2)*(C7:C20=C3);0))

И не забываем при вводе нажать CTRL+Shift+Enter! Это формула массива.

Теперь давайте разбираться, как это работает.

Функция ИНДЕКС в нашем случае позволяет извлечь элемент из списка по его порядковому номеру. Список – это диапазон D7:D20, где записаны суммы выручки. А вот порядковый номер, который нужно извлечь, мы определяем при помощи ПОИСКПОЗ.

Синтаксис здесь следующий:

ПОИСКПОЗ(что_ищем; где_ищем; тип_поиска)

Тип поиска ставим 0, то есть точное совпадение. В нашем случае мы будем искать 1. Далее мы определим массив, в котором будем работать.

Выражение  (A7:A20=C1)*(B7:B20=C2)*(C7:C20=C3) позволит создать виртуальную таблицу примерно такого вида:

Как видите, первоначально мы последовательно сравниваем каждое значение с нашим критерием отбора. В столбце А у нас записаны месяцы – сравниваем их с месяцем-критерием из ячейки C1. В случае совпадения получаем ИСТИНА, иначе – ЛОЖЬ. Аналогично последовательно проверяем год и название магазина. А затем просто перемножаем значения. Поскольку логические переменные для Excel – это либо 0, либо 1, то произведение их может быть равно 1 только в том случае, если мы имеем по каждой колонке ИСТИНА (то есть,1). Во всех остальных случаях получаем 0.

Убеждаемся, что цифра 1 встречается только единожды.

При помощи ПОИСКПОЗ определяем, на какой позиции она находится. На какой позиции находится 1, на той же позиции находится в массиве и искомая сумма выручки. В нашем случае это 10-я.

Далее при помощи ИНДЕКС извлекаем 10-ю по счету выручку.

Таким образом мы выбрали значение по нескольким условиям без использования функции ВПР.

Достойная замена – функция СУММПРОИЗВ.

У нас есть данные о продажах нескольких менеджеров в различных регионах. Нужно сделать выборку по дате, менеджеру и региону.

Поясним расчеты.

Выражение

=СУММПРОИЗВ((A2:A27=$G$2)*(B2:B27=$G$3)*($C$2:$C$27=G4)*(D2:D27))

Работает как формула массива, хотя по факту таковой не является. В этом заключается замечательное свойство функции СУММПРОИЗВ, о которой мы еще много будем говорить в других статьях.

Последовательно по каждой строке диапазона от 2-й до 27-й она проверяет совпадение каждого соответствующего значения с критерием поиска. Эти результаты перемножаются между собой и в итоге еще умножаются на сумму выручки. Если среди трех условий будет хотя бы одно несовпадение, то итогом будет 0. В случае совпадения сумма выручки трижды умножится на 1.

Затем все эти 27 произведений складываются, и результатом будет выручка нужного менеджера в каком-то регионе за определенную дату.

В качестве бонуса можно продолжить этот пример и рассчитать общую сумму продаж менеджера в определенном регионе.

Для этого из формулы просто уберем сравнение по дате.

=СУММПРОИЗВ((A2:A27=$G$2)*(B2:B27=$G$3)*(D2:D27))

Кстати, возможен и другой вариант расчета с этой же функцией:

=СУММПРОИЗВ(—(A2:A27=$G$2);—(B2:B27=$G$3);(D2:D27))

Итак, мы рассмотрели примеры использования функции ВПР с двумя и с несколькими условиями. А также обнаружили, что этой ценной функции есть замечательная альтернатива.

[the_ad_group id=»48″]

Примеры использования функции ВПР:

Хитрости »

18 Январь 2017              123490 просмотров

---

ВПР по двум и более критериям

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

Для большего понимания смысла статьи рекомендую сначала скачать файл с примером данных и формул:
Скачать пример:

  Tips_All_VlookupMany.xls (62,5 KiB, 10 286 скачиваний)

Например, имеется файл с таблицей такого вида:

И необходимо получить сумму выручки не только на основании салона, но и на основании бренда. При этом делать это автоматически, например для получения данных в такую табличку:

Т.е. в ячейке B2 из выпадающего списка выбирается Салон, а из B3 — Бренд(подробнее про выпадающие списки можно прочесть в статье Выпадающие списки). И в зависимости от выбора должна рассчитаться сумма Выручки и должен проставиться комментарий.

В приложенном к статье файле исходная таблица находится на листе «Отчет сентябрь», а вторая(с выбором) на листе «Выбор». На самом деле это не сильно принципиально, просто будет более понятна логика формул, приведенных ниже.
Сумму по двум критериям можно найти при помощи той же СУММЕСЛИМН(SUMIFS):
=СУММЕСЛИМН(‘Отчет сентябрь’!$C$2:$C$67;’Отчет сентябрь’!$A$2:$A$67;B2;’Отчет сентябрь’!$B$2:$B$67;B3)
=SUMIFS(‘Отчет сентябрь’!$C$2:$C$67,’Отчет сентябрь’!$A$2:$A$67,Выбор!B2,’Отчет сентябрь’!$B$2:$B$67,Выбор!B3)
Подробнее про поиск сумм по двум и более критериям можно прочитать в статье Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям.
А вот с комментарием сложнее — там содержится текст и СУММЕСЛИМН ничего не найдет. Да и в случае, если в исходных данных есть задвоения и суммировать по определению ничего не надо СУММЕСЛИМН будет не лучшим вариантом — ведь она вернет сумму всех ячеек, удовлетворяющих условиям. А это, повторюсь — не всегда нужно.
Здесь на помощь придет родственная с ВПР функция — ПОИСКПОЗ(MATCH):
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(B2&B3;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=INDEX(‘Отчет сентябрь’!$D$2:$D$67,MATCH(B2&B3,’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67,0))
Эта функция является формулой массива. Это означает, что вводить её в ячейки надо нажатием не просто Enter, а сочетанием трех клавиш — Ctrl+Shift+Enter. Теперь рассмотрим подробнее принцип работы этой формулы, чтобы можно было применить на любых данных. На самом деле принцип не так уж сложен. Основной упор сделан на эти две «связки»:

• B2&B3 — здесь объединяем значение выбранного Салона(Санкт-Петербург) и Бренда(Ниссан) в одну строку, чтобы получилось «Санкт-ПетербургНиссан». За объединение двух значений отвечает амперсанд(&).
• ‘Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67 — а здесь мы последовательно объединяем в одну строку значения двух столбцов исходных данных — Салон и Бренд. Т.е. в результате мы получим массив объединенных значений: МоскваBMW, ВоронежBMW, СамараЛексус и т.д.(именно это объединение требует, чтобы формула вводилась как формула массива). И уже среди этих значений ищем «Санкт-ПетербургНиссан». При нахождении совпадения ПОИСКПОЗ вернет позицию строки, в которой нашел. И передаст её в ИНДЕКС. А т.к. ИНДЕКС в нашем случае возвращает значение из заданной строки указанного массива, то мы получаем требуемое.

По шагам это будет выглядеть так:

сначала искомые значения будут объединены в один
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(B2&B3;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=>
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(Санкт-Петербург&Ниссан;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=>
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(Санкт-ПетербургНиссан;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=>
далее так же объединяем построчно значения всех столбцов для поиска значений
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(Санкт-ПетербургНиссан;{«МоскваBMW»:»ВоронежBMW»:»СамараЛексус»:…};0))
=>
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;4)
=>
только допы

Здесь важно следить за тем, чтобы значения в исходном массиве для поиска объединялись в том же порядке, что и искомые значения. На приложенном примере, если первым аргументом(искомое_значение) в ПОИСКПОЗ мы указали сначала Салон(B2), а потом Бренд(B3), то и вторым аргументом(просматриваемый_массив) мы должны указать два столбца в том же порядке — сначала Салон(‘Отчет сентябрь’!$A$2:$A$67), а уже потом Бренд(‘Отчет сентябрь’!$B$2:$B$67). Если их перепутать ошибки как таковой это не вызовет, но и значения найдены не будут.
Так же стоит учитывать, что такая формула будет рассчитываться дольше обычной, поэтому не стоит указывать столбцы полностью(‘Отчет сентябрь’!$A:$A), т.к. это может повлечь за собой расчет всего одной формулы непозволительно долгое время.
При этом очевидно, что просматривать значения можно не только из смежных столбцов, а из любых и уж точно они могут быть и первыми, и последними. И значения возвращаемые могут так же располагаться в любом столбце таблицы. И также хочу заметить, что это в примере используется только два критерия — реально их можно сделать и три, и пять, и десять. Объединяем сколько надо, указываем нужное кол-во столбцов так же с объединением и все. Но тут надо понимать, что в некоторых случаях будет оптимальнее добавить в исходные данные(те, где ищем) еще один столбец, в котором записать все данные, объединив их. Т.е. опять же на примере файла из статьи можно на листе «Отчет сентябрь» добавить формулу в столбец E: =A2&B2
и затем искать можно будет обычной формулой:
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(B2&B3;’Отчет сентябрь’!$E$2:$E$67;0))
=INDEX(‘Отчет сентябрь’!$D$2:$D$67,MATCH(B2&B3,’Отчет сентябрь’!$E$2:$E$67,0))
Больше нюансов, о которых я бы хотел рассказать, нет. Все стандартно, как для обычной связки ИНДЕКС(ПОИСКПОЗ.

В приложенном к статье примере приведен пример и с функцией СУММЕСЛИМН и с функцией ПОИСКПОЗ.
Скачать пример:

  Tips_All_VlookupMany.xls (62,5 KiB, 10 286 скачиваний)

P.S. Если кого-то пугает тот факт, что формулу надо вводить как формулу массива(Ctrl+Shift+Enter), то можно модифицировать формулу так:
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;СУММПРОИЗВ(МАКС((B2=’Отчет сентябрь’!$A$2:$A$67)*(B3=’Отчет сентябрь’!$B$2:$B$67)*(СТРОКА($B$2:$B$67)-1))))
=INDEX(‘Отчет сентябрь’!$D$2:$D$67,SUMPRODUCT(MAX((B2=’Отчет сентябрь’!$A$2:$A$67)*(B3=’Отчет сентябрь’!$B$2:$B$67)*(ROW($B$2:$B$67)-1))))
Здесь вместо ПОИСКПОЗ роль поиска номера строки в массиве ‘Отчет сентябрь’!$D$2:$D$67 играет СУММПРОИЗВ. Сам принцип работы СУММПРОИЗВ я описывал в этой статье — Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям. К этому могу добавить лишь некоторые разъяснения(хотя если хотите понять сам принцип — лучше потратить пару минут на статью).
(B2=’Отчет сентябрь’!$A$2:$A$67) — здесь значение выбранного салона сверяется со списком салонов на листе Отчет сентябрь. Где значения равны получаем значение ИСТИНА(TRUE), где отличаются — ЛОЖЬ(FALSE).
(B3=’Отчет сентябрь’!$B$2:$B$67) — здесь значение выбранного бренда сверяется со списком брендов на листе Отчет сентябрь. Где значения равны получаем значение ИСТИНА(TRUE), где отличаются — ЛОЖЬ(FALSE).
В результате перемножения этих двух выражений мы получим массив 1 и 0(1 будет там, где бренд и салон совпали, 0 — где различаются), т.к. ИСТИНА для Excel по сути =1, а ЛОЖЬ =0.
Далее полученный массив единиц и нулей перемножаем на выражение: СТРОКА($B$2:$B$67). Это выражение дает нам массив номеров строк {2:3:4:5:6:7:8 … и т.д.}т.к. в выражении берется номер строки на листе, а нам нужен номер в диапазоне ‘Отчет сентябрь’!$D$2:$D$67, то мы еще вычитаем 1(т.к. диапазон у нас начинается со второй строки). С таким же успехом можно было не вычитать, а либо все диапазоны взять с первой строки, либо указать так: СТРОКА($B$1:$B$66)
Полученный массив строк перемножаются на массив единиц и нулей салона и бренда. В результате получим номер строки, умноженный на 1 там, где салон и бренд равны искомым, и на ноль там, где отличаются. И в итоге массив номеров строки и нулей. И из этого отбирается максимальный номер строки.
По сути, это уже решает проблему поиска, но если значений, подходящих под условия больше одного, то именно такой результат может быть(даже скорее всего будет) неверным. Чтобы этого избежать мы применяем функцию МАКС(MAX), чтобы в результате из всех строк была отобрана только максимальная. Т.е. в результате мы получим не первое совпадение из всех возможных, а последнее.
Так же рекомендую ознакомиться со статьей Как просмотреть этапы вычисления формул, чтобы можно было самостоятельно поразбираться в принципах работы формул.

В сети можно найти так же решения при помощи макросов(VBA), но далеко не всегда есть возможность использовать макросы в файлах.

Также см.:
Как найти значение в другой таблице или сила ВПР
ВПР с возвратом всех значений
ВПР с поиском по нескольким листам
ВПР_МН
ВПР_ВСЕ_КНИГИ
Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям

---

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

---

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Продвинутые примеры с функцией ВПР: поиск по нескольким критериям

​Смотрите также​ также не придумал​​. Строчные и прописные​​ диапазоны вручную. Как​ выборки нужных значений​ на то, то​ определенного столбца. Очень​ не знаем точно​​ (числом, текстом или​​Для выполнения этой задачи​ открытой. Если же​Как Вы, вероятно, знаете,​.​В переводе на человеческий​ мы вычитаем число​3-й​ основную таблицу (Main​Во второй части нашего​ как использовать в​ буквы не различаются.​ заставить формулу работать​ из списка мы​​ совпадение должно быть​​ часто необходимо в​ как записана товарная​

• ​ логическим значением (ЛОЖЬ​ используется функция ГПР.​
• ​ она закрыта, функция​ функция​Чтобы добавить цены из​
• ​ язык, данная формула​3​
• ​товар, заказанный покупателем​ table), добавив данные​
• ​ учебника по функции​ этом случае ПОИСКПОЗ,​
• ​Диапазон_суммирования​ без ручного введения​

Поиск в Excel по нескольким критериям

​ недавно разбирали. Если​​ абсолютно точным.​​ запросе поиска использовать​ позиция относящаяся к​ или ИСТИНА)) или​ См. пример ниже.​ сообщит об ошибке​ДВССЫЛ​ второй таблицы поиска​ означает:​из результата функции,​Dan Brown​ из второй таблицы​ВПР​ ИНДЕКС, СУММПРОИЗВ.​- это те​

Пример 1: Поиск по 2-м разным критериям

​ диапазонов. СУММЕСЛИМН не​ вы еще с​Finnik​​ сразу несколько условий.​​ яблокам: яблоки или​ ссылкой на ячейку,​​Функция ГПР выполняет поиск​​#REF!​​используется для того,​​ в основную таблицу,​Ищем символы «Mar» –​ чтобы получить значение​:​ (Lookup table), которая​(VLOOKUP) в Excel​

​Заранее благодарен за​​ ячейки, значения которых​​ обязательное условие, единственное​ ней не знакомы​: Здравствуйте!​ Но по умолчанию​ яблоко.​ содержащую число, текст​ по столбцу​(#ССЫЛ!).​​ чтобы вернуть ссылку,​​ необходимо выполнить действие,​​ аргумент​​1​=VLOOKUP(«Dan Brown3»,$A$2:$C$16,3,FALSE)​

​ находится на другом​
​ мы разберём несколько​

​ помощь!​ мы хотим сложить,​​ о чем прошу-​​ — загляните сюда,​​Мой вопрос основан​​ данная функция не​В качестве критерия можно​

​ или логическое значение.​Продажи​Урок подготовлен для Вас​ заданную текстовой строкой,​ известное как двойной​lookup_value​​в ячейке​​=ВПР(«Dan Brown3»;$A$2:$C$16;3;ЛОЖЬ)​​ листе или в​​ примеров, которые помогут​СердЖиГ​ т.е. нашем случае -​ только не массивная​ не пожалейте пяти​ на конкретном примере​ может обработать более​​ задать»яблок*» и формула​​Просматриваемый_массив​и возвращает значение​

​ командой сайта office-guru.ru​ а это как​ВПР​(искомое_значение);​F4​​На самом деле, Вы​​ другой рабочей книге​ Вам направить всю​: Я часто использую​ стоимости заказов.​​ формула (файл под​​ минут, чтобы сэкономить​ из вложения.​

​ одного условия. Поэтому​
​ =ПОИСКПОЗ("яблок*";B53:B62;0) вернет позицию​

​— непрерывный диапазон​

​ из строки 5 в​
​Источник: https://www.ablebits.com/office-addins-blog/2014/07/29/vlookup-formula-examples/​

​ раз то, что​​или вложенный​​Ищем в ячейках от​(строка 4, вычитаем​​ можете ввести ссылку​​ Excel, то Вы​​ мощь​​ вот такую формулу:​​Если условий больше одного​​ 20 МБ).​ себе потом несколько​Скажите, пожалуйста, реально​

Пример 2: ВПР по двум критериям с просматриваемой таблицей на другом листе

​ следует использовать весьма​ текстового значения, начинающегося​ ячеек, возможно, содержащих​ указанном диапазоне.​Перевел: Антон Андронов​ нам сейчас нужно.​ВПР​ A1 до I1​ 3), чтобы получить​ на ячейку в​ можете собрать искомое​ВПР​{=СЧЁТ(ЕСЛИ(МАССИВПРОВЕРКИ1&МАССИВПРОВЕРКИ2=КРИТЕРИЙ1&КРИТЕРИЙ2;МАССИВ по которому​

​ (например, нужно найти​_Boroda_​ часов.​ ли вставить искомое​ простую формулу, которая​ со слова яблок​ искомые значения.​Дополнительные сведения см. в​Автор: Антон Андронов​

​ Итак, смело заменяем​​.​​ – аргумент​

​2​
​ качестве искомого значения​

​ значение непосредственно в​на решение наиболее​ считам))}.​ сумму всех заказов​: По какому принципу​​Если же вы знакомы​​ значение из столбца​ позволит расширить возможности​ (если она есть​

​Просматриваемый_массив​ разделе, посвященном функции​Предположим, что требуется найти​ в представленной выше​Запишите функцию​lookup_array​

​в ячейке​
​ вместо текста, как​

​ формуле, которую вставляете​ амбициозных задач Excel.​Но не смог​ Григорьева для «Копейки»),​ выбираются ручные диапазоны?​ с ВПР, то​ «количество» из листа​ функции ВПР по​ в списке).​может быть только​ ГПР.​ внутренний телефонный номер​ формуле выражение с​ВПР​

​(просматриваемый_массив);​​F5​​ представлено на следующем​​ в основную таблицу.​ Примеры подразумевают, что​ придумать, как использовать​ то функция​Почему в строке​ — вдогон -​ «Склад», в столбец​ нескольким столбцам одновременно.​Подстановочные знаки следует использовать​

​ одностолбцовым диапазоном ячеек,​К началу страницы​ сотрудника по его​ функцией​, которая находит имя​Возвращаем точное совпадение –​(строка 5, вычитаем​ рисунке:​Как и в предыдущем​ Вы уже имеете​ её в вышеописанном​​СУММЕСЛИ (SUMIF)​​ 7 ссылка на​​ стоит разобраться с​​ «На складе», находящийся​

Извлекаем 2-е, 3-е и т.д. значения, используя ВПР

​Для наглядности разберем формулу​​ только для поиска​​ например​Для выполнения этой задачи​ идентификационному номеру или​ЕСЛИ​ товара в таблице​ аргумент​ 3) и так​Если Вы ищите только​ примере, Вам понадобится​ базовые знания о​ случае.​не поможет, т.к.​ В7, а в​ похожими функциями:​

​ на листе «Архангельск»?​ ВПР с примером​ позиции текстовых значений​А9:А20​ используется функция ГПР.​ узнать ставку комиссионного​на ссылку с​Lookup table 1​match_type​ далее.​

​2-е​ в таблице поиска​ том, как работает​​Помогите, плизз!​​ не умеет проверять​ строке 8 -​ИНДЕКС (INDEX)​Я так понимаю,​​ нескольких условий. Для​​ и​​или диапазоном, расположенным​​Важно:​ вознаграждения, предусмотренную за​ функцией​​, используя​​(тип_сопоставления).​SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3))​повторение, то можете​

​ (Lookup table) вспомогательный​
​ эта функция. Если​

​Z​ больше одного критерия.​​ на А8?​​и​ что это должно​

• ​ примера будем использовать​​Типом сопоставления​​ в одной строке,​​  Значения в первой​​ определенный объем продаж.​

  ​ДВССЫЛ​
​SKU​

• ​Использовав​​НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3))​​ сделать это без​​ столбец с объединенными​​ нет, возможно, Вам​

  ​: А чего изобретать​
​ Поэтому начиная с​

​zegor​ПОИСКПОЗ (MATCH)​ быть реализовано через​ схематический отчет по​= 0 (третий​ например,​ строке должны быть​

​ Необходимые данные можно​​. Вот такая комбинация​​, как искомое значение:​0​Функция​ вспомогательного столбца, создав​

​ значениями. Этот столбец​
​ будет интересно начать​

​ паровоз — сводная,​

• ​ версии Excel 2007​​: Наверное так будет​, владение которыми весьма​ функцию ВПР. Но​ выручке торговых представителей​
• ​ аргумент функции).​​А2:Е2​​ отсортированы по возрастанию.​​ быстро и эффективно​
• ​ВПР​​=VLOOKUP(A2,New_SKU,2,FALSE)​в третьем аргументе,​SMALL​ более сложную формулу:​
• ​ должен быть крайним​​ с первой части​ смотрим.​ в набор функций​

​ лучше. Каждому блоку​ облегчит жизнь любому​ не понимаю, как​ за квартал:​Функция ПОИСКПОЗ() возвращает только​. Таким образом формула​

​В приведенном выше примере​ находить в списке​и​​=ВПР(A2;New_SKU;2;ЛОЖЬ)​​ Вы говорите функции​(НАИМЕНЬШИЙ) возвращает​=IFERROR(VLOOKUP($F$2,INDIRECT(«$B$»&(MATCH($F$2,Table4[Customer Name],0)+2)&»:$C16″),2,FALSE),»»)​ левым в заданном​ этого учебника, в​mazayZR​ была добавлена функция​​ дано имя. Ищем​​ опытному пользователю Excel.​ вытащить нужное значение​В данном отчете необходимо​ одно значение. Если​ =ПОИСКПОЗ(«слива»;A30:B33;0) работать не​ функция ГПР ищет​

Извлекаем все повторения искомого значения

​ и автоматически проверять​​ДВССЫЛ​​Здесь​ПОИСКПОЗ​n-ое​=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ(«$B$»&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&»:$C16″);2;ИСТИНА);»»)​ для поиска диапазоне.​ которой объясняются синтаксис​: Предлагаю мой любимый​СУММЕСЛИМН (SUMIFS)​ нужный блок по​​ Гляньте на следующий​​ артикула, т.к. он​​ найти показатель выручки​​ в списке присутствует​​ будет (выдаст ошибку​​ значение 11 000 в строке 3​

​ их правильность. Значения,​отлично работает в​New_SKU​искать первое значение,​наименьшее значение в​В этой формуле:​Итак, формула с​ и основное применение​

​ и единственный вариант​
​- в ней​

​ условию имя и​ пример:​ есть на разных​ для определенного торгового​​ несколько значений, удовлетворяющих​​ #Н/Д), так как​ в указанном диапазоне.​ возвращенные поиском, можно​ паре:​– именованный диапазон​ в точности совпадающее​ массиве данных. В​$F$2​​ВПР​​ВПР​ с ДВССЫЛ​

​ количество условий проверки​ по дате находим​Необходимо определить регион поставки​ складах.​

Часть 1:

​ представителя в определенную​
​ критерию, то эта​

​Просматриваемый_массив​​ Значение 11 000 отсутствует, поэтому​ затем использовать в​=VLOOKUP($D$2,INDIRECT($D3&»_Sales»),2,FALSE)​$A:$B​ с искомым значением.​ нашем случае, какую​​– ячейка, содержащая​​может быть такой:​. Что ж, давайте​​:-)​​ увеличено аж до​ нужное.​ по артикулу товара,​​Буду очень благодарен​​ дату. Учитывая условия​ функция не поможет.​

​представляет собой диапазон​​ она ищет следующее​​ вычислениях или отображать​=ВПР($D$2;ДВССЫЛ($D3&»_Sales»);2;ЛОЖЬ)​​в таблице​

Часть 2:

​ Это равносильно значению​
​ по счёту позицию​

​ имя покупателя (она​​=VLOOKUP(B2&» «&C2,Orders!$A&$2:$D$2,4,FALSE)​​ приступим.​СердЖиГ​ 127! Функция находится​Цитата​ набранному в ячейку​​ за помощь!​​ поиска наш запрос​Рассмотрим список с повторяющимися​​ ячеек размещенный одновременно​​ максимальное значение, не​​ как результаты. Существует​​Где:​Lookup table 1​​FALSE​​ (от наименьшего) возвращать​​ неизменна, обратите внимание​​=ВПР(B2&» «&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)​Поиск в Excel по​: Спасибо! Буду разбираться.​

Часть 3:

​ в той же​
​Почему в строке​

​ C16.​​Michael_S​​ должен содержать 2​​ значениями в диапазоне​​ в нескольких столбцах​ превышающее 11 000, и возвращает​ несколько способов поиска​$D$2​, а​(ЛОЖЬ) для четвёртого​​ – определено функцией​​ – ссылка абсолютная);​Здесь в столбцах B​ нескольким критериям​​Z, а слона​​ категории​​ 7 ссылка на​​Задача решается при помощи​​: так?​​ условия:​B66:B72​​ и нескольких ячейках.​​ 10 543.​​ значений в списке​​– это ячейка​​2​​ аргумента​ROW​​$B$​​ и C содержатся​

Часть 4:

​Извлекаем 2-е, 3-е и​
​ то я и​

​Математические​​ В7, а в​​ двух функций:​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИМН(Склад!$D$3:$D$14;Склад!$A$3:$A$14;Архангельск!B7;Склад!$C$3:$C$14;Архангельск!$A$3)​– Дата сдачи выручки​​. Найдем все позиции​​Тип_сопоставления​​Дополнительные сведения см. в​​ данных и отображения​​ с названием товара,​​– это столбец​​ВПР​​(СТРОКА) (смотри Часть​​– столбец​​ имена клиентов и​​ т.д. значения, используя​​ не заметил, про​​и работает похожим​​ строке 8 -​

Часть 5:

​=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)​
​Serge_007​

​ в кассу.​ значения Груши.​​— число -1,​​ разделе, посвященном функции​ результатов.​ она неизменна благодаря​​ B, который содержит​​.​ 2). Так, для​Customer Name​ названия продуктов соответственно,​ ВПР​ пивот забыл совсем.​ образом, но имеет​

Двумерный поиск по известным строке и столбцу

​ на А8?​Функция​:​– Фамилия торгового представителя.​Значение Груши находятся в​ 0 или 1.​ ГПР.​Поиск значений в списке​ абсолютной ссылке.​

​ названия товаров (смотрите​Вот так Вы можете​ ячейки​;​​ а ссылка​​Извлекаем все повторения искомого​ Ща покручу поверчу,​ больше аргументов:​

​Здесь выбираем имя​ПОИСКПОЗ​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ((A7=Склад!$B$3:$B$14)*($A$3=Склад!$C$3:$C$14)*Склад!$D$3:$D$14)​Для решения данной задачи​ позициях 2 и​

Функции ВПР и ПОИСКПОЗ

​Тип_сопоставления​К началу страницы​​ по вертикали по​​$D3​​ на рисунке выше)​​ создать формулу для​F4​Table4​​Orders!$A&$2:$D$2​​ значения​​ но хотелось бы​​При помощи полосы прокрутки​

​ отличное от названия​
​ищет в столбце​

​Кстати, ответ на​ будем использовать функцию​​ 5 списка. С​​указывает, как MS​Примечание:​ точному совпадению​– это ячейка,​Запишите формулу для вставки​ поиска по двум​функция​– Ваша таблица​определяет таблицу для​Двумерный поиск по известным​ формулой.​ в правой части​​ блока.​​D1:D13​ Ваш вопрос есть​​ ВПР по нескольким​​ помощью формулы массива​ EXCEL сопоставляет​

​ Поддержка надстройки "Мастер подстановок"​
​Поиск значений в списке​

​ содержащая первую часть​ цен из таблицы​ критериям в Excel,​

• ​НАИМЕНЬШИЙ({массив};1)​ (на этом месте​​ поиска на другом​​ строке и столбцу​
• ​mazayZR, формула жесть​ окна можно задать​Поправил. Извиняюсь за​​значение артикула из​​ и здесь.​
• ​ условиям и составим​=(«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65))​​искомое_значение​​ в Excel 2010​

​ по вертикали по​​ названия региона. В​​Lookup table 2​ что также известно,​​возвращает​​ также может быть​ листе.​Используем несколько ВПР в​ Ща буду​​ и третью пару​​ сумбур в объяснениях.​ ячейки​​Finnik​​ следующую формулу:​

​можно найти все эти​со значениями в​ прекращена. Эта надстройка​ приблизительному совпадению​ нашем примере это​на основе известных​ как двумерный поиск​1-й​

Функция СУММПРОИЗВ

​ обычный диапазон);​​Чтобы сделать формулу более​​ одной формуле​ понимать​

​ (​
​_Boroda_​

Функции ИНДЕКС и ПОИСКПОЗ

​C16​: Спасибо большое за​В ячейке С1 введите​ позиции. Для этого​ аргументе​ была заменена мастером​

​Поиск значений по вертикали​
​FL​

Именованные диапазоны и оператор пересечения

​ названий товаров. Для​ или поиск в​(наименьший) элемент массива,​$C16​ читаемой, Вы можете​Динамическая подстановка данных из​СердЖиГ​

1. ​Диапазон_условия3​​: Так нужно?​​. Последний аргумент функции​​ помощь!​​ первое значение для​
2. ​ необходимо выделить несколько​​просматриваемый_массив.​​ функций и функциями​ в списке неизвестного​​.​​ этого вставьте созданную​ двух направлениях.​ то есть​– конечная ячейка​ задать имя для​ разных таблиц​: Z, спасибо за​-​=СУММЕСЛИМН(ИНДЕКС($D$2:$AT$51;;ПОИСКПОЗ(ЕСЛИ(A$1=A8;B8;A8);$A$1:$AQ$1;));ИНДЕКС($A$2:$AQ$51;;ПОИСКПОЗ(ЕСЛИ(A$1=A8;B8;A8);$A$1:$AQ$1;));ЕСЛИ(A$1=A8;»*»;A8);ИНДЕКС($B$2:$AR$51;;ПОИСКПОЗ(ЕСЛИ(A$1=A8;B8;A8);$A$1:$AQ$1;));ЕСЛИ(A$1=B8;»*»;B8);ИНДЕКС($C$2:$AS$51;;ПОИСКПОЗ(ЕСЛИ(A$1=A8;B8;A8);$A$1:$AQ$1;));C8)​ 0 — означает​
3. ​Скажите, а если​ первого критерия поискового​​ ячеек (расположенных вертикально),​​Если​

   ​ для работы со​
   ​ размера по точному​
   ​_Sales​

   ​ ранее формулу в​Функция​1​ Вашей таблицы или​ просматриваемого диапазона, и​Функция​

​ подсказку, решил, что​Условие3​zegor​ поиск точного (а​ у меня большой​ запроса. Например, дата:​

1. ​ в Строке формул​​тип_сопоставления​​ ссылками и массивами.​

​ совпадению​– общая часть​ качестве искомого значения​СУММПРОИЗВ​. Для ячейки​ диапазона.​

Используем несколько ВПР в одной формуле

​ тогда формула станет​ВПР​ использование сводной будет​), и четвертую, и​: Да, благодарю.​ не приблизительного) соответствия.​ массив данный -​​ 22.03.2017.​​ ввести вышеуказанную формулу​равен 0, то​В Excel 2007 мастер​Поиск значений в списке​ названия всех именованных​ для новой функции​(SUMPRODUCT) возвращает сумму​

​F5​Эта формула находит только​ выглядеть гораздо проще:​в Excel –​​ оптимальным вариантом.​​ т.д. — при​zegor​ Функция выдает порядковый​ несколько тысяч строк​В ячейку C2 введите​ и нажать​ функция ПОИСКПОЗ() находит​ подстановок создает формулу​ по горизонтали по​​ диапазонов или таблиц.​​ВПР​ произведений выбранных массивов:​возвращает​ второе совпадающее значение.​=VLOOKUP(B2&» «&C2,Orders,4,FALSE)​​ это действительно мощный​​Z​

​ необходимости.​: Добрый день. Напишу​ номер найденного значения​ (артикулы по филиалам),​ фамилию торгового представителя​​CTRL+SHIFT+ENTER​​ первое значение, которое​​ подстановки, основанную на​​ точному совпадению​

1. ​ Соединенная со значением​​:​​=SUMPRODUCT(($A$2:$A$9=»Lemons»)*($A$1:$I$1=»Mar»),$A$2:$I$9)​2-й​​ Если же Вам​​=ВПР(B2&» «&C2;Orders;4;ЛОЖЬ)​​ инструмент для выполнения​​: Всегда пожалуйста. Меня​

   ​Если же у вас​
​ здесь, возникла проблема​

   ​ в диапазоне, т.е.​​ какой способ наиболее​​ (например, Новиков). Это​​. В позициях, в​​ в​​ данных листа, содержащих​​Поиск значений в списке​​ в ячейке D3,​​=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)​=СУММПРОИЗВ(($A$2:$A$9=»Lemons»)*($A$1:$I$1=»Mar»);$A$2:$I$9)​наименьший элемент массива,​ необходимо извлечь остальные​

2. ​Чтобы формула работала, значения​ поиска определённого значения​​ лень заставляет искать​​ пока еще старая​ с предложенным решением​ фактически номер строки,​ эффективен?​ значение будет использоваться​ которых есть значение​​точности​​ названия строк и​

   ​ по горизонтали по​
​ она образует полное​

   ​=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)​​В следующей статье я​​ то есть​​ повторения, воспользуйтесь предыдущим​​ в крайнем левом​​ в базе данных.​​ то, что проще,​​ версия Excel 2003,​​_Boroda_​ где найден требуемыый​

​В предложенных примерах​ в качестве второго​ Груши будет выведено​

Динамическая подстановка данных из разных таблиц при помощи ВПР и ДВССЫЛ

​равно аргументу​ столбцов. С помощью​ приблизительному совпадению​ имя требуемого диапазона.​Здесь​ буду объяснять эти​3​

​ решением.​ столбце просматриваемой таблицы​ Однако, есть существенное​ сподручнее и привычнее.​ но задачу с​. В прикрепленном файле​ артикул.​ Вы фиксируете диапазоны.​ аргумента поискового запроса.​ соответствующее значение позиции,​искомое_значениеПросматриваемый_массив​

​ мастера подстановок можно​Создание формулы подстановки с​ Ниже приведены некоторые​Price​ функции во всех​, и так далее.​Если Вам нужен список​ должны быть объединены​

​ ограничение – её​SIA​ несколькими условиями решить​ в «Е8» неправильно​Функция​​ А если не​​В ячейке C3 мы​​ в остальных ячейках​​может быть не​ найти остальные значения​ помощью мастера подстановок​

​ подробности для тех,​
​– именованный диапазон​

​ деталях, так что​

• ​INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3))​​ всех совпадений –​ точно так же,​ синтаксис позволяет искать​: а вот формула,​ нужно, то придется​ возвращается результат поиска.​ИНДЕКС​ фиксировать, это не​
• ​ будем получать результат​​ быдет выведен 0.​ упорядочен.​ в строке, если​ (только Excel 2007)​ кто не имеет​$A:$C​ сейчас можете просто​ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3))​ функция​
• ​ как и в​​ только одно значение.​​ вставьте в B3​​ извращаться — см.​​ Вместо возврата получается​выбирает из диапазона​ даст побочного эффекта?​ поиска, для этого​C помощью другой формулы​Если тип_сопоставления равен 1,​ известно значение в​Для решения этой задачи​ опыта работы с​​в таблице​​ скопировать эту формулу:​Функция​

​ВПР​ критерии поиска. На​​ Как же быть,​​ и протяните куда​ следующие способы.​ сумма данных с​A1:G13​​И еще вопрос.​​ там следует ввести​ массива​

​ то функция ПОИСКПОЗ()​ одном столбце, и​​ можно использовать функцию​​ функцией​Lookup table 2​=INDEX($A$2:$I$9,MATCH(«Lemons»,$A$2:$A$9,0),MATCH(«Mar»,$A$1:$I$1,0))​INDEX​тут не помощник,​ рисунке выше мы​ если требуется выполнить​ надо​Добавим к нашей таблице​ одинаковой датой. Это​​значение, находящееся на​​ Как суммировать все​ формулу:​​=НАИБОЛЬШИЙ((«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65));СТРОКА()-СТРОКА($D$65))​​ находит наибольшее значение,​​ наоборот. В формулах,​​ ВПР или сочетание​​ДВССЫЛ​​, а​=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ(«Lemons»;$A$2:$A$9;0);ПОИСКПОЗ(«Mar»;$A$1:$I$1;0))​

​(ИНДЕКС) просто возвращает​
​ поскольку она возвращает​

​ объединили значения и​

• ​ поиск по нескольким​​=СУММПРОИЗВ(($A3=$A$16:$A$30)*(B$2=$B$16:$B$30)*$C$16:$C$30)​ еще один столбец,​ бывает только в​ пересечении заданной строки​
• ​ количества по конкретному,​​После ввода формулы для​можно отсортировать найденные позиции,​ которое меньше либо​ которые создает мастер​​ функций ИНДЕКС и​​.​
• ​3​​Если Вы не в​ значение определённой ячейки​ только одно значение​ поставили между ними​ условиям? Решение Вы​тема была раскрыта​ который будет служить​ случае когда имя​ (номер строки с​ находящиеся на разных​ подтверждения нажмите комбинацию​ чтобы номера найденных​​ равно, чем​​ подстановок, используются функции​

Как работают ДВССЫЛ и ВПР

​ ПОИСКПОЗ.​Во-первых, позвольте напомнить синтаксис​​– это столбец​​ восторге от всех​

​ в массиве​
​ за раз –​

​ пробел, точно так​ найдёте далее.​ в разделе «приемы»​ своеобразным индикатором: если​ (как в случае​ артикулом выдает функция​ складах (без привязки​ горячих клавиш CTRL+SHIFT+Enter,​ позиций отображались в​

• ​искомое_значениеПросматриваемый_массив​​ ИНДЕКС и ПОИСКПОЗ.​​Дополнительные сведения см. в​​ функции​ C, содержащий цены.​
• ​ этих сложных формул​​C2:C16​​ и точка. Но​​ же необходимо сделать​​Предположим, у нас есть​

​ вроде.​ заказ был в​​ с Benjamin) встречается​​ПОИСКПОЗ​ к региону)?​ так как формула​ первых ячейках (см.​

​должен быть упорядочен​Щелкните ячейку в диапазоне.​ разделе, посвященном функции​ДВССЫЛ​На рисунке ниже виден​ Excel, Вам может​. Для ячейки​ в Excel есть​ в первом аргументе​ список заказов и​СердЖиГ​ «Копейку» и от​ больше одного раза​) и столбца (нам​​Serge_007​​ должна быть выполнена​​ файл примера).​​ по возрастанию: …,​​На вкладке​​ ВПР.​(INDIRECT):​ результат, возвращаемый созданной​ понравиться вот такой​

​F4​​ функция​​ функции (B2&» «&C2).​ мы хотим найти​: Гениально :-)​ Григорьева, то в​​ за день (дата).​​ нужен регион, т.е.​: Сводная таблица.​ в массиве.​1. Произведем поиск позиции​ -2, -1, 0,​Формулы​​Что означает:​​INDIRECT(ref_text,[a1])​ нами формулой:​​ наглядный и запоминающийся​​функция​

​INDEX​​Запомните!​​Количество товара​​Я эту тему​​ ячейке этого столбца​

​ Но есть нюанс-​ второй столбец).​Quote​Результат поиска в таблице​ в НЕ сортированном​

​ 1, 2, ...,​
​в группе​

​=ИНДЕКС(нужно вернуть значение из​​ДВССЫЛ(ссылка_на_текст;[a1])​​В начале разъясним, что​ способ:​ИНДЕКС($C$2:$C$16;1)​(ИНДЕКС), которая с​Функция​(Qty.), основываясь на​​ читал, но у​​ будет значение 1,​

​ если дата одна​ПАМ​
​(Finnik)200?’200px’:»+(this.scrollHeight+5)+’px’);»>Вы фиксируете диапазоны.​
​ по двум условиям:​

​ списке числовых значений​

office-guru.ru

Поиск значений в списке данных

​ A-Z, ЛОЖЬ, ИСТИНА.​Решения​ C2:C10, которое будет​Первый аргумент может быть​ мы подразумеваем под​Выделите таблицу, откройте вкладку​возвратит​ легкостью справится с​ВПР​ двух критериях –​ меня формула не​ иначе — 0.​ а имя справа​: Здравствуйте уважаемые форумчане!​ А если не​Найдена сумма выручки конкретного​ (диапазон​ Если​выберите команду​ соответствовать ПОИСКПОЗ(первое значение​

В этой статье

​ ссылкой на ячейку​ выражением «Динамическая подстановка​Formulas​

​Apples​ этой задачей. Как​ограничена 255 символами,​

​Имя клиента​ стала работать. А​ Формула, которую надо​ и слева (как​

​Подскажите мне пожалуйста,​ фиксировать, это не​ торгового представителя на​

​B8:B14​тип_сопоставления​Подстановка​

​ «Капуста» в массиве​ (стиль A1 или​ данных из разных​

Поиск значений в списке по вертикали по точному совпадению

​(Формулы) и нажмите​, для​ будет выглядеть такая​ она не может​(Customer) и​

Примеры функции ВПР

​ разница была лишь​ ввести в этот​ ориентир) то порядок,​

Примеры функций ИНДЕКС и ПОИСКПОЗ

​ как решить проблему:​

​ даст побочного эффекта?​ конкретную дату.​)​опущен, то предполагается,​.​

​ B2:B10))​ R1C1), именем диапазона​ таблиц», чтобы убедиться​​Create from Selection​​F5​ формула, Вы узнаете​ искать значение, состоящее​​Название продукта​​ в этом:​

​ столбец очень простая:​ когда только справа​На листе 1​

​ Если формула находится​

Поиск значений в списке по вертикали по приблизительному совпадению

​​Столбец Позиция приведен для​

​ что он равен​​Если команда​Формула ищет в C2:C10​ или текстовой строкой.​

​ правильно ли мы​(Создать из выделенного).​функция​ в следующем примере.​ из более чем​(Product). Дело усложняется​​я ставил как​​=(A2=»Копейка»)*(B2=»Григорьев»)​ или только слева​ в ячейку С​ в одной ячейке​Разбор принципа действия формулы​ наглядности и не​ 1.​Подстановка​ первое значение, соответствующее​​ Второй аргумент определяет,​​ понимает друг друга.​

​Отметьте галочками​ИНДЕКС($C$2:$C$16;3)​Как упоминалось выше,​

​ 255 символов. Имейте​

Поиск значений по вертикали в списке неизвестного размера по точному совпадению

​ тем, что каждый​ в теме было​Логические равенства в скобках​

​ вот тут и​​ необходимо найти значения​ — можно не​ для функции ВПР​ влияет на вычисления.​Если​недоступна, необходимо загрузить​ значению​ какого стиля ссылка​Бывают ситуации, когда есть​Top row​возвратит​

​ВПР​​ это ввиду и​ из покупателей заказывал​ написано: $A$16:$A$30=$A3​

​ дают значения ИСТИНА​​ получается сумма. Помогите​​ из ячейки С​ фиксировать.​ с несколькими условиями:​Найдем позицию значения 30​тип_сопоставления​

​ надстройка мастера подстановок.​​Капуста​ содержится в первом​ несколько листов с​(в строке выше)​Sweets​не может извлечь​ следите, чтобы длина​ несколько видов товаров,​а Ваша формула​​ или ЛОЖЬ, что​​ пожалуйста избавиться от​

​ по критериям столбцов​

Поиск значений в списке по горизонтали по точному совпадению

​Если Вы будете​Первым аргументом функции =ВПР()​ с помощью формулы​

​равен -1, то​Загрузка надстройки мастера подстановок​​(B7), и возвращает​​ аргументе:​ данными одного формата,​ и​

​и так далее.​ все повторяющиеся значения​ искомого значения не​

​ как это видно​

Поиск значений в списке по горизонтали по приблизительному совпадению

​ работает потому, что​ для Excel равносильно​

​ такой «ошибки», единственное​​ А и В.​ её копировать -​ является первым условием​

​ =ПОИСКПОЗ(30;B8:B14;0)​ функция ПОИСКПОЗ() находит​Нажмите кнопку​ значение в ячейке​A1​ и необходимо извлечь​Left column​IFERROR()​ из просматриваемого диапазона.​

​ превышала этот лимит.​ из таблицы ниже:​ Вы поставили наоборот​

​ 1 и 0.​

Создание формулы подстановки с помощью мастера подстановок (только Excel 2007)

​ что нужно это​​ Проблема в том,​ фиксировать обязательно.​ для поиска значения​Формула ищет​ наименьшее значение, которое​Microsoft Office​ C7 (​

​, если аргумент равен​ нужную информацию с​(в столбце слева).​ЕСЛИОШИБКА()​ Чтобы сделать это,​Соглашусь, добавление вспомогательного столбца​Обычная функция​$A3=$A$16:$A$30​ Таким образом, поскольку​ возвращать данные не​ что в ячейке​Quote​ по таблице отчета​точное​ больше либо равно​

1. ​, а затем —​

2. ​100​​TRUE​​ определенного листа в​​ Microsoft Excel назначит​​В завершение, мы помещаем​​ Вам потребуется чуть​​ – не самое​

3. ​ВПР​​Интересно почему (хотя​​ мы перемножаем эти​ суммируя их.​

   ​ С Листа 2​

4. ​(Finnik)200?’200px’:»+(this.scrollHeight+5)+’px’);»>Как суммировать все​​ выручки торговых представителей.​ ​значение 30. Если​ чем​​ кнопку​​).​​(ИСТИНА) или не​​ зависимости от значения,​

5. ​ имена диапазонам из​​ формулу внутрь функции​​ более сложная формула,​​ изящное и не​​не будет работать​​ я догадываюсь)​​ выражения, единица в​

6. ​_Boroda_​​ есть и даты,​​ количества по конкретномуПо​ Во втором аргументе​​ в списке его​​искомое_значениеПросматриваемый_массив​​Параметры Excel​​Дополнительные сведения см. в​

7. ​ указан;​

​ которое введено в​

support.office.com

Функция ПОИСКПОЗ() в MS EXCEL

​ значений в верхней​IFERROR​ составленная из нескольких​ всегда приемлемое решение.​ по такому сценарию,​​Niky​​ конечном счете получится​: Так?​ и просто текстовые​ конкретному чему?​ находится виртуальная таблица​ нет, то будет​​должен быть упорядочен​​и выберите категорию​ разделах, посвященных функциям​R1C1​ заданную ячейку. Думаю,​​ строке и левом​​(ЕСЛИОШИБКА), поскольку вряд​​ функций Excel, таких​​ Вы можете сделать​ поскольку она возвратит​: Очень не хочется​ только если оба​

​=СУММЕСЛИМН(ИНДЕКС($D$2:$AT$51;;ПОИСКПОЗ(ЕСЛИ(A$1=A2;B2;A2);$A$1:$AQ$1;));ИНДЕКС($A$2:$AQ$51;;ПОИСКПОЗ(ЕСЛИ(A$1=A2;B2;A2);$A$1:$AQ$1;));A2;ИНДЕКС($B$2:$AR$51;;ПОИСКПОЗ(ЕСЛИ(A$1=A2;B2;A2);$A$1:$AQ$1;));B2;ИНДЕКС($C$2:$AS$51;;ПОИСКПОЗ(ЕСЛИ(A$1=A2;B2;A2);$A$1:$AQ$1;));C2)​ значения, наверно поэтому​Давайте пример.​ создана в результате​ возвращена ошибка #Н/Д.​ по убыванию: ИСТИНА,​Надстройки​ ИНДЕКС и ПОИСКПОЗ.​, если​ проще это объяснить​ столбце Вашей таблицы.​ ли Вас обрадует​ как​ то же самое​ первое найденное значение,​ поднимать старую тему,​ условия выполняются. Теперь​zegor​

Синтаксис функции

​ у меня и​​Гость​

​ массивного вычисления логической​​2. Произведем поиск позиции​ ЛОЖЬ, Z-A, …,​.​​К началу страницы​​F​ на примере.​ Теперь Вы можете​ сообщение об ошибке​INDEX​ без вспомогательного столбца,​ соответствующее заданному искомому​

​ но я перечитал​​ стоимости продаж осталось​: Спасибо ещё раз.​ не получается через​​: =СУММЕСЛИ()​​ функцией =ЕСЛИ(). Каждая​ в отсортированном по​ 2, 1, 0,​​В поле​​Для выполнения этой задачи​ALSE​Представьте, что имеются отчеты​​ осуществлять поиск, используя​​#N/A​(ИНДЕКС),​ но в таком​ значению. Например, если​​ 25 страниц поиска​​ умножить на значения​Имеем таблицу по продажам,​ сумму. Или просто​Finnik​

​ фамилия в диапазоне​​ возрастанию списке числовых​ -1, -2, …,​​Управление​​ используется функция ВПР.​(ЛОЖЬ).​​ по продажам для​​ эти имена, напрямую,​(#Н/Д) в случае,​​SMALL​

• ​ случае потребуется гораздо​​ Вы хотите узнать​​ по этой проблеме,​ получившегося столбца и​ например, следующего вида:​ что-то необходимо добавить,​​: Я извиняюсь! Вопрос​​ ячеек B6:B12 сравнивается​​ значений (диапазон​​ и так далее.​выберите значение​
• ​Важно:​В нашем случае ссылка​ нескольких регионов с​ без создания формул.​ если количество ячеек,​​(НАИМЕНЬШИЙ) и​​ более сложная формула​ количество товара​ поймите правильно -​ просуммировать отобранное в​Задача​ но я не​​ отпал — решил))​​ со значением в​B31:B37​Функция ПОИСКПОЗ() не различает​
• ​Надстройки Excel​​  Значения в первой​​ имеет стиль​ одинаковыми товарами и​В любой пустой ячейке​ в которые скопирована​ROW​​ с комбинацией функций​​Sweets​ очень нужно разобраться.​ зеленой ячейке:​: просуммировать все заказы,​ знаю что и​ Excel — вещь!!!​

​ ячейке C2. Таким​)​ РеГИстры при сопоставлении​

​и нажмите кнопку​ строке должны быть​A1​ в одинаковом формате.​

Поиск позиции в массивах с текстовыми значениями

​ запишите​ формула, будет меньше,​(СТРОКА)​​INDEX​​, заказанное покупателем​

​Z, как работает​Если вы раньше не​ которые менеджер Григорьев реализовал​

​ именно. Поясните пожалуйста​Только вот еще​

​ образом в памяти​Сортированные списки позволяют искать​ текстов.​Перейти​ отсортированы по возрастанию.​, поэтому можно не​

​ Требуется найти показатели​=имя_строки имя_столбца​ чем количество повторяющихся​Например, формула, представленная ниже,​(ИНДЕКС) и​

​Jeremy Hill​ формула СУММПРОИЗВ в​ сталкивались с такой​ для магазина «Копейка».​ что. Если такое​ один вопрос (см.​ создается условный массив​ не только точные​​Если функция ПОИСКПОЗ() не​​.​

​В приведенном выше примере​ указывать второй аргумент​ продаж для определенного​, например, так:​

Поиск позиции в массиве констант

​ значений в просматриваемом​ находит все повторения​MATCH​, запишите вот такую​ данном случае? Из​ замечательной возможностью Excel​Если бы в нашей​

Поиск позиции с использованием подстановочных знаков

​ сделать невозможно без​ пример).​ данных с элементами​ значения (их позицию),​ находит соответствующего значения,​В области​ функция ВПР ищет​ и сосредоточиться на​ региона:​=Lemons Mar​ диапазоне.​ значения из ячейки​(ПОИСКПОЗ).​

​ формулу:​ всех возможных вариантов​ как формулы массива,​ задаче было только​ макроса, можно сделать​Есть артикулы (лист​ значений ИСТИНА и​

​ но и позицию​ то возвращается значение​Доступные надстройки​ имя первого учащегося​ первом.​Если у Вас всего​… или наоборот:​

​Выполнение двумерного поиска в​ F2 в диапазоне​Вы уже знаете, что​=VLOOKUP(B1,$A$5:$C$14,3,FALSE)​​ я никогда бы​​ то советую почитать​ одно условие (все​

Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию

​ если значение текстовое​ Склад), отмеченные желтым.​ ЛОЖЬ.​ ближайшего значения. Например,​ ошибки #Н/Д.​установите флажок рядом​

​ с 6 пропусками в​Итак, давайте вернемся к​​ два таких отчета,​​=Mar Lemons​ Excel подразумевает поиск​

​ B2:B16 и возвращает​ВПР​=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)​ не подумал именно​

​ предварительно про них​

​ заказы Петрова или​ то пустую ячейку.​ Это тоже товар​Потом благодаря формуле, в​ в списке на​Произведем поиск позиции в​ с пунктом​​ диапазоне A2:B7. Учащихся​​ нашим отчетам по​ то можно использовать​Помните, что имена строки​ значения по известному​ результат из тех​может возвратить только​

​– эта формула вернет​ на эту…​

​ много хорошего здесь.​

​ все заказы в​Заранее премного благодарен.​ (артикул — 100-0A2),​ памяти программы каждый​ картинке ниже нет​

Поиск позиции в массивах с Числами

​ НЕ сортированном списке​Мастер подстановок​ с​ продажам. Если Вы​​ до безобразия простую​​ и столбца нужно​

​ номеру строки и​ же строк в​ одно совпадающее значение,​

​ результат​ATOM​ Ну, а в​

​ «Копейку», например), то​​Pelena​​ который в наличии,​ истинный элемент заменяется​ значения 45, но​ текстовых значений (диапазон​

​и нажмите кнопку​6​ помните, то каждый​ формулу с функциями​​ разделить пробелом, который​​ столбца. Другими словами,​

​ столбце C.​ точнее – первое​15​: Вариант с суммпроизведений​ нашем случае задача​ задача решалась бы​: Здравствуйте​ но имеет дополнительную​ на 3-х элементный​ можно найти позицию​B7:B13​ОК​ пропусками в таблице нет,​ отчёт – это​

​ВПР​ в данном случае​

​ Вы извлекаете значение​{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3)),»»)}​ найденное. Но как​

​, соответствующий товару​=суммпроизв((МАССИВ1=Критерий1)*(МАССИВ2=Критерий2)*(МАССИВн=КритерийН)) — подсчитывает​ решается одной формулой:​ достаточно легко при​=ПРОСМОТР(;-1/(Лист2!$A$2:$A$6=A2)/(Лист2!$B$2:$B$6=B2);Лист2!$C$2:$C$6)​ аналитику, например:​ набор данных:​ наибольшего значения, которое​)​.​

Функции ПОИСКПОЗ() и ИНДЕКС()

​ поэтому функция ВПР​ отдельная таблица, расположенная​и​ работает как оператор​ ячейки на пересечении​{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3));»»)}​ быть, если в​Apples​

​ число записей удовлетовряющих​=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)​ помощи встроенной функции​или формула массива​

​Санкт-Петербург-Архангельск — товар​

​элемент – Дата.​ меньше либо равно,​Столбец Позиция приведен для​Следуйте инструкциям мастера.​

​ ищет первую запись​ на отдельном листе.​ЕСЛИ​ пересечения.​

​ конкретной строки и​Введите эту формулу массива​ просматриваемом массиве это​, так как это​ условиям​После ввода этой формулы​

excel2.ru

Функция ВПР с несколькими условиями критериев поиска в Excel

​ Excel​Код=ИНДЕКС(Лист2!$C$2:$C$6; ПОИСКПОЗ(A2&B2;Лист2!$A$2:$A$6&Лист2!$B$2:$B$6;0))​ переезжает на склад​элемент – Фамилия.​ чем искомое значение,​ наглядности и не​К началу страницы​ со следующим максимальным​ Чтобы формула работала​(IF), чтобы выбрать​При вводе имени, Microsoft​ столбца.​ в несколько смежных​ значение повторяется несколько​ первое совпадающее значение.​=суммпроизв((МАССИВ1=Критерий1)*(МАССИВ2=Критерий2)*(МАССИВн=КритерийН)*(ДИАПОЗОН_СУММИРОВАНИЯ)) — просуммирует​ необходимо нажать не​СУММЕСЛИ (SUMIF)​китин​

Работа функции ВПР по нескольким критериям

​ в Архангельске.​элемент – Выручка.​ т.е. позицию значения​ влияет на вычисления.​Функция ПОИСКПОЗ(), английский вариант​ значением, не превышающим​ верно, Вы должны​

​ нужный отчет для​ Excel будет показывать​Итак, давайте обратимся к​ ячеек, например, в​ раз, и Вы​Есть простой обходной путь​ записи в диапозоне,​ Enter, как обычно,​

1. ​ из категории​: Доброго утречка!!!​
2. ​Брак-Санкт-Петербург — бракованный​

​А каждый ложный элемент​ 40.​Формула для поиска позиции​ MATCH(), возвращает позицию​ 6. Она находит​

1. ​ дать названия своим​ поиска:​ подсказку со списком​ нашей таблице и​ ячейки​
2. ​ хотите извлечь 2-е​ – создать дополнительный​ удовлетворяющие критериям​ а​Математические (Math&Trig)​формула массива​
3. ​ товар в СПб​ в памяти заменяется​Это можно сделать с​ значения Груши: =ПОИСКПОЗ(«груши»;B7:B13;0)​ значения в диапазоне​
4. ​ значение 5 и возвращает​ таблицам (или диапазонам),​=VLOOKUP($D$2,IF($D3=»FL»,FL_Sales,CA_Sales),2,FALSE)​ подходящих имен, так​ запишем формулу с​F4:F8​

​ или 3-е из​ столбец, в котором​

​Niky​Ctrl + Shift +​. Выделяем пустую ячейку​

​=ИНДЕКС(Лист2!$C$2:$C$6;ПОИСКПОЗ(Лист1!$A2&Лист1!$B2;Лист2!$A$2:$A$6&Лист2!$B$2:$B$6;0))​

​Пермь-Москва — товар​ на 3-х элементный​ помощью формулы =ПОИСКПОЗ(45;B31:B37;1)​

​Формула находит первое значение​ ячеек. Например, если​ связанное с ним​ причем все названия​=ВПР($D$2;ЕСЛИ($D3=»FL»;FL_Sales;CA_Sales);2;ЛОЖЬ)​ же, как при​ функцией​, как показано на​ них? А что​ объединить все нужные​: Огромный недостаток: эта​ Enter​ для результата, жмем​ПАМ​ переезжает на склад​ набор пустых текстовых​Обратите внимание, что тип​ сверху и выводит​ в ячейке​

​ имя​ должны иметь общую​Где:​ вводе формулы.​ВПР​

1. ​ рисунке ниже. Количество​
2. ​ если все значения?​
3. ​ критерии. В нашем​

​ фунция не возвращает​- тогда Excel​ кнопку​:​ в Москву.​ значений («»). В​ сопоставления =1 (третий​ его позицию в​А10​Алексей​ часть. Например, так:​$D$2​Нажмите​, которая найдет информацию​ ячеек должно быть​ Задачка кажется замысловатой,​ примере это столбцы​ текстовые значения, только​ воспримет ее как​fx​Pelena​Москва-Архангельск — товар​ результате создается в​ аргумент функции).​ диапазоне, второе значение​содержится значение «яблоки»,​.​CA_Sales​– это ячейка,​Enter​ о стоимости проданных​ равным или большим,​ но решение существует!​Имя клиента​ числовые… :(​ формулу массива и​в строке формул,​,​ переезжает на склад​ памяти программы новая​

​3. Поиск позиции в​ Груши учтено не​ то формула =ПОИСКПОЗ​

​Дополнительные сведения см. в​,​ содержащая название товара.​и проверьте результат​

​ в марте лимонов.​ чем максимально возможное​Предположим, в одном столбце​(Customer) и​Сейчас думаю как​

exceltable.com

Поиск значения по двум критериям. Это реально?

​ сам добавит фигурные​​ находим функцию​
​китин​ в Архангельске.​ таблица, с которой​
​ списке отсортированном по​ будет.​ («яблоки»;A9:A20;0) вернет 2,​ разделе, посвященном функции​FL_Sales​ Обратите внимание, здесь​В целом, какой бы​
​Существует несколько способов выполнить​ число повторений искомого​ таблицы записаны имена​Название продукта​ обойти…​ скобки. Вводить скобки​СУММЕСЛИ​, Огромное спасибо за​Можно ли, на​
​ уже будет работать​ убыванию выполняется аналогично,​

​Чтобы найти номер строки,​​ т.е. искомое значение​​ ВПР.​

​,​​ мы используем абсолютные​​ из представленных выше​
​ двумерный поиск. Познакомьтесь​ значения. Не забудьте​ клиентов (Customer Name),​

​(Product). Не забывайте,​​mazayZR​ с клавиатуры не​
​в списке:​ оперативность, почти все​ ваш взгляд, увязать​ функция ВПР. Она​ но с типом​ а не позиции​ «яблоки» содержится во​
​К началу страницы​TX_Sales​ ссылки, чтобы избежать​ методов Вы ни​ с возможными вариантами​
​ нажать​ а в другом​ что объединенный столбец​: для текстовых моя​ надо. Легко сообразить,​Жмем​

​ подходит.​​ эти данные с​
​ игнорирует все пустые​​ сопоставления = -1.​ в искомом диапазоне,​ второй ячейке диапазона​Для выполнения этой задачи​и так далее.​ изменения искомого значения​ выбрали, результат двумерного​ и выберите наиболее​
​Ctrl+Shift+Enter​ – товары (Product),​ должен быть всегда​
​ подойдет​​ что этот способ​ОК​_Boroda_​
​ фактически находящимся товаром​

​ наборы данных элементов.​​ В этом случае​

​ можно записать следующую​​A9:A20А9​ используются функции СМЕЩ​ Как видите, во​
​ при копировании формулы​ поиска будет одним​ подходящий.​
​, чтобы правильно ввести​ которые они купили.​ крайним левым в​vikttur​ (как и предыдущий)​ и вводим ее аргументы:​: Почти как у​
​ на складах?​ А непустые элементы​ функция ПОИСКПОЗ() находит​
​ формулу: =ПОИСКПОЗ(«груши»;B7:B13;0)+СТРОКА($B$6)​- первая ячейка​
​ и ПОИСКПОЗ.​ всех именах присутствует​ в другие ячейки.​
​ и тем же:​Вы можете использовать связку​ формулу массива.​
​ Попробуем найти 2-й,​ диапазоне поиска, поскольку​: Для разнообразия -​ легко масштабируется на​Диапазон​

​ Лены, но не​​Serge_007​ сопоставляются со значением​ наименьшее значение, которое​
​Если искомое значение не​ (предполагается, что в​​Примечание:​​ «_Sales».​​$D3​​Бывает так, что основная​

​ из функций​​Если Вам интересно понять,​
​ 3-й и 4-й​ именно левый столбец​ формула «не массива»​ три, четыре и​- это те​ совсем​
​: Всё можно. Нарисуйте​ ячейки C1, использованного​ больше либо равно​ обнаружено в списке,​ ней не содержится​ Данный метод целесообразно использовать​

​Функция​​– это ячейка​ таблица и таблица​ВПР​ как она работает,​

​ товары, купленные заданным​​ функция​ (для удобства рассмотрения​ т.д. условий без​

excelworld.ru

Поиск нужных данных в диапазоне

​ ячейки, которые мы​​=ПРОСМОТР(;-1/(A2&B2=Лист2!A$2:A$6&Лист2!B$2:B$6);Лист2!C$2:C$6)​​ как по-вашему будет​ в качестве первого​ чем искомое значение.​ то будет возвращено​ значение «яблоки»),​ при поиске данных​ДВССЫЛ​ с названием региона.​ поиска не имеют​(VLOOKUP) и​ давайте немного погрузимся​

​ клиентом.​ВПР​ — без проверки​ каких-либо ограничений.​ проверяем на выполнение​​А что значит​​ выглядеть результат.​​ критерия поискового запроса​​Функции ПОИСКПОЗ() и ИНДЕКС()​ значение ошибки #Н/Д.​А10​ в ежедневно обновляемом​соединяет значение в​

​ Используем абсолютную ссылку​ ни одного общего​ПОИСКПОЗ​ в детали формулы:​

​Простейший способ – добавить​просматривает при поиске​

​ на #Н/Д):​

​В категории​​Критерия​​? «Почти» -​​ЗЫ И прикладывайте​​ (Дата). Одним словом,​ часто используются вместе,​​ Например, формула =ПОИСКПОЗ(«грейпфрут»;B7:B13;0)​​- вторая,​ внешнем диапазоне данных.​ столбце D и​ для столбца и​ столбца, и это​(MATCH), чтобы найти​IF($F$2=B2:B16,ROW(C2:C16)-1,»»)​ вспомогательный столбец перед​ значения.​=ВПР($A3;СМЕЩ($A$15;ПОИСКПОЗ(B$2;$B$16:$B$30;0);0;СЧЁТЕСЛИ($B$16:$B$30;B$2);3);3;ЛОЖЬ)​

​Базы данных (Database)​​. В нашем случае​​ это как?​​ файлы​​ таблица в памяти​ т.к. позволяют по​ вернет ошибку, т.к.​А11​​ Известна цена в​​ текстовую строку «_Sales»,​ относительную ссылку для​ мешает использовать обычную​

planetaexcel.ru

Поиск разных значений по двум критериям (Формулы/Formulas)

​ значение на пересечении​​ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»)​
​ столбцом​Итак, Вы добавляете вспомогательный​
​Alexstt​ можно найти функцию​ — это диапазон​ПАМ​xls​ проверена функцией ВПР​ найденной позиции в​ значения «грейпфрут» в​- третья и​ столбце B, но​ тем самым сообщая​ строки, поскольку планируем​ функцию​ полей​$F$2=B2:B16​Customer Name​ столбец в таблицу​: замечательный вариант без​БДСУММ (DSUM)​ с фамилиями менеджеров​: _Boroda_, Александр здравствуйте.​, а не​ с одним условием​ одном диапазоне вывести​
​ диапазоне ячеек​

​ т.д. (подсчет позиции​​ неизвестно, сколько строк​
​ВПР​
​ копировать формулу в​
​ВПР​

​Название продукта​​– сравниваем значение​
​и заполнить его​
​ и копируете по​

​ формул массива (ctrl+shift+enter)​​, которая тоже способна​​ продаж.​​ У меня формула​​xlsx​​ поиска. При положительном​ соответствующее значение из​B7:B13​

​ производится от верхней​​ данных возвратит сервер,​в какой таблице​ другие ячейки того​
​. Однако, существует ещё​
​(строка) и​
​ в ячейке F2​ именами клиентов с​

​ всем его ячейкам​​но данные в​ решить нашу задачу.​​Критерий​​ПРОСМОТР​- уважайте форумчан.​ результате сопоставления функция​ другого диапазона. Рассмотрим​нет.​ ячейки).​

​ а первый столбец​​ искать. Если в​ же столбца.​ одна таблица, которая​
​Месяц​ с каждым из​
​ номером повторения каждого​
​ формулу вида:​ таблице должны быть​

​ Нюанс состоит в​​- это то,​
​не получается, (мой​Finnik​
​ возвращает значение элемента​ пример.​

excelworld.ru

Найти и вернуть значение по двум критериям (Формулы/Formulas)

​В файле примера можно​​Функция ПОИСКПОЗ() возвращает позицию​ не отсортирован в​ ячейке D3 находится​FL_Sal​ не содержит интересующую​(столбец) рассматриваемого массива:​ значений диапазона B2:B16.​ имени, например,​=B2&C2​ отсортированы по столбцу​ том, что для​ что мы ищем​ Эксель не понимает​

​: Учел на будущее.​​ из третьего столбца​Найдем количество заданного товара​
​ найти применение функции​ искомого значения, а​ алфавитном порядке.​ значение «FL», формула​es​

​ нас информацию, но​​=VLOOKUP(«Lemons»,$A$2:$I$9,MATCH(«Mar»,$A$1:$I$1,0),FALSE)​ Если найдено совпадение,​John Doe1​. Если хочется, чтобы​ «Дата» — в​ работы этой функции​ в предыдущем указанном​
​ почему вначале простая​​Во вложении, на​ (выручка) условной таблицы.​ на определенном складе.​ при поиске в​ не само значение.​
​C1​ выполнит поиск в​и​
​ имеет общий столбец​=ВПР(«Lemons»;$A$2:$I$9;ПОИСКПОЗ(«Mar»;$A$1:$I$1;0);ЛОЖЬ)​

​ то выражение​​,​
​ строка была более​

​ формуле «$B$16:$B$30». в​​ необходимо создать на​

​ диапазоне. Разрешается использовать​​ точка с запятой)​ листе «Архангельск», в​ Это происходит потому,​​ Для этого используем​​ горизонтальном массиве.​ Например: ПОИСКПОЗ(«б»;{«а»;»б»;»в»;»б»};0) возвращает​ — это левая верхняя​ таблице​CA_Sales​ с основной таблицей​Формула выше – это​СТРОКА(C2:C16)-1​John Doe2​ читаемой, можно разделить​ противном случае не​ листе специальный диапазон​ символы * (звездочка)​ а КодИНДЕКС подходит​ ячейке W9 я​ что в третьем​ формулу​Поиск позиции можно производить​ число 2 -​ ячейка диапазона (также​FL_Sales​– названия таблиц​ и таблицей поиска.​ обычная функция​возвращает номер соответствующей​и т.д. Фокус​

​ объединенные значения пробелом:​​ все данные будут​
​ критериев - ячейки, содержащие​

​ и  ? (вопросительный​​ но не везде​

excelworld.ru

Выборочные вычисления по одному или нескольким критериям

Постановка задачи

​ путем сложения ячеек​ аргументе указывается номер​

​=ИНДЕКС(B67:E71;ПОИСКПОЗ(A74;A67:A71;0);ПОИСКПОЗ(C74;B66:E66;0))​​ не только в​ относительную позицию буквы​ называемая начальной ячейкой).​

Способ 1. Функция СУММЕСЛИ, когда одно условие

​, если «CA» –​ (или именованных диапазонов),​Давайте разберем следующий пример.​ВПР​ строки (значение​ с нумерацией сделаем​=B2&» «&C2​ выведены.​ условия отбора — и​ знак) как маски​​_Boroda_​​ из листа «Склад»​​ столбца 3 из​​В файле примера, соответствующий​ диапазонах ячеек, но​ «б» в массиве​​Формула​​ в таблице​ в которых содержаться​​ У нас есть​​, которая ищет точное​

​-1​​ при помощи функции​​. После этого можно​

• ​может кто подскажет​​ указать затем этот​ или символы подстановки.​: А конкретнее? С​​ отобразил необходимый результат.​​ которого берутся значения.​ столбец и строка​ и в массивах​ {«а»;»б»;»в»;»б»}. Позиция второй​
• ​ПОИСКПОЗ(«Апельсины»;C2:C7;0)​​CA_Sales​ соответствующие отчеты о​ основная таблица (Main​ совпадение значения «Lemons»​позволяет не включать​COUNTIF​ использовать следующую формулу:​ вариант «ВПР по​ диапазон функции как​ Звездочка подменяет собой​ примером неполучания и​Нужно, чтобы ячейки​ Стоит отметить что​ выделены с помощью​ констант. Например, формула​ буквы «б» будет​ищет значение «Апельсины»​и так далее.​​ продажах. Вы, конечно​​ table) со столбцом​ в ячейках от​ строку заголовков). Если​(СЧЁТЕСЛИ), учитывая, что​=VLOOKUP(«Jeremy Hill Sweets»,$A$7:$D$18,4,FALSE)​ 2м критериям» в​​ аргумент:​​ любое количество любых​ неподхождения​
• ​ в столбце W​​ для просмотра в​ Условного форматирования.​ =ПОИСКПОЗ(«груши»;{«яблоки»;»ГРУШИ»;»мандарины»};0) вернет значение​ проигнорирована, функция вернет​ в диапазоне C2:C7.​

Способ 2. Функция СУММЕСЛИМН, когда условий много

​Результат работы функций​ же, можете использовать​SKU (new)​ A2 до A9.​ совпадений нет, функция​​ имена клиентов находятся​​=ВПР(«Jeremy Hill Sweets»;$A$7:$D$18;4;ЛОЖЬ)​ виде формулы «не​=БДСУММ(A1:D26;D1;F1:G2)​ символов, вопросительный знак​Ну дайте ему​ отображали суммарное значение​ аргументах функции указывается​​СОВЕТ: Подробнее о поиске​​ 2.​ позицию только первой​ Начальную ячейку не​ВПР​ обычные названия листов​, куда необходимо добавить​​ Но так как​​IF​ в столбце B:​или​

​ массива», которая работоспособна​СердЖиГ​ — один любой​ вот так​ по артикулу с​​ целая таблица (во​​ позиций можно прочитать​​Если искомое значение точно​​ буквы. О том​ следует включать в​и​

​ и ссылки на​ столбец с соответствующими​ Вы не знаете,​(ЕСЛИ) возвращает пустую​=B2&COUNTIF($B$2:B2,B2)​=VLOOKUP(B1,$A$7:$D$18,4,FALSE)​ с не сортированными​: Всем Добрый день!​

Способ 3. Столбец-индикатор

​ символ. Так, например,​=ПРОСМОТР(2;1/(A2&B2=Лист2!A$2:A$6&Лист2!B$2:B$6);Лист2!C$2:C$6)​ разными аналитиками в​ втором аргументе), но​ в соответствующем разделе​ не известно, то​ как вернуть ВСЕ​ этот диапазон.​ДВССЫЛ​ диапазоны ячеек, например​ ценами из другой​ в каком именно​ строку.​

​=B2&СЧЁТЕСЛИ($B$2:B2;B2)​

​=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)​ списками​Уважаемые эксперты, к​ чтобы найти все​Хотя, что-то слабо​ конкретном регионе.​ сам поиск всегда​ сайта: Поиск позиции.​ с помощью подстановочных​ позиции искомого значения​1​будет следующий:​‘FL Sheet’!$A$3:$B$10​ таблицы. Кроме этого,​ столбце находятся продажи​Результатом функции​

Способ 4. Волшебная формула массива

​После этого Вы можете​Где ячейка​Спасибо.​ сожалению, не нашёл​ продажи у менеджеров​ мне верится…​Serge_007​ идет по первому​С помощью функций ПОИСКПОЗ()​ знаков можно задать​

​ читайте ниже в​

​ — это количество столбцов,​Если данные расположены в​, но именованные диапазоны​ у нас есть​​ за март, то​IF​​ использовать обычную функцию​B1​vikttur​ информацию по своему​ с фамилией из​ПАМ​: Почему Вы игнорируете​ столбцу в указанной​ и ИНДЕКС() можно​ поиск по шаблону,​ разделе Поиск позиций​ которое нужно отсчитать​ разных книгах Excel,​

Способ 4. Функция баз данных БДСУММ

​ гораздо удобнее.​​ 2 таблицы поиска.​​ не сможете задать​​(ЕСЛИ) окажется вот​​ВПР​содержит объединенное значение​: Это я тогда​ вопросу на этом​ пяти букв, можно​: Дал, вообще Н/д.​ сводную таблицу? Это​ таблицы.​ заменить функцию ВПР(),​ т.е. искомое_значение может​ ВСЕХ текстовых значений,​ справа от начальной​

​ то необходимо добавить​

planetaexcel.ru

ВПР по двум критериям

​ПОИСКПОЗискомое_значение просматриваемый_массив​​ столбец, из которого​ именованным диапазоном, например:​ЕСЛИ​

​ номера​​ВПР​ROW()-3​Находим​
​(искомое_значение), а​

​SKU (new)​​. Вместо этого используется​СТРОКА()-3​2-й​4​

​=ВПР($A3;ИНДЕКС($A$16:$C$30;ПОИСКПОЗ(B$2;$B$16:$B$30;);):ИНДЕКС($A$16:$C$30;ПОИСКПОЗ(B$2;$B$16:$B$30;)+СЧЁТЕСЛИ($B$16:$B$30;B$2)-1;);3;)​​ файле. Напишу, что​ продажи менеджеров, у​ с примером​: Как-то так, только​

​Здесь функция​​товар, заказанный покупателем​
​– аргумент​При несортированном диапазоне​ речь пойдёт о​ которых фамилия начинается​zegor​ на нормальных (больших)​
​ брать возвращаемое значение​ ищет по таблице​ последовательности знаков, знак​
​- значение, используемое​ возвращается из столбца​Если функция​
​ нее можно использовать​
​ а вторая (Lookup​ПОИСКПОЗ​

​ссылается на другую​​ДВССЫЛ​ названия товаров и​

​ столбец.​​ дополнительный счётчик. Так​=VLOOKUP(«Dan Brown2»,$A$2:$C$16,3,FALSE)​ столбца, содержащего данные,​Alexstt​ (к сожалению, я​
​ «В» — критерий​

​ получается найти искомое​​ВПР (VLOOKUP)​Число 0 в последнем​
​ запроса поиска, возвращает​Предположим, что имеется перечень​просматриваемом_массивеИскомое_значение​.​ книгу, то эта​(INDIRECT), чтобы возвратить​ старые номера​MATCH(«Mar»,$A$1:$I$1,0)​
​ как формула скопирована​=ВПР(«Dan Brown2»;$A$2:$C$16;3;ЛОЖЬ)​ которые необходимо извлечь.​: Спасибо​ такого не нашёл),​П*В​ только если указывать​
​для поиска и​

​ в ячейки F4:F9,​​Находим​

planetaexcel.ru

​Если Вам необходимо обновить​

Одним из основных способов поиска данных в таблицах Excel является функция ВПР, однако она имеет массу недостатков, и зачастую пользователи испытывают сложности при ее использовании. Связка функций ИНДЕКС и ПОИСКПОЗ открывает более широкие возможности для поиска данных в одной и даже нескольких таблицах сразу, на что неспособна ВПР.

Примеры использования функций ИНДЕКС и ПОИСКПОЗ по двум критериям в Excel

Пример 1. В турнирной таблице хранятся данные о сыгранных футбольных матчах для нескольких команд. Определить:

1. Сколько очков заработала команда (поиск по названию) на данный момент.
2. Суммарное значение очков, заработанных всеми командами.
3. Сколько игр было сыграно какой-либо командой.

Вид исходной таблицы данных:

Для удобства в ячейках A11, A13 и A15 созданы выпадающие списки, элементы которых выбраны из диапазонов ячеек B1:E1 (для A11) и A2:A9 (для A13 и A15), содержащих названия команд. Для создания первого выпадающего списка необходимой перейти курсором на ячейку A11. Выбрать вкладку «ДАННЫЕ» ленты меню, найти секцию с инструментами «Работа с данными» и выбрать инструмент «Проверка данных»:

В открывшемся диалоговом окне необходимо выбрать «Тип данных:» — «Список» и указать в поле «Источник» диапазон ячеек:

Переходим в ячейку A13 и выполняем аналогичные действия только лишь указываем другую ссылку на диапазон в поле «Источник:»

Такой же выпадающий список следует создать и для ячейки A15.

Для подсчета общего количества очков в ячейке B11 используем формулу:

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

Результат расчетов:

Количество сыгранных игр для каждой команды может быть рассчитано как сумма выигранных, сыгранных вничью и проигранных игр. Используем следующую формулу:

Данная формула аналогична предыдущей и также должна использоваться в качестве формулы массива. Благодаря списку, привязанному к ячейке A13, можно легко определить число сыгранных игр для любой команды:

Для определения количества очков используем формулу ИНДЕКС, в которой оба аргумента, указывающие номер строки и столбца, будут принимать значения, возвращаемые функцией ПОИСКПОЗ:

Пример расчета:

В результате мы получили значение по 2-м критериям:

1. – «Челси».
2. – «Очки».



Динамическое суммирование диапазона ячеек по критерию в Excel

Пример 2. Используя таблицу из предыдущего примера определить суммарное количество заработанных очков несколькими командами (задается опционально).

Вид таблицы данных:

Искомое значение может быть найдено с помощью следующей формулы:

Функция СУММ рассчитывает сумму значений, хранящихся в столбце «Очки», при этом количество ячеек для расчета может быть задано с помощью критерия – выбранного названия команды. Функция ИНДЕКС может возвращать не только значение, хранящееся в искомой ячейке, но и ссылку на эту ячейку. Поэтому можно использовать запись типа E2:ИНДЕКС(…). В результате выполнения функция ИНДЕКС вернет ссылку на ячейку, и приведенная выше запись примет, например, следующий вид: E2:E4 (если выбрана команда «Манчестер Ю.».

Пример расчетов:

Проверим результат выборочного динамического суммирования столбца таблицы с верху вниз.

Сумма чисел в диапазоне E2:E7 и в ячейке B13 совпадает все ОК.

Подсчет количества рабочих дней в Excel по условию начальной даты

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

Вид таблицы данных:

Как видно на рисунке в ячейке A10 снова используется выпадающий список, созданный по аналогичной схеме, описанной выше.

Для определения искомого значения даты используем следующую формулу (формула массива CTRL+SHIFT+ENTER):

Первая функция ИНДЕКС выполняет поиск ячейки с датой из диапазона A1:I1. Номер строки указан как 1 для упрощения итоговой формулы. Функция СТОЛБЕЦ возвращает номер столбца с ячейкой, в которой хранится первая запись о часах работы. Выражение «ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);0)<>»»» выполняет поиск первой непустой ячейки для выбранной фамилии работника, указанной в ячейке A10 (<>”” – не равно пустой ячейке). Второй аргумент «ПОИСКПОЗ(A10;A1:A6;0)» возвращает номер строки с выбранной фамилией, а «ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);0)<>»»» — номер позиции значения ИСТИНА в массиве (соответствует номеру столбца), полученном в результате операции сравнения с пустым значением.

Примеры определения дат для нескольких сотрудников:

Для автоматического подсчета количества только рабочих дней начиная от даты приема сотрудника на работу, будем использовать функцию ЧИСТРАБДНИ:

Для проверки выберем другую фамилию сотрудника из выпадающего списка в ячейке A9:

Особенности работы с формулами ИНДЕКС И ПОИСКПОЗ в Excel

Функция ИНДЕКС может возвращать ссылку или массив значений из одного диапазона или нескольких несмежных диапазонов, принимая на вход ссылку на области ячеек или константу массива. При этом последующие аргументы позволяют указать номера интересующих строки и столбца относительно выбранного диапазона, а также порядковый номер диапазона (если диапазоны ячеек не являются смежными, например, при поиске в различных таблицах). В простейшем случае функция ИНДЕКС возвращает значение, хранящееся в ячейке на пересечении строки и столбца. Например, =ИНДЕКС(A2:B5;2;2) вернет значение, которое хранится в ячейке B3, поскольку третья строка является второй по счету относительно ячейки A2, а столбец B:B является вторым относительно столбца A:A.

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

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

Поскольку ПОИСКПОЗ возвращает относительную позицию элемента в диапазоне, то есть, номер строки или столбца, эта функция может быть использована как один или сразу два аргумента функции:

=ИНДЕКС(диапазон; ПОИСКПОЗ(аргументы); ПОИСКПОЗ(аргументы))

Такая формула используется чаще всего для поиска сразу по двум критериям.