O sprawdzaniu poprawności, listach i grupowaniu

by Marcin

Sprawdzanie poprawności - podkategorieRóżnego rodzaju listy („czegoś”,a nie „listy do”) mają to do siebie, że potrafią być długie i pogmatwane. Oczywiście są pewne wyjątki (lista z odpowiedziami „Tak/Nie”), ale co do zasady mają one tendencję do rozrastania się. Długa lista z kolei nieco odstrasza użytkownika, a poza tym trudniej na niej znaleźć to, czego się szuka. Weźmy na przykład listę różnego rodzaju czasopism, z której musimy coś wybrać.

Sprawdzanie poprawności - podkategorie

Niby się da, ale aż prosi się o to, aby to jakoś pogrupować.

Sprawdzanie poprawności w akcji

Podejść do problemu można na kilka sposobów, ja dzisiaj chciałbym napisać o jednym z prostszych (z wykorzystaniem Sprawdzania Poprawności) jednak z kategorii tych, na które często się nie „wpada”.

Jak zapewne wiesz (jeżeli nie, to z całą pewnością powinieneś o tym poczytać) jako kryterium poprawności możesz wybrać listę i jako źródło podajesz wtedy odpowiedni zakres z wartościami, które powinny się na liście znaleźć. Tak ja na rysunku poniżej.

Sprawdzanie poprawności - podkategorie

Można jednak pójść dalej i np. przy pomocy funkcji JEŻELI uzależnić wybór zakresu od określonego kryterium. W moim przypadku uzależnię to od już wybranej z listy wartości.

Jeżeli wybrano „Komputerowe” funkcja zwróci, jako źródło listy, zakres z tytułami czasopism komputerowych. Jeżeli wybraną wartością będzie „Biznesowe” – z tytułami czasopism o tematyce biznesowej. I tak dalej.

Na początek dla ułatwienia ograniczę się tylko do dwóch głównych kategorii. Później pokażę jak można liczbę kategorii zwiększyć bez gmatwania się z olbrzymią ilością funkcji JEŻELI.

Sprawdzanie poprawności - podkategorie

Odpowiednia funkcja wygląda następująco:

=JEŻELI(LUB($F$11=”Lista kategorii”;$F$11=””);KategorieGlowne;JEŻELI($F$11=”Biznesowe”;KsiazkiPodkategorie;CzasopismaPodkategorie))

Gdzie odpowiednie, nazwane, zakresy zdefiniowane są następująco:

Sprawdzanie poprawności - podkategorie

W komórce F11 znajduje się oczywiście nasza lista, z której mamy wybierać (pogrupowane w kategorie) wartości.  Proszę nie zwracać uwagi na nazwy. Zrzuty ekranu przygotowywałem wczoraj, kiedy jako przykład kategorii głównych wykorzystywałem książki i czasopisma. Jakoś nie miałem już dzisiaj nastroju zmieniać obrazków. Zostaje tak jak jest. W naszym przykładzie „KsiazkiPodkategorie” to tak naprawdę magazyny komputerowe, a CzasopismaKategorie to magazyny biznesowe. Zasadniczo proszę nie przywiązywać wagi do tego jak nazwałem zakresy. Nazwa to tylko nazwa – ważne jest zrozumienie zasady działania.

W zasadzie to tyle – powinno działać.

To samo z funkcją WYBIERZ

Wszystko robi się nieco bardziej skomplikowane kiedy chcemy mieć więcej kategorii głównych. Ilość funkcji JEŻELI zwiększa się wtedy niebezpiecznie kosztem czytelności (no i oczywiście miejsca). Skoro więc funkcja JEŻELI sprawia problemy pozbędziemy się jej i skorzystamy z funkcji WYBIERZ. Ponieważ funkcja wstawiana w pole „źródło” nie może być nieskończenie długa uprościmy też nazwy zakresów. Ostatecznie moja funkcja wygląda tak:

=JEŻELI(LUB($G$11=”Lista kategorii”;$G$11=””;CZY.BŁĄD(PODAJ.POZYCJĘ(G11;KGT;0)));KGT;WYBIERZ(PODAJ.POZYCJĘ(G11;KGT;0);KP;CP;IP))

Tym razem lista znajduje się w komórce G1.

Jak to działa

Funkcja WYBIERZ ma za zadanie wybrać (wskazać) jeden z podanych zakresów. Jako drugi argument podajemy więc listę zakresów z podkategoriami. Jak widzisz, w moim przykładzie, jest to: „KP;CP;IP”. Odpowiada to odpowiednio zakresom z listą magazynów komputerowych, biznesowych i innych (można oczywiście dodać więcej). Wszystko, co teraz pozostaje to „mądry” pierwszy argument, który ma wskazać konkretny zakres. Oczywiście wybrany ma zostać zakres odpowiadający wybranej kategorii. Ja skorzystałem z funkcji PODAJ.POZYCJĘ. Przy jej pomocy przeszukujemy listę kategorii w poszukiwaniu wartości wybranej z listy. Pozycja tej wartości na liście kategorii określa numer kolejnego zakresu do podstawienia do listy. Oczywiście wszystko wymaga nieco organizacji i trzymania się pewnego ustalonego porządku, ale docelowo … działa.

Jeżeli całość brzmi trochę zagmatwanie, to sie mimo wszystko nie przejmować. Tak naprawdę jest to bardzo proste. W razie wątpliwości, jak zwykle, odsyłam do załączonego pliku z przykładem. Jak zwykle także – zachęcam do dzielenia się Waszymi pomysłami oraz do wymiany komentarzy.

Marcin

Download

{ 7 comments… read them below or add one }

ADNER

Świetny i przydatny przykład, jednak mam kłopot z „zaimplemetnowaniem” go w Excell 2007 w troszkę zmienionej wersji.

Chciałbym użyć metody do zastosowania dla wielu wierszy, bez konieczności wpisywania dla każdego z nich adresu komórki „referencyjnej”, dlatego zuniwersalniłem wybieranie komórki.

W pierwszej kolumnie wybieramy typ (zakres: TypDostaw), druga ma umożliwiać wybór pozycji z danego podtypu (zakresy: PELE,PAPA,PPLC).

Formuła wpsiana w komórce lub poprawności danych:

=WYBIERZ(PODAJ.POZYCJĘ(ADR.POŚR(ADRES(WIERSZ();5;4);1);TypDostaw;0);PELE;PAPA;PPLC)

niestety zwraca błąd #ARG! i nie daje się zastosować w Poprawności danych (lista).

Marcin

Czesc,

Myslisz, ze moglbys podeslac mi plik z przykladem tego co robisz? Jakos lepiej pracuje mi sie na czyms bardziej … uchwytnym. Mniej zgadywania 🙂

Marcin

ADNER

Wysłałem przykładowy plik na maila. Mam nadzieję, że pomoże w rozwiązaniu problemu. Może ktoś inny w przyszłości też trafi na taką przypadłość.

Krzysiek

Bardzo dobry przykład, z tym, że nie udało mi się go powtórzyć w praktyce 🙁
Czy możesz podesłać mnie również ten przykład lub go opublikować?

Tomek

Witam.
Nie owijając w bawełnę: Tak samo jak Marcin i Krzysiek totalnie nic nie zrozumiałem z przykładu i prosiłbym o gotowca z którego można coś wyczytać albo o jaśniejsze wyjaśnienie bo nie chciałbym po porostu przekopiować funkcji bo nie o to chodzi;)
Dzięki, Pozdrawiam.

barthez16

Witam.
Wie ktoś jak przerobić formułę dla więcej niż trzech możliwości tak, żeby druga lista była w innej komórce?

Świetna stronka.
Pozdrawiam 🙂

grzego40

Dobry opis, przykład rozgryzłem.
Czy wie ktoś, jak dodać trzeci poziom wyboru (np. nr danego czasopisma wybierany z listy)?

Leave a Comment

Previous post:

Next post: