Excel“时间点”与“时间点”数据合并,为啥总是出错呢?
今天遇到这样的一个Excel实际办公中的例子:
A2和B2单元格分别为两个时间点数据,现在我们想要将两个时间点数据合并显示,并且中间用“-”(小横杠)来进行连接。如果在数据量非常大的情况下,我们该如何批量实现这种数据的合成操作呢?
有的小伙伴说了,我们直接用最经典的合并符&连接不就可以了,但是当我们在C2单元格输入了公式后:
=A2&"-"&B2
我们发现最后形成的是一个错误的结果。
如果数据只有几行的情况下,在我们的时间并不紧张的情况下,我们完全可以靠手动输入的方法进行录入合并后的数据。如下图所示。
但是如果数据量成百上千行,依旧采取人工手动输入方式的话,工作效率我们可想而知。
我们直接在C2单元格时输入公式:
=TEXT(A2,"h:mm")&"-"&TEXT(B2,"h:mm")
用Text函数将两个时间点分别转换为对应标准格式的文本后再用合并符&相连接即可。
TEXT函数:
将数值转换为按指定数字格式表示的文本。
语法:
TEXT(value,format_text)
Value 为数值、计算结果为数字值的公式,或对包含数字值的单元格的引用。
Format_text 为"单元格格式"对话框中"数字"选项卡上"分类"框中的文本形式的数字格式。
文本连接,可以用“&”来实现,还可以用TEXTJOIN这个函数来实现。
在C2单元格直接输入函数:
=TEXTJOIN("-",,TEXT(A2,"h:mm"),TEXT(B2,"h:mm"))
TEXTJOIN 使用分隔符连接列表或文本字符串
TEXT 格式化文本,将数值转换成时间格式文本
TEXT(A2,"H:mm") 将A2单元格数值,转换成时间格式文本,H 代表小时,mm代表分钟,记得一定要输入双引号包含,不然会报错。
TEXTJOIN 函数将多个区域和/或字符串的文本组合起来,并包括你在要组合的各文本值之间指定的分隔符。如果分隔符是空的文本字符串,则此函数将有效连接这些区域。
语法:
TEXTJOIN(分隔符, ignore_empty, text1, [text2], …)
例如:
=TEXTJOIN (" ",TRUE, "The", "sun", "will", "come", "up", "tomorrow"。) ") 将返回明天将有日出。
备注:
如果生成的字符串超出单元格限制 (32767 个字符) ,则 TEXTJOIN 返回#VALUE!错误。
直接在C2单元格输入公式:
=CONCATENATE(TEXT(A2,"h:mm"),"-",TEXT(B2,"h:mm"))
即可实现两个时间的连接:C2单元格的结果是 8:15-9:32。
现在解释一下这个函数的原理:
CONCATENATE()是外围函数,将内部的参数连接起来,不过内部需要是文本格式。
TEXT(A2,"h:mm") 是将A2单元格设置为文本格式。
=CONCATENATE(TEXT(A2,"h:mm"),"-",TEXT(B2,"h:mm"))
意思是将“A2”与“B2”两个单元格的日期格式更改为文本格式,然后将“A2”与“-”与“B2”三个参数连接起来放置在C2。
得到想要的结果。
使用 CONCATENATE(其中一个文本函数)将两个或多个文本字符串联接为一个字符串。
重要:
在 Excel 2016、Excel Mobile 和 Excel 网页版 中,此函数已替换为 CONCAT 函数。尽管 CONCATENATE 函数仍可向后兼容,但应考虑从现在开始使用 CONCAT。这是因为 CONCATENATE 可能不再适用将来的 Excel 版本。
语法:
CONCATENATE(text1, [text2], ...)
例如:
=CONCATENATE("Stream population for ", A2, " ", A3, " is ", A4, "/mile")
=CONCATENATE(B2, " ", C2)
参数名称: