| Microsoft Exel 2000 | |
| Автор: drug | Категория: Технические науки / Автоматизация | Просмотров: | Комментирии: 0 | 01-01-2013 22:29 |
Введение
Microsoft Exel 2000 – табличный процессор для представления и обработки данных в виде электронных таблиц, имеющий универсальные возможности решения задач и встроенные средства деловой графики. Microsoft Exel 2000 имеет мощный аппарат математических инструментов для решения задач линейного программирования, оптимизации статистического моделирования и т.п.. Создаваемые файлы имеют расширение XLS
Табличный процессор Excel входит в состав пакета MS Office, предназначен для создания, изменения и анализа документов, представленных в виде таблиц. Областью применения MS Excel может являться делопроизводство, учет, статистика, финансы, инженерия, наука.
При помощи MS Excel можно форматировать и редактировать таблицы; проводить вычисления с использованием сложных формул и сценариев; графически представлять данные в виде диаграмм; сортировать и фильтровать данные; объединять информацию из разных таблиц в сводные таблицы. Помимо вышеперечисленного, Excel, как программный продукт, имеет простую систему настройки и управления, развитую систему контекстных справок, меры защиты от неправильных действий пользователя.
Цель работы:
Целью работы является получение навыков при работе с мастером диаграмм для построения графиков, которые более наглядно представляют данные; нахождение определённого результата для ячейки с помощью подбора значения другой ячейки на примере полиномов различ¬ных степеней; расчеты по формулам, завися¬щим от большого массива данных.
3. Краткие теоретические сведения.
3.1. Основные теоретические сведения.
Типовая структура интерфейса MS Excel (рис. 3.1) включает ряд элементов.
Рисунок 3.1 – Типовая структура интерфейса MS Excel.
Заголовок окна - это поле, которое содержит название приложения и обрабатываемого в нем документа, а также кнопки управления размером и положением окна.
Главное меню системы – это меню, через которое доступны все функции MS Excel.
Для удобства многие средства управления дублируются в форме пиктограмм, содержащихся на панелях инструментов.
Строка формул - это элемент, который представляет формулы.
Поле имени – поле, служащее для выполнения следующих функций:
1) в обычном состоянии отображает адрес текущей ячейки рабочего листа;
2) во время выделения диапазона в поле отображает число включенных строк и столбцов;
3) в этом поле можно непосредственно ввести адрес нужной клетки, тогда, после нажатия клавиши Enter, активным окажется указанное место листа;
4) в режиме редактирования формул появляется меню функций Excel, из которого можно выбрать нужную.
Ниже строки формул находится рабочее пространство Excel, называемое рабочим листом.
Под пространством рабочего листа отображены вкладки листов, позволяющие быстро вызвать любой из имеющихся в рабочей книге листов, щелкнув по нему мышью. Здесь же имеются четыре кнопки-треугольника перемещения по листам. Они используются при необходимости доступа к листам, вкладки которых не уместились в строке.
Самая нижняя строка интерфейса - строка состояния, в этой строке отображается текущий режим таблицы, а также даются подсказки по возможным действиям. В левой части строки состояния указывается текущий режим работы таблицы:
1) Готово - в этом состоянии возможно перемещение в таблице и доступ к меню пиктограмм.
2) Ввод - режим ввода данных в клетку.
3) Правка - похож на предыдущий, режим редактирования уже введенных данных.
4) Указать - режим выбора объекта (клетки, области) для включения его в выражение. Выделяемый объект ограничивается бегущей пунктирной линией.
В правой части строки состояния имеется поле, где можно отобразить сумму, максимум, минимум, среднее, количество значений и количество ячеек любой выделенной области таблицы. Отображаемую функцию можно задать, щелкнув правой кнопкой мыши в строке состояния.
3.2. Организация данных в Excel
Книга - самая крупная единица данных в Excel, являющаяся синонимом файла Excel. Все такие файлы имеют расширение .xls. Книге можно дать любое имя, допустимое в среде Windows. По умолчанию они получают имена Книга1.xls, Книга2.xls. Число книг ограничено только дисковым пространством компьютера. Книга содержит рабочие листы.
Рабочий лист образует рабочее пространство пользователя. Книга состоит из нескольких рабочих листов (до 255). При открытии рабочей книги на экране виден только один лист - верхний. Нижняя часть листа содержит ярлычки других листов. Щелкая мышью на ярлычках листов, можно перейти к другому листу.
Листам можно давать произвольные имена длиной до 31-го символа, исключая знаки, такие, как: * : / \ ? [ ], по умолчанию они получают имена вида Лист1, Лист2.
Для изменения имени листа можно воспользоваться контекстным меню. Для этого нажмите правой кнопкой мыши на названии листа и выберите пункт меню «Переименовать».
Рабочий лист электронной таблицы состоит из строк и столбцов. Каждое пересечение столбца и строки образует ячейку, в которую можно вводить данные (текст, число, формулу).
Номер строки - определяет ряд в электронной таблице. Он обозначен на левой границе рабочего поля. Всего на листе 65536 строк.
Буква столбца - определяет колонку в электронной таблице. Буквы находятся на верхней границе рабочего поля. Колонки нумеруются в следующем порядке: A - Z, затем AA - AZ, затем BA - BZ и т.д.. Всего на листе 256 столбцов.
Ячейка - первичный элемент таблицы, содержащий данные. Каждая ячейка имеет уникальный адрес, состоящий из буквы столбца и номера строки. Например, адрес B3 определяет ячейку на пересечении столбца B и строки номер 3. Всего на листе 256ґ65536=16 777 21 ячеек.
Указатель ячейки - черный светящийся прямоугольник, определяющий текущую ячейку. Указатель можно перемещать по таблице, как при помощи клавиатуры, так и мышью.
Текущая ячейка помечена указателем. Ввод данных и некоторые другие действия по умолчанию относятся к текущей ячейке.
Диапазон представляет собой прямоугольную область смежных ячеек. Диапазон может состоять из одной или нескольких ячеек, строк или столбцов. Адрес диапазона состоит из координат противоположных углов, разделенных двоеточием. Например, B13:C19, A12:D27 или D:F.
3.3. Типы данных MS Excel
В программе Excel существует несколько типов данных:
1. текстовый;
2. графический;
3. числовой:
- числовой (целые числа и числа с дробной частью);
- денежный (после числа указывается обозначение валюты);
- процентный (20% = 0,02);
- дата и время (число дней начиная от 01.01.1900г – это целая часть, время – дробная часть).
3.4. Функции
Функции в Excel используются для выполнения стандартных вычислений в рабочих книгах. Значения, которые используются для вычисления функций, называются аргументами. Значения, возвращаемые функциями в качестве ответа, называются результатами. Помимо встроенных функций можно использовать в вычислениях пользовательские функции, которые создаются при помощи средств Excel.
Чтобы использовать функцию, нужно ввести ее как часть формулы в ячейку рабочего листа. Последовательность, в которой должны располагаться используемые в формуле символы, называется синтаксисом функции. Все функции используют одинаковые основные правила синтаксиса. Если нарушить правила синтаксиса, Excel выдаст сообщение о том, что в формуле имеется ошибка.
Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле.
Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой «;». Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы.
В качестве аргументов можно использовать числа, текст, логические значения, массивы, значения ошибок или ссылки. Аргументы могут быть как константами, так и формулами. В свою очередь эти формулы могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Excel можно использовать до семи уровней вложенности функций.
Задаваемые входные параметры должны иметь допустимые для данного аргумента значения. Некоторые функции могут иметь необязательные аргументы, которые могут отсутствовать при вычислении значения функции.
3.5. Типы функций
Для удобства работы функции в Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок. Кроме того, присутствуют следующие категории функций: статистические, текстовые и математические.
При помощи текстовых функций имеется возможность обрабатывать текст: извлекать символы, находить нужные, записывать символы в строго определенное место текста и многое другое.
С помощью функций даты и времени можно решить практически любые задачи, связанные с учетом даты или времени (например, определить возраст, вычислить стаж работы, определить число рабочих дней на любом промежутке времени).
Логические функции помогают создавать сложные формулы, которые в зависимости от выполнения тех или иных условий будут совершать различные виды обработки данных.
В Excel широко представлены математические функции. Например, можно выполнять различные операции с матрицами: умножать, находить обратную матрицу, транспонировать.
С помощью статистических функций возможно проводить статистическое моделирование.
4. Методика выполнения лабораторной работы
4.1. Основные приемы заполнения таблиц
Содержимое ячеек
Для ввода данных необходимо переместиться в нужную ячейку и набрать данные, а затем нажать
Excel определяет, являются ли вводимые данные текстом, числом или формулой по первому символу. Если первый символ буква, то считается, что вводится текст, если первый символ цифра или знак =, то считается, что вводится число или формула.
Данные, которые вводятся, отображаются в ячейке и в строке формул и только при нажатии
Ввод текста
Текст - это набор любых символов. Если ширина текста больше ширины ячейки и ячейка справа от заполняемой пуста, то текст на экране займет и ее место. При вводе данных в соседнюю ячейку предыдущий текст будет обрезан (но при этом в ячейке он будет сохранен полностью).
По умолчанию после фиксации текста в ячейке он будет прижат к левому краю.
Текст может автоматически переноситься на следующую строку внутри ячейки. Для этого следует в меню Формат - Ячейка - Выравнивание, установить Переносить по словам.
Ввод чисел
Числа в ячейку можно вводить со знаками =, -, + или без них. Если ширина введенного числа больше, чем ширина ячейки на экране, то Excel изображает его в экспоненциальной форме либо вместо числа ставит символы #### (при этом число в ячейке будет сохранено полностью).
Экспоненциальная форма используется для представления очень маленьких либо очень больших чисел. Число 501000000 будет записано как 5,01Е+08, что означает 5,01*10^8. Число 0,000000005 будет представлено как 5Е-9, что означает 5*10^-9.
Для ввода дробных чисел используется десятичная запятая или точка в зависимости от настройки.
Любой символ перед числом или в середине числа превращает его в текст.
По умолчанию после фиксации числа в ячейке Excel сдвигает его к правой границе ячейки.
Ввод даты и времени
При вводе даты или времени Excel преобразует их в специальное число (поэтому дата и время после фиксации в ячейке прижаты к правому краю ячейки)..
Введенная дата может быть представлена в одном из нижеперечисленных форматов.
3/21/99; 21-Мар-99; 21-Мар; Мар-21
После фиксации даты в ячейке в качестве разделителя между днем, месяцем и годом устанавливается точка. Например: 3.21.99, 12.Мар.99.
Введенное время может иметь следующие форматы: 14:25; 14:25:09; 2:25 PM; 2:25:09 PM
Ввод формул
В виде формулы может быть записано арифметическое выражение. Оно представляет собой последовательность чисел или ссылок на ячейки, объединенных знаками арифметических операций или функциями.
Формула должна начинаться со знака =.
Для ввода в ячейку формулы C1+F5 ее следует записать как =C1+F5. Это означает, что к содержимому ячейки C1 будет прибавлено содержимое ячейки F5. Результат будет получен в ячейке, куда занесена формула.
По умолчанию после фиксации формулы в ячейке отображается результат вычислений по заданной формуле.
В Excel допустимы арифметические операторы, операторы отношения:
Таблица 4.1 Арифметические операторы MS Excel
Оператор Значение
- инвертирование (умножение на минус 1),
% операция процента (применяется к отдельному числу), если ввести в ячейку число со знаком %, фактическое его значение будет в 100 раз меньше. Таким образом, вводится процент, а хранится коэффициент.
^ возведение в степень,
*, / умножение, деление,
+, - сложение, вычитание.
Операции выполняются слева направо в порядке их приоритетов, которые могут быть изменены круглыми скобками.
Таблица 4.2 – Операторы отношения MS Excel
Оператор Значение
= равно
больше
= больше или равно
<> не равно
4.2. Оформление таблицы
Объединение и центрирование ячеек
Для объединения ячеек и центрирования содержимого используют кнопку «Объединить и поместить в центре» панели инструментов Форматирование.
Оформление символов
Символы любой ячейки или диапазона можно оформить различными шрифтами, начертанием, высотой и т.д. Для выполнения этих действий необходимо выделить ячейку или диапазон, а затем воспользоваться специальными кнопками из панели инструментов Форматирование. Можно воспользоваться командой меню Формат – Ячейки – Шрифт.
Выравнивание
Содержимое любой ячейки можно выровнять внутри по одному из краев или по центру, как по горизонтали так и по вертикали, а также можно задать необходимую ориентацию текста (снизу вверх, сверху вниз и т.д.). Для задания необходимой ориентации используются специальные кнопки на панели инструментов Форматирование.
Можно воспользоваться командой меню Формат – Ячейки - Выравнивание.
Обрамление
Несмотря на то, что рабочее поле разделено на ячейки, при распечатке документа на принтере сетки таблицы видно не будет. Для того чтобы сетка появилась при распечатке, необходимо обрамить таблицу и ячейки в ней. Для задания обрамления используется кнопка на панели Форматирование. Можно использовать команду меню Формат – Ячейки - Граница.
Фон
Содержимое любой ячейки или диапазона может иметь необходимый фон (тип штриховки, цвет штриховки, цвет фона, а также цвет шрифта).
Для задания обрамления используется кнопка на панели Форматирование.
Можно использовать команду меню Формат – Ячейки – Вид.
4.3. Использование адресации в MS Excel
Относительная адресация
При обращении к ячейке можно использовать, например B3, A1:G9 и т.д. Такая адресация называется относительной. При использовании подобной адресации в формулах, Excel запоминает расположение относительно текущей ячейки. Так, например, когда вводится формула =B1+B2 в ячейку B4, то Excel интерпретирует формулу как «прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки - двумя рядами выше».
Если скопировать формулу =B1+B2 из ячейки В4 в ячейку С4, Excel также интерпретирует формулу как «прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки - двумя рядами выше». Таким образом, формула в ячейке С4 изменит свой вид на =С1+С2. Обратим внимание на то, что при копировании копируются не данные, а формула.
Абсолютная адресация
Иногда при копировании формул необходимо сохранить ссылку на конкретную ячейку или область. Тогда применяется метод абсолютной адресации. Для ее задания необходимо перед буквой колонки и перед номером ряда напечатать символ «$». Например: $B$4 или $C$2:$F$48 и т.д.
Смешанная адресация
Символ «$» ставится только там, где он необходим, например: B$4 или $C2. Тогда при копировании один параметр адреса изменяется, а другой нет.
4.4. Работа с мастером функций
Excel содержит более 400 встроенных функций. Поэтому непосредственного вводить с клавиатуры в формулу названия функций и значения входных параметров не всегда удобно. В Excel есть специальное средство для работы с функциями — Мастер функций. При работе с этим средством сначала предлагается выбрать нужную функцию из списка категорий, а затем в окне диалога предлагается ввести входные значения.
Мастер функций вызывается командой Вставка | Функции или нажатием на кнопку Мастер функций .
4.5. Построение диаграммы
Удобным средством графического представления данных является диаграмма.
Создать диаграмму или график можно с помощью Мастера диаграмм. Это функция Excel, которая с помощью диалоговых окон позволяет получить всю необходимую информацию для построения диаграммы или графика и внедрения его в рабочий лист.
Для оздания диаграммы следует выполнить следующую последовательность действий:
1. Запустить Мастер диаграмм (выполнив команду Вставка – Диаграмма или используя кнопку на панели инструментов).
2. В появившемся диалоговом окне изображены типы диаграмм, которые можно построить посредством MS Excel, из представленного списка нужно выбрать тип диаграммы и нажать кнопку Далее.
3. В следующем окне необходимо указать ячейки, содержимое которых следует представить на диаграмме. Это можно сделать следующими способами:
- набрав интервал вручную в списке Диапазон;
- выделив интервал с помощью мыши.
В этом же окне необходимо указать, где находятся данные - в столбцах или в строках, и указать интервал, содержащий названия рядов данных для легенды. Легенда показывает названия и маркеры данных на диаграмме. Если в легенде не введено никаких названий, то рядом с меткой находится название РЯД1, РЯД2 и т.д. Ввод данных для легенды производят с помощью закладки Ряд.
4. Следующее диалоговое окно предназначено для окончательного оформления вида диаграммы. Здесь можно указать, следует ли добавлять к тексту легенду с названиями и маркерами данных, а также можно ввести названия диаграммы, осей X,Y и т.д. Для этого нужно открыть соответствующую закладку.
5. Предоставляется выбор места расположения построенной диаграммы (на отдельном листе или на имеющемся), после того, как выбор произведён следует нажать кнопку Готово.
Перемещение диаграммы
Для перемещения и изменения размеров диаграммы ее предварительно необходимо выделить.
Чтобы выделить диаграмму, следует поместить на ней указатель мыши и щелкнуть левой кнопкой мыши. Вокруг диаграммы появится тонкая рамка с размерными маркерами. Для изменения размеров диаграммы необходимо переместить размерные маркеры. Перемещение маркера, расположенного на середине стороны, позволяет изменять вертикальные и горизонтальные размеры диаграммы. Перемещение углового маркера позволяет пропорционально изменять размеры диаграммы. Указатель мыши при этом изменяет свою форму на двунаправленную стрелку.
Для перемещения диаграммы необходимо установить указатель мыши на выделенной диаграмме и переместить её на новое место. Указатель мыши при этом не изменяет свою форму.
Редактирование диаграммы
Для редактирования диаграммы ее необходимо выделить. Вокруг диаграммы появится штриховая рамка. В появившемся меню Диаграммы необходимо выбрать тот элемент диаграммы, который необходимо редактировать, это могут быть такие элементы, как:
- Тип диаграммы;
- Исходные данные;
- Параметры диаграммы;
- Размещение.
Аналогичного результата можно добиться, если сначала выделить диаграмму, а затем щелкнуть на ней правой кнопкой мыши (появится контекстно-зависимое меню).
Удаление, копирование диаграмм и их элементов осуществляется обычным способом после их выделения.
4.6. Работа с сериями
В MS Excel разработан механизм ввода рядов данных (серий). Под сериями подразумеваются ряды данных, построенные по определенному закону. При этом данные не обязательно должны быть числовыми, они могут быть и формализованными текстовыми.
Создать серии можно несколькими способами, отдельным подразделом создания серий является Прогрессия.
Для построения произвольных серий следует выполнить команду Правка - Заполнить - Прогрессия. Появится окно диалога Прогрессия, в котором можно установить параметры серий.
В окне можно выбрать тип прогрессии Арифметическую, Геометрическую, Даты и Автозаполнение и как она будет заполняться По строкам или По столбцам.
Для создания прогрессии необходимо ввести в ячейку начальное значение, выбрать Расположение и Тип прогрессии, указать Шаг и Предельное значение и нажать OK.
Ряды дат и времени дня могут использовать приращения по дням, неделям, месяцам, годам. Чтобы получить ряд дат, нужно указать повторяемость последовательности: дни, недели, месяцы или годы.
Автозаполнение. В этом режиме можно продолжать различные типы данных. Возможность Автозаполнения логически продолжает некоторые заданные последовательности. Можно использовать возможность Автозаполнения непосредственно с помощью перемещения курсора заполнения на рабочем листе или с помощью окна диалога Прогрессия.
В поле ввода Предельное Значение можно ввести значение, на котором нужно закончить ряд. Если выделенная область заполняется до достижения этого значения, то построение ряда прекращается. Поле ввода Предельное Значение может оставаться пустым. Тогда заполняется вся выделенная область.
Наиболее интересным параметром в окне диалога Прогрессия является флажок Автоматическое определение шага. Если он установлен, то вычисляется значение шага, опираясь на значения в верхних или левых ячейках выделения, для создания наиболее близкой к ряду прямой (для линейного тренда) или экспоненциальной линии (для экспоненциального тренда). При установке этого флажка значение из поля ввода Шаг игнорируется.
Помимо использования прогрессий серии можно создавать еще несколькими способами без помощи меню, такими способами являются:
1. В ячейку нужно ввести первый член ряда, указатель мыши подвести к черному квадрату в правом нижнем углу выделенной ячейки (в этот момент белый крестик переходит в черный) и нажать левую кнопку мыши. Удерживая нажатой кнопку мыши, выделите нужную часть строки или столбца. Выделенная область заполнится данными.
Такой простой процесс ввода возможен только тогда, когда в ячейку введен один из элементов имеющегося списка. Списки создаются в диалоговом окне Файл – Параметры - Списки.
2. Можно построить серию указав шаг построения. Для этого следует ввести вручную второй член будущего ряда, выделить обе ячейки и затем, воспользовавшись правым нижним углом выделения, продолжить выделение до нужной области. Две первых ячейки, введенные вручную, задают шаг серии данных.
Данный способ является самым удобным для создания простых серий (типа арифметической прогрессии).
3. Третий способ является самым универсальным для создания серий любого цифрового типа арифметической и геометрические прогрессии, степенные ряды и другие сложные серии.
Для этого в первой ячейки серии вводится ее начальное значение, во второй ячейки серии вводится формула определяющая данную серию и нажимается ENTER. Затем, воспользовавшись правым нижним углом выделения, нужно продолжить выделение до нужной области, аналогично первому и второму способу.
4.7. Примеры работы с MS Excel
1. Рассмотрение процедуры построения графика функции
а)Y - cos2 (x) при х [0,1 ].
Для построения графика функции, необходимо сначала построить таб¬лицу ее значений при различных значениях аргумента, причем аргумент изменяется с фиксированным шагом (шаг h = 0,1). Необходимо найти y(0), y(0,1), у(0,2),..., у(1). С этой целью в диапазон ячеек А1:А11 нужно ввести значения переменной х0, 0,1, 0,2 0,3 ... 1. следует отметить, что выбранные значения переменной образуют арифметическую прогрессию.
Затем в ячейку В1 нужно ввести формулу =COS(ПИ( )*А1)^2.
Для того чтобы завершить процесс табулирования функции, необходимо выделить ячейку В1, установить указатель мыши на маркере заполнения и протащить его вниз до ячейки В11.
Для построения графика функций нужно воспользоваться Мастером диаграмм. Далее необходимо выбрать тип диаграммы, затем ввести диапазон ячеек В1:В11, по которому будет строиться график и установить переключатель Ряды в положение В столбцах. Выберать вкладку Ряд и в открывшемся диалоговом окне Источник данных диаграммы в поле Подписи по оси X ввести диапазон А1:А11. Затем
в окне Параметры диаграммы, выбирая нужные вкладки, ввести название диаграммы, подписи по оси X и Y, добавьте легенду и т.д., после чего следует выбрать место расположение диаграммы и нажать кнопку Готово.
б)
x [0,1].
Этот график строится также, как и в вышеописанном примере, за исключением того, что в ячейку В1 вводится формула:
ЕСЛИ(А1 < 0,5; 1 – ABS (0,2-A1)) / (1+A1+A1^2);A1^(1/3))
2. Рассмотрение примера нахождения всей корней уравнения
а) х3 + 0.01x2 - 0,7044х + 0,139104= 0.
У полиномов третей степени имеется не более трех вещественных корней. Для нахождения корней их предварительно нужно локализовать. Для этого следует построить график функции или протабулировать её. Например, полином на отрезке [-1, 1] с шагом 0,2. в ячейку В2 следует ввести формулу:
= А2 ^ З - 0,01 * A2 ^ 2 - 0,7044 * A2 + 0, 139104.
Рис. 4.1 - Локализация корней полинома
Полином меняет знак на интервалах: [-1; -0,8], [0,2; 0,4] и [0,6; 0,8]. Это означает, что на каждом из них имеется корень данного полинома.
Корни полинома можно найти методом последовательных приближений с помощью команды Сервис - Подбор параметра (Tools, Goal Seek). Относительная погрешность вычислений и предельное число итераций задаются на вкладке Вычисления диалогового окна Параметры, открываемого командой Сервис - Параметры
Нужно задать относительную погрешность и предельное число итераций, равным 0.00001 и 1000, соответственно.
В качестве начальных значений приближений к корням следует выбрать любые точки из отрезков локализации корней. Например, их средние точки: -0,9, 03 и 0,7. Ввести их в диапазон ячеек С2:С4. В ячейку D2 ввести формулу
=С2 ^ 3 - 0,01 * С2 ^ 2 - 0,7044 * С2 + 0,139104.
Выделить эту ячейку и с помощью маркера заполнения протащить введенную в нее формулу на диапазон D2:D4. Таким образом, в данном диапазоне будет вычисляться значения полинома для всех начальных значений.
Выбрать команду Сервис - Подбор параметра и заполните диалого¬вое окно Подбора параметра (рис. 4.2).
Рисунок 4.2 - Диалоговое окно «Подбор параметра»
В поле Установить в ячейке ввести D2. В этом поле дается ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения. В поле Значение ввести 0 (в этом поле указывается правая часть уравнения). В поле Изменяя значения ячейки ввести С2 (в этом поле дается ссылка на ячейку, отведенную под переменную).
Вводить ссылки на ячейки удобнее не с клавиатуры, а щелчком на соответствующей ячейке. При этом Excel автоматически будет превращать их в абсолютные ссылки.
После нажатия кнопки ОК средство подбора параметров находит приближенное значение корня, которое помешает в ячейку С2. Для рассмотрен¬ного примера оно равно -0,92034.
Аналогично в ячейках СЗ и С4 нужно найти два оставшихся корня.
3. рассмотрение работы с массивами
В качестве примера простой операции над массивами можно рассмотреть умножение массива А1:B2 на число 5, для этого нужно выделить на рабочем листе область, например, D1 :E2, такого же размера, как и массив – множимое
Затем ввести формулу =A1:B2*5, Для этого следует установить курсор в строке формул и закончить ввод не как обычно нажатием клавиши Enter, а нажатием клавиш Ctrl + Shift + Enter, Таким образом, программе сообщается о том, что необходимо выполнять операцию над массивом. При этом Excel заключит формулу в строке формул в фигурные скобки { = А1:В2*5}
При работе с массивами формула действует на все ячейки диапазона, Нельзя изменять отдельные ячейки в операндах формулы, Аналогично мож¬но вычислить сумму (разность) массивов
Аналогично можно определить массив, каждый элемент которого связан посредством некоторой функции с соответствующим элементом первоначального массива
В Excel имеются специальные встроенные функции для работы с матрицами:
МОБР (MINVERSE) - Обратная матрица
МОПРЕД (MDETERM) - Определитель матрицы
МУМНОЖ (MMULT) - Матричное произведение двух матриц
ТРАНСП (TRANSPOSE) - Транспонированная матрица
Во всех случаях при работе с матрицами перед вводом формулы нужно выделить область на рабочем листе, куда будет выведен результат вычислений,
В качестве примера можно представить систему линейных уравнений с двумя неизвестными, матрица коэффициентов которой записана в ячейки F1:G2, а свободные члены – в ячейки I1:I2. Решение линейной системы АХ = В, где А – матрица коэффициентов, В – столбец (вектор) свободных членов, Х – столбец (вектор) неизвестных, имеет вид Х = А–1В, где А–1 – матрица, обратная по отношению к А, Поэтому для решения системы уравнений нужно выделить под вектор решений диапазон К1:К2 и ввести в него формулу.
{=МУМНОЖ(МОБР(F1:G2);I1:I2)}
Аналогично можно решить систему линейных уравнений А2Х = В, где
Для решения этой системы нужно ввести в диапазон ячеек А1:В2 элементы матрицы А, а в диапазон ячейки D1:D2 – элементы столбца свободных членов В, выбрать диапазон Fl:F2, где будут расположены элементы вектора решения, и ввести следующую формулу:
{= МУМНОЖ(МОБР(МУМНОЖ(А1:В2;А1:B2));D1 :D2)}
Вычисление квадратичной формулы z = XT АХ, где А – квадратная матрица, введенная в диапазон А1:В2, X – вектор, введенный в диапазон D1:D2, а символ (Т) обозначает операцию транспонирования, Для вычисления z следует ввести в ячейку F1 формулу:
{ = МУМНОЖ(МУМНОЖ(ТРАНСП(D1:D2);A1 :B2);D1 :D2)}
Хотя результатом этой формулы является число, не нужно забывать о нажатии клавиш Ctrl + Shift + Enter, Если этого не сделать, в ячейке F1 появится сообщение #ЗНАЧ!
Аналогично вычисляется значение квадратичной формы z = YT АT АY, где
Для решения этой задачи необходимо в диапазон ячеек А1:В2 ввести элементы матрицы А, а в диапазон D1:D2 – элементы столбца Y, Для вычисления квадратичной формы нужно ввести в ячейку F1 формулу:
{ = МУМНОЖ(ТРАНСП(D1:D2);МУМНОЖ(ТРАНСП(A1:B2);МУМН0Ж(A1:B2;D1:D2)))}
В качестве следующего примера работы с массивами можно рассмотреть ре¬шение системы линейных уравнений методом Гаусса,
2х1 + 3x2 + 7х3 + 6х4 = 1,
Зх1, + 5x2 + Зх3 + x4 = 3,
5x1 + 3x2 + x3 + 3x4 = 4,
Зх1 + 3х2 + х3 + 6х4 = 5
В диапазон ячеек E33:E36 введены матрица коэффициентов и столбец свободных членов, соответственно, Содержимое ячеек A33:E33 скопировано в ячейки A38:E38, А43:E43 и A48:E48, В диапазон ячеек А39:Е39 введена формула:
{ = A34:E34 – $A$33:$E$33*(A34/$A$33)},
Которая обращает в нуль коэффициент при xt во втором уравнении системы. Следует выделить диапазон А39:A39 и протянуть маркер выполнения этого диапазона так, чтобы выполнить диапазон А39:E41, Это обратит в нуль коэффициент при х1 в третьем и четвертом уравнениях системы, Скопировать значения из диапазона ячеек А39:Е39 в диапазоны A44:E44 и А49:E49, Для копирования значений без формул можно воспользоваться командой Правка - Специальная вставка (Edit, Special Paste) и в открывшемся диалоговом окне Специальная вставка (Special Paste) в группе Вставить (Paste) установить переключатель в положение Значение (Value)
В диапазон ячеек А45:Е45 ввести формулу:
{ = A40:E40 – A$39:$E$39*(B40/$B$39)},
Выделить диапазон A45:E45 и протянуть маркер заполнения этого диапазона так, чтобы заполнить диапазон A45:E46, Это обратит в нуль коэффициент при x2 в третьем и четвертом уравнениях системы, скопировать значения из диапазона ячеек А45:Е45 в диапазон A50:E50, в диапазон ячеек A51:E51 ввести формулу
{ = A46:E46 – $A$45:$E$45*(С46/$С$45)},
которая обращает в нуль коэффициент при х3 четвертого уравнения системы, Прямая прогонка метода Гаусса завершена, обратная прогонка заключается в вводе в диапазоны G36:K36, G35:K35,G34:K34 и G33:K33, соответственно, следующих формул:
{ = A51:E51/D51)
{ = ( A50 : E50 – G36 : K36 * D50 ) / С50}
{ = (A49:E49 – G36 : K36* D49 – G35 : K35 * С49) / B49}
{ = ( A48 : E48 – G36 : K36 * D48 – G35 : K35* С48 – G34 : K34 * B48) / A48}
В диапазоне ячеек K33:К36 получено решение системы.
Варианты заданий:
Построение графиков
Варианты заданий
Таблица 1. Построить в разных системах координат графики следующих функций:
1.
Нахождение корней уравнения
Найти все корни уравнения Таблица 2
№
1. x3 – 2,56x2 – 1,3251x + 4,395006 = 0
Работа с массивами
3. Варианты заданий
1. Решить системы линейных уравнений AX=B, A2ATX=B и вычислить значение квадратичной формы z= YTA3Y, где
Microsoft Exel 2000 – табличный процессор для представления и обработки данных в виде электронных таблиц, имеющий универсальные возможности решения задач и встроенные средства деловой графики. Microsoft Exel 2000 имеет мощный аппарат математических инструментов для решения задач линейного программирования, оптимизации статистического моделирования и т.п.. Создаваемые файлы имеют расширение XLS
Табличный процессор Excel входит в состав пакета MS Office, предназначен для создания, изменения и анализа документов, представленных в виде таблиц. Областью применения MS Excel может являться делопроизводство, учет, статистика, финансы, инженерия, наука.
При помощи MS Excel можно форматировать и редактировать таблицы; проводить вычисления с использованием сложных формул и сценариев; графически представлять данные в виде диаграмм; сортировать и фильтровать данные; объединять информацию из разных таблиц в сводные таблицы. Помимо вышеперечисленного, Excel, как программный продукт, имеет простую систему настройки и управления, развитую систему контекстных справок, меры защиты от неправильных действий пользователя.
Цель работы:
Целью работы является получение навыков при работе с мастером диаграмм для построения графиков, которые более наглядно представляют данные; нахождение определённого результата для ячейки с помощью подбора значения другой ячейки на примере полиномов различ¬ных степеней; расчеты по формулам, завися¬щим от большого массива данных.
3. Краткие теоретические сведения.
3.1. Основные теоретические сведения.
Типовая структура интерфейса MS Excel (рис. 3.1) включает ряд элементов.
Рисунок 3.1 – Типовая структура интерфейса MS Excel.
Заголовок окна - это поле, которое содержит название приложения и обрабатываемого в нем документа, а также кнопки управления размером и положением окна.
Главное меню системы – это меню, через которое доступны все функции MS Excel.
Для удобства многие средства управления дублируются в форме пиктограмм, содержащихся на панелях инструментов.
Строка формул - это элемент, который представляет формулы.
Поле имени – поле, служащее для выполнения следующих функций:
1) в обычном состоянии отображает адрес текущей ячейки рабочего листа;
2) во время выделения диапазона в поле отображает число включенных строк и столбцов;
3) в этом поле можно непосредственно ввести адрес нужной клетки, тогда, после нажатия клавиши Enter, активным окажется указанное место листа;
4) в режиме редактирования формул появляется меню функций Excel, из которого можно выбрать нужную.
Ниже строки формул находится рабочее пространство Excel, называемое рабочим листом.
Под пространством рабочего листа отображены вкладки листов, позволяющие быстро вызвать любой из имеющихся в рабочей книге листов, щелкнув по нему мышью. Здесь же имеются четыре кнопки-треугольника перемещения по листам. Они используются при необходимости доступа к листам, вкладки которых не уместились в строке.
Самая нижняя строка интерфейса - строка состояния, в этой строке отображается текущий режим таблицы, а также даются подсказки по возможным действиям. В левой части строки состояния указывается текущий режим работы таблицы:
1) Готово - в этом состоянии возможно перемещение в таблице и доступ к меню пиктограмм.
2) Ввод - режим ввода данных в клетку.
3) Правка - похож на предыдущий, режим редактирования уже введенных данных.
4) Указать - режим выбора объекта (клетки, области) для включения его в выражение. Выделяемый объект ограничивается бегущей пунктирной линией.
В правой части строки состояния имеется поле, где можно отобразить сумму, максимум, минимум, среднее, количество значений и количество ячеек любой выделенной области таблицы. Отображаемую функцию можно задать, щелкнув правой кнопкой мыши в строке состояния.
3.2. Организация данных в Excel
Книга - самая крупная единица данных в Excel, являющаяся синонимом файла Excel. Все такие файлы имеют расширение .xls. Книге можно дать любое имя, допустимое в среде Windows. По умолчанию они получают имена Книга1.xls, Книга2.xls. Число книг ограничено только дисковым пространством компьютера. Книга содержит рабочие листы.
Рабочий лист образует рабочее пространство пользователя. Книга состоит из нескольких рабочих листов (до 255). При открытии рабочей книги на экране виден только один лист - верхний. Нижняя часть листа содержит ярлычки других листов. Щелкая мышью на ярлычках листов, можно перейти к другому листу.
Листам можно давать произвольные имена длиной до 31-го символа, исключая знаки, такие, как: * : / \ ? [ ], по умолчанию они получают имена вида Лист1, Лист2.
Для изменения имени листа можно воспользоваться контекстным меню. Для этого нажмите правой кнопкой мыши на названии листа и выберите пункт меню «Переименовать».
Рабочий лист электронной таблицы состоит из строк и столбцов. Каждое пересечение столбца и строки образует ячейку, в которую можно вводить данные (текст, число, формулу).
Номер строки - определяет ряд в электронной таблице. Он обозначен на левой границе рабочего поля. Всего на листе 65536 строк.
Буква столбца - определяет колонку в электронной таблице. Буквы находятся на верхней границе рабочего поля. Колонки нумеруются в следующем порядке: A - Z, затем AA - AZ, затем BA - BZ и т.д.. Всего на листе 256 столбцов.
Ячейка - первичный элемент таблицы, содержащий данные. Каждая ячейка имеет уникальный адрес, состоящий из буквы столбца и номера строки. Например, адрес B3 определяет ячейку на пересечении столбца B и строки номер 3. Всего на листе 256ґ65536=16 777 21 ячеек.
Указатель ячейки - черный светящийся прямоугольник, определяющий текущую ячейку. Указатель можно перемещать по таблице, как при помощи клавиатуры, так и мышью.
Текущая ячейка помечена указателем. Ввод данных и некоторые другие действия по умолчанию относятся к текущей ячейке.
Диапазон представляет собой прямоугольную область смежных ячеек. Диапазон может состоять из одной или нескольких ячеек, строк или столбцов. Адрес диапазона состоит из координат противоположных углов, разделенных двоеточием. Например, B13:C19, A12:D27 или D:F.
3.3. Типы данных MS Excel
В программе Excel существует несколько типов данных:
1. текстовый;
2. графический;
3. числовой:
- числовой (целые числа и числа с дробной частью);
- денежный (после числа указывается обозначение валюты);
- процентный (20% = 0,02);
- дата и время (число дней начиная от 01.01.1900г – это целая часть, время – дробная часть).
3.4. Функции
Функции в Excel используются для выполнения стандартных вычислений в рабочих книгах. Значения, которые используются для вычисления функций, называются аргументами. Значения, возвращаемые функциями в качестве ответа, называются результатами. Помимо встроенных функций можно использовать в вычислениях пользовательские функции, которые создаются при помощи средств Excel.
Чтобы использовать функцию, нужно ввести ее как часть формулы в ячейку рабочего листа. Последовательность, в которой должны располагаться используемые в формуле символы, называется синтаксисом функции. Все функции используют одинаковые основные правила синтаксиса. Если нарушить правила синтаксиса, Excel выдаст сообщение о том, что в формуле имеется ошибка.
Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле.
Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой «;». Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы.
В качестве аргументов можно использовать числа, текст, логические значения, массивы, значения ошибок или ссылки. Аргументы могут быть как константами, так и формулами. В свою очередь эти формулы могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Excel можно использовать до семи уровней вложенности функций.
Задаваемые входные параметры должны иметь допустимые для данного аргумента значения. Некоторые функции могут иметь необязательные аргументы, которые могут отсутствовать при вычислении значения функции.
3.5. Типы функций
Для удобства работы функции в Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок. Кроме того, присутствуют следующие категории функций: статистические, текстовые и математические.
При помощи текстовых функций имеется возможность обрабатывать текст: извлекать символы, находить нужные, записывать символы в строго определенное место текста и многое другое.
С помощью функций даты и времени можно решить практически любые задачи, связанные с учетом даты или времени (например, определить возраст, вычислить стаж работы, определить число рабочих дней на любом промежутке времени).
Логические функции помогают создавать сложные формулы, которые в зависимости от выполнения тех или иных условий будут совершать различные виды обработки данных.
В Excel широко представлены математические функции. Например, можно выполнять различные операции с матрицами: умножать, находить обратную матрицу, транспонировать.
С помощью статистических функций возможно проводить статистическое моделирование.
4. Методика выполнения лабораторной работы
4.1. Основные приемы заполнения таблиц
Содержимое ячеек
Для ввода данных необходимо переместиться в нужную ячейку и набрать данные, а затем нажать
Excel определяет, являются ли вводимые данные текстом, числом или формулой по первому символу. Если первый символ буква, то считается, что вводится текст, если первый символ цифра или знак =, то считается, что вводится число или формула.
Данные, которые вводятся, отображаются в ячейке и в строке формул и только при нажатии
Ввод текста
Текст - это набор любых символов. Если ширина текста больше ширины ячейки и ячейка справа от заполняемой пуста, то текст на экране займет и ее место. При вводе данных в соседнюю ячейку предыдущий текст будет обрезан (но при этом в ячейке он будет сохранен полностью).
По умолчанию после фиксации текста в ячейке он будет прижат к левому краю.
Текст может автоматически переноситься на следующую строку внутри ячейки. Для этого следует в меню Формат - Ячейка - Выравнивание, установить Переносить по словам.
Ввод чисел
Числа в ячейку можно вводить со знаками =, -, + или без них. Если ширина введенного числа больше, чем ширина ячейки на экране, то Excel изображает его в экспоненциальной форме либо вместо числа ставит символы #### (при этом число в ячейке будет сохранено полностью).
Экспоненциальная форма используется для представления очень маленьких либо очень больших чисел. Число 501000000 будет записано как 5,01Е+08, что означает 5,01*10^8. Число 0,000000005 будет представлено как 5Е-9, что означает 5*10^-9.
Для ввода дробных чисел используется десятичная запятая или точка в зависимости от настройки.
Любой символ перед числом или в середине числа превращает его в текст.
По умолчанию после фиксации числа в ячейке Excel сдвигает его к правой границе ячейки.
Ввод даты и времени
При вводе даты или времени Excel преобразует их в специальное число (поэтому дата и время после фиксации в ячейке прижаты к правому краю ячейки)..
Введенная дата может быть представлена в одном из нижеперечисленных форматов.
3/21/99; 21-Мар-99; 21-Мар; Мар-21
После фиксации даты в ячейке в качестве разделителя между днем, месяцем и годом устанавливается точка. Например: 3.21.99, 12.Мар.99.
Введенное время может иметь следующие форматы: 14:25; 14:25:09; 2:25 PM; 2:25:09 PM
Ввод формул
В виде формулы может быть записано арифметическое выражение. Оно представляет собой последовательность чисел или ссылок на ячейки, объединенных знаками арифметических операций или функциями.
Формула должна начинаться со знака =.
Для ввода в ячейку формулы C1+F5 ее следует записать как =C1+F5. Это означает, что к содержимому ячейки C1 будет прибавлено содержимое ячейки F5. Результат будет получен в ячейке, куда занесена формула.
По умолчанию после фиксации формулы в ячейке отображается результат вычислений по заданной формуле.
В Excel допустимы арифметические операторы, операторы отношения:
Таблица 4.1 Арифметические операторы MS Excel
Оператор Значение
- инвертирование (умножение на минус 1),
% операция процента (применяется к отдельному числу), если ввести в ячейку число со знаком %, фактическое его значение будет в 100 раз меньше. Таким образом, вводится процент, а хранится коэффициент.
^ возведение в степень,
*, / умножение, деление,
+, - сложение, вычитание.
Операции выполняются слева направо в порядке их приоритетов, которые могут быть изменены круглыми скобками.
Таблица 4.2 – Операторы отношения MS Excel
Оператор Значение
= равно
больше
= больше или равно
<> не равно
4.2. Оформление таблицы
Объединение и центрирование ячеек
Для объединения ячеек и центрирования содержимого используют кнопку «Объединить и поместить в центре» панели инструментов Форматирование.
Оформление символов
Символы любой ячейки или диапазона можно оформить различными шрифтами, начертанием, высотой и т.д. Для выполнения этих действий необходимо выделить ячейку или диапазон, а затем воспользоваться специальными кнопками из панели инструментов Форматирование. Можно воспользоваться командой меню Формат – Ячейки – Шрифт.
Выравнивание
Содержимое любой ячейки можно выровнять внутри по одному из краев или по центру, как по горизонтали так и по вертикали, а также можно задать необходимую ориентацию текста (снизу вверх, сверху вниз и т.д.). Для задания необходимой ориентации используются специальные кнопки на панели инструментов Форматирование.
Можно воспользоваться командой меню Формат – Ячейки - Выравнивание.
Обрамление
Несмотря на то, что рабочее поле разделено на ячейки, при распечатке документа на принтере сетки таблицы видно не будет. Для того чтобы сетка появилась при распечатке, необходимо обрамить таблицу и ячейки в ней. Для задания обрамления используется кнопка на панели Форматирование. Можно использовать команду меню Формат – Ячейки - Граница.
Фон
Содержимое любой ячейки или диапазона может иметь необходимый фон (тип штриховки, цвет штриховки, цвет фона, а также цвет шрифта).
Для задания обрамления используется кнопка на панели Форматирование.
Можно использовать команду меню Формат – Ячейки – Вид.
4.3. Использование адресации в MS Excel
Относительная адресация
При обращении к ячейке можно использовать, например B3, A1:G9 и т.д. Такая адресация называется относительной. При использовании подобной адресации в формулах, Excel запоминает расположение относительно текущей ячейки. Так, например, когда вводится формула =B1+B2 в ячейку B4, то Excel интерпретирует формулу как «прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки - двумя рядами выше».
Если скопировать формулу =B1+B2 из ячейки В4 в ячейку С4, Excel также интерпретирует формулу как «прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки - двумя рядами выше». Таким образом, формула в ячейке С4 изменит свой вид на =С1+С2. Обратим внимание на то, что при копировании копируются не данные, а формула.
Абсолютная адресация
Иногда при копировании формул необходимо сохранить ссылку на конкретную ячейку или область. Тогда применяется метод абсолютной адресации. Для ее задания необходимо перед буквой колонки и перед номером ряда напечатать символ «$». Например: $B$4 или $C$2:$F$48 и т.д.
Смешанная адресация
Символ «$» ставится только там, где он необходим, например: B$4 или $C2. Тогда при копировании один параметр адреса изменяется, а другой нет.
4.4. Работа с мастером функций
Excel содержит более 400 встроенных функций. Поэтому непосредственного вводить с клавиатуры в формулу названия функций и значения входных параметров не всегда удобно. В Excel есть специальное средство для работы с функциями — Мастер функций. При работе с этим средством сначала предлагается выбрать нужную функцию из списка категорий, а затем в окне диалога предлагается ввести входные значения.
Мастер функций вызывается командой Вставка | Функции или нажатием на кнопку Мастер функций .
4.5. Построение диаграммы
Удобным средством графического представления данных является диаграмма.
Создать диаграмму или график можно с помощью Мастера диаграмм. Это функция Excel, которая с помощью диалоговых окон позволяет получить всю необходимую информацию для построения диаграммы или графика и внедрения его в рабочий лист.
Для оздания диаграммы следует выполнить следующую последовательность действий:
1. Запустить Мастер диаграмм (выполнив команду Вставка – Диаграмма или используя кнопку на панели инструментов).
2. В появившемся диалоговом окне изображены типы диаграмм, которые можно построить посредством MS Excel, из представленного списка нужно выбрать тип диаграммы и нажать кнопку Далее.
3. В следующем окне необходимо указать ячейки, содержимое которых следует представить на диаграмме. Это можно сделать следующими способами:
- набрав интервал вручную в списке Диапазон;
- выделив интервал с помощью мыши.
В этом же окне необходимо указать, где находятся данные - в столбцах или в строках, и указать интервал, содержащий названия рядов данных для легенды. Легенда показывает названия и маркеры данных на диаграмме. Если в легенде не введено никаких названий, то рядом с меткой находится название РЯД1, РЯД2 и т.д. Ввод данных для легенды производят с помощью закладки Ряд.
4. Следующее диалоговое окно предназначено для окончательного оформления вида диаграммы. Здесь можно указать, следует ли добавлять к тексту легенду с названиями и маркерами данных, а также можно ввести названия диаграммы, осей X,Y и т.д. Для этого нужно открыть соответствующую закладку.
5. Предоставляется выбор места расположения построенной диаграммы (на отдельном листе или на имеющемся), после того, как выбор произведён следует нажать кнопку Готово.
Перемещение диаграммы
Для перемещения и изменения размеров диаграммы ее предварительно необходимо выделить.
Чтобы выделить диаграмму, следует поместить на ней указатель мыши и щелкнуть левой кнопкой мыши. Вокруг диаграммы появится тонкая рамка с размерными маркерами. Для изменения размеров диаграммы необходимо переместить размерные маркеры. Перемещение маркера, расположенного на середине стороны, позволяет изменять вертикальные и горизонтальные размеры диаграммы. Перемещение углового маркера позволяет пропорционально изменять размеры диаграммы. Указатель мыши при этом изменяет свою форму на двунаправленную стрелку.
Для перемещения диаграммы необходимо установить указатель мыши на выделенной диаграмме и переместить её на новое место. Указатель мыши при этом не изменяет свою форму.
Редактирование диаграммы
Для редактирования диаграммы ее необходимо выделить. Вокруг диаграммы появится штриховая рамка. В появившемся меню Диаграммы необходимо выбрать тот элемент диаграммы, который необходимо редактировать, это могут быть такие элементы, как:
- Тип диаграммы;
- Исходные данные;
- Параметры диаграммы;
- Размещение.
Аналогичного результата можно добиться, если сначала выделить диаграмму, а затем щелкнуть на ней правой кнопкой мыши (появится контекстно-зависимое меню).
Удаление, копирование диаграмм и их элементов осуществляется обычным способом после их выделения.
4.6. Работа с сериями
В MS Excel разработан механизм ввода рядов данных (серий). Под сериями подразумеваются ряды данных, построенные по определенному закону. При этом данные не обязательно должны быть числовыми, они могут быть и формализованными текстовыми.
Создать серии можно несколькими способами, отдельным подразделом создания серий является Прогрессия.
Для построения произвольных серий следует выполнить команду Правка - Заполнить - Прогрессия. Появится окно диалога Прогрессия, в котором можно установить параметры серий.
В окне можно выбрать тип прогрессии Арифметическую, Геометрическую, Даты и Автозаполнение и как она будет заполняться По строкам или По столбцам.
Для создания прогрессии необходимо ввести в ячейку начальное значение, выбрать Расположение и Тип прогрессии, указать Шаг и Предельное значение и нажать OK.
Ряды дат и времени дня могут использовать приращения по дням, неделям, месяцам, годам. Чтобы получить ряд дат, нужно указать повторяемость последовательности: дни, недели, месяцы или годы.
Автозаполнение. В этом режиме можно продолжать различные типы данных. Возможность Автозаполнения логически продолжает некоторые заданные последовательности. Можно использовать возможность Автозаполнения непосредственно с помощью перемещения курсора заполнения на рабочем листе или с помощью окна диалога Прогрессия.
В поле ввода Предельное Значение можно ввести значение, на котором нужно закончить ряд. Если выделенная область заполняется до достижения этого значения, то построение ряда прекращается. Поле ввода Предельное Значение может оставаться пустым. Тогда заполняется вся выделенная область.
Наиболее интересным параметром в окне диалога Прогрессия является флажок Автоматическое определение шага. Если он установлен, то вычисляется значение шага, опираясь на значения в верхних или левых ячейках выделения, для создания наиболее близкой к ряду прямой (для линейного тренда) или экспоненциальной линии (для экспоненциального тренда). При установке этого флажка значение из поля ввода Шаг игнорируется.
Помимо использования прогрессий серии можно создавать еще несколькими способами без помощи меню, такими способами являются:
1. В ячейку нужно ввести первый член ряда, указатель мыши подвести к черному квадрату в правом нижнем углу выделенной ячейки (в этот момент белый крестик переходит в черный) и нажать левую кнопку мыши. Удерживая нажатой кнопку мыши, выделите нужную часть строки или столбца. Выделенная область заполнится данными.
Такой простой процесс ввода возможен только тогда, когда в ячейку введен один из элементов имеющегося списка. Списки создаются в диалоговом окне Файл – Параметры - Списки.
2. Можно построить серию указав шаг построения. Для этого следует ввести вручную второй член будущего ряда, выделить обе ячейки и затем, воспользовавшись правым нижним углом выделения, продолжить выделение до нужной области. Две первых ячейки, введенные вручную, задают шаг серии данных.
Данный способ является самым удобным для создания простых серий (типа арифметической прогрессии).
3. Третий способ является самым универсальным для создания серий любого цифрового типа арифметической и геометрические прогрессии, степенные ряды и другие сложные серии.
Для этого в первой ячейки серии вводится ее начальное значение, во второй ячейки серии вводится формула определяющая данную серию и нажимается ENTER. Затем, воспользовавшись правым нижним углом выделения, нужно продолжить выделение до нужной области, аналогично первому и второму способу.
4.7. Примеры работы с MS Excel
1. Рассмотрение процедуры построения графика функции
а)Y - cos2 (x) при х [0,1 ].
Для построения графика функции, необходимо сначала построить таб¬лицу ее значений при различных значениях аргумента, причем аргумент изменяется с фиксированным шагом (шаг h = 0,1). Необходимо найти y(0), y(0,1), у(0,2),..., у(1). С этой целью в диапазон ячеек А1:А11 нужно ввести значения переменной х0, 0,1, 0,2 0,3 ... 1. следует отметить, что выбранные значения переменной образуют арифметическую прогрессию.
Затем в ячейку В1 нужно ввести формулу =COS(ПИ( )*А1)^2.
Для того чтобы завершить процесс табулирования функции, необходимо выделить ячейку В1, установить указатель мыши на маркере заполнения и протащить его вниз до ячейки В11.
Для построения графика функций нужно воспользоваться Мастером диаграмм. Далее необходимо выбрать тип диаграммы, затем ввести диапазон ячеек В1:В11, по которому будет строиться график и установить переключатель Ряды в положение В столбцах. Выберать вкладку Ряд и в открывшемся диалоговом окне Источник данных диаграммы в поле Подписи по оси X ввести диапазон А1:А11. Затем
в окне Параметры диаграммы, выбирая нужные вкладки, ввести название диаграммы, подписи по оси X и Y, добавьте легенду и т.д., после чего следует выбрать место расположение диаграммы и нажать кнопку Готово.
б)
x [0,1].
Этот график строится также, как и в вышеописанном примере, за исключением того, что в ячейку В1 вводится формула:
ЕСЛИ(А1 < 0,5; 1 – ABS (0,2-A1)) / (1+A1+A1^2);A1^(1/3))
2. Рассмотрение примера нахождения всей корней уравнения
а) х3 + 0.01x2 - 0,7044х + 0,139104= 0.
У полиномов третей степени имеется не более трех вещественных корней. Для нахождения корней их предварительно нужно локализовать. Для этого следует построить график функции или протабулировать её. Например, полином на отрезке [-1, 1] с шагом 0,2. в ячейку В2 следует ввести формулу:
= А2 ^ З - 0,01 * A2 ^ 2 - 0,7044 * A2 + 0, 139104.
Рис. 4.1 - Локализация корней полинома
Полином меняет знак на интервалах: [-1; -0,8], [0,2; 0,4] и [0,6; 0,8]. Это означает, что на каждом из них имеется корень данного полинома.
Корни полинома можно найти методом последовательных приближений с помощью команды Сервис - Подбор параметра (Tools, Goal Seek). Относительная погрешность вычислений и предельное число итераций задаются на вкладке Вычисления диалогового окна Параметры, открываемого командой Сервис - Параметры
Нужно задать относительную погрешность и предельное число итераций, равным 0.00001 и 1000, соответственно.
В качестве начальных значений приближений к корням следует выбрать любые точки из отрезков локализации корней. Например, их средние точки: -0,9, 03 и 0,7. Ввести их в диапазон ячеек С2:С4. В ячейку D2 ввести формулу
=С2 ^ 3 - 0,01 * С2 ^ 2 - 0,7044 * С2 + 0,139104.
Выделить эту ячейку и с помощью маркера заполнения протащить введенную в нее формулу на диапазон D2:D4. Таким образом, в данном диапазоне будет вычисляться значения полинома для всех начальных значений.
Выбрать команду Сервис - Подбор параметра и заполните диалого¬вое окно Подбора параметра (рис. 4.2).
Рисунок 4.2 - Диалоговое окно «Подбор параметра»
В поле Установить в ячейке ввести D2. В этом поле дается ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения. В поле Значение ввести 0 (в этом поле указывается правая часть уравнения). В поле Изменяя значения ячейки ввести С2 (в этом поле дается ссылка на ячейку, отведенную под переменную).
Вводить ссылки на ячейки удобнее не с клавиатуры, а щелчком на соответствующей ячейке. При этом Excel автоматически будет превращать их в абсолютные ссылки.
После нажатия кнопки ОК средство подбора параметров находит приближенное значение корня, которое помешает в ячейку С2. Для рассмотрен¬ного примера оно равно -0,92034.
Аналогично в ячейках СЗ и С4 нужно найти два оставшихся корня.
3. рассмотрение работы с массивами
В качестве примера простой операции над массивами можно рассмотреть умножение массива А1:B2 на число 5, для этого нужно выделить на рабочем листе область, например, D1 :E2, такого же размера, как и массив – множимое
Затем ввести формулу =A1:B2*5, Для этого следует установить курсор в строке формул и закончить ввод не как обычно нажатием клавиши Enter, а нажатием клавиш Ctrl + Shift + Enter, Таким образом, программе сообщается о том, что необходимо выполнять операцию над массивом. При этом Excel заключит формулу в строке формул в фигурные скобки { = А1:В2*5}
При работе с массивами формула действует на все ячейки диапазона, Нельзя изменять отдельные ячейки в операндах формулы, Аналогично мож¬но вычислить сумму (разность) массивов
Аналогично можно определить массив, каждый элемент которого связан посредством некоторой функции с соответствующим элементом первоначального массива
В Excel имеются специальные встроенные функции для работы с матрицами:
МОБР (MINVERSE) - Обратная матрица
МОПРЕД (MDETERM) - Определитель матрицы
МУМНОЖ (MMULT) - Матричное произведение двух матриц
ТРАНСП (TRANSPOSE) - Транспонированная матрица
Во всех случаях при работе с матрицами перед вводом формулы нужно выделить область на рабочем листе, куда будет выведен результат вычислений,
В качестве примера можно представить систему линейных уравнений с двумя неизвестными, матрица коэффициентов которой записана в ячейки F1:G2, а свободные члены – в ячейки I1:I2. Решение линейной системы АХ = В, где А – матрица коэффициентов, В – столбец (вектор) свободных членов, Х – столбец (вектор) неизвестных, имеет вид Х = А–1В, где А–1 – матрица, обратная по отношению к А, Поэтому для решения системы уравнений нужно выделить под вектор решений диапазон К1:К2 и ввести в него формулу.
{=МУМНОЖ(МОБР(F1:G2);I1:I2)}
Аналогично можно решить систему линейных уравнений А2Х = В, где
Для решения этой системы нужно ввести в диапазон ячеек А1:В2 элементы матрицы А, а в диапазон ячейки D1:D2 – элементы столбца свободных членов В, выбрать диапазон Fl:F2, где будут расположены элементы вектора решения, и ввести следующую формулу:
{= МУМНОЖ(МОБР(МУМНОЖ(А1:В2;А1:B2));D1 :D2)}
Вычисление квадратичной формулы z = XT АХ, где А – квадратная матрица, введенная в диапазон А1:В2, X – вектор, введенный в диапазон D1:D2, а символ (Т) обозначает операцию транспонирования, Для вычисления z следует ввести в ячейку F1 формулу:
{ = МУМНОЖ(МУМНОЖ(ТРАНСП(D1:D2);A1 :B2);D1 :D2)}
Хотя результатом этой формулы является число, не нужно забывать о нажатии клавиш Ctrl + Shift + Enter, Если этого не сделать, в ячейке F1 появится сообщение #ЗНАЧ!
Аналогично вычисляется значение квадратичной формы z = YT АT АY, где
Для решения этой задачи необходимо в диапазон ячеек А1:В2 ввести элементы матрицы А, а в диапазон D1:D2 – элементы столбца Y, Для вычисления квадратичной формы нужно ввести в ячейку F1 формулу:
{ = МУМНОЖ(ТРАНСП(D1:D2);МУМНОЖ(ТРАНСП(A1:B2);МУМН0Ж(A1:B2;D1:D2)))}
В качестве следующего примера работы с массивами можно рассмотреть ре¬шение системы линейных уравнений методом Гаусса,
2х1 + 3x2 + 7х3 + 6х4 = 1,
Зх1, + 5x2 + Зх3 + x4 = 3,
5x1 + 3x2 + x3 + 3x4 = 4,
Зх1 + 3х2 + х3 + 6х4 = 5
В диапазон ячеек E33:E36 введены матрица коэффициентов и столбец свободных членов, соответственно, Содержимое ячеек A33:E33 скопировано в ячейки A38:E38, А43:E43 и A48:E48, В диапазон ячеек А39:Е39 введена формула:
{ = A34:E34 – $A$33:$E$33*(A34/$A$33)},
Которая обращает в нуль коэффициент при xt во втором уравнении системы. Следует выделить диапазон А39:A39 и протянуть маркер выполнения этого диапазона так, чтобы выполнить диапазон А39:E41, Это обратит в нуль коэффициент при х1 в третьем и четвертом уравнениях системы, Скопировать значения из диапазона ячеек А39:Е39 в диапазоны A44:E44 и А49:E49, Для копирования значений без формул можно воспользоваться командой Правка - Специальная вставка (Edit, Special Paste) и в открывшемся диалоговом окне Специальная вставка (Special Paste) в группе Вставить (Paste) установить переключатель в положение Значение (Value)
В диапазон ячеек А45:Е45 ввести формулу:
{ = A40:E40 – A$39:$E$39*(B40/$B$39)},
Выделить диапазон A45:E45 и протянуть маркер заполнения этого диапазона так, чтобы заполнить диапазон A45:E46, Это обратит в нуль коэффициент при x2 в третьем и четвертом уравнениях системы, скопировать значения из диапазона ячеек А45:Е45 в диапазон A50:E50, в диапазон ячеек A51:E51 ввести формулу
{ = A46:E46 – $A$45:$E$45*(С46/$С$45)},
которая обращает в нуль коэффициент при х3 четвертого уравнения системы, Прямая прогонка метода Гаусса завершена, обратная прогонка заключается в вводе в диапазоны G36:K36, G35:K35,G34:K34 и G33:K33, соответственно, следующих формул:
{ = A51:E51/D51)
{ = ( A50 : E50 – G36 : K36 * D50 ) / С50}
{ = (A49:E49 – G36 : K36* D49 – G35 : K35 * С49) / B49}
{ = ( A48 : E48 – G36 : K36 * D48 – G35 : K35* С48 – G34 : K34 * B48) / A48}
В диапазоне ячеек K33:К36 получено решение системы.
Варианты заданий:
Построение графиков
Варианты заданий
Таблица 1. Построить в разных системах координат графики следующих функций:
1.
Нахождение корней уравнения
Найти все корни уравнения Таблица 2
№
1. x3 – 2,56x2 – 1,3251x + 4,395006 = 0
Работа с массивами
3. Варианты заданий
1. Решить системы линейных уравнений AX=B, A2ATX=B и вычислить значение квадратичной формы z= YTA3Y, где
Не Пропустите:
- Тест по электронной таблице
- Лабораторная работа № 2. 1. СОЗДАНИЕ ТАБЛИЦЫ С РАСЧЕТНЫМИ ФОРМУЛАМИ
- ОТЧЕТ по лабораторной работе №1 по дисциплине «Введение в АСОИУ СН»
- ОТЧЕТ по практике по профилю специальности
- ЛАБОРАТОРНАЯ РАБОТА №3 МЕТОДЫ БЕЗУСЛОВНОЙ ОПТИМИЗАЦИИ Цель работы: изучение методов безусловной оптимизации