[ ‰‚ß‚Ä‚Ì•û‚Ö | ˆê——(Å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)
[ ˆê——(ÅVXV‡) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.