时间戳

首页 > 解决方案 > 获取值大于 0 的所有列的值

excel-formula - 获取值大于 0 的所有列的值

问题描述

我正在使用带有如下水平表的 Libre Calc

一个 b C d e F G H 我 j
0.1 0.7 2.3 5

我想列出大于 0 的值，如下所示

姓名 价值
b 0.1
e 0.7
F 2.3
G 5

我尝试使用 INDEX 和 MATCH 但没有成功如何使用 Index 查找所有大于的值

=INDEX($R$4:$R$13,MATCH(1,($S$4:$S$13>0)*(COUNTIF($U$3:U3,$R$4:$R$13)=0),0) )

我还尝试创建一个单独的表转置，然后以有限的成功运行上述命令。

标签： excel-formulalibreoffice-calc

解决方案

该任务可以通过几种不同的方式完成。

第一个选项（您尝试过）是一个冗长而晦涩的公式。更准确地说，两个公式 - 第一个将选择应显示的列的名称，第二个将使用所选名称将必要的值拉入结果表（这可以是普通的 HLOOKUP()）

（顺便说一句，您尝试使用的公式在道德上已经过时了 - 现在 Excel 有一个FILTER()函数，它与 TRASPOSE() 函数相结合，将更容易获得所需的结果。让我们希望 Calc 会很快还会获得 FILTER()、UNIQUE() 等函数

另一种方法是手动复制原始范围，在启用转置和链接选项的情况下使用选择性粘贴 (Ctrl+Shift+V) 将其粘贴到新位置，然后使用通常的标准过滤器过滤生成的辅助范围，复制过滤结果到指定位置。

我更喜欢第三种方式——UDF，用户自定义函数。任务很简单，数据通常从一个地方移动到另一个地方。因此，算法也很简单，宏代码也很简单：

Function TransposeAndFilter(aData As Variant) As Variant
Dim aResult As Variant 
Dim lB1 As Long, uB1 As Long 
Dim lB2 As Long, uB2 As Long 
Dim i As Long, j As Long, nextRow As Long 
    lB1 = LBound(aData, 1) : UB1 = UBound(aData, 1)
    lB2 = LBound(aData, 2) : UB2 = UBound(aData, 2)
    ReDim aResult(lB2 To uB2, lB1 To uB1)
    nextRow = lB2 - 1
    For i = lB2 To uB2
        If aData(uB1,i) > 0 Then 
            nextRow = nextRow + 1
            For j = lB1 To uB1
                    aResult(nextRow, j) = aData( j, i)
            Next j
        EndIf 
    Next i
    TransposeAndFilter = aResult
End Function

在单元格中写入公式，如 as=TRANSPOSEANDFILTER(A1:I2)并按Ctrl+Shift+Enter

推荐阅读