Главная | Обратная связь | Поможем написать вашу работу!

Тема: Обработка финансово-экономической информации средствами табличного процессора MS Excel

ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

«ОРЕНБУРГСКИЙ ГОСУДАРСТВЕННЫЙ АГРАРНЫЙ УНИВЕРСИТЕТ»

Кафедра «Организации технологических процессов»

МЕТОДИЧЕСКИЕ УКАЗАНИЯ ПО ВЫПОЛНЕНИЮ

ЛАБОРАТОРНЫХ РАБОТ ПО ДИСЦИПЛИНЕ

Б2.В.ДВ.1.1 Информационные технологии и системы в экономике

Направление подготовки 080100.62 "Экономика"______________________

Профиль подготовки "Бухгалтерский учет, анализ и аудит" _ _____________

Квалификация (степень) выпускника бакалавр_____________________

Нормативный срок обучения 3г 6м_________________________________

Форма обучения заочная (сокр.срок)___________________________________

Оренбург 2014 г.

Методические указания по выполнению лабораторных работ

Лабораторная работа – небольшой научный отчет, обобщающий проведенную студентом работу, которую представляют для защиты преподавателю. К лабораторным работам предъявляется ряд требований, основным из которых является полное, исчерпывающее описание всей проделанной работы, позволяющее судить о полученных результатах, степени выполнения заданий и профессиональной подготовке студентов.

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

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

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

В отчет должны быть включены следующие пункты:

- титульный лист;

- цель работы;

- общая постановка задачи;

- результаты выполнения работы;

- ответы на контрольные вопросы.

Требования к содержанию отдельных частей отчета по лабораторной работе

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

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

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

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

В нижнем поле листа указывается место выполнения работы и год ее написания (без слова год).

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

Цель работы должна отражать конкретные задачи, поставленные студенту на период выполнения работы. По объему цель работы в зависимости от сложности и многозадачности работы составляет от нескольких строк до 0,5 страницы (например: получение практических навыков создания диаграмм в электронных таблицах Microsoft Excel).

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

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

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

Требования к оформлению отчетов:

Отчет должен быть выполнен в текстовом процессоре MS Word.

При оформлении отчета необходимо:

а) использовать следующие параметры страницы:

♦ шрифт Times New Roman, начертание обычное, размер шрифта 12 пт.;

♦ поля - верхнее и нижнее по 2 см., левое и правое по 1,5 см.;

♦ абзац - выравнивание по ширине, отступ первой строки 1,25 см.;

♦ одинарный межстрочный интервал.

Решения задач должны быть представлены в табличном процессоре MS Excel.

Электронный вариант отчета и результаты решения задач представляются в виде отдельных файлов по электронной почте до 01 июня 2015 г. на адрес: [email protected]. Имя файла должно содержать фамилию автора и номер лабораторной работы, например: Иванов_отчетЛР1 (документ Word)и Иванов_задачаЛР1 (документ Excel)т.д.

Приложение 1

Образец оформления отчета по лабораторной работе

«ОРЕНБУРГСКИЙ ГОСУДАРСТВЕННЫЙ АГРАРНЫЙ УНИВЕРСИТЕТ»

КАФЕДРА ОРГАНИЗАЦИИ ТЕХНОЛОГИЧЕСКИХ ПРОЦЕССОВ

Отчет по лабораторной работе № _________

по дисциплине _______________________________________________

Тема работы __________________________________________________

Выполнил: студент(ка)

______________________________

Проверил: _____________________

Оренбург, 20___

Цель работы

Общая постановка задачи

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

Ответы на контрольные вопросы

____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

Лабораторная работа 1: Основы компьютерного делопроизводства в среде текстового процессора MS Word

Цель работы: Освоить инструменты и технологические операции для создания финансового документа в среде Word и Paint.

Теоретическая часть

Основным носителем информации является документ – материальный носитель, содержащий информацию в зафиксированном виде, оформленный в установленном порядке и имеющий в соответствии с законодательством правовое значение. Документ является одним из предметов проектирования при разработке АИТ и ЭИС. От качества его проектирования зависит сокращение объемов работ при заполнении документов и вводе данных в компьютер.

Условно финансовый документ (ФД) имеет следующие основные зоны (рисунок 1):

Рис. 1 – Разметка финансового документа по зонам.

Зона 1 содержит графический образ, отождествляющий деятельность организации или фирмы, для которой создается документ, ее название и реквизиты; 2 – номер формы, гриф использования документа, например, «форма № 2/6, для служебного пользования», 3 – перечень реквизитов, общих для ряда данных документа, например: «цех №1»; 4 – наименование ФД и дата заполнения; 5 – содержательная или основная часть; 6 – зона для подписей ответственных лиц и для печати.

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

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

- наличие и соответствующее размещение зон, указанных на рисунке 1;

- соответствие стандартному формату, например, A4;

- рациональное заполнение площади формата;

- соблюдение полей, толщины линий и размеров шрифтов;

- оригинальность, аккуратность выполнения и удобство в применении;

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

В качестве инструментальной базы для работы предлагается использование панелей инструментов приложений Word и Paint. Конечный результат должен быть оформлен как документ в среде Word.

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

Используя возможности приложений Word и Pаint создать финансовый документ согласно заданному варианту. Эскиз возможного варианта ФД представлен на рисунке 2. При создании документа использовать следующие шрифты: зона 1: реквизиты - размер шрифта 10-12, название – размер шрифта 12-14, жирный; зона 2: размер шрифта 14; зона 3: размер шрифта 14, курсив; зона 4: размер шрифта 16, жирный; зона 5: размер шрифта 14; зона 6: размер шрифта 14.

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

- создание графической части ФД в зоне 1;

- создание текстовой части всех зон, кроме таблицы;

- создание табличной части ФД в зоне 5.

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

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

Текстовую часть ФД желательно выполнять с использованием режима «Надпись», это позволит автономное форматирование, размещение и редактирование текста по зонам.

Табличную часть документа необходимо выполнить в режиме «Таблица». В таблице обязательно предусмотреть расчет итоговых показателей (Таблица/Формула).

Все виды работы провести с учетом требований к ФД.

Порядок выполнения работы

Установить режим создания нового документа командой: «Файл - Создать». Установить вид шрифта «Times New Roman».

Создание графического образа (ГО) документа. В библиотеке картинок в режиме «Вставка - Рисунок – Картинки» подобрать подходящий к теме ФД рисунок и скопировать его на лист ФД. Для корректировки и добавления новых элементов в рисунок следует перенести картинку в окно редактора Paint в режиме: выделить картинку левой кнопкой мыши, правой кнопкой вызвать контекстное меню, скопировать картинку, открыть окно Paint, выбрать пункты горизонтального меню «правка – вставить».

Используя инструменты программы Paint ввести необходимые изменения. Например, дорисовать «мышь» в графическом образе на рисунке 2. Завершенный графический образ перенести на документ Word используя режим выделения ГО, копирования, открытия окна Word и вставки.

Оформление текстовой части ФД. С учетом методических указаний п. 2, а также требований к оформлению ФД оформить текстовую часть документа. Удаление линий рамки при работе в режиме «Надпись» произвести в режиме: выделить левой кнопкой мыши текст надписи, с помощью правой кнопки выбрать режим «формат надписи – линии – цвет - нет линий». В качестве примера использовать текстовое оформление рисунка 2.

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

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

1. Составить ФД для следующих предприятий и организаций:

Вариант 1. Столовая;

Вариант 2. Автосервис;

Вариант 3. Мебельный салон;

Вариант 4. Туристическое агентство;

Вариант 5. Интернет-кафе;

Вариант 6. Компьютерный центр;

Вариант 7. Библиотека;

Вариант 8. Центральный рынок;

Вариант 9. Мясокомбинат;

Вариант 10. Торговый центр.

2. Оформить отчет

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

1. Основные требования к ФД.

2. Основные инструменты для создания ФД.

3. Назначения зон ФД.

4. Характеристика режима обмена результатами в приложениях Word и Paint с использованием буферной памяти.

5. Режим вычисления контрольной суммы в среде Word.

6. Режимы группировки элементов графических образов.

7. Режим автоматической организации переносов слов при наборе текстовой части ФД.

8. Режим работы “Надпись”.

9. Режим написания и корректировки математических формул.

10. Режим масштабирования графического образа в среде Paint.

Ведомость

по заработной плате за январь 2004 г. от 2.02.2004 г.

Директор: П.С. Иванов

Бухгалтер: Т.И. Николаева

Рис.2 – Пример подготовки финансового документа

Тема: Обработка финансово-экономической информации средствами табличного процессора MS Excel

Лабораторная работа 2: Организация расчетов в табличном процессоре MS Excel

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

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

Исходные данные представлены на рис. 2.1.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу (при стандартной установке MS Office выполните Пуск/Программы/Microsoft Excel).

Рис. 2.1 - Исходные данные для Задания 2.1

2.Введите заголовок таблицы «Финансовая сводка за неделю (тыс.руб.)», начиная с ячейки А1.

3. Для оформления шапки таблицы выделите ячейки на третьей строке A3:D3 и создайте стиль для оформления. Для этого выполните команду Формат/Стиль и в открывшемся окне Стиль (рис. 2.2) наберите имя стиля «Шапка таблиц» и нажмите кнопку Изменить. В открывшемся окне на вкладке Выравнивание задайте Переносить по словам и выберите горизонтальное и вертикальное выравнивание — по центру (рис. 2.3), на вкладке Число укажите формат — Текстовый. После этого нажмите кнопки ОК/Добавить/ОК.

4.На третьей строке введите названия колонок таблицы — «Дни недели», «Доход», «Расход», «Финансовый результат», далее заполните таблицу исходными данными согласно заданию 2.1.

Краткая справка. Для ввода дней недели наберите «Понедельник» и произведите автокопирование до «Воскресенья» (левой кнопкой мыши за маркер автозаполнения в правом нижнем углу ячейки, при этом указатель мыши превращается в маркер заполнения (черный крестик +)).

Установите ширину столбцов таблицы в соответствии с рис. 2.1. Для этого:

· подведите указатель мыши к правой черте клетки с именем столбца (номером строки), например В, так, чтобы указатель мыши изменил свое изображение на «;

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

Рис. 2.2 – Создание стиля оформления шапки таблицы

Рис. 2.3 – Форматирование ячеек – задание переноса по словам

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

5. Произведите расчеты в графе «Финансовый результат» по следующей формуле:

Финансовый результат = Доход - Расход,

для этого в ячейке D4 наберите формулу: = В4-С4.

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

6. Для ячеек с результатом расчетов задайте формат «Денежный» с выделением отрицательных чисел красным цветом (рис. 2.4) (Формат/Ячейки/ вкладка Число/ формат - Денежный/ отрицательные числа - красные. Число десятичных знаков задайте равное 2).

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

7. Рассчитайте средние значения дохода и расхода, пользуясь мастером функций (кнопка f_x). Функция «Среднее значение» (СРЗНАЧ) находится в разделе «Статистические». Для расчета функции СРЗНАЧ дохода установите курсор в соответствующей ячейке для расчета среднего значения (В11), запустите мастер функций (Вставка/Функция/ Категория - Статистические/ Функция - СРЗНАЧ) (рис. 2.5). В качестве первого числа выделите группу ячеек с данными для расчета среднего значения - В4:В10.

Аналогично рассчитайте «Среднее значение» расхода.

Рис. 2.4. – Задание формата отрицательных чисел красным цветом

Рис. 2.5 – Выбор функции расчета среднего значения

8. В ячейке D13 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат»). Для выполнения автосуммы удобно пользоваться кнопкой Автосуммирования (S) на панели инструментов или функцией СУММ (рис. 2.6). В качестве первого числа выделите группу ячеек с данными для расчета суммы — D4:D10.

Рис. 2.6 – Задание интервала ячеек при суммировании функцией СУММ

Рис. 2.7 – Таблица расчета финансового результата

9. Проведите форматирование заголовка таблицы. Для этого выделите интервал ячеек от А1 до D1, объедините их кнопкой панели инструментов Объединить и поместить в центре или командой меню Формат/Ячейки/ вкладка Выравнивание/ отображение - Объединение ячеек). Задайте начертание шрифта - полужирное; цвет - по вашему усмотрению.

Конечный вид таблицы приведен на рис. 2.7.

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

Для этого выделите интервал ячеек с данными финансового результата D4:D10 и выберите команду Вставка/Диаграмма. На первом шаге работы с мастером диаграмм выберите тип диаграммы - линейчатая; на втором шаге на вкладке Ряд в окошке Подписи оси Х укажите интервал ячеек с днями недели - А4:А10 (рис. 2.8).

Рис. 2.8 – Задание подписи оси X при построении диаграммы

Рис. 2.9 – Конечный вид диаграммы

Далее введите название диаграммы и подписи осей; дальнейшие шаги построения диаграммы осуществляются автоматически по подсказкам мастера. Конечный вид диаграммы приведен на рис. 2.9.

11. Произведите фильтрацию значений дохода, превышающих 4000 руб.

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

Для установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командой Данные/Фильтр/Автофильтр. В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «Доход»), и вы увидите список всех неповторяющихся значений этого поля. Выберите команду для фильтрации - Условие.

В открывшемся окне Пользовательский автофильтр задайте условие «Больше 4000» (рис.2.10). Произойдет отбор данных по заданному условию.

Проследите, как изменились вид таблицы (рис. 2.11) и построенная диаграмма.

12. Сохраните созданную электронную книгу.

Рис. 2.10 – Пользовательский автофильтр

Рис. 2.11 – Вид таблицы после фильтрации данных

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

Вариант 1

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

Рис. 1 - Исходные данные для варианта 1

Формулы для расчета:

Сумма = Цена * Количество

Всего = сумма значений колонки «Сумма»

Краткая справка. Для выделения максимального/минимального значений выберите встроенную функцию Excel MAKC (МИН) из категории «Статистические».

Вариант 2

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

Формулы для расчета:

Сумма брака = Процент брака* Сумма зарплаты.

Краткая справка. В колонке «Процент брака» установите процентный формат чисел (Формат/Ячейки/ вкладка Число/ формат – Процентный). Для выделения максимального/минимального значений выберите встроенную функцию Excel MAKC (МИН) из категории «Статистические».

Рис. 2 - Исходные данные для варианта 2

Вариант 3

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

Формулы для расчета:

Всего= Безналичные платежи + Наличные платежи

Выручка от продажи = Цена * Всего

Рис. 3 - Исходные данные для варианта 3

Вариант 4

Заполнить таблицу 4, рассчитать прибыль от реализации продукции, рентабельность и строку ИТОГО; произвести фильтрацию, отобрав только те культуры, для которых рентабельность превышает 50%, построить гистограмму отфильтрованных значений изменения прибыли по видам продукции (рис. 4).

Формулы для расчета:

Прибыль от реализации = Выручка от реализации - Себестоимость реализованной продукции

Рентабельность = Прибыль от реализации / Выручка от реализации

Вид продукции	Выручка от реализации, тыс.руб.	Себестоимость реализованной продукции, тыс.руб.	Прибыль от реализации, тыс.руб.	Рентабельность, %
Горох			?	?
Овес			?	?
Просо			?	?
Пшеница			?	?
Ячмень			?	?
ИТОГО	?	?	?	?

Рис. 4 - Исходные данные для варианта 4

Вариант 5

Заполнить таблицу, рассчитать потребность в горючем, стоимость горючего и строку «Итого»; произвести фильтрацию по объему работ, превышающему 8000 га, построить график отфильтрованных значений, отражающий объем работ и стоимость горючего (рис. 5).

Формулы для расчета:

Горючего всего = Объем работ * Расход горючего на единицу/100

Стоимость горючего = Расход горючего * Стоимость 1ц горючего

Стоимость 1ц горючего-200 руб.

.№ п/п	Наименование работ	Объем работ, га	Расход горючего на единицу, кг	Горючего всего, ц	Стоимость горючего, руб.
	Вспашка Снегозадержание Закрытие влаги Посев Культивация		15,4 0,85 1,7 2,1 2,04	? ? ? ? ?	? ? ? ? ?
	Итого	?	?	?	?

Рис. 5 - Исходные данные для варианта 5

Вариант 6

Заполнить таблицу, рассчитать сумму амортизационных отчислений и строку «Итого»; произвести фильтрацию по норме амортизации, превышающей 9%, построить график отфильтрованных значений, отражающий сумму амортизации (рис. 6).

Формулы для расчета:

Сумма амортизации = Баланс стоимости * Норма амортизации

Марка машины	Баланс стоимости, тыс. руб.	Норма амортизации, %	Сумма амортизации, руб.
ГАЗ-53			?
ЗИЛ-130			?
К-700А		8,5	?
ДТ-75			?
Т-4			?
ИТОГО:	?	?	?

Рис. 6 - Исходные данные для варианта 6

Вариант 7

Заполнить таблицу, рассчитать товарооборот и строку «Итого»; произвести фильтрацию, отобрав период с 1994 по 1996 гг., построить гистограмму отфильтрованных значений изменения товарооборота по годам (рис. 7).

Формулы для расчета:

Товарооборот = Объем реализации * Цена за 1т

Годы	Объем реализации, т	Цена за 1т, тыс. руб.	Товарооборот, млн. руб.
		95,87	?
		243,86	?
		629,49	?
		552,5	?
			?
Итого:	?	?	?

Рис. 7 - Исходные данные для варианта 7

Вариант 8

Заполнить таблицу, рассчитать “Объем реализации” за 1 тонну и итоговую строку; произвести фильтрацию, отобрав период с 1994 по 1997гг., построить диаграмму отфильтрованных значений, отражающую объем реализации (рис. 8).

Формулы для расчета:

Объем реализации = Товарооборот / Цена за 1т

Годы	Объем реализации, т.	Цена за 1т.,тыс. руб.	Товарооборот, млн.руб.
	? ? ? ? ? ?	89,45 95,90 253,86 630,50 553,48 487,00	75755383,90 80063074,00 240587691,48 645370973,00 424911023,84 436702640,00
ИТОГО:	?	?	?

Рис. 8 - Исходные данные для варианта 8

Вариант 9

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

Формулы для расчета:

Произведено продукции 1 работником = Стоимость валовой продукции / Количество работников

Годы	Стоимость валовой продукции, тыс.руб.	Количество работников, чел.	Произведено продукции 1 работником, тыс.руб.
			?
			?
			?
			?
			?
			?

Рис. 9 - Исходные данные для варианта 9

Вариант 10

Заполнить таблицу, рассчитать столбцы ”Грузооборот, т/км ”, “Стоимость услуг автотранспорта, руб” и строку “Итого”; произвести фильтрацию по расстоянию перевозок, превышающей 15 км, построить график отфильтрованных значений, отражающий грузообъем и грузооборот (рис. 10).

Формулы для расчета:

Грузооборот = Грузобъём * Расстояние перевозки

Стоимость услуг автотранспорта = Грузобъём * Стоимость 1 ткм

Стоимость 1 ткм – 0,5 р.

Наименование работ	Грузообъем, т	Расстояние перевозки, км	Грузооборот, ткм	Стоимость услуг автотранспорта, руб.
Транспортировка семян			?	?
Транспортировка удобрений			?	?
Транспортировка ядохимикатов			?	?
Транспортировка зерна			?	?
Итого	?	?	?	?

Рис. 10 - Исходные данные для варианта 10

Лабораторная работа 3: Методы обработки и анализа экономической информации средствами табличного процессора MS Excel

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

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

Исходные данные представлены на рис. 3.1.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу. Сохраните созданную электронную книгу под именем «Зарплата».

2. Создайте таблицу расчета заработной платы по образцу (см. рис. 3.1). Введите исходные данные — Табельный номер, ФИО и Оклад, % Премии = 27%, %Удержания = 13 %.

Примечание. Выделите отдельные ячейки для значений % Премии (D4) и %Удержания (F4).

Рис. 3.1 - Исходные данные для задания 3.1

Произведите расчеты во всех столбцах таблицы.

При расчете Премии используется формула:

Премия =Оклад * % Премии.

В ячейке D5 наберите формулу = С5 * $D$4 (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением.

Рекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную окрашенная ячейка (т.е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символов $ с клавиатуры или нажатием клавиши [F4]).

Формула для расчета «Всего начислено»:

Всего начислено = Оклад + Премия.

При расчете удержания используется формула:

Удержание = Всего начислено * % Удержания,

для этого в ячейке F5 наберите формулу = $F$4 * Е5.

Формула для расчета столбца «К выдаче»:

К выдаче = Всего начислено - Удержания.

3. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Вставка/Функция/ категория— Статистические функции).

4. Переименуйте ярлычок листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши. Результаты работы представлены на рис 3.2.

Краткая справка. Каждая рабочая книга Excel может содержать до 255 рабочих листов. Это позволяет, используя несколько листов, создавать понятные и четко структурированные документы, вместо того, чтобы хранить большие последовательные наборы данных на одном листе.

5. Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/Переместить/Скопировать лист). Можно воспользоваться командой Переместить/Скопировать контекстного меню ярлычка. Не забудьте для копирования поставить галочку в окошке Создавать копию (рис. 3.3).

Краткая справка. Перемещать и копировать листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl]).

6. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение премии на 32%. Убедитесь, что программа произвела пересчет формул.

7. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» (Вставка/Столбец) и рассчитайте значение доплаты по формуле Доплата = Оклад * % Доплаты. Значение доплаты примите

равным 5 %.

Рис. 3.2 - Итоговый вид таблицы расчета заработной платы за октябрь

8. Измените формулу для расчета значений колонки «Всего начислено»:

Всего начислено = Оклад + Премия + Доплата.

Рис. 3.3 – Копирование листа электронной книги

Рис. 3.4 – Условное форматирование данных

9. Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 5000 и 7000 зеленым цветом шрифта; меньше 5000 - красным; больше или равно 7000 - синим цветом шрифта (Формат/Условное форматирование) (рис. 3.4).

10. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент таблицы А5:H18), выберите меню Данные/Сортировка, сортировать по — Столбец В (рис. 3.6).

11. Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Вставка/Примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания.

12. Защитите лист «Зарплата ноябрь» от изменений (Сервис/Защита/Защитить лист). Задайте пароль на лист (рис. 3.5), сделайте подтверждение пароля.

Рис. 3.5 – Защита листа электронной книги
Рис. 3.6 – Сортировка данных

Убедитесь, что лист защищен и невозможно удаление данных. Снимите защиту листа {Сервис/Защита/Снять защиту листа

Воспользуйтесь поиском по сайту: