数据分析实战-excel制作自动化周报

code_ent

已于 2024-09-07 05:27:17 修改

阅读量296

点赞数

分类专栏：统计学与编程文章标签： excel

于 2023-04-03 22:06:42 首次发布

本文链接：https://blog.csdn.net/code_ent/article/details/129940944

版权

统计学与编程专栏收录该内容

6 篇文章 0 订阅

订阅专栏

前言

本数据分析-excel篇是根据数据分析自学课程-戴戴戴师兄，基于制作企业必备excel周报的基础知识，从课程中汲取出自己的语言汇总而来。学会这一整个篇章后，你应该会自己做一种很酷的周报了，然而，这些知识的迁移才是最重要的，将这些制作周报的知识为你更多工作的效率提升做好铺垫，才是最重要的。

正文

数据分析工作快捷化

数据分析工作可以通过掌握一些Excel快捷操作来显著提高效率。以下是一些实用的快捷键和功能，它们可以帮助您更快地处理数据。

Ctrl+Shift+L：筛选模式 - 此快捷键可以快速激活或关闭数据筛选功能，便于截取各种分类变量的信息。
Ctrl+E：快速填充 - 又称为“填十字下拉”，它能智能识别数据模式并快速填充单元格。
Alt+=：自动求和 - 选择一个范围后，使用此快捷键可以快速插入求和公式，适用于对数据行或列进行快速求和。
F4：锁定引用 - 当需要在公式中固定某个单元格的引用时，F4键非常有用，特别是在填充公式时，确保引用的单元格不会改变。
Ctrl+H：查找和替换 - 用于快速查找或替换特定数据或格式，提高数据处理效率。
Ctrl+A：全选 - 当需要在工作表中选择所有内容时，Ctrl+A是一个非常便捷的快捷键。
Ctrl+1：设置单元格格式 - 快速访问单元格格式设置，适用于调整日期显示格式或小数与百分比的转换。
Ctrl+Enter：快速粘贴相同内容 - 在多个选定的单元格中快速粘贴相同的内容，无需重复操作。
新建窗口 - 对于大型数据表，新建窗口允许您在不同的窗口中查看或编辑数据，实现单屏操作。
Win+←/→：窗口分屏 - 快速将当前窗口分隔到屏幕的左侧或右侧，便于同时查看多个工作表或文档。
冻结窗格 - 对于数据量大的源数据表，冻结窗格功能（例如冻结B2单元格）可以保持标题行和列始终可见，便于数据导航。

这些快捷键和功能可以帮助数据分析人员节省大量时间，提高工作效率。掌握这些技巧，可以使数据分析工作变得更加快捷和高效。

SUM累计

在商业数据分析中，理解和使用正确的术语至关重要。以下是一些常见商业术语及其解释：

时间维度 - 指的是数据分析时考虑的时间单位，如年、月、周、日。时间维度帮助分析数据随时间的变化趋势。
计算维度 - 用于数据计算和分析的维度，包括：
- 累计 - 数据在一定时间范围内的总和。
- 转化率 - 实际消费（或转化）数量与曝光次数的比例，表示为实消/曝光。
- 环比 - 以时间作为单一维度，比较相邻时间段（如前后一日）的数据变化。
- 同比 - 在时间上层维度（如年）保持一致的情况下，比较下层维度（如月）的数据变化，如2010年3月与2011年3月的对比。
文本维度 - 涉及数据指标的类型和含义，包括：
- 结果指标 - 以产出为导向，衡量业务活动的最终效果，如实收、有效客源量、GMV等。
- 过程指标 - 以投入为导向，反映业务活动的实际过程，如下单、点击量、转化率、曝光、各工作成分占比等。
- GMV（商品交易总额） - 指在一定时间内的商品交易总额，包括所有点击购买的金额。
- UV（独立访客） - 24小时内访问网页的自然人数，每人只计一次。
- PV（页面浏览量） - 同一页面的多次访问记录，也称为点击量。
- CPC（每次点击成本） - 产生一次点击所需的平均成本。

掌握这些术语有助于更好地理解和分析商业数据，从而做出更有效的商业决策。

指标操作

①累计

在商业数据分析中，"累计"是一个常用的术语，它涉及对一段时间或一系列数据点的总和进行计算。以下是关于"累计"的详细解释及其在Excel中的实现方法：

累计的概念 - 累计通常指对一定时间范围内的数据总和的计算。在商业分析中，这可以帮助理解随时间变化的趋势，比如销售额的月累计或季度累计。
在Excel中实现累计计算 - 使用Excel的SUM函数是计算累计值的常用方法。此外，利用筛选功能可以更精确地定义需要累计的数据范围。
- Ctrl+Shift+L快捷键 - 此快捷键用于进入筛选模式，便于快速筛选出需要进行累计计算的数据范围。
- SUM函数的格式 - SUM函数的基本格式为 =SUM(区域)，其中“区域”指的是你希望计算总和的单元格范围。

虽然SUM函数的使用相对直观，它主要适用于简单的数值求和，但在特定的数据分析场景中，如时间序列数据的累计分析，它是一个非常有用的工具。通过结合筛选功能，可以更灵活地处理和分析数据。

②使用SUMIFS函数进行条件累计

在Excel中，SUMIFS函数是一个非常有用的工具，它允许您在满足特定条件的情况下对数值进行求和。与基本的SUM函数不同，SUMIFS函数可以基于一个或多个条件来筛选数据，这使得它非常适合用于复杂的数据分析。

SUMIFS函数的基本格式：=SUMIFS(需求数值列, 限制条件列1, 限制条件1, 限制条件列2, 限制条件2, ...)。在此格式中，需求数值列是你希望对其中的数值进行求和的列，而限制条件列和条件则是用来筛选哪些数值将被包括在求和中的。
条件的使用：限制条件可以是具体的数值，也可以是单元格引用。当你需要对某个特定日期、特定人员或其他分类的数据进行求和时，SUMIFS函数特别有用。
处理日期范围：当处理日期这样的具有“范围大小”的类别时，SUMIFS函数尤其强大。例如，如果你想对一个特定月份的所有GMV（商品交易总额）进行累计，你可以将需求数值列设为GMV列，限制条件列1设为日期列，条件1设为该月份的第一天，条件2设为该月份的最后一天。这样，SUMIFS函数将只对那个特定月份内的GMV进行求和。
处理不规则的日期范围：对于月份的最后一天，由于不同月份的天数不同，你可能需要根据具体情况调整条件。例如，2月份通常只有28天或29天（闰年），而其他月份可能是30天或31天。在设置条件时，需要根据具体月份的天数来调整。

SUMIFS函数的这种灵活性和强大功能，使其成为处理复杂数据集时的理想选择。通过精确设置条件，可以实现对数据的精细筛选和准确求和，从而在商业数据分析中发挥重要作用。

③环比和同比的计算方法

在商业数据分析中，环比和同比是两个常用的概念，用于比较不同时间段的数据变化。以下是关于日环比、日同比以及如何使用Excel函数进行计算的解释：

日环比 - 日环比用于比较今天和前一天的数据变化。其计算公式为：日环比 = (今日信息 / 前日信息) - 1。使用SUMIFS函数可以轻松获取今日和前日的信息，从而计算出日环比。
日同比 - 日同比用于比较当月某一天和上个月同一天的数据。其计算公式为：日同比 = (当月该日信息 / 前月该日信息) - 1。为了计算日同比，需要使用DATE函数和日期函数。DATE函数的格式为：=DATE(年, 月, 日)。结合日期函数（如YEAR、MONTH、DAY），可以根据选中数据的日期信息提取年、月、日数值。例如，要选取上个月同一天的信息，可以将当天的日期作为选中数据，然后在MONTH函数后减1，得到上个月的日期。
周环比、月环比、年环比 - 这些环比计算的基本原理与日环比相似，关键在于正确圈定比较的时间范围。对于周环比，可以通过在当周日期上减7天来获取上周的日期。月环比则涉及本月和上月的对比，重点是正确设定时间范围。年环比同理。
月同比 - 月同比的计算与日同比类似，但涉及整个月的数据。关键在于正确使用DATE函数来获取每月的第一天和最后一天，以便进行准确的比较。

这些计算方法在Excel中实现相对简单，关键在于正确应用SUMIFS和DATE函数，以及合理设置时间范围。通过这些计算，可以有效地分析和理解数据随时间的变化趋势。

④在Excel中处理特定日期

在Excel中处理日期数据时，有一些最佳实践可以遵循，以确保数据的准确性和处理效率。以下是一些关于如何处理特定日期的技巧：

使用字符串存储日期 - 尽管Excel提供了日期格式，但在某些情况下，使用字符串格式存储日期可能更为灵活和方便，特别是在处理复杂的日期计算和格式转换时。
使用DATE函数获取特定日期 - DATE函数是处理日期数据的有用工具。例如，要获取某个月的第一天，可以使用公式 =DATE(YEAR(日期单元格), MONTH(日期单元格), 1)。这里，“日期单元格”是指包含日期的单元格。
计算每月的最后一天 - 要得到每月的最后一天，可以使用公式 =DATE(YEAR(日期单元格), MONTH(日期单元格) + 1, 1) - 1。这个公式的原理是先得到下一个月的第一天，然后减去一天，从而得到当前月份的最后一天。这种方法的好处是，它自动适应不同月份的天数差异，无需手动考虑每月是30天、31天还是28天或29天（闰年）。
结合SUMIFS函数进行日期范围计算 - 当需要计算某个日期范围内的数据总和时，可以将DATE函数与SUMIFS函数结合使用。例如，要计算某个月份的总销售额，可以使用SUMIFS函数，并将日期范围设定为该月的第一天和最后一天。

通过这些方法，可以更有效地在Excel中处理日期数据，特别是在需要进行复杂的日期比较和计算时。这不仅提高了数据处理的速度，还减少了出错的可能性。

⑤利用INDEX和MATCH实现数据自动化

在Excel中，INDEX和MATCH函数的结合使用是实现数据自动化的强大工具。这种方法特别适用于需要频繁更新数据的情况，如周报或月报的自动化处理。以下是关于如何使用这两个函数的详细解释：

INDEX函数 - INDEX函数的基本格式是 =INDEX(区域, 行号, 列号)。它用于返回指定区域中特定行和列的单元格内容。当行号或列号设为0时，INDEX函数将返回整个行或列的数据。
MATCH函数 - MATCH函数的基本格式是 =MATCH(查找项, 查找区域, 0)。这里的“0”代表精确匹配。MATCH函数返回查找项在查找区域中的位置，以数值形式表示。
结合使用INDEX和MATCH - 当INDEX和MATCH结合使用时，可以实现数据的自动化查找和更新。MATCH函数用于定位表头所对应的数据在源数据表中的位置，INDEX函数则根据MATCH的结果返回相应的数据。这样，只要表头数据不变，即使源数据更新，通过INDEX和MATCH函数构建的公式也能自动调整到正确的数据。
应用示例 - 例如，在一个周报中，如果需要查找特定表头下的数据，可以使用公式 =INDEX(A:X, 0, MATCH(查找项, 查找区域, 0))。在这里，“查找项”是周报中的表头，而“查找区域”则是源数据表中包含表头的行。
优势 - 这种方法的优势在于，当源数据表更新时，只需覆盖旧数据，周报中的数据就会自动更新。这使得处理周期性报告变得非常高效和自动化。

通过掌握INDEX和MATCH函数的结合使用，可以大大提高数据处理的速度和准确性，特别适合于需要定期更新的报告和数据分析工作。

⑥利用SUBTOTAL和VLOOKUP进行数据计算和查询

在Excel中，SUBTOTAL和VLOOKUP是两个非常实用的函数，它们可以帮助用户进行各种数据计算和查询操作。以下是关于这两个函数的详细解释：

SUBTOTAL函数 - SUBTOTAL函数的基本格式是 =SUBTOTAL(function_num, ref1, ref2...)。这个函数用于在列表或数据库中计算一系列数值的总和、平均值、最大值、最小值等。其中，function_num参数指定要进行的计算类型，例如1表示平均值，4表示最大值，9表示求和等。ref1, ref2...是参与计算的单元格范围。SUBTOTAL函数的一个优点是，在进行计算时可以自动忽略隐藏行，这在处理大型数据集时非常有用。
VLOOKUP函数 - VLOOKUP函数的基本格式是 =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])。这个函数用于在表格或列中搜索某个值，并返回该值所在行指定列的值。lookup_value是要查找的值，table_array是包含数据的区域，col_index_num是要返回值的列号，range_lookup指定是近似匹配（1）还是精确匹配（0）。VLOOKUP函数通常用于数据查询和匹配，特别是在连接不同数据表或数据源时非常有用。