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

[µo°Ý] ¦Pµ§­q³æ­pºâ¥[Á`

[µo°Ý] ¦Pµ§­q³æ­pºâ¥[Á`

¦U¦ì¤j¤j
¨¯­W¤F
¤p§Ì¨C¶g³£­n²Î¾ã·~°È¤H­ûªºÁZ®ÄÂI¼Æ
¦ý¨Cµ§³£¬O¤â°ÊKEY¤J¸ê®Æ+¤â°Ê­pºâÁ`·~ÁZ
½Ð°Ý¦³¤°»ò¤½¦¡¥i¥HÅý§Ú¤£¥Î¦b¨C­Ó¤H¨C¤@µ§¥h¤â°Ê¥[Á`©O?

ªþ¹Ï»¡©ú:


¤@¶}©l§Ú§â¸ê®Æªí¨Ì·Ó·~°È¤H­ûªº½s¸¹°µ±Æ§Ç
±µµÛ­n¦bJÄæ(ÂI¼Æ¥[Á`)­pºâBÄæ(·~°È¤H­û)³o­Ó¤ëªºÁ`ÂI¼Æ(IÄæ)

­è±µÄ²¸ê®Æ³B²zªº¤u§@¡A§Ú³£¬O¥Î²´¤O¤@µ§¤@µ§ªº¥[Á`ÂI¼Æ
·Q¸ß°Ý¤j¤j­Ì¬O§_¦³§ó§Öªº¤è¦¡¯à°÷§ó¦³®Ä²vªº§âµ²ªG­pºâ¥X¨Ó©O?


ªþ¤WÀÉ®×:
½Æ¥» ª÷µP±ÀÂˤý´ú¸Õ.zip (64.59 KB)
¤£·|EXCALLªº²Â³J

¦^´_ 1# prince0413


   
³o¼Ë?
  1. Private Sub CommandButton1_Click()
  2. Application.ScreenUpdating = False
  3. [N2:O10000] = ""
  4. For Each aa In Range([B2], [B2].End(xlDown))
  5. For i = 1 To Application.CountA(Range("N:N"))
  6. If aa = Cells(i, 14) Then
  7. Cells(i, 15) = Cells(i, 15) + aa.Offset(, 7)
  8. ElseIf Cells(i + 1, 14) = "" Then
  9. Cells(i + 1, 14) = aa.Offset(, 0)
  10. Cells(i + 1, 15) = aa.Offset(, 7)
  11. End If
  12. Next
  13. Next
  14. End Sub
½Æ»s¥N½X

½Æ¥» ª÷µP±ÀÂˤý´ú¸Õ.rar (67.61 KB)

¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

=IF(COUNTIF(B$2:B2,B2)=1,SUMIF(B:B,B2,I:I),"")
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 3# hcm19522


  ÁÂÁ±z
¦ý¬O§Ú§â¤½¦¡¶K¤W©¹¤U©ÔÅý¨C­ÓÀx¦s®æ¹ïÀ³«á
µo²{¦³¨ÇÅã¥Üªº¼Æ¦r¤£¤@¼Ë­C
½Ð°Ý¤j¤j¬O¬°¤°»ò©O?
¤£·|EXCALLªº²Â³J

TOP

¥»©«³Ì«á¥Ñ prince0413 ©ó 2018-10-21 14:59 ½s¿è

¦^´_ 2# faye59


    «D±`·PÁ°ª¤â
³o´N¬O§Ú·Q­nªº
½Ð°Ý¥i¥H¸ÑÄÀ¤@¤U±z¼¶¼gªºµ{¦¡½X¶Ü?
¦]¬°¨C­Ó§«ô³£·|§ó·s¸ê®Æ¶i¥h
³q±`·|¦h¥X¦n´Xµ§·sªº¸ê®Æ¥X¨Ó(¦³¨Ç·~°È¤H­û³£¬O§Ö¤ë©³¤~·|¶}©l½Ä·~ÁZ)
·Q»¡¬O­þ¬qµ{¦¡½X¦b±±¨î­pºâ½d³ò»Pµ²ªGªºÅã¥Ü©O
ÁÙ¬O±z¼gµ¹§Úªº³o¬qµ{¦¡½X´N¤w¸g¨¬°÷À³¥I1000µ§¸ê®Æ¥H¤º¤F©O??
³Â·Ð±z¸Ñ´b¤F
«D±`·P®¦
¤£·|EXCALLªº²Â³J

TOP

¦^´_ 5# prince0413


   
§Ú¼g±o¦n¹³¨S¦³¤W­­­È(¦Ü¤Ö¯à¨ì65536µ§)
³o¬qµ{¦¡½X¨S¦³«Ü¬¡
©Ò¥H¦³¦p¤H¦WKey¿ù¡BÄæ¦ì¤£¹ï¡B¤Á´«­¶¤£¹ï...µ¥³£¥i¯à³y¦¨µ{§Ç¿ù»~
»Ý­n­×¥¿¦Aµ¹¦hÂI¸ê°T¤Î¥i¼W¥[
  1. Private Sub CommandButton1_Click()'¶}©l«ö¶sµ{¦¡
  2. Application.ScreenUpdating = False'Ãö³¬¨êÀW
  3. [N2:O10000] = ""'²M°£½d³ò¸ê®Æ
  4. For Each aa In Range([B2], [B2].End(xlDown))'°j°é¤H­û¦W³æ
  5. For i = 1 To Application.CountA(Range("N:N"))'°j°é²Î­p·~ÁZÁ`ÂI¼Æ
  6. If aa = Cells(i, 14) Then'·í¦W¤l­«ÂЮɰõ¦æ
  7. Cells(i, 15) = Cells(i, 15) + aa.Offset(, 7)'¦W¤lÄæ¦ì©¹¥k¤@®æ¶ñ¤JFor Each¨ú±oªºÂI¼Æ¡A¨Ã¥[Á`
  8. ElseIf Cells(i + 1, 14) = "" Then'·í¦W¤l¤£­«ÂЮɫá¤@µ§¸ê®Æ¬°ªÅ¥Õ®É°õ¦æ
  9. Cells(i + 1, 14) = aa.Offset(, 0)'·s«Ø¥ß¦W¤l
  10. Cells(i + 1, 15) = aa.Offset(, 7)'·s«Ø¥ß¸Ó­ûÂI¼Æ
  11. End If'µ²§ô¦pªG
  12. Next'¤U¤@­ÓFor i°j°é
  13. Next'¤U¤@­ÓFor Each°j°é
  14. End Sub'µ²§ôµ{¦¡
½Æ»s¥N½X
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¦^´_ 5# prince0413


    ÁÙ¦³µ{§Ç¤¤¥u¦³¼g·í«eSheetªº¸ê®Æ²Î­p¡A
¨S¦³°Ï¤À¤ë¥÷¡A
¦pªG­n¤ë¥÷»Ý­n¦A¥[¤JAÄæ¦ìªº§PŪ¡A
§_«h§A·í«eSheet´N©ñ¤J·í¤ë¥÷´N¦n¡C
(·í®É²©ö¼g¼g¦Ó¤w¡A¨S·Q¤Ó¦h)

¦pªG»Ý­n­×§ï¦A´£¥X°ÝÃD¡A
¤@¦¸À°§A°µ­×¥¿¡C
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2018-10-24 06:32 ½s¿è
  1. Option Explicit
  2. Private Sub CommandButton1_Click()
  3.     Dim Sh As Worksheet, Rng As Range
  4.     Application.ScreenUpdating = False
  5.     Set Sh = Sheets("10¤ë")                              '**«ü©w¤u§@ªí
  6.     Set Rng = Sh.Range("B1", Sh.Range("B1").End(xlDown)) '**BÄ檺½d³ò: B1¨ì³Ì«á¤@µ§ªº¸ê®Æ
  7.     With Sh.Range("N1")
  8.         .CurrentRegion.Clear                             '**²M°£ ©µ¦ù½d³ò
  9.         Rng.AdvancedFilter xlFilterCopy, , .Cells, xlYes '**¶i¶¥¿z¿ï ±N·~°È¤H­û½s¸¹ ½Æ»s¨ì N1
  10.                                                          '** xlYes °Ñ¼Æ -- ¤£¿ï­«Âиê®Æ
  11.         With .CurrentRegion.Columns(2)                   '**©µ¦ù½d³òªº²Ä¤GÄæ
  12.             .Cells = "=" & "SUMIF(" & Rng.Address(, , xlR1C1) & ",RC[-1]," & Rng.Offset(, 7).Address(, , xlR1C1) & ")"
  13.             '** hcm19522 ªº¤½¦¡ =IF(COUNTIF(B$2:B2,B2)=1,SUMIF(B:B,B2,I:I),"")
  14.             '**¨ú SUMIF(B:B,B2,I:I)  ±a¤J¤½¦¡
  15.             '**Rng.Address(, , xlR1C1)             ** B:B ªº R1C1 ¼Ë¦¡ªº°Ñ·Ó
  16.             '**Rng.Offset(, 7).Address(, , xlR1C1) ** I:I ªº R1C1 ¼Ë¦¡ªº°Ñ·Ó
  17.             '**RC[-1]                           ** B2  ªº R1C1 ¼Ë¦¡ªº°Ñ·Ó
  18.             .Value = .Value                       '** ±N¤½¦¡Âন­È
  19.         End With
  20.         .Range("B1") = "ÂI¼Æ¥[Á`"                 '**¸É¤W¼ÐÀY
  21.     End With
  22. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2018-10-24 17:42 ½s¿è

Sub TEST()
Dim Arr, xD, i&, T$, U&, N&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([B1], [I65536].End(xlUp))
Arr(1, 2) = "ÂI¼Æ¥[Á`"
For i = 2 To UBound(Arr)
    T = Arr(i, 1): U = xD(T): Arr(i, 2) = ""
    If U = 0 Then N = N + 1: U = N: xD(T) = N
    Arr(U + 1, 1) = Arr(i, 1)
    Arr(U + 1, 2) = Val(Arr(U + 1, 2)) + Val(Arr(i, 8))
Next i
[M:N].ClearContents
With [M1:N1].Resize(N + 1): .Value = Arr: .Borders.LineStyle = 1: End With
End Sub

TOP

¦^´_ 7# faye59


    «D±`·PÁ¤j¤j±zªº±Ð¾É
¤£·|EXCALLªº²Â³J

TOP

        ÀR«ä¦Û¦b : ¤£­n¤p¬Ý¦Û¤v¡A¦]¬°¤H¦³µL­­ªº¥i¯à¡C
ªð¦^¦Cªí ¤W¤@¥DÃD