2D-在Excel中查找不同组合的最大和 [英] 2D - Finding largest sum of different combination in 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中查找不同组合的最大和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！