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

[µo°Ý] ¦p¦ó±N¹ïÀ³ªº­È¦Û°Ê¶ñ¤J¬õ¦r®Ø®Ø¤º (ªþÀÉ®×)

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-10-22 14:34 ½s¿è

¦^´_ 8# mmokdtmj


EXCELªº¼Æ¦rªø«×³Ì¦h¢°¢´¦ì¡A¶W¹Lªº³£Åã¥Ü¢¯¡A¥i°Ñ¦Ò¥H¤Uºâªk¡G
¤Q¸U¬°¢µ¦ì¼Æ¡A¼Æ¦r*10^7¡A«h¥i±N¤p¼Æ¦A¶i¦ì¢¶¦ì¡A¦X­p¢°¢²¦ì¡A
¨Ò¦p¡G=INT(124715.272815689*10^7)¡@¡×¡@1247152728156

§ÀºÝ¡e¢±¦ì¡f«h«O¯dµ¹°}¦C°Ñ¼Æ{32,16,8,6,4,2,1}¡A
¨Ò¦p¡G=INT(124715.272815689*10^7)*100+{32,16,8,6,4,2,1}
«h²£¥Í¦p¤Uµ²ªG¡G{124715272815632,124715272815616,124715272815608,
124715272815606,124715272815604,124715272815602,124715272815601}

³Ì«á¥H RIGHT(??,2) ¨ú¥X«á¢±¦ì°Ñ¼Æ­È¡ã¡ã

¥i¨Ì¦¹ÅÞ¿è¥h±À­nºâ¤p¼Æªº¦ì¼Æ¡ã¡ã

ML089 ªO¥Dªº¤½¦¡¶Wµ´¡A¦ý­pºâÅÞ¿è´N½ÆÂø±o¦h¡A¬Ý±oÀ´¤]¥i±Ä¥Î¡ã¡ã¡@

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2015-10-22 14:47 ½s¿è

D24{=MID(INDEX($66,INT(RIGHT(MIN(IF(MOD(COLUMN($C8AG14),5)=3,ROUND(ABS(C$8:AG$14-C2),2)*10000+COLUMN(C$8:AG$14))),2)/5)*5+2),4,9)
E2:E4{=INDEX(B:B,RIGHT(MIN(IF(MOD(COLUMN($C8AG14),5)=3,ROUND(ABS(C$8:AG$14-C2),2)*10000+ROW(C$8:AG$14))),2))

TOP

D24{=MID(INDEX($66,INT(RIGHT(MIN(IF(MOD(COLUMN($C8AG14),5)=3,ROUND(ABS(C$8:AG$14-C2),2)*10000+COLUMN(C$8:AG$14))),2)/5)*5+2),4,9)
E2:E4{=INDEX(B:B,RIGHT(MIN(IF(MOD(COLUMN($C8AG14),5)=3,ROUND(ABS(C$8:AG$14-C2),2)*10000+ROW(C$8:AG$14))),2))

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-10-22 17:15 ½s¿è

D2:D4{=MID(INDEX($6:$6,INT(RIGHT(MIN(IF(MOD(COLUMN($C8:$AG14),5)=3,ROUND(ABS(C$8:AG$14-C2),2)*10000+COLUMN(C$8:AG$14))),2)/5)*5+2),4,9)
E2:E4{=INDEX(B:B,RIGHT(MIN(IF(MOD(COLUMN($C8:$AG14),5)=3,ROUND(ABS(C$8:AG$14-C2),2)*10000+ROW(C$8:AG$14))),2))

TOP

http://www.FunP.Net/225006

TOP

D2 =INDEX($B$8:$B$14,MID(MAX((ABS($C2-N(OFFSET($C$8,{0;1;2;3;4;5;6},{0,1,2,3,4,5,6}*5)))=MIN(ABS($C2-N(OFFSET($C$8,{0;1;2;3;4;5;6},{0,1,2,3,4,5,6}*5)))))*({7,6,5,4,3,2,1}*10+{1;2;3;4;5;6;7})),(D$1="QD")+1,1))
¤@¯ë¤½¦¡ ¥k©Ô1®æ¡A¤U©Ô2®æ

±µªñ­Èªº¨DªkÆ[©À
¨â¼Æªºµ´¹ï®t­È¶V¤p¶V±µªñ¡A·í®t­È¬°0®Éªí¥Ü¨â¼Æ¬Û¦P

¨â¼Æ®t­È°}¦C
= ABS($C2-N(OFFSET($C$8,{0;1;2;3;4;5;6},{0,1,2,3,4,5,6}*5)))

³Ì¤p®t­È
= MIN(¨â¼Æ®t­È°}¦C)
= MIN(ABS($C2-N(OFFSET($C$8,{0;1;2;3;4;5;6},{0,1,2,3,4,5,6}*5))))

³Ì¤p®t­È°}¦C¦ì¸m
= (³Ì¤p®t­È = ¨â¼Æ®t­È°}¦C)
= (MIN(ABS($C2-N(OFFSET($C$8,{0;1;2;3;4;5;6},{0,1,2,3,4,5,6}*5)))) = ABS($C2-N(OFFSET($C$8,{0;1;2;3;4;5;6},{0,1,2,3,4,5,6}*5))))

¨â¼Æ®t­È°}¦C¡B³Ì¤p®t­È°}¦C¦ì¸m ¬° 7*7 ªº°}¦C
««ª½¬° QD¶µ¦³7²Õ * ¤ô¥­¬°TC¶µ¦³7²Õ
QDTC¦ì¸m°}¦C
=QD¦ì¸m*10+TC¦ì¸m
=({7,6,5,4,3,2,1}*10+{1;2;3;4;5;6;7})

QDTC¦ì¸m²Õ¦X¼Æ­È
=MAX(³Ì¤p®t­È°}¦C¦ì¸m*QDTC¦ì¸m°}¦C)

TC©ÎQD¦ì¸mªº¼Æ­È
=MID(QDTC¦ì¸m²Õ¦X¼Æ­È,(D$1="QD")+1,1)


TC©ÎQD¼Æ­È°}¦C
=$B$8:$B$14

¨D¥XTC©ÎQD¼Æ­È
=INDEX(TC©ÎQD¼Æ­È°}¦C, TC©ÎQD¦ì¸mªº¼Æ­È)
=INDEX($B$8:$B$14, MID(QDTC¦ì¸m²Õ¦X¼Æ­È,(D$1="QD")+1,1))
=INDEX($B$8:$B$14, MID(MAX(³Ì¤p®t­È°}¦C¦ì¸m*QDTC¦ì¸m°}¦C),(D$1="QD")+1,1))
=INDEX($B$8:$B$14, MID(MAX((³Ì¤p®t­È = ¨â¼Æ®t­È°}¦C)*QDTC¦ì¸m°}¦C),(D$1="QD")+1,1))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

ÁÂÁ¤j®a, ML089 ªO¥D ªº¸Ô²Ó»¡©ú, §Ú·|¦b¬ãŪ¤F¸Ñ¤@¤U, §Úºâ¬Oªì¾ÇªÌ, Æ[©À»Ý­n¦b«Ø¥ß¤@¤U, ^^

TOP

¦^´_ 17# mmokdtmj

¤½¦¡½ÆÂø´N¬O§Aªº¸ê®Æ±Æ¦C¤£²Å¦X¸ê®Æ®w«¬¦¡

¼Ð·Ç¸ê®Æ®w¤@¯ë³£2ºû¸ê®Æªí¡A¦p IOps  ART  QD  TC

¥H IOps Äæ¥Ñ¤p¦Ü¤j±Æ¦C¡A³o­n¬d¸ß¥u­n¥ÎVLOOKUP¨ç¼Æ´N¯à²³æ¬d¸ß

»Ý­n¬Ýªºªí®æ¦A¥Ñ¸ê®Æ®w²£¥Í¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 18# ML089

¬Oªº, ´N¬O§Ú¦P¨Æ³o¼Ë±Æ, ¾É­P§Ú«Ü·Ð´o-_-"

TOP

        ÀR«ä¦Û¦b : ¦Y­W¤F­W¡B­WºÉ¤Ü¨Ó¡A¨ÉºÖ¤FºÖ¡BºÖºÉ´d¨Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD