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.).
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])
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)
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))
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]))
Wystarczy teraz, że skopiujesz tą formułę i naciśniesz skrót Ctrl + F3, żeby wejść do okna menadżera nazw.
Tam możesz dodać Nową nazwę (ListaNiewybranych) i wkleić skopiowaną przed chwilą formułę.
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.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
