Спасаемся от рутинной работы посредством функции впр в excel

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

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

Вот небольшая таблица для наглядности.

11

Допустим, перед нами стоит задача определить, по какой стоимости был завезен гофрированный картон от производителя «ОАО «Восток». В этом случае нам требуется два условия:

1. Материал.
2. Производитель.

Но это еще не все, потому что каждый производитель завозит сразу несколько товаров. Как можно выкрутиться в этой ситуации? А вот, как:

1. К таблице присоединяется крайний левый столбец, чтобы поставщики и материалы были в одной группе.
2. Критерии также нужно объединить.
3. Курсор устанавливается в требуемом месте, и в скобках указываются аргументы функции (или же через соответствующее диалоговое окно). =ВПР(I6;$A$2:$D$15;4;ЛОЖЬ). После этого Excel определит необходимую стоимость.

Использование функций ИНДЕКС и ПОИСКПОЗ

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

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

=ИНДЕКС(D2:D13;ПОИСКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0))

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

Сначала рассмотрим функцию ПОИСКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0). В данном случае последовательно сравнивается значение ячейки G1 с каждым значением ячеек диапазона B2:B13 и возвращается ИСТИНА, если значения совпадают и ЛОЖЬ, если нет. Такое же сравнение производится со значением ячейки G2 и диапазоном C2:C13. Далее мы сравниваем оба эти массива, состоящих из ИСТИНА и ЛОЖЬ. Комбинация ИСТИНА * ИСТИНА дает нам результат 1 (ИСТИНА). Давайте посмотрим на картинку ниже, которая поможет объяснить принцип работы более наглядно.

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

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

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

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

Взглянем, как работает функция ВПР на конкретном примере.

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

1. Кликаем по верхней ячейке (C3) в столбце «Цена» в первой таблице. Затем, жмем на значок «Вставить функцию», который расположен перед строкой формул.
2. В открывшемся окне мастера функций выбираем категорию «Ссылки и массивы». Затем, из представленного набора функций выбираем «ВПР». Жмем на кнопку «OK».
3. После этого открывается окно, в которое нужно вставить аргументы функции. Жмем на кнопку, расположенную справа от поля ввода данных, чтобы приступить к выбору аргумента искомого значения.
4. Так как у нас искомое значение для ячейки C3, это «Картофель», то и выделяем соответствующее значение. Возвращаемся к окну аргументов функции.
5. Точно таким же образом кликаем по значку справа от поля ввода данных, для выбора таблицы, откуда будут подтягиваться значения.
6. Выделяем всю область второй таблицы, где будет производиться поиск значений, кроме шапки. Опять возвращаемся к окну аргументов функции.
7. Для того, чтобы выбранные значения сделать из относительных абсолютными, а это нам нужно, чтобы значения не сдвинулись при последующем изменении таблицы, просто выделяем ссылку в поле «Таблица», и жмем на функциональную клавишу F4. После этого к ссылке добавляются знаки доллара и она превращается в абсолютную.
8. В следующей графе «Номер столбца» нам нужно указать номер того столбца, откуда будем выводить значения. Этот столбец располагается в выделенной выше области таблицы. Так как таблица состоит из двух столбцов, а столбец с ценами является вторым, то ставим номер «2».
9. В последней графе «Интервальный просмотр» нам нужно указать значение «0» (ЛОЖЬ) или «1» (ИСТИНА). В первом случае, будут выводиться только точные совпадения, а во втором — наиболее приближенные. Так как наименование продуктов – это текстовые данные, то они не могут быть приближенными, в отличие от числовых данных, поэтому нам нужно поставить значение «0». Далее, жмем на кнопку «OK».

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

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

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

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

Формула ВПР в Excel имеет 4 аргумента:

• искомое значение в формате текстовой строки, ссылки на какую-либо ячейку, значения – это параметр в первом столбце слева;
• таблица – диапазон для поиска в виде именного диапазона или ссылки на определенный диапазон (это бывает и вся таблица, и отдельные столбцы);
• номер столбца – отсюда берется искомая величина, указывается номер столбца из диапазона;
• интервальный просмотр – 0 или 1.

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

Синтаксис

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

Элемент – может быть числовым (адрес ячейки) или текстовым («текст»).

Адрес таблицы – диапазон ячеек, где примерно находится значение.

Номер столбца – принимает целое число из диапазона от 1 до n, из него будет извлечен результат.

Интервальный просмотр – приблизительное (ближайшее) соответствие критерию обозначается как 1 (истина), а точное соответствие – 0 (ложь). Данный логический аргумент указывать необязательно, если таблица отсортирована от минимального к максимальному значению. Если таблица не отсортирована и аргумент опущен, это равносильно истине.

В русифицированной версии Excel аргументы вводятся через знак «;», в англоязычной – через запятую.

Синтаксис

Синтаксис функции ВПР
ВПР (заданное значение, таблица, номер столбца, )

Аргументы: 

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

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

Таблица – область поиска, содержащая и ключевой столбец; 

Номер столбца указывает, какие именно данные следует извлечь из строки. 

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

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

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

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

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

Как видите, размер премии зависит от диапазона по системе премирования, куда попадает показатель эффективности продаж конкретного сотрудника. Мы видим, что при выполнении плана менее чем на 100 % премия не начисляется, а при 107 % (выше 100 %, но менее 110 %) работник получает премию в размере 10 %. Описанные показатели премии нам необходимо ввести с помощью функции ВПР в графу «Премия» первой таблицы, только на этот раз критерий будет находиться в определенном диапазоне.

Для корректной работы убедитесь, что границы диапазонов во второй таблице в крайнем левом столбце расположены в порядке возрастания сверху вниз (шаг 1). Формула берет выбранный нами критерий и выполняет поиск в первом столбце второй таблицы (шаг 2) и перебирает все значения сверху вниз (шаг 3). Как только функция находит первое значение, превышающее критерий из первой таблицы, она «откатывается» (шаг 4) и считывает значение, соответствующее найденному критерию (шаг 5). Другими словами, при неточном поиске функция ВПР ищет меньшее значение для искомого критерия:

Итак, наша функция будет выглядеть так:

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

Например, у сотрудника Ольги премия 0%, так как она выполнила 76% продаж, то есть перевыполнила план на 0%. А сотрудница Наталья сдала на 21% сверх нормы и получила вознаграждение в размере 20%, что мы и увидим, если сравним данные из двух таблиц самостоятельно.

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

Что такое ВПР в Excel?

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

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

Как перенести данные из одной таблицы в другую? (Функция ВПР-VLOOKUP)

Функция ВПР (VLOOKUP) позволяет подтянуть данные из таблицы по названию показателя, например:

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

У функции ВПР есть следующие аргументы:

• Искомое значение – то, что мы будем искать в основной таблице (название книги в данном случае). В Основной таблице эти значения должны быть в первом столбце;
• Таблица – ссылка на основную таблицу. Фиксируем долларами (см. пример), так как таблица одна и ссылки на нее не должны меняться при смещении самой формулы;
• Номер столбца – номер столбца в основной таблице, данные из которого мы хотим подтянуть. В данном случае 3 – так как тематика находится в третьем столбце основной таблицы;
• Интервальный просмотр – если вы ищете текстовые значения, указывайте 0 (ноль). Тогда функция будет искать точные значения. 1 в этом аргументе указывается, если вы ищете ближайшее числовое значение к искомому. Но для корректной работы ВПР в этом случае сама таблица должна быть отсортирована по возрастанию.

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

Почему не работает функция ВПР

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

• Не указали символ доллара в диапазоне (аргумент 2).В этом случае первая формула, введённая вручную, будет верной, а скопированные в другие ячейки — нет, поскольку диапазон поиска смещается в соответствии с правилами Эксель при копировании формул.
• Неверно указан диапазон поиска.Опять же это ошибка в аргументе 2. Диапазон ячеек нужно указывать так, чтобы столбец, в котором ищется совпадение, был первым в выделенном диапазоне. Иначе конечно ничего найдено не будет.
• Неверно указан номер столбца (аргумент 3).Например, можно указать столбец с таким номером, которого нет в выделенном диапазоне. Или номер столбца указан так, что в этом столбце находятся не те данные (напоминаем, что столбцы нумеруются с единицы).

Если у Вас не работает функция ВПР, то значит скорее всего Вы неверно задали её параметры. Это бывает чаще всего, а не «глючит Excel».

Работаем с выпадающим списком

Предположим, что у нас определенные данные (например, «Материалы») показываются в раскрывающимся списке. Наша задача, чтобы при этом было доступно видеть еще и стоимость.

Для этого предпримем следующие шаги:

Нажимаем мышкой на ячейку «E8» и во вкладке «Данные» выбираем «Проверку данных»

Нажмите на «Проверка данных»

Далее во вкладке «Параметры» выберите «Список» и нажмите на «ОК»

Выберите «Список» и нажмите на «ОК»

Теперь в выпадающем списке мы видим пункт «Цена»

Получаем пункт «Цена»

• Далее нам нужно, чтобы напротив пункта «Цена» выставлялась стоимость материала
• Нажмите на ячейку «E9», в «Мастере функций» выберите «ВПР». Напротив пункта «Таблица» должны быть выставлены следующе показатели.

В «Таблица» должны быть следующе показатели

Нажмите на «Ок» и получите результат

Получаем результаты

Почему не работает ВПР в Excel

То, что ВПР не работает, можно понять по появлению ошибки #Н/Д.

Не работает ВПР — значит, что функция не находит значение. Причины:

Ошибка в написании критерия. Часто в текст закрадываются лишние пробелы или латинские буквы вместо кириллицы, и тогда появится ошибка.

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

Съехал или “не дотянут” диапазон таблицы для поиска.

В данном случае значение Январь не попало в диапазон поиска, поэтому появилась ошибка #Н/Д.

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

Перенос данных в другой файл

Аналогично можно переносить данные из одного файла в другой. Данная книга в нашем примере так и называется EXCEL. Создадим еще одну и назовем ее ПРИМЕР.

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

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

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

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

Ошибки при работе с функцией ВПР в Excel

Не закрепление диапазона в таблице

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

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

В нашем примере если мы забудем зафиксировать диапазон таблицы A1:E11, то при протягивании формулы он сначала превратится в A2:E12, затем в A3:E13 и т.д.

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

Поиск не по первому столбцу

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

В нашем случае, к примеру, если мы хотим искать по наименованию (которые находятся в столбце B), то в качестве таблицы нужно выбирать диапазон B1:E11 (не A1:E11), так как именно по столбцу B будет делаться поиск и именно он является первым столбцом в таблице B1:E11.

Пропуск интервального просмотра

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

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

В нашем случае пропуск параметра приводит к тому, что ВПР находит совершенно не то, что нужно, как раз из-за приближенного поиска:

Смена номера столбца

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

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

Идем дальше.

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

Функция ВПР в Excel не может смотреть налево. Она всегда ищет значение в крайнем левом столбце диапазона, заданного аргументом table_array (таблица).
В функции ВПР все значения используются без учета регистра, то есть маленькие и большие буквы эквивалентны.
Если искомое значение меньше минимального значения в первом столбце просматриваемого диапазона, функция ВПР сообщит об ошибке #N/A (#Н/Д).
Если 3-й аргумент col_index_num (номер_столбца) меньше 1, функция ВПР сообщит об ошибке #VALUE! (#ЗНАЧ!). Если же он больше количества столбцов в диапазоне table_array (таблица), функция сообщит об ошибке #REF! (#ССЫЛКА!).
Используйте абсолютные ссылки на ячейки в аргументе table_array (таблица), чтобы при копировании формулы сохранялся правильный диапазон поиска

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

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

ВПР имеет два режима поиска.

Четвертый параметр «интервальный просмотр» позволяет задать режим сравнения, в котором нужно работать – точный или приблизительный. Это логический параметр, то есть для него возможно 2 варианта:

• или ЛОЖЬ – интервальный просмотр выключен (ищем точное соответствие)
• 1 или ИСТИНА – интервальный просмотр включен (достаточно приблизительного)

Если этот параметр – ЛОЖЬ, то ищется точное равенство в первом столбце с аргументом «искомое». Если же такого нет, воз­вращается ошибка #Н/Д. Точный режим нужен, когда вы хотите найти информацию, основанную на каком-то уникальном ключе. Скажем, информацию о товаре – по коду товара, или данные фильма по его названию, человека – по фамилии.

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

Обратите внимание, что при этом предполагается, что наш перечень отсортирован по столбцу поиска

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

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

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

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

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

Конструкция с функцией ЕСЛИОШИБКА

Наряду с функцией ВПР часто используется функция ЕСЛИОШИБКА, которая «замалчивает» ошибки #Н/Д и вместо этого возвращает некоторое значение. Обычно это 0 или пусто. 

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

Разные форматы критерия в таблицах

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

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

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

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

Автоматически изменить формат критерия во второй таблице нельзя, потому что ссылка ведет на полный диапазон. Придется мешать ссылку на критерий в первой таблице. Для этого нужно добавить функцию ТЕКСТ, которая изменит числовой формат на текстовый. Синтаксис функции ТЕКСТ требует указания формата. Просто установите формат #. Ниже изображение с готовой формулой.

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

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

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

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

Отсутствующие критерии по-прежнему вызывают ошибку #Н/Д. В этом случае всю формулу можно «обернуть» обратно в ЕСЛИОШИБКА.

Функция СЖПРОБЕЛЫ для чистки текстового критерия

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

Подсчет номера столбца в большой таблице

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

Отличная экономия времени.

Автоматический перенос данных из одной таблицы в другую в Excel

Приведу три способа Автоматического переноса данных с одного листа программы «Эксель» в другой.

Рассмотрим, как соединить две таблицы по шагам.

Второй шаг.

Копировать информацию сочетанием клавиш ctrl+C или вызвав контекстное меню правой кнопкой мыши и кликнув по пункту меню «Копировать»

Четвертый шаг.

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

После вставки связи следует отформатировать вид ячеек – привести их к надлежащему виду.

При использовании данного метода роль второй таблицы («реципиента») играет сама сводная таблица.

При клике правой кнопкой мыши по сводной таблице и нажатии на пункт «Обновить» сводная таблица автоматически перенесет все данные из связанного массива информации («таблицы донора»).

О том, как в «Эксель» создавать сводные таблицы подробно написано в статье:

Как делать сводные таблицы в программе «Excel» и для чего они нужны.

Правда нужно отметить, что этот способ подходит только пользователям   Excel 2016 и пользователям Excel 2013и выше с установленной надстройкой «Power Query».

Смысл способа в следующем:

Необходимо открыть вкладку «Power Query». В разделе «Данные Excel» нажимаем кнопку (пиктограмму) «Из таблицы».

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

После выбора области данных появится окно настройки вида новой таблицы. В этом окне Вы можете настроить последовательность вывода столбцов и удалить ненужные столбцы.

После настройки вида таблицы нажмите кнопку «Закрыть и загрузить»

Обновление полученной таблицы происходит кликом правой кнопки мыши по названию нужного запроса в правой части листа (список «Запросы книги»). После клика правой кнопкой мыши в выпадающем контекстном меню следует нажать на пункт «Обновить»

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

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

ВПР(искомое_значение; таблица; номер_столбца; )

Смысл этих аргументов следующий:

Искомое_значениеОбязательный параметр. Это то самое значение, которое должна искать функция в первом(!) столбце таблицы, заданной аргументом 2 (см. ниже). Здесь обычно указывается адрес ячейки, в которой находится нужное нам значение (в примере, рассмотренном ниже, это адрес ячейки в одной из строк колонки «Должность»). Указывать простой текст или числовую константу не имеет смысла, так как функция ВПР используется для обработки больших массивов данных. Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента таблица, функция ВПР возвращает значение ошибки #Н/Д.

ТаблицаТоже обязательный параметр. Это та таблица, данные из которой мы собираемся добавить. Таблица указывается как диапазон ячеек (в примере, рассмотренном ниже, это диапазон А10:B12)

Обратите внимание, что диапазон ячеек должен быть указан таким образом, чтобы в первом(!) его столбце находились данные, которые мы ищем (в нашем примере это столбец «Должность»). Значения в первом столбце могут быть текстовыми, числовыми или логическими

Текстовые значения в нижнем и верхнем регистре считаются эквивалентными.

Номер_столбцаУказывать обязательно. Это порядковый номер столбца, данные из которого нужно вернуть функции ВПР в качестве ответа, если в первом столбце второй таблицы будет найдено искомое значение (аргумент 1). Столбцы нумеруются начиная с 1 (единица), так что в нашем примере (см. далее) это столбец 2 («Зарплата»). Итого: если номер_столбца = 1, то возвращается значение из первого столбца таблицы; если номер_столбца = 2 — значение из второго столбца таблицы и т. д.

интервальный_просмотрВарианты значений: 0 или 1. Если указать 0, то функция будет искать точное совпадение для параметра 1 (искомое значение); если указать 1, то поиск будет производиться приближённо. Чаще всего используется именно 0, поскольку требуется найти именно то, что у нас в аналогичном столбце первой таблицы (в нашем примере это должность сотрудника).

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

В простом примере ниже эти аргументы рассмотрены на практике.

ВПР пригодится, чтобы объединять данные из разных таблиц.

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

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

Обратите также внимание, что даже точный поиск можно сделать немного приблизительным, используя знаки подстановки * и ?. Напомню, что знак вопроса заменяет собой любой один символ, а звездочка – любое количество знаков (в том числе и ноль)

Таким вот образом мы обнаружили в перечне товаров персики, записав в условии поиска “*”&”персики”&”*” или же “*”&D2&”*”.