Фильтруйте и сортируйте данные с помощью нескольких критериев столбца и строки и сопоставляйте с ними соответствующие данные с помощью фиксированных и гибких return_arrays
Цель
Конечным результатом является Range M26:Q34, который представляет собой нисходящий список на основе 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_array3вCell N16иret_array4вCell O16.
(Примечание:lookup_array=rowCrit-->Cell M16=Cell M11)
В отличие от этого вопроса первые два столбца
ProductиTypeфиксированы, что означает, что они всегда должны появляться по умолчанию.
Только столбцы после них являются гибкими в зависимости от входных данных вCell N16иCell O16.
К формуле должно быть легко присоединить еще большеret_arrays.
- Из списка на шаге 2 должны отображаться только те значения, которые ранжированы по позициям на основе
Cell M21иCell N21.
В этом случае10 positionsпосле1st position. - После шагов 1–3 на шаге 4 должен появиться окончательный список.
Как мне нужно изменить формулу из этого вопроса, чтобы она работала?
=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, TOROW(HSTACK(N16,O16,M16),1),
_e, CHOOSECOLS(A1:K31,XMATCH(_d,A1:K1)),
_f, COLUMNS(_d),
_g, SORT(FILTER(HSTACK(_e,FILTER(A1:K31,A1:K1=M11,"")),_a*_b*_c,""),_f+1,-1),
VSTACK(_d, WRAPROWS(TOCOL(INDEX(_g,SEQUENCE(M21,,N21),SEQUENCE(,_f)),2),_f)))
Изменил на 2023-S.
Еще один, Product D -- Type t3 --> Shop 1 показан дважды. Разве не должно быть один раз 160 и еще один 130?
Тоже поменяй.
Ответы 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, TOROW(HSTACK("Product","Type",N16,O16,M16),1),
_e, CHOOSECOLS(A1:K31,XMATCH(_d,A1:K1)),
_f, COLUMNS(_d),
_g, SORT(FILTER(HSTACK(_e,FILTER(A1:K31,A1:K1=M11,"")),_a*_b*_c,""),_f+1,-1),
VSTACK(_d, WRAPROWS(TOCOL(INDEX(_g,SEQUENCE(M21,,N21),SEQUENCE(,_f)),2),_f)))
Есть ли также способ сделать это, когда заголовок столбца отсутствует, поэтому он фиксируется в столбце A и столбце C?
Извините, я не могу понять, не могли бы вы объяснить подробнее.
Но ваша цель — иметь фиксированные столбцы для Products и Type, тогда почему A To C?
Ваше решение работает. Это был скорее дополнительный вопрос от меня, есть ли опция без ссылки на заголовок столбца, чтобы формула была независима от имени заголовка и основывалась на самом столбце.
Я также могу открыть для этого отдельный вопрос, но не уверен, что это необходимо, если решение простое, и вы можете добавить к своему ответу здесь.
Дай мне подумать, что ты хочешь сказать. я обновлю
Это означает, что в моем примере вместо ссылок на «Продукт» и «Тип» вы ссылаетесь на столбец A и столбец K. Я думаю, что этот пример немного сбивает с толку, потому что лучше иметь пример, в котором он будет относиться к столбцу A. Столбец C, а также «Тип» и «2023-M» представляют собой гибкие return_arrays. Позже я открою новый вопрос.
@Michi Теперь я понимаю, почему бы просто не сослаться на ячейку заголовков столбцов следующим образом: TOROW(HSTACK(A1,K1,N16,O16,M16),1)
Column 2023-Mбудет в процентах, и, кстати, вот что вам нужно ввести:TOROW(HSTACK("Product","Type",N16,O16,M16),1)