Мой первый лист содержит информацию о большом количестве питомцев, участвовавших в большой выставке. У каждого уникального питомца есть своя строка, а в столбцах отображаются такие значения, как дрессировщик, возраст, цвет и пол. У меня также есть столбцы для конкретных соревнований во время шоу, где вы можете узнать, участвует ли оно в этом конкретном соревновании и на каком уровне (уровень тех, кто участвует в этом соревновании, указан в ячейке, например «Новичок», а те, кто участвует в этом соревновании, имеют свой уровень, указанный в ячейке, например «Новичок», а не введенные имеют пустые ячейки). Я получаю эти данные при их вводе (по форме, поэтому просто копирую и вставляю всю строку). Никаких проблем.
Лист 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 в качестве пятого отображаемого столбца, отсортировав его и изменив его на белый цвет текста. Этого было достаточно для того, что мне нужно (печатные списки), но при этом можно было копировать и вставлять информацию из форм, заполненных конкурентами :)
Обновлено: чтобы это работало, используйте 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