エクセル入門
スピルとは:ここまでの総合演習問題と解説

Excelの初心者向け入門解説
公開日:2022-09-29 最終更新日:2022-10-13

スピルとは:ここまでの総合演習問題と解説


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

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

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


目次

ここまでの総合演習1

出題

【Excelスピル問題】※ここまでの総合演習1
J2:J5の科目の各月の予算比(実績/予算)をK2:P5に縦横同時スピルで出力してください。
※実績/予算は、必ず実績が上でその下が予算になっています。
※表示形式は気にしない(添付画像は先に設定済)
※画像データは画像の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


解答・解説

【解答数式】
=LET(
実績予算,C:H,
行番号,XMATCH(J2:J5,A:A),
列番号,SEQUENCE(,COLUMNS(実績予算)),
INDEX(実績予算,行番号,列番号)/INDEX(実績予算,行番号+1,列番号)
)
XLOOKUPやFILTERでやりたくなるけど、出来そうで出来ない…
そんな時はINDEX+MATCHがやはり最強ですね。

Excel エクセル スピル問題


今回は横の月は順番が変わらないのでSEQUENCEにしましたが、ここをMATCHにすれば横位置も可変にできます。
※XLOOKUPは縦横にスピルさせられません。
XLOOKUPを覚えた後に1度は必ず通過すると思うので、誰かにXLOOKUPを教える時は一緒に伝えるようにしてください。


FILTERも使いたくなりますが、項目(縦)に複数条件で絞り込むのはFILTERでは扱いずらくなります。
=LET(
実績,FILTER(A:H,B:B="実績"),
予算,FILTER(A:H,B:B="予算"),
FILTER(IFERROR(実績/予算,実績),A1:H1<>"月")
)
FILTERなら列の絞り込み項目も一緒に出力するようにした方が実用的だと思います。

Excel エクセル スピル問題


ここまでの総合演習2

出題

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

Excel エクセル スピル問題


解答・解説

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

Excel エクセル スピル問題


なんとなく計算でもできそうですよね…
=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))
)
難しい、無理やり感しかない…
たぶん結果は良いと思うのですが、作ったものの自分でも理解できないですね…

Excel エクセル スピル問題


ここまでの総合演習3

出題

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

Excel エクセル スピル問題


解答・解説

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

Excel エクセル スピル問題


ここまでの総合演習4

出題

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

Excel エクセル スピル問題

Excel エクセル スピル問題


解答・解説

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

Excel エクセル スピル問題


【解答数式】
問②スピルあり
=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ならはるかに簡単に…それは今後に。

Excel エクセル スピル問題


ここまでの総合演習5

出題

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

Excel エクセル スピル問題
取引先 科目 日付 金額 検索条件 取引先 科目 日付 金額
令和鋼業 前受金 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条件の()*()と掛け算でやるのは以前の問題でやりました。

Excel エクセル スピル問題


FILTER関数では部分一致が面倒ですね。
今回は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

出題

【Excelスピル問題】※ここまでの総合演習6
3の倍数と3の付く数字はアホになると聞いたことがあります。
アホにはなりたくないので、3の倍数と3の付く数字を除いた連番を作成してください。
※とりあえず100まで試せば十分でしょう。
30問目の記念サービス問題です(やはりアホかなw)

Excel エクセル スピル問題



解答・解説

これはもうお好きなように数式を書いてもらえれば良いと思います。
一例として、
=LET(
s,SEQUENCE(100),
t,IF(MOD(s,3)=0,0,IFERROR(FIND(3,s)*0,s)),
FILTER(t,t>0))
FizzBuzzやナベアツ数列は良く出題されますが、基本要素が入っていますので、いろいろな書き方で試してみてください。




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

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

・スピルって何だ編1 ・スピルって何だ編2 ・スピルって何だ編3 ・スピルって何だ編4 ・スピルって何だ編5 ・スピルって何だ編6 ・ハイレツテイスウっておいしいの編1 ・ハイレツテイスウっておいしいの編2
スピルとは:旧関数でスピルを使う問題と解説
・イフでウフフ編1 ・イフでウフフ編2 ・イフでウフフ編3 ・かー‼うんと頑張ってみよう編 ・サムはイフと恋をした編 ・君はヤマダなのかい編 ・毎月月末は彼女とデートなんだよ編
スピルとは:スピルの新関数を使う問題と解説
・SEQUENCE編1 ・SEQUENCE編2 ・FILTER編1 ・FILTER編2 ・SORTBY編1 ・SORTBY編2 ・UNIQUE(そろそろLETも)編 ・XLOOKUP編1 ・XLOOKUP編2
スピルとは:ここまでの総合演習問題と解説
LAMBDA以降の新関数について
・LAMBDA関数とLAMBDA関数を引数に指定できるヘルパー関数群 ・TEXT処理関数 ・配列操作関数群
LAMBDA関数(カスタム関数の作成)
・LAMBDA関数の構文 ・LAMBDA関数をセルで使う場合の基本 ・LAMBDA関数の「数式の検証」について ・LAMBDA関数をセルで使う場合の使用例 ・パラメーターの省略について ・LAMBDA関数を名前定義に登録 ・再帰関数の作成 ・LET関数内でLAMBDA関数を使用する ・LAMBDA関数にLAMBDA関数を渡す ・LAMBDA関数のネストと変数のスコープ(適用範囲) ・遅延評価によりLAMBDA関数オブジェクト(関数値)を返すことができる関数 ・LAMBDAヘルパー関数について
MAP関数(配列各値を新しい値にマッピングした配列を返す)
MAP関数はLAMBDAヘルパー関数(LAMBDAと一緒に使う)の一つです。配列にLAMBDAを適用して新しい値を作成することにより、配列内の各値を新しい値にマッピング(元配列に対して異なるデータを割り当て)して形成された配列を返します。MAP関数はLAMBDAヘルパー関数なので、LAMBDAについては別途習得して…
REDUCE関数(配列にLAMBDAを適用し累積値を返す)
・REDUCE関数の構文 ・REDUCEの基本動作 ・REDUCE関数の使用例と解説 ・初期値に配列を指定した場合 ・[初期値]を省略した時の動作
SCAN関数(配列にLAMBDAを適用し格中間値を返す)
・SCAN関数の構文 ・SCANの基本動作 ・SCAN関数の使用例と解説 ・[初期値]を省略した時の動作
BYROW関数(配列の行単位にLAMBDAを適用し列を集約)
・BYROW関数の構文 ・BYROWの基本動作 ・BYROW関数の使用例と解説
BYCOL関数(配列の列単位にLAMBDAを適用し行を集約)
・BYCOL関数の構文 ・BYCOLの基本動作 ・BYCOL関数の使用例と解説


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

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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