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

[µo°Ý] ¦³Ãø«×ªºÅÞ¿è

[µo°Ý] ¦³Ãø«×ªºÅÞ¿è

¤½¥q­n¨Dªº¥\¯à,¦p³sµ²:
http://www.2shared.com/document/g-7rzfUv/test1.html
Ãø¦b¤À°t¤H¼Æªº¦Xªk©ÊÀˬd
½Ð±Ð­n«ç»ò§PÂ_?
ÁÂÁÂ!
Symis

¦^´_ 1# symis
§Ú¥u¯à·Q¨ì³o­Ó²Â¤èªk:
E4 °}¦C¤½¦¡¡G
=SUM(LEN(UPPER($J$3:$J$10))-LEN(SUBSTITUTE(UPPER($J$3:$J$10),C4,"")))
¦V¤U©Ô

E8 °}¦C¤½¦¡¡G
=SUM(E4:E5)

¦³¿ù»~®É, ¿é¤J¦³ÂI³Â·Ð¡G
(¦Ü¤Ö­n¾Þ§@¨â¦¸¥H¤W, ¬Ý·Ç­n­×§ïªº®æ¤l¦A­×§ï)
Á`¼Æ¦³¿ù»~®É, ¤£µ¹Äµ§i, ¥H§K¤Ó³Â·Ð, ¤Ï¥¿¥þ³¡¿é¤J§¹²¦´Nok¤F,
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2.     Dim a1, b1, c1, d1 As Integer, Rng As Range
  3.     Set Rng = Worksheets(1).Range("J3:J10")
  4.    
  5.    
  6.     If Not Intersect(Target, Rng) Is Nothing Then
  7.         [E4].FormulaArray = _
  8.                 "=SUM(LEN(UPPER($J$3:$J$10))-LEN(SUBSTITUTE(UPPER($J$3:$J$10),C4,"""")))"
  9.         [E5].FormulaArray = _
  10.                 "=SUM(LEN(UPPER($J$3:$J$10))-LEN(SUBSTITUTE(UPPER($J$3:$J$10),C5,"""")))"
  11.         [E6].FormulaArray = _
  12.                 "=SUM(LEN(UPPER($J$3:$J$10))-LEN(SUBSTITUTE(UPPER($J$3:$J$10),C6,"""")))"
  13.         [E7].FormulaArray = _
  14.                 "=SUM(LEN(UPPER($J$3:$J$10))-LEN(SUBSTITUTE(UPPER($J$3:$J$10),C7,"""")))"
  15.         [E8] = "=SUM(E4:E5)"
  16.         
  17.         If [E4] > 1 Then
  18.            MsgBox "A ³Ì¦h¥u¯à¦³1­Ó", vbCritical
  19.         End If
  20.         If [E5] > 2 Then
  21.            MsgBox "B ³Ì¦h¥u¯à¦³2­Ó", vbCritical
  22.         End If
  23.         If [E6] < 4 Then
  24.            MsgBox "C ³Ì¤Ö­n¦³4­Ó", vbCritical
  25.         End If
  26.         If [E7] < 1 Then
  27.            MsgBox "D ³Ì¤Ö­n¦³1­Ó", vbCritical
  28.         End If
  29.     End If
  30.    
  31. End Sub
½Æ»s¥N½X

TOP

¦^´_ 1# symis
³o­Ó¤èªk¤H¦h®É¤£¾A¥Î(¤Ó¦hĵ§i¤F)

TOP

·PÁÂyen956,¦ý¬O¿é¤J¥¿½T,¬°¦ó¤´¦³Äµ§i?
Symis

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2014-4-22 18:49 ½s¿è

©êºp, §Ú¦n¹³¦³°½§ï§AªºÀɮתº¼Ë¤l,
(¨â­Ó¹Ï¤£¤@¼Ë, ¯u©êºp, ±`¬°¤F¿ý¼v¦Ó压ÁY­ìªÅ¶¡)
¤S, ©Ò¿× °}¦C¤½¦¡
ÂI[E4], ¦b¡i¸ê®Æ½s¿è¦C¡j¿é¤J¤½¦¡,
¤£­n«ö Enter, §ï«ö Ctrl+Shift+Enter

TOP

¦^´_ 5# yen956

±z¤Ó«È®ð¤F,§Ú¸Õªºµ²ªG¤@¼Ë©O!
¬O¤£¬O§Ú¤]§âE5,E6,E7¤]·Ó°µ¦³Ãö?
¬d¤F¤@¤U,Ctrl+Shift+Enter ¬O°}¦C¤½¦¡
§Ú¦A¬ã¨s¤@¤U¦n¤F
ÁÂÁ±z!
Symis

TOP

¦^´_ 4# symis
§â B3:C7 °Å¤U, ¶K¨ì C3 ´N¥i¥H¤F, ¤½¦¡¤w¦b vba ¤¤

¦³Ãø«×ªºÅÞ¿è(¦Ò®Öªí).7z
http://www.mediafire.com/download/j90ni7j1evwrix5/%E6%9C%89%E9%9B%A3%E5%BA%A6%E7%9A%84%E9%82%8F%E8%BC%AF%28%E8%80%83%E6%A0%B8%E8%A1%A8%29.7z

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2014-4-23 08:33 ½s¿è

¦^´_ 6# symis
¦pªG¾ã±i¤u§@ªí, ¥u¦³³o¥|­Ó¤½¦¡¡G
[E4].FormulaArray = "=SUM(LEN(UPPER($J$3:$J$10))-LEN(SUBSTITUTE(UPPER($J$3:$J$10),C4,"""")))"
(©Î³\¤½¦¡À³§ï¬°¡G) "=SUM(LEN(UPPER($J$3:$J$10))-LEN(SUBSTITUTE(UPPER($J$3:$J$10),B4,"""")))"
....
¦A¤]¨S¦³¨ä¥Lªº¤½¦¡, «h¥i±N­ìVBA§R°£, §ï¥Î³o­Ó¸Õ¸Õ¬Ý¡G
(­ì¤½¦¡«O¯d)
  1. Private Sub Worksheet_Calculate()
  2.     If [E4] > 1 Then
  3.        MsgBox "A ³Ì¦h¥u¯à¦³1­Ó", vbCritical
  4.     End If
  5.     If [E5] > 2 Then
  6.        MsgBox "B ³Ì¦h¥u¯à¦³2­Ó", vbCritical
  7.     End If
  8.     If [E6] < 4 Then
  9.        MsgBox "C ³Ì¤Ö­n¦³4­Ó", vbCritical
  10.     End If
  11.     If [E7] < 1 Then
  12.        MsgBox "D ³Ì¤Ö­n¦³1­Ó", vbCritical
  13.     End If
  14. End Sub
½Æ»s¥N½X
¤S, ­ìVBA¦³»~(¤£¤p¤ß, ©êºp!!)
[E8] = "=SUM(E4:E5)"
À³§ï¬°
[E8] = "=SUM(E4:E7)"

TOP

¤j¤j¯u¼F®`!
´XÂI½Ð±Ð:
1. ¦p¦óÅýUSER­Y§ï¨ì³W©w¤H¼Æ®É,¤]·|´£¥Ü?
2. A,B,C,D¿é¤J¿ù»~·|´£¥Ü,¦ý¤´¥iÄ~Äò¾Þ§@,³o¼ËUSER¥i¤£²z? ÁÙ¬O,­Y¦³¿ù,«ö±¼°T®§«á,¦^´_¦^­È¤ñ¸û¦n?
3. macro3ªº¥Î³~¬O...?
Symis

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2014-4-25 16:44 ½s¿è
  1. 2. A,B,C,D¿é¤J¿ù»~·|´£¥Ü,¦ý¤´¥iÄ~Äò¾Þ§@,³o¼ËUSER¥i¤£²z? ÁÙ¬O,­Y¦³¿ù,«ö±¼°T®§«á,¦^´_¦^­È¤ñ¸û¦n?
½Æ»s¥N½X
¦^´_ 9# symis
[J4:J10] ¥Î¸ê®Æ¡÷Åçµý¡÷²M³æ, ¥i¨¾¨Ï¥ÎªÌ¿é¤J¿ù»~.
  1. 1. ¦p¦óÅýUSER­Y§ï¨ì³W©w¤H¼Æ®É,¤]·|´£¥Ü?
½Æ»s¥N½X
¨ä¹ê¥Î¡i¿ù»~·|´£¥Ü¡j¤£¬O¦n¤èªk¡A¤Ó¦h¡i¿ù»~·|´£¥Ü¡j·|¤¤Â_¾Þ§@
«Øij[E3:E8]¥i§ï¥Î¡i³]©w®æ¦¡¤Æ±ø¥ó¡j,¨Ò¦p¡G
·í B¯Å(§Y[E5]) ¤j©ó2®É, [E5]´NÅܦ¨º¡¦¿¬õ(¤½¦¡¤´¦b),
¦ý¤£·|¥X²{ĵ§i´£¥Ü, ¤£·|¤¤Â_¾Þ§@, ¦p¦ó?
  1. 3. macro3ªº¥Î³~¬O...?
½Æ»s¥N½X
¤£ª¾macro3¬O«ü¤°»ò?

TOP

        ÀR«ä¦Û¦b : ¡i®É¤é²öªÅ¹L¡j¤@­Ó¤H¦b¥@¶¡°µ¤F¦h¤Ö¨Æ¡A´Nµ¥©ó¹Ø©R¦³¦hªø¡C¦]¦¹¥²¶·»P®É¶¡Ävª§¡A¤Á²ö¨Ï®É¤éªÅ¹L¡C
ªð¦^¦Cªí ¤W¤@¥DÃD