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

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

スピルとは:旧関数でスピルを使う問題と解説


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

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

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


目次

イフでウフフ編1

出題

【Excelスピル問題】※イフでウフフ編1
5人による総当たりリーグ戦
B2に数式を入れて、
「○○ 対 △△」の形式で表を埋めてください。
※B2,C3等の同一人対戦の部分は空白にしてください。
※名前の順番は問わない(気にしなくて良い)

Excel エクセル スピル問題


解答・解説

数式です。
=IF(B1:F1=A2:A6,"",B1:F1&" 対 "&A2:A6)
IF関数を使わない方法もありますが…※で書いてますからねw
100ます計算の演算部分を文字列結合にするだけです。
ただし、&を関数でやろうとすると少し注意が必要になります。

Excel エクセル スピル問題


文字列結合にCONCAT関数やTEXTJOIN関数は使えません。
=IF(B1:F1=A2:A6,"",TEXTJOIN(" ",,B1:F1,"対",A2:A6))
これらの関数はセル範囲の文字列を全部結合してしまいます。
CONCATENATE関数なら&と同様に使えます。
=IF(B1:F1=A2:A6,"",CONCATENATE(B1:F1," 対 ",A2:A6))

Excel エクセル スピル問題

Excel エクセル スピル問題


イフでウフフ編2

出題

【Excelスピル問題】※イフでウフフ編2
A1:A5とB1:B5の左右を入れ替えた結果を出力してください。(添付参照)
※特に制限はありません。とはいえ配列定数直書きはなしw

Excel エクセル スピル問題


解答・解説

数式です。
=IF({1,0},B1:B5,A1:A5)
いきなりだとちょっと戸惑うかもしれません。
真偽の横配列をIFの条件式に入れると、真(TRUE)と偽(FALSE)の2列にスピルします。
=IF({1,0},1,2)
0がFALSE、0以外の数値はTRUEになります。
この真と偽のところに縦のセル範囲を入れることで列を入れ替えています。

Excel エクセル スピル問題

Excel エクセル スピル問題


イフでウフフ編3

出題

【Excelスピル問題】※イフでウフフ編3
A1:E4の範囲を上下逆さまにして出力してください。(添付参照)
A1:E1 → G4:K4
A2:E2 → G3:K3
A3:E3 → G2:K2
A4:E4 → G1:K1
※特に制限はありませんが、※書いてますからw
前回問題の練習問題です。

Excel エクセル スピル問題


解答・解説

数式です。
お題に書いたのでまずはIF関数から。
=IF({1;0;0;0},A4:E4,IF({0;1;0;0},A3:E3,IF({0;0;1;0},A2:E2,A1:E1)))
ネストの仕方は何通りか書き方がありますね。
このIFが書けるなら、他の分岐系の関数にすることもできると思います。

Excel エクセル スピル問題


=IFS({1;0;0;0},A4:E4,{0;1;0;0},A3:E3,{0;0;1;0},A2:E2,{0;0;0;1},A1:E1)
=CHOOSE({1;2;3;4},A4:E4,A3:E3,A2:E2,A1:E1)
=SWITCH({1;2;3;4},1,A4:E4,2,A3:E3,3,A2:E2,4,A1:E1)
もちろんスピル以降の関数を使えば…それは今後に。


かー‼うんと頑張ってみよう編

出題

【Excelスピル問題】※かー‼うんと頑張ってみよう編
A:E列で先頭文字がG2:G4で始まるセルの件数をH2以下に出力してください。
※大文字小文字は気にしなくて良い。
スピルで使える関数の基本からです。

Excel エクセル スピル問題


解答・解説

【解答数式】
=COUNTIFS(A:E,G2:G4&"*")
条件に合致したセルの個数を数えるならCOUNTIF(S)関数。
=COUNTIFS(検索条件範囲,検索条件,...)
検索条件はワイルドカードが使えます。
?:任意の1文字
*:任意の文字列(0個または1個以上の文字)
ここにセル範囲(または配列)を指定することでスピルします。

Excel エクセル スピル問題


サムはイフと恋をした編

出題

【Excelスピル問題】※サムはイフと恋をした編
A:C列のデータについて、以下の条件を満たす値をG2以下に出力してください。
・A列の日付がE2の値と一致
・B列の区分がF2:F4のそれぞれと一致
・C列の値がプラス値(>0)

Excel エクセル スピル問題


問題文が曖昧でした。
※条件に一致するC列の値を合計してG2以下に出力してください。


解答・解説

【解答数式】
=SUMIFS(C:C,A:A,E2,B:B,F2:F4,C:C,">0")
COUNTIFSと同様のスピルのさせ方です。
=SUMIFS(合計対象範囲,条件範囲,条件,...)
条件にセル範囲(または配列)を指定することで、その指定と同じ大きさでスピルします。
E2は$を付けたくなりますが絶対参照にする必要はありません。

Excel エクセル スピル問題


君はヤマダなのかい編

出題

【Excelスピル問題】※君はヤマダなのかい編
A1:A10の日付の年・月・日をB列C列D列にそれぞれ出力してください。
※日付以外のデータが入っていることは考慮しなくて良い。
※出力は文字列でも数値でもどちらでも良い。
(数値の場合は表示形式は気にしないでください。)
※特に制限はありません。

Excel エクセル スピル問題


解答・解説

【解答数式】
=TEXT(A1:A10,{"yyyy","mm","dd"})
TEXT関数の表示形式にセル範囲(または配列)を指定することでスピルします。
今回は横に年・月・日をスピルさせるので、指定する配列は横配列になります。
分岐関数(CHOOSE等)とYEAR,MONTH,DAYでもできますが今回の場合は面倒になります。

Excel エクセル スピル問題


毎月月末は彼女とデートなんだよ編

出題

【Excelスピル問題】※毎月月末は彼女とデートなんだよ編
A1:A10の日付の月末日をB列に出力してください。
※表示形式は気にしなくて良い。
(サンプル画像はB列の表示形式を設定済)
※特に制限はありません。

Excel エクセル スピル問題


解答・解説

【解答数式】

【解答数式】
最初に入れてみたくなる数式は、
=EOMONTH(A1:A10,0)
この数式は「#VALUE!」となってしまいます。
なぜでしょう、困りましたね。
関数によってセル範囲を指定できない引数があります。
ではとうするかです。
①別の関数を使う
②指定方法を変えてみる
どちらかになります。

Excel エクセル スピル問題


①別の関数を使う
日付関係ならDATE関数ですね。
=DATE(YEAR(A1:A10),MONTH(A1:A10)+1,0)
DATE関数の引数にはセル範囲を指定できます。
日に0を指定すると、指定年月の前月末日になります。

Excel エクセル スピル問題


②指定方法を変えてみる
セル範囲がダメなら配列を指定したら…
セル範囲に何らかの計算をしてやると配列になります。
=EOMONTH(A1:A10*1,0)
=EOMONTH(A1:A10+0,0)
=EOMONTH(+A1:A10,0)
=EOMONTH(--A1:A10,0)

Excel エクセル スピル問題


セル範囲に関数を適用して配列にすることもできます。
=EOMONTH(VALUE(A1:A10),0)
しかし使えそうで使えない関数もあります。
=EOMONTH(N(A1:A10),0)
当然ですがこの時に使う関数がセル範囲に対応していなければ使えません。
引数の特性は関数ごとに違い、一定の決まりみたいなものはなさそうです。

Excel エクセル スピル問題

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」をお願いいたします。
本文下部へ