Цель: научиться:

а) использовать стандартные функции MS Excel для расчета среднего, максимального и минимального значений; б) строить диаграммы по заданным значениям; в) добавлять данные на построенную диаграмму; д) применять встроенные макеты и стили для оформления диаграмм;

освоить:

а) технологию использования функции ЕСЛИ; б) комбинированный способ записи функций И() и ЕСЛИ() для составление сложных условий оценки данных; е) основные приемы форматирования элементов диаграммы.

1. Задание.

Создать в личной папке рабочую книгу ЛР2.xlsx. Оформить рабочий лист по образцу рис. 3 в точном соответствии с указаниями. Построить диаграммы (см. рис. 7 и рис. 8). Выполнить задание для самостоятельной работы. Оформить отчет по лабораторной работе. Отчет должен содержать следующие элементы:

• номер лабораторной работы;
• тема лабораторной работы;
• цель;
• снимки экрана с выполненным заданием;
• ответы на контрольные вопросы;
• вывод.

1. Порядок выполнения задания.
2. Создание таблицы с исходными данными.

Указания.

1.1. Создать/открыть рабочую книгу ЛР2.xlsx

1.2. Ввести данные в диапазон A1:Q13 по образцу рис. 1.

Рис. 1 – Образец для выполнения задания.

 

 

 

2. Рассчитать среднее значение величин в диапазоне ячеек С11:N11 с помощью функции СРЗНАЧ.

Указания.

2.1. Активизировать ячейку C11.

2.2. Ввести в выделенную ячейку формулу следующим образом:

• с клавиатуры ввести знак =
• начать набирать с клавиатуры название функции СРЗНАЧ, пока не появится меню со списком доступных функций (см. рис. 2);
• с помощью клавиш управления курсором ¯ или ­ выбрать требуемую функцию и нажать клавишу <Tab>;
• протягиванием ЛКМ выделить диапазон, содержащий значения для расчета среднего значения (C4:C10);
• закончить ввод формулы нажатием клавиши <Enter>. В ячейке C11 отобразится среднее значение длины окна сканирования, рассчитанное при помощи стандартной функции MS Excel.

2.3. Скопировать формулу из ячейки C11 в ячейки диапазона D11:N11 любым известным вам способом (например, методом автозаполнения).

2.4. Сравнить полученные результаты расчетов с образцом (см. рис. 4).

3. Рассчитать максимальное значение величин в диапазоне ячеек С12:N12 с помощью функции МАКС().

Указания.

3.1. Максимальные значения величин рассчитать самостоятельно, по аналогии с п. 2.

4. Рассчитать минимальное значение величин в диапазоне ячеек С12:N12 с помощью функции МИН().

Указания.

4.1. Минимальные значения величин рассчитать самостоятельно, по аналогии с п. 2.

5. Рассчитать оценку разрешения экрана окна сканирования (ячейки O4:O10) с помощью функции ЕСЛИ().

Примечание. Для расчета оценки использовать следующий алгоритм:

• для каждого устройства разрешение оценить как «высокое», если его значение больше или равно среднему значению разрешения, в противном случае разрешение оценить как «низкое»;
• скорость сканирования оценивается как «быстро», если ее значение для заданного устройства больше или равно среднему значению скорости сканирования, в противном случае скорость сканирования оценивается как «медленно».

Указания.

5.1. Активизировать ячейку О4 и ввести в нее формулу следующим образом:

• ввести с клавиатуры знак =
• начать набирать с клавиатуры название функции ЕСЛИ, пока не появится меню со списком доступных функций;
• с помощью клавиш управления курсором ¯ или ­ выбрать требуемую функцию и нажать клавишу <Tab> (в активной ячейке автоматически закончится ввод названия функции и откроется круглая скобка, после которой будет размещен мигающий курсор – см. рис. 3);
• ввести аргументы функции (название активного аргумента будет выделено полужирным начертанием во всплывающей подсказке, см. рис. 3):
• закрыть скобку и закончить ввод формулы нажатием клавиши <Enter>.

Рис. 3 – Ввод функции ЕСЛИ.

5.2. Скопировать содержимое ячейки О4 в диапазон ячеек О5:О10 любым известным вам способом.

5.3. Сравнить полученные результаты расчетов с образцом (см. рис. 4). Обратить внимание на относительные ссылки в формуле в ячейках О5:О10 после копирования формулы из ячейки O4. Исправить ссылку на полуабсолютную в формуле из ячейки O4 и заново скопировать исправленную формулу из ячейки О4 в диапазон ячеек О5:О10. Повторно сравнить результаты расчетов с образцом (см. рис. 4).

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

Относительная ссылка – ссылка, изменяющаяся при копировании.

MS Excel меняет ссылки в формуле по следующему правилу:

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

Чтобы MS Excel не менял ссылки в формуле при копировании, необходимо использовать абсолютную адресацию.

Абсолютная ссылка – ссылка, не изменяющаяся при копировании формулы.

Полуабсолютная ссылка – ссылка, в которой не изменяется номер строки либо название столбца при копировании формулы.

 

Для задания абсолютной/полуабсолютной адресации нужно в ссылке поставить знак $, при этом возможны три варианта:

• полуабсолютная ссылка: знак $ размещается только перед названием столбца (пример: $A1), тогда при копировании не будет изменяться название столбца;
• полуабсолютная ссылка: знак $ размещается только перед номером строки (пример: A$1), тогда при копировании не будет изменяться номер строки;
• абсолютная ссылка: знак $ размещается и перед названием столбца, и перед номером строки (пример: $A$1), тогда при копировании не будет изменяться ни название столбца, ни номер строки.

Чтобы быстро изменить способ адресации, можно установить курсор в ссылку между названием столбца и номером строки и последовательно нажимать клавишу <F4>.

6. Рассчитать оценку скорости сканирования (ячейки P4:P10) с помощью функции ЕСЛИ().

Примечание. Для расчета оценки использовать следующий алгоритм:

• для каждого устройства скорость оценить как «быстро», если ее значение меньше или равно среднему значению скорости сканирования, в противном случае скорость оценить как «медленно».

Указания.

6.1. Оценку скорости сканирования рассчитать самостоятельно по аналогии с п. 5.

7. Рассчитать градуированную оценку сканера (ячейки Q4:Q10) с помощью функций ЕСЛИ() и И().

Примечание. Для расчета градуированной оценки использовать следующий алгоритм:

• устройству выставляется оценка «отлично», если его разрешение больше среднего значения разрешения и скорость сканирования меньше среднего значения скорости, в противном случае устройству выставляется оценка «удовлетворительно».

Указания.

7.1. Градуированную оценку сканера рассчитать самостоятельно по аналогии с п. 5. Для одновременного выполнения условий использовать функцию И().

Рис. 4 – Таблица с итоговыми расчетами.

8. Графически проанализировать динамику объема продаж сканеров STS-715, MSO 300 Sagem и BioLink U-Match 3.5 за период с 2005 по 2016 года.

Указания.

8.1. Выделить диапазоны ячеек, содержащие данные для осей абсцисс и ординат:

• методом протягивания ЛКМ выделить диапазон ячеек I3:N3 (значения, которые будут отложены по оси абсцисс ОХ);
• зажать клавишу <ctrl> и, удерживая ее, последовательно выделить диапазон ячеек I6:N6 и I8:N8 (значения, которые будут отложены по оси ординат ОY);

8.2. На ленте открыть вкладку «Вставка» и в группе «Диаграммы» выбрать тип «Точечная с гладкими кривыми и маркерами». Будет построена диаграмма как на рис. 5.

Рис. 5 – заготовка диаграммы, отражающей динамику продаж сканеров STS-715 и MSO 300 Sagem.

8.3. Добавить название диаграммы (см. рис. 6). Для этого:

• выделить диаграмму одиночным щелчком ЛКМ (на ленте появятся дополнительные вкладки по работе с диаграммами: «Конструктор», «Макет», «Формат»);
• перейти на вкладку «Макет» и в группе «Подписи» выбрать команду «Название диаграммы» (вариант размещения названия выбрать в соответствии с образцом рис. 6);
• в появившейся на диаграмме области ввести название (см. рис. 6).

8.4. Добавить подписи к осям абсцисс и ординат (см. рис. 6). Для этого:

• перейти на вкладку «Макет» и в группе «Подписи» выбрать команду «Название осей» (варианты размещения названий для осей выбрать в соответствии с образцом рис. 6);

8.5.  Изменить названия рядов, чтобы легенда стала информативной (см. рис. 6). Для этого:

• перейти на вкладку «Конструктор» и в группе «Данные» выполнить команду «Выбрать данные»;
• в появившемся д/о «Выбор источника данных» в области «Элементы легенды (ряды)» одиночным щелчком ЛКМ выбрать «Ряд 1» и щелкнуть по кнопке «Изменить»;
• в открывшемся д/о «Изменение ряда» щелкнуть по кнопке в поле «Имя ряда» (при этом д/о «Изменение ряда» свернется до размеров соответствующего поля) и выделить ячейку, содержащую название сканера STS-715 (ячейка B6);
• развернуть д/о «Изменение ряда» повторным щелчком ЛКМ по кнопке и нажать кнопку «ОК»;
• для второго ряда указать имя самостоятельно;
• завершить работу с д/о «Выбор источника данных» щелчком по кнопке «ОК».

Рис. 6 –диаграмма, отражающая динамику продаж сканеров STS-715 и MSO 300 Sagem.

8.6.  Добавить на диаграмму данные для сканера BioLink U-Match 3.5 (см рис. 7). Для этого:

• перейти на вкладку «Конструктор» и в группе «Данные» выполнить команду «Выбрать данные»;
• в появившемся д/о «Выбор источника данных» в области «Элементы легенды (ряды)» щелкнуть по кнопке «Добавить»;
• свернуть открывшееся д/о «Изменение ряда» по полю «Имя ряда» нажатием соответствующей кнопки , выделить ячейку, содержащую название сканера BioLink U-Match 3.5 (ячейка B4) и развернуть д/о «Изменение ряда» повторным щелчком по кнопке ;
• свернуть д/о «Изменение ряда» по полю «Значения Х» нажатием соответствующей кнопки , выделить диапазон ячеек, содержащих года (ячейки I3:N3) и развернуть д/о «Изменение ряда» повторным щелчком по кнопке ;
• свернуть д/о «Изменение ряда» по полю «Значения Y» нажатием соответствующей кнопки , выделить диапазон ячеек, содержащих объем продаж заданного сканера (ячейки I4:N4) и развернуть д/о «Изменение ряда» повторным щелчком по кнопке ;
• завершить работу с д/о «Изменение ряда» щелчком по кнопке «ОК»;
• завершить работу с д/о «Выбор источника данных» щелчком по кнопке «ОК».

Рис. 7 –диаграмма, отражающая динамику продаж сканеров STS-715, MSO 300 Sagem и BioLink U-Match 3.5.

9. Графически проанализировать продажи сканеров в 2008 (см. рис. 8).

Указания.

9.1. Создать заготовку диаграммы. Для этого:

• выделить диапазон ячеек, содержащий данные для построения диаграммы (ячейки K4:K10);
• на ленте открыть вкладку «Вставка» и выбрать вариант диаграммы «Круговая».

9.2. Добавить название диаграммы (см. п. 8.3) по образцу рис. 8.

9.3. Изменить подписи к горизонтальной оси, чтобы легенда стала информативной. Для этого:

• перейти на вкладку «Конструктор» и в группе «Данные» выполнить команду «Выбрать данные»;
• в появившемся д/о «Выбор источника данных» в области «Подписи горизонтальной оси (категории)» щелкнуть по кнопке «Изменить»;
• в открывшемся д/о «Подписи оси» в поле «Диапазон подписей оси:» указать ссылки на диапазон ячеек, содержащих названия сканеров (ячейки B4:B10);
• завершить работу с д/о «Подписи оси» щелчком по кнопке «ОК»;
• завершить работу с д/о «Выбор источника данных» щелчком по кнопке «ОК».

9.4. Выбрать макет диаграммы. Для этого:

• на ленте открыть вкладку «Конструктор» и в группе «Макеты диаграмм» выбрать вариант, на котором отображается название диаграммы, легенда и значения в процентах (Макет 6).

9.5. Выбрать стиль диаграммы. Для этого:

на ленте открыть вкладку «Конструктор» и в группе «Стили диаграмм» выбрать «Стиль 26»