Сортировка отфильтрованных данных по индивидуальному заказу с помощью формулы (Excel)

Мой первый лист содержит информацию о большом количестве питомцев, участвовавших в большой выставке. У каждого уникального питомца есть своя строка, а в столбцах отображаются такие значения, как дрессировщик, возраст, цвет и пол. У меня также есть столбцы для конкретных соревнований во время шоу, где вы можете узнать, участвует ли оно в этом конкретном соревновании и на каком уровне (уровень тех, кто участвует в этом соревновании, указан в ячейке, например «Новичок», а те, кто участвует в этом соревновании, имеют свой уровень, указанный в ячейке, например «Новичок», а не введенные имеют пустые ячейки). Я получаю эти данные при их вводе (по форме, поэтому просто копирую и вставляю всю строку). Никаких проблем.

Лист 2: Здесь мне нужны стартовые заявки на различные соревнования, чтобы я мог легко их распечатать. У меня есть формула для извлечения данных из строк, содержащих текст, в определенном столбце соревнования. Я отлично отображаю имя, хендлера и уровень соревнований. Но я также хочу, чтобы они автоматически сортировались по уровню. И вот моя проблема: уровни расположены не в алфавитном порядке. Поскольку в общей сложности шоу включает около 40 различных соревнований, я, конечно, хочу, чтобы это было сделано только из Листа 1, а не вручную сортировать 40 списков в Листе 2.

Например: Питомцы, соревнующиеся на уровне «Новичок», должны быть вверху списка, в то время как питомцы, соревнующиеся на первом уровне, должны быть ниже них, а затем на втором уровне и так далее, поэтому обратный алфавитный порядок также невозможен. И в списке должно быть указано, на каком уровне они находятся, чтобы я не мог преобразовать его в цифры.

Это формула, которая дает мне нужную информацию в ячейках листа 2, но, конечно, не в правильном порядке: =ВЫБОР((ФИЛЬТР((Лист1!$A$4:$AP$100);(Лист1!N$4:N$100<> "");"Нет");1;2;9;14)

В столбце N (№ 14) указывается, какой уровень используется для данного конкретного соревнования.

Я использую Microsoft Excel для Mac версии 16.88.

[Вид моего списка участников на Листе 1. Я знаю, что он уменьшен, но, возможно, это может дать представление о том, как он выглядит][1]

[Список, который я хочу извлечь из Листа 1 и автоматически отсортировать на Листе 2 (здесь нет настоящих имен, просто сделал небольшой образец) [2]

Я перепробовал очень много вещей, которые нашел в Google, и пока ничего не помогло (и, честно говоря, я не знаю, правильно ли я написал формулы). Буду очень признателен за помощь, раньше я почти не пользовался Excel.

Редактировать: В итоге я немного схитрил. Используя предложенный вспомогательный столбец IFS в качестве пятого отображаемого столбца, отсортировав его и изменив его на белый цвет текста. Этого было достаточно для того, что мне нужно (печатные списки), но при этом можно было копировать и вставлять информацию из форм, заполненных конкурентами :)


73
3

Ответы:

Решено

Обновлено: чтобы это работало, используйте Excel 365, вдохновленный П.б.

Добавьте вспомогательный столбец, скажем, в столбец BI листа 1: =IFS($N$5:$N$100 = "Novice";1;$N$5:$N$100 = "First Level";2;$N$5:$N$100 = "Entry";3;$N$5:$N$100<>"Entry";"")

В листе 2 используйте следующую формулу: =LET(_Data;FILTER((Sheet1!$A$4:$BI$100);(Sheet1!N$4:N$100<> "");"None"); _SortData;SORTBY(_Data;CHOOSECOL(_Data;61)); _Result;CHOOSECOL(_SortData;1;2;9;14); _Result)

Лист1

Лист2


Изменить. Оригинальный ответ ниже, этот способ не работает.


Если у вас есть место для запасной колонки, вы можете сделать следующее:

Создайте запасной столбец и используйте формулу типа IFS(N:N = "Novice";1;N:N = "First Level";2,--insert levels--;--and numbers--;N:N<>"name of last level";"") (заполните после него столько уровней, сколько у вас есть).

Тогда вы можете использовать SORTBY вот так:=SORTBY(array you want to sort;helper column)

Итак, ваша окончательная формула будет выглядеть так: =SORTBY(CHOOSECOL((FILTER((Sheet1!$A$4:$AP$100);(Sheet1!N$4:N$100<> "");"None"));1;2;9;14);Sheet1!$Y$4:$Y$100)

Где Sheet1!$Y$4:$Y$100 — это место, где вы создали свой вспомогательный столбец.


Просто оберните формулу в сортировку, используя четвертый столбец в качестве индекса сортировки: =SORT(FILTER(CHOOSECOLS(Sheet1!$A$4:$AP$100;1;2;9;14);Sheet1!N$4:N$100<>"");4)

(Я сохранил разделитель ; в ответе, как в версии вашего вопроса)


Вот один из способов (протестировано в Microsoft 365 на Mac):

Использование LC:1 в качестве примера

  • Перечислите классы в порядке группировки: klasserna, TRIM(TEXTSPLIT("LD,LC,LB,LA,MsvB,MsvA,SvårB,SvårA", ","))

  • Разделить уровень на :,

    • Найдите индекс LC, присвойте 100 (больше, чем количество классов), если не найдено, преобразуйте в число. TEXT(IFERROR(XMATCH(INDEX(split, , 1), klasserna), 100), "000")
    • Преобразуйте номер группы1 (-- используется для получения номера текста в виде номера) в текст и присоединяйтесь; это будет использоваться для сортировки & "-" & TEXT(--INDEX(split, , 2), "000")
  • Фильтр, он очень похож на ваш (<> ""), использование TRIM здесь и в других местах помогает получить ожидаемые результаты: LEN(TRIM(CHOOSECOLS(data, col_num_competion))) > 0

  • Было бы полезно выбрать имя завершения для отчета с раскрывающимся списком (B1 в примере) с проверкой данных (например, =Sheet1!$N$2:$O$2).

    Пожалуйста, извините за орфографические ошибки;), а также замените разделитель параметров , на ; при необходимости. Функции переводчика для конвертации сейчас не работают.

=LET(
    comment_1, "Used for custom sort order",
    klasserna, TRIM(TEXTSPLIT("LD,LC,LB,LA,MsvB,MsvA,SvårB,SvårA", ",")),
    num_rows, 100,
    data, Sheet1!$A$4:INDEX(Sheet1!$AP:$AP, num_rows),
    competition_name, $B$1,
    header_main, TRIM(Sheet1!$A$3:$M$3),
    header_competition, TRIM(Sheet1!$A$2:$AP$2),
    col_num_competion, XMATCH(competition_name, header_competition),
    col_nums_for_detail, XMATCH({"Hästnr.", "Hästens namn", "Ägera"}, header_main),
    competion_col, CHOOSECOLS(data, col_num_competion),
    filter_by, LEN(TRIM(CHOOSECOLS(data, col_num_competion))) > 0,
    sort_by_col, FILTER(
        MAP(
            competion_col,
            LAMBDA(a,
                LET(
                    split, TEXTSPLIT(a, ":"),
                    TEXT(IFERROR(XMATCH(INDEX(split, , 1), klasserna), 100), "000")  
                    & "-" & TEXT(--INDEX(split, , 2), "000")
                )
            )
        ),
        filter_by
    ),
    report_header, HSTACK(CHOOSECOLS(header_main, col_nums_for_detail), CHOOSECOLS(header_competition, col_num_competion)),
    report_data, SORTBY(FILTER(CHOOSECOLS(data, col_nums_for_detail, col_num_competion), filter_by, "None"), sort_by_col),
    VSTACK(report_header, report_data)
)


Начало новой строки текста внутри ячейки в Excel - Служба поддержки Microsoft