Как отсортировать таблицу в Excel, если в ней есть ссылки на ячейки?

У меня есть таблица данных в excel в листе 1, которая ссылается на различные разные ячейки во многих других листах. Когда я пытаюсь сортировать или фильтровать лист, ссылки меняются, когда ячейка перемещается. Однако я не хочу вручную входить в каждую ячейку и вставлять знаки $везде, потому что некоторые ссылки непрерывны, и я могу захотеть автозаполнение позже.

Например, один из моих столбцов данных использует лист 2, столбец B 23:28, лист 2, столбец C 1:15 и некоторые другие. Раздел столбца, который использует столбец B 2, находится в одном и том же месте перед сортировкой, как и каждый из других разделов. Если я вставляю знаки $, я бы больше не мог вставлять пустые строки и использовать автозаполнение для новых данных.

В заключение я хотел бы, чтобы мои ссылки на ячейки были статичными без использования знаков $, но только для сортировки/фильтрации. Мое текущее обходное решение включает в себя копирование значений таблицы, вставку на пустой лист и сортировку. Надеюсь, там будет лучший способ сделать это.

Ответ 1

Я уверен, что это можно решить с помощью функции 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.

Надеюсь, что это работает в вашей ситуации. Он исправил аналогичную проблему, с которой я столкнулся.

Ответ 2

Для меня это работало, как показано ниже -

У меня были ссылки на имя листа в формуле для того же листа. Когда я удалил текущее имя листа из формулы и отсортировал его, он работал правильно.

Ответ 3

У вас есть несколько вариантов:

(1) Невозможно перепутать ссылки на ячейки при сортировке, если не использовать статические ссылки. Самый простой способ справиться с этим - просто скопировать и вставить как значения перед сортировкой, которые могут быть автоматизированы с помощью простого макроса VBA.

(2) Вы также можете попробовать использовать именованные диапазоны, если используете несколько общих диапазонов для своих формул. Вы можете определить "Sheet2! B23: 28" как "Range1" и ссылку "Range1" в ваших формулах. В этом случае сортировка, очевидно, не повлияет на диапазон, указанный, поскольку он определен в другом месте.

НТН

Ответ 4

Даже при абсолютных ссылках сортировка неправильно обрабатывает ссылки. Относительные ссылки заключаются в том же относительном смещении от новой позиции строки (что, очевидно, неверно, потому что другие строки не находятся в одном и том же относительном положении), а абсолютные ссылки не изменяются (поскольку SORT исключает шаг перевода абсолютных ссылок после каждой перегруппировки строки). Единственный способ сделать это - вручную перевести строки (с преобразованием ссылок на абсолютные) один за другим. Затем Excel выполняет необходимый перевод ссылок. Excel SORT недостаточно, поскольку он этого не делает.

Ответ 5

У меня были проблемы с этим самим и нашел ЛУЧШЕЕ и самое простое решение...

Скопируйте данные, которые вы хотите внести в таблицу, а затем выполните сортировку.
Паста находится где-то в вашей таблице.
Значения будут меняться из-за ссылок на ячейки, но мы не хотим, чтобы так щелкали всплывающие окна, чтобы сделать "вставить специальные" и выбрать только "значения".
Превратите свои новые данные в таблицу и соберите свою жизнь!

Надеюсь, вы найдете, что это работает. С наилучшими пожеланиями.

Ответ 6

Мы также боремся с той же проблемой.

В качестве обходного пути мы используем макрос для скрытой таблицы для списка, сортируем список и затем скрываем список до таблицы.

здесь, пожалуйста, найдите образец макроса

    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

Ответ 7

У меня была такая же проблема; У меня был мастер-лист, в котором была сводка информации о других листах в моей книге.

Если вы хотите просто фильтровать/сортировать на листе, где хранятся ваши данные, а затем возвращать его в исходное состояние (независимо от того, что вы фильтруете/сортируете), просто сделайте свой первый столбец номером позиции.

После вашего начального фильтра/сортировки вы можете просто прибегнуть к "Номер позиции", чтобы вернуть все обратно в нормальное состояние. ПРИМЕЧАНИЕ. Это работает только в том случае, если вы всегда добавляете новые строки в конец списка в последовательности.

Ответ 8

Это один хороший ответ, чтобы выяснить, как сортировка работает от другого пользователя, и я добавлю свои заметки, чтобы узнать, как это не совсем правильно и что правильно:

Влияние на формулу после сортировки такое же, как при копировании. Сортировка не перемещает содержимое строки, она копирует их. Формула может (или может не зависящей от абс/относительных ссылок) использовать новые данные, как это делает скопированная формула.

Моя точка зрения заключается в том, что если формулу можно скопировать из одной строки в другую и эффекты не меняются, сортировка не повлияет на результаты формулы. Если формулы настолько сложны и зависят от положения, что их копирование изменяет относительное содержимое, то не сортируйте их.

И мое замечание, которое я испытал на практике:

Вышеуказанный пользователь говорит правильно, но на самом деле имеет некоторое исключение: части формулы столбцов, содержащие имя листа (например, sheet1! A1), рассматриваются как абсолютные ссылки (несмотря на то, что копирование изменяет ссылки, если они являются относительными) так что часть формулы будет скопирована без изменения ссылок относительно изменения места формулы Это включает в себя текущие ячейки листа, адресованные полностью как: sheet1! A2 и будут рассматриваться как абсолютные ссылки (только для сортировки) Я тестировал это из excel 2010, и я не думаю, что эта проблема будет решена в других версиях. Решение состоит в том, чтобы копировать и использовать специальные значения в другом месте, а затем использовать сортировку.

Ответ 9

Лучший способ - сохранить свои справочные данные на одной стороне листа и на всех формулах на другой стороне листа. затем оставьте пустой столбец между ними (спрячьте его, если хотите), тогда вы будете сортировать только контрольные данные, сохраняя ссылки на формулу, указывающие всегда на одном месте. Недостатком является то, что Excel будет пересчитывать каждый раз, когда вы сортируете.

Ответ 10

Очень простой способ - оставить столбцы проблем из отфильтрованных данных, но добавить столбец в отфильтрованный раздел с ссылками на ячейки (просто "=" ячейка ").

Таким образом, формулы проблемы останутся нетронутыми, но отфильтрованные данные будут ссылаться на них правильно, поэтому вы можете использовать эти ячейки для сортировки соответственно

Ответ 11

Попробуйте этот метод:

Вставьте столбец слева от сортируемых ячеек.
Предположим, что cols B: G должны быть отсортированы на основе B
Копировать col B
Вставить значения в col A
Выделите cols B: G
Заменить = знаки С# знаками
Сортировка cols A: G на основе A
Выделите cols B: G
заменить # знаки с помощью знака
Удалить col A.
Voila!

Ответ 12

Мне нужно было сортировать ячейки со ссылками, и действительно нужно было избегать вставки значений для работы с ними. "Сводная таблица" сделала трюк.

Подготовьте свои таблицы со ссылками.
Выберите таблицу (со ссылками) и вставьте сводную таблицу
В сводной таблице выберите необходимые фильтры, чтобы таблица Pivot выглядела как исходная таблица (если необходимо).
Сортировать/фильтровать данные по мере необходимости.

Просто не забудьте щелкнуть правой кнопкой мыши по таблице сводных данных и нажать "обновить" каждый раз при изменении некоторых общих данных (используемых в ваших таблицах).

Надеюсь, это поможет. Андрей

Ответ 13

Не уверен, что это удовлетворит все, но я нашел простую обходную проблему, которая исправила проблему для меня. Переместите все ссылающиеся ячейки/формулы на другой лист, чтобы вы не ссылались на какие-либо ячейки на листе, у которых есть таблица для сортировки. Если вы сделаете это, вы можете разобраться!

Ответ 14

Ответ для меня заключался в том, чтобы разделить данные на две сетки с пустым столбцом между ними. Сетка слева - это данные, которые вы хотите отсортировать, а сетка справа содержит формулу, которую вы хотите вычислить. Когда вы сортируете, формула просто работает с данными в строках в левой сетке и не получает ссылки на строки, смешанные по сортировке.

Ответ 15

Самый простой способ - использовать функцию OFFSET. Итак, для исходного примера формула будет: = Смещение (c2,0, -1) * 1.33 = "используя текущую ячейку (c2) в качестве контрольной точки, получите содержимое ячейки в той же строке, но один столбец слева (b2) и умножьте ее на 1.33"

Работает с удовольствием.

Ответ 16

Я использовал "save as", чтобы скопировать лист в новый файл в виде текстового формата msdos. При этом удаляются формулы, заменяя содержимое ячейки только вычисленными значениями. Затем откройте новый файл как разделитель табуляции и сортируйте после определения столбцов. Мне нужно было сортировать вычисленные значения с помощью связанной текстовой строки (адресата) для журнала пробега, чтобы я мог суммировать пробег для каждого пункта назначения.

дата начало конец расстояние назначение

где в строке 2 и последовательных строках начинается предыдущая строка, заканчивающаяся и расстояние заканчивается минус начало.

Ответ 17

создайте две вкладки, одну с формулами, а затем вторую, где вы вставляете ссылку на всю таблицу. при сортировке второй вкладке не должно быть проблем!

Ответ 18

Другое решение состоит в том, чтобы: (1) скопировать всю таблицу - вставить как ссылку в одну и ту же электронную таблицу, отныне работать только над "связанной таблицей" (2) скопировать столбец со значениями для сортировки и вставить только следующие значения к таблице, которую вы хотите отсортировать (3) выберите таблицу и замените все = на eg, например, это изменит ссылку в статическом тексте (4) отсортируйте таблицу по вставленным значениям (5) замените все # на =, ссылки вернулись Готово! Это происходит довольно быстро при использовании ярлыков Excel

Ответ 19

Добавьте имя листа к формуле, которая делает ссылку абсолютной. Например, если ссылка на ячейку = T7, сделайте это = Sheet1! T7. Вставить ссылку сделал бы то же самое, кроме как при вставке на другой лист. Вставить ссылку не работает должным образом, если вы вставляете на тот же лист.

Ответ 20

Я надеялся найти причину здесь, но я думаю, что ответ проще, чем мы это делаем.

Ячейки должны сортироваться независимо от того, на какой странице они ссылаются в вашей книге. То, что вызывало проблему для нас, - это любые ссылки на названия страниц на текущей странице.

EX: Я нахожусь в книге "Сегодня", и я ссылаюсь на данные для торгового представителя на другой странице.

Если мои критерии (имя повтора) находятся в Today! C1 (или $C1, $c $1), и я пытаюсь сортировать, лист не распознает действие. Но если вы удалите имя, ссылающееся на страницу, на которой вы находитесь (действительно, на резервную ссылку), эта проблема должна прекратиться.

Итак, SUMIFS ('Sales'! C1, 'Sales'! A1, Today! C1) теперь будет SUMIFS ('Sales'! C1, 'Sales'! A1, C1)

Если кто-то может просветить, почему он так работает, это было бы потрясающе.

Ответ 21

Поместите знак доллара перед строкой и/или столбцом ячейки, в которой вы хотите оставаться постоянным.

Исправлено это для меня!

Ответ 22

Простое решение - скопируйте с Excel и вставьте в листы Google. Он копирует все ваши эталонные ячейки как абсолютные ячейки, чтобы вы могли начать с нуля. Затем сортируйте и загрузите обратно в качестве сетки Excel и переформатируйте в соответствии с вашими потребностями.