Excel w praktyce

Jeżeli korzystasz z danych źródłowych, które są na bieżąco uzupełniane, powinieneś stosować dynamiczne odwołania. Dzięki nim każda wartość dopisana w tabeli źródłowej zostanie automatycznie uwzględniona we właściwych raportach czy rozwijanych listach.

W poprzednim wydaniu e-lettera (kliknij, aby go przeczytać) udostępniłem Ci darmowy szablonu planu lekcji, w którym zastosowałem pewne usprawnienie. Były to rozwijane listy dla których dane były pobierane z innego arkusza za pomocą dynamicznego odwołania.

Otrzymałem wiadomości od Czytelników e-lettera z pytaniem: jak się tworzy takie odwołania? Przygotowałem instrukcję krok po kroku. Jest ona ściśle powiązana z szablonem udostępnionym w poprzednim wydaniu e-lettera.

Pierwszy etap to zdefiniowanie nazwy i podczepienie do niej formuły. To właśnie formuła określi, do jakiego zakresu odniesie się nazwa.

1. Z menu Wstaw wybierz polecenie Nazwa/Definiuj (w Excelu 2007: uaktywnij kartę Formuły i w grupie poleceń Nazwy zdefiniowane kliknij Definiuj nazwę).
2. W polu nazwy wpisz łatwo rozpoznawalną nazwę, np. przedmiot.
3. W polu Odwołuje się do wprowadź następującą formułę:

=PRZESUNIĘCIE(Przedmioty!$A$2;0;0;
ILE.NIEPUSTYCH(Przedmioty!$A:$A);1)

Wyjaśnienie działania formuły:
Trzon formuły stanowi funkcja PRZESUNIĘCIE, która zwraca odwołanie do zakresu oddalonego o określoną liczbę kolumn lub wierszy od komórki lub zakresu bazowego.

W naszym przykładzie komórką bazową jest A2, która znajduje się w arkuszu o nazwie Przedmioty (Przedmioty!$A$2). Twoim zadaniem nie jest wykonanie przesunięcia, a jedynie odnalezienie obszaru arkusza, który się stale zmienia. W drugim i trzecim argumencie wpisano zatem wartość zerową.

Wysokość i szerokość owego obszaru są określane w czwartym i piątym argumencie funkcji PRZESUNIĘCIE. Do wyznaczenia wysokości obszaru wykorzystano funkcję ILE.NIEPUSTYCH zwracającą liczbę komórek zawierających jakiś wpis w całej kolumnie A [ILE.NIEPUSTYCH(Przedmioty!$A:$A)]. Szerokość obszaru jest stała i w naszym przykładzie obejmuje 1 kolumnę.

Podsumowując: pierwszy argument określa lewy górny róg (początek) obszaru danych źródłowych; czwarty i piąty jego wysokość oraz szerokość. Funkcja ILE.NIEPUSTYCH będzie na bieżąco sprawdzać liczbę wypełnionych komórek na liście źródłowej i podawać aktualną wysokość zakresu zwracanego przez funkcję PRZESUNIĘCIE.

Tak zdefiniowaną nazwę podajesz jako źródło danych, np. przy tworzeniu rozwijanej listy czy tabeli przestawnej.