Lista rozwijana, tabelki i aparat fotograficzny

Dzisiaj o tym, w jaki sposób dodać do swojego arkusza możliwość wyboru i wyświetlenia jednej z wielu tabel (zakresów) znajdujących się w innym skoroszycie. Zadanie niezbyt skomplikowane, ma jednak tę zaletę, że będziemy korzystać z kilku ciekawych i bardzo użytecznych technik (funkcji Excela). Będzię to wspomniane już w tytule narzędzie Aparat Fotograficzny, o którym pisałem jakiś czas temu, sprawdzanie poprawności, zdefiniowane nazwy, funkcja PRZESUNIĘCIE oraz  dynamiczne zakresy. Innymi słowy świetny materiał dla wszystkich tych, którzy chcą swoją wiedzę o Excelu poszerzyć. Do roboty więc ….

Mamy sobie listę dat i przypisaną do każdej daty tabelkę. Wygląda to mniej więcej tak:

Chcemy teraz dodać listę rozwijaną ze wszystkimi datami (idealnie gdyby lista ta aktualizowała się automatycznie po dodaniu nowej daty), która umożliwiać będzie wybór daty i wyświetlenie odpowiedniej (tylko jednej) tabelki. Jak spomniałem, wykorzystamy dzisiaj narzędzie Aparat Fotograficzny. Nie jest to rozwiązanie idealne. Pomimo wielu zalet, problem z aparatem fotograficznym jest taki, że od czasu do czasu „kopiowane” obrazy bywają nieco zniekształcone. Pomaga wtedy wybór nieco innego zakresu źródłowego, lub esperymentowanie z rozmiarem docelowego obrazka. W każdym razie można to wszystko poustawiać tak, aby wyglądało ładnie.

Pierwsza rzecz to stworzenie listy rozwijanej. Tu sprawa jest prosta. Korzystamy ze sprawdzania poprawności i jako dozwolone kryteria poprawności wybieramy „lista”, a w polu „źródło” odwołamy się do zdefiniowanego zakresu . Cała sztuczka polega teraz na stworzeniu odpowiedniego, samoaktualizującego się zakresu, wypełnionego wszystkimi datami. Wydaje mi się, że nie uda się tego zrobić  bez wykorzystania kolumny pomocniczej.

Nasza pomocnicza kolumna wygląda tak:

a magiczna formuła wykonująca całą brudną robotę  (automatycznie aktualizuje naszą listę o nowe daty, które umieszcza na górze listy – reszta wypełniona jest komunikatami błędu) tak:

{=MAX.K(Zakres_z_Datami,WIERSZ(ADR.POŚR("1:"&ILE.WIERSZY(Zakres_z_Datami))))}

Tak, tak – jest to formuła tablicowa i jeżeli nie wiesz jak one działają, a czujesz teraz gwałtowną potrzebę zdobycia tej wiedzy zapraszam do jednego z moich wpisów gdzie wyjaśniam cały sekret formuł tablicowych w Excelu.

Wszystko czego potrzebujemy teraz to zakres ograniczający naszą kolumnę pomocniczą tylko do dat (wykluczamy wszystkie błędy). Efekt osiągamy przy pomocy formuły:

=PRZESUNIĘCIE(Dane!$K$6,0,0,ILE.NIEPUSTYCH(Dane!$B:$B),1)

Jak widać na poniższym obrazki, zdefiniowana nazwa (używa wcześniej w Sprawdzaniu poprawności „ListaDat”) definiuje dokłądnie taki zakres jakiego oczekiwaliśmy.

Mamy więc już listę rozwijaną z naszymi datami, pozostaje sprawić, aby zmiana daty powodowała wyświetlenie odpowiedniej tabelki. Zakładam, że po przeczytaniu mojego artykułu o narzędziu aparat fotograficzny wiesz już doskonale jak zrobić „zdjęcie” jednej z tabelek (na wszelki wypadek, krótka podpowiedź poniżej). Robimy więc takie zdjęcie i wstawiamy je tuż pod naszą listą wyboru.

Czas na ostatni krok.

Marcin

Tagged as: Adr.pośr, dynamiczne zakresy, Formuła tablicowa, Indeks, lista rozwijana, Podaj.Pozycję, Przesunięcie, Wiersz