有時,我們需要一些自動化流程,來滿足資料整理與計算的需求。
這時,石墨的陣列計算功能就可以派上用場。相比於普通一函式返回一結果,資料函式可以一函式返回多結果,大大擴充了函式用法。
比如,如果我們想對一片資料進行去重,並且源資料修改後,去重結果也自動更新,就可以使用去重函式。
常用的陣列函式可以滿足自動排序、自動篩選、自動去重、自動排序、自動篩選、自動查詢等常見需求。
去重函式:UNIQUE
有時我們需要對收集到的資料去重,這裡可以使用去重函式 UNIQUE。
公式:UNIQUE(範圍)
示例:UNIQUE(A2:B20)
說明:返回源範圍中具有唯一性的行,剔除重複行。這些行按其在源範圍中首次出現的順序返回。
比如上表中,標記行為重複行,該行中每一列資料均相同,我們需要將類似的重複行去掉。
使用時,先選定某一單元格放置去重結果的位置,比如 A18,最後,結果會出現在這裡。
然後選中 A18 單元格,並依次點選上方選單欄「公式」「自定義公式」「UNIQUE」公式。
然後滑鼠選中所要去重區域,並按回車建即可。
排序函式:SORT
石墨提供的排序函式 SORT,可對指定的資料區域進行排序。
公式:SORT(範圍,排序依照列,按升序排序)
示例:SORT(A1:B5,1,1)
說明:
- 單元格範圍:要進行排序的資料區域。
- 排序依據列:指定某一列作為排序依據列。
- 表示方法一:可用數字表示資料區域內的某一列,例如 1 表示資料區域的第一列。
- 表示方法二:可用單元格引用表示依據列,注意單元格引用必須是單列,且與資料區域的行數一致。
- 升序或降序:1 表示升序,-1 表示降序。
- 排序依據 2,升序或降序 2(可選):次要排序依據列和排序方式。
比如下表中,我們希望按照訂單號升序排序。
選中某空白區域單元格(A18),排序結果將放置在此。
依次選擇「公式」「自定義」「SORT」函式。(引數之間,需加入英文半形逗號)
括號中第一個引數處填寫需要排序的所有資料,再次選擇排序依據列(這裡也可以直接填寫數字 2,表示該表中的第二列),第三引數為排序依據,1 表示升序,-1 表示降序。
注意:
- 如果在公式中沒輸入第二引數(排序依據列)及第三引數(升序、降序),則結果預設按照範圍內第一列升序排序。
- SORT 函式支援多個排序依據,如:=SORT(A18:G24,2,1,A18:A24,1),這裡的「A18:A24,1」就是第二排序依據。優先順序上,第一排序依據大於第二排序依據。
篩選函式:FILTER
石墨提供的篩選函式 FILTER,可以對指定資料區域按條件進行篩選。
公式:FILTER(單元格範圍,條件 1,[條件 2,...])
示例:FILTER(A1:B5,A1:A5>10)
說明:單元格範圍:要進行篩選的資料區域。
- 條件 1:需要滿足的篩選條件。若要篩選行,則篩選條件的區域應是與資料區域行數相同的一列範圍;若要篩選列,則篩選條件的區域應是與資料區域列數相同的一行範圍。
- 條件 2(可選):補充的篩選條件,參與判斷的條件區域應與條件 1 的區域形狀一致。
比如我們需要將上圖所有使用者評級為 B 級的行篩選出來。
首先,選中某空白區域單元格(A18),接下來的排序結果將放置在此。
依次選擇「公式」「自定義」「FILTER」函式。(引數之間,需加入英文半形逗號)
選中排序範圍後,選中第三列,並在該引數處填寫「C2:C8="B 級"」,(引號為英文半形引號),並按回車即可顯示結果。
FILTER 函式還可以設定多個篩選條件,比如現在要篩選出評級「B」,並且購買型別為「公寓」的使用者,新增「公寓」條件即可。
查詢函式:XLOOKUP
石墨提供的查詢函式 XLOOKUP,可以搜尋指定區域或陣列,並由此從要返回的範圍中返回對應位置的值。
公式:XLOOKUP(搜尋值,搜尋範圍,返回的資料範圍,匹配型別,搜尋模式)
示例:XLOOKUP(330,C:C,A:A)
說明:
- 搜尋值:要查詢的目標值。例如 42、"Cats"或 124
- 搜尋範圍:要進行查詢的陣列或單元格區域。
- 返回的資料範圍:從資料範圍中返回與搜尋範圍搜尋到第一個匹配項的對應位置的項。
- 匹配型別:指定資料匹配型別(數值,可選)。完全匹配(0,預設);若未找到,返回大於搜尋值的最小值(1),或返回小於搜尋值的最大值(-1);或萬用字元匹配(2)。
- 搜尋模式:指定搜尋模式(數值,可選)。從第一個專案開始執行搜尋(1,預設),或從最後一個專案開始搜尋(-1),或按已升序進行搜尋(2),或按已降序進行搜尋(-2)
比如我們根據訂單號 216,將相應的使用者評級、姓名和聯絡方式查找出來。
首先,選中某空白區域單元格(B18),接下來的排序結果將放置在此。
然後,依次選擇「公式」「自定義」「XlOOKUP」函式。(引數之間,需加入英文半形逗號)
之後,選中搜索值單元格 B7(也可手動輸入該值),搜尋範圍選中 B 列,返回資料範圍選中 C2:E8。
相比 VLOOKUP 函式,XLOOKUP 可以更快捷地滿足逆向查詢、多條件查詢、指定位置查詢等使用場景的需要,是 VLOOKUP 函式的加強版。
陣列公式多用於處理合併後的工作表結果。此外,陣列公式還可與其他功能關聯使用,如公式的計算結果應用條件格式來高亮重點;公式結果作為資料來源生成資料透視表或者繪製圖表。