Табличный процессор MS Excel. Условное форматирование

Задача 1. (10 баллов) Штатное расписание

1. На одном листе рабочей книги составить таблицу штатного расписания некой организации в соответствии с приведенным рисунком 1.

Рис.1

2. На втором листе составить таблицу вычисления заработной платы в соответствии с рисунком 2.

В данной таблице столбец " Итого " заполняется как сумма полей " Оклад ", " Доплата 1 " и " Доплата 2 ". Столбец " Бонус " необходим для начисления заработной платы.

Рис.2

3. Заполнить таблицу рис.1 исходными данными, которых должно быть не менее 20. При этом учесть, что заработная плата начисляется в зависимости от должности (поле " Итого " таблица рис.2) и суммы " Бонуса ", умноженного на заданный " Минимум " (=ИНДЕКС(Зарплата!$E$3:$E$9;ПОИСКПОЗ(Штат!G3;Зарплата!$A$3:$A$9;0))+Зарплата!$I$3*ПРОСМОТР(Штат!H3;Зарплата!$G$3:$G$12))

4. Скопировать исходную таблицу (рис.1). Провести фильтрацию данных:

А) Список сотрудников по заданной должности

Б) Список однофамильцев (если нет данных в таблице, то добавить их_

В) Список сотрудников, старше заданного возраста

Г) Список сотрудников, получающих заработную плату в интервале от 3500 руб. до 7200 руб.

5. Скопировать исходную таблицу (рис.1). Провести сортировку данных:

А) Алфавитный порядок по фамилии

Б) Обратный хронологический порядок по возрасту

6. Скопировать исходную таблицу. Провести условное форматирование ячеек по заданному критерию:

А) Выделить синим цветом ячейки, содержащие заработную плату менее 2000 руб., зеленым цветом – более 2000 руб., но мене 6000, красным цветом – более 6000 руб.

Например, таблица может выглядеть так:

Рис.3

Задача 2.. (10 баллов) Задача о рациональном питании

Ферма производит откорм скота с коммерческой целью. Для простоты допустим, что имеется всего четыре вида продуктов: П1, П2, П3, П4; стоимость единицы каждого продукта равна соответственно С1, С2, С3, С4. Из этих продуктов требуется составить пищевой рацион, который должен содержать: белков – не менее b1 единиц; углеводов – не менее b2 единиц; жиров – не менее b3 единиц. Для продуктов П1, П2, П3, П4 содержание белков, углеводов и жиров (в единицах на единицу продукта) известно и задано в таблице, где a_ij (i=1,2,3,4; j=1,2,3) – какие–то определённые числа; первый индекс указывает номер продукта, второй – номер элемента (белки, углеводы, жиры). |продукт | элементы || |белки |углеводы |жиры ||П1 |A11 |A12 |A13 ||П2 |A21 |A22 |A23 ||П3 |A31 |A32 |A33 ||П4 |A41 |A42 |A43 |Требуется составить такой пищевой рацион (т.е. назначить количества продуктов П1, П2, П3, П4, входящих в него), чтобы условия по белкам, углеводам и жирам были выполнены и при этом стоимость рациона была минимальна. МАТЕМАТИЧЕСКАЯ МОДЕЛЬ. Обозначим x1, x2, x3, x4 количества продуктов П1, П2, П3, П4, входящих в рацион. Показатель эффективности, который требуется минимизировать, - стоимость рациона (обозначим её L): она линейно зависит от элементов решения x1, x2, x3, x4. Целевая функция: Система ограничений: a11x1+a21x2+a31x3+a41x4=b1 a12x1+a22x2+a32x3+a42x4=b2 a13x1+a23x2+a32x3+a43x4=b3Эти линейные неравенства представляют собой ограничения, накладываемые на элементы
решения x1, x2, x3, x4.Таким образом, поставленная задача сводится к следующей: найти такие неотрицательные значения переменных x1, x2, x3, x4, чтобы они удовлетворяли ограничениям – неравенствам и одновременно обращали в минимум линейную функцию этих переменных: Требуется спроектировать решение задачи в табличном процессоре Excel, на примере конкретных продуктов.

Задача 3.. (5 баллов) Выпуск изделий

Предприятие выпускает 3 вида изделий. Для выпуска единицы изделия необходимо сырье в количестве 3 кг для 1-го вида, 8 кг для 2-го вида и 1 кг для 3-го вида. Общий запас сырья составляет 9500 кг. Изделия по видам входят в комплект в количестве 2, 1 и 5 штук соответственно. Определить оптимальное количество выпуска изделий, при котором количество комплектов будет максимальным. Комплекты немедленно отправляются потребителю. Склад вмещает не более 20 штук лишних изделий 2-го вида.

Задача 4. (5 баллов) Цирк

Рассчитайте еженедельную выручку цирка, если известно:
- количество проданных билетов каждый день
- цена взрослого билета - 15 руб.
Постройте диаграмму (график) ежедневной выручки цирка:

А) за неделю

Б) за месяц

В) за квартал. Найдите максимальную и минимальную выручку за квартал.

Для заполнения исходных данных можно воспользоваться функцией СЛЧИС(), которая определяет случайное число в диапазоне . Чтобы найти случайное число на отрезке [ a, b ], надо спроектировать отрезок [0,1) на отрезок [ a, b ]. Для этого составляется формула:

Задача 5. (5 баллов) Зоопарк

Рассчитайте еженедельную выручку зоопарка, если известно:

- количество проданных билетов каждый день

- цена взрослого билета - 15 руб.

- цена детского на 30% дешевле, чем взрослого.

Постройте диаграмму (график) ежедневной выручки зоопарка:

А) за неделю

Б) за месяц

В) за квартал, найдите максимальную и минимальную выручку за квартал.

Для заполнения исходных данных можно воспользоваться функцией СЛЧИС().

Задача 6. (5 баллов)

Подготовьте бланк заказа для магазина, если известно:

- продукты(хлеб, мука, макаронные изделия и т.д., не менее 10 наименований)

- цена каждого продукта

- количество заказанного каждого продукта

Рассчитайте на какую сумму заказано продуктов. Усовершенствуйте бланк заказа, добавив скидку (например 10%), если стоимость купленных продуктов будет более 5000 руб. Постройте диаграмму (гистограмму) стоимости заказанного товара.

Задача 7. (5 баллов)

Цена на хлеб составляет N руб. за булку хлеба. Прогнозируемая инфляция составляет 0,3% в месяц. Вычислите сколько средств (в руб.) тратит семья на покупку хлеба в год, если ежедневно семья покупает 2 булки хлеба. Построить диаграмму (график) зависимости цены хлеба по месяцам.

Задача 8. (5 баллов)

Цена 1 куб. метра леса в январе - N долларов. Прогнозируемая инфляция составляет 17% в год. Рассчитайте стоимость (в руб., при этом курс доллара m -рублей/$) 1 куб. метра в конце года (декабрь). Постройте диаграмму (график) цены 1 куб. метра (в руб.) по месяцам.