Нормативно-справочная информация реализуется в виде «Таблицы настройки», «Отпуска» и «Оборот».
Таблица настройки
| 20000 мини мум, руб | % | Льготы на раб-ка | Льгота на иждивенца |
| 20 000 | |||
| 20 000 | |||
| 20 000 | |||
| 20 000 | |||
| 20 000 | |||
| 20 000 | |||
| 20 000 | |||
| 20 000 | |||
| 20 000 | |||
| 20 000 | |||
| 20 000 | |||
| 20 000 |
Отпуска
Оборот
| Месяц | Плановый оборот, руб. | Фактический оборот, руб. | Отклоне- ние, руб. |
| 350 000 | 358 963 | 8 963 | |
| 370 000 | 359 891 | - 10 109 | |
| 400 000 | 451 239 | 51 239 | |
| 500 000 | 501 002 | 1 002 | |
| 830 000 | 965 712 | 135 712 | |
| 1 200 000 | 1 362 561 | 162 561 | |
| 1 250 000 | 1 389 265 | 139 265 | |
| 1 035 000 | 1 049 865 | 14 865 | |
| 800 000 | 968 231 | 168 231 | |
| 760 000 | 705 632 | - 54 368 | |
| 600 000 | 569 870 | - 30 130 | |
| 360 000 | 359 689 | - 311 | |
| Итого: | 8 455 000 | 9 041 920 | 586 920 |
Данные таблицы создаются один раз на момент внедрения задачи и
корректируются по мере необходимости: изменение объемов деятельности, %-ных ставок премий и зарплат, продолжительности отпусков и т.д.
Также одним из источников входной информации является формируемый в виде
ЭТ документ – «Лицевая карточка», фрагмент которой представлен в табл.
Лицевая карточка для работника № 01
ЛИЦЕВАЯ КАРТОЧКА № 01
Дата начисления 02
Фамилия И.О. Афоничкина Е.А.
Текущий оклад 12600
Должность Директор маркетинговой службы
Подразделение Отдел маркетинга
| Месяц | Оклад | Раб. дн. в мес. | % пре мии,% | Начисления | |||||
| Отпуск ные, р. | 13-я з/п, р | Больнич- ные сум- мы, р | 14-я з/п, р | Матери- альная помощь, р | Прочие, р | ||||
На нижеприведенном рисунке показан фрагмент лицевой карточки первого работающего в виде ЭТ, где формируется вся необходимая для расчетов месячных сумм заработной платы и других начислений и удержаний из нее информация.
Обобщенная структура ЭТ работающего включает несколько частей: блок постоянной информации; блок информации для начислений; блок расчета начислений; блок расчета удержаний; виды льгот для начислений.
Блок постоянной информации
Месяц Оклад Раб. дн.
в мес.
Блок информации для начисления
| Отпуск ные, р. | 13-я з/п, р | Больнич- ные сум- мы, р | 14-я з/п, р | Матери- альная помощь, р | Прочие, р |
Блок расчета начислений
Начислено по окладу, р.
Сумма
премии,
р.
Итого,
р.
Блок расчета удержаний
| ЕСН, р | НДФЛ, р | Итого, р. | Профсо- юзные взносы, р. |
Виды льгот для начисления
Число
иждивен-
цев
Сумма
льгот,
р.
Всего,
р
В области ЭТ размещаются аналогичные таблицы для каждого работающего в
своем блоке ячеек.
Таким образом, логическая «Лицевая карточка» разбита на две части
(стороны). В графах первой части лицевой стороны находится вся информация о начислениях и льготах по каждому конкретному сотруднику, а в графах второй
части – все расчеты заработной платы данного сотрудника.
На второй стороне документа находятся графы, обеспечивающие расчет показателя «Сумма к выдаче». Документ создается, заполняется и рассчитывается
для каждого сотрудника ежемесячно. Номер лицевой карточки является табельным номером сотрудника.
Алгоритм решения задачи
ФИО
Начало
YHO-V
Таблнаст
р.
Ввод
Отпуска
YHO_M
Оборот
YHO_M
Код
показате ля
YHO_M
YHO_M
Лицевая
карточка
YHO
Лицевая
карточка
Вывод
Ведомость
Конец
Блок-схема
Алгоритм расчета показателей
Блок «Ввода». Данные первичных документов вводятся в ЭВМ.
Блок «Расчета». На основе данных первичных документов в лицевой карточке производятся следующие расчеты:
1. Отпускные =(Сумма оклада за 3мес/(кол.-во мес.*29,6))*кол.-во дней отпуска
2. 13-я з/п =если фактический оборот больше планового оборота, то план.оборот*процент по 13-ой з/п, если фактич. оборот меньше планового, то ставим 0.
3. Больничные =(з/п за предыд. 2 мес/кол.-во раб дней)*кол-во дней на больнич.
4.14-яз/п =Если факт. оборот> план. оборота, то отклонение*на % по 14-ой
з/п.
5. Начислено по окладу =Сумме оклада
6. Сумма премий =Положит. отклонение оборота за месяц*% по премиям.
7. Итого =1+2+3+4+5+6
8. ЕСН =7*Ставку налога (35,6%)
9. НДФЛ =7*Ставку налога (13%)
10. Итого =8+9
11. Проф. взнос =7*Ставку взноса (1%)
Льготы, если годовой доход меньше 20 000 руб.:
™ На каждого иждивенца – 300 р.
™ На рабочего – 400 р.
Блок «Вывода». На основе расчетных данных «Лицевой карточки» формируется
конечный документ «Ведомость по заработной плате», выдаваемая и распечатываемая ежемесячно.
5.3. Проверка и защита данных
Рассмотрим дополнительные возможности автоматизации ввода и проверки данных. Пусть нам требуется автоматизировать получение простой таблицы учета проданных товаров, как на приведенном ниже рисунке.
Постановка задачи. Условимся, что наименования товаров будем брать только из единого справочника, цена товара будет находиться в диапазоне от 1 до 15 у.е., количество ограничим, а при превышении суммы свыше 100 у.е.будет выводиться сообщение красным текстом на синем фоне.
Порядок действий.
1. В новой книге на листе 1 в строке 1 введем заголовки колонок.
Колонкам B и C присвоим имена по их названию: Выделить колонки B
или С Þ Вставка Þ Имя Þ Создать Þ галочка в строке выше. Автоматически всей колонке (кроме первой строки) будет присвоено имя, совпадающее с текстом первой строки.
2. В колонке Д должна быть формула =B2*C2 и т.д., но мы будем использовать имена диапазонов: встать на D2 Þ = Þ Вставка Þ Имя Þ Присвоить Þ Выбрать Кол_во Þ * Þ Вставка Цена и получившуюся формулу =Кол_во*Цена размножить.
3. Для автоматизации ввода данных в колонку А на этом же листе в колонках АА-АД создадим Справочник товаров.
1 Дело в том, что для вставки значений из списка в Excel этот список не может находиться на других листах и книгах. Может, в следующих версиях появятся и такие возможности, которые самую популярную прикладную программу совсем приблизят к стандартным базам данных. А тенденция непрерывного развития информационных технологий такова, что, если какая-то возможность не реализована в данной версии,
но она логически напрашивается, то считайте, что в следующих версиях она появится. Если не появляется, значит, фирма-разработчик не дорабатывает или эта возможность реализована при помощи других программных продуктов. Например, с таблицами проще работать не в Wordе, а в Excel.
4. Теперь вернемся на начало листа, выделим диапазон А2:А30 и через Данные Þ Проверка Þ Список зададим диапазон списка, Сообщения
об ошибках примерно как на рисунках.
1 Здесь особых пояснений не требуется. Если список небольшой, то его можно задавать и простым перечислением через; (точка с запятой).
h СамостоятельнозадайтеусловияпроверокдляколонокКол-вои
Цена. Картинки прилагаются
После всего попробуйте ввести входные данные:
наименования товаров только из прилагаемого списка (попытка ввести другое название с клавиатуры выдаст сообщение об ошибке); введите цифровые данные в B и C, данные в колонке Сумма автоматически пересчитаются. Потом выделите данные в Д и примените Формат Þ Условное форматирование (см. Постановку задачи и картинки). Попробуйте ограничить количество одной тысячей и т.д.
Имена
* Имена ячейкам и диапазонам ячеек можно
задавать и непосредственно изменяя Имя ячейки в строке формул. Дополнительно почитайте через Справочник.
* Присвоенные имена ячеек и диапазонов распространяются на всю книгу, поэтому мы
не можем дать одинаковые имена, пусть на разных листах, но в одной книге.
1 Для защиты исходных данных от изменений можно столбцы АА-АД скрыть, а еще эффективнее будет защитить: 1. Выделите колонки A-D Þ Формат Þ Ячейки Þ Защита, снимите галочку Защищаемая ячейка Þ Ок. 2. Сервис Þ Защита Þ Защитить лист Þ Ок. Теперь весь лист защищен от изменений, кроме колонок A-D. Попробуйте! Подробнее – в Справочнике.
6. Решение задач оптимизации в Excel
Задачи линейного программирования или оптимизационные задачи в общем случае сводятся к вычислению максимального или минимального значения линейной целевой функции при заданных ограничениях.
при ограничениях
F(x) ΠMax (Min)
f1(x1, x2…, xn) (<=, >=, =) b1
f2(x1, x2…, xn) (<=, >=, =) b2
………………………………………………
Fn(x1, x2…, xn) (<=, >=, =) bn
Здесь F(x) = f(x1, x2…, xn) – целевая функция.
Используем инструмент Поиск решения. Если он не установлен в Excel на вашем компьютере, то надо будет включить его через меню Сервис J Надстройки:
После этого готовим данные для расчета по примерному шаблону.
Задача 1. Уборка кормов. Многолетние травы посеяны на площади 1 000
га. Найти оптимальное сочетание их уборки на сено, сенаж и силос, если требуется заготовить не менее 21 000 ц корм. ед. грубых кормов(сено и
сенаж) и не менее 12 000 ц силоса. При этом общие трудовые ресурсы
составляют (ограничены) 15 760 чел.-ч. Показатели выхода кормов и трудовых затрат приведены в таблице:
| Показатели | Cено | Cенаж | Cилос |
| Выход продукции с 1 га, ц | |||
| Затраты труда на 1 ц, чел.-час | 0,2 | 0,128 | 0,1 |
| Содержание кормовых единиц в 1 ц корма, ц | 0,5 | 0,4 | 0,16 |
Решение.
1. На листе Excel составим следующие таблицы - шаблон:
(заполнить все строки данными и формулами!)
Копия листа после решения задачи.
В строке 7 Площадь посевов пока ничего не ставим. Нам эти показатели еще предстоит найти. В общем случае там могут быть любые данные, в том числе
и пустые(нули). Наша целевая функция выделена.
2. После заполнения данными и формулами курсор устанавливаем в целевой ячейке В10 и вызываем инструмент Поиск решения:
и выбираем параметры в нижеприведенном окне: Выбираем Максимальному значению, курсор в окне Поиск решения на Изменяя значение (он замигает) и
в таблице мышью же укажем диапазон B7:D7 (абсолютные адреса сами
присвоятся).
3. На вкладке Ограничения последовательно добавим ограничения,
ссылаясь на ячейки B13 = D13, B14 <= D14, B15 >= D15, B16 >=D16.
4. Проверим параметры (здесь можно ничего не менять, кроме включения
«галочек» Линейная модель и Неотрицательные значения и ОК.
5. Мы вернулись в окно Поиск… и теперь смело можем нажать на
Выполнить.
6. Если все верно, то должны получиться приведенные значения.
Результаты можно сохранить, нажав на Результаты в окне сохранения
Тип отчета. Автоматически добавятся дополнительные листы:
7. Наши результаты в Excel совпали с результатами в книге,
выполненными при помощи других программ.
Самостоятельно проанализируйте при помощи данного же инструмента нашу задачу при отсутствии ограничений на трудовые ресурсы (на уборку пригнали студентов). Просто во время выполнения Поиска удалите данное ограничение. У Вас должен получиться следующий результат:
Из анализа следует, что при наличии дешевой рабочей силы выгоднее траву
убирать только на сенаж и силос. При этом достигается максимальное значение производства кормов: 47 000 вместо 34 750 единиц.
Задача 2. О бензиновых смесях.
| Стандартом предусмотрено, что октановое число автомобильного бензина А-76 должно быть не ниже 76, а содержание серы в нем — не более 0,3%. Для изготовления такого бензина на заводе используется смесь из четырех компонентов. Данные о ресурсах смешиваемых компонентов, их себестоимости и их октановом числе, а также о содержании серы приведены в таблице: | |||||||||
| Компоненты автомобильного бензина | |||||||||
| Характеристика | |||||||||
| № 1 | №2 | №3 | №4 | ||||||
| Октановое число | |||||||||
| Содержание серы, % | 0,35 | 0,35 | 0,3 | 0,2 | |||||
| Ресурсы, т | |||||||||
| Себестоимость, ден.ед./т | |||||||||
| Требуется определить, сколько тонн каждого компонента следует использовать для получения 1000 т автомобильного бензина А-76, чтобы его себестоимость была минимальной. |
Реше ние. Для решения этой задачи сформулируем ее экономико- математическую модель, т.е. сформулируем задачу математически. Введем необходимые обозначения: пусть Xj (] = 1,2,3,4) — количество в смеси компонента с номером j. С учетом этих обозначений имеем задачу (кри- терий оптимальности — «минимум себестоимости»):
f(X) = 40*x1 + 45*x2 + 60*x3 + 90*x4 J Min
x1 + x2+ x3+ х 4= 1000
| Задача 3. Фирма «Снежок» выпускает мороженое 2-х видов: сливочное и шоколадное. Для изготовления требуются молоко, сливки, сахар и какао. Смета расходов на 1 кг продукта приведена в табл.3 | ||||||
| Исх. продукты | Расходы на 1 кг | |||||
| Молоко | Сливки | Какао | Сахар | Наполнители | ||
| Сливочное | 0,4 | 0,1 | 0,1 | 0,4 | ||
| Шоколадное | 0,6 | 0,2 | 0,1 | 0,2 | ||
| Цена | ||||||
| Изучение рынка сбыта мороженого показало, что соотношение сливочного и шоколадного сортов составляет как 2,5:1, а максимальный объем сбыта до 6 000 кг. Определить план выпуска мороженого, чтобы прибыль от реализации была максимальной, если сливочное мороженое отпускается по 26 рублей за килограмм, а шоколадное-24 рубля? |
Записать целевую функцию и ограничения, решить средствами Excel.
Задача 4. Транспортная задача
В городе имеются два склада муки и два хлебозавода. Ежедневно с
первого склада вывозится 50 т муки, со второго 70 т.
Эта мука доставляется на хлебозаводы, причем первый завод получает
40 т, второй 80 т. Допустим, что перевозка одной тонны муки с первого склада на первый завод стоит 1 р. 20 к., с первого склада на второй завод – 1
р. 60 к., со второго склада на первый завод 80 к. и со второго склада на второй завод 1 р.
Как нужно спланировать перевозки, чтобы их стоимость была минимальной?
Для того, чтобы ответить на этот вопрос, дадим математическую постановку задачи. Обозначим через Х1 и Х2 количество муки, которую нужно перевезти со второго склада на первый и второй завод. Эти условия приводят к системе уравнений:
Х1+Х2=50
Х3+Х4=70 (1)
Х1+Х3=40
Х2+Х4=80
Хi>=0, i=1,2,3,4. (2)
Первые два уравнения системы определяют, сколько муки нужно вывести с каждого склада, два других уравнения показывают, сколько муки
нужно привести на каждый завод.
Неравенство (2) означают, что в обратном направлении с заводов на склады муку не возят. Общая стоимость всех перевозок определяется
формулой:
F=1,2X1+1,6X2+0,8X3+X4 J Min (3)
С математической точки зрения задача заключается в том, чтобы найти числа Xi(i=1,2,3,4), удовлетворяющие условиям (1), (2) и минимизирующие
стоимость перевозок (3).
Рассмотрим систему (1). Это система четырёх уравнений с четырьмя неизвестными. Однако независимыми в ней являются только первые три уравнения, 4-ое их следствие (если сложить 1-ое и 2-ое уравнения и вычесть
3-ье, получится 4-ое). Таким образом, фактически нужно рассмотреть следующую систему, эквивалентную (1):
Х1+Х2=50
Х3+Х4=70
Х1+Х3=40 (4)
Теперь эту задачу можно решить стандартными средствами.
Попробуйте самостоятельно.
Из законов Мерфи
© Закон Дж. Б. Шоу. Кто может - делает. Кто не может - учит.
© Дополнение студентов МГПИ им. Ленина. Кто не может учить - учит, как учить.
© Дополнение Мартина. Кто не может учить - управляет.
© Принцип Питера. В любой иерархической системе каждый служащий стремится достичь своего уровня некомпетентности.
Следствия
1. С течением времени каждая должность будет занята служащим, который некомпетентен в выполнении своих обязанностей.
2. Работа выполняется теми служащими, которые еще не достигли своего уровня некомпетентности.
3. Каждый служащий начинает со своего уровня компетентности.
4. Проявляйте заботу о мухах, а слоны о себе позаботятся.
5. Унция репутации стоит фунта работы.
6. Закон Корнуэлла. Начальство склонно давать работу тем, кто менее всего способен ее выполнить.
7. Люди согласны сделать работу, когда необходимость в этом уже отпала.
© Закон Поттера. Величина рекламной шумихи вокруг товара обратно пропорциональна его реальной стоимости.
© Закон Вейлера. Нет невыполнимой работы для человека, который не обязан делать ее сам.
© Второй закон Вейнберга. Если бы строители строили здания так же, как программисты пишут программы, первый залетевший дятел разрушил бы цивилизацию.
Законы исходных данных Спенсера
1. Каждый может принимать решение, располагая достаточной информацией.
2. Хороший руководитель принимает решение и при ее нехватке.
3. Идеальный - действует в абсолютном неведении.
© Правило точности Рэя. Измеряй микрометром. Отмечай мелом. Отрубай топором.
© Первый закон ремонта. Нельзя починить то, что не сломано.
© Закон свадебных затрат Тома. Продолжительность брака обратно пропорциональна расходам на свадьбу.
© Правило соседства по комнате. Тот, кто храпит, засыпает первым.
© Первый постулат Пардо. Все, что есть хорошего в жизни, либо незаконно,
либо аморально, либо ведет к ожирению.
© Наблюдение Этторе. Соседняя очередь всегда движется быстрее.
Серия «Компьютердля Петруши»
В серии вышли и выходят:
9 Самоучитель по DOC и NC
9 Excel 7.0 и не только...
9 ACCESS 2000
9 Текстовые редакторы
9 Excel 2000 и не только…
9 Microsoft OFFICE. Учебный практикум (в трех частях)
Учебно-методические пособия серии "Компьютер для Петруши" отличаются оригинальной методикой для самостоятельного изучения программных продуктов, сочетающей в себе легкость изложения и глубину освоения, доступность для начинающих и пользу для опытных пользователей Печатается без согласования с Microsoft. Билл Гейтс отдыхает…
