使用Excel 2016的获取和转换功能对Excel表格进行数据清洗
使用Excel 2016的获取和转换功能对Excel表格进行数据清洗
gOxiA 之前有了解过 Microsoft PowerBI,所以对大数据的应用非常感兴趣。由于还处在入门阶段,所以目前仍在学习和实践,那么在日常工作中主要是通过处理一些数据表格文件,实现简单的数据清洗,使之数据浏览起来显得更加直观和清晰,以满足自己的需求。虽然整个过程非常简单,但也乐在其中,今天整理一下与大家分享,也希望能带给大家一些帮助和提示。
在Excel 2016中提供了“获取和转换”数据的功能,简单理解呢就是对数据的查询和编辑功能,与 PowerBI 无本质差别,但整个的处理过程都是在Excel 2016中完成的,其价值也非常明显。
例如 gOxiA 定期要处理一些 ITSM 相关的台账数据,并对这个导出的数据表进行无关数据的清洗和整理,最终编辑为 gOxiA 所需要的表格结构,及数据呈现方式。由于这个台账数据需要每一段时间导出一次,那就意味着每次都要重新进行清洗和整理,是相当繁琐和麻烦的,无形之中也会产生很大的工作量,带来工作压力。
如果有一种软件可以实现基于这个原始台账进行编辑和生成,并可动态更新,岂不是高效太多!!!之前也想过用 PowerBI 来实现,但感觉当前的需求过于简单,也没有数据可视化需求,暂时没必要动用 PowerBI,后来发现了Excel 的数据查询(获取和转换)功能,如获至宝!
微软官方对“获取和转换”的解释还是很到位的,虽然是机器翻译,但理解起来也并不困难。Excel 中,我们可以搜索数据源,并进行连接,然后根据需要筛选、更改、删除数据,也可以对表进行合并,以满足我们需要的方式,一旦数据表调整完毕,就可以共享我们清洗过的数据表格文件。
如果按照顺序看“获取和转换”功能中的这些步骤,将发现其通常按照下面的方式进行:
- 连接 - 建立与云中、服务内和本地的数据之间的连接
- 转换 - 调整数据以满足你的需求;原始源保持不变
- 组合 - 从多个数据源,创建数据模型,获得数据的独特见解
- 共享 - 查询完成后可以将其保存、共享或将其用于报表
那么在具体应用中该如何操作呢?!举例来说,gOxiA 有一个定期从后台系统导出的台账表格,原始表格中包含很多无关的数据列需要删除;每行数据的排列顺序也需要重新整理;一些列的数据还需要进行筛选剔除无关数据;且数据类型也需要整理,便于后续的计算;还需要通过“条件格式”对一些数据进行自动标注;最后还需要从某列开始进行冻结以便于查阅。
可以看出上述列出的一些修改实际上包含很大的工作量,如果仅仅是一次性表格,倒也无所谓直接在原始表格上修改,但是这个原始台账每个一段时间就要提取出来进行整理,可想而知会产生多巨大的工作压力。
现在我们就利用 Excel 2016 内置的数据“获取和转换”功能简化上述的工作。首先准备好数据源,本例是一个Excel表格文档。然后新建一个Excel文件,切换到“数据”选项卡,找到“获取和转换”区域,并点击“新建查询”,如下图所示。
Excel 2016 支持从文件、数据库、Azure、在线服务和其他源,进行数据的连接和查询。在本例中选择的是从工作簿,即一个 Excel 文档。
在随后选择完毕 Excel 文件后,便会弹出“导航器”,原始表格的默认查询已经预加载,我们可以选择选择要加载的工作表,或先执行编辑查询。
加载查询后得到的结果即如下图类似,如果在上一步直接选择了加载,那么这里应该看到的是一个与原始表格基本相同的一个工作表。接下来我们就可以根据自己实际的需求对数据表进行修改,为此我们需要在 Excel 工具栏上切换到“查询”选项卡,然后点击“编辑”。
在弹出的“查询编辑器”中我们便可以对工作表数据进行按需清洗,操作过程如同我们编辑 Excel 一样,当完成编辑后,便可点击“关闭并上载”来刷新我们的当前工作表,实现最终的工作表视图结果。之后就可利用一些Excel常规的编辑功能对表格内容进行修饰。
当过了一段时间,原始表格数据进行了更新,我们只需要在“查询”选项卡中,点击“刷新”即可将新数据应用到当前工作表中。而之前我们所做过的筛选、修改、删除等操作都会继续应用到新数据上,无需再次整理。
[Office]HOWTO: 解决 Outlook 无法打开正文嵌入的文件对象 故障
HOWTO: 解决 Outlook 无法打开正文嵌入的文件对象 故障
最近一段时间应该有不少用户遭遇到了Outlook无法打开正文嵌入的文件对象 故障,具体表现为会议约会正文中嵌入的文档对象无法正常打开,会提示“用于创建此对象的程序时Outlook。您的计算机尚未安装此程序或此程序无响应。若要编辑此对象,请安装Outlook或确保Outlook中的任何对话框已关闭。”如下图所示:
出现该问题是由于用户安装了微软于6月13日发布的 Office 安全补丁(KB3203467)所致,该补丁旨在修复用户打开经特殊设计的 Office 文件时可能允许执行代码的漏洞,但是该补丁存在已知问题会导致用户无法正常打开 Outlook 附件或正文中嵌入的文件对象。
在7月5日微软又发布了 KB4011042 用于解决 KB3203467 已知问题,但随后又撤掉了该更新补丁。可能该补丁存在一些问题,直至7月25日微软终于重新发布了用于修复 Office 文件可能允许远程执行代码的漏洞补丁——KB2956078,并且还修复了打开附件或嵌入的文件对象失败的已知问题。对于还在使用 Office 2007 的用户,请安装 KB3213643 解决此问题。
[Office365] Office Deployment Tool 相关配置参考信息
Office Deployment Tool 相关配置参考信息
通过 Office Deployment Tool(ODT)安装的 Office 有一个显著优势就是日后更新非常便捷,只需在 Office 账户下通过更新选项即可将当前 Office 升级到最新版本,而且利用 ODT 还可以下载安装最新的 Build。gOxiA 曾分享过两篇有关的文章“HOWTO: 从本地位置部署 Office365 ProPlus”、“Office 2016 Deployment Tool 现已发布”。
使用 ODT 不仅可以针对企业级部署和安装 Office 365 ProPlus 这些主要商用版本,还能获取到更多的 SKU 以及更多的语言版本。而要获得更多的资源就依赖特定的配置信息,例如要获取 32 或 64 位版本,我们需要使用“OfficeClientEdition”,值 = 32 或者 64 即可。
对于要获得更多 SKU,即 Edition 发行版本,则需要指定“Product ID”,下面列出了各 Edition 所对应的值。
Edition | Value |
Office 2013/2016 Home and Student | HomeStudentRetail |
Office 2013/2016 Personal | PersonalRetail |
Office 2013/2016 Home and Business | HomeBusinessRetail |
Office 2013/2016 Professional | ProfessionalRetail |
Office 2013/2016 Professional Plus | ProPlusRetail |
Office 365 Home Premium | O365HomePremRetail |
Office 365 Small Business Premium | O365SmallBusPremRetail |
Office 365 Business | O365BusinessRetail |
Office 365 Professional Plus | O365ProPlusRetail |
Access 2013/2016 | AccessRetail |
Excel 2013/2016 | ExcelRetail |
InfoPath 2013/2016 | InfoPathRetail |
OneNote 2013/2016 | OneNoteRetail |
Outlook 2013/2016 | OutlookRetail |
PowerPoint 2013/2016 | PowerPointRetail |
Publisher 2013/2016 | PublisherRetail |
Word 2013/2016 | WordRetail |
SharePoint Designer 2013 | SPDRetail |
Project 2013/2016 Standard | ProjectStdRetail |
Project 2013/2016 Professional | ProjectProRetail |
Project 2013/2016 Standard volume license | ProjectStdXVolume |
Project 2013/2016 Professional volume license | ProjectProXVolume |
Visio 2013/2016 Standard | VisioStdRetail |
Visio 2013/2016 Professional | VisioProRetail |
Visio 2013/2016 Standard volume license | VisioStdXVolume |
Visio 2013/2016 Professional volume license | VisioProXVolume |
Skype for Business Basic 2015 (Office 2013) | LyncEntryRetail |
Skype for Business 2015 (Office 2013) | LyncRetail |
Skype for Business Basic 2016 (Office 2016) | SkypeforBusinessEntryRetail |
Skype for Business 2016 (Office 2016) | SkypeforBusinessRetail |
而下表则是语言版本,即“Language ID”所对应的值。
Language | Value |
Arabic (Saudi-Arabia) | ar-sa |
Bulgarian (Bulgaria) | bg-bg |
Chinese, simplified (PR China) | zh-cn |
Chinese, traditional (Taiwan) | zh-tw |
Croatian (Croatia) | hr-hr |
Czech (Chech Replublic) | cs-cz |
Danish (Denmakr) | da-dk |
Dutch (Netherlands) | nl-nl |
English (USA) | en-us |
Estonian (Estonia) | et-ee |
Finnish (Finland) | fi-fi |
French (France) | fr-fr |
German (Germany) | de-de |
Greek (Greece) | el-gr |
Hebrew (Israel) | he-il |
Hindi (India) | hi-in |
Hungarian (Hungary) | hu-hu |
Indonesian (Indonesia) | id-id |
Italian (Italy) | it-it |
Japanese (Japan) | ja-jp |
Kazakh (Kazakhstan) | kk-kz |
Korean (Republic of Korea) | ko-kr |
Latvian (Latvia) | lv-lv |
Lithuanian (Lithuania) | lt-lt |
Malay (Malaysia) | ms-my |
Norwegian, Bokmål (Norway) | nb-no |
Polish (Poland) | pl-pl |
Portuguese (Brazil) | pt-br |
Portuguese (Portugal) | pt-pt |
Romanian (Romania) | ro-ro |
Russian (Russian Federation) | ru-ru |
Serbian, Latin (Serbia / Montenegro) | sr-latn-cs |
Slovakian (Slovakia) | sk-sk |
Slovenian (Slovenia) | sl-si |
Spanish (Spain) | es-es |
Swedish (Sweden) | sv-se |
Thai (Thailand) | th-th |
Turkish (Turkey) | tr-tr |
Ukrainian (Ukraine) | uk-ua |
Vietnamese (Vietnam) | vi-vn |