Excel20に実装されたスピルですが今まで気にしていなかったし、なんなら使わないようにしていたぐらいです
が、意識して使ってみると結構便利な印象だったので自分のための深堀ついでにまとめてみました
そもそもスピルとは
スピルを直訳すると「こぼれる」らしくセルからこぼれて表示されてるってこと（だと思う）
個人的な感覚だと1つのセルに複数セル（範囲）が登録されているような状態で実際にサンプルを見てもらうとわかりやすい
スピルの簡単な例
“=範囲”にすればスピルで反映します
下の画像の通りF1セルに10行×2列のデータが格納されてるイメージで、スピルの範囲は青い枠で囲われた状態になります
スピルの範囲にカーソルをあてると数式がグレーになってスピルによって表現されてますよ感を出してきます
スピルが展開されるセルに空白以外があるとエラーになります
展開したい範囲は青破線でお知らせしてくれるのでスピルをあきらめるかデータ削除のどちらかを
スピルの実例
スピル機能があることで全ての関数が強化されています
具体的にどう強化されているのかと言うのをみんな大好きXLOOKUPで解説します
まず普通のXLOOKUPの使い方
まずXLOOKUPの引数と説明を
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
XLOOKUP自体の説明は割愛しますが一番オーソドックスなVLOOKUP味な使い方をしますが、下画像の式ではA列の中にある検索値と一致する行でC列の値が返り値になります
検索範囲を複数行×1列、戻り範囲を複数行×複数列にした場合
検索値と一致した行の全列がスピルで返ってきます
検索範囲を1行×複数列、戻り範囲を複数行×複数列にした場合
検索値と一致した行の全行がスピルで返ってきます
検索値を範囲にした場合
相対参照でコピペした時のように連続して検索したようになります
検索値を範囲にして戻り範囲を複数行×複数列にした場合
複数列で返ってきてほしいところですが残念ながら1列の時と同じ結果に・・
万能ではないんですね
ちょっと上級者向けな余談
今度はXLOOKUPの先輩、VLOOKUPを例に上級者向けな余談を
何が上級者向けかと言うと普段EXCELで使うことのない配列を使います
列番号を数字が入力されている範囲にしてみる
それぞれの列番号の値がスピルで返ってくるので”検索値を範囲にした場合”がVLOOKUPでも起こります
列番号を数字の配列にしてみる
EXCELではあまり使う機会が無かった配列がスピルによって活かされることになりました
“{}”で囲うと配列になるのでセル範囲の代わりに使うこともできます
スピルをふんだんに使ってみた例
Office 365に追加されたスピルありきのUNIQUE関数を使うことで今までピポッドテーブルで作っていたような表も簡単に作成できます
まずその手順を動画でどうぞ
この手順自体は最適解ではなくスピルの機能・特徴を使うようにしていますが、ポイントとしては
- UNIQUE関数を使って重複しないIDをスピルで反映
- XLOOKUPの検索値を”セル#”のスピル範囲にしてるので分類もスピルで反映している
- SUMIFSの検索値はあえてスピルにしていないのは3行目以降のスピルもちゃんと検索値で使えることを確認している（ので検索値を範囲にしていればスピルで一気に反映する）
ただただ表示されるだけじゃなくて疑似的な値なのに引数としても使えるので結構使い道ありそうです
スピルの小ネタ
細々とした制御がありそうなので気が付いたら書き足していきます
- スピルで反映されたセルは実態がないからかEXCELコピペしても何も貼り付けできない
- でも”スピルの範囲外に値貼り付け”をした時はちゃんと反映するし外部アプリ(メモ帳とか）には貼り付けできる
- スピルになる数式が入ったセルをコピー＆値貼り付けするとエラーになる
- スピルの結果を値にしたい時はスピルで反映した範囲を全てコピー＆値貼り付けすればOK
- スピルの結果を範囲として数式で採用したい時は「”数式が入ったセル”に”#”」でOK（”A1#”とか）
あとがき
スピルは途中でも触れましたが配列をEXCELのシート状に体現した機能と言えるのでVBAとかプログラミングをやってる人にはちょっとした感動
今まで無理矢理VBAで作っていた機能も関数だけで完結することが増えたのでEXCELツール作成がうねります
コメント