EXCEL操作技巧企业培训版Word文档格式.docx

1、4.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 SUM 402.2.2数据库函数： 402.2.3查找引用函数 402.2.4日期时间函数 402.2.5财务会计函数 402.2.6信息函数 402.3数

2、组 402.4宏表函数 40第3章 图表 40第4章 窗体和VBA 40第5章 综合应用案例分析 40第6章 经典技巧大典 40序言关于本书本书给那些经常使用EXCEL，又不熟悉它的强大功能的人准备！本书不讲类如安装，新建，保存等非常基础的东东，如果这些都不会就不要读此书了！本书可以达到的效果是： ！Excel第1章 基本操作1.1 文件1.1.1另存为为了有效保护文档安全，经常需要设置密码： （OFFICE密码只能放菜鸟）A .文件 另存为 工具 常规选项输入密码 确定（要覆盖原文件才能设置成功密码，否则另存为新文件，只能是新文件有密码的）还有一种设置密码方法：B 工具 选项 安全性 输入密

3、码 确定 1.1.2页面设置1.1.2.1页面1. 方向：选择打印的纸张方向2. 缩放：.调整为，意思把多于一页的表格调整为特定页数！此操纵前，最好先把页边距调整好 （.文件 打印预览 页边距）。此功能等同于（ 视图 ） 分页预览。1.1.2.2页边距1.上下左右，页眉页脚：设置页边距；等同于 .文件 打印预览 页边距2.居中方式：数据（图表）在这个页面里面的居中（水平，垂直）位置，1.1.2.3页眉页脚1.自定义页眉（页脚）：文件 页面设置自定义页眉（页脚）从左至右顺序功能01.: 设置页眉/页脚文字的属性,例如:字体,字型,大小,下划线,删除线,上标,下标02.页码:当前第几页03.总页数

4、,所有数据共有几页04.日期:系统当前日期,是自动更新的05.时间系统当前时间, 是自动更新的06.路径文件:此文件的绝对路径+文件名,例如07.文件:当前文件名08.标签名:当前表名09.图片:插入图片,一般是企业LOGO 或者徽标10.设置:插入图片后,设置图片格式的11（12.13）:位置: 设置页眉（页脚）在在文件的左,中,右位置实际应用例子:1.要求每张文件右下脚都要有”制表人”,？ 答：就在上图 13”位置直接填入:”制表人:” 说明： 工作表设置（详见1.1.2.4 工作表）打印标题栏只能打印顶端（左端）标题栏，不能打印尾端标题栏；页脚设置可以部分解决这个问题。彻底解决就需要设置

5、复杂的公式，详见CD文件！2.曾经有人问：正常编辑时候是自己要求的数据和图表，但是预览和打印的时候就在页眉，页脚位置出现一些多余的文字和数据，怎么解决？ 看看页眉，页脚的自定义； 3.1.打印区域: 不打印全部文章，只打印部分,等同于 .文件 打印区域 设置打印区域2.打印标题:每页都打印同样的标题!实际应用：如表格很长,要每页都要有标题行,就点击顶端标题行设置即可,左端标题列类似.3.打印：网格线 把虚线的网格线都打印出来单色打印 彩色的也只是按照黑白打印按草稿方式 不打印边框，彩色打印成黑白色行号列标 是否打印行号列标批注 是否打印批注错误单元格打印为 设置错误值打印样式要填写标题，主题，

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

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

8、簿： 把选定工作表移至（剪切）当前打开的某个excel文件2. 下列选定工作表之前：把选定工作表移至（剪切） 某个表前面3. 建立副本： 选中就不是 移至（剪切），而是复制1.2.4查找（替换）1格式：可以按照特定格式查找（替换）2范围：可在当前工作表，当前整个文件查找例子：1. 想把表内单元格值为0的为改为空格按ctrl+H替换查找内容输入0替换为空全部替换。这样做 101 变成 11,1000001 也会变成 11，不是我们要的结果！正确做法:要勾选单元格匹配,只替换0值的单元格2. 查找（替换）特殊字符: * ? （非常重要的知识）如果对类似01011061*A这样带有 * 的数据处理会

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

24、0时左右有“”12.212值为正时常规显示-102（102）值为负时用括号括起来电话号码（#） #-#852*（852377） 659-6295#/#-#852377/659-6295日期格式mmmm-yy1995-6-1June-95mmmm d, yyyyJune 1, 1995ddddThursdaymmmm d, yyyy （dddd）June 1, 1995 （Thursday）Its dddds Thursday设置不同颜色红色=1#,#0;G/通用格式-1-100.0%45G/通用格式;红色G/通用格式德赛电子（惠州）有限公司最后一个节规定了文本的颜色234其他符号2004#-#-#421897322421-89-7322社会福利号 #-#-#社会福利号 421-89-7322=C113-TODAY（）2006-12-10已过期100天!描述正数;负数;零文本32正数仅以文字的格式表示原来的数，不影响D53至D56单元格