Przeszukiwanie tabel w Excelu – dwa wyrazy w jednym wierszu (kolumnie)

by Marcin

ExcelTabelkaSzukanie2

Andrzej, jeden z czytelników bloga zadał następujące pytanie:

W 2 kolumnach mam wiele różnych zmiennych np. Imiona, samochody. są one także rozmieszczone w tablicy, skąd trzeba pobrac dane. Zadanie polega na tym, że jeśli w kolumnie1 jest wyraz „Maria”, w kolumnie2 „opel”, to excel ma przeszukać całą tablice i sprawdzić, czy oba wyrazy występują w jednej kolumnie (lub wierszu, po transpozycji tablicy). Jeśli tak, wstawić literę „s”, jeśli nie, „d” (…) Inną odmianą tego zadania jest, jeśli dane znajdują sie w tej samej kolumnie/wierszu, Excel ma podać numer kolumny/wiersza”

Ponieważ  próba odpowiedzi na to pytanie łączy się nieco (postanowiłem nawet użyć tej samej przykładowej tabeli) z moim ostatnim wpisem o przeszukiwaniu tabeli w Excelu, postanowiłem iść za ciosem i zaproponować rozwiązanie problemu, a w zasadzie jedno z możliwych rozwiązań.

Układ mojego skoroszytu wygląda następująco:

ExcelTabelaPrzeszukiwanie

Z lewej strony mamy więc tabelkę, w kórej będziemy poszukiwać wyrażeń. Jest to dokładnie ta sama tabelka, której użyłem we wspomnianym wcześniej wpisie o przeszukiwanie tabeli w Excelu.  W tabeli obok znajduje się miejsce na wpisanie dwóch poszukiwanych wyrazów, kolumna z informacją czy wyrazy te znajdują się w jednym wierszu, oraz dwie kolumny z informacją o numerze tego wiersza (numer liczony od początku tabelki oraz od początku skoroszytu).  Dodatkowo, umieściłem tam także komórkę do której należy wpisać ilość wierszy naszej tabeli z wyrażeniami/wyrazami. Korzystam z tej liczby w swoich formułach.

ExcelTabelaPrzeszukiwanie2

Na koniec formuły, które umieściłem w kolumnach I, J i K. Wszystkie formuły są formułami tablicowymi. Nie będę tłumaczył ich działania. Jeżeli prześledziłeś moje poprzednie wpisy na blogu nie będziesz miał problemów ze zrozumieniem formuł. Jeżeli nie prześledziłeś moich wpisów, … no cóż…. pora prześledzić 🙂

1. Sprawdzamy czy dwa wyrazy znajdują się w jednym wierszu.

{=JEŻELI(SUMA(LICZ.JEŻELI(PRZESUNIĘCIE($B$6:$E$6,WIERSZ(ADR.POŚR("1:"&G10)),0),G7)*LICZ.JEŻELI(PRZESUNIĘCIE($B$6:$E$6,WIERSZ(ADR.POŚR("1:"&G10)),0),H7)),"S","D")}

2. Zwracamy numer tego wiersza (licząc od początku tabelki)

{=PODAJ.POZYCJĘ(1,LICZ.JEŻELI(PRZESUNIĘCIE($B$6:$E$6,WIERSZ(ADR.POŚR("1:"&G10)),0),G7)*LICZ.JEŻELI(PRZESUNIĘCIE($B$6:$E$6,WIERSZ(ADR.POŚR("1:"&G10)),0),H7),0)}

3. Zwracamy numer tego wiersza (licząc od początku skoroszytu)

{=SUMA(LICZ.JEŻELI(PRZESUNIĘCIE($B$6:$E$6,WIERSZ(ADR.POŚR("1:"&G10)),0),G7)*LICZ.JEŻELI(PRZESUNIĘCIE($B$6:$E$6,WIERSZ(ADR.POŚR("1:"&G10)),0),H7)*WIERSZ(PRZESUNIĘCIE($B$7:$E$7,0,0,G10)))}

Zbyt długo tego nie testowałem, ale wydaje się działać jak należy. Dodam jeszcze, że moim założeniem jest to, że poszukiwana para wyrazów występuje tylko raz w przeszukiwanej tabeli.

Dla wszystkich zainteresowanych plik z przykładem do ściągnięcia.

Excel timestamp - plik przykładowy

Marcin

{ 14 comments… read them below or add one }

Julia

Bardzo dziękuję za chęć pomocy, włożony wysiłek i poswięcony czas! Usiłuję to wdrożyć na moich danych, na razie jeszcze bez sukcesu, ale będę walczyć:)

dude

Szukam pomocy jak zrobić taki wykres, w którym jeden słupek obrazowałby zsumowaną zawartość kilku komórek, ale bez wcześniejszego sumowania ich w oddzielnej komórce.

jochen

A jak zrobić gdy poszukiwana para wyrazów występuje kilka razy w przeszukiwanej tabeli. A zamiast S/D wyświetlała się maksymalna data, która znajduje się przy danej parze 🙂

Erydan

Marcinie,
moje wczorajsze pytanie dotyczące Twojej wiedzy nt. ewentualnych ograniczeń na stosowanie formuł w tabelach Excela 2007 jest już nieaktualne. Znalazłem odpowiedź w książce Excel 2007. Formuły – J. Walkenbach’a. (punkt – Ograniczenia tabel)
– Nie można w tabelach (między innymi) wstawiać automatycznych sum częściowych (Dane/Konspekt/Suma częściowa).
Automatyczne numerowanie wyłącznie widocznych wierszy w tabeli będę musiał wstawiać przy użyciu innej formuły (na razie takiej nie znalazłem).
Pozdrawiam
Krzysztof

domis

witam,
ciesze się, że są takie strony jak ta – kopalnia wiedzy!
mam pytanie – jak ograniczyć dokument tak jak ten z tematu?
Dokument kończy się na L22 i zakończony ładnym zielonym cieniowaniem.

berial

witam
staram się utworzyć funkcje do przeszukiwania wierszy w tabeli gdzie
znajdują się dane z całego tygodnia, w wyniku staram się otrzymać sumę
ze wszystkich rekordów spelniajacych 2 warunki . zaczelem używać twojej
funkcji ale nie wiem jak dodać do niej więcej warunków oraz żeby wynik był
suma gdyz wynikow bedzie duzo

=INDEX($B$1:$C$6,MATCH(A10,$A$1:$A$6,FALSE),1)

Mario

Witam,

a mnie bardzo interesuje w jaki sposób można osiągnąć widok arkusza w takim kształcie jak ten w pliku źródłowym? Tzn, że mamy zakres kolumn od A do L i wierszy od 1 do do 22?

Adam

dzieki, na pewno sie przyda!:)

hellz

@Mario – zaznacz kolumny M do końca i ukryj. To samo z wierszami od 23 do końca.

merti

Witam. Wiem że to jest na pewno proste ale motam się, nie potrafię tego ogarnąć 😉 Jak przestawić wartości z pionu do poziomu. Mam daty, obok wartości lub pusta komórka- to pion dwukolumnowy 😉 Teraz chciałbym by od znalezionej daty, w poziomie wypisało mi w kolejnych komórkach te wartości z pionu, oczywiście tam gdzie pusta to i w poziomie pusta 😉
Z góry dziękuję za pomoc 🙂

merti

Walczę i coś tam mam ale dalej jestem w lesie 😉

Niby formuła =INDEKS($Z:$Z;WIERSZ($Z25)+C$1); (gdzie C$1 to kolejne liczby w komórkach wiersza) zwraca mi te wartości ale muszę pokazać składni WIERSZ gdzie ma zacząć szukać, a mnie chodzi o to by samo znalazło w kolumnie Y:Y interesującą mnie datę i od tego zaczął wypisywać wartości z kolumny Z:Z 😉 Ciągle wywala mi błąd #LICZBA, formatują na różne sposoby i doopa 🙁 …

Sansej

merit – użyj funkcji tablicowej transponuj.

Malina

Świetny sposób na prezentację zagadnienia!

blogi

Witam, ciekawy tekst, niemniej przydałoby się trochę więcej zdjęć i grafik
wizualizujących artykuł. Osobiście wiem że nie
jest to łatwe – bo redaguję swój portal (odnośnik wyżej), jednak da się sobie tak czy owak pomagać obrazami z darmowych stocków – sxc.hu, wikimedii i pokrewnych.

Życzę powodzenia i zachęcam do odwiedzenia
mojego serwisu!

Leave a Comment

Previous post: