スピルとは:ここまでの総合演習問題と解説
2019年にOffice365(永続版では2021)のExcelに実装された機能にスピルがあります。
スピルを使いこなすには慣れが必要だと思います。
ここまでやってきたことの総合演習問題と解説です。
問題と解説でスピルに慣れてください。
スピルについての通常の解説は以下をご覧ください。
ツイッターで【Excelスピル問題】を出題し、それに解答する形で解説していきます。
本ページは、このツイートのまとめです。
解答なしの問題だけはこちら:スピルとは:スピル基礎から応用までの問題集
目次
ここまでの総合演習1
出題
J2:J5の科目の各月の予算比(実績/予算)をK2:P5に縦横同時スピルで出力してください。
※実績/予算は、必ず実績が上でその下が予算になっています。
※表示形式は気にしない(添付画像は先に設定済)
※画像データは画像のALT

科目 | 月 | 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 |
解答・解説
=LET(
実績予算,C:H,
行番号,XMATCH(J2:J5,A:A),
列番号,SEQUENCE(,COLUMNS(実績予算)),
INDEX(実績予算,行番号,列番号)/INDEX(実績予算,行番号+1,列番号)
)
XLOOKUPやFILTERでやりたくなるけど、出来そうで出来ない…
そんな時はINDEX+MATCHがやはり最強ですね。

※XLOOKUPは縦横にスピルさせられません。
XLOOKUPを覚えた後に1度は必ず通過すると思うので、誰かにXLOOKUPを教える時は一緒に伝えるようにしてください。
=LET(
実績,FILTER(A:H,B:B="実績"),
予算,FILTER(A:H,B:B="予算"),
FILTER(IFERROR(実績/予算,実績),A1:H1<>"月")
)
FILTERなら列の絞り込み項目も一緒に出力するようにした方が実用的だと思います。

ここまでの総合演習2
出題
A1日付以降の土日をB1セル値の回数出力してください。
添付なら10/08以降の土日8回分。
※土日だけ。祝日は関係なし。
※表示形式は気にしない。(サンプル画像は表示形式設定済)
このワークができると国際的にもエクセル使える人だなって思われかも…

解答・解説
=WORKDAY.INTL(A1-1,SEQUENCE(B1),"1111100")
WORKDAY.INTLは2010で追加された関数です。
書式
WORKDAY.INTL(開始日, 日数, [週末], [休日])
「週末」には「週末番号」または「文字列」を指定。
文字列は"0"を稼働日とした月曜開始の7文字で指定。
※日数0は当日になってしまいます。

=LET(
a,A1,b,B1,m,a-WEEKDAY(a,3),
s,SEQUENCE((b+ISODD(b))/2*7+IF(WEEKDAY(a)=1,6,0)-ISODD(b),,m),
FILTER(s,(WEEKDAY(s,11)>=6)*(s>=a))
)
難しい、無理やり感しかない…
たぶん結果は良いと思うのですが、作ったものの自分でも理解できないですね…

ここまでの総合演習3
出題
A列のセル値を、数値・文字・エラーの3種類でカウントしてC2以下にスピル一発で出力してください。
添付参照
※日付は数値扱い
※長さ0文字は文字扱い
難しいかも…練習として取り組んでみてください。
制限なし(私は2021縛り😅)

解答・解説
=CHOOSE(SEQUENCE(3,2),
"数値",COUNT(A:A),
"文字",SUM(ISTEXT(A:A)*1),
"エラー",SUM(ISERROR(A:A)*1))
SWITCHでもできますが少し面倒です。
データ型を調べるTYPE関数は普通にはスピルさせられません。
ISTEXTはCOUNTAからの引き算にしても良いと思います。

ここまでの総合演習4
出題
A列のURLから最後の「/」以降のファイル名を取り出してください。
問①:A1セルだけでスピルさせない場合の数式
問②:A1:A3を指定してスピルさせる場合の数式
※最後が/なら空白で良い
②の数式で①も対応できますが、スピル不要ならどんな数式にしますか。


解答・解説
問①スピルなし
=LET(a,A1,ln,LEN(a),
MID(a,XMATCH("/",MID(a,SEQUENCE(ln),1),,-1)+1,ln))
Excel2021までの範囲で最初に思いついた数式です。
1文字ずつに分解して、XMATCHで後ろから検索しています。
普通の発想だと思うので分かり易いと思います。
書き方は沢山あると思います。

問②スピルあり
=LET(a,A1:A4,
c,LEN(a)-LEN(SUBSTITUTE(a,"/","")),
s,SUBSTITUTE(a,"/",CHAR(1),c),
IFERROR(MID(a,FIND(CHAR(1),s)+1,LEN(a)),""))
①の数式はスピルさせられません。
そこでスピル以前からある手法を使いました。
現365ならはるかに簡単に…それは今後に。

ここまでの総合演習5
出題
A:D列のデータに対して、G2:G5の条件で絞り込みI2以下に金額降順で出力してください。
※取引先と科目は部分一致(*は不要だが分かり易いように付けました)
※年・月は必須、空欄はなし。
※データは画像ALTに
計算式が思いのほか重い🐷言って見たかったw

取引先 | 科目 | 日付 | 金額 | 検索条件 | 取引先 | 科目 | 日付 | 金額 | |||
令和鋼業 | 前受金 | 2022/09/09 | 689,525 | 取引先 | *日本* | ||||||
令和産業 | 前受金 | 2022/09/20 | 89,441 | 科目 | *売上* | ||||||
日本興業 | 売掛売上 | 2022/09/23 | 292,865 | 年 | 2022 | ||||||
令和産業 | 売掛売上 | 2022/09/25 | 932,058 | 月 | 10 | ||||||
日本産業 | 前受金 | 2022/09/28 | 317,032 | ||||||||
大日本総業 | 前受金 | 2022/09/29 | 840,013 | ||||||||
令和鋼業 | 現金売上 | 2022/09/30 | 200,376 | ||||||||
令和産業 | 現金売上 | 2022/10/01 | 89,441 | ||||||||
日本興業 | 売掛売上 | 2022/10/03 | 63,082 | ||||||||
日本産業 | 現金売上 | 2022/10/04 | 44,845 | ||||||||
大日本総業 | 現金売上 | 2022/10/05 | 84,420 | ||||||||
令和鋼業 | 現金売上 | 2022/10/06 | 689,525 | ||||||||
令和産業 | 現金売上 | 2022/10/07 | 932,058 | ||||||||
株式会社令和 | 売掛売上 | 2022/10/08 | 526,736 |
解答・解説
=SORT(
FILTER(A:D,
ISNUMBER(SEARCH(G2,A:A))*
ISNUMBER(SEARCH(G3,B:B))*
(C:C>=DATE(G4,G5,1))*(C:C<=DATE(G4,G5+1,0))),
MATCH("金額",A1:D1,0),-1)
FILTER関数を使うところまでは問題ないと思います。
AND条件の()*()と掛け算でやるのは以前の問題でやりました。

今回はSEARCHを使いました。ワイルドカードも使えるので後々も使い回し易いかなと思います。
日付はDATE関数で1日から末日の範囲にしています。
もっと簡単な方法がありそうにも感じますが、これには理由があります。
=SORT(
FILTER(A:D,
ISNUMBER(SEARCH(G2,A:A))*
ISNUMBER(SEARCH(G3,B:B))*
(TEXT(C:C,"yyyymm")=G4&G5)),
MATCH("金額",A1:D1,0),-1)
これは出来れば実際に動かしてみて欲しいです。
FILTERで上手い事やってほしいところですが、どうやらこのような使い方をすると、全行のデータに対して処理してしまうので、
TEXT(C:C,"yyyymm")
これに時間がかかり過ぎてしまうようです。
範囲を絞って指定する場合は問題ありませんが列指定の場合は注意してください。
ここまでの総合演習6
出題
3の倍数と3の付く数字はアホになると聞いたことがあります。
アホにはなりたくないので、3の倍数と3の付く数字を除いた連番を作成してください。
※とりあえず100まで試せば十分でしょう。
30問目の記念サービス問題です(やはりアホかなw)

解答・解説
一例として、
=LET(
s,SEQUENCE(100),
t,IF(MOD(s,3)=0,0,IFERROR(FIND(3,s)*0,s)),
FILTER(t,t>0))
FizzBuzzやナベアツ数列は良く出題されますが、基本要素が入っていますので、いろいろな書き方で試してみてください。
同じテーマ「エクセル入門」の記事
スピルとは:スピル入門の問題と解説
スピルとは:旧関数でスピルを使う問題と解説
スピルとは:スピルの新関数を使う問題と解説
スピルとは:ここまでの総合演習問題と解説
LAMBDA以降の新関数について
LAMBDA関数(カスタム関数の作成)
MAP関数(配列各値を新しい値にマッピングした配列を返す)
REDUCE関数(配列にLAMBDAを適用し累積値を返す)
SCAN関数(配列にLAMBDAを適用し各中間値を返す)
BYROW関数(配列の行単位にLAMBDAを適用し列を集約)
BYCOL関数(配列の列単位に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.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.メッセージボックス(MsgBox関数)|VBA入門
8.セルのクリア(Clear,ClearContents)|VBA入門
9.ブック・シートの選択(Select,Activate)|VBA入門
10.条件分岐(Select Case)|VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。