Excel 他シートのデータを抽出

質問

お世話になっております。

さっそく質問失礼いたします。

全売上が記載されたシートのデータを抽出し、営業担当者名で割り振られた各シートに該当するデータを抽出する方法を教えてください。

具体的には下記のようなものを作成したく思います。

シート1（月度売上表）
日付　担当　商品　台数
1日　田中　　A1　　１台
1日　山田　　B1　　２台
1日　佐藤　　B1　　５台
2日　田中　　B1　　３台
2日　佐藤　　A1　　２台
2日　佐藤　　B1　　４台

シート２（田中）
日付　担当　商品　台数
1日　田中　　A1　　１台
2日　田中　　B1　　３台

シート３（佐藤）
日付　担当　商品　台数
1日　佐藤　　B1　　５台
2日　佐藤　　A1　　２台
2日　佐藤　　B1　　４台

以下続く

以上のような表を一覧として作成したいと思っております。
シート1に内容が記載されたら自動的にリンクされるようにしたいので、ご質問いたしました。

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

回答No.7

kagakusuki · ベストアンサー

回答No.5、6です。

　それから別の方式として、元データの表中の項目（列）の中に「入力されているデータが行ごとに必ず異なっていて、重複するデータは存在しない」という事が確実である列が存在している場合には、作業列を使わずに済ます事も出来ます。

　今仮に、月度売上表シートのA列に連番が振られていて、B列には日付が、C列には担当名が、D列には商品名が、E列には台数が、それぞれ入力されていて、A1セルには「No.」、B1セルには「日付」、C1セルには「担当」、D1セルには「商品」、E1セルには「台数」と、それぞれ入力されているものとします。

　まず、「田中」というシート名のシートを作成して下さい。
　次に、田中シートのB2セルに次の数式を入力して下さい。

=REPLACE(CELL("filename",B1),1,FIND("]",CELL("filename",B1),FIND(".xls",CELL("filename",B1))),)

　次に、田中シートのA3セルに「No.」、Bセ3ルには「日付」、Cセ3ルには「商品」、D3セルには「台数」と、それぞれ入力して下さい。
　次に、田中シートのA4セルに次の数式を入力して下さい。

=IF(ROWS($4:4)>COUNTIF(月度売上表!$C:$C,$B$1),"",INDEX(月度売上表!$A:$E,MATCH($B$1,INDEX(月度売上表!$C:$C,MATCH($A3,月度売上表!$A:$A,0)+1):INDEX(月度売上表!$C:$C,ROWS(A:A)),0)+MATCH($A3,月度売上表!$A:$A,0),MATCH(A$3,月度売上表!$A$1:$E$1,0)))

　次に、田中シートのA4セルをコピーして、田中シートのB4～D4の範囲に貼り付けて下さい。
　次に、田中シートのA4～D4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　次に、田中シートのコピーシートを、担当者の人数分だけ複製し、それらのコピーシートの各々のシート名を、各担当者名に変更する事で、全ての担当者のデータを抽出表示するためのシートを作成して下さい。

　以上です。

お礼

ありがとうございました！

回答No.6

kagakusuki

回答No.5です。

　それから、例えば田中シートには田中さんのデータしか存在していない筈ですので、田中シートのB列の全ての行に亘って「田中」と表示するのは無駄ではないかと思います。
　尤も、印刷した際にはシート名は表示されませんから、単純にB列の「担当」という項目を無くしてしまうだけでは、印刷時にどの担当者のデータであるのか判らなくなる恐れがあります。
　ですから、適当なセル（ここでは仮にB1セルとします）1個だけに「田中」と表示する様にされては如何でしょうか？（この方が数式も単純化させる事が出来るため、同じ情報を扱うために必要となるＥｘｃｅｌファイルの情報量を節約する事が出来ます）

　まず、月度売上表シート中の適当な未使用の列（ここでは仮にF列であるものとします）の2行目のセル（ここの例で言いますとF2セル）に、次の数式を入力して下さい。

=IF(INDEX($B:$B,ROW())="","",INDEX($B:$B,ROW())&"◆"&COUNTIF($B$1:INDEX($B:$B,ROW()),INDEX($B:$B,ROW())))

　次に、月度売上表シートのF2セルをコピーして、月度売上表シートのF3以下に貼り付けて下さい。

　次に、「田中」というシート名のシートを作成して下さい。
　※ここまでは、回答No.5と同じです。

　次に、田中シートのB2セルに次の数式を入力して下さい。

=REPLACE(CELL("filename",B1),1,FIND("]",CELL("filename",B1),FIND(".xls",CELL("filename",B1))),)

　次に、田中シートのA3セルに「日付」、B3セルに「商品」、C3セルに「台数」と入力して下さい。
　次に、田中シートのA4セルに次の数式を入力して下さい。

=IF(ROWS($4:4)>COUNTIF(月度売上表!$B:$B,$B$1),"",INDEX(月度売上表!$A:$D,MATCH($B$1&"◆"&ROWS($4:4),月度売上表!$F:$F,0),MATCH(A$3,月度売上表!$A$1:$D$1,0)))

　次に、田中シートのA4セルをコピーして、田中シートのB4～C4の範囲に貼り付けて下さい。
　次に、田中シートのA4～C4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　次に、田中シートのコピーシートを、担当者の人数分だけ複製し、それらのコピーシートの各々のシート名を、各担当者名に変更する事で、全ての担当者のデータを抽出表示するためのシートを作成して下さい。

　以上です。

回答No.5

kagakusuki

作業列を担当者の人数分だけ用意する必要などは御座いません。
　御質問の件の様な目的でしたら、作業列を1列だけ用意されるだけで事足ります。

　今仮に、元データが入力されているシートが「月度売上表」というシート名であり、各担当者ごとのデータを抽出するシートのシート名は、元データの表中に入力されている担当者名と同じ文字列のシート名となっているものとします。

　まず、月度売上表シート中の適当な未使用の列（ここでは仮にF列であるものとします）の2行目のセル（ここの例で言いますとF2セル）に、次の数式を入力して下さい。

=IF(INDEX($B:$B,ROW())="","",INDEX($B:$B,ROW())&"◆"&COUNTIF($B$1:INDEX($B:$B,ROW()),INDEX($B:$B,ROW())))

　次に、月度売上表シートのF2セルをコピーして、月度売上表シートのF3以下に貼り付けて下さい。

　次に、「田中」というシート名のシートを作成して下さい。
　次に、田中シートのA1セルに「日付」、B1セルに「担当」、C1セルに「商品」、D1セルに「台数」と入力して下さい。
　次に、田中シートのA2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF(月度売上表!$B:$B,REPLACE(CELL("filename",A2),1,FIND("]",CELL("filename",A2),FIND(".xls",CELL("filename",A2))),)),"",INDEX(月度売上表!A:A,MATCH(REPLACE(CELL("filename",A2),1,FIND("]",CELL("filename",A2),FIND(".xls",CELL("filename",A2))),)&"◆"&ROWS($2:2),月度売上表!$F:$F,0)))

　尚、この数式中の

FIND("]",CELL("filename",A3),FIND(".xls",CELL("filename",A3)))

という箇所に関してですが、この部分を

FIND("]",CELL("filename",A3))

とした場合でも正常に動作は致しますが、万が一、「この数式が入力されているＥｘｃｅｌファイル」が収まっているフォルダーの、フォルダー名の中に、「]」の文字が含まれていた場合には、単に

FIND("]",CELL("filename",A3))

とした場合や、回答:No.2様の様に

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)

とした場合には、正しい抽出結果が得られない恐れがありますので、念のために

FIND("]",CELL("filename",A3),FIND(".xls",CELL("filename",A3)))

としております。

　次に、田中シートのA2セルをコピーして、田中シートのB2～D2の範囲に貼り付けて下さい。
　次に、田中シートのA2～D2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、田中シートのコピーシートを、担当者の人数分だけ複製し、それらのコピーシートの各々のシート名を、各担当者名に変更する事で、全ての担当者のデータを抽出表示するためのシートを作成して下さい。

　以上です。

回答No.4

noname#204879

[No.2補足]へのコメント、
済みませんでした。ステップ９の「次式」を書き漏らしておりました。
=IF($F2="","",INDEX(月度売上表!A:A,$F2))

お礼

ありがとうございました！

制作の参考にさせていただきました！

回答No.3

noname#204879

[No.2回答]へのコメント、

ステップ８に示した式の末尾で改行することを失念しておりました。
次のように訂正ください。

８．セル F2 に次式を入力して、此れを下方にズズーッとドラッグ＆ペースト
　　=IF(ISERROR(SMALL(INDIRECT($F$1),ROW(A1))),"",SMALL(INDIRECT($F$1),ROW(A1)))
９．セル A2 に次式を入力して、此れを右方にズズーッとドラッグ＆ペースト

回答No.2

noname#204879

添付図参照
シート「月度売上表」において、
１．セル F1 から右方に担当名を入力
２．セル F1 に式 =IF($B2=F$1,ROW(),"") を入力して、此れを右方にズズーッとドラッグ＆ペースト
３．Ｆ列以右の２行目を下方にズズーッとドラッグ＆ペースト
４．Ｆ列以右の全行を選択
５．[挿入]→[名前]→[作成]を実行
６．“上端行”だけにチェックを入れて Enterキーを「エイヤッ！」と叩き付け
シート「田中」において、
７．セル F1 に次式を入力
　　=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)
８．セル F2 に次式を入力して、此れを下方にズズーッとドラッグ＆ペースト
　　=IF(ISERROR(SMALL(INDIRECT($F$1),ROW(A1))),"",SMALL(INDIRECT($F$1),ROW(A1)))９．セル A2 に次式を入力して、此れを右方にズズーッとドラッグ＆ペースト
10．範囲 A2:D2 を下方にズズーッとドラッグ＆ペースト
シート「佐藤」もステップ７以降を実行

お礼

ありがとうございました！

補足

回答ありがとうございます。
９のセルA2に次式を～とありますが次式はどちらですか？

回答No.1

tom04

こんにちは！
個人的には、コマンドボタンを配置してクリックすると各Sheetに表示される方法が簡単だと思いますが。

他の方法の一例です。
↓の画像で左側が「月度売上表」Sheetとします。
右側は各Sheetの配置ですが、とりあえず「田中」Sheetで説明します。
Sheet見出しの2番目以降に個人のSheetがあるとして、

Sheet2を選択　→　Shiftキーを押しながら最後のSheet見出しをクリック
これでSheet2以降が作業グループ化されました。
各SheetのE列を作業用の列とします。
E2セルに
=IF(OR(E$1="",月度売上表!B2<>E$1),"",ROW())
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そしてA2セルに
=IF(COUNT($E:$E)<ROW(A1),"",INDEX(月度売上表!A:A,SMALL($E:$E,ROW(A1))))
という数式を入れ、列方向・行方向にオートフィルでコピー！

最後にSheet見出し上で右クリック　→　作業グループ化の解除を行い
各SheetのE1セルに担当者名を入力していきます。
これで各Sheet、画像のような感じで表示されると思います。

※　作業列が目障りであれば非表示にしておきます。

こんなんではどうでしょうか？m（_ _）m

お礼

ありがとうございました！

さんこうにさせていただきました！