close

3977

Excel如何產生一個動態的清單

錢泳辰:錦子老師您好,想要用OffsetMatchCountif三個公式產生一個動態的清單,但是顯示的採購清單是不正確的,研究了很久看不出來是哪裡錯了,還請老師指教,謝謝。

image

錦子老師詠辰,由於沒有看到妳的公式,故老師用我的解法,使用INDEXSUMPRODUCTCOUNTIFLARGE組合而成的公式來做。

步驟一:點取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儲存格。

image

公式一($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儲存格內容的筆數。

image

公式三 LARGE(公式一,公式二-ROW()+2) 將公式一中符合條件記錄的第N大列號傳回,由於是由小到大排列,故將公式二結果減目前儲存格列號再加2,從符合的最小列號向下排到最大列號。

image

公式四SUMPRODUCT(公式三) 將公式三結果加總。

公式五INDEX($B$1:$B$11,公式四,1) 傳回B1:B11儲存格中第幾列(公式四傳回結果)內容。

image

公式六IFERROR(公式五,"") 如果公式四結果找不到記錄出現錯誤訊息,則不填入資料

幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~

更多相關影片教學:請點我

更多相關文章:請點我


arrow
arrow
    創作者介紹

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