EXCEL：抽出・シートに分散させる方法

質問

下記の様なデータを元に、各担当者別に用意された売り上げ表の雛形に表示させたい（指定した項目を指定したセルに）のですが、
実現出来る関数はありますでしょうか？
オートフィルタ＞コピペではなく、毎月の処理になるので自動的に出来る様にしたいのですが。

顧客名　　担当者　　商品名　　金額
Ａ社　　　佐藤　　　バナナ　　100
Ｂ社　　　鈴木　　　バナナ　　100
Ｃ社　　　田中　　　メロン　　100
Ａ社　　　佐藤　　　バナナ　　300
Ａ社　　　佐藤　　　リンゴ　　200
Ｃ社　　　田中　　　メロン　　100

これを↓

Sheet 佐藤

顧客名　　商品名　　金額
Ａ社　　　バナナ　　100
Ａ社　　　バナナ　　300
Ａ社　　　リンゴ　　200

------------------
Sheet 鈴木

顧客名　　商品名　　金額
Ｂ社　　　バナナ　　100

------------------
Sheet 田中

顧客名　　商品名　　金額
Ｃ社　　　メロン　　100
Ｃ社　　　メロン　　100

よろしくお願いします。

回答No.13

Poer · ベストアンサー

Poerです。今までの私の回答はなかったものとして、最初からご説明いたします。この手順に従えば、それなりの作品が出来上がります。ご希望に添えると良いのですが．．．。

(1)Sheet1のA1から10項目（10項目以内なら何項目でもいいです）の表を入れる（ただし一行目を項目名とする）。

(2)Sheet2に切り替える。

(3)まずは見出しを入力します。以下、セル番地・・・「入力する項目名」です。

　　A2・・・　「条件1」
　　B1・・・　「表1」
　　F1・・・　「条件(表1)」
　　I1・・・　「該当(表1)」
　　L1・・・　「抽出No. (表1)」

(4)次に見出しを（オートフィルで）コピーします。

　　A1をA6までコピー（「条件1」「条件2」．．．「条件5」と縦にならぶ）。
　　B1をD1までコピー（「表1」「表2」．．．と横にならぶ）。同様に、
　　F1をH1までコピー。
　　I1をK1までコピー。

(5)関数を入力します。

　　F2・・・　=IF(B2="",0,B2)
　　I2・・・=IF(AND(MATCH(F$2,$O2:$X2,0)>0,MATCH(F$3,$O2:$X2,0)>0,MATCH(F$4,$O2:$X2,0)>0,MATCH(F$5,$O2:$X2,0)>0,MATCH(F$6,$O2:$X2,0)>0),1,0)
　　L2・・・　=SUMIF(I$2:I2,1)

　　O1・・・　=Sheet1!A1

　　Y1・・・　=CONCATENATE(B2,IF(B3="","","/"),B3,IF(B4="","","/"),B4,IF(B5="","","/"),B5,IF(B6="","","/"),B6)
　　Y14・・・=CONCATENATE(C2,IF(C3="","","/"),C3,IF(C4="","","/"),C4,IF(C5="","","/"),C5,IF(C6="","","/"),C6)
　　Y26・・・　=CONCATENATE(D2,IF(D3="","","/"),D3,IF(D4="","","/"),D4,IF(D5="","","/"),D5,IF(D6="","","/"),D6)

　　Y2・・・　=O1
　　Y15・・・　=O1
　　Y27・・・　=O1

　　Y3・・・　=IF(ISERROR(VLOOKUP(ROW(A1),$L:O,COLUMN(D1),0)),"",VLOOKUP(ROW(A1),$L:O,COLUMN(D1),0))
　　Y16・・・　=IF(ISERROR(VLOOKUP(ROW(A1),$M:O,COLUMN(C1),0)),"",VLOOKUP(ROW(A1),$M:O,COLUMN(C1),0))
　　Y28・・・　=IF(ISERROR(VLOOKUP(ROW(A1),$N:O,COLUMN(B1),0)),"",VLOOKUP(ROW(A1),$N:O,COLUMN(B1),0))

(6)関数を（オートフィルで）コピーします（注：10項目の表の下端の行を仮に101行とします）。

　　F2をH101までコピー。
　　I2をK101までコピー。
　　L2をN101までコピー。

　　O1をX101までコピー。

　　Y2をAH2までコピー。
　　Y15をAH15までコピー。
　　Y27をAH27までコピー。

　　Y3をAH12までコピー。
　　Y16をAH24までコピー。
　　Y28をAH37までコピー。

(7)作業列（F列からX列まで）の列幅をゼロにして列を隠します。

(8)メニューバーの「ツール」→「オプション」→「表示」タブ→「ウィンドウオプション」の「ゼロ値」のチェックをはずします。

(9)B2～B6に抽出条件を入力（任意の箇所に入力できます）すると、3つの表（Y1:AH12、Y14:AH24、Y26:AH37）に結果が表示されます。

(10)必要に応じてシート自体を他のシートにもコピーします。

　これでご希望に添えなければ残念としか言いようがありません（＞◇＜）。とりあえず試してみてください！

回答No.4

KenKen_SP

ほぼ、そのままズバリかも知れません。

http://oshiete1.goo.ne.jp/kotaeru.php3?q=1219526

参考URL：

http://oshiete1.goo.ne.jp/kotaeru.php3?q=1219526

お礼

ありがとうございます。拝見させていただいたのですが、私の場合、
シートは自動作成する必要は無く、雛形があるので指定したセルに振り分けたいのです。
どうにか応用して出来るのでしょうか・・・。

回答No.3

imogasi

私が、あまりエクセルの関数に慣れてない人に本質問を聞かれたら、「できません」と答えます。
結構複雑です。
それと個人別シートの全セルが関数式で埋まります。
他の計算式を入れることが制約されます。
また下記回答も不完全です。完全にしようとするとさらに複雑になります。まあしかし記しておきます。
例データ
顧客名　　 担当者　　 商品名　　 金額
Ａ社　　　 佐藤　　　 バナナ　　 100
Ｂ社　　　 鈴木　　　 バナナ　　 100
Ｃ社　　　 田中　　　 メロン　　 100
Ａ社　　　 佐藤　　　 バナナ　　 300
Ａ社　　　 佐藤　　　 リンゴ　　 200
Ｃ社　　　 田中　　　 メロン　　 100
質問問題のもの
Ｆ，Ｇ，Ｈ列の関数を入れた結果
これらはワーク列（作業中間列）
初出番号 初出番号付番 担当者別番号付番
1 1 11
2 2 21
3 3 31
1 12
1 13
3 32
関数について
Ｆ２には=IF(COUNTIF($B$2:B2,B2)=1,MAX($F$1:F1)+1,"")と入れてＦ７まで式複写
Ｇ２には=VLOOKUP(B2,$B$2:$F$100,5,FALSE)と入れて
Ｇ７まで式複写
Ｈ２には=G2&COUNTIF($B$2:B2,B2)と入れてＨ７まで式を複写します。
－－－－－－
Ｓｈｅｅｔ２に移って
Ａ２には=OFFSET(Sheet1!$A$2,MATCH(1&(ROW(A2)-1),Sheet1!$H$2:$H$100,0)-1,COLUMN(A1)-1)
と入れてＤ２まで式複写します。
Ａ２：Ｄ２を範囲指定し、Ｄ２に＋ハンドルを出して
Ａ４：Ｄ４まで引っ張ります。
結果
Ａ社　　　 佐藤　　　 バナナ　　 100
Ａ社　　　 佐藤　　　 バナナ　　 300
Ａ社　　　 佐藤　　　 リンゴ　　 200
鈴木、田中はＳｈｅｅｔ３、Ｓｈｅｅｔ４で同じく式を入れてください。
＄１００は最多１００までということで適当に見積もり仮定してます。
不完全といったのは佐藤は３行、鈴木１行、田中２行でとめないとエラーが出ますが、これを防ごうとすると式がさらに複雑になることをさしてますが、略。
私なら上記でなく、ＶＢＡでやることをお勧めします。

回答No.2

SAKURAMYLOVE

関数を使うと煩雑になりますが、以下の方法ではいかがですか。
【条件】
(1)元のデータのシート名をDATAとする
(2)データには、100行までデータを入れるものとする
　（必要があれば、関数内の引数を適宜直してください）

【手順】
(1)データがD列まで入っているとして、作業用の列にセルE1:佐藤、F2:鈴木、G1:田中　を入力しておく。
(2)セルE2に以下の式を入力する。
E2=IF($B2=E$1,COUNTIF($B2:$B$100,E$1),"")
(3)E2の式をE2:G100までコピー、貼り付けする
　（目障りであれば、E～G列は非表示としてください）
(4)個人別シートを作成し、以下の項目名を１列目に入力する。
A1:No.(A2以下1,2,3・・・と連番を振っておく）、B1:顧客名、C1:商品名、D1金額、D1:担当名（佐藤）
(5)B2～D2まで以下の式を入力し、下の行へコピー、貼付けを行う。
B2=IF(COUNTIF(DATA!$B$2:$B$100,$E$1)<$A2,"",INDEX(DATA!$A$2:$A$100,MATCH($A2,DATA!$E$2:$E$100,0)))
C2=IF(COUNTIF(DATA!$B$2:$B$100,$E$1)<$A2,"",INDEX(DATA!$C$2:$C$100,MATCH($A2,DATA!$E$2:$E$100,0)))
D2=IF(COUNTIF(DATA!$B$2:$B$100,$E$1)<$A2,"",INDEX(DATA!$D$2:$D$100,MATCH($A2,DATA!$E$2:$E$100,0)))
(6)必要に応じ担当名シートを追加して(5)のシートをコピー、貼り付けしてください。
その際、E1の担当名を変更するとともに、DATAで追加した担当名に該当する列名に以下の式を変更してください。
例：鈴木のシートを追加した場合、E1を鈴木にかえるとともにB2は以下の式に変更する。
B2=IF(COUNTIF(DATA!$B$2:$B$100,$E$1)<$A2,"",INDEX(DATA!$A$2:$A$100,MATCH($A2,DATA!$F$2:$F$100,0)))

MATCH($A2,DATA!$E$2:$E$100)をMATCH($A2,DATA!$F$2:$F$100)にE→F書き換え（C2,D2以下の行も同様）

回答No.1

saburo0

質問内容と多少違うかも知れませんが、下記Ｑ＆Ａが役に立ちますか？
http://oshiete1.goo.ne.jp/kotaeru.php3?q=1405360

＃１は私ですが、＃２の方の方法は私のやり方より簡潔に出来ているので、そっちを使ったほうが良いかもしれません。
分からなければ、補足願います。

参考URL：

http://oshiete1.goo.ne.jp/kotaeru.php3?q=1405360