欢迎关注天善智能微信公众号，我们是专注于商业智能BI，大数据，数据分析领域的垂直社区。

本文为小编学习天善学院李奇老师用数据说话-Excel BI商业智能分析零基础精讲课程第一章第五节笔记。

Excel商业智能分析中常用的到的五种方法：数据标准化、加权平均、转换变量类型、直方图、盒须图。

重要性：不同指标进行对比评价时，经常会遇到由于各指标间的性质，量纲，或者是数量级的不同而造成各指标间的水平相差很大时，直接用原始指标值进行数据分析，就会突出数值较高的指标在综合分析中的作用。为了保证结果的可靠性，需要对原始的指标数据进行标准化处理。

方法：主要介绍2种：

MIN-MAX标准化：新数据=（原数据-极小值）/（极大值-极小值）

注：原数据映射在0-1区间内，同意数量级，方便进行进一步比较、分析

Z-SCORE标准化：新数据=（原数据-均值）/标准差

注：围绕0上下波动，大于0说明高于平均水平，小于0说明低于平均水平。

利用交叉表求权重方法介绍：

1. 纵向和横向对比，横向重要则为1，纵向重要为0

2. 横向加总

3. 每个阶段合计值/合计总值*100%

实操部分：

变量类型：

1. 名义型变量： 值与值之间没有等级顺序之分，仅代表不同类的事物。

例： 性别、民族、职业

2. 有序型变量： 值与值之间有等级顺序之分，不仅能够代表事物的分类，还能代表事物按某种特性的排序。

例： 销售阶段、优良中差

3. 连续型变量：不仅能将变量区分类别和等级，而且可以确定变量之间的数量差别和间隔距离。

例： 营业额、身高、体重

实操：

频数与频率

频数是落在各类别中的数据个数。各类别频数与总频数之比称频率。频数和频率分别从绝对数和相对数上，反映出数据在各变量值上的分布状况。（学习直方图前了解这两个概念）

组距=（最大值-最小值）/组数

1. 选择数据

2. 设置接受区域

3. 调整频率分布

直方图与柱形图区别：柱形图看的是高度，对比数值，直方图看的是面积，组距内频率的分布情况，展现整体分布趋势。

实操：

调出数据分析库之后打开直方图

设置好参数

注意：此时显示的是频数而不是频率，需要调整一下。

设置好之后调整直方图，邮件调出数据系列格式，“分类间距”调为“0”。加上相应轮廓即可。

盒须图用来体现数据分散情况，版本建议excel2016版。

四分位数：将数据由小到大排列并分成四等份，处于三个分割点位置的数值就是四分位数

上边缘 = Q3+1.5*（Q3-Q1）

下边缘 = Q1-1.5*（Q3-Q1）

通过盒须图，可以清晰发现一组数据的分散情况。

实战：

选中所有数据之后，找出所有图标中的“箱形图”

本章笔记就到这里，后续继续更新哈，欢迎大家关注。感兴趣的同学也可以留言一起交流学习，记得点赞哈。

更多精彩内容，请登陆天善学院：hellobi。

其实我要统计的就是每个人各有几个甲乙丙等，现在只能做到统计姓名的个数（甲乙丙无法分开统计）

要统计的表格

67

我用的方法

6767

这是最后要的结果

哪位高手有简单的方法，指导下，谢谢。

转载自百家号作者：奥德王打电脑

关键字统计查询是在Excel中比较常用的一个函数，比如产品推介会后收集到的与会名单统计。老板问你来了多少男的？多少女的？A类产品意向多少？B类产品意向多少？怎么办，这就要用到关键字统计查询了。

上图演示了关键字统计查询应用的效果，用到的Excel函数是COUNTIF。

COUNTIF ：计算某个区域中满足条件的单元格数目

Excel中统计关键字函数步骤使用如下：

第一步，准备好表格，源数据表和统计表格；

第二步，输入“=COUNTIF(表3[性别],"男")” ，回车；

COUNTIF函数参数解释：第一个参数是指要统计的数据区域，第二个参数是要统计的关键字（需要引号括起来）；

注意：Excel中函数参数中用到的符号，如逗号等都是半角英文符号，即在英文状态下输入；

第三步，其他统计项目依次填写对应函数式；

咦！有什么奇怪的东西混进来了，怎么性别统计都是零呢？

这就需要用到一些编程小知识了，需要在第二个参数中加一个通配符“*”号，像这样“=COUNTIF(表3[姓名],"张*")”。

通配符 “*” 星号，用来表示匹配任意字符。除了这个常用的公式符号，还有以下这些常用的符号：不等于 (<>)、大于 (>) 、小于 (<)、等于 (=)，用来对数值进行统计比较。

你学会了吗？有问题请留言。祝，安好。

可能会遇到的问题：

输入法问题导致的参数错误？输入参数用的各种符号时，请将输入法调至英文输入状态；为什么没能返回预期得值？请确保函数及其参数输入正确；为什么统计出来的没有进度条显示？给单元格设置个数据条件样式就可以了；

1、一列数据同时除以10000

复制10000所在单元格，选取数据区域 - 选择粘性粘贴 - 除

2、同时冻结第1行和第1列

选取第一列和第一行交汇处的墙角位置B2，窗口 - 冻结窗格

3、快速把公式转换为值

选取公式区域 - 按右键向右拖一下再拖回来 - 选取只保留数值。

4、显示指定区域所有公式

查找 = 替换为“ =”（空格+=号） ，即可显示工作表中所有公式

5、同时编辑所有工作表

全选工作表，直接编辑，会更新到所有工作表。

6、删除重复值

选取数据区域 - 数据 - 删除重复值

7、显示重复值

选取数据区域 - 开始 - 条件格式 - 显示规则 - 重复值

8、把文本型数字转换成数值型

选取文本数字区域，打开左上角单元格的绿三角，选取 转换为数值

9、隐藏单元格内容

选取要隐藏的区域 - 设置单元格格式 - 数字 - 自定义 - 输入三个分号;;;

10、给excel文件添加密码

文件 - 信息 - 保护工作簿 - 用密码进行加密

11、给单元格区域添加密码

审阅 - 允许用户编辑区域 - 添加区域和设置密码

12、把多个单元格内容粘贴一个单元格

复制区域 - 打开剪贴板 - 选取某个单元格 - 在编辑栏中点击剪贴板中复制的内容

13、同时查看一个excel文件的两个工作表

视图 - 新建窗口 - 全部重排

14、输入分数

先后输入 0 ，再输入 空格， 再输入分数即可

15、强制换行

在文字后按alt+回车键即可换到下一行

16、删除空行

选取A列 - Ctrl+g打开定位窗口 - 定位条件：空值 - 整行删除

17、隔行插入空行

在数据表旁拖动复制1~N，然后再复制序号到下面，然后按序号列排序即可。

18、快速查找工作表

在进度条右键菜单中选取要找的工作表即可。

19、快速筛选

右键菜单中 - 筛选 - 按所选单元格值进行筛选

20、让PPT的图表随excel同步更新

复制excel中的图表 - 在PPT界面中 - 选择性粘贴 - 粘贴链接

21、隐藏公式

选取公式所在区域 - 设置单元格格式 - 保护：选取隐藏 - 保护工作表

22、行高按厘米设置

点右下角“页面布局”按钮，行高单位即可厘米

23、复制时保护行高列宽不变

整行选取复制，粘贴后选取“保持列宽。

24、输入以0开始的数字或超过15位的长数字

先输入单引号，然后再输入数字。或先设置格式为文本再输入。

25、全部显示超过11的长数字

选数区域 - 设置单元格格式 - 自定义 - 输入0

26、快速调整列宽

选取多列，双击边线即可自动调整适合的列宽

27、图表快速添加新系列

复制 - 粘贴，即可给图表添加新的系列

28、设置大于72磅的字体

excel里的最大字并不是72磅，而是409磅。你只需要输入数字即可。

29、设置标题行打印

页面设置 - 工作表 - 顶端标题行

30、不打印错误值

页面设置 - 工作表 - 错误值打印为：空

31、隐藏0值

文件 - 选项 - 高级 - 去掉“显在具有零值的单元格中显示零”

32、设置新建文件的字体和字号

文件 - 选项 - 常规 - 新建工作簿时....

33、快速查看函数帮助

在公式中点击下面显示的函数名称，即可打开该函数的帮助页面。

34、加快excel文件打开速度

如果文件公式过多，在关闭时设置为手动，打开时会更快。

35、按行排序

在排序界面，点击选项，选中按行排序

36、设置可以打印的背景图片

在页眉中插入图片即要

来源：网络整理

百家号-【袁帅数据分析运营】运营者：袁帅，会展业信息化、数字化领域专家。新社汇平台联合创始人，永洪数据科学研究院MVP。认证数据分析师、网络营销师、SEM搜索引擎营销师、SEO工程师、中国电子商务职业经理人。畅销书《互联网销售宝典》联合出品人。

COUNTIF函数虽然是一个简单的函数，但是它在办公应用中相当广泛，countif函数的功能是用来计算个数，表示计算区域中满足给定条件的单元格的个数。

首先我们来了解下这个函数的基本定义，该函数的语法规则如下：

=countif（range，criteria），其中：

第一个参数：range 要计算其中非空单元格数目的区域

第二个参数：criteria 以数字、表达式或文本形式定义的条件

举个例子更好理解：下面这个表格中，我们需要统计含有“老刘”的单元格有多少？

输入函数公式=COUNTIF(A2:A10,"老刘")

也可以直接插入函数，如下图：

这个公式使用起来非常灵活，也可以统计其他的，如下图，统计大于或等于20的单元格有多少？

真的很简单是不是？朋友们，学会了吗？

职工工资核算与管理是整个企业财务核算与管理中是一个十分重要的部分，也是财务部门最基本的业务之一。传统的手工核算不仅效率低且容易出错，利用excel来进行薪酬管理可以提高工作效率同时又能确保核算的准确性。

下面我们重点介绍：

工资数据的录入

工资数据的查询与汇总

一、工资数据的录入

基本项目的录入。尽管各企业工资管理制度有所不同，但是在一些基本构成项目上大致相同，我们在excel中建立一个工作表来记录这些基本项目数据。如下图所示输入表头和工资项目。

分别对性别、部门、职工类别进行数据的有效性设置（2016版本中为数据验证）。性别的有效性条件为“男，女”，部门和职工类别可以先定义名称，再在有效性条件来源中引用名称。

设置完毕后可以拖动填充柄向下填充至其它单元格。

最后录入基础的数据资料（编号、姓名、性别、部门、职工类别、基本工资、事假天数、病假天数等）。如果数据量很大，可以通过数据记录单添加数据。

基础数据录入完毕后，再录入其他工资项目数据，假定其他项目数据具体规定如下：

根据职工类别的不同发放岗位工资和住房补贴

根据上月各部门效益发放本月奖金

病事假扣款规定

个人所得税税率表

养老保险按照（基本工资+岗位工资）*8%扣款

医疗保险按照（基本工资+岗位工资）*2%扣款

分别设置公式计算其他工资项目。

这里用vlookup查找的方式计算出岗位工资、住房补贴和奖金，也可以用if函数嵌套的方式计算。

至此，我们完成了上述职工工资统计表的填制。

个人代扣税用if函数嵌套公式很长，我们也可以用lookup来计算，需要在个税税率表中添加一个数据为升序的辅助列。

工资统计表制作完成了，我们还需要对职工工资数据进行查询，需要制作职工工资条发放给每位员工，需要依据部门和职工类别进行统计分析。这些内容我们后期再聊。

今天，带大家梳理一下excel统计函数中涉及“最”的函数和公式。

一、求最大值

1、B列最大值

=Max(B2:B11)

=Large(B2:B11,1)

2、A产品最大值（数组公式，按ctrl+shift+enter完成输入）

{=MAX((A2:A11="A")*B2:B11)}

二、求最小值

1、B列最小值

=Min(B2:B11)

=Small(B2:B11,1)

2、A产品最小值（数组公式）

{=MIN((A2:A11="A")*B2:B11)}

三 、出现频率最高的数字

1 、返回最多的其中一个

=MODE(A2:A10)

2、频率最高如果有几个是相同的，以数组形式返回

=MODE.MULT(A2:A10)

四、最接近的数（数组公式）

{=INDEX(A:A,MAX(ROW(A2:A10)*((ABS(A2:A10-100)=MIN(ABS(A2:A10-100))))))}

五、最后的值

1、A列最后一个数字

=Lookup(9^9,a:A)

2、A列最后一个文本

=Lookup("座",A:A)

3、A产品最后一次入库单价

=lookup(1,0/(c5:c10=b13),d5:d10)

六、日期之最

1、本月最后一天日期

=EOMONTH(TODAY(),0)

2、本月第1个星期日

=DATE(YEAR(NOW()),MONTH(NOW()),1)+7-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1),2)

3、本月最后1个星期日

=DATE(YEAR(NOW()),MONTH(NOW())+1,0)-MOD(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0),2),7)

今天列出的几组excel公式在数据统计中非常的重要，请同学们务必收藏起来备用。

在excel中可以利用FREQUENCY函数进行频数统计，利用“数据分析”中的“直方图”宏程序进行频数分析。

函数 FREQUENCY 可以计算数值在某个区域内的出现频率，然后返回一个垂直数组，

由于 函数FREQUENCY 返回一个数组，所以它必须以数组公式的形式输入。

通过频数分布函数可以对数据进行分组和归类，从而使数据的分布形态更加清楚地表现出来。

下图是某课程的学生成绩，我们对其进行统计分析。

我们把学生成绩进行分段0-60、60-70、70-80、80-90、90-100。

我们在C2：C5区域一次输入59、69、79、89作为频数的接收区域（输入的不是每组上限，上限不在本组内）,因为分5组，所以有4个分段点，最后一个不用分段点，但是在计算时要包含进去。

选中D2:D6区域，编辑栏中输入：=FREQUENCY（B2：B25，C2：C5），再按Ctrl+Shift+Enter组合键，得到频数分布结果。

重新整理得到频数分布表。

我们利用直方图也可以获得各组的频数，在菜单“数据”选项卡下“数据分析”选项。如果没有找到需要先在excel选项中的“自定义功能区”中把“开发工具”选中。

然后打开“开发工具”加载项，选中“分析工具库”和“分析工具库-VBA函数”复选框。

这时我们打开“数据”选项卡，单击“数据分析”，弹出“数据分析”对话框。我们选择“直方图”。

在弹出的“直方图”对话框中选工作表中B2:B25填入输入区域，C2:C5填入接收区域。在输出区域中选一个单元格作为输出图表的起始区域，我们这里选G11，同时选中“累计百分比”、“图表输出”。（选中相应单元格区域系统自动加上绝对引用符号）如下图所示：

单击确定按钮，得到频数分布表、直方图及累计频数图。

朋友们

今天

我们来谈谈COUNTIF函数

接触过EXCEL的朋友

肯定都知道

COUNTIF函数

它是统计

某个单元格区域中

符合指定条件的单元格数目

比如以下这个例子

要统计出A1至B13单元格区域中

分数为95的人数

我们在D2单元格中输入公式

=COUNTIF(B2:B13,95)

结果就出来了

上面的例子

统计的是一个数据

在一个区域中的个数

那如果我们要统计的是

单元格颜色呢

如下图

我们还能使用

COUNTIF函数吗

其实

要统计每个颜色

在单元格中的个数

单单用COUNTIF函数是实现不了的

但是我们可以使用

自定义函数

具体操作如下

首先

选中A1单元格

点击【公式】—【定义名称】

在对话框中

名称设置为【颜色】

引用位置输入为

【=GET.CELL(38,Sheet1!A1)】

（38表示单元格颜色）

并点击确定

公式说明：

GET.CELL函数可返回引用单元格的信息

即返回A1单元格中的颜色的名称

接着

在A列中输入【=颜色】

并向下填充

选择【不带格式填充】

即可得出每个颜色的代码

最后

在B列中输入公式

=IF(COUNTIF($A$1:A1,A1)=1,COUNTIF($A$1:$A$11,A1),"")

即可得到我们最终需要的结果

公式说明：

=COUNTIF($A$1:A1,A1)

指在$A$1:A1区域中A1单元格中的颜色第几次出现

如果A1单元格中的颜色是第一次出现

则返回=COUNTIF($A$1:$A$11,A1)

即A1单元格中颜色出现的总次数

否则返回空值

好了

我们的COUNTIF函数

就讲到这了

今天我们讲的主要内容

就是由COUNTIF函数的

应用中延伸出的问题

所以

看了今天的内容

你们是不是

对COUNTIF函数的了解更深了

对如何统计单元格中颜色的个数也清楚了

如果是的话

就给小编点个赞哟

心事留言，小编答你

在excel中我们有时候需要将多个图表组合在一起，以便我们更清楚地查看其中的数据。以下面的“成本费用预算表”为例，看看如何应用组合图。

首先我们插入“簇状柱形图”，选择数据源，设置图例、标题等得到如下图表。

这时我们发现：图表中没有出现“增减率”的图。因为“增减率”的数据远远小于“上年实际”、“本年预算”的数据，所以在图表中，“增减率”数据不能有效地呈现出来。如何解决？我们想到设置次坐标轴的方法，来显示增减率数据，也即创建组合图来呈现。

方法：1、选择“增减率”图例项，鼠标右键选择更改图表类型，得到下图所示：

2、选择次坐标轴“增减率”，图表类型选择“带数据标记的折线图”，下图所示：

单击确定，组合图创建完毕，最终效果如下：

“增减率”数据清楚地呈现出来了。

office微信公众号

相关链接：

制作九九乘法口诀表

频数分析||Excel在统计中的应用

Word也能制作工资条

AND、OR、NOT函数使用介绍

EXCEL中获取外部数据

15个模式化的EXCEL表格公式

超简便的个税计算方法