ExcelのSpillを使って自動でid番号を割り振ってみる

##はじめに
Office TANAKAさんのブログでExcelのSpillについて知りました。
http://officetanaka.net/excel/excel2016/13.htm

Office TANAKAさん、最近ユーチューブを始められたようで、大変有益な動画内容ばかりなのでおすすめです。

このSpill機能はExcelの欠点というか、喉元に引っかかる使いづらさを解消する劇的な機能だと思います。
1つのセルに配列を指定すると、配列の範囲まで自動でオートフィルしてくれます。
（ユーザーにはオートフィルしているように見えても、実際の処理はオートフィルではないかもしれません。
この記事ではオートフィルという言葉を使いますが、適切ではないかもしれません。）

今までオートフィルを手動でやっていたころは、これ以上は絶対ないデータ行のMAX値＋20行くらいまで予め
オートフィルしておいて、MAX値が分からない場合は渋々マクロでオートフィルしていました。

この機能があればもうそんな心配はありません。

##Spillの機能とは
実際Spill機能がどう動くのか、テストとしてD列に=C1:C5と打ってみます。
これがSpill実装前は最初のC1のリンクしか現れませんでした。
![04271.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/361605/3bfb3ef6-0997-50da-f92a-a7ac0dd2a742.png)

Enterを押すと、こうなります。
C1からC5、合計5つの配列を渡したことで、5つ分のオートフィルがかけられました。
青線で囲まれた部分がSpillによって表示された部分です。
※もし選択配列がそのまま参照されていなかったらSpill実装されていません。残念
![04272.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/361605/ca9bbaa1-c190-56d2-2583-7e0df9274a25.png)

オートフィルの途中に文字を入れようとすると、「何割り込んでんじゃボケェ」と怒られます。

![04273.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/361605/94428af6-dc0b-7880-afc8-db9caf6e82a3.png)


次に、Spill機能とROW関数を使って列番号を出してみましょう。


![04277.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/361605/80f83d6a-b492-aaa5-a8f0-2ee8b1c7c0c5.png)

見出し行はカウントしないので返り値を-1します。
実行すると、

![04275.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/361605/41f5f7ed-9a94-c8eb-50d6-64be7e735592.png)

=ROW(C2)-1から=ROW(C5)-1までの値をC2からC5の行まで繰り返します。

##動的に行数を指定する

次に この式をINDIRECT関数と組み合わせることで、
「必要な行数だけ動的にオートフィルできる」ことが実現できます。

INDIRECT関数は、「文字列として渡した情報をセルの参照として使用できる」関数です。

どういうことか見ていきましょう。

この場合、動的に変化させたい部分は、=ROW(C2:C5)-1  の内の、C5内の5の数字です。
この数字を変化させて、=ROW(C2:C10)-1 にしたり、=ROW(C2:C100)-1に変化させるのが目的です。

じゃあC5に&演算子を使って、直接関数で出た数字を足していけばいいんじゃね？
と思ってセルの参照にそのまま数字を足そうとすると…
![04278.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/361605/945b4762-118f-d563-9cb5-95dd6aacddf1.png)


エラーになります。
Excel側が、「"C2"から"C5+1"番地ってなんやねん」と認識している為です。

Excelにこれを認識させるには、「"C2"から"C6"番地」ときちんと変換して渡してあげなければいけません。

これを解決してくれるのがINDIRECT関数です。

さっそくやってみましょう。INDIRECT関数の引数に参照先を文字列で渡します。
こうするとしっかり"C2:C6"の配列が渡され、Spill機能で増行分が返ってきました。
![04279.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/361605/6615fcf4-d974-2e5c-2b97-da29281e33fa.png)

これを踏まえてC列に何か文字が打ち込まれたら、B列にid番号を返すというのをやってみましょう
COUNTA関数で空白以外のセルをカウントし、見出し行はカウントしないので-1としています。
![04276.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/361605/56368696-8d22-5b65-e5e5-7a7c0928c2a3.png)

できました。
これで、行数に入力した数だけid番号を自動で返すことが出来ました。

![042710.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/361605/4ea2cd7a-e4de-5cca-bfd1-d9349b265e0b.png)


注意点として、データの間に空白行を作ってしまうと正しく表示されません。

##おわりに

他の新機能には重複を削除したり、並べ替えをしたり出来るような
「これを待ってた」と言いたくなるような数々の機能が実装されました。
私の過去に作ってきたExcelツールもSpill機能の登場で見直さなければいけない部分が多々出てきました。

欲を言えば、個人的には最終列と最終行を出す関数が欲しいです。
さっきのRow関数に見出し行を引いてーとかちょっとスマートさに欠けますし、
マクロで maxRow = Cells(Rows.count,1).End(xlup).Row とかやるのもう飽きたので・・
=MAXROW関数とか出てくれないですかね;;

Excelは3Dグラフィックが挿入できるようになったり(1つ挿入しただけでファイルサイズが30MBに
跳ね上がったのはビビりましたが)どんどん幅を利かせています。

5Gの普及によりGoogleスプレッドシートによりExcelは淘汰されるだと等の噂もありますが、
むしろExcelがクラウド上でローカルで動作するExcelと大差ないパフォーマンスを見せたらどうでしょうね。

「クラウド上のExcelファイルがサーバーとして使える！」なんて時代も近いのかもしれません。