用Excel手把手实现交通规划4阶段法教程

用excel的规划求解解决运输最优解问题
题目：
操作步骤：
1.启动excel（本人用的是金山的）。

2.在表格上按题目填写运费表。

如图：
3.另绘制一个可变量表，也就是待求解的运量表。

如图：
其中实际销量单元格填上公式，如：此单元格为它的纵向前三个单元格相加。

实际产量就为它的横向前四个单元格相加。

因为现在还没进行求解，所以都为0.
4.设立目标函数：=sumproduct(运价区域,运量区域)
5.选择规划求解功能，如图：
如果在“工具”中没有“规划求解”的，就选择“加载宏”，添加“规划求解”。

6.在“设置目标”选中写了目标函数的单元格。

选择“最小值”。

在“通过可更改可变单元格”选中运量区域。

遵守约束条件：每个实际销量=对应的题设销量。

每个实际产量=相应的题设产量。

运量区域≥0。

点击“求解”。

7.结果：。

如何使用Excel的“规划求解”功能进行优化在日常工作和生活中，我们经常会遇到需要优化的问题，比如如何在有限的资源条件下实现最大的效益，或者如何找到满足多个条件的最优方案。

这时候，Excel 的“规划求解”功能就可以派上用场了。

“规划求解”是 Excel 中一个强大的工具，它可以帮助我们通过建立数学模型来找到最优解。

接下来，让我们详细了解一下如何使用这个功能。

首先，确保您的 Excel 中已经加载了“规划求解”功能。

如果没有，可以通过以下步骤进行加载：点击“文件”选项卡，选择“选项”，在弹出的“Excel 选项”对话框中，选择“加载项”，然后在“管理”下拉菜单中选择“Excel 加载项”，点击“转到”按钮，在弹出的“加载宏”对话框中勾选“规划求解加载项”，点击“确定”即可。

在使用“规划求解”之前，我们需要明确问题的目标和约束条件，并将其转化为数学模型。

例如，假设我们有一个生产问题，需要决定生产两种产品 A 和 B 的数量，已知产品 A 的单位利润为 10 元，产品 B 的单位利润为 15 元，我们拥有的原材料限制为 100 单位，生产产品 A 每单位需要消耗 2 单位原材料，生产产品 B 每单位需要消耗 3 单位原材料。

我们的目标是最大化总利润。

接下来，我们在 Excel 中建立表格来表示这个问题。

在第一列中输入产品名称（A 和 B），第二列输入生产数量（假设初始值为 10），第三列输入单位利润（分别为 10 和 15），第四列计算每种产品的利润（数量乘以单位利润），第五列输入每种产品消耗的原材料数量（分别为 2 和 3），第六列计算总的原材料消耗（数量乘以消耗的原材料数量）。

然后，我们设置目标单元格。

在这个例子中，目标是最大化总利润，所以我们选择计算总利润的单元格作为目标单元格。

接下来，设置变量单元格，即生产数量所在的单元格。

再然后，添加约束条件。

在这个例子中，约束条件是总的原材料消耗不能超过 100 单位，所以我们添加这个约束条件。

excel规划求解的使用教程详细图文步骤
规划求解使用步骤2：创建表格，如下。

单击“数据“工具栏，选择”规划求解“，随即弹出【规划求解参数】对话框，在【设置目标单元格】中输入“$B$12”;在【可变单元格】中输入“$C$3：$C$5”，单击“添加”按钮，弹出【添加约束】对话框，在【单元格引用位置】输入“$B$10”，在其右侧的下拉列表中选择【<=】，在【约束值】中输入“$B$7”。

规划求解使用步骤3：单击“添加”按钮，继续添加约束条件。

使用相同方式，再添加4个约束条件。

规划求解使用步骤4：约束条件添加完毕，单击“确定”按钮，返回【规划求解参数】对话框，此时可发现在【约束】列表中显示出了添加的所有约束条件，然后单击“选项”按钮。

随即弹出【规划求解选项】对话框，选中“采用线性模型”和“假定非负”，其余保持默认设定，单击“确定”。

返回【规划求解参数】对话框，单击“求解”按钮。

excel2007进行规划求解的操作
Excel的基础统计分析功能主要体现在众多的统计函数中，这些函数的熟练应用有助于方便我们直接求得数据的统计量，如数学期望和方差等。

当然Excel还包涵了更复杂的数据分析功能都在Excel的【规划求解】模块当中，今天，店铺就教大家如何进行规划求解的方法!
Excel2007规划求解的步骤如下：
打开Excel2007工作文件——>【鼠标左键】单击【Microsoft office】按钮——>【鼠标左键】单击【Excel 选项】按钮;
然后在弹出的【Excel选项】下——>【鼠标左键】单击【加载项】——>在【管理：】右方选择【Excel 加载项】选项卡——>【鼠标左键】单击【转到】按钮;
再在弹出的【加载宏】方框下——>【鼠标左键】勾选【规划求解加载项】——>【鼠标左键】单击【确定】按钮;
4最后在Excel工作文档中【鼠标左键】单击【数据】选项卡即可看到位于该选项卡下右方的【数据分析】模块，【鼠标左键】单击【规划求解】即可看到【规划求解】模块。

注意事项：
第2步必须是在【Excel 加载项】选项卡下【鼠标左键】单击【转到】按钮!。

本文整理于网络，仅供阅读参考
Excel规划求解功能的使用教程
excel规划求解功能的使用教程：
规划求解使用步骤1：安装规划求解：规划求解是excel的一个插件，需要安装。

打开新建文档左上角office按钮——excel 选项——自定义——从下列位置选择命令(所有命令)——加载宏——添加——确定。

点击“加载宏”工具，弹出【加载宏】对话框，勾选“分析工具库“和”规划求解加载项“，点击”确定“。

随即弹出microsoft office excel对话框，点击”是“。

开始安装。

规划求解使用步骤2：创建表格，如下。

单击“数据“工具栏，选择”规划求解“，随即弹出【规划求解参数】对话框，在【设置目标单元格】中输入“$b$12”;在【可变单元格】中输入“$c$3：$c$5”，单击“添加”按钮，弹出【添加约束】对话框，在【单元格引用位置】输入“$b$10”，在其右侧的下拉列表中选择【看了excel规划求解功能的使用教程。

数学与信息科学学院Excel求解线性规划问题实验教程二零一三零八月目录1.关于“规划求解” (1)2.如何加载“规划求解” (2)3.“规划求解”各参数解释和设置 (3)4.“规划求解”的步骤 (6)5.Excel求解线性规划问题 (8)6.Excel求解运输问题 (14)7.Excel求解目标规划问题 (18)8.Excel求解整数规划问题 (22)1.关于“规划求解”“规划求解”是Excel中的一个加载宏，借助“规划求解”，可求得工作表上某个单元格（被称为目标单元格）中公式（公式:单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。

公式总是以等号（=）开始）的最优值。

“规划求解”将对直接或间接目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。

“规划求解”通过调整所指定的可更改的单元格（可变单元格）中的值，从目标单元格公式中求得所需的结果。

在创建模型过程中，可以对“规划求解”中的可变单元格数值应用约束条件（约束条件：“规划求解”中设置的限制条件。

可以将约束条件应用于可变单元格、目标单元格或其它与目标单元格直接或间接相关的单元格。

而且约束条件可以引用其它影响目标单元格公式的单元格。

使用“规划求解”可通过更改其它单元格来确定某个单元格的最大值或最小值。

）Microsoft Excel的“规划求解”工具取自德克萨斯大学奥斯汀分校的Leon Lasdon 和克里夫兰州立大学的Allan Waren共同开发的Generalized Reduced Gradient(GRG2)非线性最优化代码。

线性和整数规划问题取自Frontline Systems公司的John Watson 和Dan F ylstra提供的有界变量单纯形法和分支边界法。

2.如何加载“规划求解”安装office的时候，系统默认的安装方式不会安装宏程序，需要用户根据自己的需求选择安装。

如何利用Excel进行战略规划和决策支持在当今竞争激烈的商业环境中，有效的战略规划和明智的决策是企业取得成功的关键。

而 Excel 作为一款功能强大的电子表格软件，不仅可以用于简单的数据处理和分析，还能在战略规划和决策支持方面发挥重要作用。

下面将详细介绍如何利用 Excel 进行战略规划和决策支持。

一、明确战略规划和决策支持的目标在使用 Excel 之前，首先要明确战略规划和决策支持的具体目标。

这可能包括评估市场趋势、预测销售业绩、分析成本结构、评估投资项目的可行性等。

明确的目标将有助于确定所需的数据和分析方法，从而更有效地利用 Excel 的功能。

二、数据收集与整理1、内部数据从企业内部的各个部门收集相关数据，如财务报表、销售记录、生产数据、人力资源信息等。

这些数据通常可以在企业的数据库、文件系统或专门的管理系统中获取。

2、外部数据通过市场调研、行业报告、政府统计数据等渠道收集外部数据，以了解市场动态、竞争对手情况、宏观经济环境等。

在收集到数据后，需要对其进行整理和清洗，确保数据的准确性、完整性和一致性。

例如，去除重复数据、纠正错误数据、统一数据格式等。

三、构建数据模型1、制定数据结构根据分析的目标和数据的特点，在 Excel 中设计合适的数据结构。

可以使用工作表来分别存储不同类型的数据，并通过命名和标注来清晰地说明每个工作表和单元格的含义。

2、建立计算公式利用 Excel 的函数和公式功能，对数据进行计算和转换。

例如，可以使用SUM、AVERAGE、VLOOKUP 等函数进行求和、平均值计算、数据查找和引用等操作。

3、运用数据分析工具Excel 提供了丰富的数据分析工具，如数据透视表、图表等。

数据透视表可以快速汇总和分析大量数据，帮助发现数据中的模式和趋势。

图表则可以直观地展示数据，如柱状图、折线图、饼图等，使数据更易于理解和解读。

四、进行情景分析和预测1、敏感性分析通过改变关键变量的值，观察其对结果的影响程度，从而确定哪些因素对决策最为敏感。

用Excel 软件求解规划的方法Microsoft Excel 软件是当今十分流行的功能强大操作方便的软件。

在Microsoft Excel 软件中，具有规划求解功能。

如图1，在工具菜单下，一般有“规划求解”项，若未有，则应先运行“加载宏”项目把其安装上。

图1 图21 一般线性规划的求解现在让我们以下面的模型为例，介绍如何利用Microsoft Excel 软件求解线性规划模型的操作方法。

首先，打开Microsoft Excel 的一个工作簿，把模型的约束系数矩阵置于A1至B4范围，约束常数置于D1至D4范围，而利润系数则置于A5至B5范围。

选择A7至B7范围作可变单元（即这两个格相当于变量X1与X2），并输入初值０。

然后，在单元格C1处输入“=A1*A7+B1*B7”，即第一个约束不等式的左边；同理，在单元格C2处输入“=A2*A7+B2*B7”，即第二个约束不等式的左边；对C3与C4也同样处理。

最后，以单元格C5作目标单元格，输入“=A5*A7+B5*B7”。

如图2。

接下来，按下主菜单的工具处，再在下拉菜单处选择“规划求解”，则弹出窗口如图3。

⎪⎪⎪⎩⎪⎪⎪⎨⎧≥≥≤+≤≤≤0x 0,x x x x x x 4+x s.t. x +x =f max 21112121222700050122700075.182700025.56270000155.75.2图3 图4在“设置目标单元格”处输入“C5”,然后选“最大值”，再在“可变单元格”处输入“A7:B7”,在“约束”处按一下“添加”按钮，又弹出如图4的窗口。

在此，我们要添加5个约束：“C1 <= D1”、“C2 <= D2”、“C3 <= D3”、“C4 <= D4”、“A7：B7 >= 0”。

对第一个约束，在“单元格引用位置”处输入“C1”,在中间下拉框选择“<=”, 再在“约束值”处输入“D1”。

然后按“添加”按钮，再类似地添加其它约束。

利用Excel 进行规划求解Excel 具有规划求解的基本功能，包括线性规划和非线性规划。

对于常规的线性规划问题，Excel 就可以给出求解结果。

对于比较复杂的问题，那就需要用到较难掌握的数学软件如Matlab 了。

不过，大多数规划问题Mathcad 即可完成所赋予的任务。

利用Excel 求解规划问题有些“罗嗦”，但也不难掌握。

下面以几个简单的实例说明其应用方法，希望各位能够举一反三，将其推广到多变量的情形。

【例1】设有一位个体户制杯者，有两副模具，分别用来生产果汁杯和鸡尾酒杯。

有关生产情况的各种数据资料见下表。

3 果汁杯6 h/百件 10 m 3/百件 600件 600元/百件 鸡尾酒杯 5 h/百件 20 m 3/百件 0件 400元/百件 *注：定点量为每周生产的最大数量。

若每周工作不超过50小时，且拥有储藏量为140m3的仓库。

问：⑴ 该个体户如何安排工作时间才能使得每周的收益最大？⑵ 若每周多干1小时，收益增大多少？⑶ 通过加班加点达到的收益极限是多少？解：这个例子取自一本面向中学生的知识读物，是一个最大收益问题，可以建立模型如下：21400600)(Max x x x f +=⎪⎪⎩⎪⎪⎨⎧≥≥≤≤+≤+0,0614020105056 s.t.2112121x x x x x x x 显然，约束条件中的第三个式子x 1≤6可以表作1*x 1+0*x 2≤6，从而有如下矩阵⎥⎦⎤⎢⎣⎡=400600c ，⎥⎦⎤⎢⎣⎡=21x x x ，⎥⎥⎥⎦⎤⎢⎢⎢⎣⎡=01201056A ，⎥⎥⎥⎦⎤⎢⎢⎢⎣⎡=614050b 容易看到，上述模型表为矩阵形式便是：目标函数为[]⎥⎦⎤⎢⎣⎡==21400600)(Max x x x c x f T 约束条件为⎪⎪⎪⎩⎪⎪⎪⎨⎧≥⎥⎦⎤⎢⎣⎡=⎥⎥⎥⎦⎤⎢⎢⎢⎣⎡=≤⎥⎥⎥⎦⎤⎢⎢⎢⎣⎡=061405001201056 s.t.21x x x b Ax下面是利用Excel 求解规划结果的详细步骤：第一步，录入数据，定义有关单元格在Excel 中，将有关数据资料按一定的规范录入，最好按照资料表格录入。

§9.6 Excel软件“规划求解”的使用用Excel软件的“规划求解”功能可以方便地求解线性规划、整数规划和非线性规划问题。

但如果安装Office 97时采用的是典型安装方法，则【工具】菜单中是无“规划求解”功能项的。

可参照§2.8中介绍的方法将未安装的组件安装完整。

下面以第八章例8.1为例介绍用Excel求解线性规划的操作步骤和运行输出结果的分析。

一．求解线性规划的操作过程1．输入数据、公式和说明文字（1）在工作表中按图9.7所示格式输入必要的说明文字（图中粗体字部分）和LP模型的原始数据（图中虚线框所示单元格内，注意并不需要化为标准型）；图中F4是放置目标函数的单元格，B5:D5是放置决策变量X1、X2、X3（既“可变单元格”）的区域。

图9.7（2）在F4单元格内输入目标函数X0的计算公式：=B4*B5+C4*C5+D4*D5或=SUMPRODUCT（B4:D4,B5:D5）其中SUMPRODUCT（）函数返回两个或多个区域（即数组）中对应单元格乘积之和的值。

该函数可在Excel的“数学和三角函数”中找到。

（1）在E8单元格中输入第一个约束条件左端的计算公式：=B8*$B$5+ C8*$C$5+D8*$D$5或= SUMPRODUCT（B8:D8,$B$5:$D$5）然后拖曳E8的填充柄将公式复制到E9、E10单元格（注意公式中的B5、C5、D5或B5:D5要使用绝对引用）。

当模型中的变量数较多时，使用SUMPRODUCT（）函数可大大加快以上两个公式的输入速度。

说明：图中粗线框是表示要输入公式的单元格。

用Excel求解线性规划的数据输入格式可由用户自行设计，但以上介绍的格式不仅与我们所熟悉的LP模型相似，便于理解和使用；而且便于在对话框中输入约束条件。

按以上格式输入说明文字后，还可以使系统所输出的三个运行结果报告更具可读性。

2．选【工具】→“规划求解”，“打开规划求解参数”对话框，见图9.8。

一、transcad通规划四阶段法具体操作1.1生成初始路网图1.1.1 生成路网前准备❖打开背景图片（Open：*.tif ）❖设置单位为：MetersEdit—preferences—system—map units：meters1.1.2 新建路网层（线层）❖New－Geographic File❖选择Line Geographic File，层名：street，端点层名：node❖域字段：speed、capacity（4bytes）time（8bytes）❖画路网：Tools－Map Editing－Toolbox❖画完路网检查路网的连通性：Tools－Map Editing－check line layer connectivity1.2 生成小区图❖新建交通区层（面层）❖New－Geographic File,选择Area Geographic File，options选择第二项，输入层名:area ❖添加域字段：交通区编码ID，现状base，现状吸引量A base，现状交通区容量POP base，未来发生量P fur，未来吸引量A fur，未来交通区容量POP fur❖画交通区：Tools－Map Editing－Toolbox1.3 在路网与交通区层填入数据并进行人口预测❖根据调查所得的数据填入路网与交通区数据表中❖进行交通区未来人口预测(1)、打开交通区数据表(2)、操作过程❖点击statistics—Model Esticmation，在dependent选项里面选择P base，independent 里面选POP base—add—POP base—OK—保存（文件名为P fur）；❖同样在dependent选项里面选择A base，independent里面选POP base—add—POP base—OK—保存(文件名为A fur)；❖点击数据表中P fur下任意一格，选statistics—Model Evaluation—打开P fur文件—将数据导入表中（A fur导入方式相同）1.4 生成小区质心❖建立交通区形心与路网的连接(1)在“ node层”添加字段index，操作方法：dataview－modify table—add field—命名为index(2)输出质心：选择面层“area”，tool-export选择All features，standard geographic file，ID，include built data，export as centroid points(3)选择面层“area”，使用tool-mapediting-connect，选择node field: index. fill with 选择Ids from area layer❖更新路网的路段属性信息❖添加路段属性数据：使用Fill…❖Capacity=100000，speed=3，time=Length/speed1.5 创建网络(1)选出质心：在点层上-Selection-selection by condition…-填入index<>null-ok（2）建立网络❖在路网层上，点击Networks/Paths→Create调出创建网络对话框将Optional Fields里的容全选，连接后的路网将继承这些属性点OK，出现保存对话框起好名字，点击Save。

交通规划课程设计.现状路网构造1.交叉口和路段标号的路网图2.邻接矩阵1 2 3 4 5 6 7 8 9 101(0 1 0 0 0 1 0 0 0 02 1 0 1 0 1 0 0 0 0 03 0 1 0 1 0 0 0 0 0 04 0 0 1 0 0 0 0 0 1 15 0 1 0 0 0 1 0 1 1 06 1 0 0 0 1 0 1 0 1 07 0 0 0 0 0 1 0 1 0 08 0 0 0 0 1 0 1 0 0 19 0 0 0 1 1 0 0 0 0 1 10( 0 0 0 1 0 0 0 1 1 0 J 3.道路网的拓扑关系及属性数据表二.回归法预测规划区人口数据利用已给的2005年-2014年的人口数据可在Excel 中对各小区未来年的人口 作出预测，并得出预测函数。

如下面的五个图分别为五个小区的预测图。

各小区人口线形回归图： 小区一:小区♦小区1 ——线性「卜区小区21200♦小区2——线性「卜区2)小区三：小区3♦小区3——线性「卜区第小区四:小区4小区五:小区5三.发生与吸引交通量预测各区现在的出行发生量与吸引量♦小区4 ——线性「卜区♦小区s ——线性「卜区根据上表中数据易得现状出行生成量T=11345；现状常住人口 N=5311；将来 常住人口 M=6997；人均单位出行次数T/N=11345/5311=2.136(次/d) 将来的生成交通量 X=M*(T/N)=6997*2.136=14945.5各小区的内内交通量都认为是0,假设各小区的发生与吸引原单位不变。

求现状发生与吸引原单位：小区一的发生原单位：1581/1029=1.536 小区二的发生原单位：1978/1076=1.838 小区三的发生原单位：2232/1042=2.142 小区四的发生原单位：1906/1098=1.736 小区五的发生原单位：3648/1066=3.422 同理，将来的生成交通量X=M*(T/N)各小区的发生与吸引交通量计算结果如 下表所示：由上面的结果可知，各小区发生交通量之和不等于其吸引交通量之和，所以， 需要进行调整计算。

.《交通规划》课程设计———四阶段法交通需求预测姓名：专业：班级：学号：指导教师：本论文主要是以福州市某轨道交通的道路网络来举例论证，如图4-1所示。

其中1、3、7、9四个交通节点分别作为四个交通区A 、B 、C 、D 的作用点，交通节点之间边线上的数据是路段的行驶时间。

该道路网络的每条道路上都设有中央分隔带以及机动车和非机动车分隔带的双向两车道道路。

图4-1 福州某轨道交通的道路网络 单位：min4.1交通生成预测根据每个家庭的月收入的不同来统计出行率，经过调查见表4-1： 表4-1出行率表收入分类（元/月） 0～800 800～1600 1600～2400 2400以上 出行率（人次/天）2.93.23.43.7小区现状家庭数和目标家庭数以及不同家庭的月收入比例如表4-2所示： 表4-2 小区家庭数小 区 A B C D 现 状 家 庭 数 8700 9200 8400 9600 目标年家庭数9800 11300 9600 11800 比例0-800 0.04 0.02 0.12 0.03 800-16000.150.260.130.241600-2400 0.25 0.37 0.28 0.33 2400以上0.650.420.560.46现状及目标年各小区家庭数以及不同收入家庭的比例。

123 45678 988448888 7.27.288DA B C使用交叉分类法，计算现状及目标年各小区的交通生成量。

现状交通生成量：交通小区A：8700×0.04×2.9+8700×0.15×3.2+8700×0.25×3.4+8700×0.65×3.7=33504交通小区B：9200×0.02×2.9+9200×0.26×3.2+9200×0.37×3.4+9200×0.42×3.7=34058交通小区C：8400×0.12×2.9+8400×0.13×3.2+8400×0.28×3.4+8400×0.56×3.7=28842交通小区D：9600×0.03×2.9+9600×0.24×3.2+9600×0.33×3.4+9600×0.46×3.7=25772目标年交通生成量：交通小区A：9800×0.04×2.9+9800×0.15×3.2+9800×0.25×3.4+9800×0.65×3.7=37740交通小区B：11300×0.02×2.9+11300×0.26×3.2+11300×0.37×3.4+11300×0.42×3.7=41833 交通小区C：9600×0.12×2.9+9600×0.13×3.2+9600×0.28×3.4+9600×0.56×3.7=28842交通小区D：11800×0.03×2.9+11800×0.24×3.2+11800×0.33×3.4+11800×0.46×3.7=257722.交通分布预测已知现状交通分布的OD矩阵为：（单位：人次/天）A B C DA 0 6180 6180 15450B 6180 0 15450 3090C 6180 15450 0 7725D 15450 3090 7725 0 使用增长系数法计算目标年的OD矩阵。