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

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

2、保护，那别人既看不到你的内容也没办法修改了。2、标题行的重复打印：1）单击菜单,文件,页面设置,，在打开的“页面设置”对话框中单击“工作表”选项卡;2）如果要指定在顶部重复的一行或连续的几行，则可单击“顶端标题行”文本框最右边的按钮，然后在工作表中进行相应的指定;3）如果要指定在左侧重复的一行或连续的几行，则可单击“左端标题列”文本框最右边的按钮，然后在工作表中进行相应的指定，如下图所示;4）单击“确定”按钮关闭“页面设置”对话框。|前时置_一J師II蠢趣同訥脚工磁顶端标题行®:，珀:gi匡j左端标题珈©:$止:|K7|打甲3、巧用右键和双击填充：如果用户先输入内容到一个单

3、元格，然后用鼠标右键拖曳此单元格，那么松开右键，就会出现一个快捷菜单，菜单上显示了本次填充可以选用的类型，甚至可以链接到“序列”对话框进行更复杂的设置，如下图所示：ABC2345Sgioli121415167当用户在数据区域中对某一列的内容进行填充时，不必从第一个单元格一直拖以超国齐也X.工氷日憤五世肚冃第范迪!_复制单元榕迄）戎序列方式埴充富仅壻充格式电】不带格式垢充电）等證序列.曳到最后一个单元格，只需要双击第一个单元格的填充柄即可。但是使用双击的方法进行填充的时候，填充到的最后一个单元格的位置则取决于左边一列中第一个空白单元格的位置（如果填充列是第一列，则参考右边列中的单元格）。例如在下

4、图中因为A5是空白单元格，所以B列中的填充只进行到B4单元格就会停止。4、快速输入对号、错号、平方与立方:下面的数字均必须在小键盘上输入。AB1f.2-34.四鳳E7六名称快捷键名称快捷键名称快捷键名称快捷键对号错号平方立方ALT+41420ALT+41409ALT+178ALT+1795、神奇的选择性粘贴:1）全部:在绝大多数情况下等效于常规的粘贴;2）公式:只复制原始区域的公式;3）数值:只复制数值，如果原始区域是公式，则只复制公式的计算结果;4）格式:只复制原始区域的格式;5）批注:只复制原始区域的批注;6）有效性验证:只复制原始区域中设置的数字有效性;7）边框除外:复制边框之外的所有内

5、容;8）列宽:从一列到另一列复制列宽信息;9）跳过空单元格:可以有效地防止原始区域中的空单元格覆盖粘贴目标区域中的单元格内容;10）转置:能够让原始区域在复制后行列互换;11）粘贴链接:将建立一个由公式组成的连接原始区域的动态链接;12）加:允许用户进行一次简单的数值运算。例如:选定任意一个空白单元格，然后按Ctrl+C组合键进行复制，按住Ctrl键单击所有的需要取消超链接的单元格。如果是一个单元格区域，可以先用鼠标单击并按住最外面的单元格，然后向上拖动选定整个区域，待光标由手的形状转变为十字形然后再松开鼠标按键，单击菜单,编辑,选择性粘贴,，在打开的“选择性粘贴”对话框中选中“加”单选按钮，

6、然后单击“确定”按钮，这样所有的被选定的超链接就都转换为普通文本了，如下图所示。6、创建动态名称:如果需要创建一个名称来引用C列中的数据，但又不希望这1htlp!/www.hnkyscicom2h'ttp:/mw.gm个引用区域包含空白单元格，在这种情况下可以创建动态名称，根据用户追加或删除数据的结果来自动地调整引用的位置，以达到始终引用非空白单元格区域的结果。创建动态名称的方法如下:1）单击菜单,插入,名称,定义,;2）打开“定义名称”对话框，在“在当前工作薄中的名称”文本框中输入“Date”，在“引用位置”文本框中输入公式:=OFFSET（Sheetl!$C$4,COUNTA（Sh

7、eet1!$C:$C）-1）,如下图所示，单击“确定”按钮。在当祈工柞溥中的店确葩Data引员也_,話亠_叨应sgi血*njg屯”*cdtnHkeshaiii114匚:舱而以上公式先计算C列中除了列标题以外的非空白单元格的数量，然后以C4单元格(首个数据单元格)为基准开始向下定位，定位的行数等于刚才计算出来的数据。下面可以在C列以外的单元格中通过计算来验证此名称的引用是否正确，比如在B1中输入公式:二SUM(Date)，如下图所示。A-Ic1岀库数量含廿：1490'区日期单号数量45月工日AW01jao&A1002切iisArBAl003瓯&月总日M004:帧S5月即日

8、A1G05sotrg5月閣日Al.0.0.61-20如果继续追加记录，名称“Date”的引用位置就会自动地发生改变，B2中的计算结果能够体现这一点，如下图所示。:宜'S'C1出阵数量台计：18502日期单号魏量45月1日Altol5胡汨:A1I1Q2&5胡日75月因日A1004390'&.胡靡日Al0052®:i5月购日Aldrin：130.L05J跖日A100Y310s115月肝日A10Q3诚-注意:以上公式只能正确计算不间断的连续数据，如果表格中的数据有空白单元格，那么动态名称的引用位置将发生错误。7、单元格中切换中英文输入法:例如选定A列

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

10、框中输入“二COUNTIF（A:A,A1）=1”，单击“确定”按钮。9、禁止重复报餐:某公司为方便员工中午用餐，在3家餐馆为员工定餐，员工让每位员工自己在工作表上报餐，方法为:在自己名字与日期交叉的单元格输入数字1，工作人员只需对相应的单元格区域求和就可以算出某家餐馆共有几位员工报餐，以便与餐馆结账，为了防止同一员工同一天在多家餐馆重复报餐，可以设置数据有效性来禁止，方法如下：1）选定B2单元格，单击菜单，插入，一，名称，一，定义，打开“定义名称”对话框;2）在“在当前工作簿中的名称”文本框中输入名称“Count”，在“引用位置”文本框中输入“二”，鼠标单击第1张工作表（鑫德海）的标签，按住S

11、hift键不放用鼠标再单击最后一张工作表（一品套餐）的标签，放3）选定“鑫海德”工作表的单元格区域B2:F26，单击菜单，数据，一,有效性，打开“数据有效性”对话框;4）选择“设置”选项卡，在“允许”下拉列表中选择“自定义”选项，在“公式”文本框中输入“=SUM（COUNT）=1”，并取消“忽略空值”复选框的勾选状态;5）切换到“出错警告”选项卡，在“样式”下拉列表中选择“停止”选项，在“标题”文本框中输入“注意”，在“错误信息”文本框中输入“请不要重复报餐”，然后单击“确定”按钮;6）重复步骤3,步骤5,为另外两个工作表设置相同的数据有效性。通过设置以上的数据有效性后，同一员工在同一天就不能

12、在多家餐馆重复报餐了。如A12单元格的“西门雪”在“鑫德海”报餐后，如果要在“禄鼎记”再报一次餐，Excel就会弹出“注意”对话框，阻止用户继续报餐。公式解析:名称Count是一个三维引用的名称，返回3个工作表的同一位置单元格的引用，B2单元格的有效性公式“=SUM（C0UNT）”即表示“二鑫德海B2+禄鼎记B2+一品套餐B2”。10、创建二级下拉菜单源为单个工作表:下图所示的是“查询”表的B1单元格创建下拉菜单，可供选择的选项为“烟”和“酒”，C1单元格能根据B1单元格所选择的不同内容产生不同序列内容的下拉菜单，并且下拉菜单中不能包含空格，g1）使用直接在有效性中输入序列内容创建下拉菜单的方

13、法，为“查询”表B1要创建这样的下拉菜单，方法如下:遒単.仰（元烦）H蓝需马羣荊底口SLQ歌盒中华58DHSDeI皇家礼咆TOG晚盘中越395TDOliftXO3J0處门珍品220烦nd聲番埼案对310註七匹狐1ES他!逼莆马警利5K上阀恃薜好零再n1叫藍芾马纠t»红石抑91TCiOnlVF屋喬VSDF245小龍貓172亦0«总鑼甬马ITS15ETDOal特醇肃马300芙蓉王21QCD酒品名:单价3带马董利症sio2）单击菜单,插入,名称,定义,弹出“定义名称”对话框，在“当前工作单元格创建序列为“烟”和“酒”的下拉菜单，如下图所示:薄中的名称”文本框中输入“PingMin

14、g”，在“引用位置”文本框中输入公式:=OFFSET（数据!$A$2,MATCH（查询!$B2,数据!$1:$1,）-1,COUNTA（OFFSET（数据!$A$2,MATCH（查询!$B2,数据!$1:$1,）-1,65535）,如下图12所示,3）选定“查询”表中的C1单元格，单击菜单，数据，一，有效性,打开“数据有然后单击“确定”按钮。在当繭工雅再中牺称（T；引用位査:OFFSETISAS2,.MA.TCHciiU!I&Z弱撰!$1Jl.：i-l.COUBTHmS勺效性”对话框;4）切换到“设置”选项卡，在“允许”下拉列表中选择“序列”选项，在“来源”文本框中输入“=PinMin

15、g”，然后单击“确定”按钮。5）在“查询”表的D1单元格中输入公式:“=SUMIF（数据!A:C,C2，数据!B:D）”。11、导入Word文档中的表格:Word文档中的表格不能直接导入Excel工作表中，不过用户可以采用，复制,粘贴，的方法将Word文档中的表格复制到Excel工作表中。但如果文档中的表格较多时，复制起来就会很不方便。这里介绍一下通过网页文件快速导入Word文档中的数据表格的方法。1）打开Word文档“示例5”，在Word工作窗口中单击菜单，文件,另存为，对话框，在“保存类型”下拉列表中选择“单个文件网页”，然后单击“保存”按钮将该文档另存为网页文件;2）单击菜单,数据,导入

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

17、数据，完成Word文档中表格的导入工作。E112、快速插入多个单元格:更快捷的方法是:先选定目标单元格区域，然后按住<Shift>键，把光标移动到选定区域的右下角，当光标变成分隔箭头时，如下图所示，再往右或者往下拖动，拖动的距离就等于插入单元格的数量，拖动的方向等于“活动单元格”移动的方向。的自定义数字格式就能够在不改变数值本身的同时对它们进行缩放，如下图所示。原始数值显示为代码i克明12367：19123.460/70.06巳2T按岂万縮疏数佢1234S612.3万讥%审"愷万貓放数值123466123.46千,&C千”:安千貓放数值125412.3405疫百编

18、放数值序改变即完成。3电G3B：BrnX713334AABBCCDD14、快速缩放数值:许多用户在工作中常常需要处理很大的数字，而利用下面15、单元格文本数据分行:在下图中，A1单元格是由一个由多个成语连接而成的字符串，各个字符之间没有间隔，现需要将这一字符串中的各个成语分成多行并排显示，方法如下:1）将A列的列宽调整到显示4个汉字的宽度;2）选中A1单元格，单击菜单编辑填充内容重排弹出“文本将超出选定区域”的警告窗口;3）单击警告窗口中的“确定”按钮即可得到分行结果。注意:使用这种方法也可以将多行数据合并成一行。16、多行多列数据转为单列数据:1）在下图中选中A1:C3单元格区域，连续按两次

19、Ctrl+C组合键打开“剪贴板"任务窗格;rElTXV147C25B11也7136'SS'53-2'3：3E92）双击E1单元格激活编辑状态，然后单击“剪贴板”中刚才复制的内容，这些内容将粘贴到E1单元格并显示在编辑栏中；3）在编辑栏中全部选取之前粘贴过来的数据，然后单击“常用”工具栏中的“复制”按钮；4）按ESC键退出单元格的编辑状态，然后选中E1单元格，单击“常用”工具栏中的“粘贴”按钮;5）将E列的列宽调整为显示单个字符的宽度，再单击菜单编辑填充内容重排，将会弹出“文本将超出选定区域”的警告窗口，然后单击“确定”按钮继续操作;6）数据内容重排后，转换后的

20、单列数据已经具备雏形，但单元格显示仍不完全，此时选中E列，重新将列宽宽度调大，以使其能够完全地显示数据内容，最后结果如下图所示。AHJ1T丄J耳§4&冒%7.6：-9-17、排序字母与数字的混合内容：1）在B1单元格中输入以下公式:二LEFT（A1,1）&RIGHT（OOO&RIGHT（A1,LEN（A1）1）,3）;2）把B1单元格中的公式向下复制到B10单元格选中B2单元格，然后单击“常用”工具栏中的“升序排序”按钮。注意:下图中，A列是原始数据，B列是套公式后得到的数据，C列选择性粘贴B列数值后排序的数据。下填充到C20单元格;2）选中C1单元格，单击“

21、常用”工具栏中的“升序排序”按钮出现“排序警告”对话框，选中“扩展选定区域”单选按钮后单击“确定”按钮即可得到排序结果；3）清除C列内容，最终得到删除空行、紧缩数据行的效果。AB匸代码.二止1二代2S9Q308414S449FALSE5TRUE4S9Q308414S449FALSE5出二吃TRUES£626156579139JtQD'5FALSE7TRUE19、快速制作工资条:1）在下图的J2:J11单元格区域依次填入数字1至10,再将同样顺序的10个数字复制到J11：J21单元格区域;2）选中J2单元格，单击“常用”工具栏中的“升序排序”按钮对数据区域进行升序排序;3）选中

22、A3:J120单元格区域，按Ctrl+G组合键打开“定位”对话框，选中“空值”单选按钮，然后单击“确定”按钮即可选中当前区域中的空单元格;4）输入公式“=A1”，按Ctrl+Enter组合键确认公式输入。丄DnEFHIr13-Li|：略槿¥申狒ir2i.的:J-巧叫a坦QE刖i珂曹S2I'TV1h11舟StI119111J.37T2E血LiBi.5iti1Z2A.5Ew*TJ7ICQSEEZ5XJJX-5B-空innLiOL52B011.9石n日仙KIQ口网加皓OfflD7H啦13132：g11A?Derg1ionE7555BU；7.1JQ*品1lSO23S皿隘011IQEO

23、ZS-i辽9U22Emm.$20、一次性删除所有的重复数据：1）在下图中的B2单元格输入公式:=IF（COUNTIF（A$2:A2,A2）1,1），复制公式向下填充到B10单元格;选中B2单元格，单击“常用”工具栏中的“降序排序”按钮;B列显示为1所对应的A列编号的重复1次以上的编号清除这部分数据即可得到不包含重复项的数据表。B1骑号E15TS0FAI5Ea.IF56LFALSE7B2&何心FAISE5168602-3FAISE厂I17J589FAI£E7E555Z1FJilSEWE?55211T；1655521:aF血21、了解高级筛选:在下图中的表格是一张包含了筛选条件区

24、域的数据列表，如果要将“人员类别”字段中为“经理人员”的记录通过高级筛选功能筛选出来，人员类別昭理人员具体步骤如下:.L274:序号姓名51肖剑7-8部门名称总餐理figj财务部市场部人员类别SSA52管理人员经理人员1,刊（.犯50D.D0000.日血CIO50.00BIO.DO'840.001）单击菜单数据筛选高级筛选弹出“高级筛选”对话框;2）在“方式”组合框中选中“将筛选结果复制到其他位置”单选按钮;3）然后将光标定位在“列表区域”文本框中，在表格内选取A4:F14单元格区域，此区域的地址会自动地填写在“列表区域”文本框中，此区域即为当前数据列表所在的单元格区域；4）与步骤3类

25、似，如下图所示，在“条件区域”文本框中选取B1:B2单元格区域，在“复制到”区域中选择A18:F18，最后单击“确定”按钮得到筛选结果;确定取消则表区域条件区1或篡制到:在原有区域显示筛选结果逻I将筛选结杲复制到苴他位置©I选择不重复的记录OJ5）以上面同样的方式可以继续筛选出数量大于900的结果。22、使用“视图管理器”简化重复的筛选操作:在下图的数据列表中，如果希望根据不同的工厂名和季节条件筛选出相关的记录，并且筛选条件在不断地发生变E1E弟四零-4200063Z870ggggjgai5398&20653加L029J5510S9655化，那么运用“视图管理器”功能可以大大

26、地简化高级筛选的设置工作。1）设置数据列表筛选区域，通过数据有效性将“工厂名”设置为可选择的下拉菜单;2）在条件区域选择条件。使用高级筛选，并选择在原数据区域显示筛选结果，如下图书所示;aniLD匚BE1ir客対匹1戏&T.rS2000丄.H窑弟一伞我二幸.黑團幸&&工厂56-1d65ZSTWO日ir2296涵曲19&工L239537&5GS203）单击菜单视图视图管理器弹出“视图管理器”对话框;4）单击“添加”按钮弹出“添加视图”对话框，定义“名称”为“1”，然后单击“确定”按钮关闭对话框，此时当前窗口设置即被定义为视图名称1;当用户改变筛选条件后，如

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

28、的表是一张包含“表2”的数据列表，“表2”是“表1”的子集（每个编码都只有一条记录），现在要将“表1”中不包含于“表2”中的数据拆分出来，此时可以借助高级筛选功能实现，方法如下:ABCEFK工11轰12扁码种类飯色樹量编码种类数量3甲34DA-D-1Q乙黄75GI-I-5.4红71CC-D-70甲红S65丙85FO-MB茗36'&CU-C-GD甲57I0-E-7Q萤21）在A10单元格输入“筛选条件”，然后在All单元格输入筛选条件公式:=ISNA（MATCH（A3,$F$3:$F$5,0）;2）选中A2:D7单元格区域，单击菜单数据筛选高级筛选弹出“高级筛选”对话框，然后按下

29、图所示进行设置;表拆分的目的，如下图所示。ID11TRUE取消厂r；f$磁:釦$T列表区憾尬食件区域©变制爭1):?-二Vs阳曰Lff-$A$10：iUII电曙If：卿叙4选择不重复的记录迅3)单击“确定”按钮关闭“高级筛选”对话框即可得到筛选结果，实现数据列:>13:结累袤S5"T117IE-P-.7&飞-CWlQ'la-E-79公式思路解析:=ISNA(MATCH(A3,$F$3:$F$5,0),该公式通过MATCH函数，在表2”的编码字段中查找“表1”中的编码，如果“表1”的编码包含在“表2”当中，则返回数值，如果没有找到则返回错误值。然后利用I

30、SNA函数返回MATCH函数计算结果中的错误值，表示此编码不包含在“表2”之中。24、标识中标公司和中标金额:下图显示的是10家公司对5个工程项目的投标情况表，为了更醒目地显示每个项目的中标公司(此处假定金额最大者判为中标)及其中标金额，可以使用条件格式进行标识，方法如下:a.D£a揺标结果公布表¥顷目A项目血顶吕E0iWOWOi£；J卿収1LiSflO4112DOOOIO0DCKIQ103EODIJ12S00X1230EOD$1270(K0EOOOO12200)01050090L30OCODL1ED0CDlEQMdtILEfflOC11MOWiniCCOD戊:公

31、司125OCO0£190000慚mwlneonc1i?3Li'i!)9101OCODt190000115O3D012OTOCOIDSOKiO甘iseowjoiMOMD怕MOOQILMOK10L350GOD127000012700001090DD0U0OCOD11I£40(K)11500001210000lOCOOL1E0TODI13DWQULCOOC12550K)10WQOQ1) 选定单兀格区域B3:F12，单击菜单格式条件格式弹出“条件格式"对话框;2)在“条件1(1)”下方的下拉列表中选择“公式”，在右侧的文本框中输入“二B3二MAX(B$3:B$12

32、”；3) 单击“格式”按钮，在弹出的“单兀格格式”对话框中选择“字体”选项卡，在“字形”列表框中选择“加粗”，在“颜色”下拉列表中选择“白色”，然后选择“图案”选项卡，设置“单兀格底纹”颜色为“黑色”；4) 单击“确定”按钮关闭“单兀格格式”对话框，然后单击“条件格式”对话框中的“确定”按钮关闭“条件格式”对话框。若要对中标公司也进一步标识，可以在A3:A12设置条件，方法和B3:F12的条件格式的设置相同，公式为:=OR(B3:F3=SUBTOTAL(4,OFFSET($B$3:$B$12,COLUMN($B:$F)-2)；公式中使用了对投标金额区域的三维引用：0FFSET($B$3:$B$

33、12,C0LUMN($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)”下方的下拉列表中选

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

35、UMN(),2)=0”；5) 单击“格式”按钮，设置相应的颜色。26、标识重复值:下图是某电脑城一些电脑设备型号表，现要求把设备型号重复的记录全部用玫瑰红底纹标识出来，方法如下:Aff匚1世片编号设备常称设备型号ZA砌別TGT6A3itliGlE1ai76-KT4gOSlYfi台式初1818-1025fll35QI81TS-ITS&13556台式柄1EVOMINT8752&434-45CjJF8L1407BO台式赫1G抵如9九鼎用91a434-67CIQ'500冃式和1E434-&7C11?S0EC316824-Lir120513台式初.GK11013-

36、7;0514台式和2Z5S-D6CK0515台罚*1ai34-&£C帀JL445E9台m渊15434-&7CLiefco5i7台式初1D460Dn冋丁餌18434-82C18fell15324-LECIS44253台式初T500CkO8387L68Z4-53C211479台式和604S-I022.7980台比折1D31N囱*14454"占式眾1&434-&7C241417台式初1KE320合式U1tS4'34-a2C2605295台式撕8307-81C汀ii'0469?11) 出现重复就标识:输入公式二COUNTIF($C$2

37、:$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，设置黄色。值，1UU_12取后按“部门AIE|C!瓮養；些期孚庭SU*!年雜工閒2300«F25OD30002&00250025QD200023QD2300soapSood”字段求出“工龄”的最大值，方法如下:nft

38、齟.開Sr邑茂隹生吿36zag291(?"SG1?a26T21目5T45'2627、多汇总方式的分类汇总:这里以下图中的数据列表为例，如果希望先按“部门”字段对“缴费基数”进行汇总，再按“部门”字段求出“年龄”的平均1）选择数据列表中的任意一个单元格，如A2单元格，单击菜单数据排序打开“排序”对话框，在“主要关键字”下拉列表中选择“部门”字段，右侧的排序顺序选择“升序”选项，然后单击“确定”按钮完成分类字段的排序;2）单击菜单数据分类汇总打开“分类汇总”对话框，如下图所示，在“分类字段”下拉列表中选择“部门”，在“汇总方式”下拉列表中选择“求和”，在“选定汇总项”列表框中勾选

39、“缴费基数”复选框，并勾选“汇总结果显示在数据下方”复选框，然后单击“确定”按钮关闭“分类汇总”对话框;3）其它设置同步骤2，唯一不同的地方是取消勾选“替换当前分类汇总”复选框，最后的结果如下图所示（该图只截取了上半小部分）：匚荀务j厂j£_-.-.y|1Jt各狂J1民跌.*好珏景；年髀工曲SL10累盼干1ML生严阴IIT虚I.丢稱出IC內EJ&饮&F女S爭fe25冊筋捋劉淫斋JOOO.一戲血._25&0|<*产111130028、使用合并计算建立分户报表：合并计算可以按类别进行合并，如果引用区域的行列方向均包含了多个类别，则可利用合并计算功能将引用区域

40、中的全部类别汇总到同一表格上并显示所有的明细。例如2007年8月份南京、上海、海口、珠海4个城市的销售额数据分别在4个不同的工作表中，南京、上海销售表结构和数据分别如下图所示:IKIA壬：2DDT年甘月鴨希菩昔况表A_L日120078月舒销普昔况表品种一卜海销售颌?4a/2品亍000勒9晶种南帛销售瓠£C产m7.QQQ.QCJ4段产品6.650.006D产HH1,000.0GE匚产話B.000.00Q产'AB,000OtT.SD产話L.D0U.001）选中“汇总”工作表的A3单元格作为结果表的起始单元格，单击菜单数据合并计算打开“合并计算”对话框;2）在“函数”下拉列表中选择

41、“求和”，在“引用位置”文本框中分别添加“南京”、“上海”、“海口”、“珠海”等4个工作表中的数据区域，并在“标签位置”组合框中勾选“首行”和“最左列”复选框，然后单击“确定”按钮即可得到各个城市销售额的汇总明细，如下图所示。AB-I'E2007年8月分户销售18况表L海销售章腕销售芬上翩肖售簸琲毒申肯售番6.GSO.OOU,300OJSAF品5,000M5,DQO.0061:产品00.0016,0004HEODO.MEOOD.tMT沪品£.ODD00LOGO肌.5.沪品氐畑00F产品1,200.0029、利用合并计算进行数值型数据核对:利用合并计算的“按类别”合并的功能，用

42、户可以将烦琐的数据核对工作变得轻松，如下图所示的是新旧两组数据，现要将这两组数据的差异找出来，方法如下:ADE1.新数据£旧代号，新代号陈新B100001王小二0100003丄李浩0100002B100007姚明0100003陈新61000016-李新810C004李明8100005T李明0100005李浩S10U002%B100006冋军SL00009岂8100007姚明B10000310王小二810C008李新810001411何军8100009&100C0612B10001013赵颐S1000111）更改新旧数据表中“代号”字段的列标题，使两个数据表的第二段标题不相同。

43、例如将旧数据表中的列标题“代号”改名为“旧代号”，将新数据表的列标题“代号”改名为“新代号”;2）选中A17单元格作为结果存放的起始位置，在菜单栏上单击数据合并计算打开“合并计算”对话框;3）在“引用位置”文本框中依次选取添加旧数据表的A2:B11区域和新数据表的D2:E13区域，在“函数”下拉列表中选择“求和”，在“标签位置”组合框中同时勾选“首行”和“最左列”复选框，然后单击“确定”按钮，结果如下图所示;16核对菇果一旧代号新代号陈新3100001S100001019姦詹B100D02姚明S1D0003于新3100C0481000025100003斗61000142Z圭日冃3100005傑

44、嗥B1D000681000050SI00006.24.高B1000U7""ibiiIm：i.piqifqiM"口王浙二8100000910000T1严*Wl81D0008-42&冋军91.0000961000090車孝萍61000101258100011i.4）为了进一步显示出新旧数据的不同之处，可以在D18单元格中输入以下公式:二N（B18<>C18），并复制公式向下填充至D28单元格；5）补齐标题名称，选中A17:D28区域，在菜单栏上单击数据筛选自动筛选;6）单击D17单元格中的自动筛选标记，然后选择筛选值为“1”的记录即可得到新旧数据的

45、差异核对结果。30、利用数据透视表统计考试分数:利用数据透视表“字段设置”中不同的汇总方式，用户还可以对字段中的数值型数据进行汇总统计，例如求最大值、最小值、平均值等。例如要对下图所示的“各班成绩”表进行统计，求出各班级分数的最大值、最小值、平均值，方法如下:Aa-1d腕15常羽片側羽5TD班65&一血疏IQ7一贰泸B一1竦.jJj-纹6一齢2710一間冼11一血刷12-T?）廉S61）选中数据源“各班成绩”工作内的任意一个数据区域，单击数据数据透视表和数据透视图;2）在弹出的“数据透视表和数据透视图向导3步骤1”对话框中单击“完成”按钮生成一个空白的数据透视表，显示“数据透视表字段列

46、表”窗口;3）将“数据透视表字段列表”窗口中的“班级”字段拖曳至数据透视表的“列字段”位置，如下图所示：1一厂iei）i丄_71l匚H«已成绩4）将“数据透视表字段列表”窗口中的“成绩”字段拖曳至数据透视表的“数据项区域”，再重复两次操作;5）选中A5单元格（求和项:成绩），单击右键，在弹出的快捷菜单中选择“字段设置”命令，在弹出的“数据透视表字段”对话框中的“汇总方式”列表框中选择31、在数据透视表中添加计算项:数据透视表创建完成后，用户无法更改或者移动数据透视表中的任何区域，也不能在数据透视表中插入单元格或是添加公式进行自定义的计算。如果需要在数据透视表中创建自定义的计算项目，必

47、须使用“添加计算字段”或“添加计算项”的功能。下图展示了一张已经创建成功的数据透视9関500241440込1商沁贬表，下面通过添加计算项来达到对比“国内”与“进口”产品各年的差异的目的。1）选中数据透视表中的“类别”按钮标题，然后单击“数据透视表”工具栏中A.S：!CDJ23求和顶:数量缈,14年份卜国内进口合计.5曲血乞34口1394-2口2342003-100/447'泗137.x!S嗣.13100羽巳100t葩5,200200E1的87242,099'L貶871总计的“数据透视表公式计算项，如下图所示:敷话腳叢®小空甌i电整I|.箱團i些u设置报告格f血数据懂视

48、图©刪数据透观衷商导睚If刷新数据®|脱机垃淞也）._隠爾匹"M盟>|._粗及显示明细数据晏式邮昨-也宇段设置®分拱汇总包计宜字段的.计算项（X）二我解汝序.虽I列出益式©2）弹出“在类别'中插入计算字段”对话框，在“名称”文本框中输入“差额”，把光标定位到“公式”文本框中，单击“字段”列表框中的“类别”选项，接着双击右侧“项”列表框中出现的“国内”选项，然后输入减号“”，再双击项列表框中的“进口”选项，如下图所示:合+帼人事栓囱亦刼莎|名称划邊輛分？：也1:m-讲口密改叫3）单击“添加”按钮，最后单击“确定”按钮关闭对话框。此时

49、数据透视表的列字段区域中已经插入了一个新的项目“差额”，其数值就是“国内”字段数据与“进口”字段数据的差值。但是这里会出现一个问题，数据透视表中的行“总计”将汇总所有的行项目，包括新添加的“差额”项，因此其结果不再具有实际意义。为了使行“总计”恢复实际的统计功能，需要继续对数据透视表进行设置;4）在数据透视表的任意一个区域单击右键，在弹出的快捷菜单中选择“表格选项”命令;5）在弹出的“数据透视表选项”对话框中取消勾选“行总计”复选项框，然后单击“确定”按钮关闭对话框;6）重复步骤1继续添加计算项。将国内和进口进行合计，最后的结果如下图所示。B695,002B；34Q进口駁243145.00010出刖3282.5P21喝前口沌m149,S722CKESM32004£DCiE13Q,W咨tco运勢g412.4-4Q含汁20.234绅1ST1595,200旧乙8712.107,44232、切换显示公式和运算结果:单击菜单,工具,选项,弹出“选项”对话框，如下图所示，在“视图”选项卡的“窗口选项”组合框中勾选“公式”复选框，这样用户在单元格输入公式时显示的则是公式本身而不是公式的运算结果。此外用户还可以使用Ctrl+'组合键（右撇号，一般在键盘上位于数字1左侧的一个键）快速地切换该选项。画表飯色1:视图重