錦子老師

2677

選單問題

翁倩玉：「錦子老師，我這邊工作表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,,,),"")。

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

部落格相關範例

2020.06.10	1035Excel如何即時判斷二個條件-AVERAGEIF函數
2020.06.09	1034Excel日期輸入方法-EOMONTH函數
2020.06.08	1033Excel工作天數計算-NETWORKDAYS函數
2020.06.07	2674Excel 時間公式計算
2020.06.07	1031Excel統計各姓氏人數
2020.06.06	2673Excel 公式計算問題
2020.06.06	1030Excel儲存格輸入數值自動小數4位
2020.06.05	1029Excel公式拖拉問題
2020.06.05	1028Excel儲存格特殊符號值加總-VLOOKUP函數
2020.06.04	2671請問Office 2019有沒有辦法禁用更新
2020.06.04	1027Excel表格資料合併-VLOOKUP函數
2020.06.03	2670最近只要更新WIN10 不斷藍頻
2020.06.03	1026Excel平時成績計算-取最佳六次
2020.06.02	2668XLOOKUP函數問題
2020.06.02	1025Excel條件式加總-SUMIF函數
2020.06.01	2666如何將資料轉置
2020.06.01	1024Excel依身份證字號判別性別
2020.05.31	2665如何將所要字串擷取出來
2020.05.31	1023Excel 多欄數值資料照大小排成一欄
2020.05.30	2664資料剖析應用