[µo°Ý] ½Ð°Ý¦³msgbox¥Î"ª«¥ó¬d¸ß"¥¨¶°¯àÀ°¤p§Ì¸Ñ¨M°ÝÃD¶Ü?

joey77373

¤p§Ì¥Ø«e¥¿¦b½s¼g¬ã¨s½×¤å¡A¥Ø«e´N®t³Ì«á¤@¨BÆJ½×¤å´N§¹¦¨¤F¡C

±Ð±Â­n§Ú¦b®×¨Ò³¡¤À¸ê®Æ¶×¤Jexcel¡A¨Ã¨Ï¥Î¤º«Øvba¼gªº¥¨¶°¸É¦b³Ì«á¤@³¹¡AÅý¾ã­Ó®×¨Ò¦n¬Ý¨Ç

¤£¹L±Ð±Âª¾¹D³o¤£¬O§Ú­Ì±Mªø»â°ì¡A¦P·N§Ú¸ß°Ý·|vbaªº¤H¨ó§U

­Yª©¤W¦³°ª¤â¯àÀ°§U¡A¤p§Ì¯u¬O¦b·PÁ¤£¹L¡C

ªþ¥ó¤¤¤p§Ì´£°Ý¤F3­Ó«ü¥Oªº°ÝÃD¡A²Ä1ÃD¤p§Ì¦³¼g¤F¤@¤U¦ý¬O¶]¥Xªºµª®×µLªk±q¤j­P¤p±Æ¦C¡A¦ü¥G¤Ö¤F¨Ç«ü¥O¡C


¡°ªþ¥ó¤¤¦³2­ÓÀɮפ@­Ó¬°wordÀÉ¡A¸Ì­±´y­z¸ß°Ý°ÝÃD¡A¥t¤@­ÓÀɮ׬°excel¨Ò¤l¸ê®Æ¡AÁÙ¦³¤p§Ì¹q¤l«H½c
   ¥D­n¸ß°ÝÃö©ó"msgbox"¥¨¶°¡A»P°ÝÃD¥¨¶°³]©wµ¹¤©½d¨Ò«ü¥O°Ñ¦Ò¡A½Ð¹L¥Ø¡C

joey77373

¦^´_ 12# Hsieh



³o¦³¤F´X­Ó¥¨¶°Åý§Ú¯àÄ~Äò§¹¦¨½×¤å¥¼§¹¦¨³¡¤À¡C
ÁÂÁÂHsieh¤j¡A³o»ò¶O®ÉÀ°§U§Ú¡A¤Ó·PÁ§A¤F¡A¯uªº¥Ñ°J·PÁ¡C

Hsieh

¦^´_ 11# joey77373

1. Public Rng As Range, MyCol%, MyStr$
   
2. Sub ²Ä¤@ÃD()
   
3.     Dim myInput, i As Integer, Ar As Variant
   
4.     Dim Str As String, Dic As Object
   
5.     myInput = InputBox("½Ð¿é¤JÁ`¦¾¬VªùÂe­È(%):")
   
6.     Set Dic = CreateObject("Scripting.Dictionary")
   
7.     For i = 2 To 9
   
8.     If Not IsNumeric(Cells(i, 9)) Then MsgBox "I" & i & "½Ð¿é¤J¼Æ¦r": Exit Sub
   
9.       If Cells(i, 9) >= (myInput / 1) Then
   
10.          Dic(Cells(i, 1).Value) = Array(Cells(i, 9), Cells(i, 1))
    
11.          If Rng Is Nothing Then Set Rng = Cells(i, "L").Resize(, 6) Else Set Rng = Union(Rng, Cells(i, "L").Resize(, 6))
    
12.       End If
    
13.     Next i
    
14.     If Dic.Count = 0 Then MsgBox "¨S¦³²Å¦X±ø¥óªº¤¸¥ó": Set Rng = Nothing: Exit Sub
    
15.     TestStr = Join(Dic.keys, ",")
    
16.     Do Until Dic.Count = 0
    
17.      Ar = Application.Transpose(Application.Transpose(Dic.items))
    
18.      MyMax = Application.Max(Application.Index(Ar, , 1))
    
19.      MyStr = Application.VLookup(MyMax, Ar, 2, 0)
    
20.      MsgBox "¤¸¥ó  " & MyStr & "  " & MyMax
    
21.      Dic.Remove MyStr
    
22.     Loop
    
23. End Sub
    
24. Sub ²Ä¤GÃD()
    
25. Dim Rw As Range
    
26. If Rng Is Nothing Then MsgBox "¥¼¦³²Å¦X¤§¤¸¥ó¡A½Ð¥ý°õ¦æ²Ä¤@ÃD": Exit Sub
    
27. If Application.Count(Rng) <> Rng.Count Then MsgBox Rng.SpecialCells(xlCellTypeConstants, 2).Address & "¬°«D¼Æ­È¸ê®Æ": Exit Sub
    
28. MyCol = InputBox("·Å«Ç®ÄÀ³%¼Æ=1" & Chr(10) & "¯ä®ñ®ø¯Ó%¼Æ=2" & Chr(10) & "»Ä¤Æ%¼Æ=3" & Chr(10) & "Àu¾i¤Æ%¼Æ=4" & Chr(10) & "­«ª÷ÄÝ%¼Æ=5" & Chr(10) & "­PÀù©Ê%¼Æ=6", "½Ð¿é¤JÄæ¦ì", 1)
    
29. For Each Rw In Rng.Rows
    
30.    If Rw.Cells(, MyCol) > MyMaxNum Then MyMaxNum = Rw.Cells(, MyCol): MyStr = Cells(Rw.Row, 1)
    
31. Next
    
32. MsgBox "¤¸¥ó  " & MyStr & [K1].Offset(, MyCol) & Format(MyMaxNum, "0.00%")
    
33. End Sub
    
34. Sub ²Ä¤TÃD()
    
35. Dim A As Range, Temp#
    
36. Set A = [S:S].Find(MyStr, lookat:=xlWhole)
    
37. Temp = A.Offset(, 7)
    
38. Do Until Left(A, 1) <> MyStr
    
39.   If A.Offset(, MyCol) <= [A:A].Find(MyStr, lookat:=xlWhole).Offset(, MyCol) Then
    
40.      If A.Offset(, 7) <= Temp Then Temp = A.Offset(, 7): Ch = A: Test = A.Offset(, MyCol)
    
41.   End If
    
42.   Set A = A.Offset(1)
    
43. Loop
    
44. MsgBox "´À¥N¤¸¥ó " & Ch & [S1].Offset(, MyCol) & "­È= " & Test & "Á`¦¾¬VÅv­«­È= " & Temp
    
45. End Sub

½Æ»s¥N½X

joey77373

¦^´_ 10# Hsieh

¹ï!!¬O³o¼Ëªº!!¤Ó¼F®`¤F(½¤«ô)

¤p§Ì·|§âHsieh¤j¦b¼g½×¤å­PÁ¤W·PÁÂÀ°§U¡I

­è­è¸Õ¶]¡A¦³­Ó¤p°ÝÃD¡A²Ä¤GÃD¦U§O°w¹ï1¦Ü6(·Å«Ç®ÄÀ³%¼Æ=1¡B¯ä®ñ®ø¯Ó%¼Æ=2¡B»Ä¤Æ%¼Æ=3¡BÀu¾i¤Æ%¼Æ=4¡B­«ª÷ÄÝ%¼Æ=5¡B­PÀù©Ê%¼Æ=6)

¶]§¹«á¡A°õ¦æ²Ä¤TÃD
¦b²Ä¤GÃDIJµo1¥H«á¶i¤J²Ä¤TÃD¥X²{ªºµª®×¬O¿ùªº¡A¨ä¥L2¦Ü6³£¥¿±`¡C

¨ä¥L³£¨S°ÝÃD¡C

Hsieh

¦^´_ 9# joey77373
¬O³o¼Ë¶Ü?

1. Public Rng As Range, MyMaxNum#, MyCol%, MyStr$
   
2. Sub ²Ä¤@ÃD()
   
3.     Dim myInput, i As Integer, Ar As Variant, MyMax# ', MyStr
   
4.     Dim Str As String, Dic As Object
   
5.     myInput = InputBox("½Ð¿é¤JÁ`¦¾¬VªùÂe­È(%):")
   
6.     Set Dic = CreateObject("Scripting.Dictionary")
   
7.     For i = 2 To 9
   
8.     If Not IsNumeric(Cells(i, 9)) Then MsgBox "I" & i & "½Ð¿é¤J¼Æ¦r": Exit Sub
   
9.       If Cells(i, 9) >= (myInput / 1) Then
   
10.          Dic(Cells(i, 1).Value) = Array(Cells(i, 9), Cells(i, 1))
    
11.          If Rng Is Nothing Then Set Rng = Cells(i, "L").Resize(, 6) Else Set Rng = Union(Rng, Cells(i, "L").Resize(, 6))
    
12.       End If
    
13.     Next i
    
14.     If Dic.Count = 0 Then MsgBox "¨S¦³²Å¦X±ø¥óªº¤¸¥ó": Set Rng = Nothing: Exit Sub
    
15.     TestStr = Join(Dic.keys, ",")
    
16.     Do Until Dic.Count = 0
    
17.      Ar = Application.Transpose(Application.Transpose(Dic.items))
    
18.      MyMax = Application.Max(Application.Index(Ar, , 1))
    
19.      MyStr = Application.VLookup(MyMax, Ar, 2, 0)
    
20.      MsgBox "¤¸¥ó  " & MyStr & "  " & MyMax
    
21.      Dic.Remove MyStr
    
22.     Loop
    
23. End Sub
    
24. Sub ²Ä¤GÃD()
    
25. Dim MyTital$, Rw As Range
    
26. If Rng Is Nothing Then MsgBox "¥¼¦³²Å¦X¤§¤¸¥ó¡A½Ð¥ý°õ¦æ²Ä¤@ÃD": Exit Sub
    
27. If Application.Count(Rng) <> Rng.Count Then MsgBox Rng.SpecialCells(xlCellTypeConstants, 2).Address & "¬°«D¼Æ­È¸ê®Æ": Exit Sub
    
28. MyCol = InputBox("·Å«Ç®ÄÀ³%¼Æ=1" & Chr(10) & "¯ä®ñ®ø¯Ó%¼Æ=2" & Chr(10) & "»Ä¤Æ%¼Æ=3" & Chr(10) & "Àu¾i¤Æ%¼Æ=4" & Chr(10) & "­«ª÷ÄÝ%¼Æ=5" & Chr(10) & "­PÀù©Ê%¼Æ=6", "½Ð¿é¤JÄæ¦ì", 1)
    
29. For Each Rw In Rng.Rows
    
30.    If Rw.Cells(MyCol) > MyMaxNum Then MyMaxNum = Rw.Cells(MyCol): MyStr = Cells(Rw.Row, 1)
    
31. Next
    
32. MsgBox "¤¸¥ó  " & MyStr & "  " & Format(MyMaxNum, "0.00%")
    
33. End Sub
    
34. Sub ²Ä¤TÃD()
    
35. Dim A As Range, Temp#
    
36. Set A = [S:S].Find(MyStr, lookat:=xlWhole)
    
37. Temp = A.Offset(, 7)
    
38. Do Until Left(A, 1) <> MyStr
    
39.   If A.Offset(, MyCol) <= MyMaxNum Then
    
40.      If A.Offset(, 7) <= Temp Then Temp = A.Offset(, 7): Ch = A: Test = A.Offset(, MyCol)
    
41.   End If
    
42.   Set A = A.Offset(1)
    
43. Loop
    
44. MsgBox "´À¥N¤¸¥ó " & Ch & [S1].Offset(, MyCol) & "­È= " & Test & "Á`¦¾¬VÅv­«­È= " & Temp
    
45. End Sub

½Æ»s¥N½X

joey77373

¦^´_ 8# Hsieh

©êºpHsieh¤j¡AÀ³¸Ó¬O§Ú´y­zªº°ÝÃD¡A§Ú²Ä¤T­Ó°ÝÃDªºªí®æÅܰʤF¤@¤U³o¼Ë¤ñ¸û¦n´y­z§Úªº°ÝÃD

»P²Ä¤GÃDªºÃöÁp©Ê¬O¡A·íµ²ªGÅã¥Ü"C¤¸¥ó"·Å«Ç®ÄÀ³%¼Æ³Ì°ª"

©Ò¥H±q²Ä¤TÃDªº¤¸¥ó¸ê®Æ¤¤´M§ä"C"ªº´À¥N¤¸¥ó(SÄæ¦ì)¡A±q¸ê®Æ¥i¥H¬Ý¨ìC¦³4­Ó´À¥N¤¸¥ó(C-1¡BC-2¡BC-3¡BC-4)

µM«á¥ý¤ñ¸û´À¥N¤¸¥óªº"·Å«Ç®ÄÀ³"(TÄæ¦ì)ªº­È¦³¨S¦³¤ñ­ì¥»ªº°ª¡A°ªªº±Æ¥X(C-3¡BC-4)

³Ñ¤Uªº´À¥N¤¸¥ó(C-1¡BC-2)¡A´M§ä"Á`¦¾¬VÅv­«­È"(ZÄæ¦ì)¤ñ¸ûC-1»PC-2¿ï¾Ü¸û¤pªº­È¬°µª®×¡C

Hsieh

¦^´_ 7# joey77373
«e¨â­Ó°ÝÃD¬O§_¦³¹F¨ì§Aªº»Ý¨D?
²Ä¤T­Ó°ÝÃD¤´µM¬Ý¤£¥X»P«e¨â­Ó°ÝÃDªºÃöÁp©Ê
¬Oª½±µ¨ú±o³Ì¤p­È¨q¥X¡A»P«e­±ªº¿z¿ïµLÃö
­Y§Aªº»Ý¨D¨Ã¤£¬O¦p¦¹¡A½Ð¸Ô­z¨ä»P«e¨â­Ó°ÝÃDªºÃöÁp©Ê

joey77373

¦^´_ 6# Hsieh


·PÁÂHsieh´£¨Ñ¡A­è­è¿é¤J©óExcel¤¤run«á¡A¨t²Îª½±µ¥X²{¤¸¥óC-2Á`¦¾¬VÅv­«­È32.62

¹Lµ{¸ß°Ýµøµ¡¨S¦³¥X²{¡A¤£ª¾¹D¬O¤£¬O²Ä¤GÃD³¡¤Àªº°ÝÃD¡C

Hsieh

¦^´_ 5# joey77373

1. Public Rng As Range
   
2. Sub ²Ä¤@ÃD()
   
3.     Dim myInput, i As Integer, Ar As Variant, MyMax#, MyStr
   
4.     Dim Str As String, Dic As Object
   
5.     myInput = InputBox("½Ð¿é¤JÁ`¦¾¬VªùÂe­È(%):")
   
6.     Set Dic = CreateObject("Scripting.Dictionary")
   
7.     For i = 2 To 9
   
8.     If Not IsNumeric(Cells(i, 9)) Then MsgBox "I" & i & "½Ð¿é¤J¼Æ¦r": Exit Sub
   
9.       If Cells(i, 9) >= (myInput / 1) Then
   
10.          Dic(Cells(i, 1).Value) = Array(Cells(i, 9), Cells(i, 1))
    
11.          If Rng Is Nothing Then Set Rng = Cells(i, "L").Resize(, 6) Else Set Rng = Union(Rng, Cells(i, "L").Resize(, 6))
    
12.       End If
    
13.     Next i
    
14.     If Dic.Count = 0 Then MsgBox "¨S¦³²Å¦X±ø¥óªº¤¸¥ó": Set Rng = Nothing: Exit Sub
    
15.     TestStr = Join(Dic.keys, ",")
    
16.     Do Until Dic.Count = 0
    
17.      Ar = Application.Transpose(Application.Transpose(Dic.items))
    
18.      MyMax = Application.Max(Application.Index(Ar, , 1))
    
19.      MyStr = Application.VLookup(MyMax, Ar, 2, 0)
    
20.      MsgBox "¤¸¥ó  " & MyStr & "  " & MyMax
    
21.      Dic.Remove MyStr
    
22.     Loop
    
23. End Sub
    
24. Sub ²Ä¤GÃD()
    
25. Dim MyMax#, MyStr$, MyTital$, MyCol%, Rw As Range
    
26. If Rng Is Nothing Then MsgBox "¥¼¦³²Å¦X¤§¤¸¥ó¡A½Ð¥ý°õ¦æ²Ä¤@ÃD": Exit Sub
    
27. If Application.Count(Rng) <> Rng.Count Then MsgBox Rng.SpecialCells(xlCellTypeConstants, 2).Address & "¬°«D¼Æ­È¸ê®Æ": Exit Sub
    
28. MyCol = InputBox("·Å«Ç®ÄÀ³%¼Æ=1" & Chr(10) & "¯ä®ñ®ø¯Ó%¼Æ=2" & Chr(10) & "»Ä¤Æ%¼Æ=3" & Chr(10) & "Àu¾i¤Æ%¼Æ=4" & Chr(10) & "­«ª÷ÄÝ%¼Æ=5" & Chr(10) & "­PÀù©Ê%¼Æ=6", "½Ð¿é¤JÄæ¦ì", 1)
    
29. For Each Rw In Rng.Rows
    
30.    If Rw.Cells(MyCol) > MyMax Then MyMax = Rw.Cells(MyCol): MyStr = Cells(Rw.Row, 1)
    
31. Next
    
32. MsgBox "¤¸¥ó  " & MyStr & "  " & Format(MyMax, "0.00%")
    
33. End Sub
    
34. Sub ²Ä¤TÃD()
    
35. Dim MyMax#, MyStr$, MyTital$, MyCol%
    
36. If Application.Count([Z2:Z4]) <> [Z2:Z4].Count Then MsgBox [Z2:Z4].SpecialCells(xlCellTypeConstants, 2).Address & "¬°«D¼Æ­È¸ê®Æ": Exit Sub
    
37. MyMax = Application.Min([Z2:Z4])
    
38. k = Application.Match(MyMax, [Z2:Z4], 0)
    
39. MsgBox "¤¸¥ó  " & [S1].Offset(k) & " Á`¦¾¬VÅv­«­È " & MyMax
    
40. End Sub

½Æ»s¥N½X

joey77373

¦^´_ 2# Hsieh

§Ú¸É¥R¤@¤U¡A¦]¬°«e­±ªº´y­z§Úµ¹¦³µ{¦¡©³¤lªºªB¤Í¥L­Ì»¡¬Ý¤£À´
­n§Ú­×§ï¤è¦¡¥h´y­z
³o¼ËÁ¿À³¸Ó¤ñ¸û²M·¡

²Ä¤GÃD·Q§e²{ªºµ²ªG
©µ¦ù²Ä¤@ÃDªºµ²ªG¡A¤ñ¸ûL¨ìQ¦UÄæ¦ìªº¤j¤p¡A¨Ò¦p¡G¦¹®É§Ú¿é¤JÃöª`LÄæ¦ì
«h·|¥h¤ñ¸û¤¸¥óCªºLÄæ¦ì¡A¸ò¤¸¥óEªºLÄæ¦ì¤§¶¡¤j¤p¡AÅã¥Ü¥X¤jªº¼Æ­È

²Ä¤TÃD·Q§e²{ªºµ²ªG
¤ñ¸ûZÄæ¦ìªº¤j¤p¡A¿ï¾Ü³Ì¤pªº