Обща формула
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Обобщение
За да търсите стойност в няколко работни листа в работна книга и да върнете преброяване, можете да използвате формула, базирана на функциите COUNTIF и INDIRECT. С някои предварителни настройки можете да използвате този подход за търсене в цяла работна книга за конкретна стойност. В показания пример формулата в C5 е:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Контекст - примерни данни
Работната книга съдържа общо 4 работни листа. Лист1 , Лист2 и Лист3 съдържат по 1000 произволни имена, които изглеждат така:
Обяснение
Диапазонът B7: B9 съдържа имената на листове, които искаме да включим в търсенето. Това са само текстови низове и трябва да свършим малко работа, за да бъдат разпознати като валидни препратки към листа.
Работейки отвътре навън, този израз се използва за изграждане на пълен референтен лист:
"'"&B7&"'!"&"1:1048576"
Единичните кавички се добавят, за да позволят имена на листове с интервали, а удивителен знак е стандартен синтаксис за диапазони, които включват име на лист. Текстът "1: 1048576" е диапазон, който включва всеки ред в работния лист.
След като B7 е оценен и стойностите са обединени, горният израз се връща:
"'Sheet1'!1:1048576"
което влиза във функцията INDIRECT като аргумент 'ref_text'. INDIRECT оценява този текст и връща стандартна препратка към всяка клетка в Sheet1 . Това влиза във функцията COUNTIF като диапазон. Критериите се предоставят като абсолютна препратка към C4 (заключена, за да може формулата да бъде копирана надолу в колона C).
След това COUNTIF връща брой на всички клетки със стойност, равна на "mary", 25 в този случай.
Забележка: COUNTIF не чувствителен към регистъра.
Съдържа срещу равни
Ако искате да преброите всички клетки, които съдържат стойността в C4, вместо всички клетки, равни на C4, можете да добавите заместващи символи към критериите като този:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Сега COUNTIF ще брои клетки с подниза "Джон" навсякъде в клетката.
производителност
Като цяло не е добра практика да се определя диапазон, който включва всички клетки на работния лист. Това може да доведе до проблеми с производителността, тъй като обхватът включва милиони и милиони клетки. В този пример проблемът се усложнява, тъй като формулата използва функцията INDIRECT, която е нестабилна функция. Летливите функции се преизчисляват при всяка промяна на работния лист, така че въздействието върху производителността може да бъде огромно.
Когато е възможно, ограничете диапазоните до разумен размер. Например, ако знаете, че данните няма да се показват след ред 1000, можете да търсите само първите 1000 реда по този начин:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
