2339 |
Excel問題休假時數表-SUMPRODUCT |
雨萱:「錦子老師,下方工作表1工作表的表格為每日要KEY IN的資料。
依據上方的日期及姓名將假別及時數自動套用到下方表單內,用過INDEX+MATCH一直無法成功,想請幫忙還有什麼函數可以讓上方的資料自動套用到下方工作表二?謝謝!」
錦子老師:
點取B3儲存格輸入公式:
=IFERROR(OFFSET(工作表1!$C$1,IFERROR(SUMPRODUCT((工作表1!$A$1:$A$8=B$1)*(工作表1!$B$1:$B$8=$A3)*ROW(工作表1!$A$1:$A$8)),"")-1,0,1,1),"")
再將公式複製到C3:AF3儲存格。
【公式說明】
首先我們要判斷工作表1工作表中A2:A8儲存格中與工作表2工作表B1儲存格日期是同一天的資料(工作表1!$A$1:$A$8=B$1),由於公式會向下複製到列號1須加上$鎖定。
接著判斷工作表1工作表中B2:B8儲存格中與工作表2工作表A3儲存格是同一類型的資料(工作表1!$B$1:$B$8=$A3),由於公式會向右複製故欄名A須加上$鎖定。
傳回工作表1工作表中A2:A8儲存格的列號ROW(工作表1!$A$1:$A$8)。
最後利用SUMPRODUCT函數將前面三項相乘,由於第1,2項會傳回TRUE(1)與FALSE(0),所以必須增加第3項的列號,使其可以變為數字並得知資料在第幾列SUMPRODUCT((工作表1!$A$1:$A$8=B$1)*(工作表1!$B$1:$B$8=$A3)*ROW(工作表1!$A$1:$A$8)),"")。
然後透過OFFSET函數,從C1儲存格向下移動前面算出的列數-1(因為一開始在第一列,故須減1),不移動欄,傳回該儲存格內容(假別)OFFSET(工作表1!$C$1,IFERROR(SUMPRODUCT((工作表1!$A$1:$A$8=B$1)*(工作表1!$B$1:$B$8=$A3)*ROW(工作表1!$A$1:$A$8)),"")-1,0,1,1)。
如果有錯誤訊息表示沒資料,則不填入資料=IFERROR(OFFSET(工作表1!$C$1,IFERROR(SUMPRODUCT((工作表1!$A$1:$A$8=B$1)*(工作表1!$B$1:$B$8=$A3)*ROW(工作表1!$A$1:$A$8)),"")-1,0,1,1),"")。
點取B4儲存格輸入公式(傳回時數):
=IFERROR(OFFSET(工作表1!$D$1,IFERROR(SUMPRODUCT((工作表1!$A$1:$A$8=B$1)*(工作表1!$B$1:$B$8=$A3)*ROW(工作表1!$A$1:$A$8)),"")-1,0,1,1),"")
再將公式複製到C4:AF4儲存格。
將B3:AF4框選起來,再複製到B5:AF12儲存格。
部落格相關範例
2018.07.15 |
|
2017.08.15 |
|
2019.09.29 |
2219統計同列儲存格中各區間數值數量-COUNT、OFFSET、SUMPRODUCT、COUNTIF、COLUMN函數 |
2017.05.22 |
|
2019.06.14 |
|
2019.05.27 |
|
2018.07.12 |
|
2019.05.06 |
|
2018.07.24 |
|
2017.05.10 |
|
2017.11.13 |
|
2019.09.27 |
|
2017.08.20 |
|
2019.10.11 |
|
2017.09.17 |
|
2017.09.06 |
|
2018.12.02 |
|
2019.05.15 |
|
2019.08.05 |
|
2019.08.27 |
留言列表