本ページはプロモーションが含まれています
EXCELを使用していると
「行と列をとばして集計したい！」
って事ありますよね。
関数を使ったやり方を紹介します。
プロローグ
メールで送られてきたEXCELファイル。
=C3+F3+I3+L3+・・・・・・・（気が遠くなるほどえんえんと）
「一生懸命に計算式入れたな」
と、頑張りは伝わりました(笑)
関数を使って
「行と列をとばして集計する方法」
を紹介します。
等間隔に間の空いたデータを列ごとに集計する
サンプルはこちら。
テーマは「前期/後期/合計の科目ごとに集計」です。
これくらいのデータだったら
地道にクリックしてもすぐ終わりますが、
あくまでサンプルです。
ご容赦ください。
O5（前期の国語)に入る関数はこちら。
=SUMPRODUCT((MOD(COLUMN($C5:$K5),3)=0)*1,$C5:$K5)関数が３種類でてきました。
| 関数 | できる事 |
| SUMPRODUCT | 条件に合うデータを集計する |
| MOD | 割り算した余りを返す |
| COLUMN | 列番号を返す |
わかりやすいところから見て行きます。
COLUMN(範囲)
列番号を返すCOLUMN関数。
$C5:$K5
と範囲で指定しています。
範囲内のすべてが対象になります。
「セルをひとつずつ順番に処理していく」
と考えればわかりやすいです。
最初のC5の計算のときにはA,B,Cと
３番目の列になるので「3」を返します。
MOD(割られる数値,割る数値）
割り算をした余りを返す、MOD関数。
上と同じくC5が計算対象のとき、
MOD(COLUMN($C5:$K5),3
先述のとおり、赤線の部分は3を返します。
3 ÷ 3
で余りはゼロですね。
MOD関数から得られる値はゼロです。
次のD5が計算対象になると
MOD(COLUMN($C5:$K5),3
赤線の部分は4を返します。
4 ÷ 3
で余りは１。
MOD関数からは1が返されます。
SUMPRODUCT((配列1＝条件1)＊(条件2),配列2)
使い方が多いSUMPRODUCT関数。
今回の使い方は
「条件を満たす合計を求める」
=SUMPRODUCT((MOD(COLUMN($C5:$K5),3)=0)*1,$C5:$K5)
赤線の部分が条件の１つ目。
列番号を3で割って、結果が=0なら「真(TRUE)」。
青線の部分が条件の２つ目。
*は論理式でANDの意味。
1は「真(TRUE)」の意味。
赤の部分と青の部分両方が「真(TRUE)」なら
条件成立で計算対象になります。
黄線で指定した範囲内で
対象セル（３で割って余りが0の列番号のセル）の合計を返します。
他のセルの入力値は
そのとなり、P5の計算式は
=SUMPRODUCT((MOD(COLUMN($C5:$K5),3)=1)*1,$C5:$K5)Q5には
=SUMPRODUCT((MOD(COLUMN($C5:$K5),3)=2)*1,$C5:$K5)それぞれ「余り」の値が1,2と変化しただけ。
列番号を３で割った余りが1の合計、２の合計を算出しています。
下の行についてはO5～Q5を選択。
そのままコピーすればOKですね。
（$マーク、絶対参照を間違えないように）
結果はこちらです。
等間隔に間の空いたデータを行ごとに集計する
今度は行ごとに集計します。
サンプルはこちら。
C24に入力する式はこちらです。
=SUMPRODUCT((MOD(ROW(C$5:C$19),5)=0)*1,C$5:C$19)ほとんど一緒。
違うのは
✔ ROW関数
✔ MOD関数の割る数値が5になった
✔ セルの対象範囲が変わった
ってことです。
COLUMN関数が列番号を返す
に対して
ROW関数は行番号を返す
って事だけです。
割る数値が5になったのは
サンプルシートの場合、
列方向には３セルずつの集計でした。
行方向には４教科＋合計で
５セルずつ間を空けての集計になるから。
下方向へのセルは
条件の「=0」の部分が1～4に変化するだけですね。
結果はこちらです。
行と列、両方の間を空けて集計する
さぁ、最後です。
行と列、両方の間を空けて集計する方法。
サンプルシートはこちら。
右下の部分を求めます。
O24(前期、国語）への計算式です。
=SUMPRODUCT((MOD(COLUMN($C$5:$K$19),3)=0)*(MOD(ROW($C$5:$K$19),5)=0),$C$5:$K$19)列ごとの集計、行ごとの集計の合体です。
長い式が入ってますが、
やってる事は難しくありません。
さきほどまで「*1」となってた部分が変わっただけ。
他のセルへの入力も
列方向、行方向ともに
=0が、0～2と0～4と変化します。
結果はこちら。
まとめ
大量のデータを処理するときに使って下さい。
作業効率のアップにつながればうれしいです。
ほかにもいろいろ書いてます
↓ 下記からご覧ください
サイトマップ
ブックマークして頂けると嬉しいです
よろしくお願いいたします
コメント