Загальна формула
=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%) включно.
Хоча для вирішення цієї проблеми можна використовувати функцію IF (див. Нижче), результат буде дещо довшим і зайвим. Натомість у наведеному прикладі використовується комбінація функцій 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 також може бути використана для вирішення цієї проблеми. Для цього нам потрібні дві окремі функції IF. Один ПЧ примушує негативні значення до нуля:
IF(B5<0,0,B5) // cap at zero
Другий ПЧ обмежує більші значення на 1:
=IF(B5>1,1,B5) // cap at 1
Коли ми вкладаємо перший IF у друге, ми маємо остаточну формулу:
=IF(B5>1,1,IF(B5<0,0,B5))
Це приклад вкладеного IF. Він повертає точно такий самий результат, як формула MIN та MAX вище, але трохи складніший та зайвіший. Зверніть увагу, наприклад, посилання на В5 трапляється три окремі рази.
Суть - коли вам потрібно зробити вибір на основі менших чи більших значень, функції MIN та MAX можуть бути розумним та елегантним способом спростити формулу.
Функція MEDIAN
Добре, тепер, коли ми поговорили про вкладеність і поговорили про елегантність MIN з MAX, я повинен згадати, що цю проблему можна вирішити без взагалі вкладеності за допомогою функції MEDIAN. Загальна версія формули виглядає так:
=MEDIAN(0,1,A1)
Це працює, оскільки функція MEDIAN повертає медіану (середнє число) у групі чисел. Коли значення від’ємне, нуль стає середнім числом. Коли число більше 1, 1 стає середнім числом. Розумні!
Однак примітка MEDIAN повертає середнє число лише тоді, коли загальна кількість значень непарна. Якщо число значень парне, MEDIAN повертає середнє значення двох чисел посередині. Як наслідок, якщо цільова комірка (А1) порожня, MEDIAN поверне середнє значення 1 і нуль, що становить 0,5 або 50% при форматуванні у відсотках.
