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

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

Здравствуйте!
Есть строка, в ней простые формулы.
Например в ячейке G27 формула «=C23».
Копируем ячейку в G31 как формулу и получаем «=C27».
Как скопировать, что бы формула осталась «=C23»?? т.е. абсолютно неизменно.
Поясню зачем мне это. Просто это большая строка от G27 до W27. Я ее хочу скопировать, далее заменить С на B. Т.е. важны как раз цифры. Уже не раз сталкивался и не могу найти ответа. Очень бесит переписывать всю строку руками.
Спасибо!

• Вопрос задан более трёх лет назад
• 1552 просмотра

Комментировать
Решения вопроса 1

Александр @ForestAndGarden
Совершенствовать среду обитания

Сделайте из =C23 =C$23 . Готово. Была относительная ссылка, стала смешанная. Ещё есть абсолютные: =$C$23

Ответ написан более трёх лет назад
Нравится 3 3 комментария
Setitle @Setitle Автор вопроса

Спасибо. Идея хорошая. Но это подходит только для заранее готового шаблона. Мне же приходится копировать разные куски.. Везде не поставишь $. Конкретно в данном случае придется редактировать каждую ячейку. Проще в каждую зайти и скопировать саму формулу и вставить в саму ячейку куда надо. Что я и сделал 🙁
Еще момент! Если вы присмотритесь, то увидите 21, 22, 23.. это говорит о том, что когда я изначально делал таблицу лет 5 назад, я тянул формулу столбцом. Т.е. речи о $ не может идти в изначальном варианте.

Александр @ForestAndGarden
Тогда нужно активно использовать F2, Ctrl+A, Ctrl+C, Ctrl+V.
Setitle @Setitle Автор вопроса

Нет. Все проще. Ваш комментарий пометил как ответ. А написать не успел.
1. Выделяем диапазон, меняем разом все C на C$ с помощью CTRL+F (расширенные параметры+поиск по формулам в диапазоне) В итоге получим то, что говорили вы «С$23 и т.д.»
2. Копируем нужный диапазон.
3. В скопированном диапазоне делаем уже, что надо. В моем случае замена всех С на B так же через поиск.
Спасибо!! Что-то я не догадался, что цифры можно закрепить. Так же F2 тоже нужная клавиша.

Ответы на вопрос 0
Ваш ответ на вопрос

Войдите, чтобы написать ответ

• Google Sheets

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

• 1 подписчик
• час назад
• 4 просмотра

• Google Sheets

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

• 2 подписчика
• 12 часов назад
• 39 просмотров

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.

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

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

Команда контент-менеджеров wikiHow тщательно следит за работой редакторов, чтобы гарантировать соответствие каждой статьи нашим высоким стандартам качества.

Количество источников, использованных в этой статье: 8. Вы найдете их список внизу страницы.

Количество просмотров этой статьи: 136 874.

В этой статье:

В Excel можно быстро скопировать формулу во множество ячеек одной строки или одного столбца, но результат не всегда будет таким, какой планировался. Если вы не добились желаемого результата или в ячейках появились сообщения #REF и /DIV0, почитайте, что такое абсолютные и относительные адреса ячеек, чтобы выяснить причину ошибки. Помните, что вносить изменения во всех ячейках таблицы, которая состоит из 5000 строк, не придется — для этого есть методы, которые позволяют автоматически обновить формулу в определенной ячейке или скопировать ее без изменения значений.

Метод 1 из 4:

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

Введите формулу в одной ячейке. Сначала введите знак равенства (=), а затем введите нужную функцию или математическую операцию. Например, вам нужно сложить значения столбцов A и B:

Таблица

Столбец A	Столбец B	Столбец C
строка 1	10	9	= A1 + B1
строка 2	20	8
строка 3	30	7
строка 4	40	6

Нажмите клавишу «Enter», чтобы вычислить результат по введенной формуле. В ячейке, где вы ввели формулу, отобразится результат (в нашем примере вычисленная сумма 19), но формула будет храниться в электронной таблице.

Таблица

Столбец A	Столбец B	Столбец C
строка 1	10	9	19
строка 2	20	8
строка 3	30	7
строка 4	40	6

Нажмите на маркер в нижнем правом углу ячейки. Переместите указатель в правый нижний угол ячейки с формулой; указатель превратится в символ «+». [1] X Источник информации

Удерживайте значок «+» и перетащите его по нужным ячейкам столбца или строки. Удерживайте кнопку мыши нажатой, а затем перетащите символ «+» вниз по столбцу или вправо по строке, чтобы выделить нужные ячейки. Введенная формула будет автоматически скопирована в выделенные ячейки. Так как здесь присутствует относительный адрес ячейки, адреса ячеек (в скопированных формулах) соответственно изменятся. В нашем примере (показаны формулы, которые автоматически поменялись, и вычисленные значения):

Таблица

Столбец A	Столбец B	Столбец C
строка 1	10	9	= A1 + B1
строка 2	20	8	= A2 + B2
строка 3	30	7	= A3 + B3
строка 4	40	6	= A4 + B4

Таблица

Столбец A	Столбец B	Столбец C
строка 1	10	9	19
строка 2	20	8	28
строка 3	30	7	37
строка 4	40	6	46

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

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

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

1. Выделите ячейку, содержащую нужную формулу.
2. Для копирования формулы выберите команду Правка — Копировать или нажмите комбинацию клавиш COMMAND CTRL + С.
3. Выделите ячейку, в которую нужно скопировать формулу.
4. Выберите команду Правка — Вставить или нажмите клавиши COMMAND CTRL +V. Формула размещается в новой ячейке.

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

1. Выделите ячейку, содержащую нужную формулу.
2. Наведите указатель мыши на нижний правый угол выделенной границы ячейки и удерживайте нажатой кнопку мыши, пока указатель не примет форму перекрестия.
3. Не отпуская кнопку мыши, протащите указатель вниз или вправо по ячейкам, в которые нужно скопировать формулу.
4. Отпустите кнопку мыши. Формула будет скопирована в ячейки и автоматически скорректирована.

Если автоматическая корректировка текста и значений не требуется, то при перетаскивании указателя мыши удерживайте нажатой клавишу COMMAND CTRL . Формулы, однако, всегда корректируются соответствующим образом.