用MS QUERYE做動(dòng)態(tài)查詢,不用函數(shù)!
?
作者:小窩來源:部落窩教育發(fā)布時(shí)間:2023-11-17 01:34:37點(diǎn)擊:692
在低版本Excel中因?yàn)槿狈?span>Filter函數(shù),用公式做動(dòng)態(tài)查詢,尤其是多條件模糊查詢要編寫復(fù)雜的公式。今天分享用Microsoft Query做動(dòng)態(tài)查詢,只需簡(jiǎn)單幾步操作即可。
Microsoft Query在Excel高低版本中都可以使用。為了方便低版本(小于2019)的伙伴,小窩用Excel 2016演示。
啟動(dòng)Excel 2016,打開素材。這是一份人員名單。
如果按部門進(jìn)行查詢,先篩選:
然后再選擇可見單元格進(jìn)行復(fù)制粘貼得到需要的數(shù)據(jù):
有沒有覺得笨拙?
下面感受一下Microsoft Query建立的動(dòng)態(tài)查詢。
精確查詢:
模糊查詢:
怎么樣,很牛吧?!
下面就看看是怎么實(shí)現(xiàn)的。
1.通過MS Query建立數(shù)據(jù)連接
Step 01 首先通過數(shù)據(jù)驗(yàn)證建立用于篩選的下拉菜單。
step 02 單擊“數(shù)據(jù) — 自其他來源 — 來自Microsoft Query”,然后選擇“Excel Files”作為數(shù)據(jù)源,確定后從彈出的“選擇工作簿”對(duì)話框中選擇要查詢的素材文件。
Step 03 在“查詢向?qū)?span>”對(duì)話框中首先單擊“選項(xiàng)”,把“系統(tǒng)表”勾選上。然后展開工作表,選擇需要的列(后續(xù)還可以增刪列)加入查詢結(jié)果的列中。
注:如果你遇到警告“數(shù)據(jù)源中沒有包含可見的表格”,不急,進(jìn)入選項(xiàng)中勾選“系統(tǒng)表”即可。
Step 04 多次單擊“下一步”,最后選擇“在Microsoft Query中查看數(shù)據(jù)或編輯查詢”單擊“完成”。
現(xiàn)在進(jìn)入了MS Query中。
2.添加精確查詢條件
Step 01 單擊“顯示/隱藏條件”按鈕將條件編輯欄顯示出來。
Step 02 在第一列第一行中單擊選擇“部門”作為條件字段。在第二行中雙擊,彈出編輯條件對(duì)話框,運(yùn)算符選擇“等于”,指定值中輸入?yún)?shù)名稱“[bumen]”(不與條件字段相同的任何文字)。(也可以不雙擊,直接在值行中輸入?yún)?shù)名稱[bumen]并回車。)確定或回車后會(huì)彈出“輸入?yún)?shù)值”對(duì)話框,任意輸入一個(gè)部門名稱即可。確定后可看到下方的數(shù)據(jù)已經(jīng)完成了篩選。
說明:方括號(hào)必須,用于標(biāo)記參數(shù)名稱。
Step 03 單擊“將數(shù)據(jù)返回到Excel”按鈕回到Excel界面,彈出“導(dǎo)入數(shù)據(jù)”對(duì)話框。單擊下方“屬性”按鈕,切換到“定義”選項(xiàng)卡。單擊下方的“參數(shù)”按鈕彈出“查詢參數(shù)”設(shè)置框,選擇“從下列單元格中獲取數(shù)值”,拾取L2單元格(前面做的下拉菜單單元格),勾選“單元格值更改時(shí)自動(dòng)刷新”選項(xiàng)。兩次確定后回到“導(dǎo)入數(shù)據(jù)”對(duì)話框,拾取查詢數(shù)據(jù)放置位置后單擊“確定”。
Step 04 在L2單元格選擇不同部門即可看到查詢結(jié)果。
我們可以隨時(shí)編輯查詢,修改條件。下方通過修改創(chuàng)建模糊查詢。
3.編輯查詢
Step 01 單擊任意查詢數(shù)據(jù),右擊鼠標(biāo),選中“表格 — 編輯查詢”命令。會(huì)彈出“查詢向?qū)o法編輯此查詢”的警告,直接確定。確定后會(huì)讓輸入?yún)?shù)值,可以隨意輸入一個(gè)部門,如“技術(shù)部”進(jìn)行確定,也可以不輸入而直接確定。
Step 02 在條件編輯欄上方單擊選中當(dāng)前的整個(gè)條件,然后按Delete鍵刪除。
下面我們以姓名作為模糊篩選條件。
Step 03 在條件字段行中輸入字段“姓名”;雙擊值行,運(yùn)算符選擇“包含”,指定值中輸入?yún)?shù)名“[xingming]”。確定后值行顯示查詢語句“Like '% [xingming] %'”。將該語句修改成“Like '%' & [xingming] & '%'”,回車后彈出輸入?yún)?shù)值對(duì)話框,隨意輸入一個(gè)姓氏,如“李”即可。(如果熟悉語句了,就不用雙擊值行而直接輸入Like '%' & [xingming] & '%'即可)
說明:MS Query采用SQL語句。like代表“包含”運(yùn)算,可以實(shí)現(xiàn)模糊查找。%代表任意字符。'%'& [xingming] & '%'相當(dāng)于Excel工作表中使用通配符的查找條件“*關(guān)鍵字*”。
Step 04 單擊“將數(shù)據(jù)返回到Excel”按鈕回到Excel中。刪除原來的部門篩選下拉菜單,“部門”改“姓名”。在查詢數(shù)據(jù)上右擊,選擇“表格 — 參數(shù)”修改查詢參數(shù)的設(shè)置。完成動(dòng)態(tài)模糊查詢。
4.多條件動(dòng)態(tài)查詢
采用相同的方法可以增加篩選條件,實(shí)現(xiàn)多條件動(dòng)態(tài)篩選。
1)添加并列條件
在條件欄的第2列中增加條件即可表達(dá)并列多個(gè)條件查詢。
譬如再添加一個(gè)部門模糊查詢,實(shí)現(xiàn)姓名、部門的雙查詢。
右鍵“編輯查詢”回到MS Query中,新增一個(gè)包含條件即可。
ok,用Microsoft Query進(jìn)行動(dòng)態(tài)查詢就介紹到這里。
再譬如修改查詢?yōu)楣g范圍查詢。范圍查詢,并列兩個(gè)條件,字段都是“工齡”,一個(gè)值是“>=[gongling1]”,另一個(gè)值是“<=[gongling2]”。如圖:
注:
(1)也可以不用建立兩列條件,就在條件1的值中寫成“>=[gongling1] and <=[gongling2]”。
(2)值中支持比較運(yùn)算符“>、>=、<>、<、<=”,支持邏輯符號(hào)“and、or、not”。
(3)如果提示“標(biāo)準(zhǔn)表達(dá)式數(shù)據(jù)類型不匹配”,確定后不輸入值即可。
2)添加或條件
或條件就在同列的或行中增加。
譬如修改查詢?yōu)椴檎覍W(xué)歷是某某或者某某。
注:同樣可以不用在或行輸入,可以直接在第一個(gè)值中輸入“[xueli1] or [xueli2]”。
OK,關(guān)于用 MS Query做動(dòng)態(tài)查詢就介紹這么多。
親們,點(diǎn)贊、分享,走起!
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育Excel精品好課任你選擇!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
版權(quán)申明:
本文作者小窩;部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!