巧解excel经常遇到的问题Word下载.docx
《巧解excel经常遇到的问题Word下载.docx》由会员分享，可在线阅读，更多相关《巧解excel经常遇到的问题Word下载.docx（19页珍藏版）》请在冰豆网上搜索。
1）在需要数字或逻辑值时输入了文本，Excel不能将文本转换为正确的数据类型。
确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。
例如：
如果单元格A1包含一个数字，单元格A2包含文本，则公式="
A1+A2"
将返回错误值#VALUE!
可以用SUM工作表函数将这两个值相加（SUM函数忽略文本）：
=SUM（A1:
A2）。
2）将单元格引用、公式或函数作为数组常量输入。
确认数组常量不是单元格引用、公式或函数。
3）赋予需要单一数值的运算符或函数一个数值区域。
将数值区域改为单一数值。
修改数值区域，使其包含公式所在的数据行或列。
3．#DIV/O!
当公式被零除时，将会产生错误值#DIV/O!
在具体操作中主要表现为以下两种原因。
1）在公式中，除数使用了指向空单元格或包含零值单元格的单元格引用（在Excel中如果运算对象是空白单元格，Excel将此空值当作零值）。
修改单元格引用，或者在用作除数的单元格中输入不为零的值。
2）输入的公式中包含明显的除数零，例如：
公式=1/0。
将零改为非零值。
4．#N/A
当在函数或公式中没有可用数值时，将产生错误值#N/A。
如果工作表中某些单元格暂时没有数值，请在这些单元格中输入"
#N/A"
，公式在引用这些单元格时，将不进行数值计算，而是返回#N/A。
5．#REF!
删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他公式引用的单元格中。
当单元格引用无效时将产生错误值#REF！
更改公式或者在删除或粘贴单元格之后，立即单击"
撤消"
按钮，以恢复工作表中的单元格。
6．#NUM！
当公式或函数中某个数字有问题时将产生错误值#NUM！
1）在需要数字参数的函数中使用了不能接受的参数。
确认函数中使用的参数类型正确无误。
2）由公式产生的数字太大或太小，Excel不能表示。
修改公式，使其结果在有效数字范围之间。
7．#NULL！
使用了不正确的区域运算符或不正确的单元格引用。
当试图为两个并不相交的区域指定交叉点时将产生错误值#NULL！
如果要引用两个不相交的区域，请使用联合运算符逗号（，）。
公式要对两个区域求和，请确认在引用这两个区域时，使用逗号。
如果没有使用逗号，Excel将试图对同时属于两个区域的单元格求和，由于A1:
A13和c12:
c23并不相交，它们没有共同的单元格所以就会出错。
让你的Excel单元格自动调整字号技巧
[导读]在使用Excel的过程中，我们往往先把表格根据要求做好，这时单元格的列宽已经固定好了，但有的列字符数目不等。
在使用Excel的过程中，我们往往先把表格根据要求做好，这时单元格的列宽已经固定好了，但有的列字符数目不等。
比如在录入家庭住址时，有的家庭住址比较长，则会使该单元格中的内容不能完全显示在屏幕上，为了让这些单元格中的内容能全部显示在屏幕上，就不得不重新定义单元格的字号。
如果一个一个的调整势必会大大增加我们的工作量，其实，我们可以采用下面的方法让其自动调整字号大小：
选中需要调整字号的单元格区域，依次选择“格式→单元格”菜单命令，出现“单元格格式”对话框，选择“对齐”选项卡，在“文本控制”框中勾选“缩小字体填充”复选框，单击“确定”按钮。
现在，当我们在这些单元格中输入数据时，如果输入的数据长度超过了单元格的宽度，Excel就会自动缩小字符的大小，以使数据全部显示在单元格中。
利用Excel加载宏轻松提取身份证省份信息
[导读]在建立企事业单位员工Excel表格过程中，员工的身份证号、所属省份、性别等信息都是必须要输入的。
如果单位员工众多的话，相关个人信息的输入工作量无疑是相当大的。
在建立企事业单位员工Excel表格过程中，员工的身份证号、所属省份、性别等信息都是必须要输入的。
那么，我们能不能让繁琐的个人信息输入过程变得更轻松以提高自己的工作效率呢？
答案当然是肯定的。
因为只须利用一个Excel加载宏，我们就可以很方便地达到上述目的。
先下载Excel加载宏“根据身份证求省市”，然后将下载的RAR格式同名压缩包解压到硬盘的任意位置（如“C:
\加载宏”目录下），接着运行“Excel2007”（其他版本操作类似）并单击其左上角的圆形按钮，选择“Excel选项”，打开“Excel选项”对话框的“加载项”分支;
再单击该分支下的“转到”按钮，以打开“加载宏”对话框（如图1）;
最后勾选，A处新出现的“根据身份证求省市”复选框并单击“确定”按钮即可。
经过以上步骤的操作，我们就完成了Excel加载宏“根据身份证求省市.xla”在Excel2007中的添加操作，接着打开某个已经输入了姓名信息的“某单位员工信息表.xlsx”工作簿（如图2），接着在图2中的A2单元格里边输入函数“=sfz（B2，“DQ”）”（不包括最外侧引号，下同），回车之后往下拖动填充手柄以完成所有员工“所属省份”户籍地信息的快速录入操作，此时就可以看到效果了，如A处。
当然，依此类推在B2单元格中输入“=sfz（B2，“SR”）”、在C2单元格中输入“=sfz（B2，“XB”）”并往下拖动填充手柄，还可以快速地完成“出生日期”和“性别”等信息的快速录入操作，如B、C两处。
用有效性圈出Excel表格中的指定数据
[导读]考试后老师可能会想把Excel表格中高分用红圈突出显示出来，公司统计时也许也想把特别的数据突出显示出来。
这样的要求用Excel的数据有效性就能够轻松完成。
考试后老师可能会想把Excel表格中高分用红圈突出显示出来，公司统计时也许也想把特别的数据突出显示出来。
下面我以成绩表为例讲解一下如何实现这一要求。
（注：
本例要求把语文成绩在120分以上的用红圈突出显示。
）
屏显操作步骤：
第一步：
打开成绩表→选择语文成绩→单击菜单栏上的“数据”按钮→选择“有效性”。
第二步：
在弹出的“数据有效性”对话框中，在“允许”一栏中选择“整数”→在“数据”下面框中选择“介于”→最小值输入0→最大值输入120→确定。
第三步：
单击菜单栏上的“工具”按钮→选择“公式审核”→选择显示“公式审核”工具栏。
第四步：
单击“圈释无效数据”按钮。
这样语文分数在120分以上的就被圈上了一个红圈，起到了突出显示的作用。
但这些圈只能在电脑中显示出来并不能打印出来。
有的老师想把它打印出来，笔者经过探索，找到一种可行的办法，下面把这种方法介绍一下。
打印操作步骤：
选择表格→按住Shift键不放单击菜单栏上的“编辑”按钮→选择“复制图片”→确定。
单击菜单栏上的“编辑”按钮→选择粘贴图片→把原数据表删除→调整一下复制的数据表的位置就行了。
注：
在进行第一步和第二步的操作过程中要始终按住Shift键不放，否则操作不成功。
快速加入Excel隔空行让表格更加清爽
当我们在处理表格时，如果密密麻麻的排成一片，观看起来非常不便。
此时为表格加入隔空行无疑可以让表格更加清爽，传统方法可用Ctrl键依次单击行按钮，然后单击右键，选择快捷菜单中的“插入”命令来添加，但是如果处理的表格成千上万，那么必须要一个更有效率的方法来实现。
下面我们就一起做做吧。
1.添加辅助列单元格
要实现快速添加隔空行，最高效的方式就是借助辅助列单元格进行实现，在数据区外，找到一个空列，然后在第一个单元格中随意输入一个数字，如数字“1”。
将“1”下面的单元格置空。
然后选定这两个单元格，按住Ctrl键的同时拖动填充柄向下填充，直到表格需要添加隔空行的末尾。
2.对辅助列进行编辑设置
将辅助列下拉到结束为止后，保持选中状态，然后保持此时的选中状态，在工具栏中选择“编辑”→“定位”，打开“定位”对话框后，选择“定位条件”功能项，然后将“定位条件”中的设置更改为“空值”即可。
3.插入命令中选择“整行”
设置完毕后，点击确认键，原选择区域中的空单元格将被选中。
在选中单元格上单击右键，选择“插入”命令，在功能中选择“整行”，单击“确定”按钮后，Excel将自动在辅助列的每个空格行中加入一个空行。
4.选择常量添加所有内容的隔空行
选择所有操作过的单元格，然后在工具栏中执行“编辑”→“定位”，打开“定位”对话框，选择“定位条件”，在“定位条件”对话框中选择“常量”。
“确定”后原选择区域中的“1”单元格将全部被选中。
此时再在选中单元格上单击右键，选择“插入”命令，再在对话框中选择“整行”，“确定”后，每个选择单元格所在行上将添加一空行。
在制作最后，将先前添加的辅助列删除，隔空行就全部做好了
妙用Excel语音校对功能实现数据核对
单位经常用Excel来统计分点压力数据，每次统计完成后还要对上百个中低压数据进行校对。
如果用眼睛校对，很容易出差错。
而使用Excel的“文本到语音”功能，让软件自动报数据不仅不会出错还减轻了劳动量。
单位经常用Excel来统计分点压力数据，每次统计完成后还要对上百个中低压数据进行校对。
首先打开Excel，点击视图→工具→语音→显示文本到语音工具栏，由于这个功能在安装Excel时不是默认的，所以在第一次使用时会提示插入Office安装光盘来安装这个功能。
安装后打开“文本到语音”工具栏，先在数据文件中用鼠标选择要朗读的第一个数据，接着根据数据的排列情况来选择是“按行”还是“按列”来进行朗读，设置完成后点击工具栏最左面的“朗读单元格”按钮就可以了。
在朗读的时候被朗读到的单元格会以选中状态显示，而且还可以流利地朗读中文，英文是一个字母一个字母朗读的，这样就可以校对英文单词的拼写是否正确（如图）。
该功能还可以一边输入数据一边来进行语音校对，只要点击一下工具栏最右面的“按回车开始朗读”按钮，使其为选中状态，这样在完成一个单元格的输入后按回车，Excel就会自动来朗读这个单元格中的内容。
朗读默认是男声，如果不喜欢，可以通过系统里的设置改为女声，打开控制面板中的“语音”项，在里面的“语音选择”来选择一下语音，然后点击下面的“预览声音”就可以听效果了，而且还可以设置语音的朗读速度，完成后点击确定按钮就可以了。
Excel财务应用实例：
按职称分级计算工资
[导读]经过一些摸索，我找到两种方法在Excek中高效解决按职称分级加工资这个问题。
本月公司员工的工资要调整，经理要求按每个人的职称来加工资。
具体要求：
职称为初级的本月加20元，职称为中级的本月加50元，职称为高级的本月加70元。
但是因为公司人数太多，逐个添加不但麻烦还容易出错。
经过一些摸索，我找到两种方法在Excek中高效解决按职称分级加工资这个问题。
方法一：
运用条件函数If
打开工资表，在实发工资下面输入=IF（B2=“初级”，G2+20，IF（B2=“中级”，G2+50，IF（B2=“高级”），G2+70））。
（B2代表职称;
G2代表应发工资，图1）。
回车，向下快速填充即可。
方法二：
运用VisualBasic编辑器
工具→宏→VisualBasic编辑器。
在Sheet!
上右击→插入→模块。
输入如下代码
以下是引用片段：
Functionsss（tatol，ss）
Ifss=“初级”Then
sss=tatol+20
ElseIfss=“中级”Then
sss=tatol+50
ElseIfss=“高级”Then
sss=tatol+70
EndIf
EndFunction
代码解释：
sss代表函数名;
tatol代表实发工资;
ss代表应发工资。
关闭VisualBasic编辑器→在实发工资下输入=sss（G2，B2）→回车向下填充（图2）。
快速为Excel工作簿创建工作表目录的方法
当一个文档中的工作表达到一定数量时，要想找到需要的工作表就变得很麻烦了。
此时若能建立一张“目录”工作表显示所有工作表的名称和链接，事情将会简单很多。
Excel工作簿工作表目录标志设计笔记本广告设计
我们经常把同类相关Excel工作表集中保存在同一文档中，以便于在各表格间进行引用、查看。
下面介绍一种可以快速为Excel工作簿创建工作表目录的方法。
定义名称
打开Excel2007，右击第一张工作表标签选择“重命名”，把它重命名为“目录”工作表。
选中B1单元格，切换到“公式”选项卡，单击“定义名称”，在弹出的“新建名称”窗口中输入名称“工作表名”，在引用位置中则输入公式=INDEX（GET.WORKBOOK
（1），$A1）&
T（NOW（）），单击确定即可定义出一个名为“工作表名”的名称。
公式中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:
ProgramFilesMicrosoftOfficeOffice12XLSTART文件夹下，关闭Excel2007退出。
若你的Office不是按默认路径安装，请按实际安装路径修改。
三秒创建目录
以后要为工作簿创建目录就简单了，只要用Excel2007打开要创建目录的工作簿，在第一张工作表的标签上右击选择“插入”，在“插入”窗口中双击选择“目录”，即可在第一张工作表前插入一张“目录”工作表，并显示出所有工作表目录。
这操作有3秒就够了吧？
在“目录”工作表中，可通过对目录进行筛选、排序、查找来快速找到工作表名，然后单击工作表名即可打开相应工作表。
创建目录后，在这个工作簿中增加、删除工作表或者修改工作表名称，“目录”工作表中的工作表目录都会自动更新。
此外，前面我们只复制了300行目录公式，因此工作表总数超过300个时，超出的工作表名就不会显示了，得把“目录”工作表中A1：
B1的公式再向下复制填充才行。
由于宏表函数GET.WORKBOOK
（1）是通过宏功能起作用的，所以插入了工作表目录的文档最后都必须以“Excel启用宏的工作簿（*.xlsm）”格式另存，这样下次打开时才能正常显示工作表目录。
此外，打开工作簿时，Excel2007默认会禁用宏，得单击警告栏中的“选项”按钮，选中“启用此内容”单选项，确定后才能显示工作表目录。
锁定Excel表中的多个指定单元格
对于某个Excel工作簿中的数据，如果我们仅仅是希望别人查看而不希望其随意地修改的话，为该工作簿添加一个密码无疑是个很简单实用的方法。
Excel单元格标志设计笔记本广告设计
对于某个Excel工作簿中的数据，如果我们仅仅是希望别人查看而不希望其随意地修改的话，为该工作簿添加一个密码无疑是个很简单实用的方法。
在实际应用中，我们在对Excel工作簿中某些指定单元格中的数据加以保护的同时，却还得允许别人可以修改其他单元格中的数据。
那么，这又该如何操作呢？
接下来，笔者就以某Excel工作簿为例（只保护C列和D列单元格中的数据），给大家介绍一下如何来实现。
打开工作簿并切换到“Sheet1”工作表下;
接着选中该工作表中的所有单元格并按下“Ctrl+1”快捷键，打开“自定义序列”对话框的“保护”选项卡（图1）;
最后取消“锁定”复选框并单击“确定”按钮。
天极软件编注：
这一步的目的主要是先将所有的单元格都取消锁定。
下一步再对指定单元格做锁定设置。
“薪酬表.xlsx”工作簿的“Sheet1”工作表中，先选中需要进行保护的C列单元格和D列单元格（也可以是其他连续或非连续的多个单元格）;
接着按下“Ctrl+1”快捷键，打开“自定义序列”的“保护”选项卡，同时勾选图1，A、B处所示的“锁定”和“隐藏”复选框并单击“确定”按钮。
然后在Excel2007主界面中单击“审阅”选项卡中的“允许用户编辑区域”按钮，打开一个与之同名的对话框并单击其中的“新建”按钮，以弹出“新区域”对话框。
再在“新区域”对话框“区域密码”下边的文本框内输入密码并单击“确定”按钮。
最后在新弹出的“确认密码”对话框中重新输入一次密码就可以了。
完成上述操作后，单击Excel2007主界面“开始”选项卡中的“格式”按钮，在弹出的下拉菜单中选择“保护工作表”以打开“保护工作表”对话框，然后按照提示在该对话框“取消工作表保护时使用的密码”下边的文本框内输入新设定的密码（也可与第一次为保护某些单元格设置的密码相同）并单击“确定”按钮即可。
这样一来，我们就完成了为“薪酬表.xlsx”工作簿“Sheet1”工作表中的C列和D列单元格添加只读密码的所有操作。
当然，如果对“Sheet1”工作表中的C列和D列单元格以外其他任意一个单元格执行编辑操作时，则无须输入密码。
用Excel函数将英文基数词转换成序数词
将英文的基数词转换成序数词是一个比较复杂的问题。
因为它没有一个十分固定的模式：
大多数的数字变成序数词都是使用的“th”后缀，但是以“1”、“2”、“3”结尾的数字却分别是以“st”、“nd”和“rd”结尾的。
而且，“11”、“12”、“13”这3个数字又不一样，它们仍然是以“th”结尾的。
因此，实现起来似乎很复杂。
其实，只要我们理清思路，找准Excel函数，只须编写一个公式，就可轻松将英文基数词转换成序数词。
公式如下：
“=A2&
IF（OR（VALUE（RIGHT（A2，2））={11，12，13}），″th″，IF（OR（VALUE（RIGHT（A2））={1，2，3，}，CHOOSE（RIGHT（A2），″st″，″nd″，″rd″），″th″））”。
该公式尽管一长串，不过含义却很明确：
如果数字是以“11”、“12”、“13”结尾的，则加上“th”后缀;
如果第1原则无效，则检查最后一个数字，以“1”结尾使用“st”、以“2”结尾使用“nd”、以“3”结尾使用“rd”;
如果第1、第2原则都无效，那么就用“th”。
如此，基数词和序数词的转换就变得相当轻松和快捷了。
巧用Excel批量生成和打印考场座位标签
马上就是各个学校期中考试的时间了。
为了使各类考试的组织工作能顺利地进行，让考生顺利地找到自己的考场座位坐下来安静待考是非常重要的。
这其中打印考场座位标签成为一项必不可少的工作。
许多朋友都是用Excel进行考务管理工作的，那如何利用Excel来实现批量座位标签的打印呢?
为了方便给大家介绍，接下来笔者以4个班级的考生数据为例介绍下实现的过程。
考生名册的生成
根据座位标签中需要打印的项目，笔者设计了“考生名册”工作表。
为了体现考试的公平，大家可事先将考生按班级号“1～4”循环的顺序整理好，“座位号”也可根据考场的大小设计成“1~30”的循环号，即每个考场30个考生。
考场座位标签报表的设计
考场座位标签的设计要本着美观、实用、节省纸张和便于剪裁的原则，结合实际使用的经验，笔者在一张A4的打印纸上设计了30个座位标签（1行3个，共10行，如下图）。
报表的设计工作在“桌贴”工作表中进行。
开始时可先设计一个座位标签，然后对该标签中要调用的数据进行反复测试。
调用数据的方法是（以第1个考生的座位标签为例），在第1个考生的“准考证号”所在的B3单元格
升级会员 