Узнайте, как копировать формулы в несколько ячеек, вниз по столбцу, в несмежные ячейки, копировать формулы с абсолютными или смешанными ссылками на ячейки и т. Д.
Копирование формул - одна из наиболее распространенных и простых задач, которые вы выполняете в типичной электронной таблице, которая в основном полагается на формулы. Вместо того, чтобы вводить одну и ту же формулу снова и снова в Excel, вы можете просто скопировать и вставить формулу из одной ячейки в несколько ячеек.
После написания формулы в Excel вы можете использовать команды копирования и вставки в несколько ячеек, несколько несмежных ячеек или целые столбцы. Если вы не сделаете это правильно, вы получите ужасные ошибки # REF и / DIV0. В этой статье мы покажем вам различные методы, которые вы можете использовать для копирования формул в Excel.
Как копировать и вставлять формулы в Excel
Microsoft Excel предоставляет различные способы копирования формул с относительными ссылками на ячейки, абсолютными ссылками на ячейки или смешанными ссылками.
- Копировать формулу из одной ячейки в другую
- Копирование одной ячейки формулы в несколько ячеек
- Копирование формулы во весь столбец
- Копирование формулы без форматирования
- Копировать формулы в несмежные ячейки
- Копирование формул без изменения ссылок на ячейки
Как скопировать формулу из одной ячейки в другую в Excel
Иногда вам может потребоваться скопировать формулу из одной ячейки в другую в Excel, чтобы избежать повторного ввода всей формулы заново и сэкономить при этом время.
Допустим, у нас есть такая таблица:
Есть несколько способов скопировать формулу из одной ячейки в другую.
Сначала выберите ячейку с формулой и щелкните правой кнопкой мыши, а затем в контекстном меню выберите «Копировать», чтобы скопировать формулу. Или вы можете использовать опцию «Копировать» в разделе «Буфер обмена» на вкладке «Главная».
Но вы также копируете формулы, просто нажимая сочетание клавиш Ctrl + C. Это более эффективный и экономящий время метод.
Затем мы переходим к ячейке, которую хотим вставить, нажимаем ярлык Ctrl + V чтобы вставить формулу. Или щелкните правой кнопкой мыши ячейку, которую хотите вставить, и выберите параметры в разделе «Параметры вставки»: либо простой вариант «Вставить (P)», либо вариант «Вставить как вариант« Формула (F) ».
Кроме того, вы также можете нажать «Специальная вставка» под шестью значками вставки, чтобы открыть диалоговое окно «Специальная вставка». Здесь у вас есть несколько вариантов, включая шесть вариантов вставки из контекстного меню. Выберите «Все» или «Формулы» в разделе «Вставить» и нажмите «ОК».
Теперь в ячейке с вставленной формулой должны быть те же формулы (как и в скопированной ячейке), но с другими ссылками на ячейки. Адрес ячейки автоматически настраивается Excel, чтобы соответствовать номеру строки вставленной ячейки.
Копировать формулу из одной ячейки в несколько ячеек
Та же операция вставки работает точно так же, если мы выбираем несколько ячеек или диапазон ячеек.
Выделите ячейку с формулой и нажмите Ctrl + C чтобы скопировать формулу. Затем выберите все ячейки, в которые вы хотите вставить формулу, и нажмите Ctrl + V , чтобы вставить формулу, или используйте один из описанных выше методов вставки, чтобы вставить формулу (как мы это сделали для отдельной ячейки).
Копировать формулу во весь столбец или строку
В Excel вы можете быстро скопировать формулу во весь столбец или строку.
Чтобы скопировать формулу в столбец или строку, сначала введите формулу в ячейку. Затем выберите ячейку с формулой (D1) и наведите курсор на небольшой зеленый квадрат в правом нижнем углу ячейки. При наведении курсора курсор изменится на черный знак плюса (+), который называется маркером заливки. Щелкните и удерживайте этот маркер заливки и перетащите его в любом направлении (столбец или строка) над ячейками, чтобы скопировать формулу.
Когда вы копируете формулу в диапазон ячеек, ссылки на ячейки формулы будут автоматически корректироваться в зависимости от относительного расположения строк и столбцов, а формула будет выполнять вычисления на основе значений в этих ссылках на ячейки (см. Ниже).
В приведенном выше примере, когда формула из D1 (= A1 * B1) / 2) копируется в ячейку D2, относительная ссылка изменяется в зависимости от ее местоположения (= A2 * B2) / 2) и т. Д.
Таким же образом вы можете перетащить формулу в соседние ячейки влево, вправо или вверх.
Другой способ скопировать формулу во весь столбец - дважды щелкнуть маркер заполнения вместо его перетаскивания. Если дважды щелкнуть маркер заполнения, формула сразу же применяется к соседней ячейке, если есть какие-либо данные.
Копирование формулы в диапазон без копирования форматирования
Когда вы копируете формулу в диапазон ячеек с помощью маркера заполнения, она также копирует форматирование исходной ячейки, такое как цвет шрифта или цвет фона, валюта, процент, время и т. Д. (Как показано ниже).
Чтобы предотвратить копирование форматирования ячейки, перетащите маркер заполнения и щелкните «Параметры автозаполнения» в правом нижнем углу последней ячейки. Затем в раскрывающемся меню выберите «Заполнить без форматирования».
Результат:
Скопируйте формулу Excel только с числовым форматированием
Если вы хотите скопировать формулу только с формулой и форматированием, таким как процентный формат, десятичные точки и т. Д.
Скопируйте формулу и выделите все ячейки, в которые вы хотите скопировать формулу. На вкладке «Главная» нажмите стрелку под кнопкой «Вставить» на ленте. Затем щелкните значок «Формулы и форматирование чисел» (значок с% fx) в раскрывающемся списке, чтобы вставить только формулу и форматирование чисел.
Этот параметр копирует только форматирование формул и чисел, но игнорирует все другие форматы ячеек, такие как цвет фона, цвет шрифта и т. Д.
Скопируйте формулу в несмежные / несмежные ячейки
Если вы хотите скопировать формулу в несмежные ячейки или несмежные диапазоны, вы можете сделать это с помощью Ctrl ключ.
Выделите ячейку с формулой и нажмите Ctrl + C скопировать это. Затем выберите несмежные ячейки / диапазоны, удерживая нажатой кнопку Ctrl ключ. Затем нажмите Ctrl + V вставить формулу и нажать Входить завершить.
Копирование формул без изменения ссылок на ячейки в Excel
Когда формула копируется в другую ячейку, Excel автоматически меняет ссылки на ячейки, чтобы они соответствовали ее новому расположению. Эти ссылки на ячейки используют относительное расположение адреса ячейки, поэтому они называются относительной ссылкой на ячейку (без $). Например, если у вас есть формула «= A1 * B1» в ячейке C1, и вы скопируете эту формулу в ячейку C2, формула изменится на «= A2 * B2». Все методы, которые мы обсуждали выше, используют относительные ссылки.
Когда вы копируете формулу с относительными ссылками на ячейки, она автоматически меняет ссылки, чтобы формула ссылалась на соответствующие строки и столбцы. Если вы используете в формуле абсолютные ссылки, то та же формула копируется без изменения ссылок на ячейки.
Когда вы помещаете знак доллара ($) перед буквой столбца и номером строки ячейки (например, $ A $ 1), он превращает ячейку в абсолютную ячейку. Теперь независимо от того, куда вы копируете формулу, содержащую абсолютную ссылку на ячейку, формула никогда не будет. Но если у вас есть относительная или смешанная ссылка на ячейку в формуле, используйте любой из следующих методов для копирования без изменения ссылок на ячейки.
Копирование формулы с абсолютной ссылкой на ячейку с помощью метода копирования-вставки
Иногда вам может потребоваться скопировать / применить точную формулу вниз по столбцу, не меняя ссылки на ячейки. Если вы хотите скопировать или переместить точную формулу с абсолютной ссылкой, сделайте следующее:
Сначала выберите ячейку с формулой, которую вы хотите скопировать. Затем щелкните строку формул, выберите формулу с помощью мыши и нажмите Ctrl + C скопировать это. Если вы хотите переместить формулу, нажмите Ctrl + X резать это. Затем нажмите Esc , чтобы выйти из строки формул.
Либо выберите ячейку с формулой и нажмите F2 или дважды щелкните ячейку. Это переведет выбранную ячейку в режим редактирования. Затем выберите формулу в ячейке и нажмите Ctrl + C , чтобы скопировать формулу в ячейку как текст.
Затем выберите ячейку назначения и нажмите Ctrl + V чтобы вставить формулу.
Теперь точная формула копируется в ячейку назначения без каких-либо изменений ссылки на ячейку.
Копирование формул с абсолютными или смешанными ссылками на ячейки
Если вы хотите переместить или скопировать точные формулы без изменения ссылок на ячейки, вам следует заменить относительные ссылки на ячейки абсолютными. Например, добавление знака ($) к относительной ссылке на ячейку (B1) делает ее абсолютной ссылкой ($ B $ 1), поэтому она остается статической независимо от того, куда копируется или перемещается формула.
Но иногда вам может потребоваться использовать смешанные ссылки на ячейки ($ B1 или B $ 1), добавив знак доллара ($) перед буквой столбца или номером строки, чтобы заблокировать строку или столбец на месте.
Поясним на примере. Предположим, у вас есть таблица, в которой ежемесячная экономия рассчитывается путем вычитания арендной платы (B9) из заработка (в столбце B) каждый месяц.
В приведенном ниже примере формула использует абсолютную ссылку на ячейку ($ B $ 9) для привязки суммы арендной платы к ячейке B9 и относительную ссылку на ячейку для ячейки B2, поскольку ее необходимо настраивать для каждой строки, чтобы она соответствовала каждому месяцу. B9 делается абсолютной ссылкой на ячейку ($ 9), потому что вы хотите вычесть ту же сумму арендной платы из ежемесячного дохода.
Допустим, вы хотите переместить балансы из столбца C в столбец E. Если вы скопируете формулу (обычным методом копирования / вставки) из ячейки C2 (= B2- $ B $ 9), она изменится на = D2- $ B $ 9 при вставке. в ячейке E2, что делает ваши вычисления неверными!
В этом случае замените относительную ссылку на ячейку (B2) на смешанную ссылку на ячейку ($ B2), добавив знак «$» перед буквой столбца формулы, введенной в ячейку C2.
И теперь, если вы скопируете или переместите формулу из ячейки C2 в E2 или любую другую ячейку и примените формулу вниз по столбцу, ссылка на столбец останется прежней, а номер строки будет скорректирован для каждой ячейки.
Копирование и вставка формул Excel без изменения ссылок с помощью Блокнота
Вы можете увидеть каждую формулу в своей электронной таблице Excel, используя параметры «Показать формулу». Для этого перейдите на вкладку «Формулы» и выберите «Показать формулы».
Кроме того, вы можете войти в режим просмотра формул, нажав кнопку Ctrl + ` ярлык, который отображает все формулы на вашем листе. Вы можете найти клавишу с ударением (`) в верхнем левом углу клавиатуры в ряду с цифровыми клавишами (под ESC и перед клавишей с цифрой 1).
Выделите все ячейки с формулами, которые хотите скопировать, и нажмите Ctrl + C скопировать их, или Ctrl + X резать их. Затем откройте Блокнот и нажмите Ctrl + V чтобы вставить формулы в блокнот.
Затем выберите формулу и скопируйте (Ctrl + C) его из блокнота и вставьте (Ctrl + V) в ячейке, в которую вы хотите скопировать точную формулу. Вы можете копировать и вставлять их по одному или все сразу.
После вставки формул выключите режим просмотра формул, нажав Ctrl + ` или снова перейдите в «Формулы» -> «Показать формулы».
Скопируйте точные формулы с помощью функции "Найти и заменить" в Excel
Если вы хотите скопировать ряд точных формул, вы также можете использовать для этого инструмент Excel «Найти и заменить».
Выделите все ячейки с формулами, которые вы хотите скопировать. Затем перейдите на вкладку «Главная», нажмите «Найти и выбрать» в группе редактирования и выберите вариант «Заменить» или просто нажмите Ctrl + H , чтобы открыть диалоговое окно «Найти и заменить».
В диалоговом окне «Найти и заменить» введите знак равенства (=) в поле «Найти». В поле «Заменить на» введите символ или символ, который еще не является частью ваших формул, например # или и т. Д. Затем нажмите кнопку «Заменить все».
Вы увидите окно с сообщением «Мы сделали 6 замен» (потому что мы выбрали 6 ячеек с формулами). Затем нажмите «ОК» и «Закрыть», чтобы закрыть оба диалоговых окна. При этом все знаки равенства (=) заменяются знаками решетки (#), а формулы превращаются в текстовые строки. Теперь ссылки на ячейки в формулах не меняются при копировании.
Теперь вы можете выбрать эти ячейки, нажмите Ctrl + C чтобы скопировать их и вставить в целевые ячейки с помощью Ctrl + V.
Наконец, вам нужно заменить знаки (#) на знаки (=). Для этого выберите оба диапазона (исходный и скопированный) и нажмите Ctrl + H , чтобы открыть диалоговое окно «Найти и заменить». На этот раз введите знак решетки (#) в поле «Найти» и знак равенства (=) в поле «Заменить на» и нажмите кнопку «Заменить все». Нажмите «Закрыть», чтобы закрыть диалоговое окно.
Теперь текстовые строки преобразуются обратно в формулы, и вы получите следующий результат:
Сделанный!