Граф в excel как построить

Обновлено: 07.07.2024

Построить максимальный поток от истока I к стоку S в сети, заданной графом (см. рис.1):

В скобках заданы пропускные способности ребер в направлении от меньшего номера вершины к большему и в обратном направлении.

Решение задачи:

Согласно алгоритму Форда-Фалкерсона:

Строим произвольный начальный поток на сети.

Находим подмножество А вершин, достижимых из истока I по ненасыщенным ребрам.

Если S не принадлежит A, то построенный поток максимальный и задача решена. Если S принадлежит A, то переходим к пункту 4.

Выделяем путь из I к S, состоящий из ненасыщенных ребер, и увеличиваем поток x_ij по каждому ребру этого пути на величину, равную min по всем ребрам (i,j) выделенного пути. После построения нового потока возвращаемся к пункту 2 алгоритма.

Реализация в Excel

В качестве начального потока выбираем, например, поток, проходящий по ребрам 1-3-5-6. Максимальная величина потока, который можно пропустить по этим ребрам, равна 2.

На чистом рабочем листе заполняем форму решения задачи. В ячейки B 3: G 8 заносим данные о пропускных способностях ребер сети.

Ниже строим матрицу начального потока на сети.

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

Для заполнения нижней части матрицы воспользуемся функцией ТРАНСП(массив).

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

Для заполнения столбца В13:В17 установите курсор в ячейку В13, введите формулу = - ТРАНСП( C 12: G 12) и нажмите клавишу < ENTER > (знак <–> перед формулой вводится для того, чтобы матрица получилась антисимметричной). В ячейке появится знак ошибки. Затем нужно выделить диапазон В13:В17, нажать клавишу , а затем клавиши . В результате будет сформирован первый столбец матрицы потока. Остальные столбцы под нижней диагональю заполняются аналогично.

Ниже матрицы потока строим матрицу ненасыщенности ребер.

Для этого в ячейку В21 вводим формулу:

После чего выделяем диапазон В21: G26, нажимаем клавишу , а затем клавиши .

В результате третья матрица заполняется значениями, равными «недогрузке» ребер.

Находим ненасыщенные пути. Для этого в матрице ненасыщенности выбираем ненулевые элементы:

Таким образом, можно выбрать путь 1-2-4-6 с максимально возможным объемом дополнительного груза, равным 4.

Для удобства можно выписать процедуру поиска ненасыщенного пути справа от матрицы ненасыщенности ребер:

Выделяя потоки по выбранным ребрам каким-либо цветом, легко найти их минимум (см. рис.4). Строим справа матрицу дополнительного потока по аналогии с матрицей начального потока.

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

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

Задание для самостоятельной работы

Учащиеся выбирают номер варианта согласно списку.

Сеть содержит 10 вершин (рис. 5). Пропускные способности дуг заданы таблицей. Найти максимальный поток между вершинами 1 и 10 (номера вариантов указаны в верхнем левом углу таблицы).

В статье Поиск решения MS EXCEL (6.3). Задача коммивояжера (полный граф, линейная модель) рассматривались модели, в которых коммивояжёру требуется посетить все имеющиеся города. В этой задаче посещение всех городов не требуется.

Задача

Имеется 11 городов, координаты которых известны. Маршруты проложены только между некоторыми городами (неполный граф). Найти кратчайший путь между 2-мя заданными городами. Построить Линейную модель.

Создание модели

Так как даны координаты городов, то сначала найдем расстояния между ними (см. файл примера ).

Расстояния рассчитаем с помощью формулы: = КОРЕНЬ((ИНДЕКС($C$7:$D$17;ПОИСКПОЗ($A30;$A$7:$A$17;0);1)-ИНДЕКС($C$7:$D$17;ПОИСКПОЗ(B$29;$A$7:$A$17;0);1))^2 +(ИНДЕКС($C$7:$D$17;ПОИСКПОЗ($A30;$A$7:$A$17;0);2)-ИНДЕКС($C$7:$D$17;ПОИСКПОЗ(B$29;$A$7:$A$17;0);2))^2)

Теперь создадим линейную модель для решения задачи с помощью Поиска решения .

Совет : Вводная статья про Поиск решения в MS EXCEL 2010 находится здесь .

Переменные (выделено зеленым) . В качестве переменных модели следует взять номера маршрутов между городами: если маршрут включен в кратчайший путь, то переменная =1, если нет, то =0. Ограничения (выделено синим) . Необходимо, чтобы из каждого города, в котором побывал путешественник, был входящий и выходящий маршрут. Так как входящий маршрут обозначается 1, а исходящий -1, то их сумма, равная 0, будет означать, что в город вошли и вышли (включен в кратчайший путь). Исключение составляют город – начальная точка путешествия (сумма =-1) и город – конечная точка (сумма =1). Изменяя ограничение в синем столбце, можно задавать начальные и конечные пункты путешествия. Целевая функция (выделено красным) . Длина маршрута должна быть минимальной.

Примечание : для удобства настройки Поиска решения используются именованные диапазоны .

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

Найденное Решение

Поиск решения гарантировано найдет самый короткий маршрут, т.к. модель линейная.

Изменив начальный и конечный пункт путешествия, и перезапустив Поиск решения , получим другой маршрут.

Будьте внимательны, не все пары конечных и начальных пунктов допустимы. Например, задав путешествие из Москвы в Копенгаген, Поиск решения не найдет маршрут, т.к. для этого потребуется «двигаться назад», а в маршрутах между городами обратные пути не прописаны (маршруты, конечно, можно добавить в столбцы J:M, но не забудьте изменить и другие формулы).

1. Цель работы: изучение информационной технологии использования встроенных вычислительных функций Excel для финансового анализа.

2.Приборы и оборудование: Персональный компьютер.

3 . Выполнение работы.

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

Порядок работы

Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу

Рис 2. Создание стиля оформления шапки таблицы

2. Введите заголовок таблицы «Финансовая сводка за неделю (тыс. р.)», начиная с ячейки А1.

3. Для оформления шапки таблицы выделите ячейки на третьей строке A 3: D 3 и создайте стиль для оформления. Для этого выполните команду Формат/Стиль и в открывшемся окне Стиль (рис.2) наберите имя стиля «Шапка таблиц» и нажмите кнопку Изменить. В открывшемся окне на вкладке Выравнивание задайте Переносить по словам и выберите горизонтальное и вертикальное выравнивание — по центру, на вкладке Число укажите формат — Текстовый. После этого нажмите кнопку Добавить.

4. На третьей строке введите названия колонок таблицы — « Дни недели», «Доход», «Расход », « Финансовый результат», далее заполните таблицу исходными данными

. Для ввода дней недели наберите «Понедельник» и произведите автокопирование до «Воскресенья» (левой кнопкой мыши за маркер автозаполнения в правом нижнем углу ячейки).

5. Произведите расчеты в графе « Финансовый результат » последующей формуле: = Доход - Расход

для этого в ячейке D 4 наберите формулу = В4-С4.

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

6. Для ячеек с результатом расчетов задайте формат — «Денежный» с выделением отрицательных чисел красным цветом ( Формат/Ячейки/ вкладка Число/формат — Денежный / отрицательные числа — красные. Число десятичных знаков задайте равное 2).

Обратите внимание, как изменился цвет отрицательных значений финансового результата на красный

7. Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (кнопка fx ). Функция «Среднее значение» (СРЗНАЧ) находится в разделе «Статистические». Для расчета функции СРЗНАЧ дохода установите курсор в соответствующей ячейке для расчета среднего значения ( В11 ), запустите мастер функций ( Вставка/Функция /категория — Статистические / СРЗНАЧ )

В качестве первого числа выделите группу ячеек с данными для расчета среднего значения — В4:В10.

Аналогично рассчитайте « Среднее значение » расхода.

8. В ячейке D 13 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат»). Для выполнения автосуммы удобно пользоваться кнопкой Автосуммирования на панели инструментов или функцией СУММ . В качестве первого числа выделите группу ячеек с данными для расчета суммы — D 4: D 10

9. Проведите форматирование заголовка таблицы. Для этого выделите интервал ячеек от А1 до D 1 , объедините их кнопкой панели инструментов Объединить и поместить в центре или командой меню Формат/Ячейки/ вкладка Выравнивание/отображение — Объединение ячеек . Задайте начертание шрифта — полужирное; цвет —по вашему усмотрению.

10. Произведите фильтрацию значений дохода, превышающих 4000 р.

В режиме фильтра в таблице видны только те данные, которые удовлетворяют некоторому критерию, при этом остальные строки скрыты

В этом режиме все операции форматирования, копирования, автозаполнения, автосуммирования

и т.д. применяются только к видимым ячейкам листа. Для установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командой Данные/Фильтр/Автофильтр .

В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «Доход»), и вы увидите список всех неповторяющихся значений этого поля. Выберите команду для фильтрации — Условие

В открывшемся окне Пользовательский автофильтр задайте условие « Больше 4000 » Произойдет отбор данных по заданному условию.

Проследите, как изменились вид таблицы и построенная диаграмма.

11. Сохраните созданную электронную книгу в своей папке.

Задание 2. Заполнить таблицу, произвести расчеты, выделить минимальную и максимальную суммы покупки, по результатам расчета построить круговую диаграмму суммы продаж.

Используйте созданный стиль (Формат/Стиль/Шапка таблиц).

Формулы для расчета: Сумма = Цена* Количество ; Всего = сумма значений колонки « Сумма».

. Для выделения максимального/минимального значений установите курсор в ячейке расчета, выберите встроенную функцию МАКС (МИН) из категории «Статистические», в качестве первого числа выделите диапазон ячеек значений столбца «Сумма» (ячейки ЕЗ:Е10 ).

Задание 3. Заполнить ведомость учета брака, произвести расчеты, выделить минимальную, максимальную и среднюю суммы брака, а также средний процент брака; произвести фильтрацию данных по условию процента брака < 8 %, построить график отфильтрованных значений изменения суммы брака по месяцам.

Формула для расчета: Сумма брака = Процент брака *Сумма зарплаты .

В колонке «Процент брака» установите процентный формат чисел (Формат/Ячейки/вкладка Число/формат — Процентный).

Задание 4. Заполнить таблицу анализа продаж, произвести расчеты, выделить минимальную и максимальную продажи (количество и сумму); произвести фильтрацию по цене, превышающей 9000 р., построить гистограмму отфильтрованных значений изменения выручки по видам продукции

Формулы для расчета:

Всего = Безналичные платежи + Наличные платежи; Выручка от продажи = Цена х Всего

Содержание отчета:

Ход и выполнение работы.

Представить результаты работы на компьютере.

Список литературы

1. Михеева Е.В. Информационные технологии в профессиональной деятельности: Учеб. пособие для сред. проф. образования – М.: Издательский центр «Академия», 2005.

2. Михеева Е.В. Практикум по информационным технологиям в профессиональной деятельности: Учеб. пособие для сред. проф.образования – М.: Издательский центр «Академия», 2005.

3. Шафрин Ю.А Информационные технологии: 2ч. Ч1: Офисная технология и информационные системы – М.: БИНОМ. Лаборатория знаний, 2003.

Самый наглядный способ демонстрации динамики изменения данных в таблице - график. Информация в таком виде воспринимается легче табличной. В данной статье мы на примерах разберём несколько простейших способов построения графиков в Excel. С одним и несколькими рядами данных.

Для создания графика в программе MS Excel на основе таблицы с данными воспользуемся разделом Диаграммы на вкладке Вставка ленты инструментов. Разберём подробно инструменты: График и Точечная. Эти виды диаграмм соответствуют привычным математическим графикам.

Диаграмма типа График

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

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

Внесëм необходимые изменения при помощи раздела Работа с диаграммами и двух его вкладок Конструктор и Формат. Он становится доступным при выделенной диаграмме. Также справа от диаграммы появляются три дополнительных инструмента: элементы, стили и фильтры. Они помогают облегчить работу по редактированию графика.

Добавить подписи и легенду можно двумя способами:

с помощью инструмента Экспресс макеты на вкладке Конструктор;
воспользоваться инструментом Элементы диаграммы (значок + в правом верхнем углу), расставив необходимые флажки.

Заполняем поля подписей осей. Щелчком правой кнопки мыши (ПКМ) на поле надписи вызываем контекстное меню и выбираем пункт Изменить текст. Вводим данные с клавиатуры или копируем из соответствующих ячеек таблицы.

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

Последним шагом в оформлении графика будет форматирование осей. Шаг необязательный, но после него оси абсцисс и ординат станут заметнее выделяться на сетке. Это сделает график более наглядным.

Левой кнопкой мыши (ЛКМ) выделяем горизонтальную ось и переходим на вкладку Формат. Инструмент Формат выделенного объекта вызывает диалоговое окно форматирования в правой части программы.

В разделе Заливка и границы изменяем параметры для линии: цвет, ширина, тип. Также, если есть необходимость, прорабатываем параметры текста в том же окне.

Добавление линии на график

Добавим к построенному графику ещё одну функцию. Для этого одним щелчком ЛКМ выделяем график и на вкладке Конструктор выбираем инструмент Выбрать данные. В диалоговом окне в графе Элементы легенды нажимаем кнопку Добавить.

Заполняем диалоговое окно Изменение ряда. В поле Имя ряда вносим ячейку, содержащую название функции. В поле Значения — диапазон значений функции. Дважды нажимаем кнопку ОК.

Диаграмма типа Точечная

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

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

добавить подписи осей и названия диаграммы;
добавить или изменить легенду (название функций);
оформить оси (по необходимости).

Для этого, также как и в случае с графиком, используются раздел Работа с диаграммами и вкладки Конструктор и Формат. А также дополнительные инструменты справа от диаграммы: элементы, стили и фильтры.

Добавляем подписи и легенду с помощью инструмента Экспресс макеты на вкладке Конструктор или воспользовавшись инструментом Элементы диаграммы (значок + справа от диаграммы), расставив необходимые флажки.

Поля подписей заполняем аналогично предыдущему графику: щелчок ПКМ по полю надписи — Изменить текст или двойной щелчок ЛКМ. Данные заполняем с клавиатуры или путём копирования из соответствующих ячеек.

Последним штрихом придаём графику больше наглядности — форматируем оси. Левой кнопкой мыши выделяем горизонтальную ось и переходим на вкладку Формат. Инструмент Формат выделенного объекта вызывает окно форматирования в правой части программы.

В разделе Заливка и границы изменяем параметры для линии тем же способом как при форматировании графика.

Следующим шагом ограничим ось абсцисс. В разделе Параметры оси изменяем максимальную и минимальную границы на 4,5 и - 4,5 соответственно. Уменьшаем шаг с 1 до 0,5 в разделе Единицы измерения для более наглядного отображения данных.

Последним штрихом в работе с графиком функции может быть изменение заливки области диаграммы. Для этого на вкладке Формат выбираем инструмент Заливка фигуры и устанавливаем необходимый цвет заливки.

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

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

Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями

Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст

Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.

Читайте также: