EXCEL2007高级技巧大全(有史以来最全).pdf
《EXCEL2007高级技巧大全(有史以来最全).pdf》由会员分享,可在线阅读,更多相关《EXCEL2007高级技巧大全(有史以来最全).pdf(45页珍藏版)》请在咨信网上搜索。
1、1 Excel 第第一一章章 数据的整理和分析数据的整理和分析 1.1 数据的排序数据的排序 Excel 提供了多种方法对工作表区域进行排序,用户可以根据需要按行或列、按升序或降序已经使用自定义排序命令。当用户按行进行排序时,数据列表中的列将被重新排列,但行保持不变,如果按列进行排序,行将被重新排列而列保持不变。没有经过排序的数据列表看上去杂乱无章,不利于我们对数据进行查找和分析,所以此时我们需要按照对数据表进行整理。我们可以将数据列表按“出生年月”进行排序。如下图:首先我们需要单击数据列表中的任意一个单元格,然后单击数据标签中的排序按钮,此时会出现排序的对话框。看到弹出的排序对话框中,在“主
2、要关键字”下拉列表框中选择“出生年月”,在设置好主要关键字后,可以对排序依据进行设置,例如,数值、单元格颜色、字体颜色、单元格图标,在这样我们可以选择默认的数据作为排序依据。最后我们可以对数据排序次序进行设置,在次序下拉菜单中选择升序、降序或自定义排序。在这里我们选择升序,设置完成后单 2 击确定即可。如下图:我们除了可以对数据表进行单一列的排序之外,如果用户希望对列表中的数据按“性别”的升序来排序、性别相同的数据按“文化程度”升序排序、“性别”和“文化程度”都相同的记录按照“基本工资”从小到大的顺序来排序,此时我们就要对 3 个不同的列进行排序才能达到用户的要求。首先我们需要单击数据列表中的
3、任意一个单元格,然后单击数据标签中的排序按钮,此时会出现排序的对话框。看到弹出的排序对话框中,在“主要关键字”下拉列表框中选择“性别”,在添加好主要关键字后,单击“添加条件”按钮,此时在对话框中显示“次要关键字”,同设置“主要关键字”方法相同,在下拉菜单中选择“文化程度”,然后再点击“添加条件”添加第三个排序条件,选择“基本工资”。在设置好多列排序的条件后,单击确定即可看到多列排序后的数据表。如下图:在 Excel 2007 中,排序条件最多可以支持 64 个关键字。1.2 数据的筛选数据的筛选 筛选数据列表的意思就是将不符合用户特定条件的行隐藏起来,这样可以更方便的让用3 户对数据进行查看。
4、Excel 提供了两种筛选数据列表的命令。自动筛选:适用于简单的筛选条件 高级筛选:适用于复杂的筛选条件 想要使用 Excel 的自动筛选功能,首先要单击数据列表中的任意单元格,然后选择数据标签,单击筛选按钮即可。单击数据列表中的任何一列标题行的下拉箭头,选择希望显示的特定行的信息,Excel会自动筛选出包含这个特定行信息的全部数据。如下图:在数据表格中,如果单元格填充了颜色,使用 Excel 2007 我们还可以按照颜色进行筛选。如果条件比较多,可以使用“高级筛选”来进行。使用高级筛选功能可以一次把我们想要看到的数据都找出来。例如数据表中我们想要把性别为男、文化程度为研究生、工资大于 200
5、0 的人显示出来。我们可以这么来做:先设置一个条件区域,第一行输入排序的字段名称,在第二行中输入条件,建立一个条件区域。如下图:4 然后选中数据区域中的一个单元格,单击数据标签中的高级筛选命令。Excel 自动选择好了筛选的区域,我们单击这个条件区域框中的拾取按钮,选中我们刚才设置的条件区域,单击拾取框中的按钮返回高级筛选对话框,单击确定按钮,现在表中就是我们希望看到的结果了。设置高级筛选条件设置高级筛选条件 高级筛选可以设置行与行之间的“或”关系条件,也可以对一个特定的列指定三个以上的条件,还可以指定计算条件,这些都是它比自动筛选优越的地方。高级筛选的条件区域应该至少有两行,第一行用来放置列
6、标题,下面的行则放置筛选条件,需要注意的是,这里的列标题一定要与数据清单中的列标题完全一样才行。在条件区域的筛选条件的设置中,同一行上的条件认为是“与”条件,而不同行上的条件认为是“或”条件。1.3 在筛选时使用通配符在筛选时使用通配符 在设置自动筛选的自定义条件时,可以使用通配符,其中问号(?)代表任意单个字符,星号(*)代表任意一组字符。5 1.4 数据的分类汇总与分级显示数据的分类汇总与分级显示 分类汇总是 Excel 中最常用的功能之一,它能够快速地以某一个字段为分类项,对数据列表中的数值字段进行各种统计计算,如求和、计数、平均值、最大值、最小值、乘积等。比如这张各部门工资统计表,我们
7、希望可以得出数据表中每个部门的员工实发工资之和。现在我们用分类汇总来做:首先单击部门单元格,单击数据标签中的升序按钮,把数据表按照“部门”进行排序,然后在数据标签中,单击分类汇总按钮,在这里的分类字段的下拉列表框中选择分类字段为“部门”,选择汇总方式为“求和”,汇总项选择一个“实发工资”,单击“确定”按钮。如下图:6 当我们点击确定后,我们就可以看到已经计算好各部门实发工资之和了。如下图:在分类汇总中我们的数据是分级显示的,现在工作表的左上角出现了这样的一个区域,我们单击这个 1,在表中就只有这个总计项出现了。7 单击这个 2,出现的就只有这些汇总的部分,这样我们可以清楚地看到各部门的汇总。单
8、击 3,可以显示所有的内容。复制汇总结果复制汇总结果 当我们使用分类汇总后,往往希望将汇总结果复制到一个新的数据表中,如下图。但当我们直接进行复制后,无法只复制汇总结果,而复制的是所有数据。此时我们就需要使用“Alt+;”组合键选取当前屏幕中显示的内容,然后在进行复制粘贴。1.5 条件格式条件格式 使用 Excel 中的条件格式功能,可以预置一种单元格格式,并在指定的某种条件被满足时自动应用于目标单元格。可以预置的单元格格式包括边框、底纹、字体颜色等。此功能可以根据用户的要求,快速对特定单元格进行必要的标识,以起到突出显示的作用。8 例如下图中的产品销售标,我们需要快速找出所有与“防水键盘”相
9、关的销售数据。首先我们全选所有数据,然后在开始标签中找到条件格式按钮,单击突出显示单元格规则,在打开的选项中单击等于按钮。9 在弹出的对话框中输入我们想要查找的“防水键盘”,然后设置单元格显示样式,例如让单元格以“浅红填充色深红色文本”显示,设置完毕后,点击确定按钮。在点击确定后,我们可以数据表中已经显示出和防水键盘相关的所有信息。这样我们就可以快速查看我们所关心的信息了。如下图:在 Excel 2007 中,使用条件格式不仅可以快速查找相关数据,还可以以数据条、色阶、图标的方式显示数据,让用户可以对数据一目了然。首先我们全选所有数据,然后在开始标签中找到条件格式按钮,单击数据条,在打开的选项
10、中选择任意颜色。当我们选择颜色时,即使不点击,也可以即时的看到数据变化。通过条件格式,我们就可以清楚的看到那条数据销售额最高,那条数据销售额最低。如下图:10 1.6 数据透视表数据透视表 数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式动态表格,能帮助用户分析、组织数据。例如,计算平均数、标准差,建立列联表、计算百分比、建立新的数据子集等。建好数据透视表后,可以对数据透视表重新安排,以便从不同的角度查看数据。数据透视表可以从大量看似无关的数据中寻找背后的联系,从而将纷繁的数据转化为有价值的信息,以供研究和决策所用。我们来看下面这个数据表,在这个数据表中显示了每个财季产品销售的明细数据
11、。11 现在我们想把这里的统计每个财季的利润,可以结合条件格式判断最低利润出自哪个财季。首先打开插入标签,单击数据透视表按钮,打开创建数据透视表对话框。然后选择透视表的数据来源的区域,Excel 已经自动选取了范围,这里它的选取是正确的,我们不做什么改动,接下来我们选择透视表放置的位置,选择新建工作表项,单击确定按钮。现在就出现了我们建立的数据透视表。在工作表的透视表的各个部分都有提示,同时界面中出现了一个数据透视表字段列表,里面列出了所有我们可以使用的字段,我们的目的是查看统计每个财季的利润,因此我们在要添加到报表的字段中选择财季,然后拖动利润和数量字段到数值区域,此时,我们就可以看到每个财
12、季的利润和销售数量显示在数据透视表中了。同样的,在数据透视表中可以直接看到利润和销售数量的汇总数目。现在我们就可以结合条件格式功能,查看出那个财季利润最低。如下图:12 1.7 数据透视图的使用数据透视图的使用 我们还可以根据数据透视表直接生成图表:点击选项标签,点击数据透视图按钮,在弹出的对话框中选择图表的样式后,单击确定就可以直接创建出数据透视图。不难看出这个图表跟我们平时使用的图表基本上一致,所不同的只是这里多了几个下拉箭头,单击“财季”的下拉箭头,这实际上是透视表中字段,选择“Q1”,可以看到图表中的数据也发生了变化,其它有很多在透视表中使用的方法也可以在这个图表中使用,把图表的格式设
13、置一下,一个漂亮的报告图就完成了。13 1.8 获取外部数据获取外部数据 在 Excel 中,我们可以获取外部的数据到 Excel 中,这样我们就可以利用 Excel 的功能对数据进行整理和分析。以一个例子来看:这里有这样的一个文本文件,我们把其中的数据引入到 Excel 中来。打开 Excel,打开数据标签,我们可以看到很多种文件类型都可以倒入到 Excel 表格中。我们点击“自文本”按钮,然后选择我们文本所在位置,选择完毕后,此时会弹出“文本导入向导”,这里的原始数据类型就选择“分隔符号”一项,在这个对话框的下面有一个预览框,从这里可以看出来现在的第一行没有什么用处,因此我们将这里的“导入
14、起始行”输入框中的数值改成 2,单击“下一步”。14 现在我们要选择文本文件的数据字段分隔符,根据文件的特点,我们选择为“Tab 键”,单击“下一步”按钮。现在要选择导入数据的默认格式,现在的默认格式是“常规”,我们这里不做什么改动,单击“完成”按钮,此时出现“导入数据”对话框,我们可以选择将数据导入到现有工作表或新建工作表。把文本文件中的数据全部引入进来以后,把它们稍微调整一下,我们就可以使用这些数据了。15 1.9 冻结和拆分窗口冻结和拆分窗口 拆分窗口:我们可以使用视图标签中的拆分按钮来拆分窗口。而如果只需要垂直拆分,可以直接拖动垂直滚动条到最顶端,此时横项拆分线就会消失。撤消窗口的拆分
15、:同样在视图标签下,再次点击拆分按钮即可撤销窗口的拆分。冻结窗格:我们可以使用冻结窗格来更好的查看数据表头。首先选中一个单元格,选择视图标签,点击冻结窗,在下拉菜单中我们可以选择冻结第一行或第一列,当然我们也可以自行选择想要冻结的位置。如下图:如果我们想要冻结第一行以及 A、B 两列,此时我们需要将光标放在第一行的下面,并且 A、B 两列的右侧,即 C2 单元格,此时点击冻结拆分窗格即可冻结第一行以及 A、B 两列。16 1.10 表对象表对象 在 Excel 2007 中,我们可以直接将数据表格直接转换为表对象,这样我们就可以直接对数据表格进行排序、筛选、调整格式等操作。首先我们找到插入标签
16、,点击表按钮,此时我们就将数据表格转换为了表对象。我们可以看到,当我们把数据转换为表对象后,他会自动出现筛选功能。图下图:并且在设计标签中,我们可以即时调整表的样式等。如下图:1.11 插入插入 SmartArt 在 Excel 2007 中,有了一种新的对象可以更好的丰富 Excel 数据表,那就是 SmartArt。我们可以通过插入标签,点击 SnartArt,在弹出得对话框中选择想要插入 SmartArt 类型以及具体样式,如下图:17 我们选择基本列表后,点击确定,这样一个 SmaerArt 对象就直接添加到我们的数据表当中,此时我们就可以输入文字、数据并且调整样式了。很快,一个漂亮的
17、 SmartArt 就已经出现在我们的 Excel 当中了。如下图:1.12 插入其他的对象插入其他的对象 插入一个已存在的对象:在插入标签中,点击对象按钮,在对象对话框中(如下图)选择“Microsoft Office Visio 绘图”,点击确定,此时就会在 Excel 中启动 Visio 绘图功能,当我们绘制好图形后,就可以将 Visio 图直接插入到 Excel 中。18 1.13 选择性粘贴选择性粘贴 Excel 为我们提供了一些自动功能。例如选择性粘贴。这里的选择性粘贴是指把剪贴板中的内容按照一定的规则粘贴到工作表中,而不是像前面那样简单地拷贝。就拿这个数据表来举例。如下图:这里的
18、“销售利润”一栏是我们使用公式计算得到的,选择这一栏,复制到 Sheet2 中,可以看到数值并没有跟着复制过来;这时就可以使用选择性粘贴了:单击鼠标右键,单击“选择性粘贴”命令,打开“选择性粘贴”对话框(如下图),在“粘贴”一栏中选择“数值”,单击“确定”按钮,数值就可以粘贴过来了。这种情况不仅是在几个工作表之间复制时会发生,在同一个工作表中进行复制时也会遇到,到时可要注意。19 选择性粘贴还有一个很常用的功能就是转置功能。简单地理解就是把一个横排的表变成竖排的或把一个竖排的表变成横排的:选择这个表格,复制一下,切换到另一个工作表中,打开“选择性粘贴”对话框,选中“转置”前的复选框,单击“确定
19、”按钮,可以看到行和列的位置相互转换了过来。另外一些简单的计算也可以用选择性粘贴来完成:选中这些单元格,复制一下,然后打开“选择性粘贴”对话框,在“运算”一栏选择“加”,单击“确定”按钮,单元格的数值就是原来的两倍了。此外你还可以粘贴全部格式或部分格式,或只粘贴公式等等。1.14 自动更正自动更正 现在我们在单元格中输入(c)然后按一下回车,可以看到输入的字符变了。这是 Excel的自动更正功能,我们再输入(r),回车;同样可以看到输入的字符又变了。其实这些都是可以设置的:点击 office 按钮,单击选项,在校对中找到自动更正选项按钮,单击会打开自动更正对话框(如下图),这里列出了所有自动更
20、正的选择,在“键入时自动替换”列表中的头两个就是我们刚才看到的,输入(c)则替换为(c),输入(r)则替换为(r)。20 我们还可以把自己容易犯错的词做一个自动更正的词条,以后再输入起来就很方便了:在“替换”输入框中写入“制做”,在“替换为”输入框中填上“制作”,单击“添加”按钮将其添加到列表中,单击“确定”按钮;在单元格中输入“制做”,回车,可以看到“制做”就被替换成了“制作”。如果你不想使用哪个自动更正的选项了,就在自动更正对话框中把这个选项去掉就行了。1.15 宏的使用宏的使用 在 Excel 中有一种重要的自动功能,那就是“宏”。我们每次听到“宏”总是和病毒在一块儿,一般自己做的宏是不
21、会对自己有害的。合理地使用宏可以大幅度地提高工作效率,而且有些操作不使用宏的话会很麻烦。最简单的宏可以通过录制来做。在 Excel 2007 中要想使用宏功能,需要添加开发工具标签。首先点击 Office 按钮,单击 Excel 选项按钮,在常用选项中,将“在功能区显示开发工具选项卡”勾选,点击确定返回 Excel 界面后,我们就可以看到在标签中多了一个“开发工具”。21 现在比如我们写一个宏,让它删除 A3 单元格的内容:点击开发工具标签,单击录制宏按钮,在弹出“录制新宏”对话框(如下图),在“宏名”输入框中输入宏的名字,单击“确定”按钮。此时我们就可以进行宏的录制了,在开发工具标签中最左侧
22、可以看到目前的状态是“停止录制”,表示当我们在创建一个宏后,就已经开始在录制宏了;单击 A3 单元格,按一下Delete 键,这个宏的操作就算完成了,单击“停止录制”。现在在 A3 单元格中随便填上数值,选择其它的单元格,找到开发工具标签,单击宏按钮,打开“宏”对话框(如下图),选择我们刚才录制的宏,单击“执行”按钮,A3 单元格的内容就没有了。现在我们打开一个带有宏的工作簿,Excel 会提醒我们打开的文件中带有宏,如果你不能确定宏是否带有恶意的成分就选择“禁用宏”,否则可以选择“启用宏”;在禁用之后即使宏中有恶意成份也不会对你的机器起作用了。22 1.16 超链接的使用超链接的使用 在 E
23、xcel 中我们可以使用超级链接连接其它的文件或 Web 地址,也可以粘贴一个电子邮件地址,在用鼠标单击这个地址时自动启动相应的电子邮件程序,发送电子邮件等等。选中要插入超级链接的单元格,找到插入标签,单击超链接按钮,打开“插入超级链接”对话框(如下图),这里的“要显示的文字”输入框中显示的就是单元格中的文字;先来插入一个经常去的 Web 页面:注意左边这个“原有文件或网页”按钮处于“按下”的状态,在列表中显示出了最近浏览过的页面,在上面的输入框中我们输入 ,单击“确定”按钮将这个超级链接设置好;现在将鼠标移动到单元格上,鼠标变成了手的形状,单击,系统就会自动启动浏览器对我们输入的页面进行浏览
24、了。不过这只是超级链接作用的一个小方面,更多的时候使用超级链接是为了查阅工作表的方便;在 A1 单元格上单击右键,从弹出的菜单中单击“超链接”按钮,就可以打开“插入超链接”对话框(如下图),单击左边“链接到”列表中的“本文档中的位置”按钮,对话框的样子就发生了改变,单击“或在这篇文档中选择位置”:列表中“单元格引用”前面的加号,选择“Sheet2”,在“请键入单元格引用”输入框中就显示出了“A1”,这正是我们想要链接的单元格,单击“确定”按钮设置好链接,现在单击这个单元格,Excel 就自动跳转到了 Sheet2 的 A1 单元格上。23 我们还可以像在网页上那样插入一个邮件地址:选择单元格,
25、打开单击右键,从弹出的菜单中选择“超链接”按钮,打开“插入超链接”对话框,单击左边“链接到”列表中的“电子邮件地址”按钮,然后在“电子邮件地址”输入框中输入要插入的电子邮件地址,单击“确定”按钮,就将电子邮件地址链接到了单元格中;单击单元格,就会启动默认的电子邮件程序给刚才插入的电子邮件地址发送信件了。我们还可以链接到其它的文档中,打开“插入超链接”对话框,选择“链接到”列表中的“原有文件或网页”按钮,然后单击“当前文件夹”按钮,选择要链接的文件,单击“确定”按钮设置好链接,单击刚才设置的单元格,就可以转而查阅链接的文档了。1.17 数据的有效性数据的有效性 使用 Excel 的数据有效性功能
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL2007 高级 技巧 大全 有史以来
1、咨信平台为文档C2C交易模式,即用户上传的文档直接被用户下载,收益归上传人(含作者)所有;本站仅是提供信息存储空间和展示预览,仅对用户上传内容的表现方式做保护处理,对上载内容不做任何修改或编辑。所展示的作品文档包括内容和图片全部来源于网络用户和作者上传投稿,我们不确定上传用户享有完全著作权,根据《信息网络传播权保护条例》,如果侵犯了您的版权、权益或隐私,请联系我们,核实后会尽快下架及时删除,并可随时和客服了解处理情况,尊重保护知识产权我们共同努力。
2、文档的总页数、文档格式和文档大小以系统显示为准(内容中显示的页数不一定正确),网站客服只以系统显示的页数、文件格式、文档大小作为仲裁依据,平台无法对文档的真实性、完整性、权威性、准确性、专业性及其观点立场做任何保证或承诺,下载前须认真查看,确认无误后再购买,务必慎重购买;若有违法违纪将进行移交司法处理,若涉侵权平台将进行基本处罚并下架。
3、本站所有内容均由用户上传,付费前请自行鉴别,如您付费,意味着您已接受本站规则且自行承担风险,本站不进行额外附加服务,虚拟产品一经售出概不退款(未进行购买下载可退充值款),文档一经付费(服务费)、不意味着购买了该文档的版权,仅供个人/单位学习、研究之用,不得用于商业用途,未经授权,严禁复制、发行、汇编、翻译或者网络传播等,侵权必究。
4、如你看到网页展示的文档有www.zixin.com.cn水印,是因预览和防盗链等技术需要对页面进行转换压缩成图而已,我们并不对上传的文档进行任何编辑或修改,文档下载后都不会有水印标识(原文档上传前个别存留的除外),下载后原文更清晰;试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓;PPT和DOC文档可被视为“模板”,允许上传人保留章节、目录结构的情况下删减部份的内容;PDF文档不管是原文档转换或图片扫描而得,本站不作要求视为允许,下载前自行私信或留言给上传者【二***】。
5、本文档所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用;网站提供的党政主题相关内容(国旗、国徽、党徽--等)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
6、文档遇到问题,请及时私信或留言给本站上传会员【二***】,需本站解决可联系【 微信客服】、【 QQ客服】,若有其他问题请点击或扫码反馈【 服务填表】;文档侵犯商业秘密、侵犯著作权、侵犯人身权等,请点击“【 版权申诉】”(推荐),意见反馈和侵权处理邮箱:1219186828@qq.com;也可以拔打客服电话:4008-655-100;投诉/维权电话:4009-655-100。