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

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

Как пользоваться формулой ВПР в Excel

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

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

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

1. Искомое значение.
2. Таблица.
3. Номер столбца.
4. Интервальный просмотр (не обязательный для заполнения) – определяет точное (ЛОЖЬ) или приблизительное (ИСТИНА) совпадение искомых и найденных значений.

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



Последний аргумент для каждой функции ВПР в формуле данного примера содержит значение 0 (или ЛОЖЬ). Как уже упоминалось выше если данный 4-й аргумент будет содержать значение ЛОЖЬ, тогда функция ВПР будет возвращать результат только при точном совпадении с искомым значением. В противном случае будет возвращена ошибка с кодом #Н/Д!

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

и страховки:

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

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

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

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

Итоговая сумма всех вычетов в ячейке G20 вычисляется формулой: =СУММ(G17:G19).

Выплата по факту в ячейке G14 рассчитывается путем вычитания от общей выплаты минус вычеты: =C17-G20.

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

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