Przeszukiwanie tabelki i nagłówek kolumny ze znalezioną wartością.

Dzisiaj małe ćwiczenie, które pokazuje jak w Excelu ten sam efekt można osiągnąć przy wykorzystaniu zupełnie różnych funkcji.

Mamy więc sobie tabelkę i chcielibyśmy, aby formuła Excela odpowiedziała nam na pytanie, w której kolumnie (lub wierszu, w naszym przykładzie używam jednak kolumn) znajduje się poszukiwane słowo. Myślę, że animacja powyżej dokładnie pokazuje problem.

Pierwsze co przychodzi do głowy w tego rodzaju sytuacjach, to odczytać po kolei (przy pomocy jakigoś rodzaju pętli) każdą komórkę z naszej tabelki i porównać z poszukiwanym słowem. Jeżeli są sobie równe to wyświetlamy nazwę odpowiedniej kolumny.

Kiedy myślę o pętlach w Excelu (wyłączając oczywiście VBA, bo tam sprawa jest o wiele prostsza) to do głowy przychodzą mi tylko:

• obliczenia iteracyjne  (o których nie tak dawno pisałem, przy okazji omawiania „timestamps”czyli sposobu na datowanie zmian wprowadzanych do komórki) oraz
• formuły tablicowe (lub fukcje takie jak SUMA.ILOCZYNÓW, które operują na tablicach, pomimo, że nie wprowadzamy ich tak jak klasycznych formuł tablicowych przy pomocy Ctr+Shift+Enter).

Obliczenia iteracyjne  zostawiamy na boku i skupimy się znalezieniu rozwiązania opartego na formułach tablicowych ().

Pierwsze rozwiązanie wygląda następująco:

{=INDEKS(6:6,SUMA(($B$7:$E$14=G7)*NR.KOLUMNY($B$7:$E$14)))}

Ponieważ jestem dzisiaj w nieco gawędziarskim nastroju (a i zależy mi na tym, aby czytelnicy mojego bloga coraz lepiej rozumieli sposób działania formuł Excela), przyjrzymy sie bliżej sposobowi w jaki ta formuła działa (a działa tak tylko dlatego, że została prowadzona jako formuła tablicowa).

($B$7:$E$14=G7)

ten fragment, porówna wartość każdej komórki tabelki z poszukiwanym słowem i utworzy w pamięci komputera tablicę o wymiarach równych wymiarom naszej tabelki, wypełnioną zerami ( w zasadzie wartościami FAŁSZ, ale za chwilę wartość tę będziemy mnożyć i Excel automatycznie zamieni to na zero. Dla uproszczenia więc od razu piszę o zerach) wszędzie tam gdzie wartość w tabelce nie jest równa szukanemu słowu. Jak łatwo zgadnąć, w przypadku odnalezienia szukanego słowa, w pamięcu komputera w odpowiednim miejscu znajdzie się cyfra 1.

NR.KOLUMNY($B$7:$E$14)

W tym samym czasie, tworzona jest w pamięci komputera inna tablica (o tym samych wymiarach, równych wymiarom naszej źródłowej tabelki) zawierająca numery kolumn, dla każdej z komórej naszej tabelki. Następnie,  jedna tablica zostaje pomnożona przez druga. Wygląda to mniej więcej tak jak na rysunku poniżej (przykłąd, dla szukanego wyrażenia „Spacer w parku”).

W rezultacie otrzymujemy tablicę z zerami wszędzie tam, gdzie wartość w naszej tabelce nie jest równa poszukiwanemu wyrażeniu, oraz numer kolumny tam gdzie odpowiednia wartość została odnaleziona.

SUMA(($B$7:$E$14=G7)*NR.KOLUMNY($B$7:$E$14))

Na koniec wszystkie wartość z naszej tablicy zostają zsumowane (w naszym przykładzie jako wynik otrzymamy “3” czyli numer kolumny, w której znajduje się poszukiwane przez nas wyrażenie). Reszta to poprostu wybranie i pokazanie wartości z wiersza nagłowka, co robimy przy pomocy funkcji INDEKS.

Zamiast formuły tablicowej możemy skorzystać z formuły opartej na funkcji SUMA.ILOCZYNÓW.

=INDEKS(B6:E6,SUMA.ILOCZYNÓW(($B$7:$B$14=G8)*1+($C$7:$C$14=G8)*2+($D$7:$D$14=G8)*3+($E$7:$E$14=G8)*4))

Sposob działania jest w zasadzie dokładnie taki sam jak w pierwszym przypadku. Korzystamy tu po prostu z innej funkcji Excela i całość nie musi być zatwierdzana tablicowo (Ctr+Shift+Enter). Tym razem tworzymy jednak w pamięci komputera tyle tablic, ile mamy kolumn w naszej tabelce. Za każym razem w takiej pojedynczej tablicy znadują się zera wszędzie tam, gdzie poszukiwana wartość nie została odnaleziona. Zamiast jednak korzystać tu z funkcji Nr.Kolumny wymnarzamy każdą taką tymczasową tablicę przez wpisany ręcznie numer kolumny.

Trzeci przykład to także formuła tablicowa.

=INDEKS(6:6,SUMA(JEŻELI($B$7:$E$14=G9,NR.KOLUMNY($B$7:$E$14),0)))

Jest to dokładnie to samo co w rozwiązaniu pierwszym, tylko zapis jest nieco inny. Polecam samemu sobie porównać te formuły w ramach ćwiczenia.

Wreszcie na koniec, nieco inne (nietablicowe) podejście. Z reguły, za każdym razem kiedy mam czegoś szukać w Excelu do głowy zaraz przychodzi mi para funkcji INDEKS i PODAJ.POZYCJĘ. Więc i tym razem chciałem spróbować je wykorzystać. Rezultat poszukiwań wygląda tak:

=INDEKS(B6:E6,1*NIE(CZY.BŁĄD(PODAJ.POZYCJĘ(G10,B7:B14,0))) +2*NIE(CZY.BŁĄD(PODAJ.POZYCJĘ(G10,C7:C14,0)))+3*NIE(CZY.BŁĄD(PODAJ.POZYCJĘ(G10,D7:D14,0)))+3*NIE(CZY.BŁĄD(PODAJ.POZYCJĘ(G10,E7:E14,0))))

Formuła zbudowana jest z bloków o, mniej więcej, następującym wyglądzie

1*NIE(CZY.BŁĄD(PODAJ.POZYCJĘ(G10,B7:B14,0)))

Zmieniają się zaznaczone fragmenty. Dla każdej kolumny, przy pomocy funkcji PODAJ.POZYCJĘ, sprawdzamy czy uda nam się odnaleźć w kolumnie poszukiwane wyrażenie. Jeżeli nie, to funkcja zwraca błąd. Przy pomocy funkcji CZY.BŁĄD sprawdzamy więc czy PODAJ.POZYCJĘ zwróciła błąd. Jeżeli tak, to otrzymujemy wartość PRAWDA. Zamieniamy ją szybko na FAŁSZ  i mnożymy przez, wprowadzony ręcznie, numer kolumny (wartośc PRAWDA zamieniamy na FAŁSZ dlatego, że nie interesuje nas błąd zwracany przez funkcję PODAJ.POZYCJĘ. Szukamy przecież wariantu (kolumny), w którym formuła nie zwróci błędu. Oznaczać to bowiem będzie, że poszukiwana wartość została odnaleziona). Trochę zagmatwałem, ale pewien jestem, że analiza formuły pozwoli Wam szybko zrozumiec mój tok rozumowania.

Plik z przykładem, jak zwykle, do ściągnięcia dla zalogowanych użytkowników.

Marcin

Tagged as: Formuła tablicowa, Indeks, Podaj.Pozycję, suma.iloczynów