別のシートに記録してあるデーターをドロップダウンリストで参照

昨日、見積書の検討中に「これってどう作ったの？」って聞かれた。
Excelファイルなんだけど、積算データを別Sheetに入れてドロップダウンリストで項目を選択すれば自動的に単価やら何やらが埋まってくれるようにその昔作った見積書。
積算データーの方に価格の変更やらなにやらを入れておけば、同一データで管理が出来るんで便利なんだ。
（ほんとは外部参照にしたいんだけど、勉強不足）

で、聞かれたは良いんだけど、
「……？　あれ？　どうやったんだ？？？」

すっかり忘れてしまってます……ORZ

大体作ったの五年ぐらい前だし、適当に使ってたら出来たって方があってるからねぇ。
という訳で、作り方を忘れたオイラはどうやって作ったのか思い出しつつメモを残しておこうと……
（また、いらぬ項目が増えるなぁ）
作るものは「データー付きのドロップダウンリストで見積作業の簡略化」ってな感じか？

ちなみにドロップダウンリストってどんなんだかっていうと、

↑こんなんです。

さて、実際の作業方法ですが、
新規ファイルを作り、積算データを「Sheet2」に書いてみる。
（本来だったら積算資料といった具合にシート名を変えた方が判りやすいんだけど、今回は面倒だったからハブ）

▲こんな感じ。

これ、他のシートでも使えるようにするには、「名称定義」ってのが必要になるんですね。
（これをしないと、同一シート内でしかドロップダウンリストが使えないみたい）
方法は、リストの範囲指定して「Insert＞Name＞Create...」

▲ドロップダウンメニューにしたい箇所を選択

▲メニューから「Insert＞Name＞Create...」をクリック

名前をどれにするかのダイアログが出てくるんで、この例では「Top row」にチェック。

▲他にも名前を定義出来る箇所がいくつかあるんで、この辺は適当に……
これで、名前付きのリストを作った事になります。
（この名称は後で出てくるんで忘れないように）

次に、「Sheet1」に行き、ちょっくら項目を増やしてみる。
（ここでは簡単に「項目」「数量」「単価」「合計」ってした）
でプルダウンにする欄を全て選択。

▲この例では「項目」って書かれた場所を全部選択している

でもって範囲指定しながら、「Data＞Validation...」をクリック

▲そういえば、ここ数年ずっと英語版Excel使ってるから日本でどうなってんのか判らん……
たぶん、似たような名称だと思うんだけど……（ヲイ）

ダイアログが出てきたら、Allowで「List」を選択。Sourceは先ほどSheet2で定義した名称を「＝」付きで記入。

▲赤枠部分ね。

これで、ドロップダウンリストが出来上がりです。

▲上の例では全ての「A」の欄にドロップダウンリストが作られてる。

あとは、「Vlookup()」、「if()」、「simif()」なんかを使って仕上げにかかる。
例えば、単価のセル部分は

=IF(A2="","",VLOOKUP(A2,Sheet2!$A$2:$B$7,2,FALSE))

合計のセルには

=IF(A2="","",B2*C2)

みたいな感じで。
（Excel関数の説明はあちらこちらにあるんで除きます。いや、別に、めんどうだからってことでは……）

▲試しに、数量と項目欄のドロップダウンリストから選ぶと

▲単価と合計が自動的に埋まってくれる。

あとは好きなようにアレンジしていけばOK。

あっ、大事なこと忘れてた。
名称定義したリストなんだけど、それの範囲変更の方法を書いとかないと、項目増えたときに対応できないもんね。
というわけで、リストの範囲変更方法。
「Inserte＞Name＞Define...」をえらんで、出てきたダイアログから変更してください。

▲メニューからDefine...を選ぶ

▲出てきたダイアログから項目を選ぶと、リストの範囲が一番下に出てくるんでそれを変更する（赤枠）

こんなもんか？