W tym artykule dowiemy się, jak tworzyć kolumny obliczeniowe w programie Excel.
Scenariusz
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, przyjrzyjmy się najpierw różnym scenariuszom.
Jak utworzyć kolumnę obliczeniową w tabeli:
- Zaznacz komórkę w jednej z kolumn tabeli lub pustą komórkę od prawej do ostatniej kolumny, w poniższym przykładzie zaznaczona komórka to G2.
- Wstaw formułę obliczającą sprzedaż na jednostkę, wpisz symbol równości (=), wybierz komórkę F2, wpisz symbol podziału (/), a następnie wybierz komórkę E2 i naciśnij klawisz Enter.
Formuła jest automatycznie kopiowana do wszystkich komórek w kolumnie.
Przykład :
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.
| =SUMA PRODUKT((C3:N7)*(C2:N2=B10)) |
a to zwróci:
Jak to działa?
To jest proste. W formule stwierdzenie C2:N2=B10 zwraca tablicę, która zawiera wszystkie wartości FALSE z wyjątkiem jednej, która pasuje do B10. Teraz formuła to
| =PROD.SUMA((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, ,MECZ(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,,MECZ(B10,C2:N2,0))) |
To zwraca:
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(POŚREDNI(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(POŚREDNI(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&"]")) |
Mam nadzieję, że ten artykuł na temat tworzenia kolumn obliczeniowych w programie Excel jest objaśniający. Więcej artykułów na temat obliczania wartości i powiązanych formuł programu Excel znajdziesz tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na e-mail.
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…
SUMIF z referencją 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 Excela 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 :
Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.
Jak korzystać z funkcji 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.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.