Hsieh

B­ì«h¤W¤]ºâ«O¯d¦r
­Y¿é¤JB¡AEXCEL·|¦Û°Ê§ï¦¨bb
Åã¥Ü¼Æ­È¥H1900¦~¬°43°_ºâªº¦~«×
1900¦~¤é´ÁÅã¥Ü¦¨43
1901¦~¤é´ÁÅã¥Ü¦¨44
¥H¦¹Ãþ±À
¥[¤WÅå¹Ä¸¹!©Î¤Ï±×½u\©Î¥ÎÂù¤Þ¸¹""
³£¥Nªí¨Ï¥Î¦r¤¸
¥u¬O¤Ï±×½u·|³QEXCEL¦Û°ÊÂনÅå¹Ä¸¹

oobird

§â§Ú°µªº¹Lµ{PO¤W¨Ó¡C

¤U¸ü (746.28 KB)

2010-12-3 00:05

gong

¥»¤]·Q¥Î»²§UÄæ,¦ýµLªk¹F¦¨ª½±µ¨ú±o­È
¥tºØ¤è¦¡¤]´£¨Ñ°Ñ¦Ò

fr447446

¦^´_ 18# gong


    GONG¤j¡G
§Ú¦b²Ä¤T±i¹Ï­n§ï¦¨²Ä¥|±i¹Ïªº®É­Ô­n¿é¤J =E$5 ªº¨BÆJ®É
¥X²{¡A"±zµLªk½s¿è¦¹­¶¡B¦¹Äæ¡B¦¹¦C¤¤ªºÄæ¦ìÀx¦s®æ"¡A³o¼Ëªº¿ù»~°T®§¡A
¤£ª¾¹DGONG¤j¬O¦p¦ó¿ì¨ìªº

gong

=E$5
¦pªG¥i¥H¿é¤J
¤£¬O«ö"ENTER"

­n«ö"CTRL+ENTER"

fr447446

¶â~¤S¾Ç¨ì¤F¤@­Ó¯S®íªº¥Îªk¡A
¨ü±Ð¤F~

FAlonso

¦^´_ 1# oobird
°µ¤FVBAµ{¦¡,¤£¹Lµ¥­Ô®É¶¡¸ûªø,¦³®Éµ{¦¡·|¦Û°Ê¤¤Â_+¦³Åã¥Ü¿ù»~,©Ò¥H¤£­nÀÝ¥ÎVBA

1. Option Explicit
   
2. Sub table()
   
3. Dim classnumber As Object, mycell As Range
   
4. Set classnumber = CreateObject("scripting.dictionary")
   
5. 
   
6. With Range("S1")
   
7. .Value = "¦Ò¥Í¸¹"
   
8. .Offset(, 1).Value = "©m¦W"
   
9. .Offset(, 2).Value = "¤Æ¾Ç"
   
10. .Offset(, 3).Value = "¥~»y"
    
11. .Offset(, 4).Value = "¥Íª«"
    
12. .Offset(, 5).Value = "¦a²z"
    
13. .Offset(, 6).Value = "ª«²z"
    
14. .Offset(, 7).Value = "¬Fªv"
    
15. .Offset(, 8).Value = "»y¤å"
    
16. .Offset(, 9).Value = "¼Æ¾Ç"
    
17. .Offset(, 10).Value = "¾ú¥v"
    
18. End With
    
19. 
    
20. Range("S2").Activate
    
21. 
    
22. For Each mycell In Sheet1.Range([a2], [a65536].End(xlUp))
    
23. If classnumber.exists(mycell.Value) Then
    
24. Else
    
25. classnumber.Add mycell.Value, mycell.Value
    
26. ActiveCell.Value = mycell.Value
    
27. ActiveCell.Offset(, 1).Value = mycell.Offset(, 1).Value
    
28. ActiveCell.Offset(, 2).Value = mycell.Offset(, 3).Value
    
29. ActiveCell.Offset(, 3).Value = mycell.Offset(1, 3).Value
    
30. ActiveCell.Offset(, 4).Value = mycell.Offset(2, 3).Value
    
31. ActiveCell.Offset(, 5).Value = mycell.Offset(3, 3).Value
    
32. ActiveCell.Offset(, 6).Value = mycell.Offset(4, 3).Value
    
33. ActiveCell.Offset(, 7).Value = mycell.Offset(5, 3).Value
    
34. ActiveCell.Offset(, 8).Value = mycell.Offset(6, 3).Value
    
35. ActiveCell.Offset(, 9).Value = mycell.Offset(7, 3).Value
    
36. ActiveCell.Offset(, 10).Value = mycell.Offset(8, 3).Value
    
37. ActiveCell.Offset(1).Activate
    
38. End If
    
39. Next
    
40. 
    
41. For Each mycell In Range("U2:Ac643")
    
42. If mycell.Value >= 85 Then
    
43. mycell.Value = "A"
    
44. ElseIf mycell.Value >= 65 And mycell.Value < 85 Then
    
45. mycell.Value = "B"
    
46. ElseIf mycell.Value < 65 Then
    
47. mycell.Value = "C"
    
48. End If
    
49. Next
    
50. 
    
51. Columns("S").NumberFormat = "0"
    
52. Columns("S").AutoFit
    
53. 
    
54. Set classnumber = Nothing
    
55. 
    
56. End Sub

½Æ»s¥N½X

¤£¹L©O,¥i§_­°§C¤U¸üªþ¥óªºªùÂe,¬Ý¨º»ò¦hµL·N¸qªº¦^©«

Hsieh

¦^´_ 39# FAlonso
³o½g­«ÂI¦b©ó°ò¥»§Þ¥©ºt½m¡A¨Ã¤£¹ªÀy¨Ï¥ÎVBA©Î¨ç¼Æ¸ÑÃD
¬JµM¥S¥x¨Ï¥ÎVBA¨Ó°µ¡A¦Ó¥B±z¤]´£¨ì¤@¨ÇVBA¨Ï¥Î¤Wªº§xÂZ
©³¤Uµ{¦¡½X¨Ñ±z°Ñ¦Ò¬Ý¬Ý
±z·|µo¥Í¿ù»~­ì¦]¥i¯àµo¥Í¦b
dictionaryª«¥ó¥[¤J¶µ¥Øªº¤èªk
±z¨Ï¥Î¤Fadd¤èªk¨Ó¥[¤J¶µ¥Ø
dictionaryª«¥ó¦³¤@­Ó¯S©Ê¡A´N¬O¯Á¤Þ­È¤£¯à­«ÂÐ
·íadd¤èªk¥[¤J¯Á¤Þ®É¡A­Y¸Ó¯Á¤Þ­È¤w¸g¦s¦b´N·|²£¥Í¿ù»~
ÁקK¦¹±¡ªpµo¥Í¡A¥i¥Îexists¤èªk¥ý§PÂ_¯Á¤Þ­È¬O§_¦s¦b¡AµM«á¦A¨M©w¬O§_¥[¤J
¦b±zªº¬yµ{¤¤dictionaryª«¥ó¬O¥Î¤£¨ìªº
¦Ó©³¤Uµ{¦¡½X§Q¥Îªº¤èªk¬Oª½±µ«ü©w¯Á¤Þ­È¹ïÀ³ªº­È
³o¼Ë¡A­È·|§ïÅÜ¡A¦ý¤£·|¦³­«½Æ¯Á¤ÞºÃ¼{
¦A«h±z´£¨ì³t«×°ÝÃD
¥[§Ö³t«×ªº¤èªk¦]­Ó®×¦U¦³¤£¦P
¥H¥»ÃD¬°¨Ò¡A­º¥ý¬O±N¸ê®Æ½d³ò¥H°}¦C«¬ºAŪ¨ú
ª½±µ¾a°O¾ÐÅéŪ¼g¡A·|¤ñ¤@®æ¤@®æªºÅª¼g§Ö«Ü¦h
¦b¨Ó¬O·í§A­n«ü©wÀx¦s®æ®É¡A­n¾¨¶q´î¤Öselectªº¨Ï¥Î
ª½±µ«ü©wÄæ¦C¦ì¤]·|¨Ï³t«×¥[§Ö
´î¤Ö¤£¥²­nªº´`Àô»P°Ê§@³£¬O¥[³tªº¤èªk
±z¥i¥H¦Û¤v¸Õ¸Õ¬Ý

1. Sub Ex()
   
2. Dim d As Object, d1 As Object, d2 As Object, Ar(), i&, A As Range, C As Range
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. Set d1 = CreateObject("Scripting.Dictionary")
   
5. Set d2 = CreateObject("Scripting.Dictionary")
   
6. d2("¦Ò¥Í¸¹") = "©m¦W"
   
7. Ar = Range([A1], [A65536].End(xlUp).Offset(, 3))
   
8. For i = 2 To UBound(Ar, 1)
   
9.    d(Ar(i, 1) & Ar(i, 3)) = IIf(Ar(i, 4) < 65, "C", IIf(Ar(i, 4) < 85, "B", "A"))
   
10.    d1(Ar(i, 3)) = ""
    
11.    d2("'" & Ar(i, 1)) = Ar(i, 2)
    
12. Next
    
13. [G:Q].ClearContents
    
14. Application.ScreenUpdating = False
    
15. [G1].Resize(d2.Count, 1) = Application.Transpose(d2.keys)
    
16. [H1].Resize(d2.Count, 1) = Application.Transpose(d2.items)
    
17. [I1].Resize(, d1.Count) = d1.keys
    
18. For Each A In Range([I1], [I1].End(xlToRight))
    
19.    For Each C In Range([G2], [G65536].End(xlUp))
    
20.       Cells(C.Row, A.Column) = d(C & A)
    
21.    Next
    
22. Next
    
23. End Sub

½Æ»s¥N½X

FAlonso

¦^´_ 40# Hsieh
¦hÁÂH¤j«üÂI

§Ú«Ü³ßÅwpivot table¨º­Ó¸Ñ¨M¿ìªk,§Ö±¶±o«Ü(ÁÙ¦³¬O«ç¼Ë¿ý»s¼v¤ùªº?)

zeny

pivotetable©Mvlookupºî¦XÀ³¥Î.rar (10.85 KB)

¤U¸ü¦¸¼Æ: 10

2011-8-12 20:15

«Ü´o¤H¡A ¦³­Ó43¤Àªº®æ¦¡¦Û©w¡A´N¬OÂà´«¤£¦¨C