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

Сергей Ломакин Редакция «Текстерры»

Копирование ячейки с формулой в Excel — это на самом деле не проблема. И тут бы можно было закончить заметку, но нет! Вы выделяете то, что нужно скопировать. И Excel послушно скопирует текст, формулу, формат, шрифт. Другими словам — все, что было в исходной ячейке. Но проблемы начинаются, когда вы захотите скопировать ячейку только с формулой или, например, содержимое ячейки без формулы. А нередко нужно скопировать и просто саму формулу в отрыве от ячейки. Часто это необходимо, например, для того, чтобы составлять таблицы для анализа различных данных и показателей (например, посещаемость страниц) в рамках SEO-продвижения сайта
Как поступить в таких случаях? – Читайте наше руководство!

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

Копировать / вставить

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

Копирование происходит через одноименную команду в меню, либо нажав сочетание клавиш Ctrl+C.

Функция копировать / вставить позволяет сразу же задействовать одну и ту же формулу к нескольким ячейкам. И это — без необходимости ручным способом прописывать формулу в первой, второй, третьей, четвертой ячейке.

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

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

Пример: в ячейке A1 нашей таблицы есть формула, которая ссылается на ячейку B1. Мы хотим вставить какую-либо формулу в ячейку C1. В таком случае, по умолчанию, формула в ячейке C1 будет ссылаться на ячейку D1.

Важно: существуют несколько методов копирования / вставки формул в Excel, включая метод перетаскивания, использование ручки заполнения или команды «Заполнить вниз» или «Заполнить вправо». Прежде чем решить, какой способ будет наиболее эффективным, необходимо протестировать каждый их них.

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

Напишите формулу с любыми данными и скопируйте ее. Нужно знать несколько правил:

• Если нужно оставить ячейку неизменной — используйте символ $. Пример: $C$2.
• Если нужно оставить неизменным только столбик — используйте тот же символ. Пример: $C2
• Если нужно оставить неизменным только строчку — используйте тот же символ. Пример: C$2.

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

Что происходит с ячейкой с формулой при копировании

Проделаем небольшой эксперимент:

1. Откроем таблицу.
2. Копируем формулу любым удобным способом.
3. Вставляем ее через инструмент «Вставить функцию».

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

Формула после вставки перешла в соседнюю ячейку

Мы рекомендуем два решения этой проблемы:

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

Итак, если новая формула переходит в соседнюю ячейку, то допустимо будет применить инструмент «Автофильтр»:

1. Наводим стрелку в угол ячейки (обязательно в нижний, справа).
2. Сразу же курсор примет вид символа +.
3. Кликаем по этому плюсу и зажимаем правую кнопку.
4. Не отпуская кнопки двигаем курсор вниз, вверх, либо — в любую сторону. Формула будет автоматически подтягиваться в новую ячейку.

Если требуется указать формулу неизменной, то нужно убедиться, что задействовано исходное значение. Сделайте это по такому шаблону:

Важно: независимо от того, куда вы переместите эту формулу, она постоянно будет ссылаться на A1 и A2.

Удаление $ перед 1 и 2 позволяет изменять строки, но не столбцы. Изменение $ перед A позволит менять столбцы, но не даст изменять строки.

Результат копирования: относительные и абсолютные формулы

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

Все виды формул в Excel можно разделить на два: относительные и абсолютные. Давайте смотреть в чем между ними разница.

Относительные формулы

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

Запомнить: при копировании относительной формулы все такие ссылки (напомним, что они ведут на ячейки) будут автоматом исправлены — чтобы соответствовать относительному положению формулы в ячейке назначения.

Пример: у нас имеется одна формула "=A1+B1" в ячейке C1. Мы копируем указанную формулу в ячейку C2, при этом – формула в ячейке C2 автоматически изменится на "=A2+B2".

Абсолютные формулы

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

Для создания абсолютной формулы, как вы уже поняли, применяется символ ($), который фиксирует ссылку на ячейку.

Пример: в нашей таблице имеется формула вида "=$A$1+$B$1" в ячейке C1. Мы хотим скопировать эту формулу в ячейку C2. Делаем это и видим: формула в ячейке C2 останется такой же, как и исходная формула "=$A$1+$B$1".

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

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

Решений у этой задачи может быть несколько. Рассмотрим каждый вариант.

Предварительные условия: допустим, у нас есть формула вида:

И нам нужно скопировать её вниз, чтобы получить:

…и так далее.

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

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

Вот еще несколько важных моментов, которые нужно знать:

• Если поставить символ $ перед столбцом (например, $D62), то столбец будет заблокирован, но строка может изменяться,
• Если поставить символ $ перед строкой (например, D$62), то строка будет заблокирована, но столбец может изменяться.
• Если поставить символ $ перед обеими строками, то они будут заблокированы целиком. Например: $D$73.

Эксперимент: попробуйте выделить C62 и сразу нажмите на клавишу F4 до тех пор, пока не увидите необходимое вам значение.

Альтернативный вариант 1

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

1. Перейдите к нужной ячейке.
2. Нажмите F2.
3. Нажмите CTRL+A (так вы выделите все содержимое ячейки).
4. Нажмите CTRL+C (так вы скопируете все содержимое).
5. Теперь кликните по какой-либо другой ячейке, куда нужно вставить формулу, и нажмите F2.
6. Нажмите CTRL + V. Будет вставлена абсолютно идентичная формула.
7. Теперь осталось просто перетащить формулу по всему диапазону.

Альтернативный вариант 2

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

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

Попробуйте потянуть эту формулу вниз или вправо. Вы увидите, что ссылка осталась такой, какой была изначально. Опять же — вы можете просто удалить символ доллара перед «C». В таком случае значение 62 будет сохранено. Если же вы сотрете символ доллара перед 62, то сохранится «C».

Альтернативный вариант 3

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

И вот еще один вариант решения этой задачи:

1. Измените первую формулу (т.е. исходную формулу), закрепив C62.
2. Направьте указатель мыши в ячейку, где находится первое вхождение формулы.
3. Нажмите F2 (редактировать)
4. Переместите курсор на C62.
5. Нажмите F4 (закрепить).

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

Задача практически решена. Осталось лишь только копировать значения в соответствующие ячейки.

Важно:

• Все ссылки, которые ведут на С62, — абсолютные
• Все ссылки, которые ведут на ячейки К, — относительные.

Сложные вопросы в работе с Excel

Я хочу скопировать / вставить идентичную формулу. Как это сделать?

Допустим: вы не хотите, чтобы ссылки на ячейки менялись. А возвращаться назад и вставлять все знаки $$ — это слишком долго или не подходит по иным причинам. Есть другие возможности.

Для одной формулы

Просто скопируйте ее из панели формул и вставьте в нужное место.

Для нескольких формул

1. Создайте пустой лист в рабочей книге.
2. Вернитесь к первому рабочему листу и скопируйте нужный вам диапазон ячеек.
3. На новом рабочем листе добавьте идентичный диапазон ячеек и вставьте его туда. Обратите внимание: все адреса ссылок в формулах остались прежними.
4. Перетащите вставленный диапазон в тот же диапазон на новом рабочем листе. Именно на этот лист, в конечном итоге, и нужно будет вставить диапазон (внимание: речь про первый рабочий лист). Обратите внимание: все наши адреса ссылок остались прежними (внутри формул).
5. Скопируйте этот диапазон формул на новый рабочий лист.
6. Перейдите в нужный диапазон назначения на первом рабочем листе (это должен быть тот же диапазон адресов, из которого скопировали на новый рабочий лист) и примените команду вставки.

Обратите внимание: все ранее используемые адреса ссылок остались прежними (внутри формул).

Почему функция суммирования не работает?

Не видя вашего рабочего листа, сложно сделать на 100% корректный вывод и дать совет. Но наиболее частой причиной некорректной работы СУММ можно назвать наличие в ячейке текста (а не цифр).

Дело в том, что функция SUM (СУММ) всегда игнорирует любые текстовые ячейки.

Обычно текстовые числа легко обнаружить: они выровнены по левому краю и имеют зеленый маркер в углу. Проблема в том, что некоторые пользователи привыкли выравнивать текст по правому краю. Но Excel – умная программа: даже когда номер выровнен по правому краю, зеленый маркер также отображается. Некоторые пользователи идут еще дальше: они выбирают опцию «Игнорировать эту ошибку». В итоге — текст показывается как обычные числа (поэтому всегда обещайте внимание на зеленый уголок в ячейке):

Не нужно использовать конструкции типа =B7+B6+B5+B4+B3+B2. Вы получите ошибки. Но эту формулу можно задействовать для обнаружения текста, который маскируется под цифры.

Вот как решить проблему чисел, сохраненных как текст:

1. Выберите диапазон чисел (не включая функцию SUM или СУММ).
2. Используйте сочетание горячих клавиш Alt+D+E+F. Это приведет к запуску инструмента Text to Columns. В итоге текстовые числа автоматически переведутся в настоящие. Посмотрите на пример ниже:

Это наиболее вероятная причина некорректной работы функции суммирования. Но бывают и другие.

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

Рабочую книгу очень просто перевести в режим вычислений. Если коллега перевел одну рабочую книгу в режим ручного расчета, а затем вы открываете эту книгу с пятью собственными книгами в ручном режиме, то и все пять книг автоматически уйдут в режим ручного расчета. Если указанная проблема для вас актуальна ежедневно или через день, то лучше исправить эту ситуацию: сделайте правый клик по кнопке «Параметры быстрого доступа» и добавьте ее в панель инструментов быстрого доступа.

Два чекбокса Manual (либо, в противоположном случае — Automatic) будут выводиться на панели инструментов. Соответственно, вы сможете очень быстро заметить, когда редактор находится в ручном режиме и наоборот.

Можно ли сделать фильтрацию дубликатов значений из уже настроенного диапазона?

Да, можно. В Excel есть специальная формула для этого:

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

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

• Если вернулся «duplicate» — значение в этой ячейке уже было в диапазоне.
• Если вернулся «unique» — значение в этой ячейке еще не было в диапазоне.

Также можно применять опцию «Удалить дубликаты» на вкладке «Данные». Этот инструмент автоматически очистит все дубли из указанного диапазона, сохранив лишь уникальные значения.

Заключение: краткая инструкция

Помните важное:

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

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

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

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

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

Вы можете воспользоваться, как минимум, двумя способами:

1. Нажать F2 и скопировать текст формулы вручную и вставить его в нужное место.
2. Скопировать ячейку, перейти в ячейку (куда вы хотите вставить формулу) и просто вставить её. Формула будет вставлена с соответствующими корректировками ссылок. Либо — можно скопировать ячейку с формулой и перейти в новую ячейку, воспользоваться сочетанием клавиш Alt+A+E+S и выделить формулу (это специальная вставка).

Если же нужно копировать формулу из «Эксель» в другое приложение, — нажмите клавишы Alt+. Вы увидите длинный список всех формул. Из него вы уже сможете скопировать любой необходимый вариант.

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

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

1. Нажмите клавишу F2. Выделите формулу в ячейке и задействуйте комбинацию гор. клавиш Ctrl+C (или используйте функцию копирования, вызываемую через меню).
2. Выделите формулу на панели формул и нажмите Ctrl+C или кликните по иконке копирования в меню (находится она на панели инструментов).

Теперь вы можете вставить формулу как-текст в любую другую программу.

Помните важное: при вставке формулы внутри «Эксель» автоматически обновятся ссылки.

Как вставить одну и ту же формулу несколько раз

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

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

Возможные варианты.

• Перетащить или заполнить – ссылки на формулы будут меняться в каждой ячейке.
• Введите «статическую» формулу (например, $A$1), затем – перетащите её или заполните.

Как копировать в Excel вообще ячейку без формулы

Способов скопировать такую ячейку несколько:

• Выделите формулу на панели формул и нажмите Ctrl+C или кликните по иконке копирования в меню (панель инструментов).
• Перейдите в строку меню вверху и найдите выпадающее меню (речь о меню редактирования, конечно), а затем — просто скопируйте.
• Делаем правый клик по ячейке и сразу же в выпадающем меню находим функцию копирования.
• Выделите ячейку, а затем, когда содержимое ячейки будет выведено в новом окне вверху, скопируйте ее содержимое.

Если вы хотите скопировать формулу только в ячейку выше / ниже / правее / левее ячейки, в которой уже находится формула, — используйте перетаскивание. Для этого выделите курсором один угол ячейки и переместите ячейки через него. В результате — ячейки будут заполнены формулой.

Как использовать специальную вставку при фильтрации листов

Здесь все не так сложно:

1. Примените нужные вам фильтры.
2. Маркируйте цветным выделением столбик, содержащие данные.
3. Удалите все фильтры. Для этого используйте сочетание горячих клавиш Alt+D, затем нажмите клавишу F, и после – клавишу S.
4. Теперь нужно сделать сортировку столбика по цвету (его мы задействовали в шаге до этого).
5. Спокойно применяем специальную вставку. Данные, которые расположены между фильтрами, никак не пострадают.
6. Уберите маркирование цветом, которое мы сделали в предыдущих шагах. Удалите раскраску из первого шага.
7. Снова примените фильтр.

Как копировать и вставлять текст и формулы в таблицу Excel

Самый простой способ — кликните по нужной ячейке и тут же нажмите клавишу F2, затем — просто вставьте текст. Либо — кликните по строке для ввода формулы (Fx), вставьте ее туда и нажмите Enter.

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

Если вы копируете «из Excel в Excel», то можно вставлять специальные значения или сами формулы.

Как копировать в Excel только формулу

1. Сделайте выделение ячейки с формулой, которую нужно будет скопировать.
2. Примените команду «Копировать» любым удобным способом (через меню или сочетанием горячих клавиш).
3. Если нужно сохранить формулу с форматированием, то на вкладке «Буфер обмена» — используйте опцию «В виде вкладки».
4. Если же нужно сохранить только формулу (без форматирования), то используйте вкладку Paste / Вставка (в главном меню) и затем выберите «Формулы».

Как переместить формулу

1. Сделайте выделение ячейки с формулой, которую нужно будет переместить или сдвинуть.
2. Используйте команду «Вырезать» любым удобным способом (через меню или вызовите её сочетанием горячих клавиш).
3. Вы можете задействовать и перетаскивание (drag-and-drop), но в этом случае все текущие данные будут заменены.
4. Если нужно сохранить формулу с форматированием, то на вкладке «Буфер обмена» используйте опцию «В виде вкладки».
5. Если же нужно сохранить только формулу (без форматирования), то используйте кнопку Paste (в главном меню) и затем выберите раздел «Формулы».

Читайте также:

17 фишек Excel, с которыми будет проще запустить рекламу

Умные таблицы в Excel: что это, как сделать и как все вернуть

20+ функций Excel, которые ускорят работу в несколько раз