エクセルデータの抽出方法について

質問

Ａ３：Ｅ３に横に仕入れ先、品名、数量、金額の項目がありランダムに15行データが入っています。
この表をＧ3：Ｊ17に仕入れ先ＡをＭ3：Ｑ17に仕入れ先Ｂをという順番で仕入れ先毎にデータを抽出する方法を教えてください。
サンプル表を添付します。

回答No.7

Chiquilin

> 抽出したデータを業者毎の発注表として活用するので関数でお願
> いします。
だから数式じゃないとダメってのはどういう理屈なんでしょう。

抽出だけならセルの値をパラメータにしてクエリを１つ作れば済み
ます。 Excel2013なら PowerQueryが使えるでしょう。

わざわざ手間かけて数式で頑張ったところで Excel2013が来年 4月
で延長サポートが切れることを考えたら掛けた手間は無駄になる
でしょう。

新しい Excelでは FILTER関数一本で済む話だからです。

回答No.6

msMike

[No.5]用の“忘れ物”です。m(_._)m

回答No.5

msMike

添付図参照(Excel 2019)

１．"仕入先"@ に書式設定したセル G1、L1、Q1 に文字列 A、B、C を入力して、それぞれのセルを含めて右方4列ずつセル結合
２．私の技量不足で151行目を作業用として使用、式↓
￣￣=OFFSET($G$1,,INT((COLUMN(A1)-1)/5)*5)
￣￣を入力したセル G151 を右方にズズーッと(U列まで)オートフィル
３．配列(SCE)数式↓
=IFERROR(INDEX(INDIRECT(G$2),SMALL(IF(仕入先=G$151,ROW(仕入先)),ROW(A1))-2),"")
￣￣を入力したセル G3 を右方T列まで＆下方(全列が空白になるまで)ズズーッとオートフィル
オ・シ・マ・イ

この私の投稿をご覧になった方へお願いです。
ステップ３の式中の G$151 にステップ２の式 OFFSET($G$1,,INT((COLUMN(A1)-1)/5)*5) を代入すれば作業用の 151行目は不要のはずと思っているのですが、期待通りにならない理由が分からず戸惑っています。お分かりになるなら教えてください。m(_._)m

回答No.4

imogasi

#1です。
データを抽出する課題で、作業列を使う方法は、OKWAVEの質問に対し、今までもimogasi方式とか自称して、回答を挙げてきました。
これを避けるためには、例えばA列でセルの値のaaが、「ｎ（式の中で指定）番目に現れる行は何行目か」を返してくれる関数があれば、作業列は不要になることに気づき、そういうユーザー関数を作って、やってみました。
今回の質問者向け、ではないかもしれないが、他の方の参考になる場合もあると思って。そのうちFILTER関数が使えるバージョンが使われて、必要なくなるでしょうが。
ーー
ＶＢＡですが、標準モジュールに
Function banmeR(rng As Range, s As String, n As Long)
i = 0
For Each cl In rng
If cl = s Then
i = i + 1
If i = n Then
banmeR = cl.Row
End If
End If
Next
End Function
＝＝＝
例データ　Sheet1のA1:B10に
品名 価格
aa 10
bb 20
aa 30
bb 40
cc 50
aa 60
bb 70
cc 80
bb 90
とあるとする。
ーー
品名aaだけを抜き出す、関数は
=INDEX($A$1:$B$10,banmer($A$1:$A$10,"aa",ROW()-13),COLUMN()-5)
これは式をF14から入れる（抽出したデータを置く）場合の式です。
COLUMN()-5は、直前のE列が第5番目の列なので-5した,
ROW()-13は１４行目に入れるので-13しています（F14は気まぐれで決めた）。
banmerは定義した関数の名前です。このブック内のシートでしか使えません。
ーー
結果　F14:F1７
aa 10
aa 30
aa 60
#VALUE! #VALUE!
なお、#VALUE!は、IFERROR関数を被せれば、消せます。
=IFERROR(上記の式,””）など。

回答No.3

imogasi

#1です。
この質問に付いての、考え方につて、是非知ってほしい。
（１）最近では、「関数でやりたい」だけでは済まないのだ。関数も２０１６，２０１９，２０２１、３６５あたりから、数が増えた（それも重要なもの）り、新しい考え方（「スピル」、ラムダ式など）も出ているからだ。
２０１３は残念ながら、その前のものです。
だから旧来の考え・関数でやらざるを得ない。FILTER関数が使えないのが痛い。
（２）そして小生が＃１で分けて例示したように、関数でも、作業列を使うことを我慢するかどうかが、ある。
＃２の回答でも、作業列を使う回答のようだ。
そもそも、エクセル関数で、データ抜出しの課題をやるのは、難しい課題だと思う。
ここの質問者は、そのむつかしさに気が付いていないので、作業列を使う式は、敬遠しているようだ。
ーー
作業列を使わない場合は（１）難しい式の組み合わせが必要（２）配列数式（３）
SUMPRODUCT関数の応用でできないか、などが過去の同種の質問での、回答タイプだったように思う。
（３）操作で、フィルタ―（その後可視セル範囲のコピー）を使えば、仕入れ先A,B、・・ごとの部分表にできるのでは。
データの或る行と同じ行範囲（且つ範囲外の列）内に、抽出データを出している場合は、フィルタアイコンをもう一度クリックすると、全貌（全行）が見える。
（４）関数でやる場合、品名のA、B、C・・の文字列をG3セル、M3セル、S3セ
ル・・（離れた列にある。それが難しさの原因）にほしい（必要な）、のだが、この問題まで、関数で出すとすると、式の複写方式では出来ないだろう。結局、人間の判断や操作が必要になるのでは。
質問者はどういう箇所が、難しいことになるかわかってない、のではと思う。
だからある程度（重複のない品名を、望みのセルに手操作で入力するの）は我慢しなくてはならないだろう。こういうのはVBAなら、プログラムを組む段階で、決めて、プログラムに組み込むので、この表のユーザーはエクセル素人でもよい。

回答No.2

SI299792

Ｇ列の仕入れ先は不要だと思うのですが。
バージョンは何ですか。バージョンによって使える関数か変わるので面倒です。
共通
F1: A
G1: ="仕入れ先"&F1

Excel2021 or OneDriveなら
G3: =FILTER(A:E,A:A=F1)

Excel2019 以前なら、
Ｆ列をワークエリアに使います。目障りならフォントを白又は非表示にして下さい。
F3: =F2+($F$1=A3)
G3: =IFERROR(INDEX(A:A,MATCH(ROW()-2,$F:$F,0)),"")
G3をK3迄コピペ。
纏めて下へコピペ。

F1をプルダウンにしておけば、切り替えが不要なので、Ｌ列以右は不要です。
それでも作りたい場合、
検索と選択、置換で数式の$ を消します。
Ｆ～Ｋ列をＬ列へコピペ、L1を変更、この処理を繰り返します。

F1をプルダウンにするなら、自動でプルダウンが作成された方がいいと思います。
（補足に書いていただければ、作り方を上げます）

補足

バージョンはエクセル2013です

回答No.1

imogasi

仕入れ先・列でソート（並べ替え）して、仕入れ先Aの塊（行範囲）を（目視で判別し）望みの場所に張り付けたら仕舞い。
これが一番早い。
ーー
どういう方法でということを、質問に書かないから、こう言う回答をした。
下記（1）関数を希望か？
エクセルは関数がすべてではないよ。
ーー
方法は
（１）関数
（１’）作業列を使ってよいか
（２）操作（フィルタなど）
（３）VBAなど利用
（４）その他、便利な（フリー）ソフト利用
（５）データベースソフト（Accessのようなもの）

補足

抽出したデータを業者毎の発注表として活用するので関数でお願いします。