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

Автор: Дмитрий
Опубликована: 02.04.2012

Популярность= 123 ( +102 / -21 )

Особенности копирования формул в Microsoft Excel с примерами.

Excel стал повсеместно популярным благодаря возможности расчета ячеек в таблицах через формулы. Формулы могу быть сколь угодно сложными, а исходные данные можно менять и итоговые ячейки при этом пересчитаются автоматически. Информацию о том, как вставлять формулы в ячейки Вы можете прочитать в статье «Формулы в Excel: создание и расчет».

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

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

Рассмотрим следующий пример:

Пусть в таблице имеется три столбца: «Слагаемое 1», «Слагаемое 2» и «Сумма». Столбцы слагаемых заполнены исходными данными, а столбец «Сумма» необходимо рассчитать на основе слагаемых. На рисунке выше в примере в ячейке D4 вставлена формула расчета суммы чисел в ячейках B4 и C4. (3+5=8). Требуется рассчитать суммы в строках с пятой по девятую аналогичным образом.

Вместо того, чтобы вставлять формулу суммы в каждую ячейку столбца D в рассматриваемых строках, можно скопировать формулу, находящуюся в ячейке D4, и поместить ее во все ячейки столбца в диапазоне D5-D9.

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

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

На выделенных пустых ячейках нажимаем правой клавишей мыши и в контекстном меню выбираем пункт «Вставить».

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

Распространение формул

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

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

Чтобы рассчитать суммы продаж за февраль и март можно скопировать в строке «Итого» формулы по столбцам, но проще распространить формулу за январь на февраль и март. Для этого необходимо выделить ячейку (B9), где уже стоит формула, и потянуть вправо мышкой за правый нижний угол этой ячейки (за меленький черный квадрат), а затем отпустить мышку над последним столбцом в строке «Итого» (в примере D9). Формула распространится на соседние столбцы!

То же самое можно сделать и для строк в колонке «За квартал». Выделите ячейку с формулой (E3) и потяните за правый нижний угол вниз, отпустив на последней строке (в примере E8).

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

Копирование формулы с неизменяемым значением (с абсолютными ссылками)

Иногда при копировании формулы в Экселе требуется, чтобы программа не изменяла одно из значений ячейки, а другое менялось по строке или столбцу. Для этого перед координатой ячейки, которую необходимо зафиксировать в формуле (Например, E2), нужно поставить знак $ (доллар) перед координатой строки и столбца: $E$2. Можно устанавливать фиксацию только по координате столбца $E2 или только строки E$2. Тогда при копировании формулы меняться будет только незафиксированная координата.

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

Для главного склада в ячейку D3 вводим формулу для расчета процента выручки от общей суммы «Итого»: (C3/$C$8)*100. Чтобы дробные значения не выглядели слишком длинно, помещаем имеющуюся формулу в формулу округления до второго знака после запятой: ОКРУГЛ((C3/$C$8)*100; 2).

У нас зафиксирована ячейка C8 по строке и столбцу, т.е. при копировании формулы координаты ячейки меняться не будут и где бы ни находилась формула, для расчета будет подставлено значение ячейки C8. Далее можно скопировать (или распространить) формулу по столбцу D до ячейки D8.

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

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

​Смотрите также​​вуаля!​ в большой таблице​Ставим галочку у слов​ мышку. Все, таблица​не копировать, а перенести​ только заполненные ячейки,​ квадратик в правом​ же формулу в​ можно не только​ функцию "Копировать".​Урок подготовлен для Вас​, выделите ячейку​ CTRL+V.​ и относительные ссылки​ абсолютные или относительные​ или в другой​Примечание:​Estella​ Excel нужную ячейку,​ «Создать копию». Выбираем​ заполнена.​

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

​Или, можно нажать​ командой сайта office-guru.ru​

​B3​Чтобы воспользоваться другими параметрами​

​ на ячейки ознакомьтесь​​ ссылки, которые вы​​ лист щелкните фигуру​​Мы стараемся как​​: Ну а ты​ диапазон, можно воспользоваться​

​ куда переместить скопированный​Так же можно​, то не копируем​ заполненные ячейки в​ Указатель мышки приобретает​ Таким образом, заполняем​

​ и складывать их,​ сочетание клавиш "Ctrl"​Источник: http://www.excel-easy.com/examples/copy-exact-formula.html​​, и нажмите​​ вставки, щелкните стрелку​​ со статьей Обзор​​ использовали.​ на ячейку, в​

​ можно оперативнее обеспечивать​ и так только​​ функцией "Поиск". Смотрите​​ лист (в эту​

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

​ таблицу формулами. Например,​​ вычитать, умножать, т.​ + "C".​Перевел: Антон Андронов​​CTRL+V​

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

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

​ числа скопируешь без​ статью "Поиск в​ или другую книгу,​ года, т.д. Таким​ контекстное меню "Вырезать"​ Например, у нас​ Нажимаем два раза​ копируем формулы вниз​ д. Подробнее, смотрите​Или, на закладке​

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

​ материалами на вашем​

​ формул. При вставке​

​ Excel".​ перед каким листом)​

​ же образом можно​

​ -> "Вставить". Формула​ такая таблица, нам​

​ подряд левой мышкой​

​ по столбцу. Для​ в статье "Суммирование​

​ "Главная" в разделе​

​Здесь рассмотрим,​ автоматически ссылаться на​

​и выберите один​

​ формулы при перемещении​ и вправо от​Чтобы вставить формулу с​ языке. Эта страница​ выбери специальная вставка​

​Т.е. в строке каждая​

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

​ нужно поставить даты​ на этот крестик.​ этого устанавливаем (пишем)​ в Excel" здесь.​ "Буфер обмена" нажать​

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

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

​ сохранением форматирования, выберите​ переведена автоматически, поэтому​ и там чисто​ ячейка высчитывается по​ и нажимаем «ОК».​ как по порядку,​ ячейки и перенесется​ в столбце А​ Формула скопируестя до​ формулу в первую​Если копируем числа​ на кнопку функции​

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

​ вариантов.​ место в той​ которые ссылки будут​

​ пункты​​ ее текст может​​ числа или что​​ формуле, нужно скопировать​ ​ Затем, у скопированного​ так и с​

​ в новое место.​ через одну пустую​ конца заполненного столбца.​ ячейку. Затем нажимаем​ с точками -​

​ "Копировать", предварительно выделив​ лист в Excel,​.​​Формулы​​ же или другой​​ обновляться согласно следующее:​​Главная​ содержать неточности и​

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

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

​Если вы не желаете​ ​Чтобы вставить только​ лист, ссылки на​Ссылка​ >​

Как скопировать точную формулу в Excel

​ грамматические ошибки. Для​ роде будет.​Divo​ название, переместить его​. Например: 5,10, 15,​ формул, чтобы ссылки​

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

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

​ не изменится —​$A$1 (абсолютный столбец и​или нажмите клавиши​ эта статья была​: Как скопировать несколько​ формулой.​

1. ​ изменить цвет ярлычка.​ интервал 2.​ менялись, описаны в​
2. ​ ячейку ниже.​​Получилось.​​ на правый нижний​​ в дату. Чтобы​​(без формулы).​
3. ​ облегчающих работу –​​ (без изменения ссылок​​Значения​ вне зависимости от​
4. ​ абсолютная строка)​​ CTRL+V.​​ вам полезна. Просим​​ ячеек с формулами​​Для Excel 2003:​

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

​Если нужно​​ статье "Как скопировать​​Протягиваем за заленый​​Внимание!​​ угол этой ячейки,​ этого избежать ,​

​Выделяем ячейки, которые​ это​
​ на ячейки), выполните​
​, чтобы вставить​

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

Как скопировать данные из Excel? (не формулы а сами числа).

​выбираем команду "Специальная вставка"​ "Копировать", достаточно нажать​A3​B3​

​ пункты​​ абсолютных и относительных​ все ссылки на​
​Копировать​ё=​: стрл+Ф​ формулы) в ячейках,​правой мышью нажимаем​ месяцами года.​ формулах Excel». Ссылка​ столбцам.​.​Можно​, выбираем — "Значение".​ правой кнопкой мыши​и​(выделите ячейку​Главная​
​ ссылок и выберите​ ячейки. Выбранная ячейка​или нажмите клавиши​Заменить:​ктрл+С​ читайте статью "Печать​

​ на ярлык листа.​​Справа всплывает подсказка,​
​ на статью в​
​Копирование в Excel через​
​Нам нужно скопировать​
​копировать формулы перетаскиванием​

​Затем, как обычно -​​ на выделенную ячейку,​B3​A3​ >​ необходимый вариант.​ ссылки могут измениться​ CTRL+C.​=​Альт+Таб​

Как скопировать несколько ячеек с формулами (Как скопировать несколько ячеек с формулами)

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

​ «Вставить». С помощью​​ диапзон и выбрать​) содержат одну и​
​, нажмите сочетание клавиш​Вставить​
​Дополнительные сведения о абсолютные​
​ с учетом типа​​На том же листе​
​Заменить​​Ктрл+М​
​Чтобы найти быстро​
​ выбираем «Переместить/скопировать».​
​ заполнили таблицу. Отпускаем​Если нужно​
​Можно копировать не​
​ F24. Наводим на​
​ одну и ту​​ функции "Специальная вставка"​
​ из контекстного меню​​ ту же формулу.​
​CTRL+C​

Проблема

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

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

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

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

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

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

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

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