我正在尝试使用公式对值进行排序。一开始我信心十足，但最后却头疼不已:)

如果 A1 = 0，我希望显示同一行下一个单元格中小于或大于 0 的值，依此类推。逐行显示。排除所有 0 值，并从左侧第一个单元格对小于或大于 0 的值进行排序。

仅使用公式是否可行？我尝试过 IF、INDEX、SORT、FILTER 等不同的解决方案，但都无法达到我想要的效果。

第一张图片显示起始值：

这就是我仅使用公式试图实现的目标：

感谢所有能给我帮助的人！

谨致问候杰克


最佳答案
3

将不需要的值移至右侧

=LET(data,A2:D6,crit,0,rep,"",
    d,IF(data=crit,rep,data),
    rc,ROWS(d),
    cc,COLUMNS(d),
    rs,SEQUENCE(rc),
    s,SEQUENCE(rc,cc)+(rs-1)*cc,
    ss,s+(d=rep)*cc,
    r,WRAPROWS(SORTBY(TOCOL(d),TOCOL(ss)),cc),
    r)
  • 将最底部的内容替换r为任何其他变量，以查看它们包含的内容。

编辑

  • 感谢rotabor指出 是-1多余的。我只在以下公式中更改它，因为它被用来启动 ˙s˙ 序列，1如屏幕截图所示。
  • 我本可以使用+100，而公式仍然有效，因为只需要s序列中的每一行具有连续的上升值，并且每行之间的差为 8（列数的两倍）。
  • 额外的 4 个（列）数字用于通过公式对不需要的值进行索引，该公式将为产生序列的每个（不需要的值）IF向序列添加 4 。s0ss
  • 然后按“TOCOLed”序列对“TOCOLed”数据进行排序ss，并使用返回其原始形状WRAPROWS。

短的

  • 删除冗余变量并应用建议的更改（删除-1）后，您将得到以下公式：
=LET(data,A2:D6,crit,0,rep,"",
    d,IF(data=crit,rep,data),
    rc,ROWS(d),
    cc,COLUMNS(d),
    WRAPROWS(SORTBY(TOCOL(d),TOCOL(SEQUENCE(rc,cc)
       +((d=rep)+SEQUENCE(rc))*cc)),cc))

如果/序列

  • 如果您想要继续前行IF/SEQUENCE，可以使用以下公式：
=LET(data,A2:D6,crit,0,rep,"",
    d,IF(data=crit,rep,data),
    rc,ROWS(d),
    cc,COLUMNS(d),
    rs,SEQUENCE(rc),
    s,IF(rs,SEQUENCE(,cc))+2*rs*cc,
    ss,s+(d=rep)*cc,
    r,WRAPROWS(SORTBY(TOCOL(d),TOCOL(ss)),cc),
    r)
  • 删除冗余变量后，您将得到以下公式：
=LET(data,A2:D6,crit,0,rep,"",
    d,IF(data=crit,rep,data),
    rs,SEQUENCE(ROWS(d)),
    cc,COLUMNS(d),
    WRAPROWS(SORTBY(TOCOL(d),TOCOL(IF(rs,SEQUENCE(,cc))
        +(2*rs+(d=rep))*cc)),cc))

效率

  • 所有公式大约需要 2 秒才能返回最大行数。

限制

  • TOCOL它可以容纳的值限制为大约 10^20 (1048576) 个，即，如果有 4 列，它最多可以处理262145行。另一方面，如果有20列，则最多可以处理52429行。
  • 下面的截图说明了这一行为。

2

  • 1
    这是原始的方式。


    – 

  • 在最后的公式中，-1是额外的。


    – 

以下是使用单个动态数组公式实现所需输出的一种方法：

• 单元格中使用的公式F1

=LET(
     a, A1:D4, 
     IFNA(DROP(REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(x,y, 
     VSTACK(x,SORT(TOROW(INDEX(a,y,)/(INDEX(a,y,)<>0),2))))),1),""))

• 或者，如果您想为每一行填写以下内容：

=LET(a, A1:D1, SORT(TOROW(a/(a<>0),2)))

• 或者使用FILTER()函数：

=LET(a, A1:D1, SORT(FILTER(a,a<>0,"")))

或者，也可以这样：

=LET(
     a, A1:D4,
     b, SEQUENCE(ROWS(a)),
     IFNA(DROP(REDUCE(0,b,LAMBDA(x,y,
     LET(z, INDEX(a,y,), VSTACK(x,
     SORT(FILTER(z,z<>0)))))),1),""))

3

  • 1
    从逻辑上来说不是，但 LET 什么都没带来。另外，SORT 也不是预期的。


    – 


  • 是的，它确实避免了重复使用范围。所以我这样做了。SORT()用于获取已排序的范围。这是一个额外的好处。


    – 


  • 1) 在答案中的公式中，公式 #3 仅在一行中全为 0 时才通过测试。2) 正如我已经指出的那样，SORT 函数不是必需的（如插图所示）。幸运的是，它不会影响计算，因为它按行排序。


    – 


我也想为解决这个问题做出贡献。

Excel 的缺陷在于公式无法返回空白单元格。当然，如果单元格包含公式，则不能将其视为空单元格。但在某些情况下可能需要空白单元格。

DoSortByRows VBA 子程序能够解决这一需求。

Option Explicit

Sub DoSortByRows()
  Dim a, ac As Long, rc As Long, cc As Long
  ac = Selection.Areas.Count
  If ac <> 2 And ac <> 3 Then GoTo ErrorMsg
  a = SortByRows(Selection.Areas(1), Selection.Areas(2), Empty)
  If IsError(a) Then GoTo ErrorMsg
  rc = Selection.Areas(1).Rows.Count:  cc = Selection.Areas(1).Columns.Count
  If ac = 2 Then
    Selection.Areas(1) = a
  ElseIf ac = 3 Then
    Selection.Areas(3).Cells(1).Resize(rc, cc) = a
  End If
ExitSub:
  Exit Sub
ErrorMsg:
  MsgBox "Wrong arguments provided", , "DoSortByRows"
  GoTo ExitSub
End Sub

Function SortByRows(source As Variant, criteria As Variant, replacement As Variant) As Variant
  Dim src, crt, rc As Long, cc As Long, i As Long, j As Long, k As Long
  If Not IsArray(source) Or Not IsArray(criteria) Or IsArray(replacement) Then
    SortByRows = CVErr(xlErrCalc)
    Exit Function
  End If
  src = source: crt = criteria: rc = UBound(src, 1): cc = UBound(src, 2)
  If rc <> UBound(crt, 1) Or cc <> UBound(crt, 2) Then
    SortByRows = CVErr(xlErrCalc)
    Exit Function
  End If
  For i = 1 To rc
    k = 1
    For j = 1 To cc
      If Not crt(i, j) Then
        src(i, k) = src(i, j)
        k = k + 1
      End If
    Next
    For j = k To cc
      src(i, j) = replacement
    Next
  Next
  SortByRows = src
End Function

DoSortByRows 要求选择 2 或 3 个范围。第一个范围是源表，第二个范围是相同大小的条件数组，其中不需要的值会导致 TRUE，可选的第三个范围是结果的新位置，否则结果将覆盖源。

具有两个选定范围的示例。

选择第一个范围，然后按住 Ctrl 键并选择第二个范围。

F11=A11:D14=0

按 Alt+F8 并运行 DoSortByRows 宏

A11:D14最后可以清除。

具有三个选定范围的示例。

选择第一个范围，然后按住 Ctrl 键并选择第二个范围，然后仍然按住 Ctrl 键选择第三个范围。

最后，可以在工作表上使用 SortByRows 函数：

F6=SortByRows(A6:D9,A6:D9=0,"")