excel表格使用技巧.pptx

1、怎样能够快速的插入工资表的表头在每个人的信息栏怎样能够快速的插入工资表的表头在每个人的信息栏?在表头下位置插入N行,复制表头.再在第一列插入空列,在A列输入单数列1,3,5.至表头末,再在原来的人名前输入双数列2,4,6.,用排序的方法(按A列,从小到大排序)就可得到有表头的工资表了.2、建立分类下拉列表填充项、建立分类下拉列表填充项 做财务报表时，我们常常要将资产的“关键字”和“地点”输入到表格中，为了保持名称的一致性，利用“数据有效性”功能建了一个分类下拉列表填充项。1）建立一个资产的“关键字”和“地点”数据库，选中A列(“关键字”名称所在列)，在“名称”栏内，输入“服务器设备”字符后，按“回车”键进行确认。仿照上面的操作，将B、C列分别命名为“终端软件”、“软件”第一节典型案例2）切换到Sheet1中，选中需要输入“关键字”的列(如C列)，执行“数据有效性”命令，打开“数据有效性”对话框。在“设置”标签中，单击“允许”右侧的下拉按钮，选中“序列”选项，在下面的“来源”方框中，输入“工业企业输入“服务器设备“终端软件”、“软件”序列(各元素之间用英文逗号隔开)，确定退出。3.数据图表的拍照。特别是目标位置的格式编排也必须反映出数据图表的拍照。特别是目标位置的格式编排也必须反映出来的时候，再使用连接数据的方式就行不通了。来的时候，再使用连接数据的方式就行不通了。Excel早为我早为我们准备了们准备了“照相机照相机”，你只要把希望反映出来的那部分内容，你只要把希望反映出来的那部分内容“照照”下来，然后把下来，然后把“照片照片”粘贴到其他的页面即可。粘贴到其他的页面即可。A、准备准备“照相机照相机”1)打开Excel的“工具”菜单，选择“自定义”对话框。2)单击“命令”选项卡，在“类别”中选择“工具”，在右边“命令”列表中找到“摄影”，并且将其拖到Excel页面工具栏的任意位置。这一步很多人并不熟悉，一定要自己试一试。(在Excel2007中打开“工具栏”，在“不在功能区中的命令”中找到“照相机”，点“添加”即可使用。B、给目标、给目标“拍照拍照”假设我们要让Sheet2中的部分内容自动出现在Sheet1中。1)拖动鼠标并且选择Sheet2中需要“拍照”的内容。这一步相当重要，一定要选准，不选或者选错，那你的“照片”就会啼笑皆非了。2)用鼠标单击工具栏上准备好的“照相机”按钮，于是这个选定的区域就被“拍”了下来。C、粘贴、粘贴“照片照片”1)打开Sheet1工作表。2)在需要显示“照片”的位置上单击鼠标左键，被“拍摄”的“照片”就立即粘贴过来了。在Sheet2中调整“照片”的各种格式，粘贴到Sheet1中的内容同步发生变化，而且因为插入的的确是一幅自动更新的图像文件，所以，“图片”工具栏对这个照片也是有效的，照片还可以自由的旋转、放缩、拖移。D、取消拍照粘贴功能、取消拍照粘贴功能如果拍照完毕，此时想取消照片粘贴功能，只要再次用鼠标单击工具栏上的“照相机”按钮，刚才“拍”下的照片则被取消，不会再被粘贴。员工面试成功后，公司HR需要给每位员工发放一份入职通知书，其中包括员工姓名、薪资、职级等内容，如果数量少的话大家往往是采用手工填写或者打印，若是人数较多的话，工作量非常大，实际上利用Excel的一些函数以及功能，并可以实现批量打印通知书，下面就以某公司的员工入职通知书打印为例，来介绍具体实现方法。员工入职信息表员工入职信息表通常一份标准的入职通知书，一般都需要包括员工姓名，薪资，职级，日期，因此在打印通知书之前，必须将这些资料整理好。4Excel的函数实现入职通知书的批量打印的函数实现入职通知书的批量打印设计通知书模板设计通知书模板新建一个工作表，将其命名为“批量聘用函”，然后在此表的A1:H7单元格区域中，根据自己公司的要求，输入入职通知书的必要内容，如编号、标题、开头语、表格以及员工签名的位置(图3)。由于要实现批量发送，必须在此入职函模板中添加一个通编号选择单元格，这样就能够通过这个单元格中的不同序号，从而能够显示不同的人员的入职函，在G1单元格中输入“编号”，然后确定H1单元格为通知书序号选项单元格。同时为了将前面入职信息明细中每个员工的数据自动引用过来，在B3中输入公式“=VLOOKUP(G1,erf!A2:K11,3,0)”，在C6中输入公式“=VLOOKUP($B$3,erf!C2:K11,3,0)”，并依次类推，在G1单元格中输入1时，就会在通知书模板中显示第1个入职函，而2时，则显示第2个入职函。需要注意的是，VLOOKUP等函数，其作用为根据G1单元格中的数值，而自动引用入职信息表明细对应的员工姓名，职级，薪资等信息。5.如何设置如何设置Excel公式让数据不再重复公式让数据不再重复我们在使用Excel2007编辑工作表格时经常要输入大量数据，如何设置才能使Excel公式让数据不再重复？有时要求输入的这些数据是不允许重复的，例如常见的身份证号码等，一旦错误输入查找起来那可是相当麻烦的一件事。对于这个问题，其实我们可以自定义公式，让Excel对重复数据能够自动弹出警告信息，以便及时提醒操作人员。Step1：运行Excel2007程序并新建一个工作表格，然后按下“Ctrl+A”组合键全选该表格。Step2：单击“数据”菜单，然后在“数据工具”功能区域中选择“数据有效性”选项，接下来在随即弹出的“数据有效性”对话框中选择“设置”选项卡，在“允许”下拉列表框中选择“自定义”标签，在“公式”下面的文本框中输入“=COUNTIF($1:$65535，A1)=1”(不含双引号)。这里的“$1:$65535”表示对全工作表范围进行重复检查。如果你需要检查的只是某一特定的单元格范围，可以根据实际情况重新进行调整，但必须使用绝对方式。Step3：切换到“出错警告”选项卡，在这里程序已经自动勾选了“输入无效数据时显示出错警告”的复选框，接下来将“样式”设置为“停止”选项，然后在右侧的“标题”和“错误信息”两个文本框中可以自行输入相关的内容、具体的警告信息等等，然后单击“确定”按钮即可。经过设置之后，今后重复输入相关数据时，程序会自动弹出一个对话框，提示“有重复数据出现”，提供“重试”、“取消”、“帮助”三个选项。选择“重试”，则对现有数据进行检查校核；选择“取消”可以重新输入。6、自动切换输入法、自动切换输入法 在一弛工作表中，去返是既无数据，又有文字，这样在输入时就须要往返在中英文之间重复切换输入法，无比麻烦。如果你要输入的货色很有法则性，比如这一列全是单词，下一列齐是汉语说明，你可以用以下方式真现自动切换。办法是：（1）选中要输入英文的列，单击“数据”菜单，选择“有效性.”命令，在弹出的“数占有效性”对话框中，选中“输入法模式”选项卡，在“模式”框中选择“封闭（英文模式）”命令，单击“断定”按钮。（2）选中要输入汉字的列，在“有效数据”对话框中，单击“IME模式”选项卡，在“模式”框中选择“打启”命令，单击“肯定”按钮。这样，当光标在前一列时，可以输入英文，在下一列时，直接可以输入中文，从而实现了中英文输入方法之间的自动切换。第二节：第二节：Excel函数和统计特训函数和统计特训绝对引用和相对引用在绝对与相对单元引用之间切换当你在Excel中创建一个公式时，该公式可以使用相对单元引用，即相对于公式所在的位置引用单元，也可以使用绝对单元引用，引用特定位置上的单元。公式还可以混合使用相对单元和绝对单元。绝对引用由$后跟符号表示，例如，$B$1是对第一行B列的绝对引用。借助公式工作时，通过使用下面这个捷径，你可以轻松地将行和列的引用从相对引用改变到绝对引用，反之亦然。操作方法是:选中包含公式的单元格，在公式栏中选择你想要改变的引用，按下F4切换。一、使用逻辑函数1.使用IF函数实现逻辑判断IF(logical_test，value_if_true，value_if_false)Logical_test计算结果为TRUE或FALSE的任何数值或表达式；Value_if_true是Logical_test为TRUE时函数的返回值，如果logical_test为TRUE并且省略了value_if_true，则返回TRUE。而且Value_if_true可以是一个表达式；Value_if_false是Logical_test为FALSE时函数的返回值。如果logical_test为FALSE并且省略value_if_false，则返回FALSE。Value_if_false也可以是一个表达式。一个常用的例子，就是老师在统计学生成就时，盼望输入60以下的分数时，能显示为“不及格”；输入60以上的分数时，显示为“合格。这样的后果，利用IF函数可以很方就地实现。假设成绩在A2单元格中，断定结果在A3单元格中。那么在A3单元格中输入公式：=if（A260，“不及格”，“及格”）同时，在IF函数中还可以嵌套IF函数或其它函数。例如，如果输入：=if（A260，“不合格”，if（A20，SUM（A1：A5），0）此式就利用了嵌套函数，意念是，当A1至A5的和大于0时，返回这个值，如果小于0，那么就返回0。还有一点要提示你注意：以上的符号均为半角，而且IF与括号之间也不能有空格。二、字符串函数1、随意抽取字符串任意位置的内容除了直接输入外，从已存在的单元格内容中提取特定字符输入，绝对是一种省时又省事的方法，特别是对一些样式雷同的信息更是如此，比如员工名单、籍贯等信息。如果我们想快速从A4单元格中提取称谓的话，最好使用“=RIGHT(源数据格，提取的字符数)”函数，它表示“从A4单元格最右侧的字符开始提取2个字符”输入到此位置。当然，如果你想提取姓名的话，则要使用“=LEFT(源数据格，提取的字符数)”函数了。还有一种情况，我们不从左右两端开始，而是直接从数据中间提取几个字符。比如我们要想从A5单元格中提取“武汉”两个字时，就只须在目标单元格中输入“=MID(A5,4,2)”就可以了。意思是：在A5单元格中提取第4个字符后的两个字符，也就是第4和第5两个字。举例：从身份证自动抽取出生年月日c)文字和数字的相互转换在Excel2002中，至少提供了三种有关文本大小写转换的函数。它们分别是：“=UPPER(源数据格)”，将文本全部转换为大写；“=LOWER(源数据格)”，将文本全部转换成小写；“=PROPER(源数据格)”，将文本转换成“适当”的大小写，如让每个单词的首字母为大写等。三、查找函数1.使用VLOOKUPVLOOKUP()函数的格式如下:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)函数的参数中除了col_index_num表示table_array中待返回的匹配值的列序号外,其他参数的意义和HLOOKUP()相同。四.合并函数使用 CONCATENATE(text1,text2,.)Text1,text2,.为 1到 30个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。说明说明：也可以用&（和号）运算符代替函数 CONCATENATE实现文本项的合并。五、常用统计函数1.常用统计函数简介SUMIF公式（条件求和公式）公式（条件求和公式）SUMIF公式是根据指定条件对若干单元格求和的公式。比如，仓库在打元龙大料单时，需要将图号相同的调拨数加起来算总的调拨数；又如有时需要将每一种零件的当月发出数累加汇总。此时，SUMIF公式就非常有用。SUMIF公式的一般格式如下：=SUMIF（用来判断的单元格区域，判断的条件，用来求和的单元格（用来判断的单元格区域，判断的条件，用来求和的单元格区域）区域）2.数据透视分类汇总数据透视表是一种交互式的表，可以进行某些计算，如求和与计数等。所进行的计算与数据在数据透视表中的排列有关。之所以称为数据透视表，是因为可以动态地改变它们的版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改变版面布置时，数据透视表会立即按照新的布置重新计算数据。另外，如果原始数据发生更改，则可以更新数据透视表。六、在Excel中自定义函数Excel函数虽然丰富，但并不能满足我们的所有需要。我们可以自定义一个函数，来完成一些特定的运算。下面，我们就来自定义一个计算梯形面积的函数：1.执行“工具宏VisualBasic编辑器”菜单命令(或按“Alt+F11”快捷键)，打开VisualBasic编辑窗口。2.在窗口中，执行“插入模块”菜单命令，插入一个新的模块模块1。3.在右边的“代码窗口”中输入以下代码：FunctionV(a,b,h)V=h*(a+b)/2EndFunction4.关闭窗口，自定义函数完成。以后可以像使用内置函数一样使用自定义函数。提示：用上面方法自定义的函数通常只能在相应的工作簿中使用。七、数据表的安全性1.如何保护公式不被误修改如何保护公式不被误修改1）选定全表，点格式-单元格-保护，取消锁定前面的对勾，点确定。2）选中你要保护的含有公式的单元格，点格式-单元格-保护，在锁定前面打上对勾，点确定点工具-保护-保护工作表，输入两次密码，点两次确定。2.如何设置文档为只读如何设置文档为只读,到文件夹无法删除到文件夹无法删除,方法如下：方法如下：1）首先要把该文件夹所在的盘转换成NTFS格式（在CMD里输入convertX:/fs:ntfs回车即可）2）进入CMD窗口找到该文件所在目录执行命令：cacls文件夹名子/p计算机用户名就是进入系统输入的那个用户名:N回车这个文件夹没有人能访问了，就不要说删除了，你想用的时候只要cacls文件夹名子/p计算机用户名就是进入系统输入的那个用户名:F回车对了就是把N换成F。就是建立一个别人既不能进入又不能删除的文件夹，第一步：在运行中输入cmd，回车，打开命令行窗口第二步：在命令行窗口中切换到想要建立文件夹的硬盘分区，如D盘第三步：输入MD123.回车，注意文件夹名后有2个小数点那么，如果自己想删除或者进入这个文件夹，又应该如何操作呢？同样也很简单。如果想删除，在命令行窗口中输入rd123.回车，即可删除;如果想进入，在命令行窗口中输入startd:123.（注意这里一定要是文件夹的绝对路径，否则无法打开即可打开此文件夹）.八、如何减少重复劳动?我们在实际应用Excel时，经常遇到有些操作重复应用(如定义上下标等)。为了减少重复劳动，我们可以把一些常用到的操作定义成宏。其方法是：选取“工具”菜单中的“宏”命令，执行“记录新宏”，记录好后按“停止”按钮即可。也可以用VBA编程定义宏宏是一个指令集，“宏”，指一系列EXCEL能够执行的VBA语句。用来告知EXCEL来完成用户指定的动作。宏相似于计算机程序，然而它是完整运行于EXCEL之中的，我们可以使用宏来完成单调的、频繁的反复性工作。宏完成动作的速度比用户大家做要快得多。例如，我们可以创建一个宏，用来在工作表的每一行上输入一组日期，并在每一单元格内居中对齐日期，然后对此行应用边框格式。我们还可以创建一个宏，在“页面设置”对话框中指定打印设置并打印文档。如果只是用“录制宏”的方法，基本就没有什么难的，只是把一些操作象用录音机一样录下来，到用的时候，只要履行这个宏，体系就会把那操作再执行一遍。下面给出了宏的应用场所，只要用“录制宏”就可以助你完成义务，而不需要编程。如果想对所录制的宏再进行编辑，就要有必定的VBA常识了。