3977 |
Excel如何產生一個動態的清單 |
錢泳辰:錦子老師您好,想要用Offset,Match,Countif三個公式產生一個動態的清單,但是顯示的採購清單是不正確的,研究了很久看不出來是哪裡錯了,還請老師指教,謝謝。
錦子老師:詠辰,由於沒有看到妳的公式,故老師用我的解法,使用INDEX、SUMPRODUCT、COUNTIF、LARGE組合而成的公式來做。
步驟一:點取E2儲存格輸入「=IFERROR(INDEX($B$1:$B$11,SUMPRODUCT(LARGE (($A$2:$A$11=E$1)*ROW($A$2:$A$11),COUNTIF($A$2:$A$11,E$1)-ROW()+2)),1),"")」後,再將公式複製到E2:H5儲存格。
公式一:($A$2:$A$11=E$1)*ROW($A$2:$A$11) 將A2:A11儲存格內容等於E1儲存格內容的列號排選出來,由於A2:A11範圍須固定故在欄名列號前皆加上$號鎖定,而E1由於公式向下右複製時,要欄名跟著變動故不鎖定,而向下複製時列號要鎖定,故加上$號鎖定。
公式二:COUNTIF($A$2:$A$11,E$1) 統計A2:A11儲存格內容等於E1儲存格內容的筆數。
公式三: LARGE(公式一,公式二-ROW()+2) 將公式一中符合條件記錄的第N大列號傳回,由於是由小到大排列,故將公式二結果減目前儲存格列號再加2,從符合的最小列號向下排到最大列號。
公式四:SUMPRODUCT(公式三) 將公式三結果加總。
公式五:INDEX($B$1:$B$11,公式四,1) 傳回B1:B11儲存格中第幾列(公式四傳回結果)內容。
公式六:IFERROR(公式五,"") 如果公式四結果找不到記錄出現錯誤訊息,則不填入資料。
幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~
更多相關影片教學:請點我
更多相關文章:請點我
留言列表