☰

乘積與第一個*最後一個而不是第一個*第一個

tag-icon • tag-icon microsoft-excel microsoft-excel-2007
乘積與第一個*最後一個而不是第一個*第一個

我正在 Excel 2007 中尋找一些函數或公式來執行以下操作：

我有兩行數字：

1 -1  2 5 10 
1  2 -1 2  5

我想做一些類似於 sumproduct 所做的事情，即將每列中的條目相乘，然後添加總計。但是，我想將其中一行中的第一個條目乘以另一行中的最後一個條目，然後將第二個條目乘以第二個條目，依此類推。

所以：

1 * 5 + -1 * 2 + 2 * -1 + 5 * 2 + 10 * 1

而不是僅僅

1 * 1 + -1 * 2 + ...

是否有一些合理的方法來進行這種逆序總和乘積類型的計算？

我不想為我想要做的每個總和產品以相反的順序創建額外的行。

答案1

您可以使用下列公式新增另一行（例如第 3 行）：

=INDEX($A$2:$J$2,COUNTA($A$2:$J$2)-COLUMN()+1)

然後SUMPRODUCT對第一行和第三行進行操作。

我看不到一種方法可以只用一個語句來做到這一點

答案2

假設您的範例位於 和 中A1:E1，A2:E2以下內容將在單一公式中完成您所需的操作，只需修改第二個範圍的輸入方式即可SUMPRODUCT()：
（為了清晰起見並避免滾動條換行）

=SUMPRODUCT(A1:E1,
            N(OFFSET(A2:E2,0,COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1))
           )

快速瀏覽一下元件：

OFFSET(A2:E2,0,COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1)

SUMPRODUCT()這有效地在範圍 上進行迭代（因為它被用作數組） A2:E2。它在同一行（0）上執行此操作，但隨後使用 計算出的水平偏移量，該偏移量將從less one（即最後一個條目）COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1中的列數開始，並減少到零（即第一個條目），A2:E2因此，當作為數組讀取時，將以相反的順序讀取。

這被包裝在一個N()呼叫中，以確保任何空白單元格、文字等都被讀取為零，並防止#VALUE!在這些情況下出現錯誤。如果你想這會產生錯誤，然後不要使用N().

然後將其簡單地放入SUMPRODUCT()作為要使用的第二個數組中。

答案3

這是一個可以幫助您入門的 UDF。

Function SUMPRODREV(rForward As Range, rBackward As Range) As Double

    Dim i As Long, j As Long
    Dim vaForw As Variant
    Dim vaBack As Variant
    Dim dReturn As Double

    'put range values into arrays
    vaForw = rForward.Value: vaBack = rBackward.Value

    'if only 1 row, multiply columns
    If UBound(vaForw, 1) = 1 Then
        For i = LBound(vaForw, 2) To UBound(vaForw, 2)
            dReturn = dReturn + (vaForw(1, i) * vaBack(1, UBound(vaForw, 2) - (i - 1)))
        Next i
    Else 'if only 1 column, multiply rows
        For i = LBound(vaForw, 1) To UBound(vaForw, 1)
            dReturn = dReturn + (vaForw(i, 1) * vaBack(UBound(vaForw, 1) - (i - 1), 1))
        Next i
    End If

    SUMPRODREV = dReturn

End Function

答案4

這裡有一個公式可以滿足您的需要，但輸入它有一個技巧。

假設您的儲存格範圍如下所示。我將範圍命名為“頂部”和“底部”。

在此輸入影像描述

1
反白顯示任 2 個空白儲存格。就我而言，我選擇了單元格 B4:C4，如下所示：

在此輸入影像描述

2
將其輸入到公式欄作為數組公式（按Ctrl+ Shift+ Enter）：

=SUM(top*INDEX(bottom,1,LARGE(COLUMN(bottom),COLUMN(top))))

或這個（不使用命名範圍）：

=SUM(A1:J1*INDEX(A2:J2,1,LARGE(COLUMN(A2:J2),COLUMN(A1:J1))))

在此輸入影像描述

你會得到類似上面的結果；常規SUMPRODUCT收益率220。

缺點是您只需透過更改其字體顏色或隱藏其列或行來隱藏額外的儲存格。額外的儲存格是強制公式的第二部分（索引部分）「移動」或傳回值數組所必需的。

相關內容