☰

別の列の一致と 3 番目の列での最初の個別の値の発生に基づいて列の値を合計する

tag-icon • tag-icon microsoft-excel worksheet-function
別の列の一致と 3 番目の列での最初の個別の値の発生に基づいて列の値を合計する

別の列の値との一致に基づいて列の値を合計する数式ベースのソリューションを見つけようとしていますが、3 番目の列の値の個別の出現に対してのみ計算します。

簡略化されたサンプルスプレッドシートを次に示します。

これは非常に基本的なサンプルスプレッドシートです

列 F (A、B、C) の文字を「文字」列 B と一致させ、「数値」列 C の個別の数字ごとに「値」列 D の値を合計し、その合計を列 G の「値の合計」に表示する必要があります。

正しい合計は G 列のセルに表示されますが、これを実行する数式がありません。ご協力いただければ幸いです。

答え1

この種の問題では、配列の観点から考えると役立ちます。

文字 (列 B) が "A" に等しく、数値から重複が削除された値 (列 D) の数値の配列 (リスト) を取得できる場合は、配列を合計するだけで答えを得ることができます。

この表現:

(B$2:B$12=F2)

True/False列 B が "A"である値の配列を返しますTrue。次のようになります:

(C$2:C$12<>C$3:C$13)

列 C のセルが次のセルと等しくない値の配列を返します。重複は連続したセルにあるため (常にそうでない場合は以下にコメントしてください)、この配列には値の余分な出現箇所が含まTrue/Falseれ、基本的に重複が除外されます。これら 2 つの配列を掛け合わせると次のようになります。TrueFalse

(B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)

値を 1 と 0 に変換しTrue/False、合計に含める位置に 1 が含まれる配列を生成します。この配列を の として使用しlogical_test、IF()列 D を として使用しますvalue_if_true。

IF((B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)>0,D$2:D$12)

列 D の値のうち、1 があった場所には値の配列が返され、False0 があった場所には値の配列が点在します。これで、配列を合計することができます。この数式を G2 から下方向に入力すると、以下に示す結果が得られます。

=SUM(IF((B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)>0,D$2:D$12))

これは配列数式なので、 とともに入力する必要があることに注意してくださいCTRLShiftEnter。

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

答え2

データに列を追加できる場合は、次の簡単な例が機能します。

  1. 文字と数字を連結する数式を追加します。この数式には列 A、つまりセル A2 を使用しました: =B2&C2
  2. この数式を下にドラッグしてセルA2:A12に適用します
  3. 文字と数字の各連結が最初の一意の連結であるかどうかをテストする数式を追加します。この数式には列 E、つまりセル E2 を使用しました: =COUNTIF(A$2:A2,A2)
  4. この数式を下にドラッグしてセル E2:E12 に適用します。
  5. H2 で次の数式を使用して、文字と数字の最初の連結、つまりセル G2 のみで文字が一致する値を合計します: =SUMIFS($D$2:$D$12,$B$2:$B$12,F2,$E$2:$E$12,1)
  6. この数式を下にドラッグしてセルG2:G4に適用します

関連情報