[µo°Ý] ½Ð²¤Æ¿ý»sªºµ{¦¡½X¡C

ziv976688

´ú¸ÕÀÉ : ®æ¦¡¤Æ±ø¥ó¤½¦¡.rar (7.64 KB)

¤U¸ü¦¸¼Æ: 6

2019-5-21 05:39

¥H¤U¬OB2¡JF2,G2¡JK2,......, AU2¡JAX2®æ¦¡¤Æ±ø¥ó¤½¦¡¿ý»sªºµ{¦¡½X¡J
    Range("B2:F2").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(B2=MAX($B2:$F2))*(B2>0)"
    Selection.FormatConditions(1).Interior.ColorIndex = 43
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(SUMPRODUCT((B2<=$B2:$F2)/COUNTIF($B2:$F2,$B2:$F2))=2)*(B2>0)"
    Selection.FormatConditions(2).Interior.ColorIndex = 8
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(SUMPRODUCT((B2<=$B2:$F2)/COUNTIF($B2:$F2,$B2:$F2))=3)*(B2>0)"
    Selection.FormatConditions(3).Interior.ColorIndex = 37
   
    Range("G2:K2").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(G2=MAX($G2:$K2))*(G2>0)"
    Selection.FormatConditions(1).Interior.ColorIndex = 43
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(SUMPRODUCT((G2<=$G2:$K2)/COUNTIF($G2:$K2,$G2:$K2))=2)*(G2>0)"
    Selection.FormatConditions(2).Interior.ColorIndex = 8
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(SUMPRODUCT((G2<=$G2:$K2)/COUNTIF($G2:$K2,$G2:$K2))=3)*(G2>0)"
    Selection.FormatConditions(3).Interior.ColorIndex = 37
¡J
¡J
    Range("AU2:AX2").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(AU2=MAX($AU2:$AX2))*(AU2>0)"
    Selection.FormatConditions(1).Interior.ColorIndex = 43
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(SUMPRODUCT((AU2<=$AU2:$AX2)/COUNTIF($AU2:$AX2,$AU2:$AX2))=2)*(AU2>0)"
    Selection.FormatConditions(2).Interior.ColorIndex = 8
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(SUMPRODUCT((AU2<=$AU2:$AX2)/COUNTIF($AU2:$AX2,$AU2:$AX2))=3)*(AU2>0)"
    Selection.FormatConditions(3).Interior.ColorIndex = 37

¦@10¬q(½Ð¸Ô¨£´ú¸ÕÀÉ)

½Ð°Ý¡J¥i¥H¦A²¤Æ¶Ü?

PS¡J
¤½¦¡­n«O¯d¡C
¦pªG¥u¯à¤@¬q¤@¬q§ï¡A´N½Ð¥u§ï¤@¬q´N¥i¥H¤F¡A¨ä¥¦9¬q§Ú¦A®M¼g´N¦n!

ÁÂÁÂÀ°¦£!

Scott090

¦^´_ 1# ziv976688

   ½Ð§â±ø¥ó¥Î¥Õ¸Ü¤åÁ¬ݬÝ
    ¨Ò¦p  Range("B2:F2") ¤¤ªº
            ±ø¥ó   1. ³Ì¤j¼Æªº©³¦â¬O ....
            ±ø¥ó   2. ¦¸¤j .... ©³¦â¬O ....
            ±ø¥ó   3. ........
            °²¦p¼Æ¦r¤@¼Ë¤j ? ....
    ¤W­±Á|ªº   ¤£¤@©w¬O³o­Ó°ÝÃDªº»Ý¨D¡A¥u¬O¸ÕµÛ¥Î¤å¦r´y­z±ø¥ó»Pµ²ªG»Ý¨D
     ¦³®É¥Î¿ý»sªº¨Ó§ï¼g¤Ï¦Ó¦³¨Ç³Â·Ð¡A·íµM¦³®É¿ý»s·|¦³«Ü¤jªº§U¯q¡C

ziv976688

¦^´_ 2# Scott090
¨Ò¦p  Range("B2:F2") ¤¤ªº
            ±ø¥ó   1. ³Ì¤j¼Æªº©³¦â¬O ....43¸¹©³¦â
            ±ø¥ó   2. ¦¸¤j .... ©³¦â¬O ....8¸¹©³¦â
            ±ø¥ó   3. ........37¸¹©³¦â
            °²¦p¼Æ¦r¤@¼Ë¤j ? ....¦P¼Ë©³¦â¡CEX :¦³¤G­Ó©Î¤T­Ó¦P¬O³Ì¤j®É¡A«h³£¼Ð¥Ü43¸¹©³¦â¡F¦³¤G­Ó©Î¤T­Ó¦P¬O¦¸¤j®É¡A«h³£¼Ð¥Ü8¸¹©³¦â¡F¦³¤G­Ó©Î¤T­Ó¦P¬O¤T¤j®É¡A«h³£¼Ð¥Ü37¸¹©³¦â¡C

    ¥H¤W¨Ñ°Ñ !ÁÂÁ§A !

GBKEE

¦^´_ 3# ziv976688

¨Ì§AÀɮתºµ{¦¡½X©Ò²¤Æªº°j°é
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub ®æ¦¡¤Æ±ø¥ó()
   
3.     Dim Rng As Range, AR(1 To 3) As String,i As Integer
   
4.     Set Rng = Range("B2:F2")     '²Ä¤@­Ó½d³ò
   
5.     For i = 0 To 9                           '10½d³ò­n³B¸Ì
   
6.         With Rng
   
7.             '**Rng.Address                  ½d³òªºµ´¹ï¦ì¸m : $B2:$F2
   
8.             '**Rng(1).Address(0, 0)     ½d³òªº²Ä¤@­ÓCell ¬Û¹ï¦ì¸m B2
   
9.             ' *¤½¦¡¤@"=(B2=MAX($B2:$F2))*(B2>0)"
   
10.             AR(1) = "=(" & Rng(1).Address(0, 0) & "=MAX(" & Rng.Address & "))*(" & Rng(1).Address(0, 0) & ">0)"
    
11.             '* ¤½¦¡¤G"=(SUMPRODUCT((B2<=$B2:$F2)/COUNTIF($B2:$F2,$B2:$F2))=2)*(B2>0)"
    
12.             AR(2) = "=(SUMPRODUCT((" & Rng(1).Address(0, 0) & "<=" & Rng.Address & ")/COUNTIF(" & Rng.Address & "," & Rng.Address & "))=2)*(" & Rng(1).Address(0, 0) & ">0)"
    
13.             '* ¤½¦¡¤T"=(SUMPRODUCT((B2<=$B2:$F2)/COUNTIF($B2:$F2,$B2:$F2))=3)*(B2>0)"
    
14.             AR(3) = "=(SUMPRODUCT((" & Rng(1).Address(0, 0) & "<=" & Rng.Address & ")/COUNTIF(" & Rng.Address & "," & Rng.Address & "))=3)*(" & Rng(1).Address(0, 0) & ">0)"
    
15.             .Select
    
16.             .FormatConditions.Delete
    
17.             .FormatConditions.Add Type:=xlExpression, Formula1:=AR(1)
    
18.             .FormatConditions(1).Interior.ColorIndex = 43
    
19.             .FormatConditions.Add Type:=xlExpression, Formula1:=AR(2)
    
20.             .FormatConditions(2).Interior.ColorIndex = 8
    
21.             .FormatConditions.Add Type:=xlExpression, Formula1:=AR(3)
    
22.             .FormatConditions(3).Interior.ColorIndex = 37
    
23.         End With
    
24.         Set Rng = Rng.Offset(, Rng.Columns.Count)  '**¤U¤@­Ó½d³ò
    
25.     Next
    
26. End Sub

½Æ»s¥N½X

Scott090

¦^´_ 3# ziv976688

   ¬O³o¼Ë¶Ü?
     Option Explicit
Option Base 1

Sub Main_test()
      Dim sh As Worksheet
      Dim i%, j%, k%, colNo%, RowNo%
      Dim arWk%(5, 2), arColor()
      
      Set sh = Sheets("sheet1")
      '¥ýÁ٭쩳¦â
      sh.Range([B2], Cells(2, 50)).Interior.ColorIndex = 0
      
      arColor = Array(43, 8, 37)
      colNo = [B2].End(xlToRight).Column
      For i = 2 To colNo Step 5
            For j = 1 To 5
                  arWk(j, 1) = Cells(2, j + i - 1): arWk(j, 2) = i + j - 1
            Next
                              
            BubbleSortDesc arWk
            k = 1
            For j = 1 To 5
ColoringAgain:
                  If Val(arWk(j, 1)) <> 0 Then
                        Cells(2, arWk(j, 2)).Interior.ColorIndex = arColor(k)
                        If j + 1 > 5 Then Exit For
                        If arWk(j + 1, 1) = arWk(j, 1) Then j = j + 1: GoTo ColoringAgain
                        k = k + 1
                  End If
                  If k > 3 Then Exit For
            Next
            ReDim aewk(5, 2)
    Next
      
End Sub


'      °}¦C±Æ§Ç¥Ñ¤j¦Ó¤p
'======================
Sub BubbleSortDesc(arr)
    Dim arTemp%(2)
    Dim i%, j%, UB%
   
    UB = UBound(arr)
    For i = 1 To UB
        For j = i + 1 To UB
            If arr(i, 1) < arr(j, 1) Then
                arTemp(1) = arr(i, 1): arTemp(2) = arr(i, 2)
                arr(i, 1) = arr(j, 1): arr(i, 2) = arr(j, 2)
                arr(j, 1) = arTemp(1): arr(j, 2) = arTemp(2)
            End If
        Next j
    Next i

End Sub

Sub ®æ¦¡¤Æ±ø¥ó()
      Main_test
End Sub

ziv976688

¦^´_ 4# GBKEE
·PÁ¸ѵª¡C
½Ð¦A­×¥¿³Ì«á¤@¬q(²Ä10¬q)$AU : $AX2  ->¥u¦³4Äæ
°£¤F For i = 0 To 9 §ï¦¨= 0 To 8
½Ð°Ý : ²Ä9¬q­n«ç»ò¸É¼g?

ÁÙ¦³ª½°õ¦æ«áªº¤½¦¡ªº"¦C¦ì"¦h¤Fµ´¹ï²Å¸¹ " $ " ->EX : $B2 : $F2Åܦ¨ $B$2 : $F$2 ¡F$G2 : $K2Åܦ¨ $G$2 : $K$2¡F.......
©Ò¥HµLªk¦A½Æ»s®æ¦¡¨ì¨ä¥¦¦C¡C
½Ð°Ý : ­n¦p¦ó­×§ï?

¥H¤W½Ð§A­×¥¿¡CÁÂÁ§A^^

ziv976688

¦^´_ 5# Scott090
·PÁ¸ѵª¡C
°õ¦æ«á¡A«e¤T¤jªº(¤¤¦¡±Æ¦W)©³¦â¼Ð¥Ü¥¿½T¡A¦ý¤½¦¡¤£¨£¤F^^"
©Ò¥HµLªk¦A§@½Æ»s®æ¦¡¨ì¨ä¥¦¦C¤§¥Î¡C
½Ð§A­×¥¿¬°¯à±N¤½¦¡«O¯d¡CÁÂÁ§A^^

Scott090

¦^´_ 7# ziv976688

         ©êºp¡A¤£ª¾§A¬O­n«O¯d¥ÎÀx¦s®æ¨ç¼Æ¥h°õ¦æªº¡C
         ½Ð¥Î 4# GBKEE ¤j¤j ´£¨Ñªº¼Ò¦¡

ziv976688

¦^´_ 8# Scott090
¤£¦n·N«ä¡A§Ú¦bµo°Ý®É¡A´N¦³¯S§Oµù©ú"¤½¦¡­n«O¯d"~¥i¯à§A¨S¦³ª`·N¨ì
ÁÙ¬O«D±`·PÁ§A¤@¦AªºÀ°¦£

ziv976688

¦^´_ 4# GBKEE
¦³µ§»~~­«·s¦^ÂЩM»¡©ú¡C

·PÁ¸ѵª¡C
½Ð¦A­×¥¿³Ì«á¤@¬q(²Ä10¬q)$AU2 : $AX2  ->¥u¦³4Äæ¡F¤£¬O$AU2 : AY2
°£¤F For i = 0 To 9 §ï¦¨= 0 To 8
½Ð°Ý : ²Ä10¬q­n«ç»ò¸É¼g?

ÁÙ¦³°õ¦æ«áªº¤½¦¡ªº"¦C¦ì"¦h¤Fµ´¹ï²Å¸¹ " $ " ->EX : $B2 : $F2Åܦ¨ $B$2 : $F$2 ¡F$G2 : $K2Åܦ¨ $G$2 : $K$2¡F.......¡F$AP2 : $AT2Åܦ¨$AP$2 : $AT$2¡F$AU2 : $AX2Åܦ¨$AU$2 : $AX$2
©Ò¥HµLªk¦A¥Î"¤j±½§â"½Æ»s®æ¦¡¨ì¨ä¥¦¦C¡C
½Ð°Ý : ­n¦p¦ó­×§ï?

¥H¤W  ·Ð½Ð§A­×¥¿¡CÁÂÁ§A^^