【プチテクニック】OfficeスクリプトでExcel帳票に値を入力する際、行・列を追加しても参照先をずらさない方法

2024-11-02 2024-11-02

イチゲキコピペくん

Contents

1 結論
2 解説
- 2.1 名前の定義方法
3 メリットなど

結論

セルに名前を付けて、スクリプトに記述するセル参照はセルの名前を指定すること。


function main(
    workbook: ExcelScript.Workbook,
    sName: string,
    sBirthDate: string,
    sPost: string
) {
    //シートオブジェクト取得
    let sh = workbook.getWorksheet("Sheet2"); //シート名を取得

    //セルへ書き込み
    sh.getRange("_氏名欄").setValue(sName);
    sh.getRange("_生年月日").setValue(sBirthDate);
    sh.getRange("_役職").setValue(sPost);

}

解説

OfficeスクリプトでExcelシートに値を入力する際、よく行うのはセルの位置情報を列番号・行番号を指定する方法、またはデフォルトのセル名称（C1など）を指定する方法の二つがある。


function main(
    workbook: ExcelScript.Workbook,
    sName:string,
    sBirthDate:string,
    sPost:string
    )
{
    //シートオブジェクト取得
    let sh = workbook.getWorksheet("Sheet1"); //シート名を取得

    //セルへ書き込み
    sh.getRange("C4").setValue(sName);
    sh.getRange("D4").setValue(sBirthDate);
    sh.getRange("E4").setValue(sPost);

}

上記を実行すると引数の入力ののち、各セルに値が入力される。

しかし、Excelの列を追加するなどして、セルの位置がずれると、ソースコードは自動的に書き換わるわけではないので、意図した箇所に意図した文字列が挿入されない。
たとえばDとEの間に新たに列を追加すると、当然E4に入力した役職名が挿入される。

実行結果。ソースコード通りE4に役職名が挿入されるが、意図した箇所とは言えない。

別にソースコードをE4からF4に修正すればいいのだが、Excel自体のフォーマットの修正など保守性を考慮すると、列や行の追加があっても意図した箇所に・つまり絶対参照的な形で入力してくれたほうが望ましい場合がある。

そのような場合は事前にセルに名前を定義し、プログラムコードでセルの番地ではなく名称を指定してあげればよい。

名前の定義方法

名前を付けたいセルをクリックし、左上（入力欄の左側）に任意の文字列を挿入すればok。

あらためて冒頭のとおり、セルを名前で指定するプログラムを作成、実行。


  function main(
      workbook: ExcelScript.Workbook,
      sName: string,
      sBirthDate: string,
      sPost: string
  ) {
      //シートオブジェクト取得
      let sh = workbook.getWorksheet("Sheet2"); //シート名を取得

      //セルへ書き込み
      sh.getRange("_氏名欄").setValue(sName);
      sh.getRange("_生年月日").setValue(sBirthDate);
      sh.getRange("_役職").setValue(sPost);

  }

間に列が入っているかどうかに関係なく、指定されたセルの名前をもとに値が入力された。

メリットなど

「いちいち名前定義するのが面倒くさいな」となるかもしれないが、これをやっておくと以下のメリットがある。

・ソースコードの可読性が上がる
・将来的なExcelシート側の改変・修正に伴うソースコードの保守性が上がる

実際、C4がこれ、D4がこれ、というのを目でチェックしながらソースコードを修正するのは（数が多ければ多いほど）なかなか堪えるので、一番最初に設定しておけば可読性と保守性を向上できる場面があるだろう。