Excel中操作说明及要点.docx

Excel操作要点一、 输入1. 输入分数 “ 0 1/3” 2. 控制输入长度 “设置”选项卡在有效性条件中，“允许”下拉列表中选择“文本长度”，“数据”下拉列表中选择“等于”，“长度”中输入5（要求长度为5），再配合“出错警告”选项卡中相应设置即可得分。3. 输入不重复的数值如：第31行不重复-自定义公式里输入：=countif(31:31,A31)=1二、 条件格式1. 管理规则进入规则编辑对话框，如下设置：2. 多条件设置时，要按照顺序！例如18套题中（1）红色格式的设置一定要在蓝色格式设置之前 （2）颜色的问题：红、绿、蓝色的设置必须要到颜色调色板中，设置具体的RGB分量，见下图三、 数组公式数组公式是指可以在数组的一项或多项上执行多个计算的公式。数组公式可以返回多个结果，也可返回一个结果。例如，可以将数组公式放入单元格区域中，并使用数组公式计算列或行的小计。也可以将数组公式放入单个单元格中，然后计算单个量。位于多个单元格中的数组公式称为多单元格公式，位于单个单元格中的数组公式称为单个单元格公式。创建多单元格数组公式1. 打开一个新的空白工作簿。2. 复制示例工作表数据，并将它粘贴到的新工作簿中（从单元格 A1 开始）。销售人员汽车类型销售数量单价总销售额刘鹏四门轿车52200双门轿车41800尹歌四门轿车62300双门轿车81700林彩瑜四门轿车32000双门轿车11600潘杰四门轿车92150双门轿车51950施德福四门轿车62250双门轿车820003. 使用附近显示的“粘贴选项”按钮以匹配目标格式。4. 要将数组（单元格区域 C2 到 D11）中的值相乘，请选择单元格 E2 到 E11，然后在编辑栏中输入以下公式：=C2:C11*D2:D115. 按 Ctrl+Shift+Enter。Excel 使用大括号 ( ) 将公式括起，并将一个公式实例放入所选区域的每个单元格中。因为执行速度很快，所以您在 E 列中看到的是每位销售人员每种轿车类型的总销售额。四、 数据分析1. 高级筛选和自动筛选（1）本次21套题有5套是自动筛选的，分别是21、19、16、15、14（2）高级筛选部分，按照以前的做法，先复制，再输入条件，最后筛选即可；自动筛选做法也和以前一样，但在具体操作上仍有几项注意：（1）复制时一定要仔细，先复制表头，再复制数据部分，若数据部分涉及到当前数据区域以外的单元格的引用是，粘贴时一定要选择“值”的粘贴，更让人闹心的是，如果数据区域里面有日期格式的数据，则在粘贴“值”之前，要先粘贴“格式”一次，再进行“值”粘贴，如下图值格式（2）另外筛选前，要先将数据区域内不能完全显示的内容（如：以“#”显示在单元格中的部分），以合适的列宽显示出来，否则也会失分（3）筛选之前还要确保复制过来的数据区域部分的数据正确有效，如第6套题中，I列数据是由F列*G列数据得到，但如果写公式时写成“G3*F3”，那么关于I列数据的计算结果自然是不能得分的，由此还会影响到高级筛选时也会失分。所以在评测软件中，交换很是不成立滴。2. 数据透视表和数据透视图每套题的第8小题都是这类问题，10版的Excel是先插入一个空的数据透视表，然后再进行合理的布局，它不提供“向导”，就是用鼠标拖动一些数据项到X轴、Y轴和数据区，就能成图。具体操作时基本上有2点注意：（1）建立数据透视图Chart1，是真正意义上的新建，要在文档中出现一个和各个sheet并列的单独的Chart1（2）数据透视表要进行一次选项设置，将让的显示方式设置成经典的透视图方式。3. 其他问题1) 判断奇数个数 SUMPRODUCT和MOD函数 SUMPRODUCT函数功能：此函数的功能是用于求解括号内数组或区域乘积的和例如：求解C1到C20中奇数的个数，SUMPRODUCT(MOD(C1:C20,2)MOD的结果是由若干个0或1构成的数组0，1，1，1代表此单元格是奇数，然后将数组元素加起来，有多少个“1”即表示了奇数的个数。2) COUNTIF的应用该函数除了常规的条件计数外，还可以用于解决“数据有效性”相关题中的一类题目，即设置某一区域中单元格数据不重复。具体用法（例设置C列单元格不能重复输入）：“数据”选项卡”数据有效性”窗口”设置”选项卡有效性条件中，“允许”下拉列表中选择“自定义”在公式中输入“=COUNTIF(C:C,C1)=1”，意思为C列中与C1单元格值相同的单元格数目只能有1个。3) 将时间四舍五入到最接近的15分钟的倍数如，将15:37:48四舍五入到最接近15分钟的倍数，有两种参考方法：方法I：mround(B1,15/1440)方法II：参考书P122，round(b1*24/0.25, 0 )*0.25/244) 四舍五入到百位：Round（，-2）5) 字母大小写转换函数：Upper，如excel第19套题第3小题6) 判断是否是文本：IStext，如excel第5套题第6小题7) 第17套题中第5小题，有一个求跑得最快的学生成绩，要注意不是用MAX，而是用MIN。五、 函数1. IF函数： IF (,)1) 嵌套例题：否为闰年。如excel第3套题第6小题，excel第4套题第2小题（当年）分析：判断闰年条件：1.能被4整除且不能被100整除；或 2.能被400整除条件1：mod (year,4)=0 and mod(year,100)0 mod(year,400)=0操作示例：条件2：mod(year,400)=0条件1：mod(year,4)=0 and mod(year,100)0 单击OR单击IF,最终结果2) 嵌套其他IF函数：如excel第2套题第4小题、excel第17套题第4小题。excel第2套题第4小题分析如下图所示：逻辑关系：If 数量=100if 数量=200if 数量=300 d=0.1end ifend ifend if操作步骤：2. 几个统计函数运用及区别1) Count、CountA、countblankCount：只能统计数值个数，如成绩表上统计人数，只能统计“成绩”；CountA：可以统计非数值的个数，如成绩表上统计人数，可以统计“姓名”；Countblank：统计空单元格的数量，如excel第5套题第5小题2) Countif：一个条件的统计，如excel第13套题第5小题3) Dcount：多条件的统计，如excel第11套题第6小题4) Sumif：如excel第2套题第6小题，excel第13套题第5小题3. 查找引用函数VLOOKUP和HLOOKUP函数VLOOKUP：如excel第2套题第3小题HLOOKUP：如excel第9套题第3小题4. 文本函数1) Replace函数电话号码升级，如excel第4套题第4小题,对电话号码升级， F2单元格的原号码是“05716742801”，在其区号0571后加入“8”，即“057186742801” 则=REPLACE(F2,5,0,8)2) Mid函数：取出考生考试级别，如excel第13套题第4小题与if函数综合运用：根据身份证号码判断性别：如excel第16套题第1和3小题3) CONCATENATE函数：与Mid函数结合使用，根据身份证号码，写出出生年月日，如第18套题的第3小题4) Text函数：如第14套题的第6小题5) Find：如第21套题的第6小题6) Exact：如第19套题的第6小题5. 时间日期函数1) 今年：YEAR(TODAY)典型应用：判断今年是否是闰年，与if、mod函数结合使用。excel第9套题第2小题。2) 计算年龄或工龄：YEAR(TODAY)-YEAR(出生年月)，如excel第8套题第4小题；3) 时间的加减问题，如excel第9套题第4小题（停放时间）；excel第14套题第4小题（预计报销时间），excel第15套题第4小题（护理天数）。6. 数据库函数数据库函数是用于对存储在数据清单或数据库中的数据进行分析，判断其是否符合特定的条件. 函数名称（database，field，criteria）函数名功能典型应用DCOUNT返回列表或数据库中满足指定条件的记录字段（列）中包含数值的单元格的个数如excel第5套题第4小题如excel第11套题第6小题如excel第15套题第6小题DCOUNTA返回列表或数据库中满足指定条件的记录字段（列）中非空单元格的个数如excel第15套题第6小题DGET从列表或数据库的列中提取符合指定条件的单个值如excel第11套题第6小题DAVERAGE计算列表或数据库的列中满足指定条件的数值的平均值如excel第5套题第4小题如excel第11套题第6小题DMAX返回列表或数据库的列中满足条件的最大值如excel第11套题第6小题DSUM返回列表或数据库中满足指定条件的记录字段（列）中的数字之和如excel