Excelで最終行のデータを見つける方法(関数 INDEX・COUNTA)

Excel

Excelで過去のデータから直近のデータだけを抽出したいというケースもあるでしょう。
その場合の対処方法をまとめてみました。

直近のデータだけ表示したい

 

過去からのデータのうち、一部のデータだけを抽出したい、直近のデータだけが欲しいというケースもあるでしょう。

GOforIT

今回の例は、データのうち、直近から12ヶ月分の推移を見たいという事例。

この直近をどう抽出するか。やり方はいろいろありますが、Excelの関数を使って一番下のデータを見つけることができれば、データの抽出はできます。

 

11月が終わったら、上記の式をコピペして、G15セルで一番下のデータを見つけて、それに対応するデータも表示させ、12ヶ月になるようにそれ以前の月のデータも1ヶ月ずつ繰り上げています。

GOforIT 1

Excelで最終行のデータを見つけることができれば、その都度、過去12ヶ月のデータを表示させることができます。

それをもとにグラフも。

GOforIT

もちろん、12ヶ月だけでなく、2年でも、3年でも大丈夫です。

Excelで最終行のデータを見つけるには?

 

Excelで一番下のデータを見つけるには、次のような関数をつかっています。

 

INDEX関数

 

INDEX関数を使ってなにができるか?やってみたほうが早いかと。

ある行の特定の5行目のデータを抽出したいなら、このようにすると
GOforIT 2

 

「日本」を抽出することができます。今回はA列の5番目ということです。
GOforIT

 

ところが、データが増えれば最終行も変わります。つまり、◯番目がわかりません。

そこで、5番目のところをCOUNTA(A:A)に変えて、「=INDEX(A:A,COUNTA(A:A))」とし、A列のデータ数を数えて11月のデータ表示をこころみます。

GOforIT

 

ところが表示されたのは、11月でなく10月。11月までデータが入っているのに…。
GOforIT

実は、関数COUNTAはデータの数を数えて数字を返す関数。A列の一番上、A1セルにはデータがなく、A列にあるデータはA1セル以外の37個。

結果、セルA37が抽出されることになります。でも、11月のデータがあるのは38行目です。

データにスペースをつくると、こういう影響があります。

この解決策としては、A1セルに項目を入力するか、「=INDEX(A:A,COUNTA(B:B))」とデータを数える列をB列に変えるなど。

B1にはデータが入っていますから、データが38個あるということで、「=INDEX(A:A,38)」と読み替えることになって、晴れて11月のデータが表示されます。

GOforIT 4

XLOOKUP関数

 

最新の月が表示されたら、その日付データをもとにXLOOKUPでデータを抽出します。
たとえば、セルH15では「=XLOOKUP(G$15,A:A,B:B)」としています。

GOforIT 5

 

セルG15にある日付データをA列から探し、該当のセルのB列のデータ「14,623」を抽出します。

GOforIT 6

他も同じようにXLOOKUPを使って抽出することができます。

Excel関数(VLOOKUP・XLOOKUP・HLOOKUP)で推移表データから数字を抽出するパターン。 – GO for IT 〜 税理士 植村 豪 Official Blog

最新月から逆に 関数でしくみづくり

 

最新月が11月として、その前の月をどうやって表示させるか。
やり方はいろいろありますが、わたしはEOMONTHという関数を使っています。

 

関数「EOMONTH」は(End of Month)の意味。

たとえば、セルG14が「2022/10/1」となっている場合、セルG15に「=EOMONTH(G14,1)」とすれば、G15セルは翌月末、11月30日が表示されます。

 

今回は、最新月の2022年11月1日を基準としてさかのぼるので、月初の1日を表示させたい場合には、「-2」とし、9月30日。「+1」とすれば10月1日となります。
GOforIT 7

 

なぜ、「+1」ができるかといえば、Excelの日付はこのように本来は数字のデータだからです。表示上、日付になっているだけであって。

GOforIT 8

なので、「+1」とすれば、1日後のデータとなります。

 

 

じぶんの数字をチェックしたくても、会計ソフトでは会社は年度、個人は年で区切られていることがほとんどです。

かといって、年度や年は税金の計算のための区切りでしかなく、仕事は続いているわけですから、それをカバーするしくみをExcelでつくってみるというのもおすすめです。

Excelなら5年でも10年でもグラフを自由につくることができますし、そのためのしくみも考えますし、勉強にもなります。

GOforIT

【編集後記】
昨日は法人の決算など税理士業を中心に。夜は長男(7)が絵を書くしりとりをやろうということで一緒に。親に似ず、けっこう味のある絵を書くなぁと。iPadも使い慣れたものです。

【昨日の1日1新】
※「1日1新」→詳細はコチラ
K&F Concept 俯瞰 カメラ三脚 中心軸横断
長男(7)と絵しりとり

【長男とW杯観戦記(期間限定コーナー:ネタバレなし)】
夜中の試合は見れないので、夜に前日の試合を一緒にチェックしています。ただ、アルゼンチンの試合は見たいようで「あさ、4じにおこして」と。見たい気持ちはわかるのですが、学校あるし、どうなることやら…。