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

Содержание

Как вычитать числа в Экселе: формула, примеры

Среди всех арифметических действий можно выделить четыре основных: сложение, умножение, деление и вычитание. О последнем и пойдет в речь в данной статье. Давайте рассмотрим, какими методами можно выполнять данное действие в Excel.

Содержание

  1. Процедура вычитания
    • Пример 1: разность конкретных чисел
    • Пример 2: вычитание числа из ячейки
    • Пример 3: разность между числами в ячейках
    • Пример 4: Вычитание одного столбца из другого
    • Пример 5: Вычитание конкретного числа из столбца
  2. Заключение

Процедура вычитания

Смотрите также: “Как убрать формулу из ячейки в Excel с сохранением значения”

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

Само действие можно выполнить с помощью формулы, которая начинается со знака “равно” (“=”). Затем, согласно законам арифметики, пишем уменьшаемое, после него ставим знак “минус” (“-“) и в конце указываем вычитаемое. В сложных формулах вычитаемых можно быть несколько, и в этом случае, они идут следом, а между ними ставится ставится “-“. Таким образом, мы получаем результат в виде разности чисел.

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

Пример 1: разность конкретных чисел

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

  1. Переходим в свободную ячейку таблицы, в которой планируем произвести необходимые расчеты. Печатаем в ней знак “=”, после чего пишем выражение: =365-264.
  2. После того, как формула набрана, нажимаем клавишу Enter и получаем требуемый результат.

Примечание: безусловно, программа Эксель умеет работать и с отрицательными числами, поэтому, вычитание можно выполнить в обратном порядке. В этом случае формула выглядит так: =264-365.

Пример 2: вычитание числа из ячейки

Смотрите также: “Как разъединить объединенные ячейки в Excel”

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

  1. Как и в первом методе, сначала выбираем свободную ячейку, куда хотим вывести результат вычисления. В ней:
    • пишем знак “=”.
    • указываем адрес ячейки, в которой находится уменьшаемое. Сделать это можно вручную, прописав координаты с помощью клавиш на клавиатуре. Либо можно выбрать нужную ячейку, кликну по ней левой кнопкой мыши.
    • добавляем в формулу знак вычитания (“-“).
    • пишем вычитаемое (если вычитаемых несколько, добавляем их через символ “-“).
  2. После нажатия клавиши Enter, получаем результат в выбранной ячейке.

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

Пример 3: разность между числами в ячейках

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

  1. Встаем в результирующую ячейку, после чего:
    • ставим символ “=”.
    • аналогично примеру 2, указываем ячейку, содержащую уменьшаемое.
    • таким же образом добавляем в формулу ячейку с вычитаемым, не забыв перед ее адресом добавить знак “минус”.
    • если вычитаемых несколько, добавляем их подряд со знаком “-“ впереди.
  2. Нажав клавишу Enter, мы увидим результат в ячейке с формулой.

Пример 4: Вычитание одного столбца из другого

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

Программа предоставляет пользователю такую возможность, и вот как ее можно реализовать:

  1. Переходим в первую ячейку столбца, в котором планируем производить расчеты. Пишем формулу вычитания, указав адреса ячеек, которые содержат уменьшаемое и вычитаемое. В нашем случае выражение выглядит следующим образом: =С2-B2.
  2. Жмем клавишу Enter и получаем разность чисел.
  3. Остается только автоматически выполнить вычитание для оставшихся ячеек столбца с результатами. Для этого наводим указатель мыши на правый нижний угол ячейки с формулой, и после того, как появится маркер заполнения в виде черного плюсика, зажав левую кнопку мыши тянем его до конца столбца.
  4. Как только мы отпустим кнопку мыши, ячейки столбца заполнятся результатами вычитания.

Пример 5: Вычитание конкретного числа из столбца

В некоторых случаях требуется вычесть из всех ячеек столбца одно и то же конкретное число.

Данное число можно просто указать в формуле. Допустим, мы хотим вычесть из первого столбца нашей таблицы число 65.

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

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

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

Алгоритм действий в данном случае следующий:

  1. Переходим в самую верхнюю ячейку столбца для вычислений. Пишем в нем привычную формулу вычитания между двумя ячейками.
  2. Когда формула готова, не спешим нажимать клавишу Enter. Чтобы при растягивании формулы зафиксировать адрес ячейки с вычитаемым, необходимо напротив ее координат вставить символы “$” (другими словами, сделать адрес ячейки абсолютными, так как по умолчанию ссылки в программе относительные). Сделать это можно вручную, прописав в формуле нужные символы, или же, при ее редактировании переместить курсор на адрес ячейки с вычитаемым и один раз нажать клавишу F4. В итоге формула (в нашем случае) должна выглядеть так:
  3. После того, как формула полностью готова, жмем Enter для получения результата.
  4. С помощью маркера заполнения производим аналогичные вычисления в остальных ячейках столбца.

Примечание: рассмотренный выше пример можно рассмотреть в обратном порядке. Т.е. вычесть из одной и той же ячейки данные из другого столбца.

Заключение

Смотрите также: “Подбор параметра в Excel: где находится, как сделать”

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

Выборочные вычисления по одному или нескольким критериям

5092
20.02.2013
Скачать пример

Постановка задачи


Имеем таблицу по продажам, например, следующего вида:


Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина «Копейка».

Способ 1. Функция СУММЕСЛИ, когда одно условие


Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в «Копейку», например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig). Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:


Жмем ОК и вводим ее аргументы:

  • Диапазон — это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае — это диапазон с фамилиями менеджеров продаж.

  • Критерий — это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и  ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак — один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий ?????. А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву «П», а заканчивается на «В» — критерий П*В. Строчные и прописные буквы не различаются.

  • Диапазон_суммирования — это те ячейки, значения которых мы хотим сложить, т.е. нашем случае — стоимости заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много


Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для «Копейки»), то функция СУММЕСЛИ (SUMIF) не поможет, т. к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) — в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:


При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3—Условие3), и четвертую, и т.д. — при необходимости.


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

Способ 3. Столбец-индикатор


Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в «Копейку» и от Григорьева, то в ячейке этого столбца будет значение 1, иначе — 0. Формула, которую надо ввести в этот столбец очень простая:


=(A2=»Копейка»)*(B2=»Григорьев»)


Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:

Способ 4. Волшебная формула массива


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


=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)


После ввода этой формулы необходимо нажать не Enter, как обычно, а Ctrl + Shift + Enter — тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

Способ 4.

Функция баз данных БДСУММ


В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM), которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев — ячейки, содержащие условия отбора — и указать затем этот диапазон функции как аргумент:


=БДСУММ(A1:D26;D1;F1:G2)

Как суммировать столбцы в Excel

В этом руководстве показано, как суммировать столбцы в Excel 2010–2016. Попробуйте 5 различных способов суммирования столбцов: найдите сумму выбранных ячеек в строке состояния, используйте автосумму в Excel. чтобы просуммировать все или только отфильтрованные ячейки, используйте функцию СУММ или преобразуйте свой диапазон в таблицу для упрощения вычислений.

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

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

  • Как суммировать столбец в Excel одним кликом
  • Всего столбцов с функцией автосуммы Excel
  • Используйте функцию СУММ для суммирования столбца
  • Суммировать только отфильтрованные ячейки с использованием промежуточных итогов
  • Получить итоговое значение столбца в таблице Excel

Как суммировать столбец в Excel одним щелчком мыши

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

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

Как суммировать столбцы в Excel с помощью автосуммы

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

  1. Чтобы избежать каких-либо дополнительных действий, таких как выбор диапазона, щелкните первую пустую ячейку под столбцом, который необходимо суммировать.
  2. Перейдите на вкладку Главная -> Группа редактирования и нажмите кнопку AutoSum .
  3. Вы увидите, что Excel автоматически добавит функцию = SUM и выберет диапазон с вашими числами.
  4. Просто нажмите . Введите на клавиатуре, чтобы увидеть итоговый столбец в Excel.

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

Используйте функцию СУММ для суммирования столбца

Вы также можете ввести функцию СУММ вручную. Зачем вам это нужно? Суммировать только часть ячеек в столбце или указать адрес для большого диапазона вместо того, чтобы выбирать его вручную.

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

    Совет. Вы можете ввести адрес диапазона вручную, например =sum(B1:B2000) . Это полезно, если у вас есть большие диапазоны для расчета.

    Вот оно! Вы увидите суммированный столбец. Сумма появится в правильной ячейке.

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

Советы:

  • Используя функцию СУММ, вы также можете автоматически суммировать новые значения в столбце по мере их добавления и вычислять совокупную сумму.
  • Чтобы умножить один столбец на другой, используйте функцию ПРОИЗВЕД или оператор умножения. Для получения полной информации см. Как умножить два или более столбца в Excel.

Использовать промежуточный итог в Excel для суммирования только отфильтрованных ячеек

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

  1. Сначала отфильтруйте таблицу. Нажмите на любую ячейку в ваших данных, перейдите на вкладку Data и нажмите на Значок фильтра .
  2. В заголовках столбцов появятся стрелки. Нажмите на стрелку рядом с правильным заголовком, чтобы сузить данные.
  3. Снимите флажок Выбрать все и отметьте только те значения, по которым следует фильтровать. Нажмите OK , чтобы увидеть результаты.
  4. Выберите диапазон с числами для сложения и нажмите AutoSum на вкладке Home .
    Вуаля! Суммируются только отфильтрованные ячейки в столбце.

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

  • Использование промежуточных итогов в Microsoft Excel
  • Применение нескольких промежуточных итогов к таблице Excel

Преобразуйте ваши данные в таблицу Excel, чтобы получить итог для вашего столбца

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

  1. Нажмите Ctrl + T на клавиатуре, чтобы отформатировать диапазон ячеек как Таблица Excel .
  2. Появится новая вкладка Design . Перейдите на эту вкладку и установите флажок Total Row .
  3. В конец вашей таблицы будет добавлена ​​новая строка. Чтобы убедиться, что вы получили сумму, выберите число в новой строке и нажмите на маленькая стрелка вниз рядом с ним. Выберите из списка опцию Sum .
    Использование этого параметра позволяет легко отображать итоги для каждого столбца. Вы можете видеть сумму, а также многие другие функции, такие как среднее, минимальное и максимальное.

    Эта функция суммирует только видимые (отфильтрованные) ячейки. Если вам нужно рассчитать все данные, не стесняйтесь использовать инструкции из . Как суммировать столбцы в Excel с помощью автосуммы и . Введите функцию СУММ вручную, чтобы суммировать столбец .

Нужно ли вам суммировать весь столбец в Excel или суммировать только видимые ячейки, в этой статье я рассмотрел все возможные решения. Выберите вариант, который будет работать для вашей таблицы: проверьте сумму в строке состояния Excel, используйте функцию СУММ или ПРОМЕЖУТОЧНЫЙ ИТОГ, проверьте функцию автосуммы или отформатируйте данные в виде таблицы.

Если у вас есть какие-либо вопросы или трудности, не стесняйтесь оставлять комментарии. Будьте счастливы и преуспевайте в Excel!

Вас также может заинтересовать

Как применить формулу ко всему столбцу Excel — заполнить формулой соседние ячейки

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

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

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

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

Вы можете использовать инструмент «Заливка», чтобы управлять применением формулы в ячейках. Инструмент «Заливка» находится на ленте, а ниже приведены несколько простых шагов:

  • Выберите ячейку, в которую нужно поместить формулу, и добавьте формулу.
  • Нажмите клавишу ВВОД.
  • Выделите ячейки, в которых нужно применить формулу.
  • Откройте вкладку «Главная».
  • В меню «Редактирование» выберите инструмент «Заливка».
  • В появившемся списке выберите пункт «Вниз». Выберите вариант заполнения, влево и вправо.

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

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

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

  • Ниже приведена формула:

=B2*15%

  • Теперь давайте применим эту формулу ко всему столбцу C. :
  • Поместите формулу в ячейку A2:

=B2*15%

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

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

Теперь дважды щелкните левой кнопкой мыши.

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

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

Как применить формулу ко всему столбцу Excel с помощью сочетания клавиш

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

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

  • Нажмите клавишу ВВОД.
  • Перетащите мышь вниз, чтобы выделить ячейки, к которым необходимо применить формулу.
  • Нажмите CTRL + D в Windows и Command+D в Mac.
  • Вы увидите, что в выбранных ячейках теперь есть формулы.

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

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

  • Выберите ячейку, к которой нужно применить формулу.
  • Теперь нажмите CTRL + C в Windows, чтобы скопировать его. или нажмите Command + C на Mac.
  • Нажмите CTRL + V для Windows и Command + V для Mac, чтобы вставить при выборе ячеек, указанных ниже столбца. Кроме того, вы даже можете вставить его в другое место в другом столбце.
  • Вы также можете избавиться от форматирования ячейки, щелкнув правой кнопкой мыши и выбрав параметр «Формулы», указанный в разделе «Параметры вставки».

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