データを検索し関連セルのデータを表示

質問

エクセル200２を使っています。

画像の様なデータシートがあります。

紫の部分　Q１４に　機器A　と入力された場合に　AR列の機器Aを下から５つ検索し、その関連データを水色の部分に表示させたいと思います。

画像で言えばオレンジの部分のデータを水色の部分に表示させたいです。
（水色の部分は求めたい答えを回答者の方に解りやすく表示させたもので、もともとは空白です）

繰り返しますが、機器Aが５つ以上有った場合下から５つを抜き出します。

この場合、どういった関数を使えばよいでしょうか？

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

回答No.5

tom04 · ベストアンサー

No.2・3です。

補足の件ですが・・・
単に作用列のセル番地の参照先を変えるだけです。

例えばO14セルに検索したいデータがある場合は
前回の数式の
$Q$14の部分の「Q」を「O」に変更するだけです。

結果を表示させたいセルに入れる数式はそのままでOKです。
ただし、
＞=IF(COUNT($AX:$AX)<6-ROW(A1),"",INDEX(AV:AV,LARGE($AX:$AX,6-ROW(A1))))
のAXはAX列が作業列の場合の数式ですので、実際の作業列の列番号に合わせてください。

次に
＞また、Q79に入力した場合と２パターン教えていただけませんか？
に関してですが、Q79とは検索データをQ79に入れるというコトですか？
それとも結果を表示させたいセルに入れる数式でしょうか？

仮に検索データをQ79に入れるのであれば、作業列の数式の
＞$Q$14を
＞$Q$79
とするだけです。
もし、結果を表示させたいセルがQ79以降であれば
結果表示の数式はそのままでOKです。

※　一つ気になるのが、
＞ちなみに、同時にQ14,O14、M14.....とたくさん入力されます
の部分です。
これは検索データが複数あり、一つの表からそれぞれを表示させたい！という意味でしょうか？
もしそうであれば表示させたいデータ分だけ作業列を設ける必要があります。
同一Sheetで作業列が目障りであれば、
別Sheetに作業列を設けてそれを参照させた方が良いと思います。

操作方法はまったく一緒ですので、チャレンジしてみてください。
数式は同じなので、参照先が変わるだけです。

とりあえず今回はこの程度で・・・m(_ _)m

お礼

ご回答ありがとうございます。

AX列は先ほどのデータがあったので、次の列AYに作業列を設けやってみました。

AY1
=IF(AR1=$O$14,ROW(),"")

N37に
=IF(COUNT($AY:$AY)<6-ROW(A1),"",INDEX(AV:AV,LARGE($AY:$AY,6-ROW(A1))))

残りはオートフィルで解決できました。

最後まで教えていただき大変ありがたく思います。

ありがとうございました！

回答No.7

KURUMITO

回答No6です。
AX2セルへの入力の式が循環参照になるとのことですがこちらの試験ではそうはなりません。原因が分かりませんね。
ただ、式そのものが難しくなっています。お示しした式は多数の機器について同時にデータを表示させる場合にはよいのですが今回のケースでは機器Aだけのデータを表示させればよいわけですからお示しした式を必要としませんね。
次のようにしても十分対応できますね。
AX2セルには次の式を入力して下方にドラッグコピーします。

=IF(AR2="","",IF(AR2=$Q$14,MAX(AX$1:AX1)+1,""))

P37セルには次の式を入力してQ37セルまで横にドラッグコピーしたのちに41行目まで下方にもドラッグコピーします。

=IF(MAX($AX:$AX)<=5-ROW(A1),"",INDEX(AV:AV,MATCH(MAX($AX:$AX)-5+ROW(A1),$AX:$AX,0)))

回答No.6

KURUMITO

前回のご質問はせっかく苦労して回答したにもかかわらず削除されてしまったことは残念です。
作業列を作って対応します。
データは2行目から下方に入力されているとして例えばAX2セルには次の式を入力して下方にドラッグコピーします。

=IF(AR2="","",IF(COUNTIF(AR$2:AR2,AR2)=1,ROUNDDOWN(MAX(AX$1:AX1),-3)+1000,ROUNDDOWN(INDEX(AX$1:AX1,MATCH(AR2,AR$1:AR1,0)),-3)+COUNTIF(AR$1:AR1,AR2)))

その後に答えですがP37セルには次の式を入力してQ37セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(COUNTIF($AR:$AR,$Q$14)-6+ROW(A1)<0,"",IF(ISERROR(INDEX(AV:AV,MATCH(SMALL($AX:$AX,RANK(INDEX($AX:$AX,MATCH($Q$14,$AR:$AR,0)),$AX:$AX,1)+COUNTIF($AR:$AR,$Q$14)-6+ROW(A1)),$AX:$AX,0))),"",INDEX(AV:AV,MATCH(SMALL($AX:$AX,RANK(INDEX($AX:$AX,MATCH($Q$14,$AR:$AR,0)),$AX:$AX,1)+COUNTIF($AR:$AR,$Q$14)-6+ROW(A1)),$AX:$AX,0))))

なお、エラーが表示される場合には機器Aなどの文字がすべての入力されたセルで一致していない場合が考えられますので一度文字をコピーして他のセルに貼り付けるなどの操作が必要ですね。

補足

ご回答ありがとうございます。

私の無知さゆえ、回答を誤解されては疑心暗鬼になり、正確な再質問をさせていただきました。

正常なご判断が出来る回答者の皆様にはお手数になり大変感謝しております。

ありがとうございます。

それでAX２セルに=IF(AR2="","",IF(COUNTIF(AR$2:AR2,AR2)=1,ROUNDDOWN(MAX(AX$1:AX1),-3)+1000,ROUNDDOWN(INDEX(AX$1:AX1,MATCH(AR2,AR$1:AR1,0)),-3)+COUNTIF(AR$1:AR1,AR2)))
を入力してみましたが、循環参照をしているとのエラーが出て計算できません。

これはどういったことなのでしょうか？

ちなみにQ37セルは他セルを参照していますが複雑な式ではなく、＝セル番号　となっています。

回答No.4

web2525

No1です

=INDEX(AS:AS,LARGE(($AR:$AR=$Q$14)*ROW(AR:AR),6-ROW(1:1)),1)
↓
修正
↓
=INDEX(AV:AV,LARGE(($AR:$AR=$Q$14)*ROW(AR:AR),6-ROW(1:1)),1)

データ列を読み違えていました

補足

ご回答ありがとうございます。

やってみたのですが、同じく＃NUM　とエラーが出ます。

回答No.3

tom04

No.2です！
補足に
＞データは１行目からありますので、AX１に
＞=IF(AR13=$Q$14,ROW(),"")

とありましたので・・・

1行目からデータがある（AR1から「機器1」等）があるのであれば
作業列の数式はAX1セルに
=IF(AR1=$Q$14,ROW(),"")
とします。

AR列がP14セルと一致する行に行番号を表示させるためです。

P37セルに入れる数式はそのままでOKです。
これで大丈夫だと思います。m(_ _)m

補足

ご回答ありがとうございます。

無事目的が果たせましたが、この関数の応用方法がわかりません。

今回はQ14に入力した数字のデータを表示させましたが、それがO14なら関数のどの部分を変えれば良いでしょうか？

また、Q79に入力した場合と２パターン教えていただけませんか？

ちなみに、同時にQ14,O14、M14.....とたくさん入力されます。

回答No.2

tom04

こんばんは！

http://okwave.jp/qa/q7885322.html
で回答した者です。

列配置が違っていますが、前回の数式の列合わせで大丈夫だと思いますが・・・

画像では13行目からデータがあるように見えますので、
作業列AX13セルに
=IF(AR13=$Q$14,ROW(),"")
AX13セルのフィルハンドルでダブルクリック、またはオートフィルで下へコピー！

P37セルに
=IF(COUNT($AX:$AX)<6-ROW(A1),"",INDEX(AV:AV,LARGE($AX:$AX,6-ROW(A1))))
という数式を入れ、隣のQ37セルまでと5行分オートフィルでコピー！

これで何とかご希望通りにならないでしょうか？
※　結局、前回の数式をそのまま列合わせしただけです。

ただ、作業列を使いたくない場合はNo.1さんのように配列数式にする必要があります。m(_ _)m

お礼

ご回答ありがとうございます。

無事目的が果たせましたが、この関数の応用方法がわかりません。

今回はQ14に入力した数字のデータを表示させましたが、それがO14なら関数のどの部分を変えれば良いでしょうか？

また、Q79に入力した場合と２パターン教えていただけませんか？

ちなみに、同時にQ14,O14、M14.....とたくさん入力されます。

補足

ご回答ありがとうございます。

データは１行目からありますので、AX１に
=IF(AR13=$Q$14,ROW(),"")
を入力し最後尾までオートフィルしました。

P37セルに
=IF(COUNT($AX:$AX)<6-ROW(A1),"",INDEX(AV:AV,LARGE($AX:$AX,6-ROW(A1))))

をいれ、隣のQ37にオートフィルしさらに５行オートフィルしました。

P,Qの３７は空白、下４行はデータと、おそらくいい感じで抜き出してくれているようなんですが、データが違うのです。

どうも違う場所を抜き出してる感じです。

回答No.1

web2525

P37セルに
=INDEX(AS:AS,LARGE(($AR:$AR=$Q$14)*ROW(AR:AR),6-ROW(1:1)),1)

配列計算なので【Shift】＋【Ctrl】＋【Enter】で確定
Q41セルまでドラッグしてコピー

補足

ありがとうございます、回答どおりP37に
=INDEX(AS:AS,LARGE(($AR:$AR=$Q$14)*ROW(AR:AR),6-ROW(1:1)),1)
を入力し　コントロール+シフト+エンターキーで確定させたのですが＃NUM　とエラーが出てしまいます。

画像にはデータが１３行からあるようにみえますが、実は１行目からあるんです。

これが原因でしょうか？