《Excel使用技巧》PPT课件

1、Microsoft Excel 操作技巧及常用函数应用,Excel基础和操作技巧,工作表基础 工作表编辑 Excel键盘快捷键 保护工作表,1.1 工作簿 当我们打开一个工作簿时，看到的是“BOOK1”，在默认的情况 下，每一个工作簿文件会打开3个工作表，分别是sheet1、sheet2、 sheet3来命名,每一个工作簿内最多可以有255个工作表。,可以根据需要改变默认值，方法是：使用“工具” “选项”命令， 单击“常规”选项卡，然后改变新工作簿中的工作表设置的值。,1.2 工作表 工作表是由65,536行和256列所构成的一个表格，行号的编号是由 上自下从“1”到“65,536”，列号由左

2、向右采用字母编号为“A”.“IV” 分别可用Ctrl +和Ctrl + 进行查找。 1.2.1 快速增加工作表格 Ctrl +“选定多个工作表” 按鼠标右键单击“插入”选项卡,1.2.2 复制可视单元格 选中需要复制的单元格,使用“编辑”菜单中的“定位”命令项下的“定位条件” 选择“可见单元格”选项，单击“确定”, 复制、粘贴,1.2.3 增加常用工具栏 选中“工具”菜单中的“自定义”选项卡 从“自定义”选项卡中的“命令”选项中选择, 按鼠标左键将其拖放到常用工具栏,1.3 设置单元格 1.3.1定义单元格为条件格式单元格 当你想将某个单元格定义为条件单元格时，方法是： 选定需要设置的单元格

3、使用“格式”菜单中“条件格式”, 选用条件后单击“确定”,1.3.2为单元格增加提示信息与有效数据检验 选定单元格 使用“数据”菜单中的“有效性”命令, 单击“设置”选项卡，在有效条件的列表框上单击，选择需要的类型 单击“确定”,2、工作表编辑 2.1 快速制表 按住Ctrl键，用鼠标同时选中多个工作表，即变为工作组 输入内容或制作表格 或 同时选中几个工作表，将工作表变为工作组 选中需要复制的内容的单元格 使用“编辑”菜单中的“填充”项下的“至同组工作表”，选择需要的 类型 单击“确定”,2.2 快速录入数据 2.2.1 如果需要在一个范围内的所有单元格中录入相同的数据，往活动单元 格中录入

4、信息，然后按Ctrl + Enter组合键 2.2.2在增加新行数据时，如果某项数据与前一行重复，可以使用 Ctrl+Shift+”快捷键进行快速复制，而不必重复输入。 2.3 在Excel中，当建立新的工作表时，所有单元格都采用默认的通用数字 格式，每个单元格可输入11位数字，当数字长度超过单元格的宽度时，Excel 将自动使用科学计数法来表示，这时可输入项前添加“ ”的方法来区分 是“数字字符串”和“数字”，“数字字符串”不参与计算。,2.4 记忆式输入法 可在同一数据中快速填写重复录入项，方法是： 在要输入内容的单元格上按Alt+ 2.5 如果需要使用一个数值的增值填充一个范围，拖动这个

5、单元格右下角填 充柄的同时按Ctrl键,2.6 快速互换两列/行之间的数据，其方法是： 用鼠标选定A列数据区域；把鼠标放在A列数据区域的右边；按下 “Shift”键的同时，按下鼠标左键，这时鼠标变为向左的箭头；拖动鼠标至 B列数据区域的右边，看到一条垂直的虚线(如果看到一条水平的虚线，表示 在B列插入数据)，同时松开“Shift”键和鼠标左键，这样就实现了A、B列的 数据互换。同样，也可以实现两行数据的互换。,2.7 分列的妙用,我们可以分列功能将编号和文本分开。选中此列，单击“数据”菜单中 的“分列”命令，弹出“文本分列向导”对话框。,在对话框中提供了两类数据分割方法，一是按固定宽度，一是按

6、分隔符 号。将姓与名分开的问题就可以通过选择“固定宽度”项来解决，当前这个 例子可以选择“分隔符号”项，单击“下一步”。,这里要选择分隔符号，因为例子中的编号和文本中间都有“.”，所以 我们可以选择“其他”复选框，并在后面的文本框中输入“.”，再单击“ 下一步”。,单击“完成”按钮，编号和文本就被分开了。,2.8 单元格中设置“斜线”效果,实现在单元格中分行的效果：我们首先在单元格中输入“性别”，这时候 按回车键的话，光标就会转到其他单元格中去了，所以必须按住“Alt”键的 同时按回车键，光标转到下一行，接着输入“姓名”，然后再在“性别”前加入 若干空格键，即可达到如图2的效果。 。,图1,图

7、2, 在单元格中设置斜线：选择单元格，在右键菜单中选择“设置单元格 格式”，在出现的对话框中选择“边框”标签，进行斜线的设置(如图3)。,3、Excel键盘快捷键 3.1 插入、删除和复制单元格,3.2 设置数据格式, 注意：“2”、“3”、“4”不能使用数字键盘,续上表, 注意：所有数字必须使用数字小键盘,3.3 用于选择数据和单元格的按键, 注意：“9”、“0”不能使用数字键盘,3.4 用于输入数据或查找的按键,3.5 处理工作表,4、工作表保护 4.1 保护工作表 选中可能被覆盖的单元格 从“格式”菜单中的选择“单元格”命令，选定“保护”选项卡, 在“保护”选项卡中，取消标记“锁定”的选

8、择框,单击“确定”按钮，关闭“单元格格式”对话框 使用“工具”菜单中的“保护工作表”命令,单击“保护工作表”选项卡，选择需要的选项并输入密码后单击 “确定”,4.2 隐藏公式 使用“工具”菜单中“允许用户编辑区域”定义需要编辑区域 选定想隐藏公式的单元格范围 从“格式”菜单中的选择“单元格”命令，取消“保护”选项卡 选择标记为“隐藏”的选择框，单击“确定”按钮, 使用“工具”菜单中的“保护工作表”命令 单击“保护工作表”选项卡，选择需要的选项并输入密码后单击“确定”,4.3保护工作簿 （1）、使用“工具”“保护”“保护工作簿”命令 （2）、可以进入“保护工作簿”对话框，选中“结构”复选项。,4

9、.4保护Excel文件 选择“文件”菜单中的“另存为”命令 在“另存为”对话框中选择“工具”栏 选中“工具”栏中的“常规选项”，出现“保存选项”复选框, 在“保存选项”复选框中输入密码后单击“确定”,Excel函数,函数 数学和三角函数 查找与引用 统计函数 文本函数 逻辑 日期与时间,财务函数 数据库 信息 工程 用户定义,运算符号与类别,常见的几种错误信息,使用函数进行运算时需要注意的事项,函数名与其后的“（”之间不能有空格 当有多个参数时，参数之间用“，”分隔 参数部分总长度不能超过1,024个字符， 注意函数格式中的“（” 、“）”和“，”都是半角字符，而非全角 的中文字符 参数的引用

10、 相对引用(A1)、绝对引用($A$1)、混合引用(A$1,$A1),1、数学与三角函数函数 1.1 SUM函数 SUM函数即求和函数,用于计算一系列数的和,它的格式为 =SUM（参数1，参数2，参数N） 其中,N=30，参数可以是数值、公式和区域等。 1.2 SUMIF函数 SUM函数用于分类汇总，其格式为 =SUMIF（查找范围，查找条件，求和范围） 其中：“查找范围”是“查找条件”的作用区域；“查找条件”是进行查询 的条件；“求和范围”是求和的区域，与“查找范围”有一定的对应关系。,1.3 ROUND函数 ROUND函数是按指定的位数对数值进行四舍五入保留，其格式为： =ROUND（参数

11、或数值，保留位数） 通常与SUM一起使用。 1.4 ROUNDDOWN函数 ROUNDDOWN函数是按指定的位数对数值向下舍去数字，其格式与ROUND 相同。 1.5 ROUNDUP函数 ROUNDUP函数是按指定的位数对数值向上舍去数字，其格式与ROUND 相同。,2、查找与引用函数 2.1 VLOOKUP函数 VLOOKUP函数用于搜索表区域首列满足条件的元素，确定待检单元格 在区域中的行序号，再一步返回选定单元格的值，默认情况下表是以升序 排序的。其格式为： =VLOOKUP（查找值，查找表或区域，索引列号，检索类型） 函数中的第4项为“检索类型”，它的取值可以是0或1。如果取1或忽略

12、时，允许非精确匹配，但“查找表”中第1列的值必须按升序排列，否则函 数不能给出正确值；如果取0的话，则必须是精确匹配，此时“查找表”中 的值不需要排序。,2.2 HLOOKUP函数 HLOOKUP函数用于搜索表区域首列满足条件的元素，确定待检单元格 在区域中的行序号，再一步返回选定单元格的值，默认情况下表是以升序 排序的。其格式为： = HLOOKUP（查找值，查找表或区域，索引列号，检索类型） 该函数与VLOOKUP函数类似，不同之处在于它是首行而非首列进行检 索,3、统计函数 3.1 COUNTIF函数 COUNTIF函数用于计算满足参数中所指定条件的单元格的个数。其格 式为： =COUN

13、TIF（计算范围，条件） 其中条件可以是常数、表达式、函数、单元格引用等。,4、文本函数 4.1 CONCATENATE函数 CONCATENATE函数是用于将多个文本字符串合并成一个。其格式为： =CONCATENATE（参数1，参数2，参数N） 其中N=30。例如：“=CONCATENATE（“AB”，“CD”）”的返回值 为“ABCD”。,4.2 LEFT函数 LEFT函数是用于从一个字符串的第一个字符开始返回指定个数的字 符。其格式为： =LEFT（字符串，N） 例如：“=LEFT（“ABCD”，1）”的返回值为“A”。使用该函数 应注意N的取值应小于字符串的总长度。,4.3 RIGH

14、T函数 RIGHT函数是用于从一个文本字符串的最后一个字符开始返回指定个 数的字符。其他格式为： =RIGHT（字符串，N） 例如：“=RIGHT（“ABCD”，2）”的返回值为“CD”。使用该函数 应注意N的取值应小于字符串的总长度,4.4 MID函数 MID函数是用于从文本字符串中指定的起始位置起返回指定长度的字 符。其格式为： =MID（字符串，M，N） 例如：“=MID（“ABCD”，1，2）”的返回值为“AB”。使用该函数 应注意M和N的取值应小于字符串的总长度,5、逻辑函数 5.1 IF函数 IF函数是用于判断一个条件是否满足，如果满足返回一个值，如果不 满足则返回另一个值。其格式

15、为： =IF（逻辑表达式，真值，假值） 当逻辑表达式成立时，返回真值，否则返回假值。 例如：“IF=（1020,15,20）”的返回值为“15”，即当1020成立 时，返回的值为真值15，否则，返回值为假值20。,5.2 AND函数 AND函数是用于检查是否所有逻辑表达式均同时满足要求。其格式为： =AND（逻辑表达式1，逻辑表达式2，逻辑表达式N） 表示逻辑表达式1，逻辑表达式2，逻辑表达式N同时成立。通常 与IF 同时使用。 例如：“IF（AND（1015,2030），50，0）”的含义为：当1015和 2030同时成立时则返回值“50”，否则返回“0”。,5.3 OR函数 OR函数是用于

16、检查是否逻辑表达式中有任一参数值满足要求。其格式 为： =OR（逻辑表达式1，逻辑表达式2，逻辑表达式N） 表示逻辑表达式1，或逻辑表达式2，或逻辑表达式N成立。通常 与IF同时使用。 例如：“IF（OR（1015,2030），50，0）”的含义为：当1015或 2030成立时则返回值“50”，否则返回“0”。,6、日期与时间函数 6.1 TODAY函数 TODAY函数是用返回系统的当前日期，其格式为： =TODAY（） 注意：该函数没有参数。,6.2 WEEKDAY函数 WEEKDAY函数可以接受一个日期参数，返回1到7（相对星期的天数） 之间的整数。其格式为： =WEEKDAY（DATE（

17、日期）,参数） 例如：=WEEKDAY（DATE（2002，1，11）,1）返回值为3，表示2002年 1月11日是星期二。 WEEKDAY函数使用3个可选参数，确定结果的日期编号系统。如果确 定第一个参数为1，函数返回结果是：星期日为1，星期一为2，依此类推。 如果确定第二个参数为2，函数返回结果是：星期一为1，星期二为2，依此 类推。如果确定第三个参数3，函数返回结果是：星期一为0，星期一为1，依此类 推。省略系统默认为1。,6.3 DATEDIF函数 DATEDIF函数可以计算两日期之间的天数、月数、年数。其格式为： =DATEDIF（起始日期，结束日期，时间单位代码） 其中单位代码包括

18、：“Y”表示这个阶段的整年数；“M”表示这阶段 的整月数；“D”表示这个阶段的天数；“MD”表示从起始日期到结束日期 的天数差，忽略日期的年和月；“YM” 表示从起始日期到结束日期的月份 之间的差，忽略日期的年和天数；“YD” 表示从起始日期到结束日期的天 数之间的差，忽略日期的年数。注意单位代码必须加引号。,7、财务函数 7.1 FV函数 FV函数用于固定利率及等额分期付款方式，计算某项投资的未来值。 （即零存整取）其格式为： =FV（rate，nper，pmt，pv，type） 其中：rate表示利率 nper表示付款总次数 pmt表示各期支出的金额 pv表示开始入账前的金额，省略则默认为0,type表示付款时间，取0，1或省略，取值为0或省略时表示期末付款， 取值为1时表示期初付款。 例如：每月存入500元，年利率为10%，5年后能得到多少？ =FV（10%/12，5*12，500） 返回值为-38,718.54元，该项存款5年后能得到38,718.54