Pobierz przykładowe pliki potrzebne do wykonania tego ćwiczenia.
Swego czasu opiekowałem się pewnym sklepem internetowym. Właściciel sklepu postanowił zrobić promocję świąteczną 2+1 tj. kupując dwie sztuki pewnego produktu trzeci otrzymasz gratis. Wszystkie osoby, które się zapisały na newsletter otrzymały informacje o świątecznej promocji. Liczba zamówień wzrosła co mnie cieszyło, gdyż „byłem na prowizji”. Niestety, ale otrzymaliśmy kilka niezbyt kulturalnych wiadomości od osób, które np. dwa dni wcześniej złożyły zamówienie na dwie sztuki danego produktu i nie załapały się na promocję. Aby udobruchać takich klientów wysłaliśmy im w ramach przeprosin gratisowy produkt.
Rok później, gdy organizowaliśmy podobną promocję postanowiliśmy wysłać mailing do wszystkich czytelników newslettera, ale z bazy mailingowej usunęliśmy adresy e-mail osób, które w ciągu ostatnich 30 dni składały u nas zamówienie (tak naprawdę nie usuwaliśmy tych osób z bazy newslettera na stałe a jedynie utworzyliśmy nowe konto w programie do wysyłki mailingów).
Jeśli Twoje oprogramowanie do wysyłania mailingów nie posiada opcji nie wysyłania wiadomości do osób, które znajdują się w danej tabeli możesz skorzystać z dodatku Power Query, który jest automatycznie zainstalowany jeśli posiadasz Excela w wersji 2013 lub nowszej.
W tym ćwiczeniu utworzymy nową trzecią tabelę która będzie zawierała wszystkie rekordy osób z tabeli A pod warunkiem, że dana osoba nie znajduje się w tabeli B.
W archiwum, które pobrałeś znajdują się dwa pliki: czytelnicy-newslettera.csv oraz zamowienia-30-ost-dni.csv. Pliki z rozszerzeniem CSV są to pliki tekstowe (możesz otworzyć je np. w notatniku). Pliki mają bardzo prostą budowę: każdy wiersz zawiera jeden rekord, kolumny w danym rekordzie są od siebie oddzielone znakiem średnika. Dlaczego nie wrzuciłem tych danych w pliku XLSX? Otóż wiele programów (np. sklepy internetowe, oprogramowanie do wysyłania mailingów) posiada możliwość eksportu danych właśnie do plików CSV.
Aby wykonać to ćwiczenie należy wykonać następujące czynności:
- Uruchom program Excel. Następnie na karcie Dane wybierzpolecenie Z pliku tekstowego/CSV
- Pojawi się okno dialogowe Importowanie danych wybierz plik czytelnicy-newslettera.csv i kliknij w przycisk Importuj
- Pojawi się okno dialogowe, w którym musimy określić jaki znak oddziela poszczególne kolumny w danym wierszu a także w jaki sposób są kodowane „polskie ogonki”. W naszym przypadku w polu Pochodzenie pliku (czyli sposób kodowania polskich ogonków) powinna być wybrana opcja 1250: Środkowoeuropejski. Jako ogranicznik w naszym wypadku powinna być zaznaczona opcja Średnik. Jeśli wszystkie dane są poprawne kliknij w przycisk Załaduj.
Praktyczna uwaga: wiele systemów np. Allegro koduje znaki w standardzie 65001: Unicode (UTF-8) – więc zawsze jak importujesz jakieś dane, zwracaj uwagę na to, czy w podglądzie poprawnie się wyświetlają „polskie ogonki”
- W ten sam sposób zaimportuj drugi plik czyli zamowienia-30-ost-dni.csv
- Zaimportowaliśmy dwie tabele z danymi. Zwróć uwagę na to, że obie tabele mają nieco różną strukturę tj. tabela zamówienia-ost-30-dni nie posiada kolumny Wiek. Tabele, na których będziemy dokonywać scalenia nie muszą mieć identycznej budowy!
- Teraz musimy utworzyć nową tabelę, która będzie zawierała wszystkie rekordy będące różnicą tych dwóch tabel. W tym celu na karcie Dane wybierz polecenie Pobierz dane -> Połącz zapytania -> Scal.
- Pojawi się okno dialogowe Scalenie. I tutaj musimy ustawić kilka rzeczy. Jako pierwszą tabelę scalenia (lewa część złączenia) wybierz z listy rozwijanej czytelnicy-newslettera. Jako drugą tabelę (prawa część złączenia) wybierz zamowienia-ost-30-dni.
- Następnie musimy wskazać klucz wyszukiwania między tabelami (jest to wartość, po której możemy jednoznacznie dopasować rekordy z obu tabel w naszym przypadku jest to adres e-mail, często w różnych relacjach w bazach danych jest to np. nr klienta, numer PESEL). W tym celu w obu tabelach kliknij w nagłówek e-mail a kolumna e-mail zostanie podświetlona.
- Ostatni typ, to rodzaj sprzężenia. Wybierz Anty lewe (rodzaje sprzężeń wkrótce opiszę w osobnej notce na blogu) tj. zostaną dopasowane te rekordy które są w lewej tabeli (tej pierwszej) ale nie znajdują się w prawej tabeli (tej drugiej) i kliknij w przycisk OK.
- Załaduje się edytor Power Query. Ponieważ nie będziemy dokonywać żadnych przekształceń na naszych danych, więc kliknij w przycisk Zamknij i załaduj znajdujący się w lewym górnym rogu ekranu.
- Powstał nowy arkusz o nazwie Scalenie1. W tym arkuszu znajdują się wszystkie rekordy z Arkusza czytelnicy-newslettera z wyjątkiem tych rekordów, które znajdują się w Arkuszu zamówienie-ost-30-dni.
Zaletą narzędzia Power Query jest fakt, że zostały zapisane wszystkie przekształcenia, jakie wykonaliśmy na danych (dwa importy, jedno scalenie). Oznacza to, że jeśli za rok będziesz chciał wykonać to samo ćwiczenie (bo znów będziesz organizował promocję świąteczną) to tak naprawdę wystarczy podmienić oba pliki CSV a w naszym pliku Excelowym w Arkuszu Scalenie1 wystarczy tylko kliknąć prawym przyciskiem myszy na tabeli i wybrać opcję Odśwież. Zresztą zróbmy szybciutko to ćwiczenie:
- Otwórz w notatniku plik czytelnicy-newslettera.csv
- Na końcu pliku dopisz przykładowy rekord np. 128;Gall;Anonim;[email protected];70 (pamiętaj o używaniu średnika, który oddziela poszczególne kolumny w nowo dopisanym rekordzie)
- Zapisz zmiany w pliku czytelnicy-newslettera.csv i go zamknij
- W arkuszu Scalenie1 kliknij prawym przyciskiem myszy i wybierz opcję Odśwież.
- Na końcu naszej tabeli powinien pojawić się Gall Anonim.
