1、Excel 常用技巧及函数一、常用技巧：单元格的格式：自定义格式：”显示时添加内部字符，如”生日是”YYYY”年”MM”月”DD”日”； #.#(设置小数点后位数,但不补 0),00000.00(设置小数点前后位数,不足补 0),?.?( 设置小数点前后位数,不足补空位,便于对齐).数据前面带 0 和超过 15 位数字输入：当我们输入 002354 时，Excel 会自动将之作为数值2354 处理，这时我们只需在 002354 前加上一个“ ”，即输入“ 002354”。或者先将单元格设为文本格式。单元格内换行：ALT+ENTER同时在多个单元格中输入相同数据：选定需要输入数据的单元格，单元格
2、可以是相邻的，也可以是不相邻的，然后键入相应数据，按 CTRL+ENTER 键即可。条件格式：不同类型数据以不同格式显示。如对成绩表不同成绩段以不同颜色显示。数据有效性：对输入的数据进行限制，也可以提供下拉列表选择的快捷方式。窗口拆分和冻结：比如一个工资表有几百行和几十列，行和列都不能在一页全部显示，要在任何时候都可以看到标题行和姓名列，就可以对窗口进行拆分和冻结，这样标题行等就不会移动。标题行打印：比如一个工资表有几百行，打印时每页都要打印标题行，则在打印时设置标题行。文件页面设置工作表顶端标题行（也可选择左端标题列）公式和数据的保护：数据和公式创建好了，为了防止不小心改动或者不允许别的用户
3、改动，可以对已固定的数据进行保护；也可以对公式进行隐藏，只显示计算结果。文档保护：对文档加密，防止其他用户查阅。二、常用函数：在学习 Excel 函数前，我们先要了解一些相关的基本知识：填充柄：位于活动单元格的右下角，可使用拖动的方式，执行填充单元格的功能。智能标签：当我们执行了某些命令时它会自动出现在活动单元格“填充柄”的旁边，等待着我们下达与此命令相关的动作。（如果你不理它，它就会看自动消失）如下图。编辑栏：要是你的编辑栏不见了，就到“视图”菜单中把它找回来。单元格名称：EXCEL 使用单元格所在的行和列命名每一个单元格，如 A1，C5。智能标签勾选之后，编辑栏就出现了单元格区域：一组相邻
4、的呈矩形的单元格。引用单元格用它的左上角单元格的地址和右下角的地址来表示，中间用一个冒号。如：A2：B3，$A$2:$B$3，A:A（代表整个 A 列），B:C（代表整个 B 列和 C 列）。单元格的几种引用方法：最主要的引用就是 A1 方式，就是根据行列确定引用的单元格，包含三种方式：1）相对引用：以列标和行号组成，如 A1，B2，C3 等，在进行公式复制等操作时，若引用公式的单元格地址发生变动，公式中的相对地址会随之变动。2）绝对引用：以列标和行号前加上符号“$”构成，如$A$1,$B$2,$C$3 等。在进行公式复制等操作时，当引用公式的单元格发生变动时，公式中的绝对地址保持不变。3）混
5、合引用：它是上述两种地址的混合使用方式，如$A1(绝对列相对行)，A$1(相对列绝对行)等。在进行公式复制等操作时，公式中相对行和相对列部分会随引用公式的单元格地址变动而变动，而绝对行和绝对部分则保持不变。引用方法实例：计算示例表中不同价格和销量下毛利润：除了 A1 方式，还有对命名区域引用，R1C1 方式等。什么是函数：Excel 中所提的函数其实是一些预定义的计算规则，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算。例如，SUM 函数对单元格或单元格区域进行加法运算。当然 Excel 中函数有其固定的结构。如下图所示，函数的结构
6、以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号（=）。在创建包含函数的公式时，公式选项板将提供相关的帮助。 这些都是我们平时输入公式时所要清楚的，要是万一我们忘记了怎么办呢？不要急，Microsoft 早就帮我们想好了处理办法：推荐函数窗口，可以描述你所要的功能搜索相应的函数。并帮我们准备了几个常用的函数以备我们随时取用，也可以分类查找所需要的函数，如要处理文本，选择类别为“文本”。同时我们还可以利用公式选项板来帮助我们完成公式的编辑。它可以给我们提供有关函数及其参数的说明信息。如下图： 函数的种类Excel 自带的函数一共有 9
7、 类，分别是数据库函数、日期与时间函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数。 常用函数这些函数在这里就不再一一细述，我们只看一些平时我们经常会用到的函数。SUM1、 数值、行或列求和，因为最常用，所以在标题栏上有个快捷按钮 。例： =SUM(12,45),=SUM(23, H3:H12,45),=SUM(H3:H12) ，=SUM(A5:H5)2、 区域求和区域求和常用于对一张工作表中的所有数据求总计。此时你可以让单元格指针停留在存放结果的单元格，然后在 Excel 编辑栏输入公式“=SUM（）“，用鼠标在括号中间单击，最后拖过需要求和的所有单元格
8、。若这些单元格是不连续的，可以按住 Ctrl 键分别拖过它们。对于需要减去的单元格，则可以按住 Ctrl 键逐个选中它们，然后用手工在公式引用的单元格前加上负号。当然你也可以用公式选项板完成上述工作，不过对于 SUM 函数来说手工还是来的快一些。如： =SUM(D3:D12,F3:F12)-SUM(G3:G12)3、 注意SUM 函数中的参数，即被求和的单元格或单元格区域不能超过 30 个。换句话说，SUM 函数括号中出现的分隔符（逗号）不能多于 29 个，否则 Excel 就会提示参数太多。对需要参与求和的某个常数，可用“=SUM（单元格区域，常数）“的形式直接引用，一般不必绝对引用存放该常
9、数的单元格。另外还有求最大值：MAX，最小值：MIN，平均值：AVERAGE，计数：COUNT,用法与 SUM 一致。对这类函数，EXCEL 还提供了快捷查看方式，选中所要统计数据区域，即可在下方状态栏看到相应结果，掌握这种方法对实际工作很有帮助。IFIF 函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此 If 函数也称之为条件函数。语法：IF(logical_test,value_if_true,value_if_false)。应用：1、 输出带有公式的空白表单 选中数据区域状态栏的快捷统计结果以图中所示的人事状况分析表为例，由于各部门关于人员的组成情况的数据尚未填写，在总
10、计栏（以单元格 G15 为例）公式为：=SUM(C15:F15) 我们看到计算为 0 的结果。如果这样的表格打印出来就页面的美观来看显示是不令人满意的。是否有办法去掉总计栏中的 0 呢？你可能会说，不写公式不就行了。当然这是一个办法，但是，如果我们利用了 IF 函数的话，也可以在写公式的情况下，同样不显示这些 0。如何实现呢？只需将总计栏中的公式（仅以单元格 G15 为例）改写成：=IF(SUM(C15:F15),SUM(C15:F15),“) 通俗的解释就是：如果 SUM(C5:F5)不等于零，则在单元格中显示 SUM(C5:F5)的结果，否则显示空字符串。2、 不同的条件返回不同的结果如果
11、对上述例子有了很好的理解后，我们就很容易将 IF 函数应用到更广泛的领域。比如，在成绩表中根据不同的成绩区分合格与不合格。现在我们就以某班级的英语成绩为例具体说明用法。某班级的成绩如下图所示，为了做出最终的综合评定，我们设定按照平均分判断该学生成绩是否合格的规则。如果各科平均分超过 60 分则认为是合格的，否则记作不合格。根据这一规则，我们在综合评定中写公式（以单元格 B12 为例）：=IF(B11=60,“合格“,“不合格“)语法解释为，如果单元格 B11 的值大于 60，则执行第二个参数即在单元格 B12 中显示合格字样，否则执行第三个参数即在单元格 B12 中显示不合格字样。在综合评定栏
12、中可以看到由于 C 列的同学各科平均分为 54 分，综合评定为不合格。其余均为合格。3、 多层嵌套函数的应用在上述的例子中，我们只是将成绩简单区分为合格与不合格，在实际应用中，成绩通常是有多个等级的，比如优、良、中、及格、不及格等。有办法一次性区分吗？可以使用多层嵌套的办法来实现。仍以上例为例，我们设定综合评定的规则为当各科平均分超过 90 时，评定为优秀。如下图所示。 说明：为了解释起来比较方便，我们在这里仅做两重嵌套的示例，您可以按照实际情况进行更多重的嵌套，但请注意 Excel 的 IF 函数最多允许七重嵌套。根据这一规则，我们在综合评定中写公式（以单元格 F12 为例）：=IF(F11
13、60,IF(AND(F1190),“优秀“,“合格“),“不合格“)语法解释为，如果单元格 F11 的值大于 60，则执行第二个参数，在这里为嵌套函数，继续判断单元格 F11 的值是否大于 90 如果满足在单元格 F12 中显示优秀字样，不满足显示合格字样，如果 F11 的值以上条件都不满足，则执行第三个参数即在单元格 F12 中显示不合格字样。多条件同时满足和任意满足：OR 与 ANDSUMIFSUMIF 函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。例： =SUMIF($C$3:$C$12,“销售部“,$F$3:$F$12)其中“$C$3:$C$12“为提供逻辑判断依据的单元格区域，“销售部“为判断条件即只统计$C$3:$C$12 区域中部门为“销售部“的单元格，$F$3:$F$12 为实际求和的单元格区域。COUNTIFCOUNTIF 可以用来计算给定区域内满足特定条件的单元格的数目。语法形式为 COUNTIF(range,criteria)。
