Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
查看: 8110|回复: 25

VBA查询指定材料表在另一个库存表中的库存总数和品牌加总

[复制链接]
发表于 2020-5-8 11:00 | 显示全部楼层 |阅读模式
2学分
本帖最后由 davidshi 于 2020-5-8 12:44 编辑

VBA大师请协助:

     EXCEL函数查询左侧物料对应 无锡华虹表单里 L列材料总数,H列品牌合并。因为有的材料编码对应多个品牌,或者品牌相同放置的位置不同是分开的存在多条该材料数量记录。多数材料在别的库存表中只有一个记录,但有的有2~4个左右记录。


VBA查询不会弄,黄色区域用excel函数累加数量,品牌合并显示;库存总数量没问题,但超过3个以上记录的材料品牌无法全部合并。

请大师看能否VBA查询指导下我,自己想用数组写入,但尝试失败,右上方绿色区域VBA程序写入的数量 品牌全是错误的,自己都看不懂。




1.jpg

Inventory_0427.rar

171.54 KB, 下载次数: 9

最佳答案

查看完整内容

数量肯定是相加了的,但你说的品牌,我不知道同一型号是否会有不同的品牌?如果有,品牌如何相加?是把品牌串起来就行了吗? 你可以自己根据需要修改如下代码: If zd.exists(s) Then '字典存在,表示存在多条记录 arr1 = zd(s) '读取字典原数据到数组arr1 arr1(2) = arr1(2) + arr(2) '数量累加,arr1为原数据,arr为当前数据, ...
excel精英培训的微信平台,每天都会发送excel学习教程和资料。扫一扫明天就可以收到新教程
发表于 2020-5-8 11:00 | 显示全部楼层
本帖最后由 hfwufanhf2006 于 2020-5-8 18:09 编辑
davidshi 发表于 2020-5-8 17:37
你好,朋友。
刚才验证了以下,库存表中如果有两个以上的记录,并没有加总数量和 品牌。

数量肯定是相加了的,但你说的品牌,我不知道同一型号是否会有不同的品牌?如果有,品牌如何相加?是把品牌串起来就行了吗?
你可以自己根据需要修改如下代码:
   If zd.exists(s) Then                           '字典存在,表示存在多条记录
       arr1 = zd(s)                                 '读取字典原数据到数组arr1
       arr1(2) = arr1(2) + arr(2)              '数量累加,arr1为原数据,arr为当前数据,只有两个元素:品牌和数量,arr(1)是品牌,arr(2)是数量
       if instr(arr1(1),arr(1))=0 then         '品牌处理,这是我新加的,先要判断是否有新品牌,只有先出现的品牌才累加          arr1(1) = arr1(1) & arr(1)              '目前只是串起来了,你根据需要自己插入特定的字符分割
       endif
       zd(s) = arr1

    else

回复

使用道具 举报

发表于 2020-5-8 11:29 | 显示全部楼层
你表达的不是很清楚,“无锡华虹“里面还有很多“空”的品牌,该如何汇总?
下面代码把所有的“空”品牌都归在同一类:

Dim zd As Object
Set zd = CreateObject("scripting.dictionary")
For i = 2 To Worksheets("无锡华虹").[b10000].End(3).Row
    s1 = Worksheets("无锡华虹").Cells(i, "H")
    s2 = Worksheets("无锡华虹").Cells(i, "L")
    If zd(s1) Then
       s3 = zd(s1) + s2                                  '如果是多行记录,累加
       zd(s1) = s3
    Else
       zd(s1) = s2
    End If
Next i

Dim arr
arr = zd.keys

rem 下面是把字典内容输出,你可随便修改。我把写在AK:AL列,是方便你对照
Range("ak4:al1000").ClearContents            '先清除Ak:aL
For i = 0 To UBound(arr)
    Cells(i + 4, "al") = arr(i)                        'AL列写入品牌
    Cells(i + 4, "ak") = zd(arr(i))                 'AK列写入数量
Next i


回复

使用道具 举报

 楼主| 发表于 2020-5-8 11:54 | 显示全部楼层
hfwufanhf2006 发表于 2020-5-8 11:29
你表达的不是很清楚,“无锡华虹“里面还有很多“空”的品牌,该如何汇总?
下面代码把所有的“空”品牌都 ...

你好,很感谢,我目前还看不明白,然后运行代码但结果不符,不知道什么原因,我用函数的结果和右边VBA程序结果不一致,手动检查也发现结果不对。
我想要查询材料表,是否有库存,库存总数,库存品牌,如果有库存数量但库存品牌是空的,忽略品牌即可。
1.jpg

回复

使用道具 举报

发表于 2020-5-8 12:14 | 显示全部楼层
davidshi 发表于 2020-5-8 11:54
你好,很感谢,我目前还看不明白,然后运行代码但结果不符,不知道什么原因,我用函数的结果和右边VBA程 ...

你的函数我不懂,我理解你是要按照品牌做分类汇总。分类汇总其实数据透视也很简单,看下面截图:用数据透视生成的结果(右边)与代码产生的结果(需要按品牌排序)是一模一样的;

你解释说是查询,越说我越糊涂,我现在反而不知道你要干什么了;




1.png
回复

使用道具 举报

 楼主| 发表于 2020-5-8 12:44 | 显示全部楼层
hfwufanhf2006 发表于 2020-5-8 12:14
你的函数我不懂,我理解你是要按照品牌做分类汇总。分类汇总其实数据透视也很简单,看下面截图:用数据透 ...

其实很好理解嘛,”PR“物料编码清单是要准备购买材料的,但改还有其他仓库有库存表 比如”无锡华虹“,我想实现的功能就是,每一次登记到这张表”PR"的物料编码,都是准备采购的,我用VBA查询查询其他仓库,分别写入每一个物料编码的右边某个区域。用于对比现有库存和品牌,再决定是否需要购买。(我原始表6 7 哥仓库,我删除了,只要方法学到了就行)
如果 对应仓库里左侧物料编码查询不到记录,就忽略。换下一个物料编码继续查询。理论上左侧物料编码是唯一的;库存表”无锡华虹“里的物料编码可能不是唯一。


回复

使用道具 举报

发表于 2020-5-8 14:03 | 显示全部楼层
davidshi 发表于 2020-5-8 12:44
其实很好理解嘛,”PR“物料编码清单是要准备购买材料的,但改还有其他仓库有库存表 比如”无锡华虹“, ...

对你的意图我琢磨了好久,你再试下这个对不对?仍然是写在 AL:AK列


Dim zd As Object
Set zd = CreateObject("scripting.dictionary")
Dim arr(1 To 2)
Dim arr1
For i = 2 To Worksheets("无锡华虹").[b10000].End(3).Row
    s = Worksheets("无锡华虹").Cells(i, 1)
    arr(1) = Worksheets("无锡华虹").Cells(i, "H")
    arr(2) = Worksheets("无锡华虹").Cells(i, "L")
    If zd.exists(s) Then
       arr1 = zd(s)
       arr1(2) = arr1(2) + arr(2)
       zd(s) = arr1
    Else
       zd(s) = arr
    End If
Next i
Range("ak4:al1000").ClearContents
For i = 4 To [b10000].End(3).Row
    s = Cells(i, 2)
    If zd.exists(s) Then
       arr1 = zd(s)
       Cells(i, "al") = arr1(1)
       Cells(i, "ak") = arr1(2)
    End If
Next i



评分

参与人数 1学分 +2 收起 理由
davidshi + 2 学习

查看全部评分

回复

使用道具 举报

 楼主| 发表于 2020-5-8 17:27 | 显示全部楼层
你好,朋友,非常感谢您的协助。
自己用数组很复杂的完成了一个仓库的查询,套用第二个仓库就是空白的。
谢谢你的代码,和帮助,用你的程序可以查询,唯一的遗憾就是,左侧物料编码里有一些是空白的没有编码,查询后会加总所有库存表空白的项次。
可否过滤掉物料编码为空的情况呢,想加if条件不知道放在哪儿。
1.jpg

回复

使用道具 举报

 楼主| 发表于 2020-5-8 17:37 | 显示全部楼层
hfwufanhf2006 发表于 2020-5-8 14:03
对你的意图我琢磨了好久,你再试下这个对不对?仍然是写在 AL:AK列

你好,朋友。
刚才验证了以下,库存表中如果有两个以上的记录,并没有加总数量和 品牌。

回复

使用道具 举报

 楼主| 发表于 2020-5-22 10:39 | 显示全部楼层
hfwufanhf2006 发表于 2020-5-8 18:01
数量肯定是相加了的,但你说的品牌,我不知道同一型号是否会有不同的品牌?如果有,品牌如何相加?是把品 ...

嗨,谢谢大师的指导。最近忘记登录账号一直没来得及感谢亲。

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-29 06:57 , Processed in 0.868057 second(s), 15 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表