エクセルで非常に困っています。

質問

昨日、エクセルで複数の条件範囲に合う行を別シートに抽出したいと思い
書き込んだ者です。
http://okwave.jp/qa/q7859821.html
参照して頂ければお分かりになると思います。

教えて頂いた通りに
Sheet2のP2セルに（←関らずSheet1の最初のデータがある行に数式をいれます）
=IF(AND(Sheet1!A2>=$B$2,Sheet1!A2<=$D$2,Sheet1!O2>=$B$3,Sheet1!O2<=$D$3),ROW(),"")
という数式を入れオートフィル
Sheet2のA6セルに
=IF(COUNT(Sheet1!$P:$P)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$P:$P,ROW(A1))))
と入れたのですが、どうしても検索結果が
宿泊日、人数ともに検索条件と違う行が抽出されてしまいます。

作業列は、間違えてないと考えているのですが、
どうしても検索結果が上手く出ません。

どなたか、お分かりになる方助けて下さい。
宜しくお願いします。

回答No.2

tom04 · ベストアンサー

こんにちは！

具体的にどのように行の食い違いがあるのか判らないのですが・・・
おそらく、後の数式は問題ないと思います。

考えられる原因としては、作業列の数式の始まりの行がどこから始まっているのか？
というコトです。

元データがあるSheetの最初のデータ行に作業用の数式を入れそれをオートフィルで下へコピーします。

１行でもずれると全く違ったデータが表示されてしまいます。

※　他の原因ならごめんなさいね。m(_ _)m

お礼

その通り作業列の数式の始まりでした！
本当にありがとうございます!!!

回答No.6

noname#204879

[No.4お礼]へのコメント、
》 気になるのですが
》 士スト範囲の$N$370
》 とは、何を指しているのでしょうか？

そこが「気になる」とは思ってもいませんでした。
横は、Ｂ列から「人数」のＯ列の左隣のＮ列まで、
縦は、１年の日数365を切り上げて370日分の範囲を用意したまで
ということをお察しいただけませんか？
それとも「$」記号が気になるのかしら？

お礼

なるほどそうでしたか。
スッキリしました。
私は一応、MOSエクセル2007を
所持しているので
ある程度でしたら
分かるんですよ。

ただ、あまりにも難しい式を作るとなると
勉強不足でして出来ないんです；

回答No.5

kagakusuki

>Sheet2のP2セルに（←関らずSheet1の最初のデータがある行に数式をいれます）

とありますが、結果を表示するシートに作業列を設けますと、必要としている結果以外の情報も表示されてしまい、若干見苦しくなりますから、作業列は使用していない適当なシートに設けた方が、見栄えという観点で言いますと良いかも知れません。
　そして、

>宿泊日、人数ともに検索条件と違う行が抽出されてしまいます。

という件ですが、

http://okwave.jp/qa/q7859821.html

において、御質問文中の説明では、
B2セルに「宿泊日の指定範囲の最初の日」を入力し、D2セルに「宿泊日の指定範囲の最終日」を入力し、
B3セルに「人数の指定範囲の最少人数」を入力し、D3セルに「人数の指定範囲の最多人数」を入力
する事になっているにも関わらず、質問者様の添付画像においては、
C4セルに「宿泊日の指定範囲の最初の日」を入力し、F4セルに「宿泊日の指定範囲の最終日」を入力し、
C8セルに「人数の指定範囲の最少人数」を入力し、F8セルに「人数の指定範囲の最多人数」を入力
されていて、
御質問文中の説明内容と、実際の条件を指定するセルが異なっているにも関わらず、質問者様が御質問文中の説明で提示された条件に合わせて組まれた関数を、実際の条件を指定するセルのセル番号に併せて修正される事無く、回答された関数をそのまま入力されておられる事が、原因の一つではないかと思われます。
　そして、2つ目として、

>Sheet2のP2セルに（←関らずSheet1の最初のデータがある行に数式をいれます）

となっているにも関わらず、関数内のセルの参照先を「Sheet1の最初のデータがある行」に合わせて修正される事なく、Sheet1!A2やSheet1!O2のままとされておられる事が原因だと思われます。
　3点目として、日付の入力を2011/01/01の様な日付で行うのではなく、20110101の様な日付ではない、単なる整数値を入力されている事も影響しています。

　条件範囲を指定するセルがどのシートにおけるどのセルなのかは、質問者様の御説明が不正確なままである以上、回答者にはどうする事も出来ませんので、質問者様に正しい情報を御提示頂くか、或いは質問者様ご自身で、関数を修正して頂くしか方法は御座いませんが、日付の入力を単なる整数値で行った上で、「Sheet1の最初のデータがある行に合わせて修正」する必要があまりない方法でしたら以下の様になります。

　今仮に、Sheet2のC4セルに「宿泊日の指定範囲の最初の日」を入力し、Sheet2のF4セルに「宿泊日の指定範囲の最終日」を入力し、Sheet2のC8セルに「人数の指定範囲の最少人数」を入力し、Sheet2のF8セルに「人数の指定範囲の最多人数」を入力するものとします。（この点に関しては、条件を入力する実際のセル番号に合わせて、関数を修正する必要があります）
　又、Sheet3のA列を作業列として使用するものとします。

　まず、Sheet3のA1セル（←Sheet1において最初のデータがある行が何行目であるのかには関らず、A1セルに関数を入力して下さい）に次の2つの関数の内、どちらかお好きな方を入力して下さい。

=IF(ISNUMBER(1/TEXT(Sheet1!$A1,"0000-00-00")/Sheet1!$O1),IF(AND(TEXT(Sheet1!$A1,"0000-00-00")-IF(ISNUMBER(1/TEXT(Sheet2!$C$2,"0000-00-00")),TEXT(Sheet2!$C$2,"0000-00-00"),0)>=0,IF(ISNUMBER(1/TEXT(Sheet2!$E$2,"0000-00-00")),TEXT(Sheet2!$E$2,"0000-00-00"),9E+99)-TEXT(Sheet1!$A1,"0000-00-00")>=0,Sheet1!$O1>=IF(ISNUMBER(Sheet2!$C$3),Sheet2!$C$3,0),Sheet1!$O1<=IF(ISNUMBER(Sheet2!$E$3),Sheet2!$E$3,MAX(Sheet1!$O:$O))),ROW(Sheet1!$A1),""),"")

或いは

=IF(ISNUMBER(1/TEXT(INDEX(Sheet1!$A:$A,ROW()),"0000-00-00")/INDEX(Sheet1!$O:$O,ROW())),IF(AND(TEXT(INDEX(Sheet1!$A:$A,ROW()),"0000-00-00")-IF(ISNUMBER(1/TEXT(Sheet2!$C$2,"0000-00-00")),TEXT(Sheet2!$C$2,"0000-00-00"),0)>=0,IF(ISNUMBER(1/TEXT(Sheet2!$E$2,"0000-00-00")),TEXT(Sheet2!$E$2,"0000-00-00"),9E+99)-TEXT(INDEX(Sheet1!$A:$A,ROW()),"0000-00-00")>=0,INDEX(Sheet1!$O:$O,ROW())>=IF(ISNUMBER(Sheet2!$C$3),Sheet2!$C$3,0),INDEX(Sheet1!$O:$O,ROW())<=IF(ISNUMBER(Sheet2!$E$3),Sheet2!$E$3,MAX(Sheet1!$O:$O))),ROW(),""),"")

　次に、Sheet3のA1セルを下方向にオートフィルして下さい。

　次に、Sheet2のA13セル（Sheet2のA列において、「最初の日付を表示する行」の1つ上の行）に、

日付

と入力して下さい。
　次に、Sheet2のA14セル（Sheet2のA列において、最初の日付を表示する行）に次の関数を入力して下さい。

=IF(ROW()-MATCH("*?",$A$1:$A13,-1)>COUNT(Sheet3!$A:$A),"",IF(INDEX(Sheet1!A:A,SMALL(Sheet3!$A:$A,ROW()-MATCH("*?",INDEX($A:$A,1):$A13,-1)))="","",INDEX(Sheet1!A:A,SMALL(Sheet3!$A:$A,ROW()-MATCH("*?",INDEX($A:$A,1):$A13,-1)))))

　次に、Sheet2のA14セルを右方向にオートフィルして下さい。
　次に、Sheet2の14行目全体を下方向にオートフィルして下さい。

　以上で準備は完了で、後はSheet2のC4セルに「宿泊日の指定範囲の最初の日」を入力し、Sheet2のF4セルに「宿泊日の指定範囲の最終日」を入力し、Sheet2のC8セルに「人数の指定範囲の最少人数」を入力し、Sheet2のF8セルに「人数の指定範囲の最多人数」を入力しますと、抽出結果がSheet2の14行目以下に表示されます。
　尚、この回答の関数の場合、
「宿泊日の指定範囲の最初の日」を指定せずに空欄のままとしますと、「Sheet1に入力されている中で最も古い日付」～「宿泊日の指定範囲の最終日」が抽出される日付範囲となり、
「宿泊日の指定範囲の最終日」を指定せずに空欄のままとしますと、「宿泊日の指定範囲の最初の日」～「Sheet1に入力されている中で最も新しい日付」が抽出される日付範囲となり、
「人数の指定範囲の最少人数」を指定せずに空欄のままとしますと、「0人」～「人数の指定範囲の最多人数」が抽出される人数の範囲となり、
「人数の指定範囲の最多人数」を指定せずに空欄のままとしますと、「人数の指定範囲の最少人数」～「Sheet1に入力されている人数の中で最も多い人数」が抽出される人数の範囲となります。

補足

ご指摘の通り、添付の画像とは異なって質問してしまい、
混乱させてしまいまして申し訳ありません。
セルの番号は変えていましたが、
関数内のセルの参照先を「Sheet1の最初のデータがある行」に合わせて修正される事なく、
Sheet1!A2やSheet1!O2のままでしておりました。
確認してもう一度、チャレンジしてみたいと思います
。
今回、御教示頂いた関数なのですが、Sheet1!O2にコピーしオートフィルしてみましたが
私が悪いのか、何にも数字が出ません。
ちなみにSheet1!O3は数字が出ました。
お時間があれば是非、何故このような関数関数式になるか
細かくご指導して頂ければ幸いです。

今回は、事細かく指摘して頂き
ありがとうございました

回答No.4

noname#204879

》 …参照して頂ければお分かりになると思います
いえ、仰るほど分かり易いとは言えません。
でも、貴方が「関数」に固執する割には苦労されている様子が目に見えるようです。

長ったらしい関数を使わないで済む[フィルタオプションの設定]による方法を別解として載せておきます。気が向いたら目を通しておくのも今後の勉強になろうかと。

Sheet2!E1: 必ず空白のままにしておく
Sheet2!E2: =AND(Sheet1!A2>=B$2,Sheet1!A2<=D$2,Sheet1!O2>=B$3,Sheet1!O2<=D$3)

[フィルタオプションの設定]は必ず Sheet2 から実行します。
[抽出先]　　　“指定した範囲”
[リスト範囲]　　Sheet1!$A$1:$N$370
[検索条件範囲]　$E$1:$E$2
[抽出範囲]　　　$A$5:$O$5

お礼

違う観点からの方法を
教えて下さいまして
ありがとうございます。
気になるのですが
士スト範囲の$N$370
とは、何を指しているのでしょうか？
もし、宜しければ教えて頂きたいです。

回答No.3

K Kazz（@JazzCorp）

サンプルはイロイロあります、時間がありましたらお試しください、、、

■06.03.22_Excel：
抽出データを詰めて表示する
http://www.geocities.jp/chiquilin_site/data/060322_extraction.html

最初のＱのＡ#3の画をミマするに、（ケッコウ簡単な式のようですが、）中間の計算結果はどうみてもあってないですネェ、、、
EXCELの再計算処理（のタイミング）、何んか変！？、、、

お礼

勉強になります。
サイトを教えて頂き、
ありがとうございます。

回答No.1

KURUMITO

以前のご質問に回答したものです。
作業列を作って対応するのがデータが多くなってもパソコンに負担を掛けない方法としてお勧めです。
シート１ではお示しのようにA２セルから下方に数値が、また、O２セルから下方に人数が有るとします。
シート２ではB２セルに宿泊日の初めがC２セルは～、D２セルには宿泊日の終わりが入力するとします。また、B３セルには検索人数の初めの人数が、C３セルは～、D３セルにはいわりの人数を入力することにします。
そこで以前はシート１に作業列を作ることを提案しましたがシート１に作るのはやめたいとのことですのでシート２のP列に作ることにします。P２セルには次の式を入力して下方にドラッグコピーします。

=IF(Sheet1!A2="","",IF(AND(Sheet1!A2>=B$2,Sheet1!A2<=D$2,Sheet1!O2>=B$3,Sheet1!O2<=D$3),MAX(P$1:P1)+1,""))

この作業列のデータを使ってシート２の６行目から下方に条件検索での該当するデータを表示することにします。
シート２のA６セルには次の式を入力してO６セルまで右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX($P:$P),"",IF(INDEX(Sheet1!$A:$O,MATCH(ROW(A1),$P:$P,0),COLUMN(A1))="","",INDEX(Sheet1!$A:$O,MATCH(ROW(A1),$P:$P,0),COLUMN(A1))))

補足

試してみましたが、駄目でした。
他の関数はないでしょうか？
スミマセンが宜しくお願い致します。