エクセル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.1
- 回答日時:
こんばんは!
なかなか他の方からの回答がないようなので・・・
お示しの画像を拝見すると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列に入力されていることを条件分岐に入れさせて頂きます。
いつもエクセルは手探りで四苦八苦していましたが、今回は解決が早そうです。
ご回答大変参考になりました。ありがとうございます!!
No.2
- 回答日時:
関数を使った方法です。
今仮に、(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.3ベストアンサー
- 回答日時:
回答番号: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関数にこんな使用方法があると知らず知識不足でした。。
ご教示頂きありがとうございます!
教えて頂いた数式をもとにエクセルを修正して利用したいと思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 【条件付き書式】countifsで複数条件を満たしたセルを赤くする方法 2 2023/02/09 23:53
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Visual Basic(VBA) 【VBA】指定した検索条件に一致したら別シートに転記したい 2 2022/03/23 16:14
- Visual Basic(VBA) VBAで、1つのエクセルで、2つのシートからもう1つのシートに条件のある転記コードを教えてください。 1 2023/03/16 18:07
- Excel(エクセル) エクセルの条件付き書式で*を使いたい 4 2022/05/13 16:49
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Visual Basic(VBA) エクセルについて教えてください。 3 2023/06/28 09:11
- Excel(エクセル) エクセルで条件付き書式を使わずにセルの文字の色を変える方法を教えて下さい 8 2023/07/28 01:15
- Excel(エクセル) Excel2007での条件付き書式について 6 2023/05/02 10:56
- Excel(エクセル) 指定した条件でTRANSPOSE関数を使う 5 2023/08/18 19:45
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
Excel 日付を比較したら、同じ...
-
Excelで空白以外の値がある列の...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
Excelで時間計算(負)
-
ウィンドウィズ メモ帳で日付だ...
-
Excel テーブル内の空白行の削除
-
Microsoft Formsの「個人情報や...
-
エクセルで英文字に入れた下線...
-
【Excel VBA】PDFを作成して,...
-
OFFICEで自動保存されたファイ...
-
【スプレッドシート】指定の日...
-
Excelに貼ったリンクについて E...
-
エクセルでXLOOKUP関数...
-
VBAファイルの保存先について
-
エクセルでレーダーチャートの...
-
Outlook 電源OFFの受診の仕方
-
Microsoft Edgeの「ニュースと...
-
何文字超えたファイルだけを抽...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報