Функция Гпр в Excel Пошаговая Инструкция • Выделим сезонность
Как сделать функцию просмотр в excel?
Варианты записи функции ПРОСМОТР
Начнем с того, что функция ПРОСМОТР имеет две формы записи: векторная и массив. Вводя функцию на рабочий лист, Excel напоминает Вам об этом следующим образом:
Форма массива
Форма массива очень похожа на функции ВПР и ГПР. Основная разница в том, что ГПР ищет значение в первой строке диапазона, ВПР в первом столбце, а функция ПРОСМОТР либо в первом столбце, либо в первой строке, в зависимости от размерности массива. Есть и другие отличия, но они менее существенны.
Данную форму записи мы подробно разбирать не будем, поскольку она давно устарела и оставлена в Excel только для совместимости с ранними версиями программы. Вместо нее рекомендуется использовать функции ВПР или ГПР.
Векторная форма
Функция ПРОСМОТР (в векторной форме) просматривает диапазон, который состоит из одной строки или одного столбца. Находит в нем заданное значение и возвращает результат из соответствующей ячейки второго диапазона, который также состоит из одной строки или столбца.
Вот это да! Это ж надо такое понаписать… Чтобы стало понятней, рассмотрим небольшой пример.
Пример 1
На рисунке ниже представлена таблица, где указаны номера телефонов и фамилии сотрудников. Наша задача по фамилии сотрудника определить его номер телефона.
В данном примере функцию ВПР не применить, поскольку просматриваемый столбец не является крайним левым. Именно в таких случаях можно использовать функцию ПРОСМОТР. Формула будет выглядеть следующим образом:
Пример 2
Функцию ПРОСМОТР в Excel удобно использовать, когда векторы просмотра и результатов относятся к разным таблицам, располагаются в отдаленных частях листа или же вовсе на разных листах. Самое главное, чтобы оба вектора имели одинаковую размерность.
На рисунке ниже Вы можете увидеть один из таких примеров:
Как видите, диапазоны смещены друг относительно друга, как по вертикали, так и по горизонтали, но формула все равно вернет правильный результат. Главное, чтобы размерность векторов совпадала. Нажав Enter, мы получим требуемый результат:
При использовании функции ПРОСМОТР в Excel значения в просматриваемом векторе должны быть отсортированы в порядке возрастания, иначе она может вернуть неверный результат.
Так вот коротко и на примерах мы познакомились с функцией ПРОСМОТР и научились использовать ее в рабочих книгах Excel. Надеюсь, что данная информация оказалась для Вас полезной, и Вы обязательно найдете ей применение. Всего Вам доброго и успехов в изучении Excel.
Функция ПРОСМОТР в Excel возвращает искомое значение из массива данных, строки либо столбца таблицы. Она позволяет быстро найти искомое значения без необходимости ручного поиска среди больших объемов информации. Особенности использования функции будут указаны ниже в примерах.
Функция ПРОСМОТР в Excel и особенности ее использования
Функция ПРОСМОТР упрощает поиск данных в строке, столбце таблицы и массиве данных наряду с ее аналогами:
Обратите внимание: результат работы функции ПРОСМОТР может оказаться некорректным, если данные в массиве или столбце таблицы не отсортированы в порядке возрастания числового значения или алфавитном порядке. Если сортировка невозможна в силу различных причин, рекомендуется использовать перечисленные выше аналоги данной функции.
Данная функция может быть записана в двух синтаксических вариантах:
1. Векторная форма записи. Вектором данных в Excel принято считать диапазон данных, содержащих лишь одну строку либо столбец таблицы. Соответственно, функция ПРОСМОТР используется для поиска определенного значения в одной строке или одном столбце. Синтаксис:
Два первых аргумента функции должны быть обязательно указаны.
- Искомое_значение – объект числовых, текстовых, ссылочных или логических данных. Функция ПРОСМОТР выполняет поиск значения этих данных в векторе данных.
- Просматриваемый_вектор – диапазон данных, который представляет собой столбец таблицы или строку. Вектор данных может содержать числовые, текстовые и логические значения. Все значения вектора данных должны быть отсортированы в порядке возрастания величин (А-Я; ЛОЖЬ, ИСТИНА; -2, 0, 103, 1000).
- Вектор_результатов – необязательный аргумент, представляющий собой диапазон данных из одной строки либо столбца таблицы. Размеры просматриваемого и вектора результатов должны быть тождественны.
2. Форма массива. В Excel массивом считается группа ячеек либо значений, обрабатываемых в качестве единого модуля. Некоторые функции Excel принимают массивы в качестве аргументов, либо возвращают результаты в виде массивов данных. Синтаксис:
Все аргументы в данной форме записи являются обязательными.
- Искомое_значение — объект текстовых, логических, числовых или ссылочных данных, значение которого функция ПРОСМОТР ищет в определенном массиве данных. Если искомое_значение отсутствует в указанном массиве, функция выбирает наибольшее значение из массива, которое меньше или равно искомому. Ошибка #Н/Д будет возвращена, если значение первого элемента массива больше, чем искомое_значение.
- Массив – массив данных в Excel (текстовые, числовые, логические), сравниваемый с искомым значением. Функция ПРОСМОТР производит поиск в соответствии с размерностями массива, то есть в первой строке либо первом столбце таблицы, если она содержит больше столбцов чем строк либо больше строк чем столбцов соответственно.
Обратите внимание: запись функции ПРОСМОТР в форме массива была предусмотрена только для совместимости различных программных продуктов для работы с таблицами, аналогичных Excel. Эта форма записи может возвращать некорректные результаты и не рекомендуется для использования. При работе с массивами данных рекомендуют применять аналоги: ГПР и ВПР.
Примеры использования функции ПРОСМОТР для быстрого поиска в таблицах Excel
Пример 1. Физик определял ускорение свободного падения эмпирическим путем: с определенной высоты запускал обтекаемый предмет и засекал время, которое требовалось на прохождение пути от точки запуска до момента соприкосновения с поверхностью земли. Затем по формуле g=2S/t2 определял искомую величину. После проведения нескольких опытов были получены следующие результаты:
Необходимо определить, находится ли среди полученных результатов значение, соответствующее общепризнанному значение g=9,81.
То есть, среди результатов вычислений находилась искомая величина.
Вторая версия функции ПРОСМОТР в Excel
Пример 2. В банк обратились 5 клиентов с целью получения кредита на определенные различные между собой суммы. Банк определяет процент за использование кредита с учетом суммы запрошенных средств в долг. Каждый клиент должен вернуть банку сумму денег, которая телу кредита и процентов в пересчете на денежные средства. Введем исходные данные в таблицу:
Задача состоит в поиске процента возврата с учетом зависимости между процентом и суммой кредита, а также вычисление суммы возврата. Определим искомые величины для клиента с фамилией Иванов. Для этого в ячейке C2 введем следующую формулу:
- B2 – сумма взятого клиентом кредита.
- $A$12:$A$17 – массив сумм, среди которых производится поиск эквивалентного или ближайшего значения к искомому.
- $B$12:$B$17 – массив соответствующих процентов.
Примечание: знак «$» использован для «фиксации» ссылок на ячейки.
То есть, клиент Иванов обязан вернуть 127,2 денежных единиц. Подобным методом производится расчет задолженности для остальных клиентов.
Пример 3. В офисе работают 5 сотрудников различного возраста. Необходимо найти возраст Виталия.
Для определения возраста самого младшего сотрудника введем формулу в ячейке E3:
Значит, возраст сотрудника Виталия составляет 43 года.
В данном примере мы ознакомились с двумя версиями функции ПРОСМОТР на 2 и 3 аргумента для заполнения входящими данными.
Применение оператора ПРОСМОТР
Прежде, чем приступить к использованию инструмента ПРОСМОТР нужно создать таблицу, где будут значения, которые нужно найти, и заданные значения. Именно по данным параметрам поиск и будет осуществляться. Существует два способа использования функции: векторная форма и форма массива.
Способ 1: векторная форма
Данный способ наиболее часто применим среди пользователей при использовании оператора ПРОСМОТР.
Для тех пользователей, которые желают применять данный оператор вручную, без использования «Мастера функций», важно знать синтаксис его написания. Он выглядит следующим образом:
Мы же остановимся на тех значениях, которые следует вносить в окно аргументов.
В поле «Искомое значение» вводим координаты ячейки, куда будем записывать параметр, по которому будет проводиться поиск. Мы во второй таблице назвали так отдельную ячейку. Как обычно, адрес ссылки прописывается в поле либо вручную с клавиатуры, либо путем выделения соответствующей области. Второй вариант намного удобнее.
После того, как данные были введены, ячейка, в которой находится функция, автоматически заполняется соответствующим показателем из вектора результатов.
Если мы введем в ячейку искомого значения другое наименование, то и результат, соответственно, поменяется.
Функция ПРОСМОТР очень напоминает ВПР. Но в ВПР просматриваемый столбец обязательно должен быть крайним левым. У ПРОСМОТР данное ограничение отсутствует, что мы и видим на примере выше.
Способ 2: форма массива
В отличие от предыдущего способа, данная форма оперирует целым массивом, в который сразу входит просматриваемый диапазон и диапазон результатов. При этом просматриваемый диапазон должен являться обязательно крайнем левым столбцом массива.
В поле «Искомое значение», как и при предыдущем способе, вписываем координаты ячейки, в которую будет вводиться запрос.
Как видим, после этого автоматически в соответствующую область выводится результат.
Как видим, оператор ПРОСМОТР является отличным помощником при поиске данных по искомому значению. Особенно эта возможность полезна в длинных таблицах. Также следует заметить, что существуют две формы этой функции – векторная и для массивов. Последняя из них является уже устаревшей. Хотя некоторыми пользователями она применяется до сих пор.
Мы рады, что смогли помочь Вам в решении проблемы.
Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.
Функции Впр и Гпр в Excel Как Работать – Функция столбец | 📂Все о программе Word
- Искомое_значение – объект числовых, текстовых, ссылочных или логических данных. Функция ПРОСМОТР выполняет поиск значения этих данных в векторе данных.
- Просматриваемый_вектор – диапазон данных, который представляет собой столбец таблицы или строку. Вектор данных может содержать числовые, текстовые и логические значения. Все значения вектора данных должны быть отсортированы в порядке возрастания величин (А-Я; ЛОЖЬ, ИСТИНА; -2, 0, 103, 1000).
- Вектор_результатов – необязательный аргумент, представляющий собой диапазон данных из одной строки либо столбца таблицы. Размеры просматриваемого и вектора результатов должны быть тождественны.
На этой прекрасной ноте мы закончим. В этом уроке Вы познакомились еще с двумя полезными функциями Microsoft Excel — ПОИСКПОЗ и ИНДЕКС, разобрали возможности на простых примерах, а также посмотрели их совместное использование. Надеюсь, что данный урок Вам пригодился. Оставайтесь с нами и успехов в изучении Excel.
Функция поискпоз в Excel пошаговая инструкция
Функция ПОИСКПОЗ в Excel
Функция ПОИСКПОЗ возвращает относительное расположение ячейки в заданном диапазоне Excel, содержимое которой соответствует искомому значению. Т.е. данная функция возвращает не само содержимое, а его местоположение в массиве данных.
Например, на рисунке ниже формула вернет число 5, поскольку имя «Дарья» находится в пятой строке диапазона A1:A9.
В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.
Из приведенных примеров видно, что первым аргументом функции ПОИСКПОЗ является искомое значение. Вторым аргументом выступает диапазон, который содержит искомое значение. Также функция имеет еще и третий аргумент, который задает тип сопоставления. Он может принимать один из трех вариантов:
-
0 — функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.
В одиночку функция ПОИСКПОЗ, как правило, не представляет особой ценности, поэтому в Excel ее очень часто используют вместе с функцией ИНДЕКС.
Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.
Стоит отметить, что номера строк и столбцов задаются относительно верхней левой ячейки диапазона. Например, если ту же таблицу расположить в другом диапазоне, то формула вернет тот же результат:
Если массив содержит только одну строку или один столбец, т.е. является вектором, то второй аргумент функции ИНДЕКС указывает номер значения в этом векторе. При этом третий аргумент указывать необязательно.
Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):
Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):
Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel
На рисунке ниже представлена таблица, которая содержит месячные объемы продаж каждого из четырех видов товара. Наша задача, указав требуемый месяц и тип товара, получить объем продаж.
Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 — тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:
=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))
Как видите, мы получили верный результат. Если поменять месяц и тип товара, формула снова вернет правильный результат:
В данной формуле функция ИНДЕКС принимает все 3 аргумента:
-
Первый аргумент — это диапазон B2:E13, в котором мы осуществляем поиск.
Если подставить в исходную громоздкую формулу вместо функций ПОИСКПОЗ уже вычисленные данные из ячеек D15 и D16, то формула преобразится в более компактный и понятный вид:
На этой прекрасной ноте мы закончим. В этом уроке Вы познакомились еще с двумя полезными функциями Microsoft Excel — ПОИСКПОЗ и ИНДЕКС, разобрали возможности на простых примерах, а также посмотрели их совместное использование. Надеюсь, что данный урок Вам пригодился. Оставайтесь с нами и успехов в изучении Excel.
Функция ВПР в Excel: пошаговая инструкция с примерами для чайников |
В нашем случае, к примеру, если мы хотим искать по наименованию которые находятся в столбце B , то в качестве таблицы нужно выбирать диапазон B1 E11 не A1 E11 , так как именно по столбцу B будет делаться поиск и именно он является первым столбцом в таблице B1 E11. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Функция ВПР в Excel: пошаговая инструкция с примерами для чайников
Упростить работу с представленными в табличном виде данными в программе Эксель помогают десятки полезных функций. Одна из них – VLOOKUP или ВПР в Excel, позволяющая переставлять информацию из одной таблицы в другую. Рассказываем, как ее применять.
ВПР применяют для получения одного массива из 2 или большего количества меньших по размеру, что позволяет упростить сравнение данных вручную. Особенно если таблицы состоят из нескольких сотен и даже тысяч строк, когда их объединение другими способами, без функции ВПР, может привести к ошибкам.
Особенности использования функции
Принцип действия и синтаксис функции не меняются в зависимости от версии табличного процессора — она остается актуальной и для пакета MS Office 1997, и для Офис 365. Но для того, чтобы лучше разобраться с ее работой, стоит рассмотреть ВПР в Excel для чайников с примерами — такими, как списки продуктов на складе магазина.
Предположим, что есть две отдельные таблицы с одинаковыми наименованиями — но в одной указаны только цены, а в другой представлено количество товара.
Чтобы объединить таблицы, пользователь должен воспользоваться таким алгоритмом:
Результатом становится заполненный новый столбец общего массива, где теперь указано не только количество товаров, но и цена за единицу измерения. Все данные точно соответствуют колонке «Наименование», независимо от их сортировки. Даже если названия продуктов во второй таблице будут расположены по-другому, например, в алфавитном порядке.
Теперь можно закончить процесс подстановки и расчета общей стоимости продуктов. Для этого достаточно указать в верхней ячейке соответствующей колонки простую формулу: «Стоимость» = «Кол-во на складе» * «Цена, руб.». В данном случае это будет: «=F3*G3».
Данные «размножаются» стандартным способом: захватите крест и протяните его до конца колонки.
Если нужно, чтобы результат оставался постоянным, можно создать дополнительный столбец, скопировав данные из «Общей стоимости» и выполнив специальную вставку — только «Значений». Правда, данные не будут изменяться и при уменьшении или увеличении количества продуктов, потому для нашего примера этот способ не подходит.
Принцип действия функции
Чтобы понять, почему не имеет никакого значения сортировка, стоит рассмотреть принцип действия функции ВПР в Excel для чайников:
- Сначала функция выполняет проверку верхней ячейки колонки «Наименования» первого массива с соответствующим столбцом второй таблицы. В данном случае информация («Баклажан») совпадает сразу, и к количеству «120» добавляется цена «80».
- Проверка продолжается, и теперь проверяется значение «Петрушка». Функция продолжает сравнивать данные до тех пор, пока не доходит до 10-й строчки списка. После этого к количеству «12» добавляется соответствующая цена «270».
- Процесс продолжается до тех пор, пока нужные сведения не будут найдены.
Из-за того, что при настройке работы функции был указан параметр «ЛОЖЬ», данные в колонках «Наименование» должны точно соответствовать друг другу. Даже если в столбцах будет изменение на 1 знак (например, «Помидоры 1 сорт» и «Помидоры 2 сорт»), сравнение получится не корректным и сопоставления данных не произойдет.
Применение функции в выпадающих списках
Применять ВПР можно и при использовании выпадающих списков. Например, для того, чтобы при выборе из перечня нужного значения одновременно с ним в соответствующей ячейке появлялись связанные с ним данные. В выбранном примере это может быть название и цена.
Лучший способ рассмотреть действие функции ВПР в Excel – пошаговая инструкция для заданных условий. От пользователя требуется выполнить такие действия:
Впр и гпр в одной формуле. Функция ВПР, ГПР, пошаговая инструкция. Уроки Excel.
- Сначала функция выполняет проверку верхней ячейки колонки «Наименования» первого массива с соответствующим столбцом второй таблицы. В данном случае информация («Баклажан») совпадает сразу, и к количеству «120» добавляется цена «80».
- Проверка продолжается, и теперь проверяется значение «Петрушка». Функция продолжает сравнивать данные до тех пор, пока не доходит до 10-й строчки списка. После этого к количеству «12» добавляется соответствующая цена «270».
- Процесс продолжается до тех пор, пока нужные сведения не будут найдены.
Функция ГПР имеет такой же синтаксис, как и ВПР, но ищет результат не в столбцах, а в строках. То есть, просматривает таблицы не сверху вниз, а слева направо и выводит заданный номер строки, а не столбца.
Публикуя свою персональную информацию в открытом доступе на нашем сайте вы, даете согласие на обработку персональных данных и самостоятельно несете ответственность за содержание высказываний, мнений и предоставляемых данных. Мы никак не используем, не продаем и не передаем ваши данные третьим лицам.