中企动力 > 商学院 > excel如何做统计
  • ?

    546.Excel技巧:如何用Excel设计一个唱票统计系统?

    毕瑛

    展开

    秦老师最近帝都培训出差,碰到天气变化,结果航班取消,住了酒店居然还没网络,正巧小伙伴问道想设计一个用于统计投票的系统,最好前三名能自动显示为红色,能显示唱票的总计,正好试试研究了一下。牛牛的搞定了这个问题。不敢独享,赶紧和小伙伴们分享一下。

    具体操作如下:

    首先需要一个如下的数据结构。

    唱票数G列区域,不能手动输入候选人票数,这样很不方便,所以我们需要一个窗体控件,用点击鼠标的方法来实现唱票。在“开发工具-插入-数值调节钮”下图3处,然后拖拽得到一个如下图的控件。(下图4处)

    接着利用控件来控制G3单元格,即第一个候选人。

    选中控件,点击鼠标邮件,在“设置控件格式-链接单元格-G3单元格”,这是点击控件的上下箭头,可以来控制单元格的票数。如下动图操作。

    依此类题,选中控件,Ctrl+C/Ctrl+V复制出剩下的五个控件,然后将其单元格链接分别对应后几个候选人对应的单元格。如下图动图所示。

    接着插入一个图表,将其置于底层,按动控件的箭头,图表的箭头就会随之移动,那么我们这个唱票系统的雏形就有了。看下图动图:

    但小伙伴们对唱票系统有要求,要求前三名的图表柱子能自动变成红色。剩余的名次的柱子保持原有颜色。而且还要对每个候选人的唱票有最终的汇总数字表达。所以我们要在插入图表之间,对数据结构进行改进。这也是本例的难点。

    在数据结构后面,新增前三名列(H列)和剩余名次列(G列)。然后将下面的公式复制到数据结构中,注意按ctrl+shift+enter来执行这个函数公式。然后在进行拖拽填充,这是系统会知道抓住候选人的前三名数字,不是前三名的候选人会用数字0来代替。

    =IFERROR(VLOOKUP(G3,LARGE($G$3:$G$8,ROW($G$1:$G$3)),1,0),0)

    该公式是一个数组公式,注意ROW($G$1:$G$3)控制选择需要变红的名词的个数,本例为前三名,如果要前六名变颜色,就要改为ROW($G$1:$G$6).

    Large函数来动态获取前三名的区域范围。

    Vlookup来匹配候选人的数值是否能匹配前三名的数值区域,从而获得该数值。

    IFERROR用让剩余没匹配的剩余(不是前三名的)数值为0。

    G列这是候选人数据所在的列。

    搞清楚这个公式,就可以对该数据模板进行随意按需的调整。

    接着我们将不是前三名的数据显示在I列,所以利用如下公式即可搞定。

    =IF(G3-H3>0,G3,0)

    有了这两个辅助列,然后在插入堆积柱形图,然后对前三列的数据统一设置为红色,即可实现动态的前三名的柱形图显示。(看动图操作)

    最难的地方搞定后,接下来开始对图表进行美化,调整图表的大小尺寸,固定坐标轴的最大高度,也就是唱票可能出现的最高值。

    这里有些技巧:

    1.拖拽图表大小的时候,按住alt键可以与单元格边缘进行吸附。

    2.调整最后一个控件的位置,然后利用对齐按钮进行快速的对齐分距调整。

    3.选中坐标轴,按ctrl+1可以快速打开坐标轴最大值设置窗口。

    设置坐标轴最大值。

    接着设置候选人对应的唱票数统计,在图表工具中插入对应的文本框。然后在编辑栏中输入=号,去引用G列对应的唱票数。再按住ctrl+shift进行快速的横向拖拽复制。

    最后修改其他的文本框对应的单元格引用。然后美化图表,增加主次网格线即制作设计完毕。

    最终效果如下:前三名会自动为红色。如果前三名票数一样,也会是红色。

    总结:利用控件+函数+图表,就可以制作出一个非常专业的动态图表展现系统。本例的唱票系统就是一个典型,希望大家能够掌握。

  • ?

    怎么用Excel自带功能统计数目?

    尹太君

    展开

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

    要统计的表格

    67

    我用的方法

    6767

    这是最后要的结果

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

  • ?

    统计篇 | Excel如何快速统计唯一项(非重复项)的个数!

    如花

    展开

    在使用Excel表格处理数据过程中,我们经常会碰到许多的重复项,重复项的出现会干扰我们统计唯一项的个数。例如表格是按照姓名以及职务输入的,但是有些人对应的多个职务,这时候我们想要快速统计出源数据中包含多少个人,即统计表格中的唯一项。本教程以Excel2013为例,教大家如何运用函数公式快速的统计出表格中的唯一项个数,希望对大家有点帮助。

    教程以下列表格A列单元格中包含重复项和唯一项两种数据为例,需要将统计唯一项的结果放入标记为黄色的单元格,即D3单元格中。首先我们需要知道我们的统计数据源区域是A2到A10单元格区域,然后复制粘贴或输入以下函数公式:=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))。

    公式解析如下:COUNTIF()函数为统计满足要求的单元格个数,SUMPRODUCT()函数得到的是对应数组或指定区域乘积之和。复制好上述公式后,选择需要放入结果的D3单元格,直接粘贴或者粘贴到编辑栏中,然后按下Enter键即可得到指定区域唯一项的统计结果。

    感兴趣的朋友,记得收藏或分享一下再走哟,您的支持是我不懈努力的动力,谢谢!(*O*)

  • ?

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

    悠悠云

    展开

    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"是条件。

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

  • ?

    Excel数据统计分析中36个小技巧

    武烨磊

    展开

    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统计各分数段的学生数量,这4种方法掌握处理数据不再是难事

    静枫

    展开

    一份学生成绩单,需要分别统计出60分以下、60-69、70-79、80-89、90-100各阶段成绩的学生数量。下面分别介绍4种方法来统计符合要求的学生数量。

    1、利用FREQUENCY函数

    FREQUENCY函数为:计算数值在某个区域内的出现频率(个数)。函数语法为:

    FREQUENCY(data_array, bins_array):

    Data_array 必需。 要对其频率进行计数的一组数值或对这组数值的引用。 如果 data_array 中不包含任何数值,则 FREQUENCY 返回一个零数组。Bins_array 必需。 要将 data_array 中的值插入到的间隔数组或对间隔的引用。 如果 bins_array 中不包含任何数值,则 FREQUENCY 返回 data_array 中的元素个数。使用FREQUENCY函数难点为函数返回的是一个数组,所以它必须以数组公式的形式输入。C2:C26 为学生成绩,E2:E6为成绩分数区间分割点,对应的分别在F2:F6统计数量。

    1)选中F2:F6单元格区域(由于是数组函数,必须选中F2:F6单元格区域),选定后在F2输入公式:

    =FREQUENCY(C2:C26,E2:E6)。

    2)公式输入完后同时按Shift+Ctrl+Enter键,会在F2:F6中分别返回满足条件的成绩个数。

    2、使用COUNTIFS函数

    COUNTIFS函数为:统计满足某个条件的单元格的数量(个数)。函数语法为:

    =COUNTIF(要检查哪些区域? 要查找哪些内容?)

    这里将学生成绩按成绩范围各自计数,E2:F6为成绩范围,在G2输入公式:

    =COUNTIFS($C$2:$C$26,">="&E2,$C$2:$C$26,"<="&F2),然后向下拖动复制到G6即可。注意C2:C26为绝对引用,按F4加"$"符号。

    3、利用数据透视表

    数据透视表处理大量数据时比较常用的方法,而且比较直观。

    4、利用分析工具库

    首先加载分析工具库。打开Excel选项,点击加载项,在管理下拉列表选择excel加载项,点击转到,勾选分析工具库。在excel数据工具栏下会加载数据分析选项。

    1)点击工具栏数据—数据分析,在数据分析对话框中选择直方图,确定。

    2)在直方图对话框输入区域和接收区域分别输入数据区域,在输出区域选择一个空单元格,下面柏拉图、累积百分率、直方图3个选项任选一个,我这里选直方图,确定。

  • ?

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

    豆豆鱼

    展开

    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自然就是计算满足条件的单元格个数啦。

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

  • ?

    Excel中如何快速统计一列中相同数据的个数

    雷欧

    展开

    Excel是办公中必不可少的工具,但是我们经常会遇到一个表格中有好多数据,并且要统计出数据相同的个数,那么我们该如何做呢?

    今天给大家分享一下,如何在Excel中快速统计出一列数据中相同数据的个数。如下:

    在这里我们要用到sumif函数。

    sumif函数是用来根据指定条件对若干单元格进行求和。(即按条件求和)

    sumif函数的语法格式=sumif(range,criteria,sum_range) Sumif(条件区域,求和条件,实际求和区域),第二个求和条件参数在第一个条件区域里。

    如何做呢?

    1、我们在表格中将B列设置为标记,数字均为1个

    2、在F列中写入函数=sumif(A:A,E2,B:B)

    即条件区域是A列,E列为求和条件,B列为实际求和区域。

    这样就很迅速简单的统计出来了。

  • ?

    5分钟搞定Excel多表统计,太简单啦!

    Diane

    展开

    同样一个任务,一个人花了5小时,吃了很多苦,做到60分;另一个人花了5分钟,轻轻松松,做到了80分。你会给第一个人点赞,夸她棒吗?

    在职场,过程不重要,结果才是最重要的。

    1.格式相同的表格,要在第一个表汇总后面所有表格D列的总量。

    格式相同的表,多少个都一样,一个SUM函数就秒杀了。

    =SUM('4.1:4.7'!D3)

    语法:

    SUM(起始表格名称:结束表格名称!单元格)

    表格名称没有规律也可以,在有特殊字符的情况下需要加''。

    我们知道*是通配符,代表所有的意思,在这里也可以用*来实现统计。

    =SUM('*'!D3)

    2.同样也是格式相同,但要汇总的形式不一样,要汇总每一个月份的总销量。

    这里是用SUM+INDIRECT函数的组合实现。

    可能很多人一开始直接就用SUM,可惜得到的是错误值。A2&"!C:C"被当成文本处理,而不是区域。

    =SUM(A2&"!C:C")

    我们正常的引用都是直接引用,如区域C:C。间接引用就是通过第三者才能获得的,如INDIRECT("C:C")。也就是说,只要嵌套个INDIRECT函数就可以。

    3.跟上一个例子很像,唯一的差别就是最后多一个合计的实发数量,而每一个表格的行数不确定。现在要获取每个表合计的实发数量。

    这种有很多种处理方法,最简单的办法有两种。

    01 查找每个表,最后一个数量。

    =LOOKUP(9E+307,INDIRECT(A2&"!B:B"))

    02 统计每个表的数量后再除以2。

    =SUM(INDIRECT(A2&"!B:B"))/2

    4.现在换成统计商品的销售量。

    明细表

    汇总表

    难度瞬间就上来了,要结合好多函数。

    =SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$6)&"月!B:B"),A2,INDIRECT(ROW($1:$6)&"月!C:C")))

    看起来很复杂,但实际上也不是太难理解。

    INDIRECT(ROW($1:$6)&"月!B:B")这部分就是获取1-6月的B列

    INDIRECT(ROW($1:$6)&"月!C:C")这部分就是获取1-6月的C列

    SUMIF(1-6月的B列,A2,1-6月的C列)就是分别统计这6个表

    SUMPRODUCT(SUMIF())就是将最终这6个表进行求和

    看了这个技巧是不是很惊喜。惊喜就分享给你的朋友吧!

  • ?

    如何用Excel函数做关键字统计

    远山

    展开

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

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

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

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

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

    第一步,准备好表格,源数据表和统计表格;

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

    COUNTIF函数参数解释:第一个参数是指要统计的数据区域,第二个参数是要统计的关键字(需要引号括起来);

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

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

    咦!有什么奇怪的东西混进来了,怎么性别统计都是零呢?

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

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

    你学会了吗?有问题请留言。祝,安好。

    可能会遇到的问题:

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

所有视频需要登录后,才能观看

请先登录您的帐号,即可完整播放,如果您尚未注册帐号,请先点击注册。