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.15

Poer

No.14は余計でした。やはりNo.14はなかったことに．．．（＞◇＜）。

お礼

お礼が遅くなりまして、大変申し訳ありません。
教えていただいた方法で、何とか思い通りのものが作れました。本当にありがとうございました。
ただ、やはりかなり重くなってしまうものなのですね。
少しずつマクロの方も勉強して、より使いやすいものに仕上げていきたいと思います。
また機会がありましたらよろしくお願いします！

回答No.14

Poer

細かいですが、No.13を一部訂正します。

　手順(5)の中の関数

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

回答No.12

Poer

Poerです。正直予想外でした(>_<)。場合によっては根本からやり直す必要があります。次の事柄を教えてください。
　　●商品の種類は最大いくつでしょうか？
　　●項目は全部でいくつでしょうか？
　　●バナナ用の表示スペースははH2～H9までの８行だけ（しかも定位置）でよいのでしょうか？メロンやリンゴの表示スペースも同様に８行の定位置でよいのでしょうか？

お礼

いつもありがとうございます。
説明が足らず、申し訳ありませんでした。
●商品の種類は最大いくつでしょうか？
　　＞３つです
●項目は全部でいくつでしょうか？
　　＞10個程度です
●バナナ用の表示スペースははH2～H9までの８行だけ（しかも定位置）でよいのでしょうか？メロンやリンゴの表示スペースも同様に８行の定位置でよいのでしょうか？
　　＞印刷用にあらかじめ作られたシートの、決められたセルに振り分けをしたいのです。
10個ある項目の中から、振り分ける条件として
「担当者」「商品名」「売上月（今月）」「売上月（四半期）」が、今のところ予定として考えられます。
表示スペースは、各10行程度あると十分です。

振り分け方がわかればあとは何とか応用して出来るものなのかと思い、
最初から説明足らずだったこと、ご迷惑おかけして申し訳ありませんでした。

回答No.11

Poer

Poerです。失礼しました。
　B2は　=SUM(A$2:A2)　で十分です。これなら少しは容量が減るかも？しれません。
　何度も訂正してかっこ悪いですね。しかしこちらも勉強になるのでありがたいです。

お礼

何度もご回答いただき、本当にありがとうございます。
補足いただいた方法で、思い通りに抽出することが出来ました。
出来たのですが・・・、あと一歩、やりたい様に出来なくて・・・。
わがまま言って申し訳ないのですが、
「佐藤」で「バナナ」をH2行目～、
「佐藤」で「リンゴ」をH10行目～
表示させるにはどうしたらいいでしょうか・・・。
AとBの様な列を増やしてみたりしたのですが、うまく出来ません。
もし宜しかったらまたアドバイスお願いします。

回答No.10

Poer

Poerです。＃９のお礼にお答えします。

　　Sheet佐藤のA2に　=IF(D2=G$1,1,0)
というところを、
　　Sheet佐藤のA2に　=IF(AND(D2=G$1,OR(E2=G$2,G$2="")),1,0)
に替えてみてください（もちろん縦にコピーします）。

（項目を増やされたようなので列の名前が代わっていると思います。上の式のG$2もそれに合わせて変更してください）

　そうすると、「佐藤」を入れると佐藤の条件に当てはまるものすべて、その真下のセル（元のG2に当たるセル）に「バナナ」を入れると「佐藤」「バナナ」の両条件に当てはまるものが抽出されます。

　私の不手際で何かと問答が長くなりましたが、お役に立てれば幸いです。ちなみにあまりシートを増やすと容量が大きく動作が鈍くなる可能性があるので、その辺はお気をつけ下さい。
　ちなみにB2のCOUNTIFはSUMIFでも構いません（容量が軽くなるかどうかは分かりませんが．．．）。
　何かあればまた補足してください。うまくいけば締め切ってください。応援してます、Kabooさん！！

回答No.9

Poer

混乱させてしまってすみません。＃７の「お礼」にお答えします。
　　C1に　=Sheet1!A4
は、C1に　=Sheet1!A1　の間違えでした。大変申し訳ありません。
　項目を増やす場合は、F列の右に列を挿入し、F列をコピーしてください。同様に、K列の右にもF列の右に挿入したのと同じ列数だけコピーしてください。
　これでお答えできたでしょうか？

お礼

ありがとうございます。
項目を増やすことは出来たのですが、検索条件を増やすことって可能でしょうか？
「佐藤」で「バナナ」のみ抽出させたいのですが。
お手数おかけしますが、補足いただけるとありがたいです。よろしくお願いします。

回答No.8

imogasi

＃３です。＃3では佐藤さんなどの明細が１０件未満しか考えてないので、実情にあわないかなと思い修正します。
Ｈ２は=G2*100+COUNTIF($B$2:B2,B2)にして下方向に複写します。
101
201
301
102
103
302
となります。
Ｓｈｅｅｔ２のＡ２は
=OFFSET(Sheet1!$A$2,MATCH(100+(ROW(A2)-1),Sheet1!$H$2:$H$100,0)-1,COLUMN(A1)-1)
といれＤ２まで式を複写する。
その後、Ａ２：Ｄ２を範囲指定して、Ｄ２で＋ハンドルうおだし、Ａ４：Ｄ４まで下方向に式を複写してください。
別の質問の質問者曰く、「値しかとってこない」と。
当たり前です。関数でやれば、値しかもってこれません。
また全セルに式が入っているのでデータが多いと重いでしょう。
この回答のイメージは分類して、「送る」のでなく、
条件にあったものを「吸い取る」イメージです。

回答No.7

Poer

＃６の訂正です。
　　(7)．．．横３列（列F）まで　→　．．．横３列（列K）まで

お礼

たくさんのご回答いただき、ありがとうございます。遅くなった上にまとめてお礼を言わせていただくことになってしまって、大変申し訳ありません。

No.6Poerさんの方法なのですが、H1～K1にはリストの一行目が常に表示されてしまう様なのですが、
これは回避できる方法はありますか？
（「(5)C1に　=Sheet1!A4」の部分は「Sheet1!A2」に変更しましたがリストの一行目ってことでいいのですよね？）

それから、ひとつやりたいことが追加になってしまったのですが・・・、
項目に「販売日」が追加になりました。日付（2005/06/08・2005/07/01等）が入力されています。
Sheet佐藤 他 の中で「今月」「翌月」と別れており、日付の項目からも該当するデータを拾い、
それぞれ指定したセルに表示したいのです。

説明が下手で申し訳ありませんが、どなたかお願いします。

回答No.6

Poer

＃５です。手順をご説明します。

　　(1)Sheet1にご質問の例のような表を入れます（１行目は項目見出し）。

　　(2)画面を「Sheet佐藤」に切り替えます。

　　(3)A2に　=IF(D2=G$1,1,0)　を入力、これを必要な行（Sheet1の表で必要とする行）だけ縦にコピーします（A65536までコピーしても構いませんが、あとで容量が大きくなるので避けた方がいいです）。

　　(4)B2に　=COUNTIF(A$2:A2,1)　を入力、必要な行だけ縦にコピー。

　　(5)C1に　=Sheet1!A4　を入力、必要な行だけ縦にコピー。そのまま横３列（列F）までコピー。→Sheet1と同じ表になります。

　　(6)H1に　=C1　と入力し、右にK1までコピー。

　　(7)H2に　=IF(ISERROR(VLOOKUP(ROW(A1),$B$2:C$●,COLUMN(B1),0)),"",(VLOOKUP(ROW(A1),$B$2:C$●,COLUMN(B1),0)))　を入力（●には必要な行数を入れる）。必要な行だけ縦にコピー。そのまま横３列（列F）までコピー。

　　(8)ここでG1に「佐藤」と入力してみてください。H列からK列までに結果が抽出されるはずです。

　　(9)うまくいったら、A列からF列までの列幅を０（ゼロ）にして隠します。

　　(10)Sheet佐藤の全体をSheet鈴木、Sheet田中にコピーします。

　　(11)Sheet鈴木のG1に「鈴木」、Sheet田中のG1に「田中」と入力すれば、それぞれの結果が出てきます。

　手順は以上です。レイアウトが気に入らなければ、セルを移動して調整してください。

回答No.5

Poer

このページを偶然に発見しました。まさに私の得意とする分野です。締め切らずに少しお時間をいただければお答えできます。