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

§ì¨ú¸ê®Æ¤p°ÝÃD

Dear¤j¤j­Ì¡G

§Úªº­ì©l¸ê®Æ¦bsheet1¡A·Q±qsheet1§ì¸ê®Æ¡A§¹¦¨sheet2

sheet2!B2=SUMPRODUCT((Sheet1!$ ...
jj369963 µoªí©ó 2014-9-17 22:11

¥Î VBA ¤ñ¸û²³æ:
  1. Private Sub Workbook_Open()
  2.   Dim iCol%
  3.   Dim lRow&
  4.   Dim sStr$
  5.   Dim vD
  6.   
  7.   Set vD = CreateObject("Scripting.Dictionary")
  8.   lRow = 2
  9.   With Sheets("Sheet1")
  10.     Do While .Cells(lRow, 1) <> ""
  11.       sStr = .Cells(lRow, 1) & "_" & .Cells(lRow, 3)
  12.       vD(sStr) = vD(sStr) + .Cells(lRow, 5)
  13.       lRow = lRow + 1
  14.     Loop
  15.   End With
  16.   
  17.   lRow = 2
  18.   With Sheets("Sheet2")
  19.      Do While .Cells(lRow, 1) <> ""
  20.        For iCol = 2 To 7
  21.          sStr = .Cells(lRow, 1) & "_" & .Cells(1, iCol)
  22.          .Cells(lRow, iCol) = vD(sStr)
  23.        Next
  24.        lRow = lRow + 1
  25.      Loop
  26.   End With
  27. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD