永久ライセンスの「エクセル2019」を購入していたユーザー達は、一足早くエクセルの新機能である「スピル」を試していた人も多かったでしょう。
サブスクリプションの「Office365」を使っていたユーザには、順次新機能が配布され自動的にアップデートされていたわけですが、ようやく当サイトでもこの「スピル」を紹介できる準備が整いました。
スピルが使えるまでは、配列数式で処理するのが一般的だったと思います。
しかし、普段エクセルでデータを処理していても、
”そもそも配列数式すらなじみの薄い機能なんだけど”
と言う方も多いかもしれませんね。
今回は、「配列数式とは何か？」という点も「スピル」と一緒におさらいしてみます。
通常のオートフィル操作で計算式をコピーして連続データを入力する方法と
関数でも普通の四則計算でも使える配列数式を使って連続データを入力する方法、
そして、スピルを使って一気に連続データを入力する方法と
これまでの操作とスピルを比べながら、スピルがいかに「連続データの入力を楽にしているか」を一緒に見ていきましょう。
※画面はWindows10となります。
目次
九九計算をそれぞれの方法で入力してみる
オートフィルを使う
普通の数式では一番分かりやすい、「掛け算の九九」の表を使ってみます。
上のように、1から9までが縦横に並び、そのクロスするマスが九九計算の答えとなります。
例えば、セル「B2」であれば「1×1」の答えが入るので、数式は次のようになります。
さて、それではフィルハンドルをドラッグして、「1の段」(B列)を完成させてみましょう。
・・・と、その前にやらなければならない処理がありますね。
そう、「参照の切り替え」です。
セル「B1」に入力されている「1」は計算時に、固定されなければなりません。
したがって、「B1」をとりあえず絶対参照にしてみます。
つまり、セル「B2」に入力された数式の「A2*B1」は
「A2*$B$1」となるわけです。
では、セル「B10」までフィルハンドルをドラッグしてみましょう。
簡単に「1の段」の答えが全て記入できました。
さて、それでは「2の段」以降に数式をコピーする場合はどのようにしたらよいでしょうか。
一番簡単なのは、「1の段」を右にドラッグすればいいと分かります。
しかし、それでフィルハンドルをコピーしてみると・・
↑のようになります。
つまり、フィルハンドルをコピーした際に、固定したいセルの参照方法が違うため、計算結果もおかしくなっています。
もう一度、最初に戻ってみます。
今度は、上のようにセルの参照を切り替えてみました。
セル「B2」に入力されている数式は、「$A2×B$1」となっています。
もし、「1の段」、「2の段」そして「3の段」と、どの段でも縦にオートフィルをした場合、参照したい行は必ず「1行目」となります。
以下のようなイメージですね。
同じように、「1の段」、「2の段」そして「3の段」と、どの段で横にオートフィルをしても、参照したい列は必ず「A列」となります。
今度は、以下のようなイメージです。
つまり、「セルの絶対参照」ではなく「セルの複合参照」を数式に指定すれば、
この九九の表は、フィルハンドルのコピーで正しい答えを手に入れられる、というわけです。
正しいセル参照で、フィルハンドルをコピーしてすべての答えが入力されました。
セルの参照を
相対参照→絶対参照→複合参照
と切り替える方法は簡単にできます。
数式バーのセル番地にカーソルを当て、キーボードから「F4」キーを押します。
「F4」キーを押すごとに、セルの参照方法が切り替わります。
しかし、セル参照が苦手な方は、この話が少し掴みづらく感じるかもしれません。
では、同じ処理を「配列数式」を使って答えを導き出してみましょう。
配列数式を使う
「配列数式」を使うと、セルの数式を配列として処理できます。
分かりやすくするために、まずは4つのセルに答えを求める数式を作ってみたいと思います。
以下のように各セルに入力する掛け算の「数式」を表示してみました。
では、これを配列数式を使って入力してみたいと思います。
最初に、配列により数式を作成する範囲を範囲選択します。
その後に、普段数式を入力する時のように「＝」を入力します。
ここからは、少し特殊となりますが掛け算をするセルは
「単独のセルではなくセル範囲を指定」します。
つまり、4つのセルが選択された状態で、
「=A2:A3*B1:C1」
と入力されればOKです。
これは、分解すると
「=A2*B1」、「=A2*C1」、「=A3*B1」、「=A3*C1」
の4つの数式を指定したのと同じになります。
最後に「配列数式」を指定するには、数式を入力した後に、
キーボードから「Ctrl+Shift」を押しながら、エンターキーを押して確定します。
数式バーに入力された数式に、「｛｝」が付きましたね。
これは、配列数式であるという意味になります。
配列数式で注意したい、最後の「{}」ですが、この中カッコを自分でキーボードから入力しても配列数式とは認識されません。
必ず、最後のエンターキーを押して数式を確定する際に、「Ctrl+Shift」を押しながらエンターキーを押す、という操作を忘れないようにしてください。
では、九九の表に戻してみましょう。
配列数式の作り方は、同じですね。
まず、配列として計算したいセル範囲を選択します。
数式をセル範囲の掛け算で指定して、最後に「Ctrl+Shift」を押したままエンターキーを押して確定します。
単純な掛け算であれば、例え片方のセル範囲が増えても問題ありません。
例えば、セル「A11」に「10」という数値を入力してみます。
「10行×9列」の掛け算となりますが、同じように答えを求めたいセル範囲を選択してから次のように数式を入力します。
もちろん、配列数式ですので、「Ctrl+Shift」を押しながらエンターキーを押して確定します。
11行目の「10の段」もきちんと計算されています。
操作方法は特殊ですが、配列の考え方に慣れると、
最初に見た「セルの参照を切り替えてオートフィルでコピーする方法」よりも、ずっと簡単に数式を指定できると思います。
なお、配列数式を設定したセルには、すべて同じ数式が入っており、
上のようにその一部のセルの値を変更しようとしても、エラーウィンドウが表示されます。
配列数式で入力したデータの一部だけを削除したい場合でも、
配列数式が設定されたセル全体を選択してから、削除する必要があるのです。
スピルを使う
さて、それではスピルを使ってみましょう。
配列数式と似たような動作になりますが、
ユーザー側の労力はさらに楽になります。
まず、配列数式の時には、数式を指定するセルを範囲選択しましたが、
スピルを使うと、全体の一番左上のセルだけ選択されていればOKとなります。
そして、数式の指定は、配列数式と同じとなります。
最後に「Ctrl+Shift」を押す必要はありません。
そのままエンターキーだけを押すと、残りの計算の範囲としたセルにも自動的に結果が入力されます。
スピルの範囲は、青い線で囲まれます。
だんだんと説明に要する文章が短くなったように、
非常にシンプルに計算結果を取得できるのがスピルの特徴でもあります。
配列数式と比較した場合の違いは以下のようになります。
- スピルする範囲の最初のセルだけを選択していれば残りのセルには自動で数式が入る
- 最後のエンターキーで確定する際に他の余計なキーを押す必要がない
- スピルした範囲の最初のセルだけを選択して削除すると残りのスピル範囲全てのセルを削除できる
関数の引数にスピルを使う
スピルの威力が、ユーザーの労力を和らげてくれると分かったところで、
次は、関数にスピルを使ってみたいと思います。
IF関数でスピルを使う
IF関数で判断した条件により違う文字を入力する時にスピルを使えば、作業は一瞬で終わります。
A列の数値が10を超えるなら「OK」、10以下なら「Miss」の文字をB列に入力したいとします。
IF関数で判断させますが、スピルを使えば最初のセル、つまりセル「B1」に数式を指定するだけでセル「B9」まで自動的に結果が入力されます。
セル「B1」に条件を判断したいセル範囲を含めたIF関数の式を設定します。
IF関数の内容自体は簡単ですね。
入力できたら、「OK」ボタンをクリックします。
セル「B1」からセル「B9」まで、A列の数値によって「OK」か「Miss」か自動的に入力(スピル)されました。
Filter関数でスピルを使う
配列を引数に指定する「Filter」関数を使うと、配列(一覧表)から特定の条件に当てはまるデータだけを抽出できるようになります。
例えば、「氏名」と「好きなチーム名」の一覧表から「カープ」が好きな氏名だけを「Filter」関数で拾ってみたいと思います。
「Filter」関数の最初の引数に抽出したい配列を指定します。
この場合、条件に合う氏名を抽出したいので、最初の引数には氏名の配列となる「A12:A20」を指定します。
2番目の引数には、最初の引数と同じ配列数が、ある条件と合致するかどうかを式として作成します。
最初の引数が「A12:A20」なので、この引数にも同じ数の配列となる「B12:B20」を指定します。
さらに、”ある条件と合致するかどうか”の部分は、”カープが好きかどうか”となります。
つまり、「B12:B20=”カープ”」となり、セル「B12」からセル「B20」までの値がカープとなっているセルの行と同じ行をA列から抽出できるようになるのです。
結果として4行目の「丸山」と7行目の「山本」が抽出されました。
セル「D11」だけに関数を指定すれば、残り(この場合はセル「D12」)はスピルで結果が表示されます。
「Filter」関数のように、引数に指定する配列の数が合っていないと
「#VALUE!」のエラーが返されます。
上の例で言えば、最初の引数に「A12:A20」と指定しているのに、2番目の引数に「B12:B19」と指定した場合はエラーとなります。
総括
さて、エクセルの新機能である「スピル」はいかがでしたでしょうか。
配列を扱う方法は、スピルが登場する前には「配列数式」しかなかったわけですが、
スピルの登場でエクセルで様々な数式や関数を扱うユーザーにとっては、さらに簡単にデータ処理を行えるようになりました。
今回、配列数式は単純な「九九の表」でしか使わなかったですが、
当サイトには以前、「年末調整」に関する税額計算をエクセルで関数などを使いながら計算していく、という記事がありました。
この時には、「年調所得税額」を計算する際に、少し複雑な配列数式をIF関数で処理したのです。
配列数式の奥深さについては、こちらの記事の方が実感できるかもしれません。
なにしろ、実務に使いそうな計算中において配列数式が登場するわけですからね。
配列を引数に持つ関数であっても、スピルがなかった時代は、一つのセルに入力した関数式をフィルハンドルをドラッグしてコピーしていたわけです。
しかも、参照が変わらないように、関数式内のセルを絶対参照にしたり、複合参照にしたり苦労しながら・・です。
それを考えると、セルの複合処理が進化したこのスピルという機能は本当に素晴らしい新機能と言えるかもしれませんね。
是非、スピルが使えるエクセルをお使いの方は、関数などと絡めて色々試してみてください！
