Formuła ogólna
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Podsumowanie
Aby warunkowo zsumować identyczne zakresy, które istnieją w oddzielnych arkuszach, wszystkie w jednej formule, możesz użyć funkcji SUMA.JEŻELI z POŚREDNIE, opakowaną w SUMPRODUCT. W pokazanym przykładzie formuła w C9 to:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Wyjaśnienie
Dane na każdym z trzech przetwarzanych arkuszy wyglądają następująco:
Przede wszystkim należy pamiętać, że nie można używać funkcji SUMIF z „normalnym” odniesieniem 3D, takim jak ten:
Sheet1:Sheet3!D4:D5
To jest standardowa „składnia 3D”, ale jeśli spróbujesz jej użyć z funkcją SUMA.JEŻELI, zostanie wyświetlony błąd #ARG. Aby obejść ten problem, możesz użyć nazwanego zakresu „arkuszy”, który zawiera listę każdego arkusza (karty arkusza), który chcesz dołączyć. Jednak aby zbudować odwołania, które program Excel będzie poprawnie zinterpretować, musimy połączyć nazwy arkuszy z zakresami, z którymi musimy pracować, a następnie użyć opcji POŚREDNIE, aby program Excel poprawnie je rozpoznał.
Ponadto, ponieważ nazwany zakres „arkusze” zawiera wiele wartości (tj. Jest to tablica), wynik funkcji SUMA.JEŻELI w tym przypadku jest również tablicą (czasami nazywaną „tablicą wynikową”). Dlatego do obsługi tego używamy SUMPRODUCT, ponieważ SUMPRODUCT ma możliwość obsługi tablic w sposób natywny bez konieczności używania klawiszy Ctrl-Shift-Enter, podobnie jak wiele innych formuł tablicowych.
Inny sposób
Powyższy przykład jest nieco skomplikowany. Innym sposobem rozwiązania tego problemu jest wykonanie „lokalnej” sumy warunkowej na każdym arkuszu, a następnie użycie zwykłej sumy 3D w celu zsumowania każdej wartości na karcie podsumowania.
Aby to zrobić, dodaj formułę SUMA.JEŻELI do każdego arkusza, który używa komórki kryteriów w arkuszu podsumowania. Następnie po zmianie kryteriów wszystkie połączone formuły SUMA.JEŻELI zostaną zaktualizowane.
