Как скопировать содержимое ячеек в excel с формулами

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

Проблема

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

exact-formulas-copy1.png

Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать:

exact-formulas-copy2.png

Задача: скопировать диапазон с формулами так, чтобы формулы не изменились и остались теми же самыми, сохранив результаты расчета.

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

exact-formulas-copy9.png

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

Чтобы формулы при копировании не менялись, надо (временно) сделать так, чтобы Excel перестал их рассматривать как формулы. Это можно сделать, заменив на время копирования знак "равно" (=) на любой другой символ, не встречающийся обычно в формулах, например на "решетку" (#) или на пару амперсандов (&&). Для этого:

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

exact-formulas-copy3.png

  • В появившемся диалоговом окне вводим что ищем и на что заменяем и в Параметрах (Options) не забываем уточнить Область поиска — Формулы. Жмем Заменить все (Replace all) .
  • Копируем получившийся диапазон с деактивированными формулами в нужное место:
  • 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 и скопируйте туда текст вот такого макроса:

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

    Перемещение и копирование ячеек и их содержимого

    В этом курсе:

    • Перемещение и копирование ячеек и их содержимого
      Статья
    • Изменение ширины столбцов и высоты строк в Excel
      Статья
    • Поиск или замена текста и чисел на листе
      Статья
    • Объединение и отмена объединения ячеек
      Статья
    • Применение проверки данных к ячейкам
      Статья
    • Импорт и экспорт текстовых файлов (в формате TXT или CSV)
      Статья

    Перемещение и копирование ячеек и их содержимого

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

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

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

    Перемещение ячеек путем перетаскивания

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

    Наведите указатель мыши на границу выделенной области.

    Когда указатель примет вид указателя перемещения , перетащите ячейку или диапазон ячеек в другое место.

    Перемещение ячеек с помощью команд "Вырезать" и "Вставить"

    Выделите ячейку или диапазон ячеек.

    На вкладке Главная нажмите кнопку Вырезать или нажмите клавиши CTRL+X.

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

    На вкладке Главная нажмите кнопку Вставить или нажмите клавиши CTRL+V.

    Копируйте ячейки на листе с помощью команд Копировать и Вставить.

    Выделите ячейку или диапазон ячеек.

    Нажмите кнопку Копировать или клавиши CTRL+C.

    Нажмите кнопку Вставить или клавиши CTRL+V.

    Выделите ячейки, которые нужно переместить или скопировать.

    На вкладке Главная в группе Буфер обмена выполните одно из указанных ниже действий.

    Чтобы переместить ячейки, нажмите кнопку Вырезать .

    Сочетание клавиш Можно также нажать клавиши CTRL+X.

    Чтобы скопировать ячейки, нажмите кнопку Копировать .

    Сочетание клавиш Можно также нажать сочетание клавиш CTRL+C.

    Выделите левую верхнюю ячейку области вставки.

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

    На вкладке Главная в группе Буфер обмена нажмите кнопку Вставить .

    Сочетание клавиш Можно также нажать сочетание клавиш CTRL+V.

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

    При перемещении ячеек путем вырезания и вставки в приложении Excel замещаются существующие данные в области вставки.

    Чтобы воспользоваться специальными вариантами вставки ячеек, можно щелкнуть стрелку под кнопкой Вставить и выбрать нужный вариант. Например, вы можете нажать Специальная вставка или Рисунок.

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

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

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

    Выполните одно из указанных ниже действий.

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

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

    Примечание: При перемещении ячеек в приложении Excel замещаются существующие данные в области вставки.

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

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

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

    На вкладке Главная в группе Буфер обмена выполните одно из указанных ниже действий.

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

    Сочетание клавиш Можно также нажать клавиши CTRL+X.

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

    Сочетание клавиш Можно также нажать сочетание клавиш CTRL+C.

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

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

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

    Заметка При вставке целых строк или столбцов окружающие строки и столбцы сдвигаются соответственно вниз и влево.

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

    Выделите ячейки, которые вы хотите скопировать.

    На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить и выберите команду Выделить группу ячеек.

    В группе Выбор щелкните элемент Только видимые ячейки и нажмите кнопку ОК.

    На вкладке Главная в группе Буфер обмена нажмите кнопку Копировать .

    Сочетание клавиш Можно также нажать сочетание клавиш CTRL+C.

    Выделите левую верхнюю ячейку области вставки.

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

    На вкладке Главная в группе Буфер обмена нажмите кнопку Вставить .

    Сочетание клавиш Можно также нажать сочетание клавиш CTRL+V.

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

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

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

    Выделите диапазон ячеек, содержащий пустые ячейки.

    На вкладке Главная в группе Буфер обмена нажмите кнопку Копировать .

    Сочетание клавиш Можно также нажать сочетание клавиш CTRL+C.

    Выделите левую верхнюю ячейку области вставки.

    На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой Вставить и выберите вариант Специальная вставка.

    Установите флажок пропускать пустые ячейки.

    Дважды щелкните ячейку с данными, которые нужно переместить или скопировать.

    Заметка По умолчанию можно редактировать и выделять данные непосредственно внутри ячейки, дважды щелкнув ее, но можно редактировать и выделять данные и в строка формул.

    В ячейке выделите символы, которые нужно скопировать или переместить.

    На вкладке Главная в группе Буфер обмена выполните одно из указанных ниже действий.

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

    Сочетание клавиш Можно также нажать клавиши CTRL+X.

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

    Сочетание клавиш Можно также нажать сочетание клавиш CTRL+C.

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

    На вкладке Главная в группе Буфер обмена нажмите кнопку Вставить .

    Сочетание клавиш Можно также нажать сочетание клавиш CTRL+V.

    Нажмите клавишу ВВОД.

    Примечание: При двойном щелчке ячейки или нажатии клавиши F2 для редактирования активной ячейки клавиши со стрелками будут работать только внутри данной ячейки. Чтобы с помощью клавиш со стрелками переместиться в другую ячейку, сначала нажмите клавишу ВВОД, чтобы завершить внесение изменений в активную ячейку.

    При вставке скопированных данных можно выполнить любое из следующих действий:

    вставить только параметры форматирования ячейки, такие как цвет текста или цвет заливки (но не содержимое ячеек);

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

    вставить только формулы (но не вычисленные значения).

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

    На вкладке Главная в группе Буфер обмена нажмите кнопку Копировать .

    Сочетание клавиш Можно также нажать сочетание клавиш CTRL+C.

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

    На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой Вставить и выполните одно из следующих действий:

    чтобы вставить только значения, выберите вариант значения;

    чтобы вставить только форматы ячеек, выберите вариант Форматирование;

    чтобы вставить только формулы, выберите вариант формулы.

    Примечание: Если копируемые формулы содержат относительные ссылки на ячейки, Excel настраивает ссылки (и относительные части смешанных ссылок) в скопированных формулах. Допустим, ячейка B8 содержит формулу =СУММ(B1:B7). Если скопировать эту формулу в ячейку C8, новая формула будет ссылаться на соответствующие ячейки этого столбца: =СУММ(C1:C7). Если копируемые формулы содержат абсолютные ссылки на ячейки, ссылки в скопированных формулах не меняются. Если вы не получили ожидаемого результата, попробуйте изменить ссылки в исходных формулах на относительные или абсолютные, а затем снова скопировать ячейки.

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

    Выделите ячейки, которые нужно переместить или скопировать.

    На вкладке Главная в группе Буфер обмена выполните одно из указанных ниже действий.

    Чтобы переместить ячейки, нажмите кнопку Вырезать .

    Сочетание клавиш Можно также нажать клавиши CTRL+X.

    Чтобы скопировать ячейки, нажмите кнопку Копировать .

    Сочетание клавиш Можно также нажать сочетание клавиш CTRL+C.

    Выделите левую верхнюю ячейку области вставки.

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

    На вкладке Главная в группе Буфер обмена нажмите стрелку под кнопкой Вставить и выберите пункт Сохранить ширину столбцов оригинала.

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

    Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

    Ссылка на основную публикацию