Jeśli chcesz uzyskać sumę kolumny, używając tylko nazwy kolumny, możesz to zrobić na 3 proste sposoby w programie Excel. Zbadajmy te sposoby.
W przeciwieństwie do innych artykułów, najpierw przyjrzyjmy się scenariuszowi.
Tutaj mam tabelę sprzedaży dokonanych przez różnych sprzedawców w różnych miesiącach.
Teraz zadaniem jest zebranie sumy sprzedaży z danego miesiąca w Cell C10. Jeśli zmienimy miesiąc w B10, suma powinna się zmienić i zwróci sumę z tego miesiąca, bez zmiany czegokolwiek w formule.
Metoda 1: Sumuj całą kolumnę w tabeli za pomocą funkcji SUMPRODUCT.
Składnia metody SUMPRODUCT do sumowania pasującej kolumny to:
| =SUMPRODUCT((kolumny)*(nagłówki=nagłówek)) |
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:Jest to zakres nagłówka kolumny które chcesz podsumować. W powyższych danych jest to C2:N2.
Nagłówek: To jest nagłówek, który chcesz dopasować. W powyższym przykładzie jest to B10.
Bez dalszej zwłoki skorzystajmy ze wzoru.
| =PROD.SUMA((C3:N7)*(C2:N2=B10)) |
a to zwróci:
Jak to działa?
To jest proste. W formule stwierdzenieC2:N2=B10 zwraca tablicę, która zawiera wszystkie wartości FALSE z wyjątkiem jednej, która pasuje do B10. Teraz formuła to
| =SUMPRODUCT((C3:N7)*{FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA,FAŁSZ,FAŁSZ,FAŁSZ,FAŁSZ,FAŁSZ,FAŁSZ,FAŁSZ}) |
Teraz C3:N7 jest mnożone przez każdą wartość tej tablicy. Każda kolumna staje się zerem, z wyjątkiem kolumny, która jest pomnożona przez TRUE. Teraz formuła staje się:
| =PROD.SUMA({0,0,0,0,6,0,0,0,0,0,0,0,0;0,0,0,0,12,0,0,0,0,0,0, 0;0,0,0,0,15,0,0,0,0,0,0,0;0,0,0,0,15,0,0,0,0,0,0,0; 0,0,0,0,8,0,0,0,0,0,0,0}) |
Teraz ta tablica jest sumowana i otrzymujemy sumę kolumny, która pasuje do kolumny w komórce B10.
Metoda 2: Sumuj całą kolumnę w tabeli za pomocą funkcji INDEX-MATCH.
Składnia metody sumowania pasującego nagłówka kolumny w programie Excel to:
| =SUMA(INDEKS(kolumny;,PODZIEL(nagłówek;nagłówki;0))) |
Wszystkie zmienne w tej metodzie są takie same jak w metodzie SUMPRODUCT. Po prostu zaimplementujmy to, aby rozwiązać problem. Zapisz tę formułę w C10.
| =SUMA(INDEKS(C3:N7;PODZIEL(B10;C2:N2,0))) |
To zwraca:
Jak to działa?
Formuła jest rozwiązana na lewą stronę. Najpierw funkcja PODAJ.POZYCJĘ zwraca indeks pasującego miesiąca z zakresu C2:N2. Ponieważ mamy maj w B1o, otrzymujemy 5. Teraz formuła staje się
| =SUMA(INDEKS(C3:N7;5)) |
Następnie funkcja INDEKS zwraca wartości z piątej kolumny C3:N7. Teraz formuła staje się:
| =SUMA({6;12;15;15;8}) |
I na koniec otrzymujemy sumę tych wartości.
Metoda 3: Sumuj całą kolumnę w tabeli przy użyciu nazwanego zakresu i funkcji ADR.POŚR
Wszystko staje się proste, jeśli nazwiesz swoje zakresy jako nagłówki kolumn. W tej metodzie najpierw musimy nazwać kolumny jako ich nazwy nagłówków.
Wybierz tabelę zawierającą nagłówki i naciśnij klawisze CTRL+SHIFT+F3. Otworzy się okno dialogowe, w którym można utworzyć nazwę z zakresów. Sprawdź górny rząd i naciśnij przycisk OK.
Wszystkie kolumny danych zostaną nazwane jako ich nagłówki.
Teraz ogólna formuła sumująca pasującą kolumnę będzie:
| =SUMA(ADR.POŚR(nagłówek)) |
Nagłówek: Jest to nazwa kolumny, którą chcesz zsumować. W tym przykładzie to B10 zawiera od teraz może.
Aby zaimplementować tę ogólną formułę, wpisz tę formułę w komórce C10.
| =SUMA(ADR.POŚR(B10)) |
Zwraca sumę miesiąca majowego:
Inna metoda jest podobna do tej. W tej metodzie używamy tabel programu Excel i jest to strukturalne nazewnictwo. Powiedzmy, że nazwałeś powyższą tabelę jako table1. Wtedy ta formuła będzie działać tak samo jak powyższa formuła.
| =SUMA(ADR.POŚR("Tabela1["&B10&"]")) |
Jak to działa?
W tej formule funkcja ADR.POŚR przyjmuje odwołanie do nazwy i konwertuje je na rzeczywiste odwołanie do nazwy. Procedura jest prosta. Funkcja SUMA sumuje nazwany zakres.
Więc tak, w ten sposób można zsumować pasującą kolumnę w programie Excel. Mam nadzieję, że jest to dla Ciebie pomocne i wyjaśniające. Jeśli masz jakiekolwiek wątpliwości dotyczące tego artykułu lub jakiegokolwiek innego tematu związanego z programem Excel/VBA, zapytaj w sekcji komentarzy poniżej.
Jak sumować, dopasowując wiersz i kolumnę w programie Excel |SUMPRODUCT jest najbardziej wszechstronną funkcją, jeśli chodzi o sumowanie i liczenie wartości z trudnymi kryteriami. Ogólna funkcja do sumowania przez dopasowanie kolumny i wiersza 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.