excel数据太多，如何批量、快速的判断并进行统计呢？

举个例子，假如公司需要进行优秀员工数量统计，我们现在有这样一些excel数据:

我们需要统计的excel数据

我们现在的要求是，计算excel里完成力、执行力、创新力任意一项大于98，就算优秀员工，有人说，这个不是很简单吗？我们来数一数，张三执完成力96、执行力65、创新力78，不满足，李四完成力73、执行力83、创新力83，不满足，以此类推，当然，数量少是没有问题的，不过要是员工人数几千或者上万呢，我的天呐，要累死人啦！

那么，excel里有快速的办法吗？有的！

excel，需要显示优秀员工的单元格输入=IF(OR(B2>=95,C2>=95,D2>=95),"优秀员工","")，向下填充，在需要统计人数的单元格输入=COUNTIF(E2:E9,"优秀员工")，哇，瞬间得到结果，何惧几千上万呢？

excel瞬间完成的计算

excel需要的单元格输入函数

这是什么意思呢？if函数就是假如的意思，or就是或者，意思就是假如引用单元格1大于等于98，或者引用单元格2大于等于98，或者引用单元格3大于等于98，假如有1个条件满足，就是优秀员工，否则的话就为空。

COUNTIF自然就是计算满足条件的单元格个数啦。

怎么样，很简单吧，你学会了吗？快去试试吧！非常感谢您观看文本，如果你有更好的方法适用这个情况，请您留言分享！分享好玩、有趣、实用的手机和电脑技巧，您的支持就是我的最大动力！

大家好，我是牧野，在纽约的一家app公司做数据处理。

今天和大家分享一个很常见的小问题，如何用excel中的公式来计算出每月/每年所需要的日期出现了几次。

在计算这个的时候一般为了干净起见，我会新开一个空白的表格，然后将所需要的数据放在左侧，如下图三栏，date,fruit,amount.

然后在右侧建立一个新的小表格，开头写成Year(年), Month(月）, Count(计数).在这里年输入格式为2013，如果是1月这里我们就写1即可。

接下来就要重点介绍我们的函数了--SUMPRODUCT

在图示中的例子中，我们想要计算出在A列第2行到第24行中2013年1月出现的次数是多少，这时我们用的函数具体写为：

=SUMPRODUCT((MONTH($A$2:$A$24)=F2)*(YEAR($A$2:$A$24)=$E$2))

其中我们要注意的是：

1. A列第2行到第24行我们采用了绝对引用---$A$2:$A$24

2. 年和月的连接处我们用的是乘号*

3.我们对2013年这个在E2格的数据也采用了绝对引用$E$2

4.我们想要计算其他月份的时候就把鼠标拖到表格的右下角，看到加号之后就进行拖拽。

最后就会像上图一样做好啦~

通过这个办法我们可以计算出月度新增用户数量，月度用户进行预约的次数，适合分析用户增长情况。

在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，同时选中“累计百分比”、“图表输出”。（选中相应单元格区域系统自动加上绝对引用符号）如下图所示：

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

excel数据量太多，如何进行快速、批量的判断并统计数量呢？

假如你是一个教师，你们学校的所有学生成绩都归你统计，哈哈，这个时候你开心了吧，一个学校好几千个学生，别人给了你这样的excel成绩表：

excel成绩表

领导让你统计一年级优秀学生人数是多少？条件是分数大于等于95。这个时候有人会说了，这个不是很简单吗？我们一个一个去查，一年级，张三96，一个优秀，二年级李四98,2个优秀，依此类推，我的天哪，数量少当然没问题，但是学校好几千个人呐，这是要累死的节奏吗？当然不会。

你这样，在excel里需要统计优秀学生的单元格输入=COUNTIFS(A2:A9,"一年级",C2:C9,">=95")，一瞬间，哇哦，结果出来了，几千也不怕：

excel瞬间得到优秀学生结果

其他年级excel也是一样的瞬间得到结果

哎呀，这是个什么意思呢？为什么excel会这么神奇呢？COUNTIFS是条件计数，A2:A9是引用范围，我们这里的范围是一年级，所以自然是引用年级这列，"一年级"自然是条件啦，还有另一个条件就是分数大于等于95，所以我们写上C2:C9,">=95"，一样的C2:C9是范围，">=95"是条件。

怎么样，很简单吧，你学会了吗？快去试试吧！非常感谢您观看文本，如果你有更好的方法适用这个情况，请您留言分享！分享好玩、有趣、实用的手机和电脑技巧，您的支持就是我的最大动力！

回归分析法，即二元一次线性回归分析预测法

先以一个小故事开始本文的介绍。十三多年前，笔者就职于深圳F集团时，曾就做年度库存预测报告，与笔者新入职一台籍高管Edwin分别按不同的方法模拟预测下一个年度公司总存货库存。令我吃惊的是，本人以完整的数据推算做依据，做出的报告结果居然与仅入职数周，数据不齐全的Edwin制定的报告结果吻合度达到99%以上。仍清楚记得，笔者曾用得是标准的周转天数计算公式反推法，而Edwin用的正是本文重点介绍的二元一次线性回归分析法。

二元一次线性回归分析法是一种数据分析模型。

在EXCEL函数公式是FORECAST（英文意思是：预测），其用途是根据一条线性回归拟合线返回一个预测值，此函数使用可对未来销售额、库存需求或未来数据趋势进行预测分析。

要做好库存预测须具备几个条件，首先须具备过去较长的某个时间段的完整整的数据。这里说的时间段最好是上一年度一整年或最近两年的数据。

完整的数库据指的是需要有年度对应每个月的实际库存与营收额或销货成本。

同样我们把库存预测肢解成几个关键步骤。

第一步：数据准备，依要求对EXCEL公式数据输入

先看一组实际的数据，其中蓝色字体是已知具备的数据，黄色则是需要预测的库存数据。预测库存，则至少需要具备的数据是标注蓝色三行数据。为别是：上一年度月营收，上一年度月实际库存，本年度月营收目标。可参照始下截图与视频。

第二步：依KPI目标调整预测数据

假设要求实际目标要求对总体存货周转率提升10%，则总体平均存货库存也减少10%，具体数据如下截图标注粉色行。

第三步：把总库存分解成不同物料形态的库存。这里讲的不同类别可以指的是：

物料形态分类：原材料、半成品、在制品以及成品等。

仓码分类：原材料仓、包装仓、成品仓、重要物资仓、五金仓、配件仓以及辅助物料仓等。

这里我们以第一种物料类型实例说明。须依据上年度不同物料类别占总库存的比率，再计算对应类别库存总额，如下截图。

第四：验证二无一次线性回归分析方法的准确度。

存货周转天数=(（期初库存+期末库存）/2*30）/(营收*物料成本率）=(平均库存*30）/销售成本。

依公式反推预测库存，平均库存=（目标周转天数*营收*物料成本率）/30，前提需要更多的数据信息，包括物料成本率与以往的周转天数做为计划依据。

如下截图，两种不同的方法得出库存预测吻度为97%（或103%）。

企业管理中，要快速地对企业活动做出判断，需要完整的数据管理积累支撑

二元一次回归分析法做库存预测速度快，效率更高。而标准的周转天数计算预法会更准确与准确。到底应当选择哪个方法？不同的时期，不同的方法如何选择则是仁者见仁，没有对或错，只有合适与否。但有肯定的一点，那就是类似二元一次回归分析法管理工具的熟练应用，则一定对会对企业管理起到更好的帮助，在做数据调研时也是个好的选择。

说到Excel表格的使用，很多人首先想到的肯定是求和、统计、函数、透视表等等，对于这些常用的Excel功能技巧，我们就不多说了，

其实在Excel表格中，它还有一个隐藏的小技巧——快速分析，不知道有多少人知道了？

1.图表功能

在Excel表格制作中，我们可以通过快速分析功能快速制作图表，其实用这个快速分析功能是非常简单的，首先选中【数据】-点击数据右下角的【快速分析功能】-选择【图表】，这时我们选择的图表就会根据单元格中的数据进行变化，用这个完成图表的创建是十分的简单、方便。

2.数据条功能

数据条功能其实是相对于【条件格式】，首先全选数据，然后通过点击快速分析中的【格式化】选择【数据条】，然后点击【色阶】进行对数据的填充，最后点击【大于】标记处特定大小数据，这样我们的整个数据条就会变得很清晰

3.迷你图功能

迷你图功能也是包含在快速分析功能中的，其实他的操作和上面的差不多，都需要先选中数据，然后点击快速功能中的迷你图，然后我们可以对创建好的迷你图进行修改，如，点击【设计】，标出最高点。

4.汇总功能

很多时候，我们需要对数据进行汇总求和，有的是需要利用公式才能进行计算，今天的这种最常规的计算是不需要进行输入公式或是函数的。具体操作如下：

选中数据区域，然后通过快速分析功能选择【汇总】，然后就可以对数据进行求和、求平均值等计算。

5.表格功能

虽然我们可以用菜单中的功能对数据制作透视表，但今天我们不用菜单栏中的功能制作，用的是Excel中隐藏的功能——快速分析功能

首先选中数据区域，然后利用快速分析功能中的【表格】功能，然后点击【透视表】，就可以对数据制作透视表了。

在Excel表格制作时，这些都是我们需要用到的一些小技巧，虽然这只是一个小小的功能，但可以给你带来不少的便捷了，喜欢就赶紧动手试试吧。

如果有一张上千行的销售表，像下图（各个地区按天统计的1至12月的物品销量），要统计每个月，每个地区的数据，当看到上千行的数据，是不是发愁无从下手呢，利用数据透视表，轻松搞定。

1、选中数据表中任意单元格，点击工具栏插入——数据透视表。弹出创建透视表对话框，点击确定。

2、在右边数据透视表字段对话框添加字段，这里勾选订购日期、地区、分类、销售额和成本。字段勾选根据数据分析的要求勾选。

勾选后，左边单元格会生成下图所示数据表。

3、但是日期是按天来统计，我们需要的是按月统计，选中行标签统计的某一天的单元格，例如2015/1/24，右键创建组，在组合对话框中，选择月，确定。

4、确定后生成下图所示的统计表，但是原始数据没有统计利润，这里我们为了说明问题，我们简单统计利润。

5、增加字段，统计利润，假设利润为销售额减去成本，点击数据透视表工具的分析菜单选项。

6、点击字段、项目和集——计算字段。

7、在插入计算字段对话框中，名称填写利润，公式填写=销售额-利润，确定。

8、则上千行的数据按月份统计完成。

这里只是数据透视表的基本用法，数据透视表还可以排序、筛选，还可以转换称图表，还有很多更强大用途。

快捷键

Excel的快捷键很多，以下主要是能提高效率。

Ctrl+方向键，对单元格光标快速移动，移动到数据边缘（空格位置）。

Ctrl+Shift+方向键，对单元格快读框选，选择到数据边缘（空格位置）。

Ctrl+空格键，选定整列。

Shift+空格键，选定整行。

Ctrl+A，选择整张表。

Alt+Enter，换行。

Ctrl+Enter，以当前单元格为始，往下填充数据和函数。

Ctrl+S，快读保存。

Ctrl+Z，撤回当前操作。

如果是效率达人，可以学习更多快捷键。Mac用户的Ctrl一般需要用command替换。

格式转换

Excel的格式及转换很容易忽略，但格式会如影随形伴随数据分析者的一切场景。

通常我们将Excel格式分为数值、文本、时间。

数值常见整数型 Int和小数/浮点型 Float。两者的界限很模糊。

文本分为中文和英文，存储字节，字符长度不同。

时间格式在Excel中可以和数值直接互换，也能用加减法进行天数换算。

时间格式有不同表达。例如2016年11月11日，2016/11/11，2016-11-11等。当数据源多就会变得混乱。我们可以用自定义格式规范时间。

数组

数组很多人都不会用到，甚至不知道有这个功能。。

数组由多个元素组成。普通函数的计算结果是一个值，数组类函数的计算结果返回多个值。

数组用大括号表示，当函数中使用到数组，应该用Ctrl+Shift+Enter输入，不然会报错。

先看数组的最基础使用。选择A1

1区域，输入={1,2,3,4}。记住是大括号。然后Ctrl+Shift+Enter。我们发现数组里的四个值被分别传到四个单元格中，这是数组的独有用法。

我们再来看一下数组和函数的应用。利用{}，我们能做到1匹配a，2匹配b，3匹配c。也就是一一对应。专业说法是Mapping。

=lookup(查找值，{1,2,3}，{"a","b","c"})

分列

Excel可以将多个单元格的内容合并，但是不擅长拆分。分列功能可以将某一列按照特定规则拆分。常常用来进行数据清洗。

上文我有一列地区的数据，我想要将市和区分成两列。我们可以用mid和find函数查找市截取字符。但最快的做法就是用“市”分列。

合并单元个格

单元格作为报表整理使用，除非是最终输出格式，例如打印。否则不要随意合并单元格。

一旦使用合并单元格，绝大多数函数都不能正常使用，影响批量的数据处理和格式转换。合并单元格也会造成Python和SQL的读取错误。

数据透视表

数据透视表的主要功能是将数据聚合，按照各子段进行sum( )，count( )的运算。

下图我选择想要计算的数据，然后点击创建透视表。

此时会新建一个Sheet，这是数据透视表的优点，将原始数据和汇总计算数据分离。

数据透视表的核心思想是聚合运算，将字段名相同的数据聚合起来，所谓数以类分。

列和行的设置，则是按不同轴向展现数据。简单说，你想要什么结构的报表，就用什么样的拖拽方式。

删除重复项

一种数据清洗和检验的快速方式。想要验证某一列有多少个唯一值，或者数据清洗，都可以使用。

条件格式

条件格式可以当作数据可视化的应用。如果我们要使用函数在大量数据中找出前三的值，可能会用到rank( )函数，排序，然后过滤出1，2，3。

用条件格式则是另外一种快速方法，直接用颜色标出，非常直观。

冻结首行首列

Excel的首行一般是各字段名Header，俗称表头，当行数和列数过多的时候，观察数据比较麻烦。我们可以通过固定住首行，方便浏览和操作。

Header是一个较为重要的概念。在Python和R中，read_csv函数，会有一个专门的参数header=true，来判断是否读取表头作为columns的名字。

自定义下拉菜单（数据有效性）

数据有效性是一种约束，针对单元格限制其输入，也就是让其只能固定几个值。下拉菜单是一种高阶应用，通过允许下拉箭头即可。

自定义名称

自定义名称是一个很好用的技巧，我们可以为一个区域，变量、或者数组定义一个名称。后续要经常使用的话，直接引用即可，无需再次定位。这是复用的概念。

我们将A1:A3区域命名为NUM

直接使用=sum(NUM) ，等价于sum(A1:A3)。

查找公式错误

公式报错也不知道错在哪里的时候可以使用，尤其是各类IF嵌套或者多表关联，逻辑复杂时。查找公式错误是逐步运算的，方便定位。

分组和分级显示

分组和分级显示，常用在报表中，在报表行数多到一定程度时，通过分组达到快速切换和隐藏的目的。越是专业度的报表（咨询、财务等），越可以学习这块。在数据菜单下。

分析工具库

分析工具库是高阶分析的利器，包含很多统计计算，检验功能等工具。Excel是默认不安装的，要安装需要加载项，在工具菜单下（不同版本安装方式会有一点小差异）。

分析工具库是统计包，规划求解是计算最优解，类似决策树。

内容来源：网络整理

百家号-【袁帅数据分析运营】运营者：袁帅，互联网数据分析运营实践者。会展业信息化、数字化领域专家。认证数据分析师、网络营销师、SEM搜索引擎营销师、SEO工程师、电子商务职业经理人。

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工程师、中国电子商务职业经理人。畅销书《互联网销售宝典》联合出品人。

上一篇给大家分享了一下筛选功能的使用，特别要注意不能随意复制粘贴的原因和解决办法。有兴趣的朋友们可以点击或关注百家号，进去查看历史文章。

那么，今天给大家分享下EXCEL透视表功能的简单使用。通常情况下，我们需要做批量数据的统计、用excel出图表等等的时候，需要计数或者求和的结果作展示的时候都会用到。可以说是在大数据分析以及展示结果的时候，所必须会使用到的一个功能。

在此，让我们一起通过一个实例来看一下，excel数据透视表的具体使用方法。只需要简单几步，就可以完成一个简单的数据透视！

首先，我们打开一个要处理的EXCEL，比如需要统计各部门总工资。如下图的数据。通过1月到5月每个人的工资记录，来计算出部门工资的总数及每个月的走势。

第一步：选定A列到H列，即包含所有数据的列。

第二步：点击插入-数据透视表，出现一个创建数据透视表的小窗口，直接点击确定。

此时出现了一个新的sheet页，如下图。这里为了方便大家看全，我把表格横向缩小到了一起。实际上数据透视表字段是在EXCEL最右侧。

第三步：新sheet页的最右侧数据透视表字段，有一个选择要添加到报表的字段，可以看到原始表格的标题列。继续往下看，有四个区域，分别为筛选器，列，行，值。我们把月份点住，拖动到列的区域中。

再分别把部门、姓名拖动到行，部门在上。最后把工资拖动到值。

第四步：值里边默认是计数项，我们需要修改一下，工资是以求和来统计。点击计数项：工资，会出现值字段设置。打开后，选择求和，然后点击确定。

第五步：此时已经可以看到表中的数据都已经出现，每个部门每个人1月到5月工资以及总计的工资。可以点击技术部、科研部、运营部前边的-号，代表隐藏姓名；最后一列的总计，每一行代表这一行数据的总计，比如第一行代表技术部1月到5月的总计工资数目；最后一行的总计，每一列代表的是这一列数据的总计，比如1月的那一列代表1月各个部门的总计工资。这样就满足我们的需求了，可以看到每个部门在每个月以及合计的工资数目。

习惯而言，统计的数据都喜欢有高低顺序来浏览，方便一眼看出哪个部门的工资总额高低。我们可以再点击一下总计那列，然后点击排序，选择降序排列。这样就可以看到一个按高到低排序的工资图表了。

好了，本篇就给大家讲到这里，大家可以自己试着随意在四个区域里，把其他的标题也拖进去，看看会出现什么变化？其实看似枯燥的Excel工具也有非常有趣的一面，更多的技巧就留给大家自己开发吧！有什么问题欢迎留言给我们哦！