Содержание
- 1 Суммирование значений в строке
- 1.1 Способ 1: арифметическая формула
- 1.2 Способ 2: автосумма
- 1.3 Способ 3: функция СУММ
- 1.4 Способ 4: массовое суммирование значений в строках
- 1.5 Помогла ли вам эта статья?
- 2 Простейшее сложение
- 3 Суммирование в один клик
- 4 Выборочное суммирование
Как посчитать сумму в таблице Excel?
Сегодня у нас тема для начинающих пользователей Microsoft Office Excel (Эксель), а именно: как правильно подсчитать сумму цифр?
На начало Excel (Эксель) это одна из программ пакета Microsoft Office предназначена для работы с таблицами данных. Совместима с операционными системами Windows и MacOS и программным продуктом OpenOffice.
Так как Excel (Эксель) это уже готовые таблицы с ячейками для размещения данных, он может выполнять арифметические операции в них с группами данных по вертикали и горизонтали, а также с отдельным ячейками, или отдельным группам ячеек.
Сейчас отдельно мы сосредоточимся на том, как посчитать сумму.
1. Выделяем нужный диапазон ячеек с цифрами по вертикали продолжая выделение на две пустые ячейки ниже и нажимаем значок «Посчитать сумму» в меню программы.
2. Выделяем нужный диапазон ячеек с цифрами по горизонтали продолжая выделение на две пустые ячейки ниже и нажимаем значок «Посчитать сумму» в меню программы.
3. Можно добавлять отдельные ячейки, расположенные в разных строках и столбцах. Для этого в Excel (Эксель) необходимо выбрать пустую ячейку и прописать формулу суммы. Равно (=), выбираем ячейку 1+ ячейку 5+ ячейку N и нажимаем Enter.
Кстати так можно не только получить сумму, но и умножать, делить, отнимать и применять более сложные формулы.
Как посчитать сумму?
Теперь можно попробовать посчитать сумму 2 чисел. Например, надо сложить 2 числа — 500 и 700. Выполнить эту операцию поможет довольно простая формула, которая выглядит следующим образом «=А+Б».
Итак, сначала необходимо щелкнуть левой кнопкой мыши по любой ячейке и написать в ней следующее: «=500+700» (без кавычек). После нажатия кнопки «Enter» будет получен результат — 1200. Вот таким простым способом можно сложить 2 числа. С помощью такой же функции можно выполнять и другие операции — умножение, деление и пр. В этом случае формула будет выглядеть так: «цифра, знак, цифра, Enter». Это был очень простой пример сложения 2 чисел, но, как правило, на практике он используется довольно редко.
Более сложный вариант, как посчитать сумму, можно рассмотреть на следующем примере. Допустим, есть таблица со следующими полями:
наименование;
количество;
цена;
сумма.
Всего в таблице имеется 5 наименований и 4 столбца (все заполненные, кроме суммы). Поставленная задача — найти сумму по каждому товару.
Например, первое наименование — ручка: количество — 100 штук, цена — 20 рублей. Чтобы найти сумму, можно воспользоваться той простой формулой, которая уже была рассмотрена выше, т.е. написать так: «=100×20». Такой вариант использовать, конечно, можно, но это будет не совсем практично. Допустим, цена на ручку поменялась, и теперь она стоит 25 рублей. И что делать тогда — переписывать формулу? А если в таблице наименований товаров не 5, а 100 или даже 1000? В таких ситуациях Эксель может получать сумму чисел и другими способами, в т.ч. пересчитывая формулу, если одна из ячеек изменяется.
Чтобы посчитать сумму практичным способом, понадобится другая формула. Итак, сначала нужно в соответствующей ячейке столбца «Сумма» поставить знак «равно». Далее, необходимо щелкнуть левой кнопкой мыши на количество ручек (в данном случае это будет число «100»), поставить знак умножения, а затем еще раз щелкнуть левой кнопкой мыши на цену ручки — 20 рублей. После этого можно нажать «Enter». Вроде бы ничего не изменилось, поскольку результат остался прежним — 2000 рублей.
Но тут есть два нюанса. Первый — это сама формула. Если нажать на ячейку, то можно увидеть, что там написаны не числа, а что-то вроде «=B2*C2». Программа написала в формулу не числа, а название ячеек, в которых находятся эти числа. А второй нюанс заключается в том, что теперь при изменении любого числа в этих ячейках («Количество» или «Цена») формула будет автоматически пересчитываться.
Если попробовать изменить цену ручки на 25 рублей, то в соответствующей ячейке «Сумма» сразу же будет отображен другой результат — 2500 рублей. То есть при использовании такой функции не нужно будет самостоятельно пересчитывать каждое число, если изменилась некоторая информация. Достаточно лишь изменить исходные данные (если нужно), а Excel автоматически все пересчитает.
После этого пользователь должен будет посчитать сумму и оставшихся 4 наименований. Скорее всего, расчет будет производиться знакомым ему образом: знак равно, щелчок на ячейке «Количество», знак умножения, еще один щелчок на ячейке «Цена» и «Enter». Но в программе Microsoft Excel для этого есть одна очень интересная функция, которая позволяет сэкономить время, просто скопировав формулу в другие поля.
Итак, сначала необходимо выделить ту ячейку, в которой уже была посчитана общая сумма ручек. Выбранная ячейка будет выделена жирными линиями, а в правом нижнем углу будет находиться маленький черный квадратик. Если правильно навести мышкой на этот квадратик, то внешний вид курсора будет изменен: вместо белого «плюсика» станет черный «плюсик». В том момент, когда курсор будет выглядеть как черный плюсик, необходимо нажать левой кнопкой мыши на этот правый нижний квадрат и потянуть вниз до нужного момента (в данном случае — на 4 строки вниз).
Данная манипуляция позволяет «потянуть» формулу вниз и скопировать ее еще в 4 ячейки. Эксель моментально выдаст все результаты. Если щелкнуть на любую из этих ячеек, то можно увидеть, что программа самостоятельно прописала нужные формулы для каждой ячейки и сделала это абсолютно правильно. Такая манипуляция будет полезной, если в таблице находится очень много наименований. Но тут есть некоторые ограничения.
Во-первых, формулу можно «потянуть» только вниз/вверх или в сторону (т.е. по вертикали или по горизонтали). Во-вторых, формула должна быть одна и та же. Поэтому, если в одной ячейке рассчитывается сумма, а следующей (под ней) — числа умножаются, то такая манипуляция не поможет, в данном случае она скопирует только сложение чисел (если копировалась первая ячейка).
Как посчитать сумму с помощью функции «Автосумма»?
Еще один способ, как посчитать сумму чисел — это с помощью функции «Автосумма». Эта функция обычно находится в панели инструментов (чуть ниже панели меню). Выглядит «Автосумма» как греческая буква «Е». Итак, например, есть столбец цифр, и нужно найти их сумму. Для этого нужно выделить ячейку под этим столбцом и нажать значок «Автосуммы». Эксель автоматически выделит все ячейки по вертикали и напишет формулу, а пользователю лишь останется нажать «Enter» для получения результата.
Вот такими способами можно посчитать сумму в Excel. Хотя эта программа способна выполнять не только такие простые операции, но и более сложные математические расчеты
Работая с таблицами, часто приходится подбивать общие итоги по конкретному наименованию. В качестве данного наименования может выступать название контрагента, фамилия работника, номер подразделения, дата и т.д. Нередко эти наименования являются заглавием строк и поэтому, чтобы подсчитать общий итог по каждому элементу, приходится суммировать содержимое ячеек конкретной строки. Иногда сложение данных в строках производят и в иных целях. Давайте разберем различные способы, как это можно сделать в программе Excel.
Читайте также: Как посчитать сумму в Экселе
Суммирование значений в строке
По большому счету, просуммировать в Экселе значения в строке можно тремя основными способами: использование арифметической формулы, применение функций и автосуммы. При этом, данные способы могут делиться ещё на ряд более конкретизированных вариантов.
Способ 1: арифметическая формула
Прежде всего, разберем, как при помощи арифметической формулы можно подсчитать сумму в строчке. Давайте посмотрим, как действует данный способ на конкретном примере.
Имеем таблицу, в которой указана выручка пяти магазинов по датам. Наименования магазинов являются названиями строк, а даты – названиями столбцов. Нам нужно рассчитать общую сумму выручки первого магазина за весь период. Для этого нам придется произвести сложение всех ячеек строчки, которая относится к данной торговой точке.
- Выделяем ячейку, в которую будет выводиться готовый результат подсчета итога. Ставим туда знак «=». Кликаем левой кнопкой мыши по первой ячейке в данной строке, которая содержит числовые значения. Как видим, её адрес тут же отобразится в элементе для вывода суммы. Ставим знак «+». Затем кликаем по следующей ячейке в строке. Таким способом чередуем знак «+» с адресами ячеек строчки, которая относится к первому магазину.
В итоге в конкретно нашем случае получается следующая формула:
=B3+C3+D3+E3+F3+G3+H3Естественно, при использовании других таблиц вид её будет отличаться.
- Для выведения общей суммы выручки по первой торговой точке жмем на кнопку Enter на клавиатуре. Результат выводится в ту ячейку, в которой была расположена формула.
Как видим, данный способ довольно простой и интуитивно понятный, но у него имеется один существенный недостаток. На его осуществление нужно потратить много времени, если сравнивать с теми вариантами, которые мы рассмотрим ниже. А если в таблице очень много столбцов, то временные затраты увеличатся ещё больше.
Способ 2: автосумма
Гораздо более быстрым способом сложить данные в строчке является применение автосуммы.
- Выделяем все ячейки с числовыми значениями первой строчки. Выделение проводим, зажав левую кнопку мыши. Перейдя во вкладку «Главная», жмем на значок «Автосумма», который расположен на ленте в блоке инструментов «Редактирование».
Другим вариантом вызова автосуммы является переход во вкладку «Формулы». Там в блоке инструментов «Библиотека функций» на ленте следует щелкнуть по кнопке «Автосумма».
Если же вы вообще не хотите перемещаться по вкладкам, то после выделения строчки можно просто набрать сочетание горячих клавиш Alt+=.
- Какое бы действие из вышеописанных манипуляций вы не выбрали, справа от выделенного диапазона отобразится число. Оно и будет составлять сумму значений строчки.
Как видим, данный способ позволяет произвести подсчет суммы в строке гораздо быстрее, чем предыдущий вариант. Но у него тоже имеется недостаток. Он состоит в том, что сумма будет выводиться только справа от выделенного горизонтального диапазона, а не в том месте, где захочет пользователь.
Способ 3: функция СУММ
Преодолеть недостатки двух вышеописанных способов может вариант с применением встроенной функции Excel под названием СУММ.
Оператор СУММ принадлежит к группе математических функций Эксель. Его задачей является суммирование чисел. Синтаксис этой функции имеет такой вид:
=СУММ(число1;число2;…)
Как видим, аргументами этого оператора являются числа или адреса ячеек, в которых они располагаются. Их количество может быть до 255.
Посмотрим, как можно просуммировать элементы в строке при помощи данного оператора на примере нашей таблицы.
- Выделяем любую пустую ячейку на листе, куда предполагаем выводить итог вычисления. При желании можно её выбрать даже на другом листе книги. Но подобное бывает все-таки редко, так как в большинстве случаев более удобно традиционно располагать ячейку для вывода итогов в той же строке, в которой находятся расчетные данные. После того, как выделение произведено, щелкаем по значку «Вставить функцию» слева от строки формул.
- Запускается инструмент, который носит наименование Мастер функций. Переходим в нем в категорию «Математические» и из открывшегося списка операторов выбираем название «СУММ». Затем кликаем по кнопке «OK» внизу окошка Мастера функций.
- Производится активация окна аргументов оператора СУММ. В этом окне может располагаться до 255 полей, но для решения нашей задачи понадобится всего одно поле – «Число1». В него нужно ввести координаты той строки, значения в которой следует сложить. Для этого ставим курсор в указанное поле, а затем, произведя зажим левой кнопкой мыши, выделяем курсором весь числовой диапазон нужной нам строчки. Как видим, адрес данного диапазона тут же будет отображен в поле окна аргументов. Затем щелкаем по кнопке «OK».
- После того, как мы произвели указанное действие, сумма значений строки тут же отобразится в той ячейке, которую мы выделили ещё на самом первом этапе решения задачи данным способом.
Как видим, данный способ довольно гибкий и относительно быстрый. Правда, не для всех пользователей он интуитивно понятен. Поэтому те из них, которые не знают о его существовании из различных источников, редко когда находят его в интерфейсе Excel самостоятельно.
Урок: Мастер функций в Экселе
Способ 4: массовое суммирование значений в строках
Но что делать, если нужно просуммировать не одну и не две строчки, а, скажем 10, 100 или даже 1000? Неужели для каждой строки требуется в отдельности применять вышеописанные действия? Как оказывается, совсем не обязательно. Для этого нужно просто скопировать формулу суммирования в другие ячейки, в которые вы планируете выводить сумму по остальным строчкам. Сделать это можно при помощи инструмента, который носит наименование маркера заполнения.
- Производим сложение значений в первой строке таблицы любым из тех способов, которые были описаны ранее. Ставим курсор в нижний правый угол ячейки, в которой отображается результат применяемой формулы или функции. При этом курсор должен изменить свой внешний вид и преобразоваться в маркер заполнения, который выглядит, как небольшой крестик. Затем зажимаем левую кнопку мыши и перетаскиваем курсор вниз, параллельно ячейкам с наименованиями строк.
- Как видим, все ячейки были заполнены данными. Это и есть сумма значений отдельно по строкам. Такой результат удалось получить потому, что по умолчанию все ссылки в Excel относительны, а не абсолютны, и при копировании изменяют свои координаты.
Урок: Как сделать автозаполнение в Экселе
Как видим, в Экселе существует три основных способа подсчитать сумму значений в строчках: арифметическая формула, автосумма и функция СУММ. Каждый из данных вариантов имеет свои достоинства и недостатки. Наиболее интуитивно простой способ – это применение формулы, самый быстрый вариант – автосумма, а самый универсальный – использование оператора СУММ. Кроме того, с помощью маркера заполнения можно проводить массовое суммирование значений по строкам, выполненное одним из трех способов, которые были перечислены выше.
Мы рады, что смогли помочь Вам в решении проблемы.
Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Да Нет
Электронные таблицы Эксель являются очень удобным средством для работы с табличными данными. С помощью Экселя табличные данные можно сортировать, выявлять в них характерные закономерности, строить диаграммы различного вида, проводить с данными различные математические действия. Одним из наиболее часто востребованных математических действий является суммирование данных. С помощью Экселя можно буквально за секунды подсчитать сумму в столбце из десятков, сотен и даже тысяч чисел. Рассмотрим как это сделать.
Как найти сумму чисел в Excel Существуют различные способы, позволяющие найти сумму заданных чисел в Эксель, в зависимости от стоящих целей и задач.
- Самым простым и быстрым способом подсчета суммы чисел в Excel является выделение с помощью мыши диапазона чисел в столбце, для которого необходимо найти сумму. Если использовать мышь вам неудобно, то нажмите на верхнюю ячейку столбца, зажмите клавишу Shift и нажимая стрелку вниз на клавиатуре ↓ выделите числа в столбце. После завершения выделения отпустите клавишу Shift. Сумма будет отражена в нижней части экрана программы наряду с другими показателями: средним значением, общим количеством чисел, минимальным и максимальным значением.
простой спооб нахождения суммы
- В том случае, когда нужно отразить сумму какой-либо специальной ячейке, например соответствующей графе итого, можно воспользоваться заданием формулы для нее. Для этого: установите курсор в ячейку, в которой будет отображаться сумма, левее поля для формулы нажмите на значок обозначение функции fx, в появившемся окне мастера функций выберите функцию СУММ.
использование формулы суммыВ следующем окне укажите диапазон чисел, который необходимо суммировать.
указание диапазона чисел для суммированияПри указании диапазона чисел можно использовать следующие подстановки:
- =СУММ(A1:A10) – сумма чисел в диапазоне ячеек от A1 до A10;
- =СУММ(A1;A10) – сумма чисел в ячейках A1 и A10.
- Еще одним способом нахождения суммы является использование функции Автосумма. Для этого выделите диапазон суммируемых чисел таким же образом, как в п.1 данной статьи. Перейдите на вкладку Формулы в программе и нажмите кнопку Автосумма. Итоговое значение будет записано в ячейке за последней цифрой в столбце.
использование автосуммы
Кроме рассмотренных простейших способов суммирования в Excel существуют возможности для более сложных расчетов. Например, сумма чисел, отвечающих определенным признакам и некоторые другие.
Самая простая и востребованная операция офисной программы — эксель (excel) — суммирование данных. Как в экселе посчитать сумму — эта задача становится актуальной в 2 случаях:
- исходных значений больше 2;
- исходные данные многозначны (2,35476).
Простейшее сложение
Эксель (Microsoft Excel) предназначена для всевозможных операций с числовыми данными в таблице, представленной на мониторе компьютера как тетрадный лист в клеточку. Каждая клеточка называется ячейкой. Наведя на нее курсор и щелкнув левой кнопкой мыши, можно записать первое исходное число посредством клавиатуры компьютера.
Для того чтобы сложить нескольких чисел, нужно написать их со знаками «+», а перед первым числом поставить знак «=» (например, =7+3+4). После нажатия клавиши enter (ввод), появится результат (14).
Сложение 2 простых чисел выполняется устно или на калькуляторе. Если же исходных параметров очень много, например, когда они накапливаются за период времени, то определение суммы без компьютера становится сложной рутинной задачей.
Данные, расположенные по вертикали, называются «столбец», а по горизонтали — «ряд». Заполнять их можно, используя опцию «копировать» на панели инструментов раздела «главная».
При занесении числа клавиатурой или копированием из других источников создаются электронные таблицы для последующих вычислений.
Размеры электронных таблиц многократно превышают объем любой тетради, поэтому все операции проводятся автоматически. Выполнение формул определяют стандартные манипуляции мышью компьютера.
Суммирование в один клик
Навести курсор на пустую ячейку, следующую за последним значением в столбце (строке), и щелкнуть (кликнуть) левой кнопкой мыши. Затем на панели инструментов в разделе «главная» найти значок суммы ∑ и кликнуть курсором по нему.
Умная программа очертит бегущим пунктиром диапазон для визуального контроля правильности вычислений, а строка формул покажет номера анализируемых ячеек.
Заметив ошибку ввода данных, достаточно нажать клавишу esc, чтобы отменить сложение. Нажав на клавиатуре клавишу enter (ввод), получим искомый результат.
Если значений немного и они расположены на одной странице таблицы, то достаточно провести по ним курсором с нажатой левой кнопкой мыши, чтобы нижняя панель показала их сумму.
Выборочное суммирование
При анализе исходных данных часто появляется необходимость сложить их, выбирая из разных столбцов или строк, например, максимальные числа.
Как в экселе посчитать сумму выборочных ячеек, решает функция «СУММ» раздела «Формулы» панели управлений.
Все операции выполняются наведением курсора на разделы панели управления и ячейки с данными, щелканьем («клик») левой кнопкой мыши, в следующей последовательности:
- любая пустая ячейка;
- раздел «ФОРМУЛЫ» панели управлений;
- раздел «Вставить функцию»;
- раздел «Категория», выбрать «Математические»;
- раздел «Выберите функцию» выбрать «СУММ», перейти к разделу «Аргументы функции»;
- ячейка таблицы с первым слагаемым;
- раздел «Аргументы функции», строка «Число 2»;
- ячейка таблицы со вторым слагаемым.
После того как все слагаемые введены, нажать «ОК» окна «Аргументы функции», получив окончательный результат.
Программа позволяет находить более сложные суммы данных, объединенных между собой условием (СУММ ЕСЛИ).
Такими условиями могут быть отрицательная величина, экстремальные значения, т.е. сложению будут подвергаться только отрицательные значения или отличающиеся от экстремумов.
Можно складывать результаты промежуточных расчетов: квадраты, произведения, квадраты разностей и разность квадратов — необходимые при статистическом анализе.
Электронные таблицы могут содержать тысячи исходных данных, но простая операция в эксель гарантирует быстрый подсчет их суммы.
Электронные таблицы в Excel позволяют выполнять различные вычислительные действия, включая суммирование ячеек. При работе с внушительным объемом данных, иногда требуется проделывать математические операции с ячейками, попадающими по конкретные условия. Для таких задач в Excel предусмотрены специальные функции для суммирования по условию, которые упрощают работу с таблицами.
- 1 Простое сложение в Excel
- 2 Суммирование ячеек по условию
- 3 Функция «СУММЕСЛИ»
- 4 Функция «СУММЕСЛИМН»
- 5 Итог
Простое сложение в Excel
Складывать значения ячеек можно с помощью простой функции «СУММ», которая суммирует выбранные значения без определенного условия. Она используется, когда есть ячейки, массив или несколько массивов для сложения. В этом случае достаточно выделить нужные элементы электронной таблицы и применить функцию. Она играет роль обычного калькулятора со знаком «+».
Суммирование ячеек по условию
Excel также предусматривает суммирование с учетом условия. Функция применяется, когда необходимо просуммировать только те значения, которые соответствуют заданному условию. Для нее возможно задать три аргумента:
- Диапазон – обязательный аргумент, представляющий собой массив, в котором происходит проверка заданного условия;
- Критерий – еще один обязательный аргумент, которое является условием для отбора значений в ячейках. При равенстве определенному числу, необходимо ввести его без кавычек, в других случаях необходимы кавычки: например, если значение больше числа 5, то его нужно прописать, как
">5". Также работают текстовые значения: если нужно суммировать выручку продавца Иванова в таблице, то прописывается условие"Иванов"; - Диапазон суммирования – массив значений, которые нужно сложить.
Для удобной работы с вычислительными операциями в таблицах Excel есть две функции, которые суммируют ячейки, если задать условия: «СУММЕСЛИ» и «СУММЕСЛИМН». Рассмотрим каждую из них более подробно.
Функция «СУММЕСЛИ»
СУММЕСЛИ подходит, когда нужно суммировать значения ячеек с одним условием. Чтобы применить ее в действии, следуйте инструкции:
- По свободной ячейке кликните левой клавишей мыши, чтобы выбрать место, где будет отображаться результат операции.
- Кликните по иконке, расположенной рядом со строкой ввода формул, чтобы вызвать меню с функциями.
- Выберите категорию «Математические» и найдите в списке функций «СУММЕСЛИ».
- Кликните по кнопке «ОК», чтобы подтвердить свой выбор.
- В появившемся окне «Аргументы функции» нужно заполнить обязательные поля: «Диапазон» и «Критерий». Например, нужно узнать, сколько килограмм фруктов продал продавец Иванов. Для этого выделите диапазон столбца «Продавец» полностью.
- В строке «Критерий» пропишите фамилию продавца: «Иванов». Кавычки можно не указывать, программа вставляет их автоматически.
- Поставьте курсор мыши в поле диапазона суммирования и выделите столбец «Количество (кг)» в таблице.
- Нажмите на кнопку «ОК» внизу, чтобы произвести суммирование с условием, или из данной таблицы узнать, сколько килограмм фруктов продал Иванов.
В выделенной ячейке отобразится результат сложения, а в верхней строке – функция. Вычислить значение можно без вызова окна «Аргументы функции», если прописать все вручную. При этом базовая запись должна выглядеть так: =СУММЕСЛИ(X; «Условие»; Y), где X – это диапазон, а Y – диапазон суммирования. Значения не обязательно вписывать, их достаточно выделить с помощью мышки в таблице. Все элементы функции разделены знаком «;».
Следует отметить, что если третий аргумент явно не выделили, то диапазон, указанный как первый аргумент, также становится диапазоном для сложения.
Функция «СУММЕСЛИМН»
«СУММЕСЛИМН» позволяет рассчитать результат суммирования с использованием нескольких условий. Функция предоставляет больше возможностей для задания параметров математического вычисления. Для расчета можно использовать сразу несколько критериев суммирования, причем условий может быть задано до 127. На примере данной таблицы рассмотрим, как найти, сколько килограмм яблок купил Евдокимов, ведь он приобретал также и бананы.
Чтобы суммировать ячейки с несколькими условиями, действуйте согласно следующей инструкции:
- Выделите пустую ячейку, в которой будет отображаться конечный результат, затем нажмите на кнопку fx, которая находится рядом со строкой функций.
- В разделе «Математические» в окне «Вставка функций» нажмите «СУММЕСЛИМН», затем подтвердите выбор, нажав на кнопку «ОК».
- В появившемся окне в строке «Диапазон суммирования» введите ячейки, который находятся в столбце «Количество».
- В «Диапазон условия» выделите все ячейки в столбце «Товар».
- В качестве первого условия пропишите значение «Яблоки».
- После этого необходимо задать второе условие и диапазон для него. В данной таблице столбец «Покупатели» является значением для диапазона. Выделите его в строку, затем в втором условии пропишите фамилию Евдокимов.
- Нажмите на кнопку «ОК», чтобы программа посчитала, сколько яблок купил Евдокимов.
Функцию «СУММЕСЛИМН» возможно прописать вручную в строке формул, но это сложно, поскольку используется слишком много условий. В данной таблице результат равен 8, а вверху отображается функция полностью.
Итог
Встроенные инструменты программы Excel позволяют эффективно работать с большими электронными таблицами. С помощью функций суммирования возможно быстро сложить определенные ячейки, а с дополнительными условиями – подобрать критерии выбора для сложения. Мы рассмотрели, как пользоваться «СУММЕСЛИ» и «СУММЕСЛИМН» на примере простой таблицы, но эти функции применимы и для объемных баз данных.
Работая с табличной информацией, у пользователей зачастую возникает необходимость в подсчете суммы того или иного показателя. Часто в качестве этих показателей выступают наименования строчек, по которым необходимо произвести суммирование всей информации в ячейках. Из статьи вы узнаете все методы, позволяющие реализовать эту процедуру.
Содержание
- Суммирование значений в строке
- Способ 1: арифметическая формула
- Способ 2: автосумма
- Способ 3: функция СУММ
- Функция СУММ(SUM) не работает
- Как найти сумму наибольших (наименьших) значений
- Растягивание формулы/функции суммирования на другие строки
- Как вычислить сумму каждой N-ой строки.
- 3-D сумма, или работаем с несколькими листами рабочей книги Excel
- Сумма с несколькими условиями
- Как посчитать процент от суммы
- Заключение
Суммирование значений в строке
Воспроизвести процесс суммирования значений в строке можно следующими методами:
- арифметическая формула;
- автосумма;
- различные функции.
Каждый из этих способов подразделяется на дополнительные способы. Разберемся с ними более подробно.
Способ 1: арифметическая формула
Первоначально выясним, как, используя арифметическую формулу, возможно произвести суммирование в строчке. Разберем все на определенном примере. Допустим, у нас есть табличка, в которой отображена выручка 5 магазинов по определенным датам. Названия торговых точек – наименования строчек. Даты – наименования колонок.
Цель: посчитать итоговую сумму дохода первой торговой точки за все время. Для достижения этой цели необходимо сложить все ячейки строки, относящиеся к этому магазину. Пошаговое руководство выглядит так:
- Производим выделение той ячейки, где в дальнейшем будет отражаться результат. Вводим в ячейку символ «=». Жмем ЛКМ по самой первой ячейке в этой строчке, содержащей числовые показатели. Замечаем, что после нажатия координаты ячейки отобразились в ячейке для подсчета результата. Вводим символ «+» и кликаем на следующую ячейку в строке. Продолжаем чередовать символ «+» с координатами ячеек строки первой торговой точки. В результате мы получаем формулу: =B3+C3+D3+E3+F3+G3+H3.
- После проведения всех манипуляций нажимаем на «Enter».
- Готово! Результат вывелся в ячейке, в которую мы вводили формулу для подсчёта суммы.
Обратите внимание! Как видите, этот метод является понятным и легким, но в нем существует один неприятный недостаток. Реализация этого метода занимает большое количество времени. Рассмотрим более быстрые варианты суммирования.
Способ 2: автосумма
Использование автоматической суммы – метод, который выполняется намного быстрее, чем вышерассмотренный. Пошаговое руководство выглядит так:
- Используя зажатую ЛКМ, производим выделение всех ячеек первой строки, которые имеют числовые данные. Передвигаемся в раздел «Главная», располагающийся в верхней части интерфейса табличного процессора. Находим блок команд «Редактирование» и кликаем на элемент под названием «Редактирование».
4
Рекомендация! Альтернативный вариант – переходим в раздел «Формулы» и нажимаем на кнопку «Автосумма», находящуюся в блоке «Библиотека функций». Третий вариант – использование комбинации клавиш «Alt» + «=» после выделения ячейки.
- Вне зависимости от того, какой вариант вы применили, правее от выделенных ячеек появилось числовое значение. Это число является суммой показателей строки.
Как вы заметили, этот способ производит суммирование в строчке намного быстрее, чем вышеописанный. Главный недостаток – результат выводится только правее от выбранного диапазона. Чтобы результат отображался в любом выбранном месте, необходимо воспользоваться другими методами.
Способ 3: функция СУММ
Использование интегрированной функции табличного процессора под названием СУММ не имеет недостатков, как у рассмотренных ранее способов. СУММ – математическая функция. Задача оператора – суммирование числовых значений. Общий вид оператора: =СУММ(число1;число2;…).
Важно! Аргументами этой функции могут являться как числовые значения, так и координаты ячеек. Максимальное число аргументов – 255.
Пошаговое руководство выглядит следующим образом:
- Производим выделение какой-либо пустой ячейки на рабочем листе. В ней мы будем выводить результат суммирования. Стоит отметить, что она может располагаться даже на отдельном рабочем листе документа. После осуществления выделения жмем на кнопку «Вставить функцию», находящуюся рядом со строчкой для ввода формул.
- На экране отобразилось небольшое окошко под названием «Мастер функций». Раскрываем список около надписи «Категория:» и выбираем элемент «Математические». Чуть ниже в перечне «Выберите функцию:» находим оператор СУММ и щелкаем на него. После проведения всех манипуляций щелкаем на кнопочку «ОК», располагающуюся в нижней части окна.
- На дисплее появилось окошко под названием «Аргументы функции». В пустое поле «Число1» вводим адрес строки, значения в которой необходимо сложить. Для осуществления этой процедуры ставим указатель в эту строчку, а потом, используя зажатие ЛКМ, производим выделение всего диапазона с числовыми значениями. После проведения всех манипуляций щелкаем на «ОК».
- Готово! В выбранной изначально ячейке отобразился результат суммирования.
Функция СУММ(SUM) не работает
Иногда случается так, что оператор СУММ не работает. Основные причины неисправности:
- неправильный формат чисел (текстовый) при импорте данных;
- наличие скрытых символов и пробелов в ячейках с числовыми значениями.
Стоит отметить! Числовые значения всегда выравниваются по правой стороне, а текстовая информация – по левой.
Как найти сумму наибольших (наименьших) значений
Разберемся с тем, как вычислить сумму наименьших или же наибольших значений. К примеру, нам необходимо просуммировать три минимальных или же три максимальных значения.
Оператор НАИБОЛЬШИЙ позволяет возвратить максимальный показатель из выбранных данных. 2-й аргумент указывает, какой именно показатель необходимо вернуть. В нашем конкретном примере формула выглядит так: =СУММ(НАИБОЛЬШИЙ(B2:D13;{1;2;3})).
Таким же образом работает и поиск наименьшего значения, только вместо оператора НАИБОЛЬШИЙ используется функция НАИМЕНЬШИЙ. Формула выглядит следующим образом: =СУММ(НАИМЕНЬШИЙ(B2:D13;{1;2;3})).
Растягивание формулы/функции суммирования на другие строки
Мы выяснили, каким образом высчитывается общая сумма по ячейкам в одной строчке. Разберемся с тем, как реализовать процедуру суммирования по всем строкам таблички. Прописывание формул ручным способом и вставка оператора СУММ – это долгие и неэффективные способы. Оптимальное решение – растягивание функции или же формулы на нужное число строк. Пошаговое руководство выглядит следующим образом:
- Производим подсчет суммы одним из вышеописанных способов. Перемещаем указатель мышки на нижнюю правую рамку ячейки с выведенным результатом. Курсор примет вид небольшого плюсика темного цвета. Зажимаем ЛКМ и растягиваем формулу до самого низа таблички.
- Готово! Мы вывели итоговые результаты по всем названиям. Мы добились такого результата благодаря тому, что при копировании формулы адреса смещаются. Смещение координат происходит из-за того, что адреса являются относительными.
- Для 3-й строчки формула имеет вид: =B3+C3+D3+E3+F3+G3+H3.
Как вычислить сумму каждой N-ой строки.
На конкретном примере разберем, как вычислить сумму каждой N-ой строки. К примеру, у нас есть табличка, в которой отражена каждодневная прибыль торговой точки за определенный промежуток времени.
Задача: произвести расчёт еженедельной прибыли за каждую неделю. Оператор СУММ позволяет суммировать данные не только в диапазоне, но и в массиве. Здесь необходимо использование вспомогательного оператора СМЕЩ. В операторе СМЕЩ указывается некоторое количество аргументов:
- Первая точка. Ячейка С2 вводится в виде абсолютной ссылки.
- Количество шагов вниз.
- Количество шагов вправо.
- Число шагов вниз.
- Количество столбиков в массиве. Попадание в последнюю точку массива показателей.
В конечном итоге мы получаем следующую формулу для первой недели: =СУММ(СМЕЩ($C$2;(СТРОКА()-2)*5;0;5;1)). В результате оператор сумм произведет суммирование всех пяти числовых значений.
3-D сумма, или работаем с несколькими листами рабочей книги Excel
Для подсчета чисел из одинаковой формы диапазона на некотором количестве рабочих листов необходимо использовать особый синтаксис, который называется «3D-ссылкой». Допустим, на всех рабочих листах книги располагается табличка с информацией за неделю. Нам необходимо соединить это все в одно целое и вывести в месячный показатель. Для начала вам необходимо посмотреть следующий видеоролик:
У нас есть четыре идентичных таблички. Обычный способ подсчета прибыли выглядит так: =СУММ(неделя1!B2:B8;неделя2!B2:B8;неделя3!B2:B8;неделя4!B2:B8). Здесь в роли аргументов выступают диапазоны ячеек.
Формула 3D-суммы выглядит следующим образом: =СУММ(неделя1:неделя4!B2:B8). Здесь говорится о том, что производится суммирование в диапазонах В2:В8, которые располагаются на рабочих листах: неделя (от 1 до 4). Происходит пошаговое увеличение номера рабочего листа на единицу.
Сумма с несколькими условиями
Бываю случаи, когда пользователю необходимо осуществить решение задачи, в которой указано от двух и более условий и нужно посчитать сумму числовых значений по разным критериям. Для реализации этой процедуры применяется функция «=СУММЕСЛИМН».
Пошаговое руководство выглядит следующим образом:
- Для начала формируется табличка.
- Производится выбор ячейки, в которой будет выведен результат суммирования.
- Перемещаемся в строчку для ввода формул.
- Вводим оператор: =СУММАЕСЛИМН.
- Пошагово вводим диапазон сложения, диапазон условия1, условие1 и так далее.
- После проведения всех манипуляций жмем «Enter». Готово! Подсчет произведен.
Стоит отметить! Между аргументами оператора в обязательном порядке должен стоять разделитель в виде точки с запятой «;». Если не использовать этот разделитель, то табличный процессор выведет ошибку, говорящую о том, что функция была введена неправильно.
Как посчитать процент от суммы
Теперь поговорим о том, как правильно посчитать процент от суммы. Самый легкий метод, который будет понятен всем пользователям, – это применение пропорции или правила «квадрата». Суть можно понять по нижеприведенной картинке:
Общая сумма выводится в ячейке F8 и имеет значение 3060. Иными словами, это стопроцентный доход, а нам необходимо найти, какую часть прибыли сделал Саша. Для подсчета мы применяем специальную формулу пропорции, которая выглядит следующим образом: =F10*G8/F8.
Важно! Первым делом умножается 2 известных числовых значения по диагонали, а потом производится деление на оставшееся 3-е значение.
Используя это простое правило, можно легко и просто высчитать процент от суммы.
Заключение
В статье было рассмотрено несколько способов, позволяющих получить сумму данных строки в табличном процессоре Эксель. Каждый способ имеет собственные преимущества и недостатки. Применение арифметической формулы – самый простой в использовании способ, но к нему целесообразнее прибегать при работе с небольшими объемами информации. Для работы с большими объёмами данных хорошо подходит автоматическое суммирование, а также функция СУММ.
Оцените качество статьи. Нам важно ваше мнение:
Простые логические функции такие как ЕСЛИ обычно предназначены для работы с одним столбцом или одной ячейкой. Excel также предлагает несколько других логических функций служащих для агрегирования данных. Например, функция СУММЕСЛИ для выборочного суммирования диапазона значений по условию.
Примеры формулы для суммы диапазонов с условием отбора в Excel
Ниже на рисунке представлен в таблице список счетов вместе с состоянием по каждому счету в виде положительных или отрицательных чисел. Допустим нам необходимо посчитать сумму всех отрицательных чисел для расчета суммарного расхода по движению финансовых средств. Этот результат будет позже сравниваться вместе с сумой положительных чисел с целью верификации и вывода балансового сальдо. Узнаем одинаковые ли суммы доходов и расходов – сойдется ли у нас дебит с кредитом. Для суммирования числовых значений по условию в Excel применяется логическая функция =СУММЕСЛИ():
Функция СУММЕСЛИ анализирует каждое значение ячейки в диапазоне B2:B12 и проверяет соответствует ли оно заданному условию (указанному во втором аргументе функции). Если значение меньше чем 0, тогда условие выполнено и данное число учитывается в общей итоговой сумме. Числовые значения больше или равно нулю игнорируются функцией. Проигнорированы также текстовые значения и пустые ячейки.
В приведенном примере сначала проверяется значения ячейки B2 и так как оно больше чем 0 – будет проигнорировано. Далее проверяется ячейка B3. В ней числовое значение меньше нуля, значит условие выполнено, поэтому оно добавляется к общей сумме. Данный процесс повторяется для каждой ячейки. В результате его выполнения суммированы значения ячеек B3, B6, B7, B8 и B10, а остальные ячейки не учитываются в итоговой сумме.
Обратите внимание что ниже результата суммирования отрицательных чисел находится формула суммирования положительных чисел. Единственное отличие между ними — это обратный оператор сравнения во втором аргументе где указывается условие для суммирования – вместо строки «<0» (меньше чем ноль) используется строка «>0» (больше чем ноль). Теперь мы можем убедиться в том, что дебет с кредитом сходится балансовое сальдо будет равно нулю если сложить арифметически в ячейке B16 формулой =B15+B14.
Пример логического выражения в формуле для суммы с условием
Другой пример, когда нам нужно отдельно суммировать цены на группы товаров стоимости до 1000 и отдельно со стоимостью больше 1000. В таком случае одного оператора сравнения нам недостаточно (<1000) придется использовать знак «меньше или равно» (<=1000) иначе цены со значением ровно 1000 не будут включены в расчеты. Более того в таком случае мы уже не можем использовать в другой формуле «больше или равно» (>=1000) иначе мы просуммируем сумму ровно в 1000 – 2 раза, что приведет к ошибочным итоговым результатам:
Это очень распространенная ошибка пользователей Excel при работе с логическими функциями!
Внимание! В первом примере нулевые значения нам необязательно было учитывать, так как на балансовое сальдо это никак не повлияло бы, но во втором случаи нужно составлять критерий условия суммирования иначе, чтобы не допустить ошибочных просчетов.
Второй аргумент функции СУММЕСЛИ, то есть условие, которое должно быть выполнено, записывается между двойными кавычками. В данном примере используется символ сравнения – «меньше» (<). По синтаксису функции необходимо представлять такие записи как строка, то есть в двойных кавычках. Иначе Excel выдаст предупреждение об ошибке в формуле.
Синтаксис формулы суммирования по условию в Excel
В синтаксисе функции СУММЕСЛИ присутствует необязательный для заполнения третий аргумент – диапазон суммирования. В предыдущем примере условие проверялось по тому же диапазону, который нужно было суммировать. Благодаря третьему аргументу появляется возможность проверять условия по одному диапазону, а суммировать другой.
Ниже на рисунке представлена таблица отчета продаж по клиентам за месяц. Напротив, каждого клиента указана сумма, на которую совершил покупки клиент на протяжении месяца. Некоторые клиенты за месяц совершили по несколько покупок, поэтому их наименование дублируется в списке. Необходимо узнать на какую общую сумму купил товара «Клиент3» в данном месяце. Здесь весьма уместно использовать формулу с заполнением третьего аргумента функции СУММЕСЛИ:
Как видно на всех выше приведенных примерах во втором аргументе указывается строка текста с условием, а не логическое выражение как в функции ЕСЛИ. На практике использования функции СУММЕСЛИ этот второй логический аргумент с условием может содержать:
- сроки с текстовым значением;
- числовые значения;
- логические выражения;
- формулы с функциями;
- ссылки на ячейки.
Далее рассмотрим больше примеров составления условий для второго логического аргумента функции СУММЕСЛИ более детально.
Создание критериев условий для функции СУММЕСЛИ
Второй аргумент функции называется «Критерий». Данный логический аргумент используется и в других подобных логических функциях: СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. В каждом случаи аргумент заполняется согласно одних и тех же правил составления логических условий. Другими словами, для всех этих функций второй аргумент с критерием условий является логическим выражением возвращающим результат ИСТИНА или ЛОЖЬ. Это значит, что выражение должно содержать оператор сравнения, например: больше (>) меньше (<) равно (=) неравно (<>), больше или равно (>=), меньше или равно (<=). За исключением можно не указывать оператор равно (=), если должно быть проверено точное совпадение значений.
Создание сложных критериев условий может быть запутанным. Однако если придерживаться нескольких простых правил описанных в ниже приведенной таблице, не будет возникать никаких проблем.
Таблица правил составления критериев условий:
| Чтобы создать условие | Примените правило | Пример |
| Значение равно заданному числу или ячейке с данным адресом. | Не используйте знак равенства и двойных кавычек. | =СУММЕСЛИ(B1:B10;3) |
| Значение равно текстовой строке. | Не используйте знак равенства, но используйте двойные кавычки по краям. | =СУММЕСЛИ(B1:B10;»Клиент5″) |
| Значение отличается от заданного числа. | Поместите оператор и число в двойные кавычки. | =СУММЕСЛИ(B1:B10;»>=50″) |
| Значение отличается от текстовой строки. | Поместите оператор и число в двойные кавычки. | =СУММЕСЛИ(B1:B10;»<>выплата») |
| Значение отличается от ячейки по указанному адресу или от результата вычисления формулы. | Поместите оператор сравнения в двойные кавычки и соедините его символом амперсант (&) вместе со ссылкой на ячейку или с формулой. | =СУММЕСЛИ(A1:A10;»<«&C1) или =СУММЕСЛИ(B1:B10;»<>»&СЕГОДНЯ()) |
| Значение содержит фрагмент строки | Используйте операторы многозначных символов и поместите их в двойные кавычки | =СУММЕСЛИ(A1:A10;»*кг*»;B1:B10) |
Во втором аргументе критериев условий можно использовать разные функции и формулы. Ниже на рисунке изображен список дат и присвоенных им значений. Важно отметить что сегодня на момент написания статьи дата – «03.11.2018». Чтобы суммировать числовые значения только по сегодняшней дате используйте формулу:
Чтобы суммировать только значения от сегодняшнего дня включительно и до конца периода времени воспользуйтесь оператором «больше или равно» (>=) вместе с соответственной функцией =СЕГОДНЯ(). Формула c операторам (>=):
Суммирование по неточному совпадению в условии критерия отбора
Во втором логическом аргументе критериев условий функции СУММЕСЛИ можно применять многозначные символы – (?)и(*) для составления относительных неточных запросов. Знак вопроса (?) – следует читать как любой символ, а звездочка (*) – это строка из любого количества любых символов или пустая строка. Например, нам необходимо просуммировать только защитные краски-лаки с кодом 3 английские буквы в начале наименования:
Суммируются все значения ячеек в диапазоне B2:B16 в соответствии со значениями в ячейках диапазона A2:A16, в которых после третьего символа фрагмент строки «-защита».
Таким образом удалось суммировать только определенную группу товаров в общем списке отчета по складу. Данный фрагмент наименования товара должен встречаться в определенном месте – 3 символа от начала строки. Нет необходимости использовать сложные формулы с функцией =ЛЕВСИМВ() и т.д. Достаточно лишь воспользоваться операторами многозначных символов чтобы сформулировать простой и лаконичный запрос к базе данных с минимальными нагрузками на системные ресурсы.
Skip to content
В таблицах Excel можно не просто находить сумму чисел, но и делать это в зависимости от заранее определённых критериев отбора. Хорошо знакомая нам функция ЕСЛИ позволяет производить вычисления в зависимости от выполнения условия. Функция СУММ позволяет складывать числовые значения. А что если нам нужна формула ЕСЛИ СУММ? Для этого случая в Excel имеется специальная функция СУММЕСЛИ.
Мы рассмотрим, как правильно применить функцию СУММЕСЛИ (Sumif в английской версии) в таблицах Excel. Начнем с самых простых случаев, как можно использовать при этом знаки подстановки, назначить диапазон суммирования, работать с числами, текстом и датами. Особо остановимся на том, как использовать сразу несколько условий. И, конечно, мы применим новые знания на практике, рассмотрев несложные примеры.
- Как пользоваться СУММЕСЛИ в Excel – синтаксис
- Примеры использования функции СУММЕСЛИ в Excel
- Сумма если больше чем, меньше, или равно
- Критерии для текста.
- Подстановочные знаки для частичного совпадения.
- Точная дата либо диапазон дат.
- Сумма значений, соответствующих пустым либо непустым ячейкам
- Сумма по нескольким условиям.
- Почему СУММЕСЛИ у меня не работает?
Хорошо, что функция СУММЕСЛИ одинакова во всех версиях MS Excel. Еще одна приятная новость: если вы потратите некоторое время на ее изучение, вам потребуется совсем немного усилий, чтобы понять другие «ЕСЛИ»-функции, такие как СУММЕСЛИМН, СЧЕТЕСЛИ, СЧЕТЕСЛИМН и т.д.
Как пользоваться СУММЕСЛИ в Excel – синтаксис
Её назначение – найти итог значений, которые удовлетворяют определённым требованиям.
Синтаксис функции выглядит следующим образом:
=СУММЕСЛИ(диапазон, критерий, [диапазон_суммирования])
Диапазон – это область, которую мы исследуем на соответствие определённому значению.
Критерий – это значение или шаблон, по которому мы производим отбор чисел для суммирования.
Значение критерия может быть записано прямо в самой формуле. В этом случае не забывайте, что текст нужно обязательно заключать в двойные кавычки.
Также он может быть представлен в виде ссылки на ячейку таблицы, в которой будет указано требуемое ограничение. Безусловно, второй способ является более рациональным, поскольку позволяет гибко менять расчеты, не редактируя выражение.
Диапазон_суммирования — третий параметр, который является необязательным, однако он весьма полезен. Благодаря ему мы можем производить поиск в одной области, а суммировать значения из другой в соответствующих строках.
Итак, если он указан, то расчет идет именно по его данным. Если отсутствует, то складываются значения из той же области, где производился поиск.
Чтобы лучше понять это описание, рассмотрим несколько простых задач. Надеюсь, что они будут понятны не только «продвинутым» пользователям, но и подойдут для «чайников».
Примеры использования функции СУММЕСЛИ в Excel
Сумма если больше чем, меньше, или равно
Начнем с самого простого. Предположим, у нас есть данные о продажах шоколада. Рассчитаем различные варианты продаж.
В I3 записано:
=СУММЕСЛИ(D2:D21;I2)
D2:D21 – это координаты, в которых мы ищем значение.
I2 – ссылка на критерий отбора. Иначе говоря, мы ищем ячейки со значением 144 и складываем их.
Поскольку третий параметр функции не указан, то мы сразу складываем отобранные числа. Область поиска будет одновременно являться и диапазоном суммирования.
Кроме того, в качестве задания для отбора нужных значений можно указать текстовое выражение, состоящее из знаков >, <, <>, <= или >= и числа.
Можно указать его прямо в формуле, как это сделано в I13
=СУММЕСЛИ(D2:D21;«<144»)
То есть подытоживаем все заказы, в которых количество меньше 144.
Но, согласитесь, это не слишком удобно, поскольку нужно корректировать саму формулу, да и условие еще нужно не забыть заключить в кавычки.
В дальнейшем мы будем стараться использовать только ссылку на критерий, поскольку это значительно упрощает возможные корректировки.
Критерии для текста.
Гораздо чаще встречаются ситуации, когда поиск нужно проводить в одном месте, а в другом — суммировать данные, соответствующие найденному.
Чаще всего это необходимо, если необходимо использовать отбор по определённым словам. Ведь текстовые значения складывать нельзя, а вот соответствующие им числа – можно.
Как простой прием использования формулы СУММ ЕСЛИ в Эксель таблицах, рассчитаем итог по выполненным заказам.
В I3 запишем выражение:
=СУММЕСЛИ(F2:F21;I2;E2:Е21)
F2:F21 – это область, в которой мы отбираем подходящие значения.
I2 – здесь записано, что именно отбираем.
E2:E21 – складываем числа, соответствующие найденным совпадениям.
Конечно, можно указать параметр отбора прямо в выражении:
=СУММЕСЛИ(F2:F21;”Да”;E2:Е21)
Но мы уже договорились, что так делать не совсем рационально.
Важное замечание. Не забываем, что все текстовые значения необходимо заключать в кавычки.
Подстановочные знаки для частичного совпадения.
При работе с текстовыми данными часто приходится производить поиск по какой-то части слова или фразы.
Вернемся к нашему случаю. Определим, сколько всего было заказов на черный шоколад. В результате, у нас есть 2 подходящих наименования товара. Как учесть их оба? Для этого есть понятие неточного соответствия.
Мы можем производить поиск и подсчет значений, указывая не всё содержимое ячейки, а только её часть. Таким образом мы можем расширить границы поиска, применив знаки подстановки “?”, “*”.
Символ “?” позволяет заменить собой один любой символ.
Символ ”*” позволяет заменить собой не один, а любое количество символов (в том числе ноль).
Эти знаки можно применить в нашем случае двумя способами. Либо прямо вписать их в таблицу –
=СУММЕСЛИ(C2:C21;I2;E2:Е21) , где в E2 записано *[слово]*
либо
=СУММЕСЛИ(C2:C21;»*»&I2&»*»;E2:E21)
где * вставлены прямо в выражение и «склеены» с нужным текстом.
Давайте потренируемся:
- “*черный*” — мы ищем фразу, в которой встречается это выражение, а до него и после него – любые буквы, знаки и числа. В нашем случае этому соответствуют “Черный шоколад” и “Супер Черный шоколад”.
- “Д?” — необходимо слово из 2 букв, первая из которых “Д”, а вторая – любая. В нашем случае подойдет “Да”.
- “???” — найдем слово из любых 3 букв
=СУММЕСЛИ(F2:F21;”???”;E2:E21)
Этому требованию соответствует “Нет”.
- “???????*” — текст из любых 7 и более букв.
=СУММЕСЛИ(B2:B21;“???????*”;E8:E28)
Подойдет “Зеленый”, “Оранжевый”, “Серебряный”, “Голубой”, “Коричневый”, “Золотой”, “Розовый”.
- “З*” — мы выбираем фразу, первая буква которой “З”, а далее – любые буквы, знаки и числа. Это “Золотой” и “Зеленый”.
- “Черный*” — подходит фраза, которая начинаются именно с этого слова, а далее – любые буквы, знаки и числа. Подходит “Черный шоколад”.
Примечание. Если вам необходимо в качестве задания для поиска применять текст, который содержит в себе * и ?, то используйте знак тильда (~), поставив его перед этими символами. Тогда * и ? будут считаться обычными символами, а не шаблоном:
=СУММЕСЛИ(B2:B21;“*~?*”;E8:E28)
Важное замечание. Если в вашем тексте для поиска встречается несколько знаков * и ?, то тильду (~) нужно поставить перед каждым из них. К примеру, если мы будем искать текст, состоящий из трех звездочек, то формулу ЕСЛИ СУММ можно записать так:
=СУММЕСЛИ(B2:B21;“~*~*~*”;E8:E28)
А если текст просто содержит в себе 3 звездочки, то можно наше выражение переписать так:
=СУММЕСЛИ(B2:B21;“*~*~*~**”;E8:E28)
Точная дата либо диапазон дат.
Если нам нужно найти сумму чисел, соответствующих определённой дате, то проще всего в качестве критерия указать саму эту дату.
Примечание. При этом не забывайте, что формат указанной вами даты должен соответствовать региональным настройкам вашей таблицы!
Обратите внимание, что мы также можем здесь вписать ее прямо в формулу, а можем использовать ссылку.
Рассчитываем итог продаж за сегодняшний день – 04.02.2020г.
=СУММЕСЛИ(A2:A21;I1;E2:E21)
или же
=СУММЕСЛИ(A2:A21;СЕГОДНЯ();E2:E21)
Рассчитаем за вчерашний день.
=СУММЕСЛИ(A2:A21;СЕГОДНЯ()-1;E2:E21)
СЕГОДНЯ()-1 как раз и будет «вчера».
Складываем за даты, которые предшествовали 1 февраля.
=СУММЕСЛИ(A2:A21;»<«&»01.02.2020»;E2:E21)
После 1 февраля включительно:
=СУММЕСЛИ(A2:A21;»>=»&»01.02.2020″;E2:E21)
А если нас интересует временной интервал «от-до»?
Мы можем рассчитать итоги за определённый период времени. Для этого применим маленькую хитрость: разность функций СУММЕСЛИ. Предположим, нам нужна выручка с 1 по 4 февраля включительно. Из продаж после 1 февраля вычитаем все, что реализовано после 4 февраля.
=СУММЕСЛИ(A2:A21;»>=»&»01.02.2020″;E2:E21) — СУММЕСЛИ(A2:A21;»>=»&»04.02.2020″;E2:E21)
Сумма значений, соответствующих пустым либо непустым ячейкам
Случается, что в качестве условия суммирования нужно использовать все непустые клетки, в которых есть хотя бы одна буква, цифра или символ.
Рассмотрим ещё один вариант использования формулы СУММ ЕСЛИ в таблице Excel, где нам необходимо подсчитать заказы, в которых нет отметки о выполнении, а также сколько было вообще обработанных заказов.
Если критерий указать просто “*”, то мы учитываем для подсчета непустые ячейки, в которых имеется хотя бы одна буква или символ (кроме пустых).
=СУММЕСЛИ(F2:F21;»*»;E2:E21)
Точно такой же результат даёт использование вместо звездочки пары знаков «больше» и «меньше» — <>.
=СУММЕСЛИ(F2:F21;»<>»;E2:E21)
Теперь рассмотрим, как можно находить сумму, соответствующую пустым ячейкам.
Для того, чтобы найти пустые, в которых нет ни букв, ни цифр, в качестве критерия поставьте парные одинарные кавычки ‘’, если значение критерия указано в ячейке, а формула ссылается на неё.
Если же указать на отбор только пустых ячеек в самой формуле СУММ ЕСЛИ, то впишите двойные кавычки.
=СУММЕСЛИ(F2:F21;«»;E2:E21)
Сумма по нескольким условиям.
Функция СУММЕСЛИ может работать только с одним условием, как мы это делали ранее. Но очень часто случается, что нужно найти совокупность данных, удовлетворяющих сразу нескольким требованиям. Сделать это можно как при помощи некоторых хитростей, так и с использованием других функций. Рассмотрим все по порядку.
Вновь вернемся к нашему случаю с заказами. Рассмотрим два условия и посчитаем, сколько всего сделано заказов черного и молочного шоколада.
1. СУММЕСЛИ + СУММЕСЛИ
Все просто:
=СУММЕСЛИ($C$2:$C$21;»*»&H3&»*»;$E$2:$E$21)+СУММЕСЛИ($C$2:$C$21;»*»&H4&»*»;$E$2:$E$21)
Находим сумму заказов по каждому виду товара, а затем просто их складываем. Думаю, с этим вы уже научились работать :).
Это самое простое решение, но не самое универсальное и далеко не единственное.
2. СУММ и СУММЕСЛИ с аргументами массива.
Вышеупомянутое решение очень простое и может выполнить работу быстро, когда критериев немного. Но если вы захотите работать с несколькими, то она станет просто огромной. В этом случае лучшим подходом является использование в качестве аргумента массива критериев. Давайте рассмотрим этот подход.
Вы можете начать с перечисления всех ваших условий, разделенных запятыми, а затем заключить итоговый список, разделенный точкой с запятой, в {фигурные скобки}, который технически называется массивом.
Если вы хотите найти покупки этих двух товаров, то ваши критерии в виде массива будут выглядеть так:
СУММЕСЛИ($C$2:$C$21;{«*черный*»;»*молочный*»};$E$2:$E$21)
Поскольку здесь использован массив критериев, то результатом вычислений также будет массив, состоящий из двух значений.
А теперь воспользуемся функцией СУММ, которая умеет работать с массивами данных, складывая их содержимое.
=СУММ(СУММЕСЛИ($C$2:$C$21;{«*черный*»;»*молочный*»};$E$2:$E$21))
Важно, что результаты вычислений в первом и втором случае совпадают.
3. СУММПРОИЗВ и СУММЕСЛИ.
А если вы предпочитаете перечислять критерии в какой-то специально отведенной для этого части таблицы? Можете использовать СУММЕСЛИ в сочетании с функцией СУММПРОИЗВ, которая умножает компоненты в заданных массивах и возвращает сумму этих произведений.
Вот как это будет выглядеть:
=СУММПРОИЗВ(СУММЕСЛИ(C2:C21;H3:H4;E2:E21))
в H3 и H4 мы запишем критерии отбора.
Но, конечно, ничто не мешает вам перечислить значения в виде массива критериев:
=СУММПРОИЗВ(СУММЕСЛИ(C2:C21;{«*черный*»;»*молочный*»};E2:E21))
Результат, возвращаемый в обоих случаях, будет идентичен тому, что вы наблюдаете на скриншоте.
Важное замечание! Обратите внимание, что все перечисленные выше три способа производят расчет по логическому ИЛИ. То есть, нам нужны продажи шоколада, который будет или черным, или молочным.
Почему СУММЕСЛИ у меня не работает?
Этому может быть несколько причин. Иногда ваше выражение не возвращает того, что вы ожидаете, только потому, что тип данных в ячейке или в каком-либо аргументе не подходит для нее. Итак, вот что нужно проверить.
1. «Диапазон данных» и «диапазон суммирования» должны быть указаны ссылками, а не в виде массива.
Первый и третий атрибуты функции всегда должны быть ссылкой на область таблицы, например A1: A10. Если вы попытаетесь передать что-нибудь еще, например, массив {1,2,3}, Excel выдаст сообщение об ошибке.
Правильно: =СУММЕСЛИ(A1:A3, «цвет», C1:C3)
Неверно : =СУММЕСЛИ({1,2,3}, «цвет», C1:C3)
2. Ошибка при суммировании значений из других листов или рабочих книг.
Как и любая другая функция Excel, СУММЕСЛИ может ссылаться на другие листы и рабочие книги, если они в данный момент открыты.
Найдем сумму значений в F2: F9 на листе 1 книги 1, если соответствующие данные записаны в столбце A, и если среди них содержатся «яблоки»:
=СУММЕСЛИ([Книга1.xlsx]Лист1!$A$2:$A$9,»яблоки»,[Книга1.xlsx]Лист1!$F$2:$F$9)
Однако это перестанет работать, как только Книга1 будет закрыта. Это происходит потому, что области, на которые ссылаются формулы в закрытых книгах, преобразуются в массивы и хранятся в таком виде в текущей книге. А поскольку в аргументах 1 и 3 массивы не допускаются, то формула выдает ошибку #ЗНАЧ!.
3. Чтобы избежать проблем, убедитесь, что диапазоны данных и поиска имеют одинаковый размер.
Как отмечалось в начале этого руководства, в современных версиях Microsoft Excel они не обязательно должны иметь одинаковый размер. Но вот в Excel 2000 и более ранних версиях это может вызвать проблемы. Однако, даже в самых последних версиях Excel сложные выражения, в которых диапазон сложения имеет меньше строк и/или столбцов, чем диапазон поиска, являются капризными. Вот почему рекомендуется всегда иметь их одинакового размера и формы.
Примеры расчета суммы: