Впр бухгалтерия что это

Функция ВПР в Excel для чайников — все по делу, без воды!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

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

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

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

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

Формула выше состоит из четырех аргументов:

Ниже, наглядно изображено как работает по примеру наша функция.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример 3. Используем выпадающий список при двух факторном поиске

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

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

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

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

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

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

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

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

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

Чтобы сделать выпадающий список, нужно:

Теперь у вас появится раскрывающийся список в ячейке “G4”. Аналогично, вы можете создать его в “H3” для предметов.

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

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

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

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

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

Вот пример трехстороннего поиска:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=ВПР(“яяя”;$A$1:$A$14;1;ИСТИНА) – русская версия

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=VLOOKUP($F3&”|”&G$2,$C$2:$D$19,2,0) – английская версия

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

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

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

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

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

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

Для того, чтобы заменить сообщение ошибки на какое-то более формальное значение нам поможет функция ЕСЛИОШИБКА(IFERROR) :

=IFERROR(VLOOKUP(D2,$A$2:$B$7,2,0),”Нет данных”) – английская версия

=ЕСЛИОШИБКА(ВПР(D2;$A$2:$B$7;2;0);”Нет данных”) – русская версия

Функция ЕСЛИОШИБКА(IFERROR) проверяет, если в результате вычислений по формуле выдается ошибка, то она выдает, то значение, которое вы указали в функции ЕСЛИОШИБКА(IFERROR), например “Нет данных”.

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

Источник

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

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

Функция ВПР в Экселе: пошаговая инструкция

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

Если перечень товаров в обеих таблицах совпадает, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится.

Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.

Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.

Функция ВПР в Эксель легко справится с задачей.

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

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

Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.

Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».

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

Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов. Это не важно, Excel все сосчитает.

Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.

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

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

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

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

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

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

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

4. Последний параметр формулы, который 0 (нуль), ставить нужно обязательно. Иначе формула может криво работать.

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

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

Это позволит удалить сразу все внешние ссылки.

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

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

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

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

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

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

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

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

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

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

Первый случай, когда критерии в первой таблице сохранены как числа, а критерии во второй таблице – как текст.

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

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

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

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

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

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

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

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

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

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

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

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

Здорово экономит время.

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

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

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

Проще понять на примере. По результатам выполнения плана продаж каждому торговому агенту нужно выдать заслуженную премию (в процентах от оклада). Если план выполнен менее, чем на 100%, премия не положена, если план выполнен от 100% до 110% (110% не входит) – премия 20%, от 110% до 120% (120% не входит) – 40%, 120% и более – премия 60%. Данные находятся в следующем виде.

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

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

Джеки Чан выполнил план на 124%. Значит ВПР в качестве критерия ищет во второй таблице ближайшее меньшее значение. Это 120%. Затем отсчитывает 2 столбца и возвращает премию 60%. Брюс Ли план не выполнил, поэтому его ближайший меньший критерий – 0%.

Предлагаю посмотреть видеоурок о работе ВПР из курса «Основные функции Excel».

Источник