Полное и выборочное суммирование данных, округление числовых значений
Математические функции СУММ, СУММЕСЛИ/МН, ОКРУГЛ





Все варианты суммирования данных в Excel - сплошным диапазоном или выборочное суммирование по одному либо нескольким условиям.

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

Математические функции СУММ, СУММЕСЛИ/МН, ОКРУГЛ

Все варианты суммирования данных в Excel - сплошным диапазоном или выборочное суммирование по одному либо нескольким условиям. Урок описывает правила использования функций выборочного и сплошного суммирования данных в таблицах Excel. А также достаточно подробно разбирает примеры условий - одного или нескольких.

Автосумма
Начнем знакомство со встроенными функциями с самой базовой и знакомой всем СУММ [SUM] : как сложить все значения, как сложить только те значения, которые отвечают условиям, а если условий много? как просуммировать значения в этом случае? На этом уроке ответим на все такие вопросы. Для разминки – несколько простых вещей и постепенно будем задачи усложнять (для работы потребуется прикрепленный к уроку файл: Ex-РВ-Урок5-Математические функции_СУММ)
Попробуем в ячейке I4 получить итоговую сумму столбца «Всего, т.р.» , для этого вставляем имя функции и выделяем диапазон ячеек F5:F25 , ну или пробуем выделить :-) и получаем картину вот такую:

Портят ее конечно же объединенные ячейки – ведь красоту в таблице наводили с помощью кнопки Объединить и поместить в центре [Merge & Center] . Предлагаю пару вариантов выхода в данной ситуации (выделять фрагменты диапазона при помощи Ctrl не будем, так как их может оказаться большое количество) Начните выделять тот диапазон ячеек, который выделить получается, - F5:F8 , например, а потом вручную перепишете порядковый номер крайне строки – сотрите порядковый номер строки 8 при помощи клавиш Delete или Backspace введите с клавиатуры 25 :

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

Оформление заголовков таблицы
Для дальнейшей работы на данном листе предлагаю убрать объединенные ячейки, так как они будут только мешать: выделяем весь лист (кнопка выделения листа находится на пересечении заголовков строк и столбцов) и нажимам кнопку, которая сейчас активна: Главная – Выравнивание – Объединить и поместить в центре [Home – Alignment – Merge & Center] )
Однако, выровнять заголовки по центру таблицы все же необходимо… Поступите следующим образом: выделите ячейки, по центру которых должна располагаться запись (в примере C4:F4 ) и вызовите окно Формат ячеек [Format Cells] (при помощи контекстного меню, либо нажмитем Ctrl+1 ) и на вкладке Выравнивание [Alignment] выберите в списке по горизонтали [Horizontal] команду по центру выделения [Center Across Selection] , нажмите ОК . Далее выделите следующий диапазон ячеек C15:F15 и вместо команды нажмите клавишу F4 – она повторит последнее совершенное действие
По большому счету, эта клавиша повторяет большинство действий по форматированию объектов и управлению их количеством (заливка, границы, параметры шрифта, добавление элементов, удаление, скрытие, отображение, группировка строк и/или столбцов и т.д.), не затрагивая содержимого. Чтобы подружиться с этой клавишей, запомните порядок действий – сначала хотя бы один раз выполните команду к выбранному объекту, а затем выделяете следующий объект, по отношению к которому нужно выполнить точно такие же действия, но вместо кнопок на ленте или поиска команды в контекстнмо меню жмете F4

Функция СУММЕСЛИ – суммирование некоторых ячеек
Если необходимо сложить значения и отбросить ошибки – воспользуйтесь ЭТИМ примером
Общий итог по таблице так или иначе мы получим, но далее необходимо получить итог только по товарам 2-го сорта. Для некоторых вычислений требуется выбрать часть ячеек из большого списка в соответствии с некоторыми критериями (условиями) - тогда пригодятся функции с приставкой ЕСЛИ [IF] , в нашем случае – СУММЕСЛИ [SUMIF] Результат предлагаю получить в ячейке I5 . Ставим в указанной ячейке знак равно , далее вписываем имя нужной функции (как только оно появится под курсором мыши – а обычно это происходит после ввода двух-четырех первых букв – кликните по нему дважды или доберитесь до него стрелками и нажмите клавишу Tab ). А теперь приступаем к заполнению аргументов (это можно делать двумя способами: выделять нужные ячейки на листе, разделяя аргументы точкой с запятой, либо упростить себе задачу и воспользоваться в окошке Аргументы функции [Function Arguments] ). Итак, нужно указать аргументы в следующем порядке: где искать ( Диапазон [Range] ) – что искать (Критерий [Criteria] ) – что складывать ( Диапазон_суммирования [Sum_range] ), должно получиться вот так: =СУММЕСЛИ(E:E;2;F:F)

В идеале, конечно, значение сорта – цифру 2 – желательно записать не в аргументах функции, но в ячейке листа, а в качестве аргумента функции сделать ссылку на ячейку с этой цифрой. Исправляем ситуацию и пишем цифру 2 в ячейке H5 . Далее переходим в режим редактирования формулы в ячейке I5 (двойной щелчок левой кнопкой мыши по этой ячейке либо клавиша F2 в помощь), стираем цифру 2 в соответствующем аргументе и пробуем вместо нее сделать ссылку на ячейку H5 – получается? ) Текст формулы перекрыл нужную ячейку и щелчок по ней сделать не удается. На самом деле, есть целых три варианта выхода в сложившейся ситуации:
1. Находясь в режиме редактирования ячейки с формулой вызовите окно Аргументы функции [Function Arguments] при помощи Fx (значок слева от строки формул) либо клавиш Ctrl+A или Shift+F3 – дело в том, что в этом окне выделить нужную ячейку получится, даже если текст формулы ее закрывает
2. Находясь в режиме редактирования ячейки с формулой выставите курсор в месте ввода адреса ячейки и щелкните по любой соседней ячейке, по которой получается, а далее при помощи стрелок на клавиатуре подведите рамку выделения к нужному адресу (в моем примере я щелкнула по H4 и нажала на клавиатуре стрелку вниз):

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

Готовый образец расчета итоговых сумм по датам можно взять в практикуме - это ТУТ

Функция СУММЕСЛИ – суммирование по части текста
А теперь давайте посчитаем, на какую сумму реализовано товаров-новинок. Заполнив аргументы функции СУММЕСЛИ [SUMIF] скорее всего получим нулевое значение, а формула получится следующая: =СУММЕСЛИ(C:C;»новинка»;F:F)

Почему ноль? Потому что в указанном столбце C:C Excel ищет ячейки только со словом новинка и не находит таковых – это в самом деле так, ведь перед искомым словом и после него есть еще много других знаков. Т.е. если бы слово «новинка» было бы значением ячейки, то все сработало бы. Но как быть в такой ситуации? Делать дополнительный столбец и что-то в нем писать, фильтровать записи и т.д. и т.п. не стоит, потому что решение есть и вот как оно выглядит
Нужно найти ячейки по фрагменту тексту, а для этого требуется отбросить все знаки, которые есть в ячейках до слова «новинка» и после него. Мы не знаем сколько этих знаков в каждом конкретном случае и какие они, поэтому используем знак умножения * - он способен заменить любое количество неизвестных знаков. Поставить его нужно внутри кавычек – вместо текста, который мог бы там быть. В результате преобразований формула будет выглядеть вот так: =СУММЕСЛИ(C:C;»*новинка*»;F:F) , а результат вычислений = 14.358
Можно усложнить задачу и выбрать только новинки 12-го, допустим, года – тогда звездочек будет больше (ставим их в тех местах, где есть неизвестные нам фрагменты текста): =СУММЕСЛИ(C:C;»*новинка*12*»;F:F)
А вот как сказать Excel, что нужно сложить все значения, кроме некоторых? Знака "не равно" в Excel нет как такового, но можно его собрать из двух символов буквы Б и Ю ). И прописать их нужно внутри кавычек; итого продано всех товаров кроме новинок 12-го года: =СУММЕСЛИ(C:C;»

Функция СУММЕСЛИМН – суммирование по месяцам
Чтобы узнать сумму реализации товаров за прошлый месяц, запишем в отдельных ячейках границы месяца – начальную и конечную даты (я написала в ячейках I10 и I11 ). Разбираемся далее – условий два, а функция СУММЕСЛИ умеет работать только с одним, поэтому обратимся к другому алгоритму СУММЕСЛИМН [SUMIFS] (подобные функций появились в продолжение уже известных алгоритмов расчета СУММ [SUM], СЧЁТ [COUNT] и СРЗНАЧ [AVERAGE] с 2007-ой версии Excel). Заполняем аккуратно аргументы и вновь получаем нулевой результат – сейчас выясним почему и как значение все-таки посчитать, формула же на данном этапе такая: =СУММЕСЛИМН(F:F;B:B;">=I10";B:B;" . Посмотрите внимательно на текст формулы в режиме редактирования:

Подозрительно здесь вот что – ссылки на ячейки с датами черно-белые, нет цветных рамок выделения этих ячеек и вообще они взяты в кавычки – заодно со знаками = , т.е. сейчас Excel не видит их как адреса ссылок, но как текст. Очевидно, что ссылки нужно вытащить из кавычек и каким-то образом соединить со знаками сравнения. При помощи ручного редактирования текста формулы приведите ее к вот такому виду – и тогда все заработает и посчитается: =СУММЕСЛИМН(F:F;B:B;">="&I10;B:B;" . Итог = 9.898

Можно складывать даже время - часы, минуты и секунды! Подробное описание, как это сделать берите в практикуме - это ТУТ

Функция ОКРУГЛ
В рамках данного урока разберем еще одну функцию, задача которой избавлять нас от лишних знаков – чаще всего после запятой. Это особенно актуально, когда вопрос касается финансовых величин и для копеек нужно отвести ровно два знака. Решая вопрос при помощи формата ячеек мы решаем его лишь визуально, а в ячейке и в памяти Excel продолжают храниться все знаки в полном объеме и именно это полное значение будет использовано в дальнейших расчетах и повлечет неприятные ошибки в виде лишних копеек-рублей в итоговых суммах, или «странные» значения – например 1,5 единицы неделимого товара. Мне случалось видеть, как коллеги пересчитывали и выверяли данные на калькуляторе (!): имея под рукой такой мощный инструмент… Попробуем доверить эту задачу Excel (работать будем в файле «Ex-РВ-Урок5-Математические функции_ОКРУГЛ»)
Составим план на полугодие. Руководством обозначена цифра общего количества товара к реализации (материалов к производству, поставке и т.п.), а нам нужно раскидать ее по месяцам в пропорциях прошлого периода – они уже высчитаны и указаны в столбце «Доля (по прошлому году)» . Формула для января будет такой: =B5*$C$2
Что означают и зачем нужны один или два знака доллара в адресе ячейки, а также как их быстро расставить – можно узнать из УРОКА 1

Копируем формулу до конца таблицы и получаем картину следующую:

Большое количество знаков после запятой для штучного товара. Чтобы избавиться от них, поступаем следующим образом – перейдите в режим редактирования формулы для строки «Январь» и после знака равно допишите имя функции ОКРУГЛ [ROUND] и откройте скобку (либо выберите ее имя в выпадающем списке и скобка появится сама):

Далее жмем Fx (значок слева от строки формул) либо Ctrl+A или Shift+F3 и заполняем аргумент число_разрядов [num_digits] – в нашем случае нужно поставить ноль (если результат вычислений денежный, то два); либо можно обойтись без окна аргументы функции [Function Arguments] – перейдите в самый конец текста формулы, поставьте точку с запятой, а далее значение нулевое значение следующего аргумента (с каким аргументом работаете – демонстрирует подсказка под курсором мыши – полужирным выделен как раз тот аргумент, в котором находится курсор):

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

В данном конкретном случае не нужно вручную прибавлять в одной из ячеек недостающее число. Я бы решила задачу вот так: для ячейки с последним месяцем использую другую формулу: =C2-СУММ(C5:C9) – от общего числа отниму то количество, которое выше уже распределилось, а в последний месяц должен попасть весь остаток

Финальным действием в данном примере будет следующая операция – так как в данном конкретном случае мы составляем план, то руководству желательно показывать круглые значения, чтобы не возникало лишни вопросов, т.е. не 642 – а 600 , не 4.425 – а 4.400 и т.д. Разумеется, мы не будем исправлять значения вручную – ведь итоговая цифра с планом может меняться часто – решим вопрос по-прежнему с использованием функции ОКРУГЛ , но вот каким образом изменив значение одного из ее аргументов: если аргументу число_разрядов [num_digits] задать отрицательного значение, то функция округлит значение до указанного по модулю числа знаков перед запятой и формула для января получится вот такая: =ОКРУГЛ(B5*$C$2;-2)
Скопируйте формулу до предпоследнего месяца включительно (помним о том, что в последний месяц закидываем весь остаток уже без помощи ОКРУГЛ и несем план шефу :-)

ВАЖНО (!) При работе с конспектами помним, что все названия программных продуктов являются зарегистрированными торговыми марками. Никакая часть настоящего издания ни в каких целях не может быть воспроизведена в какой бы то ни было форме и какими бы то ни было средствами, будь то электронные и механические, включая фотокопирование и запись на магнитный носитель, если на это нет письменного разрешения автора.

Похожие материалы

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

Правила и закономерности, по которым работает Excel
Общие принципы, лежащие в основе любого инструмента программы Excel
Как понять программу Excel? Это несложно, список правил и настроек приведён в этом уроке.

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

Создание кнопок ПЛЮС и МИНУС для скрытия и отображения строк и столбцов
Группировка строк и столбцов
Как сделать кнопки плюс и минус для скрытия и отображения данных в Excel? Как настроить, чтобы они были в строках сверху или в столбах слева?

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

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



Оставляя заявку, вы соглашаетесь на обработку ваших персональных данных.