Niedawno Zbyszek zapisał się na newsletter i przy okazji zadał ciekawe pytanie: jak wyświetlić wartość z określonej kolumny, na podstawie jej nazwy (w nagłówku)? Myślę, że odpowiedź na to pytanie zaciekawi wieeelu z Was, dlatego postanowiłam napisać o tym artykuł (i nagrać filmik – pod wpisem). Przykład z życia wzięty dopasowałam do tego taki:

Formatka

Jest to tabelka pokazująca ceny za m2 mieszkań znajdujących się na określonym piętrze i o określonym metrażu. Metraż mamy w kolumnach, piętra – w wierszach. W żółtych polach obok każdego piętra chcemy wybrać metraż z listy rozwijanej i na tej podstawie ma nam się wyświetlić cena za m2 (w kolumnie Wartość). To jest zadanie na dziś i jednocześnie klasyczny przykład wykorzystania funkcji INDEKS i PODAJ.POZYCJĘ. Można byłoby tutaj wykorzystać też WYSZUKAJ.POZIOMO z funkcją PODAJ.POZYCJĘ (pod koniec wpisu też to pokazuję).

Wybór metrażu – lista rozwijana

Aby stworzyć listę rozwijaną w żółtych komórkach na formatce najpierw je zaznacz. Następnie Wejdź do menu Dane/ Poprawność danych. Pokaże się następujące okienko, w którym należy zaznaczyć to, co na obrazku poniżej:

Lista rozwijana – ustawienia

Czyli jako dozwolony wybieramy Lista, a jako zakres listy podajemy zakres nagłówków z metrażem – $D$3:$H$3.

Po zatwierdzeniu OK ta łatwiejsza część zadania za nami. 😉 Oczywiście pamiętajcie, że lista rozwijana to głównie bajerek, aczkolwiek jeśli z naszego pliku ma korzystać ktoś jeszcze, to trzeba się liczyć z tym, że jego pomysłowość może być nieograniczona (i zazwyczaj jest;)). Dlatego lepiej ograniczyć użytkownikowi pole manewru, do czego lista rozwijana się świetnie nadaje. User będzie mógł wybrać tylko określone wartości, a nie np. 46,89 m2…

Oczywiście wartość metrażu można równie dobrze wpisać z palca.

Przyporządkowanie ceny za m2 –  Formuła

Zabieramy się za formułę. Składa się ona z dwóch funkcji: INDEKS i PODAJ POZYCJĘ. Pierwsza określa zakres, z którego mają być pobierane wartości, a druga – pozycję, na której znajduje się wartość do pobrania. Zauważcie, że wartość do pobrania „na odpowiednim piętrze”, czyli w odpowiednim wierszu, będzie dokładnie na tej samej pozycji, co wybrany przez użytkownika metraż w nagłówku. Właśnie te dwie rzeczy wykorzystuje poniższa formuła, którą należy wpisać do kolumny Wartość:

=INDEKS(D4:H4;1;PODAJ.POZYCJĘ(B4;$D$3:$H$3;0))

Problem z nią jest tylko taki, że – jak nie wpiszemy do żółtych komórek wartości – to jej wynik będzie… taki:

Formuła zwracająca błędy

Oczywiście funkcja działa poprawnie w każdym przypadku, tylko wygląda nieelegancko. Tam, gdzie w żółtej komórce nie ma wartości, zwraca błąd #N/D!. Dzieje się tak dlatego, że nie ma zerowego metrażu, a taki właśnie każemy wtedy funkcji szukać. Wynik prawidłowy, ale brzydki. Poprawmy go więc za pomocą JEŻELI.BŁĄD i np. nie wyświetlajmy nic w przypadku, gdy użytkownik nie wpisze wartości do żółtej komórki. Formuła będzie taka:

=JEŻELI.BŁĄD(INDEKS(D4:H4;1;PODAJ.POZYCJĘ(B4;$D$3:$H$3;0));"")

Efekt otrzymujemy następujący:

Oczywiście można dyskutować, czy nic nie wyświetlać (czyli w funkcji wpisać: „”, co w rzeczywistości jest tekstem) czy np. zero. Oczywiście wszystko zależy. Jeśli nic dalej z tym nie będziemy robić, czyli nie wykorzystamy do dalszych przeliczeń, myślę, że ok jest zostawienie tego postaci podanej przeze mnie. Jeśli natomiast wyniku będziemy potrzebowali do dalszych obliczeń, to wolałabym, aby formuła wyświetlała zero. Zawsze można je przecież ukryć za pomocą odpowiedniego formatowania niestandardowego, które zera po prostu nie wyświetli. Jak zwykle wszystko zależy. 🙂

Wersja formuły z WYSZUKAJ.POZIOMO

I na koniec obiecana wersja formuły. Ma ona też tę wadę, że trzeba w niej zagnieździć kolejną funkcję WIERSZ. No ale działa. 🙂

=JEŻELI.BŁĄD(WYSZUKAJ.POZIOMO(B4;$D$3:$H$9;WIERSZ()-2;0);"")

Dajcie znać w komentarzach, do czego jeszcze może Wam się przydać odszukiwanie konkretnej wartości z tabeli na podstawie nazwy nagłówka jednej z kolumn. Domyślam się, że zastosowań jest dużo. 🙂

I wersja wideo: