Kiedyś wykorzystałam go, gdy tworzyłam formularz, za pomocą którego handlowcy zamawiali towar. Spośród całego asortymentu mieli oni wybrać te produkty, które zamierzali sprzedać. Każdy handlowiec najpierw określał grupę produktową, a następnie konkretny produkt z tej grupy. Na formularzu miała się znaleźć cała nazwa grupy oraz konkretny indeks produktu. Ponieważ wpisywanie tego z palca byłoby zbyt czasochłonne (i denerwujące), zaproponowałam bardzo sprytne i proste rozwiązanie – 2 listy rozwijane. Pierwsza była listą wszystkich kategorii produktów, druga zaś listą wszystkich produktów znajdujących się w wybranej kategorii. Stworzyłam więc listę rozwijaną zależną od wyboru dokonanego na poprzedniej liście (tutaj znajdziesz wpis o tym, jak stworzyć dwie zależne listy rozwijane).
Taki sam efekt chce uzyskać Waldkorg, który do budżetu domowego potrzebuje kategorii i podkategorii wydatków. Przykładowe dane znajdują się na poniższym obrazku:
Zależna lista rozwijana
Czyli np. jeśli wybierzemy kategorię Rozrywka, to na liście podkategorii powinny się znaleźć: Kino, Teatr, Basen. Bardzo sprytne rozwiązanie, jeśli w swoim budżecie domowym chcecie analizować więcej szczegółów. Przyznam, że w mojej propozycji budżetu domowego ograniczam się tylko do kategorii, dlatego że taki podział wydatków w zupełności mi wystarcza (nazwę wydatku/przychodu traktuję jak podkategorię). Jeśli jednak potrzebujecie podzielić to na podkategorie, to metoda, którą opiszę dalej będzie idealna. Korzystajcie śmiało!
Formatka – tabela z danymi do zależnej listy rozwijanej
Najpierw należy stworzyć listę kategorii (A3:A5) i tabelę z danymi dla podkategorii. Lista kategorii to po prostu nazwy kategorii wypisane pod sobą – ona więc jest bardzo prosta.
Tabela podkategorii jest trochę trudniejsza, ponieważ powinna ona zawierać nazwę kategorii powtórzoną tyle razy, ile jest podkategorii i oczywiście podkategorie. I ważna sprawa: cała ta tabela powinna być posortowana według kategorii! Będzie to niezwykle istotne przy późniejszym pisaniu formuły.
Tabela powinna wyglądać tak (zakres G2:H15):
Formatka
No dobrze. Teraz po kolei opiszę kroki tworzenia zależnej listy rozwijanej.
1. Nazwanie zakresów dla zależnej listy rozwijanej
Jest to krok nieobowiązkowy, bez niego bowiem bez problemu sobie poradzimy. Lubię jednak stosować nazwy, ponieważ znacznie ułatwiają one zarówno pisanie, jak i czytanie formuł.
Nazwiemy 2 zakresy. Listę wszystkich kategorii i roboczą listę kategorii. Będą to zakresy A3:A5 (lista kategorii w zielonej tabelce na pierwszym obrazku) i G3:G15 (lista powtórzonych kategorii w fioletowej tabelce roboczej).
Żeby nazwać listę kategorii:
- Zaznacz zakres A3:A5.
- W polu nazwy (pole po lewej stronie od paska formuły) wpisz nazwę Kategorie.
- Zatwierdź Enterem.
Te same czynności wykonaj dla zakresu roboczej listy kategorii G3:G15, którą nazwij ListaRob. Tego zakresu będziemy używać w formule.
2. Stworzenie listy rozwijanej dla kategorii
To będzie proste:
- Zaznacz komórkę, w której chcesz umieścić listę. U mnie to A12.
- Z menu Dane wybierz Poprawność danych. Pojawi się okno Sprawdzanie poprawności danych.
- Jako wartości dozwolone wybierz lista.
- Jako źródło wpisz: =Kategorie (obrazek poniżej).
Sprawdzanie poprawności – kategorie
- Zatwierdź OK.
Wynik jest następujący:
Lista rozwijana kategorii
3. Stworzenie (zależnej) listy rozwijanej dla podkategorii
Tutaj zaczyna się zabawa. Tworzyć listę umiemy – zrobiliśmy to przed chwilą dla kategorii. Pytanie tylko: „Jak powiedzieć Excelowi, aby wybrał tylko te wartości, które są przyporządkowane do konkretnej kategorii?”. Jak się zapewne domyślacie, skorzystam tutaj z tabeli roboczej i oczywiście z formuł.
Zacznijmy od tego, co umiemy, czyli od stworzenia listy rozwijanej w komórce B12. Zaznacz więc tę komórkę i wybierz Dane/ Poprawność danych, a jako kryterium poprawności – Lista.
W źródło listy wpisz taką formułę:
=PRZESUNIĘCIE($H$2;PODAJ.POZYCJĘ(A12;ListaRob;0);0;LICZ.JEŻELI(ListaRob;A12);1)
Wygląd okna Sprawdzanie poprawności danych:
Sprawdzanie poprawności dla podkategorii
Jak widać, cały trik listy zależnej polega na użyciu funkcji PRZESUNIĘCIE. No dobra, prawie cały. Pomagają jej funkcje PODAJ.POZYCJĘ i LICZ.JEŻELI. Funkcja PRZESUNIĘCIE pozwala dynamicznie określić zakresy. Na początku ustalamy komórkę, od której ma się zacząć przesuwanie zakresu, a w kolejnych argumentach ustalamy jego rozmiary.
W naszym przykładzie zakres będzie przesuwał się po kolumnie Podkategoria w tabeli roboczej (G2:H15). Przesuwanie zaczniemy od komórki H2, która jest jednocześnie pierwszym argumentem naszej funkcji. W formule ją zablokowałam, ponieważ zakładam, że listę rozwijaną będziemy wykorzystywać w wielu komórkach.
Ponieważ tabela robocza jest posortowana po Kategorii, to zakres, który ma być źródłem do listy rozwijanej, będzie zaczynał się tam, gdzie pierwsze wystąpienie wybranej kategorii. Np. dla kategorii Jedzenie chcemy wyświetlić zakres H6:H11, dla Transport – zakres H12:H15 itd. Zauważmy, że cały czas poruszamy się po kolumnie H, a jedyne co się zmienia to początek zakresu i jego wysokość (czyli liczba elementów na liście).
Początek zakresu zostanie przesunięty względem komórki H2 o tyle komórek w dół (o taką liczbę), ile wynosi numer pozycji pierwszego wystąpienia kategorii w kolumnie Kategoria. Łatwiej będzie na przykładzie: zakres dla kategorii Jedzenie jest przesunięty o 4 komórki w dół względem komórki H2 (zaczyna się w 4. komórce pod zakresem H2). W 4. komórce kolumny Podkategoria (nie uwzględniam nagłówka, czyli interesuje mnie nazwany zakres ListaRob) jest również słowo Jedzenie (jego pierwsze wystąpienie). Wykorzystujemy ten fakt właśnie do określenia początku zakresu. Posłuży nam do tego funkcja PODAJ.POZYCJĘ (wpisana w drugim argumencie funkcji PRZESUNIĘCIE):
PODAJ.POZYCJĘ(A12;ListaRob;0)
Wysokość zakresu ustali funkcja LICZ.JEŻELI. Zliczy ona wszystkie wystąpienia kategorii, czyli słowa Jedzenie. Słowo to występuje tyle razy, ile będzie pozycji w naszym zakresie. Liczba pozycji w zakresie to jego wysokość. Oto funkcja:
LICZ.JEŻELI(ListaRob;A12)
Oczywiście obie funkcje zawiera już funkcja PRZESUNIĘCIE opisana wyżej. Dodatkowo zauważcie, że zarówno w funkcji PODAJ.POZYCJĘ, jak i w LICZ.JEŻELI odwołałam się do nazwanego zakresu ListaRob. Tak jak wspomniałam wcześniej, nie trzeba używać nazw zakresów, można wpisać po prostu $H3:$H15. Użycie nazwy jest jednak łatwiejsze i czytelne.
I tyle. Jedna formułka, no niekoniecznie prosta, i załatwia sprawę! Dwa zastosowania tego triku już podałam. Ciekawa jestem natomiast, gdzie wy to wykorzystacie?
A poniżej wersja wideo opisywanego case’a:
Powiązane produkty
- Nazwy w Excelu – wstęp – pokazuję tutaj jak stworzyć nazwy komórek, zakresów i – co jest arcyciekawe – formuł! To bardzo proste i przydatne, zobaczysz 🙂
- Listy rozwijane – pokazuję jak zrobić listę rozwijaną zarówno taką, jak we wpisie (czyli sprawdzanie poprawności), jak i formant formularza, oraz jak je wykorzystać np. na wykresach.
- Walidacja danych – triki – opowiadam o ciekawych zastosowaniach sprawdzania poprawności, którego lista rozwijana w komórce (użyta w tym wpisie) jest opcją. Ale narzędzie to potrafi dużo, duuuużo więcej!
- Zagnieżdżanie funkcji – podczas tego webinaru pokazuję jak w prosty sposób zagnieżdżać funkcje jedna w drugiej i trzeciej…
Redakcja językowa: Aleksandra Wasiak
Fajny artykuł!
Zadanie można wykonać chyba nieco łatwiej:
– rozpoczynamy podobnie jak powyżej, nazywając interesujące nas zakresy, korzystając z Menedżera nazw
– dalej postępujemy jak w pkt. 2
– korzystamy z adresowania pośredniego, czyli zaznaczamy komórkę B12, klikamy poprawność danych, ustawiamy „lista” a w polu Źródło wpisujemy:
=ADR.POŚR(A12)
koniec zadania 😉
ew. jeżeli chcemy dołożyć kolejne stopnie „zagnieżdżenia” zależnych list rozwijalnych to za każdym razem w kolejnym polu wyboru ustawiamy jak powyżej:
=ADR.POŚR(<>) czyli np. =ADR.POŚR(B12)
życzę miłego klikania! 🙂
Super propozycja! 🙂 Czyli zależy od tego, jak mamy dane ułożone, to tak można rozwiązać zadanie 🙂 W Excelu fajne jest to, że jedno zadanie można rozwiązać na wiele różnych sposobów – na szczęście, bo daje to o wiele więcej możliwości 😉
to drugie mi nie działa.
Oczywiście, że nie działa, bo kolega bobby najwyraźniej nie zrozumiał zagadnienia, którego rozwiązanie jest tu przedstawione, albo myślał, że rozumie przedstawiając =ADR.POŚR() jako magiczną funkcję, która sama wie skąd, co i ile brać. Bez sprawdzania mogę powiedzieć, że o ile użycie adr.pośr będzie możliwe, to nie obędzie się bez zagnieżdżania dodatkowych funkcji jak licz.jeżeli. Użycie adr.pośr może być bardziej problematyczne, z bardziej statycznego charakteru tej funkcji. Funkcja =przesunięcie jest wprowadzona wlaśnie dla określania dynamicznego zakresu i jej zastosowanie tutaj ma sens.
Malinko, zrobisz filmik, jak zrobić listę rozwijaną, ale taką, że po wybraniu wartości wyświetla się inna wartość (np wybierasz z listy „jabłko” a wyświetli się „Apple” lub wybierasz pracownika a wyświetla się jego indentyfikator).da się bez Mark?
Cześć Kasiu, czy się da bez makr? Może jakoś tak, żeby wartość ta inna, „Apple”, wyświetlała się w komórce obok to tak, jak najbardziej się da (https://malinowyexcel.pl/uzupelnianie-danych-na-podstawie-wyboru-z-listy-rozwijanej/). A żeby w tej samej komórce wpisać, to przychodzi mi do głowy tylko rozwiązanie z makrem (https://malinowyexcel.pl/opis-skrocony-na-liscie-rozwijanej/).
Również użyłem tak jak bobby metody wykorzystującej funkcję adres pośredni i na początku nie działało. Po kilku próbach i przeszukaniu internetu w końcu się udało. Trzeba pamiętać o poprawnym nazywaniu zakresów komórek. U mnie w nazwie była spacja i chyba dlatego nie działało. Po wprowadzeniu poprawek wszystko śmigało.
Aczkolwiek żadne z dwóch rozwiązań nie było dla mnie optymalne, ponieważ potrzebowałem utworzyć dwie listy rozwijane zależne od tego samego parametru lecz z różnymi danymi. Udało się to wykonać w następujący sposób:
1. Utworzenie bazowej listy rozwijanej jak w powyższym przykładzie.
2. Stworzenie osobnych arkuszy z danymi dla poszczególnych zakresów z bazowej listy rozwijanej. Arkusze muszą mieć takie same nazwy jak dane z bazowej listy rozwijanej.
3. Stworzenie drugiej listy rozwijanej i zastosowanie funkcji adres pośredni odnoszącej się do nazwy odpowiedniego arkusza z danymi, którego nazwa znajduje się w liście bazowej.
=ADR.POŚR(M10&”!$C$3:$R$3″)
M10 to adres komórki, w której znajduje się bazowa lista rozwijana.
Może komuś się przyda 😉
Super! Z pewnością się przyda 🙂
Hej, czy możesz gdzieś udostępnić plik w którym zastosowałeś to rozwiązanie? Wygląda na to, że szukam czegoś identycznego ale nie ograniam kompletnie tej funkcji
=ADR.POŚR(M10&”!$C$3:$R$3″)
Thx,
na podstawie powyższego artykułu pokonałem bardzo podobny problem 🙂
Trzymaj się!
Cieszę się i oby tak dalej!
Witam. Czy można to zrobić tak: w kolumnie G kategorie, w H podkategorie a w I kolejny poziom. I ile stopni podkategorii można wprowadzić?
Hej,
jeśli dobrze rozumiem pytanie: nie ma ograniczeń w liczbie podkategorii – jedyne co, to trzeba w kolejnych listach (dla podpodkategorii) napisać analogiczne formuły.
Boże drogi, ja się zaraz potnę przez tego excela.
Potnę się, ale zrobię z trzema podkategoriami. Nie rozumiem tylko dlaczego w odwołaniu funkcji przesunięcie klikasz w komórkę podkategoria? co to daje? czy zdefiniowano wcześniej coś dla tej komórki. Przecież excel nie bada komórek pod wskazaną (w tym przypadku „Podkategoria”). I w opisie powyżej „=PRZESUNIĘCIE(H2;” i w nagraniu „G2” samo kliknięcie co Nam daje. Nie rozumiem a nie znoszę kopiować bez zrozumienia.
Cofam mój ostatni komentarz, już powiedzmy wiem dlaczego (raczej wiem (widzę) co się dzieje po kliknięciu). Mam pytanie odnośnie 2 podkategorii. Załóżmy, że mamy kategorię główną, jej podkategorię i podkategorię podkategorii. np. salon samochodowy oferujący 2 marki: kategoria główna (samochody osobowe, samochody dostawcze), podkategoria 1 (vw, fiat), podkategoria 2 (polo, golf, passat, punto, stilo, multipla, caddy, transporter, crafter, fiorino, doblo, ducato). pytanie 1 jak zrobić listę podkategorii 1 żeby nie wyświetlała tego samego kilka razy (vw, vw, vw, vw, vw, fiat, fiat, fiat, fiat, fiat) i jak poczynić funkcję żeby w 3 komórce po uprzednim wyborze kategorii „dostawcze”, potem „fiat” wyświetlał dostawcze modele fiata (w tym przypadku doblo, ducato)
Hej,
jeśli dobrze rozumiem problem – zarówno Fiat jak i VW mają osobowe i dostawcze. Ja bym tutaj po prostu dała listy rozwijane z palca dla kategorii głównej (Dostawczy/Osobowy) i też z palca podkategorię (Fiat i VW). Niezależnie od wyboru kategorii – podkategorię będą te same 2 do wyboru. Natomiast podpodkategoria… hehe – to dopiero kosmos 🙂 Napiszę o tym kolejny wpis, bo wyszła mi formuła MEGA!
Cześć,
super, nie mogę się doczekać, bo w moim przypadku jest 5 pozycji w kategorii głównej, 6 podkategorii i 10-13 podpodkategorii (10-13 pozycji dla każdej z 6 podkategorii…itd., czyli około 10x6x5 możliwości, chyba że to jakaś silnia;-), nie ważne). Stale tych samych przez lata wpisywanych z palca z błędami przez pracowników. A można rozwinąć listę i wybrać właściwą. Przy okazji zapytam czy można gdzieś zmienić widok listy rozwijalnej żeby wyświetlane były wszystkie wyniki a nie 7 z paskiem bocznym do przesunięcia?
Hej
a możesz przysłać swoje kategorie i pod/podkategorie? Bo ja kombinowałam na podanej przez Ciebie wcześniej liście, a teraz to nie jestem pewna czy zadziała.
Odnośnie wyglądu listy – niestety nie ;( Jest taka toporna i that’s the way it is…
Pisałem na YT, ale chyba tam Pani rzadziej zagląda 😉
Mam problem i zarazem pytanie, można zmodyfikować tą formulę w taki sposób by działało z listą nieuporządkowaną? kiedy wyniki są naprzemienne np. (Rozrywka, Rozrywka, Jedzenie, Rozrywka, Rozrywka)?
Ech, zwykłą formułą to nie za bardzo… tablicową tak, ale ona wymaga zaznaczenia zakresu tablicy, więc w sprawdzaniu poprawności tego nie da się zrobić. Ewentualnie funkcja w VBA zwracająca zakres… cieżka sprawa na pewno. A do czego to potrzebne? :>
A sposób na to jest tutaj: https://malinowyexcel.pl/zalezna-lista-rozwijana-z-nieposortowanych-danych-zdarzenie-vba/.
🙂
Udało mi się obejść to, że nie można do sprawdzania poprawności danych wstawić nazwy obiektu tablicy, czyli stworzonego Ctrl+T. Dodałam w menadżerze nazw nowy zakres nazw, który odwołuje się do mojej tablicy i zadziałało 🙂
Super, że sobie poradziłaś! 🙂
Cześć,
Powyższe rozwiązanie jest rewelacyjne. Już je stosowałam i było ok. Do dziś… Mam przygotowaną tabelę kategorii i podkategorii. Z tym, że często do danej kategorii A mam tylko 1 przykład podkategorii B1. W takiej sytuacji przy zastosowaniu formuły PRZESUNIĘCIE pojawiają się dane dla komórki poniżej z kolumny B. Dlaczego tak się dzieje?
O jejku… chyba nie rozumiem dobrze. Możesz podesłać mi plik? Sprawdzałam na moim przykładzie i działało…
witam, a jak połączyć listy tak zeby podkategorie zmienialy sie przy zmianie kategorii??
Hej, zobacz tutaj:
😉
Można prosić o link?
Też właśnie szukam zabezpieczenia, bo wychodzi zakłamanie jak się wybierze kategorię ROZRYWKA i podkategorię KINO, to przy zmianie ROZRYWKI na JEDZENIE mamy wynik:
JEDZENIE / KINO
O rety, nie wkleił się ;( Już podaję: https://malinowyexcel.pl/kasowanie-wartosci-zaleznej-listy-rozwijanej-na-dlugiej-liscie/
🙂
Dziękuję Tobie bardzo! 🙂
Proszę bardzo 🙂
Dziekuję za informacje. Na początku mi nie wychodziło i nie wiedziałam co robię źle. Aż wydrukowałam sobie i znak po znaku zweryfikowałam i mam. Jesteś bardzo pomocna. Teraz biorę się za podpodkategorię. Zobaczę czy szybciej mi pójdzie. Jeszcze raz wielkie dzieki 🙂
Bardzo się cieszę. Powodzenia 🙂
No mnie niestety nie działa. Nie bardzo wiem co zrobiłem źle. Wynik to #N/D
Hmmm…. musiałabym zobaczyć plik… przyczyn może być dużo…
Kurde analizuje i nie wychodzi. Cały czas błąd
=PRZESUNIĘCIE(AD2;PODAJ.POZYCJĘ(I3;AC3:AC43;0);0;LICZ.JEŻELI(AC3:AC43;I3);1)
jest ok dopóki nie zacznę niżej schodzić z wierszami. Np. w I3 do I10 wybieram z listy tą samą kategorię (która ma 5 podkategorii), na liście rozwijanej w J3 pojawia się 5 podkategorii, w J4 już 4 w J5 odpowiednio 3 aż w końcu całkowicie znikają podkategorie – domyślam się, że chodzi o zablokowanie którejś komórki/zakresu ale której?
Hej, tak, w Twojej formule w ogóle nie ma dolarów. Zobacz we wpisie – tam jest to opisane. A tam gdzie masz nazwy – należy blokować wszystko 🙂
Hej
pytanie czy jest mozliwość aby nie tworzyć dwóch list zależnych, tylko zamiast tego podac zakres do ktorego ma sie odwoływać?Czyli mamy:
LISTA WYNIK
A 3
A 1
A 2
B 1
I wtedy lista rozwijana miała by być tylko na z kolumny WYNIK, w zalezności od tego czy szukana bedzie A lub B.
Oj, nie rozumiem pytania ;(
Dzień dobry,
potrzebuje by moje kategorie i podkategorie były w oddzielnym arkuszu niż komórki w których będę mieć listę rozwijalną.
(Chodzi o to, że chce zrobić budżet domowy z 12 arkuszami z miesiącami oraz jeden arkusz w którym będę mieć wszystkie kategorie – aby zmiana w kategorii spowodowała zmiany w tych 12 arkuszach).
Udało mi się wyczytać, że w Excelu powyżej 2010 nie ma tego problemu. Niestety ja mam Excel 2007 🙁
Znalazłam w internecie jakieś wyjaśnienie jak to obejść ale nie zrozumiałam…
Mogłaby Pani pomóc i napisać jak mogę to obejść?
Hej, opisuję ten problem w powyższym artykule :). Należy nazwać zakresy (pkt. 1 w artykule), które mają być wyświetlane. Jeśli tego nie zrobimy – faktycznie starsze wersje Excela tego nei zrozumieją. One nie umieją w listach rozwijanych odwoływać się do zakresów w innych arkuszach. Natomiast, jeśli takie zakresy są nazwane – już umieją 🙂
Dzień dobry,
potrzebuje by moje kategorie i podkategorie były w oddzielnym arkuszu niż komórki w których będę mieć listę rozwijalną.
Udało mi się wyczytać, że w Excelu powyżej 2010 nie ma tego problemu. Niestety ja mam Excel 2007 🙁
Znalazłam w internecie jakieś wyjaśnienie jak to obejść ale nie zrozumiałam…
Mogłaby Pani pomóc i napisać jak mogę to obejść?
A ja gdzieś robię błąd tylko jestem raczkującym excelowcem i za pioruna nie mogę dojść co i jak z tym źle zrobiłem.
Czy mogliby Państwo rzucić na to okiem :
=PRZESUNIĘCIE($H$2;PODAJ.POZYCJĘ(A12;ListaRob;0);0;LICZ.JEŻELI(ListaRob;A12);1)
Hmm… wkleiłam tę funkcję do pliku i działa… A w czym konkretnie jest problem? Lista się nie rozwija?
No właśnie nic się nie rozwija i nie wiem gdzie tkwi przyczyna …
A taką formułę utworzyłem:
=PRZESUNIĘCIE(Klienci!$D$1;PODAJ.POZYCJĘ($E$11;Klienci!$D$2:$D$25;0);0;LICZ.JEŻELI(Klienci!$D$2:$D$25;$E$11);1)
Hej, ok. A powiedz z jakiej wersji Excela korzystasz?
Mam wersję 2013
Ok, rozumiem. A możesz podesłać plik? Tak po samej formule to ciężko…
Oczywiście, z miłą chęcią,
Podglądałem też twoje rozwiązania na youtube, i nic nie znalazłem podobnego więc jestem w kropce troszkę 🙂
🙂
A czy można osiągnąć ten efekt bez wymogu sortowania kategorii? To mi wiele (jeśli nie wszystko) komplikuje 🙁 Powiedzmy, że w jednej zakładce mam listę posiłków. Każdy posiłek ma swoje id, nazwę, typ (śniadanie, obiad, deser, kolacja) i parę innych, nieistotnych pól, które pominę. Oczywistym jest, że raz pojawi się np. obiad, poniżej może być kolacja, później wpisano śniadanie, później znów obiad itp. Na to wpływu nie mam. A sortować nie mogę, bo mi się sypnie autoincrementowany licznik (id). W innej zakładce chciałabym zrobić listę rozwijalną, ale dla samych śniadań. Potem drugą listę rozwijalną dla samych obiadów itp. Ponieważ – jak rozumiem – w mojej sytuacji to typ posiłku jest kategorią, a podkategorią jest nazwa posiłku, nie mogę skorzystać z tutoriala, bo nie jestem posortowana po typie. Jest na to jakaś rada?
Hej, tak, można to zrobić za pomocą VBA, pisałam o tym tutaj. A jak masz najnowszego Excela, z nowym mechanizmem obliczeniowym, to tam jest cudowna funkcja SORT, która załatwi sprawę ;).
Dziękuję! Pokombinuję z tym 🙂
PS. Coś refresh na tej stronie nie działa należycie, bo dopiero jak napisałam drugi komentarz, zobaczyłam, że ten pierwszy się opublikował, a czekałam kilka dni i go nie było(zakładka otwarta w przeglądarce).
Super 🙂
Odnośnie refresha – może to kwestia tego, że zatwierdzam ręcznie komentarze i stąd opóźnienie. Tak podejrzewam 🙂
A jak zrobić to samo ale bez wymogu sortowania pierwszej kolumny?
PS. Mój pierwszy komentarz, w którym dokładnie opisałam problem, nigdy nie został opublikowany! 🙁
Hej, widziałam, że już znalazłaś ten wpis: https://malinowyexcel.pl/zalezna-lista-rozwijana-z-nieposortowanych-danych-zdarzenie-vba/ 🙂
Moi drodzy mam pytanie/problem:
Czy istnieje mozliwosc aby menu rozwijane pokazywalo rozne tabele w zaleznosci od wybranej opcji z listy?
Chodzi mi o stworzenie dokumentu w ktorym najpierw wybiera sie z listy potrzebna tabele i ta sie wyswietla, a przy wybraniu innej pokazuje sie ta inna.
Hej, ja bym tutaj zadziałała ze zdarzeniem VBA (na zmianę wartości w komórce) w połączeniu z PQ. VBA by odświeżało odpowiednie zapytanie PQ.
Mój słownik był trochę bardziej rozbudowany, dlatego zamiast słownika „pionowego – dane do listy rozwijalnej pobierane z wierszy kolumny sąsiadującej”, stworzyłem sobie słownik „poprzeczny – do listy rozwijalnej pobierane z kolumn pop prawej stronie, dane umieszczone w jednym wierszu – „. Oczywiście została stosownie zmodyfikowana funkcja (przykład jak poniżej).
Ze względu na to, że np. w jednym wierszu zakres danych pochodził z 5 kolumn, a w drugim z 28, a zakres wszystkich danych pochodził z 30 kolumn, to funkcja została lekko zmodyfikowana w postaci zagnieżdżenia LICZ.PUSTE co pozwoliło wyeliminować z listy rozwijalnej puste pola.
=PRZESUNIĘCIE(słownik2!$T$1;PODAJ.POZYCJĘ(E1689;ST_ENG3a;0);0;1;30-LICZ.PUSTE(PRZESUNIĘCIE(słownik2!$T$1;PODAJ.POZYCJĘ(E1689;ST_ENG3a;0);0;1;30)))
Super 🙂
Witam wszystkich
Mam taki problem mianowicie próbuje zrobić food cost do restauracji i potrzebuje zrobić żeby po wyborze z listy rozwijanej np hamburgera w tabeli mógł wpisywać składniki przypisane tylko do tej pozycji … jak potem wybiorę pizze to produkty się zmieniają w zależności od wyboru.
Mam ustawione 3 arkusze..
1 Arkusz – Tabela
2 Arkusz – Pozycje Menu
3 Arkusz – Produkty
W pierwszym arkuszu mam zrobioną listę rozwijaną menu w której potrzebuje umieścić składniki w zależności od dania z listy menu w drugim arkuszu (hamburger, pizza… ) a w trzecim arkuszu mam wszystkie produkty (ser, pieczarki …)
Hej, czy dobrze rozumiem, że w tabeli chcesz wybierać potrawę (hamburger), chcesz, aby na podstawie tego wyboru wyświetliły się składniki na tę potrawę (przypisania zdefiniowane w arkuszu „Pozycje menu”). A jak się wyświetlą, to mają się podpowiedzieć ceny poszczególnych produktów? Czy może cena ogólna tego produktu, na podstawie cen składników (zakładam, że ceny mamy w ark „produkty”)?
Chciałbym listy zależne wykorzystać w moim pliku z budżetem, ale w nim każdy wydatek to oddzielny wiersz (i ten wydatek przypisuje do kategorii i podkategorii). W związku z tym opisany sposób nie zadziała, bo musiałbym mieć oddzielną nazwę na każdy wiersz. Zastanawiam się, czy mogę ten problem rozwiązać bez VBA, ale obawiam się, że nie (a zależy mi na braku makr, ponieważ często edytuje plik w Excelu na telefonie, gdzie VBA nie działa). Jakieś pomysły 🙂 ?
Hej, poprawiłam trochę ten artykuł, bo chaos straszny w nim był. Zobacz teraz – moim zdaniem zaprezentowane w nim rozwiązanie zadziała (tak, trzeba wpisać każdy wydatek w oddzielny wiersz).
Witam wszystkich,
siedzę nad tym już prawie od tygodnia i chyba się poddam. Czy można to przełożyć na Arkusze Google? Podejrzewam, że nie, ale może znajdzie się ktoś, kto to „ogarnie”?
Napiszę o tym post. Ukaże się jutro, tj. 4.02.2020 :). Generalnie tak samo 🙂
I artykuł już jest: https://malinowyexcel.pl/zalezna-lista-rozwijana-w-google-sheets
Daj znać, czy działa 🙂
Działa! Wielkie dzięki!
Super, cieszę się 🙂
Witam serdecznie, „operację” próbuję wykonać w Excelu w języku angielskim, oto moja formuła: =OFFSET(Picklist!$M$2,MATCH($AN$2,Picklist!$L$3:$L$14,0),0,COUNIF(Picklist!$L$3:$L$14,$AN$2)).
Dane są w jednym arkuszu, drop down menu ma być w drugim.
Błąd który otrzymuję: „named range you specified cannot be found”. Próbowałam ponazywać zakresy, bez skutku.
Co może być nie tak?
Z góry dziękuję,
Ewa
Hmmmm… rozumiem, że ta formuła jest nazwana? I lista rozwijana, w źródle, powinna mieć własnie odwołanie do tej nazwy. A zobacz w menedżerze nazw (Ctrl + F3) czy ta nazwa jest? Może z jakiegoś powodu się nie zapisała?
A czy dałoby się ulepszyć taką listę zależna o jakieś stałe pozycje? Czyli że jakieś elementy na liście są zawsze a niektóre zależnie od wyboru winnej komórce.
Generalnie chodzi o to by lista zawierała zarówno elementy stałe jak i elementy uzależnione od wyboru w innej komórce.
To ciekawy pomysł – pomyślę!
Na pewno poprawność danych nie chce przyjąć prostego wymienienia zakresów ;(
Próbuję zastosować to rozwiązanie, ale nie zwraca mi wyników z kolejnej kolumny, tylko skraca listę do ilości pozycji poniżej pierwszego znalezionego rekordu. Nie wiem co robię źle, wydaje mi się, że coś źle wpisuję w formule przesunięcie.
A sprawdź jak masz dolary wpisane? Może to tego kwestia?
Hej, mam taki problem. Utworzyłem kalendarz.
Data Opis
01.01.2021 Nowy Rok (tło czerwone z formatowania warunkowego, opis z „wyszukaj.pionowo z nazwanego zakresu „Święta”)
02.01.2021 Sobota „X”(tło niebieskie z form. war. opis z „dzień.tyg”)
03.01.2021 Niedziela „X”(tło niebieskie z formatowania warunkowego, opis jw.)
04.01.2021 Poniedziałek dzień pracujący
…
…
31.12.2021 Sylwester dzień pracujący
Formuła w każdej komórce kolumny „Opis”:
=JEŻELI(DZIEŃ.TYG($A2;2)>=6;”X”;WYSZUKAJ.PIONOWO($A2;Swieta;2;FAŁSZ))
Chciałbym aby w powyższej formule dopisać:
*magic*
i uzyskać w dni robocze listę rozwijalną z miejscowościami gdzie planuję być danego dnia. Wiem że mogę to zrobić „ręcznie” i tak właśnie mam, że w dni robocze mam ręcznie wkopiowane listy rozwijalne. Ale jak zrobić by komórka „wiedziała”, że jest dzień roboczy o trzeba automatycznie pokazać listę rozwijalną?
Bardzo serdecznie pozdrawiam Piotr
Hej, to trzeba napisać formułę w sprawdzaniu poprawności, która będzie podbierała odpowiedni zakres w zalezności od dnia. Zwykłe JEZELI wystarczy, tylko trzeba jej podać 2 zakresy „dni zwykłe” i święta. Zadziała 🙂
Cześć, szukam czegoś takiego tylko na odwrót. tzn żeby po wybraniu z listy w jednej komórce np „KINO” w drugiej komórce pojawiała się „ROZRYWKA”
z góry dziękuję i pozdrawiam
Funkcja np. WYSZUKAJ.PIONOWO 🙂
Cześć, mam problem z listą rozwijaną, mianowicie chodzi o zmianę wartości w pierwszej liście rozwijanej (kategorie) z „rozrywki” na „jedzenie”, druga lista zależna od pierwszej nadal pokazuje poprzedni wybór (podkategorii) przypisany do „rozrywki”. Co zrobić aby w tym okienku wystąpił błąd lub pojawiło się puste okno, proszę o pomoc.
Z góry dziękuje i pozdrawiam.
Hej :). Zobacz proszę tutaj: https://malinowyexcel.pl/zalezna-lista-rozwijana-automatyczne-usuwanie-podkategorii-po-zmianie-kategorii/
Świetny materiał, działa ale nie wszystko. Jak pisze formule zależnej listy w komórce to podaje mi wyniki w zależności od wybranej nadrzędnej kategorii ale w momencie wklejania tej formuły do stworzenia rozwijanej listy w poprawności danych krzyczy, że formuła jest niepoprawna
Rozwiązane, dodawały się bez sensu $
Super, bardzo się cieszę 🙂
Witam,
Mam pytanie odnośnie wywołania warunkowego UserForm jeżeli wartość TextBox5 w innej UserForm jest większa niż np. 20
Proszę o pomoc
Diabeł tkwi w szczegółach… wygląda to na prostego if-a, ale pewnie sa jeszcze jakieś inne warunki brzegowe?
Już się ucieszyłam, że wszystko działa – są listy rozwijane zależne, ale za każdy razem jak zamknę plik, to po jego otwarciu od nowa muszę klikać w Sprawdzaniu poprawności danych opcję zastosuj te zmiany do wszystkich komórek z takimi ustawieniami.
Z czego to może wynika, bo mocno upierdliwe to jest?
Hmmmm… to dziwne, że w tym zastosowaniu CI się tak dzieje. Jest znany problem, bug, ale w sytuacji formuły tablicowej w sprawdzaniu poprawności… dla zwykłej formuły powinno działać!
A jaką masz wersję Excela?
PS to prawda, że to musi być upierdliwe 😉
Nie działa niestety 🙁 Pierwsza lista z kategoriami działa dobrze, druga lista, w moim przypadku z produktami, nie działa. W oknie 'Sprawdź poprawność danych’ tylko pokazuje, że źródło daje w wyniku błąd. Formuła w arkuszu pokazuje #N/D!. Myślę, że wszystkie odwołania w formule wprowadziłam prawidłowo i nie mam pojęcia co może być przyczyną. Korzystam z Excel 2010
=PRZESUNIĘCIE(D12;PODAJ.POZYCJĘ(C2;lista;0);0;LICZ.JEŻELI(lista;C2);1)
korzystam z takiej formuły gdzie komórka D12 to nagłówek mojej kolumny z podkategoriami, C2 to komórka z pierwszą listą rozwijaną z kategoriami, lista to zakres kategorii w tabeli roboczej z danymi
Hej. Czy dałoby się taki sposób (albo chociaz podobny tylko nie znalazłam nic innego co by pasowało) użyć do czegoś takiego:
Mam jakąś listę numerów (powiedzmy od 1 do 100). Do każdej z liczb jest przypisana inna nazwa (1 to A, 2 to B, 3 to C itd).
Jak zrobić żeby po wpisaniu w kolumnę A wartości 1, automatycznie wpisywała się w kolumnę B przypisana do niej literka? Albo chociaż ta jedna opcja pojawiała się na liście rozwijanej. I takie samo rozwiązanie dla wielu wierszy poniżej. Jakiś pomysł?
Tu jest chyba coś podobnego https://www.excelforum.pl/topics2/lista-rozwijana-z-warunkami-vt73101.htm