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

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

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

  • Как предотвратить захват / захват нажатия клавиш веб-сайтами в Firefox?
  • Firefox: сочетание клавиш для «Выделить все» в панели поиска
  • Можно ли визуально отображать нажатия клавиш и мыши?
  • Самый быстрый способ открыть cmd в текущей папке по ярлыку (windows 8/10)?
  • Google Chrome (Mac) установил фокус клавиатуры с адресной строки назад на страницу
  • Как установить пользовательские привязки клавиш и ключевые макросы в Mac OS X?
  • Пример. В скриншоте примера я хочу скопировать блок A2: B3 вниз. При копировании я в основном хочу, чтобы формула в B3 ( =$A$3 ) изменилась так, чтобы она ссылалась на ячейку слева от нее, например, став =$A$11 при копировании на B11, как в нижней части Снимок экрана.

    Пример в Excel

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

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

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

  • Как правильно направить Alt на клавишу контекстного меню
  • Переместить каждые 7 столбцов в новую строку в Excel
  • Формула Excel 2010 для удаления повторяющегося контента на основе значения
  • VBA делает ячейки обязательными на основе предыдущей записи ячейки
  • Почему Excel усекает мои 16-значные цифры?
  • Разделить ячейку Excel последним разделителем
  • 6 Solutions collect form web for “Как временно копировать абсолютные ссылки на ячейки как относительные ссылки в Excel?”

    Вы всегда можете попробовать написать макрос для этого. 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 

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

    • Создайте блок ячеек, который вы хотите вставить, который включает в себя все ваши абсолютные реферансы (в вашем примере блок заблокирован «Исходный блок»)

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

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

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

    Удачи 🙂

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

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

    Я пробовал этот макрос (хранился в 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 

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

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

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

    Введите описание изображения здесь

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

    Введите описание изображения здесь

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

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

    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 + `, чтобы переключиться из режима просмотра формул.
    Давайте будем гением компьютера.