России по линии тренда составит 131 млн. чел. Задание 3. Заполните Таблицу

Район города				Этажность домов
	1 эт.	2 эт.	3 эт.	4 эт.	5 эт.	7 эт.		9 эт.	12 эт.	>12
Центральный	245	511	30	0	1356	1657		2345	3983	57
Северный	123	345	1	4	478	341		2496	2034	14
Южный	12	2	0	0	0	5246		1657	3156	245
Западный	2637	2104	1670	0	0	0		23	435	0
Восточный	302	23	0	0	145	1436		3547	1263	143

Задание 4. Построить сравнительную характеристику этажности домов по районам.

•Выделить область данных A2:J7

•Вызовите мастер диаграмм

•Шаг 1. Выбрать тип и вид диаграммы (гистограмма плоская)

•Шаг 2. На вкладке Диапазон данных проверить диапазон данных и их расположение (в столбцах). На вкладке Ряд проверить области Имя, Значения и Подписи по оси X.

•Шаг 3. На вкладке Заголовки впишите название диаграммы и названия осей.

•Шаг 4. Выбрать размещение диаграммы на этом же рабочем листе.

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

Дополнительные задания.

1. Задачи оптимизации (поиск решения)

Задание 1. Составление плана выгодного производства.

Фирма производит несколько видов продукции из одного и того же сырья – А, В и С. Реализация продукции А дает прибыль 10р., В - 15р. и С – 20р. на единицу изделия.

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

1) Запустите редактор элек-

тронных таблиц Microsoft Excel

и создайте новую электронную книгу.

33

2)Создайте расчетную таблицу как на рис. 4. Введите исходные данные и формулы в электронную таблицу.

3)Расчетные формулы имеют такой вид:

(Расход сырья 1) = (количество сырья 1) * (норма расхода сырья А)+(количество сырья 1)*(норма расхода сырья В) + (количество сырья 1) *(норма расхода сырья С).

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

(Общая прибыль по А) = (прибыль на ед.изделий А) * (количество А)

Итоговая общая прибыль = (Общая прибыль по А) + (Общая прибыль по В) + (Общая прибыль по С)

4)В меню Сервис активизируйте команду Поиск решения и введите параметры поиска, как указано на рис 5.

В качестве целевой ячейки укажите ячейку «Итоговая общая прибыль»(E10), в качестве изменяемых ячеек – ячейки ко-

личества сырья (B9:D9)

5)Установите параметры поиска решения. Для этого кноп-

кой Параметры откройте диалоговое окно Параметры поиска решения, устано-

вите параметры Сходимость → Линейная модель. Кнопкой Выполнить запустите Поиск решения.

6)Сохраните созданный документ под именем «План производства».

2.Связи между файлами. Консолидации данных в MS Excel

Задание 1. Задать связи между файлами.

1)Запустите редактор электронных таблиц Microsoft Excel

2)Создайте 3 электронных книги и назовите их (1 квартал, 2 квартал, полуго-

дие).

3)В каждой книги создайте таблицу «Отчет о продажах » по образцу рис.1.

4)Проведите расчет Прибыли в каждой из книг: Прибыль = Доходы - Расходы.

5)Для расчета полугодовых итогов свяжите формулами файлы «1 квартал» и «2 квартал»

Формула для расчета: Доход за полугодие = Доход за 1 квартал + Доход за 2

квартал.

!!!Чтобы вставить в формулу адрес ячейки или диапазона ячеек из другого файла (файла-источника), щелкните мышью по этим ячейкам, при этом расположите окна файлов на экране так, чтобы они не перекрывали друг друга.

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

В ячейке В3 файла «Полугодие» формула для расчета полугодового дохода имеет следующий вид:

34

='[1 квартал.xls]Лист1'!$B$3+'[2 квартал.xls]Лист1'!$B$3

6)Аналогично рассчитайте полугодовые значения Расходов и Прибыли, используя данные файлов «1 квартал» и «2 квартал». Результаты работы представлены на рис.1. Сохраните текущие результаты расчетов.

Задание 2.

Обновить связи между файлами.

1)Закройте файл «Полугодие» предыдущего задания.

2)Измените значения «Доходы» в файлах первого и второго кварталов, увеличив их на 100 р.:

Доходы 1квартала =334,58 Доходы 2квартала =552,6. Сохраните изменения и закройте файлы.

3)Откройте файл «Полугодие». Одновременно с открытием файла появится окно с предложением обновить связи. Для обновления нажмите кнопку ДА. Проследите, как изменились данные файла «Полугодие»

4)Изучим процесс ручного обновления связи.

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

5) Откройте файл «Полугодие». Одновременно с открытием файла появится окно с предложением обновить связи, нажмите кнопку Нет.

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

6) Расположите его так, чтобы были видны данные файла «Полугодие», выберите Файл «1 квартал» и нажмите кнопку обновить и проследите как изменились данные файла «Полугодие»

Задание 3

Консолидация данных для подведения итогов по таблицам данных исходной структуры.

1)Откройте все три файла задания 2 и в файле «Полугодие» в колонке В удалите все численные значения данных. Установите курсор в ячейку B3

2)Выполните команду Данные → Консолидации. В появившемся окне Консолидация выберите функцию — «Сумма».

3)В строке «Ссылка» сначала выделите в файле «1 квартал» диапазон ячеек В3:В5 и нажмите кнопку Добавить, затем выделите в файле «2 квартал» диапазон ячеек В3:В5 и снова нажмите кнопку Добавить. В списке диапазонов будут нахо-

35

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

3. Использование электронных таблиц Excel 2000 для вычисления выборочных характеристик данных

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

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

1. Характеристика пакета Excel

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

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

2. Использование специальных функций

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

Функция СРЗНАЧ вычисляет среднее арифметическое из нескольких массивов (аргументов) чисел. Аргументы число1, число2, ... — это от 1 до 30 массивов для которых вычисляется среднее.

Функция МЕДИАНА позволяет получать медиану заданной выборки. Медиана - это элемент выборки, число элементов выборки со значениями больше которого и меньше которого равно.

Функция МОДА вычисляет наиболее часто встречающееся значение в выборке. Функция ДИСП позволяет оценить дисперсию по выборочным данным. Функция СТАНДОТКЛОН вычисляет стандартное отклонение.

Функция ЭКСЦЕСС вычисляет оценку эксцесса по выборочным данным. Функция СКОС позволяет оценить асимметрию выборочного распределения. Функция КВАРТИЛЬ вычисляет квартили распределения. Функция имеет

формат КВАРТИЛЬ(массив, значение), где массив – интервал ячеек, содержащих значения СВ; значение определяет какая квартиль должна быть найдена (0 – минимальное значение, 1 – нижняя квартиль, 2 – медиана, 3 – верхняя квартиль, 4 – максимальное значение распределения).

36