( 1 )データの大修正のお膳立て

データを大修正する場合には、元のオリジナルのデータ(シート)を必ず残しましょう。そして、新しいブック(シート)にコピーして、そのコピーをいじりましょう。

作業中にいったん削除したデータが、途中からやはり必要だったということになったときでも、すぐにオリジナルから得ることができます。

そして、データを修正する前に、まずはオリジナルのデータそのものを検証します。ここを怠ると、とんでもないムダな時間の浪費になるおそれがあります。

オリジナルデータの分析

さまざまな目的で経営上Excelデータを作成することが一般的ですが、いろいろな事情(最新のデータが入っていない、フォーマットが変更された、数式が当たらなくなっている、リンク切れなど)でデータの信頼性が落ちてしまっている場合や、シートとシートを統合して新たな情報を作ろうとする場合、その前提としてオリジナルデータの信頼性を検証する必要があります。

オリジナルのシートでは作業しない

分析の前提として、オリジナルシートを必ず残し、そして、オリジナルのシートで直接作業はしないことです。

データを大修正する場合、既存のデータのうち不要な部分を削除することになることが少なくないのですが、削除して修正を行ったあとで、オリジナルデータからの情報を加えたり、削除したときは不要と思われたデータがやはり必要であったりします。このような場合に備えて、必ずオリジナルのシートは残します。

新規のシートにオリジナルシートをコピーしてから内容を分析します。

全行をつなぐ

シートをチェックしたり、また、データを抽出したり削除するときに有効なのがフィルター機能です。このフィルター機能が有効に働くためには、データが入力されている全行にフィルターが当たっていなければなりません。

まず、シートの左端(A列)に列を挿入し何かの文字を入れて、データがある一番下の行までずっとコピーします。「あ」でも「A」でも「+」でもかまいません。その理由は、列にフィルターをかけて検証や行の削除を行うときに、途中に何もデータがない行が挿入されていたりすると、その行のためにフィルターがその行の前の行までしかデータを拾ってきません。それを防ぐために、ある列に文字を入れて全行にコピーすることで、データが存在するすべての行にフィルターが当たるようになります。

番号を振る

次に、もう1列挿入し、番号を振ります。この理由は、データを大修正すると、並べ替えや行の削除が頻繁に起こります。このとき、オリジナルシートでは何行目にあったのかをあとで確認するために番号を振っておくのです。これを行うことによって、この行とこの行の間を何行削除したのかを後で確認できたり、大きく並べ替えを行ったときに、元の並びではどうだったのかを把握でき、データの補充や他のデータの結合を行うときに見つけやすくなります。

数式表示して検証

そして、データの各数値を検証します。私の場合には、オリジナルデータとオリジナルをコピーしたデータをディスプレイ上に整列して表示し、オリジナルデータのセルを数式表示にします。「ファイル  ホーム  挿入  ページレイアウト  数式  データ  校閲  表示」のうち「数式」を選び「関数ライブラリ  定義された名前  ワークシート分析」のうち「ワークシート分析」の「数式の表示」をクリックします。ディスプレイ上でふたつの同じシートが数字と数式で整列表示されているため、どのような数式で計算されているのかをチェックできます。

この作業で重要なのは、他のシートからのリンクがある場合です。そのセルはオリジナルシートをコピーしたシートではリンク切れでデータとして表示されていないこともあります。このようなデータについては、リンク元のシートを入手してそのシートをさらに分析することも必要になることがありますが、完全にブラックボックス化している場合には、「コピー」「形式を選択して貼り付け」「値」にしてしまいます。

行や列を挿入して検証

各数値を検証する場合には、大胆に行を挿入したり列を挿入したりして、その挿入された行や列に関数を入れることによって数字の正確さを確かめたり、誤りを見つけたりします。セルが文字列になっているために計算がおかしい場合や、小計行があるシートでその前に行を挿入したもののその行には数式があたっていないため計算されていないことなどが多く見受けられます。

単位未満の端数をチェック

このとき、各セルについて、小数点以下10桁くらいまで一気に表示します。この理由はもちろん、表示された金額の単位未満の端数処理がどうなっているのか検証します。

すると、オリジナルシートに小計行や合計行があるとき、それは表示単位未満の端数を考慮してそのまま「なりゆき」で行われているのか、なんらかの要因でベタ打ち(手入力)しているのかも容易にチェックできます。ベタ打ちしたデータに無限に表示単位以下の端数があるはずはないからです。 これは実は重要で、それぞれの小計行や合計行の数値が修正すべからざるものである場合、どこかに端数調整のためのセルを挿入しなければならなくなるからです。

並べ替えが重要になるデータの場合には、端数処理が致命傷になったりします。一見すべて「1」のはずのに、0.9と1では並べ替えではちゃんと順番が前後してしまいます。0.9と1では比較的明らかですが、無限に小数点以下の数値があるセルの場合では、小数点2位くらいまでの表示にしたのでは何の意味もないことがあります。

最初の段階でこのあたりを整理しておかないと、いざ完璧に作り上げたところで、「なぜ合計数値が合わないんだ?!」「なぜうまく数値が集計されない(並び替えられない)んだ?!」ということになります。この段階で再び修正するというのは容易ではありません。

値へ修正

数値自体の検証があらかた終了したら、数式を値に変更することを検討します。

数式は入っていると、もし行や列を削除すると、削除したセルに計算式が当たっている場合に、データにエラーが出てしまうからです。それを防ぐためには、数式のセルを値に変換するほうがよいことになります。

シート全体をコピーして、まったく同じ箇所に貼り付けますが、その際に「形式を選択して貼り付け」「値」で貼り付けます。

文字列の修正

名称でフィルターをかけたりする場合には、各セルのデータを完璧に一致させなければなりません。並べ替えではそれなりにうまくいっても、フィルターやSUMIFやVLOOKUPをかけると異なるデータとして認識されるため、抽出されない恐れがあります。これを前提にして集計され加工されたデータに信頼性がないのは当然といえます。

全角や半角、スペース(これも全角と半角)、文字列と数字、記号・・・これによってデータがうまく拾えない場合があります。

これらを修正する場合でも、隣に列を 1 列挿入してオリジナルの列データをコピーして修正を行います。

文字列操作の関数を入れる方法もありますが、それほど複雑でない場合、私は「置換」を使います。「置換」によってスペースや特定の文字を簡単に修正することができるのです。ベタな和暦の文字列であったデータを、カンマを斜線に置換するなど何回か作業を行うことで西暦の日付表示に変換することもできます。

( つづく )