他说每次要么搞成货币数逗号,要么就数零,没法一下子读出来个十百千万的

更令他恼火的是由于数据表列数很多,大数字非常占屏幕,放不下几列就满了(屏霸,不限于胡歌),希望能将其缩写

他的吐槽引发了表哥脑回路,冒出理解决方法,详见下文。

第一类:转化为常用的k/m/b,常见于薪水和老外

思路是:设定折算结果数字,保持在区间[0.1 ,100)内,判断依据为各数量级的指数。

不惧大年夜数字0000000屏霸EXCEL之怪状运用LOG函数游刃有余

大数字转化为k/m/b

量级过大如单元格C6(520,000)表示成”520k”,量级过小如单元格C8(52,000,000)表示成”0.052b”,将失落去了折算意义

终极折算后的样式,便是上图标绿底红字的9个单元格

以第8行为例,公式为D8=IF($C8/10^D$4>=100,\公众量级过大\"大众,IF($C8/10^D$4<0.1,\"大众量级过小\公众,ROUND($C8/10^D$4,1)&\公众k\"大众)),对应千级折算,量级过大,而E8经公式打算后符合设定区间内,显示为52m。

表哥Tips:

第2行的数量级指数,3,6,9分别对应折算的三个数量级K,M,B。

1,000=1K=10的3次方=10^3

1,000,000=1M=10的6次方=10^6

1,000,000,000=1B=10的9次方=10^9

那么,如何求出数量级指数3,6,9,连续唠

第二类:各数量级缩写并还原,常见调度严重超出单元格大小或可视范围的数据

由于数字毕竟不但是看还须要打算,以是文章里会讲到双向调度的方法。

缩写/还原大数量级数字

1.原始数据转化为数量级表示

第一步,算出数量单位的数量级指数

各数量单位对应指数

以单元格F11为例,公式为F11=INT(LOG10(C11))

表哥小注:

Log在数学中是对数,是对求幂的逆运算,如10的2次幂是100,则2是100以10为底的对数。
(此处原来还给了数学老师,是度娘召回表哥依稀影象,召回办法请自动忽略)

第二步,原始数据转化为数量级表示

大数字缩写为D列

思路是:求出原始数据以10为底的对数,取整该结果整数部分,将其作为10的幂,用原始数据去除10的幂,即得数量级表示数字

以转换第12行为例,1,314,520的转化公式为D12=C12/10^(INT(LOG10(C12)))

第三步,根据指数匹配对应数量单位

匹配E列数量级单位

蓝色两列须要常常用到,可以单独存成一张小表,这里要匹配D12对应的数量单位,用函数VLOOKUP即可,但在文章中希望多分享一些,于是特意自找麻烦,将匹配列E放在参考列左侧,这样加一些分外的处理。

E12=VLOOKUP(F12,IF({1,0},$F$2:$F$11,$E$2:$E$11),2,FALSE)

实际运用时,要尽可能减少麻烦,碰着这种情形,列序交流即可,以防万一,就用到上面公式办理。

2.还原转化数字为原始数据

由于转化数字保留原数大小,只是调度小数点,故此时还原结果会非常准确。

以G12为例,公式为G12=D1210^F12。
这样就顺理成章地还原了。

这篇文章有点点难度,如果你坚持看到这里,请想象表哥正为你竖起大拇指示赞!


希望表哥的思路能够对你起到抛砖引玉的浸染;

如果没清楚,欢迎批评示正;

如果有困难,欢迎留言给表哥,或许能帮到你哦。