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

¦³ÃC¦âÀx¦s®æ¼Æ¦r¥[Á`

¦³ÃC¦âÀx¦s®æ¼Æ¦r¥[Á`

¦U¦ì¤j¤j§A­Ì¦n
   ½Ð°Ý¬O§_¯à¦bC4Àx¦s®æ¤¤¼g¤@¨ç¦¡«áºâ¥XA¦C¤¤©Ò¦³¶À¦â©³¦âªº¼Æ¦r¥[Á`

¦^´_ 1# zamamilo

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim xR As Range, MyClr
For Each xR In Range([a1], Cells(Rows.Count, 1).End(3))
    MyClr = xR.DisplayFormat.Interior.ColorIndex
    If MyClr <> -4142 Then
        Cells(4, 3) = Cells(4, 3) + xR
    End If
Next
End Sub

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

ÁÂÁ¤T©ô¤j¤j,¦ý§Ú§Æ±æ¥Îªº¬O¨ç¦¡,ÁÙ¬OÁÂÁ§A
ÁÂÁ hcm19522¤j¤j,³o¤@½g§Ú¬Ý¹L¤£¬O§Ú­nªº,¦ý¤]ÁÂÁ§A

TOP

¦^´_ 2# samwang


    ÁÂÁ«e½ú
¦³¾Ç¨ìDisplayFormat.Interior.ColorIndex ¤~¯à¿ëÃѮ榡¤Æ±ø¥óªº¶À©³

TOP

¦^´_ 1# zamamilo


    ÁÂÁ«e½úµoªí¦¹¥DÃD
¥H¤U½d¨Ò ½Ð¸Õ¸Õ¬Ý
¦³ÃC¦âÀx¦s®æ¼Æ¦r¥[Á`_20221104.zip (17.37 KB)

AÄæ¨S¦³¶À©³®æ®É:


A1Àx¦s®æÅܶÀ©³®É:


¦pÃD¥Ø¥[Á`113 ®É


«D¼Æ¦rÀx¦s®æÅܶÀ©³®É:

TOP

ÃC¦â²Î­p¶·¥[¤u¦A³B²z, VBA¦Û­q¨ç¼Æ//



Xl0000147.rar (10.77 KB)

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-11-7 10:34 ½s¿è

¦^´_ 7# ­ã´£³¡ªL


    ÁÂÁ«e½ú«ü¾É
«á¾Çªì¨B»{ÃÑFunction ¦Û­q¸q¨ç¼Æ,­ì¨Ó¥¦¥i¥H¥Î¦bVBAµ{¦¡½X¸Ì,¤]¥i¥H·íÀx¦s®æ¤½¦¡ªº¨ç¼Æ
¥H¤U¾Ç²ß¤ß±oµù¸Ñ½Ð«e½ú¦A«ü¾É!

Function GetRangeColor(xA As Range, xArea As Range, xType%)
'¡ôFunction()¨ç¼Æ µ{¦¡¬O¤@¨t¦Cªº Visual Basic »y¥y¡A¥Ñ Function ©M End ¨ç¼Æ »y¥y©Ò¬A¦í¡C
'¨ç¼Æ µ{¦¡»P °Æµ{¦¡ Ãþ¦ü¡A¦ý¨ç¼Æ¤]¥i¥H¶Ç¦^­È¡C


'(xA As Range, xArea As Range, xType%)=(ÃC¦â®æ,¸ê®Æ°Ï°ì,­pºâ¼Ò¦¡)
'¥H¤U¥H[E3]¬°¨Ò: =GetRangeColor($D3,$A$1:$A$25,E$1)
'ÃC¦â®æ(xA):$D3  >>«ü©w[D3]Àx¦s®æ!¬O¦]¬°³]­pªÌ¦b«á¤èµ{§Ç·|§PŪ¦¹®æ©³¦â (¶À¦â)
'ª`·Nªº¬O:¤£¬O¦]¬°[D3]Àx¦s®æ¸Ì¦³ "¶À" ³o­Ó¦r! ¬Oµ{¦¡§PŪ¦¹®æ©³¦â¬O¶À¦â

'¸ê®Æ°Ï°ì(xArea):$A$1:$A$25  >>«ü©w³o­Ó°Ï°ìÀx¦s®æ!¬O¦]¬°³]­pªÌ¦b«á¤èµ{¦¡¸Ì
',§PŪ­þ´X®æªº©³¦â¸ò[D3]ªº©³¦â¤@¼Ë§@¬°±ø¥ó

'­pºâ¼Ò¦¡(xType):E$1   >>«ü©w³]­pªÌ²Î­pªº­þ¤@­Ó­È!³]­pªÌ¦b«á¤èµ{¦¡¸Ì²Î­p¤F
'¦X­p    ­Ó¼Æ    ¥­§¡­È  ³Ì¤j­È  ³Ì¤p­È >>¨Ï¥ÎªÌ¥i¥H¥Ñ 1~5 «ü©w
'¤]¥i¥Hª½±µ¿é¤J1 =GetRangeColor($D3,$A$1:$A$25,1)
'¬°¤°»ò¤£ª½±µ¿é¤J 1? ¦]¬°¦pªG¨ä¥¦Àx¦s®æ¤]¥Î[E1]·íÅܼÆ!§â[E1]§ï2 ´N·|¤@°_ÅÜ!¤£¥²¤@¤@§ï
'µ²½×:§PŪ $A$1:$A$25 Àx¦s®æ¸Ì,¦pªG©³¦â¬O¶À¦â,´N¦b [E3]Àx¦s®æ Åã¥Ü ¦X­p ­È

Dim xR As Range, X, S(5), C&
'¡ô«Å§iÅܼÆ,S(5)¬O§å¦¸«Å§i±qS(0)~S(5) ¦@¤»­Ó
Application.Volatile
'¡ô±N¨Ï¥ÎªÌ©w¸qªº¨ç¼Æ¼Ð¥Ü¬°©öÅÜ¡C
'¨C·í¤u§@ªí¤W¥ô¦óÀx¦s®æµo¥Í­pºâ®É¡A³£¥²¶·­«·s­pºâ©öÅܨç¼Æ¡C

X = xA.Interior.ColorIndex
'¡ô¥H[E3]¬°¨Ò:¦¹³B¤w¸gµ¹ GetRangeColor¨ç¼Æ²Ä¤@­Ó­È 6(¶À¦â)
For Each xR In xArea
'¡ô³]¶¶°j°é! ¥OxR¬O ¸ê®Æ°Ï°ì(xArea)¸Ìªº¤@­û
    If xR.Interior.ColorIndex = X Then
    '¡ô¥H[E3]¬°¨Ò:¦pªG[A1]¬OX(¶À¦â) ?? [A1]¬O¶À©³!Àx¦s®æ­È¬O 51
       S(0) = Val(xR.Value)
       '¡ô¥H[E3]¬°¨Ò:´NÅý²Ä¤@­ÓSÅܼƬO [A1]ªº¹Bºâ­È 51
       S(1) = S(1) + S(0) '¦X­p
       '¡ô¥H[E3]¬°¨Ò:Åý²Ä¤G­ÓSÅܼƲ֥[ ²Ä¤@­ÓSÅܼÆ0+51=51
       S(2) = S(2) + 1 '­Ó¼Æ
       '¡ô¥H[E3]¬°¨Ò:Åý²Ä¤T­ÓSÅܼƲ֥[ 0+1 = 1
       If S(2) > 0 Then S(3) = S(1) / S(2) '¥­§¡­È
       '¡ô¦pªG²Ä¤T­ÓSÅܼƤj©ó 0 ??´NÅý²Ä¥|­ÓSÅܼÆ= ²Ä¤@­ÓSÅܼÆ/²Ä¤G­ÓSÅܼÆ
       '¡ô¥H[E3]¬°¨Ò:(51/1)=51
       '¦]¬°¦pªG°£¦¡ªº¤À¥À¬O 0 °£¦¡ªº°Ó­È¬OµL­­¤j!³o­n¦h¤@­ÓIf §PÂ_¦¡Á×±¼!
       If S(0) > S(4) Then S(4) = S(0) '³Ì¤j­È
       '¡ô¦pªG²Ä¤@­ÓSÅܼƤj©ó ²Ä¤­­ÓSÅܼÆ! ´N²Ä¤­­ÓSÅܼƴN´«¸Ë²Ä¤@­ÓSÅܼÆ
       If S(5) = Empty Or S(0) < S(5) Then S(5) = S(0) '³Ì¤p­È
      '¡ô¦pªG²Ä¤»­ÓSÅܼƬOªì©l­È©Î ²Ä¤@­ÓSÅܼƤp©ó ²Ä¤»­ÓSÅܼÆ!
       '´N²Ä¤»­ÓSÅܼƴN´«¸Ë²Ä¤@­ÓSÅܼÆ

    End If
Next
GetRangeColor = S(xType)
'¡ô¨Ï¥Î GetRangeColor ³o­Ó¦Û­q¸q¨ç¼Æ!
'±ø¥ó§¹¾ã!´NÅýÀx¦s®æ©Î°Æµ{¦¡­pºâ­È!§_«h´N¸Ó°»¿ùÅo!
'¦pªGIJµo¥\¯à¬O¥¢®Ä¼Ò¦¡ Application.EnableEvents = False,
'GetRangeColor¨ç¼Æ¤]·|¥¢®Ä!¦³¥¢¯uªººÃ¼{!©Ò¥H¥Î©óÀx¦s®æ¤½¦¡¸Ì®É!­nª`·N!

End Function
Sub TTT()
Dim C As Range, AR As Range, T%
Set C = [SHEET1!D3]: Set AR = [SHEET1!A1:A25]: T = 1
MsgBox "[E3]Àx¦s®æ²Î­p[A1:A25]¶À©³®æ¦X­p­È" & GetRangeColor(C, AR, T)
End Sub

TOP

¦^´_ 7# ­ã´£³¡ªL


    ÁÂÁ­㴣³¡ªL¤j¤j

  ¸Ñ¨M¤F§Úªº°ÝÃD¤F

TOP

¦^´_ 1# zamamilo


¦pªG¤£®e³\ VBA ©Î ¦Û­q¨ç¼Æ¦p Get.Cell
§A¥i¥Hªþ¥[¤@¦C­I´ºÃC¦â¸ê®Æ (¤£¬üÆ[¥iÁôÂøӦC), ¦A¥Î Sumif ³B²z

ps 113 ¬O§A¤H¤â¿é¤J¡H

TOP

        ÀR«ä¦Û¦b : ¡i°±º¢¤£«e¡A²×µL©Ò±o¡j¤H³£°g©ó´M§ä©_ÂÝ¡A¦]¦Ó°±º¢¤£«e¡FÁa¨Ï®É¶¡¦A¦h¡B¸ô¦Aªø¡A¤]¤FµL¥Î³B¡A²×µL©Ò±o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD