首页 文章

Excel：引用非零单元格

提问于
浏览 次
0

我正在尝试将50行x 8列单元格（定义为“allhazards”）列入一列

但是，myhazards中的每个单元格都引用其他工作表，并包含0，其中没有要引用的文本 .

当我使用以下公式在单个列中的'allhazards'中列出数据时：

=INDEX(allhazards,1+INT((ROW($A1)-1)/COLUMNS(allhazards)),MOD(ROW($A1)-1+COLUMNS(allhazards),COLUMNS(allhazards))+1)

（然后向下拖动列以从'allhazards'获取所有单元格）

我该如何实现这个：

if cell in 'allhazards' is 0, do not reference this cell, move to next row ...then reference next row's columns until cell is 0, then move to next row ...keep doing this until there are no rows left to be referenced

例如 . 如果'allhazards'包含这些单元格（例如，2行×8列）：

hello how are 0 0 0 0 0
good  0   0   0 0 0 0 0

拖动公式时应该产生这个：

hello
how
are
good

但不是这个：

hello
how
are
0
0
0
0
0
good
0
0
0
0
0
0
0
excel vba excel-vba indexing excel-formula

2 回答

  • 1

    我根据你的情况创建了一个UDF . 请将以下过程放在标准代码模块中 .

    Public Function MATRIX2VECTOR(r As Range)
    Dim i&, j&, k&, v, m, o
    v = r
    ReDim o(1 To Application.Caller.Rows.Count, 1 To 1)
    For i = 1 To UBound(v, 1)
        For j = 1 To UBound(v, 2)
            m = v(i, j)
            If Len(m) Then
                If m <> 0 Then
                    k = k + 1
                    o(k, 1) = v(i, j)
                End If
            End If
        Next
    Next
    For k = k + 1 To UBound(o): o(k, 1) = "": Next
    MATRIX2VECTOR = o
End Function

    现在，您可以像工作表中的任何内置函数一样在公式中调用它 .

    1

    选择足够高的垂直范围的单元格以容纳转置的数据 .

    2

    单击Excel顶部的“公式栏” .

    3

    输入以下公式：

    =MATRIX2VECTOR(allhazards)

    4

    这是一个数组公式，必须使用Ctrl Shift Enter确认 .

    回复于
  • 1

    如果您对非VBA解决方案感兴趣：

    =IF(ROWS($1:1)>COUNTIF(allhazards,"<>0"),"",INDIRECT(TEXT(AGGREGATE(15,6,(10^5*ROW(allhazards)+COLUMN(allhazards))/(allhazards<>0),ROWS($1:1)),"R0C00000"),0))

    根据需要复制 .

    如果使用单个辅助单元格来存储allhazards中的非零条目数，并将 ROW / COLUMN 部分存储为定义名称，则效率会更高 . 例如，如果你把：

    =COUNTIF(allhazards,"<>0")

    在例如J1，并在Name Manager中定义Arry1为：

    =10^5*ROW(allhazards)+COLUMN(allhazards)

    然后主要公式变成：

    =IF(ROWS($1:1)>$J$1,"",INDIRECT(TEXT(AGGREGATE(15,6,Arry1/(allhazards<>0),ROWS($1:1)),"R0C00000"),0))

    如果您的数据与结果相同，则只需包含包含数据的工作表名称，即：

    =IF(ROWS($1:1)>$J$1,"",INDIRECT("'YourSheetName'!"&TEXT(AGGREGATE(15,6,Arry1/(allhazards<>0),ROWS($1:1)),"R0C00000"),0))

    问候

    回复于

相关问题