EXCEL常用操作技巧解析

1、EXCEL常用技巧,基本操作； 公式与函数； 数据分析与数据处理； 图表处理；,基本操作,操作环境： 单元格操作和不同数据类型输入 格式设置与应用,操作环境：,设置菜单字体与字号; 更改工具栏为大字体； 设置工作表标签字体、颜色； 设置菜单完整显示（窗体属性菜单项；工具自定义下的 设置）； 设置坐标的RC样式显示（常规）； 设置网格与标尺；滚动条，标签的显示与隐藏。,单元格操作和不同数据类型输入,建立命名快速定义单元格区域； 使用F4的重复插入工作表与删除工作表； 使用shift,Ctrl,搬移行列； 设置输入（编辑设定单元格内直接编辑）： 练习文本与基本数据（特殊数据，数字大小写）： 负数与

2、分数的输入（5）=-5；0空格1/4为分数；,单元格操作和不同数据类型输入,日期与时间的输入； 自动填充(自定义序列)； 选择性粘贴的转置； 批注的插入与修改;显示图形的外形;（工具视图：批注的显示与否，批注的图形变化：自选图形的更改；选择性粘贴批注；）； 工作组的设置：同时选中多表可以同时操作； 并排比较与拆分、冻结,格式设置与应用,格式的设置（字符串中一部分文字修饰）：边框与底纹；套用系统默用格式； 定义使用样式（格式下样式设置）； 字体的填充；缩进；垂直排列； 自动换行：Alt+回车=自动换行； 全选单元格可以全部设置； 工具常规可以进行标准字体的设置；,条件单元格；不同条件下颜色的设置

3、； 奇偶格色彩设置与棋盘型色彩设置； 页面设置与打印工作表设置；,EXCEL公式与函数篇,公式与函数概念 公式与函数操作 常见函数错误类型： 逻辑关系判断 常用函数与财务函数,公式与函数概念,公式与函数都是以等号开始的，公式是对工作表中数值执行计算的等式；而函数则是按特定顺序或结构执行计算的特殊公式。根据应用领域的不同，EXCEL函数一般可分为：逻辑、信息、日期与时间、数学与三角、统计、查找与引用、数据库、文本、财务、工程类别等；此外，还有：扩展函数和外部函数等。 公式的输入与编辑：工具拦，插入函数向导，如果不熟悉的话，还可以以搜索的方式；手工方式；编辑与复制。（工具/常规/函数提示后，单击函

4、数名称可以突出正在编辑的函数。）,公式与函数操作,输入函数名称后：Ctrl+A显示Shift+Ctrl+A可以继续函数。 工具/选项/常规/函数提示：,常见错误类型：,使用后台错误检查：工具/选项/错误检查/允许后台检查；绿色小三角形：称为智能标记。 右毽工具栏中：公式审核：显示计算步骤；监视窗口；分布查看计算结果；F9健:用光标摸黑部分公式，按F9毽可以计算部分结果,逻辑关系判断,与关系/或关系/非关系： =AND(A10,A140,D1=“教授”);筛选满足三个条件。 =OR(A1”硕士”。 在6080之间为合格，其余不合格。,常用函数,?/*/去通配符 计算以字母A开头的记录个数（=co

5、untif(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”).,如果有两科以上不及格需要补考； 如果只有一科不及格，而这一科如

6、果又低于40分，需要补考； 如果只有一科不及格，而该科又达到40分，允许不补考； 为鼓励学生尽量学好各科，如果只有一科不及格，且在40分以下，但若有4科在70分以上，也可以不补考。,特殊函数举例,分析：如果不及格科目超过一门，补考；否则没有超过但有低于40分，且该生成绩70分以上的 科目没有达到4门，补考；否则，不及格没有超过两门且没有科目低于40分不用补考；23 If(or(countif(c2:i2,”1,and(countif(c2:i2,”70”)4),”补考”，“”）,SUM(); AVERAGE();215； ROUND(); INT(); ABS (); SIGN(); SQRT

7、(); 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$1

8、2)+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. ty

9、pe：省略或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

10、,10000),数据分析与数据处理,动态名称的创建与应用 单元格中下拉列表的创建与数据有效性： 快速切换有效性的来源： 排序与筛选： 模拟运算表与规划求解:,动态名称的创建与应用：82,利用OFFSET与COUNTA函数的组合，可以创建动态名称；例如82表格，如果行数记录发生变化的情况下，如何保持计算结果的正确性呢？ 定义DATA为下面的公式： =OFFSET($C$4,COUNTA($C:$C)-1) 缺陷：单元格中间不能有空格。,单元格中下拉列表的创建与数据有效性：102,在单元格中进行下拉列表的设置：102；钩选忽略空格和提供下拉列表： 利用有效性设置批注：103；数据有效性输入信息选项

11、卡； 标题：提示： 输入信息：请输入您的姓名。,快速切换有效性的来源：,在通常情况下，当用户使用有效性时，只能处理一组数据源；在多组数据源下的数据列如何切换呢？ A2单元格数据有效性下引入：=OFFSET(list.1,A1-1),排序与筛选：,超过3列的数据排序：121； 由于在多列排序时，先被排序的列会在后面按其他列保持自己的序列；所以在本例中，或者直接使用排序按钮：顺序为：责任人、完成日期、开始日期、项目、类别。 或者：先完成日期为主关键字、责任人为次关键字排序后，再以类别为主关键字、项目与开始日期为次关键字排序即可。,创建自定义序列：122；多列排序要一次一次排： 按笔画排序：123；

12、 按行排序：124；此时只能选择非标题列中数据区域。 按字符数量排序：125；(设置字符数量列，最后删掉即可)；125,按颜色排序或筛选：126 随机排序：127；借用随机列完成； 排序字母与数字的混合内容：128； 公式：=LEFT(A1,1)按照规定一月份交货30台,二月份交货80台,三月份交货90台;美航公司的最大生产能力为每月11台,生产成本为: F(x)=40X+0.25X2 ;其中X为台数.很容易看出生产规模越大,平均生产成本越低.如果生产的台数大于交货数量,剩余部分可以下月交货,但是每台水泵需要5元的保存费用. 因此,也不是规模越大越好.如何安排每月的产量才能满足交货合同,并使综

13、合费用最低.,图表处理,符合制作数据透视表的表格条件 数据透视表的设置于操作 数据透视图与设置 常用图设置于操作,符合制作数据透视表的表格条件,二维表创建数据透视表：136 数据透视表是对数据的统计与比较;它的数据源可以是数据列表,外部数据源,多个合并区域,以及其他的数据透视表. 数据列表是指以列表形式存在的数据表格;指一维表格;而实际工作中表格是以二维表格的形式存在的.,1.转换为一维表;选定任意数据单元格,单击数据透视表;在步骤三之一选择多重合并计算区域与数据透视表,再选择创建单页字段项,选择区域选择整个表,单击添加;选择新表建立数据透视表;拖走行列字段;双击汇总数据,得到数据列表.修改列

14、标题,就可以建立数据透视表了.,组合数据透视表内的日期项:137 建立数据透视表,按年,月汇总,组合; 快速统计重复项目:138 拖动销售区域到行字段,拖动到数据项区域再; 在计算字段中添加计算项:139; 单击数据透视表工具栏中数据透视表中,公式,计算项,右击菜单栏可以得到数据透视表工具栏;类别插入计算字段,名称输入差额,把光标定位到公式框中,单击类别,双击项内国内项的国内;输入减法,再输入进口,确定;,插入图表：选择图表类型；设置系列名称；设置图标位置；编辑图表格式；1133,当分类轴上有缺口时，单击选中整个数据系列，单击图表、图表选项中分类选项下；完成去除缺口。表277。 折线图中处理空

15、单元格的三种样式：工具选项中图表选项卡：不绘制、以零值代表、以内插值替换。278 选中该图，单击图表并在新表中插入，可以将该图插入到新表中；单击视图：随窗口大小调整；可以使该图随窗口大小调整。279,快速设置图表字体：单击字号：11、倾斜字形、字体：宋体。280； 隐藏接近于零的数据标签：选中数据系列的数据标志；在数据标志格式的数字选项卡中，分类中选择自定义：类型中输入：1000;0 ;282 快速添加图标数据系列：283；复制数据列2到图表中；或选中后拖动到图表中。,在图表中食用透明填充色：284;不透明的矩形数据列需要：自绘矩形，后格式菜单设置透明度85%；再复制该矩形到数据列即可。显示网

16、格线了。 背景透明的图表：边框区域设为无。285；,制作完全静态的图表：表287；shift+菜单：复制，粘贴。 控制数据标志的显示位置：288；双击数据标志，数据标志格式中切换到对齐选项卡，标签位置修改为数据标记内。（居中）。 添加自定义图表到：291； 制作瀑布图：设置辅助数据列；b2=0;B3=c2+C3;向下填充；以数据源A1:C10制作堆积柱形图；双击柱形白色部分，数据系列格式中图案选项卡，选择边框无，内部无；双击柱形红色部分，数据系列格式选项选项卡，分类间距设为：0；数据标志选项卡，勾选值复选框，得到。294；,美化三维图表：295； 选中图表，设置三维视图格式，双击背景墙，边框与区域颜色设置为无；双击网格线，在网格线中设置线条颜色为白；双击数据系列，数据系列格式中图案选项卡中，设置边框无或设置其他颜色；切换到数据标志选项卡中，勾选值复选框；两个数据系列都要设置；双击图例，边框无；用鼠标将图例拖到绘图区中； 双击图表中的基底，在基底格式对话框中分别设置边框与区域颜色为无；单击绘图区，光标拖到边框处，变成箭头时可以改变绘图区在图表中的大小。,另外，可以在数据系列格式对话框中切换到形状选项卡，改变柱形形状。 复合饼图：301 在使用饼图揭示数据的比例关系时，如果有一部分数据的值小于其他数据，则在图表中显示的扇形区很狭窄； 1.创建复合饼图，切换数据系列格