贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

有讀者根據這篇:Excel-略過空白儲存格重整資料(陣列,OFFSET)文章,想要將下圖左含有空白列的資料清單改成去除空白的資料清單(下圖右),該如何處理?

第(1)式

儲存格D2:{=SMALL(IF($A$1:$A$17<>"",ROW($A$1:$A$17),999),ROW(2:2))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」

IF(廠商<>"",ROW(廠商),999):判斷廠商陣列中是否不為空白儲存格,若成立則取得列號陣列,不成立則給予一個很大的值(本例為999)。本例結果為{2,3,5,6,7,…}。

SMALL(IF(廠商<>"",ROW(廠商),999),ROW(1:1)):根據上式取得的列號陣列,取出最小的一個列號(ROW(2:2)=2),當向下複製公式時,可以依序取得較小的第 2、3、…之值。

第(2)式

儲存格D2:{=INDEX($A$1:$B$17,第(1)式,COLUMN(A:A))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」

COLUMN(A:A)=1,當向右複製公式時,會變為 COLUMN(B:B)=2。

透過 INDEX 函數利用查表方式取得A欄中的第一筆資料「子」,而看到的「#REF!」是錯誤訊息,乃因位址參照錯誤產生查詢不到資料。

複製儲存格D2,貼至儲存格D2:E17。

第(3)式

儲存格D2:{=IFERROR(第(2)式,"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」

透過 IFERROR 函數將產生的錯誤訊息(#REF!)轉換為空白。

複製儲存格D2,貼至儲存格D2:E17。

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

vincent 發表在 痞客邦 留言(3) 人氣()


留言列表 (3)

發表留言
  • kuan7953
  • 有個問題請你幫幫忙 我用此程式做了儲存格對調的按鍵 但是對調後 儲存格裡面的公式都不見了 該怎麼寫才對呢 求解 感謝!
    Private Sub CommandButton1_Click()
    Dim a1, c1, e1, a2, c2, e2
    With ActiveSheet
    a1 = .[a1]: c1 = .[c1]: e1 = .[e1]
    a2 = .[a2]: c2 = .[c2]: e2 = .[e2]
    .[a1] = a2: .[c1] = c2: .[e1] = e2
    .[a2] = a1: .[c2] = c1: .[e2] = e1
    End With
    End Sub
  • 一般二個變數(A,B)要交換時, 必須借助第三個變數(Temp):
    Temp = A : A = B : B = Temp
    即可完成交換。

    vincent 於 2015/08/25 09:36 回覆

  • Yong
  • 你好!
    最近遇到些問題想請您幫幫忙
    1.需要略過空白格
    2.需要統計不同廠商與不同產品的需求量
    3.相同產品會出給不同廠商
    4.同一廠商也會拿不同產品
    請問是否有辦法用函數方式統計呢? 謝謝您!

    廠商 產品 需求量 廠商 產品 需求量
    A a 3 A a 5
    B b 3 A b 5
    B b 6
    A a 2 C c 4
    C c 4 D d 4
    A b 5 E e 1
    D d 4 F f 2
    B b 3 G g 1
    E e 1 H h 5


    F f 2
    G g 1
    H h 5
  • 沒有利用公式來解題,改用樞紐分析工具來操作。
    請參考:http://isvincent.pixnet.net/blog/post/46685299

    vincent 於 2016/09/25 11:55 回覆

  • kenwang0814
  • 老師您好,


    我是VBA 的新手,在EXECL上遇到一些問題,想與老師請教
    想請教 假設\我一列有12欄位

    想針對 其中的L M 二欄的資料做判斷

    若當L欄的值是ABC,M欄會因L欄目前的值是ABC而更改驗證該M欄已KEY入的值方式
    若L2是 CCC,則M2會跳另一種值的驗證

    那我現在的問題是

    如何設定這個RANGE 區間

    當 L1 =ABC 自動判斷使用者key入M1的值進行驗證
    L2=CCC 自動判斷已KEY入M2的值 依此類推

    但L欄不是一定要有資料,所以L是空白時,M也不需有資料

    以上請教老師 大概的寫法要怎麼做呢?謝謝


您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼