Объединение функций Excel ФИЛЬТР() и СУММ()
Насколько это возможно, я хочу взять табличный набор данных в Excel, в котором есть (намеренно) повторяющиеся идентификаторы записей, и объединить его в уникальный/суммированный набор данных (очень часто встречается в той работе, которую я выполняю).
Я, конечно, могу сделать это с помощью SUMIFS() и написать сценарий решения на основе VBA, но мне бы очень хотелось сделать это с помощью функций динамических массивов Excel (вычисления в реальном времени и отсутствие необходимости в книгах с поддержкой макросов).
Прикреплено наглядное изображение:
Менее очевидным здесь является то, что я использовал UNIQUE() в ячейке H4 (таким образом, ссылка на H4# в формуле, как показано в ячейке J4). В операторе LET() я определяю исходные данные как значения, а идентификаторы как цвет/тип, которые затем объединяю в одну строку, которую называю «srcKey».
Их уникальные комбинации затем помещаются в массив H4#, который я фиксирую как «fltrKey». Моим желаемым результатом было бы сохранить разбиение столбца (отсюда и функция BYCOL()), но суммировать строки, в которых fltrKey находится внутри srcKey. Это не дало правильных результатов, поэтому я перешел с fltrKey на прямые ссылки на ячейки H4 и I4 соответственно (просто в качестве теста).
Это дает мне правильные суммарные значения для синих птиц, но мне нужна динамическая формула, которая бы делала это для каждой комбинации в моем fltrKey.
Возможно, мой метод здесь (использование FILTER() с ISNUMBER() и SEARCH() не является подходящим подходом). Я могу получить желаемые результаты, просто выполняя по одному столбцу исходных значений за раз, но это не идеально/предпочтительно. Я почти уверен, что Excel справится с этим, мне просто нужна помощь в правильной методологии.
Мне было неясно. Я могу выполнить это с помощью SUMIFS() или решения на основе VBA (не используя оба вместе).
Ответы 3
Один из способов — использовать MAKEARRAY:
=LET(
clr,B4:B10,
typ,C4:C10,
unq,H4#,
val,D4:F10,
MAKEARRAY(ROWS(unq),COLUMNS(val),LAMBDA(z,y,SUMIFS(INDEX(val,0,y),clr,INDEX(TAKE(unq,,1),z),typ,INDEX(TAKE(unq,,-1),z)))))
Одна запись:
Как только GROUPBY станет широко доступен, его можно будет использовать здесь:
=GROUPBY(B3:C10,D3:F10,SUM,3,0)
Хорошее дополнение к GROUPBY.
«СУММЕСЛИ» по столбцам
- Чтобы получить все это, включая заголовки и метки строк, вы можете попробовать следующее:
=LET(data,B3:F13,u_cols,2,u_sort_cols,{2;1},
h,TAKE(data,1),
d,DROP(data,1),
du,SORT(UNIQUE(TAKE(d,,u_cols)),u_sort_cols),
sv,DROP(d,,u_cols),
dv,MAKEARRAY(ROWS(du),COLUMNS(sv),LAMBDA(r,c,
SUM(FILTER(INDEX(sv,,c),(INDEX(d,,1)=INDEX(du,r,1))
*(INDEX(d,,2)=INDEX(du,r,2)))))),
r,VSTACK(h,HSTACK(du,dv)),
r)
- Замените последнюю
rлюбой другой переменной, чтобы увидеть, что она возвращает.
Просто ради удовольствия работаю только с требованием диапазона SUMIFS;
=LAMBDA(srcVal1, srcVal2, srcVal3, srcColors, srcTypes, header,
LET(
sum_, LAMBDA(srcVal,
SUMIFS(srcVal, srcColors, srcColors, srcTypes, srcTypes)
),
VSTACK(
header,
UNIQUE(
HSTACK(
srcColors,
srcTypes,
sum_(srcVal1),
sum_(srcVal2),
sum_(srcVal3)
)
)
)
)
)(D4:D10, E4:E10, F4:F10, B4:B10, C4:C10, B3:F3)
Использование REDUCE:
=LAMBDA(srcVals, srcColors, srcTypes, header,
LET(
sum_, LAMBDA(acc, srcValCol,
HSTACK(
acc,
SUMIFS(
INDEX(srcVals, , srcValCol),
srcColors, srcColors,
srcTypes, srcTypes
)
)
),
sums, REDUCE(
HSTACK(srcColors, srcTypes),
SEQUENCE(COLUMNS(srcVals)),
sum_
),
VSTACK(header, UNIQUE(sums))
)
)(D4:F10, B4:B10, C4:C10, B3:F3)
Как вы думаете, почему для использования
SUMIFSпотребуется VBA? Я этого не вижу.