PODAJ.POZYCJĘ – przeszukiwanie dwóch kolumn na raz

by Marcin

PODAJ.POZYCJĘ - przeszukiwanie więcej niż jednej kolumny na raz

Dzisiaj o tym, w jaki sposób przy pomocy funkcji PODAJ.POZYCJĘ przeszukać więcej niż jedną kolumnę na raz. W typowych zastosowaniach funkcje typu WYSZUKAJ.PIONOWO, WYSZUKAJ.PIONOWO lub PODAJ.POZYCJĘ poszukują wskazanej wartości w jednej, wskazanej kolumnie. Z reguły to wystarcza, zdarzają się jednak sytuacje, kiedy aż prosi się o coś więcej. Najlepiej zilustruje to przykład ( jak zwykle z życia wzięty).

Jestem w trakcie przygotowywania arkusza ułatwiającego zarządzanie domowym budżetem (jak tylko skończę udostępnię go na blogu). W jednej kolumnie mam nazwy kategorii, w drugiej nazwy odpowiadających im podkategorii. Użytkownik wybiera sobie kategorię i podkategorię, a nasze zadanie polega na odszukaniu wiersza w tabeli, który odpowiada dokonanemu wyborowi. Nie byłoby z tym żadnego problemu gdyby nie fakt, że podkategorie powiązane są nierozłącznie z kategoriami oraz mogą się powtarzać w ramach różnych kategorii.

Jak widzisz na rysunku poniżej podkategoria „inne” występuje aż trzy razy w kolumnie, za każdym razem przy innej kategorii. Przeszukanie tylko kolumny „Podkategoria” w poszukiwaniu wybranej kombinacji kategoria/podkategoria nie wystarczy w tym przypadku.

PODAJ.POZYCJĘ - przeszukiwanie dwóch kolum na raz

Tak naprawdę potrzebujesz przeszukać dwie kolumny na raz w poszukiwaniu, jednocześnie, wybranej kategorii i podkategorii. Wbrew pozorom nie jest to zadanie trudne.

PODAJ.POZYCJĘ - przeszukiwanie dwóch kolumn na raz

W komórce I9 znajduje się nazwa poszukiwanej kategorii, w komórce I9 – podkategorii. Jak widzisz na rysunku, wszystko, co musisz zrobić to połączyć te dwie wartości w jedno przy pomocy znaku ampersand („&”) lub przy pomocy funkcji Złącz.Teksty, łącząc jednocześnie w ten sam sposób dwie kolumny, w których chcesz szukać wskazanych wartości (w moim przykładzie wyglądałoby to tak: B10:B20&C10:C20). Ostatecznie, odpowiednia formuła wygląda tak:

=PODAJ.POZYCJĘ(I9&J9;B10:B20&C10:C20;0)

Musisz ją wprowadzić jako formułę tablicową, zatwierdzając Shift+Ctr+Enter.

W zasadzie mógłbym na tym skończyć moje dzisiejsze rozważania gdyby nie jedno „ale”. Jako poszukiwaną kategorię wpisz „Rozrywka”, a podkategorię „Restauracje”.

PODAJ.POZYCJĘ - przeszukiwanie dwóch kolumn na raz

Formuła zwraca cyfrę „7” czyli wskazuje na siódmy wiersz naszej tabeli. Niby nic niezwykłego, ale ….. jeżeli przyjrzysz się uważnie tabeli z danymi zobaczysz, że nie ma tam podkategorii „Restauracje odpowiadającej kategorii „Rozrywka” ! Jest za to kategoria „Rozrywk” i podkategria „aRestauracje”.

Nasza formuła „zlepia” poszukiwaną kategorię i podkategorię w jedno i ostatecznie szuka ciągu „RozrywkaRestauracje” w tablicy wartości powstałych ze zlepienia kolumn z danymi źródłowymi. Jeżeli zlepisz w taki sposób „Rozrywk” z „aRestauracje” otrzymasz oczywiście „RozrywkaRestauracje”, co odpowiada poszukiwanej wartości. Technicznie wszystko się więc zgadza tyle, że wynik jest inny od oczekiwanego. Rozwiązanie problemu jest także i tym razem całkiem proste. Wystarczy pomiędzy „zlepiane” wartości wstawić jakiś znak, który zagwarantuje jednoznaczność naszego zapytania.

Dla przykładu, „Marcin” & „Excelblog” utworzy ten sam ciąg co „Mar” & „cinExcelblog”, ale już „Marcin” & „5” & „Excelblog” oraz „Mar” & „5” & „Excelblog” to już dwa, zupełnie różne ciągi.

Ostatecznie, poprawiona formuła, wyglądać będzie np. tak:

=PODAJ.POZYCJĘ(I15&ZNAK(10)&J15;B10:B20&ZNAK(10)&C10:C20;0)

Wynik działania takiej formuły widoczny jest na rysunku poniżej. Szukana kombinacja kategorii i podkategorii nie została odnaleziona.

PODAJ.POZYCJĘ - przeszukiwanie dwóch kolumn na raz

Po wybraniu innej (istniejącej) podkategorii łatwo się przekonasz, że obydwie funkcje zwracają ten sam wynik.Na koniec, skoro już się rozpisałem, wspomnę o dwóch innych metodach osiągnięcia tego samego rezultatu. Możesz zastosować formułę tablicową:

=PODAJ.POZYCJĘ(1;(B10:B20=I25)*(C10:C20=J25);0)

Formuła sprawdza czy wartości w kolumnie „Kategorie” odpowiadają wybranej kategorii, jeżeli tak to tymczasowej tablicy przechowywanej w pamięci komputera zapisywana jest wartość PRAWDA. W innym przypadku zapisywana jest oczywiście wartość FAŁSZ. Podobna operacja zachodzi dla wartości z kolumny „Podkategoria”. Następnie wartości w tych dwóch tablic tymczasowych są przez siebie mnożone. Jeżeli pamiętasz, że PRAWDA przyjmuje wartość 1, oraz, że PRAWDA*PRAWDA=1, a PRAWDA*FAŁSZ=0 oraz FAŁSZ*FAŁSZ=0 łatwo zobaczysz, że ostatecznie wartość 1 znajdzie się tylko w tym wierszu, w którym nazwa kategorii i podkategorii pasują do wartości poszukiwanych. Na koniec,  przy pomocy funkcji PODAJ.POZYCJĘ odszukujemy „1” (numer wiersza) w tymczasowej tablicy.

PODAJ.POZYCJĘ - przeszukiwanie dwóch kolumn na raz

Możesz także po prostu dodać jedną kolumnę pomocniczą. W zależności od tego czy w danym wierszu znajduje się odpowiednia kombinacja kategorii i podkategorii formuła użyta w kolumnie pomocniczej zwróci wartość PRAWDA dla wiersza z odpowiednią kombinacją i FAŁSZ w każdym innym przypadku. Numer odpowiedniego wiersza odnajdziesz następnie przy pomocy prostej funkcji

=PODAJ.POZYCJĘ(PRAWDA;E10:E20)

PODAJ.POZYCJĘ - przeszukiwanie dwóch kolumn na raz

Oczywiście istnieje pewnie przynajmniej kilka innych sposobów. Jeżeli masz inne, ciekawe pomysły jak zwykle zachęcam do kontaktu, komentowania oraz krytyki.

Marcin

Download

{ 22 comments… read them below or add one }

Roniek

Czesc,
Pisze, zebys wiedzial, ze czytuje Twojego bloga i zebys nie myslal o zamykaniu go. Ciekaw jestem, czy pracowales na excelu 2007 – przymierzam sie do kupna i interesuja mnie potencjalne nowe funkcje, jakich moge sie spodziewac. Mozesz juz cos na ten temat powiedziec?

Pozdrawiam

Marcin

Czesc Roniek,

O zamykaniu bloga nie myślę, ale dzieki za słowa zachęty. Jeżeli idzie o Excela 2007 to pracuję na nim bardzo sporadycznie. Bynajmniej nie ze względów ideologicznych. Po prostu do tej pory Excel 2003 doskonale spełnia swoje zadanie i jakos nie czuję presji na „przesiadkę”. Zasadniczo uważam, że to czy narzędzie jest dobre czy złe zależy od tego co potrafi z nim (narzędziem) zrobić użytkownik. NIe przywiązywałbym zbyt wielkiej wago do numerów. Oczywiście excel 2007 ma kilka cech, które mogą okazać się ważne dla niektórych, ale skoro o nich jeszcze nie wiesz i pytasz to pewnie nie są jeszcze ważne dla Ciebie. Informacje o excelu 2007 znajdziesz na wielu stronach internetowych (strony Microsoft są intuicyjnym wyborem) więc nie bedę tu opisywał cech programu.
Z ze swojej, w kwestii Excela 2007, „nie widze przeciwskazań” 🙂

Pozdrawiam,

Marcin

Quasi

Cześć Marcin – fajnie się czyta to co piszesz – jednym tchem :-). Co do przykładu – podoba mi się zwłaszcza ten drugi sposób oparty na koniunkcji warunków. Gdyby dołożyć jeszcze na początku formuły funkcję INDEKS możnaby było przyporządkować danej kategorii i podkategorii np. cenę, która znajdowałaby się w trzeciej kolumnie w układzie danych :-). Fajny sposób – moim zdaniem jeżeli chodzi o formułę to najlepszy chociaż filtr zaawansowany też sprawdziłby się tutaj świetnie nawet dla kilku różnych cen da danej kategorii i podkategorii :-).

Marcin

Cześć Quasi,

>fajnie się czyta to co piszesz – jednym tchem

Od czasu do czasu zalecam jednak kilka oddechów 🙂 A tak serio – dzięki. Bardzo miło jest usłyszeć, że to jak pisze komuś tam się podoba.

>Gdyby dołożyć jeszcze na początku formuły funkcję INDEKS (…)

Oczywiście, że masz rację. Tak właśnie wygląda to u mnie w skoroszycie z budżetem. W przykładach na blogu wszystkiego nie pokazałem, ale kolumnę kategorii i podkategorii przeszukuję oczywiście w celu „wyłuskania” danych z innych kolumn.

>filtr zaawansowany też sprawdziłby się tutaj świetnie

Ano mądrze gadasz – jak zwykle 🙂 Pewnie, że się da. Wybór metody zależy oczywiście od indywidualnych preferencji, potrzeb, przyzwyczajeń etc.

Pozdrawiam i życzę sukcesów na Twoim „podwórku”,

Marcin

JM

Jeszcze inna metoda. 🙂

=SUMA.ILOCZYNÓW(($B$10:$B$20=$I15)*($C$10:$C$20=$J15)*($D$10:$D$20))

sznurowadlo

Ciekawy blog.

Szczerze mówiąc wykorzystuję go do nauki co ciekawszych zastosowań excela ponieważ informacje w nim podane są w zdecydowanie bardziej przystępny sposób niż w podręcznikach opisujących funkcje, formuły czy też VB.

Pozdrawiam,

shai

Szkoda tylko, że już od 2 miesięcy nieaktualizowany. ;(

Marcin

>Szkoda tylko, że już od 2 miesięcy nieaktualizowany. ;(

Tak sie akurat niefortunnie zlozylo, ze niespodziewanie strasznie duzo czasu musze poswiecic kilku zupelnie innym rzeczom, a ze doba ma tylko 24 godziny ….. 🙂 Poza tym uwazam, ze jak mam cos robic byle jak i przygotowywac wpisy na „odwal sie” tylko po to, zeby mi sie blogowa statystyka nabijala – to lepiej uczciwie sobie na jakis czas odpuscic i nie robic nic. Z cala pewnoscia (obiecuje to teraz publicznie wszystkim) do pisania bloga powroce. Materialow mam sporo, tematow poruszonych przez czytelnikow bloga takze cale mnostwo. Tylko czasu troche brak 🙂 Prosze wiec o cierpliwosc.

Marcin

shai

Dlatego też cały czas na wszelki wypadek zaglądam tutaj 🙂

skrzynia

Witam,
od 1/2 roku pracuję na ex2007 i moje wnioski soą następujące:
+ 1. więcej wierszi i kolumn
+2. pomocna formuła sumifS – może być wiele „ifów”
– 1 edycja wykresów do bani – wolno, często się wiesza, trudno znaleźć opcje
-2 trzeba się przestawić (co trwa kilka tyg.), totalnie inna szata graficzna i pogrupowanie pozycji menu. Początki były bolesne, ale ogólnie po kilku mies. oceniam na +
Ogólnie na necie jest kilka porownań i podsumowań na ten temat i opinie są takie, że w wielu obszarach jest to nawet krok wstecz.

Btw: jak jes podaj.pozycje po angielsku???

Danny

Taki fajny blog, a tak rzadko aktualizowany. :/

Pzdr,
D

PS Z niecierpliwością czekam na arkusz do zarządzania domowym budżetem. Sam coś takiego prowadzę w Excelu to chętnie zobaczę, jak to zrobił specjalista. 🙂

zbiniek

Witam!

„Jak jest podaj.pozycje po angielsku???”

Znajdź na swoim dysku (tam, gdzie masz zainstalowanego Excela) plik funcs.xls – znajdziesz tam tłumaczenia tej i pozostałych funkcji (przynajmniej w Excelu 2003 tak jest).

pzdr

PITTER

Cześć,
i co z tym plikiem z budżetem domowym ? Chyba od sierpnia nie siedzisz nad nim 😉 ?

Marcin

@Pitter

Plik budzetowy „dojrzewa” 🙂 Zaczalem to robic na swoje prywatne potrzeby z mysla, ze jak juz wszystko bedzie ladnie dzialac i bede calkowicie zadowolony to opublikuje. Poki co dzila dobrze na moje potrzeby, ale nie na tyle, abym sie tym mogl publicznie dzielic. W miedzyczasie popelnilem (no prawie) takze kilka innych „projektow”, ktore takze bede powoli publikowal. Koncze wlasnie fajny pliczek do wystawiania faktur (bez makr).

Swoja droga przyznam, ze presja wywierama przez Was dziala na mnie motywujaco. Znaczy sie dreczy mnie mysl, ze jednak czas zabrac sie za bloga. Tak trzymajcie 🙂

Marcin

Als

No i zdaje się, że tym razem blog padł skutecznie… Szkoda. Tak czy inaczej pozdrawiam autora.

Marcin

E tam padl. Gdzie Twoj niezachwiany optymizm 🙂 Dzisiaj bede publikowal kolejny wpis. W sumie byl gotowy wczoraj, ale lubie jak mi sie wpisy „przeleza”. Zawsze cos tam sie wtedy do poprawienia znajdzie.

Marcin

Kate

Bardzo fajny i użyteczny przykład, mam niestety problem z zastosowaniem go tak aby wyszukane dane wstawić do innego arkusza, grzebiąc w necie natknęłam się na uwagę, że formuła tablicowa działa w obrębie jednego arkusza. Jak zatem ominąć ten problem??

daro_niewinny

Mała literówka w zdaniu:
Dla przykładu, „Marcin” & „Excelblog” utworzy ten sam ciąg co „Mar” & „cinExcelblog”, ale już „Marcin” & „5” & „Excelblog” oraz „Mar” & „5” & „Excelblog” to już dwa, zupełnie różne ciągi.
– powinno być -> „Mar” & „5” & „cinExcelblog”

irminzar

a ja mam pytanie, jak ograniczyć „resztę” arkusza jak w tym pliku. Aktywne jest tylko małe okno

maxII

…na początek : Dziękuję bogom, że Was znalałem ! 🙂 …
Czytam sobie powolutku, bo ….tak w takim samym tempie myślę :)… i kupę „rzeczy” już ponaprawiałem dzięki tym …pozornie małym opracowaniom. Dzięki.
Panie Marcinie – Dzięki.

Czesław

Kate – u mnie działa w dodatku z funkcją INDEKS
{=INDEKS(spis2!$F2:$F2353;PODAJ.POZYCJĘ(C7&C8;spis2!$C2:$C2353&spis2!$D3:$D2353;0))}
a ogolnie Marcin b.dobra stronka. Pozdrawiam

Robert

Fajne rozwiązanie. Bardzo przydatne. Ale to nie zmienia faktu, że jest to rozwiązanie excel-wymiarowe a nie na przykład 3 lub 4 wymiarowe. Basic taki z przed 30 lat ma taką świetną formułę wielowymiarową. A excel to tak jak by pochodził z przed stu lat nic nie ma w tym temacie. Groza. Jeżeli by się dało połączyć funkcję „wyszukaj” i „przesunięcie” – ale się nie da. Brakuje liczników do funkcji „jeżeli” (for,to,step,next) itp. itd.

Leave a Comment

Previous post:

Next post: