英文:
Sum the values in respective columns if the value in first column are same in MS Excel
问题
需要在第一行的每个相同数值下方获取总和。我尝试了Subtotal(9, range)，但这需要大量手动工作。我有大约1000多行需要汇总。
图片中显示了问题的示例（着色的单元格）。
英文:
Need to get the total sum at the bottom for each value in the first row if they are same. I tried Subtotal(9,range) but this requires lot of manual work. I have around 1000+ rows which needs to be summed.
Example of the problem in the picture below. (colored cells)
答案1
得分: 2
使用SUM()和FILTER()。BYROW()用于动态溢出数组以迭代条件。
=BYROW($A$13:$A$15,LAMBDA(x,SUM(FILTER(B$2:B$9,$A$2:$A$9=x))))
一公式搞定-
=LET( id,UNIQUE(A2:A9), x,MAKEARRAY(ROWS(id),COLUMNS(B2:D9),LAMBDA(r,c,SUM(CHOOSECOLS(FILTER(B2:D9,A2:A9=r),c)))), HSTACK(id,x))
英文:
Use SUM() with FILTER(). BYROW() is for dynamic spill array to iterate criteria's.
=BYROW($A$13:$A$15,LAMBDA(x,SUM(FILTER(B$2:B$9,$A$2:$A$9=x))))
All in one formula-
=LET(
id,UNIQUE(A2:A9),
x,MAKEARRAY(ROWS(id),COLUMNS(B2:D9),LAMBDA(r,c,SUM(CHOOSECOLS(FILTER(B2:D9,A2:A9=r),c)))),
HSTACK(id,x))
答案2
得分: 2
你是对的，使用小计功能是正确的，但我认为你错误地将小计功能视为Excel函数，而不是Excel的完整功能，如此处所述：
如何启动和配置小计功能：
在左边栏点击<kbd>2</kbd>之前的第一个结果：
在左边栏点击<kbd>2</kbd>后的最终结果：
英文:
You were right using subtotals, but I believe you were wrong using subtotals as an Excel function, instead of the Excel complete feature, as described here:
How to start and configure subtotals:
First result, before clicking on <kbd>2</kbd> (in the left margin):
After clicking on <kbd>2</kbd> in the left margin:
Final result:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论