錦子老師

王建瑄：「錦子老師您好，我在A2儲存格照你教的公式及步驟輸入。

=SUMPRODUCT(LARGE(((LEFT($K$1:$N$30000,3)=A$1)*($K$1:$N$30000)),ROW()-1))

但為什麼你做就OK，而我做就會出現#VALUE!錯誤訊息：

麻煩解惑 ~ 感恩，謝謝！」

錦子老師：「這是由於你的資料是從記事本或其他電腦系統轉來的，有一些不可見的字元隱藏在儲存格中，所以造成困擾，首先先你如何知道，作法如下：」

步驟1：點取編輯的「插入函數fx」圖示，再點取LARGE中任一字元，會看到如下圖。

會發現ARRAY參數的第一個傳回的不是數值，而是#VALUE!錯誤訊息，則表示K1儲存格的內容看起來是數值，但實際不是，我們只要再重新輸入一次那串數字，再將公式中的陣列範圍從30000縮小到100列即可，記得要按CTRL+SHIFT+ENTER鍵完成輸入哦!

這是因為30000列有多個假數值，用眼睛無法視別，而【函數引數】對話方塊只可顯示10個左右的儲存格結果，故需要使用其他函數配合檢查。

步驟2：點取J1儲存格輸入公式：

=IF(VALUE(K1),1)

再將公式複製到J2:J30000儲存格。

步驟3：點取「資料 > 篩選」圖示。

步驟4：點取J1儲存格右方▼鈕。

步驟5：點取「1」核取方塊，使其空白。

步驟6：點取「確定」鈕，則會看到有問題的列數。

步驟7：將有問題列數的G欄數值重新輸入。

步驟8：再點取「資料 > 篩選」圖示取消篩選。

步驟9：依照上述步驟，將J欄公式依序改成「=IF(VALUE(L1),1)」、「=IF(VALUE(M1),1)」、「=IF(VALUE(N1),1)」一一測試，直接L到N欄都沒問題，才將J欄選取，按DELETE鍵清除公式。

步驟10：將A2儲存格公式改成：

=SUMPRODUCT(LARGE(((LEFT($K$1:$N$35554,3)=A$1)*($K$1:$N$35554)),ROW()-1))

按CTRL+SHIFT+ENTER鍵完成陣列公式輸入，再將公式複製到A2:I350儲存格。

【注意】

由於資料及設公式的儲存格太多，若記憶體不足會需要一段時間運算，所以要控制好存放公式的列數，不能超出符合結果列數太多，造成一直在運算不能工作。

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

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

更多相關文章：請點我