EXCEL基础应用培训课程(共66张).pptx

前言（Preface） Excel 是一个功能强大的电子表格软件，是 Office 办公自动化系列软件的重要组成部分之一。 Excel 具有强大的数据计算与分析功能，可以把数据用各种统计图的形式形象地表示出来，被广泛应用于财务、金融、经济、审计和统计等众多领域。 熟练使用Excel，掌握Excel的功能，能大大的提高我们的工作效率。2 2培训内容3 3Excel基础认识及设置Excel常用函数筛选与排序数据透视表与图表链接及超级链接Excel认识及基础设置4 4Excel主窗口界面Excel基本要素Excel简化操作Excel常用快捷键Excel认识及基础设置目录5 5Excel主窗口界面Excel基本要素Excel简化操作Excel常用快捷键6 6标题栏标题栏菜单栏菜单栏工具栏工具栏名称框名称框编辑框编辑框工作表区工作表区工作表标签区域工作表标签区域状态栏状态栏滚滚动动条条区区域域滚动条区域滚动条区域Excel基本要素7 7要素解释工作簿 book存储并处理数据的文件，可包含多个工作表，系统默认为3个工作表sheet由行和列组成的表格，用来存储和处理数据的基础行row行标为数字，每一工作表最多有65,536行列column列标为字母，每一个工作表最多有256列单元格cellexcel基本要素，列标行标表示，如：A8、CD26，每一个工作表最多有1,677,216个单元格备注：2007版本中最多有1,048,576行、16,384列Excel简化操作8 8提高效率善用右键操作自定义工具栏选项设置良好习惯Excel使用应具备的良好习惯一、同一对象请使用同一名称，而不要使用简写、别称，以便使用函数查找、引用，如果你使用过vlookup等函数你就可体会到这个习惯的必要性。同理，不同部门间对同一对象亦应统一名称或编码，便于数据的传递、引用。二、排版时不要在字符串内、前后插入空格，尽量使用对齐功能来排版，以便使用函数查找、引用。9 9Excel使用应具备的良好习惯三、在要使用公式的单元格，尽量不使用合并单元格功能（可用跨列对齐来居中）以便填充公式、排序、筛选。四、排版时使用顶端标题行、标题列功能、而不要笨拙、野蛮地强制性插入标题行、标题列。五、数字及文本的对齐方式请使用Excel默认的格式，即数字靠右对齐，文本靠左对齐。数字使用千位分隔符。1010顶端标题行设置窗口11 11Excel使用应具备的良好习惯六、学会使用拆分窗口功能，免除为看到标题行而大量滚屏，以提高工作效率。七、工作簿内工作表较多时，使用工作表标签的快捷菜单，快速选取工作表。通过用鼠标右键单击标签滚动按钮，显示快捷菜单。八、经常使用快捷键，提高工作效率。九、在同一工作簿的不同工作表的相同单元格输入相同的数据或设置相同的格式，请使用组合工作表功能，以提高效率。1212文本数字时间文本格式 9,864,882.66 2008-7-12009年8月8日99.33%2008-7-24 9:29:440112800023477865521:22:08EXCEL1998.22Jul-22 1/2字符显示格式范例1313Excel使用应具备的良好习惯十、 在要进行乘除等运算时，要养成在公式的最外层使用round函数的习惯，以免出现四舍五入的误差。十一、在计算结果可能出现#DIV/0!、#VALUE!、#N/A等错误，而其它单元格又需对此单元格进行运算时，要养成使用if和ISERROR函数消除错误值的习惯。十二、要对工作表进行临时性排序时，养成添加序号列的习惯，以便恢复原来的顺序。十三、养成使用状态栏进行临时性的求和、计数、求平均值的习惯。1414工作簿选项设置u 统一工作表的外观、格式，减少重复调整操作1515目的操作u【工具】选项自定义工具栏位置： 工具栏操作：【工具】自定义：分为工具栏、命令、选项三个选项卡。在“命令”选项卡可以将你要经常用到的一些命令拖到工具栏上，以方便操作，不要时将其拖回去即可。1616善用鼠标右键1717单元格行工作表常用快捷键1818移动光标编辑工作表填充工作表其他快捷键移动光标1919Ctrl+光标键 移动到工作表边缘； Ctrl+Home 回到A1； Ctrl+End 去到本工作表的最尾端； Ctrl+Page Up (Down) 在同一工作簿中向前、向后切换工作表； Shift+Enter在选定区域内从下往上或从右往左移动，即与 Enter的移动方向相反； Shift+Tab在选定区域内从右至左由下至上移动，即与Tab的移动方向相反。编辑工作表(一)2020快捷键快捷键用法解释用法解释Ctrl+A全部选定Ctrl+空格键选定整列注：如果与输入法切换的快捷键相冲突时，输入法切换优先Shift+空格键选定整行注：此快捷键与全角半角切换的快捷键相冲突时，输入法切换优先Ctrl+C复制Ctrl+X剪切Ctrl+V粘贴Ctrl+Alt+V选择性粘贴Ctrl+Shift+（双引号）将活动单元格上方单元格中的数值复制到当前单元格或编辑栏编辑工作表(二)2121快捷键用法解释Ctrl+（撇号）：将活动单元格上方单元格中的公式复制到当前单元格或编辑栏Ctrl+B单元格内文字加粗Ctrl+3 单元格内文字变成斜体Ctrl+4单元格内文字加下划线 Ctrl+Z回到前一步Alt+Tab切换窗口功能键F4、 Alt+Enter重复上一步操作功能键F2或Ctrl+在显示公式和计算结果之间切换，若单元格内为文字，按F2直接在文字的最后端显示光标，可直接进行修改编辑工作表(三)2222快捷键用法解释Ctrl+9隐藏选中单元格所在行（取消隐藏用Ctrl+Shift+9）。 Ctrl+0隐藏选中单元格所在列（取消隐藏用Ctrl+Shift+0）。 Ctrl+F9最小化工作簿窗口。 Ctrl+F10最大化或还原选定的工作簿窗口。 PrtScr将屏幕图片复制到剪贴板中。 Alt+ PrtScr将当前窗口的图片复制到剪贴板中。 Alt+ Enter编辑单元格时换行输入。填充工作表填充工作表2323快捷键用法解释Ctrl+Enter在多个单元格输入同一内容，即先选中所需填写的单元格，输入数据，最后用Ctrl+Enter即可。（不受单元格相邻与否的限制） Ctrl+DR向下右填充。(相邻单元格之间)双击填充柄复制公式到整列（至文件尾），即选中有公式的单元格，待右下角出现黑色填充柄时，双击即可。 Shift +F11(Alt+Shift+F1)快速插入新工作表。 右键单击工作薄左下角的工作表滚动轴(sheet tabs)在快捷菜单中选择目标工作表。 快速选择的工作表Alt+ =(等号)用 SUM 函数插入“自动求和”公式。 Ctrl+;（分号）输入当前日期。 Ctrl+Shift+:（冒号）输入当前时间。其他快捷键 Excel的帮助系统中有大量常用的快捷键的示例说明，在实际应用中，可以点击【帮助】菜单选项在弹出的对话框中输入快捷键来搜素相关帮助信息。2424常用函数函数基本格式及图示理解常用函数语法及应用实例2525函数的基本格式 必须以=开头，加上括号和自变量 自变量可以是：范围、地址、数值、函数、范围名称、文字、逻辑值 =SUM(c2:h10,k5,100,average(m3:m6)以SUM为例，自变量最多可达30个 有自变量的函数 =SUM(c2:h10) 没有自变量的函数 =NOW()、=TODAY()2626函数理解 函数的作用：将指定的数据按照一定的规则转化为需要的结果2727输入函数的方法直接在单元格中输入完整的函数内容 =sum(c2:h10)先输入指令和左括号再拖曳范围 =sum(点选插入/函数，使用插入函数对话窗口2828常用函数语法及应用实例数学函数逻辑判断函数取值函数Vlookup/Hlookup函数时间函数转换函数2929数学函数 加总：=SUM(D3:D6) 相乘：PRODUCT(5, 4)=20 取整数=INT(123.579) =123 指定小数位数=ROUND(123.579,2) =123.58 无条件舍位=ROUNDDOWN(123.579,2) =123.57 无条件进位=ROUNDUP(123.572,2) =123.58 距阵相乘：=SUMPRODUCT(B6:E6,A3:D3) 求余数：=MOD(9,2)=1 求商：=QUOTIENT(8,3)=2 倍数进位：=CEILING(17.3,0.5)=17.5 =TRUNC(17.3,0.5)=173030SUMIF求和函数语法格式：SUMIF(比对的范围,条件,要加总的范围)示例：=SUMIF(D2:D10,”=1000000”)=SUMIF(资产类别,D5,期初金额)3131逻辑判断函数IF=IF(条件,条件成立使用此值,不成立使用此值)=IF(D2=90,”优”,”)=IF(成绩=90,”优”,IF(考绩=80,”甲”, IF(成绩=70,”乙”, IF(成绩=60,”丙”, ”丁”)=IF(数量1000000,金额*0.05,0)uIF语句最多只能嵌套7层3232IF组合语句应用=IF(AND(B7=90,C7=90,D7=90),“优秀,)=IF(OR(B760,C760,D790) ,1,2)返回TRUE 或FALSE3333数值类型判断函数ISIS相关函数(共有11个)，返回TRUE 或FALSEISNA()ISTEXT()ISBLANK()ISERROR()3434取值函数之字符串 字符串比较=EXACT(“北京”,“北京 ) =FALSE 删除字符串右边空格=TRIM(“北京 “) =“北京” 套用指定格式并转换成文字 =TEXT(123456789,“$ #,#0 元整”) =$ 123,456,789 元整 重复字符串=REPT(,ROUND(D3*100,0) Right、left、mid函数3535取值函数之小数取值函数之小数取整函数 =INT(123.579) =123 =TRUNC(123.579) =123指定小数位数=ROUND(123.579,2) =123.58 =ROUNDDOWN(123.579,2) =123.57 =ROUNDUP(123.572,2) =123.58倍数进位函数=CEILING(15.6,0.5) =16 =FLOOR(15.6,0.5) =15.53636Vlookup/Hlookup函数函数功能： Vlookup用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值 Hlookup用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值 当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用Hlookup 当比较值位于数据表的最左列，并且要查找右面给定列中的数据时，请使用Vlookup3737使用方法使用方法 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) ) =VLOOKUP(A2,對照表,2,FALSE 或true) 使用true時，對照表中的資料必須由小到大排序 HLOOKUP() 只是資料排列的方向不同 LOOKUP(lookup_value,lookup_vector,result_vector ) =LOOKUP(A2,B2:B10,C2:C10) 對照表中的資料必須由小到大排序3838时间函数时间函数(一一) Excel的日期范围：1900/1/1-9999/12/31 NOW() = 2006/1/20 15:18返回当前日期和时间 TODAY() = 2006/1/20返回当前日期 DATEVALUE(日期)将字符串转换成天数=DATEVALUE(2005/12/31)=38717=DATEVALUE(“2008/7/1”) -TODAY()=921=“从今天到2008/7/1共经过&DATEVALUE(2008/7/1) -TODAY() &天”3939时间函数时间函数(二二) DAY(TODAY() =27 第几天 MONTH(TODAY() =12 第几个月 YEAR(TODAY() =2005 第几年 WORKDAY(开始日期，经过天数,假日) 计算二指定日期之间的工作天(可扣除特别假) NETWORKDAYS(开始日期,结束日期,假日) 计算二指定日期之间的可工作天数 =NETWORKDAYS(2005/12/25,2006/1/25) =NETWORKDAYS(D1,D2)4040时间函数时间函数(三三) DATEDIF() 生日、年资计算(计算到今天) =DATEDIF(D3,TODAY(),y) =17 (D3=1988/7/16) =DATEDIF(D3,TODAY(),“ym) =5 =DATEDIF(D3,TODAY(),“md) =11 =DATEDIF(D3,TODAY(),“m”) =209個月 =DATEDIF(D3,TODAY(),“d”) =6373天 半年之后的票期(配合TEXT()取得中文日期格式) =“半年后到期日为：”&TEXT(TODAY()+180,”ee年mm月dd日”) =TEXT(D16,yyyy年mm月dd日“)4141转换函数转换函数 LOWER：所有大写转换为小写； 语法：lower（text） UPPER：所有小写转换为大写；语法：upper（text） PROPER：首字母及任何非字母后的首字段转换大写，其他字母转换为小写 语法：proper（text） VALUE：将一个代表数值的文本字符串转换成数值 语法：value（text） TEXT：将任意类型的字符串转换成文本 语法：text（text）4242数据透视表数据透视表数据透视表概念数据透视表应用4343数据透视表概念 数据透视表是“数据”菜单中“数据透视表与数据透视图”的一部分， “数据透视表与数据透视图”有机的综合了数据排序、筛选、分类汇总等数据分析的优点，可方便地调整分类汇总的方式，灵活地以多种不同方式展示数据的特征。如计算平均数、标准差，建立列联表、计算百分比、建立新的数据子集、绘制统计图形等均可利用这一工具完成。因此，该工具是最常用、功能最全的EXCEL数据分析工具之一。4444创建数据透视表步骤：1、在数据工作表选定任意单元格，单击菜单中的“数据数据透视表与数据透视图” ，弹出“数据透视表与数据透视图向导3步骤之1”对话框；2、单击“下一步”按纽框，弹出“数据透视表与数据透视图向导3步骤之2”对话框；3、确定EXCEL已正确识别数据的单元格区域（一般自动识别），如没有自动识别，则用鼠标进行选取，然后单击“下一步”按纽，弹出“数据透视表与数据透视图向导3步骤之3”对话框4、单击完成键，EXCEL创建了一张新的工作表。4545“数据透视表与数据透视图向导3步骤之1”对话框4646“数据透视表与数据透视图向导数据透视表与数据透视图向导3步骤之步骤之2”对话框对话框4747“数据透视表与数据透视图向导数据透视表与数据透视图向导3步骤之步骤之3”对话框对话框4848数据透视表雏形4949页字段页字段数据字段数据字段行字段行字段列字段列字段分类变量，分类变量，该变量的该变量的分组类别分组类别排在一行，排在一行，每个变量每个变量占一列。占一列。可放多个可放多个变量。变量。分类变量，分类变量，该变量的该变量的分组类别分组类别排在一列，排在一列，每个类别每个类别占一行。占一行。可放多个可放多个变量。变量。在页字段中，放入分类变量，选择某一分类，产生一在页字段中，放入分类变量，选择某一分类，产生一个数据透视表，选择另一分类，产生新的透视表，并个数据透视表，选择另一分类，产生新的透视表，并覆盖前次表格，相当于统计上的分层。选择覆盖前次表格，相当于统计上的分层。选择“全部全部”时，时，则忽略该变量的作用。则忽略该变量的作用。5050在透视表中添加或删除字段添加字段步骤 单击数据透视表中任一单元格，从“数据”菜单中选中“数据透视表” 拖曳“列标”钮至“数据”框中 单击确定按钮删除字段步骤 单击数据透视表中任一单元格，从“数据”菜单中选中“数据透视表” 从“数据”框中将某“列标”拖曳出来 单击确定按钮也可以利用工具栏5151数据透视表的特点 透视性：可以对各字段进行设置，从多角度分析数据，还可以改变汇总方式； “只读”性：可以修饰、制作图表，在源数据中更改了某个数据，必须通过“更新数据”，才能达到修改的目的。5252筛选与排序操作筛选操作详解排序操作详解5353筛选操作详解筛选操作详解 利用“数据”菜单中的“筛选”命令可对清单中指定的数据进行查找和完成其他工作。 在“筛选”命令子菜单中，Excel同时提供了“自动筛选”和“高级筛选”两种命令来筛选用户的数据。“自动筛选”可满足大部分需求，但当需要更复杂的条件来筛选清单时，则可用“高级筛选器”。在取消了“自动筛选”命令后，则会显示所有的数据。 5454排序操作详解排序操作详解 以当前数据清单中的一个或几个变量为关键字，对整个数据清单进行排序。 排序方式：升序、降序 数字型变量按数值大小； 字符型变量按ASCII码大小； 日期型变量按日期先后； 中文字按拼音或笔画。5555单个关键字排序单个关键字排序1、用鼠标单击一个排序关键字；2、单击常用工具栏中的升序（A-Z）按纽或降序（Z-A）按纽。那么整个数据清单就按指定关键字进行排序5656多个关键字排序多个关键字排序1、选定数据清单中的任意单元格为当前单元格；2、单击菜单中的“数据排序”命令，弹出排序对话框；3、在“排序”对话框中指定关键字：主要、次要、第三，选择排序方式，最后按“确定”按纽。5757链接与超级链接链接与超级链接概念与区别 操作详解5858概念与区别概念与区别概念：在Excel中 对某一对象的引用操作，通常被称为链接。 对某一对象的跳转指向操作，通常被称为超级链接。区别： 最直观的区别：链接：地址可见在excel编辑栏中；超级链接：鼠标指向进行操作的对象时，地址在鼠标处显示； 操作的区别：链接可在编辑栏中编辑；超级链接可以点击右键进行操作；5959操作详解之链接操作详解之链接(一一)在Excel中公式对其他地址的引用分为三种情况：相对引用：即对另一单元的地址引用是按与当前单元的特定距来确定的，这种引用在该公式被复制时会自动调整绝对引用：即对被引用单元采用绝对的行列号来引用，公式不会自动调整混合引用：行或列之一采用绝对引用如果有区域或单元格命名存在，还可以按名称引用6060操作详解之链接操作详解之链接(二二)在Excel中输入公式需要进行地址引用时可以采用的操作方式为：直接输入式：直接输入被引用对象的行列地址交互指定式：即用鼠标或光标键直接交互式指出被引用对象的地址名称粘贴式：如果引用的对象已有命名，可以采用F3键来粘贴该对象的名称于公式之中6161操作详解之链接操作详解之链接(三三) 在Excel中输入公式时，相对地址与绝对地址可以进行转换操作： 凡行或列地址前出现有“$”标志的，该行或列的地址即为绝对地址引用，否则为相对地址引用 在地址输入时，相对地址和绝对地址间可以用F4键进行循环转换6262操作详解之超级链接操作详解之超级链接6363结束语 Excel功能强、使用范围广，若要灵活掌握、应用，需要平时多学习、多练习、多动脑！64646565演讲完毕，谢谢观看！