数据分析基础(1)——超实用‼️Excel 常用函数和实用技巧

超实用‼️Excel 常用函数和实用技巧

weixin_49171365

1929人浏览 · 2023-08-27 17:23:13

weixin_49171365 · 2023-08-27 17:23:13 发布

学习教程：☑️ 懒人Excel - Excel 函数公式、操作技巧、数据分析、图表模板、VBA、数据透视表教程

一、Excel知识体系✨

掌握以下知识，将10倍提升你的工作效率，快收藏吧💫相关知识体系如下：
1、快捷键
2、重要操作
3、函数
4、数据透视表
5、图表制作
6、Power Query
7、VBA
8、Power BI
关于Excel的知识体系就是这些啦！

今天主要分享Excel 函数，把Excel中常用函数公式和用法整理出来啦。非常实用!

二、Excel 常用函数💡

1. SUM函数：对一行/列数字进行求和。例如，=SUM(A1:A10)将计算从A1到A10的数字总和。

2. AVERAGE函数：计算一行/列数字平均值。例如，=AVERAGE(A1:A10)将计算从A要A10的数字平均值。

3. MAX和MIN函数：查找一行/列数字的最大值和最小值。例如，=MAX(A1:A10)将查找从A1到A10的数字中的最大值。

=IF(COUNT(T303,Z303,AF303,AL303,AR303,AX303,BF303)>0,MAX(T303,Z303,AF303,AL303,AR303,AX303,BF303),"")

这段代码的作用是判断一系列单元格（T303、Z303、AF303、AL303、AR303、AX303 和 BF303）中是否有至少一个非空单元格。如果有，返回这些单元格中的最大值；否则，返回空字符串。

4. IF函数：根据条件进行判断并返回不同的结果。例如，=IF(A1>10,"大于10","小于等于10")将根据A1单元格的值返回“大于10”或“小于等于10”。

=IF(COUNT(B1:AD1)>0,MAX(B1:AD1),"")这段函数的目的是判断范围 B1:AD1 内是否有至少一个非空单元格。如果有，返回该范围内的最大值；否则，返回空字符串（表面上就隐藏了）。

COUNT(B1:AD1) 统计了单元格 B1 到 AD1 范围内的非空单元格数量。

IF 函数接受三个参数：条件、如果条件为真的返回值、如果条件为假的返回值。

MAX(B1:AD1) 返回范围内的最大值。

当 COUNT(B1:AD1)>0 时，即范围内至少有一个非空单元格时，IF 函数返回 MAX(B1:AD1)。

如果 COUNT(B1:AD1)<=0，则 IF 函数返回空字符串""。

5. COUNT函数：计算一列数据中有多少个数据。例如，=COUNT(A1:A10)将计算从A1到A10中有多少个数据。

6. COUNTIF：计算一个区域中满足给定条件的单元格数目。例如，=COUNTIF(A1:A10, "＞90")将计算在A1到A10单元格中大于90的单元格数目。

7. VLOOKUP函数：在一个表格中查找一个值，并返回该值所在行或列中的另一个值。例如，=VLOOKUP(A1, B:C, 2, FALSE)将在B和C列中查找A1的值，并返回该值所在行的第二个值。

8. LEFT、RIGHT和MID函数：从文本字符串中提取指定的字符。例如，=LEFT(A1, 3)将从A1单元格中提取前三个字符。

9. UPPER和LOWER函数：将文本字符串转换为大写或小写字母。例如，=UPPER(A1)将把A1单元格中的文本字符串转换为大写字母。

10. TRIM和CLEAN函数：去除文本字符串中的空格和不可见字符。例如，=TRIM(A1)将去除A1单元格中的前导和尾随空格，=CLEAN(A1)将去除A1单元格中的所有非打印字符。

11. FIND和SEARCH：查找一个文本字符串在另一个文本字符串中的位置。例如，=FIND("World", "Hello World")将返回“Hello World”中“World”第一次出现的位置。

12. CONCATENATE函数：将两个或更多文本字符串连接起来。例如，=CONCATENATE("Hello, World")将返回“Hello World”。

13. SUMIF、AVERAGEIF、MAXIF和MINIF：对一个区域中满足给定条件的单元格求和、计算平均值、最大值和最小值。例如：

=SUMIF(B2:B25, "＜=20")将计算在B2到B25单元格中小于等于20的单元格的数值之和。

=AVERAGEIF(B2:B25, "＜=20")将计算在B2到B25单元格中小于等于20的单元格的平均值。

=MAXIF(B2:B25, "＜=20")将查找在B2到B25单元格中小于等于20的单元格中的最大数值。

=MINIF(B2:B25, "＜=20")将查找在B2到B25单元格中小于等于20的单元格中的最小数值。

14. TEXTJOIN函数：用于将一列文本字符串连接起来，并指定分隔符。例如，=TEXTJOIN(", ", 1, A1:A10)将把从A1到A10的文本字符串连接起来，并用逗号和空格分隔。

15. 时间函数DATE、DAY、MONTH和YEAR：

DATE函数用于创建日期序列。例如，=DATE(2023,4,5)将返回2023年5月5日的日期序列。

DAY函数用于从日期序列中提取日部分。例如，=DAY(DATE(2023,4,5))将返回5，表示2023年5月5日的日部分。

MONTH函数用于从日期序列中提取月部分。例如，=MONTH(DATE(2023,4,5))将返回5，表示2023年5月5日的月部分。

YEAR函数：用于从日期序列中提取年份部分。例如，=YEAR(DATE(2023,4,5))将返回2023，表示2023年5月5日的年份部分。

Excel函数公式的价值在于它们可以帮助用户更快速、准确地进行数据处理和分析，避免手动和重复的工作，从而大大提高工作效率和质量。此外，Excel函数公式还可以帮助用户发现数据中的规律和趋势，从而做出更好的决策和预测。

三、Excel 使用技巧 🤔

1、隐藏单元格内容：

或者使用函数进行隐藏，例如：

=IF( AND ( ISBLANK(AR308),ISBLANK(AR28)),"",AR308-AR28)

ISBLANK(AR308) 判断 AR308 单元格是否为空。如果为空，返回 TRUE；否则返回 FALSE。
ISBLANK(AR28) 判断 AR28 单元格是否为空。如果为空，返回 TRUE；否则返回 FALSE。
AND(ISBLANK(AR308), ISBLANK(AR28)) 判断 AR308 和 AR28 是否都为空。如果都为空，返回 TRUE；否则返回 FALSE。
如果 AR308 和 AR28 都为空，则返回空字符串""。
如果 AR308 和 AR28 至少有一个不为空，则执行 AR308-AR28 的数值计算，并返回结果。

总结起来，这段代码的作用是判断 AR308 和 AR28 单元格是否为空。如果它们都为空，则返回空字符串""（即隐藏起来）；否则，返回 AR308 减去 AR28 的值。

2、关联单元格：

与Sheet这个页面的中的A5的单元格内容进行关联

3、嵌套IF函数：

例如要完成如下实例

如果 BL303 或 N298 为空，则返回空字符串。
如果 BL303 大于等于 BY298 并且 BR303 小于等于 BZ298，则返回"合格"。
如果 BY298 为空，并且 BR303 小于等于 BZ298，则返回"合格"。
如果 BY298 不为空，则返回"不合格"。

=IF( OR (BL303="",N298=""), "" ,

IF( AND (BL303>=BY298,BR303<=BZ298), "合格" ,

IF( BY298="", IF(BR303<=BZ298,"合格","不合格") , "不合格" )

)

)

具体解释如下：

OR(BL303="", N298="") 判断 BL303 单元格和 N298 单元格是否有一个为空。如果有一个为空，则返回空字符串""。如果 BL303 和 N298 都不为空，继续执行下一层的 IF 函数。

IF(AND(BL303>=BY298, BR303<=BZ298), "合格", IF(BY298="", IF(BR303<=BZ298, "合格", "不合格"), "不合格")) 是第二层的 IF 函数。

AND(BL303>=BY298, BR303<=BZ298) 判断 BL303 是否大于等于 BY298 并且 BR303 是否小于等于 BZ298。如果条件都成立，则返回"合格"。

如果上述条件不成立，继续执行下一层的 IF 函数。

IF(BY298="", IF(BR303<=BZ298, "合格", "不合格"), "不合格") 是第三层的 IF 函数。

当 BY298 为空时，判断 BR303 是否小于等于 BZ298。如果成立，则返回"合格"；否则返回"不合格"。如果 BY298 不为空，则直接返回"不合格"。

或者更复杂一点的情况是：

如果 BL303 或 N298 为空，则返回空字符串。
如果 BL303 大于等于 BY298 并且 BR303 小于等于 BZ298，则返回"合格"。
如果 BY298 为空，并且 BR303 小于等于 BZ298，则返回"合格"。
如果 BZ298 为空，并且 BR303 大于等于 BY298，则返回"合格"。
否则，则返回"不合格"。

=IF( OR(ISBLANK(BL303), ISBLANK(N298)), " ",

        IF( AND (BL303 >= BY298, BR303 <= BZ298), "合格",

               IF(AND(ISBLANK(BY298), BR303 <= BZ298), "合格",

                       IF(AND(ISBLANK(BZ298), BR303 >= BY298), "合格",

                                "不合格" )

                )

        )

)

4、根据公式设置带有格式的单元格

要求：在excel中，T18 和 BY18，BZ18的值进行比较，（BY18是最小值，BZ18是最大值）如果BY18或者BZ18任意一个单元格为空，就只要比较其中的一个范围（例如：当BZ18为空的时候，执行T18>=BY18的比较判断，BY18为空的时候，执行T18<=BZ18的比较判断），否则两个单元格都要比较，如果T18不在这个最大值和最小值的范围中，将T18单元格填充为灰色。

正着去写这个逻辑会比较麻烦，所以反过来分析一下，就变成了：

如果 T18 不在 BY18 和 BZ18 的范围内，或者当 BY18 为空时，T18 大于 BZ18，或者当 BZ18 为空时，T18 小于 BY18，T18 单元格就会被填充为灰色。其他情况下，T18 单元格将保持原样。

要根据公式的结果为单元格应用填充颜色，你可以使用条件格式来实现。按照以下步骤进行操作：

选中T18单元格。
点击Excel菜单栏中的【开始】选项卡。
在【样式】组中，点击【条件格式】按钮，并选择【新建规则】。
在弹出的对话框中，选择【使用公式确定要设置格式的单元格】选项。
在【格式值】框中输入以下公式：
=IF( OR (  ISBLANK(BY18), ISBLANK(BZ18)  ) , 
    IF( AND ( ISBLANK(BY18 ) , T18<=BZ18)  , TRUE  , 
        IF( AND( ISBLANK(BZ18 ),T18>=BY18 ),TRUE,  FALSE) ),
            IF( AND(T18>=BY18,T18<=BZ18),FALSE,  TRUE)
        )
点击【确定】继续设置。
在弹出的对话框中，点击【格式】按钮来设置填充颜色为灰色。
点击【确定】完成条件格式的设置。

现在，如果T18不在最大值和最小值的范围内，它将会根据公式的结果被填充为灰色。请注意，这个条件格式设置只适用于T18单元格，你可以将这个设置应用到其他需要的单元格上。

补充：

1、自学数据分析学习路线

① 、EXCEL
1.视频课程(b站):王佩丰EXCEL，孙兴华(powerquery)
2.体系课程:百职帮Excel，简单易学
3.知识体系:快捷键、函数、图表制作、数据透视表、powerquery

②、SQL
1.书籍:mysql必知必会
2.视频(b站):尚硅谷MYSQL基础+高级篇
3.知识网站:菜鸟教程(知识体系)，leetcode(刷题)

③、BI
1.推荐软件:tableau(某宝购买会员)
2.推荐视频(b站):tableau零基础教程

④、python基础知识
1.推荐书籍:python教程:从入门到实践
2推荐视频(b站):黑马程序员python教程
3.刷题网站:leetcode，廖雪峰python课

⑤、python机器学习
1.推荐书籍:西瓜书
2.推荐视频(b站):吴恩达的机器学习
3.推荐网站:kaggle(具有较多的数据集，可实操)

⑥、分析思维
1.推荐书籍:金字塔原理、精益数据分析、增长黑客
2.视频课程(b站):秦路:七周成为数据分析师
3.核心思维:结构化、公式化、业务化
4.主要分析方法:二八法、象限法、对比法、漏斗法
⑦、业务知识
1.推荐网站:人人都是产品经历、36氪、虎嗅
2.核心:基于所在行业的业务知识，了解相关数据分析的思路;如电商行业常用的GMV拆解、用户下单路径的漏斗转化
3.牢记:加强对业务知识的学习
4.推荐公众号:晚点latepost、深燃、空白女侠、晴雨数据