ヘルプの森~Excel・Access・Office全般ヘルプデスクサイト

No.010

アイコン 奇数行の合計、偶数行の合計、n行おきの合計を求める 《配列数式/IF・SUM・MOD》

Excel2003/2007/2010/2013

まれに、1件が複数行で1セットになっているようなデータを見かけることがあります。
数値データの合計を出すときに、それが1行おきであったり数行おきであったりする場合はどうすれば効率いいでしょうか。
もちろんSUM関数でそれらセルを指定していけばいいのですが、かなりの行があったら重労働です。

今回はセルA1~A20までの数値データに対し、奇数行の合計、偶数行の合計、n行おき(5行おき)の合計を求めてみます。

先に注意点を申し上げておくと、配列数式で求めますので、最後は「Ctrl」+「Shift」+「Enter」で確定する必要があります。
そして確定後の数式バーには、配列数式のしるしとして、式の前後に中カッコ{}が表示されます。

では順番にいきますが、今回登場する関数は、SUM関数(関数の分類: 数学/三角)、IF関数(関数の分類: 論理)、MOD関数(関数の分類:数学/三角)、ROW関数(関数の分類: 検索/行列)です。
MOD関数は除算のあまりを返し、ROW関数はセルの行番号を返します。

<奇数行の合計>

そのセルの行番号を2で割り余りが1であれば(奇数行)そのセルの値を取り、2で割り切れた場合(偶数行)は空白にして集計から外して、合計します。

  {=SUM(IF(MOD(ROW(A1:A20),2)=1,A1:A20,""))}

奇数行の合計

上図の下、[関数の引数]ダイアログ ボックスは、IF関数のものです。
IF関数の条件で、MOD関数の答えが1(奇数行)であれば、そのままそのセルの値を返し、そうでなければ空白を返すようにしています。
赤い枠のところがIF関数でまとめて返している値、それらをSUM関数します。

式は通常どおりに作成し、最後の確定時に「Ctrl」+「Shift」+「Enter」を押します。

<偶数行の合計>

奇数の場合と違うのは、MOD関数の答えが0である(偶数行)ということだけで、後は同じです。

  {=SUM(IF(MOD(ROW(A1:A20),2)=0,A1:A20,""))}

奇数行の合計

<n行おきの合計>

今回は5行おきの合計を求めてみたいと思います。

奇数偶数行と違うのは、5で割り切れるとかでなく、5行のうちの何行目を合計するのかにより立てる式も違ってきます。

初めて合計するセルの行番号より5行の何行目か(行番号を5で割ったあまり)を求め、そこから同じあまりである5行おきの合計を求めます。

今回はセルA3が先頭の合計する値で5行おき、5行セットのうち3行目のセルを合計しようと思います。

  {=SUM(IF(MOD(ROW(A1:A20),5)=MOD(ROW(A3),5),A1:A20,""))}

5行おきの合計

IF関数の条件にMOD関数が2回出てきています。
左辺のMODはそのセルの行番号を5で割ったときのあまり、右辺の方は先頭セルA3の行番号を5で割ったときのあまり、両者のあまりが一致するならば、という条件です。
一致する場合はそのセルの値、一致しなければ空白を返させ、そのIF関数の返した値(上図の赤い枠内)を合計しています。

つまり、n行おきの合計を求める場合は、次の式になります。

  {=SUM(IF(MOD(ROW(セル範囲),n)=MOD(ROW(先頭セル),n)
    ,セル範囲,""))}

ヘルプmemo

<MOD関数 (数学/三角関数)>

数値を除数で割ったときの剰余を返します。
 数式: =MOD(数値, 除数)
 引数:
  数値... 除算の分子となる数値を指定
  除数... 除算の分母となる数値を指定

関連ヘルプ

フィルターで抽出されたデータの件数、合計をセルに表示したい 《SUBTOTAL》
検索条件に一致するデータの合計を求める 《SUMIF》
時刻を5分単位で切り上げまたは切り捨てて表示するには 《CEILING・FLOOR・ROUNDUP》
OR条件に当てはまるデータの合計を求める 《SUMIF・SUMIFS・SUMPRODUCT・DSUM》
10進数の数値を2進数と16進数に変換するには 《DEC2BIN・DEC2HEX》
ページのトップへ戻る