文、菜鳥編

在Excel中依照數字的排序,使用「自動填滿」就可以滿足,相信一定難不倒你。但如果今天我們資料共有5,000筆,你覺得用下拉的方式適合?或者,今天需要分部門人員給予編號等特殊需求,用下拉填滿的方式就不見得是個明智的選擇。當儲存格大小不同時,更是無法使用下拉填滿的,相信有經驗的應該都有過這經驗,這次就來分享大家最常見的序號狀況解決方式。

 

 

 

前提:

 

如果你對填滿功能還不是很清楚的話,不妨可以先參考先前介紹的Excel教學:懂「填滿」,你能更有效率

 

 

 

一、生成大範圍數量的序號:

 

如前面提到的,當要產生1~5000的序號,總不能透過下拉填充方式慢慢的拖拉,這裡就需要透過「名稱方塊」來完成。

 

名稱方塊是什麼?就是在輸入方塊左方的區域。預設是顯示你當前儲存格的位置。

 

在名稱方塊中輸入「A1:A5000」,就可以快速的將A1至A5000的範圍選取。

 

 

接著在輸入方塊中打上「=ROW(A1)」,但可別急著按下Enter,這邊要用Ctrl+Enter方式,Ctrl+Enter主要用在連續或非連續的多個儲存格做一次性填滿。這樣一來,序號1~5000馬上就完成,是不是比用下拉填滿方式來的快呢?

 

 


 

 

二、特殊規則序號-按部門產生序號:

 

如下表,要在各部門前填入序號。

 

 

可以透過COUNTIF函數來產生對應的序號,如果對COUNTIF函數不熟悉悉的可以參考這一篇Excel中你知道那些判斷的函數嗎(下)

 

在A2中打上「=COUNTIF(B$2:B2,B2)」

 

 

將滑鼠移至A2儲存格右下角,出現「+」符號後,雙擊滑鼠就會向下將有資料的自動填滿。

 

 

完成效果如下

 

 


 

 

三、特殊規則序號-按部門人員產生序號:

 

假如上面範例的資料改成下方的樣式,需要在人員後方填入序號又該怎麼處理?當然,可以把合併儲存格取消後填入部門資料,就會與「按部門產生序號」相同,但如果今天資料很多,可能這樣的方式就不是最好的解決方式。

 

 

解決方式只要在C2中輸入「=IF(A2<>””,1,C1+1)」,這邊的<>”” 的意思指儲存格中的值不等於"",也就是,它們不是空的。以行銷部來說,A2會被偵測到非空值,所以判斷式會回傳「1」:接著C3部分偵測A3時,就會因空值而進行C2+1,所以儲存格的值就會變成「2」。

 

 


 

 

四、特殊規則序號-合併儲存格的序號產生:

 

一開頭有提到,當資料中有不等大小的儲存格時,是無法透過下拉填滿的方式自動產生序號。

 

 

解決方式一樣透過函數來完成,要使用的為MAX這個函數。MAX會傳回一組數值中的最大值,如果引數中不包含數字,就會傳回0。透過下面的動畫應該就很容易理解。

 

 

接著就來解決儲存格不同大小的問題,先將要填充序號的儲存格選取起來,在A2中輸入「=MAX(A$1:A1)+1」,按下Ctrl+Enter就可以完成。稍作解釋一下,在A2中我們會獲得MAX(A$1:A1)回傳的0,加1後A2儲存格就是1了。而A6就會獲得MAX(A$1:A5)回傳的1,計算後會獲得2,以此類推就會完成排序了。

 

 


 

 

五、篩選後維持連續的排序:

 

下面的動畫我們可以看到,當透過篩選過後,排序就會錯亂。

 

 

解決方式就是使用SUBTOTAL 函數,這個函數可能比較陌生,先解釋一下,以這邊的範例來說,可以把SUBTOTAL函數的公式想成「=SUBTOTAL(Function_num , 範圍)」,Function_num可以想成是要使用的參數。

 

以下表來說,傳入的參數代表使用的函數與是否要統計被隱藏的值。例如參數為102就是使用COUNT函數來統計範圍的值,但排除被隱藏的範圍。

Function_num 
(包括隱藏的值)

Function_num 
(忽略隱藏的值)

函數

1

101

AVERAGE

2

102

COUNT

3

103

COUNTA

4

104

MAX

5

105

MIN

6

106

PRODUCT

7

107

STDEV

8

108

STDEVP

9

109

SUM

 

 

使用範例來說明,下表儲存格A12為1到100的加總。

 

 

當把第5與第6行隱藏,可以看到計算並沒有改變。

 

 

如果把公式改成「= SUBTOTAL(109,A2:A11)」,可以看到隱藏的內容就不會被計算到。

 

 

所以這邊要解決篩選後續號不連續的話,我們要使用SUBTOTAL函數的參數為「103」使用COUNTA函數,選取範圍後,在A2輸入公式「=SUBTOTAL(103,B$2:B2)」,按下Ctrl+Enter。

 

 

搞懂這五種方式,相信序號產生的問題你都可以解決了,如果你覺得這篇文章很實用,也別忘了分享給你的親友們,相信對他們一定很有幫助!

 

►推薦課程:Excel財務試算表應用

 

 

 

 

延伸閱讀

聯成電腦分享:多學一招 創造更高的工作效率

鳥編Excel教學:懂「填滿」,你能更有效率

鳥編Excel教學:透過投票學資料重複處理

鳥編Excel應用:8種錯誤提示與解決方法

鳥編專欄:在Excel中的各種隱身術

 

 

官方網站:http://www.lccnet.com.tw
FB粉絲團:https://www.facebook.com/lccnetzone
菜鳥救星:https://www.facebook.com/greensn0w

 

arrow
arrow

    聯成電腦 發表在 痞客邦 留言(0) 人氣()