В настоящее время среди табличных процессоров наибольшее распространение получили Lotus 1-2-3

 

Введение

 

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

Идея создания электронной таблицы возникла у студента Гарвардского университете (США) Дэна Бриклина в 1979 г. Выполняя скучные вычисления экономического характера, он и его друг Боб Франкстон, который разбирался в программировании, разработали первую программу электронной таблицы, названную ими VisiCalc.

В настоящее время среди табличных процессоров наибольшее распространение получили Lotus 1-2-3, QuatroPro, SuperCalc и Microsoft Excel, версия которого Excel 2000 рассматривается в настоящем методическом пособии.

Первый вариант табличного процессора Excel фирмы Microsoft появился в 1987 г. В настоящее время широко применяются его последние версии: Excel 2000 для Windows 2000 и Excel XP для Windows XP.

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

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

· сортировать и преобразовывать данные;

· представлять результаты в графическом виде.

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

 

Занятие 1, 2 (4 ч., лабораторное)

Тема: Составление штатного расписания хозрасчетного пансионата с помощью Excel.

 

Постановка задачи

1) 5-7 санитарок; 2) 8-10 медсестер; 3) 10-12 врачей;

Ход работы

    A B C D E F G H Коэф. Коэф. …   2. В ячейку H3 занесите в качестве первого приближения значение заработной платы санитарки 150 и установите для нее…

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

1. Составьте штатное расписание кафе.

 

 

Занятие 3, 4 (4 ч., лабораторное)

Тема: Решение задач линейного программирования с помощью Excel.

 

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

 

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

 

Ресурсы	Нормы затрат ресурсов на одно изделие	Общее количество ресурсов
Столы	Стулья
Древесина, м 3	0,5	0,04
Трудоемкость, чел.-ч.		0,6
Прибыль, руб.			-

 

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

 

Обозначим через x ₁ количество столов, а через x ₂ – количество стульев.

Тогда матмодель примет следующий вид:

F(x) = 180 x ₁ + 20 x ₂ à max – целевая функция, описывающая суммарную прибыль мебельной фабрики от реализации столов и стульев;

0,5 x ₁ + 0,04 x ₂ £ 200 – ограничение по первому ресурсу (древесине),

12 x ₁ + 0,6 x ₂ £ 1800 – ограничение по второму ресурсу (трудоемкости),

x ₁ ³ 80 – ограничение на количество столов согласно контракту с муниципалитетом,

x ₁ ³ 0, x ₂ ³ 0 – произведенное количество столов должно быть больше или равно 0,

x ₁, x ₂ – целые числа – план производства не может быть выражен в дробных числах.

 

Ход работы

    A B C D E F Ресурсы Столы Стулья …   Область ячеек, отмеченная серым цветом, предназначена для размещения результатов расчета: найденных значений объемов…

Ход работы

    A B C D E F G H I J Запасы товара…   Область ячеек, отмеченная серым цветом, предназначена для размещения результатов расчета: найденных значений объемов…

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

1. Торговое предприятие планирует организовать продажу четырех видов товара (A, B, C, D), используя при этом только два вида ресурсов: рабочее время продавцов в количестве 840 ч. и площадь торгового зала 180 м ².

При этом известны плановые нормативы затрат этих ресурсов в расчете на единицу товаров A, B, C и D и прибыль от их продажи, которые представлены в следующей таблице:

 

Ресурсы	Нормы затрат ресурсов на одно изделие	Общее количество ресурсов
A	B	C	D
Расход рабочего времени на единицу товара, ч.	0,6	0,8	0,6	0,4
Использование площади торгового зала на единицу товара, м 2	0,1	0,2	0,4	0,1
Прибыль, руб.					-

 

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

2. В пункте назначения N существует спрос на следующие товары:

 

Наименование товара	Спрос, единиц
Товар1
Товар2
Товар3

 

Товары находятся на нескольких складах. Запасы товара на складах (единиц) представлены в следующей таблице:

 

Наименование товара	Склад1	Склад2	Склад3	Склад4	Склад5
Товар1
Товар2
Товар3

 

Стоимость доставки единицы товара в пункт назначения N (руб.) представлена в следующей таблице:

 

 

Наименование товара	Склад1	Склад2	Склад3	Склад4	Склад5
Товар1
Товар2
Товар3

 

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

 

Занятие 7, 8 (4 ч., лабораторное)

Тема: Оценка эффективности денежных вложений с помощью Excel.

 

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

· БЗ();

· ПЗ();

· ППЛАТ().

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

 

Пример 1: найдите стоимость суммы $1000 через 20 лет при следующих условиях:

1. Деньги вложены на сберегательный счет с годовой процентной ставкой 5%.

2. На всю сумму на фондовой бирже куплены акции, прибыль по которым составляет в среднем 12,3% в год.

3. Деньги спрятаны в кубышку, а действующий процент инфляции равен 2,7% в год.

В нашем примере начальная сумма $1000 – это приведенная стоимость. Конечная сумма, которая получится в конце указанного периода (через 20 лет) – это будущая стоимость. Для вычисления будущей стоимости, если известна приведенная стоимость, периодическая процентная ставка и количество начислений процентов, можно использовать функцию БЗ(). Синтаксис этой функции:

 

БЗ(норма;число_периодов;выплата;нз;тип),

 

где норма – процентная ставка за период вложения;

число_периодов – количество начислений процентов;

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

нз – приведенная стоимость;

тип – определяет время начислений: в начале периода (тип = 1) или в конце периода (тип = 0). Если аргумент опущен, то предполагается что тип = 0.

 

Ход работы

    A B C D E F G Использование функции БЗ …   В ячейке B5 установите процентный формат, а в ячейке B8 – денежный: Формат è Ячейки… è вкладка Число. …

Ход работы

    A B C D E F G Использование функции ПЗ …   В ячейке B5 установите процентный формат, а в ячейке B8 – денежный: Формат è Ячейки… è вкладка Число. …

Ход работы

    A B C D E F G Использование функции ППЛАТ …   B5: 9% / 12 месяцев = 0,75%; B6: 4 года * 12 месяцев = 48 месяцев.

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

1. Сколько денег необходимо положить на сберегательный счет в банке под 5% годовых, чтобы иметь возможность в течение ближайших 10 лет тратить по $5700 ежегодно?

2. На каких условиях выгоднее купить автомобиль стоимостью $15000: взять ссуду под 1,5% в год (номинальная процентная ставка, проценты по которой начисляются раз в месяц) на 4 года и заплатить полную стоимость или получить скидку $1500 и взять ссуду под 9% в год (номинальная процентная ставка, проценты по которой начисляются раз в месяц) на 4 года?

3. На покупку хорошего дома нередко нужно не менее $100000. Обычно банки предоставляют ссуды сроком на 15, 20 или 30 лет.

а) Вычислите ежемесячную выплату, которая требуется в случае предоставления ссуды общей суммой $100000 под 7% в год сроком на 30 лет.

б) На сколько увеличится сумма ежемесячных выплат, если срок ссуды уменьшится до 15 лет?

4. Молодые люди планируют купить дом. Они собираются взять ссуду сроком на 30 лет, ежемесячно выплачивая при этом $500. Ссуду на какую сумму они могут взять, если годовая процентная ставка составляет 7%?

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

а) новый спортивный автомобиль стоимостью $26000 берется напрокат сроком на 36 месяцев с ежемесячными выплатами $380 и начальным взносом $1750. В конце срока клиент возвращает автомобиль, не получая при этом никакой компенсации;

б) новый спортивный автомобиль стоит $26000. Чтобы его приобрести, необходимо уплатить начальный взнос $1750, а для покрытия оставшейся суммы можно взять ссуду сроком на 60 месяцев с годовой процентной ставкой, равной 9%. По истечении трех лет автомобиль продается за $15000, а вырученные деньги идут на выплату оставшейся части ссуды, после чего у клиента останется некоторая сумма в наличных.

 

Занятие 10 (2 ч., лабораторное)

Тема: Использование макросов в Excel.

 

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

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

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

Макросы в Excel создаются двумя способами: путем записи последовательности команд или составления подпрограммы на языке Visual Basic for Applications (VBA). Мы рассмотрим простейший способ создания макроса – его запись.

 

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

 

(*).

 

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

212 ^о F = 100 ^о C (температура кипения воды);

98,6 ^о F = 37 ^о C (температура тела человека);

32 ^о F = 0 ^о C (температура замерзания воды);

-40 ^о F = -40 ^о C (точка равенства температур).

 

Ход работы

    A B C Температура (о F) (о C)   …   2. Поместите курсор в ячейку B4.

Ход работы

    A B C Динамика количества отдыхающих в пансионате …   2. На Листе2 постройте диаграмму Точечная, показывающую динамику количества отдыхающих в пансионате: Вставка è…

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

1. Создайте макрос, преобразующий температуру по шкале Цельсия в градусы Фаренгейта.

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

 

Занятие 11 (2 ч., лабораторное)

Тема: Связь между файлами Excel.

 

Постановка задачи

При заполнении ведомостей для учета результатов сдачи экзаменов использовать оценки 2, 3, 4, 5, н/я (неявка). В ведомости производить подсчет общего… В ведомости начисления стипендии производить подсчет количества сданных… · если средний балл больше либо равен 4,5, то выплачивается 50%-ная надбавка к базовой стипендии;

Ход работы

    A B C D E F G H I J   …   Столбцы F, G, H, I, J – дополнительные, они необходимы для подсчета общего количества каждой оценки.

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

 

1. Информатика: Учебник / Под ред. проф. Н. В. Макаровой. – 2-е изд. – М.: Финансы и статистика, 1998. – 768 с.

2. Информатика. Базовый курс / Симонович С. В. и др. – СПб: Питер, 2000. – 640 с.

3. Острейковский В. А. Информатика: Учеб. для вузов. – М.: Высш. шк., 2000. – 511 с.

4. Основы современных компьютерных технологий: Учебное пособие / Под ред. проф. А.Д. Хомоненко – СПб.: КОРОНА принт, 1998. – 448 с.

5. Карлберг, Конрад Бизнес-анализ с помощью Excel.: Пер. с англ. – К.: Диалектика, 1997. – 448 с.