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

[µo°Ý] ¦C¥X¥ý¶i¥ý¥X°T®§

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-4-4 05:19 ½s¿è

¦^´_ 4# n7822123


¬Ý¤F·Ç¤j¼gªºµ{¦¡¡A§Úµo²{¡A§Ú¼gªºÁÙ¯u¬O¤¾ªø.......

§Ú¤]­n¾Ç¾Ç¥u¥Î¦r¨å¬ö¿ý¦C¸¹¡A¦Ó¤£¬O§â¸ê®Æ¤@ªÑ¸£ªº¶ë¶i¦r¨åXD

¤£¹L·Ç¤j¥Î¤F¤£¤Ö¤p§Þ¥©¡A­nŪÀ´¥i¯à¤ñ¸ûÃø¤@ÂIÂI

(¦p¦^Ū²Ä¤@¦C¸ê®Æ¡A¥Î val ¨ç¼Æ§â¯Â¦r¦êÅܦ¨¼Æ¦r0)

µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 11# n7822123


¦]¹ïexcel¤½¦¡¦³¨Ç¸gÅç, ©Ò¥Hµ{¦¡½X¤¤ºU¤J¤F¤½¦¡§Þ¥©,
ÁöµM¬Ý°_¨Óµu, ¦ý®Ä²v¤Î©öŪ©Ê¤£¤@©w´N¤ñ¸û¦n!
¨ä¹êµ{¦¡½X³Ì¦n¬O®Ä²v­ÝÅU©öŪ©Ê, §Q¤_¥H«áªº­×§ï¡C

¦]±NÅܼƩw¸q¬°¾ã¼Æ, ©Ò¥H¥Îval(???) ¨ÓÁ׿ù,
¤]¥i¤Ö¤@¦¸IFªº§PÂ_~~ IF ISNUMERIC(ARR(I,3)) THEN

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2024-1-31 17:10 ½s¿è

(¿é¤J½s¸¹10769-1) googleºô§}:https://draft.blogger.com/blog/posts/9094075214774179359
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 8# ­ã´£³¡ªL


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

Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, »Ý¨D&, Z, i&, j%, C%, T$, ®w¦s&, D As Date, W&, V&, R&
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range([®w¦s!C1], [®w¦s!A65536].End(3))
For i = 2 To UBound(Brr)
   T = Trim(Brr(i, 1)): If T = "" Then GoTo i01
   If Not IsObject(Z(T)) Then Set Z(T) = CreateObject("Scripting.Dictionary")
   Z(T)(i) = 0: Z(T & "Tot") = Z(T & "Tot") + Val(Brr(i, 3))
i01: Next
Crr = Range([­ì©l!C1], [­ì©l!A65536].End(3))
ReDim Arr(2 To UBound(Crr), 1 To 100)
For i = 2 To UBound(Crr)
   T = Trim(Crr(i, 2)): »Ý¨D = Val(Crr(i, 3)): C = 0: If Z(T & "Tot") = 0 Then GoTo i02
   For j = Z(T & "No") To Z(T).Count - 1
      W = W + 1: R = Z(Trim(Crr(i, 2))).keys()(j)
      ®w¦s = Val(Brr(R, 3))
      D = CDate(Brr(Z(Trim(Crr(i, 2))).keys()(j), 2))
      V = IIf(®w¦s < »Ý¨D, ®w¦s, »Ý¨D)
      Arr(i, C + 1) = D: Arr(i, C + 2) = V: C = C + 2
      Z(T & "Tot") = Z(T & "Tot") - V
      »Ý¨D = »Ý¨D - V: ®w¦s = ®w¦s - V
      If ®w¦s = 0 Then Z(T & "No") = Z(T & "No") + 1 Else Brr(R, 3) = ®w¦s
      If »Ý¨D = 0 Then Exit For
   Next
i02: If Z(T & "Tot") = 0 And »Ý¨D > 0 Then Arr(i, C + 1) = "¨S¦³¸ê®Æ": Arr(i, C + 2) = "¼Æ¶q¤£¨¬"
Next
[­ì©l!D2].Resize(UBound(Arr) - 1, UBound(Arr, 2)) = Arr
MsgBox "°j°é¼Æ:" & W
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤p¨Æ¤£°µ¡B¤j¨ÆÃø¦¨¡C
ªð¦^¦Cªí ¤W¤@¥DÃD