「ちょっとした工夫」でグラフの定期更新がラクラクに

「エクセル評論家」でもある当ウェブサイトの著者は、『エク達』というウェブサイトを運営していました。本稿はそのコンテンツ『非常に簡単で正確なグラフ更新術』の転載版です。転載に当たっては改題していますが、記事の中身自体に関しては、基本的に用語、言葉遣い等を原文どおりに転載しています。

エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

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

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

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

たとえば、こんなデータがあったとする(図表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セルだけを書き換えれば、すべてのデータが自動的に最新のものに置き換えられるのである。

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

読者コメント一覧

  1. クロワッサン より:

    ふと思ったのですが、A欄の上下を反転させたいのならば、対象列を選択して、先頭行を固定にし、並び替え機能を使えば良さそうかと…この様な置き換え方の方が汎用性があったり応用が効きやすかったりするのでしょうか?

    1. エク達管理人 より:

      コメントありがとうございます。『エク達』管理人です。
      『エク達』の趣旨は「誰でもミスなくできるだけ簡単な操作で高度な仕事をすること」にあります。
      並び替えを使うと次の弊害が生じるかもしれません。
      ①グラフが壊れます。
      ②「並び替え」のorderを間違えるとシートがぶっ壊れます。
      ③計算式が壊れます。
      これに対し、本稿で示した方法はエクセルに習熟していない人であっても日付セルを変更するだけで簡単に増えていくデータに対応できるという点に威力があると思う次第です。何卒よろしくお願い申し上げます。

      1. クロワッサン より:

        エク達管理人 さん

        なるほどです、ありがとうございます😊

  2. ななっしー より:

    なるほど、本連載におけるデータの並べ方は

      A      B
    1 順列1、(左を,範囲から)探せ
    2 順列2、(左を,範囲から)探せ
    3 順列3、(左を,範囲から)探せ
    ・・・

    これをヒナ形とするのですね。
    表示数も決まっているので、最新日付マイナス31から下にコピー、もアリですね。

    でも並べ替えも自然なコストの少ない問題解決思考だと思うなー。
    SORT関数も実装されたことだし。
    ただこの機能、買い切り版だとエクセル2021以降だったりする(MS365版ならOK)。

    ところで表計算の範囲選択、関数コピペは存在意義並みの長所であると同時に、その繰り返しは苦行とも思う。
    しかし最新エクセルには「スピル」なる機能があると知ってビックリ仰天!
    エクセルはどこまで行くのか!
    …と思ったらグーグルスプレッドシートのほうが先だったw

※【重要】ご注意:他サイトの文章の転載は可能な限りお控えください。

やむを得ず他サイトの文章を引用する場合、引用率(引用する文字数の元サイトの文字数に対する比率)は10%以下にしてください。著作権侵害コメントにつきましては、発見次第、削除します。

※【重要】ご注意:人格攻撃等に関するコメントは禁止です。

当ウェブサイトのポリシーのページなどに再三示していますが、基本的に第三者の人格等を攻撃するようなコメントについては書き込まないでください。今後は警告なしに削除します。なお、コメントにつきましては、これらの注意点を踏まえたうえで、ご自由になさってください。また、コメントにあたって、メールアドレス、URLの入力は必要ありません(メールアドレスは開示されません)。ブログ、ツイッターアカウントなどをお持ちの方は、該当するURLを記載するなど、宣伝にもご活用ください。なお、原則として頂いたコメントには個別に返信いたしませんが、必ず目を通しておりますし、本文で取り上げることもございます。是非、お気軽なコメントを賜りますと幸いです。

コメントを残す

メールアドレスが公開されることはありません。