エクセル入門
スピルとは:スピルの新関数を使う問題と解説

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

スピルとは:スピルの新関数を使う問題と解説


2019年にOffice365(永続版では2021)のExcelに実装された機能にスピルがあります。
スピルを使いこなすには慣れが必要だと思います。
スピルの新関数を使う問題と解説です。
問題と解説でスピルに慣れてください。
スピルについての通常の解説は以下をご覧ください。

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

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


目次

SEQUENCE編1

出題

【Excelスピル問題】※SEQUENCE編1(今回から素直にw)
A1:A8に添付画像の数列を作成してください。

ところで、コンピューターは何で動いているか知っていますか。
「2進数で動いている」
そう答える人が多いかもしれませんね。
私が知る限りでは…電気で動いています。
※少しだけ頭の体操

Excel エクセル スピル問題


解答・解説

スピルで追加された関数にSEQUENCE関数があります。
SEQUENCE関数は連続した数値の配列を作成します。
書式
=SEQUENCE(行,[列],[開始],[目盛り])
詳細については、以下をご覧ください。
https://excel-ubara.com/excel1/EXCEL633.html
・SEQUENCE関数の書式 ・SEQUENCE関数の使用例 ・SEQUENCE関数を使ったエクセル問題 ・スピルによって新しく追加された関数


【解答数式】
=2^SEQUENCE(8)-1
この数列は2進数の、
1
11
111
1111

これになります。
これに1を足すと、
10
100
1000
10000

ですので、
2^n-1
と言う事ですね。
2進数の文字列を作り出して10進に戻す回答もありました。
=BIN2DEC(REPT(1,SEQUENCE(8)))
これはこれで素晴らしい。

エクセル Excel サンプル画像


SEQUENCE編2

出題

【Excelスピル問題】※SEQUENCE編2
A1:J10に添付画像の数列を作成してください。

ところで、どんな商売でも難しいものです。
単に割り切って考えて良いものでもないし、割り切れない事をグタグタ考えても仕方ないし…
そこを上手くやっていくのが商売のコツかもしれません。独り言です…
※頭の体操

Excel エクセル スピル問題


解答・解説

【解答数式】
=TRANSPOSE(SEQUENCE(10,10))
SEQUENCEは横→縦の順でしか作成できません。
縦→横の順にするならTRANSPOSEで入れ替えます。

=SEQUENCE(10)+SEQUENCE(,10,0,10)
100ます計算と同じ理屈です。
縦配列と横配列の演算によりクロス結合された配列が作成されます。添付は分かり易くしたもの。

Excel エクセル スピル問題

Excel エクセル スピル問題


この問題は頭の体操とも書きましたが、工夫を凝らすと変わった方法も出来そうです。
=SEQUENCE(10,10,1,10)-INT(SEQUENCE(10,10,0)/10)*99
2つの2次元配列の引き算で表現してみました。
これはあくまで余興の範囲です。

Excel エクセル スピル問題


FILTER編1

出題

【Excelスピル問題】※FILTER編1
A2:D11の動物一覧について、以下の条件で抽出しF2以下に出力してください。
・「種」の文字数が3文字
・「年齢」が5歳以下
結果は添付参照してください。
名前は気にしないw
※画像データはALT(形式を選択して貼り付けで)

Excel エクセル スピル問題
名前 性別 年齢
こんた キツネ 4
にゃあすけ ネコ 5
みどりん カメ 12
ひまわり サル 9
かわこ メダカ 1
ぴょんすけ ウサギ 4
はちわん イヌ 7
はむはむ ハムスター 2
ぽんぽこ タヌキ 8
すいすい 金魚 3


解答・解説

【解答数式】
スピルで追加された関数にFILTER関数があります。
FILTER関数は条件に基づいてデータ範囲をフィルター処理した結果を返します。
書式
=FILTER(配列,含む,空の場合)
詳細については、以下をご覧ください。
https://excel-ubara.com/excel1/EXCEL629.html
・FILTER関数の書式 ・FILTER関数使用例のサンプルデータ ・FILTER関数の基本 ・空白セルを0ではなく空白にする場合 ・複数条件のフィルター ・関数を使ってフィルター ・横(列)でフィルター ・表示する列を選択する ・FILTER関数の結果を他の関数で使う ・スピルによって新しく追加された関数


【解答数式】
=FILTER(A2:D11,(LEN(B2:B11)=3)*(D2:D11<=5))
第2引数の「含む」にAND条件を指定する場合は、
(条件1)*(条件2)
このように条件を掛け算します。
OR条件を指定する場合は、
(条件1)+(条件2)
このように条件を足し算します。

Excel エクセル スピル問題


FILTER編2

出題

【Excelスピル問題】※FILTER編2
A2:D11の動物一覧について、以下の条件で抽出しF2以下に出力してください。
・「性別」が雌
・F1:G1の見出しと一致する列のみ
結果は添付参照
※画像データはALT(形式を選択して貼り付けで)

Excel エクセル スピル問題
名前 性別 年齢
こんた キツネ 4
にゃあすけ ネコ 5
みどりん カメ 12
ひまわり サル 9
かわこ メダカ 1
ぴょんすけ ウサギ 4
はちわん イヌ 7
はむはむ ハムスター 2
ぽんぽこ タヌキ 8
すいすい 金魚 3


解答・解説

【解答数式】
=FILTER(FILTER(A2:D11,C2:C11="雌"),COUNTIF(F1:G1,A1:D1))
FILTER関数は縦でも横でもできます。
=FILTER(配列,含む,[空の場合])
「含む」に
縦配列を指定すれば行(縦)をフィルター
横配列を指定すれば列(横)をフィルター
もちろんネストできます。
※他の数式でも良いですよ。

Excel エクセル スピル問題


SORTBY編1

出題

【Excelスピル問題】※SORTBY編1
A2:D21の一覧について、誕生日の月日の昇順に並べてF2以下に出力してください。
年は関係なく月日だけで並べてください。(添付参照)
※画像データはALT(形式を選択して貼り付けで)

Excel エクセル スピル問題
名前 性別 誕生日 都道府県
篠崎 育二 1994/11/23 大阪府
沢 亮 1981/11/29 福岡県
奈良 芽以 1999/08/15 神奈川県
根本 亮 1974/01/26 青森県
平林 一恵 1976/06/19 埼玉県
大高 薫 2010/01/15 愛知県
一木 崇史 1979/11/21 滋賀県
本間 晃司 1979/12/31 鹿児島県
吉村 将也 2009/03/12 埼玉県
三好 兼 1992/09/25 愛知県
近藤 美希 1982/03/12 佐賀県
沼田 まなみ 1972/07/30 広島県
神山 法嗣 1995/07/11 福岡県
浅沼 優 1977/07/07 北海道
芦屋 紗季 1988/03/12 大分県
柳川 直人 2004/02/13 岩手県
金子 雅功 1971/10/28 愛知県
宮内 智花 1988/04/02 神奈川県
中沢 ヒカル 1990/08/14 熊本県
大後 まさみ 1979/09/30 新潟県


解答・解説

スピルで追加された関数にSORT/SORTBY関数があります。
SORT関数は列位置で、SORTBY関数は対応する範囲または配列で並べ替えます。
書式
=SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])
=SORTBY(配列,基準配列,[並べ替え順序],...)
詳細については以下を
https://excel-ubara.com/excel1/EXCEL630.html
・SORT関数の書式 ・SORTBY関数の書式 ・SORT関数、SORTBY関数と、ワークシートの並べ替えの違い ・最も単純な並べ替え ・複数キーでの並べ替え ・列方向(横方向)で並べ替え ・並べ替え範囲(配列)以外の基準で並べ替える ・列全体を範囲指定する場合 ・スピルによって新しく追加された関数


【解答数式】
=SORTBY(A2:D21,TEXT(C2:C21,"mmdd"),1)
=SORTBY(A2:D21,MONTH(C2:C21),1,DAY(C2:C21),1)
基準配列には関数も使えます。
上記はどちらでも同じですね。
並べ替え順序は、
1 : 昇順
-1 : 降順
省略時は昇順になるので今回の場合は省略可能です。

Excel エクセル スピル問題


SORTBY編2

出題

【Excelスピル問題】※SORTBY編2
A2:D21の一覧について、以下の基準で並べ替えてF2以下に出力してください。
・都道府県コードの昇順
・誕生日の昇順
都道府県コードは「都道府県」シートにあります。
画像を参照してください。
※画像データはそれぞれのALTに入れてあります。

Excel エクセル スピル問題
データはSORTBY編1と同じ
Excel エクセル スピル問題
都道府県 都道府県コード
北海道 1
青森県 2
岩手県 3
宮城県 4
秋田県 5
山形県 6
福島県 7
茨城県 8
栃木県 9
群馬県 10
埼玉県 11
千葉県 12
東京都 13
神奈川県 14
新潟県 15
富山県 16
石川県 17
福井県 18
山梨県 19
長野県 20
岐阜県 21
静岡県 22
愛知県 23
三重県 24
滋賀県 25
京都府 26
大阪府 27
兵庫県 28
奈良県 29
和歌山県 30
鳥取県 31
島根県 32
岡山県 33
広島県 34
山口県 35
徳島県 36
香川県 37
愛媛県 38
高知県 39
福岡県 40
佐賀県 41
長崎県 42
熊本県 43
大分県 44
宮崎県 45
鹿児島県 46
沖縄県 47


解答・解説

【解答数式】
=SORTBY(A2:D21,VLOOKUP(D2:D21,都道府県!A:B,2,FALSE),1,C2:C21,1)
基準配列の作成で関数は使えますので、もちろん他シートの参照も可能です。
前問が出来ればこれは簡単に書けると思います。
基準配列が複数ある場合の指定順序に気を付けるくらいでしょうか。

Excel エクセル スピル問題


UNIQUE(そろそろLETも)編

出題


Excel エクセル スピル問題
データはSORTBY編1と同じ

解答・解説

スピルで追加された関数にUNIQUE関数があります。
UNIQUE関数はセル範囲または配列から一意(ユニーク)な値を返します。
重複を削除して一意化した配列を作れます。
書式
=UNIQUE(配列,列の比較,回数指定)
詳細については、以下をご覧ください。
https://excel-ubara.com/excel1/EXCEL631.html
・UNIQUE関数の書式 ・ワークシートでの一意化(ユニーク化) ・UNIQUE関数の使用例 ・UNIQUE関数の応用例 ・スピルによって新しく追加された関数


【解答数式】
=IF({1,0},UNIQUE(D2:D21),COUNTIF(D2:D21,UNIQUE(D2:D21)))
UNIQUE関数で都道府県をユニーク化し、SUMIFS関数で人数をカウントし、IF({1,0},で横に並べています。
IF({1,0},で横に並べるテクは既にやりましたね。
これで良いのですが、2度同じUNIQUE(D2:D21)がでてきます、なんとなく…

Excel エクセル スピル問題


スピル以降に追加された関数にLET関数があります。
関数内で計算結果やセル範囲に名前を定義できます。
プログラミングにおける変数と同じような機能です。
書式
=LET(名前1,名前値1,計算または名前2,名前値2,...)
詳細については、以下をご覧ください。
https://excel-ubara.com/excel1/EXCEL631.html
・UNIQUE関数の書式 ・ワークシートでの一意化(ユニーク化) ・UNIQUE関数の使用例 ・UNIQUE関数の応用例 ・スピルによって新しく追加された関数


LET関数を使って先の数式を書き換えてみます。
=LET(
一意,UNIQUE(D2:D21),
IF({1,0},
一意,
COUNTIF(D2:D21,一意)
)
)
このくらいなら気にする必要はありませんが、
LET関数を使って複雑な数式を作成する時は、改行・インデントを適宜行ったほうが良いですね。

Excel エクセル スピル問題


XLOOKUP編1

出題

【Excelスピル問題】※XLOOKUP編1
A:D列に棋士ごとの対局数、勝数、負数の一覧があります。
F2:F4の棋士の勝率を求めてG列に出力してください。
※特に制限はありません。ありませんが※に・・・
※画像データは画像のALT

Excel エクセル スピル問題
棋士名 対局数 勝数 負数
藤井聡太 20 16 4
渡辺明 16 9 7
永瀬拓矢 22 14 8
谷川浩司 14 8 6
羽生善治 18 12 6
佐藤康光 11 4 7
森内俊之 6 1 5
青野照市 9 1 8
福崎文吾 4 0 4
脇謙二 5 0 5
高橋道雄 10 1 9
中村修 14 6 8
島朗 10 3 7
南芳一 9 0 9
塚田泰明 7 2 5


解答・解説

スピルで追加された関数にXLOOKUP関数があります。
範囲または配列を検索し最初の一致に対応する範囲を返します。
VLOOKUPとHLOOKUPそこに新機能まで追加された関数。
=XLOOKUP(検索値,検索範囲,戻り範囲,見つからない場合,一致モード,検索モード)
詳細は以下をご覧ください。
https://excel-ubara.com/excel1/EXCEL634.html
・XLOOKUP関数の書式 ・従来の関数の代わりとして ・XLOOKUP関数をスピルさせる ・見つからない場合 ・一致モードの使い方 ・検索モードの使い方 ・XLOOKUP関数をネストして戻り列を可変にする ・XLOOKUP関数の戻りセル範囲を別の関数で使う ・XLOOKUP関数が縦横に同時にスピルしないことについて ・スピルと新関数の練習 ・XLOOKUP関数のVBA使用例 ・スピルによって新しく追加された関数


【解答数式】
=XLOOKUP(F2:F4,A:A,C:C)/XLOOKUP(F2:F4,A:A,B:B)
素直に書くならこうでしょうか。
なんとなーく、1つのXLOOKUPに出来そうな・・・
=XLOOKUP(F2:F4,A:A ,C:C/B:B)
XLOOKUPの「戻り範囲」は配列も指定できます。
=XLOOKUP(F2:F4,A:A,C:C/B:B)
すっきりしました。

Excel エクセル スピル問題


スピルで列指定する場合は注意しないととても重い数式になってしまいます。
この場合も列の割り算がちょっと気になりますが、この場合は1回だけなのであまり問題ないでしょう。
XLOOKUPでこれが出来るならVLOOKUPでもできますね。
=VLOOKUP(F2:F4,IF({1,0},A:A,C:C/B:B),2,FALSE)

Excel エクセル スピル問題


XLOOKUP編2

出題

【Excelスピル問題】※XLOOKUP編2
J2の科目の各月の予算比(実績/予算)をK2:P2に出力してください。
※実績/予算は、必ず実績が上でそのすぐ下が予算になっています。
※表示形式は気にしない(添付画像は先に設定済)
※特に制限はありませんが、※にw
※画像データは画像のALT

Excel エクセル スピル問題
科目 10 11 12 1 2 3
科目A 実績 381 313 333 309 304 313
科目A 予算 310 434 354 472 404 377
科目B 実績 255 299 291 259 243 231
科目B 予算 238 225 215 244 243 241
科目C 実績 559 607 510 540 554 502
科目C 予算 557 591 504 619 579 514
科目D 実績 529 532 496 405 593 488
科目D 予算 443 421 506 518 409 408


解答・解説

【解答数式】
=XLOOKUP(J3,A:A,C:H)/XLOOKUP(J3,A:A,C:H,,,-1)
せっかくなのでVLOOKUPにはないXLOOKUPの新機能を使って。
検索モード
1 : 先頭から末尾へ検索
-1 : 末尾から先頭へ検索
2 : バイナリ検索(昇順で並べ替え)
-2 :バイナリ検索(降順で並べ替え)
省略した場合は1 (先頭から末尾)

Excel エクセル スピル問題


=LET(
x,XLOOKUP(J3,A:A,C:H),
x/OFFSET(x,1,0)
)
XLOOKUPの横スピルは「セル参照」を返します。
「セル参照」なのでOFFSETできます。
縦スピルは「配列」なのでOFFSETできまん。
そしてXLOOKUPは縦横同時スピル出来ません。

Excel エクセル スピル問題



Excel エクセル スピル問題


今回はお題としてXLOOKUPだったので当然XLOOKUPを解答しました。
この内容ならFILTER関数を使いたくなる人も結構いるかもしれません。
=FILTER(C2:H9,(A2:A9=J3)*(B2:B9="実績"))/FILTER(C2:H9,(A2:A9=J3)*(B2:B9="予算"))
時々でどちらでも良いと思います。

Excel エクセル スピル問題




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

スピルとは:スピル基礎から応用までの問題集
スピルとは:スピル入門の問題と解説
スピルとは:旧関数でスピルを使う問題と解説
スピルとは:スピルの新関数を使う問題と解説
スピルとは:ここまでの総合演習問題と解説
LAMBDA以降の新関数について
LAMBDA関数(カスタム関数の作成)
MAP関数(配列各値を新しい値にマッピングした配列を返す)
REDUCE関数(配列にLAMBDAを適用し累積値を返す)
SCAN関数(配列にLAMBDAを適用し格中間値を返す)
BYROW関数(配列の行単位にLAMBDAを適用し列を集約)


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

数字(1~50)を丸付き数字に変換するVBA|VBA技術解説(2022-11-15)
TEXTAFTER関数(テキストの指定文字列より後ろの部分を返す)|エクセル入門(2022-11-14)
TEXTBEFORE関数(テキストの指定文字列より前の部分を返す)|エクセル入門(2022-11-14)
TEXTSPLIT関数(列と行の区切り記号で文字列を分割)|エクセル入門(2022-11-12)
LAMBDA以降の新関数はVBAで使えるか|VBA技術解説(2022-11-11)
WRAPCOLS関数(1次元配列を指定数の列で折り返す)|エクセル入門(2022-11-08)
WRAPROWS関数(1次元配列を指定数の行で折り返す)|エクセル入門(2022-11-08)
EXPAND関数(配列を指定された行と列に拡張する)|エクセル入門(2022-11-07)
TAKE関数(配列の先頭/末尾から指定行/列数を取得)|エクセル入門(2022-11-06)
DROP関数(配列の先頭/末尾から指定行/列数を除外)|エクセル入門(2022-11-06)


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

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.マクロって何?VBAって何?|VBA入門
10.ExcelマクロVBAの基礎を学習する方法|エクセルの神髄




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


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



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