エクセル2010 別シートへのデータ抽出および元データ更新時に自動更新するには？

エクセル2010を使用しています。

元データはSheet１（データが更新され、量が多いです。）

部署　品名　型式　数量　単価　合計
ああ　AA　aa　　1 100 100
いい　BB　ad　　2　200　400
うう　BB　au　　11 100 1100
ああ　CA　ki　　2　100　　200
うう　CD　do　　5　300　1500　　（合計の部分には数式が入っています。）
↓（データが続いていきます）

抽出Sheetは各部署ごとにシートを作成、元データが更新されたら抽出Sheetも更新したい。

フィルターでやった場合、更新されたデータ反映のさせ方がわかりません。
プログラムでの場合は、言葉の意味が分からないので、注釈を入れていただけたら助かります。
作業が進まず困っていますので、どうぞよろしくお願いいたします。

No.3 ベストアンサー 回答者： tom04 回答日時： 2017/07/18 21:04

こんばんは！

VBAでの一例です。
元データはSheet1にあるとします。
標準モジュールにしてください。

Sub Sample1()
Dim i As Long, k As Long, sN As String
Dim wS As Worksheet, sSh As Worksheet, myFlg As Boolean
Application.ScreenUpdating = False
Worksheets.Add after:=Worksheets(Worksheets.Count)
Set sSh = ActiveSheet
With Worksheets("Sheet1")
.Range("A:A").AdvancedFilter Action:=xlFilterCopy, copytorange:=sSh.Range("A1"), unique:=True
For i = 2 To sSh.Cells(Rows.Count, "A").End(xlUp).Row
sN = sSh.Cells(i, "A")
For k = 2 To Worksheets.Count - 1
If Worksheets(k).Name = sN Then
myFlg = True
Exit For
End If
Next k
If myFlg = False Then
Worksheets.Add after:=Worksheets(k - 1)
ActiveSheet.Name = sN
End If
Set wS = Worksheets(sN)
wS.Cells.Clear
.Range("A1").AutoFilter field:=1, Criteria1:=sN
.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy wS.Range("A1")
wS.Columns.AutoFit
wS.Move after:=Worksheets(i - 1)
myFlg = False
Next i
.AutoFilterMode = False
Application.DisplayAlerts = False
Worksheets(Worksheets.Count).Delete
Application.DisplayAlerts = True
End With
Application.ScreenUpdating = True
MsgBox "完了"
End Sub

関数でないのでデータ変更があるたびにマクロを実行する必要があります。

※　コードの細かい説明は割愛します。
流れとしては
①　作業用Sheetを追加
②　Sheet1のA列データを重複なしに作業用SheetのA列にコピー＆ペースト
③　各「部署」名Sheetが存在するかどうか確認する
④　部署名のシートがない場合はSheetを追加し、シート名を「部署」名にする
⑤　作業用Sheetのデータ順にSheet1のA列をキーにフィルタをかける
④　フィルタで非表示になっていデータのみを各部署名のSheetにコピー＆ペースト

といった流れのコードにしています。m(_ _)m

この回答へのお礼

tom04さん
ご回答ありがとうございます。
自宅のパソコンで早速試しました。
求めていた答えにたどり着けました＼(^o^)／
あとは、職場のパソコンで実働してみます。うまくいくことを願い。
またご報告させていただきます。まずは、ご連絡まで。

お礼日時：2017/07/18 21:35

No.4 回答者： d-q-t-p 回答日時： 2017/07/19 12:10

部署別にシートを分けるより抽出用のシートを１つだけ作った方が

いいと思いますけど。マクロでの案は出ているのでデータベースク
エリでの抽出方法を載せておきます。

Sheet2の A2に部署名を入れて B:F列に抽出されるようにします。

[データ]→[外部データの取り込み]の[その他のデータ ソース]
→[Microsoft Query]
[データ ソースの選択]から「Excel Files*」を選択し[OK]
参照しているファイルを選択して[OK]
名前定義したテーブル「データ範囲」を選択して「>」
→[次へ]→[次へ]→[次へ]→[完了]

[データのインポート]で データを返す先を指定して
[プロパティ]
→「定義」タブの[コマンド文字列]に

SELECT 品名, 型式, 数量, 単価, 合計
FROM [Sheet1$A:F]
WHERE 部署 = ?

と入力して[OK]→[OK]
するとパラメータを訊いてきます。

[パラメータ値の入力]で[パラメーター1]に B2セルを指定し
[この値/参照を今後の更新に使用する]にチェック
→[この値が変わるときに自動的に更新する]にチェック
→[OK]

以上。

この回答へのお礼

d-q-t-pさん
ご回答ありがとうございますm(__)m

私の理解力ではどうも上手くいかず出来ませんでした。
申し訳ありません。ありがとうございました

お礼日時：2017/07/20 20:48

No.2 回答者： mike_g 回答日時： 2017/07/18 18:21

添付図参照

名前が部署名の(例[ああ])のシートにおいて、
次式を入力したセル A2 を右方および下方にズズーッとオートフィル
=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A:$A=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99),ROW($A:$A),""),ROW(A1))),"")
【お断り】上式は必ず配列数式として入力のこと

この回答へのお礼

mike_gさん
ご回答ありがとうございます。
申し訳ありません。
丁寧に書いていただいたのに、うまく作動させることができませんでした。

書いた部分が表記できるようでしたらお願いいたします。

また、元データが追加された場合（行は100以上あります）はどの用意対処したらよいのでしょうか？

お礼日時：2017/07/18 20:37

No.1 回答者： angkor_h 回答日時： 2017/07/18 16:23

抽出シートの統計が関数利用であれば、

「自動計算」となっている場合、で、
データー側と統計側が同時に開かれている場合、は、
データーの変更は即座に統計側も更新されます。

「フィルターでやった場合」という詳細がよく解りませんが、
フィルターで用いたキーを使った関数で統計すればよいとおみます。

この回答へのお礼

angkor_hさん　ご回答ありがとうございます。

フィルターで部署ごとに別シートにデータを抽出を試みたのですが、
一度フィルターでデータ抽出した後に、追加データを再フィルターしてみたらデータの抽出ができませんでした。

統計はする必要はなく、追加されるたびにデータがそれぞれのシートに反映してほしいのです。

お礼日時：2017/07/18 20:22