Pobierz przykładowy skoroszyt
W tym samouczku zademonstrujemy, jak używać funkcji SUMPRODUCT i SUMIFS do sumowania danych spełniających określone kryteria w wielu arkuszach w programie Excel i Arkuszach Google.
Regularna suma w wielu arkuszach
Czasami Twoje dane mogą obejmować kilka arkuszy w pliku Excel. Jest to typowe dla danych zbieranych okresowo. Każdy arkusz w skoroszycie może zawierać dane z określonego okresu. Potrzebujemy formuły sumującej dane zawarte w dwóch lub więcej arkuszach.
Funkcja SUM umożliwia łatwe sumowanie danych z wielu arkuszy za pomocą Odniesienie 3D:
| 1 | =SUMA(Arkusz1:Arkusz2!A1) |
Nie jest to jednak możliwe w przypadku funkcji SUMIFS. Zamiast tego musimy użyć bardziej skomplikowanej formuły.
Suma, jeśli w wielu arkuszach
Ten przykład zsumuje Liczba planowanych dostaw dla każdego Klient w wielu arkuszach roboczych, z których każdy zawiera dane dotyczące innego miesiąca, przy użyciu funkcji SUMATY, SUMPRODUCT i ADR.POŚREDNIA:
| 1 | =PROD.SUMA(SUMA.WARUNKI(ADR.POŚR("'"&F3:F6&"'!"&"D3:D7");ADR.POŚR("'"&F3:F6&"'!"&"C3:C7");H3)) |
Przejdźmy przez tę formułę.
Krok 1: Utwórz formułę SUMIFS tylko dla 1 arkusza wejściowego:
Używamy funkcji SUMIFS, aby zsumować Liczba planowanych dostaw za pomocą Klient dla pojedynczego arkusza danych wejściowych:
| 1 | =SUMA.WARUNKI(D3:D7;C3:C7;H3) |
Krok 2: Dodaj odwołanie do arkusza do wzoru
Wynik formuły zachowujemy bez zmian, ale określamy, że dane wejściowe znajdują się w arkuszu o nazwie 'Krok 2'
| 1 | =SUMATY('Krok 2'!D3:D7;'Krok 2'!C3:C7;H3) |
Krok 3: Zagnieżdżenie w funkcji SUMPRODUCT
Aby przygotować formułę do wykonywania obliczeń SUMIFS na wielu arkuszach, a następnie do zsumowania wyników, dodajemy funkcję SUMPRODUCT wokół formuły
| 1 | =PROD.SUMA(SUMY('Krok 3'!D3:D7;'Krok 3'!C3:C7;H3)) |
Użycie funkcji SUMA.FS na jednym arkuszu daje jedną wartość. Na wielu arkuszach funkcja SUMA.WARUNKI wyprowadza tablicę wartości (po jednej dla każdego arkusza). Używamy funkcji SUMPRODUCT do sumowania wartości w tej tablicy.
Krok 4: Zastąp odniesienie arkusza listą nazw arkuszy
Chcemy wymienić Nazwa arkusza część formuły z listą danych zawierającą wartości: Jan, luty, Zniszczyć, oraz kwiecień. Ta lista jest przechowywana w komórkach F3:F6.
Funkcja POŚREDNIA zapewniająca, że wyświetlana lista tekstowa Nazwy arkuszy jest traktowany jako część prawidłowego odwołania do komórki w funkcji SUMIFS.
| 1 | =PROD.SUMA(SUMA.WARUNKI(ADR.POŚR("'"&F3:F6&"'!"&"D3:D7");ADR.POŚR("'"&F3:F6&"'!"&"C3:C7");H3)) |
W tej formule wcześniej napisane odniesienie do zakresu:
| 1 | „Krok 3”!D3:D7 |
Zastępuje:
| 1 | ADR.POŚR("'"&F3:F6&"'!"&"D3:D7") |
Znaki cudzysłowu utrudniają odczytanie wzoru, dlatego tutaj jest on pokazany z dodanymi spacjami:
| 1 | POŚREDNIA ( " ' " & F3:F6 & " ' ! " & "D3:D7" ) |
Korzystanie z tego sposobu odwoływania się do listy komórek pozwala nam również podsumować dane z wielu arkuszy, które nie są zgodne ze stylem listy numerycznej. Standardowe odniesienie 3D wymagałoby, aby nazwy arkuszy były w stylu: Input1, Input2, Input3 itd., ale powyższy przykład umożliwia użycie listy dowolnych Nazwy arkuszy i mieć do nich odniesienie w osobnej komórce.
Blokowanie odwołań do komórek
Aby ułatwić czytanie naszych formuł, pokazaliśmy formuły bez zablokowanych odwołań do komórek:
| 1 | =PROD.SUMA(SUMA.WARUNKI(ADR.POŚR("'"&F3:F6&"'!"&"D3:D7");ADR.POŚR("'"&F3:F6&"'!"&"C3:C7");H3)) |
Ale te formuły nie będą działać poprawnie po skopiowaniu i wklejeniu w innym miejscu pliku. Zamiast tego powinieneś używać zablokowanych odwołań do komórek w następujący sposób:
| 1 | =SUMAPRODUKT(SUMA.ADR.("'"&$F$3:$F$6&"'!"&"D3:D7");ADR.POŚR("'"&$F$3:$F$6&"'!"& "C3:C7"),H3)) |
Przeczytaj nasz artykuł na temat blokowania odwołań do komórek, aby dowiedzieć się więcej.
Sumuj, jeśli w wielu arkuszach w Arkuszach Google
Używanie funkcji ADR.POŚR do odwoływania się do listy arkuszy w funkcji SUMPRODUCT i SUMIFS nie jest obecnie możliwe w Arkuszach Google.
Zamiast tego dla każdego arkusza wejściowego można wykonać oddzielne obliczenia SUMIFS, a wyniki dodać razem:
| 1234 | =SUMATY(sty!D3:D7;sty!C3:C7;H3)+SUMATY(lut!D3:D7;lut!C3:C7,H3)+SUMATY(Mar!D3:D7;Mar!C3:C7,H3)+SUMATY(Kwi!D3:D7;Kwi!C3:C7,H3) |
