Ведомость на выдачу заработной платы

4) Запишите макрос «Диаграмма», создающий диаграмму на отдельном листе (объемная круговая, подписи данных - значение), отражающую начисления каждого сотрудника. На листе «Начисления» поместите кнопку «Диаграмма» для выполнения этого макроса. После выполнения макроса «Диаграмма» переименуйте созданный лист в лист «Диаграмма».

5) Запишите макрос «Новый сотрудник», добавляющий в таблицу пустую 10 строку и необходимые формулы. На листе «Начисления» поместите кнопку «Новый сотрудник» для выполнения этого макроса.

6) Введите данные нового сотрудника.

7) Запишите макрос «Сортировка ФИО», выполняющий сортировку таблицы сведения о начислениях по фамилии. На листе «Начисления» поместите кнопку «Сортировка ФИО» для выполнения этого макроса.

8) Запустите макрос «Новый сотрудник», введите данные, затем запустите макрос «Сортировка ФИО».

9) Вставьте лист. Переименуйте его в «Итоги». Скопируйте таблицу сведения о начислениях на этот лист и выполните автоматическое вычисление итогов по числу детей.

Пояснения к выполнению работы:

Надбавка районная вычисляется как произведение районного коэффициента на оклад. Формула, которую необходимо ввести в ячейку D7, имеет вид = $K$3*C7. После ввода формулы ее нужно распространить вниз (протянув за маркер автозаполнения).

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

Принять профсоюзный и пенсионный налоги, составляющими по 1% от оклада. Формула, которую необходимо ввести в ячейку F7, имеет вид = $C7/100 или =$C7*1%. После ввода формулы ее нужно распространить вниз (протянув за маркер выделения) и затем вправо на один столбец.

Ячейке, содержащей значение Мин.з/п, присвоить имя Мин_з_п, выполнив команду Вставка-Имя-Присвоить. Далее в поле Имя введите нужное имя ячейки.

Подоходный налог подсчитать по формуле 13% от Всего начислено за вычетом пенсионного фонда, минимальной зарплаты и льгот на детей. Льготы на детей составляют 75% от минимальной зарплаты умноженной на количество детей. Формула имеет вид = (E7 – G7 – Мин_з_п – Мин_з_п * 75 / 100 * K7) * 13 / 100 или = (E7 – G7 – Мин_з_п –Мин_з_п * 75% * K7) * 13%. После ввода формулы в ячейку H7, ее нужно распространить вниз.

Всего удержано вычисляется как сумма всех налогов и размещенной в ячейке I7 и распространенной вниз. Для подсчета Суммы к выдаче примените формулу, вычисляющую разность всего начислено и налогов. Примерный вид формулы =E7-I7, размещенной в ячейке J7 и распространенной вниз.

Для вычисления Итого, в ячейку D18 введите Автосуммирование S, затем распространите формулу по строке.

Для вычисления среднего значения окладов в ячейку E19 необходимо с помощью мастера функций на панели инструментов (или командой Вставка – Функция) ввести функцию СРЗНАЧ() из категории Статистические. Аргументом для функции является диапазон ячеек E7:E17, т.е. получится формула =СРЗНАЧ(Е7:Е17).

Переименовать ярлычки листов в соответствии с их содержимым. Для этого, выполните команду Формат-Лист-Переименовать и в поле ввода Имя листа введите новое название листа, например, “Начисления”.

Перейдите к Листу 2. Сразу же переименуйте его в “Детские”. Выделите ячейку А2 листа “Детские” и введите формулу: = Начисления!В7, где имя листа определяется восклицательным знаком, а В7 - адрес ячейки, в которой размещена первая фамилия сотрудника на листе “Начисления”. Распространите формулу вниз. В графе “Сумма” разместите формулу =Начисления!K7*Начисления!$K$4, где K7 - адрес первой ячейки на листе “Начисления”, содержащей число детей, K4 - адрес ячейки, содержащей пособие на одного ребенка. Заполните эту формулу вниз.

Для того, чтобы список состоял только из сотрудников, имеющих детей, установите фильтр по наличию детей (Данные-Фильтр-Автофильтр, в раскрывающемся списке “Сумма” выберите и установите критерий по условию >0).

Для записи макроса необходимо выполнить команду Сервис – Макрос – Начать запись. В появившемся диалогом окне введите имя макроса, например: «Выдача». Далее добавьте лист и на этом листе создайте ведомость на выдачу заработной платы, осуществляя связь с листом «Начисления».Выполните оформление таблицы по образцу. Затем остановите запись макроса (Сервис-Макрос-Остановить запись).

Для создания кнопки необходимо добавить панель инструментов – Формы (Вид–панели инструментов-Формы). Выбрать инструмент Кнопка и расположить ее листе «Начисления». В появившемся диалогом окне Назначить макрос объекту выберите необходимый макрос, например: «Выдача». Измените текст названия кнопки на текст «Ведомость». Проверьте выполнение макроса, нажав на созданную кнопку. Переименуйте добавленный лист «К выдаче».

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

Чтобы выделить два столбца в разных местах таблицы, следует удерживать нажатой клавишу Ctrl.

Для построения диаграммы требуется выделить два столбца таблицы: “Фамилия, имя, отчество” и “Сумма к выдаче”, и запустить Мастер диаграмм одним из способов: либо выбрав кнопку на панели инструментов, либо команду меню Вставка-Диаграмма. Передвигаясь по шагам с Мастером диаграмм, выберите тип диаграммы - круговая объемная, подписи данных - Значение. Поместить диаграмму на листе - Отдельном.

Переименуйте лист, содержащий диаграмму из “Диаграмма1” в “Диаграмма”.

При записи макроса «Новый сотрудник» для добавления пустой строки нужно выделить ту строку, которую будете вставлять (у нас 10-ая строка) и выполнить команду Вставка – Строки. Затем, в предыдущей строке выделите ячейки, содержащие формулы (это диапазон ячеек E9:K9), и с помощью маркера заполнения распространите формулы в добавленную строку. Остановите запись макроса. Введите данные о новом сотруднике.

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

Проверьте, какая связь существует между таблицей начислений и диаграммой. Для этого перейдите на лист “Начисления” и запустите макрос «Новый сотрудник». Введите данные о новом сотруднике. Теперь перейдите на лист “Диаграмма” и проверьте, как новые данные отразились на диаграмме. Данные о новом сотруднике сразу же вносятся в диаграмму.

Выполните сортировку таблицы с помощью макроса «Сортировка ФИО», нажав на кнопку «Сортировка ФИО».

Для выполнения автоматического вычисления итогов по числу детей, сделайте сортировку по числу детей, а затем выполните команду Данные – Итоги. В диалоговом окне Промежуточные итоги установите в поле При каждом изменении – Число детей, в поле Операция – Количество, в поле Добавить итоги по –Число детей. Установите флажки в Заменить текущие итоги и Итоги под данными.

Лабораторная работа №7 (анализ спроса)

1) В книге «Анализ спроса и продаж» создайте лист “Январь” и в нём заполните таблицу, анализирующую спрос и продажи одной из торговых фирм, и заполните ее постоянными значениями – числами, символами, текстом (см. Рис.).

a) Название таблицы вводится в любую ячейку и оформляется шрифтами (рекомендуется вводить в левую верхнюю ячейку, а затем, после оформления шапки таблицы, сделать выравнивание по центру выделения (по ширине таблицы));

b) Формирование шапки таблицы рекомендуется проводить в следующей последовательности:

· задайте способ выравнивания названия граф (при больших текстах необходимо обеспечить перенос по словам);

· в каждую ячейку одной строки введите названия граф таблицы;

· установите ширину каждого столбца таблицы.

2) После окончания оформления шапки таблицы введите в таблицу постоянные данные:

· Наименования продукции, цену за ед., спрос, предложения, продажу безналичную и наличную;

· Заголовок в нижней части для итоговых данных.

Рис. Форма таблицы «Анализ спроса и продаж»

3) Введите формулы для подсчета:

a) Продажа Всего — сумма Продажа Безнал. и Налич.;

b) Выручка от продаж — произведение Продажа Всего на Цена за ед.;

c) Итого — сумма количества продукции по Спросу, Предлож., Продажам по Безнал., Налич., Всего и сумма Выручка от продаж. Используйте Автосумму.

4) Вставьте в середину таблицы три дополнительные строки со следующей информацией:

5) Заполните отсутствующие формулы.

6) Скопируйте таблицу с листа Январь на Лист2 и Лист3.

7) Переименуйте листы, дав им имена: февраль, март, соответственно.

8) Отредактируйте содержимое столбцов Наименование продукции и Цена за ед. за февраль и март в соответствии со следующими данными:

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

10) Построите диаграммы:

a) показывающую спрос и предложения для различных видов товаров;
тип диаграммы - О бычная гистограммы, поместите диаграммы на имеющемся ли сте

b) показывающую соотношение объема безналичных и наличных продаж;
тип диаграммы – График, поместите диаграмму на новом листе

c) показывающую соотношение спроса и цены;
тип диаграммы – Классическая смешанная гистограммы(График\Гистограмма 2), поместите диаграммы на имеющемся ли сте.
Добавьте в эту диаграмму параметры диаграммы - Подписи данных - значения

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

11) Измените в таблице значение спроса по одному из изделий и проследите за графиками.

12) Измените значение в диаграмме, проследите за таблицей.

13) Трендовый анализ.

а) Постройте дополнительно таблицу:

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

Лабораторная работа № 8 (решение уравнений)

1) Используя электронную таблицу Excel, создайте файл с именем «Численные задачи».

2) Переименуйте Лист1 в “РНУ” (Решение Нелинейных Уравнений).

3) Постройте график функции f(x) на интервале поиска (-3,3)

4) На листе “РНУ” решите нелинейное алгебраическое уравнение f(x)=0, где

Для решения уравнения (5 корней) используйте два метода:

а) подбор параметра;

б) поиск решения.

Лабораторная работа № 9 (решение систем)

1) В книге “Численные задачи” создайте лист “СЛАУ”(Система линейных алгебраических уравнений)

2) На листе “СЛАУ” с помощью мастера формул (Вставка – Объект – Microsoft Equation 3.0) введите систему линейных алгебраических уравнений:

3) Решите заданную систему двумя методами:

а) методом поиска решений;

б) с помощью матричных операторов.

Лабораторная работа № 10 (системы нелинейных уравнений)

1) В книге “Численные задачи” создайте лист “СНУ”(Система нелинейных уравнений).

2) Решите систему двух нелинейных уравнений, т.е. найдите точки пересечения функций:

a) графически;

b) методом Подбор параметра;

c) методом Поиск решения.

Для решения системы методами п.2b и п.2c заполните таблицу:

В столбец “Формулы” вводите обе функции, которые в качестве аргумента Х ссылаются на ячейку с начальным значением. В качестве целевой выберите функцию СРОТКЛ – среднее арифметическое абсолютных отклонений значений аргументов от среднего (аргументами являются функции).

Найдите два разных решения. Результат зависит от заданного начального значения.

Проверьте, совпадают ли решения, найденные различными методами.

Лабораторная работа № 11 (трендовый анализ)

1) В книге “Численные задачи” создайте лист “Тренд”и в нем заполните таблицу:

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

3) Определите, в каком году производительность предприятия будет равна 4500.

4) Осуществите прогноз на графике.

Лабораторная работа № 12 (финансовые функции)

В книге “Финансовые функции” создайте лист “Функции”, и в нем выполните решение следующих задач: