Уважаемые коллеги, в связи с многочисленными ошибками при выполнении возникла необходимость подробно разобрать еще одно из заданий ЛР 3.2. — а именно, задание 9.2
Написать формулу, вычисляющую:
Сумму элементов диапазона из отрезка [-5;10]
Типичная ошибка состоит в некорректном использовании функции
СУММЕСЛИ(диапазон;критерий;диапазон_суммирования),
где диапазон — диапазон вычисляемых ячеек, критерий — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки (например, 32, «32», «>32», «яблоки») и диапазон_суммирования — фактические ячейки для суммирования.
Дело в том, что упомянутый критерий суммирования,
- во-первых, в данной функции может быть только один, а
- во-вторых, при его задании необходимо соблюдение гораздо более жестких условий, чем при задании «обычного» логического выражения.
Например, первое условие — то, что суммируемые элементы диапазона должны быть большими либо равными -5, можно задать как «>=-5» или «>=»&G2 — в том случае, если упомянутое значение содержится в ячейке G2. Однако использование функции СУММЕСЛИ() «в одно действие» не поможет, если условий больше одного, как в данном случае (второе — то, что суммируемые элементы должны быть меньшими либо равными 10), а также если условие чуть более сложное — например, найти сумму четных элементов диапазона.
В этих случаях необходимо использовать комбинации функций СУММ() и ЕСЛИ (). Например, просуммировать четные числа диапазона A1:B4 можно так:
(1) {=СУММ(ЕСЛИ(ОСТАТ(A1:B4;2)=0;A1:B4;0))}
а просуммировать элементы этого же диапазона из отрезка [-5;10] — так:
(2) {=СУММ(ЕСЛИ(A1:B4>=-5;ЕСЛИ(A1:B4<=10;A1:B4;0);0))} (через 2 функции ЕСЛИ() — внешнюю и внутреннюю) или так —
(3) {=СУММ(ЕСЛИ((A1:B4>=-5)*(A1:B4<=10);A1:B4;0))} (через задание 2-х логических условий в одной функции ЕСЛИ()) или
в несколько действий:
(4) =СУММ(A1:B4)-СУММЕСЛИ(A1:B4;»<-5″)-СУММЕСЛИ(A1:B4;»>10″) — первый вариант или
(5) =СУММЕСЛИ(A1:B4;»>=-5″)+СУММЕСЛИ(A1:B4;»<=10″)-СУММ(A1:B4) — второй вариант (о других вариантах — ниже).
обратите внимание, что 3 первые, (1-3) («в одно действие») формулы являются формулами массива, т.е. будут выдавать корректный результат при нажатии клавиш Ctrl+Shift+Enter (иначе будет выдано #ЗНАЧ).
Формула массива — формула, выполняющая несколько вычислений над одним или несколькими наборами значений, а затем возвращающая один или несколько результатов.
обратите также внимание, как именно в формуле массива (3) заданы два условия в функции ЕСЛИ(). Дело в том, что эквивалентом логического значения ИСТИНА является 1, логического значения ЛОЖЬ является 0, таким образом, перемножение двух значений эквивалентно выполнения логической операции И (), то есть в качестве ответа выдается 1 (то есть ИСТИНА) в том, и только в том случае, если оба аргумента равны 1 (то есть ИСТИНА) и 0 — в любом другом случае. Более того, использование самой имеющейся в Excel логической функции И () в формуле массива выдает некорректный результат.
Ознакомьтесь с видеоуроком — построением формулы нахождения суммы четных отрицательных чисел диапазона. Обратите внимание, что условия в данном случае также два
- четность суммируемых чисел диапазона (то есть равенство 0 остатка от деления на 2) — ОСТАТ(A1:B4;2)=0;
- и то, что суммируемые числа — отрицательные — A1:B4<0;
Результирующая формула массива имеет вид:
{=СУММ(ЕСЛИ((ОСТАТ(A1:B4;2)=0)*(A1:B4<0);A1:B4;0))}
(для просмотра рекомендуется полноэкранный режим)
Возвращаясь к первоначальной задаче (найти сумму элементов диапазона из отрезка [-5;10]) — еще один возможный вариант формулы массива с задействованием вложенной функции ЕСЛИ() для того, чтобы задать второе условие (т.е. аналог (2)):
{=СУММ(ЕСЛИ(A1:B4>=-5;ЕСЛИ(A1:B4<=10;A1:B4;);))}
обратите внимание, что в этом случае для функции ЕСЛИ() вообще не заданы на соответствующих позициях значения, присваиваемые в том случае, если логическое условие не выполняется (в предыдущем случае задавались значения, равные 0) — возможный, хотя и, вероятно, менее «читаемый» вариант, чем (2).
Несколько дополнительных замечаний.
Не будет считаться ошибкой выполнения задания в несколько действий.
1. Например, сначала создается первый дополнительный диапазон, равный по размерности исходному A1:B4 (например, H1:I4), в который выводятся значения, удовлетворяющие первому критерию (т.е. большие либо равные -5) с помощью формулы
=ЕСЛИ(A1>=-5;A1;»»)
(и аналогичных формул для остальных ячеек), затем — создается второй дополнительный диапазон той же размерности (например, K1:L4), в который, в свою очередь, выводятся значения предыдущего диапазона H1:I4, удовлетворяющие второму критерию (то есть меньшие либо равные 10) с помощью аналогичной предыдущей —
=ЕСЛИ(I1<=10;I1;»»)
и, наконец, в свободную ячейку вводится формула для суммирования значений второго вспомогательного диапазона:
=СУММ(K1:L4)
2. Еще один вариант — создание только одного вспомогательного диапазона, опять же по размерности аналогичного исходному A1:B4 — например, M1:N4, в который выводятся значения исходного диапазона, удовлетворяющие обоим критериям с помощью формулы:
=ЕСЛИ(И(A1>=-5;<=10);A1;»»)
в этом случае уже допустимо использование логической функции И(), а далее остается в свободную ячейку ввести суммирующую формулу —
=СУММ(M1:N4)
3. Наконец, еще один вариант решения данной задачи, уже упоминавшийся выше, состоит в использовании упомянутой функции СУММЕСЛИ(). Коль скоро вычисление суммы значений удовлетворяющих одному критерию функция позволяет, алгоритм решения состоит в том, чтобы снучала просуммировать все значения исходного диапазона, а затем вычесть из указанной суммы две суммы полученные с использованием функции СУММЕСЛИ() — первая — те значения, которые не удовлетворяют первому критерию (то есть меньшие -5), а вторая — те, которые не удовлетворяют второму (то есть большие 10):
=СУММ(A1:B4)-СУММЕСЛИ(A1:B4;»<-5″)-СУММЕСЛИ(A1:B4;»>10″)
Еще один вариант — просуммировать те знаения, которые удовлетворяют первому критерию (то есть большие и равные -5) и те, которые удовлетворяют второму критерию (то есть меньшие и равные 10), в результате полученная сумма будет отличаться от суммы всех элементов диапазона как раз на требуемую сумму удовлетворяющих обоим критериям значений (значения большие и равные -5 и меньшие и равные 10 присутствуют и в первом и во втором слагаемом), и остается лишь отнять сумму всех значений:
=СУММЕСЛИ(A1:B4;»>=-5″)-СУММЕСЛИ(A1:B4;»<=10″)-СУММ(A1:B4)
Несколько дополнительных вопросов на понимание вышесказанного. С помощью какой (каких) функций оптимально нахождение:
1. Суммы элементов диапазона, больших 5?
2. Суммы элементов диапазона, больших значения, указанного в ячейке С5?
3. Суммы элементов диапазона, кратных 3-м?
4. Суммы элементов диапазона, больших среднего значения его элементов?
5. Количества элементов диапазона, удовлетворяющих критериям 1-4?