excel數(shù)據(jù)存放的位置：單元格和內(nèi)存

如果數(shù)據(jù)存放再單元格中，系統(tǒng)計算需要兩步：1，提取數(shù)據(jù)，加載到內(nèi)存中；2、運算內(nèi)存中的數(shù)據(jù)。而數(shù)據(jù)如直接在內(nèi)存中，則可以直接進行運算，從而節(jié)省大量的時間，注意：是大量的時間。

數(shù)組：可以將一組數(shù)據(jù)存儲在內(nèi)存中，可以存放文本、數(shù)值、對象等。

VBA數(shù)組是以變量形式存放的一個空間。

數(shù)組分類：常量數(shù)組、靜態(tài)數(shù)組和動態(tài)數(shù)組

常量數(shù)組：數(shù)組空間大小和元素均已確定

靜態(tài)數(shù)組：數(shù)組空間大小已確定，但數(shù)組中的元素尚未確定

動態(tài)數(shù)組：數(shù)組的空間和元素都未確定

1、數(shù)組寫入數(shù)據(jù)

1.1、一維數(shù)組寫入數(shù)據(jù)

注意

arr(10) 的取值范圍是[0,10]，一共11個數(shù)字，如果要指定從1到10，則指定具體的取值范圍：arr(1 To 10)

一維數(shù)組即單行單列結(jié)構(gòu)

1.2、二維數(shù)組寫入數(shù)據(jù)

Sub test()Dim arr(1 To 5, 1 To 3), x!, y!, i!For x = 1 To 5 For y = 1 To 3 arr(x, y) = i i = i + 1 Next yNext xEnd Sub

說明

二維數(shù)組即多行多列組成的區(qū)域

三維數(shù)組是工作表和區(qū)域共同組成的結(jié)構(gòu)，如arr(工作表，行，列）

2、動態(tài)數(shù)組

動態(tài)數(shù)組使用需要兩步

第一步：聲明數(shù)組變量,不指定空間大小

[代碼塊]

第二步：ReDim 數(shù)組變量名（數(shù)組空間大小）

3、數(shù)組的批量寫入

3.1、使用Array函數(shù)寫入

3.2、單元格區(qū)域?qū)懭?/p>

結(jié)果：arr是一個由B3:F5值組成的二維數(shù)組，如果由空單元格，值為空

Sub test()Dim arrarr = [b3:f5]End Sub

結(jié)果：arr是一個由B3:F5值組成的二維數(shù)組，如果由空單元格，值為空

注意

如果Dim arr聲明了一個數(shù)組，則此數(shù)組為二維數(shù)組，即使arr=[a1:a10]，該數(shù)組也是一個10行1列的二維數(shù)組

4、將數(shù)組寫入到單元格

4.1、二維數(shù)組寫入

4.2、一維數(shù)組寫入

Sub test()Dim arr, arr1(1 To 5)Dim x!arr = Range('b2:c6')For x = 1 To 5 arr1(x) = arr(x, 1) * arr(x, 2)Next xRange('d2').Resize(5) = Application.WorksheetFunction.Transpose(arr1)End Sub

說明

一維數(shù)組默認(rèn)是行結(jié)構(gòu)，如果需要列結(jié)構(gòu)，需要使用Transpose函數(shù)

4.3、數(shù)組部分寫入

說明

1、數(shù)組的有效數(shù)據(jù)不能少于存儲單元格數(shù)量，如上例中，數(shù)組大小為50個，有效數(shù)據(jù)5個，所需要存儲的單元格為5個，則可正常存入。

2、arr = range()，只會講range的value屬性賦值給數(shù)組arr，如果需要裝載range對象本身，需使用set arr = range()語句。

5、數(shù)組的排序

數(shù)組使用編號排序的，可以通過Lbound方法和Ubound方法獲取數(shù)組的上下限編號

Lbound(數(shù)組)可以獲取數(shù)組的最小編號(下標(biāo))

Ubound(數(shù)組)可以獲取數(shù)組的最大編號(上標(biāo))

Ubound(數(shù)組,1)獲得二維數(shù)組的行上標(biāo)

Ubound(數(shù)組,2)獲得二維數(shù)組的列上標(biāo)

Lbound反之

示例

Sub test()Dim arrDim x!arr = Range('a2:d5')For x = 1 to Ubound(arr,1)?Next xEnd Sub

6、動態(tài)數(shù)組的動態(tài)擴充

使用以下語句來擴充原先的數(shù)組，并且保留原來的數(shù)據(jù)

ReDim Preserve arr()

ReDim Preserve改變最末維的上界，且不能改變數(shù)組維數(shù)。如果改變下標(biāo)，則出錯。

一維數(shù)組動態(tài)擴充辦法：直接擴充

多維數(shù)組動態(tài)擴充辦法：逐個維度擴充，中間使用轉(zhuǎn)置函數(shù)轉(zhuǎn)換維度。

技巧

如果可以估算出動態(tài)數(shù)組的上界，則可以設(shè)定一個上界很大的數(shù)組，然后正常操作，最后截取有效數(shù)據(jù)部分。

7、強制聲明數(shù)組下標(biāo)

數(shù)組的下標(biāo)除了在生命時規(guī)定下界（如dim arr(1 to 5)）之外，可以使用強制生命來改變數(shù)組下界不以0開始

Option Base 數(shù)字

示例

8、循環(huán)速度

在循環(huán)數(shù)組時，使用for each循環(huán)結(jié)構(gòu)比for next結(jié)構(gòu)要快，但不足之處是for each不支持修改數(shù)組的元素（即修改語句無效）

示例

for each無法修改數(shù)組元素

Sub test()Dim arr, elementarr = [a1:b12]For Each element In arr element = element - 1Next elementEnd Sub

結(jié)果：a1:b12單元格值不變。

注意

無論數(shù)組是什么數(shù)據(jù)類型，for each的循環(huán)變量必須是Variant類型。

9、清空數(shù)組

Erase 方法

清空數(shù)組

語法

Erase 數(shù)組名稱

10、函數(shù)與數(shù)組

10.1、可以使用VBA函數(shù)計算數(shù)組,如VBA.Filter函數(shù)

10.2、可以使用工作表函數(shù)計算數(shù)組，如Application.Max(arr1)

11、數(shù)組與單元格格式

取得單元格的地址，然后放入到Range中，然后設(shè)置Range的單元格屬性

其中注意Range中的字符長度<=255。

技巧

因Range有255個字符限制，所以在獲得單元格地址時，可以只獲得單元格的行號（同一個Ragne可以放置更多的結(jié)果，提高運行速度），然后使用Application.Intersect()方法獲取行列的交集，從而定位到目標(biāo)單元格。

12、排序算法

12.1、冒泡排序

每一個數(shù)字與其位置之后的所有數(shù)字進行比較，如大于后面的數(shù)字，位置不變，如小于后面的數(shù)字，雙方互換。

示例

12.2、選擇排序

從后往前循環(huán)，每一個數(shù)字與位置之前的最大值進行比較互換。

選擇排序比冒泡排序速度快

示例

Sub 選擇排序()Dim arr,temp,x,y,iMax,k,k1,k2arr = Range('a1:a10)For x = Ubound(arr) To 1+1 Step -1 iMax = 1 For y = 1 To x If arr(y,1) > arr(iMax,1) Then iMax = y Next y temp = arr(iMax,1) arr(iMax,1) = arr(x,1) arr(x,1) = tempNext xEnd Sub

13.3、插入排序

從數(shù)組的第2個元素開始往后循環(huán)，每一個數(shù)字與它之前的數(shù)字比較，如果位置之前的數(shù)字大于其，則將該數(shù)字與其互換，放置其后，否則，位置不變。

13.4、希爾排序

引用“

希爾排序?qū)?未排序數(shù)據(jù)的順序 不敏感，雖然平均而言速度比快速排序慢一倍左右

但是卻安全很多，沒有快速排序的堆棧溢出問題，沒有退化為O(n^2)的問題

而且對比較有序的輸入數(shù)據(jù)，重復(fù)度高的數(shù)據(jù)，速度遠(yuǎn)遠(yuǎn)超過快速排序。

所以還是一個很不錯的排序算法，尤其是還可以跟快速排序結(jié)合，大大降低快速排序的遞歸次數(shù)。

希爾排序?qū)崿F(xiàn)起來比也非常簡單。就是多重循環(huán)比較不好看而已。

附件的希爾排序已經(jīng)做成一個子程序，在任何程序直接調(diào)用即可。

希爾排序的速度關(guān)鍵是：選擇合適的h序列，也就是每次循環(huán)跳過多少個數(shù)據(jù)來交換

再啰嗦一下h序列的問題，假設(shè)已經(jīng)知道希爾排序使用的h序列是怎么回事

那么比較常用的，已經(jīng)經(jīng)過計算機界的大師驗證，絕對慢不到哪里去的h序列，有以下幾種

1 h1=1, 然后往后每個h都等于 3h+1

2 h1=1, 然后往后每個h都等于 2.25h+1，取整

3 這個比較復(fù)雜, h1當(dāng)然也是1，h2往后則用公式計算出來，假如

[n]是單數(shù),那么 h[n] =82^n-62^((n+1)/2)+1

[n]是雙數(shù),那么 h[n] =92^n-92^(n/2)+1

也就是說 h序列 = {1,,5,19,41,109,209,505,929,........}

這一個序列已經(jīng)被數(shù)學(xué)證明為O(n^1.25)的增長階，證明過程則估計我完全看不懂。。。。。。

4 我自己的發(fā)現(xiàn)，由于h序列一般來說，前后兩個h是互質(zhì)的會比較好，而我又發(fā)現(xiàn)3號序列并不完全是質(zhì)數(shù)

于是就把所有的非質(zhì)數(shù)換為最接近的質(zhì)數(shù)，結(jié)果發(fā)現(xiàn)又快了3%左右，yeah！ 這也是我的附件代碼最終采用的h序列

論壇以前發(fā)過的希爾排序貼，貌似都是用 h[n] = 2^n - 1，或者干脆就是 h[n] = 2^n

前者貌似也是O(n^1.25)，但是明顯比前面四個序列慢一大截

后者則是算法發(fā)明人希爾最早提出的序列，但是很快就被證明是個非常壞的h序列，速度慢而且很可能會退化到O(n^2)，所以不宜采用。

*************以下討論堆排序

堆排序的速度，一般比希爾排序慢10%到20%

基本的堆排序，速度很慢，原因是每次交換最大的元素到數(shù)組末尾，這時候數(shù)組末尾的元素將交換到堆頂

再次把這個元素放進堆里面，就是把一個很小的元素壓進堆，比較的次數(shù)將會很多，幾乎必定是log(n)

所以采用Floyd的算法，先把這個很小的堆頂元素壓到最底層，然后再上升到合適位置，這樣就可以加快一倍

附件是四個堆排序算法：分別是最基本的堆排序，三元分叉的堆排序，最基本堆排序＋先沉底再上升（最佳版本），三元的堆排序+先沉底再上升

可以看出，基本三元堆比基本兩元堆快，而且先沉底再上升的做法，對三元堆，兩元堆都有效

而令人驚奇的是：兩元堆先沉底再上升，比三元堆先沉底再上升更快，可能是三元堆提升三個葉子的最大元素，其比較次數(shù)太多

這倒是件好事，因為兩元堆的代碼比較簡單，而且速度更快，好極了。

堆排序最大特點：可以迅速取出最大值（或者最小值），如果是求100萬元素的前1萬個，那么堆排序肯定是最快的。

因為只需要對1萬個元素進行堆處理，而其它排序方法需要對100萬個元素排序。

嗯。。。。。。也許快速排序也可以縮小排序的范圍，如超過1萬的部分就不再遞歸排序，也許還有可能比堆排序快。

*************以下討論快速排序

作為一般而言最高速的排序算法，快速排序既讓人高興，又讓人不放心

高興的是：速度真的快多了

不放心的是：碰到某些序列，排序時間會惡化到無法接受的O(n^2)

附件是六個版本的快速排序，基本上包括了對快速排序進行優(yōu)化的各種技巧

作為對比，第一個最慢速最不可靠的版本是從論壇帖“七種排序算法”抄過來的

其余五個版本是我自己的作品

簡而言之，快速排序的弊端是

1 遞歸次數(shù)太多，會導(dǎo)致堆棧空間溢出。

解決辦法：對小于一定數(shù)量的元素進行直接插入排序，希爾排序，而不是留給下一層的遞歸快速排序

2 每次劃分的樞紐值選取不當(dāng)，就會大大影響速度

解決辦法：用平均值，隨機值等等方式來決定樞紐值，但是無法保證一定不會出現(xiàn)速度退化的現(xiàn)象

進一步解決方法：先用快速排序，然后如果超過一定時間，或者遞歸一定次數(shù)后還沒完成，就完全轉(zhuǎn)為希爾排序。

綜合而論，我個人覺得還是希爾排序最可靠，堆排序由于每次都取出最大值的特點，有些時候是非它不可！

快速排序單純用原始版本，是一個又慢又危險的算法，完全沒有任何價值

改良以后速度極好，但不能完全放心，輔以希爾排序才是王道。

補充漢字的排序方式：

Excel本身的sort方法，有兩種漢字排序方式

名稱 值 描述

xlPinYin 1 按字符的漢語拼音順序排序。這是默認(rèn)值。

xlStroke 2 按每個字符的筆劃數(shù)排序。

VBA里面，字符串比大小也有兩種方式

option compare binary 對應(yīng)按筆畫數(shù)排序（默認(rèn)值）

option compare text 對應(yīng)按拼音排序，速度大概慢一倍吧