База Знаний: Функции Calc. Условный подсчёт и суммирование
В этой статье приведён обзор различных способов условного подсчёта и суммирования содержимого ячеек, в зависимости от результата некоторой проверки.
Условия в диапазонах ячеек
Рис. 1. Условия в диапазонах ячеек.
Один простой метод для подсчета или суммирования, использующий несколько условий — ввести условия в новую строку или столбец. Например, если диапазон A1:A6 содержит список цветов, а диапазон B1:B6 — список размеров, то мы можем ввести в ячейку D1 формулу =(A1=“красный”), которая возвращается TRUE или FALSE в зависимости от того, содержит ячейка A1 текст красный или нет. В качестве альтернативы, мы можем ввести в ячейку D1 формулу =AND(A1=“красный”; B1=“большой”), которая возвращает TRUE, если ячейка A1 содержит красный И ячейка B1 содержит большой и FALSE в противном случае. Скопируйте и вставьте эту формулу в ячейки D2:D6, и мы имеем диапазон ячеек, которые содержат TRUE, если условия выполняются и FALSE в противном случае.
В числовых вычислениях, TRUE рассматривается как 1, а FALSE — как 0. Таким образом введённая формула =SUM(D1:D6) просто суммирует эти единицы и нули, и даёт нам количество элементов, которые соответствуют одновременно условиям красный И большой.
На самом деле, так как TRUE и FALSE оцениваются как 1 и 0, мы не нуждаемся в функции AND — в D1 мы можем просто записать =(A1=“красный”)*(B1=“большой”), и скопировать / вставить вниз в ячейки D2:D6.
Теперь пойдём дальше. Предположим, что диапазон C1:C6 содержит список весов этих элементов, и мы желаем знать суммарную массу для всех больших и красных элементов. В ячейке D1 мы запишем =(A1=“красный”)*(B1=“большой”)*C1, и скопируем / вставим эту формулу вниз в ячейки D2:D6. Ячейка D1 будет содержать вес в C1, если условия будут выполняться (и ноль в противном случае) и так далее для ячеек D2:D6, следовательно =SUM(D1:D6) теперь даст нам суммарный вес.
Рис. 2: Условия в диапазонах ячеек.
В качестве альтернативы, можно заполнить диапазон D1:D6 формулой массива. В ячейке D1, напишите =(A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6, и введите, нажав Ctrl + ⇧ Shift + ↵ Enter . Все ячейки в диапазоне D1:D6 теперь показывают требуемый вес как ранее.
Функция SUMPRODUCT
Функция SUMPRODUCT может использоваться для выполнения вычислений подсчёта и суммирования в предыдущем разделе, не используя дополнительные столбцы. Необходимо понять формулы массива, чтобы понять её.
Используя пример суммирования из предыдущего раздела, A1:A6=“красный”, B1:B6=“большой” и C1:C6 можно рассмотреть как 3 отдельных массива, не отображаемых, но внутренне рассчитываемых.
=SUMPRODUCT(A1:A6=“красный”; B1:B6=“большой”; C1:C6), перемножит соответствующие элементы массивов вместе и возвратит их сумму, то есть:
Это так-же даёт нам суммарный вес, однако, дополнительный столбец не требуется.
Для переносимости в Excel, используйте формулу =SUMPRODUCT((A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6),поскольку Calc и Excel рассматривают логические результаты по-разному.
Заметьте, что формулы с использованием функции SUMPRODUCT просто вводятся нажатием клавиши ↵ Enter — они не требуют нажатия Ctrl + ⇧ Shift + ↵ Enter даже при том, что связанны с массивами.
Рис. 3: SUMPRODUCT.
Кроме того, знайте, что вычисления, использующие очень большие массивы берут много компьютерного процессорного времени, и могут сильно замедлить обработку электронных таблиц.
Функция SUM с формулами массива
В качестве альтернативы функции SUMPRODUCT можно использовать функцию SUM. Предыдущий пример был бы записан следующим образом:=SUM((A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6))и введён как формула массива, нажатием Ctrl + ⇧ Shift + ↵ Enter . Как и в случае с функцией SUMPRODUCT, это работает, перемножая соответствующие элементы массивов вместе и возвращая их сумму.
Суммирование элементов с определённым форматированием
Функция CELL возвращает информацию о ячейках, например, что данные отображаются в формате числа или даты и ширину столбца. Не существует функции, которая возвращает цвет или шрифт ячейки. Если не существует самостоятельной формулы, имеющая отношение к цвету или шрифту, то необходимо использовать макрос.
Суммирование элементов, соответствующих элементам в отдельном списке
Предположим, что диапазон A1:A9 содержит список дат, диапазон B1:B9 — телефонные номера, а диапазон C1:C9 — затраты на телефонные звонки. F1:F5 — список определённых телефонных номеров, и необходимо узнать общую стоимость звонков по этим номерам.
В ячейке D1 введём:=ISNUMBER(MATCH(B1; F$1:F$5; 0))*C1и скопируем и вставим в ячейки D2:D9. =SUM(D1:D9) теперь даёт общую стоимость звонков.
Рис. 4: ISNUMBER.
Чтобы выполнять это вычисление без дополнительного столбца, можно использовать: =SUMPRODUCT(NOT(ISERROR(MATCH(B1:B9; F$1:F$5; 0))); C1:C9)
Мы обсуждали выше, как может быть важно проверить параметры настройки Calc; это — хороший пример — если пользователь имеет включённые регулярные выражения (по умолчанию), номер телефона, записанный как (720) 528-1700 интерпретируется как регулярное выражение и может соответствовать записям иным, чем (720) 528-1700.
СУММ (функция СУММ)
Функция СУММ добавляет значения. Вы можете складывать отдельные значения, диапазоны ячеек, ссылки на ячейки или данные всех этих трех видов.
- =СУММ(A2:A10) Добавляет значения в ячейки A2:10.
- =СУММ(A2:A10;C2:C10) Добавляет значения в ячейки A2:10, а также ячейки C2:C10.
СУММ(число1;[число2];…)
Имя аргумента
Первое число для сложения. Это может быть число 4, ссылка на ячейку, например B6, или диапазон ячеек, например B2:B8.
Это второе число для сложения. Можно указать до 255 чисел.
Рекомендации по использованию функции СУММ
Этот раздел содержит некоторые рекомендации по работе с функцией СУММ. Многие из этих рекомендаций можно применить и к другим функциям.
Метод =1+2 или =A+B. Вы можете ввести =1+2+3 или =A1+B1+C2 и получить абсолютно точные результаты, однако этот метод ненадежен по ряду причин.
- Опечатки. Допустим, вы пытаетесь ввести много больших значений такого вида:
- =14598,93+65437,90+78496,23
А теперь попробуйте проверить правильность записей. Гораздо проще поместить эти значения в отдельные ячейки и использовать их в формуле СУММ. Кроме того, значения в ячейках можно отформатировать, чтобы привести их к более наглядному виду, чем если бы они были в формуле.
- =A1+B1+C1 или =A1+A2+A3
Если ячейки, на которые указывают ссылки, содержат нечисловые (текстовые) значения, формула может вернуть ошибку #ЗНАЧ!. Функция СУММ пропускает текстовые значения и выдает сумму только числовых значений.
При удалении строки или столбца формулы не обновляются: из них не исключаются удаленные значения, поэтому возвращается ошибка #ССЫЛКА!. Функция СУММ, в свою очередь, обновляется автоматически. 
При вставке строки или столбца формула не обновляется — в нее не включается добавленная строка, тогда как функция СУММ будет автоматически обновляться (пока вы не вышли за пределы диапазона, на который ссылается формула). Это особенно важно, когда вы рассчитываете, что формула обновится, но этого не происходит. В итоге ваши результаты остаются неполными, и этого можно не заметить. 
- =СУММ(A1;A2;A3;B1;B2;B3)
вы изначально закладываете в нее вероятность появления ошибок при вставке или удалении строк в указанном диапазоне по тем же причинам. Гораздо лучше использовать отдельные диапазоны, например:
Такая формула будет обновляться при добавлении и удалении строк.
Вопросы и ответы
- Мне нужно добавить, вычесть, умножить или поделить числа. Просмотрите серию учебных видео: Основные математические операции в Excel или Использование Microsoft Excel в качестве калькулятора.
- Как уменьшить или увеличить число отображаемых десятичных знаков? Можно изменить числовой формат. Выберите соответствующую ячейку или соответствующий диапазон и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек, затем щелкните вкладку Число и выберите нужный формат, указав при этом нужное количество десятичных знаков.
- Как добавить или вычесть значения времени? Есть несколько способов добавить или вычесть значения времени. Например, чтобы получить разницу между 8:00 и 12:00 для вычисления заработной платы, можно воспользоваться формулой =(«12:00»-«8:00»)*24, т. е. отнять время начала от времени окончания. Обратите внимание, что Excel вычисляет значения времени как часть дня, поэтому чтобы получить суммарное количество часов, необходимо умножить результат на 24. В первом примере используется формула =((B2-A2)+(D2-C2))*24 для вычисления количества часов от начала до окончания работы с учетом обеденного перерыва (всего 8,5 часов). Если вам нужно просто добавить часы и минуты, вы можете просто вычислить сумму, не умножая ее на 24. Во втором примере используется формула =СУММ(A6:C6), так как здесь нужно просто посчитать общее количество часов и минут, затраченных на задания (5:36, т. е. 5 часов 36 минут).
Дополнительные сведения см. в статье Сложение и вычитание значений времени - Как получить разницу между датами? Как и значения времени, значения дат можно добавить или вычесть. Вот распространенный пример вычисления количества дней между датами. Для этого используется простая формула =B2-A2. При работе со значениями дат и времени важно помнить, что дата или время начала вычитается из даты или времени окончания.
Другие способы работы с датами описаны в статье Вычисление разности двух дат. - Как вычислить сумму только видимых ячеек? Иногда когда вы вручную скрываете строки или используете автофильтр, чтобы отображались только определенные данные, может понадобиться вычислить сумму только видимых ячеек. Для этого можно воспользоваться функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Если вы используете строку итогов в таблице Excel, любая функция, выбранная из раскрывающегося списка «Итог», автоматически вводится как промежуточный итог. Дополнительные сведения см. в статье Данные итогов в таблице Excel.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как называется функция excel которая вычисляет сумму значений переданных ей ячеек и диапазонов
Скачай курс
в приложении
Перейти в приложение
Открыть мобильную версию сайта
© 2013 — 2023. Stepik
Наши условия использования и конфиденциальности
Public user contributions licensed under cc-wiki license with attribution required
Электронные таблицы Excel
Внимание! Все тесты в этом разделе разработаны пользователями сайта для собственного использования. Администрация сайта не проверяет возможные ошибки, которые могут встретиться в тестах.
Работа с электронными таблицами Excel. Основные обозначения, функции.
Система оценки: 5* балльная
Список вопросов теста
Вопрос 1
Укажите правильное обозначение диапазона таблицы в программе Excel
Варианты ответов
Вопрос 2
Электронная таблица представляет собой:
Варианты ответов
- совокупность пронумерованных строк и столбцов
- совокупность нумерованных строк и поименованных буквами латинского алфавита столбцов
- совокупность строк и столбцов, именуемых пользователем произвольным образом
- совокупность поименованных буквами латинского алфавита строк и нумерованных столбцов
Вопрос 3
Какие данные можно вводить в ячейку в программе Excel ?
Варианты ответов
- число
- формула
- текст
- все перечисленные
Вопрос 4
Укажите, что определяет функция СРЗНАЧ в программе Excel ?
Варианты ответов
- поиск самого короткого текста
- среднее арифметическое значение заданного диапазона ячеек
- среднее значение от деления ячеек
- упорядочение по убыванию чисел
Вопрос 5
Выберите верную запись формулы для электронной таблицы:
Варианты ответов
- С3=С1+2*С
- =3С+4*D4
- =A5B5+23
- =A2*A3-A4
Вопрос 6
Как называется функция Excel, которая вычисляет сумму значений переданных ей ячеек и диапазонов?
Вопрос 7
Какую формулу нужно вписать в ячейку A1 для того, чтобы вычислить сумму значений выделенного диапазона?
Вопрос 8
Какую формулу нужно вписать в ячейку A1 для того, чтобы вычислить среднее арифметическое значений выделенного диапазона?
Вопрос 9
Какое значение появится в ячейке С3 при вводе указанных формул?
Вопрос 10
Какое значение появится в ячейке С3 при вводе указанных формул?