Wyszukiwanie w tablicy dwuwymiarowej

Witam,
Dzisiaj pokażę jak wyszukiwać dane w tablicy dwuwymiarowej, czyli jak znaleźć komórkę na przecięciu szukanej kolumny i wiersza. Zajmiemy się dwoma problemami, kiedy nagłówki i wiersze są unikalne, oraz kiedy się powtarzają. Za pomocą wcześniejszej metody, możemy wyszukiwać dowolną wartość, także tekst. Kolejna nadaje się tylko do tabel zawierających liczby.

Przeszukiwana tabela, pierwszym wymiarem są kolory a drugim imiona


Plik, do tego przykładu jest dostępny tutaj.

W obu poniższych przypadkach będziemy starali się znaleźć wszystkie liczby w tabelce, znajdujące się na przecięciu wiersza Agata, oraz kolumny Czerwony.

Metoda pierwsza – kolumny i wiersze nie powtarzają się

W tym przypadku zarówno nagłówki kolumn, jak i wierszy są znormalizowane, znaczy to tylko tyle, że nie ma dwóch takich samych nagłówków wierszy ani dwóch takich samych nagłówków kolumn. W naszym konkretnym przykładzie w kolumnie A nie ma dwóch takich samych imion, a w wierszu 1 nie ma dwóch takich samych kolorów.

Oznacza to, że poszukując naszej wartości poszukujemy tylko jednej komórki. Posłużymy tutaj się funkcją Indeks.

=INDEKS(B2:F6;x;y)

Funkcja ta zwraca nam wartość komórki, znajdującej się w x wierszu i y kolumnie tablicy B2:F6. Musimy jeszcze tylko obliczyć, x i y. X to po prostu wiersz w którym znajduje się Agata, a y to kolumna w której mamy słowo Czerwony. Posłużymy się tutaj funkcją PODAJ.POZYCJE.

=PODAJ.POZYCJĘ(„Agata”;A2:A6;0) (zwróci nam x)
=PODAJ.POZYCJĘ(„Czerwony”;B1:F11;0) (zwróci nam y)

Pierwszy argument funkcji to wartość, której szukamy, drugi oznacza gdzie należy tego szukać (są to odpowiednio nagłówki wierszy i kolumn). Ciekawym, dlaczego ostatni argument, jest równy zero, polecam pomoc Excel.

Zamienię teraz jeszcze szukane słowa na adresy komórek, i połączę wszystkie funkcje w jedną:

=INDEKS(B2:F6;PODAJ.POZYCJĘ(A12;A2:A6);PODAJ.POZYCJĘ(B12;B1:F1))

Funkcja zwróci nam 115,31. Metoda ta nie używa działań matematycznych, można więc wyszukiwać dowolne ciągi znaków, niekoniecznie cyfry.

Metoda druga – występują powtarzające się kolumny lub wiersze

Drugi przykład jest równie prosty. Będę używał w nim działań matematycznych, nadaję się więc tylko do tabel zawierających liczby. Zamieńmy nagłówek Zielony na Czerwony. Oraz Janek na Agata. wcześniejsza metoda już nie będzie działać, poszukujemy teraz kilku komórek.

Posłużymy się więc poniższą funkcją:

=SUMA.ILOCZYNÓW((A2:A6=”Agata”)*(B1:F1=”Czerwony”)*B2:F6)

Obszerny opis tej funkcji znajduje się w pomocy do programu. W skrócie, powyższa formuła porównuje każdą kombinację nagłówka wiersza i kolumny z odpowiednimi wartościami. Jeżeli nagłówki są odpowiednio równy Agata lub Czerwony to mnożenie  ( A2:A6 =”Agata”)*( B1:F1 =”Czerwony”)  przyjmuje wartość  1 i mnożąc to przez odpowiednią wartość z zakresu B2:F6 otrzymujemy część wyniku formuły. Za każdym razem, kiedy nagłówki mają odpowiednią wartość, do sumy dodawana jest zawartość komórki.

Subskrybuj RSS, aby otrzymywać najnowsze informacje.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *