Poprzednia część kursu odnosiła się przede wszystkim do pracy w arkuszu, wykorzystaniu jego funkcji, opcji oraz procedur. Wiemy jak sumować, agregować w inny sposób dane, wiemy jak wyszukać dane interesujące z gąszcza danych w tabeli Excela, wiemy jak parametryzować funkcje, jak odnosić je do danych w innych arkuszach i skoroszytach.

Przyszła więc kolej na następny krok – aby dane w arkuszu obrabiać – dane w arkuszu jakoś muszą się uprzednio znaleźć. W tej części kursu standardową funkcjonalność osoby, obsługującej arkusz kalkulacyjny, jaką jest sprawne wpisywanie danych (złośliwi mówią: klepanie), skwapliwie pomijamy. Postaram się Państwa przekonać, że

 

efektywna aplikacja, zbudowana w lub na bazie arkusza kalkulacyjnego nie wymaga ręcznego wpisywania danych,

 

chyba, że danych parametrów, adresów plików, kodów dostępu, itp. Ten i kilka następnych felietonów naszego cyklu poświęcimy problemowi pozyskania danych do Excela.

 

Zacznijmy jednak od podstaw. Zastanówmy się nad odpowiedzą na pytanie: Dane – jak je chcemy mieć, czyli czego wymagać? Do czego dążymy, przeglądając opcje naszego modułu raportowego lub ustawienia wydruku z programu FK?

 

Niniejsze opracowanie ma charakter ogólny, opisowy i mało „warsztatowy”. Stanowi jednak opis nie tyle samych procedur, ile opis przydatności poszczególnych procedur do różnych rodzajów problemów. Gdy potem poznamy te procedury, niniejszy tekst stanie się „jaśniejszy”. Warto więc do niego wrócić.

 

POŻĄDANE FORMATY

Dostępność do danych zewnętrznych, w odróżnieniu od tworzenia tabel w samym arkuszu kalkulacyjnym, ma pewną właściwą sobie cechę: Dane, ich format, struktura, układ i objętość nie zależą od nas, czyli od ich użytkownika. To producenci systemów transakcyjnych decydują, do jakiego formatu dane z tegoż systemu wyeksportować, jaki mają mieć one układ, itp. I, proszę nam wierzyć, wcale nie zachodzi bezpośrednia zależność pomiędzy „nowoczesnością” modułu raportowego, a jakością danych z niego wychodzących. Na czym więc nam powinno zależeć, aby dane dobrze nadawały się do pracy z nimi w Excelu?

 

Doświadczenie podpowiada nam, że

(1) dobre dane to takie, w których wszystkie informacje danej tabeli znajdują się w wyodrębnionych rekordach-wierszach tabeli.

Przy czym format zapisu tych danych nie ma decydującego znaczenia (!). Moduły, eksportujące bezpośrednio do Excela, powodują niekiedy więcej szkód niż pożytków - to paradoks, ale prawda - radzimy omijać.

 

Rys 1. Dane „poprawne” – pomimo faktu, że w „zaśmieconym” tekście

Rys 2. Dane „bezużyteczne” – pomimo faktu, że w Excelu

 

Większość formatów bazodanowych warunek ten spełnia, niejako, automatycznie. Ale spotyka się czasami wydruki lub arkusze kalkulacyjne, w których istnieje tzw. struktura wewnętrzna wydruku – tak jak pokazano na Rys.1. powyżej, w jego drugiej części. Pojęcie „struktura” oznacza tu, że najpierw znajdujemy rekord (wiersz) nadrzędny, tu: wiersz z nazwą konta i numerem konta (na rys. wiersz 2., ale też 7. i 11.) a pod nim dopiero wiersze pozostałe, z innymi danymi, jego dotyczącymi, tu: wiersze z danymi zaksięgowanych dokumentów (na rys. wiersze 3:5, ale też 8:9 i 12 i dalsze). Normalne procedury importowe nie obsługują hierarchicznych (ze strukturą) plików danych.

 

Drugim problem w powyższym arkuszu, dostępnym już w Excelu, jest fakt psucia przez Excela formatów danych. O ile z kolumną E (tj. żeby otrzymać dane numeryczne) poradzimy sobie bez trudu (zaznaczamy kolumnę E, i opcją Zamień zamieniamy w niej kropki na przecinki), o tyle dane z kolumny A są bardzo ciężkie do odczytania - trzeba pisać skomplikowane funkcje konwertujące na jednolity format.

 

Biorąc pod uwagę formaty danych, to:

(2) dobre dane to takie, do których posiadamy sterownik ODBC lub inny sterownik dostępu (zdarza się coraz częściej, że producenci aplikacji dostarczają takowy – warto się o niego postarać). Więcej o tym później, przy tematach, dotyczących procedur dostępu do danych zewnętrznych

Niektóre formaty danych są przez Excela odczytywane wprost i nie stanowi to problemu, np. formaty CSV, DBF, czy też (w najnowszej wersji Excela 2003) pliki XML, nie mówiąc, oczywiście, o plikach samego Excela. Teksty płaskie (taki np. jak na Rys1. - w pierwszej części), pomimo specjalnej, gotowej i dostępnej w Excelu procedury importowej, importuje się źle, bowiem z nagłówkami, znakami drukarskimi, itp. Nie zawsze starcza nam wtedy 65.000 wierszy arkusza Excela.

 

Kolejnym kryterium oceny danych pod kątem ich przydatności, jest ich wewnętrzna struktura:

Łatwiej obrabia się tabele „szerokie” – to oczywiste, ale – w przypadku importu danych – nierealne do uzyskania wprost – relacyjne bazy danych zostały wymyślone właśnie po to, aby tego uniknąć.

Pola krótkie, o niewielkiej ilości znaków nadają się do analizy tabelami przestawnymi, pola długie do analizy funkcjami wyszukiwawczymi (np. Suma.jeżeli)

(3) dobre dane to dane w tabelach o dużej ilości krótkich pól lub w przypadku pól długich, o znanej i stałej strukturze znaków w polu

 

Rys 3. Dane, przydatne pod kątem analizy w Excelu według kryterium konstrukcji tabeli (zestawu tabel) danych.

 

W przypadku wydruków obowiązują nieco inne cechy wzorcowe.

Na Rys.1. pokazano bardzo dobry wydruk do pracy z Excelem, tylko, że... nie wolno go importować do Excela! Przynajmniej nie bezpośrednio! Należy to zrobić przez MS Query.

Równie dobrą postacią pliku tekstowego przy imporcie do Excela jest plik rozdzielany przecinkami (tzw. format CSV) lub dowolnymi innymi znakami.

 

UWAGA: Powszechnie uznawany pogląd, że analizę danych można prowadzić w Excelu pod warunkiem, że dane nie mają więcej, niż 65000 rekordów, jest całkowicie błędny! W następnym odcinku pokażę, jak to efektywnie zrobić.

 

CELE

Określmy więc cel naszej procedury poboru danych, tak, aby każdy jej krok, w wielu aspektach naraz, zbliżał nas do celu ogólnego, jakim jest efektywna analiza.

 

Podstawowe pytania to:

Czy chcemy używać tabeli przestawnej? (Zalety i wady tabeli przestawnej w innym odcinku kursu) – preferujemy wtedy pola krótkie, dopuszczamy możliwość bezpośredniej budowy kwerendy z tabeli (teoretycznie uniezależnia nas to od bariery 65000 rekordów analizowanych danych)

 

Czy będziemy stosować funkcje obliczeniowe i wyszukiwawcze? Preferujemy wtedy pola długie o znanej strukturze znaków w polu, najlepiej tekstowym. Pola sumowania musza oczywiście posiadać format numeryczny. Patrz niżej: pole kombi

 

Czy arkusz obliczeniowy będzie przesyłany pocztą elektroniczną lub używany na wielu stanowiskach? Preferujemy wtedy dane zagregowane, zamiast „analitycznych”.

 

Celem poboru danych do Excela jest otrzymanie możliwie NAJSZERSZEJ tabeli danych, tzn. tabeli o jak największej ilości pól. Format jest nieważny! Pliki tekstowe mogą mieć „nieskończoną” szerokość. Gdy nasz zestaw danych jest zestawem tabel tzw. relacyjnej bazy danych, tak jak pokazano na Rys.3., czyli w wielu tabelach mamy wiele krótkich pól, wszystko jest w porządku! Trzeba tylko użyć odpowiednich metod, żeby sprowadzić te dane do szerokiej, ale jednej, tabeli. (patrz: felieton o MS Query).

 

 

POLE KOMBI A ATRYBUTOWANIE

O zaletach atrybutowania danych wiemy już dużo, pora określić więc wadę tej metody: W Excelu (standardowym) brak jest efektywnej funkcji sumującej dane według wielopolowego kryterium wyboru rekordów. Można działać albo na jednym polu, albo być zmuszonym do używania tabel przestawnych. Jest jednak metoda, która, przy kompetentnym użyciu połączy nam zalety wielowymiarowości tabeli z elastycznością funkcji – my nazywamy tę metodę „POLEM KOMBI” - całkowicie sztucznym polem (kolumną), obliczanym WYŁĄCZNIE w celu dokonywania analiz funkcją Suma.Jeżeli.

 

Czym jest pole kombi?

 

Rys.4. Dana jest tabela (plik rk2001.dbf):

 

Jeżeli naszym celem jest posumowanie wartości tabeli według tylko numerów zleceń lub tylko wybranych kont, funkcja Suma.Jeżeli(...) jest dobrym rozwiązaniem. Problem występuje, gdy interesującą nas warunek obejmuje dwa lub więcej pól. Funkcja BD.Suma rozwiązałaby ten problem, gdyby nie jej, opisana już wada.

 

Jak więc połączyć zalety obu wymienionych funkcji?

Sumować możemy z warunkiem jednopolowym, więc stwórzmy sztucznie dodatkowe pole (może być wiele takich pól), zawierające interesujące nas dane. I tak, gdy interesuje na np. konto i zlecenie, wpiszmy formułę =G:G&C:C w całą kolumnę (H) obok danych. Nasza tabela wygląda teraz tak:

 

Rys.5. Pole kombi

 

Wiemy już, jak sumować dane po warunku, zawierającym jedno pole, więc Suma.Jeżeli posumuje nam żądany warunek, bo.... nie „zauważy”, że w naszym polu „kombi” znajdują się informacje z dwóch lub więcej pól!

Przykładowy warunek (dot. tabeli powyżej)

=Suma.Jeżeli(H:H;”=Zlec_75401*”;E:E) posumuje kwoty dla zlecenia 75. dla konta 401, więc cel swój osiągnęliśmy! (Wartość funkcji dla całej tabeli: 1903,05)

 

Problemy, które występują przy zastosowaniu tej metody:

  1. Jak ujmować w polu kombi dane numeryczne i dotyczące dat?

Dane numeryczne w polach, które chcemy przeszukiwać, nie stanowią poważnego problemu. Zastosowanie funkcji TEKST(), omówionej w poprzednich odcinkach, eliminuje je całkowicie: np. TEKST(AdresPolaDaty;”RRRR.MM.DD)

  1. Dane w polach są różnej długości
    W powyższej tabeli mamy właśnie taki przykład: pole zlecenia ma wprawdzie określoną stałą długość, ale pole kown nie i czasami, dodatkowo, w ogóle nie ma wartości. Radzimy sobie poprzez sztuczne usztywnienie długości pola formułą LEWY(AdresPolaOZmiennychWartościach&”……….”;10) – takie pole ma zawsze 10 znaków.
  2. Dane są tak znacznej objętości (np. pow. 20.000 wierszy), że wpisanie formuł pola kombi jest samo w sobie uciążliwe – radzimy sobie inaczej, patrz; felieton o „MS Query” w naszym cyklu.

 

Test:

  1. Napisz formułę pola kombi oraz funkcję, sumującą interesujące Cię dane, jeżeli są one określone warunkiem: wartość księgowań dla zleceń o numerach, zaczynających się od cyfry 7 oraz dla kont, kończących się na „01”.
  2. Scharakteruj wymienione formaty danych.
  3. Określ główne cechy danych, które należy wymagać od systemu lub firmowego informatyka celem ich dalszej obróbki w Excelu.

 

Rozwiązanie testu (zad.1):

Formuła pola kombi jak wyżej. Funkcja: =Suma.Jeżeli(H:H;”Zlec_7????01”;E:E)