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 }

Przeszukiwanie tabelki i nagłówek kolumny ze znalezioną wartością.

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 […]

Czytaj cały wpis →

Solver i kombinacja liczb dających określoną sumę

Kilka dni temu stanąłem (mam dziwne wrażenie, że nie ja pierwszy) przed następującym problemem: w jaki sposób z listy liczb wybrać wszystkie kombinacje dające określoną sumę ? Problem tylko pozornie wydaje się prosty (w każdym razie wydawał mi się taki kilka dni temu, kiedy pełen zapału postanowiłem pomóc współpracownikowi, który musi ręcznie wyłuskiwać z bardzo […]

Czytaj cały wpis →

Excel „timestamp” (czyli o datowaniu zmian w komórce)

Od czasu do czasu zachodzi potrzeba zapisania w Excelu daty i godziny wprowadzenia nowej wartości (lub jej zamiany) do komórki. Przykładem może tu być lista, którą będzie wypełniał pracownik, a my chcielibyśmy dokładnie wiedzieć kiedy każda wartość została wpisana. Załóżmy sobie także na dzisiaj (tak dla podniesienia ciśnienia), że edytor VBA ma dzisiaj wolne, poszedł […]

Czytaj cały wpis →

Lista rozwijana, tabelki i aparat fotograficzny

Dzisiaj o tym, w jaki sposób dodać do swojego arkusza możliwość wyboru i wyświetlenia jednej z wielu tabel (zakresów) znajdujących się w innym skoroszycie. Zadanie niezbyt skomplikowane, ma jednak tę zaletę, że będziemy korzystać z kilku ciekawych i bardzo użytecznych technik (funkcji Excela). Będzię to wspomniane już w tytule narzędzie Aparat Fotograficzny, o którym pisałem […]

Czytaj cały wpis →

VSTO i Add-in Express 2010

Od pewnego czasu mam okazję pracować z Visual Studio oraz dodatkiem do niego – Add-in Express™ 2010 (http://www.add-in-express.com). Ponieważ moje wrażenia związane z dodatkiem są bardzo pozytywne, chciałbym podzielić się z Wami kilkoma wrażeniami z użytkowania całości. Od razu zaznaczyć muszę, że jestem nowicjuszem jeśli idzie o programowanie VSTO i Visual Studio. Wyjście poza VBA […]

Czytaj cały wpis →

Excelowa animacja Noworoczna

Całość to tylko kilka linijek kodu VBA (plus sprytne – no w sumie sprytny chłopak jestem – wykorzystanie Excelowych wykresów) . Dla zainteresowanych, jak  zwykle zresztą, plik Excela do ściągnięcia. Marcin

Czytaj cały wpis →