Ragic 博客
企业电子化的专家 Ragic 教你如何利用各种软件、
云服务让公司快速升级!
加入 Ragic 企业电子化的行列!
云工作术
各类应用演示
案例故事
逃离恶梦
关于 Ragic
Facebook Twitter YouTube
云数据库
博客
关于Ragic
云工作术
各类应用演示
案例故事
逃离恶梦
关于 Ragic

数字怎么又跑掉了?Excel/Google 电子表格三大常见“格式”问题、解法

作者:Jaykee Chou

在我们的日常生活中,常遇到以一长串数字成的内容,像是产品序号、商品代码,又或者是电话号码。然而,在将这些内容输入至 Excel 和 Google 电子表格时,你可能会发现有些内容没办法如你所愿被正确地显示出来,例如将一串较长的数字序号放进 Excel 会变得好像乱数:

使用 Google 电子表格时虽然不会出现乱数,但有时你可能也会发现刚贴上的编号开头的 0 不见了:

这是怎么回事?怎样才能调回来?类似这样的问题,其实通常并不是数据坏掉,只是系统猜错了你需要的“单元格格式”,让你的数字呈现不符预期(甚至有点惊吓)。只要你对 Excel 和 Google 电子表格里的“格式”更有概念,下次遇到这类问题时,就更能知道解决方向。

因此,接下来我们会先跟你快速解说几个基础的“单元格格式”运作原理,让你掌握基本逻辑,然后实际演示如何透过调整单元格格式,解决 3 个最常见的“数字乱跑”困扰:长串数字变乱码开头 0 消失输入分数(如 1/3)被系统辨认成日期。(若有需要,可直接点超链接或目录切换查看对应范例)

你应该先认识的“单元格格式”

要了解“数字乱掉”问题,首先我们要知道,在使用 Excel 和 Google 电子表格时,每一格数据,都会有一个对应的“单元格格式”,这些格式会影响系统怎么识别输入的数据,以及要以什么样的方式呈现。

以上面这张图来说,虽然两者纪录的值都是 1,但左方 Excel 单元格 A1 显示的 “1.00” 就是指定为“数值”格式;右方 Google 电子表格单元格 A1 显示的 “100.00%” 就是指定为“百分比”格式。

了解“默认格式”

在你没有特别设置时,不管是手 key 或贴上数据,Excel 和 Google 电子表格都会自动套用系统默认为你选择的格式。以 Excel 来说,不做任何设置时,系统默认给我们的格式叫做“通用格式”;以 Google 电子表格来说,默认给我们的格式则叫做“自动格式”。

Excel“通用格式”显示数据的基本逻辑是“输入什么就显示什么”,因此多数时候你的数字并不会有任何变动。但有以下例外:

输入的数据 通用格式显示的样子 背后的处理逻辑
0922333444 922333444 系统认为输入的值是可计算的数字,可删除开头无意义的 0
1234567890123
(超过 12 位的数值)
1.23E+11
(以科学记号显示)
需要精简过长信息
1/3
(有 "/" 符号的数值)
1月3日
(以日期格式显示)
系统认为输入的是日期

Google 电子表格“自动格式”是类似的逻辑,只是它“自作主张”的幅度小一些,不会将较长的数值或包含“/”的数值转换形式。但是,在 Google 电子表格输入开头有 0 的数值时,0 也会被省略。

这样的处理逻辑有时能让你很方便(例如 - 输入 1/3 确实是在记录日期、也希望显示成 “1 月 3 日”),但有时正好很不适合(例如 - 输入 1/3 是要表示“三分之一”),不适合时,就容易出现文章开头说的“数字乱跑”困扰。

理解原因后,只要另外找到真正适合我们情境的单元格格式来做设置,就可以避免相关问题。

重要概念:发现问题“事后调整格式”没用

不过,这里也要先提另一个需要注意的概念:如果你输入、贴上数据后,发现数字怪怪的,判断可能是格式问题,此时“直接手动改动格式”往往没用。

这是因为单元格格式有时不只影响保存值的外观,特定情况下它还可能直接调整实际上保存的值,所以如果在“输入数值”后才“更改格式”,很多时候已经来不及了;最保险的方法就是先“更改格式”,再“输入数值”,特定情况下先设置好单元格的格式后,再将值放入单元格。

至于哪些情况应该调整成什么格式呢?以下我们就直接从个别范例来一一做解说!

案例 1:输入长数字后,跑出难懂乱数(如“1.23E+11”)?

在 Excel 里,在贴上一长串的数字之后,你可能会发现格内的值竟然变成像是“1.23E+11”这样看起来非常可怕的乱数:

发生这件事情的原因,其实是因为系统将这串数字辨认为了数值,所以在显示较大的值时会选择使用科学记号来精简地记录这个值。

也就是说,要避免这样的情况,只要将指定单元格的格式设为“字”(而非套用默认的通用格式),就能够避免值被以科学记号的方式显示了。

在 Excel 里,选择要更改设置的单元格范围后,从原本显示“通用格式”的位置展开下拉菜单就能够找到“字”的选项:

经过这样的设置之后,重新将原本的数据贴到工作表,就可以看到工作表内保存、显示的数据都是完整的序号了:

如果你使用的是 Windows 版(版本为 Build 16808.10000 以上)或 Mac 版(版本为 Build 23091003 以上)的 Excel 365,可以参照这则说明,从“文件 → 选项 → 数据”中找到“自动数据转换”,撤销勾选“保留长数目的前 15 个数字,并以科学标号法表示”。

进阶解法:希望数值能够完整呈现,同时能够进行公式计算?

将单元格格式改为“字”虽然能够确保输入的所有内容都被完整记录、显示,不会因为数字过长而被精简为科学记号,但字格式本身并不适合公式运算,在一部份的情况下(例如公式字段曾参照过字格式,导致字段本身也自动被转为字格式时),会无法触发公式计算,或是在计算时生成不可预期的错误:

假如你只是希望长数字能够不被简化、改变呈现方式,但同样保留数值的计算弹性,可以将单元格格式改为“数值”后,再对小数码数进行增减(默认的数值格式会固定显示到小数点后两位)。

你可以从格式的下拉菜单中找到“数值”格式选项:

在将格式更改为数值后,单元格内的数据会被加上小数点后两位,尽管小数点后两位没有数字也会显示 “.00”:

这个时候,你可以藉由点击下图的“减少小数码数”单击钮,调整需要显示的小数码数:

最后再输入公式检查是否能够正确计算出结果:

案例 2:编号开头的 0 不见了

另一个常见的情境,是在将各种编号数据贴上至 Excel 或 Google 电子表格后,你会发现有些值最前方的 0 神秘地消失了:

导致 0 消失的原因其实也跟默认格式辨认内容的方式有关。因为输入的值全部都是数字的关系,因此 Excel 和 Google 电子表格的默认格式都会直接将这些值识别为数值。又因为在数学上,开头的 0 多半是无意义的,这些值开头的 0 才会直接被省去。

也就是说,这个问题的解决逻辑其实与前一点类似,只要将单元格转为字格式纪录,再重新将数据录入,系统就能够保留你输入的所有值,正确保存编号开头的 0 了。如果你是使用 Excel 的话,可以点击这里查看 Excel 的设置操作步骤。

在 Google 电子表格内,你可以点击 “123” 的图符,展开格式菜单,并点选纯字以转换格式:

再将原先的数据贴上,就能发现所有的数据都被完整纪录并显示了:

扩展情境:打开其他地方汇出的 csv 时,数字首位的 0 不见了吗?

我们前面提到的所有情境都是基于你自己操作 Excel 与 Google 电子表格时,手动输入或贴上数据时会发生的状况。

除了手动输入以外,另一种可能性是在尝试打开外部系统汇出的 csv 时,数字首位的 0 因为数值格式的规则而消失:

其实这是因为 csv 本身并没有包含任何格式定义,导致 Excel 使用“通用格式”来识别所有的单元格,所以我们需要让 Excel 能够知道我们目前汇入的单元格是属于“字”。

为此,你可以参阅这篇说明,使用 Excel 的功能“汇入字符串向导”以在汇入数据前预先指定每一栏的格式。

如果你使用的是 Windows 版(版本为 Build 16808.10000 以上)或 Mac 版(版本为 Build 23091003 以上)的 Excel 365,可以参照这则说明,从“文件 → 选项 → 数据”中找到“自动数据转换”,撤销勾选“移除前置 '0' 字符并转换成一个号码”。

案例 3:输入数字却变成日期?

如果你打算透过 Excel 来记录食谱,也许会常常需要使用“1/3 茶匙”、“1/4 杯水”这样的描述方式。但要是直接将 1/3、1/4 这样的数字输入到 Excel,通用格式就会自动将这些数据变成日期:

数据变成日期最大的困扰是,Excel 并不只是换个方式显示原先的内容,而是会直接将输入的内容转化为该日期的值,例如输入的 1/3 变成 2023/01/03 以后,再将单元格改为通用或数值格式,会发现单元格的值变成 “44929” 这个与原本的 “1/3” 完全无关的值:

因此,要输入分数的话也必须先确保单元格的格式设置正确,除了能够将单元格改为“字”确保值不会被更改之外,你也可以将格式改为“分数”以利于计算:

分数格式在进行计算之后,会以适当的方式分隔整数部分与分数部分,对于常常需要计算多份用量的用户来说更加方便、易懂。

由于 Google 电子表格不支持分数格式,只能透过将单元格格式改为字以保留分数的显示方式,但无法进行公式运算。因此,我们并不建议使用 Google 电子表格保存分数。

进阶解法:如何救回已经被改成日期的值?

如果你手上有着分数已经被更改为日期的数据,希望至少能够让这些数据以分数的方式呈现,你可以先更改日期的显示格式,再汇出为 csv 文件,最后重新汇入并指定为“字”格式的方式来更改保存值,如果需要获取源的分数值以方便计算,也可以再利用字符串公式达成。

首先,你需要在格式设置中找到“其他数字格式”,再依序找到“日期”中的“3/14”类型:

接下来,再将文件保存为“CSV UTF-8(逗号分隔)”:

最后,你可以再利用汇入字符串向导,将刚刚保存的 csv 檔汇入,并在最后一个步骤时指定应该保存分数的字段为“字”:

点击完成后,Excel 就会以“字”格式保存这些分数了:

透过这个方法还原的分数因为实际上是“字”,没有办法直接进行公式运算,如果有计算需求的话,可以另外在其他单元格使用 LEFTRIGHT 公式分别获取分数的分子与分母后,将两者相除以得到该分数的值(只能以小数点呈现,但因为是数值字段,所以可以进行运算)。

使用 Ragic,格式不再乱跑

有别于 Excel 和 Google 电子表格的默认格式,会发生因为输入的数据不同而发生变化的状况,Ragic 的每一个字段都是在设计表单时就决定好字段种类(并同步设置字段格式)的,因此在保存设计之后,不会基于实际使用时登录的数据,使字段格式发生变化。

如果没有特别设置的话,Ragic 会预先套用“自由输入”这个种类,自由输入的操作逻辑就像 Excel 和 Google 电子表格的“字”格式,不管输入什么都会完整保存,因此尽管没有指定字段种类,也不用担心输入的数据被更改、调不回来:

每个字段种类都有各自的特性与适合的数据内容,假如在设计表单时就预先指定这个字段为“编号/号码”,未来这个字段就会完整保存输入的所有数字,不会因为数据太长而被精简化,或是省略首位的 0:

例如 EAN-13 这个全球通用、随国别不同,而可能有些编号以 0 作为开头的商品编号格式。透过使用我们的编号/号码字段加上我们预先设计好的 EAN-13 格式,你就能够在直接输入数字的情况下得到包含首位 0、也正确以标准格式编排的 EAN-13 代码了:

又或者是我们开头提到的电话号码,在 Ragic 也有专属的字段种类与格式,让你运用适合的选项来登记不同地区的格式记录号码,以适当的分隔方式提升数据的易读性。另外,Ragic 也设计了直接点击电话格式内的号码以拨出的功能:

标签: Excel

归类: 云工作术, 表格技巧

博客背后使用 Ragic! : 最强大的 No Code 企业电子化工具
把数据放在Excel上不只是拖累团队的行政效率,他也很容易出错并且无法进行任何内控。
当您的团队成长时,使用Excel管理数据就会越来越痛苦。
创建你们的第一个云数据库!

马上登记
免费试用 Ragic!

用 Google 帐号登记

立即科技 Ragic, Inc.
02-7728-8692
台北市中正区南昌路二段81号9楼