またまた質問させてもらいます・・・

VLOOKUPは本来検索範囲の左端の列を検索していくの
ですが、それを他の列をキーにしたいのです。ですが、
元表の順番を変えることができないので、検索できずに
困っています。そこで質問なんですが、

・元表を変えずに(コピー等を作らずに)、関数内だけで
表を擬似的に並べ替えて、左端の列以外の列を検索キーと
した列検索は可能でしょうか?可能なら方法はどうやれば
よろしいでしょうか。

・もしそれがだめな場合他のアプローチはありますが?
できれば方法も・・・

あと、もうひとつ質問なんですが、検索範囲を指定する
時に左上のセルの位置は固定で右下のセルが表の行数
によって変化する場合、汎用的に関数で指定することは可
能でしょうか?

つまり、B4:H14という範囲指定がある場合、表の行数がひ
とつ増えるとB4:H15と範囲指定を変えてくれるような方法
はありますか?ということです。

ということです。それではよろしくお願いします。

A 回答 (3件)

例えば、A1の値をG4:G15と照合して、一致したら対応するE列の値を持ってきます。


 =IF(ISERROR(MATCH(A1,G4:G15,0)),"",INDEX(E4:E15,MATCH(A1,G4:G15,0)))
ISERRORは見つからなかった場合の対応です。

範囲が変動する場合は、例えばD列は通番とかでデータの最後まで埋められている(空白が無い)とすれば、
 =INDEX(INDIRECT("E4:E"&(COUNTA(D:D)+3)),MATCH(A1,INDIRECT("G4:G"&(COUNTA(D:D))+3),0))
でできるはずです。
見つからなかった場合の対応は最初の式のようにして
 ISERROR(MATCH(A1,INDIRECT("G4:G"&(COUNTA(D:D)+3)),0))
と変形します。

ご参考に。
    • good
    • 0
この回答へのお礼

ありがとうございました、この方法が一番うまくできました。ありがとうございます。

お礼日時:2001/10/29 19:11

LOOKUP関数を使用されたらどうでしょうか。


=LOOKUP(検査値,検査範囲,対応範囲)という具合になります。
検査キーがB1:B15、C1の値で検索し、A列の値を取り出す場合は、
=LOOKUP(C1,B1:B15,A1:A15)になります。

>変化する場合、汎用的に関数で指定することは可能でしょうか?

何行まで値が入っているかを自分で指定する方法、例えば15行までデータが入っているのでD1セルに15と入力する方法で良ければ、
=LOOKUP(C1,B1:INDIRECT("B"&D1),A1:INDIRECT("A"&D1))
とかいう方法があると思います。

表の行数を関数で求めるのなら、D1セルに配列数式で、
{=MAX(IF(B1:B65535<>"",ROW(B1:B65535)))}
を入力しておく方法もあるかと思います。
    • good
    • 0

>・元表を変えずに


 元表をvloopupで使用しやすいように
 違うシートにコピーしてお使い下さい
>B4:H14という範囲指定がある場合、表の行数がひ
 INDEX関数をお使い下さい。

 使用方法はヘルプでINDEXと入力し検索して下さい

がんばって下さい

この回答への補足

「コピー」とかは無しでと書いたつもりなのですが・・・(つまり違うシートにコピーを作らずにという事です。)
それがあればやっていますし。自分でもわかります。それ以外の方法が無いかということです。

補足日時:2001/10/24 11:34
    • good
    • 0

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

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

Q範囲を指定した中で検索し、その列を指定するVBA

エクセルVBAのことで伺います。

3行目のD列からBC列の中で、出発地、又は到着地と入力されたセルを探し、
そのセルの含まれる列を指定し、当該マクロを作動させたいと思っているの
ですが、

If Intersect(Target, Range("D3:BC3"),Find("出発地")or("到着地").Column)

と書いても、構文エラーと表示されてしまいます。

どう書けば良いのか、どなたかご教授願います。
よろしくお願いいたします。

Aベストアンサー

何度もごめんなさい。

前回の変更したコードでも無駄なループをしていましたので、
もう一度考え直してみました。
今までのコードはすべて削除して↓のコードに変更してみてください。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim j As Long, r As Range, myRng As Range, Foundcell As Range
'とりあえず 出発日があるかどうか念のため確認
Set myRng = Rows(3).Find(what:="出発日", LookIn:=xlValues, lookat:=xlWhole)
'もし「出発日」があれば
If Not myRng Is Nothing Then
'1列目~3行目の最終列までループ
For j = 1 To Cells(3, Columns.Count).End(xlToLeft).Column
'ループさせているセルが「出発日」もしくは「到着日」であれば
If Cells(3, j) = "出発日" Or Cells(3, j) = "到着日" Then
'出発日、到着日のすべてのセルを「myRng」に格納
Set myRng = Union(myRng, Cells(3, j))
End If
Next j
End If
With Target '変化したセルの・・・
'セル番地(Targetの列の3行目)がmyRngにない場合は何もしない
If Intersect(Cells(3, .Column), myRng) Is Nothing Then Exit Sub
'★↓からは今までの処理
Set Foundcell = Range("D3:Q3").Find("年").Offset(0, -1)
If IsDate(.Value) Then
If Year(.Value) <> Foundcell Then
Application.EnableEvents = False
.Value = DateSerial(Foundcell, Month(.Value), Day(.Value))
Application.EnableEvents = True
End If
End If
End With
End Sub

※ 若干コードが短くなったと思います。m(_ _)m

何度もごめんなさい。

前回の変更したコードでも無駄なループをしていましたので、
もう一度考え直してみました。
今までのコードはすべて削除して↓のコードに変更してみてください。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim j As Long, r As Range, myRng As Range, Foundcell As Range
'とりあえず 出発日があるかどうか念のため確認
Set myRng = Rows(3).Find(what:="出発日", LookIn:=xlValues, lookat:=xlWhole)
'もし「出発日」があれば
If Not myRng Is Nothing Then
'1列目~3...続きを読む

Qエクセルの関数です。一列目で指定した値の間で、二列目で指定した値を示す、一列目の最初の値を求める。

エクセルの関数です。
一列目で指定した値の間で、二列目で指定した値を示す、一列目の最初の値を求める関数を教えてください。
添付した図で、具体的に説明します。
A列に値(時間)、B列に値があります。
この配列の中から、
F4の値(時間)と同じ値(時間)を示すA列の行から、F5の値(時間)と同じ値(時間)を示すA列の行までの中で、
F3の値と同じ値がB列にある、A列の値(時間)の内、
A列で上から最初の値(時間)
です。

min、offset、index、match を組み合わせてみるのですが、うまくいきません。
どうぞよろしくお願いします。

Aベストアンサー

こんにちは!

画像の配置でF6セルに「7」という結果が返れば良い訳ですかね?

少し長くなりますが、
=INDEX(INDIRECT("A"&MATCH(F4,A:A,0)&":A"&MATCH(F5,A:A,0)),MATCH(F3,INDIRECT("B"&MATCH(F4,A:A,0)&":B"&MATCH(F5,A:A,0)),0))
という数式を入れてみてください。

※ F3セルは質問に載っていないので余計なお世話かもしれませんが
同じようなやり方で
=MAX(INDIRECT("B"&MATCH(F1,A:A,0)&":B"&MATCH(F2,A:A,0)))
という数式になると思います。

※ エラー処理はしていません。m(_ _)m

Q【Excelマクロ】範囲の指定列選択 範囲文字入力

EXCELの範囲選択している部分の
一列部分のみを選択したいです。

画像を貼りました。マクロ
例ですが、
6行目から10行目まで選択していて、
その中のB列を選択したい場合のマクロです。

6行目~10行目→B6:B10

また、選択している範囲全て(B6:B10)に
「OK」と記入したい場合に、
どのようにするのかも教えて頂けるとありがたいです。

よろしくお願い致します。

Aベストアンサー

こんな感じでどうでしょう

> 6行目から10行目まで選択していて、
> その中のB列を選択したい場合のマクロです。

Range("B" & Selection(1).Row & ":B" & Selection(Selection.Count).Row).Select

> また、選択している範囲全て(B6:B10)に
> 「OK」と記入したい場合に、
> どのようにするのかも教えて頂けるとありがたいです。

Selection = "OK"

Q範囲を検索し指定の数だけあれば、それを書き出すには

いつも皆さんに大変お世話になっております。またひとつ教えていただきたいことがございます。
組、番号、氏名、数学の点数、英語の点数が表の見出しで、50名ほどのデータが入っている表があります。この表は200名ほどのデータの中で、2つの教科ともに100点を取った者だけが抽出されている表です。
数回実施したテストごとに両教科100点の者だけを抽出し、前のテストの同じ表に付け足しをしているのを「並べ替え」によって、次のようになっています。

組 番号 氏名    数学 英語
1  2  佐藤正一 100 100
1  2  佐藤正一 100 100
1  2  佐藤正一 100 100
1 35  山口篤  100 100
2  9  加藤稔  100 100
2  9  加藤稔  100 100 
6 11  原田聡  100 100
6 11  原田聡  100 100
6 11  原田聡  100 100

やりたいことは、この表から3回名前がある者だけを上から順に拾っていき、別の場所に書き出し、3回とも100点だった者のリストを作りたいということなのです。
出来上がりはこういう感じです。↓
組 番号 氏名    数学 英語
1  2  佐藤正一 100 100
6 11  原田聡  100 100

今のところ、countifをマクロで使う方法などを調べてやってみたのですが自分の理解が不足でうまくいきませんでした。

一番シンプルなマクロの記述で実現できる方法を教えていただきたく存じます。
よろしくお願いします。

いつも皆さんに大変お世話になっております。またひとつ教えていただきたいことがございます。
組、番号、氏名、数学の点数、英語の点数が表の見出しで、50名ほどのデータが入っている表があります。この表は200名ほどのデータの中で、2つの教科ともに100点を取った者だけが抽出されている表です。
数回実施したテストごとに両教科100点の者だけを抽出し、前のテストの同じ表に付け足しをしているのを「並べ替え」によって、次のようになっています。

組 番号 氏名    数学 英語
1  2  佐藤...続きを読む

Aベストアンサー

Sheet1のC列に名前があるとします。
3回以上出現した場合、Shee2にコピーします。
(Sheet2はあらかじめ用意しておいてください)

Sub macro()
Dim C As Range, I As Integer
For Each C In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
If C.Value = C.Offset(1).Value Then
I = I + 1
If I = 2 Then
C.EntireRow.Copy Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Else
I = 0
End If
Next C
End Sub

QExcelで、自列に数字がる行の左端列の数字を加えるには?

下表で、左端は行番号、最上行は列名で、-は空白セルを表しています。
例えば、(1)=40行のGには、G列に数字がある行のF列の数字を足す式を求めています。

+FGHI
719--
81---
951--
102-5
110--5
121---

40(1)(2)  (3)

(1)=G40=1+5
(2)=H40=2
(3)=I40=0
なお、スペースが詰まった表示になりますが、Excel表をここで表示する方法があればそれも教えて下さい。以上

Aベストアンサー

40行目のG列のセル、H列のセル、I列のセルに
それぞれの列の1~39行の間で"-"以外の値が入っている行のF列のセルの値の合計を表示する方法を知りたい
という質問でよろしいでしょうか?
合計を求める際に条件をつけることのできるSUMIF関数というものがあります。
SUMIF関数には3つの引数があり、1つ目の引数には条件をつける範囲、2つ目の引数にはその条件を、3つ目の引数には合計を求める範囲を渡して使用します。
例えば今回の場合、G40には=SUMIF(G1:G39,"<>-",F1:F39)が入ります。


人気Q&Aランキング

おすすめ情報