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

folder_openAppSheet

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

1. 「品目マスタ」Viewを追加する

ここまでテーブルと列の設定を進めてきましたが、まだ請求書アプリのイメージが湧いてこないのではないでしょうか。
サンプルデータを追加することでアプリのイメージが掴めるようになります。

データを追加する前に、必要なViewを追加しておきます。
「品目マスタ」テーブルにデータを追加するためのViewがまだありません。
Viewの設定は次回(第5回)行いますが、「品目マスタ」Viewだけ先取りして作っておきましょう。

AppSheetエディタでアプリをプレビュー表示すると、Primary View にまだ品目、品目マスタがない。

それでは、Viewを設定しましょう。
AppSheetエディタ左側メニューにある「UX」をクリック、タブメニュー「Views」をクリックします。

AppSheetエディタでView を設定する。

「New View」をクリックして新しいView「品目マスタ」を作成します。

「品目マスタ」Viewを新規作成する。

ここでは「品目マスタ」Viewの設定を以下のように変更しました。
View type は「deck」または「Card」の「list」あたりがよいと思いますが、ここでは「deck」にしました。

設定項目
View name 「品目マスタ」
For this data 「品目マスタ」
View type 「deck」
Position 「right」
View Options
Primary header 「品目」
Secondary header 「単価」
Nested table column 「単位」
Actions 「Edit」
Display
Icon 「boxes」

2. サンプルデータを追加してみる

では、サンプルデータを追加してみましょう。
「取引先」と「品目マスタ」を追加します。

AppSheetから取引先を追加する

取引先を1件、追加してみましょう。
「取引先」をクリックして選択、「+」ボタンで入力フォームを開いてください。

AppSheetアプリでタブバーで「取引先」を選択して、取引先フォームを開く。

架空の取引先でよいので、入力後に「Save」をクリックして保存してください。
電話番号等は、スマホでアイコンを触るとAppSheetが相手に電話をかけてしまうので、あり得なそうな番号(9999-9999など)にしておくとよいでしょう。

AppSheetアプリで架空の「取引先」レコードを作成する。

スプレッドシートにはこのように記録され、レコードが1件作成されます。

保存された取引先レコード。

AppSheetから品目マスタを追加する

品目マスタを何件か追加してみましょう。
「品目マスタ」をクリックして選択、「+」ボタンで入力フォームを開いてください。

AppSheetアプリのタブバーで「品目マスタ」を選択する。

「品目」「単価」「単位」「税区分」の順で入力していきます。
例は食品なので税区分が「軽減8%」ですが、通常は「10%」でよいです。
「Save」をクリックして保存します。

AppSheetアプリの「品目マスタ」の入力フォームを表示する。

スプレッドシートにはこのように記録されます。
3〜4件、作成しておくとよいでしょう。

AppSheetデータソースの「品目マスタ」のスプレッドシートにレコードが追加された。

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

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

「請求書」レコードの「ID」と同じ「請求書ID」を持つ「品目」レコードを抜き出して、その金額を合計する。

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関数の使い方を学びましょう。
試しに「請求書」テーブルに3件の請求書を追加してみました。スプレッドシートは以下のようになります。
「ID」列の値が「854b939c」の請求書について、小計を計算してみます。

「品目」テーブルで探したい「ID」

「品目」テーブルで「請求書ID」が「854b939c」の品目を探します。
4〜6行目が探している請求書の品目であることが分かります。

AppSheet、SELECT関数で、探している「ID」と同じ「請求書ID」を特定する。

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

AppSheet、SELECT関数で、探している「請求書ID」の「金額」をリストで取得する。

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

[_THISROW].[ID] = "854b939c"

ということになります。

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

SELECT(
 品目[金額],
 [請求書ID] = [_THISROW].[ID]
)

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

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

SUM関数

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

SUM関数

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

品目明細から小計を計算する式は、以下のようになります。

品目明細から小計を計算する

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

実例でこの式を実行すると、SUM( { 69445, 20834, 55556 } ) で、「145835」が返ります。
この請求書の小計は、145,835円ということになります。

4. 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」をクリックします。
入力する式は、【品目明細から小計を計算する】の式をコピペできます。

AppSheet列設定パネルで「Expression Assistant」に式を入力する。

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

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

AppSheet列設定パネルで、列TYPE「Price」の列を設定する。

「消費税」列

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

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

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

あとは、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件、請求データを作成してみてください。
以下のように、「小計」「消費税額」「合計」の金額が計算され、表示されるようになります。

Virtual Column(仮想列)で合計金額を計算、表示ができる。

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

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

Share Me!

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

関連記事

メニュー