2018-11-24

E战到底特训营  第二章  函数与公式

日期：2018/11/24

学员：3队若水

三队超燃战队口号：超越自我，燃烧他人。

一、函数的基本用法

（一）什么是公式

1、是EXCEL工作表中进行数值计算的等式。

2、公式输入是以“=”开始的。

3、简单的公式有加、减、乘、除等计算。

（二）什么是函数

1、简而言之，函数就是预先定义好了的公式。比如上例中的公式就可以用函数G13=SUM(G1:G11)代替。

2、函数不仅可以简化公式，而且具有仅用运算符连接的一般公式所不能替代的功能。例如查找引用，逻辑判断等等。

（三）数组公式

输入后按ctrl+shift+enter三键结束，多对多。

（四）循环使用

公式—错误检查—循环引用

（五）公式快速填空

1、拖动填空柄

2、双击填充柄

3、CTRL+D

4、选择性粘贴

（六）只显示公式

快捷键：CTRL+~

（七）公式调试

1、按F9，选中一个区域，按F9，可看到对应的计算结果。如选中一个区域，按F9，按回车，直接返回，公式则不显示。

2、公式—公式求值—依次显示值，如中间出现错误，可以快速找到。

错误检查—追踪错误—错误原因显示—关于此错误的帮助—找到对应解决方法—修改。

（八）公式不检查

公式—计算选项—手动改自动—如不改自动的话按ctrl+s保存会自动运算一次。

（九）一次性选中公式中引用的单元格

按CTRL+[ （左方括号）快速找到公司对应的区域

（十）监视窗口

按CTRL+[ 快速跳转对应的区域—公式—监视窗口—添加监视—选择区域（如A1:）—显示（工作薄、工作表、名称、单元格、值、公式）—数量修改—监视区的结果会变更。

二、运算符

（一）1、算术运算符：＋－＊  /％  ^

含义依次为加、减、乘、除、百分数、乘方Shift+6(英文状态下)

作用：完成基本的数学运算，产生数字结果

2、比较运算符：=< >>= <>

含义依次为等于、大于、小于、大于等于、小于等于、不等于

作用：比较两个值，结果为一个逻辑值：TRUE或FALSE

3、文本连接符

& 用SHIFT+7(英文状态下)

作用：连接两个字符串以合并成一个长文本

4、引用运算符

冒号：连续区域运算

逗号：将多个引用合并为一个引用

空格：取多个引用的交集为一个引用（交叉点的值）

5、通配符

*：任意多个字符

？：单个占位符，占1个字符位置

（二）运算优先顺序：

括号（）→百分比%→乘方^ →乘*、除// →加+、减

1、冒号、逗号、空格

2、负号

3、百分比

4、乘方

5、乘除

6、加减

7、&（连接）

8、= 、> 、<、>=、<=、<>

（三）支持通配符的函数

SUMIF、SUMIFS、COUNTIF、COUNTIFS、VERAGEIF、AVERAGEIFS、SEVECH/B、

MATCH、VLOOKUP

三、函数使用

（一）函数的插入方式：

1、手工插入，直接输入=

2、编辑栏上的fx图标

3、工具栏里快速录入通过开头字母的提示快速输入

4、alt+=是自动求和的快捷键

（二）查找函数用法

1、在插入函数窗口中，查找到该函数，点“有关该函数的帮助”

2、在输入函数时点函数提示的函数名称。

（三）函数嵌套

2个或2个以上函数综合应用。=TEXT(TODAY(),"YYYY年MM月DD日")

=TODAY()

例：TEXT和TODAY的嵌套应用。

（四）函数结构分析

IF和ROUND嵌套

四、函数的学习思路

要拓宽思路，学会用另外的方式去检查校验，保证百分百准确。一共有346个函数，51个常用的。

五、相对引用和绝对引用

引用是用工作簿、工作表和单元格地址来引用其中的值。

相对引用：引用的地址不是固定的。

绝对引用：引用的地址是固定的。按F4（快捷键）$,美元符号就是绝对引用了。

注：固定和不固定是指在复制单元格到其他位置时，引用的地址是否会发生变化

把绝对引用想象成美元。

一句话概括：在引用的单元格地址中添加$即为绝对引用，反之为相对引用。

行前添加$,复制公式时，行数不会发生变化

列前添加$,复制公式时，列数不会发生变化

六、利用乘法口诀表，加深记忆。

七、公式操作小技巧

1、隐藏和保护

选中公式—右键—设置单元格格式—Ctrl+1—保护—取消格式—审阅—保护工作表—输入密码—确定—公式看不到了（如果想取消，只要撤销工作表保护即可）

2、公式的快速复制

选中区域—Ctrl+End;Ctrl+上箭头—名称框中输入H197（定义名称）—shift+回车—编辑栏中选定公式—Ctrl+回车

3、把公式转换为数值

复制—粘贴值即可，或者使用快速访问栏的粘贴值。

4、合并单元格复制公式

函数公式—MAX($D$26:26)+1—CTRL+回车

5、快速修改公式

选中区域_—F2—快速修改

6、引用其他工作薄中数据

（1）直接用=引用本工作薄中的数据='[5.1.GPS定位.xlsx]1.基本用法'!$I$2

（2）编辑链接：数据—编辑链接—更新值、更改值、打开源文件、断开链接（根据需要勾选）

（八）快速替换公式

选中区域—Ctrl+H—替换内容—全部替换（注意不要把函数中的值替换）

八、公式返回的错误值，八个常见问题

九、快捷键的妙用

选中区域,，ALT+=，快速批量求和。（要掌握）

十、跨工作表求和

选中区域—输入=SUM公式—选第一个工作表按Shift至最后一个表—按Ctrl回车。