作者 | SUNIL RAY
编译 | CDA数据分析师
Simple Yet Powerful Excel Tricks for Analyzing Data
介绍
我一直都很佩服Excel强大的数据分析能力。这款软件不仅能够进行基本的数据计算,还可以使用它来进行数据分析。它被广泛用于许多的领域内,包括财务建模和业务规划等。对于数据分析领域的新手来说,Excel它可以成为一个很好的跳板。
甚至来说在学习R或Python之前,最好先了解一下Excel。将Excel添加到你的技能库中没有什么坏处。Excel具有非常广泛的功能:可视化功能、数组,使你能够迅速的通过数据产生洞察力,否则这些数据将很难看到价值。
当然Excel它也有一些缺点。比如它不能非常有效地处理大型的数据集。相信每个人都已经遇到了这个问题。当你尝试对大约200,000个条的数据进行数据计算的时候,你会注意到excel开始出现问题。当然有一些方法可以解决这个问题并在一定程度上处理这些数据,但是Excel并不是一个处理大数据的工具。在有大型数据集的情况下,R或Python是最好的选择。
我很幸运,我学习的旅程始于Excel。多年来,我学到了许多技巧,学会了许多可以更快处理数据的技巧。Excel具有许多功能。选择最好的一个功能有时会会很令人困惑的。在本文中,将为大家提供一些在Excel的技巧,这样可以节省工作或处理数据的时间。这篇文章比较适合那些热衷于升级其数据分析技能的人们。
需要注意的是:如果你认为自己是数据科学领域的大师级人员,那么你可能觉得这篇文章对你来说不会有任何帮助。对于其他人,我建议你练习这些技巧以对它们有一个具体的了解。
常用函数
1. Vlookup():这个函数有助于在表中搜索值并返回相应的值。让我们看一下下表(Policy和Customer)。在“Policy”表中,我们希望基于公共的字段“Customer_id”从Customer表中找到“City”的名称。在这里,函数vlookup()就可以帮助你完成这项工作。
语法:= VLOOKUP(查找的值,要查找的区域,查找区域的第几列,是否相对匹配)
对于上述问题,我们可以在单元格“ F4”中将公式写为= VLOOKUP(B4,$ H $ 4:$ L $ 15,5,0),这将返回所有客户ID 1的城市名称并将该公式复制到所有的客户ID中。
提示:不要忘记使用“ $”符号锁定第二个表的范围,因为如果不锁定的话,可能会在向下复制公式的时候出现错误。而这就是所谓的相对引用。
2. CONCATINATE():将两个或多个单元格中的文本合并为一个单元格非常有用。例如:我们要基于主机名和请求路径的输入来创建URL。
语法:= Concatenate(文本1,文本2,.....文本n)
那么我们可以使用公式= concatenate(B3,C3)并向下复制公式来解决上述问题。
提示:当然我更喜欢使用“&”符号,因为它比键入完整的“连接”公式要短,并且执行出来完全相同的结果。该公式也可以写为“ = B3&C3”。
3. LEN() 这个函数告诉你单元格的长度,也就是包括空格和特殊字符在内的字符数。
语法:= Len(文本)
例如:= Len(B3)= 23
4. LOWER(),UPPER()和PROPER() –这三个函数分别帮助我们可以将文本更改为小写,大写和句子大小写(每个单词的首字母大写)。
语法:=upper(文本)/lower(文字)/proper(文字)
在数据分析项目中,这些有助于将不同案例的类别转换为单个情况下的类,否则将它们视为给定要素的不同类别。看下面的照片这种,A列有五个类(标签),其中B列只有两个类(因为我们已将内容转换为小写)。
5. TRIM(): 这是一个非常方便的函数,用于清除文本中开头和结尾的空白。通常,当你从数据库中获取数据进行单独存储的时候,你要处理的文本中的一些可能会被填充为空白。而且,如果你不处理它们,它们也将被视为列表中的一条数据,这对你来说肯定是没有帮助的。
语法:= Trim(文本)
6. If():我发现这个函数是excel中最有用价值的函数之一。它使你可以使用条件公式,当某件事为真时,它使用一种计算方式,而当某件事为假时,则进行另一种计算方式。例如,你要将每个销售标记为“高”和“低”。如果销售额大于或等于$ 5000,则标记为“高”,否则标记为“低”。
语法:= IF(条件,条件为真的计算,条件为假的计算)
从数据产生推理
1.数据透视表: 当你处理公司的数据时,你都在寻找问题的答案,例如“北部地区的分公司贡献了多少收入?”或“产品A的平均客户数量是多少?”等等。
Excel的数据透视表可帮助你轻松地回答这些问题。数据透视表是一个汇总表,可以让你根据所选的特征进行计数,求平均值,求和并执行其他计算,即,它将数据表转换为推理表,这有助于我们做出决策。查看下面的图片:
在上面,你可以看到左侧的表具有针对每个客户的销售明细,并提供了区域和产品。在右表中,我们总结了区域级别的信息,现在可以帮助我们生成一个推论,也就是南部地区的销售额最高。
创建数据透视表的方法: 步骤1:单击数据列表中的某处。选择插入选项卡,然后单击数据透视表。Excel将自动选择包含数据(包括标题)的区域。如果没有正确选择区域,请在区域上拖动以手动选择合适的区域。最好将数据透视表放在新的工作表上,因此单击“ 新建工作表”作为位置,然后单击“ 确定”。
步骤2:现在,你可以看到“数据透视表字段列表”的面板,其中包含列表中的字段;你需要做的就是将它们放在面板底部的框框中。完成此操作后,左侧的图表将成为数据透视表。
在上面,你可以看到我们在行中安排了“Region”,在列中安排了“Product id”,并以“Premium”的总和作为值。现在你准备好使用数据透视表了,它显示地区和产品保费总和。您还可以使用计数、平均值、最小值、最大值和其他汇总度量。。
2.创建图表:在excel中创建图表只需要选择要绘制的数据范围并按F11即可。这会创建一个默认图表样式的excel图表,但您可以通过选择不同的图表样式来更改它。如果您希望图表与数据位于相同的工作表上,请按ALT + F1而不是按F11。
当然,无论哪种情况,一旦创建了图表,就可以根据自己的特定需求进行自定义图表,以传达所需的消息。
数据清理
1.删除重复值: Excel具有内置的功能,可以从表中删除重复值。它根据选定的列从给定表中删除重复的值,即,如果你选择了两列,则它将搜索具有两列数据相同组合的重复值。
在上面,您可以看到A001和A002有重复的值,但是如果我们同时选择“ID”和“Name”列,那么我们只有一个重复的值(A002, 2)。请按照以下步骤删除重复值:选择数据–>转到数据功能区–>删除重复项
2.文本分列: 假设你将数据存储在列中,如下图片所示。
在上面,你可以看到值之间用分号“;”分隔。现在要将这些值拆分到不同的列中,我建议在excel中使用“ 分列 ”功能。请按照以下步骤将其转换为不同的列:
基本键盘快捷键
键盘快捷键是快速浏览单元格或更快输入公式的最佳方法。在下面列出了我们的经常会使用的一些快捷键。
注意:这不是一个完全的快捷键列表。你可以在下面的评论部分中随意分享你在Excel中最喜欢的键盘快捷键。实际上,我使用这些快捷方式基本上完成了80%的excel任务。
结语
Excel可以说是有史以来最好的程序之一,它一直是全球几乎所有企业的黄金选择标准。但是,无论你是新手还是高级用户,总有一些东西需要学习。
数据分析咨询请扫描二维码
若不方便扫码,搜微信号:CDAshujufenxi
最近我发现一个绝招,用DeepSeek AI处理Excel数据简直太爽了!处理速度嘎嘎快! 平常一整天的表格处理工作,现在只要三步就能搞 ...
2025-04-01你是否被统计学复杂的理论和晦涩的公式劝退过?别担心,“山有木兮:统计学极简入门(Python)” 将为你一一化解这些难题。课程 ...
2025-03-31在电商、零售、甚至内容付费业务中,你真的了解你的客户吗? 有些客户下了一两次单就消失了,有些人每个月都回购,有些人曾经是 ...
2025-03-31在数字化浪潮中,数据驱动决策已成为企业发展的核心竞争力,数据分析人才的需求持续飙升。世界经济论坛发布的《未来就业报告》, ...
2025-03-28你有没有遇到过这样的情况?流量进来了,转化率却不高,辛辛苦苦拉来的用户,最后大部分都悄无声息地离开了,这时候漏斗分析就非 ...
2025-03-27TensorFlow Datasets(TFDS)是一个用于下载、管理和预处理机器学习数据集的库。它提供了易于使用的API,允许用户从现有集合中 ...
2025-03-26"不谋全局者,不足谋一域。"在数据驱动的商业时代,战略级数据分析能力已成为职场核心竞争力。《CDA二级教材:商业策略数据分析 ...
2025-03-26当你在某宝刷到【猜你喜欢】时,当抖音精准推来你的梦中情猫时,当美团外卖弹窗刚好是你想吃的火锅店…… 恭喜你,你正在被用户 ...
2025-03-26当面试官问起随机森林时,他到底在考察什么? ""请解释随机森林的原理""——这是数据分析岗位面试中的经典问题。但你可能不知道 ...
2025-03-25在数字化浪潮席卷的当下,数据俨然成为企业的命脉,贯穿于业务运作的各个环节。从线上到线下,从平台的交易数据,到门店的运营 ...
2025-03-25在互联网和移动应用领域,DAU(日活跃用户数)是一个耳熟能详的指标。无论是产品经理、运营,还是数据分析师,DAU都是衡量产品 ...
2025-03-24ABtest做的好,产品优化效果差不了!可见ABtest在评估优化策略的效果方面地位还是很高的,那么如何在业务中应用ABtest? 结合企业 ...
2025-03-21在企业数据分析中,指标体系是至关重要的工具。不仅帮助企业统一数据标准、提升数据质量,还能为业务决策提供有力支持。本文将围 ...
2025-03-20解锁数据分析师高薪密码,CDA 脱产就业班助你逆袭! 在数字化浪潮中,数据驱动决策已成为企业发展的核心竞争力,数据分析人才的 ...
2025-03-19在 MySQL 数据库中,查询一张表但是不包含某个字段可以通过以下两种方法实现:使用 SELECT 子句以明确指定想要的字段,或者使 ...
2025-03-17在当今数字化时代,数据成为企业发展的关键驱动力,而用户画像作为数据分析的重要成果,改变了企业理解用户、开展业务的方式。无 ...
2025-03-172025年是智能体(AI Agent)的元年,大模型和智能体的发展比较迅猛。感觉年初的deepseek刚火没多久,这几天Manus又成为媒体头条 ...
2025-03-14以下的文章内容来源于柯家媛老师的专栏,如果您想阅读专栏《小白必备的数据思维课》,点击下方链接 https://edu.cda.cn/goods/sh ...
2025-03-13以下的文章内容来源于刘静老师的专栏,如果您想阅读专栏《10大业务分析模型突破业务瓶颈》,点击下方链接 https://edu.cda.cn/go ...
2025-03-12以下的文章内容来源于柯家媛老师的专栏,如果您想阅读专栏《小白必备的数据思维课》,点击下方链接 https://edu.cda.cn/goods/sh ...
2025-03-11