2022/05/07に更新

【GASで時短】数式エラーが出ているセルを通知する

スプレッドシート小技

数式でエラーが出ているセルを探してくれるGoogle Apps Scriptです。巨大なシートだとエラーに気づかないこともある・・・?ということで作ってみました。

こんな悩みを解決します


  • データソースが変わったので数式にエラーが出てないか確認したい・・・けど目視はツラい

用意するもの


特にありません。今お使いのスプレッドシートに組み込めます。

このスクリプトで出来ること


  • 数式エラーが出ているセル番地を検索、アラート形式で教えてくれます。

使い方


1.スクリプトの実行

サンプルとして、エラーを出しまくったシートを用意しました。
参考までに、各エラーの出し方は以下のとおりです。

  • #N/A : VLOOKUPで検索値が見つからない
  • #ERROR! : 存在しないセル番地を指定したSUM()
  • #DIV/0! : ゼロで割り算
  • #REF! : 存在しないシート名を参照

上のメニューにある「スクリプト実行」から「数式エラーが出ているセルを調べる」を実行します。

2.結果の確認

ポップアップウィンドウで、エラーが出ているセル番地が表示されます。
問題なさそうです。
今回のサンプルはデータ量が少ないので目視で十分ですが、画面外のエラーは本アプリで見つけやすくなる・・はず?

4.注意点

「数式エラーが出ているかどうかを判定」できるようなメソッドは見つけられなかったため、セルに入っている文字列(#ERROR!等)を判定しています。
数式エラーと見せかけた単なる文字列も拾ってしまいますのでご注意ください。

スクリプト


/**
 * このスクリプトの説明、使い方はこちら。
 * https://myfunc.jp/items/00081/index.html
 */

function onOpen() {
  // スプレッドシートを開いたときに実行される関数

  // UIの取得
  const ui = SpreadsheetApp.getUi()

  // メニューの表示名
  const menu = ui.createMenu('スクリプト実行');

  // メニューに追加するボタン
  menu.addItem('数式エラーが出ているセルを調べる', 'report_formula_errors');

  // メニューを画面に追加する
  menu.addToUi();
}

function report_formula_errors() {

  // アラートを表示するため、UIオブジェクトを取得
  const ui = SpreadsheetApp.getUi();

  // 開いているスプレッドシートオブジェクトを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // 開いているシートのオブジェクトを取得
  const sheet = ss.getActiveSheet();

  // アクティブシートのデータが存在する範囲を取得
  const range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());

  // セルに入っている値をすべて取得
  const values = range.getValues();

  // 見つかったエラーを格納する配列
  const result = [];

  // 値をループしてチェックする
  for (let i = 0; i < values.length; i++) { // 行のループ

    for (let j = 0; j < values[i].length; j++) { // 列のループ

      // 数式エラー発生時の文字列とマッチングする
      if (String(values[i][j]).match(/^(#N\/A|#ERROR!|#DIV\/0!|#REF!)$/)) {

        // マッチした場合は該当セルの番地をA1形式で取得する
        let cell = sheet.getRange(i + 1, j + 1).getA1Notation();

        // セル番地を結果格納用配列に追加する
        result.push(cell);

      }
    }
  }

  // エラーの有無に応じてメッセージを出し分け
  if (result.length > 0) {
    ui.alert('以下のセルでエラーが出ています。\n\n' + result.join('\n'));
  } else {
    ui.alert('このシート内で、数式エラーが出ているセルは見つかりませんでした。')
  }

}

関連するアプリ


【GASで便利に】シート内のすべての数式を表示する

シートに入力されている全ての数式を、別シートに出力するGoogle Apps Scriptです。数式のセル参照が連鎖していて、確認が大変だったので作ってみました。

【GASで便利に】数式が入っているセルに色を付ける

シート内の数式が入っているセルを探して、色を付けてくれるGoogle Apps Scriptです。他の人から引き継いだシートだと、数式がどこにあるのかわかりにくいので作ってみました。

【GASで時短】シート内の数式だけを削除する

セルに入力されている数式をすべて消し去るGoogle Apps Scriptです。数式を多用してデータを作ったが、再計算することはないので数値が直接入力されている状態にしたい時・・・などに。

【GASで時短】2つのシートの差分をチェックする

2つのシートを比較して、値や数式に差分があるかどうか教えてくれるGoogle Apps Scriptです。スプレッドシートでもdiffがしたい!という時にどうぞ。

【GASで時短】同じ背景色のセルを選択状態にする

選択したセルと同じ背景色のセルを選択状態にするGoogle Apps Scriptです。細かく色分けされたセルの色を変えたくなったときにどうぞ。

【GASで時短】同じ文字色のセルを選択状態にする

文字色が同じセルを選択状態にしてくれるGoogle Apps Scriptです。特定の文字色をまとめて変えたくなったときにどうぞ。

タグ一覧

最新のアプリ

人気のアプリ

myfunc.jp