Table.addRows でもR1C1参照形式で関数式を書き込みたい！

おはこんばんにちは！　株式会社VALGO エンジニアの u-sho です。
私はWebフロントエンドが専門で、普段は TypeScript や CSS をよく書いています。

今回は業務で Excel のマクロを触ることになったので、お勉強しています。

いまどきの Excel は「VBA」だけではなく、「Officeスクリプト」という TypeScriptライク な言語でマクロを書けるらしいので、Officeスクリプトを使って関数を埋め込んでいきたいと思います。

## OfficeスクリプトでセルにExcelの関数式を書き込む

Officeスクリプトを書いていて、セルにExcelの関数式を直接書き込みたい場面があります。
例えば「A31セルに =SUM(A2:A30) を書き込む」ようなことです。
Officeスクリプトで計算した値だけ書き込むよりも、Excel側で編集できたり、小数の丸め方を後から設定できたりといったメリットがあります。
このとき、Officeスクリプトはこのようになります。

/** `A31`セルに`=SUM(A2:A30)`を書き込む */
function main(workbook: ExcelScript.Workbook){
  // 現在のExcelワークシートを取得
  const selectedSheet = workbook.getActiveWorksheet();

  // 現在のワークシートからA31セルを取得
  const A31Cell = selectedSheet.getCell(30, 0); // A31セルは30行0列目（ゼロはじまり）にある

  // A31セルに関数式を書き込む
  A31Cell.setFormula("=SUM(A2:A30)");

  return;
}

## OfficeスクリプトでセルにR1C1参照形式の関数式を書き込む

先の例では、書き込む数式中の`A2:A30`のセル範囲がA1参照形式¹で分かっていますが、Officeスクリプトでデータの整理などを行っていると、何行何列目から何行何列目の範囲かは分かっていてもA1参照形式は分からない²ということもよく発生します。

そこで登場するのが、R1C1参照形式です！  

R1C1参照形式には、絶対参照形式と相対参照形式があります。  

絶対参照形式は R31C1 のように書くことで、ワークシートの左上から31行1列目のセル（＝A31セル）を表すことができます。  

相対参照形式ば R[-1]C[1] のように書き、書き込まれたセルから1行上かつ1列右のセルを表すことができます。

### OfficeスクリプトでセルにR1C1絶対参照形式の関数式を書き込む

R1C1絶対参照形式を使って先ほどの例「A31セルに =SUM(A2:A30) を書き込む」を書き直すと次のようになります。

/** `A31`セルに`=SUM(A2:A30)`を書き込む */
function main(workbook: ExcelScript.Workbook){
  // 現在のExcelワークシートを取得
  const selectedSheet = workbook.getActiveWorksheet();

  // 現在のワークシートからA31セルを取得
  const A31Cell = selectedSheet.getCell(30, 0); // A31セルは30行0列目（ゼロはじまり）にある

  // A31セルにR1C1絶対参照形式の関数式を書き込む
  const startRangeCellName = "R2C1";  // A2セル
  const   endRangeCellName = "R30C1"; // A30セル
  const formulaR1C1 = `=SUM(${startRangeCellName}:${endRangeCellName})`; // "=SUM(R2C1:R30C1)"
  A31Cell.setFormulaR1C1(furmulaR1C1); 
  return;
}

この例では startRangeCellName と endRangeCellName は固定値ですが、 i 行 j 列目のセルを参照したいときは startRangeCellName = `R${i}C${j}`; のように、座標変換なしで簡単に参照することができます。

### OfficeスクリプトでセルにR1C1相対参照形式の関数式を書き込む

R1C1相対参照形式では、例えば、 i 行 j 列目のセルにおいて、 i-29 行 j 列目から i-1 行 j 列目までの範囲の合計を表すのは、 =SUM(R[-29]C:R[-1]C) となります。

R1C1相対参照形式を使って、先ほどの例「A31セルに =SUM(A2:A30) を書き込む」を書き直すと次のようになります。

/** `A31`セルに`=SUM(A2:A30)`を書き込む */
function main(workbook: ExcelScript.Workbook){
  // 現在のExcelワークシートを取得
  const selectedSheet = workbook.getActiveWorksheet();
  // 現在のワークシートからA31セルを取得
  const A31Cell = selectedSheet.getCell(30, 0); // A31セルは30行0列目（ゼロはじまり）にある

  // A31セルにR1C1絶対参照形式の関数式を書き込む
  const startRangeCellName = "R[-29]C";  // A31セルの29行上のセル（＝A2セル）
  const   endRangeCellName = "R[-1]C";   // A31セルの1行上のセル（＝A30セル）
  const formulaR1C1 = `=SUM(${startRangeCellName}:${endRangeCellName})`; // "=SUM(R[-29]C:R[-1]C)"
  A31Cell.setFormulaR1C1(formulaR1C1);
  return;
}

この例では startRangeCellName と endRangeCellName は固定値ですが、i 行 j 列目のセルを参照したいときは startRangeCellName = `R${i}C${j}`; のように、座標変換なしで簡単に参照することができます。

## Officeスクリプトで表（テーブル）に関数式を含むデータを追加する

便利なR1C1参照形式ですが「これで複雑な参照もできるし全部解決！」かというとそうではありません。

セルや Range に対しては setFormulaR1C1メソッドや setFormulasR1C1メソッドが用意されていますが、TableやTableColumn、TableRowに対しては用意されていません。

addColumnメソッドやaddRowメソッド、addRowsメソッドなどでR1C1参照形式の関数式を書き込もうとすると「Table addRows: 引数が正しくない、不足している、または形式が不適切です。」とエラーが出てしまいます。

一応 addRow() や addColumn() を行ってから getRange().setFormulasR1C1() を行うことはできますが、シートへの書き込み回数を増やすとパフォーマンスが悪化したり、コードの意図がわかりにくくなってしまうので避けたいところです。

### 行番号と列番号からA1参照形式を取得する

そこで、i 行 j 列目のセルのA1参照形式を取得する関数 convertToA1Format(i,j) が欲しくなってきます。

まず、ワークシート上から直接持ってきちゃう方法がこちらです。

/**
 * 行番号と列番号の組をA1参照形式に変換する
 * @param {ExcelScript.Worksheet} sheet  ワークシート。変更されないので何でもよい。
 * @param {number}                row    列番号。Zero-Indexed（ゼロはじまり、1番上の行は0）。
 * @param {number}                column 列番号。Zero-Indexed（ゼロはじまり、1番左の列は0）。
 * @returns {string}              `row`行`column`列のA1参照形式
 */
function convertToA1Format(sheet: ExcelScript.Worksheet, row: number, column: number): string {
  const cell                     = sheet.getCell(row, column);   // `row`行`column`列にあるセル
  const cellAddressWithSheetName = cell.getAddress();            // 例: 'シート名!A30'
  const cellAddress              = cellAddress.split('!').pop(); // 例: 'A30'
  return cellAddress;
}

/**
 * 行番号と列番号の組をA1参照形式に変換する
 * @param sheet  ワークシート。変更されないので何でもよい。
 * @param row    列番号。Zero-Indexed（ゼロはじまり、1番上の行は0）。
 * @param column 列番号。Zero-Indexed（ゼロはじまり、1番左の列は0）。
 * @returns `row`行`column`列のA1参照形式
 */
function convertToA1Format(sheet: ExcelScript.Worksheet, row: number, column: number) {
  return sheet.getCell(row, column).getAddress().split('!').pop();
}

分かりやすくていいと思うのですが、シートへの書き込み回数を減らす代わりに読み取り回数を増やすことになるので、あまりスマートではありませんね。

ということで、ワークシートを介さずにA1参照形式を得る方法がこちらです。

これでワークシートへの読み書き回数を減らすことができます<small>（実際にパフォーマンスが向上するかは計測していません）</small>。

/**
 * 行番号と列番号の組をA1参照形式に変換する
 * @param {number} row    行番号。One-Indexed（1番上の行は1）
 * @param {number} column 列番号。One-Indexed（1番左の列は1）
 * @returns {string}      `row`行`column`列のA1参照形式
 */
function convertToA1Format(row: number, column: number) {
  const ALPHABETS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  let columnName = '';
  do {
    column--;
    columnName = ALPHABETS[column % 26] + columnName;
    column = Math.floor(column / 26);
  } while (column)
  return `${columnName}${row}`;
}

/**
 * A1参照形式から列番号を取得する
 * @param {string} cellName `row`行`column`列のA1参照形式
 * @returns {number}        `column`（列番号）
 */
function getColumnIndexFromA1Format(cellName: string) {
  const ALPHABETS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('');
  let column = 0;
  for (const c of cellName) {
    const n = ALPHABETS.findIndex(a => a === c);
    if (n === -1) return column;
    column = column * 26 + n + 1;
  }
  return column;
}

### Officeスクリプトでテーブルに関数式を含むデータを追加する

では、実際にOfficeスクリプトで`Table`に関数式を含むデータを追加してみましょう！

まずA1:D1範囲に表を作成し、そこをヘッダー行とします。

その表に対して次のようなデータを書き込み、コスパを=RC[-1]/RC[-2]のような関数で求めてみましょう。

表．GPU性能比較表（引用：PC自由帳）

/** `A1:D5`範囲に表を作成し、ヘッダを除く最終列に`=RC[-1]/RC[-2]`相当の式を書き込む */
function main(workbook: ExcelScript.Workbook) {
  // 現在のExcelワークシートの`A1:D1`範囲にヘッダ行のみのテーブルを作成
  const table = workbook.getActiveWorksheet().addTable('A1:D1', true);
  table.getHeaderRowRange().setValues([['グラフィックボード', '価格', '性能', 'コスパ']]);

  // 関数を含むデータの用意
  const data: (string | number)[][] = [
    ['GeForce RTX 5090', 428000, 47487],
    ['GeForce RTX 5080', 198800, 32411],
    ['GeForce RTX 4090', 323800, 36340],
    ['GeForce RTX 4080', 165800, 28127]
  ].map(
    (datum, i) =>
      [...datum, `=${convertToA1Format(i+2, 3)}/${convertToA1Format(i+2, 2)}`]
  );

  // データの書き込み
  table.addRows(0, data);

  return;
}

/**
 * 行番号と列番号の組をA1参照形式に変換する
 * @param {number} row    行番号。One-Indexed（1番上の行は1）
 * @param {number} column 列番号。One-Indexed（1番左の列は1）
 * @returns {string}      `row`行`column`列のA1参照形式
 */
function convertToA1Format(row: number, column: number) {
    const ALPHABETS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    let columnName = '';
    do {
        column--;
        columnName = ALPHABETS[column % 26] + columnName;
        column = Math.floor(column / 26);
    } while (column)
    return `${columnName}${row}`;
}

## 終わりに

簡単にOfficeスクリプトを用いたセルやテーブルへの Excel関数 の書き込みと、R1C1参照形式の紹介をしました。

この記事がみなさまのOfficeスクリプトライフの一助となれば幸いです。

p.s. Table.addColumnR1C1メソッドや Table.addRowsR1C1メソッドの実装を期待しています。

1. ↩︎
2. ↩︎