はじめに
この記事はLinkbal Advent Calendar 2019の24日目の記事です。
昨日は、昨年に引き続きリンクバルで一番プログラミングできると私が思っている方の自社のマッチングアプリでよく使わているArray関数ランキング ＆ 使われ方の紹介です。
スプレッドシートで売上管理をするときに、こんな感じにほとんどの項目を数式を使って自動で出したいことがありますよね？...ありませんか？
この記事では、どういう考え方で出しているかと、そこで使う数式や関数を紹介します。
あまり考えずに書いていったら関数の紹介が多くなったので、「スプレッドシートよく使うけど関数は全然知らない！」って方におすすめです。
GASは使っていませんので、プログラミング言語は一切ないです！（ただし数式がプログラムチックです）
この記事の考え方や数式は、スプレッドシートの関数をあまり知らない私が調べながら出したものなので、効率が悪い可能性があります。
もっといいやり方があれば、お気軽にコメントくださると嬉しいです！
スプレッドシートでできるだけミス無く自動入力で売上管理がしたい
スプレッドシートで、月額定額モデルのサービスの売上を、初月の日割り売上を含めて管理するとします。
正直、売上管理はどこかのツールを使ってきちんとしたほうがいいと思います（普通がどうかは知りません）。
が、ビジネスが始まったばかりで顧客がほとんどいないとか、そこにお金使えないといったときはとりあえずExcelやスプレッドシート使うことがあると思います。
そんなときは、関数をゴリゴリ使ってうまいこと出しましょう！
・・・GASを使ったほうがいいと最初は思いましたが、書くのが面倒だったのと、スプレッドシートで意外といけたので、使わないです。
この記事で扱う関数
記事内で簡単に説明しています。
リンクはすべて公式のドキュメントです。
IFとANDも使っていますが、説明はしていません。
前提
とある企業向けのWebサービスがあるとして、1アカウントごとに月額100,000円（税抜）のサブスクリプションとします。
| 項目 | 内容 |
|---|---|
| 利用料金 | 1アカウント 100,000円 / 月 |
| 契約期間 | 1年 |
| 初月日割り | あり |
| 小数 | 切り捨て |
| 支払いサイクル・支払いサイト | 月末締め翌月末払いの30日 |
シートの作り
こちらからシートが見れます。
https://docs.google.com/spreadsheets/d/1UK0MWuP8SQDgfqCHie1WctW3vb8wWqmGHRfG9h03ofc/edit?usp=sharing
会社名、アカウント数、月間売上、料金発生日、初回入金日（なくてもいい）、初月日数、初月利用日数、初月売上、月ごとの売上からなります。
1行目にその月の合計金額、2行目にヘッダー、3行目以降に各クライアント情報を載せます。
このシートでは、会社名、アカウント数、料金発生日以外はすべて数式で自動入力させます。
自動入力の列の数式を説明します。
月間売上（＝月額利用料金）
C3に以下の式を入れます。
=ARRAYFORMULA(IF(ISBLANK(B3:B), "", 'マスタ'!A$2*B3:B))
単純に アカウント数 * 月額利用料金 です。
月額利用料金は、「マスタ」という別シートを用意して、A2に金額を入れています。
利用料金の参照もっといいやり方あると思いますが、ひとまずセルに数字直打ちよりマスタを用意して一括変更を楽にしておいた方が良いはず。
ISBLANK関数
ISBLANK 関数は、そのセルが空かどうかを判定し、TRUE / FALSEを返します。
上記だと、B列にアカウント数が入っていない行は、C列も空のままにするために入れています。
これがないと、データがない行も0と表示されて邪魔になります。
この関数は条件付き書式や簡単な数式で、かなり使うため覚えておくとよいです！
ARRAYFORMULA関数
ARRAYFORMULA 関数は配列数式を使用します。
説明が難しいですが、 =IF(ISBLANK(A[n]:A), "", 'マスタ'!A$2*B[n]) ※nは行番号 の式がすべてのB列に入ると思ってください。
これを使う理由は、シートを軽くするためです。セルひとつずつに数式を入れるより、ARRAYFORMULA を使った方が処理が早くなるそうです。
データが多い＆処理が複雑ほど効果をはっきするはずです。
個人的に、スプレッドシートに用意されている関数の中でも、名前が屈指のかっこよさだと思います。
ARRAYFORMULA - ドキュメント エディタ ヘルプ
初回入金日
この列は売上を管理するだけなら別になくてもよいのですが、入金管理もするときは使います。
E3に以下の式を入れます。
=ARRAYFORMULA(IF(ISBLANK(D3:D), "", EOMONTH(D3:D, 1)))
料金発生月の最終日を出しています。
EOMONTH関数
EOMONTH 関数は、第一引数を第二引数の前まとは後ろの月の最終日の日付を返します。
第二引数が1だと一ヶ月後、6だと半年後、-1だと一ヶ月前の最終日が返ってきます。
支払いサイクルが月末締め翌月末払いなので、料金発生日が12月の1日だろうと31日だろうと、支払い（締切り）日は1月31日です。
初月日数・初月利用日数・初月売上
E列からH列は、初月売上を出すための列です。
初月売上のセルにすべてぶっこんでもいいですが、わかりづらくなるので独立した列を用意しておきます。
まず、日割りは 月額利用料金 × 利用日数 / カレンダー日数 で出せます。
一番手作業でやりたくない部分ですね笑
ミスが起こりやすいですし、かなりめんどうです。
まず、料金発生月の日数を出しましょう。
F3に以下の式を入れます。
=ARRAYFORMULA(IF(ISBLANK(D3:D), "", DAY(EOMONTH(D3:D, 0))))
その月の日数 = 最終日の日付 なので、料金発生月の最終日をEOMONTHで出し、その日をDAYで出します。
次に料金発生月の利用日数です。
G3に以下の式を入れます。
=ARRAYFORMULA(IF(ISBLANK(D3:D), "", F3:F-DAY(D3:D)+1))
その月の日数 - 最終日の日付 + 1 で、利用日数が出せます。
最後に初月売上です。
H3に以下の式を入れます。
=ARRAYFORMULA(IFERROR(ROUNDDOWN(C3:C*G3:G/F3:F)))
改めて、日割りの計算式は 月額利用料金 × 利用日数 / カレンダー日数 です。
そのまま各セルの値を入れて計算しているだけです。
計算結果から ROUNDDOWN で小数点以下を切り捨てます。
DAY関数
DAY 関数は、引数の日付を数値で返します。
DAY("2019/12/15") なら15、DAY("2019/12/31") なら31です。
ROUNDDOWN関数
ROUNDDOWN 関数は、第一引数を第二引数で指定した小数点以下の桁数で小数を切り捨てます。
第二引数は省力すると0になるため、小数点以下切り捨てになります。
IFERROR関数
IFERROR 関数は、第一引数がエラーになるときに第二引数を表示します。
例えば IFERROR(1/0, "-") だと0で割ろうとしてエラーになるため、-が表示されます。
第二引数を省力すると空になります。
このシートでは IFBLNAK 的な使い方をしています。
IFBLNAK でもいいですが、IFERROR の方が短いのと、IFBLNAK を使っているセルはエラーにならずに0になるため、使い分けています。
この関数も便利なので覚えておくと重宝します。
月ごとの売上
このシートで一番複雑な数式かつ微妙な処理だと思います。
以下の数式をJ3以降の全セルに入れます。
※ARRAYFORMULA ではできませんでした
=IFERROR(
IFS(
AND(
YEAR($D3)&MONTH($D3)=YEAR(J$2)&MONTH(J$2), ISBLANK(OFFSET(J3, 0, -1))
), $H3,
(DATEDIF(YEAR($D3)&"/"&MONTH($D3)&"/"&"1", J$2, "M")<12), $C3
)
)
長いので改行とインデントをしています（違うインデントや改行にしても↑に整形されるので、おそらく正しいインデント）。
まず、出したい値が以下の3パターンあります。
- 初月売上
- 初月以外の売上
- 売上がない
そのため、IFS 関数で3パターンを出し分けています。
IFS関数
IFS 関数は、複数条件を処理します。
プログラミング言語の if, else if と捉えるとわかりやすいです。
第一引数を満たしたら第二引数を表示、第三引数を満たしたら第四引数を表示、第五引数を満たしたら、、、です。
else 的なものは、最後の条件を TRUE にすれば表現できます。
スプレッドシートの IFS(A1 >= 60, "60以上", A1 >=40, "40以上", TRUE, "40未満") はプログラミング言語だと以下のようなコードになります。
if(cell_a1 >= 60) {
print('60以上')
} else if(cell_a1 >= 40) {
print('40以上')
} else {
print('40未満')
}
初月売上を表示する条件
以下の2つを満たしたら初月売上として、AND 関数に条件を入れています。
- 初回入金日の年月とその売上月の年月が等しい
- その前の月の売上がない
AND(
YEAR($D3)&MONTH($D3)=YEAR(J$2)&MONTH(J$2),
ISBLANK(OFFSET(J3, 0, -1))
)
初回入金日の年月とその売上月の年月が等しい
まず、以下の式で、初回入金日の年月と、その売上月の年月が等しいかをチェックします。
YEAR($D3)&MONTH($D3)=YEAR(J$2)&MONTH(J$2)
YEAR と MONTH を & で連結して比較しています。
正直いい方法とは思っていません。
他に DAYS 関数の結果が売上月の日数以下などを思いつきましたが、それはより微妙だと思います。
その前の月の売上がない
次に、前の月（左のセル）の売上がないかをチェックします。
ISBLANK(OFFSET(J3, 0, -1))
OFFSET 関数でそのセルの一つ左のセルを見て、ISBLANK で空かどうかをチェックします。
始まりはJ列にして、I列は空けておきます。
YEAR関数
DAY関数の年版です。
MONTH関数
DAY関数の月版です。
OFFSET関数
OFFSET 関数は、第一引数のセルの位置から第二引数の数値分移動した行かつ第三引数の数値分移動した列のセルへの参照を返します。
OFFSET(A1, 10, 10) だと、K11セルへの参照になります。
初月以外の売上（売上があるかどうか）
初月以外は、料金発生日からその月の初日までの間が12ヶ月未満なら、売上ありとして月間売上を表示します。
(DATEDIF(YEAR($D3)&"/"&MONTH($D3)&"/"&"1", J$2, "M")<12), $C3
料金発生日の年と月と1を & で連結し、料金発生日の初日を作ります（このやり方は無理やりな気がします。。。）。
それとその月の初日までの間を DATEDIF で月単位で出し、それが12未満なら月間売上を表示します。
DATEDIF
DATEDIF 関数は、第一引数と第二引数の差の日数や年数を計算します。
第三引数で年、月、日、その他もろもろを指定できます。
契約終了月に色をつける
契約終了月はわかりやすくしたいですよね。
終了月の売上の背景を、条件付き書式を使って赤色にしましょう。
=(DATEDIF(YEAR($D3)&"/"&MONTH($D3)&"/"&"1", J$2, "M")=11)
初月以外は、料金発生日からその月の初日までの間が12ヶ月未満なら、売上ありとしているので、間が11ヶ月なら最後の月ですよね。
同じ用に計算し、月で計算した差が11なら赤色をつけています。
おわりに
GASを書かなくても、思った以上にスプレッドシートだけでやりたいことはできたりしますので、色々関数を探してみてください！
なお、このシートでは一括払いはないものとしていますが、一括払いもありで、一括か月額払いかをシートに入れていてその入金管理をする、、、みたいなめんどうなものでも、スプレッドシートでもちろんできます！
最初に書きましたが、この記事の考え方や数式は、スプレッドシートの関数をあまり知らない私が調べながら出したものなので、効率が悪い可能性があります。
もっといいやり方があれば、本当にお気軽にコメントくださると嬉しいです！
INDIRECT関数やVLOOKUP関数、HLOOKUP関数、ADDRESS関数あたりを使ったらもっとスマートにできるのかなーと考えています。
深く考えていないので、できるかは知りません。
月ごとの売上を ARRAYFORMULA を使って出したかった。。。というより全体的にもっといい方法で出したい。。。
参考
「こういうことできないかな」と思ったらググればよいですが、「いい方法ないかな」というときは関数リストで探してみるのがおすすめです。
思ってもみなかった処理ができる関数があるので、いろいろな方法が思い浮かぶきっかけになります。