Как временно скопировать и вставить абсолютные ссылки на ячейки как относительные ссылки в Excel?

Question

В Excel (2007), когда ячейки, которые содержат абсолютные ссылки (например, $A$3), копируются, абсолютная ссылка остается той же. Это по замыслу и причина использования абсолютных ссылок.

Проблема - Тем не менее, иногда я хочу скопировать блок ячеек (которые содержат абсолютные и, вероятно, также относительные ссылки) и вставить их с абсолютными ссылками, смещенными правильно для нового блока. То есть я хочу, чтобы при копировании абсолютные ссылки вели себя как относительные ссылки, но при этом оставались абсолютными ссылками в конечном результате копирования.

Пример. На снимке экрана с примером я хочу скопировать блок A2:B3 вниз. При копировании я в основном хочу изменить формулу в B3 (=$A$3) так, чтобы она ссылалась на ячейку слева от нее, например, становясь =$A$11 при копировании в B11, как в нижней части скриншот

Обходной путь - я нашел обходной путь для этого:

1. создание копии всего рабочего листа (вкладка рабочего листа ctrl-drag в новое место),
2. затем вырезать (Ctrl-X) соответствующий блок ячеек из новой таблицы
3. вставка (Ctrl-V) в исходный лист.
4. наконец, удаляем новый временный лист (щелкните правой кнопкой мыши вкладку листа и удалите).

Вопрос - Но это слишком много действий на мой вкус. Есть ли более простой способ (возможно, какая-то скрытая опция «Специальная вставка»)?

Answer 1

Вы всегда можете попробовать написать макрос для этого. В Excel есть действительно хороший инструмент для записи макросов, который вы также можете использовать, а затем просто запускать его по мере необходимости (при условии, что вы сначала внесете некоторые изменения в программу)

dim firstLetter as String 
dim secondLetter as String 
dim firstNumber as integer 
dim secondNumber as integer 
dim firstReference as string 
dim secondReference as string 
dim contents as string 
firstLetter = inputbox("Where's the first column? (it's letter)") 
firstNumber = inputbox("And what's the first row? (just the number)") 
secondLetter = inputbox("What column is this going to be moved to? (the letter only.)") 
secondNumber = inputbox("And what row? (the number.)") 
contents = range(firstletter + cstr(firstnumber)).formula 
range(secondletter + cstr(secondnumber)).formula = contents 

Answer 2

Работа, которую я использовал, заключается в следующем:

• создайте блок ячеек, который вы хотите вставить, который включает в себя все ваши абсолютные ссылки (в вашем примере блок помечен как 'Original Block')

• затем создайте рабочий лист, который вы хотели бы видеть в конце, скопировав и вставив любой стиль, который вам нравится. Таким образом, чтобы продолжить ваш пример, вы должны использовать «Оригинальный блок» и скопировать его, скажем, в 10 раз ниже того, что будет окончательным макетом рабочего листа. (Вы заметите, что у вас все еще есть та же проблема с абсолютной ячейкой, ссылающейся на ячейку (и) из исходного блока.)

• Возьмите и выделите весь лист и CUT и скопируйте на новый лист

Затем вы заметите, что все ячейки были связаны с их соответствующими абсолютами, которые вы желали на своем новом и улучшенном листе.

Удачи :)

Answer 3

Я нашел workaroud для дублирования.

Проблема: Дублируйте выделение из начальной ячейки B2 и поместите его в начальную ячейку P2.

1. Создайте новый лист.
2. С оригинального листа скопируйте все, что вы хотите скопировать, и вставьте его на новый лист в той же начальной ячейке (если выбранный прямоугольник начинается с B2, вставьте его на новый лист B2)
3. Переместите этот выбор на новом листе справа в новую начальную ячейку (переместите его в начальную ячейку P2).
4. Скопировать выбор.
5. Вставьте выделение на исходный лист справа в новую начальную ячейку (P2).

Answer 4

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

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

1. Сделайте новый лист рядом с оригинальным листом. Это будет лист с конечным продуктом.

2. Перейти к исходному листу. Выделите столбцы с формулами. Копировать.

3. Перейдите на новый лист и выделите те же столбцы. Вставить.

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

5. Выделите новые столбцы. Копировать.

6. Перейти на новый лист. Выделите точно такие же столбцы на новом листе. Вставить.

Answer 5

Если я попытаюсь выполнить шаги по вырезанию и вставке из нового листа, я обнаружу, что все ссылки в формулах остаются неизменными при копировании в новое местоположение, включая относительные ссылки. Фактически в Excel 2010 я обнаружил, что после вырезания и вставки формул первая строка и столбец содержат ссылки на старые листы, но другие строки и столбцы ссылаются на новый лист, который выглядит как ошибка.

Если вы хотите скопировать блок формул, сохраняя все ссылки одинаковыми, вы можете нажать Ctrl+`(обратная кавычка), чтобы отобразить формулы, а затем скопировать и вставить, щелкнув значок на панели задач буфера обмена (активировать с помощью маленькой стрелки в разделе буфера обмена Главная вкладка). Если это не то, чего вы пытаетесь достичь, может помочь простой пример.

Answer 6

Следующее будет работать с меньшей сложностью, чем написание собственного макроса, и достигнет конечного результата.

Да, я знаю, что не использую ссылку на ячейку Absolute, но, как показано в примере с OP, она вам не нужна.

Выберите диапазон, который вы хотите скопировать

Затем из выпадающего меню «Вставить» выберите параметр «Формулы», как показано здесь.

Это было проверено мной и работает в Excel 2007 и 2010. Наслаждаться :)

Answer 7

Я пытался этот макрос (хранится в Personal.xlsb и привязан к сочетанию клавиш), чтобы преобразовать ссылки в абсолютные перед копированием.

Sub ToAbsolute()
 Dim c As Variant
    Application.ScreenUpdating = False
    For Each c In Selection
        If (Not IsEmpty(c.Value)) Then
            c.Value = Application.ConvertFormula(c.Formula, xlA1, , xlAbsolute)
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

Sub ToRelative()
 Dim c As Variant
    Application.ScreenUpdating = False
    For Each c In Selection
        If (Not IsEmpty(c.Value)) Then
            c.Value = Application.ConvertFormula(c.Formula, xlA1, , xlRelative, c)
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

Answer 8

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

1. Переведите Excel в режим просмотра формул. Самый простой способ сделать это - нажать Ctrl+`(этот символ является« обратным апострофом ») и обычно находится на той же клавише, что и ~ (тильда).
2. Выберите диапазон для копирования.
3. Нажмите Ctrl+C
4. Запустите Windows Notepad
5. Нажмите Ctrl+V, чтобы вставить скопированные данные в Блокнот
6. В Блокноте нажмите Ctrl+A, а затем Ctrl+C, чтобы скопировать текст (В некоторых случаях я обнаружил, что мне нужно вернуться в Excel и очистить существующий выбор, прежде чем выполнять Ctrl+C в Блокноте).
7. Активируйте Excel и активируйте верхнюю левую ячейку, куда вы хотите вставить формулы. И убедитесь, что лист, на который вы копируете, находится в режиме просмотра формул.
8. Нажмите Ctrl+V, чтобы вставить.
9. Нажмите Ctrl+`, чтобы выйти из режима просмотра формул.

Answer 9

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

Итак, имея в виду, скопировать блок ячеек, позволяя обновлять абсолютные ссылки:

1. Выделите диапазон ячеек, которые вы хотите скопировать
2. «Заменить все» $ буквой, которая все еще делает его действительной ссылкой.

Из-за общего количества столбцов XFD # является последней действительной ссылкой на ячейку. Убедитесь, что новые сгенерированные ссылки не перекрывают фактическую ссылку в диапазоне ячеек, иначе эта ссылка будет нарушена на последнем шаге. Выбор буквы из столбца, на который вы никогда не ссылаетесь, в своем блоке ячеек для копирования, гарантирует, что вы не нарушите никаких формул. Например, если вы замените $ на 'h', $ b $ 42 станет hbh42, который по-прежнему является действительной ссылкой, которая теперь может быть скопирована и будет обновляться автоматически!

3. Наконец, преобразуйте временный символ ('h' в приведенном выше примере) обратно в $, и все готово!

Вы можете попробовать заменить $ на 2 буквы в некоторых случаях, но это имеет ряд собственных проблем, поэтому было бы проще найти замену на 1 букву, которая не будет перекрывать фактическую ссылку.