Функция ГПР в Excel

Все видеоНовые видеоПопулярные видеоКатегории видео

Авто Видео-блоги ДТП, аварии Для маленьких Еда, напитки
Животные Закон и право Знаменитости Игры Искусство
Комедии Красота, мода Кулинария, рецепты Люди Мото
Музыка Мультфильмы Наука, технологии Новости Образование
Политика Праздники Приколы Природа Происшествия
Путешествия Развлечения Ржач Семья Сериалы
Спорт Стиль жизни ТВ передачи Танцы Технологии
Товары Ужасы Фильмы Шоу-бизнес Юмор

Главные новости Оперативно-профилактическое мероприятие «Должник»»>Сотрудники полиции устанавливают собственников гаражей по адресу Парковая, 14″>Позвонили от имени банка и пугают оформлением кредита на Ваше имя? – немедленно положите трубку. Вы имеете дело с мошенниками»>Полицейские Верхней Салды напоминают: находясь в садах, на участках, на отдыхе — необходимо следить за личными вещами!»>Сотрудники полиции Верхней Салды раскрыли серию краж прессованного картона из магазинов»>Перекрытие дорог 12.06.2021″>В рамках профилактической операции «Нелегальный мигрант» выявлено 23 факта нарушений миграционного законодательства»>Профилактическое мероприятие «Безопасная дорога»»>С наступлением лета сотрудники полиции усилили контроль за безопасностью несовершеннолетних»>Сотрудники ГИБДД выясняют обстоятельства ДТП на автодороге «Нижняя Салда – д. Медведево», в результате которого пострадал человек»> Покупка сток одежды для мужчин оптом»>Лучшие комедии 2021 года по мнению зрителей»>Металлопрокат и сфера его использования в повседневной жизни»>Реконструкция и реновация зданий»>Свято-Троице-Сергиева Лавра»>Как отличить оригинальные запчасти от подделки?»>Приложение Google Play Market»>The Witcher 3: Wild Hunt Прохождение — Дружелюбный Новиград #23″>Деятельность одного из подразделений Белгородского линейного отдела проверили общественники»>Как пробить финансовый потолок? Как пробить свой финансовый потолок и заработать нужную сумму денег?»> Склад ответственного хранения»>Фторопластовые втулки ф4, ф4К20 куплю по России неликвиды, невостребованные»>Стержень фторопластовый ф4, ф4к20 куплю по России излишки, неликвиды»>Куплю кабель апвпу2г, ввгнг-ls, пвпу2г, пввнг-ls, пвкп2г, асбл, сбшв, аабл и прочий по России»>Куплю фторопласт ФУМ лента, ФУМ жгут, плёнка фторопластовая неликвиды по России»>Силовой кабель закупаем в Екатеринбурге, области, по РФ неликвиды, излишки»>Фторопластовая труба ф4, лента ф4ПН куплю с хранения, невостребованную по РФ»>Фторопластовый порошок куплю по всей России неликвиды, с хранения»>Куплю провод изолированный СИП-2, СИП-3, СИП-4, СИП-5 невостребованный, неликвиды по РФ»>Фторкаучук скф-26, 26 ОНМ, скф-32 куплю по всей России неликвиды, невостребованный»> Фото laribalashova»>Фото Vladimir Vasin»>Фото Аля М»>Фото Светлана Латифов»>Фото Маго Мед»>Фото Сергей»>Фото Ольга Глазачева (Коростелева)»>Фото magomed03255@gmail.com»>Фото tamirumarov80″>Фото Елена Нестерова (Солодовникова»> наружная реклама 9 мин. назад мешки под глазами 16 мин. назад Популярная пекарня Хорс 29 мин. назад Цветы 33 мин. назад Как избавиться от запора быстро и эффективно 44 мин. назад Курсы 52 мин. назад труба из нержавейки 1 ч. 19 мин. назад Лингафонный кабинет 1 ч. 26 мин. назад Гидроизоляция полимочевиной 1 ч. 51 мин. назад Сериалы 1 ч. 58 мин. назад Последние комментарии Сергей Эти 3 техники помогают мне когда я напряжен, в основном на работе. Использую их на обеде. Случайно наткнулся на статью и попробо… 10 ч. 5 мин. назад aresfok Приветствуем вас на страницах нашего туристического портала Gidlite.ru, посвящённым отпуску. Очень важно не только работать, но … 14 ч. 5 мин. назад Анна Волкова Как оказалось не такая-уж и простая стала задача: в кратчайшие сроки найти работу вебкам моделью на дому. Гдето платят сущие коп… Вчера, 13:44:29 07072016uva Холодильник должен быть вместительным, не шумным и надежным. Стоит обратить внимание на зарекомендовавшие себя торговые марки. Н… 14 июня 2021 г. 16:55:19 07072016uva Холодильник должен быть вместительным, не шумным и надежным. Стоит обратить внимание на зарекомендовавшие себя торговые марки. Н… 11 июня 2021 г. 21:32:51 bakir7458 Выбирать нужно проверенные бренды. Даже если дороговато, но зато надежно… 11 июня 2021 г. 21:11:19 rom kov Естественно, мониторинг цен необходим, чтобы, исходя из этого, назначать свою цену на товары или услуги, если такая возможность … 11 июня 2021 г. 2:29:25 acercool Мониторинг цен в бизнесе — это конечно очень важный процесс. Тут всегда надо держать « ушки на макушке », ведь конкуренты, понят… 10 июня 2021 г. 14:40:20 07072016uva Цены мониторить очень важно, что бы не допускать демпинга и завышения. Что бы все слои населения имели доступ ко всем категориям… 10 июня 2021 г. 13:30:51 Хронос Согласен с автором статьи и считаю, что эта тема действительно имеет место быть в наше время! Но при прочтении статьи у меня сфо… 10 июня 2021 г. 12:44:00

В этой статье описаны синтаксис формулы и использование функции ГПР в Microsoft Excel.

Описание

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

Буква Г в аббревиатуре «ГПР» означает «горизонтальный».

Синтаксис

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

Аргументы функции ГПР описаны ниже.

Примечание

    Пример

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

    Подшипники

    Болты

    Формула

    Описание

    Результат

    ГПР(«Оси»;A1:C4;2;ИСТИНА)

    Поиск слова «Оси» в строке 1 и возврат значения из строки 2, находящейся в том же столбце (столбец A).

    ГПР(«Подшипники»;A1:C4;3;ЛОЖЬ)

    Поиск слова «Подшипники» в строке 1 и возврат значения из строки 3, находящейся в том же столбце (столбец B).

    ГПР(«П»;A1:C4;3;ИСТИНА)

    Поиск буквы «П» в строке 1 и возврат значения из строки 3, находящейся в том же столбце. Так как «П» найти не удалось, возвращается ближайшее из меньших значений: «Оси» (в столбце A).

    ГПР(«Болты»;A1:C4;4)

    Поиск слова «Болты» в строке 1 и возврат значения из строки 4, находящейся в том же столбце (столбец C).

    ГПР(3;{1;2;3:»a»;»b»;»c»;»d»;»e»;»f»};2;ИСТИНА)

    Поиск числа 3 в трех строках константы массива и возврат значения из строки 2 того же (в данном случае — третьего) столбца. Константа массива содержит три строки значений, разделенных точкой с запятой (;). Так как «c» было найдено в строке 2 того же столбца, что и 3, возвращается «c».

    Цель данной статьи — показать, как работает функция Excel ГПР . Рассмотрим работу функций на примере расчета прогноза с логарифмическим трендом и сезонностью.

    По ходу статьи мы:

    1. Рассчитаем значения логарифмического тренда и продлим тренд в будущее;
    2. Выделим сезонность;
    3. Прогнозный тренд скорректируем сезонностью. Для этого воспользуемся функцией Excel =ГПР и получим прогноз.

    1. Рассчитаем значения логарифмического тренда и продлим тренд в будущее.

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

    Для этого воспользуемся функцией Excel =Предсказ:

    Разберем формулу Excel =ПРЕДСКАЗ(

    • LN(D2) — X- ссылка но номер периода X, причем делаем логарифм Х;
    • $D$4:$BE$4 — известные значения y — зафиксированная ссылка на ряд с объемом продаж за весь период;
    • LN($D$2:$BE$2) — известные значения x — зафиксированная ссылка на диапазон с номерами периодов — X под логарифмом;
    • Обратите внимание, что ряд «известные значения y» = «известные значения x»

    Способы расчета логарифмического тренда в Excel в статье 5 способов расчета логарифмического тренда «

    Для чего мы пронумеровываем периоды и о временных рядах читайте статью

    Протягиваем формулу до конца ряда и на 12 месяцев вперед, получаем значения тренда для каждого момента времени в прошлом и прогнозный тренд на 12 месяцев в будущее:

    2. Выделим сезонность

    1. Определим отношение Объема продаж к тренду — объем продаж разделим на логарифмический тренд:

    2. Рассчитаем среднее отношение объема продаж к тренду для каждого месяца в году.

    2.1. Определим номер месяца для каждого периода в ряду:

    2.2. Пронумеруем номера месяцев для сезонности:

    2.3. Воспользовавшись функцией Excel=СУММЕСЛИ и =СЧЕТЕСЛИ (подробнее о работе с этими функциями читайте в статье «Формулы Excel «СУММЕСЛИ» и «СЧЕТЕСЛИ» «), рассчитаем сезонность для каждого месяца — среднее отклонение отношений объема продаж от логарифмического тренда:

    2.3. Рассчитаем нормирующий коэффициент — среднее значений полученной сезонности

    В среднем сезонность должна быть равна 1. А у нас получилась 0,995027

    2.4. Рассчитаем сезонность.

    Для того, чтобы сезонность стала равной 1, разделим полученную сезонность на нормирующий коэффициент, который у нас равен 0,995027:

    Получили месячные коэффициенты сезонности приведенные в среднем к 1.

    3. Прогнозный тренд скорректируем сезонностью, для этоговоспользуемся функцией Excel =ГПР — получим прогноз.

    Мы рассчитали:

    • Значения логарифмического тренда и продлили тренд в будущее на 12 месяцев
    • Сезонность.

    Теперь рассчитаем прогноз.

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

    Для поиска нужного коэффициента воспользуемся функцией Excel ГПР.

    Разберем ГПР(искомое значение; таблица; номер строки; интервальный просмотр) по частям:

    • Искомое значение — BF5 — номер прогнозного месяца, для которого нам необходимо найти коэффициент сезонности в таблице и вернуть его значение в ячейку:
    • Таблица — $D$12:$O$13 — зафиксированная ссылка на таблицу с сезонностью, в которой в первой строке находятся номера месяцев, а во второй коэффициенты сезонности:

    Формула ГПР ищет номер месяца в первой строке таблицы и возвращает коэффициент сезонности из второй , но для этого еще надо указать строку из которой возвращаем сезонность:

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

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

    В этой статье описаны синтаксис формулы и использование функции ГПР в Microsoft Excel.

    Описание

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

    Буква Г в аббревиатуре «ГПР» означает «горизонтальный».

    Синтаксис

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

    Аргументы функции ГПР описаны ниже.

    Примечание

      Пример

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

      Подшипники

      Болты

      Формула

      Описание

      Результат

      ГПР(«Оси»;A1:C4;2;ИСТИНА)

      Поиск слова «Оси» в строке 1 и возврат значения из строки 2, находящейся в том же столбце (столбец A).

      ГПР(«Подшипники»;A1:C4;3;ЛОЖЬ)

      Поиск слова «Подшипники» в строке 1 и возврат значения из строки 3, находящейся в том же столбце (столбец B).

      ГПР(«П»;A1:C4;3;ИСТИНА)

      Поиск буквы «П» в строке 1 и возврат значения из строки 3, находящейся в том же столбце. Так как «П» найти не удалось, возвращается ближайшее из меньших значений: «Оси» (в столбце A).

      ГПР(«Болты»;A1:C4;4)

      Поиск слова «Болты» в строке 1 и возврат значения из строки 4, находящейся в том же столбце (столбец C).

      ГПР(3;{1;2;3:»a»;»b»;»c»;»d»;»e»;»f»};2;ИСТИНА)

      Поиск числа 3 в трех строках константы массива и возврат значения из строки 2 того же (в данном случае — третьего) столбца. Константа массива содержит три строки значений, разделенных точкой с запятой (;). Так как «c» было найдено в строке 2 того же столбца, что и 3, возвращается «c».

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

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

      Пример 1

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

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

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

      Пример 2

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

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

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

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

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

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

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

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

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

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

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

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

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

      a 7.2 Проведение анализа документа

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

      ВПР() – «В ертикальный ПР осмотр» ГПР() – «Г оризонтальный Пр осмотр»

      Для начала создадим документ следующего вида:

      Для этого:

      1. Создайте документ Microsoft Excel.
      2. Для ячеек A1:F1 выполните команду Объединить и центрировать . Введите заголовок.
      3. Введите заголовки столбцов. Выделите ячейки A2:F2 и выполните команду Формат/Ячейки . Во вкладке Выравнивание установите флажок на Переносить по словам .
      4. Введите данные в таблицу.
      5. Для ячеек A20:B20 выполните команду Объединить и центрировать и введите заголовок таблицы.
      6. В ячейки A22:A26 введите заголовки таблицы.
      7. В ячейку B22 введите название любой страны.

      Переходим к использованию функций ВПР() и ГПР(). Эти функции имеют следующий синтаксис:

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

      ГПР (искомое_значение; диапазон_таблицы; номер_строки;_интервальный_просмотр)

      искомое_значение – это значение, которое требуется найти в первом столбце/строке таблицы; диапазон_таблицы – это ячейки, составляющие информационную таблицу; номер_столбца/номер_строки – номер столбца/строки, в котором требуется найти искомое значение; интервальный_просмотр – (является необязательным) Это логическое значение, которое определяет, нужно ли, чтобы функция ВПР()/ВКР() искала точное значение или приближенное соответствие. Если этот аргумент опущен, или имеет истинное значение, то функция возвращает приблизительное соответствующее значение (если точное соответствие не найдено).

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

      1. В ячейку B22 введите формулу =ВПР(B21;A3:F17;2;ЛОЖЬ) . У вас отобразится информация о выбранной вами стране, а именно, её площадь.
      2. Аналогичным образом «узнайте» другую информацию об этой стране.
      3. В ячейку B21 введите название другой страны. В ячейках B22:B26 немедленно отобразится информация об этой стране.

      Автор: · Опубликовано 27.04.2018 · Обновлено 08.02.2019

      Редактор Эксель – очень мощная программа для работы с таблицами. Иногда бывает так, что приходится работать с большим объемом данных. В таких случаях используются различные инструменты поиска информации. Функция «ВПР» в Excel – одна из самых востребованных для этой цели. Рассмотрим её более внимательно.

      Расшифровка

      Большинство пользователей не знают, что аббревиатура «ВПР» расшифровывается как «Вертикальный Просмотр». На английском функция называется «VLOOKUP», которая означает «Vertical LOOK UP»

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

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

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

      Теперь нужно воспользоваться этой же формулой и для остальных полей. Простое копирование ячейки при помощи Ctrl+C и Ctrl+V не подойдёт, поскольку у нас используются относительные ссылки и каждый раз будет меняться номер столбца.

      Для того чтобы всё сработало правильно, нужно сделать следующее:

      1. Кликните на ячейку с первой функцией.
      2. Перейдите в строку ввода формул.
      3. Скопируйте текст при помощи Ctrl+C.
      1. Сделайте активной следующее поле.
      2. Снова перейдите в строку ввода формул.
      3. Нажмите на горячие клавиши Ctrl+V.

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

      1. Затем меняем номер столбца на нужный. В нашем случае это 3. Нажимаем на клавишу Enter.
      1. Благодаря этому мы видим, что данные из столбца «Год рождения» определились правильно.
      1. После этого повторяем те же самые действия для последнего поля, но с корректировкой номера нужного столбца.

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

      То есть нумерация начинается не с начала листа, а с начала указанной области ячеек.

      Как использовать функцию «ВПР» для сравнения данных

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

      1. Добавим второй лист с точно такой же таблицей (копировали при помощи горячих клавиш Ctrl+C и Ctrl+V).
      2. Повысим стажеров до «Младший сотрудник». Эта информация будет отправной точкой для сравнения.
      1. Добавим ещё один столбец в нашу старую таблицу.
      1. Переходим в первую клетку нового столбца и вводим там следующую формулу.

      =ВПР($B$3:$B$11;Лист2!$B$3:$E$11;4;ЛОЖЬ)

      Она означает:

      • $B$3:$B$11 – для поиска используются все значения первой колонки (применяются абсолютные ссылки);
      • Лист2! – эти значения нужно искать на листе с указанным названием;
      • $B$3:$E$11 – таблица, в которой нужно искать (диапазон ячеек);
      • 4 – номер столбца в указанной области данных;
      • ЛОЖЬ – искать точные совпадения.
      1. Новая информация выведется в том месте, где мы указали формулу.
      2. Результат будет следующим.
      1. Теперь продублируйте эту формулу в остальные ячейки. Для этого нужно потянуть мышкой за правый нижний угол исходной клетки.
      1. В итоге мы увидим, что написанная нами формула работает корректно, поскольку все новые должности скопировались как положено.

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

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

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

      Функция «ВПР» и выпадающие списки

      Рассмотрим примеры использования этих двух инструментов одновременно. Для этого нужно выполнить следующие действия.

      1. Перейдите в ячейку, в которой происходит выбор фамилии.
      2. Откройте вкладку «Данные».
      3. Кликните на указанный инструмент и выберите пункт «Проверка данных».
      1. В новом окне в графе «Тип данных» выберите пункт «Список».
      1. После этого появится новое поле «Источник». Кликните туда.
      2. Затем выделите первый столбец. Ссылка на ячейки подставится автоматически.
      3. Для продолжения нажмите на «OK».
      1. Благодаря этому у вас появится список всех фамилий, которые находятся в базе. Выберите что-нибудь из предложенного.
      1. Вы увидите, что данные автоматически изменились. При этом всё подставится согласно выбранной фамилии.

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

      Ошибки #Н/Д

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

      • вы включили точный поиск (последний параметр 0/ЛОЖЬ), а это значение в таблице отсутствует;
      • вы включили неточный поиск (последний параметр 1/ИСТИНА), но при этом данные не отсортированы по возрастанию (если используется приблизительный поиск, то разработчики Microsoft рекомендуют использовать упорядоченные данные);
      • аргументы функции имеют различный формат (например, что-то в текстовом виде, а остальное – в числовом);
      • в формуле присутствуют опечатки или лишние символы (пробелы, непечатаемые знаки, переносы и так далее).

      Отличие от функции «ГПР»

      Данный инструмент практически точно такой же, только ищет по горизонтали. Более подробно о нем можно узнать на официальном сайте Microsoft.

      Заключение

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

      Если у вас что-то не получается, возможно, вы неправильно указываете аргументы функции. Например, неправильный номер столбца или неверный диапазон ячеек. Попробуйте повторить описанные выше действия заново, но на этот раз более внимательно. И у вас всё получится.

      Видеоинструкция

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

      • Категория: MS Office
      • Теги: Формулы и функции Excel

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

      По принципу работы функция ГПР в Excel аналогична другой, не менее востребованной формуле данной программы, осуществляющей «вертикальный» поиск. Подробнее про функцию ВПР читайте [u0423u0447u0435u0431u043du044bu0435 u0441u0442u0430u0442u044cu0438]</span> u0424u0443u043du043au0446u0438u044f u0412u041fu0420 u0432 Excel u0447u0430u0441u0442u043e u0442u0440u0435u0431u0443u0435u0442u0441u044f u043du0430 u043fu0440u0430u043au0442u0438u043au0435 u043fu0440u0438 u043eu0431u0440u0430u0431u043eu0442u043au0435 u0431u043eu043bu044cu0448u0438u0445 u043cu0430u0441u0441u0438u0432u043eu0432 u0434u0430u043du043du044bu0445, u043du043e u043fu0440u0438 u044du0442u043eu043c u0441 u044du0442u043eu0439 u0436u0435 u0444u0443u043du043au0446u0438u0435u0439 u0447u0430u0449u0435 u0432u0441u0435u0433u043e u0432u043eu0437u043du0438u043au0430u044eu0442 u043fu0440u043eu0431u043bu0435u043cu044b u043fu0440u0438 u0435u0451 u0438u0441u043fu043eu043bu044cu0437u043eu0432u0430u043du0438u0438. u0412 u0441u0442u0430u0442u044cu0435 u043eu043fu0438u0441u0430u043du0430 u043fu043eu0448u0430u0433u043eu0432u0430u044f u0438u043du0441u0442u0440u0443u043au0446u0438u044f u043fu043e u0444u0443u043du043au0446u0438u0438 u0412u041fu0420 «u0434u043bu044f u0447u0430u0439u043du0438u043au043eu0432″, u0430 u0442u0430u043au0436u0435 u043fu0440u0438u0432u0435u0434u0435u043du044b u043fu0440u0438u043cu0435u0440u044b u0438 u0432u0438u0434u0435u043eu0443u0440u043eu043a.»,»image»:»/upload/artemvm.info/information_system_38/4/2/3/item_4238/small_item_4238.jpg»,»href»:»/information/uchebnye-stati/microsoft-office/funkcziya-vpr-v-excel/»}»>здесь.

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

      Ниже коротко рассмотрена работа функции ГПР, её синтаксис (для ручного написания, а также понимания аргументов функции) и рассмотрен простой пример применения ГПР на практике.

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

      image[нажмите на картинку для увеличения] Справка: как сохранять фото с сайтов

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

      Добавить формулу в ячейку Вы можете либо вручную, либо при помощи [u0423u0447u0435u0431u043du044bu0435 u0441u0442u0430u0442u044cu0438]</span> u041cu0430u0441u0442u0435u0440 u0444u0443u043du043au0446u0438u0439 u0432 Excel u0438u0441u043fu043eu043bu044cu0437u0443u0435u0442u0441u044f u0434u043bu044f u0432u0441u0442u0430u0432u043au0438 u0438 u0438u0437u043cu0435u043du0435u043du0438u044f u0444u043eu0440u043cu0443u043b u0432 u044fu0447u0435u0439u043au0430u0445. u0418u0441u043fu043eu043bu044cu0437u043eu0432u0430u0442u044c u041cu0430u0441u0442u0435u0440 u0444u0443u043du043au0446u0438u0439 u0432 u042du043au0441u0435u043bu0435 u0433u043eu0440u0430u0437u0434u043e u043fu0440u043eu0449u0435 u0438 u0443u0434u043eu0431u043du0435u0435, u0447u0435u043c u043fu0438u0441u0430u0442u044c u0444u043eu0440u043cu0443u043bu044b u0432u0440u0443u0447u043du0443u044e u043du0435u043fu043eu0441u0440u0435u0434u0441u0442u0432u0435u043du043du043e u0432 u044fu0447u0435u0439u043au0435 u0442u0430u0431u043bu0438u0446u044b u0438u043bu0438 u0441u0442u0440u043eu043au0435 u0444u043eu0440u043cu0443u043b.»,»image»:»/upload/artemvm.info/information_system_38/4/2/3/item_4233/small_item_4233.jpg»,»href»:»/information/uchebnye-stati/microsoft-office/master-funkczij-v-excel/»}»>Мастера функций. Последний способ предпочтительнее, поскольку позволяет указывать как минимум половину параметров просто при помощи мышки.

      Обобщённый синтаксис формулы ГПР следующий: ГПР(искомое_значение; таблица; номер_строки; [интервальный_просмотр])

      Параметры формулы имеют следующее значение:

      • искомое_значение Адрес ячейки в первой таблице (где наша функция ГПР), которое нужно найти в аналогичной по содержанию строке второй таблицы. Вместо адреса ячейки можно указать текст или число, но обычно это не имеет значения, так как для разных ячеек искомое значение меняется. Это обязательный аргумент. В нашем примере для ячейки B5 искомым значением является наименование должности в ячейке B4.
      • таблица Диапазон ячеек, в которых ищется совпадение по значению, указанное в аргументе 1. Диапазон должен быть указан таким образом, чтобы строка, в которой производится поиск совпадений, была первой (считая сверху вниз). Заголовки таблицы не нужно включать в диапазон. В нашем примере это $B8:$D9 (обратите внимание на символ доллара — он нужен чтобы диапазон оставался неизменным при копировании формулы в другие ячейки первой таблицы).
      • номер_строки Порядковый номер строки в указанном диапазоне ячеек (параметр «таблица»), значение из которого будет являться результатом работы функции ГПР. Именно это значение будет возвращено функцией (вставлено в ячейку), если совпадение искомого значения найдено. В нашем примере для ячейки B5 это 2 (строки нумеруются начиная с 1 от верхнего края диапазона).
      • интервальный_просмотр Значение 0 или 1. Число 0 означает поиск точного совпадения искомого значения; 1 — приблизительный. В примере мы ищем точное совпадение, поскольку нам требуется найти соответствие зарплаты по должности.

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

      Как работает функция ГПР в Excel

      Формула ГПР выполняет «горизонтальный» поиск в указанном диапазоне. Если в первой строке диапазона найдено совпадение с искомым значением, то функция возвращает значение из строки с указанным в аргументе 3 номером и вставляет его в ячейку с функцией.

      Если совпадение не найдено, то ГПР возвращает значение «#Н/Д».

      Почему не работает функция ГПР

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

      • Неверно указан диапазон для поиска (в том числе стоит проверить, что диапазон не изменяется при копировании формулы в другие ячейки).
      • Неверно указан номер столбца в диапазоне (например, столбца с таким номером нет или есть, но там не те данные).
      • Не задан интервальный просмотр (вообще-то это не обязательный параметр, но практика показывает, что лучше его указывать явно).

      Если производится обработка больших массивов данных, особенно [u0423u0447u0435u0431u043du044bu0435 u0441u0442u0430u0442u044cu0438]</span> u041au0430u043a u0434u043eu0431u0430u0432u0438u0442u044c u0442u0435u043au0441u0442 u0432 Excel u0438u0437 u0434u0440u0443u0433u0438u0445 u043fu0440u043eu0433u0440u0430u043cu043c u043fu0440u0438 u043fu043eu043cu043eu0449u0438 u043cu0430u0441u0442u0435u0440u0430 u0438u043cu043fu043eu0440u0442u0430. u041fu0440u0438u043cu0435u0440 u0432 u0441u0442u0430u0442u044cu0435 u0438 u043du0430 u0432u0438u0434u0435u043e.»,»image»:»/upload/artemvm.info/information_system_38/3/8/7/item_3870/small_item_3870.jpg»,»href»:»/information/uchebnye-stati/microsoft-office/import-teksta-v-excel/»}»>импортированных из внешних источников, то вполне вероятно, что совпадения могут быть не для всех ячеек. В реальных случаях значения «#Н/Д» в некоторых ячейках является нормальным (можно выборочно проверить несколько таких ячеек вручную, чтобы убедиться в том, что это не ошибка, а просто отсутствие совпадений).

      В нашем примере для ячейки B5 во второй таблице будет найдено значение зарплаты по значению должности. Алгоритм поиска работает так:

      • В ячейке B4 указана должность «Директор»;
      • Функция ГПР просматривает таблицу 2 (нижнюю, см. скриншот) и в первой строке ищет слово «Директор»;
      • Если в первой строке второй таблицы (там, где написаны должности) будет найдено совпадение, то функция вернёт значение зарплаты из второй строки таблицы.

      Итого в результате для ячейки B5 результат работы ГПР будет такой: «40000»

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

      ГПР в Excel, примеры

      Показанный выше пример рассмотрен на видео. Также Вы можете скачать Excel файл с этим примером.

      Больше практических примеров по работе в Excel, включая изучение программы с самых основ есть в нашем спецкурсе на видео. Посмотреть примеры уроков и учебный план можно [ArtemVM.iNFO]</span> u0426u0435u043du0430: 4000 u0440u0443u0431.</strong> u0412u0438u0434u0435u043eu043au0443u0440u0441 u043fu0440u0435u0434u043du0430u0437u043du0430u0447u0435u043d u0434u043bu044f u0441u0430u043cu043eu0441u0442u043eu044fu0442u0435u043bu044cu043du043eu0433u043e u043eu0441u0432u043eu0435u043du0438u044f u043fu0440u043eu0433u0440u0430u043cu043cu044b Microsoft Excel u00abu0441 u043du0443u043bu044fu00bb. u0422u0430u043au0436u0435 u0431u0443u0434u0435u0442 u043fu043eu043bu0435u0437u0435u043d u0438 u0442u0435u043c u043fu043eu043bu044cu0437u043eu0432u0430u0442u0435u043bu044fu043c, u043au043eu0442u043eu0440u044bu0435 u043cu043eu0433u0443u0442 u0432u044bu043fu043eu043bu043du044fu0442u044c u0432 Excel u0442u043eu043bu044cu043au043e u0441u0430u043cu044bu0435 u043fu0440u043eu0441u0442u044bu0435 u043eu043fu0435u0440u0430u0446u0438u0438.»,»image»:»/upload/artemvm.info/shop_3/2/1/6/item_216/small_item_216.jpg»,»href»:»/shop/video/microsoft-office/uchebnyj-videokurs-po-programme-microsoft-excel/»}»>здесь.

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

      Смотреть видео Функция ГПР в Excel

      Вы можете просмотреть любой прикреплённый документ в виде PDF файла. Все документы открываются во всплывающем окне, поэтому для закрытия документа пожалуйста не используйте кнопку «Назад» браузера.

      • Справка по функции ГПР в Excel.pdf

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

      • ГПР в Excel пример.zip

      Авторы публикации

      Артём В. Меньщиков

      Преподаватель & программист

      [u0423u0447u0430u0441u0442u043du0438u043au0438 u043fu0440u043eu0435u043au0442u0430]</span> u0410u0432u0442u043eu0440 u043eu043du043bu0430u0439u043d u043au0443u0440u0441u043eu0432 u0438 u0443u0447u0435u0431u043du044bu0445 u0432u0438u0434u0435u043eu043au0443u0440u0441u043eu0432, u043eu0441u043du043eu0432u043du043eu0439 u043fu0440u0435u043fu043eu0434u0430u0432u0430u0442u0435u043bu044c u043fu0440u043eu0435u043au0442u0430, u043fu0440u043eu0433u0440u0430u043cu043cu0438u0441u0442. u0420u0430u0437u0440u0430u0431u043eu0442u0447u0438u043a u0438 u0430u0434u043cu0438u043du0438u0441u0442u0440u0430u0442u043eu0440 u043eu0431u0440u0430u0437u043eu0432u0430u0442u0435u043bu044cu043du043eu0433u043e u0438 u0438u043du0444u043eu0440u043cu0430u0446u0438u043eu043du043du043eu0433u043e u043fu0440u043eu0435u043au0442u0430 ArtemVM.INFO.»,»image»:»/upload/artemvm.info/information_system_47/3/7/8/item_3787/small_item_3787.jpg»,»href»:»/information/uchastniki-proekta/artemvm/»}»>подробнее

      Оцените статью
      Рейтинг автора
      5
      Материал подготовил
      Илья Коршунов
      Наш эксперт
      Написано статей
      134
      А как считаете Вы?
      Напишите в комментариях, что вы думаете – согласны
      ли со статьей или есть что добавить?
      Добавить комментарий