搜索
EXCEL常用技巧基本操作；公式与函数；数据分析与数据处理；图表处理；基本操作操作环境：单元格操作和不同数据类型输入格式设置与应用操作环境：•设置菜单字体与字号;•更改工具栏为大字体；•设置工作表标签字体、颜色；•设置菜单完整显示（窗体属性菜单项；工具自定义下的设置））；•设置坐标的RC样式显示（常规）；•设置网格与标尺；滚动条，标签的显示与隐藏。单元格操作和不同数据类型输入•建立命名快速定义单元格区域；•使用F4的重复插入工作表与删除工作表；•使用shift,Ctrl,搬移行列；•设置输入（编辑设定单元格内直接编辑）：•练习文本与基本数据（特殊数据，数字大小写）：•负数与分数的输入（5）=-5；0空格1/4为分数；单元格操作和不同数据类型输入•日期与时间的输入；•自动填充(自定义序列)；•选择性粘贴的转置；•批注的插入与修改;显示图形的外形;（工具视图：批注的显示与否，批注的图形变化：自选图形的更改；选择性粘贴批注；）；•工作组的设置：同时选中多表可以同时操作；•并排比较与拆分、冻结格式设置与应用•格式的设置（字符串中一部分文字修饰）：边框与底纹；套用系统默用格式；•定义使用样式（格式下样式设置）；•字体的填充；缩进；垂直排列；•自动换行：Alt+回车=自动换行；•全选单元格可以全部设置；•工具常规可以进行标准字体的设置；•条件单元格；不同条件下颜色的设置；•奇偶格色彩设置与棋盘型色彩设置；•页面设置与打印工作表设置；EXCEL公式与函数篇公式与函数概念公式与函数操作常见函数错误类型：逻辑关系判断常用函数与财务函数公式与函数概念公式与函数都是以等号开始的，公式是对工作表中数值执行计算的等式；而函数则是按特定顺序或结构执行计算的特殊公式。根据应用领域的不同，EXCEL函数一般可分为：逻辑、信息、日期与时间、数学与三角、统计、查找与引用、数据库、文本、财务、工程类别等；此外，还有：扩展函数和外部函数等。公式的输入与编辑：工具拦，插入函数向导，如果不熟悉的话，还可以以搜索的方式；手工方式；编辑与复制。（工具/常规/函数提示后，单击函数名称可以突出正在编辑的函数。）公式与函数操作输入函数名称后：Ctrl+A显示Shift+Ctrl+A可以继续函数。工具/选项/常规/函数提示：常见错误类型：错误类型#####列宽不够时，或者使用了负的日期或负的日期时#VALUE当使用的参数或操作数类型错误时，出现错误#DIV/0!当数字被零除时#NAME当EXCEL未识别公式中的文本时#N/A当数值对函数或公式不可用时#REF!当单元格引用无效时#NUM!公式或函数中使用无效数字值时#NULL!当指定并不相交的两个区域的交点时，使用后台错误检查：工具/选项/错误检查/允许后台检查；绿色小三角形：称为智能标记。右毽工具栏中：公式审核：显示计算步骤；监视窗口；分布查看计算结果；F9健:用光标摸黑部分公式，按F9毽可以计算部分结果逻辑关系判断•与关系/或关系/非关系：•=AND(A10,A1=10).•=AND(B1=“男”，C140,D1=“教授”);筛选满足三个条件。•=OR(A160,B160,C160);筛选不及格记录。•=NOT(A1=“硕士”）;筛选非记录。=A1”硕士”。•在60—80之间为合格，其余不合格。常用函数•?/*/～去通配符•计算以字母A开头的记录个数（=countif(A1:A10,”A*”)）•ROUND();•Round(a1,3)----129.215;----191•Int(66.948)----66;•Trunc(66.948)—66;•Trunc(66.948,2)----66.94•Rand();随机函数：•返回大于1小于10的随机实数：•Rand()*(b-a)+a;•Rand()*(10-1)+1;•UPPER();LOWER().164•CODE();CHAR().•LEFT();RIGHT();MID();/提取出生日期，174/=TEXT(mid(A1,7,8),”#-00-00”).如果有两科以上不及格需要补考；如果只有一科不及格，而这一科如果又低于40分，需要补考；如果只有一科不及格，而该科又达到40分，允许不补考；为鼓励学生尽量学好各科，如果只有一科不及格，且在40分以下，但若有4科在70分以上，也可以不补考。特殊函数举例•分析：如果不及格科目超过一门，补考；否则没有超过但有低于40分，且该生成绩70分以上的科目没有达到4门，补考；否则，不及格没有超过两门且没有科目低于40分不用补考；23•If(or(countif(c2:i2,”60”)1,and(countif(c2:i2,”40”),countif(c2:i2,”70”)4)),”补考”，“”）•SUM();•AVERAGE();215；•ROUND();•INT();•ABS();•SIGN();•SQRT();LEN();max213;•Countif(range,criteria);201•统计大于等于100且小于等于130的记录个数；•=countif(C2:C10,”=100)-countif(C2:C10,”130);•Sumif();20--203•统计40以上男性，sum(a2:a12=“男”*（b2:b1240）)•Ctrl+shift+enter.205统配副模糊求和。•Rank();rank(a2,a2:a12,bc2:c12)不连续区域排名；219•Crtl=shift+enter;•=sum(if(a$2:a$12a2,1/countif(a$2:a$12,a$2:a$12)))+1;绝对引用。•税制：211；财务函数•现值函数:返回投资的现值:•PV(rate,nper,pmt,fv,type);•Rate:投资或贷款的利率或贴现率.例:如果按10%的年利率借来一笔贷款购买汽车,按月还银行的话,则月利率为10%/12.•Nper:总投资期或总贷款期:例:一笔4年期按月偿还的汽车贷款,付款期总数为12*4.•FV:未来值:或在最后一次支付后希望得到的现金余额:例:需要在18年后支付$50000,则$50000就是未来值.•PMT:各期应付金额，其数值在整个年金期间保持不变。比如贷款￥10000，年利率12%，月偿还额为263.33，则263.33为PMT.•type：省略或0为期末，1为期初。•假设购买一项保险年金，在二十年内每月回报为￥500，此年金的购买成本为￥60000，投资回报率8%，计算是否值得。•=PV(0.08/12,12*20,500,0)•将来值函数：=FV(rate,nper,pmt,pv,type)•将￥1000以年利率2%，按月计息存入储蓄账户中，并在以后12月中每个月初存入￥100，则一年以后账户存款为多少？•=FV(2%/12,12,-100,-1000,1)•每期付款额函数：PMT:•PMT(rate,nper,pv,fv,type);•如果在10个月内付清￥10000贷款，则月支付额是多少？•=PMT(8%/12,10,10000)数据分析与数据处理动态名称的创建与应用单元格中下拉列表的创建与数据有效性：快速切换有效性的来源：排序与筛选：模拟运算表与规划求解:动态名称的创建与应用：82•利用OFFSET与COUNTA函数的组合，可以创建动态名称；例如82表格，如果行数记录发生变化的情况下，如何保持计算结果的正确性呢？•定义DATA为下面的公式：•=OFFSET($C$4,,,COUNTA($C:$C)-1)•缺陷：单元格中间不能有空格。单元格中下拉列表的创建与数据有效性：102•在单元格中进行下拉列表的设置：102；钩选忽略空格和提供下拉列表：•利用有效性设置批注：103；数据有效性输入信息选项卡；•标题：提示：•输入信息：请输入您的姓名。快速切换有效性的来源：•在通常情况下，当用户使用有效性时，只能处理一组数据源；在多组数据源下的数据列如何切换呢？•A2单元格数据有效性下引入：=OFFSET(list.1,,A1-1)排序与筛选：•超过3列的数据排序：121；•由于在多列排序时，先被排序的列会在后面按其他列保持自己的序列；所以在本例中，或者直接使用排序按钮：顺序为：责任人、完成日期、开始日期、项目、类别。•或者：先完成日期为主关键字、责任人为次关键字排序后，再以类别为主关键字、项目与开始日期为次关键字排序即可。•创建自定义序列：122；多列排序要一次一次排：•按笔画排序：123；•按行排序：124；此时只能选择非标题列中数据区域。•按字符数量排序：125；(设置字符数量列，最后删掉即可)；125•按颜色排序或筛选：126•随机排序：127；借用随机列完成；•排序字母与数字的混合内容：128；•公式：=LEFT(A1,1)&RIGHT(“000”&RIGHT(A1,LEN(A1)-1),3)•返回排序前的表格：129；借用排序列；•自动筛选：自定义筛选；前5个；•高级筛选：设置筛选条件；•单个查询：VLOOKUP()函数；模糊查询要求第一列升序排列；区域可包括第一列。•分类汇总；两级分类汇总。•单变量模拟运算表:•小王要贷款50万元买房,年利率为12%,还贷年限为30年,现在分析不同的年利率对(假设在11%--13%之间变动)对每月还贷额度的影响.采用单变量模拟运算表来解决问题.•双变量模拟运算表:•小王要贷款50万元买房,年利率为12%,还贷年限为30年,现在分析不同的年利率和贷款年限对(假设在年利率11%--13%之间变动,还款年限在26—30年之间变动)对每月还贷额度的影响.采用双变量模拟运算表来解决问题.•规划求解:•美航公司根据合同要求,向客户提供水泵;按照规定一月份交货30台,二月份交货80台,三月份交货90台;美航公司的最大生产能力为每月11台,生产成本为:•F(x)=40X+0.25X2;其中X为台数.很容易看出生产规模越大,平均生产成本越低.如果生产的台数大于交货数量,剩余部分可以下月交货,但是每台水泵需要5元的保存费用.•因此,也不是规模越大越好.如何安排每月的产量才能满足交货合同,并使综合费用最低.图表处理符合制作数据透视表的表格条件数据透视表的设置于操作数据透视图与设置常用图设置于操作符合制作数据透视表的表格条件•二维表创建数据透视表：136•数据透视表是对数据的统计与比较;它的数据源可以是数据列表,外部数据源,多个合并区域,以及其他的数据透视表.•数据列表是指以列表形式存在的数据表格;指一维表格;而实际工作中表格是以二维表格的形式存在的.•1.转换为一维表;选定任意数据单元格,单击数据透视表;在步骤三之一选择多重合并计算区域与数据透视表,再选择创建单页字段项,选择区域选择整个表,单击添加;选择新表建立数据透视表;拖走行列字段;双击汇总数据,得到数据列表.修改列标题,就可以建立数据透视表了.•组合数据透视表内的日期项:137•建立数据透视表,按年,月汇总,组合;•快速统计重复项目:138•拖动销售区域到行字段,拖动到数据项区域再;•在计算字段中添加计算项:139;•单击数据透视表工具栏中数据透视表中,公式,计算项,右击菜单栏可以得到数据透视表工具栏;类别插入计算字段,•名称输入差额,把光标定位到公式框中,单击类别,双击项内国内项的国内;输入减法,再输入进口,确定;•插入图表：选择图表类型；设置系列名称；设置图标位置；编辑图表格式；1133•当分类轴上有缺口时，单击选中整个数据系列，单击图表、图表选项中分类选项下；完成去除缺口。表277。•折线图中处理空单元格的三种样式：工具选项中图表选项卡：不绘制、以零值代表、以内插值替换。278•选中该图，单击图表并在新表中插入，可以将该图插入到新表中；单击视图：随窗口大小调整；可以使该图随窗口大小调整。279•快速设置图表字体：单击字号：11、倾斜字形、字体：宋体。280；•隐藏接近于零的数据标签：选中数据系列的数据标志；在数据标志格式的数字选项卡中，分类中选择自定义