Formuła ogólna
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Podsumowanie
Aby przeszukać wiele arkuszy w skoroszycie pod kątem wartości i zwrócić liczbę, możesz użyć formuły opartej na funkcjach LICZ.JEŻELI i POŚR. W przypadku wstępnej konfiguracji można użyć tego podejścia do przeszukiwania całego skoroszytu pod kątem określonej wartości. W przedstawionym przykładzie formuła w C5 to:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Kontekst - przykładowe dane
Skoroszyt zawiera łącznie 4 arkusze. Arkusz1 , Arkusz2 i Arkusz3 każda zawiera 1000 losowo wybranych imion, które wyglądać tak:
Wyjaśnienie
Zakres B7: B9 zawiera nazwy arkuszy, które chcemy uwzględnić w wyszukiwaniu. To tylko ciągi tekstowe i musimy trochę popracować, aby zostały rozpoznane jako prawidłowe odniesienia do arkuszy.
Działając od wewnątrz, to wyrażenie służy do tworzenia pełnego odwołania do arkusza:
"'"&B7&"'!"&"1:1048576"
Pojedyncze cudzysłowy są dodawane, aby umożliwić nazwy arkuszy ze spacjami, a wykrzyknik jest standardową składnią dla zakresów zawierających nazwę arkusza. Tekst „1: 1048576” to zakres obejmujący każdy wiersz w arkuszu.
Po obliczeniu B7 i konkatenacji wartości powyższe wyrażenie zwraca:
"'Sheet1'!1:1048576"
który trafia do funkcji INDIRECT jako argument „ref_text”. INDIRECT ocenia ten tekst i zwraca standardowe odwołanie do każdej komórki w Sheet1 . Wchodzi to do funkcji LICZ.JEŻELI jako zakres. Kryteria są podawane jako bezwzględne odniesienie do C4 (zablokowane, aby można było skopiować formułę do kolumny C).
LICZ.JEŻELI zwraca następnie liczbę wszystkich komórek o wartości równej „mary”, w tym przypadku 25.
Uwaga: W LICZ.JEŻELI nie jest rozróżniana wielkość liter.
Zawiera vs. równe
Jeśli chcesz policzyć wszystkie komórki zawierające wartość w C4, zamiast wszystkich komórek równych C4, możesz dodać symbole wieloznaczne do takich kryteriów:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Teraz LICZ.JEŻELI zlicza komórki z podciągiem „Jan” w dowolnym miejscu komórki.
Występ
Ogólnie nie jest dobrą praktyką określanie zakresu obejmującego wszystkie komórki arkusza. Może to spowodować problemy z wydajnością, ponieważ zakres obejmuje miliony komórek. W tym przykładzie problem jest złożony, ponieważ formuła używa funkcji POŚREDNIE, która jest funkcją zmienną. Zmienne funkcje przeliczają się ponownie przy każdej zmianie arkusza, więc wpływ na wydajność może być ogromny.
Jeśli to możliwe, ogranicz zakresy do rozsądnego rozmiaru. Na przykład, jeśli wiesz, że dane nie pojawią się za wierszem 1000, możesz przeszukać tylko pierwsze 1000 wierszy w następujący sposób:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
