従来データの集計をするとなると一番手っ取り早いのは、ピボットテーブルでクロス集計することです。
項目ごとに分類して集計値を求めるというのがとても簡単にできるからです。
しかしピボットテーブルを毎月の業務の結果を報告するレポートに入れようとすると、売上データを追加した時点では、すぐにピボットテーブルに反映するということではなく、データ更新をするという1操作が必要になってきます。
このような操作が一つ増えるだけで、例えば更新をし忘れると言うミスが入る余地が増えてしまいます。
実際にはそんなミスしないよとおっしゃる方もいらっしゃいますが、人が操作することなので本当に大丈夫かなと私は思ってしまいます。
元のデータを追加したと同時にクロス集計表が自動的に再計算されるといいと思うのですが、従来はそれができなくて、データ更新されたときを開始イベントとしたVBAを仕込んでいたわけですが、もしその VBA が何かの拍子に動かないということがあったら困るなとも思っていました。
そこで、今回Excelに追加された「スピル」機能、及び、スピル機能に関連する関数のうち重複データを削除する「UNIQUE関数」を使うことによって、データ追加と同時に、動的に自動計算されるピボットテーブルを作成できるようになりました。
これらは機能ではなく、計算式なので、自動計算されるからなのです。
このことにより、操作ミスをしてしまう心配、VBAなどのプログラムが万が一正常に動かなかった時の心配をする必要がなくなりました。
まず次のようなデータを元データとして考えてみます。
このデータをピボットテーブルでクロス集計すると次のような形になります。
これと同じものを作る仕組みを考えてみます。
まずUNIQUE関数で、元データの項目ごとの重複したものを削除して一つのデータだけを取り出します。
次のような計算式になるかと思います。
でも、ここでちょっと気に入らないのですが、元データが11行目以降に追加されると、このUNIQUE関数の参照範囲では、データの追加されないのです。
OFFSET関数などで入力されている範囲を参照することもできますが、手っ取り早く考えるには、元のデータをテーブル化してしまうと良いでしょう。
元データを［テーブルとして書式設定］してしまいます。
その上で先ほどと同じ範囲をUNIQUE関数で参照すると次のような計算式になります。
データ範囲をテーブル化すると、そのテーブルには「テーブルxx」という名前が自動で着きます。この名前は任意で変更することもできます。
「テーブル1[営業所]」は、テーブル1というデータ範囲の中の営業所項目のデータ全部という意味です。
テーブル機能にすることによってその下にデータが追加されたらそのデータもテーブルの一部になりますが、このようにしておけばデータが追加され、または、削除されてデータの総個数が変わったとしても見たい範囲を必ず追いかけ続けてくれます。
UNIQUE関数は、スピル機能を使う関数です。スピルはテーブル内で使うことはできません。しかし、テーブルを元にしてテーブルではないセルに計算することはできるので、有効に使っていきましょう。スピルは、テーブル内に設置できないがテーブルを参照することはできる、ここものすごく大事なポイントです。
UNIQUE関数を使って上記のような流れを作りましたが、作成したいものでは、営業所は横向きになるので、横向きにします。
横向きにするのは従来からあった、縦横を変換するTRANSPOSE関数を使います。
今まで、TRANSPOSE関数はOFFSET関数やINDIRECT関数と同じ範囲を表す関数で、本来、１セルだけの値を求める関数ではなかったので、配列数式のような特殊な計算方法か、INDEX関数で範囲の中の1つの値を取り出すという使い方しかできなかったのですが、スピルができるようになったことで、直接、関数の答えを複数のセル範囲に出すことができるようになったのでこのような使い方ができるのです。このような使い方ができるようになった関数は、OFFSET関数、INDIRECT関数、INDEX関数で行か列に0と指定すると範囲を答えとすることができましたがそれもこのような使い方が出来るようになっています。
スピルは新しい関数だけではなくて従来からあった関数の使い方も変えてくれています。
ここでちょっと計算式をセル上に作ってみたいのですが、TRANSPOSE関数で作ったF3からH3の範囲を参照する数式を作ってみたいと思います。
=を入力してF3からH3の範囲を選択すると「F3#」となります。
スピルで作った範囲は、このようにその範囲の左上のセルに#をつけることによってスピルされたすべてのセルを指定することができるのです。
FILTER関数では、抽出されるデータ個数は元データと抽出条件によって常に変わっていきます。
そのようにデータの個数が変わってしまっても、この#をつけた参照範囲を使うことで同じデータ個数の範囲を指定することができるようになります。
ここも大事なポイントです。
今回は集計値として合計を求めるのですが、縦の項目と横の項目という複数の条件に当てはまったデータだけの合計値を求めるので、その関数はExcel2016以降で正式採用となったSUMIFS関数を使います。
元データ範囲に対して、縦の項目名で一致するもの、横の項目名で一致するものの合計を求めています。
従来あった関数の参照でも、範囲を指定すればスピルができるので、一つの計算式だけ入れてコピーすることもなくなります。スピルを使えば計算式のコピーし忘れを防ぐことができるということですね。
ちなみに単一条件であればSUMIF関数が使えました。
今回のように従来の計算式や関数でも、その内容に範囲を指定することでスピルすることができるようになります。
例えばVLOOKUP関数の検索値である第一引数、RANK関数の値を示す第一引数のような、今まで一つのセルだけを参照する引数は範囲を指定することで計算結果をスピルすることができるということです。
SUM関数の引数や、VLOOKUP関数の一覧表範囲である第２引数のような、今まででも範囲を指定する引数は、従来通り範囲で指定してもスピルしません。
では元データの11行目に新たに営業所Dで製品がA04の10,000円の売上データを足してみます。
ただ11行目に入力しただけで、テーブル機能により11行目もテーブルの範囲の一部と自動的に認識されました。
それによって、UNIQUE関数で求める範囲にも11行目のデータが含まれたため、A04のデータ、Dのデータが再計算されて追加されました。
その範囲を元にしたSUMIFS関数で合計を求めた範囲も広がり、新たにDのA04の行列が再計算によって追加され、合計値を求めるようになっています。
一回だけ集計するような集計作業の場合はピボットテーブルの方が早いですし、そのようなハイパー電卓のようなExcelの使い方も知っておいてほしいと思います。会議の席で発言者の言った数字がどんな意味を持っているのか自分なりにとらえるために従来は電卓を持って行きましたが、Excelを使えば、必要なデータを入れたパソコンを持ち込んでおけば、今まで以上の速度でその場で何を言ってるかより深く理解できるようになるので、この使い方は是非オススメです。
今回紹介したようなスピルを使った集計表の作成は、データの定点観測に使います。
このような仕組みがあれば、毎月売上が確定した時に出てくる資料をお待たずとも、その売り上げが成立した時点でデータさえ入力してしまえば最新のデータを見ることができるのです。
このご時世、様々な変化があります。企業にとってもいいことも悪いこともものすごいスピードで起きるので、このような仕組みを作って、現時点の最新の状況はどうなっているかすぐにチェックできるような状況にしておくことができるようになったので、是非スピルを使った集計表を作ってみると良いと思います。
自動的に再計算されるのでミスなく最新の情報になっているというのが作れるようになったというのが、私が一番スピルに魅力を感じている部分です。
コメント