Пояснения к лабораторной работе №3_2 (продолжение)

Уважаемые коллеги, в связи с многочисленными ошибками при выполнении возникла необходимость подробно разобрать еще одно из заданий ЛР 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?