Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - 57. Dynamiczne listy rozwijane - trochę inaczej

Maciej Gonet · Excel Expert Wersja: Win Office 365 Posty: 10361

Gdy chcemy zdefiniować listę kontroli poprawności danych w taki sposób, aby do zakresu źródłowego można było dopisywać dane albo je usuwać, określa się to zwykle mianem listy dynamicznej. Mówimy w tym momencie o liście tworzonej ze stałych, a nie za pomocą formuł.
Po przejrzeniu różnych porad dotyczących tego zagadnienia zauważyłem, że dominują propozycje, aby taki zakres definiować za pomocą funkcji INDEKS lub PRZESUNIĘCIE. Idea jest taka, że określamy sobie zakres_max z zapasem, a zakres_listy definiujemy bezpośrednio formułą:

Kod:

=PRZESUNIĘCIE(zakres_max;;;ILE.NIEPUSTYCH(zakres_max))

albo poprzez nazwę:

Kod:

=zakres_listy

W Menedżerze nazw definiujemy

Kod:

zakres_listy : =INDEKS(zakres_max;1):INDEKS(zakres_max;ILE.NIEPUSTYCH(zakres_max))

W tym drugim przypadku trzeba posłużyć się nazwą, bo "Nie można stosować operatorów odwołania (sumy, przecięcia i zakresów) ani stałych tablicowych dla kryteriów Sprawdzanie danych."
Tymczasem zapominamy o innych możliwościach, jakie daje nam Excel. Od wersji 2007 dostępne są tabele strukturalne, które powiększają się dynamicznie w miarę dopisywania danych (albo skracają w miarę ich usuwania). W wersji 2003 dostępne były tzw. listy Excela, które były protoplastą późniejszych tabel. Nic nie stoi na przeszkodzie, aby wykorzystać funkcjonalność tabel lub list Excela do definiowania list sprawdzania poprawności i w konsekwencji list rozwijanych.
Załóżmy, że zakres zawierający dane do listy rozwijanej przekształciliśmy w tabelę, której została automatycznie nadana nazwa Tabela1. Zakres ten należy teraz przypisać do listy kontroli poprawności. Można to zrobić kilkoma sposobami. Nie zadziała tylko sposób najbardziej naturalny - bezpośrednie odwołanie strukturalne:

Kod:

=Tabela1

Ale można posłużyć się odwołaniem pośrednim:

Kod:

=ADR.POŚR("Tabela1")

Można również nadać własną nazwę odwołaniu do tabeli:
w Menedżerze nazw

Kod:

moja_lista : =Tabela1

w definicji listy sprawdzania poprawności

Kod:

=moja_lista

lub

Kod:

=ADR.POŚR("moja_lista")

Kolejne sposoby to odwołanie do adresu zakresu danych tabeli lub nazwy przypisanej do tego zakresu. Ważne, aby wskazać całą kolumnę wchodzącą w skład listy Excela lub tabeli.
w Menedżerze nazw

Kod:

lista_adr : =Arkusz1!$B$2:$B$5

w definicji listy sprawdzania poprawności

Kod:

=Arkusz1!$B$2:$B$5

lub

Kod:

=lista_adr

lub

Kod:

=ADR.POŚR("lista_adr")

Te ostatnie sposoby odnoszą się w równym stopniu do tabel (Excel 2007 i nast.) jak i do list Excela (2003). Listy Excela nie miały własnych nazw predefiniowanych i można było bazować tylko na ich adresach. Zaletą tych wszystkich sposobów jest to, że zmiany zakresów tabel i list są automatycznie odwzorowywane w listach rozwijanych.
Zwracam uwagę, że gdy używamy odwołań pośrednich argumentem może być tylko tekst nazwy, a nie tekst adresu (tekst adresu daje odwołanie statyczne).