Вся суть функции ВПР в Excel (для начинающих пользователей)

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

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

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

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

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

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

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

Итого, соединяя все вышеперечисленное в одну формулу, получаем для зеленой ячейки решение:

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

или в английском варианте

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

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

vlookup2d2.png

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

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

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

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

Разница только в последнем аргументе обеих функций ПОИСКПОЗ (MATCH)Типу сопоставления (здесь он равен минус 1). Это некий аналог четвертого аргумента функции ВПР (VLOOKUP) – Интервального просмотра (Range Lookup). Вообще говоря, возможных значений для него три:

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

Важно отметить, что при использовании приблизительного поиска с округлением диапазон поиска – а значит и вся таблица – должна быть отсортирована по возрастанию (для Типа сопоставления = 1) или по убыванию (для Типа сопоставления = -1) по строчкам и по столбцам. Иначе приблизительный поиск корректно работать не будет!

Для точного поиска (Тип сопоставления = 0) сортировка не нужна и никакой роли не играет.

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

В комментах неоднократно интересуются – а как сделать обратную операцию, т.е. определить в первом примере город и товар если мы знаем значение из таблицы? Тут потребуются две небольшие формулы массива (не забудьте ввести их с помощью сочетания клавиш Ctrl+Shift+Enter, а не обычного Enter):

Как подготовить ребенка к ВПР

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

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

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

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

  • Для хороших результатов диктанта по русскому языку полезным будет развитие навыка ребенка писать под диктовку разных людей. За четыре года ребенок привыкает к голосу своего преподавателя, его интонации и тембру.
  • Для подготовки к ВПР можно использовать банк заданий, который размещен на образовательных ресурсах, и дополнительно заниматься дома.
  • Необходимо повышать уровень общей эрудиции ребенка, читать книги и энциклопедии.
  • Не создавать дополнительный стресс для ребенка вокруг ВПР, быть спокойными, не предъявлять завышенных требований.
  • Не пытаться «впихнуть» в голову ребенка все знания за последний месяц, это приведет к перенапряжению.

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

Функция ПРОСМОТРX (XLOOKUP) вместо ВПР, ГПР и других функций в Excel

Научитесь использовать все прикладные инструменты из функционала MS Excel.

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

Перед разработчиками из Microsoft стояла задача подобрать достойную замену, и они не ударили в грязь лицом, выпустив функцию ПРОСМОТРX (XLOOKUP), которая представляет собой не только улучшенную версию ВПР, но ещё и замену ГПР, ИНДЕКС и ПОИСКПОЗ. Если хотите узнать о других полезных функциях Excel, то рекомендуем записаться на бесплатный онлайн-курс «Аналитика в Excel».

Пару слов про функцию ПРОСМОТРX

Прежде чем приступать к работе с функцией ПРОСМОТРХ, нужно учесть 3 момента:

1. Версию Excel: на данный момент, осень 2020 года, ПРОСМОТРX доступен только пользователям Office 365, хотя поговаривают, что эту брешь залатают в Standalone-версии Microsoft Office 2021.

2. Буква Х в названии функции – английская, поэтому велика вероятность ошибки при дотошном вводе; лучше всего использовать автозавершение ввода функций с помощью клавиши Tab.

3. Перебирать аргументы и диапазоны (не только в ПРОСМОТРХ, но и в других функциях) намного быстрее с зажатой клавишей Ctrl, чем с помощью ручного ввода точек с запятой.

Про атрибуты и возможности функции мы поговорим уже в боевых условиях, разбираясь с тем, что же всё-таки умеет делать ПРОСМОТРХ.

Левый ВПР? Я могу орудовать, где угодно!

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

пример данных на одном листе

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

данные на другом листе

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

«Загвоздка?!» – удивляется ПРОСМОТРХ. «Да ещё чего!».

Специально используем «Мастер функций» комбинацией Shift+F3 (или кнопочка Fx слева от строки ввода формул), чтобы показать аргументы.

используем функцию ПРОСМОТРХ

Искомое значение – это ячейка А2, то есть Gary Miller, с него мы начнём выводить бонусы.

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

Возвращаемый массив – тот самый столбец бонусов, которые мы хотим достать. Тоже забираем его со второго листа, зафиксировав с помощью F4.

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

Режим сопоставления – классическая указка Excel для поиска точных или приблизительных значения. В данном случае выбираем 0 – точный поиск.

Протягиваем результат в ячейке ниже и сразу обращаем внимание на 0 – это те сотрудники, которых ПРОСМОТРХ не обнаружил в списке.

Кстати, если изначально функция везде выдаёт вам нули, то нужно проверить формат ячеек и проставить там числовой

Чтобы посчитать годовой оклад, можно умножить наши результаты на показатели столбца В, то есть дописываем в формулу *B2:

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

Иди домой, ГПР!

Раз мы сказали о том, что ПРОСМОТРХ умеет работать в любых направлениях, то и с заменой ГПР, которая считает по строкам, тоже проблем не будет.

замена гпр на ПРОСМОТРХ

Заберём аналогичные данные для бонуса по сотрудникам, как делали ранее, но теперь обратимся к горизонтально развёрнутой табличке на другом листе. Естественно, строка с бонусом находится выше строки с сотрудниками, что в случае с обычным ГПР нам принесло бы ошибку. Сейчас же мы действуем по накатанной схеме:

Указываем ячейку с сотрудником. Затем выбираем диапазон с сотрудниками, в котором ПРОСМОТРХ находит искомое значение. Фиксируем по нажатию F4.

Далее указываем строку с бонусами, тоже фиксируем через F4.

Если ничего не найдено, ставим 0.

Точность поиска – тоже 0, точный поиск.

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

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

Кажется, на нём были очки…

Представим, что необходимо достать все реквизиты сотрудника, но мы знаем лишь его фамилию (или какие-то другие отличительные знаки). Попробуем извлечь данные человека по фамилии Willard в отдельной ячейке.

извлечение данных

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

В первый аргумент, перед искомой ячейкой G2, дописываем “*”. Звёздочка – это служебный символ, которого мы сцепляем амперсандом (&) с ячейкой G2 и таким образом говорим программе, чтобы осуществлялся поиск не только Willard, но и всего остального содержимого ячейки до Willard. То есть первый аргумент у нас примет вид: «*»&G2

Второй аргумент – искомый массив, то есть столбец с именами: A2:A19

Третий аргумент – возвращаемый массив, то есть опять столбец с именами: A2:A19

Четвёртый аргумент – пропускаем

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

Вот мы и получили James Willard.

«Двойной ПРОСМОТРХ» или «Зависимые выпадающие списки»

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

Сперва добавим в нашу таблицу новый столбец под названием «Итоговый платёж», в котором будет осуществляться суммирование столбцов B и C.

Теперь добавим выпадающий список в ячейку G7.

Вкладка «Данные» – «Проверка данных». Тип данных – список.

В поле «Источник» указываем диапазон с нашими сотрудниками из столбца А. Жмём ОК.

Проверяем. Список работает.

Ниже, в ячейке G8, создаём второй выпадающий список, вот только в поле «Источник» указываем заголовки столбцов из шапки таблицы, то есть протягиваем строку от Годового оклада до Итогового платежа. Нажимаем ОК.

Тоже работает. Едем дальше.

Всё готово для встраивания двойного ПРОСМОТРХ.

Переходим в ячейку G9, хотя вы можете выбрать абсолютно любую ячейку.

Начинаем вводить формулу.

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Мы ищем имя из выпадающего списка выше, то есть ячейка G7 – это первый аргумент.

Затем выбираем диапазон сотрудников – это второй аргумент.

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

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

Второй аргумент – это шапка таблицы.

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

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

Готово. Теперь, в зависимости от выбранного сотрудника в первом списке, переключаются его показатели в различных столбцах из второго. Шикарно!

Как насчёт подсветки выбираемых имён?

В качестве бонуса можете прикрутить сюда условное форматирование, чтобы оно подсвечивало строку по выбранному имени. Мы же смотрим отчётность, это было бы крайне удобно!

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

Обводим всю нашу таблицу. Переходим на вкладку «Главная», потом «Условное форматирование», затем «Создать правило».

Выбираем «Использовать формулу для определения форматируемых ячеек».

Сперва зададим формат. Зальём каким-нибудь цветом. Окрасим шрифт. Должно выйти неплохо. Теперь переходим к прописыванию формулы.

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

$F$7 – наша зафиксированная ячейка с именем из выпадающего списка.

0 – просматриваемый массив, то есть ПОИСКПОЗ находит первое значение, равное искомому.

Нажимаем ОК и ОК далее.

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

ПРОСМОТРХ вместо задания условий

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

Если зарплата равна 10 000 – никакого бонуса, 30 000 – 5% бонусом, 50 000 – 8% бонусом и так далее. Определим, кому какой бонус полагается с помощью ПРОСМОТРХ.

Создадим новый столбец «Новый бонус» на основном листе.

Пишем в первой ячейке нового столбца формулу:

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

В качестве второго аргумента указываем столбец с жалованием из бонусной таблички с другого листа, то есть у нас будет Лист2!F13:$F$17.

Возвращаемый массив – уже бонусный проценты, то есть бонусный столбец из бонусной таблички с другого листа – Лист2!G13:$G$17.

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

И самое главное – пятый аргумент, который отвечает за точность поиска. Указываем там -1, то есть точное совпадение или следующее меньшее значение.

Теперь нужно проверить результаты.

Гари Миллеру досталось 10% бонусов. Идём на бонусную табличку и смотрим.

Его оклад составляет 60 000 – это 10%. Следующий оклад для бонусов уже 100 000 и 15% соответственно. Что сделал Excel: он нашёл 60 000 и затем отобрал следующее минимальное значение, то есть 10%.

Никаких условий и никаких подборов. Одна только функция ПРОСМОТРХ помогла нам решить целую вереницу задач.

Научитесь использовать все прикладные инструменты из функционала MS Excel.

ВПР эксель как пользоваться

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

Искомое значение – значение для поиска. Значение, которое вы хотите найти. Поиск этого значения будет в первом столбце диапазона ячеек, указанного в аргументе – Таблица.

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

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

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

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

Читайте также:  Как посчитать процентное распределение в Excel по формуле

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

В текущем примере у нас таблица начинается со столбца «Табельный номер» и заканчивается столбцом «Зарплата», поэтому номер столбца будет 2. Т.к. он второй в нашей таблице.

Интервальный просмотр – число 0 или 1.

(Ложь [False]) Точное совпадение–осуществляет поиск точного значения в первом столбце(если не находит –#Н/Д [#N/A]).

1 (Истина [True]) Приближенное соответствие–если нет совпадения, то выдает max_значение <искомого, при этом выделенная таблица должна быть отсортирована по первому столбцу по возрастанию.

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

А пока просто запомните, что здесь всегда ставим 0.

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

Выше мы работали с одним условием – наименованием материалов. Но в реальности обстоятельства могут сложиться иначе. Мы можем получить таблицу с двумя и более условиями, например, как с наименованием материалов, так и с наименованием их поставщиков:

funkcii-excel9-13

Таблица с наименованиями поставщиков и материалов

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

Попробуем решить эту задачу:

  • Добавьте в таблицу левый крайний столбец и объедините два столбца – «Материалы» и «Поставщики».

funkcii-excel9-14

Объедините два столбца – «Материалы» и «Поставщики»

  • Точно так же объедините искомые критерии запроса

funkcii-excel9-15

Объедините искомые критерии запроса

  • Снова в пункте «Таблица» задайте соответствующие показатели

2

В пункте «Таблица» задайте такие показатели

  • На скриншоте выше мы видим следующую формулу (1 – объект поиска; 2 – место поиска; 3 – взятые нами данные).

funkcii-excel9-16

1 – объект поиска; 2 – место поиска; 3 – взятые нами данные

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

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

Выполнить эту задачу можно при помощи конструкции:

Ввод и использование функции ВПР в Excel

  • B2 – это исходник по которому будет идти сопоставление с первой таблицей;
  • H2:J26 – область поиска;
  • 2 – сюда включается номер строки, который потребуется подставить при нахождении совпадения;
  • 0 — передает точное значение, если выбрать 1 то будет передано приблизительное значение.

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

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

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

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

Функция ВПР в Excel примеры

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

Функция ВПР на разных листах

На практике формулы ВПР редко используются для поиска данных на одном листе. Чаще всего вам придется искать и вытаскивать соответствующие данные с другого листа.

Чтобы использовать функцию ВПР с другого листа Excel, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(40;Лист2!A2:B15;2). Формула указывает, что диапазон поиска A2:B15 находится в Лист2.

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

Формула, которую вы видите на изображении ниже, ищет текст в ячейке А2 (« Продукт 3 ») в столбце A (1-й столбец диапазона поиска A2:B9) на листе « Цены »:

Функция ВПР в Excel – Функция ВПР на разных листах

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

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

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

Чтобы создать именованный диапазон, просто выберите ячейки и введите любое имя в поле « Имя », слева от панели « Формула ».

Функция ВПР в Excel – Присвоение имени диапазону

Теперь вы можете написать следующую формулу ВПР, чтобы получить цену Продукта 1:

Функция ВПР в Excel – Пример функции ВПР с именем диапазона

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

Если вы преобразовали диапазон ячеек в полнофункциональную таблицу Excel (вкладка «Вставка» —> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, а Microsoft Excel автоматически добавит имена колонок или имя таблицы в формулу:

Функция ВПР в Excel – Пример функции ВПР с именем таблицы

Полная формула может выглядеть примерно так:

или даже =ВПР(«Продукт 1»;Таблица6;2).

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

Самый быстрый ВПР

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

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

Когда я писал свою первую книжку пять лет назад, то уже делал сравнительный скоростной тест различных способов поиска и подстановки данных функциями ВПР, ИНДЕКС+ПОИСКПОЗ, СУММЕСЛИ и др. С тех пор сменилось три версии Office, появились надстройки Power Query и Power Pivot, кардинально изменившие весь процесс работы с данными. А в прошлом году ещё и обновился вычислительный движок Excel, получив поддержку динамических массивов и новые функции ПРОСМОТРХ, ФИЛЬТР и т.п.

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

Подопытный кролик

Тест будем проводить на следующем примере:

Исходный пример

Это книга Excel с одним листом, где расположены две таблицы: отгрузки (500 000 строк) и прайс-лист (600 строк). Наша задача – подставить цены из прайс-листа в таблицу отгрузок. Для каждого способа будем вводить формулу в ячейку С2 и копировать вниз на весь столбец, замеряя время, которое потребуется Excel, чтобы просчитать весь столбец из полумиллиона ячеек. Полученные значения, безусловно, зависят от множества факторов (поколение процессора, объем оперативной памяти, текущая загрузка системы, версия Office и т.д.), но нам важны не конкретные цифры, а, скорее, их сравнение друг с другом. Важно понимать прожорливость каждого способа и их ограничения.

Способ 1. ВПР

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

ВПР

  • B2 – искомое значение, т.е. название товара, который мы хотим найти в прайс-листе
  • $G$2:$H$600 – закреплённая знаками доллара (чтобы не сползала при копировании формулы вниз) абсолютная ссылка на прайс
  • 2 – номер столбца в прайс-листе, откуда мы хотим взять цену
  • или ЛОЖЬ – переключение в режим поиска точного соответствия, когда любое некорректное название товара (например, ФОНЕРА) в столбце B в таблице отгрузок приведёт к появлению ошибки #Н/Д как результата работы функции.

Время вычисления = 4,3 сек.

Способ 2. ВПР с выделением столбцов целиком

Многие пользователи, применяя ВПР, во втором аргументе этой функции, где нужно задать поисковую таблицу (прайс), выделяют не ограниченный диапазон ( $G$2:$H$600 ), а сразу столбцы G:H целиком. Это проще, быстрее, позволяет не думать про F4 и то, что завтра прайс-лист может быть на несколько строк больше. Формула в этом случае выглядит тоже компактнее:

ВПР с выделением столбцов целиком

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

Время вычисления = 14,5 сек.

Способ 3. ИНДЕКС и ПОИСКПОЗ

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

ИНДЕКС и ПОИСКПОЗ

Функция ИНДЕКС извлекает из заданного в первом аргументе диапазона (столбца $H$2:$H$600 с ценами в прайс-листе) содержимое ячейки с заданным номером. А номер этот, в свою очередь, определяется функцией ПОИСКПОЗ, у которой три аргумента:

  • Что нужно найти – название товара из B2
  • Где мы это ищем – столбец с названиями товаров в прайсе ( $G$2:$G$600 )
  • Режим поиска: – точный, 1 или -1 – приблизительный с округлением в меньшую или большую сторону, соответственно.

Формула выходит чуть сложнее, но, при этом имеет несколько ощутимых преимуществ перед классической ВПР, а именно:

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

По скорости, однако же, этот способ проигрывает ВПР почти в два раза:

Время вычисления = 7,8 сек.

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

ИНДЕКС и ПОИСКПОЗ с выделением столбцов целиком

. то результат получается совсем печальный:

Время вычисления = 28,5 сек.

28 секунд, Карл! В 6 раз медленнее ВПР!

Способ 4. СУММЕСЛИ

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

СУММЕСЛИ

  • Первый аргумент СУММЕСЛИ – это диапазон проверяемых ячеек, т.е. названия товаров в прайсе ( $G$2:$G$600 ).
  • Второй аргумент ( B2 ) – что мы ищем.
  • Третий аргумент – диапазон ячеек с ценами $H$2:$H$600 , числа из которых мы хотим просуммировать, если в соседних ячейках проверяемого диапазона есть искомое значение.

Очевидным минусом такого подхода является то, что он работает только с числами. Также этот способ не удобен, если прайс-лист находится в отдельном файле – придется всё время держать его открытым, т.к. функция СУММЕСЛИ не умеет брать данные из закрытых книг, в отличие от ВПР, для которой это не проблема.

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

Время вычисления = 12,8 сек.

При выделении столбцов целиком, т.е. использовании формулы вида =СУММЕСЛИ( G:G ; B2 ; H:H ) всё ещё хуже:

Время вычисления = 41,7 сек.

Это самый плохой результат в нашем тесте.

Способ 5. СУММПРОИЗВ

Этот подход сейчас встречается не часто, но всё ещё достаточно регулярно. Обычно так любят извращаться пользователи старой школы, ещё хорошо помнящие те времена, когда в Excel было всего 255 столбцов и 56 цветов 🙂

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

СУММПРОИЗВ

Выражение ($G$2:$G$600=B2) , по сути, проверяет каждое название груза в прайс-листе на предмет соответствия искомому значению (ФАНЕРА ПР). Результатом каждого сравнения будет логическое значение ИСТИНА (TRUE ) или ЛОЖЬ (FALSE) , что в Excel интерпретируется как 1 и 0, соответственно. Последующее умножение этих нулей и единиц на цены оставит в живых цену только того товара, который нам, в данном случае, и нужен.

Эта формула является, по сути, формулой массива, но не требует нажатия обычного для них сочетания клавиш Ctrl + Shift + Enter , т.к. функция СУММПРОИЗВ поддерживает массивы уже сама по себе. Возможно, по этой же причине (формулы массива всегда медленнее, чем обычные) такой скорость пересчёта такой формулы – не очень:

Время вычисления = 11,8 сек.

  • Совместимость с любыми, самыми древними версиями Excel.
  • Возможность задавать сложные условия (и несколько)
  • Способность этой формулы работать с данными из закрытых файлов, если добавить перед ней двойное бинарное отрицание (два подряд знака “минус”). СУММЕСЛИМН таким похвастаться не может.

Способ 6. ПРОСМОТР

Ещё один относительно экзотический способ поиска и подстановки данных, наравне с ВПР – это использование функции ПРОСМОТР (LOOKUP) . Только не перепутайте её с новой, буквально, на днях появившейся функцией ПРОСМОТРХ (XLOOKUP) – про неё мы поговорим дальше особо. Функция ПРОСМОТР существовала в Excel начиная с самых ранних версий и тоже вполне может решить нашу задачу:

ПРОСМОТР

  • B2 – название груза, которое мы ищем
  • $G$2:$G$600 – одномерный диапазон-вектор (столбец или строка), где мы ищем совпадение
  • $H$2:$H$600 – такого же размера диапазон, откуда нужно вернуть найденный результат (цену)

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

  • Эта функция требует обязательной сортировки прайс-листа по возрастанию (алфавиту) и без этого не работает.
  • Если в таблице отгрузок искомое значение будет написано с опечаткой (например, АГ Е ДОЛ вместо АГИДОЛ), то функция ПРОСМОТР выдаст не ошибку #Н/Д, а цену для ближайшего предыдущего товара:

При работе с неидеальными данными в реальном мире это гарантированно создаст проблемы, как вы понимаете.

Скорость же вычислений у функции ПРОСМОТР (LOOKUP) весьма приличная:

Время вычисления = 7,6 сек.

Способ 7. Новая функция ПРОСМОТРХ

Эта функция пришла с одним из недавних обновлений пока только пользователям Office 365 и пока отсутствует во всех остальных версиях (Excel 2010, 2013, 2016, 2019). По сравнению с классической ВПР у этой функции есть масса преимуществ (упрощенный синтаксис, возможность искать не только сверху-вниз, возможность сразу задать значение вместо #Н/Д и т.д.) Формула для решения нашей задачи будет выглядеть в этом случае так:

ПРОСМОТРХ

Если не брать в расчёт необязательные 4,5,6 аргументы, то синтаксис этой функции полностью совпадает с её предшественником – функцией ПРОСМОТР (LOOKUP) . Скорость вычислений при тестировании на наши 500000 строк тоже оказалась аналогичной:

Время вычисления = 7,6 сек.

Почти в два раза медленнее, чем у ВПР, вместо которой Microsoft предлагает теперь использовать ПРОСМОТРХ. Жаль.

И, опять же, если полениться и выделить диапазоны в прайс-листе целыми столбцами:

ПРОСМОТРХ и выделение столбцов целиком

. то скорость падает до совершенно неприличных уже значений:

Время вычисления = 28,3 сек.

А если на динамических массивах?

Прошлогоднее (осень 2019) обновление вычислительного движка Microsoft Excel добавило ему поддержку динамических массивов (Dynamic Arrays), о которых я уже писал. Это принципиально новый подход к работе с данными, который можно использовать почти с любыми классическими функциями Excel. На примере ВПР это будет выглядеть так:

ВПР на динамических массивах

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

Скорость пересчета в таком варианте меня, откровенно говоря, ошеломила – пауза между нажатием на Enter после ввода формулы и получением результатов почти отсутствовала.

Время вычисления = 1 сек.

Что интересно, и новая ПРОСМОТРХ, и старая ПРОСМОТР, и связка ИНДЕКС+ПОИСКПОЗ в таком режиме тоже были очень быстрыми – время вычислений не больше 1 секунды! Фантастика.

А вот олдскульные подходы на основе СУММПРОИЗВ и СУММЕСЛИ(МН) с динамическими массивами работать отказались 🙁

Что с умными таблицами?

Обрадовавшись фантастическим результатам, полученным на динамических массивах, я решил вдогон попробовать протестировать разницу в скорости при работе с обычными и “умными” таблицами. Я имею ввиду те самые “красивые таблицы”, в которые вы можете преобразовать ваш диапазон с помощью команды Форматировать как таблицу на вкладке Главная (Home – Format as Table) или с помощью сочетания клавиш Ctrl + T .

Если предварительно превратить наши отгрузки и прайс в “умные” (по умолчанию они получат имена Таблица1 и Таблица2, соответственно), то формула с той же ВПР будет выглядеть как:

ВПР на умных таблицах

  • [@Груз] – ссылка на ячейку B2, означающая, в данном случае, что нужно взять значение из той же строки из столбца Груз текущей умной таблицы.
  • Таблица2 – ссылка на прайс-лист

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

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

Читайте также:  Функция ПЕРЕКЛЮЧ для создания переключателя значений в Excel

Время вычисления = 1 сек.

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

Бонус. Запрос Power Query

Замерять, так замерять! Давайте, для полноты картины, сравним наши перечисленные способы еще и с запросом Power Query, который тоже может решить нашу задачу. Кто-то скажет, что некорректно сравнивать пересчёт формул с механизмом обновления запроса, но мне, откровенно говоря, просто самому было интересно – кто быстрее?

  1. Превращаем обе наши таблицы в “умные” с помощью команды Форматировать как таблицу на вкладке Главная (Home – Format as Table) или с помощью сочетания клавиш Ctrl + T .
  2. По очереди загружаем таблицы в Power Query с помощью команды Данные – Из таблицы / диапазона (Data – From Table/Range) .
  3. После загрузки в Power Query возвращаемся обратно в Excel, оставляя загруженные данные как подключение. Для этого в окне Power Query выбираем Главная – Закрыть и загрузить – Закрыть и загрузить в. – Только создать подключение (Home – Close&Load – Close&Load to. – Only create connection) .
  4. После того, как обе исходные таблицы будут загружены как подключения, создадим ещё один, третий запрос, который будет объединять их между собой, подставляя цены из прайса в отгрузки. Для этого на вкладке Данные выберем Получить данные / Создать запрос – Объединить запросы – Объединить (Get Data / New Query – Merge queries – Merge) :

Объединяем запросы

Настройки объединения

Разворачиваем вложенные таблицы после объединения

В отличие от формул, запросы Power Query не обновляются автоматически “на лету”, а требуют щелчка правой кнопкой мыши по таблице (или запросу в правой панели) и выбору команды Обновить (Refresh) . Также можно воспользоваться командой Обновить все (Refresh All) на вкладке Данные (Data) .

Время обновления = 8,2 сек.

Итоговая таблица и выводы

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

Итоговая таблица результатов

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

Использование СУММПРОИЗВ

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

Принцип работы данной формулы схож с принципом работы предыдущего подхода. Создается виртуальная таблица, в которой сравниваются значения ячеек G1 и G2 с диапазонами B2:B13 и С2:С13 соответственно. Далее оба этих массива сопоставляются и получается массив из единиц и нулей, где единица присваивается той строке, в которой оба условия совпали. Далее данный виртуальный массив перемножается на диапазон D2:D13. Так как в нашем виртуальном массиве будет только одна единица в шестой строке, формула вернёт результат 189.

Данная функция не будет работать, если в диапазоне D2:D13 имеются текстовые значения.

Чтобы понять, как работает данная формула, рекомендую прочитать статью о функции СУММПРОИЗ.

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

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

Как же конкретно работает формула ВПР

  • Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
  • Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром <ЧТО>, в противном случае возвращается код ошибки #Н/Д (#N/A).
    Param4-False

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

Кому лень или нет времени читать – смотрим видео. Подробности и нюансы – в тексте ниже.

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

Итак, имеем две таблицы – таблицу заказов и прайс-лист:

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

Решение

В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP) . Эта функция ищет заданное значение (в нашем примере это слово “Яблоки”) в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:

Для простоты дальнейшего использования функции сразу сделайте одну вещь – дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме “шапки” (G3:H19), выберите в меню Вставка – Имя – Присвоить (Insert – Name – Define) или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы – Вставка функции (Formulas – Insert Function) . В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:

vlookup3.png

Заполняем их по очереди:

  • Искомое значение (Lookup Value) – то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае – слово “Яблоки” из ячейки B3.
  • Таблица (Table Array) – таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя “Прайс” данное ранее. Если вы не давали имя, то можно просто выделить таблицу, но не забудьте нажать потом клавишу F4 , чтобы закрепить ссылку знаками доллара , т.к. в противном случае она будет соскальзывать при копировании нашей формулы вниз, на остальные ячейки столбца D3:D30.
  • Номер_столбца (Column index number) – порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.
  • Интервальный_просмотр (Range Lookup) – в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:
      • Если введено значение или ЛОЖЬ (FALSE) , то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, “Кокос”), то она выдаст ошибку #Н/Д (нет данных).
      • Если введено значение 1 или ИСТИНА (TRUE) , то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия, т.е. в случае с “кокосом” функция попытается найти товар с наименованием, которое максимально похоже на “кокос” и выдаст цену для этого наименования. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле! Так что для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать. Исключением является случай, когда мы ищем числа, а не текст – например, при расчете Ступенчатых скидок.

      Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.

      Ошибки #Н/Д и их подавление

      Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если:

      • Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.
      • Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.
      • Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
        =ВПР(ТЕКСТ(B3);прайс;0)
        Подробнее об этом можно почитать тут.
      • Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:
        =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)
        =VLOOKUP(TRIM(CLEAN(B3));прайс;0)

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

      Если нужно извлечь не одно значение а сразу весь набор (если их встречается несколько разных), то придется шаманить с формулой массива. или использовать новую функцию ПРОСМОТРX (XLOOKUP) из Office 365.

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

        .
      • Функции VLOOKUP2 и VLOOKUP3 из надстройки PLEX

      :)

      Не за что – стараемся по мере сил и способностей Все мои видеоуроки можно найти (и подписаться на них) на канале planetaexcel на YouTube.

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

      :)

      Спасибо Вам, Николай, как раз то что надо!

      :)

      Сцепить два ваших критерия в отдельном столбце в один и делать ВПР по нему.
      Либо написать свой вариант ВПР на Visual Basic

      Подскажите, почему формула не работает? Составил Таблицу из 2-х столбцов, в первом Наименование, во втором параметр. Рядом 2 ячейки – выбор Наименования (через выпадающий список), во второй должно ставиться значение через формулу ВПР. Но появляются ошибки: При выборе Строчки “Ручка Опера” значение подставляется неверно, При выборе строчки “Ручка Бридж” значения не находит.

      Ручка Н 54
      Ручка С 36
      Ручка Сэко 16
      Ручка Опера 37
      Ручка Тауэр 62
      Ручка Бридж 2

      P.S.: и как сюда пример готовый добавить?

      ;)

      Спасибо, экономил очень много времении )))))

      Помогите, ВПР формулу прописал. Перепроверил. И мне в нужном месте вместо числового значения выдало ошибку #ССЫЛКА! Что делать и как ее обойти. И вообще почему она появилась? Для понимания проблемы. Всё сделал как в указанном примере. Но результат я уже написал.

      :)

      УРРРЯЯЯЯЯЯ, сам разобрался. Проблема решилась когда я повторно пересмотрел урок и понял, что столбец указал не правильный для цены. Нужно указывать номер стоблца именно в ДИАПАЗОНЕ В КОТОРОМ БУДЕТ ВЫБИРАТЬСЯ ЦЕНА, а не столбец по порядку начиная с первого.

      ;)

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

      Б 826Д*60*100 1 067,00 Нет этого товара в Ч.Р. 10 937 Б 826Д*60*100
      Б 826Д*60*70 914,00 914,00 10 939 Б 826Д*60*70
      Б 826Д*60*80 965,00 965,00 10 940 Б 826Д*60*80
      Б 826Д*60*90 1 016,00 1 016,00 10 941 Б 826Д*60*90
      Б 826Д*61*90,5 1 042,00 1 042,00 10 942 Б 826Д*61*90,5
      Б 826Д*62*96 1 067,00 1 067,00 10 943 Б 826Д*62*96
      Б 826Д*63*73 965,00 965,00 10 944 Б 826Д*63*73
      914,00 Б 826Д**

      Николай, как быть в случае если цены в течении периода меняются. Предполагаем что в ТАБЛИЦЕ ЗАКАЗОВ есть столбец ДАТА. И в ПРАЙС-ЛИСТЕ есть столбцы ЦЕНА НА ЯНВАРЬ, ЦЕНА НА ФЕВРАЛ и т.д.

      Очень полезная штука!
      Николай, у меня вопрос.
      Есть три таблицы:
      1. Список товаров с артикулами и другими данными
      2. Таблица соответствия артикула уникальному номеру (2 столбца).
      3. Таблица соответствия уникального номера пути к фотографиям. В этой таблице два столбца, в одном уникальный номер, в другом путь к фото товара. Фотографий может быть как одна так и несколько. То есть айди может быть в пяти йчейках напротив которых ячейка с указанием пути.

      Вопрос: Как в первую таблицу вытащить пути к нескольким фотографиям одного товара в одну ячейку из третей через вторую? При этом пути к фото разместить через символ | например.

      Заранее огромное спасибо!

      Николай, здравствуйте!
      У меня вопрос. Может ли ВПР найти артикул с текстом?
      Например, “525 393-090” ищем в “Кроссовки 525 393-090”

      Заранее огромное спасибо!

      ВПР не может, но можно по-другому:

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

      Непонятки вот в чём.

      По описанию функции ПРОСМОТР, аргумент №1 — строка с искомым значением, аргумент №2 — область-вектор (т.е. одномерная), в которой происходит поиск ячейки со значением, заданным аргументом №1, аргумент №3 — область-вектор, из которой выбирается значение-результат. То есть, если в области “аргумент №2” находится ячейка, значение которой равно “аргумент №1” — функция вычисляет порядковый номер этой ячейки в этой области и возвращает значение ячейки уже из области “аргумент №3” с тем же порядковым номером.

      Вопрос 1. В этом свете, непонятно в вашем примере, какой смысл имеет на месте аргумента №1 значение 2^15 ?
      Вопрос 2. В качестве аргумента №2 в вашей формуле стоит функция ПОИСК, результатом работы которой является вроде как «позиция первого вхождения знака или текстовой строки». А по описанию функции ПРОСМОТР, аргументом №2 должен быть дипазон ячеек (область). Как так?
      Вопрос 3. По описанию фукции ПОИСК её аргумент №1 — строковый (текстовой), а в вашей формуле на этом месте — дипазон ячеек (область). Как так?
      Вопрос 4. Ещё по функции ПОИСК. Когда я скопировал часть вашей формулы (аргумент №2 из ПРОСМОТР) в отдельную ячейку, чтобы посмотреть промежуточный результат, то есть, =ПОИСК(F$2:F$4;A2) — к моему недоумению результатом оказалось #ЗНАЧ! ! То есть, фукция отдельно — вроде неправильная, но внутри другой функции как-то работает?

      ;-)

      Повторю, вся эта конструкция (формула) тем не менее, работает! Шаманство какое-то.

      .

      После долгих размышлений и экспериментов я догадался (в экселовской СПРАВКЕ об этом ни пол-слова! ), что конструкция ПОИСК(F$2:F$4;A2) возвращает виртуальный массив (!) из 4-х элементов (для удобства далее я его буду условно называть “ Просматриваемый_Вектор ”), с значениями, являющимися результатом поиска в значении ячейки A2 значений из соответствующих ячеек массива F$2:F$4 (далее буду условно называть “ Массив_Примет ”),
      то есть,
      в результирующий массив в элемент Просматриваемый_Вектор (1) помещается результат обычной функции ПОИСК(F$2;A2) (как бы, ПОИСК( Массив_Примет (1);A2)), в элемент Просматриваемый_Вектор (2) — результат ПОИСК(F$3;A2) (как бы, Массив_Примет (2);A2), и т. д.
      То есть, в элементы Просматриваемый_Вектор (i) помещается: либо число (найденная в A2 позиция искомой подстроки Массив_Примет (i)), либо значение #ЗНАЧ! (если значение-подстрока Массив_Примет (i) в A2не найдена).

      Что-ж, с моими вопросами 2, 3, 4 из предыдущего сообщения вроде разобрались. (За исключением того, что такое использование функции ПОИСК в экселовской Справке — отсутствует, т.е. — это недокументированная возможность?)

      Остался вопрос 1 , по функции ПРОСМОТР:
      Почему для выявления среди элементов массива Просматриваемый_Вектор элемента, содержащего число, их надо сравнивать именно c значением 2^15 ?

      И ещё: В экселовской справке по функции ПРОСМОТР отмечено: «Значения в аргументе `просматриваемый_вектор` должны быть расположены в порядке возрастания. в противном случае функция ПРОСМОТР может вернуть неверный результат.».
      В то же время, в вашем примере, в возвращаемом функцией ПОИСК виртуальном массиве Просматриваемый_Вектор положение элемента, содержащего число (т.е. соответствующего искомой подстроке) — относительно других элементов произвольное. Как это может сказаться на правильности работы ПРОСМОТР?

      Я правильно понимаю, что Вы исключили в функции VLOOKUP 2 такую вещь как ” приблизительный поиск (Интервальный просмотр=1) ” ?
      Т.е. она ищет только точно совпадение. Верно ?
      У меня потребность именно в приблизительном поиске и лучше (крайне желательно) без сортировки таблицы.

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

      =ВПР(A2;$J$2:$L$97864;3;0)

      а собственно значения (хотя бы #Н/Д ) не выдается. Много раз следовала Вашему уроку, но не получается..

      Помогите пожалуйста какая то ошибка в формуле, интересует формула в клетке `M5`, там гдето ошибка, так как если правильно, то оно должно выдать результат аналогичный как в клетке `L5`, помогите найти ошибку пожалуйста..

      Николай, добрый день.

      Есть такая таблица:

      A B C
      1 ИМЯ
      2 500

      при помощи функции ВПР (искомое значение “ИМЯ”;) мы можем присвоить значение из строки №1. Но, там пустые ячейки.
      Как присвоить значение из строки, которая находится ниже? В данном примере “500”

      Добрый день! А если в таблице позиции повторяются, а значения у них разные? ВПР цепляет тогда первое совпадение и получаются некорректные данные.

      Например структура таблицы такая:
      СКЛАД 1
      яблоки 15 кг
      груши 20 кг
      апельсины 15 кг
      СКЛАД 2
      арбузы 30 кг
      дыни 30 кг
      яблоки 10 кг

      В этом случае получается всегда “яблоки 15 кг”

      Доброе время суток, Николай, а если ситуация вот такая:
      Наименование
      Сочное Яблоко красное 121212
      Сочное Яблоко зеленое 333222
      Сочное яблоко грени 4443344
      Сочная Груша зелёная 33333
      Сочная Груша желтая 55555
      и т.д. до бесконечности, как это бывает в прайсах. Искать надо по слову Яблоко\Груша, как это можно реализовать?

      Заранее, огромное Вам спасибо.
      С уважением, Джон.

      Добрый день. Как можно в одной таблице совместить возможности функций ВПР и VLOOKUP3
      Т.е. есть исходные данные которые содержат в разных строках одинаковый артикул.
      Если использовать ВПР, то она ищет только первое значение, если VLOOKUP3, то массив, но только для одного артикула. А мне нужно в одну таблицу “собрать” данные соответствующие и одиночным артикулам и если их несколько.
      Т.е. по сути сводная таблица, но с простой, двумерной структурой.

      Составил пример в экселе, не могу его прикрепить.

      Файл1 Закупка

      № п/п Наименование Страна Артикул Дата покупки Объем
      партии, кг
      Цена Стоимость партии
      9 Капуста Россия 0004/01 01.12.2015 5 12 60
      8 Ананас Эквадор 0001/02 02.12.2015 10 120 1200
      14 Киви Тунис 0005/01 03.12.2015 13 60 780
      11 Грейпфрут Марокко 0003/01 04.12.2015 14 45 630
      17 Нектарин Тайланд 0007/01 05.12.2015 14 40 560
      5 Киви Тунис 0005/01 01.01.2016 23 60 1380
      10 Манго Тайланд 0006/01 01.01.2016 10 80 800
      13 Киви Тунис 0005/01 01.01.2016 15 80 1200
      16 Абрикос Армения 0001/01 01.01.2016 26 40 1040
      3 Капуста Россия 0004/01 01.02.2016 35 12 420
      6 Капуста Россия 0004/01 02.02.2016 36 12 432
      2 Груши Россия 0003/02 03.02.2016 40 38 1520
      15 Персик Армения 0008/01 04.02.2016 42 45 1890
      1 Яблоки Россия 0009/01 01.03.2016 60 23 1380
      4 Мандарины Марокко 0006/02 01.03.2016 45 45 2025
      7 Киви Тунис 0005/01 01.03.2016 60 60 3600
      12 Банан Алжир 0002/01 01.03.2016 48 22 1056

      Файл2 Продажи

      № п/п Артикул Дата продажи Объем
      партии, кг
      Цена Стоимость партии
      1 0009/01 10.03.2016 60 33 1980
      2 0003/02 10.02.2016 40 48 1920
      3 0004/01 03.02.2016 35 22 770
      4 0006/02 10.03.2016 45 55 2475
      5 0005/01 20.01.2016 23 70 1610
      6 0004/01 03.02.2016 36 30 1080
      7 0005/01 10.03.2016 40 70 2800
      8 0001/02 10.01.2016 10 130 1300
      9 0005/01 11.03.2016 15 80 1200
      10 0004/01 15.01.2016 5 35 175
      11 0005/01 12.03.2016 5 90 450
      12 0006/01 05.01.2016 10 90 900
      13 0003/01 16.01.2016 14 55 770
      14 0002/01 10.03.2016 48 32 1536
      15 0005/01 05.01.2016 15 90 1350
      16 0005/01 15.01.2016 10 70 700
      17 0005/01 15.01.2016 3 80 240
      18 0008/01 15.03.2016 42 55 2310
      19 0001/01 15.01.2016 20 50 1000
      20 0007/01 17.01.2016 14 50 700
      21 0001/01 15.01.2016 6 60 360

      Что необходимо

      Сводная таблица по продажам
      Наименование Страна Артикул Продано Средняя цена Сумма
      Абрикос Армения 0001/01 20 50 1000
      Ананас Эквадор 0001/02 10 130 1300
      Банан Алжир 0002/01 48 32 1536
      Грейпфрут Марокко 0003/01 14 55 770
      Груши Россия 0003/02 40 48 1920
      Капуста Россия 0004/01 76 29 2204
      Киви Тунис 0005/01 111 78.57 8721.27
      Манго Тайланд 0006/01 10 90 900
      Мандарины Марокко 0006/02 45 55 2475
      Нектарин Тайланд 0007/01 14 50 700
      Персик Армения 0008/01 42 55 2310
      Яблоки Россия 0009/01 60 33 1980

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

      День добрый! Подскажите пожалуйста, как сделать ВПР в такой ситуации (я уже думала о других формулах, как поискпоз, просмотр, но что-то как-то не выходит). Во вкладке Исходник 10 столбцов : Фио, группа, город, ДАТА. 9-й столбец Показатель, 10-й – время. Данные идут вертикально по Датам. Необходимо перенести данные Во вкладку Показатель, где первые 3 столбца Фио, группа, город, а после идут 1,2,3 . , то есть даты, они что во вкладке Исходник и Показатель указаны как числа (общий формат), так как сам файл идет за определенный месяц. Как в формуле привязать эти числа, чтоб переносились данные во вкладку Показатель по определенному Фио и дате.
      Вкладка Исходник

      ФИО Группа Город Дата Показатель Время
      Петров Иван Смирнов Саратов 3 196 0:01:54
      Петров Иван Смирнов Саратов 4 176 0:02:54
      Иванова Катя Кузнецов Омск 5 171 0:03:10

      Вкладка Показатель

      ФИО Группа Город 1 2 3 4 5 6
      Петров Иван Смирнов Саратов
      Смирнова Юлия Попов Самара
      Мартынова Екатерина Попов Самара
      Иванова Катя Кузнецов Омск

      Заранее спасибо.

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

      Пример:
      Имеется: “лист-1” с названиями моделей и их техническим описанием.
      Необходимо: на “лист-2” указывать актуальные в данный момент модели (в определенных ячейках, позиции ячеек определены для возможности последующей печати), и чтобы рядом с ними автоматически выводились таблицы с их описаниями с “лист-1”.
      Далее лист с таблицами выбранных моделей отправляется на печать.

      Подскажите как можно заставить ВПР работать со списком “Наименование”(из вашего примера), диапазон которого меняется, может быть короче или длиннее. Нужно составить универсальный шаблон, где будут прописаны все формулы, но расчёты будут основываться на выборке с помощью ВПР по списку “Наименование”, но длинна списка может меняться бессистемно в широком диапазоне 10-10 000.

      Впр excel — расшифровка и пояснение

      Объяснение для чайников начать нужно с расшифровки названия функции. ВПР — всего лишь сокращение от Вертикального Поиска. На английском выглядит название с аббревиатурой так: VLOOKUP – от Vertical LOOKUP. По названию уже можно понять, что функция считывает информацию в столбцах по вертикали, начиная слева направо, и является идентификатором этой информации.
      ВПР в эксель — это один из самых быстрых и простых способов найти в таблице интересующие данные. Например, артикул определенного товара в списке. Или его цену. Простыми словами ВПР — это самая обычная команда, которую можно задать с помощью составленной определенным образом формулы — и получить искомый ответ.

      Искомый ответ

      Формула состоит из нескольких элементов. Если вы знакомы с функциями эксель, вам известно, что любая команда начинается со значка «=». Далее будет следовать название самой функции — то есть, ВПР. После этого в круглых скобках задаются аргументы, х может быть от двух и до пяти-шести. Чем больше элементов поиска задается, тем более направленным он будет. Обычно в формуле указывается название элемента, который нужно найти в таблице, и его параметры — это минимум.

      Таблица с данными

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

      Точный ответ

      Что еще можно сделать при помощи ВПР в эксель:

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

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

      Функция ВПР (VLOOKUP) или тайна четвёртого параметра

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

      ВПР — это аббревиатура от Вертикального ПРосмотра. Аналогично и VLOOKUP — Vertical LOOKUP.

      Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку).

      Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP).

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

      Синтаксис

      • Функция ВПР имеет 4 параметра:
      • =ВПР( ; ; [;] ) , тут:
      • — искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев)
      • — ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра
      • — номер столбца в диапазоне, из которого будет возвращено значение

      — это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона . В случае, если массив отсортирован, то мы указываем значение ИСТИНА (TRUE) или 1, в противном случае ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, то он по умолчанию принимается равным 1.

      Держу пари, что многие из тех, кто знают функцию ВПР, как облупленную, прочтя описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0), либо же о диапазонном просмотре (ИСТИНА или 1).

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

      Итак, как же конкретно работает формула ВПР:

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

      Как сделать впр в excel по частичному совпадению?

      Схемы работы формул

      ВПР тип I

      ВПР тип II

      Следствия для формул вида I:

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

      Следствия для формул вида II:

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

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

      Вы добрались до кульминационного места статьи.

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

      • мне нужен более мощный компьютер
      • мне нужна более быстрая формула, например, многие знают про ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая, якобы, быстрее на жалкие 5-10%.

      И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Данный факт я проверил вполне надёжно.

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

      Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще.

      Тоже самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР (LOOKUP), которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus 1-2-3.

      Недостатки формулы

      Недостатки ВПР очевидны: во-первых, она ищет только в первом столбце, указанного массива, и, во-вторых, — только справа от данного столбца.

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

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

      Реальное применение формулы

      Диапазонный поиск

      Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа.

      Как сделать впр в excel по частичному совпадению?

      Поиск текстовых строк

      Безусловно ВПР ищет не только числа, но и текст. При этом надо принимать во внимание, что регистр символов формула не различает. Если использовать символы подстановки, то можно организовать нечёткий поиск. Есть 2 символа подстановки: «?» — заменяющий любой один символ в текстовой строке, и «*» — заменяющая любое количество любых символов.

      Как сделать впр в excel по частичному совпадению?

      Борьба с пробелами

      Часто поднимается вопрос, как решить проблему лишних пробелов при поиске. Если справочную таблицу ещё можно вычистить от них, то первый параметр формулы ВПР не всегда зависит от вас. Поэтому, если риск засорения ячеек лишними пробелами присутствует, то можно применять для очистки функции СЖПРОБЕЛЫ (TRIM).

      Как сделать впр в excel по частичному совпадению?

      Разный формат данных

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

      =ВПР(—D7; Продукты!$A$2:$C$5; 3; 0) — если A7 содержит текст, а таблица числа

      Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:

      • Двойное отрицание —D7
      • Умножение на единицу D7*1
      • Сложение с нулём D7+0
      • Возведение в первую степень D7^1

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

      Как подавить выдачу #Н/Д

      1. Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).

      Массив

      Часто забывают ссылку массива сделать абсолютной и при протягивания массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5.

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

      Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона.

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

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

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

      Ну и на грани гениальности оформить массив в виде умной таблицы.

      Использование функции СТОЛБЕЦ для указания колонки извлечения

      Если таблица, в которую вы извлекаете данные при помощи ВПР имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов. При этом все ВПР формулы будут одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого параметра координата столбца абсолютная.

      Как сделать впр в excel по частичному совпадению?

      Создание составного ключа через &»|»&

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

      Как сделать впр в excel по частичному совпадению?

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

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

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

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

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

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

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

      1. – Дата сдачи выручки в кассу.
      2. – Фамилия торгового представителя.

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

      Результат поиска в таблице по двум условиям:

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

      Найдена сумма выручки конкретного торгового представителя на конкретную дату.

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

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

      Во втором аргументе находится виртуальная таблица создана в результате массивного вычисления логической функцией =ЕСЛИ(). Каждая фамилия в диапазоне ячеек B6:B12 сравнивается со значением в ячейке C2.

      Таким образом в памяти создается условный массив данных с элементами значений ИСТИНА и ЛОЖЬ.

      Потом благодаря формуле, в памяти программы каждый истинный элемент заменяется на 3-х элементный набор данных:

      1. элемент – Дата.
      2. элемент – Фамилия.
      3. элемент – Выручка.

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

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

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

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

Ссылка на основную публикацию
Adblock
detector