Общая формула
=MAX(0,MIN(A1,1))
Резюме
Чтобы ограничить процентное значение таким образом, чтобы оно составляло от 0% до 100%, вы можете использовать формулу, основанную на функциях MIN и MAX. В показанном примере формула в C5, скопированная, имеет следующий вид:
=MAX(0,MIN(B5,1))
В результате отрицательные значения обнуляются, значения больше 1 ограничиваются 1, а значения от 0 до 1 остаются неизменными.
Примечание: все значения отформатированы в формате процентных чисел.
Объяснение
Чтобы понять эту проблему, убедитесь, что вы понимаете, как работает форматирование процентных чисел. В двух словах, проценты представляют собой десятичные значения: 0,1 - 10%, 0,2 - 20% и т. Д. Число 1 в процентах равно 100%. Подробнее о числовых форматах здесь.
Цель этого примера - ограничить входящие процентные значения так, чтобы они попадали в верхний и нижний пороговые значения. Отрицательные значения и значения более 100% не допускаются, поэтому окончательный результат должен быть числом от нуля до 1 (0–100%) включительно.
Хотя для решения этой проблемы можно использовать функцию ЕСЛИ (см. Ниже), результат будет несколько более длинным и избыточным. Вместо этого в показанном примере используется комбинация функций MIN и MAX в очень компактной формуле:
=MAX(0,MIN(B5,1))
Это пример вложенности - функция MIN вложена в функцию MAX. Вложенность - это ключевой строительный блок для более сложных формул.
Работая изнутри, функция MIN используется для ограничения входящих значений до 1 следующим образом:
MIN(B5,1) // get smaller value
Перевод: возвращает меньшее из B5 и 1. Для любого значения больше 1 возвращается значение B5. В примере B5 содержит -5% (-0,05), поэтому MIN возвращает -0,05. Этот результат возвращается непосредственно в функцию MAX:
=MAX(0,-0.05) // get larger value
Здесь мы видим, что формула выполняет свою работу. Поскольку ноль больше (больше), чем -0,05, MAX возвращает ноль в качестве окончательного результата. Исходное значение отбрасывается.
Функция ЕСЛИ
Как упоминалось выше, функцию ЕСЛИ также можно использовать для решения этой проблемы. Для этого нам понадобятся две отдельные функции ЕСЛИ. Одна IF обнуляет отрицательные значения:
IF(B5<0,0,B5) // cap at zero
Вторая IF ограничивает большие значения 1:
=IF(B5>1,1,B5) // cap at 1
Когда мы вкладываем первый IF во второй, мы получаем окончательную формулу:
=IF(B5>1,1,IF(B5<0,0,B5))
Это пример вложенного IF. Он возвращает тот же результат, что и приведенная выше формула MIN и MAX, но является немного более сложной и избыточной. Обратите внимание, например, что ссылка на B5 встречается три раза.
Итог - когда вам нужно сделать выбор на основе меньших или больших значений, функции MIN и MAX могут быть умным и элегантным способом упростить формулу.
Функция МЕДИАНА
Хорошо, теперь, когда мы говорили о вложенности и говорили об элегантности MIN с MAX, я должен упомянуть, что эту проблему можно решить без вложенности вообще с помощью функции MEDIAN. Общая версия формулы выглядит так:
=MEDIAN(0,1,A1)
Это работает, потому что функция МЕДИАНА возвращает медиану (среднее число) в группе чисел. Когда значение отрицательное, ноль становится средним числом. Когда число больше 1, 1 становится средним числом. Умный!
Однако обратите внимание, что МЕДИАНА возвращает только среднее число, когда общее количество значений нечетное. Если количество значений четное, МЕДИАНА возвращает среднее значение двух чисел в середине. Как следствие, если целевая ячейка (A1) пуста, МЕДИАНА вернет среднее значение 1 и ноль, что составляет 0,5 или 50% при форматировании в процентах.
