PR

 今回はデータ集計のワークシートです。元データに抜けがあって飛び飛びになっていても処理できる工夫をしてあります。図の例では東京、大阪、福岡の各拠点での売上金額をまとめようとしています。ところが拠点によって営業日が違うし、営業していない日はデータがありません。コピー・アンド・ペーストでデータを集めるだけですが、手作業でやるのは面倒なだけでなく、違う日のデータをコピーしてしまうなどのミスがつきものです。

 実をいえばこの問題、最適な解決方法は各拠点に指示して、あらかじめ日付の抜けがない表にデータを入れてもらうように業務改善することでしょう。そうすれば簡単にデータを集められます。Excelテクニック以前の問題です。

 しかし現実には抜けのあるデータを集計しなければならないことはままあります。「抜けのないデータをあらかじめ作成しておくべき」などと正論を吐いたところで事態は前進しません。何とかExcelでカバーしてみましょう。

名前を付けてVLOOKUP

 単純化するために、図の例では1枚のシートに元データと集計表を同居させました。左の東京、大阪、福岡と書かれた3つの表が元データ、右にある一番大きな表が集計表です。集計表の日付部分はオートフィルを使って抜けのない日付を入れてあります。

拡大表示
東京、大阪、福岡の3拠点にわたる売上データを集計したい。ただし拠点によって営業日が違うし、非営業日はデータがない。手作業でやっていると必ず転記間違いが起こる

 元データを転記するために使ったのはVLOOKUP関数です。式を分かりやすくするために元データの範囲に「名前」を付けました。名前を付けるのは対象のセル範囲(図の例ではB3からC13)をドラッグして選択状態にして「挿入」メニューから「名前」→「定義」とたどります。慣れたら簡単に数式バーから名前を付けることもできます。同様に大阪と福岡の元データ部分にも名前を付けてあります。

 さて集計表の数式です。L3セルの式を図に示してあります。VLOOKUPは元のデータから表引きして目的の値を求める関数です。引数の意味を少し説明しておきましょう。2番目の引数には元データの範囲を入れます。この場合だと「B3:C13」と入れるか、先に名前を付けてありますから「東京」と入れます。でも「東京」の文字はL2 セルに入れてINDIRECT関数で参照しました。こうすることで大阪、福岡の集計でも同じ数式をコピーして使えるからです。

エラーは隠して無視する

 ここまでの数式だと、データの抜けている日は「#N/A」というエラー表示になってしまいます。IF関数を組み合わせれば回避できますが、式が複雑になります。そこでエラー表示は残して、「条件付き書式」機能を使って、エラー表示の場合は文字の色を「白」にする設定を加えました。これでほぼ大丈夫です。

 Excel 2002以降のバージョンを使っている場合、エラーのあるセルには左上隅に小さな緑のマークが付きます。図の集計表部分でも空欄のセルには隅に小さなマークが付いていることが確認できます。

 問題はデータの合計です。「#N/A」エラーの入ったセル範囲をSUM関数で合計すると「#N/A」エラーになってしまいます。そこでSUMIF関数を使いました。「0より大きい」データだけを合計しています。エラーのセルは条件から外れますからエラーのない数値部分だけが合計されます。ただし、金額がマイナスになる可能性がある場合はこの数式は使えないのでご注意ください。マイナスのデータが合計に含まれなくなります。

■解説で使用したExcelファイルを以下からダウンロードできます。

 自己解凍形式(38KB)
 Excelファイル形式(24KB)

 うまくダウンロードできない場合は日経パソコン講座ファイルのダウンロード方法をご覧ください。