Копирование формул excel: Как скопировать точную формулу в Excel

Содержание

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

20/12/2015

1909

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

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

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

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

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

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

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

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

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

Ctr+`(тильда)«, или вкладка «Формулы«, группа «Зависимости формул«, команда «Показать формулы«).

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

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

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

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

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

А можно предложить свой вариант в комментариях.

Еще записей в тему?

Если честно, некоторые могут быть не свежие:)

БОЛЬШЕ МАТЕРИАЛОВ

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

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

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

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

Для того чтобы ссылки на ячейки не изменялись при копировании, следует перед символами адреса ячейки или ссылки установить символы «$», т.е. задать абсолютные ссылки. Изменяться будут только те части адреса ячейки, перед которыми не стоит символ «$».

Для копирования формулы нужно выполнить следующие действия:

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

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

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

Для перемещения формулы необходимо выполнить следующие действия:

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

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

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

Для распространения формулы необходимо выполнить следующие действия:

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

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

Как скопировать формулу в Excel? 9 Простые методы

При работе с электронной таблицей может возникнуть множество причин, по которым вам может понадобиться скопировать формулу в одну или несколько ячеек. Они могут быть смежными (столбец или строка) или располагаться в разных частях электронной таблицы. В любом случае Microsoft Excel предлагает несколько вариантов копирования и вставки формул и других частей или функций содержимого ячейки.

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

Получите навсегда бесплатную учетную запись Confluence!

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

ПОЛУЧИТЕ БЕСПЛАТНО

Абсолютные и относительные ссылки в формулах

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

Абсолютная ссылка

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

Как скопировать формулу в Excel? 9 простых методов — абсолютная ссылка

смешанная ссылка

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

Как скопировать формулу в Excel? 9 простых методов — фиксированный столбец

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

Как скопировать формулу в Excel? 9 Простые методы — фиксированная строка

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

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

Как скопировать формулу в Excel? 9 простых методов — относительные ссылки

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

Существуют различные способы копирования и вставки формулы в Microsoft Excel. Вы можете скопировать его любым из доступных способов, в том числе щелкнув правой кнопкой мыши, чтобы увидеть меню, или используя сочетания клавиш: Ctrl+c (Windows) или Cmd+c (Mac).

Выберите ячейки, в которые вы хотите вставить.

Как скопировать формулу в Excel? 9 простых методов — копирование ячеек

Используйте сочетание клавиш — Ctrl + v или Cmd + v — или щелкните правой кнопкой мыши и выберите «Вставить». Это позволит точно воспроизвести ячейку, включая формулу и формат. Однако тип ссылки будет зависеть от исходной ячейки. Использование относительных ссылок в исходной формуле адаптирует ссылки к новому местоположению.

Как скопировать формулу в Excel? 9 простых методов — простая вставка

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

Если вы хотите сохранить только формулу, используйте «Специальная вставка» и выберите «Формулы».

Как скопировать формулу в Excel? 9 простых методов — вставка только формул

Однако, как вы можете видеть на снимке экрана ниже, Microsoft Excel предлагает множество специальных опций для вставки.

Как скопировать формулу в Excel? 9 простых способов — специальная вставка

Нажмите «Специальная вставка…», чтобы просмотреть дополнительные параметры.

Как скопировать формулу в Excel? 9 простых методов — дополнительные специальные параметры вставки

Как поделиться только одной вкладкой в Google Sheets

При совместном использовании электронной таблицы Google Таблиц Google обычно пытается предоставить общий доступ ко всему документу. Вот как вместо этого поделиться только одной вкладкой.

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

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

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

Как скопировать формулу в Excel? 9 простых способов — копирование ячейки с помощью формулы

2. Выберите первую ячейку и, удерживая нажатой клавишу Ctrl или Cmd, выберите остальные.

Как скопировать формулу в Excel? 9 простых методов — выделение ячеек

3. Нажмите Ctrl + v или Cmd + v, чтобы скопировать содержимое в выбранные ячейки.

Как скопировать формулу в Excel? 9 простых способов — вставить ярлык

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

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

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

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

1. Выберите любую ячейку в диапазоне и нажмите Ctrl + t (Windows) или Cmd + t (Mac).

Как скопировать формулу в Excel? 9 простых методов — преобразование в таблицу Excel

2. Введите формулу в первую ячейку столбца, где вы хотите получить результаты. В этом случае я хочу умножить значение в столбце суммы на 0,1 (10%).

Как скопировать формулу в Excel? 9 простых методов — введите формулу

3. Как только вы нажмете «Ввод», формула скопируется в последнюю строку.

Как скопировать формулу в Excel? 9 простых методов — нажмите Enter, чтобы применить формулу к столбцу

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

Если вы не хотите преобразовывать данные в таблицу Excel, вы можете использовать ярлык массива, чтобы применить формулу ко всему столбцу. Как и в предыдущем примере, я хочу умножить каждое значение в первом столбце на 0,1 (10%).

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

Как скопировать формулу в Excel? 9 простых методов — выберите столбец

2. Введите формулу и нажмите Ctrl + Shift + Enter.

Как скопировать формулу в Excel? 9 простых методов — введите формулу и используйте ярлык

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

Как скопировать формулу в Excel? 9Простые методы — скопированная формула

Как защитить паролем таблицу Google?

Если вы работаете с важными данными в Google Sheets, вам, вероятно, нужен дополнительный уровень защиты. Вот как вы можете защитить паролем Google Sheet

Использовать инструмент «Заливка»

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

Как скопировать формулу в Excel? 9 Простые методы — Главная > Редактирование

Нажмите, чтобы увидеть раскрывающееся меню «Заполнить». В этом случае я выберу «Вниз».

Как скопировать формулу в Excel? 9 Easy Methods – Меню заполнения

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

Как скопировать формулу в Excel? 9 Простые методы — формула копируется в столбец

Двойной щелчок по маркеру заполнения

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

Как скопировать формулу в Excel? 9 простых методов — двойной щелчок по маркеру заполнения

Формула автоматически копируется в последнюю строку.

Как скопировать формулу в Excel? 9 простых методов — формула скопирована в последнюю строку

Перетащите маркер заполнения

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

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

Как скопировать формулу в Excel? 9Простые методы — захватить маркер заполнения

2. Перетащите его по ячейкам, в которые вы хотите скопировать, в любом направлении.

Как скопировать формулу в Excel? 9 простых способов — перетащите для копирования

Используйте ярлык

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

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

Как скопировать формулу в Excel? 9Простые методы — копирование ячейки и выделение столбца

2. Вставьте и нажмите Ctrl + d (Windows) или Cmd + d (Mac). Чтобы копировать по строке, а не вниз по столбцу, выберите строку и используйте Ctrl + r или Cmd + r.

Как скопировать формулу в Excel? 9 простых способов: вставьте и используйте ярлык

Опыт Confluence: рабочее пространство Ultimate Team

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

Основные характеристики и преимущества:

Централизованное знание: Удобный доступ к коллективному разуму вашей команды.
Совместное рабочее пространство: Поощряйте взаимодействие с помощью гибких инструментов проекта.
Удобная связь: Подключите всю свою организацию без особых усилий.
Сохранить идеи: Сохраняйте идеи, не теряя их в чатах или уведомлениях.
Комплексная платформа: Управляйте всем контентом в одном месте.
Открытая командная работа: Предоставьте сотрудникам возможность вносить свой вклад, делиться информацией и расти.
Превосходная интеграция: Синхронизация с такими инструментами, как Slack, Jira, Trello и другими.

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

Оставьте это поле пустым

руководств и ресурсов по Excel и Google Sheets прямо в вашей почте!

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

Заключение

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

Самый простой способ обеспечить точное копирование формул во весь столбец таблицы — преобразовать диапазон в таблицу Excel. Если вы не хотите использовать таблицу Excel, вы знаете, что есть несколько методов, которые вы можете использовать для копирования формулы в любом направлении. Эти методы включают в себя использование сочетания клавиш формулы массива, использование инструмента заливки, перетаскивание или двойной щелчок маркера заполнения ячейки, а также использование сочетания клавиш для копирования формулы вниз или поперек выбранных ячеек.

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

Как заблокировать ячейки в Excel (ячейки, листы и формулы)
Как создать ссылку на другой лист или книгу в Excel
Автоматический перенос данных с одного листа Excel на другой
Как сравнить два файла Excel на наличие различий?

Получите навсегда бесплатную учетную запись Confluence!

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

ПОЛУЧИТЕ БЕСПЛАТНО

Копирование формул с использованием шаблона в Excel

Существует несколько различных способов копирования формул в Excel с использованием шаблона. Один из способов — использовать дескриптор заполнения. Ручка заполнения — это маленький квадратик в правом нижнем углу выбранной ячейки. При наведении указателя мыши на маркер заполнения курсор принимает форму перекрестия. Чтобы скопировать формулу с помощью маркера заполнения, выполните следующие действия:

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

Другой способ копирования формул — использование команд «Копировать» и «Вставить». Чтобы скопировать формулу с помощью этих команд, выполните следующие действия:

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

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

Основные сведения о формулах в Excel

Прежде чем мы углубимся в тему копирования формул с использованием шаблона, давайте сначала разберемся с основами формул в Excel. Формула — это уравнение, выполняющее вычисление одного или нескольких значений. Например, формула =A1+B1 суммирует значения в ячейках A1 и B1. Формулы в Excel всегда начинаются со знака равенства (=).

Excel использует систему ссылок на ячейки для идентификации ячеек, которые используются в формуле. Ссылка на ячейку — это комбинация буквы столбца и номера строки, которая идентифицирует конкретную ячейку. Например, ссылка на ячейку в первом столбце и первой строке — A1.

Копирование формул с использованием шаблона

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

Шаг 1. Создание формулы

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

Шаг 2: Выберите ячейку с формулой

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

Шаг 3. Используйте маркер заполнения для копирования формулы

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

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

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