Formuła ogólna
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
Podsumowanie
Aby losowo posortować istniejące wartości za pomocą formuły, możesz użyć formuły INDEKS i PODAJ.POZYCJĘ razem z kolumnami pomocniczymi, jak pokazano na zrzucie ekranu. W pokazanym przykładzie formuła w E5 to:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
gdzie „nazwy” to nazwany zakres B5: B11, „rand” to nazwany zakres C5: C11, a „sortowanie” to nazwany zakres D5: D11.
Wyjaśnienie
Ta formuła zależy od dwóch kolumn pomocniczych. Pierwsza kolumna pomocnicza zawiera losowe wartości utworzone za pomocą funkcji RAND (). Wzór w C5 skopiowany w dół to:
=RAND()
Funkcja RAND generuje losową wartość w każdym wierszu.
Uwaga: LOS jest funkcją nietrwałą i będzie generować nowe wartości przy każdej zmianie arkusza.
Druga kolumna pomocnicza zawiera liczby używane do sortowania danych, wygenerowane za pomocą formuły. Wzór w D5 to:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Zobacz tę stronę, aby uzyskać wyjaśnienie tego wzoru.
Wzór w E5 to:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
W tym przypadku funkcja INDEKS służy do pobierania wartości z nazwanego zakresu „nazwy” przy użyciu wartości sortowania w nazwanym zakresie „sort”. Rzeczywista praca polegająca na ustaleniu, jaką wartość należy pobrać, jest wykonywana przez funkcję MATCH w tym fragmencie:
MATCH(ROWS($D$5:$D5),sort,0)
Wewnątrz funkcji MATCH funkcja ROWS otrzymuje rozszerzający się zakres jako wartość wyszukiwania, który zaczyna się od jednej komórki i rozwija się, gdy formuła jest kopiowana w dół kolumny. Zwiększa to wartość wyszukiwania, zaczynając od 1 i kontynuując do 7. PODAJ.POZYCJĘ, a następnie zwraca pozycję wartości wyszukiwania na liście.
Pozycja jest przekazywana do INDEKSU jako numer wiersza, a INDEKS pobiera nazwę z tej pozycji.
