德尔菲法Excel上机指导
- 格式:doc
- 大小:240.00 KB
- 文档页数:10
Excel上机操作题一、1.新建一个工作簿,以文件名“销售报告”保存进行以下操作:(1)在工作表上输入如图所示的数据,并将工作表命名为“总表”:(2)利用公式计算各种项目需要三个工作站时的价格注:“三个工作站”所需的价格为“两个工作站”与“每增一个站”的价格之和(3)利用函数计算各种情况下所有项目的“总计”价格二、新建一个如图所示工作表中进行以下格式设置:(1)利用格式工具栏按钮为数字应用“货币样式”和“千位分隔样式”(2)调整行高与列宽,使它们以“最合适的行高”与“最合适的列宽”显示(3)为表格设置网格边框线,其外边框线为粗实线、内边框线为细实线(4)为表格行标题和列标题添加黄色底纹(5)用公式计算出表中“实发工资”的数目。
(6)用条件格式标记水电费超过90的条目。
三、在工作表中输入如图所示数据:(1)将标题“财务管理软件价格”的格式设置为:20磅、黑体、合并及居中(2)将表格行标题和列标题的格式设置为:楷体、加粗、居中对齐、蓝色利用函数功能,计算工作表中各项目单用户的“销售金额”注:“销售金额”三地销售总量的乘积四、在工作表中输入如图所示数据:(1)设置纸张大小为B5,方向为纵向,页边距为2cm;(2)将“基本工资”和“水电费”的数据设置为保留一位小数;(3)设置标题的字号为18,字体为黑体,颜色为深绿,对齐选合并单元格,垂直、水平均为居中;(4)在表中增加“补贴”、“应发工资”和“科室”三列;(3)用函数统计基本工资、水电费、补贴和应发工资的合计与平均;五、新建工作表如图所示:(1)在表中增加“补贴”、“应发工资”和“科室”三列;(2)用函数和公式计算每个职工的补贴、应发工资和实发工资;(3)按基本工资进行排序,要求低工资在前;(4)分别计算男、女职工的平均基本工资;(5)显示水电费超过70元的男职工记录;六、设工作表中数据如图所示,对工作表做如下操作:(1)分别在单元格F2和G2中填写计算总分和平均分的公式,用公式复制的方法分别求出各学生的总分和平均分;(3)筛选出姓王,并且性别为女的同学;(4)筛选出平均分在75分以上,或性别为女的同学;七、新建如下工作表(1)将“基本工资”和“水电费”的数据设置为保留一位小数;(2)设置标题的字号为18,字体为黑体,颜色为深绿,对齐选合并单元格,垂直、水平均为居中;(3)在表中增加“补贴”、“应发工资”和“科室”三列;(4)用函数计算每个职工的补贴,计算方法:助讲的补贴为50元,讲师的补贴为70元,副教授的补贴为90元,教授的补贴为110元;。
层次分析法确定评价指标权重的Excel实现展开全文层次分析法(Analytic Hierarchy Process,AHP)由美国运筹学家、匹兹堡大学教授T.L.Satty提出,是一种将与决策有关的元素分解成目标、准则、方案等层次,在此基础上进行定性和定量分析的决策方法。
在医疗质控工作中,常和德尔菲法、百分权重法结合,用于确定评价指标的权重。
本节内容简述应用层次分析法确定评价指标权重的基本原理和Excel实现。
基本原理1.构造判断矩阵由专家对同一层次内n个指标的相对重要性(两两因素之间)进行打分。
相对重要性的比例标度取1-9之间:构建判断矩阵A(正交矩阵),用aij表示第i个因素相对于第j个因素的比较结果:2. 计算权重将矩阵A的各行向量进行几何平均(方根法),然后进行归一化,即得到各评价指标权重和特征向量W:3.一致性检验计算最大特征根λmax:计算一致性指标CI(Consistency Index)、随机一致性指标RI (Random Index)和一致性比例CR(Consistency Ratio):一般情况下,当CR<0.1时,即认为矩阵具有满意的一致性,否则需要对判断矩阵进行调整。
Excel操作步骤现某课题构建患者安全评价指标体系,将一级指标拟定为3项:“结构安全”、“过程安全”、“结果安全”,通过专家咨询得到的判断矩阵如下:设计层次分析法计算过程的Excel表格如下:各列键入公式:按行相乘:F3=PRODUCT(C3:E3),下拉至F5。
开n次方:G3=POWER(F3,1/3),下拉至G5;G6=SUM(G3:G5)。
权重wi:H3=G3/G$6,下拉至H5。
AWi:I3=C3*H$3+D3*H$4+E3*H$5,下拉至I5。
AWi/wi:J3=I3/H3,下拉至J5;J6=AVERAGE(J3:J5)。
CI:K3=(J6-3)/2。
CR:L3=K3/0.5149得到结果:可见CR=0.037<0.01,认为矩阵具有满意的一致性。
excel上机操作试题及答案Excel上机操作试题及答案试题一:基本操作1. 打开Excel,创建一个新的工作簿。
2. 在A1单元格输入文字“姓名”,在B1单元格输入文字“成绩”。
3. 在A2单元格输入文字“张三”,在B2单元格输入数字85。
4. 在A3单元格输入文字“李四”,在B3单元格输入数字90。
5. 将A1:B3的单元格数据合并为一个单元格。
6. 将工作表的列宽设置为20。
7. 将A1:B3的单元格背景颜色设置为黄色。
8. 保存工作簿为“学生成绩表.xlsx”。
答案一:1. 打开Excel应用程序,点击“新建”按钮创建一个新的工作簿。
2. 在A1单元格输入“姓名”,在B1单元格输入“成绩”。
3. 在A2单元格输入“张三”,在B2单元格输入85。
4. 在A3单元格输入“李四”,在B3单元格输入90。
5. 选中A1:B3单元格,点击“合并和居中”按钮。
6. 选中A列和B列,右键点击选择“列宽”,输入20。
7. 选中A1:B3单元格,点击“开始”选项卡下的“填充颜色”按钮,选择黄色。
8. 点击“文件”选项卡,选择“另存为”,输入文件名“学生成绩表.xl sx”,选择保存位置后点击“保存”。
试题二:公式与函数1. 在C1单元格输入文字“平均分”。
2. 在C2单元格使用AVERAGE函数计算B2:B3的平均分。
3. 在C3单元格使用MAX函数找出B2:B3的最大值。
4. 在C4单元格使用MIN函数找出B2:B3的最小值。
5. 在C5单元格使用SUM函数计算B2:B3的总和。
6. 将C1:C5的单元格数据合并为一个单元格。
7. 将C1:C5的单元格背景颜色设置为绿色。
8. 保存工作簿。
答案二:1. 在C1单元格输入“平均分”。
2. 在C2单元格输入公式`=AVERAGE(B2:B3)`。
3. 在C3单元格输入公式`=MAX(B2:B3)`。
4. 在C4单元格输入公式`=MIN(B2:B3)`。
5. 在C5单元格输入公式`=SUM(B2:B3)`。
1.1 德尔菲法实验1.1.1 理论知识准备德尔菲(Delphi )是在专家个人判断法的基础上发展起来的一种新型直观的预测方法。
目前,德尔菲法已经运用在规划和决策中,并具有较高的声望,是一种重要的规划决策工具。
德尔菲法与专家会议法相比有三个特点,即匿名性、反馈性和预测结果的统计特性。
匿名性就是采用匿名函询的方式征求意见,以就消除对专家判断的客观性有影响的一些不良因素。
反馈性就是要进行几轮专家意见征询,并把上一轮的结果反馈到下一轮的预测中去,以便专家们可以参考有价值的意见从而提出更好的意见。
预测结果的统计特性是指德尔菲法采用统计方法对专家意见进行处理,从而得到定量的表达,使得专家意见逐渐趋于一致。
德尔菲法预测步骤如下:(1) 确定预测主题,归纳预测事件预测主题就是所要研究和解决的问题。
一个主题包括若干个事件。
事件是用来说明主题的重要指标。
确定预测主题和归纳预测事件是德尔菲法的关键一步。
(2) 选择专家德尔菲法要求专家对预测主题相当了解,对预测问题的研究非常深入,所选择专家来源广泛,一般是本企业、本部门的专家和有业务联系、关系密切的外部专家以及在社会上有影响的知名人士。
专家人数恰当,通常视预测主题规模而定。
专家人数太少,缺乏代表性,太多又难于组织。
一般情况下,专家小组人数以10~50人为宜。
对重大问题的预测,专家小组的人数可扩大到l00人左右。
(3) 预测过程经典德尔菲法的预测过程一般分为四轮。
第一轮确定预测事件,要求各个专家根据所要预测的主题提出预测事件,并用准确的术语列出“预测事件一览表”。
第二轮初次预测,将“预测事件一览表”发给各个专家,要求他们对各个事件做出评价,提出相应的预测,并附上理由。
有必要还可以提出需要的补充资料,使预测更加准确。
第三轮修改预测,专家根据预测领导小组所反馈的第二轮预测结果和补充资料,再一次进行预测,并像第二轮预测一样附上理由。
第四轮最后预测,专家再次根据反馈结果做出最后的预测,并根据领导小组的要求,做出或不做出新的论证。
Excel 上机考试指导说明一、特别说明1、Excel 电子表格占上机考试总成绩的40%,分量较重。
2、Excel操作是上机考试的第二部分,但难度较大,题量较多。
所以建议大家打完字后先进行windows 操作,最后完成Excel部分。
3、考试的范围不会超过过关训练,并且很可能类似过关训练中题目,希望大家熟练操作过关训练中。
二、考点提示1、表格的修饰美化:选定表格——鼠标右键——设置单元格格式难点提示:合并居中对齐边框线设置底纹颜色2、函数的使用:要求掌握sum求和及Average 平均数两种函数3、难点提示:求总和函数求平均数函数公式的使用:选定单元格——输入等号——输入公式难点提示:公式的正确书写4、图表的使用:要求掌握柱形图、折线图和饼图方法:按题目要求选定图表区域——插入——图表——按要求选择图表难点提示:图表绘制方法5、排序:降序和升序方法:选定整个表格——数据菜单——排序——6、分类汇总:选定表格中的任一单元格——数据——分类汇总——难点提示:分类汇总方法7、自动筛选:选定表格中的任一单元格——数据——筛选——自动筛选——每一字段名下有小三角按钮——点击选择难点提示:自动筛选三、过关训练1、样题一:打开指定文件夹中的EX5.xls文件,即技能竞赛成绩表题目一:输入各班获得奖牌数目,将“初一级技能竞赛成绩表”的颜色设置为深红,字体为隶书。
题目二:用SUM函数统计名班获得的奖牌数目。
统计团体总分。
计算公式:团体总分=金牌*4+银牌*2+铜牌。
题目三:将A2到F6的外边框线设置为双实线,内边框线设置为单实线,颜色为黑色。
题目四:利用班别、金银铜奖牌数目(A2:D6)画出三维折线图。
2、样题二打开指定文件夹中的EX8.xls文件,进行下列操作:题目一:统计各人总分,统计各科平均分。
题目二:按性别进行分类汇总,统计不同性别的语、数、英平均分。
题目三:按数学成绩从低到高分排序。
题目四:利用EXCEL的自动筛选,筛选出所有语文成绩大于80,数学成绩大于等于80的所有姓陈的学生。
1.1 德尔菲法实验1.1.1 理论知识准备德尔菲(Delphi )是在专家个人判断法的基础上发展起来的一种新型直观的预测方法。
目前,德尔菲法已经运用在规划和决策中,并具有较高的声望,是一种重要的规划决策工具。
德尔菲法与专家会议法相比有三个特点,即匿名性、反馈性和预测结果的统计特性。
匿名性就是采用匿名函询的方式征求意见,以就消除对专家判断的客观性有影响的一些不良因素。
反馈性就是要进行几轮专家意见征询,并把上一轮的结果反馈到下一轮的预测中去,以便专家们可以参考有价值的意见从而提出更好的意见。
预测结果的统计特性是指德尔菲法采用统计方法对专家意见进行处理,从而得到定量的表达,使得专家意见逐渐趋于一致。
德尔菲法预测步骤如下:(1) 确定预测主题,归纳预测事件预测主题就是所要研究和解决的问题。
一个主题包括若干个事件。
事件是用来说明主题的重要指标。
确定预测主题和归纳预测事件是德尔菲法的关键一步。
(2) 选择专家德尔菲法要求专家对预测主题相当了解,对预测问题的研究非常深入,所选择专家来源广泛,一般是本企业、本部门的专家和有业务联系、关系密切的外部专家以及在社会上有影响的知名人士。
专家人数恰当,通常视预测主题规模而定。
专家人数太少,缺乏代表性,太多又难于组织。
一般情况下,专家小组人数以10~50人为宜。
对重大问题的预测,专家小组的人数可扩大到l00人左右。
(3) 预测过程经典德尔菲法的预测过程一般分为四轮。
第一轮确定预测事件,要求各个专家根据所要预测的主题提出预测事件,并用准确的术语列出“预测事件一览表”。
第二轮初次预测,将“预测事件一览表”发给各个专家,要求他们对各个事件做出评价,提出相应的预测,并附上理由。
有必要还可以提出需要的补充资料,使预测更加准确。
第三轮修改预测,专家根据预测领导小组所反馈的第二轮预测结果和补充资料,再一次进行预测,并像第二轮预测一样附上理由。
第四轮最后预测,专家再次根据反馈结果做出最后的预测,并根据领导小组的要求,做出或不做出新的论证。
在实际运用中,预测的轮数依照实际情况而定,如果大多数专家不再修改自己的意见,这表明专家们的意见基本趋于一致,这种情况下才能结束预测。
(4) 确定预测值,做出预测结论最后对专家应答结果进行量化分析和处理,这是德尔菲法最重要的阶段,常采用中位数法,即上、下四分点之间的距离越小,说明专家们的意见越集中,用中位数代表的预测结果的可信程度越高。
首先,把专家们的意见(即对某个问题的不同方案所给出的得分)按从小到大的顺序排列。
若有n 个专家,n 个(包括重复的)答数排列如下:n x x x ≤⋯≤≤21,若中位数及上、下四分点分别用下上中,,x x x 表示,则⎩⎨⎧=++=++k n x x k n x x k kk 2,2/)(12,11=中⎪⎪⎩⎪⎪⎨⎧=+=+=++=+++++为偶数为奇数为偶数为奇数,)(=上k k n x x k k n x k k n x x k k n x x k k k k k k ,2,2/,2,,12,2/)(,12,2/312/32/)13(2/322/312/)1(3 ⎩⎨⎧=+=+=+=++为偶数或为奇数或=下k k n k n x x k k n k n x x k k k ,,212,2/)(,212,2/12/2/)1( 通过德尔菲法上述步骤,理论上可认为预测结果围绕中位数和上下四分位点形成的信区间分布,即预测结果在一定程度上可能是中位数和上下四分位点形成的信区间中的某一个点。
置信区间越窄,即上、下四分点间距越小,说明专家们的意见越集中,用中位数代表预测结果的可信程度越高。
最后,计算专家意见协调程度。
用变异系数表示j jj M V σ=,分子为方案所得分的标准差,分母为方案所得分的均值。
变异系数值越小,专家协调程度越高。
这可在一定程度上说明专家对预测结果的认可程度。
1.1.2 上机实验目的(1) 了解Excel 具有设计与实现德尔菲法的功能,熟悉和掌握德尔菲法基本步骤及原理。
(2) 掌握运用Excel 的公式工具设计实现德尔菲法。
(3) 掌握运用Excel 的公式函数SUM 、STDEV 。
(4) 复习Excel 的描述统计,并掌握Excel 的数据统计相关函数:COUNT (返回一组数的个数)、QUARTILE (返回一组数的四分位点)。
1.1.3 上机实验准备(1) 通过Excel 的“帮助”菜单查找关于SUM 、STDEV 、COUNT (返回一组数的个数)、QUARTILE (返回一组数的四分位点)的使用说明。
(2) 复习通过Excel “工具”菜单中的“数据分析”打开描述统计,如错误!未找到引用源。
所示。
图错误!文档中没有指定样式的文字。
-1描述统计的参数输入窗口1.1.4上机实验内容设有如下案例:公司前5年货运总量情况如错误!未找到引用源。
表 2-1公司前5年货运总量问题1. 您认为明年的货运量将是:问题2. 您认为5年后的货运量将是:问题3. 您认为10年后的货运量将是:下列5方面因素将影响今后的运量:下列5方面因素将影响今后的运量:货源、国家经济政策、运输能力与港口能力、航道条件和气候、交通运输体制和经营管理思想。
请您根据他们对运量影响程度的大小,按由强到弱次序排列。
(1)根据案例预测目标设计调查表,确定参与预测专家人数和人选,主持者向参与预测人员介绍相关情况和调查规则;(2)组织者通过与参与专家反复协调,交流其他参与者意见,统计出集中调查结果,如下错误!未找到引用源。
图 2-2专家意见汇总(3) 运用Excel 对问题1的数据情况进行分析,找出该问题的对应数据的中位数以及上四分位点和下四分位点,得到未来的预测空间。
(1.1.5 上机实验步骤下面用Excel 来实现德尔菲法中的中位数法。
以上例的第一问为例,即根据历史资料,预测明年的货运量。
专家所给出的意见展开后所得结果错误!未找到引用源。
左列,应用Excel 升序工具排列后的结果如错误!未找到引用源。
右列。
读者可以通过工具栏上的“升序”按钮或者菜单“数据”→“排序”进行排序操作,具体过程请参见本书第二章“信息整理实验”的“上机实验步骤”环节。
图 错误!文档中没有指定样式的文字。
-3第一个问题的意见排序结果 根据理论知识准备环节中的相关知识介绍得k=7,得到中位数的位置为81=+k ,上下分位点的位置为:()122/33=+k ,()42/1=+k 。
所得结果为:394、394、417。
明显地,这种方法的手工操作步骤较多。
方法二:使用描述统计对数据列进行分析。
在“工具”菜单中选择“数据分析”->“描述统计”如错误!未找到引用源。
所示。
图 2-4用“描述统计”处理第一个问题在打开的“描述统计”的窗口中,点击“输入区域”后的文本框,用鼠标在工作簿上选中数据列,也可以手工输入数据列的地址,然后指定“输出区域”如错误!未找到引用源。
所示。
图 2-5用“描述统计”处理第一个问题的参数输入窗口点击“确定”,得到如错误!未找到引用源。
所示的结果。
图 2-6“描述统计”结果再利用公式求出上下分位点。
所得结果为:394、394、417。
这种方法较之第一种方法而言,可以得到关于数据列更加详细的统计信息,但还是需要手工计算分位点。
方法三:使用函数QUARTILE(返回一组数的四分位点)。
在单元格I1中输入“=QUARTILE(A1:A15,1)”求出下四分位点,再将A1:A15的行号和列好用“$”锁定,拖动至C2、C3,将“=QUARTILE($A$1:$A$15,1)”中的1改为2、3即可得到中位数和上四分位点,如错误!未找到引用源。
图 2-7 QUARTILE函数计算结果这种方法是寻找分位点最直接、最快速的方法,但前提是要了解中位数和上下四分位数的数学定义。
注意的是,由于Excel 中QUARTILE 函数所遵循的原理与理论知识准备中的原理不同,所以这里用于下四分位数最接近的数417来替代。
按照上述步骤,对其他问题进行相同处理,得到各自的中位数和四分位数如错误!未找到引用源。
图 2-8 各方案的中位数和四分位数根据所得的各方案的中位数和四分位数,理论上可认为各问题未来的发展趋势会介于上、下四分位数间,遵循上、下四分位数与中位数所确定的区间的一定分布。
下面,以第一个问题为例,介绍如何实施并评价德尔菲法中专家对问题的各个方案的评分效果。
(4) 根据对问题1a 方案的调查结果,在Excel 中得到问题1a 的各个方案的等级评定程度,如错误!未找到引用源。
所示。
图2-9 各方案的最终重要程度列表(5) 计算专家意见集中度S j ,通过5种方案的等级和来衡量∑==j m i ij j R S 1(方案的集中度),如错误!未找到引用源。
中单元格A17-E17。
图 2-10 各方案的等级和这里可先计算出一个方案的重要程度之和,然后用鼠标将公式拖动到其它对应的单元格中。
集中度越小,该方案越重要。
可见此题中方案2最重要。
(6) 计算变异系数j j j M V σ=,以反映专家意见协调程度,计算结果如错误!未找到引用源。
所示。
图 2-11 各方案的重要程度均值使用STDEV函数求解标准差,如错误!未找到引用源。
中A19至E19所示。
图 2-12 各方案的重要程度标准差变异系数V j如错误!未找到引用源。
所示。
图 2-13 各方案的变异系数单元格“A18:E18”的数值反映了专家意见集中度,值越小,方案越重要,由此可见方案2最重要。
单元格“A20:E20”中的数值是专家意见变异系数,值越小,专家协调程度越高,可见方案4协调程度最高。
1.1.6总结德尔菲法是系统分析法在意见和价值判断领域中的一种有益延伸,突破了传统的数量分析限制,为更合理地制定决策开阔了思路。
德尔菲法的实质是利用专家的主观判断,通过信息的交流与反馈,使预测意见趋向一致,逼近实际值。
在大多数情况下,专家的预测意见趋向一致,预测结果具有收敛性。
在少数情况下,无法取得一致意见。
即使这样,也能使预测者的见解明朗化,有利于对问题的深入研究。
同时,德尔菲法不受地区和人员的限制,用途广泛,费用较低,且能引导思维,提供了一种预测的系统方法。
德尔菲法尽管有很多优点也存在着如下一些不足:(1)易受主观因素的影响。
(2)缺乏深刻的理论论证。
(3)技术上不够成熟。
(4)预测结果是以中位数为标志的,完全不考虑离中位数较远(上、下四分点以外)的预测意见,有时确实漏掉了具有独特见解的有价值的预见。