Сумма всех зарплат по столбцу.
15. Отсортировать таблицу по возрастанию зарплаты.
16. Осуществить фильтрацию этой таблицы, выделив все фамилии медсестер, зарплаты которых меньше или равны 180000.
17. Скопировать получившуюся таблицу на лист 4, дать имя листу «Материальная помощь».
18. Добавить в эту таблицу 2 строки.
19. Добавить в эти строки текст (см. образец 3).
20. Сохранить все на своем диске.
21. Перейти на лист 5. Дать этому листу имя Дополнительная информация.
22. Создать на нем шапку таблицы по образцу 4.
23. Объединить столбцы «№», «Фамилия», «Зарплата», «Год рождения» и «Стаж работы» и построить новую таблицу, используя предыдущие.
Здесь:
Формула 1 = Зарплата_санитарки (в абсолютной адресации) *
* Коэффициент_А + Коэффициент_В
Формула 2 = Зарплата_Сотрудника * Количество_сотрудников
Формула 3 = Сумма зарплат всех сотрудников.
В. Доход фирмы
1. На листе 1 составить таблицу по предложенному образцу и отформатировать ее. При этом:
– номера п/п сформировать протаскиванием мыши;
– сумму дохода за январь сформировать как ряд (Правка – Заполнить –
Прогрессия: начальное значение =5000, шаг 100, всего – 8 фирм);
– список наименований месяцев сформировать протаскиванием мыши.
2. Сохранить ее на своем диске в своей папке под именем Доход фирмы.xls.
3. Переименовать лист 1 с этой таблицей, дать ему имя Табл. 1.
4. Скопировать эту таблицу в буфер обмена, перейти на лист 2 и вставить туда таблицу из буфера обмена.
5. Переименовать лист 2 с этой таблицей, дать ему имя Копия табл. 1.
6. Выделить столбцы «Фирма» и «Суммарный доход» и составить по этой информации диаграмму, поместив ее на новый лист, дав листу имя Диаграмма 1. Выделить всю таблицу и построить по ней график доходов фирм, поместив график на этот же лист.
7. Перейти на лист 3, дать ему имя Итоги.
8. Скопировать на нее таблицу с листа «Табл. 1».
9. Отсортировать таблицу по наименованиям фирм (по алфавиту).
10. Подвести промежуточные итоги по каждой фирме.
11. Перейти на лист 4, дать ему имя Фильтрация.
12. Скопировать на нее таблицу с листа «Табл. 1».
13. Отсортировать таблицу по наименованиям фирм (по алфавиту).
14. Осуществить фильтрацию этой таблицы, выделив все фирмы, годовой доход которых меньше или равны 18000.
15. Скопировать получившуюся таблицу на лист 5, дать имя листу Фирмы с небольшим доходом.
Здесь:
Формула 1 = доход фирмы за предыдущий месяц + доход за предыдущий месяц *0,1 (т. е. на 10 %);
Формула 2 = сумма дохода фирмы за указанный квартал;
Формула 3 = сумма дохода фирмы за год;
Формула 4 = суммарный доход фирмы в долларах;
Формула 5 = суммарный доход всех фирм за указанное время.
Г. Премирование
1. Построить таблицу по предложенному образцу.
2. Упорядочить таблицу, расположив фамилии в алфавитном порядке.
3. Вычислить значения «Стаж», «Пенсионер» и «Льготы» по предложенным формулам.
4. Построить круговую диаграмму для столбцов «ФИО» и «Стаж».
5. Построить гистограмму для столбцов «ФИО» и «Год рождения».
6. Подсчитать количество пенсионеров, используя команды Данные – Итоги.
7. Выделить с помощью Автофильтра всех сотрудников старше 40 лет.
8. С помощью буфера обмена скопировать таких сотрудников на другой лист и дать листу имя Старше 40 лет.
9. Вернуться на предыдущий лист и с помощью расширенного фильтра определить всех сотрудников, у которых возрастной год является юбилейным годом (то есть, кратен 5 или 10).
10. Используя буфер обмена, скопировать данный список на новый лист и дать этому листу имя Юбиляры.
11. Результат сохранить в файле Премирование.xls.
Образец 1 для задания Б
| Зарплата санитарки | ввести число | ||||
| Должность | Коэфф. А | Коэфф. В | Зарплата сотрудника | Количество сотрудников | Суммарная зарплата |
| Санитарка | 0,3 | Формула 1 | Формула 2 | ||
| Медсестра | 1,5 | 0,7 | Формула 1 | Формула 2 | |
| Врач | 1,5 | Формула 1 | Формула 2 | ||
| Зав.отделением | 1,8 | Формула 1 | Формула 2 | ||
| Зав.аптекой | 0,7 | Формула 1 | Формула 2 | ||
| Завхоз | 1,5 | 0,4 | Формула 1 | Формула 2 | |
| Главврач | Формула 1 | Формула 2 | |||
| Зав.больницей | 2,2 | Формула 1 | Формула 2 | ||
| Месячный фонд зарплаты Формула 3 |
Образец 2 для задания Б
| |
| |
| № | Фамилия | Коэффициент А | Коэффициент C | Зарплата | Подпись |
| 1,7 | Формула 4 | ||||
| Формула 4 | |||||
| 1,3 | Формула 4 | ||||
| … | 0,7 | Формула 4 | |||
| ИТОГО: | Формула 5 |
Образец 3 для задания Б
Председателю профкома
Больницы № 121
Просим оказать материальную помощь следующим сотрудникам больницы:
Образец 4 для задания Б
| Фамилия | Год рождения | Стаж |
Образец для задания В
| А | В | С | D | Е | … | N | О | Р | Q | R | S | ||
| Курс долл. | число | ||||||||||||
| № п/п | Фирма | Доход фирмы | |||||||||||
| Январь | Февраль | Март | ... | Декабрь | Итого за 1 квартал | Итого за 2 квартал | Итого за 3 квартал | Итого за 4 квартал | Итого за год | Итого за год в долл. | |||
| Эврика | Ввести любое число больше 5000 | Формула 1 | Формула 1 | Формула 1 | Формула 2 | Формула 2 | Формула 2 | Формула 2 | Формула 3 | Формула 4 | |||
| Анадема | Ввести любое число больше 5000 | Формула 1 | Формула 1 | Формула1 | Формула 2 | Формула 2 | Формула 2 | Формула 2 | Формула 3 | Формула 4 | |||
| Изумруд | Ввести любое число больше 5000 | Формула 1 | Формула 1 | Формула 1 | Формула 2 | Формула 2 | Формула 2 | Формула 2 | Формула 3 | Формула 4 | |||
| Авиценна | Ввести любое число больше 5000 | Формула 1 | Формула 1 | Формула 1 | Формула 2 | Формула 2 | Формула 2 | Формула 2 | Формула 3 | Формула 4 | |||
| Альянс | Ввести любое число больше 5000 | Формула 1 | Формула 1 | Формула 1 | Формула 2 | Формула 2 | Формула 2 | Формула 2 | Формула 3 | Формула 4 | |||
| Яхонт | Ввести любое число больше 5000 | Формула 1 | Формула 1 | Формула 1 | Формула 2 | Формула 2 | Формула 2 | Формула 2 | Формула 3 | Формула 4 | |||
| Барф | Ввести любое число больше 5000 | Формула 1 | Формула 1 | Формула 1 | Формула 2 | Формула 2 | Формула 2 | Формула 2 | Формула 3 | Формула 4 | |||
| Темп | Ввести любое число больше 5000 | Формула 1 | Формула 1 | Формула 1 | Формула 2 | Формула 2 | Формула 2 | Формула 2 | Формула 3 | Формула 4 | |||
| Итого | Формула 5 | Формула 5 | Формула 5 | Формула 5 | Формула 5 | Формула 5 | Формула 5 | Формула 5 | Формула 5 | ||||
Образец для задания Г
| A | B | C | D | E | F |
Таблица премиальных
| № | ФИО | Год рождения | Стаж работы | Пенсионер | Льготы | |
| Иванов | =2009-СЗ-20 | =ЕСЛИ (2009-СЗ)>60; «Пенсионный возраст»; «Не пенсионный возраст») | =ЕСЛИ(РЗ>20; «Начислить премию за стаж»; «») | |||
| Петров | =2009-С4-20 | =ЕСЛИ(2009-С4)>60; «Пенсионный возраст»; «Не пенсионный возраст») | =ЕСЛИ(Р4>20; «Начислить премию за стаж»; «») | |||
| Сидоров | =2009-С5-20 | =ЕСЛИ(2009-С5)>60; «Пенсионный возраст»; «Не пенсионный возраст») | =ЕСЛИ(О5>20; «Начислить премию за стаж»; «») | |||
| Валентинов | =2009-Сб-20 | =ЕСЛИ(2009-С6)>60; «Пенсионный возраст»; «Не пенсионный возраст») | =ЕСЛИ(О6>20; «Начислить премию за стаж»; «») | |||
| Александров | =2009-С7-20 | =ЕСЛИ(2009-С7)>60; «Пенсионный возраст»; «Не пенсионный возраст») | =ЕСЛИ(Р7>20; «Начислить премию за стаж»; «») | |||
| Евгеньев | =2009-С8-20 | =ЕСЛИ(2009-С8)>60; «Пенсионный возраст»; «Не пенсионный возраст») | =EGTIM(D8>20; «Начислить премию за стаж»; «») | |||
| Терехин | =2009-С9-20 | =ЕСЛИ(2009-С9)>60; «Пенсионный возраст»; «Не пенсионный возраст») | =ЕСЛИ(О9>20; «Начислить премию за стаж»; «») | |||
| Арбузов | =2009-С10-20 | =ЕСЛИ(2009-С10)>60; «Пенсионный возраст»; «Не пенсионный возраст») | =ЕСЛИ(Р10>20; «Начислить премию за стаж»;«») | |||
| Николаев | =2009-С11-20 | =ЕСЛИ(2009-С11)>60; «Пенсионный возраст»; «Не пенсионный возраст») | =ЕСЛИ(Р11>20; «Начислить премию за стаж»; «») | |||
| Григорьев | =2009-С12-20 | =ЕСЛИ(2009-С12)>60; «Пенсионный возраст»; «Не пенсионный возраст») | =ЕСЛИ(Р12>20; «Начислить премию за стаж»; «») |
Лабораторная работа № 9
МАКРОСЫ
Задания
Задание 1. Операции над ячейками:
Сформировать макросы, реализующие следующие действия:
1) изменить фон и обрамление выделенных ячеек;
2) в ячейках А1, В1, С1 заданы коэффициенты a, b, c квадратного уравнения (ax2 +bx+c=0); построить макрос для нахождения корней этого уравнения.
Задание 2. Создать таблицу по приведенному образцу:
Количество вредных веществ, ежегодно выбрасываемых в
атмосферу
| Город | Твердых (тыс. т) | Газообразных и жидких (тыс. т) | Всего (тыс. т) |
| Брест Полоцк Новополоцк Минск Могилев Бобруйск Орша Витебск Солигорск Лида Полоцк | 0,8 0,9 1,1 8,9 2,9 2,5 0,6 9,5 1,2 0,5 1,9 | 4,0 11,6 158,0 103,5 88,3 50,2 9,1 27,0 20,0 5,7 12,0 |
Используя данные таблицы, рассчитать суммарный выброс вредных веществ в атмосферу по каждому городу Беларуси (тыс. тонн в год).
Создать макрос, который выполняет следующие действия:
– форматирует таблицу (заголовок – 12 пт, Times New Roman, заливка – светло-серый цвет; города – синий цвет шрифта, 11 пт, Arial, выравнивание – по центру; остальные столбцы – 11 пт, Arial);
− сортирует поля таблицы по возрастанию поля Всего;
Макрос должен вызываться при нажатии комбинации клавиш CTRL+Я.
Построить график зависимости по полям Город и Всего.