[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『別ファイルから特定の項目にかかった時間を日毎に集計する』（湯）
はじめまして、エクセル初心者です。 別ファイルの「管理表」というシートにある個人が各作業にかかった時間を まとめて1つのファイルで日毎に見たいのですが何かいい方法はないでしょうか？
【管理表ファイル】 「作業者名」 1 2 3 4 5 6 7 8…（以下日付が続きます） 作業内容 名称 作業名A 3 1 5 作業内容 名称 作業名B 7 作業内容 名称 作業名A 4 2
【集計ファイル】 作業名A 1 2 3 4 5 6 7 8…（以下日付が続きます） 作業者名 7 1 2 5
というように抽出したいのです。
ちなみに作業の種類がJ列から、行が8始まりで47で終わっています。 1日はLから始まっているので
=SUMIF([ファイル名]管理表!$J$8:$J$47,"作業名",[ファイル名]管理表!$L$8:$L$47) （以下2日はM、3日はNという風に式を続けています）
で一応抽出はできるのですが、作業名がたくさんあるのと 月ごとにファイル名が変わるので、改修していかないといけないのではと 悩んでいます。
OSはWindowsXP、Excel2003です。
何かいい方法がありましたら、教えていただけると幸いです。
話が分かりにくいので、サンプルデータを載せて貰った時に 行列番号も分かるように書いておいて貰うと良いと思うのですが。
あと、「作業者名」ってのはどこでマッチングさせるのですか？
名前は適当に「Aさん」「Bさん」と言った感じで 作業名も適当に「掃除」「梱包」と言った感じで 紛らわしくないようにしてもらえると良いのですが。
＞[ファイル名]管理表! この「ファイル名」に Aさん とかって成ってるのかな？
でも ＞月ごとにファイル名が変わるので ファイル名は 2010年1月 とかって成ってそうですが。。。 あ、2010年1月Aさん とかって成ってるのかな？
(HANA)
HANAさん、ご返答ありがとうございます。説明下手で申し訳ありません。 ご質問いただいた点をふまえ、サンプルを改修させていただきました。
【管理表ファイル】 「Aさん（作業者名）」 1 2 3 4 5 6 7 8…（列番号はLから始まっていて、以下日付が続きます） 作業内容 名称 ファイリング 3 1 5 作業内容 名称 文書作成 7 作業内容 名称 ファイリング 4 2 ↑ 作業名（列J）
このようなファイルがBさん、Cさん…と人数分あります。
【集計ファイル】 ファイリング 1 2 3 4 5 6 7 8…（以下日付が続きます） Aさん 7 1 2 5 Bさん
文書作成
1 2 3 4 5 6 7 8…（ﾈ下日付が続きます）
Bさん 8 2 6 2
Cさん 1 1 4
という風に別ファイルで抽出したいのです。 ファイル名は作業管理表（Aさん）.xlsという名前で、各月ごとに フォルダに分かれて保管されています。 （3月なら3月という名前のフォルダの中に作業管理表（Aさん）.xlsというファイルが入っている状態です）
なので同じ場所に集計ファイルを作るとすると
↓シート名です =SUMIF([作業管理表（Aさん）.xls]管理表!$J$8:$J$47,"ファイリング",[作業管理表（Aさん）.xls]管理表!$L$8:$L$47)
になるのではと思うのですが…。
ファイル名は以前作業管理表3月（Aさん）.xlsという風になっていたので改修したのですが フォルダで分割されていたら結局参照先を変更しなくてはいけませんよね…。 勝手に保存先を変更できないのが悩みどころです；
(湯)
＞フォルダで分割されていたら結局参照先を変更しなくてはいけませんよね…。 そうですね。 これをなんとかするのが良いと思いますが。
それに、SUMIF関数で集計すると ファイルを全て 開いておかないといけないですよね？
何人くらい居るのですか？
(HANA)
上司に説明をして、前月のものを旧フォルダに入れておくことでまとまりました。 ですのでフォルダの場所の問題は大丈夫です。ありがとうございます。
>それに、SUMIF関数で集計すると ファイルを全て >開いておかないといけないですよね？
その通りですね…。集計する人数が100名ほどいるので、SUMIF関数以外で 何かいい方法があればと思っています。 よくわからないけどマクロを使えば簡単なんじゃないの？という提案だけ 上司にされ、丸投げされてしまいました； どうにかひとつひとつファイルを見て手打ちをしなくても時間が表示されるようにしたいのですが その場合は、やはりマクロを使用するのでしょうか？
（湯）
あれ？ 失礼しました。 ＞フォルダで分割されていたら結局参照先を変更しなくてはいけませんよね…。 より 「ファイル（データの有る場所）が分かれていたら駄目だよね」 でした。
エクセルは、データの場所を指定すれば簡単に集計してくれる機能を持っていますが 「データの場所を指定する」のは人が行う事ですから。 データが一つの表にまとまっていれば、ピボットテーブルが利用できると思います。
一人40行程で100名なら 4000行ですね。 まずは一つのシートにまとめる事から考えてみられてはどうでしょう？ マクロは全くやってみられたことは無いですか？
過去ログも見てみられると良いと思います。
(HANA)
HANAさん、ご回答ありがとうございます。 やはりデータの場所指定は人の手で指定しないといけないのですね…
データを一つの表にまとめることはできないので（個人でファイルを作ることが決まっているため） 地道に手作業で入れていくしかないようですね… 私の一存で変更がきかないのが残念です；
一応一人40行程度なのですが、人によっては100行を超えたり 同じ文書作成作業でも依頼元で細かく分かれていて 種類も32社×作業名35種類=1120個になるので 1人あたり1120個種類を指定しないといけません； こうなると、上司に一人一人の作業管理表を見てもらったほうが早いと思います（苦笑）
マクロはまだまったく手をつけていないので、これから勉強していきたいと思います。 過去ログもまだ途中までしか見ていないので、じっくり読ませていただこうと思います。ありがとうございました。
（湯）
見つけてみました。 参考に成ると思います。 [[20080821183744]] 『複数のBookのデータを新たなBookの一つのSheetにａx（さくら）
．．．って衝突したと思ったら、人によって行数が違うんですか。 まぁ、取り敢えず検索してみて下さい。
＞データを一つの表にまとめることはできないので だから、 「この作業だけマクロでやればいいジャン」 と提案をしている所です。
(HANA)
マクロでデータをまとめていく場合 ファイルやデータの状態に付いて確認させてもらいたいのですが。
まず、ファイルの場所は ・△△年度フォルダ＞旧フォルダ＞各月のフォルダ の中でしょうか？ ・フォルダ名は「○月」でしょうか？
ファイルに関しては ・フォルダの中に今回の集計と関係ないファイルが有りますか？ ・ファイル名は「作業管理表（□□）.xls」ですか？ つまり、前から６文字～後ろから５文字の間 の様に特定して名前が分かる。
データに関しては L7～日付が入っていると思います。 J8から作業名が入っていると思います。 ・J7には何か入力が有りますか？また、K7には？ ・このデータの表は何列から始まっていますか？ ＞同じ文書作成作業でも依頼元で細かく分かれていて って事なので、それらの情報が入っている列も有ると思いますが。 また、それらの列の7行目のセルは何か入力が有りますか？無いですか？ 無い場合は項目名を教えて下さい。
実際にコードをつくって行っている内に更に不明点が出てくるかも知れませんが 現在コードを作ろうと思っている段階での確認したい事柄をあげてみました。
(HANA)
HANAさん、過去ログを見つけて下さりありがとうございます。 拝見したところ、似たような事例でしたのでじっくり読んでいきたいと思います。
マクロの提案もありがとうございます。 読解力が足りず申し訳ありません。
ご質問いただいた点についてお答えします。
>まず、ファイルの場所は >・△△年度フォルダ＞旧フォルダ＞各月のフォルダ > の中でしょうか？ >・フォルダ名は「○月」でしょうか？
ファイルの場所は会社のサーバーの中にあるので 会社のサーバ＞作業管理表フォルダ＞2010年フォルダ＞3月フォルダ＞○○（グループ名）作業管理表フォルダ＞作業管理表（□□）.xlsになります。
>ファイルに関しては >・フォルダの中に今回の集計と関係ないファイルが有りますか？ >・ファイル名は「作業管理表（□□）.xls」ですか？ >つまり、前から６文字～後ろから５文字の間 の様に特定して名前が分かる。
フォルダの中には作業管理表（サンプル）.xlsというファイルがありますが 中に入力はないので大丈夫かと思います。 ファイル名は「作業管理表（□□）.xls」です。
>データに関しては >L7～日付が入っていると思います。 >J8から作業名が入っていると思います。 >・J7には何か入力が有りますか？また、K7には？ >・このデータの表は何列から始まっていますか？ >＞同じ文書作成作業でも依頼元で細かく分かれていて >って事なので、それらの情報が入っている列も有ると思いますが。 >また、それらの列の7行目のセルは何か入力が有りますか？無いですか？ >無い場合は項目名を教えて下さい。
L7～日付が、J8から作業名が入っています。 J7には、7行目にその日にやった全ての作業の合計時間「=SUM(L8:L47)」が 入っているため「合計」という入力がしてあります。 K7にはその月にかかった合計の時間「=SUM(L7:AP7)」が入っています。
お返事が遅くなり申し訳ありません。マクロで上手く集計できるよう勉強してきます。
（湯）
もう少し教えて下さい。
ファイルの場所についてですが ＞・・・3月フォルダ＞○○（グループ名）作業管理表フォルダ＞作業管理表（□□）.xls 月のフォルダの中でさらに、グループ毎に分かれている様です。 集計する場合は、どの様に集計するのでしょう？ グループはいくつか有るけど、一つのグループしか集計しない？ Aグループの人も、Bグループの人も 一つの表に集計する？
データに関してですが このデータの表は何列から始まっていますか？また、何列まで有りますか？
それから、それぞれの項目名も教えて下さい。 ＞J8から作業名が入っています。 という事は、J列の項目名は「作業名」ですね？ 元から8行目項目名が入力されている列に関しては不要です。
(HANA)
ファイルの集計ですが、Aグループの人もBグループの人も1つの表に集計します。 ただ、月ごとにAグループ→Bグループに移動する人もいるのですが、大丈夫でしょうか。
>このデータの表は何列から始まっていますか？また、何列まで有りますか？
表自体はA列から始まっています。 時間を入力するところまでで考えるとAP列までですが、最後に備考欄があるのでAQ列まであります。
>それから、それぞれの項目名も教えて下さい。 > ＞J8から作業名が入っています。 > という事は、J列の項目名は「作業名」ですね？
それぞれの列の項目名ですが A列：NO. B列：機種 C列：図面番号 D列：図面名称 E列：SEG F列：種類 G列：作成又は点検 H列：A3換算 I列：点数 J列：集計「=B8&F8&G8&E8」（以下「=B9&F9&G9&E9」…と続きます） ↑この列の項目を、G列を除いた状態（B,F,E）で抽出したいと考えています。 K列：工数「=SUM(L8:AP8)」（以下「=SUM(L9:AP9)」…と続きます）
L列から時間を入力するようになっています。 B列,E列,F列,G列はプルダウンメニューから種類を選ぶことができるようになっています。
上手く説明できず申し訳ありません。よろしくお願いいたします。
（湯）
有り難う御座います。だいぶ分かってきました。
グループはいくつくらい有りますか？
J列は数式が入っているのですね。 表の入力が有る最終行を確認したかったら A列の最終行を確認すれば良いでしょうか？
それとも、A列の番号は多めに入力が有るのでしょうか？ B列やC列の方が良いのかな？ （でも、ここも「未定で空白」とかありそうな？） 複数列での確認が必要な場合は その列でも良いので教えて下さい。
(HANA)
親身に相談にのって下さり本当にありがとうございます。
グループは2グループに分かれています。
>表の入力が有る最終行を確認したかったら >A列の最終行を確認すれば良いでしょうか？
>それとも、A列の番号は多めに入力が有るのでしょうか？ >B列やC列の方が良いのかな？ >（でも、ここも「未定で空白」とかありそうな？）
A列はとりあえずNo.40まで入力があります。 人によってはNo.1しか使わない人もいればNo.100ぐらいまで使う人もいます。 No.40以上使う方は挿入で行を増やしてもらっています。 B列も余分に入力している人がいますので 入力のある最終行を確認するにはC列が妥当なのではないかと思います。
先程マクロのページを読ませていただきましたが、難しそうですね… じっくり読み込んで理解していきたいと思います。
（湯）
上手く動くと良いですが。。。。 こんなコードにしてみました。
'------
Sub まとめる()
Dim GrName As Variant, myGrName As Variant
Dim seDir As String, myDir As String, myFileName As String
Dim wb As Workbook, ws As Worksheet
Dim mxRow As Long, myRow As Long
GrName = Array("Aグループ", "Bグループ") '★グループ名を変更して下さい
With ActiveSheet
If .Parent.Name = ThisWorkbook.Name Then
MsgBox "作業ブックを選択して実行して下さい。"
Exit Sub
End If
If MsgBox("処理を開始します。現在のシートのデータは削除されます。", _
vbOKCancel + vbExclamation) = vbCancel Then
Exit Sub
End If
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = True Then
seDir = .SelectedItems(1)
End If
End With
If Right(seDir, 1) <> "月" Or seDir = "" Then
MsgBox "処理を中止します。"
Exit Sub
End If
.Cells.Clear
'Application.ScreenUpdating = False
For Each myGrName In GrName
myDir = seDir & "\" & myGrName & "\"
myFileName = Dir(myDir & "*.xls")
Do While myFileName <> ""
If myFileName <> "作業管理表（サンプル）.xls" Then
Set wb = Workbooks.Open(myDir & myFileName)
Set ws = wb.Sheets("管理表")
mxRow = ws.Range("C" & Rows.Count).End(xlUp).Row
If mxRow > 7 Then
If myRow = 0 Then
.Range("C1").Resize(1, 43).Value = ws.Range("A7:AQ7").Value
End If
.Range("A" & myRow + 2).Resize(mxRow - 7, 1).Value = myGrName
.Range("B" & myRow + 2).Resize(mxRow - 7, 1).Value = Mid(myFileName, 7, Len(myFileName) - 11)
.Range("C" & myRow + 2).Resize(mxRow - 7, 43).Value = ws.Range("A8:AQ" & mxRow).Value
myRow = myRow + mxRow - 7
End If
wb.Close
End If
myFileName = Dir
Loop
Next
.Range("A1:M1").Value = _
Array("グループ名", "作業者名", "NO.", "機種", "図面番号", "図面名称", "SEG", _
"種類", "作成又は点検", "A3換算", "点数", "集計", "工数")
'Application.ScreenUpdating = True
End With
End Sub
'------
新しいブックを一つ用意して、標準モジュールにコードを貼り付けます。 コード内の「'★グループ名を変更して下さい」の所は、グループのフォルダ名と 同じフォルダ名に変更して下さい。
もう一つ別のブックを用意して、マクロを実行します。 マクロと同じブックのシートが選択されている状態ではマクロは実行されません。 別のブックのシートをアクティブにして実行して下さい。 フォルダを選択するダイアログが開くので 会社のサーバ＞作業管理表フォルダ＞2010年フォルダ＞3月フォルダ を選んで下さい。 （因みに 3月フォルダのフォルダ名は「3月」と書いてある予定です。）
現在選択されているシートに、データを集めます。 「Application.ScreenUpdating」をコメント化しています。 これを有効にして処理時間が短くなる様なら有効にして貰うと良いかもしれません。 但し、ちょっと止まった感じに成ると思うので 不安に成るかもしれません。 （私は ちらちらするのが好きです。笑）
想定と違う事が有ると、途端にエラーが出ます。 エラーメッセージと、デバッグボタンを押したときに黄色くなる行を教えて下さい。
(HANA)
HANAさん、コードありがとうございます！！ 勉強不足でマクロについてまだ探っている状態なのですが 週明けに出社したときに実行させていただきたいと思います。
またご報告にあがると思いますので、よろしくお願いいたします。
（湯）
HANAさん、おはようございます。 お返事が遅れましたが、今日マクロを実行したところ エラーが出ず無事に出力されました。 おかげでいちいち開く手間が省けました。ありがとうございます！ これで時間を短縮できます。
ただ出力されたときに
【出力されたデータ】 グループ名 作業者名 No. 機種 図面番号 図面名称 SEG 種類 作成または点検 A3換算 点数 集計 工数 （以下合計の時間） ○○グループ Aさん 1 A 00000 PIN 001 図面 作成 1 1 A図面作成001 （合計時間）
こうなったのですが「工数」の横の合計時間が一番上のファイル（例えばAさん）のみの合計になるのですが これは仕様なんでしょうか？
あとはこのデータにするだけなのですが 【集計ファイル】 ファイリング 1 2 3 4 5 6 7 8…（以下日付が続きます） Aさん 7 1 2 5 Bさん
文書作成
1 2 3 4 5 6 7 8…（ﾈ下日付が続きます）
Bさん 8 2 6 2
Cさん 1 1 4
オートフィルタをかけて集計する予定なのですが、何か他に効率のいい集計方法はありますでしょうか？ 色々聞いて申し訳ありません。よろしくお願いいたします。 （湯）
先に元ファイルの状態を教えて貰いたいのですが
＞「工数」の横の合計時間が一番上のファイル（例えばAさん）のみの合計になるのですが これはまとめたシートの1行目の項目名の事でしょうか？
ちょっと読み違えていたようです。 L列以降の7行目は ＞【管理表ファイル】 ＞ 1 2 3 4 5 6 7 8…（列番号はLから始まっていて、以下日付が続きます） と書いてある様に日付が入っているのかと思っていましたが 日毎の合計をする数式が入っているのですね？ =SUM(L8:L ・・・) と言った。
では、項目はどの行に入っているのでしょう？ L列以前も入力が無い様ですし、列番号だけでそれぞれを入力しているとは思えないのですが。
レイアウトを載せて下さる時に [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [1] ファイリング [2] 1 2 3 4 5 6 7 8 … （以下日付が続きます） [3] Aさん 7 1 2 5 [4] Bさん [5] こんな感じで行列番号をつけて載せてもらえると 何がどこにあるのか 見て分かりやすいのですが。
集計に関してですが 「ファイリング」「文書作成」などはどの項目になりますか？ 作成または点検 の列でしょうか？
(HANA)
HANAさん、早速のお返事ありがとうございます。 こちらの説明間違いでした。申し訳ありません。
L列の4行目に日付が入力されています。以前ご質問いただいたときに誤ってL7と回答した私の ミスでした。大変申し訳ありません。
管理表ファイルは
[A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M]
[1] 3月 作業実績 工数 [2] 氏名 Aさん
[3] NO. 機種 図面番号 図面名称 SEG 種類 作成/点検 A3換算 点数 集計 工数 3月
[4] 1 2 3 4 5 6 7 8 … （以下日付が続きます）
[5] 月 火 水 木 金 土 日 月 … （以下曜日が続きます）
[6] 休暇区分
[7] 合計（J7まで結合） =SUM(L7:AP7) =SUM(L8:L47)…
[8] 1 A A社図面作成 PIN 001 図面 作成 1 1 A図面作成001 =B8&F8&G8&E8
[9] 2 =B9&F9&G9&E9
[10] 3
[11] 4
見づらいですがこんな感じです。
そして…申し訳ないのですが、先程上司に言われ、集計ファイルを
[A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [1] Aさん [2] 1 2 3 4 5 6 7 8 … （以下日付が続きます） [3] ファイリング 7 1 2 5 [4] 文書作成 [5]
の形にして欲しいと言われました；変更になってしまって申し訳ありません；
>集計に関してですが >「ファイリング」「文書作成」などはどの項目になりますか？ >作成または点検 の列でしょうか？
「ファイリング」「文書作成」などは【管理表ファイル】のF列「種類」になります。
色々と変更点が出てきてしまってすいません。よろしくお願いいたします。
（湯）
先に、データをまとめるコードですが これで動きますか？ 未テストコードなので、変なところが有ったら教えて下さい。
'------
Sub まとめる02()
Dim GrName As Variant, myGrName As Variant
Dim seDir As String, myDir As String, myFileName As String
Dim wb As Workbook, ws As Worksheet
Dim mxRow As Long, myRow As Long
GrName = Array("Aグループ", "Bグループ") '★グループ名を変更して下さい
With ActiveSheet
If .Parent.Name = ThisWorkbook.Name Then
MsgBox "作業ブックを選択して実行して下さい。"
Exit Sub
End If
If MsgBox("処理を開始します。現在のシートのデータは削除されます。", _
vbOKCancel + vbExclamation) = vbCancel Then
Exit Sub
End If
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = True Then
seDir = .SelectedItems(1)
End If
End With
If Right(seDir, 1) <> "月" Or seDir = "" Then
MsgBox "処理を中止します。"
Exit Sub
End If
.Cells.Clear
'Application.ScreenUpdating = False
For Each myGrName In GrName
myDir = seDir & "\" & myGrName & "\"
myFileName = Dir(myDir & "*.xls")
Do While myFileName <> ""
If myFileName <> "作業管理表（サンプル）.xls" Then
Set wb = Workbooks.Open(myDir & myFileName)
Set ws = wb.Sheets("管理表")
mxRow = ws.Range("C" & Rows.Count).End(xlUp).Row
If mxRow > 7 Then
If myRow = 0 Then
.Range("A1:C1").Value = Array("月", "グループ名", "作業者名")
.Range("D1:N1").Value = ws.Range("A3:L3").Value
.Range("O1").Resize(1, 32).Value = ws.Range("L4:AQ4").Value
End If
.Range("A" & myRow + 2).Resize(mxRow - 7, 1).Value = ws.Range("A1").Value
.Range("B" & myRow + 2).Resize(mxRow - 7, 1).Value = myGrName
.Range("C" & myRow + 2).Resize(mxRow - 7, 1).Value = ws.Range("B2").Value
.Range("D" & myRow + 2).Resize(mxRow - 7, 43).Value = ws.Range("A8:AQ" & mxRow).Value
myRow = myRow + mxRow - 7
End If
wb.Close
End If
myFileName = Dir
Loop
Next
'Application.ScreenUpdating = True
End With
End Sub
'------
(HANA)
HANAさん、早速のご返信ありがとうございます。
綺麗に表ができました。ありがとうございます。 ただやはり
[A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P]
[1] 月 ｸﾞﾙｰﾌﾟ名 名前 No 機種 図面番号 図面名称 SEG 種類 作成/点検 A3換算 点数 集計 工数
O1列に最初の（例えばAさん）のファイルの時間の合計が表示されてしまいます。 多分ここに日付が入るのではと思うのですが… ご確認いただけると嬉しいです。
（湯）
あ、間違えました。。。
＞.Range("O1").Resize(1, 32).Value = ws.Range("L7:AQ7").Value
ここは、7行目じゃなくて、4行目でしたね。 ~~~↓~~~~（正）
.Range("O1").Resize(1, 32).Value = ws.Range("L4:AQ4").Value
上のコードは直接変更します。 まとめる01→まとめる02に名前を変更します。 この１行を直してやってみて下さい。
(HANA)
直りました！ありがとうございます。
あとはこのデータをまとめて集計ファイルを作るだけなのですが また上司の言うことがコロコロ変わって困っています。 とりあえず今日は調整をして、最終的な集計ファイルの形を決めていきたいと思います。
上司が言うには、最終的に
[A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L]
[1] ファイリング
[2] 1 2 3 4 5 6 7 1週間の合計 1週間のA3換算合計 時間/A3
[3] Aさん 7 1 2 5 =SUM(B3:H3) （まとめデータから抽出？） =I3/J3
[4] Bさん 2 4 1 5 1 =SUM(B4:H4) =I4/J4
[5]
[6] 作業者全員の合計時間 =SUM(B3:H4)
[7] 人数 2
（以下1週間ごとに同じものが続きます）
↑のようなデータと、このデータを使った
[A] [B] [C] [D] [E]
[1] Aさん
[2] 作業内容 時間/A3
[3] 第1週目 第2週目 第3週目 第4週目
[4] ファイリング 上のファイルのK3と同じ
[5] 文書作成
というファイルが欲しいようなのです。
個人的には別ファイルにせず、別シートにすれば良いのではと思うのですが
やはり別シートにしたほうが簡単でしょうか？
何度も色々聞いてしまって申し訳ありません。 本当に助かってます。ありがとうございます。
（湯）
＞また上司の言うことがコロコロ変わって困っています。 それが上司の仕事ですから仕方ないですね。 キットタノシンデイルンデスヨ。
まず、一つ作ってみましょう。 ピボットテーブルで集計してみます。 面倒ですが。。。
まず、一行目は表示形式を「d」にしておいて下さい。 元から1,2,3・・・と成っているならそのままで良いですが。 A1セルをアクティブにした状態で メニュー・データ(D)→ピボットテーブルと・・・(P) と進み、ウィザードを開始します。
後はこちらをご参考に。。。次へ → 次へ → 完了 と進みます。 http://www.excel.studio-kazu.jp/lib/e2d/e2d.html
ページのフィールド に 「種類」 行のフィールド に 「作業者名」 データアイテム に 「1」「2」「3」・・・と日付の部分を全部 入れます。
すると、
[作業者名] [データ] [ 合計 ]
Aさん 合計 / 1
合計 / 2
:
Bさん 合計 / 1
合計 / 2
:
って感じのが出来ると思うので、「データ」のセルを「合計」のセルの上に
ドラッグ＆ドロップして下さい。
[データ]
[作業者名] 合計 / 1 合計 / 2 ・・・・
Aさん
Bさん
という並びに変わります。
B1セルに (すべて)▼ と成っていますが ここで一つだけチェックをつけて貰うと その作業だけの集計に成ります。
(HANA)
ピボットテーブルも無事に完了しました。 色々丁寧に教えてくださり本当にありがとうございます！大感謝です。
＞キットタノシンデイルンデスヨ。
そうかもしれません（笑）
これで来月から時間がかなり削減できますので これを機にエクセルの勉強をもっとしてみようと思います。
HANAさん、ありがとうございました！！
（湯）
あ。。。それで良かったですか？ でしたら、そう言う事で＾＾
現在元のリストが 縦に名前等、横に日付が入っています。 つまり、縦横の項目を確認して特定のデータを見つける。 エクセルで扱いやすい表は、横に項目が有って縦にデータが並んでいる [作業者名][種類] [日付][時間] ←1行目に項目があって Aさん ファイリング 1 1 ←2行目からデータ こんな感じの表です。
すると、日付でグループ化などが行えるように成ります。
因みに、ピボットテーブルの範囲が現在 データがある範囲に成っていると思います。 完成したピボットテーブルをアクティブにした状態で もう一度ウィザードを表示させ [ 戻る ] で、2/3 まで戻り 列単位での参照に変えておくのが良いかもしれません。
データ数が増えたときに範囲の変更をしなくて良くなるので。
(HANA)
上司の希望する項目を出すためにピボットテーブルの
ページのフィールド に 「種類」「SEG」「機種」 行のフィールド に 「作業者名」 データアイテム に 「A3換算」「1」「2」「3」・・・と日付の部分を全部
を入れれば欲しい情報がすぐに見れそうです。 これで運用していこうと思います。
>因みに、ピボットテーブルの範囲が現在 データがある範囲に成っていると思います。 >完成したピボットテーブルをアクティブにした状態で >もう一度ウィザードを表示させ [ 戻る ] で、2/3 まで戻り >列単位での参照に変えておくのが良いかもしれません。
なるほど、了解しました。いじって変更しておきます。
なにからなにまでお世話になりました。ありがとうございました。
（湯）
ちょっと思ったのですが GETPIVOTDATA関数と組み合わせると もう少し欲しい情報が載った表が 簡単に作れるように成るかもしれません。
元データの表と ピボットテーブルの表と そこから欲しい情報を抜き出した表（ここに関数で表示） の三つの表が出来るイメージです。
研究してみてもらっても良いかもしれません。
(HANA)
再び質問させていただきます。
上にあるマクロ
'------
Sub まとめる02()
Dim GrName As Variant, myGrName As Variant
Dim seDir As String, myDir As String, myFileName As String
Dim wb As Workbook, ws As Worksheet
Dim mxRow As Long, myRow As Long
GrName = Array("Aグループ", "Bグループ") '★グループ名を変更して下さい
With ActiveSheet
If .Parent.Name = ThisWorkbook.Name Then
MsgBox "作業ブックを選択して実行して下さい。"
Exit Sub
End If
If MsgBox("処理を開始します。現在のシートのデータは削除されます。", _
vbOKCancel + vbExclamation) = vbCancel Then
Exit Sub
End If
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = True Then
seDir = .SelectedItems(1)
End If
End With
If Right(seDir, 1) <> "月" Or seDir = "" Then
MsgBox "処理を中止します。"
Exit Sub
End If
.Cells.Clear
'Application.ScreenUpdating = False
For Each myGrName In GrName
myDir = seDir & "\" & myGrName & "\"
myFileName = Dir(myDir & "*.xls")
Do While myFileName <> ""
If myFileName <> "作業管理表（サンプル）.xls" Then
Set wb = Workbooks.Open(myDir & myFileName)
Set ws = wb.Sheets("管理表")
mxRow = ws.Range("C" & Rows.Count).End(xlUp).Row
If mxRow > 7 Then
If myRow = 0 Then
.Range("A1:C1").Value = Array("月", "グループ名", "作業者名")
.Range("D1:N1").Value = ws.Range("A3:L3").Value
.Range("O1").Resize(1, 32).Value = ws.Range("L4:AQ4").Value
End If
.Range("A" & myRow + 2).Resize(mxRow - 7, 1).Value = ws.Range("A1").Value
.Range("B" & myRow + 2).Resize(mxRow - 7, 1).Value = myGrName
.Range("C" & myRow + 2).Resize(mxRow - 7, 1).Value = ws.Range("B2").Value
.Range("D" & myRow + 2).Resize(mxRow - 7, 43).Value = ws.Range("A8:AQ" & mxRow).Value
myRow = myRow + mxRow - 7
End If
wb.Close
End If
myFileName = Dir
Loop
Next
'Application.ScreenUpdating = True
End With
End Sub
'------
を使い、週毎（例えば22日～28日までのみの結果を出す場合抽出範囲を変えると思うのですが 変更をする箇所は
Range("O1").Resize(1, 32).Value = ws.Range("L4:AQ4").Value
.Range("D" & myRow + 2).Resize(mxRow - 7, 43).Value = ws.Range("A8:AQ" & mxRow).Value
の2箇所を変更するのでしょうか？（ちなみに22～28までのデータはAH～AMに入っています。）
全て抽出してしまうとピボットテーブルを使ったときにその月の合計のA3換算を 持ってきてしまうので悩んでいます…
もしくは今のままのデータ抽出方法で、ピボットテーブルで表を作成するときに 22～28日までのA3換算の合計が出せるのでしょうか？ ちなみに今は全て抽出後、オートフィルタをかけて空白を消しています。
何度も質問してしまい申し訳ありませんが、よろしくお願いいたします。
（湯）
えっと。。。それがその上の書き込みの ＞GETPIVOTDATA関数と組み合わせると ＞もう少し欲しい情報が載った表が ＞簡単に作れるように成るかもしれません。 の部分に成ってくると思いますが。
元々無理が有る表なので、段階を追って 作成して行ってもらうのが良いのではないかと思います。
勿論、コードの変更をしたり ピボットテーブルを変更するのも一つの方法とは思いますが。 考えて居られる程良い結果には成らないと思います。
試しに、必要な部分だけを別シートに貼り付けて その範囲でピボットテーブルを作成してみられてはどうでしょう？ 現在、日付部分は項目に成っていますので データ（日付範囲）が変わると ピボットテーブルの項目の入れ直しが 必要に成ってくると思います。 そして、範囲を広めに設定していた場合その都度集計方法の変更が必要。 逆に、範囲をぴったりにしていた場合は、データが増えた場合に変更が必要。
他の関数や、一般機能との組合せも必要に成ってくると思いますが GETPIVOTDATA関数や、INDEX関数 等 調べてみて貰うと良いのではないかと思います。
(HANA)
以前は大変お世話になりました。 作成していただいたマクロのおかげで毎週の集計がとてもスムーズになりました。 再度質問させていただきたいのですが 項目が増え、列が追加になった場合、変更する箇所は
.Range("A1:C1").Value = Array("月", "グループ名", "作業者名")
.Range("D1:N1").Value = ws.Range("A3:L3").Value
.Range("O1").Resize(1, 32).Value = ws.Range("L4:AQ4").Value
End If
.Range("A" & myRow + 2).Resize(mxRow - 7, 1).Value = ws.Range("A1").Value
.Range("B" & myRow + 2).Resize(mxRow - 7, 1).Value = myGrName
.Range("C" & myRow + 2).Resize(mxRow - 7, 1).Value = ws.Range("B2").Value
.Range("D" & myRow + 2).Resize(mxRow - 7, 43).Value = ws.Range("A8:AQ" & mxRow).Value
myRow = myRow + mxRow - 7
End If
このあたりにある
.Range("D1:N1").Value = ws.Range("A3:L3").Value
↑ココ ↑ココ
.Range("O1").Resize(1, 32).Value = ws.Range("L4:AQ4").Value
↑ココ
.Range("D" & myRow + 2).Resize(mxRow - 7, 43).Value = ws.Range("A8:AQ" & mxRow).Value
↑ココ
あたりを変更することになるのでしょうか？ 申し訳ないですが列が増えたときに変更する箇所を教えていただきたいです。 （湯）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.