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

¦p¦ó±N°ê¦rÂର¼Æ¦r

¦p¦ó±N°ê¦rÂର¼Æ¦r

½Ð°Ý¦³¤H·|±N°ê¦rÂର¼Æ¦r
¨Ò¦p:³°¨ÕÂର600
          ¥î¥a³°¨Õ¬m¬BÂର5670

·PÁÂ

http://blog.xuite.net/hcm19522/twblog/136417260

TOP

¦^´_ 2# hcm19522

«D±`·PÁÂ
ÁöµM§Ú¬Ý¤£À´
¦ý¸Ñ¨M¤F§Úªº°ÝÃD¤F

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2015-12-25 08:27 ½s¿è

¦^´_ 1# liling81

°Ñ¦Ò2¼Óªº¸ê®Æ¡A©óD2¿é¤J¤½¦¡¡A¤U©Ô½Æ»s¤½¦¡

D2 =SUM((1-ISERR(FIND({"³ü","¶L","°Ñ","¸v","¥î","³°","¬m","®Ã","¨h"}&{"¸U";"¥a";"¨Õ";"¬B";"¤¸"},A2)))*{1,2,3,4,5,6,7,8,9}*10^{4;3;2;1;0})&"¤¸¾ã"

¥u¯à­pºâ ¨h¸U¨h¥a¨h¨Õ¨h¬B¨h¤¸¥H¤U

6¤Ñ¤£¦b®a¨S¦³¿ìªk¥ß§Y¦^ÂÐ
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 4# ML089


    ¬Oªº,§Ú¥¿·Q¸ß°Ý
¬B¸U¥H¤Wªº­n«ç»ò³]©w
ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2015-12-25 11:53 ½s¿è

¦^´_ 5# liling81
¸Õ¸ÕVBA:
'========= ¨Ï¥Î¤èªk ==============
'1. «ö Alt+F11 ª½±µ¶i¤JVBA½s¿èµøµ¡(¥iÁ׶}¡u¶}µo¤H­û¡vªºÀˬd)
'2. ´¡¤J¡÷¼Ò²Õ(Module1)
'3. ±N¤U¦C¨â­Ó Function ¶K¨ì Module1
'4. ¦^¨ì¤u§@­¶
'5. °²©w B2=¥î¸U³°¥a¬m¨Õ®Ã¬B¤¸¾ã
'6. ¥ô¤@®æ =CH2Num(B2)
  1. '========= ±N¤¤¤åª÷ÃB¦Ü¤d¦ìÂন¼Æ­È, ¨Ñ¤U¦C Function ©I¥s¥Î ==============
  2. Function CHS2Num(CH As String) As Integer
  3.     Dim FD,  CN As String, Num As Integer
  4.     CN = "³ü¶L°Ñ¸v¥î³°¬m®Ã¨h"
  5.     Num = 0
  6.     FD = Application.Find("¥a", CH)
  7.     If IsNumeric(FD) Then
  8.         FD = Application.Find(Left(CH, 1), CN)
  9.         If IsNumeric(FD) Then
  10.             Num = Num + FD * 1000
  11.         End If
  12.     End If
  13.     FD = Application.Find("¨Õ", CH)
  14.     If IsNumeric(FD) Then
  15.         FD = Application.Find(Mid(CH, FD - 1, 1), CN)
  16.         If IsNumeric(FD) Then
  17.             Num = Num + FD * 100
  18.         End If
  19.     End If
  20.     FD = Application.Find("¬B", CH)
  21.     If IsNumeric(FD) Then
  22.         FD = Application.Find(Mid(CH, FD - 1, 1), CN)
  23.         If IsNumeric(FD) Then
  24.             Num = Num + FD * 10
  25.         End If
  26.     End If
  27.     FD = Application.Find(Right(CH, 1), CN)
  28.     If IsNumeric(FD) Then
  29.         Num = Num + FD
  30.     End If
  31.     CHS2Num = Num
  32. End Function

  33. '========= ±N¤¤¤åª÷ÃB¦Ü¥ü¦ìÂন¼Æ­È ==============
  34. Function CH2Num(CH As String) As Double
  35.     Dim FD, CNunit As String, Num As Double
  36.     Num = 0
  37.     CNunit = "¸U»õ¥ü"
  38.     FD = Application.Find("¥ü", CH)
  39.     If IsNumeric(FD) Then
  40.         Num = Num + CHS2Num(Left(CH, FD - 1)) * 10 ^ 12
  41.         CH = Right(CH, Len(CH) - FD)
  42.     End If
  43.     FD = Application.Find("»õ", CH)
  44.     If IsNumeric(FD) Then
  45.         Num = Num + CHS2Num(Left(CH, FD - 1)) * 10 ^ 8
  46.         CH = Right(CH, Len(CH) - FD)
  47.     End If
  48.     FD = Application.Find("¸U", CH)
  49.     If IsNumeric(FD) Then
  50.         Num = Num + CHS2Num(Left(CH, FD - 1)) * 10 ^ 4
  51.         CH = Right(CH, Len(CH) - FD)
  52.     End If
  53.     CH2Num = Num + CHS2Num(CH)
  54. End Function
½Æ»s¥N½X

TOP

¦^´_ 5# liling81

VBA ¦Û­q¨ç¼Æªk, ¥i¨ì¡e¥ü¡f¤¸¡G
http://forum.twbts.com/thread-16006-1-1.html

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2016-1-2 22:55 ½s¿è

¦^´_ 5# liling81

­Y¬O¸ê®Æ¶q¤p¥i¥H¨Ï¥Î¤U¦C¤½¦¡¡A¦]¬°¤½¦¡«Ü¯Ó­pºâ

¤½¦¡¥i¥H­pºâ¨ì[¥ü] 1~9999¥ü
=IF(COUNTIF(A2,"*¤¸*"),IFERROR(LOOKUP(,0/FIND(TEXT(ROW($1:$9999),"[DBNum2]G/³q¥Î®æ¦¡¤¸"),A2),ROW($1:$9999)),0),0)
+IF(COUNTIF(A2,"*¸U*"),IFERROR(LOOKUP(,0/FIND(TEXT(ROW($1:$9999),"[DBNum2]G/³q¥Î®æ¦¡¸U"),A2),ROW($1:$9999))*10^4,0),0)
+IF(COUNTIF(A2,"*»õ*"),IFERROR(LOOKUP(,0/FIND(TEXT(ROW($1:$9999),"[DBNum2]G/³q¥Î®æ¦¡»õ"),A2),ROW($1:$9999))*10^8,0),0)
+IF(COUNTIF(A2,"*¥ü*"),IFERROR(LOOKUP(,0/FIND(TEXT(ROW($1:$9999),"[DBNum2]G/³q¥Î®æ¦¡¥ü"),A2),ROW($1:$9999))*10^12,0),0)

¤½¦¡¥i¥H­pºâ¨ì[»õ] 1~9999»õ
=IF(COUNTIF(A2,"*¤¸*"),IFERROR(LOOKUP(,0/FIND(TEXT(ROW($1:$9999),"[DBNum2]G/³q¥Î®æ¦¡¤¸"),A2),ROW($1:$9999)),0),0)
+IF(COUNTIF(A2,"*¸U*"),IFERROR(LOOKUP(,0/FIND(TEXT(ROW($1:$9999),"[DBNum2]G/³q¥Î®æ¦¡¸U"),A2),ROW($1:$9999))*10^4,0),0)
+IF(COUNTIF(A2,"*»õ*"),IFERROR(LOOKUP(,0/FIND(TEXT(ROW($1:$9999),"[DBNum2]G/³q¥Î®æ¦¡»õ"),A2),ROW($1:$9999))*10^8,0),0)

¤½¦¡¥i¥H­pºâ¨ì[¸U] 1~9999¸U¤¸
=IF(COUNTIF(A2,"*¤¸*"),IFERROR(LOOKUP(,0/FIND(TEXT(ROW($1:$9999),"[DBNum2]G/³q¥Î®æ¦¡¤¸"),A2),ROW($1:$9999)),0),0)
+IF(COUNTIF(A2,"*¸U*"),IFERROR(LOOKUP(,0/FIND(TEXT(ROW($1:$9999),"[DBNum2]G/³q¥Î®æ¦¡¸U"),A2),ROW($1:$9999))*10^4,0),0)

¤½¦¡¥i¥H­pºâ¨ì[¤¸] 1~9999¤¸
=IF(COUNTIF(A2,"*¤¸*"),IFERROR(LOOKUP(,0/FIND(TEXT(ROW($1:$9999),"[DBNum2]G/³q¥Î®æ¦¡¤¸"),A2),ROW($1:$9999)),0),0)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

«D±`·PÁÂ
¤j®a³£¦n¼ö¤ß
³o¯u¬O¤@­Ó¦n©Ò¦b
ºCºCºN¯Á¤¤

TOP

        ÀR«ä¦Û¦b : ¹D¼w¬O´£ª@¦Û§Úªº©ú¿O¡A¤£¸Ó¬O¨þ¥¸§O¤HªºÃ@¤l¡C
ªð¦^¦Cªí ¤W¤@¥DÃD