Ale nie wolno go kopiować ani drukować, chyba, że uzyska się od autorów odpowiednią, pisemną, zgodę. Taki, my autorzy, mamy kaprys. Prosimy o poszanowanie.
W odpowiednim czasie zostanie opublikowany w formie papierowej.
Tekst nie jest jeszcze gotowy – ale codziennie jest go więcej… Zapisz w ‘Ulubionych’ ;)
Podoba się? Zapraszamy na szkolenia.
Rozdział książki WG&KR „SQL w Excelu”
Wojciech Gardziński, Krzysztof Rumiński © 2010
Spis treści:
Pozyskanie danych ze źródła, zupełnie od nas niezależnego
Przetworzenie danych w sposób, umożliwiający swobodną analizę
Stworzenie raportu, odświeżalnego na każde życzenie, w narzędziu łatwym w obsłudze i ogólnodostępnym
Praktyka wymiany danych – Przykład praktyczny: Pozyskanie danych ze źródła od nas całkowicie niezależnego
Postanowiliśmy zrobić przykład praktycznego procesu przetworzenia informacji od momentu jej znalezienia do uzyskania efektu końcowego – gotowego raportu.
Zanim przejdziemy do szczegółów, opiszmy cele, jakie temu przyświecają:
Cele
- Pokazanie kroków dojścia do efektu, w tym wszystkie błędy, próby (w tym „ślepe”), sposób wyciągania wniosków z kolejnych przybliżeń
- Dyskusja na temat najlepszego rozwiązania danego problemu, w tym pokaz rozwiązania typowo „excelowego” i, związanych z tym, problemów praktycznych
- Stworzenie raportu o znacznym stopniu skomplikowania
Pozyskanie danych ze źródła, zupełnie od nas niezależnego
Zastanawialiśmy się chwilę nad źródłem danych do takiego przykładu – i mieliśmy problem: albo pokażemy dane rzeczywiste, ale będą to dane „czyjeś” (nawet po zmianach wartości trudno to będzie opublikować), albo stworzymy je ręcznie (wtedy będą małe i nikt nam nie uwierzy, gdy wspomnimy o jakiś problemach), albo… Internet! Internet to coś zupełnie niezależnego od nas, to taki „System ERP”, który nam coś daje, co my, analitycy, MUSIMY zaakceptować i stworzyć z tego coś, czyli raport.
Ale co? Co będzie ogólnodostępne, łatwe w odnalezieniu i pozyskaniu oraz będzie zawierało dane, z których raport będzie miał sens? I, tu… pomysł!
Katalog webowy, prosty folder plików w Internecie, zawiera dane o nazwie, dacie pliku i jego rozmiarze – i jest ogólnie dostępny. To wystarczy, i tak przewidujemy sporo problemów.
Uwaga! W praktyce nikt nie analizuje zawartości katalogu webowego – wiemy o tym doskonale – proszę jednak zwrócić uwagę, że, czy to jest zawartość katalogu webowego, czy też jakikolwiek import pliku z systemu ERP, niezależnie od tego, czy „do Excela”, czy też „do pliku tekstowego” – problemy są identyczne!
Katalog webowy: http://afin.net/webcasts
Raport do uzyskania: Ranking plików względem ich wielkości z rozbiciem na lata i kwartały daty stworzenia pliku – wszystko w tabeli przestawnej w Excelu.
Źródło danych w przeglądarce internetowej. Wygląda, na pierwszy rzut oka, bardzo dobrze.
Możemy to źródło danych otworzyć również w Excelu (!), wprowadzając adres internetowy bezpośrednio jako nazwę pliku w okienku „Otwórz plik”
Uzyskujemy nowy skoroszyt, a, w nim, arkusz z hiperłączami.
Mamy już dane w Excelu – czegóż chcieć więcej?
Po sformatowaniu danych na czcionkę „Courier New” (czcionka o stałej szerokości znaku) okazuje się, że nawet tekst jest „równy”, więc łatwo go będzie podzielić na kolumny.
Wszystko, na razie, idzie zgodnie z naszymi oczekiwaniami…
Po podziale na kolumny (spacja jako separator) uzyskujemy:
Napotykamy na pierwsze problemy.
- Tylko niektóre daty zostały przez Excela uznane jako daty, reszta pozostaje tekstem i to jeszcze anglojęzycznym – nie będzie można przekonwertować ich na datę żadną funkcja Excela, przynajmniej w polskiej wersji językowej.
- Rozmiar pliku nie dość, że się nie rozdzielił na wartość i jednostkę, to jeszcze zawiera kropkę i jest tekstem. Nie dość tego: raz przyrostek to „k” znaczy kilobajty (trzeba wartość pomnożyć przez 1.000) lub „M” – Megabajty (wartość mnożymy przez 1.000.000, ale uwaga, wartość jest z miejscem po przecinku, a tu separatorem jest kropka)
Trzeba więc wykonać sporo pracy, aby uzyskać efekt – daty w kolumnie C oraz wartości numeryczne w kolumnie E.
Wykonujemy więc ręcznie 12 zamian w kolumnie C, po kolei zamieniając tekst trzyliterowego skrótu miesiąca po angielsku np. „Jan” na podobny skrót polski „Sty”, itd.
Oto efekt pierwszej zamiany – reszta miesięcy na podobnej zasadzie.
Po wykonaniu 12 zamian problem dat został rozwiązany – tu trzeba przyznać skuteczność Excelowi – zamiany zostały wykonane na danych tekstowych, a otrzymane teksty automatycznie zamieniły się na wartości dat! Nie zawsze przy importach tekstów się tak zdarza.
Podobnie postępujemy w kolumnie E, gdzie zamieniamy kolejno przedrostek jednostki M (Mega) na „00000” (Pięć zer, nie sześć! Mamy szczęście, że katalog webowy, dla danych w Megabajtach, pokazuje zawsze wartość z jednym miejscem po przecinku – inaczej mielibyśmy spory kłopot z usunięciem kropki) i k (kilo) na ”000” (tu dane zawsze bez miejsc po przecinku), a na samym końcu ‘.’ (kropka) na ‘’ (ciąg pusty).
Proszę znowu zwrócić uwagę na „inteligencję” Excela, bowiem dane tekstowe znowu stały się automatycznie wartościami numerycznymi.
Teraz to już nie problem – Usuwamy wiersze 4:7, zawierające puste komórki oraz niepotrzebny nam nagłówek folderu webowego oraz tworzymy tabelę przestawną na obszarze danych, tj. (tu) zakresie komórek B3:F116
Następnie ustawiamy i odpowiednio formatujemy tabelę przestawną, grupując daty według lat, kwartałów i miesięcy
Formatujemy liczby na wartości bez zer po przecinku, ale z separatorem tysięcznym oraz określamy poziom wyświetlania danych z dokładnością do kwartału. Sortujemy otrzymane sumy malejąco, otrzymując efekt końcowy:
Uff, jest! Ale…
Nieeeeeeee! Zadzwonił telefon, że informatyk dodał do katalogu ftp jeden plik!
Wszystko od nowa: import, podziały kolumn, zamiany, tworzenie tabeli przestawnej, jej format i grupowanie – straciliśmy 15 minut pracy…
Jak więc to zautomatyzować?
Pierwszym, narzucającym się, rozwiązaniem, jest nagranie wszystkich, powyższych, operacji jako makro, rejestratorem makr VBA. Co więcej, nagrywarka (rejestrator) makr prawdopodobnie stworzy makro rzeczywiście funkcjonalne. Z paroma wyjątkami, np. zakres tabeli zostanie zapisany na sztywno, a przecież nie wiadomo, czy jutro nie dojdą nowe pliki i rozmiar jej danych się zmieni. Trzeba analizować makro i je poprawić – kto to potrafi?
Poza tym, kto wie, gdzie plik z makrem zapisać, jak uruchamiać, jak dzielić się nim ze współpracownikami? VBA to programowanie – żeby stworzyć (tu: tylko poprawić) dobry program, potrzeba sporo umiejętności i doświadczenia.
Więc, jak to zrobić inaczej – profesjonalnie?
Przetworzenie danych w sposób, umożliwiający swobodną analizę
Profesjonalne podejście do tematu opiera się na kilku zasadach:
- Wszystko (cały proces od pozyskania danych do dostarczenia gotowych danych do raportu) musi się odbywać automatycznie, wymagając od użytkownika, co najwyżej, trzykrotnego użycia myszy lub klawiatury.
- Dane, po ich zebraniu i przetworzeniu, powinny być gotowe do użycia przez więcej niż jedną osobę – inicjatora procesu odświeżania. Dane powinny być dostępne zarówno w formie hurtowej (jako tabela danych), jak i detalicznej (pojedyncza wartość, szczegółowo wyspecyfikowana zapytaniem), czyli, gdy poprawnie sporządzimy odpowiednie zapytanie bazodanowe, mające na celu otrzymanie jakiejś jednej, precyzyjnie określonej wartości, to mamy mieć możliwość ją otrzymać. Dane powinny być udostępniane użytkownikom na zasadzie wielodostępu tylko do odczytu, tzn. nieautoryzowany użytkownik ma nie mieć prawa ich zmienić.
- Sporządzenie procesu, jego ustawienie i automatyzacja nie mogą wymagać od użytkowników umiejętności programowania w jakimkolwiek stopniu, co najwyżej zmianę parametrów, najlepiej wypreparowanych i umieszczonych w łatwo dostępnym i edytowalnym, miejscu. Jeżeli trzeba zastosować jakieś formuły obliczeniowe, to tylko w Excelu, bo (zakładamy, że) użytkownik to potrafi.
Stworzenie raportu, odświeżalnego na każde życzenie, w narzędziu łatwym w obsłudze i ogólnodostępnym
…czyli Excelu. Ale bez wad procesu, opisanego szczegółowo powyżej.
Jeżeli dane pochodzą z Internetu (czymkolwiek są) warto spróbować dostać się do nich tzw. kwerendą internetową. Gdyby dane były w lokalnym pliku tekstowym, odpowiednikiem byłaby zwykła kwerenda do danych tekstowych.
Tworzymy kwerendę internetową.
Dane kwerendy wstawiamy do nowego arkusza (jak zawsze przy wykorzystaniu kwerend)
Uwaga: Poniższy krok (ustawienia właściwości kwerendy) można zrobić już, czyli w bieżącym kroku, można też zrobić to później, już po wstawieniu kwerendy do arkusza.
Tu pokazujemy, jak ustawić właściwości kwerendy (po naciśnięciu przycisku ‘właściwości’)
- Opcja: Odśwież co X minut
- Opcja: Odśwież przy otwarciu pliku
- Opcja: Dostosuj szerokość kolumn
Gdy opcja ta jest zaznaczona, kwerenda automatycznie dostosowuje nam szerokość kolumn, a rzadko tego chcemy. Odznaczamy ją. - Opcja: Wstawiaj i usuwaj komórki przyległe do danych
Ta, i kolejna, opcja to kluczowe opcje tej metody. Kwerenda, jak wiadomo, to obiekt arkuszowy zmiennej długości. Posiada jednak ciekawą możliwość – wszystkie formuły do niej przylegające kopiują się (lub usuwają) wraz ze wzrostem (zmniejszeniem) ilości wierszy. Nawet o tym nie trzeba pamiętać. - Opcja: Wypełniaj automatycznie formuły w zakresie, przyległym do danych
Następnie, już w arkuszu, musimy stworzyć formuły Excela, które umożliwią nam takie przetworzenie danych, uzyskanych z kwerendy, aby łatwo je przetwarzać dalej tabelą przestawną – to najtrudniejsza i najbardziej mozolna część naszego zadania.
Obszar A:F (widok poniżej) to kwerenda – tu niewiele możemy zrobić. Dane, gdy się odświeżają, zawsze mają taki sam format – celem tego zakresu jest tylko dostarczenie danych
Obszar G:N to formuły, przetwarzające dane – przyjrzyjmy się im bliżej:
G7: =VALUE(RIGHT(D7;4))
Obliczamy rok (wartość numeryczna) z daty (wartość tekstowa) – pomimo tego, że wiele z wierszy zwróci nam błąd, gdyż w niektórych z nich data już jest ‘datą’.
H7: =MATCH(MID(D7;4;3);{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"};0)
Przyporządkowanie wyciętego tekstu nazwy miesiąca do tablicy trzyliterowych nazw miesięcy po angielsku – formuła ta zwraca nam numer miesiąca w formie liczby 1-12.
I7: =VALUE(LEFT(D7;2))
Obliczenie numeru dnia w formie liczby z daty w formie tekstu
J7: =IF(ISERROR(DATE(G7;H7;I7));D7;DATE(G7;H7;I7))
To tzw. „Obsługa błędów”, czyli sprawdzenie czy data oblicza się poprawnie: jeśli tak to ma się obliczać, jeśli nie to znaczy, że komórka D7 już dostarcza właściwą datę.
K7: =INT((MONTH(J7)-1)/3)+1
Obliczenie numeru kwartału z miesiąca obliczonej daty
L7: =LEFT(F7;LEN(F7)-1)
Wycięcie kawałka tekstu, dotyczącego rozmiaru pliku: ‘wszystkie znaki oprócz ostatniego’
M7: =RIGHT(F7;1)
Ostatni znak, czyli oznaczenie jednostki, a właściwie jej mnożnika: (M)megabajty, (k)kilobajty.
N7: =IF(M7="M";VALUE(SUBSTITUTE(L7;".";","))*1000000;L7*1000)
Przemnożenie wartości przez odpowiednią wielkość mnożnika poprzedniej kolumny
Ale, pomimo, że już wszystkie formuły są i działają prawidłowo, stworzenie tabeli przestawnej dalej będzie kłopotliwe, z względu na:
- Brak nagłówka w F1 – i nic na to nie poradzimy – dane tak właśnie prezentują się w kwerendzie
- Błędy i niepotrzebne obliczenia pomocnicze w kolumnach G:M
Aby więc uniknąć błędów tabeli przestawnej, tworzymy dodatkowy „blok danych”, dalej „przyklejony” (nie może być pustej kolumny rozdzielającej) do naszych poprzednich formuł, ale posiadający, wybrane już przez nas, wartościowe kolumny danych. I tak:
O7: =C7
P7: = J7
Q7:= N7
Na tych kolumnach tworzymy tabelę przestawną
Odpowiednio ją formatujemy, uzyskując efekt końcowy.
Zalety powyższej metody:
- Łatwe i efektywne odświeżanie – 2 (słownie: dwa) kliknięcia myszą!
- Całkowity brak makr – tylko i wyłącznie formuły arkuszowe
- Pełna czytelność formuł
- Bezpieczeństwo procesu
Wady, tj. niedogodności, które, pomimo poprawnego podejścia, występują nadal
- Trudności w dzieleniu się plikiem
- Pomimo tego, że tylko dwa kliknięcia myszą odświeżają nasz raport, to dalej są dwa – lepsze byłoby jedno.
Automatyzacja w AFIN.NET
Cdn.