スピルとは:スピル入門の問題と解説
2019年にOffice365(永続版では2021)のExcelに実装された機能にスピルがあります。
数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。
スピル入門の問題と解説です。
問題と解説を通してスピルに慣れてください。
スピルについての通常の解説は以下をご覧ください。
ツイッターで【Excelスピル問題】を出題し、それに解答する形で解説していきます。
本ページは、このツイートのまとめです。
解答なしの問題だけはこちら:スピルとは:スピル基礎から応用までの問題集
目次
スピルって何だ編1
出題
B1セルだけに数式を入れて、「A1~A10」の値を「B1~B10」に出力してください。
解答・解説
B1=A1:A10
複数の値(配列)を返す数式はスピルします。
A1:A10は縦10横1の配列で、B1からあふれ出(スピル)した値がB2から下に出力されます。
B2セルを選択すると数式が薄い表示になっていて、これを「ゴースト」と呼びます。
B1:B10={=A1:A10}
ですが旧バージョンで入れた配列数式が勝手にスピルに書き換わることはありません。
もちろんスピルバージョンでも配列数式で入れることもできます
スピルって何だ編2
出題
B2セルに=A1:A10と入れるとB2:B11にA1:A10が出力されれます(1枚目)
しかしスピルなしVerで同じ入力をするとB2にはA2の値だけが出力されます(2枚目)
では問題です。
スピルVerでA1:A10を参照しながらA2だけを出力したい場合にB2入れる数式は?
解答・解説
参照元(参照している)セル範囲と自信のセル位置関係により、暗黙的に共通部分(この場合は共通する行)を参照する機能があります。
しかしスピルVerでは、セル範囲を参照するとスピルしてしまいます。
「暗黙的なインターセクション演算子」と呼ばれます。
セル範囲指定の直前に@演算子を付けます。
=@A1:A10
※ツイートの都合で@の後ろに非表示文字を入れてあります。
スピルって何だ編3
出題
1セルに数式を入れるだけで、
A1からE2までの数値を2倍した数値を出力してください。
解答・解説
=A1:E2*2
*2とすれば参照しているセル範囲全体に対して2倍した結果が参照元と同じ大きさの「配列」で返されます。
他の算術演算子(+-*/^%)でも同じです。
「配列」なのでスピルします。
=A1:E2
この場合はセル範囲の「参照」が返ってきますが、
*2した場合は「配列」が返ってきます。
しかし、配列はOFFSET関数に入れられません。(2枚目)
「参照」と「配列」の違いに戸惑う場合もあると思います。
例えば、SUMIF(S)には「配列」は入れられません。
数式をスピルさせようとした時に戸惑う場合も出てくると思います。
スピルって何だ編4
出題
C1に数式を入れることで、A1:A10とB1:B10を横に掛け算してC1:C10に出力してください。
C1=A1*B1
C2=A2*B2
・・・
C10=A10*B10
となるようにC1に数式を入れてください。
解答・解説
=A1:A10*B1:B10
素直にそのまま書けばスピルしてくれます。
簡単なので使うのに困ることはなさそうに思います。
しかし、なぜこれでうまく計算してくれるのでしょうか。
この数式は絶対参照にしても同じ結果となります。
=$A$1:$A$10*$B$1:$B$10
ここはしっかり考えてみたいところです。
C1セルに=A1*B1を入れてC10までフィルコピー、またはC1:C10を選択して=A1*B1これでCtrl+Enter。
多くの人は今まではこのようにしていたはずです。
ですが、前々回にお伝えした暗黙的に共通部分を参照する機能を使って数式を入れることもできます。
C1=$A$1:$A$10*$B$1:$B$10
スピルVerなら、暗黙的なインターセクション演算子を使って
=@$A$1:$A$10*@$B$1:$B$10
そして、これを下にコピーします。
絶対参照にしているのでC2以下にも同じ数式で入ります。
同じ数式でも参照する行が自動的にずれて正しく計算してくれます。
(横範囲でも同じです。その場合は共通する列になります)
したがって、参照している範囲の行がずれているとその行の計算はエラーとなります。
=$A$1:$A$10*$B$3:$B$12
ここで配列数式がでてきます。
{=$A$1:$A$10*$B$3:$B$12}
({}を除いて数式を入れてCtrl+Shift+Enter)
配列数式では同じ行や列ではなく同じ位置(上からの位置、列なら左からの位置)を参照します。
※C1=A1*B3
{=A1:A10*B3:B12}
(配列数式では、相対参照でも入力セル以外のセルで数式のセル番地がずれません)
スピルでは配列数式と同じくシート上の行(または列)の番号ではなく、指定範囲内の相対位置の共通部分を参照します。
つまりスピルは配列数式を先頭セルに普通にEnterで入れるだけで、そのセルに収まらない結果がこぼれだして他のセルにまで出力される機能だという事です。
逆に配列数式をあまり使ってこなかった人は配列数式に少しばかり慣れる必要があるかもしれません。
この「Excelスピル問題」をやることで少しでも慣れてもらえると嬉しいです。
スピルって何だ編5
出題
定番中の定番、基本のキ、100ます計算です。
B2に数式を入れるだけで、
1行目(B1:K1)とA列(A2:A11)の交点の掛け算の結果をB2:K11に出力してください。
解答・解説
=B1:K1*A2:A11
1行とA列はどちらが先でもこの場合は同じです。
また、これは絶対参照にしても同じ結果となります。
=$B$1:$K$1*$A$2:$A$11
前回配列数式について説明しましたが配列数式では絶対参照にする必要がありません。
スピルも同様に絶対参照にする必要がありませんでした。
縦の範囲どうしまたは横の範囲どうしの場合は、それぞれの範囲内における相対位置が同じ場所どうしが使われます。
では、縦範囲と横範囲の場合はどうなるのでしょうか・・・
B1に対してA2:A11を計算しB2*B11へ
C1に対してA2:A11を計算しC2*C11へ
・・・
K1に対してA2:A11を計算しK2*K11へ
直積、交差結合、クロス結合
このように呼ばれる処理になります。
これは他の演算子(算術・比較・&等)でも同じです。
スピルって何だ編6
出題
A1:C3の数値に対して、
問1.A5:C7には1行は2倍、2行は3倍、3行は4倍して出力。(1枚目)
問2.E5:G7にはA列は2倍、B列は3倍、C列は4倍して出力。(2枚目)
※2倍3倍4倍の2,3,4の数値はE1:G3のセル範囲のいずれかの3つのセル範囲を使用してください。
解答・解説
A5=A1:C3*E1:E3
E5=A1:C3*E1:G1
縦範囲どうしなら、その範囲内の同じ縦の相対位置が使われる。
横範囲どうしなら、その範囲内の同じ横の相対位置が使われる。
「スピルって何だ編4」でやりました。
これを踏まえて、掛け算の数値範囲をE1:G3から選択します。
縦に2,3,4と掛け算する場合はE1:E3の縦範囲を選択します。
横に2,3,4と掛け算する場合はE1:G1の横範囲を選択します。
問題なのでE1:G3を紛らわしくしましたが、
矩形に対して、
縦の計算を変化させる場合は縦の範囲を用意し、
横の計算を変化させる場合は横の範囲を用意します。
ハイレツテイスウっておいしいの編1
出題
A1:C3の数値に対して、
問1.A5:C7には1行は2倍、2行は3倍、3行は4倍して出力。(1枚目)
問2.E5:G7にはA列は2倍、B列は3倍、C列は4倍して出力。(2枚目)
※以下制限事項
・A1:C3以外のセルは参照しない。
・関数は使用しない。
解答・解説
A5=A1:C3*{2;3;4}
E5=A1:C3*{2,3,4}
配列定数の書き方は波括弧{}を使います。
要素の区切りは、
「;」セミコロンが行(縦)
「,」カンマ(コンマ)が列(横)
配列定数だけをセルに入れるとスピルするので分かり易いと思います。
スピル数式を入れたセル番地に「#」を付けます。
(# はスピル範囲演算子と言いますが、マウス選択で自動的に付くので特に意識しなくても良い)
縦横2次元なら、
={2,3,4;3,3,3;4,3,2}
セル範囲と配列がほぼ同じように扱えることを理解してください。
ハイレツテイスウっておいしいの編2
出題
添付画像の数式を作成してください。
(この問題は頭の体操をかねての書き方の練習です。)
※以下制限事項
・セルは参照しない。
・関数は使用しない。
解答・解説
={2;4;6;8;10}+{0,10,20}
=({1;2;3;4;5}+{0,1,2}*5)*2
=({0;1;2;3;4}+1+{0,1,2}*5)*2
計算方法はいろいろと書けますね。
この問題は配列定数を書く練習でしたので、実際に書いて慣れてもらえれば良いと思います。
同じテーマ「エクセル入門」の記事
LET関数(数式で変数を使う)
スピルと新関数の練習(XLOOKUP関数、LET関数、VBAまで)
スピルとは:スピル基礎から応用までの問題集
スピルとは:スピル入門の問題と解説
スピルとは:旧関数でスピルを使う問題と解説
スピルとは:スピルの新関数を使う問題と解説
スピルとは:ここまでの総合演習問題と解説
LAMBDA以降の新関数について
LAMBDA関数(カスタム関数の作成)
MAP関数(配列各値を新しい値にマッピングした配列を返す)
REDUCE関数(配列にLAMBDAを適用し累積値を返す)
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数(セル範囲をトリム:端の空白セルを除外)|エクセル入門(2024-08-30)
正規表現関数(REGEXTEST,REGEXREPLACE,REGEXEXTRACT)|エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.繰り返し処理(For Next)|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.セルのクリア(Clear,ClearContents)|VBA入門
8.メッセージボックス(MsgBox関数)|VBA入門
9.条件分岐(Select Case)|VBA入門
10.ブック・シートの選択(Select,Activate)|VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。