[[20190612085935]] 『カレンダーに反映させたい』(りー) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『カレンダーに反映させたい』(りー)

別シート、またはブックで作成した進捗表の一部の内容をカレンダーに連動させるために数式を入れてみましたが、うまく反映されません。
反映させるようにするためには、
どのような数式にしたらいいか教えて下さい。

■進捗表

  B C D E F G H I J K L M N O  P  Q……AP…AV…BC
5                     着 上 竣
                      工 棟 工
  ――――――――――――――――――――――――――――
6 番 お客様名          契約時
7 号 建築地           予定日  6/1 6/30 8/30
8   営業  設計  CD  監督 実施日
9   大工      基礎    備考 
  ――――――――――――――――――――――――――――
10 2   A以下同じ
11 番
12
13

J5〜BI5に工程の内容を記載
C6(CDEFGHI結合)お客様名
C7(CD結合)建築地 E7(EFGHI結合)建築地記入欄
C8営業 D8(DEF結合)営業名記入欄 M8監督 N8(NO結合)監督名記入
     AP5着工 AV5上棟 BC5竣工 
P6契約時
P7予定日 
P8実施日

・カレンダーに反映させたい日付は、予定日の行で【着工・上棟・竣工】の3つです。
そこに日付を入力すると下のカレンダーに、その日付の場所に【お客様名・営業・建築地・監督】が入る様にしたい。

※約150件程の予定です。

■カレンダー
・7×4タイプで日曜始まり
・1日に4件まで書き込めるようにしたい。

   A  B C D ……
――――――――――――――――――――――――
5|  日      月   火   水   木   金   土
6|   1       2   3    4   5    6   7
7| 名前 |営業|…

 | ――――――――――――――――――――――――――――――
8| 建築地|監督|…
 | ―――――――――――――――――――――――――――――
9| 名前 |営業|…
 |  ―――――――――――――――――――――――――――――
10   建築地|監督|…
  ――――――――
※1日4件入るようにしたい。
※お客様名・営業名・建築地・監督名で1セット。

《連動させたい部分》
A7に進捗表のお客様名 B7に進捗表の営業名
A8に進捗表E7の建築地 B8に進捗表のN8監督名

と進捗表に日付が記入されるとカレンダーにその日付の所に
それぞれの名前などが入力される連動にしたいです。

・スケジュール(カレンダー)は一月事にブックで管理し、着工予定・上棟予定・完工予定、それぞれに色もつけたいです。
・反映させるのは予定日になる日にちです。
・契約時と実行日は反映はさせません。

※カレンダーを一月事に分ける為、数式も複雑になると予想しております。
出来れば1年分のカレンダーと連動しているように設定したいです。

念の為、試してみた式です。
=IFERROR(INDEX(進捗管理表!$C$6:$C$6,SMALL(INDEX((進捗管理表!$AP$7,進捗管理表!$AV$7,進捗管理表!$BE$7<>LOOKUP(10^5,A$6:A6))*10^5+ROW(進捗管理表!$AP$7:$BE$7),),MOD(ROW()-ROW(A$7)+1,8)),)&"","")

分かりにくくて申し訳ありませんが
よろしくお願いいたします。

< 使用 Excel:Excel2013、使用 OS:Windows10 >


回答じゃないですが、なんで↓で続けないんですか?(違う話なのでしょうか?)
[[20190611113356]] 『別シート(ブック)の進捗表をカレンダーに反映させ』(りー)

(もこな2) 2019/06/12(水) 12:44


もこな2さん

同じですが、わかりにくいと掲載してから思い削除方法が分からなかったので
新たに載せ直しました。
(りー) 2019/06/12(水) 13:00


上記内容の連動は難しいのでしょうか。
出来る、出来ないかだけでも教えて頂けると助かります。
(りー) 2019/06/15(土) 09:13

 >出来る、出来ないかだけでも教えて頂けると助かります。

 数式では出来ないと思うけど、断言はしない。

 マクロなら可能。(簡単でもないけど、数式よりは楽)

(半平太) 2019/06/15(土) 10:42


半平太さん

ありがとうございます。
マクロの方法教えて頂けますか?
(りー) 2019/06/15(土) 10:58


 マクロの知識はどの程度ありますか?

(半平太) 2019/06/15(土) 11:02


半平太さん

ほとんど使ったことがありません。
ネットで調べてマクロがどういうものか知ってる程度です。
(りー) 2019/06/15(土) 11:11


 それだと、私にはちょっと教えられません。

 すると、こっちで全部作って、そちらはただ使うだけ、という図式になりますが、
 現時点では、私は元気がないので無理です。

 もう少し待ってみて下さい。

 「数式でもできるよ」とか「マクロを作ってあげますよ」とか
 「一から教えられます」とか言う人が現われるかも知れません。

(半平太) 2019/06/15(土) 11:17


 食指が動く人はいないですか・・

 あれこれ考えてみましたが、カレンダーの仕様が今一つ不安ですね。

 そこまでマクロで作り込んでも、
  「実際に使ってみたら、○○のところは△△にしたくなった」
 なんて話が何度か出そう。

 そう言う変更は、そちらの手作業で好きなだけやって貰えると、お互いストレスが減ります。

 なので、取り敢えずマクロは、進捗表の日付別集計だけを行う、と言うのはどうでしょうか?

 その集計シートがあれば、カレンダーがどんな形状でも、簡単な関数の組み合わせで対応できます。

 それで何ヶ月間か改善を繰り返し、使い勝手のいいカレンダー形状が固まったところで、
 その部分のマクロ化について再質問してもらうのがよさそう。

 まぁ、その時はもう満足して、再質問の必要もなくなっているかも知れませんけど。

(半平太) 2019/06/16(日) 09:59


半平太さん

いろいろとありがとうございます。

今マクロを色々調べ勉強中です。

取急ぎカレンダーに連動したいのが、
お客様名が着工予定日・上棟予定日・竣工日の3つ
カレンダーに連動できればと今模索しておりました。

担当営業などはその後、対応出来ればいいかと。

質問です。

>カレンダーの仕様が不安とのことですが、
4項目(お客様・営業・大工・場所)の構成になっているからと
いう意味でしょうか?

これをお客様名だけにしたらそれぞれ可能になりますか?

>進捗表の日付別集計だけ行う
理解する為に教えて下さい。

進捗表はそのままに、日付別集計を行ってからカレンダーに反映させる
という認識で合っているでしょうか。

もしそれで反映が可能なら教えて頂きたいです。

最終的には4項目全て反映させることが目標です。

よろしくお願いいたします。
(りー) 2019/06/17(月) 12:28


 >質問です。 
 >>カレンダーの仕様が不安とのことですが、 
 >4項目(お客様・営業・大工・場所)の構成になっているからと 
 >いう意味でしょうか? 

 表現が難しいですが、
 要件がコナレていない、または最終的に固まっていない、恐れを感じると言うことです。

 4項目(お客様・営業・大工・場所)が最終的な項目数かと言うと、
 後で、別の項目を増やしたくなったりすることは珍しくないです。(何せ、まだ使ったことがないですから)

 実際、着工・上棟・竣工は区別できるようにして集計して置かなければ今回の処理はできないです。
 シート上に表示するかどうかは別として、その為のデータはどこかに持っている必要があります。

 カレンダーは、
 キッチリ1日から始めるのがいいと思っているのでしょうが、
 当月の前後1週間も余分に含めたくなったりする可能性があります。

 実際に使ったことがないカレンダーですから、今では想像もできない改善点が
 使ってみていろいろ出てくる可能性が高いと感じます。

 なので、カレンダーまでマクロで作り込むと
 手直ししたい要望が生じると面倒なことになります。

 なので、当面、マクロは進捗表の日付別集計を行うに留め、
 カレンダーはそちらでお好きな様に作って頂くのがお互いの幸せになる。

 当該「集計」結果が有れば、カレンダーへの反映は簡単な関数の組み合わせで対応可能となります。

 >>進捗表の日付別集計だけ行う 
 >理解する為に教えて下さい。 
 >進捗表はそのままに、日付別集計を行ってからカレンダーに反映させる 
 >という認識で合っているでしょうか。 

 合っています。

 日付別集計は、下図の様な体裁にすればいいと思っています。

 集計案1、集計案2、いずれでもMatchとIndex関数の組み合わせで、カレンダーに所要データを引っ張って来れます。

 ただし、そのままでは、着工・上棟・竣工の区別をつけるデータが無いので、
 条件付き書式で色を付けたくても、出来ないです。

 なので例えば、大谷様→大谷様(着工) とか表示する様にして、その区別をつけさせるか、
 列を1つ増やして、3列目に「着工・上棟・竣工」のデータを入れる、と言う様な対策が必要になります。

 1.縦型、横型、それぞれ一長一短がありますが、どっちにするか、りーさんが決めてください。
 2.また、「着工・上棟・竣工」の区別をどうつけて対応するのかも、そちらで決めてください。

 <進捗表 サンプル>
  行 _A_ _B_ ___C___ __D__ ______E______ _F_ __G__ _ __M__ ____N____ _O_ ___P___   __AP__ _ ___AV____ ___BC___
   6       1 大谷様                                                                 着工     上棟      着工    
   7     番  建築地        赤坂Pホテル                                   予定日                                
   8         営業    営業1                   設計    監督  長嶋監督      実施日     6月1日   6月30日   8月30日 
   9         大工    基礎                                                                                      
  10       2 流星様                                                                                            
  11     番  建築地        六本木ヒルズ                                  予定日                                
  12         営業    営業2                   設計    監督  王貞治        実施日     6月1日   7月1日    9月10日 
  13         大工    基礎                                                       
 ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 <集計案1=縦型>        ┃ <集計案2=横型>
  行  ______A______  ____B____┃  行 ______A______ ____B____ _____C_____ ____D____ _____E_____ ____F____
   1  2019/6/1                ┃   1 2019/6/1                2019/6/30             2019/8/30            
   2  大谷様         営業1    ┃   2 大谷様        営業1     大谷様      営業1     大谷様      営業1    
   3  赤坂Pホテル    長嶋監督 ┃   3 赤坂Pホテル   長嶋監督  赤坂Pホテル 長嶋監督  赤坂Pホテル 長嶋監督 
   4  流星様         営業2    ┃   4 流星様        営業2                                                
   5  六本木ヒルズ   王貞治   ┃   5 六本木ヒルズ  王貞治                                               
   6                          ┃
   7                          ┃
   8                          ┃
   9                          ┃
  10  2019/6/30               ┃
  11  大谷様         営業1    ┃
  12  赤坂Pホテル    長嶋監督 ┃
    :        :   ┃

(半平太) 2019/06/17(月) 16:11


半平太さん

なるほど…
ただ単にカレンダーへ連動できればと考えていたので
最初、私が言った内容だとカレンダーまでマクロになるとは思っていませんでした。

お話を聞く限り、半平太さんのおっしゃる内容でやらせて頂くのが
今の最善かなと思っています。

集計案としては集計案1がいいと思っています。
理由は件数が100件〜150件を想定しているので、
縦列で見た方がわかりやすいと判断しました。

またカレンダーですが、1日はじまりを想定してますが
おっしゃる通り、使っていて前後1週間入れる可能性ものちのち出てくるかな?
と思うようになりました。

1年間のカレンダーに反映できるようになれれば
のちのち少しずつ変更修正し、完成形に近づけて行ければと思ってます。

質問です。
集計案で

 行  ______A______  ____B____┃  
   1  2019/6/1                ┃              
   2  大谷様         営業1    ┃      
   3  赤坂Pホテル    長嶋監督 ┃    
   4  流星様         営業2    ┃ 
   5  六本木ヒルズ   王貞治   ┃  
   6                          ┃
   7                          ┃
   8                          ┃
   9                          ┃
  10  2019/6/30               ┃
  11  大谷様         営業1    ┃
  12  赤坂Pホテル    長嶋監督 ┃
    :        : 

となっておりましたが

〈集計案の確認〉
行 ______A______ ____B_____ C_____D____

   1  2019/6/1                              
   2  大谷様         営業1   赤坂Pホテル    長嶋監督       
   3 2019/6/10
  4  流星様         営業2   六本木ヒルズ   王貞治 
   5      
   6                          

というように横に一列で並べずでいいのでしょうか?
日付別集計などで見るのは、一つの項目(案件)で一列での集計が多いと思っていたので…
カレンダーに合わせてという事なのでしょうか。
(りー) 2019/06/17(月) 17:08


 >というように横に一列で並べずでいいのでしょうか? 
 >日付別集計などで見るのは、一つの項目(案件)で一列での集計が多いと思っていたので… 
 >カレンダーに合わせてという事なのでしょうか。

 当初、カレンダーに流し込むつもりだったので、データの並びはそんな形になっています。

 一行にまとめると、それを「8行x2列」に振り分けないとならないので、結構大変です。

 ・・とは言え、カレンダーの形に合わせたとしても、数式で引っ張るのはそんなに簡単じゃなかったです。m(__)m

 ちょっと工夫して、A列に補助番号を入力したらどうかと思っています。(下図個参照)

 <カレンダー>
 (1) B7セル =IFERROR(OFFSET(INDEX(日付別集計!$A:$A,MATCH(OFFSET(B6,-$A7,0),日付別集計!$A:$A,0)+1),$A7,0)&"","")
 (2) C7セル =IFERROR(OFFSET(INDEX(日付別集計!$B:$B,MATCH(OFFSET(B6,-$A7,0),日付別集計!$A:$A,0)+1),$A7,0)&"","")

  2セル一括選択して下方へコピー
  その後、2列まとめて選択して右方へコピー

 <カレンダー サンプル結果図>
  行 ____A____ ______B______ ____C____ _D_ _E_ ___F___ __G__ __H__ _I_ __J__ _K_ __L__ _M_ _____N_____ ____O____

   6 補助番号  2019/6/1                6/2     6/3           6/4       6/5       6/6       2019/6/7             
   7        0  大谷様        営業1                                                         加藤様      営業Z    
   8        1  赤坂Pホテル   長嶋茂雄                                                      帝国ホテル  高橋由伸 
   9        2  流星様        営業2                                                         顧客1       営業A    
  10        3  六本木ヒルズ  王貞治                                                        ホテル1     監督1   
  11        4                                                                              顧客2       営業B    
  12        5                                                                              ホテル2     監督2   
  13        6                                                                                                   
  14        7                                                                                                   
  15           2019/6/8                6/9     6/10          6/11      6/12      6/13      2019/6/14            
  16        0                                  顧客5   営業D                                                    
  17        1                                  ホテル5 監督5                                                    
  18        2                                  顧客6   営業E                                                    
  19        3                                  ホテル6 監督6                                                    
  20        4                                  顧客7   営業F                                                    
  21        5                                  ホテル7 監督7                                                    
  22        6                                  顧客8   営業G                                                    
  23        7                                  ホテル8 監督8                                                    

 <日付別集計 サンプル>
  行  ______A______  ____B____
   1  2019/6/1                
   2  大谷様         営業1    
   3  赤坂Pホテル    長嶋茂雄 
   4  流星様         営業2    
   5  六本木ヒルズ   王貞治   
   6                          
   7                          
   8                          
   9                          
  10  2019/6/7                
  11  加藤様         営業Z    
  12  帝国ホテル     高橋由伸 
  13  顧客1          営業A    
  14  ホテル1        監督1   
  15  顧客2          営業B    
  16  ホテル2        監督2   
  17                          
  18                          
  19  2019/6/10               
  20  顧客5          営業D    
  21  ホテル5        監督5    
  22  顧客6          営業E    
  23  ホテル6        監督6    
  24  顧客7          営業F    
  25  ホテル7        監督7    
  26  顧客8          営業G    
  27  ホテル8        監督8    

(半平太) 2019/06/17(月) 21:31


 > 一行にまとめると、それを「8行x2列」に振り分けないとならないので、結構大変です

 そちらで数式は対応すると言うことであれば、1行にまとめます。

 その場合、日付も含めて、こんな1行にする予定です。

 <日付別集計>
  行 ____A____ ___B___ __C__ _____D_____ ____E____ ___F___ __G__ ______H______ ___I___
   1 日付         1      2        3         4        5     6         7        8・・
   2 2019/6/1  大谷様  営業1 赤坂Pホテル 長嶋茂雄  流星様  営業2 六本木ヒルズ  王貞治 ・・
   3 2019/6/9  加藤様  営業Z 帝国ホテル  高橋由伸                                     

(半平太) 2019/06/17(月) 22:14


半平太さん

ありがとうございます!!

話を聞いて、日付別集計はカレンダーと同じような形で作成していきたいと思います。

>>↓↓

 <カレンダー>
 (1) B7セル =IFERROR(OFFSET(INDEX(日付別集計!$A:$A,MATCH(OFFSET(B6,-$A7,0),日付別集計!$A:$A,0)+1),$A7,0)&"","")
 (2) C7セル =IFERROR(OFFSET(INDEX(日付別集計!$B:$B,MATCH(OFFSET(B6,-$A7,0),日付別集計!$A:$A,0)+1),$A7,0)&"","")

こちらの数式は、カレンダーに記入すると思うのですが、
その前に進捗表から日付別集計にする方法でお伺いしたいのですが・・・

VLOOKUPでやろうとし、半平太さんが考えて下さった
日付別集計がうまく出来ませんでした。

何度もお手数おかけし申し訳ないのですが、
その方法もご伝授頂ければ幸いです。
(りー) 2019/06/18(火) 16:07


 >その前に進捗表から日付別集計にする方法でお伺いしたいのですが・・・ 

 縦型で確定したので、これからそのマクロを作ります。

 (しばらくお待ちください)

(半平太) 2019/06/18(火) 16:17


半平太さん

すみません。
マクロで作成になるのですね・・・

知識が至らず申し訳ありません。
(りー) 2019/06/18(火) 16:29


 1.新規シートを挿入して、シート名を「日付別集計」としてください。

 2.標準モジュールに後記マクロ「CollByDay」をコピペする。

 3.進捗表のシートモジュールに、後記マクロ「Worksheet_Activate」を貼り付ける

 ※シートモジュールは、進捗表のシート見出しを右クリックすると
  サブメニューの中に「コードの表示(V)」があるので、それを選択すると
  VBE画面に遷移します。その画面中央の白いエリアのところです。

 4.進捗表に何かデータを入れて、スケジュールがどうなったか見たくなった時、
  スケジュールシートのシート見出しをクリックして、当該シートに移動した時
  CollbyDayが自動的に実行されて、日付別集計のデータが更新されます。

  ※なので、数式のようなリアルタイムではないです。

 ’<標準モジュールへコピペ>-------------------------
 Public Sub CollByDay()
     Const MaxForTheDay As Long = 4
     Const 氏名 As Long = 3 'C列
     Const 営業 As Long = 4 'D列
     Const 建築 As Long = 5 'E列
     Const 監督 As Long = 14 'N列列番号
     Dim dicT As Object
     Dim customers As Range
     Dim RW As Long, Col As Long, NewPos As Long, RWprnt As Long
     Dim aDT As Range
     Dim Kbn
     Dim Outs    '(1 To 8, 1 To 3)の配列
     Dim SchKey
     Dim Prnt()

     '(1,3)は件数管理。(2,3),(4,3),(6,3),(8,3) は着工・上棟・竣工区分

     Set dicT = CreateObject("Scripting.Dictionary") ' 連想配列の定義

     With Sheets("進捗表")
         Set customers = .Range("A6:A" & .Cells(.Rows.Count, "C").End(xlUp).Row)
     End With

     For RW = 1 To customers.Rows.Count Step 4 '6行目スタート、4行毎に顧客名

         For Each aDT In customers(RW + 1).Range("AP1,AV1,BC1")
             If IsDate(aDT.Value) Then
                 Kbn = aDT.EntireColumn.Cells(5, 1).Value

                 If dicT.Exists(aDT.Value) Then
                     Outs = dicT(aDT.Value)  '既存データ読み込み

                     Outs(1, 3) = Outs(1, 3) + 1  '日付別件数加算
                     If Outs(1, 3) > MaxForTheDay Then
                         MsgBox aDT & "の予定が" & MaxForTheDay & "件を超えました。処理終了"
                         Exit Sub
                     End If

                     NewPos = Outs(1, 3) * 2 - 1  '書き込みポジションを算出

                     Outs(NewPos, 1) = customers(RW, 氏名).Value
                     Outs(NewPos, 2) = customers(RW + 2, 営業).Value
                     Outs(NewPos + 1, 1) = customers(RW + 1, 建築).Value
                     Outs(NewPos + 1, 2) = customers(RW + 2, 監督).Value
                     Outs(NewPos + 1, 3) = Kbn '着工・上棟・竣工区分をセット

                     dicT(aDT.Value) = Outs

                 Else
                     ReDim Outs(1 To 8, 1 To 3)
                     Outs(1, 3) = 1 '件数セット

                     Outs(1, 1) = customers(RW, 氏名).Value
                     Outs(1, 2) = customers(RW + 2, 営業).Value
                     Outs(2, 1) = customers(RW + 1, 建築).Value
                     Outs(2, 2) = customers(RW + 2, 監督).Value
                     Outs(2, 3) = Kbn '着工・上棟・竣工区分をセット

                     dicT(aDT.Value) = Outs
                 End If
             End If
         Next
     Next RW

     If dicT.Count = 0 Then
         MsgBox "有効データなし"
         Exit Sub
     End If

     ReDim Prnt(1 To dicT.Count * (MaxForTheDay * 2 + 1), 1 To 3)

     RWprnt = 1

     For Each SchKey In dicT
         Prnt(RWprnt, 1) = SchKey
         RWprnt = RWprnt + 1

         Outs = dicT(SchKey)

         For RW = 1 To MaxForTheDay * 2 '1 - 8

             For Col = 1 To 3
                 Prnt(RWprnt, Col) = Outs(RW, Col)
             Next Col
             RWprnt = RWprnt + 1
         Next RW

     Next

     Sheets("日付別集計").Columns("A:C").ClearContents
     Sheets("日付別集計").Range("A1").Resize(UBound(Prnt), 3).Value = Prnt
 End Sub

 ’<進捗表のシートモジュールへコピペ>----------------------
 Private Sub Worksheet_Activate()
     CollByDay
 End Sub

(半平太) 2019/06/18(火) 20:15


  マクロが実行されると、日付別集計表は下図のようになります。

  ※上述の通り、この表を見に行ってもデータは更新されません。
    もし同じように更新させたい場合は、例のマクロを
    日付別集計のシートモジュールにもコピペすれば可能となります。

   このプログラムです。
  ↓    
   Private Sub Worksheet_Activate()
       CollByDay
   End Sub

  <日付別集計 結果図>
   行  ______A______  ____B____  __C__
    1  2019/6/1                       
    2  大谷様         営業1        2  ←当日の予定件数
    3  赤坂Pホテル    長嶋監督   着工 ←着工・上棟・竣工区分
    4  流星様         営業2           
    5  六本木ヒルズ   王貞治     着工 ←着工・上棟・竣工区分
    6                                 
    7                                 
    8                                 
    9                                 
   10  2019/6/30                      
   11  大谷様         営業1        1   ←当日の予定件数
   12  赤坂Pホテル    長嶋監督   上棟  ←着工・上棟・竣工区分
   13                                 

(半平太) 2019/06/18(火) 20:30


半平太さん

本当にありがとうございます!!
いただいたマクロで一度作成してみます。

また、不明な点が出ましたら教えて頂ければ幸いです。

(りー) 2019/06/19(水) 09:45


半平太さん

いただいたマクロで作成し、日付別集計出来ました!
ですが、竣工が反映されず、何が原因なのかと思いご相談させてください。

↓↓

 Outs(1, 1) = customers(RW, 氏名).Value
                     Outs(1, 2) = customers(RW + 2, 営業).Value
                     Outs(2, 1) = customers(RW + 1, 建築).Value
                     Outs(2, 2) = customers(RW + 2, 監督).Value
                     Outs(2, 3) = Kbn '着工・上棟・竣工区分をセット

とあるところかな?と思っているのですが・・・
(りー) 2019/06/19(水) 11:03


 >竣工が反映されず、何が原因なのか

 私も分かりません。こちらでは問題ないです。

 「進捗表のBC7セルが6/10になっているのだが、集計表のどこにも出てこない。進捗表のBC5セルにはチャンと竣工の文字が入っている」
 「進捗表のBC7セルが6/15になっており、集計表にも6/15は出ているのだが、竣工の文字がない。」
 とか言う具体的な説明をお願いします。

(半平太) 2019/06/19(水) 11:24


半平太さん

すみません!!
私の進捗表とセル番号がズレていた様で、
BC→BEに変更しましたら反映されました!

続いて、カレンダーへの反映にとりかかります。

取急ぎご連絡までとさせていただきます。
(りー) 2019/06/19(水) 11:36


半平太さん

カレンダーへ下記の関数を入れました。

 (1) B7セル =IFERROR(OFFSET(INDEX(日付別集計!$A:$A,MATCH(OFFSET(B6,-$A7,0),日付別集計!$A:$A,0)+1),$A7,0)&"","")
 (2) C7セル =IFERROR(OFFSET(INDEX(日付別集計!$B:$B,MATCH(OFFSET(B6,-$A7,0),日付別集計!$A:$A,0)+1),$A7,0)&"","")

≪結果≫
7月の予定でやりました。
下記の様に、〈 0 〉と表示されてしまいます。
進捗表には7月の日付を記入し、日付別集計にも反映はされております。

 A_____B______C______D________E_____F_______G___H__
1
2 2019 年 7 月 
3
4
5補助  日      月       火      水   
6 0          1日       2日      3日  
  1   0  0   0  0     0  0    0  0

  2   0  0   0  0     0  0    0  0
  3   ・ ・
  4   ・ ・
   5   ・ ・
   6
   7

セルの書式設定で表示形式を色々と試してみましたが変わりありませんでした。

ちなみに、カレンダーは月を変えると日付が自動で変わる様に設定してあります。
B6から入力してます。

=IF($D$2<>MONTH(DATE($B$2,$D$2,1)-WEEKDAY(DATE($B$2,$D$2,1))+COLUMN(A$1)+7*INT((ROW()-ROW($AC$6))/8)),"",DATE($B$2,$D$2,1)-WEEKDAY(DATE($B$2,$D$2,1))+COLUMN(A$1)+7*INT((ROW()-ROW($AC$6))/8))

この関数が関係しているのでしょうか・・・。

  

(りー) 2019/06/19(水) 12:46


 全部「0」?         文字で返ってくるので、0なんて出るハズないんですけどねぇ・・
                 ↓
 >B7セル =IFERROR(OFFSET(・・・)&"","")

 カレンダーの「B7セル、C7セル、D7セル」の値は何ですか?

 書式を標準にして、表示される実体値を教えてください。

 ※7月1日のところなら、43647 となっているハズですが。

(半平太) 2019/06/19(水) 13:10


 済みません

 >  カレンダーの「B7セル、C7セル、D7セル」の値は何ですか?
                    ↓
 (正)カレンダーの「B6セル、C6セル、D6セル」の値は何ですか?

(半平太) 2019/06/19(水) 13:19


半平太さん

>カレンダーの「B6セル、C6セル、D6セル」の値は何ですか?

BC・DE・FG・HI・JK……
とセルを結合し↓

=IF($D$2<>MONTH(DATE($B$2,$D$2,1)-WEEKDAY(DATE($B$2,$D$2,1))+COLUMN(A$1)+7*INT((ROW()-ROW($AC$6))/8)),"",DATE($B$2,$D$2,1)-WEEKDAY(DATE($B$2,$D$2,1))+COLUMN(A$1)+7*INT((ROW()-ROW($AC$6))/8))

が記入されております。

1日、2日と表示されるようになっていて、
表示形式→ユーザー定義(d“日”)にしてます。
(りー) 2019/06/19(水) 13:55


 >BC・DE・FG・HI・JK…… 
 >とセルを結合し↓ 
 >表示形式→ユーザー定義(d“日”)にしてます。

 B6セル、D6セルの書式をユーザー定義から「標準」に変えたら、何が見えるか教えてください。

 2日のところなら、43648 と出てくるハズですが。

(半平太) 2019/06/19(水) 14:24


半平太さん

標準にしたところ

1日は43647
2日は43648と出てきました。
(りー) 2019/06/19(水) 14:36


 >1日は43647 
 >2日は43648と出てきました。

 正しいです。

 すると、これが正しく入力されてないとしか思えないです。
      ↓
  (1) B7セル =IFERROR(OFFSET(INDEX(日付別集計!$A:$A,MATCH(OFFSET(B6,-$A7,0),日付別集計!$A:$A,0)+1),$A7,0)&"","")
  (2) C7セル =IFERROR(OFFSET(INDEX(日付別集計!$B:$B,MATCH(OFFSET(B6,-$A7,0),日付別集計!$A:$A,0)+1),$A7,0)&"","")

 数式を掲示板からのコピペで入力しましたか?

(半平太) 2019/06/19(水) 14:59


半平太さん

コピペで入力しました。
もう一度、やり直してみます。
(りー) 2019/06/19(水) 15:07


 >BC・DE・FG・HI・JK…… 
 >とセルを結合し↓ 
 >=IF($D$2<>MONTH(DATE($B$2,$D$2,1)-・・・・・・・・)-ROW($AC$6))/8)) 

 ところで、その式をこちらで入れても7/1なんて出て来ないんですけど、
 なにか説明してないことありませんか?

 こちらではこうなります。
       ↓
 <カレンダー >
  行  ____A____  __B__  _C_  _____D_____  ____E____  ______F______  ___G___
   1                                                                       
   2             2019             7                                   
   3                                                                       
   4                                                                       
   5                                                                       
   6  補助番号               2日                     4日                   
   7         0               大谷様       営業1      流星様         営業2  
   8         1               赤坂Pホテル  長嶋監督   六本木ヒルズ   王貞治 
   9         2                                                             
  10         3                                                             

(半平太) 2019/06/19(水) 15:15


半平太さん

遅くなりました!!

先程のを再度やり直しましたら出来ました!

感謝してもしきれません!
ありがとうございます!!

(りー) 2019/06/19(水) 16:45


半平太さん

度々の質問、失礼いたします。

先日教えて頂いたマクロ及び関数で、無事仕上げる事が出来ました。
こちらに関しては、本当にありがとうございました。

こちらの不手際で申し訳ないのですが、

カレンダーを着工・上棟・竣工それぞれ分けて欲しいと上司から言われ。
いただいたデーターを紐解きながらやってみたのですが、

≪インデックスが有効範囲にありません。エラーコード9≫
が出てきてしまいました。

■やった内容■

1.日付別集計シートを3つ作成
  日付別集計・日付別集計(上棟)・日付別集計(竣工)

2.マクロ修正
標準モジュールを2つ追加、合計3つ
Module1→着工
Module2→上棟
Module3→竣工

※抜粋

  For RW = 1 To customers.Rows.Count Step 4 '6行目スタート、4行毎に顧客名
         For Each aDT In customers(RW + 1).Range("AV1") 
             If IsDate(aDT.Value) Then

Range("AV1") ←着工・上棟・竣工のセル一つずつにしました。

最後のところ日付別集計のシート名をそれぞれ変更。

   Sheets("日付別集計(着工)").Columns("A:C").ClearContents
     Sheets("日付別集計(着工)").Range("A1").Resize(UBound(Prnt), 3).Value = Prnt
 End Sub

その後、実行をするとエラーが最後の

 Sheets("日付別集計(着工)").Columns("A:C").ClearContents
で引っ掛かってしまいました。

申し訳ございませんが、ご教授のほどよろしくお願いいたします。
(りー) 2019/06/21(金) 14:06


よこからですけど、
 日付別集計・日付別集計(上棟)・日付別集計(竣工)  というシートを作成して
 Sheets("日付別集計(着工)").Columns("A:C").ClearContents 【←でエラー発生】

というとなら理由は分かります。
だって、そんなシート無いですから・・・・

 日付別集計(竣工)
   ↑↓どこか違いませんか?
 日付別集計(着工)

(もこな2) 2019/06/21(金) 14:31


おっと。。。こうでしたね。
 日付別集計
   ↑↓どこか違いませんか?
 日付別集計(着工)

(もこな2) 2019/06/21(金) 14:35


そのくらいは自分で解決しましょ。
() 2019/06/21(金) 16:04

皆さま

先程、解決した旨をお伝えしたと思ってましたが、
掲示されていなかったようです。

分けるのは無事解決しました。
(りー) 2019/06/21(金) 16:11


下記、エラーがどうしても不明なので
教えて頂きたいです。

分ける前は、問題ありませんでしたが
その後、下記のコードに対してスパイラルエラーが出てしまいました。

Private Sub Worksheet_Activate()

     CollByDay

CollByDay  ← 名前が適切ではありません。

と出てしまいます。

(りー) 2019/06/21(金) 16:14


 早くも改善点が出ましたか。

 多分、今回の上司の指示も、また変わりそうな予感がします。

 「別々もいいけど、やっぱり全貌も必要だな。それも復活してくれ」
 「但し、全貌は、着工・上棟・竣工が区別されないと使いにくいなぁ、
   当初、それらを色分けする構想はどこへ行ったんだぁ、
  それが出来たら、別々は要らないかも」

 なんて展開が頭に浮かびます。
 まぁ、部下としては、言われた都度、従うしかないので、言っても詮無いことですが。。

 私はそれが嫌で日付別集計とマクロを分けたんですけど、結局、マクロの手直しが必要になちゃいました。

 考え方としては、全貌復活への心変わりも視野に入れると、今の処理に追加する形で、
 その右隣りに各3列ずつ、着工・上棟・竣工の専門のデータを並べ、
 数式は、その先頭列を参照に行く(※)、と言う手が私の手間が一番少なくなるような気がしています。

 ※つまり、全貌はA列、着工はD列、上棟はG列・・を参照する数式にする

 ただ気にかかるのは、数式がOFFSET関数を使っていること。

 OFFSET関数は揮発性関数で、何かデータをいじると、全部の数式が再計算されます。
 シート数が多くなってくると重くなる弊害が生じます。

 まぁ、上司が満足する最終形が固まるまでの我慢で、最終形が決まった段階で
 全マクロ化を再質問してもらうのがいいかも知れません。

 上の構想で良ければ作成しますが、どうしますか?
     ↓
 (専用データ列追加。シートは「日付別集計」1枚のみ)

(半平太) 2019/06/21(金) 16:28


半平太さん

色々と考慮いただき、ありがとうございます。

>シート数が多くなってくると重くなる弊害が生じます。
↑確かに、シート数が多くなりました。

それで、エラーが出たのでしょうか?

>OFFSET関数は揮発性関数で、何かデータをいじると、全部の数式が再計算されます。

データをいじったせいで・・・
ということなのでしょうか。

全貌もたしかに後々出てきそうな予感はあります。

マクロ以外でいじったと考えると、カレンダーに土日祝の色を自動で振り分けること。
(条件付き書式にて)
あとは、関数をいじらないようにする保護をかけたぐらいです。

取急ぎは、エラーが改善できれば今のままでいきたいと考えております。
(りー) 2019/06/21(金) 17:26


 >それで、エラーが出たのでしょうか? 
 何の関係もありません。

 >データをいじったせいで・・・ 
 >ということなのでしょうか。 
 いえ、いじったと言うより、普通に使って、普通にデータを入力した時なので、揮発性関数の弊害です。

 >取急ぎは、エラーが改善できれば今のままでいきたいと考えております。
 そうですか、私はそちらでマクロをどうにかすることは
 想定しておりませんでしたので、これ以上深入りはしませんけど。

 >CollByDay  ← 名前が適切ではありません。 
 同じプログラム名が、3つのモジュールに書かれているので、
 どれにするのか分かんないと言うことじゃないかと思います。

 モジュール名て特定化を図るか、
 それぞれプロシージャ名を変えて対応関係を明確にするか、です。

    後者でやるなら、こんなので
             ↓
 Module1→着工 分は、CollByDayStarted
 Module2→上棟 分は、CollByDayFramed
 Module3→竣工 分は、CollByDayCompleted

(半平太) 2019/06/21(金) 17:55


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.