首页 文章

如果Excel中有单元格引用，如何在Excel中对表进行排序？

提问于
浏览 次
24

我在表1中的Excel中有一个数据表，它引用了许多其他表中的各种不同单元格 . 当我尝试对工作表进行排序或过滤时，引用会在单元格移动时发生变化 . 但是，我不想手动进入每个单元格并在任何地方插入$符号，因为一些引用是连续的，我可能想稍后自动填充 .

例如，我的一个数据列使用表2，B列23:28，表2，C列1:15和其他一些 . 使用表2的列B的列的部分在排序之前都在同一位置，每个其他部分也是如此 . 如果我插入$符号，我将无法再插入空行并使用自动填充来获取新数据 .

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

excel

21 回答

  • 6

    我很确定这可以用 indirect() 函数解决 . 这是一个简化的电子表格：

    A         B                       C                         D    ...
       +------------------------------------------------------+- - - - - - - - -
     1 |CITY     |Q1-Q3 SALES|ANNUALIZED SALES:(Q1+Q2+Q3)*1.33|
       +======================================================+- - - - - - - - -
     2 |Tampa    | $23,453.00|                      $31,192.49|
       +------------------------------------------------------+
     3 |Chicago  | $33,251.00|                      $44,223.83|
       +------------------------------------------------------+
     4 |Portland | $14,423.00|                      $19,182.59|
       +------------------------------------------------------+
    ...|   ...   |    ...    |              ...               |

    通常，单元格C2中的公式将为 =B2*1.33 ，在您进行复杂排序之前，它可以正常工作 . 为了使其对排序很健壮，使用该单元格的行号构建您自己的单元格引用，如下所示： =indirect("B"&row())*1.33 .

    希望在你的情况下有效 . 它解决了我遇到的类似问题 .

    回复于
  • 0

    对我来说，这个工作如下 -

    我在同一张表的公式中有工作表名称引用 . 当我从公式中删除当前工作表名称并对其进行排序时，工作正常 .

    回复于
  • 0

    你有几个选择：

    （1）当不使用静态引用时，在排序时无法使单元格引用混乱 . 处理此问题的最基本方法是在排序之前简单地复制和粘贴为值，这可以通过简单的VBA宏自动执行 .

    （2）如果你在公式中使用了许多公共范围，你也可以尝试使用命名范围 . 您可以将'Sheet2！B23：28'定义为'Range1'并在公式中引用'Range1' . 在这种情况下，排序显然不会影响指定的范围，因为它在别处定义 .

    HTH

    回复于
  • 0

    即使使用绝对引用，sort也不能正确处理引用 . 相对引用指向与新行位置相同的相对偏移（这显然是错误的，因为其他行不在相同的相对位置）并且绝对引用不会更改（因为SORT省略了转换绝对引用的步骤在每次重新安排之后） . 执行此操作的唯一方法是逐个手动移动行（将引用转换为绝对） . Excel然后进行必要的引用翻译 . Excel SORT有缺陷，因为它没有这样做 .

    回复于
  • 0

    我自己遇到了麻烦，找到了最好最简单的解决方案......

    • 将要制作的数据复制到表格中然后排序 .

    • 粘贴位于电子表格的其他位置

    • 由于您的单元格引用，值会发生变化，但我们只会't want that so click the pop-up to do a '粘贴特殊的' and choose paste '值 .

    • 将您的新数据转换为表格并将您的生活排序！

    希望你发现它有效 . 问候 .

    回复于
  • 10

    我们也在努力解决同样的问题 .

    作为一种解决方法，我们使用宏来隐藏表到列表，对列表进行排序，然后将列表转换回表 .

    请在此处找到示例宏

    Sub Sort_Table()

    'change table name to "table1"
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$c$4"), , xlYes).Name = _
    "Table1"
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"


    Dim oSh As Worksheet
    Set oSh = ActiveSheet
  'remove table or list style
   oSh.ListObjects("Table1").Unlist

 'Sort List
  Range("B2").Select
  ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A4"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:C4")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
 'Change list back to table again
  ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$c$4"), , xlYes).Name = _
    "Table1"
  ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
  End Sub
    回复于
  • 0

    我有同样的问题;我有一张主表，它是我工作簿中其他工作表的信息摘要 .

    如果您只想在存储数据的工作表中进行过滤/排序，然后将其恢复到原始状态（无论您要过滤/排序），只需将第一列作为行项目编号即可 .

    在初始过滤/排序之后，您可以通过“行项目编号”来使一切恢复正常 . 注意：仅当您始终按顺序将新行添加到列表末尾时，此选项才有效 .

    回复于
  • 16

    最好的方法是将参考数据保存在工作表的一侧，将所有公式保存在工作表的另一侧 . 然后在它们之间留下一个空白列（如果你愿意，可以隐藏它）然后你将只对参考数据进行排序，使公式引用始终指向同一个位置 . 下行是Excel将在每次排序时重新计算 .

    回复于
  • 0

    一种非常简单的方法是将问题列排除在外过滤数据，但在过滤部分中添加一列，并对其进行单元格引用（只需“='单元'”） .

    这样，问题公式将保持不变，但过滤后的数据将正确引用它们，以便您可以使用这些单元格进行相应的排序

    回复于
  • 0

    试试这个方法：

    • 在要排序的单元格的左侧插入一列 .

    • 假设cols B：G将根据B进行排序

    • 复制col B.

    • 将值粘贴到col A中

    • 突出显示cols B：G

    • 用符号替换=符号

    • 排序列A：G基于A.

    • 突出显示cols B：G

    • 将＃标志替换为=符号

    • 删除col A.

    • 瞧！

    回复于
  • 3

    我需要使用引用对单元格进行排序，并且确实需要避免粘贴值以使用..“数据透视表”完成了这一操作 .

    • 使用参考准备表格 .

    • 选择表（带引用）并插入数据透视表

    • 在数据透视表中，选择所需的过滤器以使数据透视表看起来像原始表（如果需要） .

    • 根据需要进一步排序/过滤数据 .

    只需确保右键单击数据透视表，并在每次更改某些通用数据（在表中使用）时点击“刷新” .

    希望它会有所帮助 . 安德烈

    回复于
  • 0

    不确定这是否会满足所有，但我找到了一个简单的解决方法，为我解决了问题 . 将所有引用的单元格/公式移动到另一个工作表，以便您不引用工作表中具有要排序的表的任何单元格 . 如果你这样做，你可以排序！

    回复于
  • -3

    这是一个很好的答案，弄清楚排序是如何从另一个用户工作，我将添加我的笔记，以了解它是如何不完全正确和什么是正确的：

    排序后对公式的影响与复制相同 . 排序不会移动行内容，而是复制它们 . 公式可能（或可能不取决于绝对/相对参考）使用新数据，就像复制的公式一样 .

    我的观点是，如果公式可以从1行复制到另一行并且效果不会改变，则排序不会影响公式结果 . 如果公式如此复杂并且如此依赖于位置，则复制它们会更改相对内容，然后不对它们进行排序 .

    我在实践中经历过的笔记：

    上面的用户说得对，但事实上它有一些例外：包含工作表名称的列公式的部分（如sheet1！A1）被视为绝对引用（尽管复制会更改引用，如果它们是相对的），那么这部分公式将被复制而不改变相对于改变公式位置的参考这包括完全解决的当前表格单元格：sheet1！A2并将被视为绝对引用（仅用于排序）我测试了excel 2010的这个并且我不认为这个问题可以在其他版本中解决 . 解决方案是在另一个地方复制并过去special作为值，然后使用排序 .

    回复于
  • 0

    我的答案是将数据分成两个网格，它们之间有一个空白列 . 左侧的网格是您希望能够排序的数据，右侧的网格包含您要计算的公式 . 排序时，公式只是处理左侧网格中行中的数据，并且不会通过排序将行引用混淆 .

    回复于
  • 0

    最简单的方法是使用OFFSET函数 . 因此，对于原始示例，公式为：= offset（c2,0，-1）* 1.33 =“使用当前单元格（c2）作为参考点，获取同一行上的单元格内容，但左侧一列（b2）并乘以1.33“

    工作一种享受 .

    回复于
  • -3

    我使用“另存为”将工作表复制为msdos文本格式的新文件 . 这样做会删除公式，仅使用计算值替换单元格内容 . 然后以制表符分隔符打开新文件，并在定义列后进行排序 . 我需要通过相关的文本字符串（目标）对里程记录进行排序，以便我可以总结每个目的地的里程数 .

    日期开始结束距离目的地

    在第2行和连续行开始的位置是前一行结束，距离结束减去开始 .

    回复于
  • 6

    创建两个选项卡，一个使用公式，然后第二个选项卡粘贴整个表 . 排序时第二个标签应该没有问题！

    回复于
  • 0

    另一个解决方案是：（1）复制整个表 - paste as a link 在同一个电子表格中，从现在开始只在'linked table'（2）上复制带有要排序值的列并在表旁边粘贴 values only 你想排序（3）选择表并将所有=替换为例如＃，这将改变静态文本中的引用（4）按粘贴值对表进行排序（5）将所有#with替换为＃，引用又返回完成！使用excel快捷方式时速度非常快

    回复于
  • 0

    我希望在这里找到原因，但我认为答案比我们做的更简单 .

    无论他们在工作簿中引用哪个页面，单元格都应该排序 . 导致问题的原因是当前页面上的任何页面名称引用 .

    EX：我在今天的工作簿中，我在不同的页面上引用销售代表的数据 .

    如果我的标准（代表名称）在今天！C1（或$ C1，$ c $ 1）并且我尝试排序，则表单将无法识别该操作 . 但是，如果删除引用您所在页面的名称（确实是冗余引用），则应该停止此问题 .

    所以SUMIFS（'销售'！C1，'销售'！A1，今天！C1）现在将是SUMIFS（'销售'！C1，'销售'！A1，C1）

    如果有人能够启发它为什么以这种方式工作，那将是惊人的 .

    回复于
  • -1

    在要保持不变的单元格的行和/或列前放置一个美元符号 .

    为我修好了！

    回复于
  • 0

    简单的解决方案 - 从excel复制并粘贴到谷歌表 . 它将所有参考单元格复制为绝对单元格，以便您可以从头开始 . 然后排序并下载为excel网格并重新格式化以满足您的需求 .

    回复于

相关问题