入力規則とスピルと最新関数解答

公開日：2013年5月以前　最終更新日：2025-09-28

入力規則とスピルと最新関数解答

エクセル練習問題解答ページです。

エクセル練習問題

学校の情報授業を作成しましょう！

H:Iの科目名と曜日時間をK列以降に展開して、B2:F6の入力規則のリストに使います。

・K2に入れる数式
・B2:F6の入力規則のリストでの数式

この2つの数式を作成してください。

※K列以降のフォーマットは自由です（画像はあくまでサンプルなので、別フォーマットでも良い）

シンキングタイム

シンキングタイム開始

シンキングタイム終了

当サイトのコンテンツ

自分に合った、学習方法を見つけましょう。

エクセル入門

エクセル作業において必須となるショートカットキーから基本の操作、機能、そして覚えておくべき関数とその使い方までを、入門・初級・初心者向けに解説しています。エクセルには多くの関数がありますが、どの業種でも必要とされる関数に絞って紹介しています。

エクセル基本操作

エクセルの基本操作について解説。知ってそうで知らない操作や高度な操作まで。日付、時刻に関するあれこれ数値の書式のあれこれ１．千単位、百万単位２．不要な0を表示せずに、小数点位置を揃える３．ユーザー定義書式で条件付き書式を使う４．Excel2003で、条件によりフォント色を５段階で表示する方法。

エクセル関数応用

エクセルの関数の応用技の解説。関数サンプルと必須の基本技術から応用・高等テクニックまでを紹介しています。１年後の日付、○か月後の日付複数条件の合計・件数・サンプルデータ・複数条件の合計・複数条件の件数・スピルと新関数入力規則のリストを、追加・削除に自動対応で作成一覧リストを使用して、「入力規則」の「リスト」を作…

ここで、じっくり勉強して下さい。

エクセル練習問題解答

では解答です。

K2の数式

=SORT(
    TEXTSPLIT(
        TEXTJOIN(
            CHAR(2),
            TRUE,
            MAP(
                DROP(H:.H, 1),
                DROP(I:.I, 1),
                LAMBDA(x, y,
                    TEXTJOIN(
                        CHAR(2),
                        TRUE,
                        x & CHAR(1) & TOCOL(TEXTSPLIT(y, ","))
                    )
                )
            )
        ),
        CHAR(1),
        CHAR(2)
    ),
    2
)

数式の処理概要

この数式は、ネストされた複数の関数を使用しており、主に以下のステップで処理を実行しています。
H列とI列のデータを取得し、各科目に対応する「科目名 + 曜日・時限」のペアを展開（DROP、MAP、LAMBDA、TOCOL、TEXTSPLIT、TEXTJOIN）
展開されたデータを単一の文字列に連結（TEXTJOIN）
連結された文字列を行と列に分割して表形式に戻す（TEXTSPLIT）
「曜日・時限」の列で並べ替え（ソート）（SORT）

各関数の詳細な役割

1. データの展開と結合 (MAP / LAMBDA / TEXTSPLIT / TOCOL / TEXTJOIN (内部))

ここが数式の核となる部分です。

DROP(H:.H, 1)、DROP(I:.I, 1): H列（科目名）とI列（曜日・時限）のデータ範囲から、見出し行（1行目）を除外して取得します。
MAP(..., LAMBDA(x, y, ...)): H列の各セル（x：科目名）とI列の各セル（y：曜日・時限）の組み合わせに対して、以下の処理を繰り返し実行します。
- TEXTSPLIT(y, ","): I列のセル（例：「火2,金3」）をコンマ（,）で分割し、曜日・時限のリスト（例：{'火2'; '金3'}）を作成します。
- TOCOL(...): 分割された曜日・時限のリストを1列のデータに変換します。
- x & CHAR(1) & ...: 科目名（x）と、上記で得られた各曜日・時限（例：'火2'）を、区切り文字 CHAR(1) で結合します（例：「情報サイエンス応用$CHAR(1)火2」、「情報サイエンス応用CHAR(1)$金3」）。
- TEXTJOIN(CHAR(2), TRUE, ...): 各科目の結合ペア（例：「情報サイエンス応用$CHAR(1)火2」、「情報サイエンス応用CHAR(1)$金3」）を、別の区切り文字 CHAR(2) で連結します。

結果: 各科目名と、それに対応する全ての曜日・時限のペアが、CHAR(2) で区切られて1つのリストになります。

2. 単一文字列への連結 (TEXTJOIN (外部))

TEXTJOIN(CHAR(2), TRUE, ...): ステップ1で得られた、各科目のリスト（複数の文字列）全体を、さらに区切り文字 CHAR(2) で連結し、大きな1つの文字列にします。
結果: 全ての「科目名$CHAR(1)$曜日・時限」のペアが、CHAR(2) で区切られた巨大な文字列になります。

3. 表形式への再分割 (TEXTSPLIT)

TEXTSPLIT(..., CHAR(1), CHAR(2)): ステップ2で作成された大きな文字列を、列の区切りとして CHAR(1)、行の区切りとして CHAR(2) を指定して分割します。

行の区切り CHAR(2) で各ペア（例：「情報サイエンス応用$CHAR(1)$火2」）を新しい行に分けます。
列の区切り CHAR(1) で各行のデータを「科目名」と「曜日・時限」の2列に分けます。

結果: K列とL列に表示されているような、「科目名」と「曜日・時限」が対応する2列の表形式のデータ（例：K列「情報サイエンス応用」、L列「火2」）ができます。

4. 並べ替え (SORT)

SORT(..., 2): ステップ3で作成された2列の表を、2列目（曜日・時限の列）を基準にしてソート（並べ替え）します。
結果: 「曜日・時限」が昇順で並んだ、最終的なリストが出力されます。

使用されている特殊文字について

CHAR(1): 列の区切りとして使用されています（科目名と曜日・時限の間）。これは印刷されない特殊な文字コードです。
CHAR(2): 行の区切りとして使用されています（個々の「科目名と曜日・時限のペア」の間）。これも印刷されない特殊な文字コードです。
これらの特殊文字を使うことで、データ内のコンマやスペースと混同することなく、正確にデータを区切って結合・再分割することができます。

この数式は一例です。
順に読み解いてみてください。
数式の書き方は沢山ありますので、各自工夫して作成してみてください。

入力規則

=OFFSET(INDEX(TAKE($K$2#,,1),XMATCH(B$1&$A2,TAKE($K$2#,,-1),0,1)),0,0,XMATCH(B$1&$A2,TAKE($K$2#,,-1),0,-1)-XMATCH(B$1&$A2,TAKE($K$2#,,-1),0,1)+1)

数式の処理概要

この数式は、主に以下のステップで動作し、抽出対象の範囲を動的に決定しています。

検索対象のリスト（K2#）から、曜日・時限の列（L列のデータ）を抽出します。
B1セル（時限）とA2セル（曜日）の値を結合して、完全一致の検索キーを作成します。
XMATCH 関数を使い、検索キーに最初に一致する行と、最後に一致する行の位置（インデックス）を特定します。
INDEX 関数と OFFSET 関数を使って、最初と最後の位置から、抽出する範囲全体を動的に定義します。

各関数の詳細な役割

数式を内側から順に見ていきます。

検索キーと検索対象の列の準備
- TAKE($K$2#,,-1):
  $K$2#は、K列とL列で展開されたリスト全体（K2セルから始まる動的配列）を指しています。
  TAKE関数は、配列から指定した行数・列数だけを取得します。ここでは列数に-1を指定することで、リストの最後の1列（つまりL列の曜日・時限のデータ）を取得しています。これが検索対象の範囲です。
- B$1 & $A2:
  B1セルの値（例：「火」）とA2セルの値（例：「1」）を結合し、「火1」のような検索キーを作成しています。
最初と最後の出現位置の特定（XMATCH）
ここでは、検索対象の範囲内で、上記で作成した検索キーが最初と最後に現れる行の位置を特定します。
- XMATCH(B$1 & $A2, TAKE($K$2#,,-1), 0, 1):
  1 は検索モードを指定しており、「昇順の完全一致」または「最初に見つかったもの」を探します。
  ここでは、検索キーに**最初に完全一致する行のインデックス（K2#の中での行番号）**を取得します。
- XMATCH(B$1 & $A2, TAKE($K$2#,,-1), 0, -1):
  -1 は検索モードを指定しており、「降順の完全一致」または「最後に見つかったもの」を探します。
  ここでは、検索キーに最後に完全一致する行のインデックスを取得します。
抽出する範囲の定義と取得（INDEX / OFFSET）
- XMATCH(..., 0, -1) - XMATCH(..., 0, 1) + 1:
  これは「抽出する行数」を計算しています。
  最後のインデックス - 最初のインデックス + 1 の計算により、検索キーに一致するデータの総数を求めています。
  例：最後が5行目、最初が3行目なら、5-3+1=3行が抽出対象です。
- INDEX(TAKE($K$2#,,1), XMATCH(..., 0, 1)):
  TAKE($K$2#,,1) は、リストの最初の1列（K列の科目名）を取得します。
  INDEX関数は、このK列のデータの中から、最初に一致した行のデータ（最初の科目名）を取得し、これを抽出開始のセルとして指定します。
- OFFSET(..., 0, 0, ..., ):
  OFFSET関数は、開始セル（INDEXの結果）から指定された行数・列数分ずれた範囲を返します。
  開始セル：INDEX(...)の結果（最初に一致した科目名のセル）
  行オフセット：0（行をずらさない）
  列オフセット：0（列をずらさない）
  高さ（行数）：最後のインデックス - 最初のインデックス + 1 の計算結果（抽出する行数）
  幅（列数）：省略されているため、開始セルと同じ幅（1列）

結果: OFFSET関数が、最初に一致したセルから始まり、必要な行数だけ下にあるK列のデータ（科目名）の範囲を動的に返し、その結果がセルに一覧表示されます。

参考ページ

基本操作.条件付き書式

条件付き書式は、指定した条件に基づいてセルの書式（セルの外観）を変更できます。通常の「セルの書式設定」では、書式を設定するセル自身の数値によって変更することは可能ですが、条件付き書式では、他のセル値も含めて複雑な条件によって書式を変更できます。

LET関数（数式で変数を使う）

LET関数は、関数内で計算結果やセル範囲に名前を定義できます。これにより、数式の中間計算に名前を定義したり、後ろの引数で定義した名前を式に使う事が出来ます。これはプログラミングにおける変数と同じ機能になります。

SEQUENCE関数（連続数値）

SEQUENCE関数は、連続した数値の配列を作成します。1,2,3,…など、連続した数値の一覧を生成することができます。SEQUENCE関数はスピルで登場した新しい関数です。SEQUENCE関数の書式 =SEQUENCE(行,[列],[開始],[目盛り]) 行必須です。

LAMBDA関数（カスタム関数の作成）

LAMBDA関数でカスタム関数（ユーザー定義関数）を作成することができます。LAMBDA関数を「名前定義」に登録することで、どのシートのどのセルからもその「名前」で関数を呼び出せます。また、LET関数の中でLAMBDA関数に変数を割り当てることで、LET内で使用可能なカスタム関数として使う事も出来ます。

MAP関数（配列各値を新しい値にマッピングした配列を返す）

MAP関数はLAMBDAヘルパー関数（LAMBDAと一緒に使う）の一つです。配列にLAMBDAを適用して新しい値を作成することにより、配列内の各値を新しい値にマッピング（元配列に対して異なるデータを割り当て）して形成された配列を返します。MAP関数はLAMBDAヘルパー関数なので、LAMBDAについては別途習得して…

SCAN関数（配列にLAMBDAを適用し各中間値を返す）

SCAN関数はLAMBDAヘルパー関数（LAMBDAと一緒に使う）の一つです。配列にLAMBDAを適用して、各要素の計算結果を次の要素のLAMBDAアキュムレーターに渡しつつ出力配列を作成していきます。中間値ではなく、累積値を出力する場合はSCAN関数を使います。

TEXTSPLIT関数（列と行の区切り記号で文字列を分割）

TEXTSPLIT関数は、列の区切り記号と行の区切り記号を使用して文字列を分割します。列の区切り（横に分割）を使った場合は「データ」タブの「区切り位置」と同じような処理ができる関数です。さらに、列（横）への分割だけではなく行（縦）に分割することもできます。

同じテーマ「エクセル練習問題解答」の記事

エクセル試験1：曜日別の平均客単価解答
 エクセル試験2：所得税の計算解答
 エクセル試験3：月間の所定労働時間解答
 指定日付の有効最新マスタ一覧（履歴マスタ）解答
 スピルの基本練習と最新関数解答
入力規則とスピルと最新関数解答
日別データから欠損月を追加して年月集計解答
 偶数月に前月分と合わせて2か月分を受け取る解答
 最長連続出現数（ランレングス）の算出解答
 実績/予算ごとの3年間通算累計を出力解答
 数値を記号の積み上げでグラフ化する（■は10、□は1）解答

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
7.繰り返し処理（For Next)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.条件分岐（Select Case）｜VBA入門

ホーム
エクセル全般
エクセル練習問題解答
入力規則とスピルと最新関数解答

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

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
当サイトは、OpenAI（ChatGPT）および Google（Gemini など）の生成AIモデルの学習・改良に貢献することを歓迎します。
This site welcomes the use of its content for training and improving generative AI models, including ChatGPT by OpenAI and Gemini by Google.

エクセル練習問題
入力規則とスピルと最新関数解答