Analizuj dane sprzedażowe za pomocą Excel Pivot Tables

WSTĘP DO TUTORIA ANALIZY SPRZEDAŻY

W tym poradniku dowiesz się, jak zaimportować dane w pliku “tekstowym” za pomocą kreatora importu w Excelu, a następnie przeanalizować je za pomocą tabel przestawnych.

Aby rozpocząć, należy najpierw pobrać plik STARTUP. Jest to plik Word (jedna strona). Gdy pojawi się on na Twoim ekranie, wybierz Plik – Drukuj, aby wydrukować drukowaną kopię, a następnie wróć do tego punktu w Serwisie Księgowym. Aby pobrać ten plik teraz, kliknij dwukrotnie na Startup.

Korzystając z instrukcji w programie STARTUP, pobierz teraz dwa pliki, których będziesz używać w samouczku. Są to pliki Word i txt

.

Jeśli Twój własny pakiet kont posiada program Generator raportów, powinieneś mieć możliwość użycia go do wyeksportowania podobnych danych do pliku tekstowego, który następnie może zostać odczytany przez program Excel.

“Tekst” to format o najniższym wspólnym mianowniku, który wszystkie komputery mogą rozpoznać, ponieważ nie zawiera on żadnych znaków specjalnych, poza uniwersalnymi, takimi jak Powrót do wózka, Tab lub Przecinek. Pliki w formacie tekstowym są czasami nazywane również plikami “CDF” (Comma Delimited Files), “CSV” (Comma Separated Values) lub “ASCII”.

Nie martw się zbytnio o warunki techniczne. Excel rozpoznaje wszystkie te formaty i może je “importować” automatycznie.

Zobaczysz, że tabele przestawne pozwalają na analizę faktur sprzedaży w dowolny sposób – sprzedaż według klientów, sprzedaż według grup produktów, sprzedaż według okresów, marże brutto według grup produktów itp.

Jeśli kiedykolwiek słyszałeś o pojęciu “Hurtownia Danych”, to może zainteresuje cię, że właśnie to będziesz robił przez najbliższe pół godziny – zakładanie i przesłuchiwanie hurtowni danych.

Ten tutorial został opracowany przy użyciu Excel 97 i przetestowany w Excel 2000 i Excel 5. Odchylenia od Excela 97 są odnotowywane w nawiasach kwadratowych [ ], gdy się pojawiają. Powinno ci to zająć około 30-45 minut. Jeśli to możliwe, dobrze jest, aby dwie osoby pracowały nad nim razem, jedna czytając instrukcje, druga obsługując klawiaturę.

1. PRZYWOZUJĄCE WYPEŁNIENIA TEKSTOWE DO EXCELU

Uruchomić Excel. Potem: Plik – Otwórz

Pliki typu: (na dole) zmiana z Microsoft Excel Workbook na Text Files

Wybierz SALANAL [Excel 5 = SALANAL.TXT]

Pojawia się kreator importu tekstu, krok 1 z 3.

Program Excel powinien był ustalić, że typ danych jest ograniczony, a nie o stałej szerokości. Jeśli tak, kliknij przycisk Dalej.

Kreator importu tekstu – pojawia się krok 2 z 3.

Pomiędzy kolumnami powinny znajdować się pionowe linie (program Excel zidentyfikował znaczniki zakładek jako ograniczniki).

Dodatkowo, w nagłówku każdej kolumny można zobaczyć nazwy pól – TRANSN, INVNO, DATA, ACCNO itd.

Kliknij Next. Pojawi się krok 3 z 3.

Kolumna TRANSN jest podświetlona. W nagłówku kolumny znajduje się format, w którym będzie ona importowana – format “Ogólny”. Program Excel przyjmuje format OGÓLNY dla wszystkich pól.

W prawym górnym rogu ekranu znajdują się możliwe formaty danych kolumn – General Text Date & Skip (tzn. nie importuj).

Ogólny format jest OK dla wszystkich pól z wyjątkiem DATA. Będziesz musiał zmienić format daty.

W polu Column Data Format opcja “Data” powinna mieć domyślną wartość DMY względem niej. Oznacza to, że program Excel oczekuje, że wszystkie pola daty, które znajdzie w pliku tekstowym, będą w formacie Day/Month/Year, który jest standardem brytyjskim.

Jeśli DMY nie jest oferowany domyślnie, wybierz DMY z listy, tak aby był dostępny.

Jeśli wartością domyślną nie jest DMY, ale MDY, oznacza to, że komputer jest ustawiony na wyrażanie dat w formacie miesiąc/dzień/rok USA. Jeśli jesteś użytkownikiem z Wielkiej Brytanii, musisz zmienić to teraz na format brytyjski. Anuluj kreator importu i wyjdź z programu Excel. Zamknij wszystkie inne programy, ponieważ będziesz musiał zrestartować swój komputer. Następnie wybierz:

Start – Ustawienia – Panel sterowania – Ustawienia regionalne. Zmiana z angielskiego (Stany Zjednoczone) na angielski (Wielka Brytania).

Uruchomić ponownie komputer zgodnie z instrukcją lub w razie potrzeby. Uruchomić ponownie samouczek. Tym razem kreator importu powinien domyślnie oferować DMY].

Kliknij na nagłówek “Ogólne” w trzeciej kolumnie, DATA. Kolumna jest podświetlona

Na górze po prawej stronie kliknij przycisk Data. Nagłówek kolumny DATA powinien teraz brzmieć DMY.

Kliknij na Finish. Poczekaj chwilę. Plik jest importowany do Excela.

2. PORZĄDKOWANIE ZE SKŁADEM

Plik tekstowy zaimportowany z innego systemu straci większość swojego formatowania. Dlatego najpierw należy go uporządkować:

Zaznacz pierwszy wiersz arkusza kalkulacyjnego, klikając na 1 po jego lewej stronie. Następnie kliknij B na pasku narzędzi, aby nagłówki kolumn stały się pogrubione.

Podświetlić cały arkusz klikając na górną szarą cegiełkę LH nad 1 i na lewo od A.

.

Dwukrotne kliknięcie pomiędzy nagłówkami kolumn A i B. Wszystkie kolumny są poszerzone w celu jak najlepszego dopasowania.

Zaznacz drugi wiersz arkusza kalkulacyjnego, klikając na 2. Następnie wybierz: Okno – Zamrażanie szyb

Zanim przejdziesz dalej, skopiuj plik do odpowiedniego katalogu i przekonwertuj go do formatu Excel, w następujący sposób:

Plik – Zapisz jako. Zostawiamy nazwę pliku jako SALANAL, ale poniżej nazwy pliku zmieniamy ją w następujący sposób.

Zapisz jako Typ: Zmień z Text (Tab delimited) na Microsoft Excel Workbook. Zapisz.

3. CO STANOWIĄ PŁYWY

Jest to zestaw rekordów pobranych z pliku invoice_detail w pakiecie kont. Pola te są:

TRANSN = Rodzaj transakcji (np. faktura lub nota kredytowa w tym przypadku)

INVNO = Numer faktury lub noty kredytowej

DATA = Data wystawienia faktury lub noty kredytowej

ACCNO = Kod konta klienta

CUSTNAME

PARTNO

OPIS

MANUFR

PRODGROUP = Grupa Produktów

QTY = Ilość Fakturowana

S.P. = Cena sprzedaży przedmiotu

NET = Wartość netto linii (tj. Qty x S.P.)

C.P. = Cena Koszt artykułu

KOSZT = Koszt linii (tzn. Qty x CP)

MRGN = Line Margin (tzn. NET – COST)

SALES EXEC = nasz przedstawiciel handlowy

Tak więc, biorąc za przykład dwie pierwsze linie, 4 kwietnia 1995 r. wystawiliśmy fakturę sprzedaży nr SIN001649 na numer klienta. R001, Rolls Royce Ltd, na dwa produkty – HP51625A Colour Print Cartridge, ilość 2, po 18 funtów każdy, oraz US Robotics Sportster 28.8 Zewnętrzny modem, ilość 1, po 228 funtów każdy.

4. RUCHOMY POWROTU POWROTU

4.

Przewiń około 100 pierwszych rekordów, aby zobaczyć, jakie dane zawiera dany plik. Następnie:

Przejdź do sedna sprawy: (wskazówka: skrót klawiszowy = przytrzymaj klawisz Ctrl, a następnie naciśnij strzałkę w dół)

Istnieje 4 188 wierszy (tj. 4 188 zapisów dotyczących szczegółów faktur sprzedaży)

Przejdź do dolnej części kolumny L, kolumny NET.

Autosum kolumna – pamiętaj, aby nacisnąć przycisk powrotu. Powinna ona wynosić 5.442.458 funtów

.

Zanotuj sumę kolumn. Następnie usuń sumę…

Wróć do górnej części pliku, komórka A2. (skrót klawiszowy = przytrzymaj klawisz Ctrl, a następnie naciśnij klawisz “Home”)

.

5. GENEROWANIE TABELI PIONOWEJ

Wskaźnik komórki powinien znajdować się na A2 (lub w dowolnym miejscu w obrębie danych)

.

Będziemy budować stół obrotowy krok po kroku. Z menu głównego w górnej części ekranu wybierz :

Dane – raport tabeli przestawnej [Excel 2000 = raport tabeli przestawnej i wykresu przestawnego, Excel 5 = tabela przestawna]

Zostanie wyświetlony Kreator tabeli przestawnej, krok 1 z 4 ekranów. Kliknij Next

.

Krok 2 z 4 Pudełko Range powinno mówić: $A$1:$P$4188.

[Wcześniejsze wersje Excela czasami nie odbierały zasięgu automatycznie.] Jeśli pole Range jest puste lub mówi “Database” wpisz $A$1:$P$4188].

Kliknij na Next.

Krok 3 z 4: Pojawia się pole COLUMN-ROW-DATA. Po prawej stronie znajdują się wszystkie nagłówki kolumn w arkuszu kalkulacyjnym
.
Jeśli korzystasz z programu Excel 2000, musisz najpierw kliknąć na Layout]
.
Przeciągnij i upuść te nagłówki w następujący sposób:

PRODGROUP do obszaru ROW

NET do obszaru DANYCH

[Po wrzuceniu do obszaru DATA, NET powinien teraz powiedzieć “Suma NET”. Jeśli jest napisane “Count of NET”, należy kliknąć dwukrotnie na Count of NET. Pojawi się okienko. Zmień “Count” na “Sum”]

Teraz kliknij na Finish. [jeśli używasz Excel 2000, OK, a następnie Finish]

Obok każdej z nich znajduje się lista grup produktów. Na dole arkusza znajduje się suma, którą zanotowałeś.

Z 4 188 rekordów w arkuszu roboczym SALANAL Excel wygenerował tabelę przestawną podsumowującą je w podziale na grupy produktów.

Spójrz na dolną lewą stronę ekranu. Aby utworzyć tabelę przestawną, program Excel wygenerował nowy arkusz po lewej stronie ekranu SALANAL. Ten arkusz nosi prawdopodobnie nazwę Arkusz1.

Kliknij z powrotem na arkusz roboczy SALANAL, aby potwierdzić, że oryginalna baza danych faktur sprzedaży nadal tam jest.

Teraz kliknij prawym przyciskiem myszy z powrotem na nowy arkusz, który zawiera tabelę przegubową. Zmień nazwę tego arkusza na PIVOT.

6. SORTOWANIE TABELI PIONOWEJ

Chcesz uszeregować grupy produktów według wartości. Aby to zrobić:

Kliknąć w dowolnym miejscu kolumny “Total”, np. w komórce B10

.

Kliknąć na ikonę ZA. Grupy produktów są posortowane w kolejności malejącej, z PC na górze.

[jeśli nie masz ikony ZA, użyj zamiast niej ikony AZ]

Teraz należy posortować stół obrotowy z powrotem w kolejności grup produktów, w następujący sposób:

Kliknij lewym przyciskiem myszy na szare pole PRODGROUP w komórce A2. Kolumna jest podświetlona. [nie w programie Excel w wersji 5.0]

[Uwaga do użytkowników Excel 2000. Odniesienia do komórek w tabelach przestawnych są zazwyczaj dwa wiersze niżej w programie Excel 2000 w porównaniu z innymi wersjami programu. Komórka A2 oznacza zatem zazwyczaj komórkę A4 itd.].

Kliknij na ikonę AZ. Grupy produktów są posortowane w porządku alfabetycznym, rosnąco.

7. SPRZEDAŻ ANALIZOWA DLA KLIENTA

Teraz chciałbyś zobaczyć dane inaczej, tym razem patrząc na sprzedaż według klientów.

Kliknij w dowolnym miejscu w tabeli obrotowej, a następnie kliknij prawym przyciskiem myszy.

Pojawi się menu z “Formatowanie komórek” na górze lub na środku.

Z menu wybierz Kreator. [w programie Excel 5 lub Excel 7 = tabela przestawna]

Ponownie pojawia się pole danych COLUMN-ROW-DATA. [Excel 2000, wybierz najpierw układ]

Przeciągnij i upuść PRODGROUP w dowolnym miejscu z obszaru ROW.

Wrzuć ACCNO do obszaru ROW.

Następnie zrzuć CUSTNAME poniżej ACCNO w obszarze ROW. Następnie zakończ.

W tabeli obrotowej wyświetlana jest teraz sprzedaż według numeru konta klienta i nazwy. Ekran nie jest jednak zbyt czytelny, ponieważ ACCNO jest zsumowany. Te sumy muszą zostać usunięte:.

Podwójne kliknięcie prawym przyciskiem myszy na szary przycisk pola ACCNO, komórka A2. Pojawi się menu

Wybierz: Pole [Excel 2000 = Ustawienia pól; Excel 5 lub Excel 7 = Pole tabeli przestawnej].

W obszarze Subtotals po lewej stronie, zmień z Automatic na None. OK

To wygląda lepiej. Zauważ, że w sumie znowu 5,442,458.

Teraz ponownie kliknij lewym przyciskiem myszy na przycisk pola ACCNO w komórce A2. Kolumna zostanie podświetlona. [nie w programie Excel 5]

Wyśrodkuj kody kont, klikając ikonę “Wyśrodkuj”.

8. SPRZEDAŻ SPRZEDAŻY ŁUKAMANIOWEJ W CIĄGU MIESIĘCY (“GRUPA I OUTLINA”)

Faktury sprzedaży były datowane od kwietnia do września. Teraz, aby podzielić sumy na miesiące:

Kliknij prawym przyciskiem myszy w obrębie tabeli obrotowej. Pojawi się menu. Wybierz:

Kreator [Excel 5 lub 7 = Tabela przestawna] Ponownie pojawia się okno COLUMN-ROW-DATA. [Excel 2000, wybierz najpierw układ]

Przeciągnij i upuść DATE do obszaru COLUMN. Skończyć. Tabela obrotowa jest przeliczana.

Na górze jest teraz seria randek. Daty muszą być pogrupowane według miesięcy. Aby to zrobić:

Podwójne kliknięcie prawym przyciskiem myszy na szare pole DATA w komórce C1. Pojawi się menu.

Wybierz: Group & Outline – Grupa Teraz powinno się pojawić okno dialogowe “Grouping”.

[Jeśli nie, ale dostaniesz komunikat “Cannot Group That selection”, zobacz paragraf poniżej].

Wybierz Miesiące i Lata (na dole listy w sekcji Kwartały). OK.

W tabeli obrotowej wyświetlana jest teraz sprzedaż netto według miesięcy.

[Komunikat o błędzie – “Nie można zgrupować tego wyboru” Polecenie “Zgrupowanie” jest umiarkowane. Mam więcej problemów z grupowaniem pól daty niż wszystkie inne problemy razem wzięte. A jeśli się nie powiedzie, jedyna informacja zwrotna, którą otrzymasz to “Nie można pogrupować tego wyboru”.

Istnieją dwa prawdopodobne źródła błędów. Po pierwsze, domyślny format daty w Windows jest ustawiony na amerykański format miesiąc/dzień/rok, podczas gdy pola daty w importowanych danych są wyrażone w brytyjskim formacie dzień/miesiąc/rok. Aby to sprawdzić, przyjrzyj się bliżej sposobowi, w jaki daty są obecnie formatowane w formacie Excel.
.
Jeśli są to Stany Zjednoczone, z pierwszym miesiącem musisz zresetować swoje Regionalne Sektory w Panelu Zarządzania, jak opisano w sekcji 1 powyżej, a następnie ponownie uruchomić tutorial. Drugą możliwością jest to, że jedno lub więcej pól daty w twoich danych jest albo puste, albo nie jest poprawnie sformatowane jako daty.

Najpierw należy przejść do arkusza SALANALNEGO, kliknąć w dowolnym miejscu kolumny DATA, ALE NIE KOLUMNUJĄCA ILOŚĆ, a następnie kliknąć ikonę AZ. Spowoduje to uporządkowanie bazy danych według daty. Spójrz na pierwszą i ostatnią datę w arkuszu, ponieważ właśnie w tym miejscu pojawią się błędne daty. Poprawić ewentualne błędy, a następnie odświeżyć Data.

Po drugie, sprawdź, czy Zakres nie obejmuje żadnych pustych wierszy, które będą zawierały puste pola daty. Zrób to za pomocą Kreatora – Wróć, który powinien pokazać Range $A$1:$P$4188. Jeśli mówi on $A$1:$P$4189, to trzeba go zmienić na $P$4188.
.
Nawet po usunięciu wszystkich błędów, po odświeżeniu danych może pojawić się komunikat “Nie można grupować tego wyboru”. W takim przypadku usuń tabelę przestawną i zbuduj ją ponownie od podstaw i może tym razem zadziałać. W końcu, czasami okazuje się, że kiedy umieściłem pole Data w obszarze wiersza, to nie będzie Group, ale jeśli umieściłem go w obszarze Column, to będzie.

Jeśli daty nie będą grupowane w obszarze Wiersz, ale w Kolumnie, zgrupuj je najpierw w Kolumnie, a następnie przejdź do kreatora i przenieś lata/daty z Kolumny i do Wiersza. Zobaczysz, że grupowanie pozostaje OK.]

9. NUMERYCZNE LICZBY W TABELI PIONOWEJ

Dane dotyczące sprzedaży byłyby łatwiejsze do odczytania, gdyby tysiące były oddzielone przecinkami. Dlatego też:

Kliknij prawym przyciskiem myszy na dowolny numer w tabeli obrotowej, a następnie wybierz z menu: Pole.

[lub Ustawienia pola, lub Pole tabeli przestawnej]

Pojawi się okno dialogowe “Pole tabeli obrotowej”. Z listy opcji po prawej stronie wybierz:

Numer. Pojawia się pole “Formatowanie komórek”.

Z listy “Kategoria” wybierz: Numer [Excel 5 – wybierz format #,##0; [Red]-#,##0].

Potem: Miejsca dziesiętne = 0 – użyć separatora 1000, TAK – – -1234 w kolorze czerwonym OK OK

Tabela obrotowa zawiera teraz przecinki oddzielające tysiące.

10. DRUKOWANIE TABELI PIONOWEJ

Wydrukujmy tabelę obrotową przedstawiającą miesięczną sprzedaż w podziale na klientów.

Najpierw należy kliknąć ikonę Podglądu wydruku, aby zobaczyć gotowy raport na ekranie [lub z menu Plik – Podgląd wydruku]

.

Ten raport wyglądałby lepiej, gdyby został wydrukowany w Landscape. Dlatego:

Wybierz: Setup – Page – Landscape – OK Następnie,

Wybierz: Konfiguracja – Arkusz – zaznacz pole wyboru Linie kratowe – OK

Drukuj do druku.

11. sprzedaż analityczna przez producenta

Do tej pory analizowaliśmy sprzedaż w podziale na grupy produktów i klientów. Teraz przeanalizujemy je według producenta, a następnie zobaczymy, które produkty producenta sprzedają się najlepiej.

Kliknij prawym przyciskiem myszy w obrębie tabeli przestawnej, a następnie z menu wybierz Kreator [Excel 5 lub 7 = Tabela przestawna]

.

Ponownie pojawia się pole danych COLUMN-ROW-DATA.

Usuń ACCNO i CUSTNAME, upuszczając je w dowolnym miejscu poza stołem.

Przeciągnij i upuść MANUFR-a do obszaru ROW.

Dodatkowo, upuść PRODGROUP na obszar PAGE.

Wykończenie

Tabela obrotowa jest przeliczana, tym razem z uwzględnieniem sprzedaży według producenta.

[Lista producentów powinna być uporządkowana alfabetycznie, zaczynając od 3COM. Jednak czasami DEC pojawia się pierwszy – nie pytaj mnie dlaczego. Jeśli tak, posortuj producentów alfabetycznie w następujący sposób:

Kliknij lewym przyciskiem myszy na szary przycisk MANUFR w komórce A5. Kolumna jest podświetlona.

Kliknij na ikonę AZ. Kolumna zostanie posortowana i DEC przejdzie do odpowiedniej pozycji.

12. DODAWANIE DALSZEJ WYMIARU Z POKŁADAMI STRONOWYMI

Do tej pory dane prezentowane są w dwóch wymiarach, według wierszy i kolumn. Można jednak dodać trzeci wymiar analizy, korzystając z pola PAGE.

Na przykład, możemy chcieć zobaczyć, która marka PC sprzedaje się najlepiej. Dlatego:

PRODGROUP to pole strony w komórce A1. Kliknij na strzałkę w dół po prawej stronie komórki B1.

(Wszystkie), COMMS, INTERNET, MEMORY, MONITORY itp. Podświetlić PC i kliknąć.

Tabela obrotowa pokazuje teraz tylko tych producentów, którzy sprzedają komputery PC.

Całkowita sprzedaż komputerów osobistych wyniosła 2 813 885 funtów. Najlepiej sprzedawała się DEC, której sprzedaż wyniosła 1 026 424 GBP.

Teraz kliknij ponownie na strzałkę B1 w dół i spójrz na sprzedaż dla PRINTERS.

Podana jest łączna wartość sprzedaży w podziale na producentów (243 132 funty). Ale które modele sprzedawały się najlepiej?

Aby się tego dowiedzieć, kliknij prawym przyciskiem myszy w dowolnym miejscu w tabeli obrotowej. Z menu wybierz Kreator

Przeciągnij PARTNO i upuść go pod MANUFR-em w obszarze ROW.

Przeciągnij OPIS i upuść go pod PARTNO w obszarze ROW

W obszarze ROW kliknij dwukrotnie na PARTNO. Pojawi się pole tabeli obrotowej

.

Zmiana sumy częściowej z automatycznej na żadną. OK Zakończenie

Tabela obrotowa jest wyświetlana na nowo, ale tym razem pokazuje sprzedaż poszczególnych modeli drukarek. W razie potrzeby szersza kolumna B.

Uwaga: jeśli otrzymasz komunikat “Microsoft Excel nie może dokonać tej zmiany, ponieważ jest zbyt wiele elementów wierszy lub kolumn”, Twój komputer nie ma wystarczającej ilości pamięci, aby pomieścić zarówno pole PARTNO jak i OPIS w tabeli przestawnej. Spróbuj po prostu dodać tylko PARTNO i zobacz czy to działa].

13. TYTUŁ PODWÓJNYCH LINII KOLUMNOWYCH I GŁOWIN (tylko Excel 97 i Excel 2000)

W programach Excel 5 i Excel 7 jakiekolwiek formatowanie zastosowane do tabeli przestawnej zostało utracone w momencie ponownego obliczenia tabeli przestawnej lub odświeżenia danych. Dopiero wprowadzenie opcji “Wybierz” w programie Excel 97 spowodowało, że formatowanie “trzymało się” po ponownym obliczeniu tabeli przestawnej.

W tej sekcji przedstawiono użytkownikom programów Excel 1997 i Excel 2000 sposób stosowania cieniowania w liniach szczegółowych “CANON Total”, “EPSON Total” w tabeli. Użytkownicy programów Excel 5 i Excel 7 powinni przejść do następnej sekcji.

[Excel 97]

Kliknij na “CANON Total” w komórce A15. Wszystkie linie sumy częściowej są podświetlone
.
lub

[Excel 2000]

Przesuń wskaźnik myszy na szarą 15 cegiełkę naprzeciwko komórki A15, “CANON Total”. Przesuwaj go stopniowo w prawo, aż przyjmie kształt grubej, poziomej, czarnej strzałki. Następnie kliknij lewym przyciskiem myszy. Wszystkie linie sumy cząstkowej są podświetlone

.

Aby dodać cieniowanie, kliknij na żółtą ikonę “Fill Color” w dolnym rzędzie ikon, drugą od prawej (ta, która wygląda jak wlewanie puszki z farbą).

Teraz kliknij gdziekolwiek, aby usunąć podświetlenie. Linie “Total” są wszystkie zacienione na żółto.

Dodatkowo, zacienimy i wyśrodkujemy miesięczne nagłówki kolumn, w następujący sposób:

Kliknij na przycisk pola DATA w komórce E3. Nagłówki kolumn “Apr” do “Sep” są podświetlone.

Zastosuj ponownie żółtą ikonę Fill Color. Lub jeśli wolisz inny kolor, kliknij na strzałkę w dół po prawej stronie ikony i wybierz kolor z palety. Osobiście podoba mi się jasnoniebieski lub jasnozielony w dolnym rzędzie.

Wyśrodkuj teraz miesiące, klikając na ikonę Centrum. Kliknij w dowolnym miejscu, aby usunąć podświetlenie.

14. DRUKOWANIE TABELI PIONOWEJ

Wydrukujemy ten raport i przekażemy go kierownictwu.

Najpierw zrób Podgląd wydruku (który powinien być w trybie Krajobraz) .

Zwróć uwagę, że w dolnej części LH ekranu podglądu wydruku jest napisane Strona 1 z 2. (a może 1 z 3)

Kliknij Next, aby zobaczyć podgląd wydruku strony 2. Strona 2 utraciła nagłówki kolumn

Aby zachować nagłówki kolumn na wszystkich stronach należy użyć opcji Plik – Ustawienia strony z menu głównego.

Najpierw, Zamknij podgląd wydruku. Następnie z menu głównego wybierz: Plik – Ustawienia strony

Kliknij na zakładkę Arkusz

Kliknij na pole Rows To Repeat w górnej części.

Wpisz $1:$5 (w przeciwnym razie przesuń myszkę do górnej części LH arkusza kalkulacyjnego, a następnie przeciągnij ją w dół nad cegłami od 1 do 5).

Po drugie, zaznaczyć pole “Linia kratowa”

Teraz kliknij na zakładkę Nagłówek/Samochód

.

Wybierz: Nagłówek niestandardowy

Lewa sekcja: Typ: Wydrukowano: następnie kliknij na ikonę 8/7. &[Data] pojawia się

Prawa sekcja: Typ: Strona: kliknij na ikonę # Typ: z kliknięciem na ikonę ++.

Sekcja centralna: Wpisz wielkimi literami ANALIZĘ SPRZEDAŻY GŁÓWNEJ W MODELU [Excel 5, usuń &tab]

Podświetl ten tekst myszą.

Kliknąć na ikonę A. Pojawi się pole “Czcionka”

Wybierz pogrubienie i 10

OK – OK

Teraz kliknij ikonę Podglądu wydruku i spójrz na stronę 1 i stronę 2. Strona 2 ma teraz nagłówki kolumn

Teraz wydrukuj, aby wydrukować raport.

15. BADANIE TYTUŁU: SPRZEDAŻ SPRZEDAŻY SPRZEDAŻY EXEC

Do tej pory analizowaliśmy według Grupy Produktów, Klienta i Producenta. Baza danych SALANAL rejestruje również, który przedstawiciel handlowy dokonał sprzedaży w obszarze SPRZEDAŻY EXEC.

Kliknij prawym przyciskiem myszy na tabelę obrotową. Wybierz: Kreator Pojawi się okno ROW-COLUMN-DATA.

Kierownictwo chce znać wartość sprzedaży komputerów PC w każdym miesiącu, w rozbiciu na Sales Exec. Chcą również wiedzieć, który z programów Sales Exec osiągnął najwyższą łączną sprzedaż komputerów PC.

Czy można ustalić, które pola należy umieścić w obszarach ROW – COLUMN – DATA – PAGE w celu przekazania im tych informacji?

Odpowiedź jest podana na końcu tutorialu.

Jeśli nie potrafisz tego wymyślić, nie martw się. Po prostu przejdź do następnej sekcji.

16. ANALIZOWANIE MARŻYWY SPRZEDAŻY BRUTTO NA SPRZEDAŻY

Do tej pory na sprzedaż patrzyliśmy tylko w kategoriach wartości sprzedaży netto. Jeszcze ważniejsza jest jednak całkowita wygenerowana marża brutto, zwłaszcza jeśli Państwa firma oferuje rabaty.

Zapisy na fakturach sprzedaży w systemie SALANAL zawierają zarówno cenę sprzedaży (SP), jak i cenę kosztów (CP) każdej sprzedanej pozycji, a zatem łatwo jest obliczyć marżę.

Zrobimy to teraz. Jednak zamiast zmieniać poprzednią tabelę przestawną, jak to robiliśmy do tej pory, pozostawimy ją nietkniętą i zbudujemy drugą tabelę przestawną do analizy marż.

Zrób to w następujący sposób. Kliknij z powrotem na główny arkusz roboczy SALANAL. Następnie wybierz: Dane – Raport tabeli przestawnej

Zostanie wyświetlony Kreator tabeli przestawnej, krok 1 z 4 ekranów. Kliknij Next

.

Krok 2 z 4 Pudełko Range powinno mówić: $A$1:$P$4188. Kliknij na Next.

Pojawia się komunikat “Twoja nowa tablica przestawna zużyje mniej pamięci, jeśli ………etc.”. Wybierz NO

Krok 3 z 4: Pojawia się pole COLUMN-ROW-DATA. Upuść i upuść następujące pola: – Krok 3 z 4: Pojawia się pole COLUMN-ROW-DATA:

ROW : ACCNO, następnie CUSTNAME

COLUMN : pozostawić puste miejsce

DANE : NET, następnie pod nim KOSZT, następnie pod nim MRGN

Obszar DANYCH powinien teraz pokazywać sumę NET, sumę KOSZTU i sumę MRGN pod sobą

.

W obszarze ROW kliknij dwukrotnie na ACCTNO. Pojawi się pole tabeli obrotowej

.

Zmiana sumy częściowej z automatycznej na żadną. OK Zakończenie

Tabela przestawna jest przeliczana, przedstawiając dla każdego klienta wartość netto, koszt i marżę.

W razie potrzeby szersza kolumna B.

Liczby NET, COST i MRGN znajdują się pod sobą. Lepiej by wyglądały obok siebie. Żeby to zrobić:

Kliknij lewym przyciskiem myszy na szary przycisk pola DATA w komórce C1 i przytrzymaj klawisz myszy.
.
[uwaga: użytkownicy programu Excel 2000; zob. akapit kilka wierszy poniżej]

Utrzymując wciśnięty klawisz myszy LH, przeciągnij bardzo delikatnie w górę, aż pojawi się pionowa “cegła”.

Utrzymując wciśnięty klawisz myszy, powoli przeciągnij cegłę w prawo, aż zmieni się na poziomą. Następnie,

Zwolnij klawisz myszy. Tabela przestawna zostanie przeformatowana, z sumą NET, sumą COST i sumą MRGN obok siebie.

[jeżeli używasz Excel 2000.] Wciśnij myszkę LH na komórkę C3 (“Dane”) i przeciągnij ją w dół na kilka milimetrów, aż zobaczysz poziomą “cegiełkę”, a pod nią ikonę z niebieską płytką po lewej stronie].

[Excel 2000, kontynuacja. Trzymając oko na niebieskiej płycie, przeciągnij cegłę do pudełka Total w celi D3. Gdy płyta niebieska zmieni się na poziomą, zwolnij klawisz myszy.

Przeformatowana jest tabela przegubowa, z sumą NET, sumą COST i sumą MRGN obok siebie].

17. UŻYWANIE FORMULARZÓW DO OBLICZANIA PERCENTAŻU MARGINU (wyłącznie Excel 97, Excel 2000)

[Należy pamiętać, że tylko Excel 97 i Excel 2000 mają opcję “Formuły”. Jeśli masz Excel 5 lub 7, pominąć tę sekcję i przejść od razu do sekcji 18].

Chciałbyś zobaczyć procent marży brutto, który robisz dla każdego klienta.

Kliknij prawym przyciskiem myszy w obrębie tabeli przestawnej, a następnie wybierz z menu: Wzory – pole obliczone

Pojawia się pole “Wstaw obliczone pole”.

W polu “Nazwa” zastąpić pole 1 przez MGN% (wystarczy wpisać nad nim)

.

W polu “Formuła” kliknij na lewo od 0. Następnie usuń 0:

Na liście pól znajdź MRGN i zaznacz go. Następnie kliknąć na Insert Fields.

MRGN pojawia się w polu wzoru

Na prawo od MRGN należy dodać prawy ukośnik (“/”), oznaczający “podzielić przez”.

Wróć do listy pól. Znajdź NET i podświetl go. Wstaw pole

W polu wzoru powinien teraz znajdować się wzór =MRGN/NET

Kliknij na Dodaj, następnie OK

W tabeli przestawnej znajduje się czwarta kolumna, zatytułowana “Suma MGN%”

.

Nowa kolumna musi zostać przeformatowana procentowo. Dlatego należy kliknąć prawym przyciskiem myszy w dowolnym miejscu nowej kolumny

.

Wybierz: Pole – Liczba – Kategoria = Procent – Miejsca dziesiętne = 0 OK OK

Marginesy wahają się od 76% dla Eurotunnel plc, do 14% dla Hanson Industries

.

Ogólny procent marży wygląda bardzo zdrowo i wynosi 47%. Nie jest to jednak takie proste. Aby dowiedzieć się dlaczego, obliczmy ponownie marżę brutto, tym razem według grup produktów:

Kliknij prawym przyciskiem myszy w dowolnym miejscu tabeli obrotowej. Z menu wybierz: Kreator.

W obszarze ROW należy usunąć ACCNO i CUSTNAME, a następnie zastąpić je PRODGROUP. Wykończenie

Tabela przestawna jest przeliczana, pokazując procentowy udział marży w poszczególnych grupach produktów.

Procenty na INTERNET, USŁUGI i SZKOLENIA wynoszą 89%, 90% i 97%. Są one oczywiście zbyt wysokie…

18. WYSTĘPOWANIE NA BALANIECZCE DO ZOBOWIĄZANIA PODSTAWOWYCH TRANSAKCJI

Cenną cechą tabel przestawnych jest zdolność, jeśli widzisz saldo, które jest podejrzane, do “wierceń w dół” na tym saldzie i zobaczyć poszczególne transakcje, które składają się na niego.

Tak więc w tym przypadku należy sprawdzić 97% procent marży dla SZKOLENIA w komórce E15.

Podwójne kliknięcie na 97% w komórce E15

[Użytkownicy Excel 5 lub Excel 7. W chwili obecnej tabela obrotowa pokazuje sprzedaż, koszty i marżę w podziale na klientów. Musisz to zmienić, aby pokazać je w podziale na grupy produktów. Wejdź do tabeli obrotowej, usuń ACCNO i CUSTNAME z obszaru ROW i zastąp je PRODGROUP. Zakończyć w celu ponownego obliczenia. Następnie kliknij dwukrotnie na komórkę D15, łączny margines dla Treningu].

Generowany jest nowy arkusz, w którym wyszczególnione są wszystkie transakcje składające się na tę liczbę.

Kliknąć w dowolnym miejscu danych, aby usunąć podświetlenie.

Przewiń w prawo, aby zobaczyć wartości NET i COST dla każdego wiersza w kolumnach L i N.

W wielu przypadkach wartość KOSZTU wynosi zero, co skutkuje 100% marżą brutto i zawyżeniem rentowności.

Na koniec, kliknij prawym przyciskiem myszy z powrotem na arkusz marginesów, Arkusz2. Zmień nazwę tego arkusza Marginesy.

Uwaga: Jest to pierwsza wskazówka, że gdy dane są eksportowane z pakietu kont, mogą one często zawierać błędy w postaci błędnych wartości, błędnych kodów, pustych rekordów itp. Zanim cokolwiek się stanie, trzeba będzie najpierw posprzątać dane.

Szczególnym problemem są ceny kosztów. Działy administracji sprzedaży, które zajmują się obsługą zamówień i faktur sprzedaży, zazwyczaj nie przejmują się nimi.

Cena sprzedaży jest oczywiście ważna, ponieważ klient będzie się skarżył, jeśli się myli, ale komu zależy na dokładnych cenach kosztów? Ponadto, gdy usługi są świadczone w przeciwieństwie do produktów, często trudno jest ustalić, jaki jest prawdziwy koszt?

Podczas gdy personel administracyjny lub marketingowy może zostać przeszkolony w zakresie używania tabel przestawnych do raportowania dziennej lub miesięcznej sprzedaży według wartości, raportowanie marży brutto powinno być wykonywane tylko przez przeszkolonego księgowego, który jest w stanie docenić potencjalne pułapki.

KONIEC TUTORIA

Odpowiedź na rozdział 15 – Analiza sprzedaży przez Sales Exec:

Najlepszym sprzedawcą był Eddie Mars, który sprzedał 1.055.231 funtów PC. Żeby to wyświetlić, trzeba było:

Row = SALES EXEC. Kolumna = DATA Dane = NET Page = PRODGROUP. Następnie w komórce B1 wybrać PC

.

DAVID CARTER, 6 stycznia 2000.

David Carter, z siedzibą w Hemel Hempstead, Herts, jest niezależnym konsultantem, który instaluje systemy księgowe i przetwarzania zamówień w małych i średnich przedsiębiorstwach. Od 1996 roku szkoli ludzi w zakresie korzystania z tabel przestawnych programu Excel w takich obszarach jak budżetowanie, raportowanie centrów kosztowych, kalkulacja kosztów pracy, analiza sprzedaży, analiza ksiąg zamówień i kontrola produkcji. Chętnie udzieli wszelkich informacji zwrotnych na temat tego tutorialu i będzie można się z nim skontaktować:

Email: Telefon: 01442-216778