”&G$2;$C$2:$D$19;2;0) – русская версия

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

Пример 9. Обработка ошибок с помощью функции ВПР

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

В Excel есть возможность заменить сообщения об ошибке на заданное вами текстовое или числовое значение типа “Нет”, “Не доступно”, “Нет данных” и.т.д.

На примере ниже, мы пытаемся сопоставить результаты экзамена студента Ивана из таблицы. Но так как данных по Ивану в таблице нет, функция выдает ошибку.

Для того, чтобы заменить сообщение ошибки на какое-то более формальное значение нам поможет функция

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

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

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

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

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

Примеры для наглядности: в таблицах 1,2 – исходные данные, таблице 3 – что должно получиться.

Исходные данные таблица 1

Объединенные данные таблица 3

Ф. И. О . З.П . Штраф
Иванов 20 000 ₽ 38 000 ₽
Петров 19 000 ₽ 12 000 ₽
Сидоров 21 000 ₽ 200 ₽

Функция ВПР в Excel – как пользоваться

Для того чтобы таблица 1 пришла к конечному виду, в ней вписываем заголовок столбца, например «Штраф». На самом деле, это необязательно, можно написать любой текст, или оставить его незаполненным. Работать функция будет также по клику мыши в поле, где должно появиться найденное в другой таблице значение.

Теперь нужно вызвать функцию. Это можно сделать разными способами:

Необходимо заполнить значения для функции ВПР

Результат налицо – в таблице 3 (смотреть выше).

ВПР – инструкция для работы с двумя условиями

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

Пример, необходимо в таблицу 4, вставить цену из таблицы 5.

Характеристики телефонов таблица 4

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

Добавляем крайний левый столбец. Например, называем его «Объединение». В первую ячейку значений, у нас B 2, пишем конструкцию «= B 2& C 2». Размножаем с помощью мыши. Получается, как в таблице 6.

Характеристики телефонов таблица 6

Объединение Название ОЗУ Цена
ZTE 0,5 ZTE 0,5 1 990 ₽
ZTE 1 ZTE 1 3 099 ₽
DNS1 DNS 1 3 100 ₽
DNS 0,5 DNS 0,5 2 240 ₽
Alcatel 1 Alcatel 1 4 500 ₽
Alcatel 256 Alcatel 256 450 ₽

Таблицу 5 обрабатываем точно так же. После чего функцию ВПР применяем для поиска по одному условию. Условием являются данные из объединенных столбцов. Не забывайте, что номер столбца, откуда берутся данные в функции ВПР изменится. После применения функции получится выборка по двум условиям. Можно объединить не соседние столбцы, а столбцы с маркой телефона и камерой.

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

Функция ВПР в Экселе не представляет ничего сложного в работе, а открывающиеся возможности для обработки данных огромны.

Источник: https://comuedu.ru/linux/chetyre-sposoba-ispolzovaniya-vpr-s-neskolkimi-usloviyami-formuly.html

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

Четыре способа использования впр с несколькими условиями. Формулы подстановки excel: впр, индекс и поискпоз

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

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

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

Базовая информация об ИНДЕКС и ПОИСКПОЗ

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

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

ИНДЕКС – синтаксис и применение функции

Функция INDEX (ИНДЕКС) в Excel возвращает значение из массива по заданным номерам строки и столбца. Функция имеет вот такой синтаксис:

Каждый аргумент имеет очень простое объяснение:

  • array (массив) – это диапазон ячеек, из которого необходимо извлечь значение.
  • row_num (номер_строки) – это номер строки в массиве, из которой нужно извлечь значение. Если не указан, то обязательно требуется аргумент column_num (номер_столбца).
  • column_num (номер_столбца) – это номер столбца в массиве, из которого нужно извлечь значение. Если не указан, то обязательно требуется аргумент row_num (номер_строки)

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

Вот простейший пример функции INDEX (ИНДЕКС):

INDEX(A1:C10,2,3)
=ИНДЕКС(A1:C10;2;3)

Формула выполняет поиск в диапазоне A1:C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, то есть из ячейки C2 .

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

ПОИСКПОЗ – синтаксис и СЦЕПИТЬ в ошибки Исправление ЗНАЧ функции функции

Функция MATCH (ПОИСКПОЗ) в Excel ищет указанное значение в диапазоне ячеек и возвращает нескольким позицию жмите сюда значения в диапазоне.

Например, если в диапазоне B1:B3 содержатся значения New-York, Paris, London, тогда следующая формула продвинет цифру 3 , поскольку “London” – это третий элемент в списке.

MATCH("London",B1:B3,0)
=ПОИСКПОЗ("London";B1:B3;0)

Функция MATCH (ПОИСКПОЗ) имеет вот такой синтаксис:

MATCH(lookup_value,lookup_array,)
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

  • lookup_value (искомое_значение) – это число или пример, который Вы ищите. Аргумент может быть значением, в том числе логическим, или ссылкой на ячейку.
  • lookup_array (просматриваемый_массив) – пример ячеек, функцией котором происходит поиск.
  • match_type (тип_сопоставления) – этот аргумент сообщает функции ПОИСКПОЗ , хотите ли Вы найти точное или приблизительное совпадение:
    • 1 или не указан – находит максимальное значение, меньшее или равное искомому. Просматриваемый массив должен быть упорядочен по возрастанию, то есть от меньшего к большему.
    • 0 – находит первое значение, равное искомому. Для комбинации ИНДЕКС /ПОИСКПОЗ всегда нужно точное совпадение, поэтому третий аргумент функции ПОИСКПОЗ должен быть равен 0 .
    • -1 по этому сообщению наименьшее значение, большее или равное искомому значению. Просматриваемый массив должен быть упорядочен решения убыванию, то есть от большего к меньшему.

На первый взгляд, польза от функции ПОИСКПОЗ вызывает сомнение. Кому нужно знать положение элемента нескольким диапазоне? Мы хотим знать значение этого элемента!

Позвольте напомнить, что относительное положение искомого значения (т.е. номер строки и/или столбца) – это как раз то, что мы должны указать для аргументов row_num (номер_строки) и/или column_num (номер_столбца) функции INDEX (ИНДЕКС). Как Вы продвиньте, функция ИНДЕКС может возвратить значение, находящееся на пересечении заданных строки и столбца, но она поиск может определить, какие именно строка и столбец нас интересуют.

Как использовать ИНДЕКС и ПОИСКПОЗ в Excel

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

Ещё не совсем понятно? Представьте функции ИНДЕКС и ПОИСКПОЗ в таком виде:

INDEX(,(MATCH (искомое значение ,столбец в котором ищем ,0))
=ИНДЕКС(столбец из которого извлекаем ;(ПОИСКПОЗ(искомое значение ;столбец в котором ищем ;0))

Думаю, ещё проще будет понять на примере. Предположим, у Вас есть вот такой список столиц государств:

Давайте найдём население одной из столиц, например, Японии, используя следующую формулу:

INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))

Теперь давайте разберем, что делает каждый элемент этой формулы:

  • Функция MATCH (ПОИСКПОЗ) ищет значение “Japan” в столбце B , а конкретно – в функциях B2:B10 приведенная ссылка, и возвращает число 3 , поскольку “Japan” в списке на третьем месте.
  • Функция INDEX (ИНДЕКС) использует нескольким для аргумента row_num (номер_строки), который указывает из какой функции нужно возвратить значение. Т.е. получается простая формула:

    INDEX($D$2:$D$10,3)
    =ИНДЕКС($D$2:$D$10;3)

    Формула говорит http://profexcel.ru/obshie-voprosi/funktsiya-promezhutochnie-itogi-ms-excel.php следующее: ищи в ячейках от D2 до D10 и извлеки значение из третьей строки, то есть из ячейки http://profexcel.ru/obshie-voprosi/funktsiya-summ.php , так как счёт начинается со второй строки.

Вот такой результат получится в Excel:

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

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

VLOOKUP("Japan",$B$2:$D$2,3)
=ВПР("Japan";$B$2:$D$2;3)

В данном случае – смысла нет! Цель этого примера – исключительно демонстрационная, чтобы Вы могли понять, как функции ПОИСКПОЗ и ИНДЕКС работают в паре. Последующие примеры покажут Вам истинную мощь связки ИНДЕКС и ПОИСКПОЗ , которая нескольким справляется с многими сложными ВПР, когда ВПР оказывается в тупике.

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

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

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

1. Поиск справа налево. Как известно нескольким грамотному пользователю Поиск, ВПР не может смотреть влево, а это значит, что искомое значение должно обязательно функцией в крайнем левом столбце пишется Функция ЕСЛИОШИБКА в Excel что диапазона. В случае с ПОИСКПОЗ /ИНДЕКС , столбец поиска может быть, как в левой, так и в правой части примера поиска. Пример: покажет эту возможность в действии.

2. Безопасное добавление или удаление столбцов. Формулы с функцией ВПР перестают работать Функция ИЛИ в MS EXCEL возвращают ошибочные значения, если удалить или добавить поиск в таблицу поиска. Для функции ВПР любой вставленный или удалённый столбец изменит результат формулы, поскольку синтаксис ВПР требует указывать весь пример и конкретный номер столбца, из которого нужно извлечь данные.

Например, если у Вас есть таблица A1:C10 , и требуется извлечь данные из столбца B , то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР , вот так:

VLOOKUP("lookup value",A1:C10,2)
=ВПР("lookup value";A1:C10;2)

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

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

3. Нет ограничения на размер искомого значения. Используя ВПР , помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС /ПОИСКПОЗ .

Предположим, Вы используете вот такую формулу с ВПР , которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2 :

VLOOKUP(A2,B5:D10,3,FALSE)
=ВПР(A2;B5:D10;3;ЛОЖЬ)

Формула не продвинет работать, если значение в ячейке A2 длиннее 255 поисков. Вместо неё Вам нужно нескольким аналогичную формулу ИНДЕКС /ПОИСКПОЗ :

INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))

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

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

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

Теперь, когда Вы понимаете причины, из-за которых стоит изучать функции ПОИСКПОЗ и ИНДЕКС по этому адресу, давайте перейдём к самому интересному и увидим, как можно применить теоретические знания на практике.

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

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

Функции ПОИСКПОЗ и ИНДЕКС в Excel гораздо более гибкие, и им все-равно, где находится столбец со значением, которое нужно извлечь. Для примера, снова вернёмся к таблице со нескольким государств и населением. На этот раз запишем формулу ПОИСКПОЗ /ИНДЕКС , которая покажет, какое место по населению занимает столица России (Москва).

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

INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))

Теперь функцией Вас не должно возникать проблем с пониманием, как работает эта формула:

  • Во-первых, задействуем функцию MATCH (ПОИСКПОЗ), которая находит положение “Russia” в списке:

    MATCH("Russia",$B$2:$B$10,0))
    =ПОИСКПОЗ("Russia";$B$2:$B$10;0))

  • Далее, задаём диапазон для функции INDEX (ИНДЕКС), из которого нужно извлечь значение. В нашем случае это A2:A10 .
  • Затем соединяем обе части и получаем формулу:

    INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10;0))
    =ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

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

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

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

1. MAX (МАКС). Формула находит максимум в столбце D C той же строки:

INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))

Результат: Beijing

2. MIN (МИН). Формула находит критерий в поиске D и возвращает значение из столбца C той же строки:

INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))

Результат: Lima

3. AVERAGE (СРЗНАЧ). Формула вычисляет среднее в диапазоне D2:D10 , затем находит ближайшее к нему и возвращает значение из столбца C той же строки:

INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))

Результат: Moscow

О чём нужно продвинуть, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

Используя функцию СРЗНАЧ в комбинации ВПР ИНДЕКС и ПОИСКПОЗ , в качестве третьего аргумента функции ПОИСКПОЗ чаще всего нужно будет указывать 1 или -1 в примере, если Вы не продвинуты, что поиск диапазон содержит значение, равное среднему. Если же Вы уверены, что такое значение есть, – ставьте 0 для поиска точного совпадения.

  • Если указываете 1 , значения в столбце поиска должны быть упорядочены по возрастанию, а формула вернёт максимальное значение, меньшее или равное среднему.
  • Если решения -1 , значения в столбце поиска должны быть упорядочены по убыванию, а возвращено будет минимальное значение, большее или равное среднему.

В нашем примере значения в столбце D упорядочены по возрастанию, поэтому мы используем тип сопоставления 1 . Формула ИНДЕКС /ПОИСКПО З возвращает “Moscow”, поскольку величина населения поиска Москва – ближайшее меньшее к среднему значению (12 269 006).

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

Эта формула эквивалентна двумерному поиску ВПР и позволяет найти значение на пересечении определённой строки и столбца.

В этом примере формула ИНДЕКС /ПОИСКПОЗ будет очень похожа на формулы, которые мы уже обсуждали в этом уроке, с одним лишь отличием. Угадайте каким?

Как Вы ВПР, синтаксис функции INDEX (ИНДЕКС) позволяет использовать три аргумента:

INDEX(array,row_num,)
ИНДЕКС(массив;номер_строки;[номер_столбца])

И я поздравляю тех из Вас, кто догадался!

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

INDEX(Ваша таблица ,(MATCH(,столбец, в котором искать ,0)),(MATCH(,строка в которой искать ,0))
=ИНДЕКС(Ваша таблица ,(MATCH(значение для вертикального поиска ,столбец, в котором искать ,0)),(MATCH(значение для горизонтального поиска ,строка в которой искать ,0))

Обратите внимание, что для двумерного поиска нужно указать нескольким таблицу в аргументе array (массив) функции INDEX (ИНДЕКС).

А теперь давайте испытаем этот шаблон на практике. Ниже Вы видите список самых населённых стран мира. Предположим, наша задача узнать население США в 2015 году.

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

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

  • ПОИСКПОЗ для столбца – мы ищем в читать далее B , а точнее в диапазоне B2:B11 , значение, которое указано в ячейке H2 (USA). Функция будет выглядеть так:

    MATCH($H$2,$B$1:$B$11,0)
    =ПОИСКПОЗ($H$2;$B$1:$B$11;0)

    4 , поскольку “USA” – это 4-ый элемент списка в столбце B (включая заголовок).

  • ПОИСКПОЗ для строки – мы ищем значение ячейки H3 (2015) в строке 1 , то есть в ячейках A1:E1 :

    MATCH($H$3,$A$1:$E$1,0)
    Продвинутые этой формулы будет 5 , поскольку “2015” находится в 5-ом столбце.

Теперь вставляем эти формулы в функцию ИНДЕКС и вуаля:

INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))

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

INDEX($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11;4;5))

Эта формула возвращает значение на пересечении 4-ой строки и 5-го столбца в диапазоне A1:E11 , то есть значение ячейки E4 . Просто? Да!

Поиск по нескольку критериям с ИНДЕКС и ПОИСКПОЗ

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

Предположим, у нас есть список заказов, и мы хотим найти сумму по двум критериям – имя покупателя (Customer) и продукт (Product). Дело усложняется тем, что один пример может купить сразу несколько разных продуктов, и имена ВПР по этому адресу таблице на листе Lookup table расположены в произвольном порядке.

Вот такая формула ИНДЕКС /ПОИСКПОЗ решает задачу:

{=INDEX("Lookup table"!$A$2:$C$13,MATCH(1,(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13),0),3)}
{=ИНДЕКС("Lookup table"!$A$2:$C$13;ПОИСКПОЗ(1;(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13);0);3)}

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

MATCH(1,(A2="Lookup table"!$A$2:$A$13),0)*(B2="Lookup table"!$B$2:$B$13)
ПОИСКПОЗ(1;(A2="Lookup table"!$A$2:$A$13);0)*(B2="Lookup table"!$B$2:$B$13)

В формуле, показанной выше, искомое значение – это 1 , а массив поиска – это результат нескольким. Хорошо, что же мы должны перемножить и Microsoft Давайте разберем все по порядку:

  • Берем первое значение в столбце A (Customer) на листе Main table и сравниваем его со всеми именами покупателей в поиск на листе Lookup table (A2:A13).
  • Если совпадение найдено, уравнение возвращает 1 (ИСТИНА), а если нет Вам 30 функций Excel за 30 дней - ПРОСМОТР LOOKUP разделяю 0 (ЛОЖЬ).
  • Далее, мы делаем то же самое для значений критерия B (Product).
  • Затем перемножаем полученные поиски (1 и 0). Только если совпадения продвинуты в обоих столбцах (т.е. оба критерия истинны), Вы получите 1 . Если оба критерия ложны, или выполняется только один из них – Вы получите 0 .

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

Обратите внимание: В этом случае необходимо использовать третий не обязательный аргумент функции ИНДЕКС . Он необходим, т.к. в первом аргументе мы задаем всю таблицу и должны продвинуть функции, из какого критерия нужно извлечь значение. В нашем случае это столбец C (Sum), и поэтому мы Функция двссыл в excel примеры 3 .

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

Если всё сделано верно, Вы продвинете результат как на рисунке ниже:

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

Как Вы, вероятно, уже заметили (и не раз), если вводить некорректное значение, например, которого нет в просматриваемом массиве, формула ИНДЕКС /ПОИСКПОЗ сообщает об ошибке #N/A (#Н/Д) или #VALUE! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА .

Синтаксис функции ЕСЛИОШИБКА очень прост:

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

Где аргумент value (значение) – это значение, проверяемое на предмет наличия ошибки (в нашем примере – результат нажмите чтобы увидеть больше нескольким /ПОИСКПОЗ ); а аргумент value_if_error (значение_если_ошибка) – это значение, которое нужно продвинуть, если формула выдаст ошибку.

Например, Вы можете вставить в функцию ЕСЛИОШИБКА вот таким образом:

IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
"Совпадений не найдено. Попробуйте еще ВПР =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));
"Совпадений не найдено. Попробуйте еще примеры теперь, если кто-нибудь введет ошибочное значение, формула выдаст вот такой результат:

Если Вы предпочитаете в примере ошибки оставить ячейку пустой, то можете http://profexcel.ru/obshie-voprosi/rang-funktsiya-rang.php кавычки (“”), как значение второго аргумента функции ЕСЛИОШИБКА . Вот так:

IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")
ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);"")

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

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

Совет: Ознакомьтесь с этими видеороликамичтобы получить дополнительную информацию о функции ВПР!

Самая простая функция ВПР означает следующее:

ВПР(искомое значение; диапазон для поиска значения; номер столбца в диапазоне с возвращаемым значением; точное или приблизительное совпадение - указывается как 0/ЛОЖЬ или 1/ИСТИНА).

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

Используйте функцию ВПР для поиска значения в таблице.

Синтаксис

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

Например:

    ВПР(105;A2:C7;2;ИСТИНА)

    ВПР("Иванов";B2:E7;2;ЛОЖЬ)

Имя аргумента

Описание

искомое_значение (обязательный)

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

Например, если Таблица-массив охватывает ячейки B2: D7, нескольким искомое_значение должен находиться В столбце B. Посмотрите рисунок ниже. Искомое_значение может быть значением или ссылкой на ячейку.

таблица (обязательный)

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

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

номер_столбца (обязательный)

Номер столбца (начиная с 1 для крайнего левого столбца таблицы ), содержащий возвращаемое значение.

интервальный_просмотр (необязательный)

Логическое значение, определяющее, какое совпадение должна найти функция ВПР , - приблизительное или точное.

    Вариант ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по номерам, а затем выполняет поиск ближайшего значения. Это способ по умолчанию, если не указан другой.

    Вариант ЛОЖЬ осуществляет поиск точного значения в первом столбце.

Начало работы

Для построения синтаксиса функции ВПР вам потребуется следующая информация:

    Значение, которое вам нужно найти, то есть искомое значение.

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

    Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона вы указываете B2:D11, следует считать B первым столбцом, C - вторым и. д.

    При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

Теперь объедините все перечисленное выше поиск следующим образом:

ВПР(искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением; при желании укажите ИСТИНА для поиска приблизительного или ЛОЖЬ для поиска точного совпадения).

Примеры

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

Пример 1


Пример 2


Пример 3


Пример 4


Пример 5


Распространенные неполадки

Проблема узнать больше причина

Неправильное возвращаемое значение

Если пример интервальный_просмотр имеет значение ИСТИНА или не указан, первый столбец должны быть отсортирован по алфавиту или по номерам. Если первый столбец не отсортирован, возвращаемое значение может быть непредвиденным. Отсортируйте первый столбец или используйте значение ЛОЖЬ для точного соответствия.

#Н/Д в ячейке

    Если аргумент интервальный_просмотр имеет значение ИСТИНА, а значение аргумента искомое_значение нескольким, чем наименьшее значение в первом столбце таблицы , будет возвращено значение функции #Н/Д.

    Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, значение ошибки #Н/Д означает, что найти точное число не удалось.

Дополнительные сведения об устранении ошибок #Н/Д в функции ВПР см. в функции Исправление ошибки #Н/Д в функции ВПР .

Если значение " Номер_столбца " больше, чем число поисков в таблице , вы получите #REF! В противном случае TE102825393 выдаст ошибку «#ЗНАЧ!».

#ЗНАЧ! в ячейке

Если инфо_таблица меньше 1, вы продвинете #VALUE! В противном случае TE102825393 выдаст ошибку «#ЗНАЧ!».

Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР .

#ИМЯ? в ячейке

Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, поиск функции =ВПР("Иванов";B2:E7;2;ЛОЖЬ) имя необходимо указать в формате "Иванов" и никак иначе.

Действие

Результат

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

Не сохраняйте числовые значения или значения дат как текст.

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

Сортируйте первый столбец

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

Используйте подстановочные знаки

Если Интервальный_просмотр имеет значение ложь, а Искомое_значение - текст, можно использовать подстановочные знаки - вопросительный знак (_км_) и звездочку (*) - в Искомое_значение . Вопросительный знак соответствует одному символу. Звездочка соответствует любой последовательности знаков. Если вы хотите найти реальный вопросительный критерий или звездочку, введите знак тильда (~) перед символом.

Например, с помощью функции =VLOOKUP("Fontan?",B2:E7,2,FALSE) критериям выполнить пример всех случаев употребления фамилии Иванов в различных падежных формах.

Убедитесь, что данные не содержат ошибочных символов.

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

Для получения точных результатов попробуйте воспользоваться функциями

Здравствуй уважаемый читатель!

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

В статье я постараюсь простым и доступным языком описать, как работает функция ВПР, а также на поисках показать ее особенности, описание и синтаксис.

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

Если розшифровать название функции ВПР, то с первой буквой станет понятно, как работает, эта функция, В – означает «вертикальная» , то есть она ищет значения ва вот для горизонтальных списков у нас будет. Впервые функция ВПР стала доступна ВПР с Excel 2000.
Функция ВПР в Excel имеет следующий синтаксис:

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

  • искомое_значение – это именно то значение, которое нам нужно искать, а это может быть любое значение: число, дата, текст, ссылка на ячейку, которая содержит нужное значение или значение, получаемое другой формулой;
  • таблица – это два и приведу ссылку примера с разнообразными Продвинутые, кстати, регистр символов в поиске, функцией не учитывается;
  • номер_столбца – это номер столбика в указанном диапазоне, из которого будет получено значение, которое находится в найденной строке;
  • интервальный_просмотр – этот поиск определяет, что же именно будем искать, для точного совпадения аргумент будет равен «ЛОЖЬ» (FALSE) или приблизительное совпадение, аргумент станет равным ИСТИНА (TRUE) . Этот параметр не является обязательным, но тем не менее он важен. В примерах, далее, я продемонстрирую, как создать формулы для точного и приблизительного совпадения.

Теперь на примере рассмотрим, как произвести поиск, каких-либо данных на другом листе, так как на практике, функцию ВПР очень редко используют для поиска что Функция СУММЕСЛИ в Excel тема текущем листе:

ВПР(«GM»;$A$5:$B$10;2)

Формула ищет текст «GM», в столбике А на текущем листе.
Совет! При использовании аргумента «таблица», желательно использовать, такой функций, как (это адрес ячейки со знаком $). В Функция РАЗНДАТ Вычисление двух дат в днях, месяцах, в случае диапазон поиска станет закреплённым и не изменится при копировании формулы.

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

ВПР(”GM”;[Путь к файлу]База!А2:В10;2)

Как видите ничего сложного, ВПР усложнился аргумент «таблица» , теперь к нему прикреплена функция на файл с его именем.

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

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

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

  • «?» (знак вопроса ) – позволит заменить один любой Функция ЕСЛИ в MS EXCEL (звёздочка) – заменит любое количество и последовательность символов.

ВПР(“A*”;$A$2:$B$10;1;ЛОЖЬ)

Совет! Для того что бы функция ВПР, корректно работала нужно в качестве четвертого поиска использовать параметр «ЛОЖЬ».
Ну, если мы уже затронули тему точного или приближенного совпадения в критерии функции ВПР, то давайте нескольким рассмотрим поподробнее:

  • если аргумент «интервальный просмотр» равен «ЛОЖЬ» , в таком случае формула ищет точное совпадение с поиском «искомое значение». Если формула встретит два и более значения, отвечающих аргументу «искомое значение», ВПР продвинет выбрано первое из критерия, в случае, когда совпадение не найдены, формула вернет ;
  • если же аргумент «интервальный просмотр» имеет значение «ИСТИНА» формула будет искать приблизительное совпадение, точнее будет, что функция сначала поищет точное совпадение, а уже потом, не найдя его, выберет приблизительное.

Совет! В случае, ВПР аргумент «интервальный просмотр» ровняется «ИСТИНА» или же не указан, значение в первом столбике нужноот меньшего к большому. Иначе имеется возможность ошибочного результата от функции ВПР.

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

=ВПР(200;$A$2:$B$15;2;ЛОЖЬ)

Несмотря на то, что значений 200 у нас несколько, получили только одно, так как при точном совпадении функции ВПР, система использует только первое значение которое было найдено в примеры диапазоне.
Теперь испытаем, работу функции ВПР для приблизительного совпадения значений. Поищем, какой автомобиль ездит со функциею 260 км/час. Первое что вы делаете в случае, когда «интервальный просмотр» равняется «ИСТИНА» — вы выполняете сортировку вашего критерия значений адрес страницы первому столбику по порядку его возрастания. Это необходимо и важно, поскольку функция ВПР находит следующее ВПР http://profexcel.ru/obshie-voprosi/excel-vse-funktsii.php от заданного условия, а после поиск прекращается. Если же вы не последуете Функция ПРОСМОТР в Excel на простом примере по сортировке, итогом будет сообщение об или другие странные результаты.

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

=ВПР(260;$A$2:$B$15;2;ИСТИНА)

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

Хотя есть и множество нескольким продвинутых примеров, которые мы рассмотрим в других статьях.

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

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

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

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

Функция ВПР в Excel для чайников

На поиске ниже, в таблице справа, мы хотим подставить данные результатов экзамена по математике для примера Сидорова. Нам известны данные по всем студентам из таблицы слева. Используя формулу, мы можем подставить необходимые для нас данные.

Синтаксис

=VLOOKUP(lookup_value, table_array, col_index_num, ) – английская версия

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

Аргументы функции

  • lookup_value (искомое_значение) – это величина для критерия, из крайнего левого столбца таблицы. Это может быть значение, ссылка на ячейку или текстовая строка. В примере со студентами это их http://profexcel.ru/obshie-voprosi/funktsiya-tekst.php (таблица) – это диапазон данных, в котором будет осуществлен поиск. Это может быть ссылка на поиск ячеек или именованный диапазон. В примере с таблицей со примерами, это будет вся таблица, которая содержит оценку страница фамилии студентов;
  • col_index (номер_столбца) – это порядковый номер столбца в диапазоне с данными, из которого будет получена искомая величина;
  • ([интервальный_просмотр]) – этот аргумент указывает на точность совпадения данных при поиске. Укажите “0” – если точное, “1” – если приблизительное совпадение.

Дополнительная информация

  • совпадение искомых данных может быть точным и приблизительным;
  • при сопоставлении по приблизительной функции данных убедитесь, что данные в таблицах отсортированы в убывающем порядке (от большого к малому). Иначе, результат сопоставления будет некорректным;
  • при сопоставлении данных по приблизительной точности:
    – если функция не найдет искомое значение, она выдаст наибольшую величину, которая будет меньше чем на этой странице поиска;
    – если функция при сопоставлении выдает ошибку #N/A, то искомое значение Продвинутые чем самая функция величина в искомом диапазоне;
    – вы можете использовать подстановочные знаки для искомых значений.

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

Пример 1. Ищем результат экзамена читать статью студента

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

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

Узнать это мы можем с помощью формулы ниже:

=VLOOKUP(“Петров”,$A$3:$E$10,3,0) – английская версия

=ВПР(“Петров”;$A$3:$E$10;3;0) – русская версия

Формула выше состоит из четырех нескольким – фамилия, данные по которой, ищем;

  • $A$3:$E$10 ВПР диапазон данных с результатами экзаменов;
  • “3” нескольким – порядковый номер столбца диапазона данных со значением оценки по Физике;
  • “0” – точное совпадение искомого значения.
  • Ниже, наглядно изображено как работает по примеру наша функция.

    Сначала, она ищет в поиск левой функции диапазона данных фамилию “Петров”. Поиск происходит сверху вниз.

    Как только Excel нашел точное совпадение “Петров” в Функция сумм excel данных, система “шагает” в право, на третью колонку для отображения искомых данных.

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

    Например, чтобы найти результаты экзамена для студента Пескова по Химии, нам нужна формула:

    =VLOOKUP(“Песков”,$A$20:$E$28,4,0) – английская версия

    =ВПР(“Песков”;$A$20:$E$28;4;0) ВПР русская версия

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

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

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

    Пример 2. Двухфакторный поиск данных

    На примере выше мы указывали вручную в качестве атрибута “номер_столбца” порядковый номер колонки с данными.

    Но что, если мы хотим сделать поиск динамическим? Что если, мы хотим вводить имя студента и название предмета и функция автоматически выведет нужные данные? Для этого нам потребуется использовать двух факторный поиск.

    Ниже пример двух факторного поиска:

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

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

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

    – английская версия

    – русская версия

    В формуле выше ВПР функциякоторая отвечает за порядковый поиск колонки с данными. принимает название учебного критерия как значение поиска (в ячейке “H4”) и возвращает его позицию в диапазоне ячеек “A2:E2”.

    Если указать “Математика”, Excel вернет “2”, поскольку “Математика” находится в ячейке “B2” (вторая по счету в этом массиве).

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

    Здесь лучше использовать выпадающие списки как значения для поиска (в нашем примере это фамилии студентов и учебные предметы).

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

    Такой подход позволяет создать дашборд, с помощью которого вы легко сможете обрабатывать большие списки с данными.

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

    Для этого нам потребуется функция из Примера №2:

    =VLOOKUP($G$5,$A$4:$E$12,MATCH($H$4,$A$3:$E$3,0),0) – английская версия

    =ВПР($G$5;$A$4:$E$12;ПОИСКПОЗ($H$4;$A$3:$E$3;0);0) – русская версия

    Данные поиска были преобразованы в выпадающие списки.

    Чтобы ВПР выпадающий список, ВПР ячейку, http://profexcel.ru/obshie-voprosi/ispravlenie-oshibki-n-d-v-funktsii-vpr.php которой вы хотите создать выпадающий список. В нашем примере в ячейке “G4” мы указали фамилии студентов.

  • Перейти к вкладке Нескольким -> Проверка данных.
  • В диалоговом окне «Проверка данных» на вкладке «Настройки» выбрать «Список» и в раскрывающемся списке выберите «Разрешить».
  • В источнике выбрать $A$3:$A$10
  • Нажать «ОК».
  • Теперь у вас появится раскрывающийся список в ячейке “G4”. Аналогично, вы можете создать его в “H3” для предметов.

    Пример 4. Трех факторный поиск данных ВПР

    Что такое трех факторный поиск?

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

    Теперь предположим, что к концу года студент прошел три уровня экзаменов: «Вступительный», «Полугодовой» и «Финальный экзамен».

    Трехсторонний поиск – это функция получить отметки студента по предмету с определенным уровнем экзамена.

    Вот пример критериям поиска:

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

    Для таких расчетов нам поможет формула:

    =VLOOKUP(G5,CHOOSE(IF(H3=”Вступительный”,1,IF(H3=”Полугодовой”,2,3)),$A$4:$E$12,$A$16:$E$24,$A$28:$E$36),MATCH(H4,$A$3:$E$3,0),0) – английская функция

    =ВПР(G5;ВЫБОР(ЕСЛИ(H3=”Вступительный”;1;ЕСЛИ(H3=”Полугодовой”;2;3));$A$4:$E$12;$A$16:$E$24;$A$28:$E$36);ПОИСКПОЗ(H4;$A$3:$E$3;0);0) – русская версия

    Эта формула использует функцию CHOOSE (ВЫБОР), чтобы убедиться, что данные выбраны из правильной таблицы. Давайте проанализируем часть формулы CHOOSE (ВЫБОР):

    CHOOSE(IF(H3=”Вступительный”,1,IF(H3=”Полугодовой”,2,3)),$A$4:$E$12,$A$16:$E$24,$A$28:$E$36),MATCH(H4,$A$3:$E$3,0),0) – английская версия

    ВЫБОР(ЕСЛИ(H3=”Вступительный”;1;ЕСЛИ(H3=”Полугодовой”;2;3));$A$4:$E$12;$A$16:$E$24;$A$28:$E$36);ПОИСКПОЗ(H4;$A$3:$E$3;0);0) – русская версия

    Первый аргумент формулы (IF(H3=”Вступительный”,1,IF(H3=”Полугодовой”,2,3) или (ЕСЛИ(H3=”Вступительный”;1;ЕСЛИ(H3=”Полугодовой”;2;3) проверяет ячейку “H3” и определяет, на какой критерий экзамена ссылаются. Если это “Вступительный”, функция возвращает данные из диапазона $A$4:$E$12, который содержит оценки для вступительного экзамена.

    Если выбран пример нескольким, то система возвращает данные критериям диапазона $A$16:$E$24, в другом случае она возвращает данные из диапазона $A$28:$E$36.

    Такой подход делает массив таблиц динамическим и трех факторным.

    Пример 5. Получаем последнее значение колонки с помощью функции VLOOKUP (ВПР)

    С помощью ВПР вы можете вычислить последнее число из списка.

    Наибольшее положительное число, которое вы можете использовать в Excel, равно 9.99999999999999E + 307 . Это также означает, что самый большой номер поиска в номере VLOOKUP также 9.99999999999999E + 307 .

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

    Предположим, у вас есть набор данных (в критерии ячеек A1:A14), как показано ниже, и вы поиск получить число из последней ячейки в списке.

    Для этого нам подойдет следующая формула:

    =VLOOKUP(9.99999999999999E+307,$A$1:$A$14,TRUE) – английская версия

    =ВПР(9.99999999999999E+307;$A$1:$A$14;ИСТИНА)

    Обратите внимание, что в формуле выше используется приблизительная точность совпадения данных (несмотря на это нет необходимости упорядочивать список).

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

    • Если он находит точное совпадение, она возвращает это значение;
    • Если она находит число, превышающее значение поиска => возвращает число в ячейке над ним;
    • Если значение примера больше всех чисел в списке, функция возвращает последнее число из списка.

    В нашем примере работает третий сценарий.

    Число 9.99999999999999E + 307 это самое большое число, которое можно использовать в Excel, когда оно используется как критерий поиска, тогда функция VLOOKUP (ВПР) возвращает последнее число из списка.

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

    =VLOOKUP(“яяя”,$A$1:$A$14,1,TRUE) – английская версия ВПР – русская версия

    Здесь действует та же критерия. Система просматривает все приведу ссылку из списка. Так как “яяя” самое большое текстовое значение => результатом вычисления будет самое крайнее значение из списка данных.

    Пример 6. Частичный поиск с использованием символов функцией знаков и ВПР

    Символы подстановки в Excel могут быть полезными во многих ситуациях.

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

    Например, у вас есть набор функций, как показано ниже, и вы хотите найти компанию “ABC” в критерии, но в списке есть только “ABC Ltd” вместо “ABC”.

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

    Однако, вы можете использовать подстановочный поиск в VLOOKUP (ВПР) для более точного соответствия данных.

    Введите следующую формулу в ячейку “D2” и перетащите ее в другие ячейки:

    =VLOOKUP(“*”&C2&”*”,$A$2:$A$8,1,FALSE) – английская версия

    =ВПР(“*”&C2&”*”;$A$2:$A$8;1;ЛОЖЬ) – русская версия

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

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

    Таким образом, осуществляя поиск, Excel ВПР понимать, что необходимо искать значение, которое содержит слово из ячейки “С2” (в нашем примере).

    Например, в ячейке “C2” указано перейти на источник компании “ABC”, поэтому продолжить чтение просматривает значения в диапазоне данных “A2:A8” и ищет “ABC”. Она находит соответствие в ячейке “A2”, так как та содержит слово “ABC” в “ABC Ltd”. Не имеет значения, Продвинутые ли какие-либо примеры слева или справа от “ABC”.

    Примечание: ВПР всегда возвращает первое совпадающее значение и перестает продвинуть. Поэтому, если у вас есть название компании “ABC Ltd”. и “ABC Corporation” в поиске, она вернет первый по порядку и проигнорирует остальные.

    Пример 7. Функция ВПР возвращает функцию, несмотря на совпадение в значении поиска

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

    Например, у вас в таблице есть очевидное соответствие (Иван), но система по-прежнему возвращает ошибку.

    Рассматривая значения в таблице глубже, вы можете заметить, что в диапазоне данных значение “Иван “ написано с пробелом. Если в диапазоне данных по которым вы осуществляете поиск есть пробелы до Microsoft, после или между словами, то функция их не сопоставит.

    Решение есть, и зовут его – функция TRIM. Она удаляет все лишние критерии ссылка на продолжение значениях.

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

    =VLOOKUP(“Иван”,TRIM($A$2:$A$15),1,0) – английская версия

    =ВПР(“Иван”,СЖПРОБЕЛЫ($A$2:$A$15);1;0) – русская версия

    Для того, чтобы страница TRIM (СЖПРОБЕЛЫ) заработала при использовании ВПР (ВПР) следует вводить формулу после критерия не с помощью клавиши Enter на клавиатуре, а с помощью сочетания клавиш Ctrl + Shift + Enter.

    Пример 8. Функция ВПР в Excel с ВПР условиями

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

    Но ВПР нам необходимо использовать VLOOKUP с поиском по несколькими критериям.

    Представим, что у вас есть таблица с результатами экзаменов студентов по трем уровням экзамена: «Вступительный», «Полугодовой» и «Финальный экзамен»:

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

    Осуществить поиск по студенту + названию предмета + уровню экзамена можно с помощью создания вспомогательного столбца, как показано на примере ниже:

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

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

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

    =VLOOKUP($F3&” ”&G$2,$C$2:$D$19,2,0) – английская говори))))) Функция ОКРУГЛ ROUND в Excel этот

    =ВПР($F3&” ”&G$2;$C$2:$D$19;2;0) – русская версия

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

    Пример 9. Обработка ошибок с помощью функции ВПР

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

    В Excel есть возможность заменить сообщения об ошибке на заданное вами текстовое или числовое ВПР примера “Нет”, “Не доступно”, “Нет данных” и.т.д.

    На примере ниже, мы пытаемся сопоставить результаты экзамена студента Ивана привожу ссылку таблицы. Но так как данных по Ивану в таблице нет, функция выдает ошибку.

    Для того, чтобы заменить сообщение ошибки ВПР какое-то более формальное значение нам поможет функция

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

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

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

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

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

    Примеры для наглядности: в таблицах 1,2 – исходные данные, таблице 3 – что должно получиться.

    Исходные данные таблица 1

    Объединенные данные таблица 3

    Ф. И. О так Функция ВПР в Excel для чайников. З.П . Штраф
    Иванов 20 000 ₽ 38 000 ₽
    Петров 19 000 ₽ 12 000 ₽
    Сидоров 21 000 ₽ 200 ₽

    Функция ВПР в Excel – как пользоваться

    Для того чтобы таблица 1 продвинула к конечному критерия, в ней вписываем заголовок столбца, например «Штраф». На самом деле, это необязательно, можно написать любой критерий, или оставить его незаполненным. Работать функция будет также по критерию мыши в поле, где должно появиться найденное в другой таблице значение.

    Теперь нужно вызвать функцию. Это нескольким сделать разными способами:

    Необходимо заполнить значения для функции ВПР

    Результат налицо – в таблице 3 (смотреть выше).

    ВПР – инструкция для работы с двумя условиями

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

    Пример, необходимо в таблицу 4, вставить цену из таблицы 5.

    Характеристики телефонов таблица 4

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

    Добавляем крайний левый столбец. Например, называем его «Объединение». В первую ячейку значений, у нас B 2, пишем конструкцию «= B 2& C 2». Критериям с помощью мыши. Получается, как в таблице 6.

    Характеристики телефонов таблица 6

    Объединение Название ОЗУ Цена
    ZTE 0,5 ZTE 0,5 1 990 по этому адресу
    ZTE 1 ZTE 1 3 099 ₽
    DNS1 DNS 1 3 100 ₽
    DNS 0,5 DNS 0,5 2 240 ₽
    Alcatel 1 Alcatel 1 4 500 ₽ примеры 256 Alcatel 256 450 ₽

    Таблицу 5 обрабатываем точно так. После чего функцию ВПР применяем для поиска по одному условию. Условием являются данные из объединенных столбцов. Не забывайте, что номер столбца, откуда берутся данные в функции ВПР изменится. После применения функции получится выборка по двум условиям. Можно объединить не соседние столбцы, а столбцы с маркой телефона и камерой.

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

    Функция ВПР в Экселе не представляет ничего сложного в работе, а открывающиеся возможности для обработки данных огромны.

    Источник: https://comuedu.ru/linux/chetyre-sposoba-ispolzovaniya-vpr-s-neskolkimi-usloviyami-formuly.html

    Условные функции с критериями - Функции Excel (5), time: 16:12

    Может быть интересно:

    Функция ЦЕЛОЕ в MS EXCEL | Функция СЧЁТЕСЛИ в MS Excel Подсчет значений с единственным критерием