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

質問

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

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

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

回答No.7

mu2011 · ベストアンサー

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

お礼

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

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

回答No.6

tom04

こんばんは！
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

お礼

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

回答No.5

mu2011

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）としても上手くいきません（答えが空欄になる）。
どこを変えればいいのでしょうか？

回答No.4

某HN クロメート(Chromate)（@CoalTar）

バージョンがないので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列まで切り取って別シートへ貼り付け

お礼

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

回答No.3

mu2011

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

お礼

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

回答No.2

noname#158634

シート1には常に全ての元データがあることに変化は無いんですよね？
それならVLOOKUP使えばいいんじゃないですか？

お礼

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

回答No.1

yasuto07

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

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

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

お礼

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