Как в эксель скопировать формулу для других ячеек: Как скопировать точную формулу в Excel

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

В процессе работы с Microsoft Excel у пользователей возникает необходимость внесения дополнительных данных. Для этого необходимо добавлять или копировать строки с формулами.

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

Как добавить новую строку с формулами в Excel?

Пускай мы добавили формулы в одну строку или в несколько. Теперь нам нужно скопировать все строки с формулами. Как это сделать при добавлении новой строки?

1. Выделяем ту строку, которая содержит формулы. В нашей ситуации это строка под номером 3. Нам необходимо, чтобы остальные строки содержали в себе такие же формулы.
2. Нажимаем на выделенную область правой кнопкой мышки. Из контекстного меню нужно выбрать опцию «Копировать» (или жмем CTRL+C).
3. После этого следует выделить следующую одну (или несколько) нижнюю строку, куда будет вставлена новая с формулами.
4. Щелкнуть по заголовку выделенной строки правой кнопкой мышки и выбрать опцию вставить скопированные ячейки (или нажать комбинацию клавиш CTRL+SHIFT+«=»).

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



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

Часто при копировании относительные формулы (т.е. те, в которых нет знака «$») «подставляют» другие значения. А что делать, если вы не хотите изменять значение формул?

Можно сделать все ссылки в формулах абсолютными. Такие формулы будут копироваться неизменно, без изменения данных. Чтобы сделать ссылки абсолютными, добавляем к ним знак «$».

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

На пример в такой таблице ссылка на наценку ($B$1) не будет изменятся при копировании на новых строках, так как она является абсолютной, а не относительной:

Но в некоторых больших и сложных таблицах слишком много формул, которые нужно скопировать и переместить. И чтобы во всех ссылках проставить знак «$» нужно потратить много времени.

Для решения данной задачи можно воспользоваться хитрым способом. До копирования заменить все знаки «=» на символ «#».

Для «активации» работы формул используется знак «равно» (или «=»). Чтобы деактивировать их на время копирования поменяем = на #. А после копирования вернем все на свои места. Это позволит нам обойти автоматическую смену адресов в относительных ссылках при копировании.

1. Нажимаем Ctrl+H (вызываем окно «Главная»-«Найти и выделить»-«Заменить»).
2. Необходимо в поле «Найти» указать символ =, а в поле «Заменить на» символ #. И жмем «Заменить все».
3. После этого вы можете скопировать необходимую строку, а в ее формулах не изменятся адреса ссылок на ячейки, так как они временно деактивированные.
4. Снова жмем комбинацию горячих клавиш «Ctrl+H», но теперь указываем в полях обратные значения: найти «#» и заменить на «=». Система вернет все на свои места

В результате при копировании во всех строках ссылки формул остались неизменными.

Настройка формул для всех строк с помощью формул в столбцах

Применить к

Smartsheet

• Pro
• Business
• Enterprise

Возможности

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

PLANS

• Smartsheet
• Pro
• Business
• Enterprise

Права доступа

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

Этот Справочная статья есть в следующих учебных курсах:

Учебный курс

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

Формулы в столбцах позволяют применять одинаковые вычисления и выражения ко всем строкам в таблице. Формулы в столбцах также автоматически применяются ко всем новым строкам, добавляемым в таблицу. Например, можно делать следующее:

• отслеживать расхождения в датах запланированного и фактического завершения всех задач проекта;
• автоматически подставлять информацию о назначенном ресурсе (например, его должности, отделе и непосредственном руководителе) с помощью функции INDEX;
• автоматически рассчитывать итоговые значения для запросов цен на основе информации, предоставленной с помощью формы соответствующего запроса.

Создание и редактирование формул в столбцах

Чтобы создать формулу в столбце, сделайте следующее:

1. Введите формулу в любую ячейку требуемого столбца.

   ПРИМЕЧАНИЕ. Формулы в столбцах не должны ссылаться на ячейки или диапазоны с конкретными номерами строк (например, по абсолютным ссылкам, ссылкам на определённые ячейки или диапазоны, охватывающие часть столбца). Вместо этого используйте нотацию @row, ссылки на столбцы и межтабличные ссылки.
   

    

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

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

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

Ограничения, связанные с формулами в столбцах

 

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

Тип ссылки	Пример	Поддерживается?
@row	[Имя задачи]@row	✓
Ссылки на столбцы	[Имя задачи]:[Имя задачи]	✓
Межтабличные ссылки	{Столбец с именем задачи}	✓
Ссылки на ячейки	[Имя задачи]1	X
Абсолютные ссылки	[Имя задачи]$1	X
Диапазоны, охватывающие часть столбца	[Имя задачи]1:[Имя задачи]12	X

Связанный контент

Справочная статья

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

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

Справочная статья

Создание эффективных формул с помощью @cell и @row

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

Справочная статья

Формулы: ссылки на данные из других таблиц

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

Вам помогла эта статья?

ДаНет

Скопируйте и вставьте формулы в Numbers на iPad

Numbers

Искать в этом руководстве

Numbers Руководство пользователя для iPad

• Добро пожаловать
• • Введение в числа
  • Знакомство с изображениями, диаграммами и другими объектами
  • Создать электронную таблицу
  • Открытые таблицы
  • Предотвращение случайного редактирования
  • Персонализируйте шаблоны
  • Использовать листы
  • Отменить или повторить изменения
  • Сохраните таблицу
  • Найти электронную таблицу
  • Удалить таблицу
  • Распечатать электронную таблицу
  • Изменить фон листа
  • Копировать текст и объекты между приложениями
  • Настроить панель инструментов
  • Основы сенсорного экрана
  • Используйте Apple Pencil с цифрами
  • • Используйте VoiceOver для создания электронной таблицы
    • Используйте VoiceOver для создания формул и автозаполнения ячеек
• • Добавить или удалить таблицу
  • Выбор ячеек, строк и столбцов
  • • Добавление или удаление строк и столбцов
    • Переместить строки и столбцы
    • Изменение размера строк и столбцов
  • Объединить или разъединить ячейки
  • • Изменение внешнего вида текста таблицы
    • Показать, скрыть или изменить заголовок таблицы
    • Изменить цвета сетки таблицы
    • Используйте стили таблиц
  • Изменение размера, перемещение или блокировка таблицы
• • • Добавить текст в таблицы
    • Ввод данных с помощью форм
    • Добавляйте объекты в ячейки
    • Добавить информацию об акциях
    • Добавьте дату, время или продолжительность
    • Автозаполнение ячеек
    • Копировать, перемещать или удалять содержимое ячейки
    • Создание снимка таблицы
  • • Форматирование дат, валюты и т. д.
    • Добавьте флажки и другие элементы управления в ячейки
    • Форматирование таблиц для двунаправленного текста
  • Перенос текста по размеру ячейки
  • Выделите ячейки
  • Алфавитизация или сортировка данных в таблице
  • Данные фильтра
  • • Знакомство с категориями
    • Добавляйте, редактируйте или удаляйте категории
    • Изменить группы категорий
    • Добавьте расчеты для суммирования групповых данных
  • • Введение в сводные таблицы
    • Создать сводную таблицу
    • Добавить и упорядочить данные сводной таблицы
    • Изменение способа сортировки, группировки данных сводной таблицы и т. д.
    • Обновить сводную таблицу
    • Просмотр исходных данных для значения сводной таблицы
• • Быстро рассчитать сумму, среднее значение и т. д.
  • Используйте клавиатуру формул
  • Вычислять значения, используя данные в ячейках таблицы
  • Скопируйте и вставьте формулы
  • Используйте справку по формулам и функциям
• • • Выберите данные для построения диаграммы
    • Добавляйте столбчатые, столбчатые, линейные, площадные, круговые, кольцевые и лепестковые диаграммы.
    • Добавьте точечные и пузырьковые диаграммы
    • Интерактивные графики
    • Удалить диаграмму
    • Изменение типа диаграммы
  • Изменить данные диаграммы
  • Перемещение и изменение размера диаграмм
  • • Изменение внешнего вида рядов данных
    • Добавьте легенду, линии сетки и другие маркировки
    • Изменение внешнего вида текста и меток диаграммы
    • Добавление границы и фона диаграммы
    • Используйте стили диаграммы
• • Выберите текст и поместите точку вставки
  • • Добавить текст
    • Скопируйте и вставьте текст
    • • Форматирование электронной таблицы для другого языка
      • Используйте фонетические справочники
      • Использовать двунаправленный текст
      • Используйте вертикальный текст
  • Изменить внешний вид текста
  • Используйте стили текста
  • • Изменить заглавные буквы текста
    • Добавить буквицы
    • Лигатуры
    • Сделать символы надстрочными или подстрочными
    • Автоматически форматировать дроби
    • Формат дефисов и кавычек
    • Форматирование китайского, японского или корейского текста
  • • Установить позиции табуляции
    • Выровнять текст
    • Форматировать текст в столбцы
    • Отрегулируйте межстрочный интервал
  • Форматировать списки
  • Добавляйте и редактируйте уравнения
  • Добавить ссылки
• • • Добавить изображение
    • Добавить галерею изображений
    • Редактировать изображение
  • • Добавить и изменить фигуру
    • Объединяйте или разбивайте фигуры
    • Сохранение фигуры в библиотеке фигур
    • Добавление и выравнивание текста внутри фигуры
  • Добавьте линии и стрелки
  • Добавляйте и редактируйте рисунки
  • • Добавить видео и аудио
    • Запись видео и аудио
    • Редактировать видео и аудио
  • Установите формат фильма
• • • Размещение и выравнивание объектов
    • Используйте направляющие для выравнивания
    • Поместите объекты в текстовое поле или фигуру
    • Слой, группировка и блокировка объектов
  • Изменить прозрачность объекта
  • Заливка объектов цветом или изображением
  • Добавление границы к объекту или листу
  • Добавить подпись или заголовок
  • Добавьте отражение или тень
  • Используйте стили объектов
  • Изменение размера, поворот и отражение объектов
• • Поиск слов
  • Найти и заменить текст
  • Заменить текст автоматически
  • Проверять орфографию
  • Установите имя автора для комментариев
  • Выделите текст
  • Добавить и распечатать комментарии
• • Отправить таблицу
  • Введение в сотрудничество
  • Приглашайте других к сотрудничеству
  • Совместная работа над общей электронной таблицей
  • Просматривайте последние действия в общей электронной таблице
  • Изменение настроек общей электронной таблицы
  • Прекратить совместное использование электронной таблицы
  • Общие папки и совместная работа
  • Используйте Box для совместной работы
• • Используйте iCloud с номерами
  • Импорт файла Excel или текстового файла
  • Экспорт в Excel или другой формат файла
  • Восстановить более раннюю версию электронной таблицы
  • Переместить электронную таблицу
  • Защита электронной таблицы паролем
  • Создание пользовательских шаблонов и управление ими
  • • Перенос электронных таблиц с помощью AirDrop
    • Передача электронных таблиц с помощью Handoff
    • Перенос электронных таблиц с помощью Finder
• • Горячие клавиши
  • Символы сочетания клавиш
• Авторские права

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

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

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

1. Коснитесь ячейки с формулой, которую хотите скопировать, коснитесь ее еще раз, затем коснитесь «Копировать» в контекстном меню.

2. Коснитесь ячейки, в которую вы хотите вставить формулу или результат, коснитесь ее еще раз, затем коснитесь «Вставить».

3. Чтобы сохранить формулу, коснитесь «Вставить формулы» или, чтобы вставить только результат, коснитесь «Вставить значения».

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

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

2. Нажмите справа от формулы в редакторе формул над клавиатурой, затем нажмите «Выбрать».

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

4. Нажмите .

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

6. Коснитесь в редакторе формул места, куда вы хотите вставить частичную формулу, коснитесь «Вставить», затем коснитесь .

Максимальное количество символов: 250

Пожалуйста, не указывайте личную информацию в своем комментарии.

Максимальное количество символов — 250.

Спасибо за отзыв.

Относительные и абсолютные ссылки на ячейки

Урок 4: Относительные и абсолютные ссылки на ячейки

/en/excelformulas/complex-formulas/content/

Введение

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

Дополнительно: загрузите наш пример файла для этого урока.

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

Относительные ссылки

По умолчанию все ссылки на ячейки являются относительными ссылками . При копировании в несколько ячеек они изменяются в зависимости от относительного положения строк и столбцов. Например, если скопировать формулу =A1+B1 из строки 1 в строку 2, формула примет вид =A2+B2. Относительные ссылки особенно удобны, когда вам нужно повторить одинаковый расчет для нескольких строк или столбцов.

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

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

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку D2 .
2. Введите формулу для расчета требуемого значения. В нашем примере мы введем =B2*C2 .
3. Нажмите Введите на клавиатуре. Формула будет вычислено , и результат будет отображаться в ячейке.
4. Найдите маркер заполнения в правом нижнем углу нужной ячейки. В нашем примере мы найдем дескриптор заполнения для ячейки 9.0495 Д2 .
5. Нажмите, удерживайте и перетащите маркер заполнения по ячейкам, которые вы хотите заполнить. В нашем примере мы выберем ячейки D3:D12 .
6. Отпустите кнопку мыши. Формула будет скопирована в выбранные ячейки с относительными ссылками , и значения будут рассчитаны в каждой ячейке.

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

Абсолютные ссылки

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

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

В большинстве формул вы будете использовать относительный ( A2 ) и абсолютный ( $A$2 ) форматы. Смешанные ссылки используются реже.

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

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

В нашем примере мы будем использовать 7,5% ставку налога с продаж в ячейке E1 для расчета налога с продаж для всех товаров в столбце D . Нам нужно будет использовать абсолютную ссылку на ячейку $E$1 в нашей формуле. Поскольку в каждой формуле используется одна и та же налоговая ставка, мы хотим, чтобы эта ссылка оставалась постоянной, когда формула копируется и заполняется другими ячейками в столбце D.

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку Д3 .
2. Введите формулу для расчета требуемого значения. В нашем примере мы введем = (B3*C3)*$E$1 .
3. Нажмите Введите на клавиатуре. Формула рассчитает, и результат отобразится в ячейке.
4. Найдите маркер заполнения в правом нижнем углу нужной ячейки. В нашем примере мы найдем дескриптор заполнения для ячейки D3 .
5. Нажмите, удерживайте и перетащите маркер заполнения над ячейками, которые вы хотите заполнить, ячейки D4:D13 в нашем примере.
6. Отпустите кнопку мыши. Формула будет скопирована в выбранные ячейки с абсолютной ссылкой , и значения будут рассчитаны в каждой ячейке.

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

Не забудьте включить знак доллара ( $) всякий раз, когда вы делаете абсолютную ссылку на несколько ячеек. В приведенном ниже примере знаки доллара были опущены. Это привело к тому, что электронная таблица интерпретировала ее как относительную ссылку, что дало неверный результат при копировании в другие ячейки.

Использование ссылок на ячейки с несколькими рабочими листами

Большинство программ для работы с электронными таблицами позволяют ссылаться на любую ячейку на любом рабочем листе , что может быть особенно полезно, если вы хотите сослаться на конкретное значение с одного рабочего листа на другой. Для этого вам просто нужно начать ссылку на ячейку с 9Рабочий лист 0495 имя , за которым следует восклицательный знак точка ( ! ). Например, если вы хотите сослаться на ячейку A1 на Sheet1 , ее ссылкой на ячейку будет Sheet1!A1 .

Обратите внимание: если имя рабочего листа содержит пробел , вам нужно будет заключить одинарных кавычек ( ‘ ‘ ) вокруг имени. Например, если вы хотите сослаться на ячейку A1 на листе с именем Бюджет на июль ссылка на его ячейку будет ‘Бюджет на июль’!A1 .

Для ссылки на ячейки между рабочими листами:

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

1. Найдите ячейку, на которую вы хотите сослаться, и обратите внимание на ее рабочий лист. В нашем примере мы хотим сослаться на ячейку 9.0495 E14 на рабочем листе заказа меню .
2. Перейдите к нужному рабочему листу . В нашем примере мы выберем рабочий лист Счет-фактура общественного питания .
3. Появится выбранный рабочий лист .
4. Найдите и выберите ячейку , в которой должно отображаться значение. В нашем примере мы выберем ячейку B2 .
5. Введите знак равенства (=) , имя листа и цифру восклицательный знак ( ! ) и адрес ячейки . В нашем примере мы введем =’Порядок меню’!E14 .
6. Нажмите Введите на клавиатуре. Появится значение указанной ячейки. Если значение ячейки E14 изменится на рабочем листе «Заказ меню», оно будет автоматически обновлено на рабочем листе «Счет-фактура общественного питания».

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

Вызов!

1. Откройте существующую книгу Excel. Если вы хотите, вы можете использовать файл примера для этого урока.
2. Создайте формулу, использующую относительную ссылку . Если вы используете пример, используйте маркер заполнения , чтобы заполнить формулу в ячейках с E4 по E14 . Дважды щелкните ячейку, чтобы увидеть скопированную формулу и относительные ссылки на ячейки.
3. Создайте формулу, использующую абсолютный ссылка . Если вы используете пример, исправьте формулу в ячейке D4 , чтобы она ссылалась только на налоговую ставку в ячейке E2 как на абсолютную ссылку , затем используйте маркер заполнения, чтобы заполнить формулу из ячеек D4 в Д14 .