分类：Excel 2016实用技巧大全

图4.217是值班表的格式，要求每人值班一周，因此每个人的值班日期是“1月1日-1月7日”以这种格式按范围来确定的。现在的问题在于这种格式的日期是否可以通过填充方式批量地生成，而不是人工逐个输入？

解题步骤

用“-”将两个日期连起来后，日期就变成了文本，文本是无法通过填充产生递增或递减的序列的，因此可以将它们拆分到两列中，变成两个日期值，对日期填充后再用公式将它们连起来即可，具体操作步骤如下。

1.在 D2 单元格输入“1-1”，在 E2 单元格输入“1-7”，然后在 F2 单元格输入公式“=TEXT（D2，＂m月d日＂）㊣＂-＂㊣TEXT（E2，＂m月d日＂）”，公式的含义是D2和E2的值都转换成“m月d日”的格式，然后将它们与“-”连起来，最终效果如图4.218所示。

2.选择D2：E2区域，然后用右键按住填充柄向下填充到第15行，松开右键后，从右键菜单中选择“序列”，弹出“序列”对话框，并在其中将步长值修改为 7，操作界面如图 4.219 所示。

3.单击“确定”按钮执行填充。

4.选择F2单元格，然后用左键双击填充柄，从而使F2的值向下填充到F15，如图4.220所示。

5.复制F2：F15区域的值，然后右键单击A2单元格，从右键菜单的粘贴选项中选择“值”，从而将F2：F15区域的值粘贴到A列对应的区域。

6.删除A：F区域的值。

知识扩展

1.工作中有很多需求都不可能一步到位，适当地变通一下，通过辅助区域或辅助软件完成也是可以接受的，领导通常只要求完成什么工作，而不会强制要求用什么方法完成。

2.F列的值是公式产生的，直接复制到A列只能得到错误值，要么将F列的公式转换成值再复制，要么使用“选择性粘贴”→“值”的方式复制到A列。

当身边没有计算器时，都用Excel作为计算工具，但是Excel规定必须以等号开头，否则表达式不会计算，这与学校里练习四则运算时的习惯是不相符的。那么是否可以不输入等号，直接输入四则运算的表达式就得到计算结果呢？

解题步骤

LOTUS 1-2-3软件早于微软的Office，它支持不以等号开头的公式，微软的Office软件是后来上市的，为了抢占市场，微软让自己的软件去兼容LOTUS 1-2-3，从而便于对方的用户转移到Office 大军中来之后仍然可以正常使用以前的文件。在“Excel 选项”对话框中，Excel 提供了兼容 LOTUS 1-2-3 软件的选项，只要开启就可以支持不以等号开头的公式运算，具体操作步骤如下。

1.按组合键＜Alt+T+O＞，弹出“Excel选项”对话框。

2.打开“高级”选项卡，然后将右方的垂直滚动条下拉到底，并选择最后两个复选框，操作界面如图4.214所示。

3.单击“确定”按钮返回工作表界面，然后在 A1 单元格中输入数学表达式“（1+5）*6”，当单击Enter键后表达式会自动转换成计算结果，但是同时在公式栏仍然可以看到表达式本身。图4.215是输入表达式时的状态，图4.216则是按下Enter键后的状态。

知识扩展

1.计算没有等号的表达式原本是LOTUS 1-2-3软件的功能，微软为了提升Excel的兼容性才添加了这两个选项，不过默认状态是关闭的，必须人工打开后才能使用。

2.本例提供的方式仅对加、减、乘、除四则运算有效，对函数运算、指数运算是不支持的。

工作中经常需要用到平方米符号㎡，每次输入这个符号都需要到图4.209所示的“符号”对话框中找，效率相当低，是否有办法直接通过键盘输入此符号呢？

解题步骤

键盘上没有㎡符号，五笔输入法也不能直接打出这个字符，好在Excel支持自动更正功能，可以借助自动更正实现键盘快速录入㎡符号，具体操作步骤如下。

1.按组合键＜Alt+T+O＞，弹出“Excel选项”对话框。

2.单击左侧的“校对”，然后单击右方的“自动更正选项”对话框，弹出“自动更正”对话框。图4.210是“自动更正选项”的位置说明。

3.将“替换”设置为“（m）”，将“为”设置为“㎡”，表示在单元格中输入“（m）”时自动替换成㎡，操作界面如图4.211所示。

4.单击“添加”按钮，以及“确定”按钮返回工作表界面。

5.在单元格中输入123（m），单击Enter键后Excel会将它自动转换成123㎡。图4.212是输入时的值，图4.213则是按下Enter键启动自动更正后的值。

知识扩展

1.微软公司在开发自动更正工具时的原意是用它来纠错，如输入those时误写为thsoe，那么Excel会自动更正为those。而在实际工作中，自动更正工具不仅用于纠错，也用它来生成一些输入有困难的字符。

在本例中，输入“㎡”比较困难，而输入“（m）”则相对容易得多，因此通过设置自动更正选项来改变输入方式可以提升输入效率。

2.本例中的“（m）”采用了半角状态的括号，因此在单元格中输入时不能使用全角的括号，否则Excel不会启用自动更正。但是Excel能够忽略大小写问题，因此输入“（M）”也可以自动更正为“m²”。

3.不宜用字母作为替换对象，如将“mmm”自动更正为“m²”，因为对字母执行自动更正仅作用于字母出现在汉字之后的情况，而工作中“m²”总是出现在数字右方。

在单元格中输入当前日期和时间，有什么快捷的方法来实现？

解题步骤

快捷产生日期和时间有两种方法：其一是通过函数产生当前时间，该时间会自动刷新，任何时候打开工作簿都能看到当前的准确日期和时间。但是当要求日期和时间不变化时就不宜使用函数了，此时应使用方法二——通过快捷键产生日期和时间。

快速输入当前日期和时间的操作步骤如下。

1.假设要在A1单元格生成自动更新的日期，那么在A1单元格中输入以下公式：

=Today()

公式的含义是生成当前日期，效果如图4.205所示。每天打开此工作簿时A1单元格的值都会更新，永远显示当前日期。

2.假设要显示自动更新的时间，那么可以在A2单元格输入以下公式：

=TEXT(NOW(),＂hh:mm:ss＂)

输入公式并按下Enter键时，A2单元格会显示按下Enter键那一瞬间的时间，而按下F9键刷新公式时则会马上更新为按F9键的时间，如图4.206所示。

有些情况下要求单元格只能显示当前日期或时间，不允许自动更新，那么应按以下方法操作。1.选择D1单元格，然后按组合键＜Ctrl+；＞生成当前日期，效果如图4.207所示。

2.如果还想在此单元格中产生当前时间，那么先按下空格，然后再按组合键＜Ctrl+Shift+；＞，此时在日期后方会追加当前的时间，效果如图4.208所示。

上述两步生成的值是永远不更新的，只是记录按下组合键的时间。如果要更新，选择单元格后再次按下两个组合键即可。

知识扩展

1.函数TODAY没有参数，它用于产生今天的日期，不带时间值。此函数在4种情况下会自动更新结果，其一是编辑单元格并按下Enter键时，第二是按F9键时，其三是保存工作簿时，其四是打开工作簿时。

NOW函数也没有参数，它用于产生当前日期和时间，同时包含日期和时间值，但是在单元格中输入公式“=NOW（）”时并不会将年、月、日和时、分、秒都显示出来，因此需要在外面套一个TEXT函数，让它强制显示当前的年、月、日和时、分、秒。

2.严格来说，公式记录的是控制面板设置的时间，假设控制面板里面的时间设置有误，那么公式生成的时间也会错误。

四维公司的员工编号规则如下：

SW代表四维，001开始，代表员工工号，最后用01代表A车间、02代表B车间，因此A车间的员工编码序号应为SW00101、SW00201、SW00301，B车间的员编码序号应为SW00102、SW00202、SW00302。

图4.201中B列和D列需要填入员工编码，如何才能快速完成呢？

解题步骤

若字符串中有多个数字却只要求部分数字在填充时产生递增，唯一的方法是将不需要递增的字符放在自定义的数字格式中，将需要产生递增的部分直接写在单元格中，具体操作步骤如下。

1.选择B2：B11区域，然后按组合键＜Ctrl+1＞，弹出“设置单元格格式”对话框。

2.选择“自定义”，然后在右方的格式代码框中输入代码“\S\W000\0\1”，效果如图4.202所示。

3.单击“确定”保存设置，同时返回工作表界面。

4.重复步骤1、2、3，对D2：D11区域设置格式代码“\S\W000\0\2”，然后单击“确定”按钮返回工作表界面。

5.在B2：B3单元格分别输入1和2，Excel会自动将它们显示为SW00101、SW00201，效果如图4.203所示。

6.选择B2：B3，然后向下填充到B11，B2：B11区域会自动产生递增的编码，效果如图4.204所示。

7.在D2：D3区域输入1和2，Excel会将它们显示为SW00102、SW00202。和步骤6一样，D2：D3同样可以向下填充产生递增的编码，编码的首尾字符不变，仅仅是中间的001、002会逐个递增。

知识扩展

1.在单元格中输入的数值可以在填充时递增或递减，但是通过自定义单元格的数字格式从而产生的字符是永远不变的。

2.按下填充柄向下填充数值时，假设数值没有产生递增。例如，A1单元格输入1，然后向下填充时A2、A3仍然是1，那么此时可以在A2单元格输入2，然后选择A1：A2区域再向下填充。其原理是通知Excel填充时的步长值，让Excel按照用户指定的差异频率去填充后面的区域。

3.格式代码“\S\W000\0\1”中间的3个0表示单元格总是显示为3位数，位数不足时用0填充，位数达到三位时则显示数值本身。其他“SW”和“01”是永远在单元格中显示出来的值，不需要用户输入，因此采用符号“\”消除这些字符的特殊含义，还原其字符属性。假设不使用“\”，Excel则会弹出出错提示对话框。

当然，“W”和“1”本身是可以不使用“\”的，因此格式代码也可以简写为“\SW000\01”。不过为了方便理解，以及照顾新手，所有前缀和后缀都加上“\”较好，对于老手而言没有坏处，对于新手而言却有助于理解，在有利无弊的情况下采用保守的措施比较妥当。

制表时经常会遇到当前需要输入的值刚好与左方单元格的值相同，或者与上方单元格的值相同，如何才能快速地输入这些值呢？

解题步骤

要输入与某个单元格相同的值，最容易想到的往往是复制、粘贴。然而复制到右方或下方时最高效的方法不是复制，而是使用快捷键填充，具体步骤如下。

1.假设要将图4.196中A2：C2区域的值复制到A3：C4区域中，即A3：A4等于A2的值、B3：B4等于B2的值、C3：C4等于C2的值，那么应该选择A2：C4，然后按组合键＜Ctrl+D＞，填充效果如图4.197所示。

2.假设要将图4.196中A2：C2区域的值复制到A3：C3区域中，即A3等于A2的值、B3等于B2的值、C3等于C2的值，那么可以先选择A3：C3，然后按组合键＜Ctrl+D＞，填充效果如图4.198所示。

也就是说，要填充一行的话可以只选择这一行，然后按＜Ctrl+D＞组合键。如果要一次填充多行，那么必须选择被复制的区域和目标区域，然后再按＜Ctrl+D＞组合键。

3.假设要将图4.199中A2：A4区域的值复制到B2：C4区域中，那么应该选择A2：C4区域，然后按组合键＜Ctrl+R＞，填充效果如图4.200所示。

向右填充也和向下填充一样，可以选择B2：B4区域后按下组合键＜Ctrl+R＞，将A2：A4的值复制过去。

知识扩展

1.＜Ctrl+D＞代表向下填充，其中字母 D 来自单词 Down，表示向下；＜Ctrl+R＞代表向右填充，字母R来自单词Right，表示向右。

2.使用快捷键填充会将格式信息一并复制过来，如果需要忽略格式，那么应采用选择性粘贴。

Excel 内置了 10 多组序列，只要单元格的值在序列范围之内，那么就可以按住填充柄向任意方向拖动从而快速产生序列中的其他值。例如，在A1单元格输入丙，将A1向下填充时可以自动产生丁、戊、己、庚等字符。

是否可以自定义序列，当在单元格中输入“小学”并向下填充时自动产生“初中”、“高中”、“大学”、“博士”、“教授”呢？或者在单元中输入“A”并向下填充时自动产生“B”、“C”、“D”……

解题步骤

Excel允许用户在“自定义序列”对话框中手工录入序列，也可以直接导入区域中的值作为序列，当保存序列后，可以在填充单元格的值时自动引用该序列。

以导入区域中的值从而生成序列为例，具体操作步骤如下。

1.在A1：A6单元格中分别输入小学、初中、高中、大学、博士、教授。

2.在B1：B26单元格中分别输入A、B、C…X、Y、Z。

3.按组合键＜Alt+D+O＞，弹出“Excel 选项”对话框，然后打开“高级”选项卡，将右方的滚动下条下拉到底，直到出现“编辑自定义列表”按钮，操作界面如图4.192所示。

4.单击“编辑自定义列表”按钮弹出“选项”对话框，单击对话框中的区域选择工具（图标为），然后选择 A1：A6 区域，最后单击“导入”按钮，将 A1：A6 区域的值导入到序列对话框中，效果如图4.193所示。

也可以手工输入A1：A6，不用鼠标选择区域。

5.将“$A$1：$A$6”修改为“B1：B26”，然后单击“导入”按钮。

6.单击两次“确定”按钮关闭对话框。

7.删除工作表中的值，然后在C2单元格中输入字母“D”，并将C2向下填充到C6，填充效果如图4.194所示。很显然，借助自定义序列输入连续字母可以大幅提升工作效率。

8.在D6单元格输入“博士”，然后将D6向上填充到D2，填充效果如图4.195所示。

知识扩展

1.Excel支持两种自定义序列的方法：其一是本文使用的导入区域法，其二是在图4.193所示的对话框中手工输入序列，然后单击“添加”按钮。

2.通过导入区域的值生成自定义序列时，区域中的任意单元格不能含有公式，否则可能导入失败。可以复制区域，然后通过选择性粘贴将区域的公式转换成值，最后再导入到序列中。

3.如果采用导入方式生成自定义序列，导入的数量不能大于255个。

4.通过填充方式输入自定义的序列时，不能像填充数值那样自定义步长值，填充自定义列时步长值总是1。

图4.188所示的工作表中包含5个部门的职员信息，现要求将它们拆分到5个工作表中去，每个工作表只存放一个部门的职员信息，而且工作表以部门名称命名，是否有办法一次性拆分完成？

解题步骤

新建5个工作表，并用部门名称命名，然后手工逐条复制数据到对应的工作表，尽管采用此方式也可以完成工作，但是效率相当低，采用数据透视表则可以几秒钟完成，具体操作步骤如下。

1.选中A1单元格，然后单击功能区的“插入”→“数据透视表”，弹出“创建数据透视表”对话框。

2.在对话框中将“新工作表”修改为“现有工作表”，同时将位置设置为“G1”，然后单击“确定”按钮，操作界面如图4.189所示。

3.在工作表界面右方的“数据透视表字段”中将“部门”字段拖到筛选器标题中，再将“姓名”、“性别”、“籍贯”和“学历”字段拖到行标题中，图4.190是操作示意图及透视结果。

4.单击功能区的“设计”→“报表布局”→“以表格形式显示”。

5.单击功能区的“设计”→“分类汇总”→“不显示分类汇总”。

6.单击功能区的“设计”→“总计”→“对行和列禁用”。

7.对数据透视表所在区域添加边框，然后单击功能区的“分析”→“选项”→“显示报表筛选项”，从而弹出“显示报表筛选页”对话框。

8.在对话框中单击“确定”按钮，Excel 会按部门名称拆分透视表的数据到“财务”、“仓库”、“后勤”和“业务”5个工作表中去，效果如图4.191所示。

知识扩展

1.借助透视表拆分工作表必须为透视表设置筛选器（在Excel 2003中称为页字段，在Excel 2010中称为报表筛选），以及将报表布局由默认的压缩格式修改为报表格式，否则姓名、性别、籍贯和学历数据会分别显示在4行中，而非4列中。

2.使用透视表拆分后生成的工作表本质上仍然是透视表，若要得到普通工作表，那么按住Shift键选择所有拆分生成的工作表，然后按组合键＜Ctrl+A＞全选所有单元格，最后通过“复制”→“选择性粘贴”→“值”，将透视表区域转换成普通单元格。

假设今天是2015年9月1日，要求对图4.178所示表格中的5个职员安排9月值日表，每人值日一天后轮到下一人，自动跳过周末。有什么办法快捷完成而非手工写入日期呢？

解题步骤

Excel的填充工具可以智能判断工作日，还可以设置递进数值，借助填充工具可以用几秒钟完成以上工作，具体操作步骤如下。

1.在B2单元格输入初始日期9-1。

2.将鼠标指针移到 B2 单元格的右下角，当产生图 4.179 所示的黑色十字光标时按下右键拖动，当拖到F5时再松开鼠标，此时会弹出图4.180所示的右键菜单，单击“序列”菜单，弹出“序列”对话框。

3.在“序列”对话框中将日期单位设置为“工作日”，步长值则采用默认值1，然后单击“确定”按钮执行填充操作。图4.181为设置填充方式的界面，而图4.182则是填充效果。

在图4.182中F2单元格的值是7日而不是5日，因为9月5日和6日属于周末，在“序列”对话框中指定的是“工作日”，那么填充时一定会忽略所有周末和法定假日。

4.将光标移到 F2 单元格的右下角，按下右键不松开，然后将填充柄向下拖动，直到第 7行时松开鼠标，最后在弹出的右键菜单中选择“序列”菜单，弹出“序列”对话框。

5.在对话框中选中单选按钮“列”，然后将日期单位设置为“工作日”，将步长值设置为5，将终止值设置为“9月30日”，表示填充时按5递增，跳过周末和假日，填充到9月30日时结束。图4.183是设置界面，而图4.184是填充结果。

知识扩展

1.工作日是指周末和法定假日以外的需要上班的日期，人工判断效率太低，Excel在填充时可以自动判断工作日，从而使输入日期的工作效率提高数倍。

2.本例中横向填充时以1为级差向上递增，因此在“序列”对话框中将步长值保持默认值1即可，纵向填充时，由于有5人值班，因此需要递增5，步长值必须修改为5。

3.终止值表示填充时的上限，如果不指定就可能产生属于10月份的日期，显然不符合需求，因此将9月最后一天作为终止值。