close

3959

Excel各數值的排序及數量加總

左芊芊:錦子老師您好,想請教的問題如下圖所示,如何用函數讓左邊表格轉置成右邊的表格並由小至大向下排序,並正確顯示各長度對應數量(加總)

image

錦子老師芊芊,其實我們可以用下列公式來完成。

  • 長度項目分析

=UNIQUE(SMALL(FILTER(B1:F8,A1:A8=H1),ROW(INDIRECT("A1:A"&ROWS(B1:B8)*COLUMNS(B1:F1)/2))))

公式一:ROWS(B1:B8)*COLUMNS(B1:F1)/2 計算出會有多少個儲存格資料要分析及統計。

image

公式二:INDIRECT("A1:A"&公式一) 設定儲存格為A1:A?(公式一傳回數值),分析資料用,目前會顯示儲存格內容。

image

公式三:ROW(公式二) 顯示各個列號。

image

公式四:FILTER(B1:F8,A1:A8=H1) 抓取長度的所有資料。

image

公式五:SMALL(公式四,公式三) 當各個列號對應的長度由小到大排列,會有重覆資料(我以填滿黃色標示出來)

image

公式六:UNIQUE(公式五) 將有重覆的記錄只抓取一筆記錄。

image

  • 數量統計

=IF(H2="","",SUMIF($B$1:$F$8,H2,$B$2:$F$8))

公式一:SUMIF($B$1:$F$8,H2,$B$2:$F$8) 統計B1:F8等於同列H(長度)儲存格內容的儲存格下一格(數量)數值加總。

公式二:IF(H2="","",公式一) 如果同列H欄儲存格為空格,則不填入資料,否則填入公式一運算結果。

image

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

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

更多相關文章:請點我

 


arrow
arrow
    創作者介紹

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