Excel表格汇总.docx

Excel表格汇总.docx

《Excel表格汇总.docx》由会员分享，可在线阅读，更多相关《Excel表格汇总.docx（111页珍藏版）》请在冰豆网上搜索。

Excel表格汇总

函数公式解决筛选难题

　　某天，有一同事拿着一份成绩表来找我寻求帮助。

原来这是一份Excel格式的全市对口高考成绩表，整个工作表中除了处于A列的准考证号字段外，其他字段就是各科的考试成绩以及总分成绩。

现在的要求是从这份工作表中将本校学生的成绩筛选出来。

但现在的问题是当初排准考证号的时候是各个学校的学生间隔排列的，只知道本校考生的准考证号都是偶数。

因此，只有把所有的偶数考生成绩筛选出来，才有可能最后解决问题。

　　可是，怎样才能把所有的偶数学生全部筛选出来呢？

想了“分类汇总”、“自动筛选”、“高级筛选”等可能的功能，但都没有什么结果。

总不能要我一行一行地逐个选取吧？

这可是有着一千多考生的工作表呀，这么选的话，岂不把我的眼都要弄瞎了？

不行，还得想个好办法。

　　对了，既然要根据准考证号来选取其中的偶数，那么是不是可以利用取余数函数来解决问题呢？

Excel中MOD（Number，Divisor）函数可以返回两数相除的余数，而拿准考证号除2，所得的余数不是0就是1吗？

这样的话，我们再把余数为0的全部筛选出来，问题不就解决了吗？

　　但仔细看看，问题还有两个：

一是准考证号是首位为0的14位数，在Excel中这样的数字运算起来是可能有其他问题出现的；第二个问题是准考证号是文本格式，根本不能直接拿来运算。

解决的办法很快也找到了，第一个问题我们可以使用RIGHT函数截取最后两位数进行运算，这对于余数是没有任何影响的。

用RIGHT函数截取出来的也是文本，所以，还得使用VALUE函数将它转换成数字。

这样，第二个问题也就不是问题了。

　　办法有了，实现起来就简单多了。

在工作表的最后（G列）添加“识别符”字段。

点击G2单元格，输入如下公式：

=MOD（VALUE（RIGHT（A3,2））,2），按下回车键。

您看，结果是不是出来了？

选中该单元格，将鼠标移到单元格的填充句柄处，向下拖动至最后一行，自动计算出全部结果。

剩下的事情就是在这一列中筛选出所有为0结果，我就不再罗嗦了。

EXCEL中高级筛选条件的写法

　　EXCEL电子表格软件的高级筛选是一项功能非常好用的数据筛选功能，利用高级筛选，可以从数据清单中按照某些复杂的条件来查找符合条件的记录，它的操作过程很简单，但操作的关键是写好筛选条件，下面就来讲一讲如何书写筛选条件。

　　书写筛选条件时要先划分一片条件区域，条件区域可以选择数据清单以外的任何空白处，只要空白的空间足以放下所有条件就可以。

写条件时要遵守的规则是：

　　1、要在条件区域的第一行写上条件中用到的字段名，比如要筛选数据清单中“年龄”在30岁以上，“学历”为本科的职员，其中“年龄”和“学历”是数据清单中对应列的列名，称作字段名，那么在条件区域的第一行一定是写这两个列的名称（字段名），即“年龄”和“学历”，而且字段名的一定要写在同一行。

　　2、在字段名行的下方书写筛选条件，条件的数据要和相应的字段在同一列，比如上例中年龄为30岁，则“30”这个数据要写在条件区域中“年龄”所在列，同时“本科”要写在条件区域中“学历”所在的列。

　　那么在写条件时，一般都同时有多个条件，这些条件是个什么关系，又怎么在条件区域中分布呢？

　　我们平时所用的逻辑条件，如果有多个，它们之间总是会存在两种关系，一种是“或”关系，一种是“与”关系。

　　“或”关系是指几个条件中只需有一个成立，结果就会成立的情况，比如一句话“小张和小王，你们谁来把你们的邮件取回去。

”，这样表达的是他们两个人的邮件要往回取，“去人取”是条件，“取回邮件”是结果。

这里存在三个条件，一个是“小张去取”，一个是“小王去取”，再一个是“两个人都去”，那么在这三个条件中，只要有一个成立，结果“取回邮件”就会成立，那么这些条件之间的关系就是“或”关系，当然在考虑这个关系的时候，我们只要记作“小张去取或小王去取”就可以了，表示两个条件中，只要有一个成立，结果就成立，当然两个都成立，结果更会成立了。

　　“与”关系是指在几个条件中，必须这些条件都成立，结果才成立的情况，比如一件事“如果明天不下雨也不刮风，那我就出门”，这里有两个条件，一个是“不下雨”，一个是“不刮风”，只有这两个条件都成立，结果“我出门”才会有，那么我们就称这两个条件为“与”关系。

可以记作“不下雨与不刮风”，表示两个条件要同时成立结果才会成立。

　　那么回到高级筛选中，在具体写条件时，我们要分析好条件之间是与关系还是或关系，如果是与关系，这些条件要写到同一行中，如是是或关系，这些条件要写到不同的行中，也就是说不同行的条件表示或关系，同行的条件表示与关系。

　　说这么多，不知你听着有什么感觉，拿上面举的那个例子来说，“年龄在30岁以上”和“学历是本科”是两个条件，在题目的含义中我们看出这两个条件要同时成立，是与关系，所以条件区域写法如下表

年龄 

学历 

 >30  

 本科

把两个条件写在一行，表示他们是与关系。

　　这里第一个条件“>30”写的时候在前面加了一个“>”号，表示在30以上，我们平时用的不等关系有大于、小于等几种，它们使用的符号如下表所示：

大于 

>

 小于

< 

 大于等于

 >=

 小于等于

 <=

 不等于

 <>

如果是等于关系，则直接写值，不需加符号。

　　另外要注意的是，这些符号必须是英文半角符号，也就是说最好在英文状态下输入，不可以是全角符号，否则EXCEL不会认识它们。

下面举几个具体的实例，看一下筛选条件的写法：

　　1、选择出语文、数学、英语三门成绩中至有少一门不及格的学生。

　　分析：

三门课程中至少有一门课程不及格，我们可以这样理解：

一位学生，如果语文不及格就选出来，或者数学、或者英语不及格都把他们选出来，那么这样三个条件，很显然是或关系。

条件区域写法如下：

 语文

数学 

英语 

 <60

<60 

<60 

　　2、筛选出语文、数学、英语三门课程都及格的学生。

　　分析：

三门课程都及格，要求语文、数学、英语同时在60分以上，这些条件是与关系。

条件区域写法如下：

 语文

数学 

英语 

 >=60

>=60 

>=60 

　　3、筛选出语文、数学、英语三门课程中只有一门不及格的学生。

　　分析：

只有一门不及格，我们可以这样考虑：

如果语文不及格，肯定要数学、英语及格，如果数学不及格，肯定要语文、英语及格，如果要英语不及格，肯定要语文、数学及格。

这样三个大条件，每个大条件中又包含三个小条件，这些条件是个什么关系呢，我们先分析其中的一个大条件“语文不及格，数学英语及格”，这个大条件中有三个小条件，三个小条件要同时成立才符合我们的要求，所以它们之间是与关系，那么三个条件之间只要有一个成立，该学生就是只有一门不及格者，所以它们之间是或关系，这样我们写出条件区域如下所示：

 语文

数学 

英语 

 <60

 >=60

 >=60

 >=60

 <60

 >=60

 >=60

 >=60

 <60

　　4、筛选出“系别”为英语系、中文系，“年龄”在20岁以上的“籍贯”是北京、天津的学生。

　　此条件区域写法如下，读者可以自己分析条件的含义。

 系别

 年龄

 籍贯

 英语系

 20

 北京

 英语系

 20

 天津

 中文系

 20

 北京

 中文系

 20

 天津

　　其实条件写法并不难，只要你把所需的问题分解成最小的逻辑条件，把条件之间的逻辑关系弄清楚，就可以写出正确的条件区域。

弹指之间输入特定文本

　　Excel让我们能够轻松处理各种各样的表格，大大提高了我们办公准备就绪，但有时常常要输入一些特定的文本（如单位名称等），有没有什么快捷的方法来准确无误地输入这些特定文本，其实Excel提供了多种方法来方便我们实现高效输入特定文本。

　　1.剪贴板法Office拥有更多强大的剪贴板，我们可以利用这一功能反复输入一些特定的文本。

首先把要多次输入的文本复制到剪贴板，在要使用时就可在右边的剪贴板窗口中选取要输入的文本即可。

　　2.查找替换法在Excel工作表中可能要多次输入相同的文本，尤其是如果要在多处使用一些特殊符号的话，那就非常麻烦，不过以通过“编辑”菜单中的“查找”功能来实现。

具体操作方法：

首先在要输入这些符号的单元格中输入一个代替的字母或单词（如WMG注意：

该字母/单词不能与其他单元格中的相同），然后直接按Ctrl+H快捷键来打开“替换”对话框，在“查找内容”栏内输入代替的东东“WMG”，在“替换为”栏内输入“智创工作室 亦可”，再按“替换全部”按钮即可。

（图一）

　　3.自动更正法OFFICE提供了“自动更正”功能，通过“工具”菜单中的“自动更正选项”打开自动更正窗口，在“替换”栏内填入“WMG”，在“替换为”栏内填入“智创工作室亦可”，单击“添加”后按“确定”返回。

以后只要在单元格中输入“WMG”以及后续文本或者按“Enter”键后，系统会自动将其更正为“智创工作室 亦可”。

（图二）

　　4.巧用快捷键我们可以使用快捷键来高效输一些文本，先按住Ctrl键的同时，用鼠标左键选定多个不连续的单元格（或区域），然后输入文本，再按下“Ctrl+Enter”快捷键，这样就可将该文本同时输入上述选定的单元格中。

　　5.函数合并法对于经常需要输入的一些单位的名称（如“娄底市电广网络公司”，“娄底市信息网络公司”等），通常也可用Excel提供的CONCATENATE函数（功能：

将多个文本字符串合并成一个）来实现这些特定文本的输入。

具体操作方法：

假设在A1和A2单元格已经输入了“娄底市”和”网络公司”，而我们要在C1中输入“某某市电广网络公司”，那就在C1单元格中输入公式：

=CONCATENATE（A1,D2,A2），而以后我们只要在D2单元格中输入“电广”，则C1单元格中将自动填充文本“娄底市电广网络公司”，若在D2中输入“信息”则C1自动填充为：

“娄底市信息网络公司”。

　　6.选择列表法 假设要需要输入的文本在同一列中前面已经输入过，并且该列中没有空单元格，我们就可以右击下面的单元格，选“选择列表”选项，则上面输入过的文本以下拉菜单形式出现，用左键选中你需要的文本，即可将其快速输入到选定的单元格中。

　　7.格式定义法通过我们在做报表时需要给大量的数值加上单位（如“万元”等），这时我们就可通过“自定义”单元格格式的方法来达到自动给数值添加单位的目的：

我们选中需要添加单位的单元格，点击“格式”菜单中的“单元格”命令即打开了“单元格格式”对话框，切换到“数字”选项卡片中，选中“分类”列表框中的“自定义”，然后在“类型”栏内输入“#万元”，单击“确定”按钮返回，以后在上述单元格中输入数值后即会自动添加“万元”两字在其后面，不过此格式定义法仅仅能够适用于数值。

（图三）

有了这些快速输入文本的方法后，工作效率是不是提高啦！

听着就行让Excel报账 

　　许多人经常要用Excel来统计一些数据，统计完成后还要对数据进行校对，但是这么多数据怎样才能不出差错地进行校对呢？

其实我们可以用一下Excel自带的“文本到语音”功能，让软件通过发声自动给你报账。

　　打开Excel，点击“工具→语音→显示文本到语音工具栏”，由于这个功能在安装Excel的时候不是默认安装的，所以在第一次使用时会提示插入Office安装光盘来安装这个功能。

　　安装完成后打开“文本到语音”工具栏，先在数据文件中用鼠标选择要朗读的第一个数据，接着根据数据的排列情况来选择是“按行”还是“按列”来进行朗读，设置完成后点击工具栏最左面的“朗读单元格”按钮就可以了。

在朗读的时候被朗读到的单元格会以选中状态显示，而且还可以流利地朗读中文，英文是一个字母一个字母朗读的，这样就可以校对英文单词的拼写是否正确。

（图1）

　　该功能还可以一边输入数据一边来进行语音校对，只要点击一下工具栏最右面的“按回车开始朗读”按钮，使其为选中状态，这样在完成一个单元格的输入后按回车，Excel就会自动来朗读这个单元格中的内容。

（图2）

　　一般朗读默认的是男声的，如果你听腻了，可以通过系统里的设置把男声改为女声，打开控制面板中的“语音”项，在里面的“语音选择”来选择一下语音，然后点击下面的“预览声音”就可以听效果了，而且还可以设置语音的朗读速度，完成后点击“确定”按钮就可以了。

将Excel的文本数字转换成数字

　　在Excel中，系统将前面带有半角单引号的数字视为文本对待，而且为了某些需要，用户可以通过“格式”菜单中的“单元格”命令，将数字设置为文本格式。

然而，现在我们却需要把工作表中文本格式的数字转换成数字，那么以下方法可以一试。

　　1．一次转换一个单元格

　　在“工具”菜单上，单击“选项”，再单击“错误检查”选项卡（一定要确保选中了“允许后台错误检查”和“数字以文本形式存储”复选框）。

选中任何在左上角有绿色错误指示符的单元格。

在单元格旁边，单击出现的按钮，再单击“转换为数字”。

　　2．一次转换整个区域

　　在某空白单元格中，输入数字“1”。

选中该单元格，并在“编辑”菜单上单击“复制”命令。

选取需转换的存储为文本数字的单元格区域。

在“编辑”菜单上，单击“选择性粘贴”。

在“运算”下，单击“乘”。

单击“确定”按钮。

　　3．处理特殊财务数字

　　一些财务程序显示负值时在该值右边带有负号“-”。

要将此文本字符串转换为数值，必须返回除最右边字符（即负号）以外的所有文本字符串字符，然后乘以“-1”。

例如，如果单元格A2中的值为“156-”，那么公式“=LEFT（A2,LEN（A2）-1）*-1”将文本转换为数值“-156”，然后用上面的方法操作。

把Excel表格转换为图片

许多报纸和杂志都介绍过在Excel中，同时按住Shift键点击“文件”菜单，原来的“关闭”菜单项就会变成“全部关闭”。

如果我们在按下Shift键的同时点击“编辑”菜单，原来的复制和粘贴就会变成“复制图片”和“粘贴图片”。

利用这一功能，我们可以将一个数据表以图片的形式进行复制，从而将其转换为图片。

方法如下：

　　首先选中需要复制成图片的单元格区域，然后按住Shift键依次选择“编辑→复制图片”命令，接着弹出“复制图片”窗口，选择“图片”单选项后点击“确定”按钮，这时就将选定的表格区域复制成图片了。

最后复制到目标只需直接选择“粘贴”命令即可（或者按Shift键再选择“编辑→粘贴图片”命令）。

我们还可以将其在Word中进行粘贴。

　　另外，在复制图片时如果选择了“如打印效果”单选项，在粘贴的时候如果表格没有边框，复制后的图片也不会出现边框。

数据格式我做主玩转Excel“单元格格式”

　　在Excel中，使用最多的也是最常见的一个对话框，恐怕要数“单元格格式”对话框了。

下面，我们就通过功能描述、使用方法、实例剖析、使用技巧几个方面来彻底认识一下它。

相信看过这个专题，大家会对Excel数据表的格式使用更加得心应手。

　　在Excel中选中单元格（区域），执行“格式→单元格”命令（或者右击鼠标，选“设置单元格格式”选项），即可打开“单元格格式”对话框（参见图1）。

图1

　　一、“数字”标签（图1）

　　[纸上谈兵]

　　①根据单元格中实际输入的数据，先选定相应的数据类型（如“数值”等）；

　　②再配合数据类型，进一步设置数据的具体格式；

　　③设置前的情况；

　　④设置后的情形。

　　[实战演习]身份证号码轻松输入

　　大家知道，在Excel默认情况下，当单元格中输入的数字位数超过11位（不含11位）时，系统将以“科学记数”格式显示输入的数字；当输入的数字位数超过15位（不含15位）时，系统将15位以后的数字全部显示为“0”。

这样一来，如果我们要输入身份证号码（15位或18位），身份证号码就不能正确显示出来了。

此时，我们就请“数字”标签出山，来解决这一“难题”。

　　1、完整显示出身份证号码

　　选中需要输入身份证号码的单元格（区域），打开“单元格格式”对话框，进入“数字”标签，在“分类”下面选中“文本”选项，确定退出，再输入身份证号码即可。

　　[小技巧]

　　在输入身份证号码时，在号码前面加上一个英文状态下的单引号“”，即可让身份证号码完全显示出来（不要担心，确认后，该单引号是不会显示出来的）。

　　2、让格式自动添加固定的数字

　　大家知道，身份证号码的前6位，是省、市、区（县）的代号（如“340505”等），如果持证人是同一区（县）的，输入这样的的身份证号码，可以让“数字”标签帮你减轻输入的工作量：

选中需要输入身份证号码的单元格（区域），打开“单元格格式”对话框，进入“数字”标签，在“分类”下面选中“自定义”选项，然后在右侧“类型”下面的方框中输入：

"340505"@，确定退出。

以后输入身份证号码7位及以后的数字（如“198903040611”）后，系统会自动在前面加上相应的代码，形成完整的身份证号码（如“340505198903040611”）。

　　二、“对齐”标签

　　这个标签控制数据的三个属性——对齐方式、显示方向、控制文本（参见图2、3、4）。

　　[纸上谈兵]

图2

　　1、（图2）①设置数据左右对齐方式（默认为靠左）；②设置数据上下对齐方式（2000默认为“靠下”，XP默认为“居中”；③设置数据的缩进（也就是通常所说的退格）；④设置前；⑤设置后。

　　2、（图3）①将数据设置成竖排格式；

图3

　　②③均用于设置数据的倾斜方向，可以通过拖动②区中指针设置，也可以直接在③区中输入具体数据设置；④设置前；⑤设置后。

　　3、（图4）①选中此选项后，当单元格中的文本一行容纳不下时，系统会自动将文本转行排列；

图4

　　②选中此选项后，当单元格中容纳不下数据时，系统会自动将数据缩小排列（上一项选中后，此项不能无效）；③选中此选项后，可以将选中的多个单元格合并成一个单元格；④设置前情况；⑤设置后情形。

　　[实战演习]　让姓名排列得整整齐齐

　　时下，员工的姓名，以3个字和2个字的居多，也是4个字或更多字的。

可以用“分散对齐”或“缩小字体填充”来让它们排列整齐。

　　1、同时选中输入3个字和2个字姓名的单元格区域（需要用Shift和Ctrl帮忙选择），打开“单元格格式”对话框，进入“对齐”标签，按“水平对齐”右侧的下拉按钮，选中“分散对齐”选项，确定退出。

　　2、同时选中输入4个字和更多字姓名的单元格区域，打开“单元格格式”对话框，进入“对齐”标签，选中“缩小字体填充”选项，确定退出。

　　[特别提醒]遗憾的是，上述两项功能不能同时使用，希望微软能在下一个版本中加以改进。

　　三、“字体”标签（图5）

图5

　　[纸上谈兵]

　　①拉动滚动条，设置字体；

　　②设置字型；

　　③拉动滚动条，设置字号；

　　④点击下拉按钮，设置下划线；

　　⑤点击下拉按钮，设置字符颜色；

　　⑥设置上、下标、删除线等特殊格式；

　　⑦预览设置效果；

　　⑧上标设置前；

　　⑨上标设置后。

　　[实战演习]　让标题更具魅力

　　利用“字体”标签，可以一次性完成表格标题的个性化设置。

选中标题所在的单元格（通常是合并居中后的一个单元格），打开“单元格格式”对话框，进入“字体”标签，设置字体为“华文新魏”，字形为“加粗”，字号为“22”号，下划线为“双下划线”，颜色为“蓝色”等，设置完成后，确定退出，一个漂亮的标题就呈现在你的眼前了（如图6）

图6

　　四、“边框”标签（图7）

图7

　　[纸上谈兵]

　　①点击下拉按钮，选中需要设定的边框颜色；②选定边框样式；③点击相应按钮，设置边框位置；④确定退出，完成边框的设置（图7的设置结果参见图8）

图8

　　[特别提醒]

　　如果要为工作表设置不同颜色和样式的边框，每一颜色和样式需要重复上述①至③步的操作。

　　[实战演习]　为表格添加粉红色的双边框

　　如果我们要为表格设置“粉红色的双线外边框”，可以这样设置。

选中整个工作表（当然不包括标题啦^_^），打开“单元格格式”对话框，进入“边框”标签，在“线条”下面选中双线条“样式”和粉红色的“颜色”，然后单击“预制”下面的“外边框”按钮，确定退出，边框添加成功（参见图6）。

　　[小技巧]为单元格添加一条斜线：

选中相应的单元格，在上述“边框”标签中，选中斜线“样式”，然后根据所要添加斜线的方向，单击“边框”左（或右）下角的相应“斜线”按钮，确定退出，斜线添加成功（参见图7）。

　　五、“图案”标签（图9）

图9

　　[纸上谈兵]

　　①选定单元格底色；

　　②点击下拉按钮，选定添加的网纹及网纹颜色（二者需要进行两次操作）；

　　③设置效果。

　　[实战演习]　标题的再修饰

　　通过“图案”标签，为标题单元格“填充”上“淡紫色”底色，并添加上网纹，让标题更具个性。

选中标题所在的单元格，打开“单元格格式”对话框，进入“图案”标签，选中“颜色”下面的“淡紫”颜色，然后按“图案”右侧的下拉按钮，在随后弹出的对话框中，选中一种网纹样式（如“12.5%灰色”等），确定退出，标题即被加上美丽的底纹和底色（参见图10，是不是比图6效果更好一些？

）

图10

　　六、“保护”标签（图11）

图11

　　[纸上谈兵]

　　①选中此项，相应单元格中的数据不能被修改；

　　②选中此项，相应单元格中的数据在编辑栏上不显示；

　　③确定退出；

　　④“隐藏”的效果。

　　[实战演习]　　保护你的公式

　　如果你制作的表格是让别人使用的，辛辛苦苦输入的公式，是不是常常被使用者搞得面目全非呢？

没关系！

将他们保护起来。

　　1、锁定单元格中的公式

　　①先在工作表左上角行、列交汇处单击鼠标，选定整个工作表。

　　②打开打开“单元格格式”对话框，进入“保护”标签，清除“锁定”前面复选框中的“∨”号，确定退出（解除对整个工作表的锁定）。

　　③执行“编辑→定位”命令（或直接按“Ctrl+G”组合键，或按F5功能键），打开“定位”对话框，单击“定位条件”按钮，在弹出的“定位条件”对话框（如图12）中，选中“公式”选项后，确定退出，选中工作表中所有包含公式的单元格。

图12

　　④再次进入“保护”标签，在“锁定”选中前面的复选框中加上“∨”号，确定退出。

⑤执行“工具→保护→保护工作表”命令，打开“保护工作表”对话框（如图13），

图13

　　输入密码（密码需要确认一次）后确定退出，公式就被锁定而不能修改了。

　　2、将公式隐藏起来

　　我们知道，单元格中输入公式后，在单元格中显示的是运算结果，而当我们选中该单元格时，在编辑栏中就显示出了公式的具体内容。

如果不想让别人看见具体的公式内容，可以将其隐藏起来：

　　①用上面的方法，选中所有包含公式的单元格。

　　②进入“保护”标签，在“隐藏”选中前面的复选框中加上“∨”号，确定退出。

　　③再用上面的方法“保护工作表”，公式的具体内容就被隐藏起来了。

　　[特别提醒]如果你想修改公式的内容，或者要显示出公式的具体内容，需要执行“工具→保护→撤销保护工作表”命令，输入密码解除保护。

妙用Excel把有规律的文本数据分列

　　刚考完试，领导心血来潮，要求统计一下男女比例、平均分、及格人数等各项信息。

学校一直使用专用软件登记考生资料，但该软件竟没有这些统计功能，暴汗。

幸好发现这软件能把基本数据导出为文本文件，我想，拿到Word或Exce里或许有办法解决。

下面是导出的文本，如图1。

　　要统