Загрузите образец книги
В этом руководстве будет показано, как динамически сортировать данные в соответствии с настраиваемым порядком сортировки в Excel и Google Таблицах.
Функция СОРТБИ в Excel 365
Функция SORTBY создает динамический массив отсортированных данных.
В этом примере мы используем функцию SORTBY, чтобы взять таблицу данных и создать динамический массив, который сортирует ее по Команда после Пользовательский список сортировки:
| 1 | = СОРТИРОВАТЬ (B3: D8; ПОИСКПОЗ (B3: B8; F3: F5; 0)) |
Примечание: Функция СОРТБИ написана только в ячейке H3. Остальная часть таблицы заполнится автоматически.
Сортированная таблица является динамической, что означает, что если любой из Команда или Имя игрока значения изменяются или записываются, или добавляются / удаляются, тогда выходная таблица будет автоматически изменять размер и сортировку.
Примечание: Функция СОРТБИ недоступна в Excel 2022 или более ранней версии. Ниже приведены параметры сортировки, доступные для более ранних версий Excel.
Чтобы объяснить, как работает эта формула, давайте разберем ее на шаги:
Наша окончательная формула:
| 1 | = СОРТИРОВАТЬ (B3: D8; ПОИСКПОЗ (B3: B8; F3: F5; 0)) |
Сначала мы определяем таблицу данных, которую нужно отсортировать. Это будет B3: D8.
Далее нам нужно определить порядок сортировки. Мы будем использовать функцию MATCH, чтобы найти порядок, в котором каждый Команда имя появляется в Пользовательский список сортировки. Эта часть формулы:
| 1 | = ПОИСКПОЗ (B3: B8; F3: F5,0) |
Складывая значения из диапазонов ячеек, получаем:
| 1 | = MATCH ({"Синий"; "Красный"; "Желтый"; "Красный"; "Синий"; "Желтый"}, {"Желтый"; "Синий"; "Красный"}, 0) |
Функция ПОИСКПОЗ заменяет все Команда имена в том порядке, в котором они появляются в Пользовательский список сортировки, используя точное текстовое совпадение:
| 1 | ={2; 3; 1; 3; 2; 1} |
Этот массив значений затем используется в качестве порядка сортировки основной функцией SORTBY:
| 1 | = СОРТБИ (B3: D8; {2; 3; 1; 3; 2; 1}) |
Затем создается отсортированная таблица.
Настраиваемый список сортировки - до Excel 365
Поскольку функция SORTBY недоступна для пользователей Excel до версии Excel 365, теперь мы покажем, как выполнить ту же задачу, используя более ручной процесс.
Встроенный инструмент сортировки Excel можно использовать для сортировки по Пользовательский список сортировки выбрав «Пользовательский список…» из параметров порядка сортировки в инструменте сортировки:
Новый Пользовательский список должны быть добавлены в виде списка, разделенного запятыми. В этом примере мы добавляем: ‘Желтый, синий, красный »
В Пользовательский список затем отображается в выбранном порядке сортировки
Затем таблица данных сортируется по Пользовательский список ценности
Обратите внимание, что этот настраиваемый порядок сортировки сохраняется в книге и доступен для повторного использования. Если вы не хотите добавлять Пользовательский список в Excel, то вместо этого можно попробовать метод, использующий вспомогательный столбец.
Настраиваемый список сортировки со вспомогательным столбцом - Pre-Excel 365
В следующем примере мы используем функцию MATCH во вспомогательном столбце, чтобы определить Пользовательский порядок сортировки ссылаясь на Пользовательский список сортировки:
| 1 | = ПОИСКПОЗ (B3; $ G $ 3: $ G $ 5,0) |
Обратите внимание на использование символа $ при ссылке на Пользовательский список сортировки. Пожалуйста, прочтите нашу статью о блокировке ссылок на ячейки, чтобы узнать больше по этому поводу.
После добавления вспомогательного столбца процесс сортировки вручную можно запустить с помощью Мастер сортировки:
Затем создается таблица, отсортированная по Пользовательский порядок сортировки:
Если в Команда или Пользовательский список сортировки значений, то потребуется повторно запустить мастер сортировки, чтобы обновить порядок сортировки таблицы данных.
Пользовательский список сортировки с формулой в Google Таблицах
Функция SORTBY недоступна в Google Sheets, но вместо нее может использоваться ее функция SORT, которая является более мощной, чем функция SORT в Excel 365. Она позволяет нам использовать настраиваемые списки сортировки для динамической сортировки диапазонов данных.
В этом примере мы используем функцию SORT Google Sheets, чтобы взять таблицу данных и создать динамический массив, который сортирует Команда ценностей Пользовательский список сортировки:
| 1 | = СОРТИРОВАТЬ (B3: D8; ПОИСКПОЗ (B3: B8; F3: F5; 0); ИСТИНА) |
Функция СОРТИРОВКИ Google Таблиц сортирует данные по указанным номерам столбцов или настраиваемым порядкам сортировки во входных данных и позволяет нам установить, будет ли порядок сортировки возрастающим или нет.
