Критерии оценки лабораторной работы

Задание 1. Анализ продаж

1. Переименуйте Лист2, присвоив ему имя«Задание2»;

2. Оформите таблицу по образцу;

3. Произведите расчеты в графе «Сумма» по формуле: Сумма = Цена * Количество;

4. Вычислите результаты в ячейках Е11, Е13, Е14.

Задание 2. Финансовая сводка за неделю

1. Переименуйте Лист1, присвоив ему имя «Задание1»;

2. Оформите таблицу по образцу;

3. Произведите расчеты в графе «Финансовый результат» по следующей формуле: Финансовый результат = Доход – Расход;

4. Для ячеек с результатом расчетов задайте формат «Денежный» с выделением отрицательных чисел красным цветом;

5. Рассчитайте средние значения Дохода, Расхода и Финансового результата;

6. В ячейке D13 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат»);

7. Выполните заливку цветом ячейки D13;

8. Постройте линейчатую диаграмму изменения финансовых результатов по дням недели;

9. Произведите фильтрацию значений дохода, превышающих 4200 руб.

Задание 3. Ведомость учета брака

1. Переименуйте Лист3, присвоив ему имя «Задание3»;

2. Оформите таблицу по образцу;

3. Произведите расчеты в графе «Сумма брака» по следующей формуле: Сумма брака = Процент брака * Сумма зарплаты;

4. Вычислите минимальную, максимальную и среднюю сумму брака, а также средний процент брака;

5. Произведите фильтрацию данных по условию: Процент брака < 9 %;

6. Постройте график отфильтрованных значений изменения суммы брака по фамииям.

Задание 4. Анализ продаж продукции

1. Добавьте Лист4 и переименуйте, присвоив ему имя «Задание4»;

2. Оформите таблицу по образцу;

3. Произведите расчеты в графе «Всего» по следующей формуле: Всего = Безналичные платежи + Наличные платежи; в графе «Выручка от продажи» по следующей формуле: Выручка от продажи = Цена*Всего.

4. Ввчислите минимальную и максимальную продажу (количество и сумму);

5. Произведите фильтрацию по цене, превышающей 9300 руб.;

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

Задание 5. Создание многостраничной таблицы

1. Добавьте Лист5 и переименуйте, присвоив ему имя «Зарплата за январь»;

2. Создайте таблицу по образцу;

3. Произведите вычисления по формулам:

a. Премия = Оклад * %Премии (зафиксируйте значение ячейки, используя клавишу F4);

b. Всего начислено =Оклад + Премия;

c. Удержания = Всего начислено * % Удержаний (зафиксируйте значение ячейки, используя клавишу F4);

d. К выдаче = Всего начислено – Удержания;

4. Рассчитайте Итоги по столбцам;

5. Рассчитайте максимальный, минимальный и средний доход по данным колонки «К выдаче»;

6. Добавьте Лист6 и скопируйте содержимое листа «Зарплата за январь» на Лист 6;

7. Присвойте Листу6 имя «Зарплата за февраль»;

8. Исправьте название месяца в названии таблицы;

9. Измените значение Премии на 24%, убедитесь, что программа произвела пересчет формул;

10. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата»;

11. Рассчитайте значение доплаты по формуле: Доплата = Оклад * %Доплаты. Значение доплаты примите равным 5%;

12. Измените формулу для расчета значений колонки «Всего начислено»: Всего начислено = Оклад + Премия + Доплата. Скопируйте формулу вниз по столбцу.

13. Проведите условное форматирование значений колонки «К выдаче» на листе «Зарплата за январь» (используйте команду ФорматàУсловное форматирование):

a. Выделите ячейки G4:G9;

b. Установите формат вывода значений между 3000 и 5000 - зеленым цветом шрифта, меньше или равно 3000 – красным цветом шрифта, больше или равно 5000 – синим цветом шрифта;

14. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию;

15. Защитите лист «Зарплата за январь» от изменений:

a. Задайте пароль на лист,

b. Сделайте подтверждение пароля;

c. Убедитесь, что лист защищен и невозможно удаление данных.

d. Снимите защиту листа.

16. Выполните условное форматирование оклада и премии за февраль месяц:

a. до 3000 – желтым цветом заливки;

b. от 3000 до 6000 – зеленым цветом заливки;

c. свыше 6000 – малиновым цветом заливки, белым цветом шрифта.

17. Защитите лист «Зарплата за февраль» от изменений. Проверьте защиту. Убедитесь в неизменяемости данных. Снимите защиту со всех листов электронной книги «Зарплата».

18. Постройте круговую диаграмму начисленной «Суммы к выдаче» всех сотрудников за февраль месяц.

Задание 6. Комплексное задание по электронным таблицам

1. Добавьте к имеющимся листам еще четыре листа;

2. Переименуйте листы следующим образом:

a. Лист 7 – Выручка;

b. Лист 8 – Себестоимость;

c. Лист 9 – График;

d. Лист 10 – Прибыль;

3. На лист «Выручка » занесите и оформите таблицу;

4. Произведите следующие расчеты: в колонке «Всего за 6 лет» – суммирование по строке;

в колонке «Доля в общей выручке» – вычисление доли каждого магазина от общей выручке в процентах; в колонке «Ранг» – расстановка магазинов по местам с помощью функции РАНГ;

5. Используя функции ЕСЛИ и И, поместите в колонку «Тенденция за последние 3 года» следующие слова:

a. Стабильный рост – если выручка за шестой год больше выручки за пятый год, а выручка пятого года больше, чем четвертого;

b. Стабильное снижение – если выручка за шестой год меньше выручки за пятый год, а выручка пятого года меньше, чем четвертого;

c. Неизменное состояние – если на протяжении последних трех лет неизменна;

d. Нестабильные изменения – во всех остальных случаях;

6. На лист «Себестоимость» занесите и оформите следующую таблицу:

7. Произведите следующие расчеты: в колонке «Всего за 6 лет» – суммирование по строке;

в колонке «Среднегодовая себестоимость» – вычислите среднее значение; в столбцах «Максимальная себестоимость» и «Минимальная себестоимость» используйте функцию МАКС и МИН; в строке «Итого» - просуммируйте соответствующие столбцы;

8. На листе «График»постройте график выручки по годам во всех магазинах;

9. На лист «Прибыль»занесите и оформите третью таблицу:

10. Произведите следующие расчеты: Прибыль=Выручка–Себестоимость, «Всего за 6 лет» и «Долю» рассчитывайте аналогично первой таблице;

11. На этом же листе расположите две диаграммы:

a. круговую диаграмму – отражающую долю магазинов в общей прибыли,

b. объемную нормированную гистограмму для отражения прибыли магазинов по годам.

Задание 7. Расчет заработной платы, использование функции ВПР

1. Добавьте Лист11 и переименуйте, присвоив ему имя «Ставка ЕТС»;

2.Оформите таблицу по образцу;

3. Рассчитайте оклад по формуле Оклад=мин.оклад*коэффициент;

4. Выделите блок A3:C6 и присвойте ему имя «etc»;

5. Добавьте Лист12 и переименуйте, присвоив ему имя «Зарплата»;

6. Создайте таблицу по образцу;

7. Используя функцию ВПР, рассчитайте столбец «Оклад»;

8. Вычислите премию по формуле Премия=Оклад*Процент премии, з арплатупо формуле Зарплата=Оклад+Премия;

9. Скопируйте лист «Зарплата» два раза (назовите новые листы соответственно «Зарплата1» и «Зарплата2;

10. Используя команду Итоги, на листе «Зарплата1» вычислите суммарную зарплату по каждому разряду, на листе «Зарплата2» вычислите среднюю зарплату по каждой должности;

11. На листе «Зарплата» постройте гистограмму, отражающую зарплату каждого сотрудника и круговую диаграмму, отражающую долю каждого сотрудника в процентах относительно общей суммы зарплаты.

Задание 8. Использование смешанной адресации

1. Добавьте Лист13 и переименуйте, присвоив ему имя «Аварии»;

2. На трех заводах происходят аварии, создайте таблицу по образцу;

3. Сосчитайте суммарное количество аварий по предприятиям и по годам;

4. Скопируйте диапазон А1:Е6 в буфер обмена и вставьте сначала в ячейку А10, затем вставьте в ячейку А20;

5. В ячейках А10:Е14 разместите формулы для вычисления процента аварий, принимая за 100% число аварий за текущий год, используйте смешанную адресацию; на ячейки В11:Е14 наложите процентный формат;

40%

60%

100%

(например, строка для 2008 года будет выглядеть так)

6. В ячейках А20:D25 разместите формулы для вычисления процента аварий, принимая за 100% суммарное число аварий по предприятию; на ячейки В21:D25 наложите процентный формат;

7. Постройте объемную трехмерную гистограмму, отражающую количество аварий по годам по предприятиям.

Задание 9. Вступительные экзамены

1. Добавьте Лист14 и переименуйте, присвоив ему имя «Экзамены»;

2. Создайте таблицу по образцу; добавьте в таблицу еще 10 студентов и проставьте им тестовые баллы, но не более чем 20 баллов за тест;

3. Вычислите суммарное количество баллов для каждого студента в столбце «Итого»;

4. Вычислите значение в столбце «Оценка1», используя функцию ЕСЛИ. Оценки проставляются в соответствии со шкалой: меньше 18 – «2», от 18 до 32 – «3», от 33 до 48 – «4», больше 49 – «5»;

5. Отсортируйте таблицу по убыванию оценки, а в пределах одинаковой оценки – по фамилиям по алфавиту;

6. Вычислите значение в столбце «Оценка2 » с помощью функции ВПР, используйте вспомогательную таблицу;

7. Используя функцию РАНГ, в колонке «Ранг» расставьте студентов по местам в соответствии с набранными баллами;

8. Для студентов, получивших оценку 5, постройте обычную гистограмму на листе «Экзамены»: по оси категорий – фамилии, для каждого студента четыре столбика - для тестов и для суммы, над каждым столбиком количество набранных баллов;

9. Для студентов, получившего оценку 4, постройте гистограмму с накоплением листе «Экзамены»;

10. Использую команду Итоги, вычислите количество оценок каждого из достоинств;

11. Постройте на листе «Экзамены» круговую диаграмму, отражающую в процентном отношении количество оценок каждого вида.

Задание 10. Ставка подоходного налога

1. Добавьте Лист15 и переименуйте, присвоив ему имя «Налог»;

2. Создайте таблицу по образцу;

3. Рассчитайте подоходный налог в соответствии с алгоритмом, представленном в таблице;

Размер облагаемого совокупного дохода, полученного в 2010 году	Сумма налога
до 20000 р.	12%
от 20001 р. до 40000 р.	2400 р. + 15% с суммы, превышающей 20000 р.
от 40001 р. до 60000 р.	5400 р. + 20% с суммы, превышающей 40000 р.
от 60001 р. до 80000 р.	9400 р. + 25% с суммы, превышающей 60000 р.
от 80001 р. до 100000 р.	14400 р. + 30% с суммы, превышающей 80000 р.
от 100001 р.	20400 р. + 35% с суммы, превышающей 100000 р.

4. Расчет можно производить при помощи функции ЕСЛИ или при помощи функции ВПР, тогда используйте вспомогательную таблицу.

		12%
		15%
		20%
		25%
		30%
		35%

Критерии оценки лабораторной работы

(мах – 40 баллов)

Задание 1. Анализ продаж – 1 балл, в том числе:

- использование формул, форматирование таблицы, применение форматов данных – 1 балл.

Задание 2. Финансовая сводка за неделю – 3 балла, в том числе: