Results of 1 - 1 of about 1385 for ������������ #N/A (0.016 sec.)
- [[20100722103416]]
- #score: 6930
- @digest: fbc006d80ca23b6fe9cd05fb1a28bd97
- @id: 50590
- @mdate: 2010-08-20T07:54:14Z
- @size: 81602
- @type: text/plain
- #keywords: 度_ (961605), 業管 (728612), 当a (383347), 担a (324222), 曜_ (274112), 週_ (264321), 月度 (258412), 数管 (244151), 度¥ (220389), 就業 (212526), 目月 (170433), 表7 (135064), 理8 (135064), ダ│ (130329), 細'! (128951), 〜¥ (114843), 日- (103547), 理表 (100130), 工数 (92153), ト¥ (89327), 週目 (89187), 細!$ (88706), 表¥ (76758), 新処 (71168), ル¥ (64849), 目金 (53424), 別フ (51261), 担当 (50100), 管理 (49429), コタ (43740), 明細 (42694), 金曜 (39908)
- 『vlookup関数の式の変更について』（sachi）
1ヶ月分の就業管理表に別フォルダの別ファイルから値をvlookupで抽出しています。 1日分に14列、記入欄があり、それぞれ別ファイルの列番号 4,5,7,8,11,12,13,14,15,16,17,18,19,20の値を順に抽出します。 別ファイルには月〜金曜日のデータが月曜日F:Y 火曜日AB:AU 水曜日AX:BQ 木曜日BT:CM 金曜日CP:DIの範囲に5週間分入力できるようになっていて、1週目から順に 6,25,44,63,82行目の値を参照します。 式は以下のようになっています。 =IF(AND($T8='¥¥****¥****¥[*月度_担当A.xls]明細 '!$F$36), VLOOKUP($T8,'¥¥****¥****¥[*月度_担当A.xls]明細 '!$F$36:$Y$36,4,0)) 就業管理表のT列に日付が入力されていて、*月度_担当Aという別ファイルの F,AB,AX,BT,CPセルにも日付が入っているので、この日付をキーにvlookupで値を 抽出しています。 別ファイルはフォルダ名とファイル名が月ごとに変化し、参照範囲も曜日によって 変わるため、毎月式の変更をしています。 （ちなみに就業管理表と別ファイルの対象期間が締め日の都合で違うため、 就業管理表には2ヶ月分の別ファイルから値を抽出している状態です。） 就業管理表と別ファイルは個人別になっていて複数存在し、現在は1人目のファイルの 式を変更した後に、置換でファイル名を変更しているのですが、毎月の式の変更を もう少し簡単にできる方法がないかと思い質問させて頂きました。 よろしくお願いします。 Excel2000 Windows XP ←行頭を半角スペースで始めると書き込みの改行位置が反映されます。 ---- こんにちは。 例えばですが、1年分の就業管理表を1つのブックにまとめるのは、どうでしょうか。 そうすれば、年1回の式変更で済むと思いますが。 それと、式は具体的に、どのような変更を行っているのでしょうか。 規則性のある変更なら、何らかの対応ができるかもしれませんよ。 （コタ） 就業管理表をいじるのが難しければ、別ファイルと就業管理表の間に、1年分の データをためこむ中間ファイルを作ってもいいかもしれません。 ただし、毎月のデータ更新は、両ファイル（中間・就業管理表）とも行う必要がありますが。 （コタ）追記 ---- コタ様 就業管理表は対象人数が多いため、月度別に分けたいと思っています。 式の変更は下記の式でいうとフォルダ名とファイル名の変更と、T列との一致を見ているF列、 参照範囲のF〜Y列を曜日と日にちに合った範囲と行に変更し、以降の13列分に式をコピーしてから 列番号を変更しています。参照範囲は曜日別に5パターン、参照行も週別に5パターンの中で変更 するので、規則性があるとは思うのですが・・・ =IF(AND($T8='¥¥****¥****¥[*月度_担当A.xls]明細 '!$F$36), VLOOKUP($T8,'¥¥****¥****¥[*月度_担当A.xls]明細 '!$F$36:$Y$36,4,0)) コタさんの質問の意図が理解できていなかったらすみません。 (sachi) ---- 数式の参照範囲を条件によって変えるのは、IF関数で分けるなどして 出来ると思います。 しかし、その参照範囲を実際に数式に使うには、INDIRECT関数の助けが必要です。 ところがこいつは、参照先のブックが開いていないとエラーになるのです。 ご提示の数式を見ると別のブックへの参照を使っているので、 結局ご希望のようなことは、数式だけでは出来ないでしょう。 >置換でファイル名を変更しているのですが、 ↑ これをマクロにする手はあるかもしれません。 結局、データを複数のブックに分散させる運用が、こういったことを 難しくさせています。 （純丸）(o^-')b ---- どのような変更をするのか、今ひとつイメージがつかめません。＞＜ 試しに、私が行っている毎月更新の例を挙げてみますので、何をしているか分かるかどうか、 見てみてください。 ＜ブック・シート構成＞ ・[2010年勤務票.xls]ブック ├【4月】 シート → 4月分勤務票。データは全て、まとめシートを参照。 │ 〜 ├【6月】 シート → 同上 └【まとめ】シート → 1年分の出退時間、休暇などのデータ ・[月別勤務票201007.xls]ブック └【Sheet1】シート → 勤務票（月別）の雛型。毎月送られてくる。 ・[出退時間201007.csv]ファイル → 2010年7月分の出退時間 ＜更新処理＞ ・2010年勤務票ブックに、マクロを作成して、以下の処理を行っている。 (1) 年月の入力:更新年月（201007）をユーザー入力 ※以下で使用している年月は、全てこれを使っている (2) 雛型（月別勤務票201007ブック）のシートを、2010年勤務票ブックにコピー&「7月」にリネーム ※雛型ブックは、全て同じフォルダに保存されている（ファイル名が異なるだけ） (3) 7月シートに、まとめシートを参照する数式を入力 ※全シートで共通の数式なので、マクロで直接入力 (4) まとめシート内に、各月シートを参照する数式（※）があるので、コピーして、7月に置換 ※='6月'!$C$49、='6月'!$U$49など。 (5) 7月分の出退時間データ（CSVファイル）を開いて、まとめシート内に貼り付け もちろん実際のレイアウトが分からないとマクロは作れませんが、更新処理として ・どんな情報をマクロに渡して、 ・どんなことを行っているか は、大体つかめるのではないでしょうか。 こんな感じで、sachiさんの行いたいことが、回答者に伝わるように考えてみてください。 とくに、 ・フォルダ名とファイル名の変更→変更するには、どんな情報が必要? ・曜日と日にちに合った範囲と行? ・曜日別に5パターン、参照行も週別に5パターン? このあたりがイメージできてません。 （コタ） ---- =IF(AND($T8='¥¥****¥****¥[*月度_担当A.xls]明細 '!$F$36), VLOOKUP($T8,'¥¥****¥****¥[*月度_担当A.xls]明細 '!$F$36:$Y$36,4,0)) 話の腰を折るようで申し訳ないですけれども、これって、実際に使っている式ですか? AND関数は余り意味がありません。 参照範囲も一行、IF関数の条件判断でその先頭の照合を行っている のでVLOOKUP関数の必然性もありません。 それに、IF関数の条件がFALSEのときの処理が未記入ですね。 この式に限れば、単純参照で事足ります。 =IF($T8='¥¥****¥****¥[*月度_担当A.xls]明細 '!$F$36,'¥¥****¥****¥[*月度_担当A.xls]明細 '!$I$36,"") (みやほりん)(-_∂)b ---- 回答頂いた皆様、ご指摘ありがとうございます。 vlookupを使わなくても、単純参照でできるとのことなので、とりあえずこれはすぐに式を 修正します。ありがとうございました。 コタ様 以下、vlookupで値を抽出していた状態での話ですが… 別ファイルの構成 F:Y AB:AU AX:BQ BT:CM CP:DI 6行目 1週目月曜日 〃火曜日 〃水曜日 〃木曜日 〃金曜日 25行目 2週目月曜日 〃火曜日 〃水曜日 〃木曜日 〃金曜日 44行目 3週目月曜日 〃火曜日 〃水曜日 〃木曜日 〃金曜日 63行目 4週目月曜日 〃火曜日 〃水曜日 〃木曜日 〃金曜日 82行目 5週目月曜日 〃火曜日 〃水曜日 〃木曜日 〃金曜日 就業管理表は21日始まり、別ファイルは12日前後始まり（この週を1週目とする）。 例えば、本日7/23は別ファイルでは2週目の金曜日になるので、CP25:DI25 の値を 就業管理表に抽出する。 就業管理表の構成 T U V X Y AG AH AJ AK AL AM AN AO AP AQ 8行目 日付 : : 38行目 上記のU〜AQにvlookupで別ファイルの値を列番号4,5,7,8,11,12,13,14,15,16,17,18,19,20の 順に抽出する。 就業管理表は前月の雛形ブックの日付を当月のカレンダーに合わせて変更。セルに入っている 関数式の参照範囲を当月の曜日に合わせて修正。これを現在はU列のセルに入っている式を フォルダ名から参照セルまで別ファイルから範囲指定し直してから、それをV列〜AQ列に式を コピペして列番号を修正していました。 フォルダを分けていることで余計な手間がかかっていることは分かっているのですが、現時点では フォルダを一つにすることは難しいです。 このような場合でもマクロを使って式の置換もしくは別ファイルを参照する式の入力といったことが 可能なのでしょうか? マクロ初心者のため、よくわからずすみません。 (sachi) ---- ＞このような場合でもマクロを使って式の置換もしくは別ファイルを参照する式の入力といったことが ＞可能なのでしょうか? Excel上で手作業で行っていることは、大体マクロでできます。 しかも、マクロの自動記録という機能があるので、まっさらからマクロのコードを書く必要もありません。 ↓ご参照あれ http://www.excel.studio-kazu.jp/lib/e4b/e4b.html もちろん、自動記録したままだと、常に同じ月の更新しか使えなかったりしますので、ユーザー入力を 追加したり、変数を使って処理するなど、いろいろ変更は必要ですが、その辺は追々行っていけばいいです。 さて、現在の更新処理は、以下の3つに分けられると思います。 (1) 参照先のフォルダ名・ファイル名の変更 (2) 参照先のセル範囲の変更 (3) ファイルのコピー&リネーム まずは、(1)の部分を、サンプルを使って作ってみましょう。 ※以下のファイル（ブック）は全て、C:¥testというフォルダおよびサブフォルダの下に作っています。 ＜フォルダ・ブック・シート構成＞ ↓こんな感じで、3つのブックを用意してください。 ・C:¥test¥[就業管理表.xls]ブック └【管理】シート → データの参照先 ・C:¥test¥あ¥[6月度_担当A.xls]ブック └【明細】シート → データの参照元（更新前） ・C:¥test¥い¥[7月度_担当A.xls]ブック └【明細】シート → データの参照元（更新後） ---------- 次に、それぞれのシートを、以下の状態にします。 ○ [就業管理表.xls]ブック【管理】シート [R/C] [A] [B] [C] [1] いちご みかん りんご A1 ='C:¥test¥あ¥[6月度_担当A.xls]明細'!$C$1 B1 ='C:¥test¥あ¥[6月度_担当A.xls]明細'!$A$1 C1 ='C:¥test¥あ¥[6月度_担当A.xls]明細'!$B$1 ※参照元ブックを同時に開いていると、=[6月度_担当A.xls]明細!$C$1 のような表示になります。 ○ [6月度_担当A.xls]ブック【明細】シート [R/C] [A] [B] [C] [1] いちご みかん りんご ○ [7月度_担当A.xls]ブック【明細】シート [R/C] [A] [B] [C] [1] もも なし すいか ---------- では、マクロを使って、[就業管理表.xls]ブックの参照元を更新してみましょう。 ※このときは、参照元ブックを必ず閉じておいてください。 ↓のコードを、[就業管理表.xls]ブックのModule1にコピペして、実行してみてください。 ○【Module1】標準モジュール Sub フォルダファイル名置換() Dim ws As Worksheet Set ws = Worksheets("管理") ws.Cells.Replace What:="'C:¥test¥あ¥[6月度_担当A.xls]明細'", _ Replacement:="'C:¥test¥い¥[7月度_担当A.xls]明細'", _ LookAt:=xlPart, MatchCase:=False, MatchByte:=False End Sub 管理シートの参照元が、以下のように更新されたと思いますが、いかがでしょうか。 ○ [就業管理表.xls]ブック【管理】シート [R/C] [A] [B] [C] [1] すいか もも なし A1 ='C:¥test¥い¥[7月度_担当A.xls]明細'!$C$1 B1 ='C:¥test¥い¥[7月度_担当A.xls]明細'!$A$1 C1 ='C:¥test¥い¥[7月度_担当A.xls]明細'!$B$1 ---------- これができたら、上のコード内の "管理" （[就業管理表.xls]ブックのシート名） "'C:¥test¥あ¥[6月度_担当A.xls]明細'" （更新前の参照元） "'C:¥test¥い¥[7月度_担当A.xls]明細'" （更新後の参照元） の部分を、実際の名前に変えて、試してみてください。 ※[就業管理表.xls]ブックのバックアップをお忘れなく。 ちなみに、2ヵ月分参照しているとのことなので、その場合は、新しいほうの月名を 先に更新してくださいね。 （例えば、5月と6月なら→6月を7月にしてから→5月を6月にする） ---------- ところで、問題は(2) 参照先のセル範囲の変更ですが、まだ詳細がつかみきれません。 以下の点を確認させてください。 ・＞別ファイルは12日前後始まり 何日始まりなのかは、どのようなルールで決定されるのでしょうか? ・＞[*月度_担当A.xls]明細 '!$F$36:$Y$36 例示された数式では、36行目となっていますが、「別ファイルの構成」では、36行目の 説明がありません。これはなぜでしょうか? ・就業管理表の8行目は、毎月21日から始まるのでしょうか? ・就業管理表のどこかに、年と月のデータは収録されていませんか? ない場合は、適当な場所に入力するか、マクロの起動時に入力画面が必要になるでしょう。 （コタ） ---- 「(2) 参照先のセル範囲の変更」についてですが、↑の不明点はありつつも、推測しながら 就業管理表を再現してみました。 まず、参照元となるブックの明細シートに、以下のようなデータを入れました。 ・横に長いので、分割表示しています。 ・1行目の番号は、列の位置を見るためのもので、どこにも使っていません。 ・各ブロック（F:Y、AB:AU、AX:BQ、BT:CM、CP:DI）の再左列は、日付データです（表示形式mmdd）。 ・それ以外のデータは適当に、参照先で見たときに、どこからのものか分かる情報を入れています。 （実際には、勤務開始時間とかなのでしょうね） ○ [6月度_担当A.xls]ブック【明細】シート ※「_」セルは未入力または"" [R/C] [A] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Y] [Z] [AA] [AB] [AU] [AV] [AW] [AX] [BQ] [BR] [1] _ 1 2 3 4 5 6 7 8 9 10 11 20 _ _ 1 20 _ _ 1 20 _ [6] _ 0614 ← F6 1 週 目 月 曜 日 - 6F Y6 _ _ 0615 AU6 _ _ 0616 BQ6 _ [25] _ 0621 ← F25 2 週 目 月 曜 日 - 6F Y25 _ _ 0622 AU25 _ _ 0623 BQ25 _ [44] _ 0628 ← F44 3 週 目 月 曜 日 - 6F Y44 _ _ 0629 AU44 _ _ 0630 BQ44 _ [63] _ 0705 ← F63 4 週 目 月 曜 日 - 6F Y63 _ _ 0706 AU63 _ _ 0707 BQ63 _ [R/C] [BS] [BT] [CM] [CN] [CO] [CP] [CQ] [CR] [CS] [CT] [CU] [CV] [CW] [CX] [CY] [CZ] [DI] [1] _ 1 20 _ _ 1 2 3 4 5 6 7 8 9 10 11 20 [6] _ 0617 CM6 _ _ 0618 ← CP6 1 週 目 金 曜 日 - 6F DI6 [25] _ 0624 CM25 _ _ 0625 ← CP25 2 週 目 金 曜 日 - 6F DI25 [44] _ 0701 CM44 _ _ 0702 ← CP44 3 週 目 金 曜 日 - 6F DI44 [63] _ 0708 CM63 _ _ 0709 ← CP63 4 週 目 金 曜 日 - 6F DI63 ○ [7月度_担当A.xls]ブック【明細】シート [R/C] [A] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Y] [Z] [AA] [AB] [AU] [AV] [AW] [AX] [BQ] [BR] [1] _ 1 2 3 4 5 6 7 8 9 10 11 20 _ _ 1 20 _ _ 1 20 _ [6] _ 0712 ← F6 1 週 目 月 曜 日 - 7F Y6 _ _ 0713 AU6 _ _ 0714 BQ6 _ [25] _ 0719 ← F25 2 週 目 月 曜 日 - 7F Y25 _ _ 0720 AU25 _ _ 0721 BQ25 _ [44] _ 0726 ← F44 3 週 目 月 曜 日 - 7F Y44 _ _ 0727 AU44 _ _ 0728 BQ44 _ [63] _ 0802 ← F63 4 週 目 月 曜 日 - 7F Y63 _ _ 0803 AU63 _ _ 0804 BQ63 _ [82] _ 0809 ← F82 5 週 目 月 曜 日 - 7F Y82 _ _ 0810 AU82 _ _ 0811 BQ82 _ [R/C] [BS] [BT] [CM] [CN] [CO] [CP] [CQ] [CR] [CS] [CT] [CU] [CV] [CW] [CX] [CY] [CZ] [DI] [1] _ 1 20 _ _ 1 2 3 4 5 6 7 8 9 10 11 20 [6] _ 0715 CM6 _ _ 0716 ← CP6 1 週 目 金 曜 日 - 7F DI6 [25] _ 0722 CM25 _ _ 0723 ← CP25 2 週 目 金 曜 日 - 7F DI25 [44] _ 0729 CM44 _ _ 0730 ← CP44 3 週 目 金 曜 日 - 7F DI44 [63] _ 0805 CM63 _ _ 0806 ← CP63 4 週 目 金 曜 日 - 7F DI63 [82] _ 0812 CM82 _ _ 0813 ← CP82 5 週 目 金 曜 日 - 7F DI82 ---------- さて、上の2か月分のブックから、必要なデータを参照します。 ・T列には1ヶ月の日付データを入力しています（この場合は、6月21日開始で30日分なので、37行目まで）。 ※今はT8セルの6月21日を手入力していますが、月の情報があれば、マクロで指定可能です） ・現行のsachiさんのブックでは、U8:AQ38まで（途中抜けあり）、全て異なる参照範囲を手入力していると思います。 例えば、↓こんな数式でしょうか。 U8 =IF($T8='¥¥****¥****¥[*月度_担当A.xls]明細 '!$F$25,'¥¥****¥****¥[*月度_担当A.xls]明細 '!$I$25,"") V8 =IF($T8='¥¥****¥****¥[*月度_担当A.xls]明細 '!$F$25,'¥¥****¥****¥[*月度_担当A.xls]明細 '!$J$25,"") U9 =IF($T9='¥¥****¥****¥[*月度_担当A.xls]明細 '!$AB$25,'¥¥****¥****¥[*月度_担当A.xls]明細 '!$AE$25,"") これは大変な労力ですね。 ・なので、これを全て同じ数式で参照することを考えてみました。 ※もちろんマクロでも可能ですが、数式でできることは数式でやりたい主義ですので。^^ ファイルサイズや更新処理時間などで、問題があるようなら、そのとき考えましょう。 ・作業列（AR〜AU列目）を4つ、作業行（7行目）を1つ使用しますが、U8:AQ38は1つの数式をフィルコピーできます。 （歯抜けの列は、当然別の何かが入っているでしょうから、そこは避けてください） ・このようにしておけば、更新の際には、フォルダ・ファイル名と、月の変更だけで済みます。 レイアウト等は、実際のものと同じはずですので、試してみてください。 （参照元のフォルダ・ファイル・シート名は、実際のものに適宜変更してください） ○ [就業管理表.xls]ブック【管理】シート ※「_」セルは未入力または"" [R/C] [A] [T] [U] [V] [W] [X] [Y] [Z] [AF] [AG] [AH] [AI] [AJ] [AK] [AL] [AM] [AN] [AO] [AP] [AQ] [AR] [AS] [AT] [AU] [1] _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ [7] _ 日付 4 5 _ 7 8 _ _ 11 12 _ 13 14 15 16 17 18 19 20 0614 0712 _ _ [8] _ 0621 2 週 _ 月 曜 _ _ 6F Q25 _ R25 S25 T25 U25 V25 W25 X25 Y25 7 -21 20 0 [9] _ 0622 2 週 _ 火 曜 _ _ 6F AM25 _ AN25 AO25 AP25 AQ25 AR25 AS25 AT25 AU25 8 -20 20 22 [10] _ 0623 2 週 _ 水 曜 _ _ 6F BI25 _ BJ25 BK25 BL25 BM25 BN25 BO25 BP25 BQ25 9 -19 20 44 [11] _ 0624 2 週 _ 木 曜 _ _ 6F CE25 _ CF25 CG25 CH25 CI25 CJ25 CK25 CL25 CM25 10 -18 20 66 [12] _ 0625 2 週 _ 金 曜 _ _ 6F DA25 _ DB25 DC25 DD25 DE25 DF25 DG25 DH25 DI25 11 -17 20 88 [13] _ 0626 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 12 -16 20 110 [14] _ 0627 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 13 -15 20 132 [15] _ 0628 3 週 _ 月 曜 _ _ 6F Q44 _ R44 S44 T44 U44 V44 W44 X44 Y44 14 -14 39 0 [16] _ 0629 3 週 _ 火 曜 _ _ 6F AM44 _ AN44 AO44 AP44 AQ44 AR44 AS44 AT44 AU44 15 -13 39 22 [17] _ 0630 3 週 _ 水 曜 _ _ 6F BI44 _ BJ44 BK44 BL44 BM44 BN44 BO44 BP44 BQ44 16 -12 39 44 [18] _ 0701 3 週 _ 木 曜 _ _ 6F CE44 _ CF44 CG44 CH44 CI44 CJ44 CK44 CL44 CM44 17 -11 39 66 [19] _ 0702 3 週 _ 金 曜 _ _ 6F DA44 _ DB44 DC44 DD44 DE44 DF44 DG44 DH44 DI44 18 -10 39 88 [20] _ 0703 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 19 -9 39 110 [21] _ 0704 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 20 -8 39 132 [22] _ 0705 4 週 _ 月 曜 _ _ 6F Q63 _ R63 S63 T63 U63 V63 W63 X63 Y63 21 -7 58 0 [23] _ 0706 4 週 _ 火 曜 _ _ 6F AM63 _ AN63 AO63 AP63 AQ63 AR63 AS63 AT63 AU63 22 -6 58 22 [24] _ 0707 4 週 _ 水 曜 _ _ 6F BI63 _ BJ63 BK63 BL63 BM63 BN63 BO63 BP63 BQ63 23 -5 58 44 [25] _ 0708 4 週 _ 木 曜 _ _ 6F CE63 _ CF63 CG63 CH63 CI63 CJ63 CK63 CL63 CM63 24 -4 58 66 [26] _ 0709 4 週 _ 金 曜 _ _ 6F DA63 _ DB63 DC63 DD63 DE63 DF63 DG63 DH63 DI63 25 -3 58 88 [27] _ 0710 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 26 -2 58 110 [28] _ 0711 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 27 -1 58 132 [29] _ 0712 1 週 _ 月 曜 _ _ 7F Q6 _ R6 S6 T6 U6 V6 W6 X6 Y6 28 0 1 0 [30] _ 0713 1 週 _ 火 曜 _ _ 7F AM6 _ AN6 AO6 AP6 AQ6 AR6 AS6 AT6 AU6 29 1 1 22 [31] _ 0714 1 週 _ 水 曜 _ _ 7F BI6 _ BJ6 BK6 BL6 BM6 BN6 BO6 BP6 BQ6 30 2 1 44 [32] _ 0715 1 週 _ 木 曜 _ _ 7F CE6 _ CF6 CG6 CH6 CI6 CJ6 CK6 CL6 CM6 31 3 1 66 [33] _ 0716 1 週 _ 金 曜 _ _ 7F DA6 _ DB6 DC6 DD6 DE6 DF6 DG6 DH6 DI6 32 4 1 88 [34] _ 0717 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 33 5 1 110 [35] _ 0718 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 34 6 1 132 [36] _ 0719 2 週 _ 月 曜 _ _ 7F Q25 _ R25 S25 T25 U25 V25 W25 X25 Y25 35 7 20 0 [37] _ 0720 2 週 _ 火 曜 _ _ 7F AM25 _ AN25 AO25 AP25 AQ25 AR25 AS25 AT25 AU25 36 8 20 22 [38] _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 0 0 1 0 ☆ 作業行・列 AR7 ='C:¥test¥あ¥[6月度_担当A.xls]明細'!$F$6 ※日付データ AS7 ='C:¥test¥い¥[7月度_担当A.xls]明細'!$F$6 ※日付データ AR8 =IF($T8="",0,$T8-AR$7) →AR8:AR38フィルコピー ※6月分ブックの開始日との差 AS8 =IF($T8="",0,$T8-AS$7) →AS8:AS38フィルコピー ※7月分ブックの開始日との差 AT8 =INT(IF(AS8<0,AR8,AS8)/7)*19+1 →AT8:AT38フィルコピー ※参照元の表内での行位置 AU8 =MOD(IF(AS8<0,AR8,AS8),7)*22 →AU8:AU38フィルコピー ※参照元の表内での列位置 ※作業行・列の場所がすでに使用中なら、好きなところに移動してください。 ☆ メイン U8 =IF(WEEKDAY($T8,3)>=5,"",INDEX(IF($AS8<0,'C:¥test¥あ¥[6月度_担当A.xls]明細'!$F$6:$DI$63, 'C:¥test¥い¥[7月度_担当A.xls]明細'!$F$6:$DI$82),$AT8,$AU8+U$7)) →U8:AQ38フィルコピー （コタ） ---- コタ様 ご回答ありがとうございます。また返答が遅くなり申し訳ありません。 まず、教えて頂いた数式の方を試したところうまくいきました。2ヶ月にまたがるファイルからの 参照がきちんとできたので、大変感激しました!ありがとうございます。 マクロの方もご説明ありがとうございます。 確認事項については下記の通りです。 ・＞別ファイルは12日前後始まり 何日始まりなのかは、どのようなルールで決定されるのでしょうか? ・＞[*月度_担当A.xls]明細 '!$F$36:$Y$36 例示された数式では、36行目となっていますが、「別ファイルの構成」では、36行目の 説明がありません。これはなぜでしょうか? ・就業管理表の8行目は、毎月21日から始まるのでしょうか? ・就業管理表のどこかに、年と月のデータは収録されていませんか? 別ファイルの開始日については、費用請求先の締め日に従っていましてどのようなルールに なっているのかがわかりません。年間のスケジュールはもらっているので、1年分の開始日はわかります。 例示した式の36行目はこちらの間違いでした、すみません。先月から行を追加していたのですが、 例示の式はそれ以前のものでした。なので、現在は上に記載した別ファイルの構成通りです。 就業管理表の8行目は毎月21日始まりで変更ありません。それから年と月は就業管理表の T2に平成22年を入力、W2に○月期と入力しています。それ以外ではT8からの日付欄に年/月/日 が入っています。 (sachi) ---- ＞教えて頂いた数式の方を試したところうまくいきました できましたか。よかったです。 参照範囲の部分が一律化できれば、更新作業の負荷も半減しますね。^^ マクロで更新するにしても、大半は(1) 参照先のフォルダ名・ファイル名の変更の処理で 終わってしまいますし、残っているのは年月の更新ぐらいです。 ＞別ファイルの開始日については、〜どのようなルールになっているのかがわかりません。 これについては、明細'!$F$6の日付を参照するようにしたので、だいじょうぶです。 ＞例示した式の36行目はこちらの間違いでした 了解です。 ＞就業管理表の8行目は毎月21日始まりで変更ありません。 了解です。 ＞それから年と月は就業管理表の T2に平成22年を入力、W2に○月期と入力しています。 ＞それ以外ではT8からの日付欄に年/月/日 が入っています。 どちらかの情報を使えば、年月の更新は自動で可能ですね。 （2010/6→+1ヶ月→2010/7→+1ヶ月→2010/8） ちなみに、どちらかだけを入力にしておけば、残りは数式で出力可能ですが、どっちが 使いやすいでしょうか。 (ア)年と月を入力にする場合 ・T2に22と入力（表示形式を "平成"0"年度"）、W2に6と入力（表示形式を 0"月度"） ・T8を数式で =DATE(1988+T2,W2,21) ※この場合、T2,W2の実体は数値 (イ)日付を入力にする場合 ・T8に6/21（or 10/6/21）入力 ・T2 =TEXT(T8,"ggge年")、W2 =MONTH(T8)&"月度" ※この場合、T2,W2は文字列 ---------- ところで、(1) 参照先のフォルダ名・ファイル名の変更ですが、↑のサンプルでは、 マクロコード内に直接、変更前後のフォルダ名・ファイル名を記述していますが、 これだと毎月コード書き換えになって使いにくいです。 なのでこの部分も、自動的に作成できるようにしたほうがいいと思いますが、 フォルダ名・ファイル名について、何か規則はありませんか? （¥2010年¥6月のように年月だけ変わるとか） 規則性がない場合は、予めどこかのセルに、フォルダ名・ファイル名の一覧を入力しておくのが、 一番手間が少ないかと思います。 （コタ） ---- コタ様 ご親切に色々と教えて頂き、大変ありがとうございます。 就業管理表の年月の入力については、(イ)日付を入力にする場合が使いやすそうなので、早速 来月分のファイルから使わせて頂きます。（ア）の方も参考にさせて頂きます。 フォルダ名・ファイル名の規則についてですが、どちらも変更になるのは月度のみです。 一つ質問なのですが、教えて頂いた数式のほうのメインセルに入れる式の頭の U8 =IF(WEEKDAY($T8,3)>=5,"",の部分ですが、もし今後別ファイルに土日のデータを追加した場合、 U8 =IF(WEEKDAY($T8,3)>=7,"",とすれば就業管理表の土日欄も対応可能という理解であっている でしょうか? (sachi) ---- ＞ フォルダ名・ファイル名の規則についてですが、どちらも変更になるのは月度のみです。 それは朗報ですね。更新処理が既存の情報だけで済みます。 ↓こんな名前だとしたら、 'C:¥test¥6月度¥[6月度_担当A.xls]明細' 7月度→8月度、6月度→7月度の順で置換してやればOKですね。 試しにサンプルで作ってみますので、しばらくお待ちください。 ＞ U8 =IF(WEEKDAY($T8,3)>=5,"",の部分ですが、もし今後別ファイルに土日のデータを追加した場合、 WEEKDAY($T8,3)は、$T8（日付）の曜日を、0（月曜）〜6（日曜）の範囲で返しますので、 WEEKDAY($T8,3)>=5 の部分は、$T8の曜日が土曜か日曜のときTrue、それ以外のときFalseとなります。 この分岐によって、T列が土日のときは""、それ以外のときは参照元のデータを出力しています。 なので、別ファイルに土日のデータが追加されても、IF(WEEKDAY($T8,3)>=5,"",の部分は 変更する必要はありません。 さらにいうと、例えば土日のデータが、別ファイルのDJ列以右に追加されるのであれば、 メイン数式・作業列含めて、何も変更する必要はないです。 （参照の対象としているのは、$F$6:$DI$63の範囲だけなので） （コタ） あ、ごめんなさい。よく見たら土日も反映したいということでしたか。 であれば逆に、IF(WEEKDAY($T8,3)>=5,"",の部分はいらなくなり、INDEX(〜)の部分だけでよくなります。 その代わり、INDEXの参照範囲を広げることになるでしょうね。 （コタ）追記16:55 ---- コタ様 数式の土日対応については理解できました。ご説明ありがとうございました。 先に教えて頂いていたマクロの参照式更新のテストもできました。 お忙しい中、長々とおつきあい頂き本当にありがとうございます。 またサンプルコードができましたらご教授下さい。よろしくお願いします。 (sachi) ---- サンプルマクロができました。 （以下裏話ですが、当初参照元ブックを閉じたまま、月度の置換で更新しようとしたところ、 びっくりするほど時間がかかりました。1セル1秒ぐらいで、ちょっとずつ変わっていく。。 おそらくINDEXで範囲を参照しているためだと思いますが、これでは実用に耐えないので、 予め参照元ブックを開いて置換し、終わったら閉じる方法で作ってみました。 これなら1シートの更新が数秒で終わるので、それほどストレスにはならないでしょう。） サンプルで使用した構成は、以下のとおりです。 ※現在が6月度で、7月度に更新の前提。 以前の「あ」「い」フォルダは不使用です。 ＜フォルダ等体系＞ ・〜¥test フォルダ ├「6月度」フォルダ │ ├［6月度_担当A.xls］ブック │ └［6月度_担当B.xls］ブック ←(3)で使用予定 ├「7月度」フォルダ │ ├［7月度_担当A.xls］ブック │ └［7月度_担当B.xls］ブック ├「8月度」フォルダ │ ├［8月度_担当A.xls］ブック │ └［8月度_担当B.xls］ブック └［就業管理表.xls］ブック ＜ブック・シート構成＞ ・[就業管理表.xls]ブック └【担当A】シート →「担当A」の集計表。データは、6月度_担当Aブックと7月度_担当Aブックを参照。 ・[6月度_担当A.xls]ブック └【明細】シート →「担当A」の明細表。就業管理表ブックの参照元。 ※[*月度_担当*.xls]ブックは全て同様なので省略。 ＜シートレイアウト＞ ○ [就業管理表.xls]ブック【担当A】シート ・レイアウトは前と同じですが、メインの数式を若干変更しました。 ・数式は、参照元ブックを同時に開いている状態のものです。 ・数式の変更は、参照元ブックを同時に開いている状態で行ってください。 （閉じている状態だと、ものすごく時間がかかります） U8 =IF(WEEKDAY($T8,3)>=5,"",INDEX(IF($AS8<0,[6月度_担当A.xls]明細!$F$6:$FA$82, [7月度_担当A.xls]明細!$F$6:$FA$82),$AT8,$AU8+U$7)) →U8:AQ38フィルコピー ※変更箇所 [6月度_担当A.xls]明細!$F$6:$DI$63 [7月度_担当A.xls]明細!$F$6:$DI$82 →[6月度_担当A.xls]明細!$F$6:$FA$82 →[7月度_担当A.xls]明細!$F$6:$FA$82 ^^ ^^ ^^ DI→FA:参照元ブックに、土日のデータが追加されたときのための拡張 63→82:ミス。月によらず5週目分までの範囲を設定しておく必要があった ※参照元ブック[*月度_担当*.xls]は、変更ないので省略。 -------------------------------------------------- ここまでが前提の説明で（長い）、以下に更新マクロサンプルを提示します。 ・これもそこそこ長いですが、あまり難しいことはやってないです。 ・実行直前に保存しておくと、何度もやり直しができていいです。 ○【Module1】標準モジュール Sub 更新処理() Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook Dim ws As Worksheet Dim dt As Date Dim i As Integer, d As Integer, n As Integer Dim m1 As Integer, m2 As Integer, m3 As Integer Dim s As String, t As String Dim s1 As String, s2 As String, s3 As String '初期設定 s = "参照元ファイルは、全て閉じてありますか?" & vbLf _ & "（6月度_担当A.xlsなど）" If MsgBox(s, vbYesNo + vbDefaultButton2) = vbNo Then Exit Sub Set ws = ActiveSheet dt = ws.Range("T8").Value '当月21日 m1 = Month(dt) '当月 m2 = m1 Mod 12 + 1 '翌月 m3 = m2 Mod 12 + 1 '翌々月 n = Worksheets.Count 'パス取得&存在チェック s = ws.Range("AR7").Formula s = Replace(Replace(s, "]明細'!$F$6", ""), "[", "") s1 = Replace(Replace(s, "'", ""), "=", "") '当月Fのパス s2 = Replace(s1, m1 & "月", m2 & "月") '翌月Fのパス s3 = Replace(s1, m1 & "月", m3 & "月") '翌々月Fのパス s = "" If Dir(s1, vbNormal) = "" Then s = s & s1 & vbLf If Dir(s2, vbNormal) = "" Then s = s & s2 & vbLf If Dir(s3, vbNormal) = "" Then s = s & s3 & vbLf If s <> "" Then MsgBox s & vbLf & "ファイルが存在しないパスのため、マクロを終了します。" Exit Sub End If '画面&計算停止 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'ファイルオープン Set wb1 = Workbooks.Open(s1, ReadOnly:=True) Set wb2 = Workbooks.Open(s2, ReadOnly:=True) Set wb3 = Workbooks.Open(s3, ReadOnly:=True) '月度更新 ws.Range("T8").Value = DateSerial(Year(dt), m1 + 1, 21) '翌月21日 With ws.Range("T7:AS38") .Replace What:="[" & m2 & "月", Replacement:="[" & m3 & "月", _ LookAt:=xlPart, MatchCase:=False, MatchByte:=False .Replace What:="[" & m1 & "月", Replacement:="[" & m2 & "月" End With '日付増減 ws.Range("T9:T38").Formula = "=T8+1" d = Day(DateSerial(Year(dt), m1 + 2, 0)) '翌月の日数 If d < 31 Then ws.Range("T8").Offset(d).Resize(31 - d).ClearContents 'ファイルクローズ wb1.Close False wb2.Close False wb3.Close False '終了設定 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -------------------------------------------------- これができれば、(2) 参照先のセル範囲の変更までが終了ですね。 ところで(3) ファイルのコピー&リネームに関して、いくつか不明点があります。 ・＞就業管理表と別ファイルは個人別になっていて複数存在し、 別ファイルのほうは、[*月度_担当*.xls]というファイル名と分かりましたが、 就業管理表のほうは、どのようなファイル名なのでしょうか? 就業管理表_*月度_担当*.xls? ・就業管理表は、どのような フォルダ体系で保管されているのでしょうか? 1つのフォルダ内に全員の全月分を保管?月別フォルダに分けて保管? ・就業管理表の保管されているフォルダ内に、それ以外のファイルは存在しますか? あるならば、それらのファイルの名前を教えてください。（更新対象から外すため） （コタ） ---- コタ様 確認事項ですが、フォルダの体系とファイル名は以下のような感じになっています。 別ファイル→ 'C:¥test¥*月度¥別ファイル¥[*月度_担当*.xls] 就業管理表→ 'C:¥test¥就業管理表¥*月度¥[就業管理表*月度_担当A.xls] 就業管理表の保管フォルダには就業管理表以外は存在しません。 教えて頂いたマクロをテストフォルダを作って試してみましたが、ファイルが存在しないパスに なってしまいました。コードを勉強しつつ、もう1度試してみます。 (sachi) ---- ご回答ありがとうございます。 フォルダ内の全ブックについて、更新処理する方向で考えてみますね。 ＞ファイルが存在しないパスに なってしまいました。 むむ、おかしいですね。 AR7セルの数式から、余計なものを削って、参照元のパスを取得しているはずなのですが。 サーバー名のままだと、だめなのかな。。 こちらでもちょっと調べてみますね。 （コタ） ---- なかなか時間がとれなくて、遅くなってしまいましたが、なんとかできました。 ・更新対象のブック全てに処理を行うので、別に1つ更新用のブックを用意しました。 （¥就業管理表フォルダの直下に、「就業管理表更新.xls」で作成） ・フォルダの体系が判明したので、更新ブックのワークシート上の数式で、対象のフォルダ名を作成しました。 ・¥就業管理表フォルダの下の翌月度フォルダ（↓では7月度）は、更新処理前に作成しておいてください。 （ない場合に、自動作成も可能ですが、今のところ未対応です。） ・前回ご提示した就業管理表*月度_担当*.xlsの更新処理マクロは不要ですので、削除しておいてください。 では例によって、構成の説明から。 ＜フォルダ等体系＞ ・〜¥test フォルダ ├「6月度」フォルダ │ └「別ファイル」フォルダ │ ├［6月度_担当A.xls］ブック │ └［6月度_担当B.xls］ブック ├「7月度」フォルダ │ └「別ファイル」フォルダ │ ├［7月度_担当A.xls］ブック │ └［7月度_担当B.xls］ブック ├「8月度」フォルダ │ └「別ファイル」フォルダ │ ├［8月度_担当A.xls］ブック │ └［8月度_担当B.xls］ブック │ └「就業管理表」フォルダ ├「6月度」フォルダ │ ├［就業管理表6月度_担当A.xls］ブック │ └［就業管理表6月度_担当B.xls］ブック ├「7月度」フォルダ │ ├［就業管理表7月度_担当A.xls］ブック ←これを自動作成 │ └［就業管理表7月度_担当B.xls］ブック ← 〃 │ └［就業管理表更新.xls］ブック ←更新処理マクロのはいったブック（今回追加） ＜ブック・シート構成＞ ・［就業管理表*月度_担当*.xls］ブックは、シートが1つの前提（シート名は任意）。 ＜シートレイアウト＞ ※数式の変更はないので省略。 -------------------------------------------------- 次に、[就業管理表更新.xls]ブックのワークシートのレイアウトです。 ・入力するのは、B2セル（更新前の当月:この例では6）のみです。 ・【更新結果】の部分（14行目以降）は、更新先ブックがあるかないかで、既存/作成の どちらかが出力されます。 （更新先ブックがある場合は、更新処理そのものを行いません。） ○ [就業管理表更新.xls]ブック【更新情報】シート ※「_」セルは未入力または"" [R/C] [A] [B] [C] [1] 【更新月】 入力↓ _ [2] 更新前 6 月から [3] 更新後 7 月へ [4] 翌々月 8 月 [5] 【参照元】 _ _ [6] 1:別ファイル1 ¥6月度¥別ファイル¥ _ [7] 2:別ファイル2 ¥7月度¥別ファイル¥ _ [8] 3:別ファイル3 ¥8月度¥別ファイル¥ _ [9] 【参照先】 _ _ [10] 4:就業管理表1 ¥就業管理表¥6月度¥ _ [11] 5:就業管理表2 ¥就業管理表¥7月度¥ _ [12] _ _ _ [13] 【更新結果】 更新元 更新先 [14] 作成 就業管理表6月度_担当A.xls 就業管理表7月度_担当A.xls [15] 作成 就業管理表6月度_担当B.xls 就業管理表7月度_担当B.xls B3 =MOD(B2,12)+1 B4 =MOD(B3,12)+1 B6 ="¥" & B2&"月度¥別ファイル¥" →B6:B8フィルコピー B10 ="¥就業管理表¥" & B2&"月度¥" →B10:B11フィルコピー -------------------------------------------------- 最後に、[就業管理表更新.xls]ブックの更新処理マクロです。 ・存在チェックの部分で、半分以上使ってしまいました。 ○【Module1】標準モジュール Public Sub 更新処理() Dim fso As Object, ofi As Object Dim v As Variant Dim ws As Worksheet Dim dt As Date Dim i As Integer, j As Integer, d As Integer, n As Integer Dim m(1 To 3) As Integer '当月,翌月,翌々月 Dim g(1 To 3) As String '*月度 Dim s As String, t As String Dim st As String '親フォルダ名 Dim sfo(1 To 5) As String 'フォルダパス Dim ofo4 As Object Dim SName As String, BName As String Dim paths() As String, pth(1 To 5) As String 'ファイルパス '実行確認 s = "更新関連ファイルは、全て閉じてありますか?" & vbLf _ & "（就業管理表、6月度_担当A.xlsなど）" If MsgBox(s, vbYesNo + vbDefaultButton2) = vbNo Then Exit Sub '画面&計算停止 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual '初期設定 Set fso = CreateObject("Scripting.FileSystemObject") Set ws = ActiveSheet v = ws.Range("B1:B11").Value For i = 1 To 3 m(i) = v(i + 1, 1) '月 g(i) = m(i) & "月度" '月度 Next st = fso.GetParentFolderName(ThisWorkbook.Path) '親フォルダ名 'フォルダ&ファイル存在チェック s = "" For i = 1 To 5 sfo(i) = st & v(Array(6, 7, 8, 10, 11)(i - 1), 1) 'フォルダパス If Dir(sfo(i), vbDirectory) = "" Then s = s & sfo(i) & vbLf Next Call ExistCheck(s, "フォルダ") s = "" Set ofo4 = fso.GetFolder(sfo(4)) n = ofo4.Files.Count If n = 0 Then s = sfo(4) & vbLf Call ExistCheck(s, "ファイル") ReDim paths(1 To n, 1 To 5) s = "": i = 0 For Each ofi In ofo4.Files i = i + 1 paths(i, 4) = ofi.Path '就業表当月パス paths(i, 5) = Replace(paths(i, 4), g(1), g(2)) '就業表翌月パス SName = fso.GetfileName(paths(i, 4)) '就業表名 BName = Replace(SName, "就業管理表" & g(1), "") '_担当名.xls For j = 1 To 3 paths(i, j) = sfo(j) & g(j) & BName '別ファパス If Dir(paths(i, j), vbNormal) = "" Then s = s & paths(i, j) & vbLf Next Next Call ExistCheck(s, "ファイル") '更新処理 ws.Range("A14", ws.Range("C1000").End(xlUp).Offset(1)).ClearContents For i = 1 To n Application.StatusBar = i & "/" & n & "番目処理中・・・" With ws.Cells(13 + i, 1) .Offset(, 1).Value = fso.GetfileName(paths(i, 4)) .Offset(, 2).Value = fso.GetfileName(paths(i, 5)) If Dir(paths(i, 5), vbNormal) = "" Then For j = 1 To 5 pth(j) = paths(i, j) Next Call ファイル更新(m(1), g, pth) .Value = "作成" Else .Value = "既存" End If End With Next '終了設定 Set fso = Nothing Application.StatusBar = False Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub '-------------------------------------------------- '同じ処理を外出し Private Sub ExistCheck(ByVal s As String, ByVal t As String) If s <> "" Then MsgBox s & vbLf & t & "が存在しないため、マクロを終了します。" End End If End Sub '-------------------------------------------------- '更新処理のメイン Private Sub ファイル更新(ByVal m1 As Integer, ByRef g() As String, _ ByRef pth() As String) Dim wb(1 To 4) As Workbook Dim ws As Worksheet Dim dt As Date Dim i As Integer, d As Integer 'ファイルオープン For i = 1 To 4 Set wb(i) = Workbooks.Open(pth(i), ReadOnly:=True) Next Set ws = wb(4).Worksheets(1) dt = ws.Range("T8").Value '当月21日 '月度更新 ws.Range("T8").Value = DateSerial(Year(dt), m1 + 1, 21) '翌月21日 With ws.Range("T7:AS38") .Replace What:="[" & g(2), Replacement:="[" & g(3), _ LookAt:=xlPart, MatchCase:=False, MatchByte:=False .Replace What:="[" & g(1), Replacement:="[" & g(2) End With '日付増減 ws.Range("T9:T38").Formula = "=T8+1" d = Day(DateSerial(Year(dt), m1 + 2, 0)) '翌月の日数 If d < 31 Then ws.Range("T8").Offset(d).Resize(31 - d).ClearContents 'ファイルクローズ For i = 1 To 3 wb(i).Close False Next wb(4).SaveAs (pth(5)) wb(4).Close False End Sub パスの情報をワークシートで設定しているので、こちらのほうが成功しやすいかもしれませんね。 じっくりと検証してみてください。 （コタ） ---- コタ様 返答が遅くなり大変すみませんでした。急ぎの用件があり、昨日からようやくこちらに取り掛かって います。フォルダ体系を上記に合わせて作成し、更新用のファイルも作成し、マクロを 実行してみたのですが、下記の名前のファイルが存在しないということで、マクロが終了して しまいました。 〜¥6月度¥別ファイル¥6月度就業管理表就業管理表7月度_担当A.xls 〜¥6月度¥別ファイル¥7月度就業管理表就業管理表7月度_担当A.xls 〜¥6月度¥別ファイル¥8月度就業管理表就業管理表7月度_担当A.xls （_担当B.xlsも同じく） ※担当A、Bのところはもちろん実際使用しているファイル名で試しています。 フォルダ&ファイル存在チェックのところのコードを変更すればいいのかと思ったのですが、 コードがまだ理解しきれていないためどこを修正していいのかわかりません。 すみませんが、どうするべきなのか再度教えて下さい。よろしくお願い致します。 (sachi) ---- ＞ 〜¥6月度¥別ファイル¥6月度就業管理表就業管理表7月度_担当A.xls うーん?例えば、¥就業管理表¥6月度¥の下に、就業管理表7月度_担当A.xls （本来は、就業管理表6月度_担当A.xls）があると、 〜¥6月度¥別ファイル¥6月度就業管理表7月度_担当A.xls こんなパスになって、チェックにひっかかるのですが、これよりさらに 「就業管理表」が多いですね。 まずは、 ＜フォルダ等体系＞ ○ [就業管理表更新.xls]ブック【更新情報】シート が提示のものと一致しているか、確認してみてください。 （月が違っていないか、¥が抜けていないかなど） それでも原因不明なら、パスを調べるマクロを作ってみますので。 --------------------------- 別件ですが、提示のマクロで、修正しておいたほうがよさそうな箇所がありましたので、 ご連絡しておきます（バグではありません）。 '同じ処理を外出し Private Sub ExistCheck(ByVal s As String, ByVal t As String) If s <> "" Then MsgBox s & vbLf & t & "が存在しないため、マクロを終了します。" Application.Calculation = xlCalculationAutomatic '←8/6追加 End End If End Sub 上の8/6追加と書いてある行を、追加しておいてください。 ※存在チェックにひっかかってマクロが止まると、計算方法が手動のままでした。 （コタ） ---- コタ様 コードの追加については了解致しました。 もう1度[就業管理表更新.xls]ブック【更新情報】シートを見直してみましたが、 月や¥の抜けなどコタさん提示のものと違っているところはありませんでした… 間違いのないようテストフォルダで同じ体系を作成し、6〜8月の期間で試していますので、 B2からB11セルまで提示のものと同じになっています。 明日から長期連休に入るため10日程、確認ができなくなります。パスを調べるマクロ を作って頂けるようでしたら、急ぎませんのでお時間が空いたときによろしくお願い致します。 お手数をおかけしてすみません。 (sachi) ---- ちょうど今できました。^^ ・以下のマクロを、既存のマクロの下にでもコピペし実行 ・開いているシートのG列に、パスの一覧が出力される ・実名などを変更 のうえ、こちらに貼り付けてみてください。 （件数が多い場合は、同じようなパスは適当に外していいですよ） ○【Module1】標準モジュール 'パス取得マクロ Sub パス取得() Dim st As String Dim i As Long Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") st = fso.GetParentFolderName(ThisWorkbook.Path) '親フォルダ名 Range("G1").CurrentRegion.ClearContents i = 1 Call 取得メイン(st, i, fso) Range("G1").CurrentRegion.Replace _ fso.GetParentFolderName(st), "〜", LookAt:=xlPart, MatchCase:=False Set fso = Nothing End Sub 'パス取得のメイン Private Sub 取得メイン(st, i, fso) Dim ofo As Object, ofi As Object, oSb As Object Set ofo = fso.GetFolder(st) For Each oSb In ofo.SubFolders Cells(i, 7).Value = oSb.Path i = i + 1 Next For Each ofi In ofo.Files Cells(i, 7).Value = ofi.Path i = i + 1 Next For Each oSb In ofo.SubFolders Call 取得メイン(oSb.Path, i, fso) Next End Sub （コタ） ---- コタ様 返信が大変遅くなり申し訳ありません。 上記のマクロを実行した結果は以下の通りです。 〜¥テスト¥6月度 〜¥テスト¥7月度 〜¥テスト¥8月度 〜¥テスト¥就業管理表 〜¥テスト¥6月度¥別ファイル 〜¥テスト¥6月度¥別ファイル¥工数管理7月度_担当A.xls 〜¥テスト¥7月度¥別ファイル 〜¥テスト¥7月度¥別ファイル¥工数管理8月度_担当A.xls 〜¥テスト¥8月度¥別ファイル 〜¥テスト¥8月度¥別ファイル¥工数管理9月度_担当A.xls 〜¥テスト¥就業管理表¥6月度 〜¥テスト¥就業管理表¥7月度 〜¥テスト¥就業管理表¥就業管理表更新.xls 〜¥テスト¥就業管理表¥6月度¥就業管理表7月度_担当A.xls よろしくお願い致します。 (sachi) ---- おひさしぶりです。同じく休み明けのコタです。 まずは、ファイル名が異なっていますね。 参照元である「別ファイル」の名前は、工数管理*月度_担当A.xls でしたか。 ただし、これが原因ではありません。 原因は、フォルダ名の中の月度と、ファイル名の中の月度がずれている点です。 （例えば、〜¥テスト¥6月度¥別ファイル¥工数管理7月度_担当A.xlsとか） ^ ^ こういう認識はなかったので、マクロで月度を進める際に、おかしな状況になっています。 コタ体系 sachiさん体系 [1] 〜¥test¥6月度 〜¥テスト¥6月度 [2] 〜¥test¥7月度 〜¥テスト¥7月度 [3] 〜¥test¥8月度 〜¥テスト¥8月度 [4] 〜¥test¥就業管理表 〜¥テスト¥就業管理表 [5] 〜¥test¥6月度¥別ファイル 〜¥テスト¥6月度¥別ファイル [6] 〜¥test¥6月度¥別ファイル¥6月度_担当A.xls 〜¥テスト¥6月度¥別ファイル¥工数管理7月度_担当A.xls [7] 〜¥test¥7月度¥別ファイル 〜¥テスト¥7月度¥別ファイル [8] 〜¥test¥7月度¥別ファイル¥7月度_担当A.xls 〜¥テスト¥7月度¥別ファイル¥工数管理8月度_担当A.xls [9] 〜¥test¥8月度¥別ファイル 〜¥テスト¥8月度¥別ファイル [10] 〜¥test¥8月度¥別ファイル¥8月度_担当A.xls 〜¥テスト¥8月度¥別ファイル¥工数管理9月度_担当A.xls [11] 〜¥test¥就業管理表¥6月度 〜¥テスト¥就業管理表¥6月度 [12] 〜¥test¥就業管理表¥7月度 〜¥テスト¥就業管理表¥7月度 [13] 〜¥test¥就業管理表¥就業管理表更新.xls 〜¥テスト¥就業管理表¥就業管理表更新.xls [14] 〜¥test¥就業管理表¥6月度¥就業管理表6月度_担当A.xls 〜¥テスト¥就業管理表¥6月度¥就業管理表7月度_担当A.xls さて、ファイル名やマクロを修正しないといけませんが、認識に齟齬があるといけませんので、再度確認しますね。 以下の2ファイルに含まれている日付データは、何月何日から何月何日までになっていますか? ・参照元ファイル:〜¥テスト¥6月度¥別ファイル¥工数管理7月度_担当A.xls →○/○〜○/○ ・参照先ファイル:〜¥テスト¥就業管理表¥6月度¥就業管理表7月度_担当A.xls →○/○〜○/○ （コタ） ---- コタ様 こちらの説明が足りず、すみませんでした。 対象範囲は下記の通りです。お願い致します。 ・参照元ファイル:〜¥テスト¥6月度¥別ファイル¥工数管理7月度_担当A.xls →6/11〜7/12 ・参照先ファイル:〜¥テスト¥就業管理表¥6月度¥就業管理表7月度_担当A.xls →6/21〜7/20 (sachi) ---- ＞ ・参照先ファイル:〜¥テスト¥就業管理表¥6月度¥就業管理表7月度_担当A.xls ＞ →6/21〜7/20 こちらのほうは認識どおりですが、 ＞ ・参照元ファイル:〜¥テスト¥6月度¥別ファイル¥工数管理7月度_担当A.xls ＞ →6/11〜7/12 6/11（金）から始まるのですか?? 以前に提示された別ファイルの構成では、F6:Y6セルが第1週目の月曜日データということでしたので、 就業管理表のAR7セルに、F6セルの日付を取得して、他の数式に使用しています。 第1週目が6/11（金）から始まるのだとしたら、F6セルにはデータが入っていないことになり、 参照自体ができなくなります。 ○ [6月度_担当A.xls]ブック【明細】シート ※「_」セルは未入力または"" [R/C] [A] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Y] [Z] [AA] [AB] [AU] [AV] [AW] [AX] [BQ] [BR] [1] _ 1 2 3 4 5 6 7 8 9 10 11 20 _ _ 1 20 _ _ 1 20 _ [6] _ 0614 ← F6 1 週 目 月 曜 日 - 6F Y6 _ _ 0615 AU6 _ _ 0616 BQ6 _ [25] _ 0621 ← F25 2 週 目 月 曜 日 - 6F Y25 _ _ 0622 AU25 _ _ 0623 BQ25 _ ○ [就業管理表.xls]ブック【管理】シート ※「_」セルは未入力または"" [R/C] [A] [T] [U] [V] [W] [X] [Y] [Z] [AF] [AG] [AH] [AI] [AJ] [AK] [AL] [AM] [AN] [AO] [AP] [AQ] [AR] [AS] [AT] [AU] [1] _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ [7] _ 日付 4 5 _ 7 8 _ _ 11 12 _ 13 14 15 16 17 18 19 20 0614 0712 _ _ [8] _ 0621 2 週 _ 月 曜 _ _ 6F Q25 _ R25 S25 T25 U25 V25 W25 X25 Y25 7 -21 20 0 [9] _ 0622 2 週 _ 火 曜 _ _ 6F AM25 _ AN25 AO25 AP25 AQ25 AR25 AS25 AT25 AU25 8 -20 20 22 就業管理表の数式を見直さないといけないので、以下の点を教えてください。 （工数管理ファイルの日付データがいつから始まるか、を正確に捉えないと、就業管理表に反映できないためです） ・6/11〜7/12だと、1ヶ月超のデータになりますが、工数管理8月度_担当A.xlsは、いつからいつまでの データが入っていますか?7/12から、7/13から、あるいはそれ以外からいつまで? ・また、工数管理ファイルは、第6週目までデータが入ることがありますか? →この質問は不要でした。6/11〜7/12のデータだと、第6週目までになりますね。（17:56追記） ・工数管理7月度_担当A.xlsのCP6セル（第1週目の金曜日）が6/11（金）の日付データだと思いますが、 その1日前にあたるBT6セル（第1週目の木曜日）には、どのようなデータが入っていますか?あるいは未入力のまま? ※数式が入っている場合は、数式と表示がどのようになっているかを教えてください。 （コタ） ---- コタ様 以前に就業管理表の締め日が違うので、2ヶ月分の工数管理ファイルからデータを 参照しているという説明をし、関数については、そのようにコタさんに対応して 頂きましたので、今はそれを活用させて頂いています。なので、これについては解決済みだと 解釈していました。 確認事項については8月度の工数管理の対象期間はイレギュラーで7/13〜8/1までとなっています。 7月度ファイルのBT6セルは対象範囲外なので、未入力のままで数式なども入れていません。 (sachi) ---- ＞今はそれを活用させて頂いています。 あれ?工数管理ファイルのF6セルが未入力なら、就業管理表はエラーになるはずですが。 もう一度確認しますが、[工数管理7月度_担当A.xls]ブックの実際のレイアウトは、 ↓のようになっているのですよね? （重要なのは、F,AB,,,CP列のどのセルに、いつの日付が入っているか、です） ○ [工数管理7月度_担当A.xls]ブック【明細】シート ※「_」セルは未入力または"" [R/C] [A] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [Y] [Z] [AA] [AB] [AH] [AU] [AV] [AW] [AX] [BD] [BQ] [BR] [1] _ 1 2 3 4 5 6 7 8 9 10 11 12 20 _ _ 1 7 20 _ _ 1 7 20 _ [6] _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ [25] _ 0614 ← F25 2 週 目 月 曜 日 - 6F 担A Y25 _ _ 0615 火 AU25 _ _ 0616 水 BQ25 _ [44] _ 0621 ← F44 3 週 目 月 曜 日 - 6F 担A Y44 _ _ 0622 火 AU44 _ _ 0623 水 BQ44 _ [63] _ 0628 ← F63 4 週 目 月 曜 日 - 6F 担A Y63 _ _ 0629 火 AU63 _ _ 0630 水 BQ63 _ [82] _ 0705 ← F82 5 週 目 月 曜 日 - 6F 担A Y82 _ _ 0706 火 AU82 _ _ 0707 水 BQ82 _ [101] _ 0712 ← F101 6 週 目 月 曜 日 - 6F 担A Y101 _ _ _ _ _ _ _ _ _ _ _ [R/C] [BS] [BT] [BZ] [CM] [CN] [CO] [CP] [CQ] [CR] [CS] [CT] [CU] [CV] [CW] [CX] [CY] [CZ] [DA] [DI] [1] _ 1 7 20 _ _ 1 2 3 4 5 6 7 8 9 10 11 12 20 [6] _ _ _ _ _ _ 0611 ← CP6 1 週 目 金 曜 日 - 6F 担A DI6 [25] _ 0617 木 CM25 _ _ 0618 ← CP25 2 週 目 金 曜 日 - 6F 担A DI25 [44] _ 0624 木 CM44 _ _ 0625 ← CP44 3 週 目 金 曜 日 - 6F 担A DI44 [63] _ 0701 木 CM63 _ _ 0702 ← CP63 4 週 目 金 曜 日 - 6F 担A DI63 [82] _ 0708 木 CM82 _ _ 0709 ← CP82 5 週 目 金 曜 日 - 6F 担A DI82 [101] _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ ＞8月度の工数管理の対象期間はイレギュラーで7/13〜8/1までとなっています。 おや、こんなケースがあるのですか。 そうすると9月度のデータは、8/2〜9/12,13頃ということですか。 対象期間の変動が激しいなら、参照範囲をもっと多めにしておきます。 （ちなみに、2ヶ月続けて対象期間が少ないことがあったら（=3ヶ月分の工数管理が必要）、 現在の数式では対応できません） ＞未入力のままで数式なども入れていません。 了解しました。ちょっとだけ数式が簡単になります。 （コタ） ---- コタ様 すみません!私が間違えていました。 工数管理の7月度ファイルの対象期間は6/11〜ですが、F6,AB6,A6,BT6セルにも 6/7〜6/10の日付のみ入力してあります。 8月度のように対象期間が短くなることは、年に2回ほどあるようですが、2ヶ月続けてと いうことは年間のスケジュールで確認した限りありませんでした。 (sachi) ---- ＞工数管理の7月度ファイルの対象期間は6/11〜ですが、F6,AB6,A6,BT6セルにも ＞6/7〜6/10の日付のみ入力してあります。 なるほど、必ずF6セルには日付が存在するのですね。 しかしそうなると、7月度ファイルの対象期間が6/11〜7/12であることや、8月度ファイルの対象期間が 7/13〜8/1であることは、どのセルの情報から判断できるのでしょうか。 例えば、 ・どこかのセルに開始日と終了日が入っている ・6/7〜6/10のように、日付のみ入力してある場合は、1つ右のセルF7,AB7,,,とかが未入力になっている とか。 （今の数式だと、7/12のデータが、8月度ファイルから参照されてしまっているかと思います） （コタ） ---- コタ様 もう1度確認したところ、確かに7/12のデータが、8月度ファイルから参照されていました。 対象期間についてですが、対象外の日付の欄については、日付以外はF7以降未入力になっています。 後は欄に斜線を引いて、対象外と分かるようにしています。 (sachi) ---- ＞対象外の日付の欄については、日付以外はF7以降未入力になっています。 了解しました。 ではF7,AB7,,,が未入力かどうかで、開始日を判定しましょう。 さて、いろいろ勘違いや早とちりやらで、お手間をとらせてしまいましたが、ようやくできたようですので、 まとめの意味も込めて、最初から記述していきますね。 -------------------------------------------------- 1.＜フォルダ等体系＞ ・〜¥test フォルダ ├「6月度」フォルダ │ └「別ファイル」フォルダ │ ├［工数管理7月度_担当A.xls］ブック :就業管理表7月度_担当A.xlsの参照元1つ目 │ └［工数管理7月度_担当B.xls］ブック ├「7月度」フォルダ │ └「別ファイル」フォルダ │ ├［工数管理8月度_担当A.xls］ブック :就業管理表7月度_担当A.xlsの参照元2つ目、かつ │ │ :就業管理表8月度_担当A.xlsの参照元1つ目 │ └［工数管理8月度_担当B.xls］ブック ├「8月度」フォルダ │ └「別ファイル」フォルダ │ ├［工数管理8月度_担当A.xls］ブック :就業管理表8月度_担当A.xlsの参照元2つ目 │ └［工数管理8月度_担当B.xls］ブック │ └「就業管理表」フォルダ ├「6月度」フォルダ │ ├［就業管理表7月度_担当A.xls］ブック :工数管理7,8月度_担当A.xlsの参照先（既存） │ └［就業管理表7月度_担当B.xls］ブック : 〃 ├「7月度」フォルダ │ ├［就業管理表8月度_担当A.xls］ブック ←これを自動作成 │ └［就業管理表8月度_担当B.xls］ブック ← 〃 │ └［就業管理表更新.xls］ブック ←更新処理マクロのはいったブック -------------------------------------------------- 2.＜ブック・シート構成＞ ・[工数管理7月度_担当A.xls]ブック └【明細】シート →「担当A」の明細表。就業管理表ブックの参照元。 ・[就業管理表7月度_担当A.xls]ブック └【担当A】シート →「担当A」の集計表（シート名は任意）。工数管理ブックの参照先。 ※[工数管理*月度_担当*.xls],[就業管理表*月度_担当*.xls]ブックは全て同様なので以下省略。 ・[就業管理表更新.xls]ブック ├【Module1】標準モジュール →就業管理表ブックを月度更新&新規作成するマクロ記述。 └【更新情報】シート →マクロに必要な情報を入力。 -------------------------------------------------- 3.＜シートレイアウト＞ まず、参照元となる工数管理ブックの明細シートです。 ・横に長いので、分割表示しています。 ・1行目の番号は、列の位置を見るためのもので、どこにも使っていません。 ・各ブロック（F:Y、AB:AU、AX:BQ、BT:CM、CP:DI）の再左列は、日付データです（表示形式mmdd）。 ○ [工数管理7月度_担当A.xls]ブック【明細】シート ・6/11(金)〜7/12(月) ただし、6/7〜6/10は日付のみ入力 ※「_」セルは未入力または"" [R/C] [A] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [Y] [Z] [AA] [AB] [AH] [AU] [AV] [AW] [AX] [BD] [BQ] [BR] [1] _ 1 2 3 4 5 6 7 8 9 10 11 12 20 _ _ 1 7 20 _ _ 1 7 20 _ [6] _ 0607 _ _ _ _ _ _ _ _ _ _ _ _ _ _ 0608 _ _ _ _ 0609 _ _ _ [25] _ 0614 ← F25 2 週 目 月 曜 日 - 6F 担A Y25 _ _ 0615 火 AU25 _ _ 0616 水 BQ25 _ [44] _ 0621 ← F44 3 週 目 月 曜 日 - 6F 担A Y44 _ _ 0622 火 AU44 _ _ 0623 水 BQ44 _ [63] _ 0628 ← F63 4 週 目 月 曜 日 - 6F 担A Y63 _ _ 0629 火 AU63 _ _ 0630 水 BQ63 _ [82] _ 0705 ← F82 5 週 目 月 曜 日 - 6F 担A Y82 _ _ 0706 火 AU82 _ _ 0707 水 BQ82 _ [101] _ 0712 ← F101 6 週 目 月 曜 日 - 6F 担A Y101 _ _ _ _ _ _ _ _ _ _ _ [R/C] [BS] [BT] [BZ] [CM] [CN] [CO] [CP] [CQ] [CR] [CS] [CT] [CU] [CV] [CW] [CX] [CY] [CZ] [DA] [DI] [1] _ 1 7 20 _ _ 1 2 3 4 5 6 7 8 9 10 11 12 20 [6] _ 0610 _ _ _ _ 0611 ← CP6 1 週 目 金 曜 日 - 6F 担A DI6 [25] _ 0617 木 CM25 _ _ 0618 ← CP25 2 週 目 金 曜 日 - 6F 担A DI25 [44] _ 0624 木 CM44 _ _ 0625 ← CP44 3 週 目 金 曜 日 - 6F 担A DI44 [63] _ 0701 木 CM63 _ _ 0702 ← CP63 4 週 目 金 曜 日 - 6F 担A DI63 [82] _ 0708 木 CM82 _ _ 0709 ← CP82 5 週 目 金 曜 日 - 6F 担A DI82 [101] _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ ○ [工数管理8月度_担当A.xls]ブック【明細】シート ・7/13(火)〜8/1(日) ただし、7/12は日付のみ入力 [R/C] [A] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [Y] [Z] [AA] [AB] [AU] [AV] [AW] [AX] [BQ] [BR] [1] _ 1 2 3 4 5 6 7 8 9 10 11 12 20 _ _ 1 20 _ _ 1 20 _ [6] _ 0712 _ _ _ _ _ _ _ _ _ _ _ _ _ _ 0713 AU6 _ _ 0714 BQ6 _ [25] _ 0719 ← F25 2 週 目 月 曜 日 - 7F 担A Y25 _ _ 0720 AU25 _ _ 0721 BQ25 _ [44] _ 0726 ← F44 3 週 目 月 曜 日 - 7F 担A Y44 _ _ 0727 AU44 _ _ 0728 BQ44 _ [R/C] [BS] [BT] [CM] [CN] [CO] [CP] [CQ] [CR] [CS] [CT] [CU] [CV] [CW] [CX] [CY] [CZ] [DA] [DI] [1] _ 1 20 _ _ 1 2 3 4 5 6 7 8 9 10 11 12 20 [6] _ 0715 CM6 _ _ 0716 ← CP6 1 週 目 金 曜 日 - 7F 担A DI6 [25] _ 0722 CM25 _ _ 0723 ← CP25 2 週 目 金 曜 日 - 7F 担A DI25 [44] _ 0729 CM44 _ _ 0730 ← CP44 3 週 目 金 曜 日 - 7F 担A DI44 ○ [工数管理9月度_担当A.xls]ブック【明細】シート ・8/2(月)〜9/13(月) [R/C] [A] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [Y] [Z] [AA] [AB] [AU] [AV] [AW] [AX] [BQ] [BR] [1] _ 1 2 3 4 5 6 7 8 9 10 11 12 20 _ _ 1 20 _ _ 1 20 _ [6] _ 0802 ← F6 1 週 目 月 曜 日 - 8F 担A Y6 _ _ 0803 AU6 _ _ 0804 BQ6 _ [25] _ 0809 ← F25 2 週 目 月 曜 日 - 8F 担A Y25 _ _ 0810 AU25 _ _ 0811 BQ25 _ [44] _ 0816 ← F44 3 週 目 月 曜 日 - 8F 担A Y44 _ _ 0817 AU44 _ _ 0818 BQ44 _ [63] _ 0823 ← F63 4 週 目 月 曜 日 - 8F 担A Y63 _ _ 0824 AU63 _ _ 0825 BQ63 _ [82] _ 0830 ← F82 5 週 目 月 曜 日 - 8F 担A Y82 _ _ 0831 AU82 _ _ 0901 BQ82 _ [101] _ 0906 ← F101 6 週 目 月 曜 日 - 8F 担A Y101 _ _ 0907 AU101 _ _ 0908 BQ101 _ [120] _ 0913 ← F120 7 週 目 月 曜 日 - 8F 担A Y120 _ _ _ _ _ _ _ _ _ [R/C] [BS] [BT] [CM] [CN] [CO] [CP] [CQ] [CR] [CS] [CT] [CU] [CV] [CW] [CX] [CY] [CZ] [DA] [DI] [1] _ 1 20 _ _ 1 2 3 4 5 6 7 8 9 10 11 12 20 [6] _ 0805 CM6 _ _ 0806 ← CP6 1 週 目 金 曜 日 - 8F 担A DI6 [25] _ 0812 CM25 _ _ 0813 ← CP25 2 週 目 金 曜 日 - 8F 担A DI25 [44] _ 0819 CM44 _ _ 0820 ← CP44 3 週 目 金 曜 日 - 8F 担A DI44 [63] _ 0826 CM63 _ _ 0827 ← CP63 4 週 目 金 曜 日 - 8F 担A DI63 [82] _ 0902 CM82 _ _ 0903 ← CP82 5 週 目 金 曜 日 - 8F 担A DI82 [101] _ 0909 CM101 _ _ 0910 ← CP101 6 週 目 金 曜 日 - 8F 担A DI101 [120] _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ ==================== 次に、参照先となる就業管理表ブックの担当者シートです。 ・T列には1ヶ月の日付データを入力しています（この場合は、6月21日開始で30日分なので、37行目まで）。 ・作業列（AR〜AU列目）を4つ、作業行（7行目）を1つ使用しますが、U8:AQ38は1つの数式をフィルコピーできます。 （歯抜けの列は、当然別の何かが入っているでしょうから、そこは避けてください） ○ [就業管理表7月度_担当A.xls]ブック【担当A】シート ※「_」セルは未入力または"" [R/C] [A] [T] [U] [V] [W] [X] [Y] [Z] [AF] [AG] [AH] [AI] [AJ] [AK] [AL] [AM] [AN] [AO] [AP] [AQ] [AR] [AS] [AT] [AU] [1] _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ [7] _ 日付 4 5 _ 7 8 _ _ 11 12 _ 13 14 15 16 17 18 19 20 0607 0713 0 1 [8] _ 0621 3 週 _ 月 曜 _ _ 6F 担A _ R44 S44 T44 U44 V44 W44 X44 Y44 14 -22 39 0 [9] _ 0622 3 週 _ 火 曜 _ _ 6F 担A _ AN44 AO44 AP44 AQ44 AR44 AS44 AT44 AU44 15 -21 39 22 [10] _ 0623 3 週 _ 水 曜 _ _ 6F 担A _ BJ44 BK44 BL44 BM44 BN44 BO44 BP44 BQ44 16 -20 39 44 [11] _ 0624 3 週 _ 木 曜 _ _ 6F 担A _ CF44 CG44 CH44 CI44 CJ44 CK44 CL44 CM44 17 -19 39 66 [12] _ 0625 3 週 _ 金 曜 _ _ 6F 担A _ DB44 DC44 DD44 DE44 DF44 DG44 DH44 DI44 18 -18 39 88 [13] _ 0626 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 19 -17 39 110 [14] _ 0627 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 20 -16 39 132 [15] _ 0628 4 週 _ 月 曜 _ _ 6F 担A _ R63 S63 T63 U63 V63 W63 X63 Y63 21 -15 58 0 [26] _ 0709 5 週 _ 金 曜 _ _ 6F 担A _ DB82 DC82 DD82 DE82 DF82 DG82 DH82 DI82 32 -4 77 88 [27] _ 0710 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 33 -3 77 110 [28] _ 0711 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 34 -2 77 132 [29] _ 0712 6 週 _ 月 曜 _ _ 6F 担A _ R101 S101 T101 U101 V101 W101 X101 Y101 35 -1 96 0 [30] _ 0713 1 週 _ 火 曜 _ _ 7F 担A _ AN6 AO6 AP6 AQ6 AR6 AS6 AT6 AU6 36 0 1 22 [31] _ 0714 1 週 _ 水 曜 _ _ 7F 担A _ BJ6 BK6 BL6 BM6 BN6 BO6 BP6 BQ6 37 1 1 44 [32] _ 0715 1 週 _ 木 曜 _ _ 7F 担A _ CF6 CG6 CH6 CI6 CJ6 CK6 CL6 CM6 38 2 1 66 [33] _ 0716 1 週 _ 金 曜 _ _ 7F 担A _ DB6 DC6 DD6 DE6 DF6 DG6 DH6 DI6 39 3 1 88 [34] _ 0717 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 40 4 1 110 [35] _ 0718 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 41 5 1 132 [36] _ 0719 2 週 _ 月 曜 _ _ 7F 担A _ R25 S25 T25 U25 V25 W25 X25 Y25 42 6 20 0 [37] _ 0720 2 週 _ 火 曜 _ _ 7F 担A _ AN25 AO25 AP25 AQ25 AR25 AS25 AT25 AU25 43 7 20 22 [38] _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 0 0 1 22 ☆ 作業行・列 AR7 =[工数管理7月度_担当A.xls]明細!$F$6 ※日付データ（当月工数管理ブックの第1週月曜日固定） AS7 =INDEX([工数管理8月度_担当A.xls]明細!$F$6:$CP$6, MATCH(1,INDEX(ISNUMBER(0/([工数管理8月度_担当A.xls]明細!$G$6:$CQ$6<>"")/(MOD(COLUMN($G$6:$CQ$6),22)=7))*1,),0)) '14:42修正 ※日付データ（翌月工数管理ブックの対象期間開始日） AT7 =WEEKDAY(AR7,3) →AT7:AU7フィルコピー AR8 =IF($T8="",0,$T8-AR$7) →AR8:AR38フィルコピー ※当月工数管理ブックの第1週月曜日との差 AS8 =IF($T8="",0,$T8-AS$7) →AS8:AS38フィルコピー ※翌月工数管理ブックの開始日との差 AT8 =INT(IF(AS8<0,AR8+AT$7,AS8+AU$7)/7)*19+1 →AT8:AT38フィルコピー ※参照元の表内での行位置 AU8 =MOD(IF(AS8<0,AR8+AT$7,AS8+AU$7),7)*22 →AU8:AU38フィルコピー ※参照元の表内での行位置 ※作業行・列の場所がすでに使用中なら、好きなところに移動してください。 ☆ メイン U8 =IF(WEEKDAY($T8,3)>=5,"",INDEX(IF($AS8<0,[工数管理7月度_担当A.xls]明細!$F$6:$FA$101, [工数管理8月度_担当A.xls]明細!$F$6:$FA$101),$AT8,$AU8+U$7)) →U8:AQ38フィルコピー ==================== 最後に、更新マクロのある就業管理表更新ブックの更新情報シートです。 ・入力するのは、B2セル（更新前の当月:この例では6）のみです。 ・【更新結果】の部分（15行目以降）は、更新先ブックがあるかないかで、既存/作成のどちらかが出力されます。 （更新先ブックがある場合は、更新処理そのものを行いません。） ○ [就業管理表更新.xls]ブック【更新情報】シート ※「_」セルは未入力または"" [R/C] [A] [B] [C] [1] 【更新月】 入力↓ _ [2] 1:更新前 6 月から [3] 2:更新後 7 月へ [4] 3:翌々月 8 月 [5] 4:3ヶ月後 9 月 [6] 【参照元】 _ _ [7] 1:別ファイル1 ¥6月度¥別ファイル¥ 工数管理7月度_担当*.xls [8] 2:別ファイル2 ¥7月度¥別ファイル¥ 工数管理8月度_担当*.xls [9] 3:別ファイル3 ¥8月度¥別ファイル¥ 工数管理9月度_担当*.xls [10] 【参照先】 _ _ [11] 4:就業管理表1 ¥就業管理表¥6月度¥ 就業管理表7月度_担当*.xls [12] 5:就業管理表2 ¥就業管理表¥7月度¥ 就業管理表8月度_担当*.xls [13] _ _ _ [14] 【更新結果】 更新元 更新先 [15] 作成 就業管理表7月度_担当A.xls 就業管理表8月度_担当A.xls [16] 作成 就業管理表7月度_担当B.xls 就業管理表8月度_担当B.xls B3 =MOD(B2,12)+1 →B3:B5フィルコピー B7 ="¥" & B2&"月度¥別ファイル¥" →B7:B9フィルコピー C7 ="工数管理"&B3&"月度_担当*.xls" →C7:C9フィルコピー B11 ="¥就業管理表¥" & B2&"月度¥" →B11:B12フィルコピー C11 ="就業管理表"&B3&"月度_担当*.xls" →C11:C12フィルコピー -------------------------------------------------- 4.＜マクロコード＞ ○ [就業管理表更新.xls]ブック【Module1】標準モジュール ・マクロは以下の3つに分かれています。 Public Sub 更新処理() :更新処理の実行マクロ Private Sub ExistCheck(〜) :同じ処理を外出し Private Sub ファイル更新(〜) :更新処理のメイン ↓ここから Const moto As String = "工数管理" Const saki As String = "就業管理表" '==================== '更新処理実行 Public Sub 更新処理() Dim fso As Object, ofi As Object Dim v As Variant Dim ws As Worksheet Dim dt As Date Dim i As Integer, j As Integer, d As Integer, n As Integer Dim m(1 To 4) As Integer '当月,翌月,翌々月,3ヶ月後 Dim g(1 To 4) As String '*月度 Dim s As String, t As String Dim st As String '親フォルダ名 Dim sfo(1 To 5) As String 'フォルダパス Dim ofo4 As Object Dim SName As String, BName As String Dim paths() As String, pth(1 To 5) As String 'ファイルパス '実行確認 s = "更新関連ファイルは、全て閉じてありますか?" & vbLf _ & "（就業管理表、工数管理など）" If MsgBox(s, vbYesNo + vbDefaultButton2) = vbNo Then Exit Sub '画面&計算停止 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual '初期設定 Set fso = CreateObject("Scripting.FileSystemObject") Set ws = ActiveSheet v = ws.Range("B1:B12").Value For i = 1 To 4 m(i) = v(i + 1, 1) '月 g(i) = m(i) & "月度" '月度 Next st = fso.GetParentFolderName(ThisWorkbook.Path) '親フォルダ名 'フォルダ&ファイル存在チェック s = "" For i = 1 To 5 sfo(i) = st & v(Array(7, 8, 9, 11, 12)(i - 1), 1) 'フォルダパス If Dir(sfo(i), vbDirectory) = "" Then s = s & sfo(i) & vbLf Next Call ExistCheck(s, "フォルダ") s = "" Set ofo4 = fso.GetFolder(sfo(4)) n = ofo4.Files.Count If n = 0 Then s = sfo(4) & vbLf Call ExistCheck(s, "ファイル") ReDim paths(1 To n, 1 To 5) s = "": i = 0 For Each ofi In ofo4.Files i = i + 1 paths(i, 4) = ofi.Path '就業表当月パス paths(i, 5) = Replace(paths(i, 4), "¥" & g(1) & "¥" & saki & g(2), _ "¥" & g(2) & "¥" & saki & g(3)) '就業表翌月パス SName = fso.GetFileName(paths(i, 4)) '就業表名 BName = Replace(SName, saki & g(2), "") '_担当名.xls For j = 1 To 3 paths(i, j) = sfo(j) & moto & g(j + 1) & BName '別ファパス If Dir(paths(i, j), vbNormal) = "" Then s = s & paths(i, j) & vbLf Next Next Call ExistCheck(s, "ファイル") '更新処理 ws.Range("A15", ws.Range("C1000").End(xlUp).Offset(1)).ClearContents For i = 1 To n Application.StatusBar = i & "/" & n & "番目処理中・・・" With ws.Cells(14 + i, 1) .Offset(, 1).Value = fso.GetFileName(paths(i, 4)) .Offset(, 2).Value = fso.GetFileName(paths(i, 5)) If Dir(paths(i, 5), vbNormal) = "" Then For j = 1 To 5 pth(j) = paths(i, j) Next Call ファイル更新(m(1), g, pth) .Value = "作成" Else .Value = "既存" End If End With Next '終了設定 Set fso = Nothing Application.StatusBar = False Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub '==================== '同じ処理を外出し Private Sub ExistCheck(ByVal s As String, ByVal t As String) If s <> "" Then MsgBox s & vbLf & t & "が存在しないため、マクロを終了します。" Application.Calculation = xlCalculationAutomatic '←8/6追加 End End If End Sub '==================== '更新処理のメイン Private Sub ファイル更新(ByVal m1 As Integer, ByRef g() As String, _ ByRef pth() As String) Dim wb(1 To 4) As Workbook Dim ws As Worksheet Dim dt As Date Dim i As Integer, d As Integer 'ファイルオープン For i = 1 To 4 Set wb(i) = Workbooks.Open(pth(i), ReadOnly:=True) Next Set ws = wb(4).Worksheets(1) '就業表当月 dt = ws.Range("T8").Value '当月21日 '月度更新 ws.Range("T8").Value = DateSerial(Year(dt), m1 + 1, 21) '翌月21日 With ws.Range("T7:AS38") .Replace What:=moto & g(3), Replacement:=moto & g(4), _ LookAt:=xlPart, MatchCase:=False, MatchByte:=False .Replace What:=moto & g(2), Replacement:=moto & g(3) End With '日付増減 ws.Range("T9:T38").Formula = "=T8+1" d = Day(DateSerial(Year(dt), m1 + 2, 0)) '翌月の日数 If d < 31 Then ws.Range("T8").Offset(d).Resize(31 - d).ClearContents 'ファイルクローズ For i = 1 To 3 wb(i).Close False Next wb(4).SaveAs (pth(5)) wb(4).Close False End Sub ↑ここまで まずは、工数管理ブックと就業管理表ブックのリンクを確実なものにしてから、 おもむろにマクロを実行してみてくださいね。 （コタ） ---- コタ様 こちらの説明不足等々、余計なお手間をおかけしてすみません。 就業管理表の数式を直しているところのなのですが、下記の式でつまづきました。 AS7 =INDEX([工数管理8月度_担当A.xls]明細!$F$6:$CP$6,MATCH(0,INDEX(ISERROR(0/[工数管理8月度_担当A.xls]明細!$F$6:$CP$6/(MOD(COLUMN($F$6:$CP$6),22)=6))*1,),0)) 式を入力したところ1/2と表示されメインの数式が入っているセルがすべてエラー表示になりました。 工数管理表のF6:CP6は表示形式は日付で7/12というような表記の仕方にしています。 就業管理表のAS7欄も同じ表示形式ですが、これで問題ありませんか? ISERROR以降の式の意味がよく理解できていないのですみませんが、あわせて教えて下さい。 (sachi) ---- ＞AS7 =INDEX([工数管理8月度_担当A.xls]明細!$F$6:$CP$6,MATCH(0,INDEX(ISERROR(0/[工数管理8月度_担当A.xls]明細!$F$6:$CP$6/(MOD(COLUMN($F$6:$CP$6),22)=6))*1,),0)) あー、ごめんなさい。昔の数式のままでUPしてしまいました。 正しくは、↓です。 AS7 =INDEX([工数管理8月度_担当A.xls]明細!$F$6:$CP$6,MATCH(1,INDEX(ISNUMBER(0/([工数管理8月度_担当A.xls]明細!$G$6:$CQ$6<>"")/(MOD(COLUMN($G$6:$CQ$6),22)=7))*1,),0)) （上で挙げた分も修正しておきました） ＞式を入力したところ1/2と表示され ただちょっと気になるのは、昔の数式で確認したところ、こちらでは7/12の日付が返ってきました（ほしいのは7/13です）。 修正後の数式で正しく7/13になれば、問題はないのですが。 ＞ 就業管理表のAS7欄も同じ表示形式ですが、これで問題ありませんか? これは表示形式の違いだけですので、問題ありません。 （コタ） ---- コタ様 修正した数式で試してみましたが、AS7の値が#N/Aになってしまいました。 式の中で明細!$G$6:$CQ$6を参照していますが、工数管理表の日付欄はF6:H6というように3つのセルを 結合して日付表示をしているのですが、これが影響しているのでしょうか? (sachi) ---- ＞工数管理表の日付欄はF6:H6というように3つのセルを 結合して日付表示をしているのですが セル結合すると、一番左のセル（この場合はF6）以外は未入力になりますので、7/12(月)〜7/16(金)全てについて 日付セルの1つ右セルが未入力、という状態になっていたためです。 日付セルの3つ右セル（F6→I6）で判定すればだいじょうぶと思いますので、 AS7 =INDEX([工数管理8月度_担当A.xls]明細!$F$6:$CP$6, MATCH(1,INDEX(ISNUMBER(0/([工数管理8月度_担当A.xls]明細!$I$6:$CS$6<>"")/(MOD(COLUMN($I$6:$CS$6),22)=9))*1,),0)) この数式でやってみてください。 （コタ） ---- コタ様 式を修正したところ、正しく値が表示されました。 マクロの方も試したところ、就業管理表の8月度ファイルの作成とメインの式の更新はうまく いきました。が、AS7のセルのみ式の更新が出来ていなくて7月度ファイルと同じ7/13と表示 されました。これはマクロのどこを修正したらいいのでしょうか? 質問ばかりですみません、よろしくお願いします。 (sachi) ---- ＞就業管理表の8月度ファイルの作成とメインの式の更新はうまく いきました。が、AS7のセルのみ式の更新が出来ていなくて むむ?ちょっと考えにくい状況です。 更新前の数式は、それぞれ以下のようになっています。 U8 =IF(WEEKDAY($T8,3)>=5,"",INDEX(IF($AS8<0,[工数管理7月度_担当A.xls]明細!$F$6:$FA$101, [工数管理8月度_担当A.xls]明細!$F$6:$FA$101),$AT8,$AU8+U$7)) →U8:AQ38フィルコピー AS7 =INDEX([工数管理8月度_担当A.xls]明細!$F$6:$CP$6, MATCH(1,INDEX(ISNUMBER(0/([工数管理8月度_担当A.xls]明細!$I$6:$CS$6<>"")/(MOD(COLUMN($I$6:$CS$6),22)=9))*1,),0)) これらの数式をマクロの以下の部分で、月度更新しています。 With ws.Range("T7:AS38") .Replace What:=moto & g(3), Replacement:=moto & g(4), _ LookAt:=xlPart, MatchCase:=False, MatchByte:=False .Replace What:=moto & g(2), Replacement:=moto & g(3) End With なんとなく分かるかと思いますが、Range("T7:AS38")というのは、セル範囲T7:AS38のことですので、 メインもAS7も含まれており、まとめて更新しています。 なので、メインの更新がうまくいって、AS7が更新されないという状況は考えにくいのです。 更新後の［就業管理表8月度_担当A.xls］ブックについて、AS7セルの数式はどうなっていますか? （[工数管理8月度_担当A.xls]か、[工数管理9月度_担当A.xls]か） （コタ） ---- コタ様 上の説明で理解できました。AR欄を備考欄として使っているため、教えて頂いたコードの Range("T7:AS38")をRange("T7:AT38")に直してうまくいきました。 私の拙い説明に辛抱強く付き合って頂いて大変ありがとうございました。おかげで毎月困っていた 処理が時間短縮できます。 これから教えて頂いたマクロのコードを勉強してみます。 (sachi)... - http://www.excel.studio-kazu.jp/wiki/kazuwiki/201007/20100722103416.txt - [detail] - similar
PREV
NEXT
Powered by Hyper Estraier 1.4.13, with 97008 documents and 608113 words.
訪問者：