スポンサーリンク

入力規則2 リストからの入力 INDIRECT関数

セルに入力するときに、リスト(選択肢)の中からクリックで入力する方法です。

入力するセルをクリックすると入力候補が表示されクリックで選択肢が入力されます。(図はexcel2013)

list

リストからの入力を使うと文字入力の手間が省けますし、入力候補以外は入力できませんので間違いが少なるなると思います。

リストからの入力を行いたいセルを選択します。複数のセルでも構いません。

データタブのデータツールのデータの入力規則をクリックします。データの入力規則のダイアログボックスが開きますので設定タブをクリックします。

list_1

条件の設定の入力値の種類からリストを選択します。

list_2

新たに元の値という項目が現れますのでクリックでカーソルをたてます。入力候補を直接カンマ区切りで入力します。

list_3

または、入力候補がすでに入力されていればセル範囲をドラッグします。

list_4

既に入力されているセルの範囲からリストを作成する場合ですが、別のシートからシートを越えて参照することが可能です。

Excel2013、Excel2010は元の値のボックスにカーソルがある状態で別のシートのシート見出しをクリックするとシートを越えられますのでそのまま別のシートのセル範囲をドラッグして選択します。

list_6

sheet2からsheet1のセル範囲をリストにすると、元の値に =sheet1!$a$2:$a$4 と表示されました。sheet1! はシートを越えて参照するときにどのシートを参照しているかを表しています。また、$ は絶対参照ですね。

Excel2013、Excel2010はこのように簡単なのですが、Excel2007はシート見出しをクリックしてもシートが変わってくれません。この場合は =sheet1!$a$2:$a$4 のようにどのシートのどのセルからどこまでのセルとキーボードで手入力してください。

Excel2003は手入力しても受け付けてくれませんでした。

list_5

このような場合はセルに名前を付けて =名前 のように手入力する方法もあります。セルA2からセルA4にコースと名前を付けました。

list_7

次に名前を使った方法でリストで選択された結果に基づいて次のリストを選択できるようにしてみます。セルA7でマイクロソフトを選択すればセルB7ではWord、Excel、PowerPointのどれかが選択できて、ホームページ作成を選択したらセルB3からセルD3までの値が選択できるというようなものです。

list_8

これを実現するためにはセル範囲に名前を付ける必要があります。今回はセルB2からセルD2までをセルA2のマイクロソフトオフィスという名前にします。この時セルA7で選択される名前、すなわちセルA2からセルA4までの値と同じ名前にする必要があります。

セルB2からセルD2を範囲選択し名前ボックスをクリックします。

list_9

そのままマイクロソフトオフィスと入力しエンターキーをたたきます。

list_10

以降、セルB3からセルD3を範囲選択し名前ボックスにホームページ作成と入力しエンター。セルB4からセルD4を範囲選択し名前ボックスにプログラムと入力しエンターします。

名前の修正、削除、新規作成は数式タブの定義された名前の名前の管理で可能です。

list_11

セルB7でリストから入力したいのですが元の範囲のところは何を入れればよいでしょうか

list_12

=マイクロソフトオフィス (名前)と入力すればセルB2からセルD2までが入力候補になりますが、セルA7でホームページ作成が選択されてもセルB2からセルD2までのWord、Excel、PowerPointが表示されてしまいます。

この元の値が常にセルA7の値が入れば解決です。元の値に =A7 としたいのですがこれだと文字通りセルA7の値しかリストに表示されません。

list_13

INDIRECT関数を使います。

INDIRECT(参照文字列, [参照形式])

INDIRECT関数は文字列の参照を返します。少しわかりにくいですが、参照を返すというのがポイントです。

list_14

セルB2には =A1 という式が入っています。当然セルA1の値のb1という文字列が返ってきます。セルB3には =indirect(a1) という式が入っています。セルA1のb1という文字ではなくセルA1の参照ということでB1の値が返ってきます。

list_15

=indirect(a7) と手入力してください。(関数名の大文字小文字は関係ありません)

途中、絶対参照の $ がついていたり、ついていなかったりしますが、オートフィルでコピーしない場合は $ があってもなくても同じです。セルをクリックすると自動でつきます。ただし、複数のセルで同じように使うため、オートフィルでコピーした場合、絶対参照の $ がついていると思ったようにならないことがあるので気をつけてください。

スポンサーリンク

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です