VBA應用-遞迴、迴圈應用於”數學動動腦”問題

故事如下：可愛的小翊萱蹦蹦跳跳的跑到正在看卡通海綿寶寶笑的很開心的我旁邊，叔叔、叔叔可以問你一個數學問題嗎？當然啦!可愛的小翊萱問我問題哪有拒絕的道理勒，看到題目，”數學動動腦”，不看還好，一看差點就昏倒

一個很簡單敘述的數學問題–

題目如下：

將□內任意填入＋、－、×、÷ 四個運算符號，並將運算結果為117通通列舉出來

        1□2□3□4□5□6□7□8□9□10=117

 

 例如:  1+2+3*4+5+6-7+8+9*10=117

        1+2-3+4*5+6-7+8+9*10=117

 

        ….        

 

    很簡單吧，我是說題目說的很簡單，難度根本就是靠北，我很懷疑這個可以用人腦求解出答案嗎？這絕對要用電腦求解吧！ 4^9 組合；共有262144配對組合，老師到底是要考小朋友還是要考小朋友的父母的阿，難怪上次新聞報導小學生國文學修辭，原來所言不假。因此我很認真的問小翊萱說，妳們老師是男生還是女生啊，是不是漂不漂亮啊 ^___^ ……..

好，重點不是這個，重點是如何寫這道數學題目，一開始想到是窮舉法(即暴力破解法)利用9個迴圈代入算式，但是卻忽略了先乘除後加減的問題，導致後來驗證時發現求解不正確，也不知道是何原因，搞了快一下午，還好我夠宅，才能忘卻時間對我的影響，反正用利用迴圈求解的流程就是，將有乘除的符號先求出解來，之後再求出的解代入原式子便可求出正確結果

例：1+2+3×4÷5-6×7+8×9+10 =?

=>先整理乘除之解3×4÷5=2.4   6×7=42   8×7=42

=>再代入原式 1+2+2.4-42+72+10 =? 117

EXCEL 迴圈求解範例1　下載

後來試著利用遞迴的方式，比起利用迴圈來求解可以剔除很多條件式的流程，可以簡省很多時間去想條件式的規則，但是奇怪的是比起迴圈求解還要來的快，這是我沒辦法理解的，照理說應該是遞迴求解比較會花時間在處理Stack上，怎麼會反而是遞迴來的求解速度快哩？還是我迴圈寫的太多贅詞導致求解時間太慢? 反正這問題就留給之後的我在去傷腦筋吧，還是遇到高手再請教一下看看問題出在哪邊。

EXCEL 遞迴求解範例2　下載

後記：1.翊萱後來說了一句，我們老師生了兩個可愛小寶寶把我嘴給堵住了…

      2.海綿寶寶是一部真的是很好笑的卡通，有時我們全家還會一起觀賞

 

EXCEL 自訂清單

通常我們打入1、2之後 便可以用滑鼠一直拉到1、2、3、4、5…….嗎

但是很糟糕的 我們打入 一、二…….就沒辦法從一、二、三、四、五、六…….了(只能一、二、一、二不斷重覆)

 

其實我們可以從->工具->選項 ->自訂清單中 增加我們要的資料，像我依序打入一、二、三、四、五、六、七、八、九、十之後按"新增"儲存

 

 

再輸入一次  看看 是不是可以拉出來了

 

 

也可以從網路抓資料 選取我們要的資料 複製下來

 

 

按右鍵選擇性貼上

 

 

接著使用文字匯入精靈

 

 

或者也可以使用 ->資料->資料剖析

 

 

反正就是要到資料剖析精靈 選擇"分隔符號"然後按下一步

 

 

然後到 "其他 "打勾 再打入 " . "

 

 

看看 壹貳叁肆….   是不是一格一格分開來了呢  接著一樣到->工具->選項-y自訂清單->利用 "匯入情單來源"  來匯入我們要的資料

 

 

圈起 我們要的資料

 

 

然後按"匯入"

 

 

然後 試著打入壹、貳 之後往下拉 看看會不會得到想要的結果

數讀

看來要寫一個好的 EXCEL模式下的數讀 看來我這腦袋瓜 還是不太靈光

可以牽拖到因為當兵所以變笨嗎  = = + (不管了 懶得想了 明天就要休假了  )

不想把時間浪費在這 以後閒閒沒事幹再想就好

又不禁想起以前研一時 為了交報告程式 小鄭學長陪我一起熬夜寫程式 Dedug >////< 真是令人懷念的年代阿

本來想寫一個可以即時顯示出答案的EXCEL數讀解答 只要用上EXCEL本身的函數即可

不過草草寫完就懶得寫了 還是有很多數讀解答的邏輯沒寫上

本身就不太會玩數讀了 還是希望有玩數讀的高手教一下小弟數讀要怎麼玩比較快上手

這樣也比較好寫在EXCEL

當然最好是高手能幫我補充上去 讓整個更加完善 不知道是否能在我下次收假前看到成果說

檔案畫面

 

檔案下載 

 

馬上就有網友為我解答 感謝 JieJuen 、  sojibai

 

微軟官方網頁就有一數獨的範本可供下載 = = +
http://office.microsoft.com/en-us/templates/TC100809721033.aspx

Orz  早問網友連想都不想了

一個不錯的EXCEL教學的網站

 

    Excel Trump Yahoo 知識+ 知識名人分享 Excel 功能、公式、函數、密技、線上教學…

    留言版那邊有些網友問的問題真是難為他了……

資料查詢 “match" 函數應用

當參一時常常要從一堆人名抓出幾個特定的人出來，雖然可以使用"尋找"這個選項來找資料，不過常常費時又費眼 @@

所以我常用EXCEL的函數 "Match" 來找，用Match 來找資料有兩個優點，一個是不用費時一個一個去找，

另一個是可以告知我資料的位置在哪，這對建資料庫的人是個很方便常用的函數

，以某一數值，來延伸查詢到某一值的其他資料

可以更彈性的運用和參照來完成我們要的需求。

 

下圖範例  右邊為要查詢的人名

 

接著可以利用 "Match" ‘的函數指令 打法為 match(左邊所有資料人名之陣列,欲查詢之人名陣列,0) , 0代表資料須完全符合

要注意的是陣列的英文與數字前都需加上 "$" 來固定陣列位置，接著只要用滑鼠往下拉即可。

也可以做一個"排列"的動作把有用的資料排到最前面

由下圖可知  一下就可以很清楚的把所需要的人名通通找出來了，也不需利用"尋找"的選項"一筆一筆"的來找資料了

而下圖則是Match的延伸用法 ， 可以說是資料庫查詢資料必備函數，有興趣可以 下載範例 可以參考看看 不過這個範例我亂舉例的

所以沒啥意義 只是把函數的用法介紹而已  就不要太苛責我對實際上市場的商品價格和利潤的算法了

列出1…5 排列組合

如何列出1..n 的所有排列組合

例如 123 的所有排列組合 為  123 132 321 132 213 231  3! =  6種

可是如果12345 勒 就將近有120種排列組合 如果擴增到 1…10勒 就會有10!=3628800種的排列組合

這時人腦便沒辦法一個一個將他展現出來了

而且此類問題可以歸類為NP hard 問題 也就是數字規模增大 求解時間以及複雜度將會非線性的來增加

我一開始是用暴力法 也就是所謂的窮舉法一一把他列出

但是後來發現如果用遞迴(類似於Branch and bound)的方式   會比較節省運算的時間和記憶體空間

如網路上這位網友所提的概念 http://new-acos.blogspot.com/2007/07/blog-post_04.html

 

不過我既然用了窮舉法可以解出來 就懶得想遞迴的方式了 以後再說吧 或許有厲害網友或同學可以幫我寫出來讓我參模學習一下

P.S. 窮舉的方式 在EXCEL執行沒辦法解到 1…8 => 記憶體不足 XD 窮舉法實在不是好方法阿

那小弟真的是感激不盡啊

 

兩種窮舉法的原始碼列舉如下(意思是差不多的)

第一種 :

 

Dim y(5)
y(1) = 1
y(2) = 2
y(3) = 3
y(4) = 4
y(5) = 5

‘==================================================
k = 1

For a = 1 To 5
For b = 1 To 5
For c = 1 To 5
For d = 1 To 5
For e = 1 To 5

If e <> d And e <> c And e <> b And e <> a Then
 If d <> c And d <> b And d <> a Then
  If c <> b And c <> a Then
    If b <> a Then

Cells(k, 1) = y(a) & y(b) & y(c) & y(d) & y(e)

k = k + 1

   End If
  End If
 End If
End If

Next
Next
Next
Next
Next

 

第二種

 

‘=================================================
‘==使用者輸入
‘=================================================

n = 5
s = 120
Dim y(5)
Dim U(120)

‘=================================================
‘==使用者輸入
‘=================================================
y(1) = 1
y(2) = 2
y(3) = 3
y(4) = 4
y(5) = 5

‘==================================================

k = 1

For a = 1 To n ‘==使用者輸入
For b = 1 To n
For c = 1 To n
For d = 1 To n
For e = 1 To n

 

Cells(k, 1) = y(a) & y(b) & y(c) & y(d) & y(e)  ‘==使用者輸入

k = k + 1

 

Next
Next
Next
Next
Next
‘==================================================

For a = 1 To n ^ n
 For i = 1 To n
  For j = 1 To n
ten = Mid(Cells(a, 1), i, 1)
ten2 = Mid(Cells(a, 1), i + j, 1)

If Mid(Cells(a, 1), i, 1) = Mid(Cells(a, 1), i + j, 1) Then

Cells(a, 1) = ""

End If
  Next
 Next
Next

k = 1

For x = 1 To n ^ n

If Cells(x, 1) <> "" Then

U(k) = Cells(x, 1)

k = k + 1

End If

Next

For x = 1 To s

Cells(x, 2) = U(x)

Next

 

哪種方法比較好用 答案是都很差 呵呵  應該還是要走演算法的方式會比較好

而且我的版本 還要隨著數字的規模大小而做修改 所以算是很差的寫法

而且窮舉法最大的缺點就是 數字規模越大 所花費的時間是成非線性成長的

所以如果有更好建議或想法 請跟我說 我會很感激

 

兩種窮舉法EXCEL範例下載   下載1   下載2

在 Excel 2007 中算術的bug已經被修復

INTRODUCTION

Hotfix 套件修正問題

此 Hotfix 套件修正了， 不是先前已記載於 Microsoft Knowledge Base 中的下列問題：

•	在 Excel 2007 中, 執行計算時就會發生下列問題： 網頁介紹

http://support.microsoft.com/default.aspx/kb/943075/

 

下載修正檔約 32MB

http://download.microsoft.com/download/6/1/3/61343075-aa12-4152-a761-fccc16d6cef4/office-kb943075-fullfile-x86-glb.exe

 

Excel 2007重大bug：850×77.1＝100000

Google Group裏一個以微軟Office Excel爲主題的新聞組

裏有人報告說,Excel 2007裏存在一個重大bug,

會將850*77.1錯誤地計算爲100000,而不是65535.

出現這麽低級的bug,微軟工作人員開發和審核的不嚴謹是很顯然的

,至少可以說是百密一疏,尤其是舊版産品反而沒有問題,無疑會讓微軟

更加尷尬.

850*77.1並不是一個特例,不信你可以在Excel 2007裏計算下邊這些乘法:

=5.1*12850

=10.2*6425

=20.4*3212.5

=40.8*1606.25

=154.2*425

=212.5*308.4

=308.4*212.5

=425*154.2

結果都是100000吧？這個看起來有些不可思議的問題很快得到了多名專家的確認,

Excel MVP Bernad Leingme更是第一個站了出來.

 

感想：還好拉 低級到不至於 只是真的很誇張而已  office 2003版沒出現這個問題

            以後可能還是用2003版比較安心吧

開啟EXCEL自動執行巨集

這很簡單 只是一個小觀念而已 但是卻很方便 尤其是你要傳給不會使用VBA的人看的時候

他只需打開EXCEL檔案而已 就自動執行你所要執行的巨集

所以不要幹壞事啊  寫巨集病毒啊 XD  另外要提醒的是需將EXCEL的安全性調到低才可以自動執行巨集

所以還是有他的不方便性在  因此我順便教一下如何將EXCEL的安全性調低好了請到工具表單下選取巨集=>安全性 如下圖所示

 

 

接下來 只要選擇"低"後按確定便即可選擇"中"也可以

不過以後只要有巨集的檔案EXCEL都會問你要不要開啟巨集

 

 

一般來說 我建議大家選擇 "中"安全性  只是要注意的是

當你開啟有巨集的檔案時   你選擇"停用巨集" EXCEL就不會載入巨集的程式    只有 選擇"啟用巨集" 才會自動載入巨集

因此當你信任這個程式時    請不要按到"停用巨集"  不然你可能就會失去執行巨集的功能

如果妳不小心按錯到 "停用巨集" 但是你又後悔想執行該檔案巨集的功能時 你只要關掉檔案再重新開啟即可

( 這時EXCEL會再次詢問你要不要開啟巨集這項功能 這時你選"啟用巨集" 即可 )

 

 

最後如何寫自動執行的巨集 很簡單 只要在模組的名稱打上   “Auto_Open()”  如下圖藍色線上標誌所示

 

如何刪掉沒有用儲存空格

問題來自於PTT的網友 lame

問題的描述為需要把以下的沒有用空格給刪掉 

就是讓每一直直列的空格被刪掉 順序不變
由於有上萬個數據 無法一一刪  因此希望能用什麼方法能快速自動管理  如下圖所示

 

 

希望變成

 

 

同樣我利用巨集來寫  但是這次程式碼寫很差

我也懶得寫好它  因為還要想判斷式終止就覺得很麻煩 = =

反正只要能"堪用" 就行了嘛 呵呵呵….. 又不是寫論文

 

廢話不多說  我的程式碼 儲存格範圍設在 A1到T20 這個可以自行設定

迴圈次數設為400次 應該是越多越好 而且這樣寫其實也不是很好

應該是要寫一個判斷式讓迴圈終止 但是我懶得想就是了

有時間或有興致 我再會好好想的  我是懶鬼嘛 呵呵…

 

程式碼如下

 

Do
For x = 1 To 20
For y = 1 To 20
If Cells(x, y) = "" And Cells(x + 1, y) <> "" Then
Cells(x, y) = Cells(x + 1, y)
Cells(x + 1, y) = ""
End If
Next
Next
g = g + 1

Loop Until g = 400

 

範例下載 http://csuming.googlepages.com/VBAachievements.xls