Excelでデータ抽出処理をオートフィルタではなく関数でできませんか？

Excelで、発注書を作成しようとしています。
１、Sheet1には<発注先><品名><数量>のデータになっており、
　　同じ発注先のデータも含んでいます。
　　<発注先>　<品名>　<数量>
　　　Ａ社　　　みかん　　　10
　　　Ａ社　　　もも　　　　　5
　　　Ｂ社　　　ぶどう　　　　3
　　　Ｃ社　　　みかん　　　　5
　　　Ｃ社　　　バナナ　　　　5

２、このSheet1のデータを各発注先ごとに発注書を作成するため
　　別シートに用意した発注書フォーマットに貼り付けたい。
　　（注：発注先は６０社ほどあり、オートフィルタで発注先ごと
　　　　　にコピペしていくのは、困難です）

　できれば関数でできればいいのですが、何かよい方法はないでしょうか？よろしくお願いします。
　　　

No.1 回答者： maron--5 回答日時： 2007/07/11 04:07

◆Sheet1

　　　　A　　　　　　　B　　　　　　C
1　　<発注先>　　<品名>　　<数量>
2　　　Ａ社　　　　みかん　　　10
3　　　Ａ社　　　　もも　　　　　　5
4　　　Ｂ社　　　　ぶどう　　　　3
5　　　Ｃ社　　　　みかん　　　　5
6　　　Ｃ社　　　　バナナ　　　　5

◆Sheet2
　　　　A　　　　　　　B
1　　発注書
2　　発注先　　　　Ｃ社
3　　品名　　　　　数量
4　　みかん　　　　　5
5　　バナナ　　　　　5

◆Sheet2のB3に、発注先を入力します
A4=IF(ROW(A1)>COUNTIF(Sheet1!$A$1:$A$10,$B$2),"",INDEX(Sheet1!B$1:B$10,SMALL(INDEX(SUBSTITUTE(N(Sheet1!$A$1:$A$10=$B$2),0,10^5)*ROW(Sheet1!$A$1:$A$10),),ROW(A1))))
★右と下にコピー

この回答へのお礼

早速のご回答ありがとうございました。
関数を１つずつ理解しながら、なんとか理解できました。

お礼日時：2007/07/11 22:00

No.2 ベストアンサー 回答者： mike_g 回答日時： 2007/07/11 07:08

「別シート」において

１．セル B2 に「発注先」名を入力
２．セル A4 に次式を入力して、此れをセル B4 に複写
　　=IF(ROW(A1)>COUNTIF(Sheet1!$A$2:$A$1000,$B$2),"",OFFSET(Sheet1!$A$1,MATCH($B$2,Sheet1!$A$2:$A$1000,0)+ROW(A1)-1,COLUMN(A1)))
３．範囲 A4:B4 を下方にズズーッと複写

この回答へのお礼

まだ記載いただいた関数式の意味は理解できていませんが、
そのとおりに作ったら、見事できました。
これで作業がスムーズに行えます。ありがとうございました。

お礼日時：2007/07/11 22:02

No.3 回答者： imogasi 回答日時： 2007/07/11 10:22

（１）関数で　

＃１、＃２のご回答
あの式が理解でき、頭に入る方はこれが良い。
（２）関数で
もう少し判りやすいかなという方法。作業列を使います。（自称imogasi方式）Ｓｈｅｅｔ１のＡ１：Ｅ９
社名品名価格社連番
Ａ社みかん10Ａ社1
Ａ社もも5Ａ社2
Ｂ社ぶどう3Ｂ社1
Ｃ社みかん5Ｃ社1
Ｃ社バナナ5Ｃ社2
Ａ社バナナ8Ａ社3
Ｄ社栗11Ｄ社1
Ａ社スイカ7Ａ社4
ーー
Ｅ２の式は　
=A2 & COUNTIF($A2:A$2,A2)
式を下方向に複写。
結果
上記
ーーー
Ｉ１に指定会社名を入れる。
Ｉ２に
=IF(ROW()-1>COUNTIF($A$2:$A$100,$I$1),"",INDEX($A$2:$C$100,MATCH($I$1&ROW()-1,$E$2:$E$100,0),COLUMN(I2)-8))
と入れて、Ｊ、Ｋ列に式を複写。
Ｉ２：Ｋ２を範囲指定し、Ｋ２で＋ハンドルを出して下へ引っ張る。
ーー
Ｉ、ｊ、ｋ列をＳｈｅｅｔ２に出す場合は、Ｓｈｅｅｔ１のＡ－Ｄの参照には「Ｓｈｅｅｔ１！」をつければできる。

No.4 回答者： imogasi 回答日時： 2007/07/11 11:13

＃３です。

追加して、別方法で１つ上げます。
（３）ユーザー関数法
エクセルで抜き出しを難しくしているのが、２番目、３番目、・・
の見つかったデータのあり場所（行番号）が判る関数が無いのが原因と考えます。それで自作しました。
ーー
例データは＃３と同じ。
Ｉ１セルに、抜き出すＡ社を入れる。
Ｉ列　Ｊ列　Ｋ列　Ｌ列（参考）
Ａ社
Ａ社みかん102
Ａ社もも53
Ａ社バナナ87
Ａ社スイカ79
0
ーーー
ユーザー関数の作成
ＶＢＥの標準モジュールに
Function fnd(a, b, c)
k = 0
cl = a.Column
For i = 1 To 1000
If Cells(i, cl) = b Then
k = k + 1
Select Case k
Case c
' MsgBox i & "-" & k & "-" & c
fnd = i
Exit Function
Case Is > c
fnd = 0
Exit Function
End Select
End If
Next i
End Function
をコピー張り付けする。
使い方は、この関数には、３つ引数があって
=fnd(探す列、探す会社等の内容,何番目に見つかったものを採るか)
これで、指定番目に、見つかった行番号を返して来ます。
ーー
Ｉ２に
=IF(fnd($A1,$I$1,ROW()-1)=0,"",INDEX($A$1:$C$100,fnd($A1,$I$1,ROW()-1),COLUMN(I1)-8))
と入れて、Ｊ，Ｋ列に式を複写。
Ｉ２：Ｋ２を下方向に式を複写。
ーー
結果
上表のとおり。
Ｌ列はfnd関数だけの、返してくる値を参考までに上げた。
ーーー
Ｓｈｅｅｔ２に抜き出す場合は
Ａ２に
=IF(fnd(Sheet1!$A2,$A$1,ROW()-1)=0,"",INDEX(Sheet1!$A$1:$C$100,fnd(Sheet1!$A1,$A$1,ROW()-1),COLUMN()))
Ｃ２まで式を複写。
Ａ２：Ｃ２を下方向に式を複写。
結果　Ｓｈｅｅｔ２　Ａ１：Ｃ５
Ａ社
Ａ社みかん10
Ａ社もも5
Ａ社バナナ8
Ａ社スイカ7
指定社名Ａ社のセルが別のセルにある場合
社名をセットする列が変わる場合、Column()より適当数を引いて、際右列が１になるよう
にしてください。

この回答へのお礼

関数のわかりやすい解説とＶＢＡまで作成いただき本当にありがとうございました。
ＶＢＡに関しては、必ず必要になってくると感じますのでこれを機会に
納得できるよう、理解するつもりです。
エクセルって使う人によってはポテンシャルのほんの数％しか使えていないことに気づかされました。

お礼日時：2007/07/11 22:07