Содержание:
Вычисляемые столбцы в таблицах Excel упрощают ввод формул. Формула, введенная в одну ячейку столбца, автоматически распространяется на его остальную часть. При этом не приходится использовать заполнение или копирование. Это позволяет сэкономить время, особенно при наличии множества строк. То же самое происходит при изменении формулы: изменение также распространяется на все остальные ячейки вычисляемого столбца.
Примечание: Снимки экрана в этой статье получены в Excel 2016. Если вы используете другую версию, интерфейс может немного отличаться, но функции будут такими же.
Создание вычисляемого столбца
Создайте таблицу. Если вы не знакомы с таблицами Excel, см. статью Общие сведения о таблицах Excel.
Вставьте в таблицу новый столбец. Введите данные в столбец справа от таблицы, и Excel автоматически расширит ее. В этом примере мы создали новый столбец, введя "Итог" в ячейке D1.
Вы также можете добавить столбец на вкладке Главная. Просто щелкните стрелку на кнопке Вставить и выберите команду Вставить столбцы таблицы слева.
Введите нужную формулу и нажмите клавишу ВВОД.
В этом случае мы ввели =СУММ(, а затем выбрали столбцы Кв1 и Кв2. В результате Excel создал следующую формулу: =СУММ(Таблица1[@[Кв1]:[Кв2]]). Такие формулы называются формулами со структурированными ссылками, и их можно использовать только в таблицах Excel. Структурированные ссылки позволяют использовать одну и ту же формулу в каждой строке. Обычная формула Excel выглядела бы как =СУММ(B2:C2), и ее было бы необходимо добавить в остальные ячейки путем копирования и вставки или заполнения.
Дополнительные сведения о структурированных ссылках см. в статье Использование структурированных ссылок в таблицах Excel.
При нажатии клавиши ВВОД формула будет автоматически применена ко всем ячейкам столбца, которые находятся сверху и снизу от активной ячейки. Для каждой строки используется одна и та же формула, но поскольку это структурированная ссылка, Excel знает, на что она ссылается в каждой строке.
При копировании формулы во все ячейки пустого столбца или заполнении его формулой он также становится вычисляемым.
Если ввести или переместить формулу в столбец, уже содержащий данные, это не приведет к автоматическому созданию вычисляемого столбца. Однако отобразится кнопка Параметры автозамены, с помощью которой можно перезаписать данные и создать вычисляемый столбец.
При вводе новой формулы, которая отличается от существующих в вычисляемом столбце, она будет автоматически применена к столбцу. Вы можете отменить обновление и оставить только одну новую формулу, используя кнопку Параметры автозамены. Обычно не рекомендуется этого делать, так как столбец может прекратить автоматически обновляться из-за того, что при добавлении новых строк будет неясно, какую формулу нужно к ним применять.
Если вы ввели или скопировали формулу в ячейку пустого столбца и не хотите использовать новый вычисляемый столбец, нажмите Отменить 
два раза. Вы также можете дважды нажать клавиши CTRL+Z.
В вычисляемый столбец можно включать формулы, отличающиеся от формулы столбца. Ячейки с такими формулами становятся исключениями и выделяются в таблице. Это позволяет выявлять и устранять несоответствия, возникшие по ошибке.
Примечание: Исключения вычисляемого столбца возникают в результате следующих операций.
При вводе в ячейку вычисляемого столбца данных, отличных от формулы.
Ввод формулы в ячейку вычисляемого столбца и нажатие кнопки Отменить на панели быстрого доступа.
Ввод новой формулы в вычисляемый столбец, который уже содержит одно или несколько исключений.
При копировании в вычисляемый столбец данных, которые не соответствуют формуле вычисляемого столбца.
Примечание: Если скопированные данные содержат формулу, она заменяет данные в вычисляемом столбце.
При удалении формулы из одной или нескольких ячеек вычисляемого столбца.
Примечание: В этом случае исключение не помечается.
При удалении или перемещении ячейки в другую область листа, на которую ссылается одна из строк вычисляемого столбца.
Уведомление об ошибке отображается только в том случае, если включена фоновая проверка ошибок. Если вы не видите сообщение об ошибке, откройте Файл > Параметры > Формулы и убедитесь, что флажок Включить фоновую проверку ошибок установлен.
Если вы используете Excel 2007, нажмите кнопку Office 
и выберите Параметры Excel > Формулы.
Если вы используете Mac, в строке меню Excel выберите Параметры > Формулы и списки > Поиск ошибок.
Параметр автоматического заполнения формул для создания вычисляемых столбцов в таблице Excel по умолчанию включен. Если не нужно, чтобы приложение Excel создавало вычисляемые столбцы при вводе формул в столбцы таблицы, можно выключить параметр заполнения формул. Если вы не хотите выключать этот параметр, но не всегда при работе с таблицей хотите создавать вычисляемые столбцы, в этом случае можно прекратить автоматическое создание вычисляемых столбцов.
Включение и выключение вычисляемых столбцов
На вкладке Файл нажмите кнопку Параметры.
Если вы используете Excel 2007, нажмите кнопку Office и выберите Параметры Excel.
Выберите категорию Правописание.
В разделе Параметры автозамены нажмите кнопку Параметры автозамены
Откройте вкладку Автоформат при вводе.
В разделе Автоматически в ходе работы установите или снимите флажок Создать вычисляемые столбцы, заполнив таблицы формулами, чтобы включить или выключить этот параметр.
"Средства проверки правописания" > "Параметры автозамены" и снятия флажка "Создать вычисляемые столбцы, заполнив таблицы формулами"." />
Совет: Вы можете также щелкнуть кнопку Параметры автозамены, которая отобразится в столбце таблицы после ввода формулы. Нажмите кнопку Параметры автозамены, а затем снимите флажок Создать вычисляемые столбцы, заполнив таблицы формулами, чтобы отключить этот параметр.
Если вы используете Mac, выберите Excel в главном меню, а затем щелкните Параметры > Формулы и списки > Таблицы и фильтры > Автоматически заполнять формулы.
Прекращение автоматического создания вычисляемых столбцов
После ввода в столбец таблицы первой формулы нажмите отобразившуюся кнопку Параметры автозамены, а затем выберите Не создавать вычисляемые столбцы автоматически.
Вычисляемые поля можно также создавать в сводных таблицах. При этом Excel будет применять созданную формулу ко всему столбцу. Подробнее о вычислении значений в сводных таблицах.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Очень часто наблюдаю такую картину. Человек пытается "растянуть" формулу расположенную, например, в ячейке " E2 " на диапазон, скажем, " E3:E5000 ". И выглядит это очень грустно: палец на мышке затекает, глаза слезятся от проносящихся по экрану строк. А между тем эту задачу можно решить в один клик мышки.
Как это делается? Имеем такую таблицу с вбитой в ячейку "E2" формулой суммы:
Ставим курсор на ячейку с формулой и наводим мышку на правый нижний угол курсора так чтобы курсор мышки превратился в крестик:
Видео
Анимация
Чтобы увидеть видео включите в браузере GIF-Анимацию.
Теперь щелкаем левой клавишей мышки 2 раза, и формула "растягивается" на всю таблицу:
Автор: Дмитрий
Опубликована: 02.04.2012
Популярность= 123 ( +102 / -21 )
 |
Особенности копирования формул в Microsoft Excel с примерами. |
Excel стал повсеместно популярным благодаря возможности расчета ячеек в таблицах через формулы. Формулы могу быть сколь угодно сложными, а исходные данные можно менять и итоговые ячейки при этом пересчитаются автоматически. Информацию о том, как вставлять формулы в ячейки Вы можете прочитать в статье «Формулы в Excel: создание и расчет».
На практике приходится рассчитывать большие по размерам таблицы и формулы расчета регулярно повторяются. Меняются только исходные данные, которые надо брать из другой строки или столбца. Вносить в каждую ячейку формулу очень неудобно, поэтому в Excel имеется возможность копирования формул и расчет всей таблицы становится делом нескольких секунд.
Копирование формул
Рассмотрим следующий пример:
Пусть в таблице имеется три столбца: «Слагаемое 1», «Слагаемое 2» и «Сумма». Столбцы слагаемых заполнены исходными данными, а столбец «Сумма» необходимо рассчитать на основе слагаемых. На рисунке выше в примере в ячейке D4 вставлена формула расчета суммы чисел в ячейках B4 и C4. (3+5=8). Требуется рассчитать суммы в строках с пятой по девятую аналогичным образом.
Вместо того, чтобы вставлять формулу суммы в каждую ячейку столбца D в рассматриваемых строках, можно скопировать формулу, находящуюся в ячейке D4, и поместить ее во все ячейки столбца в диапазоне D5-D9.
Нажимаем правой клавишей мыши на ячейке, где уже имеется формула, и в появившемся меню выбираем пункт «Копировать».
Выделяем все ячейки столбца D, где требуется поместить формулу. Выделять можно как, как одну, так и несколько ячеек.
На выделенных пустых ячейках нажимаем правой клавишей мыши и в контекстном меню выбираем пункт «Вставить».
Выделенные ячейки заполнятся формулами, и будет произведен расчет сумм для всех строк. Причем исходные значения для расчета формулы берутся в каждой строке свои! Т.е. произойдет только копирование формулы, а не самих данных.
Распространение формул
Способ копирования формул через буфер обмена, рассмотренный выше в этой статье, позволяет вставлять формулы в любую ячейку листа или на другой лист. Но если вам необходимо вставить формулу в соседние ячейки, то существует способ еще проще – распространение формулы.
Пусть у Вас создана таблица продаж за квартал и в ней рассчитаны суммы по всем магазинам за январь и по магазину 1 за квартал:
Чтобы рассчитать суммы продаж за февраль и март можно скопировать в строке «Итого» формулы по столбцам, но проще распространить формулу за январь на февраль и март. Для этого необходимо выделить ячейку (B9), где уже стоит формула, и потянуть вправо мышкой за правый нижний угол этой ячейки (за меленький черный квадрат), а затем отпустить мышку над последним столбцом в строке «Итого» (в примере D9). Формула распространится на соседние столбцы!
То же самое можно сделать и для строк в колонке «За квартал». Выделите ячейку с формулой (E3) и потяните за правый нижний угол вниз, отпустив на последней строке (в примере E8).
Затем можно продлить распространение формулы до ячейки E9, чтобы в ней располагалась общая сумма продаж по всем магазинам за квартал.
Копирование формулы с неизменяемым значением (с абсолютными ссылками)
Иногда при копировании формулы в Экселе требуется, чтобы программа не изменяла одно из значений ячейки, а другое менялось по строке или столбцу. Для этого перед координатой ячейки, которую необходимо зафиксировать в формуле (Например, E2), нужно поставить знак $ (доллар) перед координатой строки и столбца: $E$2. Можно устанавливать фиксацию только по координате столбца $E2 или только строки E$2. Тогда при копировании формулы меняться будет только незафиксированная координата.
В качестве практического примера можно привести таблицу с выручкой за месяц по магазинам, где требуется подсчитать, какой процент от общей выручки приносит каждый магазин.
Для главного склада в ячейку D3 вводим формулу для расчета процента выручки от общей суммы «Итого»: (C3/$C$8)*100. Чтобы дробные значения не выглядели слишком длинно, помещаем имеющуюся формулу в формулу округления до второго знака после запятой: ОКРУГЛ((C3/$C$8)*100; 2).
У нас зафиксирована ячейка C8 по строке и столбцу, т.е. при копировании формулы координаты ячейки меняться не будут и где бы ни находилась формула, для расчета будет подставлено значение ячейки C8. Далее можно скопировать (или распространить) формулу по столбцу D до ячейки D8.
Таким образом, в последнем столбце оказывается процент от общей выручки продаж для каждого магазина.