Подстановка данных в excel

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

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

Обратите внимание для пользователей 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. Их можно изменять другими способами.

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

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

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

Для большей информативности рассмотрим данный вопрос на примере. Итак, у нас есть 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;ЛОЖЬ);»»).

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

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

Поиск и подстановка по нескольким условиям

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

Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно 🙂 — без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?

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

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

Способ 1. Дополнительный столбец с ключом поиска

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

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

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы : Простой способ, знакомая функция, работает с любыми данными.

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

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS) , появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

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

Минусы : Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

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

1. Выделите пустую зеленую ячейку, где должен быть результат.
2. Введите в строке формул в нее следующую формулу:

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы : Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

Таблицы подстановки в Excel

Для анализа данных при выборе оптимального варианта финансового решения зачастую применяются Таблицы подстановки в Excel.Они позволяют проводить анализ изменения результата при произвольном диапазоне исходных данных. На одном рабочем листе можно расположить несколько таблиц подстановок. Это дает возможность одновременно анализировать различные формулы и статистические данные. Данный пример подходит для версий программы Microsoft Office Excel версий 2007, 2010 и 2013.

Таблицы подстановки данных можно использовать для

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

Изменения двух исходных значений, просматривая результаты только одной формулы. При использовании таблицы с двумя переменными значениями одно из них располагается в столбце, другое — в строке; результат вычислений получают на пересечении строки и столбца.

На конкретном примере

Задание: использование таблицы подстановки с одной изменяющейся переменной и несколькими формулами. Рассчитать ежемесячные выплаты по займу и платежи по процентам. Исходные данные приведены на рисунке ниже.

Порядок работы

• Запустите MicrosoftExcel и создайте новую электронную книгу.
• Создайте таблицу ежемесячных выплат по займу и платежей по процентам по образцу.

Таблица — заготовка для решения

=ПЛТ ($В$4/12;$В$3*12;$В$2). Ежемесячная выплата составит 10178,42 р.

• Расчет платежей по процентам происходит с помощью функции ПРОЦПЛАТ (). В ячейку D6 введите формулу:

=ПРОЦПЛАТ ($B$4;$D$5;$D$3;$D$2). Платежи по процентам составят 1350 р.

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

• После подготовки исходных данных перейти к тому месту рабочего листа, где будут располагаться рассчитываемые значения в зависимости от изменения одной переменной и от различных рассчитываемых формул (смотрите рисунок ниже). В верхней строке будущей таблицы над местом расположения результатов указать адрес каждой формулы, для которой нужно получить список результатов. Вместо адреса можно поместить в ячейку саму формулу (ячейки В9 и С9 содержат формулы). Слева расположить различные значения исходных данных, которые необходимо протестировать. Подготовку исходного диапазона данных — различных величин процентной ставки в рассматриваемом примере можно осуществить с помощью маркера автозаполнения.
• Выделить ячейки, которые будут содержать таблицу. При этом самым левым столбцом таблицы должен быть столбец исходных значений, а в самой верхней строкой должна быть строка анализируемых формул.

Расчет платежей

• Далее вам нужно выделить диапазон ячеек A9:C18 , после чего перейти на вкладку данные, «анализ что-если» таблица данных. Первое поле «подставлять значения по столбцам в» оставить пустым, а в поле «подставлять значения по строкам в» указать ячейку с величиной процентной ставки зафиксировав ее знаками доллара $B$4.

Подстановка данных

Результат