非常に簡単で正確なグラフ更新術

当ウェブサイトでは、「知っていると地味に役立つ実務的なテクニック」、「ちょっとした工夫で誰にでも簡単にできる技」というものを大切にしている。具体的には、マクロだ、専門的なソフトウェアだ、システムだ、といった、難しくてコストもかかる話ではなく、もっと簡単に、実務的で誰もが納得できる工夫、テクニックを重視している、というわけだ。「データを最新の日付で並べ替える」という技などは、その典型例だろう。

日付順データは古いものから新しいものに流れるのが通常

エクセルで仕事をしていると、日付ごとにデータが格納されていて、これを逆順に並べ替えたいと思うことはないだろうか。

たとえば、こんなデータがあったとする(図表1。便宜上、シート名は「Data」だったと仮定する)。

図表1 東京都における新型コロナウィルスの新規陽性者報告数(シート名「Data」)

(【出所】東京都『新型コロナウイルス陽性患者発表詳細』より当ウェブサイト作成)

これは、新型コロナウィルスに関するPCR検査の結果、陽性だった人の数であり、CSVファイルで入手できる元データの日付欄をCOUNTIF関数(『データを数える「COUNTIF」関数の基本を覚える』等参照)などでカウントすれば簡単に求めることができる。

ただ、このデータ自体は古い順に並んでいる。

本稿執筆時点において、新型コロナウィルス感染症に関するデータは日々更新されているからであり、また、いちいち「COUNTIF」で毎日の新規陽性者数を求めるのは現実的ではないからだ。

実際には、東京都が公表するファイルには数十万件のデータが含まれており、これを最も古いデータから計算すれば、自身のPCのスペックにもよるが、下手をすれば数十秒から数分の時間を要してしまう。

エクセル自体が途中でハングアップしてしまい、泣く泣く「強制終了」させなければならないかもしれない。

従って、この手の「集計を伴うファイル」は、過去分については計算結果を「値貼り付け」し、集計は最新データのみに留めるのが鉄則であり、必然的に、データは古いものから順に並べざるを得ないのだ。

グラフを作ると大変に面倒なことに…!

しかし、そうなってくると、もうひとつ困ったことが出てくる。

よく、この手のデータを使って、日々の「新規陽性者数の推移」などをグラフで示してみたいという需要が生じることがある(図表2)。

図表2 データをグラフ化する

(※なお、グラフの見栄えを良くする方法については、本稿では取り上げない。)

この場合、グラフを単発で作る分には問題ないが、「過去1ヵ月分の新規陽性者推移に関するグラフを毎日更新したい」と思うのならば、このデータの並び方だと不便だ。

なぜなら、このシートだと、最新の日付のデータはどんどんと下に流れて行ってしまい、その都度、手動でグラフの範囲を修正してやらなければならないからだ。グラフが複雑になればなるほど(たとえば元データの範囲が広がれば広がるほど)、この作業は億劫にもなるし、ミスもしやすくなる。

逆順グラフのススメ

そこで、当ウェブサイトとしておススメなのが、もう1枚、グラフのためだけの新たなシートを立ち上げることだ。

やりかたはとっても簡単で、シートの一番上(たとえばA2セル)に最新の日付を入力し、それ以下のセル(たとえばA3セル)に「=A2-1」と入力し、以下、A32セルまでコピーしてやればよい(図表3)。

図表3 日付逆順のシートを準備する

そのうえで、B2セルには次のような関数を入力し、それをB32セルまでコピーしてやればよい。

=VLOOKUP(A2,data!A:B,2,0)

その計算結果が、図表4だ。

図表4 計算結果

そして、この逆順のシートを元にグラフを作ってやると、グラフの元データは逆順になっているのに、グラフ自体は図表2とまったく同じものが出来上がる(図表5)。

図表5 図表2とまったく同じグラフ

なによりこの図表2、図表5を比べた大きな違いは、更新の容易さにある。

図表2の場合だと、新しいデータがどんどんと下に追加されていくため、いちいちグラフの範囲を変更してやらなければならない。しかし、図表5は、グラフの範囲は一定でも、その範囲内に収まるデータが勝手に書き変わるというスグレモノであるため、いちいちグラフの範囲を変更するという手間を省くことができるのだ。

しかも、図表4でも見たとおり、A3セル以降の日付自体は計算式で自動修正されるため、ユーザー側がA2セルだけを書き換えれば、すべてのデータが自動的に最新のものに置き換えられるのである。

これこそ、「効率的かつ正確な仕事術」の本質であろう。