また同じことの繰り返し…
いつまでこの単純作業を続けるんだ…
あなたも、日々のデータ入力にうんざりしていませんか？
・毎日毎日、大量のデータ入力
・終わる気がしない
・入力ミスが多い
・とにかく時間がかかる
・集中力が途切れる
「もっと効率的にデータ入力できたら…」何度もそう思ったことがある方に朗報です！
この記事では、エクセルの連動リストを活用して、データ入力の悩みを解決する方法を徹底解説。
- 入力時間を大幅に短縮
- ミスを劇的に減らす
- 図解つきで誰でも簡単に設定できる
- リストデータの種類によっておススメ設定を紹介
この記事を読めば、「コピペ地獄」から抜け出してスマートなデータ入力術を身につけることができるようになります！
さぁ、あなたもデータ入力を超効率化して、残業知らずのデキる人になりましょう！
コピペ地獄はもう終わり！連動リストでミス撲滅＆効率爆上げ！
「この苦痛をなんとかしたい…」手作業から卒業→爆速データ入力
繰り返される単調なデータ入力作業って、まるで出口の見えない迷路ですよね…。
時間と体力をめちゃくちゃ消耗しますし、タイプミスやコピペの繰り返しでミスも発生しがちです。
私も昔は同じでした。「また今日もアレか…」ってため息ばかりでしたね…。
だからこそおススメしたいのが「連動リスト」です！！
「終わりの見えない手作業から解放されたい」
「もっと楽に、速く、正確に仕事したい」
「疲れる業務を何とかしたい」
連動リストは、疲弊しきったデータ入力ライフを「劇的に」変えることができる機能です。
具体的な作成方法を詳しく解説していきますね！
「連動リストの何が便利なの？」メリットと活用場面をわかりやすく解説
連動リスト（多階層リスト、階層型ドロップダウンリストとも呼ばれます）とは、エクセルでのデータ入力を効率化するための強力な味方です。
事前に入力内容を登録しておくことで入力の手間を省き、選択するだけで入力が完了する
という「入力規則の設定機能」を使用し、複数の情報を連動させて「全部選択するだけでOK」な状態にできるんです。
リストを段階的に絞り込むことで、目的のデータへスムーズにアクセスでき、入力作業に伴うストレスを大幅に軽減できますよ。
大前提として「連動リスト＝親リストと子リストが連携しているもの」と考えてください。
親リストで項目を選ぶと、その選択に応じて子リストの選択肢が動的に変化する、というのが最大の特徴ですね。
↑この画像で言うと、「都道府県」で東京都を選択したら「市区町村」には東京都に存在する地区だけが表示されるという感じです。
連動リストのメリット
- 選択肢を選ぶだけなので、入力ミスがない
- データの整合性が向上する
- 直感的に操作できるため、エクセル初心者でも簡単
- 選ぶだけなので入力時間を短縮できる
- 業務全体の効率向上に大きく貢献してくれる
「いいことづくめ」とは、まさにこのこと。
活用シーン
- 製品カテゴリとサブカテゴリ
- 顧客の地域情報（国、都道府県、市区町村）
- プロジェクトのフェーズとタスク
など、階層的な構造を持つデータ入力に連動リストは最適です。
皆さんの業務でも、きっと役に立つ場面があるはずですよ！
「何を準備すればいい？」連動リスト作成で大事なポイント
連動リストを作成する上で、一番大事な準備。それは「データ構造の設計」です。
最初に、どんな階層構造でデータ管理をしたいのか、これを明確にしましょう。
例えば、3階層のリストを作る場合、「大カテゴリ」→「中カテゴリ」→「小カテゴリ」のように、階層ごとの項目を洗い出すのがスタート地点ですね！
次に、各階層のリスト範囲をエクセルシート上に定義します。
一般的には、別のシートに各階層のデータをリスト形式で整理し、それぞれの範囲に名前を定義しますね。
この事前準備を丁寧にやっておくと、後のリスト作成作業が、ほんとにスムーズに進みますよ。
詳細例
今回は、3階層リストで「食品」→「野菜」→「葉物野菜」という階層構造を設計するために必要な準備を見ていきましょう。
まずはリストデータ用に新規シートを追加して（Sheet2とします）、以下のようなリストを作りましょう。（今後の説明にも転用します）
| カテゴリ | サブカテゴリ | 商品名 |
|---|---|---|
| 食品 | 野菜 | キャベツ |
| 食品 | 野菜 | ほうれん草 |
| 食品 | 果物 | りんご |
| 食品 | 果物 | みかん |
| 家電 | テレビ | 液晶テレビ |
| 家電 | テレビ | 有機ELテレビ |
| 家電 | カメラ | 一眼レフ |
| 家電 | カメラ | ミラーレス一眼 |
リストができたら、次はリスト範囲に名前を定義しましょう。以下、Sheet2のA列（大カテゴリ）に名前を定義する方法を紹介します。
ここでは名前定義の基本的なやり方を紹介するだけなので、実際に定義する時は必要な各カテゴリ全部を行うものと思ってください。
- ①定義したい範囲を選択
- ②「数式」タブ →「名前の定義」を選択
- ③「名前」欄に”カテゴリ”と入力 →「OK」を押す
手順①の「定義したい範囲を選択」時に”カテゴリ”という文字列（A1セル）を含めて選択していると、「名前」欄には最初から”カテゴリ”と入力されます。（先頭の内容をタイトルにしようとするため）
ゆうまただし、↑の方法だと選択肢内に「カテゴリ」という文字列まで含まれてしまうため、それがイヤであれば、名前定義範囲を選択する際に必要な範囲（画像だとA2～A9）だけ選択し、「名前」欄は手入力しましょう。
名前定義の方法 メリット デメリット カテゴリまで含めて選択 「名前」欄に最初から入力される 選択肢にカテゴリ名が入ってしまう 必要な内容だけ選択 選択肢にムダがない 「名前」欄に手入力する必要あり 好きなほうを選んでください。
このようにリストと名前定義を事前に準備しておくことが、連動リスト作成の基本ですね！
「入力ストレスゼロへ！」リストデータ形式ごとの設定方法の違い
連動リスト作成の大元になるリストデータですが、使用環境によって形式が異なると思います。
以下の画像のように「リストの片側に向かって細かく分類されていく」タイプのリストや、
以下のように「最上段にカテゴリ、その下に所属が記入されている」タイプのリストなどなど。
厳密にはもっといろいろ種類があるでしょうが、よく見るこの2パターンに適した連動リストの作成方法を紹介していきますね！
リストの片側に向かって細かく分類されていくタイプ
- INDIRECT関数だけで作る基本的な「2階層リスト」
- INDIRECT関数だけで作る「多階層リスト」（3階層、4階層）
- OFFSET、COUNTA、UNIQUE、FILTER関数を使う「動的範囲調整の多階層リスト」
最上段にカテゴリ、その下に所属が記入されているタイプ
- テーブル機能とINDIRECT関数で作る「動的範囲調整の多階層リスト」
使用しているリストが近い種類であれば、ぜひ参考にしてみてください！
INDIRECT関数だけで作る「2階層リスト」
最初は、基本となる2階層の連動リストから作ってみましょう。
基礎知識は以下の記事で解説していますので、詳細を知りたい方はどうぞ↓
まずは、メインとは別のシートで以下のような「リストデータ」を作成します。（先ほどの説明で作成済の場合は、そのまま使ってください）
| カテゴリ | サブカテゴリ | 商品名 |
|---|---|---|
| 食品 | 野菜 | キャベツ |
| 食品 | 野菜 | ほうれん草 |
| 食品 | 果物 | りんご |
| 食品 | 果物 | みかん |
| 家電 | テレビ | 液晶テレビ |
| 家電 | テレビ | 有機ELテレビ |
| 家電 | カメラ | 一眼レフ |
| 家電 | カメラ | ミラーレス一眼 |
- ①リストデータシートで、メインカテゴリの名前を定義する
A2～A9までを選択しましょう。
- ②「数式」タブ →「名前の定義」を選択
- ③「名前」欄に”カテゴリ”と入力 →「OK」を押す
- ④サブカテゴリの名前を定義する
「食品」カテゴリに該当する「野菜」「果物」を選択します。
- ⑤「数式」タブ →「名前の定義」を選択
- ⑥「名前」欄に”食品”と入力 →「OK」を押す
同じ手順で「テレビ」と「カメラ」を選択 →「名前」欄に”家電”と入力してOKを押せば、名前の定義は完了です！
- ⑦入力規則を設定するため、メインシートへ移動
今回はA1、B1セルに連動リストを作成します。メインシートのA1セルを選択しましょう。
- ⑧「データ」タブ →「データの入力規則」を選択
- ⑨「設定」タブ →「入力値の種類」をリストに変更 →「元の値」に ”=カテゴリ” と入力する
これで最初の選択肢が完成しました！
- ⑩2番目の選択肢作成のため、B1セルを選択 →「データの入力規則」を選択
- ⑪「設定」タブ →「入力値の種類」をリストに変更 →「元の値」に以下の数式を入力するSQL
=INDIRECT(A1)
これで連動リストの完成です！！
A1セルで選択した内容に応じて、B1セルの選択肢の内容が変化するようになりました！
どうですか？意外と簡単ですよね！
INDIRECT関数だけで作る「多階層リスト」（3階層、4階層）
基本の2階層リストができたら、次は3階層、4階層と階層を増やしてみましょう。階層が増えることでより細かい分類が可能になり、入力ミスも減らすことができます。
と言っても、難しく考えることはありません！
先ほどの「サブカテゴリの名前を定義する(←リンク)」と同じ手順で、商品名を名前定義 → C1セルにINDIRECT関数で入力規則設定するだけです！
- ①商品名を名前定義する
サブカテゴリごとに登録するので、まずは「野菜」に該当する「キャベツ」と「ほうれん草」を選択します。
- ②「数式」タブ →「名前の定義」を選択
- ③「名前」欄に”野菜”と入力 →「OK」を押す
「野菜」の名前定義が終わったら、残りの「果物」「テレビ」「カメラ」も同じ要領で名前定義しましょう。
ちなみに、今回は名前を単純に「野菜」としましたが、シンプルな名前の場合、階層が増えていくと混乱する可能性が出てきます。
混乱を避けたいのであれば、「食品_野菜」のように階層がわかる構造で作成し、「カテゴリごとに重複しない名前を定義する」ことを意識しましょう！
（使用者が混乱しないのであれば、シンプルな名前でも機能します）定義例 メリット デメリット 野菜 シンプルで分かりやすい 「カテゴリごとにサブカテゴリを分ける」場合には向いていない 食品_野菜 「カテゴリ＋サブカテゴリ」のようにグループ分けできる 入力規則の数式が少し長くなる - ④3番目の選択肢作成のため、C1セルを選択 →「データの入力規則」を選択
- ⑤「設定」タブ →「入力値の種類」をリストに変更 →「元の値」に以下の数式を入力するSQL
=INDIRECT(B1)
これで3階層リストの完成です！
もし名前の定義の段階で「食品_野菜」のように「カテゴリごとに重複しない名前」をつけた場合、「元の値」に入力する数式が以下のように変化しますのでご注意を。
SQL=INDIRECT(A1&"_"&B1)この数式は「A1とB1で選択した内容に応じて、C1セルに表示する内容を変化させる」というもの。
名前の定義で「食品_野菜」という「カテゴリごとに重複しない名前」をつけた場合にのみ、この書き方が必要になります。（定義した名前と同じにしないと機能しないということ）
このように、INDIRECT関数だけでも多階層リストは作れますが、
「データが増減する場合」
「リストの先頭行をスキップしたい場合」
などは、OFFSET、COUNTA、UNIQUE、FILTERなどの複数の関数を使うことでより柔軟なリスト作成が可能になります。
| 用途・状況 | INDIRECTのみ | 複数の関数を使用 |
|---|---|---|
| リストが固定 | ✅ 向いてる | ❌ 不要 |
| リストが増減する | ❌ 手動更新が必要 | ✅ 自動対応可能 |
| 数百～数千のデータを扱う | ❌ 名前定義が増えすぎる | ✅ 動的管理しやすい |
| 計算負荷が少ない方が良い | ✅ 高速・軽い | ❌ 計算が重くなる可能性あり |
| リストをセルの値に応じて動的に切り替えたい | ❌ できない | ✅ 可能 |
| 初心者向け | ✅ 簡単 | ❌ 設定がやや難しい |
INDIRECT関数だけでの連動リスト作成は、構造が単純だから作りやすいというメリットがある一方で、データが増えてきた時の追加作業の手間が多いというデメリットもあります。
「どういうものなの？」OFFSET関数とは何か
COUNTA、UNIQUE、FILTER関数は使ったことがある人も多いかと思うので、聞きなれないOFFSET関数だけ簡単に紹介しますね。
OFFSET関数とは？
OFFSET関数とは、指定したセルや範囲から、行方向と列方向に指定した数だけずれた位置にある範囲を参照する関数です。
基本的な構文
=OFFSET(基準, 行数, 列数, [高さ], [幅])- 基準: ずれる元のセル範囲
- 行数: 下にずれる行数（上にずれる場合はマイナスの値）
- 列数: 右にずれる列数（左にずれる場合はマイナスの値）
- [高さ]: 参照する範囲の高さ（省略可能）
- [幅]: 参照する範囲の幅（省略可能）
OFFSET関数が役立つケース
リストの先頭行を項目名としてスキップしたい場合
リストの先頭行に項目名が入っている場合に、データ範囲のみを連動リストとして使用したいことがあります。
OFFSET関数で行数をずらすことで、項目名をリストから除外できます。
リスト範囲が動的に変わる場合
リストのデータが追加・削除されるなど、リスト範囲が変動する場合に、OFFSET関数とCOUNTA関数などを組み合わせることで、常にデータが入っている範囲だけを自動的に参照するようにできます。
複数の関数で作成する「動的範囲調整の多階層リスト」
ではここから「OFFSET、COUNTA、UNIQUE、FILTER関数を活用して、データの追加・削除にも自動対応できる」リストの作り方を紹介します。
以下の画像のような「リストの片側に向かって細かく分類されていく」タイプのリストに向いた多階層リストですね。
特徴
✅ 細かい名前定義なしで、該当する商品名のみが選択肢に表示される
✅ データの増減を自動的に調整してくれる
✅ 最初の設定が少し手間だけど、メンテナンスの手間が最小限
使用するデータ
リストデータとして、また以下のデータを使用します。
| カテゴリ | サブカテゴリ | 商品名 |
|---|---|---|
| 食品 | 野菜 | キャベツ |
| 食品 | 野菜 | ほうれん草 |
| 食品 | 果物 | りんご |
| 食品 | 果物 | みかん |
| 家電 | テレビ | 液晶テレビ |
| 家電 | テレビ | 有機ELテレビ |
| 家電 | カメラ | 一眼レフ |
| 家電 | カメラ | ミラーレス一眼 |
設定手順について
これから関数を使用して名前定義や入力規則の設定をしていきます。以下を前提として進めますね。
・Sheet1＝リストデータ（以降、データシート）
・Sheet2＝プルダウンリスト（以降、プルダウンシート）
・リストデータ本体とは別に、補助データを表示する場所を設定
（データシート内ならどこでもOK。今回はデータシートのE2、F2にします）
・プルダウンリストを表示するセルを設定
（プルダウンシートならどこでもOK。今回はプルダウンシートのA1～C1にします）
- ①データシートのA列全体を選択
- ②「数式」タブ →「名前の定義」を選択
- ③「名前」に”カテゴリ”と入力し、以下の数式を「参照範囲」に入力SQL
=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A)-1, 1)
この設定で、「A1セルを無視し、A2以下のデータが増減しても自動で対応してくれる」という設定ができました。
- ④プルダウンシートのA1セルを選択 →「データの入力規則」を選択
- ⑤「設定」タブ →「入力値の種類」をリストに変更 →「元の値」に ”=カテゴリ” と入力する
これでプルダウンシートのA1セルに選択肢が表示されたはずです。手順通りなら「食品」「家電」が表示されますので、ひとまず「食品」を選択しておいてください。（どちらでもいい）
- ⑥データシートのE2セルに以下の数式を入力SQL
=UNIQUE(FILTER(Sheet1!B:B, Sheet1!A:A=Sheet2!A1, ""))
入力後のE2セルに表示される内容は、プルダウンシートのA1セルで選択したものに対応しています。
- ⑦データシートのE列全体を選択 →「数式」タブ →「名前の定義」を選択
- ⑧「名前」に”サブカテゴリ”と入力し、「参照範囲」に以下の数式を入力SQL
=Sheet1!$E$2#
最後についている「#」は、「E2セルのスピル機能で表示されている内容全体を参照範囲にする」というものです。
- ⑨プルダウンシートのB1セルを選択 →「データの入力規則」を選択
- ⑩「設定」タブ →「入力値の種類」をリストに変更 →「元の値」に ”=サブカテゴリ” と入力する
これでプルダウンシートのB2セルにも選択肢が完成しました。今回は「野菜」を選択しておきましょう。
ゆうまここまでの手順で、2階層リストは完成です！
同じ手順で3階層目を作ってしまいましょう！ - ⑪データシートのF2セルに以下の数式を入力するSQL
=UNIQUE(FILTER(Sheet1!C:C, Sheet1!B:B=Sheet2!B1, ""))
- ⑫F列全体を選択 → 以下の内容にて名前を定義する
「名前」→ 商品名
「参照範囲」↓SQL=Sheet1!$F$2#
- ⑬プルダウンシートのC1セルに入力規則を設定し、「元の値」に”=商品名”と入力する
これで「複数の関数を使用した動的範囲調整の多階層リスト」が完成しました！！
嬉しいポイント
✅ データの追加・修正があっても選択肢が自動で調整される。
✅ 追加・修正のたびに名前定義をやり直す必要がない。
✅ 前の階層で選択したものに関連する内容のみが、次の階層に表示される。
注意点
いま紹介した「複数関数を使用したプルダウンリスト」は個人的にすごくおススメなのですが、OFFSET関数が揮発性関数と呼ばれる種類のものであるため、数万行以上のリストになると動作が重くなるという弱点があります。
ワークシート内の他のセルに変更がなくても、ブックが開かれたり、任意のセルが編集されるたびに自動的に再計算される関数のこと。
これにより、シート全体の再計算頻度が増加し、大量に使用するとExcelの動作が遅くなる可能性があります。
業種によっては膨大なデータを扱うこともありますので、動作遅延やデータクラッシュを避けたいのであれば、次に紹介するテーブル機能の活用も検討しましょう！
テーブル機能とINDIRECT関数で作る「動的範囲調整の多階層リスト」
データ範囲は自動で拡張したいし、追加のたびに参照範囲の再設定するのは面倒だからイヤだな…。でもデータ量が多いからOFFSET関数だと重くなりそう。困った。
そんな方におススメしたいのが「テーブル機能」です！！
これから紹介するのは、以下の画像のような「最上段にカテゴリ、その下に所属が記入されている」タイプのリストに向いた多階層リストの作り方ですね。
特徴
✅ テーブルに備わっているフィルター機能や集計機能が使えるため、分析や管理がラク
✅ テーブル自体に自動拡張機能があるため、参照範囲再設定の手間が減る
✅ 全体的に設定がわりとラク
✅ テーブルにした時点で書式設定が適用されるため、リストデータが見やすい
使用するシート
①以下の画像のような「本部名・支部名の情報」のリストデータが入力してあるシート↓
②以下の画像のような「支部名・人物名の情報」のリストデータが入力してあるシート↓
③プルダウンリストを作成するシート（3階層リストを作るため、使用するのはA1～C1セル）
この3シートを使用します。
具体的な設定手順
- ①各リストデータをテーブル化する
・リスト内で「Ctrl+A」を押して全選択
・そのまま「Ctrl+T」でテーブル化開始
・ダイアログ内の「先頭行をテーブルの見出しとして使用する」にチェックされていることを確認してOKを押すという手順で、用意した各リストデータをテーブル化しましょう。
- ②各テーブルに名前を定義する
テーブル化した範囲内を選択すると、リボンに「テーブルデザイン」タブが出現します。
左端に「テーブル名：」という項目があるので、各テーブルを好きな名前に変更しましょう。
- ③支部名と人物名を連動するように名前を定義する
・「支部名・人物名の情報」が入力してあるリストデータのA1セルを選択
・「Ctrl+A」でリストを全選択
・「数式」タブ →「選択範囲から作成」を選択
・「上端行」だけチェックがついた状態でOKを選択
成功していれば、左上にある「名前ボックス」に支部名の一覧が登録されているはずです。
これで、名前定義までの準備は完了しました！
- ④1階層目のプルダウンリストを作成
・プルダウンリストを作成するシートのA1セルを選択
・「データ」タブを選択
・「データの入力規則」を選択・「設定」タブを選択
・「入力値の種類」をリストに変更
・「元の値」に以下の数式を入力SQL=INDIRECT("拠点[#見出し]")
これで1階層目が完成しました！
入力規則の設定が完了したら、選択肢から適当に選んでおきましょう。空白のままだと、次のセルの設定ができません。
- ⑤2階層目、3階層目のプルダウンリストを作成
基本的な手順は1階層目作成と一緒です。B1セル、C1セルにそれぞれ入力規則を設定していきましょう。
B1セルへの入力規則の設定時に、「元の値」に入力する数式↓
SQL=INDIRECT("拠点["&A1&"]")C1セルへの入力規則の設定時に、「元の値」に入力する数式↓
SQL=INDIRECT("氏名["&B1&"]")これで3階層プルダウンリストの設定が完了しました！！
知っておきたい豆知識
用意したデータをテーブル化した時点で、テーブル機能によって自動拡張に対応するようになります。
これで、新しいカテゴリを追加したい時や、カテゴリを減らしたい時も最小限の手間で済みますね！
今回は説明のためにキレイにリストが埋まった状態でしたが、リストデータって空白であることも多いですよね？
この「リストが空白の状態」でプルダウンリストを作成すると、以下の画像のように空白列ができちゃいます。
気にならないなら良いのですが、さらに厄介なのが「空白セルだと思っている場所にスペースが入力されていた」場合。プルダウンリストの空白列が2行になるんです…。
これが気になる方は、以下の手順で空白セルを埋めてやりましょう！
- ①空白含めた表全体を「Ctrl+A」で全選択する
- ②ショートカットキーで空白だけをサクッと選択する
・Ctrl+G
・Alt+S
・K
・Enterこの順番で押すことによって、対象範囲内の空白全体を選択した状態になります。
- ③そのまま好きな文字列を入力 →「Ctrl+Enter」を押して全空白セルへの一括入力
空白セルを埋めることが目的なので、文字列は何でもOKです。
↑この画像だとA4セルが選択の対象外になっています。選択対象は「空白セル」なので、対象にならなかったA4セルにスペースがあるということですね。
スペースを消して、ムダな選択肢を減らしておきましょう。
まとめ
この記事では、連動リスト（多階層リスト）を作成してデータ入力を効率化し、ミスとムダな時間を減らす方法を詳しく解説しました。
この記事の主要ポイントを、改めておさらいしましょう。
- INDIRECT関数で多階層リストを作る方法
- 複数の関数を組み合わせて作る方法
- テーブル機能との併用で管理しやすいリストを作る方法
連動リストをマスターすれば、データ入力はもう苦行ではありません。正確性とスピードが向上し、残業時間も大幅に削減できるはずです。
ぜひ連動リストを業務に取り入れて、ストレスなく仕事できる環境を手に入れてください！
あなたのエクセルスキルがレベルアップし、仕事の成果も最大化することを心から応援しています！
最後までお読みいただき、ありがとうございました！
今回は、
「本部名・支部名の情報」が入力してあるリストデータ →「拠点」
「支部名・人物名の情報」が入力してあるリストデータ →「氏名」
とします。