1008-1

71

СЧЕТЕСЛИ (диапазон, критерий)

и имеет аргументы:

диапазон – одна или несколько ячеек, по которым требуется выполнить счет;

критерий – это число или текст, с которым сравнивается содержимое ячеек, или выражение (>32), которому значения ячеек должны удовлетворять.

Математическая функция СУММЕСЛИ суммирует значения диапазона ячеек, соответствующих указанным условиям. Функция записывается как

СУММЕСЛИ (диапазон, критерий, [диапазон суммирования])

и имеет следующие аргументы:

диапазон – диапазон ячеек, сравниваемый с условием;

критерий – это число, текст, выражение, ссылки на ячейку, которые определяют какие ячейки необходимо просуммировать;

диапазон суммирования – это необязательный аргумент, в котором задаются ячейки, по которым проводится суммирование, если они отличаются от ячеек, указанных в качестве диапазона. Если данный аргумент пропущен, то суммируются ячейки, указанные в аргументе диапазон.

Упражнение 4

1.Создайте таблицу, приведенную на рис. 49.

2.Рассчитайте сумму скидки (столбец D) для клиентов, которые приобрели товары на следующих условиях:

если сумма покупки больше 3000 (ячейка D2), то скидка составляет 10 % (ячейка С3);

если сумма покупки от 1500 (ячейка С2) до 3000 (ячейка D2), то предоставляется скидка 5 % (ячейка D3).

72

Рис. 49. Таблица к упражнению 4

3.Вычислите итоговые суммы покупок, скидок и оплаченных покупок (ячейки С11, D11, E11).

4.Для подсчета количества клиентов, которые получили скидку, выделите ячейку Е14 и на вкладке Формулы в секции Библиотека функций нажмите

Другие функции Статистические СЧЕТЕСЛИ. В появившемся окне за-

дайте аргументы функции: диапазон – С6:С10 критерий – «>=»&C2

и нажмите ОК.

Замечание. При задании критерия в функциях знаки операторов сравнения, числа, текст заключаются в кавычки, перед ссылкой на ячейки ставится знак &.

5. Для расчета суммы покупок клиентов, имеющих скидку, выделите ячейку Е15 и на вкладке Формулы в секции Библиотека функций нажмите Математические СУММЕСЛИ. В появившемся окне задайте аргументы функции:

73

диапазон – С6:С10 критерий – «>=»&C2

инажмите ОК.

6.В ячейках Е16 и Е17 соответственно рассчитайте количество покупателей, которые получили максимальную скидку, и сумму их покупок. Результат сравните с рис. 50.

Рис. 50. Результирующая таблица упражнения 5

Контрольные вопросы

1. Назначение и аргументы функций ЕСЛИ, СУММЕСЛИ и СЧЕТЕСЛИ.

74

ЛАБОРАТОРНАЯ РАБОТА № 5

Тема работы: Построение диаграмм в Microsoft Excel

Диаграммы – это графическое представление данных. Они используются для анализа и сравнения данных, представления их в наглядном виде. Диаграмма состоит из элементов: линий, столбиков, секторов, точек и т.п. Каждому элементу диаграммы соответствует число в таблице. Числа и элементы диаграммы связаны между собой, поэтому при изменении чисел автоматически изменяются изображения элементов диаграммы и наоборот.

В зависимости от места расположения и особенностей построения и редактирования диаграмм различают:

внедренные диаграммы, которые размещаются на том же рабочем листе, где расположены исходные данные;

полноэкранные диаграммы, которые размещаются на отдельном рабочем листе.

Упражнение 1

Для таблицы, представленной на рис. 51, постройте два вида диаграмм – внедренную на лист с исходными данными и расположенную на отдельном листе. Для этого выполните следующие действия:

1. Создайте новую рабочую книгу, в которой Лист1 переименуйте на Ус-

певаемость.

Рис. 51. Таблица успеваемости к упражнению 1

2. Заполните таблицу согласно рис. 51. В пустые ячейки для строки с названием Факультет введите формулы вычисления среднего балла по факультету. Например, для ячейки В6 столбца Информатика:

перейдите на вкладку Формулы ленты инструментов и в группе

Библиотека функций нажмите на кнопку Другие функции Статистические СРЗНАЧ;

75

введите в первую строку диалогового окна адреса первой и последней ячеек столбца с оценками В2:В5, используя для этого мышь, и нажмите

ОК.

Скопируйте формулу из ячейки В6 в ячейку С6 столбца Высшая мате-

матика.

3.Постройте внедренную диаграмму, выполнив следующие операции:

выделите диапазон данных А1:С6;

нажмите на кнопку на вкладке Вставка в группе Диаграммы. В появившемся окне Вставка диаграммы выберите тип диаграммы Гистограмма и вид диаграммы – номер 1;

щелкните по кнопке ОК.

в результате на рабочем листе будет создана внедренная диаграмма, примерно как на рис. 52.

Рис. 52. Диаграмма типа Гистограмма, полученная в упражнении 1

Форматирование диаграмм

Для форматирования диаграммы следует выделить диаграмму. Появится группа Работа с диаграммами с вкладками Конструктор, Макет и Форма, ко-

торые включают следующие виды операций:

изменение типа и формата диаграммы;

изменение исходных данных;

изменение размещения диаграммы;

настройка области диаграмм и области построения диаграммы; изменение формы представления данных на диаграмме (рядов данных

и их элементов);

76

изменение отображения осей диаграммы;

настройка сетки в области построения диаграммы; вставка новой легенды и ее настройка.

Упражнение 2

Отредактируйте построенную по данным рис. 51 диаграмму в соответствии со следующим заданием:

1. Скопируйте диаграмму в другое место листа. Для этого:

выделите диаграмму, щелкнув левой клавишей мыши в области диаграммы один раз. Появятся метки на контуре области диаграммы;

на вкладке Главная ленты инструментов в группе Буфер обмена нажмите кнопку Копировать;

поместите курсор в новое место на рабочем листе; нажмите кнопку Вставить.

2.Измените размещение диаграммы. Для этого на вкладке Конструктор

вгруппе Расположение нажмите кнопку Переместить диаграмму. В появив-

шемся диалоговом окне Перемещение диаграммы поставьте переключатель

Разместить диаграмму в положение на отдельном листе и нажмите ОК. Диа-

грамма будет размещена на новом листе Диаграмма1.

3.Добавьте в исходную таблицу новый столбец Философия с различными оценками.

4.Вставьте в диаграмму столбцы, отражающие успеваемость по философии. Для этого:

вызовите контекстное меню диаграммы и выберите команду Вы-

брать данные;

в поле Диапазон данных для диаграммы укажите А1:D6, включив информацию столбца Философия;

нажмите на кнопку Строка/Столбец, и данные по осям Х и У поменяются местами.

нажмите кнопку ОК.

5. Измените формат диаграммы, сделав ее объемной. Для этого: установите курсор мыши во внутренней незаполненной области

диаграммы и, щелкнув правой кнопкой, вызовите контекстное меню диаграммы;

77

выполните команду Изменить тип диаграммы и выберите в окне

Изменение типа диаграммы в группе Гистограмма объемную гистограмму;

нажмите кнопку ОК и убедитесь в изменении формата диаграммы. 6. Создайте заголовок диаграммы:

на вкладке Макет ленты инструментов в группе Подписи нажмите

щелкните правой кнопкой мыши в любом из 4-х углов диаграммы, и в появившемся меню выберите команду Формат области диаграммы;

в появившемся диалоговом окне задайте: Заливка: сплошная, голубой цвет

и нажмите Закрыть;

78

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

мат области построения;

в появившемся диалоговом окне задайте: Заливка: сплошная, желтый цвет

инажмите Закрыть.

10.Настройте порядок рядов данных:

на вкладке Конструктор в группе Данные нажмите на кнопку Вы-

брать данные;

в появившемся диалоговом окне в секции Элементы легенды (ряды) выберите ряд Гр. 111 и нажмите Вниз , чтобы ряд отображался после ряда Гр.

112;

выберите ряд Гр. 111 и нажмите кнопку Вверх , чтобы он опять стал первым;

нажмите ОК для закрытия окна; 11. Проведите форматирование рядов данных:

установите указатель мыши на ряде Гр. 111 и нажмите правую кнопку мыши. В контекстном меню выполните команду Формат ряда данных;

в появившемся диалоговом окне задайте: Заливка: сплошная, синий цвет

инажмите Закрыть;

12.Повторите установку цвета заливки для остальных рядов диаграммы, задав для ряда Гр. 112 – желтый; ряда Гр. 113 – зеленый; ряда Гр. 114 – белый; а для ряда Факультет – красный цвет заливки.

13.Проведите форматирование вертикальной оси диаграммы: на вкладке Макет в группе Оси щелкните на кнопку Оси и в раскрывающемся списке вы-

берите Основная вертикальная ось Дополнительные параметры основной вертикальной оси… В появившемся диалоговом окне установите:

Минимальное значение – 0 Максимальное значение – 5 Цена основных делений – 0,5

Цена промежуточных делений – 0,25 и нажмите Закрыть.

79

14.Проведите форматирование сетки, стен и основания: на вкладке Макет

вгруппе Оси щелкните на кнопку Сетка и, выбирая в раскрывающемся списке направление, задайте вдоль каждой оси основные линии сетки.

15.Сравните свой результат с рис. 53.

Рис. 53. Диаграмма после форматирования в упражнении 2

Упражнение 3

При описании характеристик факторов среды лесопаркового объекта, требуется построить график среднемесячных температур воздуха, относительной влажности, количества осадков.

В качестве примера выполните создание климатограммы для условий г. Воронежа (рис. 54). Для этого:

1. Создайте таблицу согласно рис. 54. В ячейки с названием Годовые введите формулы, вычисляющие среднегодовые значения температуры и относительной влажности, а для осадков – суммарное количество осадков в год.

Рис. 54. Таблица Климатические показатели к упражнению 4

80

2. Значения характеристик различаются на порядок, поэтому для отображения их в одной плоскости ХУ необходимо выполнить преобразования. Для этого в таблицу следует ввести дополнительные строки (10-13 на рис. 55), содержащие значения параметров, отображаемых на графике. Например, для первого месяца:

для температуры воздуха принят масштаб – в 1 см (одно деление на графике) – 20. Поэтому в ячейку В11 поместите формулу: =В5/2;

минимальная относительная влажность составляет 48 %. Поэтому целесообразно начало оси У сопоставить значение влажности в 40 %, при этом использовать масштаб 1:4. Тогда в ячейку В12 запишите формулу: = (В6-40)/4;

минимум осадков составляет 25 мм. Для удобства построения началу оси У должно соответствовать 20 мм, а масштаб быть равным 1:6. Таким образом, в ячейке В13 должна быть формула: =(В7-20)/6;

скопируйте формулы ячеек В11:В13 в блок ячеек C11:M13. Сравните результат с рис. 55.

Рис. 55. Подготовка данных для построения климатограммы

3.Постройте диаграмму на отдельном листе, оформив ее так, как показано на рис. 56. Для этого выделите диапазон ячеек А11:M13 и на вкладке Вставка ленты инструментов в группе Диаграммы выберите тип График и вид диаграммы – номер 1.

4.На построенной диаграмме следует изменить вид и цвет каждого графика, на принятые параметры для отображения климатограмм. График температуры должен строиться красной сплошной линией, график относительной влажности воздуха – пунктирной линией зеленого цвета, а количество осадков