SORT 與 UNIQUE 實戰：從訂單流水帳做出會員清單與消費排行

店裡每天會記下一筆一筆的訂單，累積了兩週，老闆問我：這段時間有幾位會員下過單？買最多的前三名是誰？

這份流水帳一個會員會出現好幾次，要先去重、再計算、最後排序。SORT 和 UNIQUE 的基本寫法在 SORT 與 UNIQUE 入門 講過了，這篇直接拿它們組出一張排行榜。

原始流水帳與想要的成品

訂單放在 A1:D16，共 15 筆，會員用編號表示：

我想從這份資料生出四樣東西：不重複的會員名單、每個品項被點了幾次、會員的消費排行，還有排行的前三名。結果全部放在 F 欄以右的空白區域，原始資料不動，所以每一個公式都不會改到 A 到 D 欄。

先列出不重複的會員

手算的話，要從上往下看 B 欄，看到沒見過的編號就抄一次，15 筆還撐得住，每天多幾十筆就不想做了。

• UNIQUE(B2:B16)：先把 B 欄去重。
• 1：去重後只有一欄，所以依第 1 欄排序。
• TRUE：遞增。

在 F2 輸入，F1 另外手打標題「會員」。來看看結果：

一共 6 位，順序是編號由小到大。

品項被點了幾次

我想知道每個品項在流水帳出現幾次。UNIQUE 負責列出品項，COUNTIF 負責數次數，COUNTIF 的用法在 SUMIF 與 COUNTIF 入門 有講。

先拆成兩步看。F2 放 UNIQUE：

它會往下展開 4 格，F2:F5。G2 放 COUNTIF，條件一次給它整塊 F2:F5：

COUNTIF 的條件通常只吃一格，給它一整塊就得靠 ARRAYFORMULA 包起來，才會一格一格都算。來看看結果：

5 + 5 + 2 + 3 = 15，和流水帳的筆數對得上。

不過 F2:F5 是我手動框的，品項多一種就要回來改。所以我會直接讓 UNIQUE 自己出現在 COUNTIF 裡面，再用大括號把兩欄併成一塊，交給 SORT：

• {A, B}：大括號裡用逗號把兩塊資料左右併起來，兩塊要一樣高，這邊都是 4 列。
• 2, FALSE：第一優先，依第 2 欄，也就是筆數，由大到小。
• 1, TRUE：第二優先，筆數一樣的話，依第 1 欄，也就是品項，遞增。

這條是 Google 試算表的寫法，貼進 Excel 365／2024 會出錯：Excel 的大括號陣列常數放不進範圍或函式結果，併欄要用 HSTACK，多欄排序要用 SORTBY，不是 SORT 後面一組一組往下接。

來看看結果，F1:G1 手打標題：

烏龍奶茶和蘋果紅茶都是 5 筆，所以看第二組排序，品項照筆畫由少到多，「烏」比「蘋」少，烏龍奶茶排前面。

會員消費排行

接著做主菜。每位會員要三樣東西：會員編號、下了幾筆單、買了幾杯。筆數用 COUNTIF，杯數用 SUMIF，一樣各包一層 ARRAYFORMULA：

• 大括號裡三塊資料併成三欄：會員、筆數、總杯數。
• 3, FALSE：第一優先，依第 3 欄，總杯數，由大到小。
• 2, FALSE：第二優先，總杯數一樣的話，筆數多的在前。

放在 F2，F1:H1 手打標題。來看看結果：

M045 和 M033 的總杯數都是 35，M045 下了 3 筆、M033 下了 2 筆，所以 M045 排在前面。第二組排序欄就是為了這種情況而寫的。總杯數加起來也對得上：67 + 58 + 35 + 35 + 13 + 4 = 212，和流水帳 15 筆杯數的加總一樣。

用 SORTN 只取前 3 名

老闆只想看前三名。SORTN 是排序之後只留前 n 列：

=SORTN(範圍, [n], [顯示並列模式], [排序欄], [遞增], [排序欄2, 遞增2, ...])

並列的時候要留幾列，有四種模式可以選，這篇先用預設的 0，就是剛好留 n 列，四種模式的差別在 進階陷阱 有整理。排序的參數跟 SORT 一樣，是成對往後接的。Excel 沒有 SORTN，365／2024 可以用 TAKE(SORT(…), 3) 取前 3 列（Excel 2021 沒有 TAKE）。

我直接引用上一步溢出的範圍 F2:H7，放在 J2：

• F2:H7：上一步排好的會員排行，不含標題。
• 3：只留 3 列。
• 0：並列模式用預設。
• 3, FALSE, 2, FALSE：和上一個公式一樣的兩層排序。

J1:L1 手打標題，來看看結果：

這邊 F2:H7 本身已經排好了，我還是把排序欄寫上去，這樣之後上一步的 SORT 換了寫法，這條的順序也不會跟著變。

只下過一次單的會員

UNIQUE 的第三個參數填 TRUE，只會留下只出現過一次的值。我想找「只下過一次單」的會員，通常是想給他們發一張回購券：

來看看結果：

流水帳裡只有 M052 一筆，其他五位都下過兩筆以上。

資料每天新增怎麼辦

上面的範圍都停在第 16 列。明天在第 17 列往下貼新訂單，公式不會自動把第 17 列算進去，範圍要改，我是不想天天改。

可以把範圍寫成 B2:B、D2:D 這種整欄的寫法，不過整欄範圍會把下面的空白列也帶進去，所以要先用 FILTER 把空白濾掉。排行榜的公式就變成這樣，順便用 LET 先把去重後的會員存成 m，不必寫三次：

資料還是這 15 筆的時候，結果和上一節的排行表一樣。之後新增訂單，只要接在流水帳的最下面往下貼，排行榜就會跟著更新。

要小心的是排行榜下面和右邊要留空，新會員一多，結果往下展開時如果碰到別的資料，會顯示 #REF!。還有一點，排行榜不能放在被公式引用的 B 欄或 D 欄裡，不然 Sheets 會提示「系統偵測到循環相依性」，結果同樣是 #REF!，把公式搬到資料旁邊其他欄（例如 F 欄以右）就好。

做法比較

這張排行榜也可以用樞紐分析表做，兩邊各有適合的地方：

• 公式版：排序規則可以寫死兩層，取前三名也能直接接 SORTN，成品放在報表頁，每天自己更新。
• 樞紐分析表：點幾下就做完，想換個維度看，例如改成按品項彙總，拖欄位就好，詳細可以看 樞紐分析表入門。

我個人是這樣分的：要給別人看的固定報表用公式，自己臨時想看一眼用樞紐分析表。

練習

寫好之後可以自己對一下：四個品項的總杯數加起來，應該和流水帳的 212 杯一樣。

如果想先挑出某幾間門市或某段日期，再做排行，可以接著看 FILTER 實戰，把 FILTER 包在 UNIQUE 裡面就好。

我是喜特先生，Mr. Sheet，我們下個教學見！