返回列表 回復 發帖

EXCEL解覺雞兔問題

EXCEL解覺雞兔問題
:“雞兔問題”是一道古典數學問題,源自我國古代四、五世紀的數學著作《孫子算經》。算經卷下第三十一題為:“今有雉、兔同籠,上有三十五頭,下有九十四足。問雉、兔各幾何?”原著的解法為:“上署頭,下置足。半其足,以頭除足,以足除頭,即得。”具體解法即:分別列出總頭數(35)和總足數(94),總足數除以二,再減去總頭數(94÷2-35),得到兔數為12,總頭數減去兔數35-12得到雞數為23。
  雞兔問題本身並不難,使用2元1次方程組的消元演算法,可以很快得到答案。我們可以嘗試著利用Excel提供的各種計算工具來進行計算,不僅別有趣味,而且還會加深對Excel功能的綜合掌握,對於講授Excel的教師而言,則是典型的一題多解的素材。

  一、 利用IF函數試探求解

  創建一個二維表,假設雞數B2為要求解的單元格,將雞兔的總頭數和腳數分別寫入D2和D3單元格,利用已知條件在其他單元格中寫入公式:因兔頭數=總頭數-雞頭數,故在C2單元格中寫入=D2-B2;雞腳數=雞頭數*2,故B3單元格寫入=B2*2;兔腳數=兔頭數*4,故C3單元格寫入=C2*4。

  接下來我們在任意其他單元格輸入一個判斷公式(本例中使用F1單元格),公式內容為=IF(D3=B3+C3,"正解!",IF(D3>B3+C3,"高了","低了"))。公式的本質是判斷雞腳數+兔腳數與總腳數之間的關係,如果判斷運算式D3=B3+C3結果為True,就意味著我們已經得到了正確答案。

  最後在B2中輸入35以內的任意整數進行試探求解。如果輸入的數值高於正解,判斷單元格F1會提示“高了”,若數值小於正解則提示“低了”,用戶根據提示再繼續輸入其他一個數字,直到輸入了正確答案23,F1單元格會顯示“正解!”。

  這種方法比較直觀,但是非常笨拙,需要人工干預。即使用戶聰明地使用二分法試探,也需要多次輸入才能解決問題,對於更龐大的問題,這種解法幾乎是不可行的。

利用IF函數試驗求解

  二、使用模擬運算表,讓Excel自動給出答案

  第一種方法存在的問題就是非常繁瑣,需要用戶干預。為了避免用戶干預,可以考慮將雞兔問題轉化為雙變數模擬運算表,將雞數和兔數設置為兩個變數。具體做法是:

  1. 先在A12單元格中輸入參考數據如下:

參考數據

電腦知識

  2. 創建一個二維模擬運算表的框架,因為雞的數目不會超過腳數/2,即雞最多為47只,同理兔子數目不會超過94/4,即兔最多為24只。我們用第4行表示兔的數目,用第C列表示雞的數目。在D4:AA4中填充1,2,3…24等數值,在C5:C47中填充1,2,3…47,參見圖 4;

  3. 在模擬運算表的左上方C4單元格中輸入模擬運算表的公式:=IF(2*A2+4*B2=$C$2,IF(A2+B2=$D$2,"正解","X"),"X"),公式中的$C$2和$D$2單元格為已知的總腳數和總頭數,A2和B2將作為模擬運算表的兩個變數;

  4. 選中模擬運算表區域,即C4:AA47區域,然後選擇“數據”菜單中的“模擬運算表”菜單項,打開模擬運算表對話框。在對話框中,輸入引用行的單元格為$A$2(即雞數),輸入引用列的單元格為$B$2(即兔數),單擊“確定”按鈕;

輸入引用行和引用列的單元格

  5. 在模擬運算表中會顯示出計算結果,在所有的運算表區域中,只有Z16單元格中顯示了“正確”兩字,其餘單元格均為“X”,表示Z16單元格為問題的正解,查表可知,Z16單元格的兔數為12,雞數為23。

模擬運算表運算結果

  這種使用模擬運算表的方法比較“另類”。利用這種思路,不僅可以求解多元一次方程組,還可以求解多解問題。

  三、使用規劃求解,將苦活拋給Excel

  利用Excel的規劃求解功能,我們可以利用電腦高速計算的特性求解雞兔問題。如果用戶的“工具”菜單中沒有“規劃求解”菜單項,可以選擇“工具”?“加載宏”,在“加載宏”對話框中選中“規劃求解”並按下“確定”,此後在“工具”菜單就可以看到“規劃求解”功能了。

規劃求解加載宏

  新建一個工作表,單元格B1為總腳數,輸入公式=2*B3+4*B4;B2為總頭數,輸入公式=2*B3+4*B4,B3和B4單元格用於顯示計算雞數和兔數的結果,暫時留空。為求直觀友好,可分別在A1、A2、A3、A4單元格中輸入文字提示:“總腳數”、“總頭數”、“雞數”和“兔數”。

規劃求解表

  然後選擇“工具”菜單下的“規劃求解”,在“規劃求解參數”對話框中,設置目標單元格$B$1等於固定值94(即總腳數等於94),將可變單元格設置為$B$3B$4,即欲求解的雞數B3和兔數B4。在“約束”欄中,添加三個約束條件:$B$2=35(即總頭數等於35),$B$3和$B$4為整數。

規劃求解對話框

  規劃求解參數設置完畢後,按下“求解”按鈕,Excel很快地給出了正確答案:雞數B3單元格為35,兔數B4單元格為11.99999975。求解結果中兔數為小數形式,是規劃求解過程中的計算誤差。因為本問題是二元一次方程組求解,屬於線性問題,用戶可以在規劃求解參數對話框中按下“選項”按鈕,選中“採用線性模型”即可在計算結果中正確顯示整數。

  使用規劃求解,可以利用電腦高速計算的特點對複雜問題建模求解,同樣的思路也適合於解決多解的方程問題。

  四、 利用矩陣函數,線性代數思路解決問題

  雞兔問題是二元一次方程組,可以利用線性代數方法進行求解。根據題意列出二元一次方程組為:

  其中x為雞數,y為兔數。根據方程組由線性代數方法可以列出如下兩組矩陣A和矩陣B:

  設所求矩陣為x,則方程組轉化為AX=B,即。

  根據如上的數學分析,我們可以利用Excel中矩陣函數的獨特功能,使用矩陣逆函數MINVERSE對矩陣A求逆,然後利用矩陣乘函數MMULT對矩陣A的逆矩陣和B矩陣進行乘法運算,得到的結果矩陣就是方程組的解。

  具體做法如下:

  1. 在A1:B2區域中輸入矩陣A的數值,在D12區域中輸入矩陣B的數值;

  2. 求取A的逆矩陣。選中B4:C5單元格,輸入數組公式=MINVERSE(A1:B2),確認時必須按下Ctrl+Shift+Enter組合鍵;

  3. 求取A的逆矩陣和B矩陣的乘積。選中B7:B8單元格,輸入數組公式=MMULT(B4:C5,D12),確認時必須按下Ctrl+Shift+Enter組合鍵;

  4. B7、B8單元格的計算結果為23和12,即雞數為23,兔數為12。

矩陣函數求解

  使用矩陣函數的方法,在本質上是解決數學中的n元一次方程組的問題,具有比較廣泛的通用性。

  五、 使用VBA編程求解

  雞兔問題也可以編程解決。打開菜單“工具”?“宏”?“Visual Basic編輯器”,選擇VBA編輯器的“插入”?“模組”菜單,並輸入如下代碼:

  Sub chickrabbit()

  For chick = 1 To 35

  For rabbit = 1 To 35

  If (chick + rabbit = 35) And (2 * chick + 4 * rabbit = 94) Then

  MsgBox "雞的數量為" & chick & ",兔為" & rabbit

  End If

  Next rabbit

  Next chick

  End Sub

  編輯完畢後關閉VBA窗口,然後選擇“工具”?“宏”,然後執行chickrabbit宏,就會彈出正確答案。該程式可以做進一步的改進,例如可以改進執行方式、直接調用工作表數據、將輸出結果顯示在Excel單元格中等等,篇幅所限,不再贅述。

  本文分別使用了IF函數試探、雙變數模擬運算表、規劃求解、矩陣函數和VBA編程等方法對古典雞兔問題進行求解,目的並不是求解簡單數學問題的答案,而是旨在通過多種方法求解,展示Excel的多功能性和解決方式的靈活性,進一步開拓分析問題、解決問題的思路。
返回列表