Торт мороженое с шоколадом;pdf

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ
РОССИЙСКОЙ ФЕДЕРАЦИИ
Белгородский государственный технологический университет
им. В. Г. Шухова
ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ
В МЕНЕДЖМЕНТЕ
Методические указания к выполнению
лабораторных работ для студентов
направления подготовки 080200 – Менеджмент
Часть 1
Белгород
2014
3
СОДЕРЖАНИЕ
Лабораторная работа №1. Формирование решений задач
средствами электронных таблиц............................................................... 4
Лабораторная работа № 2. Технология создания и
использования оперативных форм и серийных документов
в организационно-экономической сфере ................................................ 14
Лабораторная работа № 3. Технология решения
информационно-поисковых задач и формирование
отчетов в среде табличного процессора Excel ........................................ 34
Лабораторная работа № 4. Применение стандартных
функций для решения инвестиционных задач ...................................... 48
Лабораторная работа №5. Методика постановки задачи
и реализация ее решения на компьютере
средствами Microsoft Excel ..................................................................... 58
Лабораторная работа №6. Применение информационных
технологий для формирования решений .............................................. 76
Приложение............................................................................................100
4
Лабораторная работа №1.
Формирование решений задач средствами электронных таблиц
Цель работы: изучение технологии построения вычислительных
схем средствами электронных таблиц для решения задач класса  что
будет, если?.. и как сделать, чтобы?..
Содержание
Изучаются вопросы:
1. Изучение технологии построения вычислительных схем
средствами электронных таблиц для решения задач.
2. Метод решения задач класса – что будет если?..
3. Метод решения задач класса – как сделать, чтобы?..
Выполняется вариант задания.
Указания
Согласно варианту задания построить вычислительную схему
решения задачи. Получить ответ на все вопросы задачи. В отчет по
лабораторной работе записать формулы, используемые для расчета.
Задача. Необходимо выяснить, какова будет рентабельность
продукции через 3 месяца, если известен рост выручки. Рост выручки в
месяц составляет 4% от предыдущего месяца. Выручка за январь 20ХХ
года составляет 3000 ДЕ. Постоянные затраты на период составляют
600 ДЕ. Переменные – 70% от выручки.
Сколько составит размер рентабельности в марте, если рост выручки
в месяц – 10%?
Какова должна быть величина роста выручки, чтобы рентабельность
на конец периода составила 17%?
Укажите формулы для расчета.
Решение:
Средствами электронной таблицы Еxсеl можно решить два типа
задач:
1) что будет, если?..
2) как сделать, чтобы?..
Первая задача решается достаточно просто. Для этого пользователю
необходимо создать шаблон задачи в программе Microsoft Excel. А
затем, изменяя исходные данные, можно получить различные варианты
решения.
Для расчета введем следующие обозначения: В  выручка,
ПЕ  переменные затраты, ПО  постоянные затраты, П  прибыль,
Р – рентабельность.
Расчет осуществляется по следующим формулам:
Прибыль П = В  ПЕ  ПО.
5
Рентабельность: P 
П
ПЕ  ПО 
.
Схема решения задачи в электронной таблице Excel представлена на
Рис. 1.
Рис. 1. Схема решения задачи
В результате расчетов получим результат, представленный в
табл. 1:
Таблица 1
Показатель
Выручка
Переменные затраты
Постоянные затраты
Прибыль
Рентабельность
Январь
3000,00
2100,00
600,00
300,00
11,11%
Февраль
3120,00
2184,00
600,00
336,00
12,07%
Март
3244,80
2271,36
600,00
373,44
13,01%
Для ответа на вопрос «что будет, если?..» можно заменить
коэффициент роста выручки, с 4% на 10%. Тогда будет получен ответ на
вопрос: «Сколько составит размер рентабельности в марте, если рост
выручки в месяц – 10%?» Для этого достаточно изменить значение
ячейки В4, равное 4%, на значение 10%. Значение рентабельности в
марте составит 15,57%, если рост выручки в месяц будет равен 10%
(табл. 2).
Таблица 2
Показатель
Выручка
Переменные затраты
Постоянные затраты
Прибыль
Рентабельность
Январь
3000,00
2100,00
600,00
300,00
11,11%
Февраль
3300,00
2310,00
600,00
390,00
13,40%
Март
3630,00
2541,00
600,00
489,00
15,57%
Вторая задача "как сделать, чтобы?.." состоит в определении таких
исходных данных, которые обеспечат необходимый результат. Для ее
решения следует выбрать показатель, используемый в качестве цели, и
показатель, который следует вычислить, чтобы добиться поставленной
цели. Например, если в качестве цели использовать показатель
желаемой рентабельности за март, равный 17% (0,17 в единицах), а в
качестве искомого значения показатель роста выручки (ячейка В4),
который обеспечит эту рентабельность.
6
Для этого необходимо установить курсор в ячейку D11, где должна
быть представлена желаемая рентабельность за март, и выбрать команду
Подбор параметра вкладки Данные раздел Работа с данными пункт
Анализ "что если". В поле Установить в ячейке будет находиться адрес D11
(рис. 2). В поле Значение следует указать 0,17 (17%). Так как нас
интересует
значение
показателя
рост
выручки,
в
поле
Изменяя значение ячейки: следует указать адрес ячейки В4. Величина роста
выручки должна составлять 14%, чтобы рентабельность на конец
периода (март) составила 17%.
Ответ: какой
рост должен
быть
Желаемая
рентабельность
Сюда установить
курсор и выполнить
команды "Сервис" 
"Подбор параметра"
Рис. 2. Технология решения задачи "как сделать, чтобы?.."
Аналогично можно произвести подбор любого значения.
Задания
Вариант 1
Задача 1. Необходимо выяснить какое финансовое состояние будет у
предприятия (размер прибыли) через 6 месяцев, если известен рост
выручки. Рост выручки в месяц составляет 3% от предыдущего
значения. Выручка за январь 20ХХ года составляет 2000 ДЕ.
Постоянные затраты на период составляют 400 ДЕ. Переменные – 60%
от выручки.
Сколько составит размер прибыли через 6 месяцев, если рост
выручки на период – 5%?
Какова должна быть величина роста выручки, чтобы прибыль на
конец периода составила 1500 ДЕ?
Укажите формулы для расчета.
7
Задача 2. Стоимость объекта основных средств составляет 140 тыс.
руб., срок полезного использования – 6 лет. Каковы будут годовые
суммы амортизации и остаточная стоимость объекта после каждого
срока использования, если начисление амортизации осуществляется
линейным способом?
Каковы будут годовые суммы амортизации и остаточная стоимость
объекта через шесть лет эксплуатации, если срок полезного
использования составит 8 лет? Каковы будут годовые суммы
амортизации и остаточная стоимость объекта после каждого срока
использования, если стоимость объекта основных средств составит 160
тыс. руб.?
Какова должна быть первоначальная стоимость объекта, чтобы при
начальных условиях задачи сумма годовой амортизации составляла
20 тыс. руб.?
Укажите формулы для расчета.
Вариант 2
Задача 1. Годовой выпуск продукции на предприятии – 500 ед. Цена
единицы продукции равна 200 ДЕ. Затраты на весь выпуск, ДЕ:
Сырье
 36000
Вспомогательные материалы
 300
Топливо и энергия
 24000
Заработная плата
 15000
Отчисления на социальные нужды  5700
Общепроизводственные расходы
 7400
Общехозяйственные расходы
 3000
Коммерческие расходы
 3600
Итого
 95000
Рассчитайте критический выпуск продукции.
Какой будет критический выпуск продукции, если цена на единицу
изделия увеличится до 220 ДЕ?
Какой будет критический выпуск продукции, если расходы на сырье
увеличатся на 10%?
Какова должна быть цена на единицу продукции, чтобы критический
выпуск составил 150 ед.?
Укажите формулы для расчета.
Задача 2. Производственное предприятие планировало выпустить
2000 шт. изделий. Средний расход материалов на одно изделие за
предыдущий год составил 3,4 кг, а средняя цена материала – 240 руб./кг.
Каковы будут нормативные затраты предприятия на весь выпуск
продукции? Найти нормативные затраты предприятия при объеме
производства в 1900, 2100, 2200 шт.
8
В отчетном году расход материалов на одно изделие фактически
повысился до 4,0 кг, а фактическая средняя цена материала снизилась до
210 руб./кг. Определить каково будут отклонение плановых затрат от
фактических затрат при всех объемах производства?
Какова должна быть норма расхода сырья, чтобы отклонение
плановых затрат от фактических равнялось нулю?
Укажите формулы для расчета.
Вариант 3
Задача 1. Цена продаваемой продукции – 50 ДЕ за единицу.
Переменные затраты компании составили 45 ДЕ на одно изделие, а
постоянные затраты за год были 600000 ДЕ. Если компания реализовала
1000000 изделий за текущий год, то какова прибыль (убыток) за
текущий год?
Рост объемов реализации продукции в год составляет 15%, рост цены
на единицу изделия составляет 3% в год, а рост переменных затрат
составляет 7% в год от значения предыдущего года. Какова будет
прибыль компании за 4 года, если рост объемов реализации составит
18%?
Какова будет прибыль (убыток) компании через 4 года?
Какой должен быть рост цены (в процентах) на единицу изделия,
чтобы прибыль через 4 года составляла 2500000 ДЕ?
Укажите формулы для расчета.
Задача 2. Годовой выпуск продукции на предприятии – 1000 ед.
Цена единицы продукции равна 150 ДЕ. Затраты на весь выпуск, ДЕ.:
Сырье
 40000
Вспомогательные материалы
 500
Топливо и энергия
 15000
Заработная плата
 10000
Отчисления на социальные нужды  3600
Общепроизводственные расходы
 4500
Общехозяйственные расходы
 4200
Коммерческие расходы
 2200
Итого
 80000
Рассчитайте критический выпуск продукции.
Какой будет критический выпуск продукции, если цена на единицу
изделия увеличится до 180 ДЕ?
Какой будет критический выпуск продукции, если расходы на сырье
увеличатся на 10%?
Какова должна быть цена на единицу продукции, чтобы критический
выпуск составил 80 ед.?
Укажите формулы для расчета.
9
Вариант 4
Задача 1. Стоимость объекта основных средств составляет 80 тыс.
руб., срок полезного использования – 5 лет. Каковы будут годовые
суммы амортизации и остаточная стоимость объекта после срока
использования, если начисление амортизации осуществляется способом
уменьшаемого остатка (коэффициент ускорения – 2)?
Каковы будут годовые суммы амортизации и остаточная стоимость
объекта через пять лет эксплуатации, если срок полезного
использования составит 6 лет? Каковы будут годовые суммы
амортизации и остаточная стоимость объекта после срока
использования, если стоимость объекта основных средств составит 100
тыс. руб.?
Каким должен быть коэффициент ускорения, чтобы при начальных
условиях задачи остаточная стоимость объекта через 5 лет составили
2,5 тыс. руб.?
Укажите формулы для расчета.
Задача 2. Цена продаваемой продукции – 50 ДЕ за единицу.
Переменные затраты компании составили 45 ДЕ на одно изделие, а
постоянные затраты за год были 600000 ДЕ. Если компания реализовала
1000000 изделий за текущий год, то какова прибыль (убыток) за
текущий год?
Рост объемов реализации продукции в год составляет 17%, рост цены
на единицу изделия составляет 4% в год, а рост переменных затрат
составляет 8% в год от значения предыдущего года.
Какой должен быть рост цена на единицу изделия в текущем году,
чтобы прибыль текущего года составила 3000000 ДЕ? И какова будет
прибыль (убыток) компании через 4 года?
Укажите формулы для расчета.
Вариант 5
Задача 1. Предприятие выпускает и продает изделие А. При этом
переменные издержки на производство и сбыт изделия составляют 500
руб. Деталь продается по цене 750 руб. Постоянные затраты
предприятия в месяц составляют 80 тыс. руб. Какую прибыль может
получить предприятие в месяц, если оно продаст 400, 500, 600, 700
изделий?
Какой будет прибыль (убыток) при производстве 500 изделий, если
переменные издержки увеличатся до 600 руб. или уменьшатся до 450
руб.?
10
Какова будет прибыль (убыток) если объем производства снизится
до 300 единиц или возрастет до 900 единиц при начальных условиях
задачи?
Какой должна быть цена реализации продукции, что при объеме
производства в 600 единиц получить прибыль в размере 100 тыс. руб.?
Укажите формулы для расчета.
Задача 2. Необходимо выяснить какое финансовое состояние будет у
предприятия (размер прибыли) через 7 лет, если известен рост выручки.
Рост выручки в год составляет 10% от предыдущего значения. Выручка
за январь текущего года составляет 6100 ДЕ. Постоянные затраты на
период составляют 960 ДЕ. Переменные – 70% от выручки.
Сколько составит размер прибыли через 7 лет, если рост выручки на
период – 11%?
Какова должна быть величина роста выручки, чтобы прибыль на
конец периода составила 3000 ДЕ.?
Укажите формулы для расчета.
Вариант 6
Задача 1. Необходимо выяснить какое финансовое состояние будет у
предприятия (размер прибыли) через 5 лет, если известен рост выручки.
Рост выручки в год составляет 12% от предыдущего значения. Выручка
за январь текущего года составляет 4500 ДЕ. Постоянные затраты на
период составляют 800 ДЕ. Переменные – 65% от выручки.
Сколько составит размер прибыли через 5 лет, если рост выручки на
период – 10%?
Какова должна быть величина роста выручки, чтобы прибыль на
конец периода составила 5000 ДЕ?
Укажите формулы для расчета.
Задача 2. Предприятие выпускает и продает изделие В. При этом
переменные издержки на производство и сбыт изделия составляют 650
руб. Деталь продается по цене 900 руб. Постоянные затраты
предприятия в месяц составляют 97 тыс. руб. Какую прибыль может
получить предприятие в месяц, если оно продаст 400, 500, 600, 700
изделий? Какой будет прибыль (убыток) при производстве 600 изделий,
если переменные издержки увеличатся до 750 руб. или уменьшатся до
450 руб.?
Какова будет прибыль (убыток) если объем производства снизится
до 300 единиц или возрастет до 800 единиц при начальных условиях
задачи? Какой должна быть цена реализации продукции, что при объеме
производства в 500 единиц получить прибыль в размере 100 тыс. руб.?
Укажите формулы для расчета.
11
Вариант 7
Задача 1. Цена продаваемой продукции – 42 ДЕ за единицу.
Переменные затраты компании составили 35 ДЕ на одно изделие, а
постоянные затраты за год были 560000 ДЕ. Если компания реализовала
1000000 изделий за текущий год, то какова прибыль (убыток) за
текущий год?
Рост объемов реализации продукции в год составляет 12%, рост цены
на единицу изделия составляет 2,5% в год, а рост переменных затрат
составляет 9% в год от значения предыдущего года. Какова будет
прибыль (убыток) компании за 4 года, если рост объемов реализации
составит 14%?
Какой должен быть рост цена на единицу изделия в текущем году,
чтобы прибыль текущего года составила 7000000 ДЕ? И какова будет
прибыль (убыток) компании через 4 года?
Укажите формулы для расчета.
Задача 2. Стоимость объекта основных средств составляет 190 тыс.
руб., срок полезного использования – 9 лет. Каковы будут годовые
суммы амортизации и остаточная стоимость объекта после каждого
срока использования, если начисление амортизации осуществляется
линейным способом?
Каковы будут годовые суммы амортизации и остаточная стоимость
объекта через шесть лет эксплуатации, если срок полезного
использования составит 10 лет? Каковы будут годовые суммы
амортизации и остаточная стоимость объекта после каждого срока
использования, если стоимость объекта основных средств составит 250
тыс. руб.?
Какова должна быть первоначальная стоимость объекта, чтобы при
начальных условиях задачи сумма годовой амортизации составляла
17 тыс. руб.?
Укажите формулы для расчета.
Вариант 8
Задача 1. Производственное предприятие планировало выпустить
1000 шт. изделий. Средний расход материалов на одно изделие за
предыдущий год составил 5 кг, а средняя цена материала – 100 руб./кг.
Каковы будут нормативные затраты предприятия на весь выпуск
продукции? Найти нормативные затраты предприятия при объеме
производства в 900, 1100, 1200 шт.
В отчетном году расход материалов на одно изделие фактически
снизился до 4,8 кг, а фактическая средняя цена материала повысилась до
12
120 руб./кг. Определить каково будут отклонение плановых затрат от
фактических затрат при всех объемах производства?
Какова должна быть норма расхода сырья, чтобы отклонение
плановых затрат от фактических равнялось нулю?
Укажите формулы для расчета.
Задача 2. Цена продаваемой продукции – 42 ДЕ за единицу.
Переменные затраты компании составили 35 ДЕ на одно изделие, а
постоянные затраты за год были 560000 ДЕ. Если компания реализовала
1000000 изделий за текущий год, то какова прибыль (убыток) за
текущий год?
Рост объемов реализации продукции в год составляет 12%, рост цены
на единицу изделия составляет 2% в год, а рост переменных затрат
составляет 9% в год от значения предыдущего года.
Какова будет прибыль (убыток) компании через 4 года?
Какой должен быть рост цены (в процентах) на единицу изделия,
чтобы прибыль через 4 года составляла 3000000 ДЕ?
Укажите формулы для расчета.
Вариант 9
Задача 1. Стоимость объекта основных средств составляет 120 тыс.
руб., срок полезного использования – 8 лет. Каковы будут годовые
суммы амортизации и остаточная стоимость объекта после срока
использования, если начисление амортизации осуществляется способом
уменьшаемого остатка (коэффициент ускорения – 2)?
Каковы будут годовые суммы амортизации и остаточная стоимость
объекта через восемь лет эксплуатации, если срок полезного
использования составит 9 лет? Каковы будут годовые суммы
амортизации и остаточная стоимость объекта после срока
использования, если стоимость объекта основных средств составит 200
тыс. руб.?
Каким должен быть коэффициент ускорения, чтобы при начальных
условиях задачи остаточная стоимость объекта через 8 лет составили
15 тыс. руб.?
Укажите формулы для расчета.
Задача 2. Предприятие выпускает и продает изделие Д. При этом
переменные издержки на производство и сбыт изделия составляют 422
руб. Деталь продается по цене 563 руб. Постоянные затраты
предприятия в месяц составляют 77 тыс. руб. Какую прибыль (убыток)
может получить предприятие в месяц, если оно продаст 400, 500, 600,
700 изделий?
13
Какой будет прибыль (убыток) при производстве 600 изделий, если
переменные издержки увеличатся до 480 руб. или уменьшатся до 375
руб.?
Какова будет прибыль (убыток) если объем производства снизится
до 350 единиц или возрастет до 800 единиц при начальных условиях
задачи?
Каковы должны быть переменные издержки на производство и сбыт
единицы изделия, что при объеме производства в 500 единиц получить
прибыль в размере 10 тыс. руб.?
Укажите формулы для расчета.
Вариант 10
Задача 1. Предприятие выпускает и продает изделие Б. При этом
переменные издержки на производство и сбыт изделия составляют 300
руб. Деталь продается по цене 500 руб. Постоянные затраты
предприятия в месяц составляют 95 тыс. руб. Какую прибыль (убыток)
может получить предприятие в месяц, если оно продаст 400, 500, 600,
700 изделий?
Какой будет прибыль (убыток) при производстве 500 изделий, если
переменные издержки увеличатся до 350 руб. или уменьшатся до 200
руб.?
Какова будет прибыль (убыток) если объем производства снизится
до 300 единиц или возрастет до 800 единиц при начальных условиях
задачи?
Каковы должны быть переменные издержки на производство и сбыт
единицы изделия, что при объеме производства в 700 единиц получить
прибыль в размере 100 тыс. руб.?
Укажите формулы для расчета.
Задача 2. Необходимо выяснить какое финансовое состояние будет у
предприятия (размер прибыли) через 6 месяцев, если известен рост
выручки. Рост выручки в месяц составляет 4% от предыдущего
значения. Выручка за январь 20ХХ года составляет 2300 ДЕ.
Постоянные затраты на период составляют 360 ДЕ. Переменные – 60%
от выручки.
Сколько составит размер прибыли через 6 месяцев, если рост
выручки на период – 6%?
Какова должна быть величина роста выручки, чтобы прибыль на
конец периода составила 2000 ДЕ?
Укажите формулы для расчета.
14
Лабораторная работа № 2.
Технология создания и использования оперативных форм и
серийных документов в организационно-экономической сфере
Цель работы: изучение технологии и приобретение практических
навыков создания оперативных форм, а также серийных документов
посредством технологии слияния.
Содержание
1.
2.
3.
4.
5.
Изучаются вопросы:
Понятие оперативной формы.
Технология создания оперативных форм и работа с ними.
Понятие серийных документов.
Создание основного документа, источника данных для слияния.
Отбор записей при слиянии документов. Работа с результатами
слияния.
Выполняется вариант задания.
Указания
Технология создания и использования оперативных форм
Понятие оперативных форм
В среде текстового процессора Word можно создать печатные формы
и оперативные (электронные) формы.
Печатные формы ничем не отличается от «обычного» бланка
документа, распечатываются и заполняются пользователем на бумаге.
Под оперативной (электронной) формой понимается документ
(точнее шаблон), который в обыденной практике называют бланком.
Основное ее назначение упростить и сделать более эффективной работу
по подготовке часто встречающихся в деловой практике документов,
справок, контрактов и т.д. Оперативная форма  это документ, который
содержит следующие элементы:
 текст или графические элементы, которые не могут быть
изменены лицом, заполняющим форму. Эти элементы задаются
разработчиком формы и включают в себя вопросы, списки возможных
ответов, таблицы с данными и т. д.;
 незаполненные области, в которые вводит данные лицо,
заполняющее форму. К этим элементам относятся поля формы и поля
Word.
Создание форм, заполняемых в приложении Word
При работе с электронными формами необходимо различать понятия
формы и шаблона формы. Разработчик формы создает ее образец,
который хранится в виде шаблона. При этом никаких изменений в
15
шаблон не вносится, и он может быть использован повторно.
Разработка электронной формы состоит из трех этапов:
 создания шаблона формы;
 добавления полей в шаблон формы;
 защиты и сохранения формы.
Создание шаблона формы. Шаблон формы ничем не отличается от
шаблона обычного документа Word, поэтому для его создания
используется стандартная последовательность действий.
Чтобы построить и сохранить структуру формы в виде шаблона:
1. Выберите меню Файл команду Создать. В окне Доступные шаблоны
создайте Новый документ (рис. 3).
Рис. 3. Окно Доступные шаблоны
2. Введите текстовую часть формы и другую неизменяемую
информацию.
3. Выберите команду Сохранить меню Файл, чтобы сохранить
сформированный документ в формате шаблона (*.dotx). Введите имя
шаблона в поле Имя файла и нажмите кнопку Сохранить. Оставьте шаблон
открытым, чтобы добавить поля формы.
Добавление полей в шаблон формы
Используя шаблон, можно включить элементы управления
содержимым и пояснительный текст, чтобы быстро и без труда создать
форму, которую можно отправить пользователям для заполнения с
помощью приложения Word. В расширенном сценарии любые элементы
управления содержимым, добавляемые к форме, также могут быть
связаны с данными.
На первом этапе необходимо выполнить настройку приложения
Word для создания форм.
В меню Файл выберите команду Параметры, а в окне выберите пункт
Настройка ленты. В области окна Основные вкладки установите флажок
вкладке Разработчик, а затем нажмите кнопку OК.
16
Для добавления элементов управления содержимым в форму на
вкладке Разработчик в группе Элементы управления нажмите кнопку Режим
конструктора (рис. 4), а затем щелкните место, куда необходимо вставить
элемент управления.
Рис. 4. Группа Элементы управления вкладки Разработчик
А затем выполните любое из следующих действий.
1. Вставка элемента управления текстом, куда пользователи могут
вводить текст.
На вкладке Разработчик в группе Элементы управления щелкните
элемент управления Форматированный текст
или Текст
.
2. Вставка раскрывающегося списка, ограничивающего доступный
выбор заданными вариантами.
 На вкладке Разработчик в группе Элементы управления щелкните
элемент управления Раскрывающийся список
.
 Выделите элемент управления содержимым, а затем на вкладке
Разработчик в группе Элементы управления нажмите кнопку Свойства.
 Чтобы создать список значений, в группе Свойства
раскрывающегося списка нажмите кнопку Добавить.
 Введите значение в окне Краткое имя, например Да, Нет или
Возможно.
Повторяйте этот шаг до тех пор, пока все значения не окажутся в
раскрывающемся списке.
3. Установка или изменение параметров элементов управления
содержимым.
У каждого элемента управления содержимым есть параметры,
которые можно установить или изменить. Например, в элементе
управления Выбор даты предлагаются необходимые варианты
отображения формата даты.
Щелкните правой кнопкой мыши элемент управления содержимым,
который необходимо изменить.
Выберите команду Свойства, а затем измените необходимые свойства.
4. Добавление в форму пояснительного текста.
Пояснительный
текст
может
сделать
создаваемую
и
распространяемую форму более удобной в использовании. Можно
изменить пояснительный текст по умолчанию в элементе управления
содержимым.
17
Чтобы настроить текст пояснения по умолчанию для пользователей
формы, выполните одно из следующих действий.
 На вкладке Разработчик, в группе Элементы управления нажмите
Режим конструктора.
 Щелкните элемент управления содержимым, где необходимо
изменить замещающий пояснительный текст.
 Отредактируйте и отформатируйте замещающий текст.
 Если необходимо скрыть элемент управления содержимым, когда
кто-либо вводит в собственное содержимое данные для замещения
пояснений в элементе управления Форматированный текст или Текст,
нажмите кнопку Свойства в группе Элементы управления, а затем
установите флажок Удалить элемент управления содержимым при
редактировании содержимого.
 На вкладке Разработчик в группе Элементы управления нажмите
кнопку
Режим
конструктора,
чтобы
отключить
возможность
конструирования и сохранить пояснительный текст.
Не устанавливайте флажок Содержимое нельзя редактировать, если
необходимо, чтобы пользователи формы заменяли пояснительный текст
своим собственным.
Добавление защиты в форму
Имеется возможность защитить отдельные элементы управления
содержимым в шаблоне, чтобы предотвратить удаление или
редактирование
определенного
элемента управления или группы
элементов управления, либо можно
защитить все содержимое шаблона
паролем.
Защита всего содержимого формы.
Откройте
форму,
которую
необходимо защитить. На вкладке
Разработка в группе Защита выберите
Ограничить редактирование (рис. 5).
В области задач Установить защиту в
группе Ограничения на редактирование
установите флажок Разрешить только
указанный
документа.
способ
редактирования
В
списке
ограничений
редактирования выберите пункт Ввод
данных в поля форм. В группе Включить
защиту нажмите кнопку Да, включить защиту.
Для назначения для документа пароля, чтобы только знающие его
Рис. 5. Этапы защиты
документа
18
пользователи могли удалить защиту, введите пароль в окне Новый пароль
(необязательно), а затем подтвердите его.
Создание и печать писем и других документов с помощью
слияния
Слияние применяется, когда нужно создать набор документов, таких
как письма на бланках, которые отправляются большому количеству
клиентов. Каждый документ содержит как стандартные, так и
уникальные
сведения.
Например,
письма
клиентам
можно
персонализировать, обращаясь к клиенту по имени. Уникальная
информация для каждого письма поступает из записей в источнике
данных.
Процесс слияния включает несколько общих действий.
1. Настройка основного документа. Основной документ содержит
текст и графику, общие для всех версий объединенного документа,
например обратный адрес в письме на бланке.
2. Подключение документа к источнику данных. Источником
данных является файл, содержащий сведения, которые должны
объединяться с документом, например имена и адреса получателей
письма.
3. Уточнение списка получателей или элементов. В Microsoft Word
создается копия основного документа для каждого получателя или
элемента в файле данных. Если необходимо создать копии только для
определенных элементов в файле данных, можно выбрать элементы
(или записи), которые требуется включить.
4. Добавление в документ заполнителей (полей слияния). При
слиянии эти поля заполняются сведениями из файла данных.
5. Предварительный просмотр и завершение слияния. Перед тем как
печатать весь комплект копий документа, каждую из них можно
предварительно просмотреть.
Для слияния используются команды вкладки Рассылки.
Настройка основного документа
Запустите приложение Word. По умолчанию открывается пустой
документ. Пусть он остается открытым. Если его закрыть, команды,
которые нужно будет выполнить на следующем шаге, окажутся
недоступными.
На вкладке Рассылки в группе Начало слияния выберите команду Начать
слияние (рис. 6). Выберите команду Письма.
19
Рис. 6. Начало слияния документов
Слияние также можно использовать для создания приведенных ниже
документов.
 Каталог. Для каждого элемента отображаются сведения одного и
того же типа, например имя и описание, однако сведения каждого
элемента уникальны. Выберите команду Каталог, чтобы создать
документ этого типа.
 Комплект конвертов. Обратные адреса на всех конвертах
одинаковы, однако адрес получателя на каждом конверте уникальный.
 Комплект почтовых наклеек. На каждой наклейке приводятся
имя и адрес получателя, которые являются уникальными.
 Комплект сообщений электронной почты. Основное содержимое
всех сообщений является одинаковым, но каждое из них предназначено
отдельному получателю и содержит сведения, предназначенные для
этого получателя, например его имя или другую информацию.
Подключение документа к источнику данных
Чтобы выполнить слияние данных в основной документ, необходимо
подключить этот документ к источнику данных или файлу данных. Если
файл данных еще не существует, его можно создать в процессе слияния.
При использовании существующего списка убедитесь в том, что он
содержит все необходимые сведения, включая все столбцы и строки. В
ходе слияния можно внести определенные изменения, однако открыть
источник отдельно во время этой процедуры невозможно. Выполнить
слияние проще, если источник данных подготовлен до подключения.
Для выбора файла данных на вкладке Рассылки в группе Начало
слияния нажмите кнопку Выбрать получателей (рис. 6) и выполните одно
из приведенных ниже действий.
1. Выбрать из контактов Outlook. Чтобы использовать список контактов
из приложения Microsoft Outlook, выберите данную команду.
2. Использовать существующий список. Если имеется лист Microsoft
Excel, база данных Microsoft Access или файл данных другого типа,
выберите данную команду и найдите этот файл в диалоговом окне Выбор
источника данных.
20
Если это файл Excel, можно выбрать данные из любого листа или
именованного диапазона в книге. В Access можно выбрать данные из
любой таблицы или запроса, определенных в конкретной базе данных.
Чтобы использовать файл другого типа, выберите его в диалоговом
окне Выбор источника данных. Если нужный файл не отображается,
выберите подходящий тип файла или вариант Все файлы в списке Тип
файлов. При слиянии можно использовать приведенные ниже типы
файлов данных:
 файлы одноуровневых файловых СУБД;
 HTML-файлы, содержащие одну таблицу. Первая строка таблицы
должна содержать названия столбцов, а остальные – данные.
 документ Microsoft Word, который должен содержать одну
таблицу. Первая строка таблицы должна содержать заголовки, а
остальные строки – записи, которые необходимо объединить с
документом.
 любой текстовый файл, который содержит поля данных,
разделенные (или разграниченные) знаками табуляции или запятыми, и
записи данных, разделенные знаками абзаца.
3. Ввести новый список. Если файл данных отсутствует, выберите
данную команду и создайте список в появившейся форме. Список
сохраняется в виде фала базы данных (*.mdb), который можно
использовать многократно.
Уточнение списка получателей или записей
При подключении к определенному файлу данных может
потребоваться перенести в основной документ содержимое не всех, а
только некоторых его записей (строк).
Чтобы ограничить список получателей или использовать
подмножество элементов файла данных, на вкладке Рассылки в группе
Начало слияния нажмите кнопку Изменить список получателей (рис. 6).
В диалоговом окне Получатели слияния выполните одно из
приведенных ниже действий:
 Выберите отдельные записи. Этот способ лучше всего подходит для
короткого списка. Установите флажки для тех получателей, которых
требуется включить в список, и снимите для тех, которых нужно
исключить. Если для слияния планируется использовать всего несколько
записей, можно снять флажок в строке заголовков и выбрать только
нужные записи. Аналогично, чтобы включить в документ большую
часть списка, установите флажок в строке заголовка, а затем снимите
флажки для тех записей, которые не нужно включать.
 Отсортируйте записи. Щелкните заголовок столбца, по которому
требуется выполнить сортировку. Список будет отсортирован в
21
алфавитном порядке по возрастанию (от А до Я). Чтобы отсортировать
список по убыванию (от Я до А), щелкните заголовок столбца еще раз.
 Отфильтруйте записи. Фильтрация записей полезна, если список
содержит записи, которые заведомо не требуется просматривать или
использовать для
слияния.
Выполнив
фильтрацию,
можно
устанавливать и снимать флажки, чтобы включить или исключить
отдельные записи.
 Добавьте получателей. Чтобы добавить получателей в список, в
разделе Источник данных щелкните имя файла данных, нажмите кнопку
Изменить и в диалоговом окне Изменение источника данных нажмите кнопку
Создать запись и введите сведения о получателе.
Добавление в документ заполнителей (полей слияния)
После подключения основного документа к файлу данных можно
вводить текст документа и добавлять заполнители, указывающие, где в
каждой копии документа должны появляться уникальные данные.
Установка соответствия полей слияния в файле данных. Чтобы
приложение Word всегда находило в файле данных столбец,
соответствующий каждому элементу адреса или приветствия,
необходимо сопоставить поля слияния в Word со столбцами в файле
данных. Чтобы сопоставить поля, нажмите кнопку Подбор полей в группе
Составление документа и вставка полей на вкладке Рассылки.
Откроется диалоговое окно Подбор полей, в котором элементы адреса
и приветствия приводятся слева, заголовки столбцов из файла данных
приводятся справа.
Ввод содержимого и добавление заполнителей (полей).
В основном документе введите содержимое, которое должно
отображаться во всех его копиях.
Чтобы добавить рисунок (например, эмблему), на вкладке Вставка в
группе Иллюстрации нажмите кнопку Рисунок и щелкните расположение, в
котором нужно вставить поле. Используйте команды в группе
Составление документа и вставка полей на вкладке Рассылки и добавьте
любые из перечисленных ниже полей:
Блок адреса с именем, адресом и прочими сведениями. Для этого нажмите
кнопку Блок адреса. В диалоговом окне Вставка блока адреса выберите
требуемые элементы адреса и форматы отображения, а затем нажмите
кнопку ОК.
Строка приветствия. Для этого нажмите кнопку Строка приветствия,
выберите формат строки приветствия, которая включает обращение, имя
и знак пунктуации, следующий за именем. Выберите текст строки
приветствия для случаев, когда имя получателя недоступно, например
22
когда источник данных не содержит имени или фамилии получателя, а
содержит только название компании и нажмите кнопку ОК.
Отдельные поля. Сведения можно вставлять из отдельных полей,
таких как имя, номер телефона или сумма вклада спонсоров. Чтобы
быстро добавить поле из файла данных в основной документ, щелкните
стрелку рядом со списком Вставка поля данных, а затем выберите имя
поля.
Настраиваемые поля из списка контактов Outlook. Единственный
способ включить настраиваемые поля из списка контактов в основной
документ – это начать слияние из приложения Outlook. Сначала нужно
настроить отображение в списке контактов полей, которые необходимо
использовать для слияния. После этого можно начинать слияние. Как
только будут установлены все необходимые параметры, автоматически
откроется приложение Word и можно будет завершить слияние.
Форматирование объединенных данных
В приложениях для работы с базами данных и редакторах
электронных таблиц, таких как Microsoft Access и Microsoft Excel,
вводимые в ячейки сведения сохраняются как неотформатированные
данные. Форматирование, примененное в приложении Access или Excel,
например шрифты и цвета, не хранится вместе с данными. При слиянии
сведений из файла данных с документом Word используются
неотформатированные данные.
Чтобы применить форматирование, выделите поле слияния вместе с
шевронами (« »), в которые оно заключено. На вкладке Главная в группе
Шрифт задайте параметры форматирования или нажмите кнопку запуска
диалогового окна Шрифт, чтобы получить доступ к дополнительным
параметрам.
Предварительный просмотр, завершение слияния
и печать документов
После того, как все поля добавлены в основной документ, можно
выполнить предварительный просмотр результатов слияния. Если эти
результаты соответствуют ожиданиям, можно завершить слияние.
Перед фактическим завершением слияния можно просмотреть
составные документы и, если нужно, внести соответствующие
изменения.
Составные документы можно печатать или видоизменять по
отдельности. Это может быть сделано как для всех документов, так и
для подмножества документов.
Чтобы распечатать подмножество документов, можно задать нужный
набор, указав диапазон номеров записей.
23
Для печати составных документов на вкладке Рассылки в группе
Завершение нажмите кнопку Найти и объединить и выберите команду
Печать документов. Выберите, следует ли печатать весь набор документов,
только копию, отображаемую в настоящий момент, или подмножество
документов, определяемое по указанному номеру записи.
Сохранение основного документа
Помните, что объединенные документы сохраняются отдельно от
основного. Основной документ удобно хранить отдельно, поскольку он
может оказаться полезен для последующих слияний.
При сохранении основного документа сохраняется и его
подключение к файлу данных. При следующем открытии документа в
приложении отобразится запрос на повторное слияние сведений из этого
файла данных в основной документ. В случае ответа Да документ
открывается с данными из первой записи, полученными в результате
слияния. В случае ответа Нет подключение между основным документом
и файлом данных разрывается. Основной документ становится
стандартным документом Word. Поля замещаются на уникальные
данные из первой записи.
24
Задания
Вариант 1
1. Создать шаблон бланка в среде Microsoft Word и произвести его
заполнение.
2. Создать источник данных в среде Microsoft Excel и произвести
объединение документов. Источник данных должен содержать не менее
10 записей.
Т А Л О Н № ____
на техническое обслуживание _____________
типа ________
Заводской № ____________________
Изъят «___»_________ _____ г.
на техническое обслуживание __________
типа _______
Корешок талона № ____
Дата выпуска «___» __________ ______г.
Продан(а) магазином __________________________
_____________________________________________
«___» _________ _____г.
Условия гарантийного обслуживания выполнены –
Владелец __________________________________
(Ф.И.О)
Подпись ______________
Выполнены работы по техническому обслуживанию
_____________________________________________
_____________________________________________
_____________________________________________
Механик ____________________
М. П.
Подпись
25
Вариант 2
1. Создать шаблон бланка в среде Microsoft Word и произвести его
заполнение.
2. Создать источник данных в среде Microsoft Word и произвести
объединение документов. Источник данных должен содержать не менее
10 записей.
ЛИСТОК ЧИТАТЕЛЬСКОГО ТРЕБОВАНИЯ
Читательский билет №
Дата «
»
20
г.
Шифры ____________________________________________________
Автор _____________________________________________________
Заглавие ___________________________________________________
Место издания ______________________________________________
Год издания ________________________________________________
Том
Выпуск
№
Фамилия читателя
Срочный заказ –
ЛИСТОК КОНТРОЛЯ
Читательский билет №
Дата «
»
20
г.
Шифры ____________________________________________________
Автор _____________________________________________________
Заглавие ___________________________________________________
Место издания ______________________________________________
Год издания ________________________________________________
Том
Фамилия читателя
Срочный заказ –
Выпуск
№
26
Вариант 3
1. Создать шаблон бланка в среде Microsoft Word и произвести его
заполнение.
2. Создать источник данных в среде Microsoft Excel и произвести
объединение документов. Источник данных должен содержать не менее
10 записей.
ПЛАТЕЖНОЕ ИЗВЕЩЕНИЕ № ______ (соответствует л/с)
на уплату налогов с имущества, земельного налога и налога с
имущества, переходящего в порядке наследования и дарения,
с физических лиц на 20___ г.
гр.(ке) _______________________________________________________
(фамилия, имя и отчество плательщика)
проживающему _______________________________________________
На основании действующего на территории Российской Федерации
налогового законодательства Вы должны уплатить:
Наименование налога
В том числе по срокам уплаты
15 сентября
15 ноября
*
Сумма налога
(руб.)
Недоимка по налогу за
_______ г.


Пеня на недоимку на
____ ____________


(число, месяц)
* Сроки уплаты устанавливаются налоговым органом.
М.П.
«___» ______________ 20___ г.
Начальник
Государственной налоговой инспекции
/
(подпись)
/
(Ф.И.О.)
ЛИСТОК КОНТРОЛЯ ПЛАТЕЖНОГО ИЗВЕЩЕНИЯ
ПЛАТЕЖНОЕ ИЗВЕЩЕНИЕ № ____
отправлено гр.(ке) __________________________________________
проживающему ____________________________________________
на уплату _________________________________________________
(Наименование налога)
27
Вариант 4
1. Создать шаблон бланка в среде Microsoft Word и произвести его
заполнение.
2. Создать источник данных в среде Microsoft Excel и произвести
объединение документов. Источник данных должен содержать не менее
10 записей.
Приложение № 1
к указанию ПВУ МВД России
от «__» ________ 20___ г.
В налоговый орган по
(наименование налогового органа)
(почтовый адрес, индекс)
ИНН
КПП
Код налогового органа
СВЕДЕНИЯ О РЕГИСТРАЦИИ
ФИЗИЧЕСКОГО ЛИЦА ПО МЕСТУ ЖИТЕЛЬСТВА
Фамилия ____________________________________________________
Имя _________________________________________________________
Отчество _____________________________________________________
Пол: муж.
жен.
Дата рождения «_____» ___________ ______г.
Место рождения ______________________________________________
(указывается в точном соответствии с записью в документе, удостоверяющем личность)
Гражданство _________________________________________________
(наименование гражданство, либо без гражданства)
Вид документа, удостоверяющего личность _______________________
Серия
Номер
Кем и когда выдан ____________________________________________
Подпись регистрируемого
/
/
(Фамилия)
28
Вариант 5
1. Создать шаблон бланка в среде Microsoft Word и произвести его
заполнение.
2. Создать источник данных в среде Microsoft Excel и произвести
объединение документов. Источник данных должен содержать не менее
10 записей.
Унифицированная форма № Т –6а
Утверждена Постановлением
Госкомстата России
от __.__.__ №_____
Код
Форма по ОКУД
по ОКПО
(наименование организации)
ПРИКАЗ
(распоряжение)
о предоставлении отпуска работникам
Отпуск
Фамилия
Имя
Отчество
вид
за
Профессия
(ежегодный, период
(должность)
учебный, без
сохранения с
по
з/п и другие)
количество
дата
дней
(календарных,
с по
рабочих)
С приказом
(распоряжением)
ознакомлен.
Подпись.
Руководитель организации __________ ___________ ________________
(должность)
(подпись)
(расшифровка подписи)
29
Вариант 6
1. Создать шаблон бланка в среде Microsoft Word и произвести его
заполнение.
2. Создать источник данных в среде Microsoft Word и произвести
объединение документов. Источник данных должен содержать не менее
10 записей.
Приложение 6 к Правилам доставки
товаров под таможенным контролем
Листок контроля
___________________ таможня
Товар поступил «___» ________ 20___ г.
Инспектор ___________
___________________________ таможня
Товар под таможенным контролем в количестве
__________ мест
За пломбами ___________________________________
Пломбы установлены отправителем –
Подлежит доставке в ___________________________
таможню до «___» _________ 20__г.
ДКД №
Инспектор
«___»________________ 20 ___г.
30
Вариант 7
ОТЧЕТ О ВЫПОЛНЕНИИ ПЛАНА
КАПИТАЛЬНОГО РЕМОНТА ОСНОВНЫХ ФОНДОВ
за
полугодие 20
г.
Выполнение плана сдачи капитального ремонта
по важным объектам
Фактически выполнено
за период с начала года
В том числе в
порядке
централизованного
ремонта
Всего
На период
сначала года
На отчетный
год
Объекты
Единицы
измерения
План
Код
КОРЕШОК ОТЧЕТА О ВЫПОЛНЕНИИ ПЛАНА
КАПИТАЛЬНОГО РЕМОНТА ОСНОВНЫХ ФОНДОВ
за
полугодие 20 г.
1. Создать шаблон бланка в среде Microsoft Word и произвести его
заполнение.
2. Создать источник данных в среде Microsoft Excel и произвести
объединение документов. Источник данных должен содержать не менее
10 записей.
План выполнен –
Дата заполнения «__»________ ____г.
31
Вариант 8
1. Создать шаблон бланка в среде Microsoft Word и произвести его
заполнение.
2. Создать источник данных в среде Microsoft Word и произвести
объединение документов. Источник данных должен содержать не менее
10 записей.
Сметная стоимость и себестоимость работ,
выполненных с начала года по отчетный период
Фактическая
Плановая
Работы
Сметная стоимость
Себестоимость
№ строки
КОРЕШОК ОТЧЕТА О СЕБЕСТОИМОСТИ
СТРОИТЕЛЬНЫХ И МОНТАЖНЫХ РАБОТ
за январь – ______ 20 г.
ОТЧЕТ О СЕБЕСТОИМОСТИ СТРОИТЕЛЬНЫХ
И МОНТАЖНЫХ РАБОТ
за январь –
20
г.
Все расходы
покрываемые в
порядке
компенсаций и
льгот сверх сметной
стоимости
Дата заполнения «____»____________ ____г.
32
Вариант 9
1. Создать шаблон бланка в среде Microsoft Word и произвести его
заполнение.
2. Создать источник данных в среде Microsoft Word и произвести
объединение документов. Источник данных должен содержать не менее
10 записей.
Израсходовано топлива за месяц
В том числе на отпуск
электрической и тепловой
энергии
Всего
Вид
топлива
Количество топлива
на начало месяца
КОРЕШОК ОТЧЕТА О РАБОТЕ ТЕПЛОВОЙ
ЭЛЕКТРОСТАНЦИИ
за
20
г.
Баланс топлива
На начало месяца всего
Остаток за месяц всего
ОТЧЕТ О РАБОТЕ ТЕПЛОВОЙ ЭЛЕКТРОСТАНЦИИ
за
20
г.
Баланс топлива
Натурального
Условного
Расход топлива выше нормы –
Дата заполнения «___»________ ____г.
Остаток
топлива за
месяц
33
Вариант 10
ОТЧЕТ О ВЫПОЛНЕНИИ ПЛАНА
КАПИТАЛЬНОГО РЕМОНТА ОСНОВНЫХ ФОНДОВ
за полугодие 20
г.
Выполнение плана сдачи капитального ремонта
по сметной стоимости
в порядке
централизованного
ремонта
Фактически выполнено за период с
начала года
В том числе
из него
подрядным
способом на
предприятиях
своего
Министерства
Всего
На период
сначала года
Объекты
На отчетный
год
По плану
Код
КОРЕШОК ОТЧЕТА О ВЫПОЛНЕНИИ ПЛАНА
КАПИТАЛЬНОГО РЕМОНТА ОСНОВНЫХ ФОНДОВ
за
полугодие 20 г.
1. Создать шаблон бланка и произвести его заполнение.
2. Создать шаблон бланка в среде Microsoft Word, источник данных
в среде Microsoft Excel и произвести объединение документов. Источник
данных должен содержать не менее 10 записей.
План выполнен –
Дата заполнения «__»________ ____г.
34
Лабораторная работа № 3.
Технология решения информационно-поисковых задач и
формирование отчетов в среде табличного процессора Excel
Цель работы: изучение технологии и приобретение практических
навыков решения информационно-поисковых задач и формирования
отчетов в среде табличного процессора Excel.
Содержание
Изучаются вопросы:
1. Понятие списка.
2. Поиск данных с помощью средств Автофильтра и Расширенный
фильтр. Использование встроенных функций для поиска данных.
3. Понятие сводной таблицы. Создание и работа со сводной
таблицы.
4. Вычисляемые поля и группировка элементов. Использование
формул массива.
Выполняется вариант задания.
Указания
Список  набор строк электронных таблиц, содержащих связанные
данные. Список можно представлять в виде базы данных, в которой
строки выступают в качестве записей, а столбцы являются полями.
Первая строка списка при этом содержит названия столбцов.
Первая строка списка будет содержать заголовки столбцов, которые
называются именами полей. Рекомендуется использовать только
уникальные заголовки. Располагайте все столбцы рядом друг с другом.
Используйте отдельный столбец для каждого поля или элемента
информации.
После ввода всех заголовков настройте их ширину, вид шрифта,
выравнивание и другие параметры форматирования. Это позволит
строке заголовка выделяться на общем фоне.
Фильтрация данных
Фильтрация данных позволяет легко и быстро найти нужные для
работы данные в диапазоне ячеек или таблице. Отфильтровав данные в
диапазоне ячеек или таблице, можно применить фильтр повторно, чтобы
обновить результаты, либо очистить фильтр, чтобы отобразить все
данные.
Чтобы приступить к работе с фильтрами, выделите хотя бы одну
ячейку в диапазоне ячеек или таблице Excel и нажмите кнопку Фильтр в
группе Сортировка и фильтр на вкладке Данные (рис. 7).
35
Рис. 7. Группа Сортировка и фильтр вкладки Данные
В отфильтрованных данных отображаются только строки,
соответствующие заданным условиям, а ненужные строки скрываются.
После фильтрации данные в этом подмножестве можно копировать,
искать, изменять, форматировать, преобразовывать в диаграммы и
выводить на печать; при этом их местонахождение и порядок не
изменятся.
Можно фильтровать данные по нескольким столбцам. При
применении нескольких фильтров каждый новый фильтр накладывается
на фильтр, примененный до него, и в еще большей степени
ограничивает подмножество данных.
С помощью Автофильтра можно создать фильтры трех типов: по
списку значений, по формату или по условиям. Все они являются
взаимоисключающими в пределах диапазона ячеек или столбца
таблицы. Например, можно выполнить фильтрацию по цвету ячеек или
по списку чисел, однако использовать фильтры обоих типов
одновременно нельзя; точно так же необходимо выбрать один вариант,
если требуется выполнить отбор по значкам или на основе
настраиваемого фильтра.
Чтобы определить, применен ли фильтр, обратите внимание на
значок в заголовке столбца:
 стрелка списка
означает, что фильтрация включена, но не
применена;
 кнопка
означает, что фильтр применен.
Фильтрация текста
Самыми быстрыми способами фильтрации являются выбор значений
в списке и поиск. При щелчке стрелки в столбце, для которого включена
фильтрация, все значения в этом столбце появляются в списке. Три
способа быстрой фильтрации данных показаны на рис. 8.
Фильтрация чисел
Чтобы применить фильтр к диапазону ячеек, выделите диапазон
ячеек, содержащих числовые данные и на вкладке Данные в группе
Сортировка и фильтр нажмите кнопку Фильтр.
Чтобы применить фильтр к таблице, поместите курсор в любую
ячейку таблицы и щелкните стрелку
рядом с заголовком столбца. В
36
списке чисел установите или снимите флажки для чисел, по которым
требуется выполнить фильтрацию.
Список может содержать до 10 000 чисел. Если список слишком
велик, снимите в его верхней части флажок (Выделить все) и выберите
конкретные числа, по которым требуется выполнить отбор.
1. Поиск
необходимого
текста или чисел с
помощью поля Поиск
2. Установка
и
снятие
флажков
для
отображения значений,
найденных в столбце
данных
3. Использование
расширенных условий для
поиска
определенных
значений
Рис. 8 Способы быстрой фильтрации
Фильтрация значений даты и времени
Чтобы применить фильтр к диапазону ячеек, выделите диапазон
ячеек, содержащих данные, и на вкладке Данные в группе Сортировка и
фильтр нажмите кнопку Фильтр.
Чтобы применить фильтр к таблице, поместите курсор в любую
ячейку таблицы и щелкните стрелку
рядом с заголовком столбца и в
списке, содержащем значения даты и времени, установите или снимите
флажки для значений, по которым требуется выполнить фильтрацию
Очистка фильтра
Чтобы очистить фильтр, примененный к одному столбцу диапазона
или таблицы, состоящих из нескольких столбцов, нажмите кнопку
Фильтр в заголовке столбца, а затем выберите команду Снять фильтр с
<имя столбца>.
Чтобы очистить все фильтры на листе и вывод всех строк на экран,
на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Очистить
(рис. 7).
Отбор строк с помощью расширенного фильтра
Если требуется отфильтровать данные с помощью сложных условий,
можно использовать диалоговое окно Расширенный фильтр.
Чтобы вызвать расширенный фильтр выберите команду
Дополнительно на вкладке Данные в группе Сортировка.
37
В расширенном фильтре можно использовать перечисленные ниже
операторы для сравнения двух значений (табл. 3). Результатом
сравнения является логическое значение – ИСТИНА либо ЛОЖЬ.
Таблица 3
Оператор сравнения
= (знак равенства)
> (знак "больше")
< (знак "меньше")
>= (знак "больше или равно")
<= (знак "меньше или равно")
<> (знак "не равно")
Значение
Равно
Больше
Меньше
Больше или равно
Меньше или равно
Не равно
При фильтрации текстовых данных в Excel не учитывается регистр
букв. Однако для поиска с учетом регистра можно воспользоваться
формулой.
Что запустить расширенный фильтр необходимо определить область
для хранения условий отбора (диапазон ячеек). Каждое условие
записывается в две ячейки: в верхнюю – имя поля, в нижнюю – знак
отношения
Чтобы найти строки, отвечающие нескольким условиям для одного
столбца, введите условия непосредственно одно под другим в разных
строках диапазона условий.
Щелкните ячейку в диапазоне. На вкладке Данные в группе Сортировка
и фильтрация выберите команду Дополнительно (рис. 9).
Рис. 9. Окно Расширенного фильтра
Чтобы показать результат фильтрации, скрыв ненужные строки,
установите переключатель фильтровать список на месте. Чтобы
скопировать отфильтрованные строки в другую область листа,
установите переключатель скопировать результат в другое место, перейдите
в поле Поместить результат в диапазон и затем укажите верхнюю левую
ячейку области, в которую требуется вставить строки.
Введите в поле Диапазон условий ссылку на диапазон условий,
включающий подписи столбцов условий.
38
Для поиска строк, отвечающих нескольким условиям для нескольких
столбцов, когда истинным может быть любое из условий, введите
условия в разные столбцы и строки диапазона условий.
Щелкните ячейку в диапазоне. На вкладке Данные в группе Сортировка
и фильтрация выберите команду Дополнительно. Заполните поля окна
Расширенного фильтра.
Сводная таблица
При создании отчета сводной таблицы на основе данных листа они
становятся для него исходными данными. Чтобы использовать в
качестве источника данные листа, щелкните ячейку в диапазоне,
содержащем данные. Чтобы использовать в качестве источника данные
в таблице Excel, щелкните ячейку в пределах таблицы Excel. В
диапазоне ячеек должны быть заголовки столбцов, а в таблице
отображаться заголовки. Кроме того, в диапазоне или таблице не
должно быть пустых строк.
Чтобы вставить сводную таблицу на вкладке Вставить в группе
Таблицы выберите пункт Сводная таблица или щелкните стрелку под
пунктом Сводная таблица и выберите команду Сводная таблица (рис. 10).
Откроется диалоговое окно Создание сводной таблицы.
Рис. 10. Окно Сводной таблицы группы Таблицы на вкладке Вставить
Если в разделе Выберите данные для анализа переключатель
установлен в положение Выбрать таблицу или диапазон, проверьте
диапазон ячеек, содержащий базовые данные, в поле Таблица или
диапазон.
В разделе Укажите, куда следует поместить отчет сводной таблицы задайте
расположение таблицы. Чтобы поместить отчет сводной таблицы на
новый лист, начиная с ячейки A1, щелкните пункт На новый лист. Чтобы
поместить отчет сводной таблицы на существующий лист, выберите
пункт На существующий лист и введите первую ячейку диапазона, в
который следует поместить отчет сводной таблицы, в поле Диапазон. И
нажмите кнопку ОК.
39
Microsoft Excel добавит пустой отчет сводной таблицы в указанное
место и откроет список полей сводной таблицы, с помощью которого
можно добавить поля, создать макет и настроить отчет сводной
таблицы.
Чтобы добавить поле в область раздела макета, используемую по
умолчанию, установите флажок рядом с именем поля в разделе полей.
Чтобы поместить поле в определенную область раздела макета,
щелкните его имя в разделе полей правой кнопкой мыши и выберите
пункт Добавить в фильтр отчета, Добавить в названия столбцов, Добавить в
названия строк или Добавить в значения. Можно также щелкнуть имя поля в
разделе полей и, удерживая его, перетащить поле в любую область
раздела макета.
Задача. На листе сформируем таблицу данных, в соответствии
с табл. 4.
Таблица 4
Исходные данные базы данных
Наименование
товара
Колбаса
Творог
Хлеб
Булка Дорожная
Булка Дорожная
Молоко
Творог
Мясо
Сыр
Хлеб
Хлеб
Конфеты
Пиво
Колбаса
Конфеты
Пиво
Сыр
Булка Дорожная
Колбаса
Закупочная Отпускная Транспортные
Количество
цена (руб.)
цена
расходы
02.03.13 Белмясо
Белгород
296,54
341,02
268,12
100
05.03.13 Белый город Белгород
92,04
105,85
369,48
500
05.03.13 Колос
Белгород
6,35
7,30
125,44
200
07.03.13 Колос
Белгород
7,69
8,84
127,36
180
07.03.13 Колос
Белгород
7,69
8,84
127,69
50
05.03.13 Авида
Старый Оскол
36,45
41,92
405,36
200
09.03.13 Авида
Старый Оскол
95
109,25
398,78
160
05.03.13 Останкино Белгород
198
227,70
236,77
20
09.03.13 Авида
Старый Оскол
217
249,55
354,27
100
02.03.13 Колос
Белгород
6,38
7,34
136,88
170
07.03.13 Колос
Белгород
6,38
7,34
142,69
50
05.03.13 Славянка
Старый Оскол
126,87
145,90
156,55
100
02.03.13 Балтика
Санкт-Петербург
36,48
41,95
963,54
400
05.03.13 Останкино Губкин
196,45
225,92
302,55
35
07.03.13 Славянка
Старый Оскол
96,47
110,94
569,44
200
09.03.13 Балтика
Санкт-Петербург
32,45
37,32
896,33
120
09.03.13 Авида
Старый Оскол
187,33
215,43
305,45
60
02.03.13 Колос
Белгород
7,73
8,89
101,56
120
07.03.13 Белмясо
Белгород
224,69
258,39
296,45
115
Дата
Поставщик
Город
Для расчета прибыли добавим поле
расчета (рис. 11): =(F2-E2)*H2-G2.
Прибыль,
введем формулу для
Рис. 11. Ввод формулы для расчета поля Прибыль
С помощью маркера заполнения скопируем эту формулу в ячейки
В результате лист примет вид, представленный на рис. 12.
I2:I20.
40
Рис. 12. Фрагмент данных
Сортировка баз данных
Для сортировки базы выделим ячейки A1:I20, выберем на ленте
команду Настраиваемая сортировка. И отсортируем таблицу по
возрастанию прибыли (рис. 13).
Рис. 13. Параметры сортировки
Добавление итогов в таблицу данных
Посчитать суммарную прибыль при
продаже всех продуктов можно при помощи
функции СУММ.
Если
же
воспользоваться
командой
Промежуточные итоги меню Данные, то появляется
возможность рассчитать сумму прибыли по
каждому товару или по каждому поставщику.
Итак, для суммирования прибыли по каждому
из товаров сделаем следующее: отсортируем
таблицу по наименованию товаров и выполним
Рис. 14. Окно
команду Промежуточные итоги (рис. 14). Таблица
Промежуточные итоги
примет вид, изображенный на рис. 15.
41
Рис. 15. Добавление в таблицу промежуточных итогов
Фильтрация базы данных
Процесс поиска и отбора информации называется фильтрацией. В
Excel можно применить два вида фильтра, а именно автофильтр и
расширенный фильтр.
Для включения автофильтра необходимо:
Щелкнуть в любом месте таблицы данных, в нашем случае диапазон
A1:I20. Выбрать команду Фильтр в меню Данные и таблица примет вид,
изображенный на рис. 16. В качестве условия отбора можно выбрать
либо любое значение из списка каждого поля, либо включить
Пользовательский фильтр.
Рис. 16. Добавление Автофильтра в таблицу данных
Выберем в качестве условия значение фильтра по полю Наименование
– Колбаса. В результате в таблице останется информация,
касающаяся только поставок колбасы (рис. 17).
товара
Рис. 17. Фрагмент отфильтрованной таблицы данных
42
Расширенный фильтр.
Для выделения из таблицы более сложных условий можно
воспользоваться командой Расширенный фильтр. Рассмотрим работу с
расширенным фильтром на примере выделения из таблицы записей
белгородских производителей хлеба. Для этого определим область для
хранения условий отбора. Каждое условие записывается в две ячейки: в
верхнюю – имя поля, в нижнюю – знак отношения (>, <, >= ,<= ,< >) и
значение. В нашем случае в ячейку K1 введем Наименование товара, в
ячейку K2 – Хлеб, в ячейку L1 – Город, в ячейку L2 – Белгород (рис. 18, а).
а)
б)
Рис. 18. Область условий Расширенного фильтра:
а – условия фильтра, соединенные логическим действием И,
б – условия фильтра, соединенные логическим действием ИЛИ
Теперь выполним команду Дополнительно меню Данные (рис. 19).
Рис. 19. Условия отбора расширенного фильтра
В данном случае два условия соединены логическим действием И.
Для объединения с помощью ИЛИ необходимо между именем поля и
условием пропустить строчку (рис. 18, б).
При копировании отфильтрованных данных в другое место
необходимо, чтобы копируемый диапазон начинался со строки, в
которой указываются имена полей таблицы.
Сводная таблица
Сводные таблицы – одно из наиболее мощных средств по работе с
таблицами данных. Они полезны как для анализа, так и для обобщения
информации, хранящейся в базе.
Создадим из нашей базы сводную таблицу для расчета прибыли по
каждому товару. Выполним команду Сводная таблица из меню Вставка. В
43
диалоговом окне необходимо указать диапазон данных таблицы и место
расположения сводной таблицы (рис. 20).
Рис. 20. Окно команды Сводная таблица
На новом листе в области задач сводной таблицы создадим макет по
подсчету прибыли.
Поля базы данных, на основании которой строится сводная таблица,
представлены в области создания сводной таблицы в виде списка полей
(рис. 21). Перетаскивая их в соответствующие области, пользователь
задает необходимую структуру сводной таблицы.
В окне имеются следующие области:
Названия столбцов – для использования данных поля, расположенного
в этой области, в качестве заголовков столбцов;
Названия строк – для использования данных поля, расположенного в
этой области, в качестве заголовка строки;
Значения – для суммирования значений поля, расположенного в этой
области, в ячейках сводной таблицы.
Рис. 21. Макет создания сводной таблицы
44
Задания
Вариант 1
1. В среде Microsoft Excel создать представленный список данных,
содержащий не менее 15 записей, и произвести поиск данных с
помощью средств автофильтр и расширенный фильтр.
Фамилия,
Имя,
Отчество
Табельный
номер
Отдел
Должность
Оклад
Трудовой
договор
(номер)
Трудовой
договор
(дата)
2. На основании введенных данных получить сводную таблицу.
Отдел 1
Трудовой Трудовой
договор
договор
(номер)1
(номер)2
Ф.И.О.1
Оклад
Должность1
Ф.И.О.2
Должность2 Ф.И.О.1
…
…
Трудовой
договор
(номер)1
…
Отдел 2
Трудовой
договор
(номер)2
…
…
…
…
…
Вариант 2
1. В среде Microsoft Excel создать представленный список данных,
содержащий не менее 15 записей, и произвести поиск данных с
помощью средств автофильтр и расширенный фильтр.
Табельный
номер
Фамилия,
Имя,
Отчество
Профессия,
должность
Оклад
Условия
труда
Дата
принятия
на работу
Номер
телефона
2. На основании введенных данных получить сводную таблицу.
Условия
труда 1
Условия
труда 2
…
Ф.И.О. 1
Ф.И.О. 2
Профессия 1
Дата
Дата
принятия на принятия на
работу 1
работу 2
Оклад
…
Профессия 2
…
Дата
Дата
принятия на принятия на … … … …
работу 1
работу 2
Ф.И.О. 1
…
Вариант 3
1. В среде Microsoft Excel создать представленный список данных,
содержащий не менее 15 записей, и произвести поиск данных с
помощью средств автофильтр и расширенный фильтр.
Табельный Фамилия,
Организация
номер Имя, Отчество
Оклад
Отдел
Категория
Должность
персонала
45
2. На основании введенных данных получить сводную таблицу.
Организация 1
Организация 2
…
Ф.И.О. 1
Ф.И.О. 2
Ф.И.О. 1
…
Категория персонала 1
Отдел 1
Отдел 2
Оклад
…
Категория персонала 2
…
Отдел 1
Отдел 2 … … … …
Вариант 4
1. В среде Microsoft Excel создать представленный список данных,
содержащий не менее 15 записей, и произвести поиск данных с
помощью средств автофильтр и расширенный фильтр.
Размер Количество
Дата
Табельный Фамилия,
Профессия, Условия
тарифной
дней
предоставления
номер Имя, Отчество должность
труда
ставки
отпуска
отпуска
2. На основании введенных данных получить сводную таблицу.
Ф.И.О.1
Ф.И.О.2
Условия
труда 1
Условия
труда 2
…
Профессия 1
Табельный
Табельный
номер 1
номер 2
Количество дней отпуска
…
Профессия 2
Табельный
Табельный
номер 1
номер 2
…
… … … …
Ф.И.О.1
…
Вариант 5
1. В среде Microsoft Excel создать представленный список данных,
содержащий не менее 15 записей, и произвести поиск данных с
помощью средств автофильтр и расширенный фильтр.
Табельный
номер
Фамилия,
Профессия,
Имя,
Отдел
должность
Отчество
Продолжительность
отпуска
Дата
Дата конца
начала
отпуска
отпуска
2. На основании введенных данных получить сводную таблицу.
Отдел1
Отдел2
…
Ф.И.О. 1
Ф.И.О. 2
Ф.И.О. 1
…
Продолжительность отпуска 1
Дата
Дата
начала
начала
…
отпуска 1
отпуска 2
Продолжительность
отпуска
Продолжительность отпуска 2
Дата
Дата
начала
начала
…
отпуска1
отпуска 2
…
…
…
…
46
Вариант 6
1. В среде Microsoft Excel создать представленный список данных,
содержащий не менее 15 записей, и произвести поиск данных с
помощью средств автофильтр и расширенный фильтр.
Табельный
номер
Фамилия,
Имя,
Отчество
Семейное
положение
Кол-во
детей
Военнообязанность
Домашний
адрес
Телефон
2. На основании введенных данных получить сводную таблицу.
Военнообязанный 1
Телефон1
Телефон2
Семейное
положение1
Семейное
положение2
…
…
Военнообязанный 2
Телефон1
Телефон2
…
… … … …
Кол-во детей
Ф.И.О.1
Ф.И.О.2
Ф.И.О.1
…
Вариант 7
1. В среде Microsoft Excel создать представленный список данных,
содержащий не менее 15 записей, и произвести поиск данных с
помощью средств автофильтр и расширенный фильтр.
Табельный
номер
Фамилия,
Имя,
Отчество
Профессия,
должность
Оклад
Условия
труда
Дата
принятия
на работу
Номер
телефона
2. На основании введенных данных получить сводную таблицу.
Профессия1
Профессия2
…
Ф.И.О.1
Ф.И.О.2
Ф.И.О.1
…
Условия труда 1
Условия труда 2
…
Дата
Дата
Дата
Дата
принятия на принятия на … принятия на принятия на … … … …
работу 1
работу 2
работу 1
работу 2
Оклад
Вариант 8
1. В среде Microsoft Excel создать представленный список данных,
содержащий не менее 15 записей, и произвести поиск данных с
помощью средств автофильтр и расширенный фильтр.
Название
книги
Автор
Год
издания
Место
издания
Количество
экземпляров
Жанр
книги
Серийный
номер
47
2. На основании введенных данных получить сводную таблицу.
Автор1
Автор2
…
Название книги1
Название книги2
Название книги1
…
Год издания1
Жанр
Жанр
книги 1
книги 2
Кол-во экземпляров
…
Год издания2
Жанр
Жанр
книги 1 книги 2
…
…
…
…
…
Вариант 9
1. В среде Microsoft Excel создать представленный список данных,
содержащий не менее 15 записей, и произвести поиск данных с
помощью средств автофильтр и расширенный фильтр.
Табельный
номер
Фамилия,
Имя,
Отчество
Отдел
Профессия,
должность
Продолжительность отпуска
Дата
начала
отпуска
Дата конца
отпуска
2. На основании введенных данных получить сводную таблицу.
Отдел 1
Отдел 2
…
Ф.И.О. 1
Профессия 1
Дата начала Дата начала …
отпуска 1
отпуска 2
Продолжительность
отпуска
Профессия 2
…
Дата начала Дата начала … … … …
отпуска 1
отпуска 2
Ф.И.О. 2
Ф.И.О. 1
…
Вариант 10
1. В среде Microsoft Excel создать представленный список данных,
содержащий не менее 15 записей, и произвести поиск данных с
помощью средств автофильтр и расширенный фильтр.
Фирма
Наименование
Страна
Год
изгототовара
изготовитель изготовления
витель
Номер
грузовой
таможенной
декларации
Цена, Количество,
руб.
шт.
2. На основании введенных данных получить сводную таблицу.
Фирма изготовитель 1
Фирма изготовитель 2
…
Год
Год
…
Год
Год
… … … …
изготовления изготовления
изготовления изготовления
1
2
1
2
Страна
Количество,
Наименование
изготовитель 1
шт.
товара. 1
Наименование
товара 2
Страна
Наименование
изготовитель 2
товара 1
…
…
48
Лабораторная работа № 4.
Применение стандартных функций
для решения инвестиционных задач
Цель работы: изучение технологии построения компьютерных
моделей с использованием финансовых функций и приобретение
практических навыков построения вычислительных схем.
Содержание
Изучаются вопросы:
1. Понятие основных стандартных функций финансового раздела.
2. Технология
построения
компьютерных
моделей
с
использованием стандартных функций.
Выполняется вариант задания.
Указания
Среди функций, имеющихся в Excel, раздел, посвященный
финансовым операциям, занимает значительное место. При помощи
представленных в нем функций можно выполнять вычисления,
связанные с ценными бумагами, амортизацией, а также связанные с
оценкой эффективности принимаемого решения о расчете будущей
стоимости вклада, о величине периодических выплат, прогнозировании
значений процентных ставок и т.д.
Задача 1. Рассчитать, какая сумма окажется на счете, если
27 тыс. руб. положены на 10 лет по 18,5% годовых. Проценты
начисляются каждые полгода.
Решение:
Функция БС рассчитывает будущую стоимость периодических
постоянных платежей и будущее значение единой суммы вклада или
займа на основе постоянной процентной ставки.
Будущую стоимость единовременной суммы вклада, по которой
начисляются сложные проценты определенное количество периодов,
рассчитывают по формуле
fv = pv  (1 + r)n,
где fv – будущая стоимость вклада или займа; pv – текущая стоимость вклада
(займа); r – процентная ставка по вкладу (займу); n – общее число периодов
начисления процентов.
Синтаксис БС(Ставка; Кпер; Плт; [Пс]; [Тип]),
где Ставка – процентная ставка за период; Кпер – общее количество периодов
платежей по аннуитету; Плт – выплата, производимая в каждый период; Пс –
приведенная к текущему моменту стоимость или общая сумма, которая на
текущий момент равноценна ряду будущих платежей; Тип – число 0 или 1,
обозначающее срок выплаты.
49
В зависимости от базы начисления значения аргументов должны
быть пересчитаны (табл. 5).
Модель решения задачи приведена на рис. 22.
Таблица 5
Схема начисления процентов
Метод начисления
процентов
Годовой
Полугодовой
Квартальный
Ежемесячный
Ежедневный
Общее число периодов
начисления процентов
n
n2
n4
n  12
n  365
Ставка процента на период
начисления, %
r
r/2
r/4
r/12
r/365
Рис. 22. Схема решения задачи
Задача 2. Фирме потребуется 500 000 руб. через 7 лет. В настоящее
время фирма располагает деньгами и готова положить их на депозит
отдельным вкладом. Определить необходимую сумму вклада, если
ставка процента по нему составляет 12% в год.
Решение:
Эта задача базируется на понятии текущей стоимости будущих
доходов и расходов, т.е. на начальный момент времени полученная в
будущем сумма денег имеет меньшую стоимость, чем ее эквивалент,
полученный в начальный момент времени.
В задаче известно будущее значение вклада и требуется определить
текущее значение вложений, т.е. сумму, которую необходимо положить
на счет сегодня, чтобы в конце n-го периода она достигла заданного
значения. Это значение можно получить из формулы
fv
pv 
,
(1  r ) n
где pv – текущая стоимость фиксированных периодических платежей; fv –
будущая стоимость вклада; r – постоянная процентная ставка; n – общее число
периодических выплат.
50
Для решения задачи воспользуемся функцией ПС, которая
предназначена для расчета текущей стоимости, как единой суммы
вклада (займа), так и будущих фиксированных периодических платежей.
Синтаксис ПС(Ставка; Клер; Плт; [Бс]; [Тип]),
где Ставка – процентная ставка за период; Кпер – общее число периодов платежей
для ежегодного платежа; Плт – выплата, производимая в каждый период и не
меняющаяся на протяжении всего периода ежегодного платежа; Бс – значение
будущей стоимости, т. е. желаемого остатка средств после последнего платежа;
Тип – число 0 или 1, обозначающее, когда должна производиться выплата.
Модель решения задачи приведена на рис. 23.
Рис. 23. Модель решения задачи
Функция ПС также позволяет решить задачу, в которой требуется
найти текущую стоимость будущих периодических постоянных
платежей, которые производятся в начале или в конце каждого
расчетного периода. Согласно концепции временной стоимости, чем
дальше от настоящего момента будет поступление или расходование
средств, тем меньшую текущую ценность оно представляет. Таким
образом, при равных условиях текущая стоимость вкладов
пренумерандо больше, чем текущая стоимость вкладов постнумерандо.
Расчет текущей
стоимости
серии будущих постоянных
периодических платежей, пренумерандо каждого периода и
дисконтированных нормой дохода r, выполняется по формуле:
1
1
(1  r ) n
1  r  ,
pv  pmt
r
где pmt – фиксированная периодическая сумма платежа.
В общем виде для решения таких задач функция ПС примет вид:
ПС(Ставка; Кпер; Плт; ; 1).
Для расчета текущей стоимости постоянных периодических выплат
постнумерандо формула имеет вид:
51
1
(1  r ) n
.
pv  pmt
r
И соответствующая этому расчету функция Excel
ПС(Ставка; Кпер; Плт; ;).
По умолчанию аргумент Тип = 0, поэтому его можно не указывать.
1
Задача 3. Рассчитать, через сколько лет вклад размером 1 млн руб.
достигнет величины 1 млрд руб., если годовая ставка процента по
вкладу 16,79% и начисление процентов производится ежеквартально.
Решение:
Общее число периодов постоянных выплат, необходимых для
достижения заданного будущего значения, а также число периодов,
через которое начальная сумма вклада достигнет заданного значения
можно найти с помощью встроенной функции КПЕР.
Синтаксис КПЕР(Ставка; Плт; Пс; [Бс]; [Тип]),
где Ставка – процентная ставка за период; Плт – выплата, производимая в каждый
период; Пс – приведенная (текущая) стоимость; Бс – значение будущей
стоимости; Тип – число 0 или 1, обозначающее, когда должна производиться
выплата.
Модель решения задачи приведена на рис. 24.
Рис. 24. Схема решения задачи
Задача 4. Компании потребуется 1 млн руб. через 2 года. Компания
готова вложить 50 тыс. руб. сразу и по 25 тыс. руб. каждый
последующий месяц. Каким должен быть процент на инвестированные
средства, чтобы получить необходимую сумму в конце второго года?
Решение:
Функция СТАВКА определяет значение процентной ставки за один
расчетный период. Для нахождения годовой процентной ставки
52
полученное значение следует умножить на число расчетных периодов,
составляющих год.
Синтаксис СТАВКА(Клер, Плт, Пс, [Бс], [Тип], [Прогноз]).
где Кпер – общее число периодов платежей для ежегодного платежа; Плт –
выплата, производимая в каждый период; Пс – приведенная (текущая)
стоимость, т.е. общая сумма, которая на данный момент равноценна ряду
будущих платежей; Бс – значение будущей стоимости, т.е. желаемого остатка
средств после последней выплаты; Тип – число 0 или 1, обозначающее, когда
должна производиться выплата; Прогноз – предполагаемая величина ставки.
Модель решения задачи приведена на рис. 25.
Рис. 25. Модель решения задачи
Задача 5. Определить схему погашения займа в 70 тыс. руб.,
выданного сроком на 3 года по 17% годовых.
Решение:
С помощью встроенных функций Excel позволяет вычислять
периодические платежи, осуществляемые на основе постоянной
процентной ставки, платежи по процентам за конкретный период,
основные платежи по займу (за вычетом процентов) за конкретный
период.
Для рассматриваемой задачи заем погашается одинаковыми
платежами в конце каждого расчетного периода. Будущая стоимость
этих платежей будет равна сумме займа с начисленными процентами к
концу последнего расчетного периода, если в нем предполагается
полное погашение займа.
С другой стороны, текущая стоимость выплат по займу должна
равняться настоящей сумме займа. Если известна сумма займа, ставка
процента, срок, на который выдан заем, то можно рассчитать сумму
постоянных периодических платежей, необходимых для равномерного
погашения займа с помощью функции ПЛТ.
53
Синтаксис ПЛТ(Ставка; Кпер; Плт; [Бс]; [Тип]),
где Ставка – процентная ставка по ссуде; Кпер – общее число выплат по ссуде; Пс
– приведенная к текущему моменту стоимость или общая сумма, которая на
текущий момент равноценна ряду будущих платежей; Бс – требуемое значение
будущей стоимости; Тип – число 0 (нуль) или 1, обозначающее, когда должна
производиться выплата.
Вычисленные платежи включают в себя сумму процентов по
непогашенной части займа и основную выплату по займу. Обе величины
зависят от номера периода и могут быть рассчитаны при помощи
функций ПРПЛТ и ОСПЛТ.
Функция ПРПЛТ вычисляет платежи по процентам за заданный
период на основе периодических постоянных выплат и постоянной
процентной ставки.
Синтаксис ПРПЛТ(Ставка, Период, Кпер, Пс, [Бс], [Тип]),
где Ставка  процентная ставка за период; Период  период, для которого
требуется найти платежи по процентам; Кпер  общее число периодов платежей
по аннуитету; Пс  приведенная к текущему моменту стоимость; Бс  значение
будущей стоимости; Тип  число 0 или 1, обозначающее срок выплаты.
Функция ОСПЛТ вычисляет величину основного платежа (выплаты
задолженности) по займу, который погашается равными платежами в
конце или начале каждого расчетного периода, на указанный период
ОСПЛТ(Ставка, Период, Клер, Пс, [Бс], [Тип]),
где Ставка  процентная ставка за период; Период  период: значение должно
находиться в диапазоне от 1 до Кпер; Кпер  общее число периодов платежей для
ежегодного платежа; Пс  приведенная (текущая) стоимость; Бс  значение
будущей стоимости, т. е. желаемого остатка средств после последнего платежа;
Тип  число 0 или 1, обозначающее, когда должна производиться выплата.
Модель решения задачи приведена на рис. 26.
Рис. 26. Схема и результаты решения задачи
54
Задания
Вариант 1
Задача 1. Сумма 20 тыс. руб. размещена под 9% годовых на 3 года.
Проценты начисляются раз в квартал. Какая сумма будет на счете?
Задача 2. Ожидается, что ежегодные доходы от реализации
проекта составят 33 млн руб. Необходимо рассчитать срок окупаемости
проекта, если инвестиции к началу поступления доходов составят
100 млн руб., а норма дисконтирования 12,11%.
Задача 3. Предположим, что необходимо накопить 100 тыс. руб. за
3 года, откладывая постоянную сумму в конце каждого месяца. Какой
должна быть эта сумма, если норма процента о вкладу составляет 12 %
годовых?
Вариант 2
Задача 1. Предположим, есть два варианта инвестирования средств
в течении 5 лет: в начале каждого года под 22% и в конце года под 32%
годовых. Пусть ежегодно вносится 300 тыс. руб. Определить, сколько
денег окажется на счете в конце 5-го года для каждого варианта.
Задача 2. Ссуда размером 66 000 руб., выданная под 36% годовых,
погашается обычными ежемесячными платежами 6630 руб. Рассчитать
срок погашения ссуды.
Задача 3. Банк выдал ссуду 500 тыс. руб. на 5 лет под 19% годовых.
Ссуда выдана в начале года, а погашение начинается в конце года
одинаковыми платежами. Определить размер ежегодного погашения
ссуды.
Вариант 3
Задача 1. Банк принимает вклад на срок 3 месяца с объявленной
годовой ставкой 10% или на 6 месяцев под 12% годовых. Как выгодно
вкладывать деньги на полгода: дважды на 3 месяца или один раз на 6
месяцев? Сумма вклада – 100 тыс. руб.
Задача 2. Предположим, что компания отказалась от ежемесячных
выплат и готова положит на депозит 40 тыс. руб. сроком на 2 года.
Определить минимальную годовую процентную ставку для будущей
стоимости вклада 1 млн руб.
Задача 3. Вычислить платежи по процентам за первый месяц от
трехгодичного займа в 80 тыс. руб. из расчета 10% годовых.
55
Вариант 4
Задача 1. Предположим, рассматривается два варианта покупки
дома: заплатить сразу 2 300 000 руб. или в рассрочку – по 20 500 руб.
ежемесячно в течение 15 лет. Определить, какой вариант
предпочтительнее, если ставка процента – 8% годовых.
Задача 2. Рассчитать процентную ставку для четырехлетнего займа
в 70 000 руб. с ежемесячным погашением по 2500 руб. при условии, что
заем полностью погашается.
Задача 3. Предположим, что необходимо накопить 200 тыс. руб. за
5 лет, откладывая постоянную сумму в конце каждого месяца. Какой
должна быть эта сумма, если норма процента о вкладу составляет 14 %
годовых?
Вариант 5
Задача 1. Фирме потребуется 1 млн руб. через 5 лет. В настоящее
время фирма располагает деньгами и готова положить их на депозит
отдельным вкладом. Определить необходимую сумму вклада, если
ставка процента по нему составляет 15% в год.
Задача 2. Рассчитать, через сколько лет вклад размером 1 млн руб.
достигнет величины 5 млн руб., если годовая савка процента по вкладу
18% и начисления процентов производится ежеквартально.
Задача 3. Банк выдал ссуду 700 тыс. руб. на 7 лет под 17% годовых.
Ссуда выдана в начале года, а погашение начинается в конце года
одинаковыми платежами. Определить размер ежегодного погашения
ссуды.
Вариант 6
Задача 1. Сумма 50 тыс. руб. размещена под 12% годовых на 7 лет.
Проценты начисляются раз в год. Какая сумма будет на счете?
Задача 2. Ожидается, что ежегодные доходы от реализации проекта
составят 15 млн руб. Необходимо рассчитать срок окупаемости проекта,
если инвестиции к началу поступления доходов составят
120 млн руб., а норма дисконтирования 14%.
Задача 3. Предположим, что необходимо накопить 350 тыс. руб. за
4 года, откладывая постоянную сумму в конце каждого месяца. Какой
должна быть эта сумма, если норма процента о вкладу составляет 15%
годовых?
56
Вариант 7
Задача 1. Предположим, есть два варианта инвестирования средств
в течении 5 лет: в начале каждого года под 28% и в конце года под 18%
годовых. Пусть ежегодно вносится 200 тыс. руб. Определить, сколько
денег окажется на счете в конце 5-го года для каждого варианта.
Задача 2. Ссуда размером 100 тыс. руб., выданная под 32%
годовых, погашается обычными ежемесячными платежами 11111 руб.
Рассчитать срок погашения ссуды.
Задача 3. Банк выдал ссуду 600 тыс. руб. на 6 лет под 17% годовых.
Ссуда выдана в начале года, а погашение начинается в конце года
одинаковыми платежами. Определить размер ежегодного погашения
ссуды.
Вариант 8
Задача 1. Банк принимает вклад на срок 6 месяцев с объявленной
годовой ставкой 12% или на 1 год под 19% годовых. Как выгодно
вкладывать деньги на 1 год: дважды на 6 месяцев или один раз на год?
Сумма вклада –50 тыс. руб.
Задача 2. Предположим, что компания отказалась от ежемесячных
выплат и готова положит на депозит 50 тыс. руб. сроком на 3 года.
Определить минимальную годовую процентную ставку для будущей
стоимости вклада 1,5 млн руб.
Задача 3. Вычислить платежи по процентам за первый месяц от
трехгодичного займа в 100 тыс. руб. из расчета 12% годовых.
Вариант 9
Задача 1. Предположим, рассматривается два варианта покупки
дома: заплатить сразу 3 300 000 руб. или в рассрочку – по 30 500 руб.
ежемесячно в течение 20 лет. Определить, какой вариант
предпочтительнее, если ставка процента – 12% годовых.
Задача 2. Рассчитать процентную ставку для трехлетнего займа в
100 тыс. руб. с ежемесячным погашением по 4500 руб. при условии, что
заем полностью погашается.
Задача 3. Предположим, что необходимо накопить 150 тыс. руб. за
5 лет, откладывая постоянную сумму в конце каждого месяца. Какой
должна быть эта сумма, если норма процента о вкладу составляет 15%
годовых?
57
Вариант 10
Задача 1. Фирме потребуется 2 млн руб. через 5 лет. В настоящее
время фирма располагает деньгами и готова положить их на депозит
отдельным вкладом. Определить необходимую сумму вклада, если
ставка процента по нему составляет 12% в год.
Задача 2. Рассчитать, через сколько лет вклад размером 2 млн руб.
достигнет величины 10 млн руб., если годовая савка процента по вкладу
12% и начисления процентов производится каждые полгода.
Задача 3. Банк выдал ссуду 800 тыс. руб. на 6 лет под 19% годовых.
Ссуда выдана в начале года, а погашение начинается в конце года
одинаковыми платежами. Определить размер ежегодного погашения
ссуды.
58
Лабораторная работа №5.
Методика постановки задачи и реализация ее решения на
компьютере средствами Microsoft Excel
Цель работы: изучение технологии построения автоматизируемого
процесса преобразования данных для получения итоговых отчетов.
Содержание
Изучаются вопросы:
1. Создание и описание входной и результирующей информации
базы данных. Разработка решения задачи средствами Excel.
2. Понятие
Справочника.
Организация
проверки
входной
информации.
3. Подготовка результирующих отчетов.
Выполняется вариант задания.
Указания
Рассмотрим методику автоматизируемого процесса преобразования
данных на конкретном примере.
Задача. Для планового отдела необходимо рассчитать плановый
фонд заработной платы. Форма оплаты труда работников –
повременная.
Входная информация представлена в табл. 6-7. Организовать
заполнение данных ведомости Лицевой счет, с использованием
Справочника работников. Результирующую информацию необходимо
оформить в виде форм Начисления и Удержания (табл. 8-9).
Таблица 6
Лицевой счет
Таб.
номер
Фамилия
101
102
103
104
105
106
107
Должность
Разряд
Отдел
13
14
13
15
10
8
6
Колво
льгот
0
1
2
0
3
1
1
Факт.
время
(дн.)
23
23
17
8
22
23
20
Начислено
з/п
Удержано
З/П
к выдаче
Таблица 7
Справочник работников
Таб. номер
101
102
103
104
105
106
107
Фамилия
Соколов И.И.
Иванов С.Н
Свердлов К.С.
Филатова К.М.
Петрова А.А.
Бабенко С.Н.
Давыдова С.Н.
Должность
Нач. отдела
Инженер
Инженер
Экономист
Секретарь
Экономист
Инженер
Отдел
1
2
2
1
1
1
2
Дата поступления на работу
15.04.07
01.12.99
20.07.97
02.08.03
12.10.85
01.06.87
15.11.97
59
Таблица 8
Ведомость начислений
Табельный номер
По окладу
Начислено
Премия
Всего
Таблица 9
Ведомость удержаний
Удержано
Табельный номер
Подоходный
налог
Пенсионный налог
Исполнительные
листы
Всего
Для расчетов используются следующие формулы:
 начисленная заработная плата
ЗП = ЗП окл + ПР;
ФТ
 начисленная оплата по окладу
ЗП окл  ОКЛ 
;
Т
 размер премии
ПР = ЗПокл  %ПР;
 общее удержание из зарплаты
У = Упн + Упф + Уил;
 удержание подоходного налога
Упн = (ЗП  МЗП  Л)  0,12;
 удержание пенсионного налога
Упф = ЗП  0,01;
 удержание по исполнительным
листам
Уил = (ЗП  Упн)  %ИЛ;
 заработная плата к выдаче
ЗПВ = ЗП – У,
где ОКЛ – оклад работника в соответствии с его разрядом; ФT – фактически
отработанное время в расчетном месяце (дн.); Т – количество рабочих дней в
месяце; %ПР – процент премии в расчетном месяце; МЗП – минимальная
зарплата; Л – количество льгот; %ИЛ – процент удержания по исполнительным
листам.
Оклад работника также зависит от его квалификации (разряда).
Разрядная сетка является справочником и представлена в виде табл. 10
(разряды от 1 до 18, оклады от 10000 руб. до 18500 руб.).
Таблица 10
Разрядная сетка
Разряд
Оклад, руб.
1
…
18
10000
…
18500
Размер удержания по исполнительным листам работника зависит от
процента удержания. Сведения о работниках, с которых необходимо
удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 11).
60
Таблица 11
Справочник по исполнительным листам
Табельный номер
102
105
107
Процент удержаний
25
20
5
В процессе решения задачи задайте размер минимальной оплаты
труда и количество рабочих дней в месяце, процент премии в
зависимости от выслуги лет и размер прожиточного минимума.
Решение:
1. Переименуйте Лист1 в Плановый фонд ЗП.
2. Начиная с ячейки А1 введите табл. 6 (рис. 27).
Рис. 27. Макет данных ведомости Личный счет
3. Таблицу Разрядная сетка наберите с ячейки B11 (рис. 28).
Рис. 28. Макет заголовков таблиц
Заполните таблицу Разрядная сетка,
используя функцию
автозаполнения. Для этого:
 введите 1 в ячейку B13;
 установите указатель в ячейку B13 на маркер в правом нижнем
углу. Указатель мыши примет форму ;
 удерживая клавишу Ctrl и левую клавишу мыши, протащите
указатель по диапазону B14:B30. Диапазон ячеек заполнился числами
от 1 до 18;
 введите 10000 в ячейку C13 и 10500 в ячейку С14;
 выделите ячейки С13:С14, используя автозаполнение заполните
диапазон С13:С30.
4. Таблицу Справочник по исп. листам наберите с ячейки E11. Заполните
Справочник по исп. листам согласно табл. 11.
5. Создайте Справочник работников, осуществив ввод данных с ячейки
L1 (рис. 29).
61
Рис. 29. Таблица Справочник работников
6. Начиная с ячейки A34, сформируйте Ведомость начислений (рис. 30).
Введите с ячейки A42 – Ведомость удержаний (рис. 31).
Рис. 30. Таблица Ведомость начислений
Рис. 31. Таблица Ведомость удержаний
7. Заполним таблицу Лицевой счет, используя данные справочников.
Установите курсор в клетку B3 и введите формулу заполнения
фамилии на основании данных Справочника работников (ссылки на ячейки
и диапазоны ячеек вводите, выделяя ячейки мышью, для ввода знаков $
нажимайте F4 после ввода каждого диапазона или ссылки, по окончании
ввода формулы нажмите Enter):
=ВПР(А3;$L$3:$P$9;2;0).
Знак $ фиксирует координаты ячеек и диапазонов (при копировании
формул они не изменяются). В случае возникновения ошибки
определите источник возникновения ошибки. Для этого установите
62
указатель в ячейку с формулой и нажмите на вкладке Формулы в группе
Зависимости формул кнопку группы Проверка наличия ошибок
и
выберите нужный пункт.
Скопируйте формулу определения фамилии в диапазон ячеек B4:B9.
8. Аналогично заполните диапазоны ячеек D3:D9 и E3:E9 (столбцы
Должность и Отдел) на основании данных Справочника работников.
9. Введите дополнительную информацию (рис. 32). Обратите
внимание на адреса ячеек ввода данных.
Рис. 32. Таблица дополнительной информации
10. В ячейке B34 введите формулу расчета начислений по окладу
(текст формулы вводите без переноса в одну строку):
=ВПР(ВПР(A34;$A$2:$J$9;3;0);$B$13:$C$30;2;0)*ВПР(A34;$A$3:$J$9;7;0)/$F$21
Скопируйте формулу начисления заработной платы по окладу в
диапазон В35:В40.
11. В ячейку С34 введите формулу расчета премии. Размер премии
зависит от выслуги лет, определяемой как разность между текущей
датой и датой поступления на работу. Соответственно формула для
расчета премии:
=ЕСЛИ((СЕГОДНЯ()-ВПР(A34;$L$3:$P$9;5;0))/365<5;B34*$F$23;
ЕСЛИ((СЕГОДНЯ()-ВПР(A34;$L$3:$P$9;5;0))/365<10;B34*$G$23;B34*$H$23))
Скопируйте формулу в ячейки С35:С40.
12. В ячейку D34 введите формулу расчета общих начислений
заработной платы:
=B34+C34
Скопируйте формулу в ячейки D35:D40.
13. В ячейку H3 введите формулу для начисленной оплаты из
таблицы Ведомость начислений (=D34). Скопируйте формулу в ячейки
H4:H9.
14. В ячейку B44 введите формулу расчета подоходного налога в
таблице Ведомость удержаний:
=(ВПР(A44;$A$3:$J$9;8;0)-$F$21*ВПР(A44;$A$3:$J$9;6;0))*0,12
Скопируйте формулу в ячейки B45:B50.
15. В ячейку С44 введите формулу расчета пенсионного налога:
=ВПР(A44;$A$3:$J$9;8;0)*0,01
Скопируйте формулу в ячейки С45:С50.
63
16. В ячейку D44 введите формулу расчета удержания по
исполнительным листам:
=ЕСЛИ(ЕНД(ВПР(A44;$E$13:$F$15;2;0));0;(ВПР(A44;$A$3:$J$9;8;0)-B44)*
*ВПР(A44;$E$13:$F$15;2;0)/100)
Скопируйте формулу в ячейки D45:D50.
17. В ячейку Е44 введите формулу расчета общей суммы удержания:
=B44+C44+D44.
Скопируйте формулу в ячейки E45:E50.
18. В ячейку I3 введите формулу для нахождения общей суммы
удержания (=E44) из таблицы Ведомость удержаний. Скопируйте формулу
в ячейки I4:I9.
19. Вычислите сумму к выдаче с помощью формулы массива
{=H3:H9–I3:I9}. Для этого выделите блок ячеек J3:J9, нажмите клавишу
«=», выделите блок H3:H9, нажмите клавишу «-», выделите блок I3:I9,
нажмите клавиши Ctrl +Shift+ Enter.
20. Используя автосуммирование, рассчитайте итоги в таблице
Лицевой счет. Для этого в ячейку А10 введите текст Итого, установите
указатель в ячейку H10 и выполните команду
Формулы/Библиотека функций/Автосумма.
Если выбранный командой блок окажется верным – H3:H9, нажмите
Enter. В противном случае выделите блок H3:H9 и нажмите Enter.
Аналогичные действия выполните для ячеек I10, J10.
Получение итоговых данных
21. Чтобы выполнить расчет суммы начисленной заработной платы
по отделу 1, в ячейку I12 введите Итого по отделу 1. В ячейку J12 введите
формулу:
=СУММЕСЛИ(E3:E9;1;J3:J9)
22. Самостоятельно введите в ячейку J13 формулу для расчета
суммарной начисленной заработной платы по отделу 2.
23. Рассчитайте количество работников отдела 1. Для этого в ячейку
I14 введите Кол-во сотрудников 1 отдела. В ячейку J14 введите формулу:
=СЧЕТЕСЛИ(Е3:E9;1)
24. Самостоятельно введите в ячейку J15 формулу для расчета
количества работников отдела 2.
25. Аналогично можно выполнить расчет начисленной заработной
платы и количество работников по каждой должности.
64
Задания
Вариант 1
Для планового отдела необходимо рассчитать плановый фонд
заработной платы. Форма оплаты труда работников – повременная.
Входная информация представлена в табл. 12-13 (пустые ячейки
заполните произвольными данными). Организовать заполнение данных
ведомости Лицевой счет, с использованием Справочника работников.
Результирующую информацию необходимо оформить в виде форм
Начисления и Удержания (табл. 8-9).
Таблица 12
Справочник работников
Таб. номер
1001
1002
1003
…
1007
Фамилия
Краснов А.А.
Самохов В.П.
Кривоносов А.Ф.
…
Должность
Экономист
Инженер
Бухгалтер
…
Дата поступления на работу
Отдел
3
4
3
…
15.10.2003
14.01.1994
10.03.2010
…
Таблица 13
Лицевой счет
Таб.
номер
Фамилия
Разряд
1001
1002
1003
…
1007
5
11
12
…
Должность
Отдел
Колво
льгот
0
1
1
…
Факт.
время
(дн.)
17
19
22
…
Начислено
з/п
Удержано
З/П
к выдаче
Оклад работника также зависит от его квалификации (разряда).
Разрядная сетка является справочником и представлена в виде табл. 14
(разряды от 1 до 16).
Таблица 14
Разрядная сетка
Разряд
Оклад, руб.
1
2
…
15000
15250
…
Размер удержания по исполнительным листам работника зависит от
процента удержания. Сведения о работниках, с которых необходимо
удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 15).
Таблица 15
Справочник по исполнительным листам
Табельный номер
1002
1005
1006
Процент удержаний
5
15
22
65
В процессе решения задачи задайте размер минимальной оплаты
труда (5500 руб.) и количество рабочих дней в месяце (22 дня), процент
премии в зависимости от выслуги лет (табл. 16).
Таблица 16
Процент премии
Стаж
Процент премии
меньше 10 лет
5%
от 10 до 15
25%
больше 15 лет
50%
Вариант 2
Для планового отдела необходимо рассчитать плановый фонд
заработной платы. Форма оплаты труда работников – повременная.
Входная информация представлена в табл. 17-18 (пустые ячейки
заполните произвольными данными). Организовать заполнение данных
ведомости Лицевой счет, с использованием Справочника работников.
Результирующую информацию необходимо оформить в виде форм
Начисления и Удержания (табл. 8-9).
Таблица 17
Справочник работников
Таб. номер
101
102
103
…
107
Фамилия
Соколов А.Р.
Романов А.А.
Иванов П.В.
…
Должность
Инженер
Инженер
Программист
…
Дата поступления на работу
Отдел
3
2
2
…
11.06.2007
13.12.2000
10.08.1995
…
Таблица 18
Лицевой счет
Таб.
номер
Фамилия
Разряд
101
102
103
…
107
6
11
10
…
Должность
Отдел
Колво
льгот
0
1
2
…
Факт.
время
(дн.)
22
23
21
…
Начислено
з/п
Удержано
З/П
к выдаче
Оклад работника также зависит от его квалификации (разряда).
Разрядная сетка является справочником и представлена в виде табл. 19
(разряды от 1 до 14).
Таблица 19
Разрядная сетка
Разряд
1
2
…
Оклад, руб.
16000
16500
…
Размер удержания по исполнительным листам работника зависит от
процента удержания. Сведения о работниках, с которых необходимо
66
удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 20).
Таблица 20
Справочник по исполнительным листам
Табельный номер
102
103
107
Процент удержаний
25
15
10
В процессе решения задачи задайте размер минимальной оплаты
труда (6500 руб.) и количество рабочих дней в месяце (24 дня), процент
премии в зависимости от выслуги лет (табл. 21).
Таблица 21
Процент премии
Стаж
Процент премии
меньше 10 лет
5%
от 10 до 15
25%
больше 15 лет
50%
Вариант 3
Для планового отдела необходимо рассчитать плановый фонд
заработной платы. Форма оплаты труда работников – повременная.
Входная информация представлена в табл. 22-23 (пустые ячейки
заполните произвольными данными). Организовать заполнение данных
ведомости Лицевой счет, с использованием Справочника работников.
Результирующую информацию необходимо оформить в виде форм
Начисления и Удержания (табл. 8-9).
Таблица 22
Справочник работников
Таб. номер
11
12
13
…
17
Фамилия
Павлов П.А.
Иванов И.И.
Краснов А.А.
…
Должность
Экономист
Бухгалтер
Программист
…
Отдел
1
3
1
…
Дата поступления на работу
15.02.1998
22.03.2000
26.01.2010
…
Таблица 23
Лицевой счет
Таб.
номер
11
12
13
…
17
Фамилия
Разряд
11
11
10
…
Должность
Отдел
Колво
льгот
0
1
2
…
Факт.
время
(дн.)
22
23
21
…
Начислено
з/п
Удержано
З/П
к выдаче
Оклад работника также зависит от его квалификации (разряда).
Разрядная сетка является справочником и представлена в виде табл. 24
(разряды от 1 до 14).
67
Таблица 24
Разрядная сетка
Разряд
1
2
…
Оклад, руб.
17000
17250
…
Размер удержания по исполнительным листам работника зависит от
процента удержания. Сведения о работниках, с которых необходимо
удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 25).
Таблица 25
Справочник по исполнительным листам
Табельный номер
11
14
17
Процент удержаний
35
22
6
В процессе решения задачи задайте размер минимальной оплаты
труда (5000 руб.) и количество рабочих дней в месяце (24 дня), процент
премии в зависимости от выслуги лет (табл. 26).
Таблица 26
Процент премии
Стаж
Процент премии
меньше 6 лет
5%
от 6 до 16
25%
больше 16 лет
35%
Вариант 4
Для планового отдела необходимо рассчитать плановый фонд
заработной платы. Форма оплаты труда работников – повременная.
Входная информация представлена в табл. 27-28 (пустые ячейки
заполните произвольными данными). Организовать заполнение данных
ведомости Лицевой счет, с использованием Справочника работников.
Результирующую информацию необходимо оформить в виде форм
Начисления и Удержания (табл. 8-9).
Таблица 27
Справочник работников
Таб. номер
201
202
203
…
207
Фамилия
Комаров П.А.
Романов П.Р.
Маслов П.В.
…
Должность
Нач. отдела
Экономист
Экономист
…
Отдел
3
4
3
…
Дата поступления на работу
12.03.2000
11.06.2008
10.03.1998
…
Оклад работника также зависит от его квалификации (разряда).
Разрядная сетка является справочником и представлена в виде табл. 29
(разряды от 1 до 14).
68
Таблица 28
Лицевой счет
Таб.
номер
Фамилия
Разряд
201
202
203
…
207
Должность
Отдел
11
11
10
…
Колво
льгот
0
1
2
…
Факт.
время
(дн.)
22
23
21
…
Начислено
з/п
Удержано
З/П
к выдаче
Таблица 29
Разрядная сетка
Разряд
1
2
…
Оклад, руб.
12000
12850
…
Размер удержания по исполнительным листам работника зависит от
процента удержания. Сведения о работниках, с которых необходимо
удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 30).
Таблица 30
Справочник по исполнительным листам
Табельный номер
203
205
206
Процент удержаний
35
12
18
В процессе решения задачи задайте размер минимальной оплаты
труда (5500 руб.) и количество рабочих дней в месяце (23 дня), процент
премии в зависимости от выслуги лет (табл. 31).
Таблица 31
Процент премии
Стаж
Процент премии
меньше 5 лет
5%
от 5 до 20
25%
больше 20 лет
35%
Вариант 5
Для планового отдела необходимо рассчитать плановый фонд
заработной платы. Форма оплаты труда работников – повременная.
Входная информация представлена в табл. 32-33 (пустые ячейки
заполните произвольными данными). Организовать заполнение данных
ведомости Лицевой счет, с использованием Справочника работников.
Результирующую информацию необходимо оформить в виде форм
Начисления и Удержания (табл. 8-9).
Оклад работника также зависит от его квалификации (разряда).
Разрядная сетка является справочником и представлена в виде табл. 34
(разряды от 1 до 16).
69
Таблица 32
Справочник работников
Таб. номер
2001
2002
2003
…
2007
Фамилия
Морозов А.П.
Макарова П.В.
Коровин П.А.
…
Должность
Экономист
Инженер
Экономист
…
Дата поступления на работу
Отдел
2
1
2
…
11.03.2000
12.08.2007
01.02.1998
…
Таблица 33
Лицевой счет
Таб.
номер
Фамилия
Разряд
2001
2002
2003
…
2007
Должность
Отдел
14
11
12
…
Колво
льгот
3
1
2
…
Факт.
время
(дн.)
22
23
21
…
Начислено
з/п
Удержано
З/П
к выдаче
Таблица 34
Разрядная сетка
Разряд
1
2
…
Оклад, руб.
16000
16250
…
Размер удержания по исполнительным листам работника зависит от
процента удержания. Сведения о работниках, с которых необходимо
удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 35).
Таблица 35
Справочник по исполнительным листам
Табельный номер
2001
2004
2007
Процент удержаний
10
12
11
В процессе решения задачи задайте размер минимальной оплаты
труда (6000 руб.) и количество рабочих дней в месяце (24 дня), процент
премии в зависимости от выслуги лет (табл. 36).
Таблица 36
Процент премии
Стаж
Процент премии
меньше 5 лет
5%
от 5 до 20
10%
больше 20 лет
15%
70
Вариант 6
Для планового отдела необходимо рассчитать плановый фонд
заработной платы. Форма оплаты труда работников – повременная.
Входная информация представлена в табл. 37-38 (пустые ячейки
заполните произвольными данными). Организовать заполнение данных
ведомости Лицевой счет, с использованием Справочника работников.
Результирующую информацию необходимо оформить в виде форм
Начисления и Удержания (табл. 8-9).
Таблица 37
Справочник работников
Таб. номер
21
22
23
…
27
Фамилия
Петров С.Ф.
Комаров А.Р.
Кравцов П.В.
…
Должность
Бухгалтер
Экономист
Экономист
…
Отдел
5
6
5
…
Дата поступления на работу
10.02.2003
1.06.2007
11.05.1994
…
Таблица 38
Лицевой счет
Таб.
номер
Фамилия
Разряд
21
22
23
…
27
12
13
10
…
Должность
Отдел
Колво
льгот
3
2
0
…
Факт.
время
(дн.)
15
22
23
…
Начислено
з/п
Удержано
З/П
к выдаче
Оклад работника также зависит от его квалификации (разряда).
Разрядная сетка является справочником и представлена в виде табл. 39
(разряды от 1 до 14).
Таблица 39
Разрядная сетка
Разряд
1
2
…
Оклад, руб.
18000
18250
…
Размер удержания по исполнительным листам работника зависит от
процента удержания. Сведения о работниках, с которых необходимо
удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 40).
Таблица 40
Справочник по исполнительным листам
Табельный номер
2001
2004
2007
Процент удержаний
10
12
11
В процессе решения задачи задайте размер минимальной оплаты
труда (5000 руб.) и количество рабочих дней в месяце (23 дня), процент
премии в зависимости от выслуги лет (табл. 41).
71
Таблица 41
Процент премии
Стаж
Процент премии
меньше 10 лет
25%
от 10 до 20
30%
больше 20 лет
35%
Вариант 7
Для планового отдела необходимо рассчитать плановый фонд
заработной платы. Форма оплаты труда работников – повременная.
Входная информация представлена в табл. 42-43 (пустые ячейки
заполните произвольными данными). Организовать заполнение данных
ведомости Лицевой счет, с использованием Справочника работников.
Результирующую информацию необходимо оформить в виде форм
Начисления и Удержания (табл. 8-9).
Таблица 42
Справочник работников
Таб. номер
31
32
33
…
37
Фамилия
Викторов П.А.
Макарова А.В.
Иванова О.Л.
…
Должность
Бухгалтер
Инженер
Экономист
…
Отдел
1
2
1
…
Дата поступления на работу
10.05.2000
02.08.1993
11.04.2008
…
Таблица 43
Лицевой счет
Таб.
номер
Фамилия
Разряд
31
32
33
…
37
11
11
10
…
Должность
Отдел
Колво
льгот
0
1
2
…
Факт.
время
(дн.)
22
23
21
…
Начислено
з/п
Удержано
З/П
к выдаче
Оклад работника также зависит от его квалификации (разряда).
Разрядная сетка является справочником и представлена в виде табл. 44
(разряды от 1 до 14).
Таблица 44
Разрядная сетка
Разряд
1
2
…
Оклад, руб.
14000
15000
…
Размер удержания по исполнительным листам работника зависит от
процента удержания. Сведения о работниках, с которых необходимо
удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 45).
В процессе решения задачи задайте размер минимальной оплаты
труда (7000 руб.) и количество рабочих дней в месяце (24 дня), процент
премии в зависимости от выслуги лет (табл. 46).
72
Таблица 45
Справочник по исполнительным листам
Табельный номер
31
33
37
Процент удержаний
15
25
14
Таблица 46
Процент премии
Стаж
Процент премии
меньше 8 лет
15%
от 8 до 18
20%
больше 18 лет
25%
Вариант 8
Для планового отдела необходимо рассчитать плановый фонд
заработной платы. Форма оплаты труда работников – повременная.
Входная информация представлена в табл. 47-48 (пустые ячейки
заполните произвольными данными). Организовать заполнение данных
ведомости Лицевой счет, с использованием Справочника работников.
Результирующую информацию необходимо оформить в виде форм
Начисления и Удержания (табл. 8-9).
Таблица 47
Справочник работников
Таб. номер
301
302
303
…
307
Фамилия
Петрова П.В.
Шариков П.В.
Иванов А.А.
…
Должность
Нач. отдела
Бухгалтер
Экономист
…
Отдел
5
4
4
…
Дата поступления на работу
12.09.2000
01.06.1995
14.08.2004
…
Таблица 48
Лицевой счет
Таб.
номер
Фамилия
Разряд
301
302
303
…
307
15
12
11
…
Должность
Отдел
Колво
льгот
1
2
1
…
Факт.
время
(дн.)
12
22
23
…
Начислено
з/п
Удержано
З/П
к выдаче
Оклад работника также зависит от его квалификации (разряда).
Разрядная сетка является справочником и представлена в виде табл. 49
(разряды от 1 до 16).
Таблица 49
Разрядная сетка
Разряд
1
2
…
Оклад, руб.
18000
18250
…
73
Размер удержания по исполнительным листам работника зависит от
процента удержания. Сведения о работниках, с которых необходимо
удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 50).
Таблица 50
Справочник по исполнительным листам
Табельный номер
302
305
307
Процент удержаний
15
22
36
В процессе решения задачи задайте размер минимальной оплаты
труда (6000 руб.) и количество рабочих дней в месяце (23 дня), процент
премии в зависимости от выслуги лет (табл. 51).
Таблица 51
Процент премии
Стаж
Процент премии
меньше 6 лет
5%
от 6 до 20
15%
больше 20 лет
25%
Вариант 9
Для планового отдела необходимо рассчитать плановый фонд
заработной платы. Форма оплаты труда работников – повременная.
Входная информация представлена в табл. 52-53 (пустые ячейки
заполните произвольными данными). Организовать заполнение данных
ведомости Лицевой счет, с использованием Справочника работников.
Результирующую информацию необходимо оформить в виде форм
Начисления и Удержания (табл. 8-9).
Таблица 52
Справочник работников
Таб. номер
3001
3002
3003
…
3007
Фамилия
Василькова П.Ф.
Краснов П.Д.
Сидоров А.Р.
…
Должность
Экономист
Экономист
Инженер
…
Отдел
3
2
2
…
Дата поступления на работу
02.05.2000
06.08.2007
05.03.1995
…
Таблица 53
Лицевой счет
Таб.
номер
3001
3002
3003
…
3007
Фамилия
Разряд
12
10
9
…
Должность
Отдел
Колво
льгот
1
2
2
…
Факт.
время
(дн.)
22
23
10
…
Начислено
з/п
Удержано
З/П
к выдаче
74
Оклад работника также зависит от его квалификации (разряда).
Разрядная сетка является справочником и представлена в виде табл. 54
(разряды от 1 до 14).
Таблица 54
Разрядная сетка
Разряд
1
2
…
Оклад, руб.
16000
16750
…
Размер удержания по исполнительным листам работника зависит от
процента удержания. Сведения о работниках, с которых необходимо
удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 55).
Таблица 55
Справочник по исполнительным листам
Табельный номер
3001
3005
3006
Процент удержаний
10
31
16
В процессе решения задачи задайте размер минимальной оплаты
труда (7200 руб.) и количество рабочих дней в месяце (23 дня), процент
премии в зависимости от выслуги лет (табл. 56).
Таблица 56
Процент премии
Стаж
Процент премии
меньше 10 лет
15%
от 10 до 20
30%
больше 20 лет
45%
Вариант 10
Для планового отдела необходимо рассчитать плановый фонд
заработной платы. Форма оплаты труда работников – повременная.
Входная информация представлена в табл. 57-58 (пустые ячейки
заполните произвольными данными). Организовать заполнение данных
ведомости Лицевой счет, с использованием Справочника работников.
Результирующую информацию необходимо оформить в виде форм
Начисления и Удержания (табл. 8-9).
Таблица 57
Справочник работников
Таб. номер
4001
4002
4003
…
4007
Фамилия
Федоров П.Д.
Иванова Р.В.
Комарова Ф.Н.
…
Должность
Экономист
Инженер
Инженер
…
Отдел
2
3
3
…
Дата поступления на работу
11.06.2000
12.06.1999
12.07.2006
…
75
Таблица 58
Лицевой счет
Таб.
номер
Фамилия
Разряд
4001
4002
4003
…
4007
Должность
Отдел
13
11
12
…
Колво
льгот
2
1
0
…
Факт.
время
(дн.)
22
23
23
…
Начислено
з/п
Удержано
З/П
к выдаче
Оклад работника также зависит от его квалификации (разряда).
Разрядная сетка является справочником и представлена в виде табл. 59
(разряды от 1 до 14).
Таблица 59
Разрядная сетка
Разряд
1
2
…
Оклад, руб.
18000
18750
…
Размер удержания по исполнительным листам работника зависит от
процента удержания. Сведения о работниках, с которых необходимо
удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 60).
Таблица 60
Справочник по исполнительным листам
Табельный номер
4002
4004
4007
Процент удержаний
5
12
25
В процессе решения задачи задайте размер минимальной оплаты
труда (5000 руб.) и количество рабочих дней в месяце (23 дня), процент
премии в зависимости от выслуги лет (табл. 61).
Таблица 61
Процент премии
Стаж
Процент премии
меньше 10 лет
10%
от 10 до 20
20%
больше 20 лет
30%
76
Лабораторная работа №6.
Применение информационных технологий
для формирования решений
Цель работы: изучение технологии построения формирования
решений в условиях определенности и неопределенности, приобретение
практических навыков применения методов формирования решений.
Содержание
Изучаются вопросы:
1. Формирование решений средствами таблиц.
2. Применение экспертных систем для формирования решений в
условиях определенности.
3. Применение экспертных систем для формирования решений в
условиях неопределенности.
Выполняется вариант задания.
Указания
Конечный продукт работы любого менеджера  это решение и
действия. Принятое менеджером решение ведет либо к преуспеванию
предприятия, либо к неудачам. Принятие решения  это всегда выбор
определенного направления деятельности из нескольких возможных.
Задачи принятия решений можно классифицировать по следующим
признакам: по степени определенности, по критериальности, по
коллективности.
1. Степень определенности зависит от условий, в которых
принимается решение. Известны три степени определенности: полная
определенность, рисковая ситуация и неопределенность.
Под определенностью понимается ситуация, при которой каждому
варианту решения известен вполне определенный набор последствий.
При этом задача хорошо формализована (имеется модель решения),
существует критерий оценки качества решения и последствия принятия
решения можно предвидеть.
В случае принятия решений в условиях риска каждый вариант
решения характеризуется несколькими ситуациями, которые могут
наступить с разной вероятностью, и при этом для каждого из них
известен набор последствий. Вероятность может быть вы числена с
помощью статистических данных.
Принятие решений в условиях неопределенности происходит в том
случае, когда в процессе принятия решений используют неточную,
неполную или слабоструктурированную информацию. Формальные
модели либо отсутствуют, либо сложны. Вероятности наступления
событий не определяются.
Практика принятия решений многообразна. Однако все они
77
реализуются по определенной схеме. Для того чтобы принять
эффективное
решение,
необходимо
выполнить
ряд
работ,
складывающихся из отдельных этапов, процедур и операций. Среди
многочисленных подходов к формированию решений выделим
трехэтапную модель Г. Саймона, являющуюся основой для реализации
большинства известных на сегодня технологий (рис. 33).
1-й этап
2-й этап
3-й этап
Анализ проблемы,
Сопоставление
Поиск
формирование
вариантов и
вариантов
целей, определение
выбор согласно
решений
критериев
критерию
Рис. 33. Трехэтапное формирование решений
Формирование решений средствами таблиц
Задача 1. Построить таблицу оценки последствий принятия решений
с помощью таблицы «стоимость – эффективность» для различных
вариантов капиталовложений по одному и всем указанным критериям
оценки, приведенных в табл. 62. Значения критериев: возможность
снижения транспортных затрат (K1), повышение качества выпускаемой
продукции (K2), снижение норм расходов ресурсов (K3).
Решение:
Немногие решения принимаются в условиях определенности, ведь не
всегда известен результат выбора какой-либо альтернативы, а также ее
вероятность. В такой ситуации задача сводится к выбору оптимальной
альтернативы. При выборе альтернативы необходимо учитывать по
возможности все существенно влияющие факторы, такие, как
вероятность успеха, оценка успеха, вероятность неудачи, потери от
неудачи.
Таблица 62
Таблица вариантов капиталовложений
Вариант решения
В1 – закупка нового оборудования
В2 – снижение процента брака
В3 – закупка более качественного сырья
В4 – повышение квалификации рабочих
Коэффициент значимости критерия (j)
Общие издержки,
распределенные по
критериям
К1
К2
К3
900
325
457
100
330
26
195
390
221
68
101
96
0,4
0,3
0,3
Общие доходы,
распределенные по
критериям
К1
К2
К3
1956
256
697
132
2369
547
246
154
658
114
109
139



Простейшим методом оценки последствий принятия решений
является оценка с помощью таблицы «стоимость-эффективность». В
таких таблицах, как правило, критерием выбора выступает
78
максимальный доход на единицу затрат. Для заполнения таблицы
необходимо выполнить расчет общих затрат и общих доходов по
каждому варианту, а затем ранжирование альтернатив по критерию
принятия решений.
Реализация метода по критерию К1 приведена в табл. 63.
Таблица 63
Таблица «стоимость-эффективность»
Вариант решения
В1 – закупка нового
оборудования
В2 – снижение процента
брака
В3 – закупка более
качественного сырья
В4 – повышение
квалификации рабочих
Общие
затраты
Общие
доходы
Отношение
доходов к
затратам
Ранг
варианта
900
1956
2,17
1
100
132
1,32
3
195
246
1,26
4
68
114
1,68
2
Вычисленное отношение доходов к затратам показало, что вариант
В1 имеет наибольшую величину (2,17), поэтому ему присваивается
первый ранг; варианту В4 присваивается второй ранги и т.д. Очевидно,
согласно критерию, который требует выбора варианта с максимальным
уровнем дохода на единицу затрат, лучшим будет вариант закупки
оборудования как наиболее эффективные капиталовложения (В1).
Аналогично можно выполнить расчет для каждого критерияоценки.
В случае формирования решения для всех указанных критериев
оценки создается таблица «стоимость-критерий» (табл. 64). В ней
представляются варианты решений, оцениваемые с различных точек
зрения.
Таблица 64
Таблица «стоимость-критерий»
Вариант решения
В1 – закупка нового
оборудования
В2 – снижение процента
брака
В3 – закупка более
качественного сырья
В4 – повышение
квалификации рабочих
Коэффициент значимости
критерия (j)
Общие издержки,
Общая оценка
Ранг
распределенные по критериям
по всем
варианта
критериям
К1
К2
К3
900
325
457
594,6
1
100
330
26
146,8
3
195
390
221
261,3
2
68
101
96
86,3
4
0,4
0,3
0,3
Элементами таблицы могут быть как абсолютные величины;
79
указывающие на затраты или доходы, так и относительные, например
ранг варианта, вычисленный на основе таблицы «стоимостьэффективность». В последней строке таблицы указываются
коэффициенты значимости каждого из критериев оценки. Это та
качественная информация, которая, собственно, и отличает систему
формирования решений от формальных оптимизационных методов. В
эту строку лицо принимающее решение вносит свой опыт и знание в
процесс оценки вариантов. Сумма коэффициентов значимости всех
критериев должна быть равна единице (0,4 + 0,3 + 0,3 = 1).
Общая оценка каждого из вариантов рассчитывается по формуле
Oi    j Eij ,
j
где Оi  общая оценка i-гo варианта решения; j  оценка j-го критерия;
Еij  результат, который может быть получен при i-м варианте согласно
критерию j.
Тогда па варианту В1 общая оценка равна
Oi = E11 ∙ 1 + Е12 ∙ 2 + Е13 ∙ 3 = 0,4 ∙ 900 + 0,3 ∙ 325 + 0,3 ∙ 457 = 594,6.
Наилучшим вариантом согласно данным табл. 72 является вариант
В1. Однако абсолютные величины в большинстве случаев
малоинформативны. Для повышения эффективности формирования
решения в качестве элементов Еij необходимо использовать
относительные величины (ранги, рентабельности, нормы прибыли).
Применение экспертных систем для формирования решений
в условиях определенности
Понятие
определенности
является
относительным.
Под
определенностью будем понимать ситуацию, когда одной альтернативе
решения соответствует известный набор последствий.
В иерархии управления формулируются цели, соответствующие
определенному уровню управления. На самом высоком уровне
находятся цели, носящие директивный характер. Директивные цели
всегда детализируются. В результате получают дерево целей. Нижний
уровень дерева целей превращается в мероприятие, которое следует
выполнить для достижения директивной цели.
Если можно сформулировать цель решения задачи, декомпозировать
ее на подцели, а затем указать формулы для расчета уровня достижения
каждой подцели, то процесс принятия решений можно представить с
помощью дерева целей, на котором выполняются два вида расчетов:
прямые и обратные.
Решения с помощью деревьев целей формируют в два этапа: 1)
сначала выполняют прямые расчеты (рис. 34, а), чтобы определить
фактическое состояние предприятия (каков фактический уровень
80
достижения главной цели); 2) затем выполняют обратные вычисления
(рис. 34, б), чтобы узнать, какие меры следует предпринять, чтобы
достичь желаемого уровня главной цели.
Для выполнения обратных вычислений необходимо указать:
1. ограничения на терминальные узлы дерева целей (ограничения на
ресурсы);
2. приоритеты в достижении целей;
3. направления в изменении уровня достижения целей.
a)
б)
Рис. 34. Формирование решений с помощью прямых (снизу вверх) (а) и
обратных (сверху вниз) (б) вычислений
Решением задачи является множество значений терминальных узлов
дерева целей, которые служат управляющими воздействиями для
конкретных структурных подразделений.
Задача 2. Пусть на предприятии в качестве цели служит повышение
уровня рентабельности оборотных средств, вычисление которого можно
представить деревом целей (рис. 35). На рис. 35 использованы
следующие обозначения: Р+  повысить рентабельность; П+  увеличить
прибыль отчетного периода; О  снизить среднюю стоимость остатков
материальных оборотных средств; В+  увеличить выручку от
реализации товаров, продукции, работ, услуг; З  снизить затраты на
производство и реализацию продукции; К+  увеличить объемы
реализованной продукции; Ц+  снизить цены, по которым происходит
отпуск продукции.
+
Р
=0,7
=0,3
П+
=0,6
О
=0,4
В+
=0,1
К+
З
=0,9
Ц
Рис. 35. Дерево цепей Повысить рентабельность
Знаками «+» и «» на дереве указаны направления достижения
81
целей: «+»  увеличение; «»  снижение.
Уровень достижения каждой цели измеряется е помощью
следующих показателей:
П
P 
;
П = В – З; В = КЦ
O
Каждая из целей (подцелей) снабжена своим коэффициентом
приоритетности. На рис. 36 приведены результаты прямых вычислений,
с помощью которых определена фактическая рентабельность
предприятия (0,14).
Р = 0,14
П = 20
В = 180
К = 60
О = 142,85
З = 160
Ц=3
Рис. 36. Расчет фактического уровня рентабельности
На рис. 37 представлена
приведенных расчетов.
компьютерная
модель
реализации
Рис. 37. Компьютерная модель задачи
Для того чтобы определить мероприятия, которые следует провести,
чтобы рентабельность поднялась до 0,2, необходимо выполнить
обратные вычисления. Для этого воспользуемся типовыми формулами,
предназначенными для обратных вычислений (см. Приложение).
Согласно дереву целей для роста рентабельности необходимо
увеличение прибыли и снижение стоимость остатков материальных
оборотных средств. Т.е. рентабельность является зависимой функцией
82
П  ( )
О  ()
Для уровня рентабельности получим следующие значения прироста
аргументов:
O
П + П = k1П О  О =
.
k2
Выбираем коэффициенты для расчета из Приложения.
P  P
  P
k2 
k1 
.
P
k1 P
P 
P  P
При  = 0,7,  = 0,3 получим
двух перемененных y+ = f(x+(α), z(β)), а именно если α > β, Р  
k1 
0,7  0,3  0,14
= 1,395;
0,7  0,14
0,3  0,14 
0,14  0,06
k2 
0,14  0,06
= 1,024
1,395  0,14
O 142,85

= 139,47.
k2
1,024
На рис. 38 представлена реализация приведенных расчетов
П + П = k1П = 1,395  20 = 27,89;
О  О =
Рис. 38. Компьютерная реализация расчетов
Аналогично выполняем расчет для каждого уровня дерева целей.
Для уровня прибыли получим следующие значения прироста
аргументов
З
В + В = k1В
З  З =
.
k2
Выбираем коэффициенты для расчета из Приложения
З
  П  П    B   З
k2 
k1 
.
k1B   П  П 
В
83
При  = 0,6,  = 0,4 получим k1 = 1,026, k2 = 1,02, В + В = 184,08,
3  3 = 156,86.
Для уровня выручки получим:
Ц
K + K = k1K,
Ц  Ц =
;
k2
Выбираем коэффициенты для расчета из Приложения
2




Ц  К   Ц  К   4 КЦ  В 



( В  В )k 2


k1 
,
k2 
КЦ

В 

2 К 
Ц 

При  = 0,7,  = 0,3 получим k1 = 1,215, k2 =1,184, К+ К = 72,91,
Ц  Ц= 2,53.
На рис. 39 представлена вычислительная схема решаемой задачи, а
на рис. 40 результат расчетов.
Рис. 39. Модель задачи
На рис. 41 указаны значения показателей, полученные после
выполнения
обратных вычислений.
При этом
фактическая
рентабельность равна 0,14 (14%), а ее желаемый прирост составит 0,06.
84
Рис. 40. Результат решения задачи
Р = 0,14 + 0,06
0,3
0,7
П = 27,89
О = 139,47
0,4
0,6
В = 184,08
0,1
К = 72,91
З = 156,86
0,9
Ц = 2,53
Рис. 41. Результаты формирования решения, позволяющие повысить
рентабельность
Допустим, лицо принимающее решение желает узнать, что ему
следует предпринять для того, чтобы рентабельность поднялась до 0,2
(на 0,06). Обратимся к рис. 41. Для этого на уровне рентабельности,
согласно приведенным выше формулам, необходимо повысить
показатель П (прибыль) до 27,89 ед., снизить показатель О (оборотные
средства) до 139,47 ед. В свою очередь, для того чтобы прибыль
поднялась до 27,8 ед., необходимо повысить показатель В (выручка) до
184,08 ед. и снизить показатель З (затраты) до 156,86 ед. Для того чтобы
увеличилась выручка, необходимо повысить показатель К (объем
продукции) до 72,91 ед. и снизить его цену до 2,53 ед.
85
Применение экспертных систем для формирования решений
в условиях неопределенности
Существует измеримая неопределенность, т.е. риск, и неизмеримая 
собственно неопределенность. Риск вычисляется на основе
статистических данных, а неопределенность не вычисляется. Ее
величина устанавливается на основе субъективных знаний человека.
Источниками неопределенности служат либо неполнота знаний о фактах
или событиях, либо свойство объекта, которое принципиально
невозможно измерить.
Задача 3. Рассмотрим процесс формирования решений с помощью
экспертной системы. Необходимо создать элемент экспертной системы,
ориентированной на прогнозирование снижения цены на товары.
Решение:
Сформулируем гипотезу следующим образом: «Ожидается снижение
цены на товар». Тогда задача состоит в расчете коэффициента
определенности данной гипотезы от 0 до 1.
Следующим этапом является сбор необходимых знаний по данной
экономической ситуации. Результатом данного этапа являются
логически выстроенные знания с помощью метода сценариев.
Сценарий: Ожидается снижение цены на товар в случае, если
наблюдается снижение спроса на товар или снижение себестоимости
товара, или снижение качества товара. При этом снижение спроса на
товар происходит, если появляются новые конкуренты на рынке и срок
годности товара приближается к концу и в наличии имеется
поврежденный товар. Снижение себестоимости товара наблюдается при
смене сезона или заключении выгодных договоров с поставщиками, или
приобретении нового оборудования.
Метод сценариев помогает составить представление о проблеме, а
затем приступить к более формализованному представлению системы в
виде графиков, таблиц и т. д. В данном случае полученная система
взаимосвязанных фактов формализуется с помощью метода дерева
проблем в виде графа.
Таким образом, результатом этапа формализации является дерево
вывода (рис. 42), которое имеет два уровня и объединяет ряд
закономерностей, представленных в виде трех правил ЕСЛИ-ТО
продукционной модели знаний (табл. 65).
86
Г
Е2
C1
C2
C3
C4
C5
Е8
Е9
Е10
Рис. 42. Фрагмент дерева целей
Таблица 65
Правила дерева вывода
Уровень
дерева
Номер
правила
1
2
1
1
2
Содержание правила
ЕСЛИ С1 ИЛИ С2 ИЛИ Е2 ТО Г
ЕСЛИ С3 И С4 И С5 ТО С1
ЕСЛИ Е8 ИЛИ Е9 ИЛИ Е10 ТО С2
Коэффициент
определенности
правила
ct(прi)
0,8
0,6
0,7
Теперь необходимо «снять» неопределенность. Как известно,
существует измеримая определенность, т.е. риск, и неизмеримая –
собственно неопределенность. Риск вычисляется на основе
статистических данных, а неопределенность не вычисляется. Ее
величина, принадлежащая числовой шкале [0; 1], устанавливается на
основе субъективных знаний лица, принимающего решение с
использованием метода экспертных оценок, предназначенного для
организации работы со специалистами-экспертами и обработки мнений
экспертов.
Эти мнения обычно выражены частично в количественной, а
частично в качественной форме. Для отображения степени значимости
используется
совокупность
нечётких
(или
лингвистических)
переменных типа «сильно», «умеренно», «слабо» и т. п., которым
соответствует числовая шкала [0; 1]. Коэффициенты определенности
правил и условий С3, С4, С5, Е8, Е9 и Е10 ставятся лицом, принимающим
решение (табл. 66), а Г, С1 и С2 вычисляются (табл. 65).
Рассчитаем коэффициент определенности для гипотезы, используя
табличный процессор MS Excel. В дереве вывода имеются два правила с
союзом ИЛИ и одно правило с союзом И.
Существует несколько типов правил, на основе которых
вычисляются коэффициенты достоверности заключения.
87
Таблица 66
Расшифровка обозначений в дереве вывода
Имя
узла
Содержание узла
Г
С1
С2
Е2
С3
С4
С5
Е8
Е9
Е10
Снижение цены на товар
Снижение спроса на товар
Снижение себестоимости товара
Снижение качества товара
Появление новых конкурентов на рынке
Приближение окончания срока годности товара
Уценка из-за повреждения товара
Смена сезона
Заключение выгодных договоров с поставщиками
Приобретение нового оборудования
Коэффициент
определенности
ct(Ci), ct(Ei)
?
?
?
0,5
0,6
0,5
0,6
0,2
0,8
0,5
Тип 1: правило содержит одно условие.
b
ЕСЛИ a, ТО b
Правило:
k(b) = k(a)  k(r)
k(r)
k(a)
a
Тип 2: правило содержит несколько условий, связанных условием И.
b
ЕСЛИ (a1 И а2 И а3) ТО b
k(r)
a1
a2 a3
k(a1) k(a2) k(a3)
Правило:
k(b) = kmin(a)  k(r),
где kmin(a) = min(k(a1), k(a2), …, k(am))
Тип 3: правило содержит несколько условий, связанных условием ИЛИ.
b
ЕСЛИ (a1 ИЛИ а2 ИЛИ а3) ТО b
k(r)
a1
a2 a3
k(a1) k(a2) k(a3)
Правило:
k(b) = kmax(a)  k(r),
где kmax(a) = max(k(a1), k(a2), …, k(am))
Тип 4: одно заключение поддерживается несколькими правилами.
b
k(r1)
ЕСЛИ a1, ТО b
ЕСЛИ a2, ТО b
k(r2)
a1
a2
k(a1)
k(a2)
Правило:
k(b) = k(b1) + k(b2)  k(b1)  k(b2),
где k(b1) = k(a1)  k(r1), k(b2) = k(a2)  k(r2)
Условные обозначения: a – условия; b – заключения; r – правило; k(a) –
коэффициент определенности условия; k(b) – коэффициент определенности
заключения; k(r) – коэффициент определенности правила.
88
Технология решения подобных задач в табличном процессоре MS
Excel основана на компонентной структуре экспертной системы: база
знаний, факты, пользовательский интерфейс и логический механизм
вывода.
Реализация фрагмента экспертной системы.
1. Откройте табличный процессор MS Excel. Переименуйте Лист1 в
Правила и постройте на нем таблицу как на рис. 43, представляющей
базу знаний.
Рис. 43. База знаний Правила
2. Переименуйте Лист2 – Факты и постройте таблицу, отражающую
факты по исследуемой проблеме (табл. 66). Таким образом, будет
создана база фактов (рис. 44).
Рис. 44. Обозначения в дереве выводов Факты
3. На листе Правила задайте имена ячейкам, содержащим
коэффициенты определенности для правил 1, 2 и 3, используя команду
Присвоить имя в меню Формула: ячейке D2 – пр1, ячейке D3 – пр2, ячейке
D4 – пр3.
4. Далее на листе Факты рассчитывается коэффициент
определенности для гипотезы с использованием соответствующих
формул.
В ячейках C3 и C4 рассчитываются коэффициенты определенности
для заключений C1 и C2. Для этого в соответствующие ячейки вводятся
формулы:
ячейка С3 содержит формулу =пр2*МИН(C6;C7;C8);
ячейка С4 содержит формулу =пр3*МАКС(C9;C10;C11).
В результате получим коэффициенты определенности для
заключений C1 и C2 – k(C1) = 0,3 и k(C2) = 0,56.
89
Для расчета коэффициента определенности гипотезы Г в ячейку С2
необходимо ввести следующую формулу: =пр1*МАКС(C3;C4;C5).
Коэффициент определенности гипотезы k(Г) = 0,448.
5. Следующим этапом является получение текстовой интерпретации
полученного числового значения, для этого используется передаточная
функция, основанная на использовании логической функции ЕСЛИ().
Таким образом реализуется логический механизм вывода.
Сценарий: Ожидается снижение цены на товар, если значение
коэффициента определенности принадлежит диапазону [0,75; 1].
Ожидается незначительное снижение цены на товар, если значение
коэффициента определенности принадлежит диапазону [0,74; 0,55].
Снижение цены на товар является маловероятным, если значение
коэффициента определенности меньше 0,55.
Представим данную интерпретацию полученного результата:
для этого в ячейке E8 необходимо ввести следующую формулу:
=ЕСЛИ(И(C2>=F2;C2<=G2);E2;ЕСЛИ(И(C2<=F3;C2>=G3);E3;ЕСЛИ(И(C2<=F4;C2>=G4);E4)))
Окончательное решение данной задачи представлено на рис. 45.
Рис. 45. Фрагмент экспертной системы, ориентированной
на прогнозирование снижения цены на товары
Работоспособность экспертной системы, ориентированной на
прогнозирование снижения цены на товары можно проверить, изменяя
исходные значения.
90
Задания
Вариант 1
Задача 1. Разработайте компьютерную модель формирования
решения путем вычисления его ранга в таблице «Стоимость –
эффективность» по каждому критерию и в таблице «Стоимость –
критерий» по данным табл. 67.
Таблица 67
Таблица вариантов капиталовложений
Вариант решения
В1
В2
В3
Коэффициент значимости критерия (j)
Общие издержки,
распределенные по
критериям
К1
К2
К3
500
581
457
265
368
347
158
147
118
0,2
0,5
0,3
Общие доходы,
распределенные по
критериям
К1
К2
К3
569
587
109
158
214
367
369
25
227



Задача 2. На предприятия в отчетный период получены следующие
значения показателей финансово-хозяйственной деятельности: средняя
стоимость оборотных средств 154,2 ед., затраты на производство и
реализацию продукции 200 ед., объем реализованной продукции 65 ед.,
цена единицы продукции 4 ед.
Пользуясь деревом целей (рис. 35), определите, какими должны быть
стать значения прибыли, оборотных средств, выручки, затрат, объемов
производства и цены для того чтобы рентабельность повысилась на 4%.
В табл. 68 приведены значения коэффициентов приоритетности для
цели каждого уровня.
Таблица 68
Значения коэффициентов приоритетности
Коэффициенты
приоритетности
α
β
Уровень 1
Уровень 2
Уровень 3
0,6
0,4
0,52
0,48
0,8
0,2
Задача 3. Пользуясь данными рис. 42, табл. 65-66, рассчитайте
коэффициент определенности гипотезы «ожидается снижение цены на
товар» при ct(E2) = 0,4; ct(C3) = 0,3; ct(C4) = 0,5; ct(C5) = 0,4;
ct(E8) = 0,7; ct(E9) = 0,4; ct(E10) = 0,6; ct(пр1) = 0,6; ct(пр2) = 0,6;
ct(пр3) = 0,7.
91
Вариант 2
Задача 1. Разработайте компьютерную модель формирования
решения путем вычисления его ранга в таблице «Стоимость –
эффективность» по каждому критерию и в таблице «Стоимость –
критерий» по данным табл. 69.
Таблица 69
Таблица вариантов капиталовложений
Вариант решения
В1
В2
В3
Коэффициент значимости критерия (j)
Общие издержки,
распределенные по
критериям
К1
К2
К3
20
30
40
10
40
20
15
10
30
0,4
0,4
0,2
Общие доходы,
распределенные по
критериям
К1
К2
К3
12
25
36
11
45
5
16
18
38



Задача 2. На предприятия в отчетный период получены следующие
значения показателей финансово-хозяйственной деятельности: средняя
стоимость оборотных средств 406 ед., затраты на производство и
реализацию продукции 236 ед., объем реализованной продукции 102 ед.,
цена единицы продукции 5 ед.
Пользуясь деревом целей (рис. 35), определите, какими должны быть
стать значения прибыли, оборотных средств, выручки, затрат, объемов
производства и цены для того чтобы рентабельность повысилась на 5%.
В табл. 70 приведены значения коэффициентов приоритетности для
цели каждого уровня.
Таблица 70
Значения коэффициентов приоритетности
Коэффициенты
приоритетности
α
β
Уровень 1
Уровень 2
Уровень 3
0,6
0,4
0,52
0,48
0,8
0,2
Задача 3. Пользуясь данными рис. 42, табл. 65-66, рассчитайте
коэффициент определенности гипотезы «ожидается снижение цены на
товар» при ct(E2) = 0,8; ct(C3) = 0,4; ct(C4) = 0,5; ct(C5) = 0,3;
ct(E8) = 0,6; ct(E9) = 0,5; ct(E10) = 0,6; ct(пр1) = 0,8; ct(пр2) = 0,9;
ct(пр3) = 0,2.
92
Вариант 3
Задача 1. Разработайте компьютерную модель формирования
решения путем вычисления его ранга в таблице «Стоимость –
эффективность» по каждому критерию и в таблице «Стоимость –
критерий» по данным табл. 71.
Таблица 71
Таблица вариантов капиталовложений
Вариант решения
В1
В2
В3
Коэффициент значимости критерия (j)
Общие издержки,
распределенные по
критериям
К1
К2
К3
50
110
54
40
90
25
30
40
33
0,2
0,3
0,5
Общие доходы,
распределенные по
критериям
К1
К2
К3
11
19
25
25
33
45
36
87
14



Задача 2. На предприятия в отчетный период получены следующие
значения показателей финансово-хозяйственной деятельности: средняя
стоимость оборотных средств 1457 ед., затраты на производство и
реализацию продукции 947 ед., объем реализованной продукции 88 ед.,
цена единицы продукции 15 ед.
Пользуясь деревом целей (рис. 35), определите, какими должны быть
стать значения прибыли, оборотных средств, выручки, затрат, объемов
производства и цены для того чтобы рентабельность повысилась на 3%.
В табл. 72 приведены значения коэффициентов приоритетности для
цели каждого уровня.
Таблица 72
Значения коэффициентов приоритетности
Коэффициенты
приоритетности
α
β
Уровень 1
Уровень 2
Уровень 3
0,6
0,4
0,7
0,3
0,6
0,4
Задача 3. Пользуясь данными рис. 42, табл. 65-66, рассчитайте
коэффициент определенности гипотезы «ожидается снижение цены на
товар» при ct(E2) = 0,8; ct(C3) = 0,4; ct(C4) = 0,5; ct(C5) = 0,3;
ct(E8) = 0,6; ct(E9) = 0,5; ct(E10) = 0,6; ct(пр1) = 0,7; ct(пр2) = 0,6;
ct(пр3) = 0,8.
93
Вариант 4
Задача 1. Разработайте компьютерную модель формирования
решения путем вычисления его ранга в таблице «Стоимость –
эффективность» по каждому критерию и в таблице «Стоимость –
критерий» по данным табл. 73.
Таблица 73
Таблица вариантов капиталовложений
Вариант решения
В1
В2
В3
Коэффициент значимости критерия (j)
Общие издержки,
распределенные по
критериям
К1
К2
К3
75
65
78
60
55
59
90
100
89
0,5
0,4
0,1
Общие доходы,
распределенные по
критериям
К1
К2
К3
111
98
82
105
75
63
135
106
97



Задача 2. На предприятия в отчетный период получены следующие
значения показателей финансово-хозяйственной деятельности: средняя
стоимость оборотных средств 106387 ед., затраты на производство и
реализацию продукции 102045 ед., объем реализованной продукции
1000 ед., цена единицы продукции 150 ед.
Пользуясь деревом целей (рис. 35), определите, какими должны быть
стать значения прибыли, оборотных средств, выручки, затрат, объемов
производства и цены для того чтобы рентабельность повысилась на 3%.
В табл. 74 приведены значения коэффициентов приоритетности для
цели каждого уровня.
Таблица 74
Значения коэффициентов приоритетности
Коэффициенты
приоритетности
α
β
Уровень 1
Уровень 2
Уровень 3
0,6
0,4
0,7
0,3
0,6
0,4
Задача 3. Пользуясь данными рис. 42, табл. 65-66, рассчитайте
коэффициент определенности гипотезы «ожидается снижение цены на
товар» при ct(E2) = 0,5; ct(C3) = 0,8; ct(C4) = 0,9; ct(C5) = 0,7;
ct(E8) = 0,1; ct(E9) = 0,9; ct(E10) = 0,9; ct(пр1) = 0,8; ct(пр2) = 0,6;
ct(пр3) = 0,7.
94
Вариант 5
Задача 1. Разработайте компьютерную модель формирования
решения путем вычисления его ранга в таблице «Стоимость –
эффективность» по каждому критерию и в таблице «Стоимость –
критерий» по данным табл. 75.
Таблица 75
Таблица вариантов капиталовложений
Вариант решения
В1
В2
В3
Коэффициент значимости критерия (j)
Общие издержки,
распределенные по
критериям
К1
К2
К3
14
25
14
11
26
29
12
21
33
0,4
0,2
0,4
Общие доходы,
распределенные по
критериям
К1
К2
К3
23
36
11
15
34
45
19
17
52



Задача 2. На предприятия в отчетный период получены следующие
значения показателей финансово-хозяйственной деятельности: средняя
стоимость оборотных средств 9245 ед., затраты на производство и
реализацию продукции 79324 ед., объем реализованной продукции
2451 ед., цена единицы продукции 34 ед.
Пользуясь деревом целей (рис. 35), определите, какими должны быть
стать значения прибыли, оборотных средств, выручки, затрат, объемов
производства и цены для того чтобы рентабельность повысилась на 7%.
В табл. 76 приведены значения коэффициентов приоритетности для
цели каждого уровня.
Таблица 76
Значения коэффициентов приоритетности
Коэффициенты
приоритетности
α
β
Уровень 1
Уровень 2
Уровень 3
0,9
0,1
0,7
0,3
0,6
0,4
Задача 3. Пользуясь данными рис. 42, табл. 65-66, рассчитайте
коэффициент определенности гипотезы «ожидается снижение цены на
товар» при ct(E2) = 0,1; ct(C3) = 0,3; ct(C4) = 0,5; ct(C5) = 0,2;
ct(E8) = 0,9; ct(E9) = 0,9; ct(E10) = 0,9; ct(пр1) = 0,7; ct(пр2) = 0,8;
ct(пр3) = 0,9.
95
Вариант 6
Задача 1. Разработайте компьютерную модель формирования
решения путем вычисления его ранга в таблице «Стоимость –
эффективность» по каждому критерию и в таблице «Стоимость –
критерий» по данным табл. 77.
Таблица 77
Таблица вариантов капиталовложений
Вариант решения
В1
В2
В3
Коэффициент значимости критерия (j)
Общие издержки,
распределенные по
критериям
К1
К2
К3
102
158
96
145
162
105
167
187
124
0,5
0,3
0,2
Общие доходы,
распределенные по
критериям
К1
К2
К3
154
163
187
166
179
126
178
196
199



Задача 2. На предприятия в отчетный период получены следующие
значения показателей финансово-хозяйственной деятельности: средняя
стоимость оборотных средств 9245 ед., затраты на производство и
реализацию продукции 19874 ед., объем реализованной продукции
367 ед., цена единицы продукции 68 ед.
Пользуясь деревом целей (рис. 35), определите, какими должны быть
стать значения прибыли, оборотных средств, выручки, затрат, объемов
производства и цены для того чтобы рентабельность повысилась на 6%.
В табл. 78 приведены значения коэффициентов приоритетности для
цели каждого уровня.
Таблица 78
Значения коэффициентов приоритетности
Коэффициенты
приоритетности
α
β
Уровень 1
Уровень 2
Уровень 3
0,9
0,1
0,7
0,3
0,6
0,4
Задача 3. Пользуясь данными рис. 42, табл. 65-66, рассчитайте
коэффициент определенности гипотезы «ожидается снижение цены на
товар» при ct(E2) = 0,3; ct(C3) = 0,6; ct(C4) = 0,2; ct(C5) = 0,1;
ct(E8) = 0,4; ct(E9) = 0,6; ct(E10) = 0,9; ct(пр1) = 0,6; ct(пр2) = 0,8;
ct(пр3) = 0,6.
96
Вариант 7
Задача 1. Разработайте компьютерную модель формирования
решения путем вычисления его ранга в таблице «Стоимость –
эффективность» по каждому критерию и в таблице «Стоимость –
критерий» по данным табл. 79.
Таблица 79
Таблица вариантов капиталовложений
Вариант решения
В1
В2
В3
Коэффициент значимости критерия (j)
Общие издержки,
распределенные по
критериям
К1
К2
К3
16
25
33
25
13
44
69
45
19
0,3
0,4
0,3
Общие доходы,
распределенные по
критериям
К1
К2
К3
22
29
36
36
20
48
71
49
21



Задача 2. На предприятия в отчетный период получены следующие
значения показателей финансово-хозяйственной деятельности: средняя
стоимость оборотных средств 10547 ед., затраты на производство и
реализацию продукции 32000 ед., объем реализованной продукции
1500 ед., цена единицы продукции 26 ед.
Пользуясь деревом целей (рис. 35), определите, какими должны быть
стать значения прибыли, оборотных средств, выручки, затрат, объемов
производства и цены для того чтобы рентабельность повысилась на 5%.
В табл. 80 приведены значения коэффициентов приоритетности для
цели каждого уровня.
Таблица 80
Значения коэффициентов приоритетности
Коэффициенты
приоритетности
α
β
Уровень 1
Уровень 2
Уровень 3
0,6
0,4
0,7
0,3
0,6
0,4
Задача 3. Пользуясь данными рис. 42, табл. 65-66, рассчитайте
коэффициент определенности гипотезы «ожидается снижение цены на
товар» при ct(E2) = 0,9; ct(C3) = 0,6; ct(C4) = 0,2; ct(C5) = 0,1;
ct(E8) = 0,4; ct(E9) = 0,6; ct(E10) = 0,9; ct(пр1) = 0,9; ct(пр2) = 0,8;
ct(пр3) = 0,6.
97
Вариант 8
Задача 1. Разработайте компьютерную модель формирования
решения путем вычисления его ранга в таблице «Стоимость –
эффективность» по каждому критерию и в таблице «Стоимость –
критерий» по данным табл. 81.
Таблица 81
Таблица вариантов капиталовложений
Вариант решения
В1
В2
В3
Коэффициент значимости критерия (j)
Общие издержки,
распределенные по
критериям
К1
К2
К3
29
11
45
37
16
25
33
19
19
0,6
0,2
0,2
Общие доходы,
распределенные по
критериям
К1
К2
К3
36
25
55
42
28
31
42
21
29



Задача 2. На предприятия в отчетный период получены следующие
значения показателей финансово-хозяйственной деятельности: средняя
стоимость оборотных средств 8874 ед., затраты на производство и
реализацию продукции 47123 ед., объем реализованной продукции
502 ед., цена единицы продукции 101 ед.
Пользуясь деревом целей (рис. 35), определите, какими должны быть
стать значения прибыли, оборотных средств, выручки, затрат, объемов
производства и цены для того чтобы рентабельность повысилась на 3%.
В табл. 82 приведены значения коэффициентов приоритетности для
цели каждого уровня.
Таблица 82
Значения коэффициентов приоритетности
Коэффициенты
приоритетности
α
β
Уровень 1
Уровень 2
Уровень 3
0,6
0,4
0,7
0,3
0,6
0,4
Задача 3. Пользуясь данными рис. 42, табл. 65-66, рассчитайте
коэффициент определенности гипотезы «ожидается снижение цены на
товар» при ct(E2) = 0,7; ct(C3) = 0,2; ct(C4) = 0,1; ct(C5) = 0,3;
ct(E8) = 0,8; ct(E9) = 0,2; ct(E10) = 0,2; ct(пр1) = 0,9; ct(пр2) = 0,6;
ct(пр3) = 0,7.
98
Вариант 9
Задача 1. Разработайте компьютерную модель формирования
решения путем вычисления его ранга в таблице «Стоимость –
эффективность» по каждому критерию и в таблице «Стоимость –
критерий» по данным табл. 83.
Таблица 83
Таблица вариантов капиталовложений
Вариант решения
В1
В2
В3
Коэффициент значимости критерия (j)
Общие издержки,
распределенные по
критериям
К1
К2
К3
55
25
14
51
36
19
59
51
45
0,1
0,3
0,6
Общие доходы,
распределенные по
критериям
К1
К2
К3
59
35
22
62
45
36
66
25
41



Задача 2. На предприятия в отчетный период получены следующие
значения показателей финансово-хозяйственной деятельности: средняя
стоимость оборотных средств 894 ед., затраты на производство и
реализацию продукции 2647 ед., объем реализованной продукции
200 ед., цена единицы продукции 16 ед.
Пользуясь деревом целей (рис. 35), определите, какими должны быть
стать значения прибыли, оборотных средств, выручки, затрат, объемов
производства и цены для того чтобы рентабельность повысилась на 4%.
В табл. 84 приведены значения коэффициентов приоритетности для
цели каждого уровня.
Таблица 84
Значения коэффициентов приоритетности
Коэффициенты
приоритетности
α
β
Уровень 1
Уровень 2
Уровень 3
0,7
0,3
0,8
0,2
0,6
0,4
Задача 3. Пользуясь данными рис. 42, табл. 65-66, рассчитайте
коэффициент определенности гипотезы «ожидается снижение цены на
товар» при ct(E2) = 0,3; ct(C3) = 0,2; ct(C4) = 0,1; ct(C5) = 0,3;
ct(E8) = 0,8; ct(E9) = 0,2; ct(E10) = 0,2; ct(пр1) = 0,6; ct(пр2) = 0,5;
ct(пр3) = 0,8.
99
Вариант 10
Задача 1. Разработайте компьютерную модель формирования
решения путем вычисления его ранга в таблице «Стоимость –
эффективность» по каждому критерию и в таблице «Стоимость –
критерий» по данным табл. 85.
Таблица 85
Таблица вариантов капиталовложений
Вариант решения
В1
В2
В3
Коэффициент значимости критерия (j)
Общие издержки,
распределенные по
критериям
К1
К2
К3
56
55
68
78
45
62
94
120
88
0,4
0,3
0,3
Общие доходы,
распределенные по
критериям
К1
К2
К3
62
62
72
61
54
79
96
100
92



Задача 2. На предприятия в отчетный период получены следующие
значения показателей финансово-хозяйственной деятельности: средняя
стоимость оборотных средств 894 ед., затраты на производство и
реализацию продукции 6874 ед., объем реализованной продукции
300 ед., цена единицы продукции 24 ед.
Пользуясь деревом целей (рис. 35), определите, какими должны быть
стать значения прибыли, оборотных средств, выручки, затрат, объемов
производства и цены для того чтобы рентабельность повысилась на 5%.
В табл. 86 приведены значения коэффициентов приоритетности для
цели каждого уровня.
Таблица 86
Значения коэффициентов приоритетности
Коэффициенты
приоритетности
α
β
Уровень 1
Уровень 2
Уровень 3
0,7
0,3
0,8
0,2
0,6
0,4
Задача 3. Пользуясь данными рис. 42, табл. 65-66, рассчитайте
коэффициент определенности гипотезы «ожидается снижение цены на
товар» при ct(E2) = 0,3; ct(C3) = 0,9; ct(C4) = 0,9; ct(C5) = 0,4;
ct(E8) = 0,8; ct(E9) = 0,9; ct(E10) = 0,9; ct(пр1) = 0,8; ct(пр2) = 0,6;
ct(пр3) = 0,8.
100
Приложение
Формулы обратных вычислений для типовых детерминированных зависимостей
Целевая установка
+
+
+
y = f(x (α), z (β))
Приросты аргументов
П = В (α)  З (β), α > β
В + В = k1В
З + З = k1З
y+ = f(x+(α), z-(β))
В + В = k1В
+
+
+
+
+

П = В (α)  З (β), α > β
y+ = f(x+(α), z-(β))

Р 
П ( )
О  ()
,α>β
y+ = f(x(α), z-(β))
Р 
П  ( )
О  ()
,α>β
y+ = f(x+(α), z-(β))
+
+

В = К (α)  Ц (β), α > β
З
З  З =
k2
П + П = k1П
O
О  О =
k2
П  П =
О  О =
O
k1
O
k2
K + K = k1K
Ц
Ц  Ц =
k2
Коэффициенты для расчетов
k1 
П  П  k 2 З
В
k2 
З  ( П  П )   В
З (  )
k1 
  П  П    B   З
В
k2 
З
k1 B  П  П 
k2 
P  P
k1P
k2 
k1 ( Р  Р )
Р
k1 
  P
P
P 
P  P
ОР
 П
Р
k1   Р
О   П
( В  В) k 2
k1 
КЦ
k2 

Ц К

2



 Ц  К   4 КЦ  В 






В

2 К 
Ц 

101
*
Для примера выполним расчет коэффициентов:
Целевая установка: y+ = f(x+(α), z-(β)). Здесь и далее сумма коэффициентов приоритетности всегда равна единице, т.е. α + β = 1.
Введем индивидуальные коэффициенты, с помощью которых определяются искомые приросты аргументов: x + x = k1x и
z
z  z =
. Это позволяет записать задачу обратных вычислений в следующем виде:
k2
z

 y  y  k1 x  k
2

 k1 x  x  

z

z
k2

Поскольку y + y здесь уже рассматриваются в качестве аргумента, от которого зависят приросты x: и z, следует определить
диапазон исходных значений y, α и β , при которых задача имеет смысл. Для этого следует решить систему неравенств вида:
 k1  1
.

k 2  1
Выразим из первого уравнения
z
= k1x – (y + y), подставим полученное выражение во второе уравнение
k2
k1 x  x


 k1βx – βx = αz – k1αx + α(y + y)  k1βx + k1αx = αz + βx + α(y + y).
z  k1 x  ( y  y ) 
Тогда формула для расчета коэффициента k1 
z
 z   x   ( y  y )
и k2 
.
k1 x  ( y  y )
x
102