Excel运用的一些小窍门.doc

关于EXCEL运用中的一些小窍门一、成绩统计技巧RANK返回一个数字在数字列表中的排位。=RANK(A3,$A$2:$A$6, 1)SUM返回某一单元格区域中所有数字之和。=SUM(A2:A4)IF执行真假值判断，根据逻辑计算的真假值，返回不同结果。=IF(A489,A,IF(A479,B, IF(A469,C,IF(A459,D,F)AVERAGE返回参数的平均值（算术平均值）=AVERAGE(A2:A6)CONCATENATE将几个文本字符串合并为一个文本字符串 。=CONCATENATE(Stream population for ,A2, ,A3, is ,A4,/mile)COUNT返回包含数字以及包含参数列表中的数字的单元格的个数。=COUNT(A5:A8)COUNTIF计算区域中满足给定条件的单元格的个数。=COUNTIF(B2:B5,55)MAX返回一组值中的最大值。 =MAX(A2:A6)MIN返回一组值中的最小值。=MIN(A2:A6)二、学籍管理常见函数操作技巧在学生的学籍管理中，经常要录入学生的身份证号、出生年月日、年龄等信息。如果能利用Excel的函数功能，便可以使录入的学生身份证号自动生成出生年月日和年龄，既方便，又可减少录入出生年月日、年龄的差错与繁琐。DATE函数，返回代表特定日期的系列数。语法：DATE(Year,Month,Day)。MID(Text,Start_Num,Num_Chars)。参数Text是包含要提取字符的文本串，文本中第一个字符的Start_Num为 1，以此类推；Start_Num是文本中要提取的第一个字符的位置；Num_Chars指定希望MID从文本中返回字符的个数。 （一）“=DATE(MID(J2,7,4),MID(J2,11,2),MID(J2,13,2)”（二） DATEIF函数，计算两个日期之间的天数、月数或年数。 “=DATEDIF(G2,TODAY(),Y)”，这样就可根据当前计算机中的日期，自动计算出学生的实际年龄。 （三）Excel中提取身份证的的出生年月的方法：=CONCATENATE(19,MID(E4,7,2),/,MID(E4,9,2),/,MID(E4,11,2)，必须为非文本格式。=MID(D4,1,4)&MID(D4,6,2)&MID(D4,9,2)（四）将数字转换成日期：=LEFT(A1,4)&-&MID(A1,5,2)&-&RIGHT(A1,2)三、高速打印学生成绩条小窍门常有朋友问“如何 打印成绩条”这样的问题，有不少人采取录制宏或VBA的要领来实现，这对于初学者来说有一定难度。出于此种考虑，我在这里给出一种用函数实现的简便要领。 此处假定学生成绩保存在Sheet1工作表的A1至G64单元格区域中，其中第1行为标题，第2行为学科名称。 1.切换到Sheet2工作表中，选中A1单元格，输入公式：=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,Sheet1!A$1,INDEX(Sheet1!$A:$N,INT(ROW()+4)/3),COLUMN()” 2.再次选中A1单元格，用“填充柄”将上述公式复制到B1至G1单元格中；然后，再同时选中A1至G1单元格区域，用“填充柄”将上述公式复制到A2至G185单元格中。 至此，成绩条基本成型，下面基本修饰一下。 3.调整好行高和列宽后，同时选中A1至G2单元格区域（第1位学生的成绩条区域），按“格式”工具栏“边框”右侧的下拉按钮，在随后出现的边框列表中，选中“所有框线”选项，为选中的区域添加边框（如果不须要边框，可以不执行 此步及下面的操作）。 4.同时选中A1至G3单元格区域，点击“常用”工具栏上的“格式刷”按钮，然后按住鼠标左键，自A4拖拉至G186单元格区域，为所有的成绩条添加边框。 按“打印”按钮，即可将成绩条打印出来。四、合并文本（字符串）（一）如果我们想将多列的内容合并到一列中，不须要运用 函数，一个小小的连字符“&”就能将它搞定（此处假定将B、C、D列合并到一列中）。 1.在D列后面插入两个空列（E、F列），然后在D1单元格中输入公式：=B1&C1&D1。 2.再次选中D1单元格，用“填充柄”将上述公式复制到D列下面的单元格中，B、C、D列的内容即被合并到E列对应的单元格中。 3.选中E列，执行“复制”操作，然后选中F列，执行“编辑选择性粘贴”命令，打开“选择性粘贴”对话框，选中其中的“数值”选项，按下“确定”按钮，E列的内容（不是公式）即被复制到F列中。 4.将B、C、D、E列删除，完成合并工作。 提示：完成第1、2步的操作，合并效果已经实现，但此时如果删除B、C、D列，公式会出现错误。故须执行 第3步操作，将公式转换为不变的“值”。 （二）使用函数CONCATENATE达到同样的目的，它的功能是将几个文本字符串合并为一个文本字符串，如：选中D3单元格，输入公式“=CONCATENATE(A2,B2)”，五、给表格做个超级搜索引擎 我们知道，Excel表格和Word中的表格最大的不同就是Excel是将填入表格中的所有内容（包括静态文本）都纳入了数据库的范畴之内。我们可以运用 “函数查询”，对目标数据执行 精确定位，就像网页中的搜索引擎一样。 比如在所示的表格中，从A1到F7的单元格中输入了多名同学的各科成绩。而在A8到A13的单元格中我们则建立了一个“函数查询”区域。我们的设想是，当我们在“输入学生姓名”右边的单元格，也就是C8格中输入任何一个同学的名字后，其下方的单元格中就会自动显示出该学生的各科成绩。具体实现的要领如下： 将光标定位到C9单元格中，然后单击“插入”之“函数”选项。在如图18弹出的窗口中，选择 “VLOOKUP” 函数，点“确定”。在随即弹出的“函数参数”窗口中我们配置 “Lookup_value”（指须要在数据表首列中搜索的值）为“C8”（即搜索我们在C8单元格中填入的人名）；“Table_array”（指数据搜索的范围）为“A2B6”（即在所有学生的“语文”成绩中搜索）；“Col_vindex_num”（指要搜索的数值在表格中的序列号）为“2”（即数值在第2列）；“Range_lookup”（指能不能须要精确匹配）为“FALSE”（表明不是。如果是，就为“TURE”）。设定完毕按“确定”。 此时回到表格，单击C9单元格，我们看到“fx”区域中显示的命令行为“=VLOOKUP（C8，A2B6，2，FALSE）”。复制该命令行，在C10、C11、C12、C13单元格中分别输入：“=VLOOKUP（C8，A2C6，3，FALSE）”；“=VLOOKUP（C8，A2D6，4，FALSE）”；“=VLOOKUP（C8，A2E6，5，FALSE）”；“=VLOOKUP（C8，A2F6，6，FALSE）”（其参数意义同C9中一样，不再赘述）。接下来，我们就来检验“VLOOKUP”函数的功效。试着在“C8”单元格中输入某个学生名，比如“赵耀”，回车之下我们会发现，其下方每一科目的单元格中就自动显示出该生的入学成绩了六、初三成绩分数段统计。Excel已经为我们提供了一个进行频度分析的FreQuency数组函数，它能让我们用一条数组公式就轻松地统计出各分数段的人数分布。例如，我们要统计出C2:C56区域内455至729每个分数段内的人数分布：1. 在B58:B68内输入：455、469、479、709、719、729。2. 用鼠标选择区域C58至C69，在编辑栏内输入“=FreQuency(C2:C56,B58:B69)”。3. 按“Crtl+Shift+Enter”组合键产生数组公式“=FreQuency(C2:C56,B58:B69)”，这里要注意“ ”不能手工键入，必须按下“Crtl+Shift+Enter”组合键由系统自动产生。完成后C58:C69将显示分数分布情况。七、让中、英文输入法智能化地出现在编辑表格时，有的单元格中要输入英文，有的单元格中要输入中文，反复切换输入法实在不方便，何不配