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+179 5、神奇的选择性粘贴:1)全部:在绝大多数情况下等效于常规的粘贴;2)公式:只复制原始区域的公式;3)数值:只复制数值，如果原始区域是公式，则只复制公式的计算结果;4)格式:只复制原始区域的格式;5)批注:只复制原始区域的批注;6)有效性验证:只复制原始区域中设置的数字有效性;7)边框除外:复制边框之外的所有内容;8)列宽:从一列到另一列复制列宽信息;9)
跳过空单元格:可以有效地防止原始区域中的空单元格覆盖粘贴目标区域中的单元格内容;10)转置:能够让原始区域在复制后行列互换;11)粘贴链接:将建立一个由公式组成的连接原始区域的动态链接;12)加:允许用户进行一次简单的数值运算。例如:选定任意一个空白单元格，然后按
组合键进行复制，按住键单击所有的需要取消超链接的单元格。如果是一个单元格区域，可以先用鼠标单击并按住最外面的单元格，然后向上拖动选定整个区域，待光标由手的形状转变为十字形然后再松开鼠标按键，单击菜单,编辑,—,选择性粘贴,，在打开的“选择性粘贴”对话框中选中“加”单选按钮，然后单击“确定”按钮，这样所有的被选定的超链接就都转换为普通文本了，如下图所示。
6、创建动态名称:如果需要创建一个名称来引用C列中的数据，但又不希望这个引用区域包含空白单元格，在这种情况下可以创建动态名称，根据用户追加或删除数据的结果来自动地调整引用的位置，以达到始终引用非空白单元格区域的结果。创建动态名称的方法如下:1)单击菜单,插入,—,名称,—,定义,;2)打开“定义名称”对话框，在“在当前工作薄中的名称”文本框中输入“Date”，在“引用位置”文本框中输入公式:=OFFSET(Sheet1!$C$4,,,COUNTA(Sh
eet1!$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张工作表(鑫德海)的标签，按住键不放用鼠标再单击最后一张工作表(一品套餐)的标签，放开键，输入“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(OFFSE
T(数据!$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、快速插入多个单元格:更快捷的方法是:先选定目标单元格区域，然后按住键，把光标移动到选定区域的右下角，当光标变成分隔箭头时，如下图所示，再往右或者往下拖动，拖动的距离就等于插入单元格的数量，拖动的方向等于“活动单元格”移动的方向。
13、快速改变行列的次序:把光标移动到C列右侧的黑色边框上，按住键开始往左拖动，这时我们可以看到光标左侧出现了一条工字形虚框，如下图所示，把这条虚线拖动到A列与B列之间的单元格边框上，然后松开鼠标左键，列次序改变即完成。
14、快速缩放数值:许多用户在工作中常常需要处理很大的数字，而利用下面的自定义数字格式就能够在不改变数值本身的同时对它们进行缩放，如下图所示。
15、单元格文本数据分行:在下图中，A1单元格是由一个由多个成语连接而成的字符串，各个字符之间没有间隔，现需要将这一字符串中的各个成语分成多行并排显示，方法如下:
1)将A列的列宽调整到显示4个汉字的宽度;2)选中A1单元格，单击菜单[编辑]—[填充]—[内容重排]弹出“文本将超出选定区域”的警告窗口;3)单击警告窗口中的“确定”按钮即可得到分行结果。
注意:使用这种方法也可以将多行数据合并成一行。
16、多行多列数据转为单列数据:
1)在下图中选中A1:C3单元格区域，连续按两次组合键打开“剪贴板”任务窗格;
2)双击E1单元格激活编辑状态，然后单击“剪贴板”中刚才复制的内容，这些内容将粘贴到E1单元格并显示在编辑栏中;
3)在编辑栏中全部选取之前粘贴过来的数据，然后单击“常用”工具栏中的“复制”按钮;4)按键退出单元格的编辑状态，然后选中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单元格区域，按组合键打开“定位”对话框，选中“空值”单选按钮，然后单击“确定”按钮即可选中当前区域中的空单元格;4)输入公式“=A1”，按组合键确认公式输入。
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、使用合并计算建立分户报表:合并计算可以按类别进行合并，如果引用区域的行列方向均包含了多个类别，则可利用合并计算功能将引用区域中的全部类别汇总到同一表格上并显示所有的明细。例如2007年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、切换显示公式和运算结果:单击菜单,工具,—,选项,弹出“选项”对话框，如下图所示，在“视图”选项卡的“窗口选项”组合框中勾选“公式”复选框，这样用户在单元格输入公式时显示的则是公式本身而不是公式的运算结果。此外用户还可以使用组合键(右撇号，一般在键盘上位于数字1左侧的一个键)快速地切换该选项。
33、制作按揭贷款分析表:假设购买一套住房，需要向银行贷款约15,30万元，分10,30年时间还贷，在不同的贷款利率情况下，要计算等额还款方式下每月所需按揭的费用，方法如下:
1)选中A1单元格，单击[数据]—,有效性,，打开“数据有效性对话框”，选中“设置”选项卡。在“允许”下拉列表中选择“序列”，在“来源”文本框中输入各种贷款额度，如“15万元，20万元，25万元，30万元”，各个选项之间用半角逗号间隔，然后然后单击“确定”按钮关闭对话框。接下来在A1单元格数据有效性的下拉列表中选择一个贷款额度，例如“15万元”。2)在C3:C7单元格内输入15,30年间贷款年限，在D2:L2单元格内输入各种贷款利率，例如本例中使用了从2002年至今历年来的公积金贷款年率，添加行列标题美化格式后的结果如下图所示;
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—图表类型”对话框，在“
标准
excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载
类型”选项卡中选中左侧“图表类型”列表框中的“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个月即大约14年左右可以还清全部贷款。
注意:如果使用财务函数，那么在本技巧第1幅图所示表格的B4单元格内直接输入公式“=NPER(B1/12,B3,-B2)”也可以得到结果。
39、在Excel中安装规划求解工具:使用默认方式安装的Excel 2003，通常不能直接使用规划求解工具，需要用户手动添加相关组件后才能使用此功能。规划求
解工具在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)”，此单元格将作为规划求解的目标单元格;
2)为了提高规划求解结果的可读性，可以预先将C2:H17单元格区域的数字格式自定义为“0”。
3)选中J18单元格，单击菜单[工具]—[规划求解]打开“规划求解参数”对话框，在“设置目标单元格”文本框中选择J18单元格，选中“最小值”单选按钮，在“可变单元格”文本框中选择C12:H17单元格区域;
4)单击“添加”按钮打开“添加约束”对话框进行约束条件的添加，本例中所包含的约束条件如下:条件1:C12:H17为二进制数、条件2:I12:I17=1、条件3:C18:H18=1。在条件1中将可变单元格C12:H17的约束条件设置为二进制数，可使其取值在0-1之间变化。要将目标约束为二进制数，可以在“添加约束”对话框中间的条件下拉列表不选择“bin”。各个条件添加完成单击“添加约束”对话框中的“确定”按钮返回“规划求解”对话框，如果如下图所示。
5)C12、D13„H17这6个单元格的取值需要限定为0，可以在上面的“规划求解参数”对话框中继续添加约束条件。还有一种更简单的方法是将原条件区域中的C3、D4„H8这6个对角单元格的值改为远远大于其他路线时间的数值，使得规划求解过程中不可能选取到A-A、B-B这样的路径。例如在本例中可以在这6个对角单元格中填入“999”，如下图所示。
6)单击“规划求解参数”对话框中的“求解”按钮开始求解运算过程，并显示找到一个结果，单击“规划求解结果”对话框中的“确定”按钮可以保存此结果，如下图所示:
当前规划求解找到一条最短的路线
方案
气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载
，考察这条路线的具体走法，如果能够形成一个独立的封闭回路，即从A小区出发能够访问到其他5个小区最后再返回A小区，说明此路线即为满足题目要求的最佳路线方案，否则需要根据情况继续规划求解过程以求取满足条件的答案。通过上图中的解答可以发现，当前解法路线包含两个独立的回路(反向同样成立):
显然存在上述两条回路的情况下无法满足从A点出发遍历完所有节点再返回A点的要求，因此需要进一步地查找合理的解答，同时通过当前解答也可以知道，此问题的最终合理路线方案的开销时间应该大于等于目前的结果49。
要将当前的求解结果最终调整为单独的一条回路，需要拆分目前两条回路中的一条。可以从其中的较短的一条回路“”入手，采用人为设置障碍的方法，使得“”的路线不可选或“”的路线不可选，相当于宣布“此路不能”，从而打断原有的回路，让规划求解找到更合理的最佳路线。由此这个规划求解问题分成了下面的两个分支，即分别寻求B至D不通和D至B不通情况下的最短路线方案。
分支1:D至B路径不通。
要设置人为障碍使得D至B的路径不通，可以在题目条件区域中将F4单元格的数值设置为相当大的一个数，如999，也可以直接添加约束条件限制F13单元格的取值。 7)单击菜单[工具]—[规划求解]打开“规划求解参数”对话框，在原有规划求解参数的基础上继续添加约束条件，条件4:F13=0，如下图所示;
8)单击“求解”按钮开始求解运算过程，并显示找到一个结果，单击“规划求解结果”对话框中的“确定”按钮可以保存此结果，如下图114所示:
从当前找到的分支结果来看，全局路线形成了一条完整的封闭回路(反向同样成立):
路径满足题目要求，同时此路线方案的时间开销总计为55，然后记录下此结果留待对比。
分支2:B至D路径不通。
要设置人为障碍使得B至D的路径不通，同样可以通过修改题目条件或增加约束条件，需要注意的是，在改变题目条件或增加约束条件之前，同时需要恢复之前所设置的D至B路径不通的条件，最后得到的结果如下图所示:
从现在找到的分支结果来看，全局路线仍然分成两条独立的回路。
同时当前的路线方案时间开销总计为59，如果继续在此基础上寻求单一回路的方案，其时间总开销必定比59更大，更大于第1个分支的结果55，因此第2个分支的路线方案结果必不会优于第1个分支的路线方案。
由此可以判断，最佳路线为以下路线或者其相反方向。
浙公网安备 33021202002483