☰

2つのテーブルにまたがる複数の条件で総売上を計算する

tag-icon • tag-icon microsoft-excel worksheet-function
2つのテーブルにまたがる複数の条件で総売上を計算する

私は現在、表1総売上を計算しようとしています[コラムC]同じ製品ID在庫単位と販売単位を一致させる表2SUMIFS 式を次のように使用してみました。

=SUMIFS(Table2!E:E, Table2!A:A, Table1!A1, Table2!C:C, Table2!D:D)

しかし、期待した結果が得られていないようです。どなたか手を貸して、この件について手伝っていただけませんか? ご協力いただければ幸いです。ありがとうございます!

表1（望ましい結果）：

表1

表2:

表2

答え1

私が正しく理解していれば、SUMIFS()目的の出力に機能する適切な関数ではなく、代わりにスクリーンショットに示すように次の関数を使用します。

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

• セルに使用されている数式C2をSheet Named Table1下に記入します:

=LET(
     _extract, FILTER(Table2!C$2:E$19,(Table2!A$2:A$19=Table1!A2)*(Table1!B2=Table2!B$2:B$19),""),
     SUM(--(INDEX(_extract,,1)=INDEX(_extract,,2))*TAKE(_extract,,-1)))
  • 関数を使用して、ヘッダーを として、およびに基づいてFILTER()データの範囲を抽出します。Stock UnitSales UnitSalesIDName
  • 次に、SUM()ブール演算を実行する関数を使用して、上記で抽出した配列Sales Unitと配列を比較しStock Unit、対応する配列の積の合計を計算します。

また、 を使用している場合はMS365、出力全体を 1 つの数式で出力できるため、ここに入力する必要はありません。

=MAP(A2:A7,B2:B7,LAMBDA(x,y,
  LET(z, FILTER(Table2!C:E,(Table2!A:A=x)*(Table2!B:B=y),""), 
  SUM(--(INDEX(z,,1)=INDEX(z,,2))*TAKE(z,,-1)))))
  • 関数を使用してMAP()各セルの値を反復し、カスタムLAMBDA()計算を実行します。
  • 内では、LAMBDA()を使用して、 3つの必要な列を抽出する関数をLET()渡し、にFILTER()基づいてIDName
  • 次に、ここで対応する配列の積を合計し、Stock Unit-->INDEX(z,,1)とSales Unit-->を比較してINDEX(z,,2)、およびを返しTRUE、FALSE同じものを と乗算して、Sales合計後の目的の出力を取得します。

SUMIFS()簡単な方法の 1 つは、ColumnSales Unitを としてCriteria Range、Stock UnitsColumn を として使用することですCriteriaが、これは好みによって異なります。

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

=SUMPRODUCT(
    SUMIFS(
        Table2!E:E,
        Table2!A:A, Table1!A2,
        Table2!B:B, Table1!B2,
        Table2!D:D, UNIQUE(
            Table2!C:C
        )
    )
)

もう 1 つの代替方法がありますが、これは推奨されますが、Excel の効率が低下します。

=SUM(FILTER(Table2!E:E,(IFNA(XMATCH(Table2!D:D,UNIQUE(Table2!C:C)),0))*(A2=Table2!A:A)*(Table2!B:B=Table1!B2),0))

関連情報