iT邦幫忙

0

資料整理

如何用EXCEL公式將資料這樣整理?
我想了很久也想不到,求大神解救.
一至六行是現有的資料格式,第十行是我想要的結果.

https://ithelp.ithome.com.tw/upload/images/20200818/201223983jvxQtxQNG.png

看更多先前的討論...收起先前的討論...
請假日數6指的連續請假6天?..........看懂了,不過說真的我想不出來有何"函數"可以一次填多格,但巨集或是用C#或PYTHON直接讀寫XLSX檔是可行的
AnakinTai iT邦新手 5 級 ‧ 2020-08-18 16:53:33 檢舉
但我不會用VB、 C#、PYTHO
雷伊 iT邦高手 1 級 ‧ 2020-08-18 16:56:03 檢舉
C2:E3=一次請三天,C3一天。C4:D4=一次請兩天,C5、C6各請一天
結果是要把整年度請的假排成一列
你們家沒有人資系統?
直覺上不是可以使用陣列公式去把問題看成,從C2:J6找出第1~8個不是-的值
AnakinTai iT邦新手 5 級 ‧ 2020-08-18 17:04:45 檢舉
雷伊大神,我們的人資系統老闆看不懂,之後要我們做一個他看得懂的.
ckp6250 iT邦好手 1 級 ‧ 2020-08-18 17:12:25 檢舉
生小孩的,四、五十天,那要怎麼辦?
AnakinTai iT邦新手 5 級 ‧ 2020-08-18 17:13:04 檢舉
keyway014945大神我也有想過大約的方法,但就是不會整理成第十行.
AnakinTai iT邦新手 5 級 ‧ 2020-08-18 17:15:27 檢舉
生小孩的就連續做個四五十天給他看吧
雷伊 iT邦高手 1 級 ‧ 2020-08-18 18:53:48 檢舉
函數T:雖然可以回傳文字但僅能回傳單一欄位,無法分辨整個陣列,光靠 Excel似乎是有困難的
goodnight iT邦研究生 3 級 ‧ 2020-08-19 11:13:55 檢舉
題外話, 你是想統計請假天數並且知道請了哪些天是不是?
你這樣的格式, 有沒有遇過什麼樣的問題?
AnakinTai iT邦新手 5 級 ‧ 2020-08-19 15:26:17 檢舉
goodnight大大你指原格式還是老闆要的格式.

2 個回答

0
ccenjor
iT邦研究生 5 級 ‧ 2020-08-18 19:33:01
最佳解答

只有一個人我的做法(用姓名抓資料)。
C10儲存格公式:
=IFERROR(SUMPRODUCT(LARGE(($B$2:$B$6=$B$10)($C$2:$J$6),COUNT($C$2:$J$6)-COLUMN()+3)),"")
再複製到D10:J10
https://ithelp.ithome.com.tw/upload/images/20200818/20109881JtvuTiwgnL.png
資料有二個人以上
C10儲存格公式:
=IFERROR(SUMPRODUCT(LARGE(($B$2:$B$9=$B$10)
($C$2:$J$9),SUMPRODUCT(($B$2:$B$9=$B$10)*($C$2:$J$9<>""))-COLUMN()+3)),"")
https://ithelp.ithome.com.tw/upload/images/20200818/20109881fyOtgc9fAT.png

看更多先前的回應...收起先前的回應...
AnakinTai iT邦新手 5 級 ‧ 2020-08-19 17:10:28 檢舉

ccenjor大神,這公式是可行的,但當我在跨頁時就不行了.
主要是在LARGE的K值中出現N/A

=IFERROR(SUMPRODUCT(LARGE((年假資料!$B:$B=$A2)(年假資料!$I$2:$T$1519),SUMPRODUCT((年假資料!$B:$B=$A2)(年假資料!$I$2:$T$1519<>""))-COLUMN()+3)),"")

ccenjor iT邦研究生 5 級 ‧ 2020-08-19 19:22:36 檢舉

照上面看,你公式中二個()中間沒有*號。
另外由於你跨工作表,會有年假資料工作表的B欄與目前工作表的A欄格式是否一致的問題,不要一個文字,一個是數值,會找不到。所以我才用姓名來判斷。

AnakinTai iT邦新手 5 級 ‧ 2020-08-20 09:36:18 檢舉

我嘗試過用姓名了還是不行,在同一頁是沒問題的,但跨頁就出現了問題https://ithelp.ithome.com.tw/upload/images/20200820/20122398mW1P98D0xR.png
https://ithelp.ithome.com.tw/upload/images/20200820/20122398vTI0gcDUKw.png
應該是LARGE上出現了問題https://ithelp.ithome.com.tw/upload/images/20200820/20122398frkBLn6lz1.png

ccenjor iT邦研究生 5 級 ‧ 2020-08-20 18:34:02 檢舉

因為沒看到整個畫面,所以無法判斷,檔案可否寄給我研究。

ccenjor iT邦研究生 5 級 ‧ 2020-08-20 18:53:07 檢舉

研究半天總算找到答案,那是因為你設定$A:$A,不行,要改成$A$2:$A$1519才可以,否則會員工編號或姓名與請假日期的列數對應不上,出現#N/A。

AnakinTai iT邦新手 5 級 ‧ 2020-08-21 16:44:20 檢舉

哦!原來是這樣,但-COLUMN()+3這段不太明白,如果可以希望賜教.謝謝!

ccenjor iT邦研究生 5 級 ‧ 2020-08-21 19:05:54 檢舉

以陳曉曉為例:
這是要抓第4~1大的值,也就是要從日期最小值開始抓取。
SUMPRODUCT(($B$2:$B$9=$B$10)($C$2:$J$9<>""))-COLUMN()+3))
SUMPRODUCT(($B$2:$B$9=$B$10)
($C$2:$J$9<>""))會傳回4
而開始欄在c欄,其欄編號為3,所以column()會傳回3,減掉後再加3等於0,所以會抓取第4大的值。
而到了d欄就變成4-4+3會抓取第3大的值。
這是為了方便可以複製來一次完成整個日期統計。。

1
海綿寶寶
iT邦大神 1 級 ‧ 2020-08-18 18:21:03

參考看看
這裡有三種方法
可任意修改使用

我要發表回答

立即登入回答