ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] °ÊºA°Ñ·Ó¤½¦¡ªº½s¼g

[µo°Ý] °ÊºA°Ñ·Ó¤½¦¡ªº½s¼g

ªþ¥ó¡G °ÊºA°Ñ·Ó¤½¦¡ªº½s¼g.rar (11.21 KB)
M3=IF(OR($L$1="",INDEX(M:M,MATCH($L1,$H:$H,0))=""),"",MOD($M$1-M2,39))
¥k©Ô¨ìQ3
M3ªº¤½¦¡»Ý¨D¡G
·í$L$1=""©ÎINDEX(M:M,MATCH($L1,$H:$H,0))=""®É¡A«hM3=""¡F§_«hM3=($M$1-M2)/39ªº¾l¼Æ¡C

¥H¤U¡K¡K.¦P²zÃþ±À¡C
S3=IF(OR($L$1="",INDEX(S:S,MATCH($L1,$H:$H,0))=""),"",MOD($S$1-S2,39))
¥k©Ô¨ìW3

Y3=IF(OR($L$1="",INDEX(Y:Y,MATCH($L1,$H:$H,0))=""),"",MOD($Y$1-Y2,39))
¥k©Ô¨ìAC3

AE3=IF(OR($L$1="",INDEX(AE:AE,MATCH($L1,$H:$H,0))=""),"",MOD($AE$1-AE2,39))
¥k©Ô¨ìAI3

¬õ¦rªº¦ì§}¥HINDIRECT¨ç¼Æ¡AµLªk¹F¨ì°ÊºA°Ñ·Óªº»Ý¨D¡C

½Ð°Ý¡G¥ÑM3¥k©Ô¨ìAI3¤@¦¡¨ì¦ìªº¨ç¼Æ¤½¦¡­n¦p¦ó½s¼g¡H

¦^´_ 6# hcm19522
¨ü¯q¨}¦h
ÁÂÁ¤j¤jªº¼ö¤ßÀ°¦£©M­@¤ß«ü¾É~·P®¦

TOP

¦^´_ 5# ziv976688


    M10{=IF(($B11="")+(M2=""),"",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:M$1)-N(OFFSET($A10,,MOD(COLUMN(A1),6))),10)),OFFSET($A10,,MOD(COLUMN(A1),6)),""))
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 3# hcm19522
ªþ¥ó : ¨ç¼Æ_°ÊºA°Ñ·Ó¤½¦¡ªº½s¼g-0120.rar (12 KB)
¤£¦n·N«ä¡AÁÙ¦³¤@­ÓÃþ¦üªº»Ý¨D¡A¥½¾Ç«_¬N¦A½Ð±Ð :
M10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:M$1)-B10,10)),B10,""))}
M10ªº¤½¦¡»Ý¨D¡G
·í$L$1=""®É¡A«hM10=""¡F·í($B10:$F10)/10ªº¾l¼Æ=($B11:$F11)/10ªº¾l¼Æ¡F«hM10=B10¡A§_«hM10=""¡C
M10¥k©Ô¨ìQ10¡F
Q10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:Q$1)-F10,10)),F10,""))}

¥H¤U¡K¡K.¦P²zÃþ±À¡C
S10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:S$1)-B10,10)),B10,""))}
S10¥k©Ô¨ìW10
W10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:W$1)-F10,10)),F10,""))}

Y10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:Y$1)-B10,10)),B10,""))}
Y10¥k©Ô¨ìAC10
AC10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:AC$1)-F10,10)),F10,""))}

AE10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:AE$1)-B10,10)),B10,""))}
AE10¥k©Ô¨ìAI10
AI10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:AI$1)-F10,10)),F10,""))}

½Ð°Ý¡G¥ÑM10¥k©Ô¦A¤U©Ô¨ìAI21¤@¦¡¨ì¦ìªº¨ç¼Æ¤½¦¡­n¦p¦ó½s¼g¡H

TOP

¦^´_ 3# hcm19522
´ú¸ÕOK
ÁÂÁ±zªºÀ°¦£©M«ü¾É~·P®¦

TOP

$M$1§ïLOOKUP(99,$M1:M1)
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

M3=IF(OR($L$1="",INDEX(M:M,MATCH($L1,$H:$H,0))=""),"",MOD($M$1-M2,39))
¥k©Ô¨ìQ3
M3ªº¤½¦¡»Ý¨D¡G
·í$L$1=""©ÎINDEX(M:M,MATCH($L1,$H:$H,0))=""®É¡A«hM3=""¡F§_«hM3=($M$1-M2)/39ªº¾l¼Æ¡C

¥H¤U¡K¡K.¦P²zÃþ±À¡C
S3=IF(OR($L$1="",INDEX(S:S,MATCH($L1,$H:$H,0))=""),"",MOD($S$1-S2,39))
¥k©Ô¨ìW3

Y3=IF(OR($L$1="",INDEX(Y:Y,MATCH($L1,$H:$H,0))=""),"",MOD($Y$1-Y2,39))
¥k©Ô¨ìAC3

AE3=IF(OR($L$1="",INDEX(AE:AE,MATCH($L1,$H:$H,0))=""),"",MOD($AE$1-AE2,39))
¥k©Ô¨ìAI3

¬õ¦rªº¦ì§}¥HINDIRECT¨ç¼Æ¡AµLªk¹F¨ì°ÊºA°Ñ·Óªº»Ý¨D¡C

½Ð°Ý¡G¥ÑM3¥k©Ô¨ìAI3¤@¦¡¨ì¦ìªº¨ç¼Æ¤½¦¡­n¦p¦ó½s¼g¡H

TOP

        ÀR«ä¦Û¦b : ¥¬¬I¦p¼½ºØ¡A¥HÅw³ß¤ß´þ¼íºØ¤l¡A¤~·|µoªÞ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD