計算式

特徴

計算式は Excel の計算式と高い互換性があります。日時のデータに +1 で翌日、+”1:00″ で1時間後、他には % で 1/100倍、& で数値や文字列の連結などなど、Excel と同じです。関数も Excel と同じものを約100個サポートしています。そのため、Excel を利用したことがある方はその知識をそのまま活かせます。

ただし、配列の扱いが異なります。Excel では行列が扱えますが Fullfree では扱えません。その代わり配列を多階層に出来ます。

・Excel の行列の例：{1, 2; 3, 4}
・Fullfree の多階層配列の例：{{1, 2}, {3, 4}}

配列の計算は Excel の配列数式の動作に似ています。配列を直接記述する機会はあまりありませんが、子行や複数値、そして関連テーブルの参照などが配列になります。どれも Excel にはない概念です。

計算式で出来ること

計算式を使うと例えば、生年月日から現在の年齢、単価×数量で合計金額、顧客毎に売上集計、社員毎に今月の売上、商品毎に仕入れ数量の合計から販売数量の合計を引いてリアルタイムに現在の在庫表示など様々なことが出来ます。

また、出来ることは数値計算だけではありません。日付から曜日の表示、西暦を和暦で表示(DATESTRING関数)、フリガナの入力(PHONETIC関数)、名字と名前を結合して敬称を追加など、日付や文字列も操作できます。

更に、計算式では階層やリンクでつながっているすべての項目を参照出来ます。例えば、注文明細で商品をリンクすると商品単価や商品サイズなども自由に参照出来ます。参照の計算式には Excel のように VLOOKUP 関数は必要ありません。参照したい項目をダブルクリックするだけです。

計算結果で並び替えたり検索したりも出来ます。

お使いの前に（「値の参照」と「値のコピー」の違いについて）

計算式の利用箇所は複数ありますがセルに結果を表示する場合、計算結果を保存すべきかどうか（参照かコピーか）を意識して下さい。保存すること（値のコピー）も、保存せずに常に再計算すること（値の参照）も出来ます。尚、Excel の計算動作は常に再計算の方です。

もし、商品テーブルから商品単価を注文テーブルに参照するような場合、商品単価が変わる可能性があるなら計算結果を保存して下さい。そうでないと、商品テーブルの商品単価を値下げしたら、それを参照している過去の注文金額が一斉に変更されることになります。

計算式の書き方

テーブル定義でデータ型が「計算/参照」のフィールドを追加して、計算式のボタンを押すと下記の計算式入力ダイアログが開きます。

「計算式 =」に続けて計算式を入力してください。入力ボックスのすぐ下に使える演算子の一覧があります。クリックすると演算子が入力されます。参照項目や関数は項目を選択してダブルクリックすると入力されます。

関数名は大文字/小文字どちらを使っても構いません。計算式では大文字/小文字の違いや、スペースと改行は保持されます。例えば、CountBlank や DateString と記述するとそのままの表記で保存されます。見やすいように記述して下さい。

ただし、記号や関数名はすべて半角です。全角を使うと構文エラーになります。

定数の書き方

各型に応じて定数の書き方は異なります。

• 数値：数字を直接記述（例：1.23, -10 など）
• 文字列：”” でくくる（例：”abc”, “あいう” など）
• 日時：関数を使う（例：DATE(2001, 2, 3), DATETIME(2001, 2, 3, 10, 20, 30), DATEVALUE(“2001/2/3”), DATETIMEVALUE(“2001/2/3 10:20:30”) など）
• 配列：{ } でくくる（例：{1,2,3}, {“abc”, “def”} など）

DB内データの参照

DB内のデータを参照するには [内容] という形式で記述します。階層の区切りは /、リンクは//、関連テーブルは番号で指定などルールがいろいろあります。これらを憶える必要はなく、参照項目の一覧から項目をダブルクリックして入力してください。

計算式の実際の例

デモデータを利用して下さい。デモデータでは多数計算式を使っているので参考になります。また、データが既に入っているので計算式をテストするのにも有効です。

表示形式と入力形式

[内容] の形式で参照する項目には表示形式と入力形式があります。上記の実際の例で表示されているのは表示形式です。計算式を入力するダイアログボックスでは入力形式になります。例えば、[records:着信履歴] は [records:1] などになります。また、表示形式と入力形式は同じになる場合もあります。

計算式の結果

計算式入力ダイアログで [チェック] ボタンを押すと、計算式の内容をチェックして計算式の解析結果が表示されます。計算/参照型のフィールドでは、実際のデータ型と複数値かどうかは計算式の内容によって決まります。

尚、結果が定数になる場合は、その値も確認出来ます。データベースの項目を参照しないような計算式の場合に定数になります。そこで、関数や配列の振る舞いを確認するのに利用出来ます。

書式設定・ボタン設定

計算/参照型のフィールドでは、計算式によって実際のデータ型が変わるため、計算式を決めてからでないと書式設定などは行えません。計算式を入力した後に「計算結果に応じた設定」の隣にある [設定] ボタンで書式やボタンが設定出来ます。

ボタンが設定出来るのは結果のデータ型が短いテキストになる場合です。URLを計算式で生成してボタンを押してブラウザを開くといったことが出来るようになります。

便利な機能

計算式の結果がキーリンク型（レコードを返す式）の場合は「カードで開く」のボタンが付きます。また、DBの項目を参照するだけの式（演算を一切行わない式）で、かつ、DBの項目が変更出来るもの（キーリンクや計算/参照型以外）の場合は「カードで編集」のボタンが付きます。

計算エラー

計算エラーが起こると表形式でセルの色がピンクになります。その場合、エラーセルにカーソルを移動すると計算エラーの原因が画面下のステータスエリアに表示されます。

配列の計算

配列の計算は Excel の配列数式の動作に似ています。ただし、Fullfree では行列はサポートしていません。その代わり配列を入れ子にして複数階層に出来ます。

複数値のセル、子行のセル、関連テーブルのセルを参照するといずれも配列になります。更に孫行（子行の子行）のセルだと２階層の配列なり、孫行の複数値だと３階層の配列になります。

配列の計算は下記のルールに従います。

1. 配列では一番深い階層にのみ値が存在できる
2. 配列演算では要素毎に演算が行われる（要素数が一致しないとエラー）
3. 配列演算では階層の一番深い配列に深さが拡張する

それぞれ詳しく見ていきます。

1. 配列では一番深い階層にのみ値が存在できる

配列の階層が２階層とすると、値はすべて２階層目になくてはいけません。

○　{{1,2},{3}}
×　{{1,2},3}

尚、DBの項目を参照する場合は自動的にこの条件を満たします。

2. 配列演算では要素毎に演算が行われる（要素数が一致しないとエラー）

下記のように要素毎に演算が行われます。

{1,2,3} * {10,20,30} → {1*10, 2*20, 3*30} → {10, 40, 90}

複数階層の場合も同様です。

{{1,2},{3}} * {{10,20},{30}} → {{1*10, 2*20}, {3*30}} → {{10, 40}, {90}}

要素数が一致しない場合はエラーになります。

{1,2,3} * {10,20} → 3個と2個とで異なるためエラー

尚、同じ子行の項目や同じ関連テーブルの項目を参照すると、常に配列の要素数は一致します。

3. 配列演算では階層の一番深い配列に深さが拡張する

深さの違う配列同士の演算、あるいは、配列と単一値の演算では深い方へ配列の深さが拡張します。

例1：{1,2,3} * 10 → {1,2,3} * {10,10,10} → {1*10, 2*10, 3*10} → {10, 20, 30}
例2：{{1,2},{3}} * {10,20} → {{1,2},{3}} * {{10,10},{20}} → {{10, 20}, {60}}

配列を受け取れ、配列を返す関数でも同様です。

IF({1,2,3}=2, "Yes", "No") →
IF({FALSE,TRUE,FALSE}, "Yes", "No") →
IF({FALSE,TRUE,FALSE}, {"Yes","Yes","Yes"}, {"No","No","No"}) →
{IF(FALSE, "Yes", "No"), IF(TRUE, "Yes", "No"), IF(FALSE, "Yes", "No")} →
{"No", "Yes", "No"}

尚、計算結果が２階層以上の配列になる場合、最終結果は１階層に変換されて画面表示されます。

最終結果を単一値にする方法

最終結果は配列ではなく単一値にしたい場合は、MIN, MAX, INDEX などの関数を使って下さい。例えば、最初の要素だけにする場合は INDEX(配列, 1) になります。もし、配列が2階層の場合だと INDEX(INDEX(配列, 1), 1) と階層分呼び出して下さい。

尚、常に単一値しか返さない MIN や MAX の場合は、配列が複数階層であっても呼び出しを複数回にする必要はありません。関数が配列を返すことがあるかどうかは関数説明の戻り値に * があるかどうかで分かります。

TIPS

すべてのセルに値がある場合のみ計算する方法 (空白があるなら結果も空白)

空白セルは数値演算では 0、文字列演算では “”(空文字)として計算されます。そのため、[数量] * [単価] という計算式では、数量と単価の両方が入力されない限り結果は常に 0 です。0 ではなく空白にするには、

IF(AND([数量]<>"", [単価]<>""), [数量]*[単価], "")

でもいいですが、項目数が増えると入力が面倒です。そこで、

IF(COUNT([数量], [単価])=2, [数量]*[単価], "")

にすると少しすっきりします。COUNT は空白セルはカウントしません。空白セルも含めてカウントするには COUNTB を使って下さい。

日付/時刻型で時刻のみにする方法

予約管理デモでは予約テーブルに予約日時と終了時刻があります。終了時刻を時刻だけ（日付は不要）にするのに RIGHT 関数を使っています。0 詰めされて時刻は常に 00:00 の形式なので、

RIGHT([終了日時], 5)

のようにすると日付を削除して時刻だけに出来ます。もし、書式で秒も表示している場合は 00:00:00 形式なので、

RIGHT([終了日時], 8)

とすると時刻だけになります。

バージョン 3.2.0 で TEXT.D 関数が追加されました。TEXT.D 関数を使うと日時のデータから自由なフォーマットで情報を取り出せます。例えば、時分秒の情報を表示するには

TEXT.D([終了日時], "HH:mm:ss")

となります。時間の 0 詰めをなしにするには

TEXT.D([終了日時], "H:mm:ss")

です。

日付から年度の計算

日付から年度を求めるには、月が1～3月の場合に年から1引くことで計算できます。「日付」というフィールドに対して年度を計算するには

YEAR([日付]) - IF(MONTH([日付]) < 4, 1, 0)

です。

計算式でレコードを返す意味

顧客名簿デモの顧客テーブルでは、「最後の着信」にレコードを返しています。ここは単純に

MAX([records:1/着信日時])

でも最後の着信日時は取得出来ます。それをあえて、

LOOKUP(MAX([records:1/着信日時]), [records:1/着信日時], [records:1])

と、LOOKUP 関数でレコードを探して返しているのには理由があります。レコードを返すと「カードで開く」のボタンが付くからです。詳細を知りたいとき、１クリックで参照先を開くことが出来るようになります。

Excel との違い

Fullfree の計算式は Excel の計算式と高い互換性がありますが、多少違いもあります。違いについて説明します。

書式を引き継いだ計算

Excel では計算時に書式の情報は失われます。例えば,(カンマ) 区切りに書式設定したセルに「& "円"」として「円」を末尾に付けようとすると数値の, はなくなります。また、日付のデータに「"開始日：" & 」のように先頭に文字列を付けようとすると日付はシリアル値がそのまま表示されます。

Fullfree ではこのような場合、設定した書式情報を引き継いで計算します。つまり数値の , や日付の形式のまま文字列になります。もし、Excel と同じように書式を無視するには、書式を無視した参照方法をオンにして項目をダブルクリックして下さい。

日時のシリアル値の違い

Fullfree も Excel と同じ体系のシリアル値を使って日時のデータを計算しています。ただし、起点は異なります。シリアル値 1 に対する日付は、

• Excel → 1900年1月1日 (Windwos 版 Excel)
• Fullfree → 1899年12月31日

です。1900年は閏年ではないので1900年2月29日は存在しないのですが、Excel には存在するという不具合があります。この不具合の対策のため、Fullfree では1日ずらしています。1900年3月1日以降のシリアル値はどちらも同じ値になります。

尚、Fullfree のシリアル値は負の値が扱えます。つまり、1900年以前の日時も計算できます。

多くの関数では引数が空白なら空白を返す

空白セルを四則演算した場合、0 と見なして計算します。ここまでは Excel と同じですが、関数の場合は、日付・数学・統計の多くの関数で引数が空白だと戻り値も空白になります。

尚、SUM, PRODUCT 関数の場合は、すべての引数が空白の場合のみ戻り値が空白になります。もし、引数がすべて空白で、SUM の結果を 0 と表示したい場合は、

SUM(引数) + 0

として下さい。

-A 付き関数はなく、-B 付き関数がある

Excel には末尾が -A で終わる関数があります。AVERAGEA, COUNTA, MINA, MAXA などです。-A なしの関数（AVERAGE, COUNT, MIN, MAX など）との違いは、

• -A なし → 数値（日付も含む）のみを対象とする（空白は除く）
• -A 付き → 数値以外も対象とする（空白は除く）

です。列方向に数値や文字列など混在で扱える Excel では2種類に分ける意味がありますが、列ごとに型を持っている Fullfree では -A なしはほとんど意味をなしません。文字列のフィールドでは COUNT を実行する前から結果は 0 と分かっています。数値のフィールドでは COUNT と COUNTA の結果は同じにしかなりません。

そこで、Fullfree では -A 付きの関数をなくし、Excel の -A 付きに相当するものが -A なしになっています。そして、AVERAGEB, COUNTB, MINB, MAXB など -B 付きの関数があります。-B 付きの関数は空白を 0 と見なして空白も計算の対象とします。-B は Blank（空白）の B です。

AND, OR は配列を返せる

Excel では AND, OR は常に単一値を返します。Fullfree では引数が配列だと AND, OR は配列を返します。例えば、下記のような動作になります。

AND({1,2,3}>1, 1+2>1) →
AND({FALSE,TRUE,TRUE}, TRUE) →
{AND(FALSE,TRUE), AND(TRUE,TRUE), AND(TRUE,TRUE)} →
{FALSE, TRUE, TRUE}

子行毎（配列の要素毎）に条件判定したいような場合に有効な動作です。ただし、すべての子行が条件を満たすかどうか（配列の要素がすべて TRUE かどうか）の判定には AND 関数は使えないことになります。

引数が配列の場合の AND, OR で単一値を返すには、AND, OR の代わりに PRODUCT, SUM を利用して下さい。TRUE → 1、FALSE → 0 で計算されるので、

PRODUCT(条件の配列) > 0

とすると「条件の配列」の条件がすべて TRUE の場合に結果が TRUE になります。（Excel の AND 相当）

SUM(条件の配列) > 0

とすると「条件の配列」の条件がどれか一つでも TRUE だと結果が TRUE になります。（Excel の OR 相当）

MATCH, LOOKUP の検索方法は「一致」がデフォルト

MATCH 関数には値の検索方法（照合の種類）を指定出来る引数があります。このデフォルト値が Excel と異なります。

• 1 → 検査値以下の最大の値を検索（Excelのデフォルト）
• 0 → 検査値と等しい最初の値を検索（Fullfreeのデフォルト）
• -1 → 検査値以上の最小の値を検索

Fullfree では照合の種類を省略すると 0 と見なします。また、1 や -1 を使う場合に、昇順や降順での並び替えは不要です。（Excel では並び替えが必要）

Excel の LOOKUP 関数には上記の引数はなく、1 の動作（検査値以下の最大の値を検索）をします。Fullfree には省略可能な引数として上記の引数があり、省略時は 0 の動作（検査値と等しい最初の値を検索）をします。また、LOOKUP 関数を使うのに検査対象が昇順である必要はありません。（Excel では必要）

フリガナ入力 PHONETIC 関数の動作の違い

フリガナ入力に利用出来る PHONETIC 関数の動作が Excel と異なります。

• Excel → IME入力の内容からフリガナを入力
• Fullfree → 漢字の読みからフリガナを入力

Fullfree の場合はIMEで入力した通りのフリガナにならない場合がありますが、IMEを使わないコピー＆ペーストでの入力であっても正しくフリガナ入力出来ます。（Excel では元の漢字がそのまま表示）

Fullfree では形態素解析（日本語解析）を行った上で漢字の読みを決定しているので、同じ漢字でも文脈に応じたフリガナになります。

例：

• 御手洗さんが来る → ミタライサンガクル
• 御手洗いに行く → オテアライニイク