他说每次要么搞成货币数逗号,要么就数零,没法一下子读出来个十百千万的
更令他恼火的是由于数据表列数很多,大数字非常占屏幕,放不下几列就满了(屏霸,不限于胡歌),希望能将其缩写
他的吐槽引发了表哥脑回路,冒出理解决方法,详见下文。
第一类:转化为常用的k/m/b,常见于薪水和老外思路是:设定折算结果数字,保持在区间[0.1 ,100)内,判断依据为各数量级的指数。
大数字转化为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。这样就顺理成章地还原了。
这篇文章有点点难度,如果你坚持看到这里,请想象表哥正为你竖起大拇指示赞!!
!
希望表哥的思路能够对你起到抛砖引玉的浸染;
如果没清楚,欢迎批评示正;
如果有困难,欢迎留言给表哥,或许能帮到你哦。