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

[¤À¨É] ¥¿«hªí¹F¦¡(bosco_yip ­ì³Ð)

[¤À¨É] ¥¿«hªí¹F¦¡(bosco_yip ­ì³Ð)

¥¿«hªí¹F¦¡
µ¹°Ñ¦Ò :

°ÝÃD : ¨D¤@¦r²Å¦ê¦r¥À­Ó¼Æ
A1= ascd2G34dH_º~¦r!ya¡A¤£°Ï¤À¤j¤p¼g¡Cµª®×¬O9

1] ¨ç¼Æ¸Ñªk :

=SUMPRODUCT(--(ABS(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))-77.5)<13))

2] ¥Î"¥¿«hªí¹F¦¡" (Regular Expression), ¼gªº¦Û­q¨ç¼Æ :

Function CountAlpha(Mystr)
Dim RegExp As Object
Set RegExp = CreateObject("vbscript.regexp")
RegExp.Global = True
RegExp.ignorecase = True
RegExp.Pattern = "[^a-zA-Z]"
CountAlpha = Len(RegExp.Replace(Mystr.Value, ""))
End Function
¾Ç®üµL²P_¤£®¢¤U°Ý

¦^´_ 1# Hsieh
­ì¼gªk¬O¨ú¥N±¼¦r¦ê¤¤«D¦r¥Àªº¦r¤¸
³o¸Ìµy·L­×§ï , ´£¨Ñ¥t¤@ºØ¼gªk
Function CountAlpha(Mystr)
    Dim RegExp As Object
    Set RegExp = CreateObject("vbscript.regexp")
    RegExp.Global = True
    RegExp.Pattern = "[a-zA-Z]"
    CountAlpha = RegExp.Execute(Mystr.Value).Count
End Function

TOP

¦^´_ 1# Hsieh

¨D¦r¦ê¤¤¦³´X­Ó¦r¥À¡A´£¨Ñ¤@¦¡¦p¤U
=SUMPRODUCT((MID(UPPER(A1),ROW($1:$99),1)=CHAR(COLUMN(A:Z)+64))*1)

UPPER()¨ç¼Æ±N¤p¼g¦r¥À§ï¬°¤j¼g¦r¥À¡A¥iÁY´î¤j¤p¦r¥À§PÂ_

¦¹¦¡¾A¦X§@¬°¼Æ¾Ú°}¦C³B²zªº½d¨Ò¤½¦¡
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 2# toomellowhaw

¦p¦r¦ê¬O   "aA123bB"
=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(65:90)),))) ºâ¥X 2
(ºâ¥X¦h¤ÖºØ­^¤å¦r¥À¤£¤À¤j¤p¼g)

=SUMPRODUCT(--(ABS(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))-77.5)<13))  ºâ¥X 4
(ºâ¥X¦h¤Ö­Ó­^¤å¦r¥À¤£¤À¤j¤p¼g)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

·P觉¥¿则¦¡¨ä实¦bEXCEL应¥Î°_来ªº话¥\¯à¬O¬Û当强¤jªº, ¥u¬O¨ä¦Û©w义¨ç数ªº编写规则¦ü¥G难À´¡K¡K
¥u¦³·Q¤£¨ìªº,
没¦³°µ¤£¨ìªº.

TOP

¥»©«³Ì«á¥Ñ asimov ©ó 2010-9-26 01:05 ½s¿è

¦^´_ 2# toomellowhaw


toomellowhaw¥S¥Î¨ú¥Nªº¤è¦¡«Ü¦³±Òµo©Ê
¦¬¤U¾Ç²ß¤F,ÁÂÁÂ


Hsieh ª©¥Dªº¨ç¼Æ¸Ñªk¦ü¥G¤Ö¤F "&"
=SUMPRODUCT(--(ABS(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))-77.5)<13))
¾÷¾¹¤H¤T¤j©w«ß-«OÅ@¤HÃþ¡BªA±q©R¥O¡B«OÅ@¦Û¤v
§Úªº¤T¤j©w«ß-¥Í©R¡B¥Í¦s¡B¥Í¬¡

TOP

§Ú¤]¬O¥Ñbosco_yip «ü¾ÉªìÃÑ¥¿«hªí¹F¦¡ªº¡C

TOP

¨þ¡A¨SÃö«Y
¦b³o¨à³}³}¡A¬Ý¬Ý¤@¨Ç¥H«eªº¤å³¹¡A±`¯à«õ¨ìÄ_..
¬Ý¨ì¤@¨ÇÃD¥Ø¡A·Q¸Õ¸Õ¦Û¤v¯à¤£¯à¸Ñ¡A
½m¤@½m¨ç¼Æªº¥Îªk¡AÁٻݭn¤j®a¦h¦h«ü±Ð¡A
¦pªG¦³¯Ê¥¢©Î¦Ò¼{¤£©P¥þ¤§³B¡A°È½Ð«ü¥¿¡A·P®¦..

TOP

¦^´_ 4# toomellowhaw


    ©êºp!¬O§Ú¤@®É¬Ý¿ùÃD·N
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

°ÝÃDªº­ì·N¤£¬O¥u­nºâ­^¤å¦r¥À¼Æ¶Ü¡H..ÁÙ¬O§Ú»~·|¤F¡H

TOP

        ÀR«ä¦Û¦b : ¤f»¡¦n¸Ü¡B¤ß·Q¦n·N¡B¨­¦æ¦n¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD