2014年08月23日

1行おきの値を合計してみた(関数の応用)/Excel/関数

Excelの関数を使って1行おきの値を合計してみます。
ROW関数”、“ISEVEN関数”、“SUMIF関数”を使用します。
20140822_00一行おきに合計する.jpg

例えばこんな受注データがあります。日毎の“数量”と“金額”が縦に配置されています。
20140822_01.JPG

Excelでデータ分析をしようとする賢い人は、分析用の元データをこんな形に作ってはいけません。
ですが、何も知らない人の中には見易いという理由だけで、こんなデータを作ってしまう人がいるんです。。。
後で、別の人がこのデータの分析をしようとした時に、、、
「“数量”の合計や平均どうやって集計すんの?」
「日付で並べ替えできないじゃん!!?」
と、キレ気味になっちゃいます。
んで、そんな時にExcelの関数を使って1行おきの値を合計してみます。

やりかたは、“数量”と“金額”の入力されている行番号が奇数か偶数かを求めて、奇数行だけの合計、偶数行だけの合計を求めます。

まず、行番号を求めるために“ROW関数”を使用します。
20140822_02.JPG

次に求めた行番号か奇数か偶数かを求めるのは“ISEVEN関数”を使います。
“ISEVEN関数は”偶数だったら“TRUE”、奇数だったら“FALSE”を表示する関数ですが、
“ISODD関数”で奇数だったら“TRUE”、偶数だったら“FALSE”を表示させてもよいですし、“MOD関数”で行番号を“2”で割った時の余り(“0”か“1”)を求めてもいいでしょう。。
20140822_03.JPG

と、3行目に入力されている“数量”は“FALSE”と表示されます。
20140822_04.JPG

これを、コピーして貼り付けるとこうなって、“数量”は“FALSE”、“金額”は“TRUE”に表示されました。
20140822_05.JPG

あとは、“FALSE”の数値の合計、“TRUE”の数値の合計を“SUMIF関数”で求めれば、“数量”と“金額”の合計が求められます。
“SUMIF関数”の引数は、条件検索をする範囲(ここでは、“偶数かどうか”のF列の範囲)、検索条件(“FALSE”か“TRUE”)、合計を集計する範囲(“値”のD列の範囲)を指定します。
20140822_06.JPG

と、このように、1行おきに入力されている“数量”と“金額”の合計が集計できました。
20140822_07.JPG

“AVERAGEIF関数”を使うと、平均値も集計することができます。
20140822_08.JPG

こんな感じになります。
20140822_09.JPG

それから、“ROW関数”と“ISEVEN関数”をネスト(入れ子)すれば、シンプルになります。
20140822_10.JPG

ちなみに、こんな体裁の集計表はピボットテーブルを使えば簡単にできちゃいますから、見易いからと言ってこんな表を時間をかけて作っては絶対にイケマセンっ!

では、次に同じ関数の活用で一行おきのセルに書式を設定してみましょう。


関連ページ
Excelのデータの作り方
行番号を求める / ROW
奇数か偶数かを求める / ISODD、ISEVEN
条件に一致したセルの合計を求める / SUMIF
割り算の余り(剰余)を求める / MOD
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)
posted by haku1569 at 13:39| Excel関数応用 | このブログの読者になる | 更新情報をチェックする