[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『別シートのリストを参照にカレンダーの該当日へ予定名を反映したい』（きーた）
はじめまして。
なかなか方法がみつからず困っております。ご教示ください。
Seet1のA列の予定名を予定日（BEH列）を参照にカレンダーの該当日へ反映するようにしたいです。
実施日・備考については確認用であるためここでは使用しません。
カレンダー（Seet2）はA1・C1の入力で年月が切り替わるようになっております。
現在、ひと月（10月）のデータ反映まではたどり着きましたが（=INDEX(Sheet1!A3:A8,MATCH(F4,Sheet1!B3:B8,0),1)を使用）
複数月（E・H列)の反映ができずにいます。
また、ひと月の反映ができたといっても同日に2つの予定がある場合、
最初にヒットする予定しか入りません。
どうにか下記【Seet2】の10/1,5のように複数予定が反映するようにできないでしょうか。
理想としましては、カレンダー（Seet2）の年月を切り替えると（Seet1）の参照月も切り替わるようにしたいです。
ここでは3ヶ月分を例としておりますが1年間のものを作成したいと考えております。
【Seet1】
A B C D E F G H I J 1 10月 11月 12月 2 予定日 実施日 備考 予定日 実施日 備考 予定日 実施日 備考 3 会議A 10/11 10/5 11/5 11/5 12/3 12/3 4 会議B 10/3 10/6 11/7 11/7 12/4 12/4 5 会議C 11/5 11/8 12/28 6 D資料提出〆 10/3 10/3 11/20 11/21 12/2 12/3 7 E資料提出〆 10/1 10/8 12/10 12/8 8 F資料提出〆 10/15 10/14 11/18 11/19 12/14 12/13
【Seet2】
A B C D E F G 1 2015 年 10 月 2015/10/1 2 日 月 火 水 木 金 土 3 1 2 3 4 会議A 資料提出〆 5 4 5 6 7 8 9 10 6 会議B 資料提出〆 7 11 12 13 14 15 16 17 8 資料提出〆 9 18 19 20 21 22 23 24 10 11 25 26 27 12
長々と失礼いたしました。
言葉足らずな部分や分かりにくい箇所があるかと思いますが、
何卒よろしくお願いいたします。
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
回答ではありません m(__)m
＞カレンダー（Sheet2）の年月を切り替えると（Sheet1）の参照月も切り替わるようにしたいです。
１．Sheet2のカレンダーは、ひと月だけですね？ こういうものを私も考えることがあるんですが、 月末近くになると、翌月の始めの週も見たくなるんですよ。 なので、私は、翌月の１，２週分も出すようにしているんですが、 今回、そんな必要は全くないのでしょうか？
＞ここでは3ヶ月分を例としておりますが1年間のものを作成したいと考えております。
２．Sheet1の1行目に「月」のみの情報が出ていますが、実際は年月も含んだ日付シリアル値で、 セルの書式(表示形式）で月だけにしているんですね？
＞実施日・備考については確認用であるためここでは使用しません。
３．私の場合、過去の日付は無視して、当日から未来の分しか作らないのですが、 今日が15日として、当月の1日から反映しているものが必要なんでしょうか？
もし、必要だとしたら、過去は実施日の方で作ることを考えますけど、私なら。 もっとも、過去分に「D資料提出〆」はおかしいので、「〆」は「済」に替えて表示させます。
＞また、ひと月の反映ができたといっても同日に2つの予定がある場合、 ＞ 最初にヒットする予定しか入りません。
４．こう言う複数処理は、数式では非常に面倒です。 最多で幾つまでと決まっていればまだいいんですが・・・
作業列・マクロ(ユーザー定義関数も含む）を使うかした方が面倒が少なそうですが、 作業列の使用やマクロの使用は選択肢として有りですか？
（半平太） 2015/12/16(水) 09:41
ご返信ありがとうございます。
1．はい、ひと月分の予定反映だけで問題ありません。
2．すみません、月のみ文字入力しております。リンクさせるためにその方法も含め教えていただきたいです。。
もしくは、MATCH(F4,Sheet1!B3:B8,0)部分の検索値をE3:E8、H3:H8を増やす方法もありますでしょうか。
3．当月の1日から反映しているものを希望しております。
このカレンダーは月初に一覧で目安の日にちを確認するために欲しいものなので過去日となっても
表示されたまま or 〆のまま or 実施日と異なっても構わないと考えております。
今はお恥ずかしいことにカレンダーに手書きで記入しております。。。
4．やはり難しいですか。。最多では5つ程度といったところでしょうか。
作業列の使用選択肢はありです！
マクロは無知すぎて出来れば避けたいところです；；
お手数ですが、お知恵をお貸しください。。
（きーた） 2015/12/16(水) 15:27
＞4．やはり難しいですか。。最多では5つ程度といったところでしょうか。 ＞ 作業列の使用選択肢はありです！
１．最多で「7」までとします。(仕様) ２．Sheet1のA列は100行までとします(暫定)
３．G1セルに求めたい月の初日を入力してください。(随時。例：2015/10/1) ４．L1〜R1セルに１〜７まで、連番を振ってください。(必須。1,2,3,4,5,6,7)
５．以下の数式を入力してください。 (1) A1セル =YEAR(G1) (2) C1セル =MONTH(G1) (3) J1セル =MATCH(C1,Sheet1!1:1,0)
(4) A3セル =TEXT($G$1-WEEKDAY($G$1)+COLUMN(A3)+7*((ROW()-3)/2),"[<"&$G$1&"]"""";[>"&EOMONTH($G$1,0)&"]"""";d") (5) I3セル =$G$1+ROW(A1)-1 (6) J3セル =SUBSTITUTE(TRIM(CONCATENATE(S3,T3,U3,V3,W3,X3,Y3))," ",CHAR(10)) (7) K3セル =COUNTIF(INDEX(Sheet1!$A$1:$AK$100,0,$J$1),$I3) (8) L3セル =IF($K3<L$1,"",AGGREGATE(15,6,ROW($A$1:$A$100)/(INDEX(Sheet1!$A$1:$AK$100,0,$J$1)=$I3),L$1)) (9) S3セル =IF(L3="","",INDEX(Sheet1!$A$1:$A$100,L3)&" ")
(10) A4セル =IF(A3="","",VLOOKUP($G$1+A3-1,$I$3:$J$33,2,FALSE))
５．数式をコピーしてください。 (1)A3:A4を選択して、右方向コピー(G列まで） (2)更に、A3:G4を一括選択して、まとめて下方へコピー(13行目まで）
(3)L3セルを右方向にコピー(R列まで） (4)S3セルを右方向にコピー(Y列まで） (5)I3:Y3を選択して、下方へコピー(33行目まで）
<Sheet2 結果図> 行 __A__ _B_ _____C_____ _D_ _____E_____ __F__ ____G____ _H_ ____I____ _____J_____ ___K___ _L_ _M_ _N_ _O_ _P_ _Q_ _R_ ______S______ _T_ _U_ _V_ _W_ _X_ 1 2015 年 10 月 2015/10/1 予定列 2 該当数 1 2 3 4 5 6 7 2 日 月 火 水 木 金 土 3 1 2 3 2015/10/1 0 4 会議B 2015/10/2 0 5 4 5 6 7 8 9 10 2015/10/3 会議B 1 4 会議B 6 F資料提出〆 会議C 2015/10/4 0 7 11 12 13 14 15 16 17 2015/10/5 0 8 会議A 2015/10/6 0 9 18 19 20 21 22 23 24 2015/10/7 0 10 D資料提出〆 2015/10/8 F資料提出〆 1 8 F資料提出〆
６．数式の入力が面倒と感じる場合、以下のマクロを利用して、数式を入力してください。 (面倒と思わない場合は、これ以降の記述はスルーしてください）
後記マクロコードをSheet2の「シートモジュール(※1）」に貼り付けたあと、 F5キーを押し下げてください（それで、マクロ「onlyOnce」が実行されます）
すると自動的に数式が入力されます。 実行は１回だけですので、終わったらマクロコードは削除して下さい
※1 「シートモジュール」の表示方法が分からない場合 Sheet2の「シート見出し」を右クリックして、「コードの表示」を選ぶと 画面中央に白いエリアが表れますので、そこにマクロコードをコピぺしてください
Private Sub onlyOnce()
Rem 生データのセルをまとめて処理
Range("B1").Value = "年"
Range("D1").Value = "月"
Range("G1").Value = 42278
Range("I1").Value = "該当数"
Range("L1").Value = 1
Range("M1").Value = 2
Range("N1").Value = 3
Range("O1").Value = 4
Range("P1").Value = 5
Range("Q1").Value = 6
Range("R1").Value = 7
Rem 数式セルをまとめて処理
Range("A1").FormulaR1C1Local = "=YEAR(RC[6])"
Range("C1").FormulaR1C1Local = "=MONTH(RC[4])"
Range("J1").FormulaR1C1Local = "=MATCH(RC[-7],Sheet1!R,0)"
Range("A3:G3,A5:G5,A7:G7,A9:G9,A11:G11,A13:G13").FormulaR1C1Local = "=TEXT(R1C7-WEEKDAY(R1C7)+COLUMN(RC)+7*((ROW()-3)/2),""[<""&R1C7&""]"""""""";[>""&EOMONTH(R1C7,0)&""]"""""""";d"")"
Range("I3:I33").FormulaR1C1Local = "=R1C7+ROW(R[-2]C[-8])-1"
Range("J3:J33").FormulaR1C1Local = "=SUBSTITUTE(TRIM(CONCATENATE(RC[9],RC[10],RC[11],RC[12],RC[13],RC[14],RC[15])),"" "",CHAR(10))"
Range("K3:K33").FormulaR1C1Local = "=COUNTIF(INDEX(Sheet1!R1C1:R100C37,0,R1C10),RC9)"
Range("L3:R33").FormulaR1C1Local = "=IF(RC11<R1C,"""",AGGREGATE(15,6,ROW(R1C1:R100C1)/(INDEX(Sheet1!R1C1:R100C37,0,R1C10)=RC9),R1C))"
Range("S3:X33").FormulaR1C1Local = "=IF(RC[-7]="""","""",INDEX(Sheet1!R1C1:R100C1,RC[-7])&"" "")"
Range("A4:G4,A6:G6,A8:G8,A10:G10,A12:G12").FormulaR1C1Local = "=IF(R[-1]C="""","""",VLOOKUP(R1C7+R[-1]C-1,R3C9:R33C10,2,FALSE))"
Rem 標準外書式セルをまとめて処理
Range("G1,I1,I3:I33").NumberFormatLocal = "yyyy/m/d"
End Sub
（半平太） 2015/12/16(水) 20:50
＞2．すみません、月のみ文字入力しております。リンクさせるためにその方法も含め教えていただきたいです。。
一つ書き忘れました。m(__)m
Sheet1の方の10月(B1セル）とかは、単なる10とかに変えてください。
「月」も後ろに付けたい場合は、セルの書式（表示形式)でそう見えるように塩梅してください。
（半平太） 2015/12/16(水) 21:52
ご回答ありがとうございます。
上記試してみましたが、５の(3)より全てエラーとなってしまいます。
マクロも試してみましたが、同じく全てエラーとなってしまいます。
sheet1がどこか悪いのでしょうか。
（きーた） 2015/12/17(木) 16:54
新規ブックでやってみてください。
Sheet1のサンプルデータは、以下のマクロで自動作成してください。 やり方は、上と同じです。 (Sheet1の方のモジュールにコピペして、実行です）
Private Sub onlyOnce() ’Sheet1用です
Rem 生データのセルをまとめて処理
Range("B1").Value = 10
Range("E1").Value = 11
Range("H1").Value = 12
Range("B2,E2,H2").Value = "予定日"
Range("C2,F2,I2").Value = "実施日"
Range("D2,G2").Value = "備考"
Range("A3").Value = "会議A"
Range("B3").Value = 42288
Range("C3").Value = 42282
Range("E3:F3,E5").Value = 42313
Range("H3:I3,I6").Value = 42341
Range("A4").Value = "会議B"
Range("B4,C6").Value = 42280
Range("C4").Value = 42283
Range("E4:F4").Value = 42315
Range("H4:I4").Value = 42342
Range("A5").Value = "会議C"
Range("B5").Value = 42286
Range("H5").Value = 42316
Range("I5").Value = 42366
Range("A6").Value = "D資料提出〆"
Range("B6").Value = 42297
Range("E6").Value = 42328
Range("F6").Value = 42329
Range("H6").Value = 42340
Range("A7").Value = "E資料提出〆"
Range("B7").Value = 1025
Range("C7,B8").Value = 42285
Range("H7").Value = 42348
Range("I7").Value = 42346
Range("A8").Value = "F資料提出〆"
Range("C8").Value = 42291
Range("E8").Value = 42338
Range("F8").Value = 42327
Range("H8").Value = 42369
Range("I8").Value = 42351
Rem 標準外書式セルをまとめて処理
Range("B3:I8").NumberFormatLocal = "m/d;@"
End Sub
（半平太） 2015/12/17(木) 17:04
で、できました〜！感動です！
こんなに早く解決するとは。。
大変助かりました。
目指せ半平太さま！で私ももっと勉強します。
この度は誠にありがとうございました。
（きーた） 2015/12/17(木) 17:46
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.