最近 Excel でちょっと作り込むことがあったんですけど、『スピル』の機能がとても便利でびっくりしました。
『スピル』と聞いて一番最初に思い出すのは『Don’t cry over spilled milk.』という英文です。
「覆水盆に返らず(こぼしてしまったミルクを嘆かないで)」という意味なんですが、高校の時に急に英語の先生が「お前らも知っているだろう」っていう体で言ってきたのでびっくりした覚えがあります。
(知らんかったです)
で、Excel のスピルの機能とはなんぞや、って説明なんですが、セルから「こぼれて」いるんですね。
今までの常識であれば、Excel の関数を使ってなにかの値を求めるとき、セルに関数を書いて『Enter』を押すと、返ってくるのは1つの答えでした。
でも、このスピルの機能だと、複数の回答が返ってくる(可能性がある)ので、関数を書いたセルからこぼれて溢れてしまうんです。
ピボットテーブルを作ったら意外と大きくなってしまって、すでになにか書かれているセルの上に侵入してこようとした、という覚えがある人もいらっしゃると思います。
私はそうでした。
スピルは、そんなイメージです、多分。
私が個人的に一番使うスピルの関数は『UNIQUE』です。
例えば A 列にたくさんの商品がランダムに書かれていたとして、B1 セルに
=UNIQUE(A:A)と記入すると、B1 セルから下に向かって A 列の商品から重複をなくしたものをバババッと挙げてくれます。
更に並べ替えをしたければ、UNIQUE 関数を SORT 関数でくくってあげれば OK です。
で、このスピルの便利なところが、スピルの範囲を『#』で指定することができるところです。
例えば A列に商品名のリストがあって、それを UNIQUE 関数で重複のないリストを作りたいとします。
先程の
=UNIQUE(A:A)を B1 セルに書けば、重複のないリストが B 列にできます。
で、更にそれを C1 セルでドロップダウンリストの要素として使いたいと思ったとき。
通常であれば、C1 セルを選択して『データの入力規則』を設定、『入力値の種類』で『リスト』を選択して、下の『元の値』で B1 セルから始まっている重複のないリスト範囲を選択します。
でも、A 列の商品名のリストがどんどん増えるタイプのものだったら、B 列で作成した重複のないリストも増える可能性があります。
そのたびに、C1 セルの『データの入力規則』を直さなければいけなくなります。
そのデータがスピルだったら、楽に指定することができるんです。
同じく C1 セルの『データの入力規則』の画面で、『元の値』のところに
=B1#と記入すると、『B1から始まるスピルの範囲』ということで、スピルの内容がドロップダウンリストに反映されるようになるんです。
これ、すごくないですか…!?
今までだったら『名前の管理』で『COUNTA』関数やら『RESIZE』関数やら『OFFSET』関数やらを使って泥臭く指定していたものが、こんな『#』一つで解決してしまうなんて…。
ただまぁ、UNIQUE 関数については1つ気になるところがありました。
A 列の重複のあるリストを B 列で重複なしにして C1 でドロップダウンリストに使うとき、B 列の重複なしのリストの時点で『0』という項目が挙げられてしまいます。
A 列全体を重複なしのリストにしているので、B 列にピックアップする時点で『(空欄)』(=0)も一緒にピックアップされてきてしまっているからなんですね。
『0』が存在するのはちょっとかっこ悪いし、B 列の重複なしのリストを作る時点で範囲指定をしてしまうと結局そこの範囲の管理をしなきゃいけないからめんどくさいです。
その解決策としては、B1 セルに『0』とあらかじめ書いておき、今まで数式を B1 に入力していたのを『B2』セルに移すんです。
で、C列にもう1回 UNIQUE 関数を使います。
今度は UNIQUE 関数の第3の引数『回数指定』(exactly_once)を『TRUE』にした状態で。
最後に D1 セルにドロップダウンリストを作成します。
まとめると下のようになります。
(A 列)
商品のリスト(重複あり)
(B1 セル)
0 ←「ゼロ」
(B2 セル)
=UNIQUE(A:A)
(C1 セル)
=UNIQUE(B:B,,TRUE)
(D1 セルの『データの入力規則』)
入力値の種類 → リスト
元の値 → =C1#こうすると、A 列のリストで『空欄(0)』なしの重複なしのリストをドロップダウンリストの要素として使うことができます。
B1 セルに先に『0』を入れることで、C1 セルに入れた『B 列で1回だけ発生したものだけを抽出』する UNIQUE 関数で『0』が除外されるわけです。
…UNIQUE 関数の『回数指定』の引数、この用途のためにあるのかな、なんて思ったんですけど…違いますかね?
だって、「1回しか出現しない人のリスト」って…そんなに使う?
使う人は使うのか…?
こんな感じで、スピルの範囲の指定がすごく楽ちんだよ、というお話でした。
もちろんスピルの関数は UNIQUE 関数だけではないですし、『#』を指定できるのもドロップダウンリストだけではないと思います。
私がそれ以外試していないというだけです。
でもとっても便利でした。
いやー、常に進化し続けている Excel ちゃん、本当に大好きです。
コメント