15組Excel常用公式,讓你秒變大神,收藏備用吧

2021-02-13 Excel從零到一

今天跟大家分享15個Excel函數公式的套路，如果遇到類似的問題，直接套用即可，話不多說我們開始吧

1. 查找重複值

公式：=IF(COUNTIF(A$2:A2,A2)=1,"","重複")

首選我們利用countif函數進行條件計數，然後使用if函數進行判斷當其結果等於1時代表不重複，當不等於1時候代表重複

2. 身份證號碼提取出生日期

公式：=--TEXT(MID(A2,7,8),"0-00-00")

首先利用mid函數提取出身份證號碼中的出生年月，然後利用text函數設置為日期格式，最後利用兩個減號，將文本格式轉換為日期格式，為什麼還需要轉換格式呢？因為text是文本函數，經過text函數轉換過的數據都是文本格式

3. 身份證號碼中提取性別

公式：=IF(MOD(MID(A2,17,1),2)=1,"男","女")

性別只與身份證號碼第17位有關係，當第17位為奇數則為男，為偶數則為女，所以我們利用mid函數提取第17位數字，然後利用mod函數判斷奇偶，最後利用if函數判斷

4. 身份證號碼中提取年齡

公式：=DATEDIF(B2,TODAY(),"y")

DATEDIF函數是一個隱藏函數，用於計算兩個日期時間差

5. vlookup進行多條件查找

公式：｛=VLOOKUP(E2&F2,IF({1,0},A2:A10&B2:B10,C2:C10),2,0)｝

第一參數：我們將查找值通過使用&字符合併為一項

第二參數：我們利用if函數和數組構建一個二維數組，如下圖所示

第三參數：2，在二維數組中得分在第二列，所以為2，

第四參數為0，為精確匹配

6. 使用vlookup進行反向查找

公式：=VLOOKUP(E2,IF({1,0},C2:C10,A2:A10),2,0)

我們都知道使用vlookup函數一般是從左往右查找，當我們想從右往左查找就要用到vlooup函數的反向查找，與多條件查找類似，都是構建二維數組進行查找

7. 隔行求和

公式：=SUMPRODUCT((MOD(ROW(C2:L7),2)=1)*C2:L7)

首先我們利用mod函數判斷為奇數列的行號，然後在利用SUMPRODUCT的數組特性進行求和

8. 隔列求和

公式：=SUMPRODUCT((MOD(COLUMN(B3:G12),2)=1)*B3:G12)

跟隔行求和類似，先判斷奇數列號，然後進行求和

9. 統計不重複個數

公式：=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))

首先利用countif條件計數，算出重複次數，然後利用1除以重複數，這裡為什麼用1除以呢，比如當數據出現兩次，countif函數就會算出兩個2，然後用1分別除以兩個2結果為1/2最後利用SUMPRODUCT函數，兩個1/2會相加得到1

10. 中國式排名

公式：=SUMPRODUCT(($B$2:$B$8>B2)*1)+1

使用SUMPRODUCT判斷成績大於它自己本身的數據，因為沒有等於所有加1

11，單條件計數

公式：=SUMPRODUCT((B2:B16=$G$3)*1)

首先判斷部門所在區域等於成型車間的單元格，當正確時返回true可以看做是1，錯誤時返回false可以看做是0，最後在結果後乘以1

12. 可見單元格求和

公式：=SUBTOTAL(109,B2:B10)

當SUBTOTAL的第一參數為100以上的時候，就會僅對可見區域求和

13. 雙向查找

公式：=INDEX(A1:E10,MATCH(G2,A1:A10,0),MATCH(H2,A1:E1,0))

利用match函數分別找到姓名以及科目所在的行列標號，然後利用index函數取出結果

14. 提取左邊的字符串

公式：=-LOOKUP(1,-LEFT(D2,ROW($1:$30)))

首先我們使用ROW($1:$30)構建一個1到30的序列數組，如果你的數據比較長可以適當增大，然後利用left函數對字符串提取30次，我們又在left函數前面添加了負號，而又在其前面添加了負號將提取數據轉換為數值當提取出來的數據為文本是會返回錯誤值，因為使用lookup函數函數默認是升序排列的所以函數會返回最後一個正確的值，而最後一個正確值恰恰是我們要提取的值，最後添加負號將負數轉換為正數

15. 提取右邊的數據

公式：=-LOOKUP(1,-RIGHT(A2,ROW($1:$30)))

這個跟從左提取數據是一樣的，只不過是將left函數換成了right函數

怎麼樣，這15個函數套路你你都知道嗎

我是Excel從零到一，關注我持續分享更多excel技巧

覺得有用，麻煩點個「在看」吧

👇👇👇

相關焦點

  • 常用Excel排名公式大全,再不收藏就是你的錯了
    但遇到不同的表格需要用不同的函數和公式，今天蘭色首次對排名公式進行一次全面的總結，同學們一定要收藏起來了！ 1、美式排名 2、中國式排名 3、多列排名 4、多表排名 5、組內美式排名 6、組內中國式排名 1、美式排名
  • excel函數公式應用:時間日期提取公式匯總,你用過哪些?
    今天老菜鳥針對上述在日常工作中經常會遇到的問題，總結了20個常用的關於日期和時間的公式，趕緊來看看吧！學習更多技巧，請收藏關注部落窩教育excel圖文教程。在實際工作中，經常需要進行日期和時間的計算，如：工作日天數、入職天數、合同到期日期、員工生日提醒、計算加班時間........
  • 15個excel常用函數,可直接套用,幾乎每天都用得到,收藏備用吧
    Hello.大家好，今天跟大家分享15個Excel函數公式，都是我們工作中經常用到的公式，工作中遇到類似的問題，直接套用即可，話不多說，下面就讓我們來一起學習下吧1.合併單元格求和公式：=SUM(C3:C14)-SUM(D4:D14)在這裡我們需要先選擇想要求和的區域，然後在編輯欄輸入公式，最後按Ctrl+回車批量填充公式5.
  • 常用Excel排名公式大全
    但遇到不同的表格需要用不同的函數和公式，今天蘭色首次對排名公式進行一次全面的總結，同學們一定要收藏起來了！1、美式排名2、中國式排名3、多列排名4、多表排名5、組內美式排名6、組內中國式排名1、美式排名有2個數字相同排名第1時，下一個名次直接是3而不是2=RANK(C3,C:C)2、中國式排名
  • 一組常用Excel函數公式,你肯定喜歡
  • 一組常用日期計算公式,收好備用
    小夥伴們好啊，今天你上班了嗎？咱們來分享一組常用的日期計算公式，收藏一下，說不定哪天就用上了呢。
  • excel函數公式:常用高頻公式應用總結(上)
    最近後臺的留言實在是太多了，由於時間關係，沒辦法一一給小夥伴們解答，所以這裡我們總結了小夥伴們問的最多的問題，整理出了10個職場人士最常用的excel公式，希望能幫大家排憂解難，趕緊來看看吧!今天總結了十個使用頻率很高的公式分享給大家，相信學會這十個公式，你也可以在職場上縱橫一番了。公式1：條件計數條件計數在Excel的應用中十分常見，例如統計人員名單中的女性人數，就是條件計數的典型代表。
  • Excel教程:10條工作中最常用的excel函數公式,速收藏!
    本篇教程，羅列了咱們常見的Excel常用電子表格公式和相應的案例提供給大家學習。TEXT函數是一個非常好用的函數，具體用法可以參考往期教程《Excel教程：最魔性的TEXT函數，看一眼就心動~》從輸入的身份證號碼內讓Excel自動提取性別，我們在C2單元格輸入公式：=IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,"男","女"),IF(MOD(MID(B2,17,1),2)=1,"男","女"))
  • 字符處理文本查找截取常用的10個excel函數公式,上班族職業必備
    ，不但包含省市信息，還包含區劃和路信息，要提取市名就要知道目標數據所處位置。>要從姓名中提取姓氏，由于姓名長度不同，分別有2/3/4三種長度，對應的姓氏長度也不同，分別對應提取1/1/2三種長度，所以利用LEN函數計算文本長度，再用LEFT函數藉助第二參數截尾取整的特性提取姓氏。
  • excel函數公式：常用高頻公式應用總結（上）
    最近後臺的留言實在是太多了，由於時間關係，沒辦法一一給小夥伴們解答，所以這裡我們總結了小夥伴們問的最多的問題，整理出了10個職場人士最常用的excel公式，希望能幫大家排憂解難，趕緊來看看吧! 今天總結了十個使用頻率很高的公式分享給大家，相信學會這十個公式，你也可以在職場上縱橫一番了。公式1：條件計數條件計數在Excel的應用中十分常見，例如統計人員名單中的女性人數，就是條件計數的典型代表。
  • excel函數公式應用：時間日期提取公式匯總，你用過哪些？
    今天老菜鳥針對上述在日常工作中經常會遇到的問題，總結了20個常用的關於日期和時間的公式，趕緊來看看吧！學習更多技巧，請收藏關注。如果用人工計算會非常麻煩，而使用Excel函數公式則非常簡單，今天給大家整理一期時間計算的公式套路大全，記得收藏起來慢慢看！（本教程涉及的公式都比較基礎，不做過多講解，需要哪個公式直接套用即可。）
  • 收藏 造價人最常用的Excel公式都在這了
    但是，excel功能雖多，我們造價人用的卻十分有限，下面小价就為大家總結了我們造價人最常用的excel公式，希望能讓大家在繁忙的工作中，提高效率，節省時間，少加班！三、統計公式1、統計兩個表格重複的內容公式:B2=COUNTIF(Sheet15!
  • Excel 中最值得收藏的10個函數公式,錯過1個讓你找半天
    蘭色今天精選出12個excel函數公式，雖然它們不並常用，但很實用。需要用時你還真不好搜到它們，所以趕緊收藏起來吧。 1、不重複個數公式 =SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7)) 2、提取唯一值公式 數組公式（按ctrl+shift+enter三鍵輸入，以下帶{}輸入方法相同） =IFERROR(INDEX
  • 6個常用Excel公式超全解析,直接套用工作效率翻倍,收藏備用
    你在用函數的時候還在慢慢地上網查詢嗎？下面為大家整理了職場最常用的10個函數，建議收藏哦！1. SUM函數在需要求和的地方輸入「=SUM（選中求和區域）」，在括號裡選中要求和的區域，點擊回車鍵就能完成。如果覺得比較麻煩也可以使用快捷鍵，首先選擇要求和的區域，然後按Alt+=就能一鍵完成批量求和，方便高效！
  • 財務必備:146個財務常用Excel表格模板,直接套用,建議收藏備用
    財務工作中，常常用到各種表格，例如：算工資、績效、對帳、報表、固定資產統計表等各種表格，哪個會計電腦桌面上沒有大量的表格呢！做Excel表格一定少不了的是什麼？今天小編給大家分享的是146個財務財務常用的Excel模板，包括出口退稅表、成本預算表、考勤、績效表、現金流量表、存貨盤點表、納稅申報表等，直接可以套用，建議你收藏起來，想用的時候直接調出來方便快捷。
  • 工作中最常用的Excel函數公式,幫你整理齊了!(建議收藏)
    今天要分享的資料是一份 Excel函數公式合集，非常實用，希望你們喜歡。我們都知道，在Excel中，學好Excel函數，可以讓你的工作效率翻倍，給你帶來意想不到的收穫。接下來，我羅列了咱們常見的Excel常用的10個電子表格公式和相應的案例提供給大家學習。
  • 人事常用的計算公式,90%都來自這份資料,趕緊收藏!
    人力資源部門作為企業一個重要部門有著其特殊的功能和意義，人力資源部門幾乎絕大多數的崗位都會與人的因素的數據有牽扯，掌握一定的公式及計算技巧就顯現得尤為重要。如我們常見薪資、福利的核算、社保繳納、招聘配置等都會涉及到一些excel函數公式以幫助我們提高工作效率。
  • excel函數公式:常用高頻公式應用總結(下)
    前兩天我們分享了5個職場人士最常用的函數公式，相信大家肯定沒看過癮。今天我們如約而至，繼續為大家分享後5個常用的函數公式。趕緊來看看吧~公式6：根據身份證號碼計算出生日期要從身份證號碼中得到出生日期，這種問題對於從事人資行政崗位的小夥伴一定不陌生，公式也比較簡單：=TEXT(MID(A2,7,8),"0-00-00")就能得到所需結果，如圖所示：
  • 【Excel技巧】49個Excel常用技巧|建議收藏
    在微信平臺回復「特殊符號」可以查看更多符號輸入方法excel如何去掉地址欄的名稱，excel2003版，插入菜單 - 名稱 - 定義 - 在彈出的窗口找到該名稱 點刪除按鈕 ，2010版，公式選項卡 - 名稱管理器 -找到名稱點刪除在excel裡，如何隱藏公式，讓公式別人看不到呢?答：在excel裡隱藏公式是通過設置單元格格式設置的。
  • 不知道這些excel操作技巧,你就輸了!第二彈
    昨天小編發布了一篇excel操作技巧，收到了大家的熱烈反響！於是小編趁熱打鐵，今天瘋狂整理出了第二篇！這一篇講的主要是excel中超實用的公式。大家要記得，看看、收藏都是學不會excel的，最重要的是實踐！