関数によるエクセル検索について

質問

エクセル　検索方法についての質問です。

区分　品名　数量
1　　りんご　10
1　　いちご　5
2　　みかん　2
3　　いちご　8
4　　ぶどう　10
5　　いちご　2
5　　いちご　8
5　　りんご　3
6　　みかん　7

このような表の場合

区分　1を選択し、
りんご　10
いちご　5

というようにエクセルに表示させたいです。
VLOOKUP関数をつかうと上の行の　「りんご10」　しか表示させることができません。
同じ区分のものを一度に表示させたいのですが、関数を使って表現することはできますか？？

よろしくおねがいします。

回答No.8

tom04 · ベストアンサー

No.5です！
たびたびお邪魔します。

前回の配列数式はデータ量が多い場合はコンピュータに負担がかかりますので、
作業用の列を使う方法も書いておきます。

↓の画像で、G1セルに区分を入力するとそのデータが表示される方法です。

作業列D2セルに
=IF(OR(A2="",A2<>$G$1),"",ROW(A1))
という数式を入れ、オートフィルで下へずぃ～～～！っとコピーします。

そして、F2セルに
=IF(COUNT($D$2:$D$1000)<ROW(A1),"",INDEX(B$2:B$1000,SMALL($D$2:$D$1000,ROW(A1))))

という数式を入れ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。
（1000行まで対応できるようにしています）
今回は配列数式ではありませんので、そのままコピー＆ペーストでも問題ないと思います。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

お礼

このお礼欄を借りて皆さんにお礼いたします。
回答ありがとうございました。

恥ずかしながら配列数式というものを初めて知りました。そちらの勉強をしようと思います。見につければ他のものにも応用がききそうですね！
とても参考になる回答ありがとうございました！

回答No.7

某HN クロメート(Chromate)（@CoalTar）

区分が昇順として
G2:I2セルを選択する
=IF(COUNTIF(A:A,$E$2)<=ROW()-2,"",
INDEX(A:C,MATCH($E$2,A:A,0)+ROW()-2,))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式です({}で囲まれる)
下へオートフィル

回答No.6

KURUMITO

次のようにしてはどうでしょう。
お示しの表がＡ列からＣ列に入力されているとします。
Ｄ列とＥ列は作業列とします。
Ｄ2セルには次の式を入力します。
=IF(A2="","",IF(COUNTIF(D$1:D1,A2&"/"&B2)>0,"",A2&"/"&B2))
Ｅ2セルには次の式を入力します。
=IF(D2="","",A2&"/"&COUNTIF(D$2:D2,A2&"/*"))
Ｄ2セルをＥ2セルを選択したのちに下方にオートフィルドラッグします。
Ｆ1セルには区分と入力し、Ｆ2セルにはその番号を入力します。
Ｇ1セルには品名と入力し、Ｇ2セルには次の式を入力し下方にオートフィルドラッグします。
=IF(OR(F$2="",COUNTIF(E:E,F$2&"/"&ROW(A1))=0),"",INDIRECT("B"&MATCH(F$2&"/"&ROW(A1),E:E,0)))
Ｈ1セルには総量と入力しＨ2セルには次の式を入力し下方にオートフィルドラッグします。
=IF(G2="","",SUMPRODUCT((A$2:A$10000=F$2)*(B$2:B$10000=G2)*(C$2:C$10000)))

回答No.5

tom04

こんにちは！
すでに回答は出ていて、重複するかもしれませんが・・・

E1セルに区分の数字を入力すると
E2セル以下に「品名」、F2セル以下に「数量」を表示させる方法です。

配列数式になってしまいますので、
この画面からE2セルにコピー＆ペーストしただけではエラーになると思いますので、
貼り付け後、F2キーを押すか、数式バー内で一度クリックします。
編集可能になりますので
Shift＋Ctrl＋Enterキーで確定してください。
数式の前後に｛　｝マークが入り配列数式になります。
尚、数式は1000行まで対応できるようにしていますが、データ量によって
範囲指定の領域はアレンジしてみてください。

E2セルに
=IF($E$1="","",IF(COUNTIF($A:$A,$E$1)<ROW(A1),"",INDEX(B$2:B$1000,SMALL(IF($A$2:$A$1000=$E$1,ROW($1:$999)),ROW(A1)))))

という数式を入れ、Shift＋Ctrl＋Enterキーで確定です。
これを列方向と行方向にオートフィルでコピーしてみてください。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

回答No.4

mt2008

質問で上げられているように、区分でソートされている物とします。
表がA1から始まっていると仮定。
抽出したい区分をE1セルに入れるものとする。
F1セルに　=IF(ROW()>COUNTIF(A:A,$E$1),"",INDEX(A:C,MATCH($E$1,A:A,0)+ROW()-1,2))

G1セルに　=IF(F1="","",INDEX(A:C,MATCH($E$1,A:A,0)+ROW()-1,3))

F1:G1を下方向に（一つの区分に含まれる最大要素分）コピー

無理やりですがこれで出来ます。

回答No.3

MackyNo1

複数の該当データを関数で表示するなら配列数式を使う必要があります。
たとえば、D2セルに検索文字「１」が入力されている場合、以下の式を入力してCtrl+Shift;Enterで確定して配列数式にして、右方向に１つ下方向に適当数オートフィルしてください。

=IF(COUNTIF($A$2:$A$100,$D$2)<ROW(A1),"",INDEX(B:B,SMALL(IF($A$2:$A$100=$D$2,ROW($A$2:$A$100),""),ROW(A1))))

回答No.2

chonami

関数だとMATCH、INDEXあたりを使うことになりますかね・・・
意外と面倒ですよ。

でも、フィルタオプションあたりでやる方が楽だと思いますけど。

回答No.1

mt2008

「関数」限定ですか？オートフィルタを使うべき内容だと思いますが……。

補足

できれば関数限定でお願いいたします。
よろしくお願いします。