Excel2003函數(shù)編程實(shí)現(xiàn)工程數(shù)學(xué)矩陣類運(yùn)算的自動(dòng)化處理

陳琰明

摘要：本文以Excel2003辦公軟件為平臺(tái)，以其自帶函數(shù)為工具，介紹了Excel應(yīng)用于工程數(shù)學(xué)矩陣類相關(guān)運(yùn)算的新方法——Excel函數(shù)編程法。通過(guò)工程數(shù)學(xué)教材中的實(shí)例計(jì)算，詳細(xì)說(shuō)明了自動(dòng)生成運(yùn)算結(jié)果的編程過(guò)程，并將該方法比對(duì)數(shù)學(xué)軟件Mathematica進(jìn)行分析。

關(guān)鍵詞：Excel函數(shù)編程；矩陣類運(yùn)算；自動(dòng)化處理

中圖分類號(hào)：G642.4 文獻(xiàn)標(biāo)志碼：A 文章編號(hào)：1674-9324（2014）10-0254-02

一、前言

在工程數(shù)學(xué)的教學(xué)中，經(jīng)常面臨這樣的困惑：矩陣的組成元素大多是10以內(nèi)的數(shù)字，涉及到矩陣類的運(yùn)算均是數(shù)字在滿足矩陣運(yùn)算規(guī)律下的加減乘除，如手工操作，運(yùn)算過(guò)程較繁瑣，結(jié)果不能快速得出。本科類院校，一般開(kāi)設(shè)數(shù)學(xué)實(shí)驗(yàn)課，引入諸如Mathematics、Matlab等專業(yè)軟件來(lái)進(jìn)行計(jì)算，而眾多高職高專院校未開(kāi)設(shè)數(shù)學(xué)實(shí)驗(yàn)課。另外，對(duì)專業(yè)數(shù)學(xué)軟件的學(xué)習(xí)勢(shì)必加重學(xué)生的學(xué)習(xí)負(fù)擔(dān)，筆者在教學(xué)改革工作中，通過(guò)多年的思考和探索，提出了一種新方法，即利用現(xiàn)有資源和條件，在學(xué)生理解基本數(shù)學(xué)概念的前提下，通過(guò)學(xué)生熟悉的辦公軟件Excel函數(shù)編程來(lái)解決這一問(wèn)題。該方法充分利用Excel函數(shù)自動(dòng)運(yùn)算功能，通過(guò)簡(jiǎn)單的數(shù)據(jù)錄入，便可完成矩陣類的自動(dòng)化運(yùn)算。方法簡(jiǎn)單實(shí)用，易于掌握，切實(shí)體現(xiàn)了高職高專教學(xué)“學(xué)以致用、夠用為度”的原則。

二、Excel2003與矩陣之間的相關(guān)性

Excel系微軟公司Office系列軟件中的一個(gè)組件，可制作電子表格，完成復(fù)雜的數(shù)據(jù)分析，不僅簡(jiǎn)單易學(xué)，且實(shí)用性強(qiáng)，具有容易獲得，普及面廣、操作簡(jiǎn)潔等優(yōu)點(diǎn)。矩陣即由多個(gè)數(shù)排列形成的數(shù)表，“電子表格、數(shù)表”均是表格，以此為橋梁和媒介，便可建立Excel和矩陣之間的聯(lián)系。

（一）矩陣的表示

單元格是Excel的最小單位，在其中輸入數(shù)字或文字后，由該單元格所在的行號(hào)和列號(hào)即可準(zhǔn)確定位。矩陣中的元素表示數(shù)表中的數(shù)字在第i行第j列，矩陣的基本類型包括行、列向量和矩陣。

例1：在Excel中分別表示2行3列的矩陣A、3行3列的矩陣B、3行4列的矩陣C。

Excel表示方法：如圖一所示，在Excel的中輸入數(shù)字，并附說(shuō)明性文字，如圖中的“矩陣A，2行3列”文字即簡(jiǎn)單明了表示任一個(gè)矩陣。

（二）矩陣中某一個(gè)元素的提取

矩陣運(yùn)算中涉及到從任意一個(gè)矩陣中提取某一個(gè)元素參與計(jì)算時(shí)，Excel2003利用計(jì)算相應(yīng)值函數(shù)Offset建立參照系，提取矩陣中的元素。

例2：從矩陣A■ 2 3 5 1110 8 9 7 5 6 4 3提取元素a23和a13。

分析：以元素a11=2所在單元格A1建立參照系，則元素 a23=9相對(duì)于a11只需下移1行，右移2列即可；同理若以a34=7所在單元格D2建立參照系，則元素a13=5相對(duì)于a34而言只需上移1行，左移1列即可，如圖二所示。Excel表示方法：函數(shù)Offset（reference，rows，cols），reference為參照系，rows是指相對(duì)于參照系偏移的行數(shù)，cols是指相對(duì)于參照系偏移的列數(shù)。特別注意，行（列）向下（右）偏移用正數(shù)表示，行（列）向上（左）偏移用負(fù)數(shù)表示。

三、Excel2003在矩陣類運(yùn)算中的應(yīng)用

（一）矩陣的加減

首先，判斷矩陣之間是否滿足相加（減）的條件，若滿足，Excel自動(dòng)完成，如下例3所示。

例3：已知矩陣A■1 2 34 5 6，B■7 8 91 2 1計(jì)算A+B。

分析：矩陣A、B的行數(shù)與列數(shù)對(duì)應(yīng)相等（均為“2×3”類型），滿足矩陣的加減法運(yùn)算，在Excel2003中標(biāo)注說(shuō)明文字，輸入A、B兩個(gè)矩陣即可完成計(jì)算。

步驟1：在單元格B4中輸入"=B1+G1"，將鼠標(biāo)箭頭放置B4單元格的右下角，當(dāng)出現(xiàn)“+”填充符號(hào)后向下填充至單元格B5，如圖三所示。

步驟2：將鼠標(biāo)箭頭放置B5單元格的右下角，當(dāng)出現(xiàn)“+”填充符號(hào)后向下填充至單元格D5，可實(shí)現(xiàn)自動(dòng)化運(yùn)算。在此例中運(yùn)用了Excel2003的自動(dòng)填充功能。

（二）矩陣的數(shù)乘

用數(shù)K（K≠0）乘以矩陣A，即用K乘以A中的所有元素。K作為一個(gè)常數(shù)是“不動(dòng)”的，在Excel2003中利用絕對(duì)引用便可實(shí)現(xiàn)。

絕對(duì)引用：在某一單元格位置符前加貨幣符號(hào)“$”，如單元格A1的絕對(duì)引用表示為“$A$1”。

例4：已知A2×3，則用K=4乘以矩陣A，新矩陣C的求解過(guò)如下：

分析：將K所在的單元格“G1”轉(zhuǎn)化為絕對(duì)引用“$G$1”進(jìn)入計(jì)算。如圖四所示。

操作步驟：與例3類似，在單元格B5中輸入"=B1*$G$1"，將鼠標(biāo)箭頭放置B5單元格的右下角，當(dāng)出現(xiàn)“+”后向下填充至單元格B6；將鼠標(biāo)箭頭放置B6單元格的右下角，當(dāng)出現(xiàn)“+”后向下填充至單元格D6，便可實(shí)現(xiàn)自動(dòng)化運(yùn)算。

（三）矩陣與矩陣的相乘

矩陣之間的乘積是整個(gè)矩陣運(yùn)算的一個(gè)難點(diǎn)，在矩陣相乘的條件、得到乘積結(jié)果的判斷上，很多學(xué)生存在困擾。筆者提出一個(gè)簡(jiǎn)便可行易于記憶的方法。

矩陣相乘的條件和結(jié)果的判斷：已知矩陣A和矩陣B分別為“A：m行n列”，“B：h行j列”，如圖五所示。

Excel2003函數(shù)計(jì)算：利用excel2003“MMULT”矩陣運(yùn)算類函數(shù)便可實(shí)現(xiàn)矩陣與矩陣之間乘積的自動(dòng)化輸出。

例5：矩陣A■1 23 45 8，B■1 5 6 72 3 1 5，計(jì)算C=AB。

分析：A、B矩陣相乘的條件和結(jié)果矩陣C的格式如上可以進(jìn)行判斷。Excel函數(shù)MMULT（array1 array2，……），array1 和array2等分別表示矩陣A、B所在的區(qū)域，在此Excel將矩陣看作是數(shù)組格式，如圖六所示。endprint

步驟1：在B6中輸入"=MMULT（B1：C3，F(xiàn)1：I2）"，選定A和B所在區(qū)域。按Enter鍵，“B1：C3”為矩陣A所在區(qū)域，“F1：I2”為矩陣B所在區(qū)域；

步驟2：根據(jù)圖五判斷，矩陣A、B相乘的結(jié)果C為“2×4”，選中放置輸出結(jié)果的單元格區(qū)域B6：E7，按F2，回到步驟1得出的結(jié)果，同時(shí)按住[Ctrl]+[Shift]+[Enter]鍵，最終得出圖六結(jié)果。

注：使用MMULT函數(shù)求解矩陣的乘積結(jié)果時(shí)，利用到Excel數(shù)組求值功能，在編輯欄輸入函數(shù)后，利用數(shù)組輸出格式“同時(shí)按住[Ctrl]+[Shift]+[Enter]鍵”進(jìn)行結(jié)果輸出。

（四）方陣的行列式和逆矩陣

逆陣存在的前提是方陣行列式的值不為零。可用函數(shù)MDETERM和MINVERSE來(lái)分別求解行列式的值和逆矩陣，下面通過(guò)例題來(lái)介紹兩種函數(shù)的用法。

例6：已知A■=1 2 32 2 13 4 3，計(jì)算A的行列式，并判斷A是否可逆，如可逆，得出A-1。

分析：求解逆陣，首先利用函數(shù)MDETERM計(jì)算矩陣A的行列式|A|，判斷|A|的值是否為零，確定A是否可逆。在A可逆的條件下利用函數(shù)MINVERSE求解得到A-1。如圖七所示：

步驟1：計(jì)算A的行列式，判斷是否可逆；步驟2：在可逆的前提下，計(jì)算A的逆陣。

（五）矩陣的轉(zhuǎn)置

矩陣的轉(zhuǎn)置是將矩陣的行元素轉(zhuǎn)換為列元素。在excel中可以利用函數(shù)“TRANSPOSE”或者使用復(fù)制-選擇性粘貼菜單選項(xiàng)中的“轉(zhuǎn)置”復(fù)選框來(lái)完成。下面參照?qǐng)D八，用函數(shù)TRANSPOSE（array）來(lái)完成矩陣的轉(zhuǎn)置。

分析：對(duì)矩陣E轉(zhuǎn)置，選中放置輸出結(jié)果的單元格區(qū)域G1：H3，然后選擇函數(shù)TRANSPOSE。選定矩陣A的區(qū)域“B1：D2”，利用數(shù)組格式輸出結(jié)果。

四、后記

矩陣將日常生產(chǎn)生活中的數(shù)表（包含數(shù)字的表格）進(jìn)行數(shù)學(xué)處理，可以幫助我們解決諸多實(shí)際問(wèn)題，尤其在解決經(jīng)濟(jì)類問(wèn)題時(shí)，利用矩陣可起到事半功倍的作用，如線性方程組的求解、投入產(chǎn)出問(wèn)題、運(yùn)輸問(wèn)題、資金投資策略以及閉合經(jīng)濟(jì)問(wèn)題等方面都有著廣泛的應(yīng)用，利用Excel函數(shù)實(shí)現(xiàn)矩陣的自動(dòng)化運(yùn)算，既能夠解決學(xué)生對(duì)運(yùn)算的困擾，激發(fā)學(xué)生學(xué)習(xí)興趣，培養(yǎng)學(xué)生的數(shù)字應(yīng)用能力，還不至于增加學(xué)生學(xué)習(xí)學(xué)業(yè)負(fù)擔(dān)，可謂一舉多得。

參考文獻(xiàn)：

[1]史玉磊.Excel函數(shù)與圖標(biāo)實(shí)用大全[M].北京：電子工業(yè)出版社，2007，3.

[2]丁嵐.調(diào)查分析基本技能[M].北京：中國(guó)財(cái)政經(jīng)濟(jì)出版社，2007，11.

基金項(xiàng)目：本文系甘肅省高等學(xué)校科研項(xiàng)目資助，項(xiàng)目編號(hào)2013B-125。endprint

步驟1：在B6中輸入"=MMULT（B1：C3，F(xiàn)1：I2）"，選定A和B所在區(qū)域。按Enter鍵，“B1：C3”為矩陣A所在區(qū)域，“F1：I2”為矩陣B所在區(qū)域；

步驟2：根據(jù)圖五判斷，矩陣A、B相乘的結(jié)果C為“2×4”，選中放置輸出結(jié)果的單元格區(qū)域B6：E7，按F2，回到步驟1得出的結(jié)果，同時(shí)按住[Ctrl]+[Shift]+[Enter]鍵，最終得出圖六結(jié)果。

注：使用MMULT函數(shù)求解矩陣的乘積結(jié)果時(shí)，利用到Excel數(shù)組求值功能，在編輯欄輸入函數(shù)后，利用數(shù)組輸出格式“同時(shí)按住[Ctrl]+[Shift]+[Enter]鍵”進(jìn)行結(jié)果輸出。

（四）方陣的行列式和逆矩陣

逆陣存在的前提是方陣行列式的值不為零?？捎煤瘮?shù)MDETERM和MINVERSE來(lái)分別求解行列式的值和逆矩陣，下面通過(guò)例題來(lái)介紹兩種函數(shù)的用法。

例6：已知A■=1 2 32 2 13 4 3，計(jì)算A的行列式，并判斷A是否可逆，如可逆，得出A-1。

分析：求解逆陣，首先利用函數(shù)MDETERM計(jì)算矩陣A的行列式|A|，判斷|A|的值是否為零，確定A是否可逆。在A可逆的條件下利用函數(shù)MINVERSE求解得到A-1。如圖七所示：

步驟1：計(jì)算A的行列式，判斷是否可逆；步驟2：在可逆的前提下，計(jì)算A的逆陣。

（五）矩陣的轉(zhuǎn)置

矩陣的轉(zhuǎn)置是將矩陣的行元素轉(zhuǎn)換為列元素。在excel中可以利用函數(shù)“TRANSPOSE”或者使用復(fù)制-選擇性粘貼菜單選項(xiàng)中的“轉(zhuǎn)置”復(fù)選框來(lái)完成。下面參照?qǐng)D八，用函數(shù)TRANSPOSE（array）來(lái)完成矩陣的轉(zhuǎn)置。

分析：對(duì)矩陣E轉(zhuǎn)置，選中放置輸出結(jié)果的單元格區(qū)域G1：H3，然后選擇函數(shù)TRANSPOSE。選定矩陣A的區(qū)域“B1：D2”，利用數(shù)組格式輸出結(jié)果。

四、后記

矩陣將日常生產(chǎn)生活中的數(shù)表（包含數(shù)字的表格）進(jìn)行數(shù)學(xué)處理，可以幫助我們解決諸多實(shí)際問(wèn)題，尤其在解決經(jīng)濟(jì)類問(wèn)題時(shí)，利用矩陣可起到事半功倍的作用，如線性方程組的求解、投入產(chǎn)出問(wèn)題、運(yùn)輸問(wèn)題、資金投資策略以及閉合經(jīng)濟(jì)問(wèn)題等方面都有著廣泛的應(yīng)用，利用Excel函數(shù)實(shí)現(xiàn)矩陣的自動(dòng)化運(yùn)算，既能夠解決學(xué)生對(duì)運(yùn)算的困擾，激發(fā)學(xué)生學(xué)習(xí)興趣，培養(yǎng)學(xué)生的數(shù)字應(yīng)用能力，還不至于增加學(xué)生學(xué)習(xí)學(xué)業(yè)負(fù)擔(dān)，可謂一舉多得。

參考文獻(xiàn)：

[1]史玉磊.Excel函數(shù)與圖標(biāo)實(shí)用大全[M].北京：電子工業(yè)出版社，2007，3.

[2]丁嵐.調(diào)查分析基本技能[M].北京：中國(guó)財(cái)政經(jīng)濟(jì)出版社，2007，11.

基金項(xiàng)目：本文系甘肅省高等學(xué)?？蒲许?xiàng)目資助，項(xiàng)目編號(hào)2013B-125。endprint

步驟1：在B6中輸入"=MMULT（B1：C3，F(xiàn)1：I2）"，選定A和B所在區(qū)域。按Enter鍵，“B1：C3”為矩陣A所在區(qū)域，“F1：I2”為矩陣B所在區(qū)域；

步驟2：根據(jù)圖五判斷，矩陣A、B相乘的結(jié)果C為“2×4”，選中放置輸出結(jié)果的單元格區(qū)域B6：E7，按F2，回到步驟1得出的結(jié)果，同時(shí)按住[Ctrl]+[Shift]+[Enter]鍵，最終得出圖六結(jié)果。

注：使用MMULT函數(shù)求解矩陣的乘積結(jié)果時(shí)，利用到Excel數(shù)組求值功能，在編輯欄輸入函數(shù)后，利用數(shù)組輸出格式“同時(shí)按住[Ctrl]+[Shift]+[Enter]鍵”進(jìn)行結(jié)果輸出。

（四）方陣的行列式和逆矩陣

逆陣存在的前提是方陣行列式的值不為零?？捎煤瘮?shù)MDETERM和MINVERSE來(lái)分別求解行列式的值和逆矩陣，下面通過(guò)例題來(lái)介紹兩種函數(shù)的用法。

例6：已知A■=1 2 32 2 13 4 3，計(jì)算A的行列式，并判斷A是否可逆，如可逆，得出A-1。

分析：求解逆陣，首先利用函數(shù)MDETERM計(jì)算矩陣A的行列式|A|，判斷|A|的值是否為零，確定A是否可逆。在A可逆的條件下利用函數(shù)MINVERSE求解得到A-1。如圖七所示：

步驟1：計(jì)算A的行列式，判斷是否可逆；步驟2：在可逆的前提下，計(jì)算A的逆陣。

（五）矩陣的轉(zhuǎn)置

矩陣的轉(zhuǎn)置是將矩陣的行元素轉(zhuǎn)換為列元素。在excel中可以利用函數(shù)“TRANSPOSE”或者使用復(fù)制-選擇性粘貼菜單選項(xiàng)中的“轉(zhuǎn)置”復(fù)選框來(lái)完成。下面參照?qǐng)D八，用函數(shù)TRANSPOSE（array）來(lái)完成矩陣的轉(zhuǎn)置。

分析：對(duì)矩陣E轉(zhuǎn)置，選中放置輸出結(jié)果的單元格區(qū)域G1：H3，然后選擇函數(shù)TRANSPOSE。選定矩陣A的區(qū)域“B1：D2”，利用數(shù)組格式輸出結(jié)果。

四、后記

矩陣將日常生產(chǎn)生活中的數(shù)表（包含數(shù)字的表格）進(jìn)行數(shù)學(xué)處理，可以幫助我們解決諸多實(shí)際問(wèn)題，尤其在解決經(jīng)濟(jì)類問(wèn)題時(shí)，利用矩陣可起到事半功倍的作用，如線性方程組的求解、投入產(chǎn)出問(wèn)題、運(yùn)輸問(wèn)題、資金投資策略以及閉合經(jīng)濟(jì)問(wèn)題等方面都有著廣泛的應(yīng)用，利用Excel函數(shù)實(shí)現(xiàn)矩陣的自動(dòng)化運(yùn)算，既能夠解決學(xué)生對(duì)運(yùn)算的困擾，激發(fā)學(xué)生學(xué)習(xí)興趣，培養(yǎng)學(xué)生的數(shù)字應(yīng)用能力，還不至于增加學(xué)生學(xué)習(xí)學(xué)業(yè)負(fù)擔(dān)，可謂一舉多得。

參考文獻(xiàn)：

[1]史玉磊.Excel函數(shù)與圖標(biāo)實(shí)用大全[M].北京：電子工業(yè)出版社，2007，3.

[2]丁嵐.調(diào)查分析基本技能[M].北京：中國(guó)財(cái)政經(jīng)濟(jì)出版社，2007，11.

基金項(xiàng)目：本文系甘肅省高等學(xué)?？蒲许?xiàng)目資助，項(xiàng)目編號(hào)2013B-125。endprint