時短エクセル(第30回)

Excel内シートの追加更新。参照式の一括変更

2020.06.01

クリップについて

 売り上げ一覧を毎年作成しているとする。1年1シートだ。前年同期比を算出する場合、前年(前のシート)を参照する必要がある。これを毎年追加していく場合、1つひとつ「2018」を「2019」に手直しするのは大変だ。今回は、以下の2つを紹介しよう。

1)別のシートを参照した式を立てる方法
2)次の年も簡単に参照シートを変更する方法

別のシートを参照した式を立てる

 解説のために簡易な売り上げ一覧で説明する。まずは2018年から開始した表があるとしよう。2019年には、2018年との第1四半期の前年同期比を算出したい。それには、2019年の合計を2018年の合計で割ればよい。除算の記号は「/」(半角スラッシュ)だ。

まずは半角で「=」を入力する

 

次に「E3」をクリックすると、自動的に「E3」と入力される

 

そして「/」を入力する

 

次に「2018」のシート見出しをクリックし、比較するセル(ここではE3)をクリック

 

最後に「Enter」キーを押せば式が入力される

 

 数式は「=E3/’2018′!E3」となる。あとは、F3セルを下方向に相対参照でコピーすれば、正しい数値を求められる。

F3セルの右下隅のフィルハンドルを下方向にドラッグする

 

二重罫線が消えてしまったら、オートフィルオプションをクリックし、「書式なしコピー」を選ぶ

 

すると二重罫線が復活する

 

次の年も簡単に参照シートを変更する

 では次に、2020年の表で前年同期比を計算してみよう。フォーマットを継承するために、「2019」シートをコピーする。「Ctrl」キーを押しながら「2019」シート見出しを右方向にドラッグすればコピーできる。

「2019 (2)」というシート名になるので、ダブルクリックして「2020」に書き換える

 

1月~3月の売上数(B3~D6)を選択し、「Delete」キーを押して消去する

 

 タイトルも「2020」に書き換え、データを入力していく。ここで、「前年同期比」を見てみると、「=E3/’2018′!E3」となっている。ここは修正していないので当然だ。これでは前々年との比較になってしまうので、前年との比較にしたい。この例では数カ所のみだが、巨大な表で、あらゆる所に別シートの参照がある場合は、手作業ではいくら時間があっても足りない。数式の一部を修正したい場合はどうするか。

 実はとても簡単だ。「置換」機能を使えばよいのだ。

範囲を指定し、メニューから「置換」をクリックする。範囲は、表全体を指定したが、F列だけでもよいし、巨大な表のときはシート全体を範囲としてもよい

 

検索する文字列に「’2018’」、置換後の文字列に「’2019’」と入力して「すべて置換」をクリックする

 

 「○件を置換しました。」というメッセージが出たら「OK」をクリックし、置換画面に戻ったら「閉じる」をクリックしよう。

前年同期比のセルを見ると正しく修正されていることが分かる

 

 ここでちょっとしたコツを2つ。1つは、置換文字列を「2018」や「2019」にしないこと。もし、データの中に同じ数値があるとそれも置換されてしまう。別シートを参照する場合には必ず「’」(半角シングルクォーテーション)で囲むことになっているので、「’2018’」としておけばまず問題はない。

 2つ目は置換の方法。今回は「すべて置換」で一気に置換したが、その右横にある「置換」ボタンを押すと、1件ずつ確認しながら置換できる。置換したくないセルがあった場合は「次を検索」を選べば、そのセルは置換せずに次の対象セルに移動できる。

※解説用画面はOffice 365のExcelを使用。一部メニュー名などが異なる場合がある
※第30回は2020年7月6日(月)公開

【ダウンロード】
「時短エクセル」Excel内シートの追加更新。参照式の一括変更 のお試しファイルのダウンロードはこちらから(Excelファイル)

SID : 00125030

執筆=内藤 由美

執筆=内藤 由美

大手ソフト会社で14年勤務。その後、IT関連のライター・編集者として活動。ソフトウエアの解説本、パソコンやスマートフォンの活用記事などを執筆、編集している。

あわせて読みたい記事

連載記事≪時短エクセル≫

PAGE TOP

閉じる
会員登録(無料) ここでしか読めないオリジナル記事が満載
閉じる