Лабораторная работа №3. Построение диаграмм

1. Создайте новый файл Excel «Работа с диаграммами».

2. Создайте диаграммы по предложенным ниже образцам:

Лабораторная работа №4. Работа с функциями.

Откройте файл Лабораторные работы по Excel.xls

Задание 1.

1. На новом листе создайте таблицу «Ведомость успеваемости студентов»

2. Заполните таблицу для студентов своей группы.

3. На пересечении столбцов по итогам сессии и строк для каждого студента, определите является студент отличником или хорошистом и т.д. (используйте функцию ЕСЛИ).

4. На пересечении строки Всего и столбцов по итогам сессии, подсчитайте количество отличников, хорошистов и т.д. (используйте функцию СЧЕТЕСЛИ).

5. Назначьте студентам, которые учатся на 4 и 5 стипендию в размере 2500 руб. Если студент учится на все пятерки, то его стипендия увеличивается на 50%, если у студента одна четверка, а остальные пятерки, то его стипендия увеличивается на 25%.

6. Выполните условное форматирование: выделите красным цветом фамилии студентов, имеющих задолженности и зеленым цветом – отличников.

7. Постройте диаграмму, показывающую оценки каждого студента.

Задание 2.

1. Перейдите на лист Расчетно-платежная ведомость. Добавьте столбцы: Количество оставленных детей и Алименты. Используя функцию Если, начислите алименты сотрудникам. По существующему законодательству за 1 оставленного ребенка платится 25% от оклада без подоходного налога, за 2-х –33%, за 3-х и более – 50%.

2. Внесите исправления в столбец К выдаче.

Задание 3.

1. На новом листе создайте таблицу «Прием на работу», переименуйте лист:

3. Заполнить последнюю колонку словами «принять» или «отказать», используя функции Если, Или, И, зная, что условия приема на работу следующие:

- возраст от 25 до 40 лет

- язык – английский или немецкий

- образование, вуз – ВГУ, МГИМО

- специальность – международные отношения

- стаж работы не менее 3 лет

- владение компьютером

4. Оформите таблицу, используя Автоформат.

Пояснения к выполнению лабораторной работы №4.

Работа с функциями

Таблица «Ведомость успеваемости студентов»

Рассмотрим расчет для первого студента. Для остальных студентов формулы копируются с помощью Автозаполнения.

1. Ячейка G5 (средний балл студента) содержит формулу: =СРЗНАЧ(C5:F5), где C5:F5 – диапазон ячеек, содержащий оценки для первого студента.

2. Ячейка H5 (проверяем является ли студент отличником) содержит формулу: =ЕСЛИ(G5=5;"отличник";" "). Если у студента сессия сдана на все пятерки, то его средний балл равен пяти.

3. Ячейка I5 (проверяем является ли студент хорошистом) содержит формулу: =ЕСЛИ(МИН(C5:F5)=4;"хорошист";" "). Если у студента сессия сдана без троек, то его минимальная оценка – четверка. Аналогично рассчитываются ячейки G5 и К5.

4. Ячейка H9 (вычисляем число отличников) содержит формулу: =СЧЕТЕСЛИ(Н5:Н8;«отличник»)

Аналогично рассчитываются ячейки I8:К8.

5. Ячейка Р5 (стипендия) содержит формулу:

=ЕСЛИ(G5=5;$D$11+0,5*$D$11;

ЕСЛИ(СЧЁТЕСЛИ(C5:F5;4)=1;$D$11+0,25*$D$11;

ЕСЛИ(МИН(C5:F5)=4;$D$11;0)))

Ячейка D11 содержит значение стипендии.

6. Условное форматирование

Выделите ячейку В5 (ФИО первого студента) ® выполните Формат ® Условное форматирование ® сформируйте условия следующим образом:

Обратите внимание, формула также начинается со знака «=»

® нажмите кнопку А также >> ® в появившемся окне сформируйте условия следующим образом:

® нажмите кнопку ОК ® выделите все ФИО студентов® выполните Формат ® Условное форматирование ® поменяйте ссылки ячеек с абсолютных на относительные® нажмите кнопку ОК.

Для удаления условного форматирования выполните Правка ® Очистить ® Форматы.

Таблица «Расчетно-платежная ведомость»

Добавьте два новых столбца I – «Число оставленных детей», J – Алименты. Формула для первого работника имеет вид:

=ЕСЛИ(I5=0;0;ЕСЛИ(I5=1;0,25*(D5-H5);

ЕСЛИ(I5=2;0,33*(D5-H5);(D5-H5)*0,5))),

где I5 – число детей, на которых выплачиваются алименты; D5 – оклад работника, Н5 – величина подоходного налога работника.

Таблица «Прием на работу»

1. Для приема на работу нового сотрудника требуется одновременное выполнение некоторых условий (стаж, образование и т.д.). Для формирования логического выражения функции ЕСЛИ используем функцию И, которая дает результат ИСТИНА, если все ее аргументы имеют значение ИСТИНА, т.е. если будут выполнены все условия. Так как знание языка и образование предполагает выбор (одно из двух, например, английский или немецкий), то применим функцию ИЛИ, которая дает результат ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА и результат ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Формула для первого претендента на получение работы будет иметь вид:

=ЕСЛИ(И(C4>=25;C4<=40;

ИЛИ(D4="английский";D4="немецкий");

ИЛИ(E4="ВГУ";E4="МГИМО");F4="МО";G4>=3;H4="да");

"принять";"отказать")

где С4 – ячейка, содержащая возраст первого претендента; D4 – знание иностранного языка; Е4 – образование; F4 – специальность; G4 – стаж работы; Н4 – владение компьютером.

2. Для оформления таблицы можно использовать Автоформат, для этого выполните: Формат ® Автоформат ® выберите оформление таблицы, нажмите кнопку ОК.