Фильтруйте и сортируйте данные с помощью нескольких критериев столбца и строки, а затем сопоставляйте с ними соответствующие данные с помощью нескольких гибких return_arrays
Цель
Конечным результатом является Range M26:O34, который представляет собой нисходящий список на основе lookup_array в Cell M16 и нескольких фильтров столбцов и строк (Range M6:M8,Range N6:O8, Range O6:O8 и Cell M11) и выбранных позиций в рейтинге на основе Cell M21 и Cell N21.
Чтобы получить этот список, необходимо выполнить следующие шаги:
(Вы также можете увидеть шаги в таблице в столбце M, чтобы вам было легче следовать инструкциям)
- Определите несколько критериев столбца в
Range M6:O6и критерии строки вCell M11. Все эти критерии являютсяANDкритериями. После фильтрации список следует отсортировать по убыванию. - На основе шага 1 у вас будет нисходящий список, соответствующий критериям столбца и строки. Для этого списка следует добавить соответствующие значения, основанные на
ret_array1вCell N16иret_array2вCell O16.
(Примечание:lookup_array=rowCrit-->Cell M16=Cell M11) - Из списка на шаге 2 должны отображаться только те значения, которые ранжированы по позициям на основе
Cell M21иCell N21.
В этом случае10 positionsпосле1st position. - После шагов 1–3 на шаге 4 должен появиться окончательный список.
Поэтому вкратце мне нужны эти две формулы:
Формула 1: Получение нисходящего списка с учетом нескольких критериев и отображение только определенных позиций из этого списка.
Формула 2: Получение соответствующих данных на основе нескольких гибких return_arrays.
Текущее состояние
С помощью предыдущих вопросов я смог применить эти две формулы:
1. Получаем необходимый список
Ответ на этот вопрос
=TOCOL(INDEX(SORT(
LET(
a,COUNTIF(M6:M8,A1:A31)+AND(M6:M8 = ""),
b,COUNTIF(N6:N8,C1:C31)+AND(N6:N8 = ""),
c,COUNTIF(O6:O8,K1:K31)+AND(O6:O8 = ""),
FILTER(FILTER(A1:K31,(A1:K1=M11),""),a*b*c,"")),,-1),SEQUENCE(M21,,N21)),2)
Эта формула уже работает отлично и без каких-либо проблем.
2. Получение соответствующих данных
Ответ на этот вопрос
SORT(FILTER(CHOOSECOL(A1:K30,
XMATCH(M16,A1:K1),
XMATCH(N16,A1:K1),
XMATCH(O16,A1:K1)),
(AND(M6:M8 = "")+COUNTIF(M6:M8,A1:A30))*
(AND(N6:N8 = "")+COUNTIF(N6:N8,C1:C30))*
(AND(O6:O8 = "")+COUNTIF(O6:O8,K1:K30)),""),,-1)
Эта формула почти дает мне правильный результат.
Вопрос
Что касается второй формулы, я не знаю, как к ней применить SEQUENCE и как упорядочить columns, чтобы они соответствовали желаемому результату, показанному в таблице выше.
Что мне нравится в обеих формулах, так это то, что к ним очень легко связать дополнительные критерии столбца, поскольку в исходном файле их еще больше. Было бы здорово сохранить эту гибкость.
Есть ли у вас идеи, как решить проблему?
Ответы 1
Пытаться :
=LET(
_a, COUNTIF(M6:M8,A1:A31)+AND(M6:M8 = ""),
_b, COUNTIF(N6:N8,C1:C31)+AND(N6:N8 = ""),
_c, COUNTIF(O6:O8,K1:K31)+AND(O6:O8 = ""),
_d, CHOOSECOLS(A1:K31,XMATCH(HSTACK(N16,O16,M16),A1:K1)),
_e, SORT(FILTER(HSTACK(_d,FILTER(A1:K31,A1:K1=M11,"")),_a*_b*_c,""),3,-1),
WRAPROWS(TOCOL(INDEX(_e,SEQUENCE(M21,,N21),{1,2,3}),2),3))
В зависимости от языка, который вы используете в Excel, {1,2,3} можно ввести как {1.2.3}, чтобы это работало.
Например, в немецком Excel формула работает так:
=LET(
_a; ZÄHLENWENN(M6:M8;A1:A31)+UND(M6:M8 = "");
_b; ZÄHLENWENN(N6:N8;C1:C31)+UND(N6:N8 = "");
_c; ZÄHLENWENN(O6:O8;K1:K31)+UND(O6:O8 = "");
_d; SPALTENWAHL(A1:K31;XVERGLEICH(HSTAPELN(N16;O16;M16);A1:K1));
_e; SORTIEREN(FILTER(HSTAPELN(_d;FILTER(A1:K31;A1:K1=M11;""));_a*_b*_c;"");3;-1);
ZEILENUMBRUCH(ZUSPALTE(INDEX(_e;SEQUENZ(M21;;N21);{1.2.3});2);3))
Альтернативно замена {1.2.3} на SEQUENZ(;3) также работает:
=LET(
_a; ZÄHLENWENN(M6:M8;A1:A31)+UND(M6:M8 = "");
_b; ZÄHLENWENN(N6:N8;C1:C31)+UND(N6:N8 = "");
_c; ZÄHLENWENN(O6:O8;K1:K31)+UND(O6:O8 = "");
_d; SPALTENWAHL(A1:K31;XVERGLEICH(HSTAPELN(N16;O16;M16);A1:K1));
_e; SORTIEREN(FILTER(HSTAPELN(_d;FILTER(A1:K31;A1:K1=M11;""));_a*_b*_c;"");3;-1);
ZEILENUMBRUCH(ZUSPALTE(INDEX(_e;SEQUENZ(M21;;N21);SEQUENZ(;3));2);3))
См. вопрос в отредактированном ответе.
@Мичи, ты не против, если я выложу Excel? Почему на вашей стороне возвращается только одна строка? Есть ли пробелы в ваших данных или что-то в этом роде?
Для меня это нормально, если вы опубликуете Excel.
@Michi Эти цифры 10 и 2 настоящие цифры?
Да, это цифры. Я просто выровнял их по левому краю.
@Michi скачать можно здесь: ссылка
Первое мое предположение: эта часть {1;2;3} может вызвать проблему. В немецком формате Excel мне нужно изменить «,» на «;» в формуле. Наверное, в этой части формулы мне нужно остановиться на ",". Однако когда я меняю эту часть на «», появляется сообщение об ошибке Excel: «С этой формулой возникла проблема». Что еще странно, так это то, что когда я открываю вашу ссылку, названия формул переводятся на немецкий язык, но знак "," остается, и формула работает. См. скриншот 3, который я прикрепил.
@Мичи, хммм, попробуй сделать это . или / может быть вот так. простой способ обнаружить запись в какой-либо ячейке. 1,2,3 и используйте HSTACK() для объединения, затем выберите формулу HSTACK() и нажмите F9, иначе при выборе отобразится разделитель, который вам нужно будет использовать.
@Michi или используйте вместо {1,2,3} --> SEQUENCE(;3) --> SEQUENZ(;3)
Изменение на "." работал. Проголосуйте за дополнительную помощь с моей стороны.
@Michi звучит хорошо, я прокомментировал альтернативный вариант вместо жесткого кодирования, используя SEQUENZ()
Спасибо. Это также работает. Я также добавил это в ответ.
@Michi исправил опечатку с SEQUENCE() на SEQUENZ()
Попробуйте:
=LET( a,COUNTIF(M6:M8,A1:A31)+AND(M6:M8 = ""), b,COUNTIF(N6:N8,C1:C31)+AND(N6:N8 = ""), c,COUNTIF(O6:O8,K1:K31)+AND(O6:O8 = ""), WRAPROWS(TOCOL(INDEX(SORT(FILTER(HSTACK(CHOOSECOLS(A1:K31,XMATCH(HSTACK(N16,O16,M16),A1:K1)),FILTER(A1:K31,A1:K1=M11,"")),a*b*c,""),3,-1),SEQUENCE(M21,,N21),{1,2,3}),2),3))