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
{ 2 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