Копирование ячейки с формулой в Excel — это на самом деле не проблема. И тут бы можно было закончить заметку, но нет! Вы выделяете то, что нужно скопировать. И Excel послушно скопирует текст, формулу, формат, шрифт. Другими словам — все, что было в исходной ячейке. Но проблемы начинаются, когда вы захотите скопировать ячейку только с формулой или, например, содержимое ячейки без формулы. А нередко нужно скопировать и просто саму формулу в отрыве от ячейки. Часто это необходимо, например, для того, чтобы составлять таблицы для анализа различных данных и показателей (например, посещаемость страниц) в рамках SEO-продвижения сайта
Как поступить в таких случаях? – Читайте наше руководство!
Excel — удобный инструмент для работы маркетолога. Его можно использовать, например, для составления отчетов по таргетированной рекламе или контент-планов по SMM.
Копировать / вставить
Функцию копирования / вставки в Excel можно успешно использовать для любых формул. Вы можете применять ее для копирования формулы из одной ячейки или диапазона ячеек, а затем — для вставки формулы в другую ячейку или диапазон ячеек.
Копирование происходит через одноименную команду в меню, либо нажав сочетание клавиш Ctrl+C.
Функция копировать / вставить позволяет сразу же задействовать одну и ту же формулу к нескольким ячейкам. И это — без необходимости ручным способом прописывать формулу в первой, второй, третьей, четвертой ячейке.
Как меняются ссылки при копировании формул
При вставке формулы Excel автоматически исправит ссылки на ячейки в формуле, чтобы отразить новое местоположение формулы.
Пример: в ячейке A1 нашей таблицы есть формула, которая ссылается на ячейку B1. Мы хотим вставить какую-либо формулу в ячейку C1. В таком случае, по умолчанию, формула в ячейке C1 будет ссылаться на ячейку D1.
Важно: существуют несколько методов копирования / вставки формул в Excel, включая метод перетаскивания, использование ручки заполнения или команды «Заполнить вниз» или «Заполнить вправо». Прежде чем решить, какой способ будет наиболее эффективным, необходимо протестировать каждый их них.
Как скопировать и вставить формулу в Excel при изменении ссылки на одну ячейку?
Напишите формулу с любыми данными и скопируйте ее. Нужно знать несколько правил:
- Если нужно оставить ячейку неизменной — используйте символ $. Пример: $C$2.
- Если нужно оставить неизменным только столбик — используйте тот же символ. Пример: $C2
- Если нужно оставить неизменным только строчку — используйте тот же символ. Пример: C$2.
Не используйте символ $ в изменяемых ячейках! Просто перетащите или скопируйте такую вставку.
Что происходит с ячейкой с формулой при копировании
Проделаем небольшой эксперимент:
- Откроем таблицу.
- Копируем формулу любым удобным способом.
- Вставляем ее через инструмент «Вставить функцию».
![Самый простой способ вставить функцию в таблице Вставка функции](/upload/medialibrary/a27/2z2kqosq5fdsk1x4vl9a6osfu3w3ohay/1.webp)
Да, мы работаем с формулой в текущей ячейке, и она формально вставилась. Далее можно добавить необходимые функции и увидеть подсказки по заданию входных значений… Но что-что пошло не так. А именно — формула изменила свое местоположение, и теперь она не работает в том виде, в котором нужно нам. Как решить эту проблему — читайте ниже
Формула после вставки перешла в соседнюю ячейку
Мы рекомендуем два решения этой проблемы:
- Поменять эту формулу на ту, что была до этого. Другими словами, заменить ее на исходный вариант.
- Задействовать автоматическую фильтрацию.
Итак, если новая формула переходит в соседнюю ячейку, то допустимо будет применить инструмент «Автофильтр»:
- Наводим стрелку в угол ячейки (обязательно в нижний, справа).
- Сразу же курсор примет вид символа +.
- Кликаем по этому плюсу и зажимаем правую кнопку.
- Не отпуская кнопки двигаем курсор вниз, вверх, либо — в любую сторону. Формула будет автоматически подтягиваться в новую ячейку.
![Результат протягивания формулы Вставка функции](/upload/medialibrary/a90/ma0w5ctttnjjkw08qdp88a2ejesbziyu/2.webp)
Если требуется указать формулу неизменной, то нужно убедиться, что задействовано исходное значение. Сделайте это по такому шаблону:
![Шаблон для неизменной формулы Шаблон для неизменной формулы](/upload/medialibrary/324/bpsncunrzgdsr3odn3q14pa3e48ycjny/3.webp)
Важно: независимо от того, куда вы переместите эту формулу, она постоянно будет ссылаться на 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, фиксируя постоянную ссылку на одну ячейку?
Решений у этой задачи может быть несколько. Рассмотрим каждый вариант.
Предварительные условия: допустим, у нас есть формула вида:
![Пример формулы Пример формулы](/upload/medialibrary/a0b/pz6akm19sg8u1jckrtb357n4ajo9x2ux/4.webp)
И нам нужно скопировать её вниз, чтобы получить:
![Пример формулы Пример формулы](/upload/medialibrary/a0f/549gbun11bc91p2zg2yoay2pb971nr2x/5.webp)
…и так далее.
Важно знать: по умолчанию ячейки внутри формул Excel записываются как относительная ссылка. Другими словами, когда мы копируем формулу в другую ячейку, ссылка на ячейку в формуле автоматом поменяется.
Если вы не хотите, чтобы ячейка, на которую ссылается формула, менялась, вы можете обозначить ее как абсолютную ссылку — с помощью символа $.
Вот еще несколько важных моментов, которые нужно знать:
- Если поставить символ $ перед столбцом (например, $D62), то столбец будет заблокирован, но строка может изменяться,
- Если поставить символ $ перед строкой (например, D$62), то строка будет заблокирована, но столбец может изменяться.
- Если поставить символ $ перед обеими строками, то они будут заблокированы целиком. Например: $D$73.
Эксперимент: попробуйте выделить C62 и сразу нажмите на клавишу F4 до тех пор, пока не увидите необходимое вам значение.
Альтернативный вариант 1
Если вы не хотите ставить абсолютные ссылки на ячейки, есть альтернативный вариант: его смысл в том, чтобы скопировать текст формулы при помощи соответствующей функции и вставить его в ячейку.
- Перейдите к нужной ячейке.
- Нажмите F2.
- Нажмите CTRL+A (так вы выделите все содержимое ячейки).
- Нажмите CTRL+C (так вы скопируете все содержимое).
- Теперь кликните по какой-либо другой ячейке, куда нужно вставить формулу, и нажмите F2.
- Нажмите CTRL + V. Будет вставлена абсолютно идентичная формула.
- Теперь осталось просто перетащить формулу по всему диапазону.
Альтернативный вариант 2
Существует еще один вариант решения проблемы. Чтобы воспользоваться им, понадобится немного подправить формулу. Напомним, изначально она выглядела вот так:
![Пример формулы Пример формулы](/upload/medialibrary/0be/lsswicaf2hxxhqdwwbudbsxc94ussb37/6.webp)
Скопировать / вставить формулу, сохранив постоянную ссылку на одну ячейку, гораздо проще, если мы слегка изменим формулу
![Вариант изменения формулы Вариант изменения формулы](/upload/medialibrary/225/xv4wvr911r4ymt8y3cj1f73vav88whup/7.webp)
Попробуйте потянуть эту формулу вниз или вправо. Вы увидите, что ссылка осталась такой, какой была изначально. Опять же — вы можете просто удалить символ доллара перед «C». В таком случае значение 62 будет сохранено. Если же вы сотрете символ доллара перед 62, то сохранится «C».
Альтернативный вариант 3
Вы помните о том, что мы хотим скопировать формулу вниз? Другими словами, так, чтобы по диапазону ячеек все шло подряд. Вот так:
![Формула для диапазона ячеек Формула для диапазона ячеек](/upload/medialibrary/66e/qlxdkiws78j343i1gfk7xhxpnfp2912y/8.webp)
И вот еще один вариант решения этой задачи:
- Измените первую формулу (т.е. исходную формулу), закрепив C62.
- Направьте указатель мыши в ячейку, где находится первое вхождение формулы.
- Нажмите F2 (редактировать)
- Переместите курсор на C62.
- Нажмите F4 (закрепить).
Теперь первоначальная формула у нас будет выглядеть следующим образом:
![Измененная формула Измененная формула](/upload/medialibrary/313/zf8pemvmhk31n7lojxmrbxz71hsdi1va/9.webp)
Задача практически решена. Осталось лишь только копировать значения в соответствующие ячейки.
Важно:
- Все ссылки, которые ведут на С62, — абсолютные
- Все ссылки, которые ведут на ячейки К, — относительные.
Сложные вопросы в работе с Excel
Я хочу скопировать / вставить идентичную формулу. Как это сделать?
Допустим: вы не хотите, чтобы ссылки на ячейки менялись. А возвращаться назад и вставлять все знаки $$ — это слишком долго или не подходит по иным причинам. Есть другие возможности.
Для одной формулы
Просто скопируйте ее из панели формул и вставьте в нужное место.
Для нескольких формул
- Создайте пустой лист в рабочей книге.
- Вернитесь к первому рабочему листу и скопируйте нужный вам диапазон ячеек.
- На новом рабочем листе добавьте идентичный диапазон ячеек и вставьте его туда. Обратите внимание: все адреса ссылок в формулах остались прежними.
- Перетащите вставленный диапазон в тот же диапазон на новом рабочем листе. Именно на этот лист, в конечном итоге, и нужно будет вставить диапазон (внимание: речь про первый рабочий лист). Обратите внимание: все наши адреса ссылок остались прежними (внутри формул).
- Скопируйте этот диапазон формул на новый рабочий лист.
- Перейдите в нужный диапазон назначения на первом рабочем листе (это должен быть тот же диапазон адресов, из которого скопировали на новый рабочий лист) и примените команду вставки.
Обратите внимание: все ранее используемые адреса ссылок остались прежними (внутри формул).
Почему функция суммирования не работает?
Не видя вашего рабочего листа, сложно сделать на 100% корректный вывод и дать совет. Но наиболее частой причиной некорректной работы СУММ можно назвать наличие в ячейке текста (а не цифр).
Дело в том, что функция SUM (СУММ) всегда игнорирует любые текстовые ячейки.
Обычно текстовые числа легко обнаружить: они выровнены по левому краю и имеют зеленый маркер в углу. Проблема в том, что некоторые пользователи привыкли выравнивать текст по правому краю. Но Excel – умная программа: даже когда номер выровнен по правому краю, зеленый маркер также отображается. Некоторые пользователи идут еще дальше: они выбирают опцию «Игнорировать эту ошибку». В итоге — текст показывается как обычные числа (поэтому всегда обещайте внимание на зеленый уголок в ячейке):
![Всегда обращайте внимание на зеленый уголок в ячейке Источник ошибки](/upload/medialibrary/126/j0mat2bw3336zf09yykcw3ejg7m9325m/10.webp)
Не нужно использовать конструкции типа =B7+B6+B5+B4+B3+B2. Вы получите ошибки. Но эту формулу можно задействовать для обнаружения текста, который маскируется под цифры.
Вот как решить проблему чисел, сохраненных как текст:
- Выберите диапазон чисел (не включая функцию SUM или СУММ).
- Используйте сочетание горячих клавиш Alt+D+E+F. Это приведет к запуску инструмента Text to Columns. В итоге текстовые числа автоматически переведутся в настоящие. Посмотрите на пример ниже:
![Функция SUM также заработала. Ура! Используйте это сочетание горячих клавиш](/upload/medialibrary/d84/yu100cgqov2h2dvlthxjrcclnqvum2km/11.webp)
Это наиболее вероятная причина некорректной работы функции суммирования. Но бывают и другие.
Например, возможно, что в рабочей книге установлен режим ручного расчета. Проверьте, исправляет ли он вводимые формулы. Если да, — откройте меню «Формулы», перейдите в «Параметры вычислений» и выберите «Автоматически».
Рабочую книгу очень просто перевести в режим вычислений. Если коллега перевел одну рабочую книгу в режим ручного расчета, а затем вы открываете эту книгу с пятью собственными книгами в ручном режиме, то и все пять книг автоматически уйдут в режим ручного расчета. Если указанная проблема для вас актуальна ежедневно или через день, то лучше исправить эту ситуацию: сделайте правый клик по кнопке «Параметры быстрого доступа» и добавьте ее в панель инструментов быстрого доступа.
![Добавляем инструмент на панель быстрого доступа Параметры вычислений](/upload/medialibrary/4b5/qfrly7zixreeo2u8nr5w16hw0r1vzq0p/12.webp)
Два чекбокса Manual (либо, в противоположном случае — Automatic) будут выводиться на панели инструментов. Соответственно, вы сможете очень быстро заметить, когда редактор находится в ручном режиме и наоборот.
Можно ли сделать фильтрацию дубликатов значений из уже настроенного диапазона?
Да, можно. В Excel есть специальная формула для этого:
![Специальная формула для фильтрации дубликатов Специальная формула для фильтрации дубликатов](/upload/medialibrary/a01/sft8sayrke19mxc1yql4vvt4lbiedcpg/13.webp)
Используйте эту формулу, чтобы провести фильтрацию дубликатов значений из заданного диапазона ячеек.
Вышеуказанную формулу допустимо задействовать в новом столбце рядом с диапазоном ячеек, который хотите проверить.
- Если вернулся «duplicate» — значение в этой ячейке уже было в диапазоне.
- Если вернулся «unique» — значение в этой ячейке еще не было в диапазоне.
Также можно применять опцию «Удалить дубликаты» на вкладке «Данные». Этот инструмент автоматически очистит все дубли из указанного диапазона, сохранив лишь уникальные значения.
Заключение: краткая инструкция
Помните важное:
- При перемещении формул: все ссылки на сами ячейки остаются статическими, то есть неизменными.
- При копировании формулы: все относительные ссылки, ведущие на ячейки, становятся динамическими, то есть изменяются.
Давайте резюмируем самые частые сценарии копирования ячеек с привязкой к формуле кратко.
Как копировать в Excel ячейку с формулой
Выделите ячейку и воспользуйтесь сочетанием горячих клавиш Control+С, или используйте правую кнопку мыши для выделения. Вставьте ячейку с формулой туда, куда вам необходимо (при помощи команды Control+V или через меню «Вставить»).
Как копировать текст формулы
Вы можете воспользоваться, как минимум, двумя способами:
- Нажать F2 и скопировать текст формулы вручную и вставить его в нужное место.
- Скопировать ячейку, перейти в ячейку (куда вы хотите вставить формулу) и просто вставить её. Формула будет вставлена с соответствующими корректировками ссылок. Либо — можно скопировать ячейку с формулой и перейти в новую ячейку, воспользоваться сочетанием клавиш Alt+A+E+S и выделить формулу (это специальная вставка).
Если же нужно копировать формулу из «Эксель» в другое приложение, — нажмите клавишы Alt+. Вы увидите длинный список всех формул. Из него вы уже сможете скопировать любой необходимый вариант.
Как копировать текст формулы без результата
Чтобы скопировать текст формулы, а не ее результат, используйте один из двух приемов. Сначала выделите необходимую вам ячейку (обязательно с формулой), а затем:
- Нажмите клавишу F2. Выделите формулу в ячейке и задействуйте комбинацию гор. клавиш Ctrl+C (или используйте функцию копирования, вызываемую через меню).
- Выделите формулу на панели формул и нажмите Ctrl+C или кликните по иконке копирования в меню (находится она на панели инструментов).
Теперь вы можете вставить формулу как-текст в любую другую программу.
Помните важное: при вставке формулы внутри «Эксель» автоматически обновятся ссылки.
Как вставить одну и ту же формулу несколько раз
Да, вы можете вставить одну и ту же формулу несколько раз. Для этого можно использовать функцию автозаполнения ячеек.
Как скопировать формулы во все строки в Excel
Возможные варианты.
- Перетащить или заполнить – ссылки на формулы будут меняться в каждой ячейке.
- Введите «статическую» формулу (например, $A$1), затем – перетащите её или заполните.
Как копировать в Excel вообще ячейку без формулы
Способов скопировать такую ячейку несколько:
- Выделите формулу на панели формул и нажмите Ctrl+C или кликните по иконке копирования в меню (панель инструментов).
- Перейдите в строку меню вверху и найдите выпадающее меню (речь о меню редактирования, конечно), а затем — просто скопируйте.
- Делаем правый клик по ячейке и сразу же в выпадающем меню находим функцию копирования.
- Выделите ячейку, а затем, когда содержимое ячейки будет выведено в новом окне вверху, скопируйте ее содержимое.
Если вы хотите скопировать формулу только в ячейку выше / ниже / правее / левее ячейки, в которой уже находится формула, — используйте перетаскивание. Для этого выделите курсором один угол ячейки и переместите ячейки через него. В результате — ячейки будут заполнены формулой.
Как использовать специальную вставку при фильтрации листов
Здесь все не так сложно:
- Примените нужные вам фильтры.
- Маркируйте цветным выделением столбик, содержащие данные.
- Удалите все фильтры. Для этого используйте сочетание горячих клавиш Alt+D, затем нажмите клавишу F, и после – клавишу S.
- Теперь нужно сделать сортировку столбика по цвету (его мы задействовали в шаге до этого).
- Спокойно применяем специальную вставку. Данные, которые расположены между фильтрами, никак не пострадают.
- Уберите маркирование цветом, которое мы сделали в предыдущих шагах. Удалите раскраску из первого шага.
- Снова примените фильтр.
Как копировать и вставлять текст и формулы в таблицу Excel
Самый простой способ — кликните по нужной ячейке и тут же нажмите клавишу F2, затем — просто вставьте текст. Либо — кликните по строке для ввода формулы (Fx), вставьте ее туда и нажмите Enter.
Вы также можете скопировать и вставить специальные значения.
Если вы копируете «из Excel в Excel», то можно вставлять специальные значения или сами формулы.
Как копировать в Excel только формулу
- Сделайте выделение ячейки с формулой, которую нужно будет скопировать.
- Примените команду «Копировать» любым удобным способом (через меню или сочетанием горячих клавиш).
- Если нужно сохранить формулу с форматированием, то на вкладке «Буфер обмена» — используйте опцию «В виде вкладки».
- Если же нужно сохранить только формулу (без форматирования), то используйте вкладку Paste / Вставка (в главном меню) и затем выберите «Формулы».
Как переместить формулу
- Сделайте выделение ячейки с формулой, которую нужно будет переместить или сдвинуть.
- Используйте команду «Вырезать» любым удобным способом (через меню или вызовите её сочетанием горячих клавиш).
- Вы можете задействовать и перетаскивание (drag-and-drop), но в этом случае все текущие данные будут заменены.
- Если нужно сохранить формулу с форматированием, то на вкладке «Буфер обмена» используйте опцию «В виде вкладки».
- Если же нужно сохранить только формулу (без форматирования), то используйте кнопку Paste (в главном меню) и затем выберите раздел «Формулы».
Читайте также:
17 фишек Excel, с которыми будет проще запустить рекламу
Умные таблицы в Excel: что это, как сделать и как все вернуть