Сортування за формулою за допомогою СОРТУВАННЯ та СОРТБІ - Поради Excel

Цього тижня на конференції Ignite в Орландо, штат Флорида, Microsoft дебютувала серію нових, простіших формул масивів в Excel. Я буду викладати ці нові формули щодня цього тижня, але якщо ви хочете прочитати заздалегідь:

Понеділок охопив нову формулу = A2: A20, помилку SPILL та нову функцію SINGLE, необхідну замість неявного перетину
Сьогодні буде охоплювати SORT та SORTBY
Середа буде охоплювати ФІЛЬТР
Четвер буде охоплювати УНІКАЛЬНИЙ
П’ятниця стосуватиметься функцій SEQUENCE та RANDARRAY

Сортування за допомогою формули в Excel раніше вимагало шаленої комбінації формул. Погляньте на ці дані, які будуть використані у цій статті.

Дані в A3: C11.

Для того, щоб відсортувати це за формулою до цього тижня, вам просто потрібно вибити RANK, COUNTIF, MATCH, INDEX та INDEX. Як тільки ви закінчите цей набір формул, ви будете готові до денного сну.

Старий спосіб сортування за формулою

Джо Макдейд та його команда принесли нам SORT і SORTBY.

Почнемо з СОРТУ. Ось синтаксис=SORT(Array, (Sort Index), (Sort Order), (By Column))

Функція СОРТУВАННЯ

Скажімо, ви хочете відсортувати A3: C16 за полем Оцінка. Оцінка - це третій стовпець масиву, тому ваш Індекс сортування буде 3.

Варіанти для Порядку сортування: 1 для зростання або -1 для спадання. Я не скаржусь, але ніколи не буде підтримки Сортувати за кольором, Сортувати за формулою або Сортувати за користувацьким списком за допомогою цієї функції.

Вкажіть 3 як стовпець сортування та -1 як порядок сортування за спаданням.

Четвертий аргумент буде використовуватися рідко. У діалоговому вікні Сортування можна сортувати за стовпцем замість рядків. 99,9% людей сортують за рядами. Якщо вам потрібно відсортувати за стовпцем, в кінцевому аргументі вкажіть значення True. Цей аргумент необов’язковий і за замовчуванням має значення False.

Якщо вам потрібно сортувати за стовпцями, використовуйте True в 4-му аргументі

Ось результати формули. Завдяки новому двигуну накипу формула виливається в сусідні комірки. Одна формула в O2 дає цей розчин.

Немає необхідності натискати Ctrl + Shift + Enter

Вихідні дані сортуються

Що робити, якщо вам потрібна дворівнева сортування? Сортувати за стовпцем 2 за зростанням та за стовпцем 3 за спаданням? Надайте константу масиву для 2-го та 3-го аргументів:=SORT(A2:C17,(2;3),(1;-1))

Дворівневе сортування

Функція SORTBY дозволяє сортувати за чимось, що не є результатом

Синтаксис функції SORTBY: =SORTBY(array, by_array1, sort_order1,)

СОРТУЙ щось інше

Повертаючись до вихідних даних. Скажімо, ви хочете сортувати за командою, а потім оцінка, але показуйте лише імена. Ви можете використовувати SORTBY, як показано тут.

Сортувати стовпець A за стовпцем B і стовпцем C

Випадкове тестування наркотиків та випадкове без повторів

Складні сценарії, такі як Випадкове тестування наркотиків та Випадкове без повторень, стають надзвичайно простими, коли ви поєднуєте СОРТУВАННЯ з РЕНДАРРЕЙ.

На малюнку нижче ви хочете відсортувати 13 імен випадковим чином без повторень. Використовуйте =SORTBY(A4:A16,RANDARRAY(13)). Прочитайте більше про RANDARRAY у п’ятницю.

Сортування випадковим чином без повторень

Ctrl + Shift + Enter повністю мертвий? Ні. Це все ще використовується. Скажімо, ви хотіли лише найкращі 3 результати від функції SORT. Ви можете вибрати три комірки, набрати функцію СОРТУВАННЯ і слідувати за нею за допомогою Ctrl + Shift + Enter. Це запобіжить виходу результатів за межі вихідної формули.