Количество рабочих дней в месяце
Рассчитать показатели таблицы 2 и построить диаграммы.
Таблица 2 – Структура производимой продукции
Показатели | Значение | Удельный вес |
Производство молока | 1 200 000 | |
Производство мяса | 3 000 000 | |
Производство овощей | 500 000 | |
Производство зерна | 800 000 | |
Итого: |
ЗАДАНИЕ №2
Используя команду СОРТИРОВКА , упорядочить таблицу 3 по подразделениям, а внутри подразделений по алфавиту. Порядок действий: Данные – Сортировка – Сортировать по «Подразделение» – Добавить уровень – Сортировать по «Фамилия, имя, отчество».
Проверить работу команды Фильтр. Получить итоги по подразделениям и предприятию в целом. Порядок работы: Данные – Структура - Промежуточные итоги.
На примере объекта исследования, выбранного для выполнения выпускной квалификационной работы, составьте собственный пример, в котором уровней сортировки было бы больше 3-х.
Таблица 3 – Ведомость оплаты по бригадам
Подразделение | Фамилия, имя, отчество | Сумма |
Бригада 1 | Иванов | 2000 |
Бригада 2 | Петров | 1000 |
Бригада 1 | Антонов | 500 |
Бригада 3 | Семенов | 800 |
Бригада 2 | Николаев | 900 |
Бригада 3 | Павлов | 1200 |
Бригада 1 | Андреев | 200 |
Бригада 2 | Михайлов | 400 |
ЛАБОРАТОРНАЯ РАБОТА №2
ЗАДАНИЕ №1
Работа с функцией КОНСОЛИДАЦИЯ.
Ваша задача заключается в том, чтобы создать в разных листах книги три, одинаковых по форме, но не обязательно полностью совпадающих по содержанию, таблицы. Листы носят название ЯНВАРЬ, ФЕВРАЛЬ, МАРТ.
Таблица 4 – Сумма выданных авансов за месяц
Фамилия, имя, отчество | Сумма |
Иванов | 2690 |
Петров | 400 |
Сидоров | 2000 |
Семенов | 1000 |
В них содержатся данные за различные месяцы первого квартала. Используя функцию КОНСОЛИДАЦИЯ из пункта меню ДАННЫЕ, требуется подсчитать итоги за три месяца и поместить их в лист КВАРТАЛ.
Порядок работы:
1. Создать листы Январь, Февраль, Март, Квартал.
2. В листы месяцев поместить исходные таблицы по образцу (табл. 4). При выполнении команды текущим листом должен стать лист КВАРТАЛ. Установить курсор в нужную позицию листа.
3. Выполнить команду КОНСОЛИДАЦИЯ из пункта меню ДАННЫЕ. Выбрать в качестве операции Сумма.
4. Установит курсор в позицию Ссылка, открыть лист ЯНВАРЬ и выделить диапазон для консолидации. Добавить данный диапазон. Аналогично добавить ФЕВРАЛЬ и МАРТ.
5. Для того, чтобы в консолидированной таблице появились названия граф и столбцы с текстовой информацией, необходимо активизировать в окне КОНСОЛИДАЦИЯ позиции Подписи верхней строки и Значения левого столбца.
ЗАДАНИЕ №2
Работа с функцией ПОДБОР ПАРАМЕТРА.
Предположим, у Вас есть определенная сумма денег, которую Вы хотите поместить под определенный процент в некий банк. Срок, на который Вы хотите разместить свой вклад, должен превышать 1 год.
Сумма вклада (СВК) – 1000
Процентная ставка (ПС) – 20%
Срок (С) – 3 года
Расчет суммы выплаты (СВ) в конце срока происходит по формуле:
СВ=СВК*К,
где К – это коэффициент наращивания, определяемый по формуле: К=(1+ПС)^С.
Ввести данную информацию в виде таблицы 5.
Таблица 5 – Расчет процентов и сумм выплат по вкладу
Сумма вклада | 1000 |
Процентная ставка | 20% |
Срок | 3 |
Коэффициент наращивания | |
Сумма выплат |
Рассчитать недостающие показатели по формулам.
Допустим, вы получили в виде выплат некоторую сумму, которая Вас не устраивает. Вы хотите иметь в конце срока БОЛЬШУЮ сумму. Это возможно лишь при изменении какого-либо исходного параметра:
а) исходной суммы вклада;
б) процентной ставки;
в) срока хранения вклада.
Если Вам известна величина желаемой суммы, необходимо задать ее в качестве значения позиции Установить в ячейке команды ПОДБОР ПАРАМЕТРА.
В позиции Значение требуется указать желаемую сумму, а в позиции Изменяя – тот параметр, величину которого вы хотели бы узнать.
Порядок работы:
1. Создайте таблицу 5 и выполните необходимые расчеты.
2. Вызовите команду ПОДБОР ПАРАМЕТРА из пункта меню ДАННЫЕ – АНАЛИЗ «ЧТО ЕСЛИ».
3. Ответьте на вопросы в диалоговом окне, последовательно устанавливая в качестве изменяемой ячейки сумму, срок и процентную ставку. В качестве адреса ячейки, в которой необходимо установить значение, используйте адрес ячейки Сумма выплат. Значение этой ячейки примите любое, отличное от рассчитанного ранее.
ЗАДАНИЕ №3
Используя функцию ПОДБОР ПАРАМЕТРА, найдите корни квадратного уравнения (любого).
ЗАДАНИЕ №4
На примере объекта исследования, выбранного для выполнения выпускной квалификационной работы, составьте свой пример на использование функции ПОДБОР ПАРАМЕТРА.
ЗАДАНИЕ №5
На основании таблицы 5 создать таблицу 6:
Таблица 6 – Расчет суммы выплат при различных значениях сумм вклада и сроках вклада
Сумма вклада | 1000 | 1200 | 1 | 2 | 3 | 4 | 5 |
Процентная ставка | 20% | 1000 | |||||
Срок | 1 | 1500 | |||||
Коэффициент наращивания | 1,2 | 2000 | |||||
Сумма выплат | 1200 | 2500 | |||||
3000 | |||||||
3500 | |||||||
4000 | |||||||
4500 | |||||||
5000 | |||||||
10000 |
По строкам необходимо расположить суммы вклада от 1000 до 10000 рублей, а по столбцам – сроки от 1 до 5 лет. Можно рассчитать суммы выплат при 20%-ной процентной ставке в виде таблицы, где на пересечении строки и столбца получится значение суммы выплат при определенной величине суммы вклада и срока.
Порядок работы:
1. Создайте таблицу 6, начиная заполнение таблицы с адреса A1. Выполните в ней расчеты по подобию таблицы 5.
2. В клетку с адресом С1 запишите формулу расчета суммы выплат, аналогичную формуле в клетке В5. Выделите фрагмент таблицы, начиная от клетки С1 и заканчивая последней клеткой таблицы.
3. Выполните расчет всей таблицы, используя функцию ТАБЛИЦЫ ПОДСТАНОВКИ из пункта меню ДАННЫЕ – АНАЛИЗ «ЧТО ЕСЛИ» - ТАБЛИЦА ДАННЫХ.
4. В диалоговом окне ответьте на вопросы: Подставлять значения по столбцам в …Подставлять значения по строкам в …
При расчете имейте в виду, что по столбцам находятся значения сроков вклада, которые при расчете автоматически должны подставляться в клетку, которая была использована при расчете, т.е. в клетку В3. По строкам находятся значения суммы предполагаемого вклада, и при выполнении вычислений они автоматически будут подставляться в клетку, которая была использована при расчете суммы выплат, т.е. в клетку В1.
При работе с таблицами подстановки будьте внимательны. Нельзя изменять часть таблицы подстановки, и если Вы допустили ошибку, то необходимо удалить всю таблицу подстановки.
ЗАДАНИЕ №6
На примере объекта исследования, выбранного для выполнения выпускной квалификационной работы, составьте свой собственный пример создания таблиц, используя функцию ТАБЛИЦЫ ПОДСТАНОВКИ.
ЛАБОРАТОРНАЯ РАБОТА №3
ЗАДАНИЕ №1
Рассчитать показатели таблицы 7
Таблица 7 – Расчет суточного рациона
Наименование продукта | Количество, кг | Содержание калорий в 1кг | Цена за 1 кг | Общая калорийность рациона | Стоимость рациона |
Хлеб | 0,3 | 3000 | 12 | ||
Молоко | 1 | 570 | 25 | ||
Мясо | 0,2 | 2500 | 165 | ||
Рыба | 0,2 | 1600 | 150 | ||
Овощи | 0,5 | 1200 | 30 | ||
Фрукты | 0,5 | 900 | 50 | ||
Итого: |
Используя функцию ПОИСК РЕШЕНИЯ, найти оптимальный суточный рацион, имеющий наименьшую стоимость и содержащий от 2500 до 3500 килокалорий. Общий вес съедаемого не должен превышать трех килограммов.
Порядок работы:
1. Заполнить таблицу и рассчитать необходимые показатели, включая итоговую строку.
2. Выполнить функцию ПОИСК РЕШЕНИЯ пункта меню ДАННЫЕ.
3. В открывшемся окне указать адрес ячейки целевой функции (суммарная стоимость) и установить МИНИМУМ.
4. Установить в качестве ячеек переменных (изменяемые ячейки) адреса ячеек, в которых расположены значения количества продуктов в вашем рационе.
5. Вводить ограничения с помощью клавиши ДОБАВИТЬ. В качестве ограничений принять значения суммарной калорийности в пределах от 2500 до 3500 ккал. Это вводится в виде двух ограничений: >=2500 и <=3500. Третье ограничение вводится по весу съедаемых продуктов. Четвертое обязательное ограничение – неотрицательность переменных. Для этого следует в окне Поиск решения установить в позиции Параметры Неотрицательные значения.
6. После ввода всех условий ВЫПОЛНИТЬ расчет и проанализировать полученный результат.
7. Пересчитать результат, изменив цель на ПОИСК максимального решения.
8. Выполнить расчет, подыскав рацион на 100 рублей.
ЗАДАНИЕ №2
График занятости персонала парка отдыха.
Для работников с пятидневной рабочей неделей и двумя выходными подряд требуется подобрать график работы, обеспечивающий требуемый уровень обслуживания при наименьших затратах на оплату труда.
Порядок работы:
1. Ввести данные таблицы 8.
2. Выполнить необходимые расчеты: общее количество работников, сумма общей заработной платы всех работников, количество всех работников, выходящих каждый день недели.
3. Выполнить функцию ПОИСК РЕШЕНИЯ. Установить в качестве целевой функции ячейку, в которой находится общая дневная заработная плата.
4. Переменными в задаче будут величины, соответствующие количеству работников, занятых по различным графикам.
5. Ограничения – это необходимость охраны всех объектов, т.е. значения Всего должны быть не меньше значений Всего требуется. При этом полученные значения должны быть неотрицательными и целочисленными.
Таблица 8 – График занятости персонала парка отдыха
График
А
Б
В
Г
Д
Е
Ж
Всего:
Всего требуется:
Дневная оплата работника: 500 руб.
Общая дневная заработная плата всех работников: 1600
ЗАДАНИЕ №3
Решение задачи оптимизации расходов.
Предприятие работает по недельному графику, требующему разного числа работников в разные дни недели. Необходимое число работников приведено в таблице 9.
Таблица 9 – Потребность в персонале
Понедельник | Вторник | Среда | Четверг | Пятница | Суббота | Воскресенье |
13 | 14 | 16 | 18 | 22 | 20 | 19 |
Можно использовать сотрудников с пятидневной рабочей неделей (выходные – любые два дня подряд, недельная заработная плата – 2500 рублей) и с шестидневной рабочей неделей (выходной – суббота или воскресенье, дневная заработная плата – 3500 рублей, то есть шестой рабочий день оплачивается по двойной ставке). При этом требуется, чтобы использовались все варианты расписания работы с двумя выходными (это позволяет при болезни одного из работников привлечь на замену человека, который только что имел свободный день).
ЗАДАНИЕ №4
Определить оптимальное количество каждого вида колбасных изделий, которое должно быть реализовано в собственной розничной сети, крупным оптовикам, мелким оптовикам, а также в сети розничных магазинов, не принадлежащих производителю. Исходные данные для расчета представлены в таблицах 10,11,12. Результаты расчетов оформить в виде таблиц 13,14,15.
Таблица 10 – Себестоимость и цена реализации одного килограмма продукции, руб.
Группы
Колбасных
Изделий
Собственная
Розничная сеть
Крупные оптовики
Дата: 2018-12-21, просмотров: 560.