ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¤½¦¡»P°ò¥»§Þ¥©ºt½m

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¦Û°ÊÂনÅå¹Ä¸¹
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

µû¤À¤H¼Æ

    • gong: GOOD,¾Ç²ß¤¤ª÷¿ú + 2

TOP

¥»©«³Ì«á¥Ñ gong ©ó 2010-12-3 22:00 ½s¿è

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

1.GIF (20.39 KB)

1.GIF

2.GIF (19.39 KB)

2.GIF

3.GIF (40.81 KB)

3.GIF

4.GIF (15.87 KB)

4.GIF

5.GIF (50.94 KB)

5.GIF

6.GIF (30.7 KB)

6.GIF

7.GIF (15.74 KB)

7.GIF

1

µû¤À¤H¼Æ

    • oobird: ¤£¿ù¡A¤S¬O¥t¤@­Ó­«­n§Þ¥©¡Cª÷¿ú + 2
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

¦^´_ 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¦ó¿ì¨ìªº
¨S¨Æ´N¨Ó³}³}

TOP

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

­n«ö"CTRL+ENTER"
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

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

TOP

¦^´_ 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. With Range("S1")
  6. .Value = "¦Ò¥Í¸¹"
  7. .Offset(, 1).Value = "©m¦W"
  8. .Offset(, 2).Value = "¤Æ¾Ç"
  9. .Offset(, 3).Value = "¥~»y"
  10. .Offset(, 4).Value = "¥Íª«"
  11. .Offset(, 5).Value = "¦a²z"
  12. .Offset(, 6).Value = "ª«²z"
  13. .Offset(, 7).Value = "¬Fªv"
  14. .Offset(, 8).Value = "»y¤å"
  15. .Offset(, 9).Value = "¼Æ¾Ç"
  16. .Offset(, 10).Value = "¾ú¥v"
  17. End With

  18. Range("S2").Activate

  19. For Each mycell In Sheet1.Range([a2], [a65536].End(xlUp))
  20. If classnumber.exists(mycell.Value) Then
  21. Else
  22. classnumber.Add mycell.Value, mycell.Value
  23. ActiveCell.Value = mycell.Value
  24. ActiveCell.Offset(, 1).Value = mycell.Offset(, 1).Value
  25. ActiveCell.Offset(, 2).Value = mycell.Offset(, 3).Value
  26. ActiveCell.Offset(, 3).Value = mycell.Offset(1, 3).Value
  27. ActiveCell.Offset(, 4).Value = mycell.Offset(2, 3).Value
  28. ActiveCell.Offset(, 5).Value = mycell.Offset(3, 3).Value
  29. ActiveCell.Offset(, 6).Value = mycell.Offset(4, 3).Value
  30. ActiveCell.Offset(, 7).Value = mycell.Offset(5, 3).Value
  31. ActiveCell.Offset(, 8).Value = mycell.Offset(6, 3).Value
  32. ActiveCell.Offset(, 9).Value = mycell.Offset(7, 3).Value
  33. ActiveCell.Offset(, 10).Value = mycell.Offset(8, 3).Value
  34. ActiveCell.Offset(1).Activate
  35. End If
  36. Next

  37. For Each mycell In Range("U2:Ac643")
  38. If mycell.Value >= 85 Then
  39. mycell.Value = "A"
  40. ElseIf mycell.Value >= 65 And mycell.Value < 85 Then
  41. mycell.Value = "B"
  42. ElseIf mycell.Value < 65 Then
  43. mycell.Value = "C"
  44. End If
  45. Next

  46. Columns("S").NumberFormat = "0"
  47. Columns("S").AutoFit

  48. Set classnumber = Nothing

  49. End Sub
½Æ»s¥N½X
¤£¹L©O,¥i§_­°§C¤U¸üªþ¥óªºªùÂe,¬Ý¨º»ò¦hµL·N¸qªº¦^©«
80 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-3-17 08:48 ½s¿è

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

§Ú«Ü³ßÅwpivot table¨º­Ó¸Ñ¨M¿ìªk,§Ö±¶±o«Ü(ÁÙ¦³¬O«ç¼Ë¿ý»s¼v¤ùªº?)
80 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

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

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

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

TOP

        ÀR«ä¦Û¦b : ¦³®É·í«äµL®É­W¡A¦n¤Ñ­n¿n«B¨Ó³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD