包含212个案例，每个案例都分为疑难场景、解题步骤和知识扩展3部分，其中疑难场景用于描述问题；解题步骤用于阐述解决问题的方法，按步骤先后详细地罗列出来，同时配有操作过程与操作结果的截图，并在图中提供箭头指示操作对象，从而帮助读者快速掌握解题方法；知识扩展用于补充解题方案中部分代码的相关语法、思路，或者提供其他解题方案，使读者对每个步骤了解得更透彻，并可举一反三。
生产线有15个职工，他们的今日日产量数据如图7.126所示。
领导要求计算产量高于平均值的员工占所有员工的比例，应如何计算？
图7.126 日产量表
本例可以使用COUNTIF函数计算高于平均值的人数，然后再用COUNTA函数计算总人数，两者相除即为最终结果。
公式如下：
公式结果为0.466，表示高于平均值的人数占46%左右，效果如图7.127所示。
图7.127 统计结果
1.AVERAGE函数用于计算一个或多个区域的平均值，它有1～255个参数，第一个参数是必选参数，其他所有参数都是可选参数。
2.COUNTIF函数用于计算符合条件的单元格数量，在本例中的条件是大于平均值，当平均值是一个数值时——如500——可以直接使用“＞500”作为条件，当平均值是通过表达式计算而来时，由于表达式不能放在引号中，因此本例只能使用“＂＞＂&AVERAGE（B2：B16）”作为条件，而不是“＂＞AVERAGE（B2：B16）＂”。
3.表达式“COUNTA（B2：B16）”用于计算B2：B16区域的非空单元格数量。
身份证号码中包含了持有人的性别信息，能否在输入身份证号码后让性别自动产生？
身份证号码的倒数第2位数字代表性别，该值是奇数时代表持有人的性别为男，否则为女。基于此原则，利用MID函数提取身份证号码中的第17位数，然后使用MOD函数判断该值的奇偶性即可。以图7.124为例，获取性别的操作步骤如下。
图7.124 职工信息表
1.在C2单元格录入以下公式：
2.双击C2单元格，使公式自动向下填充，生成的性别如图7.125所示。
图7.125 批量提取性别
1.表达式“MID（B2，17，1）”用于提取身份证号码中的第 17 位数字，MOD 函数则用于计算此数字除以2的余数，余数只有0或1两种情况，当余数是1时，IF函数的第一参数返回True，表示IF函数的条件成立，因此公式“=IF（MOD（MID（B2，17，1），2），＂男＂，＂女＂）”的最终结果为“男”。
2.我国的身份证号码最先使用的是15位数字，然后15位和18位并存，现在已经只有18位的身份证号码，因此不需要再考虑15位的问题。
3.当多个函数嵌套时，公式中会有多级括号，Excel总是从里层括号向外开始运算，因此理解公式的含义时也应该从里向外。对于同一层级的表达式，则遵循四则混合运算的顺序，同级别的先左后右，加、减属于同级别运算，乘、除属于同级别运算，但高于加、减运算。
要进入某个工作表，需要找到这个工作表并单击工作表名称。当工作簿中有太多的工作表时，查找工作表相当费时，因为Excel的工作表名称不支持搜索功能。
是否可以新建一个名为“目录”的工作表，然后在此表中创建所有工作表的目录呢？
Excel的一切工作表函数都无法生成活动工作簿中的所有工作表名称，而比较古老的宏表函数GET.WORKBOOK却可以，因此本例借用GET.WORKBOOK实现需求，具体操作步骤如下。
1.打开需要创建目录的工作簿，然后新建一个工作表，且将该表移到最前面，并命名为“目录”，此时工作表分布状态如图7.120所示。
图7.120 新建“目录”工作表
2.选择A1单元格，按下组合键＜Ctrl+Shift+Enter＞，打开“名称管理器”，然后单击“新建”按钮，弹出“新建名称”对话框。
3.将名称设置为“第N个工作表”，将引用位置设置为以下公式：
设置界面如图7.121所示。
图7.121 创建名称
4.单击“确定”按钮保存设置，然后返回工作表界面。
5.在A1单元格输入以下公式：
6.将公式向下填充，有多少个工作表就填充多少个单元格，填充后会产生活动工作簿中的所有工作表目录，效果如图7.122所示。
图7.122 工作表目录
7.单击A6单元格，Excel会马上激活“财务表”，且选中“财务表”中的A1单元格。
8.返回“目录”工作表，单击A7单元格，Excel会马上激活“出货表”，且选中“出货表”中的A1单元格。
1.宏表函数GET.WORKBOOK的功能是获取与工作簿相关的约一百项信息，当参数的值为1时表示获取所有工作表的名称。
由于表达式“GET.WORKBOOK（1）”可以生成一组名称，包含所有工作表，因此要取得单个名称就需要借用INDEX函数。
本例中的公式“=INDEX（GET.WORKBOOK（1），ROW（A1））”在向下填充时，ROW的参数A1会变成A2、A3……ROW（A1）也因此会生成1、2、3这种序号，所以整个公式的结果就是第一个工作表名称、第二个工作表名称、第三个工作表名称……当填充到超过工作表数量时会产生错误值。
2.宏表函数不能直接写在单元格中，只能在名称中调用宏表函数，然后在单元格中调用名称。
3.当工作簿中使用了宏表函数后，此工作簿不能保存为xlsx格式。
4.HYPERLINK 是链接函数，用于打开指定的文件、网站或激活指定的单元格，本例中用它激活每个工作表的A1单元格，从而实现目录功能。HYPERLINK函数的语法如下：
HYPERLINK(link_location,friendly_name)
第一参数代表链接对象，必须遵循严格的命名方式；第二参数代表要显示的字符，可以随意指定。本例中名称“第N个工作表”产生的结果是“[疑难170.xlsm]生产表”这种形式的，不符合单元格引用规则，因此需要在后方添加叹号和单元格地址。
5.目录中显示了工作簿名称，如果只需要工作表名称，那么可以使用FIND函数查找“]”的位置，然后用MID函数提取该位置右边的所有字符，具体的公式如下：
=HYPERLINK（第N个工作表&＂！a1＂，MID（第N个工作表，FIND（＂]＂，第N个工作表）+1，99））此公式生成的目录效果如图7.123所示。
图7.123 不包含工作簿名称的目录
图7.117中部分队员参与了多项比赛，现要求对队员编号。当一个队员参与了多项比赛时，分别编号为1、2、3……第二个队员又重新从1开始编号。
图7.117 参赛队员信息表
本例宜用COUNTIF函数解题，具体操作步骤如下。
1.在A2单元格输入以下公式：
2.双击A2单元格，使公式向下填充到底，公式产生的编号如图7.118所示。
图7.118 公式生成的编号
1.COUNTIF函数用于计算符合条件的单元格数量，此函数在以前的案例中多次用到。本例的重点在于相对引用的数据源区域“$B$2：B2”，将公式向下填充时引用对象在逐个增加，因此在数据源中符合条件的单元格数量也相应地增加。
当公式在A2时，数据源区域是B2：B2，因此符合条件的单元格只有一个；当公式在A3时，数据源区域是B2：B3，因此符合条件的单元格有两个；当公式在A4时，数据源区域是B2：B4，因此符合条件的单元格有3个；当公式在A5时，数据源区域是B2：B5，此时条件已经变成B5，符合条件的单元格只有B5一个单元格，因此编号重新从1开始，而不是4。
2.假设要求按人数生成编号，即同一人不管参加多少个项目都使用相同的编号，那么应该改用以下公式：
公式的含义在前面的案例中有详细说明，公式的结果如图7.119所示。
图7.119 按人数生成的编号
图7.115中B列是职员的身份证号码，由于身份证号码中包含了号码所有者的出生日期，因此要求利用身份证号码计算身份证持有人的年龄。
图7.115 职员信息表
身份证号码中的第7位到第14位属于出生日期，因此使用MID函数从身份证号码中提取生日，然后用它与今天的日期比较就得到年龄了。具体操作步骤如下。
1.在C2单元格中输入以下公式：
2.双击C2单元格的填充柄，从而使公式自动向下填充，公式的计算结果如图7.116所示。
图7.116 根据身份证号码计算年龄
1.身份证号码的前6位代表地区，地区右边的8位代表出生年月日，因此本例采用MID函数从身份证的第7位开始，一共提取7位数字出来，然后通过TEXT函数转换成日期样式。
例如，第一个身份证号的生日是19770316，使用TEXT函数转换后的结果为“1977-03-16”。如果不转换格式，DATEDIF函数无法识别此日期。
2.TODAY函数用于生成今天的日期，没有参数。
3.DATEDIF 函数用于计算两个日期之间的间隔时间，单位可以是年，也可以是月或天。DATEDIF函数的语法如下：
第一参数代表起始日期，第二参数代表结束日期，第三参数用于指定日期单位。
第三参数的取值范围与含义见表7.30
表7.3 unit参数说明
表7.3中说明了DATEDIF函数第三参数的6种用法，实际常用的只有第一种和第二种。
=DATEDIF（＂2012-3-1＂，＂2016-4-2＂，＂Y＂）——计算结果4，代表两个日期相差4年；
=DATEDIF（＂2012-3-1＂，＂2016-4-2＂，＂M＂）——计算结果49，代表两个日期相差49个月；
=DATEDIF（＂2012-3-1＂，＂2016-4-2＂，＂YD＂）——计算结果32，代表两个日期相差32天，忽略年，相当于只计算3月1日到4月2日之间的日期差
在本例中，DATEDIF的作用在于计算身份证号中的日期与今天之间的年份差异。
在图7.112中，省名所在单元格已经合并，现要求按省名对该省的参赛队员编号，其中第一个省编号A，第一个队员编号A1，然后向上递增。第二个省的第二个队员则编号为B2。
图7.112 参赛选手信息表
要根据省名数量产生升序的字母，重点在于COUNTA和CHAR两个函数的应用，而累加字母右边的数字编号则重点在于MID函数的应用。具体操作步骤如下。
1.在A2单元格输入以下公式：
2.将公式向下填充到A15，公式产生的编号如图7.113所示。
图7.113 双条件编号
1.COUNTA函数用于计算区域中非空单元格的数量，以图7.113为例，“=COUNTA（B2：B2）”、“=COUNTA（B2：B3）”、“=COUNTA（B2：B4）”的计算结果都是 1，因为合并区域中只有左上角单元格才有数据。
“=COUNTA（B2：B6）”、“=COUNTA（B2：B7）”、“=COUNTA（B2：B8）”的计算结果则为2，因为它的计算对象包含两个合并区域。
2.CHAR 函数可以根据字符编码生成对应的字符，如 49886 代表“罗”，那么公式“=CHAR（49886）”的运算结果就是“罗”。
字母A的编码是65，字母B的编号是66，字母C的编码是6……基于此规律，只要使用65开头的自然数编码作参数，CHAR函数就可以生成A、B、C这种升序的序号。图7.114中展示了表达式“64+COUNTA（$B$2：B2）”在不同单元格的计算结果变化过程，读者可以根据图 7.114理解字母编号的生成规律。
图7.114 CHAR函数的参数变化过程
3.由于公式从 A2 开始，因此表达式“IF（B2＜＞＂＂，1，MID（A1，2，2）+1）”的含义是：当公式所在单元格的右边单元格非空时，那么产生字符1，否则提取上方单元格字母以外的数字并累加1。由于本例的数据源少，不需要用到两位字母，因此在设置公式时可以取巧，用 MID 函数从第 2位开始取值，提取长度也等于2位。在实际工作中需要根据数据的变化修改公式。
图7.110中A1：F21区域包含了10个业务员在4个季度中的销量和退货数量，现要求计算所有人员的销量总和及退货量总和。
图7.110 销量与退货表
本例中数据的规律是：所有销量存放在偶数行，所有退货存放在奇数行。因此使用IF+MOD组合区分奇数行与偶数行，再用SUM函数求和即可，操作步骤如下。
1.在I1单元格输入以下公式：
2.在I2单元格输入以下公式：
以上两个公式都是数组公式，必须输入公式后按组合键＜Ctrl+Shift+Enter＞结束，否则无法得到正确结果。图7.111是以上两个公式的计算结果，偶数行的数据汇总后等于43623，奇数行的数据汇总结果为3293。
图7.111 统计总销量和退货数量
1.MOD函数用于计算一个数值除以另一个数值后的余数，当除数是2时，它用于判断被除数是奇数还是偶数。MOD函数的语法如下：
其中第一参数是被除数，第二参数是除数，计算结果是余数。
=MOD（154，3）——154/3的商为51，余数为1，因此公式的结果为1；
=MOD（154，2）——154/2的商为77，余数为0，因此公式的结果为0；
=IF（MOD（A1，2）=0，＂偶数＂，＂奇数＂）——当A1是偶数时，它除以2的余数为0，那么此时公式的计算结果为“偶数”，否则计算结果为“奇数”。
如果MOD的第一参数包含多个值，那么公式的计算结果也包含多个值。
2.公式“=SUM（IF（MOD（ROW（C2：F21），2）=0，C2：F21））”的含义是：如果C2：F21区域中某个单元格的行号是偶数，那么对这个单元格的值汇总。
3.假设要对偶数列的数据求和，那么将公式中的ROW函数替换成COLUMN函数即可。
图7.105中A列的姓名处于合并单元格中，能否在A列前面插入一列，然后采用与姓名列相同的合并方式，并在合并单元格中按姓名编号？
图7.105 成绩表
合并单元格无法填充公式，因此只能选中所有合并单元格，然后一次性输入公式。编写公式有很多种思路，本例展示MAX函数实现需求的思路，步骤如下。
1.选择B列并单击右键，从右键菜单中选择“插入”。
2.将B列复制到A列，然后删除A列的值，此时A列的合并单元格与B列的合并方式完全一致，效果如图7.106所示。
3.在A1中输入“编号”。
4.选择A2：A14，然后输入以下公式：
输入公式后必须按组合键＜Ctrl+Enter＞结束，公式产生的编号如图7.107所示。
图7.106 插入空列并按B列的方式合并
图7.107 在合并单元格生成编号
1.MAX函数用于计算一个或多个区域的最大值，它有1～255个参数，其中第1个参数必须输入，第2～255个参数是可选参数，可以忽略不写。函数的具体语法如下：
其中第2～255个参数可以忽略。
MAX函数提取最大值时会所略文本，但无法忽略错误值。在图7.108中，A2单元格是文本，公式“=MAX（A1：A3）”可以忽略A2的值，从其他单元格中提取最大值10。假设A2单元格中有公式“=0/0”，那么公式“=MAX（A1：A3）”也只能得到错误值＃DIV/0！。
图7.108 使用MAX函数提取区域中的最大值
2.本例公式“=MAX（$A$1：A1）+1”中MAX函数的参数使用“$A$1：A1”，它代表A列第一行到公式所在行的上一行之间的区域，当公式在A2时计算A1的最大值，公式在A5时计算A1：A4的最大值，公式在A9时计算A1：A8的最大值。
当公式在A2时，由于A1单元格只有文本，表达式“MAX（$A$1：A1）”的计算结果是0，因此在MAX后加1从而使公式产生编号1；当公式在A5时，MAX函数从A1：A4中提取到的最大值是1，此时加1后将得到2…因此公式从上向下会依次产生1、2、3这类递增的序号。
3.本例的公式其实是以A1作为辅助单元格来实现需求的，假设没有标题行，公式要从A1开始书写，那么由于没有了辅助单元格，本例公式也就不再适用。
如果不用辅助单元格，可以改用图7.109中的公式来完成。
图7.109 使用COUNTA函数生成编号
COUNTA函数用于计算非空单元格的数量，参数“$B$1：B1”锁定了起始单元格，终止单元格由公式所在行决定，因此COUNTA函数的计算结果也从1开始相应地递增，从而产生升序的编号。
4.本例还可以将公式替换为“=COUNT（$A$1：A1）+1”，同样用A1作为辅助单元格的思路达成需求。
图7.103是7月份的日产量表，由于领导怀疑周六职工的情绪会影响产量，现要求制表人员用每周六的产量平均值与其他时间的平均值比较，查看本月周六的平均值比其他时间的平均值低多少。
图7.103 本月产量表
数据源中只有日期，而且未标示每一个日期属于周几，因此需要使用TEXT函数计算每一天属于周几，然后使用IF函数搭配AVERAGE函数分别计算两者的平均值，最后两者相减即可，具体步骤如下。
1.在D1中输入文本“差值”。
2.在C2中输入以下公式：
输入公式后按组合键＜Ctrl+Shift+Enter＞结束，结果为-40.3241，表明周六的平均产量比其他时间的平均产量低40左右，效果如图7.104所示。
图7.104 计算周六和其他时间的产量差值
1.TEXT 函数属于格式化函数，能将一种格式的数字转换成另一种格式的数字，当 TEXT函数的第二参数是“AAA”时，表示将日期转换成简化的星期格式。例如，2015年10月1日是星期四，那么公式“=TEXT（＂2015/10/1＂，＂AAA＂）”的计算结果就是“四”。如果将第二参数修改为“AAAA”，公式的计算结果就是完整的星期——会显示为“星期四”。
对于英文版本的系统，使用“AAA”或“AAAA”都不生效，英文版Excel只能使用“DDD”或“DDDD”作为TEXT函数的参数，它们用于获取英文的星期。
=TEXT（＂2016-2-28＂，＂DDDD＂）——计算结果为“Sunday”，如果将“DDDD”修改为“AAAA”则会得到“星期日”。
2.“=AVERAGE（IF（TEXT（A2：A32，＂AAA＂）=＂六＂，B2：B32））”的含义是如果 A2：A32 的日期属于星期六，那么用B2：B32区域中对应的单元格参与求平均。本例中A5、A12、A19和A26的日期是星期六，因此实际参与求平均的值位于B2、B12、B9和B26单元格中，平均值为817.75。
相应地，表达式“AVERAGE（IF（TEXT（A2：A32，＂AAA＂）＜＞＂六＂，B2：B32））”表示A2：A32区域中不等于星期六时，使用C列中对应的单元格参与求平均，其平均值为858.074074074074。
3.本例属于数组公式，必须按＜Ctrl+Shift+Enter＞组合键的方式输入公式才能取得正确结果。
4.本例的公式不需要向任何方向填充，因此公式中的一切引用既可以使用相对引用也可以使用绝对引用。
图 7.99 中，C 列是生产车间今日正在生产的所有型体名称，部分型体在多台机上生产，因此型体名称存在重复值。现要求计算今日同时在生产的型体名称有多少个。
图7.99 生产表
计算一列中的数据个数（忽略重复值）有很多方法，可以使用删除重复项工具去除重复值，或使用高级筛选提取唯一值，然后计算去重后的唯一值数据个数，也可以使用公式计算一列中的不重复数据个数。使用公式达成需求的优点在于数据源变化时计算结果也相应地变化，其他方法则必须在数据源更新后重新操作一遍。
计算唯一值数据个数的公式如下，效果见图7.100。
图7.100 使用公式计算唯一值数量
1.COUNTIF函数的功能是计算符合条件的单元格数量，第一参数是计算目标，第二参数是条件。当第二参数包含多个条件时函数的计算结果也会有多个。
在本例中，COUNTIF 函数的第二参数是 C2：C13，包含 12 个值，因此表达式“COUNTIF（C2：C13，C2：C13）”会产生12个计算结果，对应12个字符串的出现次数。
图 7.101 中，A1：C12 包含 12 个产品的名称，选择 B1：B12 后输入公 式“=COUNTIF（A1：A12，A1：A12）”并按组合键＜Ctrl+Shift+Enter＞结束，公式结果包含2、1、2、1、2、1、2、1、1、2、1、2等12个结果，其中1代表此单元格的值仅在A1：A12区域中出现一次，2则代表出现过两次，如A1和A7都有压线钳，因此B1和B7的值为2。
图7.101 统计每个单元格的值的出现次数
2.“COUNTIF（C2：C13，C2：C13））”用于计 算每个单 元格值的 出现次数，而“=SUMPRODUCT（1/COUNTIF（C2：C13，C2：C13））”则用于计算C2：C13区域的唯一值数量。某个数据的出现次数是几，“COUNTIF（C2：C13，C2：C13））”的计算结果中就会有几个几。例如，某单元格的值出现了3次，那么“COUNTIF（C2：C13，C2：C13））”的计算结果中就会有3个3，再如，某单元格的值出现了5次，那么“COUNTIF（C2：C13，C2：C13））”的计算结果中就会有5个5……基于此规律，将它的倒数求和，得到的结果就刚好是不重复值的数量。
举一个简单的例子，A列有1个1、2个2、4个4，在B1中输入公式“=1/a1”从而计算其倒数（见图7.102），将公式向下填充后可以得到1个一分之一、2个二分之一和4个四分之一，使用SUM函数将它们求和后的结果为3，而A列的不重复数据个数也刚好等于3，这印证了求倒数后再汇总可以得到不重复数据个数的思路是正确的。
图7.102 对倒数求和
3.SUMPRODUCT 函数在本例中用于替换 SUM 函数，本例使用 SUM 和 SUMPRODUCT函数求和都能得到正确结果，但是使用 SUM 时必须按组合键＜Ctrl+Shift+Enter＞，而使用SUMPRODUCT不需要按此三键，相比而言更方便。