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

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

​Смотрите также​«?» - заменяет любой​ИНТЕРВАЛЬНЫЙ ПРОСМОТР – точное​ сверху, снизу, справа.​ отмечается неправильная задача.​Параметры функции при этом​ рисунке показан список​При применении 1 или​ Для этого вручную​ искомым значением является​ На нашем сайте​ четвертый аргумент, который​ человека по его​ ответ:​ примере это​ зато освоение этого​ соответствующее значение. Возвращаемся​

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

​ или приблизительное значение​ Ячейки потребуется расширять,​ То есть в​ означают следующее:​ с кличками животных​ ИСТИНЫ в четвертом​ проставляются знаки $​ сумма его продаж.​ ей посвящен целый​ позволяет задавать так​ имени. Если же​9.99​Photo frame​ инструмента сэкономит вам​ к окну аргументов​ подразумевает подтягивание в​ или цифровой информации;​

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

​ должна найти функция​ чтобы найти данные.​ любых нарушениях виноват​

​А1. Это приблизительная ссылка​ и их принадлежность​ аргументе нужно следить,​ перед буквенными и​В результате работы функции​ раздел с множеством​ называемый интервальный просмотр.​ в списке контактов​.​. Так как аргумент​ массу времени при​ функции.​ неё значений из​«*» - для замены​ (ЛОЖЬ/0 – точное;​ Так как они​ только пользователь. Есть​ на ячейку. В​ к определенному виду.​ чтобы столбец с​ численными значениями адресов​ ВПР (VLOOKUP) формируется​ самых интересных уроков!​ Он может иметь​ есть столбец с​

1. ​Давайте разберёмся, как работает​ текстовый, мы должны​​ работе с таблицами.​​Точно таким же образом​ других таблиц. Если​ любой последовательности символов.​​ ИСТИНА/1/не указано –​​ располагаются в своих​ три распространённые ошибки.​

   

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

   

3. ​ заключить его в​Автор: Максим Тютюшев​ кликаем по значку​ таблиц очень много,​Найдем текст, который начинается​ приблизительное).​ столбцах, то их​ Во-первых, человек часто​ любое значение, в​

   

4. ​ новая таблица, в​ отсортирован по возрастанию.​ правых ячеек таблицы.​​ которой конкретному искомому​​Прикладная программа Excel популярна​ и ЛОЖЬ. Причем,​ или названием компании,​ делом она ищет​

   

5. ​ кавычки:​Многие наши ученики говорили​ справа от поля​ ручной перенос заберет​ или заканчивается определенным​! Если значения в​

   

6. ​ потребуется минимум две.​ путается в аргументах​ зависимости от результата,​ которой легко найти​ При использовании 0​ В нашем случае​

   

7. ​ менеджеру быстро сопоставляется​ благодаря своей доступности​ если аргумент опущен,​ Вы можете искать​ заданное значение в​=VLOOKUP("Photo frame"​ нам, что очень​ ввода данных, для​ огромное количество времени,​​ набором символов. Предположим,​​ диапазоне отсортированы в​ Если параметров поиска​​ «ложь» и «истина».​​ который пользователь хочет​ по кличке животного​ или ЛЖИ данная​ формула принимает вид:​

   

8. ​ его сумма продаж.​​ и простоте, так​​ то это равносильно​ и эти данные,​ первом столбце таблицы,​=ВПР("Photo frame"​ хотят научиться использовать​ выбора таблицы, откуда​ а если данные​ нам нужно отыскать​ возрастающем порядке (либо​ больше, то и​ Первый ориентирован на​ получить.​​ его вид. Актуальны​​ необходимость отпадает, но​
9. ​ =ВПР (С1; $А$1:$В$5;​​Расположена формула ВПР во​​ как не требует​ истине.​​ просто изменив второй​​ выполняя поиск сверху​​Второй аргумент​​ функцию​ будут подтягиваться значения.​ постоянно обновляются, то​ название компании. Мы​ по алфавиту), мы​ количество ячеек увеличивается.​ поиск точного совпадения.​База_данных. Имя той области​ подобные поисковые системы​ также отсутствует тогда​ 2; 0).​ вкладке "Мастер функций"​ особых знаний и​​В случае, когда четвертый​​ и третий аргументы,​ вниз (вертикально). Когда​​– это диапазон​​ВПР​

​Выделяем всю область второй​ это уже будет​ забыли его, но​ указываем ИСТИНА/1. В​ Затем осуществляется проверка​ Если указывать «истина»,​ информации, которую предстоит​ при работе с​ возможность интервального просмотра.​Функция ВПР не работает,​ и разделе "Ссылки​ навыков. Табличный вид​

​ аргумент имеет значение​ как мы уже​ находится значение, например,​ ячеек, который содержит​(VLOOKUP) в Microsoft​

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

​ и массивы". Диалоговое​

lumpics.ru

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

​ предоставления информации понятен​ ИСТИНА, функция сначала​ делали в предыдущем​Photo frame​​ данные. В нашем​​ Excel.​ производиться поиск значений,​​ счастью, существует функция​​ с Kol. С​ ЛОЖЬ/0.​ то, насколько верно​ приблизительные.​ настолько обширное, как​ того чтобы вручную​ особенно важно сортировать​ сообщение в столбце​​ окно функции имеет​​ любому пользователю, а​ ищет точное соответствие,​ примере. Возможности Excel​, функция переходит во​

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

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

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

​ не может обозначаться​ использовать только по​​ записи, можно быстро​​ функция ВПР будет​​ ошибке (#N/A или​​Аргументы в формулу вносятся​ включающих "Мастер функции",​ нет, то ближайшее,​​Урок подготовлен для Вас​​ найти цену.​A2:B16​ полезный инструмент, а​​ аргументов функции.​​ данных. Давайте рассмотрим​Нам нужно отыскать название​ таблицу с данными:​ «просмотр значений». Можно​ так, чтобы поиск​

​ первым строкам или​ воспользоваться поиском и​​ выводить в ячейки​​ #Н/Д). Это происходит​ в порядке очереди:​ позволяет проводить любые​ которое меньше чем​ командой сайта office-guru.ru​ВПР​. Как и с​ научиться с ним​Для того, чтобы выбранные​ конкретные примеры работы​ компании, которое заканчивается​Формула​ выявить несоответствия в​ начинался со второго​

Добавляем аргументы

​ неправильные данные.​ в таких случаях:​​Искомое значение - то,​​ манипуляции и расчеты​ заданное. Именно поэтому​

​Источник: http://www.gcflearnfree.org/excel-tips/how-to-use-excels-vlookup-function/full​​– сокращение от​ любой другой функцией​ работать проще, чем​ значения сделать из​​ этой функции.​​ на - "uda".​Описание​ формуле.​ или последующего столбца.​

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

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

​ озаглавить диапазон таблицы,​​ заполнен (в данном​ которого являются значения​ формул Excel является​возвратила фамилию «Панченко».​Используя функцию​ПР​​ аргументами (запятая в​​ по работе с​​ чтобы значения не​​Название функции ВПР расшифровывается,​Найдем компанию, название которой​ F5 в диапазоне​ аналоге применяется одинаково.​ Поэтому если пользователь​ЛОЖЬ. Указывает на поиск​ программе, которая отличается​ в которой проводится​​ случае колонка С).​​ ячейки, ее адрес,​

​ВПР​​осмотр,​​ англоязычной версии Excel​​ функцией​ сдвинулись при последующем​ как «функция вертикального​ начинается на "Ce"​​ А2:С10 и возвращает​​ Но есть пара​​ вводит какую-то другую​​ точного совпадения. Иногда​​ красотой и простотой.​​ поиск (второй аргумент),​В столбец С внесено​ имя, заданное ей​ функции ВПР на​ задали «008», то​при работе в​VLOOKUP​ или точка с​ВПР​ изменении таблицы, просто​ просмотра». По-английски её​​ и заканчивается на​​ значение ячейки F5,​ советов от профессионалов.​ формулу, отличающуюся от​

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

Как работает функция ВПР?

​– от​ запятой – в​разжеваны самым доступным​ выделяем ссылку в​ наименование звучит –​ –"sef". Формула ВПР​ найденное в 3​ Чтобы функция работала​​ правил, то программа​​ параметры этого слова.​ различных применений, её​ на рисунке.​

​ в колонке А​​ случае - это​​ довольно сложным, но​​ бы «Панченко».​​ извлекать требуемую информацию​​V​​ русифицированной версии).​​ языком, который поймут​​ поле​​ VLOOKUP. Эта функция​​ будет выглядеть так:​​ столбце, точное совпадение.​

​ лучше, особенно после​ просто не сможет​ И программа при​ используют в абсолютно​

​ фамилия и имя​

Другой пример

​ из электронных таблиц.​ertical​=VLOOKUP("Photo frame",A2:B16​ даже полные «чайники».​«Таблица»​ ищет данные в​ .​Нам нужно найти, продавались​ изменения данных, рекомендуется​

​ её распознать.​ этом будет искать​ разных сферах: начиная​ таблицы продаж озаглавлена.​ данных). Для проверки​​ менеджера.​​При работе с формулой​ аргумент функции​ Для этих целей​LOOKUP​​=ВПР("Photo frame";A2:B16​​ Итак, приступим!​, и жмем на​

​ ли 04.08.15 бананы.​​ вводить значок доллара​​И, в-третьих, нередко неправильно​ все совпадения и​​ от обучения и​​ Для этого выделяется​​ наличия искомого значения​​Таблица - диапазон строк​

​ ВПР следует учитывать,​ВПР​ Excel предлагает несколько​.​

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

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

​ все те же​ слово «Найдено». Нет​
​ А$1$. Когда вбиваются​
​ В этом случае​

​ может работать более​

office-guru.ru

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

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

​ (слева под панелью​​ - "Найти" вставить​​ порядковое число, в​ а не по​ на рисунке ниже​ распространенная. В этом​ изменить первый аргумент:​указанного диапазона. В​ на раздел​ доллара и она​ позволяет переставлять значения​

Пример 1

​У нас есть данные​Если «бананы» сменить на​ никаких символов и​Об этом стоит поговорить​ пользователю нужно держать​ в одной или​ вкладок) присваивается ей​

​ данную запись, запустить​​ котором располагается сумма​​ строкам. Для применения​ формула вернет ошибку,​

​ уроке мы познакомимся​=VLOOKUP("T-shirt",A2:B16,2,FALSE)​​ этом примере функция​​Формулы и функции​ превращается в абсолютную.​ из ячейки одной​ о продажах за​ «груши», результат будет​ пробелов между названиями​ детально. Ни для​ в голове (номер​ нескольких таблицах. Так​ название.​ поиск. Если программа​ продаж, то есть​​ функции требуется минимальное​​ поскольку точного соответствия​ с функцией​

Пример 2

​=ВПР("T-shirt";A2:B16;2;ЛОЖЬ)​ будет искать в​нашего самоучителя по​В следующей графе​ таблицы, в другую​ январь и февраль.​ «Найдено»​

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

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

​ - подразумевает выделение​ значит оно отсутствует.​Интервальный просмотр. Он вмещает​ два, максимальное отсутствует.​Если четвертый аргумент функции​, а также рассмотрим​=VLOOKUP("Gift basket",A2:B16,2,FALSE)​A​ВПР​​нам нужно указать​​ пользоваться функцией VLOOKUP​ сравнить с помощью​ может найти значение,​Также рекомендуется тщательно проверять​ разных сферах используется​

​ он не сможет​ минимум времени.​​ диапазона данных, потом​​Форматы ячеек колонок А​ значение либо ЛОЖЬ,​Функция ВПР производит поиск​ВПР​ ее возможности на​=ВПР("Gift basket";A2:B16;2;ЛОЖЬ)​значение​

​работает одинаково во​​ номер того столбца,​​ в Excel.​ формул ВПР и​ она выдает сообщение​ таблицу, чтобы не​ функция ВПР Excel.​ найти нужную ему​Функция ВПР Excel –​​ переход в меню​​ и С (искомых​ либо ИСТИНА. Причем​

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

Горизонтальный ВПР в Excel

​ ГПР. Для наглядности​ об ошибке #Н/Д.​​ было лишних знаков​​ Инструкция по её​ информацию.​​ что это такое?​​ "Вставка"- "Имя"- "Присвоить".​ критериев) различны, например,​ ЛОЖЬ возвращает только​ может иметь любой​ или опущен, то​​Функция​​ потруднее, готовы? Представьте,​. Иногда Вам придётся​ она работает даже​ значения. Этот столбец​ ВПР на конкретном​ мы пока поместим​ Чтобы этого избежать,​ препинания или пробелов.​

​ применению может показаться​Чтобы говорить о том,​ Её также называют​Для того чтобы использовать​

​ у одной -​ точное совпадение, ИСТИНА​

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

​ данные, размещенные на​​ текстовый, а у​ - разрешает поиск​ денежный, по дате​ должен быть отсортирован​(вертикальный просмотр) ищет​

​ появился третий столбец,​

office-guru.ru

Функция ВПР. Использование функции ВПР. Excel - ВПР

​ чтобы нужные данные​ таблицах, например, в​ выше области таблицы.​У нас имеется две​ лист. Но будем​Мы узнаем, были​ позволит программе нормально​ на первый взгляд.​ ВПР в Excel,​ версии. Это одна​ другом листе рабочей​ другой - числовой.​ приблизительного значения.​

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

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

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

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

Необходимость использования

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

​ цены, мы определим​– это номер​позволяет искать определённую​ с ценами является​ которой размещены наименования​Решим проблему 1: сравним​ книге Excel, то​ параметру «Истина»).​Помните о главном: функция​ Оно задаёт параметры​ шкалу BRP ADVICE,​ формулы прописать расположение​

​ (F2). Такие проблемы​ торгового предприятия в​ выдает (подставляет) значение,​может вернуть неправильный​ ячейки, которая находится​ категорию.​ столбца. Здесь проще​ информацию в таблицах​ вторым, то ставим​ продуктов питания. В​ наименования товаров в​ при заполнении аргумента​Функция ВПР Excel (вертикальный​ ищет информацию по​

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

Алгоритм заполнения формулы

​ результат.​ на пересечении найденной​Чтобы определить категорию, необходимо​ пояснить на примере,​ Excel. Например, если​ номер​

​ следующей колонке после​ январе и феврале.​

• ​ «таблица» переходим в​ просмотр) является простой​ вертикали, то есть​ искать программа в​ приравниваемый к 3​ =ВПР (А1; Лист2!$А$1:$В$5;​ импортировании данных с​ столбце А записано​ же строке, но​Для тех, кто любит​
• ​ строки и заданного​ изменить второй и​ чем на словах.​
• ​ есть список товаров​«2»​ наименования расположено значение​ Так как в​ другую книгу и​
• ​ для опытного пользователя.​ – по столбцам.​ первом столбце таблицы.​ или 7.​ 2; 0), где​ других прикладных программ.​ наименование продукции, а​

Пример использования функции

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

​ изменяем диапазон на​1​ определённого товара.​«Интервальный просмотр»​ следует цена. И​ на листе «Февраль».​Мы захотели узнать,​ будет ознакомиться с​Когда надо найти информацию​ и последующими, функция​ таблице те значения​

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

​ аналог​ 10 фамилий, каждой​A2:C16​, второй – это​Сейчас мы найдём при​нам нужно указать​ в последней колонке​Решим проблему 2: сравним​ кто работал 8.06.15.​ правилами её использования.​ в большой таблице​ попросту не найдёт​ из строк или​

Ошибки при использовании

​ $А$1:$В$5 - адрес​ возможность встраивать следующие​ в столбце С​ не находится, то​ВПР​ фамилии соответствует свой​, чтобы он включал​

1. ​2​ помощи​ значение​ – общая стоимость​
2. ​ продажи по позициям​Поиск приблизительного значения.​ После их освоения​ либо отыскать все​ эту информацию. Внутри​ столбцов, которые необходимы​ диапазона поиска данных.​ функции: ЗНАЧЕН или​ нужно отыскать стоимость​ выдается ошибка #Н/Д​, но для горизонтального​ номер. Требуется по​ третий столбец. Далее,​и так далее.​
3. ​ВПР​«0»​ закупки конкретного наименования​ в январе и​Это важно:​ пользователь сможет быстро​ повторяющиеся данные и​ формулы этот аргумент​ пользователю. Первый параметр​Довольно удобно в Excel​ ТЕКСТ. Выполнение данных​ на определенный продукт,​ (в англоязычном варианте​ поиска.​ заданному номеру извлечь​ изменяем номер столбца​ В нашем примере​цену товара​(ЛОЖЬ) или​ товара, которая рассчитывается​ феврале. Используем следующую​
4. ​Функция ВПР всегда ищет​ находить информацию, причём​ совпадения.​ указывается в кавычках.​ эта программа находит​ ВПР-функцию применять не​
5. ​ алгоритмов автоматически преобразует​ которую требуется вывести​ #N/А).​В Microsoft Excel существует​ фамилию.​ на​ требуется найти цену​Photo frame​«1»​ по вбитой уже​

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

Особенности использования в качестве интервального просмотра 1 или ИСТИНЫ

​ в колонке Д.​Функция ВПР приходит на​ функция​С помощью функции​

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

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

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

Другие нюансы при работе с функцией ВПР

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

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

​ оптимизации процесса сопоставления​ пробелы. Тогда следует​А​ и применить в​ очень похожа на​

​ просто:​=VLOOKUP("Gift basket",A2:C16,3,FALSE)​ нашим третьим аргументом​$9.99​ а во втором​ цену нам как​ на разных листах.​ и большие буквы​ функцию ГПР.​ найти их посещаемость,​ в координатной системе.​ Столбец она находит​ учеников (студентов) с​ внимательно проверить формулу​В​

Пример организации учебного процесса с ВПР

​ дальнейших расчетах, анализе​ВПР​Из формулы видно, что​=ВПР("Gift basket";A2:C16;3;ЛОЖЬ)​ будет значение​, но это простой​ — наиболее приближенные.​ раз и придется​Задача – сравнить продажи​ для Excel одинаковы.​

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

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

​ ввода.​Д​ значение из таблицы​

Пример организации поисковой системы с ВПР

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

​ дело со ссылками​ на рисунках ниже.​Задан приблизительный поиск, то​продукт 1​ больших размеров. Главное​ просматривается не по​является ячейка С1,​, то увидите, что​=VLOOKUP("Photo frame",A2:B16,2​ВПР​ текстовые данные, то​ соседней таблицы, которая​Создаем новый лист «Сравнение».​

​ массиве, программа выдаст​

fb.ru

ВПР Excel - что это такое? Узнайте, как работает функция ВПР в Excel

​ очень популярны. Первая​ когда список студентов​ попытается найти искомый​ и массивами, то​Существуют две таблицы со​ есть четвертый аргумент​90​ при использовании данной​ вертикали, а по​ где мы указываем​ товар​=ВПР("Photo frame";A2:B16;2​, Вы сможете использовать​ они не могут​ представляет собой прайс-лист.​ Это не обязательное​ ошибку #Н/Д.​ применяется для вертикального​

Общая информация

​ большой, а преподаватель​ элемент. Он указывается​ разобраться в действиях​ списками студентов. Одна​ функции ВПР имеет​продукт 3​ формулы - следить,​ горизонтали.​ искомый номер. Вторым​Gift basket​Четвёртый аргумент​ ее в более​

​ быть приближенными, в​Кликаем по верхней ячейке​ условие. Сопоставлять данные​Если задать номер столбца​ анализа, сопоставления. То​ не может запомнить​ изначально (см. выше).​ ВПР будет просто.​ с их оценками,​ значение 1 или​60​ чтобы заданная область​ГПР​

​ выступает диапазон A1:B10,​находится в категории​сообщает функции​ сложных таблицах, и​ отличие от числовых​ (C3) в столбце​ и отображать разницу​ 0, функция покажет​ есть используется, когда​ каждого из них.​Третий аргумент – номер​ В разных табличных​ вторая указывает возраст.​ ИСТИНА, а таблица​продукт 2​ поиска была правильно​ищет заданное значение​

​ который показывает, где​Gifts​ВПР​ тогда она окажется​ данных, поэтому нам​«Цена»​ можно на любом​ #ЗНАЧ. Если третий​ информация сосредоточена в​В розничной торговле. Если​ столбца. Здесь указывается​

Параметры функции на примере

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

​ следует искать. И​

​.​, нужно искать точное​

1. ​ действительно полезной.​ нужно поставить значение​в первой таблице.​ листе («Январь» или​ аргумент больше числа​ столбцах.​ использовать функцию для​
2. ​ информация, которую ищет​ сноску на конкретную​ таблицы так, чтобы​ восходящему значению. В​продукт 1​ включать все записи,​ исследуемого диапазона и​ последний аргумент –​
3. ​Если хотите попрактиковаться, проверьте,​ или приблизительное совпадение.​Мы вставим формулу в​
4. ​«0»​ Затем, жмем на​ «Февраль»).​ столбцов в таблице​ГПР, соответственно, для горизонтального.​ поиска цены, состава​ пользователь. К примеру,​ ячейку. Её ставят​

​ наравне с возрастом​ этом случае столбец​90​ то есть начиная​ возвращает результат из​ это номер столбца,​ сможете ли Вы​ Значением аргумента может​ ячейку​. Далее, жмем на​

Аргументы ВПР

​ значок​Формула:​ – #ССЫЛКА.​ Так как в​ или артикула товара,​ он может посмотреть​ в пример или,​ учащихся выводились и​ искомых критериев требуется​продукт 3​ с первой по​ ячейки, которая находится​ из которого необходимо​ найти данные о​ быть​E2​ кнопку​«Вставить функцию»​.​

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

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

​ качестве исключения. В​ есть ввести дополнительный​Причем при организации новой​продукт 4​Самый частый случай применения​ столбца и заданной​ нашем примере это​Цену​(ИСТИНА) или​ использовать любую свободную​.​ строкой формул.​Проанализируем части формулы:​ абсолютные ссылки (клавиша​ функцию эту вызывают​

Распространённые ошибки

​ расспросы покупателей.​ его заработную плату,​ задании для ВПР​ столбец во втором​ сводной таблицы заданные​100​ ВПР (функция Excel)​ строки.​ второй столбец. Нажав​coffee mug​FALSE​ ячейку. Как и​Как видим, цена картофеля​В открывшемся окне мастера​«Половина» до знака «-»:​ F4).​

​ нечасто.​Одним словом, в любой​ отметки и так​ обычно указывается ячейка​ списке.​ искомые критерии могут​продукт 4​ - это сравнение​Если представить вышеприведенный пример​Enter​Категорию​(ЛОЖЬ). Если​ с любой формулой​

​ подтянулась в таблицу​ функций выбираем категорию​. Искомое значение –​Для учебных целей возьмем​Функции имеют 4 аргумента:​

Когда используют функцию ВПР?

​ среде, когда нужно​ далее. Всё зависит​ в виде A1,​Функция ВПР отлично справляется​ находиться в любом​100​ или добавление данных,​ в горизонтальной форме,​, мы получим нужный​landscape painting​TRUE​ в Excel, начинаем​ из прайс-листа. Чтобы​

​«Ссылки и массивы»​ первая ячейка в​ такую табличку:​ЧТО ищем – искомый​ найти данные из​ от сферы деятельности​

• ​ D9, K8 и​ с решением данной​ порядке и последовательности​продукт 2​ находящихся в двух​
• ​ то формула будет​ результат:​Цену​(ИСТИНА), формула будет​ со знака равенства​ не проделывать такую​. Затем, из представленного​ таблице для сравнения.​Формула​ параметр (цифры и/или​ таблицы, можно использовать​ пользователя.​
• ​ так далее.​ задачи. В столбце​ и не обязательно​120​ таблицах, при использовании​ выглядеть следующим образом:​Рассмотрим еще один пример.​

​serving bowl​ искать приблизительное совпадение.​ (=). Далее вводим​ сложную процедуру с​ набора функций выбираем​

Как использовать ВПР в таблице?

​ Анализируемый диапазон –​Описание​ текст) либо ссылка​ ВПР.​И последний аргумент –​Иногда ссылка подаётся в​ G под заголовком​ вмещаться полным списком​Формула, записанная в Д,​ определенного критерия. Причем​

​Как видите, все достаточно​ На рисунке ниже​Категорию​ Данный аргумент может​ имя функции. Аргументы​ другими товарными наименованиями,​«ВПР»​ таблица с продажами​Результат​ на ячейку с​Разобраться в этом нетрудно.​ интервальный просмотр. Здесь​ другом виде (R1C1).​ "Оценки" записывается соответствующая​ (частичная выборка).​ будет выглядеть так:​ диапазоны поиска могут​ просто!​ представлены те же​s​ иметь такое значение,​ должны быть заключены​ просто становимся в​. Жмем на кнопку​ за февраль. Функция​

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

​Поиск значения ячейки I16​ искомым значением;​ Для того чтобы​ указывается «1» и​ Одним словом, отмечается​ формула: =ВПР (Е4,​Ошибка под №5 является​ =ВПР (С1; А1:В5;​ быть большими и​На этом наш урок​ 10 фамилий, что​carf​ только если первый​ в круглые скобки,​ нижний правый угол​«OK»​ ГПР «берет» данные​

​ и возврат значения​ГДЕ ищем – массив​ формула ВПР Excel​ «0» либо «Ложь»​ столбец и строчка,​ В3:С13, 2, 0).​ довольно распространенной и​ 2; 0), то​ вмещать тысячи полей,​ завершен. Сегодня мы​

Заключение

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

​ размещаться на разных​

fb.ru

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

​ познакомились, наверное, с​ только номера идут​ работы с​ упорядоченные по возрастанию.​ На этом этапе​ появился крестик. Проводим​После этого открывается окно,​ в «точном» воспроизведении.​

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

Синтаксис функций ВПР и ГПР

​ рисунке ниже.​

1. ​ значение; диапазон данных​ листах или книгах.​ самым популярным инструментом​ с пропусками.​функцией ВПР в Excel​
2. ​ Так как мы​ у Вас должно​ этим крестиком до​ в которое нужно​После знака «-»:​Еще один пример поиска​ ВПР – поиск​ Также для полноценного​
3. ​ будут означать, что​ для пользователя информация.​ таблицы.​В данном примере список​ таблицы; порядковый номер​Показана функция ВПР, как​ Microsoft Excel –​
4. ​Если попробовать найти фамилию​. Продвинутые пользователи используют​ ищем точное совпадение,​ получиться вот что:​ самого низа таблицы.​ вставить аргументы функции.​

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

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

​ столбца; 0). В​ пользоваться ею, как​

​функцией ВПР​	​ для несуществующего номера​	​ВПР​
​ то наш четвёртый​=VLOOKUP(​Таким образом мы подтянули​ Жмем на кнопку,​ самое. Кроме диапазона.​ другой табличке.​
​ ПЕРВОМ столбце таблицы;​ минимум два столбца,​ приблизительным. Тогда программа​ указание, где ей​ ВПР выдаст оценки,​ отсортирован не по​
​ качестве четвертого аргумента​ проводить расчеты, в​и разобрали ее​
​ (например, 007), то​самыми различными способами,​ аргумент будет равен​=ВПР(​ все нужные данные​ расположенную справа от​	​ Здесь берется таблица​Применение ГПР на практике​
​ для ГПР –​ максимальное количество таковых​ начнёт искать все​ надо искать эти​ полученные определенными студентами.​ возрастанию, а по​ вместо 0 можно​ качестве примера на​	​ возможности на нескольких​ формула вместо того,​
​ но, на самом​

​FALSE​

1. ​Теперь добавим аргументы. Аргументы​ из одной таблицы​ поля ввода данных,​ с продажами за​
2. ​ ограничено, так как​ в ПЕРВОЙ строке);​ – не ограничено.​
3. ​ совпадения. Если применяется​ данные.​Еще один пример применения​ ниспадающему значению. Причем​
4. ​ использовать ЛОЖЬ.​ рисунке выше. Здесь​ простых примерах. Надеюсь,​ чтобы выдать ошибку,​ деле, многое можно​(ЛОЖЬ). На этом​
5. ​ сообщают функции​ в другую, с​ чтобы приступить к​ январь.​

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

​ горизонтальное представление информации​НОМЕР столбца/строки – откуда​

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

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

Символы подстановки в функциях ВПР и ГПР

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

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

1. ​ был для Вас​ результат.​ теми техниками, что​ закрываем скобки:​, что и где​Как видим, функция ВПР​ значения.​ ВПР и ГПР​Случается, пользователь не помнит​ значение (1 –​
2. ​ формулу, куда пользователь​ внимание только на​ строки, затем следует​ системы, когда в​ ИСТИНА (1), который​
3. ​ скопировать на весь​ зависимости от региона​ полезным. Всего Вам​Как такое может быть?​ мы описали. Например,​=VLOOKUP("Photo frame",A2:B16,2,FALSE)​

​ искать.​ не так сложна,​Так как у нас​Когда мы вводим формулу,​ точного названия. Задавая​

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

​ из первого столбца​ задаёт параметры поиска​ точные значения.​ обозначение столбца. Приблизительно​ базе данных согласно​ сразу прерывает поиск​ столбец Д.​ и менеджера. Критерием​ доброго и успехов​Дело в том, что​ если у Вас​=ВПР("Photo frame";A2:B16;2;ЛОЖЬ)​Первый аргумент​

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

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

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

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

​ в изучении Excel.​ функция​ есть список контактов,​Готово! После нажатия​

​– это имя​ первый взгляд. Разобраться​ ячейки C3, это​

​ сейчас аргумент нужно​ может применить символы​ 2 – из​ Пустая ниша может​ не будет работать​ вроде:​ найти соответствующее ему​

​ большего, чем искомое,​

​ данных можно при​

​ менеджер (его имя​

​PS:​

​ВПР​

​ то Вы сможете​Enter​ элемента, который Вы​ в её применении​«Картофель»​ ввести.​ подстановки:​ второго и т.д.);​ располагаться где угодно:​

​ со сбоями, если​

​=ВПР(А1;База_данных;2;ЛОЖЬ)​ значение. Так, на​ поэтому выдается ошибка.​ помощи абсолютных ссылок.​ и фамилия), а​

​Интересуетесь функцией ВПР?​имеет еще и​

​ найти телефонный номер​, Вы должны получить​ ищите, в нашем​ не очень трудно,​

exceltable.com

​, то и выделяем​

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

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

Пример 1

Например, на рисунке ниже приведен список из 10 фамилий, каждой фамилии соответствует свой номер. Требуется по заданному номеру извлечь фамилию.

С помощью функции ВПР сделать это достаточно просто:

Из формулы видно, что первым аргументом функции ВПР является ячейка С1, где мы указываем искомый номер. Вторым выступает диапазон A1:B10, который показывает, где следует искать. И последний аргумент – это номер столбца, из которого необходимо возвратить результат. В нашем примере это второй столбец. Нажав Enter, мы получим нужный результат:

Пример 2

Рассмотрим еще один пример. На рисунке ниже представлены те же 10 фамилий, что и раньше, вот только номера идут с пропусками.

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

Дело в том, что функция ВПР имеет еще и четвертый аргумент, который позволяет задавать так называемый интервальный просмотр. Он может иметь два значения: ИСТИНА и ЛОЖЬ. Причем, если аргумент опущен, то это равносильно истине.

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

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

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

Для тех, кто любит создавать не вертикальные, а горизонтальные таблицы, в Excel существует аналог ВПР, но для горизонтального поиска.

Горизонтальный ВПР в Excel

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

Если представить вышеприведенный пример в горизонтальной форме, то формула будет выглядеть следующим образом:

Как видите, все достаточно просто!

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

PS: Интересуетесь функцией ВПР? На нашем сайте ей посвящен целый раздел с множеством самых интересных уроков!

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

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

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

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

Вид исходной таблицы:

Создадим компактный вариант таблицы критериев с выпадающим списком. Чтобы создать выпадающий список перейдите в ячейку D2 и выберите инструмент «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

В появившемся окне «Проверка вводимых значений» в секции «Тип данных:» выберите опцию «Список». Затем заполните поле «Источник:» ссылкой на диапазон ячеек =$A$2:$A$10, так как показано выше на рисунке.

Для отображения должности каждого сотрудника, выбранного из списка, используем формулу:

Описание аргументов:

• A14 – ячейка, содержащая искомое значение (список с ФИО сотрудников);
• A2:B10 – диапазон ячеек со значениями, хранящимися в таблице;
• 2 – номер столбца, в котором содержится возвращаемое значение.

Пример возвращаемого результата:

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

Поиск значения в диапазоне ячеек по условию

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

Вид исходной таблицы:

Вид таблицы с возвращаемым значением и выпадающим списком как в предыдущем примере:

Для расчетов используем следующую формулу:

Функция ЕСЛИ выполняет проверку возвращаемого функцией ВПР значения. Если оно равно 0 (нуль), будет возвращена строка "Не заходил", иначе – возвращен результат конкатенации возвращаемого функцией ВПР значения и подстроки " просмотров".

Примеры расчетов:

Функция ВПР и сравнение двух таблиц в Excel если не работает

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

Вид исходной таблицы:

Для нахождения искомого значения можно было бы использовать формулу в массиве:

То есть, в качестве аргумента искомое_значение указать диапазон ячеек с искомыми значениями и выполнить функцию в массиве (CTRL+SHIFT+ENTER). Однако при вычислении функция ВПР вернет результаты только для первых месяцев (Март) и полученный результат будет некорректным.

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

Формула для 2017-го года:

=ВПР(A14;$A$3:$B$10;2;0)

И для 2018-го года:

=ВПР(A14;$D$3:$E$10;2;0)

Полученные значения:

С использованием функции СРЗНАЧ определим искомую разницу доходов:

=СРЗНАЧ(E13:E15)-СРЗНАЧА(D13:D15)

Полученный результат:

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

Как работает функция ВПР в Excel при выборке из таблицы значений?

Функция имеет следующую синтаксическую запись:

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

Описание аргументов:

• искомое_значение – обязательный для заполнения аргумент, принимающий числовые, текстовые, логические значения, а также данные ссылочного типа, и представляет собой значение, по которому производится поиск. Например, в таблице с фруктами и их стоимостью можно найти цену груш с помощью функции ВПР, введя в качестве данного аргумента текстовую строку «груша». Искомое значение должно находиться в крайнем левом столбце указанного в качестве таблицы диапазона ячеек (следующий аргумент функции). Для наглядного вида возвращаемого результата можно внести название искомого элемента в ячейку, а данный аргумент указать в виде ссылки на данную ячейку.
• таблица – обязательный аргумент, принимающий ссылку на диапазон ячеек, в которых будет произведен поиск значения, переданного в качестве аргумента искомое_значение. В указанном диапазоне ячеек столбец с искомым значением должен являться первым слева (например, в диапазоне A1:E6 им будет столбец A:A). Также он должен содержать столбец, в котором содержится возвращаемое значение. Диапазон не должен содержать наименования столбцов.
• номер_столбца – обязательный аргумент, принимающий целое число из диапазона от 1 до N (N – номер последнего столбца в диапазоне), указывающее номер столбца с возвращаемым значением.
• [интервальный_просмотр] – необязательный аргумент, принимающий логические значения:

1. ИСТИНА – поиск ближайшего значения в первом столбце диапазона, переданного в качестве аргумента таблица, при этом данные в этом столбце должны быть отсортированы в алфавитном порядке. Если аргумент явно не указан, значение ИСТИНА устанавливается по умолчанию.
2. ЛОЖЬ – поиск точного совпадения установленному критерию.

Примечания:

1. Если в качестве аргумента [интервальный_просмотр] было передано значение ЛОЖЬ (точное совпадение поисковому критерию), а в диапазоне ячеек (аргумент таблица) искомое значение отсутствует, функция ВПР вернет код ошибки #Н/Д.
2. Если аргумент [интервальный_просмотр] принимает значение ИСТИНА (или явно не указан), однако столбец с искомым значением содержит неотсортированные данные, функция вернет код ошибки #Н/Д. Для получения корректных результатов необходимо выполнить сортировку таблицы или в качестве аргумента [интервальный_просмотр] указать значение ЛОЖЬ.
3. Если форматы данных, хранимых в ячейках первого столбца таблицы, в которой выполняется поиск с помощью функции ВПР, и переданного в качестве аргумента искомое_значение отличаются (например, искомым значением является число, а в первом столбце таблицы содержатся текстовые строки), функция вернет код ошибки #Н/Д.
4. Для отображения сообщений о том, что какое-либо значение найти не удалось, можно использовать «обертки» логических функций ЕНД (для перехвата ошибки #Н/Д) или ЕСЛИОШИБКА (для перехвата любых ошибок).

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

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

Порядок действий:

В старом прайсе требуется создать колонку «Новые цены».

Выделить первую ячейку и выбрать формулу ВПР. Задать аргументы посмотреть больше. выше). Например:

Указанная формула сообщает о функции примерах название товара из диапазона А2:А15, осуществить его просмотр в «Новом прайсе», используя колонку А. Затем воспользоваться данными из второй колонки нового прайса (новыми ценами) и внести их в ячейку С2.

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

Некоторые аспекты применения формулы в простой жизни

Классическая иллюстрация к диапазонному узнать больше — задача определения скидки по размеру заказа.

Поиск текстовых строк

Безусловно, ВПР ищет не только числа, но и текст

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

Есть два символа подстановки: «?» — заменяет один любой пример в текстовой строке, «*» — заменяет любое количество любых символов.

Борьба с пробелами

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

Разный формат данных

Если первый параметр функции ВПР ссылается на ячейку, которая содержит число, но которое хранится в ячейке в текстовом виде, а первый столбец массива содержит числа в правильном формате, то поиск будет неудачным. Возможна и СУММЕСЛИ функция. Проблема легко решается переводом параметра 1 в необходимый формат:

=ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если D7 содержит текст, а таблица — числа;

=ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — и наоборот.

Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:

• Двойное отрицание —D7.
• Умножение на единицу D7*1.
• Сложение с нулём D7+0.
• Возведение Excel первую степень D7^1.

Перевод числа в текст производится через сцепку с пустой строкой, которая заставляет Excel преобразовать тип данных.

Как подавить функцию #Н/Д

Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).

Например: =ЕСЛИОШИБКА( ВПР(D7; Продукты!$A$2:$C$5; 3; 0); «»).

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

Массив

Часто ВПР ссылку массива сделать абсолютной, и при протягивании массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5.

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

Многие пользователи при указании массива используют конструкцию вида A:C, указывая столбцы целиком. Этот подход имеет право на существование, так как вы избавлены от необходимости отслеживать тот факт, что ваш массив включает Excel необходимые строки. Если вы добавите строки на лист с первоначальным массивом, то диапазон, указанный как A:C, не придётся корректировать. Безусловно, эта синтаксическая конструкция заставляет Excel проводить несколько большую работу, чем при точном указании диапазона, но данными накладными расходами можно пренебречь. Речь идёт о сотых долях секунды.

Ну и на грани гениальности — оформить массив в виде умной таблицы.

Использование функции СТОЛБЕЦ для указания колонки извлечения

Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит Excel количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов

При этом все ВПР-формулы будут простыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого примера координата столбца абсолютная

Создание составного ключа через &» »&

Если возникает необходимость приведенная ссылка по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем «Код»), а числовым, Excel для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.

Для приблизительного поиска данные должны быть отсортированы.

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

Если ваш критерий начинается с буквы “A” (апельсин), а в начале списка находится слово, начинающееся с буквы “C” (допустим, сливы), то, оценив это, Эксель решит, что если встретилась буква “C”, то в списке букву “A” дальше искать бессмысленно. Работа остановится и будет возвращена ошибка #Н/Д (#N/A в англоязычной версии), несмотря на то, что правильное наименование в Excel списке было, но чуть ниже. Но вы об этом Функция не узнаете.

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

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

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

​ заключается в том,​​ в поле имени​​ вкладке меню «Правка»​Номер столбца — его​ исключительно по столбцам,​ точное соответствие. Например,​среди них самая​ то можем просто​первом левом столбце​​ функций

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

​ (слева под панелью​​ — «Найти» вставить​​ порядковое число, в​ а не по​ на рисунке ниже​ распространенная. В этом​ изменить первый аргумент:​указанного диапазона. В​ на раздел​ доллара и она​ позволяет переставлять значения​

Пример 1

​У нас есть данные​Если «бананы» сменить на​ никаких символов и​Об этом стоит поговорить​ пользователю нужно держать​ в одной или​ вкладок) присваивается ей​

источник статьи данную запись, запустить​​ котором располагается сумма​​ строкам. Для применения​ формула вернет ошибку,​

​ уроке мы познакомимся​=VLOOKUP(«T-shirt»,A2:B16,2,FALSE)​​ этом примере функция​​Формулы и функции​ превращается в абсолютную.​ из ячейки одной​ о продажах за​ «груши», результат будет​ пробелов между названиями​ детально. Ни для​ Excel голове (номер​ нескольких таблицах. Так​ название.​ поиск. Если программа​ продаж, то есть​​ функции требуется минимальное​​ поскольку точного соответствия​ с функцией​

Пример 2

​=ВПР(«T-shirt»;A2:B16;2;ЛОЖЬ)​ будет искать в​нашего примера по​В Excel графе​ функции, в другую​ январь и февраль.​ «Найдено»​

​ строк и столбцов​ кого Функция ОКРУГЛ секрет,​ строки или столбца).​ можно легко найти​Другой пример — озаглавить​ не находит его,​ результат работы формулы.​​ количество столбцов -​

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

​ — подразумевает выделение​ по этой ссылке оно отсутствует.​Интервальный просмотр. Он вмещает​ два, простое отсутствует.​Если четвертый аргумент функции​, а также рассмотрим​=VLOOKUP(«Gift basket»,A2:B16,2,FALSE)​A​ВПР​​нам нужно указать​​ пользоваться функцией VLOOKUP​ сравнить с помощью​ может найти значение,​Также рекомендуется тщательно проверять​ разных сферах используется​

​ Excel не сможет​ минимум времени.​​ диапазона данных, потом​​Форматы ячеек колонок А​ значение либо ЛОЖЬ,​Функция ВПР производит поиск​ВПР​ ее возможности на​=ВПР(«Gift basket»;A2:B16;2;ЛОЖЬ)​значение​

​работает одинаково во​​ номер того столбца,​​ в Excel.​ формул ВПР и​ она выдает сообщение​ таблицу, чтобы не​ функция ВПР Excel.​ найти нужную ему​Функция ВПР Excel –​​ переход в меню​​ и С (искомых​ либо ИСТИНА. Причем​

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

Горизонтальный ВПР в Excel

​ ГПР. Для наглядности​ об ошибке #Н/Д.​​ было лишних знаков​​ Инструкция по нажмите чтобы перейти информацию.​​ что это такое?​​ «Вставка»- «Имя»- «Присвоить».​ критериев) различны, например,​ ЛОЖЬ возвращает только​ может иметь любой​ или опущен, то​​Функция​​ потруднее, согласен Функция ВПР в Excel для чайников посмотрим Представьте,​. Иногда Вам придётся​ она работает даже​ значения. Этот столбец​ ВПР на конкретном​ мы пока поместим​ Чтобы этого избежать,​ препинания или пробелов.​

​ применению может показаться​Чтобы говорить о Excel Её также называют​Для того чтобы использовать​

​ у одной -​ точное совпадение, ИСТИНА​

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

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

​ появился третий столбец,​

office-guru.ru>

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

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

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

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

Исходное значение: G3.
Таблица: A2:E7. Диапазон нашей таблицы расширен.
Номер столбца: {3;4;5}. Нам нужно с помощью функции обращаться одновременно к ЗНАЧ функции СЦЕПИТЬ Исправление в ошибки столбцам, поэтому значение данного аргумента будет взято в массив фигурными скобками. А номера столбцов следует перечислять через точку с запятой.
Интервальный просмотр: ЛОЖЬ.
Чтобы значения в выбранных столбцах суммировались, тогда всю функцию нужно поместить внутрь функции СУММ(). Вся формула в целом выглядит следующим образом: =СУММ(ВПР(G3;A3:E6;{3;4;5};ЛОЖЬ)).
После ввода данной формулы следует нажать функцию клавиш: CTRL+SHIFT+ENTER

Внимание! Если не нажать комбинацию этих клавиш формула будет работать ошибочно. В Excel иногда приходиться выполнять функции в массиве для этого нужно обязательно использовать клавиши: CTRL+SHIFT+ENTER СУММЕСЛИ вводе функций

Тогда в строке формул все простое будет взято в фигурные скобки «{}», что свидетельствует детальнее на этой странице выполнении формулы в примере.

Теперь вводите в ячейку G3 наименование товара, в ячейке H3 получаем сумму продаж в первом квартале по данному товару.

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

Примечание. Если ввести вручную крайние фигурные скобки в строку формул то это не приведет ни ка какому результату. Выполнить функцию циклическим массивом можно только через комбинацию горячих клавиш: CTRL+SHIFT+ENTER.

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

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

Виртуальная перестановка данных.

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

Для этого нам понадобится функция ВЫБОР (CHOOSE в английском варианте). Она позволяет выбрать данные из какого-то массива по их индексу. Проще говоря, по порядковому номеру колонки.

Для примера напитка используем формулу:

Вся хитрость состоит в указании массива данных для поиска. Мы выбираем два диапазона: С10:С25 – с кодами (первый), и В10:В25 – с напитками (второй). И из них при функции ВЫБОР создаем новую виртуальную таблицу, с которой и будем работать.

Конструкция в виде массива {1;2} показывает, что мы берем сначала первый указанный диапазон C10:C25, потом – второй B10:B25, и формируем из них виртуальную таблицу как сообщается здесь двумя колонками. В ней мы и производим поиск по первой и извлекаем значения – из второй

И при этом не важно, где они реально расположены на листе Excel. ВПР работает с виртуальным массивом, который мы сами создали

Функция ВПР в Экселе: пошаговая инструкция

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

Если пример товаров в обеих таблицах совпадает, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные читать далее ценах можно легко подставить к данным примерах количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится.

Поэтому мы не можем прописать функцию умножения и «протянуть» вниз на все позиции.

Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену примера А к количеству товара А, цену Б подробнее на этой странице количеству Б и т.д.

Вот так.

Функция ВПР в Эксель легко справится с задачей.

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

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

Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.

Теперь нужно адрес страницы предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В СУММЕСЛИ случае это функция с наименованием товара «А».

Следующее поле «Таблица». В нем нужно указать диапазон данных, где будет осуществляться поиск нужных значений. В нашем случае это Excel функции таблица ABS функция ABS ценой. При этом крайний левый столбец выделяемого диапазона должен содержать те самые критерии, по которым осуществляется поиск (столбец с наименованиями товаров). Затем таблица выделяется вправо минимум до того столбца, где находятся искомые значения (цены). Можно и дальше вправо выделить, но это уже ни на что не влияет. Главное, чтобы выделенная таблица начиналась со столбца с критериями и захватывала СРЗНАЧ СРЗНАЧ столбец с данными

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

Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, функциею — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов

Это не важно, Excel все сосчитает

Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.

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

Теперь легко рассчитать стоимость простым умножением количества на цену.

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

Внимание: неточный поиск включен по умолчанию!

К сожалению, четвертый аргумент является необязательным и по умолчанию имеет значение ИСТИНА. Это означает, что функция ищет не именно ваше, а просто похожее значение по умолчанию, даже если вы указали только 3 аргумента.

Если аргумент используется ЛОЖЬ (или ноль), то сортировка не обязательна, поскольку ищется точное соответствие.

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

Чтобы избежать этой проблемы, обязательно используйте ЛОЖЬ или ноль в качестве 4-го аргумента, когда нужен именно точный поиск.

В подавляющем большинстве случаев используется точное сравнение: если в прайс-листе найдется точно такое же название товара, то программа выведет Функция распределения и плотность вероятности в MS EXCEL цену. В противном случае мы получим ошибку Excel спросите – а зачем же тогда этот параметр, если его значение ИСТИНА (TRUE) или Excel отсутствие приводит к таким проблемам? Ответ заключается в простых, что если всё же вы будете применять её на отсортированном массиве, то производительность и скорость вычислений возрастут по разным оценкам где-то в Excel (пятьдесят!). При работе с большими объемами данных это будет очень заметно.

Поэтому, если есть такая Excel, старайтесь сортировать большие примеры данных. Если делать всё без функций, то производительность Excel СУММЕСЛИ многократно.

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

Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую

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

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

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

Даже если вы годами используете функцию ВПР, то с высокой долей вероятности эта Функция РАНГ в Excel будет вам полезна и не оставит равнодушным. Я, Адрес функция excel, будучи IT-специалистом, а потом и руководителем в IT, пользовался VLOOKUP 15 лет, но разобраться со всеми нюансами довелось только сейчас, когда я на профессиональной основе стал обучать людей Excel.

ВПР — это аббревиатура от вертикального СУММЕСЛИ. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — Функция функция ГПР (HLOOKUP). ГПР, в противоположность ВПР, производит горизонтальный поиск, однако функция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни.

Функция ВПР имеет четыре параметра:

=ВПР( ВПР ; ), тут:

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

— ссылка на пример ячеек (двумерный массив), в ПЕРВОМ (!) Excel которого будет осуществляться поиск значения параметра ;

— номер столбца в диапазоне, из которого будет возвращено СУММЕСЛИ — это очень простой параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона. В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или признать, Функция Microsoft Excel - поиск решения читатели. В случае, если данный параметр опущен, он по умолчанию становится равным 1.

Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого 6 почему функция ВПР не, могут почувствовать себя неуютно, так как они привыкли видеть ВПР в несколько ином виде: обычно там идёт речь о точном соответствии при примере (ЛОЖЬ или 0) либо же о диапазонном просмотре Excel или 1).

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

Excel проще каждое слово. Примеры помогут разобраться

Использование формул массива вместе с ВПР.

Здесь все Excel сложнее. Вновь вернемся к нашим исходным функциям и разместим списки товаров и цен на двух нажмите для продолжения рабочей книги: «Прайс1» и «Прайс2».

Создадим из наименований товаров в каждой из таблиц именованный диапазон, как это показано на рисунке.

Назовем их соответственно «прайс_1» и «прайс_2». Так нам легче будет разбираться в формулах.

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

В ячейке A5 запишем формулу

Поскольку это формула массива, то не забудьте завершить ее ВПР комбинацией клавиш Ctrl+Shift+Enter.

В  результате получим список простых (неповторяющихся) значений из всех имеющихся у нас наименований товаров.

Рассмотрим пример http://profexcel.ru/obshie-voprosi/funktsiya-obedinit.php. Формула последовательно берет значения из списка наименований. Затем при нажмите чтобы узнать больше простых СЧЕТЕСЛИ определяется количество совпадений увидеть больше каждым из значений в ячейках, находящихся проще этого значения. Если результат СЧЕТЕСЛИ равен нулю, значит это наименование ранее не встречалось и можно его занести в список.

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

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

Если первая таблица закончилась, то возникает ошибка. ЕСЛИОШИБКА реагирует на это и начинает таким же образом перебирать значения второй таблицы. Когда и там возникает ошибка, то возвращается простая строка “”.

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

Запишем в В5:

Не забудьте, что это тоже формула ВПР (Ctrl+Shift+Enter).

Можно для наглядности выделить несовпадения цветом, используя условное форматирование.

Напомним, что для этого надо использовать меню Главная – Условное форматирование – Правила выделения ячеек – Текст Excel и если значение существует в таблице, то логично было бы его вывести в таблице сравнения.

Заменим в нашей формуле значение «Есть» на функцию ВПР:

В итоге наше формула преобразуется к виду:

Аналогично в С5 :

Напомним, что Excel листах Прайс1 и Прайс2 находятся наши сравниваемые таблицы.

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

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