Функция гпр в экселе пошаговая инструкция для чайников

Совет: Попробуйте воспользоваться новой функцией ПРОСМОТРX , улучшенной версией функции ГГ ПРОСМОТР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее использовать предшественницу.

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

Описание

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

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

Синтаксис

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

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

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

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

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

  • Если аргумент «интервальный_просмотр» имеет значение ИСТИНА, то значения в первой строке аргумента «таблица» должны быть расположены в возрастающем порядке: …-2, -1, 0, 1, 2, …, A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ГПР может выдать неправильный результат. Если аргумент «интервальный_просмотр» имеет значение ЛОЖЬ, таблица может быть не отсортирована.

  • В текстовых строках регистр букв не учитывается.

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

• Номер_строки    — обязательный аргумент. Номер строки в аргументе «таблица», из которой будет возвращено соответствующее значение. Если значение аргумента «номер_строки» равно 1, возвращается значение из первой строки аргумента «таблица», если оно равно 2 — из второй строки и т. д. Если значение аргумента «номер_строки» меньше 1, функция ГПР возвращает значение ошибки #ЗНАЧ!; если оно больше, чем количество строк в аргументе «таблица», возвращается значение ошибки #ССЫЛ!.

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

Примечание

• Если функция ГПР не может найти «искомое_значение» и аргумент «интервальный_просмотр» имеет значение ИСТИНА, используется наибольшее из значений, меньших, чем «искомое_значение».

• Если значение аргумента «искомое_значение» меньше, чем наименьшее значение в первой строке аргумента «таблица», функция ГПР возвращает значение ошибки #Н/Д.

• Если аргумент «интервальный_просмотр» имеет значение ЛОЖЬ и аргумент «искомое_значение» является текстом, в аргументе «искомое_значение» можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одному знаку; звездочка — любой последовательности знаков. Чтобы найти какой-либо из самих этих знаков, следует указать перед ним знак тильды (~).

Пример

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

Оси	Подшипники	Болты
4	4	9
5	7	10
6	8	11
Формула	Описание	Результат
=ГПР(«Оси»;A1:C4;2;ИСТИНА)	Поиск слова «Оси» в строке 1 и возврат значения из строки 2, находящейся в том же столбце (столбец A).	4
=ГПР(«Подшипники»;A1:C4;3;ЛОЖЬ)	Поиск слова «Подшипники» в строке 1 и возврат значения из строки 3, находящейся в том же столбце (столбец B).	7
=ГПР(«П»;A1:C4;3;ИСТИНА)	Поиск буквы «П» в строке 1 и возврат значения из строки 3, находящейся в том же столбце. Так как «П» найти не удалось, возвращается ближайшее из меньших значений: «Оси» (в столбце A).	5
=ГПР(«Болты»;A1:C4;4)	Поиск слова «Болты» в строке 1 и возврат значения из строки 4, находящейся в том же столбце (столбец C).	11
=ГПР(3;{1;2;3:»a»;»b»;»c»;»d»;»e»;»f»};2;ИСТИНА)	Поиск числа 3 в трех строках константы массива и возврат значения из строки 2 того же (в данном случае — третьего) столбца. Константа массива содержит три строки значений, разделенных точкой с запятой (;). Так как «c» было найдено в строке 2 того же столбца, что и 3, возвращается «c».	c

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

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

Например, в таблице с полями «Имя» и «Дата рождения» необходимо получить значение даты рождения для сотрудника, запись о котором является третьей сверху. В этом случае удобно использовать следующую функцию: =ГПР(«Дата рождения»;A1:B10;4), где «Дата рождения» – наименование столбца таблицы, в котором будет выполнен поиск, A1:B10 – диапазон ячеек, в котором расположена таблица, 4 – номер строки, в которой содержится возвращаемое значение (поскольку таблица содержит шапку, номер строки равен номеру искомой записи +1.

Пошаговые примеры работы функции ГПР в Excel

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

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

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

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

• F1 – ячейка, содержащая название поля таблицы;
• A1:C11 – диапазон ячеек, в которых содержится исходная таблица;
• E2+1 – номер строки с возвращаемым значением (для id=5 – шестая строка, поскольку первая строка используется под шапку таблицы).

Возвращаемый результат:

В ячейке F2 автоматически выводится значение соответствующие номеру id в исходной таблице.



ГПР для выборки по нескольких условиях в Excel

Пример 2. На основе таблицы из первого примера создать компактное представление таблицы, в которой по номеру id можно получить записи, хранящиеся в столбцах «Клиент» и «Телефон».

Создадим заготовку таблицы:

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

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

Для выбора клиента используем следующую формулу в ячейке F2:

=ГПР(B1;A1:C11;E2+1)

Для выбора номера телефона используем следующую формулу (с учетом возможного отсутствия записи) в ячейке G2:

Функция ЕСЛИ выполняет проверку возвращаемого значения. Если искомая ячейка не содержит данных, будет возвращена строка «Не указан».

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

Интерактивный отчет для анализа прибыли и убытков в Excel

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

Вид таблиц данных с выпадающим списком в ячейке E2 (как сделать выпадающий список смотрите в примере выше):

В ячейку F2 запишем следующую формулу:

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

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

Функция ЕСЛИ сравнивает возвращаемые функциями ГПР значения и возвращает один из вариантов текстовых строк.

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

Особенности использования функции ГПР в Excel

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

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

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

• искомое_значение – обязательный аргумент, принимающий данные любого типа, характеризующие элемент первой строки константы массива или ячейку, расположенную в первой строке диапазона ячеек. При работе с именованными областями данных (списки, таблицы) в качестве данного аргумента указывают название поля (столбца), в котором содержится возвращаемое значение.
• таблица – обязательный аргумент, принимающий константу массива или ссылку на диапазон ячеек, в котором находится список или таблица. Если первой строкой диапазона является шапка таблицы с текстовыми названиями полей, при поиске требуемого названия поля не учитывается регистр символов. Для корректной работы функции ГПР необходимо, чтобы содержащиеся в первой строке значения были отсортированы слева направо.
• номер_строки – обязательный аргумент, характеризующийся числовым значением из диапазона целых положительных чисел, которое соответствует номеру строки относительно первой строки массива или диапазона ячеек, переданных в качестве аргумента таблица. Функция ГПР возвращает результат, взятый из строки таблицы с номером, указанным данным аргументом. Ошибки могут возникать в следующих случаях:

1. В качестве аргумента номер_строки передано число <1. В этом случае будет возвращен код ошибки #ЗНАЧ!.
2. Передано число, превышающее значение номера последней записи (строки) в таблице или массиве. Возвращаемое значение – код ошибки #ССЫЛ!.

• [интервальный_просмотр] – необязательный для заполнения аргумент, принимающий логические значения:

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

Примечания:

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

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

Функция ГПР – подробное описание

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

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

=ГПР(«Дата рождения»;A1:B10;4)

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

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

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

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

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

Аргументы

Давайте более детально рассмотрим каждый из этих аргументов:

1. Искомое значение. Этот аргумент необходимо указывать в обязательном порядке. Это те данные, поиск которых нам нужно осуществлять по горизонтали. Форматы могут быть разными: текстовый, ссылка или же число.
2. Таблица. Простыми словами, массив данных, в которых будет осуществляться поиск. Есть несколько вариантов, как можно записывать этот параметр: в виде текста, числа или логического значения. Также можно указывать как имя диапазона, так и ссылки. Важно учитывать то, что если используется строка текстового формата, то не учитывается то, большая буква или маленькая там написана. Сортировка значений в искомом диапазоне осуществляется таким же образом, как мы читаем книгу. То есть, слева направо. 
3. Номер строки. Здесь задается номер строки, из которого будет получено значение. Минимальное значение номера строки, которое допускается использовать в этой формуле – 1. Если указать меньшее, то будет возвращена ошибка #ЗНАЧ! Также нужно следить, чтобы количество строк в прошлом аргументе не было меньше, чем в третьем аргументе. Если допустить это, будет возвращен результат #ССЫЛ!
4. Интервальный просмотр. Этот аргумент является необязательным. Содержит логическое значение, исходя из которого Excel понимает, ему использовать точное вхождение или приблизительное. В случае истинного значения, функция ищет похожие соответствия, но не полные. Если же значение ложное, то функцией осуществляется поиск точно такого же значения, которое было указано пользователем. В случае неудачи при поиске, возвращается ошибка #Н/Д.

Замечания

При работе с функцией ГПР необходимо обратить внимание на следующие нюансы:

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

Особенности использования функции ГПР

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

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

1. ПОИСК. Это наиболее простая функция, которая ищет заданную строку с учетом регистра. При этом она не способна найти, например, второе, третье и так далее вхождение, ограничиваясь исключительно первым. Также есть похожая функция, которая называется ПОИСКБ. Ее основное отличие в том, что ориентируется она не на количество символов, а на количество байтов. Но в кириллических языках такой необходимости нет. Также в этой функции есть возможность использовать подстановочные знаки типа ?, *. Если же нужно искать конкретный знак вопроса или звездочку, то перед этим символом нужно поставить знак тильды. Если же нужное значение найти не удается, то выдается ошибка #ЗНАЧ!
2. НАЙТИ. По сути, функция, дублирующая ПОИСК за тем лишь исключением, что она учитывает регистр и не поддерживает подстановочные символы. 
3. ИНДЕКС. Синтаксис этой функции сильно отличается от того, как мы ее будем использовать. А спектр возможностей поистине огромный. Это и получение нужных данных по номеру, и извлечение нужной информации из двухмерного диапазона, и возможность осуществлять поиск исключительно в конкретной таблице, если их несколько. Кроме этого, она дает возможность давать непосредственно ссылку на нужную колонку или строку, а также ячейку.
4. ПОИСКПОЗ. Это еще одна функция, которая может использоваться для различных целей с различным синтаксисом. Она используется для того, чтобы осуществлять поиск позиции ячейки с определенным текстом. Пользователь может точно определить, какой именно текст будет искаться. Кроме этого, данная функция нередко используется для поиска первой или последней ячейки, а также осуществлять поиск по датам. Например, с целью определить этап проекта.
   Нередко функция ПОИСКПОЗ используется в сочетании с ИНДЕКС. В таком случае она действует очень похожим на функцию ВПР (не ГПР) образом, осуществляя поиск нужных данных по вертикали. В частности, она повторяет функционал интервального просмотра как в ВПР, так и в ГПР, поэтому она часто используется для замены их функционала. При этом набор особенностей даже шире по сравнению с ГПР, поскольку она дает возможность выбирать наибольшее или наименьшее ближайшее соответствие (если речь идет о поиске чисел). Правда, по синтаксису эта функция тоже сложнее.

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

ГПР для выборки по нескольким условиям

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

 Чтобы упростить использование функции, давайте в ячейке E2 создадим выпадающий список. Чтобы это сделать, необходимо воспользоваться инструментом «Работа с данными – Проверка данных». Его можно найти на вкладке «Данные». 

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

Как мы видим, в колонке, где описываются клиенты, была прописана следующая формула:

=ГПР(B1;A1:C11;E2+1)

С ее помощью программа автоматически выдает клиента, основываясь на его идентификационном номере. 

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

=ЕСЛИ(ГПР(C1;A1:C11;E2+1)=»»;»Не указан»;ГПР(C1;A1:C11;E2+1))

Давайте более подробно распишем эту формулу. С помощью функции ЕСЛИ мы осуществляем проверку значения, которое возвращается в первую ячейку (ту, в которую записывается идентификатор). Если оказывается, что номера нет, то возвращается сообщение, что клиент его не указал. 

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

Интерактивный отчет для анализа прибыли и убытков в Эксель

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

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

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

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

Теперь давайте запишем формулу со скриншота ниже.

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

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

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

Далее используем нашу формулу с аргументами, приведенными на этом скриншоте.

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

1. A1 – это ссылка на ячейку, в которой содержится название поля таблицы.
2. A1:C11 – это исходная таблица, которая используется для получения информации.
3. E2+1 – цифровое обозначение строки таблицы с учетом шапки. Если не прибавить единицу, то будут отображаться данные, которые берут за основу неправильный айди, который будет на единицу меньше. Все дело в том, что первая строка нашей таблицы зарезервирована под шапку, содержащую названия колонок.

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

Таким образом, возможностей для поиска нужных значений в программе Excel действительно огромное количество. И с каждой новой версией офисного пакета этот набор только увеличивается. Что нас ждет дальше? Неизвестно. Но точно нужно быть уверенным, что все будет отлично. Ведь как говорят эксперты по финансам, Excel и Powerpoint – две самые популярные и эффективные программы для того, чтобы генерировать деньги. Если правильно научиться пользоваться электронными таблицами, то специалист будет невероятно востребованным, независимо от сферы, в которой он работает.

Функция ГПР

​Смотрите также​ определилась что использовать​ и от количества).​​Где ищем.​​ — ЛОЖЬ. Т.к.​

Описание

​Примеры анализов прогнозирование​ Excel пошаговая инструкция​Найдем текст, который начинается​ (ЛОЖЬ/0 – точное;​ необходимо знать​ листа​ Microsoft Excel и​Цену​TRUE​E2​ «П» найти не​ искать функция ГПР​В этой статье описаны​ ГПР() или СМЕЩ()​ Для удобства ссылок​Какие данные берем.​ нам нужны точные,​ будущих показателей с​ для чайников.​

​ или заканчивается определенным​ ИСТИНА/1/не указано –​

Синтаксис

​Математические функции Excel, которые​

​Закрепление областей в Microsoft​ не только. И​

• ​coffee mug​​(ИСТИНА) или​, но Вы можете​ удалось, возвращается ближайшее​ — точное или​ синтаксис формулы и​Natali​

• ​ присвойте имена диапазонам​​Допустим, какие-то данные у​ а не приблизительные​ помощью функции ПРЕДСКАЗ​Практическое применение функции​ набором символов. Предположим,​ приблизительное).​

  • ​ необходимо знать​ Excel​ все это в​Категорию​

  • ​FALSE​ использовать любую свободную​ из меньших значений:​ приблизительное. Если этот​ использование функции​: определилась ГПР))) через​ на рабочем листе​ нас сделаны в​ значения.​ при определенных условиях.​ ГПР для выборки​ нам нужно отыскать​! Если значения в​Текстовые функции Excel в​Разделение листов и просмотр​ одном месте!​

  • ​landscape painting​(ЛОЖЬ). Если​

  • ​ ячейку. Как и​ «Оси» (в столбце​ аргумент имеет значение​ГПР​ смещение я знала​ ”Прейскурант”: блок с​

• ​ виде раскрывающегося списка.​​Нажимаем ОК. А затем​ Как спрогнозировать объем​ значений из таблиц​ название компании. Мы​ диапазоне отсортированы в​ примерах​ книги Excel в​Знакомство с Excel​Цену​TRUE​ с любой формулой​ A).​ ИСТИНА или опущен,​в Microsoft Excel.​ и через если..​ перечислением товаров назовите​ В нашем примере​ «размножаем» функцию по​ продаж или спрос​ по условию. Примеры​ забыли его, но​

• ​ возрастающем порядке (либо​​Обзор ошибок, возникающих в​ разных окнах​Интерфейс Microsoft Excel​serving bowl​(ИСТИНА), формула будет​ в Excel, начинаем​5​ возвращается приблизительное соответствие;​Выполняет поиск значения в​ а вот имено​ ”товар”, блок с​ – «Материалы». Необходимо​ всему столбцу: цепляем​ на товары в​ использования функции ГПР​ помним, что начинается​ по алфавиту), мы​ формулах Excel​Сортировка данных в Excel​Лента в Microsoft Excel​

Примечание

• ​Категорию​ искать приблизительное совпадение.​ со знака равенства​=ГПР(«Болты»;A1:C4;4)​ при отсутствии точного​ первой строке таблицы​ ГПР да и​

• ​ ценами за единицу​ настроить функцию так,​ мышью правый нижний​ Excel? 1 2​ для начинающих пользователей.​ с Kol. С​

• ​ указываем ИСТИНА/1. В​Работа с именами в​Сортировка в Excel –​Представление Backstage в Excel​s​ Данный аргумент может​ (=). Далее вводим​Поиск слова «Болты» в​ соответствия возвращается наибольшее​ или массив значений​ еще с определением​ продукции – ”цены”.​ чтобы при выборе​ угол и тянем​ 3 4 5​Примеры функции ВПР в​

Пример

​ задачей справится следующая​ противном случае –​ Excel​ основные сведения​Панель быстрого доступа и​carf​ иметь такое значение,​ имя функции. Аргументы​ строке 1 и​ из значений, меньших,​ и возвращает значение,​ зон было не​ При выполнении задания​

​ наименования появлялась цена.​	​ вниз. Получаем необходимый​	​ 6 7 8​
​ Excel для выборки​	​ формула: .​	​ ЛОЖЬ/0.​
​Знакомство с именами ячеек​	​Пользовательская сортировка в Excel​	​ режимы просмотра книги​
​Теперь Вам известны основы​	​ только если первый​	​ должны быть заключены​
​ возврат значения из​	​ чем «искомое_значение». Если​	​ находящееся в том​
​ понятно ну и​	​ пользуйтесь функциями ГПР​Сначала сделаем раскрывающийся список:​ результат.​ 9 10 11​ значений по условию.​Нам нужно отыскать название​	​​
​ и диапазонов в​	​Уровни сортировки в Excel​Создание и открытие рабочих​ работы с​ столбец содержит данные,​ в круглые скобки,​ строки 4, находящейся​	​ этот аргумент имеет​
​ же столбце в​	​ плюс я ценой​ и ПОИСКПОЗ из​Ставим курсор в ячейку​Теперь найти стоимость материалов​ 12 13 14​Примеры работы функции​ компании, которое заканчивается​Для учебных целей возьмем​ Excel​Фильтрация данных в Excel​ книг​	​функцией ВПР в Excel​
​ упорядоченные по возрастанию.​	​ поэтому открываем их.​ в том же​ значение ЛОЖЬ, функция​ заданной строке таблицы​ обзывала тока столбец​ категории ”Ссылки и​	​ Е8, где и​
​ не составит труда:​	​ 15 16 17​ ВПР для вертикального​ на — «uda».​ таблицу с данными:​Как присвоить имя ячейке​Фильтр в Excel —​Создание и открытие рабочих​. Продвинутые пользователи используют​ Так как мы​ На этом этапе​ столбце (столбец C).​ ГПР ищет точное​ или массива. Функция​ с ценами за​ массивы”.​ будет этот список.​	​ количество * цену.​

support.office.com

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

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

​ Excel​Расширенный фильтр в Excel​Режим совместимости в Excel​самыми различными способами,​ то наш четвёртый​​ получиться вот что:​​=ГПР(3;{1;2;3:»a»;»b»;»c»;»d»;»e»;»f»};2;ИСТИНА)​ его не удается,​​ сравниваемые значения расположены​​ не понятно было​ не могу разобраться​ Меню «Проверка данных».​ таблицы. Если поменяется​ одной таблицы переставить​ строкам. Сравнение двух​

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

​Найдем компанию, название которой​​Результат​​5 полезных правил и​Работа с группами и​Сохранение книг и общий​ но, на самом​ аргумент будет равен​=VLOOKUP(​Поиск числа 3 в​

​ возвращается значение ошибки​ в первой строке​​ )))​​ с ссылками. Заранее​​Выбираем тип данных –​​ прайс, то и​ в соответствующие ячейки​ таблиц по условию.​​ начинается на «Ce»​​Функция ищет значение ячейки​ рекомендаций по созданию​ подведение итогов​​ доступ​​ деле, многое можно​FALSE​=ВПР(​ трех строках константы​ #Н/Д.​

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

​ возвращаемые — на​
​: а сейчас на​

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

​ ответит.​ диапазон с наименованиями​​ на склад материалов​​ наименование – VLOOKUP.​ учетом критериев поиска.​

​ –»sef». Формула ВПР​​ А2:С10 и возвращает​ диапазонов в Excel​ в Excel​ в Excel​​ теми техниками, что​​ аргументы заканчиваются, поэтому​ сообщают функции​ значения из строки​ может найти «искомое_значение»​

​ несколько строк ниже.​
​ примере посмотрела и​

​Гость​​ материалов.​ (сегодня поступивших). Чтобы​Очень удобная и часто​Функции БСЧЁТ и БСЧЁТА​ будет выглядеть так:​​ значение ячейки F5,​​Диспетчер имен в Excel​Таблицы в Excel​Экспорт книг Excel​ мы описали. Например,​ закрываем скобки:​ВПР​ 2 того же​ и аргумент «интервальный_просмотр»​ Если сравниваемые значения​

​ ясно стало что​
​: Необходимо использовать комбинацию​

​Когда нажмем ОК –​​ этого избежать, воспользуйтесь​​ используемая. Т.к. сопоставить​​ для счета с​​ .​ найденное в 3​ – инструменты и​Создание, изменение и удаление​​Общий доступ к книгам​​ если у Вас​​=VLOOKUP(«Photo frame»,A2:B16,2,FALSE)​​, что и где​ (в данном случае —​ имеет значение ИСТИНА,​ находятся в столбце​ зону обзываешь чтоб​

​ функций СМЕЩ и​​ сформируется выпадающий список.​ «Специальной вставкой».​ вручную диапазоны с​ несколькими условиями в​Когда проблемы с памятью​ столбце, точное совпадение.​​ возможности​​ таблиц в Excel​​ Excel​​ есть список контактов,​=ВПР(«Photo frame»;A2:B16;2;ЛОЖЬ)​ искать.​ третьего) столбца. Константа​ используется наибольшее из​ слева от искомых​ потом в формуле​ ПОИСКПОЗ​​Теперь нужно сделать так,​​Выделяем столбец со вставленными​

​ десятками тысяч наименований​
​ Excel.​

​ устранены, можно работать​​Нам нужно найти, продавались​​Как присваивать имена константам​​Диаграммы и спарклайны​Основы работы с ячейками​ то Вы сможете​Готово! После нажатия​​Первый аргумент​​ массива содержит три​​ значений, меньших, чем​​ данных, используйте функцию​​ этот диапозон не​​создаете лист1 из​ чтобы при выборе​ ценами.​ проблематично.​Примеры работы функций​ с данными, используя​ ли 04.08.15 бананы.​ в Excel?​Диаграммы в Excel –​Ячейка в Excel —​ найти телефонный номер​​Enter​​– это имя​ строки значений, разделенных​ «искомое_значение».​

​ ВПР.​
​ выделять) Еще раз​

​ 4х столбцов: Наименование|​​ определенного материала в​​Правая кнопка мыши –​Допустим, на склад предприятия​​ БСЧЁТ и БСЧЁТА​​ все те же​

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

​ Если продавались, в​Работа с массивами в​ основные сведения​ базовые понятия​ человека по его​, Вы должны получить​ элемента, который Вы​ точкой с запятой​​Если значение аргумента «искомое_значение»​​Буква Г в аббревиатуре​ всем огромное спасибо!​ 1 | 30​

​ графе цена появлялась​​ «Копировать».​​ по производству тары​​ для подсчета количества​​ функции.​​ соответствующей ячейке появится​​ Excel​​Макет, стиль и прочие​​Содержимое ячеек в Excel​​ имени. Если же​​ ответ:​​ ищите, в нашем​

​ (;). Так как​ меньше, чем наименьшее​ «ГПР» означает «горизонтальный».​ru​

​ | 50 (пусть​
​ соответствующая цифра. Ставим​

​Не снимая выделения, правая​

​ и упаковки поступили​
​ значений в базе​

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

​У нас есть данные​ слово «Найдено». Нет​Знакомство с формулами массива​ параметры диаграмм​Копирование, перемещение и удаление​ в списке контактов​9.99​ примере это​ «c» было найдено​

​ значение в первой​ГПР(искомое_значение;таблица;номер_строки;[интервальный_просмотр])​: Есть вопрос по​ это будут столбцы​ курсор в ячейку​​ кнопка мыши –​​ материалы в определенном​ данных по множественным​ о продажах за​ – «Не найдено».​​ в Excel​​Как работать со спарклайнами​ ячеек в Excel​

​ есть столбец с​
​.​

​Photo frame​​ в строке 2​​ строке аргумента «таблица»,​Аргументы функции ГПР описаны​​ поводу ПОИСКПОЗ. Я​​ A, B,C,D). В​​ Е9 (где должна​​ «Специальная вставка».​

​ количестве.​ условиям. Как искать​ январь и февраль.​Если «бананы» сменить на​

• ​Многоячеечные формулы массива в​​ в Excel​
• ​Автозаполнение ячеек в Excel​​ адресом электронной почты​
• ​Давайте разберёмся, как работает​​. Так как аргумент​
• ​ того же столбца,​​ функция ГПР возвращает​​ ниже.​

​ делаю отчет по​ них перечисляете свой​​ будет появляться цена).​​Поставить галочку напротив «Значения».​​Стоимость материалов – в​​ текст с неточным​ Эти таблицы необходимо​ «груши», результат будет​ Excel​Работа с примечаниями и​Поиск и замена в​ или названием компании,​ эта формула. Первым​ текстовый, мы должны​ что и 3,​ значение ошибки #Н/Д.​Искомое_значение​ месяцам, исходная информация​ список номенклатуры и​Открываем «Мастер функций» и​ ОК.​ прайс-листе. Это отдельная​ совпадением? ​ сравнить с помощью​ «Найдено»​Одноячеечные формулы массива в​ отслеживание исправлений​ Excel​ Вы можете искать​

​ делом она ищет​ заключить его в​
​ возвращается «c».​
​Если аргумент «интервальный_просмотр» имеет​

​     — обязательный аргумент. Значение,​

office-guru.ru

Самоучитель по Microsoft Excel для чайников

​ представлена не месяцами,​ прайс-лист. Далее задействуем​​ выбираем ВПР.​Формула в ячейках исчезнет.​ таблица.​Примеры функций ИНДЕКС и​ формул ВПР и​Когда функция ВПР не​ Excel​Отслеживание исправлений в Excel​Изменение столбцов, строк и​ и эти данные,​ заданное значение в​ кавычки:​c​ значение ЛОЖЬ и​ которое требуется найти​

​ а конкретными датами.То​ еще 2 столбца​Первый аргумент – «Искомое​ Останутся только значения.​Необходимо узнать стоимость материалов,​ ПОИСКПОЗ по нескольким​ ГПР. Для наглядности​ может найти значение,​Массивы констант в Excel​Рецензирование исправлений в Excel​ ячеек​ просто изменив второй​ первом столбце таблицы,​=VLOOKUP(«Photo frame»​Многие наши ученики говорили​ аргумент «искомое_значение» является​ в первой строке​ есть надо ,​ F и G​

• ​ значение» — ячейка​​ поступивших на склад.​
• ​ критериям Excel.​ мы пока поместим​ она выдает сообщение​
• ​Редактирование формул массива в​Примечания к ячейкам в​Изменение ширины столбцов и​ и третий аргументы,​ выполняя поиск сверху​=ВПР(«Photo frame»​ нам, что очень​
• ​ текстом, в аргументе​ таблицы. «Искомое_значение» может​ чтобы змечательный, умный​ для количества и​ с выпадающим списком.​Функция помогает сопоставить значения​ Для этого нужно​Совместное использование функций​ их на один​

Раздел 1: Основы Excel

1. ​ об ошибке #Н/Д.​
   • ​ Excel​
   • ​ Excel​
   • ​ высоты строк в​
   • ​ как мы уже​ вниз (вертикально). Когда​
2. ​Второй аргумент​ хотят научиться использовать​
   • ​ «искомое_значение» можно использовать​ быть значением, ссылкой​
   • ​ Excel помог собирать​
3. ​ цены соответственно.​ Таблица – диапазон​
   • ​ в огромных таблицах.​ подставит цену из​
   • ​ ИНДЕКС и ПОИСКПОЗ​
   • ​ лист. Но будем​ Чтобы этого избежать,​
4. ​Применение формул массива в​
   • ​Завершение и защита рабочих​ Excel​
   • ​ делали в предыдущем​
   • ​ находится значение, например,​– это диапазон​
   • ​ функцию​
   • ​ подстановочные знаки: вопросительный​ или текстовой строкой.​
5. ​ инфу именно в​Итого имеем -​
   • ​ с названиями материалов​ Допустим, поменялся прайс.​ второй таблицы в​
   • ​ в Excel –​ работать в условиях,​ используем функцию ЕСЛИОШИБКА.​
   • ​ Excel​ книг​Вставка и удаление строк​
   • ​ примере. Возможности Excel​Photo frame​
6. ​ ячеек, который содержит​
   • ​ВПР​
   • ​ знак (?) и​Таблица​
   • ​ столбец конкретного месяца.​ строка «1» занята​
   • ​ и ценами. Столбец,​
7. ​ Нам нужно сравнить​ первую. И посредством​
   • ​ мощный инструмент поиска​ когда диапазоны находятся​
   • ​Мы узнаем, были​Подходы к редактированию формул​Завершение работы и защита​
   • ​ и столбцов в​
8. ​ безграничны!​
   • ​, функция переходит во​ данные. В нашем​
   • ​(VLOOKUP) в Microsoft​ звездочку (*). Вопросительный​     — обязательный аргумент. Таблица,​
9. ​ Пытаюсь использовать функцию​
   • ​ заголовками, в остальных​ соответственно, 2. Функция​
   • ​ старые цены с​ обычного умножения мы​
   • ​ данных. Выборка значений​ на разных листах.​ ли продажи 05.08.15​

Раздел 2: Формулы и функции

1. ​ массива в Excel​
   • ​ рабочих книг в​ Excel​Урок подготовлен для Вас​
   • ​ второй столбец, чтобы​ случае данные содержатся​
   • ​ Excel.​
2. ​ знак соответствует любому​
   • ​ в которой производится​ ПОИСКПОЗ, а есть​
   • ​ заняты столбцы A​ приобрела следующий вид:​
3. ​ новыми ценами.​
   • ​ найдем искомое.​
   • ​ по нескольким критериям​
   • ​Решим проблему 1: сравним​Если необходимо осуществить поиск​
4. ​Настройка интерфейса​
   • ​ Excel​Перемещение и скрытие строк​
   • ​ командой сайта office-guru.ru​
   • ​ найти цену.​
   • ​ в диапазоне​

Раздел 3: Работа с данными

1. ​Функция ВПР​ одному знаку; звездочка​
   • ​ поиск данных. Можно​ более простая формула​
   • ​ — D. В​ .​В старом прайсе делаем​
2. ​Алгоритм действий:​
   • ​ отбора.​ наименования товаров в​
   • ​ значения в другой​
   • ​Как настроить Ленту в​
3. ​Условное форматирование​
   • ​ и столбцов в​Источник: http://www.gcflearnfree.org/excel-tips/how-to-use-excels-vlookup-function/full​
   • ​ВПР​
4. ​A2:B16​– это очень​
   • ​ — любой последовательности​ использовать ссылку на​
5. ​ для решения этой​
   • ​ ячейку G2 вносите​Нажимаем ВВОД и наслаждаемся​
6. ​ столбец «Новая цена».​
   • ​Приведем первую таблицу в​Пример функции ПОИСКПОЗ для​
   • ​ январе и феврале.​ книге Excel, то​
   • ​ Excel 2013​Условное форматирование в Excel​

Раздел 4: Расширенные возможности Excel

1. ​ Excel​Перевел: Антон Андронов​
   • ​– сокращение от​
   • ​. Как и с​
   • ​ полезный инструмент, а​ знаков. Чтобы найти​
2. ​ диапазон или имя​ проблемы?​
   • ​ формулу: =СМЕЩ($B2;0;ПОИСКПОЗ($F2;$B$1:$D$1;1)-1)) .​ результатом.​Выделяем первую ячейку и​
3. ​ нужный нам вид.​
   • ​ поиска совпадения значений​
4. ​ Так как в​ при заполнении аргумента​
   • ​Режим сенсорного управления Лентой​Сводные таблицы и анализ​
   • ​Перенос текста и объединение​Автор: Антон Андронов​
   • ​В​ любой другой функцией​

Раздел 5: Продвинутая работа с формулами в Excel

1. ​ научиться с ним​ какой-либо из самих​
   • ​ диапазона.​Serge_007​
   • ​ Вот само рабочее​Изменяем материал – меняется​ выбираем функцию ВПР.​
   • ​ Добавим столбцы «Цена»​ в Excel.​
2. ​ феврале их больше,​ «таблица» переходим в​
   • ​ в Excel 2013​ данных​ ячеек в Excel​
   • ​Самоучитель по работе в​ертикальный​ Excel, Вы должны​
   • ​ работать проще, чем​ этих знаков, следует​
   • ​Значения в первой строке​: В примере пустая​
3. ​ тело формулы.​ цена:​
   • ​ Задаем аргументы (см.​ и «Стоимость/Сумма». Установим​Примеры работы функции​
   • ​ вводить формулу будем​ другую книгу и​Стили ссылок в Microsoft​
   • ​Общие сведение о сводных​Форматирование ячеек​ Excel для чайников​ПР​
   • ​ вставить разделитель между​ Вы думаете. В​ указать перед ним​
   • ​ аргумента «таблица» могут​ табличка.​
4. ​Чтобы у Вас​
   • ​Скачать пример функции ВПР​ выше). Для нашего​
   • ​ денежный формат для​ ПОИСКПОЗ для поиска​
   • ​ на листе «Февраль».​ выделяем нужный диапазон​ Excel​
5. ​ таблицах в Excel​
   • ​Настройка шрифта в Excel​позволит Вам легко​
   • ​осмотр,​ аргументами (запятая в​
   • ​ этом уроке основы​ знак тильды (~).​
   • ​ быть текстом, числами​Что в ней​
6. ​ не возникало проблем​ в Excel​
   • ​ примера: . Это​ новых ячеек.​ точного и ближайшего​
   • ​Решим проблему 2: сравним​ с данными.​Хотите узнать об Excel​
   • ​Сведение данных, фильтры, срезы​Выравнивание текста в ячейках​ понять и усвоить​VLOOKUP​
   • ​ англоязычной версии Excel​ по работе с​Скопируйте образец данных из​
   • ​ или логическими значениями.​ должно быть?​
7. ​ с нулями в​Так работает раскрывающийся список​
   • ​ значит, что нужно​Выделяем первую ячейку в​
   • ​ большего или меньшего​ продажи по позициям​
   • ​Мы захотели узнать,​ еще больше?​
   • ​ и сводные диаграммы​
   • ​ Excel​ базовые навыки работы​
   • ​– от​ или точка с​
   • ​ функцией​ следующей таблицы и​

Раздел 6: Дополнительно

1. ​Если аргумент «интервальный_просмотр» имеет​
   • ​ru​ столбце F формулу​
   • ​ в Excel с​ взять наименование материала​
   • ​ столбце «Цена». В​ значения в диапазоне​

​ в январе и​ кто работал 8.06.15.​​Специально для Вас​Анализ «что если” в​Границы, заливка и стили​ в Excel, чтобы​V​ запятой – в​ВПР​ вставьте их в​

​ значение ИСТИНА, то​: Извините, сейчас исправлюсь​
​ преобразуем к виду:​

​ функцией ВПР. Все​

office-guru.ru

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

​ из диапазона А2:А15,​ нашем примере –​ ячеек. Как сравнить​ феврале. Используем следующую​Поиск приблизительного значения.​ мы припасли целых​ Excel​ ячеек в Excel​

​ затем уверенно перейти​ertical​ русифицированной версии).​разжеваны самым доступным​ ячейку A1 нового​ значения в первой​

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

​ru​

1. ​ =ЕСЛИ($F2>0;СМЕЩ($B2;0;ПОИСКПОЗ($F2;$B$1:$D$1;1)-1));0) . Копируйте​ происходит автоматически. В​ посмотреть его в​ D2. Вызываем «Мастер​ на совпадение значений​
2. ​ формулу:​Это важно:​ два простых и​Решаем задачи с помощью​Числовое форматирование в Excel​ к более сложным​LOOKUP​=VLOOKUP(«Photo frame»,A2:B16​
3. ​ языком, который поймут​ листа Excel. Чтобы​ строке аргумента «таблица»​: у меня вот​ формулу из ячейки​ течение нескольких секунд.​ «Новом прайсе» в​
4. ​ функций» с помощью​ двух таблиц?​Для демонстрации действия функции​Функция ВПР всегда ищет​ полезных самоучителя: 300​ логических функций​

​Основные сведения о листе​ темам. Самоучитель научит​.​=ВПР(«Photo frame»;A2:B16​ даже полные «чайники».​ отобразить результаты формул,​ должны быть расположены​

​ она заполнена​

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

​ G2 и специальной​ Все работает быстро​

​ столбце А. Затем​	​ кнопки «fx» (в​	​Примеры функции ИНДЕКС для​
	​ ГПР возьмем две​ данные в крайнем​ примеров по Excel​Как задать простое логическое​ Excel​ Вас пользоваться интерфейсом​
	​Если мы захотим найти​Важно помнить, что​ Итак, приступим!​ выделите их и​ в возрастающем порядке:​ru​
	​ вставкой вставляйте формулы​ и качественно. Нужно​ взять данные из​
	​ начале строки формул)​ создания массивов значений​ «горизонтальные» таблицы, расположенные​ левом столбце таблицы​ и 30 функций​ условие в Excel​	​Переименование, вставка и удаление​ Excel, применять формулы​
	​ цену другого товара,​ВПР​Прежде чем приступить к​ нажмите клавишу F2,​ …-2, -1, 0,​: не понимаю, что​ в оставшиеся ячейки​ только разобраться с​	​ второго столбца нового​ или нажав комбинацию​
	​ в Excel.​

​ на разных листах.​

1. ​ со значениями.​ Excel за 30​Используем логические функции Excel​ листа в Excel​
2. ​ и функции для​ то можем просто​всегда ищет в​
3. ​ изучению, Вы должны​ а затем —​ 1, 2, …,​ происходит. у меня​
4. ​ столбца G.​ этой функцией.​ прайса (новую цену)​ горячих клавиш SHIFT+F3.​Примеры работы функции​Задача – сравнить продажи​
5. ​Регистр не учитывается: маленькие​ дней.​ для задания сложных​Копирование, перемещение и изменение​

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

​ решения самых различных​ изменить первый аргумент:​

​первом левом столбце​	​ понять основы работы​	​ клавишу ВВОД. При​
	​ A-Z, ЛОЖЬ, ИСТИНА;​ нормальный файлик, почему​Serge_007​Гость​
	​ и подставить их​ В категории «Ссылки​ ИНДЕКС для динамической​

​ по позициям за​ и большие буквы​Урок подготовлен для Вас​ условий​

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

​ цвета листа в​ задач, строить графики​=VLOOKUP(«T-shirt»,A2:B16,2,FALSE)​указанного диапазона. В​ функций. Обратите внимание​

• ​ необходимости измените ширину​ в противном случае​ то не получается​
• ​: Помочь не трудно.​: Создайте рабочий лист​

1. ​ в ячейку С2.​ и массивы» находим​ выборки и фильтрации​ январь и февраль.​ для Excel одинаковы.​ командой сайта office-guru.ru​Функция ЕСЛИ в Excel​ Excel​ и диаграммы, работать​=ВПР(«T-shirt»;A2:B16;2;ЛОЖЬ)​
2. ​ этом примере функция​ на раздел​ столбцов, чтобы видеть​ функция ГПР может​ отправить. сейчас попробую​
3. ​ Трудно за Вас​ ” Прейскурант” с​Данные, представленные таким образом,​ функцию ВПР и​ значений из исходной​Создаем новый лист «Сравнение».​

​Если искомое меньше, чем​Автор: Антон Андронов​ на простом примере​Группировка листов в Excel​ со сводными таблицами​

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

​или:​ будет искать в​Формулы и функции​ все данные.​ выдать неправильный результат.​ понять в чем​ файл рисовать.​ данными:​ можно сопоставлять. Находить​ жмем ОК. Данную​ таблицы. Как сделать​ Это не обязательное​ минимальное значение в​

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

​Автор: Антон Андронов​Подсчет и суммирование в​Разметка страницы​ и многое другое.​=VLOOKUP(«Gift basket»,A2:B16,2,FALSE)​ столбце​нашего самоучителя по​

​Оси​ Если аргумент «интервальный_просмотр»​ проблема​Пример давайте.​Товар 1 30​

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

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

​ массиве, программа выдаст​Функции ВПР и ГПР​ Excel​

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

​Serge_007​

​Natali​

​ 50​

​ разницу.​

​ перейдя по закладке​

​ диапазона?​ и отображать разницу​ ошибку #Н/Д.​ среди пользователей Excel​Подсчет ячеек в Excel,​ страницы в Excel​ для начинающих пользователей​Следующий пример будет чуть​значение​

​ВПР​

​Болты​ таблица может быть​: Тоже самое.​: вот фаил​Аккумуляторы 300 270​

​До сих пор мы​ «Формулы» и выбрать​

​Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel​ можно на любом​Если задать номер столбца​ очень популярны. Первая​

exceltable.com

Функции Excel с примерами и описанием для практического применения

​ используя функции СЧЕТ​Вставка разрывов страниц, печать​ Excel, точнее для​ потруднее, готовы? Представьте,​Photo frame​работает одинаково во​

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

​4​ не отсортирована.​Вы словами напишите​
​Natali​ 250​ предлагали для анализа​ из выпадающего списка​ для промежуточных расчетов​ листе («Январь» или​​ 0, функция покажет​ применяется для вертикального​ и СЧЕТЕСЛИ​
​ заголовков и колонтитулов​ «полных чайников». Информация​ что в таблице​. Иногда Вам придётся​ всех версиях Excel,​4​В текстовых строках регистр​ что должно получиться.​​: да мне бы​Труба 35 30​ только одно условие​ «Ссылки и массивы».​
​ .​ «Февраль»).​ #ЗНАЧ. Если третий​ анализа, сопоставления. То​Суммирование в Excel, используя​ в Excel​ дается поэтапно, начиная​ появился третий столбец,​​ менять столбцы местами,​ она работает даже​9​
​ букв не учитывается.​ru​ просто понять как​ 25​ – наименование материала.​Откроется окно с аргументами​Примеры работы функции​​Формула:​ аргумент больше числа​ есть используется, когда​
​ функции СУММ и​Печать книг​ с самых азов.​ который хранит категорию​ чтобы нужные данные​ в других электронных​5​Значения сортируются слева направо​​: Напишу,просто я раньше​ использовать вместе эти​Ключ 35 30​
​ На практике же​ функции. В поле​ ПРОМЕЖУТОЧНЫЕ.ИТОГИ для выполнения​.​ столбцов в таблице​ информация сосредоточена в​ СУММЕСЛИ​​Панель Печать в Microsoft​ От раздела к​ каждого товара. На​
​ оказались в первом​ таблицах, например, в​7​ по возрастанию. Дополнительные​ отправляла файлы на​ 2 функции… сижу​ 25​ нередко требуется сравнить​​ «Искомое значение» -​ итоговых расчетов только​Результат:​
​ – #ССЫЛКА.​ столбцах.​Как посчитать накопительную сумму​ Excel​ разделу самоучителя предлагаются​ этот раз, вместо​ столбце.​ Google Sheets.​​10​ сведения см. в​ наш форум без​
​ читаю пока не​Задвижка 30 26​ несколько диапазонов с​ диапазон данных первого​ для видимых ячеек​Проанализируем части формулы:​Чтобы при копировании сохранялся​ГПР, соответственно, для горизонтального.​​ в Excel​Задаем область печати в​ все более интересные​
​ цены, мы определим​Третий аргумент​Прежде всего, функция​6​ разделе Сортировка данных​ проблем. странно……. Итак.​ понятно(​ 22​ данными и выбрать​ столбца из таблицы​ автофильтра. Умные формулы​«Половина» до знака «-»:​ правильный массив, применяем​

exceltable.com

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

​ Так как в​Вычисляем средневзвешенные значения при​ Excel​ и захватывающие вещи.​ категорию.​– это номер​

​ВПР​8​ в диапазоне или​ на разных листах​kim​

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

​В строке “Товар”​ значение по 2,​ с количеством поступивших​ для вычисления промежуточных​. Искомое значение –​

​ абсолютные ссылки (клавиша​ таблицах редко строк​ помощи СУММПРОИЗВ​

​Настройка полей и масштаба​ Пройдя весь курс,​Чтобы определить категорию, необходимо​ столбца. Здесь проще​позволяет искать определённую​11​ таблице.​ книги разбросана инфа.​

​: Так использовать​

1. ​ граничные значения количества​ 3-м и т.д.​ материалов. Это те​ итогов в динамических​ первая ячейка в​ F4).​
2. ​ больше, чем столбцов,​Работа с датами и​ при печати в​ Вы будете уверенно​ изменить второй и​ пояснить на примере,​ информацию в таблицах​Формула​Номер_строки​ в одном столбце​Serge_007​ товара в наличии,​ критериям.​ значения, которые Excel​ таблицах.​ таблице для сравнения.​Для учебных целей возьмем​ функцию эту вызывают​
3. ​ временем в Excel​ Excel​ применять свои знания​ третий аргументы в​ чем на словах.​ Excel. Например, если​Описание​    — обязательный аргумент. Номер​ разные даты, в​: Игорь, там ещё​
4. ​ а ниже –​Таблица для примера:​ должен найти во​Функция НАКЛОН для определения​ Анализируемый диапазон –​ такую табличку:​ нечасто.​Дата и время в​Простые формулы​ на практике и​
5. ​ нашей формуле. Во-первых,​ Первый столбец диапазона​ есть список товаров​Результат​ строки в массиве​ другом столбце суммы​ вот это:​
6. ​ цены за единицу​Предположим, нам нужно найти,​ второй таблице.​ наклона линейной регрессии​ таблица с продажами​Формула​Функции имеют 4 аргумента:​ Excel – основные​Математические операторы и ссылки​ научитесь работать с​

​ изменяем диапазон на​ – это​ с ценами, то​=ГПР(«Оси»;A1:C4;2;ИСТИНА)​ «таблица», из которой​ . Отчет должен​Quote​

​ товара. Для оптовых​ по какой цене​Следующий аргумент – «Таблица».​

​ в Excel.​ за февраль. Функция​Описание​ЧТО ищем – искомый​ понятия​ на ячейки в​ инструментами Excel, которые​A2:C16​

1. ​1​ можно найти цену​
2. ​Поиск слова «Оси» в​ будет возвращено соответствующее​
3. ​ складываться помесячно. то​(Гость)200?’200px’:»+(this.scrollHeight+5)+’px’);»>Для удобства ссылок​ покупателей цены снижаются.​
4. ​ привезли гофрированный картон​ Это наш прайс-лист.​

​Примеры работы функции​ ГПР «берет» данные​

​Результат​

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

​ параметр (цифры и/или​Ввод и форматирование дат​ формулах Excel​ позволят решить 80%​, чтобы он включал​, второй – это​

1. ​ определённого товара.​ строке 1 и​
2. ​ значение. Если значение​ есть мне надо​ присвойте имена диапазонам​ Например, если покупатель​ от ОАО «Восток».​ Ставим курсор в​ НАКЛОН для анализа​ из 2 строки​Поиск значения ячейки I16​ текст) либо ссылка​ и времени в​Создание простых формул в​ всех Ваших задач.​ третий столбец. Далее,​2​Сейчас мы найдём при​

​ возврат значения из​ аргумента «номер_строки» равно​ январь, февраль и​ на рабочем листе​

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

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

​и так далее.​

​ помощи​ строки 2, находящейся​ 1, возвращается значение​ т.д., а исходная​ ”Прейскурант”: блок с​ 6 аккумуляторов, он​ условия для поиска​ на лист с​

​ и объема производимых​После знака «-»:​ из третьей строки​

1. ​ искомым значением;​Функции для извлечения различных​Редактирование формул в Excel​Вы навсегда забудете вопрос:​
2. ​ на​ В нашем примере​
3. ​ВПР​ в том же​ из первой строки​ информация (например) 01.01.2011,​ перечислением товаров назовите​

​ платит по 300​

1. ​ по наименованию материала​
2. ​ ценами. Выделяем диапазон​
3. ​ товаров. Прогнозирование уровня​

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

​. Все то же​ того же столбца.​ГДЕ ищем – массив​ параметров из дат​Сложные формулы​ «Как работать в​3​ требуется найти цену​

​цену товара​

1. ​ столбце (столбец A).​ аргумента «таблица», если​ 15.02. 2011 и​
2. ​ ”товар”, блок с​ руб., а если​
3. ​ и по поставщику.​ с наименованием материалов​ объема продаж с​ самое. Кроме диапазона.​
4. ​Еще один пример поиска​ данных, где будет​

​ и времени в​Знакомство со сложными формулами​ Excel?»​, поскольку категории содержатся​ товара, а цены​Photo frame​4​ оно равно 2​

1. ​ т.д.​ ценами за единицу​
2. ​ партия составит 30​Дело осложняется тем, что​ и ценами. Показываем,​ помощью наклона линейной​ Здесь берется таблица​ точного совпадения в​ производиться поиск (для​ Excel​ в Excel​
3. ​Теперь никто и никогда​ в третьем столбце.​

​ содержатся во втором​. Вероятно, Вы и​

​=ГПР(«Подшипники»;A1:C4;3;ЛОЖЬ)​ — из второй​

​Serge_007​ продукции – ”цены”​ штук, то он​ от одного поставщика​ какие значения функция​ регрессии.​ с продажами за​ другой табличке.​ ВПР – поиск​

exceltable.com

Использование функции ГПР и ПОИСКПОЗ

​Функции для создания и​​Создание сложных формул в​ не посмеет назвать​=VLOOKUP(«Gift basket»,A2:C16,3,FALSE)​
​ столбце. Таким образом,​ без того видите,​
​Поиск слова «Подшипники» в​ строки и т.​
​: ЧТО ДОЛЖНО ПОЛУЧИТЬСЯ​было​
​ платит по 270​ поступает несколько наименований.​
​ должна сопоставить.​Округление до целого числа​
​ январь.​Применение ГПР на практике​ значения осуществляется в​ отображения дат и​ Microsoft Excel​ Вас «чайником».​=ВПР(«Gift basket»;A2:C16;3;ЛОЖЬ)​ нашим третьим аргументом​ что цена товара​ строке 1 и​ д. Если значение​ В ИТОГЕ?​Natali​ руб. На следующей​Добавляем в таблицу крайний​Чтобы Excel ссылался непосредственно​ функциями ОКРУГЛ и​Скачать примеры использования функций​
​ ограничено, так как​ ПЕРВОМ столбце таблицы;​
​ времени в Excel​
​Относительные и абсолютные ссылки​
​Не нужно покупать никчемные​

​Когда Вы нажмёте​ будет значение​$9.99​ возврат значения из​ аргумента «номер_строки» меньше​Какие цифры должны​: спасибо огромное!!! а​ таблице ”Накладная ”​ левый столбец (важно!),​ на эти данные,​ FormatNumber в VBA.​ ВПР и ГПР​ горизонтальное представление информации​ для ГПР –​Функции Excel для вычисления​Относительные ссылки в Excel​ самоучители для начинающих,​Enter​2​

​, но это простой​ строки 3, находящейся​ 1, функция ГПР​ быть в С4,​ цена получается вся​

​ составьте таблицу​​ объединив «Поставщиков» и​ ссылку нужно зафиксировать.​Решение проблем с​
​Когда мы вводим формулу,​ используется очень редко.​ в ПЕРВОЙ строке);​ дат и времени​Абсолютные ссылки в Excel​ которые затем будут​, то увидите, что​.​ пример. Поняв, как​ в том же​ возвращает значение ошибки​ С5, С6: и​ таблица с ценами?​
​Товар Количество Цена​ «Материалы».​ Выделяем значение поля​ округлением чисел до​ Excel подсказывает, какой​Случается, пользователь не помнит​НОМЕР столбца/строки – откуда​Поиск данных​Ссылки на другие листы​
​ годами пылиться на​ товар​=VLOOKUP(«Photo frame»,A2:B16,2​ работает функция​ столбце (столбец B).​ #ЗНАЧ!; если оно​ С7?​ там вроде только​ за шт. Цена​Таким же образом объединяем​ «Таблица» и нажимаем​

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

​ в Excel​​ полке. Покупайте только​

​Gift basket​​=ВПР(«Photo frame»;A2:B16;2​ВПР​7​ больше, чем количество​ru​ столбец где за​

​Труба 36​​ искомые критерии запроса:​

​ F4. Появляется значок​​ функцию ОКРУГЛ и​ ввести.​

​ искомое значение, он​​ значение (1 –​ на простых примерах​Формулы и функции​ стоящую и полезную​находится в категории​Четвёртый аргумент​, Вы сможете использовать​=ГПР(«П»;A1:C4;3;ИСТИНА)​

​ строк в аргументе​

​: В итоге должно​​ еденицу товара… или​Ключ 56​Теперь ставим курсор в​ $.​ VBA-функции FormatNumber или​Примеры применения сложных​ может применить символы​ из первого столбца​

​Функция ПРОСМОТР в Excel​​Знакомство с функциями в​​ литературу!​​Gifts​сообщает функции​ ее в более​

​Поиск буквы «П» в​​ «таблица», возвращается значение​ быть B5 ЯНВАРЬ​ это я не​Задвижка 12​ нужном месте и​В поле аргумента «Номер​ Round. Алгоритм банковского​ формул и функций​ подстановки:​ или первой строки,​ на простом примере​ Excel​На нашем сайте Вы​.​

​ВПР​​ сложных таблицах, и​ строке 1 и​ ошибки #ССЫЛ!.​ C5 500р. B6​ так понимаю​Во втором и третьем​ задаем аргументы для​ столбца» ставим цифру​

​ округления до целого​​ в программе Excel​«?» — заменяет любой​ 2 – из​Функции ИНДЕКС и ПОИСКПОЗ​Вставляем функцию в Excel​ найдете еще множество​Если хотите попрактиковаться, проверьте,​, нужно искать точное​ тогда она окажется​ возврат значения из​Интервальный_просмотр​ Февраль С6 700​kim​ столбце автоматически выставляется​ функции: . Excel​ «2». Здесь находятся​

​ числа.​​ с подробным описанием.​ символ в текстовой​
​ второго и т.д.);​ в Excel на​

​Библиотека функций в Excel​​ самых различных курсов,​

​ сможете ли Вы​​ или приблизительное совпадение.​ действительно полезной.​

​ строки 3, находящейся​​    — необязательный аргумент. Логическое​ р. пробую еще​: Там еще​ цена единицы товара​ находит нужную цену.​ данные, которые нужно​Функция ПРЕДСКАЗ для прогнозирования​

​ Управление данными в​​ или цифровой информации;​
​ИНТЕРВАЛЬНЫЙ ПРОСМОТР – точное​ простых примерах​

​Мастер функций в Excel​​ уроков и пособий​ найти данные о​ Значением аргумента может​Мы вставим формулу в​ в том же​ значение, которое определяет,​ раз выслать файл​Natali​ в соответствии с​Рассмотрим формулу детально:​ «подтянуть» в первую​ будущих значений в​ электронных таблицах.​«*» — для замены​ или приблизительное значение​Полезно знать​Управление внешним видом рабочего​

​ по работе в​​ товарах:​ быть​
​ ячейку​ столбце. Так как​ какое соответствие должна​ (а вдруг?)​

​не до конца​​ прейскурантом (в зависимости​Что ищем.​ таблицу. «Интервальный просмотр»​ Excel.​Примеры функции ГПР в​ любой последовательности символов.​ должна найти функция​

excelworld.ru

​Статистические функции Excel, которые​

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

Кстати, в образовательном центре “РУНО” есть практический курс Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности, на котором можно узнать всё про вычисления в программе с помощью функций, про условное форматирование, функциях ГПР, ВПР и множестве других полезных инструментов.

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

Содержание:

1. Что такое функция ГПР;
2. Пример использования;
3. Разница между ГПР и ВПР.

Что такое функция ГПР


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

В англоязычной версии Excel функция ГПР называется HLOOKUP и дословно переводится как «горизонтальный поиск».

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

Важно знать!

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



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


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

Наша задача: проставить дату экзаменов в списке учеников класса. Для этого в столбце с датой пропишем формулу с функцией ГПР. Итак, мы будем искать название экзамена В2 в таблице с датами — диапазон F4:P5.

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

Нажимаем ОК и  получаем дату экзамена у первого ученика.

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

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

Более сложные ситуации на конкретных примерах мы рассматриваем на дистанционном практическом курсе Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности. Записавшись на наш курс вы овладеете всеми возможными навыками, облегчающими и ускоряющими работу с данными.

Разница между ГПР и ВПР

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

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

СМОТРИТЕ ВИДЕОУРОКИ ПО ТЕМЕ:

СТАТЬИ ПО ТЕМЕ:

Сортировка в Excel. Автоматизируем свою работу

Как сделать маркированный список в Word. Краткая инструкция

Как сделать фильтр в Excel. Разбираем на примере

КАТАЛОГ КУРСОВ ПО EXCEL:

Microsoft Excel 2016/2019. Уровень 1+2. Профессионал

Microsoft Excel 2016/2019. Анализ и прогнозирование данных. Уровень 3. Эксперт

Microsoft Excel 2016/2019. Уровень 1+2+3. От Новичка до Эксперта