這份面試題,有8道題目,文末給出完整的數據下載。先來看看你會做幾道題目?

【題目1】將以下左表和右表信息合并到一個表里

如圖所示,兩張表的表頭完全一樣,但有部份內容缺失,需要合并到一張表里讓內容更加完整。

可以用選擇性粘貼功能,并勾選“跳過空單元”選項,確定即可。具體操作如下:

【題目2】將下表抵押權人的內容包含"長安"的單元格置換為"長安保險公司"

如果我們用替換功能把“長安”替換成“長安保險公司”,則得到的結果如下:

原來的“長安”兩字替換成“長安保險公司”,但后面的“保險公司(寧波分公司)”還是存在。

題目的要求是包含“長安”這兩個關鍵字的整個單元格替換成“長安保險公司”,而不是部份文字變動。而且“長安”這兩字可能位于開頭,也可以是中間或者是結尾,這兩個關鍵字的位置不確定。

此時我們應該用通配符來進行模型查找包含“長安”關鍵字的單元格。通配符有兩個,它們分別是:

(1)星號(*),可以代替任意字符,可以單個,多個或者是沒有字符。

(2)問號(?),可代替任意單個字符。

本題我們用星號通配符比較合適,選中抵押權人這一列里的內容,Ctrl+F 快捷鍵打開【查找和替換】對話框。

在“查找內容”框里輸入“*長安*”,在“替換為”對話框里輸入“長安保險公司”,再點擊【全部替換】。

得到最終結果如下:

【題目3】將下表按照加盟商、省份、地區以及還款日期順序進行升序排序

需求是按四個條件排序,依次優先排序加盟商、省份、地區,最后還款日期。

單擊表格內任意一單元格,然后點擊【開始】-【排序與篩選】-【自定義排序】,然后依次添加加盟商、省份、地區,最后還款日期。具體操作如下:

四個條件排序后,結果如下圖所示。先升序排列加盟商;相同加盟商的按省份升序排列;相同加盟商,省份的,按地區升序排列;相同加盟商,省份,地區的,按還款日期升序排序。

【題目4】將下表空缺信息批量填充,并把加盟商為螞蟻**的客戶金額乘以? ?10000

第一個要求是不連續沒有規律的空白單元格批量填充,首先用到定位功能把所有空白單元格選中。

選中整張表格。在【查找和選擇】下拉列表中選擇【定位條件】,也可以使用快捷鍵F5,彈出【定位】對話框。

點擊左下角的“定位條件”按鈕,彈出的【定位條件】窗口。然后對定位條件進行一系列的設置,在本次需求中我們要定位出空白單元格,所以選“空值”。

定位出空白單元格后,在編輯欄里也就是寫公式處輸入“未知”,同時按下“ctrl+enter”,具體操作如下:

如果希望在同列中空白的單元格是按照上一行的值填充,在編輯欄里也就是寫公式處輸入“=E45”,同時按下“ctrl+enter”,得到需要的結果。

第二個要求為“把加盟商為螞蟻**的客戶金額乘以10000”,解題的思路是先把符合要求的加盟商找出來,用IF邏輯判斷函數,如果符合要求就客戶金額乘以10000。其語法是:

在本例中,公式的第一個參數為加盟商=“螞蟻**”,判斷是否滿足這一條件,如果滿足就是TRUE,則第二參數為“客戶金額乘以10000”,如果不滿足為FALSE則第三參數為原來的客戶金額值。

最終公式為:

=IF(B45="螞蟻**",C45*10000,C45)

得到的結果如下:

【題目5】根據身份證號提取性別和出生年月并計算年齡

這道題有三個要求,分別是提取性別,出生年月和計算年齡,先看第一個需求。

需求一:解題思路為身份證倒數第二位是偶數為女性,是奇數為男性。

第一步:身份證一般是18位,用MID函數提取身份證的倒數第二位也就是順數的第17位,第一參數要提取字符的文本字符串,第二參數是從文本中要提取的第一個字符的位置,第三參數是從文本中返回字符的個數,語法和具體應用如下:

在本例中的公式為:

=MID(C64,17,1)

表示在C64填寫身份證這一單元格中,從第17位開始提取,提取出1個字符,就是我們所需要的倒數第二位。

第二步:用ISEVEN判斷MID函數提取出來的數據是否為偶數,如果參數 number 為偶數,返回 TRUE,否則返回 FALSE,語法和具體應用如下:

在本例中的和MID一起組合成的公式為:

=ISEVEN(MID(C64,17,1))

第三步:使用邏輯函數 IF判斷ISEVEN返回的值,邏輯函數 IF的用法參考上一題。最后三個公式嵌套使用,具體如下:

需求二:解題思路為身份證第7至12位是表示年月。

第一步:同樣可以用MID函數提取出年月,但提取出來是文本格式,再用text函數設定顯示的格式,最后轉換為數值格式以方便后續的日期運算。

MID函數的應用和第一個需求一樣,寫上MID函數從身份證里提取出年月,得到的結果是文本“196512”,具體應用如下:

=MID(C63,7,6)

第二步:用TEXT函數,第一參數是要更改的內容,第二參數是要更改內容的顯示格式,得到的結果由原來的“196512”變成“1965-12”,注意此時的“1965-12”仍然是文本格式,只是有了日期的樣子,還不是真正的日期格式。

本例公式如下:

TEXT(MID(C63,7,6),"0-00")

第三步:在TEXT函數前加兩個短橫線,這種形式叫做減負運算,負負得正,將文本格式變成真正的日期格式。此外如果將兩個短橫線 “--”? 換成“1*”,也可以實現文本模式向數值格式的轉換,具體公式如下:

=--TEXT(MID(C63,7,6),"0-00")=1*TEXT(MID(C63,7,6),"0-00")

需求三:解題思路為現在的日期減去出生日期為年齡。

從第二步可以提取得到出生日期,用到以下兩個函數可以得到年齡,一個是TODAY函數,括號里沒有參數,返回的是當前日期,如在單元格里輸入公式=TODAY(),則得到“2020/9/19”。DATEDIF的第一參數是開始時期,第二參數是結束日期,第三參數是返回的一段時期內的整年、月或天數,具體的是語法和應用如下:

本例的公式為:

=DATEDIF(E63,TODAY(),"Y")

最終得到的結果:

【題目6】利用員工姓名查找標黃部分相關信息,不能使用輔助列。

根據員工姓名,得到相對應的工作部門,住宅電話,公司電話,手機等信息,由于VLOOKUP只能從左到右查找,要求不能使用輔助列,因此我們用MATCH和INDEX函數組合查找,這兩個函數的語法如下:

MATCH函數的第一參數是要查找的內容,本例中要找的內容是姓名所在的單元格即I75,第二參數是查找的區域也就是所有姓名所在區域,即C72:C88,第三參數是精確查找一般填0。MATCH函數最終返回的值是查找的內容也就是姓名在表格里第幾行,最終的值為1,2,3……

INDEX函數的第一參數單元格區域或數組常量,本例中就是整張表格的區域,第二參數是選擇數組中的某行,如姓名 “李項”在整張表的第1行。第三參數是選擇數組中的某列,如工作部門在整張表的第1列,最終得到當姓名是李項時,工作部門會顯示“運營部”。

本例的最終公式為兩個函數嵌套使用,具體如下:

當姓名固定不變時,住宅電話在整張表的第3列,所以INDEX函數的第三參數變成3表示在整個表格中,第1行第3列的值就是李項的住宅電話,同樣的道理依次把INDEX函數的第三參數變成4,5,就可得到公司電話,手機,具體如下:

=INDEX(B72:F88,MATCH(I75,C72:C88,0),3)
=INDEX(B72:F88,MATCH(I75,C72:C88,0),4)
=INDEX(B72:F88,MATCH(I75,C72:C88,0),5)

最終的結果如下圖所示:

【題目7】將以下數據有錯誤值的單元格標為紅色、如何判斷身份證、手機號是否準確,對于該類問題出錯如何避免?

以上的錯誤值分別為#DIV/0!和#N/A,可以使用條件格式標出來,選中要進行條件格式設置的區域,【開始】-【條件格式】-【新建規則】,然后具體操作如下:

由于excel里最多只能錄入15個字符,超過15個字符會使用科學記數法,而身份證大多數情況下是18位,因此在輸入身份證時先要把單元格設置成文本格式。

選中需要輸入身份證的單元格區域,單擊鼠標右鍵,在彈出的快捷菜單中選擇“設置單元格格式”,然后選擇文本,點擊確定。

也可以先在單元格打上英文狀態下的一撇“? '? ”,然后再接著輸入身份證,設置成功后左上角會有一個綠色的小三角形,如下所示:

身份證一般是15位或18位,電話號碼是11位,不能有重復值,當輸入的信息位數不夠或超出或有重復時會彈出一個對話框警告提示,可以避免輸錯,此時用到數據驗證功能。

具體的設置如下,選中要輸入身份證的區域,【數據驗證】,打開以下對話框并像以下這樣設置好設置好內容:

輸入的公式具體為:

當身份證的長度是是15位或者18位并且沒有重復值時為TRUE,以上的兩個條件有其中的一條不滿足時,則會彈出警告窗口。

最終的效果如下:

同樣的道理,手機號碼只有11位,公式修改為:

=AND(LEN(D102)=11,COUNTIF($D$102:$D$110,D102)=1)

則可得到同樣的結果。

【題目8】根據表一中的信息,將表二中的產品名稱補充完整

按照某一條件查找匹配其他內容,通常用到 VLOOKUP公式,其語法如下:

?第一參數你想要查找的內容,第二參數是要查找的位置,第三參數包含要返回的值的區域中的列號,第四參數返回近似或精確匹配-表示為 1/TRUE 或 0/假)。而在本例中借款人可能有相同的名字,如有兩個人的名字都叫白濤,但車牌是唯一的,因此公式可以寫為:

=VLOOKUP(F154,$D$144:$F$151,2,0)

最終結果為:

【總結】

這一套面試題,在考察面試者熟練應用Excel函數、條件格式等解決實際工作中的問題。

用選擇性粘貼合并表格,使用通配符進行模糊查找替換,進行多個關鍵字排序,利用ctrl+enter批量填充。

用條件格式,數據驗證可以快速顯示異常值及避免輸入錯誤信息。

各個函數的應用:

IF

邏輯判斷

MID

提取文本信息

ISEVEN

判斷是否是偶數

TEXT

顯示規定格式

DATEDIF

計算兩個日期間隔的年,月,日等

INDEX

在指定區域內返回幾行幾列所在的值

MATCH

查找出指定內容在第幾行

VLOOKUP

根據指定值匹配內容