- •«Тюменская государственная академия
- •Лабораторная работа №1 Тема: Основные приемы работы. Создание и оформление таблиц.
- •1. Общие приёмы работы
- •5. Объединение/разбиение ячеек.
- •8.2. Выделение крайних значений
- •8.3. Форматирование с использованием гистограммы
- •8.4. Форматирование с использованием трехцветной шкалы
- •Лабораторная работа №2
- •Тема: Использование формул. Операторы. Математические расчёты.
- •Возможные ошибки
- •Лабораторная работа №3 Тема: Адресация ячеек. Относительные, абсолютные и смешанные ссылки. Использование логических выражений
- •Лабораторная работа №4 Тема: Создание и редактирование графиков и диаграмм. Поверхности
- •Изменение типа диаграммы. После создания можно изменить тип и вид диаграммы.
- •Изменение источника данных. После создания диаграммы можно изменить диапазон данных, представленных на диаграмме.
- •Выбор стиля оформления элемента. Стиль оформления элемента опреде-ляет цвет и эффекты заливки элемента, параметры линии контура и эффекты, примененные при оформлении элемента.
- •Заливка элементов диаграммы. Заливку элемента можно установить само-стоятельно, независимо от выбранного стиля элемента.
- •Восстановление параметров оформления. Можно отказаться от всех параметров оформления элемента, назначенных после применения стиля к диаграмме.
- •Лабораторная работа №5 Тема: Функции условия
- •3.2. Начисление налогов с помощью условий
- •Практическая работа № 6 Тема: Решение задачи табулирования функции. Построение и редактирование диаграммы
- •Лабораторная работа №7 Тема: Использование средства Подбор параметров
- •2.1. Кредит на покупку квартиры
- •2.2. Вычисление суммы кредита
- •2.3. Подбор параметра для вычисления срока погашения кредита
- •2.4. Подбор параметра для вычисления процентной ставки
- •3.1. Подбор параметра для вычисления начальной суммы депозита
- •3.2. Подбор параметра для вычисления срока депозита
- •3.3. Подбор параметра для вычисления процентной ставки
- •4. Подбор параметра для оптимизации продажи театральных билетов
- •4.1. Вычисление количества билетов для детей
- •4.2. Вычисление количества билетов для взрослых
- •Лабораторная работа №8 Таблицы подстановки
- •4. Использование таблиц подстановки для подсчета дивидендов
- •5. Возможные ошибки при работе с Таблицами подстановки.
- •Лабораторная работа №9 Тема: Поиск решения
- •3. Расчёты по банковским кредитам
- •Лабораторная работа №10 Тема: Сценарии
- •1. Создание и работа со сценариями
- •2. Самостоятельная работа. Использование сценариев для прогноза объемов продаж
- •3. Защита сценариев от изменений
- •Лабораторная работа №11 Тема: Функции для работы с матрицами
- •Лабораторная работа №12 Тема: Работа с датами. Объединение ячеек с текстовыми данными. Создание пользовательских форматов
- •1.1. Автоматически обновляемая текущая дата. Для вставки текущей автоматически обновляемой даты используется функция сегодня () (рис. 1).
- •1.4. День недели произвольной даты
- •4.1. Преобразование регистра текста. Для преобразования регистра текста используются три функции: прописн, пропнач, строч.
- •4. Назначение макроса графическому объекту или элементу управления.
- •5. Запуск макроса.
- •7. Удаление макроса.
- •Лабораторная работа №14 Контрольная работа. Статистические функции Excel
- •Контрольные вопросы
- •Литература
- •Содержание
Лабораторная работа №14 Контрольная работа. Статистические функции Excel
1. Откройте таблицу «Табель учета» (файл с именем к лабораторной работе №14.хls).
2. Занесите формулы для подсчета дней явок: вкладка Формулы, группа Библиотека функций, Категория Статистические, функция СЧЕТ (подсчитывает количество ячеек, содержащих число)), указав диапазон куда входят все дни месяца, и заполните эту формулу на всех работников, используя функцию автозаполнения).
3. Для подсчета дней по болезни, прогулов, командировок, отпусков используйте функцию из категории Статистические СЧЕТЕСЛИ, где надо указать диапазон ячеек, включающий все дни месяца. В качестве критерия отбора для столбца «Отпуск» укажите букву «о», для столбца «Болезнь» – «б», для столбца «Прогул» – «п», для столбца «Командировка» – «к».
4. Для подсчета количества отработанных часов используйте функцию СУММЕСЛИ из Категории Математические, указав в качестве критерия «>0». Третье окошко (Диапазон суммирования) можно не заполнять.
5. Количество выходных дней посчитайте, используя функцию СЧИТАТЬПУСТОТЫ из категории Статистические.
6. Дайте имя листу «ТАБЕЛЬ».
7. Скройте столбцы с днями месяца и оформите таблицу по образцу (рис. 1).
|
Дни неявок |
| |||||||
№ |
Фамилия И.О. |
Профессия |
дни явок |
отпуск |
болезнь |
прогул |
командиров-ка |
отработано часов |
Количество выходных |
1 |
Абдрахманов М.Н. |
рук. фирмы |
15 |
8 |
0 |
0 |
0 |
120 |
8 |
2 |
Абакумов А.А. |
гл. бух |
14 |
9 |
9 |
0 |
0 |
112 |
8 |
… |
|
|
|
|
|
|
|
|
|
29 |
Яснов М.Г. |
эксперт |
8 |
0 |
3 |
0 |
12 |
64 |
8 |
Рис. 1. Таблица «Табель учёта рабочего времени за июль месяц 20… года»
8. Перейдите ко второму листу, дайте ему имя «Налоги». Фамилии свяжите с фамилиями на листе «ТАБЕЛЬ» (лаб. раб. №3, задание 3: связывание данных).
9. Заполните поле «Стоимость часа» (используя функции ЕСЛИ) в зависимости от разряда сотрудника. За точку отсчёта берётся стоимость часа по 9 разряду. Остальным сотрудникам оклад высчитывается в зависимости от коэффициента (табл. №1):
Таблица 1
Расчет стоимости часа сотрудников
разряд |
коэффициент |
15 |
9 |
14 |
8 |
13 |
7 |
12 |
6 |
11 |
5 |
10 |
4 |
10. Добавьте столбец «Командировочные» после столбца «Стоимость часа». Рассчитайте сумму командировочных по формуле: «Стоимость часа»*«командировка» (на листе «Табель»)*12.
11. После поля «Командировочные» добавьте столбец «Начислено». Сумма начислений вычисляется умножением «Стоимости часа сотрудника» на количество отработанных часов на листе «Табель».
12. Добавьте поле «премия» после поля «начислено». Премия начисляется следующим образом: руководитель получает 50% от начислений, главный бухгалтер получает 30% от начислений, все остальные получают 15% от начислений при отсутствии прогулов.
13. Выплаты в профсоюзный и пенсионный фонд начисляются 1% от: «начисления» + «командировочные» + «премия»).
14. Подоходный налог 12% начисляется от: начислено – профсоюзн. –
15. Сумма к выдаче начисляется из «начислено + командировочные + премия – налоги подоходный, профсоюзный и пенсионный».
16. Перейдите на лист 3, назовите его «Ведомость».
17. Свяжите фамилии с фамилиями на листе «Табель», добавьте недостающий столбец «Премия» и свяжите его с одноимённым столбцом на листе «Табель».
18. Сумму к выдаче свяжите с суммой выдачи с листа «Налоги», Подведите итоговую сумму по образцу (рис. 2).
Ведомость на выдачу зарплаты. | |||
№ п/п |
фио |
Сумма |
Подпись |
Рис. 2. Оформление ведомости на выдачу зарплаты