EXCEL 元データを更新したら別シートも更新

シート１に所属・役職・氏名のデータがあります（元データ）。
その他に所属別に分かれたシートが複数あります（データは元データの役職・氏名）。

シート1のデータを更新したら、所属別に分かれたシートの内容も自動的に更新されるというようなことはできるでしょうか？

シート1の更新は人数の増減もあるし、所属、役職が変わるケースも出ててくるし、データの位置も変わるので＝’sheet１’A１のような式を入れれば良いというようなものではありません。

No.7 ベストアンサー 回答者： mu2011 回答日時： 2011/10/26 13:10

NO5です。

>実際は、Sheet2のデータ（式を入力する場所）がD21から始まります。
　⇒数式の変更は不要ですのでD21に数式を入力して下さい。

この回答へのお礼

再度回答ありがとうございます。
早速やってみましたが上手くいかず、結果が#VALUE!となります。

因みにSheet2のA2～Q20、A49～Q55には他の文字データが入っており、セルを結合しているところもあります。
数式を入力したいのはD（役職列）21～E（氏名列）47です。
EXCELのバージョンは2003です。

お礼日時：2011/10/26 13:57

No.6 回答者： tom04 回答日時： 2011/10/20 21:22

こんばんは！

VBAでの一例です。

↓の画像で左側がSheet1で右側がとりあえず「営業」Sheetだけを表示。
Sheet2以降に所属名のSheet名で所属部署数のSheet数があるという前提です。

Alt＋F11キー　→　画面左下の「This Workbook」をダブルクリック　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub 所属Sheetに振り分け() 'この行から
Dim i, j, k As Long
Application.ScreenUpdating = False
For k = 2 To Worksheets.Count
j = Worksheets(k).Cells(Rows.Count, 1).End(xlUp).Row
If j > 1 Then
Range(Worksheets(k).Cells(2, 1), Worksheets(k).Cells(j, 2)).ClearContents
End If
For i = 2 To Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
If Worksheets(1).Cells(i, 1) = Worksheets(k).Name Then
With Worksheets(k).Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = Worksheets(1).Cells(i, 2)
.Offset(, 1) = Worksheets(1).Cells(i, 3)
End With
End If
Next i
Next k
Application.ScreenUpdating = True
End Sub 'この行まで

※　Sheet1のデータが変わるたびにマクロを実行してください。
（各Sheetに古いデータがあっても構いません）

以上、参考になれば良いのですが・・・m(_ _)m

この回答へのお礼

回答ありがとうございます。お礼が遅くなってすいません。
回答どおりやれば上手くいくのですが、実際は、各シートにフォーマットが定めれれており、私の低いマクロ能力では応用が利きませんでした。

お礼日時：2011/10/26 11:02

No.5 回答者： mu2011 回答日時： 2011/10/20 17:04

NO3です。

前回の（１）の長い判り辛い数式例です。
仮に元データはSheet1のA:C列に所属・役職・氏名、見出し行あり、
（１）Sheet2のA1に所属名は入力
（２）Sheet2のA2に以下の数式を入力、入力完了時にshift＋ctrl＋enterキーを同時押下、
　　　縦横に必要分コピー
　　　=IF(COUNTIF(Sheet1!$A:$A,$A$1)>=ROW(A1),INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$2:$A$1000=$A$1,ROW(Sheet1!$A$2:$A$1000),9999),ROW(A1))),"")
後はこのシートを所属数コピーして、各シートのA1を所属名で変更して下さい。　　　

この回答へのお礼

回答ありがとうございます。お礼が遅くなってすいません。
回答どおりやれば上手くいくのですが、実際は、Sheet2のデータ（式を入力する場所）がD21から始まります。
ROW（A1）のところをROW（D20）としても上手くいきません（答えが空欄になる）。
どこを変えればいいのでしょうか？

お礼日時：2011/10/26 10:11

No.4 回答者： CoalTar 回答日時： 2011/10/20 16:47

バージョンがないので2003として回答

案１
シート1のデータそのものは使えないか？
なぜなら、オートフィルタで抽出するだけだと思うから

案２
マクロなら、オートフィルタやフィルタオプションの設定を使って記録してみるとよいと思う。

案３
その他、MSクエリを使えばマクロは不要だろうけど、2007/2010でも使えるかは不明だし、説明も厄介。

案４ 数式
1行目C列～ 役職を横に並べた
C2セル
=SUM(C1,($A2=C$1)*1)
右へ下へオートフィル

G1セル 対象の役職入力欄(縦書きに意味はない)
G3セル 作業列の対象列を探す
=MATCH($G$1,$C$1:$E$1,0)
G5セル その役職の人数
=MAX(INDEX(C:E,,G3))

H2セル 対象の人の行番号
=IF(ROW()-1>$G$5,"",MATCH(ROW()-1,INDEX($C:$E,,$G$3),0))
下へオートフィル
I2セル
=IF($H2="","",INDEX(A:A,$H2))
右へ下へオートフィル

罫線などは 条件付き書式で整える
G列からJ列まで切り取って別シートへ貼り付け

この回答へのお礼

回答ありがとうございます。お礼が遅くなってすいません。
案１は、シートにフォーマットが定めれれており不可。
案２は、私も考えましたが保留中です（部署名もころころ変わりメンテナンス面から難）。
案４は、上司からコピペ作業は避けるよう言われているため、各シートにダイレクトに出力できるよう試してみます。

お礼日時：2011/10/26 11:32

No.3 回答者： mu2011 回答日時： 2011/10/20 16:33

>シート1のデータを更新したら、所属別に分かれたシートの内容も自動的に更新されると

>いうようなことはできるでしょうか？
　⇒可能です。
　　（１）長く判り辛い配列数式⇒処理が重たい、リアル表示が可能
　　（２）イベント対応マクロ（ＶＢＡ）⇒マクロ知識が必要、リアル表示が可能
　　（３）「フィルタオプションの設定」⇒エクセル既存操作で可能、リアル表示できない
　　お勧めは（３）が容易です、自動反映はできないが如何でしょうか。

この回答へのお礼

回答ありがとうございます。
私も（３）を提案したんですが上司に否決され困っていたところです。
（１）、（２）の具体的方法を教えていただけるとありがたいのですが・・・

お礼日時：2011/10/20 16:45

No.2 回答者： noname#158634 回答日時： 2011/10/20 16:09

シート1には常に全ての元データがあることに変化は無いんですよね？

それならVLOOKUP使えばいいんじゃないですか？

この回答へのお礼

回答ありがとうございます。
具体的にはどうやるのでしょうか？
VLOOKUPではピンときません。

お礼日時：2011/10/20 16:35

No.1 回答者： yasuto07 回答日時： 2011/10/20 16:07

厚さ３－５センチある、マクロをしっかり読み解いて、マクロを組めれば可能です。

うちの会社で、使っているエクセルの表は、退職した、職員が作っていきました。

元表から、名前を読み込んで、違う表に、表示が出来てきます。

この回答へのお礼

回答ありがとうございます。
具体的にはどうやるのでしょうか？
コードを教えてくれるとありがたいのですが・・・

お礼日時：2011/10/20 16:29