続きました。エクセルの数式のお話です。
今回の記事もエクセルのちょっとした小技を紹介して、同じ悩みを抱く人に届いて、その手助けになればいいなと思います。
では早速
今回は2点の問題解決をしようと思います。
経理担当者なら分かると思うのですが、
毎月の会計締めって翌月の月初あたりですよね。
例えば7月の会計締め日は8月5日、6日ごろかと思います。
では実際に伝票の起票やエクセルでの資料作成で必要な日付って先月の日付ですよね。
そこで使えるのが Eomonth関数
数式としては Eomonth(開始日,月)
ここでの『開始日』にはToday関数を入れてもいいし、
作業する日を別のセルに手入力しそこを読みにいく方式で可
次に『月』には、求めたい日付が開始日から見た翌月か先月かを指定してあげる
翌月の場合は 1、翌々月の場合は 2、先月の場合は -1、先々月の場合は -2
※因みに、開始日の最終日を求めたい場合は 0を入力
例：作業日が2021年8月2日
今日が2021年8月4日とした場合の翌月、先月、当月の末はいつか
| 実際の表示 | 関数 | |
| ①作業日 | 2021年8月2日 | |
| ②Today関数 | 2021年8月4日 | =TODAY() |
| ①翌月 | 2021年9月30日 | =EOMONTH(C2,1) |
| ②翌月 | 2021年9月30日 | =EOMONTH(C3,1) |
| ①先月 | 2021年7月31日 | =EOMONTH(C2,-1) |
| ②先月 | 2021年7月31日 | =EOMONTH(C3,-1) |
| ①当月 | 2021年8月31日 | =EOMONTH(C2,0) |
| ②当月 | 2021年8月31日 | =EOMONTH(C3,0) |
答えはすべて一緒ですが、上のような形になる。
※ちなみに、関数だけを表示している列ですが、これも数式で表示してます。
数式は使っている関数は Formulatext関数
これは数式を文字列で表示させることができる関数
自分が使った関数の説明や、自分以外の人が作った関数の理解をするときに利用すると便利な関数
話を戻して、Eomonth関数の中にToday関数を突っ込めば、
今日を開始日と指定できて、エクセルを開くたびに翌月などの最終日を求めることができる。
Eomonth(Today(),**) 〔**は翌月・先月・当月をそれぞれ指定〕
上の表である通り、一つのセルにToday関数を入れてそこを読み込めば事足りるけど、
Today関数を入れるためだけのセルを作らないといけないので
それさえ無くしたいぜ！っていう方は Eomonthの中にTodayを入れることをお勧めします。
それでは2つめ
転職して今の会社に入ってからそれまで使ってなかったExcelをフル活用することになって
一番初めに教えてもらった関数がVlookup関数でした。
（前職ではほぼSum関数しか使ってこなかった・・・・（ ＾ω＾）・・・）
Vlookup関数と言えば
例を用いて説明すると
| 4月 | 5月 | 6月 | 第1四半期 | 7月 | |
| 売上高 | 10,000 | 12,000 | 11,000 | 33,000 | 9,000 |
| 売上原価 | 6,000 | 7,000 | 6,500 | 19,500 | 5,000 |
| 売上総利益 | 4,000 | 5,000 | 4,500 | 13,500 | 4,000 |
| 販管費 | 2,000 | 2,500 | 2,200 | 6,700 | 1,800 |
| 営業利益 | 2,000 | 2,500 | 2,300 | 6,800 | 2,200 |
| 営業外損益 | 3,000 | 5,000 | 2,000 | 10,000 | 2,500 |
| 経常利益 | 5,000 | 7,500 | 4,300 | 16,800 | 4,700 |
| 特別損益 | 3,000 | 3,500 | 1,500 | 8,000 | 2,000 |
| 税引前当期純利益 | 8,000 | 11,000 | 5,800 | 24,800 | 6,700 |
| 法人税等 | 2,400 | 3,300 | 1,700 | 7,400 | 2,000 |
| 当期純利益 | 5,600 | 7,700 | 4,100 | 17,400 | 4,700 |
こんな感じの損益計算書の推移表があるとしましょう。（8月以降も数値は続いていくと仮定）
〔セルの開始はA1からF12とする〕
この表から6月の数値を求めたいとなった時に、まず下のような表を用意して、
〔こちらのセルの開始は H1からI12とする〕
| 6月 | |
| 売上高 | |
| 売上原価 | |
| 売上総利益 | |
| 販管費 | |
| 営業利益 | |
| 営業外損益 | |
| 経常利益 | |
| 特別損益 | |
| 税引前当期純利益 | |
| 法人税等 | |
| 当期純利益 |
売上高は Vlookup(H2,A1:F12,4,FALSE) となります。
ただし、これではセルをコピーしてそのほかの行に数式貼り付けができないので
番地を指定してあげる必要があります。
ここでは検索する範囲を指定する必要があり
A1:F12 ➡ $A$1:$F$12 にしなければなりません。
指定の仕方は該当の箇所を選んでF4をポチポチ押して指定してあげて
そうすることで数式は Vlookup(H2,$A$1:$F$12,4,FALSE)になって
そのセルをコピーしてそのほかの行に数式貼り付けができます。
ここまでがVlookupの説明で須賀、Vlookupの問題点として
列の指定に数値を入れる必要があること。
これって意外と面倒だと感じません？
列関数であるColumn関数を使ったりなんかして指定はできるけど、なんかなーって人に
Index関数とMatch関数をおすすめしたい。
数式はこんな感じ
検索する範囲は、上記の例で説明すると実際に数値が入力されている B2:F12（10,000～4,700）を指定
次に検索範囲の何行目か何列目かを指定する際に、Match関数を使う
Match関数の数式はこちら
何行目かの検索範囲には A2:A12（売上高～当期純利益）を指定し、
検索値は H2（売上高）を指定。最後の検索結果の表示については、完全一致を表す 0を入力
何列目かについては検索範囲を B1:F1（4月～7月）、検索値は I2（6月）を指定。
組み合わせると
基本的に検索範囲は番地指定をすること
※セルごとで検索範囲が異なると検索結果がおかしくなるからね
それとMatch関数内で検索値に番地しているけど、青文字のほうは Hの前につけて列を固定
緑文字のほうは 2の前につけて行を固定してます。
※今回は6月だけの表示なので、青文字のほうはつけなくてもよかったけど、
緑文字のほうは、指定しなければ検索値のセル指定がどんどんずれておかしな検索結果を
表示することになるからね。あくまで6月のが何列目にあるかを指定すること！
売上高だけの数式を比較すると
Vlookup
Index Match
書く数式は多くなるけど、ファイルの重さや列を数えることを考えると
Index Matchの組み合わせをおすすめします。
書いてることは単純明快だし♪
少し長くなりましたが、今日はここまで。
PS
α7c欲しいなぁ笑
