条件表を元に一致する値を返す関数を作成する方法

Comments

1. 

   @nak435

   more_horiz

   勝手に想定した条件ですが、ユーザ関数でよければ出来ました。

   

   ユーザ関数 ryouritsu

   function ryouritsu(range) {
     var result = []
     //condition table
     const ct = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("引数").getDataRange().getValues(); 
     const ct_a = [2, 7, 12, 11];
     for (let row in range) {
       var match_row = -1
       for (let ct_row in ct) {
         var match = true;
         for (let ct_col = 0; ct_col < ct_a.length; ct_col++) {
           if (!ct[ct_row][ct_col]) continue;
           if (ct[ct_row][ct_col] != range[row][ct_a[ct_col]]) match = false
         }
         if (match) { match_row = ct_row; break; }
       }
       if (match_row > 0) result.push([ct[match_row][4]])
       else result.push(["unmatch"])
     }
     return result
   }
   

2. 

   @naokiyamamoto

   Questioner

   more_horiz

   ご回答ありがとうございます!
   コードもありがとうございます。すでにコードを作成していただいておりますがご回答させていただきます。

   V2とは、セル位置でしょうか？　行列の見出しが無いので、セルの場所が不明です。
   →2枚目の画像で料率となっている文字のしたのセルです。

   6500を返す条件は、何でしょうか？　メインシートのどのセルを、条件シートのどのセルと比較するのか、条件を提示してください。
   6500と返す条件としては、今回のデータの場合は"シェアハウス"、"大阪府"という条件になります。

   また頂いたコードですが実行してみましたが何もでませんでした。
   こちらのコードはどういった動作のさせかたを想定したものになりますでしょうか?

   GASについては初心者で教えていただけますと幸いです。

3. 

   @nak435

   more_horiz

   →2枚目の画像で料率となっている文字のしたのセルです。

   自分がアップしたスクショを見てのとおり、そのセルは F2 になっています。
   セル位置がぜんぜん違うので、期待した動作にならないです。

   行列の見出しが分かるように メインと条件表のスクショを提供してもらえれば、コードを直します。

4. 

   @naokiyamamoto

   Questioner

   more_horiz

   ありがとうございます。

   条件についてですが、
   料率
   と
   料率掛け
   を出力する2パターンがあります。

   
   

5. 

   @nak435

   more_horiz

   条件についてですが、料率 と 料率掛け を出力する2パターンがあります。

   料率掛け は、W列ですか？
   料率 と 料率掛け のどちらかですか？　両方もあり得るのですか？

   ---

   また頂いたコードですが実行してみましたが何もでませんでした。

   ちなみに、どこのセルに、どのような式を入れたのでしょうか？
   当方で、U2に入れたていた式をV2に移動しただけでも 料率 が表示されました。なぜ何も出ないのかな？と思い。

6. 

   @nak435

   more_horiz

   料率 と 料率掛け（どちらか）に対応しました。
   下記スクショのように V2 に式を入れます。

   条件表は、同じファイルの別シート（シート名「条件表」）にあることが前提。
   （前回のユーザ関数は、シート名が「引数」になっていました。何も出なかった理由かも知れません）
   条件表を上から順に検索し、空欄以外のセルがすべて一致した場合に、その行の 料率 or 料率掛け を返します。

   ryouritsu_and_kake

   function ryouritsu_and_kake(range) {
     var result = []
     //condition table
     const ct = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("条件表").getDataRange().getValues(); 
     const ct_a = [2, 7, 12, 11];
   
     for (let row in range) {
       var match_row = -1
       for (let ct_row in ct) {
         var match = true;
         for (let ct_col = 0; ct_col < ct_a.length; ct_col++) {
           if (!ct[ct_row][ct_col]) continue;
           if (ct[ct_row][ct_col] != range[row][ct_a[ct_col]]) match = false
         }
         if (match) { match_row = ct_row; break; }
       }
       if (match_row > 0) {
         if (ct[match_row][4]) result.push([ct[match_row][4],''])
         else result.push(['', ct[match_row][5]])
       } else
         result.push(["unmatch", "unmatch"])
     }
     return result
   }
   

   

   式は、=ryouritsu_and_kake(Ax:Uy) の形式で、
   式を入れたセルの行をxに指定し、y行目まで、料率 か 料率掛け をセットします。
   （例えば、A2:U1000 のように、一つの式で何行にも対応しますが、あまり多い行数を指定すると、再計算が遅くなります）

   スクショでは、式=ryouritsu_and_kake(A2:U4)をV2セルに入れているので、V2:W4 をセットします。
   1行づつ式を入れるならば、
   　V2の式=ryouritsu_and_kake(A2:U2)
   　V3の式=ryouritsu_and_kake(A3:U3) です。

   条件表に一致しない場合は、unmatchをセットしますが、これは消せません。
   もし消したい場合は、式自体を消すこと。

   また、料率 だけ、 料率掛け だけには、対応していません。

7. 

   @naokiyamamoto

   Questioner

   more_horiz

   コードを修正していただきありがとうございました。

   ちなみに、どこのセルに、どのような式を入れたのでしょうか？
   こちら式をセルに入れていませんでした。

   料率掛け は、W列ですか？
   料率 と 料率掛け のどちらかですか？　両方もあり得るのですか？
   料率掛けはW列です。
   料率もしくは料率掛けのどちらかだけになります!

   改めて頂いたコードを貼り付けてみましたが画像のようにほぼすべてがunmatchになってしまいました･･･
   何度もすみません。

8. 

   @nak435

   more_horiz

   見る限りでは、問題がないように思え原因が分かりません。
   すみませんが、実物のスプレッドシートを見ないとこれ以上は調べられないです。
   メアドnak435com@gmail.comに共有していただけるならば、原因を特定できると思います。

9. 

   @naokiyamamoto

   Questioner

   more_horiz

   ありがとうございます。

   共有させていただきました。
   他にも何件か追加してみたところ一部は反応してるみたいです。

10. 

    @nak435

    more_horiz

    共有させていただきました。

    条件表の一見空白に見えるセルに「半角スペース」が入っていたことが原因でした。
    「半角スペース」だけのセルは比較対象外とする処理を追加しました。
    共有していただいたファイルのスクリプト（ユーザ関数）を修正してあります。

11. 

    @naokiyamamoto

    Questioner

    more_horiz

    ありがとうございます。
    初歩的なミスでした。申し訳ありません。

    条件表についてなのですが日付も条件として加えたく条件表を変更しました。
    スクリプトがエラーになってしまい、素人ながらGPTを使いながらコードを修正してみましたが思うように動作をしません。

    コード2 というのが修正してみたコードです。
    大変お手数ですが今一度ご確認いただけますでしょうか?

12. 

    @nak435

    more_horiz

    条件表の列位置に依存しているので、日付列を加えると、当然、そのままでは動作しません。

    関数を日付条件にも対応しました。コード3です。

    なお、条件表の上から順に条件一致を見て行くので、
    日付条件無しは、日付条件有りより、後ろに置く必要があります（先に日付無しで合致させないために）。

    また、条件表の日付条件は、開始日だけ、終了日だけ にも対応します。

    追伸；
    　当方で見ているときは、そちらでの更新は控えてください。

13. 

    @naokiyamamoto

    Questioner

    more_horiz

    ありがとうございます。

    無事解決しました。

14. 

    @naokiyamamoto

    Questioner

    more_horiz

    何度も恐れ入ります。

    条件表にて案件状況が施工2回目以降の場合は0と出力するという動作が動作しておらずご確認いただけますでしょうか?

    function ryouritsu_and_kake(range) {
      var result = []
      //condition table
      const ct = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("条件表").getDataRange().getValues(); 
        //条件表col  0  1  2  3   4   5
      const ct_a = [1, 1, 2, 7, 12, 11];
    
    console.log(range)
    
      for (let row in range) {
        var match_row = -1
        for (let ct_row in ct) {
          if (isDate(ct[ct_row][0]) && ct[ct_row][0] > toDate(range[row][ct_a[0]])) continue
          if (isDate(ct[ct_row][1]) && ct[ct_row][1] < toDate(range[row][ct_a[1]])) continue
          var match = true;
          for (let ct_col = 2; ct_col < ct_a.length; ct_col++) {
            if (!ct[ct_row][ct_col] || ct[ct_row][ct_col].trim().length == 0) continue;
            if (ct[ct_row][ct_col] != range[row][ct_a[ct_col]]) { match = false }
          }
          if (match) { match_row = ct_row; break; }
        }
        if (match_row > 0) {
          if (ct[match_row][6]) result.push([ct[match_row][6],''])
          else result.push(['', ct[match_row][7]])
        } else
          result.push(["", ""])
      }
      return result
    }
    
    function isDate(d) {
      return Object.prototype.toString.call(d) == "[object Date]"
    }
    
    function toDate(d) {
      return Utilities.parseDate(d, 'JST', 'yyyy/MM/dd')
    }
    

15. 

    @nak435

    more_horiz

    手元に情報が残ってないので、再度共有していただけませんか？

    すみませんが、実物のスプレッドシートを見ないとこれ以上は調べられないです。
    メアドnak435com@gmail.comに共有していただけるならば、原因を特定できると思います。

16. 

    @naokiyamamoto

    Questioner

    more_horiz

    すみません。
    コメント返信ができておりませんでした。

    共有させていただきましたのでお手漉きの際にご確認お願いいたします。

17. 

    @nak435

    more_horiz

    条件表　；施工完了2回訪問以降
    案件管理；施工完了2回目以降
    と、一致しないためです。

    次のとおりスクリプトも変更が必要でした。共有していただいたファイルは修正済みです。

    function ryouritsu_and_kake(range) {
      var result = []
      //condition table
      const ct = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("条件表").getDataRange().getValues(); 
        //条件表col  0  1  2  3   4   5
      const ct_a = [1, 1, 2, 7, 12, 11];
    
    //console.log(range)
    
      for (let row in range) {
        var match_row = -1
        for (let ct_row in ct) {
          if (isDate(ct[ct_row][0]) && ct[ct_row][0] > toDate(range[row][ct_a[0]])) continue
          if (isDate(ct[ct_row][1]) && ct[ct_row][1] < toDate(range[row][ct_a[1]])) continue
          var match = true;
          for (let ct_col = 2; ct_col < ct_a.length; ct_col++) {
            if (!ct[ct_row][ct_col] || ct[ct_row][ct_col].trim().length == 0) continue;
            if (ct[ct_row][ct_col] != range[row][ct_a[ct_col]]) { match = false }
          }
          if (match) { match_row = ct_row; break; }
        }
        if (match_row > 0) {
          if (String(ct[match_row][6]).trim().length > 0) result.push([ct[match_row][6],''])
          else result.push(['', ct[match_row][7]])
        } else
          result.push(["", ""])
      }
      return result
    }
    
    function isDate(d) {
      return Object.prototype.toString.call(d) == "[object Date]"
    }
    
    function toDate(d) {
      return Utilities.parseDate(d, 'JST', 'yyyy/MM/dd')
    }