Excel表格常见问题处理技巧
1.让不同类型的数据用不同的颜色显示
在工资表中，如果要显示工资总额大于或等于2000元的“红色”，工资总额大于或等于1500元的“蓝色”，工资总额低于1000元的“棕色” , 和其他在“棕色”“黑色”显示中，我们可以这样设置。
1、打开“工资表”工作簿，选中“工资总额”所在列，执行“格式→条件格式”命令，打开“条件格式”对话框。 单击第二个框右侧的下拉按钮，选择“大于或等于”选项，并在下面的框中输入值“2000”。 单击“格式”按钮，打开“设置单元格格式”对话框，将“字体”的“颜色”设置为“红色”。
2、按“添加”按钮，按上述操作设置其他条件（大于等于1500，字体设置为“蓝色”；小于1000，字体设置为“棕色”）。
3、设置完成后，按“确定”键。
看看薪资表，总薪资数据是不是按照你的要求用不同颜色显示的？
2.创建类别下拉列表填充项
我们经常需要在表格中输入公司名称。 为了保持名称的一致性，我们使用“数据有效性”功能来创建类别下拉列表填充项。
1、在Sheet2中，将企业名称分门别类（如“工业企业”、“商业企业”、“个体企业”等）填入不同的栏目，建立企业名称数据库。
2、选中A栏（“工业企业”名称所在栏），在“名称”栏中输入“工业企业”字样，按“回车”键确认。
按照上述操作，将B列和C列……分别命名为“商业企业”和“个体企业”……
3、切换到Sheet1，选中需要进入“企业类别”的列（如C列），执行“数据→有效性”命令，打开“数据有效性”对话框。 在“设置”选项卡中，点击“允许”右侧的下拉按钮，选择“串口”选项，在下方的“来源”框中分别输入“工业企业”、“商业企业”、“个人” Enterprise”… …序列（每个元素用逗号分隔），确认退出。
然后选择需要输入公司名称的列（如D列），然后打开“数据有效性”对话框，选择“序列”选项，在“来源”框中输入公式：=INDIRECT(C1 ), 确认退出。
4、选中C列任意一个单元格（如C4），点击右边的下拉按钮，选择相应的“企业类别”填写该单元格。 然后选中该单元格对应的D列单元格（如D4），点击下拉按钮，从相应类别的企业名称列表中选择需要的企业名称填入该单元格。
提示：如果以后打印报表时不需要打印“企业类别”栏目，可以选中该栏目，单击鼠标右键，选择“隐藏”选项，隐藏该栏目。
3、新建“常用文档”菜单
在菜单栏新建一个“常用文档”菜单，将常用的工作簿文档添加进去，随时调用。
1、在工具栏空白处单击鼠标右键，选择“自定义”选项，打开“自定义”对话框。 在“命令”选项卡中，选择“类别”下的“新建菜单”项，然后将“命令”下的“新建菜单”拖到菜单栏中。
按“更改选择”按钮，在弹出菜单的“名称”框中输入名称（如“常用文档”）。
2. 然后在“Category”下选择一个项目（比如“Insert”选项），在右边的“Command”下选择一个项目（比如“Hyperlink”选项），拖到新菜单（一般是used documents) ，并按照上述操作为其命名（如“工资表”等），创建第一个工作簿文档列表的名称。
重复以上操作，增加几个文档列表名称。
3、在“常用文档”菜单中选择一个菜单项（如“工资表”等），单击鼠标右键，在弹出的快捷菜单中选择“分配超链接→打开”选项打开“分配超链接”对话框。 通过按“搜索范围”右侧的下拉按钮，导航到相应的工作簿（例如“salary.xls”）文件夹，然后选择工作簿文档。
重复上述操作以超链接菜单项与其对应的工作簿文档。
4、以后需要在“常用文档”菜单中打开工作簿文档时，只需展开“常用文档”菜单，点击相应的选项即可。
提示：虽然我们将“超链接”选项拖到“常用文档”菜单中，但不影响“插入”菜单中的“超链接”菜单项和“常用”菜单中的“插入超链接”按钮的功能工具栏。
4.制作“专业符号”工具栏
在编辑专业表格时，经常需要输入一些特殊的专业符号。 为了方便输入，我们可以制作一个属于自己的“专业符号”工具栏。
1、执行“工具→宏→录制新宏”命令打开“录制新宏”对话框，输入宏名？ 比如“fuhao1”？ 并将宏保存在“个人宏工作簿”中，然后点击“确定”开始录制。 选择“录制宏”工具栏上的“相对引用”按钮，然后将需要的特殊符号输入到一个单元格中，再单击“录制宏”工具栏上的“停止”按钮即可完成宏录制。
按照以上操作，将其他特殊符号的输入“宏”一一记录下来。
2、打开“自定义”对话框，点击“工具栏”选项卡中的“新建”按钮，弹出“新建工具栏”对话框，输入名称-“专业符号”，确定后出现在工作区中出现一个工具栏。
切换到“命令”选项卡，选择“类别”下的“宏”，将“命令”下的“自定义按钮”项拖到“专业符号”栏（有多少按钮就拖多少特殊符号）。
3. 选择其中一个“自定义按钮”，并以第二个秘法的第1点命名。
4、右击一个命名的按钮，在弹出的快捷菜单中选择“指定宏”选项，打开“指定宏”对话框，选择对应的宏（如fuhao1等），确定退出。
重复此步骤将按钮与相应的宏链接起来。
5、关闭“自定义”对话框，即可使用“专业符号”工具栏，像使用普通工具栏一样，快速将专业符号输入到单元格中。
5.使用“视图管理器”保存多个打印页面
有些工作表经常需要打印不同的区域，使用“视图管理器”。
1、打开需要打印的工作表，用鼠标拖放不需要打印的行（或列）标记，选中后单击鼠标右键，在出现的快捷菜单中，选择“隐藏”选项，将不需要打印的行（或列）隐藏起来。
2、执行“视图→视图管理器”命令，打开“视图管理器”对话框，点击“添加”按钮，弹出“添加视图”对话框，输入名称后（如“上报表") ，单击确定按钮。
3、显示隐藏的行（或列），重复上述操作“添加”其他打印视图。
4、以后需要打印某个表格时，打开“视图管理器”，选择要打印的表格名称，点击“显示”按钮，工作表会立即按照预先设置的显示出来-设置界面。 简单的设置和排版，按下工具栏上的“打印”按钮，一切OK。
6.让数据按需排序
如果你想按部门对员工进行排序，而这些部门名称的相关信息不是按字母顺序或笔画顺序排列的，怎么办？ 您可以使用自定义序列进行排序。
1、执行“格式→选项”命令，打开“选项”对话框，进入“自定义顺序”选项卡，在“输入顺序”下的框中输入部门排序顺序（如“组织、舰队、第一workshop , Workshop 2, Workshop 3”等），点击“添加”和“确定”退出。
2、选中“部门”栏任意单元格，执行“数据→排序”命令，打开“排序”对话框，单击“选项”按钮，弹出“排序选项”对话框，按在里面的下拉按钮，选择 按两次“确定”按钮，返回到刚才自定义的顺序，所有的数据都会按要求排序。
7.完全隐藏数据
工作表的某些单元格中的内容不想被查看者看到，因此必须将其隐藏。
1、选中需要隐藏内容的单元格（范围），执行“格式→单元格”命令，打开“单元格格式”对话框，选择“数字”选项卡“类别”下的“自定义”选项, 然后在输入 ";;;" （三个英语国家的分号）在右侧“类型”下的框中。
2、切换到“保护”选项卡，选择“隐藏”选项，按“确定”键退出。
3、执行“工具→保护→保护工作表”命令，打开“保护工作表”对话框。 设置好密码后，点击“确定”返回。
这样设置后，上述单元格中的内容将不再显示，即使使用Excel的透明功能也无法使其可见。
提示：在“保护”选项卡下，请不要清除“锁定”前复选框中的“∨”号，以免他人删除您的隐藏数据。
8.让中英文输入法智能出现
编辑表格时，有些单元格需要输入英文，有些单元格需要输入中文。 反复切换输入法不方便。 为什么不设置一下，让输入法智能调整呢？
选中需要输入中文的单元格区域，执行“数据→有效性”命令，打开“数据有效性”对话框，切换到“输入模式”选项卡，按下“模式”右侧的下拉按钮”，选择“打开”选项，“确定”退出。
以后选中单元格区域任意一个需要输入中文的单元格时，自动打开中文输入法（输入法列表中第一个中文输入法），选中其他单元格时，中文输入法自动关闭。
9.让自动更正输入统一的文字
你是不是经常为输入一些固定的文字而烦恼，比如《电脑新闻》？ 然后继续阅读。
1、执行“工具→自动更正”命令，打开“自动更正”对话框。
2、在“替换”下方输入框输入“pcw”（或其他字符，“pcw”小写），在“替换为”下方输入框输入“电脑新闻”，点击“添加”，点击“确定” “ 纽扣。
3、以后如果需要输入上面的文字，只需要输入“pcw”字符即可？ 这时候可以忽略“pcw”?的大小写，再进行确认。
10.自定义Excel中的函数
Excel功能虽然丰富，但并不能满足我们所有的需求。 我们可以自定义一个函数来完成一些特定的操作。 接下来我们定义一个计算梯形面积的函数：
1、执行“工具→宏→Visual Basic编辑器”菜单命令（或按“Alt+F11”快捷键）打开Visual Basic编辑器窗口。
2、在窗口中执行“Insert→Module”菜单命令，插入一个新的模块——Module 1。
3、在右侧的“代码窗口”中输入以下代码：
函数 V(a,b,h)V = h*(a+b)/2EndFunction
4.关闭窗口，自定义功能完成。
您以后可以像使用内置函数一样使用自定义函数。
提示：通过上述方法自定义的函数只能在对应的工作簿中使用。
11.页眉下的衬里图片
添加到工作表中的背景是在整个工作表下划线的，难道只能在表头下划线吗？
1、执行“格式→工作表→背景”命令，打开“工作表背景”对话框，选中需要作为背景的图片，按“插入”按钮，将图片划线到整个工作表下方。
2、在按住Ctrl键的同时，用鼠标拖放不需要背景图的单元格（区域），同时选中这些单元格（区域）。
3、按下“格式”工具栏上“填充颜色”右侧的下拉按钮，在出现的“调色板”中选择“白色”。 这样设置后，剩下的单元格都衬上了图片excel超链接无法打开指定的文件，而上述选中的单元格（区域）则没有衬上图片（实际上是被“白”盖住的图片）。
提示？ 单元格内衬的图片不支持打印。
12.使用连字符“&”组合文本
如果我们想将多列的内容合并为一列，不需要使用函数，一个小连字符“&”就可以搞定（这里假设B、C、D列合并为一列柱子）。
1、在D列后插入两个空列（E列、F列），然后在D1单元格中输入公式：=B1&C1&D1。
2、再次选中D1单元格，用“填充柄”将上面的公式复制到D列下面的单元格中，B、C、D列的内容就会合并到E列对应的单元格中。
3、选择E列，执行“复制”操作，再选择F列，执行“编辑→选择性粘贴”命令，打开“选择性粘贴”对话框，选择“值”选项，按“确定”按钮 ，E 列的内容（不是公式）被复制到 F 列。
4.删除B、C、D、E列，完成合并工作。
提示：完成步骤1和2的操作后，已经实现了合并效果，但是如果此时删除B、C、D列，公式就会出错。 因此必须进行第三步，将公式转化为常量“值”。 学生成绩单
经常有朋友问到“如何打印成绩条”之类的问题，很多人都是用录制宏或者VBA的方法来实现，这对初学者来说比较困难。 出于这个考虑，这里我给出一个方便的方法，用函数来实现。
这里假设学生成绩保存在Sheet1的A1到G64单元格范围内，其中第一行是标题，第二行是学科名称。
1、切换到Sheet2，选中A1单元格，输入公式：=IF(MOD(ROW(), 3)=0, "", IF(0MOD?ROW(), 3(=1, sheet1!Aū, INDEX (sheet1!$A:$G, INT(((ROW()+4)/3)+1), COLUMN())))。
2、再次选中A1单元格，使用“填充柄”将上述公式复制到B1~G1单元格； 然后，同时选中A1到G1单元格区域，用“填充柄”将上面的公式复制到A2到G185单元格中。
至此，评分条基本成型，下面进行简单修改。
3、调整好行高和列宽后，同时选中A1到G2（第一个学生成绩条区域）的单元格范围，点击“边框”右侧的下拉按钮格式”工具栏，以及后面出现的边框列表中，选择“所有边框”选项，为选中区域添加边框（如果不需要边框，可以跳过这一步和后面的操作）。
4、同时选中A1到G3单元格，单击“常用”工具栏上的“格式刷”按钮，然后按住鼠标左键，从A4拖动到G186单元格，为所有等级栏添加边框。
按“打印”按钮打印得分表。
十三、Excel帮你选择功能
在使用函数处理数据时，常常不清楚使用哪个函数合适。 Excel 的“搜索功能”功能可以帮助您缩小范围并选择合适的。
执行“插入→函数”命令，打开“插入函数”对话框，在“搜索函数”下方的框中输入要求（如“计数”），然后单击“开始”按钮，系统将立即匹配“计数”选择相关函数，并显示在“选择函数”下方的列表框中。结合查看相关帮助文件，可以快速确定需要的函数。
14.同时查看不同工作表多个单元格中的数据
有时，我们在编辑一个工作表（Sheet1）时，需要查看其他工作表（Sheet2、Sheet3……）中某个单元格的内容，这可以使用Excel的“监视窗口”功能来实现。
执行“视图→工具栏→观察窗”命令，打开“观察窗”，单击其中的“添加观察点”按钮，展开“添加观察点”对话框，鼠标选中要勾选的单元格，然后单击“添加”按钮。 重复上述操作添加其他“观察点”。
以后无论在哪个工作表，只要打开“监控窗口”，就可以查看到所有监控单元格中的数据及相关信息。
15、快速给单元格画边框
在以前的 Excel 2002 版本中，给单元格区域添加边框很麻烦。 Excel 2002 对该功能进行了新的扩展。
单击“格式”工具栏上“边框”右侧的下拉按钮，在弹出的下拉列表中选择“绘制边框”选项，或执行“视图→工具栏→边框”命令展开“边框”工具栏。
点击工具栏最左侧的下拉按钮，选择一种边框样式，然后将其拖放到需要添加边框的单元格区域，即可快速为对应的单元格区域绘制边框。
Tips： ①画错边框也没关系。 选择工具栏上的“擦除边框”按钮，然后拖动错误的边框即可去除错误的边框。 ②如果需要绘制不同颜色的边框，可以先点击工具栏右侧的“线条颜色”按钮，在弹出的调色板中选择需要的颜色，然后绘制边框。 ③ 该函数还可以在单元格中画斜线。
16.控制特定单元格中输入文本的长度
你能想象一下，当你在一个应该输入四位数的单元格中填入一个两位数的单元格，或者在一个应该输入文本的单元格中输入一个数字时，Excel可以自动判断、分析并且如果弹出警告！ Excel实现这个功能并不难。
例如，我们将光标定位在一个登记“年”的单元格中，为了统一输入和计算方便，我们希望“年”用一个四位数字来表示。 所以，我们可以点击“数据”菜单的“有效性”选项。 在“设置”卡片中“有效条件”的“允许”下拉菜单中选择“文本长度”。 然后在“数据”下拉菜单中选择“等于”，“长度”为“4”。 同时，我们再次来到“错误警告”卡片，将“输入无效数据时显示的错误警告”设置为“停止”，并填写“非法输入文本！” 分别在“标题”和“错误消息”列中。 以及“请输入四位数年份”字样。
显然，如果有人在单元格中输入了四位数以外的值，Excel会弹出一个警告对话框，告诉你错误原因，你可以继续输入值，直到你输入了该值正确的“风格”。 太棒了，对吧？ 其实在Excel的“数据有效性”判断中，有很多特殊类型的数据格式可以选择，比如“文本类型”、“序列大小”、“时间距离”，如果你有兴趣，不妨自己制作自己主张，自己设计一个测试标准，让你的Excel展现与众不同的光彩。
十七、分组填充多个表格的固定单元格
我们知道，每次打开 Excel，软件都会默认打开多个工作表。 由此可见，Excel除了具有强大的单表处理能力外，更适合在多个相互关联的表中协调工作。 当然，要协调关联，首先需要同步输入。 因此，在很多情况下，需要同时在多个工作表中的同一个单元格中输入相同的内容。
那么如何分组编辑表格呢？ 首先，我们点击第一个工作表的标签名“Sheet1”，然后按住Shift键，点击最后一个表格的标签名“Sheet3”（如果我们要关联表格不在一起的话，可以按并按住 Ctrl 键进行选择）。 此时我们看到Excel的标题栏出现了“工作组”字样，我们就可以对工作组进行编辑了。 在需要一次输入多张表格内容的单元格中随便写点东西，我们发现对应的内容显示在“工作组”中所有表格的相同位置。
然而，仅仅同步输入是不够的。 比如我们需要统一改变多个表中同一位置数据的格式怎么办？ 首先，我们要改变第一个表的数据格式，然后点击“编辑”菜单中的“填充”选项，再点击子菜单中的“选择同组工作表”。 这时Excel会弹出“填充分组工作表”的对话框，这里我们选择“格式”项，点击“确定”后，同组所有表中该位置的数据格式都会改变.
十八、改变文字的大小写
在Excel中，对表格处理和数据操作提供最强大支持的不是公式或数据库，而是函数。 不要以为Excel中的函数只是针对数字的。 其实，只要是写在表格里的，Excel都有专门的编辑功能。 例如改变文本的大小写。
在Excel 2002中，至少提供了三个与文本大小写转换相关的函数。 它们是：“=UPPER(source data grid)”，将所有文本转换为大写； "=LOWER(source data grid)"，将所有文本转为小写； "=PROPER(source data grid)", convert text 转换为“proper”大小写，比如将每个单词的首字母大写等。比如我们在一个表格的A1单元格中输入小写的“excel”，然后在目标单元格输入“=UPPER(A1)”，回车后结果为“EXCEL”。 同样，如果我们在A3单元格中输入“mr.weiwei”，然后在目标单元格中输入“=PROPER(A3)”，那么我们得到的结果就是“Mr.Weiwei”。
19.提取字符串中的特定字符
除了直接输入外，从已有的单元格内容中提取特定字符输入绝对是一种省时省事的方法，尤其是对于一些风格相似的信息，比如员工名单、家乡等信息。
如果我们要快速提取A4单元格的标题，最好使用“=RIGHT（源数据单元格，提取的字符数）”函数，意思是“从A4单元格最右边的字符中提取2个字符”输入到这个位置。 当然，如果要提取名字，需要用到“=LEFT（源数据格子，提取的字符数）”函数。 还有一种情况，我们不从左右两端开始，而是直接从数据中间提取几个字符。 比如我们要从A5单元格中提取“武汉”这个词，只需要在目标单元格中输入“=MID(A5,4,2)”即可。 意思是：提取A5单元格中第4个字符之后的两个字符，即第4个和第5个单词。
20.将基数转换为序数
将英语基数词转换成序数词是一个比较复杂的问题。 因为它没有非常固定的模式：大多数数字在变成序数时都使用“th”后缀，但一般以“1”、“2”、“3”结尾的数字分别以“th”结尾。 st", "nd", "rd"。而且，"11", "12", "13"这三个数虽然不一样，但还是以"th"结尾。所以实现起来好像很复杂，其实，只要理清思路，找出函数，我们只需要写个公式就可以轻松换算，不信请看：“=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"; ②如果第一原则无效excel超链接无法打开指定的文件，检查最后一个数，"1"末尾用"st"，"2"末尾用"nd"，"3"末尾用"rd" ”；③如果第一原则和第二原则无效，则用“th”。因此，基数与序数之间的转换就这么简单快捷地实现了。
21.用特殊符号完成位数
和财务打过交道的人都知道，在填写账户时有一种惯用的“安全填写法”，即在金额处填空，或者在支付数据前加“$”等符号。 其实Excel中也有类似的输入法，那就是“REPT”功能。 它的基本格式是“=REPT(“特殊符号”，填充数字)”。
例如，我们要在A2单元格的数字末尾使用“#”来填充最多16位数字，只需要将公式改为“=(A2&REPT(″#”,16-LEN(A2) ))”即是；如果我们要将A3单元格中的数字用“#”从左边填到16位，需要改成“=REPT(″#”,16-LEN(A3))) &A3"; 另外，如果我们想用“#”从两边填充A4中的值，我们需要将其改为“=REPT(″#”,8-LEN(A4)/2)&A4&REPT(″#″) 8-LEN(A4 )/2)”；如果还是觉得不够专业，如果想在A5单元格数字的最前面加一个“$”符号，那就改成：“=(TEXT (A5,″$#,##0.00″( &REPT(″#″,16-LEN(TEXT(A5,″$#,##0.00″)))))”一定能满足你的要求。
22.创建文本直方图
除了重复输入，“REPT”功能的另一个衍生应用是可以直接在工作表中创建由纯文本组成的直方图。 它的原理也很简单，就是利用特殊符号的智能重复，根据指定单元格中的计算结果来展示不同长度的比较效果。
比如我们先做年度资产负债表，然后在柱状图中用“E列”作为“预算内”月份的显示区域，用“G列”作为“超过”的显示区域-预算”在直方图中。 然后根据表中已有结果“D列”的值，用“Wingdings”字体的“N”字符表示。 具体步骤如下：
在E3单元格中写入公式“=IF(D30,REPT(″n″,ROUND(D3*100,0)),″″)”，将填充柄拖至G14。 可以看到已经呈现了一个没有使用Excel图表功能的纯文本直方图，方便直观，简单明了。
23.计算单元格中的单词总数
有时，我们可能对单元格中的字符数感兴趣，需要计算单元格中的单词总数。 为了解决这个问题，除了使用“SUBSTITUTE”函数的虚计算外，还使用了“TRIM”函数来删除空格。 例如，如果单词“多少个单词？” 在单元格A1中输入，那么我们可以使用下面的表达式来帮助：
“=IF(LEN(A1)=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),″,″,″″))+1)”
这个公式的意思是先用“SUBSTITUTE”函数新建一个字符串，用“TRIM”函数删除字符之间的空格，然后计算这个字符串与原字符串的位数差，得到“空格”数量，最后加上空格数 + 1 得到单元格中的字符数。
24. 欧元的兑换
这是Excel 2002 中的一个新工具。如果您在安装Excel 2002 时选择了默认方法，您可能无法在“工具”菜单中找到它。 不过，我们可以先在“工具”菜单中选择“加载项”，然后在弹出的窗口中勾选“Euro Tools”选项，“确定”后Excel 2002就会自行安装。
完成后，我们再次打开“工具”菜单，点击“欧元换算”，就会出现一个单独的窗口，专门用于欧元和欧盟成员国的货币换算。 与Excel中的其他功能窗口一样，我们可以用鼠标设置货币换算的“源区”和“目标区”，然后选择换算前后不同的货币。 显示的是“100 欧元”兑换成欧盟成员国其他货币的汇率表。 当然，为了让欧元的显示更加专业，我们也可以点击Excel工具栏上的“欧元”按钮，这样所有转换后的货币值都是欧元样式。
想了解更多高校在线教育2016年秋季招生简章、专业、学习中心等请来电：0571-88267591 15345888852 徐老师 0571-88267590 15345888857 孙老师
admin