Как сохранить формулу в excel при добавлении столбца
Копирование формул - одна из наиболее распространенных операций при работе в 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-дневная пробная версия!
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 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.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Шаг 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 протянуть формулу до конца столбца с более высокой по сравнению с первой методикой скоростью. Требует от пользователя применить такие действия:
Читайте также: