小夥伴們好啊,今天和大家分享幾個Office 365中新函數的典型用法,有了這些新函數,讓原本複雜的計算變得越來越簡單。
1、數據轉置
如下圖所示,為了便於打印,要將A列中的姓名,轉換為多行多列。
D6單元格輸入以下公式,按回車:
=INDEX(A:A,SEQUENCE(E3,E4,2))&""
SEQUENCE函數的作用是按指定的行列數生成序號,常用寫法是:
=SEQUENCE(行數,[列數],[開始數],[增量])
先使用SEQUENCE函數,根據E3和E4單元格中指定的行列數,得到一個從2開始的多行多列的序號。然後再使用INDEX函數返回A列對應位置的內容。
2、銷售業績排序
如下圖所示,要根據C列的銷售套數,使用公式得到排序後的銷售記錄。
E2單元格輸入以下公式,按回車。
=SORT(A2:C17,3,-1)
SORT函數的作用是對數據區域中指定的行列進行排序,常用寫法是:
=SORT(數據區域,[對第幾列(行)排序],[升序還是降序],[按行還是按列排序])
第三參數使用-1,表示降序排序,使用1表示升序排序。
第四參數省略,表示按列方向排序。
3、多條件排序如下圖所示,要根據銷售處和銷售套數兩個指標,使用公式得到排序後的銷售記錄。
E2輸入以下公式,按回車。
=SORTBY(A2:C17,A2:A17,1,C2:C17,-1)
SORTBY的作用是對某個區域按多組條件進行排序,常用寫法是:
=SORTBY(數據區域,要排序的列1,[升序/降序],[要排序的列2],[升序/降序]..)
4、提取不重複值
如下圖所示,要在值班記錄中提取不重複的人員名單。
E2單元格輸入以下公式,按回車:
=UNIQUE(B2:C8)
UNIQUE函數的作用是在數據表中提取不重複值的,工作方式類似於刪除重複值功能,常用寫法是:
=UNIQUE(數據源,[按行/按列排序],[只提取出現一次的內容還是提取全部記錄])
5、提取符合條件的多個記錄
如下圖所示,是某公司的春節值班費明細表,要根據G2單元格指定部門,返回該部門的所有記錄。
F6單元格輸入以下公式,按回車:
=FILTER(A1:D11,A1:A11=G2)
FILTER函數的作用是根據指定條件從列表中提取記錄,常規用法是:=FILTER(數據區域,條件區域=指定條件,不符合條件時返回的內容)
6、提取指定條件的不重複值
如下圖所示,要根據G1單元格中指定區域,從左側表格中提取出該區域不重複的產品列表。
F4單元格輸入以下公式,按回車:
=UNIQUE(FILTER(D2:D17,A2:A17=G1))
公式的意思是,在FILTER函數提取出的基礎上,再使用UNIQUE獲取不重複記錄。
7、提取符合多個條件的多個記錄
如下圖所示,要根據G2單元格指定部門、以及G3單元格指定的職務,返回符合這兩個條件的所有記錄。
F6單元格輸入以下公式,按回車:
=FILTER(A1:D11,A1:A11&B1:B11=G2&G3)
這個公式和第5個示例用法是一樣的,只不過是將兩個條件區域合併成了一個,將兩個條件合併成了一個而已。使用這個方法,咱們可以根據需要添加多組不同的條件。
圖文製作:周慶麟