excel表格使用技巧.pptVIP

郁郁 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之中的，我们可以使用宏来完成单调的、频繁 的反复性工作。