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

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

¦^´_ 38# ­ã´£³¡ªL
½Ð­ã¤j«üÂI, µ{¦¡¹B§@¤@ª½¤£¥¿±`....
  1. Dim R&, xR As Range, xH As Range, C%
  2. Workbooks("²z³f³æII.xlsx").Sheets("BF²z³f").Activate
  3. R = Cells(Rows.Count, "K").End(xlUp).Row
  4. If R <= 2 Then Exit Sub
  5. For Each xR In Range("K2:K" & R)
  6.     If xR = "«~¦W" Then Set xH = xR(1, 8): C = 1: GoTo 101
  7.     If xR = "¦X­p" Then
  8.        If C = 0 Then GoTo 101
  9.        With Range(xH, xR(1, 8)) 'RÄæ¶ñ¤J¤½¦¡
  10.             .Formula = "=-SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$70=$L3)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$CD$3:$CV$3=$B3)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$CD$4:$CV$70))"
  11.             .Value = .Value
  12.             Range("R:R").Replace "0", "", 1  '*****(1,§¹¥þ²Å¦X)
  13.        End With
  14.        C = 0
  15.     End If
  16. 101: Next
  17. End Sub
½Æ»s¥N½X

TOP

¦^´_ 41# PJChen


Sub ¹º³æ_¤½¦¡()
Dim Rw&, xR As Range, xH As Range, C%, Fx$
Rw = Cells(Rows.Count, "K").End(xlUp).Row
If Rw <= 2 Then Exit Sub
[R2] = "=-SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$70=$L2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$CD$3:$CV$3=$B2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$CD$4:$CV$70))"
For Each xR In Range("K2:K" & Rw)
    If xR = "«~¦W" Then Set xH = xR(2, 8): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       With Range(xH, xR(0, 8)) 'RÄæ¶ñ¤J¤½¦¡
            .FormulaR1C1 = [R2].FormulaR1C1
            .Value = .Value
            .Replace 0, "", 1  '*****(1,§¹¥þ²Å¦X)
       End With
       C = 0
    End If
101: Next
[R2] = ""
End Sub


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

TOP

¦^´_ 42# ­ã´£³¡ªL
½Ð°Ý­ã¤j,
¥i§_¸Ñ»¡ ¥H¤U¬õ¦r

If Rw <= 2 Then Exit Sub
    If xR = "«~¦W" Then Set xH = xR(2, 8): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       With Range(xH, xR(0, 8))
.Replace 0, "", 1....³o¸Ì¤w¸g§â0¨ú¥N¬°ªÅ¥Õ,¬°¤°»òÁٻݭn[R2] = ""

TOP

¦^´_ 43# PJChen

If Rw <= 2 Then Exit Sub
__¸ê®Æ¦æ¼Æ¤p¤_µ¥2, ªí¥Üªí®æ¤¤¨S¦³¸ê®Æ

    If xR = "«~¦W" Then Set xH = xR(2, 8): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
__§ä¨ì"«~¦W", ¼ÐµùC=1, ©¹¤U§ä¨ì"¦X­p", ¤~ºâ°t¹ï¦¨¥\, ¥ç§Y"«~¦W"¨ì"¦X­p"¤§¶¡ªº½d³ò

With Range(xH, xR(0, 8))
.Replace 0, "", 1....³o¸Ì¤w¸g§â0¨ú¥N¬°ªÅ¥Õ,¬°¤°»òÁٻݭn[R2] = ""
__³o¸Ì¥u¨ú¥N"«~¦W(¤U¤@®æ)"¨ì"¦X­p(¤W¤@®æ)"¤§¶¡ªº½d³ò

TOP

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

­ã¤j¦n,
§Ú±Nµ{¦¡¥uµy§@­×§ï,®M¥Î¨ìFÄæ,¦ý¨C¦¸°õ¦æµ{¦¡,F2³£·|³Q²M°£,¸Õ¤F¦h¦¸,¨ÌµM§ä¤£¨ì­ì¦],¤£©ú¥Õ¬°¤°»ò¦P¤@µ{¦¡·|¦³¤£¦Pµ²ªG?
µ{¦¡¦p¤U¡G
  1. Sub ¼t¯Ê¸ü¤J()
  2. Dim Rw&, xR As Range, xH As Range, C%, Fx$
  3. Workbooks("²z³f³æII.xlsx").Sheets("BF²z³f").Activate
  4. Rw = Cells(Rows.Count, "K").End(xlUp).Row
  5. If Rw <= 2 Then Exit Sub
  6. [F2] = "=IF(SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$3:$CB$3=$B2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$64=$L2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$4:$CB$64))=0,"""",IF(SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$3:$CB$3=$B2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$64=$L2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$4:$CB$64))=$Q2,""¼t¯Ê"",""¯Ê""&SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$3:$CB$3=$B2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$64=$L2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$4:$CB$64))))"
  7. For Each xR In Range("K2:K" & Rw)
  8.     If xR = "«~¦W" Then Set xH = xR(2, -4): C = 1: GoTo 101
  9.     If xR = "¦X­p" Then
  10.        If C = 0 Then GoTo 101
  11.        With Range(xH, xR(0, -4)) 'FÄæ¶ñ¤J¤½¦¡
  12.             .FormulaR1C1 = [F2].FormulaR1C1
  13.             .Value = .Value
  14.             .Replace 0, "", 1  '*****(1,§¹¥þ²Å¦X)
  15.        End With
  16.        C = 0
  17.     End If
  18. 101: Next
  19. [F2] = ""
  20. End Sub
½Æ»s¥N½X
¼t¯Ê¸ü¤J.rar (331.79 KB)

TOP

¦^´_ 45# PJChen

³Ì«á¤@¦æ
[F2]="¹ê¥X®Ä´Á"

TOP

¦^´_ 45# PJChen

¤]¥i¥H³o¼Ë:
Sub ¼t¯Ê¸ü¤J()
Dim Rw&, xR As Range, xH As Range, C%, Fx$, LT$
Workbooks("²z³f³æII.xlsx").Sheets("BF²z³f").Activate
Rw = Cells(Rows.Count, "K").End(xlUp).Row
If Rw <= 2 Then Exit Sub
LT = [F2].Value
[F2] = "=IF(SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$3:$CB$3=$B2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$64=$L2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$4:$CB$64))=0,"""",IF(SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$3:$CB$3=$B2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$64=$L2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$4:$CB$64))=$Q2,""¼t¯Ê"",""¯Ê""&SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$3:$CB$3=$B2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$64=$L2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$4:$CB$64))))"
For Each xR In Range("K2:K" & Rw)
    If xR = "«~¦W" Then Set xH = xR(2, -4): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       With Range(xH, xR(0, -4)) 'FÄæ¶ñ¤J¤½¦¡
            .FormulaR1C1 = [F2].FormulaR1C1
            .Value = .Value
            .Replace 0, "", 1  '*****(1,§¹¥þ²Å¦X)
       End With
       C = 0
    End If
101: Next
[F2].Value = LT
End Sub


'===========================

TOP

¦^´_ 45# PJChen

©p¬O­nF2Äæ¦ì«ì´_Åã¥Ü"¹ê¥X®Ä´Á"¶Ü??
¦pªG¬O±Nµ{¦¡³Ì«á[F2] = ""§ï¬°[F2] = "¹ê¥X®Ä´Á"

TOP

µ{¦¡½X­nªá¨Ç®É¶¡¥h²z¸Ñ(¦³ªÅ´N¥h¬ã¨s¬ã¨s),
¤£µM³s³o¤p¤pªº°ÝÃD³£­n¦A°Ý¤@¦¸~~

TOP

¦^´_ 12# jcchiang

±z¦n,
§Ú§â³o­Óµ{¦¡¼gªkÀ³¥Î¦b¥t¤@¬d±bªí®æ¤¤,¨Ã¥B
·Q¥[¤J¤@­Ó·s¥\¯à,Åý ¦C5:6 & ¦C8 &¦C11:12 & ¦C14 ªº¼Æ­È,¯à°÷¥[¤J½c²~ ,¦ý­ì¥ýªº¼Æ­È¤£­nÅÜ°Ê
½Ð°Ý³oºØ»yªk¸Ó«ç»ò¼g?   ¥H­ì­È_¥[¤J½c²~.rar (10.12 KB)

EX1: C5ªº­ì­È¬°385
«hÅã¥Ü­È (=¤§«áªº¼Æ­È­n´«¦æ)
385=
19½c+5
²~ªº¦r¼Ë³£¤£Åã¥Ü,¦p²~¼Æ¬°0,«hÅV¥Ü­È¬°19½c+0

EX2: E5ªº­ì­È¬°19
«hÅã¥Ü­È  (=¤§«áªº¼Æ­È­n´«¦æ)
19=
0½c+19

µù¡G
ªí®æ¤ºªº¼Æ­È·|ÀHµÛ²£«~¤£¦P¦ÓÅÜ°Ê
­ìÀx¦s®æ ¦C5:6 & ¦C8 &¦C11:12 & ¦C14 ªº¼Æ­È,¸ü¤J«á³£¤w­È¤Æ
½c²~ªº­pºâ,¥H­ìÀx¦s®æ ¦C5:6 & ¦C8 &¦C11:12 & ¦C14 ªº¼Æ­È,¥h°£¥HC3ªº¤J¼Æ

TOP

        ÀR«ä¦Û¦b : ­×¦æ­nô½t­×¤ß¡AÂǨƽm¤ß¡AÀH³B¾i¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD