如何在Excel中对表进行排序？ [英] How do I sort a table in Excel if it has cell references in it?

问题描述

我有一张Excel表格中的数据表，其中列出了许多其他表格中的各种不同的单元格。当我尝试排序或过滤表单时，引用在单元格移动时更改。然而，我不想手动进入每个单元格，并在任何地方插入$符号，因为一些引用是连续的，我可能想要以后自动填充。

例如，我的一个数据列使用表2，列B 23:28，表2，列C 1:15等。使用表2的列B的列的部分在排序之前都在相同的位置，以及每个其他部分。如果我插入$迹象，我将无法插入空行，并使用自动填充新数据。

总之，我想要一种方法来保持我的单元格引用静态而不使用$符号，但仅用于排序/过滤目的。我目前的解决方法是复制表的值，粘贴在一张空白的表格中，然后进行排序。我希望有更好的方法来实现。

解决方案

你有几个选项：

（1）当不使用静态引用时，排序时，单元格参考无法解决。处理这种情况的最基本的方法是在排序之前简单地复制和粘贴为值，这可以通过简单的VBA宏进行自动化。

（2）如果您在公式中使用了多个常用范围，您还可以尝试使用命名范围。您可以在公式中定义Sheet2！B23：28作为Range1，并引用Range1。在这种情况下，排序显然不会影响被指定的范围，因为它在别的地方定义。

HTH

I have a table of data in excel in sheet 1 which references various different cells in many other sheets. When I try to sort or filter the sheet, the references change when the cell moves. However, I don't want to manually go into each cell and insert $ signs everywhere, because some of the references are continuous, and I may want to auto-fill later.

For example, one of my data columns uses sheet 2, column B 23:28, sheet 2, column C 1:15, and some others. The section of the column which uses sheet 2's column B is all in the same place before sorting, as are each of the other sections. If I insert $ signs, I would no longer be able to insert empty rows and use auto-fill for new data.

In summary, I would like a way to keep my cell references static without using $ signs, but only for sorting/filtering purposes. My current workaround involves copying the values of the table, pasting in an empty sheet, and then sorting. I hope there's a better way to do this.

解决方案

You have a couple options:

(1) There's no way around cell references getting messed up when sorting when not using static references. The most basic way to deal with this is to simply copy and paste as values before sorting, which could be automated via a simple VBA macro.

(2) You could also try utilizing named ranges if you're using a number of common ranges across your formulas. You could define 'Sheet2!B23:28' as 'Range1' and reference 'Range1' within your formulas. In that case, sorting obviously wouldn't affect the range being specified since it's defined elsewhere.

HTH

这篇关于如何在Excel中对表进行排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！