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

[µo°Ý] VBA¤¤ªº¨ç¼Æ¦¡ ¨ú¥N

[µo°Ý] VBA¤¤ªº¨ç¼Æ¦¡ ¨ú¥N

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-3-1 18:00 ½s¿è

Dear,
¦b¤u§@ªí¤¤¥i¥H¦Û©w¸q¨ç¼Æ¨Ï¤½¦¡Åܱo²¼ä,¦ý¦]¬°¥Î±o¤Ó¦h¤F,·í»Ý­n­×§ï®É,¤]¬O«Ü¥O¤H²V²c...
·Q§ï¦b¼gµ{¦¡®É,§â¤½¦¡¥Î¤@­Ó¥N½Xxcªí¥Ü,³Ì«áÅܦ¨­È,³o¼Ë´N¤£¥Î¤@ª½§â¤¾ªøªº¨ç¼Æ¦¡¼g¥X¨Ó,¦ý¬O"¦æ¤£³q",
½Ð°Ý¬O§_¦³¨ä¥Lªº¼gªkor»Ý­n¤°»òª«¥ó,¸Õ¤F¤S¸ÕÁ`¬O¤£¦æ..
                i = "C1:J1"
                    xcol = Range(i).Columns.Count
                    Set 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
                        .Range("C5").Resize(1, xcol).Value = .Range("C5").Resize(1, xcol).Value

¦^´_ 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

TOP

¦^´_ 2# jcchiang

§ï³o¼Ë ¦bC5Àx¦s®æ ¸ê®Æ¥¿½T,¦ý¨ä¥LÀx¦s®æ±o¥Xªº­È·|¿ù»~,­ì¦]¬°¦b©ó¬õ¦r³¡¥÷,C4¤£¬O$C$4 À³¯àÀHµÛÀx¦s®æ¤£¦P¦Ó¦ì²¾,¥i¬O±N¤½¦¡¥Îxc¨ú¥N®É,«oÅܱo¤£·|²¾°Ê,¥u¬O¤@­Ó¦ºªºªF¦è,½Ð°Ý¯à¦³¨ä¥L¼gªk,¥i¥HÅý¤½¦¡¬¡¥Î¶Ü?
SUMPRODUCT((­¸¤ñ!$F$4:$F$70=¬d±b!$B$3)*(­¸¤ñ!$AP$3:$BH$3=¬d±b!C4)*(¤ñµá¦h!$AP$4:$BH$70))

TOP

¦^´_ 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­þ¤@ºØ

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-3-2 17:41 ½s¿è

¦^´_ 4# jcchiang
§Ú¤GºØ³£­n¥Î,¥H¥Ø«e­qÁʼƤ½¦¡¨Ó»¡,±qC5:J5 / C14:J4
VBA¤¤ªº¨ç¼Æ¦¡ ¨ú¥N.rar (103.59 KB) ¬O¿ùªº,¥¦¤£Åý§Ú§R°£,½Ð¤Å¤U¸ü
³Ì·s®w¦s-¬d±b.rar (83.12 KB)

VBA¤¤ªº¨ç¼Æ¦¡ ¨ú¥N.rar (103.59 KB)

TOP

¦^´_ 4# jcchiang
§Úª¾¹D¤F,­n³o¼Ë§ï...·PÁ±z
.Range("C5").Resize(xcol, xcol).Value = ¬d±b_­qÁʼÆ
.Range("C5").Resize(xcol, xcol).Value = .Range("C5").Resize(xcol, xcol).Value

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-3-2 19:00 ½s¿è

¦^´_ 6# PJChen
³o¼Ë°µ,¸ê®ÆÁÙ¬O¿ù吔...°£¤FC5¤@¾ã¦Cªº¸ê®Æ¥¿½T,¨ä¾l³s°õ¦æ³£¦³°ÝÃD,¥d¦í¤F...
  1. ¬d±b_­qÁÊ¼Æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$AP$3:$BH$3=C4)*(­¸¤ñ!$AP$4:$BH$70))"
  2. ¬d±b_­qÁʽc²~ = "=" & "IF(C5=0,"",INT(C5/$C$3)&IF(MOD(C5,$C$3)=0,""½c"",""½c+""&MOD(C5,$C$3)))"
  3. ¬d±b_¼t¯Ê = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$BJ$3:$CB$3=C4)*(­¸¤ñ!$BJ$4:$CB$70))"
  4. ¬d±b_¼t¯Ê½c²~ = "=" & "IF(C7=0,"",INT(C7/$C$3)&""½c"")&IF(MOD(C7,$C$3)=0,"",""+""&MOD(C7,$C$3))"
  5. ¬d±b_¹º³æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$CD$3:$CV$3=C4)*(­¸¤ñ!$CD$4:$CV$70))"
  6. ¬d±b_¹ê¥X¼Æ = "=" & "SUMPRODUCT((­¸¤ñ!$F$4:$F$70=$B$3)*(­¸¤ñ!$CX$3:$DP$3=C4)*(­¸¤ñ!$CX$4:$DP$70))"
  7. ¬d±b_¹ê¥X½c²~ = "=" & "IF(C10=0,"",INT(C10/$C$3)&""½c"")&IF(MOD(C10,$C$3)=0,"",""+""&MOD(C10,$C$3))"

  8.                     i = "C1:J1"
  9.                     xcol = Range(i).Columns.Count  '¬Ý´Xµ§¸ê®Æ
  10.                         .Range("C5").Resize(1, xcol).Value = ¬d±b_­qÁʼÆ
  11.                         .Range("C5").Resize(1, xcol).Value = .Range("C5").Resize(1, xcol).Value
  12.                             .Range("C14").Resize(xcol, xcol).Value = ¬d±b_­qÁʼÆ
  13.                             .Range("C14").Resize(xcol, xcol).Value = .Range("C14").Resize(xcol, xcol).Value
  14.                            
  15.                                 .Range("C6").Resize(1, xcol).Value = ¬d±b_­qÁʽc²~
  16.                                 .Range("C6").Resize(1, xcol).Value = .Range("C6").Resize(1, xcol).Value
  17.                                     .Range("C15").Resize(xcol, xcol).Value = ¬d±b_­qÁʽc²~
  18.                                     .Range("C15").Resize(xcol, xcol).Value = .Range("C15").Resize(xcol, xcol).Value
  19.                                     
  20.                                         .Range("C7").Resize(1, xcol).Value = ¬d±b_¼t¯Ê
  21.                                         .Range("C7").Resize(1, xcol).Value = .Range("C7").Resize(1, xcol).Value
  22.                                             .Range("C16").Resize(xcol, xcol).Value = ¬d±b_¼t¯Ê
  23.                                             .Range("C16").Resize(xcol, xcol).Value = .Range("C16").Resize(xcol, xcol).Value
  24.                                             
  25.                                                 .Range("C8").Resize(1, xcol).Value = ¬d±b_¼t¯Ê½c²~
  26.                                                 .Range("C8").Resize(1, xcol).Value = .Range("C8").Resize(1, xcol).Value
  27.                                             .Range("C17").Resize(xcol, xcol).Value = ¬d±b_¼t¯Ê½c²~
  28.                                             .Range("C17").Resize(xcol, xcol).Value = .Range("C17").Resize(xcol, xcol).Value
  29.                                         .Range("C9").Resize(xcol, xcol).Value = ¬d±b_¹º³æ
  30.                                         .Range("C9").Resize(xcol, xcol).Value = .Range("C9").Resize(xcol, xcol).Value
  31.                                     .Range("C10").Resize(xcol, xcol).Value = ¬d±b_¹ê¥X¼Æ
  32.                                     .Range("C10").Resize(xcol, xcol).Value = .Range("C10").Resize(xcol, xcol).Value
  33.                                 .Range("C19").Resize(xcol, xcol).Value = ¬d±b_¹ê¥X¼Æ
  34.                                 .Range("C19").Resize(xcol, xcol).Value = .Range("C19").Resize(xcol, xcol).Value
  35.                             .Range("C11").Resize(xcol, xcol).Value = ¬d±b_¹ê¥X½c²~
  36.                             .Range("C11").Resize(xcol, xcol).Value = .Range("C11").Resize(xcol, xcol).Value
  37.                         .Range("C20").Resize(xcol, xcol).Value = ¬d±b_¹ê¥X½c²~
  38.                         .Range("C20").Resize(xcol, xcol).Value = .Range("C20").Resize(xcol, xcol).Value
½Æ»s¥N½X

TOP

¦^´_ 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

TOP

¦^´_ 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

TOP

¦^´_ 9# jcchiang

¥»·Q§ïµ½ªí®æ,Åý¥¦Â²¼ä¤SRUN§Ö¨Ç,¦n¹³¸£³UÅܱo§ó·Q¤£²M·¡¤F...
¥ý½Ð±Ð¥t­Ó¤½¦¡°ÝÃD,§Ö§â§Ú·dºÆ¤F....
¥H¤U3­Ó¤½¦¡,ª½±µkey¦bÀx¦s®æ®É³£¨S°ÝÃD,¦ý¬°¤°»ò¦bVBA¤¤,§¹¥þµLªk°õ¦æ?
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),"")"

TOP

        ÀR«ä¦Û¦b : ¶¢¤HµL¼Ö½ì¡A¦£¤HµL¬O«D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD