EXCEL用法分享资料.ppt

《EXCEL用法分享资料.ppt》由会员分享，可在线阅读，更多相关《EXCEL用法分享资料.ppt（39页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel用法分享用法分享信息中心：陈斌峰信息中心：陈斌峰先让大家看一个曾经很熟悉的东东！先让大家看一个曾经很熟悉的东东！再让大家看一个很奇怪的东东！再让大家看一个很奇怪的东东！两个作品两个作品研讨大纲研讨大纲 “宏”及简单VBA编程 Excel 函数用法请点击请点击请点击请点击 Excel 图表魅力请点击请点击 Excel 常用技巧请点击请点击 Excel 基础操作请点击请点击请点击请点击 Excel 综合运用实例ExcelExcel 基础操作基础操作目录目录 打印设置 定位的使用 常用快捷键请点击 查找、替换的使用 分组显示 选择性粘贴的使用 基础操作请点击请点击请点击请点击请点击请点击

2、自定义菜单、自定义快捷键请点击请点击 基本概念基本概念基本概念一、基本名词：一、基本名词：Excel软件、工作簿、工作表、行、列、单元格软件、工作簿、工作表、行、列、单元格 这几个基本概念大至可以这样理解：Excel软件软件用于打开一个或多个工作簿工作簿，一个工作簿工作簿中包含一个或多个工作表工作表，一个工作表工作表中包含多行行或多列列，行行或列列中包含一个或多个单元格单元格二、操作界面概念：二、操作界面概念：菜单栏、工具栏、行号列标（菜单栏、工具栏、行号列标（R1C1样式）、工作表标签、滚动条、样式）、工作表标签、滚动条、编辑栏、状态栏编辑栏、状态栏菜单栏：文件、编辑、视图、插入、格式、工具

3、、数据、窗口、帮助工具栏：常用、格式、绘图、控件等等基础操作一基础操作一新建工作簿、新建工作表、简单录入修改工作表名：右键工作表标签重命名或双击工作表标签插入删除单元格：【插入】单元格或右键单元格插入（Ctrl+“+”）插入删除行、列：【插入】行、列或右键行列插入（Ctrl+“+”）插入删除工作表：【插入】工作表或右键工作表标签插入切换快速选取工作表：工作表较多时使用。右键工作表标签左边的滚动条插入符号：【插入】符号插入函数：【插入】函数格式刷：常用工具栏下面的“”图标，复制原单元格格式到目标单元格插入超链接：【插入】超链接合并取消合并单元格：选右键单元格格式合并单元格或者工具栏“”图标注：可

4、以批量合并取消合并单元格，合并单元格且不改到原始数据冻结拆分工作表窗口：【窗口】冻结窗口拆分工作表：【窗口】拆分新建、重排窗口：【窗口】新建窗口、重排窗口基础操作二基础操作二公式及数据填充：鼠标移到单元格右下角变成“+”号时下拉或者双击；Ctrl+D；Ctrl+Enter设置单元格格式：【格式】单元格、右键-单元格格式组合工作表：Ctrl+鼠标选择对数据进行排序：【数据】排序筛选数据：【数据】筛选自动筛选、高级筛选对数据进行分类汇总：【数据】分类汇总分列：【数据】分列，更新工作表数据类型，将文本按长度或者标志分开合并计算：【数据】合并计算，对多工作表合并计算Excel名称定义：【插入】名称（C

5、trl+F3）如：数据有效性条件中不能使用对其他工作簿或工作表的引用，使用定义名称就可绕过此限制。打印基础设置打印基础设置1、预览打印效果：【文件】打印预览（Alt+F+V）2、页面设置：【文件】页面设置（Alt+F+U）（也可以从打印预览设置中调出）“页面”选项卡：横向、纵向、几页高几页宽、打印纸张、打印“页边距”选项卡：上下左右边距、对齐方式“页眉页脚”选项卡：定义页眉页脚，打印出相应的说明和页码等资料“工作表”选项卡：定义打印区域、打印标题、其他一些选项（是否打印单元格颜色，是否打印错误值，是否打印网格，是否打印批注等等）注：通过设置打印标题，能将较大的、需要分页的工作表每页都设置一个标

6、题，这样打印出来才能比较美观，并且比较直观3、时时关注打印效果：【视图】分页预览 切换成分页视图，可以时时调整打印页面布鼠标凭借着其傻瓜式操作，越来越多的代替了键盘的功能，甚至有人鼠标坏了就用不了电脑了，但实际上，用键盘可以完成所有操作，而只用鼠标不行，只要你愿意学习和使用，键盘许多操作是鼠标永远无法比拟和代替的，主要体现在其快捷、方便。例如：较大数据的选择，快速保存，快速定位，快速查找、快速计算、插入删除单元格及行列等常用快捷键常用快捷键查找、替换的使用查找、替换的使用操作：【编辑】查找（Ctrl+F）、替换（Ctrl+H）可查找（替换）某特定格式或某文本；可在整个工作簿内查找（默认为仅在工

7、作表内查找）；可设定是否区分大小分、全角半角等。查找内容中可使用“？”（任何单个单个字符）、“*”“任何字符数）等通配符。技巧技巧1：查找找*或？符号本身或？符号本身在*和?前加 即可使它们失去通配符的作用，如：要查找“*”那么在查找内容里输入“*”即可；要查找“lhm?88”请输入“lhm?88”。技巧技巧2：将工作表中包含某字符串（如：将工作表中包含某字符串（如“恒安集恒安集团”）的多）的多处单元格一次性元格一次性设置置为某格式某格式方法1：【编辑】替换，“替换为”“格式”。方法2：【编辑】查找“查找全部”按Ctrl+A，再设置需要的格式退出。方法3：【编辑】查找 设置格式格式刷“查找全部

8、”Ctrl+A技巧技巧3：按格式：按格式选择单元格（全元格（全选某种格式的所有某种格式的所有单元格），批量改元格），批量改变格式格式技巧技巧4：查找工作簿范找工作簿范围内的合并内的合并单元格元格技巧技巧5：查找工作簿范找工作簿范围内的外部数据内的外部数据连接接定位的使用定位的使用操作：【编辑】定位（Ctrl+G）什么是定位？定位就是Excel替你选定符合你所设定位条件的单元格。此功能的快捷捷为Ctrl+G，打开定位对话框后单击“定位条件”按钮可选择包含特殊字符或条目的对象、批注或单元格。这是一个很实用的功能。下面介绍一下它的一些常用选项：若要选择包含批注的单元格，请单击“批注”。若要选择包含常

9、量的单元格，请单击“常量”。若要选择包含公式的单元格，请单击“公式”注注：常量和公式选项又有数字、文本、逻辑值、错误等子选项可选。若要选择空白单元格，请单击“空值”。若要仅选择区域中可见的单元格，虽然该区域也跨越隐藏的行和列，请单击“可见单元格”（Alt+；）。若要选择当前区域当前区域，如整个列表，请单击“当前区域”。注注释：当前区域：当前区域指填写了数据的区域，该区域包括当前选定的单元格或单元格区域。该区域向四周扩展到第一个空行或空列。技巧：技巧：如果要选定一个很大的单元格区域，建议使用定位功能。如要定选A1：D20000区域，为避免大量滚屏，可以直接在定位对话框的“引用位置”直接输入A1：

10、D20000即可。选择性粘贴的使用选择性粘贴的使用选择性粘贴是一个非常实用的功能，下面介绍常用的几个选项：1.粘贴公式2.粘贴数值：仅粘贴来源数据的数值，不粘贴来源数据的格式3.粘贴格式：等同于格式刷4.粘贴批注5.粘贴时运算：可对粘贴对象进行加减乘除运算跳过空单元防止用复制的空白单元格替换数据单元格。6.转置复制区域的顶行数据将显示于粘贴区域的最左列，而复制区域的最左列将显示于粘贴区域的顶行，很方便哦。7.有效性粘贴单元格的有效性条件。注：注：“全部”和“边框除外”选项也可复制数据有效性设置。操作：【编辑】复制、【编辑】选择性粘贴或点右键选择性粘贴 和Word的大纲视图一样，Excel这个功

11、能主要用于处理特别大的工作表时，难以将关键条目显示在同一屏上的问题。如果在一张表格上名目繁多，但数据类型却又有一定的可比性，那么我们完全可以用组合来解决这个问题，如下所示，当然，如果你还想查看明细的话，单击表格左侧的“+”按钮即可。分组显示分组显示自定义菜单、自定义快捷键自定义菜单、自定义快捷键你是不是经常使用“复制-粘贴-选择性粘贴-数值”？是不是希望有快捷键操作？可是Excel不自带？怎么办？自定义！基本上我们经常用到的菜单都可以通过自定义快捷键实现！让我们飞速处理吧！步骤如下：工具-自定义-命令-编辑-粘贴数值找到“粘贴数值”拉入到菜单栏随便一个地方，在粘贴数值上右键勾上总是只用文字这样

12、按Alt+P就可以快速粘贴数值了基础知识测试题基础知识测试题1、新建一个工作簿，命名为“EXCEL基础知识测试答题-某某某”（某某某为您的姓名）2、删除sheet3工作表，sheet1和sheet2分别更名为工作表1和工作表23、格式设置：在工作表1中从A1到B4按以下格式输入以下左图内容（表头合并居中、单元格垂直和水平都居中，第三列数字不保留小数点，用千分位）4、打印设置：设置前两行和第一列为打印标题，打印成一页高一页宽，表头黄色不打印，设置页眉为工作簿名，页脚显示第几页5、在工作表2中将工作表1内容转换为如上右图6、见如下附表（筛选）7、见如下附表（请分别用定位方法，查找替换方法8、将基础

13、知识测试题两个工作表放入你刚建的表格中最终你的表格有四个工作表ExcelExcel 常用技巧常用技巧目录目录 Excel的自定义格式 Excel的有效性用法请点击 Excel的条件格式用法 Excel的窗体控件的用法 Excel的数据透视 Excel出错信息详解 Excel的自定义序列 Excel保护与加密请点击请点击请点击请点击请点击请点击请点击 Excel共享工作簿请点击众所周知：在单元格内输入一月、二月，然后再用鼠标一拉，三月、四月就会出来了，但能不能实现，输入一个物料类别，就可以拉出所有物料类别？能否将一个表中数据按另一个表的某一列进行排序？自定义序列帮你搞定！Excel的自定义序列的

14、自定义序列前面提到的“执着的情书”，也就是不论在单元格中单元格输入什么内容，他就是只能显示一个值，是怎么实现的呢？假如有几千人，要输入性别，能否输入1，就出来男，输入2，就自动出来女？有没遇到过这种情况，我们统计都用箱，领导要看的是万箱，我们得再进行转化，能否数据是箱，显示出来的却是万箱？能否输入小写自动变大写？自定义格式帮你都搞定！Excel的自定义格式的自定义格式有没有遇到过这样的情况，当您让别人给您填写些数据时，老是不能按您的要求填写，更有甚者，会把您的表格乱改一通，比如有时候是中纸，有时候是中国纸业，有时候是恒安中国纸业，有时候是总部纸业晕，让我如何统计分析？能否在选定某个单元格的时候

15、就提示输入的内容要求，并在输入错误的时候进行报错或警告？能否直接限死某个单元格能输入的内容和范围？能否在选定单元格时出现下拉框供用户选择？可以！数据有效性帮您实现！Excel的有效性用法的有效性用法如果你每天都在跟踪库存，你是不是希望一但库存超出或者低于警戒线时自动标出醒目颜色来提醒？如果你在管理合同，是不是希望一但合同快到到期的就提前提醒？如果你在管理绩效，是不是希望完成得好的和完成得不好的自动帮你提醒？几乎可以这样说，你想要Excel提醒你什么，它都可以做到！Excel的条件格式用法的条件格式用法Excel的数据透视的数据透视当表格中内容非常多，需要对部分数据进行统计、汇总、分析，并用不同

16、的表现形式显示出来时，可以用到数据透视表及数据透视图。比如从发货量明细中按生产公司汇总4天内发货量、7天内发货量、月度发货量、月度订单，实现步骤：选取要进行数据统计分析的范围选取“数据”下“数据透视表和数据透视图”按步骤点“下一步”，一直到向导步骤之三，点“布局”将“生产公司”拖放到“行位置”，将“4天内发货量、7天内发货量、月度发货量、月度订单”拖放到“行”与“列”交叉的位置（数据区）。对于拖放出来的“4天内发货量、7天内发货量、月度发货量、月度订单”可以用用鼠标双击，选取多个统计指标，比如“求和”、“平均值”等等。完成后点“确定”，重新进入向导步骤之三版面，点“完成”在跳出的工具栏条中选取

17、“设置报告格式”，在跳出的报告格式中选定一个格式，再点“确定”。Excel2003版本：自定义计算版本：自定义计算Excel2007版本：自定义字段版本：自定义字段Excel的窗体控件的用法的窗体控件的用法你们应该或多或少的接触过一些EXCEL做成的调查问卷，或者性格测试之类的，很多时候，这些是通过EXCEL的窗体控件完成的，一般来说，大家在EXCEL中并不常用窗体，但是窗体能给用户提供更多的自定义交互和控制功能，在制作动态图表方面会给我们一些帮助，本节先讲一个用窗体制作的职商测试。后面在讲动态图表的时候再讲讲其在动态图表中的应用，不过原理是相似的。1、打开控件工具箱：视图工具栏窗体2、选择想

18、要加入的窗体拉入到界面中3、特殊窗体需要用组合框进行组合（如选项按钮、复选框等）4、布局完成之后就要做相应数据的指定，窗体右键，然后再数据编辑栏里面输入“=”就可以将窗体控制指定给某个单元格，如输入“=A5”5、之后就可以通过对A5数据的引用来达到由窗体控制的目的当您的文件不想被别人打开时当您的公式不想被别人看到时当您的单元格不想被别人选择时当您的文件格式和布局不想被破坏时捍卫自己的劳动成果运动开始Excel保护与加密保护与加密人非圣贤，孰能无过，有错就改还是好同志，但首先要知道错在哪儿了？-如何看懂ExcelExcel出错信息。#、#DIV/0!、#VALUE!、#REF!、#N/A、#NA

19、ME?、#NUM!、#NULL！是不是很熟悉？如何解决Excel出现的错误。Excel出错信息详解出错信息详解 共享工作簿是指允许网络上的多位用户同时查看和修订的工作簿（说白了就是几个人对同一工作簿同时打开并操作,其好处是不言而喻的）。在【工具】菜单里的共享工作簿子菜单，将编辑选项卡里的“允许多人编辑”勾选上即可多人同时操作工作簿了。技巧技巧1：若要查看另外还有谁打开工作簿，请单击“工具”菜单中的“共享工作簿”，再单击“编辑”选项卡。技巧技巧2：如果希望定期自动更新其他用户的更改并加以保存或不保存，请单击“工具”菜单中的“共享工作簿”，再单击“高级”选项卡，然后在“更新”下，单击所需的选项。技

20、巧技巧3、其他用户未保存前，其对共享工作簿进行的修订不会显示。因而要停止共享工作簿必须请所有其他用户保存并关闭共享工作簿。否则，他们未保存的数据将会丢失。在向共享工作簿保存更改时，正在编辑该工作簿的其他人员可能已经将自己的更改保存在了同一个单元格中。这种情况下就会产生修订冲突，这时将出现冲突解决对话框以便决定保存哪个人的更改。Excel共享工作簿共享工作簿Excel Excel 函数用法函数用法目录目录 函数及公式概念介绍 函数的综合应用举例 常用函数表达式请点击请点击请点击 自定义函数 请点击什么是函数什么是函数Excel Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式

21、。函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。以求和函数SUM 为例，它的语法是SUM(number1,number2,.)”。其中“SUM”称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。参数参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。假如一个函数可以使用多个参数，那么参数与参数之间使用半角逗号进行分隔。参数可以是常量、逻辑值(TRUE 或FALSE)、数组、错误值(如#N/A)或单元格引用(如E1:H1)，甚至可以是另一个或几个函数甚至可以是另一个

22、或几个函数等等按照函数的来源，Excel 函数可以分为内置函数和扩展函数两大类。什么是公式什么是公式函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的公式。以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数，“A1”则是对单元格A1 的引用(使用其中存储的数据)，“26”则是常量，“*”和“+”则是算术运算符(另外还有比较运算符、文本运算符和引用运算符)。如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等

23、号，另一个则是函数本身。如果函数作为参数形式出现则不需要。函数中的“引用”单元格引用单元格引用是函数中最常见的参数，引用的目的在于标识工作表单元格或单元格区域，并指明公式或函数所使用的数据的位置，便于它们使用工作表各处的数据，或者在多个函数中使用同一个单元格的数据。还可以引用同一工作簿不同工作表的单元格，甚至引用其他工作簿中的数据。根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们把引用分为相对引用相对引用、绝对绝对引用引用和混合引用混合引用三种类型。以存放在F2 单元格中的公式“=SUM(A2:E2)”为例，当公式由F2 单元格复制到F3 单元格以后，公式中的引用也会变化为“=S

24、UM(A3:E3)”。若公式自F 列向下继续复制，“行标”每增加1 行，公式中的行标也自动加1。如果上述公式改为“=SUM($A$3:$E 3)”，则无论公式复制到何处，其引用的位置始终是“A3:E3”区域。F4键可以在绝对引用和相对键可以在绝对引用和相对引用之间进行转换。引用之间进行转换。混合引用混合引用有“绝对列和相对行”，或是“绝对行和相对列”两种形式。前者如“=SUM($A3:$E3)”，后者如“=SUM(A$3:E$3)”。想让谁不动就在谁前面加想让谁不动就在谁前面加“$”即可。即可。三维引用三维引用：上面所讲的都是同一工作表中的数据，如果要分析同一工作簿中多张工作表上的数据，就要使

25、用三维引用。三维引用中不仅包含单元格或区域引用，还要在前面加上带“!”的工作表名称。假如你要引用的数据来自另一个工作簿，如工作簿Book1中的SUM 函数要绝对引用工作簿Book2 中的数据，其公式为“=SUM(Book2Sheet1!SA S1:SA S8,Book2Sheet2!SB S1:SB S9)”，也就是在原来单元格引用的前面加上“Book2Sheet1!”。放在中括号里面的是工作簿名称，带“!”的则是其中的工作表名称。即是跨工作簿引用单元格或区域时，引用对象的前面必须用“!”作为工作表分隔符，再用中括号作为工作簿分隔符。Excel常用函数用法常用函数用法Excel自定义函数自定义

26、函数函数及常用技巧应用测试题函数及常用技巧应用测试题ExcelExcel 图表魅力图表魅力目录目录 动态图表请点击 普通图表请点击普通图表的制作普通图表的制作操作：单击常用工具栏上的“图表”按钮,或在“插入”菜单中单击“”或【插入】菜单图表，根据图表向导的提示，可完成简单的图表设置。通常对系统生成的图表还有一些需要修改的地方，常用的有以下操作(1)更改图表数值(2)在图表上同样可以修改数据点的值。(3)向图表中添加数据。(4)从图表中删除数据。(5)更改图表类型(6)设置图表选项(7)设置图表元素的选项“图表向导-4步骤之1-图表类型”对话框。在该对话框的“标准类型”选项卡中，选择要创建的图表

27、类型及子图表类型“图表向导-4步骤之2-图表源数据”对话框。在“数据区域”选项卡中，指定图表的数据区域，并指明数据系列产生在“行”还是产生在“列”在“系列”选项卡中，可以指定系列“名称”、“值”的位置，以及分类轴的标志“图表向导-4步骤之3-图表选项”对话框。在“标题”选项卡中分别输入“图表标题”、“分类（X）轴”及“分类（Y）轴”的标题。在“网格线”选项卡中，选中“分类（X）轴”下的“主要网格线”和“数值（Y）轴”下的“主要网格线”复选框“图表向导-4步骤之4-图表位置”对话框。在“图表位置”对话框中，选择图表插入的位置，选择“作为其中的对象插入”单选按钮。然后在列表框中，选择要插入其中的工

28、作表。如果选择“作为新工作表插入”单选按钮，Excel将自动插入一张图表工作表。这样就完成图表的创建。动态图表动态图表“宏宏”及简单及简单VBAVBA编程编程Excel的宏是由一系列的VB语言代码构成的，因此，如果你熟悉VB语言，可以用它编写为宏，如果用户不熟悉VB语言，可以用记录功能建立宏。实际应用中常常可以将自己编写与记录宏相结合。我这里主要大概讲一下宏录制的过程及方法一、录制宏：1、【工具】宏 记录宏（Alt+F8）2、在对话框中输入宏名。可以使用省略宏名如宏2,也可以在宏名框中键入另一名称。3、若要给出宏的简单说明,在说明框中可键入描述性文字。4、若要设置有关宏的选项,单击选项。5、单

29、击确定,然后执行所记录的操作。6、操作完成之后,在工具菜单中,选择记录宏,右侧的记录新宏命令已变成了停止记录“,单击即可结束。二、执行宏：一个宏建立完毕后就可以执行了,执行宏的方法有多种,下面介绍常用的几种方法。三、编辑和删除宏：一个宏建立之后可能不满意,需要编辑修改或其使命完成之后需要删除，方法如下。1.编辑、修改宏：如果用户对Excel的Visual Basic语言不熟悉,那么最好的方法是重新记录宏,在新建宏时,在“宏名”框中输入原来的名字,单击“确定”,Excel将提示用户是否替换已存在的宏,单击“是”即可。2.删除宏：删除宏的方法很简单,单击菜单条中的工具选项,选择宏命令项,选择要删除

30、的宏,单击删除按钮,就可以删除了。Excel综合应用实例不好的使用习惯将极大地降低你的工作效率，让你事倍功半。下面谈谈本人使用Excel的一些体会。排版时不要在字符串内、前后插入空格，尽量使用对齐功能来排版，以便使用函数查找、引用。在要使用公式的单元格，尽量不使用合并单元格功能（可使用跨列对齐来居中），以便填充公式、排序、筛选。数字及文本的对齐方式尽量统一，保持性美观性习惯使用拆分窗口功能，免除为看到标题行而大量滚屏，以提高工作效率。经常使用快捷键，提高工作效率。在要进行乘除等运算时，要养成在公式的最外层使用round函数的习惯，以免出现四舍五入的误差。在计算结果可能出现#DIV/0!、#VA

31、LUE!、#N/A等错误，而其它单元格又需对此单元格进行运算时，要养成使用if和ISERROR函数消除错误值的习惯。要对工作表进行临时性排序时，养成添加序号列的习惯，以便恢复原来的顺序。当工作表要提交他人（多人）填写时，养成保护工作表、锁定单元格以及对特定单元格设定数据有效性的习惯，以避免他人的错误操作影响报表的汇总。养成使用状态栏进行临时性的求和、计数、求平均值的习惯。使用公式将一堆数据导入到另一个表中时，不论什么情况，都要进行数据的核对！Excel技能的提高，就是多试多用，有懂就要去问去学，有事没事就按F1看Excel的联机帮助，网上有一些非常不错的Excel在线论坛，里面高手非常多，你有什么不论多么变态的问题，几乎都会有高手帮你解决！推荐以下三个：学习学习Excel之感想之感想!Questioning 欢迎交流：欢迎交流：Email： Tel：8257 MSN： 问题探讨问题探讨