专栏首页完美Excel问与答83: 如何从一行含有空值的区域中获取第n个数值?

问与答83: 如何从一行含有空值的区域中获取第n个数值?

Q:在如下图1所示,在单元格区域G3:L3中有一组分数,但是其间存在空单元格。现在我想在单元格B3至F3中使用公式来获取分数,其中单元格B3中是G3:L3中的第1个分数值,即G3中的值45;C3中是第2个分数,即H3中的值44,依此类推。如何编写这个公式?

图1

(注:这也是在chandoo.org论坛上看到的一个案例,整理在此与大家分享。)

A:使用INDEX/SMALL/IF函数组合来解决。

在单元格B3中输入下面的数组公式:

=INDEX($A$3:$L$3,SMALL(IF($G3:$L3<>"",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1)))

向右拉至单元格F3。(注意,输入完后要按Ctrl+Shift+Enter组合键)

先看看公式中的:

IF($G3:$L3<>"",COLUMN($G3:$L3))

得到数组:

{7,8,9,FALSE,11,12}

公式中的:

COLUMN()-COLUMN($A$1)

等于2-1,得到:

1

将上述两个中间结果代入SMALL函数:

SMALL(IF($G3:$L3<>"",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1))

即:

SMALL({7,8,9,FALSE,11,12},1)

得到:

7

代入INDEX函数,得到:

=INDEX($A$3:$L$3,7)

对于INDEX函数来说,如果省略其参数column_num,则直接取参数array中的第row_num个元素,即G3中的值,结果为:

45

当公式向右拖时,COLUMN()-COLUMN($A$1)的值递增,这样会依次取数组{7,8,9,FALSE,11,12}中第2、3、4、5小的值,传递给INDEX函数后分别取单元格H3、I3、K3、L3中的值。

也可以省略INDEX函数的参数row_num,此时的公式为:

=INDEX($A$3:$L$3,,SMALL(IF($G3:$L3<>"",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1)))

效果相同。

本文分享自微信公众号 - 完美Excel(excelperfect),作者:fanjy

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-04-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Excel小技巧56: 数据有效性的妙用之提示用户输入信息

    通常,我们使用Excel中的“数据有效性”功能(在2013版及之后称为“数据验证”)是方便限制用户输入,以便减少工作表中的随意性,避免输入无效数据,特别是对于某...

    fanjy
  • Excel公式练习32: 将包含空单元格的多行多列单元格区域转换成单独的列并去掉空单元格

    本次的练习是:如下图1所示,单元格区域A1:D6中是一系列数据,其中包含空单元格,现在要将它们放置到一列中,并删除空单元格,如图中所示的单元格区域G1:G13,...

    fanjy
  • Excel公式技巧03: INDEX函数,给公式提供数组

    INDEX函数虽然可以生成数组,但如果不用数组公式,似乎只能返回其生成的数组中的第一个元素。然而,可以使用INDEX函数来给公式提供数组。例如:

    fanjy
  • 优化存储性能?你需要关注这些Linux I/O调度程序选项

    要优化Linux性能,IT团队应该检查当前正在使用的I/O调度程序,并评估诸如deadline和完全公平队列(Completely Fair Queuing)这...

    小小科
  • 远程管理openresty的reload

    默认情况下,nginx/openresty会启动一个root权限运行的master进程,之后再用指定的普通用户权限启动对应的worker,如果需要对整个open...

    用户1260683
  • 持续集成-Jenkins安装部署

    说明:在网上找了很多文章去解决该离线问题,但是最后都没有解决。所以只能进入页面后再手动安装常用插件。

    踏歌行
  • 自定义圆环进度条

    六月的雨
  • ANNOVAR 软件用法还可以更复杂

    这次耗费15个小时系统性的回顾了该软件,希望可以做到教学上的最佳教程。虽然其它杂七杂八中文教程没有看的必要性,但是其英文文档是需要反复读的。

    生信技能树
  • Linux 常用命令(四)

    # Default runlevel. The runlevels used are:

    小徐
  • 【小家java】java8新特性之---lambda表达式的的原理

    本文着眼于lambda表达式的原理部分,至于基本使用层面,本章不做讨论,因为使用起来还是蛮简单的。

    YourBatman

扫码关注云+社区

领取腾讯云代金券