Excel：数据筛选排序公式函数的简单入门.ppt

Excel：数据筛选、排序、公式、函数的简单入门前言：版本之争1上篇：数据筛选、排序与制表规范2下篇：公式与函数3后记：上网求助4版本之争Excel 2003：手动挡轿车：手动挡轿车Excel 2007、2010：自动挡轿车：自动挡轿车版本之争 Excel 2003的现状：的现状：用户依然占大多数用户依然占大多数2007版或版或2010版的某些功能的确强版的某些功能的确强过过2003版，但基础功能却大同小异，甚至版，但基础功能却大同小异，甚至没有变化。没有变化。本报告立足基础，示例用本报告立足基础，示例用Excel 2003版，但尽量不涉及版本功能。版，但尽量不涉及版本功能。数据筛选、排序与制表规范引例下表是来访表的一部分，有问题吗下表是来访表的一部分，有问题吗第一宗罪日期录入不规范日期该如何录入？格式统一就行吗？日期该如何录入？格式统一就行吗？日期常见的错误录入方式“点点”型型以以“.”为分隔符录入的为分隔符录入的日期。日期。比如：2011.1.10；13.05.08。“空格空格”型型以以“”为分隔符录入的为分隔符录入的日期。日期。比如：2011 1 10；13 05 08。日期常见的正确录入方式“减号减号”型型以以“-”为分隔符录入为分隔符录入的日期。的日期。比如：2011-1-10；2013-5-8。“正斜杠正斜杠”型型以以“/”为分隔符录入为分隔符录入的日期。的日期。比如：2011/1/10；2013/5/8。示例检验日期是否正确方法：将单元格格式设置为方法：将单元格格式设置为“常规常规”，如果日期变为一组数字，就正确。，如果日期变为一组数字，就正确。日期的本质Excel默认的起始日期为默认的起始日期为1900-1-1，代表第一天，用数字，代表第一天，用数字1表示。之后每表示。之后每增加一天，数字就增加增加一天，数字就增加1。2011-1-10距离1900-1-1有40552天，40552+1=40553，因此40553就代表了2011-1-10。两个日期可以做减法，相减的结果两个日期可以做减法，相减的结果为它们相隔的天数。为它们相隔的天数。示例小技巧快捷键输入当前日期示例整改方案日期按正确的方式录入。日期按正确的方式录入。整改过程整改过程整改结果整改结果示例第二宗罪合并单元格严重破坏了数据结构合并单元格很工整，有什么问题吗？合并单元格很工整，有什么问题吗？往2行表头的危害问题1：无法使用的排序合并单元格会造成整个数据区域的合并单元格会造成整个数据区域的单元格大小不一。所以排序时，会提单元格大小不一。所以排序时，会提示错误。示错误。示例问题2：筛选会出错示例为什么会出错是因为合并单元格以后，只有首个单元是因为合并单元格以后，只有首个单元格有数据，其他的都是空白单元格。格有数据，其他的都是空白单元格。如右图，看起来F5：F6的数据内容是“*”。但其实只有F5有数据，F6对于Excel来说，值为空。所以，筛选所有售价为空白的数据明细，能得到三条记录。小技巧“合并及居中”何时使用？主要是用于要打印的表格，可以使主要是用于要打印的表格，可以使表格看上去更加明晰。表格看上去更加明晰。整改方案取消取消“合并及居中合并及居中”，录入数据在，录入数据在一行内完成。一行内完成。整改过程整改过程整改结果整改结果示例第三宗罪数据残缺有空白单元格没数据就不填写，不行吗？单元格没数据就不填写，不行吗？真空白VS假空白单元格有真空白和假空白之分。单元格有真空白和假空白之分。真空白是这个单元格真空白是这个单元格“真没有真没有”数据：数据：该单元格从未被填写过该单元格从未被填写过曾经填写的数据已经被完全删除曾经填写的数据已经被完全删除真空白会影响数据分析结果，尤其是做计数统计。真空白VS假空白 假空白是这个单元格假空白是这个单元格“可以有可以有”数据。数据。假空白的单元格仅用肉眼看，格假空白的单元格仅用肉眼看，格内什么都没有。但是选中它再看编辑内什么都没有。但是选中它再看编辑栏，却会发现编辑栏里有数据，内容栏，却会发现编辑栏里有数据，内容为为=“”（英文的双引号）。（英文的双引号）。这是一种空文本的概念。这是一种空文本的概念。示例空文本与0值空文本在空文本在Excel看来也是数据。看来也是数据。而在我们眼中，它是空白。这和这和0值一样。值一样。当单元格数值为0时，Excel也认可该单元格里有数据，只不过数值为0罢了。即：不管什么数值，只要有值就是即：不管什么数值，只要有值就是“非空单元格非空单元格”。最严谨的数据记录方式在数值部分的空白单元格里填写0值值在文本部分的空白单元格里填写相应的文本相应的文本数据，数据，“无无”或或“空空”等等整改方案空白单元格都是文本性质，均填写空白单元格都是文本性质，均填写“无无”。方案一：逐个单元格录入方案二：成片的复制、粘贴方案三：单元格一键批量录入整改过程整改过程示例整改结果整改结果小技巧单元格一键批量录入目的：在已经选定的多个单元格中目的：在已经选定的多个单元格中录入相同的数据。录入相同的数据。方法：方法：Ctrl+Enter。原理：Ctrl键有复制的功能；Enter代表单元格录入完毕。把它们组合起来，即：将录入完毕的数据同时复制到所有选定单元格。小技巧单元格一键批量录入操作很简单，仅需三步。操作很简单，仅需三步。第一步：选定多个单元格。第一步：选定多个单元格。小技巧单元格一键批量录入 第二步：什么都不要动，直接敲键第二步：什么都不要动，直接敲键盘输入内容。盘输入内容。小技巧单元格一键批量录入 第三步：压住第三步：压住Ctrl同时按同时按Enter，神，神奇效果马上显现！奇效果马上显现！回整改方案整改结果整改结果整改结果第四宗罪多余的表头该表头占了该表头占了2行，有什么问题吗？行，有什么问题吗？2行表头的实质第2行（表头第一行）只有两项内容：“性别”与“询问重点”它们对Excel识别某列数据的属性没有任何帮助实际上仅仅是文字说明！实际上仅仅是文字说明！而其他部分，则用了而其他部分，则用了合并单元格合并单元格2行表头的危害奇葩的筛选！奇葩的筛选！示例整改方案取消所有的合并单元格。如果文取消所有的合并单元格。如果文字说明必须保留，同时为了美观，可字说明必须保留，同时为了美观，可设置设置“跨列居中跨列居中”。整改过程整改过程示例整改结果整改结果小技巧单元格格式之跨列居中方法：在首个单元格输入标题，然方法：在首个单元格输入标题，然后选中区域，调出单元格格式设置，后选中区域，调出单元格格式设置，再切换到再切换到“对齐对齐”标签，将标签，将“水平对水平对齐齐”设置为设置为“跨列居中跨列居中”。小技巧单元格格式之跨列居中示例回整改方案整改结果整改结果整改结果制表规范正确的数据表应满足以下条件：正确的数据表应满足以下条件：一个标题行；一个标题行；无合并单元格；无合并单元格；数据录入统一、规范；数据录入统一、规范；数据区域中无空白单元格。数据区域中无空白单元格。数据筛选要求：筛选出要求：筛选出A置业顾问的所有来置业顾问的所有来访客户。访客户。示例数据排序要求：按能承受的总价，由高到低要求：按能承受的总价，由高到低对来访客户排序。对来访客户排序。示例数据排序要求：按能承受的总价，由高到低要求：按能承受的总价，由高到低对来访客户排序。对来访客户排序。公式与函数公式的概念和原则公式：指对工作表中的数值执行计公式：指对工作表中的数值执行计算和操作的等式。算和操作的等式。公式必须遵循以下原则：以等号“=”作为开始。等号“=”之后紧接运算数和运算符。公式由一系列数据和运算符组成。公式由一系列数据和运算符组成。公式运算数运算数即参加运算的参数，可以是运算数即参加运算的参数，可以是数值常量、单元格引用或函数等。数值常量、单元格引用或函数等。常量：直接输入的数字或文本，是不用计算的值，如89.5，”#楼”等。单元格引用：引用某一单元格或单元格区域中的数据。函数：包括函数及它们的参数。单元格引用分为两种：引用某个单元格分为两种：引用某个单元格A1；引用由一组单元格组成的数据区域引用由一组单元格组成的数据区域A1:B5。A1:B5是指以A1为左上角，B5为右下角的单元格集合去往文本连接运算符表格的智慧你是否遇到过，无论删除行或列还是插入行或列，Excel都能准确无误的计算，似乎表格的变动早在它的意料之中。这归功于单元格引用的相对性。这归功于单元格引用的相对性。相对引用和绝对引用根据单元格引用的相对性，可分为相对根据单元格引用的相对性，可分为相对引用引用(A1)、行绝对引用、行绝对引用(A$1)、列绝对引用、列绝对引用($A1)和绝对引用和绝对引用($A$1)四种情况。四种情况。它们之间的差别在于是否有它们之间的差别在于是否有“$”符号，以及符号，以及“$”符号在哪里。符号在哪里。“$”符号的意义可以理解为可以理解为“锁定锁定”，即在公式复，即在公式复制的过程中，不允许引用随公式所在制的过程中，不允许引用随公式所在位置的变化而变化。位置的变化而变化。小技巧一键切换引用类型示例相对引用复制或填充公式时，根据目标位置复制或填充公式时，根据目标位置自动调节公式中的相对地址。自动调节公式中的相对地址。向右向下复制示例绝对引用复制或填充公式时，不改变公式中复制或填充公式时，不改变公式中的绝对地址。的绝对地址。向右向下复制示例行绝对引用复制或填充公式时，只改变公式中复制或填充公式时，只改变公式中的列地址，不改变行地址。的列地址，不改变行地址。向右向下复制示例列绝对引用复制或填充公式时，只改变公式中复制或填充公式时，只改变公式中的行地址，不改变列地址。的行地址，不改变列地址。向右向下复制示例公式运算符是连接公式中的基本元素并完成特是连接公式中的基本元素并完成特定计算的符号。定计算的符号。例如：=A1+A2*10000运算符分为四类：算术运算符比较运算符引用运算符文本连接运算符算术运算符用于简单的数学计算。用于简单的数学计算。常用：+加法 -减法 *乘法 /除法不常用：几次方%百分比 -负数注意：注意：=A1+A2，A1、A2分别指的是分别指的是A1、A2单单元格中的数据。元格中的数据。示例比较运算符用于比较两个数值的大小，并产生用于比较两个数值的大小，并产生逻辑值逻辑值TRUE(真真)或或FALSE(假假)。例如：=A1 A2 比较运算符包括等于“=”、大于“”、小于“=”、小于等于“=”、不等于“”六种示例引用运算符用来引用单元格区域。用来引用单元格区域。最常用的就是：区域运算符：（冒号）表示引用相邻的单元格区域。例如A1：B5文本连接运算符&，用于连接文本字符串。，用于连接文本字符串。例如：=C3&D3注意：文本常量需要参注意：文本常量需要参与公式运算，必须将其与公式运算，必须将其包含在半角双引号之间，包含在半角双引号之间，否则会报错。否则会报错。文本连接运算符示例函数的概念与结构函数是在公式中使用的一种内部工函数是在公式中使用的一种内部工具，是预先编写好的，按特定的顺序具，是预先编写好的，按特定的顺序或结构执行计算的特殊公式。或结构执行计算的特殊公式。函数的一般结构是：函数名称（参数1，参数2，）参数是函数的输入值，可以是常量、单元格引用、逻辑值或其他的函数等。函数公式的结构=IF (A10,SUM (B1:BG),0)函数结构嵌套函数函数名称参数函数中的括号与逗号都应该是半角符函数中的括号与逗号都应该是半角符号，即英文输入法下的括号与逗号。号，即英文输入法下的括号与逗号。函数的输入对话框方法一：使用方法一：使用“插入函数插入函数”、“函函数参数数参数”对话框。对话框。函数的输入对话框方法一：使用方法一：使用“插入函数插入函数”、“函函数参数数参数”对话框。对话框。函数的输入对话框方法一：使用方法一：使用“插入函数插入函数”、“函函数参数数参数”对话框。对话框。示例函数的输入手工输入方法二：直接在单元格中依次输入。方法二：直接在单元格中依次输入。正在编辑的参数会正在编辑的参数会加粗显示加粗显示函数的输入手工输入方法二：直接在单元格中依次输入。方法二：直接在单元格中依次输入。示例函数示例按指定数位四舍五入使用使用ROUND函数按指定位数对数值进函数按指定位数对数值进行四舍五入。行四舍五入。语法为：ROUND(number,num_digits)参数number是需要进行四舍五入的数字；num_digits为指定的位数。如果参数 num_digits 大于 0，则四舍五入到指定的小数位；如果等于 0，则四舍五入到最接近的整数；如果小于 0，则四舍五入到小数点左侧指定位数。去往尾页函数示例按指定数位四舍五入例如对数值12.13、3.75、-3.75、-12.13四舍五入。示例应用应用房款=房屋单价*建筑面积。房款在合同中是需要四舍五入取整的。示例上网求助上网求助网络时代的好处：遇到疑难问题，可以在网上搜索到各种答案。上网求助的关键在于能提出合适的上网求助的关键在于能提出合适的问题，以及甄别有效的信息。问题，以及甄别有效的信息。提问的时候，多用关键字，避免写提问的时候，多用关键字，避免写很长的描述，关键字之间要用空格隔很长的描述，关键字之间要用空格隔开。开。上网求助示例问题：想知道使用什么函数能实现四舍五入的功能。解决方法：在搜索网站输入查找信解决方法：在搜索网站输入查找信息息“excel 四舍五入四舍五入 函数函数”，就会得，就会得到许多相关的答案。然后从众多答案到许多相关的答案。然后从众多答案中找到自己觉得最靠谱的，并加以实中找到自己觉得最靠谱的，并加以实验。验。上网求助示例上网求助示例去函数示例