Excel培训(2)

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Excel,日常应用略解,-,基于,Excel2003,版的使用探讨,1,如何学习,EXCEL,并成为高手,使用,EXCEL,帮助系统,在,BBS（,论坛）上进行交流,News:/,在搜索引擎中寻找学习资源和解题方法,阅读相关书籍,Excel,数据处理与分析实战技巧精粹,2,Excel,是什么？,如果我们问：,“,Excel,是用来做什么的？,”,我想，很多人都会下意识地回答：,“,Excel,是用来做表格的。,”,另外一些人可能会说：,“,Excel,是一个电子表格处理软件，他有强大的数据,处理和数据分析能力，能够把数据做成图表，还可以编程。,”,那么请问，你们更赞同那种说法呢？毫无疑问，是后者。为什么，因为后,者字数多些。,如果是我，我两者都不赞同，看到这个问题时，我会反问：,Excel,不能用,来做什么？这是我的答案。,3,Excel,究竟有哪些功能呢？概括起来说，他主要包括：,1),、数据的记录与整理；,2),、数据计算；,3),、数据分析；,4),、图表制作；,5),、数据传递与共享；,6),、,Excel,功能和用途的定制。,事实上，你对一个事物了解越多，就越觉得回答与之相关,的问题并不是一件简单的事。对于,Excel,而言，它总能在适当的时候给你带来惊喜。使用它，你总能在纠结的时候柳暗花明：,4,一、基本的工作表操作,二、认识行、列及单元格区域,三、输入、编辑和整理表格数据,四、格式化工作表,五、自定义工具栏和菜单,六、其他一些使用技巧,Excel,基本操作,5,一、,工作表的操作技巧主要包括,:,1.1,工作表的创建,工作表的创建由两种情况：,第一种是随工作表而创建 ，即打开的时候就有工作表，可以设置打开的工作表的数量；,工作表（,WorkSheet,）和工作簿（,WorkBook,）的认识，这对以后的编程有帮助。,6,一、,工作表的操作技巧主要包括,:,1.1,工作表的创建,第二种是在原有的工作薄中新增工作表，可批量新增、单个新增。,新增工作表的集中方法：,1,、右键,-“,插入”,2,、运用快捷键“,Shift+F11”,3,、如果需要批量插入多张工作表，可以在第一次插入工作表之后，按,F4,，每按一次即可新增一张工作表。,大多数情况下，没有保留太多工作表的必要，空白的工作表反而会增加文件的体积，,造成不必要的容量浪费。所以，必要时，应尽可能减少空白工作表的数量。,7,一、,工作表的操作技巧主要包括,:,1.2,设置当前工作表,当当前工作簿中的工作表很多，在工作表标签栏无法完全显示时，可以利用一些办来实现方便选择：,1,、扩大标签显示栏的长度。如图：,2,、利用标签显示栏上的,微调按钮,3,、在标签显示栏的微调按钮区点击右键，选择工作表,8,一、,工作表的操作技巧主要包括,:,1.3,同时选定多张工作表，运用,Ctrl+,工作表；,1.4,工作表的复制与移动。比如一个多工作表的工作簿中，有一个工作表需要复制或者移动出来，一般运用什么办法呢？复制，然后建个新的工作表，再粘贴？其实可以用到“复制或移动工作表”。选择某一工作表，右键，选择“复制或移动工作表”,1,、选择“建立副本”，则是复制，原表依然在原工作簿中；,否则为移动，即移出。,2,、选择“新工作簿”，则移动或复制的表会新建一个工作簿；,选择已有的工作薄，则会将表移动或复制到已有的工作薄中，,并可在列举的工作表中选择是否“移至最后”,9,一、,工作表的操作技巧主要包括,:,1.5,拖动工作表；,1.6,重命名工作表；,1.7,删除工作表；,1.8,隐藏与显示工作表。对于某些应用或者基于安全方面的考虑，可以对工作表进行隐藏。选定工作表以后，选择菜单“格式”,-“,工作表”,-“,隐藏”，即可将工作表隐藏。需要显示时，则选择“格式”,-“,工作表”,-“,取消隐藏”，会弹出取消隐藏对话框，选择需要取消隐藏的工作表即可。,10,一、,工作表的操作技巧主要包括,:,工作薄的多窗口显示；,Excel,中提供了各种窗口排列管理，包括重排窗口、并排比较、拆分窗口、冻结窗口和窗口缩放等。这些窗口操作在日常工作中运用得当的话，可以给工作带来很大的便利。尤其是并排比较和窗口冻结。,1,、并排比较。有些时候，用户需要在两个工作窗口中比较数据，则可以运用“并排比较”的功能，单击菜单“窗口”,-“,并排比较”，可以使两个工作窗口并排对比，两个窗口可以相对滚动。,2,、对于比较复杂的大型表格，有时候我们需要在滚动浏览时始终显示某些行或者列，比如表格结构的表头行或者表头列，这时就可以利用冻结窗口。,选择“窗口”,-,冻结窗格”，则可以冻结当前鼠标点击的单元格位置的上一行和左一列形成的区域。,11,工作簿、工作表、工作区域、单元格的保护；,工作薄的保护，工作薄保护中的结构、窗口保护，工作薄保护与工作薄,打开密码设定的区别。（前者仅对工作区域，后者对打开和编辑）,工作表的保护，禁止操作,工作表、单元格的权限设置，允许在权限范围内编辑,单元格的保护，禁止编辑,单元格的不同区域以不同的权限编辑,此处的工作薄、工作表的保护，不同于文件的打开权限,一、,工作表的操作技巧主要包括,:,12,二、,认识行、列及单元格区域,:,1,、行与列的概念,Excel,中，共有最大行数,65,536,行，最大列数,256,列。如果数据行列超过这两个数字，则一个,Excel,表格无法存放。（仅限,2003,版本以下，,2007,版本以上已经大大提升了数据存储区域。）,Excel,中的行列操作包括：选择、设置、插入、移动和复制、删除、隐藏和显示。这些都是,Excel,中最基本的应用。,2,、单元格与区域,单元格为行列组合形成的一个个的格子；区域是单元格的延伸，是多个单元格组成的群组。区域包括连续区域和不连续区域。,区域可以自定义名称，自定义名称的区域在函数和其他高级应用中经常使用。,行与列是,Excel,的最基本单元，准确把握行与列，是运用函数、公式以及各种高级应用的基础。,13,二、,认识行、列及单元格区域,:,2.1,单元格的选取与定位,1,）基本选取，单击；,2,）多项选取，运用,Ctrl,键,+,单击；,3,）在非空区域中连续选取：,Shift+END/Home+,上下左右键；,4,）特殊选取：定位（,Ctrl+G,）,定位功能有多种用途，其一为一般定位，即定位到某行，某列，格式为在引用位置处,输入需要到达的位置（以单元格引用为表达式）,14,二、,认识行、列及单元格区域,:,2.1,特殊选择：定位的运用,除了运用“定位”进行快速到达之外，还可以利用定位下的“定位条件”实现特殊的选取。,使用方法为：菜单“编辑”,-“,定位”,-“,定位条件”,定位条件下选择的含义以及选择运用：,一般运用：批注、常量、行内容差异单元格、列内容差异单元格等（此处需要运用,Excel,现场操作）,15,二、,认识行、列及单元格区域,:,2.1,特殊选择：定位的运用,需要特别注意的是，运用“行内容差异单元格”与“列内容差异单元格”的时候，要注意,选取的数据的位置和顺序，选取的位置和顺序，决定定位数据的正确与否。,如下图所示，要选取绿色区域中不等于,0,的数据，则必须注意选取参照列的顺序。,如果选取的数据顺序不对，那么定位的结果是无法预料的。详见附件,16,二、,认识行、列及单元格区域,:,定位条件的含义：,17,2.2,选择性粘贴的使用；,选择性粘贴可以实现：,仅粘贴数值、公式或者格式等；,可在粘贴时实现加减乘除运算,二、,认识行、列及单元格区域,:,18,2.3,复制、粘贴及神奇的,ALT+10,的使用；,二、,认识行、列及单元格区域,:,我们在使用复制粘贴时，除了上面讲到的选择性粘贴之外，还有一种情况，,就是所见即所得的粘贴。也就是说，复制的是什么格式，粘贴的就是什么格式。,那么，假如现在有下图所示的数据,我们需要把这些数据粘贴成如右图所示，那么，我,们就要找些窍门，其中，使用,ALT+10,，将是实现这种,数据转换的最佳利器。,19,二、,认识行、列及单元格区域,:,2.4,单元格数据格式：自定义格式的妙用,单元格数据中的格式设定灵活多样，我们可以运用快捷键,Ctrl+1,进行设置。除了这些,常规设置之外，还有一些比较特殊的形式。比如，我们希望当数据为,1,是显示为“男”，数,据为,0,时显示为“女”（,If,可以做到，但不可运算）,20,二、,认识行、列及单元格区域,:,2.4,单元格数据格式：自定义格式的妙用,自定义格式的格式代码规格。自定义的格式具有如下结构：,正数；负数；零值；文本,分号将格式代码分成四段，每一段对应一种数据表现。比如，自定义格式设置为：,;,则不显示所有数据；如果设成,0;0,，则不显示负数，如果定义为,;-#;,，则只显示负数。,同样，自定义条件格式也有四个区段，具有如下结构：,条件值,1,；条件值,2,；同时不满足条件,1,和条件,2,；文本,比如：,=1“”;=0“”;,其意义为：当数据等于,1,时候，显示为勾，当数据等于,0,时，,显示为叉，当数据不等于,1,也不等于,0,时，不显示。如果我们再设置,=1“”;=0“”; ”,无“,那么当数据不等于,1,也不等于,0,时，显示为”无“。,当然，我们平时使用时，也不需要完全使用,4,段代码，可以用,1-4,段之间的任一代码段，,其意义有不同的含义。,21,二、,认识行、列及单元格区域,:,2.4,单元格数据格式：自定义格式的妙用,在实际运用过程中，不必每次都按照四个区域结构来编写格式代码，甚至,1,个区域段,都是允许的，那么，不同的区域段将产生不同的效果。,区段数,代码含义,1,代码格式作用于所有数据,2,第一区段作用于正数和零，第二区段作用于负数,3,第一区段作用于正数，第二区段作用于负数，第三区段作用于零,而对于包含条件值的格式代码而言，区域可以少于,4,个，但不得少于,2,个。其少于,4,个区,域段的代码含义如下：,区段数,代码含义,2,第一区段满足条件一，第二区段满足其他情况,3,第一区段满足条件一，第二区段满足条件二，第三区段作用其他,22,二、,认识行、列及单元格区域,:,23,二、,认识行、列及单元格区域,:,2.5,单元格数据格式：自定义格式代码符号的含义及作用,24,三、,输入、编辑和整理表格数据,:,3.1,数据类型及其分类：,在单元格中可以输入和保存的数据分为,4,种基本类型：数值、日期、文本和公式。,除此之外，还有逻辑值、错误值等特殊数据类型。,3.2,数据输入使用技巧：,3.2.1,强制换行：在输入数据时，如果需要换行，可以使用,ALT+Enter,进行强制换行。,这种强制换行和设置格式中的换行有着本质的区别；,3.2.2,分数的输入，假如我们要输入一个数，四又三分之一，在单元格中该如何输入,呢？输入,4.3,精度不够，,4.333333,显然不太合适，那么，四又三分之一该如何输入,呢，我们可以先输入,4,，再空格，输入,1/3,，即可。,3.2.3,自动输入小数点：,需要指出的是，如果你选择了自动定义小数位数的话，假设你定义了小数位数为,2,，,那么，在输入数据,12,的情况下，将显示为,0.12,，而不是,12.00,25,三、,输入、编辑和整理表格数据,:,3.3,创建列表及列表选择：,在,Excel,中，有时候需要在各行中输入一个固定的列表值。比如，我们要在,Sheet1,表,中的第一列输入数据，这个数据全部在”华东、华南、华中、华北“四个选项中选取。,我们如果每次输入或者复制时，会觉得很麻烦，于是我们想，如果能创建一个下拉,列表，则显得专业且方便，那如何实现这种功能呢？,分两步：,第一步，创建列表，并将列表区域命名；,第二步：运用数据有效性，选择”序列“,第一步、注意区域命名,效果,第二步、在输入区选择整列，设定数据有效性,26,三、,输入、编辑和整理表格数据,:,3.4,包含通配符的查找和替换：,Excel,中，不但可以根据用户输入的查找内容精确匹配查找和替换，还可以根据一些,通配符进行模糊查找。在,Excel,查找和替换中，支持两个通配符”*“和”？“,*，星号，可以代替任何字符，包括单字符和多字符；,？，问号，可以代替任何单个字符。,比如，需要查找以”中联“开头，”公司“结尾的数据，则可输入”中联*公司“，查找的结果会出现中联电气公司、中联路面公司、中联混凝土公司等；如果要查找”中联“开头，以”气“字结尾，中间只有一个字的字符，则输入”中联？气“，查找结果包括中联电气、中联水气等。,注意：如果用户需要查找,*,本身，而不是他所替代的内容，那么，就要在查找是，在*前面加上,一个,符号，？也一样。而如果需要查找,字符，则需要输入两次,3.5 Excel,查找替换的不足：,虽然,Excel,查找替换支持这两个通配符，但他不能像,Word,一样支持更多的通配符，,如,%,等。更不能在替换中使用通配符进行同等替换。,27,三、,输入、编辑和整理表格数据,:,小练习：包含通配符的查找和替换,(,包括格式的替换,),：,替换成结果如下：,28,三、,输入、编辑和整理表格数据,:,3.5,排序、筛选数据与高级筛选：,3.5.1,基本排序和筛选,Excel,提供了数据的排序和自动筛选，通过排序和自动筛选，可以将数据清除明晰,地显示出来，从而实现高效处理。,3.5.2,运用高级筛选选取唯一值,在,Excel,中，我们还可以运用高级筛选，来对有重复值的行列进行唯一性筛选：,“数据”,-“,筛选”,-“,高级筛选”,选择“将筛选结果复制到其他位置,/,或在本列上显示,勾选”选择不重复记录“,”列表区域“：为原始数据所在列的数据,”复制到“：为需要放置不重复数据的列，选择单元格,”条件区域“：默认为空，可以设定条件值,29,三、,输入、编辑和整理表格数据,:,小题目：,现有,A,、,B,两列数据，其中,A,列数据包含了,B,列数据，,A/B,两列数据无序且不唯一。,要求：,1,、将,A,列数据中与,B,列数据相同的删除。,2,、,A,列数据中原有的次序保持不变。,此处将用到查找并删除可能的空值，可以用到定位。,在使用高级筛选的时候，要使用条件区域，需要列标题一致。详见附件,-,30,四、,格式化工作表,:,4.1,自动套用格式：,格式化工作表主要包括设置边框、文字、背景等。其中菜单中提供了一个叫做”自,动套用格式的功能。可以根据自动套用格式的格式设置制作的表格。,31,四、,格式化工作表,:,4.2,条件格式的基本运用：,Excel,中还提供了一种条件格式的功能，条件格式功能强大，综合运用引用、公式等可以,将条件格式的功能发挥到最大。这里我们先了解一些基本的内置条件格式功能。,要实现输入区域中，大于等,3,，小于等于,8,的突出显示，则使用条件格式,先将区域选中，然后单击菜单“格式”选择,“条件格式”然后再进行设置。,32,五、自定义工具栏和菜单,工具栏日常功能,按钮的重置：,选定可变单元格,33,6.1,批注的显示与隐藏；,六、其他一些使用技巧,34,A,、手动重算将导致计算结果无变化,B,、以显示精度为准将在计算时自动截取非显示部分。,6.2,自动重算与手动重算；,六、其他一些使用技巧,35,撤销上步操作：,Ctrl+Z,恢复上步操作：,Ctrl+Y,重复上步操作：,F4,剪切：,Ctrl+X,复制：,Ctrl+C,粘贴：,Ctrl+V,查找：,Ctrl+F,替换：,Ctrl+H,定位：,Ctrl+G,插入批注：,Shift+F2,打开“插入函数”：,Shift+F3,选择单元格所在整列：,Ctrl+Shift+,空格,选择多个元素：按住,Ctrl,再单击各个元素,打开“单元格格式”对话框：,Ctrl+1,向下填充：,Ctrl+D,连续选择：,Shift+Home/End+,上下左右,向前,/,向后切换工作簿：,Ctrl+PageUp/PageDown,公式文本和计算结果切换键：,Ctrl+,当前,日期,输入键：,Ctrl+;,上行内容,复制,键：,Ctrl+,自动输入,求和,公式：,Alt+=,常用快捷键操作,36,1,、用好“窗口,/,窗口冻结”和“窗口,/,拆分”命令，这样可以使同一,工作表,的相距很远的区域呈现在同一屏幕中，从而使得同一工作表间的数据交流（主要是剪切拷贝粘贴等操作）和数据参照变得十分方便和直观；,2,、用好“窗口,/,新建窗口”和“窗口,/,重排窗口”命令，这样可以使同一工作簿中的不同工作表呈现在同一屏幕中，从而使得不同工作表间的数据交流（主要是剪切拷贝粘贴等操作）和数据参照变得十分方便和直观；,3,、用好“窗口,/,重排窗口”命令，这样可以使不同工作簿中的工作表呈现在同一屏幕中，从而使得不同工作簿间的数据交流（主要是剪切拷贝粘贴等操作）和数据参照变得十分方便和直观；,4,、使用“显示比例”（自定义比例很重要）命令，缩小比例会使得工作界面尽可能处于一个屏幕之内，放大比例则会便于输入和查看。,37,5,、使用隐藏行列命令或极度缩小行高列宽，使得工作界面尽可能处于一个屏幕之内，以便于参照和录入数据。,6,、用好工作表标签快捷菜单，便于浏览和选取工作簿中所有的工作表表页；,7,、用好状态栏快捷菜单，有时“计数”和“计数值”两项是很有用处的；,8,、用好“名称”定义，这在,查找,区域、公式和编程,引用,时非常有益；,9,、用“自动,筛选,”命令批量进行列内容清除和行删除（注意：此时忌讳对筛选出的行进行大块连续区域的粘贴和进行拖动鼠标在列内的复制操作，因为筛选后的行不一定是连续的）。,10,、,Excel,除了提供按列,排序,功能外，也提供了行排序命令。,11,、,Excel,提供按自定义序列排序功能。,38,公式和函数基础,第二篇,39,大家都知道,80/20,法则，但在,Excel,中，据说存在着一个,95/5,法则。所谓的,95/5,法则，就是说,95%,的使用者，仅发挥了其中,5%,的功能。,我们前面第一篇，介绍了,Excel,的基本使用方法和技巧，其中没有涉及到任何有关公式和函数的相关知识。,通过对本篇的探讨，我们便能够较为深入地了解,Excel,实用工作函数的应用技术，并将其运用到实际的工作中去，从而发挥,Excel,在数据计算上的威力。,一些唠叨,40,一、认识公式和函数,1,、公式的概念,公式就是由用户自行设计并结合常量数据、单元格引用、运算符等元素,进行数据处理和计算的算式。,2,、公式的结构,=SUM(E1:H1)*A1+26,它要以等号“,=”,开始，其内部可以包括函数、引用、运算符和常量。,上式中的“,SUM(E1:H1)”,是函数，“,A1”,则是对单元格,A1,的引用,(,使用其中,存储的数据,),，“,26”,则是常量，“*” 和“,+”,则是算术运算符,(,另外还有,比较运算符、文本运算符和引用运算符,),。,实际上，,Excel,公式里接受,ALT+,回车的换行和空格，因此，为了增强公式的可读性我们可以使用这些方式来编辑公式。,41,一、认识公式和函数,3,、公式里的运算符,运算符是构成公式的基本要素之一，,Excel,中主要有四种运算符，分别为：,算术运算符、比较运算符、文本运算符和引用运算符。具体见下表：,符号,说明,实例,-,算术运算符：负号,=8*-5=-40,%,算术运算符：百分号,=60*5%=3,算术运算符：乘幂,=32=9=16(1/2)=4,*,和,/,算术运算符：乘和除,=3*2/4=1.5,+,和,-,算术运算符：加和减,=3+2-5=0,=,=,=5),判断,C1,大于等于,5,&,文本运算符：连接文本,=“,学习探讨,”,& “Excel”=,学习探讨,Excel,:,区域运算符：冒号,=SUM,（,A1:A10,）,（空格）,交叉运算符：单个空格,=SUM(A1:B5 A4:D9),相当于,=SUM(A4:B5),，,联合运算符：逗号,=SUM(A1:A5,B4:B9),相当于两个区域相加,42,一、认识公式和函数,比较运算符的结果为逻辑值。结果为,True,或者,False,。假如,A1,与,B1,的数据,完全相同，则,=(A1=B1),的结果为,true,，反之为,false,4,、运算符的优先顺序,通常情况下，,Excel,按照从左至右的顺序运算的，当使用多个运算符时，,Excel,将按照运算符的优先顺序进行运算，优先顺序如下：,:(,空格,),；,-,（负号）；,%,；,；*和,/,；,+,和,-,；,&,；,=,=,括号的优先级高于上表中的任何运算符，因此可以在公式中运用括号运算符,来调整运算的优先级次序。括号的嵌套，则由内向外进行运算。,此外，除了错误值之外，,Excel,中的文本、数值、逻辑值之间也存在大小关,系，总体上来说，数值小于文本，文本小于逻辑值。,如：,1“,一”；“真”,60,及格,不及格,),这个公式告诉我们，如果,A1,这个单元格里的数大于,60,，那么这个数表示及格,的意思，如果小于,60,，那么就表示不及格的意思。,同时，我们还可以用其他各种不同的逻辑判断值和不同的真、假返回值来满,足各种不同的要求。,比如，我们要把大于,60,分的标示为，而把低于,60,分的标示为，那么只需,将后面的返回值修改即可。,=IF(A160, , ),50,二、常用函数的使用探讨,3.3,、,IF,函数的进阶运用,由,=IF(A160,“,及格,”,“,不及格,”,),这个函数，我们可以实现了简单的判断，那,么，如果在这个基础上，我们在进行进一步的判断，则该如何呢？假如，我们需要,进一步判断：,60,以上的，高于,80,分则为优秀。那么怎么写公式呢？,=IF(A160, IF(A180, “,优秀”, “,及格 ”,) ,“,不及格”,),这个公式时上一个公式的深化，我们的要求是，在及格的范围里再进行区分，,那么我们就要将上面“及格”这个阶段再进行判断。嵌套另一个,IF,，如果大于,80,则表示优秀，否则，就是及格。,同样，我们还可以再深化一下。假如在,60,分一下，且在,55,分一下，那么就表,示补考，那怎么写公式呢？,=IF(A160, IF(A180, “,优秀”, “,及格 ”,) , IF(A155, “,补考”, “,不及格 ”,),一定要注意函数中括号的匹配。,IF,函数的判断节点需要科学设定,51,二、常用函数的使用探讨,4,、,AND/OR/NOT,函数的使用,我们在用,if,函数进行判断的时候，如果条件有多个而不仅仅是一个，比如说，,当一个数小于,10,但大于,5,，这个时候，怎么来表达呢？是否是：,=5A110,事实上，上面的公式只要,A1,取任意非错误值时，结果永远为,False,，永远不,成立。为什么呢？根据我们前面讲的运算符优先级，先从左至右运算，,5A1,假如,成立，则后面的为,True10,；假如不成立，则后面的为,False10,按照前面提到的，逻辑值最大，那么无论是,True10,或者,False10,，都是不,成立的。,这个时候，我们就要运用,AND,函数来进行多重判断。,=AND,（,A110,）,同理，,OR,表示或者；,NOT,表示都不是。其语法结构都是一样的。,52,二、常用函数的使用探讨,5,、常用日期函数,Now,、,Year,、,Month,、,Day,6,、常用数学函数,Mod,、,Round,、,Sum,、,Sumif,、,Sumproduct,7,、常用统计函数,Average,、,Count,、,Countif,、,Max,、,Min,8,、查找与引用函数,Index,、,Match,、,Vlookup,、,Row,9,、常用文本函数,Left,、,Right,、,Trim,、,Find,、,Mid,53,三、几个函数的专题探讨,3.1,Countif,函数的使用探讨,COUNTIF 函数计算区域中满足给定条件的单元格的个数。,COUNTIF(range,criteria),Range 为需要计算其中满足条件的单元,格数目的单元格区域；Criteria为确定哪些单元格将被计算在内的条件，其形,式可以为数字、表达式或文本。,实例分析：,1,、单条件下的数据统计,2,、分行统计个数，确定重复项,54,三、几个函数的专题探讨,3.2,Sumif,函数的使用探讨,用途：根据指定条件对若干单元格、区域或引用求和。,SUMIF(range,，,criteria,，,sum_range),。,Range,为用于条件判断的,单元格区域，,Criteria,是由数字、逻辑表达式等组成的判定条件，,Sum_range,为需要求和的单元格、区域或引用。,某单位统计工资报表中职称为“中级”的员工工资总额。假设工资总额存放在工,作表的,F,列，员工职称存放在工作表,B,列。则公式为“,=SUMIF(B1:B1000,，”中级,“，,F1:F1000)”,，其中“,B1:B1000”,为提供逻辑判断依据的单元格区域，“中级”,为判断条件，就是仅仅统计,B1:B1000,区域中职称为“中级”的单元格，,F1:F1000,为,实际求和的单元格区域。,55,三、几个函数的专题探讨,3.3,Vlookup,函数的使用探讨,用途：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行,中指定列处的数值。,VLOOKUP(lookup_value,，,table_array,，,col_index_num,，,range_lookup),Lookup_value,为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。,Table_array,为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。,Col_index_num,为,table_array,中待返回的匹配值的列序号。,Col_index_num,为,1,时，,返回,table_array,第一列中的数值；,col_index_num,为,2,，返回,table_array,第二列,中的数值，以此类推。,Range_lookup,为一逻辑值，指明函数,VLOOKUP,返回时是精确匹,配还是近似匹配。如果为,TRUE,或省略，则返回近似匹配值，也就是说，如果找不到精,确匹配值，则返回小于,lookup_value,的最大数值；如果,range_value,为,FALSE,， 函数,VLOOKUP,将返回精确匹配值。如果找不到，则返回错误值,#N/A,。,56,三、几个函数的专题探讨,3.4,Index+Match,组合,函数的使用探讨,INDEX(reference,，,row_num,，,column_num,，,area_num),，返回引用,中指定单元格或单元格区域的引用。,MATCH(lookup_value,，,lookup_array,，,match_type),，,返回在指定方式,下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹,配元素本身，则应该使用,MATCH,函数。,index+match,函数可以组合起来使用，并实现,Vlookup,函数所能实现的功能。,57,四、公式故障诊断,错误,常见原因,处理方法,#DIV/0!,在公式中有除数为零，或者有除数为空白的单元格,(Excel把空白单元格也当作0)。,把除数改为非零的数值，或者用,IF函数进行控制。,#N/A,在公式使用查找功能的函数,(VLOOKUP、HLOOKUP、LOOKUP等)时，找不到匹配的值。,检查被查找的值，使之的确存在于查找的数据表中的第一列。,#NAME?,在公式中使用了,Excel无法识别的文本，例如函数的名称拼写错误，使用了没有被定义的区域或单元格名称，引用文本时没有加引号等。,根据具体的公式，逐步分析出现该错误的可能，并加以改正。,#NUM!,当公式需要数字型参数时，我们却给了它一个非数字型参数；给了公式一个无效的参数；公式返回的值太大或者太小。,根据公式的具体情况，逐一分析可能的原因并修正。,58,四、公式故障诊断,错误,常见原因,处理方法,#VALUE,文本类型的数据参与了数值运算，函数参数的数值类型不正确；,函数的参数本应该是单一值，却提供了一个区域作为参数；,输入一个数组公式时，忘记按,CtrlShiftEnter键。,更正相关的数据类型或参数类型；,提供正确的参数；,输入数组公式时，记得使用,CtrlShiftEnter键确定。,#REF!,公式中使用了无效的单元格引用。通常如下这些操作会导致公式引用无效的单元格：删除了被公式引用的单元格；把公式复制到含有引用自身的单元格中。,避免导致引用无效的操作，如果已经出现错误，先撤销，然后用正确的方法操作。,#NULL!,使用了不正确的区域运算符或引用的单元格区域的交集为空。,改正区域运算符使之正确；更改引用使之相交。,59,四、公式故障诊断,使用公式检查工具：,60,进阶数据整理探讨,第三篇,-,主要对分级显示、合并计算、分类汇总及数据透视表等的探讨,61,上面两篇里，我们对,Excel,的基本数据整理,及公式函数进行了探讨。,事实上，函数功能固然强大且运用灵活，但,是，它毕竟只是完成某些具体的计算功能。,如果从,Excel,的数据处理来说，函数的运用只,是完成了处理的前期工作。,假如现在我们有一张庞大的表格，我们如何有,效地对其进行各种各样的整理和展示，这就是本篇,所要和大家一起探讨的东西。,62,一、从一张数据表说起,类似这样的一张数据表，实际上包含了所有的信息。但是有时候，我们,并不一定要在打开这个表的时候，就看到所有的信息，而只是想选择性地查,看某些信息，那么这样的表格展示，感觉冗繁了些。,63,一、从一张数据表说起,于是我们想，是否能利用一种级联式的展示方式，当需要查看某些数据,时，可以将其展开，而不需要查看的数据，则折叠起来隐藏。如下图所示：,毫无疑问，,Excel,是提供给了我们这种功能的。这就需要用到,Excel,中的,分级显示。,分级显示的好处在于，它能提供给用户一种自由选择的查看方式，只要,点击相应的折叠项，就可以对数据进行层级管理。,64,二、建立分级显示,建立分级显示，主要分为三种：自动建立、手动建立和对数据的分类汇总。,2.1,自动建立分级显示,自动建立分级显示对数据表的预制要求较高。如果表格具备以下两个基,本条件，则可以建立自动分级显示：,（,1,）、同一组中的行和列放在一起。如附件中的表格，属于各地区的省,份的行都放在一起。,（,2,）、汇总行总在数据的上方或者下方，汇总列总在数据的左侧或者,右侧。汇总行和汇总列使用求和公式,SUM,或者,SUBTOTAL,引用数据中的单元格。,在上述条件满足的情况下：,STEP1,：,选定需要分级显示的单元格区域（若对整个表格分级显示，则选中任,意单元格；,STEP2,：菜单“数据”,-“,组及分级显示”,-“,自动建立分级显示”,65,二、建立分级显示,这个时候，,Excel,会自动分析数据表中公式所在的位置，然后建立相应的,分级显示。如果只想对行进行分级显示，则在表中只选取,A1,：,C39,即可。,2.2,手动建立分级显示,手动建立分级显示对数据预制的要求较低。要手动建立分级显示，要求数,据中同一组中的行或列均放在一起，汇总行或列在数据的上方,/,下方、左侧,/,右侧，汇总行列中不要求有公式。,在上述条件下：,STEP1,：选定华北地区所在的行,26,行，,不含小计行,；,STEP2,：菜单“数据”,-“,组及分级显示”,-“,组合”；,STEP3,：用同样的方法对其他地区进行组合；,STEP4,：选定所有地区的行，,不含合计行,，即,238,行，单击菜单“数据”,-“,组及,分级显示”,-“,组合”；,66,二、建立分级显示,2.3,利用分类汇总建立分级显示,当用户选定单元格区域，执行“数据”,-“,分类汇总”后，,Excel,会总动根据,汇总字段，生成行向分级显示。,分类汇总是,Excel,中最常用的功能之一。它能快速以某一个字段为分页项，,对数据列表中的数据进行各种统计计算。,在使用分类汇总时需要注意：,第一点、在使用分类汇总功能以前，必须要对数据列表中需要分类汇总的,字段进行排序。否则无法实现分类汇总的效果。,第二点、分类汇总擅长于对单一条件进行,汇总，如果要实现多重汇总，则,需要在每次分类汇总前，将“替,换当前分类汇总”取消。,67,二、建立分级显示,2.4,分级显示的一些需要注意的地方,2.4.1,一个工作表只能有一个分级显示；,2.4.2,建立和清除分级显示都不能使用“撤销”命令进行撤销。因此，在操,作前，请慎重考虑是否要建立或者撤销；,2.4.3,分级显示最多只能有,8,个级别的明细数据；,2.4.4,可以在不删除分级显示的情况下，取消分级显示的某些部分的组合。,操作方法为：按住,Shift,键，单击该组的分级显示按钮（,+,、,-,），再,单击“数据”,-“,组及分级显示”,-“,取消组合”；,2.4.5,若要在不删除分级显示的情况下隐藏分级显示，可在“工具”,-“,选项”,-“,视图”,-“,窗口选项”，取消“分级显示符号”前的勾选。,68,三、合并计算,Excel,中可以运用“合并计算”功能来汇总一个或多个源区域中的数据，,具体根据源数据的分布形式可以分为两种：,3.1,通过位置的合并计算,通过位置的合并计算的前提是源数据中所有的项次都在相同的位置。也就,是说，每个源数据表都是统一的模式，数据位置都没有发生变化。比如：,汇总数据格式要求如下：,69,三、合并计算,上页可见，所有产品在各个分公司的位置均是不变的，包括需要汇总的汇总,表中的数据位置。这个时候，我们可以通过位置进行合并计算。,70,三、合并计算,3.2,通过分类方式的合并计算,假设在汇总公司全年员工工资时，由于每个月都有可能发生员工离职和入,职，而且排序也不完全一样，因此，不符合利用位置进行合并计算的条件。,这个时候，我们就需要使用分类方式的合并计算。,假如某公司的员工工资信息每月都保存在单独的表中，而且格式相同。,A,列为员工姓名，,B,列为员工在该月的工资。要求算出员工全年的工资总额，以,确定员工全年应缴社保的月数和基数。,操作方式见截图。,71,四、数据透视表,/,图,4.1,什么是数据透视表,数据透视表是,Excel,中最常见，功能最全的数据分析工具之一。,数据透视表有机综合了数据排序、筛选。分类汇总等数据分析的优点，可以,方便地调整分类汇总的方式，灵活地以多种不同的方式展示数据的特征。,一张数据透视表仅靠鼠标移动字段位置，即可变换出各种类型的报表。,同时，数据透视表也是解决函数公式速度瓶颈的手段之一。,总之，合理运用数据透视表进行计算与分析，能使许多复杂的问题简单化，,并极大地提高工作效率。,先来看一个简单的例子：,我们可以尝试用各种方法进行汇总分析，来确定数据透视表的优越性。,72,四、数据透视表,/,图,4.2,创建数据透视表,使用数据透视表和数据透视图向导可以创建数据透视表，启用向导的方法在,菜单“数据”里。,在该向导的指引下，用户只要一步步操作，就,可以完成基本的数据透视表的制作。,其主要步骤为：,Step1,：,选择数据源类型,Step2,：,选择数据源区域,Step2,：指定数据透视表位置,73,四、数据透视表,/,图,4.2.1,指定数据源类型，一般默认为数据透视表，却数据库为,Excel,，无需修改,4.2.2,指定数据源位置，默认为选择为有效数据区域,74,四、数据透视表,/,图,4.2.3,指定数据透视表的显示位置,使用现有工作表的话，将会在其下面的引用区域开始建立数据透视表。,单击“完成”按钮之前，可以单击“选项”对数据透视表格式和数据进,行提前设置，但建议在完成数据透视表的创建之后再进行设置。后者更,为灵活和方便。,75,四、数据透视表,/,图,4.3,数据透视表布局的设置,有两种方法可以设置数据透视表的布局，这也是创建数据透视表的过程中最,关键的一步。,4.3.1,在步骤,3,中单击“布局”按钮，在布局对话框中设置数据透视表的结构。,76,四、数据透视表,/,图,布局对话框中的结构设置如下图所示。,右边的按钮为数据表中的各列标题，用鼠标左键点击不放，可以拖,到“页”、“行”、“列”、“数据”中组成透视表的结构。,77,四、数据透视表,/,图,从结构上来看，数据透视表分成四个部分：,（,1,）页：此标志区域中的按钮将作为数据透视表的分页符。,（,2,）行：此标志区域中的按钮将作为数据透视表的行字符。,（,3,）列：此,标志区域中的按钮将作为数据透视表的列字符。,（,4,）数据：此,标志区域中的按钮将作为数据透视表中,显示汇总,的数据。,因为数据区域是需要进行,数据运算的，因此在数据,区域需要的是包含数值的,项目。可以在其上双击，,选择数据区域的运算方式。,78,四、数据透视表,/,图,这种方式下的行、列、页、数据区域含义是一样的，个人觉得在完成之前点,“布局”进行配置更为直观。,4.3.2,在步骤,3,时，直接点完成，在,Excel,工作表中进行透视表结构的配置,79,四、数据透视表,/,图,4.5,数据透视表的项目组合,项目组合是数据透视表的一个非常有用的功能。,Excel,提供了几种对项进,性组合的选择：,4.5.1,组合数据透视表的指定项,比如说，要将附件中的销售途径下的非国际业务部分组合成国内业务。,80,四、数据透视表,/,图,步骤如下：,Step1,：,单击“国内市场”，按住,Ctrl,键。在表中依次点击,A6,、,A7,、,A8,、,A9;,Step2,：,单击鼠标右键，在快捷菜单中选择“组及显示明细项目”，鼠标指向,“组合”并单击，,Excel,会自动生成销售途径,2,，并将选中的项目组合到,“数据组,1”,当中去。,Step1,：,选中标为,“,数据组,1,”,的单元格，输入新名字,“,国内业务,”,。,81,四、数据透视表,/,图,4.5.2,数字项组合,单击季度的任意字段，右键，选择组合，出现对话框，设定数字的,起止和步长即可。,82,四、数据透视表,/,图,4.5.3,时间日期项的组合,在订单日期上点击右键,-“,组及显示明细数据”,-“,组合”。,设置步长为“年”，再按住,Ctrl,，选择“月”,即可实现将年组合为年、月的数据,83,四、数据透视表,/,图,4.5.3,时间日期项的组合,在订单日期上点击右键,-“,组及显示明细数据”,-“,组合”。,设置步长为“年”，再按住,Ctrl,，选择“月”,即可实现将年组合为年、月的数据,84,谢 谢,85,