EXCEL操作技巧讲课讲稿.doc

《EXCEL操作技巧讲课讲稿.doc》由会员分享，可在线阅读，更多相关《EXCEL操作技巧讲课讲稿.doc（79页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Good is good, but better carries it.精益求精，善益求善。EXCEL操作技巧-德赛集团内部培训手册（一）Excel2002笑傲红尘客银河系出版社广东*惠州2006*08-2006*10序言5关于本书5Excel5第1章基本操作61.1文件61.1.1另存为61.1.2页面设置61.1.2.1页面61.1.2.2页边距61.1.2.3页眉页脚71.1.2.4工作表:81.1.3属性：81.1.4打印区域81.2编辑81.2.1选择性粘贴81.2.2粘贴为超链接91.2.3移动或复制工作表91.2.4查找（替换）91.2.5定位101.2.6链接101.3视图11

2、1.3.1分页预览111.3.2任务窗格111.3.3自定义工具栏111.4插入121.4.1名城121.4.2批注131.4.3对象141.4.4超链接141.5格式151.5.1单元格151.5.1.1数字151.5.1.2对齐191.5.1.3字体191.5.1.4边框191.5.1.5图案191.5.1.6保护191.5.2工作表201.5.3自动套用格式201.5.4条件格式201.6工具211.6.1保护211.6.2宏211.6.3加载宏221.6.4自动更正选项221.6.5自定义221.6.6选项231.6.6.1视图231.6.6.2重新计算231.6.6.3编辑241.6

3、.6.4常规241.6.6.51-2-3的帮助：241.6.6.6自定义序列：251.6.6.7错误检查251.6.6.8安全性251.6.7数据分析261.7数据261.7.1排序261.7.2筛选261.7.2.1自动筛选264.7.2.2高级筛选261.7.3记录单271.7.4分类汇总281.7.5有效性281.7.6模拟运算表311.7.7分列321.7.8合并计算331.7.9组及分级显示331.7.10数据透视表和数据透视图341.8窗口341.8.1拆分351.8.2冻结351.8.3当前文件351.9帮助36第2章常用函数（公式）372.1函数基础知识372.2常用函数402

4、.2.1常用函数402.2.1.1SUM402.2.2数据库函数：402.2.3查找引用函数402.2.4日期时间函数402.2.5财务会计函数402.2.6信息函数402.3数组402.4宏表函数40第3章图表40第4章窗体和VBA40第5章综合应用案例分析40第6章经典技巧大典40序言关于本书本书给那些经常使用EXCEL，又不熟悉它的强大功能的人准备！本书不讲类如安装，新建，保存等非常基础的东东，如果这些都不会就不要读此书了！本书可以达到的效果是：！Excel第1章基本操作1.1 文件1.1.1另存为为了有效保护文档安全，经常需要设置密码：（OFFICE密码只能放菜鸟）A.文件另存为工具常

5、规选项输入密码确定（要覆盖原文件才能设置成功密码，否则另存为新文件，只能是新文件有密码的）还有一种设置密码方法：B工具选项安全性输入密码确定1.1.2页面设置1.1.2.1页面1. 方向：选择打印的纸张方向2. 缩放：.调整为，意思把多于一页的表格调整为特定页数！此操纵前，最好先把页边距调整好（.文件打印预览页边距）。此功能等同于（视图）分页预览。1.1.2.2页边距1.上下左右，页眉页脚：设置页边距；等同于.文件打印预览页边距2.居中方式：数据（图表）在这个页面里面的居中（水平，垂直）位置，1.1.2.3页眉页脚1.自定义页眉（页脚）：文件页面设置自定义页眉（页脚）从左至右顺序功能01.:设

6、置页眉/页脚文字的属性,例如:字体,字型,大小,下划线,删除线,上标,下标02.页码:当前第几页03.:总页数,所有数据共有几页04.:日期:系统当前日期,是自动更新的05.:时间系统当前时间,是自动更新的06.路径文件:此文件的绝对路径+文件名,例如07.文件:当前文件名08.标签名:当前表名09.:图片:插入图片,一般是企业LOGO或者徽标10.设置:插入图片后,设置图片格式的11(12.13):位置:设置页眉（页脚）在在文件的左,中,右位置实际应用例子:1.要求每张文件右下脚都要有”制表人”,？答：就在上图13”位置直接填入:”制表人:”说明：工作表设置（详见1.1.2.4工作表）打印标

7、题栏只能打印顶端（左端）标题栏，不能打印尾端标题栏；页脚设置可以部分解决这个问题。彻底解决就需要设置复杂的公式，详见CD文件！2.曾经有人问：正常编辑时候是自己要求的数据和图表，但是预览和打印的时候就在页眉，页脚位置出现一些多余的文字和数据，怎么解决？答：看看页眉，页脚的自定义；3.1.1.2.4工作表:1.打印区域:不打印全部文章，只打印部分,等同于.文件打印区域设置打印区域2.打印标题:每页都打印同样的标题!实际应用：如表格很长,要每页都要有标题行,就点击顶端标题行设置即可,左端标题列类似.3.打印：网格线把虚线的网格线都打印出来单色打印彩色的也只是按照黑白打印按草稿方式不打印边框，彩色打

8、印成黑白色行号列标是否打印行号列标批注是否打印批注错误单元格打印为设置错误值打印样式1.1.3属性：要填写标题，主题，作者，类别，关键词，备注这些，便于以后检索.。如果文件繁多，你就喜欢这个功能了。1.1.4打印区域不想打印全部文章，只是打印部分.文件打印区域设置打印区域实践经验：如果遇到一个工作表在编辑时候能看见全部数据图表,但是某些区域，打印预览看不到，也不能打印出来,那取消打印区域可以解决1.2编辑1.2.1选择性粘贴非常有用的功能，一定要学会！只介绍常用功能，其他有兴趣自己动手实践粘贴1. 全部：等同于“粘贴”（Ctrl+V）2. 公式：只复制公式3. 把公式变成数值,复制过来（数值化

9、）4. 格式:只是复制单元格的格式批注：只是复制单元格的批注运算（非常实用的技巧）1.加（减乘除）：用法等同。把大量数据一次性加（减乘除）一个特定数据，应用于固定资产折旧的折旧月数等；把一批数据改变正负；把一批数据一次性变成千，万位单位用法如下：空白单元格填入需要的数值复制选择全部数据鼠标右键（编辑）选择性粘贴选中“数值”，“加（减乘除）”2.跳过空单元不复制空单元格3.“转置”粘贴数据时选中，把复制的行数据变成列数据了，反之亦然！（非常实用的技巧）4.粘贴连接和源数据（一般是本文件）产生连接,目标数据可以随着源数据改变而更新1.2.2粘贴为超链接和源数据（外部文件，一般是OFFICE文件）产

10、生连接,目标数据可以随源数据改变而自动更新。适用于做套表1.2.3移动或复制工作表1. 工作簿：把选定工作表移至（剪切）当前打开的某个excel文件2. 下列选定工作表之前：把选定工作表移至（剪切）某个表前面建立副本：选中就不是移至（剪切），而是复制1.2.4查找（替换）1格式：可以按照特定格式查找（替换）2范围：可在当前工作表，当前整个文件查找例子：1. 想把表内单元格值为0的为改为空格按ctrl+H替换查找内容输入0替换为空全部替换。这样做101变成11,1000001也会变成11，不是我们要的结果！2. 正确做法:要勾选单元格匹配,只替换0值的单元格查找(替换)特殊字符:*?(非常重要的

11、知识)如果对类似01011061*A这样带有*的数据处理会出错的,原因就是在Excel里面*表示所有数据,?表示一个数据(字符).A.如果处理带有*，?的数据,一定先要把*（?）换成其他字符，如:+,-,.;处理完数据再把*（?）还原B.怎样查找（替换）*(?):编辑查找（替换）输入*(以*代替*,在Esc键下边).如图注意:1.以上字符都是在拼音状态下2.查找范围:要选择数值还是公式视情况定3.如果是查找一定的区域，要先用鼠标选择那个区域，查找前鼠标的位置有1.2.5定位.编辑定位定位条件1. 批注选择当前表里面所有的批注2. 常量选择当前表里面所有的常量3. 公式选择当前表里面所有公式计算

12、得出的数字，文本，逻辑值，错误值4. 空值选择当前表里面所有的空白值.实际应用：我们经常需要更改（删除）一个有大量数据的表里面的特定数据，里面的数据有公式，文本数值，逻辑值，数字，错误值，空值但是数据没有规律，一个个处理很是耗费时间的，容易出错，可能遗漏，怎么解决？例如：要把表里面的错误值全部去掉（指公式计算出来的错误值，如果单元格里面的数据就是类似#DIV/0!这样子的错误文本，使用查找，替换可以解决；详见1.2.4查找），答：选择数据区（表格区域）.编辑定位定位条件公式选中错误1.2.6链接当文件有外部文件的链接，此功能才能用！1.更新值：更新链接的数据2.更改源：更改数据的链接源3.打开

13、源文件：打开引用的源文件4.断开链接：断开源文件的的链接，直接数值化；如果不断开，在源文件更改位置（删除）后，无法更新，将出现错误值5.检查状态：检查源文件是否可以更新6.启动提示：设置打开excel文件时候的更新属性注意：工作中，我经常遇到xp版本的excel自动更新链接的情况，即使在是否更新对话框选择不更新也不行！所以强烈建议：做好表格后，把有外部文件链接的数据全部数字化1.3视图1.3.1分页预览视图分页预览是（页面缩放）调整为的图形效果！此操纵前，最好先把页边距调整好，直接用鼠标拉动蓝色虚线调整！蓝色实线是数据区，蓝色虚线是打印线1.3.2任务窗格.视图任务窗格点击后左侧将出现任务窗格

14、1新建工作簿：选择新建一个指定类型工资簿2.剪贴板：处理复制到系统剪贴板的文字图片等3.搜索：搜索工作簿内容4.插入剪贴画：插入系统自带的剪贴画或者硬盘里面的图片1.3.3自定义工具栏我们每个人的工作不同，习惯不同；所以使用excel方式一定不同，为了更快的提高工作效率，自己打造一个符合自己习惯的工具条；其实还有很多实用的功能，系统工具条没有体现出来，下面是操纵步骤：打开自定义窗口：视图工具栏自定义：（工具自定义）还可以在工具条空白区域点击鼠标右键调出自定义窗口创建自定义工具条第一步：工具栏新建弹出新建工具栏输入名城第二步：点击命令在类别选择需要的分类菜单选中命令里面自己需要的命令按住鼠标左键

15、拖到第一步新建的工具条松开鼠标，完成后见下图：编辑自定义工具条1.在打开自定义窗口情况下才能编辑。在要编辑的按钮上点击鼠标右键，如下窗口2.常用命令如下：复位按钮图像：回复原始按钮图像编辑按钮图像：自己用鼠标画图更改按钮图像：选择系统提供的图像更改默认样式：按钮外观有图无文字总是用文字：文字图像与文本：图像文字混用做成自己特有的工具条，如图：1.4插入1.4.1名城名称定义（ctrl+F3）1.名称:为了更加直观地标识单元格或单元格区域,我们可以给它们赋予一个名称,单元格区域,鼠标单击编辑栏顶端的“名称框”,在其中输入名称后回车.也可以选中要命名的单元格或单元格区域,单击“插入名称定义”命令,

16、在打开的“定义名称”对话框中输入名称后确定即可.如果你要删除已经命名的区域,可以按相同方法打开“定义名称”对话框,选中你要删除的名称点击删除即可.1.输入名称:中文,英文皆可2.引用位置是单元格,或者公式函数,宏表函数3.只有点击确定,或者添加,才有效1.4.2批注一、批注是附加在单元格中，与其他单元格内容分开的注释。批注是十分有用的提醒方式，例如注释复杂的公式如何工作，或为其他用户提供反馈。查看批注内容MicrosoftExcel提供了几种查看批注的方法。含有批注的单元格的右上角有三角形的批注标识符。如果指针停在含有标识符的单元格上，就会显示该单元格的批注。您也可以连续地显示批注（单条批注或

17、是工作表上的所有批注）。“审阅”工具栏能使您按顺序逐条查看每项批注。您还可以将批注打印到工作表的相应位置，或在打印输出结果的底部将批注打印成列表。批注中的用户姓名按用户的姓名来区分批注，就可以在共享工作簿或传送给几个用户的工作簿中，判断出键入每项批注的操作者。当合并几个工作簿的更改时，批注也被合并，这样单元格中所有的批注文字就会按顺序显示。二、为单元格添加批注批注是为单元格键入的注释。单击要加批注的单元格。在“插入”菜单中，单击“批注”。在弹出的批注框中键入批注文本。如果不想在批注中留有您的姓名，请选择并删除姓名。完成文本键入后，请单击批注框外部的工作表区域。三、调整批注大小或移动批注在需要更

18、改批注的单元格上单击鼠标右键，在随后出现的快捷菜单中单击“显示批注”。单击批注框的边框，显示尺寸控点。请执行下列操作之一：更改尺寸拖动批注框四边或角点上的尺寸控点。移动批注拖动批注框的边框。如果隐藏并重新显示批注，它将返回到其默认位置。批注中的默认用户名可以在工具选项常规中修改批注的默认字体、字体大小、颜色可以在“windows外观”中修改“工具提示”的相应内容1.4.3对象插入对象office文件都可以插入其他各种类型的文件。新建是新建的一个空白的其他各种类型文件显示为图标：在excel里面是否以文件图标形式显示由文件创建直接把外部做好的文件插到office文件里面链接到文件：与文件同步更新

19、1.4.4超链接插入超链接要显示的文字：在excel单元格里面显示的文字查找范围：超链接文件在本地硬盘的位置地址：互联网地址书签：等同于本文档中的位置，直接链接到当前工作表的某个单元格，链接到其他表，链接到事先定义好的书签（名称）1.5格式1.5.1单元格1.5.1.1数字在Excel中使用自定义格式功能：先选中数据区格式单元格数字自定义见Error! No bookmark name given.，自定义格式的都可删除）图表1.5.Error! No sequence specified.#好象是格式在位符，0表示数位站位符，如设置单元格式为“000”，则输入数字1时，显示为“001”。？一

20、般表示一个字节位置。Excel的自定义格式使用下面的通用模型：正数格式；负数格式；零格式；文本格式。在这个通用模型中，包含三个数字段和一个文本段：大于零的数据使用正数格式；小于零的数据使用负数格式；等于零的数据使用零格式；输入单元格的正文使用文本格式。我们还可以通过使用条件测试，添加描述文本和使用颜色来扩展自定义格式通用模型的应用。1.使用颜色要在自定义格式的某个段中设置颜色，只需在该段中增加用方括号括住的颜色名或颜色编号。Excel识别的颜色名为：黑色、红色、白色、蓝色、绿色、青色和洋红。Excel也识别按颜色X指定的颜色，其中X是1至56之间的数字，代表56种颜色。2.添加描述文本要在输入

21、数字数据之后自动添加文本，使用自定义格式为：文本内容；要在输入数字数据之前自动添加文本，使用自定义格式为：文本内容。符号的位置决定了Excel输入的数字数据相对于添加文本的位置。3.创建条件格式可以使用六种逻辑符号来设计一个条件格式：（大于）、=（大于等于）、（小于）、=（小与等于）、=（等于）、（不等于）。由于自定义格式中最多只有3个数字段，Excel规定最多只能在前两个数字段中包括2个条件测试，满足某个测试条件的数字使用相应段中指定的格式，其余数字使用第3段格式。如果仅包含一个条件测试，则要根据不同的情况来具体分析。如下列自定义格式：红色；绿色；蓝色；：大于0的数字显示红色，负数显示绿色，

22、负数显示蓝色。红色0正数格式；0负数格式；零格式；文本格式。另外，我们还可以运用自定义格式来达到隐藏输入数据的目的，；0只显示负数和零，输入的正数则不显示；格式；则隐藏所有的输入值。自定义格式只改变数据的显示外观，并不改变数据的值，也就是说不影响数据的计算。#,0,?这三个字符都是数字占位符，区别在于：对于#，系统会自动忽略数字前后的0值，如：格式设置为：#.#，对于0103.50则只显示103.5；对于0，系统直接用0来补位，如：0.00，这也是常用的，如输入103.5则显示103.50；如格式设置为：00000，输入50则显示成00050，这种也是常用的编号设置法；对于?，它除了具有#号功

23、能外(去除前后的无效的0值)，还多了一个空格占位的功能，常用于数值小数点对齐，如：格式设置为：?.?，当输入0103.50或103.5则系统显示为103.5_(注：为了便于理解用_来表示一个空格占位)。补充说明：在Excel自定义数字格式使用如下的表达方式：正数的格式负数的格式零的格式文本的格式#,#0.00Red-#,#0.000.00TEXT在Excel自定义数字格式的格式代码中，用户最多可以指定四个节；每个节之间用分号进行分隔，这四个节顺序定义了格式中的正数、负数、零和文本。如果用户在表达方式中只指定两个节，则第一部分用于表示正数和零，第二部分用于表示负数。如果用户在表达方式中只指定了一

24、个节，那么所有数字都会使用该格式。如果在表达方式中要跳过某一节，则对该节仅使用分号即可。#：数字占位符(非0值占位符)G/通用格式：用常规格式显示数字：文本占位符*：重复下一个字符以充满列宽，：千位分隔符:用这种格式显示下一个字符?:数字占位符实例一：要求数值后面带单位（个，元，），数值还可以进行数学运算自定义类型输入数值格式，在后面再输入引号（一定要拼音状态下），引号里面添加所需要的文字实例二：自定义类型自定义格式单元格输入显示为解释使用文本#,#0元人民币10001,000元人民币输入数字，自动加入后缀,数值可运算答复:通用格式1000答案:1000只需输入数字，自动加入前缀,数值可运算总

25、额是#,#0美元1000总额是1,000美元只需输入数字，自动加入前缀和后缀,数值可运算分隔长数字#,#0,123456789123,457因为#,#中有千位分隔符，所以返回的数据保留分隔符；格式中最后用千位分隔符，所以数据显示时，千位后自动四舍五入了，实际数值没有变，#,#0,1234567891212,345,679#,#0,12341数据确认0.00;仅显示正数!123123.00如果是负数，仅显示后面的描述，实际数值没有变。（表示正数格式的第一个节正常书写，表示负数格式的第二个节只用了描述）0.00;仅显示正数!-123仅显示正数!0.00;仅显示正数!00.000.00;仅显示正数!

26、123.56444123.560用破折号#,#0_);(#,#0);-0-_)0-0-值为0时左右有“”#,#0_);(#,#0);-0-_)12.212值为正时常规显示#,#0_);(#,#0);-0-_)-102(102)值为负时用括号括起来电话号码(#)#-#8523776596295(852377)659-6295#/#-#8523776596295852377/659-6295日期格式mmmm-yy1995-6-1June-95mmmmd,yyyy1995-6-1June1,1995dddd1995-6-1Thursdaymmmmd,yyyy(dddd)1995-6-1June1,1

27、995(Thursday)Itsdddd1995-6-1ItsThursday设置不同颜色红色=1#,#0;G/通用格式11红色=1#,#0;G/通用格式-1-100.0%红色=1#,#0;G/通用格式4545G/通用格式;G/通用格式;G/通用格式;红色G/通用格式德赛电子(惠州)有限公司德赛电子(惠州)有限公司最后一个节规定了文本的颜色G/通用格式;G/通用格式;G/通用格式;红色G/通用格式234234其他符号G/通用格式20042004#-#-#421897322421-89-7322社会福利号#-#-#421897322社会福利号421-89-7322=C113-TODAY()200

28、6-12-10已过期100天!描述正数;负数;零;文本32正数仅以文字的格式表示原来的数，不影响D53至D56单元格中数值，可看后面的运算就知道。正数;负数;零;文本-32负数正数;负数;零;文本0零正数;负数;零;文本德赛电子文本隐藏;1234隐藏单元格内容，但编辑栏里还能看到的。不影响引用，运算。另一种隐藏是把字体设为白色。这两种方法都是表面的隐藏，都不能打印。;-145条件语句100#,000;99有要求的隐藏，大于100的数值显示，否则都不显示。其余同上。100#,000;102102100#,000;-54100#,000;德赛电子1.5.1.2对齐单元格格式的对齐一般功能很简单，如

29、果做些复杂可参考见CD例子1.5.1.3字体功能常见，不再罗嗦1.5.1.4边框常用功能！此功能与其他操做结合，可以发挥意想不到的效果，可参考CD例子1.5.1.5图案此功能多数是与其他操做结合使用，可参考CD例子1.5.1.6保护非常有用的功能，尤其对于做汇总表的人！我们经常遇到这种情况：你辛辛苦苦做成一个表格，发给别人填写，别人给你乱改表格！本来很简单的汇总数据的工作将变得很繁琐，下面的例子将彻底解决你的烦恼！保护的效果：保护自己的数据表结构不被修改，公式内容不被看见；需要输入数据的区域可以自由编辑，否则不可改动！方法1.保护(隐藏)表格公式不被修改(看见),而数据区却可以修改.解决如下:

30、方法1.选中表的所有区域鼠标右键”设置单元格格式”保护”取消”锁定”,”隐藏”选中公式区域鼠标右键”设置单元格格式”保护”选中”锁定”,”隐藏”工具”保护”保护工作表”输入”密码”确定”方法2.(适合复杂大表)说明：1.浅色（黄色）是标题栏2.深色（绿色）是公式区3.中间（白色）是数据区4.要求数据区可以输入修改数据，标题栏只能浏览，不可修改；公式区能看见结果，不能修改公式，不能看见公式。做法：1、全选工作表单元格格式保护取消锁定。2、编辑定位定位条件选公式按确定3、选定要保护的单元格，单元格格式保护勾选锁定、隐藏。4、工具保护工作表选定锁定的内容输入密码。1.5.2工作表1.重命名：更改工作

31、表名字2.隐藏：隐藏工作表3.背景：为空白背景的工作表添加漂亮的图片4.工作表标签颜色：更改工作表标签的颜色，便于工作区分1.5.3自动套用格式不常用，也不实用的！略去。可参考CD例子1.5.4条件格式重复数据设置（非常实用的技巧）：方法一.第二个及以上重复数据标注特定格式=COUNTIF($D$1:D1,D1)1（针对D列,数据是从D1开始的）=COUNTIF($A$97:A97,A97)1(针对A列,数据是从A97开始的)方法一操作：:选中数据区:格式条件格式（窗口）.在条件上选择公式输入方法一的公式：=COUNTIF($A$97:A97,A97)1)设置单元格格式注意：1.IF语句是屏蔽

32、了都是0时,条件格式无效；2.注意公式内容和数据区第一行是关联的；3.注意锁定符号位置方法二.把所有重复数据设置特定格式=COUNTIF(D:D,D1)1（针对D列,数据是从D1开始的）=COUNTIF(A:A,A97)1(针对A列,数据是从A97开始的)方法二操作：选中数据区格式条件格式.选择公式.输入方法二的公式3.设置单元格格式注意公式内容和方法一是不同的说明：1. 有人说可以用排序啊，问题是excel排序只能支持1000行数据，超过了排序就无效！2. 如果是剔除重复数据，选出不重复唯一值，可参见1.7.2高级筛选如果是选出重复数据，参见第二章函数判断单元格的值设置1.判断错误值=isE

33、RROR(A1)红色：如果是错误值，单元格填充红色2.判断文本=isTEXT(A1)黄色：如果是文本，单元格填充黄色3.判断数值=isNUMBER(A1)蓝色如果是数值，单元格填充蓝色说明：主要利用is类函数，具体操纵和上面的设置重复值类似，不再累述！以其他单元格为条件，设置当前单元格要求：客户名城单元格格式随着订单值变化，怎么做？选中数据区（客户栏）格式条件格式.选择公式.输入公式（“E196=38000,B3=38500)其中的38000和38500可以改为实际需要的日期系列数，CELL函数是判别数字格式用的。注意，你必须输入2004-1-14至2005-5-28之间的日期，另外，你输入日期的方式要正确。有效性不能限制数据粘贴，系统会将以原单元格的格式来覆盖当前格式。1.7.6模拟运算表1.7.7分列