本文将用到： IF(條件,滿足條件顯示的結果結果,不滿足顯示的結果) VLOOKUP(參考值,匹配區域,相對列數,精準匹配) ROUND(數值/公式,保留小數的位數) LOG10(數值) 結果為括号内數值以10為底的對數 INT(數值) 結果為整數部分

上周末收到一位朋友吐槽，特别不喜歡excel中出現大數字(帶一串00000000)

他說每次要麼搞成貨币數逗号，要麼就數零，沒法一下子讀出來個十百千萬的

更令他惱火的是由于數據表列數很多，大數字非常占屏幕，放不下幾列就滿了(屏霸，不限于胡歌)，希望能将其縮寫

他的吐槽激發了表哥腦回路，冒出了解決方法，詳見下文。

思路是：設定折算結果數字，保持在區間[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=D12*10^F12。這樣就順理成章地還原了。

這篇文章有點點難度，如果你堅持看到這裡，請想象表哥正為你豎起大拇指點贊！！！

希望表哥的思路能夠對你起到抛磚引玉的作用；

如果沒清楚，歡迎批評指正；

如果有困難，歡迎留言給表哥，或許能幫到你哦。

,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!

查看全部