【Excel】如何三分钟求多组数据的众数 |
您所在的位置:网站首页 › 众数函数英语 › 【Excel】如何三分钟求多组数据的众数 |
在数据处理中,很多时候我们会遇到需要批量实现的问题:比如同时分别求出多类别数据的和、批量求出多组数据的众数、批量删除不必要的不完全重复行 这一期我们就来讲讲 批量求出多组数据的众数 「 原始数据 」 「 要求 」:分别计算出每个相同的编码对应的一组数值的众数 下图以编码Y07020015215为例 以下步骤具体讲解如何实现 批量求出多组数据的众数 Step 1:为了不破坏原始数据,一般我们都需要先复制一个「 副本 」进行操作。 右键「 原始数据 」工作表标签——移动或复制 移至最后——建立副本——重命名为「 操作 」 Step 2:提取出不重复的编码,方法有以下两种: 「 借助数据透视表 」,在「 操作 」工作表中:插入——数据透视表 将「 编码 」字段添加到「 行 」字段 即可得到不重复的编码 复制到「 操作 」工作表D列备用 「 高级筛选 」 在「 操作 」工作表中:复制A列编码到D列 单击D列任意一个有数据单元格 数据——筛选——高级筛选 选择不重复的记录 Step 3:在E2单元格中输入公式: =MODE(IF(A$2:A$100=D2,B$2:B$100,"")) 「 公式解析 」 先讲最简单的一个函数mode mode函数是返回一组数据或数据区域中的众数 其使用方法很简单类似于求和公式sum 但是我们这里要求的不只是一个编码对应数组的众数,所以我们需要嵌套一个条件函数if来判断每个编码对应的数组,再分别求该数组的众数。 if函数也算是比较容易的条件函数 if函数的三个参数也很简单 =if(A=B,C,D) 如果满足条件A=B,则返回C,否则返回D 然而为了能够一次解决问题,在if函数的应用中我们选取了数据区域:A2:A100 和 B2:B100 输入=IF(A2:A100=D2,B2:B100,""),意思是A2:A100区域中如满足=D2 返回的是与D2相同的编号所对应的所有数值(数组),而非单个数值 =MODE(IF(A$2:A$100=D2,B$2:B$100,"")),是函数mode和if的完美嵌套 意思是A2:A100中编号与D2相同时,B2:B100中所对应的数组的众数 但当我们按下回车确认,会发现得到的并非是正确的众数 这是因为这里使用的是数组公式 Step 4:输入公式后,同时按下三个键确认: 「 ctrl 」+「 shift 」+「 enter 」 「 数组公式 」 数组公式简单来讲,就是桥梁的作用 将同一行对应的数据串联起来 如上图举个简单的例子 我们发现只是运用了同一个公式 但同一行的单价和数量产生了关联 数组公式为同一行的数据搭建了桥梁 明白了原理,用同样的方法可以一次性求总销售额{=SUM(G2:G5*H2:H5)} 总销售额=G2*H2+G3*H3+G4*H4+G5*H5即先乘后加 再回到我们的案例 完成了前四步后我们还需要进行批量填充 Step 5:进行单元格锁定后,双击E2单元格进行向下填充。 这时我们发现有一些单元格出错,显示的是#N/A 原因是原始数据中编码对应的数值只有一个 无法进行mode函数的运用 当然只有一个数值时众数即为它本身 Step 6:当然为了使数据满足mode的使用条件「 2个数值以上的一组数值或数据区域 」,有一个小技巧可以实现,就是将100行原始数据再复制一次置于101-199行(去掉标题行),将公式替换为: {=MODE(IF(A$2:A$199=D2,B$2:B$199,""))} 这样问题就得到完美解决啦 因为每个数值的次数都因此翻倍了 可是如果数据源太多 复制原始数据变得很吃力怎么办呢 其实我们完全可以在最开始就计算出各编码出现的次数 通过排序将次数为1的筛选出来 对次数≥2的进行mode函数运用就可以实现啦 「 小结 」 ① 建立副本 通常需要「 备份原始数据 」以免破坏原始数据,也方便数据处理不当的后期补救; ② 函数灵活嵌套 单个函数只能解决部分问题,很多时候需要「 嵌套多个函数 」实现; ③ 辅助列 当原始数据没办法处理问题时,通常我们需要添加一些辅助列来帮助我们解决问题,比如文中经筛选后的不重复编码D列。 ④ 数组公式 数组公式在很多场合都起到特别重要的作用,建议重点学习应用。 看动图的原链接【Excel】如何三分钟求多组数据的众数 关注公众号:微学时刻(Time-Micro)后台回复「 众数 」,获取原始数据进行练习时时刻刻有Office,让每一次分享产生价值。这里有技巧、有素材、有分享、有感悟,有你有我。http://weixin.qq.com/r/Xj-g_KXE2PinreGb92pM (二维码自动识别) |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |