らくらくIT活用術
PC活用講座 - Excel
「Excelの便利機能活用術」
複数店舗の小計と合計をサッと求めるテクニックとは
ITライター 立山秀利
Excelの表では、データと複数の小計が同じ列にある表で合計を求めたいケースがしばしばある。今回はそのようなケースで合計を効率的に求める方法を紹介する。
SUM関数だと合計をうまく求められない
例えば図1のように3店舗のイベント集客数の表があるとする。新橋店のデータなら1～3月のイベント集客数がC4～C6に入力され、3カ月の小計をC7セルで求める。品川店と蒲田店も同様の構成として、3店舗分が縦に並ぶ表である。そして、3店舗分の小計を足し合わせた合計を同じ列のC17セルに求めたいとする。
それぞれの小計はSUM関数で求めるのが一般的だ。C17セルに合計を求める際、「=SUM(C4:C15)」とC4～C15セルを合計するSUM関数を入力してしまうと、図1のように正しく求められない。同じ列に各月のデータと小計の両方があり、それらすべての合計を求めてしまっているからだ。
図1
正しく合計を求めるには、SUM関数ではなく、小計のセル（C6、C11、C15）だけを足すように数式「=C6+C11+C15」をC17セルに入力すればよい。とはいえ、このような数式を入力するのは手間がかかるうえに、セル番地の指定をミスしやすいもの。しかも、店舗数が増えるに従い、その問題はますます大きくなっていく。
さまざまな集計が簡単に行えるSUBTOTAL関数
そこで利用したいのがSUBTOTAL関数だ。合計をはじめ、さまざまな集計が行える関数である。SUBTOTAL関数で合計を求める書式は次の通りだ。第1引数に9を指定すると、合計が求められる。あとは第2引数に合計したいデータのセル範囲を指定すればよい。
---------------------------------
SUBTOTAL(集計方法,セル範囲)
---------------------------------
※集計方法の主な引数一覧は以下
| 引数 | 1 | 2 | 3 | 4 | 5 | 6 | 9 |
|---|---|---|---|---|---|---|---|
| 集計方法 | 平均 | 数値の個数 | データの個数 | 最大値 | 最小値 | 積 | 合計 |
他の集計方法は例えば平均なら、第1引数に1を指定し、第2引数に平均を求めたいデータのセル範囲を指定する。
先ほどの例の場合、まずは小計をSUBTOTAL関数で求めるよう変更する。
▼C7セル
---------------------------
=SUBTOTAL(9,C4:C6)
---------------------------
▼C11セル
---------------------------
=SUBTOTAL(9,C8:C10)
---------------------------
▼C15セル
---------------------------
=SUBTOTAL(9,C12:C14)
---------------------------
そして、C17セルの合計もSUBTOTAL関数で求めるようにする。その際、合計範囲となる第2引数には、C4～C15セルを指定すればよい。
▼C17セル
---------------------------
=SUBTOTAL(9,C4:C15)
---------------------------
図2
合計範囲は「C4:C15」と、各店舗のデータも小計も含めC列をまとめて指定している。SUM関数で同様に合計範囲を指定した「=SUM(C4:C15)」では、合計は図1のように正しく求められなかったが、SUBTOTAL関数では図2のように正しく求められた。これはSUBTOTAL関数が小計のセルを自動で判別して除外し、合計を求めてくれるからである。
SUBTOTAL関数を使えば、「=C6+C11+C15」のように小計のセルだけを足す数式をいちいち記述せずに済むので手間が少ない。その上、合計範囲はC列をまとめて指定できるので、もし店舗の数が増えても、合計範囲を広げるだけで素早く簡単に対応できる。各店舗で小計を集計したい月が増えても、行の挿入（行番号を右クリック→［挿入］）によってその月のセルを増やせば、小計対象のセル範囲をいちいち書き換えなくとも自動で対応できる。
なお、合計にSUBTOTAL関数を用いても、小計をSUM関数で求めていると、合計を正しく求められないので注意してほしい。小計も合計もSUBTOTAL関数を使う必要がある。
このように同じ列に小計と合計がある表の場合、SUBTOTAL関数を用いることで、合計をより簡単に求められ、変化にも素早く容易に対応可能となる。
その他の【Excel】を見る
立山秀利（たてやま・ひでとし）
カーナビのソフトウェア開発、Webプロデュース業務を経て、現在は、システムやネットワーク、Microsoft Officeを中心に執筆中。 主な著書に『Excel VBAのプログラミングのツボとコツがゼッタイにわかる本』などがある。
（監修：日経BPコンサルティング）