Загальна формула
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Резюме
Щоб підсумувати нижчі значення n у критеріях, що відповідають діапазону, ви можете використовувати формулу масиву, засновану на функції SMALL, загорнутій у функцію SUM. У загальній формі формули (вище), діапазон1 є діапазон комірок , по порівнянні з критеріями , діапазон2 містить числові значення , з яких витягуються нижні значення, і N є «N - й».
У прикладі активна комірка містить таку формулу:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Де колір - іменований діапазон B5: B12, а значення - іменований діапазон C5: C12.
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter.
Пояснення
У найпростішій формі SMALL повертає значення N-го найменшого в діапазоні з такою конструкцією:
=SMALL (range,N)
Так, наприклад:
=SMALL (C5:C12,2)
поверне друге найменше значення в діапазоні C5: C12, тобто 5 у наведеному прикладі.
Однак якщо ви надаєте "константу масиву" (наприклад, константу у вигляді (1,2,3)) для SMALL як другий аргумент, SMALL поверне масив результатів замість одного результату. Отже, формула:
=SMALL (C5:C12, (1,2,3))
поверне 1-е, 2-е і 3-те найменші значення C5: C12 у масиві, подібному до цього: (4,5,7).
Отже, фокус тут полягає у фільтруванні значень на основі кольору перед тим, як запускати МАЛИЙ. Ми робимо це з виразом на основі функції IF:
IF(color=E5,value)
Це створює масив значень, поданих у SMALL. По суті, до масиву потрапляють лише значення, пов’язані з червоним кольором. Там, де колір дорівнює "червоному", масив містить число, а там, де колір не червоний, масив містить FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
Функція SMALL ігнорує значення FALSE і повертає 3 найменші значення в масиві: (4,8,10). Функція SUM повертає кінцевий результат, 22.
