德赛集团内部培训手册 excel2002.doc

基本操作德赛集团内部培训手册（一）Excel 2002笑傲红尘客 银河系出版社广东*惠州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视图111.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.6.4常规241.6.6.5 1-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.2.1常用函数402.2.1.1 SUM402.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 .文件 另存为 工具 常规选项输入密码 确定（要覆盖原文件才能设置成功密码，否则另存为新文件，只能是新文件有密码的）还有一种设置密码方法：B 工具 选项 安全性 输入密码 确定 1.1.2页面设置1.1.2.1页面1. 方向：选择打印的纸张方向2. 缩放：.调整为，意思把多于一页的表格调整为特定页数！此操纵前，最好先把页边距调整好 （.文件 打印预览 页边距）。此功能等同于（ 视图 ） 分页预览。1.1.2.2页边距1.上下左右，页眉页脚：设置页边距；等同于 .文件 打印预览 页边距2.居中方式：数据（图表）在这个页面里面的居中（水平，垂直）位置，1.1.2.3页眉页脚1.自定义页眉（页脚）：文件 页面设置自定义页眉（页脚）从左至右顺序功能01.: 设置页眉/页脚文字的属性,例如:字体,字型,大小,下划线,删除线,上标,下标02.页码:当前第几页03.:总页数,所有数据共有几页04.:日期:系统当前日期,是自动更新的05.:时间系统当前时间, 是自动更新的06.路径文件:此文件的绝对路径+文件名,例如07.文件:当前文件名08.标签名:当前表名09.:图片:插入图片,一般是企业LOGO 或者徽标10.设置:插入图片后,设置图片格式的11(12.13):位置: 设置页眉（页脚）在在文件的左,中,右位置实际应用例子:1.要求每张文件右下脚都要有”制表人”,？ 答：就在上图 13”位置直接填入:”制表人:” 说明： 工作表设置（详见1.1.2.4 工作表）打印标题栏只能打印顶端（左端）标题栏，不能打印尾端标题栏；页脚设置可以部分解决这个问题。彻底解决就需要设置复杂的公式，详见CD文件！2.曾经有人问：正常编辑时候是自己要求的数据和图表，但是预览和打印的时候就在页眉，页脚位置出现一些多余的文字和数据，怎么解决？ 答： 看看页眉，页脚的自定义； 3.1.1.2.4工作表:1.打印区域: 不打印全部文章，只打印部分,等同于 .文件 打印区域 设置打印区域2.打印标题:每页都打印同样的标题!实际应用：如表格很长,要每页都要有标题行,就点击顶端标题行设置即可,左端标题列类似.3.打印：网格线 把虚线的网格线都打印出来单色打印 彩色的也只是按照黑白打印按草稿方式 不打印边框，彩色打印成黑白色行号列标 是否打印行号列标批注 是否打印批注错误单元格打印为 设置错误值打印样式1.1.3属性：要填写标题，主题，作者，类别，关键词，备注这些，便于以后检索.。如果文件繁多，你就喜欢这个功能了。1.1.4打印区域不想打印全部文章，只是打印部分.文件 打印区域 设置打印区域实践经验：如果遇到一个工作表在编辑时候能看见全部数据图表,但是某些区域，打印预览看不到，也不能打印出来,那取消打印区域可以解决1.2编辑1.2.1选择性粘贴非常有用的功能，一定要学会！只介绍常用功能，其他有兴趣自己动手实践粘贴1. 全部：等同于“粘贴”（Ctrl+V）2. 公式：只复制公式3. 把公式变成数值,复制过来（数值化）4. 格式:只是复制单元格的格式5. 批注：只是复制单元格的批注运算（非常实用的技巧）1.加（减乘除）：用法等同。把大量数据一次性加（减乘除）一个特定数据，应用于 固定资产折旧的折旧月数等；把一批数据改变正负；把一批数据一次性变成千，万位单位用法如下：空白单元格填入需要的数值 复制 选择全部数据 鼠标右键（编辑）选择性粘贴 选中 “数值”，“加（减乘除）”2.跳过空单元不复制空单元格3.“转置” 粘贴数据时选中，把复制的行数据变成列数据了，反之亦然！（非常实用的技巧）4.粘贴连接 和源数据（一般是本文件）产生连接,目标数据可以随着源数据改变而更新1.2.2粘贴为超链接和源数据（外部文件，一般是OFFICE文件）产生连接,目标数据可以随源数据改变而自动更新。适用于做套表1.2.3移动或复制工作表1. 工作簿： 把选定工作表移至（剪切）当前打开的某个excel文件2. 下列选定工作表之前：把选定工作表移至（剪切） 某个表前面3. 建立副本： 选中就不是 移至（剪切），而是复制1.2.4查找（替换）1格式：可以按照特定格式查找（替换）2范围：可在当前工作表，当前整个文件查找例子：1. 想把表内单元格值为0的为改为空格按ctrl+H替换查找内容输入0替换为空全部替换。这样做 101 变成 11,1000001 也会变成 11，不是我们要的结果！正确做法:要勾选单元格匹配,只替换0值的单元格2. 查找(替换)特殊字符: * ? (非常重要的知识)如果对类似01011061*A这样带有 * 的数据处理会出错的,原因就是在Excel里面 * 表示所有数据, ? 表示一个数据(字符). A.如果处理带有 * ， ? 的数据,一定先要把 * （ ? ）换成其他字符，如: +,-,. ; 处理完数据再把 * （ ? ）还原 B.怎样查找（替换） * ( ? ) :编辑查找（替换）输入*(以*代替*, 在Esc键下边).如图注意 :1.以上字符都是在拼音状态下2.查找范围:要选择数值还是公式视情况定3.如果是查找一定的区域，要先用鼠标选择那个区域，查找前鼠标的位置有1.2.5定位.编辑 定位 定位条件 1. 批注 选择当前表里面所有的批注2. 常量 选择当前表里面所有的常量3. 公式 选择当前表里面所有公式计算得出的数字，文本，逻辑值，错误值4. 空值 选择当前表里面所有的空白值.实际应用：我们经常需要更改（删除）一个有大量数据的表里面的特定数据，里面的数据有公式，文本数值，逻辑值，数字，错误值，空值 但是数据没有规律，一个个处理很是耗费时间的，容易出错，可能遗漏，怎么解决？例如：要把表里面的错误值全部去掉（指公式计算出来的错误值，如果单元格里面的数据就是类似 #DIV/0! 这样子的错误文本，使用查找，替换可以解决；详见1.2.4查找）， 答：选择数据区（表格区域） .编辑 定位 定位条件 公式 选中错误 1.2.6链接当文件有外部文件的链接，此功能才能用！1.更新值：更新链接的数据2.更改源：更改数据的链接源3.打开源文件：打开引用的源文件4.断开链接：断开源文件的的链接，直接数值化；如果不断开，在源文件更改位置（删除）后，无法更新，将出现错误值5.检查状态：检查源文件是否可以更新6.启动提示：设置打开excel文件时候的更新属性注意：工作中，我经常遇到xp版本的excel自动更新链接的情况，即使在 是否更新对话框 选择不更新 也不行！所以强烈建议：做好表格后，把有外部文件链接的数据全部数字化1.3视图1.3.1分页预览视图 分页预览 是（页面 缩放 ）调整为 的图形效果！此操纵前，最好先把页边距调整好，直接用鼠标拉动蓝色虚线调整！蓝色实线是数据区，蓝色虚线是打印线1.3.2任务窗格.视图 任务窗格 点击后左侧将出现任务窗格1新建工作簿：选择新建一个指定类型工资簿2.剪贴板：处理复制到系统剪贴板的文字图片等3.搜索：搜索工作簿内容4.插入剪贴画：插入系统自带的剪贴画或者硬盘里面的图片1.3.3自定义工具栏我们每个人的工作不同，习惯不同；所以使用excel方式一定不同，为了更快的提高工作效率，自己打造一个符合自己习惯的工具条；其实还有很多实用的功能，系统工具条没有体现出来，下面是操纵步骤：打开自定义窗口：视图 工具栏 自定义：（工具 自定义）还可以在工具条空白区域点击鼠标右键调出自定义窗口创建自定义工具条第一步：工具栏 新建 弹出 新建工具栏 输入 名城第二步：点击命令 在类别选择需要的分类菜单 选中命令里面自己需要的命令 按住鼠标左键 拖到第一步新建的工具条 松开鼠标，完成后见下图：编辑自定义工具条1.在打开自定义窗口情况下才能编辑。在要编辑的按钮上点击鼠标右键，如下窗口2.常用命令如下：复位按钮图像：回复原始按钮图像编辑按钮图像：自己用鼠标画图更改按钮图像：选择系统提供的图像更改默认样式：按钮外观有图无文字总是用文字：文字图像与文本：图像文字混用做成自己特有的工具条，如图：1.4插入1.4.1名城名称定义（ ctrl+F3）1.名称:为了更加直观地标识单元格或单元格区域,我们可以给它们赋予一个名称,单元格区域,鼠标单击编辑栏顶端的“名称框”,在其中输入名称后回车.也可以选中要命名的单元格或单元格区域,单击“插入名称定义”命令,在打开的“定义名称”对话框中输入名称后确定即可.如果你要删除已经命名的区域,可以按相同方法打开“定义名称”对话框,选中你要删除的名称点击删除即可.1.输入名称:中文,英文皆可2.引用位置是单元格,或者公式函数,宏表函数3.只有点击确定,或者添加,才有效1.4.2批注一、批注是附加在单元格中，与其他单元格内容分开的注释。批注是十分有用的提醒方式，例如注释复杂的公式如何工作，或为其他用户提供反馈。查看批注内容 Microsoft Excel 提供了几种查看批注的方法。含有批注的单元格的右上角有三角形的批注标识符。如果指针停在含有标识符的单元格上，就会显示该单元格的批注。您也可以连续地显示批注（单条批注或是工作表上的所有批注）。“审阅”工具栏能使您按顺序逐条查看每项批注。您还可以将批注打印到工作表的相应位置，或在打印输出结果的底部将批注打印成列表。批注中的用户姓名 按用户的姓名来区分批注，就可以在共享工作簿或传送给几个用户的工作簿中，判断出键入每项批注的操作者。当合并几个工作簿的更改时，批注也被合并，这样单元格中所有的批注文字就会按顺序显示。二、为单元格添加批注批注是为单元格键入的注释。单击要加批注的单元格。 在“插入”菜单中，单击“批注”。 在弹出的批注框中键入批注文本。如果不想在批注中留有您的姓名，请选择并删除姓名。完成文本键入后，请单击批注框外部的工作表区域。三、调整批注大小或移动批注在需要更改批注的单元格上单击鼠标右键，在随后出现的快捷菜单中单击“显示批注”。 单击批注框的边框，显示尺寸控点。请执行下列操作之一：更改尺寸 拖动批注框四边或角点上的尺寸控点。移动批注 拖动批注框的边框。如果隐藏并重新显示批注，它将返回到其默认位置。批注中的默认用户名可以在 工具选项常规 中修改批注的默认字体、字体大小、颜色可以在“windows外观”中修改“工具提示”的相应内容1.4.3对象插入 对象 office文件都可以插入其他各种类型的文件。新建 是新建的一个空白的其他各种类型文件显示为图标：在excel里面是否以文件图标形式显示由文件创建 直接把外部做好的文件插到office文件里面链接到文件：与文件同步更新1.4.4超链接插入 超链接 要显示的文字：在excel单元格里面显示的文字查找范围：超链接文件在本地硬盘的位置地址：互联网地址书签：等同于本文档中的位置 ，直接链接到当前工作表的某个单元格，链接到其他表，链接到事先定义好的书签（名称）1.5格式1.5.1单元格1.5.1.1数字在Excel中使用自定义格式功能 ：先选中数据区 格式 单元格 数字 自定义 见图表1.5. 1，自定义格式的都可删除）图表1.5. 1 # 好象是格式在位符，0表示数位站位符，如设置单元格式为“000”，则输入数字1时，显示为“001”。？一般表示一个字节位置。 Excel的自定义格式使用下面的通用模型：正数格式；负数格式；零格式；文本格式。在这个通用模型中，包含三个数字段和一个文本段：大于零的数据使用正数格式；小于零的数据使用负数格式；等于零的数据使用零格式；输入单元格的正文使用文本格式。 我们还可以通过使用条件测试，添加描述文本和使用颜色来扩展自定义格式通用模型的应用。 1.使用颜色要在自定义格式的某个段中设置颜色，只需在该段中增加用方括号括住的颜色名或颜色编号。Excel识别的颜色名为：黑色、红色、白色、蓝色、绿色、青色和洋红。Excel也识别按颜色X指定的颜色，其中X是1至56之间的数字，代表56种颜色。 2.添加描述文本要在输入数字数据之后自动添加文本，使用自定义格式为：文本内容；要在输入数字数据之前自动添加文本，使用自定义格式为：文本内容。符号的位置决定了Excel输入的数字数据相对于添加文本的位置。 3.创建条件格式可以使用六种逻辑符号来设计一个条件格式：（大于）、=（大于等于）、（小于）、=（小与等于）、=（等于）、（不等于）。 由于自定义格式中最多只有3个数字段，Excel规定最多只能在前两个数字段中包括2个条件测试，满足某个测试条件的数字使用相应段中指定的格式，其余数字使用第3段格式。如果仅包含一个条件测试，则要根据不同的情况来具体分析。如下列自定义格式：红色；绿色；蓝色； ：大于0的数字显示红色，负数显示绿色，负数显示蓝色。 红色0正数格式；0负数格式；零格式；文本格式。另外，我们还可以运用自定义格式来达到隐藏输入数据的目的， ；0 只显示负数和零，输入的正数则不显示；格式 ； 则隐藏所有的输入值。自定义格式只改变数据的显示外观，并不改变数据的值，也就是说不影响数据的计算。 #,0,? 这三个字符都是数字占位符，区别在于：对于#，系统会自动忽略数字前后的0值，如：格式设置为： #.# ，对于0103.50则只显示103.5；对于0，系统直接用0来补位，如：0.00，这也是常用的，如输入103.5则显示103.50；如格式设置为：00000，输入50则显示成00050，这种也是常用的编号设置法；对于?，它除了具有#号功能外(去除前后的无效的0值)，还多了一个空格占位的功能，常用于数值小数点对齐，如：格式设置为：?.?，当输入0103.50或103.5则系统显示为103.5_(注：为了便于理解用_来表示一个空格占位)。补充说明：在Excel自定义数字格式使用如下的表达方式：正数的格式负数的格式零的格式文本的格式 #,#0.00 Red-#,#0.000.00TEXT 在Excel自定义数字格式的格式代码中，用户最多可以指定四个节；每个节之间用分号进行分隔，这四个节顺序定义了格式中的正数、负数、零和文本。如果用户在表达方式中只指定两个节，则第一部分用于表示正数和零，第二部分用于表示负数。如果用户在表达方式中只指定了一个节，那么所有数字都会使用该格式。如果在表达方式中要跳过某一节，则对该节仅使用分号即可。#：数字占位符(非0值占位符) G/通用格式：用常规格式显示数字：文本占位符 *：重复下一个字符以充满列宽，：千位分隔符 :用这种格式显示下一个字符 ?:数字占位符实例一：要求数值后面带单位（个，元，），数值还可以进行数学运算自定义 类型 输入数值格式，在后面再输入引号（一定要拼音状态下），引号里面添加所需要的文字 实例二：自定义类型自定义格式单元格输入显示为解释使用文本#,#0 元人民币10001,000 元人民币输入数字，自动加入后缀,数值可运算答复: 通用格式1000答案: 1000只需输入数字，自动加入前缀,数值可运算总额是#,#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;仅显示正数!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-95mmmm d, yyyy1995-6-1June 1, 1995dddd1995-6-1Thursdaymmmm d, yyyy (dddd)1995-6-1June 1, 1995 (Thursday)Its dddd1995-6-1Its Thursday设置不同颜色红色=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()2006-12-10已过期100天!描述正数;负数;零;文本32正数仅以文字的格式表示原来的数，不影响D53至D56单元格中数值，可看后面的运算就知道。正数;负数;零;文本-32负数正数;负数;零;文本0零正数;负数;零;文本德赛电子文本隐藏;1234隐藏单元格内容，但编辑栏里还能看到的。不影响引用，运算。另一种隐藏是把字体设为白色。这两种方法都是表面的隐藏，都不能打印。;-145条件语句100#,000;99有要求的隐藏，大于100的数值显示，否则都不显示。其余同上。100#,000;102102100#,000;-54100#,000;德赛电子1.5.1.2对齐单元格格式的对齐一般功能很简单，如果做些复杂可参考见CD例子1.5.1.3字体功能常见，不再罗嗦1.5.1.4边框常用功能！此功能与其他操做结合，可以发挥意想不到的效果，可参考CD例子1.5.1.5图案此功能多数是与其他操做结合使用，可参考CD例子1.5.1.6保护非常有用的功能，尤其对于做汇总表的人！我们经常遇到这种情况：你辛辛苦苦做成一个表格，发给别人填写，别人给你乱改表格！本来很简单的汇总数据的工作将变得很繁琐，下面的例子将彻底解决你的烦恼！保护的效果：保护自己的数据表结构不被修改，公式内容不被看见；需要输入数据的区域可以自由编辑，否则不可改动！方法1. 保护(隐藏)表格公式不被修改(看见),而数据区却可以修改.解决如下:方法1.选中表的所有区域鼠标右键”设置单元格格式” ”保护” 取消”锁定”,”隐藏” 选中公式区域鼠标右键”设置单元格格式” ”保护” 选中”锁定”,”隐藏” ”工具”保护” ”保护工作表” 输入”密码” ”确定”方法2.(适合复杂大表)说明：1.浅色（黄色）是标题栏2.深色（绿色）是公式区3.中间（白色）是数据区4.要求数据区可以输入修改数据，标题栏只能浏览，不可修改；公式区能看见结果，不能修改公式，不能看见公式。做法：1、全选工作表单元格格式保护取消锁定。2、编辑 定位定位条件选公式按确定3、选定要保护的单元格，单元格格式保护勾选锁定、隐藏。4、工具 保护工作表选定锁定的内容输入密码。1.5.2工作表1.重命名：更改工作表名字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语句是屏蔽了都是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高级筛选3. 如果是选出重复数据，参见第二章 函数 判断单元格的值设置1.判断错误值=isERROR(A1)红色：如果