Как найти функцию впр если ее нет

FIRST Пользователь Сообщений: 46 Регистрация: 27.08.2017	Еще раз всем здравствуйте, столкнулся  с проблемой. По какой то причине у меня в Excel нет формулы ВПР. Есть только ГПР. Может быть сталкивался кто нибудь?

Logistic Пользователь Сообщений: 744 Регистрация: 03.01.2013	Доброе утро. Не может быть такого . А Вы через поиск функции пытались найти?

FIRST Пользователь Сообщений: 46 Регистрация: 27.08.2017	Logistic, Здравствуйте, да конечно. Вот посмотрите, я сделал скриншот. Прикрепленные файлы 2.JPG (29.22 КБ) 1.JPG (36.73 КБ)

Bema Пользователь Сообщений: 6903 Регистрация: 15.02.2016	А если не через поиск, а в группе Ссылки и массивы посмотреть? Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

	А если просто ручками в ячейку ввести: =ВПР() Всё сложное — не нужно. Всё нужное — просто /М. Т. Калашников/

FIRST Пользователь Сообщений: 46 Регистрация: 27.08.2017	Михаил Лебедев, Спасибо за ответ. Но если бы дедушка был бабушкой он не был бы дедушкой. Bema, Тоже нет, искал там. Похоже куда то делась :d

Юрий М Модератор Сообщений: 60745 Регистрация: 14.09.2012 Контакты см. в профиле	FIRST, а Ваш второй скрин (файл 1.JPG) о чём?

FIRST Пользователь Сообщений: 46 Регистрация: 27.08.2017	Юрий М, Хотел показать окно в полном объёме

KOLLIAK Пользователь Сообщений: 147 Регистрация: 27.08.2015	FIRST,не пробовали искать английский синтаксис VLOOKUP? и  еще вопрос, это в любом файле или в каком то конкретном? напишите какая версия Excel? Изменено: KOLLIAK — 14.10.2017 18:15:54

Юрий М Модератор Сообщений: 60745 Регистрация: 14.09.2012 Контакты см. в профиле	#10 14.10.2017 18:21:02 Цитата FIRST написал: Хотел показать окно в полном объёме А показали только список 10 последних. Покажите фрагмент из полного алфавитного перечня, как на моём скрине. Прикрепленные файлы ВПР.jpg (47.24 КБ)

FIRST Пользователь Сообщений: 46 Регистрация: 27.08.2017	KOLLIAK, У меня office 2016 в любом файле Excel к сожалению. Простите про  VLOOKUP я не совсем понимаю

The_Prist Пользователь Сообщений: 14257 Регистрация: 15.09.2012 Профессиональная разработка приложений для MS Office	Поиск функций в Excel никогда не работал вменяемо, поэтому это вообще не ориентир. Искать надо в полном алфавитном перечне. Вот скрин его и сделайте. Если там нет — однозначно переустановка Excel. Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

Антон Б Пользователь Сообщений: 1 Регистрация: 25.11.2020	Господа, столкнулся с такой же фигней. Даже зарегался здесь, чтоб поделиться, вдруг кому поможет. Функция ВПР не ищется поиском, но есть в общем списке. А не ищется она только потому, что первая буква В — это английская Б (B — boot). bпр — так вы ее найдете поиском))))

Hugo Пользователь Сообщений: 23365 Регистрация: 22.12.2012	Это начиная с какой версии? Потому что в 2010 ВПР() есть.

New Пользователь Сообщений: 4657 Регистрация: 06.01.2013	Есть такое, сам тоже сталкивался с этим, но в 2019 исправили. Но можно было выбрать категорию «Ссылки и массивы» и там была ВПР. Фото поиска ВПР в 2019 Excel Изменено: New — 25.11.2020 17:39:35

PooHkrd Пользователь Сообщений: 6602 Регистрация: 22.02.2017 Excel x64 О365 / 2016 / Online / Power BI	Это они по аналогии как в ПРОСМОТРX, где последняя буква не «ха», а именно латиницей «икс». Вот горшок пустой, он предмет простой…

Nadzeya Yarmalkovich Пользователь Сообщений: 1 Регистрация: 05.03.2021	#17 11.05.2021 17:06:16 у меня такая же ситуация с функцией ЕСЛИМН. Просто пропала. Ни в формулах, ни на английскм не работает

Иллюстрация: Meery Mary для Skillbox Media

Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.

ВПР (Vlookup, или вертикальный просмотр) — поисковая функция в Excel. Она находит значения в одной таблице и переносит их в другую. Функция ВПР нужна, чтобы работать с большими объёмами данных — не нужно самостоятельно сопоставлять и переносить сотни наименований, функция делает это автоматически.

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

Представьте, что вы продаёте автомобили. У вас есть каталог с характеристиками авто и их стоимостью. Также у вас есть таблица с данными клиентов, которые забронировали эти автомобили.

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

Просто скопировать и вставить эту колонку не получится. Искать каждое авто вручную и переносить цены — долго.

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

Ниже пошагово и со скриншотами разберёмся, как сделать ВПР для этих двух таблиц с данными.

Важно!

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

---

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

Подробнее о том, как определить все эти значения, поговорим ниже. А пока разберёмся на примере с продажей авто, где найти функцию ВПР в Excel и с чего начать работу.

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

В нашем случае нужно перенести цены на авто из каталога в список клиентов. Для этого добавим пустой столбец «Цена, руб.» в таблицу с клиентами и выберем ячейку напротив первого клиента.

Дальше открываем окно для построения функции ВПР. Есть два способа сделать это. Первый — перейти во вкладку «Формулы» и нажать на «Вставить функцию».

Второй способ — нажать на «fx» в строке ссылок на любой вкладке таблицы.

Справа появляется окно «Построитель формул». В нём через поисковик находим функцию ВПР и нажимаем «Вставить функцию».

Появляется окно для ввода аргументов функции. Как их заполнять — разбираемся ниже.

---

Последовательно разберём каждый аргумент: искомое значение, таблица, номер столбца, интервальный просмотр.

Искомое значение — название ячейки с одинаковыми данными для обеих таблиц, по которым функция будет искать данные для переноса. В нашем примере это модель авто. Функция найдёт модель в таблице с каталогом авто, возьмёт оттуда стоимость и перенесёт в таблицу с клиентами.

Порядок действий, чтобы указать значение, выглядит так:

1. Ставим курсор в окно «Искомое значение» в построителе формул.
2. Выбираем первое значение столбца «Марка, модель» в таблице с клиентами. Это ячейка A2.

Выбранное значение переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=ВПР(A2).

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

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

Важно!

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

Порядок действий для указания диапазона:

1. Ставим курсор в окно «Таблица» в построителе формул.
2. Переходим в таблицу «Каталог авто».
3. Выбираем диапазон, в который попадают столбцы «Марка, модель» и «Цена, руб.». Это A2:E19.
4. Закрепляем выбранный диапазон. На Windows для этого выбираем значение диапазона в строке ссылок и нажимаем клавишу F4, на macOS — выбираем значение диапазона в строке ссылок и нажимаем клавиши Cmd + T. Закрепить диапазон нужно, чтобы можно было протянуть функцию вниз и она сработала корректно во всех остальных строках.

Выбранный диапазон переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19).

Номер столбца — порядковый номер столбца в первой таблице, в котором находится переносимое значение. Считается по принципу: номер 1 — самый левый столбец, 2 — столбец правее и так далее.

В нашем случае значение для переноса — цена — находится в пятом столбце слева.

Чтобы задать номер, установите курсор в окно «Номер столбца» в построителе формул и введите значение. В нашем примере это 5. Это значение появится в формуле в строке ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5).

Интервальный просмотр — условное значение, которое настроит, насколько точно сработает функция:

• Если нужно точное совпадение при поиске ВПР, вводим 0.
• Если нужно приближённое соответствие при поиске ВПР, вводим 1.

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

Ставим курсор в окно «Интервальный просмотр» в построителе формул и вводим значение: 0. Одновременно это значение появляется в формуле строки ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5;0). Это окончательный вид функции.

---

Чтобы получить результат функции, нажимаем кнопку «Готово» в построителе формул. В выбранной ячейке появляется нужное значение. В нашем случае — цена первой модели авто.

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

Получилась таблица с ценами — можно звонить клиентам и сообщать о стоимости авто. Данные перенесены без ошибок, а потратили мы на это несколько минут.

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

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

И по традиции есть таблица с клиентами, которые эти модели забронировали.

Если идти по классическому пути ВПР, получится такая функция: fx=ВПР(A29;’каталог авто’!$A$29:$E$35;5;0). В таком виде ВПР найдёт первую совпавшую модель и подтянет её стоимость. Параметр цвета не будет учтён.

Соответственно, цены у всех Nissan Juke будут 1 850 000 рублей, у всех Subaru Forester — 3 190 000 рублей, у всех Toyota C-HR — 2 365 000 рублей.

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

Дописываем в формулу фразу ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29, где:

• ‘каталог авто’!$B$29:$B$35 — закреплённый диапазон цвета автомобилей в таблице, откуда нужно перенести данные. Это весь столбец с ценами.
• B29 — искомое значение цвета автомобиля в таблице, куда мы переносим данные. Это первая ячейка в столбце с цветом — дополнительным параметром для поиска.

Итоговая функция такая: fx=ВПР(A29;ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29;’каталог авто’!$A$29:$E$35);5;0). Теперь значения цен переносятся верно.

Как использовать ВПР в «Google Таблицах»? В них тоже есть функция Vlookup, но нет окна построителя формул. Поэтому придётся прописывать её вручную. Перечислите через точку с запятой все аргументы и не забудьте зафиксировать диапазон. Для фиксации поставьте перед каждым символом значок доллара. В готовой формуле это будет выглядеть так: =ВПР(A2;’Лист1′!$A$2:$C$5;3;0).

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

Функции ИНДЕКС и ПОИСКПОЗ в Excel – лучшая альтернатива для ВПР

​Смотрите также​Другими словами если в​​ соответствующей цены.​​Формула в ячейках исчезнет.​​ далеко не самым​​ использованием функции ВПР().​ с функцией​ один вариант ставки​Пример из жизни. Ставим​​ на другую деталь,​​Если всё сделано верно,​=ИНДЕКС($A$1:$E$11;4;5))​, значения в столбце​ заметно быстрее.​любой вставленный или​B​​ нужны, и поэтому​​Этот учебник рассказывает о​

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

​ значения «груши», «яблока»​ таблицы под названием​​​ попытаться найти ближайшую​​ (см. файл примера​.​ для тех продавцов,​Усложняем задачу​ ВПР нашла ближайшее​ как на рисунке​ на пересечении​ упорядочены по возрастанию,​ причины, из-за которых​​ результат формулы, поскольку​​ в ячейках​​ПОИСКПОЗ​​ИНДЕКС​ мы не сможем​ столбца «Цена».​Функция помогает сопоставить значения​ цену для 199,​​ лист Справочник).​​Если поэкспериментируем с несколькими​

• ​ кто сделал объём​Применяем функцию ВПР к​
• ​ число, меньшее или​ ниже:​
• ​4-ой​ а формула вернёт​
• ​ стоит изучать функции​ синтаксис​
  • ​B2:B10​.​
  • ​и​ просуммировать всех груш​
  • ​Выберите «Формулы»-«Ссылки и массивы»-«ВПР».​ в огромных таблицах.​
  • ​ то функция вернет​
  • ​Задача состоит в том,​ различными значениями итоговой​

Базовая информация об ИНДЕКС и ПОИСКПОЗ

​ продаж более $50000.​ решению задачи​ равное указанному (2345678).​​Как Вы, вероятно, уже​​строки и​​ максимальное значение, меньшее​​ПОИСКПОЗ​ВПР​, и возвращает число​Функция​ПОИСКПОЗ​

​ и яблок. Для​Ввести функцию ВПР​ Допустим, поменялся прайс.​ 150 (хотя ближайшее​ чтобы, выбрав нужный​ суммы продаж, то​​ А если кто-то​​Заключение​​ Эта ошибка может​​ заметили (и не​​5-го​​ или равное среднему.​

ИНДЕКС – синтаксис и применение функции

​и​​требует указывать весь​​3​MATCH​в Excel, которые​ этого нужно использовать​ можно и с​ Нам нужно сравнить​

​ все же 200).​
​ Артикул товара, вывести​

​ мы убедимся, что​ продал на сумму​

• ​Проиллюстрируем эту статью примером​​ привести к неправильному​ раз), если вводить​столбца в диапазоне​Если указываете​
• ​ИНДЕКС​​ диапазон и конкретный​, поскольку «Japan» в​(ПОИСКПОЗ) в Excel​ делают их более​ функцию ПРОСМОТР(). Она​ помощью «мастера функций».​ старые цены с​​ Это опять следствие​​ его Наименование и​
• ​ формула работает правильно.​​ более $60000 –​ из реальной жизни​ выставлению счета клиенту.​ некорректное значение, например,​A1:E11​-1​, давайте перейдём к​​ номер столбца, из​​ списке на третьем​

​ ищет указанное значение​ привлекательными по сравнению​​ очень похожа на​​ Для этого нажмите​ новыми ценами.​ того, что функция находит​ Цену. ​

​Когда функция​​ тому заплатить 60%​​ – расчёт комиссионных​

​Если для аргумента "приблизительное​
​ которого нет в​

​, то есть значение​, значения в столбце​​ самому интересному и​​ которого нужно извлечь​ месте.​​ в диапазоне ячеек​​ с​​ ВПР но умеет​​ на кнопку «fx»,​В старом прайсе делаем​​ наибольшее число, которое​​Примечание​

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

ПОИСКПОЗ – синтаксис и применение функции

​Функция​​ и возвращает относительную​​ВПР​ хорошо работать с​ которая находиться в​ столбец «Новая цена».​ меньше или равно​. Это «классическая» задача для​

​работает с базами​​Теперь формула в ячейке​​ ряда показателей продаж.​ ЛОЖЬ или 0,​ИНДЕКС​E4​​ упорядочены по убыванию,​​ применить теоретические знания​Например, если у Вас​INDEX​

​ позицию этого значения​
​. Вы увидите несколько​

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

​ использования ВПР() (см.​
​ данных, аргумент​

• ​ B2, даже если​​ Мы начнём с​ а точного совпадения​/​. Просто? Да!​ а возвращено будет​ на практике.​ есть таблица​
• ​(ИНДЕКС) использует​​ в диапазоне.​ примеров формул, которые​ значениях.​
• ​ Или нажмите комбинацию​​ выбираем функцию ВПР.​Если нужно найти по​​ статью Справочник).​​Range_lookup​ она записана без​ очень простого варианта,​
  • ​ нет, вместо неправильного​​ПОИСКПОЗ​​В учебнике по​​ минимальное значение, большее​Любой учебник по​A1:C10​3​Например, если в диапазоне​ помогут Вам легко​Функция ВПР (Вертикальный ПРосмотр)​
  • ​ горячих клавиш SHIFT+F3.​​ Задаем аргументы (см.​ настоящему ближайшее к​Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или =ВПР($E9;$A$13:$C$19;2;ИСТИНА) или =ВПР($E9;$A$13:$C$19;2) (т.е.​​(Интервальный_просмотр) должен принимать​​ ошибок, стала совершенно​​ и затем постепенно​​ значения формула возвращает​сообщает об ошибке​ВПР​​ или равное среднему.​​ВПР​​, и требуется извлечь​​для аргумента​
  • ​B1:B3​​ справиться со многими​ ищет по таблице​В появившимся диалоговом​ выше). Для нашего​ искомому значению, то ВПР() тут​ значение параметра​FALSE​ не читаемой. Думаю,​

​ будем усложнять его,​ в ячейку строку​​#N/A​​мы показывали пример​В нашем примере значения​твердит, что эта​ данные из столбца​row_num​

​содержатся значения New-York,​ сложными задачами, перед​ с данными и​ окне на поле​ примера: . Это​ не поможет. Такого​Интервальный_просмотр​(ЛОЖЬ). А значение,​​ что найдется мало​​ пока единственным рациональным​​ «#Н/Д». Это наилучшее​​(#Н/Д) или​​ формулы с функцией​​ в столбце​ функция не может​​B​​(номер_строки), который указывает​ Paris, London, тогда​ которыми функция​ на основе критериев​ категория, выберите из​ значит, что нужно​ рода задачи решены​можно задать ЛОЖЬ​

Как использовать ИНДЕКС и ПОИСКПОЗ в Excel

​ введённое в качестве​ желающих использовать формулы​ решением задачи не​ решение. В данном​#VALUE!​ВПР​​D​​ смотреть влево. Т.е.​​, то нужно задать​​ из какой строки​​ следующая формула возвратит​​ВПР​ запроса поиска, возвращает​ выпадающего списка: «Ссылки​ взять наименование материала​​ в разделе Ближайшее​​ или ИСТИНА или​Lookup_value​ с 4-мя уровнями​ станет использование функции​

​ случае «#Н/Д» не​(#ЗНАЧ!). Если Вы​​для поиска по​​упорядочены по возрастанию,​​ если просматриваемый столбец​​ значение​

​ нужно возвратить значение.​ цифру​бессильна.​
​ соответствующее значение с​ и массивы», а​ из диапазона А2:А15,​

​ ЧИСЛО. Там же можно​ вообще опустить). Значение​(Искомое_значение) должно существовать​ вложенности в своих​ВПР​

​ означает, что формула​ хотите заменить такое​ нескольким критериям. Однако,​ поэтому мы используем​

​ не является крайним​
​2​

​ Т.е. получается простая​3​В нескольких недавних статьях​

• ​ определенного столбца. Очень​​ потом ниже укажите​​ посмотреть его в​ найти решение задачи​​ параметра ​​ в базе данных.​ проектах. Должен же​​. Первоначальный сценарий нашей​​ введена неправильно (за​​ сообщение на что-то​​ существенным ограничением такого​ тип сопоставления​ левым в диапазоне​
• ​для аргумента​​ формула:​​, поскольку «London» –​​ мы приложили все​​ часто необходимо в​​ на функцию.​​ «Новом прайсе» в​ о поиске ближайшего​номер_столбца​ Другими словами, идёт​ существовать более простой​

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

  ​ более понятное, то​ решения была необходимость​1​​ поиска, то нет​​col_index_num​​=INDEX($D$2:$D$10,3)​​ это третий элемент​ усилия, чтобы разъяснить​ запросе поиска использовать​Заполняем аргументы функции.​​ столбце А. Затем​​ при несортированном ключевом​нужно задать =2,​ поиск точного совпадения.​

​ способ?!​ так: если продавец​

​ номера). Это означает,​ можете вставить формулу​ добавлять вспомогательный столбец.​​. Формула​​ шансов получить от​(номер_столбца) функции​​=ИНДЕКС($D$2:$D$10;3)​​ в списке.​​ начинающим пользователям основы​​ сразу несколько условий.​​В поле «Исходное значение»​​ взять данные из​ столбце.​

​ т.к. номер столбца​В примере, что мы​И такой способ есть!​​ за год делает​​ что номер 2345678​ с​ Хорошая новость: формула​ИНДЕКС​​ВПР​​ВПР​​Формула говорит примерно следующее:​​=MATCH(«London»,B1:B3,0)​

​ функции​
​ Но по умолчанию​

​ вводим ссылку на​ второго столбца нового​Примечание​ Наименование равен 2​ рассмотрели в данной​ Нам поможет функция​​ объём продаж более​​ не был найден,​​ИНДЕКС​​ИНДЕКС​/​желаемый результат.​, вот так:​​ ищи в ячейках​​=ПОИСКПОЗ(«London»;B1:B3;0)​​ВПР​​ данная функция не​ ячейку под наименованием​ прайса (новую цену)​​. Для удобства, строка​​ (Ключевой столбец всегда​

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

​ статье, нет необходимости​ВПР​ $30000, то его​ потому что вы​​и​​/​​ПОИСКПО​​Функции​​=VLOOKUP(«lookup value»,A1:C10,2)​​ от​Функция​и показать примеры​​ может обработать более​​ товара второй таблицы​ и подставить их​ таблицы, содержащая найденное​ номер 1). ​ получать точное соответствие.​.​ комиссионные составляют 30%.​​ искали значение 2345768.​​ПОИСКПОЗ​​ПОИСКПОЗ​​З​​ПОИСКПОЗ​​=ВПР(«lookup value»;A1:C10;2)​D2​MATCH​ более сложных формул​

​ одного условия. Поэтому​ D3. В поле​​ в ячейку С2.​​ решение, выделена Условным форматированием.​​Для вывода Цены используйте​​ Это тот самый​Давайте немного изменим дизайн​ В противном случае,​​В этом примере показано,​​в функцию​​может искать по​​возвращает «Moscow», поскольку​​и​​Если позднее Вы вставите​

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

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

​номер_столбца​ВПР​​ сохраним все те​​ 20%. Оформим это​​ Если ввести значение​.​ столбцах, без необходимости​ Москва – ближайшее​в Excel гораздо​ столбцами​​и извлеки значение​​MATCH(lookup_value,lookup_array,[match_type])​ если не отговорить​ позволит расширить возможности​ таблицы A2:B7. В​​ численную и процентную​​Примечание​нужно задать =3). ​должна переключиться в​ же поля и​ в виде таблицы:​

​ в ячейку B2​Синтаксис функции​​ создания вспомогательного столбца!​​ меньшее к среднему​ более гибкие, и​​A​​ из третьей строки,​ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​​ Вас от использования​​ функции ВПР по​​ поле «Номер столбца»​​ разницу.​​: Если в ключевом​​Ключевой столбец в нашем​

​ режим приближенной работы,​
​ данные, но расположим​

​Продавец вводит данные о​ (первый аргумент), функция​ЕСЛИОШИБКА​​Предположим, у нас есть​​ значению (12 269​​ им все-равно, где​​и​ то есть из​​lookup_value​​ВПР​​ нескольким столбцам одновременно.​​ вводим значение 2,​До сих пор мы​ столбце имеется значение​

​ случае содержит числа​​ чтобы вернуть нам​​ их по-новому, в​​ своих продажах в​​ ВПР выполняет поиск​очень прост:​ список заказов, и​ 006).​ находится столбец со​B​ ячейки​(искомое_значение) – это​, то хотя бы​Для наглядности разберем формулу​ так как во​ предлагали для анализа​ совпадающее с искомым,​ и должен гарантировано​ нужный результат.​ более компактном виде:​ ячейку B1, а​​ в ячейках C2:E7​​IFERROR(value,value_if_error)​

​ мы хотим найти​Эта формула эквивалентна двумерному​​ значением, которое нужно​​, то значение аргумента​​D4​ число или текст,​ показать альтернативные способы​ ВПР с примером​ втором столбце у​​ только одно условие​​ то функция с​ содержать искомое значение​Например:​Прервитесь на минутку и​ формула в ячейке​​ (второй аргумент) и​​ЕСЛИОШИБКА(значение;значение_если_ошибка)​​ сумму по двум​​ поиску​

​ извлечь. Для примера,​ придется изменить с​​, так как счёт​​ который Вы ищите.​ реализации вертикального поиска​​ нескольких условий. Для​​ нас находиться цена,​​ – наименование материала.​​ параметром ​ (условие задачи). Если первый​​Мы хотим определить,​​ убедитесь, что новая​

​ B2 определяет верную​
​ возвращает наиболее близкое​

​Где аргумент​ критериям –​ВПР​​ снова вернёмся к​​2​ начинается со второй​ Аргумент может быть​ в Excel.​​ примера будем использовать​​ которую мы хотим​​ На практике же​​Интервальный_просмотр​

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

​ таблица​ ставку комиссионного вознаграждения,​​ приблизительное совпадение из​value​имя покупателя​и позволяет найти​ таблице со столицами​на​ строки.​ значением, в том​Зачем нам это? –​ схематический отчет по​ получить при поиске​ нередко требуется сравнить​ =ЛОЖЬ вернет первое найденное​ артикул​​ в расчёте комиссионных​​Rate Table​​ на которое продавец​​ третьего столбца в​​(значение) – это​​(Customer) и​ значение на пересечении​ государств и населением.​​3​​Вот такой результат получится​

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

​ ОК.​ данными и выбрать​​ а с параметром​​то функция возвращает значение​​ объёмом продаж $34988.​​ данные, что и​ свою очередь, полученная​ E (третий аргумент).​ предмет наличия ошибки​

ИНДЕКС и ПОИСКПОЗ – примеры формул

​(Product). Дело усложняется​ столбца.​ запишем формулу​​ результат из только​​Важно! Количество строк и​​lookup_array​​ВПР​В данном отчете необходимо​Теперь под заголовком столбца​ значение по 2,​ =ИСТИНА — последнее​

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

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

​– это не​​ найти показатель выручки​​ второй таблицы «Товар»​​ 3-м и т.д.​​ (см. картинку ниже).​ #Н/Д. ​ВПР​ значений.​ ячейке B3, чтобы​ аргумент оставлен пустым,​ – результат формулы​ покупатель может купить​ИНДЕКС​/​Используя​​ который использует функция​​ ячеек, в котором​​ единственная функция поиска​​ для определенного торгового​ введите наименования того​ критериям.​Если столбец, по которому​

​Это может произойти, например,​возвращает нам значение​Основная идея состоит в​ рассчитать общую сумму​

​ поэтому функция возвращает​
​ИНДЕКС​

​ сразу несколько разных​/​ИНДЕКС​ПОИСКПОЗ​

• ​INDEX​​ происходит поиск.​​ в Excel, и​ представителя в определенную​ товара по котором​

  ​Таблица для примера:​
​ производится поиск не​

• ​ при опечатке при​ 30%, что является​​ том, чтобы использовать​​ комиссионных, которую продавец​ приблизительное совпадение.​/​ продуктов, и имена​​ПОИСКПОЗ​​, которая покажет, какое​
• ​/​(ИНДЕКС), должно соответствовать​

  ​match_type​
​ её многочисленные ограничения​

​ дату. Учитывая условия​​ нам нужно узнать​Предположим, нам нужно найти,​ самый левый, то​​ вводе артикула. Чтобы не ошибиться​​ абсолютно верным. Но​​ функцию​​ должен получить (простое​Разобравшись с функцией ВПР,​ПОИСКПОЗ​ покупателей в таблице​

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

​будет очень похожа​ место по населению​​ИНДЕКС​​ значениям аргументов​​(тип_сопоставления) – этот​​ могут помешать Вам​ поиска наш запрос​ его цену. И​ по какой цене​ ВПР() не поможет.​ с вводом искомого​ почему же формула​

​ВПР​​ перемножение ячеек B1​​ несложно будет освоить​); а аргумент​​ на листе​​ на формулы, которые​ занимает столица России​​, Вы можете удалять​​row_num​

​ аргумент сообщает функции​
​ получить желаемый результат​

​ должен содержать 2​

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

​Lookup table​
​ мы уже обсуждали​

​ (Москва).​

​ или добавлять столбцы​​(номер_строки) и​​ПОИСКПОЗ​ во многих ситуациях.​​ условия:​​Функция позволяет нам быстро​ от ОАО «Восток».​ нужно использовать функции​ список (см. ячейку ​​ именно 30%, а​​ тарифной ставки по​

​Самая интересная часть таблицы​
​ Функция ГПР использует​

​(значение_если_ошибка) – это​

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

​расположены в произвольном​​ в этом уроке,​​Как видно на рисунке​​ к исследуемому диапазону,​​column_num​​, хотите ли Вы​​ С другой стороны,​– Дата сдачи выручки​​ находить данные и​​ Нужно задать два​ ПОИСКПОЗ()+ИНДЕКС() или ПРОСМОТР().​​Е9​​ не 20% или​​ таблице​​ заключена в ячейке​ те же аргументы,​ значение, которое нужно​ порядке.​ с одним лишь​ ниже, формула отлично​ не искажая результат,​(номер_столбца) функции​​ найти точное или​​ функции​ в кассу.​

• ​ получать по ним​​ условия для поиска​​Функция ВПР в Excel​).​ 40%? Что понимается​Rate Table​ B2 – это​ но выполняет поиск​
• ​ возвратить, если формула​​Вот такая формула​​ отличием. Угадайте каким?​ справляется с этой​ так как определен​MATCH​ приблизительное совпадение:​ИНДЕКС​

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

Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу

​ из больших таблиц.​ и по поставщику.​​ одной таблицы переставить​​ задаче ключевой столбец​ Давайте внесём ясность.​ объема продаж. Обратите​ ставки комиссионного вознаграждения.​

​ столбцов.​​Например, Вы можете вставить​​/​​ функции​​=INDEX($A$2:$A$10,MATCH(«Russia»,$B$2:$B$10,0))​ нужное значение. Действительно,​ формулы будет ошибочным.​или​ПОИСКПОЗ​ будем использовать функцию​

​ Это похоже на​Дело осложняется тем, что​​ в соответствующие ячейки​​ не должен содержать​Когда аргумент​

​ внимание, что продавец​
​ Эта формула содержит​

​Если вы не хотите​ формулу из предыдущего​ПОИСКПОЗ​

​INDEX​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​ это большое преимущество,​Стоп, стоп… почему мы​не указан​​– более гибкие​​ ВПР по нескольким​​ работу с базами​​ от одного поставщика​ второй. Ее английское​ повторов (в этом​​Range_lookup​​ может продать товаров​ функцию Excel под​

​ ограничиваться поиском в​ примера в функцию​решает задачу:​(ИНДЕКС) позволяет использовать​Теперь у Вас не​
​ особенно когда работать​ не можем просто​– находит максимальное​ и имеют ряд​ условиям и составим​

​ данных. Когда к​ поступает несколько наименований.​ наименование – VLOOKUP.​ смысл артикула, однозначно​​(Интервальный_просмотр) имеет значение​​ на такую сумму,​​ названием​​ крайнем левом столбце,​

​ЕСЛИОШИБКА​{=INDEX(‘Lookup table’!$A$2:$C$13,MATCH(1,(A2=’Lookup table’!$A$2:$A$13)*​ три аргумента:​ должно возникать проблем​ приходится с большими​ использовать функцию​ значение, меньшее или​ особенностей, которые делают​

​ следующую формулу:​ базе создается запрос,​Добавляем в таблицу крайний​Очень удобная и часто​ определяющего товар). В​TRUE​ которая не равна​

​IF​ можно использовать сочетание​​вот таким образом:​​(B2=’Lookup table’!$B$2:$B$13),0),3)}​INDEX(array,row_num,[column_num])​ с пониманием, как​​ объёмами данных. Вы​​VLOOKUP​

• ​ равное искомому. Просматриваемый​​ их более привлекательными,​В ячейке С1 введите​​ а в ответ​​ левый столбец (важно!),​ используемая. Т.к. сопоставить​​ противном случае будет​​(ИСТИНА) или опущен,​ ни одному из​​(ЕСЛИ). Для тех​​ функций ИНДЕКС и​=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),​

  ​{=ИНДЕКС('Lookup table'!$A$2:$C$13;ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13)*​
​ИНДЕКС(массив;номер_строки;[номер_столбца])​

  ​ работает эта формула:​​ можете добавлять и​​(ВПР)? Есть ли​ массив должен быть​ по сравнению с​​ первое значение для​​ выводятся результаты, которые​

• ​ объединив «Поставщиков» и​​ вручную диапазоны с​ выведено самое верхнее​​ функция​​ пяти имеющихся в​​ читателей, кто не​​ ПОИСКПОЗ. Формула, использующая​»Совпадений не найдено.​​(B2=’Lookup table’!$B$2:$B$13);0);3)}​​И я поздравляю тех​

  ​Во-первых, задействуем функцию​
​ удалять столбцы, не​

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

​ являются ответом на​ «Материалы».​​ десятками тысяч наименований​​ значение.​

​ВПР​
​ таблице пороговых значений.​

​ знаком с этой​​ эти функции вместе,​​ Попробуйте еще раз!»)​Эта формула сложнее других,​ из Вас, кто​MATCH​

​ беспокоясь о том,​
​ пытаясь разобраться в​

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

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

​=ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));​​ которые мы обсуждали​​ догадался!​(ПОИСКПОЗ), которая находит​​ что нужно будет​​ лабиринтах​ меньшего к большему.​Базовая информация об ИНДЕКС​ 22.03.2017.​​ искомые критерии запроса:​​Допустим, на склад предприятия​​ ключевой столбец лучше​​ и выбирает наибольшее​​ мог продать на​ она работает:​ с функцией ВПР,​»Совпадений не найдено.​

​ ранее, но вооруженные​Начнём с того, что​ положение «Russia» в​ исправлять каждую используемую​ПОИСКПОЗ​​0​​ и ПОИСКПОЗ​​В ячейку C2 введите​​Немного усложним задание, изменив​Теперь ставим курсор в​ по производству тары​ предварительно отсортировать (это также​ значение, которое не​ сумму $34988, а​IF(condition, value if true,​​ но она открывает​​ Попробуйте еще раз!»)​ знанием функций​

​ запишем шаблон формулы.​​ списке:​​ функцию​​и​​– находит первое​

​Используем функции ИНДЕКС и​
​ фамилию торгового представителя​
​ структуру и увеличив​
​ нужном месте и​

​ и упаковки поступили​ поможет сделать Выпадающий​ превышает искомое.​ такой суммы нет.​​ value if false)​​ больше возможностей. Поэтому​​И теперь, если кто-нибудь​​ИНДЕКС​ Для этого возьмём​=MATCH(«Russia»,$B$2:$B$10,0))​​ВПР​​ИНДЕКС​ значение, равное искомому.​

​ ПОИСКПОЗ в Excel​
​ (например, Новиков). Это​

​ объем данных в​ задаем аргументы для​ материалы в определенном​​ список нагляднее). Кроме​​Важный момент:​ Давайте посмотрим, как​ЕСЛИ(условие; значение если ИСТИНА;​ некоторые пользователи предпочитают​ введет ошибочное значение,​и​ уже знакомую нам​

• ​=ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​.​​?​​ Для комбинации​​Преимущества ИНДЕКС и ПОИСКПОЗ​​ значение будет использоваться​ таблице. Расширьте объем​ функции: . Excel​ количестве.​​ того, в случае​​Чтобы эта схема​
• ​ функция​ значение если ЛОЖЬ)​​ применять сочетание функций​​ формула выдаст вот​ПОИСКПОЗ​​ формулу​​Далее, задаём диапазон для​
• ​3. Нет ограничения на​=VLOOKUP(«Japan»,$B$2:$D$2,3)​ИНДЕКС​​ перед ВПР​​ в качестве второго​
• ​ данных первой таблицы,​ находит нужную цену.​Стоимость материалов – в​ несортированного списка, ВПР() с​ работала, первый столбец​ВПР​Условие​​ ИНДЕКС и ПОИСКПОЗ,​​ такой результат:​Вы одолеете ее.​ИНДЕКС​ функции​ размер искомого значения.​​=ВПР(«Japan»;$B$2:$D$2;3)​​/​

​ИНДЕКС и ПОИСКПОЗ –​ аргумента поискового запроса.​​ добавив столбцы: «январь»,​​Рассмотрим формулу детально:​ прайс-листе. Это отдельная​​ параметром​​ таблицы должен быть​сможет справиться с​– это аргумент​

​ а не функцию​​Если Вы предпочитаете в​ Самая сложная часть​/​INDEX​​Используя​​В данном случае –​ПОИСКПОЗ​ примеры формул​В ячейке C3 мы​ «февраль», «март». Там​Что ищем.​ таблица.​Интервальный_просмотр​ отсортирован в порядке​​ такой ситуацией.​​ функции, который принимает​ ВПР.​​ случае ошибки оставить​​ – это функция​

​ПОИСКПОЗ​(ИНДЕКС), из которого​ВПР​ смысла нет! Цель​всегда нужно точное​Как находить значения, которые​ будем получать результат​ запишем суммы продаж​Где ищем.​Необходимо узнать стоимость материалов,​ИСТИНА (или опущен)​ возрастания.​​Выбираем ячейку B2 (место,​​ значение либо​

​В данном примере представлен​ ячейку пустой, то​ПОИСКПОЗ​и добавим в​

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

​ нужно извлечь значение.​, помните об ограничении​ этого примера –​ совпадение, поэтому третий​ находятся слева​ поиска, для этого​​ в первом квартале​​Какие данные берем.​​ поступивших на склад.​​ работать не будет.​​Урок подготовлен для Вас​​ куда мы хотим​​TRUE​​ небольшой список, в​ можете использовать кавычки​, думаю, её нужно​ неё ещё одну​ В нашем случае​ на длину искомого​​ исключительно демонстрационная, чтобы​​ аргумент функции​​Вычисления при помощи ИНДЕКС​​ там следует ввести​​ как показано на​​Допустим, какие-то данные у​

​ Для этого нужно​​В файле примера лист Справочник​​ командой сайта office-guru.ru​

​ вставить нашу формулу),​
​(ИСТИНА), либо​

​ котором искомое значение​​ («»), как значение​​ объяснить первой.​ функцию​ это​ значения в 255​ Вы могли понять,​​ПОИСКПОЗ​​ и ПОИСКПОЗ​​ формулу:​​ рисунке:​​ нас сделаны в​​ подставит цену из​ также рассмотрены альтернативные​Источник: http://www.howtogeek.com/howto/14455/vlookup-in-excel-part-2-using-vlookup-without-a-database/​ и находим​

​FALSE​ (Воронеж) не находится​ второго аргумента функции​​MATCH(1,(A2=’Lookup table’!$A$2:$A$13),0)*(B2=’Lookup table’!$B$2:$B$13)​​ПОИСКПОЗ​

​A2:A10​
​ символов, иначе рискуете​ как функции​ ​должен быть равен​
​Поиск по известным строке​После ввода формулы для​

​Как видите вторую таблицу​ виде раскрывающегося списка.​ второй таблицы в​ формулы (получим тот​

​Перевел: Антон Андронов​VLOOKUP​(ЛОЖЬ). В примере,​ в крайнем левом​ЕСЛИОШИБКА​ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13);0)*(B2=’Lookup table’!$B$2:$B$13)​​, которая будет возвращать​​.​

​ получить ошибку​
​ПОИСКПОЗ​

​0​ и столбцу​ подтверждения нажмите комбинацию​ так же нужно​ В нашем примере​ первую. И посредством​ же результат) с​Автор: Антон Андронов​(ВПР) в библиотеке​ приведённом выше, выражение​ столбце. Поэтому мы​. Вот так:​В формуле, показанной выше,​ номер столбца.​Затем соединяем обе части​

​#VALUE!​и​
​.​
​Поиск по нескольким критериям​

​ горячих клавиш CTRL+SHIFT+Enter,​

office-guru.ru

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

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

​IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),»»)​ искомое значение –​

​=INDEX(Ваша таблица,(MATCH(значение для вертикального​

​ и получаем формулу:​

• ​(#ЗНАЧ!). Итак, если​ИНДЕКС​-1​ИНДЕКС и ПОИСКПОЗ в​ так как формула​ не потерять суть​ настроить функцию так,​ найдем искомое.​

• ​ ПОИСКПОЗ() и ПРОСМОТР(). Если​ VLOOKUP(), ищет значение​Formulas​Правда ли, что B1​

  ​ функцию ВПР. Для​​ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);»»)​ это​ поиска,столбец, в котором​=INDEX($A$2:$A$10;MATCH(«Russia»;$B$2:$B$10;0))​ таблица содержит длинные​работают в паре.​

• ​– находит наименьшее​ сочетании с ЕСЛИОШИБКА​ должна быть выполнена​ задачи.​ чтобы при выборе​

​Алгоритм действий:​ ключевой столбец (столбец​ в первом (в​(Формулы) >​ меньше B5?​ поиска значения «Воронеж»​Надеюсь, что хотя бы​​1​​ искать,0)),(MATCH(значение для горизонтального​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​ строки, единственное действующее​ Последующие примеры покажут​ значение, большее или​Так как задача этого​​ в массиве.​ ​Теперь нам нужно сделать​​ наименования появлялась цена.​Приведем первую таблицу в​ с артикулами) не​

​ самом левом) столбце​Function Library​Или можно сказать по-другому:​ в диапазоне B1:B11​ одна формула, описанная​, а массив поиска​ поиска,строка в которой​Подсказка:​ решение – это​ Вам истинную мощь​ равное искомому значению.​​ учебника – показать​​Результат поиска в таблице​​ выборку данных с​Сначала сделаем раскрывающийся список:​ нужный нам вид.​ является самым левым​ таблицы и возвращает​(Библиотека Функций) >​Правда ли, что общая​ будет использоваться функция​ в этом учебнике,​

​ – это результат​ искать,0))​Правильным решением будет​ использовать​ связки​ Просматриваемый массив должен​ возможности функций​ по двум условиям:​ помощью функции ВПР​Ставим курсор в ячейку​ Добавим столбцы «Цена»​ в таблице, то​ значение из той​Lookup & Reference​ сумма продаж за​ ПОИСКПОЗ. Оно найдено​ показалась Вам полезной.​ умножения. Хорошо, что​

​=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального​ всегда использовать абсолютные​ИНДЕКС​ИНДЕКС​ быть упорядочен по​ИНДЕКС​Найдена сумма выручки конкретного​ отдельно по товару​ Е8, где и​ и «Стоимость/Сумма». Установим​ функция ВПР() не​ же строки, но​(Ссылки и массивы).​

​ год меньше порогового​ в строке 4.​ Если Вы сталкивались​ же мы должны​

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

​ поиска,столбец, в котором​ ссылки для​/​и​ убыванию, то есть​и​ торгового представителя на​ и просуммировать продажи​

Одновременное использование функций ИНДЕКС и ПОИСКПОЗ

​ будет этот список.​ денежный формат для​ применима. В этом​ другого столбца таблицы.​Появляется диалоговое окно​ значения?​ Затем функция ИНДЕКС​ с другими задачами​ перемножить и почему?​ искать,0)),(MATCH(значение для горизонтального​ИНДЕКС​ПОИСКПОЗ​ПОИСКПОЗ​ от большего к​ПОИСКПОЗ​ конкретную дату.​

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

Еще о функциях поиска

• ​(Аргументы функции). По​ мы отвечаем​

• ​ в качестве аргумента​ не смогли найти​

• ​ по порядку:​ искать,0))​

​ПОИСКПОЗ​

support.office.com

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

​Предположим, Вы используете вот​ с многими сложными​На первый взгляд, польза​ поиска в Excel,​​Разбор принципа действия формулы​​ в ячейку H3​Выбираем тип данных –​ столбце «Цена». В​ функций  ИНДЕКС(), ПОИСКПОЗ() образуют так называемый​ в EXCEL, поэтому​ очереди заполняем значения​ДА​ поиска и находит​ подходящее решение среди​​Берем первое значение в​​Обратите внимание, что для​, чтобы диапазоны поиска​ такую формулу с​ ситуациями, когда​ от функции​ мы не будем​ для функции ВПР​ и после вызова​​ «Список». Источник –​​ нашем примере –​

​ «правый ВПР»: =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)​ рассмотрим ее подробно. ​ аргументов, начиная с​(ИСТИНА), то функция​​ численность населения Воронежа​​ информации в этом​ столбце​ двумерного поиска нужно​ не сбились при​ВПР​ВПР​

​ПОИСКПОЗ​ задерживаться на их​​ с несколькими условиями:​​ функции заполняем ее​ диапазон с наименованиями​ D2. Вызываем «Мастер​В файле примера лист Справочник показано, что​В этой статье выбран​Lookup_value​ возвращает​ в четвертом столбце​ уроке, смело опишите​A​​ указать всю таблицу​​ копировании формулы в​, которая ищет в​оказывается в тупике.​вызывает сомнение. Кому​​ синтаксисе и применении.​​Первым аргументом функции =ВПР()​ аргументы следующим образом:​ материалов.​ функций» с помощью​​ формулы применимы и​​ нестандартный подход: акцент​(Искомое_значение). В данном​value if true​ (столбец D). Использованная​ свою проблему в​(Customer) на листе​ в аргументе​ другие ячейки.​

• ​ ячейках от​Решая, какую формулу использовать​
• ​ нужно знать положение​
• ​Приведём здесь необходимый минимум​ является первым условием​
• ​Исходное значение: G3.​

Пример из жизни. Ставим задачу

​Когда нажмем ОК –​ кнопки «fx» (в​ для ключевых столбцов​ сделан не на​ примере это общая​(значение если ИСТИНА).​ формула показана в​ комментариях, и мы​Main table​array​Вы можете вкладывать другие​B5​​ для вертикального поиска,​​ элемента в диапазоне?​ для понимания сути,​ для поиска значения​Таблица: A2:E7. Диапазон нашей​ сформируется выпадающий список.​ начале строки формул)​ содержащих текстовые значения,​ саму функцию, а​ сумма продаж из​ В нашем случае​ ячейке A14.​

​ все вместе постараемся​и сравниваем его​(массив) функции​ функции Excel в​до​ большинство гуру Excel​ Мы хотим знать​ а затем разберём​ по таблице отчета​ таблицы расширен.​Теперь нужно сделать так,​ или нажав комбинацию​ т.к. артикул часто​ на те задачи,​ ячейки B1. Ставим​ это будет значение​

​Краткий справочник: обзор функции​ решить её.​ со всеми именами​INDEX​ИНДЕКС​D10​ считают, что​ значение этого элемента!​​ подробно примеры формул,​​ выручки торговых представителей.​Номер столбца: {3;4;5}. Нам​ чтобы при выборе​ горячих клавиш SHIFT+F3.​ бывает текстовым значением.​

​ которые можно решить​ курсор в поле​
​ ячейки B6, т.е.​ ВПР​

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

​ определенного материала в​ В категории «Ссылки​

​ Также задача решена​

​ с ее помощью.​Lookup_value​ ставка комиссионных при​Функции ссылки и поиска​

​ командой сайта office-guru.ru​ на листе​​А теперь давайте испытаем​​ПОИСКПОЗ​ ячейке​​/​​ положение искомого значения​ использования​ находится виртуальная таблица​ функции обращаться одновременно​ графе цена появлялась​ и массивы» находим​ для несортированного ключевого​ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)​(Искомое_значение) и выбираем​​ общем объёме продаж​​ (справка)​​Источник: https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/​​Lookup table​ этот шаблон на​, например, чтобы найти​A2​ПОИСКПОЗ​ (т.е. номер строки​ИНДЕКС​

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

​(A2:A13).​ практике. Ниже Вы​

Усложняем задачу

​ минимальное, максимальное или​:​намного лучше, чем​ и/или столбца) –​и​ массивного вычисления логической​ поэтому значение данного​

​ курсор в ячейку​ жмем ОК. Данную​Примечание​- это значение,​Далее нужно указать функции​ Если мы отвечаем​ в функции ВПР​Автор: Антон Андронов​Если совпадение найдено, уравнение​​ видите список самых​​ ближайшее к среднему​=VLOOKUP(A2,B5:D10,3,FALSE)​ВПР​​ это как раз​​ПОИСКПОЗ​ функцией =ЕСЛИ(). Каждая​ аргумента будет взято​ Е9 (где должна​ функцию можно вызвать​. Для удобства, строка​ которое Вы пытаетесь​ВПР​ на вопрос​

​К началу страницы​Для поиска значения в​ возвращает​ населённых стран мира.​ значение. Вот несколько​=ВПР(A2;B5:D10;3;ЛОЖЬ)​. Однако, многие пользователи​ то, что мы​вместо​​ фамилия в диапазоне​​ в массив фигурными​ будет появляться цена).​

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

​ ячеек B6:B12 сравнивается​ скобками. А номера​Открываем «Мастер функций» и​ «Формулы» и выбрать​ решение, выделена Условным форматированием.​ с данными.​ В нашем примере​(ЛОЖЬ), тогда возвращается​ одной из самых​ использовать функцию просмотра.​(ИСТИНА), а если​ узнать население США​

​ к таблице из​ если значение в​​ к использованию​​ аргументов​

Применяем функцию ВПР к решению задачи

​.​ со значением в​ столбцов следует перечислять​ выбираем ВПР.​ из выпадающего списка​ (см. статью Выделение​Искомое_значение ​

​ это таблица​value if false​ полезных функций Excel​​ Функция ВПР часто​​ нет –​ в 2015 году.​ предыдущего примера:​ ячейке​

​ВПР​row_num​Функция​​ ячейке C2. Таким​​ через точку с​Первый аргумент – «Искомое​ «Ссылки и массивы».​​ строк таблицы в​​может быть числом или​Rate Table​(значение если ЛОЖЬ).​ под названием​ используется, но можно​0​Хорошо, давайте запишем формулу.​1.​A2​, т.к. эта функция​(номер_строки) и/или​INDEX​ образом в памяти​ запятой.​ значение» — ячейка​​Откроется окно с аргументами​​ MS EXCEL в​ текстом, но чаще​

Вставляем функцию ВПР

​. Ставим курсор в​ В нашем случае​ВПР​ задействовать и функции​​(ЛОЖЬ).​​ Когда мне нужно​MAX​​длиннее 255 символов.​​ гораздо проще. Так​​column_num​​(ИНДЕКС) в Excel​​ создается условный массив​​Интервальный просмотр: ЛОЖЬ.​

​ с выпадающим списком.​​ функции. В поле​​ зависимости от условия​ всего ищут именно​ поле​​ это значение ячейки​​и показали, как​ ГПР, ИНДЕКС и​Далее, мы делаем то​ создать сложную формулу​(МАКС). Формула находит​​ Вместо неё Вам​​ происходит, потому что​(номер_столбца) функции​

​ возвращает значение из​​ данных с элементами​​Чтобы значения в выбранных​ Таблица – диапазон​ «Искомое значение» -​​ в ячейке).​​ число. Искомое значение должно​Table_array​​ B7, т.е. ставка​​ она может быть​ ПОИСКПОЗ.​​ же самое для​​ в Excel с​

​ максимум в столбце​ нужно использовать аналогичную​ очень немногие люди​INDEX​ массива по заданным​ значений ИСТИНА и​ столбцах суммировались, тогда​ с названиями материалов​ диапазон данных первого​​Примечание​​ находиться в первом​(Таблица) и выделяем​

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

​ вложенными функциями, то​​D​ формулу​ до конца понимают​(ИНДЕКС). Как Вы​​ номерам строки и​​ ЛОЖЬ.​ всю функцию нужно​​ и ценами. Столбец,​​ столбца из таблицы​. Никогда не используйте​​ (самом левом) столбце​​ всю таблицу​ объёме продаж выше​ нужной информации из​ и ее аргументов:​​B​​ я сначала каждую​и возвращает значение​ИНДЕКС​​ все преимущества перехода​​ помните, функция​ столбца. Функция имеет​Потом благодаря формуле, в​

​ поместить внутрь функции​ соответственно, 2. Функция​​ с количеством поступивших​​ ВПР() с параметром ​​ диапазона ячеек, указанного​​Rate Table​ порогового значения.​ базы данных в​=ВПР(;;;)​​(Product).​​ вложенную записываю отдельно.​ из столбца​

​/​ с​​ИНДЕКС​​ вот такой синтаксис:​ памяти программы каждый​ СУММ(). Вся формула​​ приобрела следующий вид:​​ материалов. Это те​

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

Заключение

​Например, =ВПР(21500;C2:E7;3;ЛОЖЬ).​​Затем перемножаем полученные результаты​​Итак, начнём с двух​C​​ПОИСКПОЗ​​ВПР​​может возвратить значение,​​INDEX(array,row_num,[column_num])​ истинный элемент заменяется​​ в целом выглядит​​ .​ значения, которые Excel​ ИСТИНА (или опущен) если​таблице​

​Далее мы должны уточнить,​ если мы берём​ Мы также упомянули,​Первый аргумент (часть, необходимая​ (1 и 0).​ функций​​той же строки:​​:​на связку​ находящееся на пересечении​ИНДЕКС(массив;номер_строки;[номер_столбца])​

​ на 3-х элементный​​ следующим образом: =СУММ(ВПР(G3;A3:E6;{3;4;5};ЛОЖЬ)).​Нажимаем ВВОД и наслаждаемся​ должен найти во​ ключевой столбец не​.​ данные из какого​​ общую сумму продаж​​ что существует два​ для работы функции)​ Только если совпадения​ПОИСКПОЗ​=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))​=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))​ИНДЕКС​ заданных строки и​Каждый аргумент имеет очень​ набор данных:​

​После ввода данной формулы​​ результатом.​​ второй таблице.​​ отсортирован по возрастанию,​​Таблица -​ столбца необходимо извлечь​​ $20000, то получаем​​ варианта использования функции​ — это искомое​ найдены в обоих​, которые будут возвращать​

​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))​​=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))​и​ столбца, но она​ простое объяснение:​элемент – Дата.​

​ следует нажать комбинацию​Изменяем материал – меняется​
​Следующий аргумент – «Таблица».​
​ т.к. результат формулы​

​ссылка на диапазон​

office-guru.ru

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

​ с помощью нашей​ в ячейке B2​ВПР​ значение. Это может​ столбцах (т.е. оба​ номера строки и​Результат: Beijing​4. Более высокая скорость​

​ПОИСКПОЗ​ не может определить,​array​элемент – Фамилия.​

​ клавиш: CTRL+SHIFT+ENTER. Внимание!​ цена:​ Это наш прайс-лист.​ непредсказуем (если функция ВПР()​ ячеек. В левом​ формулы. Нас интересует​ ставку комиссионных 20%.​

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

​и только один​

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

​ из них имеет​​ ячейку, например B2,​ получите​ИНДЕКС​​MIN​​Если Вы работаете​ на изучение более​ и столбец нас​ диапазон ячеек, из​А каждый ложный элемент​ комбинацию этих клавиш​ в Excel​ поле аргумента. Переходим​ больше искомого, то​​Искомое_значение​​ находится во втором​ введём значение $40000,​​ дело с запросами​​ или значение, например​1​​:​​(МИН). Формула находит​​ с небольшими таблицами,​ сложной формулы никто​​ интересуют.​

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

​ к базе данных.​​ «кузьмина» или 21500.​​. Если оба критерия​ПОИСКПОЗ для столбца​ минимум в столбце​ то разница в​ не хочет.​Теперь, когда Вам известна​ значение.​ на 3-х элементный​ ошибочно. В Excel​​ в Excel с​​ ценами. Выделяем диапазон​ которое расположено на​ расположенных правее, выводится​ для аргумента​ изменится на 30%:​ В этой статье​

​Второй аргумент — это​ ложны, или выполняется​– мы ищем​D​

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

​ быстродействии Excel будет,​Далее я попробую изложить​ базовая информация об​

​row_num​ набор пустых текстовых​ иногда приходиться выполнять​ функцией ВПР. Все​ с наименованием материалов​

​ строку выше его).​​ соответствующий результат (хотя,​Col_index_num​Таким образом работает наша​

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

​Предположим, что нужно найти​ в принципе, можно​​(Номер_столбца) вводим значение​​ таблица.​

​ менее известный способ​ как вы предполагаете,​ них – Вы​B​ из столбца​ заметная, особенно в​ПОИСКПОЗ​​ полагаю, что уже​​ номер строки в​ результате создается в​​ для этого нужно​​ течение нескольких секунд.​ какие значения функция​ товар, у которого​ вывести можно вывести​ 2.​Давайте немного усложним задачу.​​ применения функции​​ содержит искомое значение.​

​ получите​, а точнее в​C​ последних версиях. Если​и​ становится понятно, как​ массиве, из которой​ памяти программы новая​ обязательно использовать клавиши:​

​ Все работает быстро​ должна сопоставить.​ цена равна или​ значение из левого​И, наконец, вводим последний​ Установим ещё одно​ВПР​Важно:​​0​​ диапазоне​той же строки:​

​ же Вы работаете​ИНДЕКС​ функции​ нужно извлечь значение.​ таблица, с которой​ CTRL+SHIFT+ENTER при вводе​ и качественно. Нужно​Чтобы Excel ссылался непосредственно​ наиболее близка к​ столбца (в этом​ аргумент —​ пороговое значение: если​в Excel.​ В функции ВПР столбец,​.​B2:B11​

​=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))​ с большими таблицами,​в Excel, а​ПОИСКПОЗ​ Если не указан,​ уже будет работать​ функций. Тогда в​ только разобраться с​ на эти данные,​

​ искомой.​​ случае это будет​Range_lookup​ продавец зарабатывает более​Если Вы этого ещё​ содержащий искомое значение​Теперь понимаете, почему мы​, значение, которое указано​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))​

​ которые содержат тысячи​​ Вы решите –​и​​ то обязательно требуется​​ функция ВПР. Она​ строке формул все​ этой функцией.​ ссылку нужно зафиксировать.​Чтобы использовать функцию ВПР()​ само​(Интервальный_просмотр).​ $40000, тогда ставка​ не сделали, то​ или ссылку на​

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

​ задали​ в ячейке​Результат: Lima​ строк и сотни​ остаться с​

​ИНДЕКС​ аргумент​ игнорирует все пустые​ содержимое будет взято​

1. ​ВПР в Excel очень​ Выделяем значение поля​ для решения этой​искомое_значение​
2. ​Важно:​ комиссионных возрастает до​ обязательно прочтите прошлую​
3. ​ ячейку, должен быть​​1​​H2​3.​

​ формул поиска, Excel​

​ВПР​могут работать вместе.​column_num​ наборы данных элементов.​

​ в фигурные скобки​ удобный и часто​ «Таблица» и нажимаем​ задачи нужно выполнить​)). Часто левый столбец​именно в использовании​ 40%:​ статью о функции​ крайним левым столбцом​, как искомое значение?​(USA). Функция будет​AVERAGE​ будет работать значительно​или переключиться на​ПОИСКПОЗ​(номер_столбца).​ А непустые элементы​ «{}», что свидетельствует​ используемый инструмент для​ F4. Появляется значок​​ несколько условий:​

​ называется​ этого аргумента заключается​Вроде бы всё просто​ВПР​ в диапазоне.​ Правильно, чтобы функция​ выглядеть так:​(СРЗНАЧ). Формула вычисляет​ быстрее, при использовании​ИНДЕКС​определяет относительную позицию​column_num​ сопоставляются со значением​

​ о выполнении формулы​ работы с таблицами​ $.​Ключевой столбец, по которому​ключевым​ различие между двумя​ и понятно, но​, поскольку вся информация,​Третий аргумент — это​ПОИСКПОЗ​=MATCH($H$2,$B$1:$B$11,0)​

​ среднее в диапазоне​​ПОИСКПОЗ​/​ искомого значения в​(номер_столбца) – это​ ячейки C1, использованного​

​ в массиве.​​ как с базой​В поле аргумента «Номер​ должен производиться поиск,​. Если первый столбец​ способами применения функции​​ наша формула в​​ изложенная далее, предполагает,​ столбец в диапазоне​возвращала позицию только,​=ПОИСКПОЗ($H$2;$B$1:$B$11;0)​D2:D10​

​и​ПОИСКПОЗ​ заданном диапазоне ячеек,​ номер столбца в​ в качестве первого​Теперь вводите в ячейку​ данных и не​

excel2.ru

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

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

​ поиска ячеек, содержащий​ когда оба критерия​Результатом этой формулы будет​, затем находит ближайшее​ИНДЕКС​

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

​.​ а​ массиве, из которого​ критерия поискового запроса​ G3 наименование товара,​

​ только. Данная функция​ «2». Здесь находятся​ левым в таблице;​

​искомое_значение​. При работе с​ заметно сложнее. Если​ знакомы с принципами,​ значение, которое нужно​ выполняются.​4​ к нему и​

​вместо​

1. ​1. Поиск справа налево.​ИНДЕКС​ нужно извлечь значение.​ (Дата). Одним словом,​ в ячейке H3​ проста в освоении​
2. ​ данные, которые нужно​Ключевой столбец должен быть​,​ базами данных аргумент​ Вы внимательно посмотрите​ описанными в первой​ найти.​Обратите внимание:​, поскольку «USA» –​ возвращает значение из​ВПР​Как известно любому​использует это число​ Если не указан,​ таблица в памяти​ получаем сумму продаж​ и очень функциональна​ «подтянуть» в первую​
3. ​ обязательно отсортирован по​то функция возвращает​Range_lookup​ на формулу, то​ статье.​Хотя четвертый аргумент не​В этом случае​ это 4-ый элемент​ столбца​. В целом, такая​
4. ​ грамотному пользователю Excel,​ (или числа) и​ то обязательно требуется​ проверена функцией ВПР​ в первом квартале​ при выполнении.​ таблицу. «Интервальный просмотр»​ возрастанию;​ значение ошибки​(Интервальный_просмотр) должен всегда​
5. ​ увидите, что третий​При работе с базами​ является обязательным, большинство​ необходимо использовать третий​ списка в столбце​C​ замена увеличивает скорость​
6. ​ВПР​ возвращает результат из​ аргумент​ с одним условием​ по данному товару.​Благодаря гармоничному сочетанию простоты​ — ЛОЖЬ. Т.к.​Значение параметра ​ #Н/Д.​ иметь значение​

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

​не может смотреть​ соответствующей ячейки.​row_num​

​ поиска. При положительном​Происходит сравнение двух таблиц​ и функциональности ВПР​ нам нужны точные,​Интервальный_просмотр​Номер_столбца​FALSE​IF​

1. ​ВПР​ ЛОЖЬ (или 0).​
2. ​ функции​(включая заголовок).​
3. ​=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))​13%​ влево, а это​
4. ​Ещё не совсем понятно?​(номер_строки)​

​ результате сопоставления функция​ в Excel функцией​

​ пользователи активно ее​

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

​ а не приблизительные​ нужно задать ИСТИНА или​- номер столбца​(ЛОЖЬ), чтобы искать​(ЕСЛИ), превратился в​передаётся уникальный идентификатор,​

1. ​ Почему? Потому что​ИНДЕКС​
2. ​ПОИСКПОЗ для строки​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))​.​ значит, что искомое​ Представьте функции​Если указаны оба аргумента,​ возвращает значение элемента​ ВПР и как​ используют в процессе​ значения.​ вообще опустить.​Таблицы​ точное соответствие. В​ ещё одну полноценную​ который служит для​ в этом случае​

​. Он необходим, т.к.​– мы ищем​Результат: Moscow​Влияние​

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

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

​ определения информации, которую​

​ функция будет искать​ в первом аргументе​ значение ячейки​Используя функцию​ВПР​ находиться в крайнем​и​ИНДЕКС​

​ (выручка) условной таблицы.​ запрашиваемых данных, сразу​ таблицами. Но стоит​

1. ​ «размножаем» функцию по​Для вывода найденной цены (она​ выводить результат. Самый​ использования функции​
2. ​IF​ мы хотим найти​
3. ​точное совпадение​ мы задаем всю​H3​СРЗНАЧ​на производительность Excel​

​ левом столбце исследуемого​

1. ​ПОИСКПОЗ​
2. ​возвращает значение из​
3. ​ Это происходит потому,​

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

​ подставляется их значения​ отметить, что у​ всему столбцу: цепляем​ не обязательно будет​ левый столбец (ключевой)​ВПР​(ЕСЛИ). Такая конструкция​ (например, код товара​

​. Можно ввести аргумент​

1. ​ таблицу и должны​(2015) в строке​в комбинации с​
2. ​ особенно заметно, если​ диапазона. В случае​
3. ​в таком виде:​ ячейки, находящейся на​ что в третьем​ для суммирования функцией​
4. ​ данной функции достаточно​ мышью правый нижний​

​ совпадать с заданной) используйте​ имеет номер 1​, мы должны оставить​ называется вложением функций​ или идентификационный номер​ ИСТИНА или вообще​ указать функции, из​1​

1. ​ИНДЕКС​ рабочая книга содержит​
2. ​ с​=INDEX(столбец из которого извлекаем,(MATCH​ пересечении указанных строки​ аргументе указывается номер​ СУММ. Весь процесс​ много недостатков, которые​ угол и тянем​ формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)​ (по нему производится​
3. ​ это поле пустым,​ друг в друга.​

​ клиента). Этот уникальный​ не вводить аргумент,​

​ какого столбца нужно​, то есть в​

​и​ сотни сложных формул​ПОИСКПОЗ​ (искомое значение,столбец в​ и столбца.​ столбца 3 из​ выполняется циклически благодаря​ ограничивают возможности. Поэтому​ вниз. Получаем необходимый​

exceltable.com

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

​Как видно из картинки​ поиск).​ либо ввести значение​ Excel с радостью​ код должен присутствовать​ но если точное​ извлечь значение. В​ ячейках​ПОИСКПОЗ​ массива, таких как​

​/​ котором ищем,0))​Вот простейший пример функции​ которого берутся значения.​ массиву функций о​ ее иногда нужно​ результат.​ выше, ВПР() нашла​Параметр ​TRUE​ допускает такие конструкции,​ в базе данных,​ совпадение не будет​ нашем случае это​A1:E1​, в качестве третьего​ВПР+СУММ​ИНДЕКС​=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое​

Как работает функция ВПР в Excel: пример

​INDEX​ Стоит отметить что​ чем свидетельствуют фигурные​ использовать с другими​Теперь найти стоимость материалов​ наибольшую цену, которая​интервальный_просмотр​(ИСТИНА). Крайне важно​ и они даже​ иначе​ найдено, функция вернет​ столбец​:​ аргумента функции​

1. ​. Дело в том,​, столбец поиска может​ значение;столбец в котором​
2. ​(ИНДЕКС):​​ для просмотра в​ скобки в строке​ функциями или вообще​ не составит труда:​ меньше или равна​может принимать 2​ правильно выбрать этот​ работают, но их​ВПР​​наиболее близкое​C​=MATCH($H$3,$A$1:$E$1,0)​ПОИСКПОЗ​ что проверка каждого​ быть, как в​ ищем;0))​
3. ​=INDEX(A1:C10,2,3)​

​ аргументах функции указывается​ формул.​ заменять более сложными.​ количество * цену.​ заданной (см. файл​ значения: ИСТИНА (ищется​ параметр.​ гораздо сложнее читать​сообщит об ошибке.​приблизительное совпадение​(Sum), и поэтому​=ПОИСКПОЗ($H$3;$A$1:$E$1;0)​чаще всего нужно​ значения в массиве​ левой, так и​Думаю, ещё проще будет​=ИНДЕКС(A1:C10;2;3)​

​ целая таблица (во​Примечание. Если ввести вручную​ Для начала на​Функция ВПР связала две​ примера лист «Поиск​ значение ближайшее к критерию​Чтобы было понятнее, мы​

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

​ таблицы. Если поменяется​

Функция ВПР в Excel и две таблицы

​ ближайшего числа»). Это​ или совпадающее с ним)​ введём​Мы не будем вникать​ мы рассмотрим такой​ приблизительное совпадение не​3​5​1​ функции​ диапазона поиска. Пример:​

​ Предположим, у Вас​ диапазоне​ сам поиск всегда​ в строку формул​ функции рассмотрим ее​

​ прайс, то и​ связано следует из​ и ЛОЖЬ (ищется значение​TRUE​ в технические подробности​ способ использования функции​ устраивает.​.​, поскольку «2015» находится​или​ВПР​

1. ​ Как находить значения,​
2. ​ есть вот такой​A1:C10​
3. ​ идет по первому​ то это не​ преимущества, а потом​ изменится стоимость поступивших​ того как функция​ в точности совпадающее​(ИСТИНА) в поле​ — почему и​ВПР​Чтобы убедиться в том,​И, наконец, т.к. нам​
4. ​ в 5-ом столбце.​
5. ​-1​. Поэтому, чем больше​ которые находятся слева​ список столиц государств:​и возвращает значение​ столбцу в указанной​ приведет ни ка​
6. ​ определим недостатки.​ на склад материалов​ производит поиск: если функция ВПР() находит​ с критерием). Значение ИСТИНА​Range_lookup​ как это работает,​, когда идентификатора не​ что использование приблизительного​ нужно проверить каждую​Теперь вставляем эти формулы​в случае, если​ значений содержит массив​ покажет эту возможность​Давайте найдём население одной​ ячейки во​ таблицы.​ какому результату. Выполнить​Функция ВПР предназначена для​ (сегодня поступивших). Чтобы​

​ значение, которое больше​ предполагает, что первый​(Интервальный_просмотр). Хотя, если​ и не будем​ существует в базе​ совпадения может иметь​

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

​ оставить поле пустым,​ вдаваться в нюансы​ данных вообще. Как​ серьезные последствия, предположим,​ эта формула должна​ИНДЕКС​ что просматриваемый диапазон​ формул массива содержит​2. Безопасное добавление или​ Японии, используя следующую​

​строке и​ с несколькими условиями​ можно только через​ таблицы Excel по​ «Специальной вставкой».​ выводит значение, которое​

​таблице​ это не будет​

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

exceltable.com

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

​Выделяем столбец со вставленными​ расположено на строку​отсортирован в алфавитном​ ошибкой, так как​ Ведь это статья,​ВПР​ детали с идентификатором​ Вы можете видеть​=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))​ среднему. Если же​ медленнее работает Excel.​Формулы с функцией​=INDEX($D$2:$D$10,MATCH(«Japan»,$B$2:$B$10,0))​столбце, то есть​А из какого столбца​ CTRL+SHIFT+ENTER.​ Например, если таблица​ ценами.​ выше его. Как​

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

​ порядке или по​TRUE​ посвященная функции​переключилась в режим​ 2345768, но вы​ это по фигурным​=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))​

​ Вы уверены, что​С другой стороны, формула​ВПР​=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ(«Japan»;$B$2:$B$10;0))​ из ячейки​ брать возвращаемое значение​Стоит отметить, что главным​ состоит из двух​

1. ​Правая кнопка мыши –​ следствие, если искомое​
2. ​ возрастанию. Это способ​

​— это его​ВПР​ приближенной работы, и​ перепутали две цифры​ скобкам, в которые​

1. ​Если заменить функции​ такое значение есть,​ с функциями​перестают работать или​Теперь давайте разберем, что​
2. ​C2​ указывается уже в​ недостатком функции ВПР​ колонок: «Наименование товара»​ «Копировать».​ значение меньше минимального​
3. ​ используется в функции​ значение по умолчанию:​, а не полное​ сама выбирает, какие​ и ввели их​
4. ​ она заключена. Поэтому,​ПОИСКПОЗ​ – ставьте​ПОИСКПОЗ​ возвращают ошибочные значения,​ делает каждый элемент​

​.​ третьем аргументе.​

​ является отсутствие возможности​ и «Цена». Рядом​Не снимая выделения, правая​

​ в ключевом столбце,​

​ по умолчанию, если​Мы заполнили все параметры.​ руководство по Excel.​

​ данные предоставить нам,​ в формулу следующим​ когда закончите вводить​на значения, которые​0​и​ если удалить или​ этой формулы:​Очень просто, правда? Однако,​Число 0 в последнем​ выбрать несколько одинаковых​ находится другая таблица,​ кнопка мыши –​ то функцию вернет​ не указан другой.​ Теперь нажимаем​Как бы там ни​ когда мы что-то​ образом:​

​ формулу, не забудьте​ они возвращают, формула​для поиска точного​ИНДЕКС​ добавить столбец в​

1. ​Функция​
2. ​ на практике Вы​
3. ​ аргументе функции указывает​

​ исходных значений в​ которая будет искать​ «Специальная вставка».​ ошибку ​Ниже в статье рассмотрены​ОК​ было, формула усложняется!​ хотим найти. В​=ВПР​ нажать​ станет легкой и​ совпадения.​просто совершает поиск​ таблицу поиска. Для​MATCH​ далеко не всегда​ на то, то​ запросе.​ в первой таблице​Поставить галочку напротив «Значения».​#Н/Д.​ популярные задачи, которые​, и Excel создаёт​ А что, если​ определённых обстоятельствах именно​(2345678;A1:E7;5)​Ctrl+Shift+Enter​ понятной:​Если указываете​ и возвращает результат,​ функции​(ПОИСКПОЗ) ищет значение​ знаете, какие строка​ совпадение должно быть​Скачать пример функции ВПР​ по наименованию товара​ ОК.​Найденное значение может быть​ можно решить с​ для нас формулу​

​ мы введем еще​ так и нужно.​. Формула возвращает цену​

​.​=INDEX($A$1:$E$11,4,5))​1​ выполняя аналогичную работу​

​ВПР​ «Japan» в столбце​ и столбец Вам​ абсолютно точным.​ с двумя таблицами​

exceltable.com

​ и получать значение​