excel新功能.docx
- 文档编号:4612050
- 上传时间:2022-12-07
- 格式:DOCX
- 页数:21
- 大小:529.33KB
excel新功能.docx
《excel新功能.docx》由会员分享,可在线阅读,更多相关《excel新功能.docx(21页珍藏版)》请在冰豆网上搜索。
excel新功能
Excel制表技巧
用Excel自动出题,就能实现自动出题、自动更新题目的目的。
具体操作方法也非常简单。
为了便于管理,先建立一个名称为乘法的文件夹,在这个文件夹里新建一个名称为出题的Excel文件(如图1所示)。
A列、C列的函数公式是:
“=INT(RAND()*(9-1)+1)”。
公式意义是随机返回一个 1至9的整数,每次打开或关闭此文件,都会重新随机产生新的整数。
此文件使用方法也很简单:
主要就是打开再关闭文件,关闭时出现“文件‘出题。
xls’已被修改,是否保存其修改的内容?
”提示框,单击[是]按钮即可。
二、自动判断
用Excel自动判断,就可以根据输入的数值,自动判断结果的正误。
具体操作方法也非常简单。
在乘法文件夹里再新建一个名称为练习的Excel文件(如图2所示)。
1. 在文件中,第1行、B列、D列输入文字即可。
2. A2单元格的函数公式是“=F:
\乘法\[出题。
xls]Sheet1!
$A$1”。
其实我们想实现的是练习文件的A2单元格等于出题文件A1单元格的内容,如果直接打公式可能比较麻烦,使用鼠标操作即可。
同时打开出题和练习这两文件,先选中练习的A2单元格,按编辑公式栏的“=”,再切换到出题文件,选中A1单元格即可。
其中公式里的“F:
\乘法\[出题。
xls]Sheet1!
”表示的就是一个相对引用,会根据文件的存放位置自动变化,这里表示刚才建立的乘法文件夹是在F盘的根目录。
根据相同的方法做完A列的A2至A11和C列的C2至C11单元格公式。
3. E列由练习人自己根据题目填写答案。
4. F2单元格的公式是“=IF(ISBLANK(E2),此题还没做!
,IF(E2=(A2*C2),√,×))”。
用ISBLANK判断做题人是否答题(如果E2单元格是空白单元格,就判定为没有答题;否则就判定为已经答题),没有答题就显示“此题还没做!
”;已经答题的,就用IF进行判断答案是否正确,如果答案正确就显示“√”,答案错误就显示“×”,F3到F11单元格的公式使用填充柄向下拉即可。
此文件使用方法也很简单:
打开文件,会出现“当前所要打开的文档含有到其他文档的链接,是否要使用其他工作簿中的改动更新当前工作簿?
”提示框,选择 [是]按钮即可以开始答题。
需要变换题目时,打开出题文件,保存后关闭,即可重新开始。
为了使练习文件里的E列不留下答题的痕迹,可以将此文件设置为只读或关闭此文件时,不要保存,使得E列保持空白。
Excel实现随机出题
以出20以内的随机加法题为例。
1. 新建一个工作表,按住Ctrl不放,分别单击A1和C1,将其选中,在其右键菜单中选择“设置单元格格式”,单击“数字”选项卡,选择“数值”,将“小数位数”右边的数字缩小为“0”后,单击[确定]按钮。
2. 分别在单元格A1中输入公式“=rand()*20”,B1中输入“+”,C1中输入公式“=rand()*(20-A1)”,D1中输入“=”。
3. 选中A1:
D1,向下拖动其右下角的填充柄至D25,一列20以内的随机加法题就完成了。
4. 选中单元格A1:
D25,将选定区域“复制”,在单元格F1位置“粘贴”,即可完成多列随机出题。
5. 若要重新生成随机题,只需拖动任意一个空白单元格右下角的填充柄即可。
Excel巧制自测练习卷
[编者] 前面我们介绍了《Excel常用函数的功能和使用方法》,现在我们学以致用,介绍一系列用这些函数实现的数据统计实例解析。
今天我们介绍一个教师学生都方便使用的电子自测练习卷。
教师的一个主要工作就是批改学生的作业,由于学生的作业量很大,教师的批改工作量非常大。
随着家庭电脑的不断普通,我们可以用Excel等软件制作电子练习卷,让学生上机解答,让电脑来自动批改。
完成功能:
我们用Excel制作这样一份电子练习卷,选择题5题,每题正确答案可能有1—2个,完成正确得10分,如果有2个正确答案,学生选择了1个且正确,得4分,其它均不得分;判断题5题,判断正确得10分,反之不得分。
注:
这样制作的电子练习卷,一般仅支持选择、判断、填空等客观性试题。
制作方法:
1、启动Excel2003(其它版本请大家仿照操作),按照练习卷的格式我要求,制作好一张空白表格。
2、将题号、题目、标准答案等内容输入到相应的单元格中(效果如图1)。
注意:
在单元格中输入文本,如果需要换行,可以通过下面两种方法来实现:
①选中需要换行的单元格,执行“格式、单元格”命令,打开“单元格格式”对话框(如图2),切换到“对齐”标签下,选中“自动换行”选项,确定返回即可。
经过这样的设置后,如果单元格中填入超过列宽的内容,系统会自动换行;如果调整列宽,系统会自动调整单元格中各行的字符量。
②如果想在某个字符处强制换行,请将光标定在相应字符的右则,然后按下“Alt+Enter”组合键即可。
让Excel给你报账
许多人经常要用Excel来统计一些数据,统计完成后还要对数据进行校对,但是这么多数据怎样才能不出差错地进行校对呢?
其实我们可以用一下Excel自带的“文本到语音”功能,让软件通过发声自动给你报账。
打开Excel,点击“工具→语音→显示文本到语音工具栏”,由于这个功能在安装Excel的时候不是默认安装的,所以在第一次使用时会提示插入Office 安装光盘来安装这个功能。
安装完成后打开“文本到语音”工具栏,先在数据文件中用鼠标选择要朗读的第一个数据,接着根据数据的排列情况来选择是“按行”还是“按列”来进行朗读,设置完成后点击工具栏最左面的“朗读单元格”按钮就可以了。
在朗读的时候被朗读到的单元格会以选中状态显示,而且还可以流利地朗读中文,英文是一个字母一个字母朗读的,这样就可以校对英文单词的拼写是否正确(图1)。
该功能还可以一边输入数据一边来进行语音校对,只要点击一下工具栏最右面的 “按回车开始朗读” 按钮,使其为选中状态,这样在完成一个单元格的输入后按回车,Excel就会自动来朗读这个单元格中的内容。
一般朗读默认的是男声的,如果你听腻了,可以通过系统里的设置把男声改为女声,打开控制面板中的“语音”项,在里面的“语音选择”来选择一下语音,然后点击下面的“预览声音”就可以听效果了,而且还可以设置语音的朗读速度,完成后点击“确定”按钮就可以了(图2)。
让Excel的格式自动变化
作为会计人员,经常要对一些金额进行控制。
比如预算、开支等。
但怎么让表格自动计算并提醒你,那些项目快要超出预算或己经超支呢?
或是,你经常要交总结材料给老板,对于表格上的一些项目,需突出显示它们的区别,以便引起老板注意,你是否每次都是不辞劳苦的对些单元格格式进行反复设置?
其实,利用Excel的条件格式,可让你一劳永逸。
请看下面的一个例子(图一)。
图一
在图一中,笔者的目地是要对每天的开支进行控制。
于是用SUMIF函数对在另一张工作表中的日常开支明细进行汇总。
对于汇总后的结果,笔者利用条件格式,对每天不同的开支数量用不同的格式显示,如果日开支等于0,则将字符颜色设成跟底色一样,以免看到0;如开支小于50元,用缺省格式,如果在50-100之间,则用黄底红字,如超过100元以上,则用红底黑字显示,以示警告。
(具体的设置情况如图二。
)
图二
这样,电脑根据不同的汇总结果,便显示不同的效果,笔者一眼看上去,便知哪天又严重超支了。
(虽然如此,笔者仍是无法降低开支,该花的还是要花… *o*)。
条件格式是支持拖拉填充的,所以,笔者只要设好B1单元格的公式及条件格式,然后往下一拖即成。
简单快速。
条件格式的设置方法为:
1. 选中要设置条件格式的单元格;
2. 选择菜单“格式”/“条件格式…”,系统弹出“条件格式”设置对话框。
见下图三。
在缺省情况下,对话框只显示一种情况,如大家要求对多种情况进行判断并作相应格式设置,可按“添加”按钮。
最多可有三种逻辑判断(参见图二),因为还存在一种以上三种情况都不是的情形,所以,真正来说,它可以设置四种状态下的格式。
如上面的例子中,开支在0-50元的情况便是系统需进行的第四种判断;
2004-5-31 15:
43:
04
图三
3. 系统预设为对该单元格的值进行判断,根据实际情况可选择大于、介于、小于等,然后在其后面的输入框中输入条件。
在条件输入框中可以直接内容也可以输入单元格名称参照,以让系统对该单元格的实际内容进行比较。
另外,点击“单元格数值”可选择判断条件为公式。
当选择条件为公式时,其后的逻辑判断框变成公式输入框。
请注意:
在输入公式时,请用“=”号开头。
对于公式,系统按公式返回的值是否为“真”进行逻辑判断。
4. 设好条件后,按“格式…”按钮,便进入格式设置画面。
(参见图四)根据自己的喜好,选择相应的格式。
然后按“确定”返回。
这时可在对话框中间看到格式预览。
图四
5. 依次设置其他条件格式,最后按“确定”返回编辑窗口,条件格式设置完毕。
这时你在该单元格输入不同的内容,其格式便会随着你定义的条件而自动变化。
6. 大家可利用格式刷将条件格式快速套用到其它地方。
条件格式的删除
如需要对己设置的条件格式进行删除,可用下面的方法进行删除:
1. 选择要删除条件格式的单元格;
2. 选择菜单“格式”/“条件格式…”,在系统弹出“条件格式”设置对话框中按“删除”按钮。
或者
1. 选择没有设置条件格式的单元格;
2. 按下工具列的格式刷,对要删除条件格式的单元格进行格式复制。
如要删除工作表中全部或部分相同条件格式,可先按“Ctrl+G”,之后在对话框中按“定位条件”,系统显示定位条件对话框(参见图五),在该框中,选择“条件格式”,之后,根据需要选择“全部”或“相同”,再按“确定”,回到编辑窗口,这时,所有设置了条件格式的单元格都被选中,再按上面所讲的第一种删除方法删除即可。
图五
自动工作日计算
工作量当然得以“工作日”的数量去安排,但每个月的天数不同,而且周六日也不尽相同。
如果想准确地知道一个月有多少个工作日,可以请Excel来帮忙!
小知识
标准工作日是指法律规定的各企业、事业、机关、团体等单位在正常情况下普遍实行的工作日。
通俗的说,就是指除了国家法定节假日(双休日、元旦、五一、国庆、春节等)之外正常工作的日期。
任务分析
因为每月的总天数和双休日的分布是不同的,而且春节是农历的假期,简单地使用Excel函数去计算是满足不了要求的。
我们先使用Excel“分析工具库”中的networkdays()函数计算出“准工作日”,再考虑其中的特殊农历假日。
这样就能准确的算出“工作日”的天数。
有请 networkdays()函数
通常情况下,在Excel中是找不到这个函数的。
别着急,跟我来。
单击“工具→加载宏”,在弹出的“加载宏”对话框中复选“分析工具库”,再单击“确定”按钮。
将Office的安装光盘放入光驱,按提示即可安装成功,如图1。
外来的和尚念不好经
networkdays()函数的语法为:
networkdays(Start_date,End_date,Holidays)。
其中Start_date表示开始日期,End_date为终止日期,Holidays表示一个或者多个特定假日序列,可以采用单元格引用的方式。
如图2,B8单元格公式“=networkdays(B2,B3,B4:
B6)”结果为20。
事实上,2004年的10月2日和3日是星期六和星期天,那么按照我们的习惯就会通过调休的方式,最终得到10月份只有18天上班,而不是networkdays()函数计算出的20天。
特殊假日的处理
虽然说直接应用networkdays()函数并不能得到正确的工作日天数,但我们还是可以通过对此函数的变化来得到。
图3是我们的范例,演示了如何一步一步逼近我们的目标。
第一步:
将A列定义为月份。
选中A列,在右键菜单中选择“设定单元格格式”,在“数字”标签中选择“分类”为“自定义”,“类型”中输入“yyyy-mm”格式,单击“确定”按钮退出。
第二步:
设计B列公式。
先直接用networkdays()函数计算只考虑本月除去双休日后的天数。
虽然可直接使用该函数,但需要知道每月的开始日期和终止日期。
开始日期当然是每月的1日,用“date(year(A2),month(A2),1)”表示就OK了。
结束日期应当是每月的最后一天,这就有些难度了,究竟是30日、31日还是28日、29日?
需要一个复杂的判断关系。
这里我们采用了一个变通的办法,就是当月的最后一天其实就是下月的第一天再减去1天,所以我们可以用“date(year(A2),month(A2)+1,1)-1”来表示。
于是B2的公式就有了:
“=networkdays(date(year(A2),month(A2),1),date(year(A2),month(A2)+1,1)-1)”。
第三步:
计算除了春节以外的双休日。
我们知道国家规定的公众假日一共10天,除了春节的3天是每年变化的外,其余元旦1天,劳动节3天,国庆节3天都是固定的。
因此我们可以用IF函数对月份是否是1月、5月和10月做判断,再来减去相应的公众假日天数,就得到了不考虑春节以外的工作日。
于是C2=if(month(A2)=5,B2-3,if(month(A2)=5,B2-3,if(month(A2)=1,B2-1,B2)))或者使用or()函数就是C2=if(or(month(A2)=5,month(A2)=10),B4-3,if(month(A2)=1,B4-1,B4))。
第四步:
考虑春节的问题。
根据常识,春节的三天只会在1月份或者2月份出现,也就是说最后的判断只正对1、2月份。
通过万年历查询得到2000——2010这十年的春节分布,巧的是正好没有春节三天跨月份的年份,也就是说春节三天不是落在1月份就是落在2月份,如图4所示,这给我们应用IF函数判断带来了方便。
通过对年份和月份的判断,减去相应的春节天数,就得到了真正意义上的工作日。
因此在D2中应该输入=IF(AND(OR(YEAR(A2)=2001,YEAR(A2)=2004,YEAR(A2)=2006,YEAR(A2)=2009),MONTH(A2)=1),C2-3,IF(AND(OR(YEAR(A2)=2000,YEAR(A2)=2002,YEAR(A2)=2003,YEAR(A2)=2005,YEAR(A2)=2007,YEAR(A2)=2008,YEAR(A2)=2010),MONTH(A2)=2),C2-3,C2))。
第五步:
隐藏过渡列。
选中“B:
C”这两列,在右键菜单中选择“隐藏”,将中间用于计算方便所使用的过渡列B和列C隐藏即可。
选中区域B2:
D2,按住填充柄向下拖动填充后,只要在A列任一单元格输入月份,就可以在D列得到该月相应的天数,是不是很方便?
让多页表自动生成表头
当我们制作的表格有好多页的时候(如成绩统计表等),为便于观看,最好是每一页都添加一个相同的表头。
如果表格文件页数很少,通过手工的方法就可以很容易地实现。
但当一个表格文件多达数十页的时候,再一个一个地逐页添加,不仅费事,还容易出错。
那么,有没有办法让系统自动生成呢?
1). 在Word 2003中
打开Word 2003表格文档,选中第一页表的表头,然后点击“表格→标题行重复”。
假如表头有两行内容,选中开头两行,这样,在后面的每一页,都会将这两行的内容作为表头。
2). 在Excel 2003中
(1)打开Excel表格文档,点击“文件→页面设置”。
(2)在调出的“页面设置”对话框中,选择“工作表”选项卡,在“顶端标题行”栏中输入表头行号,其中,“$1”表示第1行,“$1:
$2”表示第1、2行。
本例中设置为“$1:
$2”,打印出来的每页表格便都有同第1、2两行同样内容的表头。
3). 在金山文字2003中
(1)打开金山文字2003表格文档,选中表格,然后点击“表格→表格属性→表格外观”。
(2)在调出的“表格外观”属性窗口上,选择“外观设定”为“行列式1”,设置“以标题行重复出现行数”,本例设置为“2”,即在每一页表格都自动生成与第1、2行内容相同的表头。
4). 在金山表格2003中
(1)打开金山表格2003,点击“文件→页面设置”。
(2)在调出的“页面设置”选单中,选择“工作表”选项卡,在“顶端标题行”输入表头行数,本例为“2”,即在打印出来的每一页表格上,都自动生成与第1、2行内容相同的两行表头。
如果表格的表头在左边,可以在“左端标题列”栏中输入表头列数。
Excel轻松实现自动换行
Excel处理数据之便捷众人皆知,可在其单元格内换行就略显不便,不知你是否也遇到过此类问题?
通过摸索,以下四法便能轻松实现单元格内的自动换行。
1. 输入数据随时换行
用户若要在输入数据时换行,只要通过Alt+Enter组合键即可轻松实现。
此方法同样可使已输入内容的单元格在光标所在处换行。
2. 单元格区域内换行
将某个长行转成段落并在指定区域内换行。
例如:
A10内容很长,欲将其显示在A列至C列之内,步骤是:
选定区域A10:
C12(先选A10),选择“编辑→填充→内容重排”,A10内容就会分布在A10:
C12区域中。
此法特别适合用于表格内的注释。
3. 调整单元格格式换行
选定单元格,选择“格式→单元格”,在弹出的对话框中单击“对齐”,选中“自动换行”复选框,单击[确定]按钮即可。
4. 文本框的巧用
单击“视图”菜单,在“工具栏”命令中,选中“绘图”工具栏,单击该工具栏的“文本框”,为了保证文本框的边界与工作表网格线重合,需按住Alt键的同时插入文本框,然后,就可以在文本框中任意输入内容了。
通向Excel智能化的N条捷径
智能判断Excel重复数据
在Excel工作表中,若要判断各个单元格中的数据是否重复并统计出重复的行号,可使用函数令其自动执行操作。
假设,要统计显示于A1~A12单元格中的数据。
首先,在B1单元格中输入代码:
=IF(COUNTIF($A$1:
$A$13,VLOOKUP(A1,A2:
$A$13,1,0))>1,CONCATENATE("重复行号:
",MATCH(A1,A2:
A$13,0)+ROW(A1)),"")
而后,在B1单元格中会显示与A1数据重复的行号。
下面,选择区域B1~B12,点击菜单栏“编辑”→“填充”→“序列”,在弹出对话框中查看“类型”项目,在此选择“自动填充”,其余选项保持默认设置。
确认操作后,B2~B12之间的重复行号均会自动填充(如图1)。
快捷搜索相同的数据
在Excel中,若要从众多的数据快速查找到相同的一组数据,可利用其筛选功能。
先选择数据列的最上方的单元格,再点击菜单栏“数据→筛选→自动筛选”。
而后,即可在该单元格上看到一个向下箭头按钮,点击它,在弹出菜单中选择欲查找的相同数据即可(如图2)。
自动填写IP地址
如果需要在Excel中输入在大量本公司局域网的IP地址,人工操作是非常耗时的,这种情况下我们可使用以下方法使其自动完成输入。
首先,在数据列的第一行和第二行输入IP地址。
然后,选择这两个已输入IP地址的单元格,点击工具栏上的“复制”按钮。
此后,会看到该区域又被添加为虚线边框,把鼠标置于该区域的右下角,会看到一个十字形光标,点击拖拽该光标至需输入IP地址的结束位置。
放开鼠标后,将看到所有IP地址均按规则自动填充完毕(如图3)。
提示:
按照用户的习惯性思维,往往会考虑使用Excel的“自动填充”实现这一效果,但可惜在本例中此项功能并不奏效。
自动计算销售金额
在如图4所示的表单中,若要计算出某个员工一个月内的销售金额,可以使用筛选再求和的方法,也可以使用函数计算。
方法1:
定位于“人员”单元格,点击菜单栏“数据→筛选→自动筛选”,而后点击新增的该单元格向下箭头按钮,在弹出菜单醒≡褚桓鲋付ǖ男彰 似渌 嗽保 笱≡袼 邢 劢鸲钪械氖 荩 俚慊鞴ぞ呃干系摹白远 蠛汀卑磁ゼ纯苫竦眉扑憬峁 7椒?
:
定位于某人一月汇总销售金额输入位置的单元格后,输入公式代码:
=SUM((B列始行数:
B列结束行数="目标员工姓名")×(D列始行数:
D列结束行数="1")×(C列始行数:
C列结束行数))再按“Ctrl+Shift+回车”确认公式即可。
智能化判断数据有效性
1. 自动弹出说明提示
对于需要注释说明的单元格数据,我们可为其添加提示功能,以帮助用户能够更明晰地了解当前内容。
在选定Excel表格中的指定单元格后,点击菜单栏“数据→有效性”,而后在弹出窗口中切换到“输入信息”标签页,在此输入选定单元格时显示的标题及信息(如图5)。
2. 自动弹出错误提示
我们还可以设置在Excel中当输入不符合条件的数据时自动提示。
按以上方法调出“数据有效性”窗口后,切换到“出错警告”对话框,在此可选择出错警告的图标样式与标题及错误信息。
3. 限制输入数据的条件
默认状态下,在Excel中可以输入任何数据,而你若是想让指定单元格的数据只能是日期、时间或是有大小、长度等限制,即可使用Excel有效性功能。
在“数据有效性”窗口中,查看“设置”标签页,在此点击“允许”下拉列表,在此选择允许输入数据的条件,例如“整数”、“小数”、“序列”、“文本长度”等,而后再按需进一步详细设置。
4.自动添加表头
有些用户在打印多页Excel表格时发现,仅第一页有表头,而第二页以后就看不到表头,只能手工添加,其实只需要调节一下打印设置即可。
在Excel中点击菜单栏“文件→页面设置”,而后在弹出窗口中切换到“工作表”标签页,在此查看“打印标题”项目,在“顶端标题行”中输入每页打印的表头内容,再预览一下效果。
隔行数值自动求和
有时出于特殊需要,得把一个Excel表格中的
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 新功能