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

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

目標

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

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

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

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

階層別料金の例のスクリーンショット

仕組み

階層型料金モデルとは、購入した数量に応じて商品やサービスの費用が下がるモデルです。

たとえば、0 ～ 500 ドルの範囲で 10% 割引になる階層と、501 ～ 1,000 ドルの範囲で 20% 割引になる階層の 2 つがあるとします。割引を計算する必要がある合計価格が $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 コードを確認するには、以下の [ソースコードを表示] をクリックします。

ソースコードを表示

コード.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 デベロッパー エキスパートの協力を得て Google が管理しています。

次のステップ