李永刚。
2023年5月。
前言。我们日常统计工作常常要进行数据整理、数据查询、数据汇总等数据处理工作,常用的工作平台有access、vfp、sql等,但绝大多数工作完全可以在excel中实现,它们的核心是相通的。可以说精通了excel完全可以应对我们日常的小型数据处理工作。
网络科组织的数据处理培训包括excel、access、vfp、sql、cognos等几部分,由简单到复杂。今天我们讲简单的excel,基础操作大家天天用,就不再讲解。我们从excel高级应用开始进行我们的培训。
第一部分使用数据库。
第一章建立excel数据库。
主要内容:数据库简介。
在excel下建立数据库。
使用数据库表单添加、定位和删除数据。
保证数据完整性。
一、数据库简介。
数据库就是一些数据按照有意义的方式集合在一起。
1.数据库组成。
excel数据库中的信息按**的方式存放。**是独有记录的集合,通常按分类排序。而**由字段组成,字段与列相对应。
数据库的第一行存放的是不同的字段,而其它行则是不同记录,行列交叉的单元格则是具体的数据。
2.数据库的类型。
数据库的类型主要有两类:平面数据库和关系数据库。
a平面数据库是两维数据库,其中用列表示字段,用行表示记录。它的优势是简单,易建易懂,不需专门的知识,适用简单的列表,例如,**表或通信表。它的缺点是维护难,存储效率不高,并且不能处理复杂的问题。
b关系数据库可以将多个表中的数据通过关键字(或称关键字段)联系起来,构成了复杂的关系模型。它的优点是可以处理复杂的问题,而且存储效率比较高。关系数据库仅可由数据库应用程序(如microsoft access)来创建。
关系数据的模型是树型结构,不允许环型结构。
3.excel数据库的特点。
excel仅能创建平面数据库,它的优点:具有扩展分析功能,不需要专门数据库知识,适合一般用户的使用,因此它的分布比较广。它的缺点:
没有数据完整性保护,安全性差,对并行使用的支持有限,规模小:excel理论上最多256列(字段),65535行(记录),它是无法处理人口普查那样的大型数据库,即使在有效范围内,在处理大型数据库时,不但受计算机性能的影响,而且也极为不方便。
二、在excel建立数据库。
建立excel数据库比较简单,在某行上各个单元格输入不同的字段名称,并在字段行(标头行)下输入具体的数据即可。
添加记录,记录定位,删除记录,插入记录的方法按平常的操作即可。也可通过记录单进行操作(用tab键进行换行)
注意:1)excel数据库必须是一个方形,从字段行开始,数据库中不能有空行,也不能有空列,因为excel通过空行或空列来判断数据库的边界。为了帮助excel检测字段行,字段行的格式应不同于数据行。
2)建立数据库时最好空出至少前5行,这样对excel的高级操作有好处。
三、保持数据完整性。
数据表建立好后一定要保证数据体的完整性。如果不完整就要调整直到完整为止。例如:
“兴丰”和“兴丰街道办事处”为同一数据体,但在分类汇总时会把它作为两个条件,此时就要进行数据完整性的维护了。方法是选择“兴丰街道办事处”单元格,右键,“从下拉列表中选择”,选择“兴丰”,这样就把“兴丰街道办事处”变成了“兴丰”
第二章高级数据库管理技术。
主要内容:简单排序。
使用自定义序列排序。
创建复杂的排序组合。
使用excel的自动筛选。
使用条件区域。
使用高级筛选。
使用excel的数据库函数。
一、高级排序。
要掌握排序技巧,必须先弄懂排序原则。我们以升序为例讲一下排序原则:
数字型字段:由小到大。1,2,3……
文本型字段(即字符型字段):1、文本型数字:先第一个数字由小到大,然后第二,第三个,依次类推,例如“2”比“10”大;2、英文:
先第一字母由a-z,再由a-z,然后第二字母,第三字母,依次类推;3、汉字:所有的汉字拼音字母,先第一字母由a-y,然后第二字母,第三字母,依次类推(默认情况下是按字母进行排序,当然也可以设置为按笔画进行排序)。
1. 简单排序。
简单的排序最多可以按三个关键字以不同的方式进行排序。但这常常不能满足我日常一些特殊的要求,这就需要一些高级操作了。
2.通过“自定义序列”进行排序。
例如各镇名称在排序时是按字母先后进行排序,但我们常常是按“行政区划码”进行排序,如果有没有该字段该怎么办?方法是通过自定义序列来达到我们的排序要求。
工具->选项->自定义序列->在输入序列框中输入内容,每一个内容都要回车->添加->确定。
注意:自定义排序次序功能只能使用一个自定义列表,并且必须用于主排序字段。
3.创建复杂的排序组合。
如果要用多重自定义排序,必须要再做些工作。
创建辅助列。
在紧邻数据库最右侧建立辅助列。
输入公式,=match(c12,$j$12:$j$17,0),该公式能将给定字段按指定的样式转换成一个数值,并将公式向下填充。
match(a,b,c)函数的含义:返回在指定方式下与指定数值匹配的数组中元素的相应位置,它的结果是数值。a 为需要在 b中查找的数据,即查找什么,它可以为数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用。
b可能包含所要查找的数值的连续单元格区域,它可以为数组或数组引用即到哪儿查找。c为数字 或 1,它指明 microsoft excel 如何在 b 中查找a即查找方式,如果为1,函数 match 查找小于或等于 a 的最大数值,b必须按升序排列;如果为0,函数 match 查找等于 a 的第一个数值,b可以按任何顺序排列;如果为-1,函数 match 查找大于或等于 a 的最小数值。b 必须按降序排列;如果省略c,则假设为 1。
如果要排序的话,选择数据->排序->在主关系字“机构类型排序”->在次关键字选“行业排序”->确定,查看结果。
注意:如果b为单元区域的话,则对b的引用必须是绝对引用,而不能是相对引用。b区域必须是连续的。
二、高级筛选。
1.自定义自动筛选。
它只能按一个字段的两个条件进行筛选。
它有12种条件可以使用:等于,不等于,大于,大于或等于,小于,小于或等于,开始于,不开始于,结束于,不结束于,含有,不含有(前6种对文本或数字进行操作,后6种仅对文本进行操作。
2.使用高级筛选。
进行更为复杂的筛选,需要使用高级筛选功能。在高级筛选中引入了条件区域的新概念。
条件区域:它是工作表上特定的区域,规定了筛选的查找条件。它通常由两个主要部分组成。
第一部分是条件标记行,这些标记应该映射数据库字段标记。第二部分是一行或多行的比较条件。这些比较条件由选项条件运算符和比较值组成。
a设置比较条件:
excel在筛选时不区分大小写,在一行上比较条件是与(and)的关系,在多行上比较条件是或(or)的关系。计算顺序为“z”字型,而非“n”型。(演示筛选过程)
b使用计算条件:
计算公式也可以用作筛选条件。要做到这一点,必须将括号中的条件运算符与要使用的公式组合在一起,条件运算符与公式间需要用连字符“&”级连在一起。例:
="**erage(e12:e36),就是筛选利润不小于平均利润的记录。(演示筛选过程)
c两个字段进行比较。
通过一定的条件筛选出自己所需结果。
例如一般情况下利润小于产值,但时有时由于不小心或误操作而使利润大于或等于了产值,如何才能将这些记录挑选出来呢?
第一步:建立一个“假”条件,以保持这类筛选条件。在b2单元格输入“利润不小于产值?”,这个假字段名称是什么无所谓。
第二步在b3中输入公式=(f12>=e12) ,公式必须计算出true或fals,在此我们关心的不是计算结果是“真”,还是“假”,而是计算公式是否正确,因为筛选是按计算公式进行的。
第三步选择数据->筛选->高级筛选->输入列表区域或条件区域->确定。同样通过c2:c3区域可筛选出产值大于利润的数据。
d规定筛选结果包含的字段。
如果有一个大型一个数据表,对观察数据是很不利的,此时可以通过条件区域来限制记录数,同样也可通过提出区域来限定字段数。第一步将显示的字段复制到某行上(因为字段名完全一致,所以使用复制的方式),第二步选中数据表某单元格->选择数据->高级筛选->将方式改为“将结果复制到其它位置->选择数据区域($a$11:$h$36)->条件区域(sheet2!
$b$2:$b$3)->提出区域(复制到:$b$40:
$e$40),第三步确定。这样就把利润大于产值的记录就筛选出来了。
利用上述方式可以将筛选结果存放到另一个工作表中。将要选定的字段复制到表3,选中表3的提出区域(至少两行,2007仅选择字段一行即可),在表3中点击数据->高级筛选(此时由于找不到数据区域会报错,确定即可),选择数据区域(sheet2!$a$11:
$h$36),条件区域(sheet2!$c$2:$c$3),提出区域(复制到:
$b$15:$e$15)。这样就把利润小于产值的记录筛选出来了。
3.模拟运算。
模拟运算与宏极为相似。首先是我们根据公式计算某变量情况下的一个结果,然后让计算机根据公式模拟我们的计算过程计算出各个变量的结果,并将结果回填到数据表中。
excel2003可以创建单变量模拟运算和双变量模拟运算。单变量模拟运算又分行变量模拟运算和列模拟运算。excel2003最多只能进行2个变量的模拟运算,而excel2007则可以通过方案进行多变量模拟运算。
a创建单变量模拟运算表。
单变量模拟运算表的结构特点是,其输入数值被排列在一列中(列引用)或一行中(行引用)。单变量模拟运算表中使用的公式必须引用输入单元格。
在一列或一行中,键入要替换工作表上的输入单元格的数值序列。
a.列引用:如果输入数值被排成一列,请在第一个数值的上一行且处于数值列右侧的单元格中,键入所需的公式。在同一行中,在第一个公式的右边,分别键入其它公式。
b.行引用:如果输入数值被排成一行,请在第一个数值左边一列且处于数值行下方的单元格内,键入所需的公式。在同一列中,在第一个公式的下方,分别键入其它公式。
选定包含公式和需要被替换的数值的单元格区域。
在“数据”菜单中,单击“模拟运算表”命令。如果模拟运算表是列方向的,请在“输入引用列的单元格”编辑框中,为输入单元格键入引用(此时要输入成为变量的单元格引用)。 如果模拟运算表是行方向的,请在“输入引用行的单元格”编辑框中,为输入单元格键入引用。
b创建双变量模拟运算表。
双变量模拟运算表中的两组输入数值使用同一个公式。这个公式必须引用两个不同的输入单元格。
在工作表的某个单元格内,输入所需的引用两个输入单元格的公式。
在公式下面同一列中键入一组输入数值,在公式右边同一行中键入第二组输入数值。
选定包含公式以及数值行和列的单元格区域。
在“数据”菜单中,单击“模拟运算表”命令。
在“输入引用行的单元格”编辑框中,输入要由行数值替换的输入单元格的引用。
在“输入引用列的单元格”编辑框中,输入要由列数值替换的输入单元格的引用。
Excel高级使用技巧
一定会给你一个满意的答案。6 自定义函数 虽然excel中已有大量的内置函数,但有时可能还会碰到一些计算无函数可用的情况。假如某公司采用一个特殊的数学公式计算产品购买者的折扣,如果有一个函数来计算岂不更方便?下面就说一下如何创建这样的自定义函数。自定义函数,也叫用户定义函数,是excel最富有创意和...
Excel高级使用技巧
excel高级使用技巧1 编辑技巧 5 多张工作表中输入相同的内容。5 多张工作表中输入相同的内容。几个工作表中同一位置填入同一数据时,可以选中一张工作表,然后按住ctrl键,再单击窗口左下角的sheet1 sheet2.来直接选择需要输入相同内容的多个工作表,接着在其中的任意一个工作表中输入这些相...
Excel高级使用技巧
根据需要,有时想把b列与c列的内容进行合并,如果行数较少,可以直接用 剪切 和 粘贴 来完成操作,但如果有几万行,就不能这样办了。解决办法是 在c行后插入一个空列 如果d列没有内容,就直接在d列操作 在 1中输入 b1 c1 d1列的内容就是b c两列的和了。选中d1单元格,用鼠标指向单元格右下角的...