会计常用Excel技巧

目录Page14.数据透视表图3.公式与函数2.常用功能和技巧1.工作簿和工作表5.简述图表应用6.初识宏与VBA培训前言Office是最为流行的办公软件，其家族成员主要有Excel、Word、PowerPoint、Access及Outlook。前三者应用较为广泛，而Excel无疑是最重要的成员。Excel可以制作电子表格、进行各种数据处理及计算分析等，被广泛应用于管理、统计、财务、金融等众多领域。在很多企业和单位,Excel已经成为数据处理和信息化建设的重要工具。无论用户身处哪个行业、所在公司有没有实施信息ERP系统，只要和数据打交道，Excel几乎是不二的选择。Page2培训前言其实市面上有很多关于Excel的书籍写得很不错，如Excelhome网站组织编写的《Excel2007应用大全》、《Excel实战技巧精粹》系列等。出版图书是面向全国不同水平层次的读者，知识点涉及较多或为求全面难免讲解不够深入，学习不知从何入手。有些不常用的功能，花了不少的时间和精力学习，却极少使用。本教材根据公司内部培训资料整理而成，配以电子档实例、动态操作图及配套练习题，力求培训简单有效和快速吸收。Page3培训前言笔者从事财务工作13年，因此主要以财务工作中实际应用较多的操作技能为培训重点，旨在提高财务人员的实操能力，提升工作效率，也适用于统计、数据分析和文职类工作。特别说明：Excel操作通常会有三种实现方式，菜单栏选项、单击鼠标右键和快捷功能键。本着方便快捷的原则，一般只讲解常用的方式。本教材适用Excel2007版本。Page4第1章工作簿和工作表1.1认识Excel1.2工作簿的操作1.3工作表的操作Page5第1章工作簿和工作表1.1认识Excel首先，我们来了解一下Excel几个重要的对象，按照包含关系依次为工作簿、工作表和单元格。工作簿：是指Excel环境中用来储存并处理工作数据的文件，也就是我们常说的Excel文档。它是Excel工作区中一个或多个工作表的集合。受内存影响，每个工作簿最多可创建255个工作表。新建的工作簿默认有3个工作表，如果要改变默认表数量，可以通过Excel选项--常用选项卡进行修改。Page6第1章工作簿和工作表1.1认识Excel工作表：显示在工作簿窗口中、由行列交叉构成的表格,一个工作表可以有1,048,576行和16,384列（Excel2003版本为65,536行和256列）。单元格：工作表中行与列的交叉部分，它是组成表格的最小单位，可拆分或者合并。单元格按所在的行列位置来命名，例如“B5”指的是B列与第5行交叉位置上的单元格。还有其他对象如图形、批注、控件等，在后面的章节涉及时再详细介绍。Page7第1章工作簿和工作表1.2工作簿的操作1.2.1基本操作创建(Ctrl+N)：在Windows窗口空白处点击右键--新建--Excel工作表，也可以在Excel程序已打开时按Ctrl+N新建一个空白工作簿。复制(Ctrl+C)、剪切(Ctrl+X)、粘贴(Ctrl+V)：通过点击鼠标右键或快捷键完成。同一文件夹下复制，按住Ctrl键、同时用鼠标拖曳则效率更高。删除(Ctrl+D)：通过点击鼠标右键、按Ctrl+D或Delete键完成。重命名(F2)：选中工作簿名称，点击鼠标右键或按F2功能键进入修改状态。Page8第1章工作簿和工作表1.2.2直接保存手动保存：点击菜单栏图标或使用快捷键Ctrl+S。工作簿打开后有修改内容或格式（不包括查找），点击关闭会提示是否保存修改内容，否则直接关闭。自动保存：为避免各种意外造成工作簿未能及时保存，Excel提供了自动保存和恢复功能。当发生断电或程序崩溃的情形，Excel重启后会在左侧弹出最后一次保存前的工作簿，但最后一次保存后编辑的内容将不会保存，无法恢复。友情提示：自动保存不能代替手工保存，经常点击保存按钮或Ctrl+S,培养良好的工作习惯。Page9第1章工作簿和工作表1.2.3另存为另存为的作用在于原工作簿修改后不能直接保存时，可以将修改后的内容以其他格式或不同名称保存至同一文件夹中。如保存至不同文件夹，则不受名称重复限制，也不会出现“xx文件已经存在，是否替换现有文件”的提示。另存成功后，当前工作簿切换为新的工作簿。如打开A修改后另存为B，则A自动关闭，B为打开状态。A修改后如果没有手动保存，A的内容不会发生改变。工作簿打开界面，点击左上角图标，另存为选项下有多种文件格式可以选择。Page10第1章工作簿和工作表1.2.3另存为“启用宏的Excel工作簿”是指工作簿包含宏表函数、宏或者VBA代码时，必须以这种格式保存，否则无法正常保存使用宏表函数的定义名称、宏或者VBA代码。“Excel97-2003工作簿”则主要是考虑到版本向下兼容（不能向上兼容，即低版本不能另存为高版本），使两台不同Excel版本的电脑之间可以共享资源。还有其他一些格式可以根据需要进行选择，不过实际使用的概率不大。Page11第1章工作簿和工作表1.2.4工作簿加密和只读工作簿打开界面，点击左上角图标，准备--加密文档，两次输入密码，保存后即可完成工作簿加密。再次打开文档，需要输入正确的密码。解除加密状态：点击左上角图标，准备--加密文档，清除密码后保存文档，加密解除。为防止其他使用人更改文件内容，还可以选择“标记为最终状态”，相当于标记为只读属性。2007版还增加了一种通过添加不可见数字签名的方式，来确保工作簿的完整性。对文档进行数字签名后，文档就变成只读，以防止修改。Page12第1章工作簿和工作表1.3工作表的操作1.3.1基本操作选中工作表标签(以下简称表标签)，点击鼠标右键可以新建、删除、重命名、隐藏或取消隐藏、移动或复制、保护工作表。选中表标签后，按下Ctrl键、按住鼠标左键向左或向右拖动，可以快速地复制工作表；不按Ctrl键，快速移动工作表。移动或复制工作表选项：可以新建工作簿及实现不同工作簿之间工作表的复制或转移。如勾选建立副本为复制，否则为移动。无论复制还是移动,前提是目标工作簿处于打开状态。深度隐藏：即工作表界面无法显示和取消隐藏。选中工作表标签，右键--查看代码进入VBA编辑界面，选择需要隐藏的工作表，将其Visible属性改为2，然后退出即可。Page13第1章工作簿和工作表1.3.2保护工作表依次选择审阅--更改组--保护工作表或者选中工作表标签右键选择保护工作表，两次输入密码，保存后完成表格加密。工作表加密后，允许用户执行哪些操作，可以根据需要对加密内容进行勾选，如图1-1：图1-1Page14第1章工作簿和工作表1.3.2保护工作表勾选“编辑对象”，加密后仍然可以对图形、图表、控件等对象进行选中和编辑，否则只能选中一部分对象。勾选“编辑方案”，加密后仍然可以对数据--数据工具--假设分析下的方案管理器、单变量求解进行编辑。特别提示：如果想在工作表加密后使用自动筛选和数据透视表，必须在加密前标记筛选状态和添加数据透视表，并同时勾选“使用自动筛选”和“使用数据透视表”。即使这样，数据透视表仍然有一部分操作受到限制。Page15第1章工作簿和工作表1.3.2保护工作表设置允许用户编辑区域，是一个非常实用的功能。因为工作表加密后，经常是部分区域锁定，部分区域需要编辑。提示：新建工作表的单元格默认处于锁定状态。如果需要锁定大部分单元格区域，加密前先点击“允许用户编辑区域”按需要添加单元格范围，这部分单元格加密后仍然可以进行编辑。如果只是锁定小部分区域，可以在加密前全选工作表（按Ctrl+A或点击左上角行列交叉的区域），然后右键--设置单元格格式--保护选项卡，去掉“锁定”前面的勾，这样全表单元格取消锁定状态。再选定需要锁定的单元格区域，勾选“锁定”即可。Page16第1章工作簿和工作表1.3.3打印操作点击左上角图标选择打印或Ctrl+P调出打印对话框，根据需要修改打印设置，如打印范围、打印份数、打印内容等。页面布局中，可以设置固定的打印区域、页边距、页眉页脚、纸张大小等。设置顶端标题行，可以在打印多页的情况下，保证每一页都有正文标题和表头字段。插入分页符，则可以根据自己的需要安排每页打印内容，而不必每页都保持一致。Page17第1章工作簿和工作表1.3.3打印操作为提升美感或添加公司LOGO，还可以制作或插入图片作为文档背景。但背景图片不能直接打印，可以在设置页眉或页脚时插入所需图片，即可以实现此功能。特别提示：打印预览空白文档，会提示“MicrosoftOfficeExcel未发现可以打印的内容。”，但只要表格中有一个空格，就不会出现以上提示。如果你遇到明明可以打印成一页却出现两页的情况，就应该考虑是不是存在超出打印页面的空格、格式设置或标点符号之类的无效内容。Page18第2章常用功能和技巧2.1快速录入和快捷操作2.2开始菜单2.3排序2.4筛选2.5查找和替换2.6定位填充2.7分列与选择性粘贴2.8数据有效性2.9其他功能和常见问题Page19第2章常用功能和技巧为了便于记忆和实际应用，本章将按菜单栏分布顺序，选择常用的编辑或操作功能进行讲解。涉及到函数应用和数据汇总分析的，分别在第三章函数和公式、第四章数据透视表图进行讲述，此章暂不涉及。开始本章之前，先来认识一下“快速访问工具栏”，即菜单栏上方长条形区域。右键点击最后的倒三角箭头，可以通过勾选增加和删除现有快捷键。选择其他命令，还可以自定义更多快捷键。如图2-1：Page20第2章常用功能和技巧2.1快速录入和快捷操作2.1.1快速录入自动更新日期与固定日期的输入选取任意一个单元格，输入函数或快捷键：自动更新当前日期—=TODAY（）自动更新当前日期和时间—=NOW（）固定当前日期，按Ctrl+;固定当前时间，按Ctrl+Shift+;固定当前日期和时间，按Ctrl+;然后按空格键，再按Ctrl+Shift+;自动更新，意味着工作表每一次重新计算，单元格内的日期和时间都会发生变化。Page21第2章常用功能和技巧2.1.1快速录入序列填充通过序列填充可以快速输入相同或有序的文本和数字，实现方式一般有三种：①鼠标拖动填充柄输入序列（鼠标左键或右键拖放）；②通过菜单栏“编辑”→“填充”→“序列”；③通过菜单栏“数据”→“数据有效性”→“设置”选项卡→有效性条件选择“序列”；①和②侧重于数字、文本型数字和自定义序列的输入,③主要用于固定内容的文本多次选择性输入，稍后在数据有效性章节讲解。Page22第2章常用功能和技巧2.1.1快速录入鼠标拖曳填充选中单元格，鼠标移到单元格边框右下角，当鼠标变成十字架，按住鼠标左键向下拖曳或右键向下拖曳后根据实际需求进行选择。还有一种更快速的方法，就是当鼠标变成十字架时，双击鼠标左键，但前提是该填充列左边或右边列有相同行数的数据且中间不能断行。拖曳方向不同，填充结果也不一样。向下或向右是数字的绝对值递增，向上或向左是绝对值递减。来看两组动态操作图：序列填充1和序列填充2。Page23第2章常用功能和技巧2.1.1快速录入序列填充1：A列使用鼠标左键拖曳（因为左右列无相同行数的数据），且必须选中两个单元格的数据，否则无法判断等差序列的步长值。B、C、D列都是双击鼠标左键实现快速填充。操作结果显示：B列按自然数顺序填充，C列数字顺序填充、文本不变，D列按自定义序列顺序填充、达到最大值以后重新返回最小值再次顺序填充。序列填充2：拖曳鼠标左键同时按住Ctrl键（鼠标变成大小两个十字架），同样的数据，拖曳以后结果变成了复制。Page24第2章常用功能和技巧2.1.1快速录入特殊情况下的数据输入⑴.输入分数(如1/2),应先输入“0”及一个空格后顺序输入1、/、2，结果显示“1/2”。⑵.输入平方米m2，先输入m2，再双击单元格进入编辑状态，抹黑数字2，鼠标右键—设