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

¤é¼Æ²Î­p

¤é¼Æ²Î­p

¦p¦ó¦b(I1:I24)²Î­p¤@¤j°ï¤é´Á(A1:F9)¤¤ªº¤é¼Æ ²Î­p¤é¼Æ.rar (1.95 KB)

¥»©«³Ì«á¥Ñ ANGELA ©ó 2010-11-15 15:03 ½s¿è

i1=SUMPRODUCT((YEAR($A$1:$F$9)=--LEFT(H1,4))*(MONTH($A$1:$F$9)=IF(MOD(ROW(),12),MOD(ROW(),12),12)))

TOP

=COUNTIF($A$1:$F$9,">"&DATE(LEFT(H1,4),MID(H1,6,LEN(H1)-6),))-COUNTIF($A$1:$F$9,">"&DATE(LEFT(H1,4),MID(H1,6,LEN(H1)-6)+1,))
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

²Î­p¤é¼Æ.rar (2.2 KB)

TOP

¶V¨Ó¶Vºë²¤F
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

¯uªº¬O¤Ó¯«¤F ·PÁÂ
solomon

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-3-24 10:25 ½s¿è

¦^´_ 1# mmggmm


    ÁÂÁ½׾Â,ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹¥DÃD½m²ß°}¦C»P¦r¨å,¾Ç²ßVBAªº¸Ñ¨M¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

°õ¦æ«e:


°õ¦æµ²ªG:



Option Explicit
Sub TEST()
Dim Brr, T, Y, Z, A, xR As Range
'¡ô«Å§iÅܼÆ:(Brr,T,Y,Z,A)¬O³q¥Î«¬ÅܼÆ,(xR,xU)¬OÀx¦s®æÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY³o³q¥Î«¬ÅܼƬO ¦r¨å
Set Z = CreateObject("System.Collections.ArrayList")
'¡ô¥OZ³o³q¥Î«¬ÅܼƬO ¨Ï¥Î¤j¤p·|µø»Ý­n°ÊºA¼W¥[ªº°}¦C
Set xR = [A1:F9]: Brr = xR
'¡ô¥OxR³oÀx¦s®æÅܼƬO [A1:F9]Àx¦s®æ,
'¥OBrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥HxRÅܼÆÀx¦s®æ­È±a¤J°}¦C¸Ì

For Each A In Brr
'¡ô³]³v¶µ°j°é!¥OA³o³q¥Î«¬ÅܼƬO Brr°}¦C¸Ìªº¤@­Ó°}¦C­È
   A = Format(A, "yyyy" & "¦~" & "mm")
   '¡ô¥OAÅܼƬO ¥|¦ì¼Æªº¼Æ¦r½X¦~ ³s±µ"¦~",¦A³s±µ2½X¤ë¥÷¦¨ªº·s¦r¦ê
   If A <> vbNullString And Not Z.contains(A) Then Z.Add (A)
   '¡ô¦pªGAÅܼƤ£¬O ªø«×¬°¹sªº¦r¦ê,¦Ó¥BAÅܼƤ£¦bZ°}¦C¸Ì?
   '¦pªG±ø¥ó¦¨¥ß´N§â AÅܼƯǤJZ°}¦C¸Ì

Next
Z.Sort
'¡ô¥OZ°}¦C°µ¶¶±Æ§Ç
For Each A In Z: Y(A) = 0: Next
'¡ô³]³v¶µ°j°é!±NZ°}¦C¸Ìªº­È·íkey,item¬O0,¯Ç¤JY¦r¨å¸Ì
For Each A In xR
'¡ô³]³v¶µ°j°é!¥OAÅܼƬOxRÅܼÆÀx¦s®æ¤¤ªº¤@®æ
   A = Format(A, "yyyy" & "¦~" & "mm")
   '¡ô¥OAÅܼƬO ¥|¦ì¼Æªº¼Æ¦r½X¦~ ³s±µ"¦~",¦A³s±µ2½X¤ë¥÷¦¨ªº·s¦r¦ê
   Y(A) = Y(A) + 1
   '¡ô¥OAÅܼƭÈÂà¼Æ­È·íkey,item¬O item¦Û¨­­È+1
Next
[L:M].ClearContents: [L1:M1] = [{"¤ë¥÷", "¤Ñ¼Æ"}]
'¡ô¥O[L:M]Àx¦s®æ²M°£¤º®e:¥O[L1:M1]³o¨â®æ¥H¡ô°}¦C¨â¦r¦ê±a¤J
[L2].Resize(Y.Count, 1) = Application.Transpose(Y.keys)
'¡ô¥O[L2]ÂX®i¦V¤UY¦r¨åkey¼Æ¶q¼ÆªºÀx¦s®æ,
'¥HY¦r¨åkeysÂà¸m«á±a¤JÀx¦s®æ

[M2].Resize(Y.Count, 1) = Application.Transpose(Y.items)
'¡ô¥O[M2]ÂX®i¦V¤UY¦r¨åkey¼Æ¶q¼ÆªºÀx¦s®æ,
'¥HY¦r¨åitemsÂà¸m«á±a¤JÀx¦s®æ

Set Y = Nothing: Set Z = Nothing: Set xR = Nothing
Erase Brr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

        ÀR«ä¦Û¦b : ¡i¦æµ½­n¤Î®É¡j¦æµ½­n¤Î®É¡A¥\¼w­n«ùÄò¡C¦p¿N¶}¤ô¤@¯ë¡A¥¼¿N¶}¤§«e¤d¸U¤£­n°±º¶¤õ­Ô¡A§_«h­«¨Ó´N¤Ó¶O¨Æ¤F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD