首先安利一下,EFunction高階函式工具為Excel高階函式外掛,該外掛擴充了60個函式,適用於Excel2013、2016、2019及365版本。這個只有window下有效。mac無效。
永久註冊編碼:7e1op236。EFunction載入成功後,可以使用此號碼聯網獲取永久碼,獲取成功後,別忘了儲存永久註冊碼。
ETSQL應該是EFunction超級函式工具裡面最具代表性,功能最強大的一個函式。這個函式,可以實現VLOOKUP函式所有功能,也可以實現VLOOKUP函式不能實現的功能;這個函式也可以實現sumifs函式系所有的功能,也可以實現sumifs函式系不能夠實現的功能。
誇了這麼久ETSQL函式功能的強大。ETSQL為啥功能這麼強大,這是因為ETSQL函式,利用了Excel內建的SQL引擎,執行了SQL操作。
SQL語言可以實現資料查詢操作,資料統計工作,甚至可以實現資料更改操作(ETSQL只支援資料查詢、統計工作)
SQL函式執行結果為一個數組,Excel 365版本使用者,可以利用動態陣列特性,將所有結果顯示出來,低版本Excel,可以利用ETResize函式,動態顯示所有結果。
介紹幾個ETSQL函式典型應用案例,如果之前沒有學習SQL知識,收藏好了,等下次老闆派任務時,就派上用場了。
一、資料複雜查詢
Excel SQL基本語言和其他資料庫語法基本類似,但也有自身特點。資料查詢基本語法為:
select
something
from
[table$]
where
conditions
其中select from where 關鍵詞位置必須固定,而且必須存在,不限定條件時,where可以沒有。
上圖樣例資料,為胡謅的資料,如果想把這個表格裡面班組為01班的人所有資訊,都查詢出來,則SQL指令碼為:
SELECT
*
FROM
[胡謅資料 $ ]
WHERE
班組 = '01班'
select 後面“*”號表示查詢所有的欄位(列)資料,表格為“胡謅資料"sheet表格的名稱,where後面條件就是 班組='01班'。執行結果如下圖所示,將所有為01班的資料全部查詢出來,配合ETResize函式(365版本動態陣列直接顯示)。
如果想查詢01班的人員資訊,只需要將SQL指令碼之中01班替換成07班即可。實際應用場景之中,可以透過下拉選單實現。完整SQL指令碼,可以透過Excel公式拼接完成。
增加一下複雜度,想查詢01班組下,工資大於14900的所有人的資訊。則SQL指令碼寫法為:
SELECT
*
FROM
[胡謅資料 $ ]
WHERE
班組 = '01班'
AND 工資 > 14900
新增工資限制條件後,總共只查詢出來了12個人的資訊。多個條件之中使用“and”做拼接。,表示兩個條件必須同時滿足。如果將and 改為or,則表示01班的人或者工資大於14900的人,都會被選擇出來。
二、求平均工資
有這樣一種需求,想統計每個班級平均工資,那SQL該怎麼寫呢?
SELECT
班組,
avg( 工資 )
FROM
[胡謅資料 $ ]
GROUP BY
班組
再增加一下需求難度,統計出來平均工資後,想按工資,降序排序,則SQL寫法為:
SELECT
班組,
avg( 工資 )
FROM
[胡謅資料 $ ]
GROUP BY
班組
ORDER BY
avg( 工資 ) DESC
avg是聚合函式,SQL裡常用聚合函式有sum,count等。order by 是排序關鍵詞,後面跟著排序條件,desc降序,asc是升序。
上述這種統計各個班組平均工資,並且根據平均工資進行降序,排序還可以透過ETAggregate函式實現。完整函式形式為:=ETResize(ETSortBy(ETAggregate(胡謅資料!B2:H7326,3,{1,3}),2,TRUE)),執行結果和ETSQL函式一模一樣。
ETSQL上述上述介紹,已經實現了office 365 FILTER,實現了EFunction高階函式ETAggregate,SortBy等高階函式所有的功能,這是不是很強大。
三、來一個複雜的高階需求
有這樣一種需求,需要統計出來班級總工資最高的班級,並且展示前TOP10高工資的員工資訊。
那這樣怎樣處理呢?
要想實現這樣的需求,先要將需求進行分解。第一步先確定哪個班級的平均工資最高,
select 班組 from[胡謅資料$] group by 班組 order by avg(工資)desc limit 1
把上文求平均工資,並排序的SQL指令碼稍加改動,在指令碼後面新增 top 1,表示只提取排序好的資料第一行。可以將“03班”提取出來。
SELECT
top 10 *
FROM
[胡謅資料 $ ]
WHERE
班組 = ( SELECT top 1 班組 FROM [胡謅資料 $ ] GROUP BY 班組 ORDER BY avg( 工資 ) DESC )
ORDER BY
工資 DESC
完整的SQL指令碼如上所示,將最高平均工資的班級,提取出來後,最為查詢指定班組的TOP10工資。上述指令碼等價為:
SELECT
top10 *
FROM
[胡謅資料 $ ]
WHERE
班組 = '03班'
ORDER BY
工資 DESC
下文為三個ETSQL函式三個典型應用動畫:
注意實現:
- ETSQL功能很強大,但不能“貪杯”,ETSQL可以用來實現複雜資料查詢工作,複雜資料統計工作;
- Excel SQL引擎,需要資料保持強型別資料,這點和Excel本身資料型別是相悖的,在使用Excel SQL之前需要保證,每列資料為一種資料型別,例如數值型別,則該列都是數值型別,布林型別則都是布林型別,文字型別,則應該都是文字型別資料。
- 不在在查詢資料的表格內使用ETSQL函式,可以這麼理解,不要再本表格內查詢本身,這就陷入一個死迴圈,可能導致Excel異常退出。當然這點,並不是不可破解的。ETSQL函式有三個引數。第三個引數,就是破解之法,具體還需要實際操練一下。
如果EFunction的ETSQL函式對你有幫助,歡迎關注並分享,並向小編獲取EFunction工具。後續小編繼續賣力講解ETSQL典型應用指令碼模板。
劃重點EFunction獲取碼7e1op236