Zenn
レスキューナウテックブログPublicationへの投稿
⛏️

スプレッドシートにGASで相対参照と絶対参照が混在する VLOOKUP関数を埋め込む

ryana
2022/12/05に公開
GAS
#
スプレッドシート
tech

背景

社内でGoogle Workspaceを使うようになってから、Excelに随時書き溜めているデータを、コピペ程度の簡単な作業で作業者がスプレッドシートで集計できるようにしたいという要望があり、集計用のファイルと手作業でExcelデータをコピペする作業用ファイルを作成することになりました。
コピペするシートには集計で使う相対参照のVLOOKUP関数をデータの各行へセットする必要があったためGASを書くことにしたのですが、今までこの類はExcel VBAでしか書いたことがなく少し手間取ってしまったので、忘れないためにサンプルを作って書き残すことにしました。

スプレッドシートの構成

＜1シート目：在庫一覧シート＞
各支店の在庫の一覧です。
作業者が任意のタイミングでA列～D列に在庫データをコピペした後、
分類表シートから集計用の分類項目を引っ張ってくるためのVLOOKUPをE列へ埋め込み、手作業で編集した時のデータ行の増減に合わせてVLOOKUPが入る行も増減させる
部分を自動化しようとしています。

在庫一覧シート


＜2シート目：分類表シート＞
各商品の分類項目を記載した表です。

分類表シート

入力予定の式

GASを書き始める前に、埋め込む予定の式をシートへ手入力してエラーが出ないか確認します。
在庫一覧シートのセル：E2へVLOOKUPを入れてみます。キーとなるセルは相対参照、参照する分類表シートのセル範囲は絶対参照という、よくあるパターンの式です。

=VLOOKUP(C2,'分類表'!$A$2:$B$8,2,0)


VLOOKUP　手入力の場合

GAS

実現したいことを確認できたので、GASを書いていきます。

処理対象のファイルを取得

スプレッドシートのidをここに入れるの場所には、スプレッドシートのURLにあるspreadsheets/d/と/edit#の間の文字列を入力します。

（functionの前）
　- 処理対象のスプレッドシート（ファイル）のIDを変数に格納
　- 処理対象のスプレッドシート（ファイル）を取得

const ssid = 'スプレッドシートのidをここに入れる';
const zaikoApp = SpreadsheetApp.openById(ssid);

処理対象シートを取得

（以下、function内）
　- 処理対象のシート名を変数に格納
　- 処理対象のシートを取得

function checkZaikoSheet(){

  const shName = '在庫一覧';
  const zaikoSh = zaikoApp.getSheetByName(shName);

手作業部分と自動入力部分の最終行を取得

VLOOKUPの式を消去するか or 埋めるかを判定する方法として、手作業でコピペするセル範囲の列の最終行と、式を自動入力する列の最終行の差をチェックすることにしました。
そこで在庫一覧シートの手作業部分（A列～D列）の最終行と、VLOOKUPの自動入力部分（E列）の最終行を取得します。

  • 手作業部分の最終行の取得
    A列～D列であればどこでもいいのですが、とりあえずA列（1）で取得します。

- 以下で取得した手作業部分の最終行を変数に格納する
　　- 在庫一覧シートのA列の一番下のセルを取得して、
　　- そこからデータの入っている上のセルに飛んで、（Ctrl + ↑と同じ動き）
　　- そのセルの行番号を取得

  const zaikoLastRow = zaikoSh
    .getRange(zaikoSh.getMaxRows(), 1)
    .getNextDataCell(SpreadsheetApp.Direction.UP)
    .getRow();
  • 自動入力部分の最終行の取得
    手作業部分の最終行の取得と同様ですが、こちらはE列（5）を指定しています。
  const zaikoLastRow = zaikoSh
    .getRange(zaikoSh.getMaxRows(), 5)
    .getNextDataCell(SpreadsheetApp.Direction.UP)
    .getRow();

それぞれの最終行が取得できたので、次に差分をチェックします。

手作業部分 ＜ 自動入力部分の場合（式を消す）

VLOOKUPは空白セルを参照するとエラーになってしまうので、このパターンの場合は式を消去することにしました。

- 手作業部分の最終行 ＜ 自動入力部分の最終行　かつ　手作業部分の最終行が項目行以上の場合
　　- 手作業部分の最終行 － 自動入力部分の最終行　の差分を取得
　　- 対象のセル範囲を取得して、
　　- その範囲の入力内容を消去

  if(zaikoLastRow < zaikoFormulaRow && zaikoLastRow >= 1) {

    let diff = zaikoFormulaRow - zaikoLastRow;
    zaikoSh
      .getRange(zaikoLastRow + 1,5,diff,1)
      .clearContent();

  };

自動入力部分 ＜ 手作業部分の場合（式を埋める）

増えていくデータをコピペするので、確率としてはこのパターンが一番多いと思われます。
Excel VBAで書くとすればこんな感じで書くことが多いのですが・・
（これまでに登場した変数の値は取得済という前提で）

Excel VBA
Dim i As Long, fx As Variant
For i = zaikoFormulaRow + 1 To zaikoLastRow

    If Cells(i, 5).Formula = "" Then
    
        fx = "=VLOOKUP($C" & i & ",分類表!$A$2:$B$8,2,0)"
        Cells(i, 5).Formula = fx
    
    Else
    End If

Next i

GASではFor文を回さなくても簡潔に書けそうだったので使わないことにしました。
が、ここで想定以上に時間がかかってしまい、、試行錯誤した経緯をNG事例も含めて4パターン書き出してみます。

＜埋め込み失敗事例＞

  • NGその1（setFormulaを使う）
    Excel VBAで書くイメージのままGASで以下のコードを書いて実行してみたら、処理は完了したのですがシート上でエラーになってしまいました。
× GAS：NGその1
if(zaikoLastRow > zaikoFormulaRow && zaikoFormulaRow >= 1) {

    const fx = 'VLOOKUP(C" + zaikoFormulaRow + 1 + ",分類表!$A$2:$B$8,2,0)';
    let diff = zaikoLastRow - zaikoFormulaRow;
    zaikoSh
      .getRange(zaikoFormulaRow + 1,5,diff,1)
      .setFormula(fx);

  };

相対参照するセルの行番号を計算させたくてzaikoFormulaRow + 1を入れたのですが、" + zaikoFormulaRow + 1 + "が文字列として取り込まれてしまっています。

× セルに埋め込まれた式：NGその1 結果
=VLOOKUP(C" + zaikoFormulaRow + 1 + ",分類表!$A$2:$B$8,2,0)


  • NGその2（setFormulaR1C1を使う）
    次に、setFormulaではなく相対参照の式をセットしてくれるsetFormulaR1C1に変更してみましたが、「NGその1」と同じく処理は完了したもののシート上ではエラーになりました。
× GAS：NGその2
if(zaikoLastRow > zaikoFormulaRow && zaikoFormulaRow >= 1) {

  const fx = 'VLOOKUP(R[0]C[-2],分類表!$A$2:$B$8,2,0)';
  let diff = zaikoLastRow - zaikoFormulaRow;
  zaikoSh
    .getRange(zaikoFormulaRow + 1,5,diff,1)
    .setFormulaR1C1(fx);

};

キーのセルをR1C1形式で指定したものの、文字列として取り込まれてしまいました。これもVLOOKUPとして認識されていないようです。

× セルに埋め込まれた式：NGその2 結果
=VLOOKUP(R[0]C[-2],分類表!$A$2:$B$8,2,0)

＜埋め込み成功事例＞

  • setFormulaR1C1 + 参照先の範囲指定でIMPORTRANGE関数を使う
    相対参照と絶対参照が混在しているのが良くないのかな？と思ったので、相対参照のセルはこのままにして絶対参照のセル範囲を文字列として入れる方向で考え、参照先の範囲を文字列で指定できるスプレッドシート関数のIMPORTRANGEを使ってみました。
○ GAS：IMPORTRANGEバージョン
if(zaikoLastRow > zaikoFormulaRow && zaikoFormulaRow >= 1) {

    const fx = 'VLOOKUP(R[0]C[-2],IMPORTRANGE("スプレッドシートのidをここに入れる","分類表!$A$2:$B$8"),2,0)';
    let diff = zaikoLastRow - zaikoFormulaRow;
    zaikoSh
      .getRange(zaikoFormulaRow + 1,5,diff,1)
      .setFormulaR1C1(fx);

  };

今回はキーのセルが相対参照でセットされていて、計算結果でも分類項目を表示できています。

○ セルに埋め込まれた式：IMPORTRANGEバージョン 結果
=VLOOKUP(C2,IMPORTRANGE("スプレッドシートのid","分類表!$A$2:$B$8"),2,0)

ただ、、サンプル程度のボリュームであればすぐに計算結果が表示されるのですが、実際のデータは800行近くあったせいか、GASエディタで処理を実行して「実行完了」の表示が出たあとシート上で計算結果が表示されるまで6～7秒かかってしまいました。

  • setFormulaR1C1 + 参照先の範囲指定でINDIRECT関数を使う
    VLOOKUPの計算結果をシートにすんなり表示できる方法はないか・・と考えていて、ふと思い出したのがExcelでよく使っているINDIRECTです。
    INDIRECTの引数は参照文字列なので、こちらも絶対参照のセル範囲を文字列にして入れ込むことができます。同一ファイル内のシートなので、シート名を指定すれば識別できるのでは？と思い、さっそく試してみました。

- 自動入力部分の最終行 ＜ 手作業部分の最終行　かつ　手作業部分の最終行が項目行以上の場合
　　- 自動入力部分の最終行 － 手作業部分の最終行　の差分を取得
　　- 対象のセル範囲を取得して、
　　- その範囲にVLOOKUPの式を埋め込む

◎ GAS：INDIRECTバージョン
if(zaikoLastRow > zaikoFormulaRow && zaikoFormulaRow >= 1) {

  const fx = 'VLOOKUP(R[0]C[-2],INDIRECT("分類表!$A$2:$B$8"),2,0)'
  let diff = zaikoLastRow - zaikoFormulaRow;
  zaikoSh
    .getRange(zaikoFormulaRow + 1,5,diff,1)
    .setFormulaR1C1(fx);

};

エディタで「実行完了」が表示されてすぐスプレッドシートを見に行くと、すでに計算結果が正しく反映されていました！シート上でのエラーもありません。採用決定です。

◎ セルに埋め込まれた式：INDIRECTバージョン 結果
=VLOOKUP(C2,INDIRECT("分類表!$A$2:$B$8"),2,0)


＜番外編：INDIRECTの使い方＞

Excel関数ですがスプレッドシートでも使える関数で、文字列をセル（範囲）に変換して参照できるようにしてくれます。私の場合は集計表を作成する時に使うことが多いです。
使用例をひとつご紹介します。


■使用例
複数の仕入先（A社・B社・C社）の仕入単価のテーブルが各シートに記載してあって、別のシートに各仕入先の仕入単価を抽出する場合
（仕入単価の表の行数は仕入先ごとに差異があってもOK。列の構成は共通にしておく）

  1. 仕入単価の表（項目の下）を選択して［データ］-［名前付き範囲］をクリック



    2.付けたい名前を入力して［完了］をクリック（例：A社　⇒B社・C社も同様に名前を付ける）


  2. 仕入単価を抽出するシートで以下の式を入力
=VLOOKUP(B2,INDIRECT(A2),2,0)




4. 3～4行目に2行目の式をコピー

VLOOKUPで参照するセル範囲は、A列に入力された値をINDIRECTで名前付きセル範囲に変換して指定しているので、同じ商品名でも仕入先ごとに異なる仕入単価を正しく抽出することができます。
仕入先ごとにいちいちIFで分岐する必要が無く、式がスッキリして可読性が高くなるのが最大のメリットです。ただし、名前をつけるセル範囲が多すぎると重くなるので気をつけましょう。

GASの全体像

さて、本題に戻ります。ここまで書いてきたGASをエディタで見るとこんな感じになります。

const ssid = 'スプレッドシートのidをここに入れる';
const zaikoApp = SpreadsheetApp.openById(ssid);


function checkZaikoSheet(){

  const shName = '在庫一覧';
  const zaikoSh = zaikoApp.getSheetByName(shName);

  const zaikoLastRow = zaikoSh
    .getRange(zaikoSh.getMaxRows(), 1)
    .getNextDataCell(SpreadsheetApp.Direction.UP)
    .getRow();

  
  const zaikoFormulaRow = zaikoSh
    .getRange(zaikoSh.getMaxRows(), 5)
    .getNextDataCell(SpreadsheetApp.Direction.UP)
    .getRow();


  if(zaikoLastRow < zaikoFormulaRow && zaikoLastRow >= 1) {

    let diff = zaikoFormulaRow - zaikoLastRow;
    zaikoSh
      .getRange(zaikoLastRow + 1,5,diff,1)
      .clearContent();

  };

  
  if(zaikoLastRow > zaikoFormulaRow && zaikoFormulaRow >= 1) {

    const fx = 'VLOOKUP(R[0]C[-2],INDIRECT("分類表!$A$2:$B$8"),2,0)'
    let diff = zaikoLastRow - zaikoFormulaRow;
    zaikoSh
      .getRange(zaikoFormulaRow + 1,5,diff,1)
      .setFormulaR1C1(fx);

  };


}

トリガーをセットする

今回はスプレッドシートへデータをコピペしたタイミングで処理を実行したいので、
イベントのソース：スプレッドシートから
イベントの種類　：編集時
でセットしました。

最後に

今回は絶対参照のセル範囲が同一スプレッドシート内の別シートだったので、INDIRECTをVLOOKUPにネストすることで埋め込むことができました。INDIRECTはとても便利なので、スプレッドシートやExcelを日常的に使う方は覚えておいて損はないかなと個人的には思います。
これからGASを使ってみようかな、という方のお役に立てれば嬉しいです。

レスキューナウテックブログ
レスキューナウテックブログPublication

日本で唯一の危機管理情報を専門に取り扱う防災Techのスタートアップ、(株)レスキューナウです。当社で活躍するエンジニアの技術ブログを中心に公開していきます。

Discussion