Excel数据处理与分析实战精粹

Excel数据处理与分析实战技巧精粹1、使用工作表保护：1）选定全部允许访问单元格，单击菜单［格式］—［单元格］；2）在打开“单元格格式”对话框“保护”选项卡中取消勾选“锁定”复选框，然后单击“确定”按钮；3）单击菜单［工具］—［保护］—［保护工作表］，在打开“保护工作表”对话框中取消勾选“选定锁定单元格”复选框；4）假如需要，能够加上工作表保护密码并确认密码，最终单击“确定”按钮即可；5）此时在工作表中只有未锁定单元格能够被选中和编辑，而其余单元格都会受到Excel保护，无法被选中更无法进行编辑。

注意：也能够把你需要隐藏文字设置成白色，然后再进行以上工作表保护，那他人既看不到你内容也没方法修改了。

2、标题行重复打印：1）单击菜单［文件］—［页面设置］，在打开“页面设置”对话框中单击“工作表”选项卡；2）假如要指定在顶部重复一行或连续几行，则可单击“顶端标题行”文本框最右边按钮，然后在工作表中进行对应指定；3）假如要指定在左侧重复一行或连续几行，则可单击“左端标题列”文本框最右边按钮，然后在工作表中进行对应指定，以下列图所表示；4）单击“确定”按钮关闭“页面设置”对话框。

3、巧用右键和双击填充：假如用户先输入内容到一个单元格，然后用鼠标右键拖曳此单元格，那么松开右键，就会出现一个快捷菜单，菜单上显示了此次填充能够选取类型，甚至能够链接到“序列”对话框进行更复杂设置，以下列图所表示：

当用户在数据区域中对某一列内容进行填充时，无须从第一个单元格一直拖曳到最终一个单元格，只需要双击第一个单元格填充柄即可。不过使用双击方法进行填充时候，填充到最终一个单元格位置则取决于左边一列中第一个空白单元格位置（假如填充列是第一列，则参考右边列中单元格）。比如在下列图中因为A5是空白单元格，所以B列中填充只进行到B4单元格就会停顿。

4、快速输入对号、错号、平方与立方：下面数字均必须在小键盘上输入。名称快捷键名称快捷键名称快捷键名称快捷键对号ALT+41420错号ALT+41409平方ALT+178立方ALT+1795、神奇选择性粘贴：1）全部：在绝大多数情况下等效于常规粘贴；2）公式：只复制原始区域公式；3）数值：只复制数值，假如原始区域是公式，则只复制公式计算结果；4）格式：只复制原始区域格式；5）批注：只复制原始区域批注；6）有效性验证：只复制原始区域中设置数字有效性；7）边框除外：复制边框之外全部内容；8）列宽：从一列到另一列复制列宽信息；9）跳过空单元格：能够有效地预防原始区域中空单元格覆盖粘贴目标区域中单元格内容；10）转置：能够让原始区域在复制后行列交换；11）粘贴链接：将建立一个由公式组成连接原始区域动态链接；12）加：允许用户进行一次简单数值运算。比如：选定任意一个空白单元格，然后按<Ctrl+C>组合键进行复制，按住<Ctrl>键单击全部需要取消超链接单元格。假如是一个单元格区域，能够先用鼠标单击并按住最外面单元格，然后向上拖动选定整个区域，待光标由手形状转变为十字形然后再松开鼠标按键，单击菜单［编辑］—［选择性粘贴］，在打开“选择性粘贴”对话框中选中“加”单项选择按钮，然后单击“确定”按钮，这么全部被选定超链接就都转换为普通文本了，以下列图所表示。

6、创建动态名称：假如需要创建一个名称来引用C列中数据，但又不希望这个引用区域包含空白单元格，在这种情况下能够创建动态名称，依照用户追加或删除数据结果来自动地调整引用位置，以达成一直引用非空白单元格区域结果。创建动态名称方法以下：1）单击菜单［插入］—［名称］—［定义］；2）打开“定义名称”对话框，在“在当前工作薄中名称”文本框中输入“Date”，在“引用位置”文本框中输入公式：=OFFSET(Sheet1!$C$4,,,COUNTA(Sheet1!$C:$C)-1)，以下列图所表示，单击“确定”按钮。

以上公式先计算C列中除了列标题以外非空白单元格数量，然后以C4单元格（首个数据单元格）为基准开始向下定位，定位行数等于刚才计算出来数据。下面能够在C列以外单元格中经过计算来验证此名称引用是否正确，比如在B1中输入公式：=SUM(Date)，以下列图所表示。

假如继续追加统计，名称“Date”引用位置就会自动地发生改变，B2中计算结果能够表现这一点，以下列图所表示。

注意：以上公式只能正确计算不间断连续数据，假如表格中数据有空白单元格，那么动态名称引用位置将发生错误。

7、单元格中切换中英文输入法：比如选定A列，单击菜单［数据］—［有效性］打开“数据有效性”对话框，选择“输入法模式”选项卡，在“模式”下拉列表中选择“打开”选项，然后单击“确定”按钮即可，用一样方法能够把B列“输入法模式”设置为“关闭（英文模式）”，假如用户打开了某一个汉字输入法，那么选定A列单元格时将激活该输入法，选定B列单元格时将屏蔽该输入法，而显示“英语（美国）”图标，当用户需要限制某个单元格区域不能输入汉字时就能够使用此方法，以免重复地切换中英文输入法而带来麻烦。

8、限制输入重复数据：选定A列，单击菜单［数据］—［有效性］打开“数据有效性”对话框；2）选择“设置”选项卡，在“允许”下拉列表中选择“自定义”选项，在“公式”文本框中输入“=COUNTIF(A:A,A1)=1”，单击“确定”按钮。

9、禁止重复报餐：某企业为方便员工中午用餐，在3家餐馆为员工定餐，员工能够依照个人喜好选择其中一家餐馆报餐。负责报餐工作人员制作了一个Excel文件，以下列图所表示：

让每位员工自己在工作表上报餐，方法为：在自己名字与日期交叉单元格输入数字1，工作人员只需对对应单元格区域求和就能够算出某家餐馆共有几位员工报餐，方便与餐馆结账，为了预防同一员工同一天在多家餐馆重复报餐，能够设置数据有效性来禁止，方法以下：1）选定B2单元格，单击菜单［插入］—［名称］—［定义］打开“定义名称”对话框；2）在“在当前工作簿中名称”文本框中输入名称“Count”，在“引用位置”文本框中输入“=”，鼠标单击第1张工作表（鑫德海）标签，按住<Shift>键不放用鼠标再单击最终一张工作表（一品套餐）标签，放开<Shift>键，输入“B2”，然后单击“确定”按钮，以下列图所表示：

3）选定“鑫海德”工作表单元格区域B2：F26，单击菜单［数据］—［有效性］打开“数据有效性”对话框；4）选择“设置”选项卡，在“允许”下拉列表中选择“自定义”选项，在“公式”文本框中输入“=SUM(COUNT)=1”，并取消“忽略空值”复选框勾选状态；5）切换到“犯错警告”选项卡，在“样式”下拉列表中选择“停顿”选项，在“标题”文本框中输入“注意！”，在“错误信息”文本框中输入“请不要重复报餐！”，然后单击“确定”按钮；6）重复步骤3～步骤5，为另外两个工作表设置相同数据有效性。

经过设置以上数据有效性后，同一员工在同一天就不能在多家餐馆重复报餐了。如A12单元格“西门雪”在“鑫德海”报餐后，假如要在“禄鼎记”再报一次餐，Excel就会弹出“注意！”对话框，阻止用户继续报餐。

公式解析：名称Count是一个三维引用名称，返回3个工作表同一位置单元格引用，B2单元格有效性公式“=SUM(COUNT)”即表示“=鑫德海！B2+禄鼎记！B2+一品套餐！B2”。

10、创建二级下拉菜单_源为单个工作表：下列图所表示是“查询”表B1单元格创建下拉菜单，可供选择选项为“烟”和“酒”，C1单元格能依照B1单元格所选择不一样内容产生不一样序列内容下拉菜单，而且下拉菜单中不能包含空格，要创建这么下拉菜单，方法以下：

1）使用直接在有效性中输入序列内容创建下拉菜单方法，为“查询”表B1单元格创建序列为“烟”和“酒”下拉菜单，以下列图所表示：

2）单击菜单［插入］—［名称］—［定义］弹出“定义名称”对话框，在“当前工作薄中名称”文本框中输入“PingMing”，在“引用位置”文本框中输入公式：=OFFSET(数据!$A$2,,MATCH(查询!$B2,数据!$1:$1,)-1,COUNTA(OFFSET(数据!$A$2,,MATCH(查询!$B2,数据!$1:$1,)-1,65535)))，以下列图12所表示，然后单击“确定”按钮。

3）选定“查询”表中C1单元格，单击菜单［数据］—［有效性］打开“数据有效性”对话框；

4）切换到“设置”选项卡，在“允许”下拉列表中选择“序列”选项，在“起源”文本框中输入“=PinMing”，然后单击“确定”按钮。

5）在“查询”表D1单元格中输入公式：“=SUMIF(数据!A:C,C2,数据!B:D)”。

11、导入Word文档中表格：Word文档中表格不能直接导入Excel工作表中，不过用户能够采取［复制］—［粘贴］方法将Word文档中表格复制到Excel工作表中。但假如文档中表格较多时，复制起来就会很不方便。这里介绍一下经过网页文件快速导入Word文档中数据表格方法。1）打开Word文档“示例5”，在Word工作窗口中单击菜单［文件］—［另存为］对话框，在“保留类型”下拉列表中选择“单个文件网页”，然后单击“保留”按钮将该文档另存为网页文件；2）单击菜单［数据］—［导入外部数据］—［新建Web查询］打开“新建Web查询”对话框；3）在“新建Web查询”对话框“地址”下拉列表文本框中输入刚才保留文件完整路径，如file:///H:/fscommand/project/第2篇%20数据输入和导入/Chapter7%20导入外部数据/示例5.mht，然后单击“转到”按钮打开网页文件；4）在“新建Web查询”对话框中分别单击两个表格左上角“”标识将其选中，标识同时分别会变为“”，然后单击“导入”按钮打开“导入数据”对话框；5）在“数据放置位置”组合框中选中“现有工作表”单项选择按钮，并在文本框中输入数据导入起始单元格位置“=$A$1”；6）单击“确定”按钮即可导入数据，完成Word文档中表格导入工作。

12、快速插入多个单元格：更加快捷方法是：先选定目标单元格区域，然后按住<Shift>键，把光标移动到选定区域右下角，当光标变成份隔箭头时，以下列图所表示，再往右或者往下拖动，拖动距离就等于插入单元格数量，拖动方向等于“活动单元格”移动方向。

13、快速改变行列次序：把光标移动到C列右侧黑色边框上，按住<Shift>键开始往左拖动，这时我们能够看到光标左侧出现了一条工字形虚框，以下列图所表示，把这条虚线拖动到A列与B列之间单元格边框上，然后松开鼠标左键，列次序改变即完成。

14、快速缩放数值：许多用户在工作中经常需要处理很大数字，而利用下面自定义数字格式就能够在不改变数值本身同时对它们进行缩放，以下列图所表示。

15、单元格文本数据分行：在下列图中，A1单元格是由一个由多个成语连接而成字符串，各个字符之间没有间隔，现需要将这一字符串中各个成语分成多行并排显示，方法以下：

1）将A列列宽调整到显示4个汉字宽度；2）选中A1单元格，单击菜单[编辑]—[填充]—[内容重排]弹出“文本将超出选定区域”警告窗口；3）单击警告窗口中“确定”按钮即可得到分行结果。

注意：使用这种方法也能够将多行数据合并成一行。

16、多行多列数据转为单列数据：

1）在下列图中选中A1：C3单元格区域，连续按两次<Ctrl+C>组合键打开“剪贴板”任务窗格；

2）双击E1单元格激活编辑状态，然后单击“剪贴板”中刚才复制内容，这些内容将粘贴到E1单元格并显示在编辑栏中；

3）在编辑栏中全部选取之前粘贴过来数据，然后单击“惯用”工具栏中“复制”按钮；4）按<ESC>键退出单元格编辑状态，然后选中E1单元格，单击“惯用”工具栏中“粘贴”按钮；

5）将E列列宽调整为显示单个字符宽度，再单击菜单[编辑]—[填充]—[内容重排]，将会弹出“文本将超出选定区域”警告窗口，然后单击“确定”按钮继续操作；

6）数据内容重排后，转换后单列数据已经具备雏形，但单元格显示仍不完全，此时选中E列，重新将列宽宽度调大，以使其能够完全地显示数据内容，最终结果以下列图所表示。

17、排序字母与数字混合内容：1）在B1单元格中输入以下公式：=LEFT(A1,1)&RIGHT("000"&RIGHT(A1,LEN(A1)-1),3)；2）把B1单元格中公式向下复制到B10单元格；3）选中B2单元格，然后单击“惯用”工具栏中“升序排序”按钮。

注意：下列图中，A列是原始数据，B列是套公式后得到数据，C列选择性粘贴B列数值后排序数据。

18、快速删除空行：1）在下列图C1单元格输入公式“=A1=""”，然后复制公式向下填充到C20单元格；2）选中C1单元格，单击“惯用”工具栏中“升序排序”按钮出现“排序警告”对话框，选中“扩展选定区域”单项选择按钮后单击“确定”按钮即可得到排序结果；3）去除C列内容，最终得到删除空行、紧缩数据行效果。

19、快速制作工资条：1）在下列图J2：J11单元格区域依次填入数字1至10，再将一样次序10个数字复制到J11：J21单元格区域；2）选中J2单元格，单击“惯用”工具栏中“升序排序”按钮对数据区域进行升序排序；3）选中A3：J120单元格区域，按<Ctrl+G>组合键打开“定位”对话框，选中“空值”单项选择按钮，然后单击“确定”按钮即可选中当前区域中空单元格；4）输入公式“=A1”，按<Ctrl+Enter>组合键确认公式输入。

20、一次性删除全部重复数据：1）在下列图中B2单元格输入公式：=IF(COUNTIF(A$2:A2,A2)>1,1)，复制公式向下填充到B10单元格；选中B2单元格，单击“惯用”工具栏中“降序排序”按钮；B列显示为1所对应A列编号重复1次以上编号，去除这部分数据即可得到不包含重复项数据表。

21、了解高级筛选：在下列图中表格是一张包含了筛选条件区域数据列表，假如要将“人员类别”字段中为“经理人员”统计经过高级筛选功效筛选出来，详细步骤以下：

1）单击菜单[数据]—[筛选]—[高级筛选]弹出“高级筛选”对话框；

2）在“方式”组合框中选中“将筛选结果复制到其余位置”单项选择按钮；

3）然后将光标定位在“列表区域”文本框中，在表格内选取A4：F14单元格区域，此区域地址会自动地填写在“列表区域”文本框中，此区域即为当前数据列表所在单元格区域；

4）与步骤3类似，以下列图所表示，在“条件区域”文本框中选取B1：B2单元格区域，在“复制到”区域中选择A18：F18，最终单击“确定”按钮得到筛选结果；

5）以上面一样方式能够继续筛选出数量大于900结果。

22、使用“视图管理器”简化重复筛选操作：在下列图数据列表中，假如希望依照不一样工厂名和季节条件筛选出相关统计，而且筛选条件在不停地发生改变，那么利用“视图管理器”功效能够大大地简化高级筛选设置工作。

1）设置数据列表筛选区域，经过数据有效性将“工厂名”设置为可选择下拉菜单；

2）在条件区域选择条件。使用高级筛选，并选择在原数据区域显示筛选结果，以下列图书所表示；

3）单击菜单[视图]—[视图管理器]弹出“视图管理器”对话框；

4）单击“添加”按钮弹出“添加视图”对话框，定义“名称”为“1”，然后单击“确定”按钮关闭对话框，此时当前窗口设置即被定义为视图名称1；

当用户改变筛选条件后，如选择“工厂名”为“2工厂”，使用“视图管理器”能够快速地得到筛选结果，而无须重新设置高级筛选，方法以下：

1）单击菜单[数据]—[筛选]—[全部显示]，取消上次高级筛选显示结果，显示整个数据表；

2）单击菜单[视图]—[视图管理器]弹出“视图管理器”对话框；

3）选中定义视图名称1，然后点击“显示”按钮，这么在关闭“视图管理器”对话框同时也立刻显示出了新筛选条件下筛选结果。

依此方法，假如需要数次修改筛选条件，只要每次在改变条件后先恢复全部数据显示，然后调用“视图管理器”显示之前所保留定义视图，就能够立刻显示更改筛选条件后结果，这比起每一次设置“高级筛选”对话框要方便不少。

23、利用高级筛选拆分数据列表：下列图中表是一张包含“表2”数据列表，“表2”是“表1”子集（每个编码都只有一条统计），现在要将“表1”中不包含于“表2”中数据拆分出来，此时能够借助高级筛选功效实现，方法以下：

1）在A10单元格输入“筛选条件”，然后在A11单元格输入筛选条件公式：=ISNA(MATCH(A3,$F$3:$F$5,0))；

2）选中A2：D7单元格区域，单击菜单[数据]—[筛选]—[高级筛选]弹出“高级筛选”对话框，然后按下列图所表示进行设置；

3）单击“确定”按钮关闭“高级筛选”对话框即可得到筛选结果，实现数据列表拆分目标，以下列图所表示。

公式思绪解析：=ISNA(MATCH(A3,$F$3:$F$5,0))，该公式经过MATCH函数，在“表2”编码字段中查找“表1”中编码，假如“表1”编码包含在“表2”当中，则返回数值，假如没有找到则返回错误值。然后利用ISNA函数返回MATCH函数计算结果中错误值，表示此编码不包含在“表2”之中。

24、标识中标企业和中标金额：下列图显示是10家企业对5个工程项目标投标情况表，为了更醒目地显示每个项目标中标企业（此处假定金额最大者判为中标）及其中标金额，能够使用条件格式进行标识，方法以下：

1）选定单元格区域B3：F12，单击菜单[格式]—[条件格式]弹出“条件格式”对话框；

2）在“条件1（1）”下方下拉列表中选择“公式”，在右侧文本框中输入“=B3=MAX(B$3:B$12”；

3）单击“格式”按钮，在弹出“单元格格式”对话框中选择“字体”选项卡，在“字形”列表框中选择“加粗”，在“颜色”下拉列表中选择“白色”，然后选择“图案”选项卡，设置“单元格底纹”颜色为“黑色”；

4）单击“确定”按钮关闭“单元格格式”对话框，然后单击“条件格式”对话框中“确定”按钮关闭“条件格式”对话框。

若要对中标企业也深入标识，能够在A3：A12设置条件，方法和B3：F12条件格式设置相同，公式为：

=OR(B3:F3=SUBTOTAL(4,OFFSET($B$3:$B$12,,COLUMN($B:$F)-2)))；

公式中使用了对投标金额区域三维引用：OFFSET($B$3:$B$12,,COLUMN($B:$F)-2)，引用了每个项目投标金额单元格区域：B3：B12、C3：C12、D3：D12、E3：E12、F3：F12，再用分类汇总函数SUBTOTAL对每个区域求最大值（即各项目标中标金额），结果为{1280000,1290000,1270000,1260000,1300000}，最终用OR函数判断各个企业在每个项目中是否有某个投标金额等于中标金额，假如有则应用所设置格式。

25、制作国际象棋棋盘底纹：设置下列图所表示是国际象棋棋盘式底纹方法以下：

1）选择单元格区域A1：A19，单击菜单[格式]—[条件格式]；

2）在弹出“条件格式”对话框中，在“条件1（1）”下方下拉列表中选择“公式”，在右侧文本框中输入公式“=MOD(ROW()+COLUMN(),2)<>0”，此公式用于判断行号与列号之和除以2余数是否为0，假如为0，说明行数与列数奇偶性相同，不填充单元格底纹颜色，其余则填充单元格为浅绿色；在条件格式中，当公式结果返回一个数字时，非0数字即判断为条件成立，0和错误值则判断为条件不成立，所以上面公式也能够简写为：=MOD(ROW()+COLUMN(),2)；

3）单击“格式”按钮，设置对应颜色；

4）在“条件格式”对话框中单击“添加”按钮，在“条件2（2）”下方下拉列表中选择“公式”，在右侧文本框中输入公式“=MOD(ROW()+COLUMN(),2)=0”；

5）单击“格式”按钮，设置对应颜色。

26、标识重复值：下列图是某电脑城一些电脑设备型号表，现要求把设备型号重复统计全部用玫瑰红底纹标识出来，方法以下：

1）出现重复就标识：输入公式=COUNTIF($C$2:$C$27,$C2)>1；

2）第2次重复以后才标识：输入公式=COUNTIF($C$2:$C2,$C2)>1；

3）首次与其余重复次数标识不一样颜色：要求有重复不过第1次出现统计用黄色底纹标识，条件1输入公式=COUNTIF($C$2:$C2,$C2)>，设置玫瑰红；条件2输入公式=COUNTIF($C$2:$C$27,$C2)>1，设置黄色。

27、多汇总方式分类汇总：这里以下列图中数据列表为例，假如希望先按“部门”字段对“缴费基数”进行汇总，再按“部门”字段求出“年纪”平均值，最终按“部门”字段求出“工龄”最大值，方法以下：

1）选择数据列表中任意一个单元格，如A2单元格，单击菜单[数据]—[排序]打开“排序”对话框，在“主要关键字”下拉列表中选择“部门”字段，右侧排序次序选择“升序”选项，然后单击“确定”按钮完成份类字段排序；

2）单击菜单[数据]—[分类汇总]打开“分类汇总”对话框，以下列图所表示，在“分类字段”下拉列表中选择“部门”，在“汇总方式”下拉列表中选择“求和”，在“选定汇总项”列表框中勾选“缴费基数”复选框，并勾选“汇总结果显示在数据下方”复选框，然后单击“确定”按钮关闭“分类汇总”对话框；

3）其它设置同时骤2，唯一不一样地方是取消勾选“替换当前分类汇总”复选框，最终结果以下列图所表示（该图只截取了上半小部分）：

28、使用合并计算建立分户报表：合并计算能够按类别进行合并，假如引用区域行列方向均包含了多个类别，则可利用合并计算功效将引用区域中全部类别汇总到同一表格上并显示全部明细。比如8月份南京、上海、海口、珠海4个城市销售额数据分别在4个不一样工作表中，南京、上海销售表结构和数据分别以下列图所表示：

1）选中“汇总”工作表A3单元格作为结果表起始单元格，单击菜单[数据]—[合并计算]打开“合并计算”对话框；

2）在“函数”下拉列表中选择“求和”，在“引用位置”文本框中分别添加“南京”、“上海”、“海口”、“珠海”等4个工作表中数据区域，并在“标签位置”组合框中勾选“首行”和“最左列”复选框，然后单击“确定”按钮即可得到各个城市销售额汇总明细，以下列图所表示。

29、利用合并计算进行数值型数据查对：利用合并计算“按类别”合并功效，用户能够将烦琐数据查对工作变得轻松，以下列图所表示是新旧两组数据，现要将这两组数据差异找出来，方法以下：

1）更改新旧数据表中“代号”字段列标题，使两个数据表第二段标题不相同。比如将旧数据表中列标题“代号”更名为“旧代号”，将新数据表列标题“代号”更名为“新代号”；

2）选中A17单元格作为结果存放起始位置，在菜单栏上单击[数据]—[合并计算]打开“合并计算”对话框；

3）在“引用位置”文本框中依次选取添加旧数据表A2：B11区域和新数据表D2：E13区域，在“函数”下拉列表中选择“求和”，在“标签位置”组合框中同时勾选“首行”和“最左列”复选框，然后单击“确定”按钮，结果以下列图所表示；

4）为了深入显示出新旧数据不一样之处，能够在D18单元格中输入以下公式：=N(B18<>C18)，并复制公式向下填充至D28单元格；

5）补齐标题名称，选中A17：D28区域，在菜单栏上单击[数据]—[筛选]—[自动筛选]；

6）单击D17单元格中自动筛选标识，然后选择筛选值为“1”统计即可得到新旧数据差异查对结果。

30、利用数据透视表统计考试分数：利用数据透视表“字段设置”中不一样汇总方式，用户还能够对字段中数值型数据进行汇总统计，比如求最大值、最小值、平均值等。比如要对下列图所表示“各班成绩”表进行统计，求出各班级分数最大值、最小值、平均值，方法以下：

1）选中数据源“各班成绩”工作内任意一个数据区域，单击[数据]—[数据透视表和数据透视图]；

2）在弹出“数据透视表和数据透视图向导—3步骤1”对话框中单击“完成”按钮生成一个空白数据透视表，显示“数据透视表字段列表”窗口；

3）将“数据透视表字段列表”窗口中“班级”字段拖曳至数据透视表“列字段”位置，以下列图所表示：

4）将“数据透视表字段列表”窗口中“成绩”字段拖曳至数据透视表“数据项区域”，再重复两次操作；

5）选中A5单元格（求和项：成绩），单击右键，在弹出快捷菜单中选择“字段设置”命令，在弹出“数据透视表字段”对话框中“汇总方式”列表框中选择“最大值”选项，然后单击“确定”按钮；

6）依据步骤5完成最小值和平均值计算，最终止果以下列图所表示。

31、在数据透视表中添加计算项：数据透视表创建完成后，用户无法更改或者移动数据透视表中任何区域，也不能在数据透视表中插入单元格或是添加公式进行自定义计算。假如需要在数据透视表中创建自定义计算项目，必须使用“添加计算字段”或“添加计算项”功效。下列图展示了一张已经创建成功数据透视表，下面经过添加计算项来达成对比“国内”与“进口”产品各年差异目标。

1）选中数据透视表中“类别”按钮标题，然后单击“数据透视表”工具栏中[“数据透视表]—[公式]—[计算项]，以下列图所表示：

2）弹出“在‘类别’中插入计算字段”对话框，在“名称”文本框中输入“差额”，把光标定位到“公式”文本框中，单击“字段”列表框中“类别”选项，接着双击右侧“项”列表框中出现“国内”选项，然后输入减号“—”，再双击项列表框中“进口”选项，以下列图所表示：

3）单击“添加”按钮，最终单击“确定”按钮关闭对话框。此时数据透视表列字段区域中已经插入了一个新项目“差额”，其数值就是“国内”字段数据与“进口”字段数据差值。不过这里会出现一个问题，数据透视表中行“总计”将汇总全部行项目，包含新添加“差额”项，所以其结果不再具备实际意义。为了使行“总计”恢复实际统计功效，需要继续对数据透视表进行设置；

4）在数据透视表任意一个区域单击右键，在弹出快捷菜单中选择“表格选项”命令；

5）在弹出“数据透视表选项”对话框中取消勾选“行总计”复选项框，然后单击“确定”按钮关闭对话框；

6）重复步骤1继续添加计算项。将国内和进口进行共计，最终结果以下列图所表示。

32、切换显示公式和运算结果：单击菜单［工具］—［选项］弹出“选项”对话框，以下列图所表示，在“视图”选项卡“窗口选项”组合框中勾选“公式”复选框，这么用户在单元格输入公式时显示则是公式本身而不是公式运算结果。另外用户还能够使用<Ctrl+'>组合键（右撇号，通常在键盘上位于数字1左侧一个键）快速地切换该选项。

33、制作按揭贷款分析表：假设购置一套住房，需要向银行贷款约15～30万元，分10～30年时间还贷，在不一样贷款利率情况下，要计算等额还款方式下每个月所需按揭费用，方法以下：

1）选中A1单元格，单击[数据]—［有效性］，打开“数据有效性对话框”，选中“设置”选项卡。在“允许”下拉列表中选择“序列”，在“起源”文本框中输入各种贷款额度，如“15万元，20万元，25万元，30万元”，各个选项之间用半角逗号间隔，然后然后单击“确定”按钮关闭对话框。接下来在A1单元格数据有效性下拉列表中选择一个贷款额度，比如“15万元”。2）在C3：C7单元格内输入15～30年间贷款年限，在D2：L2单元格内输入各种贷款利率，比如本例中使用了从至今历年来公积金贷款年率，添加行列标题美化格式后结果以下列图所表示；

3）选中C2单元格，输入公式：=PMT(A3/12,A4*12,-LEFT(A1,2)*10000，公式结果显示为错误值“#DIV/0!”，此结果没有实际意义，并不影响模拟运算表继续操作；

4)选中单元格区域C2：L7，单击菜单[数据]—［模拟运算表］打开“模拟运算表”对话框，在“输入引用行单元格”文本框中输入“$A$3”，在“输入引用列单元格”文本框中输入“$A$4”，以下列图所表示：

5）单击“确定”按钮完成操作，调整数据显示格式后结果以下列图所表示：

上图中详细地显示了贷款15万元情形下，各种贷款年率和贷款时间所对应按月等额还款方式详细按揭金额。在A1单元格下拉列表中选择不一样贷款金额选项，模拟运算表中就会自动地改变显示对应月度按揭金额。

公式解析：=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)。PMT函数是一个财务函数，用于计算固定利率等额分期付款方式下每期付款额。该函数语法为PMT(rate,nper,pv,fv,type)，其中rate指是固定利率，利率时间单位必须与贷款期限单位以及最终计算还款时间频率单位相一致，在本例中D2：L2区域利率单位为“年”，最终计算目标为按月还款额，所以需要将年率除以12得到月率。rate参数指向单元格A3，这个单元格没有实际意义，只作为引用位置。nper指是付款总期数，也就是贷款期限，在本例中需要将C3：C7中对应年份乘以12得到月份数，以期与rate参数单位保持一致。Nper参数指向单元格A4，与上面A3单元格一样，此单元格也没有实际意义，只用做模拟运算表参数引用位置。pv指现值，在贷款模型中即指贷款总额，本例中需要从A1单元格字符串中取出对应数值同时乘上单位“万”，公式中“LEFT(A1,2)*10000”部分即是起此作用。fv指终值，在贷款模型中终值为零，可省略。type指付款方式，为1时代表期初付款，为0或省略时代表期末付款，在贷款模型中通常均为期末付款方式。

假如想要了解全部按揭期结束总还款金额，能够直接在C2单元格内将公式改为：

=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)*A4*12

显示结果以下列图所表示：

假如想要深入了解全部还款金额中所包含利息部分，能够将公式修改为：

=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)*A4*12-LEFT(A1,2)*1000

显示结果以下列图所表示：

34、设置间隔底纹：我们原来经常利用是在奇、偶数行方法来设置底纹，那我们这里来例1：按不一样地域名称设置间隔底纹：也就是依照间隔区域名称来设置间隔颜色，以下列图所表示：

从图中我们能够看出，当在B3单元格时，我们统计A列区域名称个数为1，也就是只有北京1个地域；而当在B6单元格时，我们统计A列区域名称个数为2，也就是有北京、上海2个地域；而当在B9单元格时，我们统计A列区域名称个数为3，也就是有北京、上海、重庆3个地域，依照这么规律，我们能够利用COUNTA来统计A列区域中单元格个数，那么我们选中A2：C13这一需要设置间隔底纹单元格区域，点击［格式］—［条件格式］，在公式栏中输入“=MOD(COUNTA($A$2:$A2),2)=1”，将其颜色设为淡黄色，然后添加公式，在公式栏中输入“=MOD(COUNTA($A$2:$A2),2)=0”，将其颜色设为淡绿色，完成隔色任务。

公式说明：公式中“$A$2:$A2”是A列中条件区域，“COUNTA($A$2:$A2)”统计出A列中符合条件区域个数，“=MOD(COUNTA($A$2:$A2),2)=1”是利用求模函数，将刚才统计出来A列个数与2相除得出余数，假如余数=1，则这一区域颜色被填为淡黄色。同理，“=MOD(COUNTA($A$2:$A2),2)=0”，就是余数为0，则这一区域颜色被填为淡绿色。而区域个数是1、2、3、4、5…次序递增1排列，那么其区域个数与2相除后余数必定是1、0、1、0、1…，非常有规律，于是我们就实现了不一样地域设置间隔底纹。

35、依照不一样名称设置间隔底纹：那么我们在看看下面这个图：

从图中我们能够看出，部门这一列已经排序，那么我们怎样才能按照不一样部门进行底纹颜色设置呢？我们能够依照上面例1原理，找到公式规律，“=MOD(SUM(--($B$2:$B2<>$B$1:$B1)),2”。

公式说明：“SUM(--($B$2:$B2<>$B$1:$B1)”表示统计$B$2:$B2与$B$1:$B1不相等数目，其中“—”是将逻辑值转换为数值，“MOD(SUM(--($B$2:$B2<>$B$1:$B1)),2”是求模函数，此公式后面没有等于0或1即为非0，此时我们将这一区域定义为玫红，至此完全颜色间隔分隔。

36、数据有效性中使用函数：比如，我们输入商品单价时通常输入两位小数，而且价格不可能为负数，那么用“=C5=FLOOR(C5,0.01)”这个公式能够处理问题。

公式说明：假如C5单元格中准备输入数字0.234，那么“=FLOOR(0.234,0.01)”将0.234沿绝对值减小方向向下舍入，使其等于最靠近0.01倍数(0.23)，假如C5单元格中准备输入数字-2.5，那么=FLOOR(-2.5,0.01)将返回错误值，因为-2.5和0.01符号不一样(#NUM!)。所以依照此公式输入数据有效性规则和警告提醒，就能够实现在C5单元格中只能输入两位正小数控制。

37、选择适宜趋势线类型：线性关系是众多数据关系中比较简单一个类型，其直线样式很轻易判断，但很多情况下数据规律比较复杂，“线性”趋势线并非适适用于全部数据类型趋势预测问题，为不一样数据类型选择适宜趋势线是正确使用趋势线进行预测分析主要前提。某城市对本市中、小学生中不一样年纪男性身高进行了抽样调查，得到以下列图所表示统计结果，要求依照现在统计结果预测年纪为6岁男性平均身高。

1)选中数据区域A2：B14，单击菜单［插入］—［图表］打开“图表向导—4步骤之1—图表类型”对话框，在“标准类型”选项卡中选中左侧“图表类型”列表框中“XY散点图”，然后在右侧“子图表类型”中选择“平滑线散点图”；

2)单击“完成”按钮即可生成XY散点图，进行坐标轴设置和图表格式美化后效果以下列图所表示，其中以年纪数据作为X轴，以身高数据作为Y轴。

3)在图表中单击任意一个数据点以选中数据系列，然后单击鼠标右键，在弹出快捷菜单中选择“添加趋势线”命令打开“添加趋势线”对话框，选择“类型”选项卡，在“趋势预测/回归分析类型”组合框中选择“线性”趋势线类型，如上图所表示。

4）选择“选项”选项卡，同时勾选其中“显示公式”和“显示R平方值”复选框；

5）单击“确定”按钮，结果如上图所表示，图表中不但显示了新添加趋势线，而且显示了此趋势线公式以及R平方值。R平方值也称为“决定系数”，反应了趋势线估量值与对应实际数据之间拟合程度，其数值范围位于0与1之间。当R平方值等于或靠近于1时，趋势线对于实际数据拟合程序最高，此时趋势线最可靠，经过此趋势线预测得到数据也最准确。

6）按时上面步骤能够得到线性、对数、多项式、乘幂、指数趋势图，经过比较，“对数”趋势线对应R平方值0.9946是其中最大值，将需要预测年纪6作为自变量x代入公式中，得到应变量y即预测身高值函数公式以下：=54.1*LN(6)+14.635，经过公式预测得到当地6岁男性平均身高大约为111.57厘米。

7）假如用户对现有数据类型比较了解，那么有时候并不需要分别尝试每一个趋势线类型，而依据一定经验选择适宜趋势线即可。

38、计算住房贷款之一：贷款按揭买房是现实生活中很热门话题之一，许多购置者通常先考虑自己能够承受月供范围，然后再计算能够贷款额度和期限，对于这类问题能够借助单变量求解工具来处理。假设某客户在买房前，预期每个月还款额为2500元，需要贷款30万元，现在贷款年利率假设为5.22%，计算还清贷款所需要时间方法以下：

1）将已知条件输入工作表中，形成以下列图所表示数据区域：

2）在B3单元格内输入公式“=PMT(B1/12,B4,-B2)”建立起计算模型；

3）在B4单元格内设定可变单元格初始值，比如1，然后选定B3单元格，单击菜单[工具]—[单变量求解]打开“单变量求解”对话框，将“目标值”设为2500，在“可变单元格”文本框中输入“B4”，以下列图所表示：

4）单元“确定”按钮即可，显示最终求解结果，以下列图所表示，能够看到在每个月还款2500元左右水平下，大约需要170个月即大约左右能够还清全部贷款。

注意：假如使用财务函数，那么在本技巧第1幅图所表示表格B4单元格内直接输入公式“=NPER(B1/12,B3,-B2)”也能够得到结果。

39、在Excel中安装规划求解工具：使用默认方式安装Excel，通常不能直接使用规划求解工具，需要用户手动添加相关组件后才能使用此功效。规划求解工具在Excel中是作为一个预置加载宏存在，假如用户使用菜单栏上“工具”菜单列表中没有“规划求解”命令，则可参考以下方法进行：

1）在Excel工作窗口中单击菜单[工具]—[加载宏]打开“加载宏”对话框，勾选列表中“规划求解”工具前复选框，以下列图4所表示；

2）单元“确定”按钮弹出警告对话框，系统问询用户是否需要现在安装规划求解加载宏；

3）单击“是”按钮，Excel开始安装过程，并显示安装状态对话框，直至安装完成。

在规划求解加载宏添加完成之后，在菜单栏上单击“工具”，就能够在其下拉菜单中显示“规划求解”命令。

40、求解旅行商问题：它路线需要经过网络中全部节点，而且最终形成回路。对于每个节点来说，都要被访问到而且只访问一次，同时对于某个节点来说，访问者起源必定是唯一。某送奶工天天从配送出发需要送奶至6个不一样位置小区，然后将6个地方搜集来空瓶再送回配送点，经过长时间观察统计，送奶工将6个小区之间骑行所需平均时间整理以下列图所表示，其中配送点就设置在A小区附近，所以能够将A小区视作出发点。现在送奶工想要知道，怎样规划一天送奶路线，能够使得花费在路上时间最少。此问题即为一个经典旅行商问题，能够用Excel规划求解工具来解答，方法以下：

1）依照题目需求，在原有题目条件下方建立规划求解所需公式模型，以下列图所表示，其中C12：H17单元格区域用于统计实际路径选择情况，能够用数字0表示路径未选择，用数字1表示选择从某地出发前往另一地。此区域将作为规划求解可变单元格区域。但需要注意是：其中A小区至A小区、B小区至B小区等类似路径在实际中是不存在，所以在规划求解时需要确保C12、D13…H17等单元格取值不可为1。1列用于统计抵达各地点起源数目。依照旅行商问题特征，每个地点访问起源地是唯一，在I12单元格内输入公式“=SUM（C12：H12）”，然后向下复制填充至I17单元格。第18行用于统计各出发地前往目标地数目，依照旅行商问题特征，每个出发地目标地点也是唯一确定，在C18单元格内输入公式“=SUM（C12：C17）”，然后向下复制填充至H18单元格。J列用于统计访问路线确定情况下各条线路所需时间，能够在J12单元格内输入公式“=SUMPRODOCT(C3:H3,C12:H12)”,然后向下复制填充至J17单元格。J18单元格用于累计J12：J17单元格中时间，即走完整条送奶路线总时间，能够在单元格中输入公式“=SUM(J12:J17)”，此单元格将作为规划求解目标单元格；

HYPERLINK