請求書アプリを作りながら、 AppSheet （アップシート）の使い方を学びましょう。
前回（第3回）は、「Ref」を使ってテーブル間のリレーションシップを設定しました。
今回は、明細の金額を合計する2つの計算式を解説します。
合計金額は、Virtual Column（仮想列）で計算して表示します。
目次
1. 明細の金額を合計する2つの方法
請求書には明細の金額を合計して、小計、消費税、合計（請求金額）を計算して表示する欄があります。
「請求書」レコードの「ID」と同じ「請求書ID」を持つ「明細」レコードを抜き出して金額を合計すれば計算できます。
合計の計算は、以下の2つの方法があります。
- SELECT関数で取得した金額のリストをSUM関数で合計する
- 「Related 明細s」で取得した金額のリストをSUM関数で合計する
SELECT関数は、 AppSheet では使用頻度が高く、応用が利く関数なので使い方を覚えておきましょう。
また、前回（第3回）で「請求書」テーブルと「明細」テーブルの間でリレーションシップ（親子関係）を設定したので、「Related 明細s」を使用して合計を計算することができます。「Related 明細s」を使用すると計算式を簡潔に書けるようになります。
2. SELECT関数とSUM関数を使用する
SELECT関数
はじめに、SELECT関数を使用する方法を解説します。
SELECT関数は、列の値をリストで取得できる関数です。
第1引数にテーブル名と列名を設定し、第2引数に条件式を設定します。
第3引数を「TRUE」にすると重複する値を削除します。第3引数は省略可です。
SELECT( テーブル名 [列目], 条件式) => 条件に合致するレコードの列の値をリストで返す
値のリストというのは、PHPやJavaScriptのようなスクリプト言語でいうところの配列に相当します。
AppSheet には配列（Array）という用語がなく、代わりにリスト（List）という用語を使用します。
リストは { }（中括弧、波括弧）内に、数値や文字列を ,（コンマ）で区切って並べます。
文字列の場合は、 ""（ダブルクォーテーション）で囲みます。
- { 100, 200, 300 }
- {"香川", "徳島", "愛媛", "高知"}
AppSheet にはLIST関数も用意されており、{ } の代わりに使用できます。
- LIST(100, 200, 300)
- LIST("香川", "徳島", "愛媛", "高知")
では、請求書アプリのケースでSELECT関数の使い方を学びましょう。
エディタ左側メニュー「Data」をクリック、メインパネル右上の「View source」をクリックして、データソースを開きましょう。
「請求書」ワークシートをクリックして開きましょう。
「ID」列の値が「6834ad5c」の請求書について、小計を計算してみます。
「明細」ワークシートで「請求書」が「6834ad5c」の明細を探します。
2〜4行目が探している請求書の明細であることが分かります。
小計を計算したいので、欲しいのは「金額」列の値のリストです。
{ 55000, 7500, 2250 }が欲しい値のリストになります。
以上をSELECT関数で式にすると以下のようになります。
明細から金額のリストを取得する
|
1 2 3 4 |
SELECT( 明細[金額], [請求書] = [_THISROW].[ID] ) |
式を分解すると、
- 第1引数
- テーブル名：明細
- 列名：金額
- 第2引数（条件式）：[請求書] = [_THISROW].[ID]
になっています。第2引数（条件式）は、
- 「明細」テーブルの「請求書」列の値（請求書ID）が、
- 「請求書」テーブルで現在表示している（_THISROW）請求書の「ID」列と同じ値である
という意味です。
「_THISROW」というのは現在のレコード（行）のことです。
今回のケースでは「請求書」ワークシート1行目のレコードのことを指します。つまり、
- [_THISROW].[ID] = 6834ad5c
なので、「明細」テーブルの「請求書」列の値が「6834ad5c」のレコード（行）の「金額」のリストを取得できます。
以上より、SELECT関数で条件に合致する「金額」をリストで取得すると以下のようになります。
- { 55000, 7500, 2250 }
LIST関数で表記すると以下のようになります（意味は同じです）。
- LIST( 55000, 7500, 2250 )
SUM関数
SELECT関数で取得した値リストを合計すれば小計を計算できます。
数値の合計にはSUM関数を使用します。
SUM( リスト ) => リストの値の合計する
明細の「金額」から請求書の「小計」を計算する式は、以下のようになります。
明細から小計を計算する
|
1 2 3 4 5 6 |
SUM( SELECT( 明細[金額], [請求書] = [_THISROW].[ID] ) ) |
実例でこの式を実行すると、SUM( { 55000, 7500, 2250 } ) で、「64750」が返ります。
この請求書の小計は、64,750円ということになります。
3. 「Related 明細s」を使用する
「明細」テーブルの「請求書」列は、
- TYPE（データ型）：Ref
- Source table：「請求書」テーブル
なので、「明細」テーブルと「請求書」テーブルにはリレーションシップ（親子関係）が設定されています。
上記の「Ref」を設定した時に、「請求書」テーブルに「Related 明細s」という列が追加されています。
「請求書」テーブルと「明細」テーブルの関係は、「請求書」が親で「明細」が子です。
「Related 明細s」を使用すると、親テーブルから子テーブルの列の値や、値のリストを取得できるようになります。
Dataナビゲーションで「請求書」テーブルを選択して、列設定パネルを表示しましょう。
「Related 明細s」列があるので、列名の左にある編集アイコンをクリックして設定パネルを開きます。
App formulaにREF_ROWS関数が設定されています。
入力フィールドをクリックして、Expression Assistantを開いてみます。
Testのリンクをクリックします。
それぞれの請求書の「子レコード」（明細レコード）の「ID」（KEY）がExpression Resultにリスト表示されています。
「Related 明細s」列には、請求書の明細がリストで格納されていることが分かりました。
「Related 明細s」リストを使用すれば、請求書の「小計」は以下の式で計算できます。
|
1 |
SUM([Related 明細s][金額]) |
[Related 明細s] と [金額] をそのまま繋げます。間に . はいりません。
SELECT関数を使用した式よりも「Related 明細s」の方が簡潔に書けて、結果も同じになります。
4. AppSheet ではVirtual Columnで計算するのが便利
「請求書」テーブルには「小計」「消費税」「合計」を計算、表示する列を用意していませんでした。
「小計」「消費税」「合計」は、明細行の金額を合計して計算します。
例えば、明細の商品を後から変更したり、数量を変更したりする度にスプレッドシートを上書きするのは面倒です。
AppSheet なら、Virtual Column（仮想列）で計算して表示するのが便利です。
Virtual Columnには以下のような特徴があります。
- データソース（Googleスプレッドシート）にデータを保存しない
- App formulaに設定した式を自動的に計算する
- アプリを同期する度に最新の計算結果を表示できる
Virtual Columnはデータソースには存在せず、アプリ上でだけ動作する列です。
単価や数量を変更する度にデータソースを更新する必要がなく、常に最新の計算結果を表示できます。
「請求書」テーブルに計算用のVirtual Columnを追加しましょう。
アプリエディタ左側メニュー「Data」をクリックします。
Dataナビゲーションで「請求書」テーブルを選択して、メインパネル右上にある「+」アイコン（Add virtual column）をクリックします。
「小計」列
Virtual Columnの新規設定パネルが開くので、Column nameに「小計」と入力します。
App formulaに式を入力するので、入力フィールドをクリックしてExpression Assitsantを開きます。
小計の計算式をコピペします。
SELECT関数または「Related 明細s」のどちらでもよいですが、「Related 明細s」を使用した式なら以下のように簡潔に書けます。
|
1 |
SUM([Related 明細s][金額]) |
Expression Assistant に以下のように入力して、「Save」をクリックします。
「小計」列のTYPE（データ型）は、 AppSheet が自動的に「Price」に設定したと思います。
Type Details（Decimal digits、Currency symbol）も日本円の設定になっていると思いますが、一応確認しておきましょう。
確認を終えたら、設定パネル右上の「Done」ボタンをクリックして閉じます。
「消費税」列
再度、「+」アイコン（Add virtual column）をクリックして、Virtual Columnを追加します。
Column nameに「消費税」と入力して、App formulaの入力フィールドをクリックしてExpression Assistantを開きます。
「Related 明細s」を使用した以下の式をコピペしましょう。
|
1 |
SUM([Related 明細s][消費税]) |
Expression Assistant に以下のように入力して、「Save」をクリックします。
「合計」列
さらに、「+」アイコン（Add virtual column）をクリックして、Virtual Columnを追加します。
Column nameに「合計」と入力して、App formulaの入力フィールドをクリックしてExpression Assistantを開きます。
「Related 明細s」を使用した以下の式をコピペしましょう。
|
1 |
SUM([Related 明細s][合計]) |
Expression Assistant に以下のように入力して、「Save」をクリックします。
画面右上の「SAVE」をクリックして設定を保存してください。
以上、明細から請求書の小計、消費税、合計を計算できるようになりました。
リレーションシップを利用して、親テーブルに関連する子テーブルの値リストを取得して合計するテクニックは様々なアプリで応用が効きます。
もちろん、見積書や納品書にも応用ができるので、この機会に是非習得してください。
次回（第5回）はViewを設定してアプリの見た目を整えます。
