Excel 如何输入以0开头的数字?

在单元格中输入以0开头的数值后前置的0总会自动消失。例如,在单元格输入035后单元格只显示35,在单元格中输入00042后单元格只显示42。

如何才能输入以0开头的数字而不丢失前置的0呢?

解题步骤

让单元格显示前置的0值有两种方法,方法1的步骤如下。

1.选择A1:A10区域,然后按组合键<Ctrl+1>弹出“设置单元格格式”对话框。

2.单击“数字”选项卡中的“文本”,表示将单元格设置为文本格式,操作界面如图 3.31所示。

图3.31 设置文本格式

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

4.在A2单元格;输入数字035,在A4单元格输入0028,如图3.32所示。可以发现A1:A10区域中输入的数值可以保存前置的0,而其他区域则不可以。

图3.32 文本格式的单元格中可以保存前置的0

方法2:不需要做任何设置,只需要输入数字前先输入一个半角状态的撇号即可。例如,原本要输入“026”,改成输入“'026”即可,Excel自动将撇号隐藏起来,只在单元格中显示“026”。

知识扩展

1.本例中方法1的优点是需要大量输入前面带0的数字时效率更高,不需要多输入一个撇号。方法2的优点是不用设置单元格格式,直接输入数据就好。前者适用于大面积输入数字,后者适用于少量输入的情况。

2.要让数字在单元格中显示前置的 0,本例的两种方法都可以实现,不过不管哪种方法,都是将数值变成文本,只有文本才能显示前置的0,数值无法做到。

3.文本格式的数字不能直接参与求和、求平均等计算,需要转换成数值后再运算。因此对于不需要参与运算的、带有前置0的数字,可以直接使用文本格式,如工号、学号、订单号码等。

4.如果数字右边有文本,如汉字、标点符号或英文字母,那么前置的0可以保留下来,不需要设置单元格的格式,也不需要输入撇号。

5.以文本形式输入数字后,单元格左上角会显示一个绿色的倒三角符号,单击单元格后则再显示一个黄色的惊叹号。假设需要关闭绿色的倒三角符号,只需单击黄色的惊叹号,然后在弹出的菜单中单击“忽略错误”即可。

请在封面中缝指定的QQ群下载文件和视频教材,从而方便练习,同时验证本案例中的技巧与操作结果。

Excel 如何输入分数?

数学表达式中有下图这种形式的分数,在Excel中如果输入23/4,会自动变成4月23日,而不是自己想要的二又四分之三。要如何才能准确地输入分数呢?

解题步骤

Excel的分数包含两种:一种是可以直接参与运算的;另一种是只显示为分数形态却不能参与运算的。以输入可以参与运算的分数为例,具体操作步骤如下。

1.在A1单元格中输入数值2,然后再输入一个空格。

2.接着输入3/4。

使用以下步骤验证上述分数是否可以参与运算。

1.在A2单元格输入0.25。

2.在A3单元格输入公式“=a1+a2”,计算结果为3,这表明A1单元格的分数可以参与运算。分数与公式求和结果如图3.24所示。

图3.24 验证带空格的分数是否可以参与运算

知识扩展

1.输入公式的重点在于整数与分数之间必须有一个空格,否则可能变成文本也可能变成日期。例如,在单元格中输入12/3就会变成日期,在单元格中输入23/13就会变成文本,是日期还是文本由值的大小决定,当/左边和右边的值有一个小于13、另一个小于31时就可能变成日期,当“/”前后的值有一个超12或31时就会变成文本。

2.如果在公式中输入2 3/4这种分数,Excel会将此分数自动转换成小数。例如,在A1输入以下公式:

=MAX(10 4/5,10,5)

Excel会将它自动转换成以下形式:

=MAX(10.8,10,5)

3.如果需要输入不参与运算的分数,那么可以采用插入数学公式的方式来完成,单击功能区的“插入”→“公式”,然后在公式工具的“设计”选项卡中单击“分数”→“分数(竖式)”,操作界面如图3.25所示。

图3.25 插入公式中的竖式分数

此时在工作中已经自动插入一个空白的分数,由两个文本框和一条横线组成。选择分子部分并输入2,再选择分母部分并输入3,最后再单击分数线的左端并输入2。图3.26~图3.28展示了输入分子、分母和整数后的状态。

图3.26 输入分子

图3.27 输入分母

图3.28 输入整数

由于分数太小,选择分数后单击功能区的字号列表,将它调整为20,从而扩大分数的字号。图3.29用于展示调整字号的过程,而图3.30则是调整完字体并退出编辑状态后的效果。

加大分数的字号

图3.29 加大分数的字号

图3.30 退出编辑后的分数效果

请在封面中缝指定的QQ群下载文件和视频教材,从而方便练习,同时验证本案例中的技巧与操作结果。

Excel 是否能只允许输入2000年到2015年之间的升序日期

疑难场景

公司成立于2000年,因此制表时所用到的时间都大于等于2000年、小于等于2015年。同时,由于报表中涉及时间时一律要求升序排列,如果下方单元格的时间小于上方单元格的时间则表示输入有误。此外,禁止日期区域的两个日期之间有空白单元格,即所有日期都需要填写完整,不能跳过一个日期。应该如何设置才能让单元格符合以上3个条件呢?

解题步骤

设置数据验证可以让指定区域只能输入2010年到2015年的升序日期,具体步骤如下。

1.选择需要进行限制的区域A2:A20。

2.单击功能区的“数据”→“数据验证”,弹出“数据验证”对话框。

3.将“允许”下拉列表中的“任何值”修改为“自定义”,然后在“公式”文本框中输入以下公式:

公式表示A2的值必须大于等于2010年1月1日、小于2015年12月31日、大于等于A1单元格的数值(如果A1是文本则当作0计算)、A1单元格不能等于空值。

公式中的A2和A1采用的是相对引用,因此公式在不同单元格中时,公式中的A2和A1将代表不同的单元格。对于A2单元格的数据验证,采用的是公式

而A4单元格会将A2变成A4、将A1变成A3,其数据验证公式为

简言之,公式中的A2和A1并非代表A2单元格和A1单元格,而是当前单元格与上一个单元格,公式在不同单元格中A2和A1将代表不同的对象。

4.打开“数据验证”对话框的“出错警告”选项卡,“样式”保持“停止”不变,将“标题”设置为“友情提示”,将“错误信息”设置为“1.只能是2010年到2015年之间的日期2.必须是升序的日期3.上方不能有空白单元格”。图3.18和图3.19分别是设置公式与设置出错警告信息。

由于公式较长,在图 3.18 中未完整显示公式,请读者直接使用案例文件,在文件中有完整的公式。

使用公式限制单元格

图3.18 使用公式限制单元格

图3.19 指定警告信息

5.在“数据验证”对话框中单击“确定”按钮返回工作表界面。

6.在A2单元格输入日期2008-9-1,单击Enter键后Excel将弹出如图3.20所示的警告信息,同时禁止用户输入。

7.单击“重试”按钮,重新输入日期“2010-10-5”,由于此日期符合所有要求,因此Excel允许正常输入。

8.在A3单元格录入日期“2010-2-8”,由于当前日期不符合“大于等于上方单元格的日期”这个条件,因此Excel会弹出如图3.21所示的提示信息,同时禁止用户输入。

图3.20 日期小于2010年

图3.21 非升序日期

9.单击“重试”按钮,然后重新输入日期“2012-12-8”,此日期可以正常输入。

10.选择 A5 单元格,然后输入日期“2015-8-9”,尽管此日期在指定的范围之内,且大于前面的所有日期,但是不符合“上方单元格不能空白”的条件,因此Excel会禁止输入,同时弹出如图3.22所示的警告信息对话框。

图3.22 上方单元格空白

通过测试可以确定本例的数据验证设置满足所有需求。

知识扩展

1.DATE(2010,1,1)代表2010年1月1日,不能使用“"2010-1-1"”或“"2010年1月1日"”来代表2010年1月1日,因为它们都是文本,而DATE(2010,1,1)才是真正的日期值。

2.DATE(2015,12,31)代表2015年的最后一天,同理不能使用“"2015-12-31"”或“"2015年12月31日"”。

3.N函数可以将文本转换成0,数值保持不变,例如:

=N("Excel")——运算结果为0

=N(2015)——运算结果为2015

在本例中,由于上方的一个单元格有可能是日期值也有可能是文本,使用N函数将A1转换成数值后再与A2进行比较,否则A2单元格不管输入什么日期都会禁止输入,因为任意日期值都小于文本值。

4.Excel对数据的大小排序是:

逻辑值>文本>字母>数值

其中逻辑值包含 True 和 False,文本包含汉字、标点符号、片假名等,字母即 A~Z 和 a~z各26个英文字母。数值包含正数、负数、0,日期也是数值,仅仅显示形态不同于数值,但其本质仍是数值。

根据以上排序可以得知,任意文本都是大于数值和日期的,因此当 A1 是文本、A2 是日期时,A1总是大于A2。

5.And函数用于校验多个条件是否全部成立,本例中使用了3个条件,即And函数的3个参数。And函数支持1~255个参数。

6.设置数据验证时,不管当前选中了多大的区域,公式中只写左上角一个单元格,Excel会自动将这个公式应用到所有单元格中,而且公式会随单元格地址变化而变化,自动适应,不过前提是公式中的单元格采用相对引用。

7.通过数据验证对单元格的值进行限制时,如果是简单的限制,使用“允许”下拉列表的各种现成设置足以应付,像本例这种复杂的多条件限制则必须通过公式来限制。

8.多条件限制时只能通过公式一次性指定多个条件,不能分多次设置数据验证,像条件格式那样多个条件并存,因此本例中不同的错误类型只能共用一条警告信息。

请在封面中缝指定的QQ群下载文件和视频教材,从而方便练习,同时验证本案例中的技巧与操作结果。

Excel 如何禁止输入重复值

如下图所示的工作表中A列用于存放职工工号。由于姓名允许重复,但工号不允许重复,因此要求在A输入数据时,Excel自动检查是否重复,如果重复则弹出提示信息,要求用户重新输入。

职工信息表

解题步骤

限制某区域不能输入重复值宜用数据验证,具体步骤如下。

1.选择A2:A10区域。

2.单击功能区的“数据”→“数据验证”,弹出“数据验证”对话框。

3.将对话框中“允许”下拉列表中的“任何值”修改为“自定义”。

4.在公式框中输入以下公式,操作界面如图3.14所示。

5.打开“数据验证”对话框的“出错警告”选项卡,“样式”保持“停止”不变,将“标题”设置为“友情提示”,将“错误信息”设置为“当前值在A2:A10区域已经存在,请重新录入”,设置界面如图3.15所示。

图3.14 使用公式限制A2∶A10只能输入唯一值

图3.15 设置出错警告

6.单击“数据验证”对话框中的“确定”按钮返回工作表界面,在A2:A3单元格输入工号34和35,然后在A4单元格再次输入工号34,当单击Enter键后,Excel会弹出图3.16所示的提示框,必须单击“重试”按钮,然后输入其他不重复的值,否则无法输入成功。

输入重复值时的错误提示

图3.16 输入重复值时的错误提示

知识扩展

1.COUNTIF函数属于Excel的统计函数,用于计算区域中符合某个条件的数据个数。例如,等于100的单元格数量、大于999的单元格数量、包含“合格”二字的单元格数量,或者计算不等于某个单元格的单元格数量。本例采用COUNTIF函数计算A2:A10区域中值等于当前单元格的值的单元格数量。

2.COUNTIF函数的语法如下:

COUNTIF(range,criteria)

它包含两个必选参数,第一参数代表要对其进行计算的区域,第二参数代表要计算的条件。假设要计算A1:A10区域中大于500的单元格数量,那么宜采用以下公式:

=COUNTIF(A1:A10,">500")

假设要计算A1:A10区域中等于500的单元格数量,那么宜采用以下公式:

=COUNTIF(A1:A10,500)

当第二参数是数字时可以不用等号也不加双引号。

假设要计算A1:A10区域中包含“螺丝”的单元格数量,那么宜采用以下公式:

=COUNTIF(A1:A10,"*螺丝*")

第二参数“*螺丝*”表示包含“螺丝”,前后的“*”代表任意长度的任意字符,即以任意字符开始、任意字符结尾、中间包含“螺丝”二字的字符串。公式的运算效果如图3.17所示。

图3.17 计算A1∶A10区域中包含“螺丝”的单元格数量

3.本例中公式“=COUNTIF($A$2:$A$10,A2)=1”代表A2:A10区域中任意单元格的值只允许出现一次,如果重复则会弹出提示对话框警告用户。

4.数据验证仅对设置后手工输入数据有效,如果先输入字符后设置数据验证,那么 Excel不会提示不符合规则的数据。

5.数据验证对公式也没有限制,限制A1单元格只能输入1~10之间的数值,当在A1单元格手工输入11时会禁止输入,但是A1单元格通过公式引用A2单元格的值,在A2单元格中输入11时,A1单元格也会产生11,而且不产生任何提示。

请在封面中缝指定的QQ群下载文件和视频教材,从而方便练习,同时验证本案例中的技巧与操作结果。

Excel 如何禁止输入不规范的日期

工作中会经常收到同事发来的包含不规范日期值的报表,从而导致后期运算出错。例如“2015.7.15”、“2015年7月15号”、“07.15.2015”、“20150715”等格式都属于不规范的日期值,无法使用日期函数对它们执行运算。是否可以在前期设计表格模板时禁止用户输入不规范的日期?

解题步骤

假设有图3.6所示的销量报表模板,要求对A2:A9区域加以限制,从而提升日期值的准确性,操作步骤如下。

图3.6 销量报表模板

1.选择A2:A9区域。

2.单击功能区的“数据”→“数据验证”,弹出“数据验证”对话框。

3.将对话框中“允许”下拉列表中的“任何值”修改为“日期”,表示只允许在 A2:A9 区域输入日期。

4.根据实际工作需求对“开始日期”和“结束日期”指定范围。例如,当前工作表只允许输入2015年和2016年的日期,那么就分别输入“2015-1-1”和“2016-12-31”。设置结果如图3.7所示。

5.打开“数据验证”对话框的“出错警告”选项卡,“样式”保持“停止”不变,将“标题”设置为“友情提示”,将“错误信息”设置为“请按标准的日期格式输入,有效范围在2015-1-1到2016-12-31之间”,设置结果如图3.8所示。

指定日期范围

图3.7 指定日期范围

设置出错警告

图3.8 设置出错警告

6.单击“数据验证”对话框中的“确定”按钮返回工作表界面,在 A2 单元格输入不规范的日期“2015.2.28”,当单击Enter键后将弹出图3.9所示的错误提示信息。

图3.9 输入格式不规范的日期时产生错误提示

7.单击“重试”按钮,继续输入日期“2014-5-7”,Excel会产生相同的提示信息,表示格式有误、范围有误时都禁止操作。

知识扩展

1.“2015.7.15”、“2015年7月15号”、“07.15.2015”、“20150715”等都不是规范的日期,所有日期函数都无法对它们执行日期运算。

例如,Month函数可以计算一个日期属于几月份,图3.10和图3.11两个图片分别展示了错误格式的日期和正确格式的日期的运算结果。

图3.10 错误的日期执行日期运算时出错

图3.11 正常计算出月份

2.正确的长日期有三种格式(排除英文格式),包含“2015-7-15”、“2015/7/15”和“2015年7月15日”。当在单元格中输入最后一种格式的日期时,Excel会自动将它转换成“2015/7/15”格式的日期。

3.日期其实就是数值,只是它呈现出的形态与数值不同而已,本质上是一样的。例如2015年8月8日等同于数值42224,只是显示为日期样式而已。因此日期必须是数值,不管其外观如何变化。假设按文本形式输入日期则无法参与数值运算,对工作带来负面影响。“2015.8.8”就是文本形式,所有日期函数都无法对该值执行日期运算。

4.为什么42224代表2015年8月8日呢?Excel以1900年1月1日作为初始日期,日期1900年1月1日被当作第一天,那么相对于1900年1月0日之后的第42224天则刚好是2015年8月8日。可以如此验证:在A1单元格录入数值42224,然后依次单击功能区的“开始”→“数字格式”→“长日期”,单元格中的42224马上会显示为2015年8月8日,效果如图3.12所示。

通过设置格式改变数值的显示形态

图3.12 通过设置格式改变数值的显示形态

Excel 如何禁止在某区域输入文本

在图3.1中,C2:D11区域需要录入数值(E2:E11、C12:E12是公式计算出来的,不需要输入值),由于担心该区域错误地输入了文本而导致后续运算出错,因此要求限制该区域不能输入文本。如何操作才能仅允许C2:D11区域输入数值呢?

图3.1 产量表

解题步骤

对区域进行数据类型限制一般通过设置数据验证来实现,具体步骤如下。

1.选择C2:D11区域。

2.单击功能区的“数据”→“数据验证”,弹出如图3.2所示的“数据验证”对话框。

3.在“允许”下拉列表框中将“任何值”改为“整数”,表示此区域禁止输入整数以外的值。

4.由于产量不能是负数,同时不会大于2000,因此将“最小值”设置为0,最大值设置为2000。设置界面如图3.3所示。

默认状态的“数据验证”对话框

图3.2 默认状态的“数据验证”对话框

图3.3 为C2∶D11区域指定验证条件

5.打开“数据验证”对话框的“出错警告”选项卡,“样式”保持“停止”不变,将“标题”设置为“友情提示”,将“错误信息”设置为“不能录入文本,只能录入0到2000之间的数值”,设置结果如图3.4所示。

6.在“数据验证”对话框中单击“确定”按钮返回工作表界面,然后在C11单元格输入字符9oo(后面两位不是数值0而是字母o),当按下Enter键后将弹出图3.5所示的提示框。

图3.4 设置出错警告

输入文本时自动产生警告

图3.5 输入文本时自动产生警告

7单击“重试”按钮,然后重新输入数值900,当按下Enter键后Excel不再弹出任何提示,表示输入的数据符合要求。

知识扩展

1.数据验证在Excel 2010及更早的版本中称为数据有效性,它用于控制区域中允许录入的字符类型,可以限制区域中只能输入某个范围的整数、小数、日期值、时间值,也可以限制区域中只能输入某些具体的单词,其中最强大、灵活的是“自定义”,通过公式可以限制任何类型。

2.数据验证可以理解为单元格的一种属性、一种格式,它可以很轻易地被清除。例如,将一个未设置数据验证的单元格复制到已经设置了数据验证的单元格时,由于复制时默认包含单元格的一切格式信息,因此目标单元格的格式会被覆盖掉,导致单元格的数据验证设置丢失,从而不会报警。换言之,数据验证仅对手工输入数据有效,对复制、粘贴操作无效。

3.对于错误输入时产生提示,Excel提供了两套安全机制:其一是数据验证——位于“数据”选项卡;其二是使用条件格式,其菜单位于“开始”选项卡。两者的区别是数据验证有强制性,数据错误时会弹出提示对话框从而中断操作,而条件格式仅通过单元格颜色、边框或字体颜色来提醒用户,用户可以完全忽略这些提示而继续操作。因此通俗地讲,如果只想获得通知信息则采用条件格式;如果需要在接收到提示信息后必须做出响应才能消除该信息,则宜采用数据验证。

请在封面中缝指定的QQ群下载文件和视频教材,从而方便练习,同时验证本案例中的技巧与操作结果。

Excel 2016是否可以禁止输入网址或邮箱地址时产生超链接?

在单元格中输入网址或邮箱地址后,Excel会自动在该单元格产生对应的链接,即单击网址后可打开对应的网站,单击邮箱地址后则打开发送邮件的对话框,双击单元格同样会打开对应的链接。图1.45是输入邮箱地址后产生的超链接展示。

图1.45 输入邮箱地址后自动产生超链接

是否可以关闭此功能,从而在双击单元格时不打开链接而是直接进入编辑状态呢?

解题步骤

通过设置Excel选项可以关闭自动转换超链接的功能,具体步骤如下。

1.单击菜单“文件”→“选项”,弹出“Excel选项”对话框。

2.打开“校对”选项卡,然后再单击右方的“自正更正选项”按钮,操作界面如图1.46所示。

图1.46 单击“自动更正选项”按钮

3.打开“自动更正”对话框中的“键入时自动套用格式”选项卡,然后取消选择“Internet及网络路径替换为超链接”复选框,操作界面如图1.47所示。

图1.47 关闭自动转换成超链接的功能

4.单击“确定”按钮保存设置,同时返回工作表界面。

5.在单元格中输入网址或邮箱地址,Excel不再自动为它们添加超链接。

知识扩展

1.Excel自动对网址或邮箱地址添加超链接的本意是方便工作,然而当用户只需要网址和邮箱地址本身时,它会影响工作。读者可以根据自己的实际需求决定是否关闭此功能。

2.如果只打算取消某一个单元格的网址链接,而不是关闭 Excel 的自动添加链接的功能,可以右击单元格,然后从右键菜单中选择“取消超链接”选项即可。

请在封面中缝指定的QQ群下载文件和视频教材,从而方便练习,同时验证本案例中的技巧与操作结果。

Excel处理时间数据:如何输入正确的时间

如果要详细解说应该如何处理时间数据,反而会阻碍大家的理解……真要详细地讲,那么这样的解说将会变成读起来都会觉得很厌烦的长篇大论。所以,在这里我只给大家介绍解决对策。

首先,我们来了解一下 TIME 函数。它是处理时间数据的函数,能够指定时、分、秒。比如要制作“9:30:00”这样的时间数据,我们可以输入下面的公式:

=TIME(9,30,0)

反过来,单元格 A1中含有时间数据(如“9:00”)时,想要从此单元格中分析出小时、分、秒的数值的话,就要用到 HOUR 函数、MINUTE 函数和 SECOND 函数。分别可通过以下公式导出相应的数值。

  • =HOUR(A1) : 导出单元格 A1中时间数据的小时数
  • =MINUTE(A1) : 导出单元格 A1中时间数据的分钟数
  • =SECOND(A1) : 导出单元格 A1中时间数据的秒数

在处理任何时间数据时都可以用下面的函数公式,这样能够导出绝对没有误差的时间数据(假定单元格 A1中含有时间数据)。

=HOUR(A1)*60+MINUTE(A1)

这样一来,如果单元格 A1中是“8:25”则会自动返回“505”这个数值。这个数字表示的是从“上午0:00”到“上午8:25”经过的分钟数,正好是505分钟。像这样,将时间数据转换为不含小数点的整数,就能在计算时避免出现误差。

以上一个出现误差的案例,可通过以下方式解决。

出现误差的时候,中途增加处理步骤

出现误差的时候,中途增加处理步骤

在 E 列与 F 列中,输入前文中提到的相应函数,将开始时间与结束时间转换为分别距离上午0:00的分钟数。

将 E 列到 G 列的单元格的格式改为“数值”。将这些转换后的数值相减,就会得到 G 列上的经过的分钟数,由于结果是不含小数点的整数,也不会产生误差。在单元格 G4中输入的是这两项经过的分钟数是否为相同值的判定逻辑式(=G3=G2)。结果为 TRUE,就是说判定为经过的时间相同。