Do tej pory nauczyliśmy się sumować całą pasującą kolumnę tabeli w programie Excel. Ale jak sumujemy wartości, gdy musimy dopasować kolumnę i wiersz. W tym artykule dowiemy się, jak wykonać sumę pasujących wierszy i kolumn.
Są na to dwie formuły, ale najpierw zajmijmy się scenariuszem.
Tutaj mam tabelę, która rejestruje sprzedaż dokonaną przez pracowników w różnych miesiącach. Nazwiska pracowników mogą się powtarzać. Zobacz poniższy rysunek:
Musimy uzyskać sumę za majowy miesiąc, w którym sprzedawcą jest Donald.
Metoda 1: Podsumowanie pasującego nagłówka kolumny i nagłówka wiersza Przy użyciu funkcji SUMPRODUCT.
ten SUMPRODUCT Funkcja to najbardziej wszechstronna funkcja, jeśli chodzi o sumowanie i liczenie wartości z trudnymi kryteriami. Ogólna funkcja do sumowania przez dopasowanie kolumny i wiersza to:
| =SUMPRODUCT((kolumny)*(column_headers=column_heading)*(row_headers=row_heading) |
Kolumny:Jest to dwuwymiarowy zakres kolumn, które chcesz zsumować. Nie powinien zawierać nagłówków. W powyższej tabeli jest to C3:N7.
nagłówki_kolumny:Jest to zakres nagłówkakolumny które chcesz podsumować. W powyższych danych jest to C2:N2.
nagłówek kolumny: To jest nagłówek, który chcesz dopasować. W powyższym przykładzie jest to B13.
Bez dalszej zwłoki skorzystajmy ze wzoru.
row_headers:Jest to zakres nagłówkawydziwianie które chcesz podsumować. W powyższych danych jest to B3:B10.
wiersz_nagłówek: Jest to nagłówek, który chcesz dopasować w rzędach. W powyższym przykładzie jest to F13 .
Bez dalszej zwłoki skorzystajmy ze wzoru.
Napisz tę formułę w komórce D13 i pozwól programowi Excel zrobić magię (nie ma czegoś takiego jak magia)…
| =PROD.SUMA((C3:N10)*(C2:N2=B13)*(B3:B10=E13)) |
Zwraca wartość:
Teraz po zmianie miesiąca lub sprzedawcy suma zmieni się zgodnie z nagłówkiem wiersza i nagłówkiem kolumny.
Jak to działa?
Ta prosta logika logiczna.
(C2:N2=B13): Ta instrukcja zwróci tablicę TRUE i FALSE. Wszystkie pasujące wartości w kolumnie mają wartość prawda, a inne będą miały fałsz. W tym przypadku będziemy mieli tylko jedną wartość Prawda, ponieważ zakres C2:N2 zawiera tylko jedno wystąpienie maja o godzinie 5NS Lokalizacja.
(B3:B10=E13): Będzie to działać tak samo jak powyżej i zwróci tablicę TRUE i FALSE. Wszystkie pasujące wartości będą miały TRUE, a inne FALSE. W tym przypadku będziemy mieć 2 PRAWDA, ponieważ zakres B3:B10 ma dwa wystąpienia „Donald”.
(C2:N2=B13)*(B3:B10=E13): Teraz mnożymy tablice zwracane przez instrukcje. To zaimplementuje i loguje, a otrzymamy tablicę jedynek i zer. Teraz będziemy mieli tablicę 2D, która będzie zawierała 2 jedynki i pozostałe zera.
(C3:N10)*(C2:N2=B13)*(B3:B10=E13)= Na koniec mnożymy tablicę 2D przez tablicę 2D. Zwróci ponownie tablicę zer i liczb, które pasują do kryteriów.
Wreszcie SUMA PRODUKT funkcja zsumuje tablicę, co spowoduje uzyskanie pożądanego wyniku.
Metoda 2: Podsumowanie pasującego nagłówka kolumny i nagłówka wiersza Korzystanie z funkcji SUMA i JEŻELI
Ogólna formuła sumowania pasujących wierszy i kolumn za pomocą funkcji SUMA i JEŻELI Excel to:
| =SUMA(JEŻELI(nagłówki_kolumny=nagłówek_kolumny,JEŻELI(nagłówki_wiersza=nagłówek_wiersza,kolumny))) |
Wszystkie zmienne są takie same jak w opisanej powyżej metodzie. Tutaj wystarczy ich użyć w innej kolejności.
Napisz tę formułę w komórce D13:
| =SUMA(JEŻELI(C2:N2=B13;JEŻELI(B3:B10=E13;C3:N10))) |
Zwraca poprawną odpowiedź. Zobacz zrzut ekranu poniżej:
Jak to działa?
Logika jest taka sama jak w przypadku pierwszej metody SUMPRODCUT, tylko mechanizm jest inny. Jeśli wyjaśnię to w skrócie, wewnętrzna funkcja IF zwraca tablicę 2D o tym samym wymiarze co tabela. Ta tablica zawiera liczbę dwóch dopasowanych wierszy. Następnie wewnętrzna funkcja IF dopasowuje dwukolumnowe nagłówki w tej tablicy i zwraca tablicę 2D, która zawiera tylko liczby pasujące zarówno do kolumny, jak i nagłówka. Wszystkie inne elementy tablicy będą miały wartość FALSE.
Na koniec funkcja SUM sumuje tę tablicę i otrzymujemy naszą sumę.
Więc tak, w ten sposób można podsumować pasujące wiersze i kolumny z tabeli w programie Excel. Mam nadzieję, że było to dla ciebie wyjaśnione i przydatne. Jeśli masz jakiekolwiek wątpliwości dotyczące tego tematu lub masz inne wątpliwości związane z programem Excel/VBA, zapytaj w sekcji komentarzy poniżej.
Jak sumować kolumnę w Excelu, dopasowując nagłówek | Jeśli chcesz uzyskać sumę kolumny, używając tylko nazwy kolumny, możesz to zrobić na 3 proste sposoby w programie Excel. Składnia metody SUMPRODUCT do sumowania pasującej kolumny to:
SUMA.JEŻELI z odniesieniem 3D w programie Excel |Zabawnym faktem jest to, że normalne odwoływanie się do Excela 3D nie działa z funkcjami warunkowymi, takimi jak funkcja SUMA.JEŻELI. W tym artykule dowiemy się, jak uzyskać referencje 3D pracujące z funkcją SUMA.JEŻELI.
Odniesienie względne i bezwzględne w programie Excel | Odwoływanie się w Excelu to ważny temat dla każdego początkującego. Nawet doświadczeni użytkownicy programu Excel popełniają błędy w odwoływaniu się.
Odniesienie do arkusza dynamicznego | Podaj arkusze referencyjne dynamicznie, korzystając z funkcji ADR.POŚR programu Excel. To proste…
Rozszerzanie referencji w Excelu | Rozszerzające się odniesienie rozwija się po skopiowaniu w dół lub w prawo. W tym celu używamy znaku $ przed numerem kolumny i wiersza. Oto jeden przykład…
Wszystko o absolutnym referencji | Domyślny typ odniesienia w programie Excel jest względny, ale jeśli chcesz, aby odwołanie do komórek i zakresów było bezwzględne, użyj znaku $. Oto wszystkie aspekty odwoływania się bezwzględnego w programie Excel.
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Funkcja WYSZUKAJ.PIONOWO w programie Excel | Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
LICZ.JEŻELI w Excelu 2016 | Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby policzyć konkretną wartość. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.