《Excel公式使用技巧》由会员分享,可在线阅读,更多相关《Excel公式使用技巧(7页珍藏版)》请在金锄头文库上搜索。
1、Excel公式使用技巧笔者在用Excel编制一个“学生花名册”表时,总结出一些简单方法,现整理出来,供 大豕参考。-快速输入1. 如果要输入的内容在同一列已出现过,可直接单击右键,在弹出的快捷选单中单 击最下面的“选择列表”项,出现一个下拉框,选择其中一项即可。M2. 如果感觉这样输入还是繁琐,可利用Excel的“自动更正”功能更快速地进行输 入。下面以“户籍所在地”为例说明具体操作方法。首先,确定各个派出所的简写,如 朝阳派出所的简写为“CY”、人民路派出所简写为“RM”等。注意,各个简写不能重复, 应该是表中惟一的字母组合。然后,选择“工具”选单中的“自动更正”功能,在出现 的对话框中“替
2、换(R): ”栏中输入“CY”,“替换为(W):”栏中输入“朝阳派出所”。 如果“CY”在自动更正表中已存在,对话框右下角为“替换”按钮,这时,要重新确定 简写;如果“CY”在自动更正表中不存在,对话框右下角为“添加”按钮,这时,单击“添加”按钮。注意,要选择“键入时自动替换”功能。这样,就可以用简写输入“户 籍所在地”而让系统自动替换。-年龄计算如何根据出生年月自动计算出年龄?进入新学年后如何快速更新年龄?笔者经过 摸索,找到了一个简单办法:1. 将“年龄”列的单元格式定义为“常规”类型;“出生年月”列的单元格式定义 为“日期”“1995年3月”类型,该类型默认为每月的1日,即“95-3”等
3、同于 “95-3-1”。2. 使用DAYS360函数计算两个日期间相差的天数。语法:DAYS360(start_date, end_date, method)start_date和end_date分别为起始日期和终止日期。参数既可以是用双引号括起 来的字符串,其中用数字代表月、日、年(如“1/30/93”或“1-30-93”),也可以是代 表日期的序列数。如果start_date晚于end_date,则DAYS360函数将返回负数。method 是一个逻辑值,它规定了在计算中是采用何种日期格式。3. 使用ROUND函数按要求进行四舍五入。语法:ROUND(number, num_digits)
4、Number需要四舍五入的数字。Num_digits指定的位数,按此位数进行四舍五入。若Num-digits大于0,则四舍 五入到指定的小数位。若Num-digits等于0,则四舍五入到最接近的整数。若Num-digits 小于0,则在小数点左侧进行四舍五入。4. 在表 Sheet1 的 F2 单元格输入下面公式:=ROUND(DAYS360(,00-10-1)/360,0), 其余进行复制粘贴即可。5. 前面由于将终止日期“00-10-1 ”直接写进了公式,日后更新很不方便。解决方 法是任选一个空白单元格,设置为与“出生年月”相同的日期格式,输入“0C-10-1”。 选择“插入”选单中的“名
5、称”项,单击“定义”,在“当前工作簿中的名称” 一栏中 输入“终止日期”,注意,“引用位置”应该是刚才输入“00-10-1 ”的单元格,单击“确定”结束。这样,就给该单元格定义了名称。将公式改为:=ROUND(DAYS360(,终止日 期)/360,0)。以后,只需更改“终止日期”单元格的内容,就可以达到自动更新年龄的目的。-条件统计由于工作需要,每学期都要做统计表,虽然利用Excel的“筛选”、“分类汇总”、 “数据透视表”等功能也可以统计出有关数据,但每次都要进行一番操作,比较烦琐。 笔者利用Excel的三个函数编制了一张固定的统计表,数据发生变化统计表也随之变化。 具体方法是:Excel
6、小技巧 Excel中的打印技巧 Excel2000实用技巧1. 利用COUNTA函数可以计算一个区域中非空单元格的数目。语法:COUNTA(value1, value2, .)。valuel、value2为所要计数的值,参数个数为130个。这种情况下, 参数值可以是任何类型,它们可以包括空字符(“”)但不包括空白单元格。如果参数是 数组或单元格引用,则数组或引用中的空白单元格将被忽略。该函数可以应用到本例中 的“全班人数合计”项,公式为:=COUNTA(SHEET1! A2: A10),计算A2: A10这一区域 中非空的单元格数目,即全班人数。2. 利用COUNTIF函数计算某区域内满足一个
7、给定条件的单元格数目。语法: COUNTIF(range, criteria) 0 range是将要计算其中单元格数目的区域,criteria是确 定哪些单元格将被计算在内的条件,其形式可以是数字、表达式或文本。例如,条件可 以表示为32、“32”、32”等形式。本函数可以应用到本例中Sheet2的D1等单元 格,公式为:=COUNTIF(Sheet1! B2: B10,“男”),计算出全班男生数目。3. 利用DCOUNTA函数计算给定区域中符合多个条件并且非空的单元格数。语法: DCOVNT(database,field,criteria)。database 是组成数据库的单元格区域,fie
8、ld 是将要用于函数计算的字段,criteria是包含条件的单元格区域。本函数应用到Sheet2 的D2单元格的具体方法是:在Sheet3中建立一个条件区域,如图3所示:统计全班汉族男生的公式为:=DCOUNTA(Sheet1! A1: I10,Sheet3! B1: C2)。同 样,也可以为公式中单元格区域定义名称,方便以后维护。4. 为了保证统计表Sheet2的正确性,可以利用IF函数进行一致性验证。IF函数语 法:IF(logical_test,value_if_true,value_if_false)如果logical_test计算值为TRUE,则返回value_if_true;否则返
9、回 value_if_false。例如,全班人数、男生人数和女生人数都是自动统计出来的,为了验 证其一致性,可任选一空白单元,输入公式:=IF(B1=D1+F1,OK,ERROR)若显示“ERROR”,则说明统计表有误。Excel的自动套用格式步骤详解“自动套用格式”可自动识别Excel工作表中的汇总层次以及明细数据的具体情况,然 后统一对它们的格式进行修改。Excel 一共通过“自动套用格式”功能向用户提供了简 单、经典、会计、彩色、应用和三维格式等6大类、20余种不同的内置格式集合,每种 格式集合都包括有不同的字体、字号、数字、图案、边框、对齐方式、行高、列宽等设 置项目,完全可满足我们在
10、各种不同条件下设置工作表格式的要求。具体来说,利用“自动套用格式”功能快速格式化Excel工作表的格式,可采用如 下步骤:1、启动Excel,打开需要套用格式的工作簿。2、选定需要套用格式的工作表范围(用户若不事先选择套用范围,则Excel将自动 对整个工作表的格式进行设置)。3、执行“格式”菜单的“自动套用格式”命令,打开如图所示的“自动套用格式” 对话框。4、在参考“示例”栏所显示格式样式的基础上从“格式”栏中选择合适的工作表 格式集合。5、单击选项按钮,然后从弹出的“应用格式种类”对话框中选择允许采用该种 格式集合中的那些不同格式项目。6、单击确定按钮,关闭“自动套用格式”对话框。这样,
11、我们就完成了利用“自动套用格式”快速地为Excel工作表定义格式。当然, 自动套用格式之后,我们仍可按照正常方法对这些工作表的格式进行修改,应用非常灵 活。另外,用户若对Excel自动套用的格式不满意,则可采用下述方法将工作表的格式 恢复到“自动套用格式”之前的状态:选择含有要删除自动套用格式的区域,执行“格式”菜单的“自动套用格式”命令, 从弹出的“自动套用格式”对话框的“样式”栏中选择“无”选项,单击确定按钮后, 关闭“自动套用格式”对话框。公式输入方法1. 公式选项板以成绩等级计算公式 =IF(C2=85, A ,IF(C2=70, B ,IF(C2=60, C,IF(C2=85”。第二
12、个参数“Value iftrue”是“C2=85”为“真”时的计算(判断)结果,也需要键盘在其后的输入框内 填入“A”。第三个参数“Valueiffalse”是逻辑判断“C2=85”为“假”时的计算(判 断)结果,本例是继续执行下一个IF函数。所以应让光标停留在“Valueiffalse”框内, 然后单击“函数”框中的“IF”,则一个新的“IF”公式选项板打开,按上面介绍的方法继 续输入直至结束。在公式输入过程中,已输入的内容可以在Excel编辑栏中看到。另外,公 式选项板可以用鼠标拖动,如果它影响观察,可单击输入框右边的按钮将它折叠起来。(5) 公式输入结束后,单击公式选项板中的“确定”按钮
13、完成计算。公式选项板是一种向导式输入,用户可以详细了解函数的各个参数、功能和当前的计 算结果。当光标停留在参数框中时,还可以看到对参数的描述。其最大优点是输入时不必过 多考虑分隔符、单元格引用等细节,用它建立和调试公式不易出错。2. 手工输入手工输入主要用于套用现成的公式,或输入一些比较简单的公式。它的输入方法是:(1)选中存放计算结果的单元格。(2)用鼠标在Excel的编辑栏内单击,然后按照公式的组成(见上例)顺序输入。(3)公式输入结束后,单击编辑栏左端“输入”(“J”)按钮进行计算。对于一些非常简单的公式,如两个单元格的数值相减,也可以直接在存放结果的单元 格内输入公式(如“=A1B1”
14、),然后回车计算出结果。公式使用技巧1. 复制复制是将公式应用于其它单元格的操作,最常用的有以下几种方法:(1)拖动复制操作方法是:选中存放公式的单元格,移动空心十字光标至单元格右下角。待光标变 成小实心十字时,按住鼠标左键沿列(对行计算时)或行(对列计算时)拖动,至数据结尾 完成公式的复制和计算。公式复制的快慢可由小实心十字光标距虚框的远近来调节:小实心 十字光标距虚框越远,复制越快;反之,复制越慢。(2)输入复制此法是在公式输入结束后立即完成公式的复制。操作方法是:选中需要使用该公式的 所有单元格,用上面介绍的方法输入公式,完成后按住Ctrl键并按回车键,该公式就被复 制到已选中的所有单元
15、格。(3)选择性粘贴操作方法是:选中存放公式的单元格,单击Excel工具栏中的“复制”按钮。然后选 中需要使用该公式的单元格,在选中区域内单击鼠标右键,选择快捷选单中的“选择性粘贴” 命令。打开“选择性粘贴”对话框后选中“粘贴”命令,单击“确定”,公式就被复制到已 选中的单元格。2. 编辑(1)打开公式选项板如果公式是由几个Excel函数组合而成,而你又要用公式选项板修改某个函数的参数, 则可用鼠标单击函数名称或括号内的参数,再单击编辑栏左端的“=”按钮,即可打开这个 函数的公式选项板。修改完毕后单击“确定”将其关闭。(2) 公式与结果切换单元格中的公式执行后显示的是计算结果,按Ctrl+键(位于键盘左上角),可使公 式在显示公式内容与显示公式