Перейти до основного матеріалу

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

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


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

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

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

{=SUM(–(MMULT(–(data=X),TRANSPOSE(COLUMN(data)))>0))}
Array formula, should press Ctrl + Shift + Enter keys together.
  • data: Діапазон клітинок для перевірки, чи містять вони певне значення;
  • X: Конкретне значення, яке ви використовуєте для підрахунку рядків.

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

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

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 клавіші разом, щоб отримати загальну кількість:

=SUM(--(MMULT(--(ISNUMBER(SEARCH("Joanna",A2:C12))),TRANSPOSE(COLUMN($A$2:$C$12)))>0))


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

  • Сума:
  • Функція Excel SUM повертає суму поданих значень.
  • MMULT:
  • Функція Excel MMULT повертає матричний добуток двох масивів.
  • ТРАНСПОЗИЦІЯ:
  • Функція TRANSPOSE поверне масив у новій орієнтації на основі певного діапазону комірок.
  • КОЛОНКА:
  • Функція COLUMN повертає номер стовпця, формула якого відображається, або повертає номер стовпця з даним посиланням.

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

  • Підрахувати рядки, якщо вони відповідають внутрішнім критеріям
  • Припустимо, у вас є звіт про реалізацію продукції за цей та минулий рік, а зараз вам може знадобитися підрахувати продукцію, де продажі в цьому році більші, ніж у минулому, або продажі в цьому році менші, ніж у минулому, як показано нижче показаний скріншот. Зазвичай, ви можете додати допоміжний стовпець для розрахунку різниці у продажу між двома роками, а потім використовувати COUNTIF для отримання результату. Але в цій статті я познайомлю функцію SUMPRODUCT, щоб отримати результат безпосередньо без стовпця -помічника.
  • Кількість клітин дорівнює одному з багатьох значень
  • Припустимо, у мене є список продуктів у колонці А, тепер я хочу отримати загальну кількість конкретних продуктів Яблуко, Виноград та Лимон, які перераховані в діапазоні С4: С6 зі стовпця А, як показано на скріншоті нижче. Зазвичай у програмі Excel прості функції COUNTIF та COUNTIFS не працюватимуть у цьому сценарії. У цій статті я розповім про те, як швидко та легко вирішити цю роботу за допомогою комбінації функцій SUMPRODUCT та COUNTIF.

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки Excel ...)  |  ... і більше

Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...

Опис


Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations