錦子老師

翁倩玉：「錦子老師，我這邊工作表2的B2儲存格是名字的選單，B6儲存格以下是手動輸入欄，A6、A7儲存格會自動填入，想問如何在手動輸入備註後，切換其他人備註變空白可以填入，再切換回來後，又是原本人的備註呢？是要輸入後讓他存到其他工作表欄位嗎?要怎麼存呢?救救EXCEL菜鳥。」

錦子老師：「其實我們可以在工作表1將我們需要的欄位資料輸入其中，如下圖。

再回到工作表2的A6儲存格輸入公式：

=IF(B6<>"",ROW()-5,"")

再將公式複製到A7:A45儲存格(預估只有40筆，可以視資料狀況調整)。

點取B6儲存格輸入公式：

=IFERROR(SUMPRODUCT(LARGE((工作表1!A2:A100=工作表2!$B$2)*(工作表1!B2: B100),COUNTIF(工作表1!$A$2:$A$100,$B$2)-ROW()+6)),"")

再將公式複製到B7:B45儲存格。

【公式說明】

首先傳回工作表1的A2:A100儲存格範圍與工作表2的B2儲存格相同內容(工作表1!A2:A100=工作表2!$B$2)的儲存格乘上對應的日期值(工作表1!B2: B100)。

接著統計在工作表1中A2:A100儲存格範圍含有工作表2的B2儲存格內容的儲存格個數COUNTIF(工作表1!$A$2:$A$100,$B$2)。

由於日期值要由小到大排序，故將前面傳回的日期值抓取以最大值排列從最小到最大的順序故是個數減掉ROW值+6(因為從第6列開始算)，因為抓取順序(LARGE((工作表1!A2:A100=工作表2!$B$2)*(工作表1!B2: B100),COUNTIF(工作表1!$A$2:$A$100,$B$2)-ROW()+6)並透過SUMPRODUCT函數傳回其值。

如果沒有40列，無資料可供運算的儲存格會顯示#NUM錯誤訊息，故用IFERROR函數使其不顯示錯訊息IFERROR(SUMPRODUCT(LARGE((工作表1!A2:A100=工作表2!$B$2)*(工作表1!B2: B100),COUNTIF(工作表1!$A$2:$A$100,$B$2)-ROW()+6)),"")。

點取C6儲存格輸入公式：

=IFERROR(OFFSET(工作表1!$C$1,SUMPRODUCT(LARGE((工作表1!$A$2:$A$100=工作表2!$B$2)*ROW(工作表1!$A$2:$A$100),COUNTIF(工作表1!$A$2:$A$100,$B$2)-ROW()+6))-1,,,),"")

再將公式複製到C7:C45儲存格。

【公式說明】

首先傳回工作表1的A2:A100儲存格範圍與工作表2的B2儲存格相同內容(工作表1!A2:A100=工作表2!$B$2)的儲存格所在列ROW(工作表1!$A$2:$A$100)。

接著統計在工作表1中A2:A100儲存格範圍含有工作表2的B2儲存格內容的儲存格個數COUNTIF(工作表1!$A$2:$A$100,$B$2)。

由於日期值要由小到大排序，故將前面傳回的日期值抓取以最大值排列從最小到最大的順序故是個數減掉ROW值+6(因為從第6列開始算)，因為抓取順序(LARGE((工作表1!A2:A100=工作表2!$B$2)*(工作表1!B2: B100),COUNTIF(工作表1!$A$2:$A$100,$B$2)-ROW()+6) 並透過SUMPRODUCT函數傳回其值。

由於要抓其C欄備註位置，故利用OFFSET函數從工作表1的C1移動傳回的值減1列OFFSET(工作表1!$C$1,SUMPRODUCT(LARGE((工作表1!$A$2:$A$100=工作表2!$B$2*ROW(工作表1!$A$2:$A$100),COUNTIF(工作表1!$A$2:$A$100,$B$2)-ROW()+6))-1,,,)。

如果沒有40列，無資料可供運算的儲存格會顯示#NUM錯誤訊息，故用IFERROR函數使其不顯示錯訊息IFERROR(OFFSET(工作表1!$C$1,SUMPRODUCT(LARGE((工作表1!$A$2:$A $100=工作表2!$B$2*ROW(工作表1!$A$2:$A$100),COUNTIF(工作表1!$A$2:$A$100,$B$2)-ROW ()+6))-1,,,),"")。

以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

部落格相關範例