Lista rozwijana, tabelki i aparat fotograficzny

by Marcin

Excel - Narzedzie Aparat Fotograficzny i wybieranie tabelek

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:

Excel - Narzedzie kamera i wybieranie tabelek

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.

Po wstawieniu „zdjęcia” klikamy w nie (we wstawiony obrazek)  i w pasku formuły wpisujemy „=WybranaTabelka”. Sprawi to, że nasza „dziura”  nie będzie już tylko dziurą statyczną, ale nabierze nieco dynamiki 😉
Zakres zwracany przez nazwę „WybranaTabelka” zdefiniuje, jaki obszar chcemy przez naszą „dziurę” oglądać (innymi słowy, która tabelka ma zostać pokazana).

Nasza nazwa(„WybranaTabelka”) zdefiniowana jest następująco:

=PRZESUNIĘCIE(Dane!$B$4,Przesuniecie,0,9,8)

Jeżeli nie bardzo rozumiesz jak to wszystko działa zachęcam do poczytania innych moich wpisów. Podobne formuły pojawiają się tam często i na pewno bez większych problemów znajdziesz wyjaśnienie swoich wątpliwości.

I prawdę mówiąc … to tyle. Powinno działać. Dla niedowiarków plik do ściągnięcia.

Download

Marcin

{ 5 comments… read them below or add one }


Fatal error: Cannot assign by reference to overloaded object in /home/klient.dhosting.pl/antiquus/excelblog.pl/public_html/wp-content/themes/thesis_182/lib/classes/comments.php on line 176