Working with Progressing

直到這兩三年開始跟別人解釋我學習Excel的歷程時，才開始回想開始讓我驚奇"原來Excel可以這麼做"的功能，應該就是VLOOKUP吧! 從念書一直到我第一份工作結束前，Excel我只用來使用第3方增益集做統計分析和決策分析，不然只是記錄log，最多拿來暫代VB6開發些寫轉檔程式(重灌後懶得安裝)。直到工作開始要排MPS、匯工單之後，才第一次接觸VLOOKUP (但始終想不起來當時學的心情，教我的應該是丹尼兄或艾琳妹妹...我是個受教還是叛逆的學生哩?)。 總之，累積越來越多樣的工作性質和範圍後，VLOOKUP(簡稱為"V")可以是簡單的查詢函數、更可以複雜計算報表中可活用變化的基礎元素。

要談Excel之前，必須先談Office。近期(也快10年了?!)Office的分水嶺是 Office 2007，主要改變和差異如下： Office 97, 2000, 2003: 過去的版本，彼此間的相對差異不大，整體而言2003增加一些人性化的功能，只不過不是每個都成功、甚至導致問題。例如選單項目根據使用頻率顯示或隱藏(最後我都一律關掉這功能)，或者在Word中排版圖片或表格，使用Word 2003會讓我想砸電腦，永遠搞不定改回用Word 2000可以3秒搞定的排版。 Office 2007, 2010, 2013, 2016: 最顯著的差異在Ribbon介面，初接觸時非常令人卻步，但習慣反而覺得這樣群組選單是不錯的設計。其次是每個應用程式都使用新的檔案格式(規則是副檔名加上1個X，例如*.DOC換成*.DOCX、*.XLS換成*XLSX)。其他就是越新的版本越強化了視覺化和預先定義的功能(把預想的目的直接做成一個按鈕或設定)，以及與雲端網路的更深入整合。 而針對Excel，環境許可的話，請不要再用Excel 2003以前的舊版本，很多小地方是可以大幅簡化和加快使用的作業。只不過我建議會不要使用Excel 2007，其他任何版本都沒太大差異。在過去的經驗上，這個版本蠻常導致檔案最後無法存檔、或無法正常開啟(升級到Office 2007 SP1僅些微改變)。 在我的介紹中，會避開著墨哪個版本開始導入的，這些都可以在微軟的線上說明找到答案，也可以參考Wikipedia的比較「 Microsoft Excel 」條目。 接下來討論Excel新舊格式的差異，主要可用這個屬性在表格中解釋。 --> 舊格式 新格式 *.XLS *.XLSX *.XLSM *.XLSB 官方名稱 Excel 97-2003活頁簿 Excel 活頁簿 Excel 啟用巨集的活頁簿 Excel 二進位活頁簿 工作表允許最大列數 65,536列 1,048,756列 工作表允許最大欄數 256欄 16,384欄 允許檔案內儲存巨集 Yes No Yes Yes 用Excel 2003開啟 完整讀取 & 修改 僅可讀取無法更新存檔 用Excel 2007以後開啟 「相容模式」開啟 & 修改 完整讀取 & 修改 檔案格式(概念) ...

本區是個人的EXCEL教學目錄，先說以下幾點 我沒什麼耐心閱讀訪間的EXCEL工具書，編排順序純屬個人想法 參考文件主要是微軟Office說明文件(aka. HELP)和Google大神 多半都是因工作(或私生活上)的實務需求觸發

看過儲存格內怎麼看明明都是數字沒錯，可是卻怎麼樣都沒有辦法用它來做任何運算吧? 就算使用很基本的公式(TRIM)、或者資料剖析的方式，始終沒有辦法有效的改正。 其實原因大多都是你的資料很有可能是從網頁上的表格複製下來，或者檔案根本就是網頁格式(Excel很聰明可以自動開啟，只是有些地方沒有處理)。 如何診斷 ： 使用公式 =LEN(儲存格) 來判斷，如果你眼睛明明看到的就1個3位數的數字，理當用LEN來數把3位數字當成文字的長度，應該也是3，可是結果卻是3以上的數字。 肇因 ： 儲存格除了看到的數字之外，也存在了其他「不是用鍵盤上空白鍵輸入的空白」....很像在繞口令，但文字中其實有很多沒有顯示、或者顯示跟空白相同，但是他卻不等於空白。 舉例來說：換行符號、Tab鍵(一般人非常難再儲存格中輸入TAB)、或HTML的空白 。這些都是導致該儲存格被Excel判定是文字，可是我們卻沒有辦法用取代空白的方式改變。 解法 ； 如果只有幾筆資料的話，真的！自己另外在別格看著數字重新敲一次吧。這絕對是最有效率與簡單的方法(沒開玩笑，我真的是建議幾個數字而已，手敲吧~~) 用滑鼠或鍵盤點選編輯該儲存格，在數字的前後想辦法可否選取到那看不見的空白，想辦法複製1個，再用取代的功能，用Ctrl+V貼上那看不到的空白。(一般的HTML空白是可以這麼處裡的!) 使用巨集寫成Function 或者 Sub，然後用以下的程式碼處理它。我自己是把這個類似的功能寫成Sub，然後固定儲存在個人巨集，設定快速鍵來呼叫。(改天再來寫怎麼做出個人巨集 Sub Trim_Range(selRange As Range)     Dim i As Long, j As Long, cnt As Integer     cnt = 0     For i = 1 To selRange.Areas.Count         For j = 1 To selRange.Areas(i).Count             If IsEmpty(selRange.Areas(i).Item(j).Value)...

請先閱讀OFFSET在Excel中F1的說明，或以下線上說明 http://office.microsoft.com/zh-tw/excel-help/HA102752910.aspx 我自己是有看沒有懂啦！ 經過研究之後，我的體認如下，供大家參考。  這是一個參照操作的函數，目的是給定一個參照範圍，然後依照水平與垂直位移的參數進行位移，甚至調整新的參照範圍的大小。這公式多與其他引用參照範圍的公式合併使用，透過參數可用計算、用 " 拉 " 的 (Excel 的重大技巧；自動套用自動數列 ) 、或其他小技巧，可以做出一些不太直覺卻比較好維護的結果。 想像拿一個框框，貼其格線地放在 Excel 的表格上，然後水平移動數格、在垂直移動數格，接下來稍微調整框框的大小。 用 SUM 來做例子，所謂的參照範圍是 B2:C4 ，也就是第 1 個紅色框框所表示的範圍。這個範圍的 SUM 結果是 195 。 使用 OFFSET 來移動這參照範圍 B2:C4 ， ROW 的部分移動 5 ( 也就是向下移動 5 列， ROW 參數正數表示向下，負數表示向上 ) ， COLUMN 的部分移動 2 ( 也就是向右移動 2 欄， COLUMN 參數正數表示向右，負數表示向左 ) ，此時的結果是 D7:E9 ，如右邊的紅色框框所示。所以 SUM(D7:E9) = SUM(OFFSET(B2:C4,5,2)) = 507 。 剛剛 OFFSET 的第 4, 5 參數並沒有設定，則預設值是會等同原始的參照範圍大小。如果改成 HEIGHT=1, WIDTH=4 ，則 OFFSET 的結果會是指向 D7:G7 ，如藍色的框框所示，所以 SUM(D7:G7) = SUM(OFFSET(B2:C4,5,2,1,4)) = 302 。 推算的方式就是用所謂的參照範圍的左上角，紅色的框框來做定位點，往上下 (by ROW, 第 2 個參數 ) 、往左右 (by COLUMN, 第 3 個參數 ) 的移動到藍色的點，此時框框的大小是和原始的參照範圍相同，都是紅色的框框。最後依照高度與寬度 ( 都必須是正數 ) 來往下、與往右去展開新的範圍大小，變成最後的藍色框框。

應該遇過：明明 Excel 沒存很多資料，可是檔案卻異常的龐大。甚至存檔會鈍鈍的。有時候這只是因為 Excel 檔案 " 虛胖 " 喔 !! 經驗來說，可能是以下幾種原因造成的，提供一些簡單的解決方式供參考。 1. 工作表有異常多的空白行或列。 怎麼判斷檔案有這種狀況呢？只要觀察檔案橫向 ( 水平 ) 與縱向 ( 垂直 ) 的滾軸 (scroll bar) ，當你認為你當下選取的位置已經是資料的尾端，然而滾軸卻看起來往下 ( 或往右 ) 還未到底，而且差很遠，請檢查你認為的資料尾端到把滾軸拉到底部中間，是否真的沒有資料 ( 這裡是在檢查你是否有漏看 ) 。大多數的情況你其實都可以很快地判定出來，沒有的情況就表示 Excel 工作表存在了沒有用到的行或列，而且多半數量非常的多。 造成這樣的原因，應該是 Excel 認為中間這一大段範圍中，存在過資料、或以為會有資料，因此 Excel 已經把空間空出來，然而之後卻沒正常地判斷出那些空間是不再需要了。 簡單的作法就是手動地執行刪除，大多數的情況是可以解決這問題，少數情況在舊版的 Excel 必須依靠微軟曾推出的 1 個增益集來用巨集的方式刪除，只是這增益集並沒有再繼續更新，無法直接在新版 Excel 套用。 手動刪除的步驟可參考如下：在認為資料的最後 1 列 ( 行 ) 之後，選取下 1 個空白列 ( 行 ) ，然後按著 Ctrl + Shift + 方向鍵下 ( 行的話則改方向鍵右 ) ，這時候你會看到跳到 Excel 檔案上限的最尾端，中間都被選取。這時候只要按右鍵功能表，選擇 " 刪除 " 。檔案存檔關閉後，重新打開即應可按到 scroll bar 回復正常。 2. 檔案中有使用大量引用其他檔案的公式 引用其他檔案的公式，指的是像使用 VLOOKUP 去另一個檔案 "V" 資料回來。 Excel 的儲存格存公式會比存值多佔一些空間，但如果公式的內容是其他檔案的時候，所佔的空間會來得更大上許多。在工作中應該最常發生去 VLOOKUP 一個從郵件打開的 Excel 檔，這個資料所在的 Excel 檔其實也只存在本機的暫存目錄，不可能可以更新這公式，好的工作習慣是 VLOOKUP 完之後，把結果再貼成值。只是實務面常會有...

剛好工作遇到，發覺周遭常有些人頻繁地使用EXCEL於工作中，尤其以作資料整理、control的來說，有些函式可以搭配其他函式後，讓其他函式的功能更好用、方便，只是沒有人帶入門，因此不知道...Excel的高段不是在用冷僻的函式、甚至撰寫巨集/VBA，而是組合函式與設計技巧/經驗－這才是要豎起大拇指的高手！

我們已習慣在搜尋或篩選功能中用特殊字元「*」（星號，數字8那個）來當作關鍵字，用來表示任何字元。然而當我們想用的就是字元「*」號文字本身而不是當它為關鍵字的時候？

曾聽到有人講：Office Excel 2007無法像2003一樣可以自訂工作列，然後依每個人自己的習慣把常用的功能按鈕放在上面（例如：貼上值、自動篩選等），當時我並沒有使用過2007，的確覺得這樣子是非常不方便，尤其在工作上常需要一些被藏在比較裡面的按鈕功能。

Sometimes, we might get a report with a column, which people "know" they are date but Excel not. It is very common especially as the file is from other notions. We can only use "format" or press "Enter" again in the cell to make Excel know them.

Just 'couse I had taught someone at work. Record this since I didn't know how to do it two years ago. (Excel, you are an easy to use, but hard to remember tool with a lot of "tips" hidden :(  Haha~~) 【中文】 因為日前在工作上剛好需要教人這功能，順便記錄下來，畢竟兩年前的我也根本不知道這功能怎麼用。 （題外話：Excel您真是簡單好用、但不易記得的好工具，但卻有一堆隱藏撇步啊～）

As I wrote in the last post, we can setup "hotkeys" for some macros (VBA Sub or Function) in Excel. It avoids the needs to create buttons or setup events in objects just to trigger those. It also brings the "actions" to other Excel files you have also opened.

When processing data in Excel (especial inputing values & outputting as reports), we might require (sometimes just prefer) some cells with all upper case letters (ex: ABC instead of Abc). In Word, we have a function to do so in the toolbar. In Excel, can you expect us to replace one letter by another?

There is one daily task from my colleague that she has to input a lot of values from Word files. The content of Word files is in simple form (table-like) format. However, the providers refused to provide in Excel files, which have further using possibility. So she would print out Word files in hard copy and input values into a fixed formatted Excel file.