Сопоставьте и перечислите значения в списке, включая критерии нескольких столбцов
В Range K9:K16 перечислены все значения из Column H, которые соответствуют критериям столбца в Range K4:K6 и Range L4:L6.
В Range L9:L16 я хочу добавить значение столбца на основе переменной return_array, введенной в Cell N6.
Что касается этого вопроса, мне удалось разработать следующую формулу:
=IFERROR(LET(
a; K9:K17;
b; A1:I1;
c; A3:I20;
d; XLOOKUP(N4;b;c;"");
MAP(a;LAMBDA(α; @DROP(TOCOL(FILTER(IFS(d=α;IF(c=0;x;c));N6=b);3);
COUNTIF(K9:α;α)-1))));"")
Это решение работает почти идеально.
Однако сейчас проблема заключается в значениях Cell H3 и Cell H5. Эти значения равны некоторым значениям в Range K9:K16.
Поэтому они учитываются в приведенной выше формуле, а список в Range L9:L16 неверен.
Я думаю, что чтобы решить эту проблему, критерии столбцов в Range K4:K6 и Range L4:L6 нужно каким-то образом добавить к формуле выше.
У вас есть идеи, как это сделать?
Ответы 4
Вы можете использовать COUNTIFS для фильтрации массива поиска и возврата массива:
=LET(
included, COUNTIFS(K4:K5, A3:A20, L4:L5, C3:C20),
lookup_array, FILTER(XLOOKUP(N4, A1:I1, A3:I20, ""), included, ""),
return_array, FILTER(XLOOKUP(N6, A1:I1, A3:I20, ""), included, ""),
selected, K9:K16,
MAP(
selected,
LAMBDA(x,
LET(
filtered, FILTER(return_array, lookup_array = x, ""),
current_count, COUNTIF(K9:x, x),
INDEX(filtered, MIN(current_count, ROWS(filtered)), 1)
)
)
)
)
Европейский (;) формат:
=LET(
included; COUNTIFS(K4:K5; A3:A20; L4:L5; C3:C20);
lookup_array; FILTER(XLOOKUP(N4; A1:I1; A3:I20; ""); included; "");
return_array; FILTER(XLOOKUP(N6; A1:I1; A3:I20; ""); included; "");
selected; K9:K16;
MAP(
selected;
LAMBDA(x;
LET(
filtered; FILTER(return_array; lookup_array = x; "");
current_count; COUNTIF(K9:x; x);
INDEX(filtered; MIN(current_count; ROWS(filtered)); 1)
)
)
)
)
Не уверен, какая терминология правильная. Я просто назову это ";" формат с этого момента.
Эй, не могли бы вы улучшить это, чтобы элементы в K4:K5 не находились в возвращаемом массиве? Например, если Product G написано с ошибкой, то для второго 600 в selectedfiltered вернет только один 600, а INDEX(filtered, current_count = 2, 1) приведет к ошибке.
@nkalvi, изменено на INDEX(filtered, MIN(current_count, ROWS(filtered)), 1)
Надеюсь, эта ссылка работает: 1drv.ms/x/s!AjsOQOZMdif-kjP8aNLIWu1hx-DJ?e=ScVKbR
@rachel, с INDEX(filtered, MIN(current_count, ROWS(filtered)), 1) результаты тоже неверны, хотя ошибок нет. Например, он дает 6 строк «Продукт D» с K4:K6 = (Продукт D, Продукт).
@nkalvi, я помню, в своем предыдущем посте она сказала, что частота «выбранных данных» будет такой же, как частота исходных данных. но если вас это очень беспокоит, вы можете использовать if (ROWS(filtered)<current_count, "", index(filtered, current_count,1))
Прошу прощения, @rachel, я не рассматривал это в контексте предыдущих публикаций. Нет, меня это совершенно не беспокоит. Мне нравится откровенность ваших ответов.
=SORT(FILTER(CHOOSECOLS(A3:I20,XMATCH(N4,A1:I1),XMATCH(N6,A1:I1)),COUNTIFS(K4:K6,A3:A20,L4:L6,C3:C20)),,-1)
Простой COUNTIFS можно использовать в качестве критерия фильтра для FILTER в столбцах, которые вы хотите вернуть с помощью CHOOSECOLS, включая XMATCH.
Формула почти работает. Однако, когда, например, colcrit1 (K4:K6) или colcrit2 (L4:L6) пуст, выдается ошибка #CALC! ошибка.
Да, потому что нет аргументов для фильтрации. Чего бы вы ожидали в таком случае? Пустой? =SORT(FILTER(CHOOSECOLS(A3:I20,XMATCH(N4,A1:I1),XMATCH(N6,A1:I1)),COUNTIFS(K4:K6,A3:A20,L4:L6,C3:C20),""),,-1)
Спасибо за ответ. Понял вашу точку зрения. Открыл для этого новый вопрос. stackoverflow.com/questions/78717418/…
Продлеваю от П.б
=LET(
filtered, FILTER(
CHOOSECOLS(
A3:I20,
XMATCH(N4, A1:I1),
XMATCH(N6, A1:I1)
),
COUNTIFS(K4:K6, A3:A20, L4:L6, C3:C20)
),
freq, LAMBDA(arr,
MAP(
SEQUENCE(ROWS(arr)),
LAMBDA(i,
SUM(--(TAKE(arr, i) = INDEX(arr, i, )))
)
)
),
TOCOL(
XLOOKUP(
K9:K16 & " " & freq(K9:K16),
CHOOSECOLS(filtered, 1) & " " &
freq(CHOOSECOLS(filtered, 1)),
CHOOSECOLS(filtered, 2)
),
2
)
)
Я упустил аргумент result_array, являющийся динамическим вводом. Обновил это в своем ответе.
Ах да, я тоже это пропустил. спасибо @P.b. Я обновлю ответ, чтобы отразить изменения :) Кстати, есть ли у вас более короткий метод для получения текущего подсчета?
Проведение подсчета чего? (СКАНИРОВАНИЕ или КАРТА будут хорошим выбором оружия?)
Текущий подсчет значений, например 600 1, 600 2, 400 1, 400 2 и т. д. Здесь я использую MAP вместе с SUM + IF. Поскольку у вас и @JvdV есть много кратких методов, мне было интересно узнать о других компактных способах.
Спасибо за комплимент. Трудно сказать без данных, но что-то в этом роде: =let(r,range,map(r,lambda(x,sum(n(@r:x=x)))))
Спасибо — использование @ выдает предупреждение на Mac; Я сократил СУММУ + ЕСЛИ до СУММ только в своем ответе.
@r будет относиться к началу диапазона. Это может быть A2, например: =let(r,A2:A10,map(r,lambda(x,sum(n(A2:x=x)))))Знаю, я опаздываю, и причина в том, что я совсем пропустила опечатку в ColCrit1, как П.б Сэр, так и Рейчел (данные взяла у нее, поделилась ссылкой на Excel в комментариях), уточнила проблема в контексте ОП.
У меня была та же идея использовать COUNTIFS(), но поскольку я боролся с опечаткой, о которой я не подозревал, или из-за моей ошибки, проигнорировав ее, поэтому я не мог публиковать сообщения в режиме реального времени, а также вместо использования COUNTIFS(), который уже использовался во всех выше ответы, вот моя альтернатива сохранению исходной формулы нетронутой, как в OP, использует комбинацию XMATCH()+ISNA()+1, также известную XMATCH()+ISNUMBER():
• Формула, используемая в ячейке L9
=LET(
a, K9:K16,
b, A1:I1,
c, A3:I20,
d, XLOOKUP(N4,b,c,""),
MAP(a,LAMBDA(α, @DROP(TOCOL(FILTER(IFS((d=α)*
(1-ISNA(XMATCH(A3:A20,K4:K5)+XMATCH(C3:C20,L4:L5))),
IF(c=0,x,c)),N6=b,""),3),
COUNTIF(K9:α,α)-1))))
Формула почти работает. Однако, когда, например, colcrit1 (K4:K6) или colcrit2 (L4:L6) пуст, выдается ошибка #CALC! ошибка.
@Michi, так какой же должен быть результат в этом сценарии, ты хочешь вернуть пустое значение?
Понял вашу точку зрения. Открыл для этого новый вопрос. stackoverflow.com/questions/78717418/…
Я нахожусь в Европе, и не все используют ";", но это, вероятно, связано с тем, что я работаю с представителями 90 национальностей, обучая их Excel...