☰

sumproduct を first * first ではなく first * last で乗算する

tag-icon • tag-icon microsoft-excel microsoft-excel-2007
sumproduct を first * first ではなく first * last で乗算する

Excel 2007 で次の操作を実行するための関数または数式を探しています。

数字が 2 行あります。

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

sumproduct と同様の操作、つまり各列のエントリを掛け合わせて合計を加算する操作を実行したいのですが、一方の行の最初のエントリをもう一方の行の最後のエントリで掛け、次に 2 番目のエントリを最後から 2 番目のエントリで掛け、というように掛け算をしたいのです。

それで：

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)

そして、SUMPRODUCT1 行目と 3 行目も同様に行います。

1つのステートメントだけでそれを実行する方法がわかりません

答え2

例が および に配置されていると仮定するとA1:E1、A2:E2次の式は、 2 番目の範囲を に渡す方法を変更するだけで、必要な処理を 1 つの数式で実行します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) に対して反復処理を行いますが、 によって計算された水平オフセットを使用します。水平オフセットCOLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1は、 の列数よりA2:E21 少ない数 (つまり、最後のエントリ) から始まり、ゼロ (つまり、最初のエントリ) まで減少します。そのため、配列として読み取られる場合は逆の順序で読み取られます。

これは、空のセルやテキストなどがゼロとして読み取られ、これらの場合にエラーがN()発生しないようにするための呼び出しでラップされています。#VALUE!欲しいこれを使用するとエラーが発生する場合は、を使用しないでくださいN()。

SUMPRODUCT()次に、これを2 番目に使用される配列として単純にドロップします。

答え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))))

ここに画像の説明を入力してください

上記のような結果が得られます。通常はSUMPRODUCT220 になります。

欠点は、フォントの色を変更したり、列や行を非表示にしたりして、余分なセルを非表示にする必要があることです。余分なセルは、数式の 2 番目の部分 (INDEX 部分) を強制的に「移動」したり、値の配列を返したりするために必要です。

関連情報