rows()和row()的區別以及row(a1)/row(1:1)/row(1:10)/row()-等的應用

05-28

這個疑問是看狗尾草版主貼子時遇到的,看貼前腦子還真是混亂,經整理清晰多了.

gouweicao78常見用數組公式取得多條記錄的開頭都是=if(row(1:1)>XXX,""……表示當公式下拉複製超過一定行數（滿足條件記錄的個數）時，顯示為空。書寫方式有row()-n、row(1:1)、rows($1:1)，前2個得到的是一個{}1行1列的數組，後一個得到的是一個單值，此外，如果未知數據行數要形成「多單元格數組公式」，則必須使用row()-n的形式；如果已確定要得到記錄的行數，則也可以用row(1:X)實現。比如：=IF(ROW(1:6)>COUNTIF(A1:A6,">0"),"",SMALL(IF(A1:A6>0,A1:A6),ROW(1:6)))——謝謝gvntw版主補充。

備註：n是公式所在第一行的上一行的行號。比如在A7輸入第一個公式，則n=6。

一般的Row(1:1)由於是相對引用，在公式下拖複製時變成Row(2:2)、Row(3:3)分別返回{2}、{3}等，用以結合SMALL函數將數組從小到大輸出。

column函數與row函數很相似，但由於工作表中最大行數是65536(4^8)而列數是256,所以在大多時候都用column(1:1),返回的是一個數組:{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256},這個數組一般應用中夠用了.而如果在單個單元格輸入的時候,只顯示1.

Apolloh更是一語中的:min(),max(),large(x,1),small(x,1),可將row()產生的單值數組轉換成常量。

Gdiyy的討論使我對這個概念的理解更加深入：

1、Rows()其結果返回行數－－單值，而Row()則返回行號－－單值或多值，所以Rows()返回是單值常量，而Row()則返回常量數組。

2、並不是Row(1:1)不出錯，只是你的例子看似比較特殊Rows(A1:A1)，僅僅引用了一個單元格，當你刪除1整行，Row(1:1)還是報錯的。這也從側面反映了－－在Excel中，當參數引用範圍全部丟失時，系統會自動識別為引用無效（#REF!）的。

3、對於運算速度，我覺得應該是Rows()快一些，這可能與Rows()本身的函數性質有關吧：）

Czzqb: 說說關於ROW(1:1)的看法，與樓主提的ROWS()無關

ROW(1:1)的最簡單的用法如下：

A1:A10里存放了10個數字，要在A11:A20里把它從小到大排列出來，有以下幾個寫法（自定義名稱DATA1=$A$1:$A$10)：

1, SMALL(DATA1,ROW(A1)),下拉

2, SMALL(DATA1,ROW(1:1)),下拉

3, SMALL(DATA1,ROW(INDIRECT("A"&ROW()-10))),下拉

4, 多單元格數組公式：SMALL(DATA1,ROW(INDIRECT("1:10")))

5, 多單元格數組公式：SMALL(DATA1,ROW(DATA1))

哪個好用？

公式1和2,3的計算速度應該比4和5要慢，因為它有10個公式，要計算10次，而後兩個公式只計算一次，就填入10個單元格；另外，更致命的是，一旦刪除數據區的某一行，將造成顯示結果錯誤。

公式4與5，刪除數據區不會造成錯誤，速度塊。

而公式4與5相比：公式4使用了易失函數，會引發大量的重算，速度也受到很大影響，而公式5則沒有這個問題。

我的總結：多單元格數組公式：SMALL(DATA1,ROW(DATA1))在此例是最好的。其他的地方恐怕要受到限制,因為ROW()得到的只是DATA1區域的行數，要想得到更多的行數，還是要用第4個公式。要用多單元格數組公式時，=rows($1:1)和=row(1:1)都只能返回一系列的1，只有用=row()-公式上一行的行號

才能返回1、2、3……這個系列。（補充，還有已知行數x，用row(1:x)也可以。）

Rows(ref)/Columns(ref)函數傳回的是ref所表示的單元格區域或數組所包含的行數或列數，為基本數據類型，比較簡單，運算速度較快。

Row([ref])/Column([ref])函數傳回的是ref所表示的單元格區域所包含的行號和列好的集合，為數字數組類型，適合數組應用;同時，因為Excel在非數組運算過程中會自動將數組名稱替換為數組中的第一個數字使用，所以亦可以在省略部分數據的情況下作為基本數據類型使用。因為在產生數組的過程中的計算量和保存數組所消耗的內存量較大，所以運算速度較慢。