Надстройки табличного редактора Excel

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

ВСЕРОССИЙСКИЙ ЗАОЧНЫЙ ФИНАНСОВО-ЭКОНОМИЧЕСКИЙ ИНСТИТУТ

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

КУРСОВАЯ РАБОТА

по дисциплине «Информатика»

на тему: «Надстройки MS Excel»

специальность Э и Т

группа 216

№ зачетной книжки

Руководитель:

Жуков Геннадий Александрович

Москва - 2007

Оглавление

Введение

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

1.1 Введение

1.2 Установка надстроек

1.3 Использование средства Мастер суммирования

1.4 Использование средства Мастер преобразования файлов

1.5 Использование средства Мастер подстановки

1.6 Автосохранение

1.7 Диспетчер отчетов

1.8 Заключение

2. Практическая часть

2.1 Общая характеристика задачи

2.2 Описание алгоритма решения задачи

2.2.1 Решение задачи с помощью процессора MS Excel

2.2.2 Решение задачи помощью процессора MS Access

Список использованной литературы

Введение

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

Работа по своей структуре разделена на две части: теоретическую и практическую.

В теоретической части курсовой работы будут рассмотрены виды надстроек программы Excel, а также даны характеристики каждой из них.

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

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

Процессор - Intel Celeron M Processor 420 (1,6 GHz, 533 MHz FSB, 1 MB L2 cache);

Объем оперативной - памяти 512 MB;

Объем жесткого диска - 60 GB;

Тип принтера - HP LaserJet 1320;

Программное обеспечение Windows XP Office;

MS Word;

MS Excel.

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

1.1 Введение

В наши дни информационные технологии нашли применение почти во всех сферах профессиональной деятельности. Это привело к тому, что умение эффективно использовать компьютер и работать с различными программами пакета MS Office (MS Word, MS Excel, MS Access и другие) для решения различных прикладных задач является необходимым атрибутом профессиональной деятельности любого специалиста и во многом определяет уровень его востребованности в обществе.

Программа Excel входит в пакет Microsoft Office для Windows и предназначена для подготовки и обработки данных при помощи электронных таблиц. MS Excel оснащен множеством возможностей, позволяющих нам решать различные экономические, математические, статистические и другие задачи. Помимо стандартных функций, входящих в программу MS Excel, существуют специальные средства - надстройки, расширяющие возможности данной программы. Остановимся на них подробней.

1.2 Установка надстроек

Надстройки - это программы, добавляющие в MS Excel дополнительные возможности и команды.

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

Если выполняется установка MS Office Обычная, автоматически устанавливаются следующие надстройки:

Пакет анализа - дополняет MS Excel финансовыми, статистическими и инженерными функциями.

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

Автосохранение - обеспечивает режим автоматического сохранения рабочих книг через заданный интервал времени.

Мастер суммирования - создает формулу для суммирования тех данных в диапазоне, которые удовлетворяют данному условию.

Мастер подстановки - создает формулу для поиска данных в диапазоне по другому значению в этом же диапазоне.

Мастер шаблонов

Дополнительно можно также установить еще две надстройки:

Мастер Web - страниц

Мастер Web - форм

Существуют и другие надстройки, которые не инсталлируются при установке Office Обычная. Чтобы получить к ним доступ, можно либо выполнить установку Office Выборочная, а затем выбрать соответствующие параметры, либо запустить программу установки Office и щелкнуть на кнопку Добавить/Удалить, чтобы добавить оставшиеся надстройки. Этими надстройками являются:

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

Диспетчер отчетов - возможность создавать несколько отчетов одновременно.

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

Программы для шаблонов.

1.3 Использование средства Мастер суммирования

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

Рис. 1. Список счетов на рабочем листе

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

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

Записать формулу, в которой вручную выбрать ячейки с суммой каждого счета, относящегося к продавцу, для которого необходимо вычислить итог. Например, чтобы увидеть итог по продажам для Петра, нужно вычислить формулу = F4+F13+F18.

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

Создать сложную функцию массива.

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

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

надстройка алгоритм задача ексель аксес

1.4 Использование средства Мастер преобразования файлов

Известно, что Excel может открывать файлы электронных таблиц, созданные во многих разных программ, таких как Lotus 1-2-3, Quattro Pro, Microsoft Works, а также другие типы файлов, содержащих подобные данные. После того как такой файл будет один раз открыт, его можно сохранить в собственном формате Excel - xls с помощью команды Файл - Сохранить как.

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

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

1.5 Использование средства Мастер подстановки

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

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

1.6 Автосохранение

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

Надстройка Автосохранение не запускается по умолчанию. Ее нужно активизировать с помощью команды Сервис - Параметры - Сохранение.

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

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

1.7 Диспетчер отчетов

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

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

1.8 Заключение

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

DeltaV позволяет выявлять особенности данных, событий и диагностики технологического процесса, а также воспользоваться хорошо известными возможностями Excel для генерации отчетов, подготовки диаграмм и формирования заданий. Она проста в работе для всего производственного персонала - инженеров-технологов, операторов, менеджеров. Поддерживая 32-битную OLE-интеграцию, надстройка позволяет связывать данные из системы DeltaV со всеми «настольными» приложениями, например, c MS Word. Может производиться сбор данных от нескольких серверов данных DeltaV. Данные могут использоваться в других процессах и системах управления, поддерживающих ОРС. Также можно использовать возможности надстройки для построения информативных диаграмм. Будь то рабочие вычисления, например, определение эффективности преобразования технологического процесса, либо аналитические, к примеру, вычисление баланса массы.

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

2. Практическая часть

2.1 Общая характеристика задачи

В бухгалтерии предприятия ООО «Гамма» производится расчет налоговых вычетов, предоставляемых сотрудникам, и формирование платежных ведомостей. Данные для выполнения расчета налоговых вычетов приведены на рис. 2.1. Стандартный налоговый вычет предоставляется каждому сотруднику в размере 400 руб. до тех пор, пока совокупный доход с начала года не превысит 50 000 руб., налоговый вычет на ребенка предоставляется в размере 600 руб. НДФЛ - налог на доходы физических лиц (13%) рассчитывается с начисленной суммы за минусом размера налогового вычета.

1. Используя ППП на ПК, необходимо построить таблицы по приведенным ниже данным.

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

3. Сформировать и заполнить форму счетной ведомости по заработной плате за текущий месяц (рис. 2.3).

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

ФИО сотрудника	Начислено за месяц, руб.	Совокупный доход с начала года, руб.
Васечкина М. М.	4 890,00	26 000,00
Иванова И.И.	6 800,00	35 000,00
Кузнецова С.С.	5 350,00	42 000,00
Петрова А. А.	7 500,00	54 000,00
Сидорова К. К.	8 200,00	64 000,00

Рис. 2.1 Данные для расчета налоговых вычетов



ФИО сотрудника	Стандартный налоговый вычет на физ. лицо, руб.	Кол-во детей, на которых предоставляется налоговый вычет	Размер налогового вычета за текущий месяц, руб.
Иванова И.И.	400,00	2,00
Петрова А.А.	400,00	1,00
Васечкина М.М.	400,00
Сидорова К.К.	400,00	3,00
Кузнецова С.С.	400,00	2,00

Рис. 2.2 Размер налоговых вычетов, предоставляемых сотрудникам в текущем месяце

ООО "Гамма"
	Расчетный период
	с	по
	__.__.20__	__.__.20__
Расчетная ведомость
Табельный номер	ФИО сотрудника	Начислено за месяц, руб.	Размер налогового вычета, руб.	НДФЛ,руб.	К выплате, руб.
0001	Иванова И.И.	6800,00
0002	Петрова А.А.	7500,00
0003	Васечкина М.М.	4890,00
0004	Сидорова К.К.	8200,00
0005	Кузнецова С.С.	5350,00
ИТОГО ПО ВЕДОМОСТИ
Гл. Бухгалтер_____________________________

Рис. 2.3 Расчетная ведомость

2.2 Описание алгоритма решения задачи

2.2.1 Решение задачи с помощью процессора MS Excel

Для решения данной экономической задачи была выбрана среда табличного процессора MS Excel.

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

MS Excel - программа, не требующая знаний программирования и проста в использовании в поисках результата нашей задачи.

Запустим табличный процессор MS Excel.

Создадим книгу с именем «Гамма».

Лист 1 переименуем в лист с названием Данные.

На рабочем листе Данные MS Excel создадим таблицу данные для расчета налоговых вычетов.

Заполним таблицу «Данные для расчета налоговых вычетов» исходными данными (рис. 2.4).

Рис. 2.4 Расположение таблицы «Данные для расчета налоговых вычетов» на рабочем листе Данные MS Excel

Лист 2 переименуем в лист с названием Налоговые вычеты.

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

Заполним таблицу «Размер налоговых вычетов в текущем месяце» исходными данными (рис. 2.5).



Рис. 2.5. Расположение таблицы Размер налоговых вычетов в текущем месяце на рабочем листе Налоговые вычеты MS Excel.

Лист 3 переименуем в лист с названием Расчетная ведомость.

На рабочем листе Расчетная ведомость MS Excel создадим таблицу, в которой будут содержаться данные сотрудников за месяц.

Заполним таблицу «Расчетная ведомость» исходными данными (рис. 2.6).

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

Заполним графу Размер налогового вычета таблицы «Размер налогового вычета в текущем месяце», находящейся на листе Налоговые вычеты следующим образом (рис. 2.7.):

Занести в ячейку Е2 формулу:

= Если(данные!С3+данные!D3>=50000;D2*600;С2+D2*600)

Занести в ячейку Е3 формулу:

= Если(данные!С5+данные!D5>=50000;D3*600;С3+D3*600)

Занести в ячейку Е4 формулу:

= Если(данные!С2+данные!D2>=50000;D4*600;С4+D4*600)

Занести в ячейку Е5 формулу:

= Если(данные!С6+данные!D6>=50000;D5*600;С5+D5*600)

Занести в ячейку Е6 формулу:

= Если(данные!С4+данные!D4>=50000;D6*600;С6+D6*600)

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Рис. 2.7 Размер налогового вычета в текущем месяце

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

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

Занести в ячейку D2 формулу:

= ВПР(размер!А2:А6;размер!А2:Е6;5)

Занести в ячейку D3 формулу:

= ВПР(размер!А3:А7;размер!А3:Е7;5)

Занести в ячейку D4 формулу:

= ВПР(размер!А4:А8;размер!А4:Е8;5)

Занести в ячейку D5 формулу:

= ВПР(размер!А5:А9;размер!А5:Е9;5)

Занести в ячейку D6 формулу:

= ВПР(размер!А6:А10;размер!А6:Е10;5)

Заполним графу НДФЛ таблицы Расчетная ведомость, находящейся на листе Ведомость, следующим образом:

Занести в ячейку Е2 формулу:

=(С2-D2)*13%.

Занести в ячейку Е3 формулу:

=(С3-D3)*13%.

Занести в ячейку Е4 формулу:

=(С4-D4)*13%.

Занести в ячейку Е5 формулу:

=(С5-D5)*13%.

Занести в ячейку Е6 формулу:

=(С6-D6)*13%.

Заполним графу К выплате таблицы Расчетная ведомость, находящейся на листе Ведомость, следующим образом (рис. 2.8.):

Занести в ячейку F2 формулу:

=С2-Е2.

Занести в ячейку F3 формулу:

=С3-Е3.

Занести в ячейку F4 формулу:

=С4-Е4.

Занести в ячейку F5 формулу:

=С5-Е5.

Занести в ячейку F6 формулу:

=С6-Е6.

Рис. 2.8 Расчетная ведомость

Приведем пример таблицы Размер налогового вычета в формульном и расчетном виде (рис. 2.9.).

Для этого нужно найти на панели инструментов MS Excel вкладку Сервис > Параметры > Вид > Формула

Рис. 2.9. Размер налогового вычета в формульном виде

Приведем пример таблицы Расчетная ведомость в формульном и расчетном виде (рис. 2.10.).

Рис. 2.10 Расчетная ведомость в формульном виде

Результаты вычислений представим графически (рис. 2.11).

Рис. 2.11 Графическое представление результата расчетов

2.2.2 Решение задачи с помощью процессора MS Access

1. Запустим MS Access.

2. Создаем новую базу данных в режиме конструктора. Введем в поле Имя поле ФИО сотрудника; в столбце Тип данных - текстовый; размер поля - 50

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

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

Назовем таблицу Данные (рис.2.2.1.)

Рис. 2.2.1 Таблица Данные в режиме конструктора.

3. Создаем следующую таблицу в режиме конструктора. Введем в поле Имя поля ФИО сотрудника; в столбце Тип данных - текстовый; размер поля - 50;

Введем имя поля Стандартный налоговый вычет на физ., лицо, руб.; в столбце Тип данных - денежный; размер поля - с разделителями разрядов; число десятичных знаков - авто.

Введем имя поля Кол-во детей, на которых предоставляется налоговый вычет; в столбце Тип данных - числовой; размер поля - длинное целое; число десятичных знаков - авто.

Введем имя поля Размер налогового вычета за текущий месяц, руб.; в столбце Тип данных - денежный; размер поля - с разделителями разрядов; число десятичных знаков - авто.

Назовем таблицу Размер налоговых вычетов (рис.2.2.2.)



Рис. 2.2.2 Таблица размер налоговых вычетов в режиме конструктора

4. Создаем следующую таблицу в режиме конструктора. Введем в поле Имя поля ФИО сотрудника; в столбце Тип данных - текстовый; размер поля - 50;

Введем имя поля Начислено за месяц; в столбце Тип данных - денежный; размер поля - с разделителями разрядов; число десятичных знаков - авто.

Введем имя поля Размер налогового вычета; в столбце Тип данных - денежный; размер поля - с разделителями разрядов; число десятичных знаков - авто.

Введем имя поля Стандартный НДФЛ; в столбце Тип данных - числовой; размер поля - длинное целое; число десятичных знаков - авто.

Введем имя поля Выплата; в столбце Тип данных - денежный; размер поля - с разделителями разрядов; число десятичных знаков - авто.

Назовем таблицу Расчетная ведомость (рис.2.2.3.)



Рис. 2.2.3 Таблица Расчетная ведомость в режиме конструктора.

5. Заполняем таблицы исходными данными

Рис. 2.2.4. Таблица Данные



Рис. 2.2.5 Таблица Размер налоговых расчетов

Рис. 2.2.6 Таблица Расчетная ведомость

6. Создаем запрос в виде конструктора (рис.2.2.7.)

НДФЛ: ([начислено за месяц, руб]-[размер нал вычета за тек месяц])*0,13.

К выплате: [начислено за месяц, руб]-[НДФЛ].



Рис.2.2.7 Таблица запроса в режиме конструктора

Рис. 2.2.8 Таблица-запрос

Список использованной литературы:

1. Информатика для экономистов: Учебник/Под ред. Агальцова В.П., Титова В.М. - М.: ИД «ФОРУМ»: Инфра - М, 2006.

2. Практикум по экономической информатике: Учеб. Пособие. Ч. 2/Под ред. В.П. Косарева, Г.А. Титоренко, Е.А. Мамонтовой. - М.: Финансы и статистика, 2002.

3. Экономическая информатика: Учебник / Под ред. В.П. Косарева и Л.В. Еремина. - М.: Финансы и статистика, 2001.

4. Информатика. Лабораторный практикум для студентов 2 курса всех специальностей. - М.: ВЗФЭИ, 2006.

Размещено на Allbest.ru