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

Как посчитать весь столбец в excel

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

Способ 1 – Автосуммирование

1. Курсор установить в месте, в котором надо вывести значение подсчетов.
2. На вкладке «Главная» нажать значок Автосуммы или воспользоваться сочетанием клавиш Alt и =.

Если требуется просуммировать сразу несколько столбцов или строк, то:

1. Выделить ячейки, в которых будет отражена сумма по каждому из столбцов.
2. Нажать символ Автосуммы.
3. Enter. Итоги будут посчитаны сразу по двум столбцам.

Если требуется найти сумму вместе с несколькими дополнительными ячейками:

1. Выделить область для вывода итогов.
2. Нажать на иконку Автосуммы.
3. Диапазон автоматически определится в составе всех вышерасположенных ячеек.
4. Зажать Ctrl и выделить дополнительные области и нажать Enter.

Способ 2. Ручной ввод формулы

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

1. Курсор поставить на ячейку с будущим итогом.
2. Ввести =. С равенства начинается любая формула в Excel.
3. Левой кнопкой мыши щелкать по нужным для суммирования ячейкам, ставя между ними + и Enter.

Способ 3. Наглядное суммирование

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

1. Мышкой выделить необходимый столбец или строку.
2. Посмотреть на результат.

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

Считаем сумму в столбце в Экселе

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

Способ 1: Просмотр общей суммы

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

1. С помощью мышки выделите диапазон ячеек в столбце, сумму значений в котором требуется посчитать. В нашем примере это будут ячейки с 3 по 9 столбца G.

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

Дополнительно. Такое суммирование работает, даже если в столбце есть пустые ячейки.

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

Примечание: Слева от суммы указано количество выделенных ячеек, а также среднее значение для всех указанных в них чисел.

Способ 2: Автосумма

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

Нажмите левой кнопкой мышки (ЛКМ) по пустой ячейке, расположенной под теми, сумму которых требуется посчитать.

Кликните ЛКМ по кнопке «Сумма», расположенной в блоке инструментов «Редактирование» (вкладка «Главная»). Вместо этого можно воспользоваться комбинацией клавиш «ALT» + «=».

Убедитесь, что в формуле, которая появилась в выделенной ячейке и строке формул, указан адрес первой и последней из тех, которые необходимо суммировать, и нажмите «ENTER».

Вы сразу же увидите сумму чисел во всем выделенном диапазоне.

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

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

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

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

С помощью мышки выделите диапазон ячеек, которые требуется просуммировать, и нажмите «ENTER».

Совет: Выделить диапазон ячеек можно и немного иным способом – кликните ЛКМ по первой из них, затем зажмите клавишу «SHIFT» и кликните по последней.

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

Способ 3: Автосумма нескольких столбцов

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

Щелкните ЛКМ по той ячейке, в которую планируете выводить сумму всех значений.

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

Первый столбец (над формулой) уже будет выделен. Поэтому зажмите клавишу «CTRL» на клавиатуре и выделите следующий диапазон ячеек, который тоже должен входить в рассчитываемую сумму.

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

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

Для этого нужно просто выделить кликом ячейку для формулы, нажать по кнопке «Сумма» или на горячие клавиши, выделить первый диапазон ячеек, а затем, зажимая клавишу «CTRL», выделить все остальные «участки» таблицы. Сделав это, просто нажмите «ENTER» для отображения результата.

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

Способ 4: Суммирование вручную

Как и любая формула в Microsoft Excel, «Сумма» имеет свой синтаксис, а потому она может быть прописана вручную. Зачем это нужно? Хотя бы за тем, что таким образом вы точно сможете избежать случайных ошибок при выделении суммируемого диапазона и указании входящих в него ячеек. Более того, даже если вы ошибетесь на каком-то из шагов, исправить это будет так же просто, как банальную опечатку в тексте, а значит, не потребуется проделывать всю работу с нуля, как это иногда приходится делать.

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

Формула суммы имеет следующий синтаксис:

=СУММ(суммируемые ячейки или диапазон таковых)

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

Для диапазона: А1:A10 – это сумма 10 первых ячеек в столбце A.

Для отдельных ячеек: A2;A5;A7;A10 – сумма ячеек №2, 5, 7,10 столбца A.

Смешанное значение (диапазоны и отдельные ячейки): A1;A3:A5;A7;A9:15 – сумма ячеек №1, 3-5, 7, 9-15. При этом важно понимать, что между диапазонами и единичными ячейками не должно стоять никакого дополнительного символа-разделителя.

А теперь перейдем непосредственно к ручному суммированию значений в столбце/столбцах электронной таблицы Эксель.

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

Введите в нее следующее:

=СУММ(

после чего начните поочередно вводить в строке формул адреса суммируемых диапазонов и/или отдельных ячеек, не забывая о разделителях («:» — для диапазона, «;» — для единичных ячеек, вводить без кавычек), то есть делайте все по тому алгоритму, суть которого мы выше подробно описали.

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

Примечание: Если вы заметили ошибку в итоговой формуле (например, туда вошла лишняя ячейка или какая-то была пропущена), просто вручную введите правильное обозначение (или удалите лишнее) в строке формул. Аналогичным образом, если и когда это потребуется, можно поступить и с символами-разделителями в виде «;» и «:».

Как вы, наверное, уже могли догадаться, вместо ручного ввода адресов ячеек и их диапазонов можно просто выделять необходимые элементы с помощью мышки. Главное, не забывать при этом удерживать клавишу «CTRL», когда вы переходите от выделения одного элемента к другому.

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

Заключение

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Вычисляемые столбцы в таблицах 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.

Создание ссылки на ячейку или столбец в формуле

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

Также можно ссылаться на данные из других таблиц. Подробную информацию о ссылках на данные из других таблиц можно найти в статье Формулы: ссылки на данные из других таблиц.

Краткая информация о типах ссылок в формулах

Ниже приведена памятка по созданию собственных формул.

Элемент, на который нужно сослаться	Требуемый формат	Пример
Отдельная ячейка	Название столбца, номер строки	=Бюджет1
Название столбца содержит пробел или заканчивается на число	Заключите имя столбца в квадратные скобки.	=[Столбец A]1 =DAY([Q1]1)
Абсолютная ссылка (всегда ссылается на указанную ячейку, строку или столбец)	Введите символ «$» перед названием столбца, номером строки или обоими.	=$[Столбец A]$1 =[Столбец B]$1 =$[Столбец C]1
Несколько несвязанных ячеек	Ссылки на ячейки должны быть разделены запятыми.	=SUM(Бюджет1, Затраты4, [Прогноз прибыли]20)
Диапазон ячеек в одном столбце	Введите первую и последнюю ячейки диапазона через двоеточие «:».	=SUM(Бюджет1:Бюджет12)
Столбец целиком (вместе со всеми добавляемыми ячейками)	Название столбца через двоеточие «:».	=SUM(Бюджет:Бюджет)
Диапазон ячеек в нескольких столбцах	Введите ссылку на правую верхнюю и левую нижнюю ячейки через двоеточие «:».	=SUM(Январь1:Март5)
Отдельная ячейка, диапазон ячеек или столбцы целиком из другой таблицы	Введите название созданной ссылки в другой таблице в фигурных скобках.	=COUNT({my_sheet1 Диапазон1})

Более подробное описание типов ссылок

Ссылка на отдельную ячейку

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

Например, формула в столбце «Балансовая стоимость» в следующей таблице управления запасами умножит значение из строки 1 столбца «Цена» на значение строки 1 столбца «Запасы»:

Формула вернет значение 2 994,00 р., т. е. общую стоимость:

Ссылка на название столбца, содержащего пробелы или заканчивающегося на цифру

Если название столбца содержит пробелы, специальные знаки или цифры, вы должны заключить их в квадратные скобки во избежание неопределенности:

=[Годовой бюджет]1 + [Годовой бюджет]2

=[Q1]1 + [Q2]1

=[Риск/Проблема]5 + [Вероятность/Смягчение]5

Создание абсолютной ссылки

В некоторых ситуациях, например при ссылке на таблицу функцией VLOOKUP, бывает необходимо запретить Smartsheet автоматически обновлять ссылки на ячейки при перемещении или копировании этой формулы. Для этого нужно создать абсолютную ссылку на ячейки. (Подробная информация о функции VLOOKUP приведена в статье Ссылка функцией.)

Чтобы создать абсолютную ссылку, поставьте символ $ (доллар) перед названием столбца или номером строки в ссылке на ячейку в своей формуле. Например, если вы переместите или скопируете следующую формулу, то названия столбцов и номера строк в ссылках на ячейки не изменятся:

=$[Столбец A]$1 * $[Столбец B]$1

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

=[Столбец A]$1 * [Столбец B]$1

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

=$[Столбец A]1 * $[Столбец B]1

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

Для создания ссылки на диапазон ячеек поставьте знак : (двоеточие) между ссылками на выбранные ячейки.

Например, формула в нижней части столбца «Балансовая стоимость» в следующей таблице управления запасами сложит значения строк с 1-й по 6-ю в данном столбце:

Эта формула вернет значение «40763,75 р.», то есть итоговую балансовую стоимость:

Ссылка на столбец целиком

Можно создавать ссылки на столбец целиком, включая его все заполненные ячейки. Например, следующая формула суммирует все значения столбца «Годовой бюджет» и будет меняться по мере добавления или удаления новых строк в этом столбце:

=SUM([Годовой бюджет]:[Годовой бюджет])

ПРИМЕЧАНИЕ. Если формула введена в ячейку столбца, на который она ссылается, она не будет учитывать ту ячейку, в которую она содержится. Например, если вы введете формулу из примера выше в таблицу под названием «Годовой бюджет», формула SUM просуммирует значения всех ячеек за исключением той ячейки, в которую введена эта формула.

Ссылка на диапазон ячеек из нескольких столбцов

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

Например, формула в столбце «Общий запас» в следующей таблице управления запасами просуммирует значения строки 1 из столбцов «Запасы A», «Запасы B» и «Запасы C»:

Формула вернет значение «998», т. е. сумму запасов из трёх ячеек:

excel — как ввести формулу для всего столбца

спросил
10 лет, 5 месяцев назад

Изменено
1 год, 6 месяцев назад

Просмотрено
182к раз

Я знаю, что если я выделю весь столбец, а затем введу формулу в первый столбец, а затем нажму ctrl+enter, об этом позаботятся. Однако моя первая строка — это строка заголовка. Как еще я должен это сделать?

• Microsoft-Excel

1. Введите желаемую формулу во 2-й строке столбца, который вы хотите заполнить.
   • Обязательно используйте $ для любых ячеек, на которые ссылаются, где строка остается одинаковой для всех уравнений.
2. Выберите ячейку, содержащую формулу, и нажмите Ctrl + Shift + Вниз .
   • Это должно выбрать все ячейки в этом столбце между выбранной ячейкой и самым низом.
3. Затем нажмите Ctrl + D .
   • Это должно заполнить все выбранные ячейки формулой, которую вы ввели в ячейку 2-й строки.

Немного подробнее о знаке $.

 Пример: =ВПР(A2,$B$2:$C$21,2)
 

Здесь значение для B2:C21 будет постоянным для всех ячеек.

1

1. Скопируйте столбец
2. Щелкните в любом месте ваших данных и отформатируйте их как таблицу
   Excel 2010 -> Вкладка «Главная» -> Форматировать как таблицу
3. Вставьте столбец, измените заголовок на любой.
4. Введите формулу в первую ячейку под этим
5. Вы увидите значок освещения, нажмите на него, и он применится ко всей колонке.

Обновление на основе хорошей точки от pnuts, нет причин удалять столбец, копирование столбца сохраняет данные!

2

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

Затем, когда формула окажется в этой ячейке, нажмите Ctrl + Вниз .

Это выберет все ячейки ниже той, которую вы выбрали, пока не произойдет перерыв в статусе ячеек (если они все заполнены, то в столбце появится незаполненная или наоборот)

1

Существует еще один вариант использования =ARRAYFORMULA()

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

 =ФОРМУЛА МАССИВА(A2:A*B2:B)
 

Это имеет три преимущества :

1. Полностью пропускает первую строку (ваш вопрос)
2. Эффективно применяет формулу к каждой строке, что полезно, если вы позже решите вставить строку
3. Только одно место для изменения формулы для каждой отдельной строки

Хотя может быть не сразу очевидно, где и как вычисляются ячейки. (подсказка: ctrl +~ может поможет)

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания, политикой конфиденциальности и политикой использования файлов cookie

.

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

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

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

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

• Скопировать формулу вниз по столбцу
• Копирование формулы во весь столбец
• Копировать формулу в несмежные ячейки/диапазоны
• Как ввести формулу в несколько ячеек
• Как скопировать формулу без форматирования
• Как скопировать формулу без изменения ссылок
• Ярлыки для копирования формул в Excel

Как скопировать формулу вниз по столбцу

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

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

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

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

В приведенном выше примере, чтобы убедиться, что формула скопирована правильно, давайте выберем ячейку в столбце C, скажем, C4, и просмотрим ссылку на ячейку в строке формул. Как видно на скриншоте ниже, с формулой все в порядке — относительно строки 4, именно так, как и должно быть:

Как скопировать формулу вниз без копирования форматирования

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

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

Копировать формулу во весь столбец

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

Дважды щелкните знак «плюс», чтобы заполнить весь столбец

Чтобы применить формулу ко всему столбцу, дважды щелкните знак «плюс», а не перетаскивайте его. Для тех, кто пропустил первый раздел этого руководства, подробные шаги приведены ниже.

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

1. Введите формулу в верхнюю ячейку.
2. Поместите курсор в правый нижний угол ячейки с формулой, подождите, пока он не превратится в знак плюс, а затем дважды щелкните плюс.

Примечание. Двойной щелчок по знаку «плюс» копирует формулу вниз, если в соседних столбцах есть данные. Как только появляется пустая строка, автозаполнение прекращается. Таким образом, если на вашем листе есть пропуски, вам придется повторить описанный выше процесс, чтобы скопировать формулу под пустой строкой или перетащить маркер заполнения, как описано в предыдущих примерах:

Создайте таблицу Excel, чтобы скопировать формулу во все ячейки в столбце автоматически

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

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

Чтобы преобразовать диапазон ячеек в таблицу Excel, просто выберите все ячейки и нажмите Ctrl + T. Если вы предпочитаете визуальный способ, выберите диапазон, перейдите к Вставьте вкладку > группу Таблицы на ленте Excel и нажмите кнопку Таблица .

Совет. Если вам не очень нужна таблица Excel на вашем листе, вы можете создать ее временную, чтобы упростить работу с формулами, а затем вы можете преобразовать таблицу обратно в обычный диапазон за секунду. Просто щелкните правой кнопкой мыши таблицу и выберите Table > Convert to Range в контекстном меню.

Копировать формулу в несмежные ячейки/диапазоны

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

1. Щелкните ячейку с формулой, чтобы выбрать ее.
2. Нажмите Ctrl + C, чтобы скопировать формулу.
3. Выберите ячейку или диапазон ячеек, куда вы хотите вставить формулу (чтобы выбрать несмежные диапазоны, нажмите и удерживайте клавишу Ctrl).
4. Нажмите Ctrl + V, чтобы вставить формулу.
5. Нажмите Enter, чтобы завершить вставленные формулы.

Примечание. Ярлыки копирования/вставки копируют формулу и форматирование. Чтобы скопировать формулу без форматирования , выберите соответствующий параметр Вставить на ленте или в контекстном меню, как показано в разделе Копирование формулы Excel без форматирования.

Ввод формулы в несколько ячеек одним нажатием клавиши (Ctrl + Enter)

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

1. Выберите все ячейки, в которые вы хотите ввести формулу. Чтобы выделить несмежные ячейки, нажмите и удерживайте клавишу Ctrl.
2. Нажмите F2, чтобы войти в режим редактирования.
3. Введите формулу в одну ячейку и нажмите Ctrl + Enter вместо Enter. Вот и все! Формула будет скопирована во все выбранные ячейки, и Excel соответствующим образом скорректирует относительные ссылки на ячейки.

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

Как скопировать формулу Excel, но без форматирования

Как вы уже знаете, при копировании формулы вниз по столбцу в Excel можно использовать параметр «Заполнить без форматирования», который позволяет скопировать формулу, но сохранить существующее форматирование места назначения. клетки. Эксель Функция копирования и вставки обеспечивает еще большую гибкость в отношении параметров вставки.

1. Выберите продажу, содержащую формулу.
2. Скопируйте эту ячейку, нажав Ctrl + C. Либо щелкните правой кнопкой мыши ячейку и выберите Копировать из контекстного меню или нажмите кнопку Копировать на вкладке Главная > Буфер обмена .
3. Выберите все ячейки, в которые вы хотите скопировать формулу.
4. Щелкните правой кнопкой мыши выбранные ячейки и выберите Формулы под Варианты вставки :

Чтобы просмотреть дополнительные параметры вставки, щелкните стрелку под кнопкой Вставить на ленте. Например, вы можете выбрать Формулы и форматирование чисел , чтобы вставить только формулу и числовое форматирование, например процентный формат, денежный формат и т. п.:

Подсказка. Если вы не уверены, какой вариант вставки подходит вам лучше всего, наведите указатель мыши на разные значки, чтобы просмотреть предварительный просмотр того или иного варианта вставки.

Скопировать формулу в Excel без изменения ссылок

Формулы Excel редко встречаются в электронной таблице в одиночестве. В большинстве случаев вы вводите формулу в одну ячейку, а затем копируете ее в другие ячейки того же столбца или строки, чтобы выполнить тот же расчет для группы данных. И если ваша формула содержит относительные ссылки на ячейки (без $), Excel автоматически настраивает их так, чтобы каждая формула работала с данными в своей строке или столбце. В большинстве случаев это именно то, что вы хотите. Например, если у вас есть формула =A1*2 в ячейке B1, и вы скопируете эту формулу в ячейку B3, формула изменится на =A3*2 .

Но что, если вы хотите, чтобы Excel скопировал формулу точно , не меняя при этом ссылки на ячейки? В зависимости от конкретной задачи выберите одно из следующих решений.

Скопируйте или переместите одну формулу без изменения ссылок на ячейки

Если вам нужно скопировать или переместить только одну формулу, сделать точную копию несложно.

1. Выберите ячейку с формулой, которую вы хотите скопировать.
2. Выберите формулу в строке формул с помощью мыши и нажмите Ctrl + C, чтобы скопировать ее. Если вы хотите переместить формулу, нажмите Ctrl + X, чтобы вырезать ее.
3. Нажмите клавишу Esc, чтобы выйти из строки формул.
4. Выберите ячейку назначения и нажмите Ctrl + V, чтобы вставить туда формулу.

Как вариант, можно войти в режим редактирования и скопировать формулу в ячейку как текст:

1. Выберите ячейку с формулой.
2. Нажмите F2 (или дважды щелкните ячейку), чтобы войти в режим редактирования.
3. Выберите формулу в ячейке с помощью мыши и нажмите Ctrl + C, чтобы скопировать ее.
4. Выберите ячейку назначения и нажмите Ctrl+V. Это позволит вставить формулу точно, без изменения ссылок на ячейки, поскольку формула была скопирована как текст.

Наконечник. Чтобы быстро скопировать формулу из указанной выше ячейки без изменения ссылки, выберите ячейку, в которую вы хотите вставить формулу, и нажмите Ctrl + ‘.

Скопируйте диапазон формул без изменения ссылок на ячейки

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

Способ 1. Используйте абсолютные или смешанные ссылки на ячейки

Если вам нужно сделать точную копию формул с относительными ссылками на ячейки (например, A1), лучшим способом будет изменить их на абсолютные ссылки ($ A$1), чтобы зафиксировать ссылку на данную ячейку, чтобы она оставалась статической независимо от того, куда перемещается формула. В других случаях вам может понадобиться использовать смешанные ссылки на ячейки ($A1 или A$1) для блокировки столбца или строки. Пока нет особого смысла? Хорошо, давайте рассмотрим следующий пример.

Предположим, у вас есть таблица, которая вычисляет цены на фрукты в евро на основе цены в долларах США в столбце B и обменного курса в ячейке C2:

Как вы можете видеть на снимке экрана выше, формула включает абсолютную ссылку на ячейку ($C$2), чтобы зафиксировать обменный курс в ячейке C2, и относительную ссылку на ячейку на ячейку B5, поскольку вы хотите, чтобы эта ссылка корректировалась для каждой строки. И этот подход хорошо работает до тех пор, пока формулы остаются в столбце C.

Но давайте посмотрим, что произойдет, если вам нужно, скажем, переместить цены в евро из столбца C в столбец F. Если вы скопируете формулы обычным способом, скопировав/вставив ячейки, формула из ячейки C5 (= B5 *$C$2) изменится на = D5 *$C$2 при вставке в ячейку F5, что сделает ваши вычисления неверными!

Чтобы исправить это, просто измените относительную ссылку (B5) на смешанную ссылку $B5 (абсолютный столбец и относительная строка). Поместив знак доллара ($) перед буквой столбца, вы привязываете ссылку к столбцу B, независимо от того, куда перемещается формула.

Теперь, если вы скопируете или переместите формулы из столбца D в столбец F или любой другой столбец, ссылка на столбец не изменится на , поскольку вы заблокировали его знаком доллара ($B5).

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

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

Способ 2. Скопируйте формулы Excel без изменения ссылок через Блокнот

1. Войдите в режим просмотра формул, нажав сочетание клавиш Ctrl + `, или любым другим способом, описанным в разделе Как отобразить формулы в Excel.
2. Выберите все ячейки с формулами, которые вы хотите скопировать или переместить.
3. Нажмите Ctrl + C, чтобы скопировать формулы, или Ctrl + X, чтобы вырезать их. Используйте последний ярлык, если вы хотите переместить формулы в новое место.
4. Откройте Блокнот или любой другой текстовый редактор и нажмите Ctrl + V, чтобы вставить туда формулы. Затем нажмите Ctrl+A, чтобы выделить все формулы, и Ctrl+C, чтобы скопировать их как текст.
5. На листе Excel выберите верхнюю левую ячейку, куда вы хотите вставить формулы, и нажмите Ctrl + V.

Примечания:

• Вы можете вставлять формулы только на тот же рабочий лист , где находятся исходные формулы, если только ссылки не включают имя листа, в противном случае формулы будут нарушены.
• Рабочий лист должен быть в режиме просмотра формулы . Чтобы убедиться в этом, перейдите на вкладку Формулы > группу Аудит формул и проверьте, включена ли кнопка Показать формулы .
• После вставки формул нажмите Ctrl + `, чтобы отключить режим просмотра формул.

Метод 3. Точное копирование формул с помощью функции «Найти и заменить» в Excel

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

1. Выберите ячейки с формулами, которые вы хотите скопировать.
2. На вкладке Главная перейдите в группу Редактирование и нажмите Найти и выбрать > Заменить… Или просто нажмите Ctrl + H, что является ярлыком для запуска диалогового окна «Найти и заменить» в Excel.
3. В диалоговом окне Найти и заменить введите знак равенства (=) в поле Найти что . В поле Заменить на введите какой-либо символ или строку символов, которые не используются ни в одной из ваших формул, например ‘, # или \.
   Целью этого шага является преобразование формул в текстовые строки, что не позволит Excel изменить ссылки на ячейки в процессе копирования.

   Примечание. Не используйте звездочку (*) или вопросительный знак (?) для замены, поскольку в Excel это подстановочные знаки, и их использование может затруднить последующие шаги.

4. Нажмите кнопку Заменить все и закройте диалоговое окно Найти и заменить . Все формулы в выбранном диапазоне превратятся в текстовые строки:
5. Теперь вы можете выбрать любые ячейки, нажать Ctrl + C, чтобы скопировать их, выбрать верхнюю ячейку текущего рабочего листа , куда вы хотите вставить формулы, и нажать Ctrl + V. Поскольку Excel не интерпретирует формулы без знак равенства как формулы, они будут скопированы точно, без изменения ссылок.
6. Используйте Найти и заменить еще раз, чтобы отменить изменение. Выберите обе области, с исходными формулами и скопированные (чтобы выбрать несмежные области, нажмите и удерживайте Ctrl). Нажмите Ctrl + H, чтобы открыть диалоговое окно «Найти и заменить». На этот раз введите обратную косую черту (\) (или любой другой символ, который вы использовали для первой замены) в поле Найти что и = в поле Заменить на и нажмите кнопку Заменить все . Сделанный!

Ярлыки для копирования формулы Excel в другие ячейки

1. Скопируйте формулу вниз

Ctrl + D — скопируйте формулу из ячейки выше и измените ссылки на ячейки.

Например, если у вас есть формула в ячейке A1 и вы хотите скопировать ее в ячейку A2, выберите A2 и нажмите Ctrl + D.

2. Скопируйте формулу справа

Ctrl + R — Скопируйте формулу из ячейки слева и настройте ссылки на ячейки.

Например, если у вас есть формула в ячейке A2, и вы хотите скопировать ее в ячейку B2, выберите B2 и нажмите Ctrl + R.

Совет. Оба вышеуказанных сочетания клавиш также можно использовать для копирования формул в несколько ячеек. Хитрость заключается в том, чтобы выбрать как исходную, так и целевую ячейки, прежде чем нажимать ярлык. Например, если вы хотите скопировать формулу из A1 в следующие 9 строк, выделите ячейки A1:A10 и нажмите Ctrl + D.

3. Скопируйте формулу точно вниз

Ctrl + ‘ — ячейку выше к текущей выбранной ячейке точно и оставляет ячейку в режиме редактирования.

Это быстрый способ сделать точную копию формулы без изменения ссылок на ячейки .