Голоскоков К.П., Филиппова. И.Г. (сост.) Информационные технологии в экономике - файл n1.doc

приобрести
Голоскоков К.П., Филиппова. И.Г. (сост.) Информационные технологии в экономике
скачать (791 kb.)
Доступные файлы (1):
n1.doc791kb.05.06.2012 08:51скачать

n1.doc

  1   2


МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ

Федеральное агентство по образованию
Санкт-Петербургский государственный университет сервиса и экономики

Кафедра «Экономика туризма»
К.П. Голоскоков
И.Г. Филиппова

ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ
В ЭКОНОМИКЕ


Лабораторный практикум
для студентов специальности 080502.65 (060800)
«Экономика и управление на предприятии в сфере сервиса»


Санкт-Петербург

2006
Одобрены на заседании кафедры «Экономика туризма»,
протокол №9 от 13 апреля 2004 г.
Утверждены Методическим Советом ИМТиМ, протокол №10
от 18 мая 2004 г.

Информационные технологии в экономике. Лабораторный практикум для студентов специальности 080502.65 (060800) «Экономика и управление на предприятии в сфере сервиса». – СПб.: Изд-во СПбГУСЭ, 2006. – 47 с.


Составители: канд. тех. наук, доц. К.П. Голоскоков

канд. мед. наук, доц. И.Г. Филиппова

Рецензент: д-р экон. наук, проф. В.А. Черненко
© Санкт-Петербургский государственный университет
сервиса и экономики

2006 г.

Оглавление

Оглавление 4

Введение 5

Цели и задачи дисциплины 6

Цели и задачи лабораторных работ 6

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

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

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

Лабораторная работа №4 31

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

ЛИТЕРАТУРА 51

Введение

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

знать:

уметь:

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

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

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

Методические указания по выполнению лабораторных работ по дисциплине «Информационные технологии в экономике», подготовлены в полном соответствии с государственным образовательным стандартом и предназначены для студентов дневного отделения
по специальности 0608.08 «Экономика и управление на предприятии туризма».

Цели и задачи дисциплины

Целями изучения дисциплины являются:

Достижение указанных целей обеспечивается решением следующих задач:

Цели и задачи лабораторных работ

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

Поскольку главной задачей обучения является подготовка профессионально ориентированных пользователей информационных технологий, то важной особенностью лабораторных работ является ориентация на профессиональные задачи в рамках дисциплин ИТ. То есть в основу обучения должен быть положен принцип, который заключается в следующем: информация, с которой работает учащийся на ПК с помощью того или иного программного средства, связана с профессией. Так, изучение даже инструментальных программных средств, предназначенных для обработки информации любого содержания, таких как текстовой редактор Word, электронные таблицы
Excel, СУБД Access, должно осуществляться посредством рассмотрения задач, отражающих те или иные аспекты профессиональной деятельности, с которыми учащиеся знакомятся на уроках специальных дисциплин.

В предлагаемых лабораторных работах присутствуют элементы моделирования различных аспектов деятельности предприятия.

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

I.Общие приемы работы с таблицей:

II.Создать таблицы для расчета заработной платы 10 работникам предприятия:

  1. Создать рабочую книгу Расчет зарплаты.xls. Сохранить ее с указанным именем в папке «Мои документы». Установить включенным флажок «Всегда сохранять резервную копию».

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

  1. Ввести заголовок – Справочник работников предприятия. Установить жирный шрифт заголовка. Скорректировать отдельные элементы заголовка, нажав клавишу «F2».










Справочник работников предприятия






















Таб. Номер

Фамилия, имя, отчество

Разряд

Должность

Член
профсоюза

Дата
поступления

Отдел

Кол-во
льгот

%
удержания
алиментов




  1. Выделить ячейки заголовка и ввести команду ФОРМАТ/Ячейки. Выбрать на вкладке «Выравнивание» и указать горизонтальное по значению и вертикальное по верхнему краю.

  2. Ввести 10 строк с информацией с учетом следующих правил:

  1. Отсортировать строки таблицы по возрастанию табельного номера.

Необходимо рассчитать:

ЗП=ЗПР*ФТ/Т;

УПФ=ЗП*0.01;

УПН=(ЗП-УПФ-МЗП*Л)*ПРПДН/100;

УПВЗН=ЗП*ПРВЗН/100

(только для членов профсоюза);

УАЛ=ПРАЛ*(ЗП-УПН)/100

(только для лиц, выплачивающих алименты);

ЗПВ=ЗП-УПФ-УПН-УПВЗН,

где:

ФT – фактически отработанное время (дней);

Л – количество льгот;

ЗПР – оклад работника в соответствии с его разрядом;

МЗП – минимальный размер оплаты труда;

Т – плановое количество рабочих дней в месяце;

ПРАЛ – процент удержания алиментов;

ПРВЗН – процент удержания профсоюзных взносов;

ПРПДН – процент удержания подоходного налога.

Оклад работника зависит от его квалификации (разряда). Эта зависимость представлена в Разрядной сетке (Таблица 2).

Таблица 2

Разрядная сетка

Разряд

Оклад

1

80000

2

120000

3

160000

4

200000

5

240000

6

280000

7

320000


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

  1. На листе «Справочник» в ячейках H2:H12 создать список праздничных дней текущего года, например: 01.01.1998, 02.01.1998, 07.01.1998, 08.03.1998, 01.05.1998 и т. д. Установить для этих ячеек соответствующий формат отображения дат.

  1. Создать таблицу для расчета зарплаты за ЯНВАРЬ месяц:










Расчет зарплаты за январь 1998 г.



















Таб. Номер

Фамилия, имя, отчество

Оклад

Отработано дней

Начислено рублей

Удержано
в пенсионный фонд


Льготы

Подоходн. налог




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

  2. Заполнить колонку с табельными номерами работников (10 строк) в произвольном порядке.

  3. Ввести расчетные формулы в строку первого работника:

  1. Ввести внизу под расчетной таблицей справочные данные для текущего месяца:

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

ЧИСТРАБДНИ (нач_дата;кон_дата;праздники)

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

Ввести в качестве начальной даты текст: «01.01.1998».

Ввести в качестве конечной даты текст: «31.01.1998».

Ввести в качестве 3-го аргумента (праздники) диапазон ячеек на листе «Справочник», содержащий список праздничных дней.

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

  2. Присвоить имя «Справ» диапазону ячеек справочника. Для этого выделить блок ячеек справочника и выполнить команду ВСТАВКА/Имя/Присвоить.

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

  4. Рассчитать сумму «Удержано всего» по каждому работнику.

  5. Рассчитать сумму «К выдаче» как разность между начисленной суммой и суммой «удержано всего». Предусмотреть невозможность выдачи отрицательных сумм.

  6. Подсчитать итоговые суммы по всем работникам в целом и в т.ч. по каждому отделу. Для этого использовать функции СУММ и СУММЕСЛИ.

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

Цель работы: освоение следующих технологических приемов
работы:

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

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

Технология формирования сводной таблицы

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

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

Задание 1. Использовать форму данных для просмотра и корректировки записей таблицы «Справочник работников». Форма — это наиболее удобный способ для просмотра, изменения, добавления и удаления записей списка, а также для поиска записей, удовлетворяющих заданным условиям. Перед работой с формой необходимо задать заголовки столбцов списка. Эти заголовки используются для создания полей формы.

Технология:

2. Использование автофильтра. В Microsoft Excel предусмотрены различные методы анализа данных в списке. К списку можно применить фильтр, чтобы отобрать только записи, соответствующие определенным условиям. Для этого служит команда ДАННЫЕ/Автофильтр.

Задание 2. Использовать автофильтр для вывода в таблице «Справочник работников» информации только о работниках, оклад которых меньше заданного значения.

Технология:

Задание 3. Использовать автофильтр для вывода в таблице «Справочник работников» информации о работниках:

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

Задание 4. Использовать расширенный фильтр для получения данных о работниках 1-го отдела с окладом меньшим 5000000 руб. и не являющих­ся членами профсоюза.

Технология:

Задание 5. Использовать расширенный фильтр для получения данных о работниках 1-го и 3-его отделов, с окладом меньшим 500000 руб. и не являющихся членами профсоюза.

Подбор параметра

Подбор параметра является способом прогнозирования значений с помощью анализа «что-если». При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение.

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

Технология решения:

1. На Лист 1 новой книги ввести данные калькуляции цены книги, приведенные в таблице 1. Константами должны быть: количество экземпляров, % накладных расходов, затраты на зарплату, затраты на рекламу, цена продукции и себестоимость продукции (в таблице эти значения показаны на сером фоне). Остальные данные должны быть вычислены на основании расчетных формул. Введите формулы и сверьте результаты расчета по ним с данными, приведенными в таблице.

Таблица 1


Количество экземпляров

20 000

Доход

120 000 000 р.

Себестоимость реализованной продукции

40 000 000 р.

Валовая прибыль

80 000 000 р.

% накладных расходов

30

Затраты на зарплату

5 000 000 р.

Затраты на рекламу

1 000 000 р.

Накладные расходы

36 000 000 р.

Валовые издержки

42 000 000 р.

Прибыль от продукции

38 000 000 р.

Цена продукции

6 000 р.

Себестоимость продукции

2 000 р.

2. Переименовать Лист 1 в Калькуляция и скопировать отлаженную таблицу с формулами в Лист 2. Исследуйте информацию, представленную на таблице листа Калькуляция. Увеличение прибыли может быть достигнуто за счет изменения количества экземпляров, себестоимости, валовых издержек и уровня накладных расходов.

3. Подобрать такую цену книги, чтобы прибыль от продукции составила 50 000 000 руб. Для этого:

4. Ознакомиться с результатами выполнения операции подбора параметра в окне «Состояние подбора параметра» и щелкнуть кнопку «Ok» для изменения значений ячеек таблицы в соответствии с найденным решением.

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

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

Справка для составления расчетных формул:

Доход = Цена продукции * Количество экземпляров.

Себестоимость реализованной продукции = Себестоимость продукции* Количество экземпляров.

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

Накладные расходы = Доход * % накладных расходов.

Валовые издержки = Накладные расходы + Затраты на зарплату + Затраты на рекламу.

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





A

B

4

Количество экземпляров

20000

5

Доход

=B14*B4

6

Себестоимость реализованной продукции

=B15*B4

7

Валовая прибыль

=B5-B6

8

% накладных расходов

30

9

Затраты на зарплату

5000000

10

Затраты на рекламу

1000000

11

Накладные расходы

=B5*B8%

12

Валовые издержки

=B11+B9+B10

13

Прибыль от продукции

=(B14-B15)*B4-B12

14

Цена продукции

6000

15

Себестоимость продукции

2000


Построение сценариев

Сценарием в Microsoft Excel называется набор значений подстановки, используемый для прогнозирования поведения модели. Используя сценарии, можно одновременно манипулировать 32 переменными. Каждый сценарий отражает свой ряд предположений, используемый для получения конечного результата. Существует возможность создания и сохранения различных сценариев и переключения на любой из этих сценариев для просмотра результатов. Для сопоставления и сравнения между собой различных сценариев может быть создан итоговый отчет, который может иметь вид структуры или сводной таблицы.

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

Задание 1. Ввести таблицу с упрощенным бюджетом предприятия на 1997 год и выполнить прогнозирование бюджета на 1998, 1999 и 2000 годы, манипулируя темпами роста различных показателей. Подготовить 4 сценария с различными прогнозами роста и создать итоговый сравнительный отчет.

Бюджет предприятия на 1997 г. приведен в таблице:





А

B

C

D

E

1




1997 г.

1998 г.

1999 г.

2000 г.

2

Объем продаж

1000000










3

Размер прибыли в %

25%










4

Общая прибыль

250000










5
















6

Аренда

35000










7

Услуги

13000










8

Выплаты

115000










9

Расход

163000










10
















11

Чистая прибыль

87000











Прогнозируемый ежегодный рост отдельных показателей приведен в таблице:





А

В

13

Объем продаж

4%

14

Размер прибыли

2%

15

Аренда

5%

16

Услуги

3%

17

Выплаты

5%


Технология решения задачи:

  1. Присвоить имена ячейкам В13-В17 в соответствии с названиями показателей в столбце А. Для этого последовательно устанавливать курсор на каждую ячейку и выполнять команду ВСТАВКА/Имя/Присвоить, щелкая по кнопке «Ok» в окне «Присвоение имени».

  2. Присвоить имена ячейкам результата С11, D11, E11 – «Прибыль_1998», «Прибыль_1999», «Прибыль_2000».

  3. Ввести расчетные формулы для вычисления показателей в ячейках С2:Е11.

Справка:

Общая прибыль = Объем продаж * Размер прибыли в %.

Расход = Аренда + Услуги + Выплаты.

Чистая прибыль = Общая прибыль - Расход.

Показатели в столбцах C,D,E вычисляются по схеме:

Объем продаж 1998 г = Объем продаж 1997 г * (1 + % роста
объема продаж).


Размер прибыли 1998 г = Размер прибыли 1998 г. * (1 + %
роста размера прибыли).


и т. д.

  1. Определить первый сценарий, выполнив команду СЕРВИС/Сценарии:

  1. Щелкнув по кнопке «Добавить» создать аналогично
    «Сценарий 2», изменив непосредственно в окне значения процентов роста показателей в ячейках В13:В17, например, 3%,
    2%, 6%, 4%, 4%.

  2. Аналогично предыдущему пункту, изменяя значения отдельных показателей, создать еще «Сценарий 3» и «Сценарий 4».

  3. Щелкнув по кнопке «Отчет» в окне «Диспетчер сценариев», перейти к построению отчета.

Замечание: Ссылки должны разделятся символом «;» – «точка
с запятой
».

Щелкнуть по кнопке «Ok». На экране появится рабочий лист «Структура сценария» с таблицей примерно следующего вида:



  1. Создать «Сводную таблицу по сценарию». Для этого перейти на исходный рабочий лист и выполнить команду СЕРВИС/Сценарии:

Появится рабочий лист с таблицей примерно следующего вида:



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

Цель работы: освоение следующих технологических приемов
работы:

Ознакомление с примером поиска оптимального решения корпорации Microsoft:

  1. Загрузить Microsoft Excel.

  2. Открыть табличный файл Solvsamp.xls, находящийся в папке C:\Program Files\Microsoft Office\Office\Examples\Solver.

  3. Установить рабочий лист «Краткий обзор». Освоить технологию решения на модели, связывающей затраты на рекламу с прибылью:

Выполнить поиск оптимальных решений для экономико-математических моделей, расположенных на других листах рабочей книги:

Поиск решений

Задача 1

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





A

B

C

D

1













2




Количество

Прибыль
на 1 шт.


Доход

3

Изделие А

100

13

1300

4

Изделие В

100

18

1800

5

Изделие С

100

22

2200

6

Всего

300




5300


При решении данной задачи должны быть учтены следующие ограничения:

Технология:

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

  2. Запустить задачу поиска решений. Для этого: выполнить команду Сервис/ Поиск решений … и в диалоге «Поиск решений» ввести данные:

Задача 2

С помощью средства «Поиск решения» решить задачу минимизации расходов на перевозку грузов.

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

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

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




A

B

C

D

E

F

1

Таблица стоимости перевозок

2










Склад 1

Склад 2

Склад 3

3







Магазин 1

58

47

108

4







Магазин 2

87

46

100

5







Магазин 3

121

30

57

6







Магазин 4

149

66

83

7







Магазин 5

62

115

164

8







Магазин 6

128

28

38







A

B

C

D

E

F

G

9







10







Потребность

в товаре

Количество перевезенного товара со склада

11







Склад 1

Склад 2

Склад 3

Всего

12




Магазин 1

150

25

25

25

75

13




Магазин 2

225

25

25

25

75

14




Магазин 3

100

25

25

25

75

15




Магазин 4

250

25

25

25

75

16




Магазин 5

120

25

25

25

75

17




Магазин 6

150

25

25

25

75

18




Всего

995

150

150

150

450







A

B

C

D

E

F

19
















20




Запасы на складе

400

350

500

21




Запасы после отпуска

=D20-D18

...

...







A

B

C

D

E

F

G

24




Стоимость перевозок

=СУММПРОИЗВ (D3:D8;D12:D17)








В ячейке G24 подводится общая стоимость перевозок для всех заказов.

В результате поиска оптимального решения должны быть найдены такие значения ячеек D12:F17, при которых каждый розничный магазин будет получать желаемое количество товара и общая стоимость перевозок будет минимальна. Нужно минимизировать значение, находящееся в ячейке G24, изменяя значение диапазона ячеек D12:D17 с учетом следующих ограничений:

Задача 3

Для изготовления пластмассовых втулок и шестеренок требуется стеклоткань, эпоксидная смола и отвердитель. На изготовление одной втулки затрачивается 4 ед. стеклоткани, 3 ед. – эпоксидной смолы и 2 ед. – отвердителя, а на изготовление одной шестеренки – соответственно 3, 4 и 6 ед. материалов. Прибыль предприятия от изготовления одной втулки составляет 20 руб., а шестеренки – 40 руб. Сколько втулок и шестеренок должно изготовить предприятие для получения наибольшей прибыли, если в его распоряжении имеется 480 ед. стеклоткани, 444 ед. эпоксидной смолы и 546 ед. отвердителя.

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





Втулки

Шестеренки

Расход

Имеется

Стеклоткань

4

3

603

480

Эпоксидка

3

4

1100

444

Отвердитель

2

6

1400

546
















Прибыль от 1 шт.

20

40






















Выпуск

100

200






















Общая прибыль

10000











Задача 4

Прутки длиной 8 метров разрезаются на заготовки длиной 3
и 2.4 м, Заготовок первого типа нужно получить не менее 25 штук, а второго – не менее 36 штук. Определить минимальное число разрезаемых прутков. Допускаются лишь способы разрезки, при которых длина остатка меньше любой заготовки.

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





Длина прутка

8










Заготовка 1

3










Заготовка 2

2.4

























Заготовка 1

Заготовка 2

Кол-во прутков




Способ 1

2

0

8




Способ 2

1

2

11




Способ 3

0

3

8




Всего прутков







27




Кол-во заготовок 1




27

25

Кол-во заготовок 2




38

36


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

Работа с макросами

Макрос – это программа, представляющая собой последовательность макрокоманд и макрофункций. Макросы предназначены для автоматизации различных процедур работы с таблицей, в частности, автоматизации часто повторяющихся последовательностей рабочих шагов. В Excel 97 макросы составляются на языке Visual Basic для приложений (Visual Basic for application) – едином для всех компонентов Office 97.

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

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

Задание 1

Записать макрос, который будет заносить в ячейки А1:С4 последовательность натуральных чисел, начиная с 1, по часовой стрелке. Присвоить этому макросу имя «Макрос 1». Создать новое меню «Макросы» и в нем пункт с именем «Макрос 1». Создать новую панель инструментов и поместить на нее кнопку для запуска созданного макроса.

Технология:

  1. Установите на чистом рабочем листе курсор в ячейку Е10.

  2. Для запуска макрорекордера выполните команду СЕРВИС/Макрос/Начать запись.

  3. В диалоговом окне «Запись макроса» введите имя «Макрос 1» и щелкните по кнопке «Ok». На экране появится кнопка для остановки записи макроса. Учтите, что с этого момента все Ваши действия должны быть четкими, т. к. они записываются
    макрорекордером.

  4. Последовательно введите в ячейку А1 число 1, в ячейку В1 число 2, в ячейку С1 число 3, в ячейку С2 число 4, в ячейку С3 число 5, в ячейку С4 число 6, в ячейку В4 число 7, в ячейку А4 число 8, в ячейку А3 число 9, в ячейку А2 число 10.

  5. Остановите запись макроса, щелкнув по кнопке «Остановить
    запись
    ».

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

  7. Запустите только что созданный макрос. Для этого выполните команду СЕРВИС/Макрос. Щелкните по имени макроса в окне «Макрос», а затем по кнопке «Выполнить». Действия, ранее выполненные Вами вручную, будут повторены макросом
    автоматически.

  8. Создайте меню с именем «Макросы». Для этого:

  1. Создайте пункт с именем «Макрос 1» в меню «Макросы». Для
    этого:

  1. Проверьте работу макроса 1 при его запуске через меню «Макросы». Проверьте работу макроса при запуске на другом
    рабочем листе.

  2. Просмотреть текст макроса на языке Visual Basic, выполнив команду СЕРВИС/Макрос/Макросы и щелкнув по кнопке «Войти». Вернуться в таблицу, выполнив команду редактора макросов ФАЙЛ/Закрыть и вернуться в Microsoft Excel.

  3. Создать новую панель инструментов и поместить на нее кнопку для запуска Макроса 1. Для этого:

Задание 2

Составить макрос для формирования строки с итогами по
таблице.

Технология:

  1. На новом рабочем листе Составить произвольную таблицу чисел, располагающуюся в ячейках А1: D5. Установить курсор вне таблицы, выделив ячейку вне таблицы.

  2. Включить запись макроса.

  3. Нажать комбинацию клавиш Ctrl+Home для установки курсора в ячейку А1.

  4. Нажать комбинацию клавиш Ctrl+ для установки курсора
    в ячейку А5. Нажать клавишу  для установки курсора в
    ячейку А6.

  5. Щелкнуть по кнопке «Автосуммирование». Нажать клавишу «Enter» для записи функции суммирования в ячейку А6.

  6. Скопировать мышью формулу из ячейки A6 в ячейки B6:D6.

  7. Залить итоговую строку цветом. Провести линию верхней границы для итоговой строки.

  8. Выделить ячейку вне таблиц и остановить запись.

  9. Проверить работу макроса.

  10. Создать для нового макроса дополнительный пункт в меню «Макросы».

  11. Просмотреть текст макроса в окне редакторов макросов.

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

Цель работы: освоение технологии построения диаграмм и
графиков.

  1. Основные понятия:

1. Диаграммы могут иметь два варианта размещения:

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

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

2. Типы диаграмм. Excel позволяет создавать диаграммы 14 стандартных типов: гистограмма, линейчатая, график, круговая, точечная, с областями, кольцевая, лепестковая, поверхность, пузырьковая, биржевая, коническая, цилиндрическая, пирамидальная. Каждый тип может иметь несколько вариантов (подтипов). Примеры типов диаграмм можно посмотреть, вызвав справку Excel по теме Примеры/Диаграмма.

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

4. Категория данных – понятие, взаимосвязанное с рядом данных. Если в качестве рядов данных выбраны столбцы таблицы, то категориями будут называться строки и наоборот. Обычно названия категорий располагаются вдоль оси Х.

5. Параметры диаграммы:

6. Заголовки содержат названия различных элементов диаграммы:

7. Легенда - подпись, определяющая закраску или цвета рядов данных диаграммы. Легенда содержит ключи и соответствующие им названия рядов данных. Ключ легенды определяет цвет и узор , заданный для элементов определенного ряда.

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

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

10. Область диаграммы – это вся диаграмма, вместе со всеми ее элементами.

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

12. Диаграммы строятся с помощью мастера диаграмм, который вызывается щелчком по кнопке инструментальной панели с соответствующим названием. Перед тем как строить диаграмму целесообразно выделить ячейки исходной таблицы, включая некоторые заголовки. Для построения диаграммы по несмежным областям эти области выделяются мышью при нажатой клавише «Ctrl». Процесс построения содержит 4 шага:

  1. Построение гистограммы. Ввести на один из листов рабочей
    книги следующую таблицу:

Проект отраслевого бюджета социальной сферы региона
на 1998 – 2000 гг. в млрд. руб.





Проектные годы

Отрасли

1998 г.

1999 г.

2000 г.

Культура

100

120

140

Образование

800

890

1020

Здравоохранение

1500

1600

1700

Туризм и спорт

80

90

100


Задание 1

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

Технология:

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

  2. Вызовите Мастера диаграмм, щелкнув по кнопке инструментальной панели.

  3. На шаге 1 выберите тип «гистограмма», подтип 1. Нажмите кнопку «Просмотр результата» и удерживая нажатой левую клавишу мыши, просмотрите вид диаграммы. Щелкнув по кнопке «Далее» перейдите ко 2-му шагу.

  4. На шаге 2 сохраните флажок «Ряды в столбах». Щелкнув по кнопке «Далее» перейдите к 3-му шагу.

  5. На шаге 3 установите вкладку «Заголовки» и введите:

Установите вкладку «Таблица данных» и включите флажок с тем же названием. Щелкнув по кнопке «Далее» перейдите к 4-му шагу.

  1. На шаге 3 включите флажок, позволяющий поместить диаграмму на отдельном листе рабочей книги. Щелкните по кнопке «Готово» и просмотрите диаграмму на листе «Диаграмма 1». Обратите внимание на изменение панелей меню и инструментов.

Задание 2

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

  1. Изменение формата элементов диаграммы.

  2. Изменение области данных.

  3. Изменение размещения диаграммы.

  4. Изменение типа диаграммы.

Технология:

A) Изменение формата элементов диаграммы:

  1. Увеличить размер шрифта для заголовка диаграммы, названий легенды, осей и меток осей, таблицы данных. Для этого:

  1. Отредактировать тексты заголовка и названия оси категорий.
    Для этого:

  1. Расположить текст заголовка диаграммы в 2-х строках.

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

  3. Ввести рамку контура диаграммы и выполнить заливку области диаграммы. Для этого:

  1. Уменьшить размер области построения диаграммы. Для этого выделите область построения, установите указатель мыши на угловой маркер области и переместите его внутрь области.

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

  3. Добавить в область диаграммы произвольный текст, например, под заголовком диаграммы ввести текст: «По сведениям регионального статистического управления». Для этого:

  1. Изменение области данных:

  1. Изменить высоту столбца, соответствующего ряду значений «2000 г.». Для этого:

  1. Исключить из диаграммы ряд, соответствующий 1999 г. Для
    этого:

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

C) Изменение размещения диаграммы:

  1. Переместите диаграмму на рабочий лист 2. Для этого:

  1. Изменение типа диаграммы:

  1. Преобразуйте гистограмму в объемную цилиндрическую. Для
    этого:

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

Задание 3

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



Примечания:

Задание 4

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



Задание 5

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



Задание 6

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


Технология построения:

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

  2. На шаге 2 выбрать ряды в столбцах. Указать диапазон ячеек для ряда 1 – ячейки с датами начала работ. Установить вкладку «Ряд» и добавить ряд 2, содержащий данные о продолжительности работ. Указать диапазон ячеек для подписей оси Х – ячейки с наименованиями работ.

  3. На шаге 3 ввести название диаграммы.

  4. На шаге 4 внедрить диаграмму на текущий лист.

  5. Изменить формат оси категорий (ось Х): установить на вкладке «шкала» обратный порядок категорий и пересечение с осью Х в максимальной категории.

  6. Выделить ряд 1 и в окне «Формат ряда данных» сделать границу ряда невидимой, а заливку прозрачной.

  7. Изменить минимальное значение шкалы по оси значений, введя дату 01.02.98.

  8. Отредактировать размеры шрифтов отдельных элементов и размеры области диаграммы.

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

  1   2


МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ
Учебный материал
© nashaucheba.ru
При копировании укажите ссылку.
обратиться к администрации