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

¦P¤ë¥÷¸ê®Æ¥[Á`

¦P¤ë¥÷¸ê®Æ¥[Á`

Book1.zip (8.03 KB)

¦U¦ì¤j¤j¡A½Ð°Ý§Ú·Q¥[Á`²Å¦X¬Y¤@­Ó¦WºÙªº¼Æ¶q¡A
¤S§Æ±æ¥L¯à¨Ì·Ó¤ë¥÷°µ¥[Á`¡A¦³¬Æ»ò¦nªº¨ç¼Æ¥i¥H¥Î¶Ü?
·PÁ±z!

¥»©«³Ì«á¥Ñ Hsieh ©ó 2014-5-6 08:47 ½s¿è

F3 =SUMPRODUCT((MONTH($A$2:$A$86)=ROW(A1))*($B$2:$B$86=F$2),$C$2:$C$86) ©¹¥k©¹¤U©Ô
N3= SUM($F3:$M3) ©¹¤U©Ô

TOP

¦^´_ 2# samwang


  ¹ï¤£°_: ¶K¤W®É¦n¹³¦³¨Ç¦rÅܦ¨¯S®í¦r¡A­«·s¶K¤W
F3: SUMPRODUCT((MONTH($A$2:$A$86)=ROW(A1))*($B$2:$B$86=F$2),$C$2:$C$86)

TOP

¦^´_ 3# samwang


§Ú¹ï±zªº´º¥õ¦³¦p·Ê·Ê¦¿¤ô³sºø¤£¨M
¯u¬O¤Ó·PÁ±z¤F

TOP

f3

=SUMPRODUCT(($B$2:$B$86=F$2)*(MONTH($A$2:$A$86)&"¤ë"=$E3)*$C$2:$C$86)

TOP

¦n·Q¤U¸ü¨Ó¬Ý¤@¤U, ·PÁÂ!

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:


°õ¦æµ²ªG:


Option Explicit
Sub TEST()
Dim Brr, Crr, Z, i&, R&, C%, N&, X%, T$, Dx As Date
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range([C2], [A65536].End(xlUp))
ReDim Crr(1000, 1000)
For i = 1 To UBound(Brr)
   Dx = Format(Brr(i, 1), "YY/MM/01"): R = Z(Dx)
   T = Trim(Brr(i, 2)): C = Z(T)
   If R = 0 Then N = N + 1: R = N: Z(Dx) = R: Crr(R, 0) = Dx
   If C = 0 Then X = X + 1: C = X: Z(T) = C: Crr(0, C) = T
   Crr(R, C) = Crr(R, C) + Brr(i, 3)
Next
T = [E1]: [E:Z].Clear: [E1] = T: [E1].HorizontalAlignment = xlCenter
With [E1].Resize(1, X + 2): .Merge: .Borders.LineStyle = 1: End With
If N * X = 0 Then Exit Sub
With [E2].Resize(N + 1, X + 2)
   .Value = Crr
   .Offset(0, 1).Sort KEY1:=.Item(1), Order1:=1, Header:=2, Orientation:=2
   .Offset(1, 0).Sort KEY1:=.Item(1), Order1:=1, Header:=2, Orientation:=1
   .Columns(1).NumberFormatLocal = "m""¤ë"""
   .Borders.LineStyle = 1
   .Columns(X + 2) = "=SUM(F2:" & Cells(2, X + 5).Address(0, 0) & ")"
   .Cells(1, 1) = "ºØÃþ": .Cells(1, X + 2) = "Á`­p"
End With
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¦³¤ß´N¦³ºÖ¡A¦³Ä@´N¦³¤O¡A¦Û³yºÖ¥Ð¡A¦Û±oºÖ½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD