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.
Tak naprawdę potrzebujesz przeszukać dwie kolumny na raz w poszukiwaniu, jednocześnie, wybranej kategorii i podkategorii. Wbrew pozorom nie jest to zadanie trudne.
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”.
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.
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ą:
{ 18 comments… read them below or add one }
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
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
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 
.
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
Jeszcze inna metoda.
=SUMA.ILOCZYNÓW(($B$10:$B$20=$I15)*($C$10:$C$20=$J15)*($D$10:$D$20))
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,
Szkoda tylko, że już od 2 miesięcy nieaktualizowany. ;(
>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
Dlatego też cały czas na wszelki wypadek zaglądam tutaj
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???
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.
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
Cześć,
?
i co z tym plikiem z budżetem domowym ? Chyba od sierpnia nie siedzisz nad nim
@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
No i zdaje się, że tym razem blog padł skutecznie… Szkoda. Tak czy inaczej pozdrawiam autora.
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
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??
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”