Впр что это эксель: инструкция на примере / Skillbox Media

Что такое впр в excel

ITGuides.ru

Вопросы и ответы в сфере it технологий и настройке ПК

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

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

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

Что такое ВПР в Excel?

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

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

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

Как работает ВПР?

Пример задания аргументов функции ВПР в экселе

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

Если последний параметр «отсортировано» опущен, то есть равен 1, что означает, что столбец является отсортированным, при работе эта функция остановит сортировку данных на строке, предшествующей искомой. Если она не задана, то она возвращает последнюю строку.

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

Примечательно, что Excel работает с разными типами данных, а не только с числовыми. ВПР в состоянии найти также текстовое значение. В этом случае существенным минусом, влияющим на качество поиска, могут стать лишние пробелы начала строки. Из-за них ВПР способна вернуть неправильное или необычное значение. Чтобы предотвратить искажение информации, используйте соответствующий параметр Excel для устранения пробелов. Таким образом, функция ВПР является очень лояльной и работает в сотрудничестве с другими, например, с СЖПРОБЕЛЫ и другими.

Кроме того, может случиться так, что в сравнении будут участвовать как целые, так и дробные значение. Здесь проблема заключается в разделительном знаке — точка или запятая. Если они различны, то выборку сделать окажется весьма сложно. Примените возможность Эксель «найти и заменить», что найдете на панели инструментов.

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

Естественно, такой же поиск необходимого значения можно выполнить вручную, только сколько времени потребуется для этого? Не менее 20 минут для документа, имеющего около 100 строк. А ведь в документе может быть тысяча строк, и не одна тысяча, а десятки. Тогда ручной способ займет очень много времени, сил. ВПР позволяет сделать такой поиск за интервал времени чуть более одной секунды. Экономия времени большая, а собственные трудозатраты минимальны.

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

Функция ВПР незаменима при необходимости объединить данные из разных таблиц, имеющих какое-либо общее поле (наименование, штрих-код и т.д.)

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

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

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

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

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

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

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

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

1. Приведем первую таблицу в нужный нам вид. Добавим столбцы «Цена» и «Стоимость/Сумма». Установим денежный формат для новых ячеек.
2. Выделяем первую ячейку в столбце «Цена». В нашем примере – D2. Вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или нажав комбинацию горячих клавиш SHIFT+F3. В категории «Ссылки и массивы» находим функцию ВПР и жмем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и выбрать из выпадающего списка «Ссылки и массивы».
3. Откроется окно с аргументами функции. В поле «Искомое значение» — диапазон данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен найти во второй таблице.
4. Следующий аргумент – «Таблица». Это наш прайс-лист. Ставим курсор в поле аргумента. Переходим на лист с ценами. Выделяем диапазон с наименованием материалов и ценами. Показываем, какие значения функция должна сопоставить.
5. Чтобы Excel ссылался непосредственно на эти данные, ссылку нужно зафиксировать. Выделяем значение поля «Таблица» и нажимаем F4. Появляется значок $.
6. В поле аргумента «Номер столбца» ставим цифру «2». Здесь находятся данные, которые нужно «подтянуть» в первую таблицу. «Интервальный просмотр» — ЛОЖЬ. Т.к. нам нужны точные, а не приблизительные значения.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Добавляем в таблицу крайний левый столбец (важно!), объединив «Поставщиков» и «Материалы».
2. Таким же образом объединяем искомые критерии запроса:
3. Теперь ставим курсор в нужном месте и задаем аргументы для функции: . Excel находит нужную цену.

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

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

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

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

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

1. Ставим курсор в ячейку Е8, где и будет этот список.
2. Заходим на вкладку «Данные». Меню «Проверка данных».
3. Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.
4. Когда нажмем ОК – сформируется выпадающий список.

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

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

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

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

Функция ВПР в экселе

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

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

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

Рассмотрим структуру ВПР, какие аргументы она задействует. Как и любая другая функция в Excel начинается она со знака равенства (=). Далее имя функции и аргументы, заключенные в круглые скобки.

ВПР содержит 4 аргумента.

Функция ведет поиск искомого значения в крайнем левом столбце и производит возврат значения в той же строке из указанного столбца.

Вторым — указывается именно та таблица (или диапазон ячеек), в которой следует произвести этот поиск.

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

Последний аргумент – интервальный просмотр, здесь может быть 2 значения: 0 – ЛОЖЬ, 1 — ИСТИНА. отвечает за точный поиск (совпадения при просмотре сверху вниз). Если ничего не находит, то возвращается ошибка Н/Д (нет данных), 1 – приблизительный.

Для лучшего понимания принципа работы функции рассмотрим пример использования ВПР.

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

Первый простой пример – имеются 2 таблицы. В одной указываются Товары и их идентификаторы (ID). Во второй, с помощью фильтра по ID, мы хотим получить наименование товара.

После знака равно вводим ВПР, затем Enter и Fx для ввода аргументов.

Аргументы также можно вводить в соответствующей строке, перечисляя их через точку с запятой.

Искомыми будут значения, по которым будем искать совпадения. В данном случае это ячейка E1.

Для второго аргумента выделяем диапазон таблицы.

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

Номер столбца – то, что нужно вернуть. В этом примере требуется вернуть 2 столбец (Товар). Для точного поиска 4 аргумент – .

Введя все значения, жмём кнопку ОК.

Теперь при изменении в фильтре номера ID будет изменяться наименование товара.

Теперь посмотрим другой пример.

Теперь нужно получить партию для каждого наименования товара по критерию Количество.

Например, для мелкой партии количество должно быть от 100 до 200, средней – 200-300 и т.д.

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

Как видим в полученной таблице для количества, например, 110, партию выдало Мелкая (ближайшее меньшее 100) и т. д. Обязательно сортировка критериев должна быть от большего к меньшему, иначе ВПР не сработает.

Как пользоваться ВПР в Excel — примеры

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

Что такое ВПР в Excel?

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

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

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

Совет: научиться правильно применять функцию VLOOKUP чуть сложнее, чем построить диаграмму в Excel; если планируется однократный поиск по единственной или нескольким строкам таблицы, проще будет воспользоваться комбинацией клавиш Ctrl + F и обработать данные вручную.

Как пользоваться ВПР в Excel?

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

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

Пример №1

Имеется сводная таблица с наименованиями товаров, датами и объёмами поставок и сроками годности. Требуется, не прибегая к опции поиска, вывести в любую свободную ячейку информацию о конкретном продукте.

Решение:

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

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

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

• Выделить нажатием ячейку, следующую за наименованием товара, и перейти на вкладку Excel «Формулы».

• Щёлкнуть по кнопке «Вставить функцию» — самой крайней слева в открывшейся «ленте».

• В открывшемся окошке вызвать верхний выпадающий список и выбрать кликом мыши пункт «Полный алфавитный перечень».

• Пролистать перечень вниз, выделить строчку «ВПР» и щёлкнуть по кнопке «ОК».

• Другой способ вызвать функцию VLOOKUP — на той же вкладке «Формулы» открыть выпадающий список «Ссылки и массивы» и выбрать пункт «ВПР».

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

• Во второй строчке того же окна — зажав левую клавишу мыши, выделить исходную таблицу Excel целиком.

• В третьей — порядковый номер столбца, в котором требуется осуществить поиск…

• …Или сам столбец, выделив его аналогичным образом.

• В четвёртой — указать, требуется точный поиск по таблице Excel (значение «0») или программа должна будет подобрать ближайший подходящий параметр (значение «1»). В этом примере и при решении большинства других задач, связанных с применением функции ВПР, нужны точные совпадения — следовательно. Необходимо вписать в текстовое поле «0», а затем нажать на «ОК».

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

• В нашем примере в результате должно получиться не абсолютное значение, а дата. Чтобы привести данные в требуемый вид, необходимо щёлкнуть по «неправильной» ячейке правой кнопкой мыши и выбрать в открывшемся контекстном меню пункт «Формат ячеек»…

• …После чего найти требуемый — «Дата» — и кликнуть по кнопке «ОК».

• Готово! Ячейка приобрела приемлемый для работы вид — таким он и будет оставаться вплоть до нового изменения.

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

• Чтобы добиться требуемого результата, следует или повторить для новой строчки все перечисленные манипуляции, или выделить уже готовую и скопировать в верхнем текстовом поле Excel формулу…

• …После чего вставить её в нужную строку…

• …Не забыв заменить номер столбца, в котором на этот раз должен быть осуществлён поиск — теперь это не «3», а «4».

• Нажав на клавишу Enter и при необходимости вновь подкорректировав формат ячейки, пользователь получит заполненную краткую таблицу с достоверной информацией из большой сводной.

• Теперь при изменении значения в исходной таблице оно автоматически будет обновляться в новой, что крайне удобно для отслеживания корректировок в реальном времени.

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

• После чего, вновь вызвав его, выбрать пункт «Специальная вставка», отметить кружочком в разделе «Вставить» пункт «Значения» и кликнуть по кнопке «ОК».

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

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

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

Пример №2

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

Решение:

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

• Теперь нужно добавить к одной из таблиц Excel (обычно — более содержательной) новый столбец и назвать его в соответствии с переносимыми данными.

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

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

• Во второй — вставить выделением всю вторую таблицу, включая названия столбцов и строк.

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

• Щёлкнув по кнопке «ОК», пользователь получит в первой строке искомый результат.

• Теперь не нужно подставлять формулу в каждую строку — достаточно зажать левой клавишей мыши крестик в правом нижнем углу ячейки и «растянуть» её на все позиции — данные будут подставлены автоматически и в нужном порядке.

• Соответствия сохранятся даже при пересортировке строк в первой таблице.

• А кроме того, в неё не будут включены «лишние» данные, отсутствующие во второй.

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

Вопросы от новичков

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

Как сравнить таблицы с помощью ВПР?

Сравнение двух и более таблиц проводится почти так же, как добавление нового ряда данных:

• Открыть обе таблицы, а при необходимости — перенести их на один лист.

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

• Вставить в первую ячейку под заголовком нового столбца формулу ВПР, как было описано выше. В первой строчке диалогового окна указывается наименование товара из первой таблицы, во второй — вносится вся вторая таблица, включая заголовки, в третий — номер ряда, который нужно сравнить, а в четвёртый — снова значение «0».

• Когда в ячейке появится требуемой значение, достаточно «растянуть» его на все позиции — данные подставятся автоматически.

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

Как с помощью ВПР сделать выпадающий список?

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

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

• Перейти на вкладку «Данные» и найти в «ленте» инструмент «Проверка данных».

• Щёлкнув по нему, вызвать выплывающее меню, а далее — кликнуть по одноимённой строчке.

• Выбрать в новом выплывающем меню «Тип данных» пункт «Список».

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

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

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

Подводим итоги

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

Усовершенствуем функцию ВПР в Excel / Хабр

Прочтение публикации Упрощаем бинарный поиск в Excel сподвигло на дополнительное усовершенствование функции ВПР по сравнению с приведенным в статье.

Что не было учтено, и что хотелось бы добавить:

1. Универсальность, т.е. возможность вызывать функцию как для отсортированного массива, так и для неотсортированного.

2. Исключить необходимость два раза вызывать функцию бинарного поиска (ВПР).

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

4. Сделать защиту от дурака — проверять передаваемые аргументы на корректность.

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

' VPR - улучшенная версия функций ВПР и ГПР (VLookup & HLookup)
' key - искомое значение (индекс)
' a - массив для поиска индекса
' b - массив такой же формы, для выдачи значения
' Ordered - указывает тип упорядочения массива: 1 - по возрастанию, 0 - не упорядочен, -1 - упорядочен по убыванию
' NotStrict - указывает, нужно точное или приблизительное значение: False - точное, True - достаточно приблизительного.
' Если массив не упорядочен (Ordered = 0), то всегда возвращается точное значение
Function VPR(key As Variant, ByRef a As Range, ByRef b As Range, Optional Ordered As Integer = 0, Optional NotStrict As Boolean = False) As Variant
    ' проверяем корректность аргументов a и b - должны быть линейными, из одной области и 
    ' с одинаковым ненулевым количеством элементов
    If (b. Areas.Count <> 1) Or ((b.Columns.Count > 1) And (b.Rows.Count > 1)) Then
        VPR = CDbl("")
        Exit Function
    End If
    If (a.Areas.Count <> 1) Or ((a.Columns.Count > 1) And (a.Rows.Count > 1)) Then
        VPR = CDbl("")
        Exit Function
    End If
    If (a.Count <> b.Count) Or (a.Count < 1) Then
        VPR = CDbl("")
        Exit Function
    End If
    If Ordered = 0 Then
        NotStrict = False
    End If
    Dim index As Long
    index = Application.WorksheetFunction.Match(key, a, Ordered)
    If (Not NotStrict) And (a(index).value <> key) Then
        VPR = CDbl("")
    Else
        VPR = b(index).value
    End If
End Function

Дополнительные плюшки

1. Возможность поиска в массиве, отсортированном по убыванию (Ordered = -1).
2. Функция позволяет делать поиск если любой (или оба) из аргументов a и b являются горизонтальными рядами (т.е. обобщает и функцию горизонтального просмотра ГПР).

Комментарии

1. Вызов CDbl(«») нужен для генерации ошибки (выдача #ЗНАЧ#).
2. Используется не ВПР, а функция Match (русский аналог — ПОИСКПОЗ).

Спасибо за внимание!

Что такое Excel? Обзор для начинающих

Изучите 5 обязательных для изучения «основ» Excel

Начать работу с Excel очень просто. Подпишитесь на наш 5-дневный мини-курс, чтобы получить простых уроков по использованию основных электронных таблиц.

• Основы строк, столбцов и ячеек…
• Как сортировать и фильтровать данные как про…
• Кроме того, мы расскажем , почему формулы и ссылки на ячейки так важны и как их использовать…

Имя

Адрес электронной почты

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

Спасибо, не надо! Верните меня к статье.

Microsoft Excel — это программа, входящая в пакет Microsoft Office. Он используется для создания электронных таблиц, которые представляют собой документы, в которых данные расположены в строках и столбцах — как в большой таблице.

Благодаря своей чрезвычайной универсальности и мощности Excel стал одной из наиболее часто используемых программ в деловом мире с момента своего запуска в 1985 году. Действительно, ренессанс персональных компьютеров в 1980-х и 1990-х годах был в значительной степени обусловлен многочисленными видами использования Excel и другие программы для работы с таблицами.

Что такое электронная таблица?

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

Как видите, документ состоит из столбцов (вертикальные наборы полей с метками A, B, C и т. д. в верхней части экрана) и строк (горизонтальные наборы полей с метками 1, 2, 3). и т. д. в нижней части экрана). На пересечении каждой строки и столбца находится ячейка, в которую пользователь может вводить числа или текст. Адрес любой заданной ячейки генерируется путем объединения буквы столбца ячейки с номером строки ячейки. Например, ячейка, выделенная на диаграмме ниже, имеет адрес B4, потому что она находится на пересечении столбца B и строки 4. Когда мы обращаемся к этой ячейке словесно, мы называем ее «Ячейка B4».

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

Для чего используется Excel?

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

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

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

Вот несколько примеров того, как компания может использовать Excel. Помните, однако, что это всего лишь примеры. Excel бесконечно гибок, поэтому возможности безграничны:

• Отслеживайте продажи клиентов из месяца в месяц;
• Мониторинг платежей клиентов, чтобы убедиться, что никто не задерживается;
• Следите за расходами и распределяйте их по конкретным сотрудникам;
• Рассчитать количество отработанных часов на одного работника для ежемесячной заработной платы;
• Расчет ежемесячных платежей по офисной ипотеке;
• Создание графиков или диаграмм для объяснения эффективности компании с течением времени;
• Оценка ежемесячных продаж на следующие три года на основе исторических данных; и
• Централизованно назначайте проекты и задачи сотрудникам.

Как изучить Excel

Теперь, когда вы знаете, что такое Excel, пришло время научиться им пользоваться! Начните с нашей статьи о том, как использовать Excel, и начните использовать возможности этого удивительного инструмента.

Изучите 5 обязательных для изучения «основ» Excel

Начать работу с Excel очень просто. Подпишитесь на наш 5-дневный мини-курс, чтобы получить простых уроков по использованию основных электронных таблиц.

• Основы строк, столбцов и ячеек…
• Как сортировать и фильтровать данные как про…
• Кроме того, мы расскажем почему формулы и ссылки на ячейки так важны и как их использовать…

Имя

Адрес электронной почты

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

Комментарии

Пожалуйста, включите JavaScript для просмотра комментариев.

• Начало работы
• Что такое Excel? Обзор для начинающих
• Как использовать Excel: наши 10 вещей, которые нужно изучить
• Основы работы с книгами и таблицами Excel
• Работа со строками, столбцами и ячейками
• Как расположить в Excel по алфавиту: Полное руководство по сортировке
• Как фильтровать данные с помощью автофильтров в Excel
• Замораживание областей в Excel
• Числовые форматы в Excel
• Форматирование ячеек в Excel
• Использование команды «Специальная вставка»
• Использование таблиц в Excel
• Основы функций Excel
• Математические функции
• Дата и время
• Строковые функции
• Функции поиска
• Логические функции
• Диаграммы и графики
• Сводные таблицы
• Расширенное форматирование
• Артикул
• Блог

Изучите 5 обязательных для изучения «основ» Excel

Начать работу с Excel очень просто. Запишитесь на наш 5-дневный мини-курс, чтобы получить простых уроков по использованию основных электронных таблиц.

Имя

Адрес электронной почты

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

Знакомство с анализом «что, если» — Служба поддержки Майкрософт

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

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

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

Анализ «что, если» — это процесс изменения значений в ячейках, чтобы увидеть, как эти изменения повлияют на результаты формул на листе.

Три типа инструментов анализа «что, если» поставляются с Excel: Сценарии , Поиск цели и Таблицы данных . Таблицы сценариев и данных принимают наборы входных значений и определяют возможные результаты. Таблица данных работает только с одной или двумя переменными, но может принимать множество различных значений для этих переменных. Сценарий может иметь несколько переменных, но не более 32 значений. Поиск цели работает иначе, чем сценарии и таблицы данных, поскольку он берет результат и определяет возможные входные значения, которые дают этот результат.

В дополнение к этим трем инструментам можно установить надстройки, помогающие выполнять анализ «что если», например надстройку Solver. Надстройка Solver похожа на Goal Seek, но может вмещать больше переменных. Вы также можете создавать прогнозы, используя дескриптор заполнения и различные команды, встроенные в Excel.

Для более продвинутых моделей можно использовать надстройку Analysis ToolPak .

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

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

1. Изменение ячеек

2. Ячейка результата

1. Изменение ячеек

2. Ячейка результата

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

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

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

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

Ячейки B1, B2 и B3 содержат значения суммы кредита, срока и процентной ставки.

В ячейке B4 отображается результат формулы =ПЛТ(B3/12,B2,B1) .

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

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

Ячейка B3 содержит введенное значение.
Ячейки C3, C4 и C5 представляют собой значения, которые Excel заменяет на основе значения, введенного в ячейку B3.

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

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

Можно ввести ряд значений, соответствующих простому линейному тренду или тренду экспоненциального роста, с помощью маркера заполнения или команды Series . Чтобы расширить сложные и нелинейные данные, вы можете использовать функции рабочего листа или инструмент регрессионного анализа в Надстройка Analysis ToolPak.

Хотя Goal Seek может учитывать только одну переменную, вы можете проецировать назад больше переменных с помощью надстройки Solver . Используя Solver, вы можете найти оптимальное значение формулы в одной ячейке, называемой целевой ячейкой, на рабочем листе.

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