[Excel財務レポート] （三日目）データと数式の入力

データを貼り付ける

では続いて、フォーマットにデータを入力していきます。一日目に作成した「jp-format」のデータをコピーし、「PL2009」に貼り付けます。

このときに貼り付け方法に注意してください。「形式を選択して貼り付け」から「値」を選び、「空白セルを無視する」にチェックを入れます。すると、フォーマットの数式を上書きしてしまうことなく、データだけが貼り付けられます。

図1：空白セルを無視して値貼り付け

数式が上書きされず、ちゃんと計算されていることを確認してください。

図2：数式を上書きせずに値貼り付けができた

この方法は非常に応用範囲が広く、決められたExcelフォーマットにデータを入力する業務のほとんどすべてに適用できます。本社であらかじめ作成されたフォーマットがある場合、フォーマットの縦軸を基準にして変換シートを作成し、vlookup関数で元データをフォーマットの形式に変換した上で、空白を無視して貼り付けます。するとフォーマットに複雑に組まれた数式を崩すことなく、しかも自分の使い慣れたデータ資産を元にして、本社にデータを渡すことができます。あらゆるExcelフォーマットの垣根を超えるテクニックです。

同様の作業を繰り返して直近までの数字を入力していきましょう。別の月の記帳データを「bookdata」に貼り付け、「jp-format」に出てきたデータをコピーし、「PL2009」に貼り付ける、という順番です。前年、予算のデータも同じ要領で貼り付けていきます。

累積データの数式を入力する

データの入力がすべて完了しました。データを入力すべき部分はこれだけで、あとは数式でデータを変換して利用していきます。では累計データの数式を入力してみましょう。シート「PL2009R」のセルC2に「=SUM(‘PL2009’!$C2:C2)」と入力します。この数式は、シート「PL2009」のC2セルからC2セルまでの合計、という意味です。

でもなぜ単純に「C2セル」と書かずに「C2セルからC2セルまでの合計」と書いたのでしょうか。それはこの数式を時系列にコピーしてみるとすぐに分かります。

セルD2にコピーされた数式は「=SUM(‘PL2009’!$C2:D2)」となっていますね。データが1～2月の累計になっていることを確認してください。

図3：累計を表す数式

先程、数式の中で用いた「$C2」という表現は応用範囲が非常に広いので少し詳しくご説明します。数式にセルの値を代数として入力した場合、「相対参照」と「絶対参照」という二つの参照方式があります。例えば「C2」は相対参照、「$C$2」は行列ともに絶対参照、「C$2」は行の絶対参照、「$C2」は列の絶対参照、となります。相対参照を持つ数式をコピーすると、コピー元のセルからの相対的な位置関係がそのままコピーされる一方、絶対参照の場合は行または列、あるいはその両方の位置が固定されたままコピーされます。

表1：絶対参照と相対参照（F4キーで順に変化します）

数式で累計データを表現したい場合、1月を起点として固定し（絶対参照）、各月までの各科目の（相対参照）合計を求めればいいわけですから、先程のような絶対参照と相対参照を組み合わせた数式になるのです。入力方法は、入力した数式の中で「C2」にカーソルを合わせた上で「F4」キーを押すと、順に「$C$2」、「C$2」、「$C2」と変化します。

ではセルC2に作成した数式を、空白セルすべてにコピーしましょう。「ジャンプ」から空白セルを選択し、「数式」貼り付けでOKです。

図4：累計データの完成

同様に、シート「PL2008R」、「PL2009FR」にもそれぞれ対応する累計の数式を入力します。上記の’PL2009’の部分を変えるだけでいいので簡単ですね。

これで財務レポートに必要なデータが、最小限のデータ入力で、すべて同じフォーマットで準備されました。あとは分析の必要に応じてデータを取り出していくだけです。