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

1、Excel数据处理与分析实战精粹Excel数据处理与分析实战精粹Excel数据处理与分析实战技巧精粹 1、使用工作表保护:1)选定所有允许访问的单元格，单击菜单,格式,单元格,;2)在打开的“单元格格式”对话框“保护”选项卡中取消勾选“锁定”复选框，然后单击“确定”按钮;3)单击菜单,工具,保护,保护工作表,，在打开的“保护工作表”对话框中取消勾选“选定锁定单元格”复选框;4)如果需要，可以加上工作表保护密码并确认密码，最后单击“确定”按钮即可;5)此时在工作表中只有未锁定的单元格可以被选中和编辑，而其他的单元格都会受到Excel的保护，无法被选中更无法进行编辑。 注意:也可以把你需要隐藏的文

2、字设置成白色，然后再进行以上的工作表保护，那别人既看不到你的内容也没办法修改了。 2、标题行的重复打印:1)单击菜单,文件,页面设置,，在打开的“页面设置”对话框中单击“工作表”选项卡;2)如果要指定在顶部重复的一行或连续的几行，则可单击“顶端标题行”文本框最右边的按钮，然后在工作表中进行相应的指定;3)如果要指定在左侧重复的一行或连续的几行，则可单击“左端标题列”文本框最右边的按钮，然后在工作表中进行相应的指定，如下图所示;4)单击“确定”按钮关闭“页面设置”对话框。 3、巧用右键和双击填充:如果用户先输入内容到一个单元格，然后用鼠标右键拖曳此单元格，那么松开右键，就会出现一个快捷菜单，菜单

3、上显示了本次填充可以选用的类型，甚至可以链接到“序列”对话框进行更复杂的设置，如下图所示: 当用户在数据区域中对某一列的内容进行填充时，不必从第一个单元格一直拖曳到最后一个单元格，只需要双击第一个单元格的填充柄即可。但是使用双击的方法进行填充的时候，填充到的最后一个单元格的位置则取决于左边一列中第一个空白单元格的位置(如果填充列是第一列，则参考右边列中的单元格)。例如在下图中因为A5是空白单元格，所以B列中的填充只进行到B4单元格就会停止。 4、快速输入对号、错号、平方与立方:下面的数字均必须在小键盘上输入。 名称 快捷键 名称 快捷键 名称 快捷键 名称 快捷键 对号 错号 平方 立方 AL

4、T+41420 ALT+41409 ALT+178 ALT+179 5、神奇的选择性粘贴:1)全部:在绝大多数情况下等效于常规的粘贴;2)公式:只复制原始区域的公式;3)数值:只复制数值，如果原始区域是公式，则只复制公式的计算结果;4)格式:只复制原始区域的格式;5)批注:只复制原始区域的批注;6)有效性验证:只复制原始区域中设置的数字有效性;7)边框除外:复制边框之外的所有内容;8)列宽:从一列到另一列复制列宽信息;9)跳过空单元格:可以有效地防止原始区域中的空单元格覆盖粘贴目标区域中的单元格内容;10)转置:能够让原始区域在复制后行列互换;11)粘贴链接:将建立一个由公式组成的连接原始区域

5、的动态链接;12)加:允许用户进行一次简单的数值运算。例如:选定任意一个空白单元格，然后按组合键进行复制，按住键单击所有的需要取消超链接的单元格。如果是一个单元格区域，可以先用鼠标单击并按住最外面的单元格，然后向上拖动选定整个区域，待光标由手的形状转变为十字形然后再松开鼠标按键，单击菜单,编辑,选择性粘贴,，在打开的“选择性粘贴”对话框中选中“加”单选按钮，然后单击“确定”按钮，这样所有的被选定的超链接就都转换为普通文本了，如下图所示。 6、创建动态名称:如果需要创建一个名称来引用C列中的数据，但又不希望这个引用区域包含空白单元格，在这种情况下可以创建动态名称，根据用户追加或删除数据的结果来自

6、动地调整引用的位置，以达到始终引用非空白单元格区域的结果。创建动态名称的方法如下:1)单击菜单,插入,名称,定义,;2)打开“定义名称”对话框，在“在当前工作薄中的名称”文本框中输入“Date”，在“引用位置”文本框中输入公式:=OFFSET(Sheet1!$C$4,COUNTA(Sheet1!$C:$C)-1)，如下图所示，单击“确定”按钮。 以上公式先计算C列中除了列标题以外的非空白单元格的数量，然后以C4单元格(首个数据单元格)为基准开始向下定位，定位的行数等于刚才计算出来的数据。下面可以在C列以外的单元格中通过计算来验证此名称的引用是否正确，比如在B1中输入公式:=SUM(Date)，

7、如下图所示。 如果继续追加记录，名称“Date”的引用位置就会自动地发生改变，B2中的计算结果能够体现这一点，如下图所示。 注意:以上公式只能正确计算不间断的连续数据，如果表格中的数据有空白单元格，那么动态名称的引用位置将发生错误。 7、单元格中切换中英文输入法:例如选定A列，单击菜单,数据,有效性,打开“数据有效性”对话框，选择“输入法模式”选项卡，在“模式”下拉列表中选择“打开”选项，然后单击“确定”按钮即可，用同样的方法可以把B列的“输入法模式”设置为“关闭(英文模式)”，如果用户打开了某一种中文输入法，那么选定A列的单元格时将激活该输入法，选定B列的单元格时将屏蔽该输入法，而显示“英语

8、(美国)”图标，当用户需要限制某个单元格区域不能输入中文时就可以使用此方法，以免重复地切换中英文输入法而带来麻烦。 8、限制输入重复数据:选定A列，单击菜单,数据,有效性,打开“数据有效性”对话框;2)选择“设置”选项卡，在“允许”下拉列表中选择“自定义”选项，在“公式”文本框中输入“=COUNTIF(A:A,A1)=1”，单击“确定”按钮。 9、禁止重复报餐:某公司为方便员工中午用餐，在3家餐馆为员工定餐，员工可以根据个人的喜好选择其中的一家餐馆报餐。负责报餐的工作人员制作了一个Excel文件，如下图所示: 让每位员工自己在工作表上报餐，方法为:在自己名字与日期交叉的单元格输入数字1，工作人

9、员只需对相应的单元格区域求和就可以算出某家餐馆共有几位员工报餐，以便与餐馆结账，为了防止同一员工同一天在多家餐馆重复报餐，可以设置数据有效性来禁止，方法如下:1)选定B2单元格，单击菜单,插入,名称,定义,打开“定义名称”对话框;2)在“在当前工作簿中的名称”文本框中输入名称“Count”，在“引用位置”文本框中输入“=”，鼠标单击第1张工作表(鑫德海)的标签，按住键不放用鼠标再单击最后一张工作表(一品套餐)的标签，放开键，输入“B2”，然后单击“确定”按钮，如下图所示: 3)选定“鑫海德”工作表的单元格区域B2:F26，单击菜单,数据,有效性,打开“数据有效性”对话框;4)选择“设置”选项卡

10、，在“允许”下拉列表中选择“自定义”选项，在“公式”文本框中输入“=SUM(COUNT)=1”，并取消“忽略空值”复选框的勾选状态;5)切换到“出错警告”选项卡，在“样式”下拉列表中选择“停止”选项，在“标题”文本框中输入“注意”，在“错误信息”文本框中输入“请不要重复报餐”，然后单击“确定”按钮;6)重复步骤3,步骤5，为另外两个工作表设置相同的数据有效性。 通过设置以上的数据有效性后，同一员工在同一天就不能在多家餐馆重复报餐了。如A12单元格的“西门雪”在“鑫德海”报餐后，如果要在“禄鼎记”再报一次餐，Excel就会弹出“注意”对话框，阻止用户继续报餐。 公式解析:名称Count是一个三维

11、引用的名称，返回3个工作表的同一位置单元格的引用，B2单元格的有效性公式“=SUM(COUNT)”即表示“=鑫德海B2+禄鼎记B2+一品套餐B2”。 10、创建二级下拉菜单_源为单个工作表:下图所示的是“查询”表的B1单元格创建下拉菜单，可供选择的选项为“烟”和“酒”，C1单元格能根据B1单元格所选择的不同内容产生不同序列内容的下拉菜单，并且下拉菜单中不能包含空格，要创建这样的下拉菜单，方法如下: 1)使用直接在有效性中输入序列内容创建下拉菜单的方法，为“查询”表B1单元格创建序列为“烟”和“酒”的下拉菜单，如下图所示: 2)单击菜单,插入,名称,定义,弹出“定义名称”对话框，在“当前工作薄中

12、的名称”文本框中输入“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,C

13、2,数据!B:D)”。 11、导入Word文档中的表格:Word文档中的表格不能直接导入Excel工作表中，不过用户可以采用,复制,粘贴,的方法将Word文档中的表格复制到Excel工作表中。但如果文档中的表格较多时，复制起来就会很不方便。这里介绍一下通过网页文件快速导入Word文档中的数据表格的方法。1)打开Word文档“示例5”，在Word工作窗口中单击菜单,文件,另存为,对话框，在“保存类型”下拉列表中选择“单个文件网页”，然后单击“保存”按钮将该文档另存为网页文件;2)单击菜单,数据,导入外部数据,新建Web查询,打开“新建Web查询”对话框;3)在“新建Web查询”对话框的“地址”下

14、拉列表文本框中输入刚才保存的文件的完整路径，如file:/H:/fscommand/project/第2篇%20数据的输入和导入/Chapter7%20导入外部数据/示例5.mht，然后单击“转到”按钮打开网页文件;4)在“新建Web查询”对话框中分别单击两个表格左上角的“”标识将其选中，标识同时分别会变为“”，然后单击“导入”按钮打开“导入数据”对话框;5)在“数据的放置位置”组合框中选中“现有工作表”单选按钮，并在文本框中输入数据导入的起始单元格位置“=$A$1”;6)单击“确定”按钮即可导入数据，完成Word文档中表格的导入工作。 12、快速插入多个单元格:更快捷的方法是:先选定目标单元

15、格区域，然后按住键，把光标移动到选定区域的右下角，当光标变成分隔箭头时，如下图所示，再往右或者往下拖动，拖动的距离就等于插入单元格的数量，拖动的方向等于“活动单元格”移动的方向。 13、快速改变行列的次序:把光标移动到C列右侧的黑色边框上，按住键开始往左拖动，这时我们可以看到光标左侧出现了一条工字形虚框，如下图所示，把这条虚线拖动到A列与B列之间的单元格边框上，然后松开鼠标左键，列次序改变即完成。 14、快速缩放数值:许多用户在工作中常常需要处理很大的数字，而利用下面的自定义数字格式就能够在不改变数值本身的同时对它们进行缩放，如下图所示。 15、单元格文本数据分行:在下图中，A1单元格是由一个

16、由多个成语连接而成的字符串，各个字符之间没有间隔，现需要将这一字符串中的各个成语分成多行并排显示，方法如下: 1)将A列的列宽调整到显示4个汉字的宽度;2)选中A1单元格，单击菜单编辑填充内容重排弹出“文本将超出选定区域”的警告窗口;3)单击警告窗口中的“确定”按钮即可得到分行结果。 注意:使用这种方法也可以将多行数据合并成一行。 16、多行多列数据转为单列数据: 1)在下图中选中A1:C3单元格区域，连续按两次组合键打开“剪贴板”任务窗格; 2)双击E1单元格激活编辑状态，然后单击“剪贴板”中刚才复制的内容，这些内容将粘贴到E1单元格并显示在编辑栏中; 3)在编辑栏中全部选取之前粘贴过来的数

17、据，然后单击“常用”工具栏中的“复制”按钮;4)按键退出单元格的编辑状态，然后选中E1单元格，单击“常用”工具栏中的“粘贴”按钮; 5)将E列的列宽调整为显示单个字符的宽度，再单击菜单编辑填充内容重排，将会弹出“文本将超出选定区域”的警告窗口，然后单击“确定”按钮继续操作; 6)数据内容重排后，转换后的单列数据已经具备雏形，但单元格显示仍不完全，此时选中E列，重新将列宽宽度调大，以使其能够完全地显示数据内容，最后结果如下图所示。 17、排序字母与数字的混合内容:1)在B1单元格中输入以下公式:=LEFT(A1,1) & RIGHT(000 & RIGHT(A1,LEN(A1)-1),3);2)

18、把B1单元格中的公式向下复制到B10单元格;3)选中B2单元格，然后单击“常用”工具栏中的“升序排序”按钮。 注意:下图中，A列是原始数据，B列是套公式后得到的数据，C列选择性粘贴B列数值后排序的数据。 18、快速删除空行:1)在下图C1单元格输入公式“=A1=”，然后复制公式向下填充到C20单元格;2)选中C1单元格，单击“常用”工具栏中的“升序排序”按钮出现“排序警告”对话框，选中“扩展选定区域”单选按钮后单击“确定”按钮即可得到排序结果;3)清除C列内容，最终得到删除空行、紧缩数据行的效果。 19、快速制作工资条:1)在下图的J2:J11单元格区域依次填入数字1至10，再将同样顺序的10

19、个数字复制到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次以上的编号，清除这部分数据即可得到不包含重复项的数据

20、表。 21、了解高级筛选:在下图中的表格是一张包含了筛选条件区域的数据列表，如果要将“人员类别”字段中为“经理人员”的记录通过高级筛选功能筛选出来，具体步骤如下: 1)单击菜单数据筛选高级筛选弹出“高级筛选”对话框; 2)在“方式”组合框中选中“将筛选结果复制到其他位置”单选按钮; 3)然后将光标定位在“列表区域”文本框中，在表格内选取A4:F14单元格区域，此区域的地址会自动地填写在“列表区域”文本框中，此区域即为当前数据列表所在的单元格区域; 4)与步骤3类似，如下图所示，在“条件区域”文本框中选取B1:B2单元格区域，在“复制到”区域中选择A18:F18，最后单击“确定”按钮得到筛选结果

21、; 5)以上面同样的方式可以继续筛选出数量大于900的结果。 22、使用“视图管理器”简化重复的筛选操作:在下图的数据列表中，如果希望根据不同的工厂名和季节条件筛选出相关的记录，并且筛选条件在不断地发生变化，那么运用“视图管理器”功能可以大大地简化高级筛选的设置工作。 1)设置数据列表筛选区域，通过数据有效性将“工厂名”设置为可选择的下拉菜单; 2)在条件区域选择条件。使用高级筛选，并选择在原数据区域显示筛选结果，如下图书所示; 3)单击菜单视图视图管理器弹出“视图管理器”对话框; 4)单击“添加”按钮弹出“添加视图”对话框，定义“名称”为“1”，然后单击“确定”按钮关闭对话框，此时当前窗口设

22、置即被定义为视图名称1; 当用户改变筛选条件后，如选择“工厂名”为“2工厂”，使用“视图管理器”可以快速地得到筛选结果，而无须重新设置高级筛选，方法如下: 1)单击菜单数据筛选全部显示，取消上次高级筛选显示的结果，显示整个数据表; 2)单击菜单视图视图管理器弹出“视图管理器”对话框; 3)选中定义的视图名称1，然后点击“显示”按钮，这样在关闭“视图管理器”对话框的同时也立即显示出了新的筛选条件下的筛选结果。 依此方法，如果需要多次修改筛选条件，只要每次在改变条件后先恢复全部数据的显示，然后调用“视图管理器”显示之前所保存的定义视图，就可以立即显示更改筛选条件后的结果，这比起每一次设置“高级筛选

23、”对话框要方便不少。 23、运用高级筛选拆分数据列表:下图中的表是一张包含“表2”的数据列表，“表2”是“表1”的子集(每个编码都只有一条记录)，现在要将“表1”中不包含于“表2”中的数据拆分出来，此时可以借助高级筛选功能实现，方法如下: 1)在A10单元格输入“筛选条件”，然后在A11单元格输入筛选条件公式:=ISNA(MATCH(A3,$F$3:$F$5,0); 2)选中A2:D7单元格区域，单击菜单数据筛选高级筛选弹出“高级筛选”对话框，然后按下图所示进行设置; 3)单击“确定”按钮关闭“高级筛选”对话框即可得到筛选结果，实现数据列表拆分的目的，如下图所示。 公式思路解析:=ISNA(M

24、ATCH(A3,$F$3:$F$5,0)，该公式通过MATCH函数，在“表2”的编码字段中查找“表1”中的编码，如果“表1”的编码包含在“表2”当中，则返回数值，如果没有找到则返回错误值。然后利用ISNA函数返回MATCH函数计算结果中的错误值，表示此编码不包含在“表2”之中。 24、标识中标公司和中标金额:下图显示的是10家公司对5个工程项目的投标情况表，为了更醒目地显示每个项目的中标公司(此处假定金额最大者判为中标)及其中标金额，可以使用条件格式进行标识，方法如下: 1)选定单元格区域B3:F12，单击菜单格式条件格式弹出“条件格式”对话框; 2)在“条件1(1)”下方的下拉列表中选择“公

25、式”，在右侧的文本框中输入“=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); 公

26、式中使用了对投标金额区域的三维引用: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，单击菜单格式条件格式;

27、 2)在弹出的“条件格式”对话框中，在“条件1(1)”下方的下拉列表中选择“公式”，在右侧的文本框中输入公式“=MOD(ROW()+COLUMN(),2)0”，此公式用于判断行号与列号之和除以2的余数是否为0，如果为0，说明行数与列数的奇偶性相同，不填充单元格底纹颜色，其他的则填充单元格为浅绿色;在条件格式中，当公式结果返回一个数字时，非0数字即判断为条件成立，0和错误值则判断为条件不成立，因此上面的公式也可以简写为:=MOD(ROW()+COLUMN(),2); 3)单击“格式”按钮，设置相应的颜色; 4)在“条件格式”对话框中单击“添加”按钮，在“条件2(2)”下方的下拉列表中选择“公式”

28、，在右侧的文本框中输入公式“=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

29、:$C$27,$C2)1，设置黄色。 27、多汇总方式的分类汇总:这里以下图中的数据列表为例，如果希望先按“部门”字段对“缴费基数”进行汇总，再按“部门”字段求出“年龄”的平均值，最后按“部门”字段求出“工龄”的最大值，方法如下: 1)选择数据列表中的任意一个单元格，如A2单元格，单击菜单数据排序打开“排序”对话框，在“主要关键字”下拉列表中选择“部门”字段，右侧的排序顺序选择“升序”选项，然后单击“确定”按钮完成分类字段的排序; 2)单击菜单数据分类汇总打开“分类汇总”对话框，如下图所示，在“分类字段”下拉列表中选择“部门”，在“汇总方式”下拉列表中选择“求和”，在“选定汇总项”列表框中勾选

30、“缴费基数”复选框，并勾选“汇总结果显示在数据下方”复选框，然后单击“确定”按钮关闭“分类汇总”对话框; 3)其它设置同步骤2，唯一不同的地方是取消勾选“替换当前分类汇总”复选框，最后的结果如下图所示(该图只截取了上半小部分): 28、使用合并计算建立分户报表:合并计算可以按类别进行合并，如果引用区域的行列方向均包含了多个类别，则可利用合并计算功能将引用区域中的全部类别汇总到同一表格上并显示所有的明细。例如2007年8月份南京、上海、海口、珠海4个城市的销售额数据分别在4个不同的工作表中，南京、上海销售表结构和数据分别如下图所示: 1)选中“汇总”工作表的A3单元格作为结果表的起始单元格，单击

31、菜单数据合并计算打开“合并计算”对话框; 2)在“函数”下拉列表中选择“求和”，在“引用位置”文本框中分别添加“南京”、“上海”、“海口”、“珠海”等4个工作表中的数据区域，并在“标签位置”组合框中勾选“首行”和“最左列”复选框，然后单击“确定”按钮即可得到各个城市销售额的汇总明细，如下图所示。 29、利用合并计算进行数值型数据核对:利用合并计算的“按类别”合并的功能，用户可以将烦琐的数据核对工作变得轻松，如下图所示的是新旧两组数据，现要将这两组数据的差异找出来，方法如下: 1)更改新旧数据表中“代号”字段的列标题，使两个数据表的第二段标题不相同。例如将旧数据表中的列标题“代号”改名为“旧代号”，将新数据表的列标题“代号”改名为“新代号”; 2)选中A17单元格作为结果存放的起始位置，在菜单栏上单击数据合并计算打开“合并计算”对话框; 3)在“引用位置”文本框中依次选取添加旧数据表的A2:B11区域和新数据表的D2:E13区域，在“函数”下拉列表中选择“求和”，在“标签位置”组合框中同时勾选“首行”和“最左列”复选框，然后单击“确定”按钮，结果如下图所示; 4)为了进一步显示出新旧数据的不