导入外部存储_获取外部数据用Excel进行数据分析（4）

ゝ一世哀愁。 2023-01-07 08:47 134阅读 0赞

如果我们把所有原始的数据全部加载到Excel，不但Excel运行非常缓慢，同时文件还非常巨大，所以，如何利用Excel获取外部数据，并只把结果显示在Excel，实现数据的自动更新是我们必须知道的基础，这有利于我们加倍的提高工作效率。 接下来，我们一步步实现用Excel来帮助我们更有效的进行物料管理。 通常，我们进行库存管理前，我们必须要知道的是历史物料的消耗，当前库存，客户信息，物料主数据等，然后基于历史消耗去预测将来，在这个基础上再进行后续的操作。 历史消耗的数据量非常巨大，我们至少需要一年的数据进行分析，从ERP等系统下载有时候需要分几个文件存储，因为EXCEL容量的限制，所以，很多文件是TXT格式，导入EXCEL后，字符和数字有时候也是困扰我们的很大问题，同时，等做出清晰的月度物料消耗，我们还需要通过VLOOP函数去找到物料的价格等信息，往往EXCEL运行会趋于缓慢，等下次做同样分析的时候，一切从头开始。如何避免？以下问题： ·        数据量太大，EXCEL运行缓慢 ·        数据更新需要重新计算分析，能否自动更新？ 比如，我们有三年的销售数据分三个文件存储在目录Sales下，文件格式为TXT，命名规则如下： ![bd13e8de4bd9424ac0b443982ae0ca22.png][] 我们将通过链接这三组文件，实现对物料的分析。 || **Excel 链接文件** || 打开空白EXCEL，我们先来导入主数据，通过--(Excel提供了很多可能，你可以通过数据库，网页，云等实现数据导入，这里我们只举例从文件导入，其他方法比较类似) ![213d33dd05d957fc91ccefb5361f1335.png][] Step1：找到你要导入的文件并点击Import ![522170eb219a88c129ff250e3524a1c7.png][] 然后系统会出来一个对话框，你可以看到你要导入的数据信息，EXCEL会根据你最前面的200行自动给你assign数据类型，如果你的数据中有表头，他也会自动给你设置出来，如果没有就直接显示Column1，Column2. ![d443078a11e2039dcb82387e95cf3be9.png][] 为了避免EXCEL文件太大，同时我们也避免其他人看到物料的价格，我们只对这些数据进行link，我们不导入到EXCEL 点击Load边上的下来按钮，找到LoadTo…，出现下面对话框，我们需要选Onlycreate connection，这里有很多其他选择，如果选择这个选项，EXCEL不会导入数据，你只能在PowerQuery中看到这些数据，同时不勾选Addthis data to data module. 点击Okay后，EXCEL界面右面会出现你刚链接的Query。 ![9c2ff0972e4b7e02fe24c0af66643773.png][] 将鼠标移动到MasterData上，你可以看到数据的略缩版，并看到link的信息，在这个界面里，你可以点击EDIT进行link的编辑，或者点击三个小点，进行后续操作。 ![49650c70698ab3bb60b218c3a24ca256.png][] Step 2：修改字段名 我们点击EDIT，出现PowerQuery界面，点开左面的小三角，我们可以看到所有的Query，最右面上面我们可以重新定义Query的名字，下面是我们所有操作的步骤，这是2016版新功能，记录每次操作有利于一旦出现错误，方便查找问题出在哪里。 ![c52d47549b6685df11f5dcee01278b17.png][] 修改字段名，并存储，我们用相同的方法链接Customer主数据 ||  **Excel 链接目录** || 很多时候，数据分几个文件存储，通常的做法是倒入一个，在append到下面，如果是Access以前的版本，需要调用object等来实现对目录的读取，并实现自动导入，对咱非专业IT人士，这样的导入方法太累，太麻烦，Excel 提供了简便的方法，图形化的导入模式，而且可以实现更新，接下来，我们把三个文件的 Sales 数据放到一个folder下，选择从Folder导入： ![14cfd7f72ae269c82909eb30be252834.png][] Step 1： 选择目录导入文件 找到相应的folder后，Excel的对话框跟连接文件有些不同，因为目录下有三个文件，所以这里显示的三个文件信息(如果还有其他文件，可以用filter把不需要的文件filter掉)，然后create connection。 ![5e132b3c823d505b74fc6547b32b9f57.png][]Step 2： 点击 Contenct 边上的按钮， Combine 数据，取得三个文件中的数据。 Step 3：  编辑完成后，我们重新用 LoadTo… 功能把三个 Query 设置成数据 module 。这样我们对数据的导入工作就完成了。

[bd13e8de4bd9424ac0b443982ae0ca22.png]: https://img-blog.csdnimg.cn/img_convert/bd13e8de4bd9424ac0b443982ae0ca22.png
[213d33dd05d957fc91ccefb5361f1335.png]: https://img-blog.csdnimg.cn/img_convert/213d33dd05d957fc91ccefb5361f1335.png
[522170eb219a88c129ff250e3524a1c7.png]: https://img-blog.csdnimg.cn/img_convert/522170eb219a88c129ff250e3524a1c7.png
[d443078a11e2039dcb82387e95cf3be9.png]: https://img-blog.csdnimg.cn/img_convert/d443078a11e2039dcb82387e95cf3be9.png
[9c2ff0972e4b7e02fe24c0af66643773.png]: https://img-blog.csdnimg.cn/img_convert/9c2ff0972e4b7e02fe24c0af66643773.png
[49650c70698ab3bb60b218c3a24ca256.png]: https://img-blog.csdnimg.cn/img_convert/49650c70698ab3bb60b218c3a24ca256.png
[c52d47549b6685df11f5dcee01278b17.png]: https://img-blog.csdnimg.cn/img_convert/c52d47549b6685df11f5dcee01278b17.png
[14cfd7f72ae269c82909eb30be252834.png]: https://img-blog.csdnimg.cn/img_convert/14cfd7f72ae269c82909eb30be252834.png
[5e132b3c823d505b74fc6547b32b9f57.png]: /images/20221119/b9ba6b8c43254d7da255dee32a31441e.png