Порахуйте кількість рядків, які містять певні значення в Excel

Нам може бути легко підрахувати кількість клітинок із певним значенням на робочому аркуші Excel. Однак отримати кількість рядків, що містять конкретні значення, може бути досить складно. У цьому випадку більш складна формула, заснована на функціях SUM, MMULT, TRANSPOSE та COLUMN, може зробити вам користь. Цей підручник розповість про те, як створити цю формулу для вирішення цієї роботи в excel.

• Підрахуйте кількість рядків, які містять певні значення

Підрахуйте кількість рядків, які містять певні значення

Наприклад, у вас є діапазон значень на аркуші, і тепер вам потрібно порахувати кількість рядків із заданим значенням "300", як показано на скріншоті нижче:

Щоб отримати кількість рядків, що містять певні значення, загальний синтаксис такий:

• data: Діапазон клітинок для перевірки, чи містять вони певне значення;
• X: Конкретне значення, яке ви використовуєте для підрахунку рядків.

1. Введіть або скопіюйте формулу нижче в порожню клітинку, куди ви хочете ввести результат:

2. Потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати правильний результат, див. знімок екрана:

Пояснення формули:

=SUM(--(MMULT(--($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))

• -$ A $ 2: $ C $ 12 = 300: Цей вираз перевіряє, чи існує значення “300” у діапазоні A2: C12, і генерує результат масиву TRUE та False. Подвійний негативний знак використовується для перетворення ІСТИН на 1с, а ЛОЖІ на 0. Отже, ви отримаєте такий результат: {0,0,0; 1,0,0; 0,0,0; 0,1,1; 0,0,0; 0,1,0; 0,0,0 , 1,0,0; 0,0,1; 0,0,0; 1,1,1; 11}. Масив, що складається з 3 рядків і 1 стовпців, буде працювати як масивXNUMX у функції MMULT.
• ТРАНСПОЗИЦІЯ (Стовпчик ($ A $ 2: $ C $ 12)): Функція COLUMN тут використовується для отримання номера стовпця діапазону A2: C12, вона повертає масив з 3 стовпців, такий: {1,2,3}. І тоді функція TRANSPOSE поміняє цей масив на 3-рядковий масив {1; 2; 3}, що функціонує як масив2 у функції MMULT.
• MMULT (-($ A $ 2: $ C $ 12 = "Joanna"), TRANSPOSE (COLUMN ($ A $ 2: $ C $ 12))): Ця функція MMULT повертає матричний добуток двох вищевказаних масивів, результат буде таким: {0; 1; 0; 5; 0; 2; 0; 1; 3; 0; 6}.
• SUM(--(MMULT(--($A$2:$C$12="Joanna"),TRANSPOSE(COLUMN($A$2:$C$12)))>0))= SUM(--{0;1;0;5;0;2;0;1;3;0;6}>0): Спочатку перевірте значення в масиві більше 0: Якщо значення більше 0, відображається TRUE; якщо менше 0, відображається FALSE. І тоді подвійний від’ємний знак змушує ІСТИНИ та НЕВІРНОСТІ бути 1s та 0s, тож ви отримаєте це: SUM ({0; 1; 0; 1; 0; 1; 0; 1; 1; 0; 1}). Нарешті, функція SUM підсумовує значення в масиві, щоб повернути результат: 6.

Порада:

Якщо вам потрібно порахувати кількість рядків, що містять певний текст на аркуші, застосуйте формулу нижче та не забудьте натиснути Ctrl + Shift + Enter клавіші разом, щоб отримати загальну кількість:

Використана відносна функція:

Більше статей: