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

[µo°Ý] ±a¤J¹ïÀ³ªº¤é´Á

[µo°Ý] ±a¤J¹ïÀ³ªº¤é´Á

¦U¦ì¾Çªø©j¦n
§Ú·Q¦bsheet(WIP)¥[¤J¥æ´ÁÄæ¦ì(¹Ï¤@)
±a¤Jªº³W«h¬O
¥ý¬Ý¹w©w§¹¦¨¤éªº¤é´Á
¶VªºÀu¥ý¥X³f
¦A¤ñ¹ïsheet(¥æ´Á)AÄ檺®Æ¸¹(¹Ï¤G)
¬Û¦Pªº®Æ¸¹¦b¥¼º¡¨¬¥æ´Á¼Æ¶q(CÄæ)«e
³£±a¤JBÄ檺¥æ´Á
º¡¨¬«á¦A±a¤J¤U¤@­Ó¤é´Á
¨Ò¦p
(¹Ï¤@)sheet(WIP)¦C2
®Æ¸¹0201-ACR-041¡A¼Æ¶q96¡C¤ñ¹ïsheet(¥æ´Á)«á
sheet(WIP) D2À³±a¤J"2020/5/8"
¦bÁÙ¨Sº¡¨¬sheet(¥æ´Á)C7 ªº250¼Æ¶q«e
sheet(WIP)¸Ì¦P¬°"0201-ACR-041"®Æ¸¹ªº³£±a¤J"2020/5/8"
¥H¤W»¡©ú
½Ð¦U¦ìÀ°¦£¤F¡A·P®¦!!!
¹Ï¤@.png
¹Ï¤G.png

´ú¸Õ.rar (24.27 KB)

{=MIN(IF((¥æ´Á!$A$2:$A$99=A2)*(SUMIF(A$2:A2,A2,C$2:C2)<=SUMIF(OFFSET(¥æ´Á!A$2,,,ROW($1:$98)),¥æ´Á!A$2:A$99,¥æ´Á!C$2:C$99)),¥æ´Á!B$2:B$99))
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 2# hcm19522


·PÁÂhcm19522¤j
¥i¥H±a¤J¤F
¦ý¨S¦³¥X³f¥æ´Áªº®Æ¸¹³£·|±a¤J"1900/1/0"
½Ð°Ý«ç»ò¸Ñ¨M??
§Ú·Qªº¸Ñ¨M¤è®×¬O
¦pªGºâ¥X¬O"1900/1/0"ªºÄæ¦ì
´N±a¤Jsheet(WIP)BÄ檺¤é´Á
³o¼Ë­n«ç»ò¼g¨ç¼Æ??
¦A³Â·Ð¤F!!
ÁÂÁÂ

TOP

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

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

¥æ´Áªí:
20230601_2.jpg
2023-6-1 13:20


WIPªí°õ¦æ«e:
20230601_1.jpg
2023-6-1 13:20


WIPªí°õ¦æµ²ªG:
20230601_3.jpg
2023-6-1 13:21



Option Explicit
Sub TEST() '¡ô
Dim Arr, Brr, Crr, V$, Y, R&, i&, S&, T1$, T3$
Set Y = CreateObject("Scripting.Dictionary")
Brr = Range([¥æ´Á!C2], [¥æ´Á!A65536].End(xlUp))
For i = 1 To UBound(Brr): Y(Brr(i, 1)) = Y(Brr(i, 1)) & "/" & i: Next
Crr = Range([WIP!C2], [WIP!A65536].End(xlUp))
ReDim Arr(1 To UBound(Crr), 1 To 1)
For i = 1 To UBound(Crr)
   T1 = Crr(i, 1): T3 = Crr(i, 3)
   If Y(T1) = "" Then Arr(i, 1) = "µL­q³æ": GoTo i01
i00: S = Y(T1 & "|¾l¶q")
   If S <= 0 Then
      V = Mid(Y(T1), InStr(Y(T1), "/") + 1) & "/": R = Val(V)
      If R = 0 Then Arr(i, 1) = "µLÄò³æ": GoTo i01
      Y(T1) = V: Y(T1 & "|¾l¶q") = Brr(R, 3) + S
      If Y(T1 & "|¾l¶q") <= 0 Then GoTo i00
      Y(T1 & "|¥æ´Á") = Brr(R, 2)
   End If
   Arr(i, 1) = Y(T1 & "|¥æ´Á")
   Y(T1 & "|¾l¶q") = Y(T1 & "|¾l¶q") - Crr(i, 3)
i01: Next
[WIP!D2].Resize(UBound(Arr)) = Arr
Set Y = Nothing: Erase Arr, Brr, Crr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¥@¤W¦³¨â¥ó¨Æ¤£¯àµ¥¡G¤@¡B§µ¶¶ ¤G¡B¦æµ½¡C
ªð¦^¦Cªí ¤W¤@¥DÃD