スプレッドシートで空白のセルをカウントする際にデータのある結合セルを１とカウントしたい

前提・実現したいこと

スプレッドシートで予定表（お客様予約表）を管理する際に空白のセルをカウントして予約率を割り出したいです。

発生している問題・エラーメッセージ

結合セルを1とカウントしない為正確なデータが取れません。
例えば、B2からD4まで結合してCOUNTBLANKを使用してB2からD4の空白セルを出す際に0と表示したいのに2と出るなど。
データは横に並んでいて結合するセル数は作業内容により様々です。

kuma_kuma_

2020/10/09 10:22 編集

質問者様関数を作るのはNGですか？

tokumeia

2020/10/10 01:19

NGではないです。実際試そうと思いましたが何と記述して良いかわからず質問した次第です。

行動規範の内容に同意します

回答1件

ベストアンサー

スクリプトエディターでコンテナバインドスクリプトを作って、以下の内容の関数を作成。関数名は好きに変えていい。

スプレッドシートの側では、ブランクの数を表示したいセルに=myCountBlank("B2:D4", B2:D4)みたいに式を書く。第1引数の範囲(文字列で指定)のブランクの数(ただし、結合しているセルで隠れている部分は数えない)を返す。第2引数の範囲(直接指定)は、スクリプト内の処理には必要ないが、範囲内のセルが変更された際に数式を更新するために必要。

GAS
1function myCountBlank(rangeStr, dummy) {
2  const range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(rangeStr);
3  if (range === null) {
4    return 0;
5  }
6  const mergedRanges = range.getMergedRanges();
7  const values = range.getValues();
8  if (values.length === 0) {
9    return 0;
10  }
11  let blankCount = 0;
12  for (let i = 0; i < values.length; i++) {
13    blankCount += values[i].filter(x => x === '').length;
14  }
15  for (let i in mergedRanges) {
16    blankCount -= mergedRanges[i].getWidth() * mergedRanges[i].getHeight() - 1;  
17  }
18
19  return blankCount;
20}