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