excel表格使用技巧.ppt

Excel 应用技巧及精讲案例,郁郁 2011年3月3日,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页面工具栏的任意位置。这一步很多人并不熟悉，一定要自己试一试。(在Excel 2007中打开“工具栏”，在 “不在功能区中的命令”中找到“照相机”，点“添加”即可使用。,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：运行Excel 2007程序并新建一个工作表格，然后按下“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（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) 文字和数字的相互转换 在Excel 2002中，至少提供了三种有关文本大小写转换的函数。它们分别是：“=UPPER(源数据格)”，将文本全部转换为大写； “=LOWER(源数据格)”，将文本全部转换成小写；“=PROPER(源数据格)”，将文本转换成“适当”的大小写，如让每个单词的首字母为大写等。,三、查找函数 1.使用VLOOKUP VLOOKUP()函数的格式如下: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 函数的参数中除了col_index_num表示table_array 中待返回的匹配值的列序号外,其他参数的意义和HLOOKUP()相同。,四.合并函数使用,CONCATENATE (text1,text2,.) Text1, text2, . 为 1 到 30 个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。 说明：也可以用 如果想进入，在命令行窗口中输入start d:123.（注意这里一定要是文件夹的绝对路径，否则无法打开即可打开此文件夹）.,八、如何减少重复劳动? 我们在实际应用Excel时，经常遇到有些操作重复应用(如定义上下标等)。为了减少重复劳动，我们可以把一些常用到的操作定义成宏。其方法是：选取“工具”菜单中的“宏”命令，执行“记录新宏”，记录好后按“停止”按钮即可。也可以用VBA编程定义宏,宏是一个指令集， “宏”，指一系列EXCEL能够执行的VBA语句。用来告知EXCEL来完成用户指定的动作。宏相似于计算机程序，然而它是完整运行于EXCEL之中的，我们可以使用宏来完成单调的、频繁的反复性工作。 宏完成动作的速度比用户大家做要快得多。例如，我们可以创建一个宏，用来在工作表的每一行上输入一组日期，并在每一单元格内居中对齐日期，然后对此行应用边框格式。我们还可以创建一个宏，在“页面设置”对话框中指定打印设置并打印文档。,如果只是用“录制宏”的方法，基本就没有什么难的，只是把一些操作象用录音机一样录下来，到用的时候，只要履行这个宏，体系就会把那操作再执行一遍。,下面给出了宏的应用场所，只要用“录制宏”就可以助你完成义务，而不需要编程。如果想对所录制的宏再进行编辑，就要有必定的VBA常识了。,