在Excel中,經常會遇到數字求和等于0的情況,明明是一大批看起來一本正經的數字,可為什麼就是無法計算呢?其實,Excel數字求和等于0的原因,無非就是兩種,要麼是格式有問題,要麼是内容有問題。進來我們一一來進行驗證!
先看第一種情況
一、格式有問題
在Excel中,數字有文本型和常規型之分,所以有些看起來都是數字,但卻并不一樣,文本型的數字就不能求和。如下圖所示,我在A列輸入的就是文本型的數值,C列輸入的就是常規型的,從外貌上來看,文本型數值最大的特點是在單元格的左上角有一個綠色的小三角,它表示以文本形式存儲的數字。
文本型數值和常規型數值
通過下圖,可以看到常規型的數值很容易就被SUM函數給求和了
常規型的數值可以通過SUM函數進行求和
但是,對文本型的數值,SUM函數無法将它們進行求和,求和的結果等于0
文本型的數值不能用SUM函數進行求和
通過對上面兩個截圖的了解,我們知道了,要想求和,就必須将文本型數值轉換為常規型的,那麼應該如何去做這件事呢?
選擇文本型數值的單元格區域,點上方黃色歎号中的“轉換為數字”,如下圖所示
設置“轉為數值”
設置完成後,即刻生效,下面的SUM函數立刻得出計算的結果
轉換完成
但是要注意的是,多選單元格區域的時候,如果是按整列選擇了,或者第一個選擇的單元格不是文本型數值的單元格,則不會出現黃色感歎号那個選項,如下圖所示
按整列選擇,則不會出現黃色感歎号的選項
第一個選中的不是帶綠三角單元格,也不會出現
所以,敲黑闆!!!第一個選中的單元格必須是一個文本型的數值,因為隻有這樣才會出現黃色的感歎号選項。
黃色感歎号裡面的選項,除了“轉換為數字”,還可以選擇“忽略錯誤”,如果當我們需要的就是文本型的數值,但又不想顯示綠色小三角的時候,就可以選擇“忽略錯誤”了
設置“忽略錯誤”
當設置忽略錯誤後,數值依舊還是文本型的數值,但是已經不再顯示左上角的綠色小三角。那麼,問題來了!如果面對這種數據,想将其轉換為常規型的數值,應該怎麼辦?顯然通過上面的方法已經不能夠了,因為連黃色感歎号的選項都沒有了
沒有小綠三角的文本型數值
接下來介紹兩種方法,解決這個問題
1、數據分列法
選擇單元格區域,切換至“數據”選項卡,在“數據工具”選項組中,選擇“分列”命令
選擇“數據分列”
在打開的“文本分列向導-第1步,共3步”對話框中,點下一步,然後再點下一步,到最後一步也就是第3步的時候,選擇“常規”(一般情況都不用選,默認的就是常規),然後點右下角的“完成”按鈕。
返回工作表中,既可以看到已經将文本型數值轉換成常規型了。
轉換成功
2.選擇性粘貼法
如下圖所示,複制任意一個空白單元格
複制一個空白單元格
選擇文本型的單元格區域,點鼠标右鍵,在打開的快捷菜單中選擇“選擇性粘貼”命令,然後在打開的“選擇性粘貼”對話框中,将運算設置為“加”,如果不希望格式産生變成,可以再選擇上面的“數值”(表示隻粘貼數值,而不粘貼格式),設置完畢後,點“确定”按鈕
設置運算方式為“加”
返回工作表中,即刻看到文本型數值已經轉換為常規型了
轉換成功
下面來看第二種情況
二、内容有問題
如果通過以上3種方法都無法将一個不能求和的數字轉換成可以求和,那麼就可以排除是格式有問題了,基本上可以斷定是内容有問題,什麼叫内容有問題呢?意思就是内容肯定不是純純的數字,單元格裡肯定還包含了其他我們眼睛看不見的空白字符。(注意,這裡說的空白字符不一定是指空格,空白字符有很多種,空格隻是其中的一種)
選擇一個單元格,在編輯欄雙擊,通過光标閃爍的位置,可以看到确實是有多出來的空白字符,如下圖所示
空白字符在數字的右邊
從編輯欄複制一個空白字符,注意看裡面是有幾個,如果超過一個,也不要複制多了,隻能複制一個
在編輯欄複制空白字符
選中單元格區域後,通過上述介紹的方法打開“文本分列向導”對話框,在第2步的時候,将分隔符号設置為“其他”,然後将剪貼闆上的空白字符粘貼到右面的文本輸入框裡,如下圖所示
設置分列符号
進入第3步之後,在數據預覽下方的文本框中,選中數字的列設置為“常規”,數字以外的列,設置為“不導入此列”,如下圖所示
設置輸出的格式
返回工作表中,即可看到已經将單元格内的空白字符全部清除,數字已經可以正常的求和了。
操作成功
可是空白字符的種類有很多種,除此之外,還有一類的空白字符是無法在編輯欄中看到的,如下圖所示,明明知道單元格内的内容有問題,使用LEN函數計算字符的個數也明顯多于眼睛能夠看見的個數,可是在編輯欄卻看不出有其他多餘字符的迹象,光标閃爍的位置根本看不出來有其他字符,那這種又應該怎麼辦呢?
編輯欄中無法看出有多餘的字符
首先,我們使用LEFT函數進行一個從左側的提取,提取的位數為1位,如下圖所示,提取出來是數字1,那麼說明多出的空白字符,在數字的右邊,知道它存在的位置了,接下來我們就可以寫公式提取
判斷空白字符出現的位置,是在前還是在後
在輔助列輸入公式=LEFT(A2,LEN(A2)-1)*1,将公式向下填充,即可完成轉換
操作成功
大家都學會了嗎?有沒有想要補充和提問的呢?歡迎大家在下方留言讨論~
,更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!