технология электронных таблиц. Технология электронных таблиц контрольная работа. Табличный процессор Microsoft Office Excel
 Скачать 1.88 Mb.
|
|
Лабораторная работа 5 ФОРМУЛЫ EXCEL ДЛЯ ОБРАБОТКИ МАССИВОВ ДАННЫХ Цели работы: 1. Освоить функции табличного процессора для обработки матриц и решения систем линейных алгебраических уравнений. 2. Изучить процедуру применения табличных формул для обработки больших массивов данных в Excel. При обработке матриц необходимо помнить два основныхправила: Перед вводом формулы на рабочем листе выделяется область, размер которой соответствует размерности получаемой при расчетах матрицы. Ввод матричной формулы завершается нажатием комбинации клавиш Ctrl + Shift + Enter, а не просто Enter, как при обычных вычислениях. Задание 1 Рассчитать требуемые характеристики квадратной матрицы и определить решение системы линейных уравнений. Порядок выполнения работы 1. Дать рабочему листу название «Матрицы». 2. Задать матрицы A и B. 3. Вычислить определитель квадратной матрицы (числовая характеристика) с помощью функции МОПРЕД категории Математические.  4. Вычислить обратную матрицу для заданной с помощью функции МОБР категории Математические (заметим, что матричное произведение исходной матрицы и ее обратной матрицы дает единичную матрицу).  5. Транспонировать матрицу (поменять местами строки и столбцы) с помощью функции ТРАНСП категории Ссылки и массивы. (после транспонирования вектор-столбец преобразуется в строку, а вектор-строка – в столбец). 6. Выполнить операции сложения, вычитания, умножения и деления матрицы и числа посредством арифметических операторов: +, -, *, /.  7. Вычислить матричное произведение двух матриц с помощью функции МУМНОЖ категории Математические (число столбцов первой матрицы равно числу строк второй матрицы). В результате этой операции получается матрица, число строк которой равно числу строк первой умножаемой матрицы, а число столбцов – числу столбцов второй матрицы.  9. Решить систему линейных алгебраических уравнений (СЛАУ). В алгебраической форме СЛАУ порядка n записывают в виде  Или в матричной форме: АХ = В, где А – матрица коэффициентов; В – вектор-столбец свободных членов; Х – вектор-столбец неизвестных:  Решение СЛАУ в матричном виде находят по формуле Х = А–1В, где А–1 – матрица, обратная А. На рабочем листе Excel записаны матрица коэффициентов А и вектор-столбец свободных членов В. Для получения решения выделить ячейки, соответствующие вектору-столбцу из n элементов для неизвестных и записать матричную формулу решения системы.  Задание 2 Создать таблицу расчета заработной платы, используя табличные формулы Excel для расчета процентных отчислений и сумм к выдаче. Порядок выполнения работы 1. Дать рабочему листу название «Ведомость». 2. Создать таблицу ведомости по зарплате на лист Excel, отсортировать по алфавиту. 3. Рассчитать итоговую сумму к выдаче (матричная формула). 4. В дополнительном столбце восстановить первоначальные величины окладов без вычетов налогов и взносов. 5. Удалить первую строку ведомости (сотрудник Абрамов С.Т.). 6. Добавить в таблицу ведомости нового работника – Юшкова А.Ф., размер оклада которого составляет 13570 руб. и рассчитать для него значения по всем столбцам ведомости. 7. Выделить столбец D, нажать клавишу F2 для редактирования формулы, вносим формулу расчета 12% от оклада =C2*0,12, подтвердить выполнение формулы клавишами Ctrl+Shift+Enter. 8. Аналогичные действия провести для столбов Е и F с указанием формулы =C2*0,01. 9. Для подсчета количества выданных средств ввести формулу =C2-D2-E2-F2 по аналогии с предыдущими пунктами. 10. Добавить новый столбец и рассчитать в нем восстановленный оклад сотрудников по формуле =G2+F2+E2+D2. 11. При удалении и добавлении строки происходит автоматический пересчет значений, а также заполнение вновь созданных ячеек.  Примечание. Табличные формулы редактируются не как обычные формулы рабочего листа Excel. Ниже приведены алгоритмы редактирования табличных формул. Редактирование формулы 1. Выделить блок с формулой. 2. Нажать клавишу F2. 3. Внести изменения в формулу. 4. Нажать клавиши Ctrl + Shift + Enter. Изменение размеров блока (удаление/вставка строк) 1. Выделить блок с табличной формулой. 2. Нажать клавишу F2. 3. В начало формулы добавить апостроф, формула превращается в текст. 4. Вводим текст во все ячейки с помощью клавиш Ctrl + Enter. Табличная формула прекратила существование. 5. Очистить строку таблицы. 6. Выделить блок с табличной формулой. 7. Нажать клавишу F2. 8. Удалить апостроф. 9. Нажать клавиши Ctrl + Shift + Enter. Лабораторная работа 6. ПРОГНОЗ ЗНАЧЕНИЙ ЧИСЛОВЫХ ВЕЛИЧИН С ПОМОЩЬЮ СТАТИСТИЧЕСКИХ ФУНКЦИЙ EXCEL Цели работы: 1. Освоить статистические функции Excel для прогнозирования значений числовых последовательностей. 2. Научиться использовать функции Excel для обработки экспериментальных данных при помощи трендов. Задание 1 На основании статистических данных о численности населения России на период с 1993 по 1999 годы сделать прогноз на 2001 и 2003 гг. Порядок выполнения работы 1. Дать рабочему листу название «Прогноз». 2. Заполнить ячейки рабочего листа Excel следующими статистическими данными, выделив их цветом. 3. Сделать предположение о численности населения России в 2001 году с помощью функции ПРЕДСКАЗ, которая позволяет вычислить теоретическое значение зависимой переменной (в данном случае это численность населения) в фиксированной точке аргумента (т.е. для определенного года). Для этого в ячейку F1 ввести дату – 2001, а в ячейку F2 формулу = ПРЕДСКАЗ(F1;B2:E2;B1:E1) 4. Аналогичным способом предсказать численность российских граждан в 2003 году, но уже учитывая рассчитанное ранее значение в 2001 г.  5. Используя условное форматирование, выделить столбцы с минимальными и максимальными показателями, построить график.  Задание 2 С помощью функций ТЕНДЕНЦИЯ и РОСТ предсказать изменение численности населения на периоды с 2001 по 2015 г. и смоделировать на период с 1985 по 1993 г. Порядок выполнения работы 1. Дать рабочему листу название «Тенденция». 2. Копировать часть таблицы «Прогноз». 3. Спрогнозировать дальнейшую динамику изменения численности на период с 2003 по 2013 г. при помощи функции ТЕНДЕНЦИЯ. Эта функция позволяет предсказать значения зависимой переменной для целого диапазона значений независимой переменной по линейному закону. 4. Ввести в ячейки Н1:L1 период времени с 2005 по 2013 г. с шагом 2 года. Выделить диапазон H2:L2 и ввести формулу = ТЕНДЕНЦИЯ (B2:G2;B1:G1;H1:L1). Завершить нажатием комбинации клавиш Ctrl + Shift + Enter. 5. Вычислить с помощью функции ТЕНДЕНЦИЯ предположительную численность населения России на период с 1987 по 1993 г.г. 6. Аналогичным образом спрогнозировать изменение численности населения с помощью функции РОСТ по экспоненциальному закону. 7. По заданным экспериментальным точкам и полученным модельным данным построить диаграмму типа График в виде плавной линии.  Задание 3 Построить диаграмму, содержащую заданные точки и линейный, экспоненциальный и полиномиальный тренды с соответствующими уравнениями. Оценив достоверность аппроксимации, выбрать лучший вид уравнения. Порядок выполнения работы 1. Дать рабочему листу название «Регрессия». 2. Заполнить ячейки исходными данными. 3. Построить диаграмму для экспериментальных точек. Тип диаграммы – График, но точки не соединены линиями. 4. Выделив график и щелкнув на нем правой клавишей мыши, вызвать контекстное меню и выбрать в нем строку Добавить линию тренда. 5. Добавить Линейный тренд, Полиномиальный, Степенной. В процессе построения тренда указать уравнение, которому подчиняется зависимая величина. Для этого при построении линии тренда на вкладке Параметры установить флажок в строке Показывать уравнение на диаграмме. 6. Выполнить для линий тренда: Поместить на диаграмму величину достоверности аппроксимации. 7. Оформить линии трендов по своему усмотрению, меняя цветовую гамму и форму маркеров. 8. Сформировать сводную таблицу для анализа качества приближения к экспериментальным точкам, указав в столбцах тип тренда, уравнение зависимости и значение достоверности аппроксимации.  9. Рассчитать значения функции для каждой линии тренда, для этого вставляем формулу расчета значений функции из диаграммы на лист, и заменяем значения х на относительную ссылку значения х. Копируем функции на весь диапазон значений.  10. При анализе полученных данных наиболее близкими значениями к исходным являются полиноминальные. Лабораторная работа 7 ТЕХНОЛОГИЯ ОРГАНИЗАЦИИ И ОБРАБОТКИ СПИСКОВ ДАННЫХ В EXCEL Цели работы 1. Изучить возможности для организации данных в Excel в виде списка или базы данных. 2. Освоить технологию обработки списков в Excel. 3. Научиться извлекать определенные записи и поля из баз данных. Задание Создать базу данных Excel оплаты населением города коммунальных услуг, состоящую из следующих столбцов (полей). Сформировать таблицу поступлений счетов за коммунальные услуги от населения за месяц с учётом следующих условий: - поступление счетов происходит раз внеделю; - даты поступления счетов от районов фиксируются в произвольном порядке; - наименования районов формируются в произвольной последовательности; - категории услуг формируются последовательно (газ,свет, м2); - стоимость – случайно распределенная величина в диапазоне: за газ – [1; 10]; за свет – [10; 25]; за кв. м – [50; 500]; - пени – случайно распределенная величина от 1 до 12. Задания разместить на 5-ти рабочих листахExcel. 1. Базу данных в виде списка, все сведения о поступивших счетах оплаты коммунальных услуг по районам города. 2. Таблица, отфильтрованная по категории за газ. 3. Таблица, отфильтрованная по оплате за свет в первую десятидневку месяца с построением диаграммы. 4. Таблица в соответствии с условием фильтрации списка по технологии Расширенного фильтра. 5. Таблица и диаграмма в соответствии с условием фильтрации списка по варианту самостоятельной работы. Примечание. Учитывать рабочие дни в соответствии с календарем. Порядок выполнения работы 1. Создать книгу Excel, дополните ее необходимым количеством рабочих листов. 2. Заполнить рабочий лист. Заполнить данными столбцы A, B, C (использовать автозаполнение, задав в С3, C4, C5 соответственно газ, свет, кв. м). Задать 50 записей; - выполнить вычисления в столбце поля Сумма с использованием функций: ОКРУГЛ() – округления чисел до указанного количества десятичных разрядов, Например: =ОКРУГЛ(СЛУЧМЕЖДУ(1;10);2) СЛУЧМЕЖДУ() – генерирования случайных чисел в указанном интервале значений. Примечание. Результаты вычислений по формулам могут не совпадать с результатами вычислений на образце, так как использован датчик случайных чисел; - выполнить вычисления в столбце поля Пени (%) диапазон [1, 12]. - выполнить вычисления в столбце поля Пени (руб): (формула =D3*E3/100). - выполнить вычисления в столбце поля Всего (=D3+F3).  3. Сохранить числовые значения сформированной базы данных на отдельном листе. 4. Выделив диапазон базы данных, нажать Главная / Форматировать как таблицу, в полях заголовка автоматически появятся маркеры всплывающего списка. 5. Просмотреть строки базы данных по одной, используя режим Формы. 6. Скопировать сформированную базу данных на следующие три листа книги, озаглавить листы. 7. Произвести фильтрацию базы данных по полю Услуги. 8. Произвести фильтрацию базы данных по полям Дата (меньше указанной даты) и Услуги (Свет).  9. Построить гистограмму для списка значений отфильтрованных по критериям двух полей – Дата и Услуги. 10. Выполнить фильтрацию данных списка на отдельном листе с помощью Расширенного фильтра. Активизировать расширенный фильтр Данные / Фильтр / Дополнительно / Расширенный фильтр. Появится окно Расширенный фильтр. Заполнить поля окна Расширенного фильтра, установить Фильтровать список на месте. Отфильтровать по определенному критерию (например, поля с услугой «Свет». Разместить отфильтрованные данные рядом на том же листе.  11. Сохранить книгу. Список использованной литературы: Мокрова Н.В. Табличный процессор Microsoft Office Excel 2007:Методические указания / Н.В. Мокрова. – М.: МГУИЭ,2012. – 48с.; ил. |