本帖最后由 tmplinshi 于 2024-2-10 03:03 编辑
单公式, 先把 A4:M11 清空,然后设置 A4 的公式为:
- =LET(
- src, VSTACK('1月:12月'!A23:Y30),
- srcNames, CHOOSECOLS(src, 1),
- uniqNames, UNIQUE(srcNames),
- cols, {4, 5, 8, 9, 12, 13, 16, 17, 20, 21, 24, 25},
- sumColumns, LAMBDA(arr, arrCol, i, LET(
- col, INDEX(arrCol, 1, i),
- arrOneCol, CHOOSECOLS(arr, col),
- n, SUM(IFERROR(arrOneCol,0)),
- IF(i < COLUMNS(arrCol), HSTACK(n, sumColumns(arr, arrCol, i+1)), n)
- )),
- loop, LAMBDA(i, LET(
- name, INDEX(uniqNames, i),
- srcOneName, FILTER(src, srcNames = name),
- sums, HSTACK(name, sumColumns(srcOneName, cols, 1)),
- IF(i < ROWS(uniqNames), VSTACK(sums, loop(i+1)), sums)
- )),
- loop(1)
- )
复制代码 |