Excel при добавлении строки копировались формулы
В процессе работы с Microsoft Excel у пользователей возникает необходимость внесения дополнительных данных. Для этого необходимо добавлять или копировать строки с формулами.
Кажется, это простая и понятная процедура. Но часто возникает вопрос о том, как добавить новую строку с формулами в Excel с определенными условиями. Например, без изменения адресов в формулах с относительными ссылками на ячейки и т.п. На готовых примерах рассмотрим оригинальные решения этих задач.
Как добавить новую строку с формулами в Excel?
Пускай мы добавили формулы в одну строку или в несколько. Теперь нам нужно скопировать все строки с формулами. Как это сделать при добавлении новой строки?
- Выделяем ту строку, которая содержит формулы. В нашей ситуации это строка под номером 3. Нам необходимо, чтобы остальные строки содержали в себе такие же формулы.
- Нажимаем на выделенную область правой кнопкой мышки. Из контекстного меню нужно выбрать опцию «Копировать» (или жмем CTRL+C).
- После этого следует выделить следующую одну (или несколько) нижнюю строку, куда будет вставлена новая с формулами.
- Щелкнуть по заголовку выделенной строки правой кнопкой мышки и выбрать опцию вставить скопированные ячейки (или нажать комбинацию клавиш CTRL+SHIFT+«=»).
Заметьте, значения ячеек не заменились а добавилась в середину таблицы новая строка со старыми значениями и формулами.
Как копировать без изменения формул в Excel?
Часто при копировании относительные формулы (т.е. те, в которых нет знака «$») «подставляют» другие значения. А что делать, если вы не хотите изменять значение формул?
Можно сделать все ссылки в формулах абсолютными. Такие формулы будут копироваться неизменно, без изменения данных. Чтобы сделать ссылки абсолютными, добавляем к ним знак «$».
Ставим перед буквами и перед числами знак доллара – и получаем абсолютные ссылки в формулы, значения в которых не будут изменяться при копировании.
На пример в такой таблице ссылка на наценку ($B$1) не будет изменятся при копировании на новых строках, так как она является абсолютной, а не относительной:
Но в некоторых больших и сложных таблицах слишком много формул, которые нужно скопировать и переместить. И чтобы во всех ссылках проставить знак «$» нужно потратить много времени.
В результате при копировании во всех строках ссылки формул остались неизменными.
Предположим, что у нас есть вот такая несложная таблица, в которой подсчитываются суммы по каждому месяцу в двух городах, а затем итог переводится в евро по курсу из желтой ячейки J2.
Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать:
Задача: скопировать диапазон с формулами так, чтобы формулы не изменились и остались теми же самыми, сохранив результаты расчета.
Способ 1. Абсолютные ссылки
Как можно заметить по предыдущей картинке, Excel сдвигает только относительные ссылки. Абсолютная (со знаками $) ссылка на желтую ячейку $J$2 не сместилась. Поэтому для точного копирования формул можно временно перевести все ссылки во всех формулах в абсолютные. Нужно будет выделить каждую формулу в строке формул и нажать клавишу F4:
Способ 2. Временная деактивация формул
- Выделяем диапазон с формулами (в нашем примере D2:D8)
- Жмем Ctrl+H на клавиатуре или на вкладке Главная - Найти и выделить - Заменить (Home - Find&Select - Replace)
Способ 3. Копирование через Блокнот
Этот способ существенно быстрее и проще.
Нажмите сочетание клавиш Ctrl+Ё или кнопку Показать формулы на вкладке Формулы (Formulas - Show formulas) , чтобы включить режим проверки формул - в ячейках вместо результатов начнут отображаться формулы, по которым они посчитаны:
Скопируйте наш диапазон D2:D8 и вставьте его в стандартный Блокнот:
Теперь выделите все вставленное (Ctrl+A), скопируйте в буфер еще раз (Ctrl+C) и вставьте на лист в нужное вам место:
Осталось только отжать кнопку Показать формулы (Show Formulas) , чтобы вернуть Excel в обычный режим.
Примечание: этот способ иногда дает сбой на сложных таблицах с объединенными ячейками, но в подавляющем большинстве случаев - работает отлично.
Способ 4. Макрос
Если подобное копирование формул без сдвига ссылок вам приходится делать часто, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставьте новый модуль через меню Insert - Module и скопируйте туда текст вот такого макроса:
Для запуска макроса можно воспользоваться кнопкой Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt+F8. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:
Как скопировать формулу ячейки только без форматирования в Excel?
Например, у меня есть два диапазона данных, и в одном из диапазонов данных были выполнены некоторые вычисления, и теперь мне нужно выполнить те же вычисления в другом диапазоне. Но я не хочу копировать форматирование диапазона 1 в диапазон 2, здесь в этом руководстве рассказывается о приемах решения этой проблемы в Excel.
Копировать формулу ячейки только без значений с помощью специальной вставки
Если вы напрямую скопируете и вставите ячейки формулы, форматирование ячеек будет вставлено вместе с формулой. А с помощью специальной вставки в Excel вы можете выбрать только копирование функции вставки формулы.
Выделите ячейки формулы, которые хотите скопировать, и нажмите Ctrl + C чтобы скопировать их, затем выберите ячейки, которые вы хотите вставить, и щелкните правой кнопкой мыши, чтобы отобразить контекстное меню, щелкните Специальная вставка > Формулы в Excel 2010 и 2013. Смотрите снимок экрана:
Внимание: В Excel 2007 после нажатия Вставить Special из контекстного меню открывается Специальная вставка диалоговое окно. В диалоговом окне отметьте Формулыи нажмите OK.
После этого вы увидите, что копируются только формулы.
Внимание: Ссылки на ячейки скопированных формул будут изменяться в зависимости от адресов ячеек назначения.
Копировать формулу ячейки только без значений путем копирования диапазонов
Есть мощная функция копирования - Копировать диапазоны в удобном инструменте Kutools for Excel. Используя диапазоны копирования, вы можете копировать только формат ячейки, формулу, значение, комментарии и т. Д.
Если вы не установили Kutools for Excel, просто получите его бесплатная установка сейчас. И после установки Kutools for Excel выполните следующие действия:
1. Выберите ячейки, которые хотите скопировать, и нажмите Кутулс > Копировать диапазоны. Смотрите скриншот:
2. в Копировать несколько диапазонов диалог, проверьте Формулы только вариант, затем нажмите Ok и выбрать ячейки для вставки формул. Смотрите скриншоты:
3. Нажмите OK. Теперь в ячейки вставляются формулы без форматирования. Смотрите скриншот:
Примечание: Ссылки на ячейки скопированных формул изменятся в зависимости от адресов ячеек назначения.
Копировать формулу ячейки без изменения ссылки на ячейку с помощью Exact Copy
Помимо копирования только формул, иногда может потребоваться скопировать формулы ячеек и сохранить ссылки на ячейки. Например, вы копируете формулу = MAX (B2: B14) ячейки Sheet1! B16 в ячейку Sheet2! D1, формула также = MAX (B2: B14).
Здесь, в Kutools for Excel, Точная копия Утилита может оказать вам услугу.
1. Выберите ячейки формулы, которые необходимо скопировать, и нажмите Кутулс > Точная копия. Смотрите скриншот:
2. Затем в Точная копия формулы диалог, снимите флажок Копировать форматированиеи нажмите Ok для выделения ячеек нужна формула вставки. Смотрите скриншоты:
3. Нажмите OK. Теперь формулы вставлены с сохранением ссылки на ячейку. Смотрите скриншот:
Вы можете бесплатно использовать более 100 полезных утилит в течение 60 дней, почему бы не попробовать? Бесплатная установка Kutools for Excel прямо сейчас!
Как быстро применить формулу ко всему столбцу или строке с / без перетаскивания в Excel?
Иногда вам может потребоваться применить одну и ту же формулу ко всему столбцу или строке в Excel, например C1 = A1 * 2, C2 = A2 * 2, . Сп = An * 2. Будет довольно утомительно, если вы будете вводить формулы в каждую ячейку по очереди. Есть несколько хитрых способов быстро применить одну и ту же формулу к столбцу или строке ввода.
- Применение формулы ко всему столбцу или строке с помощью перетаскивания маркера автозаполнения
- Применение формулы ко всему столбцу или строке без перетаскивания с помощью сочетаний клавиш
- Применить формулу ко всему столбцу или строке без перетаскивания функцией заполнения
- Применить формулу ко всему столбцу или строке без перетаскивания с помощью Kutools for Excel
Предположим, вам нужно применить формулу = (A1 * 3 + 8) / 5 в столбце C и см. Следующие руководства, чтобы применить ту же формулу ко всему столбцу C.
Применение формулы ко всему столбцу или строке с помощью перетаскивания маркера автозаполнения
Перетаскивание маркера автозаполнения - наиболее распространенный способ применения одной формулы ко всему столбцу или строке в Excel.
Сначала введите формулу = (A1 * 3 + 8) / 5 в ячейке C1, а затем перетащите маркер автозаполнения вниз в столбце C, затем формула = (A1 * 3 + 8) / 5 применяется ко всему столбцу C. Если вам нужно применить его ко всей строке, вы можете перетащить маркер автозаполнения в крайнем правом углу.
Внимание: Этот метод перетаскивания маркера автозаполнения требует автоматического вычисления формулы. Вы можете включить его, нажав Формулаs> Варианты расчета > Автоматически. См. Снимок экрана ниже:
Применение формулы ко всему столбцу или строке без перетаскивания с помощью сочетаний клавиш
Иногда столбец, к которому вы хотите применить формулу, может занимать сотни строк, и перетаскивание маркера заполнения может быть неудобным. На самом деле вы и используете сочетания клавиш, чтобы легко заархивировать его в Excel.
Во-первых, выберите весь столбец C, во-вторых, введите формулу = (A1 * 3 + 8) / 5, а затем нажмите Ctrl + Enter ключи вместе.
Если вы хотите применить формулу ко всей строке, вы можете сначала выбрать всю строку.
Копируйте формулы точно / статически из одного столбца в другой, не меняя ссылки на ячейки в Excel
Kutools for Excel Точная копия Утилита может помочь вам легко скопировать несколько формул без изменения ссылок на ячейки в Excel, предотвращая автоматическое обновление относительных ссылок на ячейки. Полнофункциональная бесплатная 30-дневная пробная версия!
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Применить формулу ко всему столбцу или строке без перетаскивания функцией заполнения
На самом деле есть Заполнять на ленте Excel, чтобы быстро применить формулу ко всему столбцу или строке.
Сначала введите формулу = (A1 * 3 + 8) / 5 в ячейку C1 (первая ячейка столбца, в которую вы введете ту же формулу), во-вторых, выберите весь столбец C, а затем нажмите Главная > Заполнять > вниз.
Если вы хотите применить формулу ко всей строке, просто введите формулу в первую ячейку всей строки, затем выберите всю строку и нажмите Главная> Заполнить > Правильно.
Применить формулу ко всему столбцу или строке без перетаскивания с помощью Kutools for Excel
Все вышеперечисленные методы предназначены для применения формул к пустому столбцу или строке. Иногда вам может потребоваться применить одну и ту же формулу ко всему столбцу или строке с данными. Как обрабатывать? Вы можете попробовать Kutools for Excel's Инструменты для работы.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Выберите столбец или строку, с которой вы будете работать (в этом случае выберите столбец A), и нажмите Кутулс > Еще > Эксплуатация.
2. в Инструменты для работы в диалоговом окне выберите На заказ in Эксплуатация введите, введите (? * 3 + 8) / 5 в пустом поле в На заказ и нажмите OK кнопка. Смотрите скриншот:
И тогда весь столбец заполняется формулой = (? * 3 + 8) / 5, а? относится к значению в соответствующей ячейке. См. Скриншоты ниже:
Ноты:
(1) Если вы проверите Создавать формулы вариант, результаты будут в виде формул для каждой ячейки.
(2) Если Пропустить ячейки формулы установлен флажок, операция будет пропущена и автоматически проигнорирует ячейки формулы в выбранном диапазоне.
Инструменты для работы может выполнять общие математические операции в нескольких ячейках вместе, такие как сложение, вычитание, умножение и деление и т. д.
Подробнее
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Демо: применение формулы ко всему столбцу или строке без перетаскивания с помощью Kutools for Excel
Легко добавлять префикс или суффикс ко всем ячейкам всего столбца или строки в Excel
С помощью функции автозаполнения легко заполнить все ячейки одним и тем же содержимым в столбце. Но как добавить один и тот же префикс или суффикс ко всем ячейкам в столбце? Сравнивая ввод префикса или суффикса для каждой ячейки отдельно, Kutools for Excel's Добавить текст Утилита предоставляет простой обходной путь, позволяющий сделать это всего за несколько кликов. Полнофункциональная бесплатная 30-дневная пробная версия!
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Как сделать автоматическое добавление формул при добавлении новой строки. Например в приложенном файле выделяю строку 4, вставить и чтобы добавились в вставленную строку только формулы из предыдущей строки.
Как сделать автоматическое добавление формул при добавлении новой строки. Например в приложенном файле выделяю строку 4, вставить и чтобы добавились в вставленную строку только формулы из предыдущей строки. DDS
Тот же вопрос. DDS, если бы это мало что давало, вряд ли бы созданию таблицы было присвоено сразу два хоткея: Ctrl + T и Ctrl + L
Тот же вопрос. DDS, если бы это мало что давало, вряд ли бы созданию таблицы было присвоено сразу два хоткея: Ctrl + T и Ctrl + L buchlotnik
Тот же вопрос. DDS, если бы это мало что давало, вряд ли бы созданию таблицы было присвоено сразу два хоткея: Ctrl + T и Ctrl + L Автор - buchlotnik
Дата добавления - 17.11.2014 в 00:03
Дело в том, что таблица состоит из сложной шапки, а также большого количества строк и много подсуммировок. Вот еще файл с примером: возможно ли сделать так, чтобы после добавления строки после строки "работа 1.2" автоматически добавлялась бы только формулы с верхней строки, без значений. Наименование работ не повторяются. А может возможно упростить как то шапку, чтобы применить умную таблицу?
Дело в том, что таблица состоит из сложной шапки, а также большого количества строк и много подсуммировок. Вот еще файл с примером: возможно ли сделать так, чтобы после добавления строки после строки "работа 1.2" автоматически добавлялась бы только формулы с верхней строки, без значений. Наименование работ не повторяются. А может возможно упростить как то шапку, чтобы применить умную таблицу? DDS
DDS, формулы добавятся, как только Вы заполните ячейки А8:Е8 при условии, что выше было не менее трёх строк, заполненных формулами. Чтобы считалась сумма, измените формулу на
и новые строчки всегда вставляйте перед последней пустой.
За автоматическую вставку формул отвечает флажок
DDS, формулы добавятся, как только Вы заполните ячейки А8:Е8 при условии, что выше было не менее трёх строк, заполненных формулами. Чтобы считалась сумма, измените формулу на
и новые строчки всегда вставляйте перед последней пустой.
За автоматическую вставку формул отвечает флажок
Pelena
и новые строчки всегда вставляйте перед последней пустой.
За автоматическую вставку формул отвечает флажок
Автор - Pelena
Дата добавления - 17.11.2014 в 10:39
Читайте также: