close

3055

Excel依照特定儲存格另存新檔

李碧華:「錦子老師您好,請教一下,想請問...

(1)有一總表(如圖:總表) 原本是橫向排列(內容有包含分割儲存格與合併儲存格)

image

(2)想把表單整理成直向(如圖:篩選後另存新檔(高雄、台中、台北))

(3)將表單依照篩選條件(如高雄、台中、台北)存檔,如圖?

image

麻煩解惑 ~ 感恩,謝謝!」

錦子老師:「這個問題的作法如下:」

步驟1:點取A3儲存格,輸入地區,本例為台北

步驟2:點取C1儲存格,輸入公式:

=IFERROR(OFFSET(總表!$A$1,SUMPRODUCT(LARGE((總表!C2:$C$14=地區!$A$3)*ROW (總表!$C$2:$C$14),COUNTIF(總表!$C$2:$C$14,地區!$A$3)+3-COLUMN()))-1,,,),"")

再將公式複製到D1:I1儲存格。

image

【公式說明】

=IFERROR(OFFSET(總表!$A$1,SUMPRODUCT(LARGE((總表!C2:$C$14=地區!$A$3)*ROW (總表!$C$2:$C$14),COUNTIF(總表!$C$2:$C$14,地區!$A$3)+3-COLUMN()))-1,,,),"")

公式1COUNTIF(總表!$C$2:$C$14,地區!$A$3)

統計總表C2:C14儲存格範圍中含有地區工作表A3儲存格內容的筆數。

公式2LARGE((總表!C2:$C$14=地區!$A$3)*ROW(總表!$C$2:$C$14),公式1+3-COLUMN())

統計總表C2:C14儲存格範圍中等於地區工作表A3儲存格內容的儲存格位置(ROW(總表!$C$2:$C$14))編號第N(公式13減目前欄編號)大的值。

公式3SUMPRODUCT(公式2)

統計公式2總和。

公式4OFFSET(總表!$A$1,公式3-1,,,)

傳回總表A1儲存格移動N(公式3-1)列的儲存格內容。

公式5IFERROR(公式4,"")

如果公式4傳回結果為錯誤值,則不填入資料。

步驟3:點取C2儲存格,輸入公式:

=IFERROR(VLOOKUP(C$1,總表!$A$3:$AZ$14,ROW(),0),"")

再將公式複製到D2:I2儲存格。

image

【公式說明】

=IFERROR(VLOOKUP(C$1,總表!$A$3:$AZ$14,ROW(),0),"")

公式1VLOOKUP(C$1,總表!$A$3:$AZ$14,ROW(),0)

在總表工作表A3:A14儲存格搜尋C1儲存格內容,找到後傳回同列第N(目前列號)欄儲存格內容。

公式2IFERROR(公式1,"")

如果公式2傳回結果為錯誤值,則不填入資料。

步驟4:點取C3儲存格,輸入公式:

=IF(C1<>"",$A$3,"")

再將公式複製到D3:I3儲存格。

image

步驟5:將C2:I2框選起來,再按CTRL+C鍵複製。

步驟6:將C4:I52框選起來,點取「常用 > 貼上 >貼上公式」圖示。

image

步驟7:點取A5儲存格,輸入公式:

=OFFSET(總表!$A$1,,ROW()-1,,)

再將公式複製到A9:A52儲存格。

image

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

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

更多相關文章:請點我


arrow
arrow
    創作者介紹

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