汇编集锦.docx
- 文档编号:23372232
- 上传时间:2023-05-16
- 格式:DOCX
- 页数:72
- 大小:1.95MB
汇编集锦.docx
《汇编集锦.docx》由会员分享,可在线阅读,更多相关《汇编集锦.docx(72页珍藏版)》请在冰豆网上搜索。
汇编集锦
Excel2007巧妙统计混合编班分数段
期末考试之后,自然就是成绩的统计和分析了。
为了使成绩的录入和分析更为公平,我们学校都是由专人统一录入的。
平行班级的成绩都录入在Excel的同一工作表中。
但是这样做之后,也为我们后期的成绩分析制造了麻烦。
由于工作表中混合编班,那么在统计各班各学科各分数段的人数时,就不得不进行多次按班、按学科进行排序,然后人工数出相应分数段人数,非常麻烦。
其实,如果我们是用Excel2007进行相应的工作的话,那么就可以利用新增的COUNTIFS函数来完成这项任务,非常方便。
一、1班语文学科分数段统计
图1学生成绩表
图1所示为一次考试的学生成绩表。
在不对成绩进行排序等操作的前提下,我们将完成图2所示分数段统计表。
图2Excel2007绘制表格
具体过程如下:
以1班语文学科的分数段统计为例。
将鼠标定位于统计表中的N3单元格,在编辑栏输入如下公式“=COUNTIFS($A$2:
$A$108,$L3,$C$2:
$C$108,"100")”,按下回车键,则可以得到1班语文成绩为100分的学生人数。
还是先说说这个COUNTIFS函数吧。
COUNTIFS函数的作用是统计某个区域中满足多重条件的单元格个数。
其语法格式是:
COUNTIFS(range1,criteria1,range2,criteria2…)。
其中Range1,range2,……是指对哪些区域在统计时施加条件。
Criteria1,criteria2,…则是指具体施加的条件是什么。
比如上例的公式的含义则是统计A2:
A108单元格区域中等于L3单元格(即1班)且C2:
C108单元格区域中等于100分的单元格个数。
COUNTIFS所支持的施加条件个数为127个,足够我们使用的。
因此,统计1班语文分数在90分至100分之间的人数时,我们只需要再增加一个条件。
在O3单元格输入如下公式:
“=COUNTIFS($A$2:
$A$108,$L3,$C$2:
$C$108,">=90",$C$2:
$C$108,"<100")”即可。
拖动该单元格的填充句柄向右复制公式,至S3单元格。
然后将公式中的分数作出相应的调整,那么就会迅速得到各分数段的相应人数了。
至于最后的那个50分以下的人数统计,很显然,其公式应该是“=COUNTIFS($A$2:
$A$108,$L3,$C$2:
$C$108,"<50")”。
二、其它学科及班级分数段统计
选中N3:
T3单元格区域,向下拖动填充句柄至T7,则可以将1班的分数段表格迅速填充完成。
但是,现在每一行所填充的数据,都是语文学科相应的分数段人数。
我们还需要将公式中的“$C”(语文成绩所在列)改成各学科相应的列号。
不过,这个工作也不是太麻烦。
以数学科为例。
只要选中N4:
T4单元格区域,然后按下“Ctrl+F”快捷键,打开“查找和替换”对话框。
点击“替换”选项卡,在“查找内容”的输入框中输入“$C”,在“替换为”输入框中输入“$D”(数学成绩所在的列号),如图3所示。
然后点击“全部替换”按钮,那么就可以得到数学学科的各分数段了。
图3Excel2007中执行替换
其它几个学科照此办理,将公式中的“$C”分别再替换为“$E”、“$F”、“$G”,很快就可以得到1班各学科的分数段统计。
1班各学科分数段全部统计完成后,其他各班就非常容易了。
只要选中N3:
T7单元格区域,然后按下“Ctrl+C”快捷键进行复制,再将鼠标定位于N8单元格,按下“Ctrl+V”快捷键进行粘贴,那么2班分数段统计立刻完成了。
照此办法,只要复制、粘贴,呵呵,瞬间完成。
所得结果如图4所示。
图4Excel2007完成表格
提醒一下:
L列中的班级为辅助列,是为了我们在公式中设置班级方便而加的,不能删除。
从表格美观的角度出发,可以选中整个L列后,点击右键,在弹出的菜单中点击“隐藏”命令,将其隐藏。
怎样让多个部门同时编辑Excel表格
在公司里,有时一张反映公司综合情况的报表需要多个相关部门同时制作完成。
制作这样一张报表,通常指定一个综合部门将各有关部门主管的数据收集起来后输入一个Excel文件中,然后共享在公司的局域网上供公司领导、部门和职员查询,使用起来十分方便。
可是,由于一些统计数据过细过多,输入的工作量太大了,要耗费很长一段时间;有时整个报表统计出来了,说不定误了领导的决策;而且如果发现有错误输入的情况,只要有一个人浏览,别人是无法打开文件进行编辑修正的。
为了改变一张报表只能由一个人完成的现状,其实我们完全可以通过Excel文件的设置,让相关部门的工作人员在局域网上通过密码确认,同时输入自己的有关数据,既减轻了由专人输入的压力,又节约了大量时间,同时还方便了领导和需求者的使用,下面将方法简介如下:
首先打开一个Excel工作簿,选择菜单栏中的“工具”→“共享工作簿”命令;接着在出现的“共享工作簿”对话框中,选择“编辑”选项卡,然后选中“允许多用户同时编辑......”选项并单击“确定”按钮。
共享工作簿
最后选择菜单中的“文件”→“另存为”命令,将文件共享于局域网中即可。
经过上述处理后,这个Excel工作簿可以同时被256个不同人员进行浏览或编辑
Excel技巧:
快速录数据让单位自动补全
用Excel做表格,经常遇到需要在某一列使用同一单位的情况。
如果先在第一个单元格输入对应单位的名称,然后使用“拖拽”的方法使其它行复制这一单位,达到的效果不尽人意,要么需要再改单位前的数值,要么单位与数值分在两列。
我们通过预先设置某一列的格式可以让这些单位“自动补全”。
这里以将某一列的单位设置为厘米(CM),最终的效果是在该列任一单元格中输入数字后,按下回车键或将光标移至其它单元格,在数字后会自动补齐“CM”。
第一步:
运行Excel,单击选中需要设置格式的列。
右键单击选中的列,在弹出的快捷菜单中选择“设置单元格格式”(如图1)。
第二步:
在打开的“单元格格式”对话框中,在“数字”标签中,选中“自定义”项,在右侧“类型(T):
”下方的文本框中输入“0.00"CM"”(如图2,不含外侧引号),最后单击“确定”按钮。
经过这样设置之后,在该列单元格中输入数据时,不用再考虑单位问题了,直接输入数字即可。
小提示:
其中“0.00”表示该列单元格保留两位小数,小数点后面几个“0”就代表几位小数,具体多少可根据需要自行设置。
如果是其它单位符号,只要更改双引号中的CM为相应的字符即可。
用Excel做数据分析回归分析
我们已经知道在Excel自带的数据库中已有线性拟合工具,但是它还稍显单薄,今天我们来尝试使用较为专业的拟合工具来对此类数据进行处理。
在数据分析中,对于成对成组数据的拟合是经常遇到的,涉及到的任务有线性描述,趋势预测和残差分析等等。
很多专业读者遇见此类问题时往往寻求专业软件,比如在化工中经常用到的Origin和数学中常见的MATLAB等等。
它们虽很专业,但其实使用Excel就完全够用了。
我们已经知道在Excel自带的数据库中已有线性拟合工具,但是它还稍显单薄,今天我们来尝试使用较为专业的拟合工具来对此类数据进行处理。
注:
本功能需要使用Excel扩展功能,如果您的Excel尚未安装数据分析,请依次选择“工具”-“加载宏”,在安装光盘支持下加载“分析数据库”。
加载成功后,可以在“工具”下拉菜单中看到“数据分析”选项
实例某溶液浓度正比对应于色谱仪器中的峰面积,现欲建立不同浓度下对应峰面积的标准曲线以供测试未知样品的实际浓度。
已知8组对应数据,建立标准曲线,并且对此曲线进行评价,给出残差等分析数据。
这是一个很典型的线性拟合问题,手工计算就是采用最小二乘法求出拟合直线的待定参数,同时可以得出R的值,也就是相关系数的大小。
在Excel中,可以采用先绘图再添加趋势线的方法完成前两步的要求。
选择成对的数据列,将它们使用“X、Y散点图”制成散点图。
在数据点上单击右键,选择“添加趋势线”-“线性”,并在选项标签中要求给出公式和相关系数等,可以得到拟合的直线。
由图中可知,拟合的直线是y=15620x+6606.1,R2的值为0.9994。
因为R2>0.99,所以这是一个线性特征非常明显的实验模型,即说明拟合直线能够以大于99.99%地解释、涵盖了实测数据,具有很好的一般性,可以作为标准工作曲线用于其他未知浓度溶液的测量。
为了进一步使用更多的指标来描述这一个模型,我们使用数据分析中的“回归”工具来详细分析这组数据。
在选项卡中显然详细多了,注意选择X、Y对应的数据列。
“常数为零”就是指明该模型是严格的正比例模型,本例确实是这样,因为在浓度为零时相应峰面积肯定为零。
先前得出的回归方程虽然拟合程度相当高,但是在x=0时,仍然有对应的数值,这显然是一个可笑的结论。
所以我们选择“常数为零”。
“回归”工具为我们提供了三张图,分别是残差图、线性拟合图和正态概率图。
重点来看残差图和线性拟合图。
在线性拟合图中可以看到,不但有根据要求生成的数据点,而且还有经过拟和处理的预测数据点,拟合直线的参数会在数据表格中详细显示。
本实例旨在提供更多信息以起到抛砖引玉的作用,由于涉及到过多的专业术语,请各位读者根据实际,在具体使用中另行参考各项参数,此不再对更多细节作进一步解释。
残差图是有关于世纪之与预测值之间差距的图表,如果残差图中的散点在中州上下两侧零乱分布,那么拟合直线就是合理的,否则就需要重新处理。
更多的信息在生成的表格中,详细的参数项目完全可以满足回归分析的各项要求。
下图提供的是拟合直线的得回归分析中方差、标准差等各项信息。
快速从表格中提取数据中的Office宏
方法一:
利用快捷键逐个提取
这个实例的效果是:
点选要提取数据的单元格后,只要按一下Ctrl+d键,数据就自动拷贝到你指定的位置,并且可以连续操作,后拷贝的数据会自动顺延到指定位置的下一单元格中。
Step1
启动Excel新建一个文件,保存为DataCenter.xls。
点击“工具→宏→录制新宏”菜单命令,设置宏名为CopyRange,快捷键设为Ctrl+d(注意:
不要跟系统的快捷键冲突),点击“确定”按钮。
Step2
点击“工具→宏→停止录制”菜单命令。
怎么刚开始录制就要停止呢?
这样做的目的只是要获得一个带有快捷键的宏的框架,具体代码还得自己输入。
按Alt+F11键打开VisualBasic编辑器窗口,双击打开“工程”窗格中的“模块1”,删除最后的“EndSub”语句并加入如下代码(代码和实例,可以直接从上获得):
Selection.Copy'拷贝所选单元格
TheSel=ActiveCell.Address'存储单元格位置
ForI=1To100'设定从H列的第1行开始,到100行停止,H、1、100这三个参数,你可以根据需要改动
IfRange("H"&I)=""Then'如果H列I行为空
Range("H"&I).Select'选择H列I行的单元格
ActiveSheet.Paste'粘贴单元格
Range(TheSel).Select'回到拷贝位置
GoToline1'跳出循环
EndIf
NextI
line1:
EndSub
Step3
关闭VisualBasic编辑器窗口返回Excel。
将其他Excel表格复制到DataCenter.xls中,点选需要提取数据的单元格,按Ctrl+d键,就可以把它拷贝到H列的第1行了,其他数据的提取以此类推。
方法二:
多单元格同时提取
上面的方法一次只能提取一个单元格,在此基础上再增加一个简单的宏,可以达到一次提取多个单元格的效果。
Step1
首先假设如图所示是需要提取数据的表格形式。
录制一个新宏,名称为CopyAll,快捷键设为Ctrl+e,在宏内输入以下代码(这次千万别删除“EndSub”):
Range("B1").Select'选择“姓名”后的数据
CopyRange'调用上一方法中的宏,下同
Range("B2").Select'选择“性别”后的数据
CopyRange
Range("B3").Select'选择“年龄”后的数据
CopyRange
Range函数中的参数,表示你想要提取数据的单元格位置,可以根据需要随意设置。
Step3
返回Excel,在B1、B2、B3单元格中分别输入“张小乐”、“男”、“18”。
按Ctrl+e键,这些人物数据很快就被拷贝到H1~H3单元格中。
以此类推可继续提取其他数据。
上面的方法并不完美,其他表格中的数据必须拷贝到DataCenter.xls后才能进行提取操作,有兴趣的朋友可以研究一下如何跨文件提取数据。
另外,如果拷贝的是Word表格,必须在单元格格式中,将“合并单元格”项取消掉,否则不能成功提取。
Excel2007中批量删除超链接的两种方法
Excel中某列单元格均含有超链接,用手工删除超链接的方法必须一个个进行:
右击该列中任何一个含有超链接的单元格,选择“取消超链接”(图1)。
由于数量巨大,因此只能考虑采用批量删除法。
一、宏代码去除法
打开该Excel文件,切换到“视图”选项卡,点击“宏”→“录制宏”,出现“录制新宏”窗口,在“宏名”定义一个名称为:
RemoveHyperlinks(图2),点击“确定”退出;
再点击“宏”→“查看宏”,选择“宏名”下的“RemoveHyperlinks”并点击“编辑”,打开“MicrosoftVisualBasic”编辑器,用如下内容替换右侧窗口中的所有代码(图3),然后保存关闭VBA编辑器:
SubRemoveHyperlinks()
‘Removeallhyperlinksfromtheactivesheet
ActiveSheet.Hyperlinks.Delete
EndSub
再点击“宏”→“查看宏”,选择“宏名”下的“RemoveHyperlinks”并点击“执行”即可去除该工作表的链接。
用以下代码也可以达到相同的目的:
SubZapHyperlinks()
Cells.Hyperlinks.Delete
EndSub
二、选择性粘贴法
右击含有超链接的列并选择“复制”,接着在该列(左)右边插入一空白列,然后右击该空白列,选择“选择性粘贴”(图4),
在随后出现的“选择性粘贴”窗口中,点选“数值”选项(细心的人会发现,当选择“数值”等选项时,“粘贴链接”按钮就变成灰色不可用状态,图5,自然也就不会进行超链接的粘贴),最后保留该列,再删除原先含有超链接的列即可。
常用的25条Excel技巧整理放送
在网上浏览到一些Excel技巧,在这里整理给读者们看一看,说不定您能用得上。
1、两列数据查找相同值对应的位置
=MATCH(B1,A:
A,0)
2、已知公式得结果
定义名称=EVALUATE(Sheet1!
C1)
已知结果得公式
定义名称=GET.CELL(6,Sheet1!
C1)
3、强制换行
用Alt+Enter
4、超过15位数字输入
这个问题问的人太多了,也收起来吧。
一、单元格设置为文本;二、在输入数字前先输入'
5、如果隐藏了B列,如果让它显示出来?
选中A到C列,点击右键,取消隐藏
选中A到C列,双击选中任一列宽线或改变任一列宽
将鼠标移到到AC列之间,等鼠标变为双竖线时拖动之。
6、EXCEL中行列互换
复制,选择性粘贴,选中转置,确定即可
7、Excel是怎么加密的
(1)、保存时可以的另存为>>右上角的"工具">>常规>>设置
(2)、工具>>选项>>安全性
8、关于COUNTIF
COUNTIF函数只能有一个条件,如大于90,为=COUNTIF(A1:
A10,">=90")
介于80与90之间需用减,为=COUNTIF(A1:
A10,">80")-COUNTIF(A1:
A10,">90")
9、根据身份证号提取出生日期
(1)、=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),IF(LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),"错误身份证号"))
(2)、=TEXT(MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")*1
10、想在SHEET2中完全引用SHEET1输入的数据
工作组,按住Shift或Ctrl键,同时选定Sheet1、Sheet2。
11、一列中不输入重复数字
[数据]--[有效性]--[自定义]--[公式]
输入=COUNTIF(A:
A,A1)=1
如果要查找重复输入的数字
条件格式》公式》=COUNTIF(A:
A,A5)>1》格式选红色
12、直接打开一个电子表格文件的时候打不开
“文件夹选项”-“文件类型”中找到.XLS文件,并在“高级”中确认是否有参数1%,如果没有,请手工加上
13、Excel下拉菜单的实现
[数据]-[有效性]-[序列]
14、10列数据合计成一列
=SUM(OFFSET($A$1,(ROW()-2)*10+1,,10,1))
15、查找数据公式两个(基本查找函数为VLOOKUP,MATCH)
(1)、根据符合行列两个条件查找对应结果
=VLOOKUP(H1,A1:
E7,MATCH(I1,A1:
E1,0),FALSE)
(2)、根据符合两列数据查找对应结果(为数组公式)
=INDEX(C1:
C7,MATCH(H1&I1,A1:
A7&B1:
B7,0))
16、如何隐藏单元格中的0
单元格格式自定义0;-0;;@或选项》视图》零值去勾。
呵呵,如果用公式就要看情况了。
17、多个工作表的单元格合并计算
=Sheet1!
D4+Sheet2!
D4+Sheet3!
D4,更好的=SUM(Sheet1:
Sheet3!
D4)
18、获得工作表名称
(1)、定义名称:
Name
=GET.DOCUMENT(88)
(2)、定义名称:
Path
=GET.DOCUMENT
(2)
(3)、在A1中输入=CELL("filename")得到路径级文件名
在需要得到文件名的单元格输入
=MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))
(4)、自定义函数
PublicFunctionname()
DimfilenameAsString
filename=ActiveWorkbook.name
name=filename
EndFunction
19、如何获取一个月的最大天数
:
"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1为"2001-03-01
数据区包含某一字符的项的总和,该用什么公式
=sumif(a:
a,"*"&"某一字符"&"*",数据区)
最后一行为文本:
=offset($b$1,MATCH(CHAR(65535),b:
b)-1,)
最后一行为数字:
=offset($b$1,MATCH(9.9999E+307,b:
b)-1,)
或者:
=lookup(2,1/(b1:
b1000<>""),b1:
b1000)
评委打分中,如何去掉两个以上最高分,两个以上最底分,求剩余人员的平均分?
同时显示出被去掉的分数。
看看trimmean()函数帮助。
被去掉的分数:
最大两个:
=large(data,{1;2})
最小两个:
=small(data,{1;2})
怎样很简单的判断最后一位是字母
right(a1)*1
出错的字母
=IF(ISNUMBER(--RIGHT(A1,1)),"数字","字母")
=IF(ISERR(RIGHT(A1)*1),"字母","数字")
如何设置单元格,令其不接受包含空格的字符
选定A列
数据——有效性——自定义——公式
=iserror(find("",a1))
数据--有效性--自定义--公式
=len(a1)=len(trim(a1))
原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22.....
现在是=sum(n(offset(a1,(row(1:
10)-1)*3,)))
在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据我输入的工作表名自动选择相应的工作表中的数据,请问在公式里怎样引用?
=INDIRECT("A1"&"!
"&"E1")A1为工作表名
奇数行求和=SUMPRODUCT((A1:
A1000)*MOD(ROW(A1:
A1000),2))
偶数行求和=SUMPRODUCT((A1:
A1000)*NOT(MOD(ROW(A1:
A1000),2)))
查看字符串字数
=LEN(A1)
求非空单元格数量
公式计算出来的数据,COUNTA不能用的(否则空字符也计算进去了)
=COUNTIF($E$3:
$E$65536,"?
*")
动态求和公式,自A列A1单元格到当前行前面一行的单元格求和.
=SUM(INDIRECT("A1:
A"&ROW()-1))
20、比较好用的Excel文档修复工具
ExcelRecovery
21、EXCEL开方运算
将8开3次方,可以用这个公式,在单元格中输入 =8^(1/3)
22、单元格中的数据分散对齐
文本格式》全角输入
23、查找工作表中的链接
Ctrl+~或编辑》链接
24、如何让空单元格自动填为0
选中需更改的区域》查找》空》替换》0
25、把Word里的数字转换到Excel
方法有多种,选中》复制》设置输入单元格为文本》选择性粘贴》值
选中》表格转换为文本》粘贴》分列》对分列选项设置为文本
另存为文本文件》EXCEL中打开文本文件》对导入文本对话框进行对应设置
8种常见Excel错误提示及问题解决方法
Excel经常会显一些错误值信息,如#N/A!
、#VALUE!
、#DIV/O!
等等。
出现这些错误的原因有很多种,最主要是由于公式不能计算正确结果。
例如,在需要数字的公式中使用文本、删除了被公式引用的单元格,或者使用了宽度不足以显示结果的单元格。
以下是几种Excel常见的错误及
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 汇编 集锦
![提示](https://static.bdocx.com/images/bang_tan.gif)