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

Обновлено: 06.07.2024

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

Способ 1. "Протягивание" формулы

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

При использовании этого метода будет скопирована не только формула, но и всё форматирование ячейки (заливка, границы, условное форматирование и т.д.). Минусом метода является невозможность копирования в несмежные ячейки ("протянуть" формулу можно только на соседние столбцы и строки). Если выделить несколько ячеек и протянуть - то копироваться будет весь диапазон.

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

Контекстное меню при протягивании правой кнопкой мыши Контекстное меню при протягивании правой кнопкой мыши

Если протянуть ячейки не получается, то убедитесь, что в параметрах Excel не была отключена такая возможность. Файл - Параметры - Дополнительно - галочка напротив "Разрешить маркеры заполнения и перетаскивания ячеек" должна быть активирована.

Способ 2. Использование команды "Заполнить"

Похожий на предыдущий способ копирования можно реализовать, используя команду "Заполнить" на ленте на вкладке "Главная" в группе команд "Редактирование"

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

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

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

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

Способ 1. Классический

Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:

Выделите диапазон с формулами, которые нужно заменить на значения.
Скопируйте его правой кнопкой мыши – Копировать(Copy) .
Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values) :

либо наведитесь мышью на команду Специальная вставка (Paste Special) , чтобы увидеть подменю:

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

В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special - Values) в открывшемся диалоговом окне:

Способ 2. Только клавишами без мыши

При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:

Копируем выделенный диапазон Ctrl + C
Тут же вставляем обратно сочетанием Ctrl + V
Жмём Ctrl , чтобы вызвать меню вариантов вставки
Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter :

Способ 3. Только мышью без клавиш или Ловкость Рук

Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:

Выделяем диапазон с формулами на листе
Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only) .

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

Способ 4. Кнопка для вставки значений на Панели быстрого доступа

Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл - Параметры - Панель быстрого доступа (File - Options - Customize Quick Access Toolbar) . В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:

Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:

Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt , то Excel подскажет цифру, которая за это отвечает:

Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу

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

Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:

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

И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:

Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt + F11 чтобы попасть в Visual Basic, далее Insert - Module). Запускать их потом можно через вкладку Разработчик - Макросы (Developer - Macros) или сочетанием клавиш Alt + F8 . Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить - применяйте их с осторожностью.

Способ 6. Для ленивых

Если ломает делать все вышеперечисленное, то можно поступить еще проще - установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:

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

Есть ли способ сохранить и формулу, и форматирование при копировании и вставке в Excel? Здесь я расскажу о нескольких методах ее решения.

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

Обычно Microsoft Excel сохраняет всю книгу как персональный шаблон. Но иногда вам может просто потребоваться часто повторно использовать определенный выбор. По сравнению с сохранением всей книги в виде шаблона, Kutools for Excel предоставляет симпатичный обходной путь Авто Текст Утилита для сохранения выбранного диапазона как записи автотекста, в которой могут оставаться форматы ячеек и формулы в диапазоне. И тогда вы сможете повторно использовать этот диапазон одним щелчком мыши. Полнофункциональная бесплатная 30-дневная пробная версия!

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Сохраните формулу и форматирование при копировании, изменив ссылку на формулу

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

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

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

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

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

Теперь каждая формула в указанном диапазоне изменяется на абсолютную, как показано ниже:

Шаг 3: Теперь выберите указанный диапазон, скопируйте, нажав Ctrl + C одновременно, выберите пустую ячейку и вставьте, нажав Ctrl + V ключи одновременно.

Теперь указанный диапазон копируется и вставляется с оставшейся его формулой и форматированием, как показано ниже:

Примечание: При необходимости вы можете изменить вставленные формулы и исходные формулы на относительные ссылки, выделив их и нажав Kutools > Преобразовать ссылки > К относительной > OK.

Сохраняйте формулу и форматирование при копировании с помощью Kutools for Excel

Если у вас установлен Kutools for Excel, его Точная копия Утилита может помочь вам сохранить формулы и форматирование при копировании и вставке в Excel.

Шаг 1: Выберите диапазон, который вы скопируете с формулой и форматированием.

Шаг 2: нажмите Kutools > Точная копия, и в появившемся диалоговом окне Exact Formula Copy отметьте Copy форматирование и нажмите Ok кнопка. См. Снимок экрана ниже:

Шаг 3. Теперь откроется второе диалоговое окно «Копирование точной формулы», выберите пустую ячейку и щелкните значок OK кнопку.

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

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

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

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

1 Простое протягивание формулы

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

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

Аргументы в формуле будут изменяться соответственно новому расположению. И если в самой первой ячейке это были F7 и G7, в последней позиции столбца это будет уже F12 и G12. Соответственно, если начинать распространять формулы по строкам, изменяться будут не цифры, а буквы в обозначениях ячеек.

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

2 Быстрое автозаполнение

Еще один способ в Excel протянуть формулу до конца столбца с более высокой по сравнению с первой методикой скоростью. Требует от пользователя применить такие действия:

Читайте также: