我想在一串货号代码中求以前三位开头的某批货物的累加数

-Logan

求助.rar (4.4 KB, 下载次数: 21)

2011-12-26 21:57 上传

点击文件名下载附件

-Logan

有哪位侠客有高招的，亮出来，在此谢过了！

ly

1. =SUM((LEFT(A2:A146,3)="D13")*C2:C146)

复制代码

数组公式

wudixin96

=SUMIF(A:A,"D13*",C:C)
这样就行了

heike2004

=SUM((LEFT(A2:A145,3)="D13")*C2:C145)数组

mjzxlmg

这是求D13的
Sub test()
Dim i&, Myr&, Arr, d As Object, k As Long
Set d = CreateObject("Scripting.Dictionary")
With Sheet1
Myr = .Cells(Rows.Count, 1).End(xlUp).Row - 1
Arr = .Range("a1:c" & Myr)
For i = 2 To UBound(Arr)
     s = Left(Arr(i, 1), 3)
     If s = "D13" Then
        d(s) = d(s) + Arr(i, 3)
     End If
Next
MsgBox Application.Transpose(d.keys)(1) & "开头的货品库存量有：" & Application.Transpose(d.items)(1)
.[F2].Value = Application.Transpose(d.keys)(1) & "开头的货品库存量有：" & Application.Transpose(d.items)(1)
End With
Set d = Nothing
End Sub

这是求所有前三位的：
Sub test2()
Dim i&, Myr&, Arr, d As Object
Set d = CreateObject("Scripting.Dictionary")
With Sheet1
Myr = .Cells(Rows.Count, 1).End(xlUp).Row - 1
Arr = .Range("a1:c" & Myr)
For i = 2 To UBound(Arr)
      s = Left(Arr(i, 1), 3)
      d(s) = d(s) + Arr(i, 3)
Next
.[F2].Resize(d.Count, 1).Value = Application.Transpose(d.keys)
.[g2].Resize(d.Count, 1).Value = Application.Transpose(d.items)
.[f1].Resize(1, 2) = Array("货号", "库存量")
End With
Set d = Nothing
End Sub

heike2004

=SUM(IF(LEFT(A2:A145,3)="D13",C2:C145))数组

-Logan

wudixin96 发表于 2011-12-26 22:17
=SUMIF(A:A,"D13*",C:C)
这样就行了

谢谢，这个公式很简单易懂。。

-Logan

heike2004 发表于 2011-12-26 22:50
=SUM(IF(LEFT(A2:A145,3)="D13",C2:C145))数组

呵呵，谢谢虾客啦。。

-Logan

mjzxlmg 发表于 2011-12-26 22:47
这是求D13的
Sub test()
Dim i&, Myr&, Arr, d As Object, k As Long

呵呵，谢谢你，辛苦了，不过我觉得SUMIF这个公式用得更简单一些啊。。。