Пребройте срещанията на конкретен текст в цялата работна книга на Excel
За нас е лесно да преброим колко пъти определен текст или числова стойност се среща в диапазон от клетки с функцията COUNTIF. Въпреки това, за да се преброят случаите на конкретен текст в цялата работна книга, е необходима по-сложна формула. Тази статия ще демонстрира формула, базирана на функцията SUMPRODUCT, COUNTIF и INDIRECT за решаване на този проблем.
Как да преброим срещанията в цялата работна книга?
Да предположим, че има 4 работни листа (Sheet2, Sheet3, Sheet4 и Sheet5) във вашата работна книга и искате да преброите колко пъти се появява конкретният текст „Лаптоп“ в тези 4 работни листа, можете да направите следното, за да го направите.
Генерична формула
=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&range1&”‘!”&range2),criteria))
Аргументи
Как да използвате тази формула?
1. Първо ще трябва да създадете нов лист и да изброите всички имена на листове в диапазон от клетки. В този случай изброявам всички имена на листове в диапазон B7:B10.
2. Изберете празна клетка, за да изведете резултата.
3. Въведете една от формулите по-долу и натиснете клавиша Enter, за да получите резултата.
Ако вече сте въвели конкретния текст в клетка (като B3), можете да посочите тази клетка във формулата, както следва:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3))
Или директно въведете конкретния текст, ограден с двойни кавички във формулата по следния начин:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),"Laptop"))
Забележка: Тук диапазонът A1:Z10000 е произволен диапазон, можете да го промените, за да отговаря на вашите данни
Как работи тази формула?
=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3))
- "'"&B7:B10&"'!A1:Z10000": Тук всяко име на лист в масива е свързано чрез конкатенация към диапазона A1:Z10000 и ще получите масив като {"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000";"'Sheet4' !A1:Z10000";"'Лист5'!A1:Z10000"}.
- INDIRECT({"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000";"'Sheet4'!A1:Z10000";"'Sheet5'!A1:Z10000"}): Функцията INDIRECT препраща към диапазоните в четирите листа.
- COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3): Функцията COUNTIF отчита колко пъти конкретната стойност се появява във всеки лист и връща масив {13;13;13;13}. Това означава, че конкретният текст се появява 13 пъти във всеки лист.
- SUMPRODUCT({13;13;13;13}): Функцията SUMPRODUCT сумира всички числа в масива и връща крайния резултат като 52.
Свързани функции
Функция SUMPRODUCT на Excel
Функцията SUMPRODUCT на Excel може да се използва за умножаване на две или повече колони или масиви заедно и след това да се получи сумата от продуктите.
Функция Excel COUNTIF
Функцията COUNTIF на Excel е статистическа функция в Excel, която се използва за преброяване на броя клетки, които отговарят на критерий.
Функция INDIRECT на Excel
Функцията INDIRECT на Excel преобразува текстов низ във валидна препратка.
Свързани формули
Пребройте числата, които започват с определено число
Този урок предоставя формула, базирана на функциите SUMPRODUCT и LEFT, за преброяване на броя на клетките, съдържащи числа, които започват с конкретни числа в Excel.
Преброяване на множество критерии с НЕ логика в Excel
Тази статия ще ви покаже как да преброите броя на клетките с множество критерии с НЕ логика в Excel.
Преброяване или сумиране само на цели числа в Excel
Тази публикация предоставя две формули, базирани на функцията SUMPRODUCT и функцията MOD, за да ви помогне да броите и сумирате само цели числа в диапазон от клетки в Excel.
Пребройте числа, където n-тата цифра е равна на дадено число
Този урок предоставя формула, базирана на функцията SUMPRODUCT и MID за преброяване на числа, където n-тата цифра е равна на дадено число в Excel.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
