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.

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.

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ą:

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ą:

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.

Comments 10

    1. Post
      Author
    2. Post
      Author

      Zaktualizowałem co nieco. Okazało się, że było tego trochę. Strona powinna już działać lepiej. Będę wdzięczny za komentarz.

  1. Wprowadzacie czytelników w błąd. Opisywany przykład jest błędnie zatytułowany i NIE podaje rozwiązania problemu wyszukiwania danych w tablicy dwuwymiarowej. Wyszukiwanie odbywa się w tablicach JEDNOWYMIAROWYCH. Na podstawie tych wyszukiwań WYBIERANY jest element z tablicy dwuwymiarowej. Obiektem wyszukiwania NIE SĄ dane z tablicy dwuwymiarowej, a jedynie nagłówki (poziome i pionowe) tej tablicy.

    1. Post
      Author

      Dziękuję za uwagę. Miło czasem usłyszeć twórczą krytykę zamiast pochwał. Powyższa metoda wyszukuje wartości w dwóch wymiarach i zwraca wartość na przecięciu. Podobnie jak wyszukaj.pionowo, które pomaga znaleźć dane w tablicy jednowymiarowej.
      Jeżeli poszukuje Pan odpowiedzi na inny problem, proszę go opisać, może będę mógł pomóc.

    2. Post
      Author
  2. Suma.iloczynów działa fajnie, ale tylko przez przypadek – działa, jeżeli liczba x = liczba y.

    W praktyce możemy wyobrazić sobie tablicę gdzie suma x > suma y lub odwrotnie

    Jakiej funkcji/metody wówczas użyć?

  3. Post
    Author

    Dziękuję za uwagę. Dzięki temu zauważyłem błąd w formule z podaj.pozycję.
    Co do suma.iloczynów to nie rozumiem pytania. Ma Pan na myśli sytuację, kiedy ilość kolumn jest różna od ilość wierszy? Formuła działa dla takich przypadków.

      1. Post
        Author

        Być może Pan, lub ja popełniliśmy jakiś inny błąd. Punkt o którym Pan pisze nie ma znaczenia dla tych obliczeń. Mówi on, że kolejne tablice muszą mieć taką samą szerokość i wysokość.

        SUMA.ILOCZYNÓW(tablica1;[tablica2];[tablica3];...)

        W naszym przypadku mamy tylko jeden argument.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *