Как сохранить формулу в excel при добавлении столбца
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
Вычисляемые столбцы в таблицах Excel упрощают ввод формул. Формула, введенная в одну ячейку столбца, автоматически распространяется на его остальную часть. При этом не приходится использовать заполнение или копирование. Это позволяет сэкономить время, особенно при наличии множества строк. То же самое происходит при изменении формулы: изменение также распространяется на все остальные ячейки вычисляемого столбца.
Примечание: Снимки экрана в этой статье получены в Excel 2016. Если вы используете другую версию, интерфейс может немного отличаться, но функции будут такими же.
Создание вычисляемого столбца
Создайте таблицу. Если вы не знакомы с таблицами Excel, см. статью Общие сведения о таблицах Excel.
Вставьте в таблицу новый столбец. Введите данные в столбец справа от таблицы, и Excel автоматически расширит ее. В этом примере мы создали новый столбец, введя "Итог" в ячейке D1.
Вы также можете добавить столбец на вкладке Главная. Просто щелкните стрелку на кнопке Вставить и выберите команду Вставить столбцы таблицы слева.
Введите нужную формулу и нажмите клавишу ВВОД.
В этом случае мы ввели =СУММ(, а затем выбрали столбцы Кв1 и Кв2. В результате Excel создал следующую формулу: =СУММ(Таблица1[@[Кв1]:[Кв2]]). Такие формулы называются формулами со структурированными ссылками, и их можно использовать только в таблицах Excel. Структурированные ссылки позволяют использовать одну и ту же формулу в каждой строке. Обычная формула Excel выглядела бы как =СУММ(B2:C2), и ее было бы необходимо добавить в остальные ячейки путем копирования и вставки или заполнения.
Дополнительные сведения о структурированных ссылках см. в статье Использование структурированных ссылок в таблицах Excel.
При нажатии клавиши ВВОД формула будет автоматически применена ко всем ячейкам столбца, которые находятся сверху и снизу от активной ячейки. Для каждой строки используется одна и та же формула, но поскольку это структурированная ссылка, Excel знает, на что она ссылается в каждой строке.
При копировании формулы во все ячейки пустого столбца или заполнении его формулой он также становится вычисляемым.
Если ввести или переместить формулу в столбец, уже содержащий данные, это не приведет к автоматическому созданию вычисляемого столбца. Однако отобразится кнопка Параметры автозамены, с помощью которой можно перезаписать данные и создать вычисляемый столбец.
При вводе новой формулы, которая отличается от существующих в вычисляемом столбце, она будет автоматически применена к столбцу. Вы можете отменить обновление и оставить только одну новую формулу, используя кнопку Параметры автозамены. Обычно не рекомендуется этого делать, так как столбец может прекратить автоматически обновляться из-за того, что при добавлении новых строк будет неясно, какую формулу нужно к ним применять.
Если вы введили или скопировали формулу в ячейку пустого столбца и не хотите сохранять новый вычисляемого столбца, нажмите кнопку Отменить два раза. Вы также можете дважды нажать клавиши CTRL+Z.
В вычисляемый столбец можно включать формулы, отличающиеся от формулы столбца. Ячейки с такими формулами становятся исключениями и выделяются в таблице. Это позволяет выявлять и устранять несоответствия, возникшие по ошибке.
Примечание: Исключения вычисляемого столбца возникают в результате следующих операций.
При вводе в ячейку вычисляемого столбца данных, отличных от формулы.
Ввод формулы в ячейку вычисляемого столбца и нажатие кнопки Отменить на панели быстрого доступа.
Ввод новой формулы в вычисляемый столбец, который уже содержит одно или несколько исключений.
При копировании в вычисляемый столбец данных, которые не соответствуют формуле вычисляемого столбца.
Примечание: Если скопированные данные содержат формулу, она заменяет данные в вычисляемом столбце.
При удалении формулы из одной или нескольких ячеек вычисляемого столбца.
Примечание: В этом случае исключение не помечается.
При удалении или перемещении ячейки в другую область листа, на которую ссылается одна из строк вычисляемого столбца.
Если вы используете Mac, в строке меню Excel выберите Параметры > Формулы и списки > Поиск ошибок.
Параметр автоматического заполнения формул для создания вычисляемых столбцов в таблице Excel по умолчанию включен. Если не нужно, чтобы приложение Excel создавало вычисляемые столбцы при вводе формул в столбцы таблицы, можно выключить параметр заполнения формул. Если вы не хотите выключать этот параметр, но не всегда при работе с таблицей хотите создавать вычисляемые столбцы, в этом случае можно прекратить автоматическое создание вычисляемых столбцов.
Включение и выключение вычисляемых столбцов
На вкладке Файл нажмите кнопку Параметры.
Выберите категорию Правописание.
В разделе Параметры автозамены нажмите кнопку Параметры автозамены
Откройте вкладку Автоформат при вводе.
В разделе Автоматически в ходе работы установите или снимите флажок Создать вычисляемые столбцы, заполнив таблицы формулами, чтобы включить или выключить этот параметр.
Если вы используете Mac, выберите Excel в главном меню, а затем щелкните Параметры > Формулы и списки > Таблицы и фильтры > Автоматически заполнять формулы.
Прекращение автоматического создания вычисляемых столбцов
После ввода в столбец таблицы первой формулы нажмите отобразившуюся кнопку Параметры автозамены, а затем выберите Не создавать вычисляемые столбцы автоматически.
Вы также можете создавать настраиваемые вычисляемые поля со с помощью стеблей, в которых создается одна формула Excel а затем применяется ко всему столбце. Подробнее о вычислении значений в pivotTable.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Предположим, что у нас есть вот такая несложная таблица, в которой подсчитываются суммы по каждому месяцу в двух городах, а затем итог переводится в евро по курсу из желтой ячейки 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. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:
Добрый день!
Задача следующая:
при заполнении яч. А6-D6 или А11-D11 (крайне неопытным пользователем . ),
можно ли сделать автоматическое добавление строки ниже
с обязательным сохранением формул в столбцах E и F,
например, как только будет внесена фамилия с столбец А?
Добрый день!
Задача следующая:
при заполнении яч. А6-D6 или А11-D11 (крайне неопытным пользователем . ),
можно ли сделать автоматическое добавление строки ниже
с обязательным сохранением формул в столбцах E и F,
например, как только будет внесена фамилия с столбец А? Leprotto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
nextR = Target.Row + 1
Debug.Print InStr(Cells(nextR, 1), "отдел")
If InStr(Cells(nextR, 1), "отдел") > 0 And Target.Value <> "" Then
Rows(nextR).Insert Shift:=xlDown
Range("e" & Target.Row - 1 & ":f" & Target.Row - 1).Copy Range("e" & Target.Row & ":f" & Target.Row)
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
nextR = Target.Row + 1
Debug.Print InStr(Cells(nextR, 1), "отдел")
If InStr(Cells(nextR, 1), "отдел") > 0 And Target.Value <> "" Then
Rows(nextR).Insert Shift:=xlDown
Range("e" & Target.Row - 1 & ":f" & Target.Row - 1).Copy Range("e" & Target.Row & ":f" & Target.Row)
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
nextR = Target.Row + 1
Debug.Print InStr(Cells(nextR, 1), "отдел")
If InStr(Cells(nextR, 1), "отдел") > 0 And Target.Value <> "" Then
Rows(nextR).Insert Shift:=xlDown
Range("e" & Target.Row - 1 & ":f" & Target.Row - 1).Copy Range("e" & Target.Row & ":f" & Target.Row)
End If
End Sub
Manyasha, спасибо большое! Все работает так как я и хотел.
Вот только я не настольно продвинут, чтобы интегрировать это в реальный документ ((
Manyasha, спасибо большое! Все работает так как я и хотел.
Вот только я не настольно продвинут, чтобы интегрировать это в реальный документ (( Leprotto
Leprotto, тыкаете ПКМ по ярлычку листа, в котором нужно добавлять строки, выбираете "исходный текст", вставляете в открывшееся окно код из моего файла. Если структура Вашего листа сильно отличается от файла-примера, пишите в чем именно отличия, а лучше показывайте.
Leprotto, тыкаете ПКМ по ярлычку листа, в котором нужно добавлять строки, выбираете "исходный текст", вставляете в открывшееся окно код из моего файла. Если структура Вашего листа сильно отличается от файла-примера, пишите в чем именно отличия, а лучше показывайте. Manyasha
Manyasha, отличается сильно )) прикладываю.
- добавление строк необходимо при заполнении яч. Е24, 26,28. Е325;
- с сохранением формул с столбцах D,R,S,T,U
Manyasha, отличается сильно )) прикладываю.
- добавление строк необходимо при заполнении яч. Е24, 26,28. Е325;
- с сохранением формул с столбцах D,R,S,T,U Leprotto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
'Если изменяем НЕ колонка C (3-я по номеру), выходим из макроса
If Target.Column <> 3 Then Exit Sub
nextR = Target.Row + 1
'Смотрим на колонку C, проверяем значение следующей строчки
'Если содержит текст "филиал" или "ДЗО ", вставляем еще одну строчку
If (InStr(Cells(nextR, 3), "филиал") > 0 Or InStr(Cells(nextR, 3), "ДЗО ") > 0) And Target.Value <> "" Then
Application.EnableEvents = False
Rows(nextR).Insert Shift:=xlDown
'Копируем формулы по столбцу D
Range("d" & Target.Row).Copy Range("d" & Target.Row + 1)
'Копируем формулы по столбцам R:U
Range("r" & Target.Row & ":u" & Target.Row).Copy Range("r" & Target.Row + 1 & ":u" & Target.Row + 1)
Application.EnableEvents = True
End If
End Sub
В моем первом варианте не правильно копировались формулы, здесь поправила. Если где-то еще с логикой не догнала говорите)
[p.s.]Файл пришлось немного урезать[/p.s.]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
'Если изменяем НЕ колонка C (3-я по номеру), выходим из макроса
If Target.Column <> 3 Then Exit Sub
nextR = Target.Row + 1
'Смотрим на колонку C, проверяем значение следующей строчки
'Если содержит текст "филиал" или "ДЗО ", вставляем еще одну строчку
If (InStr(Cells(nextR, 3), "филиал") > 0 Or InStr(Cells(nextR, 3), "ДЗО ") > 0) And Target.Value <> "" Then
Application.EnableEvents = False
Rows(nextR).Insert Shift:=xlDown
'Копируем формулы по столбцу D
Range("d" & Target.Row).Copy Range("d" & Target.Row + 1)
'Копируем формулы по столбцам R:U
Range("r" & Target.Row & ":u" & Target.Row).Copy Range("r" & Target.Row + 1 & ":u" & Target.Row + 1)
Application.EnableEvents = True
End If
End Sub
В моем первом варианте не правильно копировались формулы, здесь поправила. Если где-то еще с логикой не догнала говорите)
[p.s.]Файл пришлось немного урезать[/p.s.] Manyasha
ЯД: 410013299366744 WM: R193491431804
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
'Если изменяем НЕ колонка C (3-я по номеру), выходим из макроса
If Target.Column <> 3 Then Exit Sub
nextR = Target.Row + 1
'Смотрим на колонку C, проверяем значение следующей строчки
'Если содержит текст "филиал" или "ДЗО ", вставляем еще одну строчку
If (InStr(Cells(nextR, 3), "филиал") > 0 Or InStr(Cells(nextR, 3), "ДЗО ") > 0) And Target.Value <> "" Then
Application.EnableEvents = False
Rows(nextR).Insert Shift:=xlDown
'Копируем формулы по столбцу D
Range("d" & Target.Row).Copy Range("d" & Target.Row + 1)
'Копируем формулы по столбцам R:U
Range("r" & Target.Row & ":u" & Target.Row).Copy Range("r" & Target.Row + 1 & ":u" & Target.Row + 1)
Application.EnableEvents = True
End If
End Sub
В моем первом варианте не правильно копировались формулы, здесь поправила. Если где-то еще с логикой не догнала говорите)
[p.s.]Файл пришлось немного урезать[/p.s.] Автор - Manyasha
Дата добавления - 22.04.2015 в 17:33
Manyasha, с формулами все ОК!
Мне лучше бы добавление строк при внеснии в столбец Е, чем в С.
Попробовал сам переделать - все перестало работать :-(
И можно ли сделать сохранение формата ячеек? (вертикальные границы пропадают)
Manyasha, с формулами все ОК!
Мне лучше бы добавление строк при внеснии в столбец Е, чем в С.
Попробовал сам переделать - все перестало работать :-(
И можно ли сделать сохранение формата ячеек? (вертикальные границы пропадают) Leprotto
В процессе работы с Microsoft Excel у пользователей возникает необходимость внесения дополнительных данных. Для этого необходимо добавлять или копировать строки с формулами.
Кажется, это простая и понятная процедура. Но часто возникает вопрос о том, как добавить новую строку с формулами в Excel с определенными условиями. Например, без изменения адресов в формулах с относительными ссылками на ячейки и т.п. На готовых примерах рассмотрим оригинальные решения этих задач.
Как добавить новую строку с формулами в Excel?
Пускай мы добавили формулы в одну строку или в несколько. Теперь нам нужно скопировать все строки с формулами. Как это сделать при добавлении новой строки?
- Выделяем ту строку, которая содержит формулы. В нашей ситуации это строка под номером 3. Нам необходимо, чтобы остальные строки содержали в себе такие же формулы.
- Нажимаем на выделенную область правой кнопкой мышки. Из контекстного меню нужно выбрать опцию «Копировать» (или жмем CTRL+C).
- После этого следует выделить следующую одну (или несколько) нижнюю строку, куда будет вставлена новая с формулами.
- Щелкнуть по заголовку выделенной строки правой кнопкой мышки и выбрать опцию вставить скопированные ячейки (или нажать комбинацию клавиш CTRL+SHIFT+«=»).
Заметьте, значения ячеек не заменились а добавилась в середину таблицы новая строка со старыми значениями и формулами.
Как копировать без изменения формул в Excel?
Часто при копировании относительные формулы (т.е. те, в которых нет знака «$») «подставляют» другие значения. А что делать, если вы не хотите изменять значение формул?
Можно сделать все ссылки в формулах абсолютными. Такие формулы будут копироваться неизменно, без изменения данных. Чтобы сделать ссылки абсолютными, добавляем к ним знак «$».
Ставим перед буквами и перед числами знак доллара – и получаем абсолютные ссылки в формулы, значения в которых не будут изменяться при копировании.
На пример в такой таблице ссылка на наценку ($B$1) не будет изменятся при копировании на новых строках, так как она является абсолютной, а не относительной:
Но в некоторых больших и сложных таблицах слишком много формул, которые нужно скопировать и переместить. И чтобы во всех ссылках проставить знак «$» нужно потратить много времени.
В результате при копировании во всех строках ссылки формул остались неизменными.
Тем, кто постоянно работает с Microsoft Excel, часто приходится задаваться вопросом правильного копирования данных таблицы с сохранением форматирования, формул или их значений.
Как вставить формулу в таблицу Excel и сохранить формат таблицы? Ведь при решении данной задачи можно экономить вагон времени. Для этого будем использовать функцию «Специальная вставка» – это простой инструмент, который позволяет быстро решить сложные задачи и освоить общие принципы его использования. Использование этого простого инструмента позволяет вам быстро приводить все таблицы к однообразному формату и получать тот результат, который необходим.
Как скопировать таблицу с шириной столбцов и высотой строк
Допустим, у нас есть такая таблица, формат которой необходимо сохранить при копировании:
При копировании на другой лист привычным методом Ctrl+C – Ctrl+V. Получаем нежелательный результат:
Поэтому приходится вручную «расширять» ее, чтобы привести в удобный вид. Если таблица большая, то «возиться» придется долго. Но есть способ существенно сократить временные потери.
Способ1:Используем специальную вставку
- Выделяем исходную таблицу, которую нам необходимо скопировать, нажимаем на Ctrl+C.
- Выделяем новую (уже скопированную) таблицу, куда нам необходимо формат ширины столбцов и нажимаем на ячейку правой кнопкой мыши, после чего в выпадающем меню находим раздел «Специальная вставка».
- Выбираем в нем необходимый пункт напротив опции «ширина столбцов», нажимаем «ОК».
Она получила исходные параметры и выглядит идеально точно.
Способ 2: Выделяем столбцы перед копированием
Секрет данного способа в том, что если перед копированием таблицы выделить ее столбцы вместе с заголовками, то при вставке ширина каждого столбца будет так же скопирована.
Для каждого случая рационально применять свой способ. Но стоит отметить, что второй способ позволяет нам не только быстро переносить таблицу вместе с форматом, но и копировать высоту строк. Ведь в меню специальной вставки нет опции «высоту строк». Поэтому для решения такой задачи следует действовать так:
Полезный совет! Самый быстрый способ скопировать сложную и большую таблицу, сохранив ее ширину столбцов и высоту строк – это копировать ее целым листом. О том, как это сделать читайте: копирование и перемещение листов.
Вставка значений формул сохраняя формат таблицы
Специальная вставка хоть и не идеальна, все же не стоит недооценивать ее возможности. Например, как вставить значение формулы в таблицу Excel и сохранить формат ячеек.
Чтобы решить такую задачу следует выполнить 2 операции, используя специальную вставку в Excel.
Так как скопированный диапазон у нас еще находится в буфере обмена после копирования, то мы сразу еще раз вызываем специальную вставку где выбираем опцию «форматы». Жмем ОК.
Мы вставили значения формул в таблицу и сохранили форматы ячеек. Как вы догадались можно сделать и третью операцию для копирования ширины столбцов, как описано выше.
Полезный совет! Чтобы не выполнять вторую операцию можно воспользоваться инструментом «формат по образцу».
Microsoft Excel предоставляет пользователям практически неограниченные возможности для подсчета простейших функций и выполнения ряда других процедур. Использование программы позволяет устанавливать форматы, сохранять значения ячеек, работать с формулами, переносить и изменять их, таким образом, как это удобно для пользователей.
Читайте также: