スプレッドシート上で日付の過ぎた予約をGASで振り分けると、sumifs関数が正しく働きません

Question

スプレッドシート上の予約名簿の管理で、日付の過ぎたものを別シートに移動するスクリプトを下記の通り実行しましたが、そうすると、この名簿から日付、時間の条件でsumif関数で合計人数を計算している表に、正しく人数が拾わなくなります。

原因が分からずに悩んでおります。

よろしくお願い致します。

以下はスプレッドシートの名簿の見本です

function SortReservation() {

  //データー範囲
  var contents = sheet.getRange(2, 1, lastrow, 21).getValues();
  
  var data = []; //未来の予約
  var reservation = [];//過去の予約
 //順に予定を作成
  for(i = 0; i <= lastrow - 2; i++) {    
 //日程取り出し
  var today = new Date(Date.now());
  var TMonth = today.getMonth();
  var Tdate = today.getDate();
  var Reservationdate = new Date(contents[i][3]);
  var Rmonth = Reservationdate.getMonth();
  var Rodate = Reservationdate.getDate();
    
    //日程が昨日以前ならdata配列に入れる
    //来月
    if (Rmonth > TMonth) {
    data.push(contents[i]); 
    //今月で今日以降
    }else if(Rmonth == TMonth && Rodate >= Tdate ){
    data.push(contents[i]);
    //その他はreservations配列に入れる
    }else{
    reservation.push(contents[i]);  
  }    
 }
  
    //過去予約一覧に貼付
    if(reservation.length){
    sheet2.getRange(sheet2.getLastRow()+1, 1, reservation.length, 21).setValues(reservation);
    }else if(!reservation.length){
    }
    //全て削除して本日以降の予約を予約一覧に貼付
    if(data.length){  
  　sheet.deleteRows(2,lastrow);
  　sheet.getRange(2, 1, data.length, 21).setValues(data);
    }else if(!data.length){
    }
  
    //貼付け済みデータを日付/時間で並び替え
    sheet.getRange(2, 1, sheet.getLastRow(), 21).sort(5);          
    sheet.getRange(2, 1, sheet.getLastRow(), 21).sort(4);    
    sheet2.getRange(2, 1, sspast.getLastRow(), 21).sort(5);          
    Sheet2.getRange(2, 1, sspast.getLastRow(), 21).sort(4); 
}

sumifs関数は今日の日付、時間で指定(どちらも指定の値はセルから参照)
スクリプトで再入力しているため、時間や日付として認識されないのでしょうか？

GAS実行前(過去の予約もシート1にある状態)は合計は正しく計算されます。

ご教示のほどよろしくお願い致します。

Comments

[退会済みユーザー]

[2021/03/28 14:33時点の投稿内容に対する質問]
・sheet、sheet2、sspast　の各シートの具体的な構造（何を意味するシートでどこにどのようなデータが入っているのか）を記載してください。（各シートの画面キャプチャ等）
（※この続きにコメントするのではなく、質問文を編集するボタンを押して、必ず質問欄に追記してください）
・「この名簿から日付、時間の条件でsumif関数で合計人数を計算している表」とは、どのシートの、どのセルに、どのような計算式が入っているのでしょうか。これも具体的に記載してください
（これも1番目と同様に、このコメントの続きにコメントするのではなく、質問文を編集して質問欄に追記してください）

[mmyk]

ご指摘ありがとうございます。
よろしくお願い致します。

[sawa]

キャプチャ画像を見る限りは、SUMIFで集計しているシートの条件に使ってる時刻が ":"が全角になっているような。：が全角だとただの文字列として扱われると思いますが、このキャプチャの通りの時刻の表示でスクリプト実行前は集計できてましたか？

[mmyk]

コメントありがとうございます。
はい。スクリプト実行前・実行後で数字が変わってしまいます。

Answer 1

原因ですが、Google Sreadsheetで、セルに手入力した時刻値と、そのセルの値をスクリプト上で読み取り別のセルにsetValuesを使って設定した時刻値は、内部で異なる数値として評価される場合があるためと推測されます。

具体的には、下記のようになります。

・セルに手入力した時刻値を「Ａ」、
Ａのセルをスクリプト上で読み取りsetValuesを使って設定した時刻値を「Ｂ」とします。

・ＡとＢはフォーマットを同じにすれば、表示上は同じ時刻に見えます。(ミリ秒まで表示させた場合も同じ時刻になっています）

・しかし、ＡとＢをIF系関数（SUMIF関数）で比較したとき、
Ａ（Ｂ）が表す時間を24で割った値が２進法で循環小数となる場合、ＡとＢは異なる数値として評価されます。」
（おそらく手入力した時刻の内部数値と、setValuesで設定したときの数値の有効桁数が異なるためでしょう）

[検証]

1. A列の3行目から28行目までを下図の通り手入力します。(コロン含めてすべて半角で入力し、時刻値として認識されるようにしておくこと）

1. C列には、A列とB列を比較する数式を入力しておきます。

3.下記のスクリプトを実行します。
内容は、A列の値を読み取って、B列に貼り付けるというものです。

function test(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet()
  lastrow =sheet.getLastRow()
  var contents = sheet.getRange(2, 1, lastrow, 1).getValues();
  var data = [];
  data.push(contents);
  sheet
      .getRange(2, 2, lastrow, 1)
      .setValues(contents);
}

4.結果
下図のように、3:00、6:00、9:00～のように時間が３の倍数のとき、および0:45は
貼付前と後で等しくなりますが
それ以外は等しくないという結果になりました。

3÷24=0.125、6÷24=0.5、9÷24=0.375
45÷60÷24=0.03125であり、いずれも2進法で表現したとき、循環小数になりません。

一方、それ以外の時刻は、時間に換算して24で割ったとき、2進法で循環小数になります。

5.考察

以上より、時刻を手入力したときと、その時刻値をスクリプト上で読み取って単純にsetValuesで書き込んだ場合、時刻によって、IF系の関数では正しく比較できないということが推測されます。

6.対策案

・getValues/setValuesではなくコピー＆ペーストを行う。

・質問者さんがたどり着いたように、setValuesの後、書式をいったん「書式なしテキスト」にしてから「日時」に戻す。

Comments

[mmyk]

ご丁寧にありがとうございます。
難しいですが、原因が分かり非常にスッキリしました。
ありがとうございます。
ご提示頂いた方法で早速対策致します。

Answer 2

テストしてみました。
確かにsumifsが計算されなくなりました。
原因は時間の列のデータでした。

setValuesでセットすると何故だかヒットしなくなります。
対処としては以下がありました。

（対処案１）
時間の列の表示形式を「書式なしテキスト」にする
→しかし、ソートの処理で文字列としてソートされるため没

（対処案２）
setValuesの後、書式を「書式なしテキスト」にしてから「日時」に戻す

  sheet.deleteRows(2,lastrow);
  sheet.getRange(2, 1, data.length, 21).setValues(data);
  sheet.getRange('E:E').setNumberFormat('@'); // 追加コード
  sheet.getRange('E:E').setNumberFormat('H:mm'); // 追加コード

とりあえず、私の所で検証した所何故かうまくいったのですが如何でしょうか。

Comments

[mmyk]

ありがとうございます！
早速この対策法を試してみます。

Answer 3

sumif関数についてですが、添付の画像ですと、条件の一つが、集計しているシートのA3の数値と一致、となっているかと思いますが、A3セルの「9：00」の「：」が全角表記になっていませんでしょうか。予約名簿のシートでは「9:00」と半角表記のように見えます。

sumif関数を入力しているシートの、A3~A5シートの「：(全角)」を「:(半角)」に変更してみてはいかがでしょうか。

Answer 4

集計表の中に入っている数式について、シート1のD列、E列を参照しているようですが、
画像ですとシート1のD列、E列にはハイフンとコース名が入っています。

正しくはK列とL列を参照しなければならないのではないのでしょうか。

例:B3セルに入れるべき正しい数式

=SUMIFS('シート1'!$F$F,'シート1'!$K$K,$A$1,'シート1'!$L$L,$A3)

Comments

[mmyk]

ご指摘ありがとうございます。
申し訳ございません。画像が間違っておりました。

計算式は変えていないのに、上記GASを実行し、過去の予約をシート2に移すと表の計算が0になります。