Excelの使い方が激変する「スピル」

  1. Home
  2. Excel
  3. Excel 2016レビュー

本稿は、ProPlusに関する内容です。永続ライセンス版のExcel 2016やExcel 2019には実装されていません。このへんの意味が分からない人は、ぜひ下の動画をご覧ください。最新版のExcelは、2019ではなくProPlusだという件を解説しています。なお、再生すると音が出ますので、会社や電車の中でご覧になる場合は、ご注意ください。

また、本稿の内容を動画でも解説しています。動画でご覧になりたい方はどうぞ。Youtubeでは、ほかにもたくさんの動画を公開しています。チャンネル登録をお忘れなく！
Office TANAKAチャンネル

表計算ソフト暗黙のルール

Excelに限らず、今までの表計算ソフトでは、暗黙のルールがありました。それは「値や数式を入力したセルにだけ結果を表示して、何も入力されていないセルに、プログラムが勝手に何かを代入しない」ということです。

たとえば上図のように、セルB1に数式を入力します。すると、結果が表示されるのはセルB1だけです。それまでブランクセルだったところへ、勝手にExcelが計算結果を代入することはしません。

しかし、これからのExcelは違います。同じように、セルB1だけに数式を入力します。

すると、それまでブランクセルだったところへ、Excelが勝手に計算結果を代入します。これが「スピル」機能です。

何らかの結果を表示したいのなら、そのセルにあらかじめ数式を仕込んでおく。そうでないのなら、マクロで代入する。それが今までの表計算ソフトでした。フラッシュフィルは、Excelが勝手に、隣接しているセルへ値や数式を代入してきます。ほんの少しルール違反ですけど、まぁ、そんなのカワイイもんです。それに、フラッシュフィルなんて、ほとんどの人は使わないでしょうし。でも、実を言うと、Googleのスプレッドシートは、早くからこの機能を備えていました。初めてそれを見たときは驚きました。さすがGoogle、従来の考え方にとらわれないんだなぁ～すげぇなぁ～と感心しましたっけ。それが、いよいよExcelにも搭載されるんです。

動的配列数式とゴースト

上記の「=A1:A4」という数式を見て「ん？それって、配列数式じゃね？」って思った方もいるでしょう。配列数式とは違うんですよ。比べてみましょう。

【配列数式】

  1. あらかじめ、計算結果を表示したいセル(配列数式を入力したいセル)をすべて選択しておきます。

  2. 配列を使った数式を入力します。

  3. Ctrl + Shift + Enterキーで確定します。

【スピル】

  1. 数式を入力する先頭のセルだけを選択します。結果を表示するすべてのセルに数式を入力するのではありません。

  2. 複数のセルを参照するような数式を入力します。

  3. 普通にEnterキーを押します。

従来の配列数式は、計算結果を表示するすべてのセルに数式を入力します。対してスピルでは、先頭1つのセルにだけ数式を入力します。その結果、従来の配列数式と同じような計算をして、数式を入力していないセルにも値を書き込みます。「どちらでも同じ結果になる」という単純な話ではありません。考え方が大きく違います。なお、従来の配列数式に対して、スピルを使った配列を操作するような数式のことを動的配列数式と呼びます。余談ですが、従来の配列数式は、Ctrl + Shift + Enterキーで確定することから、通称「CSE」と呼ばれています。

スピル(Spill)とは「溢れる、流れ出す、こぼす」などを表す英単語です。1つのセルに入力した数式によって、結果が他のセルに"流出する"イメージです。スピルによって流出したセルは、青い枠で囲まれます。では、流出したセルは、どうなっているのでしょう。下の図で、数式バーの表記をよく見てください。

分かりますか？スピルによって流出したセルは、数式バーの表記が薄くなります。このように、スピルによって流出したセルのことをゴーストと呼びます。おそらく、攻殻機動隊とは関係ないと思われます。もちろん、ゴーストを直接編集するとエラーになりますし、ゴースト内へのセル挿入やセル削除はできません。

スピル範囲演算子

スピルによって流出したセルを、また別の関数などで計算することもあるでしょう。たとえば、次のようなケースです。

しかし、スピルによって何個のセルが流出されるかは、分かりません。こんなときは、次のように指定します。

「B1#」というのは「動的配列数式を入力したセルB1と、その動的配列数式によって流出した全セル」を表します。これで、何個のセルが流出するかを心配する必要がなくなります。なお、この「#」のことをスピル範囲演算子と呼びます。ちなみに、スピル範囲演算子はVBAでも「Range("B1#")」のように使えます。また、「動的配列数式を入力したセルと、その動的配列数式によって流出した全セル」のことを動的配列範囲と呼びます。

数式をコピーしなくてもいい

よくある表でやってみましょうか。

何をしたいかは、見れば分かりますよね。

  1. セルB2に「=VLOOKUP(A2:A7,F2:G4,2,FALSE)」という数式を入力します。VLOOKUP関数の引数「検索値」に複数のセルを指定するのがポイントです。そして、引数「検索範囲」を絶対参照にする必要はありません。

  2. Enterキーを押せば、すべてのセルでVLOOKUP関数が計算されます。

  3. セルD2に「=B2#*C2:C7」という数式を入力します。

  4. Enterキーを押せば、すべてのセルに計算結果が表示されます。

お分かりですか？もう数式をコピーしなくてもいいんです。数式をコピーすることで、あらかじめ設定していた罫線などの書式が崩れるとか、もうそんな心配しなくていいんです。それから、ビギナーには難易度の高い「絶対参照と相対参照」も考えなくていいんです。現在よくある「はじめてのExcel(仮)」とか「すぐわかるExcel(仮)」とか「猿でもわかるExcel(仮)」などで教えているようなことは、もう役に立たなくなります。それくらい画期的な機能が「スピル」です。つまり「スピル」は、Excelを教えるトレーナーや著者にとっても"マスターしなければならない"重要な仕組みだということです。取得と変換もそうですけど、もうね、昔の教え方にしがみついている時代じゃありません。いつまでも、古い教え方をしないでいただきたいです。それはユーザーのためになりません。

スピルを活用する新しい関数

計算結果を別のセルに流出させる「スピル」によって実現できた、新しいワークシート関数も追加されています。

  • SORT関数
  • SORTBY関数
  • FILTER関数
  • UNIQUE関数
  • RANDARRAY関数
  • SEQUENCE関数
  • XLOOKUP関数

それぞれの関数については、「この関数はこう使え」で詳しく解説します。

これらの新しい関数を使うと、こんなことも可能です。たとえば、次のようなデータがあったとします。

データはたくさんあります。てゆーか、何件あるか分かりません。しかも、データは増減します。ここから、下図のように、名前ごとのシートを作成したいんです。しかも、データを名前で抽出するだけでなく、抽出したデータは日付で並べ替えるとします。今までだったら、みなさん「これはマクロじゃないとできない」って思いますよね。それを関数だけでやってみす。実に簡単です。ちなみに、同じことは取得と変換でも可能です。

ここでは、話を簡単にするため、登場する名前はすでに分かっていて、名前のシートがすでに存在するものとします。「ええ～、じゃぁこうだったら？」という応用は自分で考えてください。元データはテーブル形式にしています。名前は「テーブル1」です。まず、[田中]シートだけを考えてみましょう。

これだけです。簡単でしょ。元のテーブルに新しいデータを追加してみましょう。

ちゃんと、日付で並べ替えられているのが分かります。あとは、同じ数式を、それぞれのシートに入力するだけでいいです。

こうした作業は、実務では"超あるある"です。実現するには、いくつかの方法があります。

方法 元データと各シート
手動操作 リンクされない
マクロ(VBA) リンクされない
取得と変換 リンクされる
動的配列数式 リンクされる

"手動操作"というのは、手で元データにオートフィルタをかけて、絞り込んだ結果をそれぞれのシートにコピーする方法です。シート数が少ないのでしたら、これも有効です。ちなみにショートカットキーを活用すれば、けっこう素早くコピーできます。しかし、この方法だと、元データとそれぞれのシートはリンクされません。元データが変わったら、毎回コピーする必要があります。

"マクロ"でオートフィルタをかけて結果を別シートへコピーする、というのが今までの考え方でしょうね。私も、よくそういうマクロを作りました。でも、結局やることはセルのコピーですから、元データと各シートはリンクされません。元データが変更されたら、またマクロを実行しなければなりません。どうやって実行しますか？シート上にボタンを配置しますか？ボタンを配置する空きスペースがシート上になかったら？そもそも、そうしたマクロ、何も見ないでサッと書けますか？意味も分からず、ネットに書いてあるコードをコピペすると、はい、"誰も触れないマクロもどき"の完成です。

同じことは"取得と変換"でも可能です。取得と変換に関しては「取得と変換」で詳しく解説していますので、ご覧ください。取得と変換でしたら、元データと各シートはリンクされます。「すべて更新」を実行するだけで、常に最新の状態になります。でも、取得と変換って、よく分かりませんよね。ネットにもほとんど情報がありません。Power Queryエディタの使い方だって、よく分からないのでは？とても強力な機能ですけど、ちょっと難しいです。

今回追加された、FILTER関数やSORT関数を使うと、上記のように簡単です。何と言ってもワークシート関数ですから、"VBA"や"取得と変換"よりも、とっつきやすいのでは。もちろん、他の関数と組み合わせることで、さらに複雑なことも可能です。4つの方法のうち"どれがいい"ということではなく、選択肢のひとつとして、FILTER関数やSORT関数などの"動的配列数式"も使えるようになっておきましょう。ただ、デメリットとしては、これらの関数が、すべてのExcelユーザーで使えるとは限らないということです。最新版のProPlusでないと使えません。複数の人が使うブックや、第三者に渡すブックで使うのは、事前の確認が必要ですね。

まだまだ書きたいことは山ほどあるのですけど、長くなったので、このへんにしておきます。