Sprawdzanie poprawności i lista rozwijana bez powtórzeń

by Marcin

Mam prosty skoroszyt służący przygotowywaniu zamówień. Produkty wybierane przy pomocy listy rozwijanej zrobionej za pomocą „Sprawdzania poprawności”. Całość wygląda mniej więcej tak, jak na rysunku poniżej.

Sprawdzanie poprawności (lista rozwijana bez powtórzeń)

Wczoraj pomyślałem sobie, że strasznie fajnie by było gdyby można ograniczyć listę wyboru tak, aby znajdowały się na niej tylko produkty niewybrane wcześniej. Jeżeli wybrałem Produkt1 w pierwszym wierszu chciałbym, aby nie było możliwości jego wyboru w wierszach kolejnych.

Przypomniało mi się, że kiedyś widziałem coś podobnego. Faktycznie – krótkie poszukiwanie w folderze „ciekawe formuły” i jest ! Rozwiązanie pochodzi oryginalnie ze strony http://www.contextures.com.

Do rzeczy jednak.

Wszystko, co musisz zrobić to przefiltrować listę dostępnych produktów (biorąc pod uwagę produkty już wybrane), a następnie właśnie tę przefiltrowaną listę „podpiąć” pod listę rozwijaną.

Na początek, dla ułatwienia, nadajmy nazwy kilku zakresom, z których będziesz później korzystał. Przede wszystkim zakres z wybranymi już produktami W moim przypadku (patrz rysunek powyżej) byłby to zakres B4:B7, w skoroszycie o nazwie „Zamowienie”. Zamiast jednak ustawiać go na „sztywno” o wiele lepiej zdefiniować zakres dynamicznie tak, aby dostosowywał się do ilości wybranych produktów. Stwórz więc nazwę „wybrane”, która odwoływać się będzie do:

=PRZESUNIĘCIE(Zamowienie!$B$4;0;0;ILE.NIEPUSTYCH(Sheet1!$B:$B)-1;1)

Teraz lista wszystkich dostępnych produktów (będzie w drugim skoroszycie „Produkty”). Nadaj jej nazwę „wszystkie”, która odwoływać się będzie do:

=PRZESUNIĘCIE(Produkty!$B$4;0;0;ILE.NIEPUSTYCH(Sheet2!$B:$B)-1;1)

Tyle przygotowań. Zabierzmy się za odfiltrowywanie listy. W skoroszycie „Produkty”, w komórce C4 wprowadź funkcję:

=JEŻELI(LICZ.JEŻELI(wybrane;B4)>=1;””;WIERSZ())

i przeciągnij w dół aż do końca zakresu z nazwami produktów. W rezultacie, dla każdego produktu (w komórce obok), który został już wybrany, funkcja zwróci pusty ciąg. Dla pozostałych produktów – numer wiersza, w którym znajduje się dany produkt (co później sprytnie wykorzystamy).

Sprawdzanie poprawności (lista rozwijana bez powtórzeń)

Jako wynik powinieneś otrzymać to, co widzisz na rysunku powyżej. Od razu, dla ułatwienia, nadaj nazwę „uzyte” zakresowi komórek z wartościami zwróconymi przez wspomnianą formułę (tak jak poprzednio zakres generowany będzie dynamicznie).

Wszystko wygląda już w miarę dobrze, widać wyraźnie, które produkty zostały już wybrane (1,3,4,5), a które nie. Jeżeli udałoby się teraz przesunąć wszystkie puste komórki na koniec zakresu, łatwo byłoby wykorzystać tę nową listę w sprawdzaniu poprawności.

Cel osiągniesz korzystając z następującej formuły tablicowej (wprowadzonej do komórek D4:D15 skoroszytu „Produkty”)

=INDEKS($B:$B;MIN.K(uzyte;WIERSZ(ADR.POŚR(„1:”&ILE.WIERSZY(uzyte)))))

Zamiast jednak pustych komórek formuła wyprodukowała komunikaty błędów. Łatwo się ich pozbyć rozbudowując nieco wspomniana formułę tablicową:

=JEŻELI(WIERSZ(wszystkie)-WIERSZ(B$4)+1>ILE.LICZB(uzyte);””;INDEKS($B:$B;MIN.K(uzyte;WIERSZ(ADR.POŚR(„1:”&ILE.WIERSZY(uzyte))))))

W wyniku otrzymasz ciągłą listę wszystkich, nieużytych jeszcze, nazw produktów.

Sprawdzanie poprawności (lista rozwijana bez powtórzeń)

Pozostaje tylko zdefiniowanie nazwanego zakresu ( z produktami do wykorzystania), którego użyjemy w sprawdzaniu poprawności.

Zakres nazwiemy „wynik” i zdefiniuj go następująco:

=PRZESUNIĘCIE(Sheet2!$D$4;0;0;ILE.NIEPUSTYCH(uzyte)-LICZ.PUSTE(uzyte);1)

Zwróć uwagę, że określając ilość wierszy zakresu konieczne jest zastosowanie dwóch funkcji:
ILE.NIEPUSTYCH(uzyte) oraz LICZ.PUSTE(uzyte).

Pierwsza uwzględnia bowiem także te komórki, które tylko wyglądaja na puste (np. wynik działania formuł wstawiających do komórki „” – jak to ma miejsce w naszym przypadku). Druga funkcja z kolej, zupełnie odwrotnie – traktuje te komórki jako puste (tak jak my je widzimy). Stąd konieczność odjęcia tych pustych komórek od wartości zliczonej przez funkcję ILE.NIEPUSTYCH(uzyte).

Na koniec wystarczy, że odpowiednio zmodyfikujesz ustawienia sprawdzania poprawności w pierwszym skoroszycie (tak, aby wykorzystać nową listę) i … możesz cieszyć się nową, praktyczną funkcjonalnością.

Sprawdzanie poprawności (lista rozwijana bez powtórzeń)

{ 11 comments… read them below or add one }

Slwk

Witam,

Zainteresował mnie powyższy przykład i postanowiłem zasięgnąć porady w swoim problemie.
Dane w Arkuszu1: 1.Produkt. 2.Cena Produktu. 3.Akcesoria do Produktu 4. Cena Akc. 5. Suma.
Kolumny w Arkuszu2 są następujące.
1.Produkt. 2.Cena Produktu. 3.Akcesoria do Produktu (akcesoria są przyporządkowane tylko do jednego produktu. Nie ma akcesoria, które są przyporządkowane do dwóch Produktów. 4. Cena Akcesoriów.5.Suma.

Chciałbym zrobić tak by domyślnie w Arkuszu2 w listach rozwijalnych były puste pola.Po Po wybraniu Produktu w drugiej kolumnie pojawia się przypisana do niego cena, a w następnej kolumnie jest lista rozwijalna akcesoriów przypisanych tylko do tego produktu.Ceny oczywiście podciągną się z wykorzystaniem odpowiedniej funkcji.

Dodatki:
Zależy mi by po wyborze produktu i odpowiedniego akcesoria łatwo przyszło wyczyścić komórki poprzez np. wybranie w kolumnie Produkty pustej komórki z listy rozwijalnej.

Produkt1Akcesoria1.
Jak najłatwiej dodawać wiersze z w formułami za pomocą przycisku.

A może najlepiej wyczyścić wartości nie czyszcząc formuł?
Rozwiązanie bez makr poproszę.
Do tej pory wykorzystywałem funkcję przesunięcie, listy rozwijalne. Nie mniej jednak utknąłem w kilku miejscach. Zwłaszcza wyszukanie odpowiedniego akcesoria oraz wyczyszczenie wiersza.

Dziękuję za pomoc.
Pozdrawiam

katharinaa

Witam,interesują mnie rozwijane listy z niepowtarzającymi się elementami..próbowałam prześledzić Twój przykład ale nie wszystkie kroki są dla mnie zrozumiałe…
„Stwórz więc nazwę „wybrane”, która odwoływać się będzie do:

=PRZESUNIĘCIE(Zamowienie!$B$4;0;0;ILE.NIEPUSTYCH(Sheet1!$B:$B)-1;1)”

Mam w dowolnym polu arkusza Zamówienie zdefiniować tak pole?
Jeśli to możliwe prosiłabym o przesłanie na adres e-mail tego przykładu w formacie xls abym mogła przeanalizować poszczególne kroki. Z góry dziękuję i pozdrawiam

Marcin

Katharinaa,

Poczytaj sobie w wolnej chwili o definiowaniu nazw w Excelu. Póki co tak na szybko:

Menu główne – > Wstaw -> Nazwa -> Definiuj.

Pojawi Ci się nowe okno z różnymi polami. W pole u góry wpisujesz nazwę (czyli „wybrane” w tym przypadku, w pole „Odwołuje sie do:” wpisujesz formułę. Klikasz OK (ewentualnie „Dodaj”).

Jeżeli idzie o przykład to zamieszczę go na blogu. Muszę się tylko zebrać w sobie. Nieco jestem jednak zmotywowany, ponieważ to już n-ta prośba o przykład dla tego konkretnie wpisu. Powinien pojawić się więc niebawem.

Marcin

katharinaa

Dziękuje za szybką odpowiedż, dzięki Twojej wskazówce poradziłam sobie z zefiniowaniem „wybrane” i „wszystkie” („wybrane” w skoroszycie „Zamowienie”, wszystkie w skoroszycie „produkty”? zgodnie z formułą opublikowaną na blogu), następnie w skoroszycie Produkty w komórce C4 próbowąłam zdefiniować funkcję i tu znów pojawił się błąd:/ Chyba poczekam aż na blogu pojawi się przykład…

A teraz pytanie z innej beczki, bo próbowałam w inny sposób stworzyć tę rozwijaną, niepowtarzającą sie listę (za pomocą fuknkcji =JEŻELI(LUB(A2=$C$2;A2=$C$3;A2=$C$4);””;A2 definiowaną w polu C2) gdzie A2 to element listy (jeżli lista będzie składać się z elementów A2,A3,A4 to przeciągam tę formułę do komórek poniżej, C2 (C3,C4 jeżli mamy elementy A3,A4) to kod dynamiczny..jeżli pracuje na jednym arkuszu to nie ma problemu natomiast jeżli chciałabym wyświetlać tę listę w innym arkuszu a kody i kody dynamiczne zdefiniować w innym to nie mogę skorzystać z polecenia Dane->sprawdzanie poprawności->Lista i ten pomysł w tym wypadku wydaje się być bezużyteczny,czy jest jakieś rozwiązanie by temu zaradzić?

inagol

Bardzo mi się to przydało. Dzięki.

Ale mam jedno pytanie, czy wyrażenie :

WIERSZ(ADR.POŚR(”1:”&ILE.WIERSZY(uzyte)))

służące po prostu do wygenerowania kolejnych liczb można zastąpić przez:

WIERSZ()-3.

Wydaje mi się, że efekt jest ten sam, chyba, że o czymś nie wiem.

Zakres dla formuły tablicowej (dotyczy kolumn C i D) czy tak, czy owak muszę zaznaczyć ręcznie. W tym momencie określenie dynamicznych zakresów (aczkolwiek bardzo sprytne) też nie przynosi aż tak wielkich korzyści.

student

Witam, mam prośbę w innych postach często udostępniasz plik źródłowy, mógłbyś wrzucić plik z przykładem odnośnie tego tematu?

pruzam

przyklad jest swietny, ale czy da sie jakos go poprawic tak, aby dodanie nowego produktu nie pociagalo za soba jakiej kolwiek innej modyfikacji w arkuszu?

maruhhh

piekna sprawa.

Bartek

=JEŻELI(WIERSZ(wszystkie)-WIERSZ(B$4)+1>ILE.LICZB(uzyte);””;INDEKS($B:$B;MIN.K(uzyte;WIERSZ(ADR.POŚR(„1:”&ILE.WIERSZY(uzyte))))))

Niestety ale nie dziala. Blad lezy w formule ADR.POSR, ale niestety nie czaje za bardzo.

Andzio

przyklad lux, super, hiper ale opis troszke zagmatfany
poprosilibym/smy link do oryginalnego rozwiazania, ze wpomnianego serwisu http://www.contextures.com

Piotr

Dzień dobry
Próbuję za pomocą formuł stworzyć podobny filtr, o którym jest artykuł powyżej. Nie mogę zrozumieć do czego się odnosi „1:” w adresie pośrednim.
=JEŻELI(WIERSZ(wszystkie)-WIERSZ(B$4)+1>ILE.LICZB(uzyte);””;INDEKS($B:$B;MIN.K(uzyte;WIERSZ(ADR.POŚR(„1:”&ILE.WIERSZY(uzyte))))))
Proszę o udzielenie wskazówki.
Z góry dziękuję za udzieloną pomoc
Z poważaniem
Piotr

Leave a Comment

Previous post:

Next post: