【pleasanter】計算式（規定・拡張）についてまとめてみた

１．はじめに

前回、プリザンターの計算式（拡張）等で日付の計算をやってみました。
Excel関数に並々ならぬ愛と情熱を注ぐ者にとって、プリザンターでExcelみたいに関数が使えるのが楽しくてしかたがない。
これまで計算式は規定も含めてあまり使ったことがなかったので、この機会にどんなんかなーと、あれこれやってみて、まとめてみました。

２．計算式の基本

そもそも計算式について、基本を確認しておきます。

公式マニュアル
テーブルの管理：計算式

（１）プリザンターの計算式の基本

計算式はテーブルの管理から「計算式」タブで設定します。
「新規作成」で計算式を設定していきます。
計算式は「規定」と「拡張」の二種類があります。計算方法で切り替えします。

（２）計算式が実行されるタイミング

計算式で設定した計算は「作成」または「更新」ボタンをクリックしたタイミングで実行されます。

[エディタ]タブで計算式内で使用する項目の「自動ポストバック」をオンにすると、その項目に変化があると即時に計算が行われます。
「自動ポストバック」をオンにするのは、計算式内で使われる項目です。たとえば 数値A + 数値B の結果を 数値C に反映する計算式の場合、「数値A」と「数値B」の自動ポストバックをオンにします。

「返却する項目」を指定すると、指定した項目にのみ、計算結果が反映されます。
返却する項目を指定する場合は、NumC,NumD のようにデータベース上のカラム名で指定します。数値C 等の項目名や、 金額 等の表示名で指定すると無視されます。
返却する項目は指定しなくても問題ありませんが、画面全体の項目が多い場合等に、返却する項目を限定すると、処理が早くなるみたいです。

自動ポストバックについては前回の記事で触れていますので、よろしければ合わせてご参照ください。

３．計算式 ～規定～

公式マニュアル
テーブルの管理：計算式（既定）

基本
・数値項目のみ使用できる
・四則計算ができる（足し算（+）、引き算（-）、掛け算（*）、割り算（/））

注意点
・項目名、計算式に使う記号(+等）の間には半角スペースが必要
・項目を表示名ではなく、項目名で指定するときは NumA のように指定する

以下、使用例です

①数値項目「個数」に「単価」をかけて、「金額」を出したい

[計算式]タブで「新規作成」をクリックします。

[計算方法]は「規定」、対象には計算結果を出力したい項目を選択します。
※数値項目のみ選択できます。

計算式を入力し、「追加」ボタンをクリックします。
※項目名と記号との間には半角スペースを入力します。
※使用できるのは 数値項目 のみです。

単価 * 個数

これで計算式の設定は完了です。テーブルの管理画面での「更新」ボタンも忘れずに！

レコードを新規作成してみてみます。計算ができました！

計算が行われるタイミングは ２（２）で触れたように、「単価」「個数」のエディタ設定で「自動ポストバック」をオンに設定している場合は、「単価」「個数」の入力後、即時に計算が行われます。「自動ポストバックをオンにしていない場合は、「作成」または「更新」ボタンをクリックしたタイミングで実行されます。

なお、表示名ではなく、カラム名で指定したいときは以下のように入力します。
日本語の全角入力とスペースや記号の半角入力が混ざっていて入力が面倒だなーっていう時には、項目名で指定するとすべて半角で入力できるので面倒じゃないかも。
後述しますが、計算式（拡張）では [NumA] のように指定するので、混乱注意ポイントです。

NumA * NumB

追加ボタンクリック後、計算式の画面では表示名に変換されています。

表示名を使用しないにチェックを入れた場合は、表示名に変換されません。
表示名がやたらに長い場合などにいいかもしれませんね。

②「数値A」「数値B」「数値C」を合計、合計したものに100をかけて「数値D」に計算結果を出力

(数値A + 数値B + 数値C) * 100

③「数値A」「数値B」の合計を「数値C」「数値D」の合計で除算し、計算結果を「数値E」に出力。

(数値A + 数値B) / (数値C + 数値D)

なお、四捨五入や切り捨てなどは計算式（規定）では指定できないので、エディタの「端数処理指定」で指定するとよいです。
計算式（拡張）では $ROUND 関数などが使えるので、端数処理をしたいときは 計算式（拡張）を利用するのもおすすめです。

４．計算式 ～拡張～

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

基本
・数値項目以外の項目（分類項目、日付項目、チェック項目、説明項目等）も使用できる
・四則計算のほか、専用の関数を利用した計算式が登録できる

メモ
・計算式（規定）と違い、項目間の半角スペースは気にしなくてOK。あってもなくても大丈夫。
・文字列を指定する際は “XXX” でも ‘XXX’ でも（ダブルクォーテーションでも、シングルクォーテーションでも）どちらでも大丈夫でした。本記事ではダブルクォーテーションを使用しています(エクセル気分に浸るため)。

「規定」の計算式に比べて、数値以外の項目も利用できて、関数も多数用意されており、ものすごく自由度と可能性が高まります。エクセル感覚で計算式が組めるのがうれしい！
関数の頭に $ がつくのと、論理式の「等しい」が == だったりと、Excelとちょっと違うので、Excel使いの人はそこだけ注意。

以下、使用例です　
なお、個々の計算式についての詳細な説明は割愛しています。公式マニュアルにてご確認ください。

①点数（数値項目）が 80以上であれば「優」、50以上であれば「良」、50未満であれば「可」と「結果」(分類項目）に出力（$IF,$IFS）

$IF を使用します。（下のほうで $IFS を使用した場合の書き方も載せています）

[計算式]タブで「新規作成」をクリック。
[詳細設定]画面で、計算方法は「拡張」を指定します。

[対象]には出力先の項目「結果」を指定します。

計算式を入力します。「追加」ボタンクリックして、計算式を登録します。
「更新」ボタンも忘れずに！

$IF(点数 >= 80, "優", $IF(点数 >= 50, "良", "可"))

できました！

$IFS を使った場合はこちら

$IFS(点数>=80, "優", 点数>=50, "良", "true", "可") 

②「出荷日」（日付項目）に日付を登録したら「出荷区分」（分類項目）に「100:済」を出力。日付項目が空欄の場合は「200:未済」($ISBLANK,$IF,$NOT)

$IF と $ISBLANK を使用しました。

$IF($ISBLANK(出荷日), "200", "100")

結果

以下の計算式でも可です。
$NOT

$IF($NOT($ISBLANK(出荷日)), "100", "200")

選択肢の値を指定するのは ‘100’ のようにシングルクォーテーションでも 100 のように数値のみでも大丈夫でした。

③「製品名」（分類項目）と「型番」（数値項目）を連結して「製品番号-型番」（分類項目）に出力($CONCAT)

$CONCAT を使用しました。

$CONCAT(製品名, "-", 型番)

④日付Aと日付Bの差の日数を数値Aに出力($DATEDIF)

$DATEDIF を使用しました。

$DATEDIF(日付A, 日付B, 'D')

$DATEDIF(開始日, 終了日, 計算単位) と指定します。計算単位は ‘D’ が日数、’M’ が月数、’Y’ が年数です。

※エラーについて

たとえば④の計算式で、開始日より終了日が先だと、エラーになります。
「エラーを出力する」にチェックを入れている場合は「アプリケーションで問題が発生しました」のメッセージが出て、レコードを「作成」「更新」することができません。チェックを入れていない場合は、計算が行われませんがレコードの「作成」「更新」はできます。

気持ちが悪い場合は $IF や $IFERROR で回避しましょう。

例）開始日のほうが遅い場合は 9999 を表示
$IFERROR($DATEDIF(日付A, 日付B, ‘D’), 9999)

例）どっちが先でもいいから差の日数を出す
$IF(日付A<=日付B, $DATEDIF(日付A, 日付B, ‘D’), $DATEDIF(日付B, 日付A, ‘D’))

５．計算式 ～便利機能～

（１）同期

すでに入力済のレコードがある状態で、計算式を設定した場合、設定した以前に入力済のレコードには反映されません。（もちろん、1件ずつ開いて「更新」したら、新たに設定した計算式は反映されます。）

計算式タブの「同期」ボタンで、以前に入力済のレコードにも一気に計算式を反映することができます。
反映したい計算式にチェックを付けて「同期」ボタンをクリックします。

便利！気が利いている！

（２）条件 ～ビューのフィルタを利用した計算式の条件分岐～

なんと！条件によって計算式を分岐することもできるんです。
条件は、なんと！「計算式」タブではなく、「ビュー」タブの「フィルタ」で指定するんです。

やってみます。

[エディタ]タブで
・「消費税率」の分類項目を追加し、選択肢は「消費税8%」「消費税10%」とします。
・数値項目で「単価」「個数」、そして「税込金額」を設定します。
　「税込金額」は小数点以下を「切り捨て」としました。

消費税率が「消費税8%」の場合　　　：単価 × 個数 × 1.08
消費税率が「消費税8%」ではない場合：単価 × 個数 × 1.1
の計算式を設定したいと思います。

①「ビュー」のタブの「フィルタ」で条件を設定する。

[ビュー]のタブで「新規作成」をクリックします。

・ビューの名称を入力します。ここでは「軽減税率対象」としました。
・「フィルタ」をクリックして、分岐の条件となるように、フィルタ条件を設定していきます。
・項目「消費税率」が「消費税8%」かそうではないかで分岐したいので、画面下部のフィルタ条件で「消費税率」を追加し、「消費税8%」にチェックを入れます。
・「追加」ボタンをクリックして、ビューの設定は完了です。

普通にビューとして使用する場合は、一覧画面で「軽減税率対象」のビューを指定すると、消費税率8%のレコードだけが表示されるビューとなります。

②条件付きの計算式を設定する

さて！今度は[計算式]タブで「新規作成」をクリックします。

今回は単純な掛け算なので計算方法は「規定」で、返却項目には「税込金額」を選択します。

[条件]の部分で設定済の「ビュー」が選択できます。
①で設定した「軽減税率対象」を指定します。

すると、下にもう一段、計算式を入力できる枠が追加して表示されます。
上段に、ビューのフィルタ条件に該当する場合に適用したい計算式、
下段に、該当しない場合に適用したい計算式を設定します。

「追加」をクリックして計算式の設定完了です。「更新」ボタンも忘れずに！

いくつかレコードを入力し、一覧画面で見てみたものです。
ちゃんと、消費税項目で「消費税8%」としたレコードは[税込金額]が8%で計算され、「消費税10%」としたレコードは[税込金額]が10%で計算されていますね！

余談ですが、3つ以上の条件分岐としたいとき。。。

ビューを3つ設定してい、条件外の時は「なにもしない」として3つの計算式を設定してみたら行けました。ただ、どの条件にも当てはまらないときに計算が行われず、同期がうまくいかなかったりするので、なんか不確定な感じがするのでおすすめしません。

複数の分岐をしたい場合は、計算式（拡張）の$IFで分岐したほうがいいように思います。そもそも、ビューで指定して、計算式で指定して、と２か所で設定するより計算式の1か所で設定したほうが、修正時などの管理が楽になるように思います。好みですが。

さらに余談ですが。。。
「ビュー」で条件を指定する、というのが、「え？？？？？」と、ハテナが5個くらい出てなかなか呑み込めませんでした。

妄想ですが、計算式（拡張）がない時代の「計算式」タブさんが、「条件分岐したいなー。でも新しく機能作るの面倒だなー」。。。「ん？ビュータブさん、キミのフィルタ条件、使えそうだな。ちょっと使わせてもらっていい？」という会話が行われたのではないでしょうか。←メルヘン妄想です。

ちなみに「通知」「リマインダー」等でも「ビュー」タブのフィルタを使って条件を指定することができるみたいですよ。

６．おまけ：エラー！？と思ったら確認するポイント

おまけです。ガチャガチャやっていて、数々のエラーを繰り出しまして、だいたいここが間違っていたというところを参考までに書き出します。

① 半角スペースを入れていない。スペースや+等の記号が全角になっている。（規定のみ）
② 掛けるを * でなはく × とかやっちゃったり、割るを / ではなく ÷ とかやっちゃったりしている
③ 表示名の入力間違い。
④ () が片側過不足（長い式でわけわかんなくなってやりがち）
以下は拡張
⑤ 関数の先頭の $ 忘れ
⑥ 計算式の先頭に = 入れちゃってる
⑦ 論理式 等しいを == とするところを = としちゃってる。
⑧ 論理式 等しくない を != とするところを <> とかしちゃってる
⑨ 関数のつづり間違い

カラム名で指定するときに 規定 では NumA のように指定し、拡張では [NumA] のように指定するので、その違いにも注意。

表示名が重複している場合、重複した表示名を計算式で使用すると期待した結果とならない場合があります（たぶん、優先順位が高い項目のほうが使われる）。その場合は「表示名を使用しない」にチェックして、カラム名を使用しましょう。

⑤⑥⑦⑧はエクセルやってから、さて、次はプリザンター…とやったときに起こりがち。

エラーとなったときは「アプリケーションに問題が発生しました」と出るか、無視されて計算がされなかったりのパターンがあるようです。

計算式ログやシステムログでエラー内容が見れるとか書いてあるけど、どーせ見てもわからないので、表面上で確認しています。

思ったようにいかない場合は、複数の関数を組み合わせた長い式の場合はひとつづつ確認しながら組み立てていったり、いったん適当な分類項目などにそれぞれの式の出力結果を出してみたりして、ちょっとずつ組み立てていくとだいたい何とかなります。

７．最後に

長くなっちゃって、ごめんなさい！この長さで「まとめ」と称してよいのだろうか。

計算式（拡張）が面白すぎて。興奮冷めやらないので、計算式拡張は続編をやります！

８．参考文献、リンク

公式マニュアルのリンク

テーブルの管理：計算式（拡張）
計算式（拡張）の関数一覧
計算式（拡張）の関数で使用する論理式

内部リンク
【pleasanter】計算式拡張で日付の計算をやってみた