如果你是一名财務人員,可能需要将某個表示金額的數值拆分在多個單元格中,就像是這樣。
初次遇到這個問題時,你可能很頭痛,心想“這麼多數據,我要是手動拆分他們,想想都崩潰”。
其實,借助Excel的強大功能,實現這種操作其實很簡單。
- ①首先在N3單元格中輸入公式
- ②然後填充到所有單元格就行了。
下面我們來分析一下,為什麼公式是這樣的。
1. 消除掉難纏的小數點我們需要提取的金額精确到了“分”,比如金額"0.12"中,小數點是多餘的,所以我們可以先把金額乘以100,用來将小數點消除,同時,在金額前面加上一個人民币符号"¥",所以第一步的公式就是這樣的。
比如我們要在金額"0.12"中提取出它的"角"所在位的數字,我們可以按照這樣步驟進行。
- ①首先在文本"0.12"中取出文本"12";
- ②然後再從文本"0.12"中取出首位文本"1"。
值得注意的是,公式"=LEFT(RIGHT(B3,2),1)"中的長度"2"隻能用于提取"角"所在位使用,當需要提取其他金額單位時不再适用。
3. 通用的金額提取長度為了是上一步驟中不同金額單位提取時,長度使用統一的公式,我們可以使用列号公式COLUMN。我們分别計算出不同金額單位提取時所需的長度,以及其對應的列号,如下圖。
可以發現,不同金額單位提取所需的長度與其前一列的列号之和都等于14,而14就是最低的金額單位——“分”所在的列号。所以我們可以得到如下結果:
長度 = 分所在列列号 – 前一列列号。
通過上面的分析,我們可以得到提取"0.12"的"分"所在位的公式為:"=LEFT(RIGHT("¥"&$B3*100,COLUMN($N:$N)-COLUMN(M:M)),1)",我們填入公式,并向左側填充,得到下面的結果。
可以看到,出現了很多我們不想要的人民币符号。這是因為金額"0.12"在提取"十元"所在位時,提取長度為4,而我們改造後的文本"¥12"的長度隻有3,所以使用公式RIGHT("¥12",4)的結果為文本"¥12",再使用公式LEFT("¥12",1)的結果就是"¥"。
所以我們可以在改造金額文本時,在符号"¥"前加入空格,使其變為"¥",這樣上面的問題就解決了。
至此,我們就得到了最後的公式,隻需要填入公式,然後填充就可以啦。就得到了文章開頭的結果。
怎麼樣,你學會了嗎。你在使用excel中遇到過什麼難題嗎?可以在評論區給我留言,謝謝大家,我們下期再見。
,