エクセル)日付を取得しファイルを検索してセルの値をコピー

質問

初めまして。
会社で急にエクセルを扱うことになり、本などを買って勉強中な初心者です。
今の私では難しすぎるので質問させてください。
----------------------------------------------------------------
C:\AAA\本数集計値.xlsのaシート
......A.....B.....C.....D.....E......F......G......H
1...2009........9
2............3......4......5......6......7......8......9
3
4
5............10....11...12...13...14....15....16

本数集計値.xlsのaシートは上の図のようになっていて、A1に年、C1に月、B2～H2までは日にちを手入力で入力してあります。
ただし、B列には、週の頭として必ず木曜日が入ります。
このファイルには4週分の日付が入力してあります。4週過ぎたら日付だけ変えて再利用しています。

C:\BBB\第XX週本数計測値.xlsのbシート
....A.....B.....C.....D.....E.....F.....G.....H.....I
1..................9
2..................3......4......5.....6......7.....8.....9
3..................xx
4
5..................yy

第XX週本数計測値.xlsのbシートは上の図のようになっていて、C1に月、C2～I2までは日にちが入ります。
こちらも、週の頭は木曜になっています。
XXには"C1"月"C2"日から"I2"日までがその年の第何週目か、を入力してあります。
BBBフォルダには第01週本数計測値.xlsから数字が違うだけのファイルを毎週作っています。
xx、yyには3～5桁の数字が入ります。

■質問
本数集計値.xlsの各セルに手入力された年月日から、何週目かを取得し、その週のファイルをC:\BBB\から検索し、該当する第XX週本数計測値.xlsのbシートの値、xx、yyを本数集計値.xlsのaシートの同じ日付の所定のセルにコピーしたいのです。(例えば9/3は36週目なので第36週本数計測値.xlsをBBBフォルダから検索し、そのbシートのxx、yyをコピーし、本数集計値.xlsの9/3の真下のセルB3にxx、B4にyyとペーストしたいのです)

長々と書きましたが、こんなことができそうな気がするのですが、
どうかご教授の程よろしくお願い申し上げます。

回答No.3

cistronezk · ベストアンサー

回答２の修正です。

＞ただし、B列には、週の頭として必ず木曜日が入ります。
通常の数え方の第XX週と、木曜を週初とする数え方は微妙に違うのですね。しかし、木曜日に関しては通常の数え方と同じでいいと思うので
修正前：WEEKNUM(DATEVALUE($A$1&"/"&$B$1&"/"&B2))
修正後：WEEKNUM(DATEVALUE($A$1&"/"&$B$1&"/"&$B2))
として、その週のセルでは木曜日の日付のWEEKNUMを計算すればいいでしょう。修正前は、日付文字列を「$A$1&"/"&$B$1&"/"&B2」ということで当日の日付から第XX週を算出していましたが、修正後は「$A$1&"/"&$B$1&"/"&$B2」
としてどのセルでもB列の木曜の日付を見るようにします。

つまり
=INDIRECT("'C:\BBB\[第"&WEEKNUM(DATEVALUE($A$1&"/"&$B$1&"/"&$B2))&"週本数計測値.xls]b'"&ADDRESS(3,COLUMN()+1))
=INDIRECT("'C:\BBB\[第"&WEEKNUM(DATEVALUE($A$1&"/"&$B$1&"/"&$B2))&"週本数計測値.xls]b'"&ADDRESS(5,COLUMN()+1))

以上です。

お礼

cistronezkさん、ご回答ありがとうございます。
関数だけでもここまでできるのですね。

まだ全部入力ししてはいませんが思っているのにかなり近い動作にできそうです。
ありがとうございます。

回答No.2

cistronezk

ファイルは違えど、参照するのはｂシートの３行目と５行目ということでしょうか。もしそうなら本数集計値.xlsのaシートにおいて
Ｂ３：
='C:\BBB\[第36週本数計測値.xls]b'$C$3
Ｂ４：
='C:\BBB\[第36週本数計測値.xls]b'$C$5
という参照式を入力する方法もあります。
１セルずつ入力するのは大変なので、関数で動的に参照式を作成する方法を示します。VBAほどではありませんが、ある程度は作業量が減ると思います。

（１）第ＸＸ週の取得方法
・WEEKNUM関数を使用します（使用するにはアドインで「分析ツール」にチェック）
=WEEKNUM(DATEVALUE($A$1&"/"&$B$1&"/"&B2))
とすることで第何週か取得可能。以上から参照先セルの文字列は
Ｂ３：
="'C:\BBB\[第"&WEEKNUM(DATEVALUE($A$1&"/"&$B$1&"/"&B2))&"週本数計測値.xls]b'"&ADDRESS(3,COLUMN()+1)
Ｂ４：
="'C:\BBB\[第"&WEEKNUM(DATEVALUE($A$1&"/"&$B$1&"/"&B2))&"週本数計測値.xls]b'"&ADDRESS(5,COLUMN()+1)
で作成できます。両式の違いは、Address関数の第1引数が３（行目）か５（行目）かの違いだけです。この違いを吸収してひとつの式だけにする方法もあります。ご自分でやってみてください。

（２）参照文字列から実際の値の取得方法
・INDIRECT関数を使用します。
=INDIRECT("'C:\BBB\[第"&WEEKNUM(DATEVALUE($A$1&"/"&$B$1&"/"&B2))&"週本数計測値.xls]b'"&ADDRESS(3,COLUMN()+1))
=INDIRECT("'C:\BBB\[第"&WEEKNUM(DATEVALUE($A$1&"/"&$B$1&"/"&B2))&"週本数計測値.xls]b'"&ADDRESS(5,COLUMN()+1))

以上をB3、B4に入力したらこの２セルを選択して他のセルにコピペします。
必要なら、全体を選択してコピペで値貼り付けをします。

回答No.1

rivoisu

できるけれどExcelの初心者では難しいと思います。

せめてExcelはよく使っているがVBAは使ったことがないというレベルなら
ここでの質問である程度の回答を得ることができるかもしれません。
どっちにしてもVBAが必要なようです。
ある程度自分で作ってみてそのうえでの質問をされたほうが解決が近いのではないでしょうか。

年月日から何週目かを計算する
曜日の求め方
その数値をもとにファイル名文字列を生成してファイルを開く

などExcelの初心者にとってはいささかハードルが高いかもしれませんが
頑張ってください。

お礼

早速のご回答ありがとうございます。
Excelの関数は少し事務で使ったことはあったので少しはわかるのですが、VBAはさっぱりわからず、辞典やいろいろな事例が載っている本を数冊買って読んだり試したりしているのですが、難しいです（苦笑）

>>年月日から何週目かを計算する
>>曜日の求め方
>>その数値をもとにファイル名文字列を生成してファイルを開く

もう少し本をめくってがんばってみようと思います
ありがとうございます