同じ書類の入力パターンごとに、以下のようにファイルを複数作成している場合、書類のフォーマットの修正が必要になった際にすべてを修正する必要が出てきます。

そのため今回は、１つのファイルで複数の入力パターンを簡単に管理する方法について解説していきます。

※こちらで作成したファイルは、記事の最後にて配布しています。

📘 Excel小技１００選（５００円）

1. 事前準備

今回は、以下の２枚のシートをもとに作成していきます。

「出張申請書」シートの「目的」と「詳細」の項目に入力するパターンを、「_パターン」シートに表形式でまとめています。

2. ドロップダウンリスト「目的」の作成

まずは、「出張申請書」シートの「目的」をドロップダウンリストで入力できるようにしていきます。

「目的」のセルを選択し、[データ]タブから[データの入力規則]を選択します。

表示された設定画面にて、[入力値の種類]を[リスト]にし、[元の値]に「_パターン」シートの「目的」の範囲を指定します。

このままですと、指定した範囲の値以外を入力することができないので、[エラーメッセージ]タブの[無効なデータが入力されたらエラーメッセージを表示する]のチェックを外します。

これで確定することで、「出張申請書」シートの「目的」がドロップダウンリストで選択することもできるし、直接入力もできるようになります。

3. ドロップダウンリスト「詳細」の作成

次は、「出張申請書」シートの「詳細」をドロップダウンリストで入力できるようにしていきます。

「詳細」は、「_パターン」シートの該当する「目的」の行の「詳細」の値になります。

そのため、選択された「目的」に対応する「詳細」を抽出する数式を、表の下に入力します。
指定した「目的」の行の、２列目の「詳細」を抽出するには、VLOOKUP関数を活用し、以下のような数式を入力します。

=VLOOKUP(出張申請書!B9,_パターン!B3:C7,2,FALSE)
// 出張申請書!B9：「出張申請書」シートの「目的」のセル（検索値）
// _パターン!B3:C7：「_パターン」シートの表の範囲（範囲）
// 2：抽出したい項目の先頭からの位置（列番号）
// FALSE：「出張申請書」シートの「目的」と完全一致した行の値を抽出（検索方法）

※あらかじめ、枠組みの用意と「選択中」という値を入力しています。

上記の数式を入力して確定することで、該当する行の「詳細」を抽出することができます。

※改行されずに抽出されていても問題ないです。

最後に、数式で抽出した「詳細」をドロップダウンリストで表示させます。
そのため、「出張申請書」シートの「詳細」のセルを選択し、[データ]タブの[データの入力規則]を選択します。

表示された設定画面にて、[入力値の種類]を[リスト]にし、[元の値]に「_パターン」シートの数式を入力したセルを指定します。

そして、先ほどと同様に、[エラーメッセージ]タブの[無効なデータが入力されたらエラーメッセージを表示する]のチェックを外します。

これで確定することで、「出張申請書」シートの「詳細」がドロップダウンリストで選択した後に、直接修正できるようになります。

4. 問題点

ただこのままですと、以下の３点の問題点があります。

①「目的」を選択していないときに、「詳細」のドロップダウンリストに表示される値が「#N/A」になる

②「目的」を「その他」にしたときに、「詳細」のドロップダウンリストに表示される値が「0」になる

③各目的に対して、「詳細」の候補が１つしか選択できない

ということで、ここからは３つの問題点を１つずつ解決していきます。

5. 問題点①の解決

続きはIT予備メンバー限定です。

リンクの共有はこちらから行えます。

  リンクコピー    X Facebook はてなブックマーク Pocket