3. Практическое задание

1. Открыть таблицу, созданную на предыдущем занятии (список сотрудников).

Функции СЧЕТЕСЛИ, СУММЕСЛИ

2. Подсчитать количество штатных сотрудников на предприятии.

3. Подсчитать суммарный оклад штатных сотрудников.

4. Подсчитать средний оклад штатных сотрудников.

5. Добавить в таблицу столбец Признак, отметить в нем многодетных сотрудников. Подсчитать их количество. Подсчитать суммарную материальную помощь, выплаченную многодетным сотрудникам.

Сортировка записей

6. Отсортировать таблицу так, чтобы фамилии сотрудников располагались по алфавиту.

7. Отсортировать таблицу так, чтобы в начале списка находились сотрудники с наибольшим количеством детей.

8. Выполнить двухуровневую сортировку – по категории и по алфавиту.

9. Выполнить трехуровневую сортировку, поля для сортировки выбрать самостоятельно.

Отбор (фильтрация) записей

10. Отфильтровать и скопировать на отдельный лист список совместителей. Переименовать рабочий лист, дать ему имя Совместители.

11. Сформировать на отдельном рабочем листе список многодетных сотрудников. Переименовать лист.

12. Сформировать на отдельном рабочем листе список низкооплачиваемых штатных сотрудников.

13. Сформировать на отдельном рабочем листе список лаборантов и референтов.

14. Работу с автофильтром завершить, отключить автофильтр.

Промежуточные итоги

15. Определить средние оклады штатных сотрудников и совместителей (сначала отсортировать список по категории, затем использовать команду Данные – Промежуточные итоги).

16. Обратите внимание на появление новых (дополнительных) строк в таблице.

17. Оставить на экране только итоговые строки, скрыв записи исходного списка.

18. Убрать промежуточные итоги.

Сводные таблицы

19. Создать сводную таблицу, отображающую средние оклады мужчин и женщин – совместителей и штатных сотрудников.

20. Создать сводную таблицу, отображающую средние оклады мужчин и женщин по должностям.

Консолидация

21. Создать рабочую книгу «Консолидация». Скопировать в нее таблицу с Листа1 рабочей книги Exc_3. Присвоить листу имя «2010 год».

22. Скопировать таблицу «Расход» на 2 листа. Присвоить листам имена «2011 год», «2012 год». Изменить некоторые данные на листах «2011 год», «2012 год».

23. На новом листе выполнить консолидацию по позиции таблиц на листах «2010 год», «2011 год», «2012 год».

24. Скопировать на новый лист книги Консолидация таблицу из книги Pr_sv_3. Заменить названия месяцев: апр – на янв, май – на фев, июн – мар. Присвоить листу имя Квартал_1.

25. Скопировать таблицу на 3 листа. Присвоить листам имена Квартал_2, Квартал_3, Квартал_4. Изменить названия месяцев.

26. На новом листе выполнить консолидацию по категории таблиц на листах Квартал_1, … Квартал_4.

27. На новом листе выполнить консолидацию для категорий Книга, Видео.

Функции прогнозирования

28. Скопировать на один из листов рабочей книги данные из файла NAS1_новый.xls. Присвоить листу имя Население. Определить прогноз численности населения в 2015г., используя функции Тенденция и Рост. Оформить таблицу. Построить графики.

4. ЛАБОРАТОРНАЯ РАБОТА №3 Решение различных задач с использованием функций Excel

1. ЦЕЛЬ РАБОТЫ

Изучение методов решения задач с использованием различных функций (логических, даты и времени). Разработка алгоритма решения задачи и его реализация средствами Excel.

2. ПЛАН РАБОТЫ

Выполнение расчетов с использованием логических функций.

Освоение приемов работы с командой «Специальная вставка».

Работа с датами.

Решение задач с использованием различных функций.

3. ПРАКТИЧЕСКОЕ ЗАДАНИЕ

Функция ЕСЛИ, вычисления в таблице

1. Создать новую рабочую книгу. Сохранить ее в личной папке под именем «Функции».

2. Скопировать в книгу Функции таблицу из файла Организация_10.xls. Присвоить листу имя Список_сотрудников.

3. В столбец НАДБ ввести надбавку в 25% от оклада для сотрудников, чей стаж превышает 15 лет, и 10% от оклада для сотрудников, чей стаж превышает 10 лет.

4. Вставить столбец НАДБ1. Ввести в него надбавку 20% от оклада женщинам со стажем более 10 лет и мужчинам со стажем более 15 лет.

5. В столбец ПРИЗН ввести признак «Ветеран Труда» женщинам со стажем более 20 лет и мужчинам со стажем более 25 лет.

6. Вставить столбец ПРИЗН1. Ввести в него признак «молодой специалист» для сотрудников со стажем менее 10 лет и возрастом до 33 лет.

7. Вставить столбец ПРИЗН2. Ввести в него признак «призывник» для мужчин от 18 до 27 лет. Подсчитать суммарный оклад указанных мужчин. При увольнении призывников их суммарный оклад будет равномерно распределен между оставшимися инженерами и техниками в качестве надбавки. Вставить столбец НАДБ2. Ввести в него значение надбавки для инженеров и техников, остающихся в случае увольнения всех призывников.

8. Вставить столбец ПРИЗН3. Ввести для всех пенсионеров признак «пенсионер» (мужчины старше 60 лет, женщины старше 55 лет).

9. Вставить столбец НАДБЗ. Ввести в него надбавку в 10% от оклада для пенсионеров.

Специальная вставка

10. На листе Черновик заполнить числами по 10 ячеек в любых двух столбцах. Оформить ячейки. Используя команду Специальная вставка, подсчитать в свободных столбцах:

сумму ячеек двух столбцов; произведение ячеек двух столбцов; разность ячеек двух столбцов;

Используя команду Специальная вставка, скопировать информацию из разных слоев ячеек.

Функции даты и времени

11. Одному из листов рабочей книги Функции присвоить имя Даты.

12. В любую ячейку листа Даты ввести текущую дату, используя функцию категории Дата и время.

13. На листе Даты подсчитать:

количество дней, оставшихся до каникул; количество дней, прошедших от даты Вашего рождения; количество дней в веке; какое число будет через 100 дней.

Способ решения выберите самостоятельно

14. Определить среднее значение оклада для каждого отдела.

15. Определить среднее значение оклада отдельно для мужчин и женщин в каждом отделе.

16. Определить максимальное и минимальное значение оклада для мужчин и женщин в организации.

17. Вывести список трех самых молодых сотрудников организации.

18. Вывести список из 5 самых высокооплачиваемых сотрудников.

19. Создать список сотрудников 1 и 2 отделов с окладом более 200$.

20. Создать список инженеров и техников со стажем работы более 5 лет.

21. Определить год рождения самого пожилого юриста в 1 отделе.

22. Определить количество сотрудников с окладом от 200$ до 250$.

23. Найти количество сотрудников в 1 отделе с окладом от 200$ до 250$.