亞瑟的升級過程

            會注意到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_儲存格文字格式會讓計算結果錯誤  提及同事在邏輯判斷時用"-"的方式取代零值，這讓我想起我處理零值的方式，在此處與大家分享。大概能分三種方式，1.設置進階選項。2.自訂儲存格格式。3.用IF函數判斷( 如果判斷的結果會用來作為其他函數計算必須小心使用，平均值這類計算會出現錯誤結果 )。          基於方便性會建議以第一種方式處理，因為同工作表設置一次就好，而且是office本身內鍵的功能，也不會影響到計算。            第一種方式 ，檔案→選項→進階→ 在具有零值的儲存格顯示零(Z) 打勾不要選，一開始設定是作業中的工作表，紅色圈圈中也可以選擇整個活頁簿或是其他工作表。           第二種方式 ，自訂格式，選取儲存格，按右鍵，選取儲存格格式→自訂輸入 [=0]""   。           第三種方式 ，以IF設定公式。E2輸入=IF(TEST!E2<>0,TEST!E2,"")， 這種方式不建議使用，因為如果被其他計算公式引用容易造成錯誤。

             最近幾天我同事拿了一份資料問我，為什麼他的公式顯示出來的結果有異常。同事的公式在當結果是零值時以"-"的方式顯示，他沒注意到"-"不是數值格式，導致相關計算出現問題。          同事出現錯誤的情況如下表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檔中的儲存格來源，但久了就忘了有哪些儲存格有使用外部連結，這時候可以使用搜尋xl的方式，找出有使用外部連結的儲存格。          下圖就是一開啟檔案會跳出的提示，這時建議先不更新，否則原本有的結果可能會因為找不到連結出現錯誤。          接下來按Ctrl+F搜尋，找出活業簿中所有含有xl字眼的儲存格一個一個確認 。 按全部搜尋後就會出現所有連結儲存格位置，可以逐一檢視。