Как правильно отсортировать в Excel по пользовательскому списку?

Содержание

Вопрос или проблема
Ответ или решение
Как правильно сортировать в Excel по пользовательскому списку
Ваша задача
Проблема с сортировкой в Excel
Решение проблемы с помощью функции SORTBY
Объяснение формулы
Итог

Вопрос или проблема

У меня есть список, по которому я хотел бы отсортировать. Но я думаю, что список должен быть отсортирован? Мой список таков:

Pu, Tsu, ʢu, Wu, Bi, Tzi, Ri, Yi, Do, So, Mo, Who, Ta, Za, Rra, ɲa

Мои записи представляют собой такие значения:

PuDo, PuZa, BiSoTa, PuBi, SoSo, TziRra

Если бы они были правильно отсортированы, они бы выглядели так:

PuBi, PuDo, PuZa, BiSoTa, TziRra, SoSo

Но то, что я получаю из Excel, это:

BiSoTa, PuDo, PuZa, SoSo, TziRra, PuBi

В моих испытаниях с пользовательскими списками (с вашим списком и встроенными) записи должны точно соответствовать элементам в нем, чтобы быть отсортированными соответствующим образом.

Не уверен, интересуют ли вас альтернативы, но вот быстрое предложение с использованием Excel для Microsoft 365:

Разделите записи по списку сортировки
Используйте индекс этих разделенных записей в списке сортировки с помощью SORTBY
Это быстрое, «грубое» решение, ограниченное тремя «разделениями»
Введите в ячейке, где вы хотите получить результат (C1 на скриншоте)

=LET(
    sort_by, TRIM(
        TEXTSPLIT("Pu, Tsu, ʢu, Wu, Bi, Tzi, Ri, Yi, Do, So, Mo, Who, Ta, Za, Rra, ɲa", ",")
    ),
    entries, A1:A6,
    match_in_sortby, LAMBDA(str,
        IFNA(INDEX(sort_by, XMATCH(TRUE, FIND(sort_by, str) = 1)), "")
    ),
    index_in_sortby, LAMBDA(lst, IFNA(XMATCH(lst, sort_by), ROWS(entries) + 1)),
    list_1, MAP(entries, match_in_sortby),
    list_2, MAP(SUBSTITUTE(entries, list_1, "", 1), match_in_sortby),
    list_3, MAP(SUBSTITUTE(entries, list_1 & list_2, "", 1), match_in_sortby),
    SORTBY(
        entries,
        index_in_sortby(list_1),
        ,
        index_in_sortby(list_2),
        ,
        index_in_sortby(list_3),
        
    )
)

Результат из SORTBY

Ответ или решение

Как правильно сортировать в Excel по пользовательскому списку

Сортировка данных в Excel с помощью пользовательских списков может быть сложной задачей, особенно если ваши данные не соответствуют предполагаемому порядку. Рассмотрим, как вы можете корректно организовать сортировку вашего списка.

Ваша задача

У вас есть заранее определенный список значений:

Pu, Tsu, ʢu, Wu, Bi, Tzi, Ri, Yi, Do, So, Mo, Who, Ta, Za, Rra, ɲa

И у вас есть набор записей:

PuDo, PuZa, BiSoTa, PuBi, SoSo, TziRra

Ожидаемый порядок сортировки выглядит так:

PuBi, PuDo, PuZa, BiSoTa, TziRra, SoSo

Однако Excel сортирует их в следующем порядке:

BiSoTa, PuDo, PuZa, SoSo, TziRra, PuBi

Проблема с сортировкой в Excel

Как вы правильно заметили, встроенная функция сортировки Excel использует алфавитный порядок, который не учитывает ваш пользовательский список. Действительно, для того чтобы ваши данные сортировались так, как вам нужно, вы должны использовать метод, который позволяет Excel правильно интерпретировать ваш список.

Решение проблемы с помощью функции SORTBY

Если у вас есть версия Excel для Microsoft 365, вы можете воспользоваться функцией SORTBY вместе с некоторыми дополнительными функциями для достижения желаемого результата. Вот шаги, как это сделать:

Создайте пользовательский список. Вставьте свой список в отдельный столбец (например, в столбец D).
Используйте формулу в Excel. В одной из ячеек, где вы хотите получить отсортированный список (например, в ячейке C1), вы можете использовать следующую формулу:

=LET(
    sort_by, TRIM(TEXTSPLIT("Pu, Tsu, ʢu, Wu, Bi, Tzi, Ri, Yi, Do, So, Mo, Who, Ta, Za, Rra, ɲa", ",")),
    entries, A1:A6,
    match_in_sortby, LAMBDA(str,
        IFNA(INDEX(sort_by, XMATCH(TRUE, FIND(sort_by, str) = 1)), "")
    ),
    index_in_sortby, LAMBDA(lst, IFNA(XMATCH(lst, sort_by), ROWS(entries) + 1)),
    list_1, MAP(entries, match_in_sortby),
    list_2, MAP(SUBSTITUTE(entries, list_1, "", 1), match_in_sortby),
    list_3, MAP(SUBSTITUTE(entries, list_1 & list_2, "", 1), match_in_sortby),
    SORTBY(
        entries,
        index_in_sortby(list_1),
        ,
        index_in_sortby(list_2),
        ,
        index_in_sortby(list_3)
    )
)

Объяснение формулы

LET — позволяет определить переменные, чтобы сделать формулу более читаемой и эффективной.
TEXTSPLIT — используется для разделения вашего пользовательского списка на массив.
MATCH_IN_SORTBY и INDEX — позволяют находить соответствие между вашими записями и пользовательским списком.
SORTBY — сортирует ваши записи на основе индексов, которые были созданы для каждой части строки.

Итог

Следуя указанным шагам, вы сможете добиться желаемого порядка сортировки в Excel, используя свой пользовательский список. Это решение позволит вам более точно управлять порядком сортировки и избежать проблем, связанных с алфавитным порядком, применяемым по умолчанию в Excel. Убедитесь, что у вас есть последняя версия Excel, чтобы использовать все необходимые функции.

Если у вас возникнут вопросы касательно данных действий, не стесняйтесь задавать их.