Как задать в экселе формулу для всего столбца: Как использовать вычисляемые столбцы в таблице Excel

Содержание

Как использовать вычисляемые столбцы в таблице Excel


Excel

Импорт и анализ данных

Таблицы



Таблицы

Как использовать вычисляемые столбцы в таблице 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. Если вы используете другую версию, интерфейс может немного отличаться, но функции будут такими же.

Создание вычисляемого столбца


  1. Создайте таблицу. Если вы не знакомы с таблицами Excel, см. статью Общие сведения о таблицах Excel.

  2. Вставьте в таблицу новый столбец. Введите данные в столбец справа от таблицы, и Excel автоматически расширит ее. В этом примере мы создали новый столбец, введя «Итог» в ячейке D1.

    Советы: 

    • Вы также можете добавить столбец на вкладке Главная. Просто щелкните стрелку на кнопке Вставить и выберите команду Вставить столбцы таблицы слева.

  3. Введите нужную формулу и нажмите клавишу ВВОД.

    В этом случае мы ввели =СУММ(, а затем выбрали столбцы Кв1 и Кв2. В результате Excel создал следующую формулу: =СУММ(Таблица1[@[Кв1]:[Кв2]]). Такие формулы называются формулами со структурированными ссылками, и их можно использовать только в таблицах Excel. Структурированные ссылки позволяют использовать одну и ту же формулу в каждой строке. Обычная формула Excel выглядела бы как =СУММ(B2:C2), и ее было бы необходимо добавить в остальные ячейки путем копирования и вставки или заполнения.

    Дополнительные сведения о структурированных ссылках см. в статье Использование структурированных ссылок в таблицах Excel.

  4. При нажатии клавиши ВВОД формула будет автоматически применена ко всем ячейкам столбца, которые находятся сверху и снизу от активной ячейки. Для каждой строки используется одна и та же формула, но поскольку это структурированная ссылка, Excel знает, на что она ссылается в каждой строке.

Примечания: 

  • При копировании формулы во все ячейки пустого столбца или заполнении его формулой он также становится вычисляемым.

  • Если ввести или переместить формулу в столбец, уже содержащий данные, это не приведет к автоматическому созданию вычисляемого столбца. Однако отобразится кнопка Параметры автозамены, с помощью которой можно перезаписать данные и создать вычисляемый столбец.

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

  • Если вы введили или скопировали формулу в ячейку пустого столбца и не хотите сохранять новый вычисляемого столбца, нажмите кнопку Отменить два раза. Вы также можете дважды нажать клавиши CTRL+Z.



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

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

  • При вводе в ячейку вычисляемого столбца данных, отличных от формулы.

  • Ввод формулы в ячейку вычисляемого столбца и нажатие кнопки Отменить на панели быстрого доступа.

  • org/ListItem»>

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

  • При копировании в вычисляемый столбец данных, которые не соответствуют формуле вычисляемого столбца.

    Примечание: Если скопированные данные содержат формулу, она заменяет данные в вычисляемом столбце.

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

    Примечание: В этом случае исключение не помечается.

  • При удалении или перемещении ячейки в другую область листа, на которую ссылается одна из строк вычисляемого столбца.

Уведомление об ошибке отображается только в том случае, если включена фоновая проверка ошибок. Если вы не видите сообщение об ошибке, откройте Файл > Параметры > Формулы и убедитесь, что флажок Включить фоновую проверку ошибок установлен.

  • Если вы используете Excel 2007, нажмите кнопку Office и выберите Параметры Excel > Формулы.

  • Если вы используете Mac, в строке меню Excel выберите Параметры > Формулы и списки > Поиск ошибок.


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


  • Включение и выключение вычисляемых столбцов

    1. На вкладке Файл нажмите кнопку Параметры.

      Если вы используете Excel 2007, нажмите кнопку Office и выберите Параметры Excel.

    2. org/ListItem»>

      Выберите категорию Правописание.

    3. В разделе Параметры автозамены нажмите кнопку Параметры автозамены

    4. Откройте вкладку Автоформат при вводе.

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

      Совет: Вы можете также щелкнуть кнопку Параметры автозамены, которая отобразится в столбце таблицы после ввода формулы. Нажмите кнопку Параметры автозамены, а затем снимите флажок Создать вычисляемые столбцы, заполнив таблицы формулами, чтобы отключить этот параметр.

    Если вы используете Mac, выберите Excel в главном меню, а затем щелкните Параметры > Формулы и списки > Таблицы и фильтры > Автоматически заполнять формулы.


  • Прекращение автоматического создания вычисляемых столбцов

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


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

Дополнительные сведения


Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.



См. также



Общие сведения о таблицах Excel


Форматирование таблицы Excel


Изменение размера таблицы путем добавления или удаления строк и столбцов


Данные итогов в таблице Excel


Как протянуть формулу в Excel: 5 способов продлить формулу до конца таблицы

Наверх

22.05.2021

Автор: CHIP

1 звезда2 звезды3 звезды4 звезды5 звезд

Как в Экселе протянуть формулу по строке или столбцу: 5 способов

5

5

1

2

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

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

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

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

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

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

  • Нажать на крестик и, удерживая его, протянуть формулу в нужном направлении. В указанном примере — вниз.

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

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

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

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

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

  • Навести курсор на правый нижний угол, чтобы он приобрел форму черного крестика.

  • Кликнуть два раза по нижнему правому углу ячейки. Результатом станет автоматическое распространение формулы по столбцу с соответствующим изменением аргументов.

Стоит отметить, что автоматическое протягивание выполняется только до первой пустой ячейки. И если столбец был прерван, действия придется повторить для следующего диапазоне.

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

  • Советы

    Excel: 10 формул для работы в офисе

3 Протягивание без изменения ячеек в формуле

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

Для распределения без изменения адреса ячейки выполняются те же действия, что и при обычном протягивании или автоматическом копировании. Но при вводе формулы следует зафиксировать адреса, которые не будут меняться. Для этого используются символы доллара — $. Если в каждом новом пункте столбца при расчетах используется одна и та же ячейка, значки надо будет поставить и перед номером строки, и перед литерой, которая указывает на колонку. Как в примере: $G$6.

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

4 Простое копирование

Еще один способ представляет собой не совсем протягивание, а копирование. Но только более простое и позволяющее выделить конкретный диапазон, а не доверять такое выделение компьютеру. Процесс распределения требует выполнить следующие действия:

  • Записать в одну из крайних ячеек строки или столбца нужную формулу и нажать Enter.

  • Скопировать значение функции — с помощью контекстного меню, иконки на панели или комбинации клавиш Ctrl + C.

  • Установить курсор в противоположную часть столбца или строки.

  • Нажать на клавиши Ctrl + Shift + «Стрелка вверх». Результатом становится выделение нужного диапазона, даже если на этом участке столбца будет несколько сотен или тысяч пунктов.

  • Вставить формулу. Самый простой способ сделать это — нажать комбинацию Ctrl + V.

Результатом будет такое же распределение функции по столбцу, как и при использовании способа №2. Но в отличие от него здесь можно выделить только часть диапазона. Или, наоборот, продлить такое протягивание дальше даже при наличии пустых строк. Правда, во втором случае лишнее значение придется удалить вручную.

Эта небольшая хитрость подходит и для распределения вдоль строки. В этом случае вместо комбинации Ctrl + Shift + «Стрелка вверх» придется нажать Ctrl + Shift + «Стрелка влево» (или вправо, если копируемая формула находится в крайнем левом столбце).

  • Эксплуатация

    Как в Экселе посчитать сумму определенных ячеек

5 Протягивание формул в таблице Excel

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

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

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

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

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

  • Лучшие веб-камеры для дома и офиса: рейтинг 2021 года

  • Нумерация страниц в Опен Офис: простая инструкция

Автор

CHIP

Была ли статья интересна?

Поделиться ссылкой


Нажимая на кнопку «Подписаться»,
Вы даете согласие на обработку персональных данных

Рекомендуем

Реклама на CHIP
Контакты

Заполнить формулой соседние ячейки

Excel для Microsoft 365 Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Дополнительно. ..Меньше

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

Выполните следующие действия, чтобы заполнить формулу и выбрать применимые параметры:

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

  2. Перетащите маркер заполнения по ячейкам, которые вы хотите заполнить.

    Если вы не видите дескриптор заполнения, возможно, он скрыт. Чтобы отобразить его снова:

      org/ItemList»>

    1. Нажмите Файл > Опции

    2. Нажмите Дополнительно .

    3. В разделе Параметры редактирования установите флажок Включить дескриптор заполнения и перетаскивание ячеек .

  3. Чтобы изменить способ заполнения выделения, щелкните маленький значок Параметры автозаполнения , который появляется после завершения перетаскивания, и выберите нужный вариант.

Дополнительные сведения о копировании формул см. в разделе Копирование и вставка формулы в другую ячейку или лист.

Заполнение формулами соседних ячеек

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

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

  2. Щелкните Главная > Заполнить и выберите Вниз , Вправо , Вверх или Влево .

    Сочетание клавиш:     Вы также можете нажать Ctrl+D, чтобы заполнить формулу в столбце снизу, или Ctrl+R, чтобы заполнить формулу справа в строке.

Включить расчет рабочей книги

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

Вот как это можно включить:

  1. Щелкните Файл > Параметры .

  2. Нажмите Формулы .

  3. В разделе Расчет рабочей книги выберите Автоматически .

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

  2. Перетащите маркер заполнения вниз или вправо от столбца, который вы хотите заполнить.

Сочетание клавиш: Вы также можете нажать Ctrl+D, чтобы заполнить формулой ячейку вниз в столбце, или Ctrl+R, чтобы заполнить формулу справа в строке.

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Как применить формулу ко всему столбцу в Excel (5 простых способов)

Формулы — это жизнь и кровь электронных таблиц Excel. И в большинстве случаев вам не нужна формула только в одной ячейке или паре ячеек.

В большинстве случаев вам потребуется применить формулу ко всему столбцу (или к большому диапазону ячеек в столбце).

В Excel есть несколько способов сделать это несколькими щелчками мыши (или сочетанием клавиш).

Давайте посмотрим на эти методы.

В этом учебном пособии рассматриваются:

Двойным щелчком дескриптора автозаполнения

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

Предположим, у вас есть набор данных, как показано ниже, где вы хотите рассчитать комиссию для каждого торгового представителя в столбце C (где комиссия будет составлять 15% от стоимости продажи в столбце B).

Формула для этого будет:

 =B2*15%

Ниже показано, как применить эту формулу ко всему столбцу C:

  1. В ячейку A2 введите формулу: =B2*15%
  2. Выбрав ячейку, вы увидите маленький зеленый квадрат в нижней правой части выделения.
  3. Наведите курсор на маленький зеленый квадрат. Вы заметите, что курсор изменится на знак «плюс» (это называется маркером автозаполнения)
  4. Дважды щелкните левой кнопкой мыши

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

Чтобы это работало, в соседнем столбце не должно быть данных и в нем не должно быть пустых ячеек. Если, например, в столбце B есть пустая ячейка (скажем, ячейка B6), то этот двойной щелчок с автозаполнением применит формулу только до ячейки C5

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

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

Путем перетаскивания маркера автозаполнения

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

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

Ниже приведены шаги для этого:

  1. В ячейке A2 введите формулу: =B2*15%
  2. Выбрав ячейку, вы увидите маленький зеленый квадрат в нижней правой части окна. selection
  3. Поместите курсор на маленький зеленый квадрат. Вы заметите, что курсор изменится на знак плюса
  4. Удерживая левую клавишу мыши, перетащите его в ячейку, к которой вы хотите применить формулу
  5. Когда закончите, отпустите клавишу мыши

Использование параметра «Заполнить вниз» (он находится на ленте)

Другой способ применить формулу ко всему столбцу — использовать параметр «Заполнить вниз» на ленте.

Чтобы этот метод работал, вам сначала нужно выбрать ячейки в столбце, где вы хотите иметь формулу.

Ниже приведены шаги для использования метода заполнения:

  1. В ячейку A2 введите формулу: =B2*15%
  2. Выберите все ячейки, в которых вы хотите применить формулу (включая ячейку C2)
  3. Перейдите на вкладку «Главная»
  4. В группе редактирования щелкните значок «Заполнить»
  5. Нажмите «Заполнить вниз»

Вышеуказанные шаги берут формулу из ячейки C2 и заполняют ею все выбранные ячейки

Добавление Заполнить на панели быстрого доступа

Если вам нужно часто использовать параметр заполнения, вы можете добавить его на панель быстрого доступа, чтобы вы могли использовать его одним щелчком мыши (и он всегда виден на экране) .

T0 добавьте его на панель быстрого доступа (QAT), перейдите к параметру «Заполнить вниз», щелкните его правой кнопкой мыши и выберите «Добавить на панель быстрого доступа»

Теперь вы увидите значок «Заполнить вниз» в QAT.

Использование сочетания клавиш

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

 CONTROL + D (удерживайте клавишу управления, а затем нажмите клавишу D)

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

  1. В ячейке A2 введите формулу: =B2*15%
  2. Выберите все ячейки, в которых вы хотите применить формулу (включая ячейку C2 )
  3. Удерживайте клавишу Control, а затем нажмите клавишу D

Использование формулы массива

Если вы используете Microsoft 365 и имеете доступ к динамическим массивам, вы также можете использовать метод формулы массива, чтобы применить формулу ко всему столбцу .

Предположим, у вас есть набор данных, как показано ниже, и вы хотите рассчитать комиссию в столбце C.

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

 =B2:B15*15%

Это массив формула, которая вернет 14 значений в ячейке (по одному для B2:B15). Но поскольку у нас есть динамические массивы, результат не будет ограничен одной ячейкой и будет распространяться на весь столбец.

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

Но если это не так, возможно, это не лучший способ скопировать формулу во весь столбец

Путем копирования и вставки ячейки

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

Ниже приведены шаги для этого:

  1. В ячейке A2 введите формулу: =B2*15%
  2. Скопируйте ячейку (используйте сочетание клавиш Control + C в Windows или Command + C в Mac)
  3. Выберите все ячейки, к которым вы хотите применить ту же формулу (за исключением ячейки C2)
  4. Вставьте скопированную ячейку (Control + V в Windows и Command + V в Mac)

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

Например, если ячейка C2 имеет синий цвет, все рассмотренные до сих пор методы (кроме метода формулы массива) будут не только копировать и вставлять формулу во весь столбец, но и вставлять форматирование (например, цвет ячейки, размер шрифта, полужирный/курсив)

Если вы хотите применить только формулу, а не форматирование, выполните следующие действия:

  1. В ячейку A2 введите формулу: =B2*15%
  2. Скопируйте ячейка (используйте сочетание клавиш Control + C в Windows или Command + C в Mac)
  3. Выберите все ячейки, к которым вы хотите применить одну и ту же формулу
  4. Щелкните правой кнопкой мыши на выделенном фрагменте
  5. В появившихся параметрах нажмите «Специальная вставка»
  6. В диалоговом окне «Специальная вставка» нажмите Параметр «Формулы»
  7. Нажмите «ОК».

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

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