条件格式公式

条件格式公式（共8篇）

条件格式公式 篇1

Excel条件格式公式应用四例

(2009-05-11 15:50:32)转载 标签：

excel 条件格式 杂谈

－－－－－本文乃转载，纯作学习之用。

我们知道，Excel“条件格式”功能可以根据单元格内容有选择地自动应用格式，它为Excel增色不少的同时，还为我们带来很多方便。如果让“条件格式”和公式结合使用，则可以发挥更大的威力，下面提供几个在“条件格式”中使用公式的应用实例，希望能给读者朋友带来一些启发。

一、判别输入是否正确

在输入如身份证等有固定位数的号码，出现位数不正确的情形时，我们希望Excel能够给出提示。虽然可以使用“数据有效性”设置实现，但是当输入出错时，Excel总会弹出一个提示的对话框，有朋友可能觉得这样“唐突”的提醒有点影响心情，那就让“条件格式”来“温和”的提醒吧。

1、创建“条件格式”的公式

假设我们通过“条件格式”，把符合位数(15位或18位)的号码所在单元格的填充色设置为绿色，输入完成后，通过查看单元格的填充色是否变为绿色，就可以知道输入的正确性了。由于身份证号码数据是属于“文本”类型的，先选中需要存放身份证号码的A2:A52单元格区域，将它们的数字格式设置为“文本”。然后在A2:A52单元格区域处于被选中的状态下，选择菜单“格式→条件格式”命令，打开“条件格式”对话框，单击“条件1”下方的下拉箭头，在弹出的下拉列表中选择“公式”接着在其右边的文本框中输入公式“=OR(LEN(A2)=15,LEN(A2)=18)”，然后单击“格式”按钮，在打开的“单元格格式”对话框中选择“图案”选项卡，选择绿色作为符合条件的单元格的填充色，设置好后单击“确定”按钮，返回“条件格式”对话框，检查无误再次单击“确定”就完成了条件格式的设置，小提示：上面的操作，先选中了一个单元格范围A2:A52，然后为这个单元格范围设置条件格式的公式。在这种情况下，公式中应使用选择范围中左上单元格的引用，此例中为A2。公式输入完成后，可以查看一下这个范围中的其它单元格的条件格式公式，如A8单元格，为“=OR(LEN(A8)=15,LEN(A8)=18)”，这是由于上面的引用为相对应用，它会根据单元格的实际偏移量自动改变，从而得到适合其它单元格的公式。

2、实现的具体效果

现在来测试一下上面设置可以实现的效果，在A2：A52区域的单元格中输入一些身份证号码，当位数是18位或15位时，所在单元格的填充色自动变为“绿色”，而位数不对的身份证号码，所在单元格的填充色不发生任何改变，从是否变色我们就可以判断输入的正确性了，全部输入并确认正确后，如果需要删除单元格条件格式，则先选中A2:A52单元格区域，然后打开“条件格式”对话框，单击如图3中的“删除”按钮，在打开的“删除条件格式”对话框中勾选“条件1”复选框，单击确定即可。

二、找出销售额的前三名

如图6中的B2:B12单元格中存放着销售额数据，要找出其中的前三名，让它们以蓝色字体显示。

先选中B2:B12单元格，打开如图1所示的对话框，输入公式“=B2>LARGE($B$2:$B$12,4)”然后将符合条件的字体格式设置为蓝色即可。说明：虽然可以对“销售额”数据列排序找出前三名，但是，可能我们希望以日期为顺序排列，这时“条件格式”就可以做到“两全其美”了。

三、让符合特殊条件的日期突出显示

有时，我们可能希望符合特殊条件的日期所在的单元格突出显示，比如星期六或星期天。这时我们可以先选中日期所在的单元格，如图6中的A2:A12，然后打开如图1所示的单元格，输入公式“=OR(WEEKDAY(A2,2)=6,WEEKDAY(A2,2)=7)”，然后设置符合条件的单元格填充色为阴影即可。

小提示：函数WEEKDAY(serial_number,return_type)的功能为返回某日期为星期几，Serial_number??表示一个顺序的序列号，代表要查找的那一天的日期。当参数return_type为2时，函数返回数字 1（星期一）到数字 7（星期日）之间的整数。

四、让工作表间隔固定行显示阴影

当单元格数据行较多，我们为了让显示效果更加醒目，可以让工作表间隔固定行显示阴影。

上面的效果是使用了公式“=MOD(ROW(),2)=0”，如果要间隔两行显示阴影则用公式“=MOD(ROW(),3)=0”，其余依次类推。

小提示：函数MOD(number,divisor)返回两数相除的余数，其中Number为被除数，Divisor为除数。函数ROW(reference)返回引用的行号。其中Reference??为需要得到其行号的单元格或单元格区域，如果省略 reference，则假定是对函数 ROW 所在单元格的引用。

条件格式公式 篇2

实例一:判断输入的正确性

我们知道身份证、单位工作人员工号等往往是有着固定位数的号码,如果在输入过程中出现位数不正确的情形时,Excel能够给出提示会使我们的工作减少不必要的重复。我们可以使用“条件格式”和“公式”来实现这一要求。

1 设置“条件格式”和“公式”

假设“条件格式”要求为:把符合位数(16位或18位)的号码所在单元格的填充色设置为绿色,输入完成后,通过查看单元格的填充色是否变为绿色,就可以知道输入的正确性。

首先将存放身份证号码的A2:A15单元格区域数字格式设置为“文本”,然后在A2:A15单元格区域处于被选中的状态下,选择菜单“格式→条件格式”命令,打开“条件格式”对话框,单击“条件1”下方的下拉箭头,在弹出的下拉列表中选择“公式”(图1)。

在其右边的文本框中输入公式“=OR(LEN(A2)=16,LEN(A2)=18)”,然后单击“格式”按钮,在打开的“单元格格式”对话框中选择“图案”选项卡,选择绿色作为符合条件的单元格的填充色(图2)。

设置好后单击“确定”按钮,返回“条件格式”对话框(图3),点击确定设置完毕。

2 实现效果

在A2:A15区域的单元格中输入一些身份证号码,当位数是18位或16位时,所在单元格的填充色自动变为“绿色”,而位数不对的号码所在单元格的填充色不发生任何改变(图4),从而可以判断输入的正确性。

全部输入并确认正确后需要删除单元格条件格式时,则先选中A2:A15单元格区域,然后打开“条件格式”对话框,单击如图三中的“删除”按钮,在打开的“删除条件格式”对话框中勾选“条件1”复选框,单击确定即可(图5)。

实例二:设置销售额的前三名的字体为蓝色、加粗

假设在工作表中(图6)B2:B12单元格中存放着销售额数据,要求找出其中的前三名且不改变其排序方式,将前三名以蓝色、加粗字体显示。

1)设置“条件格式”和“公式”

先选中B2:B12单元格,选择菜单“格式→条件格式”命令,打开“条件格式”对话框,输入公式“=B2>LARGE($B$2:$B$12,4)”,然后将符合条件的字体格式设置为蓝色,加粗,如图7。

点击确定设置完毕。

2)实现效果:实现效果如图8。

实例三:突出显示符合特殊条件的日期

在excel中函数WEEKDAY(serial_number,return_type)的功能为返回某日期为星期几,Serial_number表示一个顺序的序列号,代表要查找的那一天的日期。当参数return_type为2时,函数返回数字1(星期一)到数字7(星期日)之间的整数。

假设要求当天为星期六或星期天的日期所在的单元格突出显示。

1)设置“条件格式”和“公式”

以图六中数据为源数据,选中日期所在的单元格A2:A12,选择菜单“格式→条件格式”命令,打开“条件格式”对话框,输入公式“=OR(WEEK-DAY(A2,2)=6,WEEKDAY(A2,2)=7)”,然后设置符合条件的单元格图案为灰色-50%,如图9。

点击确定,设置完毕。

2)实现效果:实现效果如图10。

实例四:隔行着色

当单元格数据行较多,我们为了让显示效果更加醒目,可以为工作表设置间隔一定的行数添加颜色。

在Excel中,函数MOD(number,divisor)返回两数相除的余数,其中Number为被除数,Divisor为除数。函数ROW(reference)返回引用的行号,其中Reference为需要得到其行号的单元格或单元格区域,如果省略reference,则假定是对函数ROW所在单元格的引用。

1)设置“条件格式”和“公式”

假设要求工作表每隔一行添加灰色-25%的底纹。首先选择整个工作表,选择菜单“格式→条件格式”命令,打开“条件格式”对话框,输入公式“=MOD(ROW(),2)=0”,设置格式(图11)

单击确定设置完毕。

2)实现效果

实现效果如图12。

同样的设置方式,如果要间隔两行显示阴影则用公式“=MOD(ROW(),3)=0”,其余依次类推。

3 结束语

条件格式公式 篇3

我们先来研究一个抽奖活动：

三张奖券中只有一张能中奖,分别由三名同学无放回地依次抽取,每名同学抽取1张，问：最后一名同学抽到中奖奖券的概率是否比前两名同学小.

若抽到中奖奖券用“Y”表示，没有抽到用“Y”表示，那么这三名同学抽奖的结果共有三种可能：YYY，YYY和YYY.设“最后一名同学抽到中奖奖券”为事件B,则B仅包含一个基本事件：YYY.由古典概型的概率计算公式，可知最后一名同学抽到中奖奖券的概率P(B)=13.

思考问题一 如果已经知道第一名同学没有抽到中奖奖券，那么最后一名同学抽到中奖奖券的概率又是多少？

因为已知第一名同学没有抽到中奖奖券，所以这三名同学抽奖的结果只有两种可能：YYY和YYY.而“最后一

名同学抽到中奖奖券”仍仅包含一个基本事件：YYY.由古典概型的概率计算公式，可知最后一名同学抽到中奖奖券的概率为12，不妨记为P（B|A)，其中事件A表示“第一名同学没有抽到中奖奖券”，事件B|A表示在事件A发生的情况下事件B发生.

思考问题二 对于上面的事件A和事件B，P(B|A）与它们的概率有什么关系呢？

用Ω表示这三名同学抽奖的全部结果，则Ω=｛YYY,YYY,YYY｝，A={YYY,YYY}，B={YYY}.将事件A和事件B同时发生记作事件AB，则AB={YYY}.因此P(B|A)=12=n(AB)n(A)，

其中n(A)和n(AB)分别表示事件A和事件AB所包含的基本事件的个数.

而另一方面，根据古典概型的概率计算公式，有P(AB)=n(AB)n(Ω),P(A)=n(A)n(Ω)，其中n（Ω）表示Ω中包含的基本事件的个数.

所以P(B|A)=n(AB)n(A)=n(AB)n(Ω)n(A)n(Ω)=P(AB)P(A).

因此可以通过事件A和事件AB的概率来表示P（B|A).

由此，我们可以得到条件概率的定义和计算公式：

设A和B为任意两个事件，且P(A)＞0，那么在“A已发生”的条件下，B发生的条件概率P(B|A)=P(AB)P(A).

对任意两个事件A，B，若P（A）＞0，则有P(AB)=P(B|A)•P(A).该式为概率的乘法公式.

二、 实例体会

例1 5道题中有3道理科题、2道文科题，不放回地依次随机抽取2 道题，求：

(1) 第1次抽到理科题的概率；

(2) 第1次和第2次都抽到理科题的概率；

(3) 在第 1 次抽到理科题的条件下，第2次也抽到理科题的概率.

解 设第1次抽到理科题为事件A，第2次抽到理科题为事件B，则第1次和第2次都抽到理科题为事件AB.

(1) 从5道题中不放回地依次随机抽取2道题的基本事件数n（Ω）=A35=20.

根据分步计数原理，有n(A）=C13C14=12，于是P(A)=n(A)n(Ω)=1220=35.

(2) 根据分步计数原理，有n(AB)=A23=6，

于是P(AB)=n(AB)n(Ω)=620=310.

(3) 解法一 由(1)(2)，可得在第 1 次抽到理科题的条件下，第 2 次抽到理科题的概率P(B|A)=P(AB)P(A)=31035=12.

解法二 P(B|A)=n(AB)n(A)=612=12.

例2 一张银行储蓄卡的密码共有6位数字，每位数字都可从0～9这十个数字中任选一个.某人在银行自动提款机上取钱时，忘记了储蓄卡密码的最后一位数字，求：

(1) 任意按最后一位数字，不超过 2 次就按对的概率；

(2) 在此人记得密码的最后一位数字是偶数的条件下，任意按最后一位数字，不超过2次就按对的概率.

解 设第i次按对密码为事件Ai(i=1,2)，不超过2次就按对密码为事件A，则A=A1+A1A2.

(1) 因为事件A1与事件A1A2互斥，

事件A1与事件A2相互独立，所以P(A)=P(A1)+P(A1）P（A2)=110+910×19=15.

(2）用B 表示最后一位按偶数的事件，则P(A|B)=P(A1|B)+P(A1A2|B)=15+4×15×4=25.

巩 固 练 习

1. 将三颗骰子各掷一次，设事件A为“三个点数都不相同”，事件B为“至少出现一个6点”，则条件概率P（A|B）=（）

A. 6091 B. 12 C. 518 D. 91216

2. 在10个球中有6个红球、4个白球（各不相同），不放回地依次随机摸出2个球，在第1次摸出红球的条件下，第2次也摸出红球的概率是 .

3. 甲、乙两城市都位于长江下游，根据一百余年来的气象记录，可知甲、乙两城市一年中雨天所占的比例分别为20%和18%，两地同时雨天的比例为12%.求：

（1） 乙城市为雨天时，甲城市也为雨天的概率；

（2） 甲城市为雨天时，乙城市也为雨天的概率.

4. 袋中有6个黄色、4个白色的乒乓球，进行不放回抽样，每次任取一球，取两次，求：

（1） 第二次才取到黄球的概率；

（2） 已知其中一个是黄球，则另一个也是黄球的概率.

条件格式公式 篇4

可是条件格式一删除，“地砖”就不见了，想贴到别的地方呀，也只能带着条件格式一起贴，用什么办法能不带条件格式贴出“地砖”来呢?

很容易的，Excel里搞不定就贴到Word里再贴回来：

贴到Word里以后：

再贴回Excel中，没有了条件格式，但是“地砖”保留了：

和2010版的还有高招：用剪贴板粘贴!

以上技巧适用所有条件格式转成普通格式，

在Excel2010中将条件格式转换成普通格式方法

WPS表格条件格式应用举例二则 篇5

下面就通过几个经典的实例，带领大家进入这个神奇的世界。

首先向用户简单介绍一下单元格引用的概念，在WPS表格中，对单元格引用分为以下3类(4种引用)：

·相对引用：A1、D10等，当公式复制后，自动按行、列产生相对引用。

·绝对引用：$B$1、$F$5等，在行列号前置“$”号，保证公式复制后不会改变。

·混合引用：$A2、B$1等，行相对、列绝对引用(如$A2)，当公式向左右方向进行复制时，A列固定保持不变;行绝对、列相对引用(如：B$1)，当公式向上下方向进行复制时，第1行固定保持不变。

固定的隔行底纹

文章开始提到了“使用条件格式设置隔行底纹”的用法，但常规的隔行显示设置方法往往在用户进行条件筛选后就会失效，如图 1所示，那如何才能使间隔底纹不受筛选的影响呢?

图1 筛选后受影响的间隔底纹效果

具体设置如下：

步骤1 单击A2单元格，并拖动鼠标选择A2:G11区域;

步骤2 选择“格式|条件格式”菜单项，在弹出的对话框中设置条件格式公式如下：

条件格式公式为：=MOD (SUBTOTAL (3, $A$1:$A2), 2)

公式解析：该技巧主要利用SUBTOTAL函数支持对筛选条件下的数据计数功能来动态实现取得数据个数，再使用MOD函数进行计算余数，从而使满足条件的数据赋予格式。其中：SUBTOTAL函数参数“$A$1:$A2”中使用了A1的绝对引用和A2的混合引用。

步骤3 点击“格式”按钮，并设置相应的单元格格式如图 2所示：

图2 隔行底纹的条件格式设置

步骤4 点击“确定”按钮保存设置，并重新进行“筛选”设置，如筛选“语文成绩为60分(含)以上的学员”，最终格式显示效果如图 3所示。

图3 固定的隔行底纹显示效果

提示：由于条件格式公式中使用了SUBTOTAL函数，所以不管数据是否在筛选状态下都能够固定地显示隔行底纹，

自动标记重复数据

很多时候，用户由于误操作等原因，导致在数据表中输入了重复数据，如输入了重复的员工姓名、相同的产品编号等，如果能够在用户输入的时候进行“显式”的提醒，将会尽可能地避免用户的错误输入。所示的数据表中已经录入了部分学员的成绩，下面的方法将介绍使用“条件格式”来对用户输入了重复的学员姓名进行提醒。

图4 学员期末考试成绩表

具体的设置方法如下：

步骤1 首先将光标定位A2单元格，选择A2:G9单元格区域;

步骤2 选择“格式|条件格式”菜单项，在弹出的对话框中设置条件格式公式如下：

条件格式公式：=COUNTIF($A$1:$A2,$A2)>1

思路解析：公式主要利用了COUNTIF函数结合单元格的混合引用来实现动态计数，当用户输入重复的学员时，公式计算值必定大于1，因此满足条件赋予指定格式。

步骤3 点击“格式”按钮，并设置相应的单元格格式如图 5 所示：

图5 重复数据标记的条件格式设置

步骤4 点击“确定”按钮保存设置，当用户输入重复的学员时，系统会以特殊格式显示出来，显示效果如图 6 所示。

图6 重复数据标记结果

小结：

1、理解并掌握单元格的“混合引用”，有利于对WPS表格的公式进行复制和运用。

2、利用SUBTOTAL函数结合混合引用，可以实现“固定的隔行底纹”的显示。

3、利用COUNTIF函数结合混合引用，用户可以有目的地防止输入重复数据。

用户理解并掌握条件格式后，结合实际的工作进行合理地运用。如“查找当前数据在指定的数据表中是否存在?”这类应用都可以利用WPS表格的“条件格式”来实现的。

适用范围:

条件格式公式 篇6

应用实例：某教师想在一个工作表中突出显示期末成绩高于期中成绩的学生，实现后的工作表如图1所示。

图1

在该工作表中，有全体学生两次考试的成绩，条件格式功能将学生中期末成绩高于期中成绩的高亮显示。这种格式是动态的：如果改变考试的分数，格式会自动调整。

下面介绍具体操作步骤。

1.按图1所示创建一个工作表用于练习。

2.选择单元格A2:C11，然后选择菜单命令“格式>条件格式”，在“条件格式” 对话框中的最左边选择“公式”，然后在右侧输入框中输入下列公式(如图2所示)：

=$C2>$B2

图2

3.单击“格式”按钮，打开“单元格格式”对话框，为符合条件的单元格设置格式，例如，将单元格的底纹设置为浅绿色，如图3所示，

图3

4.设置完毕单击“确定”按钮，回到原来对话框，再单击“确定”，则现在工作表如图4所示。

图4

条件格式设置中的公式会作用于所选区域，而不只是作用于第2行，这是因为在公式列参数使用了$，这样列就成了绝对引用，而行是相对引用。我们可以在其它某行(如第10行)中单击，然后选择菜单命令“格式>条件格式”，可以看到如图5所示的公式，该行使用的公式为：

=$C10>$B10

图5

至于图1中数据区域的外观与图4略有不同，是因为对这些单元格应用了黑色的边框。从本例可以看出，灵活地运用条件格式，可以帮助我们快速完成一些比较实际的任务，从而提高工作的效率。

条件格式公式 篇7

回归分析是用数学解决实际问题时用得最多的方法 (模型) , 数百年来人们用回归模型解决了众多领域中的实际问题, 取得丰硕成果, 相应的回归理论也不断丰富和完善。平方和分解是回归模型中的重要内容, 但几乎所有讨论回归分析的教材和专著, 例如国内关于回归的经典专著 (见[1], [2]) 及最新教材包括研究生教材 (见[3], [4]) , 在讨论平方和分解时, 并没有指出什么情况下平方和分解是有效的?

为讨论这个问题, 我们在本节后半部分先给出若干基本定义, 并在第三节中加以详细讨论。事实上本文指出的问题会给实际工作者带来很多困扰。

设

y=β0+x1β1+x2β2+…+xkβk+u

是一个多自变量回归模型。它的抽样模型为

yt=β0+xt1β1+xt2β2+…+xtkβk+ut, t=1, 2, …, n (1)

模型的最小二乘估计满足平方和分解公式:

∑$(y{}_t-\overline{y}){}^2=$∑$(y{}_t-\widehat{y}{}_t+\widehat{y}{}_t-\overline{y}){}^2=$∑$(y{}_t-\widehat{y}){}^2+$∑$(\widehat{y}{}_t-\overline{y}){}^2$ (2a)

注意其中交叉项为0。记S${}_y^2$=∑$(y{}_t-\overline{y}){}^2$为总的离差平方和, S${}_v^2$=∑$(\widehat{y}{}_t-\overline{y}){}^2$为回归平方和, S${}_e^2$=∑$(y{}_t-\widehat{y}){}^2$为残差平方和, 故有

S${}_y^2$=S${}_v^2$+S${}_e^2$ (2b)

用语言表示, 就是

总离差平方和=回归平方和+残差平方和 (2c)

回归平方和占总离差平方和的比例称为判决系数, 记为R2, 即。

$R{}^2=\frac{S{}_v^2}{S{}_y^2}=\frac{{\displaystyle \sum (}\widehat{y}{}_t-\overline{y}){}^2}{{\displaystyle \sum (}y{}_t-\overline{y}){}^2}(3)$

它是衡量用自变量解释因变量y效果的重要指标, R2越接近1模型拟合效果越好。利用平方和分解R2还可表示成

$R{}^2=1-\frac{S{}_e^2}{S{}_y^2}(4)$

由于残差平方和S${}_e^2$是模型的重要指标, 许多估计与检验与S${}_e^2$有关, 是必须计算的量。故实际应用或软件设计中, 大多是按 (4) 式计算R2。但 (4) 式并不总成立。此时如果仍按 (4) 式计算, 则有可能造成R2为负值的错误。

2 Eviews软件中的一个错误

现在一些国际会议、杂志编辑部及各种赛事等, 为避免学术作假, 要求论文涉及的计算, 要使用SAS, SPSS, EVIEWS等权威商业软件计算。

但在用EVIEWS软件 (以下简称E软件) 计算回归模型时, 偶尔会遇到R2为负值的情况。显然, E软件是按 (4) 式计算才会出现R2为负值。

例1

取样本容量n=10, 自变量x1, x2, x3与随机项的观测值均取[0, 1]区间上的随机数, 因变量y按y=0.5x1+0.3x2+0.2x3+u产生, 数据见表1。

用E软件 (最新6.0版本) 分别计算有和无常数的回归模型。

模型a:有常数模型

输入指令:y c x1x2x3, 可得如下结果:

此模型R2=0.219 较小, 调整R2=-0.1709为负, 与实际数据特点相符。再考虑无常数模型模型

模型b:无常数模型

输入指令:y x1x2x3, 可得如下结果:

无常数项模型更符合数据生成过程, 从t值和p值 (概率) 而言, 模型有较大改善, 但不可思议的情况出现了, 两个平方和的比值R2竟然是负值, 显然, 平方和分解公式不总成立。

习惯上实际工作者总是先建立有常数项的回归模型, 当常数项不显著时再建无常数项的回归模型, 按回归理论, 从模型中剔除不显著变量, R2理应仅作微小降低, 而如果从模型中剔除不显著变量, R2急剧下降 (仍然>0) , 就有理由怀疑软件的正确性, 但对于实际工作者是很难发现E软件有错的这种蛛丝马迹 (见例2) 。

3 平方和分解公式成立的条件

关于各平方和的分布及相互间的独立性的证明, 需要因变量满足正态性标准假定, 且在模型的矩阵形式下更简单, 下面按模型的矩阵形式进行讨论。以下的讨论中Y, U是n维随机列向量, 1n为分量都是整数1的n维列向量, Jn=1n1′n是元素均为1的n阶方阵, X是自变量的n×k阶观测矩阵, 记

$X=(1{}_n,X)‚B=\left(\begin{array}{l}\beta {}_0\\ \beta \end{array}\right)$

是k+1维回归参数向量, 其中β′= (β1, β2, …, βk) , (1) 式可用矩阵表示成

Y=X B+U, Y～Nn (X B, σ2I) , (5)

将模型 (1) 式表成 (5) , 是将常数项视为特殊自变量。设Px为由X的列向量张成的线性子空间l (X) 的投影算子, 即Px=X (X′X) -1X′, 类似的有PX=X (X′X) -1X′, 可以验证投影算子还是对称幂等矩阵。

模型的最小二乘估计可表示为

$\widehat{B}=(X^{\prime }X){}^{-1}{X}^{\prime }Y,\widehat{Y}=X\widehat{B}={\rm P}{}_{x}Y(6)$

这时, 各平方和的二次型表示分别为:

$S{}_y^2=Y^{\prime }({\rm I}-\frac{1}{n}J{}_n)Y,S{}_v^2=Y^{\prime }({\rm P}{}_x-\frac{1}{n}J{}_n)Y,S{}_e^2=Y^{\prime }({\rm I}-{\rm P}{}_x)Y$

可以验证以上二次型的矩阵都是对称幂等矩阵。

关于多元正态随机向量Y有以下结论 (见[1]) 。若Y～Nn (μ, σ2A) , A, A1, A2为常数矩阵, 则有

命题1:如果A1A′2=0, 则A1Y与A2Y独立。

命题2:设A=A′, 则Y′AY/σ2～χ${}_m^2$ (λ) 的充要条件是A2=A, rkA=m, 其中λ=μ′Aμ是非中心化参数。

命题3:设A1, A2是对称阵, 则Y′A1Y与Y′A2Y相互独立的充要条件是A1A2=0。

关于模型含常数项时, 平方和分解成立, 各平方和的分布及独立性可由上述3个命题得到, 不在这里讨论, 以下仅讨论模型不含常数项时的结论。

不含常数项回归模型的矩阵表示式为

Y=Xβ+U, Y～Nn (Xβ, σ2I)

模型的最小二乘估计得结果如下:

$\begin{array}{l}\widehat{\beta }=(X^{\prime }X){}^{-1}{X}^{\prime }Y(7)\\ \widehat{Y}=X\widehat{\beta }=X(X^{\prime }X){}^{-1}{X}^{\prime }Y={\rm P}{}_{X}Y\end{array}$

相应的残差平方和S${}_e^2$=Y′ (I-PX) Y。注意,

$Y^{\prime }({\rm I}-\frac{1}{n}J{}_n)Y=Y^{\prime }({\rm I}-{\rm P}{}_x)Y+Y^{\prime }({\rm P}{}_X-\frac{1}{n}J{}_n)Y$

仍然成立, 但右端第二项不再是回归平方和, 事实上回归平方和

∑$(\widehat{Y}{}_t-\overline{y}){}^2=(\widehat{Y}-\overline{y}1{}_n)+(\widehat{Y}-\overline{y}1{}_n)=Y^{\prime }({\rm P}{}_X-\frac{1}{n}J{}_n)({\rm P}{}_X-\frac{1}{n}J{}_n)Y$

而由于$1{}_n\in \text{l}(X),1{}_n\notin \text{l}(X),({\rm P}{}_X-\frac{1}{n}J{}_n){}^2\ne ({\rm P}{}_X-\frac{1}{n}J{}_n)$不再是幂等阵。其次, 交叉项不为0:

$Y^{\prime }({\rm I}-{\rm P}{}_X)({\rm P}{}_X-\frac{1}{n}J{}_n)Y=\frac{1}{n}{Y}^{\prime }({\rm P}{}_{X}J{}_n-J{}_n)Y\ne 0$

因此, 我们有

定理1:当回归模型不含常数项时:

1) 平方和分解式 (2) 不成立;

2) 按 (4) 计算的R2与原始定义 (3) 式不等价, 且可能为负值;

3) 原回归平方和S${}_v^2$=∑$(\widehat{y}{}_t-\overline{y}){}^2$不服从χ2分布, 且S${}_v^2$与残差平方和S${}_e^2$不独立, 基于R2导出的F统计量不服从F分布。

4 新平方和分解公式及R2的重新定义

根据定理1、定理2, 在原有平方和分解体系下, 模型中的常数项是特殊自变量。这给实际应用带来尴尬, 常见一些文献中不讨论常数项的显著性, 例如邹至庄先生在一些回归模型中标出了常数项的t值 (显著) , 而一些回归模型却唯独不标常数项的t值 (猜测常数项的t值不显著, 见[5]P90, P91) , 无疑这也是原平方和分解惹的祸。考虑到常数项其实是恒取1的自变量, 理应和其它自变量一样对待, 这可以给理论分析和实际应用带来很多方便。这时, 回归模型总可以写成:

y=x1β1+x2β2+…+xkβk+u,

其中x1可以是恒取1的常数项 (对应与原来的有常数项模型) , 也可以是不恒取1的自变量 (对应于无常数项模型) 。相应地, 它的抽样模型为

yt=xt1β1+xt2β2+…+xtkβk+ut, t=1, 2, …, n.

定义新平方和分解式为:

∑y${}_t^2$=∑$(y{}_t-\widehat{y}{}_t+\widehat{y}{}_t){}^2=$∑$(y{}_t-\widehat{y}{}_t){}^2+$∑$\widehat{y}{}_t^2(9)$

它的矩阵形式为

Y′Y=Y′ (I-PX) Y+Y′PXY.

为区别原平方和分解的记号, 记$\stackrel{\cdots }{{\displaystyle S}}{}_y^2=Y^{\prime }Y=\parallel Y\parallel {}^2‚\stackrel{\cdots }{{\displaystyle S}}{}_v^2=Y^{\prime }y{\rm P}{}_{X}Y={\widehat{Y}}^{\prime }\widehat{Y}=\parallel \widehat{Y}\parallel {}^2$, 无论模型是否含常数项都成立的新平方和分解式:

$\stackrel{\cdots }{{\displaystyle S}}{}_y^2=\stackrel{\cdots }{{\displaystyle S}}{}_v^2+\stackrel{\cdots }{{\displaystyle S}}{}_e^2(10)$

可以重新定义度量模型拟合效果的R2,

$\stackrel{\cdots }{{\displaystyle R}}{}^2=\frac{\stackrel{\cdots }{{\displaystyle S}}{}_v^2=}{\stackrel{\cdots }{{\displaystyle S}}{}_y^2=}=\frac{\parallel \widehat{Y}\parallel {}^2}{\parallel Y\parallel {}^2}=1-\frac{S{}_e^2}{\stackrel{\cdots }{{\displaystyle S}}{}_y^2}(11)$

原R2可以理解为自变量解释因变量的波动 (平方和) 的能力, 新的$\stackrel{\cdots }{{\displaystyle R}}{}^2$则是自变量解释因变量的长度平方的能力。而且$\stackrel{\cdots }{{\displaystyle R}}{}^2$的几何意义更明显, 原始向量Y直交分解为$\widehat{Y}$及残差向量$Y-\widehat{Y}‚$自然也满足勾股定理, 同时自变量解释因变量的能力越强, 残差向量的长度越短, $\stackrel{\cdots }{{\displaystyle R}}{}^2$越接近1。

不难看出, 本文的关键变动是重新定义了回归平方和, 原有的与回归平方和无关的结论, 如$\widehat{\beta }$的分布及与S${}_e^2$独立, 检验βi=0的t统计量 (自由度为n-k) 等均成立。由以上定义及命题2, 命题3, 我们有

定理2:模型及相关定义 如上。则

(1) S${}_e^2$/σ2～χ${}_{n-k}^2$与$\stackrel{\cdots }{{\displaystyle S}}{}_v^2$独立

(2) 在β=0时有

$\stackrel{\cdots }{{\displaystyle S}}{}_v^2/\sigma {}^2\sim \chi {}^2(k)$及

$F=\frac{\stackrel{\cdots }{{\displaystyle R}}{}^2/k}{(1-\stackrel{\cdots }{{\displaystyle R}}{}^2)/(n-k)}=\frac{\stackrel{\cdots }{{\displaystyle S}}{}_v^2/k}{\stackrel{\cdots }{{\displaystyle S}}{}_e^2/(n-k)}\sim F(k,n-k)$

约翰逊等也给出了 (9) 式的分解, 且在 (9) 式两端减n$\overline{y}$2, 再验证$\overline{y}=\overline{\widehat{y}}$, 就得到原平方和分解及原R2 (见[6]) 。

而SPSS软件关于平方和分解及R2的定义与[6]相同。但在实际计算时, 若模型不含常数时SPSS软件给出的R2值是按$\stackrel{\cdots }{{\displaystyle R}}{}^2$计算的。

是否采用如SPSS的弥补方案, 在模型含常数项时用R2, 在模型不含常数项时用$\stackrel{\cdots }{{\displaystyle R}}{}^2$衡量拟合优度?笔者认为不妥。我们不妨来看看如下算例。

例2

表2中数据用SPSS17分别计算有常数的线性回归模型与无常数的线性回归模型, 部分结果如下。

事实上R2与$\stackrel{\cdots }{{\displaystyle R}}{}^2$对比参照的对象不同, 有常数项时S${}_e^2$是S${}_y^2$的一部分 (R2自然不会出现负值) , 无常数项时S${}_e^2$不再是S${}_y^2$的一部分。

根据最小二乘估计原理, 从模型中剔除一个变量 (包括常数项) 时S${}_e^2$将增大, 相应的R2将减小。经检验常数项不显著时应从模型中剔除常数项, 这时S${}_e^2$会增加, 但因参照对象不同, $\stackrel{\cdots }{{\displaystyle R}}{}^2$不一定比R2小, 这使得由R2到$\stackrel{\cdots }{{\displaystyle R}}{}^2$不再随自变量的增减保持单调, 同时弥补方案意味着将常数项视为特殊自变量, 这给理论分析带来不便。虽然本文的讨论, 对于理论工作者不难理解, 但妥协方案给大多数实际工作者的应用与计算结果分析带来困惑。

综上所述, 将$\stackrel{\cdots }{{\displaystyle R}}{}^2$替代R2, 常数项与其它自变量一样对待, 不再特殊, 势在必行。

参考文献

[1].张尧庭, 方开泰.多元统计分析引论[M].北京:科学出版社, 1982

[2].陈希孺, 王松桂.近代实用回归分析[M].南宁:广西人民出版社, 1984

[3].袁志发, 宋世德.多元统计分析[M].北京:科学出版社, 2009

[4].李静萍, 谢邦昌.多元统计分析方法与应用[M].北京:中国人民大学出版社, 2008

[5].郑宗成等译, 邹至庄著.经济计量学[M].北京:中国友谊出版公司, 1988

[6].陈旋, 叶俊译, 约翰逊等著.实用多元统计分析 (第六版) [M].北京:清华大学出版社, 2008

条件格式公式 篇8

关于申报国家高技术产业化重大专项项目的基本条件、程序、资金申请报告格式和评审要点

一、基本条件

1、符合专项项目公告的要求；

2、项目采用的科技成果应具有先进性，需经国家权威机构认证或出具技术检测报告等证明材料，并取得必要的验证和生产许可；

3、项目建设具有明确的目标，建设任务合理，具有一定的规模、较好的经济效益和良好的推广应用价值；

4、项目法人必须具有较强的技术开发、资金筹措和工程建设组织能力，具有较好的业绩、较高的资信等级，资产负债在合理范围（50%以下）；

5、项目法人应具备使用拟产业化技术的权利，应具有相关的生产、经营资格；

6、项目投资筹措方案已经落实，并符合国家有关项目资本金制度的要求（项目法人30%以上自有资金）；

7、项目应以备开工建设条件或已经开工建设但备案不超过两年，包括已落实项目建设用地，具有环保以及其他许可文件，落实主要原材料等外部配套条件；

8、项目必须已经按照规定完成备案（或核准）；对于已开工项目的备案（或核准）文件，应在有效期内且未满两年。

二、评审和实施程序

——发布公告，明确支持的重点领域和技术方向；

——项目法人编制资金申请报告；

——上报国家发展改革委初审；

——国家组织专家评审或咨询机构评估；

——国家批复资助项目，明确项目目标、内容、国家补助资金额度及使用方向；

——根据项目建设进度分下达补助资金；

——项目验收及后评估。

三、项目资金申请报告要点

1、项目建设的意义和必要性，国内外现状和技术发展趋势

（1）行业规划与产业政策

（2）产品特点与市场需求

（3）行业技术与发展趋势

（4）产业现状与产业化前景

（5）产业关联及社会经济影响

2、市场分析

（1）国内外市场

（2）产品定位

（3）目标市场

（4）分析与预测

3、项目技术基础

（1）成果来源、产权

（2）已完成研究开发情况

（3）技术或工艺特点和比较优势

（4）该技术突破对行业技术进步的重要意义

4、建设方案、规模、地点

（1）主产与副产品(现状分析产品纲领)

（2）产品规模(资源目标经济实力)规模

（3）建设地点

5、技术特点、工艺技术路线、设备选型和主要技术经济指标（先进与实用、合理性）

6、原材料供应及外部配套条件

7、环境污染防治

（1）污染因素与针对性措施

（2）达标排放

8、建设工期和工程进度安排

9、项目承担基本情况

（1）法人(资本负债业绩资信)

（2）技术与人才储备

（3）产业化基础(研发装备试销)

10、投资估算，资金筹措，贷款偿还计划，流动资金来源

（1）估算

（2）资金筹措(资本金可靠性)

11、项目经济指标（内部收益率、投资利润率、投资回收期、贷款偿还期等）的计算和评估

（1）价格

（2）成本

（3）主要经济指标

12、经济效益和社会效益分析

13、项目风险分析

（1）市场与竞争风险

（2）技术风险

（3）融资风险

主要附件：

——项目备案文件；

——资金落实有关证明材料；

——科研成果相关证明材料；

——环境保护部门审查意见；

——相关产品生产许可证明文件；

——项目建设用地落实情况证明材料；

——原材料及外部配套条件证明材料（水、电、汽等）

四、专家评审要点

1、项目必要性；

2、技术基础：

创新性、可行性、知识产权

3、方案可行性；

4、申报单位条件：

技术开发能力、经营能力、资产负债

5、预期效益