訂午餐/訂飲料/團購訂單 使用excel製作 - 3 統計項目唯一值/去除重複值 使用陣列公式

上篇文章統計了商品項目,但是中間會有空白的欄位,不太符合一般視覺感官,接下來我們就要將空白的部分移除掉。

作法是將抓出來的商品項目移除掉空白值,重新排序商品項目,由於要一次計算多個項目,所以我們這邊會需要用到陣列公式。

一、陣列公式簡單介紹

案例一:一次計算多個儲存格,回傳一個值

多個值相乘後再加總(A欄 * B欄)後加總,公式為下圖:


計算結果:


如果改為陣列公式計算只需要一行公式就可以解決,只佔用一格儲存格空間,省空間也美觀。
公式改為:
{=SUM(A2:A4*B2:B4)}
注意:A欄的格數要與B來的格數一致
{}為陣列公式符號,不能直接輸入{},要使用 ctrl + shift + enter 輸入
步驟:
在儲存格內輸入 =SUM(A2:A4*B2:B4) ,按 ctrl + shift + enter 完成儲存格輸入

計算結果:

案例二:一次計算多個儲存格回傳多個值

判斷數值是否大於50
公式為:
{=A2:A11>50}

{}為陣列公式符號,不能直接輸入{},要使用 ctrl + shift + enter 輸入
步驟:
框選C2:C11,接著輸入公式 =A2:A11>50 ,按 ctrl + shift + enter 完成儲存格輸入


二、所需公式介紹

  • OFFSET(reference, rows, cols, [height], [width])
  • INDEX(array, row_num, [column_num])
  • SMALL(array, k)

• OFFSET(reference, rows, cols, [height], [width])

描述:傳回指定列數及欄數之儲存格或儲存格範圍
reference - 參考位置
rows, cols - 以參考位置為基準的欄列位移量
[height], [width] - 回傳儲存格範圍的寬高

範例:回傳A3到A5儲存格範圍
公式:
=OFFSET($A$3,0,0,3,1)
以A3為基準,欄列位移量為0,高是3寬是1,因為A3到A5有三格。


• INDEX(array, row_num, [column_num])

描述:返回資料表或陣列中由列和欄號索引選取的元素值。

array - 儲存格範圍
row_num - 選取陣列中的列
[column_num] - 選取陣列中的資料行

如果使用陣列公式可以回傳多個值

將C4到C6選起來,輸入以下公式,按 ctrl + shift + enter 完成儲存格輸入
=INDEX(A2:A11,ROW(A2:A4))
ROW(A2:A4) 回傳行數,2,3,4



• SMALL(array, k)

描述:傳回資料集中第 K 個最小值。
array - 儲存格範圍
k - 由小到大排序後,顯示第幾個值



如果使用陣列公式可以回傳多個值

將C4到C6選起來,輸入以下公式,按 ctrl + shift + enter 完成儲存格輸入
=SMALL(A2:A11,ROW(2:4))
ROW(2:4) 回傳行數,2,3,4,與ROW(A2:A4)結果一樣


三、取得項目的唯一值

當A2:A11數值為:

不重複的唯一值會是:

將I2到I5選起來,輸入以下公式,按 ctrl + shift + enter 完成儲存格輸入
=INDEX(A:A,SMALL(IF(COUNTIF(OFFSET($A$2,0,0,ROW($A$2:$A$11)-1),$A$2:$A$11)=1,ROW($A$2:$A$11),""),ROW($A$2:$A$11)-1))


1.一步一步拆解公式結果

拆解結果



拆解結果的公式

由於是陣列公式,所以每行的公式都會長得一樣



2.將 #NUM! 顯示為空白

在出現計算錯誤的公式最外層加上以下公式:
IFERROR(計算錯誤的公式,"")

所以取得唯一值的公式襪為加上 IFERROR變為以下樣子,記得要弄成陣列公式:
=IFERROR(INDEX(A:A,SMALL(IF(COUNTIF(OFFSET($A$2,0,0,ROW($A$2:$A$11)-1),$A$2:$A$11)=1,ROW($A$2:$A$11),""),ROW($A$2:$A$11)-1)),"")

留言

這個網誌中的熱門文章

C# 模擬鍵盤滑鼠控制電腦

python pyautogui 簡介

android 定時通知(永久長期的) 本篇只講AlarmManager使用

raspberrypi 開機自動執行程式 與 在terminal開啟第二個terminal執行python

python nn 聲音辨識 -1 傅立葉轉換