Формирование таблицы «Ведомость по начислению зарплаты»

Цель задания

1. Практика в создании таблиц.

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

3. Работа с числами, представленными в процентном формате.

4. Использование кнопки Автосумма для быстрого подсчета итогов.

5. Форматирование данных таблицы.

6. Вставка и удаление записей.

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

Основные понятия

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

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

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

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

Таблица 4

Исходные данные:

n величина оклада каждого сотрудника (окл);

n ставка уральского коэффициента (ур._коэф.) и подоходного налога (под._нал.).

Расчетные формулы:

n начисление районных на заданную сумму оклада (район.=окл.* ур._коэф).;

n налог подсчитаем на всю сумму дохода: ( налог=(окл.+район.)* под._нал);

n значение зарплаты (зарпл.=окл.+район.-налог);

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

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

1. Запустите программуExcel и откройте свою рабочую книгу.

2. Щелкните на ярлычок Лист4. Смените имя листа на имя Ведомость.

3. Введите в ячейки исходные данные и расчетные формулы, как это представлено в макете табл. 4. Для этого выполните следующее:

n текст «Ведомость по начислению зарплаты» введите в ячейку A1. Выполните форматирование ее содержимого (измените размер шрифта и выполните объединение ячеек и центрирование по ширине таблицы);

n тексты «Ставка уральского коэффициента» и «Ставка подоходного налога» введите соответственно в ячейки A2 и A3. Значения ставок в процентах - в ячейки E2 и E3;

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

n порядковые номера для записей введите используя один из способов ввода последовательности в смежные ячейки. Для этого:

n введите 1 в ячейку A5;

n сделайте ее активной;

n курсор мыши установите на маркер автозаполнителя и при нажатой клавиши Ctrl создайте последовательность до цифры 5.

n в ячейки B5:B9 введите фамилии, в ячейки C5:C9 - значения окладов;

n формулу начисления районных (=C5*$E$2) введите в ячейку D5 (для первого сотрудника в списке). Ссылка C5 указывает на ячейку со значением оклада Авдеева, абсолютная ссылка $E$2 - на ячейку со значением ставки уральского коэффициента. После копирования формулы из ячейки D5 на интервал D6:D9 номера строк в ссылке C5 изменятся соответственно на C6 для формулы в ячейке D6, на C7 - для D7 и т.д. Ссылка же $E$2 останется неизменной во всех скопированных формулах.

n формулу для вычисления налога (=(C5+D5)*$E$3) введите в ячейку E5. При копировании формулы из ячейки E5 на интервал E6:E9 ссылки C5 и D5 изменятся на C6 и D6 и т.д. , а $E$3 также останется неизменной;

n формулу для подсчета зарплаты (=C5+D5-E5) введите в ячейку F5, а затем скопируйте на интервал F6:F9. Все ссылки на ячейки в скопированных формулах изменятся автоматически;

n подсчет суммы значений зарплат выполните, используя встроенную функцию СУММ( ). Для этого активизируйте ячейку F10 и щелкните на кнопке å Автосумма, которая находится на вкладке Формулы в группе Библиотека функций. В ячейке F10 и в строке формул отобразится формула =СУММ(F5:F9). Нажмите клавишу Enter для завершения ввода. Скопируйте формулу из ячейки F10 на интервал C10:E10. В строке Итого в соответствующих ячейках будут отображены суммы значений содержимого столбцов таблицы.

4. Выполните форматирование данных таблицы. Для этого сначала выделите ячейку или интервал ячеек, а затем используйте на вкладке Главная кнопки в группах Шрифт, Выравнивание, Число, Ячейки или из контекстного меню выберите команду ФорматàЯчейки... Выбирайте соответствующие вкладки и опции диалогового окна Форматирование ячеек. Выделяйте интервалы ячеек и подбирайте для них подходящий вид рамок.

5. Перезапишите рабочую книгу на диске.

6. Выполните дополнительное задание

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

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

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

7. Перезапишите рабочую книгу на диске.

8. Измените ставку уральского коэффициента на величину 30%. Обратите внимание на автоматический пересчет формул в ячейках.

· ПРИМЕЧАНИЕ. Если какое-то действие вы выполнили неверно, сразу же откажитесь от него щелчком на кнопке Отменить на панели быстрого доступа (раскрыв список можно отменить несколько последних действий выделив их в списке).Чтобы повторно выполнить отмененное действие, нажмите кнопку Вернуть на панели быстрого доступа.

Задание N 4

Создание ТАБЛИЦЫ "Курс акций" И ПОСТРОЕНИЕ ДИАГРАММ - ГРАФИКОВ

Цель задания

1. Изучить приемы построения диаграмм-графиков, иллюстрирующих данные листа Excel 2007.

2. Использование функции СЛЧИС() (генерации случайного числа) для быстрого заполнения ячеек данными.

Запустите Excel и откройте свою рабочую книгу. Вставьте новый лист и назовите его Акции. Затем выполните задание.

1. Сформируйте таблицу «Курс акций и диаграммы». Исходные данные и расчетные формулы представлены в макете табл.5.

Методические указания

1. Введите текстовую информацию в ячейки. Затем в интервал ячеек A6:A18 введите последовательность значений дат с шагом в 7 дней. Для этого:

· в ячейку A6 запишите начальную дату 02.01.10а в ячейкуА7 9.1.10;

· выделите две ячейки с датами и используя мышь и маркер автозаполнителя создайте последовательность из дат с шагом 7 дней на первый квартал 2010 года.

Таблица 5

2. В ячейку B6 введите расчетную формулу, определяющую стоимость акций на 02.01.2010 компании "Рога и копыта". Величина стоимости определяется как произведение функции СЛЧИС() на величину 1000, т.е. =1000*СЛЧИС().Эту формулу скопируйте в ячейки для всех компаний и для всего рассматриваемого интервала времени с помощью мыши и маркера Автозаполнителя(можно использовать другой способ копирования). Этот прием используется для того, чтобы не вводить большое количество исходных данных. Имейте в виду, что при нажатии функциональной клавиши F9 или выполнении любой команды ввода, значения стоимости акций компаний пересчитываются (генерируется новое значение числа при выполнении функции СЛЧИС()).

3. В ячейки, используя Мастер функций B2,B3 и B4, введите формулы, рассчитывающие высший, низший и средний курс акций: в B2 = МАКС(B6:B18), в B3 = МИН(B6:B18), в B4 = СРЗНАЧ(B6:B18).

Формулы из интервала ячеек B2:B4 скопируйте на интервал C2:F4 для всех компаний.

4. Выполните операции форматирования данных. Числовой массив значений округлите до целых командой ФорматàЯчейки...àЧислоàЧисловой форматà0 знаковàOK.

5. Задание 4а. Постройте график, отображающий динамику изменения курса акций компании Инко относительно даты и разместите его на текущем листе.