Указания методические / Н. В. Мокрова. М.: Мгуиэ, 2012. 48с.; ил. Вметодических указаниях к лабораторным работам изложены основные способы работы в приложении Microsoft Office УДК 512.85 ББК 32.973 М70 Рецензенты: кафедра математики, логики и интеллектуальных систем Российского государственного гуманитарного университета; д-р техн. наук, проф., А.М. Цирлин, Институт программных систем им. А.К. Айламазяна РАН Допущено редакционно-издательским советом МГУИЭ Мокрова Н.В. М70 Табличный процессор Microsoft Office Excel 2007: Методические указания / Н.В. Мокрова. – М.: МГУИЭ, 2012. – 48с.; ил. В методических указаниях к лабораторным работам изложены основные способы работы в приложении Microsoft Office 2007 – табличном процессоре Excel и практические задания для их освоения. Темы лабораторных работ соответствуют учебным и календарным планам занятий. В каждой лабораторной работе приведены задания для получения навыков работы в табличном процессоре и варианты заданий для контроля и проверки знаний. Лабораторные работы по Excel рассчитаны на знание интерфейса Microsoft Office 2007. При подготовке лабораторных работ использованы материалы И.В. Кошелевой. Предназначены студентам I курса инженерных и экономических специальностей МГУИЭ, изучающим дисциплины «Информатика», «Информационные технологии». УДК 512.85 ББК 32.973 ã Н.В. Мокрова, 2012 ã МГУИЭ, 2012 Лабораторная работа 1 ОСНОВНЫЕ ПРИЁМЫ РАБОТЫ в MS Excel 2007 Цели работы 1. Освоить операции ввода чисел, текста и формул в ячейки рабочего листа Excel . 2. Освоить работу с относительными адресами ячеек рабочего листа Excel и способы присваивания имен ячейкам. 3. Изучить правила применения арифметических операций и некоторых математических функций Excel . 4. Освоить основные операции: автозаполнение, копирование, сохранение данных. 5. Создать и отформатировать простейшую таблицу в MS Excel . Задание 1 Вычислить арифметические выражения, используя относительную адресацию, арифметические операции и скобки для указания приоритетов действий. Порядок выполнения работы 1. Дать рабочему листу название «Вычисления ». 2. В ячейку A 1 ввести комментарий х = формат ячейки текстовый, в ячейку A 2 – комментарий у = (рис. 1). 3. Затем в ячейку В 1 поместить число 4, а в ячейку В 2 – число 3. Выполнить вычисления в ячейках В3, В4 и В5 по предлагаемым формулам, подставляя вместо имен переменных адреса ячеек, в которых хранятся значения х и у : Ячейка B 3 B 4 B 5 Формула Рис. 1. Лист «Вычисления» Задание 2 Заданы стороны треугольника a, b и c. Вычислить его площадь по формуле Герона , где р – полупериметр, р = (а+b+c)/2, а также радиус вписанной окружности: и радиус описанной окружности . Порядок выполнения работы 1. Дать следующему рабочему листу название «Треугольник ». 2. Заполнить ячейки А 2:В 6 как показано на рис. 2 (в ячейке В 6 записать формулу в относительных ссылках, обратите внимание на форматирование текста и чисел в ячейках). 3. Присвоить ячейкам В 2 , В 3 и В 4 имена а , b , с соответственно. Для этого выделить сначала ячейку В 2 , в строке имен (над столбцом А ) выделить В 2 и набрать а , нажать Enter. Таким образом ячейке В 2 будет присвоено имя а . С помощью тех же действий ячейкам В 3 и В 6 присвоить имена b и р соответственно. При этом невозможно ячейке В 4 присвоить имя с , так как Е xcel использует символы с и r как служебные. Значит ячейке А 4 нужно присвоить имя, отличное от с, например, с _ 4. Дать имена ячейкам В 2 , В 3 , В 4 и В 6 можно и другим способом. На вкладке Формула выбрать Присвоить Имя (см. рис. 2). 5. Ввести в ячейку В 7 формулу =корень(p *(p ‑a )*(p ‑b )*(p ‑c _) , используя именованные ячейки. 6. Дополнить таблицу вычислениями радиусов вписанной и описанной окружностей. Рис. 2. Лист «Треугольник» Задание 3 Заполнить ячейки с помощью прогрессии. Заполнить столбец А с помощью арифметической прогрессии с первым элементом 5, разностью 1 и предельным значением 12. Порядок выполнения работы 1. Новому рабочему листу дать название «Прогрессия ». 2. Заполнить текстовые заголовки. 3. Заполнить столбец A арифметической прогрессией: - в ячейку А 2 ввести первый элемент, затем второй; - выделить две ячейки, поставить курсор мыши на маркер заполнения и перетащить вниз при нажатой правой кнопке мыши. 4. Заполнить столбец B, используя геометрическую прогрессию (рис. 3). - на вкладке Главная перейти на панель Редактирование и щёлкнуть Заполнить ; - в открывшемся списке выбрать Прогрессия ; - выбрать Расположение , Тип , ввести значения: Шаг ; Предельное значение . Нажать ОК . 5. Заполнить прогрессию «дата» (столбец С на рис. 3), установив параметр рабочий день. 6. Самостоятельно изучить приёмы копирования, перемещения, удаления ячеек и диапазонов. Рис. 3. Лист «Прогрессия» Задание 4 Создать и отформатировать таблицу «Финансовый план», сохранить таблицы для дальнейшего создания диаграмм. Порядок выполнения работы 1. Дать рабочему листу имя «Выполнение плана» . 2. Создать таблицу выполнения финансового плана для двух филиалов фирмы согласно образцу, пользуясь следующими инструментами Excel : - строки, содержащие одинаковые текстовые фрагменты, копировать с помощью Копировать и Вставить , либо с помощью маркера заполнения; - столбец А (названия месяцев) заполнить методом протягивания с использованием инструмента Прогрессия ; - размеры и начертание шрифтов форматировать с помощью панели инструментов, аналогичной такой же панели инструментов в W ord ; - формат записи данных (количество знаков после запятой, указание денежных единиц, способ записи даты и пр.), направление написания текста, способ его выравнивания, вид рамок, фоновый цвет и т.д. задавать, пользуясь инструментами панели форматирования и вкладками диалогового окна Формат ячеек . 3. В ячейку А 26 введите текст «Курс пересчета». В ячейку В 26 введите текст 1 у.е.= , выровняйте его по правому краю. В ячейку С 26 введите текущее значение курса доллара. 4. В ячейку С 4 введите формулу =B 4*$C $26 , с помощью маркера заполнения скопируйте эту формулу на весь диапазон С 4:С 15 . 5. В ячейку Е 4 введите формулу =D 4*$C $26 , с помощью маркера заполнения скопируйте эту формулу на весь диапазон Е 4:Е 15 . 6. В ячейку G 4 введите формулу =F 4*$C $26 , с помощью маркера заполнения скопируйте эту формулу на весь диапазон G 4:G 15 . Где здесь относительная и абсолютная адресация (ссылка)? Какие адреса (ссылки) изменятся при копировании? 7. Изменить значение курса доллара, проследить, как при этом меняются значения прибыли в столбцах С , Е и G . 8. Щелкнуть на кнопке Предварительный просмотр , чтобы увидеть, как будет выглядеть созданная таблица при печати. 9. Сделать текущей ячейку В 16 . Щелкнуть на кнопке Автосумма . Убедитесь, что программа автоматически подставила в формулу функцию СУММ и правильно выбрала диапазон ячеек для суммирования. Нажмите клавишу Enter . 10. Повторить подобные действия для ячеек С 16, D 16, E 16, F 16, G 16. 11. Сделать текущей ячейку B 17. Щелкнуть на кнопке Вставка функции (fx ), в списке Категория выбрать пункт Статистические , из развернувшегося списка выбрать функцию СРЗНАЧ и щелкнуть на кнопке ОК . Обратить внимание на то, что автоматически выделенный диапазон содержит все ячейки с числовым содержимым, включая ячейку значения суммы. Выделить правильный диапазон методом протягивания и нажмите Enter . 12. Повторить подобные действия для ячеек С 17, D 17, E 17, F 17, G 17. 13. Используя порядок действий, описанный ранее, определить месяц с наибольшей (функция МАКС ) и наименьшей (функция МИН ) прибылью. 14. Сохранить рабочую книгу в вашем каталоге. Рис. 4. Лист «Выполнение плана» Отчет о лабораторной работе должен содержать электронный документ с указанием фамилии и группы студента, выполненные на переименованных листах задания описательной части лабораторной работы и задания по предложенному преподавателем варианту на отдельных листах. Задания для самостоятельного выполнения Заполнить таблицу «Ввод данных в Excel » (рис. 5) согласно следующим требованиям: 1. Ввод текста осуществлять, соблюдая параметры форматирования ячеек. 2. Выполнить тиражирование значения (столбец B ) . 3. Оформить число 15,3 в форматах Excel (столбец C ), выбирая формат для каждой выделенной ячейки. 4. Заполнить произвольными значениями, не превышающими 15 , с проверкой вводимых данных (столбец D ). Выполнить Данные / Проверка вводимых значений / Тип данных / Действительные . 5. Заполнить столбец E случайными числами с заданным интервалом. Например: = СЛЧИС()*12 +3. (математическая функция СЛЧИС() – возвращает равномерно распределенное случайное число в интервале [0 ; 1] ). 6. Заполнить столбец F упорядоченным рядом чисел из интервала [3,15] с шагом 1,5. ( Прогрессия ). 7. Заполнить столбец G рабочими днями (автозаполнение) Рис. 5. Таблица «Ввод данных» Задания по вариантам Столб. B D E F G Вар. 1 23,9 <= 20 [‑2, 10] 1; шаг 0,5 с 01.10.10 2 34,5 >= 5 [0, 25] 7; шаг 1,5 с 01.11.10 3 0,92 [2, 15] [–1, 15] 0; шаг 0,3 с 01.12.10 4 98,1 < 34 [20, 35] -1; шаг 0,1 с 01.01.11 5 8,54 > ‑12 [–3, 18] 2; шаг 1,2 с 01.02.11 Лабораторная работа 2 ПОСТРОЕНИЕ ГРАФИКОВ И ДИАГРАММ СРЕДСТВАМИ Excel Цели работы 1. Ознакомиться с приёмами работы по созданию и редактированию графиков и диаграмм в MS Excel . 2. Освоить мастер диаграмм табличного процессора при построении графиков и диаграмм различных типов. Построение графиков и диаграмм Для построения диаграммы следует: 1. Выделить диапазон данных, которые будут отображены на диаграмме. 2. На вкладке Вставка и в группе Диаграммы выбрать тип создаваемой диаграммы (Гистограмма, Круговая, Линейчатая и др.). При вставке диаграмма переходит в режим редактирования и становится активна контекстная вкладка Работа с диаграммами. Работа с диаграммами Вкладка имеет три набора инструментов для изменения диаграмм: Конструктор – содержит параметры, определяющие тип диаграммы, источник данных и их упорядочивание, макеты диаграмм, а также команду Переместить диаграмму . Макет – позволяет указывать свойства диаграмм, добавлять или редактировать элементы диаграммы (подписи данных и осей, добавление легенды и элементов рисования), а также выбирать параметры, связанные с трёхмерными графиками. Формат – содержит опции выбора различных элементов диаграммы, присвоения стилей её графическим элементам, включая трёхмерные края, тени, фаски и пр. Построение сравнительной диаграммы финансовой деятельности двух филиалов фирмы Задание 1 Построить разные типы диаграмм для таблицы «Финансовый план» (лабораторная работа 1). Порядок выполнения работы 1. Открыть лист «Выполнение плана ». 2. Методом протягивания выделить диапазон ячеек A 4: B 15 , затем нажать клавишу CTRL и, удерживая ее, выделить диапазоны D 4:D 15 и F 4:F 15 . 3. Щелкнуть Вставка / Диаграммы / Гистограмма (для отображения графиков разных типов – столбчатой диаграммы финансового плана и двух графиков его фактического выполнения следует изменить тип выделенного элемента диаграммы (рис. 6). Рис. 6. Смешанная диаграмма 4. Так как диапазоны ячеек были выделены заранее, Мастер диаграмм автоматически определяет расположение рядов данных (в данном случае – по столбцам). Убедитесь, что данные на диаграмме выбраны правильно. 5. Используя вкладки Конструктор, Макет и Формат , измените тип диаграммы, введите название «Выполнение финансового плана» , названия осей тыс.$ и Месяцы года, сделайте соответствующие надписи в легенде. 6. По желанию можно создать эту диаграмму на текущем или отдельном рабочем листе Excel , установив соответствующий переключатель. 7. Убедитесь, что диаграмма внедрена в рабочий лист. Отформатируйте ее с помощью диалогового окна Форматирование элемента данных , которое открывается при нажатии правой клавиши мыши и позволяет изменять тип, толщину и цвет линии, а также тип, цвет и фон маркера для выделенного элемента диаграммы. 8. Создать на другом листе по образцу объемную диаграмму (рис. 7). 9. Озаглавить листы. . Рис. 7. Объемная диаграмма Построение полярных графиков и поверхностей Задание 2 Построить полярный график функции r = 2 sin(j), j Î [0; 2p]. Порядок выполнения работы 1. Табулировать заданную функцию, пользуясь арифметической прогрессией для формирования значений аргумента и автозаполнением формулой с относительной адресацией – для вычисления значений заданной функции. 2. Выделить диапазон заполненных ячеек и построить плоский график (тип диаграммы – График ) в декартовой системе координат. 3. Для этих же данных построить полярный график (тип диаграммы – лепестковая ). В полярных координатах положение точки на плоскости определяется расстоя­нием r этой точки от центра коор­динат, который в этом случае называется полюсом, и углом поворота j [рад], между лучом r и полярной осью (в Excel – это вертикаль, про­веденная из полюса вверх). Таким образом получается, что значения функции откладываются из центра координат в виде луча определенной длины, а аргумент, являющийся числом на оси Ох, преобразуется в угол поворота этого луча относительно полярной оси. 4. Сравнить полученные диаграммы. Чем отличается принцип построения этих типов графиков? Задание 3 Построить поверхность z = x2 – y2 при х , у Î [‑1; 1]. Порядок выполнения работы 1. На следующем листе построить поверхность или трехмерную диаграмму (предварительно преобразовать заданное выражение относительно z ). 2. Табулировать заданную функцию следующим образом: в столбец А , начиная с ячейки А 2 , с помощью арифметической прогрессии ввести значения аргумента х ; в строку 1, начиная с ячейки В 1 с помощью арифметической прогрессии ввести значения аргумента у . 3. В ячейку В 2 ввести формулу, соответствующую заданной функции, используя при этом смешанную адресацию: - для аргумента х – указать абсолютную ссылку на имя столбца, в котором хранятся его значения, т.е. на столбец А ; - для аргумента у – указать абсолютную ссылку на номер строки, содержащей эти значения, т.е. на строку 1. Например, для поверхности z = x2 – y2 формула будет иметь вид: =$A 2^2‑B $1^2 Создать сетку значений для каждой пары аргументов х – у: с помощью маркера заполнения размножить формулу из ячейки В 2 на весь диапазон. 4. Выделить блок рабочего листа Excel , содер­жащий значения функции z и ее аргументов, и построить трехмерную диаграмму типа Поверхность. Ряды данных при этом должны находиться в столбцах. 5. Отформатировать диаграмму. Отчет о лабораторной работе должен содержать электронный документ с указанием фамилии и группы студента, выполненные на переименованных листах задания описательной части лабораторной работы и задания по предложенному преподавателем варианту на отдельных листах. Задания для самостоятельного выполнения Построить указанные графики (поверхность и полярный) согласно вариантам (1 – 5). Для построения указанных в таблице типов диаграмм (столбцы «Тип диаграммы») использовать полученные случайным образом значения в заданных диапазонах (лабораторная работа 1). Вар. Поверхность Полярный график Тип диаграммы Круговые Линейчатые 1 Разрезанная круговая Пирамидальная Биржевая 2 Вторичная круговая Коническая Кольцевая 3 Вторичная гистограмма Объемная линейчатая Пузырьковая 4 Объемная круговая Цилиндрическая Разрезанная кольцевая 5 Объемная разрезанная круговая Линейчатая Объёмная пузырьковая Лабораторная работа 3 ЛОГИЧЕСКИЕ ФУНКЦИИ В Excel Цели работы 1. Изучить логические функции MS Excel . 2. Научиться использовать логические функции для построения таблиц истинности. 3. Изучить описание функций с условиями и построение их графиков. 4. Создать таблицу вычисления арифметических выражений в позиционных системах счисления. 5. Изучить применение функций ЕСЛИ , СЧЁТЕСЛИ для обработки табличной информации. Задание 1 Построить таблицу истинности логического выражения (ИСТИНА или ЛОЖЬ). Порядок выполнения работы 1. Дать рабочему листу название «Лог. выражение ». 2. Заполнить ячейки рабочего листа значениями логических переменных X , Y , Z . 3. Вычислить значения элементарных логических операций И (), ИЛИ (), НЕ () (столбцы D – G ). 4. В последнем столбце записать логическое выражение X ˆ Y ˇ (¬X ˇ Z ) (значения двух последних столбцов должны совпадать, рис. 10). 5. Отформатировать ячейки таблицы (использовать Переносить по словам ). Рис. 10. Лист «Лог. выражение» Задание 2 Табулировать функцию , построить график. Порядок выполнения работы 1. Дать рабочему листу название «Лог. функция ». 2. Используя арифметическую прогрессию, сформировать ряд значений аргумента (столбец А ). 3. В строке формул для столбца B задать значение логической функции (использовать автозаполнение с относительной адресацией). 4. Выделить диапазон заполненных ячеек и построить плоский график в декартовой системе координат. 5. Отформатировать диаграмму, отслеживая правильность значений по оси Ох (рис. 11). Задание 3 Создать таблицу значений арифметических действий в восьмеричной системе счисления (использовать функции перевода чисел категории Инженерные ). Порядок выполнения работы 1. Дать рабочему листу название «Восьмеричная ». 2. Заполнить таблицу сложения восьмеричных чисел согласно образцу (рис. 12). 3. Дополнить две строки и два столбца таблицы, выделить результаты «неправильные» в десятичной системе счисления. 4. Составить таблицу вычитания, отличную от таблицы сложения (размерность 5x5). 5. Составить таблицу умножения (размерность 4x4). 6. Записать формулы для перевода действительного числа из восьмеричной системы счисле­ния в десятичную и обратно. 7. Отформатировать таблицы. Задание 4 Для заданной таблицы, построенной на основе наблюдений метеостанции г. Екатеринбурга, определить: – минимальное месячное количество осадков за три года; – суммарное количество осадков, выпавшее за три года; – среднемесячное количество осадков по каждому году; – среднемесячное количество осадков по итогам трехлетних наблюдений; – максимальное месячное количество осадков по итогам трехлетних наблюдений; – количество засушливых месяцев за все 3 года, в которые выпало меньше 10 мм осадков; – количество месяцев в каждом году с осадками в пределах нормы (> 20 мм; < 80 мм); – количество месяцев в каждом году с осадками вне нормы (< 10 мм; > 100 мм); – построить столбчатую диаграмму по данным наблюдения за осадками в течение трех лет, позволяющую провести сравнение количества осадков. Порядок выполнения работы 1. Дать рабочему листу название «Погода ». 2. Для выполнения заданий использовать функции МИН ; МАКС ; СРЗНАЧ ; СУММ категории Статистические. 3. Для выполнения заданий 6, 7, 8 – функцию СЧЁТЕСЛИ , которая подсчитывает количество непустых ячеек внутри интервала, удовлетворяющих заданному критерию. При ее использовании необходимо задать: интервал поиска (диапазон ячеек) и критерий подсчета (условие). Функция СЧЁТЕСЛИ имеет только два аргумента! Например , = СЧЁТЕСЛИ (А 1:С 15 ;”< 25”). 4. Отформатировать таблицу по своему усмотрению. Отчет о лабораторной работе должен содержать электронный документ с указанием фамилии и группы студента, времени выполнения, названия, цели лабораторной работы, выполненные задания описательной части работы и задания по предложенному преподавателем варианту. Таблица Результаты наблюдений метеостанции г. Екатеринбурга Месяцы 2002 г. 2003 г. 2004 г. Месяцы 2002 г. 2003 г. 2004г. Январь 37,2 34,5 8,0 Июль 57,1 152,9 50,6 Февраль 11,4 51,3 1,2 Август 43,8 96,6 145,2 Март 16,5 20,5 3,8 Сентябрь 85,7 74,8 79,9 Апрель 19,5 26,9 11,9 Октябрь 86,0 14,5 74,9 Май 11,7 45,5 66,3 Ноябрь 12,5 21,0 56,6 Июнь 129,1 71,5 60,0 Декабрь 21,2 22,3 9,4 Задания для самостоятельного выполнения Выполнить задания 1–3 лабораторной работы 3. Вар. Составить таблицу истинности Построить график функции Составить таблицы в СС 1 Семеричная 2 Девятеричная 3 Пятеричная 4 Шестеричная 5 Троичная Лабораторная работа 4 РЕШЕНИЕ НЕЛИНЕЙНЫХ УРАВНЕНИЙ и систем уравнений ПРОГРАММНЫМИ СРЕДСТВАМИ Excel Цели работы 1. Научиться использовать инструмент Подбор параметра для решения нелинейных уравнений вида f (x ) = 0. 2. Освоить программный инструмент Поиск решения для решения систем нелинейных уравнений в среде Excel . 3. Освоить программные средства для поиска координат и значений экстремумов функции одной переменной с помощью инструмента Поиск решения . Задание 1 Найти все корни уравнения . Порядок выполнения работы 1. Дать рабочему листу название «Уравнение ». 2. Настроить текущий документ Еxcel на вычисления с заданной точностью, открыв Настройку панели быстрого доступа и выбрав Другие команды . На вкладке Формулы задать предельное число итераций, равное 10000, и относительную погрешность, равную 0,000001. 3. Найти решение нелинейного уравнения в два этапа. Этап – ЛОКАЛИЗАЦИЯ КОРНЕЙ 4. Создать таблицу значений функции f (x ) для х Î [‑2,5; 2,5], шаг изменения 0,1. 5. Построить график функции f (x ) (тип – График ). 6. Основываясь на данных таблицы и графика, выделить интервалы, на которых функция меняет знак (существует корень). Этап – УТОЧНЕНИЕ КОРНЕЙ 7. Задать начальные приближения к корням – точки из отрезков локализации корней, например: ‑2,2; 0 и 1,5. Ввести эти значения в ячейки F 16 , F 17 и F 18 соответственно. 8. В ячейку G 16 ввести формулу =(F 16^3-4*F 16+1)/(ABS (F 16)+1) и с помощью маркера заполнения размножить эту формулу в ячейки G 17 и G 18. 9. На вкладке Данные выбрать Анализ «что-если» , открыть диалоговое окно Подбор параметра и заполнить его поля: в поле Установить в ячейке ввести G 16 (в этом поле дана ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения); в поле Значение ввести значение 0 (здесь указывается правая часть уравнения); в поле Изменяя значение ячейки ввести F 16 (в этом поле дана ссылка на ячейку, отведенную под переменную). После нажатия кнопки OK средство подбора параметра находит приближенное значение корня с заданной точностью. 10. Повторить указанные в п. 9 действия для ячеек G 17 , F 17 и G 18 , F 18 соответственно. Ячейки F 16:F 18 содержат значения корней уравнения, а ячейки G 16:G 18 – значения функции в этих точках, близкие к нулю. 11. Оформить созданный лист заголовками и комментариями, как в образце (рис. 13). K Рис. 13. Лист «Уравнение» Задание 2 Найти решение системы нелинейных уравнений на интервале [– 1,7; 1,7] с помощью Excel , используя инструмент Поиск решения . Порядок выполнения работы 1. Дать рабочему листу название «Система ». 2. Преобразовать уравнения системы относительно переменной у , получив две полуокружности в положительной и отрицательной полуплоскостях, пересекающих прямую. 3. Столбец А (аргумент x ) заполнить с помощью арифметической прогрессии на интервале [– 1,7; 1,7] с шагом 0,1. 4. Столбцы B , C , D озаглавить как y 1, у 2, у 3 и заполнить формулами, соответствующими полуокружностям и прямой, используя относительную адресацию и маркер заполнения. 5. Построить графики функций системы уравнений для диапазона А 2:D 36 в одной системе координат и определить интервалы локализации корней. 6. Отредактировать диаграмму, согласно образцу (рис. 15). 7. На втором этапе – УТОЧНЕНИЕ КОРНЕЙ – найти корни системы уравнений с помощью инструмента Поиск решения. 8. Для нахождения первого корня в ячейку F 25 ввести начальное приближение для первого корня по х . В ячейку G 25 – начальное приближение для первого корня по у . В ячейку H 25 ввести формулу = ( F 25^2+ G 25^2–3)^2+(2* F 25+3* G 25–1)^2 9. Чтобы получить уточненное значение первого корня, открыть диалоговое окно Поиск решения вкладки Данные . В поле Изменяя ячейки ввести диапазон ячеек F 25: G 25 . В группе Равной установить переключатель в положение Значению , в поле ввода которого ввести 0 . Убедитесь, что в диалоговом окне Параметры поиска решения снят флажок Линейная модель . Затем нажать кнопку Выполнить (рис. 14). 10. Вы получили приближенное решение в ячейках F 25 и G 25 , а в ячейке H 25 достаточно близкое к нулю значение и с заданной точностью приблизились к точке пересечения окружности с прямой линией. Если в ячейке H 25 будет большое число, то решение найдено неверно. Рис. 14. Инструмент «Поиск решения» 11. Аналогично находят второе решение. Но в качестве начального приближения принимают точку, близкую по координатам ко второму корню. 12. Проверить пару значений (1,5; 0). Область притяжения какого корня вы получаете? 13. Оформить созданный документ заголовками и комментариями (рис. 15). Задание 3 Найти экстремумы функции на интервале [‑ 2; 2] с шагом 0,1. Точность поиска e = 10-6 Порядок выполнения работы: 1. Дать рабочему листу название «Экстремум ». 2. Задав значения аргумента (столбец А ) и рассчитав значения функции (столбец В ) , построить график функции и определить интервалы локализации экстремумов. 3. На этапе – УТОЧНЕНИЕ ЭКСТРЕМУМОВ – найти уточненные значения координат экстремумов и значения функции в этих точках с помощью инструмента Поиск решения . Рис. 15. Лист «Система» 4. Для нахождения первого экстремума (максимума) в ячейку F 17 ввести начальное приближение, в ячейку G 17 ввести формулу = F 17^3 – F 17^2 + 4. 5. Чтобы получить уточненное значение максимума, открыть диалоговое окно Поиск решения вкладки Данные. В поле Установить целевую ячейку ввести адрес ячейки, содержащей формулу – G 17 . В группе Равной установить переключатель в положение Максимальному значению . В поле Изменяя ячейки ввести адрес ячейки F 17 . Затем нажать кнопку Выполнить. 6. Получено приближенное значение координаты экстремума и максимальное значение функции в ячейках F 17 и G 17. 7. Аналогично находят второй экстремум. Но при настройке диалогового окна Поиск решения в группе Равной установить переключатель в положение Минимальному значению. 8. Оформить созданный документ заголовками и комментариями (рис. 16). Отчет о лабораторной работе должен содержать электронный документ с указанием фамилии и группы студента, времени выполнения, названия, цели лабораторной работы, выполненные задания описательной части работы и задания по предложенному преподавателем варианту. Примечание. Если на вкладке Данные не оказалось Поиска решения , то его нужно подгрузить, используя Настройку панели быстрого доступа . Рис. 16. Лист «Экстремум» Задания для самостоятельного выполнения Выполнить задания 1‑3 лабораторной работы для заданных вариантов функций Вар Решить уравнение и найти экстремум функции Решить систему нелинейных уравнений 1 2 3 4 5 Лабораторная работа 5 Формулы Excel для обработки массивов данных Цели работы 1. Освоить функции табличного процессора для обработки матриц и решения систем линейных алгебраических уравнений. 2. Изучить процедуру применения табличных формул для обработки больших массивов данных в Excel . Важно ! При обработке матриц необходимо помнить два основных правила: Перед вводом формулы на рабочем листе выделяется область, размер которой соответствует размерности получаемой при расчетах матрицы. Ввод матричной формулы завершается нажатием комбинации клавиш Ctrl + Shift + Enter , а не просто Enter , как при обычных вычислениях. Задание 1 Рассчитать требуемые характеристики квадратной матрицы и определить решение системы линейных уравнений. Порядок выполнения работы 1. Дать рабочему листу название «Матрицы ». 2. Задать матрицы A и B (рис. 17). 3. Вычислить определитель квадратной матрицы (числовая характеристика) с помощью функции МОПРЕД категории Математические (например, =МОПРЕД(B 2:D 4)). 4. Вычислить обратную матрицу для заданной с помощью функции МОБР категории Математические (заметим, что матричное произведение исходной матрицы и ее обратной матрицы дает единичную матрицу). 5. Транспонировать матрицу (поменять местами строки и столбцы) с помощью функции ТРАНСП категории Ссылки и массивы. (после транспонирования вектор-столбец преобразуется в строку, а вектор-строка – в столбец). 6. Выполнить операции сложения, вычитания, умножения и деления матрицы и числа посредством арифметических операторов: +, ‑, *, / (например, =B 2:D 4 * 0,5). 7. Операции поэлементного сложения, вычитания, умножения и деления применяют только к матрицам одинаковой размерности и их выполняют посредством арифметических операторов +, – , *, /. (например, =F 2:F 4+H 2:H 4). 8. Вычислить матричное произведение двух матриц с помощью функции МУМНОЖ категории Математические (число столбцов первой матрицы равно числу строк второй матрицы). В результате этой операции получается матрица, число строк которой равно числу строк первой умножаемой матрицы, а число столбцов – числу столбцов второй матрицы. 9. Решить систему линейных алгебраических уравнений (СЛАУ). Рис. 17. Лист «Матрицы» В алгебраической форме СЛАУ порядка n записывают в виде . Или в матричной форме: АХ = В, где А – матрица коэффициентов; В – вектор-столбец свободных членов; Х – вектор-столбец неизвестных: Решение СЛАУ в матричном виде находят по формуле Х = А– 1 В, где А–1 – матрица, обратная А. На рабочем листе Excel записаны матрица коэффициентов А и вектор-столбец свободных членов В. Для получения решения выделить ячейки, соответствующие вектору-столбцу из n элементов для неизвестных и записать матричную формулу решения системы (например: =МУМНОЖ(МОБР(B 2:D 4);F 2:F 4)) Задание 2 Создать таблицу расчета заработной платы, используя табличные формулы Excel для расчета процентных отчислений и сумм к выдаче. Порядок выполнения работы 1. Дать рабочему листу название «Ведомость ». 2. Создать таблицу ведомости по зарплате на лист Excel (см. образец), отсортировать по алфавиту. № п/п Фамилия Размер оклада, руб. Подоходный налог (12 % от оклада) Отчисления в пенсионный фонд (1 % от оклада) Профсоюзный взнос(1 % от оклада) К выдаче 1 Ушков А.С. 9 500,00 2 Карпов В.А. 7 000,00 3 Вилков И.И. 15 000,00 4 Абрамов С.Т. 18 000,00 5 Иванова С.И. 13 500,00 6 Кукушкина С.А. 7 000,00 7 Ларин В.Н. 10 000,00 8 Машин С.И. 8 000,00 9 Зуев А.И. 15 000,00 10 Кошкин А.Н. 9 500,00 Итого 0,00р. 0,00р. 0,00р. 0,00р. 3. Рассчитать итоговую сумму к выдаче (матричная формула). 4. В дополнительном столбце восстановить первоначальные величины окладов без вычетов налогов и взносов. 5. Удалить первую строку ведомости (сотрудник Абрамов С.Т.). 6. Добавить в таблицу ведомости нового работника – Юшкова А.Ф., размер оклада которого составляет 13570 руб. и рассчитать для него значения по всем столбцам ведомости. Примечание. Табличные формулы редактируются не как обычные формулы рабочего листа Excel . Ниже приведены алгоритмы редактирования табличных формул. Редактирование формулы 1. Выделить блок с формулой. 2. Нажать клавишу F 2 . 3. Внести изменения в формулу. 4. Нажать клавиши Ctrl + Shift + Enter . Изменение размеров блока (удаление/вставка строк) 1. Выделить блок с табличной формулой. 2. Нажать клавишу F 2 . 3. В начало формулы добавить апостроф, формула превращается в текст. 4. Вводим текст во все ячейки с помощью клавиш Ctrl + Enter . Табличная формула прекратила существование. 5. Очистить строку таблицы. 6. Выделить блок с табличной формулой. 7. Нажать клавишу F 2 . 8. Удалить апостроф. 9. Нажать клавиши Ctrl + Shift + Enter . Отчет о лабораторной работе должен содержать электронный документ с указанием фамилии и группы студента, времени выполнения, названия, цели лабораторной работы, выполненных заданий описательной части работы и задания по предложенному преподавателем варианту. Задания для самостоятельного выполнения Вар Решить систему Вычислить 1 Сумму матрицы А и каждой строки матрицы А . Сумму А и 3. Произведение 1-й строки и 2-го столбца матрицы А (поэлементно). Транспонировать матрицы А , 4В . Произведение матрицы Х на число 5. Сумму элементов В и 1-й строки А . 2 Разность 3-й строки и 1-го столбца матрицы А (поэлементно). Обратную матрицу для матрицы А -3. Транспонировать матрицы А , В . Сумму элементов А , В и Х . Произведение матрицы А на число 1/3. Сумму элементов В и 1-й строки А . 3 Сумму 1-й строки и 2-го столбца матрицы А (поэлементно). Обратную матрицу для матрицы А . Транспонировать матрицы 3Х , В . Сумму элементов В и 1-й строки А . Произведение матрицы А на число 0,5. Определитель матрицы А. 4 Сумму матрицы А , и ее каждого столбца. Разность 3-й строки и 2-го столбца матрицы А (поэлементно). Транспонировать матрицы Х , 2В . Разность В и 1-го столбца матрицы А . Произведение матрицы Х на число 3. Найти матричное произведение А и 2-го столбца матрицы А . 5 Сумму матрицы А и каждой строки матрицы А . Сумму А и 10. Обратную матрицу для матрицы А -3. Транспонировать матрицы А , В . Произведение матрицы Х на число 2. Матричное произведение А и 3-го столбца матрицы А . Лабораторная работа 6 ПРОГНОЗ ЗНАЧЕНИЙ ЧИСЛОВЫХ ВЕЛИЧИН С ПОМОЩЬЮ СТАТИСТИЧЕСКИХ ФУНКЦИЙ Excel Цели работы 1. Освоить статистические функции Excel для прогнозирования значений числовых последовательностей. 2. Научиться использовать функции Excel для обработки экспериментальных данных при помощи трендов. Задание 1 На основании статистических данных о численности населения России на период с 1993 по 1999 годы сделать прогноз на 2001 и 2003 г. Порядок выполнения работы 1. Дать рабочему листу название «Прогноз ». 2. Заполнить ячейки рабочего листа Excel следующими статистическими данными, выделив их цветом: A B C D E F 1 Годы 1993 1995 1997 1999 2 Численность населения, млн чел. 148,3 147,9 147,5 146,3 3. Сделать предположение о численности населения России в 2001 году с помощью функции ПРЕДСКАЗ , которая позволяет вычислить теоретическое значение зависимой переменной (в данном случае это численность населения) в фиксированной точке аргумента (т.е. для определенного года). Для этого в ячейку F 1 ввести дату – 2001, а в ячейку F 2 формулу = ПРЕДСКАЗ(F 1;B 2:E 2;B 1:E 1) 4. Аналогичным способом предсказать численность российских граждан в 2003 году, но уже учитывая рассчитанное ранее значение в 2001 г. 5. Используя условное форматирование, выделить столбцы с минимальными и максимальными показателями, построить график. Задание 2 С помощью функций ТЕНДЕНЦИЯ и РОСТ предсказать изменение численности населения на периоды с 2001 по 2015 г.г. и смоделировать на период с 1985 по 1993 г. Порядок выполнения работы 1. Дать рабочему листу название «Тенденция ». 2. Копировать часть таблицы «Прогноз ». 3. Спрогнозировать дальнейшую динамику изменения численности на период с 2003 по 2013 г.г. при помощи функции ТЕНДЕНЦИЯ. Эта функция позволяет предсказать значения зависимой переменной для целого диапазона значений независимой переменной по линейному закону. 4. Ввести в ячейки Н 1:L 1 период времени с 2005 по 2013 г. с шагом 2 года. Выделить диапазон H 2:L 2 и ввести формулу = ТЕНДЕНЦИЯ(B 2:G 2;B 1:G 1;H 1:L 1). Завершить нажатием комбинации клавиш Ctrl + Shi ft + Enter . 5. Вычислить с помощью функции ТЕНДЕНЦИЯ предположительную численность населения России на период с 1987 по 1993 г.г. 6. Аналогичным образом спрогнозировать изменение численности населения с помощью функции РОСТ по экспоненциальному закону. 7. По заданным экспериментальным точкам и полученным модельным данным построить диаграмму типа График в виде плавной линии. Сделать вывод о поведении линейной и экспоненциальной модели изменения численности населения. Задание 3 Построить диаграмму, содержащую заданные точки и линейный, экспоненциальный и полиномиальный тренды с соответствующими уравнениями. Оценив достоверность аппроксимации, выбрать лучший вид уравнения. Порядок выполнения работы 1. Дать рабочему листу название «Регрессия ». 2. Заполнить ячейки исходными данными. x 1,5 2 3 4,5 5 6 7,5 y 12 7 3 11 17 18 23 3. Построить диаграмму для экспериментальных точек. Тип диаграммы – График, но точки не соединены линиями. 4. Выделив график и щелкнув на нем правой клавишей мыши, вызвать контекстное меню и выбрать в нем строку Добавить линию тренда . 5. Добавить Линейный тренд, Полиномиальный 2-го и 3-го порядка, Степенной . В процессе построения тренда указать уравнение, которому подчиняется зависимая величина. Для этого при построении линии тренда на вкладке Параметры установить флажок в строке Показывать уравнение на диаграмме. 6. Выполнить для линий тренда: Поместить на диаграмму величину достоверности аппроксимации. 7. Оформить линии трендов по своему усмотрению, меняя цветовую гамму и форму маркеров. 8. Сформировать сводную таблицу для анализа качества приближения к экспериментальным точкам, указав в столбцах тип тренда, уравнение зависимости и значение достоверности аппроксимации. 9. Сделать вывод: какой из трендов дает наилучшее приближение к экспериментальным точкам? 10. Выполнить условное форматирование таблицы с учётом сделанного вывода. Отчет о лабораторной работе должен содержать электронный документ с указанием фамилии и группы студента, времени выполнения, названия, цели лабораторной работы, выполненных заданий описательной части работы и задания по предложенному преподавателем варианту. Задания для самостоятельного выполнения По следующим данным с помощью функции ПРЕДСКАЗ спрогнозировать стоимость киловатта электроэнергии до 2015 года. Годы 1996 1997 1998 1999 2000 2001 2002 2003 Стоим. кВт,коп. 4 13 24 38 50 70 80 98 Выполнить графическую оценку данных, используя функции РОСТ и ТЕНДЕНЦИЯ. Вар. 1 2 3 4 5 Годы 1996,1998, 2000,2002 1999,2001, 2002,2003 1997,1999, 2001,2003 1998,1999, 2000,2001 1996,1997, 1998,1999 Построить Линейный