很多人都知道Excel函數(shù)公式功能強(qiáng)大，

但是遇到問題就寫不出公式了。

現(xiàn)在找不到了。

我今天給你帶了15個

很多人都在尋找的Excel公式，

我已經(jīng)為大家做好了未雨綢繆的準(zhǔn)備。

看了感覺不錯的，

記得好好看看底部，分享給朋友。

01

下圖是某公司員工刷卡考勤的一些記錄。日期時間組合后的數(shù)據(jù)根據(jù)B欄刷卡日期和C欄刷卡時間獲取..

在D2單元格中輸入下面的公式，復(fù)制到D10單元格中，得到日期和時間組合后的數(shù)據(jù)。

=B2+C2

02

計算故障處理時間

下圖是某運(yùn)營商寬帶故障修復(fù)報告單的一部分，故障處理時間要根據(jù)C列接單時間和E列處理時間來計算..

在單元格F2中輸入以下公式，并將其復(fù)制到單元格F6。

=INT((E2-C2)*1440)

一天有1 440分鐘。要計算兩個時間間隔之間的分鐘數(shù)，只需從結(jié)束時間中減去開始時間，然后乘以1 440。最后用INT函數(shù)剔除計算結(jié)果中小于一分鐘的部分，計算出時長的分鐘數(shù)。

如果需要計算兩個時間間隔之間的秒數(shù)，可以使用下面的公式。

=(E2-C2)*86400

一天有86 400秒，所以用結(jié)束時間減去開始時間來計算秒數(shù)，然后乘以86 400。

此外，使用TEXT函數(shù)可以以文本格式將兩次之間的間隔返回為一個數(shù)字。

以下公式返回兩輪之間的小時數(shù)。

=TEXT(E2-C2，"[h]")

以下公式返回回合之間的分鐘數(shù)。

=TEXT(E2-C2，"[m]")

以下公式返回兩次舍入之間的秒數(shù)。

=TEXT(E2-C2，"[s]")

03

計算員工的在職時間

下圖是某企業(yè)員工加班出勤的一些記錄。需要根據(jù)C欄打卡時間和D欄打卡時間計算員工的加班時間..

如果用公式“=D2-C2”來計算E2單元格中的時差，由于部分員工的出發(fā)時間是第二天凌晨，出發(fā)時間小于到達(dá)時間，兩者相減得到負(fù)數(shù)，計算結(jié)果將是錯誤的。

正常情況下，員工值班時間不會超過24小時。如果打卡時間大于打卡時間，說明兩次在同一天；否則，這意味著下班時間是第二天。

在單元格E2中輸入以下公式，并將其復(fù)制到單元格E10。

=中頻(D2 >；C2、D2-C2、D2+1-C2)

中頻功能判斷D2單元的時鐘輸入時間是否大于C2單元的時鐘輸入時間，如果條件成立，則從時鐘輸入時間中減去時鐘輸入時間。否則，下班時間加1得到第二天的時間，再減去工作時間。

該公式也可以簡化為:

=中頻(D2 >；C2、D2、D2+1)-C2

也可以借助MOD函數(shù)計算余數(shù)。

=MOD(D2- C2，1)

用D2電池的非工作時間減去C2電池的工作時間，然后用MOD函數(shù)計算結(jié)果除以1的余數(shù)，返回的結(jié)果是忽略天的時差。

04

計算員工技能評估的平均時間

下圖是某企業(yè)員工技能鑒定表的部分?jǐn)?shù)據(jù)。B欄是文字記錄的員工運(yùn)營時間，需要計算員工的平均運(yùn)營時間。

將D2單元格格式設(shè)置為“時間”，然后輸入

降低數(shù)組公式，按組合鍵，計算結(jié)果為“0:01:12”。

{= sum (-text ({"at 0 "，" at 0:00 " } &；B2:B10，

“h:m:s；；；！0"))/9}

因為B列的時間記錄是文本內(nèi)容，Excel

無法直接識別和計算。

使用字符串“{”0，“^ 0:0”}”和B2:B10

圖13 -17技能考核平均由時間網(wǎng)格的內(nèi)容連接，變成九行兩列的內(nèi)存數(shù)組“{”0:1:18，“0:0:1:18“；”0: 59", "0: 00: 59";…; "0: 1: 27", "0: 0: 1: 27"} ".

Excel將“0: 00: 00”的文本字符串識別為時間，仍然將“0: 00”、“0: 00”和“0: 00”的字符串識別為文本。

TEXT函數(shù)的第二個參數(shù)使用“h:m:s；；；！0”，時間樣式字符串轉(zhuǎn)換為“h:m:s”樣式，非時間樣式文本字符串強(qiáng)制顯示為0。計算結(jié)果如下。

{"0:1:18","0";"0","0:0:59";…;"0:1:27","0"}

text函數(shù)計算的結(jié)果仍然是text，加上兩個負(fù)號，即負(fù)數(shù)的負(fù)數(shù)為正數(shù)，通過減負(fù)運(yùn)算將TEXT結(jié)果轉(zhuǎn)換為時間序列值。

最后將sum函數(shù)的SUM結(jié)果除以總?cè)藬?shù)9，得到平均考核時間。

05

從混合內(nèi)容中提取時間和日期數(shù)據(jù)

從考勤機(jī)導(dǎo)出的信用卡記錄通常包含日期和時間。如下圖所示，需要分別從C列和D列提取B列信用卡記錄中的日期和時間。

由于時間和日期數(shù)據(jù)的本質(zhì)都是序列值，所以同時包含日期和時間的數(shù)據(jù)可以視為帶小數(shù)的數(shù)值。其中，整數(shù)部分是表示日期的序列值，小數(shù)部分是表示時間的序列值。

使用以下公式提取C2單元格中的日期數(shù)據(jù)。

=整數(shù)(B2)

=TRUNC(B2)

用INT函數(shù)或TRUNC函數(shù)提取a列的整數(shù)部分，結(jié)果是代表日期的序列值。

以下公式可用于提取D2單元中的時間數(shù)據(jù)。

=B2-INT(B2)

=MOD(B2，1)

用MOD函數(shù)計算A2單元格除以1的余數(shù)，得到A2值的小數(shù)部分，結(jié)果是代表時間的序列值。如果結(jié)果顯示為十進(jìn)制，單元格格式可以設(shè)置為時間。

此外，還可以使用TEXT函數(shù)提取日期和時間，下面的公式可以提取a列中的日期。

= - TEXT(A2，“e-m-d”)

格式代碼使用“e-m-d”，即“年-月-日”。

下面的公式可以提取a列的時間。

= - TEXT(A2，“h:m:s”)

格式代碼使用“h:m:s”，即“小時:分鐘:秒”。

06

將英文月份轉(zhuǎn)換為月份值

如下圖所示，A列是英文的月份名稱，需要轉(zhuǎn)換成B列對應(yīng)的月份值..

在B2單元格中輸入以下公式，并將其復(fù)制到B10單元格。

= MONTH(A2 & amp；1)

使用連接線“&”將單元格A2與數(shù)值“1”連接，得到一個新的字符串“Apr1”，該字符串成為系統(tǒng)可識別的文本類型日期樣式，然后使用MONTH函數(shù)提取日期字符串中的月份。

年、月和日函數(shù)都支持?jǐn)?shù)組計算，并廣泛用于按時間段進(jìn)行統(tǒng)計匯總。

07

匯總特定時間段的銷售額

下圖是某公司2017年的部分銷售記錄表。A列是業(yè)務(wù)發(fā)生日期，D列是業(yè)務(wù)發(fā)生金額。需要計算上半年的業(yè)務(wù)總額。

您可以使用以下公式完成總結(jié)。

= SUMPRODUCT((MONTH(A2:A13))& lt；7)*D2:D13)

MONTH函數(shù)返回單元格A2:A13中日期數(shù)據(jù)的月份值，結(jié)果是:

{1;3;6;2;6;3;4;6;7;8;6;11}

因為要計算1-6月的總業(yè)務(wù)量，所以需要判斷月值是否小于7。

使用“月(a2: a13)”

08

匯總指定年份和月份的銷售額

下圖顯示了一家公司的部分銷售表。營業(yè)日期分布在不同的年份，銷量要按年、月匯總在G欄、H欄。

在G3單元格中輸入以下公式，復(fù)制到G3:H14單元格區(qū)域。

= sum product((YEAR($ A $ 2:$ A $ 746)= G $ 2)*(MONTH($ A $ 2:$ A $ 746)= $ F3)* $ C $ 2:

$ c $ 746)“YEAR($A:$A6)= G”是指使用YEAR函數(shù)計算“$ A $ 2:$ A $ 746”單元格的年份，并確定它是否等于“G $ 2”單元格指定的年份值。

“MONTH($A:$A6)=$F3”部分意味著使用MONTH函數(shù)分別計算“$A:$A6”單元格的月份，并確定它是否等于“$F3”單元格指定的月份值。

將兩組邏輯值相乘。如果對應(yīng)的位置都是邏輯值TRUE，則相乘后的結(jié)果為1；否則，它返回0。

用“$C:$C6”單元格的銷售額相乘，通過SUMPRODUCT函數(shù)返回產(chǎn)品的總和。

使用該公式時，需要注意使用不同單元格引用方法的變化。其中日期范圍“$A: $A6”和銷售范圍“$C:$C6”是絕對引用，而年份條件的“G”使用列相對引用和行絕對引用，月份條件的“$F3”使用列絕對引用和行相對引用。

09

日期和時間數(shù)據(jù)的組合

下圖是某企業(yè)新生產(chǎn)線設(shè)備安裝調(diào)試計劃表。每個項目的天數(shù)需要根據(jù)開始日期和結(jié)束日期來計算。

在D2單元格中輸入以下公式，將單元格格式設(shè)置為普通，然后將其復(fù)制到D6單元格。

=天數(shù)(B2 C2)+1

公式也可以寫成:

=C2-B2+1

在實際應(yīng)用中，直接減去兩個日期計算差異天數(shù)更方便。

10

如下圖所示，將A2單元格的格式設(shè)置為自定義格式“Day 0”，用下面的公式返回當(dāng)前系統(tǒng)日期是今年的那一天。

= TODAY-“1-1”+1

在Excel中以“月-日”的形式輸入日期，系統(tǒng)默認(rèn)將其作為當(dāng)年。“TODAY()-”1-1“是指從系統(tǒng)當(dāng)前日期減去今年1月1日，再加一天得到今年的日期。

同樣，下面的公式可以用來計算今年有多少天。

="12-31"-"1-1"+1

如果公式引用了包含日期或時間的單元格，Excel可能會自動將公式所在單元格的格式更改為日期或時間，然后可以根據(jù)需要重新調(diào)整單元格格式。

11

確定指定日期是季度的哪一天

如下圖所示，需要根據(jù)a列的日期計算出季度的日期。

在B2單元格中輸入以下公式，并將其復(fù)制到B10單元格。

=COUPDAYBS(A2，“9999-1”，4，1)+1

此函數(shù)是一個財務(wù)函數(shù)類別，用于返回從付息期開始到結(jié)算日的天數(shù)。

該函數(shù)的基本語法如下。

COUPDAYBS(結(jié)算、到期、頻率、[基礎(chǔ)])

第一個參數(shù)結(jié)算是證券的結(jié)算日期；第二個參數(shù)到期是證券的到期日，可以寫成任意大的日期序列值；第三個參數(shù)frequency使用4，這意味著每年的利息支付是按季度支付的。第四個參數(shù)依據(jù)使用1，這意味著日期是根據(jù)實際天數(shù)計算的。

在這個例子中，中年的利息支付次數(shù)是按季度支付的，因此單元格A2的日期所在的季度的利息支付期是該季度的第一天。公式以單元格A2的日期為結(jié)算日期。通過計算從季度的第一天到當(dāng)前日期的間隔天數(shù)，結(jié)果增加1，指定的日期是季度的第一天。

12

下圖是某公司新員工入職表單的部分記錄。入職日期要根據(jù)入職日期和實習(xí)月數(shù)來計算。

在D2單元格中輸入以下公式，并將其復(fù)制到D10單元格。

=EDATE(B2、C2)

EDATE函數(shù)使用B2單元格中的日期作為指定的開始日期，并返回C2單元格指定的月份之后的日期。

13

下圖是某公司的部分店鋪租賃表，需要根據(jù)租賃起止日期計算租賃月數(shù)。

如果在F2單元格中直接使用下面的公式來計算間隔月份，并將公式復(fù)制到F9單元格中，則會在某些單元格中得到錯誤的結(jié)果，例如圖13-54中的F2、F3、F4和F8單元格。

=DATEDIF(D2，E2，“m”)

當(dāng)使用DATEDIF函數(shù)計算間隔月份時，如果結(jié)束日期是當(dāng)月的最后一天，并且開始日期的天數(shù)大于結(jié)束日期的天數(shù)，則計算結(jié)果將少一個月。

根據(jù)這個規(guī)則，可以在原始公式中添加判斷條件。在G2單元格輸入下面的公式，復(fù)制到G9單元格，公式會返回正確的結(jié)果。

=DATEDIF(D2，E2，“m”)+AND(DAY(D2))>;日(E2)，E2 =月(E2，0))”日(D2)>;DAY(E2)"用于判斷開始日期的天數(shù)是否大于結(jié)束日期的天數(shù)。

“E2=EOMONTH(E2，0)”用于判斷結(jié)束日期是否是當(dāng)月的最后一天。

當(dāng)開始日期的天數(shù)大于結(jié)束日期的天數(shù)，并且結(jié)束日期等于當(dāng)月的最后一天時，“與”函數(shù)返回邏輯值“真”，否則返回邏輯值“假”。

最后，將DATEDIF函數(shù)的結(jié)果與AND函數(shù)返回的邏輯值相加。在四個運(yùn)算中，邏輯值TRUE的函數(shù)等價于1，邏輯值FALSE的函數(shù)等價于0。如果兩個條件同時成立，則相當(dāng)于原公式+1，否則為原公式+0。

14

下圖是某公司新員工的一些記錄。需要根據(jù)入職日期計算員工本月的出勤天數(shù)。

在C2單元格中輸入以下公式，并將其復(fù)制到C8單元格中。

=網(wǎng)絡(luò)日(B2，月(B2，0))

EOMONTH(B2，0)用于計算員工受雇月份的最后一天。

NETWORKDAYS函數(shù)將雇用日期作為開始日期，將雇員受雇月份的最后一天作為結(jié)束日期，并計算兩天內(nèi)的工作日數(shù)。

在本例中，省略了第三個參數(shù)。在實際應(yīng)用中，如果這個月還有其他法定節(jié)假日，可以用第三個參數(shù)排除。

15

判斷評估等級

下圖顯示了員工評估評分表的一部分。F3:G6單元格區(qū)域為考核等級對照表。第一列已按等級升序排序。要求根據(jù)D欄的考核等級找出相應(yīng)的等級..

在D2單元格中輸入以下公式，并將其復(fù)制到D11單元格。

=VLOOKUP(C2，F(xiàn) $ 3:G $ 6.2)

省略VLOOKUP函數(shù)的第四個參數(shù)，以近似匹配方式返回查詢值的精確匹配值或近似匹配值。如果沒有找到完全匹配，則返回小于查詢值的最大值。

C2單元格的62分沒有列在對照表中，所以Excel在F列中查找最大值60小于62進(jìn)行匹配，返回G列對應(yīng)的“合格”等級。

希望這篇文章能幫到你！

記不住的話可以發(fā)朋友圈自己標(biāo)注。

上圖來自

北京大學(xué)出版社出版

Excel 2016函數(shù)和公式的應(yīng)用