因為需要計算不重複項目, 發現大家的解決方案都是在 Excel 中使用陣列公式, 所以稍微瞭解了一下陣列的用法, 果然是神兵力器, 值得學一下。
Excel 的陣列
如果你有以下的資料, 想要算出小計：
一般的作法可能是在 B4 建立公式 =B2*B3, 然後再複製公式到 C4 與 D4；有了這些資料後, 我們就可以計算總價, 也就是所有小計的總和。
我們可以改用陣列公式取代上述運算, 在大多數情況下能夠節省很多操作時間。
使用陣列計算出選取範圍內的個別結果
以下列步驟完成同樣的結果：
-
選取小計範圍 B4:D4:
-
在公式欄位填入
=B2:D2*B3:D4後先不要急著按下 Enter
-
接著按 Ctrl+Shift+Enter, 你會看到小計欄位就已經填入我們所需要的結果了：
另外你也可以注意到我們剛剛填入的公式自動加上了一對大括號, 這表示這個公式是以陣列的形式運作。
陣列運作會針對範圍內的儲存格一個一個去運算, 像是剛剛的公式 =B2:D2*B3:D4 就是把 B2:D2 範圍內的儲存格與 B3:D4 範圍內的處存格兩兩對應相乘後放到我們選取的範圍內, 也就相當於：
B4 = B2 * B4
C4 = C2 * C4
D4 = D2 * D4
使用陣列匯總範圍內的資料
除了剛剛這樣用範圍內的儲存格一一計算出個別結果外, 你也可以用陣列公式匯總計算。例如要計算上述資料的總價, 我們就可以這樣做：
-
首先在 B5 儲存格輸入公式
=SUM(B2:D2*B3:D4)加上加總函數, 一樣先不要急著按 Enter：
-
接著按 Ctrl+Shift+Enter, 你會看到總計欄位已經算出我們所需要的結果了：
同樣的, 我們輸入的公式也自動加上了一對大括號, 表示這是陣列公式。這個公式除了依照兩個範圍兩兩對應相乘外, 還會運用 SUM 公式將計算結果全部加總。
在剛剛的作法裡, 你可以看到我們其實不需要小計欄也可以直接算出總價, 而且還可以將這個公式直接套用在別的公式裡面。
使用陣列計算不重複項目的個數
瞭解陣列的基本用法後, 我們就可以來思考一下如何使用陣列來幫我們計算一串資料中不重複項目的個數了。假設我們有以下清單：
這裡會使用一個小技巧來計算不重複項目的個數, 我們邊做邊說明。首先計算個別項目在資料中出現的次數, 這可以透過 COUNTIF 及陣列達成：
-
先選取
B1:B6後輸入公式=COUNTIF(A1:A6, A1:A6)：
-
按下 Ctrl+Shift+Enter, 就會看到個別項目出現的次數出現了：
這裡 COUNTIF 要計數的範圍是 A1:A6, 而用來判斷是否計入計數的條件並不是單一儲存格, 而是一個範圍, 由於這是陣列公式, 因此這就等於是將該範圍內的儲存格一一帶入成為 COUNTIF 的判斷條件, 並將個別的計數結果一一放入我們選取的 B1:B6 內。也就是：
B1 = COUNTIF(A1:A6, A1)
B2 = COUNTIF(A1:A6, A2)
B3 = COUNTIF(A1:A6, A3)
B4 = COUNTIF(A1:A6, A4)
B5 = COUNTIF(A1:A6, A5)
B6 = COUNTIF(A1:A6, A6)
接著我們再計算上述出現次數的倒數, 這只在 C1:C6 範圍內輸入陣列公式 =1/COUNTIF(A1:A6, A1:A6) 就可以完成：
這裡就可以看出竅門了, C 欄是 A 欄對應資料出現次數的倒數。以橘子為例, 因為它出現 2 次, 所以在 C1 和 C6 都是 2 的倒數, 也就是 1/2。如果我們將 C 欄加總, 橘子的 2 個 1/2 相加會變成 1, 所以橘子雖然在資料中重複出現, 但加總後只會是 1。利用這個作法, 如果某個項目在資料中重複出現 N 次, 那麼加總的結果就是 N * 1/N, 也就是 1, 因此就可以計算清單中不重複項目的個數了。
有了以上的認知, 大家應該都可以猜到該怎麼算出不重複的項目的個數了, 我們在 D4 輸入陣列公式 =SUM(1/COUNTIF(A1:A6, A1:A6))：
就可以看到不重複項目有 4 個, 就是橘子、頻過、鳳梨、香蕉。你還可以再延伸發揮, 想看看如果清單中有空白項目時, 要怎麼處理？
請特別留意, 在 Office 365 中若輸入公式含有陣列, 也就是原本應該是單一儲存格的參數卻輸入了範圍, 會自動變成陣列公式, 但若不是 Office 365, 就一定要按 Ctrl+Shift+Enter 才會變成陣列公式。
Latest comments (0)