• Zapisz się na newsletter i odbierz DARMOWY EBOOK: 10 najprzydatniejszych porad excelowych

Klient, który był u nas tylko raz – jak takich wyłapać?

Czyli trochę o tabelach przestawnych, jako narzędziu roboczym

Przed Wami zadanie, które idealnie nadaje się do rozwiązania dla Power Query. Są dane wejściowe, które chcemy „obrobić” i jak najszybciej uzyskać pożądany wynik (hehe, a z którymi danymi tego nie chcemy zrobić?:)). W dzisiejszym wpisie jednak rozwiążę to zadanie wykorzystując tabele przestawne. Chcę bowiem pokazać Wam, że świetnie nadają się one nie tylko do raportowania, ale też jako narzędzie robocze. Wiele osób boi się tabel przestawnych, traktując je jak swojego rodzaju tabu i coś, co tylko zaawansowani użytkownicy znają i używają. Tymczasem są one prostym i bardzo przyjaznym narzędziem, mającym wiele ciekawych właściwości, które można wykorzystywać w wielu sytuacjach. A tak samo łatwo się je tworzy i usuwa. Nic nie popsujemy 🙂 No i są one dostępne w każdej wersji Excela.

Załóżmy, że analizujemy klientów przychodni lekarskiej. Chcemy wyłapać tych, którzy byli u nas tylko raz, przy czym tego jednego razu mogli być na kilku wizytach, ale zawsze jednego dnia. Czyli tak, jak na obrazku poniżej – klient oznaczony niebieską ramką był jednego dnia (2017-04-22) na 3 wizytach i oprócz tego nigdy więcej. O takich właśnie nam chodzi.

Formatka

Formatka

Logika problemu

Ponieważ jeden klient może być jednego dnia na kilku wizytach (czyli możemy mieć kilka rekordów z tym samym PESEL-em i datą) nie wystarczy po prostu złączyć PESEL-u z datą i znaleźć pierwsze wystąpienie tego złączenia (robiłam coś takiego przy okazji np. liczenia mediany w tabeli przestawnej). Bardziej przydałoby nam się wiedzieć w jakich dniach klient u nas był. I jeśli był tylko jednego dnia – tego własnie szukamy. Na dobrą sprawę wystarczyłoby zrobić z naszych danych tabelkę przestawną i sprawa załatwiona. Tabelka bowiem pogrupuje nam zarówno PESEL-e jak i daty w unikalne wpisy. Potem wystarczy policzyć ile PESEL-i jest na takiej liście i wyniki przefiltrować w poszukiwaniu jedynek.

Tak właśnie zrobimy. Zacznijmy więc od stworzenia tabelki przestawnej…

Tworzenie tabeli przestawnej

Polecam ją stworzyć na narzędziu tabela, czyli po ustawieniu się w danych kliknąć Ctrl + t. Potem, stojąc w dowolnym miejscu zakresu danych, wystarczy z menu Wstawianie wybrać przycisk Tabela przestawna i wybrać miejsce docelowe. Ja chcę, aby moja tabela była w tym samym arkuszu co dane, więc wybieram Istniejący arkusz i zakres Dane!F3, jak na rysunku:

Wstawianie tabeli przestawnej

Wstawianie tabeli przestawnej

Powstanie nam coś takiego:

Pusta tabela przestawna

Pusta tabela przestawna

Teraz trzeba wrzucić do tabeli odpowiednie dane, czyli na pole wierszy (przy umieszczaniu danych skorzystaj z okienka Lista pól):

  1. PESEL
  2. Data

Wrzucamy metodą drag-and-drop 😉

Jeśli korzystasz z Excela 2016, Excel automatycznie zgrupuje daty w miesiące (starsze wersje tego nie zrobią same z siebie). Nie jest nam to potrzebne, więc te miesiące można od razu wyrzucić. Dostaniemy coś takiego:

Tabel przestawna przed tuningiem

Tabel przestawna przed tuningiem

Generalnie: mega-masakra! Koniecznie teraz trzeba zrobić tuning, czyli 3 rzeczy:

  1. Zmiana układu na np. tabelaryczny
  2. Powtarzanie etykiet.
  3. Usunięcie sum częściowych

Odnośnie zmiany układu na tabelaryczny, to tak:

Ustaw się na tabeli i z manu Narzędzia tabel przestawnych/ Projektowanie i wybierz w nim 2 opcje (po kolei):

  • Pokaż w formie tabelarycznej i
  • Powtórz wszystkie etykiety elementów

Czyli tak:

Opcje tuningu tabeli przestawnej

Opcje tuningu tabeli przestawnej

W tym samym menu znajdziesz też przycisk Sumy częściowe, z którego wybierz opcję Nie pokazuj sum częściowych. W efekcie otrzymamy to:

Tabela przestawna po tuningu

Tabela przestawna po tuningu

Tak na prawdę na tym można byłoby zakończyć, ponieważ mamy tutaj wszystkie potrzebne informacje (już widać klientów, o których chodziło: np. w wierszach 17-19), jednak są strasznie toporne w dalszej analizie. Dlatego wkleimy je sobie teraz specjalnie jako wartości:

  1. zaznacz całą tabelę przestawną, najlepiej zaczynając zaznaczenie z jednym pustym wierszem powyżej, czyli u mnie to będzie zakres F2:G25,
  2. skopiuj ten zakres,
  3. i wklej specjalnie jako wartości: kliknij prawy przyciskiem myszy komórkę F2 i z menu kontekstowego wybierz ikonkę: 

Od tej pory już tabel przestawna zniknie, a zostanie tylko jej wartość. Uwielbiam takie korzystanie z tabel przestawnych. Potrzebne na chwilę, ponieważ cudnie umieją grupować dane, a potem chcemy tylko ich wartości.

Oczywiście, można tabelkę przestawną zostawić. Byłoby to szczególnie dobre rozwiązanie, gdybyśmy mieli robić takie „ćwiczenie” cyklicznie. Trzeba byłoby pamiętać tylko o kopiowaniu formuły, którą za chwilę napiszemy…

Ok. Teraz mamy już przyporządkowane unikalne dni do klientów. Czyli jeśli jakiś klient był u nas tylko jednego dnia, niezależnie od tego, na ilu wizytach – w naszej tabeli jego pesel wystąpi tylko raz. Wystarczy więc teraz tylko policzyć PESEL-e w naszej tabeli i wybrać tych, którzy mają jedno wystąpienie. Idealnie nada się do tego funkcja LICZ.JEŻELI. Wpisz ją obok tabelki w komórkę H4 (ja tę kolumnę roboczą nazwałam Rob):

=LICZ.JEŻELI($F$4:$F$24;F4)

Po skopiowaniu formuły w dół np. tą metodą, otrzymujemy to, co chcieliśmy – policzone odwiedziny klientów. Wystarczy teraz to ładnie sformatować, założyć filtr (Ctrl + Shift + L) i wyfiltrować 1 w kolumnie Rob:

Wynik

Wynik

Tadam! I tyle.

Jeśli chcesz obejrzeć krok po kroku jak robię ten przykład, polecam Ci obejrzeć film:

 

 

Spodobał Ci się ten artykuł?

Jeśli TAK, zapisz się na newsletter, aby otrzymywać powiadomienia
o najnowszych wpisach (i tylko powiadomienia: zero spamu).
Dodatkowo otrzymasz DARMOWY EBOOK pt.:

10 najprzydatniejszych porad excelowych


Tagi , , , , , , , , , , .Dodaj do zakładek Link.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *