- ©«¤l
- 4901
- ¥DÃD
- 44
- ºëµØ
- 24
- ¿n¤À
- 4916
- ÂI¦W
- 43
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Office 20xx
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-4-30
- ³Ì«áµn¿ý
- 2025-2-19
               
|
¦^´_ 9# joey77373
¬O³o¼Ë¶Ü?- Public Rng As Range, MyMaxNum#, MyCol%, MyStr$
- Sub ²Ä¤@ÃD()
- Dim myInput, i As Integer, Ar As Variant, MyMax# ', MyStr
- Dim Str As String, Dic As Object
- myInput = InputBox("½Ð¿é¤JÁ`¦¾¬VªùÂeÈ(%):")
- Set Dic = CreateObject("Scripting.Dictionary")
- For i = 2 To 9
- If Not IsNumeric(Cells(i, 9)) Then MsgBox "I" & i & "½Ð¿é¤J¼Æ¦r": Exit Sub
- If Cells(i, 9) >= (myInput / 1) Then
- Dic(Cells(i, 1).Value) = Array(Cells(i, 9), Cells(i, 1))
- If Rng Is Nothing Then Set Rng = Cells(i, "L").Resize(, 6) Else Set Rng = Union(Rng, Cells(i, "L").Resize(, 6))
- End If
- Next i
- If Dic.Count = 0 Then MsgBox "¨S¦³²Å¦X±ø¥óªº¤¸¥ó": Set Rng = Nothing: Exit Sub
- TestStr = Join(Dic.keys, ",")
- Do Until Dic.Count = 0
- Ar = Application.Transpose(Application.Transpose(Dic.items))
- MyMax = Application.Max(Application.Index(Ar, , 1))
- MyStr = Application.VLookup(MyMax, Ar, 2, 0)
- MsgBox "¤¸¥ó " & MyStr & " " & MyMax
- Dic.Remove MyStr
- Loop
- End Sub
- Sub ²Ä¤GÃD()
- Dim MyTital$, Rw As Range
- If Rng Is Nothing Then MsgBox "¥¼¦³²Å¦X¤§¤¸¥ó¡A½Ð¥ý°õ¦æ²Ä¤@ÃD": Exit Sub
- If Application.Count(Rng) <> Rng.Count Then MsgBox Rng.SpecialCells(xlCellTypeConstants, 2).Address & "¬°«D¼Æȸê®Æ": Exit Sub
- MyCol = InputBox("·Å«Ç®ÄÀ³%¼Æ=1" & Chr(10) & "¯ä®ñ®ø¯Ó%¼Æ=2" & Chr(10) & "»Ä¤Æ%¼Æ=3" & Chr(10) & "Àu¾i¤Æ%¼Æ=4" & Chr(10) & "«ª÷ÄÝ%¼Æ=5" & Chr(10) & "PÀù©Ê%¼Æ=6", "½Ð¿é¤JÄæ¦ì", 1)
- For Each Rw In Rng.Rows
- If Rw.Cells(MyCol) > MyMaxNum Then MyMaxNum = Rw.Cells(MyCol): MyStr = Cells(Rw.Row, 1)
- Next
- MsgBox "¤¸¥ó " & MyStr & " " & Format(MyMaxNum, "0.00%")
- End Sub
- Sub ²Ä¤TÃD()
- Dim A As Range, Temp#
- Set A = [S:S].Find(MyStr, lookat:=xlWhole)
- Temp = A.Offset(, 7)
- Do Until Left(A, 1) <> MyStr
- If A.Offset(, MyCol) <= MyMaxNum Then
- If A.Offset(, 7) <= Temp Then Temp = A.Offset(, 7): Ch = A: Test = A.Offset(, MyCol)
- End If
- Set A = A.Offset(1)
- Loop
- MsgBox "´À¥N¤¸¥ó " & Ch & [S1].Offset(, MyCol) & "È= " & Test & "Á`¦¾¬VÅv«È= " & Temp
- End Sub
½Æ»s¥N½X |
|