Обработка и анализ данных при помощи Microsoft Excel

 

Содержание

Введение

1. Постановка задачи исследования

2. Краткий теоретический материал

2.1 Создание и редактирование простейших таблиц в Excel

2.2 Создание и редактирование диаграмм в Excel

2.3 Встроенные функции Excel. Статистический анализ

2.4 Прогнозирование

2.5 Анализ "Что-Если. Поиск решения

3. Задание №1

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

3.2 Ход работы

3.3 Ответы на контрольные вопросы и задания

4. Задание №2

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

4.2 Ход работы

4.3 Ответы на контрольные вопросы и задания

5. Задание №3

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

5.2 Ход работы

5.2.1 Задание первое

5.2.2 Задание второе

5.3 Ответы на контрольные вопросы

6. Задание №4

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

6.2 Ход работы

6.2.1 Первое задание

6.2.2 Второе задание

6.3 Ответы на контрольные вопросы

7. Задание №5

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

7.2 Ход работы

7.3 Ответы на контрольные вопросы

Заключение

Вывод

Список литературы

Введение

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

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

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

1. Постановка задачи исследования

В данной работе я собираюсь исследовать основные функции и методы работы в табличном процессоре Microsoft Excel.

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

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

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

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

Последним пунктом моей работы будет изучение основ анализа электронных таблиц Excel, построение сценариев для таблиц и знакомство с основными командами анализа "Что-Если".

2. Краткий теоретический материал

2.1 Создание и редактирование простейших таблиц в Excel

Запуск Excel осуществляется двойным щелчком мыши на пиктограмме. Вид экрана после запуска программы показан на рисунке (См. рис. 1).

Ячейки таблицы в Excel расположены на пересечении столбцов и строк. Таким образом, адрес ячейки формируется из названия столбца и номера строки. Всего рабочий лист может содержать 256 столбцов и 16384 строк.

Ввести данные можно непосредственно в активную ячейку, либо набрав их в строке формул и нажав клавишу Enter. Если текст не помещается в активной ячейке, он автоматически распространяется на соседнюю ячейку. Если не помещается число - ячейка заполняется символами "#.

Для удобства представления данных в Excel применяются различные форматы ячеек (см. табл. 1).

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

Для ввода формулы необходимо:

Выделить ячейку и нажать клавишу = (равно)

-Набрать формулу и нажать Enter

Таблица 1 - Форматы ячеек.

Содержимое ячейкиФорматРезультат 1234,567 1234,567 1234,5 1234,567 1234,5 1234,567 1234,567 0,23 1.4.96 1.4.96Числовой 0 0,00 # ##0,00 Денежный # ##0 р. # ##0,00 р. Научный 0,00E+00 ##0,0E+0 Процент 0,00% Дата Д. ММ. ГГ. Д.МММ. ГГ. 1235 1234,57 1 234,50 1 235 р. 1 234,50 р. 1,23E+03 1,2E+3 23% 1.04.96 1.апр.96

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

Готовая таблица сохраняется в рабочей книге Excel по команде Сохранить как или Сохранить из меню Файл, указав имя рабочей книги (то есть файла с расширением. xls).

табличный процессор таблица диаграмма

2.2 Создание и редактирование диаграмм в Excel

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

В Excel имеется 9 типов двумерных и 6 типов 3-х мерных диаграмм, каждая из которых имеет несколько разновидностей. Рассмотрим некоторые из них.

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

При использовании линейчатой диаграммы, отдельные значения представлены полосами различной длинны, расположенными горизонтально вдоль оси X.

Линейчатая диаграмма, в которой отдельные значения представлены вертикальными столбиками различной длинны, называется гистограммой.

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

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

Радар - это диаграмма, в которой категории представляются лучами, расходящимися из одной точки. Каждое значение ряда данных будет выделено на оси и соединено с другими замкнутой линией.

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

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

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

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

Для их создания можно использовать следующий приём:

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

-Одним из описанных выше способов выделить на гистограмме ряд данных суммарного объема продаж

В меню Формат выбрать команду Тип диаграммы

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

Если данные на диаграмме имеют слишком большой диапазон значений, то удобно использовать логарифмическую шкалу. При выделенной на диаграмме оси выберите команду Оси из меню Формат и установите переключатель Логарифмическая шкала. Иногда, как в рассмотренном выше примере, для отдельного ряда данных удобно использовать вспомогательную ось. Для этого необходимо выделить ряд данных, в меню Формат выбрать команду Выделенный ряд и в диалоговом окне Форматирование ряда данных установить переключатель на вспомогательной оси.

2.3 Встроенные функции Excel. Статистический анализ

содержит 320 встроенных функций. Простейший способ получения полной информации о любой из них заключается в переходе на вкладку Поиск из меню "?", после чего необходимо напечатать имя нужной функции и нажать кнопку Показать (см. рис. 2).

Для удобства функции в Excel разбиты по категориям (математические, финансовые, статистические и т.д.).

Обращение к каждой функции состоит из двух частей: имени функции и аргументов в круглых скобках. Аргументы функции могут быть следующих типов: Числовые константы, Ссылки на ячейки и блоки ячеек, Текстовые константы (заключенные в кавычки), Логические значения, Массивы, Имена ссылок, Смешанные аргументы.

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

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

В меню Сервис выберите команду Анализ данных

-Выберите из списка название нужного инструмента анализа и нажмите кнопку ОК

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

Некоторые функции представлены в приложении 1.

2.4 Прогнозирование

Одним из методов, используемых для прогнозирования, является регрессивный анализ.

Регрессия - это статистический метод, который позволяет найти уравнение, наилучшим образом описывающее совокупность данных. В известной мере понятия регрессии объединяет такие математические понятия, как интерполяция и экстраполяция.

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

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

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

В Excel имеется 5 функций для линейной регрессии (ЛИНЕЙН (), ТЕНДЕНЦИЯ (), ПРЕДСЕАЗ (), НАКЛОН () и СТОШУХ ()) и 2 функции для экспоненциальной регрессии (ЛГРФПРИБЛ () и РОСТ ()). Рассмотрим некоторые из них:

ЛИНЕЙН () и ЛГРФПРИБЛ () - вычисляет коэффициенты для уравнения прямойи экспоненциальной кривой, соответственно.

ТЕНДЕНЦИЯ () и РОСТ () - возвращает числовые значения, лежащие на прямой линии и экспоненциальной кривой, соответственно.

СТОШYX () - возвращает стандартную погрешность регрессии - меру погрешности предсказываемого значения y для заданного значения х.позволяет наглядно отображать тенденцию изменения данных с помощью линии тренда, которая представляет собой интерполяционную кривую, наилучшим образом в соответствии с некоторыми критериями описывающую отложенные на диаграмме данные. Для того чтобы дополнить диаграмму исходных данных линией тренда, необходимо выполнить следующие действия:

Выделить на диаграмме ряд данных, для которого требуется построить линию тренда

-В меню Вставка выбрать команду Линия тренда

В открывшемся окне задать метод интерполяции (линейный, полиномиальный, логарифмический и т.д.), а также другие параметры (например, вывод уравнения интерполяционной кривой, направление и количество периодов для экстраполяции (прогноза) и др.)

Нажать кнопку ОК

2.5 Анализ "Что-Если. Поиск решения

Одним из наиболее важных преимуществ электронных таблиц является возможность быстрого выполнения анализа "Что-Если: изменение значения какой-либо ячейки приводит к пересчету во всех зависящих от нее ячейках. Таблицы данных сокращают такой процесс варьирования до одной операции.

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

Сценарий - это именованный набор изменяемых значений, представляющих некоторое множество параметров модели "Что-Если. Сценарий используется для моделирования более сложных задач с привлечением до 32 параметров.

Модель "Что-Если - это рабочий лист, на котором различным параметром можно задавать различные значения с тем, чтобы определить их влияние на другие параметры, вычисляемые по формулам, зависящим от этих параметров.

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

Из меню Сервис выберите команду Сценарии

-В открывшемся окне Диспетчер сценариев нажмите кнопку Добавить

Введите имя сценария

В поле Изменяемые ячейки задайте те ячейки, которые вы собираетесь изменить

Нажмите кнопку ОК

-В открывшемся диалоговом окне Значения сценария для каждой изменяемой ячейки ведите новое значение или формулу. Нажмите кнопку ОК

-Нажмите кнопку Закрыть

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

Команда Подбор параметра удобна для поиска точного решения, зависящего от одного параметра.

Для решения более сложных задач следует использовать команду Поиск решения

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

3. Задание №1

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

1Разработайте таблицу согласно варианту задания (используйте условные данные). Изучите различные способы ввода формул.

2Изучите способы выделения, копирования и переноса ячеек, строк и столбцов. Получить копию созданной таблицы, скопировав ее по частям.

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

Отформатируйте исходную таблицу "вручную, используя:

-Форматы данных (числовой, денежный, процент, дата и т.д.)

-Обрамление и заполнение ячеек

Различные форматы шрифтов

Выравнивание абзацев

Изменение высоты строк и ширины столбцов

5Отформатируйте вторую таблицу, используя команду Автоформат из меню Формат.

6Подготовьте к печати одностраничный отчет. С этой целью:

оформите рабочий лист в виде фирменного бланка, внедрив в левом верхнем углу листа логотип из файла logo. xls и указав название, адрес и телефон фирмы;

создайте нижний колонтитул, включающий номер страницы в центре, а справа и слева - фамилии авторов отчета.

Вариант 3. Температура воздуха в городах мира с 1 по 7 января 2004 г. (см таб. 2)

Таблица 2 - Температура воздуха в городах мира с 1 по 7 января 2004 г.

ДатаМоскваРимПарижМадридСр. темп. Средняя температура по всем городам:

3.2 Ход работы

Пункт 1.

Создадим таблицу, согласно варианту задания, и заполним её условными данными (см. таблицу 3)

Пункт 2.

Выделяя отдельные части таблицы, скопируем и вставим их (см. таблицу 4):

) С помощью окна буфера обмена, расположенном во вкладке Главная;

) С помощью вызова контекстного меню;

) С помощью комбинации клавиш Ctrl + V;

Пункт 3.

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

Пункт 4.

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

Или контекстным меню ячеек (результат в таб. 5)

Пункт 5.

Отформатируем таблицу, используя заранее предложенные шаблоны. Для этого воспользуемся функцией Форматировать как таблицу во вкладке Главная (результат приведен на рис. 4):

Пункт 6.

Подготовим одностраничный отчёт:

Оформим рабочий лист. Вставим в рабочий лист логотип из файла logo. xls с помощью

·Окна буфера обмена, расположенном во вкладке Главная;

·Вызова контекстного меню;

·Комбинации клавиш Ctrl + V.

Также укажем условный адрес и телефон фирмы (рис. 5).

. Создадим нижние колонтитулы внизу страницы.

Для этого воспользуемся функцией Колонтитулы во вкладке Вставка (рис. 6).

После нажатие на кнопку Колонтитулы появится возможность их настройки (рис. 7).

В правый и левый столбцы мы вписываем фамилии авторов, по центру, с помощью кнопки Номер страницы, устанавливаем нумерацию.

3.3 Ответы на контрольные вопросы и задания

1Как выделить ячейку, блок ячеек, строку, столбец таблицы?

Отдельные ячейки, а также блоки ячеек мы можем выделить с помощью мыши или комбинации стрелок на клавиатуре и клавиши Shift.

Для выделения строки или столбца таблицы необходимо нажать на их "шапку".

Как скопировать данные таблицы с одного рабочего листа на другой?

Копировать данные из одного рабочего листа на другой можно с помощью окна буфера обмена, расположенного во вкладке Главная, вызова контекстного меню или комбинации клавиш Ctrl + V.

Как присвоить формат ячейке? Приведите примеры разных форматов?

Для присвоения формата ячейке необходимо выделить ячейку и во вкладке Главная в разделе Число выбрать нужный формат.

Разновидности форматов: Общий, Числовой, Процентный, Время, Краткая дата т.п.

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

Чтобы ввести формулу нужно:

выделить ячейку и нажать клавишу "=";

-набрать формулу и нажать Enter;

В формулах возможно использовать операторы: сложение (+), вычитание (-), деление (/), умножение (*), возведение в степень (^), сравнения (=, <, >, <>, <=, >=), связь диапазона (:), объединение (;), объединение текстов (&).

Для редактирования формулы можно использовать как саму ячейку, так и строку формул.

Что такое относительная и абсолютная адресация?

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

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

Как отформатировать таблицу в Excel?

Способы форматирования таблиц:

Вручную, используя возможности вкладки Главная;

-С помощью шаблона, который можно выбрать, нажав на кнопку Форматировать как таблицу.

7Как подготовить отсчет для печати?

В версии Excel 2007 параметры, настраиваемые для печати, находятся во вкладках Разметка страницы и Вставка (для Колонтитулов).

Как внедрить логотип на рабочем листе Excel, если он хранится в файле:

а) logo. bmp; б) logo. doc.

Для вставки логотипа из logo. bmp необходимо:

Перейти во вкладку Вставка

-Нажать на кнопку Рисунок

-Указать месторасположение файла logo. bmp

Для вставки логотипа из logo. doc скопировать и вставить рисунок с помощью окна буфера обмена, контекстного меню или комбинации клавиш Ctrl+V.

4. Задание №2

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

1Исходные данные для задания №2 те же, что и для предыдущего.

2На рабочих листах №2-3 создайте не менее 10 различных видов диаграмм.

Научитесь редактировать отдельные элементы диаграмм, изменять их размер и местоположение.

Создайте комбинированную диаграмму со вспомогательной осью.

Вариант 3. Создание и редактирование различных видов диаграмм в Excel.

4.2 Ход работы

Пункт 2.

Для создания диаграмм воспользуемся ранее созданной таблицей.

Чтобы создать диаграмму необходимо выделить таблицу и с помощью элемента Диаграммы во вкладке Вставка выберем необходимый тип диаграмм (рис.8):

После выбора необходимого типа диаграммы появится выбранный тип в рабочем листе (рис. 9).

В этом случае это гистограмма с группировкой.

4.3 Ответы на контрольные вопросы и задания

) 1.1) Ряды данных - это наборы значений, которые требуется изобразить на диаграмме (значение функции - Y).

.2) Категории служат для упорядочения знаний в рядах данных (аргумент функции - X)

.3) Легенда - это условные обозначения значений различных рядов данных на диаграмме.

) В круговой диаграмме - 1 ряд.

В кольцевой диаграмме - несколько.

) 3.1) Вкладка Работа с диаграммами - Макет - Название диаграммы;

.2) Вкладка Работа с диаграммами - Макет - Название осей;

.3) Вкладка Работа с диаграммами - Макет - Легенда;

.4) Вкладка Работа с диаграммами - Конструктор - Изменить тип диаграммы;

.5) Выбрать диаграмму - вызвать Контекстное меню - Выбрать данные - Добавить;

4) Выделить необходимую Ось - вызвать Контекстное меню - вкладка Параметры оси;

) Выбрать один ряд значений в диаграмме - вызвать Контекстное меню - Изменить тип диаграммы для ряда.

5. Задание №3

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

Каждый вариант состоит из двух заданий. Для выполнения первого задания необходимо:

На рабочем листе №4 построить таблицу значений функции согласно варианту задания и ее график.

2Определите среднее, минимальное и максимальное значение функции и вывести эти данные на графике.

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

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

Вариант 3.

= (x3-cos x2) / (e4x) - tg x

Где - 5 <= x <= 5, Dx = 0.75

Исходными данными для второго задания являются варианты заданий к заданию №1. Необходимо:

1. Провести статистический анализ с использованием функций 1-6 методических указаний к работе.

2. Построить гистограмму распределения данных.

5.2 Ход работы

5.2.1 Задание первое

Пункт 1.

Построим таблицу значений и ее график.

Для построения таблицы необходимо сначала ввести значения переменной Х в заданном диапазоне с заданным шагом, а затем используя формулу получаем значения Y (Таб. 6).

Если проанализировать функцию то из этого видно, что в знаменателе присутствует тангенс угла X, и в случае когда Х = 0 тангенс также принимает нулевое значение, а значит функция не существует при Х=0.

Выделим столбец значений функции Y и построим ее график воспользовавшись вкладкой Вставка. Результат приведен на рис. 10.

Пункт 2.

Найдем среднее, минимальное и максимальное значения функции, для этого воспользуемся стандартными функциями Microsoft Excel.

Получаем конечные формулы:

Для расчета минимального значения: =МИН (B2: B22)

Для расчета максимального значения: =МАКС (B2: B22)

Для расчета среднего значения: =СРЗНАЧ (B2: B22)

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

Получаем обновленный вид графика (см рис. 11)

Пункт 3.

Составим логическую формулу, относительно нашей таблицы она примет вид:

=ЕСЛИ (ИЛИ (И (C2>0; D2>0; E2>0); И (C2<0; D2<0; E2<0)); СУММ (B2: B22); ПРОИЗВЕД (B2: B22))

Подставив ее в ячейку получим значение этого логического выражения (рис. 12)

Пункт 4.

Сгенерируем случайное число при помощи стандартной функции Microsoft Excel СЛУЧМЕЖДУ.

Для данного задания она будет выглядеть следующим образом: =СЛУЧМЕЖДУ (0; 100) где 0 и 100 произвольно выбранные границы.

Создадим новый столбец значений умножив столбец y на случайное число (см. рис. 13)

Добавим на уже существующем графике новый график, определенный полученным столбцом (рис. 14)

5.2.2 Задание второе

Пункт 1.

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

СРЗНАЧ, МИН, МАКС, МЕДИАНА, МОДА, ДИСП, ОКРУГЛ. (см. рис. 15)

Пункт 2.

Построим гистограмму распределения данных, воспользовавшись меню вставка - гистограмма (рис.16).

5.3 Ответы на контрольные вопросы

1)Для чего предназначен Пакет анализа и каков порядок доступа к его инструментам?

.1Пакет анализа предназначен для построения гистограмм, произведения случайных или периодических выборки данных и нахождения их статистические характеристики, генерации неравномерно распределённых случайных чисел, проведения регрессивного анализа.

1.2Для доступа к его элементам необходимо:

1Вызвать главное меню программы;

2Выбрать Параметры Excel - Надстройки - Пакет анализа;

3После добавления на вкладке Данные появится кнопка Анализ данных.

2)В задании 2 своего варианта вычислите коэффициент вариации.

Чтобы найти Коэффициент вариации воспользуемся формулой:

где

- Коэффициент вариации;

- среднее квадратичное отклонение;

- средняя величина.

Значит формула Коэффициента вариаций: = (КОРЕНЬ (R18) /R13);

3)В чем заключаются особенности построения гистограммы распределения данных?

Особенность построения гистограммы с распределением данных в том, что для неё необходимы значения статистических данных (среднее значение, дисперсия, медиана и т.п.).

)Напишите логическую формулу, которая выводит текстовое сообщение Вычислена сумма" или Вычислено произведение в зависимости от того, что было вычислено на рабочем листе в п.3 задания 1.

Новая логическая формула будет иметь вид:

=ЕСЛИ (ЗНАК (C2) =ЗНАК (C4) =ЗНАК (C6); "Вычисленасумма"; "Вычислено произведение").

6. Задание №4

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

Вариант задания включает 2 задачи. Для каждой из них необходимо получить:

Составить таблицу исходных данных.

2Прогноз изменения данных, выполненный с использованием линейной и экспоненциальной регрессии.

Коэффициенты в уравнениях прямой и экспоненциальной кривой.

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

На гистограмме (или графике) исходных данных в задаче 1 отобразить линию тренда, а также соответствующее ей уравнение.

Вариант 3.

. Автомобильный салон имеет данные о количестве проданных автомобилей "Мерседес и "БМВ" за последние 4 квартала. Учитывая тенденцию изменения объема продаж. Определить, каких автомобилей ("Мерседес или БМВ) необходимо закупать больше в следующем квартале?

. Известны следующие данные о пяти недавно проданных подержанных автомобилях: стоимость продажи, стоимость аналогичного нового автомобиля, год выпуска, пробег, количество капитальных ремонтов, экспертные заключения о состоянии кузова и техническом состоянии автомобилей (по 10-бальной шкале). Определить, сколько может стоить автомобиль с соответствующими характеристиками: 34 млн. руб., 1990 г., 140000 км, 0, 6, 7.

6.2 Ход работы

6.2.1 Первое задание

Пункт 1.

Составим таблицу начальных значений (рис. 17).

Пункт 2.

Составим прогноз изменения объема продаж в первом квартале нового года, для этого используем функцию ТЕНДЕНЦИЯ для линейной регрессии и функцию РОСТ для экспоненциальной (рис. 18).

=ТЕНДЕНЦИЯ (F4: F7) и =ТЕНДЕНЦИЯ (J4: J7),

=РОСТ (F4: F7) и =РОСТ (J4: J7).

Как видно из результатов, тенденция продаж Мерседес может упасть, а тенденция продаж автомобилей марки БМВ может возрасти. Отвечая на вопрос задачи имеет смысл закупить больше автомобилей с большей прогнозируемой тенденцией продаж, т.е. БМВ.

Пункт 3.

Коэффициент в уравнении линейной регрессии найдем по формуле: =ЛИНЕЙН (F4: F7) и =ЛИНЕЙН (J4: J7), а для экспоненциальной регрессии - =ЛГРФПРИБЛ (F4: F7) и =ЛГРФПРИБЛ (J4: J7). Результаты для линейной регрессии Мерседес и БМВ равны 10,5 и - 12,2 соответственно, для экспоненциальной Мерседес и БМВ равны 1,0286641 и 0,999369631 соответственно.

Пункт 4.

Погрешности вычислений для линейной регрессии и экспоненциальной от МЕРСЕДЕС и БМВ рассчитаем по формулам =СТОШYX (F4: F7; $E$4: $E$7) и =СТОШYX (G4: G7; $E$4: $E$7) соответственно, а для экспоненциальной =СТОШYX (J4: J7; $I$4: $I$7) и =СТОШYX (K4: K7; $I$4: $I$7) соответственно. Результаты приведены на рис.30. В этом случае для решения данной задачи подходят оба способа решения, как с помощью линейной регрессии, так и с помощью экспоненциальной, так как погрешности практически одинаковы.

Пункт 5.

Для обоих таблиц построим гистограммы и добавим линии тренда при помощи меню макет - линия тренда - линейное приближение для линейной регрессии и экспоненциальное приближение для экспоненциальной. Результат приведен на рис. 19 и 20.

6.2.2 Второе задание

Пункт 1.

Построим таблицу начальных данных для линейной регрессии и экспоненциальной (см. рис. 35).

Пункт 2.

Рассчитаем стоимость продажи необходимого автомобиля по аналогичным формулам что и в задаче 1, изменим лишь параметры.

=ТЕНДЕНЦИЯ (B3: B7; C3: H7; C8: H8; ЛОЖЬ) - для линейной регрессии и =РОСТ (B13: B17; C13: H17; C18: H18; ЛОЖЬ) - для экспоненциальной регрессии.

Результат на рис. 21.

Пункт 3.

Коэффициенты рассчитаем используя формулы =ЛИНЕЙН (B3: B7; C3: H7;; ЛОЖЬ) для линейной регрессии и =ЛГРФПРИБЛ (B13: B17; C13: H17;; ЛОЖЬ) для экспоненциальной.

Пункт 4. Рассчитаем погрешности вычислений по формулам =СТОШYX (B3: B8; C3: H8) для прямолинейной регрессии и =СТОШYX (B13: B18; C13: H18) для экспоненциальной.

Не требуется выполнять Пункт 5, так как этого нет в задании.

6.3 Ответы на контрольные вопросы

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

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

. На основании результатов вычислений, полученных с помощью функций ЛИНЕЙН () и ЛГРФПРИБЛ (), напишите уравнения прямой и экспоненциальной кривой для простой и множественной регрессии.

Простая регрессия:

Линейная регрессия:

=-281101,8302*x

Экспоненциальная регрессия:=0,672234727x

Множественная регрессия:

Линейная регрессия:

= 426006,81*x1+186068,053*x2+6,4*x4+2707,94*x5+0,168793685

Экспоненциальная регрессия:

= (0,495592796x) * (0,676074258x) * (1x) * (0,999983514x) * (1,011410682x) * 1,000000288

. Определите погрешность вычислений для каждого коэффициента в уравнении прямой и экспоненциальной кривой.

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

Линейная регрессия: Стандартная ошибка для x 0,00075982

Экспоненциальная регрессия: Стандартная ошибка для x 0,52975662

. Каковы правила ввода и использования табличных формул?

Для введения формулы надо выделить ячейку, в которой будет производиться вычисление, в окне формул ввести знак "=", и напечатать формулу (либо воспользоваться Вставкой функции). В скобках после формулы ввести номера ячеек над данными, в которых будут производиться вычисления.

. Как на гистограмме исходных данных добавить линию тренда?

Для того чтобы дополнить диаграмму исходных данных линией тренда, необходимо выполнить следующие действия:

Выбрать подходящую линию тренда из команд, находящихся во вкладке Работа с диаграммами - Макет - Анализ - Линия тренда

В появившемся окне выбрать тот ряд данных, для которых требуется линия тренда.

. Как с помощью линии тренда отобразить прогнозируемые величины?

Вызвать контекстное меню Линии тренда

2Выбрать Формат линии тренда

Поставить галочки напротив пунктов:

-Показывать уравнение на диаграмме

-Поместить на диаграмму величину достоверности аппроксимации.

7. Задание №5

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

1) Создайте таблицу исходных данных в соответствии с вариантом задания. Для вычислений используйте, где необходимо, формулы.

) Разработайте два-три сценария для своего варианта задания.

) Получите итоговый сценарий и проанализируйте влияние различных сценариев на результирующую ячейку.

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

Вариант 3.

В городе У проводится рекламная кампания нового компакт-диска для подростков. Для двух радиостанций известны: стоимость 1 мин. эфирного времени и процент подростков среди радиослушателей в зависимости от времени суток. Заполните условными данными таблицу, аналогичную таблице варианта 2, считая, что численность подростков 183764 чел. Сколько раз и в какое время в течение суток надо разместить 1 мин рекламы в эфире, чтобы охватить максимальное количество слушателей-подростков. При этом общая сумма затрат не должна превысить 10000$, на каждую радиостанцию может быть потрачено не более 60% выделенных средств.

7.2 Ход работы

Пункт 1.

Создадим таблицу исходных данных, считая что необходимая аудитория подростков более половины, т.е. 50% (см. рис. 22).

Пункт 2.

Создадим несколько сценариев при помощи диспетчера сценариев (см. рис. 23) который расположен в Работа с данными - анализ "что если" - диспетчер сценариев.

Пункт 3.

Для того чтобы получить итоговый сценарий выведем в таблицу все предыдущие сценарии и создадим на основе полученных данных новый "Итоговый сценарий".

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

Пункт 4.

Чтобы воспользоваться опцией поиск решения необходимо вызвать ее в меню анализ - поиск решения. Далее зададим целевую ячейку С9 с параметром равной минимальному значению 10000, изменяемые ячейки и ограничения.

Получаем результат вычисления: по минимальной сумме затрат на радиостанцию 1 необходимо затратить 4800, разместив рекламу 8 раз за сутки, на радиостанцию 2 необходимо затратить 3300, разместив рекламу 6 раз за сутки, при этом общая сумма затрат составит 8100, что удовлетворяет условию поставленной задачи.

7.3 Ответы на контрольные вопросы

1Для чего используется анализ "Что-Если?

Анализ "Что-Если" служит для выяснения того, как изменения значений одних повлияют на результаты выполнения формул на листе.

Что такое сценарий? Как создать и просмотреть сценарий?

Сценарий - это именованный набор изменяемых значений, представляющих некоторое множество параметров модели "Что-Если.

Для создания сценария требуется:

Вызвать команду Диспетчер сценариев, находящуюся во вкладке Данные - Работа с данными - анализ "Что-Если"

2В открывшемся окне нажать кнопку Добавить

3Ввести имя сценария

В поле Изменяемые ячейки задать те ячейки, которые Вы собираетесь изменить. Нажать кнопку ОК

5В открывшемся диалоговом окне для каждой изменяемой ячейки ввести новое значение или формулу. Нажать кнопку ОК

3Как в Excel решаются задачи типа: Найти значение некоторого параметра, при котором значение другого параметра не превысит заданной величины?

Для решения подобной задачи следует воспользоваться командой Поиск решения.

В каких случаях используется команда Поиск решения?

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

Изучите диалоговое окно Параметры поиска решения и с помощью справочной системы Excel ответьте на вопросы:

Как ограничить длительность процесса последовательных приближений к решению?

-Что задает значение в поле Точность диалогового окна Параметры поиска решения?

Как просмотреть промежуточные результаты поиска решения?

Что такое линейная и нелинейная задачи? Как учесть характер задачи при поиске решения в Excel?

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

5.2Это погрешность - чем меньше введенное число, тем выше точность результатов.

.3Их можно посмотреть, вызвав окно отчета Поиска решения.

.4Линейные задачи - это простые задачи, для решения которых достаточно минимальных средств и возможностей MicrosoftExcel. Примером простой задачи можно считать задание на составление обыкновенной таблицы с данными, с использованием пары элементарных формул. Нелинейная задача - это задача, требующая для решения использования большинства возможностей приложения: прогнозирования, статистического анализа, составления сценариев и т.д. Перед решением любой задачи нужно определить для себя, к какому из условных типов ее можно отнести и нужно ли для ее решения использовать "продвинутые" возможности приложения.

6Как задать ограничения задачи? В каких случаях используются целочисленные ограничения?

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

Как сохранить результат поиска решения в виде сценария?

Чтобы сохранить результат Поиска решения в виде сценария в окне Результаты поиска решения (появляется после выполнения операции поиска решения) нажните на кнопку Сохранить сценарий. Далее введите название нового сценария и нажмите ОК.

Заключение

В процессе работы я изучил основные функции и методы работы в табличном процессоре Microsoft Excel.

Сначала я познакомился с интерфейсом Microsoft Excel и научился строить, использовать и редактировать простые таблицы в Excel.

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

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

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

В конце работы я освоил основы анализа Excel "Что-Если", научился создавать и анализировать сценарии, использовать функции Excel Подбор параметра и Поиск решения.

Вывод

Табличный процессор MicrosoftExcel является очень полезной и удобной программой.создан для работы с разнообразными видами таблиц. Кроме того в Excel удобно заниматься статистическим и регрессивным анализом, а так же прогнозированием. Областью применения табличного процессора Excel может быть практически любая сфера деятельности человека, но особенно это программа будет полезна в экономической сфере деятельности.

Список литературы

1.Информатика базовый курс. Акулов О.А., Медведев Н.В. М.: "Омега",-2004.

2.Богомолова О.Б. Готовимся стать сертифицированным специалистом по MS Excel.

3.Пащенко И.Г. Excel 2007

4.Лекции по общей информатике. Под ред. Проф. Атрощенко В.А., КубГТУ, Краснодар-2004.

Приложение. Функции Excel

ABSМодуль (абсолютная величина) числаCOSКосинус числаEXPЭкспонента заданного числаLNНатуральный логарифм числаLOGЛогарифм числа по заданному основаниюLOG10Десятичный логарифм числаSINСинус данного углаTANТангенс числаЗНАКЗнак числаКОРЕНЬПоложительное значение квадратного корняНОДНаибольший общий делительНОКНаименьшее общее кратноеОКРУГЛОкругление числа до указанного количества десятичных разрядовОСТАТОстаток от деленияПИВозвращает число ПИПРОИЗВЕДВозвращает произведение аргументовРИМСКОЕПреобразует число в Арабской записи к числу в Римской как текстСЛЧИСВозвращает случайное число между 0 и 1СЛУЧМЕЖДУВозвращает случайное число между двумя заданными числамиСТЕПЕНЬВозвращает результат возведения в степеньСУММСуммирует аргументСУММЕСЛИСуммирует ячейки, специфицированные заданным критериемСЧЕТЕСЛИПодсчитывает количество непустых ячеек, удовлетворяющих заданному критерию внутри интервалаФАКТРВозвращает факториал числаЧИСЛКОМБВозвращает количество комбинаций для заданного числа объектов

Содержание Введение 1. Постановка задачи исследования 2. Краткий теоретический материал 2.1 Создание и редактирование простейших таблиц в Excel

Больше работ по теме: