Практические аспекты освоения офисных программ MS Word и MS Excel

Размещено на http://www.allbest.ru/

Введение

Методические указания по выполнению лабораторных работ по дисциплине "Информатика" ориентированы, прежде всего, на студентов 1 курса, у которых слабая школьная подготовка по основам информатики. Они охватывают ряд вопросов первого семестра, которые вызывают у неподготовленных студентов некоторые сложности, связанные с освоением офисных программ MS WORD и MS EXCEL.

Указания содержат:

· основные теоретические сведения;

· перечень обязательных заданий для выполнения лабораторных работ;

· указания к выполнению лабораторных заданий;

· контрольные вопросы.

В методических указаниях приведены примеры работы с таблицами и редактором формул в MS WORD, а также с такими средствами как Подбор параметра, Поиск решения в MS EXCEL, которые применяются для решения многих оптимизационных задач. Рассмотрены возможности EXCEL при управлении плоскими базами данных. Освоение навыков работы со списками в MS EXCEL помогут студентам лучше ориентироваться при разработке реляционных баз данных и СУБД. Кроме того, приведены методические указания для работы со стандартными функциями в EXCEL: математическими, логическими, некоторыми статистическими, применяемыми при прогнозировании временных рядов (ПРЕДСКАЗ, РОСТ и ТЕНДЕНЦИЯ).

Предполагается, что студенты имеют элементарные навыки работы с компьютером.

Методические указания помогут студентам не только освоить некоторые вопросы, касающиеся MS WORD и MS EXCEL, но и развить навыки умения работать с прикладными программными средствами.

Кроме того, в зависимости от поставленных специальных задач в будущем специалист сам принимает решение о том, воспользоваться ли существующей программой или написать свой код. А для этого он должен знать возможности существующих программ. Более того, знакомство со стандартным пользовательским интерфейсом программ популярной в мире фирмы MICROSOFT будет полезным при разработке интерфейсов собственных приложений.

Методические указания могут быть полезны также студентам дистантной формы обучения при начальном освоении дисциплины "Информатика".

Лабораторная работа №1. Создание и редактирование таблиц

Цель работы: Освоение навыков построения и редактирования таблиц в текстовом редакторе WORD.

1. Теоретические сведения. Для упорядочения числовых и текстовых данных очень часто используются таблицы. Таблица состоит из строк и столбцов ячеек, которые могут содержать текст и рисунки. Таблицы также можно применять для разбиения текста на несколько колонок.

Microsoft Word предлагает несколько способов создания таблицы. Выбор наилучшего способа создания таблицы зависит от ее сложности и требований пользователя.

2. Задание к работе. Нарисовать таблицу следующего вида:

Таблица 1.1

Прибыль	I=1	I=2	I=3
	J=1	J=2	J=3	J=1	J=2	J=3	J=1	J=2	J=3
	11	8	99	5	3	2	1	8	3

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

· Выберите место создания таблицы.

· Для вставки пустой таблицы в позицию курсора воспользуйтесь командой ТаблицаДобавить таблицу.

· В диалоговом окне установите число столбцов равным 10, число строк - 3. Затем нажмите кнопку ОК.

· Объедините ячейки первого столбца в одну. Для этого выделите их и воспользуйтесь командой ТаблицаОбъединить ячейки.

· Аналогично объедините по 3 ячейки в первой строке.

· Отформатируйте таблицу. Для этого установите панель инструментов "Таблицы и границы", используя команду Вид. Выделите всю таблицу и воспользуйтесь кнопкой выравнивания текста в ячейке.

· С помощью команды ФайлПредварительный просмотр посмотрите, как будет выглядеть таблица в отпечатанном документе.

4. Лабораторные задания

4.1. Набрать следующую таблицу:

Таблица 1.2

Группы оборудования	Нормативные коэффициенты	Полезное время работы оборудования
	I=1	I=2	I=3
	J=1	J=2	J=3	J=1	J=2	J=3	J=1	J=2	J=3
R=1	2	4	3	5	6	7	2	1	2	20

Добавить в таблицу следующие строки:

R=2	2	9	2	5	2	3	3	2	9	23
R=3	5	4	1	6	6	6	6	1	5	45

4.2. Набрать таблицу

Таблица 1.3

№	Код	Направление	Срок обучения	План приема	Подано заявлений
			Очн.	Заочн.	Очн.	Заочн.	Очн.	Заочн.
1	55290	Автоматизация и управления	5	6	27	10	21	6
2	55020	Прикладная механика	5	6	7	5	14	2
3	55030	Полиграфия	5	6	10	6	9	4
4	55020	Экономика	5	6	50	5	48	5

Отсортируйте таблицу 1.3 в возрастающем порядке по столбцу Код.

Примечание. Для проведения сортировки содержимого таблицы проделайте следующие действия:

· Выделите строки таблицы, которые требуется отсортировать:

1	55290	Автоматизация и управления	5	6	27	10	21	6
2	55020	Прикладная механика	5	6	7	5	14	2
3	55030	Полиграфия	5	6	10	6	9	4
4	55020	Экономика	5	6	50	5	48	5

· Выберите команду Сортировка в меню Таблица.

· В окне "Сортировка" выберите нужные параметры сортировки:

· В окне раскрывающегося списка Сначала по выберите "столбцам 2", поскольку значения кодов находятся во втором столбце.

· Нажмите на кнопку "Оk".

5. Контрольные вопросы:

5.1. Какими способами можно создать таблицу?

5.2. Как можно объединить ячейки?

5.3. Как можно разделить ячейки?

5.4. Как форматируется таблица?

5.5. Каким образом можно отсортировать в возрастающим или убывающем порядке информацию в таблице?

5.6. Можно ли добавлять в созданную таблицу новые строки и столбцы? Если "да", то каким образов это можно осуществить?

5.7. Можно ли изменить границы и цвета заливки ячеек? Если "да", то каким образов это можно произвести?

Лабораторная работа №2. Выполнение вычислений в таблицах

Цель работы: Освоение навыков выполнения элементарных вычислений в таблицах в текстовом редакторе WORD.

1. Теоретические сведения. Для элементарных вычислений в таблицах можно воспользоваться командой Формула в меню Таблица. Для сложных вычислений используйте Microsoft Excel.

2. Задание к работе. Используя данные таблицы 2.1, определить максимальный курс $ при открытии за 9 дней и средний курс за 9 дней $ при закрытии банка.

Результаты занести в строку под номером 10.

Таблица 2.1

№	Дата	Курс $ при открытии банка	Курс $ при закрытии банка
1	01.09	47,8	48
2	02.10	47,7	47,9
3	03.10	47,8	47,9
4	04.10	47,95	48
5	05.10	48	49
6	06.10	48,5	49,2
7	07.10	47,9	48,6
8	08.10	47,7	48
9	09.10	47,75	48,1
10

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

· Установите курсор в ячейке таблицы, находящейся на пересечении строки 10 и колонки 3.

· В меню Таблица выберите команду Формула.

· Выделенная ячейка находится в самом низу столбца чисел, поэтому Microsoft Word предлагает формулу =SUM(ABOVE). Удалите ее из поля Формула, не стирая знак равенства

· В списке Вставить функцию выберите функцию max.

· Для ссылки на ячейки таблицы введите в формулу адреса этих ячеек в скобках. В данном случае введите формулу =МАХ (с 2:с 11).

· В таблице 2.2 приведены адреса ячеек таблицы 2.1.

Таблица 2.2

a1	b1	c1	d1
a2	b2	c2	d2
a3	b3	c3	d3
a4	b4	c4	d4
a5	b5	c5	d5
a6	b6	c6	d6
…	…	…	…

· В поле Формат числа введите формат для чисел. Для отображения чисел в виде процентов выберите # # # 0,00. Затем нажмите на кнопку OK.

· Аналогично в 10 строку колонки 4 введите формулу AVARAGE(d2:d11).

В результате проведенных операций строка 10 должна выглядеть следующим образом:

10		48,50	48,30

4. Примеры лабораторных заданий:

4.1 Используя данные таблицы 2.1, посчитайте минимальные значения курса $ при закрытии и открытии банка.

4.2 Используя данные таблицы 2.1, посчитайте для каждого дня средний курс доллара за день.

4.3 Используя данные таблицы 2.1 и функцию PRODUCT (произведение), определите произведение курсов $ с первого по девятый день.

4.4 Используя данные таблицы 2.1 и функцию COUNT (подсчет), посчитайте количество строк в таблице.

5. Контрольные вопросы:

5.1. Проверьте, можно ли делать вычисления в таблице с объединенными ячейками.

5.2. Как проводятся вычисления в таблицах?

5.3. Можно ли просчитать в таблице значения тригонометрических функций?

5.4. Можно ли в таблице провести деление одной ячейки на другую?

5.5. Можно ли провести вычисления в таблице с числами с десятичной точкой вместо запятой?

Лабораторная работа №3. Ввод и редактирование формул

Цель работы: Освоение навыков создания сложных формул в текстовом редакторе WORD.

1. Теоретические сведения. Формулы, которые содержат только греческие и математические символы и располагаются в одной строке (т.е. в них нет дробей, интегралов и других многоуровневых элементов формул), можно набирать без использования Редактора формул, с помощью команды ВставкаСимвол.

Редактор формул является специальной версией редактора формул MathType, разработанного компанией Design Science, Inc., настроенной для использования с приложениями корпорации Майкрософт. В формуле русские и английские буквы, знаки арифметических операций и другие обычные символы надо набирать с клавиатуры. Специфические элементы формул - дроби, интегралы, знаки сумм, матрицы и т.п. вставляются с помощью выбора шаблонов и символов на панели инструментов и ввода чисел и переменных в отделенные для них места.

С помощью редактора формул можно создавать сложные формулы, выбирая символы с панели инструментов и вводя переменные и числа. При создании формул размер шрифтов, интервалы и форматы автоматически регулируются в соответствие с правилами записи математических выражений. Изменять форматирование можно и в процессе работы. Существует также возможность переопределять автоматические стили.

2. Задание к работе. С помощью редактора формул набрать следующую формулу:

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

· Редактор формул вызывается командой Вставка Объект.

· В диалоговом окне во вкладке Создание в списке объектов с помощью вертикальной полосы прокрутки выберите Microsoft Equation 3.0.

· Нажмите кнопку OK.

· После вставки новой формулы вид окна Word изменится: будет создан новый объект-формула, занимающий в документе прямоугольную область, и запущена программа Редактор формул для работы с этим объектом. В рабочей области Word прямоугольником выделена область объекта (при необходимости она будет автоматически увеличиваться) и панель инструментов Редактора формул.

· Откройте список Шаблонов дробей и радикалов (щелкните по этой кнопке панели инструментов). Выберите шаблон Вертикальная дробь.

· Переведите курсор в числитель дроби, и из списка шаблонов Разные символы выберите букву .

· Наберите с клавиатуры английскую букву y.

· Откройте список Надстрочные знаки и выберите знак -.

· Переведите курсор в знаменатель дроби и наберите (так же как в пунктах 6-7).

· Поставьте курсор в конец формулы.

· Наберите с клавиатуры знак =.

· Наберите английскую букву a.

· Откройте шаблоны Верхних и нижних индексов.

· Из списка шаблонов выберите шаблон Нижний индекс (см. название шаблона в строке состояния).

· Справа внизу от буквы a появится пунктирный прямоугольник. Наберите в нем индекс 2.

· Поставьте курсор в конец формулы.

· Наберите букву z.

· Введите с клавиатуры знак +.

· Далее продолжайте набор формулы аналогично пунктам 11-16.

· Чтобы вернуться в Microsoft Word, щелкните документ.

4. Пример лабораторного задания. Набрать формулы:

;

;

; ;

5. Контрольные вопросы:

5.1. Как изменить размер символов в формуле?

5.2. Можно ли копировать фрагменты формулы?

5.3. Как ввести матрицу размерности 6х 8 (6 строк, 8 столбцов) с помощью шаблона матриц?

Лабораторная работа №4. Работа с простейшими базами данных (списки в MS EXCEL)

Цель работы: Освоение навыков использования MS EXCEL для ведения простейших баз данных.

1. Теоретические сведения. MS Excel обладает некоторыми возможностями систем управления базами данных (СУБД). С его помощью можно получить информацию из больших рабочих листов, выделить конкретную группу информации, соответствующую указанным критериям и сохранить обработанные данные как определенную группу информации. Хотя Excel не является полноценной СУБД, с его помощью можно реализовать потребности многих пользователей.

Набор информации, составленной в Excel определенным образом называется списком. Каждый столбец представляет определенную категорию информации (в терминологии баз данных называемый полем). Строка представляет набор информации об одном лице, теме или другом единичном элементе. В базах данных строки называются записями. Запись содержит информацию о конкретном элементе. Все записи, включенные в один файл, представляют файл базы данных (в терминологии Excel - список).

Список Excel имеет следующие ограничения:

количество полей (столбцов) не может превышать количества столбцов рабочего листа (255 столбцов) и количество записей ограничено количеством строк рабочей книги, при этом первая строка всегда используется для ввода имен полей; при использовании формы данных количество полей сокращается до 32;

MS Excel работает как плоская СУБД, т.е. за один раз можно получить информацию из одного набора данных (реляционные базы данных в отличие от плоских позволяют обрабатывать информацию, находящуюся в разных наборах данных-таблицах);

Правила составления списка.

Имена полей списка должны быть корректными, отличными друг от друга;

Формат имен полей должен быть отличен от формата основного списка, что позволяет Excel различать простую таблицу и список;

Столбцы должны располагаться в том порядке, в котором вводятся данные;

Внутри списка не должно быть пустых записей или полей;

На рабочем листе список от других данных должен быть отделен пустым столбцом или пустой строкой;

Формулы в списке, ссылающиеся на данные вне списка, должны содержать абсолютные ссылки. Так как при сортировке списка эти формулы не будут работать;

Формулы в списке, ссылающиеся на данные внутри списка должны содержать относительные ссылки, при сортировке они будут автоматически корректироваться;

Ввод данных в список.

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

1. При вводе данных вручную или непосредственном вводе в качестве полей списка можно использовать весь диапазон столбцов рабочего листа (255 полей). Но при этом необходимо следить чтобы добавляемая запись находилась внутри области, которая определена как список. Иначе, запись не будет включена в рассмотрение при выполнении команд сортировки и выбора. Непосредственный ввод данных позволяет задавать сложные критерии отбора.

2. Ввод данных с помощью команды Данные Форма представляет более наглядное и удобное средство заполнения списка. Используя названия полей из первой строки, Excel конструирует форму с соответствующими полями. Форма содержит элементы управления кнопки и полосу прокрутки, с помощью которых можно добавлять и удалять записи, вводить критерии, переходить от записи к записи. Недостаток формы в том, что она не может работать с более чем 32 полями.

Сортировка данных. Команда Данные Сортировка позволяет отсортировать список по значениям одного или нескольких полей. Одновременно сортировка может вестись максимум по трем полям. Если необходимо отсортировать, например 6 полей, сначала сортирую последние три поля, а затем первые три. Для сортировки необходимо выделить нужные столбцы и для каждого указать режим сортировки (по возрастанию или по убыванию).

Поиск и отбор данных, удовлетворяющих некоторому критерию.

Существует три способа поиска и отбора данных:

С помощью Формы данных;

Применение Автофильтра;

Применение Расширенного фильтра;

Использование Формы данных.

В окне формы данных щелкнуть по кнопке Критерии и в соответствующем поле набрать информацию необходимую для поиска записи. Если ни одна из записей не удовлетворяет, критерию будет выведена последняя запись

Применение Автофильтра. Это средство включается командой Данные ФильтрАвтофильтр. При этом справа от имени поля появится кнопка раскрытия списка, в котором находятся следующие значения: Все; Первые 10; Условие.

Выбор одного из элементов поля приводит к ограничению отображаемых записей списка. На экране остаются записи, в соответствующем поле которых находится выбранный элемент. Кроме того, данные можно отобрать по какому-либо условию, выбрав из списка значение Условие. В этом случае можно задать сложный критерий отбора записей. Для того чтобы снять наложенные условия фильтрации необходимо выбрать значение Все. Значение Первые 10 выводит на экран первые десять записей. Отключить автофильтр можно путем повторного выполнения команды Данные ФильтрАвтофильтр.

Применение расширенного фильтра.

По сравнению с Автофильтром, Расширенный фильтр позволяет задавать более сложные критерии отбора записей. Диапазон ячеек, соответствующий условию фильтрации, следует указать в поле Диапазон условий, появляющегося в результате выполнения команды Данные ФильтрРасширенный фильтр.

2. Задания к работе.

№1. Создать базу данных в EXCEL, хранящую информацию о студентах по группам. База данных должна иметь следующие поля:

· № (порядковый номер записи);

· Ф.И. О.;

· Наименование группы;

· Пол (м, ж);

· Год рождения;

· Средний балл по результатам зимней сессии (1 сессия);

· Средний балл по результатам летней сессии (2 сессия);

· Оплата (вводить оплаченную сумму по контракту или "0", если студент учится на бюджете).

Отсортировать записи по полю "Ф.И. О." в алфавитном порядке.

№2. Используя "Автофильтр", показать информацию из базы данных только по юношам.

№3. Посчитать с помощью команды "Итоги" число юношей и девушек в каждой группе.

№4. Показать данные по студентам, у которых средний балл за зимнюю и летнюю сессии больше или равен 74.

№5. Используя команду "Форма" ввести новую запись и просмотреть записи по какой-то группе "ПОВТ-3-05".

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

Выполнение задания №1

· Создать новую книгу и дать ей имя.

· На листе "Лист 1" создать базу данных (рис. 4.1).

· Оставляя текущую ячейку в базе данных, выберем команду ДанныеСортировка.

· В появившемся окне "Сортировка диапазона" выберите Ф.И. О. в поле "Сортировать по".

· В результате выполненных действий должен появиться отсортированный список, представленный на рис. 4.2.

Рис. 4.1



Рис. 4.2

Выполнение задания №2

Рис. 4.3

· 2.1. Выберите ячейку в списке, который требуется отфильтровать.

· 2.2. Задайте команду ДанныеФильтАвтофильтр.

· 2.3. В столбце, содержащем "Пол", нажмите кнопку со стрелкой и выберите вариант м.

В результате вы должны получить таблицу, представленную на рис. 4.3.

Примечания:

· Если данные уже отфильтрованы по одному из столбцов, при использовании фильтра для другого столбца будут предложены только те значения, которые видны в отфильтрованном списке.

· Чтобы удалить фильтр, примененный для одного столбца диапазона или списка, нажмите кнопку со стрелкой рядом со столбцом, а затем выберите команду Все.

· Чтобы удалить фильтры для всех столбцов диапазона или списка, выберите в меню Данные пункт Фильтр, а затем - команду Отобразить все.

· Чтобы убрать кнопки фильтра, отображающиеся рядом со столбцами, выберите в меню Данные пункт Фильтр, а затем - команду Автофильтр.

Выполнение задания №3:

· Отсортировать базу данных: сначала по полю "Группа", затем по полю "пол" в алфавитном порядке, используя команду ДанныеСортировка. В результате получится таблица, представленная на рисунке 4.4.

· Применить команду ДанныеИтоги…Появится окно "Промежуточные итоги". При каждом изменении в поле пол задайте команду на подсчет промежуточных итогов, введя операцию количество. В результате получите рисунок 4.5.

Таким образом, в группе ПОВТ-1-05 учатся 6 юношей, в ПОВТ 1-05-3 юношей и 1 девушка, в ПОВТ-3-05-2 юношей и 3 девушки.

Рис. 4.4

Рис. 4.5

Выполнение задания №4:

· Убрать все итоги. Для этого надо задать команду ДанныеИтоги… и в появившемся окне нажать на кнопку Убрать все.

· Установить Автофильтр командой ДанныеФильтАвтофильтр.

· В столбце "1 сессия", нажмите кнопку со стрелкой и выберите вариант условие.

· В появившемся окне выберите вариант больше или равно и введите число "74" (см. рис. 4.6).

Рис. 4.6

· В результате вы должны получить таблицу, представленную на рис. 4.7.



Рис. 4.7

· Повторив шаги с 4 по 5 для столбца "2 сессия", получим результирующую таблицу, представленную на рисунке 4.8.

Выполнение задания №5:

· Уберите фильтр.

· Задайте команду ДанныеФорма…

· В появившемся окне нажмите на кнопку Добавить и введите новую запись (рис. 4.9.).

Рис. 4.8

Рис. 4.9

4.1. Нажмите на кнопку Добавить и в базе данных внизу появится новая строка.

4. Пример лабораторного задания. Для таблицы 4.1 выполнить следующие ниже следующие задания.

4.1. Отсортировать записи по полю "Группа" в возрастающем порядке.

4.2. Используя Автофильтр, показать информацию из базы данных по группе "ПОВТ-2-05".

4.3 Посчитать с помощью команды Итоги общую сумму оплаты по каждой группе.

4.4. Показать данные по студентам, у которых средний балл за зимнюю и летнюю сессии больше или равен 87.

5. Контрольные вопросы:

1. Правило организации списка в MS EXCEL.

2. Чем отличается операция Сортировка от операции Фильтрация?

3. Как организовать сортировку в MS EXCEL?

4. Как установить Автофильтр?

5. Для чего используют команды Итоги и Фильтр?

Лабораторная работа №5. Работа со стандартными функциями MS EXCEL

Цель работы: Получение навыков работы со стандартными функциями MS EXCEL.

1. Теоретические сведения. Вычисления, которые позволяет производить программа EXCEL, не ограничены простейшими арифметическими операциями. Программа позволяет использовать большое число встроенных стандартных функций, обращение к которым осуществляется через Мастера функций.

Вызвать Мастера функций можно либо через меню командой Вставка Функция…, либо нажатием на кнопку fx на панели инструментов.

Диалоговое окно Мастера функций позволяет выбрать любую стандартную функцию из имеющихся в программе EXCEL. В списке Категория выбирают ту категорию, к которой относится нужная функция, а в списке Функция - конкретную функцию.

После того как нужная функция выбрана, ее имя заносится в строку формул. На экране появляется окно функции. В верхней его части размещаются поля, предназначенные для ввода аргументов, а в нижней части располагается справочная информация. Здесь указывается общее описание назначения функции, а также сведения о задаваемом аргументе. Если аргумент указан полужирным шрифтом, значит, он является обязательным, а если обычным шрифтом, то его можно пропустить.

Задавать аргументы функции можно в числовом виде (вручную) или как ссылки на ячейки (вручную или щелчком на соответствующей ячейке). Некоторые функции могут принимать в качестве параметра диапазон ячеек. Программа EXCEL допускает вложенные функций, то есть в качестве параметра одной функции может быть указано значение другой функции.

2. Задания к работе:

№1. Вычислить функцию f(x)=sin(x) для x= 30⁰.

№2. Заданы матрицы А, В и С.

.

Используя Мастера функций, вычислить М=(А+В)*С.

№3. Транспонировать матрицу D.

№4. Заданы значения курса доллара за 10 последних дней в виде таблицы 5.1.

Определить курс доллара на 10 день.

Таблица 5.1

№ дня	Курс $
1	42.10
2	42.12
3	42.13
4	42.12
5	42.115
6	42.113
7	42.11
8	42.115
9	42.117

№5. Определить значение y:

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

Выполнение задания №1:

· Создать новую книгу и дать ей имя.

· На листе "Лист 1" создать таблицу (рис. 5.1).

Рис. 5.1

· Оставляя текущей ячейкой B2, выберем команду Вставка Функция…

· В списке Категория выберем Математические.

· В категории Математические выбираем РАДИАНЫ.

· Введя в окне функции РАДИАНЫ в качестве аргумента А 2, получим результат (см. рис. 5.2).

· Делаем текущей ячейку С 3. Вызываем функцию SIN (синус) из категории Математические.

· Введя в окне функции SIN в качестве аргумента А 2, получим результат (см. рис. 5.3).



Рис. 5.2

Рис. 5.3

Выполнение задания №2:

· На листе "Лист 2" создать таблицы исходных данных (рис. 5.4).

Рис. 5.4

· Сделать текущей ячейку В 6. Набрать в ней формулу "=B3+F3" и скопировать ее на ячейки С 6, В 7 и С 7. В результате получим матрицу D=А+В (рис. 5.5.).

Рис. 5.5

· Выделить диапазон ячеек F6:G7.

· Выберем команду Вставка Функция…

· В списке Категория выберем Математические.

· В категории Математические выбираем МУМНОЖ.

· В окне функции МУМНОЖ в строке Массив 1 вводим диапазон B6:C7, а в строке Массив 2 - диапазон J3:K5.

· Нажать комбинацию клавиш Ctrl+Shift+Enter. В результате получим матрицу М (Рис. 5.6).

Рис. 5.6

Выполнение задания №3:

· На листе "Лист 3" создать таблицу исходных данных (рис. 5.7).



Рис. 4.7

· Выделить диапазон ячеек G1:I3.

· Выберем команду Вставка Функция…

· В списке Категория выберем Ссылки и массивы.

· В категории Ссылки и массивы выбираем ТРАНСП.

· В окне функции ТРАНСП в строке Массив вводим диапазон B1:D3.

· Нажать комбинацию клавиш Ctrl+Shift+Enter. В результате получим матрицу DT (Рис. 5.8).

Рис. 5.8

Выполнение задания №4:

· Добавим Лист 4 в существующую книгу.

· Создать на Листе 4 таблицу и текущей сделать ячейку В 11 (рис. 5.9)



Рис. 5.9

· Вызвать Мастер функций, выбрать в категории Статистические функцию ПРЕДСКАЗ.

· В появившемся окне функции в поле х установить ячейку А 11 (ячейка указывает точку, в которой будем получать прогнозируемое значение - 10), в поле Известные_значения_у установить диапазон В 2:В 10, (диапазон ячеек, в котором находятся известные данные по курсу доллара), в поле Известные_значения_х установить значения А 2:А 10 (диапазон ячеек, указывающий точки в которых известны данные по курсу доллара).

· Нажать на кнопку Ок.

Получим прогнозные значения курса доллара на 11 день, равный 42,1160 … (см. рис. 5.10). Функции РОСТ и ТЕНДЕНЦИЯ работают аналогично.

Выполнение задания №5:

· Добавим Лист 5 в существующую книгу.

· Сделаем текущей ячейку В 2 и будем считать, что значение аргумента х находится в ячейке А 2.

· Выберем команду Вставка Функция…

· В списке Категория выберем Логические.

· В категории Логические выбираем функцию ЕСЛИ.

· В окне функции устанавливаем аргументы (см. рис. 5.11).

Рис. 5.10

Рис. 5.11

4. Лабораторные задания.

4.1. Заполнить таблицу 5.2.

Таблица 5.2

Угол X в градусах	Угол X в радианах	Sin(X)	Cos(X)	Tg(X)
0
30
45
60
90

4.2. Заданы матрицы:

Определить:

· обратные матрицы;

· транспонированные матрицы А, В и С;

· значения определителей матриц А, В и С;

· разность матриц А и С;

· произведение матриц В и С.

4.3. Получите прогнозные значения курса доллара, представленной таблицей 5.1 с помощью функций РОСТ и ТЕНДЕНЦИЯ.

4.4. Определить значение y:

5. Контрольные вопросы:

5.1. Как можно вызвать стандартную функцию MS Excel?

5.2. Какие категории функций предлагает MS Excel

5.3. Какими функциями можно прогнозировать в MS Excel?

5.4. Какие функции MS Excel предлагает для работы с матрицами?

5.5. Для каких задач используются логические функции?

5.6. Как задаются аргументы в окнах функций?

Лабораторная работа №6. Инструментальное средство "Подбор параметра"

Цель работы: Освоение встроенного средства Excel Подбор параметра, позволяющего находить оптимальное решение простых задач, в которых требуется изменять только один параметр.

1. Теоретические сведения. Подбор параметра - одно из самых мощных средств EXCEL. С помощью простых приемов можно находить оптимальное решения многих задач.

Подбор параметра - средство EXCEL для так называемого Что- Если анализа. При этом значения ячеек-параметров изменяется так, чтобы число в целевой ячейке стало равным заданному. Иногда это средство не может найти ответ, например, входные данные неверны или ответа вообще не существует. В этом случае появляется сообщение об ошибке. По умолчанию средство Подбор параметра производит до 100 попыток найти ответ. Поиск прекращается, когда найден ответ с точностью до 0,0001.

2. Задания к работе.

№1. В одну порцию омлета входит 4 яйца, 200 гр. молока и 10 гр. сливочного масла. Рассчитать калорийность омлета, если калорийность 100 гр яиц - 150 ккал, молока - 52 ккал, масла - 742 ккал. С помощью подбора параметра изменить значение содержания яиц в омлете таким образом, чтобы калорийность блюда был 350 ккал. Вес 1 яйца принимаем равным 50 гр.

№2. Решить с помощью Подбора параметра уравнение вида: sinx=0.5.

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

Выполнение задания №1.

· Создать новую книгу и дать ей имя.

· На листе "Лист 1" создать таблицу для расчета калорийности (рис. 6.1).

· В ячейку B3 ввести формулу: = B7*50.

· В ячейку D3 вводим формулу =B3*C3/100.

· Затем эту формулу копируем соответственно в ячейки B4 и B5.

· Значения калорийности каждого продукта суммируем и получаем калорийность всего блюда. Для данных пропорций она равна 478,2 ккал.

· Чтобы получить калорийность равную 350 ккал выберем команду СервисПодбор параметра. В поле "Установить в ячейке" должна появится ссылка на целевую ячейку D6, в поле "Значение" ввести искомое значение 350, в поле "Изменяя значение ячейки" ввести ссылку на ячейку B7, в которой указано количество яиц (Рис. 6.2).

Рис. 6.1

Рис. 6.2

· Нажмите кнопку Оk. В результате в ячейке В 7 получим 2,29, что составляет примерно 2 яйца.

Выполнение задания №2.

· На листе "Лист 2" создаем таблицу исходных данных (рис. 6.3).

Рис. 6.3

· В ячейку В 3 введите формулу = sin(A3).

· Выберите команду СервисПодбор параметра.

· В поле "Установить в ячейке" введите ссылку на ячейку B3.

· В поле "Значение" введите то значение 0.5.

· В поле "Изменяя значение ячейки" введите ссылку на ячейку А 3 (рис. 6.4)

· Нажмите кнопку Оk.

Рис. 6.4

После завершения процесса подбора, в ячейке А 3 увидите результат - значение x в радианах (Рис. 6.5).

Рис. 6.5

Переведите значение x в градусы с помощью функции ГРАДУСЫ Должно получиться x=29,950608 (x30 градусов).

4. Примеры лабораторного задания:

4.1. Используя данные задания №1, получить калорийность 400 ккал, изменяя количество молока.

4.2. Используя данные задания №1, получить калорийность 500 ккал, изменяя количество масла.

4.3. Используя данные задания №1, получить калорийность 400 ккал, изменяя количество яиц.

4.4. Решить уравнения cos(x)=0,5; tg(x)=1; sin(x)=1.

5. Контрольные вопросы:

5.1. Сколько одновременно ячеек может изменять Подбор параметра?

5.2. Для решения каких задач применяется Подбор параметра?

5.3. Что содержится в поле Значение?

5.4. Что содержится в поле Установить в ячейке?

5.5. Можно ли найти с помощью Подбор параметра два корня уравнения?

Лабораторная работа №7. Инструментальное средство "Поиск решения"

Цель работы: Освоение встроенного средства Excel Поиск решения, позволяющего находить оптимальное решение сложных задач, в которых требуется изменять более одного параметра.

1. Теоретические сведения. Задачи, которые включают взаимозависимые переменные, практически невозможно решить вручную. Средство Поиск решения облегчает решения сложных задач со многими неизвестными и ограничениями.

Опишем элементы диалогового окна "Поиск решения".

Установить целевую ячейку. Служит для указания целевой ячейки, значение которой необходимо максимизировать, минимизировать или установить равным заданному числу. Эта ячейка должна содержать формулу.

Равно. Служит для выбора варианта оптимизации значения целевой ячейки (максимизация, минимизация или подбор заданного числа). Чтобы установить число, введите его в поле.

Изменяя ячейки. Служит для указания ячеек, значения которых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указанной в поле Установить целевую ячейку.

Предположить. Используется для автоматического поиска ячеек, влияющих на формулу, ссылка на которую дана в поле Установить целевую ячейку. Результат поиска отображается в поле Изменяя ячейки.

Ограничения. Служит для отображения списка граничных условий поставленной задачи.

Добавить. Служит для отображения диалогового окна Добавить ограничение.

Изменить. Служит для отображения диалогового окна Изменить ограничение. word excel таблица функция

Удалить. Служит для снятия указанного ограничения.

Выполнить. Служит для запуска поиска решения поставленной задачи.

Закрыть. Служит для выхода из окна диалога без запуска поиска решения поставленной задачи. При этом сохраняются установки, сделанные в окнах диалога, появлявшихся после нажатий на кнопки Параметры, Добавить, Изменить или Удалить.

Параметры. Служит для отображения диалогового окна Параметры поиска решения, в котором можно загрузить или сохранить оптимизируемую модель и указать предусмотренные варианты поиска решения.

Восстановить. Служит для очистки полей окна диалога и восстановления значений параметров поиска решения, используемых по умолчанию.

2. Задания к работе.

№1. С помощью средства Поиск решения решить следующую задачу. Фирма, выпускает три вида продукции А, В, с. Себестоимость выпуска единицы каждого вида продукции, прибыль на единицу от их реализации и максимальный уровень спроса на продукцию известны; они представлены в таблице 7.1. Фирма располагает денежными ресурсами в 20000сомов. Определить, в каких объемах следует выпускать продукцию каждого вида, чтобы прибыль от ее реализации была максимальной.

Таблица 7.1

Вид продукции	Себестоимость (сом)	Прибыль на ед. выпуска (сом)	Емкость рынка (шт.)
А	10	18	1000
В	15	25	5000
С	9	15	3000

№2. Используя Поиск решения, определить минимум функции:

f(x) = 3x²+4x+1.

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

Выполнение задания №1.

· Создать новую книгу и дать ей имя.

· На листе "Лист 1" создать таблицу с данными (Рис. 7.1).

Рис. 7.1

· Задаем начальные данные объемов продукции равными нулю.

· В ячейку F2 вводим формулу =E2*C2.

· Затем эту формулу копируем соответственно в ячейки F3 и F4.

· Значения общей прибыли каждой продукции суммируем и получаем общую прибыль всей продукции в ячейке F5.

· В ячейку G2 вводим формулу =E2*B2.

· Затем эту формулу копируем соответственно в ячейки G3 и G4.

· Чтобы получить оптимальный объем выпуска каждого вида продукции выберем команду СервисПоиск решения.

· В поле "Установить целевую ячейку" укажем ссылку на целевую ячейку F4.

· В поле "Равной" установим переключатель "Максимальному значению".

· В поле "Изменяя значение ячейки" ввести ссылку на диапазон ячеек Е2:Е4.

· Нажать кнопку "Добавить" и ввести в поле "Ограничение" ограничения на выпуск продукции по спросу в диалоговом окне Добавление ограничений: E2:E4<=D2:D4.

· Нажатием кнопки "Добавить" добавляем ограничения по денежным ресурсам фирмы в диалоговом окне Добавление ограничений: G5<=20000.

· Нажатием кнопки "Добавить" добавляем ограничения на целочисленность результатов (объемов выпуска продукции) в диалоговом окне Добавление ограничений.

· Нажмите кнопку Оk. В результате в поле "Ограничения" будут отражены все ограничения (Рис. 7.2).

Рис. 7.2

· Нажмите кнопку "Выполнить". В результате в ячейке F5 получим значение 34660.Объемы выпуска продукции видов А, B, C соответственно равны 1000, 406, 434 А на экране появится диалоговое окно Результаты поиска решения и если найденное решение удовлетворяет, то нажмите кнопку Оk.

Выполнение задания №2.

· На листе "Лист 1" создайте таблицу с исходными данными задачи. В ячейку B3 введите формулу (Рис. 7.3).

Рис. 7.3

· Для получения минимума функции выберем команду СервисПоиск решения.

· В поле "Установить целевую ячейку" укажем ссылку на целевую ячейку В 3.

· В поле "Равной" установим переключатель "Минимальному значению".

· В поле "Изменяя значение ячейки" ввести ссылку на ячейку А 3.

Рис. 6.4

· Нажмите кнопку "Выполнить".

В результате в ячейке F3 получим минимальное значение функции, равное -0,333. При этом х=-0,667.

Примечание. Программа Поиск решения находит оптимум вблизи начальной точки. Поэтому, если функция имеет несколько экстремумов, то следует для каждого задавать свою начальную точку.

4. Примеры лабораторного задания.

4.1. Для изготовления изделий Р 1, Р 2, Р 3 используются четыре вида материалов: S1, S2, S3, S4. Запасы материалов, технологические нормы расхода материалов на каждое изделие и цена единицы изделия приведены в таблице 6.2. Составить план выпуска изделий, обеспечивающий их максимальный выпуск по стоимости.

Таблица 7.2

Вид сырья	Запас материала, кг	Нормы расхода материала на единицу изделия, кг
		P1	P2	P3
S1	150000	4	2	1
S2	170000	6	0	2
S3	100000	0	2	4
S4	200000	8	7	0
Стоимость изделий, рублей	20	40	30

4.2. Найти максимальное значение функции f(x) =-5x²+2x+3.

4.3. Найти минимальное значение функции f(x) =5x²+2x+3.

Решения задач 4.2 и 4.3 подтвердить графически.

5. Контрольные вопросы:

5.1. Для решения каких задач применяется Поиск решения и в чем его основное отличие от Подбора параметра?

5.2. Каким критериям должны удовлетворять задачи, для решения которых применяется Поиск решения?

5.3. Дайте определение Целевой ячейке.

5.4. Дайте определение Изменяемым ячейкам.

5.5. Как задаются ограничения?

5.6. Можно ли изменять ограничения. Если "да", то как это осуществить?

Список используемой литературы

1. Симонович С., Евсеев Г., Алексеев А. Специальная информатика: Учебное пособие - М.: АСТ Пресс Инфорком Пресс, 1998 г.

2. Новейший самоучитель работы на компьютере /Под ред. С. Симоновича. М.: DECC INFORMCOM PRESS, 1999 г.

3. Информатика /Учебник под ред. Макаровой Н.В. М.: Финансы и статистика 1997 г.

4. Орвис В. Дж. EXCEL для ученых, инженеров и студентов. Киев: Юниор, 1999 г.

Размещено на Allbest.ru

...