☰

如何在 Excel 中获取行最右边的值？

tag-icon • tag-icon microsoft-excel
如何在 Excel 中获取行最右边的值？

我需要显示数组中每行最右侧非空白单元格的值。如何在 Excel 中实现此操作？

在此示例表中，[当前] 列具有所需的结果：

+---------+----------+---------+----------+
|  2016   |   2017   |  2018   | Current  |
+---------+----------+---------+----------+
|     700 |          |   200   |     200  |
|         |          |         |          |
|         |     450  |         |     450  |
|         |          |  2,700  |   2,700  |
|         |          |         |          |
|  42,350 |  71,500  |         |  71,500  |
|  2,660  |          |         |   2,660  |
|         |   1,100  |         |   1,100  |
|         |          |         |          |
|    470  |          |         |     470  |
+---------+----------+---------+----------+

主题的变化将是最左边、最上面、最下面的值；或大于n等。如果版本相关，请使用 Office 2016 中的桌面 Excel。

答案1

在此处输入图片描述

  • 输入此公式E2并填写。

=LOOKUP(2,1/(A2:C2<>""),A2:C2)

怎么运行的：

  • 公式认识到查找值2 故意大于查找向量中出现的任何值。
  • 表达式返回和值A2:C2<>""的数组 。TrueFalse
  • 1然后除以此数组并创建一个由 1 或除以零错误（#DIV/0！）组成的新数组：{1,0,1，...}。
  • 该数组是查找向量。
  • 当公式找不到查找值时，则
    Lookup匹配下一个最小的值。
  • 在这种情况下，查找值为2，但查找数组中的最大值为，因此查找将与数组中的1最后一个值匹配。1
  • LOOKUP 返回结果向量中对应的值，即同一位置的值。

:编辑:

  • 对于 Google Sheet，使用的公式如下：

    =(IFERROR(LOOKUP( 2, 1 / ( A2:C2 <> "" ), A2:C2 ),""))

  • 完成它Ctrl+Shift+Enter，公式如下，

    =ArrayFormula(IFERROR(LOOKUP( 2, 1 / ( A2:C2 <> "" ), A2:C2 ),""))

答案2

虽然这个问题已经有多个解决方案，但这是我首选的解决方案，对我来说这是最接近自然思维的：

=INDEX(A2:C2,MAX(IF(A2:C2="","",COLUMN(A2:C2))))- 这是一个数组公式，因此输入后按CTRL++ 。SHIFTENTER

在此处输入图片描述

怎么运行的：

  • IF(A2:C2="","",COLUMN(A2:C2))- 对于行中的每个单元格，如果单元格为空则返回空字符串，否则返回列号
  • MAX( ... )- 选择返回的最高列号
  • =INDEX(A2:C2, ... )- 根据最高列号从行中选择单元格

警告：仅当您的范围从第一列开始时它才能正常工作，否则需要补偿偏移，例如从 C 列开始的范围：
=INDEX(C2:X2,MAX(IF(C2:X2="","",COLUMN(C2:X2)))-2)

答案3

假设您的表格布局在 C2:F12 中，标题行是第 2 行，摘要列是 F。将以下公式放在 F3 中并向下复制。

=IFERROR(INDEX(3:3,AGGREGATE(14,6,column($C3:$E3)/($C3:$E3<>""),1)),"")

波克

笔记：

  • AGGREGATE 使用公式选择 14 和 15 执行数组操作。因此，不要在 AGGREGATE 函数中使用完整的列/行引用，因为您可能会因执行的计算次数过多而导致系统陷入瘫痪或崩溃。在数组类型函数之外使用完整的列引用是可以的。请注意，INDEX 使用的是 3:3。

  • 插入新列时，如果选择了 F 列并执行插入，则需要更新 F 中的公式，以便 C3:F3 成为新范围。如果选择了 E 列并插入新列，范围将自动更新，但现在您的数据在错误的列中。如果您将 F 列留空，将公式放在 G 列中，并使用 C3:F3 作为 AGGREGATE 中的范围，那么将来您可以选择 F 列进行插入，您的公式将更新，您可以在 F 中输入新数据。明年您将在右侧有一个空白列可供选择，以重复该过程。

答案4

另一种方法，不太优雅，更粗暴，但很容易理解，那就是使用 TEXTJOIN()，现在我们有了它。

使用 A2:C2 作为第一行，将以下内容放入 D2，然后复制并粘贴。或者填充，或者...你明白了：

为了文本连接字符串下面，使用 TEXTJOIN() 函数连接要检查的整个单元格范围。使用“TRUE”省略空格以缩短字符串，对于分隔符，使用实际上永远不会出现在数据中的字符。我在下面使用“Ŧ”（对于字符，用“Ų”替换最后一个字符）。像在 TEXTJOIN() 及其相关函数中经常使用的那样使用逗号可能会导致问题。

=RIGHT( Textjoin string,
LEN( Textjoin string ) -
FIND( "Ų", SUBSTITUTE( Textjoin string, "Ŧ", "Ų",
LEN( Textjoin string **with** delimiter ) - LEN( Textjoin string **without** delimiter )
)))

并且更容易理解。SUBSTITUTE() 可以从实例 ＃这将让你找到你的分隔符的最后一次使用带分隔符的 Textjoin 字符串。在最后一行中，您找到带分隔符和不带分隔符的 Textjoin 字符串的 LEN()，并通过减法找到差值。这就是分隔符的数量，因此实例 ＃你需要。

在倒数第二行中，用不同的字符替换该实例，然后使用 FIND() 获取其在字符串中的位置。

第二行从字符串的整体 LEN() 中减去该位置，以找出后面有多少个字符。这将告诉您需要从所创建的字符串的右侧删除多少个字符。

第一行就是这么做的，它只保留范围内最后一个单元格的内容。

Excel 使用的字符串长度因函数而异，例如，有些在 6-7,000 范围内，有些则更接近 32,000。考虑到这一点（这就是指定“TRUE”的原因），可以创建一个巨大的范围，而不是 A2:C2。

请注意，您处理的是连接的字符串，而不是单元格，因此：

  1. 您永远不必查找手机地址等。
  2. 您实际上可以在由连接的“子”范围组成的范围以及由真正分开的单元格组成的范围上使用它。不连续的范围是您的朋友和盟友。

由于 Excel 在公式中评估的片段中存在数据的方式，将数据块拆分成命名范围可能会导致问题，也可能不会导致问题，因为 Excel 评估公式所创建和使用的中间结果可能与命名范围呈现的最终结果不同，有时无法在片段上使用命名范围来布局公式的逻辑以方便将来使用。但上述内容并未出现该问题，因此您可以为 TEXTJOIN 创建命名范围，并本地输入其余内容，以便任何单击单元格的人都可以看到逻辑。或者将片段拆分成逻辑内容，如“InstanceNumber”（命名范围），这样读取起来会更加容易。创建它，然后将其全部转储到命名范围内。或者根本不要使用命名范围。

正如我所说，不够优雅。比某些解决方案要长，但并不像某些东西那样“粗暴”。没有辅助列，或者人们通常不能使用的其他东西。或者不会使用。没有 {array} 公式。

（并且您可以在需要时使用不连续范围。）该方法还可以获取报告引擎 PDF 中的一堆文本和数据，然后将其提取到 Excel 中，但针对每个相关集以不同的方式分块到单元格中（因此有关 10 个客户端的信息，每个客户端设置在 10 列 13 行的块中，但一个客户端的地址在单元格 4,6 中，而另一个客户端的地址在单元格 3,8 中，但它遵循相同的流程，只是在导入时填充不同的单元格）并通过使其成为单个字符串，让您公式化地查找各个部分。通常情况下，无论如何。或者获取一个单元格块，并使用函数（而不是宏或数组或块中每个单元格的一个辅助单元格）查看其中的任何位置是否出现一些数据。

相关内容