階層型料金割引を計算する

コーディング レベル: 初級
所要時間: 10 分
プロジェクトの種類: カスタム関数

目標

  • ソリューションの機能を理解します。
  • Apps Script サービスがソリューション内でどのように機能するかを理解します。
  • スクリプトを設定します。
  • スクリプトを実行します。

このソリューションについて

顧客に段階的な料金システムを提供する場合、このカスタム関数を使用すると、料金の割引額を簡単に計算できます。

組み込み関数 SUMPRODUCT を使用して段階的な料金計算を行うことはできますが、SUMPRODUCT を使用するのは、このソリューションのカスタム関数よりも複雑で、柔軟性に欠けます。

ティアの料金のサンプルのスクリーンショット

仕組み

階層型料金モデルでは、購入数量に基づいて売上原価が下がります。

たとえば、2 つの階層があるとします。1 つは割引額が $0 ～$500 で、もう 1 つは割引額が $501 ～$1,000 で、もう 1 つは割引額が 20% の割引額です。割引を計算するために必要な合計金額が $700 の場合、スクリプトは最初の $500 に 10%、残りの $200 を 20% 乗算して、合計割引 $90 にします。

指定された合計料金について、スクリプトは階層料金表内の指定された階層をループ処理します。合計価格の階層に含まれる各部分について、その部分に階層に関連付けられたパーセント値を掛けます。結果は、各階層の計算の合計です。

Apps Script サービス

このソリューションでは、次のサービスを使用します。

前提条件

このサンプルを使用するには、次の前提条件を満たす必要があります。

  • Google アカウント（Google Workspace アカウントの場合、管理者の承認が必要になる場合があります）。
  • インターネットにアクセスできるウェブブラウザ。

スクリプトを設定する

下のボタンをクリックして、ティア料金のカスタム関数のスプレッドシートのコピーを作成します。このソリューションの Apps Script プロジェクトは、スプレッドシートに接続されています。
コピーを作成

スクリプトを実行する

  1. コピーしたスプレッドシートの 16 行目の表に、Software as a Service（SaaS）プロダクトの価格計算のサンプルが表示されます。
  2. 割引額を計算するには、セル C20 に「=tierPrice(C19,$B$3:$D$6)」と入力します。最終価格はセル C21 で更新されます。小数点カンマを使用するロケーションでは、代わりに =tierPrice(C19;$B$3:$D$6) の入力が必要になる場合があります。

コードを確認する

このソリューションの Apps Script コードを確認するには、下の [ソースコードを表示] をクリックします。

ソースコードを表示

Code.gs

solutions/custom-functions/tier-pricing/Code.js
// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/custom-functions/tier-pricing

/*
Copyright 2022 Google LLC

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

/**
 * Calculates the tiered pricing discount.  
 *  
 * You must provide a value to calculate its discount. The value can be a string or a reference
 * to a cell that contains a string.
 * You must provide a data table range, for example, $B$4:$D$7, that includes the 
 * tier start, end, and percent columns. If your table has headers, don't include
 * the headers in the range.
 * 
 * @param {string} value The value to calculate the discount for, which can be a string or a 
 * reference to a cell that contains a string.
 * @param {string} table The tier table data range using A1 notation.
 * @return number The total discount amount for the value.
 * @customfunction
 *  
 */
function tierPrice(value, table) {
  let total = 0;
  // Creates an array for each row of the table and loops through each array.
  for (let [start, end, percent] of table) {
  // Checks if the value is less than the starting value of the tier. If it is less, the loop stops.
    if (value < start) {
      break;
    }
  // Calculates the portion of the value to be multiplied by the tier's percent value.
    let amount = Math.min(value, end) - start;
  // Multiplies the amount by the tier's percent value and adds the product to the total.
    total += amount * percent;
  }
  return total;
}

改造

カスタム関数は、必要に応じて編集できます。以下は、カスタム関数の結果を手動で更新するためのオプションです。

キャッシュに保存された結果を更新する

組み込み関数とは異なり、Google はパフォーマンスを最適化するためにカスタム関数をキャッシュに保存します。そのため、カスタム関数内で何か（計算中の値など）を変更しても、すぐには更新されない場合があります。関数の結果を手動で更新する手順は次のとおりです。

  1. [挿入] > [チェックボックス] をクリックして、空のセルにチェックボックスを追加します。
  2. チェックボックス付きのセルを、カスタム関数の追加パラメータとして追加します。たとえば、セル D20 にチェックボックスを追加する場合は、セル C20 の tierPrice() 関数を =tierPrice(C19,$B$3:$D$6,D20) に更新します。
  3. チェックボックスをオンまたはオフにして、カスタム関数の結果を更新します。

協力者

このサンプルは、Google Developer Experts の協力により Google が保守しています。

次のステップ