Лабораторная работа №4. Технология обработки числовой информации. Ms excel. Относительная и абсолютная адресация. Условное форматирование

Цель занятия. Применение относительной и абсолютной адресаций для финансовых расчетов. Сортировка, условное форматирование и копирование созданных таблиц. Работа с листами электронной книги.

Задание 1. Создать таблицы ведомости начисления заработной платы за два месяца на разных листах электронной книги, произвести расчеты, форматирование, сортировку и защиту данных.

Создайте таблицу расчета заработной платы по образцу

1. Выделите ячейки для значений % Премии (D4) и % Удержания (F4) красным цветом.

2. Произведите расчеты во всех столбцах таблицы по формулам:

Премия = Оклад * % Премии, в ячейке D5 наберите формулу =$D$4 x С5 (ячейка D4 используется и в виде абсолютной адресации)

Всего начислено = Оклад + Премия.

Удержание. = Всего начислено * % Удержания.

К выдаче = Всего начислено - Удержания.

3. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Вставка/ Функции/ Категория — Статистические функции).

4. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя.

5. Скопируйте содержимое листа «Зарплата октябрь» на новый лист Перемещать и копировать, листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl|).

6. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение Премии на 32 %. Убедитесь, что программа произвела пересчет формул.

7. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» (Вставка/Столбец) и рассчитайте значение доплаты по формуле: Доплата =Оклад * % Доплаты. Значение доплаты прямите равным 5 %.

8. Измените формулу для расчета значений колонки «Всего начислено»:

Всего начислено = Оклад + Премия + Доплата

З адание 2.

Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10 000 — зеленым цветом шрифта; меньше 7000 красным; больше или равно 10 000 — синим цветом шрифта (Формат/Условное форматирование)

1. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию(меню Данные/Сортировка, сортировать по — Столбец В).

2. Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Вставка/Примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания.

Задание 3. Защитите лист «Зарплата ноябрь» от изменений (Сервис/Защита/ Защитить лист). Задайте пароль на лист, сделайте подтверждение пароля. Убедитесь что лист защищен и не возможно удаление данных. Снимите защиту листа (Сервис/Защита/ Снять Защиту листа).

Лабораторная работа №5. Технология обработки числовой информации. Ms excel.Графическое представление результатов. Анализ полученных результатов

Цель занятия: изучение технологии использования возможностей Microsoft Excel для статистических расчетов, графического представления данных и прогнозирования.

Задание 1. С помощью диаграммы (обычная гистограмма) отобразить данные о численности населения России (млн. чел.) за 1970—2005гг.

Исходные данные представлены на рис.1.

1. Откройте редактор электронных таблиц Microsoft Excel.

2. Создайте на листе 1 таблицу численности населения по образцу.

Для ввода значений лет создайте ряд чисел с интервалом в 7 лет (введите первые два значения даты — 1970 и 1977 г., выделите обе ячейки и протяните вправо за маркер автозаполнения до нужной конечной даты).

2. Постройте диаграмму по данным таблицы. Для этого выделите интервал ячеек с данными численности населения A3:G3 и выберите команду Вставка/Диаграмма. На первом шаге работы с Мастером диаграмм выберите тип диаграммы — гистограмма обычная; на втором шаге на вкладке Ряд в окошке Подписи оси Х укажите и нтервал ячеек с годами B2:G2. Далее введите название диаграммы и подписи осей; дальнейшие шаги построения диаграммы осуществляются по подсказкам мастера

Задание 2. Осуществить прогноз численности населения России на 2012 г. добавлением линии тренда к ряду данных графика

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

2. В открывшемся окне Линия тренда на вкладке Тип выберите вид тренда — полиномиальный 4-й степени.

3 . Для осуществления прогноза на вкладке Параметры введите название кривой «Линия тренда» и установите параметр прогноза — вперед на один период.

4. На диаграмме будут показаны линия тренда и прогноз на один период вперед.

5. Внесите численное значение прогноза на 2012 г. в исходную таблицу. Если вы все сделали правильно, то прогноз численности населения России по линии тренда составит 131 млн. чел.

Задание 3. Заполните Таблицу

Район города	Этажность домов
	1 эт.	2 эт.	3 эт.	4 эт.	5 эт.	7 эт.	9 эт.	12 эт.	>12
Центральный	245	511	30	0	1356	1657	2345	3983	57
Северный	123	345	1	4	478	341	2496	2034	14
Южный	12	2	0	0	0	5246	1657	3156	245
Западный	2637	2104	1670	0	0	0	23	435	0
Восточный	302	23	0	0	145	1436	3547	1263	143

Задание 4. Построить сравнительную характеристику этажности домов по районам.

• Выделить область данных A2:J7

• Вызовите мастер диаграмм

• Шаг 1. Выбрать тип и вид диаграммы (гистограмма плоская)

• Шаг 2. На вкладке Диапазон данных проверить диапазон данных и их расположение (в столбцах). На вкладке Ряд проверить области Имя, Значения и Подписи по оси X.

• Шаг 3. На вкладке Заголовки впишите название диаграммы и названия осей.

• Шаг 4. Выбрать размещение диаграммы на этом же рабочем листе.

Задание 5. Построить диаграмму, показывающую процентный состав домов разной этажности в Центральном районе (круговая диаграмма, в качестве подписей выберите Доля )

ЛАБОРАТОРНАЯ РАБОТА №6.

Технология разработки тестов. MS EXCEL.

1 этап

1. Запустите программу MS Excel.

2. Выполните команду Сервис – Макрос – Безопасность. В открывшемся диалоговом окне Безопасность во вкладке Уровень безопасности установите Средняя.

3. В ячейку A1 введите название теста Проверка знаний по математике.

4. В ячейку B3 введите запись Вариант 1.

5. В ячейку D3 введите запись ФИО, а в ячейку D4 – Класс.

2 этап

Программа Excel позволяет создавать тесты со свободным ответом (когда обучаемому не дается варианта ответа) и с выборочным ответом (когда обучаемому предлагаются варианты ответов, из которых он выбирает правильный).

6. Введите в ячейку E4 списки классов, которые будут проходить тестирование. Встаньте в ячейку Е4.

Выбирается меню Данные. В ниспадающем меню выбирается команда Проверка.
В диалоговом окне выбирается тип данных - Список
В окне Источник перечисляются варианты ответов через точку с запятой.

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

7. В строке 6 оформите заголовки столбцов теста.

8. В ячейки В7:В16 введите вопросы, а в ячейки С7:С16 введите ответы в виде списка с выборочными четырьмя ответами, среди которых один правильный.

9. Лист 1 переименуйте Тест.(ПКМ на ярлычке листа)

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

10. Выполните команду Сервис – Макрос – Начать запись. Дайте имя макросу Очистка. Выделите все поля с ответами и нажмите клавишу delete. Также удалите фамилию ученика и класс.

11. Выполните команду Сервис – Макрос – Остановить запись.

12. Выполните команду Вид – Панели инструментов – Формы.

13. Найдите инструмент Кнопка, активизируйте его (щелкните на нем) и нарисуйте кнопку на листе.

14. Назначьте ей макрос Очистка.

15. Переименуйте кнопку.

16. Оформите тест, используя Формат – Ячейки.

Введите любые ответы, а также заполните ФИО и Класс.

Сохраните тест.

3 этап

Для подведения итогов тестирования можно предусмотреть специальный лист, переименовав его в Результат, на котором будут подведены итоги ответов.

1. Переименуйте Лист2 в Результат.

2. В строки А2 и А3 введите записи ФИО и Класс соответственно.

3. Скопируйте с первого листа номера вопросов и сами вопросы в столбцы А6:А16 и В6:В16.

4. Введите остальные заголовки таблицы, согласно рисунку (Ваш ответ, Результат, Верный ответ).

5. Создадим первый макрос – Ваш ответ.

6. Перед созданием макросов на втором листе курсор на листе ответов устанавливайте в какую-нибудь пустую ячейку, где нет записей, например, для нашего примера F9.

7. Выполните команду Сервис – Макрос – Начать запись. Дайте имя макросу Ваш_ответ.

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

9. Установите курсор в ячейку В2, нажмите знак «=», перейдите на лист вопросов и щелкните мышью в ячейку Е4 (Петров Вася) и нажмите клавишу «Enter».

10. Аналогично введите класс.

11. Таким же образом в листе ответов введите в ячейку С7 ответ с листа вопросов.

12. Скопируйте остальные варианты ответов: установите курсор в ячейку С7 и подведите его в правый нижний угол этой ячейки. Когда курсор примет вид «+», протяните вниз до ячейки С16.

13. Остановите макрос.

14. Нарисуйте кнопку и назначьте ей макрос Ваш ответ.

15. Далее оформляем столбец Результат. Для этого используем логическую функцию «если».

16. Создайте второй макрос – Результат. Для этого, на листе ответов установите курсор в ячейку D7.

17. Выполните команду Вставка – Функция (или кнопка f_x рядом со строкой формул). Выберите в категории Логические функцию Если.

18. Заполните поля согласно Рисунку. Текстовые ответы необходимо заключать в кавычки.

19. Аналогичным образом заполните ячейки D8:D16. Причем верными ответами являются: D8=“равносторонний”; D9=“тупоугольный”; D10=“биссектриса”; D11=“катет”; D12=“медиана”; D13=“подобные”; D14=“высота”; D15=“равнобедренный”; D16=“разносторонний”.

20. Остановите макрос. Нарисуйте кнопку и назначьте ей макрос Результат.

21. Далее оформляем столбец Верный ответ.

22. Создайте третий макрос – назовите его Ответ1. Установите курсор в ячейку Е7. Введите в ячейки E7:E16 верные ответы к вопросам.

23. Остановите макрос.

24. Нарисуйте кнопку Верный ответ и назначьте ей макрос Ответ1.

25. Далее оформляем столбец Оценка. Для этого используем статистическую функцию «счетесли».

26. В строки В18 и В19 введите соответственно записи Количество верных ответов, Количество неверных ответов.

27. Создайте четвертый макрос – назовите его Оценка.

28. Установите курсор в ячейку С18. Выполните команду Вставка – Функция (или кнопка fx рядом со строкой формул). Выберите в категории Статистические функцию Счетесли.

29. Выделите на листе ответов диапазон D7:D16.

30. В строке критерий введите запись «верно» и нажмите кнопку ОК.

31. Аналогичным образом введите количество неверных ответов. Только в строке критерий введите запись «неверно».

32. Для выставления оценки используем функцию «если». Критерии оценивания:

    Кол-во верных ответов	Оценка
    9-10	5
    7-8	4
    5-6	3
    >4	2

33. Установите курсор в ячейку С21. Выполните команду Вставка – Функция (или кнопка fx рядом со строкой формул). Выберите в категории Логические функцию Если.

34. После открытия окна Аргументы функции щелкните мышью в ячейку С18. Ее адрес появится в строке Лог_выражение. Далее введите записи согласно Рисунку.

35. Установите курсор в строку Значение_если_ложь и нажмите на кнопку ЕСЛИ (рядом со строкой формул) для создания следующего вложения функции Если.

36. При открытии окна Аргументы функций нужно ввести следующие записи

37. Установите курсор в строку Значение_если_ложь и нажмите на кнопку ЕСЛИ (рядом со строкой формул) для создания следующего вложения функции Если.

38. При открытии окна Аргументы функций нужно ввести следующие записи

39. Остановите макрос.

40. Нарисуйте кнопку и назначьте ей макрос Оценка.

41. Создайте кнопку Очистка для удаления данных в столбцах Ваш ответ, Результат, Верный ответ (по аналогии с кнопкой Очистка на листе Тест)

42. Теперь нам нужно задать переход с листа Тест на лист Результаты. Перейдите на лист Тест.

43. На панели Рисование выберите раздел стрелки и подберите подходящую фигуру.

44. Нарисуйте стрелку.

45. Сделайте на ней надпись Далее, используя .

46. Выделите стрелку.

47. Зайдите в меню Вставка – Гиперссылка. Установите настройки как на рисунке.

48. Перейдите на лист Тест.

49. Встаньте в ячейку Е3. Зайдите Данные – Проверка.

50. Осталось защитить наши листы. Выделите на листе Тест ячейки Е3:Е4 и С7:С16 (используйте для этого клавишу CTRL).

51. Зайдите Формат – Ячейки, на закладке Защита уберите галочку Защищаемая ячейка.

52. Зайдите Сервис – Защита – Защитить лист. Введите любой пароль. Теперь без вашего разрешения изменения будут невозможны.

53. Сохраните файл.