【進階 Excel】如何為音樂廳座號排序？

前言

為音樂廳座號排序，會遇到一個問題：如何為英文行號加數字組成的座號排序，才不是亂成一遍？

以上的影片，可以看到用 Excel 排序的問題。假設你有以下座號：

A1, A2, A10, A11, B1, B2, B10

作為人類，你會先排 A 行 1 至 11 號，然後 B 行 1 至 10 號，如此類推。但是，經 Excel 排序，你的答案會是：

A1, A10, A11, A2, B1, B10, B2

原因是，Excel 排的次序，是根據字元來排的。它排了A1，之後就是 A10，因為頭兩個位元都是「A1」，之後才到「A2」，之後才到「B1」。

於是，如果你的資料如上：

A1 A2 A4 A6 A10 A11 A12 A19 A99 A100 A101 B1 B2 B3 B10 B11 B24 B99 B100 B101 B114 C1 C2 C8 C9 C10 C11 C20 C21 C22 AA1 AA2 AA5 AA9 AA10 AA11 AA18 AA21 AA28 AA29 AA30 AA99 AA100 AA101 AA103 BB1 BB2 BB11 BB14

這就是由 A 行順序排到 Z，之後就是 AA 到 ZZ（現實大概到 MM 吧），然後排數字由 1 到 999（現實大概到 140 吧）。但結果，你在 Excel 排的話，就會是：

A1 A10 A100 A101 A11 A12 A19 A2 A4 A6 A99 AA1 AA10 AA100 AA101 AA103 AA11 AA18 AA2 AA21 AA28 AA29 AA30 AA5 AA9 AA99 B1 B10 B100 B101 B11 B114 B2 B24 B3 B99 BB1 BB11 BB14 BB2 C1 C10 C11 C2 C20 C21 C22 C8 C9

下載示範檔案

設置：分拆行號與座號

人眼看落去就懂的座號，在試算表中，是需要稍為分析的。因為，以英文字的行號，有可能是一個或兩個字母，數字有一個至三個，所以你不能只讀「左邊第一個字」為行號（例如 AA10 的第一個字，只是「A」），也不能只讀「右邊三個字」為座號（例如 A10 的右三個字是「A10」）。

所以你不能單用 =LEFT() 或 =RIGHT()。

那怎麼辦呢？

你必需要教 Excel 將字母與數字分開。

分析行號的程式，可以用這個：

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2 & “0123456789”))

這個程式來自這裏，是個很聰明的做法。簡而言之，它找出座號中第一個數字的座標，然後傳回該座標。例如，「A2」座位的第一個數字，在座標「2」；而「BB12」的則位於「3」。

有這個座標，你就可以用 =LEFT() 來控制從左面抽取多少個位元作為行號。

=LEFT(A2,B2–1)

以上，資料格「B2」就是 =FIND() 程式傳回的座標。

有了行號，座號則很容易了：

=RIGHT(A2,LEN(A2)-B2+1)

設置：行號字元

由於行號是由 A 到 Z 才到 AA 到 ZZ，所以這只需要知道行號的字元數，就可以排序。

=LEN(C2)

資料格「C2」就是行號。那麼，你在排序時，先排字元數，後排行號，就可達至以上效果。

排序

經過分析，你有三欄資料：

行號
行號字元數
座號

你只需依以上的資料順序排就可以了：

行號字元數 > 行號 > 座號

SQL 版本

思考這個問題，是因為我花了好些時間沉迷在這個問題，要將這個排序以一條 SQL statement 的形式實現。原因是，我手上的工作，正是在 MySQL 中處理大量的座位資料。（我不想在 application 的層面處理，雖然對我而言較為快捷，但犧牲了速度）

原來，要使用 MySQL 的層面，處理這個問題一點也不容易。花了很多時間找「Natural Sorting」的方法，沒有一個方案能完全滿足我的需要。

於是，最終想到的方法，就是用 CASE。

SELECT  
CASE  
WHEN seat REGEXP ‘^[A-Z]{2}’  
THEN SUBSTRING(seat,1,2)  
WHEN seat REGEXP ‘^[A-Z]{1}’  
THEN SUBSTRING(seat,1,1)  
END AS row_number,  
CASE 
WHEN seat REGEXP ‘^[A-Z]{2}’ THEN 2  
WHEN seat REGEXP ‘^[A-Z]{1}’ THEN 1  
END AS row_len,  
CASE 
WHEN seat REGEXP ‘[0–9]{3}$’ THEN 3  
WHEN seat REGEXP ‘[0–9]{2}$’ THEN 2 
WHEN seat REGEXP ‘[0–9]{1}$’ THEN 1  
END AS seat_len, seat 
FROM seats ORDER BY row_len, row_number, seat_len, seat

本質上是先建立行號、行號字元和座號三個 CASE，用 Regular Expression 去分辨字母與數字出現的位置與長度，然後將這些結果排序。以上的是簡化的版本，在 production 上，由於還有 table join，所以本來的 statement 相當長。

後來，想到這個 Excel 其實也用得着，於是將它變成 Excel 的版本。