Суммирование в Excel
Суммирование это наиболее востребованная задача не только в Excel, но и при проведении любых расчетов. Следует знать, что существует значительное количество способов как можно просуммировать числа в Excel.
Знание о том, как произвести суммирование позволит Вам более эффективно работать в Excel.
В этой небольшой заметке дается краткое представление о некоторых возможностях по использованию суммирования в Excel. Ознакомившись с ними, Вы сможете получить сумму быстро и применив именно тот вид суммирования, который более уместен в разных ситуациях.
Возможно, для кого-то это банальность, но, тем не менее, следует сказать о том, что для получения корректного (подчеркну – «корректного») результата суммирования обязательно должно быть выполнено одно существенное условие: наличие данных пригодных для суммирования.
Те, кто делал выгрузки из каких-либо баз данных или же копировал данные из Интернет с последующей целью что-то посчитать, сталкивались с ситуацией, когда невозможно это сделать. Так происходит, прежде всего, по той причине, что Excel не воспринимает данные как числовые значения. Поэтому, прежде чем что-то суммировать следует удостовериться, что мы имеем дело с числовыми значениями (проще говоря, с цифрами), так как только они могут быть просуммированы.
Итак, если данные находящиеся на листе (или листах) книги Excel воспринимаются программой как числа (числовые данные), то их можно просуммировать, в противном случае их придется дополнительно обработать (подготовить), но в рамках этой статьи мы не будем рассматривать как это можно сделать. Вы всегда можете обратиться (через форму обратной связи) и получить исчерпывающую консультацию.
Самый первый способ, который также прост как «2+2», заключается в суммировании формулой когда явно указывается константа (прописывается вручную) или идет ссылка на ячейку (возможна также комбинация того и другого).
Хочется подчеркнуть, что получение суммы в ячейке немыслимо, без применения формулы, которая, как известно, должна начинаться со знака «равно». Эта азбучная истина, но мы начнем именно с нее: любая формула в Excel (и суммирование тут не исключение) начинается с «=», т.е. со знака «равно». Кавычки, конечно, в самой формуле не нужны. Непосредственно после них идет формула, о чем и пойдет речь далее.
Самый лучший способ понять как что-то «работает» это рассмотреть примеры, которые демонстрируют различные вариации проведения операции суммирования.
Далее рассмотрим примеры суммирования в Excel констант, ячеек, диапазонов…
Все примеры пронумерованы, по ходу текста иногда идет ссылка на номер. Номер формулы с примером суммирования указан в квадратных скобках [] и он не является частью формулы, то есть при копировании (если Вы захотите попробовать эту формулу у себя в Excel) следует выделять и копировать до символов » [» (не включительно).
=1+22 [1]
=22+3+»14″+108 [2]
=0,25+3,348+200+0,0048 [3]
=-4+25 [4]
=78+-18,6+14+(-45) [5]
Комментарии к приведенным выше примерам с [1] по [5]:
— количество суммируемых чисел может быть любым;
— суммировать можно целые, нецелые числа при этом они могут быть и отрицательные и положительные (как видно в примере [5] несколько чисел — отрицательные);
— числа можно брать в скобки (делается это, например, для того, чтобы визуально отделить отрицательные числа от положительных);
— следует обратить внимание на пример в котором одно из чисел взято в кавычки. Несмотря на это Excel, тем не менее, корректно посчитает сумму с числом 14 включительно.
Отдельно необходимо сказать, что после суммирования Вы можете получить не совсем тот результат, который ожидали увидеть. Так, в примере [3] вместо ожидаемого 203,6028 в ячейке может отображаться число 203,60. Это связано с форматами ячеек, однако, в рамках этой статьи о суммировании, мы не будем на этом останавливаться.
Суммировать можно не только константы, как в примерах выше, но и значения, которые находятся в ячейках, то есть формула будет такой:
=A2+F2+B5 [6]
(важно: ячейки участвующие в расчетах в такого рода формуле должны содержать числа, если это условие не будет соблюдено, то формула вернет значение ошибки: #ЗНАЧ!, #Н/Д, #ССЫЛКА!, а также может быть даже #ИМЯ?)
Как это не парадоксально, но в процессе суммирования можно вычитать, вот пример такой формулы (предполагается, что во всех ячейках числа положительные):
=D3+E4+-F7+H10 [7]
В этой формуле от суммы ячеек D3, E4, H10 будет отминусовано значение ячейки F7
Суммировать можно ячейки не только в пределах одного листа, но также используя данные находящиеся на других листах или даже в других книгах…
Пример суммирования значений текущего листа и других листов
=H6+H8+H11+Лист2!B2+Лист3!B3 [8]
Пример суммирования значений из другой книги:
=K5+5+[Книга3]Лист1!D2 [9]
Если скомбинировать все возможные из упомянутых выше вариантов, то формула позволяющая посчитать сумму, может иметь такой вид:
=B2+16+-N4+Лист2!D2+[Книга10]Лист8!F15 [10]
То есть суммируется константа (число 16), а также еще 4 значения — два из которых находится на текущем листе, одно на Лист2 текущей книги и одно на Лист8 в Книга10 (Книга10 –это некий файл, который в момент написания формулы открыт одновременно с текущим файлом).
Также следует отдельно сказать, что если в параметрах Excel проставлена птичка в «Стиль ссылок R1C1», то формулы суммирования будут иметь немного другой вид.
Так, например, формула с порядковым номером [7] преобразуется в такую:
=R[-11]C[-4]+R[-10]C[-3]+-R[-7]C[-2]+R[-4]C [11]
(предполагается, что формула находится в ячейке H14)
В рассмотренных примерах суммирование производилось с использованием математического оператора – знака «плюс» («+»), однако, в большинстве случаев, при проведении больших расчетов используют формулы (функции).
Базовая функция для суммирования ячеек это функция СУММ()
Основное ее достоинство, что она позволяет суммировать числовые значения находящиеся в диапазоне (или диапазонах) ячеек. Далее приводится наиболее простой пример функции суммирования:
=СУММ(D1:D11) [12]
Такая формула просуммирует все числовые значения из диапазона D1:D11. При этом, если в диапазоне встретятся текстовые значения, то они будут проигнорированы. Это очень важное свойство функции СУММ()
Еще вариации использования функции СУММ
=СУММ(B2:B9;50) [13]
(суммирует данные диапазона и константу)
=СУММ(A1:B6;D1:D11) [14]
(суммируется два диапазона, один из которых двумерный, а второй одномерный)
Допустимо и такое использование функции СУММ
=СУММ(2+14+25+36+88;100;-A10;D7:D10) [15]
Формула представляет из себя симбиоз суммы констант, значения из ячейки (причем значение как бы умножается на -1), а также 4 значений из диапазона D7:D10
Несмотря на то, что Excel предлагает довольно широкие возможности просуммировать данные с помощью формул с использованием оператора суммирования или же используя функцию (функция СУММ) существует также способ узнать сумму ячеек не прибегая ни к плюсованию, ни к формулам.
Делается это довольно просто: выделяем нужные ячейки и смотрим вниз, в так называемую «Строку состояния». Там будет показана общая сумма всех выделенных (числовых) ячеек.
Какие еще есть нюансы, связанные с суммированием
Как было сказано выше суммирование это наиболее востребованное действие при проведении расчетов, подсчетов, калькуляций и прочих манипуляций с цифрами, поэтому, неудивительно, что различных вариантов получить сумму
Суммирование по условию (функция СУММЕСЛИ), например, требуется просуммировать только те ячейки, значения в которых больше 100, или просуммировать ячейки у которых соседний столбец содержит необходимое слово (вариантов всевозможных великое множество, мы приведем только некоторые из них «равно», «не равно», «больше», «меньше», «больше или равно» (и все остальные вариации)) «содержит», «не содержит», «заканчивается на», «начинается с»
Суммирование только видимых ячеек (игнорируя скрытые ячейки)…
Так называемое трехмерное суммирование, когда функция СУММ() собирает данные не с одного, а с нескольких листов, находящихся рядом.
Суммирование ячеек в динамическом диапазоне…
Иногда бывает необходимо также:
Просуммировать каждое второе (третье) значение…
Просуммировать по цвету (стандартными функциями даже в самой последней версии) это не реализуется. При этом различают суммирование по цвету заливки и суммирование по цвету шрифта ячейки.
Только уникальные значения…
Суммирование времени (часов, минут, секунд)…
Так как одно и то же действие или процесс разные люди в силу субъективных причин называют по разному, то приведем тут наиболее часто использующиеся «формулировки», которые используют когда хотят сказать о суммирование, итак: суммировать, просуммировать, считать, сосчитать, подсчитать, рассчитать, вычислить сумму, получить сумму, определить сумму, узнать сумму…
А сколько способов получить сумму чисел в Excel знаете и используете Вы?…