Кораблина Т.В., Ляховец М.В., Бабичева Н.Б., Фетинина Е.П. Решение задач информатизации с помощью MS Excel Часть II - файл n1.doc

Кораблина Т.В., Ляховец М.В., Бабичева Н.Б., Фетинина Е.П. Решение задач информатизации с помощью MS Excel Часть II

Доступные файлы (1):

n1.doc

1752kb.

01.04.2014 05:51

скачать

n1.doc

Институт информационных технологий и автоматизированных систем

Кафедра систем информатики и управления


Решение задач информатизации с помощью MS Excel

Часть II

Решение прикладных задач

Методические указания к выполнению практических и самостоятельных работ


Новокузнецк
2012
Министерство образования и науки Российской Федерации


Федеральное государственное бюджетное образовательное
учреждение высшего профессионального образования

«Сибирский государственный индустриальный университет»

Кафедра систем информатики и управления
Решение задач информатизации с помощью
ms excel

Часть II

Решение прикладных задач

Методические указания к выполнению практических

и самостоятельных работ


Новокузнецк

2012

УДК 004.9 (07)

Р-470
Рецензент

кандидат технических наук,

доцент кафедры информационных технологий в металлургии

Кожемяченко В.И.


Р-470 Решение задач информатизации с помощью MS Excel. Часть II. Решение прикладных задач: метод. указ. / Сиб. гос. индустр. ун-т ; сост. Н.Б. Бабичева, Т.В. Кораблина, М.В. Ляховец, Е.П. Фетинина. – Новокузнецк : Изд. центр СибГИУ, 2012. – 40 с., ил.
Дан материал по практическому и теоретическому освоению программного продукта Microsoft Excel. Даны практические рекомендации по использованию Excel для анализа данных, оптимизации, аппроксимации функций, построения и редактирования диаграмм.

Предназначены для студентов всех форм обучения направления подготовки 230700.62 «Прикладная информатика».

ОГЛАВЛЕНИЕ

ВВЕДЕНИЕ

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

Приложение Microsoft Office Excel 2007 — мощный инструмент, используемый для создания и форматирования электронных таблиц, анализа данных и обмена информацией для принятия более обоснованных решений. Приложение Office Excel 2007 характеризуется значительно более удобными средствами обмена данными и повышенным уровнем безопасности, благодаря которому предоставляется безопасный доступ к конфиденциальной коммерческой информации. При совместном использовании электронных таблиц в Office Excel 2007 и службах Excel можно просматривать, сортировать, фильтровать и вводить параметры, а также взаимодействовать с представлениями сводной таблицы непосредственно в веб-браузере.

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

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

Тема 1 Использование пакета анализа для построения гистограмм

Основные вопросы:

• 
  Применение гистограмм для анализа данных.
  
  • 
    Построение гистограммы.
    

Гистограмма – это диаграмма, в которой для исходного множества значений определяется число значений (частот), попадающих в интервалы разбиения (карманы).

Пусть требуется определить количественное соотношение оценок 27 выпускников одного класса. Выполним задание, построив гистограмму распределения оценок. Занесем оценки учеников в ячейки A2:A28, а в ячейки B2:B6 – возможные оценки (от 1 до 5). Далее на вкладке Вставка нажмите кнопку Гистограмма (рисунок 1.1).



Рисунок 1.1 – Инструмент Гистограмма на вкладке Вставка

При этом появится выпадающий список (рисунок 1.2), в котором приведены возможные типы гистограмм. После выбора типа гистограммы гистограмма автоматически создастся. При этом, если курсор стоял на ячейке с данными, то ряды данных в гистограмме будут сформированы автоматически.



Рисунок 1.2 – Типы гистограмм

Для изменения данных гистограммы нажмите кнопку Выбрать данные на вкладке Работа с диаграммами (рисунок 1.3).



Рисунок 1.3 – Вкладка Работа с диаграммами

После нажатия кнопки появится окно Выбора источника данных (рисунок 1.4). В поле Входной интервал указывается диапазон ячеек, в которых расположены данные (в нашем случае: A2:A28). В поле Интервал карманов указывают набор граничных значений, определяющих карманы (в нашем случае: B2:B6), эти значения должны идти по возрастанию. Если поле интервалов карманов оставить пустым, то Excel создаст равные интервалы разбиения, используя минимальное и максимальное значения исходных данных в качестве начальной и конечной точек. Число интервалов принимается равным квадратному корню из числа входных значений. В поле Выходной интервал указывается верхняя левая ячейка выходного диапазона, в котором выводятся результаты (например, $F$2).



Рисунок 1.4 – Диалоговое окно выбора источника данных гистограммы

Инструмент Гистограмма может создавать отсортированные гистограммы (Парето, выходные данные отсортировываются в порядке убывания частот), выводить накопленные проценты и генерировать диаграммы. Для этого необходимо установить флажки в диалоговом окне в разделе Параметры вывода. При установке флажка Вывод графика в окне диалога инструмент Гистограмма построит диаграмму одновременно с выводом результатов анализа (рисунок 1.5).



Рисунок 1.5 – Результат построения гистограммы

Построенная гистограмма показывает в числовом и графическом видах соотношения количества оценок выпускников (рисунок 1.2, столбец Частота).

Тема 2 Графическое решение систем уравнений

Основные вопросы:

• 
  Построение нескольких графиков в одной координатной плоскости.
  
• 
  Выбор оптимального масштаба представления графика.
  

Средствами Excel можно быстро и легко решать многие математические задачи. Заданы следующие системы уравнений:



Каждое из уравнений в представленных четырёх системах может быть записано в виде функции:

y=F(х).

Первое уравнение в первой и второй системе описывается функцией:

у=F(х)=x²–2x+4.

Другие два уравнения описываются в этих системах функциями F₁и F₂:

F₁(x)=2x+1

F₂(x)=0,3x³

Третья система для решения ее графически, по сути дела, должна быть представлена тремя уравнениями



В первом уравнении четвертой системы переменная у является аргументом функции косинус. Поэтому чтобы выразить у в явном виде, сначала уравнение нужно записать как cos(y)=x²–1. А затем от левой и правой части уравнения взять обратную функцию косинуса – арккосинус, т.е. y=arccos(x²–1). Тогда четвертая система примет вид

.

Для графического решения первой и второй систем уравнений выполните следующие шаги:

1. 
   Создайте шапку таблицы со столбцами: х, F, F₁, F₂.
   
2. 
   Заполните столбец х значениями из интервала от – 3 до 4 с шагом 0,5.
   
3. 
   Запишите в первую строку столбцов F, F₁, F₂ соответствующие формулы и растяните их в остальные ячейки столбцов. Результат показан на рисунке 2.1.
   

Рисунок 2.1 – Результаты расчетов

4. 
   Вставьте точечные диаграммы без маркеров для первой системы уравнений на основе данных столбцов х, F, F₁ и для второй системы – на основе данных столбцов х, F, F₂. Результаты на рисунках 2.2а) и б). Решением систем уравнений являются координаты точек (х; у) пересечения графиков. Первая система имеет два корня, значения которых (1; 3) и (3; 7). Вторая система имеет один корень, примерное значение которого (2,7; 5,8).
   

а) б)

Рисунок 2.2 – Результаты графического решения первой и второй системы уравнений

Для решения третьей и четвертой систем задайте значения аргумента х в диапазоне от –1 до 1 с шагом 0,1. Рассчитайте значения функций , , y=x³ для третьей системы и функций y=2+cos(x–1), y=arccos(x² –1) для четвертой системы, постройте диаграммы. Вид диаграмм представлен на рисунке 2.3.



Рисунок 2.3 – Результаты графического решения третьей и четвертой системы уравнений

Упражнение 1

Решите графически системы уравнений

; .

Тема 3 Аппроксимация функций с помощью «Линии тренда»

Основные вопросы:

• 
  Вывод линии тренда.
  
• 
  Вывод параметров линии тренда.
  

Пусть мы располагаем некими табличными данными:

Таблица 3.1 – Данные функции x – y

x	0	1	2	3	4	5	6	7	8	9	10	11
y	6,5	6,1	5,6	4,9	4,2	4,0	4,0	4,8	6,1	8,3	10,0	12,1

Необходимо сделать аппроксимацию данной функции с помощью «Линии тренда». Для этого выполните следующие действия:

• 
  Постройте с помощью Мастера диаграмм диаграмму данных таблицы 3.1 (рисунок 3.1).
  

Рисунок 3.1 – Диаграмма данных таблицы 3.1

• 
  Сделайте диаграмму активной и выделите данные диаграммы, щёлкнув один раз по линии графика правой кнопкой мыши.
  
• 
  Из появившегося всплывающего меню выберите команду Добавить линию тренда. На экране появится окно выбора аппроксимирующей кривой (рисунок 3.2).
  

Рисунок 3.2 – Диалоговое окно Линии тренда

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

• 
  Поставьте галочку в окошке «Показывать уравнение на диаграмме». В результате на диаграмму будет выведено уравнение аппроксимирующей кривой со всеми коэффициентами.
  
• 
  Редактировать аппроксимирующую кривую можно также как и всякую другую.
  

На рисунке 3.3 показан результат аппроксимации исходных данных.



Рисунок 3.3 – Результат аппроксимации исходных данных

Упражнение 2

Сделать аппроксимацию данных, согласно своему варианту (таблица 3.2), с выводом формулы аппроксимирующей кривой на поле диаграммы следующими функциями:

• 
  линейной;
  
• 
  полиномиальной максимальной степени;
  
• 
  экспоненциальной;
  
• 
  логарифмической.
  

Наглядно оформить диаграмму аналогично рисунку 3.3, учитывая масштаб данных.

Таблица 3.2 – Варианты заданий к упражнению 2

x	y (варианты)
	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
1	69	4,8	25	0,3	130	45	2,8	0,5	81	122	45	5,6	0,4	55	98
2	64	2,5	26	0,4	100	49	2,7	0,6	89	124	49	5,7	0,6	59	99
3	69	4,4	27	0,2	150	25	1,6	0,8	84	126	42	1,6	0,1	52	92
4	67	5,0	29	0,9	110	72	3,5	0,4	83	127	46	4,9	0,9	51	91
5	69	3,5	22	0,7	125	46	2,9	0,6	85	128	47	6,1	0,3	56	96
6	66	2,6	25	0,5	192	55	2,4	0,2	88	122	42	7,1	0,7	53	93
7	67	3,6	21	0,6	144	88	2,6	0,2	87	129	41	2,3	0,5	57	96
8	67	5,1	23	0,6	164	29	2,2	0,8	89	124	43	5,5	0,4	58	94
9	68	4,7	28	0,4	183	43	1,9	0,3	90	123	44	4,9	0,1	59	96
10	69	4,9	24	0,3	172	67	3,1	0,4	81	123	45	6,3	0,4	54	91