把 Excel 函数用到极致是种怎样的体验？建议收藏

表格和函数

万能公式——INDEX+SMALL+IF+ROW+IFERROR

表单和函数

组合用法1——自动化计算销售提成

组合用法2——根据身份证号码自动算性别

报表和函数

组合用法1——从乱码中提取数字

组合用法2——将数字金额转化成大写中文

天下武功，唯快不破。无论函数在哪，一切只为快。

函数是全部数学概念中最重要的概念之一。从数据清洗到计算再到分析与可视化，都可以见到它的身影：

可见，在数据的各个领域（表格表单、报表、大屏），函数基本都被作为必需配置。

抛砖引玉讲一下：

excel作为典型的表格软件，其函数约有400多个，覆盖数据计算需求的方方面面：

99个财务函数+11个逻辑函数+27个文本函数+49个时间日期函数+16个查找和引用函数+60个数学和三角函数

这么多函数，对于普通excel用户，光理解就耗费半天。

坊间也因此流传了不少万能公式，能够帮我们自如地运用多个函数，更快捷地解决问题。比如下面这个：

能够解决90%的一对多查询问题。

用法：比如要从下图数据中查找"销售1部"的全部数据（标黄部分）：

第一步：判断“销售1部”的行号，通过ROW函数把“销售1部”筛选出来。所选单元格中，符合”销售1部“则输出对应行号，不符则输出”FALSE"。

由于它是一个数组函数，所以点击CTRL+Shift+Enter完成，输出为FALSE,说明与查找目标值不符。

嵌套SMALL函数，按从小到大的顺序取第n个值，返回所有满足条件的对应行号。

用INDEX函数引用所选区域行列的交叉内容，选择区域A3-A10,引用行数3，6，7，用INDEX返回所选行数的内容，即“销售1部”。

完整公式及数据结果如下：

在目前国内软件厂商中，表单做到专业级的较少。

简道云作为早期的apaas原生厂商，以表单为基底，因此经过近些年发展，在公式与函数的应用上，趋于成熟。函数配置更偏向低复杂度，对于普通办公族友好。

19个文本函数+20个日期函数+21个数学函数+6个逻辑函数+7个高级函数：

背景：「销售上报」表单记录了销售员的每日销售数据，到月底财务需要填写「提成表」计算出某一销售员、某一月份的销售额之和，以便计算出每个人的提成数据。

实现步骤：

（1）在「销售上报」表单中添加一个辅助字段，并编辑函数，值为「姓名+销售日期对应的年月」。

在录入数据时，辅助字段也会通过公式得到值，以供「提成表」中的MAPX函数调用。

（2）在「提成表」中同样也需要添加这个辅助字段，并编辑函数，值为「姓名+当前统计的月份」。

（3）对销售总额编辑公式，值为MAPX(‘SUM’,辅助字段,辅助字段,销售额)

（4）对提成计算字段编辑公式，销售总额低于1000提成比例为0.1，超过1000提成比例为0.2

（5）看一下效果：

动图中左边为「销售上报」中的已有数据，右边为「提成表」录入数据的界面。

在填写日期时，任意选择统计月份中的任意一天即可，公式会自动提取出年月。

如何根据身份证号码自动算性别？这里用一个典型的例子来给大家启发。

需要解释的是，身份证号码第17位为性别位，奇数为男性，偶数为女性。

实现步骤：

方法一：身份证的第17位数为奇数的时候，返回“男”，否则返回“女”。

方法二：身份证的第17位数为1,3,5,7,9的时候，返回“男”，否则返回“女”。

最终效果：

关于表单中的函数，组合用法还有很多，不一一列举了，感兴趣进这些专题看：

函数专题 - 简道云 - 案例详解​hc.jiandaoyun.com

公式与函数 - 简道云 - 视频教程​hc.jiandaoyun.com

注：像简道云这样的表单型软件，由于本质是apaas，因此更为偏向数据管理与协同，和流程绑定，从而建立起各种各样的管理应用。

因此与表格及报表软件相比，函数的复杂度上会弱化，但上手迅速和操作的快捷性上，也大大得到提升。

apaas科普：apaas主要是提供一种框架，从应用和数据层面入手，让业务人员不需要学代码就能自己设计出一个管理软件。典型的apaas设计逻辑——在简道云中，通过【表单】实现堆叠搭建，利用【流程工具】将业务点串联起来，借助【仪表盘】进行数据展现与分析。（如图）

国内报表做到专业级的不少，其中finereport作为老牌产品，在数据运算上具有深厚功底，函数类型与复杂度并不弱于excel，内置了200个函数左右，能够支撑大规模数据运算：

由于实在太多，就不展开思维导图了，感兴趣可以查看文档，有对应每个函数的使用规则及介绍：

传送门：https://help.fanruan.com/finereport/doc-view-831.html

我们想从下面这个目标字符串中提取出所有的数字，并重新组合成一个字符串，该如何做呢？

实现思路：先将字符串分割成数组，再对数组每一项进行筛选，保留下数字，最后以字符串输出。

1）先利用 split( ) 函数进行分割，这里要分割每一项，所以分割符为空：=split(目标字符串,"")

2）利用 GREPARRAY( ) 函数对上一步结果进行筛选，这里使用了 regexp( ) 函数来判断是否是数字：=GREPARRAY(上一步结果, regexp(item, "[0-9]"))

3）使用 JOINARRAY( ) 函数将数组以字符串方式输出，拼接符为空：=JOINARRAY(上一步结果, "")

4）综合起来，公式就是=JOINARRAY(GREPARRAY(split(目标字符串, ""), regexp(item, "[0-9]")), "")，

提取步骤如图：

在票据、落款类报表中，为防止随意涂改作假，常需将金额数字转换为大写中文人民币形式，此时可使用 Cnmoney函数，但如果想要直接展示大写金额同时显示小数格式，不展示为人民币形式该如何实现呢？

这种情况下，我们使用NUMTO()、CONCATENATE()、MAPARRAY()等函数组合运用，通过字符转化，替换，拼接等方式，将其转化为中文格式输出即可。

1）新建一张普通报表，右击任意单元格(例如A1单元格)，输入数值1234.123：

2）选中 B1 单元格，输入公式：

由于报表软件更偏向数据分析，因此函数在其中的作用，需要能够快速运算各种数据类型，支撑起较为复杂的图表模型，从而推动一份完整的报表产生：

除了这些以外，函数在大屏软件中的类型及用法也不少，默默无闻为高逼格的数据可视化做出不少贡献，如果点赞多，后面再补充进来。

以上。