Formuła ogólna
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Podsumowanie
Aby dynamicznie sortować i wyodrębniać unikatowe wartości z listy danych, można użyć formuły tablicowej do ustalenia pozycji w kolumnie pomocniczej, a następnie użyć specjalnie skonstruowanej formuły INDEKS i PODAJ.POZYCJĘ w celu wyodrębnienia unikatowych wartości. W pokazanym przykładzie wzór do ustalenia pozycji w C5: C13 to:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
gdzie „dane” to nazwany zakres B5: B13.
Uwaga: jest to formuła tablicowa z wieloma komórkami, wprowadzana za pomocą Ctrl + Shift + Enter.
Wyjaśnienie
Uwaga: podstawowa idea tej formuły pochodzi z przykładu w doskonałej książce Mike'a Girvina Control + Shift + Enter.
W przedstawionym przykładzie zastosowano kilka formuł, które opisano poniżej. Na wysokim poziomie funkcja MMULT służy do obliczania pozycji liczbowej w kolumnie pomocniczej (kolumna C), a pozycja ta jest następnie używana przez formułę INDEKS i PODAJ.POZYCJĘ w kolumnie G w celu wyodrębnienia unikatowych wartości.
Ranking wartości danych
Funkcja MMULT wykonuje mnożenie macierzy i służy do przypisywania rangi numerycznej do każdej wartości. Pierwsza tablica jest tworzona za pomocą następującego wyrażenia:
--(data>TRANSPOSE(data))
Tutaj używamy funkcji TRANSPOSE do tworzenia poziomej tablicy danych , a wszystkie wartości są porównywane ze sobą. Zasadniczo każda wartość jest porównywana z każdą inną wartością, aby odpowiedzieć na pytanie „czy ta wartość jest większa niż każda inna wartość”. W rezultacie powstaje dwuwymiarowa tablica, 9 kolumn x 9 wierszy, wypełniona wartościami PRAWDA i FAŁSZ. Podwójny ujemny (-) jest używany do przekształcenia wartości TRUE FALSE na jedynki i zera. Możesz wizualizować wynikową tablicę w następujący sposób:
Powyższa macierz jedynek i zer staje się tablicą1 wewnątrz funkcji MMULT. Array2 jest tworzony za pomocą tego wyrażenia:
ROW(data)^0
Tutaj każdy numer wiersza w „danych” jest podnoszony do potęgi zera, aby utworzyć jednowymiarową tablicę, 1 kolumna x 9 wierszy, wypełnioną liczbą 1. Następnie MMULT zwraca iloczyn macierzy dwóch tablic, który staje się wartości widoczne w kolumnie rang.
Otrzymujemy wszystkie 9 rankingów w tym samym czasie w tablicy, więc musimy umieścić wyniki w różnych komórkach naraz. W przeciwnym razie w każdej komórce będzie wyświetlana tylko pierwsza wartość rankingu w zwracanej tablicy.
Uwaga: jest to formuła tablicowa z wieloma komórkami, wprowadzana za pomocą klawiszy Ctrl + Shift + Enter w zakresie C5: C13.
Obsługa pustych komórek
Puste komórki są obsługiwane za pomocą tej części formuły rankingu:
=IF(data="",ROWS(data)
Tutaj, zanim uruchomimy MMULT, sprawdzamy, czy bieżąca komórka w "danych" jest pusta. Jeśli tak, przypisujemy wartość rangi równą liczbie wierszy w danych. Ma to na celu wymuszenie pustych komórek na dole listy, skąd można je później łatwo wykluczyć, gdy są wyodrębniane unikalne wartości (wyjaśnione poniżej).
Liczenie unikatowych wartości
Aby policzyć unikalne wartości w danych, formuła w E5 wygląda następująco:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Ponieważ powyższa formuła rankingu przypisuje rangę liczbową do każdej wartości, możemy użyć funkcji CZĘSTOTLIWOŚĆ z SUMĄ do zliczania unikatowych wartości. Ta formuła została szczegółowo wyjaśniona tutaj. Następnie odejmujemy 1 od wyniku, jeśli w danych są jakieś puste komórki:
-(blank>0)
gdzie „puste” to nazwany zakres E8 zawierający następującą formułę:
=COUNTBLANK(data)
Zasadniczo zmniejszamy unikalną liczbę o jeden, jeśli w danych znajdują się puste komórki, ponieważ nie uwzględniamy ich w wynikach. Niepowtarzalna liczba w komórce E5 nosi nazwę „unikalna” (w przypadku liczby unikalnej) i jest używana przez formułę INDEKS i PODAJ.POZYCJĘ do odfiltrowania pustych komórek (opisanych poniżej).
Wyodrębnianie unikalnych wartości
Aby wyodrębnić unikalne wartości, G5 zawiera następującą formułę, skopiowaną w dół:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Zanim uruchomimy formułę INDEKS i PODAJ.POZYCJĘ, najpierw sprawdzamy, czy bieżąca liczba wierszy w obszarze wyodrębniania jest większa niż liczba unikatowa nazwanego zakresu „unikalny” (E5):
=IF(ROWS($G$5:G5)>unique,"",
Jeśli tak, zakończyliśmy wyodrębnianie unikatowych wartości i zwracamy pusty ciąg („”). Jeśli nie, uruchamiamy formułę ekstrakcji:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Zauważ, że są tutaj dwie funkcje MATCH, jedna wewnątrz drugiej. Wewnętrzne PODAJ.POZYCJĘ używa rozszerzającego się zakresu dla tablicy i nazwanego zakresu „dane” jako wartości wyszukiwania:
MATCH(data,$G$4:G4,0)
Zwróć uwagę, że rozszerzany zakres zaczyna się w „wierszu powyżej”, w przykładzie w wierszu 4. Wynikiem z wewnętrznego MATCH jest tablica, która dla każdej wartości w danych zawiera albo pozycję numeryczną (wartość została już wyodrębniona), albo błąd # N / A (wartość nie została jeszcze wyodrębniona). Następnie używamy IF i ISNA do filtrowania tych wyników i zwracamy wartość rankingu dla wszystkich wartości w „danych”, które nie zostały jeszcze wyodrębnione:
IF(ISNA(results),rank))
W wyniku tej operacji powstaje tablica, która jest przekazywana do funkcji MIN w celu uzyskania „minimalnej wartości rangi” dla wartości danych, które nie zostały jeszcze wyodrębnione. Funkcja MIN zwraca tę wartość do zewnętrznej funkcji MATCH jako wartość wyszukiwania, a nazwany zakres „rank” jako tablicę:
MATCH(min_not_extracted,rank)),rank,0)
Na koniec funkcja PODAJ.POZYCJĘ zwraca pozycję najniższej wartości rankingu do INDEKSU jako numer wiersza, a funkcja INDEKS zwraca wartość danych w bieżącym wierszu zakresu wyodrębniania.
