jcchiang

¦^´_ 1# PJChen

§ï³o¼Ë¸Õ¸Õ
xc = "=SUMPRODUCT((­¸¤ñ!$F$4:$F$70=¬d±b!$B$3)*(­¸¤ñ!$AP$3:$BH$3=¬d±b!C4)*(¤ñµá¦h!$AP$4:$BH$70))" '¬d±b_­qÁʼÆ
                        .Range("C5").Resize(1, xcol).Value = xc

jcchiang

¦^´_ 3# PJChen

xc = "=SUMPRODUCT((­¸¤ñ!$F$4:$F$70=¬d±b!$B$3)*(­¸¤ñ!$AP$3:$BH$3=¬d±b!C4)*(¤ñµá¦h!$AP$4:$BH$70))" '¬d±b_­qÁʼÆ
Range("C5").Resize(1, xcol).Value = xc
¦pªGxcol=5·|¥ÑC5¦ÜG5©ñ¤J¤½¦¡C4,D4,E4,F4,G4
Range("C5").Resize( xcol,1).Value = xc
¦pªGxcol=5·|¥ÑC5¦ÜC9©ñ¤J¤½¦¡C4,C5,C6,C7,C8

¤£ª¾©p¬O­n­þ¤@ºØ

jcchiang

¦^´_ 7# PJChen
2ºØ¼gªk´£¨Ñ°Ñ¦Ò
¦]¬°©pªº¤½¦¡¤Ó¦h¼Ë,¥ý¨Ì¦U¶µÄæ¦ì¶ñ¼g,¦p¦³¥i¥H¦X¨Ö½Ð¦Û¦æ½Õ¾ã
½c+²~­×§ï¬°½c+²~(1)/½c+²~(2)/½c+²~(3),¥H«K§PÂ_(Excel¤º®e½Ð­×¥¿)
½c+²~(1)--­qÁʼÆ
½c+²~(2)--¼t¯Ê
½c+²~(3)--¹ê¥X¼Æ
Âର¼Æ­È¤@¨Ö©óµ{¦¡«áÂà´«(³v¶µÂà´«µ{¦¡«O¯d,»Ý­n¦Û¦æ¶}±Ò)
¥\¤O¨S«Ü¦n,¬O§_ÁٯತÆ,´N½Ð¨ä¥L°ª¤âÀ°¦£¤F

Sub ex1()
'¬d±b_­qÁÊ¼Æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$AP$3:$BH$3=C4)*(­¸¤ñ!$AP$4:$BH$70))"
'¬d±b_­qÁʽc²~ = "=" & "IF(C5=0,"",INT(C5/$C$3)&IF(MOD(C5,$C$3)=0,""½c"",""½c+""&MOD(C5,$C$3)))"
'¬d±b_¼t¯Ê = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$BJ$3:$CB$3=C4)*(­¸¤ñ!$BJ$4:$CB$70))"
'¬d±b_¼t¯Ê½c²~ = "=" & "IF(C7=0,"",INT(C7/$C$3)&""½c"")&IF(MOD(C7,$C$3)=0,"",""+""&MOD(C7,$C$3))"'
'¬d±b_¹º³æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$CD$3:$CV$3=C4)*(­¸¤ñ!$CD$4:$CV$70))"
'¬d±b_¹ê¥X¼Æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$CX$3:$DP$3=C4)*(­¸¤ñ!$CX$4:$DP$70))"
'¬d±b_¹ê¥X½c²~ = "=" & "IF(C10=0,"",INT(C10/$C$3)&""½c"")&IF(MOD(C10,$C$3)=0,"",""+""&MOD(C10,$C$3))"
i = "C1:J1"
xcol = Range(i).Columns.Count  '¬Ý´Xµ§¸ê®Æ
For Each x In Range([b1], [b65535].End(3)) 'Range("B1:b20")
   If x = "­qÁʼÆ" Then
      ¬d±b_­qÁÊ¼Æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$AP$3:$BH$3= " & _
                    x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(­¸¤ñ!$AP$4:$BH$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = ¬d±b_­qÁʼÆ
   '  Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = Range(x.Address).Offset(0, 1).Resize(1, xcol).Value
   ElseIf x = "½c+²~(1)" Then
      ¬d±b_­qÁʽc²~ = "=" & "IF(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                      "=0,"""",INT(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                      "/$C$3)&IF(MOD(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                      ",$C$3)=0,""½c"",""½c+""&MOD(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ",$C$3)))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = ¬d±b_­qÁʽc²~
   '  Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = Range(x.Address).Offset(0, 1).Resize(1, xcol).Value
   ElseIf x = "¼t¯Ê" Then
      ¬d±b_¼t¯Ê = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$BJ$3:$CB$3=" & _
                  x.Offset(-3, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(­¸¤ñ!$BJ$4:$CB$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¼t¯Ê
  '   Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = Range(x.Address).Offset(0, 1).Resize(1, xcol).Value
   ElseIf x = "½c+²~(2)" Then
      ¬d±b_¼t¯Ê½c²~ = "=" & "IF(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                      "=0,"""",INT(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                      "/$C$3)&""½c"")&IF(MOD(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                      ",$C$3)=0,"""",""+""&MOD(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ",$C$3))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¼t¯Ê½c²~
   '  Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = Range(x.Address).Offset(0, 1).Resize(1, xcol).Value
   ElseIf x = "¹º³æ" Then
      ¬d±b_¹º³æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$CD$3:$CV$3=" & _
                   x.Offset(-5, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(­¸¤ñ!$CD$4:$CV$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¹º³æ
'    Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = Range(x.Address).Offset(0, 1).Resize(1, xcol).Value
   ElseIf x = "¹ê¥X¼Æ" Then
      ¬d±b_¹ê¥X¼Æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$CX$3:$DP$3=" & _
                    x.Offset(-6, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(­¸¤ñ!$CX$4:$DP$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¹ê¥X¼Æ
'    Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = Range(x.Address).Offset(0, 1).Resize(1, xcol).Value
   ElseIf x = "½c+²~(3)" Then
      ¬d±b_¹ê¥X½c²~ = "=" & "IF(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                      "=0,"""",INT(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                     "/$C$3)&""½c"")&IF(MOD(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                     ",$C$3)=0,"""",""+""&MOD(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                     ",$C$3))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¹ê¥X½c²~
  '   Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = Range(x.Address).Offset(0, 1).Resize(1, xcol).Value
   End If
Next
Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value = Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value '¤@¨ÖÂର¼Æ­È
End Sub

----------------------------------------------------------------------------------------------------------------------------------------------
Sub ex2()
i = "C1:J1"
xcol = Range(i).Columns.Count  '¬Ý´Xµ§¸ê®Æ
x = 1
Do While x < Range("B65535").End(3).Row + 1
   Select Case Cells(x, "B")
      Case Is = "­qÁʼÆ"
         ¬d±b_­qÁÊ¼Æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$AP$3:$BH$3= " & _
                      Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(­¸¤ñ!$AP$4:$BH$70))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = ¬d±b_­qÁʼÆ
    '    Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value
      Case Is = "½c+²~(1)"
         ¬d±b_­qÁʽc²~ = "=" & "IF(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                         "=0,"""",INT(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                         "/$C$3)&IF(MOD(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                         ",$C$3)=0,""½c"",""½c+""&MOD(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ",$C$3)))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = ¬d±b_­qÁʽc²~
    '    Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value
      Case Is = "¼t¯Ê"
         ¬d±b_¼t¯Ê = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$BJ$3:$CB$3=" & _
                     Cells(x, "B").Offset(-3, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(­¸¤ñ!$BJ$4:$CB$70))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¼t¯Ê
   '     Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value
      Case Is = "½c+²~(2)"
         ¬d±b_¼t¯Ê½c²~ = "=" & "IF(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                         "=0,"""",INT(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                         "/$C$3)&""½c"")&IF(MOD(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                         ",$C$3)=0,"""",""+""&MOD(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ",$C$3))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¼t¯Ê½c²~
    '    Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value
      Case Is = "¹º³æ"
         ¬d±b_¹º³æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$CD$3:$CV$3=" & _
                     Cells(x, "B").Offset(-5, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(­¸¤ñ!$CD$4:$CV$70))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¹º³æ
    '    Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value
      Case Is = "¹ê¥X¼Æ"
        ¬d±b_¹ê¥X¼Æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$CX$3:$DP$3=" & _
                     Cells(x, "B").Offset(-6, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(­¸¤ñ!$CX$4:$DP$70))"
        Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¹ê¥X¼Æ
   '    Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value
      Case Is = "½c+²~(3)"
        ¬d±b_¹ê¥X½c²~ = "=" & "IF(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                        "=0,"""",INT(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                        "/$C$3)&""½c"")&IF(MOD(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                        ",$C$3)=0,"""",""+""&MOD(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                        ",$C$3))"
        Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¹ê¥X½c²~
    '   Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value
   End Select
   x = x + 1
Loop
Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value = Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value '¤@¨ÖÂର¼Æ­È

End Sub

jcchiang

¦^´_ 7# PJChen
¿@ÁY¤@¤U,2ºØ¼gªk´£¨Ñ°Ñ¦Ò
¬d±b_­qÁʽc²~ = "=" & "IF(C5=0,"",INT(C5/$C$3)&IF(MOD(C5,$C$3)=0,""½c"",""½c+""&MOD(C5,$C$3)))"
¬d±b_¼t¯Ê½c²~ = "=" & "IF(C7=0,"",INT(C7/$C$3)&""½c"")&IF(MOD(C7,$C$3)=0,"",""+""&MOD(C7,$C$3))"'
¬d±b_¹ê¥X½c²~ = "=" & "IF(C10=0,"",INT(C10/$C$3)&""½c"")&IF(MOD(C10,$C$3)=0,"",""+""&MOD(C10,$C$3))"
³o¤T­Ó¤½¦¡À³¸Ó¤@¼Ë,¥u¬O­pºâ¦ì¸m¤£¦P,²Î¤@¨Ï¥Î"¬d±b_­qÁʽc²~"ªº¤½¦¡,­pºâ¦ì¸m·|§ó°Ê
©Ò¥HEXCEL¤ººû«ù¥Î"½c+²~"§PÂ_

Sub ex1()
i = "C1:J1"
xcol = Range(i).Columns.Count  '¬Ý´Xµ§¸ê®Æ
For Each x In Range([b2], [b65535].End(3)) 'Range("B1:b20")
r = x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False)
   If x = "­qÁʼÆ" Then
      ¬d±b_­qÁÊ¼Æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$AP$3:$BH$3= " & r & ")*(­¸¤ñ!$AP$4:$BH$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = ¬d±b_­qÁʼÆ
   ElseIf x = "½c+²~" Then
      ¬d±b_­qÁʽc²~ = "=" & "IF(" & r & "=0,"""",INT(" & r & "/$C$3)&IF(MOD(" & r & ",$C$3)=0,""½c"",""½c+""&MOD(" & r & ",$C$3)))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = ¬d±b_­qÁʽc²~
   ElseIf x = "¼t¯Ê" Then
      r1 = Range(r).Offset(-2, 0).Address(RowAbsolute:=False, columnAbsolute:=False)
      ¬d±b_¼t¯Ê = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$BJ$3:$CB$3=" & r1 & ")*(­¸¤ñ!$BJ$4:$CB$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¼t¯Ê
   ElseIf x = "¹º³æ" Then
      r1 = Range(r).Offset(-4, 0).Address(RowAbsolute:=False, columnAbsolute:=False)
      ¬d±b_¹º³æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$CD$3:$CV$3=" & r1 & ")*(­¸¤ñ!$CD$4:$CV$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¹º³æ
   ElseIf x = "¹ê¥X¼Æ" Then
      r1 = Range(r).Offset(-5, 0).Address(RowAbsolute:=False, columnAbsolute:=False)
      ¬d±b_¹ê¥X¼Æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$CX$3:$DP$3=" & r1 & ")*(­¸¤ñ!$CX$4:$DP$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¹ê¥X¼Æ
   End If
Next
Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value = Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value 'Âର¼Æ­È
End Sub
-----------------------------------------------------------------------------------------------
Sub ex2()
i = "C1:J1"
xcol = Range(i).Columns.Count  '¬Ý´Xµ§¸ê®Æ
x = 2
Do While x < Range("B65535").End(3).Row + 1
   r = Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False)
   Select Case Cells(x, "B")
      Case Is = "­qÁʼÆ"
         ¬d±b_­qÁÊ¼Æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$AP$3:$BH$3= " & r & ")*(­¸¤ñ!$AP$4:$BH$70))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = ¬d±b_­qÁʼÆ
      Case Is = "½c+²~"
         ¬d±b_­qÁʽc²~ = "=" & "IF(" & r & "=0,"""",INT(" & r & "/$C$3)&IF(MOD(" & r & ",$C$3)=0,""½c"",""½c+""&MOD(" & r & ",$C$3)))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = ¬d±b_­qÁʽc²~
      Case Is = "¼t¯Ê"
         r1 = Range(r).Offset(-2, 0).Address(RowAbsolute:=False, columnAbsolute:=False)
         ¬d±b_¼t¯Ê = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$BJ$3:$CB$3=" & r1 & ")*(­¸¤ñ!$BJ$4:$CB$70))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¼t¯Ê
      Case Is = "¹º³æ"
         r1 = Range(r).Offset(-4, 0).Address(RowAbsolute:=False, columnAbsolute:=False)
         ¬d±b_¹º³æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$CD$3:$CV$3=" & r1 & ")*(­¸¤ñ!$CD$4:$CV$70))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¹º³æ
      Case Is = "¹ê¥X¼Æ"
         r1 = Range(r).Offset(-5, 0).Address(RowAbsolute:=False, columnAbsolute:=False)
        ¬d±b_¹ê¥X¼Æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$CX$3:$DP$3=" & r1 & ")*(­¸¤ñ!$CX$4:$DP$70))"
        Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = ¬d±b_¹ê¥X¼Æ
   End Select
   x = x + 1
Loop
Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value = Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value 'Âର¼Æ­È
End Sub

jcchiang

¦^´_ 10# PJChen
Àx¦s®æ¤½¦¡¤¤E3="",­n¼g¦¨E3=""""

1) µLªk°õ¦æ
xRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("E3:E" & xRow).Formula = "=IF(E3="""",""µL¥æ³f"",T3&S3&R3)"
2)µLªk°õ¦æ
Range("Z3:Z" & xRow).Formula = "=IF(AB3="""","""",AB3-Y3)"

3) µLªk°õ¦æ
Range("F2:F" & xRow).Formula = "=IF(MONTH(A3)<>MONTH(A4),SUM(INDIRECT(ADDRESS(MATCH(DATE(YEAR(A3),MONTH(A3),1),A:A,0),COLUMN(D3),3)):D3),"""")"