Excel高级使用技巧课件

1、Excel 2010高级使用技巧第1页，共74页。Contents一、数据录入和格式化工作表 二、公式与函数的应用及案例三、图表及打印输出及其它设置四、数据分析及管理第2页，共74页。1、Excel2010的数据录入技巧；2、自定义数字格式；3、Excel2010条件格式设置；4、数据有效性；5、EXCEL多工作表链接、冻结与并排；6、EXCEL工作表的数据保护功能；一、数据录入和格式化工作表第3页，共74页。1.1、数据录入的相关技巧说明性，解释性的数据描述，非数值类型的。如， 员工编号、姓名、出生年月、手机号码常常用于各种数学计算的数据。如：工资、学生成绩、销售额等 是以“=”号开头，它可

2、以是简单的数学式，也可以是包含各种Excel函数的式子数值文本公式第4页，共74页。数据录入技巧灵活使用TAB、ALT、ENTER键录入分数：零+空格+分数录入文本格式数字（邮编、电话等） ：单引号+录入内容输入当前日期、时间：Ctrl+; 和 Ctrl+Shift+; 录入特殊字符（对号、平方）;不连续区域的填充：Ctrl+Enter填充柄录入（序列填充）插入批注实例01 数据录入第5页，共74页。案例分析（1）在数字后面自动添加单位（如：元、千克等）将文本字符括在双引号 ( ) 内或在单个字符前面添加一个反斜杠 ()（2）输入身份证的前几位 51102519（3）输入条形码前的某些重复数字

3、 “999999999”（4）定义小数点对齐、分数对齐 ?.? # ?/?（5）自定义各种特殊日期格式mmmm d, yyyy (dddd) December 30,2008 (Tuesday)yyyy“年”m“月”d“日”aaaa 2008年12月30日星期四 1.2、自动限定数据格式实例02 自定义数字格式第6页，共74页。1、EXCEL表格的排版技巧；2、EXCEL条件格式1.3、Excel2010条件格式设置；第7页，共74页。1.3.1、单元格格式设置文字的对齐与旋转合并单元格使用工具栏上的快捷按钮格式单元格对齐合并单元格自动换行 格式单元格对齐自动换行强制换行 ALT+ENTER文

4、字旋转 格式单元格对齐文字方向设定边框和底纹使用工具栏上的“边框”“底纹”快捷按钮格式单元格边框图案实例03 对齐技巧与边框底纹第8页，共74页。1.3.2、条件格式管理器设置什么是条件格式当单元格的值满足预先设定的条件时，自动将单元格设置成预先设定的格式多条件格式的规则按从上到下的顺序判断条件并执行最多指定三个条件根据公式自动设置格式在条件格式中使用公式和函数实现隔行变色等的特殊格式掌握ROW和MOD函数的用法 = =mod(row(),2)实例04 条件格式第9页，共74页。提高数据录入准确性有效性检查。什么是数据的有效性？数据有效性是一个包含帮助你在工作表中输入资料提示信息的工具. 它有

5、如下功能： (1) 给用户提供一个选择列表(2) 限定输入内容的类型或大小(3) 自定义设置数据有效性的设置提供下拉式菜单的输入设置输入提示设置出错警告自定义有效性条件1.4、数据的有效性实例05 数据有效性第10页，共74页。1.5、如何处理大型工作表在Excel多个工作表间快速切换技巧一：用快捷键快速导航工作表Ctrl+Page Up 移到上一个工作表Ctrl+Page Down 移到下一个工作表 技巧二：右键菜单准确定位工作表 右击导航状态按钮选择所需工作表标签名冻结工作表窗口大型工作表数据的分组、分级显示命名单元格或单元格区域，以便能够快速返回到这些工作表区域组合工作表，以便同时在多张

6、工作表中输入共用数据实例06 冻结窗格第11页，共74页。并排比较数据两个工作簿将在同一个窗口中打开。当一个工作簿滚动时，Excel 将使第二个工作簿自动滚动相应数量的单元格 同时比较两个工作簿可能很困难实例07-1 并排比较数据1实例07-2 并排比较数据2第12页，共74页。1.6、数据保护功能保护工作表、工作簿（1）为工作簿加密码（2）为工作表加密码（3）设置不同区域不同密码（4）设置只有部分单元格可编辑第13页，共74页。二、公式与函数的应用及案例公式的基本用法2.1函数分类与常用函数应用举例2.2第14页，共74页。双击填充柄复制公式区别公式中各种单元格引用方式绝对引用、相对引用、混

7、合引用、三维引用跨工作表或工作簿的数据计算不同工作表间数据的计算、不同工作簿间数据的计算自动求和与自动计算功能的使用2.1 公式的基本用法实例08 使用相对和绝对引用计算产品销售额与利润实例9-1 跨工作表数据计算实例9-2 跨工作簿数据计算第15页，共74页。函数：是预定义的公式，通过使用一些称为参数的特定值来安特定的顺序和结构执行计算函数的使用方式=函数名称（参数1，参数2，参数3)函数的嵌套特性一个函数可以引用其他函数的结果作为参数Excel最多支持七层嵌套函数逻辑文本类函数应用日期时间函数应用查询引用类函数应用统计计数类函数应用财务函数应用2.2 函数应用与常用函数应用举例实例10 函

8、数综合实例第16页，共74页。IFANDORLENMID LEFT RIGHT2.2.1逻辑文本类函数第17页，共74页。判断一个条件是否满足，如果满足返回一个值，如果不满足则返回另一个值IF函数语法：IF(logical_test,value_if_true,value_if_false)条件为真时条件为假时第18页，共74页。AND OR 函数AND函数的作用：是对多个逻辑值进行交集计算，返回逻辑值语法：AND(logical1,logical2)OR函数的作用：是对多个逻辑值进行并集计算，返回逻辑值语法：OR(logical1,logical2,)第19页，共74页。MIDB函数作用自文

9、字指定位置开始向右提取指定长度的字符串，操作对象均为文本，MID以字符为单位，MIDB以字节为单位截取语法：MID (text,start_num,num_bytes)LEFT返回文本字符串中的第一个或前几个字符，RIGHT返回文本字符串中的最后一个或后几年字符语法：LEFT(text,num_chars)语法：RIGHT(text,num_chars)MID(MIDB) LEFT RIGHT函数第20页，共74页。DATE：返回代表特定日期的序列号语法：DATE(year,month,day)NOW：返回计算机内部时钟的当前日期和时间语法：NOW( )TODAY：返回当前日期的序列编号语法：

10、TODAY( )注意：序列号是 Microsoft Excel 日期和时间计算使用的日期-时间代码。Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1 ，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。2.2.2日期与时间函数：第21页，共74页。LOOKUPHLOOKUPVLOOKUP2.2.3查询和引用类函数第22页，共74页。LOOKUP使用LOOKUP函数是在单行区域或单列区域中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值LOOKUP

11、(lookup_value,lookup_vector,result_vector)说明：lookup_vector为第一个包含一行或一列的区域；result_vector为第二个包含一行或一列的区域，其大小必须与参数lookup_vector相同第23页，共74页。HLOOKUP实现水平查找使用HLOOKUP函数可以在表格的首行查找指定的数值,并由此返回指定行处与该数值同一列的数值，当比较值位于数据表首行时，可使用此函数语法：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)第24页，共74页。VLOOKUP_实现竖直查找

12、在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时,可以使用此函数语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)第25页，共74页。COUNTCOUNTIFSUMIFINTMOD2.2.4统计计数类函数第26页，共74页。COUNT COUNT函数的作用：返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 可以计算单元格区域或数字数组中数字字段的输入项个数。语法：COUNT(value1,value2,.)参数：Value1, value

13、2, . 为包含或引用各种类型数据的参数（1 到 30个），但只有数字类型的数据才被计算。说明函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。 如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。第27页，共74页。COUNTIF函数可累计满足某一条件的对象在单元格区域中出现的次数，该条件可以是数值、文本或表达式，在人事、工资和成绩统计中有广泛用途。 COUNTIF函数语法： COUNTIF（range,criteria)第28页，共74页。SUMIF函数可对满足

14、某一条件的单元格区域求和，该条件可以是数值、文本或表达式。SUMIF函数语法： SUMIF（range,criteria,sum_range)第29页，共74页。INT INT函数的作用：将数字向下舍入到最接近的整数。语法：INT(number)参数：number是需要进行向下舍入取整的实数。MOD 返回两数相除的余数。结果的正负号与除数相同。语法MOD(number,divisor)；Number为被除数；Divisor为除数。注解如果 divisor 为零，函数 MOD 返回错误值 #DIV/0!。函数 MOD 可以借用函数 INT 来表示:MOD(n, d) = n - d*INT(n/

15、d) 第30页，共74页。PMT（）贷款函数；基于固定利率及等额分期付款方式，返回贷款的每期付款额。语法PMT(rate,nper,pv,fv,type)Rate贷款利率。Nper该项贷款的付款总数。Pv现值，或一系列未来付款的当前值的累积和，也称为本金。Fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为零，也就是一笔贷款的未来值为零。Type数字 0 或 1，用以指定各期的付款时间是在期初还是期末。注解 PMT 返回的支付款项包括本金和利息，但不包括税款、保留支付或某些与贷款有关的费用。 应确认所指定的 rate 和 nper 单位的一致性。例如，同样是四年期年

16、利率为 12% 的贷款，如果按月支付，rate 应为 12%/12，nper 应为 4*12；如果按年支付，rate 应为 12%，nper 为 4。 2.2.6财务函数及案例应用第31页，共74页。三、图表及打印输出及其它设置创建Excel图表3.1编辑Excel图表3.2打印输出及其它设置3.3第32页，共74页。柱形图：用来显示一段时间内数据的变化或者描述各个项目之间数据比较图表线性图：主要用于以等时间间隔显示数据的变化趋势，强调的是时间性和变动率，而不是变动量饼形图：显示数据系列中的项目和该项目数值总和的比例关系。它只能显示一个系列的数据比例关系图表在数据分析中的作用第33页，共74页

17、。确定分析目标明确表格数据的取值范围图表向导使用一键成图创建图表3.1 创建分析图表实例11 图表类型汇总第34页，共74页。常规编辑技巧改变图表大小移动图表位置图表内其他对象编辑图表的特殊编辑向图表添加数据删除图表内数据3.2 图表的编辑、美化技巧文字数据修饰线和阴影修饰背景修饰设置图表的序列格式综合案例分析第35页，共74页。页面设置 工作表的页眉、页脚分页符和打印选项 工作表参数设置 设置打印区域自动打印统一表头打印指定页面 打印连续页码一次打印多个工作表使某个工作表不能被打印3.3打印输出及其它设置第36页，共74页。四、数据分析及管理数据的筛选4.2数据的分类汇总4.3数据透视表4.

18、44.1数据的排序 4.1第37页，共74页。4.1 排序数据排序时，一般不要选择数据范围，以防止破坏数据的结构针对多列进行排列使用排序对话框进行相应操作简单排序多字段排序按行排序自定义序列排序实例12 数据排序第38页，共74页。4.2 数据的筛选分析 自动筛选高级筛选条件区域的设置原则工作表中指定一个空白区域作条件区域条件区域第一行是条件名(字段名)行，第二行是输入的条件条件区域中不能包含有空行或空列，单元格中不能包含空格字符字段的条件可以使用通配符* 和 ?注意：在具体写条件时，我们要分析好条件之间是与关系还是或关系。若是与关系，条件要写到同一行中，若是或关系，条件要写到不同行中实例13

19、 高级筛选第39页，共74页。分类汇总之前必须之操作注意：使用“分类汇总”功能时，首先要确定分类的依据，然后按选定的分类依据将数据排序！ 建立分类汇总使用分级功能查看汇总结果4.3 数据分类汇总实例14 分类汇总第40页，共74页。4.4 数据透视表透视表的布局及更新 数据刷新 添加字段数据的分组 格式化报表 创建数据透视图实例15 数据透视表第41页，共74页。历史岳麓版第13课交通与通讯的变化资料精品课件欢迎使用第42页，共74页。第43页，共74页。第44页，共74页。第45页，共74页。自读教材填要点 一、铁路，更多的铁路 1地位 铁路是 建设的重点，便于国计民生，成为国民经济发展的动

20、脉。 2出现 1881年，中国自建的第一条铁路唐山 至胥各庄铁路建成通车。 1888年，宫廷专用铁路落成。交通运输开平第46页，共74页。 3发展 (1)原因： 甲午战争以后列强激烈争夺在华铁路的 。 修路成为中国人 的强烈愿望。 (2)成果：1909年 建成通车；民国以后，各条商路修筑权收归国有。 4制约因素 政潮迭起，军阀混战，社会经济凋敝，铁路建设始终未入正轨。修筑权救亡图存京张铁路第47页，共74页。 二、水运与航空 1水运 (1)1872年, 正式成立，标志着中国新式航运业的诞生。 (2)1900年前后，民间兴办的各种轮船航运公司近百家，几乎都是在列强排挤中艰难求生。 2航空 (1)

21、起步：1918年，附设在福建马尾造船厂的海军飞机工程处开始研制 。 (2)发展：1918年，北洋政府在交通部下设“ ”；此后十年间，航空事业获得较快发展。轮船招商局水上飞机筹办航空事宜处第48页，共74页。 三、从驿传到邮政 1邮政 (1)初办邮政： 1896年成立“大清邮政局”，此后又设 ，邮传正式脱离海关。 (2)进一步发展：1913年，北洋政府宣布裁撤全部驿站；1920年，中国首次参加 。邮传部万国邮联大会第49页，共74页。 2电讯 (1)开端：1877年，福建巡抚在 架设第一条电报线，成为中国自办电报的开端。 (2)特点：进程曲折，发展缓慢，直到20世纪30年代情况才发生变化。 3交

22、通通讯变化的影响 (1)新式交通促进了经济发展，改变了人们的通讯手段和 ， 转变了人们的思想观念。 (2)交通近代化使中国同世界的联系大大增强，使异地传输更为便捷。 (3)促进了中国的经济与社会发展，也使人们的生活 。台湾出行方式多姿多彩第50页，共74页。合作探究提认知 电视剧闯关东讲述了济南章丘朱家峪人朱开山一家，从清末到九一八事变爆发闯关东的前尘往事。下图是朱开山一家从山东辗转逃亡到东北途中可能用到的四种交通工具。第51页，共74页。 依据材料概括晚清中国交通方式的特点，并分析其成因。 提示：特点：新旧交通工具并存(或：传统的帆船、独轮车， 近代的小火轮、火车同时使用)。 原因：近代西方

23、列强的侵略加剧了中国的贫困，阻碍社会发展；西方工业文明的冲击与示范；中国民族工业的兴起与发展；政府及各阶层人士的提倡与推动。第52页，共74页。串点成面握全局第53页，共74页。第54页，共74页。 一、近代交通业发展的原因、特点及影响 1原因 (1)先进的中国人为救国救民，积极兴办近代交通业，促进中国社会发展。 (2)列强侵华的需要。为扩大在华利益，加强控制、镇压中国人民的反抗，控制和操纵中国交通建设。 (3)工业革命的成果传入中国，为近代交通业的发展提供了物质条件。第55页，共74页。 2特点 (1)近代中国交通业逐渐开始近代化的进程，铁路、水运和航空都获得了一定程度的发展。 (2)近代中

24、国交通业受到西方列强的控制和操纵。 (3)地域之间的发展不平衡。 3影响 (1)积极影响：促进了经济发展，改变了人们的出行方式，一定程度上转变了人们的思想观念；加强了中国与世界各地的联系，丰富了人们的生活。 (2)消极影响：有利于西方列强的政治侵略和经济掠夺。第56页，共74页。1李鸿章1872年在上海创办轮船招商局，“前10年盈和，成为长江上重要商局，招商局和英商太古、怡和三家呈鼎立之势”。这说明该企业的创办()A打破了外商对中国航运业的垄断B阻止了外国对中国的经济侵略C标志着中国近代化的起步D使李鸿章转变为民族资本家第57页，共74页。解析：李鸿章是地主阶级的代表，并未转化为民族资本家；洋

25、务运动标志着中国近代化的开端，但不是具体以某个企业的创办为标志；洋务运动中民用企业的创办在一定程度上抵制了列强的经济侵略，但是并未能阻止其侵略。故B、C、D三项表述都有错误。答案：A第58页，共74页。 二、近代以来交通、通讯工具的进步对人们社会生活的影响 (1)交通工具和交通事业的发展，不仅推动各地经济文化交流和发展，而且也促进信息的传播，开阔人们的视野，加快生活的节奏，对人们的社会生活产生了深刻影响。 (2)通讯工具的变迁和电讯事业的发展，使信息的传递变得快捷简便，深刻地改变着人们的思想观念，影响着人们的社会生活。第59页，共74页。2清朝黄遵宪曾作诗曰：“钟声一及时，顷刻不少留。虽有万钧

26、柁，动如绕指柔。”这是在描写 ()A电话 B汽车C电报 D火车解析：从“万钧柁”“动如绕指柔”可推断为火车。答案：D第60页，共74页。第61页，共74页。典题例析 例1上海世博会曾吸引了大批海内外人士利用各种交通工具前往参观。然而在19世纪七十年代，江苏沿江居民到上海，最有可能乘坐的交通工具是 () A江南制造总局的汽车 B洋人发明的火车 C轮船招商局的轮船 D福州船政局的军舰第62页，共74页。 解析由材料信息“19世纪七十年代，由江苏沿江居民到上海”可判断最有可能是轮船招商局的轮船。 答案C第63页，共74页。题组冲关1中国近代史上首次打破列强垄断局面的交通行业是 ()A公路运输 B铁路

27、运输C轮船运输 D航空运输解析：根据所学1872年李鸿章创办轮船招商局，这是洋务运动中由军工企业转向兼办民用企业、由官办转向官督商办的第一个企业。具有打破外轮垄断中国航运业的积极意义，这在一定程度上保护了中国的权利。据此本题选C项。答案：C第64页，共74页。2. 右图是1909年民呼日报上登载的一幅漫画，其要表达的主题是( )A帝国主义掠夺中国铁路权益B西方国家学习中国文化C西方列强掀起瓜分中国狂潮D西方八国组成联军侵略中国第65页，共74页。解析：从图片中可以了解到各国举的灯笼是火车形状，20世纪初的这一幅漫画正反映了帝国主义掠夺中国铁路权益。B项说法错误，C项不能反映漫画的主题，D项时间上不一致。答案：A第66页，共74页。第67页，共74页。典题例析 例2(2010福建高考)上海是近代中国茶叶的一个外销中心。1884年，福建茶叶市场出现了茶叶收购价格与上海出口价格同步变动的现象。与这一现象直接相关的近代事业是 () A电报业 B大众报业 C铁路交通业 D轮船航运业 解析材料主要反映了信息交流的快捷，故选