sponsored links

Excel接近萬能函式ETSQL實現Excel主流函式所有功能

首先安利一下,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可以沒有。

Excel接近萬能函式ETSQL實現Excel主流函式所有功能


樣例資料為胡謅資料

上圖樣例資料,為胡謅的資料,如果想把這個表格裡面班組為01班的人所有資訊,都查詢出來,則SQL指令碼為:

SELECT
  *
FROM
  [胡謅資料 $ ]
WHERE
  班組 = '01班'

select 後面“*”號表示查詢所有的欄位(列)資料,表格為“胡謅資料"sheet表格的名稱,where後面條件就是 班組='01班'。執行結果如下圖所示,將所有為01班的資料全部查詢出來,配合ETResize函式(365版本動態陣列直接顯示)。

Excel接近萬能函式ETSQL實現Excel主流函式所有功能


條件查詢

如果想查詢01班的人員資訊,只需要將SQL指令碼之中01班替換成07班即可。實際應用場景之中,可以透過下拉選單實現。完整SQL指令碼,可以透過Excel公式拼接完成。

Excel接近萬能函式ETSQL實現Excel主流函式所有功能


動態篩選查詢

增加一下複雜度,想查詢01班組下,工資大於14900的所有人的資訊。則SQL指令碼寫法為:

SELECT
  *
FROM
  [胡謅資料 $ ]
WHERE
  班組 = '01班'
AND 工資 > 14900

Excel接近萬能函式ETSQL實現Excel主流函式所有功能


雙條件過濾

新增工資限制條件後,總共只查詢出來了12個人的資訊。多個條件之中使用“and”做拼接。,表示兩個條件必須同時滿足。如果將and 改為or,則表示01班的人或者工資大於14900的人,都會被選擇出來。

二、求平均工資

有這樣一種需求,想統計每個班級平均工資,那SQL該怎麼寫呢?

SELECT
  班組,
  avg( 工資 )
FROM
  [胡謅資料 $ ]
GROUP BY
  班組

Excel接近萬能函式ETSQL實現Excel主流函式所有功能

再增加一下需求難度,統計出來平均工資後,想按工資,降序排序,則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函式一模一樣。

Excel接近萬能函式ETSQL實現Excel主流函式所有功能

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

Excel接近萬能函式ETSQL實現Excel主流函式所有功能


最高工資的班組,前10名成員

下文為三個ETSQL函式三個典型應用動畫:

Excel接近萬能函式ETSQL實現Excel主流函式所有功能


ETSQL 統計平均值

Excel接近萬能函式ETSQL實現Excel主流函式所有功能


統計人數

Excel接近萬能函式ETSQL實現Excel主流函式所有功能


計算總額

注意實現

  • ETSQL功能很強大,但不能“貪杯”,ETSQL可以用來實現複雜資料查詢工作,複雜資料統計工作;
  • Excel SQL引擎,需要資料保持強型別資料,這點和Excel本身資料型別是相悖的,在使用Excel SQL之前需要保證,每列資料為一種資料型別,例如數值型別,則該列都是數值型別,布林型別則都是布林型別,文字型別,則應該都是文字型別資料。
  • 不在在查詢資料的表格內使用ETSQL函式,可以這麼理解,不要再本表格內查詢本身,這就陷入一個死迴圈,可能導致Excel異常退出。當然這點,並不是不可破解的。ETSQL函式有三個引數。第三個引數,就是破解之法,具體還需要實際操練一下。

如果EFunction的ETSQL函式對你有幫助,歡迎關注並分享,並向小編獲取EFunction工具。後續小編繼續賣力講解ETSQL典型應用指令碼模板。

劃重點EFunction獲取碼7e1op236

分類: 汽車
時間: 2021-11-21

相關文章

氣囊原因 沃爾沃全球範圍召回超46萬輛車

氣囊原因 沃爾沃全球範圍召回超46萬輛車
近日,沃爾沃在全球範圍內召回了超46萬輛老款車型,主要由於老式安全氣囊存在問題,發生碰撞有致命的危險.其中涉及2000年5月-2009年3月這期間生產的沃爾沃S60和沃爾沃S80. 沃爾沃方面坦言,由 ...

9月我國新能源車產銷均超35萬輛 動力電池裝車量15.7GWh
摘要:9月,我國新能源汽車產銷分別完成35.3萬輛和35.7萬輛,同比均增長1.5倍.1-9月,我國新能源汽車產銷分別完成216.6萬輛和215.7萬輛,同比分別增長1.8倍和1.9倍.9月,我國動力 ...

9月底新能源車銷售滲透率擴至20.4% 蔚來(NIO.US)小鵬(09868)月交付量均超1萬輛
智通財經APP獲悉,最新的國內新能源乘用車產銷跟蹤資料顯示,本季度末新能源車內銷33.8萬輛,環比大幅增加26%. 9月新能源乘用車銷量方面,批發35.5萬輛,同比增加184% ,環比增加15% ;滲 ...

每46秒交付一輛 特斯拉9月在華交付超5萬輛
北京商報訊(記者 劉洋 劉曉夢)10月12日,特斯拉(中國)釋出資料顯示,今年9月交付量為5.6萬輛,同比增長高達394%,相當於每46秒交付一輛新車. 據瞭解,在9月超5萬輛的交付量中,中國本土交付 ...

資訊 | 老款S60/S80安全氣囊存隱患,沃爾沃全球召回超46萬輛車

資訊 | 老款S60/S80安全氣囊存隱患,沃爾沃全球召回超46萬輛車
文:懂車帝原創 常思玥 [懂車帝原創 行業] 日前,沃爾沃開啟了一次全球範圍內的超過46萬輛車的大規模召回,原因是老式安全氣囊存在問題隱患,一旦發生碰撞可能會致命. 沃爾沃開啟了全球46萬車的召回 具 ...

安全氣囊存在致命威脅,沃爾沃全球範圍召回超46萬輛車

安全氣囊存在致命威脅,沃爾沃全球範圍召回超46萬輛車
近日,沃爾沃在全球範圍內進行了召回,數量超過了46萬輛老款車型,主要是2000年5月-2009年3月這期間生產的車型,由於老式安全氣囊存在問題,發生碰撞有致命的危險. 主要涉及到的車型有2001-20 ...

車壇快報|工信部:7家車企的8個新能源車型不符合生產一致性要求
工信部:7家車企的8個新能源車型不符合生產一致性要求 9月23日,工業和資訊化部發布2020年度新能源汽車監督檢查結果發現共有7家企業的8個車型不符合生產一致性要求,主要情況如下: 一.新能源乘用車 ...

今年新能源乘用車銷量已超200萬輛,各車企頻推新車衝刺

今年新能源乘用車銷量已超200萬輛,各車企頻推新車衝刺
9月,新能源汽車市場迎來銷量高峰. 據乘聯會資訊顯示,9月新能源車批發量達到35.5萬輛,同比增長184.4%,累計批發量202.3萬輛,同比增長218.9%.其中,比亞迪批發量更是突破7萬輛.特斯拉 ...

數讀 | 廣汽豐田前三季度銷量超60萬輛,新款漢蘭達提車等兩個月
文:懂車帝原創 李德喆 [懂車帝原創 行業] 10月8日,廣汽豐田官方公佈了今年前三季度銷量資料.從官方公佈的資料來看,今年1-9月,廣汽豐田終端累計銷量達60.34萬輛,同比增長12%.而從廣汽集團 ...

8月我國新能源車產銷量首超30萬輛
在今年下半年車市實現平穩開局後,8月份國內車市交出什麼樣的成績單,備受消費市場各方關注.日前,中國汽車工業協會(以下簡稱中汽協)釋出最新一期產銷資料顯示,今年8月,我國汽車銷量179.9萬輛,同比下降 ...

8月MPV銷量榜完整版,五菱宏光超2萬輛,傳祺M8再超奧德賽

8月MPV銷量榜完整版,五菱宏光超2萬輛,傳祺M8再超奧德賽
[2021年8月MPV銷量排行榜 完整榜單]據乘聯會日前公佈的資料顯示,2021年8月國內MPV市場批發銷量為8.9萬輛,同比下降11.2%,環比7月份增長10.1%.下面,我們一起去看看今年8月份4 ...

韓國迎來中秋假期返鄉潮 首日超440萬輛汽車上路

韓國迎來中秋假期返鄉潮 首日超440萬輛汽車上路
韓國18日迎來中秋假期.返鄉車輛上路,多地公路當天一早即現擁堵.韓國公路管理部門估計,當天全國出行車輛將在447萬輛左右. 圖源:新華社 韓國今年中秋假期為18日至22日.18日上午,首爾與第二大城市 ...

氣囊存在問題 沃爾沃全球召回超46萬輛汽車
據外媒報道,沃爾沃汽車將在全球範圍內召回460,769輛舊款汽車,原因是車輛氣囊存在問題,在發生車禍時可能致人死亡. 沃爾沃汽車對美國安全監管機構表示,該問題會在因為發生碰撞而啟動安全氣囊的時候出現. ...

長城汽車銷量快報:9月銷量超10萬臺,坦克交付量創新高

長城汽車銷量快報:9月銷量超10萬臺,坦克交付量創新高
10月11日,長城汽車公佈9月銷量快報.資料顯示,長城汽車9月銷售新車100022輛,1-9月累計銷售884045輛,同比增長29.9%.其中,海外市場9月銷售新車11,437輛,同比增長47.1%, ...

特斯拉再次打破紀錄:9個月交付超62萬輛

特斯拉再次打破紀錄:9個月交付超62萬輛
特斯拉公佈2021年第三季度汽車生產和交付資料,資料顯示,特斯拉再次打破交付紀錄,2021年前九個月的交付量已經超過627000輛.該公司在二季度首次交付了超過200000輛汽車,三季度的交付量甚至超 ...

長安福特9月銷量超2萬輛 實現環比/同比雙增長

長安福特9月銷量超2萬輛 實現環比/同比雙增長
日前,網通社從長安福特官方獲悉,企業2021年9月零售銷量達20,707輛,實現同環比雙增長,其中同比增長6%.環比增長17.3%.2021年1-9月,長安福特零售銷量累計已達141,520輛,同比增 ...

又因後懸架故障,超過12.6萬輛2011-2013年款福特探險者在美召回

又因後懸架故障,超過12.6萬輛2011-2013年款福特探險者在美召回
[汽車召回]據外媒報道,超過12.6萬輛福特探險者在北美召回,因為它們的懸掛部件可能會發生故障. 此次召回涉及2011-2013年款探險者,這些產品曾在2019年7月宣佈召回過.受影響的車輛可能使用了 ...

豐田緊急召回11萬輛汽車:涉及39款車型

豐田緊急召回11萬輛汽車:涉及39款車型
近日,豐田召回11萬量車型,其中涉及39款車型,共計111842輛汽車. 此次召回主要有兩大類問題: 1.控制程式存在缺陷,可能導致車輛緊急通訊裝置無法語音通話 2.引擎的部分零部件焊接部位開裂,可能 ...

年少時許下的夢 重慶車友的賽道化豐田86改裝案例

年少時許下的夢 重慶車友的賽道化豐田86改裝案例
"相信對於90後男生來說,<頭文字D>是一部構建起最初對於汽車夢想的動畫,我也亦然.在這之後,每個月用零花錢買一本汽車雜誌成為我最期待的事情,逐漸的,知道了NA.FR.手動等等當 ...

價格低保錢包,5L油耗耐用好修,家用合資車,帶你看豐田威馳FS

價格低保錢包,5L油耗耐用好修,家用合資車,帶你看豐田威馳FS
價格低保錢包,5L油耗耐用好修,家用合資車,帶你看豐田威馳FS 一樣的東西你給一家賣也是賣,給兩家賣也是賣,這是現在很多在國內有兩個合資廠商的品牌共有的一種心態,一方面兩個合作方就像婆婆與丈母孃,一碗 ...