發表文章

最神奇的VLOOKUP

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

Excel版本與檔案格式

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

Excel技巧(2):拜託殺了那些看不見的空白吧!

看過儲存格內怎麼看明明都是數字沒錯,可是卻怎麼樣都沒有辦法用它來做任何運算吧? 就算使用很基本的公式(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) Then                 cnt = cnt + 1             Else                 If cnt >

EXCEL 公式進階(1):OFFSET

圖片
請先閱讀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技巧(1):檔案肥大的原因,附上減肥撇步

圖片
應該遇過:明明 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 公式入門(1):文字篇

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