名前付き関数で受注金額の計算を共通化する

LWP | 名前付き関数で受注金額の計算を共通化する

名前付き関数で受注金額の計算を共通化する

図解1|同じ計算を別の表でも使いたい

三つの受注明細で、数量と取引単価から金額を求める共通の計算を取り出す図

図1|明細ごとに変わる値と、変わらない計算の形を分ける。

解説1|同じ計算を別の表でも使いたい

ストーリー

フナさん「受注表ごとに、数量と単価を掛けています。同じ計算ですが、何の単価かが分かるようにしたいですね。」

ココさん「数量と単価を受け取る関数にしましょう。計算に名前を付けられます。」

全国規模の中華食材卸・淡水社の架空の例を使う。一行は一つの受注明細で、取引単価はその明細の販売単位に対応する。受注金額は数量×取引単価とし、税・送料・値引き・端数処理は扱わない。

入力例

明細ID

商品コード

数量

取引単価

受注金額

O001-1

P001

6

1200

7200

O001-2

P002

4

800

3200

O002-1

P001

3

1200

3600

P001は干ししいたけ500gを袋で、P002は豆板醤1kgを本で販売する。数量と単価の単位は行ごとに対応させる。Googleスプレッドシートの名前付き関数を使い、同じ形の計算を複数のセルから呼び出す。

図解2|数量と取引単価を引数に分ける

数量と取引単価を引数として受け取り、掛け算から受注金額一つを返す図

図2|受け取る二つの値と、返す一つの値を決める。

解説2|数量と取引単価を引数に分ける

ストーリー

ココさん「今回はquantityが数量、unit_priceが取引単価です。関数の中では、この二つの名前で計算します。」

フナさん「6と1200を渡したら7200ですね。販売単位が合っているかは、入力する側でも確認します。」

名前付き関数の引数は、呼出し時に値を受け取るための名前である。quantityやunit_priceはこの記事で付ける名前であり、Sheetsの組込み関数名ではない。

関数の役割

項目

今回の定義

関数名

ORDER_AMOUNT

第1引数

quantity:数量

第2引数

unit_price:数量と同じ販売単位の取引単価

数式の定義

=quantity*unit_price

戻す値

受注金額一つ

名前付き範囲は特定のセル範囲に名前を付ける仕組みであり、今回の引数を持つ名前付き関数とは役割が異なる。定義内にC2やD2を直接書いて固定するのではなく、明細ごとに変わる値を引数として渡す。

図解3|計算式に名前と引数を登録する

データメニューの名前付き関数から、ORDER_AMOUNTと二つの引数と計算式を登録する図

図3|関数名、引数の名前、数式の定義を対応させる。

解説3|計算式に名前と引数を登録する

ストーリー

ココさん「『データ』から『名前付き関数』を開きます。引数の名前は、式の中でも同じ綴りにします。」

フナさん「quantityとunit_priceですね。説明にも、税などを含めない受注金額だと残しておきます。」

名前付き関数のパネルで、新しい関数を追加する。関数名にORDER_AMOUNT、引数のプレースホルダにquantityとunit_price、数式の定義に次の式を入れる。引数の順番は数量、取引単価とする。

数式の定義

=quantity*unit_price

登録時に確認すること

確認箇所

内容

関数名

ORDER_AMOUNT。同名の定義がないことを確認

引数

quantity、unit_priceの順

数式

登録した引数名を使って掛け算する

説明

販売単位をそろえ、税・送料・値引きを含めない受注金額

入力内容を確認して作成する。定義に直接含めたセル範囲は絶対参照として扱われるため、呼出しごとに対象を変えたい値や範囲は、今回のように引数で受け取る。

図解4|二つの明細から同じ関数を呼ぶ

E2とE3で同じORDER_AMOUNTを呼び、C列とD列の各行の値から金額を求める図

図4|同じ関数を使っても、渡す値が違えば結果が変わる。

解説4|二つの明細から同じ関数を呼ぶ

ストーリー

フナさん「関数の名前は同じですが、2行目は6と1200、3行目は4と800を渡すんですね。」

ココさん「はい。呼出し側ではセルを指定し、定義の中では引数名で計算します。」

最初の表をA1:E4に置く。C列を数量、D列を取引単価、E列を受注金額とし、E2へ次の式を入力する。

入力する数式

=ORDER_AMOUNT(C2,D2)

E3へコピーすると、参照はC3とD3へ変わる。E4ではC4とD4になる。関数の定義は一つで、各セルから渡す入力は別々である。

計算結果

呼出しセル

渡す値

戻る受注金額

E2

quantity=6、unit_price=1200

7200円

E3

quantity=4、unit_price=800

3200円

E4

quantity=3、unit_price=1200

3600円

今回は掛け算なので引数の値を逆にしても数値が同じになる。しかし、引数の意味まで同じになるわけではない。説明と引数名に沿い、数量と取引単価の順で渡す。名前を付けたことだけで、単位や入力条件の間違いを検出できるわけではない。

図解5|直接書いた式と結果を照合する

名前付き関数とC列掛けるD列の直接式を並べ、三つの明細で一致を確認する図

図5|再利用する式は、期待値と直接の計算でも確かめる。

解説5|直接書いた式と結果を照合する

ストーリー

ココさん「作れたので終わりにせず、元の掛け算とも比べましょう。」

フナさん「3明細とも一致して、合計も14000円です。単位は明細ごとに確認しますね。」

F列に直接書いた掛け算、G列に一致を調べる式を置く。F2は=C2*D2、G2は=E2=F2として4行目までコピーする。名前付き関数の結果だけでなく、入力のセルと期待金額を照合する。

結果の比較

明細ID

名前付き関数

直接の掛け算

一致

O001-1

7200

7200

TRUE

O001-2

3200

3200

TRUE

O002-1

3600

3600

TRUE

合計は14000円になる。この一致は、同じ入力に対して今回の二つの計算が一致するという確認である。入力した取引単価そのものが正しいと証明するものではない。また、今回の関数には数量の正負などを検査する処理を入れていない。入力条件の確認と、計算式の共通化を分けて扱う。

図解6|別ファイルへの取り込みと定義の更新を区別する

名前付き関数を別ファイルへインポートしても、後日の定義変更は自動同期しない関係

図6|別ファイルへ取り込んだ定義は、参照元と常時つながる関数ではない。

解説6|別ファイルへの取り込みと定義の更新を区別する

ストーリー

フナさん「別の受注表にも取り込めました。元の定義を修正したら、こちらも変わりますか。」

ココさん「自動では同期しません。修正版を使うときは、取り込み先の同名関数を確認して再度インポートします。」

名前付き関数のパネルから関数のインポートを選び、参照元のファイルと必要な関数を選択する。同名関数が取り込み先にある場合は上書きの対象になるため、内容を確認して進める。

取り込み元を後日編集しても、別ファイルへ取り込んだ定義が自動的に更新されるわけではない。同じ関数名でも定義が異なる場合がある。説明や変更記録を残し、再取り込み後は代表的な入力で結果を照合する。

まとめ

名前付き関数は、計算の形に名前を付け、変わる値を引数として受け取る仕組みである。定義と呼出し、入力条件、別ファイルへの取り込みを分けて管理する。

今回の仕掛け

ORDER_AMOUNTの定義は一つで、受け取る値は明細ごとに変わる。関数名は、業務で何を計算する式かを読み取る手掛かりになる。

出典

『Googleスプレッドシートの教科書』V056、8.4.4「名前付き関数を作成・編集する」、8.4.5「名前付き関数を別のファイルで使う」。Google公式 名前付き関数。