複数のシートの数字を、関数だけで1枚にまとめる

毎週月曜、店舗ごとのシートを開いて、コピーして貼り付けて。それを店舗の数だけくり返していませんか。

ご相談で一番多いのが、この作業です。シートは部署ごと・店舗ごと・月ごとに分かれていて、それを1枚にまとめるところだけが手作業で残っている。

■ 手でやると、何が起きるか

・見落とし。シートが5枚あると、1枚飛ばしても気づきません

・貼り間違い。行がずれても、合計そのものは出てしまいます

・元の数字が直ると、また最初から

とくに3つ目がつらいところです。集計し終わったあとに「先週の数字が違っていた」と言われると、もう一度おなじ作業をすることになります。

■ 関数だけで、ここまでできます

プログラムを書かなくても、スプレッドシートの関数だけでかなりのところまでいけます。

まず、シートを縦に積む書き方があります。

={渋谷店!A2:C;新宿店!A2:C;池袋店!A2:C}

波カッコで囲んで、セミコロンで区切ると下に積み上がります。カンマで区切ると横に並びます。

これに集計をかぶせると、1つの数式で終わります。

=QUERY({渋谷店!A2:C;新宿店!A2:C;池袋店!A2:C},"select Col1, sum(Col3) where Col1 is not null group by Col1 order by sum(Col3) desc label Col1 '商品', sum(Col3) '合計数量'",0)

長く見えますが、やっていることは3つだけです。

・波カッコで囲んだ部分 … 3つのシートを縦に積む

・group by Col1 … 1列目(商品)ごとにまとめる

・sum(Col3) … 3列目(数量)を合計する

実際に動かすと、こうなります。

商品　　合計数量

みかん　　106

りんご　　82

バナナ　　62

ぶどう　　19

元のシートの数字を直すと、この表も自動で直ります。やり直しがなくなるのが、いちばん大きいところです。

■ ここで詰まります

正直に書いておきます。この方法には限界があります。

1. 空白行が混ざる

A2:C のように終わりを決めずに書くと、シートの下のほうの空白行まで拾います。上の数式では where Col1 is not null で消していますが、これを知らないと空白だらけの表ができあがります。

2. 金額が出せない

単価×数量のように、列どうしを掛けた合計は QUERY だけでは出せません。元のシートに金額の列を足すか、別のやり方が要ります。

3. シートが増えるたびに数式を直す

店舗が増えたら、数式の中に手で書き足すことになります。シート名を変えただけでも動かなくなります。

4. 数式が長い

半年後の自分が読んで直せるかどうか。ここは正直、あやしいところです。

■ ここから先が面倒なら

シートが増えても直さなくていい形、金額まで出す形、ボタン1つで動く形にするには、簡単なプログラム(Google Apps Script)を足すことになります。

同じことを、こちらでも作っています。

「複数のシートのデータを1枚に集計します」(3,000円)

打ち合わせは不要です。シートを共有していただければ、こちらで動かして結果を確かめてからお渡ししています。

とはいえ、上の数式で足りるなら、それがいちばん早いです。まずは試してみてください。