異なるDBから、それぞれ複数の抽出データを並べたい

質問

大変恐れ入ります。過去の他の方の質問を一部引用しております。
<引用：2009/11/05　QNo.5425095>
---引用---
excel関数を用いてある行の範囲内の数値のみを違う行に抽出し、同じ列に対応する値も引っ張ってくることは可能ですか？
　　　　A 　 B 　 C 　 D
1 　　33 　 180
2 　　58 　 300
3 　　89 　 310
4 　　152 　 240
5 　　205 　 74
--------------------------------------------------

この質問に関しての回答は理解できましたが、1つ質問と、さらに応用編としてご口授いただきたくお願いいたします。

Q1.　上記にあるA1の”33”を、A1以外のセルにセットするとC,Dに正規のデータが抽出されない。
　　　この際、
　　　・ =IF(AND(A1>=60,A1<=180),ROW(A1),"")
　　　・ =IF(OR(ROW(A1)>COUNT($C:$C),COLUMN(A1)>2),"",INDEX($A:$B,SMALL($C:$C,ROW
　　　　(A1)),COLUMN(A1)))

　　　の抽出範囲も設定をしてみましたが、全く結果が得られません。必ずA1にデータがないとだ
　　　めなのでしょうか。

　　　もし可能であれば、その応用として下記の内容を検討しており、非常に苦戦しております。
　　　是非教えていただければと思いますので、よろしくお願いします。

　　　》上記サンプルの様なシートが２つ存在し、それを一枚のシートにまとめ、2つのデータベース
　　　　からそれぞれに対し異なる抽出条件を提示し、その結果一覧をそれぞれ表示したいと検討
　　　　しております。

　　　大変申し訳ないのですが、少々急いでおります。是非よろしくお願いいたします。

回答No.4

kagakusuki

ANo.1です。失礼しました、

>2つのデータベースからそれぞれに対し異なる抽出条件を提示し、

という条件を見落として居りました。

　今仮に、データベースの表がSheet1とSheet2に分かれて存在していて、
Sheet4のA列にデータベースが存在しているシートのシート名を漏らさず入力し、
Sheet4のB列には、A列で指定したデータベースに対する抽出条件の下限値を入力し、
Sheet4のC列には、B列で指定したデータベースに対する抽出条件の下限が、「その値以上」なのか「その値を超えて」なのかの区別を入力し、
Sheet4のD列には、A列で指定したデータベースに対する抽出条件の上限値を入力し、
Sheet4のE列には、D列で指定したデータベースに対する抽出条件の上限が、「その値以下」なのか「その値未満」なのかの区別を入力すると、
Sheet3に抽出結果が纏めて表示されるものとします。
　又、Sheet4のA～F列を作業列として使用するものとします。

　まず、Sheet4のC2セルに入力規則で、「入力値の種類」を[リスト]とした上で、「元の値」欄に

に等しい,以上,を超えて

というリストを設定して下さい。
　そして、Sheet4のC2セルをコピーして、Sheet4のC3以下に貼り付けて下さい。
　次に、Sheet4のE2セルに入力規則で、「入力値の種類」を[リスト]とした上で、「元の値」欄に

以下,未満

というリストを設定して下さい。
　そして、Sheet4のE2セルをコピーして、Sheet4のE3以下に貼り付けて下さい。
　次に、Sheet4のF2セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER(INDEX($B:$B,ROW())),ISNUMBER(MATCH(INDEX($C:$C,ROW()),{"に等しい","を超えて","以上"},0))),LOOKUP(INDEX($C:$C,ROW()),{"に等しい","を超えて","以下","以上","未満"},{"=",">","<=",">=","<"})&INDEX($B:$B,ROW()),"")

　次に、Sheet4のG2セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER(INDEX($D:$D,ROW())),ISNUMBER(MATCH(INDEX($E:$E,ROW()),{"以下","未満"},0))),LOOKUP(INDEX($E:$E,ROW()),{"に等しい","を超えて","以下","以上","未満"},{"=",">","<=",">=","<"})&INDEX($D:$D,ROW()),"")

　次に、Sheet4のH2セルに次の数式を入力して下さい。

=IF(ISNUMBER(MATCH(9E+307,INDIRECT("'"&INDEX($A:$A,ROW())&"'!A:A"))),MATCH(9E+307,INDIRECT("'"&INDEX($A:$A,ROW())&"'!A:A")),"")

　次に、Sheet4のI1セルに数値の0を入力して下さい。
　次に、Sheet4のI2セルに次の数式を入力して下さい。

=IF(ISNUMBER($H2),SUM($H$1:$H2),"")
　次に、Sheet4のB2～I2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
　次に、Sheet4のL1セルに次の数式を入力して下さい。

=IF(ROW()>SUM($H:$H),"",INDIRECT("'"&INDEX($A:$A,MATCH(ROW()-1,$I:$I)+1)&"'!R"&ROW()-VLOOKUP(ROW()-1,$I:$I,1)&"C"&COLUMN(Sheet1!$A$1)+COLUMNS($L:L)-1,FALSE))

　次に、Sheet4のJ1セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER($L1),IF(INDEX($F:$F,MATCH(ROW()-1,$I:$I)+1)="",TRUE,COUNTIF($L1,INDEX($F:$F,MATCH(ROW()-1,$I:$I)+1))),IF(INDEX($G:$G,MATCH(ROW()-1,$I:$I)+1)="",TRUE,COUNTIF($L1,INDEX($G:$G,MATCH(ROW()-1,$I:$I)+1)))),$L1,"")

　次に、Sheet4のK1セルに次の数式を入力して下さい。

=IF(ISNUMBER($J1),COUNTIF($J:$J,"<"&$J1)+COUNTIF($J$1:$J1,$J1),"")

　次に、Sheet4のL1セルをコピーして、Sheet4のM1セルに貼り付けて下さい。
　次に、Sheet4のJ1～M1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、Sheet3のA2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet4!$K:$K),"",VLOOKUP(ROWS($2:2),Sheet4!$K:$M,COLUMNS($A:A)+1,FALSE))

　次に、Sheet3のA2セルをコピーして、Sheet3のB2セルに貼り付けて下さい。
　次に、Sheet3のA2～B2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　後は、
Sheet4のA2以下にデータベースが存在しているシートのシート名を漏らさず入力し、
Sheet4のB2以下に、A列で指定したデータベースに対する抽出条件の下限値を入力し、
Sheet4のC2以下に、B列で指定したデータベースに対する抽出条件の下限が、「その値以上」なのか「その値を超えて」なのかの区別を入力し、
Sheet4のD2以下に、A列で指定したデータベースに対する抽出条件の上限値を入力し、
Sheet4のE2以下に、D列で指定したデータベースに対する抽出条件の上限が、「その値以下」なのか「その値未満」なのかの区別を入力しますと、
各データベース用のシートのA列の値が、各データベースごとに設定した条件を満たしているデータのみが、Sheet3に抽出されて、A列の値の昇順に並べ替えられて表示されます。
　尚、A列の値が同値となっているデータが複数存在している場合には、Sheet4のA列に記入したシート名の順番が優先され、同じシート内でA列の値が同値となっているデータが複数存在している場合には、行番号の若いものから順番に表示されます。

　それから、このサイトの使い方に関してですが、ログインをしている状態で、各回答欄の下の辺りに「お礼する」ボタンや「補足する」ボタンをクリックしますと、その回答に対するお礼や、補足内容等を書き込む事が出来る様になりますので(書き直しは不可)、「回答者が勘違いをしている点」や「説明が足りていなかったけれど、自分はこのようにしたいのだ」といった追加情報は、別の質問を立てるのではなく、「補足する」ボタンをクリックすると現れる、補足欄に書き込まれるようになさって下さい。

回答No.3

KURUMITO

例えばE列に作業列を作って対応します。
E1セルには次の式を入力して下方にドラッグコピーします。

=IF(AND(A1>=60,A1<=180),ROW(A1),"")

そこでお求めの表ですがC1セルには次の式を入力してD1セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>COUNT($E:$E),"",INDEX($A:$B,SMALL($E:$E,ROW(A1)),COLUMN(A1)))

仮に1行目には文字列などが有って2行目から下方にデータが有るのでしたらE2セルには次の式を入力して下方にドラッグコピーします。

=IF(AND(A2>=60,A2<=180),ROW(A2),"")

C2セルには次の式を入力してD2エルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>COUNT($E:$E),"",INDEX($A:$B,SMALL($E:$E,ROW(A1)),COLUMN(A1)))

回答No.2

tom04

こんにちは！
一例です。
データ量が極端に多い場合はオススメできませんが・・・

（1）二つのデータはSheet1にまとめるとします。
（Sheet1の最終データ以降に別データをコピー＆ペースト）

↓の画像で左側がSheet1で右側がSheet2になります。
Sheet2のB1セルに
=IF(OR($A1="",COUNTIF(Sheet1!$A:$A,$A1)<COLUMN(A1)),"",INDEX(Sheet1!$B$1:$B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A1,ROW($A$1:$A$1000)),COLUMN(A1))))

これは配列数式になってしまいますので、Shift+Ctrl+Enterで確定！
この画面からコピー＆ペーストする場合はB1セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

これを列方向・行方向にオートフィルでコピーすると
画像のような感じになります。

※　数式はSheet1の1000行目まで対応できるようにしていますが、
データ量がもっと多い場合は別途作業用の列を設けたほうが良いと思います。

参考になりますかね？m(_ _)m

回答No.1

kagakusuki

今仮に、データベースの表がSheet1とSheet2に分かれて存在していて、その2枚のシートのA列の値が60以上且つ180以下となっているデータのみを、Sheet3に抽出して纏めて表示するものとします。
　又、Sheet4のA～F列を作業列として使用するものとします。

　まず、Sheet4のA2以下に、「Sheet1」や「Sheet2」といったデータベースが存在しているシートのシート名を漏らさず入力して下さい。
　次に、Sheet4のB2セルに次の数式を入力して下さい。

=IF(ISNUMBER(MATCH(9E+307,INDIRECT("'"&INDEX($A:$A,ROW())&"'!A:A"))),MATCH(9E+307,INDIRECT("'"&INDEX($A:$A,ROW())&"'!A:A")),"")

　次に、Sheet4のC1セルに数値の0を入力して下さい。
　次に、Sheet4のC2セルに次の数式を入力して下さい。

=IF(ISNUMBER($B2),SUM($B$1:$B2),"")

　次に、Sheet4のB2～C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
　次に、Sheet4のE1セルに次の数式を入力して下さい。

=IF(ROW()>SUM($B:$B),"",INDIRECT("'"&INDEX($A:$A,MATCH(ROW()-1,$C:$C)+1)&"'!R"&ROW()-VLOOKUP(ROW()-1,$C:$C,1)&"C"&COLUMN(Sheet1!$A$1)+COLUMNS($E:E)-1,FALSE))

　次に、Sheet4のD1セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER($E1),$E1>=60,$E1<=180),COUNTIF($E:$E,"<"&$E1)+COUNTIF($E$1:$E1,$E1)-COUNTIF($E:$E,"<60"),"")

　次に、Sheet4のE1セルをコピーして、Sheet4のF1セルに貼り付けて下さい。
　次に、Sheet4のD1～F1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、Sheet3のA2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet4!$D:$D),"",VLOOKUP(ROWS($2:2),Sheet4!$D:$F,COLUMNS($A:A)+1,FALSE))

　次に、Sheet3のA2セルをコピーして、Sheet3のB2セルに貼り付けて下さい。
　次に、Sheet3のA2～B2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　これで、2枚のシートのA列の値が60以上且つ180以下となっているデータのみが、Sheet3に抽出されて、A列の値の昇順に並べ替えられて表示されます。
　尚、A列の値が同値となっているデータが複数存在している場合には、Sheet4のA列に記入したシート名の順番が優先され、同じシート内でA列の値が同値となっているデータが複数存在している場合には、行番号の若いものから順番に表示されます。