今回はExcelのLET関数を使って、会計年度や四半期のデータを動的に、可変的に表示させる方法を説明します。
営業部門や企画部門で業務をしていると、会計年度や四半期単位でデータを扱うことがあります。例えばFY2022Q3(2022年度第３四半期)といった具合です。会計年度や四半期で業績を管理する際に、ExcelのLET関数を使用することで、計算を簡単にすることができます。この記事では、LET関数を初めて知る方を対象に、その基本的な使い方と、四半期や会計年度を動的に表示する方法を説明します。
LET関数の基本
LET関数は、数式内で変数を定義し、それを使って複雑な計算を簡潔に記述することができる関数です。これにより、１つのセルの計算式において、同じ計算を何度も繰り返すことなく、一度定義した変数を再利用することができます。
基本的な構文は次の通りです：
=LET(名前1, 値1, 名前2, 値2, ..., 計算式)- 名前: 定義する変数の名前。
- 値: 変数に代入する値や計算式。
- 計算式: 最終的に実行する計算式。
なお、変数という概念はプログラミングでは頻出です。以下の記事も参照ください。
会計年度と四半期の計算
以下の計算式を用いて、現在の日付から会計年度と四半期を計算し、それを動的に表示する方法を示します。
=LET(
q_calc,0,
y_calc,0,
m, MONTH(TODAY()),
q_now, IF(m <= 3, 4, IF(m <= 6, 1, IF(m <= 9, 2, 3))),
q_tar, MOD((q_now-1+q_calc),4)+1,
y_now, IF(q_now=4, YEAR(TODAY()) - 1, YEAR(TODAY())),
y_q_calc, ROUNDDOWN((q_calc+q_now-4)/4,0),
y_tar,y_now+ y_q_calc+y_calc,
"FY" & y_tar & "Q" & q_tar
)これでは何が何だかわからないと思いますので、少しずつ解説します。
まず以下の２行です。
q_calc,0,
y_calc,0,ここでは、現在の会計年度と四半期に、好きな数値を設定して足したり引いたりできます。例えば、q_calcを-1とすれば、現在から比較して１つ前の四半期を表示したい、y_calcを+2とすれば、現在から２年後の会計年度を表示したい、ということになります。いわゆる、ユーザー側で設定するハイパーパラメータです。
続いて以下では、現在の月を取得しています。これを後続の計算の基準として使います。
m, MONTH(TODAY()),なお、mという変数に代入しているMONTH(NOW())というのもワークシート関数の表現です。詳細は以下を参照ください。
続いて以下では、現在の四半期を計算しています。
q_now, IF(m <= 3, 4, IF(m <= 6, 1, IF(m <= 9, 2, 3))),計算方法は言語化すると以下の通りです。
- 1月から3月: 第4四半期（Q4）
- 4月から6月: 第1四半期（Q1）
- 7月から9月: 第2四半期（Q2）
- 10月から12月: 第3四半期（Q3）
続いて以下では、最終的に表示する四半期を計算させています。
q_tar, MOD((q_now-1+q_calc),4)+1,MOD関数を使用して、現在の四半期からq_calcで設定した調整を加え、それを四半期の範囲（1から4）に収めるようにしています。MOD関数の使い方については以下を参照ください。
続いて以下では、現在の年度を計算しています。現在が第4四半期の場合のみ、会計年度をマイナス1しています。
y_now, IF(q_now=4, YEAR(TODAY()) - 1, YEAR(TODAY())),続いて以下では、四半期の計算による年度の補正をしています。
y_q_calc, ROUNDDOWN((q_calc+q_now-4)/4,0),具体的には、例えば現在が第2四半期だとして、調整で-3したとすると、前会計年度の第４四半期となりますので、会計年度も-1する必要があります。同様に、例えば現在が第1四半期だとして、調整で-5したとすると、会計年度をマイナス2とするようにしています。
なお、ROUNDDOWN関数については以下を参照ください。
続いて以下では、最終的な年度の計算をしています。四半期の調整と会計年度の調整両方を反映して、最終的に会計年度がいくつになるかを計算します。
y_tar,y_now+ y_q_calc+y_calc,最後に、文字列で”FY”及び”Q”をつけて、結合しています。
"FY" & y_tar & "Q" & q_tar実行結果は以下のようになります。
調整値を変更すると、以下のように表記も変更されます。
LET関数のメリット
では、このLET関数にはどのようなメリットがあるでしょうか。それを理解するには、LET関数を使わないとどうなるかを見てみるのが早いです。
以下の通り、LET関数を使わずとも、同じ計算をすることはできます。ただし、どこでどのような計算をしているのか、わかりにくくなります。四半期と会計年度をどこで調整したらいいのか、一目でわかりませんね。
今回はLET関数を使って数式の内容を”わかりやすく”するという観点でした。
LET関数は、冗長な数式を短縮するのにも有効です。今後記事を掲載しますので、お待ちください。
コメント