阅读是最好的习惯

excel统计函数:应用广泛的动态统计之王OF...

excel统计函数:应用广泛的动态统计之王OF...

Ctrl Q,Excel中最强大的快捷键,没有之一

添加数据条添加数据条添加色阶为大于某一数值的数据填充颜色在这里我们设置为大于500填充红色,首先选择数据区域,然后按ctrl+q选择大于然后在跳出的对话框中,左侧输入500,右侧选择需要设置的颜色即可为前10%填充颜色5.快速插入图表利用这个功能也是可以快速插入常见的五种图表的。首先点击数据的任何一个区域,然后按下快捷键Ctrl+Q,在右下角中点击图表然后选择需要的图表即可如果没有自己需要的图表也可以点击更多图表,进入图表的插入界面6.行方向汇总数据点击数据的任何一个区域,然后按ctrl+q调出 更多

【原】Excel:表格中重复项的处理

◆  ◆  ◆标注重复项(行)◆  ◆  ◆删除重复项◆  ◆  ◆防止输入重复项“有钱的捧个钱场,好看的点下在看”

excel统计函数:应用广泛的动态统计之王OFFSET(上)

编按:哈喽,大家好!在上篇文章中,我们初步学习了OFFSET的基础和进阶用法,掌握了用OFFSET转置数据、逆向查找数据、二维表转一维表等操作,今天我们就来学习一些关于OFFSET的高阶用法:制作动态下拉菜单和动态图表。相信一定会对大家的工作和学习大有帮助。

*********

【前言】在上篇文章中,我们了解了OFFSET函数的运算原理和各个参数的作用,并且我们也通过一些OFFSET的案例,了解了它的用途。那么本篇我们继续来看看,OFFSET函数在实际工作中所能起到的强大效果吧。

一、高阶应用的思路

(动态报表模板的原型)

我们使用Excel是为了快速地统计分析数据,快速地提取出我们需要的内容。现在假设以下两个场景:

场景一:领导安排了工作,统计某季度的销售数据,我们马上行动,用函数快速的制作报表;

场景二:领导安排了工作,因为每季度都需要统计销售数据,所以我们早就提前制作了模板,至于什么时候给出报表,就随我们的便了。切记,不要让“中层领导”知道你的工作效率很高。

两个场景,你会选择哪种处理方式呢?作者希望是第二个。

思路决定了我们制表的格局,这是一个简单的案例,当数据源被修改后,相对应的季度数据也会自动做出调整。在复杂的模版中并不是所有的位置都会使用OFFSET函数,但对于动态引用数据区域的需求,用OFFSET函数来处理是绝对不会错的。

二、典型用法举例

绝技4:制作动态下拉菜单

在数据建模的过程中,我们经常会使用到下拉菜单(或者是组合框控件)。为了确保下拉内容的唯一性,我们会使用INDEX+SMALL+IF+ROW的“万金油”函数来去重提取数列中的数据。还记得我们在上篇讲到的OFFSET函数替代INDEX函数的例子吗?所以说,如果OFFSET函数可以代替INDEX函数使用的话,那么OFFSET函数同样也可以实现“万金油”的过程。下面我们就一起来看看复杂的“下拉菜单”的制作过程。

步骤一:使用OFFSET函数去重提取唯一值的 “万金油”公式

这个公式比较长,列出如下:

D2单元格函数:

=IFERROR(OFFSET($A$1,SMALL(IF(ROW($A$2:$A$27)-1=MATCH($A$2:$A$27,$A$2:$A$27,0),ROW($1:$20),9^9),ROW(D1)),),””)

万金油公式不是我们今天要讲的主题,就不展开讲了。重要就是为了让大家知道OFFSET函数也是可以达到这样去重的效果。

步骤二:在名称管理器中使用OFFSET函数,建立数据源

我们可以用Ctrl+F3组合键,打开名称管理器窗口,然后新建名称,名称设置为“区域”,引用位置为“D2:D15”,如下图所示:

然后选择G1单元格,按Alt+D+L组合键可以打开数据验证设置框,在允许中选择“序列”,在来源中输入“=区域”,如下图所示:

点击确定按钮,那么我们G1单元格的下拉菜单就建立好了。但是问题也来了,我们会发现有好多的空选项,这不是我们需要的。

有的同学会说,名称管理器中选择D2:D5就可以了。是的,但是如果我们A列的区域中出现了新的数据,那下拉菜单中的数据可就少了,所以此时我们依然使用OFFSET函数来处理这个问题。

更改名称管理器中,“区域”的引用位置:

=OFFSET(动态下拉菜单!$D$1,1,,COUNTA(动态下拉菜单!$D$2:$D$15)-COUNTBLANK(动态下拉菜单!$D$2:$D$15),1)

因为我们D列的唯一值,是用公式得到的,里面的“空单元格”不是名义上的“空”,而是由公式得到的空,所以不能直接通过COUNTIF(D2:D15,”<>”)的方式得到有值的单元格个数。因此我们先使用了COUNTBLANK函数(空值单元格计数),统计空值单元格的数量,再用COUNTA函数统计非空单元格的数量,最后二者相减就得到了有值单元格的个数。将得到的结果作为OFFSET函数的第四参数(新区域的扩展行数)使用,就实现了动态引用有效数据的效果。如下图所示:

如果A列中增加了新的区域名称,那么G1的下拉菜单也会增加新的选项,让我们来一起看一下效果,我相信这是你需要的。

绝技5:OFFSET函数在图表中的使用

上面的这张图表,相信大家都不陌生吧。参加工作的同学们都会有制作图表的经历,在上图中选择A1:B10区域,在工具栏——“插入”——柱形图,就完成了我们图例的内容。

如果我们删掉一行数据,那么柱形图中的系列图例就会少一个,可是如果增加一行数据的话,就需要更改图表数据源的范围,才能显示正确的图表。但总不能每次都更改呀,那就失去了我们使用Excel 高效快速的初衷。

此时,我们依然可以借鉴OFFSET函数来解决:

步骤一:使用OFFSET函数分别对“日期列”和“数量列”,制作自定义名称

名称管理器,我们上面有介绍,就不多说了。选中“日期列”,设置如下:

引用位置函数:

=OFFSET(图表系列!$A$1,1,0,COUNTA(图表系列!$A$2:$A$1000),1)

因为原数据中并不存在公式得到的空单元格,所以这里不需要使用COUNTBLANK函数,直接用COUNTA函数统计出非空单元格的个数,作为OFFSET函数的第四参数(新区域的行数)即可。这里的A2:A1000,表示一个绝对大的区域,保证新输入的内容在这个范围内。

选中“数量列”,同理制作出数量的自定义名称,如下:

步骤二:在图表区域使用名称

这是OFFSET动态图表的关键所在,添加名称的位置是很重要的。

在绘图区,选择任意一个柱形,在编辑栏中你可以看到图标的函数写法(是不是第一次知道图表也有函数)。我们就在这里修改引用的范围。

我们只需改动区域的部分。

图表系列!$A$2:$A$10

图表系列!$B$2:$B$10

用自定义名称替换这两个红色的部分即可,切不可以将“图表系列!$A$2:$A$10”整体替换!

替换后按回车,函数就会像上图这样显示,OFFSET.xlsx是我们的工作薄名称。效果如下:

其他的图表类型也是可以的,大家可以试着操作一下,加深印象。

****部落窝教育-OFFSET函数制作动态图表****

原创:E图表述/部落窝教育(未经同意,请勿转载)

Ctrl Q,Excel中最强大的快捷键,没有之一

添加数据条添加数据条添加色阶为大于某一数值的数据填充颜色在这里我们设置为大于500填充红色,首先选择数据区域,然后按ctrl+q选择大于然后在跳出的对话框中,左侧输入500,右侧选择需要设置的颜色即可为前10%填充颜色5.快速插入图表利用这个功能也是可以快速插入常见的五种图表的。首先点击数据的任何一个区域,然后按下快捷键Ctrl+Q,在右下角中点击图表然后选择需要的图表即可如果没有自己需要的图表也可以点击更多图表,进入图表的插入界面6.行方向汇总数据点击数据的任何一个区域,然后按ctrl+q调出 更多

【原】Excel:表格中重复项的处理

◆  ◆  ◆标注重复项(行)◆  ◆  ◆删除重复项◆  ◆  ◆防止输入重复项“有钱的捧个钱场,好看的点下在看”

赞(0) 打赏
未经允许不得转载:微精选 » excel统计函数:应用广泛的动态统计之王OF...
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏