Почему не копируются формулы в excel
Перейти к содержимому

Почему не копируются формулы в excel

  • автор:

Перемещение и копирование формулы

Важно знать о возможностях изменения относительной ссылки на ячейку при перемещении или копировании формулы.

  • Перемещение формулы: При перемещении формулы ссылки на ячейки в формуле не изменяются независимо от типа используемой ссылки на ячейку.
  • Копирование формулы: При копировании формулы относительные ссылки на ячейки изменятся.

Перемещение формулы

  1. Выделите ячейку с формулой, которую необходимо переместить.
  2. В группе Буфер обмена на вкладке Главная нажмите кнопку Вырезать. Формулы можно скопировать и путем перетаскивания границы выделенной ячейки в левую верхнюю ячейку области вставки. Это заменит все существующие данные.
  3. Выполните одно из следующих действий:
    • Чтобы вставить формулу и любое форматирование: в группе Буфер обмена на вкладке Главная нажмите кнопку Вставить.
    • Чтобы вставить только формулу, в группе Буфер обмена на вкладке Главная нажмите кнопку Вставить, выберите Специальная вставка, а затем — Формулы.

Копирование формулы

  1. Выделите ячейку, содержащую формулу, которую требуется скопировать.
  2. В группе Буфер обмена на вкладке Главная нажмите кнопку Копировать.
  3. Выполните одно из следующих действий:
  4. Чтобы вставить формулу и любое форматирование, в группе Буфер обмена на вкладке Главная нажмите кнопку Вставить.
  5. Чтобы вставить только формулу, в группе Буфер обмена на вкладке Главная нажмите кнопку Вставить, выберите Специальная вставка, а затем — Формулы.

Примечание: Можно вставить только результаты формулы. В группе Буфер обмена на вкладке Главная нажмите кнопку Вставить, выберите Специальная вставка, а затем — Значения.

  1. Выделите ячейку с формулой.
  2. В строке формул строка формул

Копируемая формула

Первоначальная ссылка

Новая ссылка

$A$1 (абсолютный столбец и абсолютная строка)

A$1 (относительный столбец и абсолютная строка)

$A1 (абсолютный столбец и относительная строка)

A1 (относительный столбец и относительная строка)

Примечание: Вы также можете копировать формулы в смежные ячейки с помощью маркер заполнения

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

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

Команда

  1. Выделите ячейку, содержащую формулу, которую нужно переместить.
  2. Щелкните Главная >Вырезать (или нажмите клавиши CTRL+X).

Команда

Выделите ячейку, в которой должна быть формула, и нажмите кнопку Вставить (или нажмите клавиши CTRL+V).

Щелкните правой кнопкой мыши, чтобы увидеть команды

Совет: Вы также можете щелкнуть правой кнопкой мыши ячейки, чтобы вырезать и вставить формулу.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Почему не копируются формулы в excel

Страницы: 1

не копируются формулы

Пользователь

Сообщений: 2 Регистрация: 27.09.2011

20.01.2012 14:52:43

Всем доброго времени суток. У меня проблема, формулы не реагируют на изменения. Например: если я вношу сумму в ячейку, которая задействована в формуле, то результат меняется, только когда я нажимаю на строку вводу и щелкаю на энтер. Точно также, если я протягиваю формулу, то копируется значение, а не формула, и меняется , опять же, только когда я непосредственно кликаю на формулу. Если кто нибудь с таким сталкивался, расскажите пожалуйста как с этим бороться.

Скопируйте и вставьте формулу в другую ячейку или лист в Excel для Mac

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

Ниже объясняется, как скопировать и вставить формулу.

Стрелка рядом с кнопкой вставки

  1. Выделите ячейку с формулой, которую хотите скопировать.
  2. Нажмите клавиши « + C».
  3. Щелкните ячейку, в которую нужно вставить формулу. Если ячейка находится на другом листе, перейдите на него и выберите эту ячейку.
  4. Чтобы быстро вставить формулу с ее форматированием, нажмите клавиши « + V». Кроме того, можно щелкнуть стрелку рядом с кнопкой Вставить :

  • Формулы для вставки только формулы, а не форматирования исходной ячейки.
  • Формулы & числового форматирования— нужно вставить только формулу и форматирование чисел (например, формат процента, формат валюты и т. д.).
  • Сохранение исходного форматирования— чтобы вставить формулу, форматирование чисел, шрифт, размер шрифта, границу и заливку исходной ячейки.
  • Вставка значений чтобы исключить формулу и вставить только результат.

Другие доступные вам параметры, которые могут быть полезны:

  • Без границ— чтобы вставить формулу, форматирование чисел, шрифт, размер шрифта, заливку, но не границу исходного.
  • Сохранить ширину исходного столбца— чтобы вставить формулу, форматирование чисел, шрифт, размер шрифта, заливку, границу и ширину исходного.
  • Транспонирование — Используйте этот параметр при копировании нескольких ячеек. При копировании ячеек, которые находятся рядом в строке, этот параметр вставляет их в столбец. Если ячейки находятся в столбце, они будут вставлены в ряд рядом. Этот параметр вставляет формулу, форматирование чисел, шрифт, размер шрифта, заливку, границу.

Совет: Скопировать формулы в смежные ячейки листа также можно с помощью маркера заполнения.

Проверка и исправление ссылок на ячейки в новом месте

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

Например, если скопировать формулу из двух ячеек вниз и справа от ячейки A1, используемые ссылки на ячейки будут обновлены следующим образом:

Новая ссылка

$A$1 (абсолютный столбец и абсолютная строка)

A$1 (относительный столбец и абсолютная строка)

$A1 (абсолютный столбец и относительная строка)

A1 (относительный столбец и относительная строка)

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

Formula bar

  1. Выделите ячейку с формулой.
  2. В строке формул

Перенос формулы в другое место

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

  1. Щелкните ячейку с формулой, которую хотите перенести.
  2. Нажмите клавиши « + X».
  3. Щелкните ячейку, в которую нужно вставить формулу. Если ячейка находится на другом листе, перейдите на него и выберите эту ячейку.
  4. Чтобы вставить формулу с ее форматированием, на вкладке Главная нажмите кнопку Вставить или нажмите клавишу +V. Чтобы воспользоваться другими параметрами вставки, щелкните стрелку под кнопкой Вставить. Доступно несколько вариантов, но чаще всего используются перечисленные ниже.
    • Сохранить исходное форматирование: вставка только формул, форматов чисел, атрибутов шрифта и его размера, границ и заливки исходной ячейки.
    • Значения & исходное форматирование , чтобы вставить значения и только форматирование. Формула не вставляется.

Копирование формул без сдвига ссылок

Предположим, что у нас есть вот такая несложная таблица, в которой подсчитываются суммы по каждому месяцу в двух городах, а затем итог переводится в евро по курсу из желтой ячейки J2. exact-formulas-copy1.pngПроблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать: exact-formulas-copy2.pngЗадача: скопировать диапазон с формулами так, чтобы формулы не изменились и остались теми же самыми, сохранив результаты расчета.

Способ 1. Абсолютные ссылки

Как можно заметить по предыдущей картинке, Excel сдвигает только относительные ссылки. Абсолютная (со знаками $) ссылка на желтую ячейку $J$2 не сместилась. Поэтому для точного копирования формул можно временно перевести все ссылки во всех формулах в абсолютные. Нужно будет выделить каждую формулу в строке формул и нажать клавишу F4:

exact-formulas-copy9.png

При большом количестве ячеек этот вариант, понятное дело, отпадает — слишком трудоемко.

Способ 2. Временная деактивация формул

  1. Выделяем диапазон с формулами (в нашем примере D2:D8)
  2. Жмем Ctrl+H на клавиатуре или на вкладке Главная — Найти и выделить — Заменить (Home — Find&Select — Replace)

exact-formulas-copy3.png

exact-formulas-copy4.png

Способ 3. Копирование через Блокнот

Этот способ существенно быстрее и проще.

Нажмите сочетание клавиш Ctrl+Ё или кнопку Показать формулы на вкладке Формулы (Formulas — Show formulas) , чтобы включить режим проверки формул — в ячейках вместо результатов начнут отображаться формулы, по которым они посчитаны:

exact-formulas-copy5.png

Скопируйте наш диапазон D2:D8 и вставьте его в стандартный Блокнот:

exact-formulas-copy6.png

Теперь выделите все вставленное (Ctrl+A), скопируйте в буфер еще раз (Ctrl+C) и вставьте на лист в нужное вам место:

exact-formulas-copy7.png

Осталось только отжать кнопку Показать формулы (Show Formulas) , чтобы вернуть Excel в обычный режим.

Примечание: этот способ иногда дает сбой на сложных таблицах с объединенными ячейками, но в подавляющем большинстве случаев — работает отлично.

Способ 4. Макрос

Если подобное копирование формул без сдвига ссылок вам приходится делать часто, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставьте новый модуль через меню Insert — Module и скопируйте туда текст вот такого макроса:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Выделите ячейки с формулами, которые надо скопировать.", _ "Точное копирование формул", Default:=Selection.Address, Type:=8) If copyRange Is Nothing Then Exit Sub Set pasteRange = Application.InputBox("Теперь выделите диапазон вставки." & vbCrLf & vbCrLf & _ "Диапазон должен быть равен по размеру исходному " & vbCrLf & _ "диапазону копируемых ячеек.", "Точное копирование формул", _ Default:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "Диапазоны копирования и вставки разного размера!", vbExclamation, "Ошибка копирования" Exit Sub End If If pasteRange Is Nothing Then Exit Sub Else pasteRange.Formula = copyRange.Formula End If End Sub

Для запуска макроса можно воспользоваться кнопкой Макросы на вкладке Разработчик (Developer — Macros) или сочетанием клавиш Alt+F8. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:

exact-formulas-copy8.png

Ссылки по теме

  • Удобный просмотр формул и результатов одновременно
  • Зачем нужен стиль ссылок R1C1 в формулах Excel
  • Как быстро найти все ячейки с формулами
  • Инструмент для точного копирования формул из надстройки PLEX

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *