Excel

СОДЕРЖАНИЕ

 

Предисловие. 4

Методические указания. 6

Лабораторная работа 1. Изучение основ Excel. Заполнение таблиц 8

Лабораторная работа 2. Построение диаграмм и графиков функций 33

Лабораторная работа 3. Трендовый анализ. 46

Лабораторная работа 4. Численное решение уравнений. 54

Лабораторная работа 5. Сортировка и фильтрация данных. 70

Лабораторная работа 6. Сводные таблицы. 77

Лабораторная работа 7. Консолидация данных (связь таблиц) 80

Лабораторная работа 8. Создание простых макросов. 83

Лабораторная работа 9. Статистический анализ данных. 88

Лабораторная работа 10. Финансовые расчеты. 96

Лабораторная работа 11. Моделирование развития финансовой пирамиды 108

Лабораторная работа 12. Задачи оптимизации в экономике. 114

Комментарии. 140

Литература. 145

 

Предисловие

 

В 1979 году выпускник Гарвардского университета Дэн Бриклин и профессиональный программист Боб Франкстон предложили миру способ автоматизации трудоемких и утомительных операций пересчета, которые всегда требуются при ручном составлении таблиц в бухгалтерском и банковском учете, в проектно-сметных работах, при решении планово-экономических задач. Это была программа VisiCalc[1], которая позволяла представлять данные в виде таблицы на экране дисплея, прокручивать эту электронную таблицу по строкам и столбцам и, самое главное, обладала удивительным свойством – в таблице проводился автоматический пересчет содержимого ячеек при изменении значений одной из них. Причем все действия были очень наглядны – все выполнялось в соответствии с принципом WYSIWYG («What You See Is What You Get»).

Популярность этой программы была феноменальной. Естественно, в первую очередь ею заинтересовались представители делового мира, что, кстати, заметно стимулировало продажу персональных компьютеров. По некоторым оценкам, более четверти проданных машин Apple II в 1979 году покупались для того, чтобы получить возможность работать на VisiCalc. Компьютер превратился в неотъемлемый инструмент бизнеса.

Но создатели VisiCalc не сумели вовремя переориентироваться на появившиеся в 1981 году персональные компьютеры IBM PC, и там лидирующие позиции заняла компания Lotus Development со своими электронными таблицами Lotus 1-2-3. В 1985 году она купила VisiCalc и практически похоронила ее, прекратив все разработки в этом направлении.

В настоящее время лидирующее положение (80% всех объемов продаж) на рынке электронных таблиц занимает Excel[2] корпорации Microsoft, входящий в состав интегрированного пакета прикладных программ Microsoft Office for Windows[3].

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

· создание и редактирование электронных таблиц с применением богатого набора встроенных функций;

· оформление и печать электронных таблиц;

· построение диаграмм и графиков различной степени наглядности и детализации;

· работа с электронными таблицами как с базами данных: фильтрация, сортировка, создание итоговых и сводных таблиц, консолидация данных из различных таблиц, в том числе из внешних баз данных;

· решение экономических задач типа «что – если» путем подбора параметров;

· решение оптимизационных задач;

· численное решение разнообразных математических задач;

· статистическая обработка данных;

· использование интегрированной среды разработки собственных программ – макросов Visual Basic for Applications.

Предлагаемый цикл из 12 лабораторных работ ориентирован на студентов экономических специальностей вузов и предназначен как для ознакомления с основами Excel, так и для применения Excel при решении экономико-математических задач. Он может быть использован при изучении дисциплин «Информатика», «Моделирование экономических процессов», «Разработка и применение ППП в экономике» в качестве учебно-методического материала, демонстрирующего возможности офисных пакетов прикладных программ по решению задач экономического моделирования.

Изучение возможностей Excel основано на версии Excel 2000 из MS Office 2000 Pro. Можно также использовать Excel 97 (или Excel 8), входящий в состав MS Office 97, и Excel 2002, входящий в состав MS Office ХР.

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

Методические указания

Лабораторные работы рекомендуется выполнять в том порядке, в котором они следуют, т.е. от «простого к сложному». Первые две работы знакомят с элементарными правилами заполнения таблиц и с… Все эти лабораторные работы предлагается использовать в составе лабораторного практикума по информатике для студентов…

Лабораторная работа 1. Изучение основ Excel. Заполнение таблиц

Цель работы: научиться заносить информацию (числа, текст, формулы) в ячейки листа Excel, освоить приемы построения и форматирования таблиц.   Электронная таблица Excel – одна из составных частей пакета прикладных программ Microsoft Office, работающего в среде…

Не забывайте, адреса ячеек набираются только латинским шрифтом!

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

Задание

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

2. Добавить, если необходимо, новые строки и столбцы.

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

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

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

 

Вариант 1. Рассчитать суммы распределения прибыли в НПО.

 

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

 

Вариант 2. Выполнить расчет движения материальных ценностей по складу по балансовым счетам 051, 052, 055 (в тыс. руб.).

 

Показатели, тыс. руб.	Номер счета	Всего по складу
Остаток на начало года Приход за год Расход за год Остаток на конец года в % к началу года	? ?	? ?	? ?	? ? ? ? ?

 

Вариант 3. Найти коэффициент Энгеля, т.е. рассчитать, какой процент занимают расходы на продукты в общей сумме расходов. Какие данные лучше коррелируются: расходы на продукты и расходы на жилье или расходы на продукты и расходы на одежду?

 

Виды расходов, руб.	Группа 1	Группа 2	Группа 3	Группа 4	Группа 5
Продукты
Жилье
Коммунальные услуги
Одежда
Другие расходы
Итого	?	?	?	?	?
Коэффициент Энгеля, %	?	?	?	?	?

Примечание. Использовать функцию КОРРЕЛ.

Вариант 4. Определить экономические показатели фирмы «Геркулес» в отчетном периоде.

 

Сегмент рынка	Себестоимость единицы продукции, руб.	Цена единицы продукции, руб.	Объем реализации, тыс. шт.	Затраты на производство, тыс. руб.	Выручка от реализации, тыс. руб.	Прибыль, тыс. руб.
A B C D				? ? ? ?	? ? ? ?	? ? ? ?
Итого	?	?	?	?

Вариант 5. Рассчитать объем территориального фонда обязательного медицинского страхования в 1997 году и сравнить показатели с 1996 г.

 

Поступления, млрд руб.	1996 г.	1997 г.	В % к итогу	В % к 1996 г.
Страховые взносы Платежи на обязательное медицинское страхование неработающего населения Штрафные санкции Другие поступления	101,48     75,03 12,77 10,8	148,88     118,32 18,0 16,92	?     ? ? ?	?     ? ? ?
Итого	?	?	?	?

 

Вариант 6. Рассчитать среднюю стоимость 1 кв. м общей площади жилых помещений в отдельных городах Западно-Сибирского региона в 1997 г.

 

Рынок жилья, тыс. руб.	Томск	Новосибирск	Барнаул	Тюмень
Первичный рынок: - улучшенной планировки - элитные Средняя цена Средняя цена по отношению к томской, %	2640,4 2576,0 ?   ?	3354,6 3000,0 ?   ?	3132,0 4127,3 ?   ?	2886,5 3523,2 ?   ?
Вторичный рынок: - низкого качества - типовые - улучшенной планировки - элитные Средняя цена	2206,1 2268,7 2505,8 3805,6 ?	1394,7 1847,5 2176,4 3891,1 ?	1900,0 1989,2 2126,4 3605,3 ?	2486,7 2999,0 3150,3 4500,2 ?
Средняя цена по отношению к томской, %	?	?	?	?

Вариант 7. Рассчитать ежедневный предполагаемый доход от деятельности киносети.

 

Кинотеатры	Вместимость зрительного зала, мест	Средний % посещаемости	Средняя цена билета, руб.	Количество сеансов в день	Доход, тыс. руб.
Родина Сибиряк Аэлита Авангард					? ? ? ?
Среднее значение	?	?	?	?	?
Итого		?

 

Вариант 8. Рассчитать сумму оплаты за электроэнергию за 1-й квартал 2003 года. Определить средний ежемесячный расход электроэнергии.

 

Дата	Показания счетчика, кВт×ч	Расход электро-энергии	Тариф, руб./кВт×ч	Сумма, руб.
Текущее	Предыдущее
5.01.2003 г.		—	—	—	—
9.02.2003 г.		?	?	0,34	?
3.03.2003 г.		?	?	0,47	?
2.04.2003 г.		?	?	0,47	?
Итого к оплате	?

 

 

Вариант 9. Рассчитать сумму оплаты за услуги водоканала и теплосетей за март 2000 года, если в квартире площадью 71,4 кв. м проживает 4 чел. Каков удельный вес каждого платежа в общей сумме?

 

Вид платежа	Стоимость за единицу услуги (вода – на 1 чел., отопление – на 1 кв. м), руб.	% понижения	Стоимость эконом. обоснованная, руб.	Начислено фактически, руб.	Долг (+); переплата (-), руб.	Итого, руб.
Экономически обоснованная	С учетом понижающего коэффициента
Вода холодная и канализация Горячая вода Отопление	36,02 39,92 7,08	? ? ?		? ? ?	? ? ?	-23	? ? ?
Итого	?	?	?	?

Вариант 10. Рассчитать сумму оплаты за жилищно-коммунальные услуги за март 2000 года, если в квартире площадью 71,4 кв. м проживает 4 человека. Общая стоимость технического обслуживания и капитального ремонта исчисляется исходя из площади квартиры, остальные позиции – по числу проживающих. Каков удельный вес каждого платежа в общей сумме?

 

 

Вид платежа	Стоимость за единицу услуги, руб.	% понижения	Стоимость эконом. обоснованная, руб.	Начислено фактически, руб.	Долг (+); переплата (-), руб.	Итого, руб.
Экономически обоснованная	С учетом понижающего коэффициента
Техническое обслуживание Капитальный ремонт Очистка стоков Вывоз мусора Лифт Коллективная антенна	1,93   1,72 5,43 2,36 9,78   3,80	?   ? ? ? ?   ?		?   ? ? ? ?   ?	?   ? ? ? ?   ?	-3,25   3,00	?   ? ? ? ?   ?
Итого	?	?	?	?

Вариант 11. Расчет затрат на выработку тепла по котельным МП «ТепТоп» (в тыс. руб.).

 

Статья	Тип котельной	Всего
Газовая котельная	Угольная котельная
1. Материалы 2. Амортизация 3. Вода 4. Электроэнергия 5. Заработная плата с начислениями 6. Топливо 7. Ремонтный фонд 8. Цеховые расходы (11,9% от ст. 10) 9. Общие эксплуатационные расходы (9,1% от ст. 10) 10. Итого затрат по котельным	84,2 165,6 607,1 339,3 621,2 1234,5 590,0 ?   ? ?	85,5 337,5 80,8 333,9 3081,0 2194,7 320,6 ?   ? ?	? ? ? ? ? ? ? ?   ? ?
11. Косвенные затраты 12. Всего затрат по котельным с учетом косвенных 13. Рентабельность (15% от ст. 12)	391,5   ? ?	1709,5   ? ?	?   ? ?
14. Итого затрат с учетом рентабельности	?	?	?
15. Доля затрат котельных разного типа, %	?	?	?

Вариант 12. Проанализировать динамику поступления товаров от поставщиков.

 

Поставщики	1998 г., млн руб.	1999 г., млн руб.	Превышение	В % к 1998 г.	Удельный вес в 1998 г.	Удельный вес в 1999 г.
ООО «Прима» АОЗТ «Томь» ЧП «Сантик» ОАО «Гермес» Всего	15,5 23,4 0,96 7,5 ?	16,9 32,1 1,2 6,4 ?	? ? ? ? ?	? ? ? ? ?	? ? ? ? ?	? ? ? ? ?

 

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

 

Ф.И.О.	Тарифный разряд	% выполнения плана	Тарифная ставка, руб.	Заработная плата с премией, руб.
Пряхин Н.П. Суховейко А.Д. Лоханкин В.А. Пферд Л.Ф. Севрюгов Л.А. Гигиенишвили Г.С. Птибурдуков А.И.			? ? ? ? ? ? ?	? ? ? ? ? ? ?

Примечание 1. Тарифная ставка определяется в зависимости от разряда: 1-й разряд – 4000 руб.; 2-й разряд – 6500 руб.; 3-й разряд – 8000 руб. Тарифные ставки оформить отдельной таблицей.

Примечание 2. Размер премиальных определяется в зависимости от выполнения плана:

- ниже 100% – премия не начисляется;

- 100% – премия 20% от тарифной ставки;

- 101…110% – премия 30%;

- 111…115% – премия 40%.

Для реализации алгоритма начисления используйте вложенные функции ЕСЛИ.

Вариант 14. Сравнить доходную часть городского бюджета в 1999 и 2000 году.

 

Статья	1999 г. (отчет), тыс. руб.	Удельный вес, %	2000 г. (план), тыс. руб.	Удельный вес, %	Превышение	В % к 1999 г
Налоговые доходы 1. Налоги на прибыль (доход), прирост капитала 2. Налоги на товары и услуги, лицензионные сборы 3. Налоги на совокупный доход 4. Налоги на имущество 5. Платежи за пользование природными ресурсами 6. Прочие налоги, пошлины и сборы Неналоговые доходы 1. Доходы от имущества, находящегося в гос. собственности 2. Административные платежи и сборы 3. Штрафные санкции Итого доходов	?               ?       ?	?   ?     ?   ? ?     ?   ? ?     ?   ? ?	?               ?       ?	?   ?     ?   ? ?     ?   ? ?     ?   ? ?	?   ?     ?   ? ?     ?   ? ?     ?   ? ? ?	?   ?     ?   ? ?     ?   ? ?     ?   ? ? ?

 

Вариант 15. Рассчитать начисление стипендии студентам по итогам сессии.

 

Ф.И.О.	Оценки за экзамены	Начислено стипендии, руб.
Информатика	Экономическая теория	Теория вероятности	ТЭИС
1. Иванопуло И.П. 2. Зверев Д.Б. 3. Калачов Н.А. 4. Калачова Е.Б. 5. Синицына З.С. 6. Писаревская Л.Г. 7. Тарасов А.Н. 8. Паровицкий С.Т.					? ? ? ? ? ? ? ?

Примечание. Размер стипендии составляет 2 МРОТ (минимальный размер оплаты труда, равный 300 руб.). Если все экзамены сданы на «пятерку», то надбавка составляет 50%. Если есть хотя бы одна «четверка» (при остальных «пятерках»), то надбавка составляет 25%. Если есть хотя бы одна «двойка», то стипендия не начисляется. Для реализации алгоритма начисления используйте вложенные функции ЕСЛИ.

 

Вариант 16. Рассчитать поступление и расходование денежных средств избирательных фондов зарегистрированных кандидатов в депутаты на должность главы администрации.

 

Показатели	Ф.И.О. зарегистрированного кандидата
Полесов В.М.	Чарушников М.П.	Кислярский М.Б.
Сумма, руб.	% к всего	Сумма, руб.	% к всего	Сумма, руб.	% к всего
Поступило средств всего, в том числе: от избирательной комиссии собственные средства кандидата пожертвования юридических лиц пожертвования физических лиц	?             —	?     ?     ?   ?   ?	?             —	?     ?     ?   ?   ?	?	?     ?     ?   ?   ?
Израсходовано средств всего, в том числе: радио и телевидение печатные издания публичные мероприятия канцелярские расходы Аренда помещений и автотранспорта Прочие расходы	?     —     —   —	?     ?   ?   ?   ?     ? ?	?	?     ?   ?   ?   ?     ? ?	?	?     ?   ?   ?   ?     ? ?
Остаток неизрасходованных средств	?	?	?

Вариант 17. Рассчитать доход от реализации колбасных изделий АОЗТ «Рога и копыта».

 

Наименование изделий	Объем производства, т	Цена за кг, руб.	Торгово-сбытовая скидка, %	Цена со скидкой, руб.	Сумма, руб.
Колбаса пермская п/к, 1с. Колбаса одесская п/к, 1с. Колбаски охотничьи, п/к, в/с, Колбаса польская п/к, 2с. Колбаса таллиннская п/к, в/с.			8,5   7,8   8,5	?   ?   ?   ?   ?	?   ?   ?   ?   ?
Всего	?				?

 

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

 

Ф.И.О.	Оклад (штат.), руб.	Начислено по окладу, руб.	Надбавки	Удержано	Всего начислено, руб.	Всего удержано, руб.	Сумма к выдаче, руб.
За ученую степень (в МРОТ)	Персональная (в% от гр.2)	Остальные (в % от гр.2)	Подоходный налог (12% от гр. 10)	Пенсионный налог (1% от гр. 10)	Профсоюз (1% от гр. 10)
Штатные Персицкий Р. Судейкин М. Наперников Г		? ? ?	—	—	—	? ? ?	? ? ?	? ? ?	? ? ?	? ? ?	? ? ?
Совместители Ляпис Н. Авдотьев К.		? ?	— 1,5	—	—	? ?	? ?	? ?	? ?	? ?	? ?

Примечание 1. Надбавка за ученую степень рассчитывается, исходя из минимального размера оплаты труда (МРОТ), равного 300 руб.

Примечание 2. Начисление по окладу (графа 3) с учетом районного коэффициента составляет 130% для штатных работников и 65% для совместителей.

 

Вариант 19. Рассчитать плановую сумму прибыли ЧП «Московские баранки».

 

Показатели	Год	1999 г в % к 1997г.
1. Объем продаж, т 2. Цена единицы продукции, руб./кг 3. Выручка от реализации продукции, руб. 4. Постоянные расходы, руб. 5. Переменные расходы (60% от ст. 3), руб. 6. Общая себестоимость продукции, руб. 7. Удельная себестоимость продукции, руб./кг 8. Валовая прибыль, руб.	?   ?   ?   ? ?	?   ?   ?   ? ?	?   ?   ?   ? ?	?   ?   ? ?   ?   ?   ? ?

Вариант 20. Оценить влияние изменения расхода материалов на изменение удельной материалоемкости изделия.

 

Материалы	1998 г.	1999 г.
Количество материала, т	Затраты на материалы в ценах 1999 г.	Количество материала, т	Цена 1 кг материала, у.е.	Затраты на материалы, у.е.
Сталь 3Х13 Сталь 12ХР3А Дюралюминий Бронза Латунь		? ? ? ? ?		3,4 4,3 4,2 8,7 5,3	? ? ? ? ?
Итого	—	?	—	—	?
Оценка изменения расхода материалов на изменение удельной материалоемкости изделия, DM	?

Примечание. Величина D_M вычисляется по формуле:

,

где цена всего изделия равна 25 у.е.

 

Вариант 21. Сформировать и заполнить накопительную ведомость по переоценке основных средств производства (в млн руб.).

 

Наименование объекта	Балансовая стоимость	Износ	Остаточная стоимость	Восстановительная полная стоимость	Восстановительная остаточная стоимость
Заводоуправление	12556,4	589,3	?	?	?
Диспетчерская	184,0	51,2	?	?	?
Цех №1	954,4	235,1	?	?	?
Цех №2	821,9	218,9	?	?	?
Цех №3	529,6	124,7	?	?	?
Цех №4	758,4	171,1	?	?	?
Склад №1	580,2	223,3	?	?	?
Склад №2	443,9	98,6	?	?	?
Склад №3	579,0	123,4	?	?	?
Склад №4	322,8	69,8	?	?	?
Итого	?	?	?	?	?

Примечание 1. Восстановительная полная стоимость (ВПС) объекта и восстановительная остаточная стоимость (ВОС) объекта вычисляются по балансовой стоимости (БС) и износу объекта (ИО):

ВПС = БС*К; ВОС = ОС*К,

где К = 3,0, если БС > 500 млн руб.;

К = 2,0, если БС £ 500 млн руб.

Примечание 2. Для заполнения столбцов использовать функцию ЕСЛИ.

Вариант 22.Рассчитать структуру розничной цены продукции (руб.), исходя из следующих данных:

 

Составляющие	Виды продукции
А	В	С	D
1. Себестоимость 2. Рентабельность (25% от п.1) 3. Оптовая цена предприятия 4. Акциз (70% от оптовой цены) 5. Наценка посредника 6. НДС (20% от отпускной цены фирмы) 7. Торговая наценка 8. Розничная цена	? ? ?   ? ?	? ? ?   ? ?	? ? ?   ? ?	? ? ?   ? ?

 

 

Вариант 23. Рассчитать розничную цену 1 кг хлеба различных сортов, исходя из следующих данных:

 

Составляющие	Сорт
Пшеничный в/с	Пшеничный 1с.	Ржаной в/с
1. Стоимость пшеницы (1 кг), руб. 2. Стоимость ржи (1 кг), руб. 3. Издержки элеваторов, руб. 4. Рентабельность затрат элеваторов, % 5. Издержки мельниц, руб. 6. Рентабельность затрат мельниц, % 7. Издержки хлебозаводов, руб. 8. Рентабельность затрат хлебозаводов, % 9. Оптовая цена 1 кг хлеба (при норме выхода продукта 140%), руб. 10. НДС (10% от ст. 9), руб. 11. Отпускная цена 1 кг хлеба с НДС, руб. 12. Торговая надбавка, % 13. Розничная цена 1 кг хлеба, руб.	1,4 — 0,6 0,6 0,9   ? ? ? ?	1,1 — 0,6 0,5 0,8   ? ? ? ?	— 1,5 0,6 0,5 0,8   ? ? ? ?

 

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

- до 30000 руб.: 3% в федеральный бюджет, 9% в бюджет субъектов федерации;

- от 30001 до 60000 руб.: 3% в федеральный бюджет, 2700 руб. + 12% с суммы, превышающей 30000 руб., в бюджет субъектов федерации;

- от 60001 до 90000 руб.: 3% в федеральный бюджет, 6300 руб. + 17% с суммы, превышающей 60000 руб., в бюджет субъектов федерации;

- от 90001 до 150000 руб.: 3% в федеральный бюджет, 11400 руб. + 22% с суммы, превышающей 90000 руб., в бюджет субъектов федерации;

- от 150001 и выше: 3% в федеральный бюджет, 24600 руб. + 32% с суммы, превышающей 150000 руб., в бюджет субъектов федерации.

Подсчитать итоговые суммы к выплате.

 

Вариант 25. Рассчитать прибыль, полученную от реализации трех видов продукции.

 

Показатель	Продукция	Итого
А	В	С
1. Цена изделия, тыс. руб. 2. Количество изделий, реализуемых в рассматриваемом периоде, шт. 3. Выручка от реализации, тыс. руб. 4. Удельный вес каждого изделия в общем объеме реализации, % 5. Переменные расходы в расчете на одно изделие, тыс. руб. 6. Переменные расходы по каждому виду продукции, тыс. руб. 7. Постоянные расходы в рассматриваемом периоде, тыс. руб.	?   ?     ?   —	?   ?     ?   —	?   ?     ?   —	—   ? ?   ?   —   ?
8. Итого расходов, тыс. руб. 9. Прибыль, тыс. руб.	? ?

 

Вариант 26. Рассчитать итоговое количество продукции, произведенное в России различными отраслями за 4 года, и относительные показатели каждого года.

 

 

Продукция	Года	Итого
	В % к 1997 г.	В % к общему итогу		В % к 1997 г.	В % к общему итогу		В % к 1997 г.	В % к общему итогу
Топливно-энергетические отрасли
Электроэнергия, млрд. кВт. ч Нефть, млн. т Газ естественный, млрд. м3 Уголь, млн. т		?   ?     ?   ?	?   ?     ?   ?		?   ?     ?   ?	?   ?     ?   ?		?   ?     ?   ?	?   ?     ?   ?		?   ?     ?   ?
Черная металлургия, млн т
Чугун Сталь Прокат готовых черных металлов Трубы стальные	36,1 48,7   36,5   3,57	? ?   ?   ?	? ?   ?   ?	39,2 51,3   39,1   3,72	? ?   ?   ?	? ?   ?   ?	35,6 49,2   38,8   3,50	? ?   ?   ?	? ?   ?   ?	37,3 48,4   37,8   3,47	? ?   ?   ?

 

 

Вариант 27. Используя рекламную прессу (газета «Реклама» г. Томск), проанализировать затраты на рекламу производителей товаров и услуг по различным направлениям. Стоимость рекламных объявлений в газете «Реклама» (в рублях за 1 кв. см): первая полоса - 75, последняя полоса - 43, полоса с программой ТВ - 30, обычная полоса - 15 (эти данные оформить отдельной таблицей и ссылаться на них через адреса ячеек).

Для определения места по затратам использовать функцию РАНГ.

 

Секторы рынка	Площадь объявлений, кв. см	Затраты на рекламу, тыс. руб.	Место по затратам
Первая полоса	Последняя полоса	Полоса с программой ТВ	Обычная полоса
Информационные технологии и услуги	430,8	135,0	208,8	5363,5	?	?
Продовольственные товары	41,3			4191,3	?	?
Строительно-хозяйственные товары и услуги	149,3	138,5	488,3	3697,8	?	?
Предметы гигиены и санитарии				949,0	?	?
Одежда и обувь			108,0	534,5	?	?
Мебель и торговое оборудование	134,0			2071,3	?	?
Лекарства и медицинские услуги			42,3	568,0	?	?
Автотовары и автоуслуги	50,0			2648,1	?	?
Бытовая техника и ее обслуживание		420,0		1680,3	?	?
Недвижимость	76,3			2087,3	?	?
Итого	?	?	?	?	?	?

 

Вариант 28. Рассчитать сумму, которая будет на счету через N лет, если на счет внесено 10000 руб., для различных годовых норм банковского процента i и разных периодов капитализации n. Расчет провести для N=0,5; 1; 3.

 

 

Годовая норма i,%	Период капитализации n, мес.
	? ? ? ? ? ? ? ?	? ? ? ? ? ? ? ?	? ? ? ? ? ? ? ?	? ? ? ? ? ? ? ?	? ? ? ? ? ? ? ?	? ? ? ? ? ? ? ?

 

Примечание. Формула для расчета сложных процентов имеет вид:

,

где P₀ – первоначально вложенная сумма;

P_N – сумма, которая будет получена через N лет;

m – количество начислений процентных платежей в течение годового периода (m=12/n);

i – годовая норма банковского процента.

 

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

,

где R_t – доходы (за вычетом налогов) от реализации проекта в t-ом году, тыс. руб.;

С_t – инвестиции в проект в t-ом году, тыс. руб.;

К – норма дохода по проекту;

n – период реализации проекта в годах.

Из четырех предлагаемых проектов выбрать наиболее эффективный, используя функцию МАКС. Норма доходности инвестиций – 10%. Расчеты оформить в виде таблицы, добавив к таблице исходных данных необходимое число строк или столбцов.

 

Период	1-й проект	2-й проект	3-й проект	4-й проект
Rt	Ct	Rt	Ct	Rt	Ct	Rt	Ct
1-й год   2-й год   3-й год   4-й год   5-ый год
Чистая текущая стоимость, тыс. руб.	?	?	?	?

Вариант 30. Рассчитать изменение денежных средств с учетом доходов, полученных от вложения финансовых средств в одно- , трех- и шестимесячные депозиты к началу 7-го месяца. Доход от одномесячного депозита – 1%, от трехмесячного депозита – 4%, от шестимесячного депозита – 9%. Результаты расчета предлагается оформить в виде следующей таблицы.

 

Статьи доходов (расходов), тыс. руб.	Период, месяц	Итог на начало 7-ого месяца
Начальная сумма Погашение депозитов Проценты по депозитам 1-месячный депозит 3-месячный депозит 6-месячный депозит Внутренние расходы Сумма на конец месяца	– – ?	? ? ? – – ?	? ? ? – – ?	? ? ? – ?	? ? ? – – ?	? ? ? – – ?	? ? ? – – – – –
Итого	?

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

Лабораторная работа 2. Построение диаграмм и графиков функций

Цель работы: научиться табулировать функции одного и двух переменных, строить графики и поверхности, освоить работу с функцией Excel ЕСЛИ и…   С помощью Excel можно превращать сухие и абстрактные строки и столбцы чисел в привлекательные и информативные графики…

Лабораторная работа 3. Трендовый анализ

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

Задания

Задание 1.Используя статистические данные по численности населения России (таблица 3.1), построить график функции одного переменного ЧислСтат (Год). Выделив линию графика, построить различные линии тренда, выражающие зависимость численности населения от времени: Вставка | Линия тренда, или, наведя курсор на линию графика, щелкнуть правой клавишей мыши; в появившемся контекстно-зависимом меню выбрать Добавить линию тренда.

 

Таблица 3.1 – Население России

 

Год, t	Численность статист., млн. чел.	Теория y=k*t+m	C-T	Теория y=a*t^2+d*t+c	C-T	Теория y=a*exp(b*t)	С-Т	Теория y=c*t^n	C-T
	117,5
	130,1
	137,6
	147,4
	148,5
	147,7
	148,7
	148,4
	148,3
	?
		S1=		S2=		S3=		S4=

Проверить линейную, полиномиальную (n=2), экспоненциальную, степенную линии: Тип | Построение линии тренда (рис. 3.1).

Для каждого тренда:

а) выдать аналитическую зависимость Численность (Год): Параметры | Показывать уравнение на диаграмме (рис. 3.2);

б) подсчитать по этим зависимостям соответствующую теоретическую (трендовую) численность, заполнив столбцы Теория;

в) найти погрешность С–Т (разницу между статистической и трендовой численностью);

г) рассчитать квадратичные отклонения S_i (i=1…4), используя функцию СУММКВ.

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

Примечание. Коэффициенты трендов выдаются с недостаточной точностью, и погрешность проведенных вычислений может оказаться слишком высокой. Поэтому рекомендуется увеличить число знаков после запятой в этих коэффициентах. Для этого, выделив уравнение тренда, следует щелкнуть по «горячей» клавише (Увеличить разрядность) на панели инструментов Форматирование. Необходимое число знаков подбирается опытным путем: если расчет S по трендовым формулам с коэффициентами, имеющими n+1 знак после запятой, даст значение, отличающееся на 1% от расчета S с n знаками после запятой, то найти S по трендовым формулам с коэффициентами, имеющими n+2 знака после запятой и т.д.

Задание 2.Введя дополнительное данное: значение численности России в 1998 году – 146,2 млн чел., уточнить экстраполяцию, используя только данные 90-х годов. Сравнить полученный результат с решением, полученным в задании 1, и сделать выводы.

 

Задание 3. По таблице 3.2 построить линию полиномиального тренда, наилучшим образом (по максимальному значению критерия детерминации R²) описывающую дневную температуру в г. Томске для одних и тех же месяцев двух разных лет. Просмотреть полиномы всех возможных степеней, предлагаемых Excel. Построить график изменения значения R² в зависимости от степени полинома.

Определить максимальную (функция МАКС), минимальную (функция МИН) и среднюю (функция СРЗНАЧ) температуру месяца.

С помощью функции СЧЕТЕСЛИ определить, сколько дней в месяце держалась температура ниже средней.

Синтаксис этой функции:

СЧЕТЕСЛИ(диапазон;критерий)

Таблица 3.2 – Дневная температура в г. Томске в 1997–1999 гг. (по данным А.С. Минькова)

1997 г.

Месяц

День

Январь

Февраль

Март

Апрель

Май

Июнь

Июль

Август

Сентябрь

Октябрь

Ноябрь

Декабрь

–19 –17 –12 –3 –2 –15 –9 –18 –22 –25 –20 –13 –12 –20 –20 –5 –2 –3 –15 –20 –6 –4 –4 –14 –6 –11 –14 –10 –10 –4

–6 –15 –20 –12 –9 –8 –6 –12 –6 –6 –5 –4 –11 –7 –4 –1 –1 –2 –3 –11 –20 –16 –16 –16 –10 –6 –1

–4 –1 –1 –1 –1 –2 –3 –1

–3 –4 –3 –1

–4 –5 –5 –1 –14 –15 –7 –5 –2 –4 –21 –24 –15 –9 –25 –30 –23 –10 –15 –31 –30 –26 –17

–14 –9 –19 –28 –26 –9 –19 –22 –12 –17 –19 –13 –9 –22 –23 –25 –31 –20 –10 –11 –17 –9 –20 –29 –23 –12 –18 –15 –6 –19 –22

Продолжение таблицы 3.2

1998 г.

Месяц

День

Январь

Февраль

Март

Апрель

Май

Июнь

Июль

Август

Сентябрь

Октябрь

Ноябрь

Декабрь

–32 –23 –13 –12 –13 –20 –20 –33 –30 –28 –27 –32 –29 –25 –27 –31 –24 –18 –16 –13 –19 –18 –15 –14 –15 –10 –12 –16 –13 –14 –17

–15 –12 –3 –10 –20 –14 –15 –16 –20 –29 –21 –10 –6 –3 –11 –16 –17 –12 –7 –5 –2 –10 –5 –4 –2 –4 –1

–6 –5 –3 –1 –1 –10 –15 –22 –1 –1 –2 –2 –8 –3 –4

–2 –3 –2 –1 –1 –2 –2

–2 –8 –1 –7 –10 –5

–7 –1 –1 –7 –12 –10 –15 –13 –12 –15 –9 –16 –17 –9 –17 –22 –20 –22 –25 –31 –34 –30 –23

–19 –22 –21 –16 –15 –4 –8 –13 –7 –9 –6 –10 –5 –2 –8 –10 –12 –5 –7 –6 –4 –3 –4 –1 –8 –20 –17 –13 –2 –5

Продолжение таблицы 3.2

1999 г.

Месяц

День

Январь

Февраль

Март

Апрель

Май

Июнь

Июль

Август

Сентябрь

Октябрь

Ноябрь

Декабрь

–4 –17 –25 –23 –25 –17 –14 –19 –14 –6 –12 –15 –17 –6 –9 –16 –20 –17 –8 –5 –8 –8 –31 –30 –36 –27 –21 –15 –9 –17 –18

–6 –8 –5 –2 –12 –9 –5 –2 –4 –4 –12 –16 –8 –11 –5 –6 –10 –7 –6 –3 –1 –7 –4 –2

–5 –10 –15 –23 –20 –22 –20 –15 –20 –13 –10 –13 –15 –13 –15 –10 –16 –13 –15 –5 –2 –11 –17 –12 –5

–1 –2 –2 –12 –6 –4

–2 –5 –7 –5 –13 –4 –2 –5 –8 –19 –25 –27 –25 –22 –19 –19 –17 –14 –12 –6

–7 –5 –4 –1 –6 –4 –5 –3 –13 –4 –2 –5 –4 –2 –1 –3 –12 –14 –17 –7 –1 –1 –5 –11 –10 –6 –19 –26 –36 –39

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

Задание 4.Используя Мастер диаграмм, построить температурное поле одного выбранного года (тип диаграммы – Поверхность, вид диаграммы – Контурная диаграмма).

 

 

Лабораторная работа 4. Численное решение уравнений

С помощью Excel можно решать разнообразные математические задачи, возникающие при реализации методов экономико-математического моделирования (и не… Например, балансовая модель производства и потребления совокупного…

Задание 1. Нахождение корней полиномов при помощи табулирования и сервисной функции Подбор параметра.

Известно, что если функция, определенная в интервале [a,b], имеет значения F(а) и F(b) с разными знаками, то в интервале [a,b] есть, по крайней мере, один корень.

Для полиномов

модули всех действительных корней x_k , k = 1…n расположены в диапазонах

, (4.1)

где .

Следовательно, все действительные положительные корни лежат в интервале [A, B], а все действительные отрицательные корни - в интервале [-B, -A].

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

1. Задать относительную погрешность вычислений корней EPS=0,00001 на вкладке Вычисления диалогового окна Параметры (Сервис з Параметры з Вычисления) (рис. 4.1).

2. Определить A и B по формуле (4.1), разместив предварительно на листе Excel таблицу коэффициентов полинома (таблица 4.1).

3. Составить таблицу {x, P(x)}, табулируя полином в начальных интервалах, например, с шагом H=(В-А)/10 (см. задание 2 лабораторной работы 2).

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

5. Уточнить значение корня с помощью сервисной команды Подбор параметра(меню Сервис) (рис. 4.2). В поле Установить в ячейке ввести адрес ячейки, где вычисляется значение полинома, соответствующее выбранному начальному приближению. В поле Значение ввести 0 (т.е. искомое значение полинома). В поле Изменяя значение ячейки ввести адрес ячейки, где находится начальное приближение к корню полинома.

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

 

 

В появившемся диалоговом окне отметить Значения. После этого ячейка готова к использованию в поле Изменяя значение ячейки диалогового окна Подбор параметра.

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

 

Таблица 4.1 - Полиномы

 

Вариант	Уравнение	Вариант	Уравнение
	x4+6x3+11x2–2x–28=0		x4+3x3+8x2–5=0
	x4+5x3+9x2+5x–1=0		x4–6x3+11x2+2x–28=0
	x4+3x3+3x2–2=0		x4–5x3+9x2–5x–1=0
	x4+x3–7x2+8x–6=0		x4–3x3+3x2–2=0
	x4–10x3+16x+5=0		x4–x3–7x2–8x–6=0
	x4–3x3–4x2–x–3=0		x4–10x2–16x+5=0
	x4+4x3+4x2+4x–1=0		x4+3x3+4x2+x–3=0
	x4+6x3+13x2+10x+1=0		x4–4x3–4x2–4x–1=0
	x4+x3–4x2+16x–8=0		x4+2x3+3x2+2x–2=0
	x4–x3–4x2–11x–3=0		x4–6x3+13x2–10x+1=0
	x4–6x3–12x–8=0		x4–3x2+4x–3=0
	x4+4x3+4x2–4=0		x4–6x2+12x–8=0
	x4+x3+2x+1=0		x4–4x3+4x2–4=0
	x4+2x3+x2+2x+1=0		x4–x3–2x+1=0
	x4+3x2–4x–1=0		x4–2x3+x2–2x+1=0

 

Задание 2.Нахождение корней нелинейных уравнений с помощью метода итераций.

Пусть дано уравнение f(x)=0. Для нахождения его корней методом итераций уравнение представляют в виде x=F(x) и записывают итерационную схему

, (4.2)

с помощью которой строится итерационный процесс уточнения корней, начиная с начального значения x₀, которое выбирается самостоятельно. Достаточное условие сходимости процесса: в окрестности корня | F ^/(x)| <1.

Порядок действий в Excel может быть следующий.

1. Представить данное уравнение в виде x=F(x).

2. Создать таблицу с заголовками столбцов Номер шага, Очередное приближение к корню, Проверка на точность.

3. В первую ячейку первой строки таблицы занести значение 0, во вторую – начальное приближение.

4. В следующие строки занести, соответственно, номер очередного шага, итерационную формулу, вычисляющую правую часть итерационной схемы, и условную формулу, позволяющую помещать в ячейку текст «Стоп» или «Дальше» в зависимости от выполнения заданной точности решения (см. п. 5).

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

Если процесс расходится (получающиеся приближения удаляются друг от друга) или сходится очень медленно, то необходимо сменить вид представления x=F(x).

В этом может оказать помощь другой итерационный метод решения нелинейных уравнений – метод Ньютона. Его итерационная схема имеет вид

. (4.3)

Сравнивая (4.2) и (4.3), замечаем, что в качестве функции F(x_k) можно взять правую часть из формулы (4.3). В большинстве случаев метод Ньютона сходится быстрее.

Данные для решения взять из таблицы 4.2. Точность решения EPS=0,0001.

6. После получения решения построить график, иллюст-рирующий процесс сходимости: по оси абсцисс отложить номер шага, по оси ординат – очередное приближение к корню.

7. Ответить на вопрос: любое ли начальное приближение можно задавать в вашем варианте? Определить (примерно) диапазон возможных начальных значений, проведя численный эксперимент.

 

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

Если метод итераций сходится не всегда, то метод бисекции (или метод деления отрезка пополам, или метод дихотомии) – безусловно сходящийся метод нахождения корней нелинейного уравнения f(x)=0, лишь бы был известен отрезок, на котором расположен корень уравнения.

Пусть непрерывная функция f(x) меняет знак на концах отрезка [a,b], т.е. f(a)Ч f(b) < 0. Назовем такой отрезок отрезком локализации корня: на нем есть, по крайней мере, один корень. Найдем координату середины этого отрезка c=(a+b)/2 и рассмотрим два получившихся отрезка [a,c] и [c,b]. Если f(a)Ч f(с) < 0, то корень находится на отрезке [a,c], в противном случае – на отрезке [с,b]. Процесс деления пополам все новых и новых отрезков локализации корня продолжаем до тех пор, пока длина отрезка не станет меньше заданной величины точности решения EPS.

 

Таблица 4.2 – Нелинейные уравнения

 

Вариант	Уравнение	Вариант	Уравнение
	ln(x)+(x+1)3=0		x–sin(x)=0,25
	xЧ2x=1		tg(0,58x+0,1)=x2
	x–cos(x)=0		3x–cos(x)–1=0
	3x+cos(x)+1=0		lg(x)–7/(2x+6)=0
	x+ln(x)=0,5		x+lg(x)=0,5
	2–x=ln(x)		x3–4sin(x)=0
	(x–1)2=exp(x)/2		ctg(1,05x)–x2=0
	(2–x)exp(x)=0,5		xЧlg(x)–1,2=0
	2,2x–2x=0		ctg(x)–x/4=0
	x2+4sin(x)=0		2x–lg(x)–7=0
	2x–lg(x)=7		2arctg(x)-1/(2x3)=0
	5x–8Чln(x)=8		2cos(x+p/6)+x2=3x–2
	sin(x-0,5)–x+0,8=0		cos(x+0,3)=x2
	2Чlg(x)–x/2+1=0		x2cos(2x)=–1

 

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

- вычисление значений левой и правой границы отрезков локализации;

- нахождение середины отрезка;

- вычисление произведения значений функций в левой и правой границах отрезка (для контроля правильности алгоритма);

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

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

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

 

Задание 4. Решение систем линейных алгебраических уравнений.

В Excel имеются специальные функции для работы с матрицами (Вставка функции пМатематические):

МОБР вычисление обратной матрицы А^-1;

МОПРЕД вычисление определителя матрицы D;

МУМНОЖ нахождение произведения двух матриц.

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

 

(4.4)

 

или в матричном виде

АЧХ=В,

где А = {a_ij}– матрица коэффициентов при неизвестных; В = {b_ij} – вектор-столбец правых частей уравнений; Х = {x_ij} – вектор-столбец неизвестных.

Способ 1 (метод обратной матрицы). Решение имеет вид Х = А^–1ЧВ, где А^–1 – матрица, обратная по отношению к матрице А.

С помощью функции МОБР находится обратная матрица, а затем с помощью функции МУМНОЖ она перемножается с вектором-столбцом правых частей уравнений.

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

Примечание. При работе с матрицами перед вводом формулы необходимо выделить область на рабочем листе, куда будет выведен результат вычисления, а после задания исходных данных в поле функции выйти не как обычно, нажатием клавиши Enter или кнопки ОК, а нажатием клавиш Ctrl + Shift + Enter.

Способ 2 (правило Крамера). Если определитель D, составленный из коэффициентов при неизвестных, отличен от нуля, то решение имеет вид

x _j = D_j / D , j=1...n. (4.5)

Здесь D_j – дополнительный определитель, полученный из главного определителя системы D путем замены его j-го столбца вектором-столбцом В.

С помощью функции МОПРЕД находятся главный и дополнительные определители, и по формулам (4.5) вычисляются корни СЛАУ.

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

Предположим, что в (4.4) a₁₁ ¹ 0 (если это не так, следует переставить уравнения). Разделив первое уравнение системы на a₁₁ (этот коэффициент называется ведущим, или главным элементом), получим

Затем из каждого из остальных уравнений вычитается первое уравнение, умноженное на соответствующий коэффициент a_i1 (i=2,3,¼, n).

Эти n–1 уравнений принимают вид

где

Далее аналогичную процедуру выполняют с этой системой, оставляя в покое первое уравнение. Только теперь делят на другой ведущий элемент a₂₂⁽¹⁾ ¹0.

В результате исключения неизвестных приходим к СЛАУ с верхней треугольной матрицей с единицами на главной диагонали:

(4.6)

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

Прямой ход метода Гаусса завершен.

Обратный ход метода Гаусса заключается в нахождении неизвестных x_n, x_n–1, ... , x₁ , причем в указанном порядке.

В этом списке x_n уже определено из последнего уравнения системы (4.6), а общая формула обратного хода имеет вид:

Проиллюстрируем этот алгоритм на примере решения системы из трех уравнений.

1. Располагаем на листе Excel матрицу коэффициентов и столбец правых частей (т.н. расширенная матрица 3´4), например, в ячейках А4:D6 (рис. 4.4).

2. Выделяем диапазон ячеек А8:D8 и вводим формулу:

{=A4:D4/A4}.

Фигурные скобки появляются автоматически при вводе формулы комбинацией клавиш Shift+Ctrl+Enter, как признак того, что идет работа не с отдельными ячейками, а с массивами.

3. Выделяем диапазон ячеек А9:D9, вводим формулу

{=A5:D5-$A$8:$D$8*А5}

и копируем эту формулу в диапазон ячеек А10:D10. В ячейках А9 и А10 появились нули.

4. В ячейки А12:D12 копируем значения первой строки расширенной матрицы А8:D8, в ячейки А13:D13 – формулу

{=A9:D9/B9}.

При этом второй элемент главной диагонали матрицы коэффициентов становится равным единице.

В ячейки А14:D14 вводим формулу

{=A10:D10-$A$13:$D$13*B10}.

5. В ячейки А16:D17 копируем значения первых двух строк расширенной матрицы (А12:D13), а в ячейки А18:D18 – формулу

{=A14:D14/C14}.

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

6. В ячейку Е18 просто копируем значение ячейки D18 – это х₃. В ячейке Е17 записываем формулу для х₂:

= D17–C17*E18,

а в ячейке Е16 формулу для х₁:

= D16-C16*E18–В16*Е17.

Попутно можно получить значения определителя матрицы коэффициентов, перемножая ведущие элементы, находящиеся в ячейках А4, В9 и С14. Результат – в ячейке F17.

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

 

Таблица 4.3 – Системы линейных алгебраических уравнений

 

Вариант	СЛАУ	Вариант	СЛАУ
	2,7x1+3,3x2+1,3x3=2,1; 3,5x1–1,7x2+2,8x3=1,7; 4,1x1+5,8x2–1,7x3=0,8		0,34x1+0,71x2+0,63x3=2,08; 0,71x1–0,65x2–0,18x3=0,17; 1,17x1–2,35x2+0,75x3=1,28
	1,7x1+2,8x2+1,9x3=0,7; 2,1x1+3,4x2+1,8x3=1,1; 4,2x1–3,3x2+1,3x3=2,1		3,75x1–0,28x2+0,17x3=0,75; 2,11x1–0,11x2–0,12x3=1,11; 0,22x1–3,17x2+1,81x3=0,05
	3,1x1+2,8x2+1,9x3=0,2; 1,9x1+3,1x2+2,1x3=2,1; 7,5x1+3,8x2+4,8x3=5,6		0,21x1–0,18x2+0,75x3=0,11; 0,13x1+0,75x2–0,11x3=2,00; 3,01x1–0,33x2+0,11x3=0,13
	9,1x1+5,6x2+7,8x3=9,8; 3,8x1+5,1x2+2,8x3=6,7; 4,1x1+5,7x2+1,2x3=5,8		0,13x1–0,14x2–2,00x3=0,15; 0,75x1+0,18x2–0,77x3=0,11; 0,28x1–0,17x2+0,39x3=0,12
	3,3x1+2,1x2+2,8x3=0,8; 4,1x1+3,7x2+4,8x3=5,7; 2,7x1+1,8x2+1,1x3=3,3		3,01x1–0,14x2–0,15x3=1,00; 1,11x1+0,13x2–0,75x3=0,13; 0,17x1–2,11x2+0,71x3=0,17
	7,6x1+5,8x2+4,7x3=10,1; 3,8x1+4,1x2+2,7x3=9,7; 2,9x1+2,1x2+3,8x3=7,8		0,92x1–0,83x2+0,62x3=2,15; 0,24x1–0,54x2+0,43x3=0,62; 0,73x1–0,81x2–0,67x3=0,88
	3,2x1–2,5x2+3,7x3=6,5; 0,5x1+0,34x2+1,7x3=-0,2 1,6x1+2,3x2–1,5x3=4,3		1,24x1–0,87x2–3,17x3=0,46; 2,11x1–0,45x2+1,44x3=1,50; 0,48x1+1,25x2–0,63x3=0,35
	5,4x1–2,3x2+3,4x3=-3; 4,2x1+1,7x2–2,3x3=2,7; 3,4x1+2,4x2+7,4x3=1,9		0,64x1–0,83x2+4,2x3=2,23; 0,58x1–0,83x2+1,43x3=1,71; 0,86x1+0,77x2+0,88x3=–0,54
	3,6x1+1,8x2–4,7x3=3,8; 2,7x1–3,6x2+1,9x3=0,4; 1,5x1+4,5x2+3,3x3=-1,6		0,32x1–0,42x2+0,85x3=1,32; 0,63x1–1,43x2–0,58x3=-0,44; 0,84x1–2,23x2–0,52x3=0,64
	5,6x1+2,7x2–1,7x3=1,9; 3,4x1–3,6x2–6,7x3=-2,4; 0,8x1+1,3x2+3,7x3=1,2		0,73x1+1,24x2–0,38x3=0,58; 1,25x1+0,66x2–0,78x3=0,66; 0,75x1+1,22x2–0,83x3=0,92
	2,7x1+0,9x2–1,5x3=3,5; 4,5x1–2,8x2+6,7x3=2,6; 5,1x1+3,7x2–1,4x3=-0,14		0,62x1–0,44x2–0,86x3=0,68; 0,83x1+0,42x2–0,56x3=1,24; 0,58x1–0,37x2–0,62x3=0,87

Продолжение таблицы 4.3

Вариант	СЛАУ	Вариант	СЛАУ
	4,5x1–3,5x2+7,4x3=2,5; 3,1x1–0,6x2–2,3x3=-1,5; 0,8x1+7,4x2–0,5x3=6,4		1,26x1–2,34x2+1,17x3=3,14; 0,75x1+1,24x2–0,48x3=–1,17; 3,44x1–1,85x2+1,16x3=1,83
	3,8x1+6,7x2–1,2x3=5,2; 6,4x1+1,3x2–2,7x3=3,8; 2,4x1–4,5x2+3,5x3=–0,6		0,46x1+1,72x2+2,53x3=2,44; 1,53x1–2,32x2–1,83x3=2,83; 0,75x1+0,86x2+3,72x3=1,06
	5,4x1–6,2x2–0,5x3=0,52; 3,4x1+2,3x2+0,8x3=–0,8; 2,4x1–1,1x2+3,8x3=1,8		2,47x1+0,65x2–1,88x3=1,24; 1,34x1+1,17x2+2,54x3=2,35; 0,86x1–1,73x2–1,08x3=3,15
	7,8x1+5,3x2+4,8x3=1,8; 3,3x1+1,1x2+1,8x3=2,3; 4,5x1+3,3x2+2,8x3=3,4		4,24x1+2,73x2–1,55x3=1,87; 2,34x1+1,27x2+3,15x3=2,16; 3,05x1–1,05x2–0,63x3=–1,25
	3,8x1+4,1x2–2,3x3=4,8; 2,1x1+3,9x2–5,8x3=3,3; 1,8x1+1,1x2–2,1x3=5,8		0,43x1+1,24x2–0,58x3=2,71; 0,74x1+0,83x2+1,17x3=1,26; 1,43x1–1,58x2+0,83x3=1,03
	1,7x1–2,2x2+3,0x3=1,8; 2,1x1+1,9x2–2,3x3=2,8; 4,2x1+3,9x2–3,1x3=5,1		0,43x1+0,63x2+1,44x3=2,18; 1,64x1–0,83x2–2,45x3=1,84; 0,58x1+1,55x2+3,18x3=0,74
	2,8x1+3,8x2–3,2x3=4,5; 2,5x1–2,8x2+3,3x3=7,1; 6,5x1–7,1x2+4,8x3=6,3		1,24x1+0,62x2–0,95x3=1,43; 2,15x1–1,18x2+0,57x3=2,43; 1,72x1–0,83x2+1,57x3=3,88
	3,3x1+3,7x2+4,2x3=5,8; 2,7x1+2,3x2–2,9x3=6,1; 4,1x1+4,8x2–5,0x3=7,0		0,62x1+0,56x2–0,43x3=1,16; 1,32x1–0,88x2+1,76x3=2,07; 0,73x1+1,42x2–0,34x3=2,18
	7,1x1+6,8x2+6,1x3=7,0; 5,0x1+4,8x2+5,3x3=6,1; 8,2x1+7,8x2+7,1x3=5,8		1,06x1+0,34x2+1,26x3=1,17; 2,54x1–1,16x2+0,55x3=2,23; 1,34x1–0,47x2–0,83x3=3,26

 

 

Задание 5. Решение систем нелинейных уравнений.

С помощью сервисной программы Поиск решения (Сервис | Поиск решения) в Excel можно решать системы нелинейных уравнений.

В общем случае система нелинейных уравнений имеет вид:

(4.7)

Составим новую функцию (назовем ее целевой функцией) F(x₁, х₂, ..., х_n), представляющую собой сумму квадратов левых частей уравнений:

. (4.8)

Очевидно, переменные x₁, х₂, ..., х_n , являющиеся решением системы (4.7), с необходимостью и достаточностью являются также решением уравнения

. (4.9)

Путь решения следующий.

На листе Excel отводим ячейки для неизвестных данной системы уравнений, например с А1 по А5 (если пять переменных), и вводим туда начальные приближения. В ячейку В2 вводим формулу, вычисляющую функцию (4.8).

Открываем диалоговое окно Поиск решения (рис. 4.5). В поле Установить целевую ячейку вводим В2, в группе Равной устанавливаем переключатель в положение Значению и в поле ввода задаем 0. В поле Изменяя ячейки вводим диапазон ячеек А1:А5.

После нажатия на кнопку Выполнить будет найдено решение, которое поместится в ячейки А1:А5. В ячейке В2 будет вычислено значение левой части уравнения (4.9) с относительной погрешностью, задаваемой в диалоговом окне Параметры поиска решения.

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

 

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

Примечание 3. Более подробно о возможностях программы Поиск решения изложено в лабораторной работе 12.

 

Решить систему нелинейных уравнений, взяв данные из таблицы 4.4. Проверить найденное решение.

Построить поверхность, описываемую функцией F(x, y) в окрестности всех найденных корней, пользуясь описанием, приведенным в лабораторной работе 2.

 

Таблица 4.4 – Системы нелинейных уравнений

 

Вариант	СНУ	Вариант	СНУ

Лабораторная работа 5. Сортировка и фильтрация данных

Цель работы: познакомиться со способами сортировки и фильтрации данных в Excel, научиться создавать сводные таблицы, изучить способы консолидации…   Область электронной таблицы можно рассматривать как базу данных. При этом столбцы называются полями, а строки –…

Лабораторная работа 6. Сводные таблицы

Цель работы: познакомиться со способами подведения промежуточных итогов в базах данных Excel.   Сводная таблица – это еще один инструмент Excel для обработки больших списков с данными.

A:Папка[ИмяФайла.xls] НазвЛиста'!АдрЯч .

Используя формулы связи, можно в ячейках итоговой таблицы совершать любые операции над ячейками исходных таблиц.   Способ 2. Консолидация данных. Консолидация позволяет объединять данные из областей-источников и выводить их в области…

Задания

Задание 1.Заполните таблицу 7.1, создав ее на листе Январь.

 

Таблица 7.1 – Торговая фирма «Шмидт и сыновья»

Исходные данные за январь
Наименование продукции	Цена в У.Е.	Продано	Выручка
Телевизоры
Видеомагнитофоны
Музыкальные центры
Видеокамеры
Видеоплейеры
Аудиоплейеры
Радиотелефоны
Итого:

 

Скопируйте таблицу на два других листа, назвав ихФевраль иМарт.Измените данные второго и третьего столбца (по вашему усмотрению).

На четвертом листе (Квартал. Способ 1) создайте макет таблицы сводных показателей (Продано и Выручка за квартал). Заполните эту таблицу, суммируя данные, находящиеся в соответствующих ячейках листов показателей за январь - март.

На пятом листе (Квартал. Способ 2) получите аналогичную таблицу, но с помощью консолидации.

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

Сравните оба способа.

 

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

 

Таблица 7.2 – Цены производителей на отдельные виды энергоресурсов^*)

Период	Цены, тыс. руб. за тонну
Нефть	Бензин автомобильный	Топливо дизельное	Мазут топочный	Газ естественный**)	Уголь
энергетический	для коксования
1997 г. апрель май июнь июль август сентябрь октябрь ноябрь декабрь   1998 г. январь февраль март					37,2 39,0 39,0 39,0 39,3 39,3 39,5 39,6 39,4     40,2 40,3 44,6
*)Цены приведены на энергоресурсы, отгруженные на внутрироссийский рынок **)За тысячу кубических метров

Лабораторная работа 8. Создание простых макросов

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

Задание 1

 

1. Оформите таблицу, используя следующие столбцы: Должность, Количество сотрудников, Коэффициенты оклада(два столбца), Оклад, Итого.

При решении задачи используйте сервисную функцию Excel «Подбор параметра»: Сервис | Подбор параметра (рис. 8.1).

В поле Установить в ячейке ввести адрес ячейки, где вычисляется общая месячная зарплата всех сотрудников больницы. В поле Значение ввести предельное значение месячного фонда зарплаты. В поле Изменяя значение ячейки ввести адрес ячейки, где находится оклад санитарки. После нажатия ОК произойдет автоматический подбор значения оклада санитарки таким образом, чтобы общий месячный фонд зарплаты составил $10.000.

 

2. Рассчитайте оклады для нескольких вариантов штата, изменяя количество штатных единиц в соответствии с заданными условиями.

3. Для выполнения предыдущего пункта задания вам пришлось несколько раз подряд выполнять рутинные действия по вызову команды Подбор параметра и заполнению всех полей ее диалогового окна.

Чтобы упростить эту работу, создайте простейший макрос – программу на языке VBA (Visual Basic for Application), встроенном в офисные программы. Это можно сделать, не зная пока самого языка, с помощью транслятора MacroRecorder, который переводит на язык VBA действия пользователя с момента его запуска до окончания записи макроса.

Для активизации MacroRecorder выбираем команду Сервис | Макрос | Начать запись. В появившемся диалоговом окне Запись макроса (рис. 8.2) задаем имя макроса (например, «Staff») и описание макроса (не обязательно).

В поле Сохранить в: оставляем опцию по умолчанию Эта книга (тогда созданный макрос сохранится на новом листе модуля в активной рабочей книге). Будущий макрос можно запускать с помощью сочетания клавиш клавиатуры, например, Ctrl+z, если это указать в поле Сочетание клавиш.

После нажатия ОК на экране появляется плавающая панель с кнопкой Остановить запись. Теперь все ваши действия над ячейками будут записываться!

 

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

Чтобы посмотреть, какая же все-таки VBA - программа «создана» нами, выполним команду Сервис | Макрос | Макросы|. В появившемся диалоговом окне выберем макрос с именем «Staff» и нажмем кнопку Изменить. Откроется главное окно редактора VBA с текстом записанного макроса, например:

 

Sub Staff()

‘

‘ Staff Макрос

' Штатное расписание больницы

‘

Range(“I14”).Select

Range(“I14”).GoalSeek Goal:=10000, ChangingCell:=Range(“H6”)

End Sub

 

Именно эта процедура и выполняется, если в диалоговом окне Макросы нажать кнопку Выполнить, или на клавиатуре набрать указанное сочетание Ctrl+z. Для заданного нового количества штатных единиц будут рассчитаны новые оклады.

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

Кнопка является одним из элементов управления листа, создаваемых с помощью панели инструментов Формы. Обычно этой панели нет на экране, поэтому выполняем командуСервис | Настройка | Панели инструментов | Формы. На экран выводится панель инструментов Формы(рис. 8.3). Выбираем на ней щелчком мыши форму Кнопка. При этом указатель мыши превращается в тонкий крестик. Щелкаем им по листу. На нем появляется кнопка с именем Кнопка1 и одновременно открывается диалоговое окно Назначение макроса объекту. В поле Имя
макроса выбираем имя нашего макроса «Staff».

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

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

Кнопку вызова макроса можно разместить и на любой из панелей инструментов. Для этого выполняем команду Сервис | Настройка | Команды | Макросы | Настраиваемая кнопка.Удерживая левую кнопку мыши, перетаскиваем кнопку на панель инструментов. Вызвав контекстно-зависимое меню, выбираем пункт Назначить макрос. В появившемся диалоговом окне выбираем имя нашего макроса Staff. Закрываем диалоговое окно Настройка. Кнопка готова к работе. Можно отредактировать всплывающее имя кнопки и рисунок на ней. Для этого необходимо сначала щелкнуть по ней правой кнопкой мыши и в появившемся меню выбрать Настройка. Затем еще раз щелкнуть по ней правой кнопкой мыши и в контекстно-зависимом меню выполнить необходимые операции.

 

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

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

Чтобы наложить запрет на изменение данных в созданном вами листе Excel, необходимо выполнить последовательность действий Сервис | Защита | Защитить лист. В появившемся окне следует отметить, что именно защищается: содержимое, объекты, сценарии. Можно указать пароль. Снимается защита по команде Сервис | Защита | Снять защиту листаи паролю.

 

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

 

Задание 2

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

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

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

Лабораторная работа 9. Статистический анализ данных

Цель работы: освоить технологии бизнес-анализа данных в Excel, используя функции пакета Анализ данных и некоторые статистические функции   Для того чтобы принять верное управленческое решение, прогнозировать и планировать успешную работу предприятия, фирмы,…

Задания

Задание 1

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

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

3. Задать значение плановой годовой выручки и с помощью функции СЧЕТЕСЛИ подсчитать, сколько магазинов перевыполнили план за год.

4. Используя функцию СРЗНАЧ, подсчитать среднюю ежемесячную выручку всех магазинов и среднюю выручку каждого магазина за год.

5. Используя функцию РАНГ, подсчитать место каждого магазина по объему продаж за год.

Синтаксис этой функции:

РАНГ(число;ссылка;порядок)

Число – это число в массиве, для которого определяется ранг.

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

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

6. Используя функцию ПРОЦЕНТРАНГ, оценить для каждого магазина, какова доля значений месячных выручек, не превосходящих значение 2000 тыс. руб.

Синтаксис функции:

ПРОЦЕНТРАНГ(массив;x;разрядность)

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

х – это значение, для которого определяется процентное содержание.

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

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

Медиана – это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Медиану заданных чисел возвращает функция МЕДИАНА.

Ее синтаксис:

МЕДИАНА(число1;число2; ...)

Число1, число2, ... – это от 1 до 30 чисел, для которых определяется медиана. Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа. Excel проверяет все числа, содержащиеся в аргументах, которые являются массивами или ссылками. Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; но ячейки, которые содержат нулевые значения, учитываются.

Для нахождения медианы (и других показателей ранжирования) также можно использовать функции КВАРТИЛЬилиПЕРСЕНТИЛЬ. Квартиль – это значения признака, делящего данный ряд на 4, а персентиль – на 100 равных частей.

Синтаксис функций:

КВАРТИЛЬ(массив;к)

Массив – это массив или интервал ячеек с числовыми значениями, для которых определяется значения квартилей.

Если аргумент к=0, то функция возвращает минимальное значение (т.е. работает аналогично функции МИН); если к=1, то функция возвращает первую квартиль; если к=2, то функция возвращает медиану массива (т.е. работает аналогично функции МЕДИАНА); если к=3, то функция возвращает третью квартиль; если к=4, то функция возвращает максимальное значение (т.е. работает аналогично функции МАКС).

ПЕРСЕНТИЛЬ(массив;часть)

Массив – это массив или интервал данных с численными значениями, для которых определяется значения персентилей.

Часть – это значение персентили в интервале от 0 до 1 включительно. Например, 0,5-ая персентиль дает значение медианы, 0,75-ая персентиль дает значение третьей квартили и т.п.

8. С помощью пакета сервисных программ Анализ данных (командаРанг и персентиль)найти порядковый и процентный ранги для каждого значения в массиве месячных выручек каждого магазина. Эта процедура применяется для анализа относительного взаиморасположения данных в наборе.

Выходная таблица содержит столбцы:

· порядковый номер числа в наборе исходных данных;

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

· ранг числа;

· значение процентранга.

Используя функцию ПЕРСЕНТИЛЬ,вычислить значение персентиля для одного из значений процентранга из полученной таблицы и сравнить с соответствующим значением из столбца исходных данных.

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

Найти эти же данные с помощью встроенных функций из категории Статистические.

10. Подсчитать для множества суммарных годовых выручек магазинов, сколько значений попадает в интервалы от 0 до 5000, от 5001 до 10000, от 10001 до 15000, от 15001 до 20000 тыс. руб., а также свыше 20000 тыс. руб., используя функцию ЧАСТОТА.

Синтаксис этой функции:

ЧАСТОТА(массив_данных;массив_карманов)

Массив_данных – это массив чисел, для которых вычисляются частоты. Если массив_данных не содержит значений, то функция ЧАСТОТА возвращает массив нулей.

Массив_карманов– это массив правых концов тех интервалов, в которых группируются значения аргумента массив_данных.

 

Таблица 9.1 Таблица описательной статистики

 

Маг 1
Среднее	1865,95
Стандартная ошибка	263,24
Медиана	1993,33
Мода	#Н/Д
Стандартное отклонение	911,90
Дисперсия выборки	831563,32
Эксцесс	-1,17
Асимметричность	-0,20
Интервал	2693,80
Минимум	500,00
Максимум	3193,80
Сумма	22391,42
Счет

 

 

Функция ЧАСТОТА возвращает распределение частот в виде вертикального массива, причем количество элементов в возвращаемом массиве на единицу больше числа элементов в массив_карманов. Дополнительный элемент в возвращаемом массиве содержит количество значений, больших чем максимальное значение в интервалах. Для работы с этой функцией необходимо сначала выделить область, куда попадут результаты вычисления, а после задания исходных данных в поле функции выйти не как обычно, нажатием клавиши Enter или кнопки ОК, а нажатием клавишCtrl + Shift + Enter (см. также лабораторную работу 4).

11. Вычислить эти же частоты с помощью пакета сервисных программ Анализ данных (командаГистограмма) (рис. 9.2), где поля Входной интервал и Интервал карманов соответствуют аргументам Массив_данных и Массив_карманов функции ЧАСТОТА. Построить гистограмму ЧАСТОТА (ОБЪЕМ РЕАЛИЗАЦИИ). Проанализировать характер поведения графика Интегральный процент.

12. Выбрав из меню Анализа данных команду Корреляция, получить коэффициенты корреляции выручки трех любых магазинов (попарно) за весь год (рис. 9.3). Сделать выводы.

Коэффициент корреляции используется для определения наличия взаимосвязи между двумя различными рядами данных X_i , Y_i , i = 1... n и имеет вид:

 

.

 

 

О хорошей корреляции говорят значения К, по модулю близкие к единице. Знак «+» соответствует прямой взаимосвязи, знак «-» – обратной.

Коэффициенты корреляции можно также найти с помощью функции КОРРЕЛ, входящей в категорию Статистические.

 

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

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

По желанию могут быть выведены три графика:

остатки как функция независимой переменной;

сопоставление значений по регрессионной модели с данными статистики;

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

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

Получить уравнение линейной регрессии с помощью функции ЛИНЕЙН.

Синтаксис функции:

ЛИНЕЙН(массив_У; массив_Х;константа;статистика)

массив_У – значения исследуемой статистической функции;

массив_Х – соответствующие значения независимой переменной;

константа – ИСТИНА (по умолчанию) для вычисления b в уравнении линейной регрессии у=а₁х₁+а₂х₂+ …+а_nх_n+b или ЛОЖЬ для b=0;

статистика – ИСТИНА для вывода регрессионной статистики.

Эта функция возвращает массив значений, поэтому прежде чем к ней обращаться, необходимо выделить массив ячеек размером (n+1) ´ 5, где n – число независимых переменных.

Если независимая переменная одна, то при значении аргумента статистика равным ЛОЖЬ, достаточно указать две ячейки (в одной строке!), где окажутся коэффициенты а и b линейной регрессии. Если значение аргумента статистика равно ИСТИНА, то следует указать 10 ячеек (массив 2´5). В первой строке окажутся коэффициенты а и b, во второй – стандартные значения ошибок для коэффициентов а и b, в третьей – коэффициент детерминации и стандартная ошибка для оценки функции, в четвертой – F-статистика (для оценки взаимосвязи зависимой и независимой переменной) и число степеней свободы (для определения уровня надежности регрессионной модели), в пятой – регрессионная сумма квадратов и остаточная сумма квадратов.

Лабораторная работа 10. Финансовые расчеты

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

Лабораторная работа 11. Моделирование развития финансовой пирамиды

Цель работы: освоить приемы решения обыкновенных дифференциальных уравнений с помощью Excel, провести их параметрические исследования и…   Развитие финансовой пирамиды во многом напоминает развитие эпидемии, когда число заболевших (купивших акции) в…

Задание

1. Построить таблицу, состоящую из следующих граф (столбцов): День; Курс продаж; Продано в день; Продано всего; Курс покупки; Куплено в день; Куплено всего; Сумма в кассе; Доход в день; Доход всего. Исходные данные использовать с абсолютной адресацией, выбирая их из Таблицы исходных данных. Сдвиг волны «покупка-продажа» задать программно с помощью функций Excel из категории Ссылки и массивы, например, СМЕЩилиВПР,используя их как аргумент функцииЕСЛИ.

Функция СМЕЩ возвращает ссылку на ячейку или диапазон ячеек, отстоящие от ячейки или диапазона ячеек на заданное число строк и столбцов. Возвращаемая ссылка может быть как отдельной ячейкой, так и диапазоном ячеек. Можно задавать количество возвращаемых строк и столбцов.

Синтаксис функции:

СМЕЩ(ссылка;смещ_по_стр;смещ_по_столбц;выс;шир)

Ссылка – это ссылка на ячейку или на диапазон смежных ячеек, от которых вычисляется смещение, в противном случае функция СМЕЩ возвращает значение ошибки #ЗНАЧ!.

Смещ_по_стр – это количество строк, которые нужно отсчитать вверх или вниз, так чтобы верхняя левая ячейка результата ссылалась на это место. Если значение положительное, то отсчитывается ниже начальной ссылки, если отрицательное, то выше начальной ссылки.

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

Выс – это высота (число строк) возвращаемой ссылки. Высота должна быть положительным числом.

Шир – это ширина (число столбцов) возвращаемой ссылки. Ширина должна быть положительным числом.

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

Функция ВПР ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы[18].

Синтаксис функции:

ВПР(иск_знач;таблица;номер_столбца;интерв_просмотр)

Иск_знач – это значение, которое должно быть найдено в первом столбце массива. Иск_знач может быть значением, ссылкой или текстовой строкой.

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

Номер_столбца – это номер столбца, в котором должно быть найдено соответствующее значение. Если номер_столбца меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если номер_столбца больше, чем количество столбцов в таблице, то функция ВПР возвращает значение ошибки #ССЫЛ!.

Интерв_просмотр – это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение (наибольшее значение, которое меньше, чем иск_знач). Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д. Кроме того, если параметр интерв_просмотр имеет значение ИСТИНА, то значения в первом столбце таблицы должны быть расположены в возрастающем порядке, в противном случае функция ВПР может выдать неправильный результат. Если интерв_просмотр имеет значение ЛОЖЬ, то таблица не обязана быть сортированной.

 

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

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

 

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

3. Определить сумму максимального дохода организатора пирамиды Д_MAX и день ее достижения D_MAX, используя при этом функции Excel МАКС и ПОИСКПОЗ.

Функция ПОИСКПОЗ возвращает относительное положение (позицию) элемента массива, который соответствует заданному значению указанным образом[19].

Синтаксис функции:

ПОИСКПОЗ(иск_знач,интервал,тип_сопост)

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

Интервал – это непрерывный интервал ячеек, возможно содержащих искомые значения. Интервал может быть массивом или ссылкой на массив.

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

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

4. Любое дело требует начальных расходов, иногда весьма существенных. С помощью сервисного средства Excel Подбор параметраподобрать такое минимальное значение начального капитала П₁^MIN, которое бы позволило не уйти в отрицательную сумму в кассе на начальном этапе развития пирамиды.

Указание. Найти предварительно локальный минимум функции Сумма в кассе на начальном участке строительства пирамиды (в диапазоне дней от D=1 до D_MAX).

5. Изменяя исходные данные, проследить за изменением дохода организатора (в каждом варианте изменять только один параметр!). Результаты исследований оформить на новом листе в виде таблицы параметрического исследования модели (табл. 11.2). Можно использовать Диспетчер сценариев. Сделать выводы.

 

Таблица 11.2 – Параметрическое исследование модели

 

Изменяемый параметр	Увеличиваем параметр	Уменьшаем параметр
Значение	День Х	Доходы на день Х	Значение	День Х	Доходы на день Х
M KA ...

 

Исходные данные для расчета.

 

Число жителей в городе M=1000000.

Коэффициент ажиотажа К_А=0,0000001.

Ежедневные расходы (руб.) R=1200.

Время между покупкой и продажей акции (дни) Т=50.

Норма прибыли (ежедневный процент от суммы в кассе) S=3.

Состояние на первый день:

- начальный капитал (руб.) П₁=100000;

- число купивших акции в первый день NK₁=100.

Лабораторная работа 12. Задачи оптимизации в экономике

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

КОММЕНТАРИИ

с анализом наиболее часто совершаемых ошибок[22]   Лабораторная работа №1 (INFOLAB1)

Литература

 

1. Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах. – СПб.: BHV, 2001. – 816 с.

2. Матвеев Л.А. Компьютерная поддержка решений. – СПб.: Специальная литература, 1998. – 472 с.

3. Овчаренко Е.К., Ильина О.П., Балыбердин Е.В. Финан-сово-экономические расчеты в Excel. Изд. 3-е. – М.: Филинъ, 1999. – 328 с.

4. Гусева О.Л., Миронова Н.Н. Excel для Windows. Практические работы //Информатика и образование. – 1996. – №2–6.

5. Лавренов С.М. Excel: Сборник примеров и задач. – М.: Финансы и статистика, 2002. – 336 с.

6. Качанов В.И. Компьютерные вычисления в средах Excel и MathCAD. – М.: Горячая линия-Телеком, 2002. – 328 с.

7. Буров А.В., Миньков С.Л., Ушаков В.М. Моделирование экономических процессов и систем: Учебное пособие. В 2-х частях: Ч.1, 158 с., 2001 г.; Ч.2.. 167 с., 2003 г. Изд-во Томского государственного педагогического университета.

 

[1] Visible Calculator (Наглядный калькулятор).

[2] Не правда ли, удачное название, связанное с игрой слов: Excellent – прекрасный, Cell – ячейка, Excel – превосходить.

[3] Кроме упомянутых программных продуктов также известны Quattro Pro (фирма Word Perfect – Novell Applications Group), SuperCalc (фирма Computer Associates).

[4] Если вы хотите, чтобы число воспринималось как текст, поставьте перед ним апостроф, т.е. знак «’».

[5] Какой именно знак используется для разделения целой и дробной части числа, определяется в операционной системе. Например, в Windows 9X это можно сделать, выбрав путь Пуск | Настройка | Панель управления | Языки и стандарты | Числа | Разделитель целой и дробной части числа и определив нужный знак.

[6] Частая ошибка – начинают формулу не со знака =, а с пробела.

^[7] Для более подробного знакомства с системой хранения дат и времени суток в MS Excel обратитесь в Справку Microsoft Excel.

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

[9] С некоторыми из них: МАКС, МИН, СРЗНАЧ, СЧЕТЕСЛИ вы познакомились ранее.

2 Файлы надстроек имеют расширения .xla, .xls и по умолчанию хранятся в папке Library (C:Program FilesMicrosoft OfficeOfficeLibrary).

[10] Приведение стоимостного показателя, относящегося к будущему, на более ранний момент времени.

[11] В Excel 2002 она называется БС.

 

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

[13] В Excel 2002 она называется ПС.

[14] В Excel 2002 она называется ЧПС.

 

[15] В Excel 2002 она называется ПЛТ.

[16] В Excel 2002 она называется СТАВКА.

[17] Очков В.Ф. Mathcad PLUS 6.0 для студентов и инженеров. – М.: КомпьютерПресс, 1996. – 238 с.

 

[18] Если сравниваемые значения расположены в столбце справа от искомых данных, то используется функция ГПР.

[19] Если нужен сам элемент, а не его позиция в диапазоне, то используется функция ПРОСМОТР.

[20] Это определяется тем, как была проведена установка MS Office на ПК. Подключение при первом обращении задано в установке «Типичная».

[21] Бинарные переменные могут принимать одно из двух значений: 0 или 1 и используются в задачах назначения.

[22] По опыту работы с первым изданием пособия: Миньков С.Л. EXCEL. Лабораторный практикум. – Томск: ТМЦДО, 2000. – 105 с.