Общая формула
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Резюме
Чтобы динамически сортировать и извлекать уникальные значения из списка данных, вы можете использовать формулу массива для определения ранга во вспомогательном столбце, а затем использовать специально построенную формулу ИНДЕКС и ПОИСКПОЗ для извлечения уникальных значений. В показанном примере формула для определения ранга в C5: C13:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
где «данные» - именованный диапазон B5: B13.
Примечание: это формула массива с несколькими ячейками, вводимая с помощью Ctrl + Shift + Enter.
Объяснение
Примечание: основная идея этой формулы заимствована из примера из замечательной книги Майка Гирвина Control + Shift + Enter.
В показанном примере используется несколько формул, которые описаны ниже. На высоком уровне функция MMULT используется для вычисления числового ранга во вспомогательном столбце (столбец C), а затем этот ранг используется формулой INDEX и MATCH в столбце G для извлечения уникальных значений.
Ранжирование значений данных
Функция MMULT выполняет матричное умножение и используется для присвоения числового ранга каждому значению. Первый массив создается с помощью следующего выражения:
--(data>TRANSPOSE(data))
Здесь мы используем функцию TRANSPOSE для создания горизонтального массива данных , и все значения сравниваются друг с другом. По сути, каждое значение сравнивается с любым другим значением, чтобы ответить на вопрос «больше ли это значение, чем любое другое значение». В результате получается двумерный массив, 9 столбцов x 9 строк, заполненный значениями TRUE и FALSE. Двойное отрицание (-) используется для приведения значений ИСТИНА ЛОЖЬ к единицам и нулям. Вы можете визуализировать получившийся массив следующим образом:
Матрица из единиц и нулей, приведенная выше, становится array1 внутри функции MMULT. Array2 создается с помощью этого выражения:
ROW(data)^0
Здесь каждый номер строки в «данных» возведен в степень нуля, чтобы создать одномерный массив, 1 столбец x 9 строк, заполненный числом 1. Затем MMULT возвращает матричное произведение двух массивов, которые становятся значения, отображаемые в столбце ранга.
Мы возвращаем все 9 рейтингов одновременно в массив, поэтому нам нужно сразу поместить результаты в разные ячейки. В противном случае в каждой ячейке будет отображаться только первое значение ранжирования в возвращаемом массиве.
Примечание. Это формула массива с несколькими ячейками, вводимая с помощью Ctrl + Shift + Enter в диапазоне C5: C13.
Обработка пустых ячеек
Пустые ячейки обрабатываются с помощью этой части формулы ранжирования:
=IF(data="",ROWS(data)
Здесь перед запуском MMULT мы проверяем, пуста ли текущая ячейка в «data». Если это так, мы присваиваем значение ранга, равное количеству строк в данных. Это делается для того, чтобы пустые ячейки помещались в конец списка, откуда их можно было легко исключить позже при извлечении уникальных значений (поясняется ниже).
Подсчет уникальных значений
Чтобы подсчитать уникальные значения в данных, формула в E5:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Поскольку приведенная выше формула ранжирования присваивает каждому значению числовой ранг, мы можем использовать функцию ЧАСТОТА с СУММ для подсчета уникальных значений. Эта формула подробно объясняется здесь. Затем мы вычитаем 1 из результата, если в данных есть пустые ячейки:
-(blank>0)
где "blank" - это именованный диапазон E8, содержащий следующую формулу:
=COUNTBLANK(data)
По сути, мы уменьшаем количество уникальных данных на единицу, если в данных есть пустые ячейки, поскольку мы не включаем их в результаты. Уникальный счетчик в ячейке E5 называется «уникальный» (для уникального счетчика) и используется формулой ИНДЕКС и ПОИСКПОЗ для фильтрации пустых ячеек (описанных ниже).
Извлечение уникальных значений
Для извлечения уникальных значений G5 содержит скопированную формулу:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Перед тем как запустить формулы ИНДЕКС и ПОИСКПОЗ, мы сначала проверяем, превышает ли текущее количество строк в области извлечения уникальное количество именованного диапазона "уникальный" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Если это так, мы закончили извлечение уникальных значений и возвращаем пустую строку (""). Если нет, запускаем формулу извлечения:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Обратите внимание, что здесь есть две функции MATCH, одна внутри другой. Внутреннее ПОИСКПОЗ использует расширяющийся диапазон для массива и именованный диапазон "данных" для значения поиска:
MATCH(data,$G$4:G4,0)
Обратите внимание, что расширяющийся диапазон начинается с «строки выше», строки 4 в примере. Результатом внутреннего MATCH является массив, который для каждого значения в данных содержит либо числовую позицию (значение уже было извлечено), либо ошибку # N / A (значение еще не извлечено). Затем мы используем IF и ISNA для фильтрации этих результатов и возвращаем значение ранга для всех значений в «данных», которые еще не извлечены:
IF(ISNA(results),rank))
Результатом этой операции является массив, который передается в функцию MIN, чтобы получить «минимальное значение ранга» для значений данных, которые еще не извлечены. Функция MIN возвращает это значение во внешнее ПОИСКПОЗ в качестве значения поиска и именованный диапазон "rank" в качестве массива:
MATCH(min_not_extracted,rank)),rank,0)
Наконец, ПОИСКПОЗ возвращает позицию самого низкого значения ранга в ИНДЕКС как номер строки, а ИНДЕКС возвращает значение данных в текущей строке диапазона извлечения.
