Formuła tabeli przestawnej w programie Excel (spis treści)
- Formuła tabeli przestawnej w programie Excel
- Pole niestandardowe do obliczania kwoty zysku
- Zaawansowana formuła w polu obliczeniowym
Formuła tabeli przestawnej w programie Excel
Tabela przestawna to narzędzie, które pozwala nam analizować duże zakresy danych. Możemy analizować, interpretować i robić wiele innych rzeczy, nie tracąc przy tym dużej części głowy i potu. Może dać prawie wszystko, co jest w danych źródłowych.
Jeśli niektóre informacje znajdują się w danych źródłowych, być może będziemy musieli sami je obliczyć. Na przykład, jeśli mamy całkowitą kwotę sprzedaży i całkowity koszt, być może będziemy musieli sami obliczyć całkowity zysk lub stratę.
Nie musimy tego robić w źródle, ale możemy to zrobić w samej tabeli przestawnej, nazywane są one polami obliczeniowymi w tabeli przestawnej. Możemy użyć niestandardowych formuł, aby dane były w stanie opowiedzieć więcej historii na podstawie danych. Obliczone pola pozwalają nam zbudować nową kolumnę obliczeniową, która nie istnieje w rzeczywistym źródle danych.
W tym artykule zademonstrujemy sposoby korzystania z pól obliczeniowych tabeli przestawnej do tworzenia nowych kolumn w oparciu o nasze wymagania.
Pole niestandardowe do obliczania kwoty zysku
Możesz pobrać ten szablon Excel Formuła tabeli przestawnej tutaj - Szablon Excel Formuła tabeli przestawnejJest to najczęściej używane pole obliczeniowe w tabeli przestawnej. Spójrz na poniższe dane, mam kolumnę Nazwa kraju, Nazwa produktu, Jednostki sprzedane, Cena jednostkowa, Sprzedaż brutto, COGS (Koszt sprzedanych towarów), Data i Rok.
Pozwól mi zastosować tabelę przestawną, aby znaleźć całkowitą sprzedaż i całkowity koszt dla każdego kraju. Poniżej znajduje się tabela przestawna dla powyższych danych.
Problem polega na tym, że nie mam kolumny zysków w danych źródłowych. Muszę znaleźć zysk i procent zysku dla każdego kraju. Możemy dodać te dwie kolumny do samej tabeli przestawnej.
Krok 1: Wybierz komórkę w tabeli przestawnej. Przejdź do karty Analizuj na wstążce i wybierz Pola, Elementy i Zestawy. W tym polu wybierz Obliczone pole.
Krok 2: W poniższym oknie dialogowym podaj nazwę nowego pola obliczeniowego.
Krok 3: W sekcji Formuła zastosuj formułę, aby znaleźć zysk. Formuła znalezienia zysku to Sprzedaż brutto - KOSZY.
Wejdź do paska formuły> Wybierz Sprzedaż brutto z poniższego pola i kliknij dwukrotnie, aby pojawiło się na pasku formuły.
Teraz wpisz symbol minus (-) i wybierz COGS> Kliknij dwukrotnie.
Krok 4: Kliknij DODAJ i OK, aby zakończyć formułę.
Krok 5: Teraz mamy kolumnę ZYSK CAŁKOWITY w tabeli przestawnej.
To pole obliczeniowe jest elastyczne, nie ogranicza się tylko do analizy w skali kraju, ale możemy to wykorzystać do wszelkiego rodzaju analiz. Jeśli chcę zobaczyć analizę krajową i produktową, wystarczy przeciągnąć i upuścić kolumnę produktu w polu ROW, aby pokazać podział zysku dla każdego produktu w każdym kraju.
Krok 6: Teraz musimy obliczyć procent zysku. Wzór na obliczenie procentu zysku to Całkowity zysk / Sprzedaż brutto.
Przejdź do Analizuj i ponownie wybierz Obliczone pole w polach, elementach i zestawach.
Krok 7: Teraz musimy zobaczyć nowo wstawione obliczone pole Całkowity zysk na liście pól. Wstaw to pole do formuły.
Krok 8: Wpisz symbol rozdzielacza (/) i wstaw pole Sprzedaż brutto.
Krok 9: Nazwij to pole obliczeniowe jako procent zysku.
Krok 10: Kliknij DODAJ i OK, aby zakończyć formułę. Jako nową kolumnę mamy Procent zysku.
Zaawansowana formuła w polu obliczeniowym
Cokolwiek pokazałem teraz, jest podstawową funkcją Obliczonego Pola. W tym przykładzie pokażę Ci zaawansowane formuły w polach obliczanych w tabeli przestawnej. Teraz chcę obliczyć kwotę zachęty na podstawie procentu zysku.
Jeżeli Zysk% wynosi> 15%, motywacja powinna wynosić 6% całkowitego zysku.
Jeżeli Zysk% wynosi> 10%, motywacja powinna wynosić 5% całkowitego zysku.
Jeżeli Zysk% wynosi <10%, motywacja powinna wynosić 3% całkowitego zysku.
Krok 1: Przejdź do pola obliczeniowego i otwórz poniższe okno dialogowe. Podaj nazwę jako Kwota zachęty.
Krok 2: Teraz użyję warunku JEŻELI do obliczenia kwoty zachęty. Zastosuj poniższe formuły, jak pokazano na obrazku.
= JEŻELI („ProfitPercentage>> 15%, „ TotalProft ”* 6%, IF („ ProfitPercentage> 10%, „Total Proft” * 5%, „Total Proft” * 3%))
Krok 3: Kliknij DODAJ i OK, aby zakończyć. Teraz mamy kolumnę Kwota zachęty.
Ograniczenie pola obliczeniowego
Widzieliśmy cud obliczeń pól, ale ma też pewne ograniczenia. Teraz spójrz na poniższy obrazek, jeśli chcę zobaczyć rozbicie kwoty zachęty pod względem produktu, będziemy mieć błędne SUB TOTAL & GRAND TOTAL kwoty INCENTIVE AMOUNT.
Bądź więc ostrożny, pokazując sumę częściową pól obliczeniowych. To pokaże niewłaściwe kwoty.
Uzyskaj listę wszystkich obliczonych wzorów pól
Jeśli nie wiesz, ile formuł znajduje się w polu obliczeniowym tabeli przestawnej, możesz uzyskać podsumowanie wszystkich z nich w osobnym arkuszu.
Przejdź do opcji Analiza> Pola, przedmioty i zestawy -> Formuły list.
Daje ci podsumowanie wszystkich formuł w nowym arkuszu.
O czym należy pamiętać o formule tabeli przestawnej w programie Excel
- Możemy usunąć, zmodyfikować wszystkie pola obliczeniowe.
- Nie możemy używać formuł takich jak WYSZUKAJ.PIONOWO, SUMIF i wiele innych formuł związanych z zakresem w polach obliczeniowych, tzn. Nie można używać wszystkich formuł wymagających zasięgu.
Polecane artykuły
To był przewodnik po formule tabeli przestawnej w programie Excel. Tutaj omówiliśmy kroki w celu użycia formuły tabeli przestawnej w programie Excel wraz z przykładami i szablonem programu Excel do pobrania. Możesz również spojrzeć na te przydatne funkcje w programie Excel -
- Samouczki dotyczące wykresu przestawnego w programie Excel
- Tworzenie tabeli przestawnej w programie Excel
- Samouczek WYSZUKAJ.PIONOWO w programie Excel
- Excel Utwórz bazę danych