数据科学、数据分析、人工智能、数学建模必备知识汇总-----Excel-----持续更新

已于 2025-04-03 15:09:32 修改
阅读量1.2k 收藏 27
点赞数 30
分类专栏： 新能源 文章标签： 新能源 笔记 Excel
于 2024-07-09 08:19:41 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/grd_java/article/details/140174090
版权
数据科学、数据分析、人工智能、数学建模必备知识汇总-----主目录-----持续更新(进不去说明我没写完)：https://blog.youkuaiyun.com/grd_java/article/details/140174015

文章目录

一、数据清洗

1. 去除空格

  1. 使用trim函数去除两边空格
    在这里插入图片描述
# 公式
=trim(text)
# 作用：去除单元格两端的空格（不包括中间的）
# 其中text表示要去除空格的字符串
  1. 查找替换去除全部空格，选中单元格后按快捷键Ctrl+H即可
  2. substitute函数替换
    在这里插入图片描述
# 公式
=substitute(text,old_text,new_text)
# 作用：字符串text中，将old_text全部替换为new_text

2. 字符串拼接

  1. &：and符号，表示和、与
    在这里插入图片描述
text1 & text2
#将text1和text2拼接起来
  1. concat函数：拼接指定内容
    在这里插入图片描述
=concat(text1,[text2],...)
# 拼接所有参数（text1,text2....）
  1. textjoin函数：以指定分隔符拼接
    在这里插入图片描述
textjoin(分隔符,TRUE或False是否忽略空单元格,要连接的内容)
# 以指定分隔符拼接单元格，其中第二个参数表示是否忽略空单元格

3. 字符串截取

  1. mid函数：提取字符串中间的字符
=mid(text,start_num,num_chars)
# 从start_num位置开始，从text中提取num_chars个字符

在这里插入图片描述

  1. left函数:提取字符串左边的字符
left(text,[num_chars])
# 从左边开始，从text中提取num_chars个字符，
# num_chars默认为1，如果不指定num_chars，只会提取1个字符

在这里插入图片描述

  1. right函数：提取字符串右边的字符
right(text,[num_chars])
# 从右边开始，从text中提取num_chars个字符，
# num_chars默认为1，如果不指定num_chars，只会提取1个字符

在这里插入图片描述

4. 替换函数

  1. replace: 替换字符串中子串
    在这里插入图片描述
=replace(text,start_num,num_chars,new_text)
# 从text中start_num位置开始数num_chars个字符，替换为new_text
  1. substitute: 替换字符串中子串
    在这里插入图片描述
=substitute(text,old_text,new_text)
# 作用：字符串text中，将old_text全部替换为new_text

5. 求长度

  1. len函数：返回文本字符串中字符个数，一个中文字符长度计算为1
    在这里插入图片描述
=len(text)
# 求text的长度，汉字长度算1
  1. lenb函数：和len唯一不同是，一个中文字符长度算作2
    在这里插入图片描述
=lenb(text)
# 求text的长度，汉字长度算2
  1. 求出汉字的个数
  1. 因为len函数求的是字符个数，而lenb会在len的基础上，每个汉字额外+1.
  2. 所以lenb(text) - len(text) = text中汉字个数
    在这里插入图片描述

6. 相对引用和绝对引用

  1. 相对引用就是公式会随着你位置的变化而自动变化的引用
    在这里插入图片描述
  1. 绝对引用就是无论你怎么拖动你的公式，绝对引用的单元格不会相对变动
  1. 绝对引用的方式就在单元格列号和行号前加上$符号
  2. 混合引用只是锁定列，例如$A1,只会锁定A列，行依然会变动。而如果是A$1就是只锁定行，列依然会变动
    在这里插入图片描述
  1. 引用方式切换的快捷键是F4，在公式中对目标引用按下快捷键可以切换引用模式
    在这里插入图片描述

7. 查找函数和排名排序

重点：模糊匹配表示近似匹配，会返回小于该数值的最大数值。例如在100,1000,5000,10000,20000里面模糊匹配12000，那么它会返回所有小于12000的值中最大的一个。也就是100,1000,5000,10000,20000里面小于12000的100,1000,5000,10000中的最大值，也就是10000
在这里插入图片描述

lookup
  1. vlookup：纵向查找,搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值，默认情况下，表以升序排序
    在这里插入图片描述
vlookup(lookup_value,table_array,col_index_num,range_lookup)
# 在table_array目标区域中，按列查找满足lookup——value查找条件的值。查找到结果时
# 只会显示指定的col_index_num列结果，range_lookup取值为TRUE(近似匹配)或False(精确匹配)
  1. hlookup：横向查找，有些表给的时候和上面的图的表不一样，是横向排列的，因此需要hlookup
    在这里插入图片描述
hlookup(lookup_value,table_array,row_index_num,[range_lookup])
# 和vlookup的区别是，hlookup是按行走，显示结果也是行
  1. xlookup函数：按行查找表或区域中的东西
  1. 多行(列)查找，这里直接演示比较难得行查找
    在这里插入图片描述
  2. 如果找不到，我们可以指定if_not_found参数，在没找到时显示指定值
    在这里插入图片描述
=xlookup(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
# lookup_value:要匹配的值
# lookup_array:要匹配的区域
# return_array:要返回的区域（hlookup和vlookup只能返回单行或单列）
# if_not_found:如果没有找到怎么办
# match_mode:如何进行匹配
# search_mode:如何进行搜索
index和match

在这里插入图片描述

着重掌握index嵌套match的方式，vlookup要求目标字段在第一列，而index和match不限制，要学会如何使用index+match替代vlookup

  1. index函数：根据位置返回单元格的值
index(array,row_num,[column_num])
# 根据指定行和和列号，返回指定区域中单元格的值
# array:查找的区域
# row_num:行号
# column：列号
  1. match函数：根据单元格的值返回位置
match(lookup_value,lookup_array,[match_type])
# 和index正好相反，match是根据单元格的值返回指定区域的行号和列号
rank排名函数

rank函数：返回一列数字的排名

  1. rank.eq：数字相同，排名相同
    在这里插入图片描述
rank.eq(number,ref,[order])
# number: 要排名的值
# ref：要排名的区域
# order:升序还是降序（0降序，1升序）
  1. rank.avg：排名相同，取平均
    在这里插入图片描述
rank.avg(number,ref,[order])
排序后如何顺位修正
  1. 直接用rank函数排序会出现相同名次的情况
    在这里插入图片描述
  2. 假设现在我们要进行顺位修正，第二个相同排名的自动顺位+1，例如第一个排名第3的就是第三，第二个排名第三的，自动顺位到第4

所以我们现在统计每个排名是第几次重复
在这里插入图片描述

  1. 知道了第几次重复后，就可以进行顺位了，也就是原rank排名+第几次重复 - 1就是修正后的顺位排名
    在这里插入图片描述

8. 逻辑运算

if
  1. 用于判断数字或变量之间的逻辑关系
    在这里插入图片描述
if(logical_test,value_if_true,[value_if_false])
# if(如果为True,执行指定操作,否则执行其它操作)
# 可以多层嵌套，和编程一样
and
  1. 逻辑判断，相当于并且操作。全部参数为True则返回True
    在这里插入图片描述
AND(条件1,条件2,.....)
# 所有条件都满足返回True
or
  1. 逻辑判断，相当于或操作，只要一个参数为True，则返回True
OR(条件1,条件2,....)
# 所以条件，只要有一个满足就返回TRUE

9. 计算统计类

9.1 count系列

count

计算包含数字的单元格个数,非数字不统计

count(value1,[value2],...)
# value1，value2...是计数的单元格区域
counta

计算区域非空单元格的个数

counta(value1,[value2],...)
countif

用于统计满足某个条件的单元格的数量，只能包含一个条件

countif(range,criteria)
# range：条件区域
# criteria：需要满足的具体条件
countifs

计数，多条件统计。也就是可以多个条件统计了

countifs(criteria_range1,criteria1,[criteria_range2,criteria2],....)
# criteria_range1,criteria1是第一个条件区域和条件
# [criteria_range2,criteria2],....第二个和后面的条件区域和条件

在这里插入图片描述

9.2 SUM系列

sum

求和，单个值，单元格引用或者区域引用的和

sum(number1,[number2],....)
#求number1,[number2],....的和
sumif

条件求和，只有满足条件的值才进行求和

sumif(range,criteria,[sum_range])
# range：条件区域
# criteria：条件
# [sum_range]：要求和区域，如果不指定，就将条件区域当为求和区域
sumifs

多条件求和

sumifs(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)
# sum_range：要求和区域
# criteria_range1,criteria1,[criteria_range2,criteria2],...
# 若干条件区域和条件

在这里插入图片描述

9.3 average系列

average

返回参数平均值（算术平均值）

average(number1,[number2],...)
# 求number1,[number2],...的平均值
averageif

返回某个区域内满足给定条件的所有单元格的平均值

averageif(range,criteria,[average_range])
# 求range区域中符合criteria条件的，指定average_range区域的平均值
averageifs

返回满足多个条件的所有单元格的平均值

averageifs(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)
# average_range 要求平均值的区域
# criteria_range1条件区域1
# criteria1条件1
# [criteria_range2,criteria2],...其余条件区域和条件

在这里插入图片描述

9.4 MAX和MIN

max

求最大值

min

求最小值

在这里插入图片描述

9.5 substotal

  1. 汇总函数，可以求平均值、计数、求和、最大/最小值、标准差、方差等。也就是说，如果你突然忘记了上面介绍的某个函数，这一个汇总函数，可以临时去用它做简单的代替。但它主要作用就对筛选敏感，想要完全替代上面介绍的函数是不可能的，这就是一个汇总函数
    在这里插入图片描述
substotal(函数编号,引用区域)
# 函数编号：你想用的函数对应编号，例如1表示average函数
# 引用区域：你想使用函数的区域
  1. 如果我们不进行筛选，substotal和普通函数结果是一样的
    在这里插入图片描述
  2. 如果进行筛选，substotal的结果是会根据筛选结果发生变化的
    在这里插入图片描述

10. 时间序列类

10.1 时间日期

年月日
  1. year：提取日期中的年份
year(serial_number)
  1. month：提取日期中的月份
month(serial_number)
  1. day：提取日期中的天数
day(serial_number)
获取当前日期时间
  1. today()：获取当前日期
  2. now():获取当前日期、时间

在这里插入图片描述

10.2 星期

weekday

返回对应于某个日期的一周中的第几天

weekday(serial_number,[return_type])
# 返回时间序列serial_number的星期几。
# return_type = 1或省略时，星期日为1，星期六为7
# return_type = 2时，星期一为1，星期日为7
weeknum

返回特定日期的周数。例如包含1月1日的周为该年的第一周，编号为第一周

weeknum(serial_number,[return_type])

在这里插入图片描述

  1. 对于2024年7月5号来说，是星期五，那么可以用weekday(2024/7/5,2)来返回5，表示星期5.
  2. 对于这一天是2024年第几周，我们用weeknum(2024/7/5,2),来返回27，表示第27周
datedif

计算两个日期之间相隔的天数、月数或年数

datedif(start_date,end_date,unit)
# unit表示返回结果，Y一段时间内的整年数，M一段时间内的整月数，D一段时间内的天数

在这里插入图片描述

  1. 2019年11月26日与2022年1月1日相差2年，月数相差25个月，如果是用天来统计是767天
  2. 对于年数来说，用公式datedif(2019/11/26,2022/1/1,“Y”)来获取。最后一个参数改成"M"就是月，"D"就是天数

11. 常用操作

1. 每4行选一次

通过Row函数，它会返回指定位置的行号，例如有一列数据，第一行下标为5

在这里插入图片描述

然后每隔4行一次，他们的下标分别是5,9,13,… 只需要通过公式 起始行号 + ( R O W ( 起始行 ) − 起始行号 ) ∗ 4 起始行号+(ROW(起始行)-起始行号)*4 起始行号+(ROW(起始行)−起始行号)∗4就可以获得每4行的下标，当前例子中就是 = 5 + ( R O W ( A 5 ) − 5 ) ∗ 4 =5+(ROW(A5)-5)*4 =5+(ROW(A5)−5)∗4

在这里插入图片描述

最后通过index函数就可以根据下标来选择了，但是记住数据区域要锁死行号，就是绝对引用

在这里插入图片描述

2. 每4行求和

上面知道了如何每4行获取一次下标，那么根据起始下标向下选4行就可以了，通过offset函数

但是要注意，使用offset函数是，公式 起始行号 + ( R O W ( 起始行 ) − 起始行号 ) ∗ 4 起始行号+(ROW(起始行)-起始行号)*4 起始行号+(ROW(起始行)−起始行号)∗4变为 ( 起始行号 − 1 ) + ( R O W ( 起始行 ) − 起始行号 ) ∗ 4 (起始行号-1)+(ROW(起始行)-起始行号)*4 (起始行号−1)+(ROW(起始行)−起始行号)∗4

也就是= 5 + ( R O W ( A 5 ) − 5 ) ∗ 4 \textcolor{red}{5}+(ROW(A5)-5)*4 5+(ROW(A5)−5)∗4变为 = 4 + ( R O W ( A 5 ) − 5 ) ∗ 4 =\textcolor{red}4+(ROW(A5)-5)*4 =4+(ROW(A5)−5)∗4

在这里插入图片描述

二、软件操作技巧

由于篇幅限制，将其放在了另一篇文章中：https://blog.youkuaiyun.com/grd_java/article/details/140267717

三、数据透视表

由于篇幅限制，将其放在了另一篇文章中：https://blog.youkuaiyun.com/grd_java/article/details/140267852

四、图表

1. 经典图表

1.1 柱形图

对比柱形图
  1. 假设我们有一个KPI指标和实际完成表格，我们要对比每个月份的KPI和实际完成情况。就可以使用对比柱形图
    在这里插入图片描述
  1. 选中数据区域后，插入选项卡——图表菜单——柱形图按钮
    在这里插入图片描述
  1. 选中柱形图中的橙色柱形条(实际完成)，右键单击鼠标后，选择设置数据系列格式
    在这里插入图片描述
  1. 将其放在次坐标轴上，两个柱子就重叠了
    在这里插入图片描述
  1. 此时我们发现图表两边的纵轴数值不一样，我们只需要依次双击这两个纵轴，设置坐标轴格式为相同即可
    在这里插入图片描述
  1. 为了区分，我们讲KPI那根蓝色柱子放宽一点，依然是选中蓝色柱子，然后右键，进入设置数据系列格式菜单，然后调整宽度
    在这里插入图片描述
堆积柱形图

例如对比每个月各地区销量的情况下，可以用这种。操做方法就是选择数据区域，然后选择堆积柱形图
在这里插入图片描述

  1. 上图中，我们可以看到柱形图显示的是每个地区各月销量，和我们要求的每个月各地区效率相反，此时只需要点击图表设计选项卡——切换行/列即可
    在这里插入图片描述

1.2 条形图

正负条形图
  1. 假设我们的数据有正有负怎么制作合适的条形图呢？
  2. 首先我们需要一个辅助列，利用函数Sign，来标识数据的正负。sign函数的作用就是正数返回1，负数返回-1,0返回0
    在这里插入图片描述
  1. 为了方便显示，因为增长率是百分比，所以我们给辅助列乘0.5。然后添加负号，负号的作用是让生成的图表符合我们的预期。然后我们为整个数据包括辅助列生成堆积条形图
    在这里插入图片描述
    在这里插入图片描述
  1. 我们发现纵轴是倒着排的，如果我们想让公司1排在上面就点击坐标轴，然后右键进入设置坐标轴格式菜单，然后勾选逆序类别
    在这里插入图片描述
  1. 辅助列和纵坐标轴我一般是不让其显示的。我会选择在这一步删掉纵坐标轴，然后将辅助列条形图变为透明
    在这里插入图片描述
  1. 我们选择所有增长率条，设置其数据系列格式，然后选择以互补色代表负值，为其设置颜色
    在这里插入图片描述
  1. 展示数据标签，点击图表，右边会有一个加号，点击加号后勾选数据标签
    在这里插入图片描述
  1. 选中辅助列的所有数据标签，右键单击，设置其格式。首先勾选类别名称，取消勾选值。然后上面三个标签的位置设置为数据标签内，下面两个设置为轴内侧
    在这里插入图片描述

1.3 折线图

坐标轴简化

折线图很简单，但是坐标轴很容易乱
在这里插入图片描述

  1. 调整坐标轴格式,我们可以先将单位设置为年
    在这里插入图片描述
  1. 然后将数字的格式代码设置为yyyy表示只显示年
    在这里插入图片描述

1.4 面积图

1. 简化年份，例如2010-2011变成2010-11
  1. 设置辅助列，通过right函数，提取年份后两位
    在这里插入图片描述
  1. 创建面积图
    在这里插入图片描述
  1. 选中图表后，点击图表设计选项卡——选择数据按钮，在水平分类轴选择编辑，然后选择我们辅助列。此时横坐标就简化了
    在这里插入图片描述
    在这里插入图片描述
2. 给面积图上面加折线图边框
  1. 选中图表，点击图表设计选项卡——选择数据按钮。在图列项窗口选择添加，
    在这里插入图片描述
  1. 系列值去引用数据，也就是GDP那一列
    在这里插入图片描述
  1. 选择刚刚添加的橙色区域，点击更改图表类型，选中折线图
    在这里插入图片描述
3. 最高点赋值
  1. 选中图表，在插入选项卡中选择插入文本框，然后通过公式引用最高点的值
    在这里插入图片描述

1.5 组合图

  1. 有时候我们直接使用图表，会因为数据之间跨度不一致而导致有些数据体现不出来。例如下图中同比增长率太小，整条曲线几乎看不出变化
    在这里插入图片描述
  1. 选中图表后，我们选择更改图表类型，选择组合图，将同比增长率放在次坐标轴
    在这里插入图片描述

当然，我们可以直接选中数据，插入组合图
在这里插入图片描述

1.6 饼图

复合饼图
  1. 当有些数据占比很小时，饼图中几乎就看不到了。此时就需要用复合饼图，或者说子母饼图
    在这里插入图片描述
  1. 点击图表，右上角的加号中，添加数据标签
    在这里插入图片描述
  1. 子饼图如何控制哪些放过去呢？只需要进入设置序列格式菜单，这里选择以值为系列分割依据，并将3%以下的都放在子饼图中
    在这里插入图片描述
  1. 我们可以选择饼图分离，将颜色改为统一一点的，字体颜色需要选中数据标签，去开始选项卡修改
    在这里插入图片描述

2. 动态图表

2.1 基础和offset函数

OFFSET函数
offset(reference,rows,cols,[height],[width])
# reference: 基点，后面的参数偏移都以这个基点进行偏移
# rows：偏移几行，正数表示向上偏移，负数表示向下偏移
# cols：偏移几列，正数表示向右偏移，负数表示向左偏移
# height：新引用几行，默认为1
# width：新引用几列，默认为1
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

ydenergy_殷志鹏

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值