Excel中的日期運算
Excel中的日期、時間計算問題總是會讓人感覺到頭疼,不過涉及到時間和日期的應用也非常的廣泛,比如說上班的考勤,合同簽訂的有效期,涉及賠款的時間等等,都會有運算。
我們都知道在Excel中關於時間的函式有不少,比如Date, Datedif, Month, Year, EOMONTH等等,但是今天要給大家分享的不是這些,而是在Power Query中關於日期的運算函式。
日期和時間在Power Query中專門有個工具,用它直接就可以得到非常多的結果,隨便用一列時間來舉例,如:
在Power Query的新增列中就可以看到專門的模組:
從下拉選項中可以得到非常多的結果,比如第一個選項:年限。選擇之後看看結果:
這是怎麼計算得出的呢?透過公式欄中的公式得知:這個是用當前系統的時間與前面的時間相減得出來的,而且顯示方式是:天,時,分,秒。完成之後,再次透過選單欄中的持續時間工具可以提取出更多有效資訊。
這個如果大家感興趣,可以自行舉例看看每個功能是怎麼計算出來的,以便日後可以隨時呼叫,本次我們用一個案例來說一下可以應用的地方,肯定有小夥伴會說,直接用函式唄,但是函式是一個手動的方法,也就是說未來有資料的更新,你需要再次對公式進行填充,但是用Power Query進行整理轉換,會給你帶來完全自動化的體驗,正所謂一勞永逸。
先看案例——培訓協議管理:
直接將其匯入到Power Query之中,中文的日期自動被識別並且更改了型別,將其轉換成短日期的形式,如下:
接下來就是計算新增的列了,根據勞動合同年限,我們可以計算出勞動合同截止日,這裡的3是以年為單位,在Power Query中的函式應該是:
注意這裡用的函式是:
Date。AddYears
所以根據這個函式,延展開來他還有:Date。AddDays, Date。AddMonths, Date。AddWeeks, Date。AddQuarters
有的可能說:上面結果不嚴謹,應該要減一天,那這個就直接再套一個函式好了:
接下來計算服務年限,服務年限是根據費用來了,假設少於5000不用籤協議,小於8000籤2年,其他情況籤3年。
根據這個定義,很明顯要用到條件語句了。這個可以直接在介面進行操作。
新增條件列:
根據去服務年限,可以計算出協議的終止日期了。這個其實就跟剛才的勞動合同截止日是一樣的了,這裡不再贅述。
最後最關鍵的就是根據這些資訊,來計算如果服務期內離職應賠款的數額。
先來分析一下,需要哪些資訊:
如果當前時間已過了服務期,那麼就不用進行賠款了。
如果還在服務期之內,那麼賠款就是:可以先求出總共要服務多少天,已經服務了多少天,按照每天的賠款乘上未服務的天數,就是應賠款金額了。
直接新增列:
確定後結果是:
得到的結果直接關閉並上載看看:(為了對比,先放到原始資料旁邊)
假設原始資料要新增條目:
直接重新整理看看結果:
是不是迅速的實現了計算結果,而且不用擔心公式的範圍問題。
相關文章
- 2021-05-26這個無法解決的數學問題, 價值數千億美元, 超級計算機也無能為力
- 2021-04-25日產計劃到2025年在華匯入9款新能源車
- 2021-04-22e-POWER,讓東風日產重切市場蛋糕
- 2021-04-12清朝滅亡後,數億賠款民國是怎麼處理的?列強主動退款是咋回事?
- 2021-04-04只要學會這十個EXCEL函式,大多數難題都能解開!