Artykuł AFIN.NET:
AFIN.NET – Moja własna funkcja DANE() © AFIN 1995-2010
Wojciech Gardziński
Tworzymy własną funkcję AFIN.NET –
- tu (przykład): lepsza WYSZUKAJ.PIONOWO()
Summary in English:
Do you like VLOOKUP? Great! You probably know its limitations.
There is a procedure to make your own VLOOKUP function, being able to get data from name parametrized, external, closed spreadsheets via ODBC without programming anything in VBA.
Spis treści:
Przygotowanie zapytania, definiującego funkcję
Wybór bazy danych (skoroszytu)
Wybór tabeli (czyli arkusza Excela)
Usunięcie pola wymiaru analizy
Określenie miejsca umieszczenia kwerendy w arkuszu
Wstęp
Aby dobrze zrozumieć sens tworzenia własnej funkcji, przytoczmy definicję słowa funkcja:
Jest to jednoznaczne przyporządkowanie wartości określonemu zestawowi argumentów według pewnego, znanego algorytmu.
Podobnie jest z informacjami biznesowymi – na podstawie znanych wartości argumentów, np. nazwy klienta i zakresu czasu, możemy jednoznacznie określić sumę sprzedaży tegoż klienta w owym czasie.
Takie przyporządkowanie nazywamy też „miarą biznesową” – określ, co potrzebujesz (miara) oraz jak i gdzie (algorytm, argumenty) to znaleźć, a pewien system (funkcja) zwróci ci wartość.
W przypadku wyszukiwania danych w systemach biznesowych napotykamy jednak na pewne dodatkowe trudności, związane ze specyfiką znanych technologii dostępu do danych. W ODBC, aby sterowniki ODBC, programowane językiem SQL, mogły odszukać żądane wartości, muszą odszukać je według dwóch argumentów:
- Źródła danych – ciągu tekstowego, określającego typ bazy, użyty sterownik, określenie bazy danych (miejsce, nazwa), parametry dostępu (np. logowanie) i wiele innych
- Zapytania – ciągu tekstowego w określonym języku zapytań, najczęściej w SQL
Nasz problem jednak jest banalny, my nie chcemy roztrząsać tu żadnych definicji, studiować technologie, poznawać języki dostępu do danych.
My chcemy dane!
Załóżmy, że mamy jakiś zestaw plików Excela – w przykładzie są to 2 pliki Book1.xls i Book2.xls, w których istnieją w każdym pliku dwa arkusze: Sheet1 oraz Sheet2. W arkuszach tych dysponujemy pewnymi listami – w kolumnie A, o nagłówku FieldText, mamy jakieś wartości tekstowe, w kolumnie B, o nagłówku FieldNumeric – wartości numeryczne
Na ilustracji poniżej przedstawiono zawartość obu plików i arkuszy
Zawartość arkuszy, użytych w przykładzie
Ludzie, pracujący i znający Excela krzykną – „Przecież to załatwia funkcja WYSZUKAJ.PIONOWO(), zawarta w Excelu od lat!”. – Oczywiście, że tak, ale funkcja ta działa wyłącznie na plikach otwartych lub powiązanych na sztywno łączami międzyskoroszytowymi, a, jak plików jest kilkadziesiąt, robi się z tego bardzo awaryjny system, o czym analitycy wiedzą doskonale.
Stwórzmy więc funkcję, która zapewni nam funkcjonalność excelowej funkcji WYSZUKAJ.PIONOWO, ale która będzie miała dodatkowe atuty: możliwość wyszukiwania danych w zamkniętych plikach zewnętrznych oraz możliwość działania parametrycznego, tj. wyszukiwania danych również w plikach, nigdy wcześniej nie otwieranych w celu stworzenia do nich łącza. Ma również działać, gdy… pliku-źródła nie będzie – ma wtedy zwrócić po prostu wartość ‘0’ i nie zwracać błędu – sprzedaż dla nieistniejącego klienta ma wszak wartość 0, nieprawdaż?
Oczywiście wszystkie dane (ze wszystkich źródeł danych – tu: obu skoroszytów) powinny się docelowo znaleźć na jednym arkuszu Excela, bo tu właśnie tworzymy nasz raport.
Ale takiej funkcji nie ma.
Ani na liście funkcji Excela, ani na liście predefiniowanych funkcji w AFIN.NET.
Standardowa lista funkcji AFIN.NET
Tworzymy więc nową – własną funkcję.
Przygotowanie zapytania, definiującego funkcję
Aby stworzyć funkcję, pobierającą dane przez ODBC, wystarczy dysponować naprawdę minimalną wiedzą z tego zakresu – Excel od lat oferuje graficzny edytor zapytań bazodanowych SQL – program Microsoft Query.
Program Microsoft Query
Uruchamiamy program Microsoft Query (Jest dostępny w każdej kopii każdej wersji Excela – gdy nie jest zainstalowany domyślnie, trzeba to zrobić)
Wybieramy źródło danych. Excelowe źródło danych rejestruje się pod różnymi nazwami „Pliki programu Excel”, „Excel files”, itp. Gdy takowe nie istnieje lub istniejące nie działa – należy spróbować utworzyć nowe źródło danych (pierwszy element tej listy), wybrać dostępny (na pewno jest) sterownik Excela, itd.
Pamiętamy o NIEUŻYWANIU kreatora kwerend!
Wybór źródła danych
Wskazujemy bazę danych. W excelowym źródle danych to po prostu plik Excela.
Nawigacja w okienku katalogów jest starego typu, ale można się przyzwyczaić.
Wybór bazy danych (skoroszytu)
Wskazujemy tabelę. W excelowym źródle danych tabela to albo tabela w skoroszycie, albo nazwany obszar skoroszytu, albo, po prostu, arkusz Excela.
Arkusze mogą nie być widoczne w poniższym okienku – należy wybrać Opcje… / Tabele systemowe. Arkusze pojawiają się ze znakiem ‘$’ na końcu nazwy.
Wybór tabeli (czyli arkusza Excela)
Teraz wybieramy pola. Pól może być wiele – zawsze możemy wybrać „gwiazdką” wszystkie pola, a potem niepotrzebne pousuwać, albo - efektywniej - podejść do tematu systematycznie i wybierać pola do kwerendy po kolei: pole miary (tu: byłoby FieldNumeric), potem pola ‘wymiarów analizy’ (tak to się nazywa w analizie danych), czyli pola argumentów, które chcemy podawać w funkcji jako warunki ograniczające (tu: FieldText)
Gdy Autoodświeżanie kwerendy jest włączone (ustawienie domyślne, ikona: wykrzyknik ze strzałkami) dane pojawiają się natychmiast.
Wybór pól z tabeli danych.
Funkcja z założenia zwraca wartość lub tablicę wartości. My chcemy używać funkcję ‘normalnie’ – nie-tablicowo, więc musimy doprowadzić nasze pole miary do stanu, że wynikiem kwerendy będzie JEDEN REKORD ze zagregowaną informacją NA JEDNYM POLU miary. Tu użyjemy agregowania funkcją Suma() – w SQL SUM().
Ustawiamy się na polu miary (FieldNumeric) i klikamy narzędzie – ikonę „∑”, dostępną na pasku narzędzi MS Query.
Agregacja danych
Uwaga: Gdy, omyłkowo, klikniemy ją kilka razy, należy klikać dalej, aż, po kilku krokach, znowu otrzymamy dane niezagregowane, a potem, znowu, funkcję SUM().
Przystępujemy do definiowania argumentów naszej funkcji.
Czego właściwie chcemy? Co nam daje wstawienia argumentu funkcji?
Chcemy wyszukać rekordy i obliczyć sumę danych dla tych wyszukanych rekordów, ale co chcemy wyszukać? Dla nas rekordami wartościowymi są te, które posiadają w kolumnie ‘FieldText’, zadany przez nas parametr – w naszej funkcji będzie to jej argumentem.
Ograniczamy więc zestaw rekordów dla jakieś przykładowej wartości – tu: dla wartości ‘aaa’ (dowolna, i tak to my chcemy ją później, już w funkcji, podawać).
Klikamy więc na wartość ‘aaa’ w 1-szej kolumnie, a następnie klikamy ikonkę filtra na pasku narzędzi – na rysunku poniżej przedstawiono sytuację już PO kliknięciu.
Stworzenie kryterium kwerendy
Jak już wskazano wcześniej, dążymy do stworzenia kwerendy z jedną wartością na jednym polu – tu mamy jeszcze dwa pola: ’FieldText’ i ’Sum of FieldNumeric’. Usuwamy więc zbędne już pole FieldText.
Usunięcie pola wymiaru analizy
Kwerenda jest gotowa.
Wysyłamy (zwracamy) więc dane do Excela.
Zwrócenie danych do Excela
Wybieramy miejsce, gdzie dane mają zostać umieszczone (zupełnie dowolne miejsce) – musi być to jednak zwykła tabela – nie: tabela przestawna ani wykres przestawny.
Określenie miejsca umieszczenia kwerendy w arkuszu
Gotowe. Dane są już w arkuszu. Kwerenda zwraca jedną, słownie JEDNĄ, wartość.
Widok kwerendy w arkuszu
Jeżeli chcemy dokonać modyfikacji w naszej kwerendzie (zwróciła za mało lub też za dużo wartości, nie zdefiniowaliśmy jakiegoś, kolejnego argumentu, inne) możemy tego dokonać edytując kwerendę.
Edycja kwerendy
Definicja funkcji
Teraz przychodzi czas na najtrudniejszą operację – definicję własnej funkcji na podstawie stworzonej powyżej kwerendy.
Uruchamiamy kreatora funkcji DANE() (alias GETDATA), czyli miary biznesowej AFIN.NET
Kreator – sposób uruchomienia
Wyświetla się okno Kreatora.
Kreator ma na celu stworzenie definicji miary biznesowej w AFIN.NET, czyli czegoś, co będzie informowało funkcję, jak przetworzyć informacje, uzyskane na podstawie wartości jej zadanych argumentów, na ciągi tekstowe ‘źródła danych’ (connection string) i ‘zapytania bazodanowego’ (query text).
Kreator – wygląd okna
Miara
W pierwszym kroku definiujemy NAZWĘ naszej miary
Definicja nazwy miary i informacja, gdzie jest umieszczana w definicji miary
Argumenty funkcji
Przystępujemy do definicji argumentów funkcji. Rozpoczynamy od określenia nazwy argumentu.
Definicja nazwy 1-go argumentu funkcji
W 2. kroku MAPUJEMY ów argument na część ciągu tekstowego – w tym przypadku część definicji źródła danych – wskazanie skoroszytu
Wygląd kreatora po kliknięciu przycisku „Mapuj”. Proszę zwrócić uwagę, że definicja miary zawiera od razu podaną przez nas informację.
W analogiczny sposób mapujemy zmienną (argument) WorksheetName.
Krok powyższy musimy powtórzyć wielokrotnie, gdyż wartość argumentu WorksheetName (tu: ‘Sheet1’) występuje w naszym ciągu zapytania kilka razy (tu: czterokrotnie)
Wygląd okna kreatora po definicji argumentu WorksheetName.
Definiujemy kolejny argument – LookupValue, czyli wartość, według której będziemy filtrować dane do posumowania (tu: ‘aaa’).
Wygląd po dodaniu definicji 3. argumentu.
Zapis definicji
Klikamy przycisk zapisu nowej miary biznesowej…(powyżej), …potwierdzamy zapis nowej miary…
… i możemy jej natychmiast używać.
Budowa raportu
Wystarczy uruchomić w dowolnym miejscu Kreatora Funkcji DANE()
Sposób uruchomienia - żółty przycisk funkcji na pasku narzędzi AFIN.NET lub [F3]
Wybieramy nazwę nowej, zdefiniowanej przed chwilą funkcji.
I, krok po kroku tworzymy raport.
Dalsze kroki, aż raport będzie odpowiednio sformatowany, formuły sparametryzowane, itd.
Gotowe. Życzymy efektywnej pracy z własną funkcją!
Podsumowanie
Uzyskana funkcja jest jedną z prostszych funkcji, jakie można zbudować tą metodą. Zawiera zaledwie trzy, proste argumenty, jednoznacznie odwołujące się do nazw obiektów Excela: skoroszytu i arkusza.
Model danych AFIN.NET umożliwia tworzenie funkcji o max. 5-ciu argumentach (Nazwa samej funkcji może być 6-tym argumentem)
Funkcje mogą odnosić swoje argumenty do dowolnych elementów zarówno ciągu zapytania (Query Text) – to potrafią wszyscy, ale potrafi też parametryzować ciąg połączenia bazodanowego (Connection String) – tego nie robi nikt. A w przypadku Excela każdy skoroszyt jest bazą danych, więc wymaga innego ciągu połączenia.