ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 4134|回复: 6

[分享] 【原创】引用外部数据源--来自Microsoft Query--之余生产/合同管理的应用实例(方法)

[复制链接]

TA的精华主题

TA的得分主题

发表于 2016-11-9 20:28 | 显示全部楼层 |阅读模式
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
通过mid函数
能够截取身份证号码当中的数字
从而判定人员的
籍贯:省份、区县
年龄:今天日期-出生年月日
性别:第17位,奇数为男,偶数为女

那么如何快速输入身份证号码呢?
我们可以使用“二代身份证读卡器”读取
1.png
我在编制HR人事档案管理系统时
曾今购买过一台二代身份证读卡器
当时读卡器扫描后的数据是实时保存在链接的PC端电脑中的excel文件当中
当时我也是学艺不精
没有掌握从A表实时更新数据到B表的方法
2.png
没有想到A/B两表联动的重要应用?
它能有效解决单个excel文件无法多人并发访问的问题呀!!!

也就是说,excel可以变成:
根据不同的业务员,分配文件/文件夹的访问权限
然后通过更新外部数据源的方法,将所有的数据综合到汇总表当中
再在汇总表进行数据统计与分析,制作各类看板
这不就是信息系统(ERP)的前身吗?

温故而知新,这个技巧是最近我在excelhome泡论坛以后get的新方法
目前正在使用这个方法,编制我们公司的
【营销合同管理系统】
公司目前有10+位营销员
而且产成品入库、出库(物流发货)、财务开票的基础数据源
3.png
分别来自不同的业务部门
如果单纯的把所有的表格复制粘贴到一起
只是一个静态的数据

默默的提个醒,如果您的excel表格,需要进行大量的重复性动作,比如:复制粘贴。
那么你就该问一问:“还有没有更简便的方法?”
答案往往都是肯定的。

目前我正在编制的【营销合同管理系统】构建思路是这样的:
①各个营销员在公司内网,网上邻居上,通过共享文件夹权限,只可访问自己的合同文件夹,登记合同台账。
(表样可以设计的很美观,但要保证数据库的规范)
4.png
供应(物流发货)部、生产部、财务部,同理,共享自己的业务表单。

②通过excel的Microsoft Query,链接各数据源表。然后通过VBA的方式,将10+个营销员的合同清单,合并成一页数据库。
(excelVBA,合并多张结构相同的表为一张sheet)
5.png
贴一下代码(详见二楼回帖,单篇发帖字数超了。。。。):

③在合并后的数据库当中,插入数据透视表/数据透视图,制作营销看板。
可以通过切片器的联动,制作动态图表。
161010-1.gif

下面介绍一下Microsoft Query工具的具体使用方法:
①【数据】选项卡--【自其他来源】--【来自Microsoft Query】
01.jpg
②【选择数据源】--【excel files*】--【确定】
02.png
③【选择工作薄】,选择您需要导入的文件。
03.png
如果是网上邻居上的文件,可以复制文件路径,并将共享文件夹的名称,改为那台电脑的IP地址,如:192.168.1.101

④如果您查找不到工作表的话,需要点开【选项】勾选【系统表】
04.jpg
点击确定后,可以查看到您的工作表
05.png
选择对应的字段后,点击【下一步】
如果有筛选条件的话,可以在这里进行设置,如果显示全部数据的话,点【下一步】
这里是设置引用的数据是按照哪个字段的升序/降序进行排序的
06.png
设置完毕后,点击【下一步】
07.png

【完成】

④点击【确定】,即可以把外表的数据源,导入到当前的excel表格当中。
11.jpg
我们可以点击【属性】按钮,就可以查看到刚刚的一系列设置,实际上是一个SQL语句,把其他表格当中的数据,引用过来了。
12.jpg

通过这样的方法,我们能够完成外部数据的引用/链接。
实时更新数据源,随数据源的变化而联动
能够有效解决权限管理、数据并发访问的问题。


file:///C:\Users\Administrator\AppData\Roaming\Tencent\Users\348902122\QQ\WinTemp\RichOle\JRE`EJGN~N3HR)KB(K92S]D.png

13.jpg

TA的精华主题

TA的得分主题

 楼主| 发表于 2016-11-9 20:29 | 显示全部楼层
VBA部分的代码:

Sub 合并()
      
      Application.ScreenUpdating = False
      Call 刷新合同源
      Sheets("合同汇总").Select
      Dim wh As Worksheet, sh As Worksheet
      Dim last_column%, str As String, last_row As Integer, n As Integer, a As Integer
      Dim crr(), arr(), brr()
      Set wh = Worksheets("合同汇总")
      With wh
            last_column = .Cells(1, Columns.Count).End(xlToLeft).Column
            arr = .Range(.Cells(1, 1), .Cells(1, last_column))
           
      End With
      
      For Each sh In Worksheets
            If sh.Name = wh.Name Then Exit For
            With sh
                  last_column = .Cells(1, Columns.Count).End(xlToLeft).Column
                  last_row = .Cells(Rows.Count, 1).End(xlUp).Row
                  brr = .Range(.Cells(1, 1), .Cells(last_row, last_column))
                  For i = 2 To UBound(brr)
                        n = n + 1
                        ReDim Preserve crr(1 To UBound(arr, 2), 1 To n)
                        For k = 1 To UBound(arr, 2)
                              For j = 1 To UBound(brr, 2)
                                    If InStr(1, Trim(arr(1, k)), Trim(brr(1, j)), vbBinaryCompare) > 0 Then
                                          crr(k, n) = brr(i, j)
                                    End If
                              Next j
                        Next k
                  Next i
            End With
      Next
      wh.Range("A2:S65536").ClearContents
      wh.Range("a2").Resize(UBound(crr, 2), 19) = Application.Transpose(crr)
      Application.ScreenUpdating = True
      Sheets("营销看板").Select
      ActiveSheet.PivotTables("数据透视表1").PivotCache.Refresh
MsgBox "ok"
End Sub


我们再衍生一下它的应用场景:
数控车间的设备上,有数据实时采集器。
根据机器设备的运行情况,采集完工产品的数量,然后传输到数据中心当中。
数据中心对比计划完成情况,实时展现在车间的电子看板上。

生产调度、总监,根据机器设备运行情况
灵活安排生产,并将信息反馈给营销部门,安排发货。
这不就是智能制造,中国制造2025,工业4.0的前身么?!!!

********************
非常感谢爱学习的您,能够关注我的这篇分享。
如您有office相关的问题,欢迎与我交流。
希望我微薄的office知识,能够帮助您,提高桌面生产力~
我是【表姐】凌祯
邮箱:348902122@qq.com
微信号:lingzhen08300925


TA的精华主题

TA的得分主题

 楼主| 发表于 2016-11-11 15:37 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2017-7-29 22:30 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-8-1 21:18 | 显示全部楼层
tangxp_ys 发表于 2017-7-29 22:30
很好的思路,借鉴学习了!

感谢支持~~~~~~~~~~~~~~~~~~~~~~·

TA的精华主题

TA的得分主题

发表于 2020-10-23 10:17 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
作为一个“表姐”,怎么这么厉害~!我摩拜

TA的精华主题

TA的得分主题

发表于 2022-7-27 17:49 | 显示全部楼层
数据写入数据库更好吧,有没有成品模板分享
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-28 16:59 , Processed in 0.065251 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

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