Excelの入力規則で2列表示したい

質問

入力規則を使って、社員コードを入力する際に
10001　山田太郎
10002　鈴木花子
10003　佐藤大介
のように、ドロップダウンリストに「社員コード」「社員名」と2列表示させたいのです。
そして、例えば山田太郎を選択すると、セルには「10001」だけが入力されるようにしたいのですが・・・
（Accessのルックアップフィールドみたいな感じ）

[入力規則]-[リスト]で、社員コード表を選択しても、単一の列または行でなければいけませんとエラーが出ます。
かといって[入力規則]-[リスト]で範囲指定をカンマ区切りで
10001 山田太郎,10002 鈴木花子,・・・と入力しても、エラーは出ませんがドロップダウンリストから選択したときに社員コード表と社員名が一緒にセルに入力されてしまいます。

なにか良い方法はありませんでしょうか？
よろしくお願いいたします。

回答No.4

nihonjinn · ベストアンサー

番号と社員名の間にスペースが入っているなら
メニューバーから「データ」→「区切り位置」で
1「カンマやタブなど－－－」にチェックを入れて「次へ」
2「区切り文字」で「スペース」にチェックを入れて「次へ」
3「完了」
で番号と社員名が違う列へ分けることが出来ます。
番号のデータがA2からA4、社員名がB2からB4にあるとし
C1にリストを設定して番号を入れ、D1に社員名が反映するようにするときにはD1に
=INDEX(A2:A4,MATCH(C1,B2:B4))
と入れてやるとうまくいくと思います。

お礼

ご回答ありがとうございます。

ちなみに、=INDEX(A2:B4,MATCH(C1,A2:A4),2)で良かったですよね？？

意図していたものとちょっと違うのですが、とても良いです。
（というかこの方がいいです）
とても参考になりました。ありがとうございました^^

回答No.7

suzusan7

#5です。

説明にも簡単に書きましたが、
ドロップダウンリストではセル内改行は無効ですが、
選択後はセル内改行が有効となるという違いを利用しています。
しかし、セル内容は同じな訳です。
つまり、ドロップダウンリストのセルをキーにしても
Vlookupは使用できるわけです。
文字での検索になるので、検索の型をfalseにしておけば、問題なしです。

社員コード、社員名もVlookupで引っ張ってくるなら、
例で示した構成を、B列に社員コード、C列に社員名、
A列に社員コードと社員名の合成を使用すればいいだけですよね。

どうしても社員コードをキーにしたいということであれば、
上記のようにして、Vlookupで社員コードだけを引っ張ってきて
以後はその社員コードをキーに使用するということで問題ないかと。

または、ドロップダウンリストがセルD1にあるとして
=LOOKUP(10^17,LEFT(D1,ROW(A1:A20))*1)
と入力して、Alt＋Shift＋Enterで確定（配列形式）とすれば、
ドロップダウンリストのセルから数字部分だけ、つまり社員コードを
抜き出すことができます。
（この式は社員コードが氏名より前にある場合です）

社員コードをキーにするということにこだわるのであれば、
上記のように、ドロップダウンリストから社員コードを引っ張ってくれば
よろしいだけかと思いますが。

あと、訂正をひとつ。
セル内改行がドットで表示されると前回書きましたが、
Excel97ではそうでしたが、Excel2002ではドットで表示されませんでした。
このあたりはバージョンで異なるようです。

お礼

再度ご回答、ありがとうございます。

そうですね、VLOOKUP自体は社員コードと社員名でも検索してくれると思います。ただ今回社員コードのみをキーにしたいのは（またまた説明不足でしたが）、会社のホストコンピュータから出力されるEXCELの営業成績表を、VLOOKUPの検索範囲として参照したいから・・・などの理由からです。

いろいろなワザ（！）を駆使すればなんとかなる事なのかもしれませんが、あまりひねらずに出来る方法があるのでは？との思いから質問してみました。

この場を借りて、ご回答いただいた皆様にお礼申しあげます。
とても勉強になりました。ありがとうございました！

回答No.6

coco1

#1です。一例を補足です。

B列に社員コードを入力する必要があるとしますと、A列に社員コード＋氏名を入力する作業列を作成します。
ここではA列としましたが、実際には印刷や集計に影響のない、かつ、入力に便利な列を選択して下さい。

さらに、先に回答したようにAA列：社員コード＋氏名、AB列：社員コード、AC列：氏名を用意したテーブルを別に用意して表引き用にします。

そうして、入力する際にはAA列をリストソースにしてA列に入力します。

この後、実際に社員コードが欲しいB列には、A列をキーにして、AA～AB列に対してvlookupを行えば、社員コードだけが取り出せますので、結果的には望む体裁になりませんか？　もちろん、氏名が必要な場合はこの範囲をAC列にまで拡げればよいですし。

お礼

なるほど、よくわかりました。
この方法ですと、社員名＋社員コードをドロップダウンから選択でき、さらに関数を組み合わせると、別セルに社員コードを表示できるということですね。
参考になりました。
再度ご回答、ありがとうございました。

回答No.5

suzusan7

こんばんわぁ。

行高さの設定等で使用できない場合もあるかもしれませんが、
次の方法ではいかがでしょうか？

セルA1～A3に社員コードが、
セルB1～B3に社員名があるものとします。
セルC1に　=A1&""&B1
と入力します。ただし、""の中はAlt+Enter（セル内改行）を入れてください。
Alt＋Enterを押すと数式バーの表示も改行されますので、状況がわかるかと思います。
また、セル内改行の前後にスペースを入れておくと、後で見やすくなると思います。

これで確定をすると、セルC1には10001・山田太郎
という感じで社員コードと氏名の間にドットが入った表示になるかと思います。
（セルの設定をいじっていないことを想定してます）
セルC1をセルC2～C3にコピーして下さい。
この表示がドロップダウンリストに表示されることになりますので
見栄えはセル内改行の前後にスペースを入れる等で適当に調整してください。

次にセルD1に入力規制を設定するものとします。
セルD1のセルの書式設定で
折り返して全体を表示するにチェックを入れ、
文字配置の縦位置を上詰めにします。

この状態でセルC1～C3を元の値としてリストの設定をします。

ドロップダウンリストにはセル内改行は適用されないので
“10001・山田太郎”のようにセル内改行はドットで表示され
選択後はセル内改行が有効となり、折り返されてセルの下のほうに行くため
表示されなくなります。
（正確には表示範囲外に社員名はあることになります。）

ドロップダウンリストではドットが表示されますが、
選択後は社員コードだけになるかと思います。
（見栄えだけですけど）

行の高さがちょっと高いようでしたら、Alt＋Enterを２つ入れたりすると
もう少し調整ができたりします。

お礼

ご回答ありがとうございます。

説明がまずかったかもしれません。
補足しますと、社員コードをキーにしてVLOOKUPで別表から社員の営業成績を引っ張ってくるつもりです。
その時、社員コードを手入力するのは手間なので、ドロップダウンリストから選択（入力規則で設定）したいのです。
ただ、ドロップダウンリストから選択するときに、社員コードだけ見ても誰のものかわからないので、一緒に社員名が表示されたら便利だな、でもドロップダウンリストから選択した後セルに入力されるのは社員コードだけにしないとVLOOKUPが使えないしな・・と思ったわけなのです。

でもそんな贅沢なこと無理ですよね・・

回答No.3

imogasi

＃２です。セルには「10001」「だけ」をと言うところを見落としました。済みません。別セルに=mid(g1,1,3)とか入れるやり方やＶＢＡで
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then 'Ｇ列の場合
ActiveCell = Mid(ActiveCell, 1, 3) '３桁の場合
End If
End Sub
でそれらしくはなりましたが、なぜか反応は鈍いし、このやり方はどうでしょう。
ActiveCell = の行は
Target = Mid(Target, 1, 3)でも同じでした。
ＶＢＡでリストボックスをつかい、リストには多列表示して、１列目のみセルにセットなども、本の例題で見た気がしますが。

お礼

再びご回答ありがとうございます。

やはりＶＢＡでやるしかなさそうですね・・
ＶＢＡは全く知識がないので、これから勉強してみようと思います。

imogasiさんはＶＢＡやその他いろいろお詳しい方のようですね。
今後もお世話になるかもしれません(^^ゞ
またよろしくお願いします。

回答No.2

imogasi

Ｅ列、Ｆ列に
123 山田
124 小島
125 松屋
とある時
Ｇ１に関数式=E1&F1と入れて、式を複写します。
123山田
124小島
125松屋
となります。
=E1&" "&F1
も見やすいかも知れません。
データ－入力規則－リスト－（元の値）Ｇ１：Ｇ３
でどうですか。
Ｅ列の文字数が行で違う時は一定文字数を決めて
（一定文字数）－（実際の文字数）文の空白桁を挟んで
Ｆ列の先頭が揃うように出来るので、そうすると見やすいです。揃え方の実例は本日のＮｏ.７１７１８２の私の回答
をご参考にしてください。

お礼

ご回答ありがとうございます。

お教えいただいた方法、早速試してみました^^
ですが、これでもやはり、ドロップダウンリストから選択したときに社員コードと社員名が一緒にセルに入力されてしまいます。

ドロップダウンリストから選択するときは「社員コード」+「社員名」
選択した後にセルに入力されるのは「社員コード」のみ
このような事は不可能でしょうか？
たびたび申し訳ありませんが、よろしくお願いいたします。

回答No.1

coco1

別に列を用意して、作業用データとして

|A------------|B-----|C
10001山田太郎 |10001 |山田太郎
10002鈴木花子 |10002 |鈴木花子
10003佐藤大介 |10003 |佐藤大介

といった感じのテーブルを作成し、A列をキーにしてB列を値表示、更にB列をキーにしてC列を表示

という具合にしてはどうでしょう？

お礼

ご回答ありがとうございます。

>A列をキーにしてB列を値表示、更にB列をキーにしてC列を表示

これは、入力規則に加え関数を使うということなのでしょうか？それとも他の方法で・・？
お手数ですが、出来ればもう少し具体的にお教えいただけると嬉しいのですが。
お願いいたしますm(__)m