☰

Excel 排序命令完全没有按预期工作

tag-icon • tag-icon microsoft-excel worksheet-function sorting spreadsheet
Excel 排序命令完全没有按预期工作

我在 Excel 中有一个表格（格式化为表格），它允许我进行排序，但其结果至少在两个方面是不正确的：

  1. 我按 AZ 或从高到低排序的数值列返回的顺序不是按顺序排列的（而且我似乎无法使任何模式符合它实际所做的操作？！？

    “排序”列图像

  2. 它杂乱无章地“打乱”了其他列的数据。（就像您在使用 NULLS 或未选择整个数据范围时所期望的那样。）

  • 我确实已将其格式化为表格。
  • 数据范围内没有任何 NULL/空白。
  • 我尝试过在排序之前选择整个表格，也尝试过不选择整个表格。
  • 我试图使所有公式都是相对的（或者使用 $（如 $A$1），或者引用表格列（如 1-[@[ColumnHeader]]）。
  • 我确实有一些相当复杂的公式，但这是一个小表格（27 行 11 列），它们都有效直到我试着种类。

根据评论，尝试分享整个表格以及公式栏中公式之一的示例。
是的，“% Model”列中有一个公式，我正尝试根据该公式进行排序，但我尝试根据值进行排序...这不可能吗？ 整个表格和一个公式

添加公式文本的屏幕录制。 在此处输入图片描述在此处输入图片描述在此处输入图片描述在此处输入图片描述在此处输入图片描述

答案1

在您展示的设置中，名称列（X 列）填充了使用普通引用引用单元格的公式，而您似乎在其他公式中“虔诚地”使用结构化引用（如果可用）。在图中所示的几个公式中，N41 和 N43 占据了显著位置。

上述内容意味着这些单元格位于表格之外。另一个指标是名称列本身的存在。如果 A 列是表格的一部分，那么整个列对于 Excel 工作来说就不是必要的，尽管人们肯定希望在跨越多个显示器的空间的数据集中定期重复名称，并且需要始终看到名称。还有其他方法可以做到这一点，但这也是其中之一。但是，考虑到对 N41 和 N43 的引用，此列的存在意味着您的表格从 X 列开始。

这有什么关系？您选择的排序列 AB 有一个公式，该公式从每行的实际值列中提取一个值。反过来，它又有公式根据名称列进行查找，该查找可能会返回一些数值。

（顺便说一句，在该公式中，NOT FOUNT用于在未找到名称时返回的值。但是，如果两端没有双引号，它将被解释为命名范围，但由于单词之间有空格，它不可能是命名范围。除非我遗漏了什么，否则需要修复，而它没有成为问题的唯一原因是，就像其他公式中所有那些“NotTru”可能性的相关问题一样，数据根本没有导致公式的这一部分被使用，因此从未给事情带来麻烦。顺便说一句，“NotTru”问题是，在公式中，显然必须有数字输入，它是文本，而不是数值。因此，如果这是它所IF()在的结果，那么公式将除以...文本...并会产生错误。）

然后，该数值将用于% of Model列的公式中。所以一切都取决于名称列。由于这些不是结构化引用（可能是因为 A 列不是此表的一部分），Excel 将进行排序，在几微秒或几毫秒内，该列中的项目将随其行一起移动，完全符合预期，然后在您看到之前，Excel 会调整非结构化引用公式（=A7等等），以保持其原始顺序，这会产生连锁反应，使表恢复到排序前的外观。

修复这个问题将会是一个问题。

一种方法是反向查找，有点像。将真实姓名数据放在姓名列中，在 A 列中输入公式以获取该行的名称。当然，如果表中没有镜像其他数据，它将不会与表一起排序，所以……问题。

另一个办法是进行改进。将所有数据放在表格中，而不是放在表格左侧。带有操作内容的列（如单元格 N41 和 N43）将保留在表格之外。完全可行，但如果有其他用户不喜欢这个...问题，则可能会在操作中造成一定程度的麻烦。

后者或许确实是唯一的出路。

从 Excel 的角度来看，没有理由不能将其放入表格中。一旦进入表格，表格就不需要从外部查找数据，因此没有公式会干扰排序。操作单元格（如 N41 和 N43）不属于任何特定记录，并且位于表格之外。然后就可以自由地进行排序等等。

答案2

您在此处获得的结果相当于对公式进行排序，而无需 Excel 移动基础数据。

想象一下你有这样的情况：

     A       B
1    Data    Calc    (formula from column B)
2    1       2       =A2*2
3    5       10      =A3*2
4    8       16      =A4*2
5    0       0       =A5*2

并且你对 Excel 的 B 列进行了排序列——仅有的B 列。它会根据单元格中的值上下移动这些公式，但这些是相对引用——Excel 在上下移动它们时会修改它们。

例如，等于 16 的 B4 应该位于底部。Excel 将获取 B4 中的公式（'=A4*2'），并执行相当于将其复制并粘贴到 B5 中的一个单元格的操作。您知道 Excel 会如何处理这样的复制粘贴；当它将公式粘贴到一个单元格时，它会将其更新为 '=A5*2'。这意味着即使后排序后，B5 仍将求值为零。基本上，排序不会执行任何操作。

解决此问题的方法是将 A 列包含在相同的排序中；您可以对 B 列进行排序，只要实际移动了基础数据，您就会得到想要的结果。将 B4 和 A4 复制并粘贴到 B5 和 A5 会将 8 向下移动到 A5，而 B5 仍将以公式“=A5*2”结尾，这正是我们想要的，因为现在是 16。

您得到的相当于这个简单得多的例子——实际上，您所做的相当于只是移动公式而不移动底层数据。您需要以某种方式解决这个问题。

相关内容