Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

このQ&Aに関連する最新のQ&A

A 回答 (5件)

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す



 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。
    • good
    • 15
この回答へのお礼

ありがとうございました!できました!
VBAをいじり始めてみたのですが、今ひとつだったので大変有り難いです。
関数も使ったことのないものを教えていただき、勉強になりました。
こころから感謝申し上げます。

お礼日時:2008/01/15 13:37

#1です



やはり、ご質問の内容を、変更することをお勧めします。

ばらばらに並んでいる、現在の元データをVlookupで検索できるように並び替えることを考えたほうが早いかもしれません。

例での氏名とIDで抽出可能な条件はありませんか?
まずは行単位での抽出を考えましょう。
    • good
    • 1
この回答へのお礼

ありがとうございました。
また教えてください。

お礼日時:2008/01/15 13:38

エクセルでは関数を使ってでは、抜き出しは苦手(式が複雑)です。


この質問コーナーで、私はimogasi方式という作業列を使うやり方でたくさん答えています。Googleで「imogasi方式」で照会すれば、OKWAVEでの同類の質問が相当数でで、そこには私以外の回答も出てきます。参考にしてください。
>ユーザー定義で作りたいと
やってご覧よ。2つ以上の値を返すユーザー関数って、そうたやすく勉強できないよ。
(A)それよりある列の全セルをIF文で聞いて、該当かどうか調べて、該当なら、好みの列に1行ずつ下へずらして書き出す、を考えた方が格段に易しいよ。
上記(A)と絡めて、条件の第x番目の該当行を帰すユーザー関数を考えたら。
    • good
    • 1
この回答へのお礼

ありがとうござました。
また教えてください。

お礼日時:2008/01/15 13:47

INDEX関数とMATCH関数の組み合わせのことだと思います。



A   B
氏名 ID

であった場合
D1にID入れて、E1に
=INDEX(A:A,MATCH(D1,B:B),1)
それぞれの関数の意味はHelpで調べてみてください。
そのほか、色々と使い道のある関数です。
    • good
    • 3
この回答へのお礼

ありがとうございます。
私が扱っているデータの具体的な例を挙げさせて頂きます。
まさし 00038
ゆみこ 00025
ひろし 00041
たろう 00040
というような形ではなく、

まさし 00038
   ゆみこ 00025
      ひろし 00041
 欠席 たろう 00040
のように、バラバラに並んでいる形になっています。

関数の意味を見てみたのですが、どちらもA1:A200とかB1:B200という指定はできても、
A1:F200という指定はできないような気がするのです・・・。

氏名やIDがA列やB列にあるとは限らず、
ある人は氏名がC列にあったり、
またある人は氏名がD列にあったりします。
IDがそのすぐ隣にあることは保証されているのですが・・・。
どうぞよろしくお願いいたします。

お礼日時:2008/01/14 22:37

いちばん簡単なのはある範囲をVlookupで検索できるように変更することです。



ある範囲が数列ならそれぞれ名前定義してVlookupで探して、なければ次の名前、なければ次の名前とするのも手かもしれません。

ある範囲の規模や並び方がわかればもう少し有効な策もあるかもしれないですね。
    • good
    • 1
この回答へのお礼

どうもありがとうございます。
「ある範囲」とは列が6列、行が200行ぐらいです。
検索したい値がA列にあるのか、B列にあるのか、まったくのランダムで、
B列にその値がある時も、その隣のA列のセルが、空白なのか、何か値が入っているのか、それもランダムになります。
その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
どうぞよろしくお願いします。

お礼日時:2008/01/14 21:50

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QExcel関数(VLOOKUPの検索値が左端以外)

VLOOKUPを用いて、データの取得を考えております。
しかし、検索したい文字列(B列)と戻ってきてほしい値の列(A列)が逆になっており、VLOOKUPを用いることが出来ません。

B列とA列を逆にすればよいのですが、多数のファイルがあり、変更することができません。

B列の値を検索し、A列の値を返すことが出来ないでしょうか?

何卒よろしくお願いいたします。

Aベストアンサー

NO1です。
⇒対象エクセルをオープンしておかないと#REF!エラーになりますが、次の方法は如何でしょうか。
=IF(検索セル<>"",INDEX(INDIRECT("["&A1&"]"&B1&"!A:A"),MATCH(検索セル,INDIRECT("["&A1&"]"&B1&"!B:B"),0)),"")

QVLOOKUP関数、検索値が範囲に無い場合

A列に商品コードを書いたシートがあります。
B列に納入率を"Sheet2"からVLOOKUPで呼び出す場合、
B2には下記のような数式を入れましたが、うまく表示されません。
=IF($A2=Sheet2!$A$1:$A$6,VLOOKUP($A2,Sheet2!$A$1:$B$6,2,FALSE),65&"%")
求めたいのは、D,Eの場合はそれぞれ納入率○%、
それ以外の場合は、65%ですが、上記の数式では、全て65%で表示されてしまいます。
どの部分がおかしいのかご指摘をお願いします。

・納入率65%以外のものが少なく、それ以外の商品コードが大量にあるため、できれば全商品コードのマスタは作成したくない。
・今後、納入率65%以外が増えることを想定している(最大20個くらい)、のでIF関数では対応できない(ネストが7個までのため)。

"Sheet1"
   A       B
1 商品コード納入率
2   A      
3   B      
4   C      
5   D      
6   E    

"Sheet2"
  A   B
1  D  60%
2  E  70%
          ※DE以外は65%

A列に商品コードを書いたシートがあります。
B列に納入率を"Sheet2"からVLOOKUPで呼び出す場合、
B2には下記のような数式を入れましたが、うまく表示されません。
=IF($A2=Sheet2!$A$1:$A$6,VLOOKUP($A2,Sheet2!$A$1:$B$6,2,FALSE),65&"%")
求めたいのは、D,Eの場合はそれぞれ納入率○%、
それ以外の場合は、65%ですが、上記の数式では、全て65%で表示されてしまいます。
どの部分がおかしいのかご指摘をお願いします。

・納入率65%以外のものが少なく、それ以外の商品コードが大量にあるため、できれ...続きを読む

Aベストアンサー

=IF(ISERROR(VLOOKUP($A2,Sheet2!$A$1:$B$6,2,FALSE))=FALSE,VLOOKUP($A2,Sheet2!$A$1:$B$6,2,FALSE),65&"%")

にしてはどうでしょうか?
$A2=Sheet2!$A$1:$A$6 では、一致するものがあるかどうかの確認ができないので、VLOOKUP関数で一致するものを探してエラーが出なければ、VLOOKUP($A2,Sheet2!$A$1:$B$6,2,FALSE)を、そうでなければ65%を返させてます。
最後の65&"%"は、0.65の方がよいかもしれないですね。

http://pc.nikkeibp.co.jp/article/NPC/20070522/271846/

QVLOOKUP関数で検索値と一致項目を取得できません

VLOOKUP関数で検索値と一致項目を取得できません

下記のような表があるとき、
=VLOOKUP(K1,A2:B4,2,TRUE)と記述し『CCC』を取得したいのですが、
『BBB』を取得してしまいます。

 |A |B |C |D |E |F |G |H |I |J |K
1|0.6|0.6|0.6|0.6|0.6|0.6|0.6|0.6|0.6|0.6|=SUM(A1:J1)
2| 4|AAA
3| 5|BBB
4| 6|CCC

一致しない場合は、近似値を取得したいため、検索方法をTRUEにしています。
EXCEL2007を使用しています。

K1の値を数値で『6』と入力した場合や、『=0.6*10』など記入した場合は、『CCC』が取得できます。
また、A1~J1を0.5にした場合は『BBB』が取得できるので、関数自体は正しいと思うのですが、原因がわかりません。

現在のところ、=ROUND(SUM(A1:J1),2)としてごまかしているのですが、
原因がわかりましたら、教えていただけますでしょうか?

Aベストアンサー

だいぶシビアなところを突いていますが,浮動小数点誤差によって「内部的な値」が6未満になっています。「エクセル(コンピュータ)は計算を間違えない」というのはまっかな大嘘です,という実例の一つです。
小数点以下の数字を使って計算をしているところ,どこでもこれが紛れ込む可能性があります。


>現在のところ、=ROUND(SUM(A1:J1),2)としてごまかしているのですが、

ごまかしでもなんでもなく,極めて正しい対処です。
=VLOOKUP(ROUND(k1,2),…
=VLOOKUP(ROUND(SUM(A1:J1),2),…
などでも構いません。
また微少数(A1:J1に出てくる値よりも十分小さい値)を使って
=VLOOKUP(K1+0.0001,A2:B4,2,TRUE)
等としてもできます。

Qエクセル MATCH関数で検索範囲内に同じ値の検索値が複数ある場合

MATCH関数で検索範囲内に同じ値の検索値が複数ある場合の処理に悩んでいます。


(Sheet1)
ABCD
名称点数順位備考
あああ806アアア
いいい903イイイ
ううう855ウウウ
えええ903エエエ
おおお806オオオ
かかか709カカカ
ききき806キキキ
くくく6010ククク
けけけ1001ケケケ
こここ952コココ

C2=RANK(B2,B$2:B$11)


Sheet1のこの表からSheet2に


(Sheet2)
順位名称点数備考
1けけけ100ケケケ
2こここ95コココ
3いいい90イイイ
4えええ90エエエ
5ううう85ウウウ
6あああ80アアア
7おおお80オオオ
8ききき80キキキ
9かかか70カカカ
10くくく60ククク


という表を作ろうとして、


(Sheet2)
ABCDE
順位検索名称点数備考
19けけけ100ケケケ
210こここ95コココ
32いいい90イイイ
4#N/A#N/A#N/A#N/A
53ううう85ウウウ
61あああ80アアア
7#N/A#N/A#N/A#N/A
8#N/A#N/A#N/A#N/A
96かかか70カカカ
108くくく60ククク

B2=MATCH(A2,Sheet1!$C$2:$C$11,0)
C2=INDEX(Sheet1!$A$2:$A$11,B2)
E2=INDEX(Sheet1!$B$2:$B$11,B2)
E2=INDEX(Sheet1!$D$2:$D$11,B2)


としたのですが、90点が2人、80点が3人の部分が、#N/Aとなってしまいました。
Sheet1に4,7,8位がないのが原因と言うところまではわかったのですが、このような場合どのように処理すればよいのでしょうか。
Sheet2の同じ点数の順位の表示は、6位6位6位ではなくて、6位7位8位のままで編集したいと考えています。
また、同一順位内での表示順序はどうなってもいいと考えています。
エクセル2003です。

よろしくお願いします。

MATCH関数で検索範囲内に同じ値の検索値が複数ある場合の処理に悩んでいます。


(Sheet1)
ABCD
名称点数順位備考
あああ806アアア
いいい903イイイ
ううう855ウウウ
えええ903エエエ
おおお806オオオ
かかか709カカカ
ききき806キキキ
くくく6010ククク
けけけ1001ケケケ
こここ952コココ

C2=RANK(B2,B$2:B$11)


Sheet1のこの表からSheet2に


(Sheet2)
順位名称点数備考
1けけけ100ケケケ
2こここ95コココ
3いいい90イイイ
4えええ90エエエ
5ううう85ウウウ
6あああ80アアア
...続きを読む

Aベストアンサー

◆こんな方法もありますよ

◆Sheet1の順位の式
C2=RANK(B2,$B$2:$B$11)+COUNTIF($B$2:B2,B2)-1
★下にコピー

◆Sheet2の「名称」の式
B2=INDEX(Sheet1!A$2:A$11,MATCH($A2,Sheet1!$C$2:$C$11,0))
★右にコピーすると、「点数」の式になります

◆Sheet2の「備考」の式
D2=INDEX(Sheet1!D$2:D$11,MATCH($A2,Sheet1!$C$2:$C$11,0))
★下にコピー

Qvlookup関数で検索値を含む文字列を検索する方法

vlookup関数で例えば E1のセルに=vlookup(D1,A:C,3,false)とした場合、D1が佐藤であれば、A列に「佐藤」がある場合には当然、「佐藤」がある行の3列目の値が返されますが、「佐藤」はなく「佐藤A」や「A佐藤」がある場合にもこれらがある行の値を返して欲しいのですが、いい方法はありませんでしょうか。*を使ってできると思ったのですがうまくいきません。上記例で、=vlookup("*佐藤*",A:C,3,false)とすればできますが、*佐藤*の部分はD1の引用を利用したいのです。
 よろしくお願いします。

Aベストアンサー

=VLOOKUP("*"&D1&"*",A:C,3,FALSE)


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報