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

[µo°Ý] ¥ÎVBA¨Ó°õ¦æSUMPRODUCT¦h±ø¥ó²Î­p

¦^´_ 10# GBKEE
«D±`·PÁ¶W¯Åª©¥Dªº«ü¾É
·P¿E¤£ºÉ
ÁÂÁÂ
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 8# GBKEE
Dear GBKEE
Private Sub ªí®æ²Î­p(Rng As Range)¤¤
¨S¦³¸ê®ÆªºÀx¦s®æ¡A¥Ø«e¬°ªÅ®æ¡A
¦pªG¨S¦³¸ê®ÆªºÀx¦s®æ¶ñ¤J"0"¡A
½Ð°Ý¦p¦ó­×§ï
ÁÂÁ«ü¾É
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 12# b9208
  1. Private Sub ªí®æ²Î­p(Rng As Range)
  2.     Dim R As Integer, C As Integer
  3.     With Rng
  4.         For R = 3 To .Rows.Count - 1
  5.             For C = 2 To .Columns.Count
  6.                 If .Cells(1) = "¥þ³¡" Then                  '¥þ³¡
  7.                     .Cells(R, C) = D(1)(.Cells(R, 1) & Mid(.Cells(1, C), 1, 4) & .Cells(2, C))
  8.                 Else                                        '°Ï°ì
  9.                     .Cells(R, C) = D(2)(.Cells(R, 1) & Mid(.Cells(1, C), 1, 4) & .Cells(2, C) & .Cells(1))
  10.                 End If
  11.                 '********
  12.                 If .Cells(R, C) = "" Then .Cells(R, C) = 0   
  13.                 '********
  14.             Next
  15.         Next
  16.         For C = 2 To .Columns.Count
  17.             .Cells(.Rows.Count, C).FormulaR1C1 = "=SUM(R[-" & .Rows.Count - 3 & "]C:R[-1]C)"  '¤½¦¡
  18.             .Cells(.Rows.Count, C) = .Cells(.Rows.Count, C).Value
  19.         Next
  20.     End With
  21. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 13# GBKEE
«D±`·PÁ«ü¾É
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 8# GBKEE
Dear GBKEE
¦p¤U¦¡¤l¡A²Ö­p¦¸¼Æ
22.   M = .Cells(i, "D") & Mid(.Cells(i, "E"), 1, 4) & .Cells(i, "F")
23.   D(1)(M) = D(1)(M) + 1               '¥þ³¡
24.   M = .Cells(i, "D") & Mid(.Cells(i, "E"), 1, 4) & .Cells(i, "F") & .Cells(i, "L")
25.   D(2)(M) = D(2)(M) + 1               '°Ï°ì  

¦p±ý­pºâ¬Û¦P¸ê®Æ¥u­pºâ¤@µ§¡C¦p
22. M = .Cells(i, "D") & .Cells(i, "E") & .Cells(i, "F") ¦¹¤T¶µ¸ê®Æ¬Û¦PªÌ¥u­pºâ¤@µ§
24. M = .Cells(i, "D") & .Cells(i, "E") & .Cells(i, "F") & .Cells(i, "L")  ¦¹¥|¶µ¸ê®Æ¬Û¦PªÌ¥u­pºâ¤@µ§

·PÁ«ü¾É
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 15# b9208
©Ò¦³µ{¦¡½X¤¤
Mid(.Cells(i, "E"), 1, 4)   ­×§ï¬°  .Cells(i, "E")
Mid(.Cells(1, C), 1, 4)    ­×§ï¬°  .Cells(1, C)

1.¥i¤@¤@¦Û¦æ­×§ï
2.Vba µøµ¡«ü¥O ½s¿è->¨ú¥N
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¯d­Ó¾Ç²ß¬ö¿ý
¥Î¦r¨å¦b¬Y¨Ç±¡ªp³t«×¦n¹³¤£§Ö
³o­Ó¨Ò¤l§Ú¤j·§¥u·|·Q¨ì¥ÎSQLªº¤è¦¡³B²z
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 16# GBKEE
Dear GBKEE
¦pªG¨Ì·Ó¤è¦¡­×­q¡G
Mid(.Cells(i, "E"), 1, 4)   ­×§ï¬°  .Cells(i, "E")
Mid(.Cells(1, C), 1, 4)    ­×§ï¬°  .Cells(1, C)

¨º¶g¦¸¼Æ¦r¦p¦ó¨ú±o¤Î¿é¥X
ÁÂÁÂ
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 18# b9208
¨º¶g¦¸¼Æ¦r¦p¦ó¨ú±o¤Î¿é¥X !!!
¥h±¼MID: ¨ú[¥Ó½Ð½s¸¹]§¹¥þ¬Û¦P,ÁÙ­nÂkÃþ¨ì¶g¦¸¤¤
½Ð¤W¶Ç½d¨Ò¹Ïªí¬Ý¬Ý
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 19# GBKEE

¤W¶ÇÀÉ®×
·q½Ð«ü¾É
W0510.rar (39.91 KB)
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¨S¦³©Ò¦³Åv¡A¥u¦³¥Í©Rªº¨Ï¥ÎÅv¡C
ªð¦^¦Cªí ¤W¤@¥DÃD