Главная > Программы > Excel > Функция ВПР
 
 
 
 

Функция ВПР

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

Новый прайс
Рис. 1. Лист «Новый прайс»

Старый прайс
Рис. 2. Лист «Старый прайс»

Все сравнения и операции будем вести на листа «Новый прайс». Соответственно в ячейку D1 записываем название столбца скажем «Старая стоимость» в ячейку D2 пишем простую формулу =ВПР(A2;'Старый прайс'!A:C;3;ЛОЖЬ), это означает что нужно взять ключевое значение из ячейки D2 и посмотреть его на листе «Старый прайс» в столбце A (см. значение 'Старый прайс'!A:C), потом берем значение из 3 столбца (старую цену) и подставляем его значение в D2. В самом конце функции ВПР стоит параметр ЛОЖЬ - он отменяет интервальный просмотр значения ключа (это не наш случай).
Для английской версии Excel в ячейку D2 необходимо будет записать =VLOOKUP(A2;'Старый прайс'!A:C;3;FALSE)
Далее следует выполнить «протяжку» ячеек до D7. Как видите в столбце D получились следующие значения:

Значения получаемые с помощью функции ВПР
Рис. 3. Значения получаемые с помощью функции ВПР

Последняя ячейка D7 содержит информацию об ошибке #N/A. Такая ошибка говорит о том, что артикул 664-313 не содержится в старом прайсе, т.е. запчасть новая.
Далее можно уже сравнивать стоимости, находить численную и процентную разницу. Кроме того с помощью этой функции по поиску ключевого значения можно делать практически потрясающие вещи, но следует помнить несколько тонкостей:
При большом обеме анализируемых данных скорость работы Excel замедляется, а компьютер начинает явно «тормозить»;

  • Максимальный объем анализируемых данных (ячеек Excel) около 65 тысяч ячеек, для больших объемов и большего сравнения нужна Microsoft Access или иные системы по управлению базами данных - большие объемы информации - это их прерогатива.
  • Советуем также прочитать совет о переводе и аналогах функций в русской и английской версии Excel.

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

Оригинал

 
Почтовая форма Карта сайта Главная
FoxPro 2.6, сайтостроение – Запорожская социальная сеть
maxim.zp.ua - Запорожская социальная сеть
bigmir)net TOP 100 Яндекс.Метрика