iT邦幫忙

0

excel 年資人數統計計算問題

有沒有適合的公式可以依照已經抓出年月日的年資,

像紅框處帶出各年資的人數(現在都人工填寫)

爬很多文都是只抓前面"年"但都沒抓後面的月日
https://ithelp.ithome.com.tw/upload/images/20190816/20102095Il5b1oD6u9.jpg

懇請各位大神解惑!!!

剛有大神私底下給我答案了~

提供有需要的人索取
https://mega.nz/#!tI4ACSjA!T9ytpyE3OjCMMLWvo-9eXKZveTB_ab1aim-v_biEub0

看更多先前的討論...收起先前的討論...
...
...
This transfer is no longer available.
The transfer "2019-08-16 - My Transfer"is no longer available either because it has expired, has reached the maximum number of downloads, or was deleted.

These files have been deleted from our servers. If you still need these files, please contact the original sender and request another transfer.
提供有需要的人索取
https://mega.nz/#!tI4ACSjA!T9ytpyE3OjCMMLWvo-9eXKZveTB_ab1aim-v_biEub0
都沒抓後面的月日 是因為要計算 六個月以上未滿一年 跟 未滿六個月 嗎
算出年資沒問題,主要抓取每個年資的人數而已 如紅色框選部分,已經有大神已經解答囉!! 感謝您
jasonlin268 iT邦研究生 3 級 ‧ 2019-08-16 15:58:19 檢舉
這位大神做的更精簡耶,真是佩服,又學了一些。
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
3
jasonlin268
iT邦研究生 3 級 ‧ 2019-08-16 14:57:44
最佳解答

不太會Excel,但是覺得蠻有意思的,所以來試試看,用總月份來算不知道對不對。
https://drive.google.com/open?id=1gBDqPYEkc0MdJEmfBArlqNGePwqgDlrq

看更多先前的回應...收起先前的回應...

你的是對的,剛有高手已經先給我答案如果他沒有需要,我就選定你最佳答案囉!!

高手都很謙虛

原來還有 DATEDIF 可以用

這樣用,簡單多了!!

我也長知識了,DATEDIF平常不太用到

3
japhenchen
iT邦超人 1 級 ‧ 2019-08-16 12:17:59

我用SQL寫的,不知對你有沒有用,僅供參考

CREATE  PROCEDURE [dbo].[SP_GetEmpYears]
AS
BEGIN
	DECLARE @TOYEAR AS VARCHAR(10)
	SET @TOYEAR = CAST(YEAR(GETDATE()) AS VARCHAR)+ '/4/30'  -- 實際當年的4月30日止
	DECLARE @LASTDAY AS DATETIME
	SET @LASTDAY = CONVERT(VARCHAR(10), @TOYEAR ,111)

	DECLARE @MINYEARS AS INT
	SET @MINYEARS = 5   --最少五年以上,五一勞動箱時要頒資深員工獎用的

	SELECT 
		EMPNO 工號,
		EMPCNAME 姓名,
		HIREDDATE 到職日 ,
		floor(CAST(DATEDIFF(MONTH, HIREDDATE,@LASTDAY) AS FLOAT) /12) 實際年資,
		floor(CAST(DATEDIFF(MONTH, HIREDDATE,@LASTDAY) AS FLOAT) /60)*5 五的倍數
	FROM HR.DBO.EMPLOYEE
	WHERE 
		QUITDATE IS  NULL AND DATEDIFF(YEAR, HIREDDATE,@LASTDAY)>@MINYEARS 
		and CAST(floor(CAST(DATEDIFF(MONTH, HIREDDATE,@LASTDAY) AS FLOAT) /12) AS INT) %5 = 0
	ORDER BY HIREDDATE
END

https://ithelp.ithome.com.tw/upload/images/20190816/20117954EkEx5onAt5.jpg

看更多先前的回應...收起先前的回應...

年資6、7、8、9,11、12.......不在5的倍數年的,請繼續加油

感謝您回答但我需要EXCEl公式,一般user沒辦法操作SQL

我知道,但很明顯DATEDIF不能用了(office 2016)

所以我才做成stored procedure ,提供給使用者直接以excel查詢,或是你做成網頁或匯出excel.........blahblahblah..........

我用Excel 2016 MSO (16.0.4849.1000)64位元
Datedif還是可以用。
Datedif算是隱藏版函數,Excel內建的說明中找不到,要去微軟的網站https://support.office.com/zh-tw/article/datedif-函數-25dba1a4-2812-480b-84dd-8b32a451b35c 才有說明。

但無法在我現在手邊的EXCEL2016上使用,不管參數怎麼改,都會出現#NAME?的錯誤

djhs115z4 iT邦新手 5 級 ‧ 2019-08-19 09:31:14 檢舉

Y、M、D 要加上""
"Y"

A1格是 =now()
https://ithelp.ithome.com.tw/upload/images/20190819/20117954oxtHwrxU8z.jpg

djhs115z4 iT邦新手 5 級 ‧ 2019-08-19 10:39:20 檢舉

DATEDIF(start_date,end_date,unit)
Start_date大於End_date,則結果為#NUM !

所以還得先判斷START < END

2
paranoiadevil
iT邦新手 5 級 ‧ 2019-08-16 13:26:02

檔案已經失效了,沒辦法看。
建議把年、月換算成單一值,ex.8年6月=>8.5年,再用countifs,算出各年資區間的人數。
ex.一年以上未滿兩年,A欄為年資
=COUNTIFS(A:A,">=1",A:A,"<2")

下載連結已更新,剛測試一下好像不太對不知道哪裡有搞錯,懇請大神指引

不好意思剛剛去開會了,這個的語法是用countifs來計算符合多個條件的年資,也可以用在其他地方。

我要發表回答

立即登入回答