Excel將數值分別以個十百千萬十萬…代入【詳細公式說明】

數值分別以個十百千萬…代入

以下金額,如何分別以右方的個、十、百、千、萬、十萬、百萬、千萬、億、十億代入?

Step1 插入函數

於「B2」儲存格插入函數-「MID」函數。
傳回從文字串中的某個起始位置到指定長度之間的字元。

Step2 函數引數

Text

為要擷取字元的字串或儲存格。
選取「A2」儲存格。

Start_num

為要擷取第幾個字元(從左邊數)。
輸入「1」。

Num_chars

要傳回的字元數。
輸入「1」。

確定後則為「5631475932」從左邊數第「1」個字元,並取得「1」個字元數,即為「5」。

十億:「=MID(A2,1,1)」
億:「=MID(A2,2,1)」
千萬:「=MID(A2,3,1)」
百萬:「=MID(A2,4,1)」

以此類推…,但如果一個一個這樣輸入的話,不如直接手動直接打上數字,所以我們要改使用「COLUMN」函數來取代。

Step3 結合COLUMN函數

COLUMN」函數,即傳回儲存格位置的「」號。
A2」儲存格即為第「1」欄 (直向)。

要向右與向下拖曳,需要用「金錢符號$」鎖定儲存格。
Text引數:需固定「金額」欄,但不需固定列(要向下拖曳),即為「$A2」。
COLUMN:因需隨拖曳變動為COLUMN(B2)、COLUMN(B3)…即為2、3…遞進,不需鎖定儲存格。

確定後即可向右拖曳。

但向下拖曳會發現,數值應該靠右貼齊才對,而不是靠左。
因位數不一樣,需要使用「REPT」函數來修正。

Step4 結合REPT函數

這邊先說明下「REPT」函數,依指定字串重覆幾次顯示。
插入函數-「REPT」函數。

Text

要重覆顯示的資料。
輸入「” “」(引號 空格 引號)

Number_times

要重覆的次數。
輸入:「10-LEN(A3)」,「10」是個、十、百、千…十億,共有10格,「LEN(A3)」為列出A3儲存格的文字有幾個字元。
如:「A3」儲存格為「463348742」,「10-LEN(A3)」即為10-9=1。

確定後公式為「=REPT(” “,10-LEN(A3))」即是「463348742」9位數不足10位數,將其前面補上一個「空格」。

再使用「&」結合原數值「A3」。

確定後看不太出差異,可以將其複製並以「」貼上來看,可以發現前面多一個空格,也就是可以補足位數。

將公式「REPT(” “,10-LEN($A3))&$A3」再代入到「Text」引數。

需向右及向下拖曳,需要同前面鎖定儲存格。

B3」儲存格的公式即為以下

=MID(REPT(” “,10-LEN($A3))&$A3,COLUMN(A3),1)

接著就可以向右及向下拖曳了,因是以「B3」儲存格做介紹,也可以複製公式至「B1:K1」儲存格。

雖然整個公式一長串頗複雜,但一步一步分解之後,其實都是不難的公式。

REPT函數更多應用

想了解「REPT」函數更多應用可點選以下

其他文章分享

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *