Как в экселе посчитать сумму определенных ячеек
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Суммирование только видимых ячеек
Некоторые из приведенных ниже способов чувствительны к этим факторам, некоторые – нет. Это нужно учитывать при выполнении вычислений:
СУММ (SUM) – тупо суммирует все в выделенном диапазоне без разбора, т.е. и скрытые строки в том числе. Если хотя бы в одной ячейке есть любая ошибка – перестает считать и тоже выдает ошибку на выходе.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 9 в первом аргументе – суммирует все видимые после фильтра ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 109 в первом аргументе – суммирует все видимые после фильтра и группировки (или скрытия) ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.
Если нужно не суммировать, то можно использовать другие значения кода математической операции:
АГРЕГАТ (AGGREGATE) – самая мощная функция, появившаяся в Office 2010. Также как и ПРОМЕЖУТОЧНЫЕ.ИТОГИ может не только суммировать, но и считать среднее, количество, минимум, максимум и т.д. — код операции задается первым аргументом. Плюс к этому имеет множество опций по подсчету, которые можно указать вторым аргументом:
Ссылки по теме
- Выборочные вычисления по одному или нескольким условиям
- Вставка в отфильтрованные строки
- Быстрое скрытие и отображение ненужных строк и столбцов
Как в Excel посчитать сумму определенных ячеек (+ процент от числа). Рассмотрим неск. простых способов
Зачем считать вручную, когда есть Excel? �� (особенно, когда вопрос касается не 1-2-х строк) . Этот вопрос, оказывается, до сих пор еще актуален. Например, не так давно наблюдал, как один знакомый заполнял неск. страниц с таблицами, считая их на калькуляторе (и затратив на сие, я думаю, не менее неск. часов, в то время как в Excel потратил бы 10 мин.).
Собственно, в этот же вечер решил набросать неск. примеров на блоге — привести простые варианты подсчетов, с которыми многие сталкиваются в обычной жизни. Запомнив 2-3 формулы — можно серьёзно упростить себе ряд задач! ��
Примеры расчётов
Вариант 1 («простая» сумма)
Итак.
Для примера я набросал небольшую таблицу с вымышленными продуктами и ценами (ничего общего с реальностью она не имеет — взял просто для наглядности!).
Допустим нам нужно посчитать сумму значений из ячеек у столбца «B» (сентябрь).
В этом случае нужно выбрать одну из свободных ячеек и в строку формул написать: =B2+B3+B4 и т.д. (т.е. после знака » attachment_45762″ style=»width: 808px» >
Другой вариант : выбрать свободную ячейку под нужным столбцом, затем перейти в раздел «Формулы» и нажать по инструменту «Автосумма» — в результате Excel автоматически выделит все значения в столбце и сложит их. А вы сразу же увидите результат, даже если в таблице 1000 строк! ��
Кстати , формулу суммы можно использовать и вручную: для этого достаточно выбрать ячейку (нажать по ней мышкой), затем написать в ней «=сумм(c2:c6)» и нажать Enter (вместо c2:c6 нужно написать свой диапазон ячеек, которые нужно сложить!) . См. пример ниже. ��
Excel — формула суммы
Вариант 2 (сумма с условием)
Допустим, в нашей табличке нам нужно посчитать в ноябре (столбец «D», см. скрин ниже ��) только те продукты, которые есть в наличии: т.е. сложить только 3 строки, напротив которых присутствует аббревиатура «Есть» .
Как это сделать:
Используем нижеприведенную формулу (обратите внимание, что сначала в формуле пишется диапазон E2:E6, по которому будет проверяться условие; затем в кавычках нужный нам текст/условие; а диапазон D2:D6 — что складывать, если условие выполняется).
Сумма с условием
Рассмотрим немного отличную задачу : скажем, нам нужно сложить только те строки, в которых число больше или равно 80. В нашем примере — таких строк всего две: «Молоко» и «Яблоки» (прим.: в реальной задаче может быть неск. сотен/тысяч таких строк).
Формулу можно написать следующую:
Разумеется, знак «=» можно убрать, и «80» изменить на свое число. Дальше формула всё сделает самостоятельно. (в моем случае: сложит числа 80 и 90 ��)��
Еще один пример суммы с условием
Вариант 3 (как посчитать кол-во строк)
Допустим, нам нужно посчитать не сумму значений в различных ячейках столбиков, а количество строк (с определенными условиями). Скажем, сколько строк в моей таблице ниже ��, где встречается слово «Есть» ?
Всего 3 позиции, однако, если бы таблица была на 1000 строк — визуально бы вы их не посчитали! Нужна формула. ��
Разумеется, вместо «Есть» можно указать «Нет» или то слово, которое встречается в вашей табличке.
«Счетесли» — формула в Excel
Кстати, формула «=СЧЁТЕСЛИ» хороша и тем, что условий может быть много. Например, задачка : нужно узнать кол-во строк с ценой больше 10 и с текущим статусом «Нет» . Если взглянуть на таблицу — то таких строк всего 2! ��
Формула для счета:
Считаем кол-во строк, отвечающие двум критериям
Вариант 4 (процент от числа)
Для начала хочу порекомендовать одну мою предыдущую заметку, в которой я показал как можно считать различные задачки с процентами, в том числе и в Excel. Там всё подробно и просто! ��
�� В помощь!
Как посчитать проценты: от числа, от суммы чисел и др. [в уме, на калькуляторе и с помощью Excel] — https://ocomp.info/kak-poschitat-protsentyi.html
Теперь вернемся к нашей таблице.
Допустим выше мы посчитали уже, что у нас 3 позиции со статусом «Есть» (т.е. в наличии), а всего позиций 5. Теперь вопрос: а сколько процентов у нас в наличии от общего числа?
Т.е. по сути нам нужно узнать сколько процентов число 3 составляет от числа 5: для этого достаточно выбрать свободную ячейку и написать там формулу для деления одного числа на другое (в моем случае 3/5 или E9/E10). Да, кстати, не забудьте в Excel нажать на значок «%» (так ячейка станет «процентной». ).
Результат: 60%! Вроде бы все просто. ����
Считаем проценты — Excel
Другая задачка : допустим мы посчитали сумму значений из ячеек столбца «D» — получилось 295. �� И теперь нам требуется найти 30% от этого числа. Как это сделать?
Достаточно написать формулу вида: =(D9*30)/100 (где вместо «D9» — указывайте ссылку на нужную вам ячейку; вместо «30» — свое значение процентов, например, «5»; значение «100» менять в формуле не нужно). Ответ : 30% от числа 295 равно числу 88,5.
Как найти 30% от числа 295 (суммы ячеек из столбца)
Думаю, представленных примеров вполне достаточно для общего понимания. Если что: дополнительные уточнения — можете добавить в комментарии. (под статьей) или посм. ��в этой заметке.
За сим же я откланяюсь, всем успехов!
Как в Excel подсчитать определенные ячейки
«Подсчёт определенных ячеек в Excel» звучит как совсем несложная задача, но на практике у неё настолько много вариаций, что мне с трудом удастся перечислить их все.
Но я всё же постараюсь подойти к этому вопросу максимально структурно.
Если ответы на их все дать на одной странице, будет неудобно. Поэтому ответ на каждый из вопросов содержится в отдельных ссылках внутри этой статьи.
Чем определяется ячейка (параметры)
Удивительно, но с виду простая прямоугольная ячейка Excel на поверку — довольно сложный объект. Ведь она представляет собою одновременно:
- Форматирование ячейки,
- Формулу в ячейке,
- Значение ячейки.
Причем каждая из этих составляющих ячейки также может определяться множеством других параметров и характеристик. Разберем их по порядку и попробуем определить соответствующие задачи по извлечению и подсчету таких ячеек.
Подсчет ячеек по параметрам их форматирования
Ячейка богата способами её отформатировать — у неё есть
- Текст и его атрибуты:
- Начертание (полужирный, курсив, подчеркнутый…),
- Размер (8, 9, 10…),
- Шрифт (Arial, Verdana, Times New Roman и т.д.),
- Цвет шрифта,
- Выравнивание по ширине и высоте.
- Сплошной,
- Узор,
- Градиент.
- Ширина,
- Высота.
Вряд ли кому-то придется искать и считать ячейки с узором вида «50% серый», но, например, довольно часто люди ищут:
- Формулу «жирный шрифт» в Excel,
- Как посчитать выделенные цветом ячейки,
- Как посчитать ячейки определённого цвета.
Подсчёт ячеек по параметрам их формул
Формулы в ячейках либо есть, либо их нет, но и формулы в свою очередь могут содержать самые разные паттерны, доступные для поиска. В Excel 2013 и более поздних версиях наиболее полезной для этих целей будет функция Ф.ТЕКСТ. Она позволяет показывать формулы в ячейках как текст, после чего их можно обрабатывать как обычные текстовые данные.
Подсчёт ячеек по наличию значения
Самый верхнеуровневый параметр значения ячейки — это его наличие. А отсюда и соответствующие задачи:
- Посчитать пустые ячейки;
- Посчитать непустые ячейки;
- Посчитать количество заполненных ячеек;
- Посчитать количество ячеек с определенным значением.
Подсчёт ячеек по типу данных
Но и наличие значения можно сузить до наличия определённого значения по его типу, не глядя вглубь самого значения. О каких типах речь:
- Посчитать ячейки с логическим типом данных;
- Посчитать числовые ячейки;
- Посчитать текстовые ячейки.
Подсчёт по характеристикам самих значений ячеек
У каждого значения ячейки может быть ряд параметров, который в свою очередь зависит от типа данных, которые в них содержатся.
От простого к сложному:
Логические значения
Здесь все просто — в таких ячейках могут быть лишь два значения — ИСТИНА и ЛОЖЬ, отсюда и задачи
- посчитать количество ИСТИН в Excel;
- посчитать ЛОЖЬ/ложные значения в Excel.
Числовые значения
С числами все чуть сложнее, помимо самого их наличия, числа могут быть больше или меньше определенных значений, их часто суммируют и т.д. Поэтому вариаций задач с ними больше:
- Посчитать цифры в ячейках и ячейки с цифрами;
- Посчитать ячейки со значением больше нуля;
- Посчитать сумму отфильтрованных ячеек;
- Посчитать сумму определённых ячеек.
Текстовые значения
Наиболее вариативны задачи по работе с текстовыми ячейками:
- Посчитать ячейки, содержащие определенный текст
- в начале ячейки,
- в конце,
- где угодно,
- целиком совпадающие с текстом;