Функция подстановки данных в excel

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Формулы подстановки Excel: ВПР, ИНДЕКС и ПОИСКПОЗ

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

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

Предположим, у вас есть таблица с данными о работниках. В первой колонке хранится табельный номер сотрудника, в остальных – другие данные (ФИО, отдел и т.д.). Если у вас есть табельный номер, то можно воспользоваться функцией ВПР, чтобы вернуть определенную информацию о сотруднике. Синтаксис формулы =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр]). Она говорит Excel: «Найди в таблице строку, первая ячейка которой совпадает с искомым_значением, и верни значение ячейки с порядковым номером номер_столбца».

Но случаются ситуации, когда у вас есть имя сотрудника и необходимо вернуть табельный номер. На рисунке в ячейке A10 – имя работника и требуется определить табельный номер в ячейке B10.

Когда ключевое поле находится правее данных, которые вы хотите получить, ВПР не поможет. Если, конечно, была бы возможность задать номер_столбца -1, тогда проблем бы не было. Одним из распространенных решений является добавление нового столбца A, копирование имен сотрудников в этот столбец, заполнить табельные номера с помощью ВПР, сохранить их как значения и удалить временную колонку A.

Функция ИНДЕКС()

Чтобы решить нашу проблему в один шаг, необходимо воспользоваться формулами ИНДЕКС и ПОИСКПОЗ. Сложность данного подхода заключается в том, что требуется применить две функции, которые, возможно, вы никогда не применяли до этого. Для упрощения понимания решим эту задачу в два этапа.

Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-нибудь говорит «индекс», у меня в голове не возникает ни единой ассоциации, чем же занимается эта функция. А требует она целых три аргумента: =ИНДЕКС(массив; номер_строки; [номер_столбца]).

Говоря по-простому, Excel идет в массив данных и возвращает значение, находящееся на пересечении указанной строки и столбца. Как будто бы просто. Таким образом, формула =ИНДЕКС($A$2:$C$6;4;2) вернет значение, находящееся в ячейке B5.

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

Функция ПОИСКПОЗ()

Синтаксис этой функции таков: =ПОИСКПОЗ(искомое_значение; просматриваемы_массив; [тип_сопоставления]).

Она говорит Excel: «Найди искомое_значение в массиве данных и верни номер строки массива, в которой это значение встречается». Таким образом, чтобы найти в какой строке находиться имя сотрудника в ячейке A10, необходимо прописать формулу =ПОИСКПОЗ(A10; $B$2:$B$6; 0). Если в ячейке A10 будет имя «Колин Фарел», тогда ПОИСКПОЗ вернет 5-ю строку массива B2:B6.

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

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

Использование ВПР в Экселе для подстановки значения

Добрый день уважаемый читатель!

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

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

Поскольку данные в таблицах размещены вертикально, нам нужно использовать функцию ВПР, для горизонтальных данных существует функция ГПР, но она менее популярна. Основная суть работы функции, это поиск в прайсе по названию товара и подстановка его цены в заказ. Получиться таблица такого вида: Для простоты использования данных в формуле, возможно, использовать присвоенное диапазону значений имя, но это уже на ваше усмотрение. Для назначения имени диапазона нужно выделить диапазон «G2:H8», исключив «шапку» таблицы, а потом, нажав горячую комбинацию клавиш CTRL+F3, в появившемся диалоговом окне «Диспетчер имён» создайте вашему диапазону новое имя, например «Прайс». Теперь приступим к использованию функции ВПР в Экселе для подстановки значения. Устанавливаем курсор на ячейку «C2» и с помощью мастера функций, в категории «Ссылки и массивы» выбираем нужную функцию. Появится диалоговое окно «Аргументы функции»: Теперь введем необходимые аргументы:

• Искомое значение – указываем или наименование необходимого товара, или ссылку на ячейку, где содержится искомый аргумент;
• – указываете таблицу, с которой будут изыматься необходимые данные, в нашем случае это таблица с прайсом, возможно вместо диапазона указать его название «Прайс»;
• Номер столбика – указываем, каким порядковым номером будет столбик, из которого необходимого достать данные с указанием цены товара. Номер столбика указывается только цифрами, а поскольку цены хранятся во втором столбике, так и указываем;
• Интервальный просмотр – этот аргумент может иметь только два параметра: ИСТИНА или ЛОЖЬ. Первый режим при значении ЛОЖЬ производит поиск исключительно точного соответствия значений, а в случае когда функция не найдёт нужного значения, то вернётся ошибка #Н/Д. При втором режиме, когда значение ИСТИНА, формула ищет приблизительное соответствие необходимого значения.

Теперь осталось только одно, нажать «ОК» и получить результат, а после, скопировать полученную формулу на весь диапазон таблицы.

Избавление от полученной ошибки #Н/Д

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

1. Возникает ошибка при указании аргумента «Интервальный просмотр» как ИСТИНА или 0, что требует наличия точного вхождения значения, а его то, как раз и нет. Для устранения этой проблемы, измените условия отбора;
2. Если указан аргумент «Интервальный просмотр» как ЛОЖЬ или 1, но таблица, в которой производится поиск, не отсортирована по возрастанию наименований, то ошибка будет неизбежна. Лекарство, как и в первом варианте;
3. В случаях, когда в наличии разные форматы ячеек, тех, откуда берется необходимое значение и тех где прописан аргумент поиска, например, текстовый и числовой форматы. Частенько эта ошибка возникает, когда нужно использовать числовые коды вместо текстовых значений, это номера счетов, номенклатурные номера и прочее. Для решения этой проблемы можно преобразовывать форматы данных с помощью функций ТЕКСТ и Ч. Результатом будет такая формула: =ВПР(ТЕКСТ(B2;);$G$2:$H$8;2;ЛОЖЬ);
4. Также в случае наличия невидимых непечатаемых знаков или лишних пробелов могут возникнуть ошибки результатов. Для исправления, в этом случае, нужно задействовать функции ПЕЧСИМВ и СЖПРОБЕЛЫ, чтобы убрать излишек ненужной пунктуации. Формула приобретёт следующий вид: =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B2));$G$2:$H$8;2;ЛОЖЬ).

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

=ЕСЛИОШИБКА(ВПР(B7;$G$2:$H$8;2;ЛОЖЬ);»»).

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

Не забудьте подкинуть автору на кофе…

Создание формулы подстановки с помощью мастера подстановок

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Обратите внимание для пользователей Office 2003 Чтобы продолжить получать обновления для системы безопасности для Office, убедитесь, что вы используете Office 2003 с пакетом обновления 3 (SP3). Поддержка Office 2003 заканчивается 8 апреля 2014 г. Если вы используете версию Office 2003 после окончание поддержки, для получения важных обновлений для Office, необходимо выполнить обновление до более поздней версии, например Office 365 или Office 2013. Дополнительные сведения читайте в статье прекращение поддержки Office 2003.

В выпусках Excel 2007 и Excel 2003 мастер подстановок создает формулы подстановки на основе данных листа с подписями строк и столбцов. Мастер подстановок позволяет находить остальные значения в строке, если известно значение в одном столбце, и наоборот. В формулах, создаваемых мастером подстановок, используются функции ИНДЕКС и ПОИСКПОЗ.

Мастер больше не учитываются в Excel 2010. Он был заменен мастером функций и доступны функции ссылки и поиска (Справка).

Использование мастера подстановок в Excel 2007

Щелкните ячейку в диапазоне.

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

Нажмите кнопку Microsoft Office , выберите пункт Параметры Excelи выберите категорию надстройки .

В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти.

В области Доступные надстройки установите флажок рядом с пунктом Мастер подстановок и нажмите кнопку ОК.

Следуйте указаниям мастера.

К началу страницы

Использование мастера подстановок в Excel 2003

В меню Сервис выберите пункт Надстройки, щелкните поле Мастер подстановок, а затем нажмите кнопку ОК.

Щелкните ячейку в диапазоне.

В меню Сервис выберите пункт Подстановка.

Следуйте инструкциям мастера.

Что произошло с мастером подстановок в Excel 2010?

Мастер больше не учитываются в Excel 2010. Эта функция был заменен мастером функций и доступны функции ссылки и поиска (Справка).

Формулы, созданные с помощью этого мастера, будут действовать в Excel 2010. Их можно изменять другими способами.

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

Кому лень или нет времени читать — смотрим видео. Подробности и нюансы — в тексте ниже.

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

Итак, имеем две таблицы — таблицу заказов и прайс-лист:

Задача — подставить цены из прайс-листа в таблицу заказов автоматически, ориентируясь на название товара с тем, чтобы потом можно было посчитать стоимость.

Решение

В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP) . Эта функция ищет заданное значение (в нашем примере это слово «Яблоки») в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:

Для простоты дальнейшего использования функции сразу сделайте одну вещь — дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме «шапки» (G3:H19), выберите в меню Вставка — Имя — Присвоить (Insert — Name — Define) или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы — Вставка функции (Formulas — Insert Function) . В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:

Заполняем их по очереди:

• Искомое значение (Lookup Value) — то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае — слово «Яблоки» из ячейки B3.
• Таблица (Table Array) — таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя «Прайс» данное ранее. Если вы не давали имя, то можно просто выделить таблицу, но не забудьте нажать потом клавишу F4 , чтобы закрепить ссылку знаками доллара , т.к. в противном случае она будет соскальзывать при копировании нашей формулы вниз, на остальные ячейки столбца D3:D30.
• Номер_столбца (Column index number) — порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.
• Интервальный_просмотр (Range Lookup) — в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:
  • Если введено значение или ЛОЖЬ (FALSE) , то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, «Кокос»), то она выдаст ошибку #Н/Д (нет данных).
  • Если введено значение 1 или ИСТИНА (TRUE) , то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия, т.е. в случае с «кокосом» функция попытается найти товар с наименованием, которое максимально похоже на «кокос» и выдаст цену для этого наименования. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле! Так что для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать. Исключением является случай, когда мы ищем числа, а не текст — например, при расчете Ступенчатых скидок.

Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.

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

Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если:

• Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.
• Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.
• Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
  =ВПР(ТЕКСТ(B3);прайс;0)
• Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:
  =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)
  =VLOOKUP(TRIM(CLEAN(B3));прайс;0)

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

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