Лабораторная работа 1 Основы работы в Excel Цель работы научить студента открывать существующие книги, сохранять книги на новом месте и под новыми именами, работать с листами книги,
 Скачать 1.3 Mb.
|
|
3 Содержание Лабораторная работа № 1 Основы работы в Excel ................................................... Лабораторная работа № 2 Ввод формул. Использование относительных, абсолютных, смешанных адресов и имен ячеек ........................................................ Лабораторная работа №3 Вычисления в Excel. Использование мастера функций Лабораторная работа № 4 Форматирование электронных таблиц Образец контрольной работы по адресации и форматированию Лабораторная работа № 5 Редактирование электронных таблиц Лабораторная работа № 6 Использование функций Excel Лабораторная работа № 7 Реализация разветвляющихся алгоритмов Лабораторная работа № 8 Условное форматирование Образец контрольной работы по разветвляющимся процессами условному форматированию Лабораторная работа № 9 Графическое представление данных с использованием диаграмм Лабораторная работа № 10 Аппроксимация таблицы данных с использованием линии тренда Образец контрольной работы по графикам функций Лабораторная № 11 Подбор параметра. Решение нелинейных уравнений Лабораторная работа № 12 Поиск решения. Решение систем нелинейных уравнений с использованием поиска решения Лабораторная работа № 13 Решение систем линейных алгебраических уравнений Образец контрольной работы по подбору параметра, поиску решения и оптимизации Литература Приложение А Содержимое файла БЮДЖЕТ Приложение Б Содержимое файла TEPLO.XLS Приложение В Содержимое файла ОСАДКИ .................................................68 4 Лабораторная работа № 1 Основы работы в Excel Цель работы научить студента открывать существующие книги, сохранять книги на новом месте и под новыми именами, работать с листами книги, вводить простейшие выражения Оборудование, технические средства, инструмент персональный компьютер, Microsoft Excel Порядок выполнения работы Задание 1 Работа с окном программы Excel 1.1. Через главное меню запустить Excel из группы MS Office. 1.2. Ознакомиться со структурой окна программы. a) Какая информация содержится в заголовке программы b) Сколько панелей инструментов выведено на экран Используя всплывающие подсказки, ознакомиться с новыми кнопками на этих панелях. c) Рассмотреть рабочую область окна. Найти названия столбцов, номера строк, ярлычки с названиями листов рабочей книги, полосы прокрутки. 1.3. Свернуть окно программы в пиктограмму на панели задач. 1.4. Развернуть окно программы. 1.5. Изменить размеры окна так, чтобы оно занимало левую половину экрана. 1.6. Распахнуть окно навесь экран. 1.7. Указывая курсором на панель инструментов, вызвать контекстное меню. Какие варианты меню появятся Повторить вызов контекстного меню, установив указатель мыши a) на ячейку, b) на ярлычок листа. 1.8. Отключить панели инструментов Стандартная и Форматирование с использованием контекстного меню. 1.9. Включить панели инструментов Стандартная и Форматирование с использованием меню. 1.10. Расположить панель инструментов Стандартная полевому краю экрана, а панель инструментов Форматирование по правому краю экрана. 1.11. Вернуть панели в верхнюю часть окна. 1.12. Выполняя команду Вид-Строка формул, проследить какой элемент исчезнет с экрана. Вновь выполнить команду, для вывода строки формул на экран. 1.13. Посмотреть назначение элементов строки формул, используя всплывающие подсказки. 1.14. Щелчком мыши выделить любую ячейку таблицы. Как изменилось содержание строки формул. Задание 2 Работа с окном рабочей книги 2.1. Свернуть окно рабочей книги. Где появится свернутое окно 2.2. Восстановить размеры окна рабочей книги. 5 2.3. Набрать в ячейке А текст Лист 1. 2.4. Перейти на лист 2, щелкнув по ярлычку Лист 2 и набрать в ячейке А текст Лист 2. 2.5. Перейти на лист 3 и набрать в ячейке А текст Лист 3. 2.6. Переключаться между листами, нажимая клавиши Ctrl+PageUp, Ctrl+PageDown. 2.7. Используя контекстное меню для ярлычка листа a) переименовать лист 1 в Задание b) удалить лист 2. 2.8. Вставить новый лист, выполнив команду Вставка-Лист. С каким именем, ив каком месте появился ярлычок с новым листом 2.9. Поменять местами лист 3 илист, используя контекстное меню a) вызвать контекстное меню, находясь на листе 3; b) в контекстном меню выбрать команду Переместить/скопировать, c) в диалоговом окне в списке Перед листом выбрать значение Лист 4», нажать кнопку OK 2.10. Поместить лист "Задание" после листа 4, перемещая ярлычок листа. Для этого установить указатель мыши на ярлык "Задание и удерживая нажатой левую кнопку мыши, переместить указатель после ярлычка "Лист 4". 2.11. Используя кнопку на стандартной панели инструментов, создать новые рабочие книги Книга 2, Книга 3, Книга 4. Как посмотреть название книги, с которой идет работа в текущий момент 2.12. Используя меню Окно a) просмотреть все способы расположения окон на экране, используя команду Расположить, b) переключаться последовательно в документы Книга 2, Книга 1, 2.13. Переключиться в документ Книга 4, используя клавиши Ctrl+F6. 2.14. Закрыть без сохранения документы Книга 2, Книга 3, Книга 4. Задание 3 Создание таблицы Создать электронную таблицу, поместив в нее данные о студентах своей подгруппы. 3.1. Переименовать лист 1 в Студенты. 3.2. Набрать заголовки (в столбцы A-G, строки 1-2). Как выравниваются текстовые данные в ячейке Почему некоторые данные не полностью отображаются на рабочем листе A B C D E F G 1 №пп Фамилия Дата Alt+Enter рождения Оценки в сессию Средний Alt+Enter балл 2 Математика Информатика История 3 3.3. Получить справку об изменении ширины столбца, выполнив действия a) в поле Введите вопрос ввести текст "ширина столбца, 6 b) в области задач Поиск выбрать раздел "Изменение ширины столбца и высоты строки, затем выбрать ссылку "Одного столбца" . 3.4. Переключиться на окно Excel (не закрывая справку) и изменить ширину столбцов D, E, F, используя мышь. Закрыть справку. 3.5. Ввести в ячейки А3:А5 номера от 1 до 3, используя заполнение числовой прогрессией с шагом 1. 3.6. Ввести в столбец B, начиная с третьей строки (ячейки В, В, В, фамилии трех студентов (без инициалов) своей подгруппы. 3.7. Ввести даты рождения (день, месяц, год) в столбец B. 3.8. Ввести в столбцы D, E, F оценки. Задание 4 Сохранение файла 4.1. Выполнить команду меню Файл - Сохранить как. Посмотреть какое имя "по умолчанию" получает рабочая книга, какой тип файла соответствует программе Excel. 4.2. Сохранить таблицу в папке своей группы под именем фамилия Закрыть Excel. 4.3. Запустить "Проводник. Найти сохраненный файл. 4.4. Открыть свой файл в Проводнике. Какие действия при этом выполняются системой Задание 5 Редактирование таблицы 5.1. В ячейку G3 занести формулу =(D3+E3+F3)/3 для вычисления среднего балла. 5.2. Скопировать формулу в ячейки G4, G5, используя автозаполнение: установить курсор на маркер автозаполнения (правый нижний угол) ячейки G3 и протащить курсор по указанным ячейкам. 5.3. Выполнить редактирование столбца B, добавив инициалы к фамилии различными способами a) выполняя двойной щелчок в редактируемой ячейке b) используя строку формул. Задание 6 Простейшие вычисления Вычислить размер возвращаемой суммы краткосрочной ссуды по формуле 365 1 D 2 D ) P 1 ( S , где S - первоначальный размер ссуды, D1 - дата выдачи, D2 – дата возврата ссуды, P - процентная ставка. Вычисления выполнить для трех вариантов исходных данных Вариант 1 Вариант 2 Вариант 3 Ссуда S, руб. 200 200 200 Дата выдачи D1 01.01.2005 01.09.2005 01.01.2005 Дата возврата D2 01.05.2006 01.05.2006 01.05.2006 Процентная ставка, % 10% 10% 5% 6.1. Перейти на лист 2. Переименовать его в Ссуда. 6.2. Создать заголовок таблицы, разместить его впервой строке в столбцах А-Е. 7 A B C D E 1 Ссуда, руб. Выдать Вернуть Ставка, % Сумма, руб. 2 6.3. Занести в столбцы таблицы Аи строки 2-4 заданные исходные данные. 6.4. В ячейку Е занести формулу = A2*(1+D2)^((C2-B2)/365). Замечание результат будет выдан в виде даты. Для отображения результата в виде числа выполнить команду меню Правка – Очистить – Форматы. 6.5. Используя автозаполнение, скопировать формулу Ев ячейки Е и Е. В результате получится таблица A B C D E 1 Ссуда, руб. Выдать Вернуть Ставка, % Сумма, руб 2 200 01.01.2005 01.05.2006 10% 227,00281 3 200 01.09.2006 02.05.2006 10% 193,729 4 100 01.01.2005 01.05.2006 5% 106,69784 6.6. Сохранить файл под тем же именем. Задание 7 Создание таблицы функции, расположенной по строкам Построить таблицу функции f(x)=x 3 -10x 2 -200 на интервале x [-3, 18] с шагом 0,6. 7.1. Перейти на лист 3. Переименовать его в Таблица. 7.2. Занести в ячейки А, А заголовки строк х, f(x). 7.3. В первую строку занести значениях, используя заполнение арифметической прогрессией a) в ячейку В занести первое значение –3, b) вызвать диалоговое окно Прогрессия, в котором задать расположение – по строкам, тип – арифметическая шаг - 0,6, конечное значение – 18. 7.4. Во вторую строку занести функцию, используя автозаполнение: a) в ячейку В занести формулу =B1^3-10*B1^2-200, b) занести формулу в остальные ячейки, используя маркер автозаполнения. Задание 8 Форматирование таблиц 8.1. Перейти на лист Студенты. 8.2. Выделить мышкой ячейки D1, Е, F1 и объединить их, используя кнопку на панели Форматирование. 8.3. Аналогично объединить ячейки Аи А В и В C1 и C2, G1 и G2. 8.4. Выделить мышкой блок ячеек Аи отформатировать следующим образом AI AJ AK 8 a) выполнить центрирование текста по горизонтали и по вертикали команда меню Формат – Ячейки - вкладка Выравнивание, b) выделить полужирным, используя соответствующую кнопку на панели Форматирование какую. 8.5. Выделить всю созданную таблицу и нажать кнопку . В результате получается таблица 8.6. Перейти на лист Ссуда. 8.7. Отцентрировать и выделить полужирным заголовок (ячейки А. 8.8. Изменить при необходимости ширину столбцов. В результате получится таблица 8.9. Перейти на лист Таблица. 8.10. Выделить полужирным шрифтом и расположить по центру заголовки строк. 8.11. Изменить ширину первого столбца. 8.12. Заключить таблицу в рамку. В результате получится таблица 8.13. Сохранить файл под тем же именем. Контрольные вопросы 1. Способы запуска Excel. Структура окна Excel. 2. Назначение кнопок Свернуть, Восстановить, Закрыть, Распахнуть на все окно. Отличие кнопок Свернуть и Закрыть. 3. Работа с элементами меню способы доступа к элементам главного меню и подменю. Виды заголовков меню. Выход изменю без выполнения команды. 4. Назначение панелей инструментов. Размещение панелей на экране. Включение/отключение панелей. Какие панели рекомендуется отображать на экране. 5. Назначение строки ввода формул. Ее составные части. 6. Что такое книга в Excel? Из чего состоит книга Как переключаться между книгами Структура окна рабочей книги. AJ AK AK 9 7. Ярлычки листов и работа сними переключение между листами, добавление листа, удаление листа, переименование листа, перемещение/копирование листа. Управление видимостью ярлычков листов. 8. Ввод в ячейку текста. Что является текстовой константой, какие символы можно использовать при ее наборе К какой границе ячейки прижимается текст 9. Ввод в ячейку числа. Виды числовых констант. Какие символы можно использовать при вводе числа К какой границе ячейки прижимается число Как число преобразовать в текст 10. Ввод в ячейку даты и времени. К какому типу данных относятся эти константы 11. Что происходит, если по ширине столбца не входят а) текстовые данные б) числа, даты 12. Ввод в ячейку формулы. C какого символа начинается формула Что отображается после ввода формулы в ячейке в строке формул Лабораторная работа № 2 Ввод формул. Использование относительных, абсолютных, смешанных адресов и имен ячеек Цель работы научить студента применять различные способы адресации, использовать имена в формулах. Оборудование, технические средства, инструмент персональный компьютер, Microsoft Excel Порядок выполнения работы Задание 1 Создание счета. Использование относительных адресов 1.1. Переименовать лист 1 в Счет за ремонт 1.2. Создать документ "Счет за ремонт телевизора a) дата вводится как текущая дата, не подлежащая изменению при повторном открытии документа, A B C 1 Счет за ремонт телевизора 2 Дата: 14.02.2002 3 Наименование работ Стоимость работ Замена кинескопа Ремонт антенны 30 6 Итого НДС (20%) 46 8 Спецналог (К оплате) исходными данными являются стоимости двух работ, c) строка "Итого" является суммой этих стоимостей, d) НДС вычисляется как 20% от "Итого, а спецналог – как 1.5% от "Итого, e) строка "К оплате" – сумма трех предыдущих строк. 1.3. Сохранить документ в папке своей группы как файл с именем фамилия через меню Файл – Сохранить как. 10 Задание 2 Создать счет на оплату. Использование имени ячейки 2.1. Переименовать лист 2 в Счет на оплату. 2.2. Создать документ "Счет на оплату оборудования, выполняя действия a) справа от ячейки "от" занести текущую дату, как изменяемую при повторном открытии документа, b) справа от ячейки "Курс" - курс доллара в рублях, c) исходными данными является цена в долларах) ячейке, где находится курс доллара, дать имя Курс_доллара, e) в ячейку D6 ввести формулу для вычисления цены в рублях, как произведения цены в долларах на курс доллара, f) скопировать формулу в остальные ячейки, используя автозаполнение, g) в строке "Итого" выполняется суммирование по двум столбцам с использованием автосуммирования ( ). 2.3. Сохранить документ под тем же именем. Задание 3 Расчет скидки товаров. Использование абсолютного адреса 3.1. Переименовать лист 3 в Распродажа. 3.2. Создать документ о распродаже телевизоров, выполняя действия a) справа от ячейки "Скидка" занести скидку как число в процентном формате, b) заполнить столбец "Старая цена" исходными данными, c) столбец "Новая цена" формируется с учетом скидки и старой цены, d) в ячейку С правильно записать формулу. Скопировать эту формулу в остальные ячейки. 3.3. Сохранить документ под тем же именем. 11 A B C 1 Распродажа телевизоров 2 Скидка 10% 3 Марка Старая цена Новая цена Panasonic 8500 7650 5 Lg 6750 6075 6 Funai 4500 4050 7 Samsung 6900 6210 8 Витязь Задание 4 Создание плоской таблицы. Использование смешанных адресов 4.1. Вставить новый лист. 4.2. Переименовать лист 4 в Гипотенуза. 4.3. Создать таблицу для вычисления гипотенузы прямоугольного треугольника, заданными длинами своих катетов a) в левом столбце задать длины катета а, а впервой строке - длины катетов b, b) в ячейке A2 записать формулу вычисления гипотенузы, используя функцию КОРЕНЬ и правильно устанавливая адресацию, c) скопировать формулу вниз по столбцу. d) выделить получившийся столбец и скопировать вовсе остальные столбцы. 1 2 3 4 5 6 7 8 9 10 1 1,4142 2,2361 3,1623 4,1231 5,099 6,0828 7,0711 8,0623 9,0554 10,05 2 2,2361 2,8284 3,6056 4,4721 5,3852 6,3246 7,2801 8,2462 9,2195 10,198 3 3,1623 3,6056 4,2426 5 5,831 6,7082 7,6158 8,544 9,4868 10,44 4 4,1231 4,4721 5 5,6569 6,4031 7,2111 8,0623 8,9443 9,8489 10,77 5 5,099 5,3852 5,831 6,4031 7,0711 7,8102 8,6023 9,434 10,296 11,18 6 6,0828 6,3246 6,7082 7,2111 7,8102 8,4853 9,2195 10 10,817 11,662 7 7,0711 7,2801 7,6158 8,0623 8,6023 9,2195 9,8995 10,63 11,402 12,207 8 8,0623 8,2462 8,544 8,9443 9,434 10 10,63 11,314 12,042 12,806 9 9,0554 9,2195 9,4868 9,8489 10,296 10,817 11,402 12,042 12,728 13,454 10 10,05 10,198 10,44 10,77 11,18 11,662 12,207 12,806 13,454 Задание 5 Расчет среднего балла 5.1. Вставить новый лист. 5.2. Переименовать лист 5 в Баллы. 5.3. Создать таблицу для расчета среднего балла студента с учетом весов контрольных точек a) исходными данными являются веса контрольных точек и оценки студентов, b) столбец "Средний балл" вычисляется по правилу каждая оценка студента умножается навес контрольной точки, а затем эти произведения 12 суммируются, например, 45 0,2 + 34 0,3 + 40 0,1 + 47 0,1 + 39 0,3. Для этого используется функция СУММПРОИЗВ диапазон весов диапазон оценок. Вычислить средний балл для студента Иванова, правильно используя адреса ячеек, а затем скопировать формулу в остальные ячейки столбца A B C D E F G 1 Баллы студентов за контрольные точки по информатике 2 Веса контрольных точек 0,3 0,1 0,1 0,3 3 ФИО Номера контрольных точек Средний балл 2 3 4 5 5 Иванов 34 40 47 39 39,60 6 Петров 40 43 44 36 40,70 7 Сидоров 40 36 47 40 40,10 8 Сергеев 44 39 48 42 44,50 5.4. Сохранить документ под тем же именем. Задание 6 Создать таблицу выплаты займов 6.1. Добавить Лист 6, переименовать его в Займы 6.2. Создать таблицу выплаты займов, выполняя действия a) заполнить шапку таблицы, b) исходными данными являются процентная ставка, величина займа, количество периодов, ячейке D2 присвоить имя Период c) в ячейку |