Excel（2）

认识函数与公式

一、认识Excel公式

  1. 运算符

    公式：=开头，但=不表示等于，写公式以等号开始。点击一项单元格，写上运算符，再点击一项单元格，回车。

    算术运算符：+ - * / （加减乘除，也可对文本运算）%（百分号，除以100） &（连字符，用户文本连接、数字连接）^(乘方)

=G12+H12

=G13&H13

​ 文本不可直接求和，加0或者乘以1后，会转为数值，可以求和。

​ 在一个单元格中填写公式后，点击单元格右下角拖拽或在此直接双击可以填充一列。

  1. 公式中的比较判断

    比较运算符： = > < >= <= <>（不等于）；得到结果为true或false（false为0，true为1）

    公式中文本要用英文双引号引起来。

=(D6=“本地”)*30+E6

  1. 单元格引用

    $代表锁定的意思

    相对引用：无$

=F4+J4

​ 绝对引用：选中要固定的值 eg: J4，按F4，变为绝对引用，有两个$

=F4+$L$4

​ 混合引用：一个$（既要向下又要向右拖动，要考虑两个方向）

九九乘法表：=$B23*C$22

二、认识函数

  1. 如何使用函数

    =函数名(参数)

    参数是选择的一个区域。

  2. 学习以下函数

    sum, average, max, min, rank（排什么,在哪里排记得F4锁定区域)

=SUM(D5:G5)

=AVERAGE(D5:G5)

=MAX(D5:D11)

=MIN(D5:D11)

=RANK(H5,$H 5 : 5: 5:H$11)

​ 引用为了拖拽而服务，要怎么拖拽就怎么引用。

  1. 利用定位工具选择插入公式的位置

    跳跃式求和：选中该列—编辑—查找—转到–定位条件–空值–确定–开始标签右上角自动求和工具（自动选区）

    跳跃式求和：选中整个表—编辑—查找—转到–定位条件–空值–确定—写好公式—control+回车（批量填充）

三、IF函数逻辑判断

if函数和其他函数不同，是用来判断逻辑值的函数，不是数学类的函数。

  1. 基本IF用法

    ​如果E2为男，那么称呼为先生，否则为女士。

=IF(E2=“男“,”先生“,”女士”)

  1. IF函数的嵌套

    例：针对专业类，填上相应的专业代号。如果C2为理工，专业代号为LG；否则若C2为文科，那么专业代号为WK，否则专业代号为CJ。

=IF(B2=“理工”,“LG”,IF(B2=“文科”,“WK”,“CJ”))

  1. 例：考生来源为本地，则加上三十分，考生来源为本省，加上二十分，如果是外省，加上十分。

=IF(G2=“本地”,H2+30,IF(G2=“本省”,H2+20,H2+10))

  1. 例：总分为600分及以上，录取情况为第一批；总分400-600为第二批；总分400分以下落榜。

=IF(I2>=600,“第一批”,IF(I2>400,“第二批”,“落榜”))

  1. 2003里IF只允许最多七层嵌套。如果超过三层以上的嵌套，那就考虑用别的函数了。

=IF(G6=“A级”,10000,0)+IF(G6=“B级”,9000,0)+IF(G6=“C级”,8000,0)+IF(G6=“D级”,7000,0)

=IF(G22=“A级”,“一级”,“”)&IF(G22=“B级”,“二级”,“”)&IF(G22=“C级”,“三级”,“”)

  1. IF+ISERROR函数的运用

    ISERROR代表对值的正确或者错误的判定，返回True或False。

    ​例：如果D35除以C35是个错误值，就返回为0；如果不是错误，就直接运算D35/C35。

=IF(ISERROR(D35/C35),0,D35/C35)

四、AND函数与OR函数

  1. AND的用法（且）

=IF(AND(A3=“男”,B3>=60),1000,0)

  1. OR的用法（或）

=IF(OR(B12>=60,B12<40),1000,0)

  1. IF和AND和OR的组合用法

=IF(OR(AND(B20>60,A20=“男”),AND(B20<40,A20=“女”)),1000,0)

五、COUNTIF函数

  1. COUNT函数（数数）

    一个区域中有多少条数据

=COUNT(F:F)

  1. COUNTIF函数语法

    ​计算区域中满足给定条件的单元格的个数

    ​=COUNTIF(区域,条件)

=COUNTIF(E:E,H8)

  1. COUNTIF函数计算数值区间

    =COUNTIF(B2:G2,“>=60”)

  2. 字符串超过15位时COUNTIF函数无法判断

    解决方法： =COUNTIF(A2:A3,A2&“*”)

=COUNTIF($A 8 : 8: 8:A$20,A8&“*”)

  1. 在数据区域中寻找重复数据

=IF(COUNTIF(G:G,A2)=0,“未体检”,“已体检”)

  1. 在数据有效性中使用COUNTIF函数

    //一组值或一个区域做数据有效性时和条件格式一样，只针对白色的单元格写公式

    将C列设置为禁止输入重复数据：数据–数据验证–允许：自定义–公式：=COUNTIF(C:C,C1)<2

    ​将D2:I20设置为禁止输入重复数据：数据–数据验证–允许：自定义–公式：=COUNTIF($D 2 : 2: 2:I$20,D2)<2

    1. 在条件格式中使用COUNTIF函数

    选中区域—开始选项卡–条件格式–新建规则—样式：经典–使用公式确定要设置格式的单元格–输入公式=COUNTIF(G:G,A2)=0—设置格式–确定

    将银行卡号重复数据标记为红色背景：选中区域—开始选项卡–条件格式–新建规则—样式：经典–使用公式确定要设置格式的单元格–输入公式=COUNTIF($E 2 : 2: 2:E$14,E2&“*”)>1—设置格式–确定

    1. COUNTIFS函数（多条件的COUNTIF）

    计算多个区域中满足给定条件的单元格个数

    =COUNTIFS(区域1,条件1,区域2,条件2,…)

=COUNTIFS(E:E,J5,D:D,I5)

六、SUMIF函数

  1. SUMIF函数

    ​=sumif(区域,条件,求和区域)

=SUMIF(E:E,H8,F:F)

=SUMIF(F:F,“>=500”,F:F)

  1. SUMIF函数超过15位字符时 &“*”

=SUMIF(A:A,F3&“*”,B:B)

  1. 第三个参数的简写

    原则上第一个参数和第三个参数区间应该一样，但由于Excel有强大的容错能力，无论第三个参数写什么，第三个参数都会以第一个参数区间对齐，所以第三个参数可以选择一个单元格，而不用选整列。

    =SUMIF(D:D,H4,F:F) 第三个参数可以简写为 =SUMIF(D:D,H4,F1)

    ​注意：简写选的单元格应该和第一个参数的第一个单元格平齐对应！小心错位！！！！

  2. 在多列中使用SUMIF函数

=SUMIF(A1:I11,L3,$B$1)

  1. 使用辅助列处理多条件的SUMIF

    在任意地方插入一个辅助列，用&使多个条件组合在一起。

    列：一车间邮寄费，公式为 =SUMIF(A:A,J5&K5,G:G) 其中A列为创建的辅助列 =E2&F2

  2. SUMIFS函数

    参数顺序和SUMIF不一样，第一个参数是求和区域。

    =SUMIFS(求和区域,条件1区域,条件1,条件2区域,条件2…)

=SUMIFS(F:F,D:D,I5,E:E,J5)

  1. 用SUMIF函数替代vlookup函数（只能返回数值，因为文本不能求和）

=SUMIF(A:A,M4,J:J)

  1. 用数据有效性做下拉列表

    产品类别限制：选中产品列—数据选项卡—数据验证—允许：序列–选择数据来源=$A 2 : 2: 2:A$7–制作成下拉列表

    数量之和的限制：选中数量列—数据选项卡—数据验证—允许：自定义—公式为 =sumif(F:F,F3,G:G)<=sumif(A:A,F3,B:B)

七、VLOOKUP函数（重点）

  1. VLOOKUP函数语法

    到隔壁办公室找老张，把老张桌子上的水杯拿过来。

    （1）找谁（2）到哪去找（3）要干嘛

    ​VLOOKUP查找引用函数：在某个区域里找到某个数据，并且将数据有关系的某个值拿过来。

    ​使用VLOOKUP时要求一行是一条完整的记录，且都是有关联的，一列是一个属性。

    ​VLOOKUP要求找的是不重复的，若有重复的则只能找到重复项中的第一个。

    ​VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

    ​第一个参数：要在第二个参数的区域里找的谁

    ​第二个参数：区域要包含查找列和引用列（姓名&原始分（根据姓名找原始分）），且查找列（姓名列）必须是区域的最左列。【注意：数据区域需要绝对引用，按F4（如果选中整列则无需考虑）】

    ​第三个参数： 要返回第二个参数的区域（而非整个表格）里的第几列

    ​第四个参数： 0为精确匹配，1为模糊匹配（大多用0）

=VLOOKUP(G7,$B 5 : 5: 5:E$10,4,0)

  1. VLOOKUP跨表引用

    应用场景：两个表相互比较时，需要用到跨表引用

    跨表引用：数据源!A:B，跨表选取后立刻输逗号

=VLOOKUP(A2,数据源!A:B,2,0)

  1. 使用通配符查找

注意：VLOOKUP不是模糊匹配，而是使用通配符！

=VLOOKUP(A2&“*”,数据源!B:E,4,0)

  1. 模糊匹配

    精确匹配：找的值在表中有，就要精确匹配去找，拿回来一个确定的值。

    模糊匹配：当你觉得值在表中没有，还要去找时，用模糊匹配。找小于等于lookup_value的最大值，常用于分档匹配。（档次过多，不适合使用IF的情况，常用VLOOKUP模糊匹配）

    使用模糊匹配时，要查找的那一列需要从小到大排序。

    例：婚介所表中有女性年龄、编号，要给男性按年龄找女朋友=VLOOKUP(D3,A2:B8,2,1)模糊匹配找出一个近似值，找小于等于自己的最大值。

=VLOOKUP(G9,$C 8 : 8: 8:D$13,2,1)

  1. 数字格式问题

    格式不匹配，不改原表的情况下：

原表是文本格式，lookup_value数字格式，数字转文本格式 =VLOOKUP(F4&“”,$A 2 : 2: 2:C$6,3,0)

原表是数字格式，lookup_value文本格式，文本转数字格式 =VLOOKUP(F12*1,$A 10 : 10: 10:C 14 , 3 , 0 ) 或 者 = V L O O K U P ( − − F 12 , 14,3,0) 或者 =VLOOKUP(--F12, 14,3,0)或者=VLOOKUP(−−F12,A 10 : 10: 10:C$14,3,0)

  1. ISNA函数

    ISNA函数：判断一个公式结果是否会发生#N/A错误。

既有文本又有数值的函数，先通过数值找一次，如果用数值找到发生#N/A错误，则再通过文本找一次。

=IF(ISNA(VLOOKUP(F20*1,$A$18:$C$22,3,0)),VLOOKUP(F20&"",$A$18:$C$22,3,0),VLOOKUP(F20*1,$A$18:$C$22,3,0))

VLOOKUP(F20*1,$A 18 : 18: 18:C$22,3,0) 用数值查找

VLOOKUP(F20&“”,$A 18 : 18: 18:C$22,3,0) 用文本查找

整个if函数意思：如果用数值查找是NA错误，则用文本查找，否则用数值查找。

  1. HLOOKUP函数

    要第几行的时候用，相当于竖过来的VLOOKUP。

    (1)找什么 (2)在哪几行找 （3）第几行

    HLOOKUP和VLOOKUP使用方法相同，不同之处在于方向不同。（一个是竖向一个是横向）

=HLOOKUP(B14,$1:$3,3,0)

  1. 个人所得税计算（要用模糊匹配）

    ​一个人月薪12500，计算个人所得税：3500为免税额度，12500-3500=9000为应税所得额，个人所得税=9000*税率-速扣数。

=IF(F7<=3500,0,VLOOKUP(F7-3500,$A 5 : 5: 5:C 12 , 3 , 1 ) ∗ ( F 7 − 3500 ) − V L O O K U P ( F 7 − 3500 , 12,3,1)*(F7-3500)-VLOOKUP(F7-3500, 12,3,1)∗(F7−3500)−VLOOKUP(F7−3500,A 5 : 5: 5:D$12,4,1))

八、MATCH+INDEX函数

  1. MATCH与INDEX函数语法

    VLOOKUP函数不适用自右向左查询案例，所以就用到查找函数与引用函数MATCH与INDEX。

    MATCH与INDEX函数一起使用可以代替VLOOKUP，甚至比V要强，因为V只能引用值，M&I甚至可以引用图片。MATCH返回单元格位置的列号。

    =MATCH(lookup_value,lookup_array,[match_type])

    =MATCH(查找谁,查找区域,是否精确查找)

    是否精确查找：0 精确匹配，1 小于，-1 大于

    ​=INDEX(array,row_num,[column_num])

    ​=INDEX(在哪里拿（范围）,拿第几个)

=INDEX(数据源!B:B,MATCH(查询!A2,数据源!A:A,0))

=INDEX(数据源!A:A,MATCH(查询2!A2,数据源!B:B,0))

​ 注意：跨表取区域记得及时点逗号后再返回原表！

  1. COLUMN函数

    混合引用方式：例：99乘法表（既要向右又要向下拖拽） =$A2*B$1

    COLUMN是判断单元格在第几列的函数，返回当前单元格的列号。

    例：A1的COLUMN函数 =COLUMN(A1) 结果为1

返回多列有顺序结果 =VLOOKUP( D 4 , 数 据 源 ! D4,数据源! D4,数据源!A:$K,COLUMN()-3,0)

返回多列跨顺序结果 =VLOOKUP( A 3 , 数 据 源 ! A3,数据源! A3,数据源!A:$K,MATCH(返回多列结果!B 2 , 数 据 源 ! 2,数据源! 2,数据源!A 1 : 1: 1:K$1,0),0)

​ 注意：涉及到MATCH函数，注意混合引用！

  1. 使用函数引用图片

    插入图片小技巧：按住alt键调整图片位置可以使图片自动贴着单元格。

    公式选项卡–定义名称—输入公式=index( D D D:$D 5 , M A T C H ( 5,MATCH( 5,MATCH(J 2 , 2, 2,A 3 : 3: 3:F$4,0))–取名“图片”

    选项–自定义功能区—所有命令–照相机，把出现的图片底框放在要找图的地方，在编辑栏里输入=图片

九、邮件合并

  1. 批量生成多个文档

    ​批量生成格式一致，部分信息不同的文档。

    ​新建word文档—邮件选项卡–开始邮件合并（信函）–选择收件人（使用现有列表，浏览 excel表格，完成表格关联）—开始写内容（插入合并域，显示excel关联的项目，再写共性的东西）—预览结果—完成并合并（编辑单个文档、打印文档、合并到电子邮件）

  2. 使用word发邮件

    邮件合并–…写好邮件–完成并合并（合并到电子邮件）（在outlook中给每个人都创建了一个邮件）

  3. 每页显示多条记录

    新建word文档—邮件选项卡–开始邮件合并（目录）–选择收件人（使用现有列表，浏览 excel表格，完成表格关联）—开始写内容（插入合并域，显示excel关联的项目，再写共性的东西）—预览结果—完成并合并（编辑单个文档）

    场景：题目在excel中，要把题目创建到word中时。

  4. 邮件合并后的数字/日期格式处理

    alt+F9查看显示值的域代码，

    数字格式如下（表示有千分位分隔符，小数点后两位），填写后按F9更新域。

    \# "#,##0.00"

    日期格式如下（月份一定大写，因为分钟的m与月份冲突），填写后按F9更新域。

    \@ "yyyy-M-d"

    （excel中查看单元格格式–自定义–类型，类型即为域代码的写法）

日期格式：alt+F9，打开域编辑器， @“yyy-M-d”(月份一定大写，因为分钟的m与月份冲突)。

十、 日期函数

  1. 日期与时间

    设置单元格格式—数字–常规，可以将日期转换为数值格式，值为整数，以1900年为基年。

    设置单元格格式—数字–数值，可以将时间转换为数值格式，值为小数，0.5是中午12:00:00。

    Excel中整数1代表一天，日期与时间实际上是把一个数字转换为其他的格式。

  2. 基本的日期与时间运算

    例：9:00 + 90分钟 把90分钟/24/60得到数字的分钟

=D4+E4/24/60

​ 例：12:00 - 9:00

(12:00-9:00)变为数值*60*24

=(E9-D9)*60*24

​ 例：2013/6/16 +70 都是整数，直接相加即可

=D14+E14

​ 例：2013/8/24 - 2013/6/16 都是整数，直接相减即可

=E18-D18

  1. 日期的推算

    例：2013/6/16 + 6个月

    先拆分日期，分别需要年月日三个函数： =YEAR(日期) =MONTH(日期) =DAY(日期)

    再组合年月日： =DATE(年,月,日)

=DATE(YEAR(B5),MONTH(B5)+C5,DAY(B5))

​ 求本月最后一天：用下个月的第一天减去一天。

=DATE(YEAR(B13),MONTH(B13)+1,1)-1 或 =DATE(YEAR(B13),MONTH(B13)+1,0)

​ DATE函数会自动完成进位，结果不会出现非法的日期。

​ 求本月天数：本月最后一天为几号即有几天。

=DAY(DATE(YEAR(B21),MONTH(B21)+1,0))

  1. 日期的间隔计算（精确运算）

    DATEDIF函数：用于比较两个日期的不同。

    =DATEDIF(开始日期,结束日期,“y”)

    第一个参数一定小于第二个参数，第三个参数确定两个日期不同点是什么（"y"为间隔的年份，“m”为间隔的月份，“d”为间隔的天数，"ym"刨除年份算月份，"md"刨除年份月份算天数，"yd"刨除年份算天数）

=DATEDIF(B5,C5,“y”)

=DATEDIF(B13,C13,“y”)&“年”&DATEDIF(B13,C13,“ym”)&“月”&DATEDIF(B13,C13,“md”)&“日”

  1. 星期的计算

    求第几周： =WEEKNUM(日期,哪天当作一周的第一天)

    （WEEKNUM的第二个参数：1为星期日，2为星期一……）

    求第几天： =WEEKDAY(日期,习惯的格式号)

    （WEEKDAY的第二个参数：1返回从1（星期日）到7（星期六）的数字，2返回从1（星期一）到7（星期日）的数字，3返回从0（星期一）到6（星期日）的数字……）

    例：求日期是一年的第几周？

=WEEKNUM(B3,2)

​ 例：求日期是这一周的第几天？

=WEEKDAY(B8,2)

​ 例：求日期是第几周的第几天？

=“第”&WEEKNUM(B13,2)&“周第”&WEEKDAY(B13,2)&“天”

  1. 整容函数TEXT

    整容函数TEXT：可以把化妆弄假成真（化妆是假的，整容是真的）。

    =TEXT(整谁,怎么整)

    例：将日期整容为星期。

    化妆：设置单元格格式-自定义-aaaa

=TEXT(B3,“aaaa”)

​ 例：将假日期20130616整容为真日期2013-06-16。

​ 化妆：设置单元格格式-自定义-0000-00-00 （0在自定义格式中表示占位符）

=TEXT(B10,“0000-00-00”)*1 得到一个数值之后–设置单元格格式—数字–日期

更多推荐

Excel基础教程（2）——函数与公式