· 

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

SELECT関数とSUM関数で合計金額を計算する

 

このブログでは、請求書アプリでAppSheetの使い方を学びます。

 

前回(第3回)はリレーションシップを使用してテーブル間の親子関係を設定しました。

今回はSELECT関数とSUM関数を使用して、品目明細から請求金額の合計を計算します。

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

 

ここまでテーブルと列の設定を進めてきましたが、まだ請求書アプリのイメージが湧いてこないのではないでしょうか。

サンプルデータを入力することでアプリのイメージが掴めるようになります。

 

データ入力する前に、必要なViewを追加しておきます。

「品目マスタ」テーブルにデータを保存するためのViewがまだありません。

Viewの設定は次回(第5回)行いますが、「品目マスタ」Viewだけ先取りして作っておきましょう。

 

Primary View にまだ品目、品目マスタがない。

 

それでは、Viewを設定しましょう。

左側メニューにある「UX」をクリック、タブメニュー「Views」をクリックします。

 

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 「品目」
Scondary header 「単価」
Nested table column 「単位」
Actions 「Edit」
Display
Icon 「boxes」

2. サンプルデータを入力してみる

 

では、サンプルデータを入力してみましょう。

「取引先」と「品目マスタ」を追加します。

 

取引先

 

 取引先を1件、追加してみましょう。

「取引先」をクリックして選択、「+」ボタンで入力フォームを開いてください。

 

「取引先」を選択して、取引先フォームを開く。

 

架空の取引先でよいので、入力後に「Save」をクリックして保存してください。

電話番号等は、スマホでアイコンを触ると電話がかかってしまうので、あり得なそうな番号(9999-9999など)にしておくとよいでしょう。

「取引先」レコードを作成する。

 

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

 

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

品目マスタ

 

品目マスタを何件か追加してみましょう。

「品目マスタ」をクリックして選択、「+」ボタンで入力フォームを開いてください。

 

「品目マスタ」を選択する。

 

「品目」「単価」「単位」「税区分」の順で入力していきます。

例は食品なので税区分が「軽減8%」ですが、通常は「10%」でよいです。

「Save」をクリックして保存します。

 

「品目マスタ」の入力フォーム。

 

スプレッドシートにはこのように記録されます。

3〜4件、作成しておくとよいでしょう。

 

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

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

 

請求書には品目明細の金額を合計して、請求金額や小計などを表示する欄があります。

「請求書」テーブルと「品目」テーブルにはリレーションシップが設定されています。

「請求書」レコードの「ID」と同じ「請求書ID」を持つ「品目」レコードを抜き出して、その金額を合計すれば小計などを計算できます。

 

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

SELECT関数

 

ここで、SELECT関数とSUM関数を用いて、合計金額を計算する式を設定します。

 

SELECT関数は、列から値をリストを取得できる関数です。

第1引数にテーブル名と列名を設定し、第2引数に条件式を設定します。

第3引数を「TRUE」にすると重複する値を削除します。第3引数は省略可です。

 

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

値のリストというのは、PHPやJavaScriptのようなスクリプト言語でいうところの配列です。

AppSheetには配列(Array)という用語がなく、代わりにリスト(List)という用語を使用します。

 

リストは「{ }」(中括弧、波括弧)内に、数値や文字列を「,」(コンマ)で区切って並べます。

文字列の場合は、「""」(ダブルクォーテーション)で囲みます。

 

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

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

 

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

では、請求書アプリの実例でSELECT関数の使い方を学びましょう。

「請求書」テーブルに3件の請求書を追加してみました。スプレッドシートは以下のようになります。

「ID」列の値が「854b939c」の請求書について、小計を計算してみます。

 

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

 

「品目」テーブルで「請求書ID」が「854b939c」の品目を探します。

4〜6行目が探している請求書の品目であることが分かります。

 

探している「ID」と同じ「請求書ID」を特定する。

 

小計を計算したいので、欲しいのは「金額」列の値のリストです。

{ 69445, 20834, 55556 }が欲しい値のリストになります。

 

 

以上の実例をSELECT関数で式にすると以下のようになります。

「_THISROW」というのは「請求書」テーブル3行目のレコードのことを指します。つまり、

 

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

 

ということになります。

 

 

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

 

SELECT(

  品目[金額],

  [請求書ID] = [_THISROW].[ID]

)

 

 

式の意味は以下のようになります。

返ってくる値は、{ 69445, 20834, 55556 } です。

 

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

SUM関数

 

SELECT関数で取得した値リストを合計すれば小計を計算できます。

数値の合計にはSUM関数を使用します。

 

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

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

 

 

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

 

SUM(

  SELECT(

    品目[金額],

    [請求書ID] = [_THISROW].[ID]

  )

)

 

 

実例でこの式を実行すると、LIST( { 69445, 20834, 55556 } ) で、「145835」が返ります。

この請求書の小計は、145,835円ということになります。

4. Virtual Column に計算式を設定する

 

実は、「請求書」テーブルには「小計」「消費税」「合計」を表示する列がありませんでした。

「小計」「消費税」「合計」は、Virtual Column で表示することにします。

 

Virtual Column とは文字通り「仮想的な列」です。

品目明細を追加、更新、削除したときに、「請求書」テーブルのスプレッドシートを更新しなくても「小計」「消費税」「合計」の計算結果を更新できるため、ここでは、Virtual Columnを使用します。

 

左側メニュー「Data」をクリックします。

タブメニュー「Columns」をクリックして、「請求書」をクリックして列設定パネルを開きます。

 

「Add Virtual Column」をクリックして、Virtual Columnを追加します。

 

「小計」列

 

Virtual Column 設定パネルが開きます。

Column nameに「小計」と入力します。

 

Virtual Column(仮想列)を設定する。

 

次に、App formula の三角フラスコアイコンをクリックします。

Expression Assistant に以下のように入力して、「Save」をクリックします。

入力する式は、【品目明細から小計を計算する】の式をコピペできます。

 

「Expression Assistant」に式を入力する。

 

「小計」はお金を扱う列なので、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」をクリックします。

「消費税」列は「小計」列の式を少し変えるだけです。[金額]を[消費税額]に置き換えます。

 

「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」をクリックします。 

「合計」列は「小計」列と「消費税額」列を足し合わせるだけです。

 

Expression Assistant を設定する。

 

これで請求書を作成する準備はできました。試しに1件、作成してみてください。

以下のように、「小計」「消費税額」「合計」の金額を計算され、表示されるようになります。

 

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

 

以上で品目明細から請求書の小計、消費税額、合計を計算できるようになりました。

リレーションシップを利用して、親テーブルに関連する子テーブルの値リストを取得して合計するテクニックは様々なアプリで応用が効きます。

もちろん、見積書や納品書にも応用ができるので、この機会に是非習得してください。

 

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

 

アプリを作る前に実際に試してみたい方は、アプリスイートが作ったサンプルアプリを7日間無料体験ができます。

こちらからお問い合わせください。