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

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

目標

  • ソリューションの機能について理解する。
  • ソリューション内での Apps Script サービスの役割を理解する。
  • スクリプトを設定します。
  • スクリプトを実行します。

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

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

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

階層型料金のサンプルのスクリーンショット

仕組み

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

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

ソースコードを表示

コード.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 によって管理されています。

次のステップ