Excelのワークシート内の二つのシートのセルの値（文字列）を共有する

質問

Excelのワークシート内の二つのシートのセルの値（文字列）を共有する方法を教えて下さい。
具体的には、ワークシート内に｜31日｜30日｜・・・｜2日｜1日｜7月｜とタブがあります。
7月のシートのA1が1（日付）のとき、B1にあらかじめ入力した1日のシートのB1（文字列）を
表示させる方法があるのでしょうか。
7月のB1セルに直接=1日!B1と入力すると出来たのですがこれだとA1が28（日付）の時も1日のB1を表示させてしまいうまくいきません。
7月のシートA1の値28（日付）の時に28日のシートのB1、A1の値11（日付）の時は11日のシートのB1を表示という具合にしたいのですが初心者なのでうまくできません。
どなたか詳しく教えていただけませんか。
宜しくお願い致します。

回答No.7

hananoppo · ベストアンサー

ユーザー定義関数は通常、直接参照しているセルの値が変更されないと再計算されません。この場合だと、日付セルの値が変更されれば再計算されるのですが。
対処方法ですが、Ctrl+Alt+F9キーを押すと強制的に再計算されます。

これが面倒であれば、裏技を使う方法があります。
ユーザー定義関数の1行目を次のように変更します。

Function 日付別参照(日付セル As Range, 参照セル As String, 現在 As Date)

7月集計シートのH79、H80、H81、I79、I80、I81には、次の数式を入力します。

H79 =日付別参照(A79,"M11",NOW())
H80 =日付別参照(A79,"M12",NOW())
H81 =日付別参照(A79,"M13",NOW())
I79 =日付別参照(A79,"O11",NOW())
I80 =日付別参照(A79,"O12",NOW())
I81 =日付別参照(A79,"O13",NOW())

NOW関数は常に再計算される関数ですから、これをダミーとして数式に含めれば、どこかのセルの値が変更される度に再計算されます。

お礼

補足で何度も教えていただきありがとうございます。
丁寧に教えていただきまして本当に助かりました。
また何かわからない事があったときに投稿しますので
そのときは宜しくお願いできれば幸いです。
どうもありがとうございました。

補足

回答ありがとうございます。

早速回答のとおり入力したらうまくいきました。

再計算を行う関数があったとは知りませんでした。

今の状態で7月集計シートの入力内容を27日シート

に出力させるという逆のことはできますか？

例として7月集計シートの27日欄のC19,C20,C21と

D19,D20,D21に入力したものを27日シートのM16,

M17,M18とO16,O17,O18に出力させ、7月集計シー

トの28日欄に入力したものは28日シートに出力する

という具合にしたいのですがどのようにしたらよい

でしょうか。宜しくお願い致します。

回答No.6

hananoppo

再度、ユーザー定義関数を変更します。

Function 日付別参照(日付セル As Range, 参照セル As String)
Dim 日付 As Integer

日付 = Day(日付セル.Value)
日付別参照 = Worksheets(CStr(日付) & "日").Range(参照セル).Value
End Function

7月集計シートのH79、H80、H81、I79、I80、I81には、次の数式を入力します。

H79 =日付別参照(A79,"M11")
H80 =日付別参照(A79,"M12")
H81 =日付別参照(A79,"M13")
I79 =日付別参照(A79,"O11")
I80 =日付別参照(A79,"O12")
I81 =日付別参照(A79,"O13")

補足

再度ご指導ありがとうございます。

ユーザー定義関数を変更して7月集計シートに数式を入力したら

表示するようになりました。

でも、27日シートに入力しても表示されずに7月集計シートのH79

をダブルクリックしてEnterを押さないと表示しないのですが

27日シートに入力して7月集計シートを開いたら表示されている

ようにはできないでしょうか。

よい方法があれば教えていただきたいです。

宜しくお願い致します。

回答No.5

hananoppo

おかしいですね。#VALUE!になるはずはないのですが。
2010/7/1や2010/7/31と入力してもそうなるのでしょうか？

補足

初心者のわたしにいろいろ教えていただきありがとうございます。

補足の内容はわたしのミスでしたすみません。セルの表示形式がユーザー定義

になっていたので日付にしたらうまくいきました。

ずれるのも、シートが一つ余計に入っていたためでした。本当にすみません。

もう一つ教えていただきたいのですが、1日につき表示させたいセルが複数あり

例として、27日シートに入力したM11,M12,M13とO11,O12,O13を7月集計シートのH79,

H80,H81とI79,I80,I81の2列、3行に出力させるには

Case 27
参照セル = "M11"

をどのように変更すればよいでしょうか。

宜しくお願いします。

回答No.4

hananoppo

日付形式に対応させる場合は、5行目を次のように変更します。

日付 = Day(日付セル.Value)

お礼

初心者のわたしにいろいろ教えていただきありがとうございます。

補足の内容はわたしのミスでしたすみません。セルの表示形式がユーザー定義

になっていたので日付にしたらうまくいきました。

ずれるのも、シートが一つ余計に入っていたためでした。本当にすみません。

もう一つ教えていただきたいのですが、1日につき表示させたいセルが複数あり

例として、27日シートに入力したM11,M12,M13とO11,O12,O13を7月集計シートのH79,

H80,H81とI79,I80,I81の2列、3行に出力させるには

Case 27
参照セル = "M11"

をどのように変更すればよいでしょうか。

宜しくお願いします。

補足

回答ありがとうございます。

早速5行目の　日付 = 日付セル.Value を　日付 = Day(日付セル.Value) に

変更しましたが7月集計シートのH79は#VALUE!になってしまいます。

直接7月集計シートのセルA79に27と入力する(表示は日付形式なので数式バーには

1900/1/28と表示されています）とシート27日ではなく1日ずれたシート28日のセル

M11が表示されます。

7月集計シートの日付は旬日ごとのとりまとめなのでUserFormの入力から反映する

ようにしているので2010/7/27のときにシート27日のM11が表示できるようにした

いので宜しくお願いします。

回答No.3

hananoppo

まず、Visual Basic Editorを開きます。
左側にあるVBAProject (ファイル名)を右クリックし、挿入→標準モジュールの順にクリックします。Module1という標準モジュールが作成される筈です。ユーザー定義関数はここに定義します。

条件に合うようにユーザー定義関数を変更しました。

Function 日付別参照(日付セル As Range)
Dim 日付 As Integer
Dim 参照セル As String

日付 = 日付セル.Value
Select Case 日付
Case 1
参照セル = "B1"
Case 2
参照セル = "B1"
Case 3
参照セル = "B1"
Case 4
参照セル = "B1"
Case 5
参照セル = "B1"
Case 6
参照セル = "B1"
Case 7
参照セル = "B1"
Case 8
参照セル = "B1"
Case 9
参照セル = "B1"
Case 10
参照セル = "B1"
Case 11
参照セル = "B1"
Case 12
参照セル = "B1"
Case 13
参照セル = "B1"
Case 14
参照セル = "B1"
Case 15
参照セル = "B1"
Case 16
参照セル = "B1"
Case 17
参照セル = "B1"
Case 18
参照セル = "B1"
Case 19
参照セル = "B1"
Case 20
参照セル = "B1"
Case 21
参照セル = "B1"
Case 22
参照セル = "B1"
Case 23
参照セル = "B1"
Case 24
参照セル = "B1"
Case 25
参照セル = "B1"
Case 26
参照セル = "B1"
Case 27
参照セル = "M11"
Case 28
参照セル = "B1"
Case 29
参照セル = "B1"
Case 30
参照セル = "B1"
Case 31
参照セル = "B1"
End Select
日付別参照 = Worksheets(CStr(日付) & "日").Range(参照セル).Value
End Function

Function～End FunctionをModule1にコピーしてください。
なお、27日以外は参照セルを仮に"B1"としています。実際の参照セルに変更してください。

7月集計シートのH79には、次の数式を入力します。
=日付別参照(A79)

補足

回答の通りに入力したら、うまくできました。

ありがとうございます。

もう一つ教えて下さい。

日付ですが、日付形式（2010/07/27）だと

「#VALUE!」と表示されますが、日付形式のときは

どのようなユーザー定義関数になりますか。

何度もお手数掛けますが、宜しくお願いいたします。

回答No.2

hananoppo

ANo.1を訂正します。

「もし7月のC1セルに各月のC1セルを表示させたいときは」は「もし7月のC1セルに各日のC1セルを表示させたいときは」の誤りでした。

回答No.1

hananoppo

VBAで次のようなユーザー定義関数を作成します。

Function 日付別参照(日付セル As Range, 参照列番号 As Long) As String

日付別参照 = Worksheets(CStr(日付セル.Value) & "日").Cells(日付セル.Row, 参照列番号).Value

End Function

7月のB1セルには、次の数式を入力します。
=日付別参照(A1,2)

もし7月のC1セルに各月のC1セルを表示させたいときは、次の数式を入力します。
=日付別参照(A1,3)

また、これらの数式を2行目以降にコピーすれば、各々の行の参照もできます。

お礼

回答ありがとうございます。
初心者のため理解できないのでもう少し教えてください。
ユーザー定義関数はどこで作成したらよいのでしょうか？
Visual Basic Editorの7月集計に
Function 日付別参照(日付セル As Range, 参照列番号 As Long) As String
日付別参照 = Worksheets(CStr(日付セル.Value) & "日").Cells(日付セル.Row, 参照列番号).Value
End Functionを入力して7月集計シートのセルB1に=日付別参照(A1,2)と入力したら「NAME?」と
表示されうまくいかないので実際のシート・セル名でもう一度教えていただけませんか。

２７日の場合、日付は7月集計シートのセルA79です。27日シートのセルM11を7月集計シートの
H79に表示させたい場合についてもう一度よろしくお願いします。