相册

规划求解只能设定一个目标单元格，而此处的方程有两个，因此需要在下方继续添加约束条件。

8.单击右方的“添加”按钮，然后在对话框中按图6.77所示的方式设置条件，并单击“确定”按钮返回“规划求解参数”对话框。此条件表示B2单元格中公式的结果变成14时才停止求解。

图6.77 添加约束条件

9.在“规划求解参数”对话框中单击“求解”按钮，此时Excel会弹出如图6.78所示的对话框，保存默认设置不变，单击“确定”按钮关闭即可，在工作表的A1和A2中将看到方程组的解，其中X的值为4，Y的值为6，效果如图6.79所示。

图6.78 提示求解的结果

图6.79 方程组的解

知识扩展

1.规划求解工具是数据分析时常用的工具，但默认状态下并没有安装在 Excel 中，需要在“加载项”对话框中勾选组件名称后才能使用。

2.规划求解的可用范围很大，解方程只是多种应用之一。不过当方程有多组解时，规划求解只会找出一组解。

3.在求解时，可以设定方程的解只能是整数，也可以设定方程的解既包含整数又包含小数。假设本例中要求方程的解只能是整数，那么可以在“规划求解参数”对话框中单击“添加”按钮，弹出“添加约束”对话框，然后按图6.80所示的方式设置选项，参数int表示A1只能是整数。接着再用相同的方式将A2也设置为整数，最后单击“求解”按钮，得到的解将不再包含小数。

图6.80 设置A1只能是整数

4.如果需要解一元一次方程，规划求解工具和单变量求解工具都可以完成，不过由于一元一次方程实在太过于简单，本书不再展示求解过程。

图6.70中包含6个车间的职工产量数据，现要求将6个工作表的产量和不良品都合并到“汇总”表中。

解题步骤

Excel提供了合并计算工具，可以用它对任意工作表的相同区域执行合并，合并时既可以选择求和，也可以选择求平均、求最大值/最小值等，本例展示求和过程，具体步骤如下。

1.进入“汇总”工作表，选择A1单元格。

2.单击功能区的“数据”→“合并计算”，弹出“合并计算”对话框。

3.在对话框中将函数保持为默认的“求和”，然后单击对话框中间的浏览按钮（图标为），选择1月工作表中的A1：C11区域，最后单击“添加”按钮，将区域地址添加到下方的列表中，效果如图6.71所示。

4.继续添加其他5个工作表的A1：C11区域到列表中，且选择下方的“首行”、“最左列”复选框。图6.71和图6.72是分别添加1个和6个引用位置后的设置界面。

5.单击“确定”按钮后，在“汇总”工作表中会产生图6.73所示的合并结果。

知识扩展

1.合并计算工具用于计算比较规则的数据，应该多个工作表的格式一致，数据和文本的分布方式也一致，否则合并就没有意义了。

2.对话框中的“标签位置”用于控制合并时的行标题与列标题，标题不参与合并（指求和、求平均或最大值/最小值等运算）。其中“首行”代表列标题，最左列代表行标题。

3.“合并计算”对话框中的“指向源数据的链接”用于控制合并计算结果是否跟随数据源相应地更新，当此项目选中时表示数据源更新时合并结果也相应地更新。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

疑难场景

这是一个二元一次方程组，能否在不用公式的前提下得到方程组的解？

解题步骤

Excel的规划求解工具可以解多元方程，不过默认状态下无法调用此工具，需要安装后才能使用。安装并解题的具体步骤如下。

1.按组合键＜Alt+T+I＞，打开如图6.74所示的“加载宏”管理器。

2.选择“规划求解加载项”复选框，然后单击“确定”按钮保存设置，此时打开“数据”选项卡，可以看到“规划求解”菜单，其界面如图6.75所示。

3.假设A1单元格代表X，A2单元格代表Y，那么在B1单元格输入公式“=A1^2+A2^2”，此公式用于表明第一个方程中X和Y的关系。

4.继续在B2单元格中输入公式“=3*A2-A1”，此公式用于表明第二个方程中X和Y的关系。

5.单击功能区的“数据”→“规划求解”，弹出“规划求解参数”对话框。

6.在“设定目标”右方输入 B1，然后选择下方的“目标值”复选框，且在右方输入 52，表示B1单元格的公式的计算结果是52时才停止求解。

7.将“通过更改可变单元格”设置为A1：A2，表示通过不断改变A1和A2单元格的值来测试方程的解，直到找到目标后停止。设置界面如图6.76所示。

设置目标和可变单元格

图6.65中C列是职工的每日产量，领导要求标示出800～1000之间的产量，如何才能一次性标示完成？

解题步骤

只标示产量所在单元格而非标示符合条件的产量所在行，此需求比较简单，操作也相对简单，步骤如下。

1.选择C2：C11区域。

2.单击功能区的“开始”→“条件格式”→“突出显示单元格规则”→“介于”，弹出“介于”对话框。

3.在对话框中将数值范围设置为800到1000，格式则保持原来的“浅红填充色深红文本”不变，单击“确定”按钮即可应用此条件格式。图6.66为条件格式设置界面，图6.67为应用条件格式后的效果。

图6.66 设置条件与格式

图6.67 应用条件格式后的效果

知识扩展

1.介于800到1000之间，其实是包含了800和1000。

2.如果要将产量所在行突出显示，那么应该选择A2：E11，然后单击功能区的“开始”→“条件格式”→“新建规则”，在弹出的对话框中选择“使用公式确定要设置格式的单元格”，并输入公式“=AND（$C2＞=800，$C2＜=1000）”，最后设置格式为红色背景。图6.68是条件格式设置界面，图6.69则为应用条件格式后的效果。

图6.68 设置条件格式

图6.69 应用条件格式后的效果

公式“=AND（$C2＞=800，$C2＜=1000）”表示若C列的值大于等于800且小于等于1000，那么就填充红色背景。其中C2前面必须添加绝对符号$。

公司每月都会为过生日的员工发放小蛋糕，现要求标示出图 6.61 所示的表中下个月将要过生日的职工。

图6.61 职工信息表

解题步骤

Excel的条件格式提供了诸多内置的日期条件，单击即可标示下月日期。不过使用内置条件只能标示日期所在单元格，若要将整行数据都进行标示，则需要使用公式自定义条件。本例同时讲解两种操作过程。

方法一的步骤如下。

1.选择D2：D16区域.

2.单击功能区的“开始”→“条件格式”→“突出显示单元格规则”→“发生日期”，弹出“发生日期”对话框。

3.在对话框中将日期设置为“下个月”，格式则保持内置格式不变，此时可以从工作表中预览条件格式的效果，设置界面和预览效果如图6.62所示。

图6.62 指定条件和格式

4.单击“确定”按钮应用设置好的条件格式。

备注：笔者编写此案例时是 2015 年 11 月，因此工作表中标示的 3 个下月日期都是 2015年12月。

方法二的步骤如下。

1.选择D2：D16区域。

2.单击功能区的“开始”→“条件格式”→“新建规则”，弹出“新建格式规则”对话框。

3.将规则类型设置为“使用公式确定要设置格式的单元格”，然后在下方在的文本框中输入以下公式：

设置界面如图6.63所示。

图6.63 设置条件

4.单击“格式”按钮，弹出“设置单元格格式”对话框，然后打开“填充”选项卡，将背景色设置为红色，然后单击“确定”按钮保存设置。图6.64是应用条件格式后的效果。

图6.64 应用条件格式后的效果

知识扩展

1.方法一仅标示选区中符合条件的日期所在单元格，方法二是可以标示选区中符合条件的日期所在行，两者效果差异较大，操作复杂度也差异较大。

2.公式“=AND（$D2＞EOMONTH（TODAY（），0），$D2＜=EOMONTH（TODAY（），1））”的含义是：如果D列的日期大于本月最后一天，而且小于等于下月最后一天，那么返回True。

其中TODAY函数用于产生今日的日期，“EOMONTH（TODAY（），0）”则用于生成本月最后一天的日期，“EOMONTH（TODAY（），1）”用于生成下月最后一天的日期。参数0和1分别代表今天之后的0个月或1月，此参数允许使用负数。例如，赋值为-2表示返回2个月前的当月最后一天。

3.在设置公式时，引用对象一定要用“$D2”，不能使用“D2”或“$D$2”。

图6.57中包含10人的各季度销量数据，要求将每一行的最小值标示出来，如何才能一次性标示完成？

解题步骤

与标示两列数据的相同项一样，标示区域中的最小值要使用公式来确定目标，具体操作步骤如下。

1.选择B2：E11区域。

2.单击功能区的“开始”→“条件格式”→“新建规则”，弹出“新建格式规则”对话框。

3.选择“使用公式确定要设置格式的单元格”，然后在下方的文件框中输入公式“=B2=MIN（$B2：$E2）”，操作界面如图6.58所示。

图6.58 设置条件

4.单击“格式”按钮，弹出“设置单元格格式”对话框，然后打开“填充”选项卡，将背景色设置为红色，然后单击“确定”按钮保存设置。图6.59为格式设置界面，图6.60则是应用条件格式后的效果。

图6.59 设置格式

图6.60 条件格式的执行效果

知识扩展

1.公式“=B2=MIN（$B2：$E2）”表示当任意单元格的值等于它所在行的最小值时就符合条件，立即应用后面设置的格式。由于 MIN（$B2：$E2）总是计算单行的最小值，因此标示单元格时会每一行标示一次。

2.如果要标示B2：E11区域中的最小值，那么应该将公式修改为“=B2=MIN（$B$2：$E$11）”。

3.通过条件格式标示目标值会一直保留下来，重新打开工作簿后仍然能看到条件格式的显示结果，而采用数据验证的圆圈标示目标则只能用于临时查看，重启Excel后会自动消失。

图6.53是包含去年与今年成绩前10名的名单，现要求将两列名单中的相同值用红色标示出来。

图6.53 今年和去年的前十名名单

解题步骤

将目标单元格标示颜色的最佳工具是条件格式，具体操作步骤如下。

1.选择A2：C11区域。

2.单击功能区的“开始”→“条件格式”→“新建规则”，弹出“新建格式规则”对话框。

3.选择“使用公式确定要设置格式的单元格”，然后在下方的文本框中输入公式“=AND（ISTEXT（A2），COUNTIF（$A$2：$C$11，A2）＞1）”，设置界面如图6.54所示。

4.单击“格式”按钮，弹出“设置单元格格式”对话框，然后打开“填充”选项卡，将背景色设置为红色，然后单击“确定”按钮保存设置，图6.55为设置界面。

图6.54 设置条件

图6.55 设置格式

5.单击“确定”按钮返回工作表界面，此时工作表中A列与C列的相同值所在单元格都会显示为红色背景，而成绩相同时却不会标示出来，效果如图6.56所示。

图6.56 条件格式的执行效果

知识扩展

1.本例中选择A2：C11后再设置条件格式是因为去年与今年的学生姓名都刚好容纳于此区域中，尽管选择A2：D11区域再设置条件格式也能实现同样的功能，但是设置条件格式时应该遵循一个规则：由于条件格式本身比较耗内存，因此应该对尽可能小的区域设置条件格式，非必要的区域应该忽略。

2.公式“=AND（ISTEXT（A2），COUNTIF（$A$2：$C$11，A2）＞1）”表示如果A2：C11区域中某个单元格是文本，而且在这个区域中出现的次数大于1次，那么它就满足条件。

表达式“ISTEXT（A2）”用于判断单元格中的值是否为文本，“COUNTIF（$A$2：$C$11，A2）＞1”用于判断单元格的值出现的次数，最后用AND函数来判断是否两个条件同时满足，只有同时满足两个条件才添加背景色，从而忽略重复的成绩。

3.本例还有另一种操作方法：分两次设置条件格式，一次对 A2：A11 区域设置条件格式，一 次 对 C2：C11 区 域 设 置 条 件 格 式。对 A2：C11 区 域 设 置 条 件 格 式 时，用“=COUNTIF（$C$2：$C$11，A2）＞0” 作 为 条 件，对 C2：C11 区 域 设 置 条 件 时，用“=COUNTIF（$A$2：$A$11，C2）＞0”作为条件。