Numbers（三）绕不过去的公式

简心渡惑 2016-02-22

展开全文

表格对我们的吸引力，除了其在内容组织上的独特性，很多时候是因为公式和计算的效率，花费一点时间设计好公式以后，接下来的工作只需要填写数据就好了，越是重复性的内容，这种自动化的优势越明显。从最简单的加减乘除到专业的财务计算，从统计计数到字符串的拆分，三角、工程、统计、文本、日期与时间、逻辑与信息，品类繁多的函数让表格变得格外神奇，虽然很多函数我们可能从来都不会接触到，不过这不妨碍我们从现实的工作需求开始，从简单的函数开始为表格加入公式。

加减乘除和常用运算符

因为使用习惯上的变迁，我们还是从最简单的加减乘除开始公式的介绍。

Numbers里只需要在单元格里直接输入等号「=」就可以开始公式的输入，弹出的公式输入框是一个悬浮小窗口，在输入状态下可以随着你一起切换到其他表单、滚动页面到其他表格。公式输入状态下，点击的单元格会自动添加到公式窗口中，如果是简单的加法运算连「+」号都是自动添加，指定好单元格后点击公式窗口的绿色对勾就完成了公式的添加。

公式和单元格数据格式

公式中的单元格编号会以彩色的方式来显示，并与选中状态的单元格一一对应，选中公式中的具体单元格编号它会加深显示的颜色以示区别，对应的表格中的单元格也会以同样的颜色被框选，拖拽单元格角上的控制点可以重新调整选区或位置，如果要去掉这个单元格的引用直接从公式中删除对应的单元格编号即可。

在没有退出公式编辑模式的情况下，点击任何有效的单元格都会被添加到公式窗口中，你可以点击页面空白的地方退出公式编辑模式。

单元格内容会依据单元格面板中「数据格式」的设定来显示，例如，小数点的位数、日期格式还是文本等。被设定成特殊「数据格式」的单元格不能添加公式，例如：星形评分、滑块、步进器、弹出式菜单。

表尾行和常用函数

在单元格中输入「=」进入公式编辑状态后，还可以直接通过工具栏函数图标或者菜单「插入」- 「函数」，插入常用的求和、平均值、最大值、最小值、计数和乘积函数。通常在表格汇总和计数的应用中，我们会将最后一行作为计算结果的显示行，为了格式的统一，需要添加「表尾行」来显示统计或汇总数据。添加表尾行有两种途径：

在「表格面板」中设定「标题与表尾」，将表尾的数值由默认的0设定为1；
直接在表格中选中最后一行右键，选择「转换为表尾行」。

常用运算符号

运算符用于指定要对公式中的元素执行的计算类型。计算时有一个默认的次序，但可以使用括号更改计算次序。输入时请使用英文输入法，运算符号、逗号、引号这些都只支持英文标点。

算术运算符	含义	示例
+（加号）	加法	3+3
–（减号）	减法	3–1
*（星号）	乘法	3*3
/（正斜杠）	除法	3/3
%（百分号）	百分比	20%
^（脱字号）	乘方	3^2

比较运算符	含义	示例
=（等号）	等于	A1=B1
>（大于号）	大于	A1>B1
<（小于号）	小于	A1<B1
>=（大于等于号）	大于或等于	A1>=B1
<=（小于等于号）	小于或等于	A1<=B1
<>（不等号）	不等于	A1<>B1

文本运算符	含义	示例	结果
&（与号）	将两个值连接或串起来产生一个连续的文本	"North"&"wind"	Northwind

公式的设定和复制

公式中引用的数据源会采用彩色来标识并与表格中的数据源颜色一致，函数会以灰色的括号形状来标识函数的语法范围，输入函数时，Numbers会自动提示匹配的函数并显示参数，我们需要做的就是选中具体的参数修改或者为其指定数据源的单元格。

公式的设定

图示中利用DATEDIF函数计算员工的入职年限，用表格中的入职日期和当前的时间分别作为函数的开始日期和结束日期，计算方式选择的年「Y」。为了在函数获得的数值后添加一个「年」的字符串，用到了连接符号「&」和双引号。

退出公式编辑模式，选中已经设定好公式的单元格，拖动黄色控制点就可以向左右或上下复制公式或内容，直接「?C」「?V」同样可以完成公式的复制和粘贴，如果只想粘贴结果而不是公式按「??V」。

公式的拖拽复制

通常情况下，公式中包含的单元格会随着公式的复制进行同步的位移，以便完成对新的单元格的计算。不过实际应用中也有一些情况不希望数据源的单元格随着公式复制发生变化，这个时候可以通过在公式将数据源的单元格的位置进行固定，勾选「保留行」或保留列，如果仅需要在横向上固定勾选「保留行」，如果想在纵向上固定勾选「保留列」。

图示中，因为公式的复制本来就是沿着行进行复制的，所有我们锁定了列的位置后，单元格的编号会变成「$B2」，同时，新的单元格中公式中的「$B2」会保持不变，如果不锁定列，新的单元格的公式应该是「C2+C3」。检查公式的正确与否，通常采用的方式也是选中单元格，然后查看数据引用单元格是否正确。

单个的单元格会以类似「B2」的方式来表示，如果是锁定了位置的单元格，添加$符号「$B$2」来表示。求和函数中可以对一列或一行的数据进行汇总，用「B2:B7」来表示取值的范围，单元格前加上双冒号表示它所属的表格「表格1::B2:B7」。

调试公式时，选中公式中的具体数据源或函数段，底栏会显示当前函数的数值或者单元格数据源的数值，通过这种定位和结果的查看，可以有效的对公式进行分析和判断，在多层嵌套的公式中这种调试策略非常有用。

为了版面上的美观，有时候需要把引用数据的行或列隐藏起来，这种情况下你点选公式中的数据源时可能看不到表格上的反馈，需要把引用表格的隐藏关闭才能看到。

常用函数范例

COUNTIF(待检验阵列, 条件)
统计行或列中包含特定字符串的行或列的数量。
COUNTIF经常用来统计包含特定字符串或数值的数量，例如，员工登记表中的男女比例、统计学历、本月过生日的人员数量等等。函数中的待检验阵列可以是你圈选的列或者行，条件是你要查找的内容。

COUNTIF(员工信息登记表::D1:D6,"男")

COUNTIF-按需统计

实际使用中为了做分析展示，我们会新创建一些小的表格，用COUNTIF从原始数据表中获得想要的数据，然后将它作为图表的数据源来展示结果。

MID(来源字符串, 开始位置, 字符串长度)
MID 函数得出由指定位置开始的字符串值中给定数量的字符构成的字符串值。
范例中通过身份证号获取出生日期多次用到了MID函数，分别从身份编号的不同位数获取数值，然后使用&连接符将数值组装成年月日的最后结果。

MID(身份证号王,7,4)&"-"&MID(身份证号王,11,2)&"-"&MID(身份证号王,13,2)

MID-出生年月获取

18位身份证号码的编码规则：区域地址编码（6位）+出生年（4位）+出生月（2位）+出生日（2位）+数字顺序码（2位）+性别码（1位）+校验码（1位）。

YEAR(日期)
YEAR 函数返回给定日期/时间值或日期字符串的年份。
获取年份，图示中通过YEAR函数嵌套当前时间NOW()获得当前的年份，然后减去身份证中的出生日期获得年龄。同类型的常用时间函数还有TODAY、MONTH、TIME等。

YEAR(NOW())?MID(身份证号王, 7, 4)

YEAR-年龄计算

IF(条件表达式, 为 TRUE 时返回的值, 为 FALSE 时返回的值)
IF 函数得出 TRUE 或 FALSE，具体取决于指定的表达式求得的值是布尔值 TRUE 还是 FALSE。

IF(MOD(MID(身份证号李, 17, 1), 2)=1, "男", "女")

IF-判断性别

在图示中IF用来判断身份证中性别数值的奇偶，用到了MOD函数来除余数，当条件为真显示「男」否则显示「女」。在实际工作中IF还广泛的被用来做容错显示，避免0年12个月这样的内容显示出来，可以用IF来判断，如果年的数据为0就仅显示月。

IF的使用场景中经常会包含多重的嵌套，可以借助底栏的函数结果提示和函数选中后加深颜色的括号来辨识。

LOOKUP(搜索目标, 搜索位置, 结果值)
查找某个集合内与给定搜索值匹配的值，然后在第二个集合内具有相同相对位置的单元格中得出值。

示例是一份人员报销费用的记录表，因为费用科目不完全一致所以每个人员都是一个独立的表，左上角的表头是年度和月份，当切换月份时「月费用汇总」中会显示当月的费用汇总。

LOOKUP($A$1,丁一::B1:M1,丁一::B4:M4)

LOOKUP-数组对应数值获取

使用LOOKUP函数我们可以定义当「搜索目标」是「九月」的时候，从「搜索位置」-「项目」所在的行找到对应的「结果值」-「小计」行中对应的数值。示例中因为要从两个人员的表获取数据，所以使用了两个LOOKUP进行相加。「餐补」的计算中同样使用了LOOKUP函数函数，只不过取值的地方是另一个「工作日」表单，从工作日中获取工作天数然后乘以每天的餐补金额获得结果。

左上角的「九月」采用的单元格数据格式是「弹出式菜单」，这样可以很方便的选择一到十二月的月份，需要注意的是为了保证LOOKUP函数「搜索目标」和「搜索位置」的匹配，在将「九月」的单元格设置成「弹出式菜单」前，应先将单元格定义成日期格式。

SUMIF(待检验的值, 条件, 待求和的值)
SUMIF 函数得出数字集合的和，且仅包括满足指定条件的数字。
Numbers的模板「家居装饰」的产品表单中用到了SUMIF 函数，通过勾选操作来自动计算产品的总金额。

SUMIF(选择,TRUE,D2:D13)

SUMIF-数组匹配求和1

SUMIF 函数的待检验的值和条件一起来形成判断，判断结果匹配时计算所对应位置的数值，另一个SUMIF 函数的范例中（Numbers的个人预算模板），判断条件不再是「真假」，而是是否和条件单元格的内容一致，「预算」表单中的类别和「交易」表单流水记录中的类别是否一致，将符合条件的所有项的金额汇总反应到「预算」表单中。

SUMIF(类别,A2,$总额)