Excel VBA – 讓 User Form 右上角出現放大縮小按鈕的方法

在撰寫 Excel VBA 時,經常會用到 User Form 來製作呼叫面板,尤其是巨集準備要給別人使用的時候,有一個 User Form 可以省去很多麻煩。 User Form 雖然方便,但在設定上卻很死板,像是各種瀏覽器一定會有的放大縮小功能,沒有內建的選項可以設置: 透過程式的方式可以加以控制,例如這裡的 User Form 程式碼範例。 這樣使用起來其實不是很方便,有沒有方法可以在 User Form 上增加常見的放大縮小按鈕呢? 當然有,利用下面的程式碼,貼上到 User Form 當中可以了! Private Declare Function FindWindow Lib “user32″ Alias “FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long Private Declare Function ShowWindow Lib “user32″ (ByVal… Continue Reading

Excel 交互參照必學神器, 用 lookup 系列函數一鍵搞定資料比對 : hlookup篇

延續上一篇的 vlookup 教學文章,藉由先給定參照值,再於指定範圍內尋找符合條件的資料並傳回指定「欄位」的對應數值,藉著介紹的 hlookup 也大同小異,但找到符合條件的資料後卻是傳回指定「列」的對應數值。 由於大部分資料庫匯出的格式,欄位代表的是變數,列資料代表的是樣本(或筆數),以至於絕大部份時候會用到的是 vlookup ,因為我們希望回傳的通常是同一筆樣本在不同變數的數值,這樣就能夠在不同的資料庫(例如客服中心電話紀錄 vs 銷售紀錄)根據相同的 key 值(例如客戶編號),建立一個新的資料集或摘要表來使用,但有些時候 hlookup 也是可以派得上用場,像是典型的實驗設計表格。 範例表格如下,這是一個飲料好感度盲測研究的實驗資料。 假如我們想把指定的資料填入一組如下方的特定清單,該怎麼做呢? 當然就是要透過 hlookup 囉! 如同 vlookup , hlookup 也擁有 4 個參數,唯一的差別是傳回值的參數,在 vlookup 中是「Col_index_num」,可指定要傳回數值的垂直範圍,在 hlookup 中則是「Row_index_num」,用來指定要傳回數值的水平範圍。 Lookup_value 想要指定的參照值在此設定,以本例而言,是各廠牌的飲料名稱,先選擇摘要工作表中的 A1:A10 範圍作為參照值。 Table_array 接著指定要被比對的資料範圍在此設定,至少需要包含兩列(Row),一列用來和參照值比對,另一列則是提供要回傳的資料。以本例而言,選擇 A1:K5 的範圍。 Row_index_num 針對被比對的資料範圍(根據 Table_array 設定的範圍),指定要回傳的值,指定的方式是選擇所要回傳的「第幾列」的資料,順序由上至下。以本例而言,希望回傳盲測組 C 的評分,也就是第 4 列,填入數字 4。 Range_lookup 這是一個邏輯判斷式,填入「TRUE」or「FALSE」以指定選擇的標準。若填上 FALSE… Continue Reading

Excel 交互參照必學神器, 用 lookup 系列函數一鍵搞定資料比對 : vlookup篇

有一陣子沒有寫部落格了,最近遇見很多年輕的朋友,發現許多人等到進入職場以後,才發現原來職務說明中所謂「具備運用 Excel 的能力」,並不是指能夠打打加減乘除而已,只有學會了運用 Excel 的函數,才能在資料分析時達到事倍功半的效果,也才算是真正會用 Excel。 正因如此,就來分享一下 Excel 的使用技巧吧。 Excel 當中主要的函數列表,可以參考 Microsoft 提供的清單(按此)。在官方的分類底下,有一類就叫做「查閱與參照函數」,相同的分類也可以在各版的 Excel 函數說明中找到,可惜對於 Excel 使用不深的人,比起「統計」、「財務」等類別,這是一個經常會被遺忘的函數集,不過若以泛用性而言,參照函數或許才是各 Excel 課程首先必須著墨的部分。 不得不說,它真的太重要啦,在企業中 Excel 的使用量很大,就算自己不用,你的客戶或供應商八成也都用得很兇,而且 Excel 的應用範圍也很廣,幾乎所有部門都能用得到,各種專案合作中只要看到別人該用卻不用對應的函數,只靠手動製作資料摘要,我就會忍不住內心嘆氣+翻白眼,因為這實在是太浪費人力了,畢竟好人才的時間成本可是很貴的啊!! 各位看官,介紹參照函數必學第一名,就是大名鼎鼎的「vlookup」函數。 假如你有以下的狀況…: 必須同時管理許多個部份資料有重疊的工作表 沒有任何單一工作表包含所有要用到的資料欄位 工作表的資料筆數很多,不太可能花時間人工一一對照 那麼再不學 vlookup ,就真的太遜啦! 還不快看看底下的 vlookup 教學! 本文用氣象局的觀測資料為例,範例包含兩個工作表各自包含「酸雨PH值」及「雨量」的資料,其中「地點(鄉鎮名)」是兩個資料集的共同點,如下圖所示: 這裡另外加入一個獨立的工作表,包含了鄉鎮名稱資料方便檢視結果。 假如我們想要建立一個包含鄉鎮、雨量以及酸雨PH值的資料集(如 test 工作表的範例),那麼該怎麼做? 如果不利用 Excel 函數,那麼恐怕我們就得一筆一筆資料尋找,然後複製貼上了,假若只有幾個地點那倒無所謂,但是面對數百個鄉鎮地點,顯然最好的辦法不是手動。藉由 vlookup 函數,我們可以設定一組參照值,然後讓公式根據參照值,在給定的工作表每一列當中尋找符合參照值的資料,回傳設定好的欄位所對應的數值。 然後呢? 點兩下複製公式到所有格子就完成了! 就這麼簡單。 vlookup 共有幾個參數要設定,一一說明如下:… Continue Reading