【Excel】エクセルの外部参照をうまく使い、複数ファイルを1つにまとめる

Excel

業務でエクセルをいじる機会があり、複数のファイルを1つにまとめる、という作業の効率化のためにいい感じの式を作りました。
プログラミング、というほどでもないですがいい機会なのでまとめておきます。

元データ

2016年1月1日〜2018年12月31日までの着信数、予測着信件数、放棄件数、応答率/日

データ構成

・ファイル/月で分割(ファイル名「20YY年MM月.xlsx」)
・シート/日で分割

  • シート名「1」「2」・・・(2016年1月〜2016年3月)
  • シート名「S_01」「S_02」・・・(2016年4月〜2018年2月)
  • シート名「F_01」「F_02」・・・(2018年3月〜2018年12月)

・1シート内にA社・B社・C社のデータが存在
・横軸は0時〜23時
・縦軸はA社(着信件数・予測着信件数・放棄件数・応答率)、B社(同構成)、C社(同構成)
・A社の着信件数:

  • 行番号5開始セル位置C5 (2016年1月〜2016年3月)
  • 行番号5開始セル位置E5 (2016年4月〜2018年2月)
  • 行番号8開始セル位置I8 (2018年3月〜2018年12月)

・B社の着信件数:

  • 行番号9開始セル位置C9 (2016年1月〜2016年3月)
  • 行番号9開始セル位置E9 (2016年4月〜2018年2月)
  • 行番号12開始セル位置I12 (2018年3月〜2018年12月)

・C社の着信件数

  • 行番号13開始セル位置C13 (2016年1月〜2016年3月)
  • 行番号13開始セル位置E13 (2016年4月〜2018年2月)
  • 行番号16開始セル位置I16 (2018年3月〜2018年12月)

作成したいデータ

2016年1月1日〜2018年12月31日までの着信数/日

データ構成

・1ファイルで完結
・シート/社で分割 シート名「A社」、「B社」、「C社」
・横軸は0時〜23時
・縦軸は日(2016年1月1日〜2018年12月31日)
・日を入れるカラムの前に、曜日(日:0,月:1,・・・)と祝日フラグ(祝日:1,祝日以外:0)を入れるカラムを用意

作成方法

※外部参照をする際には当該ファイルを開いておき、埋め終わった範囲を「値として貼り付け」し直してから当該ファイルを閉じること
※(2016年1月〜2016年3月)(2016年4月〜2018年2月)(2018年3月〜2018年12月))ごとに上記のようにシート構成が変わるので、それに合わせて関数式の内容を若干変えること
※ ここではひとまず(2016年1月〜2016年3月)分のデータ作成について解説する

  1. 新しく作成したエクセルファイルにシートを3つ作成し、A社・B社・C社と名前をつける
  2. 各シートのB行に、B5セルから下に2016/1/1~2018/12/31(日付形式)をオートフィルで入れていく
  3. C4、D4セルに表のヘッダとして「曜日」「祝日」を入れる C列の以下に0~6をオートフィルで埋める D列は祝日を調べて0or1で埋める
  4. E4セルからAB4セルまで、0時~23時をオートフィルで入れていく 最後AC4セルにヘッダとして「合計」を入れる
  5. E5セルに以下の関数式をペースト、オートフィル

=INDIRECT("["&TEXT(YEAR($B5),"G/標準")&"年"&TEXT(MONTH($B5),"G/標準")&"月.xlsx]"&TEXT(DAY($B5),"G/標準")&"!"&CELL("address",C$5))

解説

上式の変数部分を日本語に置き換えるとこうなる


=INDIRECT("["&TEXT(YEAR(日付が入っているセル番地),"G/標準")&"年"&TEXT(MONTH(日付が入っているセル番地),"G/標準")&"月.xlsx]"&TEXT(DAY(日付が入っているセル番地),"G/標準")&"!"&CELL("address",参照先シートの着信件数が入っているセル番地))

  1. INDIRECT関数の引数には、外部参照したいセルの情報を文字列として入れる
  2. 形式は・・・[ファイル名.xlsx]シート名!セル番地
  3. ここで、日付が入っているセルからYEAR関数、MONTH関数、DAY関数を使って年・月・日を数値として抜き出し、TEXT関数に入れて文字列に変換する
  4. それらと”[“、”年”、”[月.xlsx]”などをうまく組み合わせ、動的に[ファイル名.xlsx]シート名!を表現する
  5. 最後に、CELL関数を使って参照したいセルのセル番地を取得し結合

以降のデータについても関数式の内容を変えて同様に行う

コメント

タイトルとURLをコピーしました