2018 年 1 月
第 33 卷，第 1 期
Office - 数据分析、机器学习和 Microsoft Excel
作者 Michael Saunders | 2018 年 1 月
请花一点时间综合分析一下 Excel。它是一款无处不在的数据分析工具，不仅操作灵活，而且功能强大。从我使用的便捷小型待办事项列表电子表格，一直到全球顶级银行使用的 100MB 大型财务模型工作簿，都有它的身影。在所有复杂功能以及各种精美图表和格式的背后，真正的价值在于计算（内部称为“Calc”）。借助 Calc，可以在单元格之间建立关系。通过编写描述值之间依赖关系树的简单公式（见图 1），表达复杂模型。然后，只要用户执行更改，Calc 就会根据这些公式更新从属值。
.png "用于在单元格之间建立关系的 Calc")
图 1：用于在单元格之间建立关系的 Calc
Excel 提供了大量实用函数，以供在公式中使用。这些函数包括基本的 AVERAGE 函数 (bit.ly/1Tjynwl)、SEARCH (bit.ly/2jhcEuV) 等用于查找子字符串的字符串分析函数和 T.TEST 统计函数 (bit.ly/2ipowKE) 等更复杂的计算函数。人们在工作单位和学校内学习在公式中使用的 Excel 函数，自然就会与这些函数进行交互，以满足自己的日常计算需求。所以，若要提供 Excel 尚无的强大功能，不妨生成具有专门用途的 Excel 函数。有关示例，请参阅图 2。Contoso Cryptographers Corp. 要发布一项解决方案，以帮助侦探在 Excel 中分析数据，从而解码消息。便捷的密码破译工具之一旨在快速识别质数，因此 Contoso 希望为数百万已使用 Excel 的侦探提供 ISPRIME 函数。本文稍后将介绍 Contoso 如何生成此函数，以及它的加载项需要的其他功能。
.png "用于识别质数的 Contoso 函数")
图 2：用于识别质数的 Contoso 函数
我可以生成怎样的解决方案？
开始生成前，请注意并非所有 Excel 功能扩展都应使用 Excel 公式。若要决定自己的扩展是否应使用，请先仔细想想以下问题：“除了要返回 Excel 值所在的单元格外，我的函数还需要更改其他任何单元格吗？” 也就是说，函数有“副作用”吗？在 Excel 中，除了要返回值所在的单元格外，公式不应更改其他任何单元格。虽然此更改可能会触发其他更改，但这些更改还是由没有“副作用”的独立公式生成。如果在 A1 单元格中键入“=SUM()”，就不应在工作表上添加图表，也不应在单元格下面新增一行，亦不应在 Internet 上更改财务数据库中的数字，尽管可以在生成的解决方案（可能包含函数及其他功能）中控制所有这些行为。
应生成用来创建 Excel 函数的解决方案的类型视所掌握的技能和目标而定。如果是专业开发人员，无论是像 Contoso Cryptographers 一样对外出售解决方案，还是为组织内人员生成解决方案，加载项都是最适合 Excel 数据分析解决方案的工具，如“Excel 加载项中的 JavaScript 自定义函数”部分所述。 如果是要生成智能模型以供数据分析员在公司使用的 AI 开发人员或数据科学家，请参阅本文稍后将介绍的“Azure 机器学习函数”部分。如果尚未掌握任何技能，Microsoft 今后也将会提供解决方案！
Excel 加载项中的 JavaScript 自定义函数
Excel 加载项是专业开发人员可以生成的工具，用于扩展 Excel 并与工作簿数据进行交互 (bit.ly/2AUOsZk)。Excel 加载项已有数千个，全都跨 Excel 平台（包括 Windows、Mac、Excel Online 和 iOS）运行。熟悉 Web 技术的人都觉得加载项容易生成，因为就像是编写网页一样。加载项运行 HTML、JavaScript 和 CSS，可以调用外部 Web 服务器。首先，无需更改任何代码，即可在各种不同的平台上运行加载项。（虽然 Excel 还支持其他类型的加载项（如 bit.ly/2qsPfLe 所述），但它们既无法跨平台运行，也无法部署到应用商店。） 图 3 展示了加载项的关键组成部分。主要部分是 manifest.xml 文件，它指定了加载项其余部分的 Web 位置及其他元数据。其余代码由开发人员部署，就像部署其他任何 Web 应用一样。
.png "加载项的关键组成部分")
图 3：加载项的关键组成部分
加载项提供了许多种实用方式来扩展 Excel（请参阅 bit.ly/2AV47rw 上的文档）。它们可以添加功能区按钮、关联菜单选项以及 HTML 任务窗格和对话框等 UI 元素。它们还可以通过数千个 API 与工作簿进行交互，如能够添加和删除行、插入和编辑图表以及向单元格应用格式。现在，随着新预览版 JavaScript 自定义函数的发布，可以扩展 Excel 公式。
借助自定义函数，开发人员可以使用加载项向 Excel 添加任何 JavaScript 函数 (bit.ly/2AYtNUW)。然后，用户可以访问自定义函数，就像访问 Excel 中的其他任何原生函数（如 AVERAGE）一样。接下来，将深入探究 Contoso Cryptographers ISPRIME 函数，看看它是如何编写的（虽然也可以仅使用内置 Excel 函数检查素性，但这对于请求计算的人员来说困难得多）。
function isPrime(n) {
var root = Math.sqrt(n);
if (n < 2) return false;
for (var divisor = 2; divisor <= root; divisor++){
if(n % divisor == 0) return false;
}
return true;
}
此函数仅通过几行 JavaScript，就能检查最大为平方根的所有除数，从而确定输入是否为质数。所有实际逻辑均已完成。但可编写的不仅仅只是此函数本身。为了成为有效扩展点，自定义函数必须看起来就像是原生函数一样。原生函数包含大量自定义信息。有关 Contoso Cryptographers ISPRIME 函数示例，请参阅图 4。除了显示此函数本身名称外，Excel 还显示有助于阐明函数用途的实用说明。
.png "自定义函数和原生函数在用户键入时自动完成")
图 4：自定义函数和原生函数在用户键入时自动完成
图 5 展示了又一示例。选择 CONTOSO.ISPRIME 后，Excel 显示参数名称（在此示例中为 n），以方便用户更轻松地选取正确的输入。
图 5：Contoso ISPRIME 函数
Excel.Script.CustomFunctions["CONTOSO"]["ISPRIME"] = {
call: isPrime,
description: "Determines whether the input is prime",
helpUrl: "https://example.com/help.html",
result: {
resultType: Excel.CustomFunctionValueType.boolean,
resultDimensionality: Excel.CustomFunctionDimensionality.scalar,
},
parameters: [{
name: "n",
description: "the number to be evaluated",
valueType: Excel.CustomFunctionValueType.number,
valueDimensionality: Excel.CustomFunctionDimensionality.scalar,
}],
options: { batched: false, streaming: false }
};
如图 5**** 所示，这两部分信息及其他内容由开发人员在此函数的元数据的 JavaScript 定义中提供。
可以看到，说明和各参数的名称均被指定为字符串。我不会在本文中介绍所有的元数据，但大家可以查看相关文档，了解详细信息。如果熟悉加载项模型，可能会想知道为什么是在 JavaScript 中提供此信息，而不是进行静态硬编码，就像在 manifest.xml 文件中一样。原因在于要确保灵活性。在 Contoso 示例中，加密函数已定义且预先已知。但有时也可能希望能够根据具体情况启用不同的函数。
虽然 Contoso Cryptographers 对 ISPRIME 函数的简单性赞不绝口，但它们的下一个目标较难一些。它们要生成函数，以生成用于加密文本的随机数。虽然 Excel 有绝佳的 RAND 函数，但问题在于 RAND 不适合加密用途，因为它的值是按过程生成的伪随机数。相比之下，通过 random.org 生成的数字可安全用于此目的，即通过大气噪音生成的数字。当然，硬编码随机数毫无益处；相反，Contoso 必须设计可以调用 random.org 并通过 HTTP 请求提取随机数的函数。幸运的是，自定义函数简化了如何添加 Web 请求。下面展示了 Contoso 的异步 RANDOM 函数：
function getRandom(min, max) {
return new OfficeExtension.Promise(function(setResult, setError){
sendRandomOrgHTTP(min, max, function(result){
if(result.number) setResult(number);
else setError(result.error);
});
});
}
此函数与 ISPRIME 的关键区别在于，RANDOM 是异步函数。也就是说，它先直接返回 JavaScript 承诺（而不是向 Excel 返回值），再对 random.org 服务发出 XMLHttpRequest（在函数中不可见，位于 sendRandomOrgHTTP 方法中）。然后，一旦 Web 服务有响应，Contoso 就会使用 random.org 数字解析承诺，将它写入单元格。由于各家公司要扩展 Excel 函数的关键原因之一是要将 Web 数据引入 Excel，因此 Microsoft 希望能够常用异步函数。
所有异步函数的一个共同点是，需要一段时间才能返回结果，所以 Excel 在等待函数解析的同时，在单元格中显示“#GETTING_DATA”消息（见图 6）。
.png "等待异步函数返回结果")
图 6：等待异步函数返回结果
在此示例中，HTTP 请求可快速完成，大约需要十分之一秒。不过，如果需要，此函数可执行许多调用，评估时间可能会更长。对于 JavaScript 自定义函数，解析 JavaScript 值能够让值立即出现在单元格中。图 7 展示了返回值后同一 RANDOM 函数的 Excel 视图。
.png "返回值后 RANDOM 函数的 Excel 视图")
图 7：返回值后 RANDOM 函数的 Excel 视图
当然，使用自定义函数的加载项可能还包含其他一些功能。Contoso Cryptographers 不妨创建任务窗格和功能区选项卡，以方便客户轻松访问自定义函数。此外，还建议设置它们的行为方式和使用指南。JavaScript API 甚至可用于方便客户通过单击按钮加密整个数据工作表。无论决定如何，都可以确信的是，整个加载项将跨 Excel 平台运行，而无需更改任何代码。
部署包含这些 JavaScript 自定义函数的加载项的主要方法有两种，具体视受众而定。公共 Office 应用商店 (bit.ly/2A70L5o) 适用于已安装 Excel 的任何人，因为 Excel 功能区中有用于浏览应用商店的按钮。应用商店支持免费和付费加载项，并要求通过 Microsoft 进行提交和验证。不过，Contoso Cryptographers Corp. 直接将加载项部署到小型企业客户。这样一来，他们无需执行任何单击操作，即可安装加载项。相反，它向所有客户提供了清单文件。然后，IT 管理员可以通过 O365 管理中心选择哪些用户能够获取访问权限（见图 8 中的界面）。这些用户可以自动安装加载项。
.png "向组织部署自定义函数")
图 8：向组织部署自定义函数
希望大家能够从 Contoso Cryptographers 资源中汲取到灵感，尝试编写自己的自定义函数（参照 aka.ms/customfunctions 上的指南和示例）。接下来，我将探索另一种 Excel 公式扩展新方式。
Azure 机器学习函数
Microsoft 已发布的第二类可扩展函数为 Azure 机器学习函数（缩写为 Azure ML）。与 JavaScript 自定义函数相比，Azure ML 函数是由 AI 开发人员（通常为数据科学家及其他专家）创建，以供组织内的分析员使用。因此，无需创建加载项包来部署 Azure ML 函数。
Azure ML 函数本身以根据机器学习 (ML) 模型计算或预测值的服务为依据。只要模型生成，创建者就可以为选定的任何人启用它。然后，每当有人要运行此函数，只需在单元格内键入即可，就像使用其他任何 Excel 函数一样。此函数调用公司 Azure 订阅中的实际 Web 服务，并异步返回结果。
例如，某零售商的营销分析员要预测不同地理位置的新产品需求。分析员在 Excel 中有相应产品的一些数据和目标市场。目标是要生成简单函数，以方便分析员在不退出 Excel 的情况下预测各市场的产品需求。图 9 展示了包含此类数据的示例电子表格。
.png "将使用 Azure ML 函数分析的示例产品和市场数据")
图 9：将使用 Azure ML 函数分析的示例产品和市场数据
此组织的 AI 开发人员使用 Azure ML 服务 (bit.ly/2nwa0WP) 创建模型。她先准备定型数据（尤其是来自外部数据库的定型数据），以根据新产品的历史销售情况生成 ML 试验。Azure Machine Learning Workbench 工具不仅简化了数据准备过程，还方便数据科学家编写 Python 代码，从而定型和评估模型。图 10 展示了 Azure ML Workbench 中的“运行”仪表板视图，其中包含试验统计数据。
.png "Azure Machine Learning Workbench")
图 10：Azure Machine Learning Workbench
模型准备就绪后，便可以作为 Web 服务部署到组织的 Azure 订阅中，同时向一组适当的用户提供访问它的权限。元数据是在 Swagger (RESTful API) 格式（自动与已部署的服务一起创建）中定义，而不是在本地运行的 JavaScript 中定义。若要让此服务显示为 Excel 函数，只需让 Excel 分析员有权访问它即可。图 11 展示了数据分析员在 Excel 中看到的已部署函数。然后，分析员可以照常运行此函数。在此服务计算结果的同时，单元格显示“#GETTING_DATA”消息，就像异步 JavaScript 自定义函数一样。
.png "Excel 中显示的已部署函数")
图 11：Excel 中显示的已部署函数
Excel 可扩展函数的未来
我希望大家能够自行尝试 Excel 可扩展函数的预览版技术，即 JavaScript 自定义函数和加载项（若为软件或服务供应商）和 Azure ML 函数（若为 AI 开发人员或数据科学家）。Microsoft 正在积极仔细地聆听预览期间的所有反馈（请将意见发布到 UserVoice 页面 bit.ly/2jRJQsu）。所以，大家除了可以抢先使用产品，还有机会参与最终发布产品的形成。即将推出这两类函数的更多改进，其中一些还是新增功能。所以，请继续关注今后发布的公告！
Michael Saunders 是 Office 团队的项目经理，负责生成面向开发人员的 Excel 功能。他来自加拿大多伦多，毕业于宾夕法尼亚大学沃顿商学院，主修管理与材料科学工程。Saunders 的业余生活是与 Seattle Esoterics 一起唱歌，并开发加载项。**
衷心感谢以下 Microsoft 技术专家对本文的审阅：Yina Arenas、Ashvini Sharma、Sandhya Vankamamidi
Yina Arenas 既是 Microsoft 首席项目经理，也是 Microsoft Graph 负责人。她将 Office 和 Microsoft API 从传统和脱节的技术带到了一个全新、统一的 API 领域。
Ashvini Sharma 是 Excel 团队资深项目经理，负责制定 Excel Analytics 愿景和路线图。
Sandhya Vankamamidi 是信息和内容使用体验团队的高级项目经理。Sandhya 负责跨必应应用系列设计和提供沉浸式体验。