Szybki start
Zobacz, jak działają funkcje na Twoich plikach!

(czyli do czego to można użyć i jak używać.)

 

 

AFIN.NET.Light – darmowy dodatek do Excela, oferujący funkcję dostępu do skoroszytów zamkniętych (działa podobnie, jak łącze międzyskoroszytowe, ale bez jego wad) oraz podręczną listę skrótów klawiaturowych Excela.

 

 

Summary in English:

AFIN.NET.Light is a free Add-In for Microsoft Excel. It resolves a huge Excel problem – link errors. The offered functions get data from closed workbooks as a normal Excel function.

Program’s interface is in Polish and in English.

List of Excel shortcuts – press [Ctrl+K]

Download: www.afin.net/excel/AFIN.NET.Light.xla

If you need more help in English, contact us: afin@afin.net

 

 

Jeżeli do tej pory tego nie zrobiłe(a)ś, pobierz i uruchom: www.afin.net/excel/AFIN.NET.Light.xla

 

 

Ćwiczenie 1.

Tworzymy środowisko (skoroszyty) testowe i sprawdzamy działanie funkcji.

 

·        Utwórz 4 niezależne skoroszyty Excela.

 

·        W każdy z nich, niezależnie, w arkuszu o nazwie Arkusz1, wpisz:

o       w skoroszycie Zeszyt1.xls, w arkuszu Arkusz1, w komórki:

§         A1: 1

§         A2: 2

§         A3: 3

o       w skoroszycie Zeszyt2.xls, w arkuszu Arkusz1, w komórki:

§         A1: 10

§         A2: 20

§         A3: 30

o       w skoroszycie Zeszyt3.xls, w arkuszu Arkusz1, w komórki:

§         A1: 100

§         A2: 200

§         A3: 300

o       w skoroszycie Zeszyt4.xls, w arkuszu Arkusz1, w komórki:

§         A1: 1000

§         A2: 2000

§         A3: 3000

 

Powstała następująca konfiguracja wartości w komórkach:

 

·        Zamknij je wszystkie, zapisując na dysku, w wybranym katalogu (tu, dla przykładu, jest to katalog C:\TestAfinNet)

 

·        Utwórz nowy skoroszyt Excela, np. skrótem [Ctrl+N]. W komórce C5 uruchom Kreatora funkcji Excela (menu: Wstaw/Funkcja, ikonka f(x) przy pasku formuły lub skrót [Shift+F3]), a następnie wybierz z listy kategorii funkcji, kategorię AFIN.NET (na końcu listy).

 

 

·        Wpisz funkcję ŁĄCZE2()

ŁĄCZE2() jest funkcją, oferującą funkcjonalność łącza międzyskoroszytowego, tzn. przekazywanie wartości pomiędzy skoroszytami Excela, gdy źródło jest zamknięte. Funkcja ta ma 4 argumenty, zdefiniowane tak, jak przyjęło się używać w określaniu adresu pliku i adresu komórki w skoroszycie. Funkcja ta jest niewątpliwie łatwiejsza do zrozumienia przez początkującego użytkownika niż druga z funkcji, ŁĄCZE(), która wymaga argumentu tekstowego, wpisanego jak pełne łącze międzyskoroszytowe.

Zaletą tej funkcji jest posiadanie zalet łącza, połączone z brakiem wad łącz, tj. wysokiej awaryjności i braku możliwości parametryzowania tekstem.

 

 

 

·        Wpisz przykładowe argumenty funkcji:

Ścieżka: „C:\TestAfinNet”

Skoroszyt: „Zeszyt1.xls”

Arkusz: „Arkusz1”

Adres: „A1” (Pamiętaj o wpisaniu adresu w cudzysłowiu!)


Już podczas prawidłowego wpisania ostatniego argumentu, w kreatorze powinna pojawić się wartość „=1”. Może się to odbyć po chwili „zawieszenia” Excela – dzieje się to tylko jeden raz w sesji Excela, podczas pierwszego uruchomienia funkcji.

 

 

 

·        W arkuszu została obliczona wartość ‘1’. To wartość, wpisana przez nas w skoroszyt Zeszyt1.xls (obecnie jest to plik zamknięty), a my uzyskaliśmy ją funkcją arkuszową, nie tworząc łącza międzyskoroszytowego.

 

 

Co otrzymaliśmy?
Funkcja ŁĄCZE2() pobiera dane z zamkniętych arkuszy Excela bez łącza międzyskoroszytowego, czyli bez wad takiego łącza. Gdy np., zmienimy nazwę katalogu TestAfinNet na np. TestAfinNetGotowe, funkcja, oczywiście, działać przestanie, ale zmiana jej pierwszego argumentu, określającego ścieżkę, na właściwy (podanie właściwej ścieżki), spowoduje ponowne przeliczenie wartości. Łącze międzyskoroszytowe w trakcie takiej operacji zostałoby utracone. Co prawda, można je przedefiniować w oknie zarządzania łączami, ale dotyczy to każdego skoroszytu oddzielnie. Gdy w katalogu-źródle (tym, któremu zmieniliśmy nazwę) jest więcej plików-źródeł łącz, jest to bardzo pracochłonne, a czasami niemożliwe.

 

·        Dla celów dalszych ćwiczeń, zamykamy bieżący skoroszyt, nadając mu nazwę raport1.xls.

 

 

 

 

 

 

Ćwiczenie 2.

Tworzymy raport, a następnie parametryzujemy go wartościami w arkuszu. Stworzymy w ten sposób komunikację z plikami, których nigdy wcześniej nie otwieraliśmy.

 

Aby zrozumieć sens tworzenia takiej, a nie innej, tabeli naszego raportu, wyjaśnijmy najpierw, co, przykładowo, znaczą dla nas, wpisane ręcznie, wartości. Załóżmy, że skoroszyty o nazwach Zeszyt1.xls, Zeszyt2.xls, … , Zeszyt4.xls, są to dane budżetowe z 4 różnych źródeł, np. otrzymane od kierowników MPKów (MPK – miejsce powstawania kosztów), o numerach, odpowiednio: 1,2,3,4.

Pliki te nie były przez nas wsześniej otwierane.

 

Skoroszyty zawierają dane budżetowe poszczególnych wydziałów w zadanym układzie:

W A1 – plan kosztów płac

W A2 – plan kosztów zużycia materiałów

W A3 – plan kosztów usług obcych

W A4 – plan kosztów pozostałych (nie szkodzi, że nic tam, do tej pory, nie wpisaliśmy)

Uproszczenie to ma na celu wyłącznie łatwość przyswojenia przykładu.

Polecamy stworzenie dużo bardziej skomplikowanego modelu wieloarkuszowego.

 

 

 

 

·        W tym ćwiczeniu wykonamy parametryzowanie najprościej, jak można.
Formuła =ŁĄCZE2("C:\TestAfinNet";"Zeszyt1.xls";"Arkusz1";"A1"), wpisana w C5, wymaga podania skoroszytu, który identyfikuje wydział. Parametryzujemy ją.
W C4 wpisujemy tekst parametru Zeszyt.xls, a formułę w C5 modyfikujemy, pobierając argument skoroszyt właśnie z komórki C4.

 

 

      Efekt, tj. wartość w komórce C5, nie powinien się zmienić (=1)

 

 

·        Postępujemy tak z kolejnymi parametrami, dotyczącymi wydziałów, oraz, tym razem w pionie, z parametrami, dotyczącymi poszczególnych pozycji kosztowych (Tu, są to, po prostu, adresy: A1, A2, A3, A4). Przy kopiowaniu formuły można (a właściwie, należy) wykorzystać blokowanie adresów w komórkach – stąd znaki ‘$’ w adresach.
Wiersz i kolumnę parametrów sformatowano na żółto.

 

 

 

Co otrzymaliśmy?

Budując jedno (!) zapytanie funkcją ŁĄCZE2, otrzymaliśmy wzorzec formuły, którą wykorzystaliśmy w całym raporcie. Zwróćmy uwagę na fakt, że plików Zeszyt2.xls, Zeszyt3.xls, Zeszyt4.xls nie otwieraliśmy nigdy wcześniej, a, pomimo to, wyciągnęliśmy z nich dane. Aby efekt taki otrzymać w „czystym” Excelu, należałoby ręcznie zmodyfikować każdą z 16 formuł. A to, przecież, tylko prosty przykład – nasze raporty sa o wiele bardziej rozbudowane.
Wiersz, oznaczony jako „inne”, zwraca zera, gdyż, w plikach źródłowych, nic w A4 nie wpisywaliśmy. Możemy więc teraz otworzyć jeden ze skoroszytów źródłowych, wpisać coś w A4, zamknąć plik źródłowy i zobaczyć efekt w naszym raporcie. Wymaga to przeliczenia arkusza, np. przez [F9] lub, gdy stosujemy przeliczanie automatyczne, poprzez wpisanie czegokolwiek w którąkolwiek z komórek.

 

Kolejna taka zmiana wartości w danej komórce może już się nie udać. Łącza, a konkretnie przekazywane wartości, „zapamiętują się” po pierwszym pobraniu.

W AFIN.NET.Light jest na to rada – wystarczy uruchomić odpowiednią opcję pod prawym przyciskiem myszy, która kasuje bufor łącz:

AFIN.NET…/Funkcja ŁĄCZE()…/Odśwież środowisko łącz (gdy się nie aktualizują).

 

·        Gotowe.

 

Ćwiczenie 3.

Wykonujemy to samo ćwiczenie, wykorzystując funkcję ŁĄCZE() i parametryzując formułę „Pomocnikiem parametryzowania formuł”.

 

·        Skasuj formuły z obszaru C5:F8.

 

·        Otwórz plik Zeszyt1.xls z katalogu C:\TestAfinNet.

 

·        Ustaw kursor w komórce C5 (w raport1.xls, Arkusz1) i wpisz formułę łącza międzyskoroszytowego, czyli ‘=’ i wskaż myszą adres A1 w skoroszycie Zeszyt1.xls, w arkuszu Arkusz1 (Jest tam wartość 1).

           

            Po zaakceptowaniu formuły, w komórce powinna pojawić się wartość 1

·        Zamknij plik Zeszyt1.xls. Możesz zapisać zmiany lub nie – nie ma to znaczenia, gdyż informacja o powstałym łączu przechowywana jest wyłącznie w pliku raport1.xls.

·        Pod prawym przyciskiem myszy, ustawionej na C5, znajdziesz nowe opcje, dostarczone przez AFIN.NET.Light. Wybierz:
AFIN.NET…/Funkcja ŁĄCZE()…/Zamień łącze bezpośrednie na funkcję ŁĄCZE()


 

Formuła, czyli łącze bezpośrednie:
='C:\TestAfinNet\[Zeszyt1.xls]Arkusz1'!A1
została zamieniona na funkcję:
=ŁĄCZE("'C:\TestAfinNet\[Zeszyt1.xls]Arkusz1'!A1")

Działa dokładnie tak samo, jednak funkcja nie tworzy łącza. Można to sprawdzić:
Menu Edycja/Łącza… . Plik raport1.xls posiada łącze wyłącznie do pliku dodatku, tj. pliku AFIN.NET.Light.xla

·        Parametryzujemy formułę.
Z menu pod prawym przyciskiem myszy, wybieramy AFIN.NET…/Parametryzowanie…/Parametryzuj (opcja dostępna również pod skrótem [Ctrl+F2])



Pojawia się okno parametryzowania formuły. Przesuwamy je w wygodne miejsce.

     

 

Teraz, kolejnymi krokami, musimy doprowadzić do sytuacji, gdzie w tekście naszej formuły – w adresie łącza, pojawią się parametry z nagłówków tabeli (żółte pola).

 

·        Zaznaczamy pierwszy element do sparametryzowania

 

 

Klikamy Parametryzuj zaznaczenie. Następnie, w oknie Wskaż komórkę na arkuszu  wskazujemy adres na arkuszu, gdzie uprzednio wpisaliśmy żądany parametr, tj. tekst Zeszyt1.xls.

 

Program domyśla się, że jest to nagłówek kolumny (adres jest w tej samej kolumnie, ponad komórką parametryzowaną), wyświetlając pytanie „Czy jest to nagłówek kolumny? Odpowiadamy: „TAK”.

 

Otrzymujemy następujący efekt:

 

·        Zaznaczamy teraz precyzyjnie (ani jednego znaku więcej i ani jednego mniej) tekst A1 i powtarzamy czynności parametryzowania formuły (poprzedni punkt), odnosząc się tym razem do nagłówka wiersza, tj. komórki B5 (tam jest tekst – adres komórki A1).

 

Efekt kolejnej parametryzacji:

 

 

·        Klikamy Zakończ (Wpisz do komórki)

 

Efekt: Sparametryzowana formuła.

 

 

·        Rozprzestrzeniamy formułę na cały raport.

 

Możemy, co prawda, zrobić to zwykłym kopiowaniem (ciągnięciem rogu formuły w prawo i w dół), ale i tu mamy pewne ułatwienie.

 

 

Otrzymujemy efekt, podobny do efektu ćwiczenia 2. Tu jednak, nie dotknęliśmy klawiatury ani razu w całym procesie tworzenia raportu.

 

 

·        Gotowe.

 

 

 

Ogólna informacja o dodatku.

 

Dodatek AFIN.NET.Light.xla służy do eliminacji problemów z łączami międzyskoroszytowymi do plików zamkniętych

 

Dostarcza również krótkiej pomocy – listy skrótów klawiszowych Excela, dostępnej w każdej chwili, w wygodnej formie okna dialogowgo.

 

Uruchomienie: Z menu AFIN.NET lub skrótem [Ctrl+k]

 

 

 

Co znajduje się w pełnej wersji dodatku AFIN.NET?

 

Dodatek AFIN.NET, w wersji komercyjnej, oferuje o wiele większe możliwości.

Lista funkcjonalności AFIN.NET obejmuje zarówno funkcje arkuszowe, jak i procedury ułatwiające pracę. Zawiera również wiele opcji dodatkowych, usprawniających pracę w Excelu: opcję publikacji, archiwum wartości, a także Analizy. Analiza to nowy obiekt, jakby ponad skoroszytem, ułatwiający pracę analityka. Grupuje skoroszyty w większe całości (grupy) – pozwala je tym samym grupowo otwierać, przeliczać i zamykać, również zdalnie i w trybie wsadowym.

Pełna lista funkcjonalności: www.afin.net