Excel(エクセル)関数の技：合計する関数(2/2)

• セルの値を合計するワークシート関数をまとめて説明しています

セル位置で合計する(3-D参照・串刺し計算)　　topへ

SUM関数：=SUM(数値1,数値2,・・・)

• シート見出しで連続するシート間で計算します。
  • =SUM(Sheet1:Sheet3!F4)　シート名が「:」でつないであります。
    • Sheet1,Sheet2,Shee3のF4セルが合計される。

    • Sheet1〜Sheet3はシート見出しで連続しています。
      Sheet1	Sheet2
      Sheet3

表示されているセルの値を合計する　　topへ

SUBTOTAL関数：=SUBTOTAL(集計方法,範囲1,範囲2,...)

• SUBTOTAL関数の集計方法(Excel2002の場合)
  Excel2003では非表示行を計算しない場合が追加された
  

  集計方法	関数	Excel2003以降 で追加された集計方法
  1	AVERAGE	101
  2	COUNT	102
  3	COUNTA	103
  4	MAX	104
  5	MIN	105
  6	PRODUCT	106
  7	STDEV	107
  8	STDEVP	108
  9	SUM	109
  10	VAR	110
  11	VARP	111

• オートフィルタで抽出されているデータの値(表示されている値)を合計する

  • すべて表示されている状態
    商品名で「みかん」を抽出した場合	商品名で「りんご」or「バナナ」で抽出した場合

あるセルを基準にセル位置で合計する　　topへ

OFFSET関数：=OFFSET(基準,行数,列数,高さ,幅)

• あるセルを基準に指定した行数・列数分シフトした範囲をOFFSET関数で求め、SUM関数で合計する。
  
  • B3セルを基準に行数1，列数は行毎に1,2・・と変化させる(ROW関数を組み合わせています)。
    セル範囲の高さは4行分とする。
  • =SUM(OFFSET($B$3,1,ROW(A1),4))
    

数行毎に合計する　　topへ

• 2行おきに合計する例です。
  • SUMIF関数を使う例
    • 行番号は=ROW()で求められるので、=MOD(ROW(),3)とすると、0,1,2,0,1・・・と区分できる。
    • =SUMIF($C$2:$C$10,2,$B$2:$B$10)
      =SUMIF($C$2:$C$10,0,$B$2:$B$10)
      =SUMIF($C$2:$C$10,1,$B$2:$B$10)
      として求める事ができる。
      
  • SUMPRODUCT関数を使う例
    • 同様の考え方でSUMPRODUCT関数を使うと作業列を使用しないで済む
      =SUMPRODUCT((MOD(ROW($A$2:$A$10),3)=2)*($B$2:$B$10))
      =SUMPRODUCT((MOD(ROW($A$2:$A$10),3)=0)*($B$2:$B$10))
      =SUMPRODUCT((MOD(ROW($A$2:$A$10),3)=1)*($B$2:$B$10))
      とします。
      これを一つの式で表すと、
      =SUMPRODUCT((MOD(ROW($A$2:$A$10),3)=MOD(ROW(A2),3))*($B$2:$B$10))
      とできる。
      

　PageViewCounter
　
　Since2006/2/27