[[20130819102754]]@wo‹àŠÇ—xi‚¹‚¢j@ƒy[ƒW‚ÌÅŒã‚É”ò‚Ô

[ ‰‚ß‚Ä‚Ì•û‚Ö | ˆê——(ÅVXV‡) | ‘S•¶ŒŸõ | ‰ß‹ŽƒƒO ]

@

wo‹àŠÇ—xi‚¹‚¢j‚d‚w‚Q‚O‚O‚O
 @  A      B
 1@—\	@8	—\’è˜J“­ŽžŠÔi‚W‚gj
 2@ŽÀ	@8	ŽÀ˜J“­ŽžŠÔ
 3@oŽž	  8:22	oŽÐŽžŠÔiƒ^ƒCƒ€ƒJ[ƒhj
 4@‘ÞŽž	  17:45	‘ÞŽÐiƒ^ƒCƒ€ƒJ[ƒhj
 5@Žc    @0	
 6@[Žc    0	

 ‹Î–±•\‚ªã‚̂悤‚É‚ ‚è
o‹ÎŽžŠÔ‚Æ‘ÞŽÐŽžŠÔ‚ð“ü—Í‚·‚邱‚Æ‚ÅŽÀ˜J“­ŽžŠÔAŽc‹ÆŽžŠÔA[–éŽc‹Æ
‚ðŽZo‚µ‚½‚¢‚Æl‚¦‚Ä‚¢‚Ü‚·B

 Šî–{˜J“­ðŒ‚Í‚WF‚R‚OoŽÐ‚P‚VF‚R‚O‘ގЂɂȂÁ‚Ä‚¨‚è“àA
1‚QF‚O‚O`‚P‚QF‚T‚Oi’‹‹xŒejA‚P‚TF‚O‚O`‚P‚TF‚P‚Oi‹xŒej
‚ª‚ ‚è‚P“ú‚WŽžŠÔ˜J“­‚Æ‚È‚è‚Ü‚·B
‚±‚ÌŽžŠÔŠOi‚WF‚R‚OˆÈ‘OA‚P‚VF‚S‚OˆÈ~j‚ÍŽc‹Æ‚Æ‚È‚è‚Ü‚·
‚»‚ÌÛ‚Í
 ‚P‚VF‚R‚O`‚P‚VF‚S‚Oi‹xŒejA‚Q‚OF‚S‚O`‚Q‚PF‚O‚Oi‹xŒej
‚ª—L‚èA‚Q‚QF‚O‚OˆÈ~‚Í[–éŽc‹Æ‚Æ‚È‚è‚Ü‚·

 ‚Ü‚½”¼“ú‹x‰É§“x‚ª‚ ‚肱‚Ì‚Æ‚«‚Í
‚WF‚R‚O`‚P‚QF‚R‚O‚Æ‚P‚RF‚Q‚O`‚P‚VF‚R‚O‚Ìo‹ÎŽžŠÔ‚ª‚ ‚è
‘OŽÒ‚ÌꇂWF‚R‚O‘O‚̘J“­‚Í‘o‚ÌŽc‹Æ
ŒãŽÒ‚Ì‚P‚VF‚R‚OˆÈ~‚Í’Êí‚ÌŽc‹Æ
‚Æ‚È‚è‚Ç‚¿‚ç‚à’‹‹xŒe‚Í–³‚­‚»‚Ì‘¼‚Ì‹xŒe‚Í’Êí’Ê‚è‚Å‚·

 ‚Ü‚½A‹Æ–±ó‹µ‚É‚æ‚Á‚ČߌãoŽÐi‚QŒð‘ãj‚ðŽÀŽ{‚·‚邱‚Æ‚ª‚ ‚è
‚±‚Ìê‡A‚P‚QF‚T‚O`‚Q‚QF‚O‚O‚ª’Êí‹Î–±ŽžŠÔ‚Æ‚È‚è
‹xŒe‚ª‚P‚TF‚O‚O`‚P‚TF‚P‚OA‚P‚VF‚O‚O`‚P‚VF‚T‚OA‚Q‚PF‚O‚O`‚Q‚PF‚P‚O
‚Æ‚È‚è‚Ü‚·B‚P‚QF‚T‚O‚æ‚è‘O‚ÍŽc‹ÆA‚Q‚QF‚O‚OˆÈ~‚Í[–éŽc‹Æ‚Å‚·

 Žc‹ÆŽžŠÔ‚Í‘S‚Ä‚R‚O•ª’PˆÊ‚ÅŒvŽZ‚µ‚Ü‚·B
ˆÈオŽ„‚̉ïŽÐ‚̋Ζ±ƒ‹[ƒ‹‚Å‚·

 ˆÈ‘O‚ÌŽ¿–⎞‚É”¼•½‘¾—l‚ÉŽc‹Æ‚Æ[–éŽc‹Æ‚̃AƒhƒoƒCƒX‚ð‚¢‚½‚¾‚«
’ÊíoŽÐŽž‚Ì”Ž®‚ð‹³‚¦‚Ä‚¢‚½‚¾‚«
‰º‹L‚̂悤‚É‚µ‚Ü‚µ‚½

 Žc@=(FLOOR(TEXT(MAX(0,"8:30"-oŽÐŽžŠÔ),"h:mm"),"0:30")+FLOOR(TEXT(MAX(0,MIN@("22:00",‘ÞŽÐŽžŠÔ)-"17:30")-MAX(0,MIN("17:40",‘ÞŽÐŽžŠÔ)-"17:30")-MAX(0,MIN("21:00",‘ÞŽÐŽžŠÔ)-"20:40"),"h:mm"),"0:30"))*24

  [Žc@=FLOOR(TEXT(MAX(0,‘ÞŽÐŽžŠÔ-"22:00"),"h:mm"),"0:30")*24

 ŽZoƒ‹[ƒ‹“™‚Ìà–¾‚ª–³‚¢‚Æ‚²Žw“E‚ðŽó‚¯Ä“xŽ¿–₳‚¹‚Ä‚¢‚½‚¾‚¢‚Ä‚¢‚Ü‚·

 ‚±‚̂悤‚ÈŒ`‚Å‚¢‚©‚ª‚Å‚µ‚傤‚©
‘«‚è‚È‚¢à–¾‚ª‚ ‚è‚Ü‚µ‚½‚ç‹X‚µ‚­‚¨Šè‚¢‚µ‚Ü‚·B

i‚¹‚¢j


 ‚Æ‚¢‚¤‚©AŒð‘Öo‹Î‚È‚Ì‚©H”¼“úo‹Î‚È‚Ì‚©H

 ‚Ç‚¤”»’f‚·‚é‚ÌH

 12:30‚É‹A‚é‚Æ‚©‚Ȃ炸”¼“ú‚É‚È‚éEEE‚Á‚Ä‚±‚Æ‚È‚ÌH
 (‘‘Þ‚Å‚È‚­‚Äj

 (GobGob)

 ”¼“ú‚Í1s–Ú‚Ìu—\’èv‚Æo‹ÎŽžŠÔ‚©‚ç”»’f‚Å‚«‚é‚©EEEE

 ‚¯‚ÇAŒð‘Ö‚ð‚Ç‚¤‚·‚é‚©‚¾‚ËBi’x{Žc‹Æ ‚Æ ŒßŒãoŽÐ‚ð‚Ç‚¤”»’f‚·‚é‚ÌHj

 (GobGob)

 ‘‘¬‚Ì•ÔM‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·
‚²Žw“E‚Ì’Ê‚è‚Å‚·‚˗Ⴆ‚Îs‚ð‚ӂ₵‚ăZƒ‹‚É“ú‚Æ‚©–é‚Æ‚©“ü‚ê‚é‚悤‚É
‚µ‚ÄŽ©“®”»’f‚Å‚«‚Ü‚·‚©Hƒ}ƒNƒ‚É‚È‚Á‚Ä‚µ‚Ü‚¢‚Ü‚·‚©


 œSheet2 ‚ÉŠe‹Î–±‚ÌÚ×B

	A	B	C	D	E
1		A	B	C	D
2	ŽžŠÔ	8:00	8:00	4:00	4:00
3	oŽÐ	8:30	12:50	8:30	13:20
4	‘ÞŽÐ	17:30	22:00	12:30	17:30
5	’‹	12:00	17:00		
6	10•ª1	15:00	15:00		15:00
7	10•ª2		21:00		
8	10•ª3	17:30		15:00	
9	20•ª	20:40			

 1s–Ú Ac’ÊíABc2Œð‘ãACcŒß‘O”¼“úADcŒßŒã”¼“ú
 2s–Ú ‹K’èA‹ÆŽžŠÔ
 3E4s–Ú ‹K’èoŽÐ/‘ÞŽÐŽžŠÔ
 5s–Ú ’‹‹xŒe ŠJŽnŽžŠÔ
 6E7s–Ú 10•ª‹xŒe ŠJŽnŽžŠÔ i‹K’莞ŠÔ“àj
 8s–Ú 10•ª‹xŒe ŠJŽnŽžŠÔi‹K’莞ŠÔŠOj
 9s–Ú 20•ª‹xŒe ŠJŽnŽžŠÔi‹K’莞ŠÔŠOj

 œSheet1‚Éo‹Î•\

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			Šî–{ŽžŠÔ			ŽÀÑ	T/C		’x/‘		‹xŒe					Žc‹Æ	
2	Ž–¼	‹Î–±	—\’è	o‹Î	‘ÞŽÐ	A‹Æ	o‹Î	‘ÞŽÐ	’x	‘‘Þ	’‹	10•ª1	10•ª2	10•ª3	20•ª	’Êí	[–é
3	A‚³‚ñ	A	8:00	8:30	17:30	8:00	8:22	17:45	0:00	0:00	0:50	0:10	0:00	0:00	0:00	0:00	0:00
4	B‚³‚ñ	A	8:00	8:30	17:30	6:30	8:22	16:00	0:00	1:30	0:50	0:10	0:00	0:00	0:00	0:00	0:00
5	C‚³‚ñ	B	8:00	12:50	22:00	8:30	12:00	22:15	0:00	0:00	0:50	0:10	0:10	0:00	0:00	0:30	0:00
6	D‚³‚ñ	B	8:00	12:50	22:00	8:00	14:00	23:30	1:30	0:00	0:50	0:10	0:10	0:00	0:00	0:00	1:30
7	E‚³‚ñ	C	4:00	8:30	12:30	4:30	8:10	13:10	0:00	0:00	0:00	0:00	0:00	0:00	0:00	0:30	0:00
8	F‚³‚ñ	C	4:00	8:30	12:30	6:10	9:00	15:30	0:30	0:00	0:00	0:00	0:00	0:10	0:00	2:50	0:00
9	G‚³‚ñ	D	4:00	13:20	17:30	4:30	13:00	18:20	0:00	0:00	0:00	0:10	0:00	0:00	0:00	0:30	0:00
10	H‚³‚ñ	D	4:00	13:20	17:30	3:30	13:10	17:00	0:00	0:30	0:00	0:10	0:00	0:00	0:00	0:00	0:00
11	I‚³‚ñ	A	8:00	8:30	17:30	11:00	8:15	21:45	0:00	0:00	0:50	0:10	0:00	0:10	0:20	3:30	0:00

 A—ñ‚É–¼‘OA
 B`E—ñ‚ɋΖ±–ˆ‚Ì‹K’è’l

 B—ñ‚ɋΖ±iA`Dj
 C3 =HLOOKUP($B3,Sheet2!$B$1:$E$2,2,0)
 D3 =HLOOKUP($B3,Sheet2!$B$1:$E$3,3,0)
 E3 =HLOOKUP($B3,Sheet2!$B$1:$E$4,4,0)

 F`Q—ñ‚ÉŽÀÑ

 F3i‡ŒvŽžŠÔj =IF(G3="","",E3-D3-SUM(I3:O3)+SUM(P3:Q3))

 G3,H3‚̓^ƒCƒ€ƒJ[ƒhŽÀт𔽉f

 I3i’xj =IF(G3="","",CEILING(TEXT(MAX(G3-D3,0),"h:mm"),"0:30"))
 J3i‘‘Þj =IF(H3="","",FLOOR(TEXT(MAX(0,E3-H3),"h:mm"),"0:30"))

 K`O—ñ c ‹xŒe

 K3 =IF($G3="","",AND(VLOOKUP(K$2,Sheet2!$A$1:$E$9,MATCH($B3,Sheet2!$A$1:$E$1,0),0)+"0:50"<=$E3+FLOOR($H3-$E3,"0:30"),VLOOKUP(K$2,Sheet2!$A$1:$E$9,MATCH($B3,Sheet2!$A$1:$E$1,0),0)>=$D3-FLOOR($D3-$G3,"0:30"))*"0:50") 
 L3 =IF($G3="","",AND(VLOOKUP(L$2,Sheet2!$A$1:$E$9,MATCH($B3,Sheet2!$A$1:$E$1,0),0)+"0:10"<=$E3+FLOOR($H3-$E3,"0:30"),VLOOKUP(L$2,Sheet2!$A$1:$E$9,MATCH($B3,Sheet2!$A$1:$E$1,0),0)>=$D3-FLOOR($D3-$G3,"0:30"))*"0:10")
 L3‚ðN3‚܂ŃRƒs[B
 O3 =IF($G3="","",AND(VLOOKUP(O$2,Sheet2!$A$1:$E$9,MATCH($B3,Sheet2!$A$1:$E$1,0),0)+"0:20"<=$E3+FLOOR($H3-$E3,"0:30"),VLOOKUP(O$2,Sheet2!$A$1:$E$9,MATCH($B3,Sheet2!$A$1:$E$1,0),0)>=$D3-FLOOR($D3-$G3,"0:30"))*"0:20")

 P`Q—ñ Žc‹Æ

 P3 =IF(G3="","",MAX(0,FLOOR(D3-G3,"0:30"))+MAX(0,FLOOR(MIN("22:",H3)-E3,"0:30")-SUM(N3:O3)))
 Q3 =IF(I3="","",MAX(0,FLOOR(H3-"22:","0:30")))

 K3:Q3 ‰º‚ÖƒRƒs[B
 F3 ‰º‚ÖƒRƒs[B

 ¦[•ª‚ÉŠm”F‚µ‚Ä‚È‚¢‚Ì‚Å‚¤‚Ü‚¢‚±‚Æ‚Å‚«‚Ä‚é‚©‚í‚©‚è‚Ü‚Ö‚ñB

 (GobGob)

 Q3‚Ì”Ž®•”•ª‚őގЂª‚Q‚QF‚O‚O‘O‚¾‚Æ”‚m‚t‚lI‚ª‚Å‚Ä
F3‚É‚à“¯‚¶ƒGƒ‰[‚ªo‚Ü‚·
‚Ç‚Ì‚ ‚½‚肪‰e‹¿‚µ‚Ä‚¢‚é‚Ì‚Å‚µ‚傤‚©
i‚¹‚¢j

 ƒIƒCƒ‰‚̓Gƒ‰[‚Å‚È‚¢‚ñ‚¾‚¯‚ÇBBBB

 “ü—Í‚ª‚¨‚©‚µ‚¢‚Æ‚©A‘ÞŽÐŽžŠÔ‚É“ú•t‚ª”½‰f‚³‚ê‚Ä‚é‚Æ‚©BBBB

 (GobGob)

ƒRƒƒ“ƒg•ÔMF

[ ˆê——(ÅVXV‡) ]


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