実務で使えるExcel入門セミナー 毎月更新するExcelファイルは「シートのコピー」と「置換」を使って効率よく仕上げよう

シェアする

  • このエントリーをはてなブックマークに追加

月ごとの売上や経費を分析のために作成するExcelファイルは、形式を変えずに「シートのコピー」と「置換」を使って更新することで、効率よく作成することができます。

スポンサーリンク
自動サイズレスボンシブ

Excelでは「別ファイル」「別シート」にあるセルを
参照することもできます

経理や財務の仕事をしていると、毎月の業績を報告するために、前月もしくは前年同月比のデータを作成することがあります。

月ごとにシートを分けた「商品別の売上ファイル」で、

前年同月の売上と比較する

たとえば、Excelで毎月の商品別売上のデータを入力して、
前年同月比のデータと比較する表を作成しているとします。

スクリーンショット 2015 11 27 22 10 42

1年分を1つのファイルにまとめて、各月のデータをシートに分けて入力しています。

スクリーンショット 2015 11 27 22 14 22


この表には1つポイントがあります。
それは、前年のデータは昨年度の商品別売上高のファイル参照して、表示させていることです。


※参照
=シート上のセルの位置(たとえば”A1”などのセル番号)を指定することで、指定されたセルに入力されているデータ表示させること)

スクリーンショット 2015 11 27 22 20 44

「※参照」の説明では「セルの位置を指定することで」とありますが、
セルだけではなく、「シート名」さらには「ファイル名」を指定することで、
別ファイルにあるセルに入力された内容も、表示させることができるということです。

ファイル間の参照

このような「ファイル間の参照」は決して難しくはありません。

2つのファイルを開いて、入力したいセルに「=」を入力した後、
別ファイルにある「参照したいセル」をクリック
すると、

スクリーンショット 2015 11 27 22 44 05

入力したいセルに「ファイル名」「シート名」「セル番号」が入力されて、


スクリーンショット 2015 11 27 22 54 54

別ファイルにあるセルの内容を、表示することができます。

「シートのコピー」と「置換」で効率よく翌月のデータを仕上げる

もう1度、サンプルに挙げた表を見てみましょう。

スクリーンショット 2015 11 27 22 10 42

前年の数値で「ファイル間の参照」を使っているものの、とてもシンプルな作りの表です。

つぎは、翌月分の表を作る事を考えましょう。
ただ作るだけでは意味がないので、できるだけ効率よく作る事を考えます。
ポイントは「シートのコピー」と「置換」です。

「シートのコピー」で前月分の形式を使う

まずは、形式を引き継いで表を作ります。
Excel業務効率化のポイントの1つは「使えるものはそのまま使う」こと。

もちろん、見づらい、使いづらいと言った問題がある場合は別ですが、
特に問題がなければ、同じ形式を使い内容だけ当月分(当期分)に書き換えるのが効率的です。

そこでまずは、シートをコピーして当月分のシートを作ります。

「シート名(ここでは「201510」)」にカーソルを合わせて右クリック。
リストが表示されるので「移動またはコピー」をクリック。


スクリーンショット 2015 11 27 23 16 02


「シートの移動またはコピー」画面が開くので、「コピーを作成する」にチェックを入れて、「OK」ボタンをクリック。

スクリーンショット 2015 11 27 23 20 44


これで、シートのコピーができます。
ここで、シート名の所をダブルクリックすると編集できる状態になるので、シート名を当月分(201511)に変更しておきましょう。

スクリーンショット 2015 11 27 23 24 20

「201510(2)」のままでは、どの月のシートかが分からなくなるからです。

当月分を入力

コピーしてきたシートのデータは、全て前月分なので、
当期分の商品別の売上高」を入力します。

スクリーンショット 2015 11 27 23 36 25

ここは、会計データから転記するしかありませんが、
その際にも、「会計ソフトからエクスポートしたデータを、どうすれば効率よく転記できるか」については考えておくべきです。

たとえば、 VLOOKUP関数などが利用できるようなら、1つ1つのデータをコピー&ペーストするよりも速く転記できるかもしれません。

前年分の参照を「置換」で効率よく変更する

最後は、前年の入力です。
この列は、前年のファイルを参照して表示させていました。

スクリーンショット 2015 11 27 22 54 54


ファイル間の参照は、「ファイル名」「シート名」「セル番号」を指定する必要がありますが、順に「2015売上商品別.xlsx」「201410」「$C$9」と指定されているのが分かります。

これを、当月分に変える訳ですが、そのためには、前年同月分つまり2014年11月分に参照を変更する必要があります。

先ほど、「ファイル間の参照」について説明しましたが、そこで説明した

「2つのファイルを開いて、参照するセルをクリックする」

という作業がここで必要になるのか、というとそうではありません。


というのも、参照するのは「2015売上商品別.xlsx」ファイルにある11月分のシートにある表です。

どの年のどの月のシートも、前月分をコピーして作成していますから、表の形は同じ
だとすれば、「シート名」を前年同月分に変える、つまり、現在入力されている「201410」を「201411」にしてあげれば、前年のファイルの11月分を参照できるはずです。

スクリーンショット 2015 11 27 23 54 34


ここで使うのが「置換」です。

「置換」を使うと、「対象となるデータ」と「置き換えるデータ」を入力すれば、
「対象となるデータ」を「置き換えるデータ」に置き換えてくれます。


具体的な「置換」の手順です。
「Ctrl+H」をタッチすると「検索と置換」の「置換」タブが開きます。

スクリーンショット 2015 11 28 0 07 01


検索する文字列」が置き換えの対象となるデータなので、「201410
置換後の文字列」が置き換えるデータなので、「201411
と入力します。

スクリーンショット 2015 11 28 0 10 22


置き換えが必要なのは「前年」の列の、「A-01」から「C-02」までの6つのセルなので、「すべて置換」をクリック。
すると、

スクリーンショット 2015 11 28 0 29 57

参照するシートが、前年のファイルの11月分(201411)に変更され、
金額も前年の11月分に変更されました。

Excelの作業を効率化するには、資料の形式を統一する

今回は、商品別売上高のファイルで、翌月分のシートを作成する作業を行いましたが、そこでポイントになったのが「置換」でした。

では、いつでも「置換」が使えるかというとそうではありません。
今回、「置換」を使うことで作業が効率化できたのは、毎年同じ形式で表を作っていたからです。

どの年のファイルでも、同じルールでシート名を決めて、同じセルに同じ商品の売上が入力されている、という前提があったからこそ、簡単に「置換」することができました。

これがシート名の決め方や表の形がバラバラだと、「置換」を使ったとしても正しく参照を指定することができず、修正に時間がかかって作業効率は落ちたはずです。

これは1つの例ですが、資料の形式を統一することがExcelの作業効率化につながることはよくありますので、覚えておくといいでしょう。

まとめ

毎月更新するExcelファイルは、「シートのコピー」と「置換」で作業を効率化できます。ただし、それが可能になるのは資料の形式を統一していることが条件です。

おまけ

気温が下がってきたので、ラン用のウェアも冬仕様に移行中。
特に指先の冷えの対策は重要なので、手袋は欠かせません。

スポンサーリンク
自動サイズレスボンシブ
自動サイズレスボンシブ

シェアする

  • このエントリーをはてなブックマークに追加

フォローする