Пребройте броя на редовете, които съдържат конкретни стойности в Excel
Може да ни е лесно да преброим броя на клетките с конкретна стойност в работен лист на Excel. Получаването на броя редове, които съдържат конкретни стойности обаче, може да бъде доста сложно. В този случай по-сложна формула, базирана на функциите SUM, MMULT, TRANSPOSE и COLUMN, може да ви направи услуга. Този урок ще говори за това как да създадете тази формула, за да се справите с тази работа в Excel.
Пребройте броя на редовете, които съдържат конкретни стойности
Например, имате диапазон от стойности в работен лист и сега трябва да преброите броя на редовете с дадена стойност „300“, както е показано на екранната снимка по-долу:
За да получите броя на редовете, които съдържат конкретни стойности, общият синтаксис е:
Array formula, should press Ctrl + Shift + Enter keys together.
- 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 резултат от масив. Двойният отрицателен знак се използва за преобразуване на TRUE в 1s и FALSE в 0s. Така ще получите следния резултат: {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}, функциониращ като array2 във функцията MMULT.
- MMULT(--($A$2:$C$12="Джоана"),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. И тогава двойният отрицателен знак принуждава TRUE и FALSE да бъдат 1s и 0s, така че ще получите това: SUM({0;1;0;1;0;1;0;1;1;0;1}). Накрая функцията SUM сумира стойностите в масива, за да върне резултата: 6.
Съвет:
Ако трябва да преброите броя на редовете, които съдържат определен текст в работен лист, моля, приложете формулата по-долу и не забравяйте да натиснете Ctrl + Shift + Enter ключове заедно, за да получите общия брой:
Използвана относителна функция:
- СУММА:
- Функцията SUM на Excel връща сумата от предоставените стойности.
- MMULT:
- Функцията MMULT на Excel връща матричното произведение на два масива.
- ТРАНСПОЗИРАНЕ:
- Функцията TRANSPOSE ще върне масив в нова ориентация въз основа на определен диапазон от клетки.
- КОЛОНА:
- Функцията COLUMN връща номера на колоната, чиято формула се появява, или връща номера на колоната на дадена препратка.
Още статии:
- Преброяване на редове, ако отговарят на вътрешните критерии
- Да предположим, че имате отчет за продажбите на продукти от тази и миналата година и сега може да се наложи да преброите продукти, при които продажбите през тази година са по-големи от миналата година или продажбите през тази година са по-малко от миналата година, както е показано по-долу показана екранна снимка. Обикновено можете да добавите помощна колона за изчисляване на разликата в продажбите между двете години и след това да използвате COUNTIF, за да получите резултат. Но в тази статия ще представя функцията SUMPRODUCT, за да получа резултата директно без помощна колона.
- Преброяване на редове, ако отговарят на множество критерии
- Пребройте броя на редовете в диапазон въз основа на множество критерии, някои от които зависят от логическите тестове, които работят на ниво ред, функцията SUMPRODUCT в Excel може да ви направи услуга.
- Преброяване на броя на клетките, равни на една от много стойности
- Да предположим, че имам списък с продукти в колона A, сега искам да получа общия брой конкретни продукти Apple, Grape и Lemon, които са изброени в диапазон C4:C6 от колона A, както е показано на екранната снимка по-долу. Обикновено в Excel простите функции COUNTIF и COUNTIFS няма да работят в този сценарий. В тази статия ще говоря за това как да решите тази задача бързо и лесно с комбинацията от функции SUMPRODUCT и COUNTIF.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
