Arkusz kalkulacyjny Excel

Organizowanie i podsumowywanie danych

Tworzenie tabel przestawnych

  1. W tym ćwiczeniu użyjemy tabeli przestawnej do szybkiego utworzenia trzech raportów podsumowujących sprzedaż towarów firmy Imbryk. Najpierw musimy otworzyć plik tekstowy Zam1996.txt zawierający szczegółowe dane o sprzedaży. Zapisz go na swoim dysku, a następnie uruchom program Excel, wybierz polecenie Otwórz z menu Plik i wskaż "Pliki tekstowe" jako typ otwieranego pliku:



    Pojawi się kreator importu plików tekstowych oferujący podgląd części naszego pliku:



    dane w tym pliku rozdzielne są znakami tabulatora:



    i mają format "ogólny":



    Kliknij przycisk Zakończ. Aby zmienić szerokość kolumn tak, aby dane zawarte w odpowiadających im komórkach pokazywane były w całości zaznacz wszystkie kolumny zawierające dane (przeciągając myszą po przyciskach od A do H), a następnie wybierz polecenie Autodopasowanie obszaru:



  2. Zaznacz jakąś komórkę listy sprzedaży, a następnie wybierz polecenie Raport tabeli przestawnej i wykresu przestawnego:



    sprawdź, czy zaznaczone są następujące opcje i kliknij w przycisk Dalej:



    Excel wyświetli zakres danych, które będą użyte do utworzenia tabeli przestawnej:



    naszą tabelę przestawną umieścimy w nowym arkuszu:



  3. Przeciągnij pole "Pracownik" z okna dialogowego "Tabela przestawna" do obszaru oznaczonego "Upuść pola kolumn tutaj":



    pole "Data zamówienia" do obszaru "Upuść pola wierszy tutaj":



    i wreszcie pole "Wartość" do obszaru "Upuść elementy danych tutaj":



    Zamknij okno "Tabela przestawna". Powstała tabelka ilustrująca sumę wartości towarów sprzedanych przez danego pracownika w danym dniu:



    Jest w niej zbyt dużo pustych pól (pracownicy często mieli złe dni, kiedy nie byli w stanie sprzedać niczego). Aby je usunąć pogrupujemy teraz dane według miesięcy. Kliknij prawym przyciskiem myszy dowolną komórkę w kolumnie zawierającej daty i wybierz polecenie Grupuj:



    według miesięcy:



    Powstała tabela jest już bardziej zwarta i dzięki temu wygląda znacznie lepiej:



  4. Zmodyfikujemy teraz utworzoną w poprzednich punktach tabelę przestawną tak, aby zawierała kwartalne dane sprzedaży poszczególnych kategorii towarów w odniesieniu do każdego pracownika. W tym celu kliknij prawym przyciskiem myszy w dowolną komórkę tabeli i wybierz polecenie Kreator:



    a następnie kliknij w przycisk Układ:



    i przeciągnij przycisk pola "Kategoria" do pola WIERSZ:



    Po kliknięciu w przyciski OK i Zakończ Twój ekran powinien wyglądać następująco:



    Ponownie wybierz polecenie grupowania według dat i wybierz opcję "Kwartały":



    Efekt wykonanego grupowania według kwartałów powinien wyglądać tak:



    W kolejnym kroku zmodyfikujemy raport tak, aby dane dotyczące poszczególnych pracowników wyświetlane były na osobnych stronach. W tym celu uruchom ponownie kreatora tabeli przestawnej i zmodyfikuj jej układ przeciągając przycisk pola "Pracownik" z pola KOLUMNA w pole STRONA:



    Możemy teraz w prosty sposób oglądać sumaryczne dane dotyczące wszystkich pracowników:



    albo tylko jednego z nich:



    W ostatnim kroku zmienimy jeszcze układ tabeli z pionowego na poziomy przeciągając przycisk "Data zamówienia" tak, aby znalazł się na prawo od przycisku "Suma: Wartość":



  5. Trzeci przygotowany przez nas raport zawierać będzie dane o średniej kwartalnej sprzedaży poszczególnych towarów. Aby go utworzyć usuniemy z tabeli przestawnej pola "Pracownik" i "Kategoria", dodamy pole "Towar" i zmienimy funkcję podsumowującą na średnią. W tym celu przeciągnij przycisk reprezentujący pole "Pracownik" poza obszar tabeli (tzn. do momentu, gdy wskaźnik myszy zmieni się w czerwony krzyżyk) i
    zwolnij przycisk myszy. Twoja tabela zmieni się w sposób następujący:



    Postępując analogicznie usuń pole "Kategoria":



    Aby dodać nowe pole kliknij prawym przyciskiem myszy w dowolną komórkę tabeli, wybierz polecenie Kreator, kliknij w przycisk Układ i przeciągnij przycisk pola "Towar" do pola WIERSZ:



    Teraz kliknij dwukrotnie w przycisk "Suma: Wartość" i zmień przypisaną do niego funkcję na średnią:



    Po kliknięciu w przyciski OK i Zakończ Twoja tabela powinna wyglądać następująco:



  6. Jeden z pracowników firmy Imbryk omyłkowo podał błędne dane dotyczące sprzedanych przez siebie towarów. Kliknij kartę arkusza "Zam1996" zawierającego dane szczegółowe i w wierszu 773 znajdź dane dotyczące kawy Antiqua z dnia 15/12/96. Zmień wartość komórki G773 (ilość) na 200, zaś wartość komórki H773 (wartość) na 6300:



    Przejdź ponownie na arkusz "Arkusz1", kliknij prawym przyciskiem myszy w dowolną komórkę tabeli i z menu podręcznego wybierz polecenie Odśwież dane:



    Średnia wartość sprzedaży kawy Antiqua wzrosłą i wynosi teraz około 1647 zł:



  7. Wyświetlimy teraz szczegóły dotyczące sprzedaży pewnego towaru. Kliknij dwukrotnie w komórkę zawierającą wartość średniej sprzedaży kawy (na przykład) Jamaican Blue Mountain:



    Powinien zostać utworzony nowy arkusz zawierający wszystkie informacje dotyczące tego gatunku kawy: