Завантажте зразок робочої книги
Цей підручник покаже, як динамічно сортувати дані за власним порядком сортування в Excel та Google Таблицях.
Функція SORTBY в Excel 365
Функція SORTBY створює динамічний масив відсортованих даних.
У цьому прикладі ми використовуємо функцію SORTBY, щоб взяти таблицю даних і створити динамічний масив, який сортує її за Команда слідуючи а Спеціальний список сортування:
| 1 | = SORTBY (B3: D8, MATCH (B3: B8, F3: F5,0)) |
Примітка: Функція SORTBY записується лише в комірці H3. Решта таблиці заповниться автоматично.
Відсортована таблиця є динамічною, що означає, що якщо будь -яка з Команда або Ім'я гравця значення змінюються або записуються або додаються / видаляються, тоді таблиця виводу буде автоматично змінювати розмір та повторно сортувати.
Примітка: Функція SORTBY недоступна в Excel 2022 або старіших версіях. Нижче прочитайте параметри сортування, доступні для попередніх версій Excel.
Щоб пояснити, як працює ця формула, давайте розберемо її на етапи:
Наша остаточна формула така:
| 1 | = SORTBY (B3: D8, MATCH (B3: B8, F3: F5,0)) |
Спочатку ми визначаємо таблицю даних, яку потрібно відсортувати. Це буде B3: D8.
Далі нам потрібно визначити порядок сортування. Ми будемо використовувати функцію MATCH, щоб знайти порядок, у якому кожен Команда назва з'являється в Спеціальний список сортування. Ця частина формули виглядає так:
| 1 | = МАТЧ (B3: B8, F3: F5,0) |
Додавши значення з діапазонів комірок, ми отримаємо:
| 1 | = MATCH ({"Blue"; "Red"; "Yellow"; "Red"; "Blue"; "Yellow"}, {"Yellow"; "Blue"; "Red"}, 0) |
Функція MATCH замінює все Команда імена з порядком їх появи у Спеціальний список сортування, використовуючи точну відповідність тексту:
| 1 | ={2; 3; 1; 3; 2; 1} |
Цей масив значень потім використовується як порядок сортування головною функцією SORTBY:
| 1 | = СОРТБІ (B3: D8, {2; 3; 1; 3; 2; 1}) |
Після цього виробляється відсортована таблиця
Спеціальний список сортування - Pre -Excel 365
Оскільки функція SORTBY недоступна для користувачів Excel до версії Excel 365, тепер ми покажемо, як виконати те саме завдання за допомогою більш ручного процесу.
Вбудований інструмент сортування Excel можна використовувати для сортування за допомогою Спеціальний список сортування вибравши "Спеціальний список…" з параметрів порядку сортування в інструменті сортування:
Новий Спеціальний список слід додати як список, розділений комами. У цьому прикладі ми додаємо:Жовтий, синій, червоний '
The Спеціальний список потім відображається у виборі порядку сортування
Потім таблицю даних сортують за Спеціальний список цінності
Зауважте, що цей користувацький порядок сортування зберігається у книзі та доступний для повторного використання. Якщо ви не хочете додавати Спеціальний список до Excel, тоді замість цього можна спробувати метод, що використовує допоміжний стовпець.
Спеціальний список сортування зі стовпцем помічника - Pre -Excel 365
У цьому наступному прикладі ми використовуємо функцію MATCH у стовпці -помічнику для визначення a Спеціальний порядок сортування посилаючись на a Спеціальний список сортування:
| 1 | = МАТЧ (B3, $ G $ 3: $ G $ 5,0) |
Зверніть увагу на використання символу $ під час посилання на Спеціальний список сортування. Будь ласка, прочитайте нашу статтю про блокування посилань на клітинки, щоб дізнатися більше на цю тему.
Додано один допоміжний стовпець, процес ручного сортування можна запустити за допомогою Майстер сортування:
Потім буде створено таблицю, відсортовану відповідно до Спеціальний порядок сортування:
Якщо вносяться якісь зміни до Команда або Спеціальний список сортування значення, то майстер сортування потрібно буде повторно запустити, щоб оновити порядок сортування таблиці даних.
Спеціальний список сортування з формулою в Таблицях Google
Функція SORTBY недоступна в Google Таблицях, але замість неї можна використовувати її функцію SORT, яка є більш потужною, ніж функція SORT у Excel 365. Вона дозволяє нам використовувати власні списки сортування для динамічного сортування діапазонів даних.
У цьому прикладі ми використовуємо функцію СОРТУВАННЯ таблиць Google, щоб взяти таблицю даних і створити динамічний масив, який сортує Команда значення а Спеціальний список сортування:
| 1 | = SORT (B3: D8, MATCH (B3: B8, F3: F5,0), TRUE) |
Функція СОРТУВАННЯ Таблиць Google сортує дані за вказаними номерами стовпців або спеціальними порядками сортування у вхідних даних і дозволяє нам встановлювати, чи порядок сортування зростає чи ні
