EXCEL实例培训.docx
- 文档编号:8302307
- 上传时间:2023-01-30
- 格式:DOCX
- 页数:20
- 大小:270.26KB
EXCEL实例培训.docx
《EXCEL实例培训.docx》由会员分享,可在线阅读,更多相关《EXCEL实例培训.docx(20页珍藏版)》请在冰豆网上搜索。
EXCEL实例培训
Excel2007技巧
技巧一有时,在粘贴数据时不需要原来的边框,则可以在粘贴时调出“选择性粘贴”对话框,选中“边框除外”前的单选按钮
技巧二在“(c)”后面跟一个空格,可以快速输入版权符号“.”;在“(r)”后面跟一个空
格,可以快速输入注册符号“”。
技巧三输入函数名和左括号后,按Ctrl+A键,将弹出“函数参数”对话框,例如在某单
元格中输入“=sum(”,然后按Ctrl+A键,则弹出关于sum函数的“函数参数”对话框。
技巧四输入函数名和左括号后,按Ctrl+Shift+A键,将列出整个函数及其参数,例如在某单元格中输入“=sum(”,然后按Ctrl+Shift+A键,该单元格将显示“=sum(number1,number2,...)”。
技巧五在输入公式时,按F4键,会在相对或绝对引用之间转换,这样可以快速输入“$”
符号。
Excel2007公式常见显示错误原因与解决方法
Excel2007的公式如果写错,就会在单元格中显示各种各样的错误信息。
看到这些奇怪的错误代码,有的朋友可能会手忙脚乱,甚至感到烦躁。
其实,任何错误均有它内在的原因,下面我们就和大家探讨根据公式返回错误值的代码识别错误的类型和原因,以及相应的处理方法,帮助朋友们轻松地应对各种常见错误。
错误显示1:
#####!
原因:
单元格所包含的数字、日期或时间占位比单元格宽。
解决方案:
拖动鼠标指针更改列宽。
错误显示2:
#NUM!
原因:
顾名思义,公式中的数字出现问题——类型错误,或者数字超出Excel表示的有效范围。
解决方案:
让数字类型和大小都符合要求。
错误显示3:
#REF!
原因:
公式引用的单元格被删除或者被错误覆盖。
解决方案:
临时修改后及时“撤销”,恢复单元格原状。
错误显示4:
#VALUE!
原因a:
需要数字或逻辑值时输入了文本,如单元格B1为数字15,单元格B2为文本,则B3中输入公式“=B1+B2”将返回错误值#VALUE!
解决方案a:
确保公式引用的单元格中包含有效值。
上例中可在B3中输入=SUM(B1:
B2),SUM函数忽略文本,所以结果显示15。
原因b:
本来应该引用单一数值,却引用了一个区域。
解决方案b:
将数值区域改成单一数值,或者修改数值区域,使其包含公式所在的数据行或列。
按Shift键把Excel单元格区域转换为图片
Shift键在Excel里有这样的妙处:
在按下Shift键的同时点击“编辑”菜单,原来的复制和粘贴选项就会变成“复制图片”和“粘贴图片”。
利用这一功能,我们就能把选定的单元格区域方便地转换为图片。
具体操作方法如下:
首先选中需要复制成图片的单元格区域,然后按住Shift键,依次选择“编辑→复制图片”命令,接着弹出“复制图片”窗口,选择“图片”单选项后点击“确定”按钮。
这时选定的表格区域就已经被复制成图片了。
再到需要使用这张图片的地方选择“粘贴”命令即可(或者按Shift键再选择“编辑→粘贴图片”命令),比如将其在Word中粘贴。
另外,如果选择“如打印效果”单选项,那么被转换为图片的数据区域是按照打印效果来处理的,比如设置有粗边框,那么粘贴出来的图片上就会有粗边框。
附送小技巧:
复制Excel单元格以后,在QQ聊天窗口里粘贴,也能转换出图片
EXCEL中行列互换
复制,选择性粘贴,选中转置,确定即可
EXCEL快速列出工资条
新建一Excel文件,在sheet1中存放工资表的原始数据,假设有N列。
第一行是工资项目,从第二行开始是每个人的工资。
在sheet2中我们来设置工资条。
根据实际情况,工资条由三行构成,一行对应工资项目,一行对应一个人的工资数据,然后是一个空行用来方便切割。
这样三行构成一个工资条。
工资项目处在行号除以3余数为1的行上;空行处在行号能整除3的行上。
以上两行不难设置,关键是工资数据行,牵扯到sheet1与sheet2中数据的对应,经分析不难看出“sheet1中的数据行=INT((sheet2中的数据行+4)/3)”。
这样我们在sheet2的A1单元格中输入公式“=IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,Sheet1!
A$1,INDEX(Sheet1!
$A:
$N,INT((ROW()+4)/3),COLUMN())))”。
确认后选择A1单元格,把鼠标放在A1单元格的右下角,鼠标变成“+”时,向右拖动鼠标自动填充至N列,这样工资条中的第一行就出来了。
选定A1:
N1,把鼠标放在N1单元格的右下角,鼠标再次变成“+”时,向下拖动鼠标自动填充到数据的最后一行,工资条就全部制作完成了。
该公式运用IF函数,对MOD函数所取的引用行号与3的余数进行判断。
如果余数为0,则产生一个空行;如果余数为1,则固定取sheet1中第一行的内容;否则运用INDEX函数和INT函数来取Sheet1对应行上的数。
最后来设置一下格式,选定A1:
N2设上表格线,空行不设。
然后选定A1:
N3,拖动N3的填充柄向下自动填充,这样有数据的有表格线,没有数据的没有表格线。
把Excel里显示的错误标识全部隐藏起来
Excel经常会因为各种原因出现错误值标识,比如“#DIV/0!
”、“#N/A”等等。
怎样才能使这些错误值标识不再显示出来呢?
一、使用条件格式
首先选中包含错误值的单元格区域,点功能区“开始”选项卡“样式”功能组中的“条件格式”下方的小三角形,在弹出的菜单中点“突出显示单元格规则→其它规则”命令,如图1所示。
打开“新建格式规则”对话框。
在“选择规则类型”列表中点“只为包含以下内容的单元格设置格式”,点“只为满足以下条件的单元格设置格式”下方的下拉按钮,在列表中选择“错误”,如图2所示。
点右下方的“格式”按钮。
打开“设置单元格格式”对话框,单击“字体”选项卡,点“颜色”下拉按钮,为单元格数值指定与背景相同的颜色,如图3所示,确定后就可以了。
我们也可以利用公式来设置条件格式。
首先选中任意一个单元格(是否为错误值均可),如D3单元格,点“条件格式”功能按钮下方的小三角形,在弹出的菜单中点“新建规则”命令,打开如图2所示对话框。
在上方的“选择规则类型”列表中选择“使用公式确定要设置格式的单元格”,在“为符合此公式的值设置格式”下方的输入框中输入公式“=ISERROR(D3)”,如图4所示。
点“格式”按钮,在打开的对话框中设置的格式。
确定后选中该单元格(D3单元格),点功能区“开始”选项卡“剪帖板”功能组中的“格式刷”按钮,刷选其它要设置条件格式的单元格区域,将设置好的条件格式复制过去就可以了。
为数据区域创建动态快照
1.选择一个数据区域。
2.选择菜单命令“编辑>复制”。
3.单击数据区域之外的其它空白单元格,然后按住Shift键的同时选择菜单命令“编辑>粘贴图片链接”,。
这里一定要注意,“粘贴图片链接”命令是一个隐藏的命令,只有在按住Shift键的同时单击“编辑”菜单才能看到。
4.我们可以使用工具栏中的“填充颜色”按钮为其填充背景色,得到如图4所示的结果,这样就看得比较清楚一些。
5.改变左边数据区域中的某些数值或者格式,观察右侧的图片是否会跟着发生变化。
如图5所示,我们为三个单元格加了填充颜色-黄色,并将白龙马三字改为红色,将C11单元格数值改为222222,右侧的图片随之也发生了改变。
这种功能还有一个非常有用的用途。
例如,我们要在一张纸上打印非连续的数据区域,这时就可以为这几个非连续数据区域粘贴为链接的图片,然后将它们放置到一个页面上进行打印即可。
用Excel做数据排序
一、快速排序
如果我们希望对员工资料按某列属性(如“工龄”由长到短)进行排列,可以这样操作:
选中“工龄”列任意一个单元格(如I3),然后按一下“常用”工具栏上的“降序排序”按钮即可。
小提示:
①如果按“常用”工具栏上的“升序排序”按钮,则将“工龄”由短到长进行排序。
②如果排序的对象是中文字符,则按“汉语拼音”顺序排序。
③如果排序的对象是英文字符,则按“英文字母”顺序排序。
二、多条件排序
如果我们需要按“学历、工龄、职称”对数据进行排序,可以这样操作:
选中数据表格中任意一个单元格,执行“数据→排序”命令,打开“排序”对话框(图2),将“主要关键词、次要关键词、第三关键词”分别设置为“学历、工龄、职称”,并设置好排序方式(“升序”或“降序”),再按下“确定”按钮就行了。
三、按笔划排序
对“姓名”进行排序时,国人喜欢按“姓氏笔划”来进行:
选中姓名列任意一个单元格,执行“数据→排序”命令,打开“排序”对话框(参见图2),单击其中的“选项”按钮,打开“排序选项”对话框(图3),选中其中的“笔划排序”选项,确定返回到“排序”对话框,再按下“确定”按钮即可。
小提示:
如果需要按某行属性对数据进行排序,我们只要在上述“排序选项”对话框中选中“按行排序”选项即可。
四、用函数进行排序
有时,我们对某些数值列(如“应扣小计、工资”等)进行排序时,不希望打乱表格原有数据的顺序,而只需要得到一个排列名次。
对于这个问题,我们可以用函数来实现(以“应扣小计”为例):
在“应扣小计”右侧插入一个空白列(J列),用于保存次序(图6),然后选中J2单元格,输入公式:
=RANK(I2,$I$2:
$I$101),然后再次选中J2单元格,将鼠标移至该单元格右下角成“细十字线状”时(这种状态,我们通常称之为“填充柄”状态),按住左键向下拖拉至最后一条数据为止,次序即刻显示出来(图6)。
小提示:
若要升序排序,可在公式最后增加一个“非零”参数,如将上述公式改为:
=RANK(I2,$I$2:
$I$101)。
五、让序号不参与排序
当我们对数据表进行排序操作后,通常位于第一列的序号也被打乱了,如何不让这个“序号”列参与排序呢?
我们在“序号”列右侧插入一个空白列(B列),将“序号”列与数据表隔开。
用上述方法对右侧的数据区域进行排序时,“序号”列就不参与排序了。
小提示:
插入的空列会影响表格的打印效果,我们可以将其隐藏起来:
选中B列(即插入的空列),右击鼠标,再选择“隐藏”选项即可。
Excel中快速输入平方和立方符号
我们在输入面积和体积时,经常要输入平方(上标2)和立方(上标3)。
在Excel中输入这两个上标,有一个非常简便的方法:
在按住Alt键的同时,按下小键盘上的数字“178”、“179”即可输入“上标2”和“上标3”。
注意:
在按住Alt键的同时,试着按小键盘上的一些数字组合(通常为三位),可以得到一些意想不到的字符(例如Alt+137—‰、Alt+177—±等)。
四种方法恢复损坏的Excel文档
将工作簿另存为SYLK格式
如果Excel文件能够打开,那么将工作簿转换为SYLK格式可以筛选出文档的损坏部分,然后再保存数据。
首先,打开需要的工作簿。
在“文件”菜单中,单击“另存为”命令。
在“保存类型”列表中,单击“SYLK(符号连接)(*.slk)”选项(图1),然后单击“保存”按钮。
关闭目前开启的文件后,打开刚才另存的SYLK版本即可。
转换为较早的版本
如果由于启动故障而没有保存Excel工作簿,则最后保存的版本可能不会被损坏。
当然,该版本不包括最后一次保存后对文档所作的更改。
关闭打开的工作簿,当系统询问是否保存更改时,单击“否”。
在“文件”菜单中,单击“打开”命令,双击该工作簿文件即可。
打开并修复工作簿
如果Excel文件根本不能够使用常规方法打开,那么可以尝试Excel2003中的“打开并修复”功能,该功能可以检查并修复Excel工作簿中的错误。
在“文件”菜单中,单击“打开”命令。
通过“查找范围”框,定位并打开包含受损文档的文件夹,选择要恢复的文件。
单击“打开”按钮旁边的箭头,然后单击“打开并修复”即可(图2)。
用Excel查看程序打开工作簿
在用尽各种方法仍不能解决问题的情况下,大家不妨考虑一下使用第三方软件开展恢复工作。
Excel查看程序是一个用于查看Excel工作簿的免费的实用程序,可从微软网站上得到,最新版本为ExcelViewer2003(下载地址:
)。
双击下载文件xlviewer.exe启动安装程序,然后按照说明完成安装。
安装完毕,单击“开始”菜单中的“MicrosoftOfficeExcelViewer2003”即可启动该软件(图3),尝试打开损坏的工作簿。
大家可以在该程序中打开损坏的工作簿,然后复制单元格,并将它们粘贴到Excel的一个新工作簿中。
如果以后要删除Excel查看程序,可通过“控制面板”中的“添加/删除程序”进行删除。
将小写转换为大写
=UPPER(Sheet!
A1)
附送一条小编知道的Word中转换大小写的快捷键:
Shift+F3。
选中字母先,按一次,小写变大写;再按一次,大写变小写。
如果选中的是大小写混排的字母,则转换效果依次为:
首字母大写->全部大写->全部小写
工作表背景图片的妙用
方法是:
单击菜单“格式”→“工作表”→“背景”,然后选择一张图片进行插入,效果如图所示。
在工作表中使用背景图片
为了让背景与工作表内容更和谐,有时需要关闭工作表的网格线显示,因为网格线会显示在背景图片上方,破坏美感。
关闭工作表网络线显示的方法是:
单击菜单“工具”→“选项”,在“选项”对话框的“视图”选项卡中,清除“网格线”复选框,单击“确定”按钮,效果如图所示。
不显示网格线时的工作表背景
在默认情况下,背景图片会平铺在整个工作表中,而且无法被打印出来。
下面介绍的技巧能够巧妙地突破这些限制,帮助用户更灵活地发挥背景图片的威力。
只在特定单元格区域中显示背景
如果不希望背景图片在整个工作表中平铺显示,或者只希望在特定的单元格区域中显示,可以在插入工作表背景以后如下操作。
只在特定单元格区域中显示背景
按
选定需要背景的单元格区域,然后按
打印背景
如果需要把背景和单元格内容一起打印出来,可以如下操作。
使用Excel的摄影功能把需要打印的单元格区域复制为链接图片,粘贴到一张空白工作表上,有关Excel的摄影功能介绍。
右键单击链接图片,在快捷菜单中选择“设置图片格式”命令。
如图所示。
设置链接图片的格式
在“设置图片格式”对话框中选择“颜色与线条”选项卡,单击“填充”→“颜色”选项的下拉箭头,选择“填充效果”,。
设置链接图片的填充效果
在“填充效果”对话框中单击“选择图片”,然后选取一张图片,单击“确定”按钮,如图所示。
在填充效果中选择图片
现在可以在打印预览中看到设置的表格背景了,如图所示。
制作斜线表头
单线表头
如果表头中只需要一条斜线,可以利用Excel的边框设置来画斜线,然后填入文字内容。
在单元格内画斜线的方法如下。
选定单元格,按
在“单元格格式”对话框的“边框”选项卡中,在“边框”区中单击斜线按钮。
画好斜线后在单元格内填充表头内容的方法。
使用文本框
单击菜单“插入”→“文本框”→“横排”(视情况也可以选择“竖排”),在单元格内连续插入两个文本框。
分别编辑两个文本框中的相关文字,并调整文本框大小和文字字号以相互适应。
选定文本框,单击菜单“格式”→“文本框”,在“设置文本框格式”对话框的“颜色与线条”选项卡中,分别将“填充颜色”与“线条颜色”设置为“无”,如图74‑2所示。
设置文本框格式
用鼠标把两个文本框分别移动到画有斜线的单元格的合适位置。
使用上下标
在画有斜线的单元格中输入文字。
把不同部分的内容分别设置为上标或下标格式。
根据需要,还可在不同部分之间插入适量的空格进行美化。
多斜线表头
如果表头中需要画多条斜线,就只能借助自选图形来做了。
选定单元格,设置足够的长度和高度。
单击工具栏中的“绘图”按钮,在“绘图”工具栏中选择“直线”工具,绘制两条或多条直线到单元格中。
使用插入文本框的方式逐个添加表头项目。
完成后的效果。
图片自动更新
在Excel中,使用动态名称与ActiveX控件,能够轻松地实现工作表中的图片自动更新的特殊效果。
本技巧中将以制作一个简单的职员资料表为例,使职员的相片能够随着姓名的改变而改变。
职员资料表工作簿内含有两张工作表,“资料表”工作表用于显示职员的资料,“图片”工作表用于存储所有职员的相片。
“资料表”中的表格。
“图片”工作表中的表格如图83‑2所示,A列是职员的姓名,B列当前是空白,用于存放职员的相片。
图83‑2“图片”工作表
采用以下步骤增加相片。
单击B1,然后单击菜单“插入”→“图片”→“来自文件”,在“插入图片”对话框中选择相应的相片文件,单击“插入”。
因为相片的原始大小比单元格大,所以需要调整大小。
单击相片,然后把光标移动到右下角的圆圈上,当光标变成一个斜线箭头时,往左上方向拖动,如图83‑3所示,直到单元格能容纳整张相片。
图83‑3调整相片大小
为了能使相片更好地被单元格所容纳,还可以使用以下方法。
单击相片,然后单击绘图工具栏的“绘图”→“自动靠齐”→“对齐网格”,如图83‑4所示。
如果绘图工具栏没有显示,可以右键单击工具栏,在弹出的菜单中单击“绘图”项。
图83‑4设置图片自动靠齐网格
双击相片,在“设置图片格式”对话框的“属性”选项卡中,选择“大小位置,随单元格而变”项,单击“确定”按钮。
使用相同的方法为所有职员插入相片,如图所示。
按
$B$1,MATCH(资料表!
$A$2,图片!
$B$1:
图片!
$B$3)-1,0)”,单击“确定”按钮。
切换到“资料表”工作表,右键单击工具栏,在弹出的菜单中选择“控件工具箱”项,在“控件工具箱”工具栏中单击“命令按钮”控件,
单击B3单元格的左上角,然后往右下方向拖动,画出一个符合单元格大小的命令按钮,如
在A2中输入某职员的姓名,如“张三”。
单击命令按钮,把光标定位到编辑栏,将原有内容“=EMBED("Forms.CommandButton.1","")”改为“=pic”。
现在,张三的相片就显示出来了。
为了让相片的大小与单元格大小相匹配,可以调整命令按钮的大小,方法同步骤2。
调整大小后的显示效果。
在A2单元格内输入不同职员的姓名,在B3中就能够自动显示其相片,如图所示。
快速比较不同区域的数值
Excel的多窗口特性能够帮助用户比较不同区域的数据(具体内容请参阅技巧50),但是当需要比较的区域较大时,人工比较不但费时而且准确率不高。
此时如果利用条件格式功能,则能快速而又准确地完成对比工作。
在如图所示的工作表中,源数据和校验数据分别位于A2∶B21和D2∶E21,如果希望标记出与源数据不匹配的校验数据,方法如下。
需要对比的两个数据区域
单击D2并拖动光标到E21以选定区域D2:
E21,单击菜单“格式”→“条件格式”。
在“条件格式”对话框中,单击“条件1
(1)”的下拉箭头,在列表中选择“单元格数值”项,运算符选择“不等于”,在右边的文本框中输入:
=A2
单击“格式”按钮,在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为淡蓝,单击“确定”按钮,如图所示。
设置条件格式对比数据
单击“条件格式”对话框的“确定”按钮,关闭对话框。
从图中可以看到,所有不匹配源数据的校验数据都已经被标出。
不符的数据被条件格式做出标记
Excel制作单元格下拉选择数据
Excel设置数据有效性实现单元格下拉菜单的3种方法
一、直接输入:
1.选择要设置的单元格,譬如A1单元格;
2.选择菜单栏的“数据”→“有效性”→出现“数据有效性”弹出窗口;
3.在“设置”选项中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉菜单”全部打勾→在“来源”下面输入数据,譬如“1,2,3,4,5,6,7,8,9”(不包括双引号,分割符号“,”必须为半角模式)→按“确定”就OK了,再次选择该A1单元格,就出现了下拉菜单。
二、引用同一工作表内的数据:
如果同一工作表的某列就是下拉菜单想要的数据,譬如引用工作表Sheet1的B2:
B5,B2:
B5分别有以下数据:
1、2、3、4,操作如下:
1.选择要设置的单元格,譬如A1单元格;
2.选择菜单栏的“数据”→“有效性”→出现“数据有效性”弹出窗口;
3.在“设置”选项中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉菜单”全部打勾→在“来源”下面输入数据“=$B$2:
$B$5”,也可以按右边带红色箭头的直接选择B2:
B5区域→按“确定”就OK了,再次选择该A1单元格,就出现了下拉菜单。
三、引用不同工作表内的数据(必须用到定义名称):
如果不同工作表的某列就是下拉菜单想要的数据,譬如工作表Sheet1的A1单元格要引用工作表Sheet2的B2:
B5区域,工作表Sheet2的B2:
B5分别有以下数据:
1、2、3、4,操作如下:
1.定义名称:
菜单栏→“插入”→“名称”→“定义”→弹出“定义名称”窗口,在“在当前工作薄中的名称”下面输入“DW”(可以自己随便明明)→“引用位置”下面输入“=Sheet2!
$B$2:
$B$5”,也可以按右边带红色箭头的直接选择B2:
B5区域→按“添加”后再按“确定”完成第一步。
2.选择菜单栏的“数据”→“有效性”→出现“数据有效性”弹出窗口;
3.在“设置”选项中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉菜单”全部打勾→在“来源”下面输入“=“=DW”,“DW”就是刚刚定义好的名称,按“确定”就OK了,再次选择该A1单元格,就出现了下拉菜单
建立分类下拉列表
当我们采取上一节的方法建立下拉列表输入某些数据时,如果此类数据类型很多,显然下拉列表就很长,这样选择起来反而不方便了。
对于这种情况,我们可以将相应的数据进行分类,然后建立一个分类元素下拉列表。
下面,我们以图书名称分类列表为例,介绍一下操作步骤:
启动Excel2007,打开相应的工作簿文档。
切换到一个空白工作表中,将图书名称按类型分别输入到不同列表中,如下图所示。
选中A列,然后将光标定位在“编辑栏”左边的“名称”框中,输入一个名称,并按下“Enter”键确认,如下图所示。
仿照上面操作,给其他类图书自定义一个名称。
按照前面讲过的“建立下拉列表”的操作,在相应工作表中的“图书类型”列的单元格区域中,建立一个图书类型下拉列表。
选中“图书名称”列相应的单元格区域,打开“数据有效性”对话框,选中“序列”选项后,在“来源”下面的方框中输入公式:
如=INDIRECT(B2),单击“确定”返回。
以后我们输入图书名称时,现在“图书类型”列中相应的单元格中选择输入图书类型,然后单击“图书名称”列对应的单元格,我们发现仅“计算机类”图书名称显示在下拉列表中,选择相应
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 实例 培训