Электронные таблицы MS Excel. Практические задания.
Задача: «Расчет начислений и удержаний». Часть 1.
Цель задания 1: Изменение ориентации текста в ячейке, ознакомление с возможностями баз данных Excel. Сортировка данных по нескольким ключам. Подготовка документа к печати. Формулы, имеющие ссылки на ячейки другого листа рабочей книги. Мастер диаграмм. Выделение ячеек таблицы, не являющихся соседними
Состав задания.
1. Создать средствами MS Excel таблицу для расчета удержаний и сумм к выдаче в соответствии с приведенной ниже формой Таблицы 1.
1.1. При оформлении заголовка выделите те ячейки, в которых нужно развернуть текст, и выберите команду «Формат – Ячейки – Выравнивание ». Используйте нужную ориентацию текста и переключатель «Переносить по словам». Для неразвернутых ячеек примените вертикальное выравнивание по центру.
1.2. Установите в ячейках, содержащих дату и денежные суммы соответственно денежный формат и формат дат.
| Дата получения | Таб.№ | Фамилия И. О. | Количество детей | Начислено | Благотворительность | Строительство жилья | Подоходный налог | К выдаче |
| 05.01.01 | Сидоров П.С. | 2 350,00 р. | 350,00 р. | 800,00 р. | ||||
| 05.01.01 | Бондарев В.К. | 1 950,00 р. | 150,00 р. |
1.3. Расчет удержаний и суммы к выдаче произведите по следующим формулам:
Подоходный = 13%*(Начислено – ИндВыч –КолДет*ДетВыч - Благотвор. – Строит.)
ИндВыч=400 р ДетВыч=300 р
К выдаче = Начислено - Подоходный - Благотворительность
2. Ввести данные по остальным работающим в режиме формы.
2.1. Выделите заголовок таблицы и 2 строки.
2.2. Выберите команду " Данные – Форма ". Получаем имена полей и окон редактирования, в которых можно вводить и редактировать текст. Вычисляемые поля выводятся на экран без окон редактирования.
2.3. Дойдя до 3 записи, заполните ее нижеприведенными данными. Переход к новой записи формы происходит по нажатию клавиши Enter. При внесении новой записи в форму она воспроизводится в исходной таблице.
| 05.01.01 | Иванов И.П. | 1 120,00 р. | 100,00 р. | |||||
| 05.01.01 | Карцев П.Р. | 2 200,00 р. | 250,00 р. | 450,00 р. | ||||
| 05.01.01 | Авдеев А.С. | 1 560,00 р. | 50,00 р. | |||||
| 05.01.01 | Якушев А.Р. | 1 760,00 р. | 300,00 р. |
3. 
Познакомиться с помощью справочной системы со скрытием отдельных частей таблицы, рабочего листа и всей рабочей книги. Скрыть часть столбцов таблицы, в соответствии с примером:
4. Отменить скрытие столбцов, скопировать часть таблицы, содержащую данные, и, изменив дату получения и данные о Начислениях, Благотворительности и Строительстве, привести таблицу к следующему виду:
| Дата получения | Таб.№ | Фамилия И. О. | Количество иждивенцев | Начислено | Благотворительность | Строительство жилья | Подоходный налог | К выдаче |
| 05.01.01 | Сидоров П.С. | 2 350 р. | 350,00 р. | 800,00 р. | 65,00р. | 1 935,00р. | ||
| 05.01.01 | Бондарев В.К. | 1 950 р. | 150,00 р. | 104,00р. | 1 696,00р. | |||
| 05.01.01 | Иванов И.П. | 1 120 р. | 100,00 р. | 2,60р. | 1 017,40р. | |||
| 05.01.01 | Карцев П.Р. | 2 200 р. | 250,00 р. | 450,00 р. | 104,00р. | 1 846,00р. | ||
| 05.01.01 | Авдеев А.С. | 1 560 р. | 50,00 р. | 27,30р. | 1 482,70р. | |||
| 05.01.01 | Якушев А.Р. | 1 760 р. | 300,00 р. | 98,80р. | 1 361,20р. | |||
| 05.02.01 | Сидоров П.С. | 2 000 р. | 250,00 р. | 650,00 р. | 52,00р. | 1 698,00р. | ||
| 05.02.01 | Бондарев В.К. | 1 800 р. | 120,00 р. | 88,40р. | 1 591,60р. | |||
| 05.02.01 | Иванов И.П. | 1 300 р. | 100,00 р. | 26,00р. | 1 174,00р. | |||
| 05.02.01 | Карцев П.Р. | 1 650 р. | 200,00 р. | 225,28 р. | 68,21р. | 1 381,79р. | ||
| 05.02.01 | Авдеев А.С. | 1 700 р. | 60,00 р. | 44,20р. | 1 595,80р. | |||
| 05.02.01 | Якушев А.Р. | 1 250 р. | 180,00 р. | 48,10р. | 1 021,90р. |
5. С помощью действий предыдущего пункта сформировать в таблице данные за шесть месяцов.
6. Познакомиться с помощью справочной системы с командами Разделить и Закрепить области меню Окно. С помощью этих команд разбить таблицу на подокна, зафиксировав заголовок и первые четыре столбца таблицы.
7. Сгруппировать данные по каждому из сотрудников, упорядочив их по месяцам.(Данные – Сортировка)
8. Для каждого из сотрудников вставить строку с его итогами за полугодие.(Данные – Итоги)
9. Скопировать таблицу. В полученной копии необходимо получить список только тех сотрудников, которые отчисляли деньги на строительство жилья. Для этого:
9.1. Выделить таблицу. Выбрать команду " Данные – Фильтр – Автофильтр ". Снять выделение с таблицы. (У каждой ячейки заголовка появится кнопка, позволяющая вводить критерии фильтра).
9.2. Раскрыть выпадающий список ячейки "Строительство жилья". Выбрать команду «Условие». В появившемся диалоговом окне установить параметры "больше" и "0".
9.3. Для того, чтобы снова увидеть перечень всех сотрудников, нужно в выпадающем списке выбрать критерий Все.
10. Познакомиться с установкой параметров страницы и колонтитулов. Установить только нижний колонтитул, содержащий номер страницы и дату. Просмотреть, как созданный документ будет выглядеть на печати.
Электронные таблицы MS Excel. Практические задания.
Задача: «Расчет начислений и удержаний». Часть 2.
Состав задания
1. Переименуйте лист, содержащий сведения о расчете удержаний и сумм к выдаче. Имя листа – «Начисления».
2. Создайте новый лист. Назовите его «Итоги». На основе данных таблицы, содержащей итоги каждого сотрудника за шесть месяцев, постройте следующую таблицу.
| Таб.№ | Фамилия И. О. | Количество иждивенцев | Начислено | Благотворительность | Строительство жилья | Подоходный налог | К выдаче |
| Карцев П.Р. | В соответствующих ячейках таблицы разместите итоговые суммы за полугодие для каждого работающего: 1.в ячейках «Начислено» сошлитесь на адрес соответствующих ячеек исходной таблицы листа «Начисления». (В ячейку введите знак =, щелкните на нужной ячейке листа «Начисления», нажмите клавишу Enter.) 2.С помощью маркера заполнения размножьте ссылки на итоговые результаты влево по строкам. | ||||||
| Иванов И.П. | |||||||
| Якушев А.Р. | |||||||
| Сидоров П.С. | |||||||
| Бондарев В.К. | |||||||
| Авдеев А.С. |
3. По данным таблицы постройте диаграмму, отражающую сумму, полученную каждым сотрудником. Для этого:
3.1. Выделите 2 столбца таблицы «Фамилия И.О.» и «К выдаче» (без ячеек заголовка). (Excel позволяет выделять расположенные не по порядку ячейки, если при выделении диапазона ячеек удерживать нажатой клавишу Ctrl. Выделить искомые столбцы можно также, используя команду Формат – Столбцы – Скрыть ).
3.2. Запустите Мастер диаграмм (Команда Вставка – Диаграмма или кнопка на панели инструментов).
3.3. Передвигаясь по шагам Мастера диаграмм, выберите:
3.3.1. тип диаграммы (Например, объемный вариант круговой диаграммы);
3.3.2. ряды данных в столбцах;
3.3.3. название диаграммы: «Начисления сотрудников»; легенду, размещенную внизу страницы; подписи значений в виде процентной доли;
3.3.4. размещение диаграммы на отдельном листе с именем "Итоговые начисления".
4. Назовите таблицу «Итоговые начисления сотрудников», оформив название как объект WordArt (Вид – Панели инструментов – WordArt). Измените цвет заливки и форму объекта.
5. Придумайте и постройте не менее трех других диаграмм и графиков, отображающих данные таблиц на листах «Начисления» и «Итоги».
6. Установите для вновь созданных листов новые параметры страницы, создайте колонтитулы, вставьте номера страниц.
Задача: "Табель учета рабочего времени".
Цель задания 2: Создание бланка-шаблона. Применение шаблона. Категория статистических функций. Функции СЧЕТЗ, СЧЕТЕСЛИ, СЧИТАТЬ ПУСТОТЫ. Проверка уровня сформированности навыков закрепления областей, построения диаграмм.
1. В новой рабочей книге оставьте только один рабочий лист.
2. Сформатируйте заголовок табеля учета рабочего времени за текущий месяц и подготовьте бланк-таблицу по нижеприведенному образцу:
| Фамилия И.О. | Профессия | Разряд | Числа | месяца | Дни недели | |||||||||||||
| Дни явок | Отпуск | Болезнь | Прогул | Отраб. часов | ||||||||||||||
| Карцев П.Р. | токарь | |||||||||||||||||
| Иванов И.П. | слесарь | |||||||||||||||||
| Якушев А.Р. | инженер | |||||||||||||||||
| Сидоров П.С. | бухгалтер | |||||||||||||||||
| Бондарев В.К. | мастер | |||||||||||||||||
| Авдеев А.С. | токарь | |||||||||||||||||
3. Воспользуйтесь всеми известными вам приемами форматирования и выравнивания текста. Введите числа месяца с 1 по 31. Для столбцов, содержащих даты, установите ширину столбца, равную 2 символам.
4. Сохраните подготовленный файл на собственном диске как шаблон (в диалоговом окне Сохранить выберите тип файла Шаблон).
5. Создайте копию шаблона как файла MS Excel. (Команда Сохранить Как … Тип файла Книга Microsoft Excel).
6. Введите название текущего месяца в заголовок табеля. Выделите цветом столбцы, соответствующие нерабочим дням недели.
7. Проставьте для каждого сотрудника:
· количество часов, отработанных за день, или
· о, если он находится в отпуске, или
· б, если в этот день сотрудник болеет, или
· п, если прогуливает. (о, б, п – русские буквы, проставляются без кавычек)
8. Зафиксировать заголовок таблицы и первый столбец. (Окно – Разделить – Закрепить области).
9. Подсчитайте:
9.1. сумму отработанных часов;
9.2. дни явок (Статистическая функция СЧЕТ);
9.3. количества дней отпуска, болезни, прогулов (Статистическая функция СЧЕТЕСЛИ, критерий – русские буквы о, б или п в кавычках);
9.4. в отдельной ячейке количество нерабочих дней месяца (Статистическая функция СЧИТАТЬ ПУСТОТЫ)
10. Откройте файл, содержащий шаблон, и скопируйте туда все формулы. Сохраните изменения.
11. Постройте круговую диаграмму, отражающую число отработанных часов каждым сотрудником. (Для выделения несоседних столбцов используется клавиша CTRL).
12. Постройте гистограмму, отражающую дни явок и число отработанных часов для каждого сотрудника.