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

folder_openAppSheet

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

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

Switch to the legacy editor」で旧エディタに切り替える。

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

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

AppSheetではRefを使って、明細行の金額を合計できる。

SELECT関数

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

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

SELECT関数

SELECT( テーブル名 [列目], 条件式) => 条件に合致するレコードの列の値をリストで返す

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

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

リスト(LIST)の例
  • { 100, 200, 300 }
  • {"香川",  "徳島", "愛媛", "高知"}

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

LIST関数
  • LIST(100, 200, 300)
  • LIST("香川",  "徳島", "愛媛", "高知")

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

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

AppSheetエディタ左側メニュー「Data」をクリック、「Tables」タブに移動して、テーブルをクリックする。

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

AppSheetではデータソースとしてGoogleスプレッドシートを使用している。

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

請求書の「ID」に該当する明細を探す。

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

請求書のIDに該当する明細の金額をリストで取得する。

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

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

ということになります。

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

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

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

SELECT関数の意味
  • 「明細」テーブルの「金額」列から値のリストを取得する
  • 「明細」テーブルの「請求書」列の値が、「請求書」テーブルの現在レコード行の「ID」列と同じ値である

SUM関数

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

SUM関数

SUM( リスト ) => リストの値の合計する

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

明細から小計を計算する

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を追加します。

AppSheetでは明細行の金額合計はVirtual Column(仮想列)を使用するのが便利。

「小計」列

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

AppSheet列設定パネルでVirtual Column(仮想列)を設定する。

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

SELECT関数で取得したリストをSUM関数で合計する。

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

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

列TYPE「Price」を日本円にする。

「消費税額」列

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

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

消費税額の計算もSELECT関数で取得したリストをSUM関数で合計する。

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

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

「合計」列

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

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

AppSheet列設定パネルで、「Expression Assistant」を設定する。

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

請求書を追加する時に、小計、消費税額、合計が計算できる。

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

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

Share Me!

前の投稿
AppSheetで請求書アプリ(第3回)リレーションシップで親子関係を設定
次の投稿
AppSheetで請求書アプリ(第5回)Viewでアプリの見た目を整える

関連記事

メニュー