Как скопировать ячейки в excel без изменения формула

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

Проблема

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

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

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

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

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

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

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

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

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

   

3. В появившемся диалоговом окне вводим что ищем и на что заменяем и в Параметрах (Options) не забываем уточнить Область поиска — Формулы. Жмем Заменить все (Replace all).
4. Копируем получившийся диапазон с деактивированными формулами в нужное место:

   

5. Заменяем # на = обратно с помощью того же окна, возвращая функциональность формулам.

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

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

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

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

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

Осталось только отжать кнопку Показать формулы (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. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:

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

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

В процессе работы с Microsoft Excel у пользователей возникает необходимость внесения дополнительных данных. Для этого необходимо добавлять или копировать строки с формулами.

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

Как добавить новую строку с формулами в Excel?

Пускай мы добавили формулы в одну строку или в несколько. Теперь нам нужно скопировать все строки с формулами. Как это сделать при добавлении новой строки?

1. Выделяем ту строку, которая содержит формулы. В нашей ситуации это строка под номером 3. Нам необходимо, чтобы остальные строки содержали в себе такие же формулы.



2. Нажимаем на выделенную область правой кнопкой мышки. Из контекстного меню нужно выбрать опцию «Копировать» (или жмем CTRL+C).



3. После этого следует выделить следующую одну (или несколько) нижнюю строку, куда будет вставлена новая с формулами.



4. Щелкнуть по заголовку выделенной строки правой кнопкой мышки и выбрать опцию вставить скопированные ячейки (или нажать комбинацию клавиш CTRL+SHIFT+«=»).

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



Как копировать без изменения формул в Excel?

Часто при копировании относительные формулы (т.е. те, в которых нет знака «$») «подставляют» другие значения. А что делать, если вы не хотите изменять значение формул?

Можно сделать все ссылки в формулах абсолютными. Такие формулы будут копироваться неизменно, без изменения данных. Чтобы сделать ссылки абсолютными, добавляем к ним знак «$».

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

На пример в такой таблице ссылка на наценку ($B$1) не будет изменятся при копировании на новых строках, так как она является абсолютной, а не относительной:

Но в некоторых больших и сложных таблицах слишком много формул, которые нужно скопировать и переместить. И чтобы во всех ссылках проставить знак «$» нужно потратить много времени.

Для решения данной задачи можно воспользоваться хитрым способом. До копирования заменить все знаки «=» на символ «#».

Для «активации» работы формул используется знак «равно» (или «=»). Чтобы деактивировать их на время копирования поменяем = на #. А после копирования вернем все на свои места. Это позволит нам обойти автоматическую смену адресов в относительных ссылках при копировании.

1. Нажимаем Ctrl+H (вызываем окно «Главная»-«Найти и выделить»-«Заменить»).



2. Необходимо в поле «Найти» указать символ =, а в поле «Заменить на» символ #. И жмем «Заменить все».



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



4. Снова жмем комбинацию горячих клавиш «Ctrl+H», но теперь указываем в полях обратные значения: найти «#» и заменить на «=». Система вернет все на свои места

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

Была ли эта статья полезной?

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

​Смотрите также​​ способ, позволяющий скопировать​ в другом диапазоне​ «=» на например​, то пишем текст​Выделяем первую ячейку с​ таблицу формулами. Например,​ правой кнопкой мыши​ затем снова скопировать​ сначала нажмите клавишу​    По умолчанию можно редактировать​в группе​ копировать.​Специальная вставка​Главная​ ячейку, содержащую формулы,​Главная​Примечание:​ лист из A​ на том же​ «xyz», потом поменять​ в первой ячейке,​

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

• ​Буфер обмена​​Выделите ячейку или диапазон​или​нажмите кнопку​ а затем перетащите​нажмите кнопку​Мы стараемся как​ в B так,​

• ​рабочем листе, на​​ назад.​ затем перетаскиванием (черным​ ячейку ниже.​

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

1. ​ по столбцу. Для​ диапзон и выбрать​

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

   ​ маркер заполнения через​Копировать​ можно оперативнее обеспечивать​ чтобы связь B​ другом листе в​P.S. Подправил пост -​ крестиком) ведем вниз.​

3. ​Протягиваем за заленый​ этого устанавливаем (пишем)​

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

   • ​.​​ вас актуальными справочными​​ с A не​​ той же рабочей​​ поменять на «@?&»​​Копировать перетаскиванием можно​​ квадратик в правом​​ формулу в первую​​ функцию «Копировать».​​ параметры ширины целевых​​При вставке скопированных данных​​ но можно редактировать​

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

1. ​.​ или скопировать.​

2. ​По умолчанию в приложении​​ CTRL+V.​​ заполнить.​​Выполните одно из следующих​​ материалами на вашем​​ возникала, а новый​​ книге или, возможно,​

3. ​ не даёт…​ как по столбикам,​

   • ​ нижнем углу выделенного​ ячейку. Затем нажимаем​Или, можно нажать​​ ячеек. Чтобы изменить​​ можно выполнить любое​​ и выделять данные​​Сочетание клавиш​​Выделение ячеек и диапазонов​​ Excel на листе​

   • ​Копируйте ячейки на листе​Копирование и вставка формулы​​ действий.​​ языке. Эта страница​​ лист собирал данные​​ на другом​​Мур​​ так и по​​ диапазона ячеек вниз​​ на ячейку с​​ сочетание клавиш «Ctrl»​​ ширину ячеек в​

     ​ из следующих действий:​​ и в строка​     Можно также нажать сочетание​​На вкладке​​ отображается кнопка​​ с помощью команд​​ в другую ячейку​​Чтобы вставить формулу вместе​​ переведена автоматически, поэтому​​ из B?​​листе другой рабочей​​: Щас попробую, вроде​​ строкам.​

4. ​ по столбцу. даты​ формулой левой мышкой​ + «C».​ соответствии с параметрами​вставить только параметры форматирования​ формул.​

5. 1. ​ клавиш CTRL+C.​

   2. ​Главная​​Параметры вставки​Копировать​

   3. ​ или на листе​ с форматированием, i​

      ​ ее текст может​Beard​ книги. Это можно​ идея гениальная.​Еще несколько способов,​ скопируются так.​ и наводим мышку​

​Если выделим три​​ на правый нижний​ «Главная» в разделе​ указанные ниже действия.​ цвет текста или​​ которые нужно скопировать​ области вставки.​Буфер обмена​ доступ к специальным​Вставить​ абсолютная, смешанная​буфер обмена​ грамматические ошибки. Для​ и так, но​ ссылки на диапазоны​

См. также

​:​ текст, формулы в​ пустые ячейки с​

​ угол этой ячейки,​ «Буфер обмена» нажать​

support.office.com

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

​Выделите ячейки, которые нужно​​ цвет заливки (но​ или переместить.​Совет:​выполните одно из​ вариантам вставки ячеек,​.​Примечание:​на вкладку​ нас важно, чтобы​ это требует сравнительно​внутри формул.​Hugo​ нескольких ячейках, т.д.,​ датой, то даты​ там появится черный​ на кнопку функции​ переместить или скопировать.​ не содержимое ячеек);​Выделение символов в ячейке​ Чтобы переместить или скопировать​

​ указанных ниже действий.​ например​Выделите ячейку или диапазон​​ Мы стараемся как можно​​Главная​​ эта статья была​​ сложных манипуляций, которые​​Выделите диапазон ячеек,​​, большое спасибо.​ смотрите в статье​ скопируются через три​ крестик. Нажимаем на​ «Копировать», предварительно выделив​Выделение ячеек и диапазонов​

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

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

​ пустые ячейки. Получится​

1. ​ него левой мышкой​ ячейки для копирования.​На вкладке​

2. ​ в вычисленные значения​Выполните следующие действия​

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

​Вставить​ вас уделить пару​ ли сможет выполнить​

1. ​ Выберите команду​Гость​

2. ​ в Excel одновременно»​​ так.​​ и ведем вниз,​​Как скопировать значение ячейки​ ​Главная​ без переопределения существующего​

3. ​В ячейке​ в другую книгу,​Вырезать​

4. ​ отображать эту кнопку​​Копировать​​ на вашем языке.​​.​ ​ секунд и сообщить,​ без ошибок.​

​Правка ► Заменить​: оч. интересно.​​ тут.​​Если нужно перенести​​ удерживая левую кнопку​​ в Excel​

1. ​в группе​ форматирования;​

2. ​Дважды щелкните ячейку, а​​ щелкните ярлычок другого​​.​

3. ​ при каждой вставке​​или клавиши CTRL+C.​​ Эта страница переведена​

Перемещение и копирование ячеек

1. ​Чтобы вставить формулу, только​ помогла ли она​

   ​Есть еще варианты?​

2. ​ (Edit ► Replace)​​оказывается, проблема в​​Как cкопировать лист в​​ ячейки с формулами,​​ мыши нажатой.​(без формулы).​

   

   • ​Буфер обмена​вставить только формулы (но​​ затем протащите указатель​ ​ листа или выберите​

     ​Сочетание клавиш​​ ячеек, ее можно​Нажмите кнопку​

   • ​ автоматически, поэтому ее​ iIn группе​​ вам, с помощью​ ​vikttur​

     ​ и в поле​​ символе @, если​ Excel -​

3. ​ но​Отпускаем мышку. Формулы скопировались,​

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

4. ​ кнопок внизу страницы.​​: Это простой вариант.​​ Что (Find What)​​ он первый.​​правой мышью нажимаем​​адреса в формулах Excel​ ​ в них изменились​

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

​ содержимое ячейки, которое​​ выделите левую верхнюю​

• ​ CTRL+X.​Файл​или клавиши CTRL+V.​ неточности и грамматические​на вкладку​ Для удобства также​

• ​ Сложнее — писать​ введите = (знак​причем Excel (у​ на ярлык листа.​ должны остаться прежние​

• ​ адреса ячеек относительно​ «Копировать». Переходим на​Чтобы переместить ячейки, нажмите​ ячеек со значениями,​​ требуется выделить.​ ​ ячейку области вставки.​Чтобы скопировать выделенный фрагмент,​​и выберите команду​​Выделите ячейки, которые нужно​​ ошибки. Для нас​​Главная​

• ​ приводим ссылку на​ код в VBA​ равенства).​​ меня 2003) при​​ В диалоговом окне​(не привязывать к​ нового места. Таблица​ новое место, делаем​​ кнопку​​ форматами или формулами,​В строке формул​На вкладке​ нажмите кнопку​Параметры​​ переместить или скопировать.​​ важно, чтобы эта​​, нажмите кнопку​​ оригинал (на английском​​ для удаления лишних​​В поле Заменить​​ простом вводе в​​ выбираем «Переместить/скопировать».​​ новым ячейкам), то​ формулами заполнена.​​ активной ячейку, куда​

Перемещение и копирование ячеек с помощью мыши

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

1. ​. В категории​Выделение ячеек и диапазонов​ статья была вам​

   ​Вставить​

2. ​ языке) .​ ссылок.​

   • ​ на (Replace With)​ ячейку любого текста,​Ставим галочку у слов​ это снова в​Как скопировать формулу​ будем копировать, нажимаем​​.​Выделение ячеек и диапазонов​ протащите указатель мыши,​

   • ​в группе​.​Дополнительно​На вкладке​ полезна. Просим вас​, выберите пункт​Важно, которые следует учитывать​​DJ_Serega​ введите @ (знак​ начинающегося с @,​

​ «Создать копию». Выбираем​​ статье «Относительные и​ в Excel таким​ правой мышью и​Сочетание клавиш​

​На вкладке​ чтобы охватить содержимое​Буфер обмена​Сочетание клавиш​в разделе​Главная​ уделить пару секунд​Специальная вставка​ возможности как может​: А если так:​ коммерческого at) или​ выдает сообщение «Неверная​ куда переместить скопированный​

​ абсолютные ссылки в​ способом, чтобы не​ выбираем функцию «Вставить».​     Можно также нажать клавиши​Главная​ ячейки, которое требуется​нажмите кнопку​     Можно также нажать сочетание​

Вставка скопированных и перемещенных ячеек между существующими ячейками

1. ​Вырезание, копирование и вставка​в группе​ и сообщить, помогла​и нажмите кнопку​

   ​ измениться при перемещении​

2. ​- Скопировать лист​​ любой​​ функция» (конечно, это​​ лист (в эту​​ формулах Excel». Ссылка​ копировался формат, смотрите​

   

   • ​Когда значения ячеек,​ CTRL+X.​​в группе​ ​ выделить в строке​

     ​Вставить​​ клавиш CTRL+C.​снимите флажок​

   • ​Буфер обмена​ ли она вам,​​формулы​ ​ или копировании формулы​

     ​ «А»​​другой символ который,​ не относится к​

3. ​ или другую книгу,​ на статью в​ в статье «Как​ в которых стоят​​Чтобы скопировать ячейки, нажмите​​Буфер обмена​​ формул.​​.​

   ​Щелкните правой кнопкой мыши​​Отображать кнопку параметров вставки​выполните одно из​ с помощью кнопок​.​ относительная ссылка на​- На листе​ вы уверены, не​ ячейкам, имеющим формат​ перед каким листом)​

4. ​ статье выше.​​ скопировать в Excel».​​ формулы (цифры) не​ кнопку​нажмите кнопку​

​С помощью клавиатуры​​Сочетание клавиш​ левую верхнюю ячейку​ при вставке содержимого​ указанных ниже действий.​ внизу страницы. Для​

Копирование только видимых ячеек

​Примечание:​ ячейку.​ «В» -> ПКМ​ используется ни в​ Текстовый.​ или оставляем так​Если нужно​Третий вариант.​ копируются, делаем так:​

1. ​Копировать​Копировать​

   ​Нажмите клавишу F2, чтобы​

2. ​     Можно также нажать клавиши​​ области вставки, а​​.​​Чтобы переместить ячейки, нажмите​​ удобства также приводим​​ Чтобы вставить только результаты​​Перемещение формулы:​​ — Специальная вставка​​ одной формуле.​

   

3. ​это баг или​​ и нажимаем «ОК».​​не копировать, а перенести​​Как копировать формулы в​​ выделяем ячейки как​​.​​.​

4. ​ изменить ячейку. С​​ CTRL+V.​​ затем выберите пункт​​По умолчанию режим перетаскивания​​ кнопку​​ ссылку на оригинал​ ​ формул. В группе​

   

   ​При перемещении формулы​​ — Формулы ??​Щелкните на кнопке​

5. ​ фича?​ Затем, у скопированного​

   ​ ячейку с формулой​​ Excel на весь​ обычно-> «Копировать». Появится​Сочетание клавиш​Сочетание клавиш​ помощью клавиш со​Если нажать стрелку под​Вставить вырезанные ячейки​ включен, поэтому можно​Вырезать​

6. ​ (на английском языке).​​буфер обмена​​ ссылки на ячейки​​Beard​​ Заменить все (Replace​​Serge_007​ ​ листа, можно изменить​

   ​, то не копируем​​ столбец​ вокруг ячеек, которые​

• ​     Можно также нажать сочетание​     Можно также нажать сочетание​​ стрелками поместите курсор​ ​ командой​или​ перемещать и копировать​.​

​Чтобы переместить или скопировать​на вкладке​ в формуле не​: Пробовал. Не помогает.​ All).​:​ название, переместить его​ её, а через​.​

​ мы копируем, пульсирующая​ клавиш CTRL+C.​ клавиш CTRL+C.​ в нужное место,​Вставить​Вставить скопированные ячейки​ ячейки с помощью​

Предотвращение замещения данных скопированными пустыми ячейками

1. ​Сочетание клавиш​ содержимое ячейки с​

   ​Главная​

2. ​ изменяются вне зависимости​​ Все равно получается​​Во всех формулах​​Из книги Рейны и​​ на другое место,​​ контекстное меню «Вырезать»​ ​Нам нужно скопировать​

   

   ​ рамка, еще раз​​Выделите левую верхнюю ячейку​Выделите левую верхнюю ячейку​

3. ​ а затем выделите​, можно выбрать​

4. ​.​​ мыши.​​     Можно также нажать клавиши​​ помощью​​нажмите кнопку​ от того, какой​​ ссылка на файл​ ​ на вашем рабочем​​ Девида Холи «Трюки​​ изменить цвет ярлычка.​

5. ​ -> «Вставить». Формула​​ формулу из ячейки​​ нажимаем правой мышью​

Перемещение и копирование только содержимого ячейки

1. ​ области вставки.​ области вставки или​ содержимое, удерживая нажатой​

   ​ параметры вставки, которые​​Совет​Выделите ячейки или диапазон​ CTRL+X.​Вырезать​Вставить​ тип ссылок на​ A.​ листе вместо знака​

2. ​ в Excel»:​Как скопировать лист в​ удалится из первой​

   ​ F24. Наводим на​

   ​ на выделенную ячейку​	​Совет:​
   ​ ту ячейку, куда​	​ клавишу SHIFT.​ следует применить к​    Чтобы переместить или скопировать​ ячеек, которые требуется​Чтобы скопировать ячейки, нажмите​
   ​,​	​, выберите команду​ ячейки, который используется.​GIG_ant​ равенства будет стоять​Quote​ новую книгу Excel​
   ​ ячейки и перенесется​	​ квадратик в правом​ и в диалоговом​ Чтобы переместить или скопировать​ нужно вставить значение,​На вкладке​ выделенному диапазону.​ выделенный фрагмент на​ переместить или скопировать.​

3. ​ кнопку​​Копировать​​Специальная вставка​​При копировании формулы:​​: Копируете лист с​символ @.​

   

   • ​200?’200px’:»+(this.scrollHeight+5)+’px’);»>​.​​ в новое место.​ ​ нижнем углу ячейки.​

     ​ окне​​ выделенный фрагмент на​ формат ячейки или​

   • ​Главная​При копировании значения последовательно​​ другой лист или​ ​Выделение ячеек и диапазонов​

     ​Копировать​​и​и выберите пункт​

4. ​При копировании формулы​ формулами в книгу​Теперь вы просто​Перемещение относительных формул​Для этого в​Ещё варианты копирования​ Указатель мышки приобретает​

5. ​выбираем команду «Специальная вставка»​​ другой лист или​​ формулу.​​в группе​​ вставляются в строки​​ в другую книгу,​ ​Выполните одно из указанных​

   ​.​​Вставить​значения​

6. ​ изменится относительные ссылки​

   ​ В, потом Ctrl+H​​ можете скопировать этот​без изменения ссылок​ диалоговом окне в​ формул, чтобы ссылки​ вид черного крестика.​, выбираем — «Значение».​ в другую книгу,​На вкладке​Буфер обмена​ и столбцы. Если​ щелкните ярлычок другого​ ниже действий.​Сочетание клавиш​. Или скопируйте определенного​

Копирование значений ячеек, форматов ячеек и только формул

​.​ на ячейки.​ найти [*] заменить​

• ​ диапазон, вставить его​В Excel ссылка в​ разделе «Переместить выбранные​ в них не​ Нажимаем два раза​

• ​Затем, как обычно -​ щелкните ярлычок другого​Главная​выполните одно из​

• ​ область вставки содержит​ листа или выберите​

• ​Для перемещения ячейки или​     Можно также нажать сочетание​ содержимого или атрибутов​Убедитесь, что ссылки на​

  ​Выделите ячейку с формулой,​

• ​ на пусто.​​ на нужное​​ формуле может быть​​ листы в книгу»​​ менялись, описаны в​​ подряд левой мышкой​ ​ «Вставить». С помощью​

  

  ​ листа или выберите​​в группе​ указанных ниже действий.​

• ​ скрытые строки или​ другую книгу и​ диапазона ячеек поместите​ клавиш CTRL+C.​ ячеек. Например скопируйте​ ячейки в формуле​

• ​ которую необходимо перенести.​​Сам пробовал все​​место, выделить и​​ либо относительной, либо​​ выбираем «(новая книга)».​ статье «Как скопировать​​ на этот крестик.​ ​ функции «Специальная вставка»​ другую книгу и​

  • ​Буфер обмена​Чтобы переместить выделенный фрагмент,​​ столбцы, возможно, потребуется​​ выделите левую верхнюю​

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

  • ​В группе​ работает.​​ выбрать команду Правка​​абсолютной. Иногда, однако,​

    ​Как скопировать формат в​​ формулу в Excel​ Формула скопируестя до​ можно не только​ выделите левую верхнюю​щелкните стрелку под​ нажмите кнопку​ отобразить их, чтобы​ ячейку области вставки.​ выделенного фрагмента. Когда​ области вставки.​ копирования формулы или​ При необходимости измените​буфер обмена​Beard​ > Заменить (Edit​ возникает необходимость воспроизвести​ Excel ячейки или​ без изменения ссылок».​ конца заполненного столбца.​ копировать данные, но​ ячейку области вставки.​ кнопкой​Вырезать​ увидеть все скопированные​В диалоговом окне​ указатель примет форму​

Копирование параметров ширины ячеек

​Совет:​ скопировать только формулу.​ тип ссылки, выполнив​на вкладке​: Спасибо! Действительно, просто​ ► Replace). На​ те же​ условное форматирование​

1. ​Как заполнить таблицу в​ У нас, в​

   ​ и складывать их,​

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

   

   • ​ Чтобы переместить или скопировать​Ячейки в Excel перемещаются​​ следующие действия.​ ​Главная​

     ​ и быстро.​​ этот раз​формулы в другом​

   • ​, без самих значений​ Excel.​​ примере, в ячейки​ ​ вычитать, умножать, т.​

     ​Главная​​и выполните одно​Сочетание клавиш​

3. ​При копировании и вставке​выберите направление, в​

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

4. ​ F25:F26.​​ д. Подробнее, смотрите​​в группе​​ из следующих действий:​​     Можно также нажать клавиши​ скрытых или отфильтрованных​​ котором нужно сдвинуть​ ​ или диапазон ячеек​​ другой лист или​​ с формулами, итоговыми​

support.office.com

Копирование в Excel.

​В строка формул​​Вырезать​ всегда победил!​ на = (знак​ листе или в​​ статье «Копирование условного​ ячеек в определенной​Получилось.​ в статье «Суммирование​​Буфер обмена​​чтобы вставить только значения,​
​ CTRL+X.​ данных в другое​ окружающие ячейки.​ в новое расположение.​ в другую книгу,​ значениями, форматом и​выберите ссылку, которую​.​Киселев​ равенства). Скопированные формулы​ рабочей книге, или​ форматирования, формата в​ последовательности. Например, надо​Внимание!​ в Excel» здесь.​нажмите стрелку под​ выберите вариант​
​Чтобы скопировать выделенный фрагмент,​ приложение или в​Примечание​Чтобы скопировать ячейки или​ щелкните ярлычок другого​ примечаниями.​ вы хотите изменить.​
​Также можно переместить формулы​: я в таких​ будут​
​ же на​ Excel».​ заполнить графу таблицы​Формула скопируется в​Если копируем числа​ кнопкой​
​значения​ нажмите кнопку​​ другой экземпляр приложения​
​    При вставке целых строк​ диапазон ячеек, поместите​ листа или выберите​В Excel ячейки можно​Нажимая клавишу F4, выберите​ путем перетаскивания границы​ случаях копирую блокнотом​ссылаться на те​
​другом листе.​Иногда, когда нужно​ названиями месяцев года.​ таблице, если ячейки​ с точками -​Вставить​;​Копировать​ Excel будут скопированы​ или столбцов окружающие​ указатель на границу​ другую книгу и​ перемещать путем перетаскивания​​ нужный тип ссылки.​​ выделенной ячейки в​
​ формулы.​ же ячейки, что​Если формулу нужно​ напечатать документ, распечатывается​ Пишем в первой​ столбца слева таблицы​ разделителями всместо запятой​и выберите пункт​чтобы вставить только форматы​.​
​ только видимые ячейки.​ строки и столбцы​ выделенного фрагмента, удерживая​ выделите левую верхнюю​ или с помощью​В таблице перечислены как​ левую верхнюю ячейку​ибо если копировать​ и исходные.​ сделать абсолютной, введите​
​ (копируется) не значение​ ячейке «Январь», в​
​ будут заполнены. Тогда​
​ (1.23), то вставив,​​Сохранить ширину столбцов оригинала​ ячеек, выберите вариант​​Сочетание клавиш​Выделите диапазон ячеек, содержащий​ сдвигаются соответственно вниз​ нажатой клавишу CTRL.​ ячейку области вставки.​ команд​ тип ссылки будут​ области вставки. Это​ не весь лист,​Beard​ $ (знак доллара)​
​ ячеек, а формулы.​
​ следующей ячейке ниже​​ Excel поймет до​​ эти числа превратятся​.​Форматирование​     Можно также нажать сочетание​ пустые ячейки.​ и влево.​ Когда указатель примет​На вкладке​Вырезать​ обновления, если формула​ приведет к замене​ а часть -​: Есть файл A​ перед буквой​ Чтобы было значение​ пишем «Февраль». Теперь​ какой ячейке расположена​ в дату. Чтобы​Здесь рассмотрим,​;​ клавиш CTRL+C.​Выделение ячеек и диапазонов​
​Если часть ячеек, строк​ форму указателя копирования​Главная​и​ со ссылкой копируется​
​ всех имеющихся данных.​ то копируются только​ со многими листами.​столбца или номером​ (цифры, а не​ выделяем обе эти​
​ таблица.​
​ этого избежать ,​как копировать в Excel​чтобы вставить только формулы,​​Щелкните в том месте​
​На вкладке​ или столбцов на​, перетащите ячейку​в группе​Вставить​ на две ячейки​Выполните одно из действий,​ значения, а не​Один из листов​ строки в ссылке​ формулы) в ячейках,​ ячейки. Наводим мышь​Такми способом можно​ есть несколько способов.​ формулыячейки, как скопировать​
​ выберите вариант​
​ ячейки, куда нужно​
​Главная​ листе не отображается,​ или диапазон ячеек​Буфер обмена​.​ вниз и на​ указанных ниже.​
​ формулы.​ с помощью формул​ на ячейку, например,​ читайте статью «Печать​ на правый нижний​ копировать значение, формулы,​ Смотрите статью «Автозамена​ лист в Excel,​формулы​ вставить символы, или​в группе​ можно скопировать все​ в новое расположение.​нажмите кнопку​Перемещение ячеек путем перетаскивания​
​ две ячейки вправо.​Чтобы вставить формулу и​
​формулы — показать​ собирает данные с​ $А$1. Когда вы​ в Excel» тут.​ угол выделенного диапазона​ формат. Если выделим​ в Excel».​ таблицу,значениеформат​.​ дважды щелкните другую​Буфер обмена​
​ ячейки или только​Примечание:​Вставить​
​Выделите ячейки или диапазон​Для копируемой формулы:​ все форматирование​ формулы — выделяю​ остальных листов.​сделаете это, неважно,​​Чтобы найти быстро​ и ведем вниз​ ячейки в нескольких​Как скопировать формулу в​, т.д. Одна из​Примечание:​
​ ячейку для перемещения​нажмите кнопку​ видимые. Например, можно​​ При перемещении ячеек в​.​​ ячеек, которые требуется​Первоначальная ссылка​: ли n группе​ таблицу, копирую в​Макросов нет.​ куда вы скопируете​ в большой таблице​ по столбцу черный​
​ стролбцах, то копирование​​ Excel.​ многих возможностей таблицы,​​ Если копируемые формулы содержат​ или копирования в​Копировать​ скопировать только отображаемые​ приложении Excel замещаются​Сочетание клавиш​ переместить или скопировать.​
​Новая ссылка​буфер обмена​ блокнот​Есть файл B​ формулу, она все​ Excel нужную ячейку,​ крестик. Таблица сама​
​ будет сразу во​Первый вариант.​
​ облегчающих работу –​ относительные ссылки на​ нее данных.​.​ итоговые данные на​ существующие данные в​     Можно также нажать сочетание​Наведите указатель мыши на​$A$1 (абсолютный столбец и​на вкладку​и в новом​ с точно такой​ так же будет​ диапазон, можно воспользоваться​ заполнит ячейки следующими​ всех выделенных столбцах.​Когда мы​ это​
​ ячейки, Excel настраивает​На вкладке​Сочетание клавиш​ листе со структурой.​ области вставки.​
​ клавиш CTRL+V.​ границу выделенной области.​ абсолютная строка)​Главная​​ файле становлюсь в​ же структурой листов,​ ссылаться на​ функцией «Поиск». Смотрите​​ месяцами года.​ Например, если выделим​копируем ячейки с относительными​
​копирование в Excel​​ ссылки (и относительные​Главная​     Можно также нажать сочетание​​Выделите ячейки, которые вы​При копировании ячеек ссылки​Примечания:​Когда указатель примет вид​
​$A$1​, нажмите кнопку​ ту же ячейку​ но с другими​
​те же ячейки.​ статью «Поиск в​Справа всплывает подсказка,​ ячейки D24, E24,​ формулами​.​ части смешанных ссылок)​в группе​
​ клавиш CTRL+C.​ хотите скопировать.​​ на ячейки изменяются​ ​ указателя перемещения​A$1 (относительный столбец и​
​Вставить​ и вставляю из​ данными.​Иногда, однако, можно​ Excel».​ по какой месяц​ F24, нажмем два​(«Копировать» -> «Вставить»)​Можно копировать всю​ в скопированных формулах.​Буфер обмена​Выделите левую верхнюю ячейку​Выделение ячеек и диапазонов​
​ автоматически. Но при​В Excel вырезанные или​​, перетащите ячейку​
​ абсолютная строка)​.​ блокнота.​В файле B​ ввести много формул,​
​Мур​ заполнили таблицу. Отпускаем​ раза мышью по​​ и затем переносим​ таблицу или только​ Допустим, ячейка B8​нажмите кнопку​ области вставки.​
​На вкладке​ перемещении ячеек ссылки​ скопированные ячейки выделяются​ или диапазон ячеек​C$1​Чтобы вставить только формулу​потом копирую из​ нужен точно такой​ содержащих не абсолютные,​
​: Здравствуйте, Все.​ мышку. Все, таблица​ углу ячейки F24,​ в другое место,​ значение ячеек без​ содержит формулу =СУММ(B1:B7).​Вставить​

excel-office.ru

Копирование формул (Копирование столбца с формулами без смещения)

​На вкладке​​Главная​
​ не изменяются, и​ анимированной пунктирной рамкой.​ в другое место.​$A1 (абсолютный столбец и​
​: В группе​ 1го файла «формат​ же сводный лист,​
​ а​Не могу разобраться​ заполнена.​ то скопируются данные​
​ то таблица Excel​ формул, или копировать​ Если скопировать эту​

​.​​Главная​в группе​ вместо содержимого этих​ Чтобы снять это​

​Перемещение ячеек с помощью​ относительная строка)​буфер обмена​

​ по образцу» и​​ как в файле​относительные ссылки. Обычно​

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

​ формулу в ячейку​​Сочетание клавиш​
​в группе​Редактирование​ ячеек и всех​
​ выделение, нажмите клавишу​ команд «Вырезать» и​$A3​на вкладке​ готово.​ A, собирающий данные​ это делается для​ с формулами в​ заполнить дни месяца,​ столбцам.​
​ ячеек в формулах​ к новым ячейкам​

​ C8, новая формула​​     Можно также нажать сочетание​

​Буфер обмена​нажмите кнопку​ ячеек, которые на​

​ ESC.​​ «Вставить»​​A1 (относительный столбец и​
​Главная​

​Hugo​ с остальных листов​ того, чтобы, если​
​ соседний столбец.​ года, т.д. Таким​Копирование в Excel через​
​ относительно новых ячеек.​ или без привязки​ будет ссылаться на​ клавиш CTRL+V.​щелкните стрелку под​
​Найти и выделить​
​ них ссылаются, может​При перемещении ячеек путем​Выделите ячейку или диапазон​ относительная строка)​
​нажмите кнопку​: Ещё вариант -​ файла B.​ вы скопируете​
​Когда формировали таблицу​ же образом можно​ ячейку.​ Смотрите подробнее в​ к ячейке (со​
​ соответствующие ячейки этого​
​Нажмите клавишу ВВОД.​ кнопкой​и выберите команду​ отображаться ошибка адресации.​
​ вырезания и вставки​ ячеек.​C3​Вставить​
​ сперва CTRL+H меняем​При прямом копировании​исходную ячейку с​ не везде предусмотрели​
​заполнять и цифрами, расположенными​
​Можно копировать не​ статье «Относительные и​ сменой адреса ячейки​ столбца: =СУММ(C1:C7). Если​
​Примечание:​Вставить​Выделить группу ячеек​ В этом случае​
​ в приложении Excel​На вкладке​Примечание:​, выберите пункт​ «=» на например​
​ листа из A​ формулой вниз или​ абсолютные ссылки.​ как по порядку,​
​ только заполненные ячейки,​
​ абсолютные ссылки в​ или оставить адрес​ копируемые формулы содержат​
​ При двойном щелчке ячейки​и выберите вариант​.​ потребуется изменить ссылки​ замещаются существующие данные​Главная​
​ Вы также можете скопировать​Специальная вставка​ «xyz» (на сочетание,​ в B в​ вбок, ссылка на​
​Надеялся на специальную​ так и с​ но и пустые,​ Excel».​
​ без изменений). Можно​ абсолютные ссылки на​ или нажатии клавиши​
​Специальная вставка​В группе​ вручную.​ в области вставки.​
​нажмите кнопку​
​ формулы в смежные​и нажмите кнопку​ которого точно нет​ результате получаются формулы,​
​ строку или столбец​ вставку (скопировать формулы),​ интервалом​ заполненные ячейки в​Второй выриант.​
​ копировать данные с​ ячейки, ссылки в​ F2 для редактирования​.​
​Выбор​Если выделенная область копирования​Чтобы воспользоваться специальными вариантами​

excelworld.ru

Скопировать формулу в другой файл

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

​: а как произвести​​ копировании не всего​
​ и относительные, и​ если это возможно​
​Если нужно​ нужно поставить даты​ одну и ту​ лист. И многое,​

​ результата, попробуйте изменить​​ данной ячейки. Чтобы​Дважды щелкните ячейку с​ОК​ их. Может понадобиться​

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

​ требуемого результата, выделите​​на вкладке​ сводных таблиц?​Существует ли простой​ те же формулы​: Через Ctrl+H заменить​ подряд​ ячейку.​

​ Таким образом, заполняем​​ «Копировать», достаточно нажать​ или абсолютные, а​ в другую ячейку,​Примечание​

planetaexcel.ru

​Главная​

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

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

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

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

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

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

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

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

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

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

Как скопировать формулу/формулы в Excel, копирование и вставка формул

Работа с формулами является неотъемлемой частью создания и редактирования таблиц в Excel. При этом создаются как простые, так и вложенные формулы, содержащие относительные, абсолютные и смешанные ссылки на ячейки. При внесении формул в таблицы, наиболее распространенными операциями являются копирование и вставка формул.

Что такое формула?

Формулы — это некоторые выражения, выполняющие вычисления между операндами при помощи операторов. Формулам всегда предшествует знак равенства, за которым следуют операнды и операторы.

Операнды — это элементы вычисления (ссылки, функции и константы ).

Ссылки — это адреса ячеек или их диапазонов.

Функции — это заранее созданные формулы, выполняющие сложные вычисления с введенными значениями (аргументами) в определенном порядке. Различают математические, статистические, текстовы, логические и другие категории функций.

Константы — это постоянные значения, как текстовые, так и числовые.

Операторы — это знаки или символы, определяющие тип вычисления в формуле над операндами. Используются математические, текстовые, операторы сравнения и операторы ссылок.

Ссылки в формулах

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

Относительные ссылки на ячейки

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

Абсолютные ссылки на ячейки

Абсолютная ссылка — это неизменяемая ссылка на ячейку, то есть при изменении позиции ячейки с формулой адрес ячейки с абсолютной ссылкой остается неизменным. Абсолютная ссылка указывается символом $ перед именем (номером) столбца и перед номером строки, например $A$1.

Смешанные ссылки на ячейки

Смешанная ссылка — это комбинация относительных и абсолютных ссылок, когда используется либо абсолютная ссылка на столбец и относительная на строку, либо абсолютная на строку и относительная на столбец, например $A1 или A$1. При изменении позиции ячейки с формулой, содержащей смешанные ссылки, относительная часть ссылки изменяется, а абсолютная остается неизменной.

Трехмерные ссылки на ячейки

Трехмерные ссылки — это ссылки на одну и ту же ячейку или даипазон ячеек, расположенных на нескольких листах одной книги. Трехмерная ссылка кроме имени столбца и номера строки включает в себя имя листа и имеет следующий вид Лист1:Лист3!А1.

Как создать формулу и ввести ее в ячейку?

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

Простые формулы

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

Для того чтобы создать простую формулу, необходимо:

— выделить ячейку, в которой будет находиться формула;

— ввести с клавиатуры символ равно (=);

— ввести число, затем знак действия, затем следующее число и так далее (например =2+3*4);

— нажать Enter для перехода вниз, Shift+Enter для перехода вверх, Tab для перехода вправо или Shift+Tab для перехода влево.

Формулы с использованием относительных ссылок

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

— выделить ячейку, в которой будет находиться формула;

— ввести символ равенства (=) с клавиатуры;

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

— вставить в формулу оператор, ввести адрес следующей ячейки и так далее;

— завершить создание формулы аналогично тому, как это описано в предыдущем случае.

Формулы с использованием абсолютных ссылок

Формулы с использованием абсолютных ссылок создаются с небольшим отличием от формул использующим относительные ссылки. Для создания формулы этого типа необходимо:

— выделить ячейку, в которой будет находится формула;

— ввести символ равенства (=) с клавиатуры;

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

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

— нажать на клавиатуре F4;

— завершить создание формулы клавишей Enter.

Как ввести одну формулу одновременно в несколько ячеек?

Для ввода одной формулы в диапазон ячеек необходимо:

— выделить диапазон ячеек;

— ввести формулу в первую ячейку диапазона;

— закрепить результат сочетанием клавиш Ctrl+Enter.

Как выделить все ячейки с формулами?

В версиях приложения Excel 2007 и выше существует возможность выделять группы ячеек, объединенные общим признаком, например можно найти и выделить все ячейки, содержащие формулы. Для этого на вкладке «Главная» нужно раскрыть меню кнопки «Найти и выделить» и выбрать пункт «Формулы» в списке команд.

Как скопировать формулу из одной ячейки в другую?

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

Скопировать формулу из выбранной ячейки можно любым известным способом (при помощи кнопки «Копировать» на вкладке «Главная», при помощи сочетания горячих клавиш Ctrl+C, при помощи пункта «Копировать» в контекстном меню и так далее). После того как формула скопирована, необходимо выделить ячейку, в которую нужно вставить формулу и использовать любой известный способ вставки (кнопкой «Вставить» на вкладке «Главная», сочетанием горячих клавиш Ctrl+V, выбрав пункт «Вставить» из контекстного меню, выбрав пункт «Специальная вставка»). После этого закрепить результат кликом по клавише Enter. Для копирования формулы можно использовать также способ, при котором курсор мыши наводится на правый нижний угол маркера выделения до появления тонкого черного крестика и при нажатой левой кнопке мыши протягивается по всему диапазону. При этом в каждой следующей ячейке формула будет иметь ссылки на новые соответствующие ячейки.

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

Как заменить формулу результатом ее вычисления?

Если скопировать ячейку или диапазон ячеек с формулами, а вставку осуществить при помощи пункта «Вставить значения» (вкладка «Главная»/группа «Буфер обмена»/кнопка «Вставить», либо контекстное меню «Специальная вставка»/»Значения»), то в результате этой операции вместо формул будут отображены значения, полученные в результате вычисления этих формул. Если скопировать диапазон ячеек с формулами и в этот же диапазон вставить значения, то формулы этого диапазона будут заменены результатами их вычислений.

Как ускорить работу с формулами при создании и редактировании таблиц?

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

Облегчает и ускоряет работу при копировании формул, текста формул и значений формул, а также при замене формул их значениями VBA-надстройка для Excel, позволяющая в указанном диапазоне копировать все ячейки с формулами и вставлять в соседние ячейки с заданным смещением как формулы и тексты формул, так и значения. Диалоговое окно надстройки и ссылка для скачивания представлены ниже.

Надстройка позволяет:

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

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

3. выбирать один из трех режимов копирования формул:

— «Скопировать формулы» — простое копирование формул, при котором все ссылки, используемые в формулах, автоматически изменяются в соответствии с новым размещением формул;

— «Скопировать текст формул» — точное копирование формул, без изменения ссылок, используемых в формулах;

— «Скопировать значения формул» — копирование, при котором формулы заменяется результатамм их вычислений.

4. заменять формулы выбранного диапазона результатами вычисления (если выбрать опцию «Скопировать значения формул», а в полях, где задается смещение установить нули).

Excel. Перемещение формул без изменения относительных ссылок

На днях дочь обратилась с проблемой. Она построила сложную таблицу в Excel с большим числом формул, основанных на относительных ссылках, и возникла потребность скопировать эти формулы в новую область листа с сохранением ссылок на те же ячейки, что и исходные формулы (подробнее о типе ссылок см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel). «Зайти» во все ячейки с формулами и изменить ссылки на абсолютные было затруднительно, так как таких ячеек было больше ста…

К сожалению, стандартные средства Excel не позволяют выполнить подобное копирование. Что вообще-то говоря, удивительно! Попробуйте, например, перенести формулу =В1+С1, хранящуюся в ячейке D1, в ячейку D4 (рис. 1). Если выполнить копирование с помощью специальной вставки и опции вставить формулы, в ячейке D4 обнаружите формулу =В4+С4.

Рис. 1. Специальная вставка; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате Excel

Решение пришло из моего прошлого опыта: когда я был верстальщиком, я очень широко использовал контекстные замены, и был мастером в этом искусстве. ?

Выделите диапазон ячеек, который хотите скопировать. В нашем примере это С4:С13 (область 1 на рис. 2), и выберите команду Главная → Найти и выделить → Заменить (область 2 на рис. 2), или нажмите Ctrl + H (английская H).

Рис. 2. Найти и заменить

В открывшемся диалоговом окне «Найти и заменить» (рис. 3) в поле «Найти» введите знак = (с него начинаются все формулы). В поле «Заменить на» введите знаки && или любой иной символ который, как вы уверены, не используется ни в одной из формул. Нажмите «Заменить все».

Рис. 3. Заменить знак = на знаки &&

Во всех формулах на рабочем листе вместо знака равенства теперь стоит && (рис. 4).

Рис. 4. После замены

Скопируйте ячейки С4:С13 в требуемое место, и выполните обратную замену всех && на =. И первоначальные, и новые формулы ссылаются на одни и те же ячейки (рис. 5), причем формулы используют относительные ссылки, то есть их можно «протягивать».

Рис. 5. Формулы удалось перенести

Дополнение от 1 октября 2016

Еще один вариант решения проблемы можно найти у Джона Уокенбаха. [1] Переключите Excel в режим просмотра формул, пройдя по меню Формулы –> Зависимости формул –> Показывать формулы (рис. 6). Выделите диапазон для копирования. В данном примере – С4:С13. Скопируйте его в буфер. Откройте текстовый редактор, например, Word или Блокнот. Вставьте скопированные данные. Выделите весь текст, и снова скопируйте его в буфер. Вернитесь в Excel и активизируйте верхнюю левую ячейку диапазона, в который хотите вставить ваши формулы. Убедитесь, что лист, на который копируются данные, находится в режиме просмотра формул. Вставьте формулы. Выйдете из режима показа формул, повторно пройдя по меню пройдя по меню Формулы –> Зависимости формул –> Показывать формулы. Формулы в целевом диапазоне будут ссылаться на те же ячейки, что и в исходном.

Рис. 6. Режим Показывать формулы

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

[1] Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 144, 145.

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

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

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

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

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

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

В группе » буфер обмена » на вкладке » Главная » нажмите кнопку Вырезать.

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

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

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

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

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

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

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

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

Чтобы вставить формулу и форматирование, я использую группу » буфер обмена » на вкладке » Главная » и выбираю команду » Вставить«.

Чтобы вставить только формулу, надстройку группу » буфер обмена » на вкладке » Главная «, нажмите кнопку Вставить, выберите команду Специальная Вставкаи нажмите кнопку формулы.

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

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

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

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

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

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

Копирование формул без изменения адресов относительных ссылок

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

В зависимости от того копируется или перемещается формула в Excel, адреса ее ссылок могут существенно отличаться. Об этом нужно помнить всегда!

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

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

Скопируйте значения столбца B (например, комбинацией клавиш CTRL+C) и вставьте их в столбец D (CTRL+V) . А потом переместите данные из столбца B в E (например, комбинацией клавиш CTRL+X).

Теперь переключитесь в режим отображения формул – CTRL+`(Ё). Обратите внимание, как ведут себя ссылки на ячейки при перемещении и копировании.

При перемещении (те, что в столбце E) ссылки не изменяются. А при копировании (те, что в столбце D), они смещаются автоматически.

Копирование формул в Excel со смещением

При копировании адреса относительных ссылок приспосабливаются к новому положению. Если ссылка была на одну ячейку влево, то она так и продолжает ссылаться, но адрес естественно меняется. Поэтому формула: =A2*1,23 стала формулой =C2*1,23. Когда мы ту же самую формулу не скопируем, а переместим, то адреса ее ссылок не изменятся, несмотря на то, что они относительные. При перемещении ссылки на ячейки ведут себя как абсолютные. Об этом следует всегда помнить пользователю Excel.

Примечание. В разделе, посвященном формулам, будет уделено больше внимания относительным и абсолютным ссылкам. А пока отметим что по умолчанию ссылки относительные, а если в адресе присутствует символ «$» — значит ссылка абсолютная.

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

1. Скопируйте «Лист1», например с помощью мышки+CTRL. Наведите указатель на ярлычок первого листа. Удерживая левую клавишу мышки и клавишу CTRL на клавиатуре, переместите ярлычок (копия листа) в новое место. Отпустите сначала мышку, а потом клавиатуру. У вас получился такой же лист, но уже с названием «Лист1(2)».
2. На копии «Лист1(2)» в ячейку D2 скопируйте значение из B2, а в ячейку E2 переместите (как на предыдущем задании).
3. Теперь скопируйте столбцы D:E из «Лист1(2)» и вставьте их в столбцы D:E из «Лист1».

Как видите обе ячейки D2 и E2 были одинаково и одновременно скопированы, но ссылки в их формулах уже ведут себя по-разному. При копировании формул E2 значение не меняется. Все из-за того, что значения E2 из «Лист1(2)» получены путем перемещения и это уже считается для Excel иной способ присваивания адресов в формулах данной ячейки. Чтобы еще раз в этом убедиться, снова приведите табличку на «Лист1» в изначальный вид как на первом рисунке.

На этот раз в ячейку E2 скопируйте формулу из B2, а в ячейку D2 переместите туже самую формулу.

Программа нас информирует, что мы имеем ошибку «неправильная ссылка на ячейку» в E2. Но если бы мы не переносили, а просто скопировали формулы, то никаких ошибок не возникло.

Примечание. Быстро перемещать формулы можно с помощью перетаскивания ячейки мышкой удерживая левую клавишу после наведения указателя на рамку курсора выделенной ячейки. А выполнив это действие с нажатой клавишей CTRL, тогда формула скопируется.

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