縮小excel檔案大小_xlsb二進位工作表

           會注意到xlsb這個檔案格式，一開始是請資訊(C公司)幫忙撈報表資料時，都是用xlsb的格式提供，有次看資料量有幾萬筆，但是檔案不大，開始了解xlsb這個格式，發現有它的優缺點。

搜尋相關資訊可以發現一些資料大概說明如下:

Excel是將資料內容儲存成了XML格式的檔案，而.xlsb格式不包含xml。

xlsb格式的優點：

檔案更小。

• xlsb的檔案小，對保存大Excel檔很有用。
• 打開和保存的速度更快。
• EXCEL直接使用二進位工作表比xlsx檔速度更快。

.xlsb格式的缺點：

 安全性。

• 對於XLSX/XLSM格式而言，我們可以輕易分辨哪些檔案帶有巨集，哪些沒有。如果用XLSB格式，無法知道這個檔案是否帶有巨集。(2003版的excel也是會有這問題)
• Power Query無法支援XLSB。如果要用Power Query，在舊版的excel跟 power query不支援xlsb，但是新版的power bi  跟 office 365 可以用power query開啟xlsb的檔案。

如果有使用excel外部連結方式，測試結果是可以把xlsb當一個小的資料庫，用sql連結撈取資料。

• 下面可以看出同樣檔案用xlsb儲存，大小會變成1/3

二進位工作表(縮小excel檔案)

excel_一個或多個循環參照的公式直接或間接參照瞭本身的儲存格

            這兩天收到同事的詢問，說excel 中出現奇怪的線，一開始同事只是截個圖，老實說我很久沒看過這種線，因為我都很久不寫函數了。官方說 數百萬人有相同的問題，我相信因為我在用函數時也遇過。

        下面簡單舉例同工作表直接參照跟間接工作表不同參照。

直接參照_同工作表參照

間接參照_不同工作表參照

就是那條線_間接參照_不同工作表參照

就是那條線_間接參照_不同工作表參照

 

錯誤訊息:

一個或多個循環參照的公式直接或間接參照瞭本身的儲存格，這可能會造成計算不正確。請嘗試移除或變更這些參照，或將公式移至不同的儲存格。

一個或多個循環參照的公式直接或間接參照瞭本身的儲存格

微軟官方說明  移除或允許循環參照  

Excel_不顯示零值的三種方式

            前一篇文章 Excel_儲存格文字格式會讓計算結果錯誤 提及同事在邏輯判斷時用"-"的方式取代零值，這讓我想起我處理零值的方式，在此處與大家分享。大概能分三種方式，1.設置進階選項。2.自訂儲存格格式。3.用IF函數判斷(如果判斷的結果會用來作為其他函數計算必須小心使用，平均值這類計算會出現錯誤結果)。

        基於方便性會建議以第一種方式處理，因為同工作表設置一次就好，而且是office本身內鍵的功能，也不會影響到計算。

         第一種方式，檔案→選項→進階→ 在具有零值的儲存格顯示零(Z) 打勾不要選，一開始設定是作業中的工作表，紅色圈圈中也可以選擇整個活頁簿或是其他工作表。

        第二種方式，自訂格式，選取儲存格，按右鍵，選取儲存格格式→自訂輸入[=0]""  。

        第三種方式，以IF設定公式。E2輸入=IF(TEST!E2<>0,TEST!E2,"")，這種方式不建議使用，因為如果被其他計算公式引用容易造成錯誤。

Excel_儲存格文字格式會讓計算結果錯誤

          最近幾天我同事拿了一份資料問我，為什麼他的公式顯示出來的結果有異常。同事的公式在當結果是零值時以"-"的方式顯示，他沒注意到"-"不是數值格式，導致相關計算出現問題。

        同事出現錯誤的情況如下表E2，因為公式輸入為=IF(TEST!E2<>0,TEST!E2,"-")，"-"結果是文字格式，與會計專用格式不同，E5是以會計專用格式顯示，視為數值。

PS:  "-"、"0"、"" 這幾種方式都會儲存格被判斷為文字格式。

E2=IF(TEST!E2<>0,TEST!E2,"-")  文字格式。

E3=IF(TEST!E3<>0,TEST!E3,"0") 文字格式。

E4=IF(TEST!E4<>0,TEST!E4,0) 數值格式。

E5僅調整儲存格格式。

可以看出E2~E3在平均值及邏輯判斷時會判斷錯誤。

在excel活頁簿中尋找外部連結

        

        有時候我們在各個excel工作表中設了連結其他excel檔中的儲存格來源，但久了就忘了有哪些儲存格有使用外部連結，這時候可以使用搜尋xl的方式，找出有使用外部連結的儲存格。

        下圖就是一開啟檔案會跳出的提示，這時建議先不更新，否則原本有的結果可能會因為找不到連結出現錯誤。

        接下來按Ctrl+F搜尋，找出活業簿中所有含有xl字眼的儲存格一個一個確認

。

按全部搜尋後就會出現所有連結儲存格位置，可以逐一檢視。