smolenka-cafe.ru


Функция excel впр пошагово

Добрый день, многоуважаемые читатели блога statanaliz.info.

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

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

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

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

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

Данные о количестве

Во второй – данные о ценах:

Данные о ценах

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

Несоответствие позиций по ценам и количеству

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

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

Вот так.

Расположение соответствующих позиций

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

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

Введение функции ВПР

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

ВПР в Мастере функций

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

Диалоговое окно ВПР

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

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

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

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

Все, поля заполнены и выглядят примерно так.

Заполненные поля для ВПР

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

Копируем ВПР вниз

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

Формулу ВПР можно прописать непосредственно в ячейке, как я обычно и делаю. Это позволяет увеличить скорость работы в Excel. Как ни странно, написать формулу вручную получается быстрее, чем заполнить кучу полей в Мастере функций. Тут все почти тоже самое, только между параметрами нужно ставить знак точки с запятой «;». Так выглядит та же формула, написанная пальчиками.

ВПР в ячейке

Название «впр» можно написать маленькими буквами, реестр значения не имеет.

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

Особенности использования функции ВПР

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

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

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

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

4. Если в качестве критерия используются числовые значения (числовые коды, артикулы и проч.), то формула ВПР чувствительна к формату ячейки! Если в одной таблице критерий будет в числовом формате, а в другой в текстовом, то даже при полном совпадении значений функция выдаст ошибку #Н/Д. Данное обстоятельство многих ставит в тупик и заставляет думать, что ВПР не работает. На самом деле пугаться не нужно. Часто достаточно проверить совпадение формата полей с критерием и, если надо, сделать их одинаковыми. Либо опять же использовать функцию СУММЕСЛИ – ей формат по барабану.

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

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

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

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

Разорвать связи

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

Диалоговое окно разрыва внешних связей

Это позволит удалить сразу все внешние ссылки. Полезная функция, возьмите на заметку.

Чуть не забыл. Есть еще одна похожая функция – ГПР. Отличие в том, что ГПР «просматривает» список данных по горизонтали. Сам никогда не пользовался, но чисто теоретически все то же самое.

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

На сегодня все. Всех благ и до новых встреч на блоге statanaliz.info.


Источник: http://statanaliz.info/excel/formuly/51-funktsiya-excel-vpr-vlookup-i-gpr-hlookup



Функция ВПР для Excel Служба поддержки Office - Служба поддержки Office - Как разводить в домашних условиях индюков



Функция excel впр пошагово Функция excel впр пошагово Функция excel впр пошагово Функция excel впр пошагово Функция excel впр пошагово Функция excel впр пошагово Функция excel впр пошагово Функция excel впр пошагово Функция excel впр пошагово