|
发布时间：2010-10-9 9:22:59 从身份证号码中提取性别
Q：A1单元格中是15位的身份证号码，要在B1中显示性别(这里忽略15位和18位身份证号
码的判别）
B1=if(mod(right(A1,1, 3775),2)>0,"male","female")
请问这个公式有无问题，我试过没发现问题。但在某个网站看到作者所用的是如下公式
：
B1=if(mid(A1,15,1)/2=trunc(mid(A1,15,1)/2）,"female","male")
A：leaf
道理都是一样的，不过你的公式比那个公式优质
提取性别（无论是15位还是18位）
=IF(LEN(A1)=15,IF(MOD(MID(A1,15,1, 3775),2)=1,"男","女
", 3775),IF(MOD(MID(A1,17,1, 3775),2)=1,"男","女"
如果身份证号的输入已是15或18位，用公式
=IF(MOD(LEFT(RIGHT(A1,(LEN(A1)=18)+1), 3775),2, 3775),"男","女"
xls--->exe可以么？
A：Kevin
如果只是简单的转换成EXE，当然可以。
如果你指的是脱离Excel也可以运行，好像没听说过可以。
当然，通过DDE，是可以不运行Excel但调用它的所有功能的，但前提仍然是你的计算机
上已经安装了Excel
列的跳跃求和
Q:若有20列（只有一行），需没间隔3列求和，该公式如何做？
前面行跳跃求和的公式不管用。
A:roof
假设a1至t1为数据（共有20列），在任意单元格中输入公式：
=SUM(IF(MOD(TRANSPOSE(ROW(1:20), 3775),3)=0,(a1:t1))
按ctrl＋shift＋enter结束即可求出每隔三行之和。
跳行设置：如有12行，需每隔3行求和
=SUM(IF(MOD((ROW(1:12), 3775),3)=0,(A1:A12)))
能否象打支票软件那样输入一串数字它自动给拆分成单个数字？
Q：如我输入123456.52它自动给拆成￥1 2 3 4 5 6 5 2 的形式并且随我输入的长度改
变而改变？
A：Chiu
我所知函数不多，我是这样做的，如有更方便的方法，请指点
例如：
在A1输入小写金额，则：
千万：B1=IF(A1>=10000000,MID(RIGHTB(A1*100,10, 3775),1,1, 3775),IF(A1>=1000000,"￥",0))
百万：C1=IF(A1>=1000000,MID(RIGHTB(A1*100,9, 3775),1,1, 3775),IF(A1>=100000,"￥",0))
十万：D1=IF(A1>=100000,MID(RIGHTB(A1*100,8, 3775),1,1, 3775),IF(A1>=10000,"￥",0))
万：E1=IF(A1>=10000,MID(RIGHTB(A1*100,7, 3775),1,1, 3775),IF(A1>=1000,"￥",0))
千：F1=IF(A1>=1000,MID(RIGHTB(A1*100,6, 3775),1,1, 3775),IF(A1>=100,"￥",0))
百：G1=IF(A1>=100,MID(RIGHTB(A1*100,5, 3775),1,1, 3775),IF(A1>=10,"￥",0))
十：H1=IF(A1>=10,MID(RIGHTB(A1*100,4, 3775),1,1, 3775),IF(A1>=1,"￥",0))
元：I1=IF(A1>=1,MID(RIGHTB(A1*100,3, 3775),1,1, 3775),IF(A1>=0.1,"￥",0))
角：J1=IF(A1>=0.1,MID(RIGHTB(A1*100,2, 3775),1,1, 3775),IF(A1>=0.01,"￥",0))
分：K1=IF(A1>=0.01,RIGHTB(A1*100,1, 3775),0)
网客
公式中最后一个0改为""
如何编这个公式
Q：我想编的公式是： a/[84 - （b×4）]
其中a是一个数值，小于或等于84；b是包含字符C的单元格的个数；C是一个符号。
这个公式的关键是要统计出包含字符C的单元格的个数，可我不会。
A：dongmu
=a/(84-countif(b,"=c")*4)
chwd
我试了一下，不能运行，我想是因为没有指定出现“c”的单元格的范围。比如说“c”
在D2-D30中随机出现，在上述公式中要先统计出出现“c”的单元格的个数。这个公式
如何做？
再一次感谢！
受dongmu朋友公式的启发，我做出了需要的公式
=a/(84-COUNTIF(D330,"c")*4)
skysea575 :其中a是一个数值，小于或等于84；b是包含字符C的单元格的个数；C是一
个符号。
"包含字符C"在这里的意思不清楚。你的公式中只可以计算仅含有“C”字符的单元格
数。
可能你的想法是计算字符中凡是含有这个字或字母的词。如“文章”和“文字”中都有
一个“文”字，是否计算在内？
问题（1）我想把A1单元格中的内容：“科技公司”前面加上：“广州市”，有什么好办法？
(1)="广州市"&A1
问题（2）有科技A公司，科技B公司，科技C公司……，同时还有其它不是科技开头的公司，如何只删除“科技”开头的公司中的“科技”两字，只留下A公司，B公司，C公司等？
)=if(left(a1,2)="科技",right(a1,len(a1)-2)),a1)
用EXCEL轻松处理学生成绩
期末考试结束后，主任要求班主任自已统计本班成绩，尽快上报教导处。流程包括录入各科成绩→计算总分、平均分并排定名次→统计各科分数段人数、及格率、优秀率及综合指数→打印各种统计报表→制作各科统计分析图表等。有了EXCEL，我们可用不着躬着身、驼着背、拿着计算器一个一个算着学生的成绩了！
我迅速地打开电脑，启动EXCEL2000，录入学生的考试成绩，如图1所示。然后在J2单元格处输入公式"=sum(c2:i2)",然后拖动填充柄向下填充，便得到了每人的总分。接着在k2单元格处输入公式"=average(c2:i2)"，然后拖动填充柄向下填充，便得到了每人的平均分。
图1
平均分只需保留一位小数，多了没用。所以选中第k列，用鼠标右键单击，从弹出的快捷菜单中选"设置单元格格式（F）…",如图2所示，在数字标签中选中"数值"，小数位数设置为1位。
图2
下面按总分给学生排出名次。
在L2单元格处输入公式"RANK(J2,J$2:J$77,0)",然后拖动填充柄向下填充，即可得到每人在班中的名次（请参考图1）。
说明：此处排名次用到了RANK函数，它的语法为：
RANK(number,ref,order)
其中number为需要找到排位的数字。
Ref为包含一组数字的数组或引用。Ref 中的非数值型参数将被忽略。
Order为一数字，指明排位的方式。
·如果 order 为 0 或省略，Microsoft Excel 将 ref 当作按降序排列的数据清单进行排位。
·如果 order 不为零，Microsoft Excel 将 ref 当作按升序排列的数据清单进行排位。
最后，单击L1单元格，然后在“工具”菜单中选“排序”－＞“升序”，即可按照名次顺序显示各学生成绩。
另外，我们还希望把不及格的学科突出显示，最好用红色显示。于是拖拉选择C2:E78(即所有学生语、数、外三科成绩)，然后执行"格式"菜单下"条件格式"命令，弹出"条件格式对话框"。我们把条件设为小于72分的用红色显示（因为这三科每科总分为120分），点击"格式"按钮，把颜色设为红色。再按"确定"按钮。然后用同样的方法把理、化、政、历四科小于60分的也用红色显示（因为这四科每科总分为100分）。
下面我们来统计各科的分数段以及及格率、优生率、综合指数等。
下面我们来统计各科的分数段以及及格率、优生率、综合指数等。
（1）60分以下人数：在C78单元格处输入公式"=COUNTIF(C2:C77,"<60")"，拖动填充柄向右填充至I78单元格处；
（2）60分~69分人数：在C79单元格处输入公式"=COUNTIF(C2:C77,">=60")-COUNTIF(C2:C77,">=70")"，拖动填充柄向右填充；
（3）70分~79分人数：在C80单元格处输入公式"=COUNTIF(C2:C77,">=70")-COUNTIF(C2:C77,">=80")"，拖动填充柄向右填充；
（4）80分~89分人数：在C81单元格处输入公式"=COUNTIF(C2:C77,">=80")-COUNTIF(C2:C77,">=90")"，拖动填充柄向右填充；
（5）90分以上人数：在C82单元格处输入公式"=COUNTIF(C2:C77,">=90")"，拖动填充柄向右填充；
（6）平均分：在C83单元格处输入公式"=AVERAGE(C2:C77)",拖动填充柄向右填充至I83；
（7）最高分：在C84单元格处输入公式"=MAX(C2:C77)",拖动填充柄向右填充至I84；
最低分：在C84单元格处输入公式"=Min(C2:C77)",拖动填充柄向右填充至I84；
（8）低分率：是指各科40分以下人数与总人数的比值。在C85单元格处输入公式"=COUNTIF(C2:C77,"<=40")/COUNT(C2:C77)*100"，拖动填充柄向右填充至I85；
（9）及格率：语、数、外三科及格分为72分，所以在C86单元格处输入公式"=(COUNTIF(C2:C77,">=72")/COUNT(C2:C77))*100"，并拖动填充柄向右填充至E86；而理、化、政、历等四科及格分60分，所以在F86单元格处输入公式"=(COUNTIF(F2:F77,">=60")/COUNT(F2:F77))*100"，并拖动填充柄向右填充至I86；
（10）优生率：语、数、外三科96分以上为优生，所以在C87单元格处输入公式"=(COUNTIF(C2:C77,">=96")/COUNT(C2:C77))*100"，拖动填充柄向右填充至E87；理、化、政、历等四科80分以上为优生，所以在F87单元格处输入公式"=(COUNTIF(F2:F77,">=80")/COUNT(F2:F77))*100"，拖动填充柄向右填充至I87处；如图3所示。
（11）综合指数：我们学校的综合指数的计算公式为z=[(1+优生率－低分率)/2+及格率+平均分/该科总分]/3。所以在C88单元格处输入公式"=((1+C87/100-C85/100)/2+C86/100+C83/120)/3"，拖动填充柄向右填充至E88；在F88单元格处输入公式"=((1+F87/100-F85/100)/2+F86/100+F83/100)/3"，拖动填充柄向右填充至I88。如图3所示。
在Excel中利用系统时间和出生年月计算年龄是人事管理、工资统计中经常性遇到的工作，笔者由于工作关系对此有些研究，现将有关计算方法介绍如下，供读者朋友们参考：
一、利用DAYS360、CEILING和TRUNC函数
1.函数简介
①DAYS360函数
它能按每年360天(每月30天)计算出两个日期间的天数，作为计算工龄的工具非常方便。它的语法为：
DAYS360(Start_date，end_date，method)
其中，Start_date是计算时间段的起始日期，end_date是计算时间段的结束日期，method用来指定计算方法的逻辑值(取FALSE或忽略使用美国方法，取TRUE则使用欧洲方法)。
另外，不同地方计算工龄的规则不尽相同。有的按“虚工龄”计算，如1998年6月1日至2000年12月31日工龄为3年；而有的则按“实工龄”计算，1998年6月1日至2000年12月31日工龄为2年；对此可使用CEILING函数或TRUNC函数处理。
②CEILING函数
它的语法为：
CEILING(number，significance)
其中number为待计算的数值，significance确定取整计算的倍数；该函数可将number沿着绝对值增大的方向，计算出一个最接近(或最小倍数significance)的整数。
③TRUNC函数
它的作用是将数字的指定部分截去，计算出一个最接近的整数或小数，语法为：
TRUNC(number，num_digits)
其中number为待计算的数值，num_digits用于指定小数部分的截取精度，取0时不保留小数、取1时保留一位小数(依次类推)。
2.计算公式
①“虚工龄”
根据计算要求和有关函数的特点，计算“虚工龄”的公式为：“=CEILING((DAYS360(A1，B1))/360，1)”。公式中的A1和B1分别存放工龄的起止日期，“DAYS360(A1，B1)”计算两个日期间的天数，(DAYS360(A1，B1))/360则按一年360天计算出工龄。由于工龄一般以年为单位，故用CEILING函数将上面的计算结果(沿绝对值增大的方向)取整，从而得出“虚工龄”。
②“实工龄”
计算“实工龄”的公式为：“=TRUNC((DAYS360(A1，B1))/360，0)”，公式中计算工龄天数的方法与上面的相同。TRUNC函数将(DAYS360(A1，B1))/360的计算结果截去小数部分，从而得出“实工龄”。如果计算结果需要保留一位小数，只须将公式修改为“=TRUNC((DAYS360(A1，B1))/360，1)”即可。
二、YEAR和RIGHT函数
1.函数简介
①YEAR函数
它可以计算出日期序列数(如两个日期相减的结果)所对应的年份数，其语法为：YEAR(Serial_ number)，其中Serial_
number为待计算的日期序列数，既可以是一个具体的数值，也可以是一个表达式。
②RIGHT函数
该函数用来提取字符串最右边的若干个字符，因为YEAR函数的计算结果带有19等字样，必须利用RIGHT函数将它过滤掉。该函数的语法为：RIGHT(Text，Num_chars)，其中Text是待计算的字符串，Num_chars用来指定从右向左提取的字符串长度(忽略时取1)，例如“=RIGHT(“电脑爱好者”，3)”的计算结果为“爱好者”。
2.计算公式
由于YEAR和RIGHT函数的特点，它们组成的公式只能计算“实工龄”，具体形式为“=RIGHT(YEAR(A1-B1)，2)”。公式中的A1和B1分别存放工龄的截止和起始日期(正好与DAYS360函数相反)，“YEAR(A1-B1)”计算出两个日期间的年份数，RIGHT(YEAR(A1-B1)，2)则通过自右向左提取年份的后两位(工龄一般都是一位或两位数)。计算出来的一位数工龄前有个“0”，与习惯不太相符，这是上述公式的缺点。
三、N和INT函数
1.函数简介
①N函数
N函数属于信息函数之列，它可以完成单元格对象的转换。就是将数值转换成数字，日期转换成序列值，TRUE转换成1，其它对象转换成0。其语法为：N(value)，其中的value是待转换的单元格对象，它可以是数值、日期等数据，也可以是一个表达式。
②INT函数
该函数可以将一个数值向下取整为最接近的整数。语法为：INT(number)，其中number是待取整的一个实数或表达式，用它将计算结果取整为“实工龄”非常方便。
2.计算公式
由于INT函数的固有特点，用N和INT函数构成的公式只能计算“实工龄”，具体形式为“=INT((N(A1-B1))/365)”。公式中的A1和B1分别存放工龄的截止和起始日期，“N(A1-B1)”计算工龄的序列数(其实就是工龄的天数)，(N(A1-B1))/365将N(A1-B1)的计算结果转换为工龄，由于工龄数一般是小数，于是通过INT函数将它向下取最接近的整数。
老师们最头疼的事是什么？当然是录入学生成绩啦，虽然现在一直在“减负”，但必要的报表还是少不了的，要是只有一个班级还好，假如你有3个班甚至5个班，那成绩的输入可不是件容易的事，既麻烦又容易出错，今天，笔者就结合成绩输入的特点，介绍几个利用Excel
XP提高输入效率的技巧：
1.中英文输入自动切换
我们通常是在Excel XP中(本文介绍的技巧在Excel
97中也同样适用)输入学生成绩，当然绝大部分时候都是输入数字，不过有的时候也会输入一些汉字，如考查科目的成绩一般是“优秀/良好/合格/不合格”四个等级，操作等级的考核一般是“优秀/良好/达标/暂未达标”，这些都是汉字。因此，当你在输入不同类型的数据时，就不得不经常变换输入法，一会儿是英文、一会儿是中文，非常麻烦。
实际上，我们可以利用Excel
XP实现在不同单元格中进行输入法的自动切换。首先选择需要输入汉字的单元格区域，然后从“数据”菜单中执行“有效性”命令，在“数据有效性”窗口中选择“输入法模式”标签页，在“模式”下拉列表框中选定“打开”模式(如图图1所示)，然后点击“确定”按钮。接着，再选择需要输入数字的单元格区域，同样打开“数据有效性”窗口的“输入法模式”标签页，不过此时是需要选择“关闭(英文模式)”模式，点击“确定”按钮结束设置工作。
如此设置后，当插入点处于不同的单元格时，Excel
XP就会根据你的设置情况在中文/英文输入法之间进行自动切换，这里自动选择的中文输入法取决于你在“控制面板/输入法”中的设置。
2.自动填充快速输入重复信息
输入学生成绩经常会有一些重复性或有规律性的工作，如学生的学号等，此时就可以利用Excel XP的自动填充功能来完成。
首先在第一个单元格中输入起始数据如2000001，再在第二个单元格中输入第二个数据如2000002，选定这两个单元格，将鼠标移到选定区域的右下角，会看到一个细十字的光标，它就是我们常说的“填充句柄”，此时你只要沿着要填充的方向拖动填充句柄到最后的单元格即可，此时拖过的单元格区域会按照Excel
XP规定的序列号进行自动填充。假如你是用鼠标右键来拖动填充句柄的话，那么会有更多的填充选择。
3.通过下拉列表快速输入数据
虽然我们可以通过复制、自动填充的方法来快速输入数据，但有时使用起来并不是很方便，要是能像网站上的填表页一样通过下拉列表来选择数据就好了。其实，假如需要选择的数据不是很多时(如前面讲到的“优秀/良好/达标/暂未达标”)，Excel
XP就可以实现这一功能。
先选取需要设置下拉列表的单元格区域，再从“数据”菜单中选择“有效性”命令，仍旧打开“数据有效性”窗口，不过注意的是这时必须选择“设置”标签页，在“允许”下拉列表框中选择“序列”项，再在“来源”文本内输入欲设置的数据序列，如“优秀，良好，达标，暂未达标”，请一定要选择“提供下拉箭头”复选框(图图2)，最后点击“确定”按钮就行了。以后当你在该区域输入数据时，就可以单击单元格右侧的下拉箭头以选择相应数据，这样可以大大加快输入速度。
4.自动检查错误
笔者这里说的不是让Excel XP自动对单元格进行拼写检查，而是指让Excel XP自动监测所输入的所有数据。
自动提示输入信息：在选定相应单元格区域后，打开“数据有效性”窗口，选择“输入信息”标签页，选定“选定单元格时显示输入信息”复选框，在“标题”文本框内输入一个比较醒目的标题如“警告”，再在“输入信息”文本框内输入如“这里应输入优秀/良好/达标/暂未达标”的信息，最后点击“确定”按钮。以后只要你的光标移到这些单元格区域中，Excel
XP就会自动显示上述提示信息，就像插入的批注一样(图图3)。
自动检查数据的有效性：我们可以通过设置让Excel
XP自动检查所输入数据的有效性，这样就可以让你及时发现错误。方法是先选定相应单元格区域，从“数据有效性”窗口中选择“设置”标签页，在“允许”下拉列表框中选择相应的数据类型，这里有任何值、整数、小数、序列、日期、时间、文本长度等供你选择，假如实在找不到你所需要的类型，可以选择“自定义”来指定一个特定的公式，如“优秀，良好，达标，暂未达标”，如果允许单元格数据为空，就应该选中“忽略空值”复选框，反之则不选。如此设置后，一旦你在上述区域输入与设置不符的数据如“88”等，就会出现“输入值非法：其他用户已经限定了可以输入该单元格的数值”信息，点击“重试”按钮就可以重新输入。
自动发出“出错警告”：在对相应单元格区域设定了有效性检查条件后，我们还可以设置“出错警告”信息，以后假如在所设置的单元格区域输入与条件不符的数据，Excel
XP就会自动发出“出错警告”信息，让你及时发现输入错误。只要从图1中选择“出错警告”标签页，选定“输入无效数据时显示出错警告”复选框，再在“警告”下拉列表框中选择一个样式(如警告、中止、信息，我们这里选择“警告”样式)，在“标题”文本框中输入“警告”，在“出错信息”文本框中输入“这里不能输入数字”，最后单击“确定”按钮即可。输入数字后出现提示信息，点击“否”可修改数据，点击“是”可以忽略错误继续输入，点击“取消”可重新输入。
5.让标题行或标题列始终显示
有时当学生人数或考试科目很多时，就会出现跨多个页面的工作表，此时作为表格重要项目的顶端标题行或左端标题列只能在表格的第1页中可以看到，进入第2页以后恐怕就无法看到了(当然假如你是以比较小的比例来显示的话另当别论)，这样输入数据时很不方便，也非常容易发生错位现象。怎么办呢？
从“文件”菜单中选择“页面设置”命令，选中“工作表”标签页，假如你想将工作表中的某行指定为重复使用的标题，可以单击“顶端标题行”编辑框右下角的“折叠对话框”按钮；假如想将某列指定为重复使用的标题，可以单击“左端标题列”编辑框右下角的“折叠对话框”按钮(图图4)，然后在欲指定为重复使用的标题行或标题列中单击任一单元格，当然也可以同时选定多行或多列作为标题，最后单击“确定”就可以了，假如你觉得不放心，可以点击“打印预览”按钮来看一看是否正确。
作为一名教师，每次考试后都要统计成绩，十分辛苦。在计算机日益普及的今天，我们可以利用它来统计、管理学生的成绩，使教师们从繁重的劳动中解脱出来。
电子表格软件Excel
2000不仅可以制作表格，强大的功能还可以作为一个小型数据库来管理一些数据。下面就以班级各科汇兑成绩统计为例来说明如何使用Excel
2000来方便管理学生成绩。
首先，我们应该建立成绩统计模板。
一、输入统计项目
运行Excel
2000，在该表第一行中输入以下内容：学号、姓名、各考试科目名称(语文、数学、英语等)、总分、名次，然后在对应列中录入学生的学号、姓名。
输入学号时，由于学号是连续的，我们可采用自动填充的功能，如：先输入第一、二个学生的学号201、202，然后选定这两个单元格，将光标移到选定单元格的右下角，光标变为细十字，按住鼠标左键向下拖拉，至所有学生学号出现。如图1。(图)
在学号下方依次输入“均分”、“优分人数”、“及格人数”、“最高分”，用于统计、分析成绩。在“姓名”对应列中输入所有学生姓名。
二、用条件格式来标出各分数段内成绩
选定所有录入学生成绩的单元格，单击菜单栏中“格式”按钮，在下拉菜单中执行“条件格式”命令。然后在“单元格数值”范围中填入“80”与“100”，单击“格式”按钮，在出现的对话框(图2)(图)中将单元格数值设置为你喜欢的格式：如数值颜色为蓝色、字形为倾斜等，这样，在80到100之间的分数在输入后自动变为蓝色并倾斜。单击“确定”，返回条件格式对话框，单击“添加”，在单元格数值范围中输入“60”到“79”，单击“格式”按钮，将分数在60到79之间的又设置为你喜欢的格式。其它可类推。
三、利用求和公式计算每位学生的总分
单击总分下的单元格，在编辑栏中输入=SUM(C2:H2)，在下一个单元格中输入=SUM(C3:H3)，然后选定这两个单元格，将光标移动到两个单元格的右下角，到变成细十字，按住鼠标左键向下拖拉至最后一名学生，利用自动填充的功能向每名学生对应的总分栏中输入求和公式，以后只要你输入学生各科成绩，每位学生的总分即自动求出。
四、利用RANK函数给学生的总成绩排名次
在Excel
2000中可以用单元格自动排序的方式给成绩排序，但不好处理同分数的名次，利用RANK函数则没有这样的问题。其方法是：总分位于I列，名次位于J列，在第一名学生的名次单元格中输入=RANK(I2,$I,$2:$I$55)，该公式的目的是排出I2单元格的数据从I2到I55所有数据中的位次(假设该班共54名学生，学生的姓名，成绩等数据从第2行排到第55行)，在第二名学生的名次单元格中输入=RANK(I3,$I$2:$I$55)，然后拖动鼠标，选中这两个单元格，利用自动填充的功能，在每个学生的名次单元格中输入RANK函数的公式。这样你只要将学生各科成绩输入到对应的单元格中后，RANK函数即自动求出该学生的名次，同分数的名次则自动排为相同的名次，下一个名次数值自动空出。
五、利用AVERGE函数计算各科平均分
在语文平均分单元格中输入公式=AVERAGE(C2:C55)，在数学平均分单元格中输入公式=AVERAGE(D2:D55)，拖动鼠标，选中这两个单元格，利用自动填充的功能，在每科均分的单元格中输入对应的公式。这样，将每科的成绩输入完了后，该科的平均分即可自动求出。
六、利用COUNTIF函数统计每科各分数段人数
在管理学生成绩时，老师常常要将优分人数和不及格人数统计出来，有了COUNTIF函数，就不必要计数了。使用方法是：在语文优分人数的单元格中输入公式=COUNTIF(C2:C55,“>=80”)(假设各科的满分为100分)，在数学优分人数的单元格中输入公式=COUNTIF(D2:D55,“>=80”)，选中这两个单元格，利用自动填充的功能将其它各科相应的公式输入。在各科及格人数单元格中只要相应地将公式改为>=60，不及格人数改为<=59即可。这样Excel
2000就自动统计出相应各分数段人数。
七、利用MAX函数找出最高分
选中语文最高分的单元格，输入公式=MAX(C2:C55)，在数学单元格中输入公式=MAX(D2:D55)，利用自动填充的功能将其它各科相应的求最高分的公式输入，在各科成绩输入结束后，Excel
2000可自动找出最高分。
八、保存为模板文件
按下常用工具栏中的“打印预览”按钮，进入打印预览，按下“设置”，出现如下对话框(图3)(图)，单击工作表标签，选中打印风格线，单击页眉/页脚标签，进入设置对话框，单击“自定义”，设置你喜欢的表格标题、页眉、页脚，然后设置纸张的大小，页边距，根据打印预览来调整字体、字号、单元格的宽度及高度。最后执行“文件”菜单“另存为”命令，在对话框的“保存类型”中下拉出“模板”，在文件名中输入如“9902成绩统计”，单击“确定”，保存为模板文件。
最后，我们就可以使用已建立的模板来处理学生的成绩。启动Excel
2000，执行“文件”菜单中的“新建”，在对话框中出现的“常用”标签下找到已建立的“9902成绩管理”模板，双击，出现了原来的模板文件，输入学生新的成绩，其它的如总分、各科的平均分、各分数段的人数，最高分、各学生的总分名次即自动统计得出。最后打印输出，既美观又便于分析。
在输入Excel
2000的函数公式时，也可利用“插入”菜单下的“函数”命令来输入相应的函数公式，因为成绩统计常用的函数在上面已提到，这里就不再多说。
谈到统计分析，不少人立即想起SAS，的确，SAS是一个非常完美的统计分析软件，但是学会SAS并不容易。其实，用EXCEL就可以完成诸如t检验，F检验，方差分析等工作。既简单又省时，何乐而不为呢！下面就以配对资料的比较所用到的t检验为例来说明如何用EXCEL做医学统计。
图
例：用某药物治疗9例高血压病人，治疗前后舒张压(单位：kp)如表所示，试问用药前后舒张压有无变化？
一、准备工作
1． 数据录入
将以上表格数据录入到EXCEL中并保存为data．xls。
2． 在EXCEL中引入统计分析工具。
选择“工具→加载宏”，出现加载宏对话框，用鼠标选中“分析工具库”，再单击“确定”。此时“工具”菜单最后多了一项“数据分析…”，到此为止准备工作已经就绪。
二、统计
1． 选择“工具→数据分析”，出现数据分析对话框。
2． 选中“t检验：平均值的成对二样本分析”，单击“确定”。出现t检验对话框。
3．
当光标在变量1的区域时，用鼠标选中“治疗前”单元格，按住Shift键，再用鼠标选中9号病人治疗前舒张压水平。同理，将光标移至变量2的区域后，选中治疗后的血压。
4． “假设平均差”填上“0”。
5． 选中“标志”复选框，意即所选范围第一行作为变量名的标志。本例两个变量名分别为“治疗前”、“治疗后”。
6．α值取默认的0．05。
7． 输出选项有“输出区域”、“新工作表组”、及“新工作簿”三个选项，这里采用默认的新“工作表组”，最终对话框如图。
8． 单击“确定”按钮，就会在新的工作表组中出现结果。
本例结论：由上表数据，平均值：治疗前＞治疗后，P=0．003082＜0．05。故治疗前后舒张压在α=0．05水平上有显著的统计学差异，可以认为该药有降压效果。
图
说明：其他统计分析过程与此类似，请大家举一反三，必将有所受益。
在EXCEL 2000中快速输入特定文本 在EXCEL 2000中快速输入特定文本
陈秀峰
因工作的需要，可能你会经常用Excel
2000来处理大量的表格，常常要输入一些特定的文本(如标准规范的名称、施工企业的名称等)，为了准确、统一、快速地输入这些特定文本，这次向大家介绍几个好方法。
1.自动更正法
用“工具→自动更正”命令，打开“自动更正”对话框，在“替换”下面填入“G300”，在“替换为”下面填入“《建筑安装工程质量检验评定统一标准》[GBJ300-88]”(不含双引号)，然后按“确定”按钮。以后只要在单元格中输入“G300”及后续文本(按“Enter”键)后，系统会自动将其更正为“《建筑安装工程质量检验评定统一标准》[GBJ300-88]”。
友情提醒：①你可以依照上述方法将有关文本一条一条定义好，方便以后使用。②在Excel中定义好的自动更正词条在Office系列其他应用程序(如WORD)中同样可以使用。③如果确实需要输入文本“G300”时，你可以先输入“G3000”及后面的文本，然后删除一个“0”即可。
2.函数合并法
经常需要输入一些施工企业的名称，如“某某市第九建筑安装工程公司”、“某某市华夏建筑安装工程公司”等，这些文本大同小异，我通常用
Excel中的Concatenate函数来实现这些特定文本的输入：
假定上述文本需要输入某一工作簿的Sheet1工作表的D列中(如D2单元格)，我们先在Sheet2工作表中的两个单元格(如A1和B1)中分别输入文本“某某市”和“建筑安装工程公司”，然后在Sheet1的D2单元格中输入公式：=CONCATENATE(^35050601a^)，以后我们只要在C2单元格中输入“华夏”，则D2单元格中将自动填入文本“某某市华夏建筑安装工程公司”。
友情提醒：这样做工作表中就会多出一列(C列)，我们在打印时不希望将它打印出来，我们选中该列后右击鼠标，在随后弹出的菜单中选“隐藏”项，即可将该列隐藏起来而不被打印出来。
3.选择列表法
如果需要输入的文本在同一列中前面已经输入过，且该列中没有空单元格，右击下面的单元格，选“选择列表”选项，则上面输入过的文本以下拉菜单形式出现，用左键选中你需要的文本，即可将其快速输入到选定的单元格中。
4.格式定义法
有时我们需要给输入的数值加上单位(如“万元”等)，少量的我们可以直接输入，而大量的如果一个一个地输入就显得太慢了。我们通过“自定义”单元格格式的方法来达到自动给数值添加单位的目的。
选中需要添加单位的单元格，用“格式→单元格”命令，打开“单元格格式”对话框，在“数字”卡片中，选中“分类”下面的“自定义”选项，再在“类型”下面的方框中输入“#平方米”，按下“确定”按钮，以后在上述单元格中输入数值(仅限于数值！)后，单元(万元)会自动加在数值的后面。
随着电脑的普及应用，每一台电脑都可以灵活方便地安装使用Excel软件。而除财会部门外，其他部门的电脑都去装上专门的财务、商务软件肯定是不现实的。在日常的业务核算、统计核算和会计核算三大核算工作中，相当多的工作人员还在依赖手工方式。面对在手工下的各种大量而又十分繁琐的费时费力又费神的数据查找、登记、计算、汇总工作，除利用财务、商务软件解决外，利用Excel去解决这些问题，其实是一件十分方便且轻松的事情。本文以任务驱动的方式，引导读者在完成任务的过程中掌握相应的操作技能。
目标任务
在一个工作簿中分别制作一个“进货”工作表、一个“销售”工作表和一个“进销存自动统计”工作表，设置好相应的公式和条件格式。
每当发生进货或销售业务而在“进货”工作表或在“销售”工作表中输入进货业务或销售业务数据时，“进销存自动统计”表中便自动计算出每一种商品的当前总进货量、当前总销售量和当前库存量。
当库存量超过或低于规定的“报警线”时，能进行特殊显示，以示警告。
操作要点及注意事项
Excel表格的制作，Excel工作表函数公式的运用，条件格式的运用。
本文所述操作在Excel 97和Excel 2000下运行测试通过。
除汉字外，Excel公式中的所有字符，都必须在英文(En)状态下输入。
方法与步骤
(一)新建工作簿
1．单击“开始”菜单，在弹出的开始菜单项中单击“新建office文档”，出现“新建office文档”对话框窗口。
2．“新建office文档”对话框窗口中的“常用”活页夹中，双击“空工作簿”，出现名为“Book1”的空工作簿。
3．将“Book1”保存为“进销存自动统计系统．xls”。
(二)定义工作表名称及数据
1．双击“Sheet1”工作表标签，输入“进货”后按【Enter】键。
2．双击“Sheet2”工作表标签，输入“销售”后按【Enter】键。
3．双击“Sheet3”工作表标签，输入“进销存自动统计”后按【Enter】键。
4．选择“进货”工作表，输入标题(进货日期、商品名称、进货数量)和相应各项数据。
限于篇幅，以及仅为说明问题起见，这里只列举甲、乙、丙三种商品(表1图)。
5．选择“销售”工作表，输入标题(销售日期、销售去向、商品名称、销售数量)和相应各项数据(表2图)。
6．选择“进销存自动统计”工作表，在第一行中分别输入标题内容：商品名称、当前总进货量、当前总销售量、当前库存量(表3图)。
(三)定义公式
1．在“进销存自动统计”工作表中选择B2单元格，输入“=SUMIF(进货！B：B，"甲"，进货！C：C)”，按【Enter】键。
2．向下拖动B2单元格右下方的黑点至B4单元格，进行公式复制的操作。
3．选择B3单元格，按F2键，修改公式中的“甲”为“乙”，同样，修改B4单元格公式中的“甲”为“丙”。如果有更多的商品，依此类推，直至修改完毕为止。注意，从公式定义可以看出，此例中的单元格相加求和的条件依据是商品名称：甲、乙、丙。
4．选定B2至B4单元格，向右拖动B4单元格右下方的黑点至C列，进行公式的复制操作。
5．选择C2单元格，按F2键，将公式中的“进货”修改为“销售”，同样，再分别修改C3、C4单元格公式中的“进货”为“销售”。如果有更多的单元格需要定义公式，依此类推，直至修改完毕为止。
6．选定D2单元格，输入“=B2-C2”，按【Enter】键。
7．向下拖动D2单元格右下方的黑点至D4单元格(如果有更多的，一直向下拖动到最后一个单元格即可)，完成公式的复制工作。
(四)库存报警(字符突出显示)设置
1．单击D列的列标，然后选择“格式”菜单中的“条件格式”命令。
2．在打开的“条件格式”对话框中，在“条件1”区域中进行最高库存量报警的突出显示设置：
首先，从左到右，分别选定“单元格数值”(Excel97中是“单元格数值为”)、“大于或等于”，并输入一个合适的最高库存量报警线数字。
然后，单击“格式”按钮，在打开的对话框中设置颜色为“红色”，字形为“加粗”。
最后按“确定”按钮，完成库存一旦超高即报警的突出显示设置。
3．在“条件格式”对话框中，单击“添加”按钮，随即便会增加一个“条件2”区域。
在“条件2”区域中进行最低库存量报警的突出显示设置：
首先，从左到右，分别选定“单元格数值”、“小于或等于”，并输入一个合适的最低库存量报警线数字(比如，输入1，表示当库存只剩一件或没有时，突出警示)。
然后单击“格式”按钮，再在打开的对话框中设置颜色为“蓝色”，字形为“加粗”。
最后按“确定”按钮，即完成库存超低的报警突出显示设置。
(五)日常应用
1．平时，每次只要在“进货”工作表和“销售”工作表中输入实际发生的进货或销售数据，“进销存自动统计”表中便会自动得到当前的总进货量、当前的总销售量以及当前库存量。同时，当库存量超过或低于报警线数字时，就会以红色或蓝色并加粗字符来突出显示。
2．购入“进货”工作表中没有的新货时，需要按照上面所述方法在“进货”工作表和“进销存自动统计”工作表中增设相应的商品名称及其取数公式，公式设置还是按照前面所描述的方法，采取复制加修改的方法最快捷。
结束语
本文提供和介绍了利用Excel实现有关进销存业务自动统计的一种基本思路和基本做法，其中重点是公式和条件格式的运用。至于商品进销存业务中的“商品编号”、“业务摘要”
、“单价”、“金额”以及“备注”等，可根据各自需要在工作表中进行相应设置；也可以对举例中的数据项标题名称进行更改；还可以对公式中单元格相加求和的条件依据进行更改，比如，“商品名称”变为“商品编号”。
在校园活动中，经常举办各种类型的大奖赛，常常会看到这样的情形，台上参赛选手的比赛紧张进行，台下负责统分的众多工作人员则忙得不可开交，手中的计算器按个不停，即使这样，还非常容易出错。其实我们只要利用Excel
2000中的RANK函数和MAX、MIN函数就可使这项工作变得非常轻松。
1.如图(图)，利用Excel 2000制作一空白评分表，将参赛选手的情况预先录入，设置好页面。
2.按照制定好的评分规则设计计算公式。在这里，我们假设此次大奖赛的评委为10名，评分规则为10名评委的评分中去掉一个最高分，去掉一个最低分，得出参赛选手的总得分，然后除以8，得出参赛选手的最后得分，根据参赛选手的最后得分由高到低来计算名次。
(1)单击N4单元格，录入公式SUM(D4:M4)-MAX(D4:M4)-MIN(D4:M4)。该公式的含义是先计算出全部10个评委的总评分(SUM(D4:M4))，然后减去10个评委中的最高分(MAX(D4:M4))和最低分(MIN(D4:M4))。
(2)单击O4单元格，录入公式N4/8，计算选手的最后得分。
(3)单击P4单元格，录入公式RANK(O4,OS4:OS8)。该公式的含义是根据各位选手的最后得分来由高到低计算名次。在这里我们用到关键函数为RANK函数，该函数的作用是返回一个数值在一组数值中的排位。它的语法为：RANK(number,ref,order)
Number 为需要找到排位的数字。
Ref 为包含一组数字的数组或引用。Ref 中的非数值型参数将被忽略。
Order 为一数字，指明排位的方式。如果 order 为 0 或省略，Excel将ref当作按降序排列的数据清单进行排位。如果 order
不为零，Excel 将ref当作按升序排列的数据清单进行排位。
(4)最后使用自动填充柄在N、O、P列按需要填充。
在使用中我们只需要在各位评委名下录入他们的打分，各位选手的得分情况和名次就自动计算出来，当比赛结束时，我们只要在P列使用自动排序功能，所有选手的名次就全部排好了，非常方便。
许多单位都用Excel来进行工资管理，用它可以轻松自如地应付各种不同变化，给单位会计人员带来了极大的方便，但是当单位的出纳员根据工资表发放工资时，常常因为各种票面的人民币搭配不当而带来麻烦，其实利用Excel可以在生成工资表的同时，计算出需要多少不同票面的人民币。
首先，我们可以在工资管理工作簿中再新建两个工作表S1和S2，用S1来存放票面计算过程，用S2来存放票面计算的结果。
S1表中的A列存放每个职工的实发工资(可以通过表间公式自动取得)，利用函数MOD将实发工资除以100元到1分13种票面，进行13次取余分解，将每次取余结果分别存放在B列到N列。例如，A2单元格的实发工资为875．46元，则B2=ROUND(MOD(A2,100),2)，结果为75．46；C2=ROUND(MOD(B2,50),20)，结果为25．46；D2=ROUND(MOD(C2,20),2)，结果为5．46……依次类推。第一行各列公式输入完以后，其他各行可以通过拖动填充柄进行复制(图1)(图)。
为了便于观察，S2表的A列也存放每个职工的实发工资，可以通过表间公式自动取得，即A2='
s1'!A2。接下来利用IF语句，对S1中B到N列的结果与100元到1分13个票面进行比较判断，计算出各种面值人民币的张数，计算结果分别存放在S2表的B到N列。接前例，S2表中B2=IF('
s1'!A2＜100,0,(' s1'!A2-' s1'!B2)/100)，结果为8张；C2=IF(' s1'!B2＜50,0,('
s1'!B2-' s1'!C2)/50)，结果为1张；……，N2=IF(' s1'!M2＜0．01,0,(' s1'!M2-'
s1'!N2)/0．01)，结果为1张。其他各行的公式同样可以通过拖动填充柄的方法进行复制(图2)(图)。
最后将计算结果进行合计，得出各种票面人民币的张数，打印出来交给出纳员到银行提取现金。
目前，彩色喷墨打印机已成为企业和家庭的打印主流，其靓丽的色彩，细腻的表现力令人喜爱不已。笔者拥有一台CANON
BJC-6200彩喷，为彩色图片的打印发挥了不小的作用，但是，这款打印机最大只能打印A4幅面，并且没有无边界分割打印功能，不能制作大幅面的彩色挂图，工作中受到局限。我通过反复实践，利用Excel97中的打印功能，实现了CANON
BJC-6200打印大幅面彩色拼图。现在介绍给大家，供拥有不能分割打印拼图的打印机的朋友们参考。
1.用Photoshop把需要打印的图片做好，存储为JPEG图片格式或其他Excel97能调用的图片格式。
2.打开Excel97的主界面，建立一个新的文件簿或工作表，将其显示比例设定为25%后用鼠标点击打印预览按钮，然后，根据界面提示点击确定，关闭后即调出页面分割线，在主菜单中用鼠标点击“文件→页面设置”根据界面提示将打印页面设置为A4幅面(横排、竖排根据图片而定)。
3.在主菜单中用鼠标点击“插入→图片→来自文件(或艺术字)”，根据弹出的界面要求将存储的图片插入工作表。
4.激活插入的图片框，根据页面分割线调整好图片框的位置后，用鼠标按住图片框右下角的空心小方块，拉动调节画面的大小即可。画面覆盖的面积即为打印时分割出的A4幅面的页数。通过打印预览可以观察到图片分割的最终结果。
5.调整打印机的设置后，打印A4幅面多页的彩色拼图，切除多余的纸边，即可拼接出一幅绚丽多彩的大型挂图或艺术文字了。
一、功能需求分析
项目概算汇总表主要涉及项目数量、概算价值、占总投资百分比及建筑技术经济指标等。我们根据有关规范及报批要求建立如下表格：(图)
不同工程的项目概算汇总表，列数固定，行数则随子项的多少而不同。最后一行为编制单位与姓名。
项目的概算价值随着项目数量及经济指标的不同而变化，横向每一子项的概算价值需要合计，并且要计算出占总投资的百分比，纵向每一部分需要合计，以便直观明了，合理利用资金。
二、功能实现
(一)建立计算公式
根据功能需求分析，我们需建立下列公式。
1.概算价值(土建)=建筑技术经济指标(其中土建)×数量。即在F8格输入“=P8*E8”，把公式复制到对应的单元格。
2.合计(概算价值)=建筑工程(含土建、水、暖)+工艺设备+强电+弱电+其他。即在L7格用常用工具栏上的“自动求和”按钮输入“=SUM(F7：K7)”，再将公式从L8纵向复制到L26。
3.第二部分其他费用，每项已给出占第一部分合计(L26)的百分比，因此直接输入公式就行了，即L28=L26*0.008，下面各单元格按照D列给出的比例相应输入。
4.各小部分纵向求和。E7=SUM(E8：E12)，从F7横向复制到M7。同样，E13=SUM(E14：E18)，从F13横向复制到M13。L19=SUM(L20：L25)，复制到M19。
5.
第一、二部分求和。L26=SUM(L7，L13，L19)，并复制到M26。L35=SUM(L28：L34)，并复制到M35。最后求总和，L36=SUM(L26，L35)，并复制到M36。
6. 各项占总投资百分比=各项的合计(L列)/总投资(L36) ×100。即在M8格输入“=L8/L36*100”，在其他单元格对应输入。
我们在复制公式时，可利用填充柄。
(二)格式化工作表
适当地将工作表显示的格式作一些灵活变换，这不但可以增加对用户的吸引力，而且可以使工作表看起来美观、容易阅读。
1.增加边框
用户在运用Excel制作表格时，如果不自己设置边框线，那么在屏幕上见到的报表样式，实质上是没有表格线的，所以必须增加数据的边框线或表格线。
可先移动鼠标指针到A3单元格，单击该单元，以此为起始点选取增加表格线的活动单元格范围，拖动A3单元到终点R36单元，移动鼠标指针至菜单条，并单击“格式”选项，在其下拉菜单选项中选取“单元格”，单击该选项后，在单元格格式对话框中设置边框线即可。另外，在选取了设定范围后，也可通过使用“格式工具栏”上的“边框”命令按钮或右键弹出式菜单完成。
给数据增加上边框线后，可以明确区分工作表中的各个范围。为了突出显示重点数据，我们还可以为数据添加颜色，使某些数据以阴影方式显示出来。选定第26行，即从A26单元到R26单元，按鼠标右键，在弹出式菜单设置单元格格式，选图案选项，选较淡的图案即可，然后用格式刷，把这一行的图案设置刷到第35、36行。
2.设置字体
为了突出显示每一部分的合计数据，使工作表更容易阅读，可使用字体加粗命令。选定A7单元到M7单元，单击格式工具是栏上的“B”加粗命令按钮即可，然后用格式刷，把一行的加粗字体设置刷到第13、19、26、35、36等行的相应位置。
把概算价值及占总投资百分比的数据小数点后保留两位有效数字，可以使数据整齐、美观，易于阅读。选定F7单元到M36单元，点击格式工具栏上的增加或减少小数为即可。
3.设置其他格式并准备打印
由于我们要用A4纸进行打印，可进行页面设置，选择A4横向纸，进行打印预览。我们发现工作表被分成了若干页，这是由于字号及行和列较大造成的。我们应对其适当调整以便能正好打印1页或2页，当然有时也需要配合调整页边距。选定A2单元到R37单元，在格式工具栏上的字号大小框里输入“9”，在选住的同时按鼠标右键，在弹出菜单里选“行高”，在行高里输入“15”。列的宽窄宜用手工拖动，例如调整C列宽度，先移动鼠标指针至C列和D列交界处，并单击之，此时指针改变成一个带双向箭头的指针，点住该处指针不放，向右或向左拖动即可。
三、使用工作表
根据前面的功能需求分析，我们知道，对于不同工程项目的概算汇总表，纵向列数不变，变化的只是横向子项多少。我们只要建立好一个工作表后，其它项目的概算汇总表，只是增加或删除行的问题。由于公式是相对引用，删除行后，工作表里的公式相对变动，不需作任何改动就可使用。而增加工程子项，插入行后，相应的公式也可随时变动，需增加该项的公式并修改其所属部分的公式。
当这个系统建立好后，我们只需输入单个项目的数量，概算价值里的水、暖、工艺设备、强电、弱电、其他及建筑技术经济指标里的单位土建造价，概算价值里的土建、每一子项及每一部分的纵向、横向合计及占总投资的百分比就会自动计算出来，不需人工计算及输入，可大大减轻我们的工作量，同时，也提高了工作表的正确性。
|
