RU/kb/00000473

 < Энциклопедия | База знаний | Модули OpenOffice.org | 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), перемножит соответствующие элементы массивов вместе и возвратит их сумму, то есть:

(A1=“red”)*(B1=“big”)*C1 + (A2=“red”)*(B2=“big”)*C2 + ...

Это так-же даёт нам суммарный вес, однако, дополнительный столбец не требуется.

Для переносимости в 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.

---

А. Е. Харламенков

---

InfraOffice.pro 3.1.x

---

---

К началу страницы