[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『sheet1のデータをsheet2のカレンダーに反映したい』（み～）
オフィス2003 windowsXP
職員の名前と出勤日と時間と部署をsheet1に入力すると、
sheet2のカレンダーに反映するようにしたいのですが、
このようなことは可能なのでしょうか？
またsheet2のカレンダーの日にちを例えばsheet1のA1とB1が2008と10月と
入力するとSheet2のカレンダーも手入力ではなく
10月のカレンダーに変更することは可能なのでしょうか？
基本的に、sheet1は各自入力するのですが、
sheet2には入力せずに見るだけにしたいのですが。。。
sheet1は20人近い社員が入力するので、
sheet2のカレンダーは各自が簡単に確認できるようにする目的です。
sheet2の9/12でみると、R11に△、S11に事務、T11に16:30と入力されています。
カレンダーの一日に入る職員は最大6人としてあるので、
各日にち6行セルが空いています。
カレンダーで同じ出勤日、部署、時間がかぶったら、
その日にちの被っているセルの色を変えたりすることは可能なのでしょうか？
すいません。
ながなが上手く説明できていませんが、ご指導のほどお願いいたします。
sheet1
A B C D E
1 2008年 9月 2 名前 出勤日 部署 時間 3 □ 9/3 営業 15:30 4 ● 9/3 事務 16:00 5 △ 9/12 事務 16:30 6 × 9/18 経理 15:30 ・ ・ ・ ・ ・ ・ ・ ・ ・ ・ ・ ・
sheet2
A B CDE FGH IJK LMN OPQ RST UVW
1 2008年 9月
2 日 月 火 水 木 金 土
3 1 2 3 4 5 6
4 □営業15:30
5 ●事務16:00
6
7
8
9
10 7 8 9 10 11 12 13
11 △事務16:30
12
13
14
15
16
17 14 15 16 17 18 19 20
18 ×経理15:30
19
20
21
22
23
24 21 22 23 24 25 26 27
25
26
27
28
29
30 28 29 30
まずはカレンダー A B C D E F G H 1 2008 3 2 日 月 火 水 木 金 土 3 1 10 2 3 4 5 6 7 8 17 9 10 11 12 13 14 15 24 16 17 18 19 20 21 22 31 23 24 25 26 27 28 29 36 30 31 行番号が飛び飛びになっていますが データ グループとアウトライン設定で非表示にした結果です。 B3に =IF(MOD(DATE($A1,$B1,1),7)=MOD(COLUMN(A1),7),1,"") C3に =IF(B3="",IF(MOD(DATE($A1,$B1,1),7)=MOD(COLUMN(B1),7),1,""),B3+1) B10に C10に =H3+1 =B10+1 B17に C17に =H10+1 =B17+1 B24に C24に =B17+1 =B24+1 B31に =IF(DAY(DATE($A$1,$B$1,B17+14))<15,"",F24+3) C31に =IF(DAY(DATE($A$1,$B$1,C17+14))<15,"",C17+14) B38に =IF(DAY(DATE($A$1,$B$1,B24+14))<15,"",F31+3) C38に =IF(DAY(DATE($A$1,$B$1,C24+14))<15,"",C24+14)
C列右へフィールコピー
グループとアウトライン設定で 週単位で見るが便利かなとはおもいますが！！ （ABC)
Sheet1!E3に =day(B3)*10+countif(B$3:B3,B3) 下へフィールコピー （ABC)
下準備です。 やってみてください。 （ABC)
書式 標準 もしくは 数値で
１ ２ ３・・・１２ カレンダーの日付け １ 31 121 ２ 32 ３ ４ ５ ６ ↑ 同じ日付けの回数
E3に1/3１、E4に2/1、E5に4/30、E&に6/29 E3に31E4に32、E5に121、E&に181 で出た数字が上の表で対応位置がわかるかと その対応位置にあう関数で
とりあえず名前検出 ＝index(sheet1!$A$3:$A$5,match(B$3*10+row(A1),sheet1!$E$3:$E$5,0))
＝index(sheet1!$A$3:$A$5,match(B$3*10+row(A1),sheet1!$E$3:$E$5,0))&index(sheet1!$C$3:$C$5,match(B$3*10+row(A1),sheet1!$E$3:$E$5,0))
（ABC)
①名前と、部署がカレンダーへ反映されました。時間も同じように関数を入れてみたのですが、ものすごい配列の数字になってしまいました。16:00というような数字を反映するようなことは難しいのでしょうか。 ②カレンダーに表示する名前、部署、時間はスペースがなく表示されてしまって少し見にくくなってしまうのですが、この間にスペースをあけることは可能なのでしょうか？ ③名前等が入力されていないカレンダーの欄に#N/Aや#VALUE!といったものが表示されてしまっているので、カレンダーが見にくくなっています。これを表示できないようにすることは可能でしょうか？ 何度も質問申し訳ありません。徐々に完成してきて感激しています。的確なご指導いつもありがとうございます。 （み～）
途中 $E$3:$E$5,0))&index(sheet1 ＆の部分を &" "& とすれば ＆文字をつなぐ " " 空白文字
=if(countif(sheet1!$E$3:$E$5,B$3*10+row(A1)),index(sheet1!$A$3:$A$5,match ....),"") ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^ ^^^^の部分前後に追加 （ABC)
＝if(countif(sheet1!$E$3:$E$5,B$3*10+row(A1)),index(sheet1!$A$3:$A$5,match....),"")(B$3*10+row(A1),sheet1!$E$3:$E$5,0))&index(sheet1!$C$3:$C$5,match(B$3*10+row(A1),sheet1!$E$3:$E$5,0)) でよろしいのでしょうか？ 入力したのですが、エラーが出てしまいます。 （み～）
Sheet1のF列に作業列を設けてみては？ =A3&" "&C3&" "&TEXT(D3,"h:mm")
VLOOKUP関数も使えるようになると思います。 http://www.excel.studio-kazu.jp/lib/e1tw/e1tw.html
(HANA)
同じ日のデータは時刻の昇順に表示しないと見にくい気がします。 また、表示データの文字数も同じにしないと、やはり見づらい様な気がします。 それに配慮して、以下、ABCさんとHANAさんのアイデアを取り入れた別案です。ご参考にどうぞ
１．Sheet1 (1) F1セル： 同一日に6人を超える日が無いかチェック =IF(MAX(FREQUENCY(K3:K300,K3:K300))>6,"同日「6人超有り！」","OK")
(2) G1セル：月初日を出力 =DATE(A1,B1,1) (3) H1セル：月末を出力 =G1+32-DAY(G1+32) (4) I1セル：月初の曜日番号を出力 =WEEKDAY(G1)
(5) J1セル：氏 名の最大文字数+1を算出 =MAX(INDEX(LEN(A3:A300),0))+1 (6) K1セル：部署名の最大文字数+1を算出 =MAX(INDEX(LEN(C3:C300),0))+1
(7) F3セル =IF(COUNT(B3:D3)<2,"",LEFT(A3&REPT(" ",6),J$1)&LEFT(C3&REPT(" ",6),K$1)&RIGHT(TEXT(D3," [h]:mm"),5))
(8) G3セル =IF(F3="","",K3*10+COUNTIF(K$3:K3,K3))
(9) H3セル =IF(F3="","",(B3&TEXT(D3,"hhmm")&TEXT(ROW(),"0000"))*1)
(10) I3セル =IF(F3="","",SMALL($H$3:$H$300,ROW(A1)))
(11) J3セル =IF(F3="","",RIGHT(I3,4)*1)
(12) K3セル =IF(F3="","",INDEX(B:B,J3))
(13) F3:K3セルを一括選択して所要行数下にフィルコピー(300行を最大としています）
２．Sheet2
(1) A1セル =Sheet1!A1
(2) B1セル =Sheet1!B1
(3) B3セル =IF(MOD(COLUMN(A:A)-Sheet1!$I$1+ROW(A1)-1,100)+1>DAY(Sheet1!$H$1),"",COLUMN(A:A)-Sheet1!$I$1+ROW(A1))
(4) B4セル =IF(COUNTIF(Sheet1!$G$1:$G$300,OFFSET(B$1,INT((ROW(B1)-1)/7)*7+2,0)*10+MOD(ROW(B1)-1,7)+1),
INDEX(Sheet1!$F:$F,VLOOKUP(OFFSET(B$1,INT((ROW(B1)-1)/7)*7+2,0)*10+MOD(ROW(B1)-1,7)+1,Sheet1!$G$3:$J$300,4,FALSE)),"")
(5) B4セルの式をB9セルまでフィルコピー
(6) B3:B9セルを一括選択して、B44セルまでフィルコピー
(7) B3:B44セルを一括選択して、H列までフィルコピー
３．結果図 ※出力文字の並びを整える為、フォントは「P」の付かないタイプにしてください。
(1) Sheet1(サンプルデータ) 行 __A___ __B___ _C__ __D__ E ________ F ________ ___G____ ____H_____ ____I_____ __J___ __K___ 1 2008 1 OK 2008/1/1 2008/1/31 3 4 3 2 名前 出勤日 部署 時間 表示位置 日+時間+行 昇順 同行番 出勤日 3 □ 3 営業 15:30 □ 営業 15:30 11 315300003 108000010 10 1 4 ● 3 事務 16:00 ● 事務 16:00 31 316000004 315300003 3 3 5 △ 12 事務 16:30 △ 事務 16:30 32 1216300005 315300008 8 3 6 × 18 経理 15:30 × 経理 15:30 33 1815300006 316000004 4 3 7 × 3 事務 17:00 × 事務 17:00 34 317000007 317000007 7 3 8 ×× 3 人事 15:30 ×× 人事 15:30 61 315300008 609000011 11 6 9 半平太 31 雑用 15:00 半平太 雑用 15:00 62 3115000009 609000012 12 6 10 半平太 1 雑用 8:00 半平太 雑用 8:00 121 108000010 1216300005 5 12 11 半平太 6 雑用 9:00 半平太 雑用 9:00 181 609000011 1815300006 6 18 12 半平太 6 雑用 9:00 半平太 雑用 9:00 311 609000012 3115000009 9 31
(2) Sheet2(結果図-抜粋) 行 _A__ _________B_________ _C_ _________D_________ E ________ F ________ _________G_________ _________H_________ 1 2008 1 2 日 月 火 水 木 金 土 3 1 2 3 4 5 4 半平太 雑用 8:00 □ 営業 15:30 5 ×× 人事 15:30 6 ● 事務 16:00 7 × 事務 17:00 8 9 10 6 7 8 9 10 11 12 11 半平太 雑用 9:00 △ 事務 16:30 12 半平太 雑用 9:00 13 14 15 16 17 13 14 15 16 17 18 19 18 × 経理 15:30 19
４．条件付き書式 B4セルをスタートとして、B4:H44のセル範囲をドラッグ選択。 [数式が] → =AND((B4<>""),OR(RIGHT(B4,5)=RIGHT(B3,5),RIGHT(B4,5)=RIGHT(B5,5))) 色パターンを設定
(半平太)
ABCさん、HANAさん、半平太さん アドバイスありがとうございました。 想像よりもはるかにすごいものができました。 エクセルって参考書に載っていないものまで色々あるんですね。 （み～）
B3セルからなら、『セルの値が』→『数式が』へ変更して、=DAY(TODAY())=B3 でいいと思います。 (半平太)
この質問は5年前のものなので、新たにスレッドを立てた方がよかったと思いますが 一応回答します。
A B C D E F G 1 2015 1 2 日 月 火 水 木 金 土 3
A1セルに年、B1セルに月 A3セル =IF(OR(MOD(ROW(A3),10)=0,$B$1<>MONTH(DATE($A$1,$B$1,1)-WEEKDAY(DATE($A$1,$B$1,1))+COLUMN(A1)+7*(INT(ROW(A1)/10)))),"",DATE($A$1,$B$1,1)-WEEKDAY(DATE($A$1,$B$1,1))+COLUMN(A1)+7*(INT(ROW(A1)/10))) G3セルまでフィルコピー A3セルからG13セルを選択した状態で58行目までフィルコピー （se_9） 2015/03/24(火) 14:19
こちらで作成されているものとほぼ同じ形で、sheet1のデータをSheet2にカレンダーを作成して
かなり満足していたのですが、カレンダー内の行数スペースが全く足りず、質問させていただきました。
が、今回ご回答いただいた感じだと、もっといい方法が今はある、ということなのですね！
ご教示いただいた関数を入れてみたのですが、日にちの部分のみの変更のようで、まったくの素人の私には、
？？？という状態です。。。
A B C D E F G
1 2015 1
2 日 月 火 水 木 金 土
3
4
5
6
7
8
9
10
11
12
13
14 (次の週）
・
・
・
のようにしたいです。
週の日にちと翌週の日にちの間の行の関数は、以前のままでいいのでしょうか？？
何度も質問させていただき、申し訳ありませんが、ご教示よろしくお願いします。
また、もっと良い別のやり方があるようでしたら、そちらを教えてください。
（ころまま） 2015/03/25(水) 22:45
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.