Как использовать вычисляемые столбцы в таблице Excel
Вычисляемые столбцы в таблицах Excel упрощают ввод формул. Формула, введенная в одну ячейку столбца, автоматически распространяется на его остальную часть. При этом не приходится использовать заполнение или копирование. Это позволяет сэкономить время, особенно при наличии множества строк. То же самое происходит при изменении формулы: изменение также распространяется на все остальные ячейки вычисляемого столбца.
Примечание: Снимки экрана в этой статье получены в Excel 2016. Если вы используете другую версию, интерфейс может немного отличаться, но функции будут такими же.
Создание вычисляемого столбца
- Создайте таблицу. Если вы не знакомы с таблицами Excel, см. статью Общие сведения о таблицах Excel.
- Вставьте в таблицу новый столбец. Введите данные в столбец справа от таблицы, и Excel автоматически расширит ее. В этом примере мы создали новый столбец, введя «Итог» в ячейке D1.
Советы:
- Вы также можете добавить столбец на вкладке Главная. Просто щелкните стрелку на кнопке Вставить и выберите команду Вставить столбцы таблицы слева.
Введите нужную формулу и нажмите клавишу ВВОД.
В этом случае мы ввели =СУММ(, а затем выбрали столбцы Кв1 и Кв2. В результате Excel создал следующую формулу: =СУММ(Таблица1[@[Кв1]:[Кв2]]). Такие формулы называются формулами со структурированными ссылками, и их можно использовать только в таблицах Excel. Структурированные ссылки позволяют использовать одну и ту же формулу в каждой строке. Обычная формула Excel выглядела бы как =СУММ(B2:C2), и ее было бы необходимо добавить в остальные ячейки путем копирования и вставки или заполнения. Дополнительные сведения о структурированных ссылках см. в статье Использование структурированных ссылок в таблицах Excel.
При нажатии клавиши ВВОД формула будет автоматически применена ко всем ячейкам столбца, которые находятся сверху и снизу от активной ячейки. Для каждой строки используется одна и та же формула, но поскольку это структурированная ссылка, Excel знает, на что она ссылается в каждой строке.
- При копировании формулы во все ячейки пустого столбца или заполнении его формулой он также становится вычисляемым.
- Если ввести или переместить формулу в столбец, уже содержащий данные, это не приведет к автоматическому созданию вычисляемого столбца. Однако отобразится кнопка Параметры автозамены, с помощью которой можно перезаписать данные и создать вычисляемый столбец.
При вводе новой формулы, которая отличается от существующих в вычисляемом столбце, она будет автоматически применена к столбцу. Вы можете отменить обновление и оставить только одну новую формулу, используя кнопку Параметры автозамены. Обычно не рекомендуется этого делать, так как столбец может прекратить автоматически обновляться из-за того, что при добавлении новых строк будет неясно, какую формулу нужно к ним применять.
Создание исключений для вычисляемого столбца
В вычисляемый столбец можно включать формулы, отличающиеся от формулы столбца. Ячейки с такими формулами становятся исключениями и выделяются в таблице. Это позволяет выявлять и устранять несоответствия, возникшие по ошибке.
Примечание: Исключения вычисляемого столбца возникают в результате следующих операций.
- При вводе в ячейку вычисляемого столбца данных, отличных от формулы.
- Ввод формулы в ячейку вычисляемого столбца и нажатие кнопки Отменить
Примечание: Если скопированные данные содержат формулу, она заменяет данные в вычисляемом столбце.
Примечание: В этом случае исключение не помечается.
Уведомление об ошибке отображается только в том случае, если включена фоновая проверка ошибок. Если вы не видите сообщение об ошибке, откройте Файл > Параметры > Формулы и убедитесь, что флажок Включить фоновую проверку ошибок установлен.
-
Если вы используете Excel 2007, нажмите кнопку Office
Прекращение создания вычисляемых столбцов
Параметр автоматического заполнения формул для создания вычисляемых столбцов в таблице Excel по умолчанию включен. Если не нужно, чтобы приложение Excel создавало вычисляемые столбцы при вводе формул в столбцы таблицы, можно выключить параметр заполнения формул. Если вы не хотите выключать этот параметр, но не всегда при работе с таблицей хотите создавать вычисляемые столбцы, в этом случае можно прекратить автоматическое создание вычисляемых столбцов.
-
Включение и выключение вычисляемых столбцов
На вкладке Файл нажмите кнопку Параметры. Если вы используете Excel 2007, нажмите кнопку Office
и выберите Параметры Excel.
Совет: Вы можете также щелкнуть кнопку Параметры автозамены, которая отобразится в столбце таблицы после ввода формулы. Нажмите кнопку Параметры автозамены, а затем снимите флажок Создать вычисляемые столбцы, заполнив таблицы формулами, чтобы отключить этот параметр.
Если вы используете Mac, выберите Excel в главном меню, а затем щелкните Параметры > Формулы и списки > Таблицы и фильтры > Автоматически заполнять формулы.
Вычисление значений в сводной таблице
Вы также можете создавать настраиваемые вычисляемые поля со сводными таблицами, где создается одна формула, а Затем Excel применяет ее ко всему столбцу. Дополнительные сведения о вычислении значений в сводной таблице.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Скопируйте формулу, перетащив дескриптор заполнения в Excel для Mac
С помощью маркера заполнения можно быстро копировать формулы в смежные ячейки.
При перетаскивании маркера в другие ячейки Excel показывает разные результаты для каждой ячейки.
Копирование формулы с помощью маркера заполнения
- Выделите ячейку, которая содержит формулу для заполнения смежных ячеек.
- Поместите курсор в правый нижний угол, чтобы он принял вид знака плюс (+). Например:
Перетащите маркер заполнения вниз, вверх или по ячейкам, которые нужно заполнить. В этом примере на рисунке показано перетаскивание дескриптора заполнения вниз:
Когда вы отпустите маркер, формула будет автоматически применена к другим ячейкам.
Чтобы изменить способ заполнения ячеек, нажмите кнопку Параметры автозаполнения
Дополнительные сведения о копировании формул см. в статье Копирование и вставка формулы в другую ячейку или на другой лист.
- Вы также можете нажать клавиши CTRL+D, чтобы заполнить формулу вниз в столбце. Сначала выберите ячейку с формулой, которую нужно заполнить, затем выделите ячейки под ней и нажмите клавиши CTRL+D.
- Можно также нажать клавиши CTRL+R, чтобы заполнить формулу справа в строке. Сначала выберите ячейку с формулой, которую вы хотите заполнить, затем выделите ячейки справа от нее и нажмите клавиши CTRL+R.
Если заполнение не работает
Иногда заполнение работает нормально, а в других случаях это не работает так, как вы ожидаете. Поэтому важно проверить ссылки на ячейки в первой ячейке.
Ссылки на ячейки — это просто указатели на другие ячейки. Например, пусть первая ячейка содержит такую формулу:
A1 и B1 являются относительными ссылками. Это просто означает, что при заполнении формулы ссылки будут постепенно меняться с A1, B1 на A2, B2 и т. д., как показано ниже:
=СУММ(A1;B1)
=СУММ(A2;B2)
=СУММ(A3;B3)
В других случаях может не потребоваться, чтобы ссылки на другие ячейки изменялись. Например, вы хотите, чтобы первая ссылка A1 оставалась фиксированной, а B1 изменялась при перетаскивании маркера заполнения. В этом случае необходимо ввести знак доллара ($) в первой ссылке: =SUM($A$1,B1). Знак доллара заставляет Excel продолжать указывать на A1 при заполнении других ячеек. Это может выглядеть таким образом:
=СУММ($A$1;B1)
=СУММ($A$1;B2)
=СУММ($A$1;B3)
Ссылки со знаками доллара ($) называются абсолютными. При заполнении ячеек вниз ссылка на A1 остается фиксированной, но ссылку B1 приложение Excel изменяет на B2 и B3.
Есть проблемы с отображением маркера заполнения?
Если маркер не отображается, возможно, он скрыт. Чтобы отобразить его, выполните приведенные далее действия.
- В меню Excel выберите пункт Параметры.
- Щелкните Правка.
- В разделе Параметры правки установите флажок Включить маркер заполнения и перетаскивание ячеек.
Если автоматическое вычисление книги не работает, формулы не будут пересчитывать при заполнении ячеек.
Ниже описано, как его включить.
- В меню Excel выберите пункт Параметры.
- Щелкните Вычисление.
- В разделе Параметры вычисления убедитесь, что выбран параметр Автоматически .
Как применить формулу в excel ко всему столбцу
Как применить(преобразовать, видоизменить. ) формулу ко всему или диапазону (с2-с101) столбца
Например, для ячейки С2 формула =B2/A2*100-100
для С3 =B3/A3*100-100
для С4 =B4/A4*100-100
.
для С101 =B101/A101*100-100
04.10.2012 18:35:52
Двойной клик по правому нижнему углу ячейки С2.
Пользователь
Сообщений: 5208 Регистрация: 22.12.2012
04.10.2012 18:39:38
>>> Двойной клик по правому нижнему углу ячейки С2.. поправочка
когда курсор изменит вид на черный маленький крестик.
или зажимаем левую кнопку на черном крестике и просто тянем вниз.
Страницы: 1
Читают тему
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Применить формулу/вычисление к выделенным ячейкам.
× The language used in menus and dialogs (Язык, используемый в меню и диалоговых окнах) in ASAP Utilities can be set to «Русский (Russian)».
Формулы › 2. Применить формулу/вычисление к выделенным ячейкам.
С помощью этой утилиты можно быстро выполнить расчет в пределах выделенного диапазона.
Например, вы можете быстро умножить значения всех ячеек в пределах выделенного фрагмента на 10 без необходимости добавления дополнительного столбца и формулы.
Формула утилиты будет применяться к исходной формуле или значению ячейки. Например, формула =(12*2) после применения этой утилиты ASAP Utilities с заданной формулой /100 будет выглядеть так: «=(12*2)/100».
Формула будет применяться ко всем ячейкам в пределах выделенного фрагмента, кроме пустых ячеек, ячеек с ошибками, текстом или формулой массива.
В случае если новая формула приведет к ошибке, на экране появится соответствующее предупреждение, и никакие изменения не будут применены.
Пользователи неанглийской версии Excel могут вводить формулу на местном языке. Например, в голландской версии можно использовать запятую в качестве десятичного разделителя и локализированные версии формул, например «+SOM(G20:G40)» вместо английского «+SUM(G20:G40)».
Существует большое количество формул, которые вы можете ввести, например «/1000», «+4», «*2,54», «*C33».
Эта утилита также часто используется для преобразования единиц измерения, например километров в метры.
Последние 100 использованных формул сохраняются в памяти для дальнейшего использования.
По умолчанию эта утилита может быть запущена при помощи сочетания клавиш Control + Alt + I .
Снимки экрана
Пример снимка экрана: 1 Perform a quick calculation in selected cells (Вот версия на английском языке.)
Пример снимка экрана: 2 The result (Вот версия на английском языке.)
Practical tricks on how this can help you
Запуск этой утилиты
- Нажмите кнопку ASAP Utilities › Формулы › 2. Применить формулу/вычисление к выделенным ячейкам.
- Укажите сочетание клавиш: ASAP Utilities › Избранное и сочетания клавиш › Редактировать избранное и сочетания клавиш.