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

-Logan · 发表于 2011-12-26 21:59

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

-Logan · 发表于 2011-12-26 22:00

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

ly · 发表于 2011-12-26 22:16

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

复制代码

数组公式

wudixin96 · 发表于 2011-12-26 22:17

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

heike2004 · 发表于 2011-12-26 22:47

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

mjzxlmg · 发表于 2011-12-26 22:47

本帖最后由 mjzxlmg 于 2011-12-26 23:01 编辑

这是求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 · 发表于 2011-12-26 22:50

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

-Logan · 发表于 2011-12-28 17:22

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

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

-Logan · 发表于 2011-12-28 17:23

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

呵呵，谢谢虾客啦。。

-Logan · 发表于 2011-12-28 17:25

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

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

		自动登录	找回密码
密码			免费注册