複数の表を切り替えて抽出する方法について(Excel)

質問

エクセルで、複数の表を切り替えて抽出する方法を色々考えてみました。はじめは、Lookup関数を使いましたが、特定の文字に対となるものを抽出するにはIndex関数とMatch関数の組み合わせがいいことが分かりました。しかし、複数の表を切り替えるとなると、やっぱり、If関数の階層化となってしまいます。名前の定義を使って切り替えようかとも思いましたが、途方にくれてしまいました。ユーザー関数の方がいいのかも？？って感じで、考えがまとまりません。どなたか？詳しい方、お知恵を拝借できないでしょうか？宜しくお願いします。イメージとしては、
=選択抽出(表の名前,検索列名前,検索文字列,抽出列名前)ってな感じで、表の名前を変更することにより任意の表を選べ、検索列名前、を変更することにより表の中の任意の列を選べる、(抽出も同じ)てな感じで、任意の表の任意の列を検索し、任意の列から抽出するってな、万能なユーザー関数を模索しています。
ヒントだけでもいいので、拝借お願い致します。
(イメージは、エクセルの名前定義を使う方法が正しいものとしての仮定での話です)宜しくお願い致します。

回答No.4

noname#262398 · ベストアンサー

#2のNNAQです。
まだ締められていないようですので、ちょっと追記。

データベース関数があるのを忘れてました。
縦２つのセル(例えばA1,B1)を使って
A1に検索列見出し、B1に検索文字列、を入力すれば、
=DGET(表名,"抽出列見出し",A1:B1)
これが一番ご要望に近いと思います。
検索列に検索文字列が複数あるとエラーになりますが、
複数列を検索条件に指定できたりとか、けっこう万能ですよ。
まあ、何をもって万能と言うかは状況によりけりでしょうが。

ユーザー定義関数だと、こんな感じでしょうか。
Function DVLOOKUP(表 As Range, 検索列名, 検索値, 抽出列名)
Dim c As Integer, cc As Integer, r As Long
With 表
For c = 1 To .Columns.Count
If .Cells(1, c).Value = 検索列名 Then Exit For
Next c
If c > .Columns.Count Then GoTo NA
For r = 2 To .Rows.Count
If .Cells(r, c).Value = 検索値 Then Exit For
Next r
If r > .Rows.Count Then GoTo NA
For cc = 1 To .Columns.Count
If .Cells(1, cc).Value = 抽出列名 Then Exit For
Next cc
If cc > .Columns.Count Then GoTo NA
DVLOOKUP = .Cells(r, cc).Value
End With
Exit Function
NA: DVLOOKUP = CVErr(xlErrNA)
End Function

考え方は#2,#3と同じです。
セルに=dvlookup(表名又はセル範囲,"検索列名","検索文字列","抽出列名")
という感じで入力します。
ただ、これだったらDGET関数の方が良いです(^^;
ヒント程度には、なるかな？

お礼

重ね重ねご丁寧に貴重な知識をご伝授いただきありがとうございます。抽象的な質問内容にも関わらず、本当にありがとうございます。勉強して、活用させて頂きます。ありがとうございました。

回答No.5

noname#262398

#4の訂正
> 縦２つのセル(例えばA1,B1)を使って
> A1に検索列見出し、B1に検索文字列、を入力すれば、
> =DGET(表名,"抽出列見出し",A1:B1)
　　　↓
縦２つのセル(例えばA1,A2)を使って
A1に検索列見出し、A2に検索文字列、を入力すれば、
=DGET(表名,"抽出列見出し",A1:A2)

回答No.3

noname#204879

No.1です。

次のような複数の表を考えます。（本当は、質問者がこのような具体例を示すべきです！）
範囲 C2:F7、D10:G15、E18:H23 に「表の名前」としてそれぞれ tbl10、tbl20、tbl30 を付けておきます。

　　　C　　　 D　　　 E　　　 F　　　 G　　　 H
1　 tbl10
2　 fld11　 fld12　 fld13　 fld14
3　 dat111　dat121　dat131　dat141
4　 dat112　dat122　dat132　dat142
5　 dat113　dat123　dat133　dat143
6　 dat114　dat124　dat134　dat144
7　 dat115　dat125　dat135　dat145
8
9　　　　　 tbl20
10　　　　　fld21　 fld22　 fld23　 fld24
11　　　　　dat211　dat221　dat231　dat241
12　　　　　dat212　dat222　dat232　dat242
13　　　　　dat213　dat223　dat233　dat243
14　　　　　dat214　dat224　dat234　dat244
15　　　　　dat215　dat225　dat235　dat245
16
17　　　　　　　　　tbl30
18　　　　　　　　　fld31　 fld32　 fld33　 fld34
19　　　　　　　　　dat311　dat321　dat331　dat341
20　　　　　　　　　dat312　dat322　dat332　dat342
21　　　　　　　　　dat313　dat323　dat333　dat343
22　　　　　　　　　dat314　dat324　dat334　dat344
23　　　　　　　　　dat315　dat325　dat335　dat345

下に示すように、範囲 A1:A4 の各セルには、それぞれその右のセルに示すデータを入力します。
最終的な抽出値をセル A6 に示していますが、入力すべき式は次のとおりです。

A8: =CELL("address",INDIRECT(A1))
A9: =COLUMNS(INDIRECT(A1))
A10: =ROWS(INDIRECT(A1))
A11: =MATCH(A2,OFFSET(INDIRECT(A8),,,,A9),0)
A12: =MATCH(A4,OFFSET(INDIRECT(A8),,,,A9),0)
A13: =MATCH(A3,OFFSET(INDIRECT(A8),,A11,A10),0)
A6: =OFFSET(D10,A13-1,A12-1)

　　　A　　　　　 B
1　 tbl20　 表の名前
2　 fld23　 検索列名前
3　 dat243　検索文字列
4　 fld22　 抽出列名前
5
6　 dat223　抽出値
7
8　 $E$11　 表の基点
9　　　　4　表の列数
10　　　 6　表の行数
11　　　 3　検索列オフセット
12　　　 2　抽出列オフセット
13　　　 4　抽出行オフセット

回答No.2

noname#262398

列の名前は、表の最初の行の列見出しですよね？
ワークシート関数ですが
A1に表の名前、B1に検索列名前、C1に検索文字列、D1に抽出列名前
を入力して、
=INDEX(INDIRECT(A1),MATCH(C1,INDEX(INDIRECT(A1),,MATCH(B1,INDEX(INDIRECT(A1),1,),0)),0),MATCH(D1,INDEX(INDIRECT(A1),1,),0))

お礼

大変お礼のお返事が遅くなり真に申し訳ありませんでした。(長期出張中でした)
なるほど、INDIRECT関数を使うと任意の文字列を使用できるのですね？早速、使わせて頂きます。
関数って単体での解説を読んでいるとなかなか使い道が分からないもので、このように、具体的な使い方を示されると大変わかりやすいと思いました。(また、そのような考え方で今後見ていきたいと思います)
本当に有難う御座いました。

回答No.1

noname#204879

「複数の表を切り替え」るための条件は？

補足

早速の返答ありがとうございます。
「複数の表を切り替える条件」は、表の名前の部分の引数で指定できるのでは？と思いましたが？？だめでしょうか？よろしくお願いいたします。