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

[µo°Ý] Åý¤½¦¡ªº­È,ª½±µ±a¤JÀx¦s®æ

[µo°Ý] Åý¤½¦¡ªº­È,ª½±µ±a¤JÀx¦s®æ

½Ð°Ý¤j¤j,

§Ú­nÅýJQ4­pºâ¨Ã±o¨ìAH4-BR4ªºµ²ªG,¦ý¥u­n­È(¤£­n®æ¦¡)´N¦n,§Ú¬O¥Î¥H¤U»yªk,
½Ð°Ý¦³¨ä¥L»yªk,¥i¥Hª½±µÅýAH4-BR4ªº­È,ª½±µ±a¤J¨ìJQ4¤@ª½¨ì¦p¦PFÄ檺¦C¼Æ,
¦Ó¤£­n¹³¥H¤U½wºCªº¨BÆJ,¦]¬°Àɮפ¤³£¬O¤½¦¡,¦­¤W¤~run¤F¤@¤U,¾ã­Ó³£·í¾÷¤F.
  1.         With Sh
  2.         Sh.Activate
  3.             Range("JQ2") = Range("H1").Value  '«e¤@¤é®w¦sÁ`¼Æ,¶K¨ìAH
  4.             Range("JQ4") = "=" & "AH4-BR4"
  5.             Range("JQ4").Copy
  6.             xRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row  '¬ÝFÄæ¦ì´Xµ§¸ê®Æ
  7.             Set Rng = Range("JQ4:JQ" & xRow)
  8.             Rng.PasteSpecial Paste:=xlPasteFormulas  '¿ï¾Ü©Ê¶K¤W¤½¦¡
  9.             Application.CutCopyMode = False '¨Ï¨Ó·½ªºcopyªºµê½u°{°Ê°±¤î
  10.             Application.DisplayAlerts = False '¦bµ{§Ç°õ¦æ¹Lµ{¤¤¨Ï¥X²{ªºÄµ§i®Ø¤£Åã¥Ü
  11.             Application.Calculation = xlAutomatic    '¦Û°Ê­pºâ
  12.             Rng = Rng.Value
  13.         End With
½Æ»s¥N½X

¥»©«³Ì«á¥Ñ PJChen ©ó 2021-4-18 18:43 ½s¿è

¦^´_ 25# ­ã´£³¡ªL
­ã¤j¦n,
³o­ÓÀÉ©µÄò¤§«e¼gªºµ{¦¡,§Q¥Î"«~¦W"&"¦X­p"¤§¶¡ªº¦C¼Æ°µ¨ÇÅܤÆ,½ÐÀ°¦£¬Ý¤U...·PÁ¡I ²z³f³æ_Åý¦C¼Æ¬Û¦P.rar (236.66 KB)

·QÅý·s²z³fÀÉ,»P±M®×²z³f¤ºªº¦P¼ËÃöÁp¦r,¦C¼Æ¬Û¦P,
ÃöÁp¦r»PÀɦWªºÃö«Y,¦C¦bMacro_2.xlsmªºACÄæ,¥¼¨Óµ{¦¡¤]©ñ³o¸Ì,
ACÄæÃöÁp¦r¦b"±M®×²z³f" ªºBÄæ,¥i¥H§ä¨ì§¹¥þ¬Û¦Pªº¦r
§Ú·Q­n§Q¥ÎÃöÁp¦r,§ä¨ì¹ïÀ³ªº·s²z³f³æ
EX:
1) Macro_2.xlsmªºACÄæ,²Ä¤@­ÓÃöÁp¦r:·x·x3
2) "±M®×²z³f" BÄæ§ä¨ì·x·x3,"«~¦W"&"¦X­p"¤§¶¡¦C¼Æ=33¦C(¥]§tªÅ¥Õ¦C)
3) ¥´¶}¹ïÀ³ÀɦW"²Î­Ü_·x·x.xlsx",¤ñ¹ï"«~¦W"&"¦X­p"¤§¶¡¦C¼Æ,¬O§_=33
4) ·s²z³fÀɦC¼Æ¤Ó¦h«h§R°£,¤Ó¤Ö«h·s¼W,¦sÀÉ,Ãö³¬
5) §¹¦¨«á´«²Ä2­ÓÃöÁp¦r....¤@ª½¨ì8­ÓÀÉ¥þ³¡§¹¦¨,
6) ¼W¥[or§R°£¦C¼Æ,¤@©w­n¾ã¦C&«O«ù¤u§@ªí­ì®æ¦¡
7) ±M®×²z³f¤ºªº²z³f¦C¼Æ,±`·|¦]»Ý¨D¦Ó¼W¥[´î¤Ö,¦Ó·s²z³f³æ¸ê®Æ§¨¤ºªº8­ÓÀÉ,¥þ³¡³£­n¤@¨Ö§ó·s,
8) µ{¦¡­n¯à¥HMacro_2.xlsmªºAC:ADÄ檺ÃöÁp¨Ó¶}ÀÉ&¤ñ¹ï¦C¼Æ

TOP

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

ÁÂÁ­ã¤j
¥i¥H¥¿±`¹B§@¤F

TOP

¦^´_ 79# PJChen

§ï¤U:
If xR = "«~¦W" Then Set xH = xR(2, 7): c = Range("A" & xR.Row + 1).Value: GoTo 101

TOP

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

­ã¤j,
¤S¨Ó³Â·Ð±z¤F....
µ{¦¡ÁöµM¨Ó¦^¬d§ä,À³¸Ó»¡´ú¸Õ¤F´X¤Ñ,¦ý¨S¦³¤@¦¸¥i¥H¹B§@¦¨¥\¡I´N¬O§ä¤£¨ì°ÝÃDÂI...
§Ú©ñ¤W2­Ó­q³æ,§Ú·Q³o2­Ó¥i¥H¹B§@ªº¸Ü,¨ä¥LªºÀ³¸Ó´N¤£·|¦³°ÝÃD¤F,
¨ç¼Æ¤è­±½T©wµL»~,³o¬O¨C¤Ñ¥²°µªº¥\½Ò,¹B¦æOK.   ²z³f³æ_­qÁʼÆ.rar (373.36 KB)

TOP

¦^´_ 77# PJChen

¤£¬ã¨s¤½¦¡¬O¹ï©Î¿ù, ¤]¨SªkÅçÃÒ, ·Ó§Û!!! ­Y¦³»~¦Û¦æ¥h­×§ï  

Sub ²z³f­qÁʶq()
Dim Rw&, xR As Range, xD, xH As Range, c$, Fx$
Rw = Cells(Rows.Count, "K").End(xlUp).Row
If Rw <= 2 Then Exit Sub
Set xD = CreateObject("Scripting.Dictionary")
xD("¥þ³£") = "=SUMIFS(ºô³æ.¥þ³£!$I:$I,ºô³æ.¥þ³£!$C:$C,BF²z³f!$D2," & _
           "ºô³æ.¥þ³£!$K:$K,BF²z³f!$C2)+IF(BF²z³f!$R$283=BF²z³f!$B$283,BF²z³f!$R2,0)"
xD("²Î²Î") = "=SUMIFS(ºô³æ.²Î²Î!$R:$R,ºô³æ.²Î²Î!$M:$M,BF²z³f!$D2,ºô³æ.²Î²Î!$AC:$AC,BF²z³f!$C2," & _
          "ºô³æ.²Î²Î!$AE:$AE,BF²z³f!$B$1)+IF(BF²z³f!$R$1=BF²z³f!$B$1,BF²z³f!$R2,0)"
xD("¼wQQK") = "=SUMIF(ºô³æ.¼wQQK!$E:$E,BF²z³f!$D2,ºô³æ.¼wQQK!$G:$G)+IF(BF²z³f!$R$388=BF²z³f!$B$388,BF²z³f!$R2,0)"
xD("MªÀ") = "=SUMPRODUCT((ºô³æ.MªÀ!$R$2:$R$300=BF²z³f!$D2)*(ºô³æ.MªÀ!$AP$2:$AP$300))+" & _
           "IF(BF²z³f!$R$561=BF²z³f!$B$561,BF²z³f!$R2,0)"
xD("±o¨Ó") = "=SUMIFS(ºô³æ.±o¨Ó!$L:$L,ºô³æ.±o¨Ó!$H:$H,BF²z³f!$D2,ºô³æ.±o¨Ó!$O:$O,BF²z³f!$C2)" & _
           "+IF(BF²z³f!$R$420=BF²z³f!$B$420,BF²z³f!$R2,0)"
xD("W±d") = "=SUMPRODUCT((ºô³æ.W±d!$C$6:$C$298=BF²z³f!$D2)*(ºô³æ.W±d!$D$6:$D$298))+" & _
           "IF(BF²z³f!$R$508=BF²z³f!$B$508,BF²z³f!$R2,0)"

For Each xR In Range("K2:K" & Rw)
    If xR = "«~¦W" Then Set xH = xR(2, 7): c = Range("A" & xR.Row): GoTo 101
    If xR = "¦X­p" Then
       Fx = xD(c)
       If c = "" Or Fx = "" Then GoTo 101
       [Q2].Formula = Fx
       With Range(xH, xR(0, 7)) 'QÄæ¶ñ¤J¤½¦¡
            .FormulaR1C1 = [Q2].FormulaR1C1
            .Value = .Value
            .Replace 0, "", 1  '*****(1,§¹¥þ²Å¦X)
       End With
       c = ""
    End If
101: Next
[Q2] = "­qÁʼÆ"
End Sub


======================================

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-7-7 19:36 ½s¿è

¦^´_ 42# ­ã´£³¡ªL
­ã¤j¦n,
§Ú¥Î42¼Óªºµ{¦¡,­×§ï«á¥Î¨Ó§ì¨ú«È¤á¤U³æªº"­qÁʼÆ",­×§ï«áªºµ{¦¡¦p¤U¡G
  1. Sub ²z³f­qÁʶq()
  2. Dim Rw&, xR As Range, xH As Range, c%, Fx$
  3. Rw = Cells(Rows.Count, "K").End(xlUp).Row
  4. If Rw <= 2 Then Exit Sub
  5. '´ú¸Õ¨ä¤¤¤@­Ó«È¤áªº¤U³æ¼Æ...¥þ³£
  6. [q2] = "=SUMIFS(ºô³æ.¥þ³£!$I:$I,ºô³æ.¥þ³£!$C:$C,BF²z³f!$D2," & _
  7. "ºô³æ.¥þ³£!$K:$K,BF²z³f!$C2)+IF(BF²z³f!$R$283=BF²z³f!$B$283,BF²z³f!$R2,0)"
  8. For Each xR In Range("K2:K" & Rw)
  9.     If xR = "«~¦W" Then Set xH = xR(2, 7): c = 1: GoTo 101
  10.     If xR = "¦X­p" Then
  11.        If c = 0 Then GoTo 101
  12.        With Range(xH, xR(0, 7)) 'QÄæ¶ñ¤J¤½¦¡
  13.             .FormulaR1C1 = [q2].FormulaR1C1
  14.             .Value = .Value
  15.             .Replace 0, "", 1  '*****(1,§¹¥þ²Å¦X)
  16.        End With
  17.        c = 0
  18.     End If
  19. 101: Next
  20. [q2] = "­qÁʼÆ"
  21. End Sub
½Æ»s¥N½X
°ÝÃD¦p¤U¡G   ²z³f³æ_­qÁʼÆ.rar (81.02 KB)
a) QÄ檺­qÁʼÆ,¬O«È¤á¤U³æªº¼Æ¶q,¦¬¨ì­q³æªº®É¶¡³£¤£¬O¦P®Éªº
b) «È¤á­q³æ¦WºÙ°Ï¤À¦bAÄæ,«È¤áªº­q³æ®æ¦¡³£¤£¬Û¦P,©Ò¥H6­Ó«È¤á¦³6­Ó¤½¦¡§ì¨ú¸ê®Æ
c) §Ú­×§ï¤F¤§«eªº¤@­Óµ{¦¡,¥Î¨Ó§ì¨úQÄ檺­qÁʼÆ,¦ýµ{¦¡¤£¬O±M¬°³o­Ó¦Ó³]­p,©Ò¥H¤U¤@­Ó«È¤áªº­q³æ,·|§â«e¤@­q³æ¼Æµ¹Âл\
d) RÄ檺¥[´î¼Æ¶q,¬O¦]À³«È¤á¦³­q³æ"¥[¶q" or "´î¶q"ªº»Ý¨D¦Ó³],¦³®É«È¤H·|¦b¤U³æ´X¤Ñ«e´N§iª¾,¦ý¤£·|­×§ï·í¤é­q³æ,©Ò¥H»Ý­n¥Î¨ìRÄ檺"¥[´î¼Æ¶q",
¥i¥H¹w¥ýkey¤J,¦ý®É¶¡¥¼¨ì®É«h¤£¤©­p¤J¡I
e) ½Ð°Ý­n¦p¦ó­×§ïµ{¦¡,¥i¥H±NAÄæ¦WºÙ(«È¤á)¦C¤Jµ{¦¡¤¤,Åý¤£¦P®É¶¡¤U³æªº6­Ó«È¤á,¦U¦Ûªº­q³æ¼Æ¤£·|³QÂл\?
''----------AÄæ¦WºÙ1) ¥þ³£
[q2] = "=SUMIFS(ºô³æ.¥þ³£!$I:$I,ºô³æ.¥þ³£!$C:$C,BF²z³f!$D2," & _
"ºô³æ.¥þ³£!$K:$K,BF²z³f!$C2)+IF(BF²z³f!$R$283=BF²z³f!$B$283,BF²z³f!$R2,0)"
''----------AÄæ¦WºÙ2) ²Î²Î
[q2] = "=SUMIFS(ºô³æ.²Î²Î!$R:$R,ºô³æ.²Î²Î!$M:$M,BF²z³f!$D2,ºô³æ.²Î²Î!$AC:$AC,BF²z³f!$C2," & _
"ºô³æ.²Î²Î!$AE:$AE,BF²z³f!$B$1)+IF(BF²z³f!$R$1=BF²z³f!$B$1,BF²z³f!$R2,0)"
''----------AÄæ¦WºÙ3) ¼wQQK
'    [q2] = "=SUMIF(ºô³æ.¼wQQK!$E:$E,BF²z³f!$D2,ºô³æ.¼wQQK!$G:$G)+IF(BF²z³f!$R$388=BF²z³f!$B$388,BF²z³f!$R2,0)"
'''----------AÄæ¦WºÙ4) MªÀ
'    [q2] = "=SUMPRODUCT((ºô³æ.MªÀ!$R$2:$R$300=BF²z³f!$D2)*(ºô³æ.MªÀ!$AP$2:$AP$300))+IF(BF²z³f!$R$561=BF²z³f!$B$561,BF²z³f!$R2,0)"
'''----------AÄæ¦WºÙ5) ±o¨Ó
'    [q2] = "=SUMIFS(ºô³æ.±o¨Ó!$L:$L,ºô³æ.±o¨Ó!$H:$H,BF²z³f!$D2,ºô³æ.±o¨Ó!$O:$O,BF²z³f!$C2)+IF(BF²z³f!$R$420=BF²z³f!$B$420,BF²z³f!$R2,0)"
'''----------AÄæ¦WºÙ6) W±d
'    [q2] = "=SUMPRODUCT((ºô³æ.W±d!$C$6:$C$298=BF²z³f!$D2)*(ºô³æ.W±d!$D$6:$D$298))+IF(BF²z³f!$R$508=BF²z³f!$B$508,BF²z³f!$R2,0)"

TOP

¦^´_ 58# jcchiang
§Ú¤w¸g¸Ñ¨M°ÝÃDÅo...·PÁÂ

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-6-25 22:43 ½s¿è

¦^´_ 58# jcchiang

±z¦n,
¥ý»¡ÁnÁÂÁÂ,§A¼gªºµ{¦¡,Á`¬OÅý§Ú±o¨ì«Ü¤jªº±Òµo
³o¬qµ{¦¡,§Úµy§@­×§ï,§Æ±æ¥¦¥i¥H¦Û°Ê¹ïÀ³,¼W¥[¤è«K©Ê,
¦ý¦³¨Ç¤G­Ó°ÝÃD§ÚµLªk¸Ñ¨M...µ{¦¡¤w¼g¤Jmacro_D    §ó·s²z³f³æ.rar (130.47 KB)
macro_Dªº"²z³f³æ"¤u§@ªí,W1 & W2ªº¹ïÀ³­È
x1 = xS.[w1] '¹ïÀ³ ¬¡°Ê½d³òa
x2 = xS.[w2] '¹ïÀ³ÀɦW
For k = 1 To 7 (­ì7­ÓÀÉ,¥ý¥Î"¤U­Ó¤ë²z³f³æ"¸ê®Æ§¨ªº2­ÓÀÉ´ú¸Õ)
xS.[V1] = k
·íxS.[w1]=1="·x·x1"
xS.[w2]="·x·x",«h¥´¶}¤½¥Î²z³f§t¦³"·x·x"¦r¼ËªºÀÉ®×,
±N²z³f³æIIªºBÄæ="·x·x1"ªºÀx¦s®æF:Pªº¸ê®Æ,
copy¨ì"1"¤u§@ªíªºB3¶K¤W­È,
¨Ï¥Î®Éµo²{µ{¦¡copy¸ê®Æ¨Ã¤£¬O«Ü§Ö³t
For Each a In Range("B:B")
If a = x1 Then d(a.Address) = d(a.Address)

©Ò¥H§Ú¬O¥Î²z³f³æIIªºF:P°Ï°ìÂл\B:L,·Q¨Ïcopy¤@¦¸§¹¦¨,
µM«á¦A±ND:F,I:Jªº¤½¦¡¥N¤J«á¤U©Ô
²{¦b¹J¨ì°ÝÃD¦p¤U¡G
1) ÁöµM¼g¤F
For k = 1 To 7
    xS.[V1] = k
¦ý¥¦¥u·|¥´¶}²Ä¤@­ÓÀÉ,§Ú­n¦p¦óÅý¥¦§â"¤U­Ó¤ë²z³f³æ"¸ê®Æ§¨,¥þ³¡Àɳ£¨Ì§Ç¥´¶},
µM«á¨Ìk = 1 To 7,©Ò¹ïÀ³ªº­È¶K¨ì¸Ó¶Kªº¦a¤è?

2) D:F,I:Jªº¤½¦¡,key¤J«á,§Æ±æ¥X²{¤½¦¡,¦Ó¤£¬O­È,¨Ò¦p¡GªL¤fªºÀÉ®×
I3­È¬O50,¦ý§Ú§Æ±æµ{¦¡
[i3] = "=" & "Int(" & [m3] & "/" & [L3] & ")" '½c¼Æ
[i3] ©Ò±o¨ìªºµª®×¬O¤½¦¡=INT(M3/L3),
D:F,I:J...4Äæ¤]³£§Æ±æ§e²{¤½¦¡¦Ó«D­È

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-6-25 22:46 ½s¿è

¦^´_ 73# ­ã´£³¡ªL
·PÁ­ã¤j,
§Ú·|¦A§ì®É¶¡´ú¸Õ

§Ú­Ì¤½¥q³W¼Ò«Ü¤p¡B«Üáàªù,³¡ªù¤º©Ò¦³Àɮ׳£¥Ñ§Ú³]­p,
¬Ý°_¨ÓÅv­­¦n¹³«Ü¤j,¦ý«Ü²Ö(³o¬O­Ó¨S¤H·Q±µªº¤u§@),
¤£¹L§Ú¦³¤Ñ°¨¦æªÅªº·Q¹³¤O,³s°µ¹Ú³£·|¹Ú¨£excel¡B
¹Ú¨£¤½¦¡ªº¹B§@,¦ý¯à§_¹ê²{,¤S¬O¥t¤@¦^¨Æ¡I
¤j³¡¥÷ªº°ÝÃD§Ú³£¯à¦Û¦æ¸Ñ¨M,¦U¦ì¬Ý¨ì§Úµo°Ý,
¨ä¹ê¥u¬O¦B¤s¤@¨¤^.^
¤£¹L»¡¨ì©³¬O¯à¤O¦³­­,¯à«÷´ê¦h¤Öºâ¦h¤ÖÅo...

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD