Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Lista pomijająca niewypełnione komórki

partner2001 · Exceloholic Wersja: Win Office 365 Posty: 245

Witam.
W komórce arkusza po kliknięciu na znacznik przy komórce rozwija mi się listę nazwisk stworzoną za pomoc sprawdzenia poprawności danych. Źródło tych danych jest w innym arkuszu, ale posiada formuły a nie tekst. Komórki te posiadają formuły i po spełnieniu pewnych warunków nazwisko pojawia się w komórce należącej do zakresu źródła komórek z których poprawność danych tworzy listę do wstawienia w komórce docelowej.
Jak zrobić, aby na tej liście rozwijanej pojawiały się tylko nazwiska a nie puste miejsca.
Jeżeli w źródle danych znajdują się nazwiska w postaci tekstowej (nie formuły) to po zaznaczeniu pola "ignoruj puste pola" lista nie pokazuje pustych (niewypełnionych) miejsc źródła.
A co zrobić, kiedy lista ta posiada w całym zakresie źródła formuły i raz jest 5 nazwisk a innym razem jest 50 nazwisk. Chciałbym, aby niezależnie od ilości wypełnionych nazwisk na liści pojawiały się tylko nazwiska a nie puste pola.

Bill Szysz · Wysłany: 29-07-2021, 13:11

partner2001, jeśli sądzisz, że "my tu som jasno(ciemno)widze" to się mylisz.
143 posty i nadal zasad nie znasz? Trochę wstyd :-P

Teraz mogę Ci tylko napisac, że pewnie funkcja FILTRUJ mogłaby pomóc.

Maciej Gonet · Wysłany: 29-07-2021, 13:15

Prosimy o załącznik. Problem jest zbyt złożony, żeby napisać odpowiedź "z pamięci".
A tworzenie własnego modelu danych na podstawie tego opisu jest dość karkołomne. Możesz dostać odpowiedź nieprzystającą do Twojego układu danych. Załącznik może być uproszczony, chodzi o to, żeby tam gdzie są formuły były formuły, tam gdzie stałe - stałe, a tam gdzie puste teksty - puste teksty.

umiejead · Wysłany: 29-07-2021, 15:35

No to jeszcze:

Cytat:

Dołączył: 27-08-2015, 12:41

Radosław Poprawski · Wysłany: 29-07-2021, 19:51

a może przedstaw cały twój schemat?
jak koledzy wyżej napisali - jest to trochę jak strzelanie po ciemku do czarnego celu, ale wydawać by się mogło ze PQ tez może rozwiązać ten problem.

Pytanie - dlaczego korzystasz z list rozwijanych a nie logiki w PQ aby otrzymać pełną gotową listę nazwisk?

partner2001 · Exceloholic Wersja: Win Office 365 Posty: 245

W arkuszu Protokół po wybraniu nr meczu wpisują się drużyny gospodarza i gości oraz data meczu i jego miejsce.
W polach "A" i "X" trzeba wybrać zawodników z listy rozwijanej zrobionej na podstawie listy poprawności danych. Lista ta ma swoje źródło w arkuszu kluby i zawodnicy. Zakres źródła to 50 nazwisk ale w niektórych klubach jest 8 zawodników a w niektórych prawie 50-ciu.
Po zaznaczeniu całego zakresu źródła pojawiają się wolne pola w liście w arkuszu Protokół.
Gdyby te listy były w postaci tekstowej np. Andrzej Adamczyk, Janusz Kowalski, Jan Robak itd. to zaznaczenie pola ignoruj puste rozwiązałoby problem, lista zawierała by tylko te nazwiska. U mnie jednak każde pole listy źródła jest zmienne i określone przez formułę więc nie jest puste i pomimo, że nie ma tam wartości jest wyświetlane puste miejsce w polu listy poprawności danych.
Załączam plik.

Protokół18.xlsm

Pobierz Plik ściągnięto 30 raz(y) 401.84 KB

Bill Szysz · Wysłany: 29-07-2021, 23:01

partner2001, nie przyglądałem się całości Twojego pliku, skupiłem się tylko na tym czego potrzebujesz. Zmieniłem reguły sprawdzania poprawności dla gości i gospodarzy. Dla gości wygląda teraz tak

Kod:

='kluby i zawodnicy'!$G$166#

odpowiednio dla gospodarzy.
W komórce G166 arkusza "kluby i zawodnicy" zmieniłem formułę na poniższą

Kod:

=FILTRUJ(M2:M51;M2:M51<>"")

i odpowiednio dla gospodarzy.
Plik wydaje się przekombinowany - i jeśli jest taka możliwość to unikaj funkcji ulotnych (np PRZESUNIECIE) bo ani się spostrzeżesz a pliczek stanie się ociężały (wolno reagujący)

Protokół18_BS.xlsm

Pobierz Plik ściągnięto 36 raz(y) 395.63 KB

Maciej Gonet · Wysłany: 29-07-2021, 23:32

Rozumiem, że ten plik jest przygotowany z myślą o wykorzystywaniu przez wiele osób.
Byłoby zatem wskazane, żeby jasno określić, w których wersjach Excela ma on działać.
Tak się niezbyt szczęśliwie składa, że właściwości list rozwijanych kontroli poprawności zmieniały się w miarę dojrzewania Excela.
Bill proponuje rozwiązania, które będą działać póki co tylko w Excelu 365. Natomiast mnie się wydaje, że problem który opisujesz (wyświetlanie pustych linii na liście kontroli poprawności, gdy w danych są puste teksty) nie występuje w Excelu 365. Ten problem występował we wcześniejszych wersjach (nie pamiętam do której, bo nie mam teraz jak sprawdzić).
Należałoby zatem doprecyzować oczekiwania w stosunku do tego programu.

Edit MG: Faktycznie w Excelu 365 są te puste wiersze. To zostało zmienione w Excelu Online, a ja zasugerowałem się (bez sprawdzenia), że w 365 jest tak samo. Niestety, co wersja to inaczej.

partner2001 · Exceloholic Wersja: Win Office 365 Posty: 245

Dzięki Bill.
A czy możesz mi powiedzieć jak wyeliminować powtarzające się nazwiska w tych listach rozwijanych inaczej niż za pomocą funkcji przesunięcie. Bardzo chętnie skorzystam z lepszego rozwiązania, ale go nie znam. Funkcja przesunięcie eliminuje mi z listy wybranych już zawodników, żeby przez pomyłkę nie wybrać tych samych zawodników.
Twoje rozwiązanie jest super, ale nie eliminuje powtarzających się nazwisk.

Mćku. Problem, który opisuję występuje również w Excelu 365, bo ja z takiego korzystam.

Bill Szysz · Wysłany: 30-07-2021, 18:26

partner2001, nie zauważyłem, że odpowiedziałeś tutaj.....i zadałeś dodatkowe pytanie.
Nie ma problemu. W załączniku pokazuję jak to zrobić. Jest to jednak dość zaawansowane rozwiązanie (dodatkowo wykraczające poza excela, w pewnym sensie - drugi argument funkcji FILTERXML) więc proszę nie pytaj dlaczego tak a nie inaczej bo....dziś piąteczek :mrgreen:

Sprawdź czy Ci odpowiada i jeśli odpowiedź będzie twierdząca, pousuwaj niepotrzebne formuły w pliku (szczególnie te z PRZESUNIĘCIEm)

Protokół18_BS_2.xlsm

Pobierz Plik ściągnięto 42 raz(y) 395.92 KB

partner2001 · Exceloholic Wersja: Win Office 365 Posty: 245

Bill - rewelacja. :clap

Co prawda nie mam zielonego pojęcia o co chodzi - (dzisiaj nie zadam tego pytania - bo piątek, piątunio, piąteczek i czas na :beer

) i co to za formuła ale rozwiązanie jest genialne.
Jakbyś kiedyś miał troszkę czasu na zbyciu to może kiedyś wróciłbyś do tematu i wytłumaczył mi jak z tego w przyszłości skorzystać i wykorzystywać ten algorytm być może w innych podobnych rozwiązaniach.
Serdeczne dzięki.
Czy ta funkcja działa w niższych wersjach excela czy tylko od wersji 2019 (365)?

partner2001 · Exceloholic Wersja: Win Office 365 Posty: 245

Cześć Bill, pozostali eksperci i forumowicze.
Minęło trochę czasu więc powracam do tematu z prośbą o opis zastosowanego przez Ciebie (genialnego) rozwiązania. Czy znalazł byś trochę czasu (lub inny expert excela) i opisał zastosowane rozwiązanie na tyle dokładnie abym ja i lub inni forumowicze mogli je zastosować do innych projektów.
Z góry dziękuję w imieniu swoim i innych osób które będą mogły skorzystać z Twojego rozwiązania.

Maciej Gonet · Wysłany: 17-09-2024, 10:56

Myślę, że w komórce P114 możesz alternatywnie zastosować formułę:

Kod:

=FILTRUJ(G2:G51;(G2:G51<>"")*(LICZ.JEŻELI(Protokół!D13:D18;G2:G51)=0))

Powinna ona również zwrócić listę bez wykorzystanych już nazwisk.
Myślę, że ta formuła jest w miarę zrozumiała. Warunek zastosowany w funkcji FILTRUJ sprawdza czy poszczególne wpisy z listy G2:G51 występują w zakresie Protokół!D13:D18. Jeśli nie występują, funkcja LICZ.JEŻELI zwraca 0, a to powoduje, że funkcja FILTRUJ je przepuszcza. Pierwsza część warunku

Kod:

G2:G51<>""

jest istotna wtedy gdy lista Protokół!D13:D18 jest w całości wypełniona i nie zawiera pustych pól.

partner2001 · Exceloholic Wersja: Win Office 365 Posty: 245

Dziękuję Maćku. Twoje rozwiązanie też działa i jest ok. Ale ja byłem ciekawy wykorzystania propozycji Billa z wykorzystaniem funkcji

Kod:

=FILTERXML("<x><i>"&POŁĄCZ.TEKSTY("</i><i>";;G166#;Protokół!AO13:AO20)&"</i></x>";"//i[not(following::*=. or preceding::*=.)]")

Bill Szysz · Wysłany: 17-09-2024, 15:17