エクセル入門
スピルとは:スピル入門の問題と解説

Excelの初心者向け入門解説
最終更新日:2022-09-28

スピルとは:スピル入門の問題と解説


2019年にOffice365(永続版では2021)のExcelに実装された機能にスピルがあります。
数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。
スピル入門の問題と解説です。
問題と解説を通してスピルに慣れてください。
スピルについての通常の解説は以下をご覧ください。

スピルについて
・スピルとは ・スピルの数式例 ・ゴースト ・スピル範囲での独特な挙動について ・スピルのエラー表示 ・スピル範囲演算子 ・暗黙的なインターセクション演算子 ・従来のスピルしないエクセルとの互換性についての注意点 ・スピル関連記事

ツイッターで【Excelスピル問題】を出題し、それに解答する形で解説していきます。
本ページは、このツイートのまとめです。
解答なしの問題だけはこちら:スピルとは:スピル基礎から応用までの問題集

・スピルって何だ編1 ・スピルって何だ編2 ・スピルって何だ編3 ・スピルって何だ編4 ・スピルって何だ編5 ・スピルって何だ編6 ・ハイレツテイスウっておいしいの編1 ・ハイレツテイスウっておいしいの編2 ・イフでウフフ編1 ・イフでウフフ編2 ・イフでウフフ編3 ・かー‼うんと頑張ってみよう編 ・サムはイフと恋をした編 ・君はヤマダなのかい編 ・毎月月末は彼女とデートなんだよ編 ・SEQUENCE編1 ・SEQUENCE編2

目次

スピルって何だ編1

出題

【Excelスピル問題】※スピルって何だ編
B1セルだけに数式を入れて、「A1~A10」の値を「B1~B10」に出力してください。

Excel エクセル スピル問題


解答・解説

さすがに回答はもらえないですね…
B1=A1:A10
複数の値(配列)を返す数式はスピルします。
A1:A10は縦10横1の配列で、B1からあふれ出(スピル)した値がB2から下に出力されます。
B2セルを選択すると数式が薄い表示になっていて、これを「ゴースト」と呼びます。

Excel エクセル スピル問題

Excel エクセル スピル問題


このエクセルを保存しスピルしないバージョンで開くと配列数式(CSE:Ctrl+Shift+Enterで数式入力)になっています。
B1:B10={=A1:A10}
ですが旧バージョンで入れた配列数式が勝手にスピルに書き換わることはありません。
もちろんスピルバージョンでも配列数式で入れることもできます

Excel エクセル スピル問題


スピルって何だ編2

出題

【Excelスピル問題】※スピルって何だ編2
B2セルに=A1:A10と入れるとB2:B11にA1:A10が出力されれます(1枚目)
しかしスピルなしVerで同じ入力をするとB2にはA2の値だけが出力されます(2枚目)
では問題です。
スピルVerでA1:A10を参照しながらA2だけを出力したい場合にB2入れる数式は?

Excel エクセル スピル問題

Excel エクセル スピル問題


解答・解説

スピルなしVerでセル範囲を参照した時、
参照元(参照している)セル範囲と自信のセル位置関係により、暗黙的に共通部分(この場合は共通する行)を参照する機能があります。
しかしスピルVerでは、セル範囲を参照するとスピルしてしまいます。


そこで従来の共通部分を参照する方法がスピル以降も用意されています。
「暗黙的なインターセクション演算子」と呼ばれます。
セル範囲指定の直前に@演算子を付けます。
=@A1:A10
※ツイートの都合で@の後ろに非表示文字を入れてあります。

Excel エクセル スピル問題


スピルって何だ編3

出題

【Excelスピル問題】※スピルって何だ編3
1セルに数式を入れるだけで、
A1からE2までの数値を2倍した数値を出力してください。

Excel エクセル スピル問題


解答・解説

数式です。
=A1:E2*2
*2とすれば参照しているセル範囲全体に対して2倍した結果が参照元と同じ大きさの「配列」で返されます。
他の算術演算子(+-*/^%)でも同じです。
「配列」なのでスピルします。
=A1:E2
この場合はセル範囲の「参照」が返ってきますが、
*2した場合は「配列」が返ってきます。

Excel エクセル スピル問題


セル範囲なら、例えばOFFSET関数で参照をずらせます。(1枚目)
しかし、配列はOFFSET関数に入れられません。(2枚目)
「参照」と「配列」の違いに戸惑う場合もあると思います。
例えば、SUMIF(S)には「配列」は入れられません。
数式をスピルさせようとした時に戸惑う場合も出てくると思います。

Excel エクセル スピル問題

Excel エクセル スピル問題


スピルって何だ編4

出題

【Excelスピル問題】※スピルって何だ編4
C1に数式を入れることで、A1:A10とB1:B10を横に掛け算してC1:C10に出力してください。
C1=A1*B1
C2=A2*B2
・・・
C10=A10*B10
となるようにC1に数式を入れてください。

Excel エクセル スピル問題


解答・解説

数式は、
=A1:A10*B1:B10
素直にそのまま書けばスピルしてくれます。
簡単なので使うのに困ることはなさそうに思います。
しかし、なぜこれでうまく計算してくれるのでしょうか。
この数式は絶対参照にしても同じ結果となります。
=$A$1:$A$10*$B$1:$B$10
ここはしっかり考えてみたいところです。

Excel エクセル スピル問題


そもそもここで入れる数式は従来なら、
C1セルに=A1*B1を入れてC10までフィルコピー、またはC1:C10を選択して=A1*B1これでCtrl+Enter。
多くの人は今まではこのようにしていたはずです。
ですが、前々回にお伝えした暗黙的に共通部分を参照する機能を使って数式を入れることもできます。


スピルなしVerなら、
C1=$A$1:$A$10*$B$1:$B$10
スピルVerなら、暗黙的なインターセクション演算子を使って
=@$A$1:$A$10*@$B$1:$B$10
そして、これを下にコピーします。
絶対参照にしているのでC2以下にも同じ数式で入ります。
同じ数式でも参照する行が自動的にずれて正しく計算してくれます。

Excel エクセル スピル問題


複数の縦範囲を参照している場合は暗黙的に共通する行が参照されます。
(横範囲でも同じです。その場合は共通する列になります)
したがって、参照している範囲の行がずれているとその行の計算はエラーとなります。
=$A$1:$A$10*$B$3:$B$12

Excel エクセル スピル問題


そこで、行または列がずれた数式で相対的な位置で計算したい時にどうするかです。
ここで配列数式がでてきます。
{=$A$1:$A$10*$B$3:$B$12}
({}を除いて数式を入れてCtrl+Shift+Enter)
配列数式では同じ行や列ではなく同じ位置(上からの位置、列なら左からの位置)を参照します。
※C1=A1*B3

Excel エクセル スピル問題


そして配列数式では絶対参照にする必要がなくなります。
{=A1:A10*B3:B12}
(配列数式では、相対参照でも入力セル以外のセルで数式のセル番地がずれません)
スピルでは配列数式と同じくシート上の行(または列)の番号ではなく、指定範囲内の相対位置の共通部分を参照します。

Excel エクセル スピル問題


スピルでは、配列数式と違い1つのセルに数式を入れるだけで対象セルの計算を行い、その結果を配列(またはセル参照)で返してくれます。
つまりスピルは配列数式を先頭セルに普通にEnterで入れるだけで、そのセルに収まらない結果がこぼれだして他のセルにまで出力される機能だという事です。

Excel エクセル スピル問題


したがって、従来のエクセルで配列数式を使ってきた人はスピルも自然と使えると思います。
逆に配列数式をあまり使ってこなかった人は配列数式に少しばかり慣れる必要があるかもしれません。
この「Excelスピル問題」をやることで少しでも慣れてもらえると嬉しいです。


スピルって何だ編5

出題

【Excelスピル問題】※スピルって何だ編5
定番中の定番、基本のキ、100ます計算です。
B2に数式を入れるだけで、
1行目(B1:K1)とA列(A2:A11)の交点の掛け算の結果をB2:K11に出力してください。

Excel エクセル スピル問題


解答・解説

数式です。
=B1:K1*A2:A11
1行とA列はどちらが先でもこの場合は同じです。
また、これは絶対参照にしても同じ結果となります。
=$B$1:$K$1*$A$2:$A$11
前回配列数式について説明しましたが配列数式では絶対参照にする必要がありません。
スピルも同様に絶対参照にする必要がありませんでした。

Excel エクセル スピル問題


スピルではあふれ出した先のセルでも同じ数式が適用されます。
縦の範囲どうしまたは横の範囲どうしの場合は、それぞれの範囲内における相対位置が同じ場所どうしが使われます。
では、縦範囲と横範囲の場合はどうなるのでしょうか・・・


片方の範囲の1つ1つに対して相手の範囲全てを処理していきます。
B1に対してA2:A11を計算しB2*B11へ
C1に対してA2:A11を計算しC2*C11へ
・・・
K1に対してA2:A11を計算しK2*K11へ
直積、交差結合、クロス結合
このように呼ばれる処理になります。
これは他の演算子(算術・比較・&等)でも同じです。

Excel エクセル スピル問題


スピルって何だ編6

出題

【Excelスピル問題】※スピルって何だ編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つのセル範囲を使用してください。

Excel エクセル スピル問題

Excel エクセル スピル問題


解答・解説

まずは数式から
A5=A1:C3*E1:E3
E5=A1:C3*E1:G1
縦範囲どうしなら、その範囲内の同じ縦の相対位置が使われる。
横範囲どうしなら、その範囲内の同じ横の相対位置が使われる。
「スピルって何だ編4」でやりました。
これを踏まえて、掛け算の数値範囲をE1:G3から選択します。

Excel エクセル スピル問題

Excel エクセル スピル問題


A1:C3に対して、
縦に2,3,4と掛け算する場合はE1:E3の縦範囲を選択します。
横に2,3,4と掛け算する場合はE1:G1の横範囲を選択します。
問題なのでE1:G3を紛らわしくしましたが、
矩形に対して、
縦の計算を変化させる場合は縦の範囲を用意し、
横の計算を変化させる場合は横の範囲を用意します。

Excel エクセル スピル問題

Excel エクセル スピル問題


ハイレツテイスウっておいしいの編1

出題

【Excelスピル問題】※ハイレツテイスウっておいしいの編
A1:C3の数値に対して、
問1.A5:C7には1行は2倍、2行は3倍、3行は4倍して出力。(1枚目)
問2.E5:G7にはA列は2倍、B列は3倍、C列は4倍して出力。(2枚目)
※以下制限事項
・A1:C3以外のセルは参照しない。
・関数は使用しない。

Excel エクセル スピル問題

Excel エクセル スピル問題


解答・解説

数式です。
A5=A1:C3*{2;3;4}
E5=A1:C3*{2,3,4}
配列定数の書き方は波括弧{}を使います。
要素の区切りは、
「;」セミコロンが行(縦)
「,」カンマ(コンマ)が列(横)
配列定数だけをセルに入れるとスピルするので分かり易いと思います。

Excel エクセル スピル問題

Excel エクセル スピル問題


スピルしたセル範囲を参照する場合は、
スピル数式を入れたセル番地に「#」を付けます。
(# はスピル範囲演算子と言いますが、マウス選択で自動的に付くので特に意識しなくても良い)
縦横2次元なら、
={2,3,4;3,3,3;4,3,2}
セル範囲と配列がほぼ同じように扱えることを理解してください。

Excel エクセル スピル問題

Excel エクセル スピル問題

Excel エクセル スピル問題


ハイレツテイスウっておいしいの編2

出題

【Excelスピル問題】※ハイレツテイスウっておいしいの編
添付画像の数式を作成してください。
(この問題は頭の体操をかねての書き方の練習です。)
※以下制限事項
・セルは参照しない。
・関数は使用しない。

Excel エクセル スピル問題


解答・解説

数式です。
={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
計算方法はいろいろと書けますね。
この問題は配列定数を書く練習でしたので、実際に書いて慣れてもらえれば良いと思います。

Excel エクセル スピル問題




同じテーマ「エクセル入門」の記事

XMATCH関数(MATCH関数を拡張した新関数)
LET関数(数式で変数を使う)
スピルと新関数の練習(XLOOKUP関数、LET関数、VBAまで)
LAMBDA以降の新関数について
LAMBDA以降の新関数の使用例
縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)
直積(クロス結合、交差結合)とピボット解除
スピルとは:スピル基礎から応用までの問題集
スピルとは:スピル入門の問題と解説
スピルとは:旧関数でスピルを使う問題と解説
スピルとは:スピルの新関数を使う問題と解説


新着記事NEW ・・・新着記事一覧を見る

スピルとは:スピル基礎から応用までの問題集|エクセル入門(2022-09-29)
スピルとは:スピル基礎から応用までの問題集|エクセル入門(2022-09-26)
リーグ表に対戦番号を振る|ツイッター出題回答 (2022-09-23)
スピルとは:旧関数でスピルを使う問題と解説|エクセル入門(2022-09-21)
スピルとは:スピル入門の問題と解説|エクセル入門(2022-09-16)
直積(クロス結合、交差結合)とピボット解除|エクセル入門(2022-09-08)
脱字メーカー(文字列から1文字削除)|ツイッター出題回答 (2022-09-05)
【VBA学習のお勧めコース】|VBA入門(2022-09-02)
振込手数料を先方負担にした時の振込金額と手数料の算出|ツイッター出題回答 (2022-09-01)
構成比を合計しても100%にならないと言われた…|ツイッター出題回答 (2022-09-01)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.並べ替え(Sort)|VBA入門
8.マクロって何?VBAって何?|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.エクセルVBAでのシート指定方法|VBA技術解説




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ