【pleasanter】計算式（拡張）活用サンプル④～フィルタ項目として利用する～

１．はじめに

前回は、「計算式（拡張）を使ってタイトルを自由自在に」をやりました。

今回は、フィルタとして使いたい項目を計算式（拡張）を使って自動作成、というのをやってみたいと思います。
適切で便利に使えるフィルタ項目を設置していると、「お！便利！」と、サイトの使い勝手がぐんと向上します。絞り込みして眺めてみたり、集計に使用してみたり、絞り込みしてエクスポートしたいときなんかにも、「あってよかった！」という適切なフィルタ項目があるととても便利です。
で、計算式（拡張）を使うことにより、入力した項目から別項目に一部を取り出したり出来るので、これれはいい！と思ってます。
で、こんな使い方どうかなーというサンプルをいくつか記載してみました。

基本的な使い方等は以下の記事、または公式マニュアルをご参照ください。関数の一覧などは公式マニュアルをご参照ください。
【pleasanter】計算式（規定・拡張）についてまとめてみた

公式マニュアル
テーブルの管理：計算式（既定）
テーブルの管理：計算式（拡張）
計算式（拡張）の関数一覧
計算式（拡張）の関数で使用する論理式

計算式の構文等の詳しい説明は割愛していますので、公式マニュアルをご参照ください。

以下、基本的に「計算式」タブで「計算方法」は[拡張]を利用、「エラーを出力する」はオフ、「表示名を使用しない」はオフ でやっています。

長くなってしまった上に脱線多めです。２の（５）はほとんど遊んじゃっています。適当に読み飛ばしていただければと思います。

２．フィルタ項目として利用する

（１）日付項目から「年」や「月」、「曜日」を別項目に出力する

日付項目のフィルタは「範囲指定」とすると、カレンダーから範囲を指定できます。「規定」とした場合、「年度」や「半期」「月」等の設定をすることにより、結構便利にフィルタとして利用することができます。

用意されていないオリジナルな区分で絞り込みしたいとき、計算式で日付項目から算出して別項目を作ってしまうといいかも！と思いました。

例１）日付項目の「年」で項目を作成

計算式（拡張）

使用する計算式：$IF, $NOT, $ISBLANK, $YEAR, $MONTH

日付項目 [発送日] の日付から「年」を取り出して、[発送日_年] に 年 の値を出力しています。
（発送日が空欄の場合に変な値が出るのを防ぐため$IF等を入れていますが、計算元の日付が空欄になることがなければ $YEAR(発送日) だけで大丈夫です)

対象（出力先項目）：発送日_年

$IF($NOT($ISBLANK(発送日)),$YEAR(発送日),"")

フィルタのイメージ

[発送日_年] を数値項目として、フィルタを「範囲指定」にした場合のイメージ

集計項目を設定しておくと、絞り込みした年の売上金額とかパッと見ることができて便利。

計算結果「年」を出力する項目は数値項目でも分類項目でも、どちらでもOK。範囲指定で絞り込みをしたいニーズがありそうなときは数値項目がおすすめです。また、一覧や編集画面で [発送日_年] を表示しなくてもフィルタ項目として使用することができます。

ちなみに、「発送日」から年度を取り出したいときの例は以下です（3月決算の場合）

$IF($NOT($ISBLANK(発送日)),$IF($MONTH(発送日)<=3, $YEAR(発送日) - 1, $YEAR(発送日)), "")

例２）日付項目の「月」で項目を作成

エディタの設定

出力項目 [発送日_月] は分類項目として、以下の選択肢を用意しておきました。

計算式（拡張）

使用する計算式：$IF, $NOT, $ISBLANK, $MONTH

対象（出力先項目）に [発送日_月] を指定しています。

$IF($NOT($ISBLANK(発送日)),$MONTH(発送日),"")

一覧でのフィルタのイメージ

はたして「月」だけ出したい要望があるかどうかはさておきですが。

例３）日付項目の「曜日」で項目を作成

「発送日」の日付から「曜日」、月～金を [平日] 、土日を [休日] とする「区分」項目を作成してみます。

エディタの設定

分類項目「曜日」「区分」には事前にエディタで選択肢を設定しておきます。
※月曜日を「１」としているのは、後述の計算式で[週の基準]=2としているためです。選択肢の値と表示値の設定は 指定した [週の基準] とリンクするように行います。

計算式（拡張）

使用する関数：$IF, $WEEKDAY

$WEEKDAY 関数を使います。
日付に対する曜日が返されます。1～7（または 0～6）の数値で返されます。
今回は土日を後ろに寄せたかったので、マニュアルを参照し、[週の基準]=2 を使用することとしました。[週の基準]=2 は月曜1、から始まり日曜が7となります。

以下の２つの計算式を設定します。

対象（出力先項目）：曜日

$WEEKDAY(発送日,2)

対象（出力先項目）：区分

$IF($WEEKDAY(発送日, 2) <= 5, "平日", "休日")

一覧でのイメージ

休日はなにが売れてるかなー、とか眺めてみたり。

金曜日はどの商品が売れてるかなーとか、集計でみてみたり

曜日ごとに集計を出してみたり。。。

あんまり曜日で分析はしないか。。。我ながら、発想が貧困。。。

例４）時間帯、午前・午後の算出

「入店日時」から「時間帯」「AM／PM」を算出。「時間帯」「AM/PM」は分類項目で事前に選択肢を用意しておきます。
選択肢を用意していない場合、分類項目であっても文字列検索になってしまうので、フィルタとして使いたい場合はエディタの設定の方で選択肢を用意しておくほうがお勧めです。

計算式（拡張）

使用する関数：$HOUR , $IF

以下の計算式を設定

対象（出力先項目）：時間帯（分類項目）

$HOUR(入店日時)

対象（出力先項目）：ＡＭ／ＰＭ（分類項目）

$IF($HOUR(入店日時)<12, "AM", "PM")

イメージ

（２）年齢から●歳台を出して、フィルタに利用する

入力項目「年齢」から「●歳台」を計算して項目を作成します。10歳区切りで数値項目と分類項目でやってみました。

計算式（拡張）

使用する計算式：$IF, $TRUNC, $ROUNDDOWN

対象（出力先）：年代_数値

$IF(年齢>=100,100,$TRUNC(年齢,-1))

対象（出力先）：年代_分類

$IF(年齢>=100,100,$TRUNC(年齢,-1))

$TRUNC の部分は $ROUNDDOWN(年齢,-1) でも同じです。

エディタの設定

年代_数値：数値項目。単位=歳台（←やってもやらなくてもOK)

年代_分類：分類項目。選択肢は以下の通りとしました。

フィルタの設定

年代_数値：既定。最小0、最大100、ステップ10（「規定」ではなく「範囲指定」でもOK）

年代_分類：デフォルトのまま

一覧画面のイメージ

10歳区切りではないオリジナルな区切り方も自由自在です。

計算式（拡張）

使用する計算式：$IFS

対象（出力先）：年代_分類

$IFS(年齢<15,"15歳未満",年齢<30,"15～29歳",年齢<65,"30～64歳",年齢<100,"65～99歳",TRUE,"100歳以上")

エディタの設定

年代_分類：選択肢を以下のように設定

一覧画面のイメージ

エディタの設定で計算式の結果と連動する選択肢を用意しておくことにより、フィルタが効果的に利用できるようになります。選択肢を用意していない自由記述方式の場合、文字列検索となり、せっかく分岐した出力結果がフィルタで活かし切れず、あまり好きではありません。
さらに 1,15歳未満／2,15～29歳… のように値と表示名の組み合わせにしておくと、ソートが意図したとおりの結果に導けます。上記のイメージは値と表示名を兼用しているため、「100歳以上」が昇順ソートで一番上になっています。

（３）説明項目の入力有無

説明項目に入力があるレコードを絞り込み表示したい、なんてことはありませんか？あるような気がしませんか？なので、やってみました。

エディタの設定

説明A：デフォルトのまま

チェックA：表示名＝入力有

フィルタの設定

「チェックA」を設定

計算式（拡張）

使用する計算式：$NOT, $ISBLANK

対象（出力先）：入力有（チェック項目）

$NOT($ISBLANK(説明A))

一覧画面のイメージ

「入力有」をオンでフィルタすると、説明Aに入力のあるレコードだけが絞り込みされます。
フィルタの設定でコントロール種類を「オンとオフ」にすると、オンの場合とオフの場合、両方の絞り込みができるようになります。「入力有」をオフでフィルタすると、説明Aに入力のないレコードだけが絞り込みされます。
※改行だけや空白文字列の入力がある場合も「入力有」になってしまいます。空白文字列の入力も除外したい場合は $NOT($ISBLANK($TRIM(説明A))) とします。改行を取り除く方法は関数ではわかりませんでした。

[フィルタ]タブ＞チェック項目を選択＞[詳細設定]>[コントロール種別]

（４）平均を出して平均点でランク分け

ベタですが。平均点を出して、平均点でランク分けして、っていうやつ。

エディタの設定

数値A：表示名＝国語

数値B：表示名＝数学

数値C：表示名＝英語

数値D：表示名＝平均、小数点以下桁数＝１、端数処理種類＝四捨五入

分類A：表示名＝ランク、選択肢は下図のように設定

計算式（拡張）

使用する計算式：$AVERAGE, $IFS

対象（出力先）：平均（数値項目）

$AVERAGE(国語, 数学, 英語)

対象（出力先）：ランク（分類項目）

$IFS($AVERAGE(国語,算数,英語)>=80,"A", $AVERAGE(国語,算数,英語)>=60,"B", $AVERAGE(国語,算数,英語)>=40,"D", $AVERAGE(国語,算数,英語)>=0, "E",TRUE, "")

または

$IFS(平均>=80,"A", 平均>=60,"B", 平均>=40,"D", 平均>=0, "E",TRUE, "")

※計算式で出力した「平均」を「ランク」の計算式に使用しても、計算は正しく行われました。
ただし、IDの順番に計算が行われるようなので（たぶん）、引数に使用する「平均」の計算式のIDが「ランク」の計算式のIDより小さい数であることが必要です。順番を逆にしてもエラーにはなりませんでしたが、「更新」ボタンを2回押さないと「ランク」の計算は正しい値にならない場合がありました。

私は慎重派なので、$IFSの中で$AVERAGEを使用する方法を使うと思います。

一覧画面のイメージ

数値項目の「平均」は[範囲指定]にしても、[規定]で10刻みとかにしても、ニーズに合わせて設定するとよいですね。

端数処理について

「平均」の端数処理について、今回は計算式ではなくエディタの設定の方で「四捨五入、小数点以下1桁」と指定しています。

計算式でやる場合は、$ROUND,$ROUNDDOWN,$ROUNDUP を使用して、端数のコントロールを行います。

$ROUND($AVERAGE(国語, 数学, 英語), 1)

このようにしたほうが、端数処理をどうするのかも計算式内でいっぺんに明示することができるので、わかりやすいかもしれませんが、どちらかというとエディタの設定ほうが細かいコントロールはしやすいような気がしています。（以下余談にて）

（５）計算式（拡張）の引数としている数値項目に「Null値を許容」としていても0が入ってしまう

2025年2月時点の、うちのPCの環境においてですが、計算式（拡張）で引数としている数値項目について、「Null許容」としていても、画面表示の際に「0」が入ってしまい、Nullにしたつもりが「0」として計算されてしまう、という状況が発生しました。

意図があってNull値はNull値で計算したい場合(空欄の場合は計算に入れない等)があるかもしれません。

あまり需要はないと思いますが、ExcelだとAVERAGEは空欄は無視して計算するため、そのノリでやろうとしたらダメだったので、じゃあと思って計算式（拡張）ではない方法でやってみました。

①サーバースクリプトでやってみた

スクリプトではなくサーバースクリプトを使おうと思ったのは、計算式（拡張）がサーバースクリプト寄りだな、と思ったからです。自動ポストバックにしているときの挙動など、計算式（拡張）と同じ動きだなあと思いました。

例：（４）と同様に、「国語」「算数」「英語」の平均点を算出する。項目値が空欄の場合は無視して平均を算出する

サーバースクリプト　タイミング：画面表示の前

try {
　//数値A,数値B,数値Cの値を配列arrに代入
  const arr = [model.NumA, model.NumB, model.NumC];
　//filterでarrの中身をnew_arrに再代入
　//tureとなった場合のみ、new_arrに代入される
　//よって、値がnullの場合、undefinedとなるためfalseと判定され、new_arrには入らない。
　//値が入力されている項目値のみでnew_arrが生成される
  const new_arr = arr.filter(val => val);
  //新たな配列new_arrの要素を足し上げて合計sumを算出
  let sum = 0;
  new_arr.forEach( score => { sum += score;} )
  //合計を配列要素数で割って平均を算出
  const avr = sum / new_arr.length;
  //数値項目「平均」に計算結果avrを入力
  model.NumD = avr;
} catch(e) {
  context.Log(e.stack);
}

chatGPT先生に聞きながら書いたものです。ifを使うのかと思ったのですが、先生はfilter押しのようです。

filterを使わずに if でやった場合は、こうかな

try {
  const arr = [model.NumA,model.NumB,model.NumC];
  arr.forEach( val => { context.Log(val);} )
  let sum = 0;
  let cnt = 0;
  arr.forEach( score => { 
    if (score) {
      sum += score;
      cnt++;
    }
  })
  
  if (cnt !== 0) {
    const avr = sum / cnt;
    model.NumD = avr;
  } else {
    model.NumD = 0;
  }
} catch(e) {
  context.Log(e.stack);
}

数値項目が空欄の場合 model.NumA は [undefined] となっていました。
undefined は Falsy な値なので、if に入れると false と判定され、sumへの加算の処理が行われません。

なお、Null許容としていない場合、filterの部分、ifの部分は、ばっさり不要です。

②計算式（拡張）で分類項目でやってみた

数値項目のNull許容は計算式（拡張）では無視されましたが、分類項目だったらいけるんじゃないかと、分類項目を数値しか入れちゃだめよと数値項目に擬態する方式でやってみました。
ただし、分類項目だと数値が入っていても一覧画面での並べ替えが文字列でのソートとなるので、イマイチです。

エディタの設定

分類A：表示名＝国語_分類
分類B：表示名＝数学_分類
分類C：表示名＝英語_分類

共通設定
入力制限：クライアント正規表現＝^\d*$、エラーメッセージ＝数字以外入力不可です
説明：点数を入力　（←これは特に設定しなくてもよいです）

計算式（拡張）

対象（出力先）：平均（数値控除句）

$AVERAGE(国語_分類, 数学_分類, 英語_分類)

結果

上が（４）の数値項目を引数とした計算結果。下が分類項目を引数とした計算結果

ちなみに正規表現の書き方は、chatGPT先生に聞いて教えてもらいました。

５．おわりに

フィルタで絞り込みして、集計件数を確認したり、絞り込み結果でエクスポートしたりフィルタは便利に使えるので、適切なフィルタ項目を設定しておくと、サイトの利便性が格段に上がります。

計算式（拡張）を使うことにより、入力項目から必要な部分を取り出しする等できるので、わざわざ別にフィルタ用の項目を入力する手間がなくなり、とても便利だと思いました。スクリプトを利用するよりお手軽です。

数値項目ではフィルタの設定で[範囲指定]を使用したり、[既定]ではステップ数を適切に設定する等、工夫を凝らすことで、さらに使い勝手がよくなります。

分類項目では、計算式の結果と連動するようにエディタの設定で選択肢を用意しておくのがお勧めです。

プリザンターのフィルタについては以前、【pleasanter】フィルタについてあれこれやってみた という記事を書いていますので、よろしければこちらもどうぞ。

脱線多めとなってしまいましたが、お読みいただきありがとうございました。

６．参考文献、記事

公式マニュアル
テーブルの管理：計算式（既定）
テーブルの管理：計算式（拡張）
計算式（拡張）の関数一覧
計算式（拡張）の関数で使用する論理式
テーブルの管理：エディタ：項目の詳細設定：端数処理種類

参考とさせていただいた記事
「銀行家の丸め」とは何か
【プリザンター】 第172回）計算式を使わずサーバスクリプトで計算

内部リンク
【pleasanter】計算式（規定・拡張）についてまとめてみた
【pleasanter】計算式（拡張）活用サンプル①～リマインダーの日付として利用する～
【pleasanter】計算式（拡張）活用サンプル②～入力チェックに利用する～
【pleasanter】計算式（拡張）活用サンプル③～タイトルを自由自在に～

【pleasanter】フィルタについてあれこれやってみた

もう読みましたか？プリザンターの概要、インストール方法から基本操作方法、導入事例、サンプル等、役に立つ情報が満載でしたよ！