Почему не копируются формулы в excel
Перейти к содержимому

Почему не копируются формулы в excel

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

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

В зависимости от того копируется или перемещается формула в 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, тогда формула скопируется.

Быстрое копирование формул.

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

покупка

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

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

  • Метод 1: с помощью функции замены
  • Метод 2: преобразование формулы в текст
  • Метод 3: путем преобразования в абсолютные ссылки
  • Метод 4: с помощью функции точного копирования

ad exact copy formulas 3

Копировать формулу, не меняя ссылки на ячейки по функции «Заменить»

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

1. Выберите ячейки формулы, которые вы скопируете, и нажмите Главная > Найти и выбрать > Замените, или нажмите ярлыки CTRL + H для открытия Найти и выбрать диалоговое окно.

2. Нажмите Замените кнопку в Найти то, что поле ввода « = ”, И в Заменить поле ввода « # »Или любые другие знаки, которые отличаются от ваших формул, и нажмите Заменить все кнопку.
По сути, это предотвратит превращение ссылок в ссылки. Например, « = A1 * B1 «Становится» # A1 * B1 », И вы можете перемещать его, не меняя автоматически Excel ссылки на ячейки в текущем листе.

3. А теперь все » = »В выбранных формулах заменяются на« # ». Появляется диалоговое окно, в котором показано, сколько замен было произведено. Пожалуйста, закройте его. Смотрите скриншот выше:
И формулы в диапазоне будут изменены на текстовые строки. Смотрите скриншоты:

4. Скопируйте и вставьте формулы в нужное место на текущем листе.

5. Выберите оба измененных диапазона, а затем выполните шаг 2 в обратном порядке. Щелкните Главная> Найти и выбрать >Заменить… или нажмите ярлыки CTRL + H, но на этот раз введите « # «В поле» Найти «и» = »В поле« Заменить на »и нажмите Заменить все. Затем формулы были скопированы и вставлены в другое место без изменения ссылок на ячейки. Смотрите скриншот:

Скопируйте формулу, не меняя ссылки на ячейки, преобразовав формулу в текст

Вышеупомянутый метод состоит в том, чтобы изменить формулу на текст с заменой = на #. На самом деле, Kutools for Excel предоставлять такие утилиты Преобразовать формулу в текст и Преобразовать текст в формулу. И вы можете преобразовывать формулы в текст и копировать их в другие места, а затем легко восстанавливать этот текст в формулу.

Kutools for Excel — Содержит более 300 основных инструментов для Excel. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Скачать сейчас!

Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Выберите ячейки формулы, которые вы скопируете, и нажмите Кутулс > Содержание > Преобразовать формулу в текст. Смотрите скриншот:

2. Теперь выбранные формулы преобразованы в текст. Скопируйте их и вставьте в целевой диапазон.

3. И затем вы можете восстановить текстовые строки в формулу, выбрав текстовые строки и нажав Кутулс > Содержание > Преобразовать текст в формулу. Смотрите скриншот:

Kutools for Excel — Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Get It Now

Скопируйте формулу без изменения ссылок на ячейки, преобразовав в абсолютные ссылки

Формулы изменяются после копирования в результате относительных ссылок. Следовательно, мы можем применить Kutools for ExcelАвтора Преобразовать ссылки утилита для изменения ссылок на ячейки на абсолютные, чтобы предотвратить их изменение после копирования в Excel.

Kutools for Excel — Содержит более 300 основных инструментов для Excel. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Скачать сейчас!

Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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

2. В открывшемся диалоговом окне «Преобразование ссылок на формулы» установите флажок К абсолютному и нажмите Ok кнопка. Смотрите скриншот:

3. Скопируйте формулы и вставьте в целевой диапазон.

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

Kutools for Excel — Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Get It Now

Скопируйте формулу, не меняя ссылки на ее ячейки, Kutools for Excel

Есть ли более простой способ скопировать формулу, не меняя ссылки на ячейки так быстро и удобно? Фактически, Kutools for Excel может помочь вам копировать формулы без быстрого изменения ссылок на ячейки.

Kutools for Excel — Содержит более 300 основных инструментов для Excel. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Скачать сейчас!

Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Выберите ячейки формулы, которые вы скопируете, и нажмите Кутулс > Точная копия.

2, Во-первых Точная копия формулы диалоговое окно, щелкните OK. Во втором диалоговом окне «Копирование точной формулы» укажите первую ячейку целевого диапазона и щелкните значок OK кнопка. Смотрите скриншот:

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

Kutools for Excel — Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Get It Now

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

Kutools for Excel: Более 300 удобных инструментов у вас под рукой! Начните свой 30 Бесплатная пробная версия на день без каких-либо ограничений сегодня. Скачать

Почему не копируются формулы в excel

Введите имя пользователя, чьи записи вы хотите найти

LiveJournal

Войти

Нет аккаунта? Зарегистрироваться

Авторизуясь в LiveJournal с помощью стороннего сервиса вы принимаете условия Пользовательского соглашения LiveJournal

Microsoft Office [entries|archive|friends|userinfo]

Microsoft Office

[ userinfo | livejournal userinfo ]
[ archive | journal archive ]
[ Links: | Тренажёр правильной осанки ТАНЦУЮЩИЙ КИПАРИС™ ]
[ ############ | Видеокурс от центра В.И.Дикуля ]
[ ############ | Cок TAHITIAN NONI® для укрепления иммунитета ]
[ ############ | Гравитационные ботинки ]

Microsoft Office

Имеется 2 файла Excel, в каждом из них есть 2 листа с одинаковыми названиями, скажем, «ABC» и «DEF». Формула на листе «ABC» ссылается на значения ячеек листа «DEF». При простом копировании (Ctrl+C/Ctrl+V) такой формулы из первого файла во второй, сслыки в ней остаются на ячейки листаа «DEF» первого файла.

Каким образом средствами Excel можно скопировать такую формулу из одного файла в другой так, чтобы она ссылалась на ячейки страницы «DEF» уже второго файла? Решение с «прятанием» ссылок в ДВССЫЛ не предлагать.

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

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

При копировании формул можно управлять изменением адресов ячеек или ссылок. Если перед всеми атрибутами адреса ячейки поставить символ “$” (например, $A$1), то это будет абсолютная ссылка, которая при копировании формулы не изменится. Изменятся только те атрибуты адреса ячейки, перед которыми не стоит символ “$”, т.е. относительные ссылки. Для быстрой установки символов “$” в ссылке ее необходимо выделить в формуле и нажать клавишу F4.

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

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

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

Установите курсор в ячейку с формулой;

Подведите указатель мыши к маркеру заполнения. Изображение указателя изменяется на черный крестик;

Нажмите левую кнопку мыши и, удерживая ее нажатой, перемещайте курсор до нужного места. Для завершения распространения формулы отпустите кнопку.

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *