エクセル2002を使っています。
画像の様なデータシートがあります。
紫の部分 Q14に 機器A と入力された場合に AR列の機器Aを下から5つ検索し、その関連データを水色の部分に表示させたいと思います。
画像で言えばオレンジの部分のデータを水色の部分に表示させたいです。
(水色の部分は求めたい答えを回答者の方に解りやすく表示させたもので、もともとは空白です)
繰り返しますが、機器Aが5つ以上有った場合下から5つを抜き出します。
この場合、どういった関数を使えばよいでしょうか?
よろしくお願いいたします。
No.5ベストアンサー
- 回答日時:
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に入力した場合と2パターン教えていただけませんか?
に関してですが、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
- 回答日時:
回答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
- 回答日時:
前回のご質問はせっかく苦労して回答したにもかかわらず削除されてしまったことは残念です。
作業列を作って対応します。
データは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などの文字がすべての入力されたセルで一致していない場合が考えられますので一度文字をコピーして他のセルに貼り付けるなどの操作が必要ですね。
この回答への補足
ご回答ありがとうございます。
私の無知さゆえ、回答を誤解されては疑心暗鬼になり、正確な再質問をさせていただきました。
正常なご判断が出来る回答者の皆様にはお手数になり大変感謝しております。
ありがとうございます。
それで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)))
を入力してみましたが、循環参照をしているとのエラーが出て計算できません。
これはどういったことなのでしょうか?
ちなみにQ37セルは他セルを参照していますが複雑な式ではなく、=セル番号 となっています。
No.4
- 回答日時:
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)
データ列を読み違えていました
No.3
- 回答日時:
No.2です!
補足に
>データは1行目からありますので、AX1に
>=IF(AR13=$Q$14,ROW(),"")
とありましたので・・・
1行目からデータがある(AR1から「機器1」等)があるのであれば
作業列の数式はAX1セルに
=IF(AR1=$Q$14,ROW(),"")
とします。
AR列がP14セルと一致する行に行番号を表示させるためです。
P37セルに入れる数式はそのままでOKです。
これで大丈夫だと思います。m(_ _)m
この回答への補足
ご回答ありがとうございます。
無事目的が果たせましたが、この関数の応用方法がわかりません。
今回はQ14に入力した数字のデータを表示させましたが、それがO14なら関数のどの部分を変えれば良いでしょうか?
また、Q79に入力した場合と2パターン教えていただけませんか?
ちなみに、同時にQ14,O14、M14.....とたくさん入力されます。
No.2
- 回答日時:
こんばんは!
http://oshiete.goo.ne.jp/qa/7885322.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
この回答への補足
ご回答ありがとうございます。
データは1行目からありますので、AX1に
=IF(AR13=$Q$14,ROW(),"")
を入力し最後尾までオートフィルしました。
P37セルに
=IF(COUNT($AX:$AX)<6-ROW(A1),"",INDEX(AV:AV,LARGE($AX:$AX,6-ROW(A1))))
をいれ、隣のQ37にオートフィルしさらに5行オートフィルしました。
P,Qの37は空白、下4行はデータと、おそらくいい感じで抜き出してくれているようなんですが、データが違うのです。
どうも違う場所を抜き出してる感じです。
ご回答ありがとうございます。
無事目的が果たせましたが、この関数の応用方法がわかりません。
今回はQ14に入力した数字のデータを表示させましたが、それがO14なら関数のどの部分を変えれば良いでしょうか?
また、Q79に入力した場合と2パターン教えていただけませんか?
ちなみに、同時にQ14,O14、M14.....とたくさん入力されます。
No.1
- 回答日時:
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 とエラーが出てしまいます。
画像にはデータが13行からあるようにみえますが、実は1行目からあるんです。
これが原因でしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel(エクセル)でフィルター抽出後、非表示の行を計算しないで、合計を算出する方法 【内容】 添 4 2023/01/30 17:17
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Visual Basic(VBA) 【VBA】データを入力後に,同一シート内に履歴として転記するVBAコードを教えていただきたいです。 3 2022/11/16 01:37
- Excel(エクセル) エクセルで対象日に該当するデータがある場合に別表へ全対象者を表示させたい。 3 2023/07/12 09:48
- Excel(エクセル) [Excel2016] 相関表等の自動作成 2 2022/08/01 20:34
- Excel(エクセル) エクセルの散布図で新たに入力した値のデータラベルが空欄になる現象 1 2022/04/26 09:31
- その他(プログラミング・Web制作) Microsoft Forms とか、それに似たツールで計算をすることはできないか? 2 2023/01/14 11:26
- Excel(エクセル) Excel フィルタ後のコピー貼付けと可視セルの関係について 3 2023/02/07 16:42
- Excel(エクセル) ExcelのVLOOKUP関数 7 2022/08/23 06:46
- Excel(エクセル) Excel 表の作成について 3 2022/06/16 12:15
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル vbaについて教えてく...
-
Excel 複数列 A列B列C列一致 D...
-
【マクロ】名前を保存する際に...
-
エクセルであるセルに数字を入...
-
エクセルファイルがファイルの...
-
Excelについて教えてください
-
エクセル マクロ チェックボックス
-
【Excel】数字を3倍にし、なお...
-
エクセルVBA、別ブックへ転記す...
-
Excel分析ツールでのポアソン回...
-
Excelでセルの値が同じか...
-
エクセルで年休を管理する方法...
-
エクセル共有したが、アクセス...
-
エクセルのデーターが2か月前の...
-
【マクロ】顧客番号にて一致さ...
-
(マクロ)データをAブックからB...
-
エクセルを使っていて2024/5/15...
-
エクセル②
-
UNIQUE関数が使えないバージョ...
-
【マクロ】その時、その時で変...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報