Тема: Технология создания электронной таблицы, использование мастера диаграмм и выполнение расчетов в среде MS Excel

Продолжительность 4 часа

Вопросы (задачи), подлежащие исследованию.

1.1. освоение методов оптимизации ввода информации;

1.2. отработка редактирования содержимого ячеек;

1.3. изучение процедуры автоформатирования таблиц;

1.4. освоение форматирования ячеек;

1.5. освоение форматирования символов.

1.6. использование мастера диаграмм

1.7. приобретение навыков составления простых формул с употреблением относительных, абсолютных и смешанных адресов.

Краткие теоретические или справочно-информационные материалы.

2.1. В любую ячейку можно внести исходные данные - число, текст, формулу для расчета, дату, время, последовательные ряды данных и др. Ширину столбца и высоту строки можно изменять Для указания на конкретную ячейку используется адрес, который составляется из обозначения столбца и номера строки, на пересечении которых эта ячейка находится (например, A1, F8, QR45 и т.д.).

2.2. Каждая ячейка может иметь относительный или абсолютный адрес или ссылку. Ссылки, как правило, используются в формулах. Т.е. в формуле, которая расположена в некоторой ячейке, производится ссылка на другую ячейку, в которой записана переменная и которая этой формулой используется. Если в формуле указывается абсолютная ссылка (абсолютный адрес), то адрес ячейки не меняется при перемещении формулы. Формула как бы "привязана" к строго определенной ячейке, в которой хранится переменная величина. Таким образом, абсолютный адрес ячейки описывает ее точные координаты, для чего в координатах ячейки указывается знак доллара. Например, $A$1, $G$45 и т.п.

2.3. Термин диаграмма активна означает, что в углах и на серединах сторон поля диаграммы расположены маркеры, которые имеют вид маленьких черных квадратиков. Диаграмма становится активной, если нажать кнопку мыши в любом месте диаграммы (предполагается, что вы находитесь вне диаграммы, то есть курсор установлен в ячейке активного листа книги). Когда диаграмма активна, можно изменять размеры поля и перемещать ее по рабочему листу.

3. Рекомендации студентам по подготовке к лабораторной работе с указанием литературы.

При подготовке к выполнению лабораторной работы необходимо изучить следующие вопросы:

3.1..Ввод и копирование формул.

3.2. Ввод функций.

3.3. Редактирование содержимого ячеек.

3.4. Автоформатирование электронной таблицы

3.5. Мастер диаграмм.

3.6. Создание диаграммы.

3.7. Редактирование диаграммы

3.8. Источники

3.8.1. Симонович С.В. Информатика. Базовый курс. Учебник для ВУЗов. Изд-во «Питер». СПб 2005.

3.8.2. Иванов М.И, Уткин Ю.Г.. Информатика. Учебное пособие М.: Изд-во «Альтаир» МГАВТ, 2004.

3.8.3. Карабутов Н.Н. Создание интегрированных документов в Microsoft Office: введение в анализ данных и подготовку документов Учебное пособие. Из-во «Солон» 2005г.

Описание лабораторного оборудования.

4.1. Персональный компьютер – ПЭВМ.

4.2..Программное обеспечение: OC WindowsXP (9х – любой версии), пакет MS Office XP

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

5.1. Подготовьте электронную таблицу для расчета

5.2. Набейте расчетные формулы в ячейках

5.3. Вычисления в таблице

5.4. Автоформат таблицы

5.5. Построение внедренной диаграммы

5.6. Редактирование построенной диаграммы

5.7. Построение тренда для гистограммы

5.8. Построение графиков функций и поверхности.

Порядок проведения работы.

6.1. Подготовьте электронную таблицу для расчета стоимости бензина при проезде на автомобиле из Москвы в Баку, Ленинград, Ригу и Тбилиси. При подготовке таблицы выполните следующие действия:

6.2. Создайте шапку таблицы путем ввода данных в следующие адреса ячеек:

А1 А3 А4 А5 А8 В8 С8

Стоимость бензина Марка автомобиля Расход бензин на 1 км в литрах Цена 1 литра бензина в руб. Город Путь в км Стоимость проезда в руб.

6.3. Заполните таблицу исходными данными путем ввода значений в следующие адреса ячеек.

D3 D4 D5 A10 A11 A12 A13 B10 B11 B12 B13

ВАЗ-2106 0,08 Баку Ленинград Рига Тбилиси

6.4. Набейте расчетные формулы в ячейках С10, =B10*D$4*D$5, а в ячейки С11:С13 распространите ее, используя Маркер заполнителя и применяя абсолютные адреса.

6.5. Перейдите к Листу 2 и подготовьте такую же таблицу, используя основные методы оптимизации ввода формул.

6.6. Отредактируйте расчетные формулы в ячейках С10:С13, записав в формулах вместо адресов D$4 и D$5 имена этих ячеек. Для этого выполните следующие действия:

6.6.1. присвойте ячейке D4 имя «Расход», используя Поле имени;

6.6.2. присвойте ячейке D5 имя «Цена», используя меню Вставка;

6.6.3. очистите ячейки С10:С13;

6.6.4. в ячейку С10 введите новую формулу и, используя Маркер заполнения, распространите ее в ячейки С11:С13.

6.7. Вернитесь к Листу 1 и пересчитайте таблицу для автомобиля «Москвич» с расходом 0,09л. на 1 км пути и цене этого бензина 18 руб. за 1л.

6.8. Перейдите к Листу 2 и посчитайте стоимость бензина при поездке в Воронеж и Ростов, если расстояние соответственно 501 км и 1050 км.

6.9. Перейдите к Листу 3 и создайте электронную таблицу набора данных для учета выручки в тыс. рублей по 4 км округам города в летние месяцы 2000 г. Для формирования шапки таблицы введите соответствующие тексты в следующие адреса ячеек:

 

A1 A2 A6 A7 A8 A9 C5 D5 E5

Продажа мороженого по округам города Лето 2000 г. Центральный Западный Северный Южный Июнь Июль Август

 

Ячейки С6:С9 заполните значениями выручки согласно

 

	Июнь	Июль	Август
Центральный Западный Северный Южный

6.10. С помощью полученной таблицы вычислите следующие величины:

6.10.1. сумму выручки по городу за каждый месяц;

6.10.2. сумму выручки по каждому округу за все лето;

6.10.3. общую сумму выручки;

6.10.4. процент выручки по каждому округу относительно общей суммы.

6.11. В ячейки В10 и F5 запишите текст «Всего», в ячейку G5 – текст «В процентах».=F6/F$10

6.12. Произведите Автоформат таблицы с помощью шаблона Классический 3.

6.13. Погасите переключатель Сетка.

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

6.14.1. вызовите Мастер функций, щелкнув по его кнопке на панели инструментов;

6.14.2. выберете категорию функций Статистическая, имя функции – СРЗНАЧ, щелкните по кнопке <OK>;

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

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

 

Рис1.Гистограмма

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

6.15.1. нажмите кнопку Мастер диаграмм или выполните команду Вставка, Диаграмма.

6.15.2. Этап 1. Выбор типа и формата диаграммы: на вкладке Стандартные выберете тип диаграммы Гистограмма и вид диаграммы – номер 1;щелкните по кнопке <Далее>.

6.15.3. Этап 2. Выбор и указание диапазона данных для построения диаграммы:

на вкладке Диапазон данных установите переключатель Ряды в столбцах; выделите диапазон данных А2:С6; в том же диалоговом окне щелкните по вкладке Ряд; в окне Ряд выделена строка с названием Ряд1, установите курсор в строке Имя и щелкните в ячейке В1 с названием Информатика; в окне Ряд щелкните по названию Ряд 2, установите курсор в строке Имя и щелкните в ячейке С1 с названием Высшая математика; для создания подписей по оси Х щелкните в строке Подписи оси Х и выделите данные первого столбца таблицы, т.е. диапазон А2:А6; щелкните по кнопке <Далее>.

6.16. Этап 3. Задание параметров диаграммы:

на вкладке Заголовки введите названия в соответствующих строках:

Название диаграммы: Сведения об успеваемости Ось X: Учебные группы Ось Y: Средний бал

на вкладке Легенда поставьте флажок Добавить легенду и переключатель Справа; щелкните по кнопке <Далее>.

6.17. Этап 4. Размещение диаграммы:

установите переключатель Поместить диаграмму на имеющемся листе и выберите из списка лист Успеваемость; щелкните по кнопке <Готово>; в результате на рабочем листе будет создана внедренная диаграмма. Сравните результат с рис.

6.18. Внимание! Для изменения размера диаграммы установите курсор мыши в поле диаграммы и один раз щелкните левой кнопкой на контуре диаграммы. На контуре появятся выделенные черные метки (квадраты). Установите курсор мыши на эти метки. Курсор мыши изменит свое начертание на черную тонкую двухстороннюю стрелку ↔. Удерживая нажатой левую кнопку, протащите мышь для изменения размеров поля диаграммы.

6.19. Постройте диаграмму другого типа на отдельном листе. Для этого выполните действия, аналогичные описанным в п.2, но на четвертом шаге установите переключатель На отдельном листе.

6.20. Отредактируйте построенную по Диаграмму в соответствии с заданием.

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

6.22. Измените формат диаграммы, сделав ее объемной.

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

6.22.2. установите переключатель Ряды в в положение столбцах и нажмите клавишу <OK>; на вкладке Ряд в окне Подписи оси Х введите диапазон ячеек B1:D1;нажмите кнопку <OK>.

Рис2.Объёмная гистограмма

6.23. Постройте линейный тренд для гистограммы на Листе1. Для этого:

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

6.23.2. выполните команду Добавить линию тренда;

6.23.3. в диалоговом окне <<Линия тренда>> на вкладке Тип выберите окошко Линейная;на вкладке Параметры установите параметры:

Прогноз: вперед на 1 период Показывать уравнение на диаграмме: установите флажок Поместить на диаграмму величину достоверности аппроксимации: установите флажок

6.24. Постройте полиномиальный тренд для гистограммы, воспользовавшись технологией п.9.

Рис3.Получение тренда

6.25. Получить изображение графика функции с использованием мастера диаграмм MS Excel

6.25.1. В столбец А ввести значения аргумента, для этого

в А1 ввести значение – 2

в А2 ввести формулу =А1+0,5

скопировать формулу в А2 и используя маркер заполнителя произвести вставку формулы с А3: А10

В столбец В ввести функцию для аргументов в столбе А

6.25.2. в ячейке B1 записать формулу =COS(A1^2)*КОРЕНЬ(А1+5)/(LN(A1+4)*SIN((A1+4)/2))

используя маркер заполнения скопировать формулу с В1: В10

По значениям столба В произвести построение графика в MS Excel по образцу:

Рис4.Построение графика

6.26. Построить графики гиперболического параболоида, который описывается уравнением вида:z=(x/a)2 - (y/b)2и эллиптического параболоида, который описывается уравнением вида: z=(x/a)2 + (y/b)2 Выполните следующие действия:

6.26.1. В ячейку B2 введите число -5 и после ввода вернитесь в ячейку B2.

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

6.26.3. В окне диалога “Прогрессии” выберите режим по строкам и тип арифметическая, введите шаг 0,5 и предельное значение равное 5. После нажатия кнопки OK во 2 строке появится ряд значений от -5 до 5 с шагом 0,5.

6.26.4. Теперь введем формулы для вычисления значений функции. Для этого в ячейку B3 введем формулу следующего вида: =(B$2/$A$1)^2-($A3/$B$1)^2 Далее распространим эту формулу на всю строку, расположенную под строкой со значениями переменной x. После этого, не сбрасывая выделение, установите указатель мыши в точку в правом нижнем углу последней выделенной ячейки в 3 строке. Затем распространите выделенные в ячейках формулы на все строки вдоль заполненного значениями переменной y столбца A.

6.26.5. Построим трехмерную диаграмму типа Поверхность по области A2:V23. Удалим легенду с диаграммы и все надписи с осей.

6.26.6. Для построения графика эллиптического параболоида Область A2:V23 скопируйте на отдельный лист рабочей книги. На этом листе выделите область без меток строк и столбцов и в этой области знак “-” замените на знак “+” при помощи команды Правка-Заменить.

Рис5.Построение поверхности

6.27. Решить систему уравнений:

5х1 + 6х2 – х3 + х4 = 3;

4х1 – х2 – 5х3 + 2х4 = 2;

9х1 + 3х2 – 10х3 + х4 = 6;

10х1 + 2х2 + 3х3 + 2х4 = 1.

Решение будет заключаться в умножении обратной матрицы коэффициентов при неизвестных на матрицу свободных членов. Эти операции можно выполнить последовательно, т.е. сначала определить обратную матрицу коэффициентов при неизвестных при помощи функции МОБР, а затем полученную обратную матрицу умножить на матрицу свободных членов при помощи функции МУМНОЖ. Но можно выполнить и быстрее: сначала вызывается функция МУМНОЖ, в диалоговом окне которой вызывается встроенная функция у первого массива, где в сою очередь вызывается функция обращения и вводится матрица коэффициентов. Для второго массива диалогового окна функции МУМНОЖ вводится диапазон матрицы свободных членов. Ввод заканчивается комбинацией клавиш <Shift>+<Ctrl>+<Enter>. Например, если матрица коэффициентов записана в диапазоне А2:D5, а матрица свободных членов – в диапазоне G2:G5, то формула выглядит так:

{=МУМНОЖ(МОБРА(А2:D5);G2:G5)}

Решение системы уравнений приведено на рисунке.

Рис6.Решение линейных уравнений

6.28. Найти решение уравнения Х3-3*Х2+Х= -1

6.28.1. Занесите в ячейку А1 значение 0.

6.28.2. Занесите в ячейку В1 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку А1. Соответствующая формула может, например, иметь вид

=А1^3-3*A1^2+А1.

6.28.3. Дайте команду Сервис> Подбор параметра.

6.28.4. В поле Установить в ячейке укажите В1 в поле Значение задайте -1, в поле Изменяя значение ячейки укажите А1.

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

6.28.6. Повторите расчет, задавая в ячейке А1 другие начальные значения, например 0,5 или 2. Совпали ли результаты вычислений? Чем можно объяснить различия?

6.29. Используя функции ИЛИ и И, создадим форму, в которой автоматически будет выводится информация о студентах, которые не сдали зачет по праву или одному из иностранных языков и потому не могут быть допущены к зачёту по информатике.

6.29.1. Создать показанную на рисунке таблицу.

Рис7.Логические функции

6.29.2. В ячейку F3 введём формулу

=ЕСЛИ(И(B3="зачтено";ИЛИ(C3="зачтено";D3="зачтено";E3="зачтено"));"";"нет допуска")

6.29.3. Скопируем формулу в нижележащие ячейки.

Техника безопасности.

При выполнении лабораторной работы необходимо строго соблюдать меры безопасности, для чего:

7.1. Включать электропитание ПК только по команде преподавателя (с разрешения преподавателя);

7.2. Строго соблюдать последовательность включения и выключения ПК; ИБП.

7.3. Категорически запрещается:

7.3.1. производить вскрытие системных блоков;

7.3.2. отключение и подключение интерфейсных кабелей и кабелей питания как во время работы, так и при отключенном напряжении;

7.3.3. производить несанкционированные действия с элементами системы ввода (мышь, клавиатура);

7.3.4. Делать перестановки оборудования на рабочем месте

Исходные данные для работы.

8.1. Операционная система MS Windows, пакет MS Office XP, Visual Basic6.0.

8.2. конспект лекций;

8.3. указания преподавателя по подготовке и проведению лабораторной работе.

9. Методика анализа полученных результатов. Контрольные вопросы.

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

9.2. Как расположить заголовок таблицы по ее центру?

9.3. Для чего используется Маркер заполнения?

9.4. Какой кнопкой пользуются для вставки в ячейку функции суммы?

9.5. Как пользоваться готовым шаблоном для форматирования таблицы?

9.6. Как погасить сетку таблицы?

9.7. Как вызвать Мастер диаграмм?

9.8. Как указать диапазон данных по которым будет строиться диаграмма?

9.9. Какие кнопки служат для перехода между шагами Мастера диаграмм?

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

9.11. В каком пункте меню находится команда Объемный вид?

9.12. Самостоятельная работа

9.12.1. Построение графика производной и первообразной к заданной функции в MS-EХСЕL.

 

Рис8. Построение графика производной и первообразной

10. Порядок оформления отчета по лабораторной работе и его защиты.

10.1. Название работы, дата выполнения, исполнитель.

10.2. Цель работы.

10.3. Исходные данные.

10.4. Перечень применяемых приборов и материалов.

10.5. Ход и результаты работы.

10.6. Контрольное задание.

10.7. Выводы.

 

 

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