Файл: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78.doc

Оглавление



Введение
В настоящее время практически на всех направлениях подготовки (квалификация «бакалавр») введена дисциплина с обобщенным условным названием «Информационные технологии (по направлению подготовки)». Объем аудиторных часов, выделяемый на указанную дисциплину, меняется от 24 до 70 часов и может включать выполнение курсовых работ.

По определению [6, 9] информационные технологии – это комплекс аппаратного и программного обеспечения, предназначенного для целей автоматизации определенных аспектов профессиональной деятельности.

Содержательное наполнение дисциплины определяется ФГОС ВПО соответствующей специальности. Например, согласно требованиям ФГОС по направлению подготовки «Менеджмент» (квалификация «бакалавр») [8] дисциплина «Информационные технологии в менеджменте» направлена на формирование общекультурных компетенций ОК-15 – ОК-18.

---

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

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

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

– способность выбирать математические модели организационных систем, анализировать их адекватность, проводить адаптацию моделей к конкретным задачам управления (ПК-32);

– владение средствами программного обеспечения анализа и количественного моделирования систем управления (ПК-33);

– владение методами и программными средствами обработки деловой информации, способность к взаимодействию со службами информационных технологий и умение эффективно использовать корпоративные информационные системы (ПК-34);

– умение моделировать бизнес-процессы и знание методов реорганизации бизнес-процессов (ПК-35).

Разработанное учебное пособие может быть также использовано на лабораторных занятиях при обучении студентов направлений подготовки «Управление персоналом» и «Государственное и муниципальное управление».

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

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

---

Все работы имеют общую структуру и состоят из следующих разделов:

– краткие теоретические сведения;

– детально разобранный пример;

– варианты заданий.

I. Общие методы работы в MS Excel

1.1. Работа с формулами
1.1.1. Общие сведения

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

Каждая ячейка таблицы имеет адрес, который образуется из названия столбцы и номера строки (например, А1, DE234), записывается латинскими буквами.

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

Формулы всегда начинаются со знака равенства (=) и могут включать в себя константы (значения, не меняющиеся при расчете), переменные, знаки арифметических операций (+, -, *, /, ^), скобки, функции.

Например:

=А1+А5/Е8;

=(0,45+А5^3)/9.

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

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

Относительная ссылка – обозначение ячейки, составленное из названия столбца и номера строки: А1, С8. При копировании формул относительная ссылка меняется.

Абсолютная ссылка создается из относительной ссылки путем вставки знака доллара ($) перед названием столбца и/или номером строки: $A$1, $C$8. При копировании формул абсолютная ссылка не меняется.

Иногда используют смешанный адрес, в котором постоянным является только один из компонентов, например:

$B7 – при копировании формул не будет изменяться номер столбца;

B$7 – при копировании формул не меняется номер строки.
Копирование содержимого ячеек и формул

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

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

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

---

Применение буфера обмена. Выделить копируемый (вырезаемый) диапазон и дать команду для помещения его в буфер обмена: Правка –> Копировать или Правка –> Вырезать. Для вставки: Правка –> Вставить.

Копирование формул производится протягиванием рамки за маркер заполнения. При этой операции происходит изменение адресации.
Вычисления сложных выражений

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

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

1) ^ (возведение в степень);

2) *, / (умножение, деление);

3) +, - (сложение, вычитание).

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

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

Разработайте электронную таблицу для расчета заработной платы сотрудников временного творческого коллектива (ВТК), создавшего Черноморское отделение Арбатовской конторы «Рога и копыта».

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

Общая сумма заработной платы ВТК составляет 100 000 руб. Каждому сотруднику начисляется из этой суммы часть в соответствии с коэффициентом трудового участия (КТУ). Например, Бендеру О.И. – 0,25 от общей суммы или 25%.

Естественно, сумма КТУ всех участников коллектива должна быть равна 1 или 100%.

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

---

1. 
   налог на доходы с физических лиц (НДФЛ), составляющий 13% от начисленной заработной платы за вычетом некоторых сумм, не облагаемых подоходным налогом.
   
2. 
   отчисления в профсоюз, составляющее 1 % от начисленной заработной платы;
   

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

Отчисления во все фонды идут уже за счет работодателя.

Если допустить, что у организации нет права на использование пониженных тарифов, то размер отчислений в социальные фонды составит 22% (так называемый единый социальный налог – ЕСН).

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

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


Рис.1. Размещение данных для задачи расчета заработной платы
2. В столбце КТУ Основной таблицы в ячейку В14 введите формулу для суммирования КТУ всех сотрудников – результат суммирования должен быть равен 1. Это необходимо для проверки правильности начисления зарплаты сотрудникам из общей суммы.

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

3. В ячейках D7:D13 рассчитайте начисленные зарплаты. Расчет производится по формуле:

ИН=ОФЗ*КТУ, (1.1)

где ИН – итого начислено;

ОФЗ – общий фонд заработной платы (указан в ячейке A4).

Для проверки правильности распределения денег между сотрудниками, в ячейке D14 организуйте подсчет общей суммы заработной платы. Она должна равняться сумме из ячейки А4.

4. В ячейках Е7:Е13 рассчитайте отчисления в пенсионный фонд. Расчет ведется по формуле:

ПФ=ИН*ЕСН, (1.2)

где ПФ – отчисления в пенсионный фонд;

ЕСН – ставка единого социального налога (указана в ячейке B4).

5. В ячейках F7:F13 рассчитайте величину профсоюзных взносов. Расчет производится по формуле:

П=ИН*СП, (1.3)

где СП – ставка профсоюзных взносов (указана в ячейке С4).

6. В ячейках G7:G13 вычислите суммы, не облагаемые подоходным налогом. Расчет ведется по формуле:
СНОН=(1+ЧД)* МРОТ, (1.5)

где

---