精進Excel
無邀自答。我也來回答下這個問題,分享下我的觀點,希望能幫到大家!
Excel最強大的公式是哪個?
有個段子:"Vlookup說它匹配厲害,Lookup就笑了;If說它邏輯厲害,Choose就笑了;Sum說它求和厲害,Subtotal就笑了;Sumif說它條件求和厲害,Sumifs就笑了;Index說它引用厲害,Indirect就笑了;Find說它查找厲害,Search就笑了;Left/Right說它截字符厲害,Mid就笑了... ..."
可見,Excel中沒有最強,只有更強!
都知道,Excel中,最常用的函數,一定是Vlookup,從某種角度上講,Lookup的功能確實比Vlookup強大。只是,功能強大的背後,那一定是複雜的使用邏輯和參數,
如:Lookup實現多條件查詢時,這樣用
=LOOKUP(1,0/((條件1)*(條件2)*……*(條件N)),目標區域)
好吧,確實很強,只是,這邏輯,這參數,多少人能理解!
所以,我個人認為,Excel中最強的,不是哪個具體公式,而在於思路。
如上的多條件查詢,我會添加一個輔助列,聯合條件,然後再使用查詢函數即可。
如下所示:
Index+Match組合,我心中的最強
查詢函數,我基本上是天天用,也是最熟悉的,比起Vlookup和Lookup,我更喜歡使用Index+Match,因為,不管哪種情況,他們的參數都差不多,沒有那些看起來羞澀難懂的各類參數寫法。
基礎用法
INDEX(區域, 第幾行);
MATCH(找什麼, 在哪裡找, 查找方式);
Vlookup中所謂的逆向查詢,無非就是index第一個參數的列標而已。
如: =index(C:C,match(D2,a:a,0))
用上這個組合,就不用再關心,到底目標列是在查詢列的左側,還是右側了。
多條件查詢
如上,邏輯還是很簡單,match用姓名找出該人員所處的行號,再用月份找出對應列號,最後一個index定位到具體位置。
返回最後一個值
我的思路是:在源數據中先進行排序,升序或者降序,看需求了。再用查詢函數查詢。當然,更穩妥的做法,那就是添加輔助列,填充序號。
多個返回結果
其實,這種情況下,我一般不用公式了,高級篩選中有一個"複製到"的選項,可比那一長串的參數好用多了。
好了,都說Excel千面,每個人心中都有一個用起來最順手的公式,那就是你的最強!
以上僅是個人觀點,希望能幫到你,歡迎評論區討論!
我是Excel技巧精選,別忘了,點贊、點贊、點贊!!!
Excel技巧精選
曾經VLOOKUP說他是最強的,LOOKUP表示不服!
說起查找與引用函數,大家最熟悉的莫過於VLOOKUP了。其實它一個家族中的另一個兄弟,LOOKUP函數比它的功能更為強大,想要更好的處理數據的查找與引用,千萬不要錯過它!
01、LOOKUP函數語法
LOOKUP函數的語法有兩種:
1、向量式:
向量式是使用的更多的一種形式:
第一參數是要查找的值;
第二參數為一行或者一列,是被查找的區域。
第三參數是返回哪行或者哪列的結果,這裡第三參數要求與第二參數有一樣的尺寸。
比如第二參數是一列,B2到B5,第三參數是C1到C6,這樣就是不可以的。
2、數組式:
=LOOKUP(lookup_value,array)
第二參數array, 是一個多行多列的區域。這個區域的首列須包含要查找的值,返回對應最後一列的值。
這兩種方式,有的時候可以實現相同的功能,區別只是寫法不同; 也有的功能只能用其中一種的寫法。下面用栗子來給大家詳細展現。
02、LOOKUP模糊查找
個人所得稅稅率查詢:
(模糊匹配,類似VLOOKUP)
應納稅的工資(減去3500之後),查找應使用的稅率時,沒有完全一樣的數值,將返回小於它的最大值。
F2單元格中的公式為(向量式):
=LOOKUP(E2,$A$2:$A$5,$C$2:$C$5)
$A$2:$A$5 是要查找的區域;
$C$2:$C$5 是對應返回的結果。
例子中工資5000在左邊的表格中找不到,將返回小於它的最大值,也就是4500對應的結果,20%(虛構數值,僅供參考)。
這個例子中,還可以使用數組的方式,結果是一樣的:
=LOOKUP(E2,$A$2:$C$5)
注意:在用LOOKUP進行模糊匹配的查詢時,第二參數須以升序排列。
03、LOOKUP反向查找
由於LOOKUP函數向量式的寫法中,被查找的區域,和結果返回的區域是分開寫的,所以可以應用在,要返回的結果在被查找區域之前的情況。
例如,知道員工工號的情況下,要在左邊的表中查找員工的姓名。
E2中的公式為:
=LOOKUP(D2,$B$2:$B$8,$A$2:$A$8)
B2至B8是要查找的區域,返回A2至A8對應的結果。
需要注意的是,LOOKUP函數默認在找不到完全一致的值時,會按模糊匹配來處理,返回一個接近的值(小於它的最大值對應的結果)。
所以,如果要查找的值,可能在被查找區域不存在,又需要精確匹配時,不要使用這種方式。
04、條件查詢
0/條件 其實是一大類用法的總結。
公式寫作:=LOOKUP(1,0/(條件),目標區域或數組)
這裡的“條件”可以是多種多樣的。我們可以根據不同的需求,構建不同的“條件”。利用“0除以任何數都得0”和“0除以錯誤值還是錯誤值”的原理,得到一串0和錯誤值組成的數組。
我們要找的是“1”,在找不到“1”時,返回最後一個“0”對應的值。(LOOKUP自動屏蔽錯誤值)
“條件”如何構建,決定能發揮怎樣的作用。下面幾種常見用法的舉例。
① 求同一值最後一次出現的情況:
我們想求某值最後一次出現的結果,可以先通過使用一個條件,將出現這個值的情況挑選出來。
這個例子中用到的“條件”就是:=FIND(E3,$B$3:$B$11)
這個FIND函數的作用是,在B3到B11區域中,挨個單元格去查找,E3的值在這個單元格中的位置。
E3中是“張三”,B3中“張三”是從第一個字符開始的,所以返回1.
同理,B4和B5都會返回1。在找不到我們要的結果的時候,會返回錯誤值:#VALUE!.
也就是,符合條件時會返回一個非零數字,不符合條件時會返回錯誤值。
所以剛才的“條件”就會得到:
=FIND(E3,$B$3:$B$11)
={1,1,1,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}
=0/FIND(E3,$B$3:$B$11)
={0,0,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}
錯誤值會被LOOKUP函數排除掉,剩下就是在符合條件的值中進行運算。
最原始的公式就會變成:
=LOOKUP(1,0/FIND(E3,$B$3:$B$11),$C$3:$C$11)
=LOOKUP(1,{0,0,0},$C$3:$C$11)
由於我們要找1,而查找區域只有0,找不到1時,會返回最後一個0對應的結果。這個案例中就是第3個0對應的值,也就是B5單元格對應的結果C5。
總結一下0/[條件]的用法就是,通過構建一個“條件”,所有符合條件的值返回一個非零數字, 不符合條件的值返回一個錯誤值。
② 多條件查找:
多條件查找其實就是上面公式的一個變種。
寫作:
=LOOKUP(1,0/((條件1)*(條件2)*……*(條件N)),目標區域)
多個條件之間使用乘號連接,表示當列出的條件同時滿足時的結果。如果有多個行出現同時滿足條件,則返回最後一個。
下面這個例子中,我們需要班級和姓名兩個條件同時滿足時,對應的結果。
I2中的公式為:
=LOOKUP(1,0/(($B$3:$B$8=F3)*($C$3:$C$8=G3)),$D$3:$D$8)
這兩個條件分別是:
B3到B8中等於F3,和C3到C8中等於G3。
同時滿足時,將返回D3到D8對應的結果。
能實現這麼多功能,你說LOOKUP是不是最強大的函數?精進Excel
TEXT函數也相當強大,尤其在條件判斷方面。
默認條件區段
四個條件區段:
TEXT函數的格式代碼默認分為4個條件區段,各區段之間用半角分號間隔。
默認情況下,這四個區段的定義為:
[>0];[<0];[=0];[文本]
【舉例1】 按區段條件判斷,然後返回相應結果:
公式:=TEXT(A2,"0.00;-0;0;文本")的含義是:
A2單元格的值,按照四種情況返回結果:
>0,保留兩位小數;
<0,只保留整數;
=0,返回0值;
文本,返回“文本”二字。
【舉例2】按區段條件,強制返回相應結果:
公式:=TEXT(A8,"1!0!0;5!0;0;文本")的含義是:
A8單元格的值,按照四種情況返回結果:
>0,返回100;
<0,返回50;
=0,返回0值;
文本,返回“文本”二字。
公式中使用的感嘆號(英文半角)是轉義字符,強制其後的第一個字符不具備代碼的含義,而僅僅是數字。比如:1!0!0,將兩個0強制成數字0,而不是數字格式代碼0。
在實際應用中,可以使用部分條件區段。
三個條件區段:
三個區段為:
[>0];[<0];[=0]
【舉例3】
公式:=TEXT(A15,"盈利;虧損;平衡")的含義是:
A15單元格的值,按照三種情況返回結果:
>0,返回“盈利”;
<0,返回“虧損”;
=0,返回“平衡”;
兩個條件區段:
兩個區段的為:
[>0];[<0]
【舉例4】
公式:=TEXT(A22,"盈利;虧損")的含義是:
A22單元格的值,按照兩種情況返回結果:
>0,返回“盈利”;
<0,返回“虧損”;
一個區段的,就不講了,昨天前天的兩篇文章,都算是一個區段的。
自定義條件區段
TEXT函數除了可以使用默認區段以外,還可以自定義條件區段。
四個自定義條件區段:
四個區段的定義為:
[條件1];[條件2];[不滿足條件的其他部分];[文本]
【舉例5】
公式:=TEXT(A38,"[>=85]優秀;[>=60]合格;不合格;無成績")的含義是:
A38單元格的值,按照自定義的四種情況返回結果:
>=85,返回“優秀”;
>=60,返回“合格”;
不滿足以上條件的數值,返回“不合格”;
非數值,返回“文本”二字。
三個自定義條件區段:
三個區段的定義為:
[條件1];[條件2];[不滿足條件的其他部分]
【舉例6】
公式:=TEXT(A46,"[>=85]優秀;[>=60]合格;不合格")的含義是:
A46單元格的值,按照自定義的四種情況返回結果:
>=85,返回“優秀”;
>=60,返回“合格”;
不滿足以上條件,返回“不合格”;
兩個自定義條件區段:
兩個區段的定義為:
[條件];[不滿足條件的其他部分]
【舉例7】
公式:=TEXT(A54,"[>=60]合格;不合格")的含義是:
A54單元格的值,按照自定義的四種情況返回結果:
>=60,返回“合格”;
不滿足以上條件,返回“不合格”;
巧用TEXT嵌套自定義多條件區段
以上舉例中,我們可以看到,成績只能判斷到“優秀、合格、不合格”級別,如果再多級別,一個TEXT就解決不了了。TEXT函數也可以嵌套解決這個問題:
【舉例8】
要求:
90分及以上,返回“優秀”;
70分及以上,返回“良好”;
60分及以上,返回“合格”;
60分以下,返回“不合格”。
結果如下:
公式:TEXT(TEXT(A62-60,"[>=30]優秀;不合格;0"),"[>=10]良好;合格"),分解來解釋:
TEXT(A62-60,"[>=30]優秀;不合格;0")
對A62-60進行分段計算:
如果>=30,返回“優秀”;
-
如果<0,返回“不合格”;
不滿足以上條件,返回成績的整數。
如果成績中有小數,最後一個區段可以寫成0.0,或0.00.
通過這個公式,把成績分段成了>=90,<60,60~89三個區段。
TEXT(TEXT(A62-60,"[>=30]優秀;不合格;0"),"[>=10]良好;合格")
這一部分,對60~89的成績,減去60,然後計算:
如果>=10,返回“良好”;
否則,返回“合格”;
韓老師講office
對於Excel公式來說,我是覺得沒有最強大之說,每個公式都有獨立的功能,用法也不一樣,能幫你更好的解決問題才能算是強大,下面我為大家分享Excel中最長用的一些公式,
1、 查找重複內容公式:=IF(COUNTIF(A:A,A2)>1,"重複\
成都朗沃教育
我個人覺得是excel公式審核。
excel公式審核是一組命令,excel2003版它位於工具 - 公式審核,excel2010版在公式選項卡中。
公式審核在編輯公式時非常的有用,但可惜的是大部分用戶並沒有重視和使用它。下面我們盤點一下它的作用。
1、迅速找到引用的單元格。
如果一個公式中引用了大量的單元格,而你想看到具體每個被引用單元格位置和值。就點擊公式審核中的“追蹤引用單元格”命令。
2、快速顯示所有公式
點公式審核中的“顯示公式”,所有公式都會現出原形。
3、查看公式錯誤原因
公式出錯了,點擊“錯誤檢查”可以查看錯誤原因
4、快速查找長公式的出錯位置
當一個很長的公式返回錯誤值,很多新手會手足無措,不知道哪裡出錯了。
【例】:如下圖所示,單元格的公式返回值錯誤。要求排查出公式的哪部分出現了錯誤。
操作方法:
1、 打開單元格左上角綠三角,點“顯示計算步驟”(等同於點公式審核中的公式求值命令)
2、在打開的“公式求值”窗口中,求值會自動停在即將出錯的位置。這時通過和編輯欄中的公式比對,就可以找出產生錯誤的單元格。(D7)
如果公式中有多處錯誤,可以先修正前一次,然後再點顯示計算步驟,查找下一處錯誤。
更多財稅職場學習資訊,關注秀財網
秀財網
肯定是被公認為“萬金油”的VLOOKUP函數。
除了最基本的查找功能外,它還可以進行多條件查找、快速核對數據、分段歸類、多列查詢等延伸功能,實際應用場景特別多樣化。
DataHunter
每個都很厲害,看具體用途,沒有最強只有更強
藍色DE流光
lookup系列函數。vlookup,hlookup。
不能說最強大,只能說使用頻率最高。
強大的函數公式很多,比如:concat\\textjoin都是很強大的函數。
Excel講堂
說到最強大,得看你做的什麼方面的事情比較多而動態變化吧
但有一個,真是平時省時省力的好公式:單元格內容合併符號&
這個符號為平時很多表格製作帶來了方便,且我一般用它來代替很多其它的公式,好記憶又簡單易用