[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『VLOOKUPとIFの使い方』(ありさ)
VLOOKUPで式を作り方を教えてください。
sheet2で範囲指定してsheet1に結果を表示させたいです。
sheet2の状態は
A列は10桁のコード。全ての行が左から3桁は同じ数字です。
B列には氏名が入っています。
C列は半角カタカナのフリガナ
(カタカナの前に0か1から始まる3桁の数字が入っている場合もあります)
sheet1のA列にsheet2のA列の10桁のコードの内、
4桁〜10桁目の数字を入力すると、sheet1のB列に該当する氏名が表示されるようにしたいです。
また、sheet1のC列は該当するフリガナが表示されるようにしたいのですが、
フリガナのみの場合は空白、フリガナの前に0か1から始まる3桁の数字がある場合、
その3桁の数字のみを表示させたいです。
例えを出しますと
sheet2のA1のセルに1231111111
sheet2のB1のセルに山田太郎
sheet2のC1のセルに022アイウエオ
と入っているとします。
sheet1のA列に1111111と入力すると
B列には山田太郎、C列には022と表示させたいです。
氏名の方については、A列に1231111111と入力するとB列に表示されますが
始めの123は入力しないようにしたいです。
検索値に”*”&A1や123&A1と入力しましたができません。
sheet2のコードの左3桁を消すこともできません。
フリガナの前の数字についてはifやleftなどを使っていますが、できません。
何か方法はありませんでしょうか。
< 使用 Excel:unknown、使用 OS:Windows7 >
とりあえず、B1 は =IFERROR(INDEX(Sheet2!B:B,MATCH(A1,VALUE(MID(Sheet2!$A$1:$A$100,4,7)),0)),"")
これを、Ctrl/Shift/Enter でいれると、取得できそうですね。 (もっとスッキリした式があるんだと思いますが)
(β) 2016/03/31(木) 22:15
>検索値に ・・・123&A1と入力しましたができません。
123を加えたことにより、文字データ化したのに、 Sheet2の方が数値データなので、マッチしないという現象だと思います。
数値に戻してやる ↓ (1) B1セル =VLOOKUP(VALUE(123&$A1),Sheet2!$A:$B,2,FALSE)
(2) C1セル =MID(LOOKUP(2000,1*(LEFT(1&VLOOKUP(VALUE(123&$A1),Sheet2!$A:$C,3,FALSE),{1,2,3,4}))),2,3)
<Sheet1 結果図> 行 ___A___ ____B____ _C_ 1 1111111 山田太郎 022 2 4567890 岡崎真司 124 3 9876540 ハリル
<Sheet2> 行 _____A_____ ____B____ ____C____ 1 1231111111 山田太郎 022アイウエオ 2 1234567890 岡崎真司 124カキク 3 1239876540 ハリル ハリル
※該当がない時の処理は考慮しておりません。 m(__)m
(半平太) 2016/03/31(木) 22:22
すでに半平太さんから回答があったので用済みですけど、一応、努力(?)の結果を。
C1 は B1の方式に、ネットで検索した、文字列から数字を抜き出す式をあてはめて以下にしたら一応でるようです。
=IFERROR(TEXT(LOOKUP(10^17,LEFT(INDEX(Sheet2!C:C,MATCH(A1,VALUE(MID(Sheet2!$A$1:$A$100,4,7)),0)),COLUMN(1:1))*1),"000"),"")
これも Ctrl/Shift/Enter で入力。
(β) 2016/03/31(木) 22:26
あっ! Sheet2 の A列の値の先頭は 123 で固定でしたか。 なら、式の中に、VLOOKUP を組みこむことができたようですね。
(β) 2016/03/31(木) 22:29
Excelのバージョンは何ですか?
2007以降だとして。
Sheet1のC列が、文字列ではなく数値でもかまわないのなら
B1 =IFERROR(VLOOKUP((123&A1)*1,Sheet2!A:B,2,FALSE),"")
C1 =IFERROR(LEFT(VLOOKUP((123&A1)*1,Sheet2!A:C,3,FALSE),3)*1,"")
C1の表示形式〜ユーザー定義を 000 にする、
とか。 (笑) 2016/03/31(木) 23:15
Sheet1のC列、数値ではなく文字列の方がいいのなら
バージョンは2007以降だとして。
B1は前回と同じ
C1 =IFERROR(TEXT(LEFT(VLOOKUP((123&A1)*1,Sheet2!A:C,3,FALSE),3),"000;;;"),"") または C1 =IF(B1="","",TEXT(LEFT(VLOOKUP((123&A1)*1,Sheet2!A:C,3,FALSE),3),"000;;;"))
表示形式「標準」
とか。 (笑) 2016/04/01(金) 22:01
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.