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

[µo°Ý] §ó·s°Ó«~¸ê®Æ

[µo°Ý] §ó·s°Ó«~¸ê®Æ

¤j¤j¦n,
½Ð°Ý§Ú­×§ï¤F­ã¤j¤§«e¼gªºµ{¦¡,¥Î¨Ó§ó·s°Ó«~¸ê®Æ,µ²ªG¡G
a) ¥i¥H§ó·s¸ê®Æ,¦ýµLªk±N¦h¾l¦C¼Æ§R°£(¦³°ÝÃD)
b) ¥i¥H§ó·s¸ê®Æ,¤£¨¬ªº¦C¼Æ¥i¦Û°Ê¸É¨¬(¨S°ÝÃD)

½ÐÀ°¦£¬Ý¤U,¬O§_µ{¦¡¦³­þ¸Ì¨S§ï¹ï¡H
¥t¥~A®w¦sªí,BF4=SUM($G4:$H4)-SUMPRODUCT(($AA$3:$BC$3="¦X­p")*($B$4:$B$80=$B4)*($AA$4:$BC$80))
³o­Ó¨ç¼Æªº¼gªk,·í§ó·s¸ê®Æ¦C¼Æ¤ñ­ì¨Óªº¦h®É,»Ý­n¤â°Ê­×§ï,½Ð°Ý¬O§_¦³¨ä¥L¼gªk?
°Ó«~§ó·s.rar (93.55 KB)

xrow = xSh.Cells(Rows.Count, 1).End(xlUp).Row - 3

xrow = xSh.Cells(Rows.Count, 2).End(xlUp).Row - 3

ÀË´ú¤w¨Ï¥Î¦æ¼Æ, §ï¥ÎB¦C~~

TOP

¤½¦¡­«·s§ï¬°¹ê»Ú¦æ¼Æ:
If xrow > R Then xSh.Range("A" & R + 4 & ":A" & xrow + 4).EntireRow.Delete '§R°£¦h¾l¦æ
©³¤U¥[³o¤@¦æ:
xSh.[BF4].Resize(R).Formula = _
       Replace("=SUM($G4:$H4)-SUMPRODUCT(($AA$3:$BC$3=""¦X­p"")*($B$4:$B$¦æ¼Æ=$B4)*($AA$4:$BC$¦C¼Æ))", "¦æ¼Æ", R + 3)

TOP

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

­ã¤j¦n,
Sheets("1" & "")§ï¬°¥H¤U,²{¦b¨S°õ¦æ¨S°ÝÃD¤F,
xrow = xSh.Cells(Rows.Count, 2).End(xlUp).Row - 3

¦ýSheets("¾ã¤ë²Î­p" & "")§ï¦¨¥H¤U,¹B§@¤´¤£¥¿±`,´Nºâ§ïCount, 3,¤]¬OµLªk§R°£¦h¾l¦æ¼Æ
drow = xSh.Cells(Rows.Count, 2).End(xlUp).Row - 3

½Ð°Ý¥H¤Uªº¼Æ¦r,¤£¬O³£ÄÝ"¦C¼Æ"?¬°¦ó$B$4:$B$¦æ¼Æ,¬O¥Î"¦æ¼Æ"?
Replace("=SUM($G4:$H4)-SUMPRODUCT(($AA$3:$BC$3=""¦X­p"")*($B$4:$B$¦æ¼Æ=$B4)*($AA$4:$BC$¦C¼Æ))", "¦æ¼Æ", R + 3)

TOP

¦^´_ 4# PJChen


"¦æ¼Æ" ¬O¼È®É©Ê¤å¦r, ¬O¥Î¨Ó¨ú¥N¦¨¹ê»Ú¦C¼Æ¥Îªº,
¤]¥i¥Î§Oªº¤å¦r, ¦p"¥x¥_"

TOP

¦^´_ 3# ­ã´£³¡ªL
­ã¤j,
µ{¦¡¤@¦A´ú¸Õ,³£¨S¦³¦A¦¨¥\¹L...
Sheets("1" & "")¥d¦b³o,»¡¬O©w¸q¿ù»~
    xSh.[BF4].Resize(R).Formula = _
       Replace("=SUM($G4:$H4)-SUMPRODUCT(($AA$3:$BC$3=""¦X­p"")*($B$4:$B$¦æ¼Æ=$B4)*($AA$4:$BC$¦C¼Æ))", "¦æ¼Æ", R + 3)
  
Sheets("¾ã¤ë²Î­p" & "")§ï¦¨¥H¤U,¹B§@¤´¤£¥¿±`,´Nºâ§ïCount, 3,¤]¬OµLªk§R°£¦h¾l¦æ¼Æ
drow = xSh.Cells(Rows.Count, 2).End(xlUp).Row - 3

TOP

µ{¦¡¬O©ñ¦b"°Ó«~"ÀÉ®×, ¤S­n°õ¦æ¶}±Ò¦Û¤vªº°Ê§@,
FN = "°Ó«~.xls"
On Error Resume Next: Set xB = Workbooks(FN): On Error GoTo 0
If xB Is Nothing Then Set xB = Workbooks.Open(PH & FN)

¨ä¥¦¬Ý¨Ó³£¶Ã¤F~~
±qµ{¦¡¥h§PÂ_»Ý¨D, ¤Ó²Ö!!!

TOP

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

¤£¦n·N«ä®@....­ã¤j,
PO¤W¨Ó³o¸Ìªº¬O´ú¸ÕÀÉ,§Ú§âµ{¦¡©ñ¦b °Ó«~.xlsm,©Ò¥H°ÆÀɦW¬O¨S¿ùªº,
FN = "°Ó«~.xlsm"
¦Óµ{¦¡¯uªº¦³ÂI©_©Ç,¦³®É¥i§R°£¦h¾l¦æ¼Æ,¦³®É¤S¤£ÆF,§Ú¤]´ú¸Õ¨ì¦³ÂIY¹F¤F¡I
¥Ø«e¦ô¥B·í¦¨¥¦¥i¥H¦Û°Ê§R°£¦h¾l¦æ¼Æ,¤£¹L±z«á¨Ó¼gªº,´N¯uªº¥d¦í¤F,»¡¬O©w¸q¿ù»~¡I
½Ð°Ý"¦C¼Æ"¨S¦³¥ô¦ó«Å§iªº±¡§Î¤U,·|¤£·|µLªk¦Û°Ê§ì¨ì¦C¼Æ¡H
Sheets("1" & "")
    xSh.[BF4].Resize(R).Formula = _
       Replace("=SUM($G4:$H4)-SUMPRODUCT(($AA$3:$BC$3=""¦X­p"")*($B$4:$B$¦æ¼Æ=$B4)*($AA$4:$BC$¦C¼Æ))", "¦æ¼Æ", R + 3)

TOP

¤w­×¦n:
°Ó«~§ó·s-01.rar (102.38 KB)

TOP

¦^´_ 9# ­ã´£³¡ªL
ÁÂÁ­ã¤j,°õ¦æ¨S°ÝÃD¤F¡I

TOP

        ÀR«ä¦Û¦b : ª¾ÃÑ­n¥Î¤ßÅé·|¡A¤~¯àÅܦ¨¦Û¤vªº´¼¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD