Примеры функции ГПР в Excel пошаговая {инструкция} для чайников

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Интересно почитать: В эксель пропала строка формул сверху

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

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

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

Гпр формула в excel

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

Синтаксис:

Синтаксис функции IFS в Excel:

Аргументы:

logical_test1 : Нужные. Условие, которое воспринимает значение ИСТИНА либо ЛОЖЬ.
value_if_true1 : Нужные. Возвращает итог, если логический_тест1 — ИСТИНА. Быть может пустым.
logical_test2 : Необязательный. 2-ое условие, которое воспринимает значение ИСТИНА либо ЛОЖЬ.
value_if_true2 : Необязательный. Возвращает 2-ой итог, если логический_тест2 ИСТИНА. Быть может пустым.

Заметки:

1. Эта функция IFS дозволяет вводить до 127 критерий. И это применимо к Excel 2019, Office 365. Все версии до Excel 2019 не поддерживают эту функцию.
2. Если ни один из логических тестов не оценивается как ИСТИНА, будет возвращена ошибка # Н / Д.
3. Значение ошибки #VALUE возникает, если предоставленный логический_тест возвращает хоть какое значение, не считая TRUE либо FALSE.

Возвратить:

Чтоб проверить несколько критерий и возвратить соответственное значение с первым результатом ИСТИНА.

Примеры:

Пример 1. Внедрение функций IFS для определения оценки на базе баллов

Чтоб назначить оценку любому учащемуся на базе их оценок, как показано на последующем скриншоте:

Пожалуйста, примените приведенную ниже формулу, чтоб оценка была распределена последующим образом: 0-60: оценка F; 60-70 — сорт Д; 70-80: сорт С; 80-90: марка Б; больше либо равно 90: класс A.

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

Пример 2: Внедрение функций IFS для расчета комиссии на базе продаж

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

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

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

если сумма продаж 0-40000: комиссия = реализации * 0;
если сумма продаж 40000-80000: комиссия = реализации * 2%;
если сумма продаж 80000-100000: комиссия = реализации * 3.5%;
если сумма продаж больше либо равна 100000: комиссия = реализации * 7%;

Советы: Функция IFS получит ошибку # Н / Д, если ни одно из критерий не оценивается как ИСТИНА. Если вы желаете поменять ошибку # N / A иным весомым значением, вы должны добавить условие ELSE в конце функции IFS.

Обычно используйте функцию IFS:

Используйте функцию IFS с условием ELSE:

Внимание: В приведенной выше формуле добавление крайнего условия «ИСТИНА», «Остальные» возвратит значение «Остальные», если ни одно из критерий в функции IFS не оценивается как ИСТИНА.

Интересно почитать: Формула вычисления процента от числа в excel формула

Наилучшие инструменты для работы в кабинете

Kutools for Excel — поможет для вас выделиться из толпы

Желаете стремительно и идеально делать свою ежедневную работу? Kutools for Excel дает массивные расширенные функции 300 (объединение книжек, сумма по цвету, разделение содержимого ячеек, дата преобразования и так дальше . ) и экономия 80% времени вам.

Как в Excel вычислить определённый интеграл

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

1 Постановка физической задачкина расчёт определённого интеграла

Допустим, у нас есть таблично данная некая величина. Для примера пусть это будет скопленная доза радиации при авиаперелёте. Скажем, был таковой опыт: человек с дозиметром летел на самолёте из пт А в пункт Б и временами определял дозиметром мощность дозы (единицы измерений – микрозиверт в час, мкЗв/ч). Может быть, Вас это изумит, но при обыкновенном перелёте на самолёте человек попадает под радиоактивное излучение, превышающее фоновый уровень до 10 раз и даже больше. Но действие это краткосрочное, и потому не настолько опасное. По результатам измерений у нас есть таблица вот такового формата: Время – Мощность дозы.

Таблично данная величина для расчёта определённого интеграла

Нужно посчитать суммарную скопленную за время полёта дозу.

2 Геометрический смыслопределённого интеграла

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

График конфигурации мощности дозы во время полёта

3 Методика вычисленияопределённого интеграла

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

Площадь трапеции определяется как полусумма оснований, умноженная на высоту: S_трап = (A + B) / 2 × h Основания в нашем случае – это табличные измеренные значения мощности дозы за 2 поочередных промежутка времени, а высота – это разница времени меж 2-мя измерениями.

Способ трапеций для вычисления значения определённого интеграла

4 Согласованиеединиц измерения

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

Для перевода просто разделим мощность дозы в мкЗв/час построчно на количество минут в часе, т.е. на 60. Добавим ещё один столбец в нашу таблицу. На иллюстрации это столбец «D». В столбце «D» в строке 2 вписываем =С2/60 А позже при помощи маркера наполнения распространяем эту формулу на все другие ячейки в столбце «D», (т.е. тянем мышью чёрный прямоугольник в правом нижнем углу ячейки). Таковым образом, в столбце «D» у нас покажутся значения мощности дозы радиации, измеряемые в микрозивертах в минутку для каждой минутки перелёта.

Интересно почитать: Создание формулы в excel

Согласуем единицы измерения по шкале времени и шкале мощности дозы

5 Вычисление площадей отдельных трапеций

Сейчас необходимо отыскать площади трапеций за любой просвет времени. В столбце «E» будем вычислять по приведённой выше формуле площади трапеций. Полусумма оснований – это половина суммы 2-ух поочередных мощностей дозы из столбца «D». Потому что данные идут с периодом 1 раз в минутку, а мы берём интеграл по времени, выраженному в минутках, то высота каждой трапеции будет равна единице (разница времени меж каждыми 2-мя поочередными измерениями, к примеру, 17ч31мин — 17ч30мин = 0ч1мин = 1мин).

Получаем формулу в ячейке «E3»: =1/2*(D3+D2)*1. Понятно, что «×1» в данной для нас формуле можно не писать. И аналогично, при помощи маркера наполнения, распространяем формулу на весь столбец. Сейчас в каждой ячейке столбца «Е» посчитана скопленная доза за 1 минутку полёта.

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

Если б данные шли не через 1 минутку, то нам необходимо было бы написать формулу так:
=1/2*(D3+D2)*(МИНУТЫ(A3) – МИНУТЫ(A2)).
Правда при всем этом, если есть переход на последующий час, то получится отрицательное значение. Чтоб этого не вышло, впишем в формулу часы:
=1/2*(D3+D2)*(ЧАС(A3)*60+МИНУТЫ(A3)) – (ЧАС(A2)*60+МИНУТЫ(A2)).
Если перебегаем на последующие день, то необходимо будет уже добавлять даты, и т.д.

5 Определение площадипод графиком функции

Осталось отыскать сумму вычисленных площадей трапеций. Можно в ячейке «F2» написать формулу: =СУММ(E:E) Это и будет сумма всех значений в столбце «E», т.е. численное значение искомого определённого интеграла. Но давайте создадим вот что: определим скопленную дозу в различные моменты полёта. Для этого в ячейку «F4» впишем формулу =СУММ(E$3:E4) и маркером наполнения распространим на весь столбец «F».

Обозначение E$3 гласит программке Excel, что наращивать индекс ячейки «3» в столбце «E» при переносе формулы на последующие строчки не надо. Т.е. в строке 4 формула будет определять сумму в ячейках с «Е3» по «Е4», в строке 5 – сумму с «Е3» по «Е5», в строке 6 – с «Е3» по «Е6» и т.д.

Построим график по столбцам «F» и «A». Это график конфигурации скопленной дозы радиации во времени. Наглядно видно однообразное повышение скопленной дозы радиации за время полёта. Это гласит о том, что мы верно высчитали интеграл. И окончательное значение скопленной за двухчасовой полёт дозы радиации, которое выходит в крайней ячейке этого столбца, равно приблизительно 4,5 микрозиверт.

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

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