Microsoft365とExcel2021から実装されたスピルという機能、良く分からなかったのでしばらく放置していたのですが、使ってみたらとても便利でだということが分かりました。しかもこの機能を応用するとかなりいろいろなことができるのではないかと思います。
まずは忘れないうちにこのスピル機能をここでご紹介させていただきます。
スピルとは
スピル(spill)とは〔容器から〕こぼす、あふれさせる、ばらまくなどの意味があります。
関数を入力したセルだけではなく、本来関数をコピーしなければならない他のセルにまで値を表示させる様子がコップから水があふれる様子が似ている事からのネーミングだそうです。
基本機能
例えば下図のような在庫管理表があったとします。
こちらの金額の列に単価(USD)×為替レート×数量の関数を入力しようと思います。
今までの関数入力方法
まず今まで通りの入力の仕方をしてみます。
=C5*$D$2*D5関数をコピーしても参照先がズレないように「D2」セルは「$D$2」と入力するのがエラーを起こさないコツです。
この関数を下までコピーします。
無事正しい計算結果が出力されました。
スピルを使った関数入力方法
次にスピル機能を使って入力してみます。
=C5:C10*D2*D5:D10関数をコピーしないので、「D2」セルも相対参照のままで問題ありません。
この状態で「エンター」キーを押すと
たったこれだけですべて最後の行まで数値が入力されました。
応用(INDEX関数・MATCH関数との組み合わせ)
INDEX関数とMATCH関数の組み合わせた方法についてはExcel(エクセル)INDEX関数とMATCH関数の組み合わせた使い方こちらの記事も参考にしてください。
まずは下図のような「得意先マスタ」を用意します。
次に、下図のように得意先コードのみ入力されている送付先情報データを用意しました。
この層先情報のシートの「C3」セルに下記のような関数を入力します。
=INDEX(得意先マスタ!B2:F7,MATCH(B3:B5,得意先マスタ!B2:B7,0),MATCH(C2:E2,得意先マスタ!B2:F2,0))こちらもコピーなどはしないので絶対参照・相対参照などは意識する必要がありません。
この状態で「エンター」キーを押すと
一発ですべてのセルに値を入れることができました。
まとめ
メリット
- 関数を一つのセルに入れるだけで表が完成する
- 途中で関数が値入力になっていたり関数が消えていたりすることによる不具合が起こらない
- 絶対参照・相対参照の考え方が不要
デメリット
- スピルで表示させるべき場所に別の値が入力されているとエラーになる
- バージョンの低いエクセルでは機能しない
この記事が皆さんのお役に立てればうれしいです。
コメント