Excel中數值與字符混合型數據的自動運算實現

朱寧賢

（山東政法學院　濟南　250014）

?

Excel中數值與字符混合型數據的自動運算實現

朱寧賢

（山東政法學院濟南250014）

摘要Excel在處理數據方面能力很強大，但它也有一套規則和標準規范，即一個單元格只有一個屬性。學生在學習excel時，只是掌握了利用函數和公式的自動運算功能，但如果函數與公式中的數據帶有單位，excel就會改變數據的本質屬性，從而導致運算的失敗。很多學生對此感到困惑不解。尤其是帶有不同長度的單位時，更是給數據的處理帶來較大的困難。如何才能解決帶用單位的excel數據運算呢？本文結合教學中積累的經驗，通過設置通用格式設置、顯示格式設置、函數轉換數據、函數提取數據等方式，從不同的途徑探討了固定長度單位、任意單位的數據運算方法，解決人們實際生活中經常遇到的實際問題。

關鍵詞Excel運算混合數據函數變換

一、問題背景

Excel具有強大的數據處理功能，廣泛應用在金融、辦公等領域。人們經常利用它的公式和函數進行數據自動運算，大大提高了辦公效率。在Excel教學中，學生只是掌握了利用函數和公式的自動運算功能，但如果要處理的數據帶有單位，Excel就會出現“#VALUE”錯誤提示，如圖1所示，學生對此困惑不解。結合多年的教學和生活中常遇到的問題，我們探討了產生此問題的原因：Excel的“一格一屬性”的規則，即一格單元格中只能有一種數據類型，如果數據和字符等不同數據混合在一起時，Excel自動將數據類型轉換為字符型，而字符型數據在運用公式和函數計算時，就必然出現錯誤提示。這就是帶有單位的Excel數據不能進行運算的原因。如何才能解決這一問題？

按照多年的教學經驗，并結合日常中常遇到的應用情景，我們按照使用單位的不同，將數據分成長度固定且內容相同、長度固定但內容不同、長度和內容均不相同等情況，采用不同的解決策略，實現各種情況下的帶有單位的Excel運算問題。

二、長度固定且內容相同時

解決方法：采用通用格式方法。在需要運算的單元格單擊鼠標右鍵，選擇“設置單元格屬性”，在彈出的對話框中，選擇“數據”選項卡，找到其中的“G/通用格式”，直接輸入“元”（注意：輸入時不要帶用引號），然后確定。關閉“設置單元格屬性”對話框返回Excel主程序后，再輸入單價的數值，系統會自動帶上單位，如圖3所示，但其數據類型依然為數值型（右對齊），所以可以直接計算結果。采用這種自定義格式，只不過是改變了單元格的顯示形式，而沒有改變單元格存儲的內容。

解決方法2：采用left（）字符函數進行截取。對于圖1所示的內容，將兩個單元格直接相乘修改為函數“=LEFT （A2，LEN（A1）-1）*B1”，如果想顯示出單位來，可以采用通用格式來顯示（參照2.1方法），如圖4所示。

三、長度固定但內容不同時

利用2.1的方法2，還有一個好處，即可以處理不同內容單位的數據運算，如圖5所示，只需要對B2也采取函數截取即可以。

如果單位內容存在不同的數據時，解決方法是函數轉換的同時進行單位轉換。

在表格當中有兩個單位，分別是元、角，如何統一還原位數并且計算？帶單位的數字是沒法進行數據運算的，只能將其進行拆分后，即由一列改為數字與單位兩列，這種情況下，首先要對單位，數字進行統一，比如單位中有元，也有角還有分時，就要按一個單位進行統一，比如均按元進行統一。同時在單位進行統一時，數字部分也要伴隨轉換。如原2角需轉換成0.2元，這樣轉換后，就可以進行數字部分的運算，同時在運算結果后再將單位添加。當然在實際應用中，最好還是使用相同的單位才好。

四、長度和內容均不相同時

1、數據有固定的分隔特征時

解決方法時：采用分列處理。如果數據和字符之間存在有固定的分隔符，可以采用數據列分隔的方法。如圖7可以采用引入列的方式，首先使excel表符合一格一屬性的要求，然后在進行計算。

方式是先將數量列分解為數值和單位列，操作步驟為“數據”|“分列”，選擇“空格”，將原有表分割為兩列，如圖8所示，然后就可以進行計算，如圖9。

2、沒有固定的分隔標志

解決方法：采用lookup（）函數處理。但如果沒有固定的分隔符，只能采用函數lookup（）來處理，將其中的數據讀取出來，如圖所示。公式含義說明：分別提取數量單元格的左邊1位、2位、3位……直到1000位，組成一個包含1000個元素的內存數組。再用“--”將其轉換成數值，最后通過LOOKUP函數從這個數組中提取最大值，即目標數量。該值乘以單價即金額。

利用lookup函數，不僅能夠處理字符在右側的數據，而且結合right（）、mid（）函數，也可以處理字符在左側或者字符在兩側的函數。

通過上述方法，可以將帶有任何不同單位的數據進行運算處理，尤其是最后一種方法，可以在不用對已有數據表進行額外處理，僅借助于lookup（）函數，就可以進行直接的運算處理，大大增強了excel的數據處理能力。

注：上述解決方法在win8+office2013環境中實現通過。

參考文獻

［1］文杰書院.excel2010公式函數圖表與數據分析［M］.清華大學出版社.2013.03.

［2］IT部落窩.Excel提取文本和數字薈萃［OL］. http: //www.ittribalwo.com/article/909.html.

Operatedautom Atically of Character and Number's Data Type in Excel

Zhu Ningxian
（Shandong University of Political Science and Law 250014）

AbstractExcel hasadvanced capabilities to process data, but it also has a set of rules and standards, known as a cell is only one attribute.When students learn Excel,they only study some automatic operation of functions and formulas. If there are some units in them, excel will change the essential attribute of data, which leads to the failure of the operation.Many students feel confused about this.Especially when the units have different length, it is more difficult to deal with it. How to solve the data with units in Excel?Based on the author's teaching experience, this paperhas four settings for data, i.e.general format、Display format、convertedand extracted function, discuss the way to deal with the fixed and unfixed length dataunit, solve practical problems that people often meet in their life.

KeywordsExceloperate Mixeddat a Functiontransform

中圖分類號TP317.3

文獻標識碼B

文章編號160314-7226

作者簡介

朱寧賢（1971～），男，山東政法學院副教授，計算機應用碩士；主要研究方向：數據庫原理與多媒體應用。