ICT業務改善

Excel集計テクニック:1行毎に集計する

 

Excelは表形式でデータを管理できるので、様々な数値管理に使われます。売上明細、実験結果、アンケート結果など、用途は様々です。そして、それらのデータを活用するには決まって「集計」が必要になってきます。

そこで、このシリーズでは、Excelの集計作業がはかどるテクニックをご紹介していきます。

今回は、地味に扱いづらい「1行毎の集計」方法です。

 

例えばこんな売上表があったとします。

 

 

表下の合計欄には、それぞれ金額の合計と、個数の合計を表示したいわけです。合計といえばSUM関数を使いたいところですが、そう簡単にはいきません。単純に「=SUM(C4:C11)」としてしまうと、金額の¥300,000も、個数の20も一緒くたに足し算されてしまいます。金額だけ、個数だけを合計するには工夫がいります。

 

先に答えを言ってしまいます。金額合計のセル入力するのは下記の関数です。

=SUMPRODUCT((MOD(ROW(C4:C11),2)=0)*C4:C11)

するとこのように、金額だけが合計された結果が表示されました。

 

 

この関数は何をしているかというと、「C4:C11の範囲内で、行番号が偶数のセルの合計」を算出しています。金額は偶数行に入力されていますから、これで合計が出せるわけです。個数合計の場合、個数は奇数行に入力されているので、関数は

=SUMPRODUCT((MOD(ROW(C4:C11),2)=1)*C4:C11)

となり、「C4:C11の範囲内で、行番号が奇数のセルの合計」を算出します。

 

この方法は便利なのですが、関数構造がややこしいので、1つずつ説明して最後に全体構造を解説します。

 

ここで使われている関数は、

  1. ROW
  2. MOD
  3. SUMPURODUCT

の3つです。

 

1. ROW関数

書式は

ROW([範囲])

です。指定された範囲の行番号を返す関数です。

「ROW(A1)」なら、A1は1行目のセルなので「1」を返します。

「ROW()」のように範囲を省略した場合は、「関数が入力されているセルの行番号」(A3セルに入力されていれば3)を返します。

今回の「ROW(C4:C11)」のように範囲にセル範囲が指定されている場合は、その範囲の行番号を配列として返します。この場合は「{4, 5, 6, 7, 8, 9, 10, 11}」を返します。

但し、セルに「=ROW(C4:C11)」とだけ入力した場合は、配列の先頭の値である「4」だけが表示されます。

 

2. MOD関数

書式は

MOD(数値, 除数)

です。数値÷除数の余りの数を返す関数です。「MOD(5,2)」なら、5÷2=2…1なので、「1」を返します。

今回の例では「MOD(ROW(C4:C11),2)」となっており、

{4, 5, 6, 7, 8, 9, 10, 11}をそれぞれ2で割った余りの配列、つまり「{0, 1, 0, 1, 0, 1, 0, 1}」を返します。

 

 

3.SUMPRODUCT関数

書式は

SUMPRODUCT(配列1, [配列2], [配列3], … )

で、配列2以降は省略可能です。指定した配列の要素間の積を計算し、更にそれらを合計した値を返します。

少し分かりにくい関数なので、簡単な例を挙げます。

このような表があるとします。

 

 

A列*B列の合計を計算したい場合、下図のようにA*Bの列を追加し、その下に合計欄を作ればできます。

 

 

しかし、SUMPRODUCT関数を使えば、A*Bの列を追加せずに合計を求めることが出来るのです。合計欄に、

=SUMPRODUCT(A2:A6,B2:B6)

と入力すると、A列*B列の合計が表示されます。

 

 

この関数は、まず配列の要素の積を求めます。

{1,2,3,4,5}*{6,7,8,9,10}

= {1*6, 2*7, 3*8, 4*9, 5*10}

そして、これらの要素を合計するので、

1*6 + 2*7 + 3*8 + 4*9 + 5*10

= 6 + 14 + 24 + 36 + 50

= 130

となり、A列*B列の合計を算出できるのです。

引数の配列が1つの場合は、配列の要素の合計を返します。

 

 

全体構造の解説

さて、各関数の役割が分かったところで、今回の関数

=SUMPRODUCT((MOD(ROW(C4:C11),2)=0)*C4:C11)

の構造を紐解いてみましょう。ここから更にややこしくなります。「なぜこの関数が偶数行の合計を出せるのか」を説明します。

 

「ROW(C4:C11)」は、「各セルの行番号」を示す配列です。

(配列の各要素は{4, 5, 6, 7, 8, 9, 10, 11})

 

「MOD(ROW(C4:C11),2)」は、「各セルの行番号を2で割った時の余りはいくらか」

を示す配列です。

(配列の各要素は{0, 1, 0, 1, 0, 1, 0, 1})

 

「(MOD(ROW(C4:C11),2)=0)」は、「各セルの行番号を2で割った時の余り = 0か否か」を示す配列。つまり、「各セルの行番号が偶数か否か」を示す配列です。

(配列の各要素は{TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE})

 

「(MOD(ROW(C4:C11),2)=0)*C4:C11」は、「各セルの行番号が偶数か否か」を示す配列と、「C4:C11の各値」を示す配列の積です。具体的には下記の様になります。

{TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE}*{300000, 20, 250000, 18, 190000, 18, 170000, 15}

Excelの計算では、TRUE=1、FALSE=0として扱われるので、

={1, 0, 1, 0, 1, 0, 1, 0}*{300000, 20, 250000, 18, 190000, 18, 170000, 15}

={1*300000, 0*20, 1*250000, 0*18, 1*190000, 0*18, 1*170000, 0*15}

={300000, 0, 250000, 0, 190000, 0, 170000, 0}

となります。

要するに、C4:C11の配列を、行番号が偶数の場合はそのまま、奇数の場合は0に変えた配列です。

 

そしてようやく「SUMPRODUCT((MOD(ROW(C4:C11),2)=0)*C4:C11)」です。SUMPRODUCT関数は、配列の要素間の積を計算し、更にそれらの計算結果を合計した値を返します。配列が1つの場合は、その配列の要素の合計を返すので、

SUMPRODUCT((MOD(ROW(C4:C11),2)=0)*C4:C11)

= SUMPRODUCT({300000, 0, 250000, 0, 190000, 0, 170000, 0})

= 300000 + 0 + 250000 + 0 + 190000 + 0 + 170000 + 0

= 910000

となり、偶数行のセルだけを合計することが出来ました。

 

奇数行のセルだけを合計したい場合は、「(MOD(ROW(C4:C11),2)=0)」を、「(MOD(ROW(C4:C11),2)=1)」に変えることで、「各セルの行番号を2で割った時の余り = 1か否か」つまり、「各セルの行番号が奇数かどうか」の判定に変えることが出来ます。

 

 

この関数は配列を扱うのでちょっと理解しにくいですが、関数をそのままコピーして、セルの範囲を変えるだけで使えますので、是非活用してみてください。

 

※画像は全て、Excel2013のもので統一しています。

 

Excelは単純な表計算だけでなく、複雑な条件の集計作業にも対応できるあらゆる機能が備わっています。これらの機能を活用することで、日々の業務が飛躍的に効率化できます。関数を使ったら、面倒だった集計作業が一瞬で終わるようになった。それは立派なイノベーションです。Excel機能を活用して、業務にイノベーションを起こしましょう!

毎日の業務に追われる中、自分たちでExcelを学習して改善点を考えるのは難しい。そんな方は、アーティに是非一度ご相談ください。無料相談でお困りごとをお聞かせ頂ければ、Excel機能を始めとするITの知見で最適な改善方法をご提案します。「効率悪いな……」と思いながらやっている作業がある方は、是非お気軽にお問合せ下さいね。