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

afu9240

¦U¦ì°ª¤â¤j¤j­Ì¦n

   ªþ¥óÀÉ·Q½Ð±Ð¬°¦óµLªk°õ¦æ,,¦³¼g¤F¤@¬qvbaªºsumif¦ýµLªk°õ¦æ~¨D¸Ñ´b ÁÂÁÂ
20190513.zip (12.7 KB)

¤U¸ü¦¸¼Æ: 7

2019-5-13 09:57

afu9240

¦^´_ 8# ­ã´£³¡ªL


    Ãh¤j¯u±j~~·PÁÂÃh¤j¦^ÂР ÁÂÁÂ

­ã´£³¡ªL

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

afu9240

¦^´_ 5# ­ã´£³¡ªL


    ²a¤j ½Ð±Ð¤@¤U~¤u§@ªí2!k2®Ú¥»¨S¸ê®Æ¬°¤°³Â  ±z±ocode¦³ ¤u§@ªí2!k2¸ò¤u§@ªí2!k2:k2

afu9240

¦^´_ 5# ­ã´£³¡ªL


    ·PÁ²a¤j´£¨Ñ,¦³ÂIÃø »Ý­n¬Ý¤@¤U~~·PÁÂ*10000

­ã´£³¡ªL

¸ê®Æ¤£¦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

'>>>>

rouber590324

DEAR SIR
¥Î²[¼Æ§Y¥i§a.¦p¤U c2¤½¦¡  ¾l¥H¦¹Ãþ±À

=SUMPRODUCT((¤u§@ªí2!$B$2B$65536=A2)*(¤u§@ªí2!$C$2C$65536=C1),¤u§@ªí2!$E$2E$65536)

afu9240

¦^´_ 2# rouber590324

·PÁÂr¤j¦^ÂÐ,, R¤j ¥Î±zªºCODE®M¤JSUMIFS¤]¥i¥H¥Î,  ³oÃä·Q½Ð±Ð­n¦p¦ó­×§ïCode¤@¦¸©Ê±NÀx¦s®æ¸ê®Æ­pºâ±a¥X©O~¨D¨ó§U~TKS
code¯à¦b²¤Æ¶Ü???ÁÂÁ 0513.zip (14.69 KB)

¤U¸ü¦¸¼Æ: 14

2019-5-13 13:47

rouber590324

¸Õ¸Õ

Sub AAA()
Dim M As Long
Sheets("¤u§@ªí1").Select
Range("A1").Select
Sheets("¤u§@ªí1").[D265536].ClearContents

For M = 2 To Sheets("¤u§@ªí1").[A65536].End(xlUp).Row

Sheets("¤u§@ªí1").Cells(M, 4) = Application.SumIf(Sheets("¤u§@ªí2").Range("B:B"), Sheets("¤u§@ªí1").Cells(M, 1), Sheets("¤u§@ªí2").Range("E:E"))

Next
End Sub