Excel (一般機能)

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

 
(Windows 11全般 : Excel 2019)
関数SUMIFとOFFSET
投稿日時: 23/10/23 22:56:05
投稿者: wa-nn

関数SUMIFとOFFSETについて
下記表のAの合計を求めたい。
 
1をA1セルとし、
 SUMIF(B1:B9, "A",OFFSET(C1,,,9))とした場合と
OFFSETの引数の高さを、省略または9以外の数にした時の
数式結果が同じ300になりました。
 
SUMIFとOFFSETを組み合わせる場合は、
高さを省略できるということでしょうか?
 
ネットを検索しても答えが見つからなかったので
どなたか教えて頂けると幸いです。
宜しくお願い致します。
 
1 A 100
2 B 200
3 C 300
4 A 100
5 B 200
6 C 300
7 A 100
8 B 200
9 C 300

回答
投稿日時: 23/10/24 07:00:29
投稿者: んなっと

OFFSET(C1,,,)はC1を返して、OFFSET(C1,,,2)はC1:C2を返すだけ。
OFFSETは無関係で、SUMIFの特徴かもしれません。
 
  B  C  D
1 P  1 11 =SUMIF(B1:B6,"P",C1)
2 Q  2  1 =SUMIF(B1,"P",C1:C6)
3 R  3  2 =SUMPRODUCT((B1:B6="P")*C1)
4 P 10  
5 Q 20  
6 R 30  
 
●SUMIF(条件範囲,条件,合計範囲)
 
D1=SUMIF(B1:B6,"P",C1)
合計範囲(C列)の行数が少ない→自動的に合計範囲を広げて条件範囲に揃えてくれる
 
  B  C      B  C
1 P  1   →  P  1
2 Q        Q  2
3 R        R  3
4 P        P 10
5 Q        Q 20
6 R        R 30
 
D2=SUMIF(B1,"P",C1:C6)
条件範囲(B列)の行数が少ない→自動的に合計範囲を狭めて条件範囲に揃えてしまう
 
  B  C      B  C
1 P  1   →  P  1
2   2        
3   3        
4   10        
5   20        
6   30        
 
●一般的な配列
 
D3=SUMPRODUCT((B1:B6="P")*C1)
どちらか一方が1行(1列)だけ→他方の行数(列数)に合わせて要素を埋める
 
  B  C      B  C
1 P  1   →   P  1
2 Q        Q  1
3 R        R  1
4 P        P  1
5 Q        Q  1
6 R        R  1

投稿日時: 23/10/24 09:30:43
投稿者: wa-nn

んなっと さんの引用:
OFFSET(C1,,,)はC1を返して、OFFSET(C1,,,2)はC1:C2を返すだけ。
OFFSETは無関係で、SUMIFの特徴かもしれません。
 
  B  C  D
1 P  1 11 =SUMIF(B1:B6,"P",C1)
2 Q  2  1 =SUMIF(B1,"P",C1:C6)
3 R  3  2 =SUMPRODUCT((B1:B6="P")*C1)
4 P 10  
5 Q 20  
6 R 30  
 
●SUMIF(条件範囲,条件,合計範囲)
 
D1=SUMIF(B1:B6,"P",C1)
合計範囲(C列)の行数が少ない→自動的に合計範囲を広げて条件範囲に揃えてくれる
 
  B  C      B  C
1 P  1   →  P  1
2 Q        Q  2
3 R        R  3
4 P        P 10
5 Q        Q 20
6 R        R 30
 
D2=SUMIF(B1,"P",C1:C6)
条件範囲(B列)の行数が少ない→自動的に合計範囲を狭めて条件範囲に揃えてしまう
 
  B  C      B  C
1 P  1   →  P  1
2   2        
3   3        
4   10        
5   20        
6   30        
 
●一般的な配列
 
D3=SUMPRODUCT((B1:B6="P")*C1)
どちらか一方が1行(1列)だけ→他方の行数(列数)に合わせて要素を埋める
 
  B  C      B  C
1 P  1   →   P  1
2 Q        Q  1
3 R        R  1
4 P        P  1
5 Q        Q  1
6 R        R  1

 
んなっとさん
御回答ありがとうございます。
SUMIFは検索範囲に合わせて合計範囲も揃えてくれるのですね!!!
わかりやすく説明して頂きありがとうございましたm(_ _)m