本文介绍了2D-在Excel中查找不同组合的最大和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有按项目列出的每月销售额,想找到哪种组合提供的总金额最大.例子,
I have this monthly sales by item and want to find which combination gives the largest sum. Example,
Feb 2020 Mar 2020 Apr 2020 May 2020
Shoes 2 2 1 0
Phone 2 3 10 1
Computer 5 7 7 10
答案是5 + 7 + 10 + 10 = 32.我需要找到超过50个项目的最大金额.任何帮助都非常感谢.
The answer is 5+7+10+10=32. I need to find the largest sum for over 50 items. Any help very much appreciated.
推荐答案
列的另一种选择:
=SUM(SUBTOTAL(4,OFFSET(INDEX(B2:E4,1,1),,COLUMN(B2:E4)-MIN(COLUMN(B2:E4)),ROWS(B2:E4))))
对于行:
=SUM(SUBTOTAL(4,OFFSET(INDEX(B2:E4,1,1),ROW(B2:E4)-MIN(ROW(B2:E4)),,,COLUMNS(B2:E4))))
这些是数组公式,因此在编辑后通过按 ctrl
+ shift
+ enter
These are array formulas so after editing confirm them by pressing ctrl
+ shift
+ enter
这篇关于2D-在Excel中查找不同组合的最大和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!