Как перенести формулу в другую ячейку в excel – Иллюстрированный самоучитель по Microsoft Excel › Работа с функциями и формулами › Перемещение и копирование формул [страница — 40] | Самоучители по офисным пакетам

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

Введение формул в ячейки Excel для расчетов данных

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

В отдельных случаях для пересчета формул следует воспользоваться специальным инструментом, но это рассмотрим в отдельном разделе посвященным вычислениям по формулам.

Заполнение листов Excel формулами

Для выполнения вычислений и расчетов следует записать формулу в ячейку Excel. В таблице из предыдущего урока (которая отображена ниже на картинке) необходимо посчитать суму, надлежащую к выплате учитывая 12% премиальных к ежемесячному окладу. Как в Excel вводить формулы в ячейки для проведения подобных расчетов?

Задание 1. В ячейке F2 введите следующую формулу следующим образом: =D2+D2*E2. После ввода нажмите «Enter».

Задание 2. В ячейке F2 введите только знак «=». После чего сделайте щелчок по ячейке D2, дальше нажмите «+», потом еще раз щелчок по D2, дальше введите «*», и щелчок по ячейке E2. После нажатия клавиши «Enter» получаем аналогичный результат.

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

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

Все формулы пересчитываются динамически. Как только изменяется содержание ячейки с входящими данными для расчетов, формулы автоматически пересчитывают их и сразу выдают новый результат вычислений. Например, если сейчас изменить ежемесячный оклад в ячейке D2 и нажать «Enter», то по адресу E2 сразу появится новый результат.

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

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

Задание 1. Перейдите в ячейку F3 и нажмите комбинацию клавиш CTRL+D. Таким образом, автоматически скопируется формула, которая находится в ячейке выше (F2). Так Excel позволяет скопировать формулу на весь столбец. Также сделайте и в ячейке F4.

Задание 2. Удалите формулы в ячейках F3:F4 (выделите диапазон и нажмите клавишу «delete»). Далее выделите диапазон ячеек F2:F4. И нажмите комбинацию клавиш CTRL+D. Как видите, это еще более эффективный способ заполнить целую колонку ячеек формулой из F2.

Задание 3. Удалите формулы в диапазоне F3:F4. Сделайте активной ячейку F2, переместив на нее курсор. Далее наведите курсор мышки на точку в нижнем правом углу прямоугольного курсора. Курсор мышки изменит свой внешний вид на знак плюс «+». Тогда удерживая левую клавишу мыши, проведите курсор вниз еще на 2 ячейки, так чтобы выделить весь диапазон F2:F4.

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

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

Эти способы более удобны для определенных ситуаций, которые мы рассмотрим на следующих уроках.

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

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

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

Если ячейка находится на другом листе, перейдите на него и выберите эту ячейку.

Чтобы быстро вставить формулу вместе с форматированием, нажмите +V. Кроме того, можно щелкнуть стрелку рядом с кнопкой Вставить:

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

Формулы — Вставка только формул и без форматирования исходной ячейки.

Формулы и форматы чисел — Вставка только формул и форматов чисел (например: процентный формат, формат валюты и т. д.).

Сохранить исходное форматирование — Чтобы вставить формулу, номера форматирования, шрифт, размер шрифта, границы и заливки исходной ячейки.

Вставить значения — для исключения формулу и вставить их результатами.

Другие доступные параметры, которые могут пригодиться:

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

Сохранить ширину столбцов оригинала — Чтобы вставить формулу, форматирование чисел, шрифта, размер шрифта, заливки, границы и ширины исходной ячейки.

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

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

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

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

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

В строке формул выделите ссылку, которую нужно изменить.

Чтобы переключиться с абсолютного на относительный тип ссылки или обратно, нажмите клавишу F4 и выберите нужный вариант.

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

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

Щелкните ячейку с формулой, которую хотите перенести.

Если ячейка находится на другом листе, перейдите на него и выберите эту ячейку.

Чтобы вставить формулу с сохранением форматирования, на вкладке Главная выберите команду Вставить или нажмите клавишу + V.

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

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

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

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

3 методика:Копирование формулы в несколько ячеекТочное копирование формулыИспользование относительных и абсолютных ссылок на ячейки

Метод 1 из 3: Копирование формулы в несколько ячеек

Метод 2 из 3: Точное копирование формулы

Метод 3 из 3: Использование относительных и абсолютных ссылок на ячейки

Советы

• Если вы копируете формулу в новую ячейку и видите зеленый треугольник, Excel обнаружил возможную ошибку. В этом случае проверьте правильность введения формулы.[9]
• Если вы случайно замените символ «=» на «?» или «*» (в методе «точное копирование формулы»), поиск символов «?» или «*» не приведет к ожидаемым результатам. Исправьте это, выполнив поиск символов «~?» или «~*».[10]
• Выделите ячейку и нажмите Ctrl + ‘ (апостроф), чтобы скопировать в эту ячейку формулу, расположенную в ячейке сверху.[11]

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

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

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

Выберите пункты Главная > Копировать или нажмите клавиши CTRL+C.

Если ячейка находится на другом листе, перейдите на него и выберите эту ячейку.

Чтобы вставить формулу с сохранением форматирования, выберите пункты Главная > Вставить или нажмите клавиши CTRL+V.

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

Проверьте ссылки на ячейки для нового расположения.

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

Также можно использовать сочетание клавиш Ctrl + X, чтобы переместить формулу.

Способы точного копирования диапазона формул в Excel

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

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

Вариант первый. Копировать не ячейку, а данные в ячейке.

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

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

Вариант второй. С использованием программы-посредника.

Для этого случая понадобиться любой простой текстовый редактор, например, «Блокнот», а также переключение листа Excel в режим отображения формул (сочетание клавиш «Ctr+`(тильда)«, или вкладка «Формулы«, группа «Зависимости формул«, команда «Показать формулы«).

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

Еще возможные варианты. Просто для ознакомления

На самом деле, существуют еще способы, но они уступают представленным выше.

Можно еще сделать макрос, но это случай, если вы ежедневно занимаетесь таким переносом.

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

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

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

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

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

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

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

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

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

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

Конечно же есть вариант с использованием макросов, но код макроса нужно где-то взять.

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

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

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

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

На примере ниже ячейка A3 содержит формулу, которая суммирует значения в ячейках A1 и A2.

Скопируйте эту формулу в ячейку B3 (выделите ячейку A3, нажмите сочетание клавиш CTRL+C, выделите ячейку B3, и нажмите CTRL+V), и формула будет автоматически ссылаться на значения в столбце B.

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

Теперь обе ячейки (A3 и B3) содержат одну и ту же формулу.

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

Сделать ссылки в формулах абсолютными. Читайте об этом статью «Относительные и абсолютные ссылки в Excel».

Копировать формулу в Excel можно, временно заменив знак «равно» на знак, не встречающийся в формулах. Например, решетка (#) или два амперсанда (&9amp;). Выделяем ячейки с формулами. Меняем через функцию «Найти и выделить» знак равно на #. Как работать с функцией «Найти и выделить», смотрите в статье «Поиск в Excel» тут.

Копируем эти формулы. Затем, снова, через функцию «Найти и выделить» меняем в формулах решетку (#) на знак «равно». Получилось так.

Формулы скопированы в столбец Е с неизменёнными ссылками на ячейки столбцов А и В.

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

В Excel можно считать при совпадении в ячейках поределенных условий. Смотрите, как написать формулу с условиями счета, в статье «Функция «ЕСЛИ» в Excel с несколькими условиями сравнения».

Если вы хотите просто вставить значения с ячеек, последовательно нажимайте клавиши Я, М и З, удерживая при этом клавишу Alt, и в конце нажмите клавишу ввода. Это бывает необходимо, когда вам нужно избавиться от форматирования и работать только с данными.

Умное» копирование формул в MS Excel | Для бухгалтерів бюджетних установ

1. ЛЕВСИМВ(Строка; Количество_символов) – выводит заданное количество символов с левого края. Например, =ЛЕВСИМВ(А1;10) выведет 10 первых символов строки в ячейке А1 . Функция имеет 2 обязательных аргумента – Строка-источник и количество выводимых символов;

Копировать формулу в Excel можно, временно заменив знак «равно» на знак, не встречающийся в формулах. Например, решетка (#) или два амперсанда (&9amp;). Выделяем ячейки с формулами. Меняем через функцию «Найти и выделить» знак равно на #. Как работать с функцией «Найти и выделить», смотрите в статье «Поиск в Excel» тут.

«Умное» копирование формул в MS Excel

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

Теперь относительно данных для расчета. Они берутся из стандартных документов финансовой отчетности — « Форма 1 », « Форма 2 », « Форма 3 ». Данные в документах показаны реальные. Единственное, что я изменил, — это убрал названия предприятий, заменил их на ничего не значащую аббревиатуру. Причина такого действия, думаю, понятна.

Наша задача — заполнить лист « СрТбл » расчетными формулами для одного предприятия и попытаться скопировать эти формулы на всю таблицу. Приступим.

1. Открываем документ, переходим на лист « СрТбл » с итоговым отчетом.

3. Вводим в нее формулу « =СНЭМф2!C55 ». Для этого щелкаем мышью на ярлычке листа « СНЭМф2 » (документ « Форма 2 » предприятия « СНЭМ »). Затем щелкаем на ячейке « C55 » и нажимаем « Enter ».

5. Оставаясь на ячейке « B5 », копируем формулу в буфер обмена (комбинация « Ctrl+C »).

7. Вставляем содержимое из буфера обмена (комбинация « Ctrl+V »). Пока все верно. В ячейках «С5:А5» мы получили правильные ссылки на ячейки листа « СНЭМф2 » (рис. 3).

8. Переходим на ячейку « B6 ». Здесь должна быть формула « =СНЭМф2!C38/СНЭМф1!C63 » со ссылками на два листа с документами « Форма 1 » и « Форма 2 ». Разумеется, такую формулу тоже удобно вводить, щелкая мышью на ячейках рабочих листов.

9. Далее копируем формулу в ячейки « C6:F6 ». И таким же способом заполняем расчетную часть, двигаясь вниз до конца итогового отчета.

При заполнении формулами отчета, изображенного на рис. 1, возникает как минимум два вопроса.

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

В наших формулах все ссылки используют обычную адресацию ячеек. Когда мы копировали эти формулы в пределах одного предприятия, все было хорошо. Такой способ адресации был нам очень кстати. Например, формула « =СНЭМф2! C 55 » из ячейки « B5 », будучи скопированной в блок « C5:F5 », породила результаты « =СНЭМф2! D 55 », « =СНЭМф2! E 55 », « =СНЭМф2! G 55 ». И это правильно.

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

Этот прием позволяет в определенной степени справиться с проблемой номер « 2 », т. е. уйти от неоправданной корректировки адресов при копировании формул. Делаем так:

1. Открываем документ, переходим на рабочий лист « СрТбл ».

3. Нажимаем клавишу « F2 ». Станет доступным для редактирования содержимое ячейки.

4. Выделяем текст формулы и копируем его в буфер обмена (комбинация « Ctrl+C »).

5. Нажимаем клавишу « Esc » (возвращаемся на рабочий лист).

6. Становимся на ячейку « G5 » и вставляем в нее содержимое буфера обмена (комбинация « Ctrl+V »). Теперь в ячейке « G5 » находится точная копия формулы из ячейки « B5 », — « =СНЭМф2!C55 ». Заметьте, что адреса колонок Excel не изменил, так как мы скопировали не фрагмент рабочего листа в виде ячейки, а ее содержимое, т. е. текст.

7. Копируем формулу из « G5 » в ячейки « H5:K5 » обычным способом. В данном случае Excel исправит номера колонок, и это как раз то, что нам нужно!

8. Повторяем аналогичные действия для оставшихся строк итоговой таблицы.

С адресацией колонок мы справились. Остается откорректировать названия листов. Разумеется, что вручную мы этого делать не будем. Сейчас самое время вспомнить, что формулы — это обычный текст. А это значит, что работать с ними можно инструментом поиска и замены. Делаем так:

3. Нажимаем клавишу « F2 » (входим в режим редактирования ячейки).

4. В тексте формулы выделяем название листа. Это будет часть текста, которая выделена полужирным начертанием: « = СНЭМф2 !C55 ».

5. Копируем этот текст в буфер обмена (комбинация « Ctrl+C »).

6. Нажимаем клавишу « Esc », чтобы вернуться на рабочий лист.

8. Вызываем инструмент « Поиск и замена ». Удобнее всего это сделать по комбинации « Ctrl+H ». Откроется окно « Найти и заменить », изображенное на рис. 4.

9. В этом окне становимся на поле « Найти: » и вставляем содержимое из буфера (комбинация « Ctrl+V »). В результате в этом поле должен появиться текст « СНЭМф2 ».

10. Переходим на поле « Заменить на: » и вводим новое название листа. В нашем примере — это « КВСЗф2 ».

11. Нажимаем кнопку « Заменить все » (рис. 4). Через пару секунд появится сообщение о том, что мы откорректировали в документе 245 формул (рис. 5).

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

13. Повторяем аналогичные действия для заполнения оставшейся части итоговой таблицы по всем предприятиям.

Копирование формул через вспомогательный рабочий лист

1. Открываем документ, ставим указатель мыши на ярлычок листа « СрТбл ».

2. Удерживая клавишу « Ctrl », перетаскиваем его в сторону в пределах области ярлыков MS Excel (вправо или влево — без разницы).

3. Отпускаем клавишу « Ctrl ». В книге появится новый лист с именем « СрТбл (2) ». Переходим на этот лист. Смотрим на формулы — они здесь точно такие же, как и на листе « СрТбл ».

5. Вырезаем этот блок в буфер обмена (можно воспользоваться комбинацией « Ctrl+X »).

7. Становимся на ячейку « G1 », — с колонки « G » мы собираемся вставить новые формулы.

8. Нажимаем « Ctrl+V » — вставляем содержимое из буфера обмена. В колонках « G:K » появятся те же значения, что и в блоке « B:F ».

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

А дальше остается действовать по известной схеме — изменить названия листов. Делаем так.

2. Нажимаем « Ctrl+H ». Откроется окно « Найти и заменить », как на рис. 3.

3. В этом окне становимся в поле « Найти: », вводим название листа « СНЭМф2 ».

6. Аналогичным образом заменяем названия всех листов, которые фигурируют в формулах блока « G:K ».

7. Повторяем копирование формул для остальных предприятий итогового отчета.

Вставка или Ctrl+V, пожалуй, самый эффективный инструмент доступный нам. Но как хорошо вы владеете им? Знаете ли вы, что есть как минимум 14 различных способов вставки данных в листах Ecxel? Удивлены? Тогда читаем этот пост, чтобы стать пэйст-мастером.

Как скопировать текст в excel — IT Новости

• Если вы копируете формулу в новую ячейку и видите зеленый треугольник, Excel обнаружил возможную ошибку. В этом случае проверьте правильность введения формулы.[9]
• Если вы случайно замените символ «=» на «?» или «*» (в методе «точное копирование формулы»), поиск символов «?» или «*» не приведет к ожидаемым результатам. Исправьте это, выполнив поиск символов «~?» или «~*».[10]
• Выделите ячейку и нажмите Ctrl + ‘ (апостроф), чтобы скопировать в эту ячейку формулу, расположенную в ячейке сверху.[11]

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