РЕШЕНИЕ ЗАДАЧ ПО ОБРАБОТКЕ ЭКОНОМИЧЕСКОЙ ИНФОРМАЦИИ

Постановка задачи

Директор магазина должен составитьштатное расписание, т.е. определить, сколько сотрудников, на каких должностях и с каким окладом он должен принять на работу. Общий месячный фонд зарплаты составляет $ 15000.

Построим модель решения этой задачи.

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

3 уборщицы; 2-4 грузчика; 2 товароведа; 1 секретарь; 5-10 сервисная служба; 8-10 продавцы-консультанты; 1 бухгалтер; 1 директор;

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

Итак, директор принимает для себя следующую модель задачи. За основу берется оклад уборщицы, а все остальные вычисляются через нее: во столько раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада уборщицы: А*С+В, где С - оклад уборщицы;

А и В - коэффициенты, которые для каждой должности определяются решением совета трудового коллектива.

Допустим, совет решил, что

грузчик должен получать в 1, 5 раз больше уборщицы

товаровед - в 3 раза больше уборщицы

сервисная служба - на 30 $ больше, чем товаровед

секретарь - в 2 раза больше уборщицы

бухгалтер - на 40 $ больше грузчика

продавец-консультант - в 4 раза больше уборщицы

директор - на 20 $ больше продавца-консультанта

А=1, 5 В=0

А=3 В=0

А=3 В=30

А=2 В=0

А=1, 5 В=40

А=4 В=0

А=4 В=20

Задав количество человек на каждой должности, можно составить уравнение:

N1*(A1*C+B1)+N2*(A2*C+B2)+.....+N8*(A8*C8+B8)=8000,

где N1 - количество уборщиц; N2 - количество грузчиков и т.д.

А1...А8 и В1...В8 - коэффициенты для каждой должности.

В этом уравнении нам известны А1...А8 и В1...В8, а не известны С и N1...N8.

Ясно, что решить такое уравнение известными методами не удастся, да и единственно верного решения нет. Остается решать уравнение путем подбора.

Взяв первоначально какие-либо приемлемые значения неизвестных, подсчитаем сумму. Если эта сумма равна фонду заработной, платы то вам повезло. Если фонд заработной платы превышен, то можно снизить оклад уборщицы либо отказаться от услуг какого-либо работника и т.д. Проделать такую работу вручную трудно. Но вам поможет электронная таблица

В столбцах А и В указываются коэффициенты А и В, соответствующие каждой должности.

В ячейку Н3 заносится значение заработной платы уборщицы.

В столбце D вычисляется заработную плату для каждой должности.

Внимание! Столбец D должен заполнятся формулами с использованием абсолютной ссылки на ячейку Н3. Изменения содержимого этой ячейки должно приводить к изменению содержимого всего столбца D.

В ячейку D3 занесите формулу =АЗ*$Н$3+ВЗ.

Скопируйте формулу из ячейки D3 в D4: D10.

При копировании адрес ячейки с зарплатой уборщицы остался постоянным (абсолютным), а адреса A3 и ВЗ перенастраиваются (они относительные).

В столбце Е укажите количество сотрудников на должностях.

Вводите значения по среднему количеству сотрудников. Данные в ячейках ЕЗ: Е5 могут изменяться в пределах штатного расписания, количество сотрудников на других должностях неизменно (см. постановку задачи).

В столбце F вычислите заработную плату всех сотрудников данной должности.

В ячейку F3 занесите формулу = зарплата * количество сотрудников.

Определите суммарный месячный фонд заработной платы.

Просуммируйте столбец F, используя кнопку «сумма».

Составьте штатное расписание.

Вносите изменения в зарплату уборщицы или меняйте количество сотрудников в ячейках Е4: Е5, Е7: Е8 до тех пор, пока полученный суммарный месячный фонд заработной платы не будет равен заданному $15000 (в ячейке F12 необходимо получить значение ≈ 15000).

Составьте штатное расписание с использованием функции автоматизации расчетов - подбор параметра.

Подбор параметра - удобное средство Excel для анализа «Что-если». При этом значения для ячеек-параметров изменяются так, чтобы число в целевой ячейке стало равно заданному.

Скопируйте таблицу на ЛИСТ 2.

Выберите команду меню сервис, подбор параметра.

Укажите в поле установить в ячейке адрес целевой ячейки $F$12.

Укажите в поле значение – 15000.

Укажите в поле изменяя ячейку адрес ячейки с зарплатой уборщицы $Н$3 и нажмите на кнопку < ОК>.

Начнется процесс подбора параметра. Если нажать на кнопку < ОК>, значения ячеек в таблице будут изменены в соответствии с найденным решением.

Нажмите на кнопку «ОК»

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

Оставьте столбцы С, D, Е, F.

Внимание! Удалить столбцы А, В, Н нельзя, так как в таблице на них есть ссылки, но их можно скрыть.

Столбцы А, В, Н необходимо выделить, а затем воспользоваться пунктом главного меню формат, столбец, скрыть или вызвать контекстно-зависимое меню и выбрать пункт скрыть.

Дайте заголовок таблице «Штатное расписание магазина» и подзаголовок «Директор Сидоров И.С.»

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

МАКРОСЫ

При использовании часто повторяющихся операций для удобства пользователя их можно оформить в макрос и вызывать нажатием одной кнопки. Создать макрос можно двумя способами: написать программу на языке программирования VISUAL BASIC или записать последовательность действий пользователя с помощью макрорекордера. Для вызова макрорекордера используется пункт меню Вид / Макросы / Начать запись, в которой следует указать имя макроса и при необходимости назначить ему “горячие” клавиши или пункт меню. После нажатия кнопки ОК на экране появится панель Остановить и все дальнейшие действия пользователя будут записаны в макрос. Для прекращения записи макроса воспользуйтесь кнопкой Остановить макрос или Вид / Макросы / Остановить запись. Для запуска макроса можно использовать “горячие” клавиши (если они были установлены) или назначить макросу кнопку панели инструментов, кнопку на поле таблицы (диаграммы) или рисованный объект.

Чтобы не создавать одинаковые макросы, можно копировать их в специальную книгу «Personal.xlsb», сохраненную на компьютере.

По умолчанию макросы Excel действуют только в тех книгах, в которых они содержатся. Это допустимо до тех пор, пока не потребуется использовать эти макросы в других книгах. Благодаря личной книге все хранящиеся в ней макросы можно открыть в любой книге Excel на компьютере.

Предоставить совместный доступ к файлу Personal.xlsb для разных компьютеров невозможно, но можно скопировать этот файл в папку XLSTART на других компьютерах.

Если требуется поделиться одним или несколькими макросами с другими пользователями, можно отправить им книгу, содержащую нужные макросы, в сообщении электронной почты. Можно также предоставить доступ к книге на общем сетевом диске или в библиотеке служб SharePoint Services.

Если требуется скопировать макрос из личной книги в другую книгу или наоборот, это можно выполнить в обозревателе проектов Редактора Visual Basic. В Excel можно запустить редактор Visual Basic, нажав сочетание клавиш ALT+F11.

Дополнительные сведения о макросах смотрите в справке Excel.

СЦЕНАРИИ

Сценарии – это набор ячеек рабочего листа, которые Excel рассматривает как отдельную группа. в качестве различных сценариев можно сохранить разные входные значения для формул и результаты их вычисления при этих входных значениях.

Сценарии применяются тогда, когда возникает необходимость сравнить и проанализировать ряд результатов однотипных расчетов. включающих входные значения и значения, вычисленные по определенным формулам.

Использование сценария можно сравнить с использованием Подбора параметра и Таблицы подстановки, но средство Подбор параметра позволяет за раз изменять значение только одной входной переменной, а Таблица подстановки ограничена форматом двухмерной таблицы, поэтому с ее помощью можно отследить влияние на формулу только одной или двух входных переменных. Сценарии не имеют таких ограничений.

ТАБЛИЦЫ ПОДСТАНОВКИ

Таблицы подстановки – это диапазон смежных ячеек, в которых показаны результаты вычисления определенных формул при изменении значений, влияющих на эти формулы. Таблицы подстановки предлагают удобный способ с помощью одного оператора вычислить, отобразить и сравнить несколько результатов вычисления определенных формул.

Таблицы подстановки применяются тогда, когда нужен удобный способ отобразить в виде таблицы результаты вычисления одной или нескольких формул для различных входных значений, от которых зависят эти формулы.

⇐ Предыдущая 1 2 345 6 Следующая ⇒