エクセルで別シートの情報の拾う方法

質問

エクセル2007で、データシートの情報を別のカレンダーシートへの転記する方法をご教示頂けますでしょうか？独学で調べていましたが良い方法が見つけられませんでした。

【エクセルの構造】（添付通り）
(1)案件シート
A列：顧客名、B列：（案件の）連番、C列：案件名、D列：客先書類番号、E列：締切、F列：発注締切
*G列以降に商品名、個数を入力する予定。複数種類の商品を要求される案件は複数行使用。

(2)カレンダーシート
A列：日付、B列：曜日、C列：分類、D列：顧客名、E列：案件名
*C列は条件付書式で締切は青、発注締切は赤文字になるよう色分け済み

【作業】
(1)のシートのE、F列に該当する日付を(2)のカレンダーシートから探し
(2)のシートのC列（分類に）締切（E2）か発注締切（F2）をインプット
D、E列には顧客名、案件名をインプットする。
ただし案件名の無いものは客先書類番号をインプットする。

【問題】
恐縮ながら対処法が全く浮かびません…。
sumproductかvlookupでデータを拾おうとしましたが、
条件分けがうまく出来ない、
ある日付に複数の案件の締切が重なる場合に両方を拾うことが出来ず挫折しました…。
VBAはなんとか読めるものの、自力で複雑なVBEを書くのは厳しいというレベルです。
VBAでしたら対応可能でしょうか？

良い対処法がありましたら何卒ご教示頂きたく宜しくお願い致します。

回答No.3

kagakusuki · ベストアンサー

回答番号:ANo.2の続きです。

　次に、Sheet2のD3セルに次の数式を入力して下さい。

D3=IF(ROWS($2:3)-MATCH(MAX($A$3:$A3),$A$3:$A3)>COUNTIF(Sheet1!$E:$F,MAX($A$3:$A3)),"",INDEX(Sheet1!$A:$A,SUMPRODUCT(ROW(OFFSET(Sheet1!$E$1,,,MATCH(9^9,Sheet1!$E:$E)))*((OFFSET(Sheet1!$E$1,,,MATCH(9^9,Sheet1!$E:$E))=MAX($A$3:$A3))+(OFFSET(Sheet1!$F$1,,,MATCH(9^9,Sheet1!$E:$E))=MAX($A$3:$A3))>0)*((COUNTIF(OFFSET(Sheet1!$E$1,,,ROW(OFFSET(Sheet1!$E$1,,,MATCH(9^9,Sheet1!$E:$E))),2),MAX($A$3:$A3))-(OFFSET(Sheet1!$E$1,,,MATCH(9^9,Sheet1!$E:$E))=OFFSET(Sheet1!$F$1,,,MATCH(9^9,Sheet1!$E:$E)))=ROWS($2:3)-MATCH(MAX($A$3:$A3),$A$3:$A3))+(COUNTIF(OFFSET(Sheet1!$E$1,,,ROW(OFFSET(Sheet1!$E$1,,,MATCH(9^9,Sheet1!$E:$E))),2),MAX($A$3:$A3))=ROWS($2:3)-MATCH(MAX($A$3:$A3),$A$3:$A3))>0))))

　次に、Sheet2のE3セルに次の数式を入力して下さい。

=IF(ROWS($2:3)-MATCH(MAX($A$3:$A3),$A$3:$A3)>COUNTIF(Sheet1!$E:$F,MAX($A$3:$A3)),"",INDEX(Sheet1!$C:$C,SUMPRODUCT(ROW(OFFSET(Sheet1!$E$1,,,MATCH(9^9,Sheet1!$E:$E)))*((OFFSET(Sheet1!$E$1,,,MATCH(9^9,Sheet1!$E:$E))=MAX($A$3:$A3))+(OFFSET(Sheet1!$F$1,,,MATCH(9^9,Sheet1!$E:$E))=MAX($A$3:$A3))>0)*((COUNTIF(OFFSET(Sheet1!$E$1,,,ROW(OFFSET(Sheet1!$E$1,,,MATCH(9^9,Sheet1!$E:$E))),2),MAX($A$3:$A3))-(OFFSET(Sheet1!$E$1,,,MATCH(9^9,Sheet1!$E:$E))=OFFSET(Sheet1!$F$1,,,MATCH(9^9,Sheet1!$E:$E)))=ROWS($2:3)-MATCH(MAX($A$3:$A3),$A$3:$A3))+(COUNTIF(OFFSET(Sheet1!$E$1,,,ROW(OFFSET(Sheet1!$E$1,,,MATCH(9^9,Sheet1!$E:$E))),2),MAX($A$3:$A3))=ROWS($2:3)-MATCH(MAX($A$3:$A3),$A$3:$A3))>0))))

　次に、Sheet2のB3～E3の範囲をコピーして、Sheet2のB4～E4の範囲に貼り付けて下さい。
　次に、Sheet2のA4～E4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　後は、Sheet2のA3セルに、

2011/2/2

という具合に、年月日（必ず年も入力して下さい）を入力すると、自動的にカレンダー表が、表示されます。（A3セルに「2011/2/2」と入力しますと、セルのデータは自動的に「2011/2/2」を表すシリアル値自動的に変換され、表示自体は「2/2」と表示されます）
　尚、複数の案件が重なる日の場合は、自動的に次の日の日付を表示する行をずらして、複数行に渡って案件が表示されます。

お礼

kagakusuki様
ご回答ありがとうございます！！
お礼が遅くなり大変失礼致しました。

offsetやrow関数にこんな使用方法があると知らず知識不足でした。。
ご教示頂きありがとうございます！
教えて頂いた数式をもとにエクセルを修正して利用したいと思います。

回答No.2

kagakusuki

関数を使った方法です。
　今仮に、(1)のシートのシート名がSheet1、(2)のシートのシート名がSheet2であるものとします。

　まず、Sheet2のA3セルとA4セルの書式設定をm/dにして下さい。
　その方法は、次の通りです。

Sheet2のA3～A4の範囲を選択
　　↓
選択範囲を示している黒い太枠の内側にカーソルを合わせ、マウスを右クリック
　　↓
現れた選択肢の中から、[セルの書式設定]を選択してクリック
　　↓
現れた[セルの書式設定]ウィンドウの[表示形式]タグをクリック
　　↓
[分類]欄の選択肢の中から、[日付]を選択してクリック
　　↓
[種類]欄の選択肢の中から

3/14

或いは、月/日形式で記されている日付のものを選択してクリック
　　↓
[セルの書式設定]ウィンドウのOKボタンをクリックする

　次に、Sheet2のB3セルの書式設定をaaaにして下さい。
　その方法は、次の通りです。

Sheet2のB3セルを選択
　　↓
選択範囲を示している黒い太枠の内側にカーソルを合わせ、マウスを右クリック
　　↓
現れた選択肢の中から、[セルの書式設定]を選択してクリック
　　↓
現れた[セルの書式設定]ウィンドウの[表示形式]タグをクリック
　　↓
[分類]欄の選択肢の中から、[ユーザー定義]を選択してクリック
　　↓
[種類]欄に

aaa

と入力する
　　↓
[セルの書式設定]ウィンドウのOKボタンをクリックする

　次に、Sheet2のA4セルに次の数式を入力して下さい。

=IF(ISNUMBER($A$3),IF(ROWS($2:4)-MATCH(MAX(A$3:A3),A$3:A3)>COUNTIF(Sheet1!$E:$F,MAX(A$3:A3)),MAX(A$3:A3)+1,""),"")

　次に、Sheet2のB3セルに次の数式を入力して下さい。

=IF($A3="","",$A3)

　次に、Sheet2のC3セルに次の数式を入力して下さい。

=IF(ROWS($2:3)-MATCH(MAX($A$3:$A3),$A$3:$A3)>COUNTIF(Sheet1!$E:$F,MAX($A$3:$A3)),"",IF(SUMPRODUCT((OFFSET(Sheet1!$E$1,,,MATCH(9^9,Sheet1!$E:$E))=MAX($A$3:$A3))*(COUNTIF(OFFSET(Sheet1!$E$1,,,ROW(OFFSET(Sheet1!$E$1,,,MATCH(9^9,Sheet1!$E:$E))),2),MAX($A$3:$A3))-(OFFSET(Sheet1!$E$1,,,MATCH(9^9,Sheet1!$E:$E))=OFFSET(Sheet1!$F$1,,,MATCH(9^9,Sheet1!$E:$E)))=ROWS($2:3)-MATCH(MAX($A$3:$A3),$A$3:$A3)))>0,"締切　","")&IF(SUMPRODUCT((OFFSET(Sheet1!$F$1,,,MATCH(9^9,Sheet1!$E:$E))=MAX($A$3:$A3))*(COUNTIF(OFFSET(Sheet1!$E$1,,,ROW(OFFSET(Sheet1!$E$1,,,MATCH(9^9,Sheet1!$E:$E))),2),MAX($A$3:$A3))=ROWS($2:3)-MATCH(MAX($A$3:$A3),$A$3:$A3)))>0,"発注締切",""))

※長過ぎる数式が他にもあり、回答欄に入力可能な文字数を超えてしまうため、このサイトの規約には少々反しますが、残りは次の回答に記させて頂きます。

回答No.1

tom04

こんばんは！
なかなか他の方からの回答がないようなので・・・

お示しの画像を拝見するとSheet1（上側のSheet）に同じデータが複数行あるように思われますが、
これは当方の見間違いでしょうか？
それから、同日の場合が複数あるようなので1行で表示するというより、複数列で同日の日数分だけデータを表示してはどうでしょうか？

一例ですが、↓の画像のようにSheet2の配置を変えてみました。
同日データがある場合は右方向へ3セルずつ表示するようにしています。

VBAになってしまいます。（Sheet1の重複する行は削除するコードも入れています）
Alt＋F11キーを押します。
VBE画面が表示されますので、画面左側にSheet1・Sheet2・・・ThisWorkbook　とあるはずです
このThisWorkbookをダブルクリックし↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行）です

Sub test() 'この行から
Dim i, j As Long
Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("sheet1") '←Sheet名の「sheet1」は適宜変更
Set ws2 = Worksheets("sheet2") '←こちらのSheet名も適宜変更
For i = ws1.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If ws1.Cells(i, 1) = ws1.Cells(i - 1, 1) And ws1.Cells(i, 3) = ws1.Cells(i - 1, 3) And _
ws1.Cells(i, 4) = ws1.Cells(i - 1, 4) And ws1.Cells(i, 5) = ws1.Cells(i - 1, 5) And _
ws1.Cells(i, 6) = ws1.Cells(i - 1, 6) Then
Rows(i).Delete (xlUp)
End If
Next i

For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
For j = 3 To ws2.Cells(Rows.Count, 1).End(xlUp).Row
If ws1.Cells(i, 5) = ws2.Cells(j, 1) Then
With ws2.Cells(j, Columns.Count).End(xlToLeft).Offset(, 1)
.Value = ws1.Cells(1, 5)
.Offset(, 1) = ws1.Cells(i, 1)
.Offset(, 2) = ws1.Cells(i, 3)
End With
End If
If ws1.Cells(i, 6) = ws2.Cells(j, 1) Then
With ws2.Cells(j, Columns.Count).End(xlToLeft).Offset(, 1)
.Value = ws1.Cells(1, 6)
.Offset(, 1) = ws1.Cells(i, 1)
.Offset(, 2) = ws1.Cells(i, 3)
End With
End If
Next j
Next i
Dim k As Long
k = ws2.UsedRange.Columns.Count
For k = 1 To k
ws2.Columns(k).AutoFit
Next k
End Sub 'この行まで

尚、一旦マクロを実行すると元に戻せませんので別Sheet・または別Bookにコピー＆ペーストしてマクロを試してみてください。

以上、参考になればよいのですが
外したいたらごめんなさいね。m(__)m

お礼

tom04様

早速のアドバイスありがとうございます！
やはりVBAを使った処理がスムーズなのですね。
重複している行は…説明が下手で申し訳ないです…。

「レインボーペイント」の「花色ニュータウン」案件では
ピンクの塗料が100缶、下地塗料が40缶必要、と言うように
（F列）発注締切の右以降に商品詳細や金額をインプットしようとしています。

また、上記のようにすると締切日が拾えないかと思い、B列に案件の連番を入れました。
tom04様に教えて頂いたVBAの、削除命令部分の代わりに
B列に入力されていることを条件分岐に入れさせて頂きます。

いつもエクセルは手探りで四苦八苦していましたが、今回は解決が早そうです。
ご回答大変参考になりました。ありがとうございます！！