Artykuł AFIN.NET:
Łącza międzyskoroszytowe Excela w AFIN.NET © AFIN 1995-2010
Wojciech Gardziński 2010.05
Początkowo tytuł miał brzmieć:
„Dlaczego nie stosować łącz…”, „Nie należy, nie wolno, itp.”
Ale, w sumie, „Wolność Tomku…”. Przyjrzyjmy się przyczynom problemów.
Przyczyny problemów, związanych z łączami międzyskoroszytowymi Excela
i sposoby ich rozwiązania za pomocą Excela, ADO oraz AFIN.NET
Summary in English:
This is about Excel links and problems with them. Why we use the links, why they make problem, how to resolve the problem using Excel, ADO and AFIN.NET.
Dlaczego powstają łącza międzyskoroszytowe?
- Bo się je łatwo tworzy do analiz ad-hoc.
To zaleta nie do przecenienia!
Dlaczego powstają z nimi problemy?
- Ponieważ elastyczna wymiana informacji w super-elastycznym środowisku arkusza kalkulacyjnego oraz równie elastycznym środowisku plikowym (skoroszyty Excela) – łącza niewątpliwie są bardzo elastyczne – jest niejako zaprzeczeniem efektywności informatycznej. Bo się łatwo kopiuje formuły, arkusze, ale i całe skoroszyty. Bo łatwo się je wysyła pocztą (niekontrolowane wyjście elementu systemu poza system), bo się łatwo edytuje skoroszyty, bo… Excel jest elastyczny.
A środowisko jest zbyt zmienne, by to się mogło udać.
Przeanalizujmy owe problemy
Problem 1. Ogromna ilość łącz
Łącza tworzy się łatwo. Wpisuje się znak równości, klika na inna komórkę (często w innym skoroszycie), potwierdza ‘Enterem’, potem ‘kopiuj i wklej’ na tysiącu komórek arkusza. Następnie ‘kopiuj arkusz’, mała zmiana łącza (np. do innego arkusza w skoroszycie-źródle i powtórne skopiowanie do tysiąca komórek. Następnie, przy okazji rozpoczęcia nowego miesiąca, skopiowanie skoroszytu styczeń.xls na luty.xls i już mamy 4.000 (cztery tysiące) łącz zewnętrznych do obsługi: pilnowania spójności, uszkodzeń, położenia plików-źródeł, kontroli poprawności danych, związanej z ingerencja innych użytkowników, itp.
Do tego dochodzą łącza w funkcjach – to dalej łącza odkryte, ale już odwołujące się zwykle nie do komórek, ale do zakresów w innych skoroszytach. Funkcja WYSZUKAJ.PIONOWO() – proteza odtwarzania relacji w bazie danych, gdzie tabela-słownik, jest zwykle skoroszytem zewnętrznym – prawie zawsze zawiera łącza. A ponieważ funkcje w Excelu stosujemy w dużych ilościach, tyle też tworzy nam się dodatkowych łącz.
Funkcje Excela z argumentami, podanymi jako łącza do innych plików, działają(!) nawet, gdy plik-źródło jest zamknięty. To tym bardziej zachęca do stosowania łącz – przy bardzo dużej dyscyplinie systemu, choć przy dużym nakładzie pracy, daje się odświeżać informacje.
Problem 2. „Sztywność” łącz
Gdy stworzymy proste łącze, tzn. ma ono formę =[InnySkoroszyt.xls]Arkusz!A1 wszystko wydaje się działać bezproblemowo. Łącze działa, bardzo szybko przekazuje wartości, jest elastyczne, daje się kopiować (nawet zmienia adresy przy kopiowaniu), więc nie mamy oporów, żeby je stosować, kopiować.
Ale, gdy tylko skoroszyt zamkniemy, do standardowego adresu zostaje dodana ścieżka dostępu do pliku, tj. =’C:\Folder\[InnySkoroszyt.xls]Arkusz’!A1
I tak, jak adres dalej zmienia się przy kopiowaniu, tak wszystkie pozostałe elementy tegoż adresu stają się sztywne – wskazują plik i nie można ich sparametryzować – są dalej „adresem”.
Próbuje się z tym walczyć poprzez Excelowi funkcje ADR.POŚR, np.
=ADR.POŚR(„[InnySkoroszyt.xls]Arkusz!A1”), ale funkcja ta wymaga plików otwartych, czyli „coś za coś” – wszystkie rozwiązania mają wady.
Sztywność łącz jest więc ich największą wadą. Można, co prawda, próbować zarządzać nimi w oknie ‘Definiuj nazwę’, ale jest to możliwe tylko w przypadku niewielkiej ilości łącz – w przypadku zmiany ścieżki, łącze do każdego skoroszytu trzeba poprawiać osobno.
Problem 3. Łącza jawne i łącza ukryte
W arkuszu może istnieć wiele łącz, których nie jesteśmy w stanie łatwo zlokalizować.
Kolejne źródło łącz to ‘nazwy w skoroszycie’. Gdy odwołujemy się do nazwy formułą, np. =netto*2 i jest to łącze w ramach skoroszytu raczej nic nam nie grozi. Ale gdy taki skoroszyt lub tylko arkusz z tzw. ‘nazwą lokalną’ zostanie skopiowany i zapisany pod inną nazwą lub w innej lokalizacji, łącza nadal będą istnieć, ale… tylko w nazwach, tzn. nie będą odnajdywane w arkuszu! Widzieliśmy takie skoroszyty, które posiadały 16.000 łącz zewnętrznych z jednego skoroszytu – 80% było łączami w nazwach, z czego 95% łączami nieaktywnymi (powodującymi błąd #ADR!). Obciążało to bardzo skoroszyt – jednoarkuszowy skoroszyt z formułami na jedną stronę wydruku, zajmował 20MB na dysku, otwierał się kilka minut (komputery w 2002 r. były wolniejsze), a w formułach arkusza nie można było wyszukać łącz do wielu monitowanych skoroszytów (monitowanych o odświeżenie łącz przy otwarciu pliku)
Inną kategorią łącz są łącza „w połączeniach skoroszytu”.
Mogą to być łącza dwojakiego rodzaju:
a) tabela przestawna została sporządzona na danych z innego skoroszytu. Po jego zamknięciu i skasowaniu/zmianie lokalizacji, tabela przestawna ma łącze Excelowe, którego nie potrafi odświeżyć. Można je zmienić tylko poprzez kreatora tabeli, czyli ręcznie (lub przez VBA).
b) łącza ODBC, stworzone poprzez kwerendę MS Query. Gdy stworzymy kwerendę do innego skoroszytu, Excel łącza bezpośrednio nie tworzy – tworzy je kwerenda MS Query, a konkretnie właściwość ‘QueryTable.Connection’. W tym przypadku zmiana jest możliwa tylko poprzez stworzenie kwerendy od nowa (lub przez VBA), a nie zawsze to jest możliwe ze względu np. na jej komplikację lub nieznajomość języka SQL lub struktury bazy danych (również skoroszytowej).
Problem 4. Częsta zmiana środowiska łącz – zależna i niezależna od ich użytkownika
Excel to arkusz kalkulacyjny – narzędzie bardzo wygodne w obsłudze i możliwe do wykorzystania nawet przez mało wprawnych użytkowników. Excel to też jego pliki – setki, jeśli nie tysiące plików, rozsianych po katalogach naszego dysku, naszej sieci lokalnej (w tym dyskach współpracowników), w innych sieciach (np. Internecie). To morze plików, w tym tylko bardzo niewielka ich część zależy od nas – my ustalamy ścieżkę, nazwę, strukturę, zawartość, itp. Reszta zależy od dziesiątek innych osób, ale także od parametrów technicznych – dostępu do Internetu, sieci, dysków współpracowników, itp. Nie jesteśmy w stanie nad tym zapanować. Kolega zmieni, nawet nieznacznie, ścieżkę dostępu, a nam… rozwalają się raporty - giną łącza, zwykle bezpowrotnie.
Ale nam, nawet gdy pracujemy tylko na swoich danych na swoim komputerze, zdarza się to również.
Jak próbuje się zwalczyć problemy?
Sposób 1. „Akceptacja” błędogenności metody, czyli de facto konieczności cyklicznej, dodatkowej, pracy nad zapewnieniem spójności systemu
Jeśli nie potrafimy problemu zwalczyć, bardzo często się do niego „przyzwyczajamy”.
Nie umiemy inaczej, więc walczymy co miesiąc o to samo, sprawdzając kolejne arkusze, a czasami wręcz poszczególne liczby osobiście lub za pomocą setek „kontrolek”. Dużo bezsensownej pracy.
Sposób 2. Próby zastąpienia systemu łącz innymi opcjami Excela – funkcjami wyszukującymi lub automatyzacją przez VBA
To również bardzo popularna metoda. Jeżeli łącza się „sypią”, jeżeli funkcje nie działają (bo łącza się „sypią”), próbuje się operować na danych, a nie na formułach. Ale, żeby wypełnić tysiące komórek, trzeba użyć programu – pisze się go więc w bólach przez lata. Stąd ogłoszenia: „Zatrudnię ANALITYKA – wymagana znajomość Excela i VBA.”
Sposób 3. Bezwzględna dyscyplina systemu
Wydziela się w firmie dysk lub folder, często sieciowy, ale czasem też na dysku komputera biegłego analityka, gdzie tworzy się ścisłą hierarchię skoroszytów, często wręcz „udokumentowaną”, gdzie każdy skoroszyt ma zawsze taką samą nazwę i to samo położenie na dysku. Wszystko po to, żeby system łącz międzyskoroszytowych nie rozsypał się i żeby mieć pewność jego zadziałania w okresie raportowym. Czyli z Excela, systemu z założenia elastycznego, tworzy się sztywną konstrukcję na wzór systemu transakcyjnego (rób tylko to, na co system pozwala). Systemy takie przenosi się czasami do równie sztywnych systemów accessowych – zawsze jednak traci się główną ich zaletę – elastyczność.
Sposób 4. „Unowocześnianie opcji” przez producenta Excela
Problem z łączami w plikach Excela znany jest co najmniej od 15 lat. Jest znany i… już. Okno zarządzania łączami było już w Excelu, bodajże ‘5’. Teraz, oczywiście, jest bogatsze o parę opcji, ale to dalej „okno zarządzania łączami”. I… to wszystko.
Problem parametryzacji łącz lub grupowej zmiany łącz w związku ze zmianą lokalizacji folderów lub grupy skoroszytów – pozostaje nierozwiązany.
Analitycy radzą sobie poprzez użycie narzędzia ‘Edycja/Zamień’, czyli chcąc zmienić łącze, powiedzmy =‘[C:\test\Budżety2009.xls]Arkusz1’!A1 na =‘[C:\test\Budżety2010.xls]Arkusz1’!A1 robią to w każdym skoroszycie oddzielnie (Dobrze, że opcja ta ma możliwość zamiany formuł w całym skoroszycie naraz – inaczej musieliby robić to w każdym arkuszu oddzielnie). Tak, czy inaczej – w którejkolwiek wersji Excela – czeka nas ta sama, powtarzalna, praca.
Sposób 5. Centralizacja systemu = Presja na nowe zakupy
Ponieważ nie ma pomysłu na efektywne rozwiązanie problemu, próbuje się analizę danych... centralizować. Wraz z Office’m 2003 wypuszczony został produkt, umożliwiający Office’ową centralizację w intra- i Internecie – MS Share Point Portal Server. Na pierwszy i nawet drugi rzut oka rozwiązanie ciekawe (choć drogie), szczególnie z korporacyjnego punktu widzenia – rynek jednak wrócił do źródeł – arkuszy lokalnych, łącz, funkcji, komunikacji E‑mailowej.
Tym bardziej, że pewne elementy owej centralizacji (centralna składnica skoroszytów) są już rozwiązywane w darmowych narzędziach: http://www.Google.Docs (Google) , http://workspace.office.live.com (Microsoft) lub w wielu podobnych produktach innych producentów.
Na ich użycie nie godzą się jednak jeszcze korporacyjne działy IT, przewrażliwione w temacie tajemnicy danych (setki megabajtów wymienianej poczty nie przeszkadzają).
Jak powinno się to robić?
Sposób 1. Przeniesienie łącza do funkcji
Jeżeli łącze jest sztywne – trzeba je uelastycznić. Co przeszkadza owej elastyczności – sztywność adresu. Więc należy go umieścić jako argument funkcji – najlepiej tekstowy. Nie daje się? Fakt, skomplikowane, ale jednak możliwe:
http://www.afin.net/AFIN.NET.Light
(Darmowe)
Bez dodatkowego oprogramowania operacja ta się nie powiedzie.
Sposób 2. Wykorzystanie MS Query (standardowe ODBC Excela)
Query ma, w porównaniu do łącz, szereg zalet:
Jest, owszem wolniejsze, ale niewątpliwie efektywniejsze.
Obie metody maja jednak wadę wspólną: sztywne łącze. Jeśli źródłem danych jest plik Excela – w obu przypadkach musi to być cały czas ten sam i taki sam plik.
Źródła danych nie da się parametryzować – to największy problem excelowej implementacji ODBC.
Sposób 3. ADO
Technologia dostępu do baz danych od wielu lat już umożliwia działania na zewnętrznych, zamkniętych plikach Excela. Dlaczego się tego powszechnie nie stosuje?
Trudno dociec, z jakiego powodu, ale ADO w Excelu zostało świadomie zepchnięte na poziom programistyczny.
W Excelu ‘97’ i ‘2000’ (kto to pamięta?) istniała funkcja SqlRequest(ConnStr, SQL) – potem ją skasowano ze standardu Excela, potem, w Excelu ‘XP’, po długich walkach z MS – przywrócono jako dodatek, potem jednak wykasowano na stałe – tylko programiści mogli korzystać z bibliotek ADO.
Ale my też możemy! W dowolnej wersji Excela (od ‘2003’ wzwyż)
http://www.afin.net/excel/ADO4Excel.xls
(Darmowe)
Dodatkowe oprogramowanie posiada odkryty kod.
Sposób 4. Hurtownia danych
Rozwiązanie problemu łącz międzyskoroszytowych jest również, częściowo przynajmniej, możliwe dzięki zmianie architektury pracy analityków, a konkretnie przeniesieniu części procesów informacyjnych na „poziom hurtowni danych”. Nie jest to jednak nigdy rozwiązanie całościowe – powoduje bowiem powstanie innego, jeszcze gorszego z punktu widzenia użytkownika, problemu, a mianowicie ograniczonego dostępu do danych oraz sztywności nowego systemu.
Nie należy wierzyć dostawcom oprogramowania, gdy mówią „nasz system współpracuje z Excelem” – współpraca ta zwykle polega na możliwości „wypchnięcia” do pliku Excela danych przetworzonych i ułożonych w tabelę przez ów system.
Hurtownia staje się więc po prostu kolejnym źródłem danych, a wychodzące z niej eksporty – pliki Excela, identycznym, jak inne, źródłem kolejnych łącz.
AFIN.NET i jego propozycja rozwiązania problemu łącz
Więcej na www.afin.net