Загальна формула
(=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)^0))>=N)))
Резюме
Для підрахунку рядків, що містять конкретні значення, ви можете використовувати формулу масиву на основі функцій MMULT, TRANSPOSE, COLUMN і SUM. У наведеному прикладі формула в K6 має вигляд:
(=SUM(--(MMULT(--((data)=2)))
де дані - іменований діапазон C5: I14.
Зверніть увагу, що це формула масиву, і її потрібно вводити з контролем shift enter.
Пояснення
Працюючи зсередини, логічними критеріями, які використовуються у цій формулі, є:
(data)<70
де дані - названий діапазон C5: I14. Це генерує результат TRUE / FALSE для кожного значення в даних, а подвійний негатив примушує значення TRUE FALSE до 1 і 0, щоб отримати такий масив:
(0,0,0,1,0,1,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,1,1,0,0,1,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,1,0,0,0,0,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0)
Як і вихідні дані, цей масив має 10 рядків на 7 стовпців (10 x 7) і переходить у функцію MMULT як array1 . Наступний аргумент array2 створюється за допомогою:
TRANSPOSE(COLUMN(data)^0))
Тут функція COLUMN використовується як спосіб генерування числового масиву правильного розміру, оскільки для множення матриць необхідний підрахунок стовпців у масиві1 (7), рівний кількості рядків у масиві2 .
Функція COLUMN повертає масив із 7 стовпців (3,4,5,6,7,8,9). Піднімаючи цей масив до рівня нуля, ми отримуємо масив 7 x 1, як (1,1,1,1,1,1,1,1), який TRANSPOSE змінюється на масив 1 x 7, як (1; 1 ; 1; 1; 1; 1; 1).
Потім MMULT запускається і повертає результат масиву 10 x 1 (2; 0; 0; 3; 0; 0; 0; 1; 0; 0), який обробляється логічним виразом> = 2, в результаті чого отримується масив TRUE FALSE значення:
(TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE).
Ми знову примушуємо TRUE FALSE до 1 і 0 з подвійним негативом, щоб отримати остаточний масив усередині SUM:
=SUM((1;0;0;1;0;0;0;0;0;0))
Що правильно повертає 2, кількість імен з принаймні 2 балами нижче 70.
