Создание и редактирование таблиц

Раздел 6 Табличный процессор Excel

 

Лабораторная работа №1

 

Создание и редактирование таблиц

Цель работы: научиться создавать и редактировать таблицы

Содержание работы:

1 Настройка новой книги

2 Ввод данных и формул

3 Форматирование ячеек

Настройка новой книги

Вкладка Общие: ▪ Снять флажок в строке Стиль ссылок R1C1, чтобы строки и столбцы… ▪ Листов в новой книге – 4

Ввод данных и формул

Ввод формул. Помимо различных типов данных ячейки таблицы Excel могут содержать формулы. Любая формула в Excel начинается со знака «=»! В формулах допустимо использование следующих операторов: вычитание (-),… 1) выделить ячейку и нажать клавишу «=» (равно);

Форматирование ячеек

Примеры форматов: Содержимое ячейки Формат Результат 1234,567 Числовой 1 234,567 …   Присвоить формат ячейке или блоку ячеек, предварительно выделив их, можно с помощью команды Ячейки из меню Формат или…

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

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

2 Как сделать копию с листа и дать ей другое имя?

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

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

5 Как изменить ширину и высоту ячейки?

6 Каковы правила ввода и редактирования данных в Excel?

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

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

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

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

11 Как внедрить логотип на рабочем листе Excel ?

12 Что такое колонтитул и как он вводится в лист?

 

Задание

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

2 Создайте две копии разработанной таблицы, скопировав ее по частям (ячейками, столбцами, строками, интервалами ячеек).

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

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

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

- обрамление, узор и заполнение ячеек;

 

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

- выравнивание абзацев:

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

В результате каждая ячейка таблицы должна быть отформатирована

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

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

5 На Листе 2 подготовьте к печати одностраничный отчет по лабораторной работе. Сделайте копию с Лист 2и переименуйте егов Отчёт 1.

С этой целью :

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

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

6 Пункты 1-4 задания выполните на Листе 1.

В итоге на Листе 2 должны быть:

- таблица-оригинал (основная таблица )

- таблица (копия –1), отформатированная вручную ,

- бланк отчёта, подготовленный для печати с таблицей (копия 2) в центре, адресом и телефоном фирмы, её логотипом, колонтитулами и др...

 

Варианты заданий.

Вариант 1 Сравнительная таблица розничных цен на продовольственные товары по городам Северного Кавказа (руб. за 1 кг)

Товар	Краснодар	Ростов	Ставрополь	Майкоп	Нальчик	Средняя цена
Говядина
Свинина
Птица
Рыба

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

Вариант 2 Данные о продаже автомобилей в 200__г.

Марка	1 квартал	%	2 квартал	%	3 квартал	%	4 квартал	%
БМВ
Форд
Ауди

 

 

Рено
Всего

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

проданных в данном квартале машин из ячеек строки «Всего» .

Вариант 3 Температура воздуха в городах мира с 1 по7 января 200__г.

Дата	Москва	Рим	Париж	Лондон	Мадрид
01 янв 2000
02.01.2000
03.1.2000
04.01.2000
5 янв
6.01
07.01.2000 13:30
Средн. темп -ра
Общая средняя. по всем городам

В ячейке строки «Средняя температура» ввести формулу для вычисления среднего значения температуры в столбце, в строку «Общая средняя по всем городам» – формулу для вычисления среднего от содержимого ячеек строки «Средняя температура».

Вариант 4 Перечень доходов и расходов за 1-е полугодие.

Статья Месяц	Янв.	Февр.	Март	Апр.	Май	Июнь
Доходы
Оклад
Премия
Совместительство
Всего доходов
Расходы
Жилье
Кредит
Питание
Налоги
Другие расходы
Всего расходов
Остаток

 

 

Остаток за полугодие

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

«Остаток за полугодие» – сумма содержимого ячеек строки «Остаток».

Вариант 5.Выручка от продажи книжной продукции в 200_ г.

Название книги	1 кв.	2 кв.	3 кв.	4 кв.	Процент за год
1.
2.
3.
4.
5.
6.
Всего за год

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

Вариант 6Итоговые экспертные оценки (от 0 до 10 баллов) кандидатов на должность генерального директора фирмы «Аква».

Номер кандидата	Эксперты	Сумма баллов	Процент
№1
№2
№3
№4
№5
Всего:

В столбце «Процент» вычисляется процентное отношение ячеек столбца «Сумма баллов» от значения ячейки строки «Всего».

Вариант 7 Прайс-лист фирмы «Трианон» на 22 декабря 200_ г.

Наименование товара	Рознич. цена, руб.	От 10 шт.	От 100 шт.	Свыше 1000 шт.	Диллеры
1.
2.
3.
4.

 

5.
6.

Формулы в ячейках столбцов «От 10 шт. ... Диллеры» должны учитывать, что цена уменьшается на 1 процент по сравнению с предыдущей

колонкой.

Вариант 8Результаты зимней сессии студентов группы 200_-Эк- 52

Фамилия И.О.	Математика	Информатика	Иност.. язык	Маркетинг	Сумма	Средн. балл
1.
2.
3.
4.
5.
6.

В столбцах «Сумма» и «Средний балл» должны быть введены соответствующие формулы.

 

Вариант 9Анализ цен на товары в конкурирующих фирмах.

Наименование товара	Название фирмы
ВЛАДОС	ОК	Контраст
1.
2.
3.
4.
5.
Сумма, руб.

 

Вариант 10 Изменение курса валют за период 1 по 5 марта 200_г.

Дата	Доллар	Рубль	Доллар/Рубль
1 мар 2000
2 мар 2000
3 мар 2000
4 мар 2000
5 мар 2000
Средний за период
Процент роста

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

 

значения в столбце, в строку «Процент роста» - формулы для определения процентного прироста курса на5 марта относительно курса на 1 марта 200_г.

Вариант 11 Сравнительная таблица розничных цен

 

Товар	Краснодар	Ростов	Ставрополь	Майкоп	Нальчик	Средняя цена
Цемент
Алебастр
Толь
Битум

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

Вариант 12 Данные о продаже автомобилей в 200__г.

Марка	1 кварт	%	2 кварт	%	3 кварт	%	4 кварт	%
Волга
Москвич
Жигули
Газель
Бычок
Всего

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

 

Вариант 13 Температура воздуха в городах мира с 1 по 5 января 200__г.

Дата	Вена	Рим	Париж	Лондон	Осло
01.01. 200…
02.01.200…
03.1.200…
04.01.200…
05.01.200…
Средн. темп -ра
Общая средняя. по всем городам

В ячейкм строки «Средняя температура» ввести формулу для вычисления среднего значения температуры в столбце, в строку «Общая средняя по всем городам» – формулу для вычисления среднего от содержимого

 

ячеек строки «Средняя температура».

Вариант 14 Перечень доходов и расходов за 1-е полугодие.

 

Статья Месяц	Янв.	Февр.	Март	Апр.	Май	Июнь
Доходы
Оклад
Премия
Прибыль
Всего доходов
Расходы
Жилье
Кредит
Питание
Налоги
Всего расходов
Остаток
Остаток за полугодие

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

 

Вариант 15.Выручка от продажи книжной продукции в 200_ г.

Название книги	1 кв.	2 кв.	3 кв.	4 кв.	% за год
1.
2.
3.
4.
5.
6.
Всего за год

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

 

Вариант 16Итоговые экспертные оценки (от 0 до 10 баллов) кандидатов на должность генерального директора фирмы «Аква».

 

 

Номер кандидата	Эксперты	Сумма баллов	Процент
№1
№2
№3
№4
№5
Всего:

В столбце «Процент» вычисляется процентное отношение ячеек столбца «Сумма баллов» от значения ячейки строки «Всего».

 

Вариант 17 Прайс-лист фирмы «Трианон» на 22 декабря 200_ г.

Наименование товара	Рознич. цена, руб.	От 10 шт.	От 100 шт.	Свыше 1000 шт.	Диллеры
1. Шторы
2. Занавес
3. Карнизы
4.Ролл-ставни
5.
6.

Формулы в ячейках столбцов «От 10 шт. ... Диллеры» должны учитывать, что цена уменьшается на 1 процент по сравнению с предыдущей колонкой.

 

Вариант 18Результаты зимней сессии студентов группы 0_-Эк- 52

Фамилия И.О.	Математика	Информатика	Иност.. язык	Маркетинг	Сумма	Средн. балл
1.
2.
3.
4.
5.
6.

В столбцах «Сумма» и «Средний балл» должны быть введены соответствующие формулы.

 

 

 

Вариант 19Анализ цен на товары в конкурирующих фирмах.

Наименование товара	Название фирмы
Санрайз	ОК	Иманго
1.
2.
3.
4.
5.
Сумма, руб.

Вариант 20 Изменение курса валют за период 1 по 5 марта 200_г.

Дата	Доллар	Евро	Доллар/Евро
1 мар 2000
2 мар 2000
3 мар 2000
4 мар 2000
5 мар 2000
Средний за период
Процент роста

В столбце «Доллар/Евро» должна быть формула для вычисления отношения курсов доллара и марки в соответствующей строке. В строку «Средний за период» вводятся формулы для вычисления среднего значения в столбце, в строку «Процент роста» - формулы для определения процентного прироста курса на 5 марта относительно курса на 1 марта 200_г.

 

Содержание отчёта

1Название, цель, содержание работы

2 Задание своего варианта

3 Письменные ответы на контрольные вопросы

4 Выводы по работе

На дискете должны быть сохранены две таблицы и фирменный бланк.

 

 

Лабораторная работа №2

 

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

Цель работы:научиться создавать и редактировать графики и

диаграммы с помощью приложения Мастер диаграмм.

Содержание работы:

1 Построение двумерных и трехмерных диаграмм

2 Редактирование диаграмм

3 Построение нестандартных диаграмм

 

Построение двумерных и трехмерных диаграмм

Ряды данных - это наборы значений, которые требуется изобразить на диаграмме (значения функции на оси Y). Например, при построении диаграммы дохода… Категории (аргументы функции на оси Х) служат для упорядочения значений в… Легенда - это условные обозначения значений различных рядов данных на диаграмме.

Редактирование диаграмм

Для добавления на диаграмме названий, легенд или меток данных можно воспользоваться командой Параметры диаграммы из меню Диаграмма. Если названия на… Часто возникает необходимость дополнить диаграмму новыми данными. Для этого…

Построение нестандартных диаграмм

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

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

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

а) ряд данных; б) категория ;в) легенда?

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

на кольцевой диаграмме?

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

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

б) названия осей Х и Y;

в) легенду ;

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

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

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

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

6 Как ввести вспомогательную ось?

 

Задания

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

2 Построить в Excel график функции:

y=n·sin(2x), -5 <= х <=5,шаг Δх = 1,

где n – номер компьютера в аудитории.

3 Построить 4 двумерные и 4 трехмерные диаграммы любых типов, содержащих не менее 3 рядов данных (функций) для 5-6 значений категорий (аргумента функций). Виды функций взять из л.р №1.

4 Построить график изменения цены товара во времени согласно табл.1.

Изменение цены товара Таблица 1

Интервал времени	Январь	Февраль	Март	Апрель
Стоимость, руб.
Стоимость, доллар.

 

В графике использовать две оси ординат, на одной цена товара должна быть приведена в рублях, на другой – в долларах (с помощью диаграммы График(2 оси).

5 Изменить цвет осей, шрифт названий графика и осей, ввести сетку.

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

 

Содержание отчёта

1Название, цель, содержание работы

2 Задание своего варианта

3 Письменные ответы на контрольные вопросы

4 Выводы по работе

На дискете должны быть сохранены результаты работы

 

 

Лабораторная работа №3

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

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

Цель работы:научиться работать с Мастером функций,

проводить анализ данных.

Содержание работы:

1Использование Мастера функций

2Анализ статистических данных

3Инструменты пакета анализа.

 

Использование Мастера функций.

Обращение к каждой функции состоит из двух частей: имени функции и аргументов в круглых скобках. Аргументы функции могут быть следующих типов: 1)числовые константы, например, функция ПРОИЗВЕД(2;3) вычисляет произведение… 2)ссылки на ячейки и блоки ячеек (функция ПРОИЗВЕД (А1;С1:СЗ) вычисляет произведение содержимого ячеек А1,С1,С2иС3,…

Анализ статистических данных

1 .Вычисление среднего арифметического последовательности чисел: =СРЗНАЧ(числа). Например: =СРЗНАЧ(5;7;9) , =СРЗНАЧ(А1 :А10;С1 :С10), =СРЗНАЧ(А1:Е20).

Инструменты пакета анализа

ПАКЕТ АНАЛИЗА –дополнение EXCEL расширяющее аналитические возможности и позволяющее строить гистограммы, составлять таблицы ранг и персентиль,… Чтобы воспользоваться инструментами анализа, выполните следующие действия: 1) В меню Сервис выберите команду Анализ данных....

Задание

Каждый вариант состоит из двух заданий.

Для выполнения первого задания необходимо:

1 На рабочем листе построить таблицу значений функции согласно

 

варианту задания и ее график (см. л.р №2 «Создание и редактирование диаграмм и графиков в Excel “).

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

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

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

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

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

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

Варианты заданий:

1 y=cos²2·x + x -1=<х=<1.5, ∆х=0.2 ·

2 y= x + e^x +tg(3·x·lgx²) -10=<x=<10 ∆x=l

3 y=(x³ - cos(x²)/e^4x - tgx -5=<x=<5 , ∆x=0.75

4 y=(|x+e^x|)^l/2+ln|xsinx| -1.8=<х=<1.5 ∆х =0.4

5 y=x^cosx/(|x+exp(x)(2+tgx)| -5.2=<х=<1.5 ∆х=0.7

6 y=lgx²e^sm2x/lg3x 1=<х=<100 ∆х=5

7 у=е^х+2+1п²(2х)/(х+10е^х) 1=<х==<50 ∆х=2.5

8 y=|sin2x+tg3xl^l/2+e^4x -2.5=<х=<1.5 ∆х=0.4

9 y= l-|sinx|+e^(ln2x+lgx) 1=<х=<10 ∆х=0.1

10 y=(-l)^xe^sinxcosx² 1=<х=<15 ∆х=1

11 y=sin²(2·x) + x -1=<х=<1.5, ∆х=0.2 ·

12 y= sin(x + e^x) +tg(3·x·lgx² ) -10=<x=<10 ∆x=l

13 y=(x³ - 4cos(x²)/e^4x - tgx -5=<x=<5 , ∆x=0.75

14 y=(|x+e^x|)^l/2+lnx^sinx -1.8=<х=<1.5 ∆х =0.4

15 y=x^cosx/(|x+exp(x)(1+tgx)| -5.2=<х=<1.5 ∆х=0.7

16 y=lgx² sm2x/lg3x 1=<х=<100 ∆х=5

17 у=е^х+2+ln²(2х)/(х+10е^х) 1=<х==<50 ∆х=2.5

18 y=|sin2x+tg3x^0,5l/2+e^4x -2.5=<х=<1.5 ∆х=0.4

19 y= l-|sinx|+e^ln2x+lgx 1=<х=<10 ∆х=0.1

20 y=(-l)^xe^sinxcosx² 1=<х=<15 ∆х=1

 

 

Содержание отчёта

1Название, цель, содержание работы

2 Задание своего варианта

3 Письменные ответы на контрольные вопросы

4 Выводы по работе

На дискете должны быть сохранены результаты работы

 

 

Лабораторная работа N4

 

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

С помощью регрессионного анализа

Цель работы: научиться выполнять прогнозирование экономических

параметров с помощью одномерного и многомерного

регрессионного анализа

Содержание работы:

1 Линейный регрессионный анализ.

2 Экспоненциальный регрессионный анализ.

3 Линейный многомерный регрессионный анализ

 

Линейный регрессионный анализ

Регрессия - это статистический метод, который позволяет найти уравнение, наилучшим образом описывающее совокупность данных, заданных таблицей. …   На графике эти данные отображаются точками. Регрессия позволяет подобрать к этим точкам кривую у=f(x), которая…

Х х

Рисунок 1 Линейная регрессия Рисунок 2 Нелинейная регрессия

 

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

 

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

y=mx+b,(1) где: х - независимая переменная ;

Экспоненциальный регрессионный анализ.

y=b·mx (2) которая позволяет наилучшим образом провести экспоненциальную кривую по точкам данных, которые изменяются нелинейно.

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

у=mlxl + m2x2 + ... + mnxn + b(3) у = b·mlxl· m2x2 · ... · mnxn(4) где:

Использование функций регрессии

1 Функция =ЛИНЕЙН(изв._знач._y;изв. _знач.x;конст;стат)(5) вычисляет коэффициентm и постоянную b для уравнения прямой (1). Известные_значения_y и известные_значения_x - это множество значений y и необязательное множество значений x (их…

ИНДЕКС(ЛИНЕЙН(изв_знач_y;изв_знач_x);1)

3 y-пересечение b:

ИНДЕКС(ЛИНЕЙН(изв_знач_y;изв_знач_x);2)

4 В случае экспоненциальной регрессии аналогом функции (5) является функция =ЛГРФПРИБЛ(изв_знaч_у; изв_ знач_х; конст;стат),(6)которая отличается… 5 Функция

Линия тренда

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

Простая линейная регрессия

а) Предположим, что фирма желает приобрести земельный участок в июле. Фирма собирает информацию о ценах за последние 12 месяцев, начиная с марта, на… =ТЕНДЕНЦИЯ (В2:В13;А2:А13;А14:А18;С2:С6).(10) После нажатия клавиш Ctrl+Shift+Enter данная функция будет введена как формула вертикального массива, а в ячейках…

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

Условие примера 2. Поскольку функция в табл. 2 носит явно нелинейный характер, целесообразно… Выделим для результата блок ячеек F8:G12, введём в строку формул

Линейный многомерный регрессионный анализ

y-оценочная цена здания под офис; x1-общая площадь в квадратных метрах; x2-количество офисов ;

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

1 Сущность регрессионного анализа, его использование для прогнозирования функций.

2 Как получить уравнение одномерной линейной регрессии, каков синтаксис функций линейного приближения?

3 Как получить уравнение многомерной линейной регрессии, каков синтаксис функции?

4 Как получить уравнение одномерной экспоненциальной регрессии, каков синтаксис функции экспоненциального приближения?

5 Как получить уравнение многомерной экспоненциальной регрессии, каков синтаксис функции экспоненциального приближения?

6 Что выполняют функции ЛИНЕЙН, ТЕНДЕНЦИЯ, РОСТ, ЛГРФПРИБЛ, ПРЕДСКАЗ?

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

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

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

Задание

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

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

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

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

4. Прогноз изменения данных, выполненный с использованием линей­ной и экспоненциальной регрессии (функции ТЕНДЕНЦИЯ, ПРЕДСКАЗ, РОСТ).

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

Варианты заданий.

(номер варианта соответствует номеру компьютера)

Вариант 1

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

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

2 Известна структура расходов фирмы на рекламу в газетах, на радио, в журналах, на телевидении, на наружную рекламу ( в процентах от общей суммы ), а также оборот фирмы в каждом за последние 6 месяцев. Какой оборот можно ожидать в следующем месяце, если предполагается следующая структура расходов на рекламу: газеты-40%, журналы-40%, радио-5%, телевидение-14%, наружная реклама-1%.

 

 

 

 

Для выполнения задания нужно составить таблицу со столбцами вида:

Месяц	Х1 – газеты, %	Х2 –журналы, %	Х3 – радио, %	Х4 – телевид.,%	Х5 – наружн. рекл.%	Оборот, $.
Январь
. . .	. . .	. . .	. . .	. . .	. . .	. . .
Июнь

и сделать множественный регрессионный прогноз .(см. Задание).

Вариант 2

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

Для выполнение задания нужно составить таблицу вида:

Годы			. . .
х1 – хлеб, кг	0,5	26,7	. . .	42,8
х2 –молоко, л	0,45		. . .	39,5
у – доход, р.			. . .

и получить два уравнения – у =f (x₁) и у= f(х₂) , сделать прогноз на следующий год для рядов х₁, х₂, у и др. .(см. Задание).

2 Руководство фирмы провело оценку качеств пяти рекламных агентов по следующим признакам: х₁- эрудиция, х₂- энергичность, х₃- умение работать с

людьми, х₄ - внешность, х ₅- знание предметной области. Полученные средние оценки, нормированные от 0 до 1, были сопоставлены с оценками эффективности деятельности агентов ( % успешных сделок от количества возможных). Определить, какую эффективность можно ожидать от рекламного агента, обладающего усредненными качествами. Сравнить ее со средней эффективностью упомянутых 5 агентов.

Исходные данные нужно ввести в таблицу вида:

	A	B	C	D	E	F	G
		х1-Эруд.	х2 -Энер	х3-Люди	х4-Вн.	х 5-Зн.	Эф-ть
	Агент 1	0,8	0,2	0,4	0,6	1,0	76%
.	. . .	. . .	. . .	. . .	. . .	. . .	. . .
	Агент 5	0,5	0,7	0,3	0,4	0,74	81%

 

 

 

	Средняя эффективность пяти агентов
	Ср. агент	0,5	0,5	0,5	0,5	0,5

Массив ячеек B2-F6 заполняется произвольными числами от 0 до 1,

столбец G2-G6 – процентами удачных сделок по принципу «Чем выше уровень качеств агента, тем выше эффективность его работы», в ячейке G7 должна быть формула для вычисления среднего значения содержимого ячеек G2-G6, в ячейке G8 нужно вычислить значение эффективности для среднего агента по формуле, полученной в результате множественного регрессионного анализа работы пяти агентов. Остальные пункты – см. Задание.

Вариант 3

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

Для выполнения задания нужно составить и заполнить таблицу вида

	квартал	квартал	квартал	квартал	Новый квартал
Мерседес
БМВ

сделать прогноз продаж на новый квартал и выполнить другие пункты Задания.

2 Известны следующие данные о 5 недавно проданных подержанных автомобилях: х₁ - стоимость продажи, х₂ - стоимость аналогичного нового автомобиля, х₃ - год выпуска, х₄ - пробег, х₅- кол-во капитальных ремонтов, х₆- экспертные заключения о состоянии кузова и техническом состоянии автомобилей ( по 10-бальной шкале ). Определить, сколько может стоить автомобиль с соответствующими характеристиками: 20 000 руб., 34 000 руб., 1990 г. , 140000 км. , 0, 6 – см. пример 4.

Вариант 4 1 Определить минимально необходимый тираж ежемесячного журнала " Speed-Info " и возможный доход от размещения в нем рекламы в следующем месяце, если известны данные об объемах продаж этого журнала и доходах от размещения рекламы за прошедшие 12 месяцев (считать, что расценки на рекламу не менялись ).

Для выполнения задания нужно составить таблицу вида

Месяц			. . .
Тираж			. . .
Доход			. . .

 

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

2 В целях привлечения покупателей и увеличения оборота фирма проводит стратегию ежемесячного снижения цен на свой товар. На основании

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

Для выполнения задания нужно составить таблицу вида

 

	A	B	C	D	T	F	G	H	I
	мес	Фирма	Конкурент 1	Конкурент 2	Конкурент 3
		У1- объём	Х1- цена	Х2- объём	Х3- цена	Х4- объём	Х5 -цена	Х6 -объём	Х7 -цена
. .	. . .	. . .	. . .	. . .	. . .	. . .	. . .	. . .	. . .

 

Вариант 5

1 На основании данных о курсе американского доллара и немецкой марки в первом полугодии сделать прогноз о соотношении данных валют на второе полугодие. Во что будет выгоднее вкладывать деньги в конце года?

Для выполнения задания нужно составить таблицу вида

 

Месяц								. . .
Доллар	24,.5	24,9	25,7	26,9	28,0	28,8
Марка	72,1	76,3	79,6	85,3	89,7	90,9

 

и сделать линейный прогнозы на следующие 6 месяцев и др.(см. Задание).

2 Известны данные за последние 6 месяцев о том, сколько раз выходила реклама фирмы, занимающейся недвижимостью, на телевидении – х₁, радио – х₂, в газетах и журналах – х₃, а также количество звонков – у₁ и количество совершенных сделок у₂. Какое соотношение количества совершенных сделок к количеству звонков у (в %) можно ожидать в следующем месяце, если известно, сколько раз выйдет реклама в каждом из перечисленных средств массовой информации.

Для выполнения задания нужно составить и заполнить таблицу вида

 

 

	A	B	C	D	E
		х1	х2	х3	y = y2/y1·100%
	Январь				78%
. .	. . .	. . .	. . .	. . .	. . .
	Июнь				89%
	Июль

 

и выполнить применительно к таблице пункты Задания.

Вариант 6

1 Для некоторого региона известен среднегодовой доход населения, а также данные о структуре расходов ( тыс. руб. в год ) за последние 5 лет по следующим статьям: питание – х₁, жилье – х₂, одежда – х₃, здоровье – х₄, транспорт –х₅, отдых – х₆, образование – х₇. На основании известных данных провести анализ потребительского кредита ( или накопления ) в следующем году.

Для выполнения задания нужно составить таблицу вида

 

Годы	х1	х2	х3	х4	х5	х6	х7	Σхi	Доход	Кредит
					0,3			18,3	21,4	3,1
. . .	. .	. .	. .	. .	. . .	. .	. . .	. . .	...	...
					1,2		6,5	24,7	26,2	1,5

 

В ячейках столбца Σх_iдолжны быть записаны формулы, вычисляющие суммы всех расходов х₁ + х₂+...+ х₇ в каждом году, в ячейках столбца Доход – Доход соответствующие среднегодовые доходы, в ячейках столбцаКредит –формулы разности содержимого ячеек с ежегодными доходами и затратами, т.е. Кредит = Доход - Σх_i.. Затем для столбца Кредит нужно выполнить регрессионный прогноз на следующий год и другие пункты Задания.

2 Для 10 однокомнатных квартир, расположенных в одном районе, известны следующие данные: общая площадь - х₁, жилая площадь – х₂, площадь кухни – х₃, наличие балкона – х₄, телефона – х₅, этаж – х₆, а также стоимость – х₇. Определить, сколько может стоить однокомнатная квартира в этом районе без балкона, без телефона, расположенная на 1-ом этаже, общей площадью 28 кв. м, жилой- 16 кв. м, с кухней 6 кв. м.

Вариант 7

1 Определить возможный прирост населения ( кол-во человек на 1000 населения ) в 2005 году, если известны данные о кол-ве родившихся и умерших на 1000 населения в 1991-2000 годах.

2 После некоторого спада наметился рост объемов продаж матричных

 

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

Для выполнения задания нужно составить и заполнить таблицу вида

 

	Матричные принтеры	Струйные принтеры	Лазерные принтеры
	Спрос , у1	Цена, x1	Р.мат z1	Спрос, y2	Цена, x2	Р.мат. z2	Спрос, y3	Цена x3	Р.мат. z3
. .	. . .	. . .	. . .	. . .	. . .	. . .	. . .	. . .	. . .

 

сделать прогноз на седьмой месяц по уравнению у₁= f(x_1,z₁), получить уравнение у₁ = f(y₂, x₂, z₂, y₃, x₃, z₃) и проанализировать его. Если слагаемые y₂ и y₃ входят в регрессионное уравнение со знаком “-“ , то уменьшение спросов y₂ и y₃ ведёт к увеличению спроса у₁. Выполнить другие пункты Задания.

Вариант 8

1 Построить прогноз развития спроса населения региона на телевизоры, если известна динамика продаж телевизоров ( тыс. шт. ) и динамика численности населения данного региона ( тыс. чел. ) за последние 10 лет.

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

2 Размещая рекламу в 4-х изданиях, фирма собрала сведения о поступивших на нее откликов - у и сопоставила их с данными об изданиях: х₁- стоимость издания, х₂ - стоимость одного блока рекламы, х₃ - тираж, х₄ - объем аудитории, х₅- периодичность, х₆- наличие телепрограммы. Какое количество откликов можно ожидать на рекламу в издании со следующими характеристиками: 15000 руб. , 10$ , 1000 экз. , 25000 чел. , 4 раза в месяц , без телепрограммы.

Для выполнения задания нужно составить и заполнить таблицу вида

 

Данные	Отклики-у	х1	х2	х3	х4	х5	х6
Издание 1
. . .	. . .	. . .	. . .	. . .	. . .	. . .	. . .

 

Издание 4
Прогноз

 

сделать прогноз при заданных характеристиках и выполнить другие пункты Задания.

Вариант 9. 1 Размещая свою рекламу в двух печатных изданиях одновременно, фирма собрала сведения о количестве поступивших звонков и количестве совершенных сделок по объявлениям в каждом из указанных изданий за последние 12 месяцев. Определить, в каком из изданий и насколько эффективность размещения рекламы в следующем месяце будет больше?

Для выполнения задания нужно составить таблицу вида:

 

	Издание 1	Издание 2
Месяцы	Звонки	Сделки	Звонки	Сделки
. . .	. . .	. . .	. . .	. . .
13 лин.
13-эксп.

 

Эффективность определяется как сделки /звонки. Сделать линейный и экспоненциальный прогнозы по обоим изданиям, выполнить другие пункты Задания.

2 Пусть комплект мягкой мебели ( диван + 2 кресла ) характеризуется следующими признаками ( 1- есть, 0- нет ) : х₁- деревянные подлокотники, х₂- велюровое покрытие, х₃ - кресло-кровать, х₄ - угловой диван, х₅- раскладывающийся диван, х₆ - место для хранения белья. На основании данных о стоимости 5 комплектов мягкой мебели, для которых известны перечисленные признаки, сделать вывод о возможной стоимости комплекта с обычным раскладывающимся диваном , с местом для белья, без деревянных подлокотников, с обычными креслами.

Для выполнения задания нужно составить таблицу

Признаки	х1	х2	х3	х4	х5	х6	у1 - стоимость
Комплект 1							12560 р.
. . .	. . .	. . .	. . .	. . .	. . .	. . .	...
Комплект 5							10980 р.
Прогноз

сделать прогноз и выполнить другие пункты Задания.

 

 

Вариант 10

1Для двух радиостанций известны данные об изменении объема аудитории и динамике роста цен за 1 минуту эфирного времени за последние 12 месяцев. Определить, для какой радиостанции стоимость одного контакта со слушателем в следующем месяце будет меньше?

Для выполнения задания нужно составить и заполнить таблицу вида

 

	A	B	C	D	E
		Радиостанция 1	Радиостанция 2
	Месяц	Аудитория	Цена 1 мин.	Аудитория	Цена 1 мин.
...	...	...	...	...	...
	Прогноз
	Контакт

 

В строке «Контакт» в ячейках С8 и D8 должны быть записаны формулы =С7/B7 и =E7/D7 соответственно, вычисляющие стоимость 1 мин. эфира для одного слушателя в прогнозируемом месяце. Прогноз нужно выполнить для линейного и экспоненциального приближений и выбрать более достоверный, а также сделать другие пункты Задания.

2 На основании данных ежемесячных исследований известна динамика рейтинга банка ( в условных единицах ) за последние 6 месяцев в следующих сферах:

а) менеджмент и технология – х₁;

б) менеджеры и персонал – х₂;

в) культура банковского обслуживания – х₃;

г) имидж банка на рынке финансовых услуг – х₄;

д) реклама банка – х₅.

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

Для выполнения задания нужно составить и заполнить таблицу

	A	B	C	D	E	F	G
	Месяц	х1	х2	х3	х4	х5	Кол-во вкладчиков
. .	. . .	. . .	. . .	. . .	. . .	. . .	. . .
	Прогноз

 

и выполнить другие пункты Задания.

 

Содержание отчёта

1Название, цель, содержание работы

2 Задание своего варианта

3 Письменные ответы на контрольные вопросы

4 Выводы по работе

 

На дискете должны быть сохранены результаты работы

 

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

 

 

 

Лабораторная работа №5

 

Работа с финансовыми функциями.

  Цель работы: научиться работать с финансовыми функциями Excel и выполнять анализ «Что-если»

А В

1 Услуга Сумма 1 Выделить ячейку В6 и щелкнуть по кноп

ке Изменить формулу (знак «=»

2 Кредит,р 140000 слева от строки формул). Появится Мас-

тер функций , поле Имя будет

3 П.платёж 10000 заменено одной из функций. Раскрыть

поле Имя и выбрать функцию

4 % 8,50 ПЛТ(если её в списке нет, то через

пункт Другие функции...произво-

5Срок, лет 5 дится выход в Мастер функций)

6 Еж. платёж 2 Щелкнуть мышью по функции ПЛТ,

перетащить окно ПЛТ на свободное место экрана, чтобы освободить таблицу и заполнить его поля:

▪ Поле Ставка – это процент в месяц, вводим 0,085,

▪ Кпер – количество периодов выплат, т.е. 5лет*12мес, вводим 5*12

▪ Нз – общая сумма всех платежей с текущего момента, вводим 140000,

▪ Бс – будущая стоимость, вводится 130000 со знаком "-", т.к. платим мы, а не банк,

§ Тип – выплата в конце месяца, поэтому вводим 0 или ничего.

3.Нажать ОК.

 

Результат: около 2738 р. ежемесячно нужно выплачивать, чтобы погасить 130000 р. за 5 лет (в конце срока последним платежом ещё 10000р.)

2 Прогнозирование с помощью анализа «Что-если».

Анализ «Что-если» позволяет прогнозировать значение какой-либо функции (математической, финансовой, статистической и др.) при изменении её аргументов. Существует три способа прогнозирования значений:

С помощью таблиц подстановки данных, с помощью сценариев и с помощью подбора параметров и поиска решения.

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

Пример 2 Компания сделала заём на 80 000 руб. сроком на 3 года. Определить:

- ежемесячные выплаты при процентных ставках 7%, 8% и 9% годовых,

- ежемесячные выплаты при процентной ставке 5%, сроке заема 5 лет и сумме заема 100 000р.

 

1 Введем таблицу подстановок в виде:

 

А В С D

Анализ выплат по закладным Платежи

Платеж наличными Нет

3 Процентная ставка 7,0%

Срок, лет. 3

Сумма займа, руб. 80 000р.

2 Введём в ячейку D2формулу платежа ПЛТ (В3/12;В4*12;В5) вручную или через окно ПЛТ из Мастера функций (см. пример 1), в D2появится рассчитанное… 3 Изменим значение ячейки В3 на 8%, получим в D2 cумму платежа –2506,91р. 4 Изменим значение ячейки В3 на 9%, получим в D2 cумму платежа –2543,98р.

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

1 Как вывести на экран Палитру формул, выйти через неё в приложение Мастер функций?

2 Какую операцию выполняет функция ППЛАТ, что вводится в её поля Норма, Кпер, Нз, Бс, Тип?

3 Назначение и способы анализа «Что если»?

4 Что такое «Таблица подстановок», каков состав её ячеек?

5 Что такое сценарий, как его создать, просмотреть, получить итоговый отчет на отдельном листе?

6 Сущность операции Подбор параметра, как она выполняется?

 

Задания

1 Выполнить задание примера 1, изменив сумму кредита на 140000·n, где n- номер студента в журнале преподавателя. Выполнить то же для новой суммы кредита, изменив годовой процент с 8,5% на 5%, а срок кредита с 5 на 10 лет.

2 Выполнить анализ «Что-если» по заданию таблицы подстановки примера 2, изменив сумму заёма на 80000·n, где n- номер студента в журнале преподавателя.

3 Оформить в виде сценариев все операции из п.1 (два сценария) и п.2 (четыре сценария) данного задания к лабораторной работе.

4 Выполнить задание примера 4, изменив сумму ежемесячной выплаты на n·100.

 

Содержание отчёта

1Название, цель, содержание работы

2 Задание своего варианта

3 Письменные ответы на контрольные вопросы

4 Выводы по работе

На дискете должны быть сохранены результаты работы

 

 

Лабораторная работа №6 (excel-6)

 

Создание макросов в Excel

Цель работы: научиться использовать макросы для автоматизации

выполняемых задач

Содержание работы:

1 Запуск макроса с клавиатуры сочетанием клавиш.

2 Запуск макроса с помощью кнопки на панели инструментов.

Создание новой панели инструментов.

3 Запуск макроса с помощью кнопки на рабочем листе.

4 Копирование макроса в другую книгу.

Общие сведения

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

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

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

 

Запуск макроса с клавиатуры сочетанием клавиш

1 В меню Сервис выберите подменю Макроси выберите команду Начать запись. 2 Введите имя для макроса в соответствующее поле. Первым символом имени макроса должна быть буква. Остальные символы могут быть буквами, цифрами или знаками…

Абсолютная адресация

1 Введём заданную функция в ячейки рабочего листа Excel. 2 Выполним команды СервисМакросНачать запись. 3 В появившемся окне Запись макроса введём в поле Имя макроса

Относительная адресация

Чтобы с помощью макроса обрабатывать произвольные ячейки, следует записать его с относительными ссылками. Для этого при записи макроса нажмите… =ТЕНДЕНЦИЯ($B$2:$E$2;$B$1:$E$1;$F$1:$H$1;1). Теперь можно обрабатывать этим макросом данные в произвольном блоке ячеек, равном по размеру исходному (B1:H2), но…

Запуск макроса с помощью кнопки на панели

Инструментов. Создание новой панели инструментов

1 Выберите команду Настройка... в меню Сервис, а затем — вкладку Панели инструментов. 2 Нажмите кнопку Создать. 3 В окне Создание панели инструментоввведите нужное имя в поле Панель инструментов, затем нажмите кнопку OK.В списке…

Копирование макроса в другую книгу

1 Открыть книгу, содержащую копируемый модуль, и книгу, в которую нужно его копировать – через меню ФайлОткрыть или кнопку Открыть на панели… 2 В меню Сервисустановить курсор на пункт Макрос и выбрать ко- манду Редактор Visual Basic.

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

1 Что называется макросом, для чего он предназначен?

2 Как записать макрос?

3 Способы запуска макроса (перечислить).

4 Запись макроса, запускаемого из меню Сервис и сочетанием клавиш с клавиатуры.

5 Использование относительной и абсолютной адресации при записи макросов.

Создание новой панели инструментов. Назначение кнопки для запуска макроса на новой панели инструментов .

7 Добавление кнопки запуска макроса на существующие панели инструментов (Стандартная,Форматирование и др.).

8 Добавление кнопки запуска на рабочий лист с помощью панели инструментов Формы.

Копирование модуля макроса в другую книгу.

 

Задание

1 Разработать макрос для вычисления прогнозируемых значений функции y = 0,2·x ⁿ , где n – номер компьютера в аудитории. Предварительно функцию нужно ввести в ячейки столбца или строки процессора Excel, задав изменение аргумента хв пределах 1...7 с шагом 1 и вычислив в соседнем столбце (или строке) по введённой формуле значения функции у(см. л.р. excel – 3). Прогноз выполнить с помощью функций ТЕНДЕНЦИЯ или РОСТ (см. л.р. excel – 4).

Макрос должен запускаться кнопкой на панели Стандартная и кнопкой, расположенной на рабочем листе.

2 Разработать макрос для построения графика функции y = 2·sin(n·x) , где n – номер компьютера в аудитории, для 10 значений аргумента х = 1...10. При записи макроса использовать относительную адресацию. Макрос должен запускаться сочетанием клавиш с клавиатуры и командой из меню Сервис.

3 Открыть новую книгу и скопировать в неё оба макроса. Создать новую панель инструментов. Назначить макросам новые кнопки для их запуска с новой панели инструментов .

 

Содержание отчёта

1Название, цель, содержание работы

2 Задание своего варианта

3 Письменные ответы на контрольные вопросы

4 Выводы по работе

На дискете должны быть сохранены результаты работы

 

Лабораторная работа №7

 

Поиск решения в Excel

Цель работы:научиться использовать процессор Excel

для решения задач оптимизации

Содержание работы:

1 Создание формы

2 Ввод данных в окно Поиск решения

3 Задание параметров поиска и решение задачи

 

Общие сведения

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

Задача линейного программирования (ЗЛП) в общем случае формулируется следующим образом:

Определить максимум (минимум) целевой функции F max(min) при заданной системе ограничений (2) и граничных условий (3):

 

Fmax(min) =A1*X1+A2*X2+...+An*Xn (1)

B11*X1+B12*X2+...+B1n*Xn<=C1

B21*X1+B22*X2+...+B2n*Xn<=C2

............................ .................................. (2)

Bn1*X1+Bn2*X2+...+Bnn*Xn<=Cn

 

Xi>=0, i=1...n (3)

 

Рассмотрим применение процессора Excel для решения ЗЛП на примере.

 

Задача. МП выпускает товары Х1,Х2,Х3,Х4, получая от реализации каждого прибыль в 60,70,120,130 руб. соответственно. Затраты на производство приведены в табл. 1. Определить:

1 Максимум прибыли в зависимости от оптимального распределения затрат.

2 Минимум ресурсов, необходимых для получения максимальной прибыли.

 

 

 

Таблица 1

Затраты	Х1	Х2	Х3	Х4	Всего
Трудовые
Сырьевые
Финансы

 

Составим математическую модель процесса по описанию задачи:

 

60Х1+70Х2+120Х3+130Х4 = Fmax – целевая функция прибыли.

Х1+Х2+Х3+Х4 <= 16

6Х1+5Х2+4Х3+Х4 <= 110 - ограничения модели

4Х1+6Х2+10Х3+13Х4 <= 100

Х_j >=0 - граничные условия модели

 

Решение задачи средствами Excel состоит из 3 этапов:

1 Создание формы для ввода условий задачи, ввод в неё исходных данных и зависимостей из математической модели.

2 Ввод данных из формы в окно Поиск решения из меню Сервис.

3 Задание параметров поиска и решение задачи.

 

Создание формы

а)Составление формы в виде:

 

А B C D E F G H

2 Значение 3 Нули 4 Коэф. ЦФ 60 70 120 130 СП(В2:Е2)(В4:Е4) max

Ввод данных в окно Поиск решения

В поле "Изменяя ячейки" ввести В2:Е2 (с клавиатуры или протащив мышью). Нажать клавишу "Добавить", в окне "добавление ограничения в… Аналогично через "Добавить" ввести F6<=H6, F7<=H7 для системы ограничений…

Задание параметров поиска и решение задачи

В окне "Поиск решения" нажать клавишу "Параметры", выбрать по

 

умолчанию Максимальное время - 100 с.(может быть до 2^15=32767 c.> 4 час.), число итераций- 100(для большинства задач это количество просчётов подходит с большим запасом), установить флажок "птичка" в строке "Линейная модель", нажать ОК, в появившемся окне Поиск Решения нажать Выполнить, появится окно:

Результаты поиска решения с таблицей результатов:

 

А В С D E F G H

2 Значение 10 0 6 0 3 Нули 4 Коэф. ЦФ 60 70 120 130 1320 max

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

1 Сформулировать основную задачу линейного программирования. Записать математическую модель ЗЛП.

2 Основные этапы решения ЗЛП с помощью процессора Excel.

 

3 Способы ввода формул математической модели ЗЛП в форму

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

5 Ввод параметров поиска решения в процессор Excel.

 

Задание

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

Аi=Ai+№, Вij=Bij+№, Ci=Ci+№.

2 Рассчитать максимальную прибыль и оптимальный план выпуска товаров для её достижения. Определить минимально необходимое количество ресурсов.

3 Выполнить то же, увеличив все ресурсы в 2 раза, сравнить результаты пунктов 2 и 3.

4 Для производства двух видов продукции А и В можно использовать сырье трех видов. При этом на изготовление единицы продукции вида А расходуется а1 кг. сырья первого вида, а2 кг сырья второго вида и а3 кг сырья третьего вида. На изготовление единицы продукции вида В расходуется в1 кг сырья первого вида, в2 кг. сырья второго вида и в3 кг сырья третьего вида (табл. 2).

На складе имеется всего сырья первого вида с1кг ,сырья второго вида с2кг и третьего вида с3 кг. От реализации единицы готовой продукции вида Апредприятие имеет прибыль Q тыс.руб. , от реализации единицы готовой продукции вида В прибыль составляет V тыс.руб. Определить максимальную прибыль от реализации всей продукции видов А и В .

Таблица 2

№ вар	A1	A2	A3	B1	B2	B3	C1	C2	C3	Q	V

 

 

Содержание отчёта

1Название, цель, содержание работы

2 Задание своего варианта

3 Письменные ответы на контрольные вопросы

4 Выводы по работе

На дискете должны быть сохранены результаты работы

 

 

 

Лабораторная работа №8

Работа с окнами в Excel

Цель работы: научиться работать с разными частями одной книги и несколькими документами одновременно

Содержание работы:

1 Вывод разных частей документа

2 Вывод разных документов

3 Операции в окнах.

4 Закрепление областей листа.

 

Общие сведения

Меню Окно из Строки меню Excel содержит три раздела:

- команды Новое, Расположить, Скрыть, Отобразить;

- команды Разделить, Закрепить области;

- список книг, открытых в Excel.

Команды первого раздела:

Команда Новоесоздаёт в отдельном окне копию открытого активного листа и используется совместно с командой Расположить.

Команда Расположитьвыводит на экран все открытые документы. При выборе команды появляется окно Расположение окон, в котором можно выбрать порядок вывода (рядом, слева направо, сверху вниз, каскадом), установив точку в соответствующей строке и нажав ОК.Документы выводится в отдельных окнах.

Команда Скрыть сворачивает активный документ, команда Отобразить восстанавливает его на экране.

Вывод разных частей документа

Создав с помощью командыНовоеещё одну копию (Книга.xls 3) и расположив её на экране, можно работать с тремя разными частями одного документа и т.д.…

Вывод на экран разных документов

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

 

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

Операции в окнах

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

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

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

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

 

4 Закрепление областей листа.

При обработке больших таблиц, которые полностью не помещаются на экране, возникает необходимость в фиксации заголовков и других частей таблиц, чтобы они при перемещении по документу всё время находились на экране. Для этого используются команды второго раздела меню Окно – Разделить и Закрепить области.

Команды второго раздела:

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

После выполнения команды разделения в меню Окнопоявляется команда Снять разделение, которая возвращает лист в исходное состояние.

Команда Закрепить области служит для отображение названий строк и столбцов при прокрутке:

1 Чтобы закрепить верхнюю горизонтальную область, выделите строку, над которой требуется разбить лист.

Чтобы закрепить левую вертикальную область, выделите столбец,

 

слеваот которого требуется разбить лист.

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

2 В меню Окновыберите команду Закрепить области. После снятия выделения они будут отчеркнуты линиями. Теперь закреплённые области будут всё время находиться на экране. Снять закрепление можно командой Снять закрепление областей.

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

1 Состав пункта строки меню Окно.

2 Назначение команды Новое. Как вывести на экран три части одного документа?

3 Назначение и использование команды Расположить.

4 Назначение и использование команд Скрыть, Отобразить.

5 Назначение и использование команды Разделить.

6 Назначение и использование команды Закрепить области.

7 Как снять разделение и закрепление областей?

Задание

1 Открыть со своей дискеты в Excel документ, разработанный в лабораторной работе №2 (excel-2) «Создание и редактирование диаграмм и графиков в Excel», сделать с него копию, расположить окно-оригинал и окно-копию на одном листе и скопировать таблицу и график y=n·sin(2x)из окна-оригинала в конец окна-копии.

2 Открыть со своей дискеты в Excel три любых файла на Листе 1, Листе 2 и Листе 3 соответственно и расположить на экране их окна способом слева направо. Скопировать любую таблицу из первого окна во второе и в третье окно, документ сохранить в дискету под именем «Разделение»..

3 Снять разделение, закрепить первую строку и первый столбец в окне Листа 1.

Содержание отчёта

1Название, цель, содержание работы

2 Задание своего варианта

3 Письменные ответы на контрольные вопросы

4 Выводы по работе

На дискете должны быть сохранены результаты работы

 

 

 

Лабораторная работа №9

 

Списки в Excel. Сортировка и фильтрация данных

Цель работы: научиться создавать базы данных, выполнять

сортировку и фильтрацию данных

Содержание работы:

1 Создание списка – базы данных

2 Сортировка данных в списке

3 Фильтрация данных в списке

Общие сведения

Список (база данных Excel) – это электронная таблица, в которой имена столбцов занимают одну строку, а строки имеют фиксированную структуру.

База данных (БД) – это поименованная совокупность данных, имеющая одно или несколько приложений. Например, список группы является БД студентов. Её приложение для деканата – это список студентов, обучающихся в академической группы № …, приложение для военкомата – список призывников, для телефонной компании – список абонентов и т.д.

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

Строка заголовков (схема таблицы) состоит из ячеек с именами полей, причём имя поля в БД должно располагаться в одной ячейке таблицы Excel.

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

Для работы с БД в Excel используются формы вывода данных (команда ДанныеФорма), можно создавать запросы на данные из созданного списка и из внешних БД (команда ДанныеИмпорт внешних данныхСоздать запрос), обновлять данные через Internet и др.

 

1 Создание списка – базы данных

После запуска приложения Excel нужно ввести таблицу БД (рис. 1). Поскольку имя каждого поля должно занимать одну ячейку, нужно выделить строку для заголовка таблицы, затем в меню ФорматЯчейки…, в окне Формат ячеек вкладкаВыравнивание установить параметры:

 

▪ по горизонтали: по значению

▪ по вертикали: по верхнему краю

▪ отображение: переносить по словам (если Имя поля – длинное)

Если имя поля ненамного превышает ширину ячейки, то можно ограничиться командой ФорматСтолбецАвтоподбор ширины.

 

Рисунок 1 Список Экзаменационная ведомость

 

Сортировка данных в списке

Цель сортировки – упорядочивание данных в порядке возрастания или убывания, она производится в том же списке. В среде Excel сортировка выполняется… Для сортировки данных нужно выполнить команду Данные Сортировка, откроется… При сортировке по возрастанию упорядочивание идёт от меньшего к большему – по алфавиту, по хронологии дат, но согласно…

Фильтрация данных в списке

Фильтрация данных – это выбор данных, соответствующих какому-либо условию, критерию. Фильтрация в Excel выполняется двумя способами:

▪ с помощью команды Автофильтр и

▪ с помощью команды Расширенный фильтр.

 

Автофильтр

Рисунок 4 Список студентов, получивших отметку 4  

Расширенный фильтр

▪ критерий сравнения; ▪ вычисляемый критерий. Если критерий формируется в нескольких полях (столбцах), то его называют множественным критерием (поэтому фильтр…

Формирование критерия сравнения

Для создания таблицы рекомендуется сначала скопировать на свободное место листа (или в другой лист, в другую книгу) всю строку с именами столбцов.… Ниже имён столбцов располагаются строки с условиями(критериями) сравнения… ▪ точного значения;

Формирование вычисляемого критерия

Примечание.Имя столбца с формулой вычисляемого критерия должно отличаться от имени столбца в списке. Пример 2 Выбрать записи о сдаче экзаменов студентами группы КТ-11 с оценкой… Здесь также возможны 3 варианта.

Фильтрация записей расширенным фильтром

▪ установить курсор в список и выполнить команду Данные Фильтр Расширенный фильтр. ▪ установить курсор в поле Исходный диапазон окна Расширенный фильтр и протянуть мышью по всей таблице, включая…

Фильтрация с помощью формы

Для вызова формы нужно установить курсор в область списка и выполнить команду ДанныеФорма…. Появляется экранная форма Список(рис. 8), в которой… Для фильтрации данных нажимается кнопка Критерии (форма очищается…

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

1 Что называется списком (базой данных) в Excel?

2 Как называются элементы таблицы в терминах БД?

3 Сортировка данных, её порядок при сортировке нескольких полей.

4 Фильтрация с помощью автофильтра, критерии фильтрации.

5 Формирование критерия сравнения в расширенном фильтре.

6 Формирование вычисляемого критерия в расширенном фильтре.

7 Фильтрация данных расширенным фильтром.

8 Фильтрация данных с помощью формы.

9 Как добавить или изменить данные в списке с помощью формы?

Задание

1 Создать в Excel cписок согласно варианту задания (не менее 10 записей), недостающие записи заполнить своими данными. Предусмотреть повторение записей в отдельных полях – для выполнения сортировки.

2 Отсортировать данные списка по трём полям (поля выбираются самостоятельно).

3 Выполнить фильтрацию данных списка тремя способами:

▪ с помощью автофильтра,

▪ с помощью расширенного фильтра по критерию сравнения,

▪ с помощью расширенного фильтра по вычисляемому критерию.

Варианты задания (№ варианта - №компьютера в аудитории)

Вариант.

Таблица "Учебники"

Код	Авторы	Название учебника	Город	Издательство	Год издания
	Макарова Н.В.	Информатика	Москва	Финансы
	Выгодский В.Н.	Высшая математика	Киев	Высшая школа
…	…	…	…	…	…
	Симонов П.А.	Общая химия	Москва	ABF

Вариант.

Таблица "Врачи"

Код	Фамилия И.О.	Специальность	Должность	Отделение	Зарплата
	Панов Н.В.	Отоляринголог	Глав. врач	Терапевтич.	6300р.
	Перов В.Н.	Стоматолог	Зав. отделением	Стоматологич.	4850р.
…	…	…	…	…	…
	Попов Г.А.	Хирург	Врач	Хирургическое	4200р.

 

Вариант.

Таблица "Больные"

Код	Ф. И.О. больного	Болезнь	№ палаты	Лечащий врач	Дата пост.
	Ванин Н.В.	Сахар. диабет		Вавлова А.А.	26.03.04.
	Петров В.Н.	Перитонит		Селезнёв П.П.	10.03.04
…	…	…	…	…	…
	Попович Г.А.	Аппендицит		Харатьян С.Г.	22.03.04.

 

Вариант.

Таблица "Спортсмены"

Код	Ф. И.О.	Вид спорта	Дата рожд.	Разряд	Тренер
	Витаанен Н.В.	Тяжёлая. атлетика	06.12. 1990	Мастер	Власов А.А.
	Провский В.Н.	Бокс	15.03.1998	ЗМС	Родин П.Э.
…	…	…	…	…	…
	Гуревич Г.А.	Таэквондо	12.10.1997	МСМК	Второв В.Д..

Вариант.

Таблица "Маршруты"

Код	Водитель	Маршрут	Дата	Время	Марка автобуса
	Ветров Н.В.	Краснодар - Сочи	06.12. 04	6.40	"Икарус"

 

 

 

	Вронский В.Н.	Армавир - Ростов	07.12.04	10.15	"ЛиАЗ"
…	…	…	…	…	…
	Гуров Г.А.	Темрюк - Туапсе	12.10.04	20.35	"Мерседес"

Вариант.

Таблица "Рейсы"

Борт №	№ рейса	Аэропорт назнач.	Дата	Время	Тип самолёта
	Ю-1138	Домодедово	06.12. 04	7.30	Як-42
	Ю-1142	Внуково	15.03.04	15.40	Ил-62
…	…	…	…	…	…
	Ю-1136	Домодедово	12.10.04	20.20	Ту-154

Вариант.

Таблица "Работники"

Код	Ф. И.О.	Должность	Профессия	Зарплата	Стаж, лет
	Витаанен Н.В.	Мастер	Токарь	8200р.
	Провский В.Н.	Рабочий	Электрик	9650р.
…	…	…	…	…	…
	Гуревич Г.А.	Начальник цеха	Механик	16800р.

Вариант.

Таблица "Штат"

Код	Ф. И.О.	Должность	Звание.	Уч. степень	Дата рождения
	Азаров Н.В.	Декан	Профессор	Д.т.н	25.05.1949
	Ржевский В.Н.	Зам. декана	Доцент	К.т.н.	20.02.1959
…	…	…	…	…	…
	Кудасова Г.А.	Секретарь	Инженер	-	22.03.1980

 

Вариант.

Таблица "Команда"

Код	Ф. И.О.	Специализация	Дата рожд.	Разряд	Тренер
	Витин Н.В.	Вратарь	06.12. 1990	Мастер	Власов А.А.
	Провский В.Н.	Нападающий	15.03.1998	ЗМС	Родин П.Э.
…	…	…	…	…	…
	Вуйкич Г.А.	Полузащитник	12.10.1997	МСМК	Власов А.А.

 

Вариант.

Таблица "Поезда"

Код	№ поезда	Маршрут	Время отправления	Дата отправления.	Вагон, место	Стоим. билета
		Москва-Курская	23.40	06.12. 04	П	520р.
		Москва-Павелец.	15.30	15.03.04	СВ	1080р.
…	…	…		…	…
		Харьков	6.12	12.10.047	К	775р.

Вариант.

Таблица "Телефоны"

Код	Ф. И.О.абонента	Адрес	№ телефона	Район	Дата устан.
	Ванин Н.В.	Красная, 32-34	135-14-56	Централ.	26.03.04.
	Петров В.Н.	Калинина, 78-1	135-23-36	Централ.	10.03.04
…	…	…	…	…	…
	Попович Г.А.	Крымская, 63-3	123-45-67	Прикуб.	22.03.04.

Вариант.

Таблица "Спортсмены"

Код	Ф. И.О.	Вид спорта	Дата рожд.	Разряд	Тренер
	Ртаанен Н.В.	Лёгкая атлетика	06.12. 1990	Мастер	Власов А.А.
	Шровская В.Н.	Теннис	15.03.1998	ЗМС	Родин П.Э.
…	…	…	…	…	…
	Гуревич Г.А.	Таэквондо	12.10.1997	МСМК	Второв В.Д..

Вариант.

Таблица "Автобусы"

Код	Водитель	Маршрут	Дата	Время	Марка автобуса
	Петров Н.В.	Ростов - Сочи	06.12. 04	6.40	"Икарус"
	Троян В.Н.	Киев - Ростов	07.12.04	10.15	"ЛиАЗ"
…	…	…	…	…	…
	Дуров Г.А.	Сочи - Туапсе	12.10.04	20.35	"Газель"

Вариант.

Таблица "Аэтопорт"

Борт №	№ рейса	Аэропорт назнач.	Дата	Время	Тип самолёта
	Ю-1138	Шереметьево	06.12. 04	7.30	Як-42
	Ю-1142	Внуково	06.12. 04	15.40	Ил-62
…	…	…	…	…	…
	Ю-1136	Домодедово	12.10.04	20.20	Ту-154

Вариант.

Таблица "Цех"

Код	Ф. И.О.	Должность	Профессия	Зарплата	Стаж, лет
	Виталин Н.В.	Мастер	Токарь	8200р.
	Прованский В.Н.	Рабочий	Электрик	9650р.
…	…	…	…	…	…
	Пуревич Г.А.	Начальник цеха	Механик	16800р.

Вариант.

Таблица "Кафедра"

Код	Ф. И.О.	Должность	Звание.	Уч. степень	Дата рождения
	Назаров Н.В.	Декан	Профессор	Д.т.н	25.05.1949
	Ряжевский В.Н.	Зам. декана	Доцент	К.т.н.	20.02.1959
…	…	…	…	…	…
	Кудасова Г.А.	Секретарь	Инженер	-	22.03.1980

Вариант.

Таблица "Футболисты"

Код	Ф. И.О.	Специализация	Дата рожд.	Разряд	Тренер
	Ватинин Н.В.	Вратарь	06.12. 1990	Мастер	Власов А.А.
	Проворов В.Н.	Нападающий	15.03.1998	ЗМС	Родин П.Э.
…	…	…	…	…	…
	Зуйкович Г.А.	Полузащитник	12.10.1997	МСМК	Власов А.А.

 

 

 

Вариант.

Таблица "Железная дорога"

Код	№ поезда	Маршрут	Время отправления	Дата отправления.	Вагон, место	Стоим. билета
		Москва-Курская	23.40	06.12. 04	П	1080р.
		Москва-Павелец.	15.30	15.03.04	СВ	1080р.
…	…	…		…	…
		Харьков	6.12	12.10.047	К	775р.

Вариант.

Таблица "Военкомат"

Код	Ф. И.О.	Адрес	№ телефона	Звание	Род войск.
	Ванин Н.В.	Красная, 32-34	135-14-56	Полковник	Авиация
	Петров В.Н.	Калинина, 78-1	135-23-36	Полковник	Артиллерия
…	…	…	…	…	…
	Попович Г.А.	Крымская, 63-3	123-45-67	Майор	Миномётн.

 

Вариант.

Таблица "Аиелье"

Код	Ф. И.О. мастера	Специальность	Дата рожд.	Разряд	№ зала
	Ртищева Н.В.	Макияж	06.12. 1990
	Перовская В.Н.	Парикмахер	15.03.1998
…	…	…	…	…	…
	Гуревич Г.А.	Маникюр	12.10.1997

Содержание отчёта

1 Название работы (в скобках – имя файла описания лаб. работы)

2 Цель работы

3 Содержание работы (порядок выполнения)

4 Заполненный вариант задания, результаты сортировки и фильтрации.

5 Письменные ответы на контрольные вопросы

6 Выводы по работе

 

 

 

Лабораторная работа №10

 

Структурирование таблиц в Excel

Цель работы: научиться структурировать таблицы ручным

способом и способом автоструктурирования

 

Содержание работы:

1 Ручной способ структурирования

2 Автоструктурирование

3 Структурирование с подведением итогов

Структурирование таблиц

  Рисунок 1 Структурированная по строкам таблица

Ручной способ структурирования

В результате создается структура таблицы (рис. 1). Кнопки 1 и 2 - номера уровней структуры – служат для открытия или скрытия соответствующего… вложенный структурный элемент нижнего иерархического уровня будет создан, если… Для отмены одного структурного компонента производится выделение области и выполняется командаДанныеГруппа и…

Автоструктурирование

Пример такой таблицы приведен на рис. 2. В ней, по сравнению с таблицей рис. 1 введены столбец, в котором рассчитывается максимальная оценка… После ввода в таблицу исходных данных и формул курсор устанавливается в…  

Структурирование с подведением итогов

Примечания: 1 Для получения итогов по группам следует заранее упорядо­чить строки списка с…  

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

1 Что такое структурированная таблица?

2 Виды структурирования таблиц в Excel.

3 Как выполняется ручное структурирование?

4 Как выполняется автоструктурирование?

5 Как выполняется структурирование с подведением итогов?

6 Для чего применяется структурирование таблиц?

7 Как выглядит структура таблицы?

8 Как отменить структуру таблицы?

 

Задание

1 Подготовить для структурирования таблицу своего варианта из лабораторной работы №9 (excel-9) "Списки в Excel. Сортировка и фильтрация данных".

Для этого добавить:

▪ справа - ещё три столбца: в первые два ввести числовые данные,

 

▪ в третий – формулу для их обработки по строкам.

Например, в первом и втором - Зарплата и Налог, в третьем – Сумма к выдаче (как разность зарплаты и налога), или Цена и Количество = Общая стоимость (произведение цены на количество), или Доза и Количество приёмов = Общая доза (произведение дозы и количества), или Стоимость бензина и Пробег = Затраты на топливо (произведение стоимости и пробега) и т.п.

▪ снизу – ёщё одну строку, ввести в её ячейки формулы для обработки данных в столбцах (например, сумма, среднее, произведение, максимум и т.п.)

2 Выполнить в подготовленной таблице:

▪ Ручное структурирование

▪ Автоструктурирование с введением нового иерархического уровня по строкам.

▪ Структурирование с подведением итогов.

В результате выполнения работы должно получиться три таблицы – аналогично рис.1, 3 и 5.

 

Пояснения к выполнению:

1 Для ручного структурирования:

▪ отсортировать таблицу по любому какому-нибудь столбцу, например, по фамилии, должности, маршруту;

▪ создать структурную группу для строк 1-3. Для этого нужно выделить строки 1-3 и выполнить команду Данные Группа и Структура Группировать (для строк). Аналогично создать структуру для двух других групп (строки 4-6 и 7-10).

▪ создать структурные группы для столбцов. Для этого выделить 2-3 столбца (начиная от ячейки с именем и вниз - до последней строки) и выполнить команду Данные Группа и Структура Группировать (для столбцов). Аналогично повторить для оставшихся столбцов.

Сохранить результат на дискете.

2 Для автоструктурирования таблицы и введения нового уровня ручным способом:

2.1 Создание автоструктуры

▪ установить курсор в любую ячейку области данных, выполнить команду Данные Группа и Структура Создать структуру;

2.2 Введение нового иерархического уровня ручным способом – по строкам:

▪ выделить строки 1-3 (с данными) и выполнить команду Данные Группа и Структура Группировать;

▪ выделить остальные строки таблицы и выполнить команду Данные Группа и Структура Группировать;

В результате внутри первого уровня структурирования (1) появится второй уровень (2), созданный вручную.

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

▪ отсортировать список записей таблицы рис. 2 командой Данные Сортировка, в окне Сортировка диапазона указать те столбцы, которые нужны для вычисления итогов. Для таблицы на рис. 5 сначала задавалась сортировка для № группы, затем для Код преп, чтобы получить итоги – средний балл по каждой группе, всей группе и по каждому преподавателю.

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

▪ создать 1-й уровень итогов – средний балл по группе. Для таблицы рис. 2 устанавливался курсор в любую ячейку списка и выполнялась команду Данные Итоги. В диалоговом окне Промежуточные итоги указывалось:

При каждом изменении в- № группы

Операция:Среднее

Добавить итоги по:Оценка

Заменять текущие итоги:нет

Конец страницы между группами:нет

Итоги по данными:да.

После нажатия ОК был рассчитан средний балл по каждой группе и общее среднее.

▪ создать 2-й уровень итогов – средний балл по каждому преподавателю для каждой группы: установить курсор в любую ячейку списка и выполнить команду Данные Итоги.В окне Промежуточные итоги указать:

При каждом изменении в– Код преп

Операция:Среднее

Добавить итоги по:Оценка

Заменять текущие итоги:нет

Конец страницы между группами:нет

Итоги по данными:да.

После нажатия ОК будет рассчитан средний балл по каждому преподавателю в каждой группе.

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

 

коду предмета…), на 2-м уровне – по № зачётной книжки (зарплате, должности…, на 3-м уровне - по дате, стоимости, телефону…).

Содержание отчёта

1 Название работы (в скобках – имя файла описания лаб. работы)

2 Цель работы

3 Содержание работы (порядок выполнения)

4 Структуры задания (на дискете) – три таблицы.

5 Письменные ответы на контрольные вопросы

6 Выводы по работе

 

 

Лабораторная работа №11

Сводные таблицы

Цель работы: научиться создавать и применять сводные таблицы

при работе с данными

Содержание работы:

1 Мастер сводных таблиц

2 Построение макета сводной таблицы

3 Работа со сводной таблицей

Мастер сводных таблиц

Пример 1 Дан список "Экзаменационная ведомость" (рис. 1). Рисунок 1 Список "Экзаменационная ведомость"

Построение макета сводной таблицы.

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

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

1 Что называется сводной таблицей, сводом?

2 Для чего предназначены сводные таблицы?

3 Что помещается в областях для строк, столбцов, данных макета?

4 Как задать в сводной таблице вид вычислительного итога Сумма, Максимум, Произведение?

5 Как обновить данные в сводной таблице?

6 Как изменить структуру сводной таблицы (добавить или изменить поля строк, столбцов, данных?

 

Задание

Для таблицы своего варианта из лабораторной работы №9 (excel-9) Списки в Excel. Сортировка и фильтрация данных построить две сводные таблицы. Поля, помещаемые в области строк, столбцов и данных выбрать самостоятельно.

Результаты ( две сводные таблицы) сохранить на дискете.

 

Содержание отчёта

1 Название работы

2 Цель работы

3 Содержание работы

4 Задание и своды (на дискете) – две таблицы.

5 Письменные ответы на контрольные вопросы

6 Выводы по работе

 

 

Лабораторная работа №12

 

Консолидация данных

Цель работы: научиться создавать консолидацию данных втаблицах

Содержание работы:

1 Консолидация данных по расположению

2 Консолидация данных по категориям

Общие сведения

Консолидация – это агрегирование (объединение) данных, представленных в исходных областях-источниках – таблицах, списках, блоках ячеек и др.

Консолидация данных (рис. 1) выполняется в соответствии с выбранной функцией обработки. Результат консолидации находятся в области- назначения. Таблица консолидации создается путем применения функции обработки к исходным значениям. Области-источники могут находиться на различных листах или рабочих книгах их может быть до 2⁸ = 255.

Области-источники

 

 

Рисунок 1 - Представление о консолидации данных

 

В Excel возможны следующие варианты консолидации данных:

· с помощью формул, где используются ссылки;

· по расположению данных для одинаково организованных областей-источников (фиксированное расположение);

· по категориям для различных по структуре области данных;

· с помощью сводной таблицы (лабораторная работа №11);

 

 

· консолидация внешних данных.

При консолидации данных с помощью формул используемые в них

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

· все области на одном листе - в ссылках указывается адрес блока ячеек (например, D1:C8);

· области на разных листах - в ссылках указывается название листа и диапазон (например, лист1 !D1:лист2!С8, т.е. с ячейки D1 листа 1по ячейку С8 листа 2);

· области в разных книгах, на разных листах — в ссылках указывается название книги, название листа, диапазон, например [кни га1]лист1! D1:[книга2]лист2!С8.

Консолидация данных по расположению

Пример 1 На разных листах рабочей книги по каждому товару хра­нятся сведения о показателях реализации товаров за конкретный период (рис.2).…   Рисунок 2Области-источники при консолидации

Консолидация данных по категориям

столбца (рис. 4). Excel автоматически переносит эти имена в область назначения.  

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

1 Что называется консолидацией?

2 Где располагается результат консолидации?

3 Что такое области-источники и где они могут располагаться?

4 Какие существуют варианты консолидации?

5 Из чего состоит область консолидации при объединении данных по

областям приёмников?

6 Из чего состоит область консолидации при объединении данных по областям приёмников?

Задание

Для таблицы своего варианта из лабораторной работы №9 (excel-9) "Списки в Excel. Сортировка и фильтрация данных" построить две таблицы:

· консолидированную по областям;

· консолидированную по категориям.

Предварительно подготовить таблицы для консолидации, т.е. для первой консолидации снять копию таблицы-оригинала из л.р.№9, а для второй – снять копию и дополнить её (копию) новым столбцом и новой строкой.

Пояснения к выполнению

Консолидация по областям

2 Сделать копию таблицы своего варианта из л.р.№9 на том же листе, изменить в ней данные. Эта таблица будет отражать, например, показатели во 2… 3 Выполнить консолидацию данных по расположению: § установить курсор в первую ячейку области, где будет располагаться консолидированная таблица, например в ячейку…

Консолидация по категориям

2 Вставить новый столбец с именем % реализации и заполнить формулой Продано*100/ Получено. Чтобы выводилось 2 знака после запятой, в меню… 3 Сделать консолидацию данных по категориям: § установить курсор в первую ячейку области, где будет располагаться консолидиpoванная таблица, например в ячейку…