Obliczenie sprzedaży dla każdego produktu jest jednym z ważnych elementów analizy marketingowej. W tej części pokaże Wam jak za pomocą tabeli przestawnej wykonać właśnie taką analizę.

Jeśli chcesz pracować u siebie na identycznym zestawie danych, ściągnij przygotowany przeze mnie zestaw z naszego GitHub-a -> tutaj.

Nasz treningowy zestaw danych wygląda jak poniżej.

Jak tworzyć tabelę przestawną opisywałem w poprzednich artykułach, więc jeśli będziesz musiał sięgnąć do bardziej szczegółowego opisu przejdź do naszego wcześniejszego artykułu, który znajdziesz -> tutaj.

A teraz w skrócie przypomnę jak tworzymy tabele przestawną:

• Stań w obrębie danych które chcesz analizować za pomocą tabeli przestawnej (nasz zakres danych zaczyna się od komórki A1 (pierwszy wiersz to nagłówki) aż do D5001, stań więc np. w komórce A2).
• Przejdź na zakładkę Wstawianie.
• Kliknij w ikonę Tabela przestawna (po lewej).
• [Okno Tworzenie tabeli przestawne] Sprawdź czy w polu Tabela/zakres znajduje się prawidłowy zakres danych. W naszym przypadku powinno być : ’dane początkowe’!$A$1:$D$5001
• [Okno Tworzenie tabeli przestawne] Zostaw zaznaczoną opcję Nowy arkusz i wszystko potwierdź przyciskiem OK.

Dobrze. Już utworzyliśmy tabelę przestawną. Teraz musimy „wrzucić” na nią dane.

Skoro w wierszach mamy mieć produkty i wielkość sprzedaży poszczególnego produktu to musimy „przeciągnąć” pole Nazwa produktu z góry i upuścić nad polem Wiersze.

A teraz przeciągnij pole Cena i upuść nad polem Wartość.

Pamiętaj, że chcemy mieć sumę sprzedaży więc sprawdź czy w polu Wartość jest opcja Suma z Cena a nie np. Liczba z Cena.

Kliknij teraz w nagłówek kolumny Suma z Cena.

Pojawi się nowe okno o nazwie Ustawienia pola wartości. W polu Nazwa niestandardowe możesz zmienić nazwę nagłówka kolumny, zmień ją na Wielkość sprzedaży. W tym miejscu możemy także ustawić format liczby. Skoro nasza wielkość sprzedaży to wynik w PLN to ustawmy format liczby na Walutowy. W tym celu kliknij na przycisk Format liczby.

W części Kategoria ustaw na Walutowe w miejscu Miejsca dziesiętne możesz ustawić z jaką precyzją chcesz prezentować wyniki w PLN. Następnie wszystko potwierdzasz przyciskiem OK. Powrócisz do okna Ustawienia pola wartość i tutaj także wszystko potwierdź przyciskiem OK.

Po naszych zabiegach zmieniła się nazwa nagłówka i sposób prezentowania wielkości sprzedaży.

Teraz oczywiście możemy całą listę sortować i filtrować wg naszych potrzeb.

To tyle na teraz. Zapraszam Was do kolejnych artykułów z tej serii.

<< wróć do kategorii: EXCEL

Dzisiaj pokażę Ci jak obliczyć procentową wielkość sprzedaży za pomocą tabeli przestawnej. Jeśli chcesz pracować u siebie na identycznym zestawie danych, ściągnij przygotowany przeze mnie zestaw z naszego GitHub-a -> tutaj.

No to zacznijmy.

Nasz zestaw danych wygląda tak.

Składa się z czterech kolumn: Nazwa produktu, Data sprzedaży, Sklep, Cena i zawiera 5000 wierszy.

Naszym zadaniem dzisiaj będzie obliczenie procentowej wielkości sprzedaży w każdym ze sklepów.

Zacznijmy od stworzenia tabeli przestawnej. W tym celu przejdź na zakładkę „Wstawianie” i kliknij w ikonę „Tabela przestawna”.

Pojawi się nowe okno „Tworzenie tabeli przestawnej” w którym będziemy mogli określić np. zakres danych, miejsce umiejscowienia tabeli itp.

Jeśli „stałeś” w środku naszej tabeli (danych początkowych) to Excel z automatu zaznaczył obszar gdzie są dane. Możesz tylko sprawdzić czy wszytko się zgadza. Nasz zakres to komórki od A1 do D5001. Zostaw także zaznaczoną opcję „Nowy arkusz” dla umiejscowienia naszej tabeli przestawnej i potwierdź wszystko przyciskiem OK.

Excel automatycznie stworzy nowy arkusz i stworzy w nim tabelę przestawną. Teraz przeciągnij tylko poszczególne nagłówki kolumn do odpowiednich pól. Sklep do Wierszy a Cena do Wartości.

Wynik.

Zwróć uwagę że w polu Wartości pojawiła nam się pozycja „Liczba z Cena”. W celu poprawnego rozwiązania naszego zadania potrzebujemy sumę a nie licznika z pola Cena. Kliknij więc w czarny trójkącik obok pozycji „Liczba z Cena” a następnie wybierz pozycję „Ustawienia pola wartość … „.

W oknie „Ustawienia pola wartość” zmień „Licznik” na „Suma” i potwierdź wszystko przyciskiem OK.

Wynikiem naszego działania jest zmiana w tabeli przestawnej licznika na sumę i zmiana wartości w tabeli.

W tym momencie nasze dane początkowe są pogrupowane wg konkretnego sklepu a wartość zakupów w poszczególnym sklepie jest zsumowana i zaprezentowana w kolumnie „Suma z Cena”. Na samym dole jest jeszcze dodatkowy wiersz „Suma końcowa” która jest sumą wszystkich powyższych wartości z wszystkich sklepów.

Ala dalej nie mamy wyników sprzedaży w procentach a to było naszym zadaniem.

Wróć do trójkącika przy pozycji „Suma z Cena” w polu „Wartości” i wybierz ponownie opcję „Ustawienia pola wartości”. Przejdź na zakładkę „Pokazywanie wartości jako”, poniżej wybierz opcję „% sumy końcowej” i potwierdź przyciskiem OK.

W wyniku naszych działań otrzymujemy to o co nam chodziło, czyli procentowa wartość sprzedaży.

Gotowe 🙂

<< wróć do kategorii: EXCEL

Potrafimy już sortować dwiema metodami: prostą i zaawansowaną. Dzisiaj przyszedł czas na naukę filtrowania, czyli oddzielania interesujących nas informacji od tych których aktualnie nie potrzebujemy wg kryteriów które sami możemy ustawić/skonfigurować.

I od razu przykład dla lepszego zrozumienia. W artykule o sortowaniu zaawansowanym korzystaliśmy z listy operacji bankowych klientów banku. I dzisiaj też z tego zestawienia skorzystamy.

Zadanie

Pokaż mi operacje bankowe typu „Wypłata z bankomatu”, których dokonał Pan Piotr Dziesiąty.

No to zaczynamy!

Nasze źródłowe dane wyglądają jak poniżej.

Jak widać lista jest pełna i widzimy wszystkich klientów i wszystkie typy operacji.

No to zacznijmy ustawiać filtry.

Przejdź na zakładkę „Dane” i kliknij w ikonę „Filtruj” (pamiętaj że aktywna komórka (ta aktualnie zaznaczona) musi być wewnątrz naszej tabeli, bo inaczej otrzymasz błąd).

Zwróć uwagę, że po kliknięciu w „Filtruj” przy nazwach nagłówków, w każdej kolumnie, pojawiły się szare kwadraciki ze czarnym trójkątem.

W zadaniu mamy dwa zadania. Pierwsze ograniczyć listę do operacji Pana Piotra Dziesiątego. Drugie ograniczyć listę operacji Pana Piotra do operacji typu „Wypłata z bankomatu”.

Zajmijmy się pierwszym ograniczeniem.

Skoro mamy ograniczyć naszą listę do konkretnego klienta to musimy kliknąć w ten szary kwadracik przy nagłówku kolumny „Klient”. Po kliknięciu rozwiną się dodatkowe opcje z listą wszystkich unikalnych wartości w dole listy.

Ponieważ chcemy ograniczyć naszą listę tylko do jednego klienta to musimy wszystkich innych klientów odznaczyć i zaznaczyć tylko tego naszego poszukiwanego. W pierwszym kroku musisz odznaczyć checkbox obok opcji „zaznacz wszytko”, wtedy wszyscy klienci z listy zostaną odznaczeni.

A następnie skorzystaj z pola „Wyszukaj” (powyżej opcji „zaznacz wszytko”) w wpisz tam imię Piotr. Lista automatycznie zostanie przefiltrowana i z automatu pojawi nam się klient Piotr Dziesiąty. Teraz musimy zaznaczyć checkbox obok tego klienta. Jeśli nie było innych klientów o imieniu Piotr pozycja ta powinna być automatycznie zaznaczona. Oczywiście wszytko potwierdzasz przyciskiem OK.

Po potwierdzeniu powinieneś widzieć tylko rekordy gdzie klientem jest Pan Piotr Dziesiąty. Ale zwróć uwagę jeszcze na dwie rzeczy. Po pierwsze trójkąt na kwadracie obok nagłówka „Klient” zmienił się na taki lejek. Oznacza to, że nasza tabela jest filtrowana w oparciu o kolumnę Klient wg zadanych przez nas kryteriów. Pamiętaj, że niekiedy dostaniesz już przefiltrowaną tabelkę i właśnie po tych ikonach (albo trójkąt albo lejek) jesteś w stanie zorientować się czy dana tabela jest już przefiltrowana czy są to źródłowe dane.

Drugą rzeczą na którą chciałem Ci zwrócić uwagę jest zielona belka na dole. Gdzie otrzymujemy informację o ilości przefiltrowanych rekordów. Mamy tam informację, że wszystkich rekordów jest 1000 a po filtrowaniu jest ich 64. Nie musisz wiec liczyć ile rekordów otrzymałeś po filtrowaniu, Excel powie Ci to od razu.

No dobrze ale zostało jeszcze drugie kryterium, czyli ograniczenie listy tylko do operacji „Wypłata z bankomatu”.

No to postępujemy w podobny sposób z kolumną „Jaka operacja bankowa”.

1. Klikasz w kwadracik obok nazwy kolumny.
2. Odznaczasz opcję „zaznacz wszystko”.
3. Tutaj lista jest krótka, więc od razu zaznaczasz opcję „Wyłata z bankomatu”.
4. Potwierdzasz przyciskiem OK.

W wyniku naszych działań otrzymaliśmy przefiltrowaną listę wg naszych dwóch kryteriów.

Zwróć uwagę że teraz lejek jest przy obu kolumnach: „Klient” i „Jaka operacja bankowa”. Ale zobacz także ze na dolnej belce pojawiła się informacja że znaleziono 13 rekordów z 1000. Czyli widzisz, że Excel na bieżąco informuje Cię o liczbie rekordów która jest „odfiltrowana” w danym momencie.

Oczywiście moglibyśmy dodać kolejne filtry np. ograniczyć naszą listę do konkretnego oddziału banku lub konkretnego przedziału kwotowego, ale to zostawiam Wam do samodzielnego przećwiczenia.

<< wróć do kategorii: EXCEL

Dzisiaj zaprezentuję Wam jak w szybki sposób można z tabelki zrobić wykres.

W arkuszu 1 przygotowałem nasze dane źródłowe. Jest to wykaz miesięcznych wydatków przykładowej rodziny.

Teraz chcielibyśmy zwizualizować te dane w formie wykresu.

Jest taki skrót klawiszowy za pomocą którego możemy to zrobić w zasadzie jednym kliknięciem. Ten skrót to F11.

Ustaw się wewnątrz naszej tabelki i wciśnij przycisk F11.

Jak widać na powyższym obrazku, Excel zwizualizował nasze dane na wykresie, utworzył automatycznie nowy arkusz „Wykres1” i umieścił nasz wykres właśnie w tym nowym arkuszu.

Sam widzisz, że jednym kliknięciem w prosty sposób możemy tworzyć wykresy z danych tabelarycznych.

To tyle w tym króciutkim wpisie.

Serdecznie zapraszam na kolejne.

<< wróć do kategorii: EXCEL

Poprzednim razem ćwiczyliśmy proste sortowanie. „Proste”, czyli tylko wg jednej kolumny. A co jeśli chcemy sortować po wielu kolumnach naraz? Tutaj z pomocą przychodzi nam sortowanie zaawansowane.

Nasze dane przesortujmy po: imieniu i nazwisku klienta, oddziale banku, operacji bankowej i kwocie. Data poszczególnej operacji będzie na nas obojętna.

Żeby lepiej zobrazować wynik sortowania przeorganizujemy (poprzesuwamy) sobie trochę kolumny. Ustaw je w takiej kolejności w jakiej są na poniższym obrazku (czyli zgodnie z uzgodnioną kolejnością sortowania).

Jeśli nie wiecie jak przesuwać kolumny względem siebie, poniżej procedura dzięki której szybko je poprzestawiacie:

Jak przesuwać kolumny względem siebie:

• Zaznacz kolumnę, którą chcesz przesunąć.
• Najedź kursorem na krawędź zaznaczonej kolumny (prawą lub lewą), aż kursor zmieni kształt na krzyżyk zakończony strzałkami.
• Wciśnij klawisz Shift i prawy przycisk myszy (PPM) i trzymając go przesuwaj mysz w stronę gdzie chcesz przesunąć daną kolumnę.
• Zauważ, że przesuwając kursor pomiędzy poszczególnymi kolumnami pojawia się taka grubsza zielona linia. To jej położenie mówi, w które miejsce aktualnie możesz przenieść daną kolumnę.
• Przesuń kursor w docelowe miejsce i zwolnij oba przyciski (Shift i PPM).

Ok. to przesuwanie mamy już ogarnięte.

To teraz przesortujmy naszą tabelę:

• Przejdź na wstążkę”Dane”.
• W części „Sortowanie i filtrowanie” wciśnij ikonę „ZAAZ sortuj”

• Pojawi się nowe okno „Sortowanie”

• Ustaw kryteria sortowania jak na poniższym obrazku. Nowy poziom dodajesz poprzez wciśnięcie przycisku „Dodaj poziom”.

• Wszytko potwierdzasz przyciskiem „ok”.

Efekt końcowy naszego filtrowania jest na obrazku poniżej (pomniejszyłem trochę zrzut żeby było widać kolejną osobę na liście).

W rezultacie otrzymujemy przesortowaną tabelę wg kryteriów jakie sobie założyliśmy na początku.

<< wróć do kategorii: EXCEL

Jedną z podstawowych technik obróbki danych jest sortowanie, czyli uporządkowanie danych wg zdefiniowanych przez nas kryteriów. W przypadku Excela, bardzo często pracujemy na danych w formie tabeli. W niniejszym wpisie pokażę Wam jak sortować właśnie taki zestaw danych.

Proste sortowanie.

Zacznijmy od prostego sortowania wg pojedynczej kolumny. Poniżej zestawienie operacji bankowych klientów jednego z banków.

Pierwszą rzeczą która od razu rzuca się w oczy to niechronologiczne daty i tym się zajmijmy. Posortujmy naszą listę wg dat.

W celu posortowania wykonaj kilka kroków:

• Kliknij w jakąś komórkę w kolumnie którą chcesz posortować. W naszym przypadku będzie to kolumna A. Kliknij więc w komórkę A2.
• Przejdź na zakładkę „Dane” i w części „Sortowanie i filtrowanie” kliknij w ikonę AZ ze strzałką w dół (patrz obrazek poniżej).

Po tej operacji nasz lista powinna być posortowana wg kolumny „Data” rosnąco i tak właśnie jest.

Jeśli chcielibyśmy posortować naszą listę malejąco to musimy skorzystać z ikony ZA ze strzałką w dół.

Sortowanie możemy zastosować do której kolumny chcemy natomiast musisz pamiętać, że przy sortowaniu prostym w danym momencie sortujemy całą tabelę tylko wg jednego kryterium, czyli tylko wg wskazanej przez nas kolumny.

Jeśli chciałbyś sortować naszą tabelę wg kilku kryteriów naraz jest to już sortowanie zaawansowane ale opowiem o tym w kolejnym wpisie.

<< wróć do kategorii: EXCEL

Poznaliśmy ostatnio ogólne skróty dot. Excel-a. Dzisiaj poznacie skróty które „otworzą” Wam dostęp do poszczególnych zakładek.

Bardzo spodobało mi się, że Excel ma wbudowaną taką funkcję, uruchamianą skrótem, która podpowiada (pokazuje) jakimi skrótami możemy się „dostać” do poszczególnych zakładek.

Poniżej widok bez skorzystania z naszego tajnego skrótu.

A teraz użyjmy naszego tajnego skrótu Alt + Y , który pokaże nam podpowiedzi dot. skrótów do każdej zakładki.

Przy każdej nazwie zakładki pojawił się czarny kwadracik z białą literą. Ta litera to druga składowa skrótu który „włączy” podpowiedzi na konkretnej zakładki, czyli przeskoczymy na konkretną zakładkę i pojawią się podpowiedzi (czarne kwadraciki z literami) dot. dostępu do poszczególnych funkcji na konkretnej zakładce. Pierwszą składową naszego skrótu to będzie klawisz ALT. Żeby wyłączyć podpowiedź użyj przycisku Escape (Esc).

Super sprawą jest to że znając praktycznie tylko jeden skrót Alt + Y jesteśmy w stanie uzyskać informację jak przejść na konkretną zakładkę a później wykorzystując skrót z podpowiedzi otrzymamy informację dot. skrótów do poszczególnych funkcji na konkretnych zakładkach.

Przyszedł czas na testy.

Po wciśnięciu skrótu Alt + Y, przy zakładce „widok” widać literę O, czyli skrótem Alt + O powinniśmy przeskoczyć na zakładkę „widok” i włączyć podpowiedź na tej zakładce. No to uruchom skrót Alt + O.

No i zgodnie z obrazkiem powyżej przeskoczyliśmy na zakładkę „widok” i pojawiły nam się znane nam kwadraciki z literami, które odpowiadają wywołaniu poszczególnych funkcji ze wskazanej przez nas zakładki. Oczywiście litery są drugim członem skrótu a pierwszym jest przycisk ALT.

Przetestujmy więc jakąś opcję. Na przykład w części „pokazywanie” jest opcja „pasek formuły” przy której jest włączony checkbox, czyli pasek formuły powinien się wyświetlać i się wyświetla co obrazuje powyższy obrazek.

Spróbujmy odznaczyć tą opcję skrótem, którym wg powyższego obrazka powinien być Alt + S. No to wciskamy.

Jak widać na powyższym obrazku po wciśnięciu naszego skrótu pasek formuły zniknął i checkbox przy tej opcji jest odznaczony, czyli wszystko zadziałało zgodnie z przewidywaniami.

A teraz wróćmy do poprzedniego stanu. Wciśnij Alt + S.

Jak widzisz pasek formuły jest z powrotem włączony i chceckbox przy tej opcji jest zaznaczony.

Widzisz więc w jak prosty i szybki sposób możesz się „dobrać” do różnych opcji w programie Excel.

<< wróć do kategorii: EXCEL

Co czego w ogóle służą skróty klawiszowe ?

Skróty klawiszowe umożliwiają szybsze wykonanie danej opcji lub funkcji Excel. Dzięki nim możemy pracować szybciej i bardziej efektywniej. W niektórych sytuacjach nie musimy „wyklikiwać” w pewnej sekwencji wielu linków lub ikon w celu wykonania pewnej opcji/funkcji. „Jednym kliknięciem” (bo skróty najczęściej składają się z kombinacji dwóch lub trzech klawiszy) możemy natomiast wykonać tą akcję, którą chcieliśmy, korzystając z szybkiego skrótu klawiszowego.

Przyznam się szczerze, że kiedyś sam nie doceniałem skrótów klawiszowych w Excel-u. Ale przyszedł czas kiedy się do nich przekonałem i teraz nie wyobrażam sobie pracy z programem Excel bez tych skrótów.

Oczywiście „nie od razu Rzym zbudowano” i sprawne posługiwanie się skrótami klawiszowymi wymaga czasu i praktyki, ale zapewniam Cię że warto poświęcić temu zagadnieniu czas. Na pewno na tym skorzystacie.

Dzisiaj kilka ogólnych skrótów dotyczących Excel-a.

W kolejnych wpisach zajmiemy się kolejnymi skrótami.

<< wróć do kategorii: EXCEL

Dzisiaj zajmiemy się sytuacją kiedy skopiowany do Excela tekst wkleił nam się do pierwszej kolumny ale posiada jakiś unikalny separator dzięki któremu moglibyśmy rozdzielić ten tekst na osobne kolumny.

Zadanie

Skopiowana z Internetu lista książek do przeczytania wkleiła nam się do pierwszej kolumny.

Rozdziel tekst na osobne kolumny w taki sposób aby powstały trzy kolumny: numer, tytuł, autor.

Rozwiązanie

Do rozwiązania naszego zadania musimy przeanalizować czy jest możliwość rozłączyć tekst w każdym wierszy na wymagane kolumny. Musimy się zastanowić czy istnieje jakaś prawidłowość, która łączy wszystkie wiersze i określić czy jest jakiś uniwersalny separator którego możemy użyć do rozłączenia tekstu. Ogólnie mówiąc czy struktura tekstu w każdym wierszu jest identyczna bo tylko wtedy możemy zastosować jakiś automatyzm.

Już na pierwszy rzut oka widzimy że w każdym wierszu po numerze jest kropka. I właśnie to wykorzystamy w pierwszym kroku.

Żeby nie modyfikować danych źródłowych powielę nasz arkusz „dane źródłowe” i nowy arkusz nazwę „krok 1 numer”.

No to teraz przechodzimy do działania.

• zaznacz kolumnę A klikają na jej nagłówek
  

• teraz przejdź do wstążki „Dane” i kliknij w ikonę „Tekst jako kolumny”.
  

• w oknie o nazwie „Kreator konwersji tekstu na kolumny” w pierwszym kroku zostawiamy zaznaczoną opcję „rozdzielany” i potwierdzamy wszystko przyciskiem „Dalej”
  

• W kolejnym kroku określamy ogranicznik. Jak już wcześniej zauważyliśmy po każdym numerze występuje znak „.” i właśnie ten znak musimy wskazać jako nasz ogranicznik. W tym celu zaznaczamy opcję „inny” i w polu obok musimy wstawić znak „.”
  Jeśli to zrobimy zwróć uwagę, że w części „podgląd danych” już widzimy różnicę i rzeczywiści Excel rozdzielił już nasze danej na 2 kolumny. W pierwszej jest numer a w drugiej reszta tekstu.
  Wszystko potwierdź przyciskiem „Dalej”
  

• W kolejnym kroku potwierdź wszystko przyciskiem „zakończ”.
  

OK, właśnie oddzieliliśmy numer od całości tekstu. W tym momencie nasz arkusz powinien wyglądać tak jak na poniższym obrazku. Czyli numer została w kolumnie A a reszta tekstu została przeniesiona do kolumny B.

Teraz musimy zrobić to samo z pozostałym tekstem. Zauważ że pomiędzy tytułem a autorem jest średnik.

No to działamy dalej. Procedura jest dokładnie taka sama jak przy numerze.

• Kliknij w komórkę B1 i skopiuj ten znak średnika.
• Zaznacz kolumnę B klikając na jej nagłówek.
• Przejdź do zakładki „Dane” i wybierz „Tekst jako kolumny”.
• W kreatorze w pierwszym kroku został opcję „rozdzielany” i kliknij w „Dalej”.
• W drugim kroku kreatora zaznacz pozycję „inny” i w puste pole wklej skopiowany wcześniej znak średnika. Potwierdź klikając w „Dalej”
  

• W trzecim kroku kreatora potwierdź wszytko klikając w „Zakończ”.

Po wszystkim nasz arkusz powinien wyglądać jak na poniższym zrzucie.

Widzisz więc, że zadanie można uznać za rozwiązane.

<< wróć do kategorii: EXCEL