Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2019)
SUMIFS関数について
投稿日時: 22/06/11 11:35:38
投稿者: TI

いずれかの条件に合致する行の集計を行いたいと考えています。
 
以下のデータがあります。
シート1
日付        番号        金額
6/1            05            10000
6/1            10            5000
6/2            33            3000
6/2            35            20000
 
シート2
条件1        05    10
条件2        33    35
 
集計シート
            条件1        条件2
6/1            15000
6/2                        23000
 
シート1の番号列の番号がシート2の各条件のいずれかに
合致する場合の金額を日付ごとに集計をしたいと考えています。
 
今は、SUMIFS関数を条件の数だけ「+」で結合して、希望の結果を
取得しています。
ただ、この方法ですと条件が増えるごとに数式の修正が必要と
なるので、条件を追加するだけでよい方法がないかと悩んでおります。
 

条件1に「15」が追加された場合は、集計シートの条件1列に
シート1の番号列の値が「15」の金額を「条件1」の集計に
加える。
 
何か良い方法がございましたら、アドバイスお願いします。

回答
投稿日時: 22/06/11 12:22:59
投稿者: んなっと

●Sheet1
 
   A   B   C
1 日付 番号  金額
2  6/1   5 10000
3  6/1  10  5000
4  6/2  33  3000
5  6/2  35 20000
 
●Sheet2
 
    A  B  C D E F
1 条件1  5 10   
2 条件2 33 35   
 
●集計
 
   A   B   C
1    条件1 条件2
2 6/1 15000   0
3 6/2   0 23000
 
B2
=SUMPRODUCT(SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,$A2,Sheet1!$B:$B,INDEX(Sheet2!$B:$F,MATCH(B$1,Sheet2!$A:$A,0),)))
右方向・→下方向・↓
 
もしかしたら
=SUM(SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,$A2,Sheet1!$B:$B,INDEX(Sheet2!$B:$F,MATCH(B$1,Sheet2!$A:$A,0),)))

投稿日時: 22/06/11 13:52:29
投稿者: TI

ご返信ありがとうございます。
 

引用:

=SUMPRODUCT(SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,$A2,Sheet1!$B:$B,INDEX(Sheet2!$B:$F,MATCH(B$1,Sheet2!$A:$A,0),)))
右方向・→下方向・↓

 
こちらで、実現できました。ありがとうございます。
 
ヘルプ等を見てもよくわからなかったのですが、
 
引用:

Sheet1!$B:$B,INDEX(Sheet2!$B:$F,MATCH(B$1,Sheet2!$A:$A,0),)

 
この部分で、
引用:

●Sheet2
  
    A  B  C D E F
1 条件1  5 10   
2 条件2 33 35

 
B列〜F列で一致するSUMIFSの配列を取得しているということで
良いのでしょうか。
 
もう少し、ヘルプ等を読み込んでみようと思いますが、どうして
このような結果を得ることができるのかが、よく理解ができて
おりません。
 
まずは、お礼まで

回答
投稿日時: 22/06/11 14:21:05
投稿者: んなっと

こんなのも。
=SUMPRODUCT(SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,$A2,Sheet1!$B:$B,INDEX(Sheet2!$B$1:$F$50,COLUMN(A2),)))
 
●Sheet2の行列を入れ替えればもっと簡単。おすすめです。
  
    A   B
1 条件1 条件2
2   5   33
3   10   35
  
●Sheet3
  
=SUMPRODUCT(SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,$A2,Sheet1!$B:$B,Sheet2!A$2:A$6))

回答
投稿日時: 22/06/11 20:46:51
投稿者: Mike

Sheet1          名前の管理
  A  B   C      名前 参照範囲     範囲
 日付 番号 金額     金額 =Sheet1!$C$2:$C$5 ブック
1 6/1  05 10,000    条件1 =Sheet1!$B$2:$E$5 ブック
2 6/1  10 5,000    条件2 =Sheet1!$B$3:$E$3 ブック
3 6/2  33 3,000    日付 =Sheet1!$A$2:$A$5 ブック
4 6/2  35 20,000    番号 =Sheet1!$B$2:$B$5 ブック
 
Sheet2          Sheet集計
  A  B C D E      A   B   C
1 条件1 05 10       1 日付 条件1 条件2
2 条件2 33 35       2 6/1 15,000   0
             3 6/2   0 23,000
             
集計!B2: =SUMPRODUCT((日付=$A2)*(番号=INDIRECT(B$1))*金額)

回答
投稿日時: 22/06/11 20:56:33
投稿者: Mike

投稿日時: 22/06/11 20:46:51 の小生のコメントの一部訂正です。m(_._)m
[名前の管理]テーブルにおいて、
×→ 条件1 =Sheet1!$B$2:$E$5 ブック
〇→ 条件1 =Sheet1!$B$2:$E$2 ブック

投稿日時: 22/06/14 17:56:28
投稿者: TI

んなっと様 Mike様
アドバイスありがとうございます。
 
まずは、んなっと様の最終案で進めさせていただきます。
希望通りの結果が取得でき、今後のメンテンナンスも
実務担当者に移すことができ、非常に助かりました。
 
まだ、んなっと様がご提示いただいた内容を完全に
自分ものにできているわけではないので、もうしばらく
理解を深めていきたいと思います。
 
今後とも、宜しくお願い申し上げます。