Как сложить каждую 2 ю 3 ю и n ю ячейку в excel

Трюк №69. Как в Excel 2010 просуммировать каждую вторую, третью или n-ую строку или ячейку

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

В Excel не предусмотрено стандартной функции, которая может суммировать каждую n-ю ячейку или строку. Однако можно выполнить эту задачу несколькими различными способами. Все эти подходы основаны на функциях СТРОКА (ROW) и ОСТАТ (MOD).

Функция СТРОКА (ROW) возвращает номер строки для заданной ссылки на ячейку: ROW(reference), в русской версии Excel СТРОКА(ссылка).
Функция ОСТАТ (MOD) возвращает остаток от деления числа на делитель: MOD(number;divisor), в русской версии Excel ОСТАТ(число;делитель).

Поместите функцию СТРОКА (ROW) в функцию ОСТАТ (MOD) (чтобы передать числовой аргумент), разделите на 2 (чтобы суммировать каждую вторую ячейку) и проверьте, не равен ли результат нулю. Если это так, ячейка суммируется. Эти функции можно использовать разнообразнейшими способами — некоторые обеспечат лучший результат, чем другие. Например, формула массива для суммирования каждой второй ячейки в диапазоне $А$1:$А$100 могла бы выглядеть так: =SUM(IF(MOD(ROW($A$1:$А$500);2)=0;$А$1:$А$500;0)) , в русской версии Excel =СУММ(ЕСЛИ(ОСТАТ(СТРОКА($А$1:$А$500);2)=0;$А$1:$А$500;0)) .

Вот другая формула, представляющая собой чуть лучший выбор: =SUMPRODUCT((MOD(ROW($A$1:$A$500);2)=0)*($A$1:$A$500)) , в русской версии Excel =СУММПРОИЗВ((ОСТАТ(СТРОКА($А$1:$А$500);2)=0)*($А$1:$А$500)) .

Нужно, однако, помнить, что эта формула вернет ошибку #ЗНАЧ! (#VALUE!), если какие-либо ячейки диапазона содержат не числа, а текст. Эта формула, хотя и не является фактически формулой массива, также замедляет работу Excel, если использовать ее слишком много раз или если каждый раз она ссылается на большой диапазон.

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

В ячейку Е1 введите слово Criteria. В ячейку Е2 введите следующую формулу: =MOD(ROW(A2)-$C$2-1;$C$2)=0 , в русской версии Excel =ОСТАТ(СТРОКА(А2)-$С$2-1;$С$2)=0 . Выделите ячейку С2 и выберите команду Данные → Проверка (Data → Validation).

В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) введите 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Удостоверьтесь, что установлен флажок Список допустимых значений (In-Cell), и щелкните на кнопке ОК. В ячейке С1 введите текст SUM every…. В любой ячейке, кроме строки 1, введите следующую формулу: =DSUM($A:$A;1;$E$1:$E$2) , в русской версии Excel =БДСУММ($А:$А;1;$Е$1:$Е$2) .

В ячейке непосредственно над той, где вы ввели функцию БДСУММ (DSUM), введите текст ="Summing Every" & $С$2 & CHOOSE($C$2;"st";"nd";"rd";"th";"th";"th";"th";"th";"th";"th") & "Cell" . Теперь осталось только выбрать нужное число в ячейке С2, а остальное сделает функция БДСУММ (DSUM).

При помощи функции БДСУММ (DSUM) можно суммировать ячейки через указанный вами интервал. Функция БДСУММ (DSUM) намного эффективнее, чем формула массива или функция СУММПРОИЗВ (SUMPRODUCT). Хотя настройка занимает немного больше времени, это тот случай, когда тяжело в ученье, легко в бою.

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

Excel – офисная программа от компании Microsoft, которая предназначена для работы с данными в таблицах. С ее помощью можно удобно и быстро проводить операции с числовым и текстовым содержанием ячеек, включая подсчет суммы. Узнать сумму в Экселе возможно несколькими способами, которые рассмотрим в статье.

  1. 1 Как в Экселе посчитать сумму определенных ячеек
  2. 2 Формула суммы в Excel
  3. 3 Функция СУММ в Excel
  1. 3.1 СУММ с помощью горячих клавиш
  • 4 Автосумма в Excel
  • 5 Сумма с разных листов
  • 6 Подсчет суммы в Excel с помощью таблицы
  • Как в Экселе посчитать сумму определенных ячеек

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

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

    Сложение ячеек Excel выделением

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

    Формула суммы в Excel

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

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

    Формула суммы в Excel

    Напечатайте в поле со знаком «=» после адреса ячейки (B3 в примере) «+», затем выберите ячейку для подсчета, кликнув по ней. Проделывайте это с каждым полем, данные которого требуются для сложения.

    Сложение ячеек Excel формулой

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

    Простое сложение в Excel

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

    Функция СУММ в Excel

    В программе Excel используются массивы, или данные, которые объединены в группы. Ими являются таблицы с различными значениями. Чтобы узнать сумму целого массива или нескольких больших массивов, используется функция «СУММ»:

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

    Функция СУММ в Excel

    Откроется окно мастера функций. Выделите пункт «СУММ», затем кликните по кнопке «ОК». Чтобы быстро отыскать нужную функцию, можно воспользоваться специальной поисковой строкой.

    Функция СУММ в Excel

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

    Функция СУММ в Excel

    В выделенной ячейке появится сумма выбранного массива, а в строке ввода – функция. Если нужно добавить еще один диапазон данных, то для этого необходимо в окне «Аргументы функции» в строке «Число 2» его определить, затем нажать на «ОК». В выбранной ячейке к ранее полученному результату прибавятся новые данные.

    Функция СУММ в Excel

    Программа Excel позволяет прописать функцию самостоятельно без использования дополнительных диалоговых окон. Чтобы это сделать, выберите нужную пустую ячейку и в строке формул вручную пропишите функцию с диапазоном. Например, =СУММ(B5:B7) , чтобы посчитать итог продаж за четверг, пятницу и субботу.

    Функция СУММ в Excel

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

    СУММ с помощью горячих клавиш

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

    1. Выберите пустую ячейку, в которую будет вписана сумма.
    2. Нажмите и удерживайте клавишу Alt , затем один раз нажмите на клавишу «равно» = .
    3. Отпустите Alt . В выделенную ячейку будет вставлена функция СУММ .
    4. Нажмите Enter , чтобы Excel подсчитал выбранный диапазон.

    Данное сочетание клавиш работает на Windows. Для операционной системы MacOS функция активируется одновременным нажатием клавиш Shift + Cmd + T .

    В программе Excel предусмотрено три разновидности функции СУММ, которые применяются в зависимости от цели. Помимо расчета результата суммирования выбранных ячеек или диапазона ячеек, есть также сложение по условию: СУММЕСЛИ и СУММЕСЛИМН.

    Автосумма в Excel

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

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

    Автосумма в Excel

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

    Автосумма в Excel

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

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

    Сумма с разных листов

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

    1. Выберите пустую ячейку на первом листе, затем перейдите в мастер функций так, как описывалось выше.
    2. В окне «Аргументы функции» поставьте курсор в строку Число2, затем перейдите на другой лист программы.
    3. Выделите нужные для подсчета ячейки и кликните по кнопке подтверждения.

    Сумма с разных листов Excel

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

    Подсчет суммы в Excel с помощью таблицы

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

      Выделите необходимые данные и преобразуйте их в таблицу, нажав на кнопку «Форматировать как таблицу» на панели инструментов. Выберите нужный стиль.

    Форматировать как таблицу Excel

  • В появившемся окне подтвердите выбранный диапазон ячеек нажатием на кнопку «ОК».
  • Перейдите на вкладку «Конструктор», нажмите на кнопку «Параметры стилей таблиц» и поставьте галочку напротив пункта «Строка итогов».
  • Под отформатированной таблицей появится новая строка, в которой отобразится сумма, автоматически рассчитанная программой.
  • Строка итогов в Excel

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

    Ссылка на основную публикацию