平均值会说谎

最后,还有一件事想告诉大家。

几乎所有关于 Excel 的书或研讨会,都把导出“平均值”的 AVERAGE 函数作为一个重要的基础函数介绍给大家。但是,在我的 Excel 培训课上并不会这样做。为什么呢?这是因为在制作处理数字数据的资料表格时,我认为不应该随意使用“平均值”。

“平均值会说谎”

请首先记住这句话。

我们在许多场合都会遇到平均值这个指标。如考试成绩的平均分、平均收入。知道了平均值,再和自己的实际情况作对比,随着数值忽高忽低也或喜或忧。

所谓平均值,就是所有数值的总和除以全部个数所得出的数值。这总会让人觉得它是“所有数值中最中间的数值”。但是,平均值有可能是脱离实际情况的。

例如,有一家想要在某一地区开展销售房屋业务的建筑公司,要在此区域调查可行的价格范围,于是对当地的人均年收入进行了调研,结果得出600万日元。于是,这家建筑公司做出了这样的判断:该地区“年收入600万日元的人占大多数”。因此决定以年收入600万日元左右的人群为目标建筑楼盘。但实际上,当地人们的收入情况有非常明显的等级差别,即“年收入900万日元的人群”与“年收入300万日元的人群”呈两极分化状态。的确,两者相加除以2,就可以得出平均年收入为600万日元的结果。但是实际上,并没有年收入达到600万日元的人群。

另外,公司一般用到的指标还有“平均年收入”。以此为判断依据的时候也需要注意。乍一看很高的平均年收入数字,很有可能是因为一部分中高层管理人员的高额收入拉高了平均值,而实际上大部分员工的年收入要远远低于平均值。

像这样的例子不在少数。请一定注意平均值会有偏离实际情况的风险。

统计学中针对这个问题,给出了“标准差”的概念,用于了解计算平均值的各个数值的偏差情况。但是,即使在公司报告说明时使用这个概念,即使听者感觉理解了这部分内容,但在实际的商务现场中并一定具有足够的说服力来实现方案中的内容。

遇到在商务文本中使用“平均值”的情况时,如果追问这个平均值是如何得出的,很多人会这么回答:“差不多是这样”、“之前的负责人算出来的”。没有带着明确的意图制作资料,就会变成这样。这是需要花费自己的宝贵时间与精力的工作,“这件工作能得到怎样的成果?”对这一点要有足够深刻的认识,绝不能随意地制作商务文本。

希望大家能够牢记工作必需的三大要素:自主性、责任感、全局观,将 Excel 当成武器,大幅提高自己的工作能力。

如何运用帕累托分析法

这7个资金种类中,哪种可以运用帕累托分析法处理呢?

例如“削减固定成本”,相信这是每家公司都在努力做的事情。为了知道“应该从固定成本中的哪个经费开始下手”,可以使用帕累托分析法。

如果想不增加固定成本而提高员工薪资的话,就要考虑削减人工成本除外的“其他固定成本”。这时,削减“其他固定成本”那个部分就变得尤其重要。只要找到效果较大的目标,即占据整体80%的20%的费用项目,然后从这个地方开始着手削减经费就事半功倍了。

关于变动费用也是一样。如果占据大部分进货成本的是少数的供货商和商品的话,只要他们稍微降低一些价格,就极有可能大幅减少成本=增加毛利。与不断地对供货商提出难以接受的降价要求比起来,这个方法无疑是最明智的。

当然关于销售额方面,按照客户、商品进行分类后运用 ABC 分析、帕累托分析,立刻就能找出重点客户和利润最大的商品。

以“资金方块拼图”来理解公司的资金流动

以“销售目标”为主要课题的企业非常多,其实在商务活动中应该重视的数字不只有“销售额”,“利润”也同样重要。

“利润”,粗略来讲就是“销售额减去成本后得到的数字”。如果销售额是100万日元,但是成本也花去了100万,那么利润就是零。

想要增加利润,选择以下两种中的其中一种或者二者结合运用(但在真实的经营环境下,并不一定都会有效果。削减经费后却导致销售额下滑,增加经费也有可能增加利润)。

  • 增加销售额
  • 削减经费

那么,为了增加销售额,应该做些什么?削减经费,又应该做些什么呢?

这里介绍给大家一个绝对不会弄错先后顺序的方法,那就是下面的“资金方块拼图”,通过这张图可以完全把握公司的资金流动情况。

为了增加公司的现金流和利润,提高销售额、削减经费是最有效的方法。

战略上要明确轻重缓急,为了使对策的效果最大化,应该从哪方面着手。

想要明确改善对象,能够找出“占据80% 的20% 是什么”的帕累托分析法是最有效的方法。“资金方块拼图”,是西顺一郎先生的 STRAC 表为基础建立的一种思考方法,和仁达也先生获得了原作者的允许,将其改良。

作为提供商品和服务的等价回报,从客户那里收取费用。这就是销售额①。

供应商成本、材料费、外包费等与销售额呈比例变动的费用,需要从销售额中剔除。这一部分费用称作“变动成本”②。

从①的销售额减去②的变动成本后得到的资金就是③“毛利”。

租金、人工成本和其他销售管理成本等变动成本以外的费用称作“固定成本”④。

固定成本大致可分为两部分,人工成本⑤和“其他固定成本”⑥。

从固定成本中减去毛利得到的剩余资金就是“利润”⑦。

这些用语,与利润表、资产负债表等各种财务表中出现的会计用语多少有些不同。例如,“利润”原本有销售利润、经常性净利润和当期利润等意思,在本书中作简化说明。

将帕累托分析法运用于制作图表的3个方法

想要制作出这样的图表,需要按照以下的3个分析手法的顺序处理。

➊ 排名分析

首先,可以简单地按照消费金额的大小给客户排序。这时,可通过 Excel 的排序功能处理。这样就可以将重要的客户排在名单的最上方。

例如,在提交新产品方案的时候,可以按照这个顺序给客户打电话。如果客户名单是按姓氏排列的话,选择使用下列的哪一种方法,也会大大影响销售的效率。

  • 直接按照顺序从上往下依次打电话
  • 先按照消费金额的大小(降序)排列数据,从上往下依次打电话

Excel 的“排序”功能能够帮助我们实现“从最重要的客户开始联络”这样理所当然的想法。

➋ ABC 分析

按照客户分类的销售额数据进行降序排列后,计算出每个客户的消费金额的构成比率。在下一页的表格中,单元格 C1中含有所有客户的消费金额的总和。将 C1和每位客户的消费金额做除法,可以得出结构比率的数据。在单元格 D4中输入下列公式,一直复制粘贴到最后一行。

=C4/$C$1

然后,将结构比率相加,可以得出累计结构比率。

得出每个客户的消费金额的结构比率

得出累计结构比率

像这样,比如就能知道“前3家客户公司占了整体销售额的50%,说明这三家公司是我们重要的客户”。为了明确客户的重要程度,“将客户分成若干小组”这种分析手法就是 ABC 分析法。在此,将累计结构比率大于或等于80%的客户分到 A 组,小于80%的分到 B 组。

可用 IF 函数处理这种分类。在单元格 F4中输入下列公式,一直复制粘贴到最后一行,就能自动分成 A、B 两组了。

=IF(E4<80%,”A”,”B”)

在单元格 F4中输入=IF(E4<80%,”A”,”B”),分成 A、B 组

➌ 帕累托分析

像这样将客户分为 A 等组与 B 等组之后,接下来就要计算两组的“交易件数”和“总计金额”了。表格则呈现以下形式。

得出 A 组客户与 B 组客户的“交易件数”与“总计金额”

用 COUNTIF 函数计算“A 和 B 各有多少个”。

用 SUMIF 函数得出“A 和 B 的总销售额是多少”。

得出实际数字后,再计算结构比率。A 的销售件数占整体的26%,但销售额却占结构比率的80%。而 B 的销售件数虽然占整体的74%,销售额的结构比率却不足20%。将这个结果制作成图表,就会看到前文中的“按客户分类的销售额结构比率”那样的表。

这样就一目了然了吧?对于这家公司来说,首先必须维护好 A 组客户,当然也不能忽视 B 组。但从优先顺序来说,应该要对 A 组客户予以更多的重视。从经营战略角度来说,

“把提供给 A 组客户的优惠政策介绍给 B 组客户,可以刺激 B 组客户的购买欲,积极升级到 A 组的等级。”

而提出这种战略计划的根据或分析,只需制作一张简单的 Excel 表格。

在削减经费方面也是一样。提取出占据大部分比例的要素后,不对症下药的话也不会有任何改善。如将便利贴裁成一半使用,重复利用打印纸的反面,等等,与其在这种事情上花费大量的时间,还不如多找出“占整体经费80%的支出是什么”。

“社长的租车费用占了很大比例。”

“如果减掉‘管理层的交际费’里面每月的夜总会花销,经费能节省20%。”

这样就能发现一些真正需要改善的地方。不要在费力而不见效的事情上花费大量的时间和精力。为了明确真正应该付出努力的对象,一定要在了解 Excel 的基础技能的基础上,熟练运用各种分析手法。

帕累托法则(二八定律)

“帕累托法则”是在商务领域中经常会提到的思考方法,也叫作“80:20法则”。它是指:

“销售额的80%,是由20%的客户提供的。”

“经费的80%,是由20%的员工使用的经费构成的。”

一言以蔽之,假定了“一部分的构成要素会给整体带来巨大影响”。

通过下面的表格会更加容易理解。

按客户分类的销售额结构比率

这是某公司按客户分类的销售额结构比率数据做成的图表。客户分为 A、B 两组,表示每组中的客户数量(图表内为件数)和销售金额的比例。

从这张图表中,可以立刻发现一个事实:在件数中占比不到26%的 A 组客户,却提供了80%的销售额。如果失去或者损失这26%的客户的销售额的话,甚至会达到影响整个公司的经营状况的程度。因此,就可以做出建立防止这26%的客户流失、采取继续维持合作关系的战略这样的判断。

帕累托分析法(Paretoanalysis)是制定决策的统计方法,用于从众多任务中选择有限数量的任务以取得显著的整体效果。帕累托分析法使用了帕累托法则,关于做20%的事可以产生整个工作80%的效果的法则。

为削减经费所付出的努力真的有意义吗?

提高销售额。减少开支。

这二者都是为了提高利润十分重要的事项,但并不是胡乱行动。如果把精力浪费在错误的对象上,即使花费大量的时间和精力,也无法获得满意的结果,最后只能获得“多劳少得”的下场。这样,不仅降低了生产能力,更是增加了多余的时间成本,浪费时间。严重的话,也会打击员工的积极性。

比如减少开支。大部分的企业认为努力“减少不必要的成本”本身是件非常有意义的事情。但是,不要忘记为了减少开支需要花费一定的劳动和时间成本。如果为削减成本花掉的时间成本,反而超过了削减的部分,这样就本末倒置了。像这样“毫无意义地努力削减经费”的做法十分不可取。我列举一些至今为止我遇到的实际事例供大家参考:

  • 将便利贴裁开使用
  • 利用打印纸的反面
  • 离开座位10分钟以上的话,关闭计算机电源
  • 裁切使用过的打印纸,当作记事本

这些都是十分花费时间和精力的事情,想要通过这些方法削减经费,并不能减少很可观的开支,也就是说无法产生新的利益。考虑到做这些事情需要花费的人工费用,可能还会产生赤字。像这种削减成本的工作通常十分无趣,生产性也过低,也会导致员工的积极性下降。

关于削减成本方面,经常可以听到这样一句话,“积少成多”。

我们需要辨别“我做的这些工作是否真的能够积少成多。”

一旦建立格式,就可反复套用

这时,从材料数据中制作用函数统计的格式是最好的方法。一旦做成这种格式,之后只要将材料数据粘贴到固定位置就能够完成表格。

在表格中,首先在单元格 J6中输入以下汇总公式:

=SUMIF(数据加工!$K:$K,$A6&$B6&J$5&J$4,数据加工!$E:$E)

在单元格 J6中输入=SUMIF(数据加工!$K:$K,$A6&$B6&J$5&J$4,数据加工!$E:$E)

在第二参数中,结合了以下4个单元格的数值。

  • 单元格 A6“啤酒”
  • 单元格 B6“北海道”
  • 单元格 J5“2009”
  • 单元格 J4“1Q”

在单元格 J5中实际上输入的是“2009”这个数值。同样,单元格 K5里也输入了“2001”这个数值。但是,单元格上显示的却是“2009年”,后缀有“年”这个字。在【设置单元格格式】➛【表示格式】的【自定义】中,在【种类】的编辑栏中输入以下内容,就能够在单元格中显示“年”这个字。

0″年”

但是,实际上在单元格中输入的是“2009”、“2010”这样的数值。而前文中的函数的第二参数就变为了“啤酒北海道20091Q”的字符串。

第一参数指定的是“数据加工”工作表中的 K 列,这一列中组合了种类、分公司、年度、季度这4个字符串。也就是说,这个公式在进行这样的处理,

  • “数据加工”工作表的 K 列
  • 为“啤酒北海道20091Q”字符串时
  • 统计“数据加工”工作表 E 列的销售额总值

将这个 J6中的公式直接从 J6复制到 K14后,就能统计出各分公司的年度销售额。

将单元格 J6的公式一直复制到 K14

这是将最开始单元格 J6中的公式一直复制粘贴到 J6:K14的范围后,例如,选择单元格 K9,按F2 键后,就会变成下表中的状态。

单元格 K9中含有 SUMIF 函数

第二参数引用了4个单元格的 SUMIF 函数,但 A9、K4这两个单元格看上去引用的是空白单元格。这里其实使用了一个秘诀。

下图是选中 A 列的状态。选中后,各个种类中的“啤酒”、“发泡酒”不仅直接出现在单元格中,同时也以白色文字的状态存在于 A 列空白位置的单元格中。

在 A 列中的文字变为白色

当然,这些文字是作为 SUMIF 的参数输入到单元格中的,但如果所有的单元格中都出现这些文字也会让人无法看懂表格中的内容,所以只保留一个单元格中的文字,将其他单元格中的相同文字的字体颜色设置为白色。在第4行中才采用了相同的处理方法。

接下来,用 SUMIF 函数统计出总和,并在同比率的单元格中加入今年÷去年的除法公式,就可以得出同比率。

这时,在有同比率的表格中事先设置“数值小于100%时单元格内文字变红且加粗”这样的格式,就可以像下图中这样自动判定计算结果并改变文字格式。

通过附带条件格式将同比率的数值小于100%的单元格中的文字设置成红字且加粗

然后,按照以下的操作顺序在其他单元格中填入公式,即可完成表格。

➊ 选中啤酒的第一季度(1Q)范围,按Ctrl +C 复制。

➋ 指定粘贴的范围。

➌ 按Ctrl +V 粘贴。

如此一来,只要在一开始制作自动汇总表的格式,就能多次利用,也就不用再重复相似的统计工作了。通过这个机制,只要在“数据加工”工作表的特定位置粘贴元数据,函数就会自动计算数据,表格也就制作完成了。

这样,1分钟就能完成原本需要2小时才能完成的工作。如果是使用数据透视表的话,即使做到天黑,制作好的资料也可能会有错误,根本无法使用。

请勿使用数据透视表(Pivot Table)

在 Excel 中,有一个方法可以快速统计出数据库形式的数据,那就是数据透视表(Pivot Table)。通过实际的使用案例,我们来看一下它有哪些作用。

➊ 选择需要统计的数据库表格中的任意一个单元格,【插入】选项卡➛点击【数据透视表】。

➋ 在下面弹出的【创建数据透视表】窗口中点击【确定】。

这里,已经勾选数据透视表选项卡➛【视图】中【使用历来数据透视表设定(可拖拽格子内的区域)】的画面,勾选这一选项,使用数据透视表时会更加方便。

➌ 在画面右侧,会出现选择的数据库表项目一览的字段列表画面。在此,勾选【零售商区域】,数据透视表的纵轴就会出现“零售商区域”。

➍ 在字段列表中勾选【销售金额】,就可以按照零售商区域统计金额。

➎ 接下来将【商品代码】的部分拖拽复制至画面右下方的【列区域】中,就可以按照“零售商区域”“商品代码”来统计金额。

像这样,数据透视表可以简单制作各种统计表格。因为其强大的统计功能,被认作是“制作表格的必备工具”,在实际工作中经常被使用。但它有个很大的漏洞,那就是:

“如果把定期更新的资料为源数据制作成数据透视表,就会大大降低制作效率。”

这一点请务必记住。

大家经常犯的错误就是不断使用数据透视表进行汇总,然后复制到表格中,并且不断重复这样的操作。这种做法不仅非常没有效率,而且在复制粘贴过程中也非常容易产生错误。

Excel将新的统计标准追加添加到元数据中

我们再确认一下最终表格的结构。

纵轴为“种类”和“分公司”。横轴为按季度分类的2009年和2010年,这两个年度的项目。

但是,元数据中却没有这些项目。

实际上这里需要转化数据。

  • 商品名称➛种类
  • 县名➛分公司
  • 销售日期➛年度、季度

具体来说,需要在元数据中的作业列中追加转化后的数据。最终,元数据会变成下表的状态。

在元数据中追加转化后的数据

F 列的“种类”,是参照 C 列的商品代码输入的。另外,G 列的“分公司”,则是参照 B 列的县名输入的。

以第2行的数据为例,数据发生了如下转化。

  • 爱知县➛中部
  • 27210786➛发泡酒

为了快速完成这种转化,需要预先制作转化对应表(转化表)。比如在其他工作表中制作下图这样的表格。

事先制作这样的变化对应表作为准备材料,之后再用 VLOOKUP 函数就可以转化所有数据。

我们来逐个看一下追加转换后的数据的函数。用来制作转化数据的表格的名称为“变化表”。

  • F 列(种类)➛=VLOOKUP(C2,变化表!A:C,3,0)
  • G 列(分公司)➛=VLOOKUP(B2,变化表!E:F,2,0)
  • H 列(年度)➛=LEFT(A2,4)
  • I 列(月)➛=VALUE(RIGHT(A2,2))
  • ※只使用 RIGHT 函数的话会得出“01”这样的字符串,因此作为在 J 列中的 VLOOKUP 函数的第一参数使用时会出现错误,应用 VALUE 函数将其转化为数值。

  • J 列(季度)➛=VLOOKUP(I2,变化表!H:I,2,0)
  • K 列(KEY)➛=F2&G2&H2&J2

※用于输入统计表中的 SUMIF 函数的第一参数(检索范围)。

在第2行中输入这些函数,然后一直复制到数据最后一行(双击鼠标就可瞬间完成),就能快速转化数据。元数据中没有的项目,可以通过函数自行追加,按照自己的想法统计数据。这样一来,就整理好了用作材料的数据。

将数据资料迅速转化为表格的技巧

下图为前文中提到的表格的全貌。

前文中的表格全貌

这张表是根据同一张表内的【数据加工】工作表中的数据为材料制作的。

作为表格的材料的【数据加工】工作表中数据

这张表格由5个项目构成。每一列项目名的单元格中主要有以下项目:

  • A 列➛表示销售日期的六位数值
  • B 列➛零售商区域
  • C 列➛商品代码
  • D 列➛商品名称
  • E 列➛销售额

也就是说,这些数据表示的是在某一段期间内,按月份、地区、商品来分类的销售额数据。但是,浏览这个放有大量信息的数据表,即使我们只能了解某些事实,却还是不能知道整体趋势和实际业绩。

那么,如果让你“以这个数据为材料分析销售情况”,应该从哪里着手呢?

首先,如果你能立刻想到同比率、达成率和结构比率三个基本指标的话,就能够快速开展分析工作。