Excel-PAPAYA

list1

14.函数简介

=sum(范围)

=average(范围)

=max(范围)

=min(范围)

=large(范围,顺位)

=small(范围,顺位)

训练：分别使用

15.IF函数

=if(条件,成立,不成立)

训练：（1）确定是否及格（2）添加颜色——开始-样式-条件格式

=ifs(条件1,成立,条件2,成立,条件3,成立)

训练：分数区分ABC（90，80，60）

16.VLOOKUP

=vlookup(关键字,资料范围,栏数,查询模式)

关键字：查找条件

查询模式：false（精准），true（模糊）注：递增方式才能执行

绝对参照：将范围锁定不动 选择+F4

训练：使用vlookup根据学号查询姓名，总平均，成绩

17.IFERROR

=iferror(要检查的公式,"错误信息")

错误信息：输入若有误需要填写的内容

验证：选择验证数据——点击数据栏——点数据验证

备注：在判断是否为空值时，函数中双引号中间不必打空格

训练：（1）填写学号错误时，显示没有这个人，学号为未填写时，其余数据也为空

答案：=IF(C3= "","",IFERROR(VLOOKUP($C$3,$E$2:$K$12,2,FALSE),"没有这个人"))

（2）使用验证，在学号中执行信息提醒和出错警告

18.COUNTIFS和SUMIFS

=count(范围)——储存格总数，注：只能计算数字资料

=counta(范围)——计算所有非空的储存格总数

=countif(资料范围，条件)——计算符合条件储存格的总数，注：条件可以连接到储存格

=countifs(范围1,条件1,范围2,条件2)——同时满足条件1，2的数据的储存格，注：条件可使用比较运算符，比较运算符需要加上" "；只有储存格可以不加" "

&——串联文字和储存格的内容

=month(范围)——简化日期资料

=sumifs(加总范围,范围1,条件1,范围2,条件2)

训练：（1）计算为手机的储存格数量

（2）同时满足娱乐和信用卡的存储格

（3）信用卡中交易金额大于1000的交易次数

（4）信用卡在6月份的消费次数

（5） 信用卡在6月份的消费金额

19.定义名称 & INDIRECT 函数 & 下拉选单设定

命名：框选范围——左上角修改名称

sum(名称)，注：名称此时不需要""

训练：（1）D3:D7的命名为金额

（2）计算他们的总值

（3）使用countif求大于3000的金额有多少单元格

名称管理员：公式——定义名称——名称管理员

indirect(线索单元格)：以线索中（来自不同的储存格用&连接）的文字为跳板，取得目标储存格内容

训练：（1）根据中西早餐类型，选择他的下拉选单

11.数据透视表

滑鼠点击——插入——数据透视表

进一步了解某栏位的资料来源——点击储存格鼠标左键

修改值的显示方式——值字段设置

ctrl选择储存格——组合——修改组合名称

修改原表数据后需要在数据透视表中进行刷新，新增数据后需要更改数据源并重新选择范围

重新选择数据过于麻烦，可在进行数据透视前，可提前套用表格格式转换为动态表格，但还需刷新

训练：（1）对产品金额进行升序排序

（2）筛选产品只保留芒果、柚子、番茄（行标签）

（3）在筛选区筛选业务员的业绩，再形成二维平面表格

（4）产品，业务员（行），金额总、百分比（值）

（5）将业务员分为南区

（6）将日期分类为季、月、日

（7）插入日程表、插入数据透视表

20.12 个小技巧

1.资料剖析

场景1：粘贴资料到excel中，资料挤在同一个栏位，没有分栏

方式：选取范围——数据-分列-原始数据类型（分隔符号）-分隔符号（空格）

场景2：分割姓和名

方式：右侧插入空白栏——选取姓名资料——数据-分列-原始数据类型（固定宽度）-数据预览滑动分栏线

2.快速选取资料

场景1：快速选取资料范围

方式：ctrl+shift+方向键——移动到表格的四角

场景2：选取整张工作表

方式：左上角三角形

3.插入多个空白列

场景1：插入多个空白列

方式：插入一个空白列——f4（重复上一个指令）

场景2：插入5个空白列

方式：选取5列——插入

4.储存格选取框

场景：用选取框来进行复制、剪切、插入

方式：选取内容——移动（剪切）

选取内容——ctrl+拖移（复制）

选取内容——shift+拖移（插入不覆盖）

5.移除重复资料

场景：移除重复资料

方式：数据-删除重复值

6.表格转置

场景：行列对调

方式：选取表格——复制——粘贴-选择性贴上（转置）

7.贴上的运算值

场景：用选择性贴上给英文分数增加5分

方式：空白框输入5——复制——框选所有同学英文成绩——选择性贴上（运算-加）

8.显示公式

场景：浏览整张工作表的公式

方式1：公式-显示公式

方式2：ctrl+~

9.目标搜寻

场景：知道某一位同学的国语和数学，计算他的英文分数才能达到60分的标准

方式：数据-模拟分析-单变量求解-目标单元格（总平均）-目标值（60）-变数单元格（英文）

10.表格绘制对角线

场景：在表格左上角绘制对角线

方式1：框线-绘制框线-框线（关闭画笔）

方式2：右键-设置单元格格式-边框-选择对角线类型

11.储存格内换行

场景：储存格内换行

方式：alt+enter

12.插入图片注解

场景：插入图片批注

方式：右键-插入批注-删作者名称-批注边框-右键-设置批注格式-颜色和线条-颜色-填充效果-图片-选取图片

21.自定数值格式

问题：输入序号或手机号码时，前面的0会被excel移除

数值格式：控制数值要如何显示在工作表内，对数据更换任何数值格式，只会影响数值如何呈现，不会影响数值的本身

场景1：输入分数1/2和日期1月2日

方式：日期 1/2；分数 0空白键1/2

场景2：保留数字前的0位数

方式1：单引号(')数字 [注：被认定为文字类型的资料]

方式2：开始——数字-文本

自定数值格式

框选储存格——开始——数字【右下角】——数字-自定义-类型中输入格式

快捷键：Ctrl+！

1.#

#：代表数值一个位数的预留位置，遇到无意义的0将不会显示

场景：将考核分数的类型设置为小数点后保留1位

方式：#.#

2.？

？：一个位数的预留位置，遇到无意义的0将会以空格方式呈现

场景：将考核分数中的小数点相互对齐

方式：#.?

3.0

0：一个位数的预留位置,强制显示每一个指定的位数，不管这个0是否有意义

【0代码很适合用来处理像是序号或电话号码等具有固定位数的资料】

场景1：将手机号码以手机号码的类型显示，中间加-

方式1：0000-000000

场景2：制定5位数的员工ID，如00001

方式2：00000

场景3：制定员工ID，如T00001

方式3："T"00000

注：在数值代码的开头或末尾添加文字[注：" "]

4.@

@：代表文字预留位置

场景：在等第后添加等这个文字

方式：@"等"

5.*

*：重复你所指定的符号，直到填满目前的储存格为止

场景1：在副标目录中目录标题的后面填满点

方式：@*.

场景2：薪资中将货币符号移动到存储格的最左侧，数值在最右侧

方式：$*【空格】0

6.,

,：千分位,可配合英文单位的缩写

场景：将员工薪资将千位以下的金额简化为k，将33000显示为33k

方式：$* 0,"k"

7.0.00;(0.00);0.00;@

场景1：将格式套入成长率，并将正负值小数点对齐

备注：_(底线，利用接续符号的宽度来增加留白)

方式：0.00_);(0.00);0.00_);@

0.00_)：代表在正值的右边增加一个右括号的宽度

场景2：成长率设置不同的色彩，蓝正红负

备注：根据不同的区段设置不同的颜色，颜色代码输入在该区域的前面，颜色的代码方式2种，第一种是直接使用颜色的名称，举例：[红色]，第二种使用色彩的调色盘，色彩后填写色彩标号，举例：[色彩33]

方式：[蓝色]0.00_);[红色](0.00);0.00_);@

备注：如果只填写了一个区段的代码，这个代码也会套用到正值以外的负值和零值，如果你设定了两个区段的代码，则第一组会套用到正值和零值，第二组则会套用到负值

[蓝色]0.00

[蓝色]0.00_);[红色](0.00)

[蓝色]0.00_);[红色](0.00);0.00_)

场景3：考核分数中>90的分数设置为绿色，80-90的分数设置为青色,其余数值依然为黑色

备注：设置条件式的语法，一个自订格式最多可以设定两个条件，需要使用3个以上的条件，最好使用条件格式

方式：[>90][绿色]0.00;[>80][蓝色](0.00);0.00_)

注意：青色好像无法设置

list2

25.INDEX & MATCH

1.VLOOKUP

=vlookup(关键字,资料范围,传回的栏数,查询模式)

缺点：你的关键字并不是在表格的最左侧或者你的表格采用了横向排列（图例1），那么vlookup就不能使用了

图例1

2.HLOOKUP

=hlookup(关键字,资料范围,传回的栏数,查询模式)

使用条件：表格横向排列、要查询的关键词列也恰巧是第一列

区别和相同：他们的查询方向不同，而函数的结构是完全一样,他们都只能进行单向的查询，而无法从条件符合的单位中同时进行左右和上下的查询

训练：查询编号为105的员工业绩--86000

答案：=HLOOKUP(C2,C5:J7,3)

3.INDEX & MATCH

3.1 INDEX

=INDEX(一行或一列的范围,第几位)

返回值：小美

返回值：小轩

=INDEX(表格的范围,行数，列数)

返回值：小玲

3.2 MATCH

区别：

index：指定一个顺位，返回这个人的姓名

match：指定这个人的姓名，返回这个人的顺位

=MATCH(查找的对象、关键词,查找的范围,比对方式)

查找的范围：范围必须是单行或单列，不能向index可以含括整个表格

比对方式：1---小于，0---完全符合，-1---大于

返回值：2

返回值：3

MATCH函数采用近似值的比对场景：分数区间

训练：查询88分是落在哪一个区间，利用这个顺位，进一步找出88分的等第，最后将两个公式试着简化为一个

答案：=MATCH(F3,B3:B7,1)---返回值4；=INDEX(C3:C7,4)----返回值甲

=INDEX(C3:C7,MATCH(F3,B3:B7,1))----返回值甲

训练：借由业务员姓名的输入来查询它是在哪一个分公司，目前的业绩如何，以及他们的考级等第。

难点：在于业务员并不是在表格的最左列，vlookup就不能使用了

答案：1.查询表设计下拉选单：

数据-数据工具-数据验证-设置为(序列)来源(分公司,业绩,考绩)

数据-数据工具-数据验证-设置为(序列)来源($D$3:$D$10)

2.行----=MATCH(I2,D3:D10,0)

3.行----=MATCH(H3,C2:F2,0)

4.答案：=INDEX(C3:F10,MATCH(I2,D3:D10,0),MATCH(H3,C2:F2,0))

27.重复资料

1.颜色标注重复资料

框选表格——开始——条件格式——突出显示单元格规则——重复值

缺点：除了这两笔重复的订单之外，Excel也把重复出现的业务员姓名标注。

训练：在进行资料比对时，不能只着眼于单栏的文字或数值，而是所有栏位的内容都完全相符，才能被称为重复的资料。

1. 在总金额栏的旁边建立一个辅助栏，输入公式（=A2&B2&C2&D2）将这四个储存格串联起来

问题：如果我们直接利用醒目提示的功能来寻找重复的值，会导致颜色只显示在辅助栏内，而不是旁边的表格。

如果某笔资料没有任何重复，最后计数结果应该是1。反之，计算结果是大于1的话，就代表了这笔资料在表格中有重复出现。

2.框选表格——开始——条件格式——新建规则——使用公式确定要设置格式的单元格——输入公式——修改后一参数值为相对参照——格式——填充——选择颜色——确定

公式：=countif($E$2:$E$14,$E2)>1

注意：当这个公式向下复制时，条件所在的列数也必须一同改变，才能计算出每笔订单所出现的次数。因此，将行数前的锁定符号删除掉。

3.隐藏辅助列

2.删除重复资料

放置表格任意位置——数据——删除重复值——勾选选项

用法：截取表格的清单内容，比如：想要得到业务员的名单，复制业务员的姓名，执行删除重复值就可以获取一份不会重复业务员姓名。

3.防止输入重复资料

训练：确保订单编号列不能出现重复的订单编号

1.选取A列——数据——数据验证——允许中选定自定义——填入公式

公式：=countif(A:A,A1)=1

注意：当公式向下复制的时候，A1储存格也会顺势变成A2、A3、A4，公式的后面=1代表只有订单编号不重复的情况之下，才接受该笔资料的输入。

1. 设置出错警告

list3

28.随机函数

应用：抽奖活动和课堂分组

假设：公司举办了一个摸彩活动，我们要从这12位员工之中抽出一名幸运的得主

函数：RANDBETWEEN(最大值,最小值)——从这个范围中随机产生一个整数

问题：将随机函数得到的数字转换为员工的姓名

方法：index函数——index(资料范围,顺位)——返回栏位资料

答案：=index(c3:c14,randbetween(1,12))

注释：fn+f9——重新运算随机函数

假设：今天公司的有一场考试，考试的题目分为a卷和b卷，以随机的方式来分配试卷

函数：choose(答案,选项a,选项b…)

答案1：if(randbetween(1,2)=1,"A","B")

答案2：index($G$3:$G$4,randbetween(1,2))——设置辅助表格

答案3:choose(randbetween(1,2),"A卷","B卷")

假设：今天我要将12个员工，分为ab两组，一组6个人来进行一项竞赛

问题：解决每组人数不平均

函数：rand()——产生一个介于0~1之间的小数

rank(自己,比较的对象)——排名函数

roundup(数值,位数)——无条件进位函数

位数为0时，数值进位到整数位

位数为1时，数值进位到小数第1位

数值为-1时，数值进位到10位数

答案：

在分组栏的左侧插入一个空白栏，取名为乱数

乱数：rand()

分组：rank(D3,$D$3:$D$14)

rank(D3,$D$3:$D$14)/6—分成≤1和>1两种

套用无条件进位，即得到整数1或者是整数2

roundup(rank(D3,$D$3:$D$14)/6,0)——数值会进位到最接近的整数

choose(roundup(rank(D3,$D$3:$D$14)/6,0),"A组","B组")

注释：当我们在其他的储存格，做了任何的编辑，Rand函数都会产生新的随机数字，导致每个人所在的组别又会被立即更新

因此当每个人所分到的组别已经确定之后，复制乱数的数据，选择性贴上——值，这时候每个人的组别就不会有变动了