Задание 4.3.6.

1. Создать таблицу, содержащую сведения о сотрудниках предприятия. Названия колонок: «Фамилия», «Должность», «Отдел», «Дата поступления на работу», «Стаж работы», «Зарплата», «Надбавка», «Премия», «Всего начислено», «Пенсионный фонд», «Налогооблагаемая база», «Налог», «Выплатить». Колонки «Фамилия», «Должность», «Отдел», «Дата поступления на работу», «Зарплата» заполнить произвольными данными, при этом в колонке «Зарплата» использовать значения от 3000 до 10 000 руб., в колонке «Должность» использовать 5-6 названий, в колонке «Отдел» использовать 3-4 названия. Значения в остальных колонках рассчитать по формулам:

Стаж работы= (Текущая дата – Дата поступления на работу)/365. Результат округлить до целого.

0, если стаж работы меньше 5 лет;

Надбавка = 5% от зарплаты, если стаж работы от 5 до 10 лет;

10% от зарплаты, если стаж работы больше 10 лет.

Премия =20%(Зарплата + Надбавка).

Всего начислено = Зарплата + Надбавка + Премия.

Пенсионный фонд = 1% от «Всего начислено».

Налогооблагаемая база = Всего начислено – Пенсионный фонд.

12% от Налогооблагаемой базы, если она меньше 1000 руб.;

Налог = 20% от Налогооблагаемой базы, если она больше 1000 руб.

 

Выплатить = Всего начислено – Пенсионный фонд – Налог.

В таблице должно быть не менее 10 строк.

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

3. Присвоить рабочему листу имя Сведения о сотрудниках. Это же название можно использовать в качестве заголовка таблицы.

4. Используя форму данных, добавить в список еще 10 записей.

5. Используя форму данных, выполнить поиск записей по следующим критериям: заданная должность; заданный отдел; стаж работы больше заданного; заданная должность и зарплата меньше заданной; заданный отдел и стаж работы больше заданного.

6. Выполнить сортировку данных по: отделам; фамилиям; отделам и фамилиям; отделам, должностям и фамилиям; отделам, должностям, зарплатам, фамилиям.

7. Используя Автофильтр, отобрать данные о сотрудниках: фамилии которые начинаются на заданную букву; зарплата которых больше заданной; стаж работы которых находится в заданном диапазоне; с заданной должностью и зарплатой в заданном диапазоне.

8. Используя расширенный фильтр, отобрать данные о сотрудниках:

· с зарплатой от 3000 до 4000 руб.;

· со стажем работы меньше 7 лет или с зарплатой меньше 4000 руб.;

· со стажем работы от 5 до 10 лет и с зарплатой от 4000 руб. до 5000 руб.;

· зарплата которых выше средней (СРЗНАЧ);

· зарплата которых выше средней, а стаж работы от 5 до 15 лет.

9. Создать сводную таблицу суммарных выплат по отделам, внутри отделов – по фамилиям. Для этого в макете поместить поля «Отдел» и «Фамилия» в область «Строка», а поле «Выплатить» - в область «Данные».

10. Скопировать лист со сводной таблицей. Внести изменения в копию, чтобы данные по каждому отделу выводились на отдельной странице (для этого поле «Отдел» переместить в область «Страница»).

11. Создать еще одну копию первой сводной таблицы. Изменить копию так, чтобы подчитывалось количество сотрудников в отделах (для этого удалить из области «Данные» поле «Выплатить» и поместить туда поле «Фамилия»).

 

4.4. Дополнительные упражнения и задания*

Упражнение 1. Создание и анализ графика .

1) Ключевой момент: табличное задание функции. Необходимо выбрать диапазон (например, от 0^о до 180^о) и шаг измерения аргумента (например, ∆x=5^о). (Лучше ∆x=10^о, т.е. от 0^о до 360^о, в соседних столбцах результат пересчитается автоматически).

I способ: Правка/Заполнить/Прогрессия (арифметическая, по столбцам, шаг 5, предельное значение 180).

II способ: Ввести в ячейку В4 нулевое значение, в ячейку В5 – значение 5, выделить обе ячейки, «ухватитесь» мышью за маркер заполнения (в уголке черный крестик) и, не отпуская левой кнопки мыши, проведите указатель до тех пор вниз, пока около указателя не появится число 180 (т.е. В40). Отпустите кнопку мыши, табл. аргументов будет построена.

2) Построение значения функции (аргумент задается в радианах, а не в градусах).

I способ: В ячейку С4 ввести значение в градусах * .

II способ: Воспользоваться соответствующей функцией Excel (f_e).

− Выделить ячейку С4 и ввести латинскими буквами и с помощью функций =sin(радианы(2*В4)). Нажать Enter. (В С4 должен быть 0).

− Возвратиться в ячейку С4, и маркером заполнения заполнить таблицу аргументов (т.е. до С40). (Должны быть числа).

3) Заполнить столбец значений функции , также не забывая про радианы и ссылки на ячейку В4: (D4: D40).

=0,5*(sin(радианы(В4))+cos(радианы(В4))). Нажать Enter. (D4=0,5; D40=-0,5).

4) Строим график функции .

− Выделить диапазон ячеек С4:С40 /Запустить Мастер диаграмм /Вкладка нестандартные / Гладкие графики / Далее.

− Появляется Источник данных диаграммы /уточняем Диапазон данных (должно быть $С4:$С40).

− Переход на вкладку Ряды.

− Имена по столбцам не давали, это поле оставить свободным.

− В поле Ряд нажмем кнопку Добавить (справа), чтобы одновременно свернуть диалоговое окно (красн. ↑).

− Проводим мышью при нажатой левой кнопке по столбцу D4:D40 и возвращаемся назад (красная ↑).

− Далее такую операцию проделываем с полем ввода Подписи оси Х, иначе по оси Х будут откладываться неверные данные (В4:В40).

− Нажмите Далее / Параметры диаграммы / Заголовок диаграммы (ввести).

− Название осей координат (не обязательно).

− Установить основные линии сетки.

− В подписях данных установим «значения».

(!) Если таблица данных небольшая, можно разместить её под графиком, если таблица большая, лучше представить ее а отдельном листе.. Нажав «Далее», разместите диаграмму на отдельном листе.

5) Отделка диаграммы (по желанию).

1 способ: Вызвать диалоговое окно форматирования.

(!) Вернуться назад, нажать внизу Лист 1 (2 и т.д.) <=> диаграмма 1 (2 и т.д.)

2 способ: Двойной щелчок левой кнопки мыши на форматируемом элементе или одинарный щелчок на этом элементе правой кнопки, затем выбрать пункт контекстного меню.

6) Если предполагается распечатка на ч/б принтере, необходимо убрать с диаграммы все цветные элементы (можно использовать сплошные и пунктирные линии и т.д.)

Упражнение 2. Диаграмма типа «Поверхность»

График 1. Построить полусферу в изометрической проекции по формуле x²+y²+z²=16.

Способ

1) В ячейку В3 ввести число -4; в ячейку В4 ввести -3,5 (т.е. шаг = 0,5). Выделить ячейки В3 и В4 и «потянуть» вниз за маркер заполнения до ячейки В19 (т.е. ввести числа от -4 до 4 с х=0,5), аналогично в С2 ввести число -4, в D2 ввести -3,5 и заполнить ряд таблицы (вправо) до S2 (конечное значение 4).

В ячейку С3 ввести формулу: =корень(16–$B3^2–C$2^2) (!) [Знак $- указывает на абсолютную ссылку].

2) Получив матрицу 17´17, удалите из нее все отрицательные данные (Excel сама указывает эти ячейки, выдав в них сообщение об ошибке).

3) Построение диаграммы: Мастер диаграмм/Поверхность (Тип диаграммы)/Ok (рис.5.6).

 

Способ

1)В ячейку В3 ввести число -4; в ячейку В4 ввести -3,5 (т.е. шаг = 0,5). Выделить ячейки В3 и В4 и «потянуть» вниз за маркер заполнения до ячейки В19 (т.е. ввести числа от -4 до 4 с х=0,5).

2) В ячейку С2 ввести -4, в ячейку С3 ввести формулу =корень(16–B3^2–$C$2^2) и распространить эту формулу вниз до ячейки В19. Далее в ячейках В3:В19 в расположенных там формулах необходимо поменять относительные адреса ячеек, на которые ссылается формула, на абсолютные, а абсолютные адреса на относительные. Это необходимо, чтобы при горизонтальном распространении формул ссылки в формулах на столбец B3:B19 не менялись.

Обмен ссылок: выделить ячейку, в которой необходимо поменять адреса, щелкнуть мышью в строке формул на адрес, подлежащий изменению, и нажимая несколько раз клавишу F4, изменить адрес на тот, который нужен и нажать Enter. Действие клавиши F4 – циклическое (4 нажатия – 1 цикл).

Рис. 4.6

Рис. 4.7

График 2. Если во всех ячейках к формуле добавить знак минус, то можно построить и нижнюю полусферу. Т.е. построенную таблицу скопировать в ячейки B20:S36, далее в скопированной части таблицы с помощью меню Правка/Заменить поменять знаки и дополнить этими данными построенную ранее диаграмму.

График 3. Интересные результаты получатся, если во всех формулах построения полусферы изменить число 16 на 30 или 40 (рис.5.7.)

 

Упражнение 3. График эллиптический параболоид (), при a =1 и b =1 и гиперболический параболоид ().

1) В ячейках A1 и B1 находятся параметры a и b соответственно (a =4, b =5). Область измерения x и y – квадрат [-5; 5]´ [-5; 5], шаг 0,5.

2) В ячейку В2 ввести число -5 и нажать Enter. Вернуться в ячейку В2. Выполнить команду Правка/Заполнение/Прогрессия (режим по строкам, прогрессия арифметическая, шаг 0,5, пред. значение -5)/ Ok или B2=-5, C2=-4,5 и заполнить маркером таблицу (вправо) до значения 5.

(Во 2-ой строке появится ряд значений от -5 до 5 с шагом 0,5)

3) Аналогично стать в ячейку А3 и проделать п.2 с тем отличием, что в окне диалога Прогрессия надо выбрать режим по столбцам или А3=-5, А4=-4,5 и заполнить маркером таблицу (вниз) до значения 5.

Рис. 4.8. Гиперболический параболоид

 

4) Далее в ячейку В3 ввести формулу: =(B$2/$A$1)^2–($A3/$B$1)^2. Распространить эту формулу на всю строку, расположенную под строкой со значениями переменной x. После этого не сбрасывая выделение, установите указатель мыши в точку в правом нижнем углу последней выделенной ячейки в 3-ей строке. Затем распространите выделенные в ячейках формулы на все строки вдоль заполненного значениями переменной y столбца А.

Постройте трехмерную диаграмму типа Поверхность по области А2:V23 на отдельном листе в ч/б варианте (оси, подписи и т.д.)

Рис. 4.9.Эллиптический параболоид

5) Для построения эллиптического параболоида область А2:V23 скопируйте на отдельный лист рабочей книги. На этом листе замените знак «–» на знак «+» при помощи Правка/Заменить.

 

Задание 4. В соответствии с вариантом создайте и проанализируйте графики функций:

1.cos2x=2(sinx+tgx)

2. sinx=1.5(cosx+ctgx)

3. tg2x=0.5(sin2x+cosx)

 

Задание 5. Постройте полусферу в изометрической проекции по формулам:

1. x²+y²+z²=9

2. x²+y²+z²=25

3. x²+y²+z²=64