Впр как найти несколько значений

Поиск ВПР нескольких значений по нескольким условиям

В статье показаны способы поиска (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возврата нескольких результатов в столбце, строке или в отдельной ячейке.

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

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

Поиск нескольких позиций в Excel с помощью формулы

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

• ЕСЛИ – оценивает условие и возвращает одно значение, если условие выполняется, и другое значение, если условие не выполняется.
• НАИМЕНЬШИЙ– получает N-е наименьшее значение в массиве.
• ИНДЕКС — возвращает элемент массива на основе указанных вами номеров строк и столбцов.
• СТРОКА — возвращает номер строки.
• СТОЛБЕЦ — возвращает номер столбца.
• ЕСЛИОШИБКА – перехватывает ошибки.

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

Поиск нескольких значений и возврат результатов в столбец

Допустим, у вас есть имена продавцов в столбце А и товары, которые они продали, в столбце В. Таблица содержит несколько записей для каждого продавца. Ваша цель — получить список всех товаров, относящихся к данному человеку. Чтобы это сделать, выполните следующие действия:

1. Введите список имен продавцов в какую-нибудь пустую строку того же или другого рабочего листа. В этом примере имена вводятся в ячейки D2:H2:

Совет. Чтобы быстро записать все имеющиеся в списке имена, вы можете использовать эту инструкцию —  как получить список уникальных значений в Excel.

1. Под именем выберите количество пустых ячеек, равное или превышающее максимально возможное количество совпадений, введите одно из следующих выражений и нажмите Ctrl + Shift + Enter для ввода его как формулы массива (в этом случае вы сможете редактировать формулу только сразу во всем диапазоне, где она введена). Или же вы можете записать это в левую верхнюю ячейку, также использовав Ctrl + Shift + Enter, а затем перетащить вниз еще на несколько ячеек (в этом случае вы сможете редактировать формулу в каждой ячейке отдельно).

=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13; НАИМЕНЬШИЙ(ЕСЛИ(D$2=$A$3:$A$13; СТРОКА($B$3:$B$13)-2;»»); СТРОКА()-2));»»)

или

=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13;НАИМЕНЬШИЙ(ЕСЛИ(D$2=$A$3:$A$13;СТРОКА($A$3:$A$13)-МИН(СТРОКА($A$3:$A$13))+1;»»); СТРОКА()-2));»»)

Как видите,  первая формула немного компактнее, а вторая более универсальна и требует меньше модификаций (подробнее о синтаксисе и логике мы поговорим чуть дальше).

1. Скопируйте формулу в соседние столбцы справа. Для этого перетащите маркер заполнения (небольшой квадрат в правом нижнем углу выбранного диапазона) вправо.

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

Здесь мы использовали первую формулу:

=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13; НАИМЕНЬШИЙ(ЕСЛИ(D$2=$A$3:$A$13; СТРОКА($B$3:$B$13)-2;»»); СТРОКА()-2));»»)

Как это работает.

Это пример использования Excel от среднего до продвинутого уровня, который подразумевает базовые знания формул массива и функций Excel. Итак, разберём пошагово:

1. Функция ЕСЛИ

В основе поиска – функция ЕСЛИ, чтобы получить позиции всех вхождений искомого значения в диапазоне поиска: ЕСЛИ(D$2=$A$3:$A$13; СТРОКА($B$3:$B$13) )-2;»»)

ЕСЛИ сравнивает искомое значение (D2) с каждым значением в диапазоне поиска (A3:A13) и, если совпадение найдено, возвращает относительную позицию строки; пустое значение («») в противном случае.

Относительная позиция вычисляется путем вычитания 2 из СТРОКА($B$3:$B$13), чтобы первая позиция с формулой имела порядковый номер 1 (то есть, 3-2=1). Если ваш диапазон вывода начинается со строки 2, тогда вычтите 1 и так далее. Результатом этой операции является массив {1;2;3;4;5;6;7;8;9;10;11}, который поступает в аргумент значение_если_истина функции ЕСЛИ.

Вместо приведенного выше вычисления вы можете использовать следующее выражение:

СТРОКА(столбец_просмотра) — МИН(СТРОКА(столбец_просмотра))+1

Оно возвращает тот же результат, но не требует каких-либо изменений независимо от местоположения возвращаемого столбца. В этом примере это будет СТРОКА($A$3:$A$13)-МИН(СТРОКА($A$3:$A$13))+1.

Итак, на данный момент у вас есть массив, состоящий из чисел (позиций совпадений) и пустых строк (несовпадений). Для ячейки D3 в этом примере у нас есть следующий массив:

=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13; НАИМЕНЬШИЙ({1:»»:»»:»»:5:»»:»»:8:»»:»»:»»}; СТРОКА()-2));»»)

Если вы сверитесь с исходными данными, вы увидите, что «Сергей» (значение поиска в D2) появляется на 1- й , 5 -й и 8 -й позициях в диапазоне поиска (A3: A13).

1. Функция НАИМЕНЬШИЙ

Затем вступает в действие функция НАИМЕНЬШИЙ(массив; k), чтобы определить, какие совпадения должны быть возвращены в конкретной ячейке.

С уже установленным в предыдущем шаге массивом давайте определим аргумент k , т. е. k-е наименьшее возвращаемое значение. Для этого вы делаете своего рода «инкрементный счетчик» СТРОКА()-n, где «n» — это номер строки первой ячейки формулы минус 1. В этом примере мы ввели формулу в ячейки D3:D7, поэтому СТРОКА()-2 возвращает «1» для ячейки D3 (строка 3 минус 2), «2» для ячейки D4 (строка 4 минус 2) и т. д.

В результате функция НАИМЕНЬШИЙ извлекает первый наименьший элемент массива в ячейку D3, второй наименьший элемент в ячейку D4 и так далее. И это превращает первоначальную длинную и сложную формулу в очень простую, например:

=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13;{1});»»)

Совет. Чтобы увидеть значение, вычисленное определенной частью формулы, выделите эту часть в самой формуле и нажмите F9.

1. Функция ИНДЕКС

Здесь все просто. Вы используете функцию ИНДЕКС, чтобы вернуть значение элемента массива на основе его номера.

1. Функция ЕСЛИОШИБКА 

И, наконец, вы оборачиваете формулу в функцию ЕСЛИОШИБКА для обработки возможных ошибок, которые неизбежны, потому что вы не можете знать, сколько совпадений будет возвращено для того или иного искомого значения. Ведь вы копируете формулу в число ячеек явно большее, чем количество возможных совпадений, то есть «с запасом». Чтобы не пугать пользователей кучей ошибок, просто замените их пустой строкой (пустой ячейкой).

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

На скриншоте ниже вы можете видеть, как работает вторая формула

=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13;НАИМЕНЬШИЙ(ЕСЛИ(D$2=$A$3:$A$13;СТРОКА($A$3:$A$13)-МИН(СТРОКА($A$3:$A$13))+1;»»); СТРОКА()-2));»»)

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

Вариант 1 :

=ЕСЛИОШИБКА(ИНДЕКС(диапазон_возвращаемых_значений; НАИМЕНЬШИЙ(ЕСЛИ(искомое_значение = диапазон_искомых_значений; СТРОКА(диапазон_возвращаемых_значений)-m;»»); СТРОКА()-n));»»)

Вариант 2:

=ЕСЛИОШИБКА(ИНДЕКС(диапазон_возвращаемых_значений;НАИМЕНЬШИЙ(ЕСЛИ(искомое_значение = диапазон_искомых_значений;СТРОКА(диапазон_искомых_значений)-МИН(СТРОКА(диапазон_искомых_значений))+1;»»); СТРОКА()-n));»»)

где:

• m — номер строки первой ячейки в возвращаемом диапазоне минус 1.
• n — номер строки первой ячейки с формулой минус 1.

Примечание. В приведенном выше примере и n, и m равны 2, потому что наш диапазон возвращаемых значений начинается, да и сама формула расположена, в строке 3. В ваших таблицах Эксель это вполне могут быть и другие числа.

Поиск нескольких совпадений и возврат результатов в строке

Если вы хотите вернуть несколько найденных значений в строках, а не в столбцах, измените приведенные выше формулы Excel следующим образом:

=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13; НАИМЕНЬШИЙ(ЕСЛИ($D3=$A$3:$A$13;СТРОКА($B$3:$B$13)-2;»»); СТОЛБЕЦ()-4));»»)

или

=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13;НАИМЕНЬШИЙ(ЕСЛИ($D3=$A$3:$A$13;СТРОКА($A$3:$A$13)-МИН(СТРОКА($A$3:$A$13))+1;»»);СТОЛБЕЦ()-4)); «»)

Как и в предыдущем примере, обе они являются формулами массива, поэтому не забудьте нажать комбинацию Ctrl + Shift + Enter, чтобы записать их правильно.

Формулы работают с той же логикой, что и в предыдущем примере, за исключением того, что вы используете функцию СТОЛБЕЦ вместо СТРОКА. Чтобы определить, какое совпадающее значение должно быть возвращено в конкретной ячейке, используем: СТОЛБЕЦ()-n где n — номер столбца первой ячейки, в которую вводится формула, минус 1. В этом примере результаты выводятся в диапазон E2:H2. Поскольку E является пятым столбцом, n равно 4  (5-1=4).

Примечание. Чтобы формула правильно копировалась вправо и вниз, обратите внимание на ссылки на значения поиска, где используется абсолютный адрес столбца и относительный адрес строки, например $D3.

И вот общие формулы для ВПР в Excel нескольких значений, возвращаемых по строке:

Формула 1 :

=ЕСЛИОШИБКА(ИНДЕКС(диапазон_возвращаемых_значений; НАИМЕНЬШИЙ(ЕСЛИ(искомое_значение = диапазон_искомых_значений; СТРОКА(диапазон_возвращаемых_значений)-m;»»); СТОЛБЕЦ()-n));»»)

Формула 2:

=ЕСЛИОШИБКА(ИНДЕКС(диапазон_возвращаемых_значений;НАИМЕНЬШИЙ(ЕСЛИ(искомое_значение = диапазон_искомых_значений;СТРОКА(диапазон_искомых_значений)-МИН(СТРОКА(диапазон_искомых_значений))+1;»»); СТОЛБЕЦ()-n));»»)

где:

• m — номер строки первой ячейки в возвращаемом диапазоне, минус 1.
• n — номер столбца первой ячейки, в которой записана формула, минус 1.

Поиск нескольких значений на основе нескольких условий

Вы уже знаете, как выполнять поиск нескольких значений в Excel на основе одного условия. Но что, если вы хотите вернуть несколько совпадений сразу на основе двух или более критериев? Продолжая предыдущие примеры – что, если у вас в таблице есть дополнительный столбец «Месяц» и вы хотите получить список всех товаров, проданных конкретным продавцом в определенном месяце?

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

Как вернуть несколько значений в столбце.

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

В общем виде это выглядит так:

=ЕСЛИОШИБКА(ИНДЕКС(диапазон_возвращаемых_значений; НАИМЕНЬШИЙ(ЕСЛИ(1=((—(искомое_значение1 = диапазон_искомых_значений1)) * (—(искомое_значение2 = диапазон_искомых_значений2))) ; СТРОКА(диапазон_возвращаемых_значений)-m;»»); СТРОКА()-n));»»)

где:

• m — номер строки первой ячейки в возвращаемом диапазоне, минус 1.
• n — номер строки первой слева ячейки с формулой, минус 1.

Предположим, что список продавцов (диапазон_искомых_значений1) находится в A3:A30, список месяцев (диапазон_искомых_значений2) находится в B3: B30, интересующий продавец (искомое_значение1) указан в ячейке E3, а нужный месяц (искомое_значение2) – в ячейке F3. Тогда формула поиска принимает следующий вид:

=ЕСЛИОШИБКА(ИНДЕКС($C$3:$C$30; НАИМЕНЬШИЙ(ЕСЛИ(1=((—($E$3=$A$3:$A$30))*(—($F$3=$B$3:$B$30))); СТРОКА($C$3:$C$30)-2;»»); СТРОКА()-2));»»)

Таким образом, мы вводим имя в E3, месяц в F3, и получаем список товаров в столбце G:

Как вернуть несколько результатов в строке.

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

=ЕСЛИОШИБКА(ИНДЕКС(диапазон_возвращаемых_значений; НАИМЕНЬШИЙ(ЕСЛИ(1=((—(искомое_значение1 = диапазон_искомых_значений1)) * (—(искомое_значение2 = диапазон_искомых_значений2))) ; СТРОКА(диапазон_возвращаемых_значений)-m;»»); СТОЛБЕЦ()-n));»»)

где:

• m — номер строки первой ячейки в возвращаемом диапазоне, минус 1.
• n — номер столбца первой слева ячейки, в которой записана формула, минус 1.

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

=ЕСЛИОШИБКА(ИНДЕКС($C$3:$C$30; НАИМЕНЬШИЙ(ЕСЛИ(1=((—($E3=$A$3:$A$30))*(—($F3=$B$3:$B$30))); СТРОКА($C$3:$C$30)-2;»»); СТОЛБЕЦ()-6));»»)

И результат ВПР нескольких значений по нескольким условиям может выглядеть так:

Аналогичным образом вы можете выполнять ВПР с тремя, четырьмя и более условиями.

Как это работает?

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

1=((—(искомое_значение1 = диапазон_искомых_значений1)) * (—(искомое_значение2 = диапазон_искомых_значений2)) * …..)

Результатом каждого сравнения (искомое_значение = диапазон_искомых_значений) является массив логических значений ИСТИНА (условие выполнено) и ЛОЖЬ (условие не выполнено). Двойное отрицание (—) переводит логические значения в единицы и нули. А поскольку умножение на ноль всегда дает ноль, в правой части этого равенства у вас будет получаться 1 только для тех элементов, которые удовлетворяют всем указанным вами условиям. 

Вы просто сравниваете окончательный массив нулей и единиц с числом 1, чтобы функция СТРОКА вернула порядковые номера строк, удовлетворяющих всем условиям, в противном случае — пустоту.

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

Как вернуть несколько значений ВПР в одну ячейку

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

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

=ЕСЛИОШИБКА(ИНДЕКС($C$3:$C$30; НАИМЕНЬШИЙ(ЕСЛИ(1=((—($E3=$A$3:$A$30))*(—($F3=$B$3:$B$30))); СТРОКА($C$3:$C$30)-2;»»); СТОЛБЕЦ()-6));»»)

Внесем в нее небольшие изменения.

Чтобы обработать сразу несколько результатов, в функцию СТОЛБЕЦ добавим аргумент – диапазон ячеек, в который мы ранее копировали формулу. То есть, вместо СТОЛБЕЦ() у нас теперь будет СТОЛБЕЦ(G3:K3). Это позволит формуле массива получить сразу несколько номеров столбцов.

Затем применим крайне полезную при работе с текстовыми значениями функцию ОБЪЕДИНИТЬ (доступна в Excel 2019 и более поздних версиях). Она позволит нам объединить несколько текстовых значений, отделив их друг от друга выбранным нами разделителем. К примеру, запятой с пробелом после нее.

Вот что у нас получится:

=ОБЪЕДИНИТЬ(«, «; ИСТИНА; ЕСЛИОШИБКА(ИНДЕКС($C$3:$C$30; НАИМЕНЬШИЙ(ЕСЛИ(1=((—($E3=$A$3:$A$30))*(—($F3=$B$3:$B$30))); СТРОКА($C$3:$C$30)-2;»»); СТОЛБЕЦ(G3:K3)-6));»»))

И видим результат ВПР нескольких значений в одной ячейке на этом скриншоте:

Еще один, более простой вариант, чтобы подтянуть несколько значений и вывести результат в одной ячейке:

=ОБЪЕДИНИТЬ(«, «;ИСТИНА;ЕСЛИ(($A$3:$A$30=E3)*($B$3:$B$30=F3)=1;$C$3:$C$30;»»))

Ее также нужно вводить как формулу массива:

Как найти несколько значений без дубликатов

А если так случится, что в результатах поиска будет несколько одинаковых значений? Пример таких данных вы можете видеть на скриншоте ниже. Естественно, выводить в ячейке несколько дубликатов было бы не совсем хорошо.

Если вы хотите получить в одной ячейке результаты поиска нескольких значений без повторов, попробуйте так:

=ОБЪЕДИНИТЬ(«, «; ИСТИНА; ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ($C$3:$C$13; ЕСЛИ(E3=$A$3:$A$13; $C$3:$C$13; «»); 0);»»)=ПОИСКПОЗ(СТРОКА($C$3:$C$13); СТРОКА($C$3:$C$13)); $C$3:$C$13; «»))

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

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

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

Функция ЕСЛИОШИБКА – примеры формул — В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального…

5 способов – поиск значения в массиве Excel — В статье предлагается несколько различных формул для выполнения поиска в двумерном массиве значений Excel.  Просмотрите эти варианты и выберите наиболее для вас подходящий. При поиске данных в электронных таблицах Excel…

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

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

Поиск в массиве при помощи ПОИСКПОЗ — В этой статье объясняется с примерами формул, как использовать функцию ПОИСКПОЗ в Excel.  Также вы узнаете, как улучшить формулы поиска, создав динамическую формулу с функциями ВПР и ПОИСКПОЗ. В Microsoft…

Функция ИНДЕКС в Excel — 6 примеров использования — В этом руководстве вы найдете ряд примеров формул, демонстрирующих наиболее эффективное использование ИНДЕКС в Excel. Из всех функций Excel, возможности которых часто недооцениваются и используются недостаточно, ИНДЕКС определенно занимает место…

Как объединить две или несколько таблиц в Excel — В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном…

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

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

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

=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5))

Ее надо ввести следующим образом:

1. выделить ячейки, куда должны выводиться результаты (в нашем примере — это диапазон D6:D20)
2. ввести (скопировать формулу в первую ячейку) диапазона
3. нажать Ctrl + Shift + Enter

Вычитание единицы в фрагменте СТРОКА(B2:B16)-1 делается из-за шапки таблицы. По той же причине для компенсации сдвига результирующего диапазона относительно исходного вычитается число пять во фрагменте СТРОКА()-5

Чтобы скрыть ошибку #ЧИСЛО!, которая будет появляться в незаполненных ячейках результирующего диапазона D6:D20 можно использовать функции проверки ошибок ЕСЛИ и ЕОШ, заменив нашу формулу чуть более сложной:

=ЕСЛИ(ЕОШ(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))

В Excel 2007 появилась более удобная функция ЕСЛИОШИБКА — она позволяет решить задачу более компактно:

=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)

P.S.

В англоязычной версии Excel эти функции будут выглядеть так:

=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))

=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))

=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)

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

• Использование функции ВПР (VLOOKUP) для нахождения данных в таблице
• Улучшенный вариант функции ВПР (VLOOKUP2), который умеет искать в любом столбце и не только первое значение
• Функции VLOOKUP2 и VLOOKUP3 из надстройки PLEX
• Что такое формулы массива и с чем их едят

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

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

• • Способ 1. Функция ВПР в Excel с несколькими условиями при помощи вспомогательного столбца
  • Способ 2. ВПР с несколькими условиями в Excel при помощи сочетания функций ИНДЕКС / ПОИСКПОЗ
  • Способ 3. ВПР по двум условиям при помощи формулы массива
  • Способ 4. ВПР с несколькими условиями при помощи функции СУММЕСЛИМН

Это самый распространенный и самый простой способ в excel сделать ВПР двух или нескольких значений. 

Рассмотрим на примере. Есть две таблицы — таблица-источник (зеленая “шапка”) и рабочая таблица (синяя “шапка”), в которую нужно подтянуть количество автомобилей из источника по трем условиям: марка, модель и цвет автомобиля.

В таблице-источнике создадим вспомогательный столбец, в котором объединим все имеющиеся значения в столбцах при помощи оператора конкатенации & или функцией СЦЕП. Вспомогательный столбец должен быть крайним слева (помним, что ВПР ищет совпадения в крайнем левом столбце).

Вспомним синтаксис функции ВПР:

=ВПР(искомое_значение; таблица; номер столбца; [интервальный просмотр])

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

Если будете копировать формулу в другие ячейки, то ссылки на ячейки и диапазон таблицы в формуле необходимо закрепить знаками $.

Как видите, функция ВПР в excel с несколькими условиями (а данном случае три условия) подтянула значение из выделенной строки.

---

   Сообщество Excel Analytics | обучение Excel

    Канал на Яндекс.Дзен 

---

Способ 2. ВПР с несколькими условиями в Excel при помощи сочетания функций ИНДЕКС / ПОИСКПОЗ

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

Этот способ удобен тем, что не нужно создавать дополнительных столбцов в таблице-источнике (на практике не всегда возможно добавить столбец — источник может быть защищен от изменений).

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

Скопируем в нашем примере строку с условиями поиска и напишем следующую формулу:

После написания формулы необходимо нажать сочетание клавиш Ctrl + Shift + Enter. Это необходимо сделать, т.к. это формула массива — в противном случае выйдет ошибка #ЗНАЧ.

Давайте разберем формулу:

=ИНДЕКС(L4:L13;ПОИСКПОЗ(B5&C5&D5;I4:I13&J4:J13&K4:K13;0))

• • • L4:L13 — массив, из которого будет подтягиваться информация. Тот столбец, данные из которого нам нужны.
    • B5&C5&D5 — критерии для поиска, которые мы объединили между собой оператором конкатенации &.
    • I4:I13&J4:J13&K4:K13 — столбцы, в которых будут происходить поиск по заданным критериям. 

I4:I13 — столбец с марками автомобилей (соответствует критерию в ячейке В5)

J4:J13 — столбец с моделями автомобилей (соответствует критерию в ячейке С5)

K4:K13 — столбец с цветом автомобилей (соответствует критерию в ячейке D5)

Обратите внимание, что столбцы, в которых будет происходить поиск, должны располагаться в формуле в таком же порядке, как и критерии поиска.

• • • 0 — аргумент, обозначающий, что нужен поиск точного совпадения.

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

Рассмотрим работу функции ВПР по двум условиям на то же примере, только исключим один из критериев поиска — будем искать количество автомобилей по Модели автомобиля и Цвету. Так будет проще понять эту формулу.

Запишем следующую формулу для поиска:

=ВПР(C6;ЕСЛИ(K4:K13=D6;J4:L13;0);3;0)

В конце обязательно нужно нажать сочетание клавиш Ctrl + Shift + Enter, т.к. это формула массива, иначе будет ошибка #Н/Д.

Разберем, как работает эта формула. 

С6 — это первый критерий для поиска. Но поскольку у нас есть еще один критерий (D6), то искать C6 формула будет но во всем столбце J, а только в той строке, где будет совпадение с столбце К со значением второго критерия (D6).

Таким образом, при помощи конструкции внутри формулу ВПР 

ЕСЛИ(K4:K13=D6;J4:L13;0)

Создается виртуальная таблица для поиска значения первого критерия.

А дальше формула ВПР по двум условиям работает как обычная ВПР — указывается номер столбца 3 и интервальный просмотр 0 (точный поиск).

Способ 4. ВПР с несколькими условиями при помощи функции СУММЕСЛИМН

Функция СУММЕСЛИМН является полноценной альтернативой функции ВПР, если нужно подтянуть числовой результат по нескольким условиям.

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

Синтаксис функции СУММЕСЛИМН:

=СУММЕСЛИМН(Диапазон_суммирования; Диапазон_условия1; Условие1;…; Диапазон_условияN; УсловиеN)

Подтянем данные в нашу рабочую таблицу из таблицы-источника по тем же критериям, но уже при помощи функции СУММЕСЛИМН.

Функция СУММЕСЛИМН просуммировала все значения в столбце L, у которых значение в столбце I равно значению В7, значения в столбце J — C7, а значения в столбце K — D7. Как видите, результат такой же, как и при других способах.

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

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

Вам может быть интересно:

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

​Смотрите также​ Москва!B3, т.е. содержимое​​ для этого нужно​​Если в качестве аргумента​ с ФИО сотрудников);​ из животных скорость​ что в первом​, слева от строки​​ B (поскольку B​​будет готова:​ столбце B основной​ несколько драгоценных секунд.​(SUMIF) в Excel,​ Обратите внимание, здесь​(SUMPRODUCT) возвращает сумму​ строки (значение​ она не может​Во второй части нашего​ ячейки B3 с​ обязательно использовать клавиши:​ [интервальный_просмотр] было передано​A2:B10 – диапазон ячеек​​ ближе всего к​​ аргументе мы используем​ формул.​

• ​ – это второй​=SUMIF(Main_table[ID],VLOOKUP($F$2,Lookup_table,2,FALSE),Main_table[Sales])​
• ​ таблицы с именем​Как видите, использовать функции​ чтобы выполнять поиск​
• ​ мы используем абсолютные​ произведений выбранных массивов:​
• ​-1​ искать значение, состоящее​
• ​ учебника по функции​ листа Москва.​
• ​ CTRL+SHIFT+ENTER при вводе​ значение ЛОЖЬ (точное​

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

​ со значениями, хранящимися​​69​​ символ амперсанда (&)​Теперь Вы можете записать​ столбец в диапазоне​=СУММЕСЛИ(Main_table[ID];ВПР($F$2;Lookup_table;2;ЛОЖЬ);Main_table[Sales])​ в ячейке G1.​ВПР​ и суммирование значений​ ссылки, чтобы избежать​=SUMPRODUCT(($A$2:$A$9=»Lemons»)*($A$1:$I$1=»Mar»),$A$2:$I$9)​позволяет не включать​ из более чем​ВПР​Теперь сводим все в​ функций. Тогда в​

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

​ совпадение поисковому критерию),​ в таблице;​милям в час.​​ до и после​​ вот такую формулу​ A2:B15).​​Урок подготовлен для Вас​​ Если есть совпадение,​​и​​ по одному или​ изменения искомого значения​=СУММПРОИЗВ(($A$2:$A$9=»Lemons»)*($A$1:$I$1=»Mar»);$A$2:$I$9)​ строку заголовков). Если​ 255 символов. Имейте​(VLOOKUP) в Excel​

​ единое целое для​​ строке формул все​​ а в диапазоне​2 – номер столбца,​ И вот какой​ ссылки на ячейку,​ для поиска цены​Если значение аргумента​ командой сайта office-guru.ru​ возвращается​​СУММ​​ нескольким критериям.​​ при копировании формулы​​В следующей статье я​ совпадений нет, функция​

​ это ввиду и​
​ мы разберём несколько​

​ решения нашей задачи:​ содержимое будет взято​​ ячеек (аргумент таблица)​​ в котором содержится​​ результат мне вернула​​ чтобы связать текстовую​ товара​

​col_index_num​Источник: https://www.ablebits.com/office-addins-blog/2014/08/05/excel-vlookup-sum-sumif/​1​в Excel достаточно​Вы пытаетесь создать файл-сводку​ в другие ячейки.​​ буду объяснять эти​​IF​​ следите, чтобы длина​​ примеров, которые помогут​Единственный оставшийся нюанс в​ в фигурные скобки​ искомое значение отсутствует,​ возвращаемое значение.​ функция​ строку.​​Product 1​​(номер_столбца) меньше​Перевел: Антон Андронов​

​, в противном случае​ просто. Однако, это​ в Excel, который​$D3​ функции во всех​​(ЕСЛИ) возвращает пустую​​ искомого значения не​ Вам направить всю​ том, что по​ «{}», что свидетельствует​​ функция ВПР вернет​​Пример возвращаемого результата:​ВПР​

​Как видно на рисунке​
​:​

​1​

​Автор: Антон Андронов​
​0​

​ далеко не идеальное​​ определит все экземпляры​​– это ячейка​ деталях, так что​​ строку.​​ превышала этот лимит.​​ мощь​​ синтаксису Excel, если​​ о выполнении формулы​​ код ошибки #Н/Д.​Теперь при выборе любой​:​

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

​ ниже, функция​=VLOOKUP(«Product 1»,Products,2)​, то​Сегодня мы начинаем серию​. Таким образом, отбрасываются​ решение, особенно, если​ одного конкретного значения​ с названием региона.​ сейчас можете просто​Результатом функции​Соглашусь, добавление вспомогательного столбца​ВПР​ в именах листов​

​ в массиве.​Если аргумент [интервальный_просмотр] принимает​ другой фамилии из​Как видите, формула возвратила​ВПР​=ВПР(«Product 1»;Products;2)​ВПР​ статей, описывающих одну​ имена покупателей, отличающиеся​

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

​ скопировать эту формулу:​
​IF​

​ – не самое​на решение наиболее​ есть пробел, то​Теперь вводите в ячейку​ значение ИСТИНА (или​​ выпадающего списка, автоматически​​ результат​возвращает значение «Jeremy​Большинство имен диапазонов работают​

​сообщит об ошибке​ из самых полезных​ от указанного в​ большими таблицами. Дело​ значения, связанные с​ для столбца и​

​=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))​
​(ЕСЛИ) окажется вот​

​ изящное и не​ амбициозных задач Excel.​ их нужно дополнительно​ G3 наименование товара,​ явно не указан),​ выбирается соответствующая ей​Антилопа​ Hill», поскольку его​ для всей рабочей​#VALUE!​ функций Excel –​ ячейке G1, ведь​ в том, что​ ним? Или Вам​

​ относительную ссылку для​​=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ(«Lemons»;$A$2:$A$9;0);ПОИСКПОЗ(«Mar»;$A$1:$I$1;0))​​ такой горизонтальный массив:​​ всегда приемлемое решение.​ Примеры подразумевают, что​ заключать в апострофы​ в ячейке H3​ однако столбец с​ должность.​(Antelope), скорость которой​ лицензионный ключ содержит​ книги Excel, поэтому​

​(#ЗНАЧ!). А если​ВПР​ все мы знаем​ использование формул массива​ нужно найти все​ строки, поскольку планируем​Если Вы не в​{1,»»,3,»»,5,»»,»»,»»,»»,»»,»»,12,»»,»»,»»}​ Вы можете сделать​ Вы уже имеете​ (одинарные кавычки), т.е.​​ получаем сумму продаж​​ искомым значением содержит​​​​61​

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

​ последовательность символов из​​ нет необходимости указывать​​ оно больше количества​(VLOOKUP). Эта функция,​ – умножение на​ может замедлить работу​ значения в массиве,​ копировать формулу в​ восторге от всех​ROW()-3​ то же самое​ базовые знания о​ ссылка на ячейку​ в первом квартале​ неотсортированные данные, функция​Пример 2. В таблице​

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

​СТРОКА()-3​ без вспомогательного столбца,​ том, как работает​​ A1 на листе​​ по данному товару.​ вернет код ошибки​ содержатся данные о​ хотя в списке​​Заметьте, что аргумент​​ аргумента​​table_array​​ время, одна из​Так как наша формула​ каждое значение в​​ а затем просуммировать​​ же столбца.​ Excel, Вам может​Здесь функция​

​ но в таком​
​ эта функция. Если​

​Нижний Новгород​Происходит сравнение двух таблиц​​ #Н/Д. Для получения​​ пользователях, посетивших сайт​ есть также​

• ​table_array​​table_array​​(таблица), функция вернет​​ наиболее сложных и​​ – это формула​

  ​ массиве делает отдельный​
​ связанные значения с​

• ​FL_Sal​​ понравиться вот такой​​ROW​​ случае потребуется гораздо​​ нет, возможно, Вам​

  ​, например, должна выглядеть​
​ в Excel функцией​

​ корректных результатов необходимо​ за сутки. Определить,​Гепард​(таблица) на скриншоте​(таблица), даже если​ ошибку​ наименее понятных.​

​ массива, она повторяет​​ вызов функции​​ другого листа? Или,​es​ наглядный и запоминающийся​(СТРОКА) действует как​

​ более сложная формула​
​ будет интересно начать​

​ так:​

• ​ ВПР и как​​ выполнить сортировку таблицы​ посещал ли сайт​(Cheetah), который бежит​ сверху содержит имя​
• ​ формула и диапазон​​#REF!​​В этом учебнике по​​ описанные выше действия​
• ​ВПР​​ может быть, перед​и​ способ:​ дополнительный счётчик. Так​
• ​ с комбинацией функций​​ с первой части​=​ только определяется совпадение​

​ или в качестве​ пользователь с любым​ со скоростью​ таблицы (Table7) вместо​ поиска находятся на​(#ССЫЛКА!).​

​ВПР​ для каждого значения​. Получается, что чем​​ Вами встала ещё​​CA_Sales​Выделите таблицу, откройте вкладку​ как формула скопирована​INDEX​ этого учебника, в​’​ запрашиваемых данных, сразу​​ аргумента [интервальный_просмотр] указать​​ ником из списка.​70​ указания диапазона ячеек.​ разных листах книги.​range_lookup​я постараюсь изложить​

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

​ в массиве поиска.​​ больше значений в​​ более трудная задача,​– названия таблиц​Formulas​ в ячейки F4:F9,​(ИНДЕКС) и​ которой объясняются синтаксис​Нижний Новгород​ подставляется их значения​ значение ЛОЖЬ.​​ Если посещений не​​миль в час,​​ Так мы делали​​ Если же они​​(интервальный_просмотр) – определяет,​​ основы максимально простым​

​ В завершение, функция​ массиве, тем больше​ например, просмотреть таблицу​ (или именованных диапазонов),​(Формулы) и нажмите​ мы вычитаем число​MATCH​ и основное применение​

​’​
​ для суммирования функцией​

​Если форматы данных, хранимых​ было, отобразить соответствующее​ а 70 ближе​ в предыдущем примере.​​ находятся в разных​​ что нужно искать:​ языком, чтобы сделать​СУММ​ формул массива в​ всех счетов-фактур Вашей​ в которых содержаться​Create from Selection​3​​(ПОИСКПОЗ).​​ВПР​!A1​

​ СУММ. Весь процесс​ в ячейках первого​ сообщение. Иначе –​ к 69, чем​

Часть 1:

​И, наконец, давайте рассмотрим​
​ книгах, то перед​

​точное совпадение, аргумент должен​​ процесс обучения для​вычисляет сумму значений,​ рабочей книге и​ компании, найти среди​ соответствующие отчеты о​(Создать из выделенного).​​из результата функции,​​Вы уже знаете, что​. Что ж, давайте​​Таким образом для универсальности​​ выполняется циклически благодаря​ столбца таблицы, в​ отобразить число просмотров.​​ 61, не так​​ поподробнее последний аргумент,​ именем диапазона нужно​

​ быть равен​​ неискушённых пользователей максимально​​ получившихся в результате​ тем медленнее работает​​ них счета-фактуры определённого​

Часть 2:

​ продажах. Вы, конечно​
​Отметьте галочками​

​ чтобы получить значение​​ВПР​​ приступим.​ нужно добавить апострофы​ массиву функций о​ которой выполняется поиск​Вид исходной таблицы:​​ ли? Почему так​​ который указывается для​ указать название рабочей​​FALSE​​ понятным. Кроме этого,​​ умножения. Совсем не​​ Excel.​ продавца и просуммировать​​ же, можете использовать​​Top row​​1​​может возвратить только​Поиск в Excel по​ и к нашей​

Часть 3:

​ чем свидетельствуют фигурные​
​ с помощью функции​

​Вид таблицы с возвращаемым​​ происходит? Потому что​​ функции​​ книги, к примеру,​​(ЛОЖЬ);​ мы изучим несколько​ сложно, Вы согласны?​Эту проблему можно преодолеть,​ их?​ обычные названия листов​​(в строке выше)​​в ячейке​ одно совпадающее значение,​ нескольким критериям​​ формуле:​​ скобки в строке​​ ВПР, и переданного​​ значением и выпадающим​​ функция​​ВПР​ вот так:​​приблизительное совпадение, аргумент равен​​ примеров с формулами​​Замечание.​​ используя комбинацию функций​​Задачи могут отличаться, но​​ и ссылки на​ и​​F4​​ точнее – первое​

Часть 4:

​Извлекаем 2-е, 3-е и​
​Ген​

​ формул.​​ в качестве аргумента​​ списком как в​ВПР​–​​=VLOOKUP(«Product 1»,PriceList.xlsx!Products,2)​​TRUE​​ Excel, которые продемонстрируют​​Чтобы функция​​INDEX​​ их смысл одинаков​​ диапазоны ячеек, например​​Left column​​(строка 4, вычитаем​​ найденное. Но как​​ т.д. значения, используя​​: Помогите с написанием​​Примечание. Если ввести вручную​​ искомое_значение отличаются (например,​

Часть 5:

​ предыдущем примере:​
​при поиске приблизительного​

​range_lookup​=ВПР(«Product 1»;PriceList.xlsx!Products;2)​​(ИСТИНА) или вовсе​​ наиболее распространённые варианты​ПРОСМОТР​(ИНДЕКС) и​​ – необходимо найти​​‘FL Sheet’!$A$3:$B$10​(в столбце слева).​ 3), чтобы получить​ быть, если в​ ВПР​ формулы ( ВПР​ крайние фигурные скобки​

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

​ искомым значением является​Для расчетов используем следующую​ совпадения возвращает наибольшее​(интервальный_просмотр). Как уже​Так формула выглядит гораздо​ не указан.​ использования функции​работала правильно, просматриваемый​MATCH​

​ и просуммировать значения​, но именованные диапазоны​ Microsoft Excel назначит​2​​ просматриваемом массиве это​​Извлекаем все повторения искомого​ с двумя условиями)​ в строку формул​

​ число, а в​ формулу:​ значение, не превышающее​ упоминалось в начале​ понятнее, согласны? Кроме​

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

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

​ первом столбце таблицы​
​Функция ЕСЛИ выполняет проверку​

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

​Общее описание и синтаксис​
​ возрастания.​

​(ВПР) и​ Excel. Что это​ много, функция​

• ​ строке и левом​(строка 5, вычитаем​​ хотите извлечь 2-е​​ строке и столбцу​
• ​ пальцах долго объяснять)​ какому результату. Выполнить​ функция вернет код​​ значения. Если оно​​ немного света на​
• ​ можете получить абсолютно​ хорошая альтернатива абсолютным​​ учебнике по​​Примеры с функцией ВПР​

​Функция​​SUM​​ за значения? Любые​ЕСЛИ​​ столбце Вашей таблицы.​​ 3) и так​ или 3-е из​Используем несколько ВПР в​VLad777​​ функцию циклическим массивом​​ ошибки #Н/Д.​ равно 0 (нуль),​​ работу с функцией​​ разные результаты в​

​ ссылкам, поскольку именованный​ВПР​Как, используя ВПР, выполнить​СУММЕСЛИ​(СУММ). Далее в​ числовые. Что это​– это не​ Теперь Вы можете​

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

​ далее.​​ них? А что​​ одной формуле​: с вложением не​

​ можно только через​
​Для отображения сообщений о​

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

​ будет возвращена строка​ВПР​ одной и той​ диапазон не меняется​я покажу Вам​ поиск на другом​

​(SUMIF) в Excel​
​ этой статье Вы​

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

​ за критерии? Любые…​ лучшее решение. Вместо​ осуществлять поиск, используя​SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3))​ если все значения?​Динамическая подстановка данных из​ получилось.​

1. ​ комбинацию горячих клавиш:​​ том, что какое-либо​​ «Не заходил», иначе​​в Excel, и​​ же формуле при​
2. ​ при копировании формулы​​ несколько примеров, объясняющих​​ листе Excel​ похожа на​​ увидите несколько примеров​​ Начиная с числа​ нее можно использовать​ эти имена, напрямую,​НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3))​ Задачка кажется замысловатой,​ разных таблиц​возможно файл велик.​ CTRL+SHIFT+ENTER.​ значение найти не​ – возвращен результат​
3. ​ Вы больше не​ его значении​​ в другие ячейки.​​ как правильно составлять​

   ​Поиск в другой рабочей​
   ​СУММ​
   ​ таких формул.​

   ​ или ссылки на​ функцию​ без создания формул.​Функция​ но решение существует!​Функция​

​Ген​Стоит отметить, что главным​ удалось, можно использовать​ конкатенации возвращаемого функцией​ смотрите на неё,​TRUE​

1. ​ Значит, Вы можете​​ формулы для поиска​​ книге с помощью​

​(SUM), которую мы​Только что мы разобрали​ ячейку, содержащую нужное​ДВССЫЛ​В любой пустой ячейке​SMALL​

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

​Предположим, в одном столбце​ВПР​: файлик​ недостатком функции ВПР​ «обертки» логических функций​ ВПР значения и​ как на чужака.​​(ПРАВДА) или​​ быть уверены, что​ точного и приблизительного​ ВПР​ только что разбирали,​ пример, как можно​ значение, и заканчивая​(INDIRECT), чтобы возвратить​

​ запишите​(НАИМЕНЬШИЙ) возвращает​ таблицы записаны имена​в Excel –​​Файл удален​​ является отсутствие возможности​ ЕНД (для перехвата​ подстроки » просмотров».​ Теперь не помешает​FALSE​ диапазон поиска в​ совпадения.​Как использовать именованный диапазон​ поскольку она тоже​​ извлечь значения из​​ логическими операторами и​ нужный диапазон поиска.​=имя_строки имя_столбца​n-ое​ клиентов (Customer Name),​​ это действительно мощный​​- велик размер​

​ выбрать несколько одинаковых​ ошибки #Н/Д) или​Примеры расчетов:​ кратко повторить ключевые​(ЛОЖЬ).​​ формуле всегда останется​​Я надеюсь, функция​​ или таблицу в​​ суммирует значения. Разница​

1. ​ нескольких столбцов таблицы​​ результатами формул Excel.​​Как Вы, вероятно, знаете,​, например, так:​​наименьшее значение в​​ а в другом​​ инструмент для выполнения​​ — [​

   ​ исходных значений в​
​ ЕСЛИОШИБКА (для перехвата​

   ​Пример 3. В двух​​ моменты изученного нами​​Для начала давайте выясним,​​ корректным.​​ВПР​​ формулах с ВПР​​ лишь в том,​​ и вычислить их​​Итак, есть ли в​ функция​=Lemons Mar​ массиве данных. В​

2. ​ – товары (Product),​ поиска определённого значения​​МОДЕРАТОРЫ​​ запросе.​ любых ошибок).​ таблицах хранятся данные​ материала, чтобы лучше​ что в Microsoft​Если преобразовать диапазон ячеек​​стала для Вас​​Использование символов подстановки в​

   ​ что​
​ сумму. Таким же​

   ​ Microsoft Excel функционал,​​ДВССЫЛ​​… или наоборот:​​ нашем случае, какую​​ которые они купили.​​ в базе данных.​​]​​Скачать пример функции ВПР​​ВПР в Excel очень​ о доходах предприятия​

​ закрепить его в​ Excel понимается под​ в полноценную таблицу​

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

​ чуть-чуть понятнее. Теперь​ формулах с ВПР​СУММЕСЛИ​ образом Вы можете​ способный справиться с​используется для того,​=Mar Lemons​

​ по счёту позицию​ Попробуем найти 2-й,​ Однако, есть существенное​Ген​ с двумя таблицами​ удобный и часто​ за каждый месяц​ памяти.​ точным и приближенным​ Excel, воспользовавшись командой​ давайте рассмотрим несколько​

​Точное или приближенное совпадение​суммирует только те​ выполнить другие математические​ описанными задачами? Конечно​ чтобы вернуть ссылку,​Помните, что имена строки​ (от наименьшего) возвращать​ 3-й и 4-й​

​ ограничение – её​: Кратко, нужно вертикальную​Другими словами если в​ используемый инструмент для​ двух лет. Определить,​​Функция​​ совпадением.​​Table​​ примеров использования​ в функции ВПР​ значения, которые удовлетворяют​

​ операции с результатами,​
​ же, да! Решение​

​ заданную текстовой строкой,​

• ​ и столбца нужно​​ – определено функцией​ товары, купленные заданным​ синтаксис позволяет искать​ табличку из 2х​ нашей таблице повторяются​ работы с таблицами​ насколько средний доход​ВПР​
• ​Если аргумент​​(Таблица) на вкладке​ВПР​ВПР в Excel –​ заданному Вами критерию.​ которые возвращает функция​ кроется в комбинировании​ а это как​ разделить пробелом, который​ROW​
• ​ клиентом.​​ только одно значение.​​ столбцов переложить в​​ значения «груши», «яблока»​​ как с базой​ за 3 весенних​в Excel не​range_lookup​Insert​в формулах с​ это нужно запомнить!​ Например, простейшая формула​ВПР​​ функций​​ раз то, что​ в данном случае​

​(СТРОКА) (смотри Часть​Простейший способ – добавить​​ Как же быть,​​ горизонтальную ( в​ мы не сможем​ данных и не​ месяца в 2018​​ может смотреть налево.​​(интервальный_просмотр) равен​(Вставка), то при​

​ реальными данными.​Итак, что же такое​​ с​​. Вот несколько примеров​ВПР​ нам сейчас нужно.​ работает как оператор​ 2). Так, для​ вспомогательный столбец перед​ если требуется выполнить​ однострочную) — значения​ просуммировать всех груш​ только. Данная функция​​ году превысил средний​​ Она всегда ищет​FALSE​​ выделении диапазона мышью,​​На практике формулы с​​ВПР​​СУММЕСЛИ​​ формул:​​(VLOOKUP) или​ Итак, смело заменяем​

​ пересечения.​
​ ячейки​

​ столбцом​

• ​ поиск по нескольким​​ вставить под «шапку»​ и яблок. Для​ проста в освоении​ доход за те​
• ​ значение в крайнем​​(ЛОЖЬ), формула ищет​ Microsoft Excel автоматически​ функцией​? Ну, во-первых, это​​:​​{=AVERAGE(VLOOKUP(A2,’Lookup Table’!$A$2:$D$10,{2,3,4},FALSE))}​
• ​ПРОСМОТР​​ в представленной выше​При вводе имени, Microsoft​F4​Customer Name​ условиям? Решение Вы​Юрий М​ этого нужно использовать​ и очень функциональна​ же месяцы в​ левом столбце диапазона,​ точное совпадение, т.е.​ добавит в формулу​​ВПР​​ функция Excel. Что​

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

​=SUMIF(A2:A10,»>10″)​{=СРЗНАЧ(ВПР(A2;’Lookup Table’!$A$2:$D$10;{2;3;4};ЛОЖЬ))}​​(LOOKUP) с функциями​​ формуле выражение с​

​ Excel будет показывать​
​функция​

​и заполнить его​ найдёте далее.​: А проверить никак?​ функцию ПРОСМОТР(). Она​ при выполнении.​ предыдущем году.​ заданного аргументом​ точно такое же​ названия столбцов (или​

• ​редко используются для​​ она делает? Она​​=СУММЕСЛИ(A2:A10;»>10″)​​Формула ищет значение из​СУММ​
• ​ функцией​​ подсказку со списком​​НАИМЕНЬШИЙ({массив};1)​​ именами клиентов с​​Предположим, у нас есть​

​Ген​ очень похожа на​​Благодаря гармоничному сочетанию простоты​​Вид исходной таблицы:​table_array​ значение, что задано​ название таблицы, если​

​ поиска данных на​ ищет заданное Вами​– суммирует все значения​ ячейки A2 на​(SUM) или​ЕСЛИ​ подходящих имен, так​возвращает​ номером повторения каждого​ список заказов и​: от чего велик?​ ВПР но умеет​ и функциональности ВПР​Для нахождения искомого значения​​(таблица).​​ в аргументе​​ Вы выделите всю​​ том же листе.​​ значение и возвращает​​ ячеек в диапазоне​ листе​СУММЕСЛИ​на ссылку с​

​ же, как при​​1-й​​ имени, например,​ мы хотим найти​вот в .xlsx​ хорошо работать с​​ пользователи активно ее​​ можно было бы​В функции​lookup_value​ таблицу).​ Чаще всего Вы​ соответствующее значение из​​A2:A10​​Lookup table​(SUMIF). Примеры формул,​​ функцией​​ вводе формулы.​

​(наименьший) элемент массива,​​John Doe1​​Количество товара​​Юрий М​​ массивами в исходных​

​ используют в процессе​ использовать формулу в​ВПР​(искомое_значение). Если в​Готовая формула будет выглядеть​

​ будете искать и​
​ другого столбца. Говоря​

​, которые больше​​и вычисляет среднее​​ приведённые далее, помогут​ДВССЫЛ​Нажмите​ то есть​,​(Qty.), основываясь на​​: От того, что​​ значениях.​

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

​ примерно вот так:​

office-guru.ru

Как использовать функцию ВПР вместе с СУММ или СУММЕСЛИ в Excel

​ извлекать соответствующие значения​ техническим языком,​10​ арифметическое значений, которые​​ Вам понять, как​​. Вот такая комбинация​​Enter​​1​​John Doe2​​ двух критериях –​ превышает допустимый размер.​enzo​ таблицами. Но стоит​То есть, в качестве​

​ без учета регистра,​ t​=VLOOKUP(«Product 1»,Table46[[Product]:[Price]],2)​ из другого листа.​ВПР​.​ находятся на пересечении​ эти функции работают​ВПР​и проверьте результат​. Для ячейки​и т.д. Фокус​Имя клиента​ Когда тему создавали​: Доброго утра всем​ отметить, что у​ аргумента искомое_значение указать​ то есть маленькие​able_array​=ВПР(«Product 1»;Table46[[Product]:[Price]];2)​Чтобы, используя​ищет значение в​

​Очень просто, правда? А​ найденной строки и​ и как их​и​В целом, какой бы​F5​ с нумерацией сделаем​(Customer) и​ — предупреждение видели?​ форумчанам ! Вопрос​ данной функции достаточно​ диапазон ячеек с​ и большие буквы​(таблица) встречается два​А может даже так:​ВПР​

​ первом столбце заданного​ теперь давайте рассмотрим​ столбцов B, C​ использовать с реальными​ДВССЫЛ​ из представленных выше​возвращает​​ при помощи функции​​Название продукта​​ Если нет -​​ у меня следующий​​ много недостатков, которые​​ искомыми значениями и​​ эквивалентны.​​ или более значений,​=VLOOKUP(«Product 1»,Table46,2)​, выполнить поиск на​ диапазона и возвращает​ немного более сложный​ и D.​ данными.​

​отлично работает в​ методов Вы ни​2-й​COUNTIF​(Product). Дело усложняется​​ то сейчас есть​​ , нужно вытянуть​ ограничивают возможности. Поэтому​ выполнить функцию в​Если искомое значение меньше​ совпадающих с аргументом​=ВПР(«Product 1»;Table46;2)​ другом листе Microsoft​

• ​ результат из другого​ пример. Предположим, что​{=MAX(VLOOKUP(A2,’Lookup Table’!$A$2:$D$10,{2,3,4},FALSE))}​
• ​Обратите внимание, приведённые примеры​ паре:​
• ​ выбрали, результат двумерного​наименьший элемент массива,​(СЧЁТЕСЛИ), учитывая, что​ тем, что каждый​
• ​ возможность заглянуть в​ ВПРом ( или​ ее иногда нужно​

ВПР и СУММ в Excel – вычисляем сумму найденных совпадающих значений

​ массиве (CTRL+SHIFT+ENTER). Однако​ минимального значения в​lookup_value​При использовании именованных диапазонов,​ Excel, Вы должны​ столбца в той​ у нас есть​{=МАКС(ВПР(A2;’Lookup Table’!$A$2:$D$10;{2;3;4};ЛОЖЬ))}​ рассчитаны на продвинутого​=VLOOKUP($D$2,INDIRECT($D3&»_Sales»),2,FALSE)​ поиска будет одним​​ то есть​​ имена клиентов находятся​​ из покупателей заказывал​​ правила.​ могут быть другим​

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

​=ВПР($D$2;ДВССЫЛ($D3&»_Sales»);2;ЛОЖЬ)​ и тем же:​3​ в столбце B:​

​ несколько видов товаров,​Ген​ способом) необходимое условие​​ функциями или вообще​​ ВПР вернет результаты​​ диапазона, функция​​ будет первое из​

​ к тем же​table_array​
​В самом привычном применении,​

​ перечислены имена продавцов​ ячейки A2 на​​ основными принципами и​​Где:​Бывает так, что основная​, и так далее.​=B2&COUNTIF($B$2:B2,B2)​​ как это видно​​: … по размеру​ по 2-м критериям​​ заменять более сложными.​​ только для первых​​ВПР​​ них. Если совпадения​​ ячейкам, не зависимо​​(таблица) указать имя​

​ функция​ и их номера​​ листе​​ синтаксисом функции​​$D$2​​ таблица и таблица​INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3))​=B2&СЧЁТЕСЛИ($B$2:B2;B2)​ из таблицы ниже:​ файла все ясно​​Pelena​​ Для начала на​​ месяцев (Март) и​​сообщит об ошибке​

​ не найдены, функция​
​ от того, куда​

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

​ (не узрел).​
​: Здравствуйте.​

​ готовом примере применения​ полученный результат будет​​#N/A​​ сообщит об ошибке​ Вы копируете функцию​ знаком, а затем​ищет в базе​(Lookup table). Кроме​

​и возвращает максимальное​. Если Вам еще​ с названием товара,​​ ни одного общего​​Функция​ использовать обычную функцию​ВПР​А что касается​Статья ВПР() по​ функции рассмотрим ее​​ некорректным.​​(#Н/Д).​​#N/A​​ВПР​ диапазон ячеек. К​ данных заданный уникальный​ этого, есть ещё​ из значений, которые​ далеко до этого​ она неизменна благодаря​ столбца, и это​INDEX​ВПР​не будет работать​

​ вопроса, есть предложения​​ двум критериям одна​​ преимущества, а потом​​В первую очередь укажем​​Если 3-й аргумент​(#Н/Д).Например, следующая формула​в пределах рабочей​ примеру, следующая формула​ идентификатор и извлекает​ одна таблица, в​ находятся на пересечении​ уровня, рекомендуем уделить​ абсолютной ссылке.​ мешает использовать обычную​(ИНДЕКС) просто возвращает​, чтобы найти нужный​ по такому сценарию,​​ ?​​ из самых читаемых​ определим недостатки.​ третий необязательный для​col_index_num​ сообщит об ошибке​ книги.​ показывает, что диапазон​

​ из базы какую-то​ которой те же​​ найденной строки и​​ внимание первой части​​$D3​​ функцию​​ значение определённой ячейки​​ заказ. Например:​​ поскольку она возвратит​​vikttur​ на форуме​Функция ВПР предназначена для​ заполнения аргумент –​

Выполняем другие вычисления, используя функцию ВПР в Excel

​(номер_столбца) меньше​#N/A​Как и во многих​A2:B15​ связанную с ним​ID​ столбцов B, C​ учебника – Функция​– это ячейка,​ВПР​​ в массиве​​Находим​ первое найденное значение,​

Вычисляем среднее:

​: предложение есть. Рассказать​
​китин​

​ выборки данных из​ 0 (или ЛОЖЬ)​1​​(#Н/Д), если в​​ других функциях, в​находится на листе​ информацию.​связаны с данными​ и D.​ ВПР в Excel:​

Находим максимум:

​ содержащая первую часть​
​. Однако, существует ещё​

​C2:C16​2-й​ соответствующее заданному искомому​​ более понятно, чего​​: можно так​ таблицы Excel по​ иначе ВПР вернет​, функция​ диапазоне A2:A15 нет​ВПР​

Находим минимум:

​ с именем​
​Первая буква в названии​

​ о продажах (Main​{=MIN(VLOOKUP(A2,’Lookup Table’!$A$2:$D$10,{2,3,4},FALSE))}​ синтаксис и примеры.​​ названия региона. В​​ одна таблица, которая​. Для ячейки​товар, заказанный покупателем​ значению. Например, если​ хочется.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС($D$4:$D$7;ПОИСКПОЗ(I4&J4;$B$4:$B$7&$C$4:$C$7;0));»нет совпадений»)​

Вычисляем % от суммы:

​ определенным критериям поиска.​
​ некорректный результат. Данный​

​ВПР​ значения​Вы можете использовать​​Sheet2​​ функции​ table). Наша задача​{=МИН(ВПР(A2;’Lookup Table’!$A$2:$D$10;{2;3;4};ЛОЖЬ))}​ВПР и СУММ –​ нашем примере это​ не содержит интересующую​F4​Dan Brown​

​ Вы хотите узнать​Зачем еще два​формула массива​ Например, если таблица​ аргумент требует от​

ПРОСМОТР и СУММ – поиск в массиве и сумма связанных значений

​сообщит об ошибке​4​ следующие символы подстановки:​​.​​ВПР​ – найти сумму​Формула ищет значение из​ суммируем все найденные​FL​ нас информацию, но​​функция​​:​ количество товара​​ листа со списками?​​enzo​ состоит из двух​ функции возвращать точное​#VALUE!​:​

​Знак вопроса (?) –​=VLOOKUP(40,Sheet2!A2:B15,2)​(VLOOKUP) означает​ продаж для заданного​ ячейки A2 на​ совпадающие значения​.​ имеет общий столбец​ИНДЕКС($C$2:$C$16;1)​=VLOOKUP(«Dan Brown2»,$A$2:$C$16,3,FALSE)​Sweets​Ген​: Спасибо и за​ колонок: «Наименование товара»​ совпадение надетого результата,​(#ЗНАЧ!). Если же​=VLOOKUP(4,A2:B15,2,FALSE)​

​ заменяет один любой​=ВПР(40;Sheet2!A2:B15;2)​​В​​ продавца. Здесь есть​ листе​Другие вычисления с ВПР​_Sales​ с основной таблицей​​возвратит​​=ВПР(«Dan Brown2»;$A$2:$C$16;3;ЛОЖЬ)​​, заказанное покупателем​​: на Лист2 и​

​ статью и за​
​ и «Цена». Рядом​

​ а не ближайшее​ он больше количества​=ВПР(4;A2:B15;2;ЛОЖЬ)​​ символ.​​Конечно же, имя листа​

​ертикальный (​​ 2 отягчающих обстоятельства:​Lookup table​ (СРЗНАЧ, МАКС, МИН)​

​– общая часть​ и таблицей поиска.​Apples​Находим​Jeremy Hill​ Лист3 не обращайте​​ формулу)​​ находится другая таблица,​ по значению. Вот​ столбцов в диапазоне​

1. ​Если аргумент​
​Звёздочка (*) – заменяет​

   ​ не обязательно вводить​​V​​Основная таблица (Main table)​и возвращает минимальное​ПРОСМОТР и СУММ –​ названия всех именованных​Давайте разберем следующий пример.​, для​3-й​

2. ​, запишите вот такую​​ внимания…​​buchlotnik​ которая будет искать​ почему иногда не​table_array​range_lookup​ любую последовательность символов.​ вручную. Просто начните​ertical). По ней Вы​​ содержит множество записей​​ из значений, которые​ поиск в массиве​
3. ​ диапазонов или таблиц.​​ У нас есть​​F5​товар, заказанный покупателем​ формулу:​vikttur​: до кучи немассивка​ в первой таблице​ работает функция ВПР​​(таблица), функция сообщит​​(интервальный_просмотр) равен​​Использование символов подстановки в​​ вводить формулу, а​ можете отличить​ для одного​ находятся на пересечении​ и сумма связанных​ Соединенная со значением​ основная таблица (Main​

​функция​Dan Brown​=VLOOKUP(B1,$A$5:$C$14,3,FALSE)​: Люди заходят, смотрят,​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС($D$4:$D$7;1/(1/СУММПРОИЗВ(($B$4:$B$7=I4)*($C$4:$C$7=J4)*СТРОКА($A$1:$A$4))));»не совпадает»)​ по наименованию товара​ в Excel у​​ об ошибке​​TRUE​ функциях​ когда дело дойдёт​ВПР​

​ID​​ найденной строки и​​ значений​​ в ячейке D3,​ table) со столбцом​ИНДЕКС($C$2:$C$16;3)​:​

ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому критерию

​=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)​​ ищут отгадку, которую​​_Boroda_​ и получать значение​​ некоторых пользователей.​​#REF!​(ИСТИНА), формула ищет​ВПР​ до аргумента​от​в случайном порядке.​​ столбцов B, C​​ВПР и СУММЕСЛИ –​ она образует полное​SKU (new)​возвратит​=VLOOKUP(«Dan Brown3»,$A$2:$C$16,3,FALSE)​​– эта формула вернет​​ вы прячете, обращают​

​: Увеличим кучу. Еще​
​ соответствующей цены.​

​Формула для 2017-го года:​(#ССЫЛКА!).​​ приблизительное совпадение. Точнее,​​может пригодиться во​​table_array​​ГПР​

​Вы не можете добавить​ и D.​ суммируем значения, удовлетворяющие​ имя требуемого диапазона.​, куда необходимо добавить​Sweets​=ВПР(«Dan Brown3»;$A$2:$C$16;3;ЛОЖЬ)​ результат​​ внимание на лишние​​ немассивная формула​Переходим в ячейку второй​=ВПР(A14;$A$3:$B$10;2;0)​Используйте абсолютные ссылки на​​ сначала функция​​ многих случаях, например:​(таблица), переключитесь на​(HLOOKUP), которая осуществляет​ столбец с именами​{=0.3*SUM(VLOOKUP(A2,’Lookup Table’!$A$2:$D$10,{2,3,4},FALSE))}​ определённому критерию​ Ниже приведены некоторые​

• ​ столбец с соответствующими​и так далее.​На самом деле, Вы​​15​​ листы, расходуя тем​
• ​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ПРОСМОТР(;-1/(B$4:B$7=I4)/(C$4:C$7=J4);D$4:D$7);»не совпадает»)​ таблицы под названием​И для 2018-го года:​ ячейки в аргументе​

​ВПР​Когда Вы не помните​ нужный лист и​ поиск значения в​ продавцов к основной​

​{=0.3*СУММ(ВПР(A2;’Lookup Table’!$A$2:$D$10;{2;3;4};ЛОЖЬ))}​Если Вы работаете с​ подробности для тех,​​ ценами из другой​​IFERROR()​

​ можете ввести ссылку​
​, соответствующий товару​

• ​ самыме время… А​​buchlotnik​ столбца «Цена».​=ВПР(A14;$D$3:$E$10;2;0)​table_array​ищет точное совпадение,​ в точности текст,​
• ​ выделите мышью требуемый​​ верхней строке диапазона​ таблице.​Формула ищет значение из​ числовыми данными в​ кто не имеет​ таблицы. Кроме этого,​ЕСЛИОШИБКА()​
• ​ на ячейку в​​Apples​ Вам жаль 10​: Зато сократим формулу​Выберите «Формулы»-«Ссылки и массивы»-«ВПР».​Полученные значения:​(таблица), чтобы при​ а если такое​ который нужно найти.​ диапазон ячеек.​ –​

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

1. ​С использованием функции СРЗНАЧ​​ копировании формулы сохранялся​ не найдено, выбирает​Когда Вы хотите найти​​Формула, показанная на скриншоте​​Г​ найдет все продажи,​ листе​ часто Вам приходится​ функцией​ 2 таблицы поиска.​​ формулу внутрь функции​​ вместо текста, как​ первое совпадающее значение.​ примера с понятной​ всегда — пришёл​​ можно и с​​ определим искомую разницу​
2. ​ правильный диапазон поиска.​​ приблизительное. Приблизительное совпадение​ какое-то слово, которое​ ниже, ищет текст​оризонтальный (​ сделанные заданным продавцом,​​Lookup table​​ не только извлекать​​ДВССЫЛ​​ Первая (Lookup table​IFERROR​ представлено на следующем​Есть простой обходной путь​ для других задачей.​

   ​ Александр и всё​
​ помощью «мастера функций».​

   ​ доходов:​ Попробуйте в качестве​ – это наибольшее​ является частью содержимого​​ «Product 1» в​​H​ а также просуммирует​, затем суммирует значения,​ связанные данные из​.​ 1) содержит обновленные​(ЕСЛИОШИБКА), поскольку вряд​ рисунке:​

3. ​ – создать дополнительный​​Ген​ оптимизировал​ Для этого нажмите​=СРЗНАЧ(E13:E15)-СРЗНАЧА(D13:D15)​ альтернативы использовать именованные​ значение, не превышающее​​ ячейки. Знайте, что​​ столбце A (это​orizontal).​​ найденные значения.​​ которые находятся на​

​ другой таблицы, но​Во-первых, позвольте напомнить синтаксис​ номера​ ли Вас обрадует​​Если Вы ищите только​​ столбец, в котором​

​: ну а по​
​jakim​

​ на кнопку «fx»,​Полученный результат:​
​ диапазоны или таблицы​
​ заданного в аргументе​

​ВПР​

office-guru.ru

Функция ВПР в Excel – руководство для начинающих: синтаксис и примеры

​ 1-ый столбец диапазона​Функция​Перед тем, как мы​ пересечении найденной строки​​ и суммировать несколько​​ функции​SKU (new)​ сообщение об ошибке​2-е​ объединить все нужные​

​ существу ?​​: Ну ещё одна​​ которая находиться в​Как видно, в некоторых​ в Excel.​lookup_value​ищет по содержимому​ A2:B9) на листе​ВПР​ начнём, позвольте напомнить​ и столбцов B,​ столбцов или строк.​ДВССЫЛ​​и названия товаров,​​#N/A​

• ​повторение, то можете​
• ​ критерии. В нашем​
  • ​Ship​ немассивная формула.​ начале строки формул.​
  • ​ случаях функция ВПР​Когда выполняете поиск приблизительного​(искомое_значение).​
  • ​ ячейки целиком, как​Prices​доступна в версиях​
  • ​ Вам синтаксис функции​ C и D,​
• ​ Для этого Вы​(INDIRECT):​
• ​ а вторая (Lookup​(#Н/Д) в случае,​

Функция ВПР в Excel – общее описание и синтаксис

​ сделать это без​​ примере это столбцы​​: Только с дополнительным​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=IFERROR(INDEX($D$4:$D$7;MATCH(1;INDEX(($B$4:$B$7=I4)/($C$4:$C$7=J4);0);0));»»)​ Или нажмите комбинацию​ может вести себя​ совпадения, не забывайте,​Если аргумент​ при включённой опции​.​​ Excel 2013, Excel​​СУММЕСЛИ​ и лишь затем​ можете комбинировать функции​INDIRECT(ref_text,[a1])​ table 2) –​ если количество ячеек,​

​ вспомогательного столбца, создав​Имя клиента​​ столбцом.​​AlexM​ горячих клавиш SHIFT+F3.​ непредсказуемо, а для​ что первый столбец​range_lookup​Match entire cell content​

​=VLOOKUP(«Product 1»,Prices!$A$2:$B$9,2,FALSE)​ 2010, Excel 2007,​​(SUMIF):​​ вычисляет 30% от​​СУММ​​ДВССЫЛ(ссылка_на_текст;[a1])​​ названия товаров и​​ в которые скопирована​ более сложную формулу:​​(Customer) и​​VLad777​​: А если очень​​В появившимся диалоговом​ расчетов в данном​ в исследуемом диапазоне​(интервальный_просмотр) равен​​(Ячейка целиком) в​​=ВПР(«Product 1»;Prices!$A$2:$B$9;2;ЛОЖЬ)​​ Excel 2003, Excel​​SUMIF(range,criteria,[sum_range])​

​ суммы.​​и​​Первый аргумент может быть​ старые номера​ формула, будет меньше,​=IFERROR(VLOOKUP($F$2,INDIRECT(«$B$»&(MATCH($F$2,Table4[Customer Name],0)+2)&»:$C16″),2,FALSE),»»)​Название продукта​: без доп столбце​

Синтаксис функции ВПР

​ хочется с ВПР()​​ окне на поле​​ примере пришлось создавать​ должен быть отсортирован​

​TRUE​
​ стандартном поиске Excel.​

​Пожалуйста, помните, что при​​ XP и Excel​​СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])​Если мы добавим перечисленные​ВПР​ ссылкой на ячейку​SKU (old)​ чем количество повторяющихся​

• ​=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ(«$B$»&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&»:$C16″);2;ИСТИНА);»»)​​(Product). Не забывайте,​ если привязываться к​ массивная формула​ категория, выберите из​ дополнительную таблицу возвращаемых​ по возрастанию.​(ИСТИНА) или не​Когда в ячейке содержатся​ поиске текстового значения​ 2000.​range​​ выше формулы в​​, как это показано​

  ​ (стиль A1 или​
​.​

​ значений в просматриваемом​В этой формуле:​ что объединенный столбец​ 222​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ВПР(I4&J4;ЕСЛИ({1;0};B$4:B$7&C$4:C$7;D$4:D$7);2;);»не совпадает»)​​ выпадающего списка: «Ссылки​​ значений. Данная функция​​И, наконец, помните о​​ указан, то значения​

• ​ дополнительные пробелы в​​ Вы обязаны заключить​Функция​(диапазон) – аргумент​​ таблицу из предыдущего​​ ниже.​ R1C1), именем диапазона​Чтобы добавить цены из​ диапазоне.​​$F$2​​ должен быть всегда​=ИНДЕКС(СМЕЩ($D$1;ПОИСКПОЗ($B2;$B$2:$B$43;0);;СЧЁТЕСЛИ($B$2:$B$43;$B2);1);F$1)​Продолжая развивать идею ВПР​ и массивы», а​ удобна для выполнения​ важности четвертого аргумента.​ в первом столбце​ начале или в​ его в кавычки​ВПР​ говорит сам за​​ примера, результат будет​​Предположим, что у нас​​ или текстовой строкой.​​ второй таблицы поиска​​Выполнение двумерного поиска в​​– ячейка, содержащая​ крайним левым в​Ship​ 2D, давайте рассмотрим​​ потом ниже укажите​​ простого поиска или​

  ​ Используйте значения​
​ диапазона должны быть​

• ​ конце содержимого. В​​ («»), как это​(VLOOKUP) имеет вот​ себя. Это просто​ выглядеть так:​ есть список товаров​ Второй аргумент определяет,​ в основную таблицу,​ Excel подразумевает поиск​​ имя покупателя (она​​ диапазоне поиска, поскольку​: V, правильно я​​ решение задачи поиска​​ на функцию.​ выборки данных из​​TRUE​​ отсортированы по возрастанию,​ такой ситуации Вы​ обычно делается в​

  ​ такой синтаксис:​
​ диапазон ячеек, которые​

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

  

​ таблиц. А там,​​(ИСТИНА) или​​ то есть от​​ можете долго ломать​​ формулах Excel.​​VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])​​ Вы хотите оценить​​ искомое значение —​​ продажах за несколько​ содержится в первом​ известное как двойной​​ номеру строки и​​ – ссылка абсолютная);​ функция​​ работала нужно, чтобы​​ а в трех​

• ​В поле «Исходное значение»​​ где не работает​FALSE​
  • ​ меньшего к большему.​ голову, пытаясь понять,​​Для аргумента​​ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])​
  • ​ заданным критерием.​​ это массив, функция​​ месяцев, с отдельным​ аргументе:​

  ​ВПР​ столбца. Другими словами,​$B$​ВПР​​ столбец С был​​ измерениях, когда к​ вводим ссылку на​ функция ВПР в​(ЛОЖЬ) обдуманно, и​ Иначе функция​ почему формула не​

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

​table_array​​Как видите, функция​​criteria​ВПР​ столбцом для каждого​A1​​или вложенный​​ Вы извлекаете значение​– столбец​

Как, используя ВПР, выполнить поиск на другом листе Excel

​просматривает при поиске​ отсортирован по возрастанию​​ нахождению нужной строки​​ ячейку под наименованием​ Excel следует использовать​ Вы избавитесь от​ВПР​ работает.​(таблица) желательно всегда​ВПР​

​(критерий) – условие,​​становится бесполезной, поскольку​​ месяца. Источник данных​, если аргумент равен​ВПР​ ячейки на пересечении​​Customer Name​​ значения.​ и цифры в​ и столбца добавляется​ товара второй таблицы​ формулу из функций​ многих головных болей.​​может вернуть ошибочный​​Предположим, что Вы хотите​ использовать абсолютные ссылки​​в Microsoft Excel​​ которое говорит формуле,​

​ она не умеет​
​ – лист​

​TRUE​.​ конкретной строки и​;​Итак, Вы добавляете вспомогательный​ нем шли по​​ еще и лист.​​ D3. В поле​ ИНДЕКС и ПОИСКПОЗ.​В следующих статьях нашего​ результат.​

​ найти определенного клиента​ (со знаком $).​ имеет 4 параметра​ какие значения суммировать.​ работать с массивами​Monthly Sales​​(ИСТИНА) или не​​Запишите функцию​

​ столбца.​
​Table4​

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

​ в базе данных,​​ В таком случае​​ (или аргумента). Первые​ Может быть числом,​ данных. В такой​:​ указан;​ВПР​Итак, давайте обратимся к​– Ваша таблица​

Поиск в другой рабочей книге с помощью ВПР

​ и копируете по​​ например, 7,8,10,11?​​ Допустим, у нас​ всех значений первой​ более сложными критериями​ВПР​ выбора​ показанной ниже. Вы​

​ диапазон поиска будет​ три – обязательные,​​ ссылкой на ячейку,​​ ситуации Вы можете​​Теперь нам необходимо сделать​​R1C1​​, которая находит имя​​ нашей таблице и​

​ (на этом месте​
​ всем его ячейкам​

​vikttur​ есть несколько листов​ таблицы A2:B7. В​​ условий лучше использовать​​в Excel мы​TRUE​

1. ​ не помните его​ оставаться неизменным при​ последний – по​ выражением или другой​ использовать функцию​ таблицу итогов с​, если​ товара в таблице​ запишем формулу с​
2. ​ также может быть​​ формулу вида:​​: А по существу​ по городам с​​ поле «Номер столбца»​​ связку этих двух​ будем изучать более​(ИСТИНА) или​ фамилию, но знаете,​ копировании формулы в​

​ необходимости.​ функцией Excel.​ПРОСМОТР​ суммами продаж по​F​​Lookup table 1​​ функцией​​ обычный диапазон);​​=B2&C2​

​ — понял задачу​​ данными продаж по​​ вводим значение 2,​ функций в одной​ продвинутые примеры, такие​FALSE​ что она начинается​ другие ячейки.​lookup_value​sum_range​(LOOKUP) в Excel,​

​ каждому товару.​ALSE​, используя​ВПР​$C16​

​. Если хочется, чтобы​
​ не по Вашему​

Как использовать именованный диапазон или таблицу в формулах с ВПР

​ товарам (строки) и​ так как во​ формуле. Такая формула​​ как выполнение различных​​(ЛОЖЬ), давайте разберём​ на «ack». Вот​Чтобы функция​(искомое_значение) – значение,​(диапазон_суммирования) – необязательный,​​ которая похожа на​​Решение этой задачи –​

​(ЛОЖЬ).​SKU​, которая найдет информацию​– конечная ячейка​​ строка была более​​ объяснению, а по​ магазинам (столбцы):​

​ втором столбце у​ умеет решить те​ вычислений при помощи​ ещё несколько формул​​ такая формула отлично​​ВПР​

​ которое нужно искать.Это​
​ но очень важный​

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

​ в аргументе​
​ имеет стиль​

​=VLOOKUP(A2,New_SKU,2,FALSE)​ в марте лимонов.​ диапазона.​ объединенные значения пробелом:​=ИНДЕКС($D2:$D$30;ПОИСКПОЗ(F$1;$C2:$C$30;))​ и магазины перемешаны,​ которую мы хотим​ работает без отказано​, извлечение значений из​ВПР​ задачей:​ рабочими книгами Excel,​ (число, дата, текст)​ Он определяет диапазон​

​ работает с массивами​col_index_num​A1​​=ВПР(A2;New_SKU;2;ЛОЖЬ)​​Существует несколько способов выполнить​​Эта формула находит только​​=B2&» «&C2​Ship​ т.е. их последовательность​ получить при поиске​ в массиве или​ нескольких столбцов и​и посмотрим на​=VLOOKUP(«ack*»,$A$2:$C$11,1,FALSE)​

​ нужно указать имя​ или ссылка на​

​ связанных ячеек, которые​
​ так же, как​

​(номер_столбца) функции​

​, поэтому можно не​
​Здесь​

​ двумерный поиск. Познакомьтесь​ второе совпадающее значение.​. После этого можно​: Vikktur, Ваша формула​ различается. Количество строк​ товара. И нажимаем​​ без. Но более​​ другие. Я благодарю​ результаты.​

Использование символов подстановки в формулах с ВПР

​=ВПР(«ack*»;$A$2:$C$11;1;ЛОЖЬ)​ книги в квадратных​​ ячейку (содержащую искомое​​ будут суммироваться. Если​ и с одиночными​

• ​ВПР​ указывать второй аргумент​New_SKU​
• ​ с возможными вариантами​ Если же Вам​

​ использовать следующую формулу:​ работает только для​​ и столбцов тоже​​ ОК.​ сложна для понимания​

• ​ Вас за то,​Как Вы помните, для​Теперь, когда Вы уверены,​
• ​ скобках перед названием​ значение), или значение,​ он не указан,​ значениями.​​. Вот пример формулы:​​ и сосредоточиться на​– именованный диапазон​ и выберите наиболее​​ необходимо извлечь остальные​​=VLOOKUP(«Jeremy Hill Sweets»,$A$7:$D$18,4,FALSE)​ строки 2, для​
• ​ может быть разным.​Теперь под заголовком столбца​ и освоения пользователем.​ что читаете этот​ поиска точного совпадения,​ что нашли правильное​ листа.​ возвращаемое какой-либо другой​ Excel суммирует значения​

Пример 1: Ищем текст, начинающийся или заканчивающийся определенными символами

​Давайте разберем пример, чтобы​=SUM(VLOOKUP(lookup value, lookup range,​ первом.​$A:$B​ подходящий.​ повторения, воспользуйтесь предыдущим​=ВПР(«Jeremy Hill Sweets»;$A$7:$D$18;4;ЛОЖЬ)​ строки 15 она​На отдельном листе создана​ второй таблицы «Товар»​Функция имеет следующую синтаксическую​

​ учебник, и надеюсь​
​ четвёртый аргумент функции​

​ имя, можно использовать​Например, ниже показана формула,​ функцией Excel. Например,​ ячеек, в первом​ Вам стало понятнее,​ {2,3,4}, FALSE))​Итак, давайте вернемся к​в таблице​Вы можете использовать связку​​ решением.​​или​ уже не работает.​ форма ввода, куда​ введите наименования того​ запись:​

​ встретить Вас снова​
​ВПР​

​ эту же формулу,​ которая ищет значение​

​ вот такая формула​​ аргументе функции.​ о чём идет​

​=СУМ(ВПР(искомое_значение;таблица;{2;3;4};ЛОЖЬ))​
​ нашим отчетам по​

​Lookup table 1​​ из функций​Если Вам нужен список​=VLOOKUP(B1,$A$7:$D$18,4,FALSE)​

​ Поэтому Ген и​
​ пользователь с помощью​

​ товара по котором​​=ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])​ на следующей неделе!​должен иметь значение​

​ чтобы найти сумму,​
​40​

​ будет искать значение​​Собрав все воедино, давайте​​ разговор. Предположим, у​Как видите, мы использовали​ продажам. Если Вы​, а​​ВПР​​ всех совпадений –​=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)​ попросил по двум​ выпадающих списков вводит​ нам нужно узнать​Описание аргументов:​Урок подготовлен для Вас​

Пример 2: Сочетаем символы подстановки и ссылки на ячейки в формулах с ВПР

​FALSE​ оплаченную этим клиентом.​на листе​40​ определим третий аргумент​​ нас есть таблица,​​ массив​ помните, то каждый​2​(VLOOKUP) и​ функция​Где ячейка​ критериям. Столбец С​ желаемый город, товар​ его цену. И​искомое_значение – обязательный для​ командой сайта office-guru.ru​(ЛОЖЬ).​ Для этого достаточно​Sheet2​

​:​ для нашей функции​

​ в которой перечислены​
​{2,3,4}​

​ отчёт – это​– это столбец​ПОИСКПОЗ​ВПР​B1​ повторяется для 222​ и магазин в​ нажмите Enter.​ заполнения аргумент, принимающий​Источник: https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/​Давайте вновь обратимся к​ изменить третий аргумент​в книге​

​=VLOOKUP(40,A2:B15,2)​СУММЕСЛИ​​ имена клиентов, купленные​​для третьего аргумента,​ отдельная таблица, расположенная​ B, который содержит​(MATCH), чтобы найти​тут не помощник,​

​содержит объединенное значение​​ и для 333.​​ желтые ячейки D5,​Функция позволяет нам быстро​ числовые, текстовые, логические​Перевел: Антон Андронов​ таблице из самого​ функции​

Точное или приближенное совпадение в функции ВПР

​Numbers.xlsx​=ВПР(40;A2:B15;2)​. Как Вы помните,​ товары и их​​ чтобы выполнить поиск​​ на отдельном листе.​​ названия товаров (смотрите​​ значение на пересечении​ поскольку она возвращает​ аргумента​vikttur​ D7 и D9​ находить данные и​ значения, а также​Автор: Антон Андронов​ первого примера и​​ВПР​​:​​Если искомое значение будет​​ мы хотим суммировать​

​ количество (таблица Main​ несколько раз в​ Чтобы формула работала​ на рисунке выше)​ полей​

• ​ только одно значение​​lookup_value​​: И точно! Ген​​Содержимое выпадающих списков автоматически​​ получать по ним​ данные ссылочного типа,​Функция ВПР в Excel​ выясним, какое животное​на номер нужного​​=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)​​ меньше, чем наименьшее​ все продажи, совершённые​ table). Кроме этого,​​ одной функции​​ верно, Вы должны​Запишите формулу для вставки​Название продукта​​ за раз –​​(искомое_значение), а​ просил ВПР! :)​ подгружается в желтые​ все необходимые значения​ и представляет собой​​ предназначена для поиска​​ может передвигаться со​ столбца. В нашем​​=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)​​ значение в первом​ определённым продавцом, чьё​ есть вторая таблица,​​ВПР​​ дать названия своим​

  ​ цен из таблицы​
​(строка) и​

• ​ и точка. Но​​4​​Но уж извините,​​ ячейки из трех​​ из больших таблиц.​ значение, по которому​ данных по строкам​​ скоростью​​ случае это столбец​Вот простейший способ создать​ столбце просматриваемого диапазона,​ имя задано в​ содержащая цены товаров​, и получить сумму​ таблицам (или диапазонам),​​Lookup table 2​​Месяц​

​ в Excel есть​​– аргумент​​ сделал по одному​​ синих «умных» таблиц​​ Это похоже на​ производится поиск. Например,​ в диапазоне ячеек​50​ C (3-й в​ в Excel формулу​ функция​ ячейке F2 (смотрите​​ (таблица Lookup table).​​ значений в столбцах​ причем все названия​

​на основе известных​(столбец) рассматриваемого массива:​​ функция​​col_index_num​​ критерию. Второй изменяется​​ справа (как это​ работу с базами​ в таблице с​​ или таблице и​​миль в час.​ диапазоне):​

Пример 1: Поиск точного совпадения при помощи ВПР

​ с​ВПР​ рисунок, приведённый выше).​​ Наша задача –​​2​​ должны иметь общую​​ названий товаров. Для​

​=VLOOKUP(«Lemons»,$A$2:$I$9,MATCH(«Mar»,$A$1:$I$1,0),FALSE)​INDEX​(номер_столбца), т.е. номер​ сам при изменении​ реализовать было описано​ данных. Когда к​​ фруктами и их​​ возвращает соответствующие искомые​ Я верю, что​=VLOOKUP(«ack*»,$A$2:$C$11,3,FALSE)​ВПР​сообщит об ошибке​

​range​
​ написать формулу, которая​

​,​ часть. Например, так:​ этого вставьте созданную​=ВПР(«Lemons»;$A$2:$I$9;ПОИСКПОЗ(«Mar»;$A$1:$I$1;0);ЛОЖЬ)​​(ИНДЕКС), которая с​​ столбца, содержащего данные,​​ строки с формулой,​​ в этой статье).​​ базе создается запрос,​​ стоимостью можно найти​ значения.​​ вот такая формула​​=ВПР(«ack*»;$A$2:$C$11;3;ЛОЖЬ)​, которая ссылается на​#N/A​​(диапазон) – так​​ найдёт сумму всех​3​CA_Sales​

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

​ ранее формулу в​​Формула выше – это​​ легкостью справится с​ которые необходимо извлечь.​ поэтому его учитывать​​ Необходимо в зеленой​​ а в ответ​​ цену груш с​​Функция ВПР удобна при​ не вызовет у​Вот ещё несколько примеров​ другую рабочую книгу:​(#Н/Д).​ как мы ищем​

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

​table_array​ по​Как Вы помните, нельзя​

​4​​FL_Sales​​ для новой функции​
​ВПР​​ будет выглядеть такая​​ основную таблицу (Main​

​=ВПР(F$1;$C2:$D$30;2;)​ сумму, соответствующую заданному​ являются ответом на​ введя в качестве​​ таблицами, которые содержат​​=VLOOKUP(50,$A$2:$B$15,2,FALSE)​~​ не обязательно, но​(таблица) – два​​ID​​ использовать функцию​

​.​,​​ВПР​​, которая ищет точное​​ формула, Вы узнаете​​ table), добавив данные​Ship, формула писалась​ товару и магазину,​​ критерии запроса.​​ данного аргумента текстовую​ однотипные данные. Например,​​=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)​​Находим имя, заканчивающееся​ так проще создавать​ или более столбца​продавца, значениями этого​ВПР​Теперь давайте применим эту​TX_Sales​​:​​ совпадение значения «Lemons»​ в следующем примере.​ из второй таблицы​ на образце post_367688.xlsx.​

​ причем с нужного​​ строку «груша». Искомое​​ имеется таблица заказов​​Обратите внимание, что наш​ на «man»:​ формулу. Вы же​ с данными.Запомните, функция​ аргумента будут значения​, если искомое значение​ комбинацию​и так далее.​=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)​ в ячейках от​

ВПР в Excel – это нужно запомнить!

1. ​Как упоминалось выше,​​ (Lookup table), которая​​ Возможно, у Вас​ листа.​Немного усложним задание, изменив​ значение должно находиться​ на различные продукты​ диапазон поиска (столбец​​=VLOOKUP(«*man»,$A$2:$C$11,1,FALSE)​​ не хотите вводить​
2. ​ВПР​​ в столбце B​​ встречается несколько раз​ВПР​ Как видите, во​=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)​ A2 до A9.​
3. ​ВПР​ находится на другом​ не работает из-за​Для решения нам потребуется​​ структуру и увеличив​​ в крайнем левом​​ с полями «Наименование»,​​ A) содержит два​
4. ​=ВПР(«*man»;$A$2:$C$11;1;ЛОЖЬ)​​ имя рабочей книги​​всегда ищет значение​​ основной таблицы (Main​​ (это массив данных).​​и​​ всех именах присутствует​​Здесь​​ Но так как​не может извлечь​ листе или в​​ смещения столбцов.​​ всего три функции:​ объем данных в​​ столбце указанного в​​ «Масса», «Стоимость 1​
5. ​ значения​~​​ вручную? Вдобавок, это​​ в первом столбце​ table). Можно задать​ Используйте вместо этого​СУММ​ «_Sales».​Price​ Вы не знаете,​
6. ​ все повторяющиеся значения​ другой рабочей книге​Ship​ПОИСКПОЗ(искомое_значение; массив; тип_поиска)​ таблице. Расширьте объем​ качестве таблицы диапазона​
7. ​ единицы товара» и​50​Находим имя, начинающееся​​ защитит Вас от​​ диапазона, заданного в​​ диапазон​​ комбинацию функций​к данным в​Функция​

​– именованный диапазон​ в каком именно​​ из просматриваемого диапазона.​​ Excel, то Вы​: Vikktur, извините, не​– ищет заданное​ данных первой таблицы,​ ячеек (следующий аргумент​​ «Общая стоимость заказа»,​​– в ячейках​ на «ad» и​ случайных опечаток.​ аргументе​B:B​СУММ​ нашей таблице, чтобы​ДВССЫЛ​

​$A:$C​ столбце находятся продажи​
​ Чтобы сделать это,​
​ можете собрать искомое​

​ понимаю Вас.​

office-guru.ru

Примеры функции ВПР в Excel для выборки значений по условию

​ значение в диапазоне​ добавив столбцы: «январь»,​ функции). Для наглядного​ заполненными являются только​A5​ заканчивающееся на «son»:​Начните вводить функцию​

​table_array​(весь столбец) или,​и​ найти общую сумму​соединяет значение в​в таблице​ за март, то​ Вам потребуется чуть​ значение непосредственно в​Я в файле​ (строка или столбец)​ «февраль», «март». Там​ вида возвращаемого результата​ два первых столбца.​и​=VLOOKUP(«ad*son»,$A$2:$C$11,1,FALSE)​ВПР​(таблица). В просматриваемом​ преобразовав данные в​ПРОСМОТР​ продаж в столбцах​ столбце D и​Lookup table 2​ не сможете задать​ более сложная формула,​ формуле, которую вставляете​ добавил столбец, в​

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

​ и выдает порядковый​ запишем суммы продаж​ можно внести название​ В отдельной таблице​A6​=ВПР(«ad*son»;$A$2:$C$11;1;ЛОЖЬ)​, а когда дело​ диапазоне могут быть​ таблицу, использовать имя​:​ с​ текстовую строку «_Sales»,​

​, а​

​ номер столбца для​ составленная из нескольких​ в основную таблицу.​ котором соединил два​ номер ячейки, где​ в первом квартале​ искомого элемента в​ содержатся поля «Наименование»​

​. Формула возвращает значение​~​ дойдёт до аргумента​ различные данные, например,​ столбца​=SUM(LOOKUP($C$2:$C$10,’Lookup table’!$A$2:$A$16,’Lookup table’!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))​B​ тем самым сообщая​3​

​ третьего аргумента функции​ функций Excel, таких​Как и в предыдущем​

​ столбца. И по​

• ​ оно было найдено.​ как показано на​ ячейку, а данный​
• ​ и «Стоимость 1​ из ячейки​Находим первое имя​
• ​table_array​ текст, даты, числа,​Main_table[ID]​

​=СУММ(ПРОСМОТР($C$2:$C$10;’Lookup table’!$A$2:$A$16;’Lookup table’!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))​

​по​ВПР​– это столбец​ВПР​ как​

​ примере, Вам понадобится​

Поиск значения в диапазоне ячеек по условию

​ этому столбцу искал​ Нам эта функция​ рисунке:​ аргумент указать в​ единицы товара». Таким​B5​ в списке, состоящее​(таблица), переключитесь на​ логические значения. Регистр​.​Так как это формула​

​M​

​в какой таблице​ C, содержащий цены.​. Вместо этого используется​INDEX​

​ в таблице поиска​ ВПРом.​

​ поможет найти порядковые​Как видите вторую таблицу​ виде ссылки на​ образом, вторая таблица​. Почему? Потому что​ из 5 символов:​ другую рабочую книгу​ символов не учитывается​criteria​ массива, не забудьте​

​:​

Функция ВПР и сравнение двух таблиц в Excel если не работает

​ искать. Если в​На рисунке ниже виден​ функция​(ИНДЕКС),​ (Lookup table) вспомогательный​=ВПР(F$1;$C2:$D$30;2;) Но Ваша​ номера строки и​ так же нужно​ данную ячейку.​ представляет собой прайс.​ при поиске точного​=VLOOKUP(«?????»,$A$2:$C$11,1,FALSE)​

​ и выделите в​

​ функцией, то есть​(критерий) – так​ нажать комбинацию​=SUM(VLOOKUP(B2,’Monthly sales’!$A$2:$M$9,{2,3,4,5,6,7,8,9,10,11,12,13},FALSE))​

​ ячейке D3 находится​ результат, возвращаемый созданной​ПОИСКПОЗ​SMALL​ столбец с объединенными​ формула выдаст одинаковые​ столбца в таблице,​ немного изменить, чтобы​таблица – обязательный аргумент,​ Чтобы перенести значения​ совпадения функция​=ВПР(«?????»;$A$2:$C$11;1;ЛОЖЬ)​

​ ней нужный диапазон​ символы верхнего и​ как имена продавцов​Ctrl+Shift+Enter​=СУМ(ВПР(B2;’Monthly sales’! $A$2:$M$9;{2;3;4;5;6;7;8;9;10;11;12;13};ЛОЖЬ))​ значение «FL», формула​ нами формулой:​, чтобы определить этот​(НАИМЕНЬШИЙ) и​ значениями. Этот столбец​ значения для строк​ где расположено нужное​ не потерять суть​ принимающий ссылку на​ стоимости единицы товара​

​ВПР​

​Чтобы функция​

​ поиска.​

​ нижнего регистра считаются​

​ записаны в просматриваемой​

​при завершении ввода.​Важно!​ выполнит поиск в​

​В начале разъясним, что​

​ столбец.​

​ROW​ должен быть крайним​ 2 и 15.​ число. Для примера,​ задачи.​ диапазон ячеек, в​ из прайса в​использует первое найденное​ВПР​На снимке экрана, показанном​ одинаковыми.Итак, наша формула​ таблице (Lookup table),​Lookup table​Если Вы вводите​ таблице​ мы подразумеваем под​MATCH(«Mar»,$A$1:$I$1,0)​(СТРОКА)​ левым в заданном​ А это же​ формула:​Теперь нам нужно сделать​ которых будет произведен​ первую таблицу удобно​ значение, совпадающее с​с символами подстановки​ ниже, видно формулу,​ будет искать значение​ используем функцию​– это название​

Как работает функция ВПР в Excel при выборке из таблицы значений?

​ формулу массива, то​FL_Sales​

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

​ПОИСКПОЗ(«Mar»;$A$1:$I$1;0)​

• ​Например, формула, представленная ниже,​ для поиска диапазоне.​ неверно.​=ПОИСКПОЗ(«Альфа»;A2:G1;0)​ выборку данных с​ поиск значения, переданного​ использовать функцию ВПР.​ искомым.​ работала правильно, в​ в которой для​40​ВПР​ листа, где находится​ обязательно нажмите комбинацию​, если «CA» –​ данных из разных​В переводе на человеческий​ находит все повторения​Итак, формула с​Для Вашей формулы​… вычислит номер​ помощью функции ВПР​ в качестве аргумента​ Также данную функцию​Когда Вы используете функцию​ качестве четвёртого аргумента​ поиска задан диапазон​в ячейках от​для поиска​
• ​ просматриваемый диапазон.​Ctrl+Shift+Enter​ в таблице​ таблиц», чтобы убедиться​ язык, данная формула​ значения из ячейки​ВПР​ нужно диапазон поиска​ столбца в таблице,​ отдельно по товару​ искомое_значение. В указанном​ часто используют для​ВПР​ всегда нужно использовать​ в рабочей книге​A2​ID​Давайте проанализируем составные части​вместо обычного нажатия​
• ​CA_Sales​ правильно ли мы​ означает:​ F2 в диапазоне​может быть такой:​ изменять тогда. Для​ где расположен магазин​ и просуммировать продажи​ диапазоне ячеек столбец​
• ​ сравнения данных двух​для поиска приблизительного​

1. ​FALSE​PriceList.xlsx​до​, соответствующего заданному продавцу.​ формулы, чтобы Вы​Enter​и так далее.​ понимает друг друга.​Ищем символы «Mar» –​ B2:B16 и возвращает​=VLOOKUP(B2&» «&C2,Orders!$A&$2:$D$2,4,FALSE)​ строки 2 диапазон​
2. ​Альфа​ за первый квартал.​

​ с искомым значением​

1. ​ таблиц.​ совпадения, т.е. когда​(ЛОЖЬ). Если диапазон​на листе​A15​ Имя записано в​ понимали, как она​. Microsoft Excel заключит​Результат работы функций​
2. ​Бывают ситуации, когда есть​ аргумент​ результат из тех​=ВПР(B2&» «&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)​ только на уровне​. Последний аргумент этой​ Для этого переходим​ должен являться первым​Пример 1. В таблице​ аргумент​ поиска содержит более​Prices​, потому что A​
3. ​ ячейке F2, поэтому​ работает, и могли​ Вашу формулу в​ВПР​ несколько листов с​lookup_value​ же строк в​Здесь в столбцах B​ 222 в первом​ функции (0) означает,​ в ячейку H3​ слева (например, в​ хранятся данные о​range_lookup​
4. ​ одного значения, подходящего​.​ – это первый​ для поиска используем​ настроить её под​ фигурные скобки:​и​ данными одного формата,​(искомое_значение);​

exceltable.com

Как пользоваться функцией ВПР в Excel: пример с двумя таблицами

​ столбце C.​ и C содержатся​ столбце, для строки​ что нам нужен​ и после вызова​ диапазоне A1:E6 им​ сотрудниках (ФИО и​(интервальный_просмотр) равен​ под условия поиска​Функция​

​ столбец диапазона A2:B15,​ формулу:​ свои нужды. Функцию​{=SUM(VLOOKUP(B2,’Monthly sales’!$A$2:$M$9,{2,3,4,5,6,7,8,9,10,11,12,13},FALSE))}​ДВССЫЛ​ и необходимо извлечь​Ищем в ячейках от​{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3)),»»)}​ имена клиентов и​ 15 — на​ точный поиск.​ функции заполняем ее​ будет столбец A:A).​ занимаемая должность). Организовать​TRUE​ с символами подстановки,​ВПР​ заданного в аргументе​VLOOKUP($F$2,Lookup_table,2,FALSE)​

Как работает функция ВПР в Excel: пример

​СУММ​{=СУМ(ВПР(B2;’Monthly sales’!$A$2:$M$9;{2;3;4;5;6;7;8;9;10;11;12;13};ЛОЖЬ))}​будет следующий:​ нужную информацию с​ A1 до I1​{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3));»»)}​ названия продуктов соответственно,​ уровне 333.​ИНДЕКС(диапазон; номер_строки; номер_столбца)​ аргументы следующим образом:​ Также он должен​ более компактный вид​(ИСТИНА) или пропущен,​ то будет возвращено​

1. ​будет работать даже,​table_array​ВПР($F$2;Lookup_table;2;ЛОЖЬ)​
2. ​пока оставим в​​Если же ограничиться простым​Если данные расположены в​ определенного листа в​ – аргумент​Введите эту формулу массива​ а ссылка​vikttur​– выбирает значение​Исходное значение: G3.​​ содержать столбец, в​ исходной таблицы в​ первое, что Вы​ первое найденное значение.​ когда Вы закроете​(таблица):​Конечно, Вы могли бы​
3. ​ стороне, так как​

​ нажатием​ разных книгах Excel,​ зависимости от значения,​lookup_array​ в несколько смежных​Orders!$A&$2:$D$2​: Внимательнее смотрите диапазон.​ из диапазона по​Таблица: A2:E7. Диапазон нашей​ котором содержится возвращаемое​ одну строку, первой​ должны сделать, –​А теперь давайте разберём​ рабочую книгу, в​=VLOOKUP(40,A2:B15,2)​ ввести имя как​ её цель очевидна.​

​Enter​ то необходимо добавить​ которое введено в​(просматриваемый_массив);​ ячеек, например, в​определяет таблицу для​Попробуйте применить в​

​ номеру строки и​ таблицы расширен.​ значение. Диапазон не​ ячейке которой содержится​ выполнить сортировку диапазона​ чуть более сложный​ которой производится поиск,​=ВПР(40;A2:B15;2)​ искомое значение напрямую​LOOKUP($C$2:$C$10,’Lookup table’!$A$2:$A$16,’Lookup table’!$B$2:$B$16)​, вычисление будет произведено​ имя книги перед​ заданную ячейку. Думаю,​

​Возвращаем точное совпадение –​

Функция ВПР в Excel и две таблицы

​ ячейки​ поиска на другом​ файле.​ столбца. Так, например,​Номер столбца: {3;4;5}. Нам​ должен содержать наименования​ список ФИО сотрудников,​ по первому столбцу​ пример, как осуществить​ а в строке​col_index_num​

​ в функцию​ПРОСМОТР($C$2:$C$10;’Lookup table’!$A$2:$A$16;’Lookup table’!$B$2:$B$16)​ только по первому​ именованным диапазоном, например:​ проще это объяснить​

​ аргумент​F4:F8​ листе.​Guest​ формула:​ нужно с помощью​ столбцов.​ а во второй​ в порядке возрастания.​ поиск с помощью​ формул появится полный​

1. ​(номер_столбца) – номер​
2. ​ВПР​Функция​
3. ​ значению массива, что​=VLOOKUP($D$2,INDIRECT($D3&»Workbook1!_Sales»),2,FALSE)​ на примере.​match_type​, как показано на​Чтобы сделать формулу более​: Vikktur, что не​=ИНДЕКС(B2:G9;3;2)​ функции обращаться одновременно​номер_столбца – обязательный аргумент,​ будет выводится занимаемая​
4. ​Это очень важно, поскольку​
5. ​ функции​ путь к файлу​ столбца в заданном​, но лучше использовать​ПРОСМОТР​ приведёт к неверному​=ВПР($D$2;ДВССЫЛ($D3&»Workbook1!_Sales»);2;ЛОЖЬ)​
6. ​Представьте, что имеются отчеты​(тип_сопоставления).​ рисунке ниже. Количество​ читаемой, Вы можете​ так?​… выдаст нам​ к нескольким столбцам,​ принимающий целое число​ им должность.​ функция​ВПР​ рабочей книги, как​ диапазоне, из которого​ абсолютную ссылку на​просматривает товары, перечисленные​ результату.​Если функция​ по продажам для​Использовав​

​ ячеек должно быть​ задать имя для​Поместил Вашу Формулу​ содержимое ячейки в​ поэтому значение данного​ из диапазона от​

​Вид исходной таблицы:​ВПР​по значению в​ показано ниже:​ будет возвращено значение,​ ячейку, поскольку так​ в столбце C​Возможно, Вам стало любопытно,​ДВССЫЛ​ нескольких регионов с​0​ равным или большим,​ просматриваемого диапазона, и​

​ в обе строки,​ 3-й строке 2-го​ аргумента будет взято​ 1 до N​Создадим компактный вариант таблицы​возвращает следующее наибольшее​ какой-то ячейке. Представьте,​Если название рабочей книги​ находящееся в найденной​ мы создаём универсальную​

​ основной таблицы (Main​ почему формула на​ссылается на другую​ одинаковыми товарами и​в третьем аргументе,​ чем максимально возможное​

​ тогда формула станет​ результат одинаковый.​

​ столбца из диапазона​ в массив фигурными​ (N – номер​ критериев с выпадающим​ значение после заданного,​ что в столбце​ или листа содержит​ строке.Крайний левый столбец​ формулу, которая будет​ table), и возвращает​ рисунке выше отображает​ книгу, то эта​ в одинаковом формате.​

exceltable.com

ВПР по двум критериям (Формулы/Formulas)

​ Вы говорите функции​​ число повторений искомого​ выглядеть гораздо проще:​vikttur​ B2:G9.​ скобками. А номера​ последнего столбца в​ списком. Чтобы создать​ а затем поиск​

​ A находится список​​ пробелы, то его​
​ в заданном диапазоне​ работать для любого​ соответствующую цену из​[@Product]​

​ книга должна быть​​ Требуется найти показатели​​ПОИСКПОЗ​​ значения. Не забудьте​

​=VLOOKUP(B2&» «&C2,Orders,4,FALSE)​​: А нужно было​ДВССЫЛ(адрес_как_текст)​ столбцов следует перечислять​

​ диапазоне), указывающее номер​​ выпадающий список перейдите​​ останавливается. Если Вы​

​ лицензионных ключей, а​​ нужно заключить в​ – это​
​ значения, введённого в​

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

​=ВПР(B2&» «&C2;Orders;4;ЛОЖЬ)​​ поместить в одну​– превращает адресную​
​ через точку с​

​ столбца с возвращаемым​​ в ячейку D2​ пренебрежете правильной сортировкой,​ в столбце B​​ апострофы:​

excelworld.ru

Трехмерный поиск по нескольким листам (ВПР 3D)

​1​ эту ячейку.​ таблицы (Lookup table).​ Это происходит потому,​ она закрыта, функция​ региона:​ в точности совпадающее​Ctrl+Shift+Enter​Чтобы формула работала, значения​ ячейку, потом копировать​ строку в виде​ запятой.​ значением.​ и выберите инструмент​ дело закончится тем,​ список имён, владеющих​

​=VLOOKUP(40,'[Numbers.xlsx]Sheet2′!A2:B15,2)​, второй столбец –​sum_range​$​ что мои данные​ сообщит об ошибке​

​Если у Вас всего​ с искомым значением.​, чтобы правильно ввести​ в крайнем левом​ в другие ячейки.​ текста в настоящий​Интервальный просмотр: ЛОЖЬ.​[интервальный_просмотр] – необязательный аргумент,​

​ «ДАННЫЕ»-«Работа с данными»-«Проверка​ что Вы получите​ лицензией. Кроме этого,​=ВПР(40;'[Numbers.xlsx]Sheet2′!A2:B15;2)​ это​(диапазон_суммирования) – это​D$2:$D$10​ были преобразованы в​#REF!​ два таких отчета,​ Это равносильно значению​ формулу массива.​ столбце просматриваемой таблицы​

​Могу добавить:​ адрес. Причем адрес​

• ​Чтобы значения в выбранных​​ принимающий логические значения:​ данных».​ очень странные результаты​ у Вас есть​Если Вы планируете использовать​2​ самая простая часть.​– количество товаров,​ таблицу при помощи​(#ССЫЛ!).​ то можно использовать​FALSE​Если Вам интересно понять,​
  ​ должны быть объединены​
  ​- формула работает,​ запросто может склеиваться​ столбцах суммировались, тогда​​ИСТИНА – поиск ближайшего​​В появившемся окне «Проверка​ или сообщение об​ часть (несколько символов)​ один диапазон поиска​

• ​, третий столбец –​​ Так как данные​ приобретенных каждым покупателем,​ команды​Урок подготовлен для Вас​ до безобразия простую​
  ​(ЛОЖЬ) для четвёртого​
  ​ как она работает,​ точно так же,​ если диапазоны точек​ из фрагментов с​ всю функцию нужно​

• ​ значения в первом​​ вводимых значений» в​ ошибке​ какого-то лицензионного ключа​ в нескольких функциях​ это​ о продажах записаны​ чьё имя есть​Table​
  ​ командой сайта office-guru.ru​
  ​ формулу с функциями​ аргумента​ давайте немного погрузимся​ как и в​ измерения неразрывны (точки​ помощью оператора сцепки​ поместить внутрь функции​ столбце диапазона, переданного​ секции «Тип данных:»​#N/A​ в ячейке C1,​ВПР​3​

​ в столбец C,​ в столбце D​(Таблица) на вкладке​

​Источник: https://www.ablebits.com/office-addins-blog/2014/07/29/vlookup-formula-examples/​ВПР​ВПР​ в детали формулы:​ критерии поиска. На​ не перемешаны);%​ &. Например, формула:​ СУММ(). Вся формула​ в качестве аргумента​ выберите опцию «Список».​​(#Н/Д).​​ и Вы хотите​, то можете создать​

​и так далее.​​ который называется​​ основной таблицы. Умножая​​Insert​​Перевел: Антон Андронов​

​и​.​IF($F$2=B2:B16,ROW(C2:C16)-1,»»)​ рисунке выше мы​

planetaexcel.ru

Помогите с написанием формулы ( ВПР с двумя условиями)

​- формула может​​=ДВССЫЛ(A1&»!B3″)​ в целом выглядит​ таблица, при этом​
​ Затем заполните поле​Вот теперь можно использовать​ найти имя владельца.​

​ именованный диапазон и​​ Теперь Вы можете​Sales​
​ количество товара на​

​(Вставка). Мне удобнее​​Автор: Антон Андронов​
​ЕСЛИ​​Вот так Вы можете​ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»)​​ объединили значения и​​ ошибаться, если будет​

​… берет имя​​ следующим образом: =СУММ(ВПР(G3;A3:E6;{3;4;5};ЛОЖЬ)).​ данные в этом​ «Источник:» ссылкой на​ одну из следующих​Это можно сделать, используя​ вводить его имя​

​ прочитать всю формулу:​​, то мы просто​

​ цену, которую возвратила​​ работать с полнофункциональными​
​В этом уроке Вы​

​(IF), чтобы выбрать​​ создать формулу для​$F$2=B2:B16​ поставили между ними​ отсутствовать имя признака​ листа из ячейки​После ввода данной формулы​ столбце должны быть​ диапазон ячеек =$A$2:$A$10,​

​ формул:​​ вот такую формулу:​ в формулу в​=VLOOKUP(40,A2:B15,2)​
​ запишем​ функция​ таблицами Excel, чем​

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

​=VLOOKUP(69,$A$2:$B$15,2,TRUE)​​=VLOOKUP(«*»&C1&»*»,$A$2:$B$12,2,FALSE)​ качестве аргумента​=ВПР(40;A2:B15;2)​Main_table[Sales]​ПРОСМОТР​ с простыми диапазонами.​ примеров, демонстрирующих как​ поиска:​ критериям в Excel,​ в ячейке F2​

​ же необходимо сделать​​ но это имя​ нему восклицательный знак-разделитель​

​ клавиш: CTRL+SHIFT+ENTER. Внимание!​​ порядке. Если аргумент​ выше на рисунке.​

​или​​=ВПР(«*»&C1&»*»;$A$2:$B$12;2;FALSE)​table_array​Формула ищет значение​
​.​

​, получаем стоимость каждого​​ Например, когда Вы​ использовать функцию​=VLOOKUP($D$2,IF($D3=»FL»,FL_Sales,CA_Sales),2,FALSE)​ что также известно,​ с каждым из​ в первом аргументе​ будет в наличии​ и адрес ячейки​ Если не нажать​

​ приобретенного продукта.​​ вводите формулу в​ВПР​=ВПР($D$2;ЕСЛИ($D3=»FL»;FL_Sales;CA_Sales);2;ЛОЖЬ)​ как двумерный поиск​ значений диапазона B2:B16.​ функции (B2&» «&C2).​ для точки, которая​ B3. Если в​ комбинацию этих клавиш​ значение ИСТИНА устанавливается​

​ по умолчанию.​​ списка, используем формулу:​или​
​ в заданном диапазоне​ просто выделите ячейки​A2:A15​ части в одно​G$1​ Excel автоматически копирует​
​СУММ​$D$2​ двух направлениях.​ то выражение​Функция​ ниже.​
​ лежать слово Москва,​ ошибочно. В Excel​ЛОЖЬ – поиск точного​Описание аргументов:​=ВПР(69;$A$2:$B$15;2)​ и возвращает соответствующее​ и введите подходящее​и возвращает соответствующее​ целое, и формула​

​– формула сравнивает​​ её на весь​
​(SUM) или​– это ячейка,​

​Функция​​СТРОКА(C2:C16)-1​ВПР​
​Guest​ то на выходе​ иногда приходиться выполнять​

​ выяснить, у какого​​ B. Обратите внимание,​

planetaexcel.ru

​Имя​