Формула массива — формула, выполняющая несколько вычислений над одним или несколькими наборами значений, а затем возвращающая один или несколько результатов

обратите также внимание, как именно в формуле массива (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)
Лабораторная работа № 11

Построение графиков, поверхностей и диаграмм в Excel.

1.

ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ

Задание 1.Составить таблицу расчета доходов фирмы в абсолютном и процентном отношении и диаграмму роста доходов на основе данных о доходах фирмы.

Выполнение.

1. Составить таблицу расчета доходов фирмы: определить тип, размер и стиль шрифтов для заголовков строк и столбцов: Times New Roman Cyr, размер 12, стиль полужирный; для остального текста - Times New Roman Cyr, размер 10, стиль обычный;

2. Вычислить рост уровня доходов фирмы в процентном отношении в каждом месяце 1999 года по отношению к январю 1999 года (3-й столбец таблицы);

=(Ci-C$3)/C$3 где Сi – адрес ячейки i-го месяца графы Уровень доходов фирмы в 1999 году, С$3 – абсолютный адрес ячейки Уровень доходов фирмы за январь 1999 года;

3. Вычислить суммарный уровень доходов фирмы за 1999 и 1998 годы, результаты поместить в последней строке второго и третьего столбца соответственно;

4. Вычислить среднее значение роста уровня доходов в процентах, результат поместить в последней строке четвертого столбца;

5. Построить диаграмму зависимости уровня доходов фирмы за 1999 и 1998 годы по месяцам в виде гистограммы;

6. Построить диаграмму зависимости уровня доходов фирмы в процентном отношении в виде линейного графика;

7. Построить совмещенную диаграмму (тип нестандартная/график|гистограмма 2) по данным полученной таблицы (второй, третий и четвертый столбцы);

8. Рассмотреть другие типы диаграмм, освоить редактирование элементов диаграмм.

Задание 2. Составить круговую диаграмму с отображением среднего балла по предметам на основании таблицы "Итоги экзаменационной сессии" Лабораторной работы №3_3.