常用Excel函數(if+choose+VLOOKUP+index+sumif+sumifs+身份證號碼相關函數)

①【IF函數】

=IF（判斷條件，符合條件時返回啥結果，不符合條件返回啥結果）

🌰：=if（B2=C2，「✓」，「×」）

②【choose函數】

排在第三個的同學，給我站出來

=choose（指定位置，隊伍方陣）

🌰=choose（F1，「張三」，「李四」，「王二麻子」，「趙六」）

【補充choose函數】是選擇函數。

choose（索引，值，值……）

索引是從1開始的，不能是小數，也不能是負數。

值……就是集合，

choose函數可基於指定索引值得出值集合中的某一個值。

🌰：

索引：2

值：鼠牛虎兔龍蛇馬羊猴雞狗豬，

得到「牛」。

🌰：

索引1就是星期一，

索引2就是星期二，

索引3就是星期三，

索引2.9，不會四捨五入，還是星期二，

索引5，超出範圍，就會報錯。

索引0，索引－1，都會報錯。要從1開始。

③【VLOOKUP函數】

=VLOOKUP（找什麼，在哪裡找，找到後要它身上哪塊東西，是準確找到還是近似找）

🌰=VLOOKUP（找什麼找e3張三，哪兒找B1：C6，要什麼2從左到右第二個數，準確找還是近似值0）

=VLOOKUP（E3，B1：C6，2，0）

已知姓名張三，VLOOKUP查找得出部門財務部。

④【index函數】

站在第3排第2列的那個同學，給我站出來。

=index（隊伍方陣，第3排，左數第2列）

=index（B2：C6，3，2）

【補充】

=index（指定的區域，返回第幾行的數據，返回第幾列的數據）

=index（選擇區域，長虹在這個選中表格裡的第3行，在這個表格裡的第2列）

=index（B4：E8，3，2）

【再補充一個🌰】

在一個指定的區域當中，獲取指定位置的單元格內容。

=index（從哪裡獲取數據A2：A14，返回第5行的數據），逗號都是英文的逗號。

=index（A2：A14，5）=拉登，

也可以=index（A2：A14，C2）=拉登，因為C2單元格裡面是5，所以這就引出來了下一個隨機抽獎。因為你這個C2裡面的數字是4，是5，後面D2出來的就是它所對應的名字了。

♛【randbetween函數】隨機抽獎用。

=randbetween（最小值1，最大13個人）

C2=randbetween（1，13）

D2=index（A2：A14，C2）

按F9鍵，讓公式自動計算，就可以隨機返回一個值，這樣就能完成抽獎名單的表格。

【再來一個🌰】

要求把下面表格中標黃的單元格，填到右側表格的商品信息中。

怎麼做呢？【思路：先找到標黃的單元格之間的規律，標黃的單元格在左側表格的第2行，第5行，第8行等等。】

規律就是2=2+0×3

5=2+1×3

8=2＋2×3

11=2+3×3

所以得出index函數裡的返回值（2+（G2－1）×3），

把2放到後面，改為（G2單元格－1）×3＋2），

接下來，外面套上一個index函數。

=index（B：B也就是從B列中提取，（G2－1）×3＋2）

⑤【sumif函數】

=sumif（條件區域，指定的條件，求和區域）

=sumif（B2：B9條件區域是各區域名稱會有重複，E3指定的條件是東北，C2：C9求和區域是各區域的銷售額）

⑥【sumifs函數】

=sumifs（求和區域，條件區域1，指定條件1，條件區域2，指定條件2）

sumifs跟sumif的題目表格比，中間多了一列「付款方式是現金還是支票。」

🌰=sumifs（求和區域D2：D9是各區域的銷售額，條件1B2：B9是各區域會有重複，指定條件F3東北，條件2C2：C9，指定條件2G3支票）

=sumifs（D2：D9，B2：B9，F3，C2：C9，G3）

⑦【關於身份證號碼的秘密】（出生日期：mid函數和text函數＋年齡：今年年份減去mid函數＋性別：if函數和mod函數和mid函數＋籍貫：VLOOKUP和left函數＋生肖屬相：choose和mod和mid函數）

♛♛♛1.出生日期。

♛【mid函數】

=mid（你要提取誰，你從第幾位開始提取，你要提取幾位）

這個太簡單了，身份證提取八位數出來，所以要把它設置成年月日出來，就引出【text函數】

♛【text函數】

=text（你要對哪個數值進行改變格式，你要改變成什麼樣的格式）

0000年00月00日，因為這個是文本格式，所以要用英文的半角引號。

兩步合一步，把text函數裡的B2換成mid函數，得到=text（mid（身份證號碼那一個單元格，從第7個數開始取，取8位數），「0000年00月00日」）

♛♛♛2.年齡。【思路就是今年的年份減去出生日期的年份】

♛【mid函數】

先把身份證的年份提取出來，用mid函數=mid（A2，第7位開始提取，取4位數）=mid（A2，7，4）

♛【mid函數】

再用今年年份2019/2020/2021減去剛剛的mid函數。=2021－mid（A2，7，4）

♛♛♛3.性別

【就是第17位，也就是倒數第2位，偶數是女性，奇數是男性。】

♛【mid函數】

首先用mid函數把第17位提取出來。=mid（A2，第17位開始提取，只提取1位）

=mid（A2，17，1）

然後再引出邏輯函數if函數，這個數是奇數就是男性，否則是女性。所以這裡難點就是怎麼判定是奇數還是偶數。可以用除以2算餘數的方式：偶數除以2，餘數=0，奇數除以2，餘數=1。這個用mod函數。

♛【mod函數】

=mod（被除數，除數）=mod（E2也就是剛剛用mid函數提取出來的第17位數，2除數是2就寫2）=mod（E2，2）

再把第一步的mid函數嵌套在mod函數裡，得到=mod（mid（A2身份證那一格，17第17位開始取數，1提取1個數），2除數為2）=mod（mid（A2，17，1），2）

♛【if函數】

第三步就是用if函數了。=if（條件=1，餘數為1就是「男」，否則就是「女」）=if（mod（mid（A2，17，1），2）=1，「男」，「女」）

【補充：在Excel裡，1代表成立，0代表不成立的意思。所以上面if函數可以簡化，這個條件=1的「=1」可以不寫。】

=if（mod（mid（A2，17，1），2），「男」，「女」）

♛♛♛4.籍貫。

（就是你的出生地在哪裡，包括省市區。主要看身份證號碼前六位。前兩位是省份，第三四兩位是所在地城市市區，第五六位是所在區縣）（有個地區編碼，是網上可以搜到的，有好幾千行。）

首先第一步是提取前六位，不用mid函數，用更方便的函數left函數。

♛【left函數】

=left（A2身份證號碼那個單元格，從左到右提取6位）

=left（A2，6）

把身份證號碼前六位提取出來以後，就引出查找函數VLOOKUP函數。

♛【VLOOKUP函數】

=VLOOKUP（查找對象也就是剛剛提前出來的前六位，查找區域選擇地區編碼那個表格，返回第幾列選擇返回第二列，精確匹配0）

=VLOOKUP（left（A2，6），地區編碼!A：B，2，0）

♛♛♛5.生肖屬相

（有點難）

鼠牛虎兔龍蛇馬羊猴雞狗豬

引出choose函數，查找引用函數。

⑴先用mid函數把年份提取出來。

=mid（A2，7，4），【把文本改成常規】

⑵用這個提取出來的年份減去1888，這個1888年是鼠年是自己取數的，你也可以不用1888年，你只要選出一個比你現有員工年份小的年份就好。

=mid（A2，7，4）－1888【把文本設置成常規】

⑶再用這個數字去除以12，是算他的餘數是多少，用mod函數。

=mod（除數mid（A2，7，4）－1888，被除數12）=餘數1，但是實踐表明這個數算出來跟實際會相差一位數，所以再加上1，得到=mod（除數mid（A2，7，4）－1888，被除數12）+1，得到餘數2。【把文本改成常規】

⑷用choose函數

=choose（mod（除數mid（A2，7，4）－1888，被除數12）＋1，「鼠」「牛」「虎」「兔」「龍」「蛇」「馬」「羊」「猴」「雞」「狗」「豬」

【補充一個課外話題：】

生僻字用拼音怎麼打？搜狗輸入法－u－生僻字分拆－🌰：ushanruo－嵶，urenxiao－尒。

【補充：Excel製作打鉤按鈕】

①首先在工具欄空白處點擊滑鼠右鍵。

②選擇自定義功能區。

③勾選【開發工具】【確定】

④點擊開發工具下的插入，選擇複選框，再脫放到對應的單元格中。

⑤然後把文字修改成需要的文本。

⑥按Ctrl ＋shift鍵，就可以水平拖動複製。