學會這個Excel萬能篩選公式,同事花大半天完成的工作,你3分鐘就搞定!

excel教程2019-08-18 00:20:35

關注回覆[目錄]學習所有教程


添加老師微信學習Excel

今天給大家分享excel跳過空單元格這個知識點,非常實用,但是知曉此技巧的童鞋不多。

比如在我們日常工作中,總會有一些表格需要多人或多部門協作填寫,例如下面的表格,分別是兩名工作人員填寫的差旅費用,B列是一個人填寫的,C列是另外一人填寫的。最後我們需要把它們彙總到一起,形成最終完整的表格。效果如EF列。


01


上面的案例,如何將B列和C列的內容合併到一列。可能大家首先想到的是使用IF函數來判斷得出結果:=IF(B2"",B2,C2),下拉複製,的確可以得到合併兩列數據的結果。

不過,本文將為大家分享另外一種excel技巧:使用跳過空單元格命令來完成。

excel跳過空單元格操作技巧如下:
複製C2:C10單元格,選擇B2單元格,再右擊並在彈出的快捷菜單中選擇“選擇性粘貼”命令,選中“跳過空單元格”,確定即可完成操作。

02


INDEX+SMALL函數構造篩選公式
篩選各組中工資最高的人的各項資料(如果最高工資重複,請按順序分別顯示出來)。


A18輸入公式,按下ctrl+shift+enter組合鍵完成數組公式的輸入,然後右拉下拉複製公式。
=INDEX($B:$F,SMALL(IF($F$2:$F$11=MAX(($D$2:$D$11=$A$16)*$F$2:$F$11),ROW($2:$11),4^8),ROW(A1)),COLUMN(A1))&""
解題思路:確定兩個條件:
組數:D2:D11=$A16
最高工資:F2:F11=MAX((D2:D11=A16)*F2:F11))
公式構成:INDEX(區域,行,列)&""
INDEX($B:$F,行部分,COLUMN(A1))&""
INDEX+SMALL函數構造出來的篩選公式,經典在於獲取出相應的行。剖析公式一般從內到外,用F9鍵逐一查看運算結果。

SMALL部分,獲取行號,剖析如下:


1.MAX部分:

MAX((D2:D11=A16)*F2:F11))

D2:D11=A16,判斷D列的組別和A16組別是否相等,得到FALSE和TRUE構成的邏輯數組。

(D2:D11=A16)*F2:F11,計算結果將符合條件的true對應的數字取出來:

{0;0;0;9000;6000;0;0;0;0;0}

然後用MAX(數字),取出最大值9000。


2.IF部分:

IF(條件,是,否)

IF(F2:F11=9000,ROW($2:$11),4^8)

在F2:F11區域中查找等於第一部分MAX計算的最大值,如果等於最大值,返回對應的行號(ROW($2:$11)),否則返回4^8。4^8:是4的8次方,結果等於65536 即2003中最大的行號。


3.SMALL部分:

SMALL(最大行號和符合條件的行號,ROW(A1))

用SMALL在65536和對應的一個行號中取最小值,得到的就是符合條件的行號。

SMALL({65536;65536;65536;5;65536;65536;65536;65536;65536;65536},ROW(A1)),結果是5。

INDEX部分解析


INDEX(區域,行,列)

INDEX($B:$F,5,COLUMN(A1)),返回B:F列這個區域的第五行第一列,對應的單元格就是B5單元格。

為了美觀,最後添加&""     


上面INDEX部分就可以完成篩選數據,但在下拉右拉複製公式時,超過結果以外的單元格會顯示“0”,如果想去掉0,直接用空白單元格,不顯示0,就可以在公式最後添加&""。


&""是什麼意思呢? &是個文本粘貼符,後面的""是表示空白文本,就等於在後面強制性的把(0)粘貼成了空白文本。


今天的內容大家學會了嗎?覺得沒學夠的話,還可以看看我們的第二篇教程哦~最後祝大家七夕節快樂!♥(๑> ₃


掃一掃加入QQ羣學習


Excel教程相關推薦



80%的人不知道吃了大虧!這個excel函數組合輕鬆解決你的工作難題!

熬夜加班髮際線後移?誰讓你不會Excel萬金油公式!

選擇性粘貼有這麼多功能,你還只會ctrl+V?

想要跟隨滴答老師全面系統學習Excel,不妨關注《一週Excel直通車》視頻課或者《Excel極速貫通班》。


《一週Excel直通車》視頻課

包含Excel技巧、函數公式、

透視表、圖表。

一次購買,永久學習。


最實用接地氣的Excel視頻課

《一週Excel直通車》

風趣易懂,快速高效,帶您7天學會Excel

38 節視頻大課

(已更新完畢,可永久學習)

理論+實操一應俱全


主講老師: 滴答


 

Excel技術大神,資深培訓師;

課程粉絲100萬+;

開發有《Excel小白脱白系列課》

        《Excel極速貫通班》。


原價299元

限時特價 99 元,隨時漲價

少喝兩杯咖啡,少吃兩袋零食

就能習得受用一生的Excel職場技能!


  長按下面二維碼立即購買學習


購課後,加客服微信:blwjymx2領取練習課件


讓工作提速百倍的「Excel極速貫通班」

↓ 點擊閲讀原文,可直接購買。

https://hk.wxwenku.com/d/201169005