EXCEL在日常工作中的应用.ppt

EXCEL在日常工作中的应用第一章EXCEL文件管理新建文件工作表的隐藏文件的安全与保护快速打印指定表格第二章单元格编辑录入相同的内容下拉列表录入录入内容的限制限制数字格式或大小文本长度单\多条件限定限制重复输入录入区域的限制第二章单元格编辑选取使用定位选取选择性粘贴粘贴数值粘贴运算隐藏查找第三章条件格式3.1条件格式的设立、添加设立条件格式添加条件3.2定义条件单元格数值条件公式条件3.3条件格式实例应用3.4小结第三章条件格式3.1条件格式的设立、添加设立条件格式添加条件3.2定义条件单元格数值条件公式条件3.3条件格式实例应用3.4小结第三章条件格式3.1条件格式的设立、添加设立条件格式操作步骤：选中区域[格式][条件格式]输入条件选择格式添加条件在[条件设置]对话框中，单击添加按钮注：条件格式最多可以设置三个第三章条件格式3.2定义条件[单元格数值]：用于简单的数值对比[公式]：用于设置较为复杂的单元格内容3.2.1单元格数值条件3.2.2公式条件第三章条件格式3.2定义条件[单元格数值]：用于简单的数值对比[公式]：用于设置较为复杂的单元格内容3.2.1单元格数值条件3.2.2公式条件第三章条件格式3.3条件格式实例应用3.3.1工龄分析的颜色提示3.2.2应收账款催款提醒3.2.3合同到期提醒监视重复录入格式化账簿代码录入的错误显示动态显示销售额排行隐藏公式中的错误值第三章条件格式3.3条件格式实例应用3.3.5代码录入的错误显示条件:1.代码位数不等于五位2.代码位数不等于八位公式:=AND(LEN($B2)5,LEN($B2)8,$B20)第三章条件格式3.3条件格式实例应用3.3.6动态显示销售额排行条件:突出显示前N名商品的销售额公式:=$D2=LARGE($D$2:$D$10,5)最大值函数,MAX求出一个最大值,LARGE可以求第N个最大值.第三章条件格式3.3条件格式实例应用3.3.7隐藏公式中错误值条件:把所有错误值隐藏公式:=ISERROR(D2)判断值是否为任意错误值（#N/A，VALUE！……）第三章条件格式3.4小结本章对条件的创建、条件的设置作了详细介绍，同时也列举了大量应用实例。读者从实例中不难看出，如果想用好条件格式，掌握公式及函数的使用是非常重要的。习题：1、如何设置公式条件2、如何突出显示重复录入内容？3、如何突出显示一列数据中最大前三个数字？4、如何添加和删除条件格式？第四章数据表和图表4.1排序4.1.1数据表排序4.1.2隔行插入空行4.2分列4.2.1拆分整列为多列4.2.2长文本型数字的导入4.2.3转化字符为日期格式4.3自动筛选4.3.1自动筛选的实现4.3.2一次删除所有重复记录第四章数据表和图表4.3自动筛选4.3.1自动筛选的实现4.3.2一次删除所有重复记录添加一辅助列,输入公式:=IF(COUNTIF($D2:D2,D2)1,1,2)这儿一定要注意理解绝对引用和相对引用的用法!第一个:=IF(COUNTIF($D$2:D2,D2)1,1,2)第二个:=IF(COUNTIF($D$2:D5,D5)1,1,2)第四章数据表和图表4.4高级筛选高级筛选功能灵活性强,和自动筛选相比有如下特点:可以把筛选结果复制到其他位置;需要设置条件区域,而且可以使用更多条件;可筛选不重复记录;4.4.1筛选符合条件的记录1.输入条件区域规则:(1)标题行和源区域一样(2)同行不同列的条件是并列关系;(3)同列不同行的条件是或者关系第四章数据表和图表2.设置筛选项目.复制标题行到要显示筛选结果的第一行.复制和手工输入有什么区别?3.数据筛选高级筛选数据源区域设置条件的区域第四章数据表和图表4.4.2筛选本列不重复记录4.4.3筛选两区域重复记录4.4.4筛选两表中不重复记录=COUNTIF($D$16:$D$24,D3)=0第四章数据表和图表4.5数据透视表数据透视表是一种对数据清单快速建立汇总的动态总结报告,它可以随时调换行列的位置而进行不同形式的汇总,是Excel提供的一个极为有效的汇总工具。数据透视表在销售数据汇总、出入库汇总及明细账汇总等方面有着广泛应用一个普通的数据表日期地区姓名产品名称销量销售单价销售金额2004-1-1中南区赵志B产品10024002400002004-1-10西北区杨东林B产品252400600002004-1-15华南区杨磊D产品451200540002004-2-1中南区孙之B产品6524001560002004-2-5中南区赵志A产品101200120002004-2-9西北区杨东林D产品131200156002004-2-13华南区杨磊E产品244100984002004-2-17中南区孙之B产品312400744002004-2-21中南区赵志D产品121200144002004-2-25西北区杨东林A产品421200504002004-2-29华南区杨磊B产品102400240002004-3-4中南区孙之D产品91200108002004-3-8中南区赵志A产品401200480002004-3-12西北区杨东林E产品324100131200你的工作表含有大量数据，但是你知道这些数字的含义吗？这些数据能够解答您的问题吗？不普通的数据透视表数据透视表提供了一种快速且强大的方式来分析数值数据、以不同的方式查看相同的数据以及回答有关这些数据的问题。第四章数据表和图表4.5数据透视表4.5.1创建数据透视表三步曲之一：确定报表类型第四章数据表和图表4.5数据透视表4.5.1创建数据透视表三步曲之二：确定数据源第四章数据表和图表4.5数据透视表4.5.1创建数据透视表三步曲之三：布局第四章数据表和图表4.5数据透视表4.5.1创建数据透视表三步曲之三：确定显示位置第四章数据表和图表4.5.5固定数据透视表格式4.5.1创建数据透视表4.5.2调整数据透视表格式4.5.3在数据透视表中设置公式4.5.4其他编辑调整汇总方式显示或隐藏汇总行数据透视表的更新和自动更新第四章数据表和图表4.5.7多个数据透视表合并第四章数据表和图表4.5.7多个数据透视表合并第四章数据表和图表4.6图表4.6.1创建图表插入图表四步曲设置图表类型设置数据源设置图表选项设置图表位置第四章数据表和图表-----四步曲第四章数据表和图表4.6.2双坐标图表0501001502002503003504004505001月2月3月4月5月6月7月8月01000000020000000300000004000000050000000600000007000000080000000库存量系列3库存金额库存量01002003004005006001月2月3月4月5月6月7月库存量0501001502002503003504004505001月2月3月4月5月6月7月8月01000000020000000300000004000000050000000600000007000000080000000库存量库存金额单坐标图表常规设置的双坐标图表双坐标图表第五章公式与函数公式与函数是Excel的精华所在,它为分析和处理数据提供了方便,特别是在处理大批量数据和进行复杂数据分析等方面更能发挥它的强大功能。本章在介绍函数的用法时，着重介绍它们在财务工作中的应用。本章要点：IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的用法IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的实例应用第五章公式与函数公式与函数是Excel的精华所在,它为分析和处理数据提供了方便,特别是在处理大批量数据和进行复杂数据分析等方面更能发挥它的强大功能。本章在介绍函数的用法时，着重介绍它们在财务工作中的应用。本章要点：IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的用法IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的实例应用第五章公式与函数5.1IF函数IF函数是工作中最常用函数之一，它可以根据设置的条件进行运算或返回值。语法：=IF（逻辑表达式，TRUE，FALSE）逻辑表达式不成立返回的值逻辑表达式成立返回的值返回值为TRUE或FALSE的逻辑表达式例:=IF(53,对,不对)例:=IF(53,不对,对)第五章公式与函数5.1IF函数逻辑表达式不成立返回的值逻辑表达式成立返回的值返回值为TRUE或FALSE的逻辑表达式打开EXCEL第五章公式与函数5.1.1单条件和多条件判断1.单条件返回文本IF函数实例（P113）逻辑表达式不成立返回的值逻辑表达式成立返回的值逻辑表达条件：比较实际数和计划数的大小D2=IF（C2B2,节约，超支）第五章公式与函数5.1.1单条件和多条件判断2.单条件判断并运算IF函数实例（P113）逻辑表达式不成立进行运算的表达式逻辑表达式成立时进行运算的表达式逻辑表达条件：销售额是否超过3万元C2=IF（B230000,B2*0.015,B2*0.01）第五章公式与函数5.1.1单条件和多条件判断3.单条件判断返回引用区域IF函数实例（P113）逻辑表达式不成立返回的区域逻辑表达式成立时返回的区域逻辑表达条件：A2是否等于销售一部=SUM(IF(A2=销售一部,B5:B9,E5:E9))第五章公式与函数5.1.1单条件和多条件判断4.多条件判断IF函数实例逻辑表达式不成立返回的表达式逻辑表达式成立时返回的值逻辑表达条件：B2或C2任一为0是否成立=IF(OR(B2=0,C2=0),,(C2-B2)/C2)第五章公式与函数5.1.1单条件和多条件判断4.多条件判断IF函数实例=IF(B220000,B2*1%,IF(B225000,B2*2%,IF(B235000,B2*3%,B2*4%)))=IF(B220000,B2*1%,IF(20000=B225000,B2*2%,IF(25000=B235000,B2*3%,B2*4%)))常见的错误常见的错误第五章公式与函数5.1.1单条件和多条件判断4.多条件判断IF函数实例（P113）=IF(B210000,B2*1%,0)+IF(AND(B220000,B2=10000),B2*2%,0)+IF(AND(B230000,B2=20000),B2*3%,0)+IF(AND(B240000,B2=30000),B2*4%,0)+IF(AND(B250000,B2=40000),B2*5%,0)+IF(AND(B260000,B2=50000),B2*6%,0)+IF(AND(B270000,B2=60000),B2*7%,0)+IF(AND(B280000,B2=70000),B2*8%,0)+IF(B280000,B2*9%,0)第五章公式与函数课堂练习：成绩表小结：这节课讲了IF函数的使用，有以下四种情况：单条件返回文本单条件进行运算单条件返回区域多条件判断IF函数在实际工作中应用很广，要注意不同函数中参数的含义。第五章公式与函数5.2SUM函数SUM函数是工作中最常用函数之一，几乎所有的表格中都有合并的运算。语法：=SUM（参数1，参数2，……参数30）参数最多为30个参数可以为引用，数值，文本，表达式和数组例:=SUM(5,3,2,1)例:=(a1:b1)第五章公式与函数5.2.1连续、不连续及交叉区域求和例1：连续区域的求和=SUM(A