- •Содержание
- •VII. Статистические методы 167
- •Введение
- •I. Общие методы работы
- •1.1. Работа с формулами
- •1.1.1. Общие сведения
- •Вычисления сложных выражений
- •1.1.2. Задание
- •1.2. Математические функции
- •1.2.1. Общие сведения
- •1.2.2. Пример
- •1.2.3. Варианты заданий
- •1.3. Вычисления с условиями
- •1.3.1. Общие сведения
- •1.4. Работа со справочниками
- •1.4.1. Общие сведения
- •1.4.2. Варианты заданий
- •«Разносортица»
- •«Маршрутное такси»
- •«Гостиница»
- •«Автовокзал»
- •«Книжное издательство»
- •«Продукты»
- •«Коттеджи»
- •«Гастроли»
- •«Туристическое агентство»
- •«Комплектующие»
- •«Авиаперевозки»
- •«Винный погребок»
- •«Сберкасса»
- •«Мебельная фабрика»
- •16. «Сага о таре»
- •1.5. Работа с диаграммами
- •1.5.1. Общие сведения
- •1.5.2. Задание на построение диаграммы
- •1. Изменение настроек параметров диаграммы:
- •3. Форматирование рядов данных и их элементов:
- •4. Форматирование осей диаграммы:
- •5. Форматирование сетки, стен и основания:
- •6. Форматирование легенды:
- •1.5.3. Варианты заданий
- •1.6. Собственные функции
- •1.6.1. Общие сведения
- •1.6.2. Общие сведения о Visual Basic for Excel
- •Математические операции
- •Математические функции
- •1.6.3. Варианты заданий
- •II. Численные методы
- •2.1. Решение алгебраических уравнений Средство «Подбор параметра»
- •2.1.1. Общие сведения
- •2.1.2. Пример
- •2.1.3. Варианты заданий
- •2.2. Решение систем уравнений
- •2.2.1. Общие сведения
- •2.2.2. Реализация расчетов в Excel
- •2.2.3. Варианты заданий
- •2.3. Задачи оптимизации
- •2.3.1. Общие сведения
- •2.3.2. Пример
- •2.3.3. Варианты заданий
- •III. Базы данных в ms Excel
- •Каждое из последующих заданий необходимо выполнять на отдельном листе!!!
- •Сортировка
- •3.1.1. Общие сведения
- •3.1.2. Варианты заданий
- •Фильтрация данных
- •3.2.1. Общие сведения
- •Варианты заданий
- •Средство «Итоги»
- •3.3.1. Общие сведения
- •Сводные таблицы
- •3.5. Функции для работы с базами данных
- •3.6. Консолидация данных
- •3.6.2. Варианты заданий
- •3.7. Контрольная работа по теме «Базы данных в Excel»
- •3.7.1. Указания
- •2. Скопируйте указанный файл в свою рабочую папку и вся дальнейшая работа должна производиться только с этой копией.
- •3.7.2. Варианты заданий
- •Вариант 12
- •Вариант 13
- •Вариант 14
- •Вариант 15
- •IV. Макросы в ms Excel
- •4.1. Макросы для автоматизации работ
- •4.1.1. Пример
- •4.2. Вычислительные макросы
- •4.2.1. Пример 1. Расчет точки безубыточности
- •4.2.2. Пример 2. Моделирование процесса налогообложения [8]
- •4.3. Использование макросов для создания интерфейса
- •V. Технология создания информационной системы средствами ms Excel
- •5.1. Постановка задачи
- •5.2. Требования к системе
- •5.3. Общая архитектура ис
- •5.3.1. Проектирование общей архитектуры
- •5.3.2. Создание общей архитектуры
- •5.3.2.1. Создание объектов ис
- •5.3.2.2. Организация переходов между объектами
- •5.3.2.3. Этапы создания интерфейса
- •5.4. Организация работы с базой данных
- •5.4.1. Заполнение таблиц модельными данными
- •5.4.2. Работа с данными
- •5.4.3. Сортировка
- •5.4.4. Поиск данных
- •5.4.5. Отчеты
- •5.4.5.1. Использование функций
- •5.4.5.2. Использование сводных таблиц
- •5.4.5.3. Использование элементов управления
- •5.4.5.5. Использование встроенных функций
- •Функция должна быть в англоязычном варианте.
- •5.4.5.6. Варианты заданий
- •5.4.6. Расчет заработной платы
- •5.4.6.1. Постановка задачи
- •5.4.6.2. Интерфейс расчета заработной платы
- •5.4.6.3. Реализация расчетов
- •VI. Экономические расчеты
- •6.1. Задачи на проценты
- •6.1.1. Общие сведения
- •6.1.2. Пример.
- •6.1.3. Варианты заданий
- •6.2. Финансовые функции
- •6.2.1. Общие сведения
- •Бс(Ставка, Кпер, Плт, Пс, Тип).
- •6.2.3. Варианты заданий
- •6.3. Анализ межотраслевого баланса (модель Леонтьева)
- •Основные понятия
- •Математическая модель межотраслевого баланса
- •6.3.4. Варианты заданий
- •6.4. Задача об эквивалентности ставок [1]
- •6.4.1. Основные формулы
- •6.4.2. Постановка задачи
- •6.4.3. Варианты заданий
- •6.5. Методы анализа проектов (использование средства «Подбор параметра»)
- •6.5.1. Термины и определения
- •6.5.2. Примеры
- •Варианты заданий
- •6.6. Выбор оптимального портфеля инвестиций
- •6.6.1. Основные определения
- •6.6.2. Пример
- •6.6.3. Варианты заданий
- •6.7. Вычисление налогов
- •6.7.1. Предварительные замечания
- •6.7.2. Пример.
- •6.7.3. Варианты заданий
- •6.8. Моделирование динамических процессов
- •6.8.1. Общие сведения
- •6.8.2. Порядок выполнения работы
- •6.8.3. Пример
- •Результаты должны отражать основные закономерности процесса
- •6.8.4. Варианты заданий
- •Производство в условиях постоянного спроса
- •Конкуренция
- •Сезонное производство
- •Рыночные отношения
- •Взаимопоставки
- •Цены в условиях ограниченного объема выпуска
- •Северный завоз
- •Два пароходства
- •Последовательные перевозки
- •Антимонопольная система
- •Конъюнктура
- •Количество информации в Интернет
- •Валютная интервенция
- •Реклама
- •VII. Статистические методы
- •7.1. Определение характеристик случайных величин
- •7.1.1. Содержание работы
- •7.1.2. Варианты заданий
- •7.2. Дисперсионный анализ
- •7.2.1. Общие сведения
- •7.2.1. Пример
- •7.2.3. Методы, применяемые после дисперсионного анализа
- •7.2.4. Варианты заданий
- •7.3. Регрессионный анализ
- •7.3.1.Общие сведения
- •7.3.2. Порядок выполнения работы
- •7.3.3. Проверка уравнения регрессии на адекватность
- •7.3.4. Использование уравнения для прогноза
- •7.4. Кластерный анализ
- •7.4.1. Общие положения.
- •7.4.2. Примеры
- •7.4.3. Формализация процесса кластеризации
- •7.4.4. Порядок выполнения работы
- •7.4.5. Задания
- •7.5. Анализ временных рядов
- •7.5.1. Общие сведения
- •7.5.2. Пример
- •Литература
- •Приложения
- •Технология генерации модельных данных
- •Приложение 2 Районы и города Чувашии в цифрах [9]
- •Тексты макросов Текст макроса для кластерного анализа
- •Текст макроса для решения систем дифференциальных уравнений
- •Текст макроса для генерации временного ряда
- •Приложение 4 Транспорт и связь
- •Статистические данные по регионам рф [5]
2.3. Задачи оптимизации
2.3.1. Общие сведения
Различные аспекты оптимизации занимают очень важное место в бизнесе и деятельности современных организаций и предприятий. С подобными задачами в своей повседневной работе сталкиваются менеджеры, экономисты, финансисты, фермеры и др. Проблемы оптимизации присутствуют в самых различных процессах производства:
-
поставка сырья;
-
оптимальный выпуск продукции;
-
оптимальное управление запасами;
-
оптимальное распределение ресурсов;
-
планирования инвестиций;
-
оптимальный рацион (смесь, сплав);
-
назначение на должность;
-
оптимальная замена оборудования и т. д.
Модели всех задач на оптимизацию состоят из следующих элементов:
1. Переменные – неизвестные величины, которые нужно найти при решении задачи.
2. Целевая функция – величина, которая зависит от переменных и является целью, ключевым показателем эффективности или оптимальности модели.
3. Ограничения – условия, которым должны удовлетворять переменные.
2.3.2. Пример
Фирма занимается составлением диеты, которая должна содержать по крайней мере 20 единиц белков, 30 единиц углеводов, 10 единиц жиров и 40 единиц витаминов. Как дешевле всего достичь этого при указанных в таблице ценах (ден. ед.) за 1 кг (или 1 л) пяти имеющихся продуктов?
|
Хлеб |
Соя |
Сушеная рыба |
Фрукты |
Молоко |
Белки |
2 |
12 |
10 |
1 |
2 |
Углеводы |
12 |
0 |
0 |
4 |
3 |
Жиры |
1 |
8 |
3 |
0 |
4 |
Витамины |
2 |
2 |
4 |
6 |
2 |
Цена |
12 |
36 |
32 |
18 |
10 |
Рассмотрим экономико-математическую модель решения данной задачи.
1. Найти количество каждого продукта x, y, z, t, f ,
где
x – количество хлеба,
y – количество сои,
z – количество сушеной рыбы,
t – количество фруктов,
f – количество молока,
2. При котором общая стоимость S=12*x+36*y+32*z+ 18*t+10*f – min
3. При ограничениях:
количество белков = 12*x+12*y+10*z+ 1*t+2*f >=20;
количество углеводов = 12*x+0*y+0*z+ 4*t+3*f >=30;
количество жиров = 1*x+8*y+3*z+ 0*t+4*f >=10;
количество витаминов = 2*x+2*y+4*z+ 6*t+2*f >=40;
и предельных условиях: x, y, z, t, f >=0.
Рассмотрим этапы реализации данной задачи в MS Excel.
В Excel необходимо создать таблицу с формулами, которые связывают план, ограничения и целевую функцию Стоимость (рис. 2.2):
Рис. 2.2. Таблица с исходными данными и формульными зависимостями
В столбец «Вошло» в каждую ячейку вводится формула вычисления количества компонентов, вошедших в диету:
=СУММПРОИЗВ(Норма; План).
В целевую ячейку «Стоимость» вводится формула:
=СУММПРОИЗВ(Цена; План).
Программа Поиск решений запускается командой Сервис – Поиск решения. В полях Установить целевую ячейку, Изменяя ячейки, Ограничения вводятся соответствующие данные (рис. 2.3).
Рис. 2.3. Окно Поиск решения
Так как это линейная модель (целевая функция S является линейной), то необходимо установить в окне Параметры поиска решений переключатель в позицию Линейная модель. После нажатия на кнопку Выполнить в появившемся окне Результаты поиска решения укажите Отчет по устойчивости. Результаты поиска решения и полученный отчет представлены на рисунках 2.4 и 2.5.
Рис. 2.5. Отчет по устойчивости
Отчет по устойчивости отражает чувствительность структуры полученного плана до изменений начальных данных и дальнейшие действия менеджера с целью улучшения результатов. Такой отчет не создается для моделей, значения в которых ограничены множеством целых чисел.
1. Результирующее значение – оптимальный план задачи.
В данной конкретной задаче оптимальный рацион минимальной стоимости 150 д. ед. состоит из 0,83 кг. сушеной рыбы, 5 кг. фруктов и 3,33 л. молока.
2. Нормированная стоимость неизвестных плана указывает, как изменится стоимость рациона при желании добавить в его состав «невыгодный» продукт, например, единица хлеба в рационе увеличит его стоимость на 0,2 д. ед., единица сои – на 4,6 д. ед.
3. Коэффициенты целевой функции.
4, 5. Границы изменений значений коэффициентов целевой функции при условии, что количество оптимальной продукции (план) не изменится. Например, если целевой коэффициент Фруктов (КФ) равен 18 (цена за 1 кг. товара), то изменяя его в рамках 18-0,22<КФ<18+2, 17,78<КФ<20 план не изменится, но значения стоимости может уменьшиться или увеличиться.
6. Количество использованных ресурсов;
7. Теневые цены показывают уровень влияния значения норм (в сравнении с другими ресурсами) на стоимость рациона относительно ее увеличения. В данном примере нормы на состав витаминов более «влиятельные» на стоимость, чем белки (2,5>2,2).
Например, увеличить норму витаминов на 1 единицу (до 41), то стоимость увеличиться на 2,5 д. ед. и будет составлять 152,5 д. ед.
8. Нормы белков, жиров, углеводов и витаминов в дневном рационе. Соответствуют условию задачи.
9, 10. Задают диапазон для 8, в котором действует теневая цена 7 (аналогично 4, 5).