close

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

839增加記錄不需要重新計算-SumproductOffsetCount函數

2017.08.15

472SUMIF好用十倍的函數SUMPRODUCT函數的使用方法及實例

2019.09.29

2219統計同列儲存格中各區間數值數量-COUNTOFFSETSUMPRODUCTCOUNTIFCOLUMN函數

2017.05.22

400別被SUMPRODUCT函數拖慢了你的Excel表格

2019.06.14

2109-38個條件求和公式沒用過,別說你會Excel

2019.05.27

2094Excel SUMPRODUCTSUMIF函數應用問題

2018.07.12

837將平時成績表中註記轉換成分數-SUMPRODUCT函數

2019.05.06

2081Excel 符合多個名稱的加總

2018.07.24

849在儲存格範圍找出是否有重複-SUMPRODUCTCOUNTACOUNT函數

2017.05.10

290SUMPRODUCT函數的公式語法及使用方法實例

2017.11.13

565Excel中隔列求和的方法

2019.09.27

2238Excel 求不連續資料顯示方法

2017.08.20

463Excel中根據工資使用公式計算各種面額鈔票的張數的方法

2019.10.11

2234Excel跨工作表二層下拉式選單

2017.09.17

371職場人士必會的16Excel函數公式

2017.09.06

454使用Excel函數來按月份對資料進行匯總的方法

2018.12.02

1065EXCEL 注塑機問題統計表製作-機台BY日期

2019.05.15

2083SUMPRODUCT函數應用

2019.08.05

2162中國職場必備的九個EXCEL函數公式-絕對硬邦邦之中國式排名

2019.08.27

2168-2Excel加總函數-2(SUMIFSSUMRRODUCT)

 


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()