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