Jak stworzyć listę rozwijaną bez powtórzeń — widzowie #101

Potrzebujesz stworzyć grupę list rozwijanych bez powtórzeń, czyli przykładowo jeśli wybierasz pracowników, to raz wybrany pracownik nie może się już pojawiać w innych komórkach należących do tej samej grupy.

Jak to osiągnąć potrzebujesz mieć przede wszystkim listę wszystkich pracowników (zawodników itp.).

Widzowie 101 - Jak stworzyć listę rozwijaną bez powtórzeń 01

Dla ułatwienia nasze dane są już przechowywane w tablicy, żeby zapewnić sobie automatyczny rozrost, gdy zostaną dodani nowi pracownicy.
Zacznijmy od tego, że musimy sprawdzić czy konkretny pracownik został już wybrany. My posłużymy się tutaj funkcją LICZ.JEŻELI i sprawdzimy, czy w zakresie komórek z listami został wybrany już konkretny pracownik.

=LICZ.JEŻELI(Listy!$B$2:$B$8;[@Nazwisko])

Widzowie 101 - Jak stworzyć listę rozwijaną bez powtórzeń 02

Przy wybranych nazwiskach pojawia się wartość 1, a przy niewybranych 0. Nam zależy bardziej na tych nie wybranych, więc posłużymy się funkcją JEŻELI, żeby ich rozróżnić. Excel wartość 0 traktuje jako FAŁSZ, a wartość 1 jako PRAWDA. Nam zależy, żeby dla 1 pojawiała się wartość FAŁSZ, a dla 0 porządkowy numer niewybranego pracownika.

Żeby obliczyć porządkowy numer niewybranego pracownika posłużymy się funkcją ILE.LICZB, która będziesz miała dynamiczny zakres od nagłówka tabeli ($B$1:B1). Patrzymy od nagłówka, żeby uniknąć odwołań cyklicznych, dlatego też będziemy musieli dodać jedynkę, żeby nasz ciąg zaczął się od wartości 1.

=JEŻELI(LICZ.JEŻELI(Listy!$B$2:$B$8;[@Nazwisko]);FAŁSZ;ILE.LICZB($B$1:B1)+1)

Widzowie 101 - Jak stworzyć listę rozwijaną bez powtórzeń 03

Jak już mamy ponumerowanych niewybranych pracowników, to w kolumnie obok (Lista Niewybranych) tworzymy listę tych niewybranych pracowników. Pomoże nam w tym funkcja INDEKS, której podamy zakres kolumny Nazwisko do wyboru, a funkcja PODAJ.POZYCJĘ, w której w kolejnych wierszach będziemy szukać coraz większej liczby (ciągu tworzonego za pomocą funkcji ILE.WIERSZY i rozrastającego się zakresu).

=INDEKS([Nazwisko];PODAJ.POZYCJĘ(ILE.WIERSZY($C$2:C2);$B$2:$B$15;0))

Widzowie 101 - Jak stworzyć listę rozwijaną bez powtórzeń 04

Dzięki powyższej formule uzyskaliśmy listę niewybranych pracowników i błędy #N/D! na koniec listy, ale nimi nie musimy się w ogóle przejmować.
Na podstawie tych obliczeń możemy stworzyć listę rozwijaną. Wykorzystamy do tego funkcję PRZESUNIĘCIE od pierwszego niewybranego pracownika (komórki C2). Z tej komórki nie chcemy się nigdzie przesuwać, ale potrzebujemy zmienić dynamicznie rozmiar w zależności od ilości nie wybranych pracowników. Ta wartość, to maksymalna wartość z kolumny Wybrany. Łącznie uzyskujemy taką formułę:

=PRZESUNIĘCIE($C$2;0;0;MAX(tabPracownicy[Wybrany]))

Widzowie 101 - Jak stworzyć listę rozwijaną bez powtórzeń 05

Wystarczy teraz, że skopiujesz tą formułę i naciśniesz skrót Ctrl + F3, żeby wejść do okna menadżera nazw.

Widzowie 101 - Jak stworzyć listę rozwijaną bez powtórzeń 06

Tam możesz dodać Nową nazwę (ListaNiewybranych) i wkleić skopiowaną przed chwilą formułę.

Widzowie 101 - Jak stworzyć listę rozwijaną bez powtórzeń 07

Pozostaje Ci tylko nałożyć sprawdzanie poprawności danych na obszarze, który przyjęliśmy do naszych list (Listy!$B$2:$B$8) i już nasze zadanie skończone.

Widzowie 101 - Jak stworzyć listę rozwijaną bez powtórzeń 08

Pozdrawiam
Adam Kopeć
Miłośnik Excela