Освоение возможностей табличного процессора Excel

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

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

 Характеристики каждого  поля определяются рядом параметров (свойств).

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

Одной из важнейших функций, которые обычно предоставляют настольные СУБД, является возможность создания различных отчетов — документов, содержащих данные из таблиц базы данных. Отличительной особенностью Microsoft Access является наличие очень мощного генератора отчетов, который позволяет создавать отчеты высокого качества. Наличие мастеров, генерирующих автоматически или при помощи диалога с пользователем самые разнообразные отчеты, значительно облегчает трудоемкий процесс их создания.

Решение штатного расписания при помощи программы  ACCESS.

Следующим шагом является создание таблицы «Штатное расписание» в  базе данных  Access:

1. Создать в режиме конструктора таблицу и заполнить ее следующим образом, сохранить и дать название «штатное расписание»

 

 

2)Далее открываем таблицу «штатное  расписание « в обычном режиме  и заполняем ее (данные берем  из задачи-2 выполненной в Excel)

 

 

3)Далее создаем запросы.  Первый запрос-запрос с выборкой.

Первый  запрос идет на выборку. Простой запрос на выборку служит для отбора данных по какому-либо признаку или комбинации признаков. В данном случае выбираем должности. С зарплатой меньше  либо равно 15,00р

Затем смотрим, что получилось:

Второй запрос идет с параметром, то есть при вызывании запроса  будет появляться диалоговое окно, где нужно будет выбрать должность, где  нужно посмотреть количество работников и зарплату. При создании запроса в режиме конструктора вводим в строку «Условие отбора»: [Введите  должность]:

 

Далее сохраняем и смотрим, что получилось:

 

 

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

 

Для предоставления результатов  запросов в наглядном виде, создаются  документы – отчеты. Отчеты можно  создавать в режиме конструктора или с помощью специальной  программы, входящей в состав СУБД –  мастера отчетов. Режим конструктора предназначен для подготовленных пользователей.

 

 

 

 

 

 

Решение транспортной задачи.

Задача 3.

Выполнение третьего задания  предполагает решение транспортной задачи, включающее:

1. построение математической модели
2. непосредственное решение задачи.

 

Вариант 3/18

Условия транспортной задачи

 

		Пункты назначения					Наличие
		В1	В2	В3	В4	В5
Пункты отправления	А1	56	15	20	40	79	210
	А2	5	19	46	13	27	110
	А3	25	21	31	16	57	150
	Потребность	60	100	90	120	100

Решение:

В общем виде формулировка транспортной задачи осуществляется следующим  образом: требуется перевезти определенное количество однородного груза из m пунктов отправления в n пунктов назначения. Известны расходы на перевозку единицы груза из каждого пункта отправления в каждый пункт назначения.

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

Для составления экономико-математической модели задачи введем обозначения:

 n число пунктов отправления;

m число пунктов назначения;

ai общее количество груза в I-м пункте отправления;

bj общее количество груза, необходимое в j-м пункте назначения;

cij затраты на транспортировку единицы груза из I-го пункта

отправления в j-й пункт назначения;

Z совокупные затраты на перевозку всего груза;

xij исходно неизвестное количество груза, которое перевозится из

i-го пункта отправления  в j-й пункт назначения.

Экономико-математическая модель задачи:

 

 

 

 

В рассмотренной модели транспортной задачи предполагается, что суммарные  запасы поставщиков равны суммарным  запросам потребителей, т.е.:

 

Такая задача называется задачей  с правильным балансом, а модель задачи закрытая.

Математическая формулировка транспортной задачи такова: найти переменные задачи X=(x_ij), i=1,2,...,m; j=1,2,...,n, удовлетворяющие системе ограничений (цифра 2 на математической модели) , (3), условиям неотрицательности (4) и обеспечивающие минимум целевой функции (1).                               Целевая функция минимизирует совокупные затраты на транспортировку всех партий грузов из всех пунктов отправления во все пункты назначения. Система ограничений (2)говорит о том, что весь груз из каждого пункта его сосредоточения должен быть вывезен. Система ограничений (3) говорит о том, что потребность в грузе в каждом пункте назначения должна быть удовлетворена. Система ограничений (4) говорит о том, что по любому маршруту некоторое количество груза либо перевозится, либо нет.

Теперь составим модель транспортной задачи для данной задачи:

Матрица исходных данных

Вводим переменные задачи (матрицу перевозок):

        x₁₁   x₁₂   x₁₃   x₁₄   x₁₅

Х=   x₂₁   x₂₂   x₂₃   x₂₄   x₂₅

           x₃₁  x₃₂   x₃₃   x₃₄   x₃₅

 

Записываем матрицу стоимостей:

           56    15    20    40  79

C=      5    19     46    13   27

           25    21    31    16   57

 

Целевая функция  задачи равняется сумме произведений всех соответствующих элементов  матриц C и X.

Z(X)= 56x₁₁ +15 x₁₂ + 20 x₁₃ + 40 x₁₄ + 79x₁₅ + 5x₂₁ + 19x₂₂ + 46x₂₃ +13x₂₄+27x₂₅ + 25x₃₁ + 21x₃₂ + 31x₃₃ + 16x₃₄ + 57x_35.

Составим систему ограничений  задачи. Сумма всех перевозок, стоящих в первой строке матрицы X, должна равняться запасам первого поставщика, а сумма перевозок во второй строке матрицы X равняться запасам второго поставщика:

x₁₁ + x₁₂ +  x₁₃ + x₁₄  + x₁₅ = 210

x₂₁ + x₂₂ +  x₂₃  + x₂₄  + x₂₅ = 110

x₃₁ + x₃₂ + x₃₃  + x₃₄ +  x₃₅ = 150

Это означает, что запасы поставщиков  вывозятся полностью.

Суммы перевозок, стоящих в каждом столбце матрицы X, должны быть равны  запросам соответствующих потребителей:

x₁₁ + x₂₁ + x₃₁ = 60

x₁₂ + x₂₂ + x₃₂ =100

x₁₃ + x₂₃ + x₃₃ =90

x₁₄ + x₂₄ + x₃₄ =120

x₁₅ + x₂₅ + x₃₅ = 100

 

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

X_iy >=0,  i=1, 2,…, m; j=1, 2…, n

Для решения задачи средством EXCEL «Поиск решения» необходимо разместить в рабочем листе исходные данные и подготовить поля для размещения условий и результатов решения  задачи (выбор ячеек листа произвольный). Таким образом, будет создана «Электронная модель транспортной задачи».

 

 

В матрице результата во все ячейки диапазона (С5:G7) внести «1» в качестве исходных значений объемов поставок xij, после решения задачи в этих ячейках будут находиться значения поставок, обеспечивающие минимальные затраты на перевозку груза.

Вводим в ячейки (C8;G8) итог поставок по потребителям: в ячейку C8 ввести формулу =СУММ(C5:C7) − итог поставок по1-му потребителю, скопировать эту формулу в ячейки (D8:G8);

Вводим в ячейки (H5:H7) итоги реализации мощности каждого из поставщиков: в ячейку H5 ввести формулу =СУММ(C5:G5) − итог реализации поставок от 1-го поставщика, скопировать эту формулу в ячейки (H6:H7);

Выделяем ячейку для ввода  формулы целевой функции,  в  этом случае В19 и ввести формулу =СУММПРОИЗВ(C14:G16;C5:G7) − суммарная стоимость перевозок по всем направлениям.

В окне Поиск решения указываем целевую ячейку В19 равную минимальному значению, а также вводим ограничения. Окно Поиска решения будет выглядеть следующим образом:

 

После проведенной подготовительной работы можно запустить задачу на выполнение. В результате решения ячейки (C5:G7) заполнились значениями объемов перевозок, которые обеспечивают минимальные суммарные затраты на транспортировку всего груза:

 

Целевая функция  минимизирует совокупные затраты на транспортировку  всех партий грузов из всех пунктов  отправления во все пункты назначения. Величина затрат равна 9700единиц.

Далее выводим отчет:

Microsoft Excel 12.0 Отчет по результатам
Рабочий лист: [задача 3.xlsx]Лист1
Отчет создан: 22.12.2012 19:23:51

Целевая ячейка (Минимум)
	Ячейка	Имя	Исходное значение	Результат
	$C$19	Целевая функция Мощность потребителей	9700	9700
Изменяемые ячейки
	Ячейка	Имя	Исходное значение	Результат
	$C$5	Потребители	0	0
	$D$5		100	100
	$E$5		90	90
	$F$5		20	20
	$G$5		0	0
	$C$6	Потребители	10	10
	$D$6		0	0
	$E$6		0	0
	$F$6		0	0
	$G$6		100	100
	$C$7	Потребители	50	50
	$D$7		0	0
	$E$7		0	0
	$F$7		100	100
	$G$7		0	0