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

[µo°Ý] ½Ð°Ý¤@¤U¨ç¼Æªºvba

¸ê®Æ¤£¦h, ¥i¥Î³o­Ó:
Sub AAA()
Dim Clmn As Range
Set Clmn = [¤u§@ªí2!K2].Resize([¤u§@ªí2!B65536].End(xlUp).Row - 1)
Clmn = "=B2&C2"
With [¤u§@ªí1!C2].Resize([¤u§@ªí1!A65536].End(xlUp).Row - 1, 3)
     .Formula = "=SUMIF(¤u§@ªí2!$K:$K,$A2&C$1,¤u§@ªí2!$E:$E)"
     .Value = .Value
     .Replace 0, "", Lookat:=xlWhole
End With
Clmn.ClearContents
End Sub

'================================
¸ê®Æ«Ü¦h, ¥Î³o­Ó:
Dim xD, Arr, Brr, i&, j&, T$
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([¤u§@ªí2!E1], [¤u§@ªí2!B65536].End(xlUp))
For i = 2 To UBound(Arr)
    T = Arr(i, 1) & Arr(i, 2)
    xD(T) = xD(T) + Arr(i, 4)
Next i
'----------------------------
Arr = Range([¤u§@ªí1!E1], [¤u§@ªí1!A65536].End(xlUp))
ReDim Brr(1 To UBound(Arr) - 1, 1 To 3)
For i = 2 To UBound(Arr)
For j = 1 To 3
    T = Arr(i, 1) & Arr(1, j + 2)
    Brr(i - 1, j) = xD(T)
Next j
Next i
'----------------------------
[¤u§@ªí1!C2].Resize(UBound(Arr) - 1, 3).Value = Brr
End Sub

'>>>>

TOP

¦^´_ 7# afu9240


KÄæ¬O¥Î¨Ó°µ»²§U¤½¦¡, §Q¥Î§¹¦A²M°£,
±N[¥Í²£§å¸¹]&[¤ÀÃþ]³s±µ¦¨¿W¥ß¦r¦ê, §Y¥i§Q¥Î¨Ó°µSUMIFªº[¯Á¤Þ­È],

¦]¸ê®Æ¦æ¼Æ¥i¯à¤£½T©w,
SUMPRODUCT­Y°µ¥þÄæ¤Þ¥Î, ¥i¯à¨ÏÀÉ®×¥d¦º,
SUMIF¤½¦¡ªº¥þÄæ¤Þ¥Î, ®Ä²v¤ñSUMIFS°ª.

TOP

        ÀR«ä¦Û¦b : ¯à¥I¥X·R¤ß´N¬OºÖ¡A¯à®ø°£·Ð´o´N¬O¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD