- ©«¤l
- 97
- ¥DÃD
- 33
- ºëµØ
- 0
- ¿n¤À
- 129
- ÂI¦W
- 0
- §@·~¨t²Î
- Win 7
- ³nÅ骩¥»
- office 2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2019-5-7
- ³Ì«áµn¿ý
- 2022-8-25
|
³o¬O§Ú°µ¥X¨Óªºµ²ªGµ¹¤j®a°Ñ¦Ò~~~
- Sub «ö¥X²{¦¸¼Æ±Æ§Ç()
- Application.ScreenUpdating = False
- Dim Arr, a, xD, Brr(1 To 20000, 1 To 2), N&
- ThisWorkbook.Sheets("¶q´ú").Range("H2:I" & Range("H" & Rows.Count).End(xlDown).Row).Clear
- Arr = Range("B2:F31")
- Set xD = CreateObject("Scripting.Dictionary")
- If ThisWorkbook.Sheets("¶q´ú").[B2] = "" Then Exit Sub
- For Each a In Arr
- If a = "" Then GoTo 101
- xD(a) = xD(a) + 1
- If xD(a) = 2 Then N = N + 1: xD(a & "S") = N: Brr(N, 1) = a
- If xD(a) > 1 Then Brr(xD(a & "S"), 2) = xD(a)
- 101: Next
- '±Æ§Ç
- If N = 0 Then Exit Sub
- With ThisWorkbook.Sheets("¶q´ú").[H2:I2].Resize(N)
- .Value = Brr
- .Sort Key1:=.Item(2), Order1:=xlDescending, _
- Key2:=.Item(1), Order2:=xlDescending, Header:=xlNo
- End With
- 'Àx¦s®æ¸m¤¤¡B®æ½u¡B¤p¼ÆÂI
- With ThisWorkbook.Sheets("¶q´ú").Range("H1:I" & Range("H" & Rows.Count).End(xlUp).Row)
- .NumberFormatLocal = "0.00_ "
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- .Borders(xlEdgeLeft).LineStyle = xlContinuous
- .Borders(xlEdgeTop).LineStyle = xlContinuous
- .Borders(xlEdgeBottom).LineStyle = xlContinuous
- .Borders(xlEdgeRight).LineStyle = xlContinuous
- .Borders(xlInsideVertical).LineStyle = xlContinuous
- .Borders(xlInsideHorizontal).LineStyle = xlContinuous
- End With
- With ThisWorkbook.Sheets("¶q´ú").Range("I1:I11")
- .NumberFormatLocal = "0"
- End With
- '§ä³Ì¤jÈ¡B³Ì¤pÈ
- With ThisWorkbook.Sheets("¶q´ú").Range("K1:L2")
- [k1].Value = "³Ì¤jÈ": [L1].Value = "³Ì¤pÈ"
- Range("K1:L1").Font.Bold = True
- Range("K1:L1").Name = "·s²Ó©úÅé"
- .Font.Size = 12
- Range("K1:L1").Interior.Color = RGB(217, 226, 243)
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- .Borders(xlEdgeLeft).LineStyle = xlContinuous
- .Borders(xlEdgeTop).LineStyle = xlContinuous
- .Borders(xlEdgeBottom).LineStyle = xlContinuous
- .Borders(xlEdgeRight).LineStyle = xlContinuous
- .Borders(xlInsideVertical).LineStyle = xlContinuous
- .Borders(xlInsideHorizontal).LineStyle = xlContinuous
- End With
- [k2] = Application.Max(Arr)
- [L2] = Application.Min(Arr)
- '§ä¹ïÀ³ªºÈ¡A¨Ã¶ñº¡ÃC¦â
- For x = 2 To 6
- For Y = 2 To 31
- Cells(Y, x).Interior.Color = xlNone
- If Cells(Y, x).Value = [H2].Value And [H2] <> "" Then
- Cells(Y, x).Interior.Color = RGB(252, 216, 162)
- [H2].Interior.Color = RGB(252, 216, 162)
- End If
- If Cells(Y, x).Value = [H3].Value And [H3] <> "" Then
- Cells(Y, x).Interior.Color = RGB(144, 248, 169)
- [H3].Interior.Color = RGB(144, 248, 169)
- End If
- If Cells(Y, x).Value = [H4].Value And [H4] <> "" Then
- Cells(Y, x).Interior.Color = RGB(170, 250, 252)
- [H4].Interior.Color = RGB(170, 250, 252)
- End If
- If Cells(Y, x).Value = [k2].Value Then
- Cells(Y, x).Font.Color = RGB(0, 0, 255)
- [k2].Font.Color = RGB(0, 0, 255)
- Cells(Y, x).Font.Bold = True
- End If
- If Cells(Y, x).Value = [L2].Value Then
- Cells(Y, x).Font.Color = RGB(255, 0, 0)
- [L2].Font.Color = RGB(255, 0, 0)
- Cells(Y, x).Font.Bold = True
- End If
- Next Y
- Next x
- '³]©wÄæ¼e
- Columns("A:L").ColumnWidth = 8
- Columns("G").ColumnWidth = 3
- Columns("J").ColumnWidth = 3
- End Sub
- '=================================
- Sub ²M°£()
- ThisWorkbook.Sheets("¶q´ú").Range("B2:F31").ClearContents
- With ThisWorkbook.Sheets("¶q´ú").Range("B2:F31")
- .NumberFormatLocal = "0.00_ "
- .Borders(xlEdgeLeft).LineStyle = xlContinuous
- .Borders(xlEdgeTop).LineStyle = xlContinuous
- .Borders(xlEdgeBottom).LineStyle = xlContinuous
- .Borders(xlEdgeRight).LineStyle = xlContinuous
- .Borders(xlInsideVertical).LineStyle = xlContinuous
- .Borders(xlInsideHorizontal).LineStyle = xlContinuous
- .Interior.Color = xlNone
- .Font.Bold = False
- .Font.Color = RGB(0, 0, 0)
- End With
- Range("B2").Select
- End Sub
½Æ»s¥N½X |
|