[µo°Ý] ¥[¤J¦Û¦æ§ì¨ú·í¤Ñ¤é´Á

cowww

=IF(IFERROR('\\shl-group.com\dept\MFMG\B.¦U²Õ¸ê®Æ (Team inform)\E.¥ÍºÞ²Õ (PPC group)\D.¥Í²£¬ÛÃö\2.¨C¤é¼Ò¨ã²§°Ê\¼Ò¨ã²§°Ê B1+M5+M6+M7\2023\[MFMG3_33_47_53_MFMG5_6_7_126_163_173_174_¶q²£¼Ò¨ã²§°Ê.xlsm]±Æµ{'!$B1,"")=0,"",IFERROR('\\shl-group.com\dept\MFMG\B.¦U²Õ¸ê®Æ (Team inform)\E.¥ÍºÞ²Õ (PPC group)\D.¥Í²£¬ÛÃö\2.¨C¤é¼Ò¨ã²§°Ê\¼Ò¨ã²§°Ê B1+M5+M6+M7\2023\[MFMG3_33_47_53_MFMG5_6_7_126_163_173_174_¶q²£¼Ò¨ã²§°Ê.xlsm]±Æµ{'!$B1,""))
¤W­±¨º¬q¥i¥H§ì¨ì"¼Ò¨ã²§°Ê B1+M5+M6+M7\2023\[MFMG3_33_47_53_MFMG5_6_7_126_163_173_174_¶q²£¼Ò¨ã²§°Ê"¸Ì­±"B1"ªº¸ê®Æ

¤U¸ü (2.77 KB)

2023-5-10 10:24

¦ý¬O²{¦b«á­±¨Óªº¤H§âÀɮצWºÙ+¤é´Á

¥H¤U¬O§Úªº¼gªk
=IF(IFERROR('\\shl-group.com\dept\MFMG\B.¦U²Õ¸ê®Æ (Team inform)\E.¥ÍºÞ²Õ (PPC group)\D.¥Í²£¬ÛÃö\2.¨C¤é¼Ò¨ã²§°Ê\¼Ò¨ã²§°Ê B1+M5+M6+M7\2023\[MFMG3_33_47_53_MFMG5_6_7_126_163_173_174_¶q²£¼Ò¨ã²§°Ê&TEXT(TODAY(),"yyyymmdd"&.xlsm]±Æµ{'!$B1,"")=0,"",IFERROR('\\shl-group.com\dept\MFMG\B.¦U²Õ¸ê®Æ (Team inform)\E.¥ÍºÞ²Õ (PPC group)\D.¥Í²£¬ÛÃö\2.¨C¤é¼Ò¨ã²§°Ê\¼Ò¨ã²§°Ê B1+M5+M6+M7\2023\[MFMG3_33_47_53_MFMG5_6_7_126_163_173_174_¶q²£¼Ò¨ã²§°Ê&TEXT(TODAY(),"yyyymmdd"&.xlsm]±Æµ{'!$B1,""))

½Ð°Ý³o­Ó¼gªkªº¿ù»~¦b­þ??
¦]¬°³o­Ó¼gªk§ì¤£¨ì¸ê®Æ

­ã´£³¡ªL

¤½¦¡¤Þ¥Î¸ô®|¤£¥i¥H¥Î¤å¦r¦ê, ¤@¯ë¥i¥ÎINDIRECT, ¦ý¥~³¡¤Þ¥Î¤£¦n¥Î//
°²³]¤½¦¡¦b D Äæ//VBA--¨ú¥Nªk
SUB ¤½¦¡¨ú¥N()
  RANGE("D:D").REPLACE "_¶q²£¼Ò¨ã²§°Ê*.xlsm", "_¶q²£¼Ò¨ã²§°Ê" & FORMAT(DATE,"yyyymmdd") & ".xlsm", LOOKAT:=XLPART
END SUB
=========================
©ÎªÌ//¦b H1 ¿é¤J«ü©w¤é´Á:¦p 2023/5/10
SUB ¤½¦¡¨ú¥N2()
  RANGE("D:D").REPLACE "_¶q²£¼Ò¨ã²§°Ê*.xlsm", "_¶q²£¼Ò¨ã²§°Ê" & FORMAT([H1],"yyyymmdd") & ".xlsm", LOOKAT:=XLPART
END SUB


¦Û¦æ¥[«ö¶s°õ¦æ

­ã´£³¡ªL

¦pªG¥~ÀÉ B1 ¬O¼Æ­È, ¤½¦¡¥i§ï¤U//
=IFERROR(1/(1/'\\shl-group.com\dept\MFMG\B.¦U²Õ¸ê®Æ (Team inform)\E.¥ÍºÞ²Õ (PPC group)\D.¥Í²£¬ÛÃö\2.¨C¤é¼Ò¨ã²§°Ê\¼Ò¨ã²§°Ê B1+M5+M6+M7\2023\[MFMG3_33_47_53_MFMG5_6_7_126_163_173_174_¶q²£¼Ò¨ã²§°Ê.xlsm]±Æµ{'!$B1&""),"")

cowww

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

«D±`·PÁ­㴣³¡ªL¤j¤jªº¸Ñ´b

­n§ì¤é´ÁªºÀɮפ½¦¡©ñ¦bPÄæ~TÄæ

¤U¸ü (15.9 KB)

2023-5-10 11:27

³o­Ó¼´¨ú¸ê®ÆªºÀɮצ³¼gVBA
¥D­n¬OM567ªºÀɮצ³¤é´Áªº°ÝÃD
¨ä¥Lªº¨S°ÝÃD
Sub full_calc()

'©w¸q®É¶¡
Dim s%
Dim T0
T0 = Timer

'§ó·s²§°Êªí
'A
  Workbooks("¤Å§R«æ¥ó¤½¦¡").Worksheets("²§°Êªí").Range("$B1:$G1").Copy '½Æ»s
  Workbooks("¤Å§R«æ¥ó¤½¦¡").Worksheets("²§°Êªí").Range("$B6:$G300").PasteSpecial Paste:=xlPasteFormulas '¶K¤W¤½¦¡
  ' xlPasteALL¥þ³¡
  ' xlPasteFormulas¤½¦¡
  ' xlPasteValues­È
  ' xlPasteFormats®æ¦¡
  Workbooks("¤Å§R«æ¥ó¤½¦¡").Worksheets("²§°Êªí").Range("$B6:$G300") = Worksheets("²§°Êªí").Range("$B6:$G300").Value '§ï¦¨­È
'C
  Workbooks("¤Å§R«æ¥ó¤½¦¡").Worksheets("²§°Êªí").Range("$I1:$N1").Copy
  Workbooks("¤Å§R«æ¥ó¤½¦¡").Worksheets("²§°Êªí").Range("$I6:$N330").PasteSpecial Paste:=xlPasteFormulas
  Workbooks("¤Å§R«æ¥ó¤½¦¡").Worksheets("²§°Êªí").Range("$I6:$N330") = Worksheets("²§°Êªí").Range("$I6:$N330").Value
'M567
  Workbooks("¤Å§R«æ¥ó¤½¦¡").Worksheets("²§°Êªí").Range("$P1:$U1").Copy
  Workbooks("¤Å§R«æ¥ó¤½¦¡").Worksheets("²§°Êªí").Range("$P6:$U300").PasteSpecial Paste:=xlPasteFormulas
  Workbooks("¤Å§R«æ¥ó¤½¦¡").Worksheets("²§°Êªí").Range("$P6:$U300") = Worksheets("²§°Êªí").Range("$P6:$U300").Value
'LF
  Workbooks("¤Å§R«æ¥ó¤½¦¡").Worksheets("²§°Êªí").Range("$W1:AB1").Copy
  Workbooks("¤Å§R«æ¥ó¤½¦¡").Worksheets("²§°Êªí").Range("$W3:$AB300").PasteSpecial Paste:=xlPasteFormulas
  Workbooks("¤Å§R«æ¥ó¤½¦¡").Worksheets("²§°Êªí").Range("$W3:$AB300") = Worksheets("²§°Êªí").Range("$W3:$AB300").Value
'­pºâ°õ¦æ®É¶¡
  s = Int(Timer - T0)
  
'§¹¦¨«áªº°T®§¤è¶ô
  MsgBox "°õ¦æ®É¶¡: " & s \ 60 & " ¤À " & s Mod 60 & " ¬í"
  
End Sub