Функция ПОРОГ для определения порогового значения в Excel

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

  • 0 – если сравниваемое число оказалось строго меньше порогового значения;
  • 1 – если сравниваемое число равно или больше порогового.

Если требуется определить точное совпадение двух чисел или опровергнуть суждение о том, что два числа равны по значению, можно использовать другую функцию Excel – ДЕЛЬТА.

Формула расчета пороговых значений показателей в Excel

Функция ПОРОГ находится в разделе формул Excel^: «Другие функции»-«Инженерные». Она позволяет заменять логические функции =И(), =ИЛИ() в Excel. Например, если нужно выполнить проверку, является ли a>=b и c>=d, используют запись =ПОРОГ(a;b)*ПОРОГ(c;d). Это выражение вернет 1, если оба условия выполнились и 0, если хотя бы одно из условий не было выполнено. Для получения аналога логического ИЛИ используют запись =ПОРОГ(a;b)+ПОРОГ(c;d), при этом число 0 будет возвращено только в случае, если a<b и c<d. В любом другом случае полученное значение (1 – если одно из чисел больше порогового значения, 2 – если оба больше пороговых значений) будет интерпретировано как логическое ИСТИНА (например, ЕСЛИ(x;ИСТИНА;ЛОЖЬ) возвращает ИСТИНА при любом x<> 0).

Пример 1. В таблице Excel содержатся данные о продажах за месяц для каждого менеджера компании, включая число отработанных часов, число единиц товаров и общую сумму средств. Определить число менеджеров, выполнивших план на месяц по следующим критериям:

  1. Проработали 180 ч и более.
  2. Проработали не менее 200 ч и продали не менее 200 единиц продукции.
  3. Продали не менее 300 единиц продукции или на сумму не менее 8000 у. е.

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

Пример 1.

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

Определим число менеджеров, проработавших не менее 180 ч. Запишем функцию в ячейке E3 и «растянем» ее вниз по столбцу:

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

ПОРОГ.

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

Результат проверки для всех менеджеров:

проверка второго условия.

Для проверки 3-го условия используем формулу:

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

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

проверка 3-го условия.

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

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



Пример как рассчитать пороговые значения показателей в Excel

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

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

Пример 2.

Запишем в ячейку C2 следующую формулу:

Результат, возвращаемый функцией (число) будет автоматически преобразован к данным логического типа (ЛОЖЬ, если возвращенное значение – 0, ИСТИНА – если 1).

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

пороговые значения показателей.

Правила определения пороговых значений по функции ПОРОГ в Excel

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

=ПОРОГ(число;[порог])

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

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

Примечания:

  1. Любой из аргументов функции должен быть указан числом, ссылкой на ячейку с числовым значением или текстовой строкой, которая может быть преобразована к числу. Иначе функция будет возвращать код ошибки #ЗНАЧ!
  2. Промежуточное преобразование логических данных к числовым значениям не выполняется. Например, =ПОРОГ(2;ИСТИНА) вернет код ошибки #ЗНАЧ!, но =ПОРОГ(2;--ИСТИНА) вернет число 0.
  3. Функцию ПОРОГ нельзя использовать как формулу массива. Например, {=ПОРОГ(A1:A20;20)} вернет массив ошибок #ЗНАЧ!

en ru