Информационные системы и технологии в финансах (стр. 19 из 21)
3. С помощью редактора Visual Basic создать пользовательскую функцию для расчета зарплаты. 4. Решить обратную задачу с помощью подбора параметра.
Порядок выполнения работы.
Создайте новый документ Excel. Внесите в него заготовку таблицы, содержащую список работников и заголовки столбцов. Таблицу начните с четвертой строки. 
Внесите в нее следующие данные:
| Фамилия | Должность | Оклад |
| Иванов А. А. | Директор | 1375 |
| Петров С. В. | Гл. бухлалтер | 1100 |
| Сидоров С. М. | Менеджер | 800 |
| Терещенко А. П. | Охранник | 390 |
| Светлов И. А. | Аналитик | 550 |
Далее заполните таблицу произвольными именами, так, чтобы общее число сотрудников было не менее 10.
Расчет заплаты будем проводить двумя способами: по старому и новому порядку. Для выбора между вариантами расчета создадим ячейку, позволяющую сделать выбор. Внесите в ячейку А2: "новая система", а в ячейку B2: "старая система". Выделите ячейку А3 и выберите пункт меню "Данные - проверка". В поле "тип данных" выберите "Список" и укажите диапазон ячеек, содержащий введенные словосочетания ("старая система" и "новая система"). Проверьте, как работает получившийся список.
Порядок начисления зарплаты
Старый вариант:
Отчисления в Пенсионный фонд – до 150 грн. – 1%, больше
150 грн. – 2%.
Отчисления в Фонд социального страхования по временной потере трудоспособности – 0,5%
Отчисления в Фонд социального страхования на случай безработицы – 0,5 %.
Подоходный налог определяется по таблице:
| Зарплата | Подоходный налог |
| 18-85 | 10% с превышения 17 грн. |
| 86-170 | 6,80 грн +15% от превышения 85 грн |
| 171 - 1020 | 19,55+20% от превышения 170 |
| 1021 - 1700 | 189,55+30% от превышения 1020 |
| Более 1701 | 393,55+40% от превышения 1700 |
Сумма к выдаче определяется по формуле:
Сумма к выдаче= Оклад -(Сумма удержаний в фонда)- Подоходный налог.
Новая система.
Отчисления в Пенсионный фонд – до 150 грн. – 1%, больше
150 грн. – 2%.
Отчисления в Фонд социального страхования по временной потере трудоспособности – 0,5%.
Отчисления в Фонд социального страхования на случай безработицы – 0,5 %.
Социальная льгота (только при зарплате > 510 грн.) 30% от минимальной зарплаты (237 грн.).
Подоходный налог определяется по формуле:
Налог=13%*(Оклад – Соц. Льгота-(Сумма удержаний в фонды)) Сумма к выдаче определяется по формуле:
Сумма к выдаче= Оклад –(Сумма удержаний в фонды)- Подоходный налог.
Создание формул в ячейках таблицы (приведенные формулы носят ориентировочный характер, в каждом конкретном случае будут свои адреса ячеек!).
Отчисления в Пенсионный фонд определяются с помощью функции ЕСЛИ, которая будет иметь примерно такой вид: ЕС-ЛИ(C7<=150;C7*0,01;C7*0,02)
Подоходный налог определяется с помощью функции ЕСЛИ, примерный вид функции: (C7-ЕСЛИ(C7<510;237*0,3;0)-G7)*0,13. Суммарное удержание зависит от того, какая система, старая или новая, выбрана для расчета, поэтому снова используем функцию ЕСЛИ, которая возвращает то или иное значение, в зависимости от значения ячейки А3; для учета таблицы подоходного налога используем вложенные функции ЕСЛИ. Формула будет иметь примерно такой вид:ЕСЛИ($I$5="новая система";G7+H7;
G7+ЕСЛИ(И(C7>=18;C7<=85);0,1*(C717);ЕСЛИ(И(C7>=86;C7<=170);6,8+0,15*(C785))+ЕСЛИ(И(C7>=171;C7<=1020);19,55+0,2*(C7-
170))+ЕСЛИ(И(C7>=1021;C7<=1700);189,55+0,3*(C7-1020))))
Сумма к выдаче определяется как разность оклада и суммарного удержания.Заполните таблицу формулами, проведите расчет зарплаты и ответьте на следующие вопросы:
У кого из сотрудников прибавка к зарплате при переходе к новой системе станет наибольшей? Какой оклад необходимо назначить Петрову, чтобы он получал на руки 600 грн? (Решение провести с помощью подбора параметра) Какие оклады необходимо назначить сотрудникам, чтобы сумма их зарплат, выданная на руки, равнялась 15000 грн.? (Решение провести с помощью поиска решения). Создайте пользовательскую функцию с именем "Зарплата", содержащую следующий текст:Dim a As Double, b As Double, c As Double If v < 150 Then a = 0.01 * v Else a = 0.02 * v b = 0.01 * v Select Case v Case 0 To 17 c = 0
Case 18 To 85 c = 0.1 * (v - 17) Case 86 To 170 c = 6.8 + 0.15 * (v - 85) Case 171 To 1020 c = 19.55 + 0.2 * (v - 170) Case 1021 To 1700 c = 189.55 + 0.3 * (v - 1020) Case Is > 1701
c = 393.55 + 0.4 * (v - 1700)
End Select
Зарплата = v - a - b – c
После создания макроса вызовите окно макросов (Alt+F8), наберите имя макроса и нажмите кнопку "Свойства". В открывшемся окне введите описание функции "Расчет зарплаты" и нажмите "ОК".
Внесите эту функцию в отдельный столбец исходной таблицы с помощью мастера функций.
Контрольные вопросы:
1. Для чего используются функции "ЕСЛИ" и "И"?
2. Как вводятся сложные формулы?
3. Как создается макрокоманда?
4. Каковы особенности использования макрокоманд в мастере формул?
Практическое занятие № 17.
Использование сводных таблиц для анализа данных
Цель: Освоить методику применения сводных таблиц для анализа данных из таблиц Excel, использовать сводные диаграммы для построения динамических диаграмм и создания отчетов.
План занятия:
1. Изучить на практике возможности сводных таблиц и промежуточных итогов.
2. Решить самостоятельно задачи на сводные таблицы и промежуточные итоги по предлагаемой таблице.
Задания для самостоятельной работы:
По предлагаемой таблице выполнить следующие задачи на сводные таблицы и фильтрацию:
С помощью промежуточных итогов определить суммарную стоимость заказов по организациям, указанным в поле ДОСТАВКА и построить по ним гистограмму на отдельном листе. Вывести на экран заказы, отправленные в Берлин. С помощью сводной таблицы определить сумму заказов, исполненных всеми Сотрудниками для всех клиентов в Австрии. С помощью сводной таблицы определить среднюю стоимость ДОСТАВКИ всех Организаций в различные страны. С помощью промежуточных итогов вывести среднее значение стоимости заказа для каждой страны и построить по этим данным диаграмму. С помощью сводной таблицы найти распределение количества заказов данного Сотрудника по различным странам. С помощью сводной таблицы найти для данной страны среднюю стоимость доставки различными организациями и построить по этим данным гистограмму. С помощью промежуточных итогов найти суммарную стоимость доставки заказов в каждую страну и построить по этим данным гистограмму. С помощью сводной таблицы найти распределение количества заказов данного Сотрудника по всем странам и всем операторам Доставки. С помощью сортировки и промежуточных итогов определить сумму заказов каждого Сотрудника за 1997 год. С помощью сводной таблицы определить количество заказов, ДОСТАВЛЕННЫХ всеми Организациями (Ространс, Почта и т.д) в различные страны. С помощью сводной таблицы определить сумму заказов исполненных всеми Сотрудниками для всех клиентов в США и построить по этим данным гистограмму.Контрольные вопросы:
1. Использование мастера построения сводных таблиц и диаграмм.
2. Настройка сводной диаграммы.
3. Использование промежуточных итогов.
4. Каковы особенности использования сводных диаграмм при импорте в Word?
Практическая работа № 18-19. Создание и заполнение базы данных
Цель: Создать базу данных "Адресная книга" и организовать в ней пользовательский интерфейс для заполнения базы данными.
План занятия:
1. Создать таблицы базы данных.
2. Определить связи между таблицами.
3. Создать и настроить простые и подчиненные формы.
4. Заполнить таблицы данными.
Порядок выполнения работы
Запустите Access и создайте пустую базу данных. Перейдите на вкладку "Таблицы", нажмите кнопку "Создать". Выберите создание таблицы в режиме конструктора. Создание базы данных адресов фирм начнем с создания таблицы областей. Создайте поле "КодОбласти", в столбце "Тип данных" укажите тип "Счетчик". Второе поле назовите "Название области", его тип – текстовый. Поле "КодОбласти" сделайте ключевым. Для этого следует щелкнуть правой кнопкой мыши слева от имени поля выбрать в контекстном меню пункт "Ключевое поле". Закройте конструктор и присвойте таблице имя "Области".