¦p¦ó°µ²Ö¥[¶i¦ì

cathaylife

¦U¦ì¦n:
§Ú²{¦b¦b³B²z¤@­Ó°ÝÃD¦p¹Ï: AÄ檺¼Æ¦rº¥º¥¼W¥[(¤]³\·|´î¤Ö)
BÄæ¬O§PÂ_AÄæ¬O§_¤j©ó500
CÄæ¬O§PÂ_AÄæ¬O§_¤j©ó1000
±µ¤U¨Ó·|¬O. §PÂ_AÄæ¬O§_¤j©ó1500,2000,2500....
DÄæ«h¬O§â¥L­ÌB¡BC....¥[°_¨Ó

¦]¬°³o­Ó¼Æ­È¥i¯à·|¤j©ó500000 §Ú·Q­n¨C500´N°µ§PÂ_»Ý­nªºÄæ¦ì·|«D±`¦h
¦³¨S¦³¿ìªk¥Î1Äæ´N°µ¨ì§Ú·Q­nªº®ÄªG©O?

ANGELA

=LOOKUP(A1,(ROW($A$1:$A$9999)-1)*500+1,ROW($1:$9999)-1)
=LOOKUP(A1,{0;501;1001;1501;2001........},{0;1;2;3;4;.......})

­ã´£³¡ªL

¢Ò¢±¡G
=INT(MAX(0,A2-1)/500)

cathaylife

ANGELA§Ú¹ê¦b¬Ý¤£À´±zªº¤½¦¡¡A¥i¥H¸ÑÄÀ¸ÑÄÀ¶Ü>?

ANGELA

=LOOKUP(A1,(ROW($A$1:$A$9999)-1)*500+1,ROW($1:$9999)-1)

cathaylife

·P®¦¡A¦ý¬O­n«Ü¦hÄæ­CQQ

hcm19522

=($A2>500*COLUMN(A1))*1
¥k©Ô¦A¤U©Ô

cathaylife

¥i¬O§Úªº¼Æ¦r¥i¯à·|¤j©ó500000³o¼Ë¤£¬O¤Óªø¤F¶Ü?

rouber590324

search(".") : §PÂ_¸Ó¼Æ­È¬O§_¬°¾ã¼Æ.(µL . ªí¾ã¼Æ)
¥ÎIF(A2-500<0,A,IF(AND(A2-500>=0,A2-1000<0),B,.......¤§¤è¦¡§Y¥i

A=IF(ISERROR(SEARCH(".",A2/500)),ROUNDDOWN(A2/500,0)-1,ROUNDDOWN(A2/500,0))
B=IF(ISERROR(SEARCH(".",A2/1000)),ROUNDDOWN(A2/1000,0)-1,ROUNDDOWN(A2/1000,0))
C=IF(ISERROR(SEARCH(".",A2/2000)),ROUNDDOWN(A2/2000,0)-1,ROUNDDOWN(A2/2000,0))

cathaylife

HI·P®¦ ¦¨¥\¤F¡A¥u¬O§Ú¤£À´search(".")³o¸ÌªºÅÞ¿è¬O? ´M§ä¤p¼ÆÂI¶Ü>?
¥t¥~½Ð°Ý¦pªG»¡§Ú·Q­n¦b¤@¨ì500ªº®É­Ô¤U¤@­Ó¶i¦ì§ï¦¨1000
¦Ó¨ì1000®É´NÅÜ2000¸Ó«ç»ò°µ©O?
¥H³o­Ó¨ç¼ÆÁ|¨Ò´N¬O
¦bsum=1 ¤]´N¬OAÄæ¶W¹L500 ¥H«á¨ç¼ÆÅܦ¨
=IF(ISERROR(SEARCH(".",A2/1000)),ROUNDDOWN(A2/1000,0)-1,ROUNDDOWN(A2/1000,0))
sum=2 ¤]´N¬OAÄæ¶W¹L1000¥H«á¨ç¼Æ­±¦¨
=IF(ISERROR(SEARCH(".",A2/1500)),ROUNDDOWN(A2/1500,0)-1,ROUNDDOWN(A2/1500,0))