Как закрепить область в excel при впр

Функция ВПР в Excel позволяет данные из одной таблицы переставить в соответствующие ячейки второй. Ее английское наименование – VLOOKUP.

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

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

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

Стоимость материалов – в прайс-листе. Это отдельная таблица.

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

Алгоритм действий:

1. Приведем первую таблицу в нужный нам вид. Добавим столбцы «Цена» и «Стоимость/Сумма». Установим денежный формат для новых ячеек.
2. Выделяем первую ячейку в столбце «Цена». В нашем примере – D2. Вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или нажав комбинацию горячих клавиш SHIFT+F3. В категории «Ссылки и массивы» находим функцию ВПР и жмем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и выбрать из выпадающего списка «Ссылки и массивы».



3. Откроется окно с аргументами функции. В поле «Искомое значение» — диапазон данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен найти во второй таблице.



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



5. Чтобы Excel ссылался непосредственно на эти данные, ссылку нужно зафиксировать. Выделяем значение поля «Таблица» и нажимаем F4. Появляется значок $.



6. В поле аргумента «Номер столбца» ставим цифру «2». Здесь находятся данные, которые нужно «подтянуть» в первую таблицу. «Интервальный просмотр» — ЛОЖЬ. Т.к. нам нужны точные, а не приблизительные значения.

Нажимаем ОК. А затем «размножаем» функцию по всему столбцу: цепляем мышью правый нижний угол и тянем вниз. Получаем необходимый результат.

Теперь найти стоимость материалов не составит труда: количество * цену.

Функция ВПР связала две таблицы. Если поменяется прайс, то и изменится стоимость поступивших на склад материалов (сегодня поступивших). Чтобы этого избежать, воспользуйтесь «Специальной вставкой».

1. Выделяем столбец со вставленными ценами.
2. Правая кнопка мыши – «Копировать».
3. Не снимая выделения, правая кнопка мыши – «Специальная вставка».
4. Поставить галочку напротив «Значения». ОК.

Формула в ячейках исчезнет. Останутся только значения.



Быстрое сравнение двух таблиц с помощью ВПР

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

1. В старом прайсе делаем столбец «Новая цена».



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

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

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

До сих пор мы предлагали для анализа только одно условие – наименование материала. На практике же нередко требуется сравнить несколько диапазонов с данными и выбрать значение по 2, 3-м и т.д. критериям.

Таблица для примера:

Предположим, нам нужно найти, по какой цене привезли гофрированный картон от ОАО «Восток». Нужно задать два условия для поиска по наименованию материала и по поставщику.

Дело осложняется тем, что от одного поставщика поступает несколько наименований.

1. Добавляем в таблицу крайний левый столбец (важно!), объединив «Поставщиков» и «Материалы».



2. Таким же образом объединяем искомые критерии запроса:



3. Теперь ставим курсор в нужном месте и задаем аргументы для функции: . Excel находит нужную цену.

Рассмотрим формулу детально:

1. Что ищем.
2. Где ищем.
3. Какие данные берем.

Функция ВПР и выпадающий список

Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.

Сначала сделаем раскрывающийся список:

1. Ставим курсор в ячейку Е8, где и будет этот список.
2. Заходим на вкладку «Данные». Меню «Проверка данных».



3. Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.



4. Когда нажмем ОК – сформируется выпадающий список.

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

1. Открываем «Мастер функций» и выбираем ВПР.
2. Первый аргумент – «Искомое значение» — ячейка с выпадающим списком. Таблица – диапазон с названиями материалов и ценами. Столбец, соответственно, 2. Функция приобрела следующий вид: .
3. Нажимаем ВВОД и наслаждаемся результатом.

Изменяем материал – меняется цена:

Скачать пример функции ВПР в Excel

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

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

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

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

Допустим, есть таблица с количеством проданного товара и таблица с ценами на эти товары

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

Как создать функцию ВПР в Excel

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

Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:

1. Выделить ячейку и вписать функцию.

2. Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».

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

Синтаксис функции ВПР выглядит так:

=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)

В нашем случае получится такая формула:

=ВПР(A2;$G$2:$H$11;2;0)

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

Сейчас разберемся что и куда писать.

Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.

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

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

=ВПР(A2;

Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании. 

В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.

=ВПР(A2;$G$2:$H$11

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

1. Формула сканирует таблицу по вертикали.

2. Находит в самом левом столбце совпадение с искомым значением.

3. Смотрит в столбец напротив, очередность которого мы указываем в этом аргументе.

4. Передает данные в ячейку с формулой.

В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.

=ВПР(A2;$G$2:$H$11;2

Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями. 

В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.

=ВПР(A2;$G$2:$H$11;2;0)

Автозаполнение

В конце протягиваем формулу вниз до конца, в результате чего происходит автозаполнение.

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

• В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.

• Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу. 

ВПР и приблизительный интервальный просмотр

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

Задача. В магазин привезли товар. Необходимо присвоить каждому товару размер партии, опираясь на его количество.

Товары такие же, как и в первом примере, но задача изменилась: нужно привязать формулу не к наименованию, а к количеству

Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.

Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:

Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение. 

В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает. 

Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает

Итоги

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

• Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).

• Функцию можно вписать вручную или в специальном окне (Shift + F3).

• Искомое значение – относительная ссылка, а таблица – абсолютная. 

• Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением. 

• Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.

• Порядок работы с функцией подходит для Гугл-таблиц.

Функция ВПР в программе Microsoft Excel

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

​Работа с обобщающей таблицей​ТЕКСТ​

Определение функции ВПР

​ попытается найти товар​ ссылки используем собственное​ указанной таблицы (прайс-листа)​ графе цена появлялась​ разницу.​ таблицу. «Интервальный просмотр»​ второй таблицы в​ меньше или равно​ больше искомого, то​ повторов (в этом​отсортирован в алфавитном​- это значение,​ в другую, с​F4​ соседней таблицы, которая​ подразумевает подтягивание в​для преобразования форматов​

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

​ с наименованием, которое​ имя «Прайс» данное​ двигаясь сверху-вниз и,​

​ соответствующая цифра. Ставим​До сих пор мы​ — ЛОЖЬ. Т.к.​ первую. И посредством​ заданному.​ она выводит значение,​ смысл артикула, однозначно​ порядке или по​ которое Вы пытаетесь​ помощью функции ВПР.​. После этого к​ представляет собой прайс-лист.​ неё значений из​ данных. Выглядеть это​ максимально похоже на​ ранее. Если вы​ найдя его, выдает​ курсор в ячейку​ предлагали для анализа​ нам нужны точные,​ обычного умножения мы​Если нужно найти по​ которое расположено на​ определяющего товар). В​ возрастанию. Это способ​

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

   

2. ​ Е9 (где должна​ только одно условие​​ а не приблизительные​​ найдем искомое.​ настоящему ближайшее к​​ строку выше его).​​ противном случае будет​​ используется в функции​​ с данными.​

   

3. ​ не так сложна,​ доллара и она​ (C3) в столбце​ таблиц очень много,​=ВПР(ТЕКСТ(B3);прайс;0)​ цену для этого​ то можно просто​ (23 руб.) Схематически​ будет появляться цена).​

   

4. ​ – наименование материала.​ значения.​Алгоритм действий:​​ искомому значению, то ВПР() тут​​Предположим, что нужно найти​ выведено самое верхнее​ по умолчанию, если​Искомое_значение ​

   

5. ​ как кажется на​ превращается в абсолютную.​«Цена»​ ручной перенос заберет​Функция не может найти​ наименования. В большинстве​

   

6. ​ выделить таблицу, но​ работу этой функции​Открываем «Мастер функций» и​ На практике же​Нажимаем ОК. А затем​Приведем первую таблицу в​

   

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

   

8. ​ рода задачи решены​​ цена равна или​​При решении таких задач​Ниже в статье рассмотрены​ текстом, но чаще​ в её применении​«Номер столбца»​ Затем, жмем на​ а если данные​ что в коде​ подстановка может сыграть​ потом клавишу​Для простоты дальнейшего использования​Первый аргумент – «Искомое​​ несколько диапазонов с​​ всему столбцу: цепляем​
9. ​ Добавим столбцы «Цена»​​ в разделе Ближайшее​​ наиболее близка к​ ключевой столбец лучше​​ популярные задачи, которые​​ всего ищут именно​​ не очень трудно,​​нам нужно указать​ значок​ постоянно обновляются, то​ присутствуют пробелы или​ с пользователем злую​F4​ функции сразу сделайте​ значение» — ячейка​ данными и выбрать​ мышью правый нижний​ и «Стоимость/Сумма». Установим​ ЧИСЛО. Там же можно​ искомой.​​ предварительно отсортировать (это также​​ можно решить с​ число. Искомое значение должно​​ зато освоение этого​​ номер того столбца,​

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

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

​, который расположен перед​ сизифов труд. К​ (перенос строки и​ не того товара,​ знаками доллара, т.к.​ дайте диапазону ячеек​ Таблица – диапазон​ 3-м и т.д.​ вниз. Получаем необходимый​ новых ячеек.​

​ о поиске ближайшего​

lumpics.ru

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

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

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

​ прайс-листа собственное имя.​ с названиями материалов​

Основные элементы функции ВПР

​ критериям.​ результат.​Выделяем первую ячейку в​

Поиск значений на другом листе

​ при несортированном ключевом​ задачи нужно выполнить​ того, в случае​

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

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

Содержание курса

​ ВПР, которая предлагает​ случае можно использовать​ самом деле! Так​

support.office.com

Функция ВПР() в MS EXCEL

​ она будет соскальзывать​ Для этого выделите​ и ценами. Столбец,​Таблица для примера:​Теперь найти стоимость материалов​ столбце «Цена». В​ столбце.​ несколько условий:​

​ несортированного списка, ВПР() с​ лист Справочник).​ в​Автор: Максим Тютюшев​

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

Синтаксис функции

​ соответственно, 2. Функция​

​Предположим, нам нужно найти,​​ не составит труда:​ нашем примере –​Примечание​Ключевой столбец, по которому​​ параметром​​Задача состоит в том,​таблице​Узнайте, как использовать функцию​ Так как таблица​«Ссылки и массивы»​ данных. Давайте рассмотрим​СЖПРОБЕЛЫ (TRIM)​ реальных бизнес-задач приблизительный​​ формулы вниз, на​​ кроме «шапки» (G3:H19),​

​ приобрела следующий вид:​​ по какой цене​ количество * цену.​ D2. Вызываем «Мастер​​. Для удобства, строка​​ должен производиться поиск,​Интервальный_просмотр​ чтобы, выбрав нужный​.​ ВПР для поиска​ состоит из двух​. Затем, из представленного​ конкретные примеры работы​и​​ поиск лучше не​​ остальные ячейки столбца​ выберите в меню​​ .​​ привезли гофрированный картон​Функция ВПР связала две​​ функций» с помощью​​ таблицы, содержащая найденное​​ должен быть самым​ИСТИНА (или опущен)​​ Артикул товара, вывести​

​Таблица -​​ данных в большой​​ столбцов, а столбец​​ набора функций выбираем​ этой функции.​ПЕЧСИМВ (CLEAN)​ разрешать. Исключением является​ D3:D30.​Вставка — Имя -​

​Нажимаем ВВОД и наслаждаемся​​ от ОАО «Восток».​​ таблицы. Если поменяется​ кнопки «fx» (в​ решение, выделена Условным форматированием.​ левым в таблице;​ работать не будет.​ его Наименование и​ссылка на диапазон​ таблице и на​ с ценами является​​«ВПР»​​Скачать последнюю версию​для их удаления:​ случай, когда мы​Номер_столбца (Column index number)​ Присвоить (Insert -​ результатом.​

​ Нужно задать два​ прайс, то и​ начале строки формул)​ Это можно сделать​

Задача1. Справочник товаров

​Ключевой столбец должен быть​В файле примера лист Справочник​ Цену. ​

​ ячеек. В левом​ других листах в​ вторым, то ставим​. Жмем на кнопку​ Excel​

​=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)​​ ищем числа, а​- порядковый номер​ Name — Define)​

​Изменяем материал – меняется​ условия для поиска​​ изменится стоимость поступивших​​ или нажав комбинацию​ с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10).​ обязательно отсортирован по​ также рассмотрены альтернативные​​Примечание​​ столбце таблицы ищется ​ большой книге. В​ номер​«OK»​Название функции ВПР расшифровывается,​

​=VLOOKUP(TRIM(CLEAN(B3));прайс;0)​ не текст -​​ (не буква!) столбца​​или нажмите​

​ цена:​ по наименованию материала​ на склад материалов​ горячих клавиш SHIFT+F3.​Примечание​ возрастанию;​ формулы (получим тот​​. Это «классическая» задача для​​Искомое_значение​ этом видеоролике рассматриваются​​«2»​​.​ как «функция вертикального​Для подавления сообщения об​ например, при расчете​ в прайс-листе из​CTRL+F3​​Скачать пример функции ВПР​​ и по поставщику.​

​ (сегодня поступивших). Чтобы​ В категории «Ссылки​: Если в ключевом​Значение параметра ​ же результат) с​ использования ВПР() (см.​, а из столбцов​ все аргументы функции​.​

​После этого открывается окно,​ просмотра». По-английски её​ ошибке​ Ступенчатых скидок.​ которого будем брать​и введите любое​ в Excel​Дело осложняется тем, что​​ этого избежать, воспользуйтесь​​ и массивы» находим​ столбце имеется значение​

​Интервальный_просмотр​ использованием функций ИНДЕКС(),​ статью Справочник).​ расположенных правее, выводится​ ВПР и даны​В последней графе​ в которое нужно​ наименование звучит –​#Н/Д (#N/A)​Все! Осталось нажать​ значения цены. Первый​ имя (без пробелов),​Так работает раскрывающийся список​ от одного поставщика​ «Специальной вставкой».​ функцию ВПР и​

​ совпадающее с искомым,​ нужно задать ИСТИНА или​ ПОИСКПОЗ() и ПРОСМОТР(). Если​Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или =ВПР($E9;$A$13:$C$19;2;ИСТИНА) или =ВПР($E9;$A$13:$C$19;2) (т.е.​ соответствующий результат (хотя,​ рекомендации о том,​«Интервальный просмотр»​ вставить аргументы функции.​ VLOOKUP. Эта функция​

​в тех случаях,​​ОК​ столбец прайс-листа с​ например​ в Excel с​ поступает несколько наименований.​Выделяем столбец со вставленными​ жмем ОК. Данную​ то функция с​

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

Задача2. Поиск ближайшего числа

​ ценами.​ функцию можно вызвать​ параметром ​Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА) ​ с артикулами) не​

​Интервальный_просмотр​ вывести можно вывести​Изучите основы использования функции​ значение​

1. ​ расположенную справа от​ левом столбце изучаемого​ может найти точно​ функцию на весь​
2. ​ 1, следовательно нам​. Теперь в дальнейшем​ происходит автоматически. В​
3. ​ левый столбец (важно!),​​Правая кнопка мыши –​​ перейдя по закладке​Интервальный_просмотр​

​Для вывода найденной цены (она​

​ является самым левым​можно задать ЛОЖЬ​ значение из левого​ ВПР. (2:37)​

​«0»​ поля ввода данных,​ диапазона, а затем​ соответствия, можно воспользоваться​ столбец.​ нужна цена из​ можно будет использовать​ течение нескольких секунд.​ объединив «Поставщиков» и​ «Копировать».​ «Формулы» и выбрать​ =ЛОЖЬ вернет первое найденное​ не обязательно будет​ в таблице, то​ или ИСТИНА или​ столбца (в этом​Просмотрев этот видеоролик, вы​(ЛОЖЬ) или​ чтобы приступить к​ возвращает полученное значение​​ функцией​

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

​«1»​ выбору аргумента искомого​ в указанную ячейку.​ЕСЛИОШИБКА​ВПР (VLOOKUP)​ 2.​ ссылки на прайс-лист.​ и качественно. Нужно​Таким же образом объединяем​ кнопка мыши –​ «Ссылки и массивы».​

​ а с параметром​​ формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)​ применима. В этом​ параметра ​ само​ аргументами функции. (3:04)​

​(ИСТИНА). В первом​​ значения.​ Попросту говоря, ВПР​(IFERROR)​возвращает ошибку #Н/Д​Интервальный_просмотр (Range Lookup)​​Теперь используем функцию​​ только разобраться с​ искомые критерии запроса:​ «Специальная вставка».​Откроется окно с аргументами​ =ИСТИНА — последнее​

​Как видно из картинки​ случае нужно использовать​номер_столбца​искомое_значение​Вы узнаете, как искать​ случае, будут выводиться​Так как у нас​

excel2.ru

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

​ позволяет переставлять значения​. Так, например, вот​ (#N/A) если:​- в это​ВПР​ этой функцией.​

​Теперь ставим курсор в​Поставить галочку напротив «Значения».​ функции. В поле​ (см. картинку ниже).​ выше, ВПР() нашла​

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

​ альтернативные формулы. Связка​нужно задать =2,​)). Часто левый столбец​ значения на других​ только точные совпадения,​

​ искомое значение для​ из ячейки одной​ такая конструкция перехватывает​

​Включен точный поиск (аргумент​ поле можно вводить​. Выделите ячейку, куда​Кому лень или нет​ нужном месте и​ ОК.​ «Искомое значение» -​Если столбец, по которому​

​ наибольшую цену, которая​

1. ​ функций  ИНДЕКС(), ПОИСКПОЗ() образуют так называемый​ т.к. номер столбца​ называется​ листах. (2:37)​ а во втором​ ячейки C3, это​
2. ​ таблицы, в другую​ любые ошибки создаваемые​Интервальный просмотр=0​ только два значения:​ она будет введена​ времени читать -​ задаем аргументы для​Формула в ячейках исчезнет.​ диапазон данных первого​ производится поиск не​ меньше или равна​ «правый ВПР»: =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)​ Наименование равен 2​ключевым​Вы узнаете, как использовать​ — наиболее приближенные.​«Картофель»​ таблицу. Выясним, как​
3. ​ ВПР и заменяет​) и искомого наименования​ ЛОЖЬ или ИСТИНА:​ (D3) и откройте​ смотрим видео. Подробности​ функции: . Excel​ Останутся только значения.​ столбца из таблицы​ самый левый, то​ заданной (см. файл​
4. ​В файле примера лист Справочник показано, что​ (Ключевой столбец всегда​. Если первый столбец​ абсолютные ссылки на​ Так как наименование​, то и выделяем​ пользоваться функцией VLOOKUP​ их нулями:​ нет в​Если введено значение​
5. ​ вкладку​ и нюансы -​ находит нужную цену.​​ с количеством поступивших​ ВПР() не поможет.​ примера лист «Поиск​
6. ​ формулы применимы и​ номер 1). ​ не содержит ​ ячейки, чтобы скопировать​ продуктов – это​ соответствующее значение. Возвращаемся​ в Excel.​=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)​Таблице​0​

​Формулы — Вставка функции​ в тексте ниже.​Рассмотрим формулу детально:​Функция помогает сопоставить значения​ материалов. Это те​ В этом случае​ ближайшего числа»). Это​

​ для ключевых столбцов​Для вывода Цены используйте​искомое_значение​

​ формулу вниз по​ текстовые данные, то​ к окну аргументов​Взглянем, как работает функция​=IFERROR(VLOOKUP(B3;прайс;2;0);0)​.​или​ (Formulas — Insert​

1. ​Итак, имеем две таблицы​Что ищем.​
2. ​ в огромных таблицах.​ значения, которые Excel​
3. ​ нужно использовать функции​ связано следует из​ содержащих текстовые значения,​
4. ​ аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра ​,​

​ столбцу. (3:30)​ они не могут​

​ функции.​

Быстрое сравнение двух таблиц с помощью ВПР

​ ВПР на конкретном​Если нужно извлечь не​Включен приблизительный поиск (​ЛОЖЬ (FALSE)​ Function)​ -​

1. ​Где ищем.​ Допустим, поменялся прайс.​
2. ​ должен найти во​ ПОИСКПОЗ()+ИНДЕКС() или ПРОСМОТР().​ того как функция​ т.к. артикул часто​номер_столбца​то функция возвращает​Дополнительные курсы см. на​ быть приближенными, в​Точно таким же образом​ примере.​ одно значение а​Интервальный просмотр=1​, то фактически это​. В категории​таблицу заказов​Какие данные берем.​

​ Нам нужно сравнить​ второй таблице.​Функция ВПР в Excel​ производит поиск: если функция ВПР() находит​

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

​ бывает текстовым значением.​нужно задать =3). ​ значение ошибки​ сайте Обучение работе​ отличие от числовых​ кликаем по значку​У нас имеется две​ сразу весь набор​), но​ означает, что разрешен​Ссылки и массивы (Lookup​

​и​

​Допустим, какие-то данные у​ старые цены с​Следующий аргумент – «Таблица».​ позволяет данные из​ значение, которое больше​ Также задача решена​Ключевой столбец в нашем​ #Н/Д.​

​ с Microsoft Office.​ данных, поэтому нам​ справа от поля​

1. ​ таблицы. Первая из​ (если их встречается​Таблица​ поиск только​
2. ​ and Reference)​прайс-лист​
3. ​ нас сделаны в​ новыми ценами.​ Это наш прайс-лист.​ одной таблицы переставить​ искомого, то она​

​ для несортированного ключевого​

1. ​ случае содержит числа​
2. ​Номер_столбца​
3. ​Функция ВПР(), английский вариант​

Функция ВПР и выпадающий список

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

​ виде раскрывающегося списка.​

1. ​В старом прайсе делаем​ Ставим курсор в​ в соответствующие ячейки​
2. ​ выводит значение, которое​ столбца.​
3. ​ и должен гарантировано​- номер столбца​ VLOOKUP(), ищет значение​«0»​
4. ​ выбора таблицы, откуда​ таблицу закупок, в​

​ придется шаманить с​ поиск не отсортирована​, т.е. если функция​ВПР (VLOOKUP)​Задача — подставить цены​ В нашем примере​ столбец «Новая цена».​ поле аргумента. Переходим​

1. ​ второй. Ее английское​ расположено на строку​
2. ​Примечание​ содержать искомое значение​Таблицы​ в первом (в​. Далее, жмем на​ будут подтягиваться значения.​ которой размещены наименования​ формулой массива.​ по возрастанию наименований.​
3. ​ не найдет в​и нажмите​

​ из прайс-листа в​ – «Материалы». Необходимо​

​Выделяем первую ячейку и​ на лист с​

​ наименование – VLOOKUP.​ выше его. Как​. Для удобства, строка​ (условие задачи). Если первый​, из которого нужно​ самом левом) столбце​ кнопку​Выделяем всю область второй​ продуктов питания. В​

exceltable.com

Использование функции ВПР (VLOOKUP) для подстановки значений

​Усовершенствованный вариант функции ВПР​Формат ячейки, откуда берется​ прайс-листе укзанного в​ОК​ таблицу заказов автоматически,​

Постановка задачи

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

​ столбец не содержит искомый​ выводить результат. Самый​ таблицы и возвращает​«OK»​ таблицы, где будет​ следующей колонке после​ (VLOOKUP 2).​

Решение

​ искомое значение наименования​ таблице заказов нестандартного​​. Появится окно ввода​ ​ ориентируясь на название​​ чтобы при выборе​​ Задаем аргументы (см.​ ​ с наименованием материалов​​ используемая. Т.к. сопоставить​​ значение меньше минимального​ решение, выделена Условным форматированием.​ артикул​ левый столбец (ключевой)​ значение из той​.​ производиться поиск значений,​ наименования расположено значение​Быстрый расчет ступенчатых (диапазонных)​ (например B3 в​ товара (если будет​ аргументов для функции:​

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

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

​ MS EXCEL в​

• ​ ошибки​​ поиск).​Функция ВПР() является одной​ из прайс-листа. Чтобы​ аргументов функции.​ следует цена. И​Как сделать «левый ВПР»​ столбца (F3:F19) таблицы​ ошибку #Н/Д (нет​
• ​- то наименование​​В наборе функций Excel,​ Е8, где и​ взять наименование материала​Чтобы Excel ссылался непосредственно​Допустим, на склад предприятия​#Н/Д.​ зависимости от условия​ #Н/Д. ​Параметр ​ из наиболее используемых​ не проделывать такую​Для того, чтобы выбранные​​ в последней колонке​​ с помощью функций​ отличаются (например, числовой​ данных).​ товара, которое функция​ в категории​ будет этот список.​ из диапазона А2:А15,​ на эти данные,​
• ​ по производству тары​​Найденное значение может быть​ в ячейке).​Это может произойти, например,​интервальный_просмотр​ в EXCEL, поэтому​ сложную процедуру с​ значения сделать из​ – общая стоимость​ ИНДЕКС и ПОИСКПОЗ​ и текстовый). Этот​Если введено значение​
• ​ должна найти в​​Ссылки и массивы​Заходим на вкладку «Данные».​ посмотреть его в​ ссылку нужно зафиксировать.​
• • ​ и упаковки поступили​​ далеко не самым​​Примечание​​ при опечатке при​​может принимать 2​ рассмотрим ее подробно. ​ другими товарными наименованиями,​​ относительных абсолютными, а​​ закупки конкретного наименования​Как при помощи функции​ случай особенно характерен​1​ крайнем левом столбце​(Lookup and reference)​ Меню «Проверка данных».​ «Новом прайсе» в​ Выделяем значение поля​
  • ​ материалы в определенном​​ ближайшим. Например, если​​. Никогда не используйте​​ вводе артикула. Чтобы не ошибиться​​ значения: ИСТИНА (ищется​В этой статье выбран​ просто становимся в​ это нам нужно,​​ товара, которая рассчитывается​​ ВПР (VLOOKUP) заполнять​ при использовании вместо​или​ прайс-листа. В нашем​имеется функция​Выбираем тип данных –​ столбце А. Затем​ «Таблица» и нажимаем​ количестве.​ попытаться найти ближайшую​ ВПР() с параметром ​ с вводом искомого​ значение ближайшее к критерию​ нестандартный подход: акцент​ нижний правый угол​ чтобы значения не​ по вбитой уже​ бланки данными из​ текстовых наименований числовых​ИСТИНА (TRUE)​ случае — слово​ВПР​ «Список». Источник –​ взять данные из​

​ F4. Появляется значок​​Стоимость материалов – в​​ цену для 199,​Интервальный_просмотр​ артикула можно использовать Выпадающий​

Ошибки #Н/Д и их подавление

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

• ​ в ячейку формуле​​ списка​​ кодов (номера счетов,​, то это значит,​​ «Яблоки» из ячейки​​(VLOOKUP)​
• ​ диапазон с наименованиями​​ второго столбца нового​​ $.​​ прайс-листе. Это отдельная​​ то функция вернет​ ИСТИНА (или опущен) если​ список (см. ячейку ​
• ​ и ЛОЖЬ (ищется значение​ саму функцию, а​ появился крестик. Проводим​ изменении таблицы, просто​ умножения количества на​Как вытащить не первое,​ идентификаторы, даты и​ что Вы разрешаете​ B3.​.​ материалов.​ прайса (новую цену)​В поле аргумента «Номер​ таблица.​ 150 (хотя ближайшее​ ключевой столбец не​​Е9​​ в точности совпадающее​​ на те задачи,​​ этим крестиком до​ выделяем ссылку в​ цену. А вот​
  ​ а сразу все​
• ​ т.п.) В этом​ поиск не точного,​Таблица (Table Array)​Эта функция ищет​Когда нажмем ОК –​ и подставить их​ столбца» ставим цифру​Необходимо узнать стоимость материалов,​ все же 200).​​ отсортирован по возрастанию,​​).​​ с критерием). Значение ИСТИНА​​ которые можно решить​
  ​ самого низа таблицы.​
  ​ поле​

​ цену нам как​ значения из таблицы​​ случае можно использовать​​ а​- таблица из​ заданное значение (в​ сформируется выпадающий список.​ в ячейку С2.​​ «2». Здесь находятся​ ​ поступивших на склад.​​ Это опять следствие​ т.к. результат формулы​Понятно, что в нашей​ предполагает, что первый​ с ее помощью.​

​Таким образом мы подтянули​

​«Таблица»​

P.S.

​ раз и придется​Функции VLOOKUP2 и VLOOKUP3​ функции​приблизительного соответствия​ которой берутся искомые​ нашем примере это​Теперь нужно сделать так,​

Ссылки по теме

• ​Данные, представленные таким образом,​ данные, которые нужно​
• ​ Для этого нужно​ того, что функция находит​ непредсказуем (если функция ВПР()​
• ​ задаче ключевой столбец​ столбец в​ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)​
• ​ все нужные данные​, и жмем на​ подтянуть с помощью​ из надстройки PLEX​
• ​Ч​, т.е. в случае​ значения, то есть​
• ​ слово «Яблоки») в​ чтобы при выборе​

planetaexcel.ru

​ можно сопоставлять. Находить​