朱寧賢

（山東政法學(xué)院　濟南　250014）

?

Excel中數(shù)值與字符混合型數(shù)據(jù)的自動運算實現(xiàn)

朱寧賢

（山東政法學(xué)院濟南250014）

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

關(guān)鍵詞Excel運算混合數(shù)據(jù)函數(shù)變換

一、問題背景

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

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

二、長度固定且內(nèi)容相同時

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

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

三、長度固定但內(nèi)容不同時

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

如果單位內(nèi)容存在不同的數(shù)據(jù)時，解決方法是函數(shù)轉(zhuǎn)換的同時進行單位轉(zhuǎn)換。

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

四、長度和內(nèi)容均不相同時

1、數(shù)據(jù)有固定的分隔特征時

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

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

2、沒有固定的分隔標志

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

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

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

注：上述解決方法在win8+office2013環(huán)境中實現(xiàn)通過。

參考文獻

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

［2］IT部落窩.Excel提取文本和數(shù)字薈萃［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～），男，山東政法學(xué)院副教授，計算機應(yīng)用碩士；主要研究方向：數(shù)據(jù)庫原理與多媒體應(yīng)用。