はじめに
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のリンクしか現れませんでした。
Enterを押すと、こうなります。
C1からC5、合計5つの配列を渡したことで、5つ分のオートフィルがかけられました。
青線で囲まれた部分がSpillによって表示された部分です。
※もし選択配列がそのまま参照されていなかったらSpill実装されていません。残念
オートフィルの途中に文字を入れようとすると、「何割り込んでんじゃボケェ」と怒られます。
次に、Spill機能とROW関数を使って列番号を出してみましょう。
見出し行はカウントしないので返り値を-1します。
実行すると、
=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に&演算子を使って、直接関数で出た数字を足していけばいいんじゃね？
と思ってセルの参照にそのまま数字を足そうとすると…
エラーになります。
Excel側が、「"C2"から"C5+1"番地ってなんやねん」と認識している為です。
Excelにこれを認識させるには、「"C2"から"C6"番地」ときちんと変換して渡してあげなければいけません。
これを解決してくれるのがINDIRECT関数です。
さっそくやってみましょう。INDIRECT関数の引数に参照先を文字列で渡します。
こうするとしっかり"C2:C6"の配列が渡され、Spill機能で増行分が返ってきました。
これを踏まえてC列に何か文字が打ち込まれたら、B列にid番号を返すというのをやってみましょう
COUNTA関数で空白以外のセルをカウントし、見出し行はカウントしないので-1としています。
できました。
これで、行数に入力した数だけid番号を自動で返すことが出来ました。
注意点として、データの間に空白行を作ってしまうと正しく表示されません。
おわりに
他の新機能には重複を削除したり、並べ替えをしたり出来るような
「これを待ってた」と言いたくなるような数々の機能が実装されました。
私の過去に作ってきたExcelツールもSpill機能の登場で見直さなければいけない部分が多々出てきました。
欲を言えば、個人的には最終列と最終行を出す関数が欲しいです。
さっきのRow関数に見出し行を引いてーとかちょっとスマートさに欠けますし、
マクロで maxRow = Cells(Rows.count,1).End(xlup).Row とかやるのもう飽きたので・・
=MAXROW関数とか出てくれないですかね;;
Excelは3Dグラフィックが挿入できるようになったり(1つ挿入しただけでファイルサイズが30MBに
跳ね上がったのはビビりましたが)どんどん幅を利かせています。
5Gの普及によりGoogleスプレッドシートによりExcelは淘汰されるだと等の噂もありますが、
むしろExcelがクラウド上でローカルで動作するExcelと大差ないパフォーマンスを見せたらどうでしょうね。
「クラウド上のExcelファイルがサーバーとして使える！」なんて時代も近いのかもしれません。