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 }


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