AppSheetで請求書アプリ（第4回）SELECT関数とSUM関数で合計を計算

請求書アプリを作りながら、AppSheetの使い方を学びましょう。
前回（第3回）はテーブル間のリレーションシップを利用して、請求書と明細の親子関係を設定しました。
今回は、SELECT関数を使って明細から金額の値リストを取得して、SUM関数を使って合計します。
SELECT関数はよく使う関数なので、使いこなせばAppSheetアプリ開発の幅が広がりますよ！

【お知らせ】
2022年10月以降、AppSheetエディタの画面が新しく変更されています。
AppSheetのアップデートが速すぎて（汗）、このブログ記事はまだ新エディタの画面には対応していません。
エディタ画面右上にある「Switch to the legacy editor」というアイコンをクリックすると、新エディタと旧エディタの切り替えができます。
記事のアップデートができるまでは、とりあえず、旧エディタに切り替えて解説を読んでください。

1. AppSheet関数、SELECTとSUMで金額を合計する

請求書には明細の金額を合計して、請求金額（合計）や小計などを表示する欄があります。
「請求書」テーブルと「明細」テーブルには既にリレーションシップが設定されています。
「請求書」レコードの「ID」と同じ「請求書ID」を持つ「明細」レコードを抜き出して、その金額を合計すれば計算できます。

SELECT関数

ここで、SELECT関数とSUM関数を用いて、明細の「金額」を合計して請求書の「小計」（消費税を含まない金額の合計）を計算する式を設定します。

SELECTというAppSheet関数は、列の値をリストで取得できる関数です。
第1引数にテーブル名と列名を設定し、第2引数に条件式を設定します。
第3引数を「TRUE」にすると重複する値を削除します。第3引数は省略可です。

値のリストというのは、PHPやJavaScriptのようなスクリプト言語でいうところの配列に相当します。
AppSheetには配列（Array）という用語がなく、代わりにリスト（List）という用語を使用します。

リストは「{ }」（中括弧、波括弧）内に、数値や文字列を「,」（コンマ）で区切って並べます。
文字列の場合は、「""」（ダブルクォーテーション）で囲みます。

LISTというAppSheet関数も用意されており、「{ }」の代わりに使用できます。

では、請求書アプリの実例でSELECT関数の使い方を学びましょう。
第2回でアプリを生成する際にダウンロードしたExcelデータには、既にサンプルデータが入っているのでそれを使いましょう。

AppSheetエディタ左側メニュー「Data」をクリック、「Tables」タブに移動して「請求書」テーブルをクリックして設定パネルを開きます。
「View source」をクリックして、データソースを開きましょう。

「請求書」ワークシートをクリックして開きましょう。
「ID」列の値が「6834ad5c」の請求書について、小計を計算してみます。

「明細」ワークシートで「請求書」が「6834ad5c」の明細を探します。
2〜4行目が探している請求書の明細であることが分かります。

小計を計算したいので、欲しいのは「金額」列の値のリストです。
{ 55000, 7500, 2250 }が欲しい値のリストになります。

以上の実例をSELECT関数で式にすると以下のようになります。
「_THISROW」というのは「請求書」ワークシート（テーブル）1行目のレコードのことを指します。つまり、

[_THISROW].[ID] = "6834ad5c"

ということになります。

明細から金額のリストを取得する

SELECT(
 明細[金額],
 [請求書] = [_THISROW].[ID]
)

式の意味は以下のようになります。
返ってくる値は、{ 55000, 7500, 2250 } です。

SUM関数

SELECT関数で取得した値リストを合計すれば小計を計算できます。
数値の合計にはSUMというAppSheet関数を使用します。

明細の「金額」から請求書の「小計」を計算する式は、以下のようになります。

明細から小計を計算する

SUM(
 SELECT(
  明細[金額],
  [請求書] = [_THISROW].[ID] 
 )
)

実例でこの式を実行すると、SUM( { 55000, 7500, 2250 } ) で、「64750」が返ります。
この請求書の小計は、64,750円ということになります。

2. AppSheetではVirtual Columnで計算するのが便利

実は、「請求書」テーブルには「小計」「消費税額」「合計」を表示する列がありませんでした。
AppSheetでは、計算結果を表示する列としてVirtual Columnを使うのが簡単です。
「小計」「消費税額」「合計」は、Virtual Columnで表示することにします。

Virtual Column とは文字通り「仮想的な列」です。
明細を追加、更新、削除したときに、「請求書」テーブルのスプレッドシートを更新しなくても「小計」「消費税額」「合計」の計算結果を更新できるため便利です。

AppSheetエディタの左側メニュー「Data」をクリックします。
タブメニュー「Columns」をクリックして、「請求書」をクリックして列設定パネルを開きます。
「Add Virtual Column」をクリックして、Virtual Columnを追加します。

「小計」列

Virtual Column設定パネルが開きます。
Column nameに「小計」と入力します。

次に、App formula の三角フラスコアイコンをクリックします。
Expression Assistant に以下のように入力して、「Save」をクリックします。
入力する式は、【明細から小計を計算する】の式をコピペできます。

「小計」はお金を扱う列なので、Typeを「Price」にします。
あとは日本円の設定を、Type Detailsで次のように設定します。

• Decimal digits：「0」
• Show thousands separator：チェックする
• Currency symbol：「¥」

「消費税額」列

続けて、「Add Virtual Column」をクリックして、Virtual Columnを追加します。
Column nameに「消費税額」と入力し、App formulaの三角フラスコアイコンをクリックします。

Expression Assistantに以下のように入力して、「Save」をクリックします。
「消費税」列は「小計」列の式を少し変えるだけです。[金額]を[消費税額]に置き換えます。

あとは、Typeを「Price」にして、Type Details を「小計」と同じように設定します。

• Decimal digits：「0」
• Show thousands separator：チェックする
• Currency symbol：「¥」

「合計」列

最後に「合計」列を追加します。
「Add Virtual Column」をクリックして、Virtual Columnを追加します。
Column nameに「合計」と入力し、App formulaの三角フラスコアイコンをクリックします。

Expression Assistantに以下のように入力して、「Save」をクリックします。
「合計」列は「小計」列と「消費税額」列を足し合わせるだけです。

これで請求書を作成する準備はできました。試しに1件、請求書を作成してみてください。
以下のように、「小計」「消費税額」「合計」の金額が計算され、表示されるようになります。

以上で明細から請求書の小計、消費税額、合計を計算できるようになりました。
リレーションシップを利用して、親テーブルに関連する子テーブルの値リストを取得して合計するテクニックは様々なアプリで応用が効きます。
もちろん、見積書や納品書にも応用ができるので、この機会に是非習得してください。

次回（第5回）はViewを設定してアプリの見た目を整えます。