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

Контрольные вопросы и задания. 

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

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

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

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

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

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

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

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

    а) logo.bmp

    б) logo.doc 
 

Ответы:

 1) Выделение производиться: перетаскиванием мышкой с нажатой ЛК; нажатой Shift + стрелка.

 2) Выделить данные на одном листе => \Копировать\ => переключиться на другой => \Вставить\.

 3) ПК (контекстное меню) => \Формат ячеек\.

 4) Формула обязана начинаться со знака « = ».

 5) Относительная адресация ячейки – копирование/перемещение ячейки изменит адресацию согласно  новому положению ячейки; абсолютная адресация - адресация при копировании не изменяется.

 6) При помощи панели \Форматирование\ можно изменить: тип линии, ее цвет, фон, выравнивание текста, размер и цвет шрифта, добавлять, удалять, объединять ячейки и т.д.

 7) \Файл\ => \Параметры печати\ => \Печать\.

 8) \Вставка\ => \Рисунок\ => \Из файла…\ => выбрать фаил из списка => \Добавить\. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Задание №2

Создание  и редактирование различных

видов диаграмм в Excel 

Исходные  данные: см. Задание №1.

Задача: На рабочем листе №2 создайте не менее 10 различных видов диаграмм. Научитесь редактировать отдельные элементы диаграмм, изменять их размер и местоположение. Создайте комбинированную диаграмму со вспомогательной осью. 

Выполнение:

 1) Создание диаграммы: \Диаграмма\ => Выбрать категорию => выбрать тип => выбрать вид => \Далее\.

 

 2) Выбрать диапозон значений => выбрать тип построения рядов => \Далее\. В нашем случае: «=’Задание1’!$A$2:$G$7».  

 

 3) Вкладка \Заголовки\ - название диаграммы и осей; вкладка \Оси\ - отображение осей; вкладка \Линии сетки\ - определение градуировки диаграммы; вкладка \Легенда\ - отображение легенды; вкладка \Подписи данных\ - подписи значения столбцов; вкладка \Таблица данных\ - отображение таблицы, по которой диаграмма строилась => \Далее\. 

 

 4) Выбрать расположение диаграммы => \Готово\. 

 

              
 
 
 
 
 
 
 
 
 
 
 

Контрольные вопросы и задания. 

 1) Что означает  понятие: 

   а) ряд данных

   б) категория

   в) легенда

 2) Сколько рядов  данных может быть изображено  на круговой диаграмме; на кольцевой диаграмме?

 3) Как отредактировать  на диаграмме: 

   а) название диаграммы

   б) название осей X и Y

   в) легенду

   г) изменить тип диаграммы

   д) добавить новые данные

 4) Как указать  на гистограмме ключевые значения  ряда данных?

 5) Каким образом можно комбинировать разные типы диаграмм? 
 

Ответы: 

 1)

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

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

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

2) В круговой диаграмме допускается только один ряд данных, тогда как в кольцевой – несколько.

3)

   а) двойной щелчок на   названии.

   б) двойной  щелчок на   названии.

   в) ПК на легенде => \Формат легенды\; ПК на поле диаграммы => \Параметры диаграммы\ => \Легенда\.

   г) ПК на поле диаграммы => \Тип диаграммы\.

   д) ПК на поле диаграммы => \Исходные данные\. Указать новый диапазон данных.

6)

5) Выбрать тип Смешанная.

                                     
 
 
 
 

Задание  № 3.

Встроенные  функции EXCEL.

Статистический  анализ. 

Исходные  данные: Y = lg x² e^{sin 2x} /lg3x, 1 <= x <= 100, Dx = 5.

Задача:

 а) На рабочем листе №4 построить таблицу значений функции согласно варианту задания и ее график. Определите среднее, минимальное и максимальное значение функции и вывести эти данные на графике. Используя логическую формулу, вычислить сумму значений функций, если среднее, минимальное и максимальное значения имеют одинаковые знаки и произведение в противном случае. Произвольной ячейке присвоить имя и сгенерировать в ней случайное число. В таблице значений функции добавить еще один столбец, полученный умножением «у» на случайное число. Добавить на графике функции второй график, соответствующий полученному столбцу данных.

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

Выполнение:

 1) Заполнение столбца  значений Х: введем в А2 значение  «1» => \Правка\ => \Заполнить\ => \Прогрессия\ => указать тип, шаг, предельное значение и направление заполнения => \Ок\. 

 

 2) Заполнение столбца значений Y: введем в В2 формулу вручную => автозаполнение. Выполнение автозаполнения: выделить ячейку с формулой => навести курсор на правый нижний угол ячейки (курсор изменит вид) => растянуть рамку на все необходимые ячейки. В нашем случае: «=LOG10(A2)^2*EXP(SIN(2*(A2)))/LOG10(3*(A2))». 
 
 
 
 

 3) Вычисление максимального значения функции: \Вставка\ => \Функция\ => \Статистические\ => выбрать из списка «МАКС» => ввести диапозон ячеек => \Ок\. В нашем случае: «=МАКС(B2:B101)». 

 

 4) Вычисление минимального  значения функции: \Вставка\ => \Функция\ => \Статистические\ => выбрать из списка «МИН» => ввести диапазон значений => \Ок\. В нашем случае: «=МИН(B2:B101)».

 5) Вычисление среднего  значения функции: \Вставка\ => \Функция\ => \Статистические\ => выбрать из  списка «СРЗНАЧ» => ввести диапазон  ячеек  => \Ок\. В нашем случае: «=СРЗНАЧ(B2:B101)». 

 
 

 6) Логическое вычисление: \Вставка\ => \Функция\ => \Логические\ => выбрать из списка «ЕСЛИ» => в поле «Лог.выражение» нажать \Вставка\ => \Функция\ => \Логические\ => выбрать из списка «ИЛИ» => в поле «Лог.выпажение1» нажать \Вставка\ => \Функция\ => \Логические\ => выбрать из списка «И» => в поле «Лог.выпажение1,2,3» ввести B101>=0, В102>=0, B103>=0 соответственно => вернуться к «ИЛИ» (нажать на «ИЛИ» в \Строка формул\) => в поле «Лог.выражение2» нажать \Вставка\ => \Функция\ => \Логические\ => выбрать из списка «И» => в поле «Лог.выражение 1,2,3» ввести В101<=0, B102<=0, B103<=0 соответственно => вернуться к «ЕСЛИ» => в поле «Истина» нажать \Вставка\ => \Функция\ => \Математические\ => из списка выбрать «СУММ» => ввести диапазон ячеек =>вернуться к «ЕСЛИ» => в поле «ЛОЖЬ» нажать \Вставка\ => \Функция\ => \Математические\ => из списка выбрать «ПРОИЗВЕД» => ввести диапозон ячеек => \Ок\. В итоге: «=ЕСЛИ(ИЛИ(И(B102>=0;B103>=0;B104>=0);И(B102<=0;B103<=0;B104<=0));СУММ(B2:B101);ПРОИЗВЕД(B2:B101))».

 7) Генерация случайного числа: \Вставка\ => \Функция\ => \Математические\ => из списка выбрать «СЛЧИСЛ» => \Ок\.

 8) Заполнение столбца значений Y*СЛЧИСЛ: ввод формулы вручную => автозаполнение.

 9) Вычисление медианы: \Вставка\ => \Функция\ => \Статистические\ => из списка выбрать «МЕДИАНА» => ввести диапозон => \Ок\.

 10) Вычисление моды: \Вставка\ => \Функция\ => \Статистические\ => из списка выбрать «МОДА» => ввести диапозон => \Ок\.

 11) Вычисление дисперсии:  \Вставка\ => \Функция\ => \Статистические\ => из списка выбрать «ДИСП» => ввести диапозон => \Ок\.

 12) Вычисление стандартного  отклонения: \Вставка\ => \Функция\ => \Статистические\ => из списка выбрать «СТАНДОТКЛОН» => ввести диапозон => \Ок\.

 13) Добавление графиков: см. Задание №2.

 14) Построение гистограммы  распределения данных: \Сервис\ => \Анализ данных\ => \Гистограмма\ => ввести входной интервал, интервал карманов, выходной интервал => \Ок\. 
 
 
 
 
 
 
 
 
 
 
 

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

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

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

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

 4) Используя информацию о том, что “как правило, 68% данных генераций совокупности с нормальным распределением находятся в пределах одного стандартного отклонения от среднего значения, а 98% - в пределах двух отклонений”, создайте на рабочем листе строку, в которой для задания №1 автоматически будут рассчитываться указанные интервалы. 
 

Ответы: 

 1) Пакет анализа – это  дополнение EXCEL, расширяющее его аналитические возможности и позволяющие строить гистограммы, делать случайные или периодические выборки данных и находить их статистические характеристики, генерировать неравномерно распределенные случайные числа, проводить регрессивный анализ и т.д.

 2) Область значений измеряемой величины разбивается на несколько интервалов, называемых карманами, в которых в виде столбцов откладывается количество попавших в этот интервал измерений, называемое частотой.

 3) =ЕСЛИ(G1+G2+G3+G4=СУММ(G1,G2,G3,G4);"Вычислена сумма") 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Задание №4.

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

Часть 1.

Исходные  данные:                                       

 Для некоторого региона  известен среднегодовой доход населения. А также данные о структуре  расходов (тыс. руб. в год) за последние  пять лет по следующим статьям: питание, жилье, одежда, здоровье, транспорт, отдых, образование.

Задача:

 На основании  известных данных провести анализ потребительского кредита (или накопления) в следующем  году.

Выполнение:

 1) Создание таблицы: ячейкам А3:А9 присвоим имена статей расходов => ячейкам В2:F2 присвоим № года => ячейки В3:F9 заполним случайными значениями.

 2) Рассчет среднего расхода за год: в B3 => \Вставка\ => \Функция\ => \Статистические\ => выбрать из списка «СРЗНАЧ» => ввести диапазон =>  => \ОК\ => выполнить aвтозаполнение по всем годам.

 3) Выполнение линейной регрессии: \Вставка\ => \Функция\ => \Статистические\ => выбрать из списка «=ТЕНДЕНЦИЯ» => ввести диапазоны => \Ок\.

 4) Выполнение экспоненциальной регрессии: \Вставка\ => \Функция\ => \Статистические\ => выбрать из списка «=РОСТ» => ввести диапазоны => \Ок\.