day27 学习总结

day27 学习总结

1.添加辅助列

• 操作方法：在第一列前插入新的一列，输入等差数列编号1,3,5,7.。。。在数据列编号完成后，再一次编号2，4,6,8.。。。最后按照序列的值进行排序

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pZdQ1bvB-1661303513079)(C:\Users\wuson\AppData\Roaming\Typora\typora-user-images\image-20220823172637913.png)]

2.拆分

• 操作：数据 --> 分列

• 如下，我们首先需要选中需要拆分的区域，然后点击数据，点击分列，选择逗号分割

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gzl09Pm7-1661303513081)(C:\Users\wuson\AppData\Roaming\Typora\typora-user-images\image-20220823173331683.png)]

• 点击下一步，选择一个列数据格式，并选定一个目标区域，完成

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3K0BaRIk-1661303513082)(C:\Users\wuson\AppData\Roaming\Typora\typora-user-images\image-20220823173448087.png)]

3. Excel 公式

3.1使用 Excel 进行数学计算

• 可以在 Excel 中进行加、减、乘、除运算，无需使用任何内置函数。只需使用部分基本运算符：+、-、*、/。所有公式均以等号 (=) 开头。如以下例子

• 要使用的数：	运算：	公式：	运算结果：
  1（单元格地址c3）	加法 (+)	=c3+c4	3
  2（单元格地址c4）	减法 (-)	=c3-c4	-1
  	乘法 (*)	=c3*c4	2
  	除法 (/)	=c3/c4	0.5
  	幂 (^)	=c3^c4	1

• 给单元格提供内容的方式：

  • 直接输入数据 - 输入的是什么，单元格的内容就是什么
  • “=单元格地址” - 直接引用单元格内容
  • = excel公式 ， - 计算公式结果来给单元格提供内容（公式中涉及到的数据可以是具体的数据，也可以是引用单元格内容）
  • = 函数表达式，获取函数返回值，来提供单元格内容。

3.2 单元格地址引用

• 注意：引用单元格内容的时候，如果引用单元格地址没有锁定，往下拉单元格行号不断加1，往右拉单元格，列号不断加1(F4----锁定行和列)
  • D1 - 行号和列号都不锁定
  • $D$1 - 行号列号都锁定
  • $D1 - 列号锁定，行号不锁定
  • D$1 – 行号锁定，列号不锁定

3.3 单列变多列

• 方法1：找到第一行和原始数据的引用关系，写出引用，然后直接往下拖

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WEIwx434-1661303513084)(C:\Users\wuson\AppData\Roaming\Typora\typora-user-images\image-20220823172008406.png)]

• 方法2: 直接通过写出引用地址关系，然后将A变成"=A"

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ulRvKYm8-1661303513085)(C:\Users\wuson\AppData\Roaming\Typora\typora-user-images\image-20220823172144082.png)]

3.4 多列变单列

• 操作步骤:
  1. 通过引用复制原来的数据

​ 2. 在第一列的最后引用第二列第一个数

​ 3.按住第一列最后添加的单元格往右拖，再往下托（拖100行）

​ 4.选择性粘贴第一列数据的数值，删除多余数据

4 excel函数

4.1文本函数

文本函数:	释义	举例	结果
LEFT()	从文本字符串的第一个字符开始返回指定个数的字符	=LEFT(H1,3)	忍一时	忍一时风平 浪静，退一步越想越气。
LEFTB()	基于所指定的字节数返回文本字符串中的第一个或前几个字符	=LEFTB(H1,6)	忍一时	忍一时风 平浪静，退一步越想越气。
RIGHT()	根据所指定的字符数返回文本字符串中最后一个或多个字符	=RIGHT(H1,4)	想越气。	忍一时风平浪静，退一 步越想越气。
RIGHTB()	根据所指定的字节数返回文本字符串中最后一个或多个字符	=RIGHTB(H1,4)	气。	忍一时风平浪静，退一步越想越气。
MID()	返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。	=MID(H1,5,5)	平浪静，退	忍一时风平浪静，退一步越想越气。
MIDB()	根据您指定的字节数，返回文本字符串中从指定位置开始的特定数目的字符。	=MIDB(H1,14,8)	，退一	忍一时风平浪静，退一步越想越气。
LEN()	返回文本字符串中的字符个数	=LEN(H1)	16	忍一时风平浪静，退一步越想越气
LENB()	返回文本字符串中用于代表字符的字节数。	=LENB(H1)	32	忍一时风平浪静，退一步越想越气。
FIND()	用于从一个文本串中定位另一个文本串的起点并返回字符数	=FIND(“退一”,H1)	9	忍一时风平浪静，退一步越想越气
FINDB()	用于从一个文本串中定位另一个文本串的起点并返回字节数	=FINDB(“时”,H1,2)	5	忍一时风平浪静，退一步越想越气
SEARCH()	用于从一个文本串中定位另一个文本串的起点并返回字符数	=SEARCH(“时”,H1)	3	忍一时风平浪静，退一步越想越气
SEARCHB()	用于从一个文本串中定位另一个文本串的起点并返回字节数	=SEARCHB(“时”,H1,2)	5	忍一时风平浪静，退一步越想越气
REPLACE()	将旧字符串的指定位置（字符下标）指定字符数的字符串替换为新字符串	=REPLACE(H1,3,1,“会儿”)	忍一会儿风平浪静，退一步越想越气。	忍一时风平浪静，退一步越想越气
REPLACEB()	将旧字符串的指定位置（字节下标）指定字节数的字符串替换为新字符串	=REPLACEB(H1,5,3,“会儿”)	忍一会儿 平浪静，退一步越想越气。	忍一时风平浪静，退一步越想越气
LOWER()	将一个文本字符串中的所有大写字母转换为小写字母。	=LOWER(“BGAni你好”)	bgani你好
UPPER()	将文本转换为大写字母。	=UPPER(“BGAni你好”)	BGANI你好
REPT()	将文本重复一定次数	=REPT(H17,2)	BGANI你好BGANI你好
SUBSTITUTE()	在文本字符串中用新文本替换旧文本	=SUBSTITUTE(H1,“一”,“会儿”,1)	忍会儿时风平浪静，退一步越想越气。	忍一时风平浪静，退一步越想越气
TRIM()	除了单词之间的单个空格之外，移除文本中的所有空格	=TRIM(I20)	喝酒 和 BFE FE FE A	喝酒 和 BFE FE FE A
TEXT()	自定义单元格格式（可点击超链接）	=TEXT(I21,“$0.00元”)	$12.46元	12.45681
VALUE()	将表示数字的文本字符串转换为数字	=VALUE(2010/10/1)	201

4.2数学函数

数学函数		举例	D列
ABS()	返回数字的绝对值	=ABS(-5)	5
INT()	向小取整	=INT(-5.49)	-6
MOD()	返回两数相除的余数（结果的符号和余数相同）	=MOD(20/3)	2
RAND()	[0,1)之间的随机数	=RAND()	0.667946974
RANDBETWEEN()	[a,b]之间的随机整数	=RANDBETWEEN(10,50)	29
ROUND()	将数字四舍五入到指定位数	=ROUND(0.6679)	0.67
SQRT()	返回算术平方根	=SQRT(29)	5.385164807
SUM()	将单个值、单元格引用或者区域相加，或者将三者的组合相加	=SUM(D1:D3)	1
SUMIF()	对范围中符合指定条件的值求和
SUMIFS()	计算满足多条件的全部参数的值

• ROUND()函数说明

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XDuVb0dE-1661303513086)(C:\Users\wuson\AppData\Roaming\Typora\typora-user-images\image-20220823200621639.png)]

• sumif举例说明，

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vqKWoJ9w-1661303513087)(C:\Users\wuson\AppData\Roaming\Typora\typora-user-images\image-20220823201121682.png)]

​ 1.“水果”类别下所有食物的销售额之和 - =SUMIF(E28:E33,“水果”,G28:G33)

​ 2.“蔬菜”类别下所有食物的销售额之和 - =SUMIF(E28:E33,“蔬菜”,G28:G33)

​ 3.以“西”开头的所有食物（西红柿、西芹）的销售额之和 - =SUMIF(F28:F33,“西*”,G28:G33)

​ 4.未指定类别的所有食物的销售额之和 - =SUMIF(E28:E33,“”,G28:G33)

• sumifs举例说明

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ivcmK1Fu-1661303513088)(C:\Users\wuson\AppData\Roaming\Typora\typora-user-images\image-20220823201725618.png)]

  1.计算以“香”开头并由“卢宁”售出的产品的总量 - =SUMIFS(E42:E49,F42:F49,“香*”,G42:G49,“卢宁”)

  2.计算卢宁售出的非香蕉产品的总量 - =SUMIFS(E42:E49,G42:G49,G42,F42:F49,“<>香蕉”）

• 注意：

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8NFFhi6x-1661303513089)(C:\Users\wuson\AppData\Roaming\Typora\typora-user-images\image-20220823202234300.png)]

4.3统计函数

AVERAGE()	返回其参数的平均值(求指定范围内所有数字的和再除以数字的个数)
AVERAGEA()	返回其参数的平均值，包括数字、文本和逻辑值（求指定范围内所有数字的和再除以所有数据的个数)）
AVERAGEIF()	返回区域中满足给定条件的所有单元格的平均值
AVERAGEIFS()	返回满足多个条件的所有单元格的平均值
COUNT()	计算参数列表中数字的个数
COUNTA()	计算参数列表中值的个数
COUNTIF()	计算区域内符合给定条件的单元格的数量
COUNTIFS()	计算区域内符合多个条件的单元格的数量
FREQUENCY()	以垂直数组的形式返回频率分布（分箱）（FREQUENCY（统计对象范围，分箱节点对应的范围）
MAX()/MIN()	返回参数列表中的最大（最小）值
RANK()	返回一列数字的数字排位（数字排位是相对于列表中其他值的大小）
RANK.AVG()	返回一列数字的数字排位（数字排位是相对于列表中其他值的大小，如果多个值具有相同的排位，则返回平均排位）
RANK.EQ()	返回一列数字的数字排位，其大小与列表中其他值相关，如果多个值具有相同的排位，则返回该组值的最高排位

• AVERAGEIF举例说明：

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QWtj1qdu-1661303513090)(C:\Users\wuson\AppData\Roaming\Typora\typora-user-images\image-20220823202702191.png)]

• 求所有佣金小于23000的平均值 - =AVERAGEIF(B19:B22,“<23000”)

• 求所有财产值大于250000的佣金的平均值 - =AVERAGEIF(A19:A22,“>250000”,B19:B22)

• COUNTIFS举例说明：

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ws1OsLSX-1661303513091)(C:\Users\wuson\AppData\Roaming\Typora\typora-user-images\image-20220823204604360.png)]

• 1.计算单元格A29到A34中包含小于5的数，同时在但与那个B29到B34中包含早于2011/5/3的日期的行数

  =COUNTIFS(A29:A34,“<5”,B29:B34,“<2011/5/3”)

• 2.计算1和6之间（不包含1和6）有几个数包含在单元格A29到A34中

  =COUNTIFS(A29:A34,“>1”,A29:A34,“<6”)

• FREQUENCY()举例：这里有两种分箱方法，要根据实际场景选择如何分箱

  分数	实际分箱	意义	数量		分箱	意义	数量
  79	59	60分以下	2		60	<=60	3
  88	69	[60,70)	4		70	<=70,>60	4
  63	79	[70,80)	2		80	<=80,>70	1
  39	89	[80,90)	3		90	<=90,>80	5
  90		>=90	2			>90	0
  87
  66
  60
  70
  65
  90
  88

    |      |      |      |          |      |
  

  | 90 | | | | | | | |
  | 88 | | | | | | | |
  | 54 | | | | | | | |