EDUCBA-Excel-透视表的员工绩效分析笔记-全-

EDUCBA Excel 透视表的员工绩效分析笔记（全）

001：员工绩效分析案例概述

在本节课中，我们将通过一个真实的案例研究，学习如何使用Excel数据透视表来分析员工绩效数据。我们将面对一个模拟真实业务场景的数据集，并基于一系列具体问题，运用数据透视表功能来寻找解决方案。

案例背景介绍

我们手头有一份员工信息数据集。这份数据包含了员工的多项信息，例如：入职日期、出生日期、性别、工作地点、职级、所属部门以及当前薪资。此外，数据中还有其他一些列，我们将基于这些信息进行所需的分析。

在数据下方，还有一个小的参照表，它明确了员工根据其绩效评级所能获得的加薪幅度。

具体规则如下：

  • 评级为1的员工将获得 0% 的加薪。
  • 评级为2的员工将获得 3% 的加薪。
  • 评级为3的员工将获得 8% 的加薪。
  • 评级为4的员工将获得 10% 的加薪。
  • 评级为5的员工将获得 12% 的加薪。


需要解决的问题

基于以上提供的信息，我们需要解决以下五个核心问题：

以下是本次案例研究需要完成的具体任务列表：

  1. 识别各部门的最高绩效者和最低绩效者：我们需要分析并找出每个部门中绩效评级最高和最低的员工。
  2. 按职级计算员工的新薪资总和：在应用加薪后，员工将获得新的薪资。我们需要按职级分类，计算这些员工新薪资的总和。
  3. 按工作地点识别员工的平均加薪幅度：我们需要分析按工作地点划分，员工获得的平均加薪百分比，以及对应的平均加薪金额。
  4. 预算与实际偏差分析：现在引入一个基于推理和分析的案例。假设公司的整体薪资预算增幅为 7%。我们需要分析实际总加薪额与预算之间的偏差。如果出现负偏差（即实际超出预算），你将如何调整加薪结构？例如，公司预算是700元，但根据员工评级计算出的总加薪额是800元，这就超出了预算100元。此时，我们需要调整加薪结构，使实际总额等于或低于预算水平。
  5. 创建部门薪资与加薪汇总：在创建了新的加薪结构后，你需要基于新的结构，展示各部门的总薪资和平均加薪幅度的摘要。

这就是摆在我们面前的完整案例。显然，完成这个案例需要一个数据库，我将在接下来的详细步骤中带你逐一使用数据透视表来解决这五个问题。

课程总结

本节课我们一起了解了本次数据透视表实战案例的背景与目标。我们明确了手头的数据包含员工基本信息和绩效加薪规则，并定义了五个需要解决的具体分析问题，包括识别绩效高低、计算薪资、分析平均加薪、进行预算控制以及生成汇总报告。在接下来的课程中，我们将逐步应用Excel数据透视表来找到这些问题的答案。

002：数据导论 📊

在本节课中，我们将学习用于分析员工绩效案例研究的基础数据库。了解数据的结构和内容是进行有效分析的第一步。

数据库概览

我们将要使用的数据库是一个典型的员工数据库，其结构与您在公司中可能遇到的数据集相似。

以下是数据库中各字段的详细介绍。

员工基本信息

首先，我们来看员工的基本身份信息。

  • 员工编号：每位员工的唯一标识。
  • 员工姓名：员工的全名。
  • 名：员工的名字。
  • 姓：员工的姓氏。
  • 州：员工工作所在的州。
  • 办公室类型：员工所属办公室是总部还是分部。总部位于华盛顿特区。

地理与组织划分

每个地理位置都与一个特定的区域相关联。

  • 区域：根据州进行划分。
    • 中西部：包含特定州。
    • 东北部：包含特定州。
    • 南部：包含佛罗里达、佐治亚、密西西比、田纳西和华盛顿特区。
    • 西部：包含亚利桑那和科罗拉多。

部门结构

接下来是公司的部门架构，部门与子部门相互关联。

  • 部门：员工所属的主要部门。
  • 子部门：隶属于主部门的细分团队。
    • 行政部：子部门为“行政”。
    • 业务支持组：子部门包括“创意”和“系统开发”。
    • 企业责任组：子部门包括“合规”、“环境健康安全”和“绿色建筑”。
    • 财务部：子部门包括“账户管理团队”和“分析师团队”。
    • 人力资源部：子部门包括“培训”、“专业培训组”和“培训”。
    • 运营部
    • 市场与销售部：子部门包括“市场品牌”、“媒体”和“销售”。
    • 生产部：子部门包括“设施”、“重大制造项目”和“制造”。
    • 研发与质量组：子部门包括“质量保证”、“质量控制”、“研究中心”和“研发”。

雇佣详情

这一部分描述了员工的雇佣状态和个人信息。

  • 状态：员工的雇佣类型，如合同制、全职、小时工或早期雇佣。
  • 性别：男性或女性。
  • 出生日期：员工的出生日期。
  • 入职日期：员工加入公司的日期，也称为聘用日期。数据从1998年开始。
  • 离职日期：员工离开组织的日期（如果适用）。
  • 在职年限：员工在公司服务的年数。

职级与薪酬

最后，我们来看与员工职级和薪酬相关的关键信息。

  • 领导组：公司设定的特定领导层级，员工可能归属于某个组或不归属。例如：高级领导、新兴领导、其他等。
  • 月薪：员工的月薪，这是分析中最重要的部分之一。
  • 奖金：员工在年度内获得的奖金。
  • 年薪：员工的年度总薪酬。
  • 基本工资：薪酬的基础部分。
  • 房屋租金津贴：住房补贴。
  • 交通津贴：通勤补贴。
  • 特殊津贴：其他特定补贴。
  • 公积金：养老金储蓄。
  • 工作评级：用于分析薪资增长的绩效评级。

薪酬计算逻辑

为了帮助理解，以下是薪酬各组成部分的一些计算逻辑：

  • 基本工资 通常是年薪的50%。公式可表示为：
    基本工资 = 年薪 * 0.5
  • 房屋租金津贴 是基本工资的50%。公式为：
    房屋租金津贴 = 基本工资 * 0.5
  • 交通津贴 是给予员工的固定金额。
  • 特殊津贴 是使总薪酬达到约定数额的平衡项。
  • 公积金 是基本工资的12%。公式为：
    公积金 = 基本工资 * 0.12

本节总结

本节课中，我们一起详细查看了用于员工绩效分析案例研究的数据库。我们了解了员工的基本信息、地理分布、部门结构、雇佣详情以及薪酬的各个组成部分和计算逻辑。这个数据库将是我们后续创建数据透视表、运用公式和分析问题的核心基础。在接下来的课程中，我们将利用这些数据来解答具体的业务问题。

003：增量计算案例研究

在本节课中，我们将通过一个案例研究，学习如何使用Excel的VLOOKUP函数和基础公式，根据不同的增量百分比计算员工的新薪资结构。我们将从识别每个部门中绩效最高和最低的员工这个问题开始。

准备工作

首先，我们需要将数据表导入到Excel工作表中。

创建一个名为“数据”的新工作表。我们将在此处创建一个名为“增量”的表格，其内容与原始数据表相同。

表格包含以下列：评级、增量百分比。评级分为1到5级，对应的增量百分比分别为：0%、3%、8%、10%和12%。此外，我们后续会用到的一个预算基准值是7%。

应用增量百分比

现在，我们将根据增量百分比来计算全新的薪资结构。

首先，我们需要使用VLOOKUP函数为每位员工查找对应的增量百分比。这是一个非常简单的查找操作。

以下是VLOOKUP公式的应用：

=VLOOKUP(评级单元格, 增量表格区域, 2, FALSE)

在这个公式中，“2”代表我们希望返回增量表格区域中的第二列（即增量百分比），“FALSE”表示需要精确匹配。

应用公式后，我们得到了每位员工的增量百分比。

请确保将这一列的格式设置为百分比格式。

构建新薪资结构

上一节我们确定了增量百分比，本节中我们来详细构建新的薪资结构。

我们将创建一个独立的区域来展示新结构，并使用浅黄色背景进行区分。

以下是计算新薪资结构的步骤：

  1. 月薪：对于获得0%增量的员工，月薪保持不变。对于其他员工，新月薪等于旧月薪乘以 (1 + 增量百分比)。我们使用ROUND函数将结果四舍五入到整数。

    =ROUND(旧月薪 * (1 + 增量百分比), 0)

  2. 奖金：由于没有关于奖金的调整说明，因此奖金金额保持不变。直接链接到原始奖金单元格即可。

  3. 年薪资：新的年薪资等于新的月薪乘以12。

    =新月薪 * 12

  4. 基本工资：基本工资通常占总薪资的固定比例（例如50%）。这里，新基本工资等于新月薪的50%。

    =新月薪 * 50%

  5. 房租津贴：房租津贴（HRA）是基本工资的40%。

    =新基本工资 * 40%

  1. 交通津贴：交通津贴是月薪的2.5%。

    =新月薪 * 2.5%

  2. 公积金：公积金（PF）缴纳比例为基本工资的12%，由公司和员工共同承担，因此总额为24%。

    =新基本工资 * 24%

  3. 特殊津贴：特殊津贴是年薪资减去上述所有其他津贴（基本工资、HRA、交通津贴、PF）后的余额。

    =新年薪资 - SUM(新基本工资, HRA, 交通津贴, PF)

  4. 工作评级：工作评级不适用于下一年度的计算，因此可以暂时忽略或标注为“不适用”。

对于获得0%增量的员工，其薪资结构的所有组成部分将完全保持不变。

总结

本节课中，我们一起学习了如何为一个简单的员工绩效案例构建增量计算模型。我们首先使用VLOOKUP函数匹配绩效评级与对应的增量百分比，然后逐步应用公式计算出包括月薪、年薪资、基本工资、各项津贴在内的完整新薪资结构。这个过程清晰地展示了如何将基础Excel函数应用于实际的人力资源数据分析场景中。

004：创建用于绩效分析的数据透视表 📊

在本节课中，我们将学习如何创建数据透视表，这是分析员工绩效的第一步。我们将基于准备好的数据库，开始解答第一个问题：找出每个部门中绩效最高和最低的员工。

上一节我们完成了数据库的整理，本节中我们来看看如何利用数据透视表进行初步分析。

首先，我们需要为第一个问题创建一个数据透视表。操作步骤如下：

以下是创建数据透视表的具体步骤：

  1. 选择数据：选中整个数据表区域。

  2. 插入透视表：点击菜单栏的“插入”选项卡，然后选择“数据透视表”。

  3. 配置透视表：此时会弹出“创建数据透视表”对话框。它要求你选择要分析的数据范围（通常已自动选中），并选择放置透视表的位置。

    • 你可以选择“新工作表”或“现有工作表”。
    • 我们点击“新工作表”，然后按“确定”。

  4. 重命名与保存：将新生成的工作表重命名为“question1”，并保存文件。

数据透视表创建完成后，点击透视表区域，右侧会出现“数据透视表字段”窗格。这个窗格列出了数据库中所有的列（字段），例如“salary 2”代表新的加薪后薪资。

在字段窗格右侧，你可以看到四个区域：筛选器、列、行和值。通过将字段拖拽到不同区域，可以构建不同的数据视图。

窗格下方是“设置”选项，可以改变字段列表的布局方式。例如：

  • 字段部分和区域部分层叠：同时显示字段列表和区域布局。
  • 仅字段部分：只显示字段列表。
  • 仅区域部分：只显示区域布局。
  • 区域部分仅1×4：以特定行列方式显示区域。

通常，我们保持默认的“字段部分和区域部分层叠”即可。

此外，在Excel功能区还会出现两个与数据透视表相关的上下文选项卡：“分析”和“设计”。

在“分析”选项卡中，你可以：

  • 查看和修改透视表名称。
  • 使用“活动字段”选项。
  • 执行“向下钻取”或“向上钻取”。
  • 对数据进行分组。
  • 插入“切片器”和“时间线”进行动态筛选。
  • 更改数据源、刷新数据、清除或移动透视表。
  • 进行字段项设置等计算。
  • 插入“数据透视图”或“推荐的透视表”。
  • 控制是否显示字段列表、+/-按钮和字段标题。

在“设计”选项卡中，你可以调整透视表的布局和样式：

  • 小计：选择不显示小计、在组底部显示或在组顶部显示。
  • 总计：控制是否对行和列启用总计。
  • 报表布局：可以选择以压缩形式、大纲形式或表格形式显示。
  • 重复项目标签：选择是否重复所有项目标签。
  • 空行：选择是否在每个项目后插入空行。
  • 透视表样式选项：控制是否启用“镶边行”和“镶边列”。
  • 透视表样式：为透视表选择不同的预定义样式。

现在，我们回到第一个问题：找出每个部门中绩效最高和最低的员工。有两种分析方法，这里我们先创建两个透视表。

首先，让我们找出各部门的员工。操作如下：

以下是构建第一个分析视图的步骤：

  1. 将“Department”（部门）字段拖到筛选器区域。
  2. 将“Job Rating”（工作评级）字段也拖到筛选器区域，这样我可以在下方筛选特定的绩效评级。
  3. 将“ID”和“Full Name”（员工姓名）字段拖到行区域。

这样，我们就得到了一个按部门筛选、并列出所有员工ID和姓名的透视表，为后续分析绩效高低奠定了基础。

本节课中我们一起学习了创建数据透视表的基本流程，并熟悉了其核心界面和功能选项。我们成功构建了第一个分析视图，将部门和绩效评级作为筛选条件，员工信息作为行标签，为下一步具体分析最高和最低绩效者做好了准备。

005：利用计算增强数据透视表

在本节课中，我们将学习如何使用Excel数据透视表来筛选和展示数据，具体目标是找出各部门中绩效评分最高和最低的员工。我们将通过创建和格式化数据透视表来实现这一目标，整个过程无需复杂的数学计算，非常适合初学者掌握。

创建基础数据透视表

首先，我们通过数据透视表列出了所有员工。员工名单按字母顺序排列。数据显示员工总数为741人，这与数据源的总数一致。

筛选最高与最低评分员工

我们的第一个任务是找出各部门中绩效评分最高和最低的员工。以下是实现此目标的几种方法之一。

我们将部门与工作评级作为筛选条件。在行区域放置员工姓名，然后在筛选器中选择工作评级。首先，我们选择最高评级5。

执行此操作后，我们得到了所有评级为5的员工名单，总计约182人。

同理，要找出最低评级的员工，我们无需重新创建整个数据透视表。只需复制现有的数据透视表，粘贴到旁边，并将筛选器中的工作评级改为1即可。

现在，我们得到了评级为1的员工名单，大约有74人。至此，我们已经列出了最高和最低评级的员工。

按部门深入分析

接下来，我们希望按部门查看这些员工。我们可以添加部门筛选器。例如，选择“行政部”后，可以看到该部门评级最高的两名员工是John Savage和Larry Wood。选择“人力资源部”，则显示有11名员工评级最高。

对于最低评级，我们同样可以应用筛选。例如，在“财务部”约有15名低评级员工，在“市场与销售部”有7名。

通过使用筛选器，我们可以清晰地描绘出每个部门中绩效最高和最低的员工。

创建部门对比视图

如果我们想对比不同部门的情况，可以创建另一个数据透视表。

在现有工作表下方插入一个新的数据透视表。我们将“部门”和“员工姓名”放入行区域，将“工作评级”放入筛选器列。选择评级5后，我们得到了每个部门中所有评级为5的员工列表。

默认情况下，数据透视表会显示分类汇总和总计。为了视图更简洁，我们可以移除它们。右键点击数据透视表，选择“分类汇总‘员工姓名’”以取消勾选，即可移除分类汇总。若要移除总计，可以进入数据透视表选项，在“总计和筛选”标签页中，取消勾选“对行和列启用总计”。

现在，视图只显示了评级为5时各部门的员工名单。要查看评级为1的员工，只需复制此数据透视表，并将筛选器中的评级改为1。

创建数据透视表并按部门列出员工姓名非常简单，这不需要任何数学知识，仅仅是利用评级筛选来生成列表。

美化数据透视表

在完成结构创建后，我们可以为其添加一些视觉效果以提升可读性。

首先，进入“视图”选项卡，取消勾选“网格线”，使整个工作表背景变得干净。接着，进入数据透视表的“设计”选项卡。这里有多种预置样式，分为“浅色”、“中等深浅”和“深色”三类，您可以根据个人喜好选择。

例如，我们可以先选择一个非常浅的色调，然后选择一个稍深的蓝色样式，最后尝试一个更深的样式。不同的样式可以赋予数据透视表不同的外观，从明亮到深邃，方便您根据报告场景选择最合适的一款。

使用折叠与展开功能

数据透视表还提供了便捷的折叠与展开功能。在按部门分组后，每个部门旁边会出现一个减号（-）按钮。点击它可以折叠该部门下的所有员工详情，只显示部门名称。同样，右键点击数据透视表，选择“展开/折叠” -> “折叠整个字段”，可以一次性折叠所有分组。

这个功能非常有用。例如，在查看最低评级员工时，我们可以快速发现“行政部”下没有评级为1的员工。通过展开“人力资源部”分组，我们可以并排查看该部门的最高和最低评级员工名单。

总结与应用场景

本节课中，我们一起学习了如何创建和格式化Excel数据透视表来筛选并列出特定条件的员工。整个过程直观且简单。数据透视表的用途非常广泛，不仅限于人力资源分析。

在财务部门，可以列出有业务往来的公司或需要重点跟进的贷款客户。在市场与销售部门，可以列出顶级客户、需要提升的客户或潜在客户目标。列表管理在许多业务场景中都至关重要。

数据透视表的核心价值在于，它能帮助您从庞大的数据库中，快速、灵活地创建出符合特定视角的子集列表，而无需手动复制和筛选原始数据。它是一个非常简单易用的数据视图工具，能极大地提升数据分析的效率。

006：按级别计算薪资总和

在本节中，我们将学习如何使用Excel数据透视表，按员工的领导力级别计算新薪资的总和与平均值。上一节我们识别了各部门的最高和最低绩效者，本节我们将聚焦于薪资数据的汇总分析。

创建数据透视表

首先，我们需要为第二个问题创建一个新的数据透视表。在Excel中插入一个新的工作表，并将其命名为“question2”。然后，基于原始数据在此工作表中创建数据透视表。

以下是创建数据透视表的步骤：

  1. 选择数据区域。
  2. 点击“插入”选项卡，选择“数据透视表”。
  3. 在对话框中，选择“现有工作表”并指定“question2”工作表中的位置。
  4. 点击“确定”。

配置数据透视表字段

数据透视表创建后，我们需要配置字段以按级别分析薪资。

以下是需要拖放的字段：

  • 行：将“Leadership Group”（领导力组）字段拖入“行”区域。这将按级别（如高级领导、进阶领导等）对员工进行分组。请确保从列表中移除任何空白项。
  • 值：将“Early Salary2”（新年度薪资）字段拖入“值”区域两次。第一次用于计算员工数量（计数），第二次用于计算薪资总和。

计算薪资总和与转换单位

默认情况下，数值字段可能显示为计数。我们需要将其更改为求和以计算总薪资。

操作步骤如下：

  1. 在“值”区域，点击“计数项：Early Salary2”旁边的下拉箭头。
  2. 选择“值字段设置”。
  3. 在“值汇总方式”选项卡下，选择“求和”。
  4. 点击“确定”。现在，数据透视表显示了每个领导力级别的新薪资总和。

为了使大额数字更易读，我们可以将其单位从“元”转换为“十万（Lakh）”。这可以通过创建计算字段来实现。

创建计算字段的公式如下：
= ‘Early Salary2’ / 100000

操作步骤如下：

  1. 在数据透视表分析选项卡中，点击“字段、项目和集”。
  2. 选择“计算字段”。
  3. 输入名称（如“New Salary Lakhs”）和上述公式。
  4. 点击“添加”，然后“确定”。新字段将出现在数据透视表中，显示以“十万”为单位的薪资。

计算平均薪资

除了总和，了解每个级别的平均薪资也很有用。我们可以直接对原始薪资字段应用平均值计算。

操作步骤如下：

  1. 在“值”区域，再次添加“Early Salary2”字段。
  2. 点击该字段的下拉箭头，选择“值字段设置”。
  3. 在“值汇总方式”选项卡下，选择“平均值”。
  4. 点击“确定”。数据透视表现在会显示每个领导力级别的平均薪资。

扩展分析：按州汇总薪资

数据透视表的优势在于其灵活性。我们可以轻松地改变分析维度。例如，将行标签的“Leadership Group”替换为“State”（州），即可快速查看不同地区的薪资汇总情况。

操作步骤如下：

  1. 将“行”区域中的“Leadership Group”字段拖出。
  2. 将“State”字段拖入“行”区域。
  3. 数据透视表将立即更新，显示按州汇总的薪资总和与平均值。

为了更直观地看出哪个州的薪资最高或最低，可以使用条件格式。

以下是应用条件格式的步骤：

  1. 选择包含平均薪资的数据列。
  2. 点击“开始”选项卡中的“条件格式”。
  3. 选择“项目选取规则” -> “前10%”。
  4. 在弹出的对话框中，可以设置格式（如将前10%的单元格填充为绿色），以高亮显示平均薪资较高的州。

总结

本节课中，我们一起学习了如何利用Excel数据透视表按级别计算员工薪资的总和与平均值。我们创建了数据透视表，配置了行字段和值字段，使用计算字段转换了数据单位，并计算了平均值。最后，我们还探索了如何通过替换行字段来快速切换分析维度（如按州分析），并使用条件格式使关键数据更加突出。这些技能能帮助你高效地从不同角度汇总和分析大型数据集。

007：地理位置性能对比 📊

在本节课中，我们将学习如何使用Excel数据透视表，按地理位置分析员工的平均加薪百分比。我们将从基础操作开始，逐步深入，并探索如何超越问题本身，进行更丰富的多维度分析。

概述

上一节我们计算了员工的新工资总和。本节中，我们将聚焦于第三个问题：按地理位置识别给予员工的平均加薪百分比。我们将创建一个数据透视表，按州和区域计算平均加薪率，并学习如何格式化数据、应用条件格式以及添加更多分析维度。

创建基础数据透视表

首先，我们需要基于原始数据创建一个新的数据透视表来分析地理位置维度的平均加薪。

  1. 新建一个工作表，命名为“Question3”。
  2. 转到“数据”选项卡，选择全部数据。
  3. 点击“插入”选项卡中的“数据透视表”。
  4. 在对话框中，选择“现有工作表”，并定位到“Question3”工作表的某个单元格。

操作完成后，Excel会创建一个空白的数据透视表框架。

配置字段与计算平均值

接下来，我们需要将相应的字段拖拽到数据透视表区域，以构建我们的分析视图。

以下是配置数据透视表字段的步骤：

  • 将“State”（州）和“Zone”（区域）字段拖入“行”区域。
  • 将“Increment Percentage”（加薪百分比）字段拖入“值”区域两次。

默认情况下，数据透视表对数值字段进行“计数”。我们需要将其改为计算平均值。

  • 右键单击第二个“Increment Percentage”字段，选择“值字段设置”。
  • 在“值汇总方式”选项卡下，选择“平均值”。

此时，数据透视表会显示按州和区域划分的平均加薪百分比，但格式是数字。

格式化与初步解读

为了使数据更直观，我们需要将其格式化为百分比。

有两种方法可以设置百分比格式：

  1. 选中平均加薪百分比所在的整列，在“开始”选项卡中点击“百分比样式”按钮（%）。
  2. 或者，右键单击数据，选择“设置单元格格式”，然后在“数字”选项卡中选择“百分比”。你可以使用快捷键 Ctrl + Shift + 5 快速应用百分比格式。

建议将小数位数设置为一位，以获得更清晰的视图。

现在，我们可以解读数据了。例如：

  • 在中西部区域，有一个州拥有64名员工，平均加薪为7.9%。
  • 在东北区域，新泽西州有73名员工，平均加薪7.6%；纽约州有55名员工，平均加薪7.3%。

你可以取消选择数据透视表中的“（空白）”项，并可以将行标签重命名为“Zone / State”，使表格更清晰。

应用条件格式深入分析

为了快速识别表现突出的数据，我们可以使用条件格式。

转到“开始”选项卡，点击“条件格式”，选择“突出显示单元格规则” -> “大于”。

  • 在对话框中输入 8%，并选择一个突出显示格式（如浅红色填充）。

应用后，你可以立即看到哪些州的平均加薪率超过了8%。这通常与员工的绩效评级相关。

超越问题：多维度关联分析

优秀的分析不应局限于问题本身。我们可以添加更多维度来探索数据背后的关联。

让我们将“Job Rating”（工作评级）字段也加入分析。

  • 将“Job Rating”字段拖入“值”区域。
  • 右键单击该字段，将其值汇总方式设置为“平均值”，并设置两位小数。

现在，表格同时显示了平均加薪百分比和平均绩效评级。观察发现，加薪率较高的州（如佐治亚州，8.4%），其平均绩效评级也较高（3.52）。这表明加薪百分比与绩效评级呈正相关。

然而，加薪金额还受原有薪资水平影响。为了验证这一点，我们再添加“Increment Amount”（加薪金额）字段。

  • 将“Increment Amount”字段拖入“值”区域，并设置其汇总方式为“平均值”。
  • 可以对此列应用新的条件格式，例如“项目选取规则” -> “值最大的10%项”，用绿色填充突出显示。

分析结果可能显示，新泽西州的平均加薪百分比虽不是最高，但因其员工平均原有薪资较高，导致平均加薪金额反而名列前茅。

核心逻辑公式可以总结为：

  • 平均加薪百分比 ≈ f(绩效评级) —— 主要取决于评级。
  • 平均加薪金额 ≈ 平均原有薪资 × 平均加薪百分比 —— 同时受原有薪资水平影响。

因此，回归线在这两个案例中都是正向的。

灵活切换分析维度

数据透视表的强大之处在于可以快速切换分析视角，无需重建。

例如，如果我们想按部门而非地理位置进行分析：

  1. 复制当前数据透视表。
  2. 在新的数据透视表中，将“行”区域中的“Zone”和“State”字段移除。
  3. 将“Department”（部门）和“Sub-Department”（子部门）字段拖入“行”区域。

瞬间，整个分析视图就切换到了部门维度。你可以发现，市场部和研究中心的平均加薪率可能最高。

我们还可以进行更细致的分析，比如查看每个部门内员工的最高和最低加薪情况。

  1. 再次复制数据透视表。
  2. 移除不必要的字段，只保留部门和分析字段。
  3. 将“Increment Percentage”字段再拖入“值”区域两次。
  4. 分别将这两个字段的汇总方式设置为“最大值”和“最小值”。

这样，你就能看到，例如在创意部，所有人的评级都较高，因此最高和最低加薪率很接近；而在某些部门，评级分布广，最高与最低加薪率的差距就会较大。

总结

本节课中，我们一起学习了如何使用Excel数据透视表进行地理位置维度的绩效分析。

我们首先创建了基础数据透视表来计算平均加薪百分比，并学会了格式化数据。接着，我们应用条件格式来快速识别关键数据点。更重要的是，我们探索了如何超越单一问题，通过添加绩效评级、加薪金额等字段进行多维度关联分析，揭示了加薪逻辑。最后，我们演示了数据透视表的灵活性，通过简单拖拽字段即可快速切换分析维度（如从地理位置切换到部门）。

数据透视表是Excel中最强大、最实用的功能之一。它操作简单（拖拽即可），无需复杂公式，却能对数据库进行高效、直观的多角度分析，是职场中必须反复掌握的核心技能。

008：预算与实际绩效分析对比 📊

在本节课中，我们将学习如何使用Excel数据透视表进行预算与实际绩效的对比分析。我们将通过一个具体案例，分析公司设定的7%整体预算增幅与实际执行情况之间的偏差，并探讨如何调整薪酬结构以管理预算。

上一节我们介绍了如何计算员工的新薪酬，本节中我们来看看如何将实际薪酬与预算目标进行对比分析。

公司设定的整体预算增幅为7%。我们需要分析实际薪酬与预算薪酬之间的偏差。如果出现负偏差（即实际成本超出预算），我们将探讨如何调整薪酬增幅结构。

首先，我们需要在数据表中加入预算增幅的计算。预算增幅的计算公式为：
预算增幅 = 当前薪酬 * 7%

接着，计算预算薪酬总额（Budget CTC）：
预算薪酬总额 = 当前薪酬 + 预算增幅

我们将预算相关数据用不同颜色（如红色）标记以便区分。完成计算后，可以观察到实际薪酬总额与预算薪酬总额之间存在差异。在本例中，总差异为负的35.6万，这意味着实际薪酬总额比预算高出约35.6万。

为了更清晰地分析，我们创建一个新的工作表并插入数据透视表。

以下是创建数据透视表的步骤：

  1. 选中数据区域。
  2. 点击 插入 选项卡。
  3. 选择 数据透视表。
  4. 在弹出的对话框中，选择将透视表放在现有工作表中。
  5. 将需要求和的字段（如薪酬差异）拖入“值”区域，并设置为“求和”。

透视表显示，总薪酬差异为正值35.6万，表明公司整体薪酬超出了预算。具体到个人，有的员工薪酬低于预算（如某人少4.5万），有的则高于预算（如某人多3.5万）。

我们的目标是将总差异尽可能调整至接近零。由于当前总差异为正（超出预算），我们需要通过降低部分员工的薪酬增幅百分比来减少总体支出。

上一部分我们通过透视表识别了预算超支的问题，接下来我们看看如何通过调整薪酬增幅结构来管理预算。

调整的核心思路是修改基于绩效评级的增幅百分比表。我们将尝试降低某些评级（如评级2）的增幅比例，并观察其对总差异的影响。

操作过程如下：

  1. 修改“增幅表”中对应绩效评级的百分比。例如，将评级2的增幅从3%尝试调整为2.5%。
  2. 每次修改后，刷新 数据透视表以查看最新的总差异。
  3. 重复此过程，微调百分比，直到总差异接近零。这是一个类似“单变量求解”的手动迭代过程。

例如，经过多次尝试，将评级2的增幅调整为2.62%后，总差异被缩小到约2万的正向差异（即实际仅超出预算2万），这已是非常接近预算目标的结果。

通过这种方式，我们实现了预算管理。关键在于所有工作表都是动态链接的：修改增幅表 → 预算增幅和预算总额自动重新计算 → 数据透视表刷新后立即反映最新的差异。

本节课中我们一起学习了如何使用Excel数据透视表进行预算与实际绩效的对比分析。关键步骤包括：计算预算增幅与预算总额、利用透视表快速汇总和识别总体偏差、以及通过动态链接的工作表和迭代调整增幅百分比来实现精细化的预算控制。掌握这一方法，你可以有效地监控和管理薪酬等项目的预算执行情况。

009：预算分析的筛选与精炼 🔍

在本节课中，我们将学习如何通过调整员工加薪比例，使总加薪成本与公司预算相匹配。这是一个典型的预算分析过程，涉及数据透视表的动态更新和“假设分析”工具的使用。

调整加薪比例以匹配预算

现在，您可以看到，我们已经修改了整个加薪比例表，以匹配我们的预算。

这在公司中也会发生：你有一个设定的预算，你分析并核对预算，查看预算和当前加薪计划的情况，然后将两者匹配，并产生一个对公司有利的数字。

这是一个纯粹的例子，我直接去修改加薪比例表。我将3%改为2.62%，然后得出一个数字，这是我们能得到的最接近的结果。差额是2005，这是一个非常微小的差异。从百分比来看，48克罗（货币单位）也存在一些调整空间。

动态调整与预算偏差

如果我将预算改为8%，那么偏差将再次改变。

我可以将这个预算加薪比例链接到这里的加薪比例表，并粘贴到下方。现在，让我们再次刷新数据透视表。

看，这里有45,000的差额。现在我可以将3级员工的加薪率提高到4%。让我们看看差额是多少。

33,000。现在将预算改为8%。现在将这个改为10%。

这是11,000。现在改为12%，我想现在它会超过预算。尽管我的差额是91,000，仍然可以接受。提高这个比例会给高绩效员工更高的加薪，让他们在公司留得更久，但这太多了。

我将得到12.1%。仍然有18,000的差额，这可以接受。如果你想为低绩效员工降低比例，可以相应调整并刷新数据透视表，但差额需要利用起来。我们将保持在4%本身。

这是根据8%的加薪预算调整后的情况，偏差较小。这是可以接受的。这个范围可以接受。你可以设定一个0到-50,000的差额范围，并相应地调整你的数字。

应对更高的预算比例

在你的案例中，如果预算比例超过12%，差额又会变得很大。看，现在是1克罗（100,000）。

我的预算比例设为14%，差额很大。现在，我将给4级员工12%的加薪。

实际上，我可以将他们的比例提高到18%，因为我的预算非常充足。

91,000的差额。现在，我们将给3级员工10%的加薪。我将给2级员工6%的加薪。好的。我将给我的高绩效员工更高的价值，因为他们也提供了高质量的工作。22%太高了。

22%不错，但差额仍然存在。23%。

或者你可以将这个降到20，这个降到15，角度更好。好的，差额3,000更多。现在必须将这个降到18%。19,000。

会让你更接近7,000的差额，好的。20%再次。随着这个比例上升，我们必须调整其他列。

所以这个保持18。现在我们将这个降到4%，因为低绩效员工无论如何提供的价值不大，抱歉，这个改为6，这个改为21。

这将使差额变为14,000，现在将这个降到4%。现在差额3,000，现在将这个改为5%。你也可以使用“单变量求解”来找出需要调整的数字。

使用“单变量求解”工具

尽管如此，你也可以使用预测组，这就是分析将寻求的。现在，“单变量求解”会给你一个很好的值，使差额为零。

通过更改单元格，但对于数据透视表，这当然不起作用，你必须使用不同的决策。我将使用一些其他方法并向你展示“单变量求解”。

我想更改这个数字，使用“假设分析”中的“单变量求解”。

将目标值设为0。所以问题是，将目标单元格设为0，通过更改单元格。

加薪比例表，这个。现在让我们看看它变成了什么。这需要一些时间。是的。

现在它正在计算并进行更改。所以想想，目标是你的当前值。现在，正如你所看到的，它得到的最接近的值，它将数字改为4.36，之前是5。它给了我一个完美的范围，我可以基本上找到我的正确值。完美的范围，所以现在是57,000。

验证与总结

应该。这给了你一个完美的检查。让我们再做一次“单变量求解”，如果你保持15%的预算比例，这对你非常有帮助。现在我的预算改变了，差额是71,000。现在我将再次进行“单变量求解”。

我将进入“数据”->“假设分析”->“单变量求解”，将此单元格的目标值设为0，通过更改单元格。

现在让我给5级员工更高的百分比。这需要一些时间，但它会找到你的解决方案。它基本上为你找到最接近的值。

我想这个值变得有点太高了。通过给它这么高的值，所以让我检查一下。好的。差额是1克罗（100,000）。

好的，21%的加薪比例。进入“假设分析”->“单变量求解”。将目标单元格设为20,000，通过更改单元格。21%的加薪比例。检查中。

加薪百分比太高了，因为它无法管理。现在，让我们看看值变成了什么。

现在，正如你所看到的，我给了它30%。现在它基本上将值改为35%。

31%。继续，32%。33%。33.5%。33.7%。33.6%，33.6%。越来越接近，33.5%左右，差额10万。10万可以接受，55,000。

好的，33.51%，接近10万的范围，可以接受。

这就是你基本处理数据并相应调整的方式。尝试链接所有内容，以便它给你一个清晰整洁的视图。

本节课总结

本节课中，我们一起学习了如何使用Excel数据透视表和“假设分析”工具进行预算精炼。核心步骤包括：

  1. 调整加薪比例：手动修改加薪表中的百分比，使总成本接近预算。
  2. 分析偏差：观察调整后总成本与预算的差额，并在可接受的范围内（如±50,000）进行微调。
  3. 使用“单变量求解”：当手动调整效率低下时，使用 数据 > 假设分析 > 单变量求解 工具，自动计算达到特定目标（如零偏差）所需的加薪比例。
  4. 动态链接与刷新：确保加薪表与数据透视表链接，任何修改后通过 刷新 数据透视表来更新结果。

通过这个过程，你可以根据公司预算灵活制定加薪策略，优先奖励高绩效员工，同时将总成本控制在预算范围内。

010：数据透视表应用 📊

在本节课中，我们将学习如何使用数据透视表创建部门化的薪资与调薪汇总，这是分析员工绩效案例研究的最后一步。我们将整合所有数据，从多个角度审视调薪周期的最终结果。

上一节我们完成了调薪结构的计算，本节中我们将利用数据透视表，对各部门的薪资总额、平均薪资及平均调薪率进行综合分析。

现在，我们进入工作表，这里已经创建了包含新调薪后数据的数据集。

创建部门汇总数据透视表

我们将基于新的数据集创建一个数据透视表，以按部门进行汇总分析。

  1. 首先，选中数据区域，点击 插入 -> 数据透视表。
  2. 在弹出的对话框中，选择将数据透视表放置在新工作表。
  3. 我们将这个新工作表命名为“部门汇总”。

以下是构建数据透视表字段的步骤：

  • 行区域：将“部门”字段拖入此处，作为分类依据。
  • 值区域：我们需要添加多个计算字段来全面分析。
    • 将“薪资”字段拖入值区域，默认会计算为 求和项:薪资。这代表各部门的薪资总额。
    • 再次将“薪资”字段拖入值区域，并将其值字段设置改为 计数。这可以让我们知道每个部门有多少名员工。
    • 第三次将“薪资”字段拖入值区域，并将其值字段设置改为 平均值。这代表各部门的平均薪资。
    • 将“调薪率”字段拖入值区域，并将其值字段设置改为 平均值。这代表各部门的平均调薪百分比。
    • 将“预算调薪后薪资”字段拖入值区域，并将其值字段设置改为 平均值。这提供了一个对比视角。

完成后的数据透视表将清晰展示各部门的以下信息：员工数量、薪资总额、平均薪资、平均调薪率以及平均预算调薪后薪资。

分析数据透视表结果

现在，我们可以对生成的数据透视表进行分析和解读。

  • 排序分析：我们可以按“平均薪资”进行排序。结果显示，企业责任组的平均薪资最低（约61,000），而行政部门的平均薪资最高（约76,600）。
  • 关联洞察：尽管企业责任组的平均调薪率最高（16.5%），但其平均薪资仍然最低。这是因为他们的薪资基数原本就较低，因此即使调薪比例高，增长的绝对值也相对较小。
  • 预算对比：我们还可以在值区域添加“预算调薪后薪资”的 求和项，与实际的“薪资总额”进行对比。计算两者差异的百分比公式为：=(实际薪资总额/预算薪资总额)-1。在本案例中，差异率仅为0.02%，远低于1%，说明整个调薪周期被严格控制在预算范围内。

通过这个数据透视表，我们成功地从员工数量、成本总额、薪酬水平、调薪力度以及预算符合度等多个维度，全面评估了本次调薪的效果。

案例总结与回顾

本节课中我们一起学习了如何运用数据透视表解决一个完整的、贴近实际工作的数据分析案例。我们通过五个核心问题，逐步演示了从数据清洗、计算新字段到多维度分析的完整流程。

这个案例是现实场景的典型缩影。在工作中或面试时，你很可能遇到类似的数据集，需要运用Excel技能（特别是数据透视表）来完成数据整理并通过它产生有价值的洞见。掌握此类分析方法，对于完成日常工作或应对专业挑战都至关重要。

希望本案例研究对你非常有帮助。我们的网站上还有更多实用课程和真实案例研究，这些都将助力你的日常工作和职业发展。感谢你学习本次关于数据透视表的案例课程。

posted @ 2026-03-26 08:20  布客飞龙II  阅读(0)  评论(0)    收藏  举报