Формула Excel: Підрахувати рядки, що містять певні значення -

Загальна формула

=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))

Резюме

Для підрахунку рядків, що містять конкретні значення, ви можете використовувати формулу масиву на основі функцій MMULT, TRANSPOSE, COLUMN і SUM. У наведеному прикладі формула в G5 має вигляд:

(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))

де дані - іменований діапазон B4: B12.

Примітка: це формула масиву, і її потрібно вводити за допомогою enter control shift.

Пояснення

Працюючи зсередини, логічними критеріями, які використовуються у цій формулі, є:

--(data=90)

де дані - іменований діапазон B4: D12. Це генерує результат TRUE / FALSE для кожного значення в даних, а подвійний негатив примушує значення TRUE FALSE до 1 і 0, щоб отримати такий масив:

(1,0,0;0,0,0;0,1,1;1,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,1)

Як і вихідні дані, цей масив має 9 рядків на 3 стовпці (9 x 3) і переходить у функцію MMULT як array1 .

Array2 походить з:

TRANSPOSE(COLUMN(data))

Це складна та весела частина цієї формули. Функція COLUMN використовується просто для зручності як спосіб створення числового масиву потрібного розміру. Щоб виконати множення матриць за допомогою MMULT, кількість стовпців у масиві1 (3) повинна дорівнювати кількості рядків у масиві2 .

COLUMN повертає масив із 3 стовпцями (2,3,4), а TRANSOSE змінює цей масив на масив із 3 рядків (2; 3; 4). Потім MMULT запускається і повертає результат масиву 9 x 1:

=SUM(--((2;0;7;2;0;0;0;0;4)>0))

Ми перевіряємо ненульові записи з> 0 і знову примушуємо TRUE FALSE до 1 і 0 з подвійним від’ємником, щоб отримати остаточний масив усередині SUM:

=SUM((1;0;1;1;0;0;0;0;1))

У цьому остаточному масиві 1 представляє рядок, де логічний тест (дані = 90) повернув true. Підсумок, який повертає SUM, - це кількість усіх рядків, що містять число 90.

Буквальний містить

Якщо вам потрібно перевірити конкретні текстові значення, іншими словами, буквально перевірте, чи містять клітинки певні текстові значення, ви можете змінити логіку у формулі на цій сторінці, щоб використовувати функції ISNUMBER та SEARCH. Наприклад, для підрахунку комірок / рядків, що містять "яблуко", ви можете використовувати: