中堅・中小企業のための“脱Excel”ロードマップ【第3回】

“神Excel”からデータを抜き出しBIに生かす　「OFFSET関数」の使い方

Microsoft Excelのデータをビジネスインテリジェンス（BI）ツールに簡単に取り込むには「OFFSET関数」が役立ちます。ポイントは、データの蓄積と出力を分離することです。

公開 2017年01月31日 09時00分

更新 2017年01月31日 18時23分

著者

村山聡

[著]

印刷する連載新着通知を受け取る

連載について

　「Microsoft Excel」（以下、Excel）は、ビジネスにおいてなくてはならないソフトウェアとして、あらゆるビジネスシーンで長年広く利用されています。一方で、Excelがあまりにも普及してしまった“副作用”が少なからず見られます。ビッグデータブーム以降、ビジネスインテリジェンス（BI）ツールがデータ分析ツールとして再び注目を集めましたが、いざ導入しようとすると現場の反対を受け、思うように導入が進まないといった問題も見受けられるようになりました。

　「なぜ、ビジネスの現場でExcelの利用がやめられないか」――本連載では、その理由を検証し、どのようなプロセスでBIツールの導入を進めていくべきかについて解説していきます。

BI切り替えに立ちはだかるExcel帳票

　BIの導入を検討するとき、特に厄介な存在が、見栄え重視で作成されたExcel帳票のデータでしょう。こうした帳票は、帳票としての体裁は整っています。しかし体裁を整えるためにセルの結合を多用したり、セルを方眼紙のように設定したり、1つのセルに1文字のみ入力するようなマス目を設定したりしています。そのため、データを加工して、BIに取り込めるようなリスト形式（列ごとに同じ種類のデータを入力する表形式）にするためには膨大な手間が掛かります。なんとか加工を終えたとしても、帳票自体を継続して使用する限り加工作業は残ります。また帳票は度々修正されることがあるため、加工作業が楽になるように工夫したとしても、帳票が修正されるたびに、加工作業にも変更を加えなければなりません。見栄え重視のExcel帳票はインターネットでは通称“神（紙）Excel”と呼ばれ、データの再利用性を損なう要因とされています。

　とはいえ見栄え重視のExcel帳票が単純に悪いという批判は早計でしょう。そもそも帳票を作成した当時は、印刷して記入する用途が前提だったかもしれません。データ入力を前提としていたとしても、データを入力した帳票は印刷して保存するというルールが組織内にあったのかもしれませんし、帳票をFAXでもやりとりすることを考慮していたのかもしれません。紙の帳票としての利用も考慮していたのであれば、一覧性、可読性を考慮して、帳票が見栄え重視となったとしても仕方のない面があったのです。

　しかし時代は変わり、帳票を紙でやりとりする場面は少なくなってきています。もし紙でのやりとりを一切排除できるのであれば、帳票を廃止し、データをログデータとしてリスト化できるWebサービスに変更することが、BI向けのデータを整える一番の近道となります。単純なWebフォームを利用することも可能ですが、BPM（ビジネスプロセスマネジメント）ツールを利用すれば、単なる情報の受け渡しだけではなく、その後の処理も管理できます。やりとり上、帳票という体裁が必要な場合は、入力した情報を帳票に変換してくれるWebサービスを利用するとよいでしょう。

併せて読みたいお薦め記事

“何でもExcel”は終わるのか

注目のセルフサービスBI

Excel帳票改善のポイント

　しかし、例えばコスト的な理由で、上述したようなWebサービスを利用できない場合も考えられます。その場合Excel帳票を継続して使用することになりますが、そのまま使用すると、結局データ加工の手間の問題は解決されません。では一体、どのように改善すべきでしょうか。最初にお伝えした通り、Excel帳票の最大の問題は、帳票に入力されたデータをBIで使うためのリスト形式に加工する作業の手間にあります。であるとすれば、まずリスト形式でデータを入力し、そのデータを帳票に反映するように変更できれば、データ加工の手間自体を無くすことが可能です。さらにいえば入力データは1つのシートにまとめて入力し、帳票に表示したいデータを都度選択して表示するようにできれば、データを集約する手間も省くことも可能です。つまりデータの蓄積と出力を分離することが改善のコンセプトとなります（図1）。

図1　Excel帳票改善のポイント

「OFFSET関数」を活用してデータと帳票を分離する

　では具体的にどう実現するか、考えてみましょう。Excelが備えるプログラミング言語「Visual Basic for Applications」（VBA）を利用して、リスト内の必要な行データを帳票へ反映することは、VBAを理解している人であればそれほど難しいスキルは必要ありません。しかし、帳票を管理している担当者に必ずしもVBAのスキルがあるとは限りません。帳票に修正が発生するたびに、帳票の担当者からVBAの修正を頼まれるようなことを避けるためにも、VBAを利用しない方法を検討すべきでしょう。このような場合に利用できるうってつけの関数が「OFFSET関数」です。

　あまりなじみがない関数かもしれませんが、OFFSET関数は、あるセルの位置を基準とし、そのセルから指定された行数および列数分、離れた位置にあるセル（範囲の指定も可能）の値を返す関数です。書式は以下のようになります。

OFFSET（基準セル、基準セルからの行数、基準セルからの列数、参照するセルの高さ、参照するセルの幅）

※参照するセルの高さと幅は省略可能

　例えば、あるセルに対して、セルC4に入力されている値を表示させたいとしましょう。一般的には「=C4」と直接セルを指定します。OFFSET関数の場合は「=OFFSET（A1,3,2）」と記述します。この記述は、セルA1を基準の位置として、3行下、2列横に移動した位置（つまりセルC4）の値を取得するという意味となります。単純にセルを指定すれば式も短くて分かりやすいのに、どうしてこのような方法を使うかというと、OFFSET関数を使うことでセルの位置を数値で指定できるようになるからです。セルの位置を数値で指定できるということは、設定用の数値を入力するセルを用意しておけば、参照する値を一度に変更することが可能になります。

図2　OFFSET関数の入力例

　図2の表はセルA1からC4までリスト形式で、データが入力されています。セルF2からF4にはOFFSET関数の式が記述されており、セルF1の値で行を指定するようになっています。

　図2上の表では行指定が「1」となっており、基準のセルであるA1を起点に、1つ下の2行目の値が、F2からF4までのセルに表示されるよう設定しています。図2下の表ではセルF1の行指定の値として「2」と入力されています。すると、F2からF4までのセルの値が、左の表における3行目の値に変わります。このようにOFFSET関数の機能をうまく利用すると、リストデータから必要な行のデータを取得することが可能になります。帳票内のセルにOFFSET関数をあらかじめ入力しておき、リストにデータを入力した後、帳票に表示させたい行を行指定のセルで指定するだけで、帳票のデータが変更されます。

　実は「VLOOKUP関数」でも同じようなことは実現できるのですが、リストの一番左の列を必ず検索値とする必要があったり、データを「テーブル」という形式に変換しないと検索範囲を変更できなかったり、といった制約があります。使い方を理解してしまえば、OFFSET関数の方が制約も少なく使い勝手がよいのです。帳票をこのように改善し、帳票の担当者にOFFSET関数の機能を教えてしまえば、帳票に何らかの変更があったとしても、帳票の担当者が自力で修正することもそれほど難しいことではないでしょう。

　データの蓄積と出力の分離は、帳票を利用する側にもメリットがあります。Excelはもともとリスト形式のデータを入力するための支援機能が充実しており、帳票に直接データを入力するよりもリストにデータを入力する方が、はるかに入力効率が良くなるからです。このようなメリットも帳票の利用者に伝えたうえで、帳票の改善を進めると現場の反発も少なくなると考えられます。

　いつかBIを導入する時に備えて、帳票の運用を早めに改善しておくことが、BI導入をスムーズに進めるための鍵となるはずです。