Wprowadzenie do zakresów dynamicznych
Funkcja WYSZUKAJ.PIONOWO jest często używana do wyszukiwania informacji przechowywanych w tabelach w programie Excel. Na przykład, jeśli mamy listę nazwisk i wieku osób:
A potem możemy w pobliskiej komórce użyć funkcji WYSZUKAJ.PIONOWO, aby określić wiek Paula:
Jak dotąd jest to dosyć standard. Ale co się stanie, jeśli będziemy musieli dodać więcej nazwisk do listy? Oczywistą myślą byłoby zmodyfikowanie zakresu w funkcji WYSZUKAJ.PIONOWO. Jednak w naprawdę złożonym modelu może istnieć kilka odniesień do funkcji WYSZUKAJ.PIONOWO. Oznacza to, że musielibyśmy zmienić każdą referencję – zakładając, że wiedzielibyśmy, gdzie one są.
Jednak Excel oferuje alternatywny sposób - zwany gamą DYNAMICZNĄ. Jest to zakres, który automatycznie rozszerza aktualizacje. Jest to idealne rozwiązanie, jeśli Twoje listy ciągle się powiększają (np. dane sprzedaży z miesiąca na miesiąc).
Aby skonfigurować zakres dynamiczny, musimy mieć nazwę zakresu – dlatego nazwiemy ją AGE_DATA. Podejście do konfigurowania zakresów dynamicznych różni się w programie Excel 2007 i wcześniejszych wersjach programu Excel:
W programie Excel 2007 kliknij „Zdefiniuj nazwę” pod formułami:
We wcześniejszych wersjach programu Excel kliknij „Wstaw”, a następnie Nazwy” .
W wyskakującym okienku wpisz nazwę naszego zakresu dynamicznego – czyli „DANE WIEKOWE”:
W polu oznaczonym „Refers To” musimy wpisać zakres naszych danych. Zostanie to osiągnięte przez funkcję OFFSET. To ma 5 argumentów:
=PRZESUNIĘCIE(Odniesienie; Rzędy; Kolumny; Wysokość; Szerokość)
- Referencja to adres LEWY GÓRNY róg naszego asortymentu - w tym przypadku komórka B5
- Wiersze to liczba wierszy od GÓRNEGO LEWEGO, jaką chcemy, aby ten zakres był - co w tym przypadku będzie równe 0
- Cols to liczba wierszy od LEWEJ GÓRY, jaką chcemy, aby ten zakres był - co w tym przypadku będzie równe 0
- Wysokość zakresu - patrz poniżej
- Szerokość zakresu - to jest 2, mamy w naszym zakresie DWIE kolumny (imię i nazwisko osoby oraz wiek)
Teraz wysokość zakresu będzie musiała się różnić w zależności od liczby wpisów w naszej tabeli (obecnie 7).
Oczywiście chcemy mieć sposób zliczania wierszy w naszej tabeli, który aktualizuje się automatycznie - więc jednym ze sposobów na to jest użycie funkcji ILE.NIEPUSTYCH. To po prostu zlicza liczbę niepustych komórek w zakresie. Ponieważ nasze nazwiska znajdują się w kolumnie B, liczba wpisów w naszych danych to COUNTA(B:B).
Zauważ, że jeśli umieścisz to w komórce, otrzymasz wartość 8 - ponieważ zawiera nagłówek Names. Jednak to jest nieistotne.
Tak więc w polu „Odnosi się do” umieszczamy:
=PRZESUNIĘCIE($B$5,0,0;liczba(B:B);2)
I kliknij przycisk OK. Nasz zakres dynamiczny został stworzony.
Teraz wróć do formuł WYSZUKAJ.PIONOWO i zastąp zakres $B:4:$C11 nazwą naszego nowego zakresu dynamicznego AGE_DATA, więc mamy:
Jak dotąd nic się nie zmieniło. Jeśli jednak dodamy do naszej tabeli jeszcze kilka nazw:
A w komórce, w której mieliśmy Paula, zamień ją na nowe imię, takie jak Pedro (którego nie było na oryginalnej liście):
Widzimy, że Excel automatycznie zwrócił wiek Pedro - mimo że nie zmieniliśmy formuły WYSZUKAJ.PIONOWO. Zamiast tego rozszerzono zakres dynamiczny o dodatkowe nazwy.
Zakresy dynamiczne są bardzo przydatne, gdy mamy coraz większe ilości danych - zwłaszcza, gdy wymagane są tabele WYSZUKAJ.PIONOWO i PRZESTAWNE.
