Загальна формула
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Резюме
Для динамічного сортування та вилучення унікальних значень зі списку даних можна використовувати формулу масиву, щоб встановити ранг у допоміжному стовпці, а потім використовувати спеціально побудовану формулу INDEX і MATCH для вилучення унікальних значень. У наведеному прикладі формула для встановлення рангу в C5: C13 має вигляд:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
де "дані" - названий діапазон B5: B13.
Примітка: це формула масиву з декількох комірок, введена за допомогою керування + shift + enter.
Пояснення
Примітка: основна ідея цієї формули адаптована на прикладі у чудовій книзі Майка Гірвіна Control + Shift + Enter.
У наведеному прикладі використовується кілька формул, які описані нижче. На високому рівні функція MMULT використовується для обчислення числового рангу в допоміжному стовпці (стовпець C), а потім цей ранг використовується формулою INDEX і MATCH у стовпці G для вилучення унікальних значень.
Значення даних ранжування
Функція MMULT виконує множення матриць і використовується для присвоєння числового рангу кожному значенню. Перший масив створюється з таким виразом:
--(data>TRANSPOSE(data))
Тут ми використовуємо функцію TRANSPOSE для створення горизонтального масиву даних , і всі значення порівнюються між собою. По суті, кожне значення порівнюється з кожним іншим значенням, щоб відповісти на питання "чи це значення більше за будь-яке інше значення". У результаті виходить двовимірний масив, 9 стовпців х 9 рядків, заповнений значеннями TRUE і FALSE. Подвійний від’ємник (-) використовується для примушення значень TRUE FALSE до 1s та нулів. Ви можете візуалізувати отриманий масив таким чином:
Матриця 1s і нулів вище стає array1 всередині функції MMULT. Array2 створюється з таким виразом:
ROW(data)^0
Тут кожен номер рядка в "data" піднімається до рівня нуля, щоб створити одновимірний масив, 1 стовпець x 9 рядків, заповнений цифрою 1. Потім MMULT повертає матричний добуток двох масивів, які стають значення, видно у стовпці ранжу.
Ми отримуємо всі 9 рейтингів одночасно в масиві, тому нам потрібно помістити результати в різні клітинки одночасно. В іншому випадку кожна комірка відображатиме лише перше значення ранжування в масиві, який повертається.
Примітка: це формула багатоклітинного масиву, введена за допомогою керування + shift + enter, в діапазоні C5: C13.
Обробка порожніх клітин
Порожні комірки обробляються за допомогою цієї частини формули ранжування:
=IF(data="",ROWS(data)
Тут перед тим, як запустити MMULT, ми перевіряємо, чи порожня поточна комірка в "data". Якщо так, то ми присвоюємо значення рангу, яке дорівнює кількості рядків у даних. Це робиться, щоб примусити порожні клітинки до кінця списку, де їх можна легко виключити пізніше, оскільки витягуються унікальні значення (пояснюється нижче).
Підрахунок унікальних значень
Для підрахунку унікальних значень у даних формула в E5 має вигляд:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Оскільки наведена вище формула ранжування присвоює числовий ранг кожному значенню, ми можемо використовувати функцію FREQUENCY з SUM для підрахунку унікальних значень. Ця формула детально пояснена тут. Потім ми віднімаємо від результату 1, якщо в даних є порожні клітинки:
-(blank>0)
де "порожній" - іменований діапазон E8 і містить цю формулу:
=COUNTBLANK(data)
По суті, ми зменшуємо унікальний підрахунок на одиницю, якщо в даних є порожні клітинки, оскільки ми не включаємо їх у результати. Унікальний підрахунок у клітинці E5 називається "унікальним" (для унікального підрахунку) і використовується формулою INDEX та MATCH для фільтрації порожніх комірок (описано нижче).
Витяг унікальних значень
Для вилучення унікальних значень G5 містить таку формулу, скопійовану вниз:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Перш ніж запускати формулу INDEX і MATCH, ми спочатку перевіряємо, чи поточний підрахунок рядків в області видобутку не перевищує унікальний підрахунок, названий діапазон "унікальний" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Якщо так, ми закінчуємо витягувати унікальні значення і повертаємо порожній рядок (""). Якщо ні, ми запускаємо формулу вилучення:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Зверніть увагу, що тут є дві функції MATCH, одна всередині іншої. Внутрішній MATCH використовує діапазон, що розширюється для масиву, і названий діапазон "дані" для значення пошуку:
MATCH(data,$G$4:G4,0)
Зверніть увагу, що розширення діапазону починається з "рядка вище", рядка 4 у прикладі. Результатом внутрішнього MATCH є масив, який для кожного значення в даних містить або числове положення (значення вже витягнуто), або помилку # N / A (значення ще не витягнуто). Потім ми використовуємо IF та ISNA для фільтрації цих результатів та повертаємо значення ранжу для всіх значень у "даних", які ще не витягнуті:
IF(ISNA(results),rank))
Ця операція призводить до масиву, який подається у функцію MIN, щоб отримати "мінімальне значення рангу" для значень даних, які ще не витягнуті. Функція MIN повертає це значення до зовнішнього MATCH як значення підстановки та названий діапазон "rank" як масив:
MATCH(min_not_extracted,rank)),rank,0)
Нарешті, MATCH повертає позицію найнижчого значення рангу до INDEX як номер рядка, а INDEX повертає значення даних у поточному рядку діапазону вилучення.
