EXCEL的一些技巧应用2Word格式.docx
- 文档编号:6423413
- 上传时间:2023-05-06
- 格式:DOCX
- 页数:15
- 大小:450.53KB
EXCEL的一些技巧应用2Word格式.docx
《EXCEL的一些技巧应用2Word格式.docx》由会员分享,可在线阅读,更多相关《EXCEL的一些技巧应用2Word格式.docx(15页珍藏版)》请在冰点文库上搜索。
很显然这是错误的信息。
接下来我们要做的事情就是快速地把所有错误信息查找出来。
全选C列的出生日期C3:
C4并单击“复制”按钮,然后把光标定位到D列单元格,点击工具栏中“粘贴”按钮右侧的小箭头并选择“值”选项(图2)。
将所有日期复制到D列之后,D列的出生日期全部处于选中状态,并且在D3单元格左侧出现一个智能提示符号,点击提示符右侧的小箭头并选择“转换为数字(C)”选项(图3)。
这样D列的数据就由文本格式转换成了真实的“日期”格式,细心的你就会发现只有正确的日期才能成功转换,其对齐方式为“右对齐”,而不正确的日期则仍是文本格式,对齐方式仍为“左对齐”(图4)。
有了比较,大家一眼就能看出不正确的身份证号码,更正起来那是相当的容易了。
小提示:
目前我国的身份证号码分为两种,一种为15位,一种为18位。
在15位的老版身份证中,第7到12位为出生日期,18位的新身份证中第7到14位为出生日期。
2、在Excel中累加的小技巧
在使用Excel时,常常需要在已有数值的单元格中再增加或减去另一个数。
一般是在计算器中计算后再覆盖原有的数据。
这样操作起来很不方便。
这里有一个小技巧,大家不妨一试。
1.创建一个宏。
选择Excel选单下的“工具→宏→录制新宏”选项;
宏名为:
MyMicro;
快捷键为:
Ctrl+Shift+J(只要不和Excel本身的快捷键重名就行);
保存在:
个人宏工作簿(可以在所有Excel工作簿中使用)。
2.用鼠标选择“停止录入”工具栏中的方块,停止录入宏。
3.选择Excel选单下的“工具→宏→VisualBasic编辑器”选项。
4.在“VisualBasic编辑器”左上角的VBAProject中用鼠标双击VBAProject(Personal.xls)打开“模块→Module1”。
注意:
你的模块可能不是Module1,也许是Module2、Module3。
5.在右侧的代码窗口中将Personal.xls-Module1(Code)中的代码更改为:
SubMyMicro()
OldValue=Val(ActiveCell.Value)
InputValue=InputBox(“输入数值,负数前输入减号”,“小小计算器”)
ActiveCell.Value=Val(OldValue+InputValue)
EndSub
6.关闭VisualBasic编辑器。
编辑完毕,你可以试试刚刚编辑的宏,按下Shift+Ctrl+J键,输入数值并按下“确定”键。
3、让Excel表格能录音与回放
在给学生讲Excel表格时,很多学生感觉到它很难。
问我可不可以把我讲课的内容录制下来,让他们拿回家再多看几遍。
我想可以用录像机把我讲课的内容录下来,但操作不方便,又费时费力。
我想学生只是要看我讲课的内容,又不是看我讲课的台风,如果只把这讲课的内容录下来不就行了吗?
经过研究,我找到了一种方法,既可以让学生看见我的Excel表格操作步骤,又可以听见我讲话的声音。
特写下来以供有这方便需求的读者参考。
操作步骤:
第一步:
打开Excel→单击菜单中的“插入”按钮→选择“对象”。
第二步:
在“对象”对话框中选择“音效”→单击“确定”按钮。
第三步:
单击“开始录制声音”按钮。
这时就可以边操作边讲话了。
所讲的话全部被录在电子表中了。
(注:
这种录单方式只能录60秒,当然我们可以让它多录制一会,方法是:
当快录制完时单击一下“停止录制”按钮,再单击“开始录制声音”按钮,这样循环往复,便可以长时间录制了)
第四步:
全部录制完毕后→单击“文件”菜单中的“退出”按钮→再弹出的对话框中单击“是”按钮。
最后把这个文件保存起来拿给学生,学生只需要双击电子表格中的图标便可以听见声音了。
4、快速输入部分重复的数据
在使用Excel进行报表处理时,我们常常会输入部分重复的数据,比如同一地区的身份证号码,前面部分是相同的数字组合。
如何快速输入带有部分重复的数据内容呢?
其实这个问题可通过Excel单元格的自定义功能轻松解决。
操作过程如下:
首先选定设置格式的单元格,然后单击“格式”菜单中的“单元格”命令,打开“单元格格式”对话框。
单击“数字”选项卡,在“分类“列表中选定“自定义”选项,任选一种内置格式(一般选择不常用到的格式),在“类型”框中输入重复部分的数字,如某一地区的身份证号码的前6位数字“123456”(一定要在数字上加上双引号),单击“确定”按钮后退出,这样在输入身份证号码时,只需输入123456之后的数字即可。
5、Excel自定义公式防止输入重复数据
大家在使用Excel2007编辑工作表格时经常要输入大量数据,有时要求输入的这些数据是不允许重复的,例如常见的身份证号码等,一旦错误输入查找起来那可是相当麻烦的一件事。
对于这个问题,其实我们可以自定义公式,让Excel对重复数据能够自动弹出警告信息,以便及时提醒操作人员。
Step1:
运行Excel2007程序并新建一个工作表格,然后按下“Ctrl+A”组合键全选该表格。
Step2:
单击“数据”菜单,然后在“数据工具”功能区域中选择“数据有效性”选项,接下来在随即弹出的“数据有效性”对话框中选择“设置”选项卡,在“允许”下拉列表框中选择“自定义”标签,在“公式”下面的文本框中输入“=COUNTIF($1:
$65535,A1)=1”(不含双引号)。
这里的“$1:
$65535”表示对全工作表范围进行重复检查。
如果你需要检查的只是某一特定的单元格范围,可以根据实际情况重新进行调整,但必须使用绝对方式。
Step3:
切换到“出错警告”选项卡,在这里程序已经自动勾选了“输入无效数据时显示出错警告”的复选框,接下来将“样式”设置为“停止”选项,然后在右侧的“标题”和“错误信息”两个文本框中可以自行输入相关的内容、具体的警告信息等等,然后单击“确定”按钮即可。
经过设置之后,今后重复输入相关数据时,程序会自动弹出一个对话框,提示“有重复数据出现”,提供“重试”、“取消”、“帮助”三个选项。
选择“重试”,则对现有数据进行检查校核;
选择“取消”可以重新输入。
6、支持粘贴预览Excel2010粘贴效果未卜先知
Excel的选择性粘贴功能是非常强大的,但是在以往的Excel版本中有一个小小的遗憾,就是只有粘贴之后才能看到粘贴的样子。
如有的时候不知道在选择性粘贴中的选项最后的样子,有时数据粘贴过来是科学计数法,有的时候连公式一起粘贴过来,因此让人觉得有点晕。
在Excel2010中,复制内容之后,在目标单元格上单击鼠标右键,在选择性粘贴右侧有个箭头,单击箭头会出现选择性粘贴的所有粘贴方式,而且按粘贴方式分成了大类,当鼠标停在某个粘贴选项上时,在Excel中会出现最终粘贴样式的预览,让用户一眼就可以知道粘贴之后是什么样子的,这样也就不会出现粘贴过来是科学计数法的尴尬了。
在Office2010的其他主要组件如Word2010和PPT2010中也有粘贴预览功能。
7、为Excel2007多个工作表快速创建目录
我们经常把同类相关Excel工作表集中保存在同一文档中,以便于在各表格间进行引用、查看。
当一个文档中的工作表达到一定数量时,要想找到需要的工作表就变得很麻烦了。
此时若能建立一张“目录”工作表显示所有工作表的名称和链接,事情将会简单很多。
下面介绍一种可以快速为Excel工作簿创建工作表目录的方法。
定义名称
打开Excel2007,右击第一张工作表标签选择“重命名”,把它重命名为“目录”工作表。
选中B1单元格,切换到“公式”选项卡,单击“定义名称”,在弹出的“新建名称”窗口中输入名称“工作表名”,在引用位置中则输入公式=INDEX(GET.WORKBOOK
(1),$A1)&
T(NOW()),单击确定即可定义出一个名为“工作表名”的名称(图1)。
公式中GET.WORKBOOK
(1)用于提取当前工作簿中所有工作表名称,INDEX函数则按A1中的数字决定要显示第几张工作表的名称。
此外,由于宏表函数GET.WORKBOOK
(1)在数据变动时不会自动重算,而NOW()是易失性函数任何变动都会强制计算,因此我们需要在公式中加上NOW()函数才能让公式自动重算。
函数T()则是将NOW()产生的数值转为空文本以免影响原公式结果。
注:
宏表函数GET.WORKBOOK,不能直接在单元格公式中使用,必须通过定义名称才能起作用。
目录设置
在“目录”工作表的A1单元格输入1,在B1单元格输入公式=IFERROR(HYPERLINK(工作表名&
"
!
A1"
,RIGHT(工作表名,LEN(工作表名)-FIND("
]"
,工作表名))),"
)。
公式表示当名称“工作表名”的值为错误值时显示为空“”,否则创建指向“工作表名!
A1”的超链接并显示该“工作表名”。
然后选中A1:
B1单元格,把鼠标指向选中区右下角的“填充柄”按住鼠标左键向下拖动到300行,把公式和编号填充出300行,在A、B列就会马上自动列出所有工作表目录(图2)。
单击相应工作表名称即可快速切换到该工作表中。
请参照可能的最多工作表个数来决定向下填充行数,一般300个应该够了。
公式中RIGHT(工作表名,LEN(工作表名)-FIND("
,工作表名))这段函数的作用是除去“工作表名”中“]”以前的内容。
若你不介意工作表名称前显示“[BOOK1.xlsx]”一类内容的话,可以把B1中的公式简化成=IFERROR(HYPERLINK(工作表名&
,工作表名),"
保存设置
切换到“开始”选项卡适当设置一下目录中的字体、字号和颜色等等,建议把字号放大并设置加粗以便查看,还要调整一下A:
B列的列宽以便完全显示工作表名称。
然后右击其他工作表标签选择“删除”,把所有其他工作表全部删除只保留一张“目录”工作表。
最后单击“Office”按钮,选择“另存为”,在弹出的另存为窗口中选择保存类型为“Excel启用宏的模板(*.xltm)”格式、文件名为“目录.xltm”,保存到C:
\ProgramFiles\MicrosoftOffice\Office12\XLSTART文件夹下,关闭Excel2007退出。
若你的Office不是按默认路径安装,请按实际安装路径修改。
三秒创建目录
以后要为工作簿创建目录就简单了,只要用Excel2007打开要创建目录的工作簿,在第一张工作表的标签上右击选择“插入”,在“插入”窗口中双击选择“目录”,即可在第一张工作表前插入一张“目录”工作表,并显示出所有工作表目录。
这操作有3秒就够了吧?
在“目录”工作表中,可通过对目录进行筛选、排序、查找来快速找到工作表名,然后单击工作表名即可打开相应工作表。
创建目录后,在这个工作簿中增加、删除工作表或者修改工作表名称,“目录”工作表中的工作表目录都会自动更新。
此外,前面我们只复制了300行目录公式,因此工作表总数超过300个时,超出的工作表名就不会显示了,得把“目录”工作表中A1:
B1的公式再向下复制填充才行。
由于宏表函数GET.WORKBOOK
(1)是通过宏功能起作用的,所以插入了工作表目录的文档最后都必须以“Excel启用宏的工作簿(*.xlsm)”格式另存,这样下次打开时才能正常显示工作表目录。
此外,打开工作簿时,Excel2007默认会禁用宏,得单击警告栏中的“选项”按钮,选中“启用此内容”单选项,确定后才能显示工作表目录。
8、快速为Excel工作簿创建工作表目录的方法
我们经常把同类相关Excel工作表集中保存在同一文档中,以便于在各表格间进行引用、查看。
T(NOW()),单击确定即可定义出一个名为“工作表名”的名称。
ProgramFilesMicrosoftOfficeOffice12XLSTART文件夹下,关闭Excel2007退出。
9、Excel2010将不相邻区域打印在同一页
有时候我们在Excel中不希望打印整张表格,只是想打印部分数据,例如要将多个不相邻区域的数据都打印在同一个页面上,在Excel2010中该如何操作呢?
step01打开要打印的.xls表格文件。
先给整张表做个视图,方法是点击视图菜单,单击工作簿视图区中的自定义视面按钮打开视图管理器,然后点右边的添加按钮,在弹出的添加视图对话框中输入一个视图名称(例如打印全部数据),按确定退出。
step02隐藏表中不需要打印的列和行。
首先鼠标点选无需打印的第一列,按住Ctrl键不放、再用鼠标分别点选需隐藏的其他列,这样就会同时选中多个不连续的列。
鼠标移到列名单元格上,右击鼠标,在弹出菜单中寻隐藏,于是无需打印的列就全部隐藏起来了。
如上法所示隐藏所有无需打印的行。
接下来点击视图菜单,单击自定义视面按钮打开视图管理器,添加一个只包含要打印行列的视图(如打印报表),按确定退出。
step03需要将多个不相邻区域打印在同一页时,先在Excel2010中将该视图(如打印报表)显示出来,即点击视图菜单,单击自定义视面按钮打开视面管理器,选中该视图(如打印报表),单击显示按钮,这样表格中就只有要打印的数据了。
然后点击文件→打颖菜单,打开打颖对话框,在设置下选中打印选定区域选项,设置一个要打印的区域,最后单击打颖按钮即可。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 一些 技巧 应用