一番下のセルを参照する(3)
明確な記録は残っていませんが、このWebサイト「Office TANAKA」を開設したのは、1995年頃です。それ以前に、私がメインで活動していたパソコン通信がNIFTY-Serveでして、そこが"ホームページ"サービスを開始して、すぐに作ってみました。当初は、それまでパソコン通信に投稿していた過去の情報を整理した内容で、確か最初に書いたのは「VBA高速化テクニック」でしたっけ。その後、officetanaka.netのドメインを取得し、不定期でコンテンツを書き続け、早30年近く経ちました。基本的に私は、過去に書いたコンテンツを削除しません。なので「Office TANAKA」には、かなり古い内容の情報も残っています。「VBAでツールバーを操作する」なんて、今では不可能なんですが、それでもページ自体は残してあります。ときどき古いコンテンツを見返すのですが「今だったら、もっと簡単にできるよなぁ～」って感じるものもあります。今回のテーマ「一番下のセルを参照する」も、そのひとつで。最初に(1)を書いてから数年経って"テーブル"という機能がExcelに備わり、(2)を書きました。そして、2024年の現在だったら、スピル系の関数を使うと、もっと柔軟にいろいろできるなぁ～ってことで、(3)を書いてみようと思います。まぁ、最近は重ための長編コンテンツばかり書いているので、ここらで"筆休め"ですかねｗ
テーブル編
元データがテーブルだったら、いろいろと便利です。
構造化参照で、テーブルの"データ部分"を指定するとき、一般的には「=Data[#データ]」のように特殊項目指定子[#データ]をつける、と言われますが、
特殊項目指定子を何も指定しないと、[#データ]を指定したことになります。この方が数式が短くなるので、私はこう書いています。
構造化参照でテーブル全体を指定できるのですから、後は自由自在です。一番下のデータ(行)を参照するなら、TAKE関数で一発ですね。TAKE関数については、下記のページをご覧ください。
以上終了なんですが、せっかくなので、もう少しあれこれやってみましょう。では「"田中"の一番下」を参照するには？これにはまず、"田中"でフィルタをかけます。FILTER関数ですね。
この結果のうち、一番下を参照すればいいのですから、TAKE関数と合わせます。
では「9月うち、最も遅い日付」だったら？今回のデータでしたら「2024/9/30」ですけど、必ずしも月末日(30日)とは限りません。難しく考える必要はありません。FILTER関数で絞り込んだ後、SORT関数で並べ替えてあげればいいです。
まぁ、元データがテーブルだったら簡単ですね。テーブルを使わない理由がありません。
ワークシート編
さてさて、問題はこっちですね。元データがテーブルではなく、通常のワークシートだったら…。ワークシートには区切りがありません。唯一、名前機能を活用するという手もありますが、事前に設定しておかなければならないなど制約も多いです。データが入力されている範囲だけを、スパッと一発で特定できる、VBAのCurrentRegionプロパティみたいな仕組みはないです。ここをどうするのかが、一番の問題であり、長年Excelユーザーを悩ませてきたポイントです。
そこで今までは、どこかの列に入力されている値の数をCOUNTA関数などでカウントして、そこからデータの行数を計算するなどの手法が一般的でした。てゆーか、それくらいしか方法がなかったです。でも、列に入力されている値の数をカウントするって、いったい何行分をカウントすればいいのか？こうなるともう、列全体を指定するしかないです。でも、列全体には1,048,576個のセルがあり、そのほとんどは空欄セルです。当然ですが、空欄セルをカウントしようとすると、データの内部にある空欄セルと、データの下に大量に存在する空欄セルの区別がつきません。
というのが過去の経緯だったのですが、近々この問題はクリアされます。本稿執筆時点では、まだInsider版のExcelでしか利用できませんが、新しくTRIMRANGE関数という武器が追加されました。
関数を入力した結果スピってる範囲に注目してください。関数には「A:D」と列全体を指定していますが、ちゃんとデータが入力されている部分だけを返しています。もちろん、データの増減にも自動的に対応します。
TRIMRANGE関数については、下記のページをご覧ください。
さらに、TRIMRANGE関数の追加に伴って、セルの参照方法にも新機能が追加されています。それがトリム参照です。やることはTRIMRANGE関数と同じですが、TRIMRANGE関数を使わなくても、新しい参照演算子の書き方によって、TRIMRANGE関数と同じセル範囲を参照できます。
同等まではいきませんが、CurrentRegionみたいな参照ですね。一応、本稿のテーマである「一番下のセルを参照」してみましょうか。1行目のタイトルは、あらかじめ入力しておきます。
もし、タイトル行も含めて参照したいのでしたら、これもTAKE関数で対応可能です。TRIMRANGE関数で、"タイトル行も含めた"リスト全体を取得できるのですから、先頭にあるタイトル行は「一番上にあるセル(行)」です。この「一番上にあるセル(行)」と「一番下にあるセル(行)」を、それぞれTAKE関数で抜き出し、両者をVSTACK関数で合体させます。
さて、データ部分だけを取り出せるのですから、次のようにFILTER関数で絞り込むことも可能ですね。どうとでもなります。
かなり便利ですね。まだ製品版のProPlusでは使えないかもしれませんが、時間の問題です。今のうちに情報収集して、楽しみにお待ちください。
以下、ちょっと厳しいことを書きますけど。今まで、長いことExcelを使っていて、昔に習った関数や機能だけで仕事をしている人って、たぶん、それなりに"Excelの自信"があると思います。そういう方々にとって、このトリム参照とか、あるいはスピルとか、もしかしたらテーブルとかPower Queryとかって、たぶん意味が分からないと思います。なぜなら、今までの「Excel(表計算)の常識」が通用しないのですから。「まぁ、私には関係ないな、今までのスキルでも、何とかなるでしょ」なんて思っていると、大変なことになりますよ。それって、江戸時代の知識で令和を生きるようなものです。だって、もうスピルとかPower Queryって普通に使われていますし、これからExcelを学ぶ若い人たちには、トリム参照が当たり前になってきます。もう、そういう時代が始まっています。そんな中で、古い知識しか持たないExcelユーザーは、皆の足を引っ張ることになります。特に、VBA依存の強い方。とにかくVBAを使えば何とかなるって誤解している方は、注意が必要です。DXの流れでは「入力→計算→出力」のうち、VBAは「出力」で使われます。「入力」と「計算」でVBAを使ってしまうと、データの流れが滞ります。くれぐれも、ご注意ください。Excelは進化しています。Excelを取り巻く環境も変化しています。昔の知識だけでは、もはや通用しません。ぜひ「今のExcel」を学習してくださいね。