企划好用的几个excel功能.docx

企劃好用的幾個excel功能內容企劃好用的幾個excel功能1功能表篇1smartArt1選取物件3清單(下拉式選單)3繁簡轉換5轉置(直橫轉換)6移除重複8共用活頁簿8巨集(VBA)9function篇10儲存格10計算11Sum11Average11Int11round & rounddown & roundup11count & countA & countIF12邏輯13If13And13Or13文字14concatenate14mid14left14right14尋找15Vlookup15Hlookup16應用16自訂函數16建立自訂函數17建立更聰明的_自訂函數18功能表篇Excel功能表裡的功能有許多所以眾所周知的、很少人會用到的功能，這裡就不提了。smartArt這是一個office很好用的功能，尤其是在作商業簡報時！它提示了許多活動式的圖表供使用者來操作。我們只需要幾個簡單的動作，就可以完成流程圖或架構圖。而這中間的過程，一律都是採用圖形化介面，不用擔心不會用的問題。選取物件這是一個使用圖表、匯入圖片、繪圖時常會用到的功能。當我們需要一次選取很多圖形時，使用這功能，它就會把滑鼠變成選取物件的游標！這時我們可以使用拖曳框選的功能，把我們要的圖形給一次選取起來，而不用擔心會點到儲存格的問題。清單(下拉式選單)應該會有人想過這個問題：如果儲存格內容也可以用下拉式選單，那該有多好？實際上，excel是有這功能的！在資料驗證的這個地方可以進入操作！點它之後會開啟下面這個視窗：這時我們只要在這選清單，然後在下面來源的部份填入清單的範圍： =活頁簿名稱!儲存格範圍 (例如：=Sheet2!A:A) 之後我們再到那個指定的範圍內，填入清單的選項就可以了！繁簡轉換這是一個簡單又好用的功能，只要點一下，就可以把繁簡體字進行切換。轉置(直橫轉換)如果我們有一串直的儲存格要貼成橫的怎麼辦？這時就會用到這功能！先把要轉置的儲存格用ctrl+c複製起來！然後在要貼上的地方按右鍵，選選擇性貼上。(不可以貼在來源儲存格的位置)把轉置的地方打勾，然後按下確定！移除重複如果說我們要把重複的資料刪除怎麼辦？只要使用移除重複的功能就可以了。先把要處理的範圍選起來，然後再按移除重複即可。這功能也可以用來移除多的空白儲存格。共用活頁簿這是一個提供多人同時編輯一份excel表的功能！當我們的表格放在公有資料夾的時候就可以用到。若沒有開啟這個功能，第2個人開啟文件時會呈現唯讀的狀態。點擊共用活頁簿把允許多人同時修改活頁簿的功能打勾，然後按確定。需要特別注意的是，使用這功能，必需要在信任中心把權限打開才可以！詳細的內容可以參考這個網址：/zh-tw/excel-help/HP010096833.aspx巨集(VBA)巨集是一個好用且重要，但是又相當進階的功能。由於內容十分龐大，所以在這裡就不講了。基本上在功能表或function中找不到的功能，都可以透過巨集來完成。比方說按一個鈕就可以把一份表單填寫完成；或是比對兩份表格，把其中不一樣的地方都標上特定顏色等功能。基本上，寫巨集就是在寫程式。function篇function是excel中用來進行特別運算用的功能。雖然excel有強大的公式運算功能，像是+、-、*、/、n(次方根)等，但是面對一些比較複雜的運算處理，仍然需要透過一些function來完成會比較方便！就好比我們在數學中會使用sin()、tan()來進行三角函數的運算表示一樣。儲存格在function中，使用儲存格來進行運算是常見的行為！通常主要的目的是把儲存格當作變數用。使用方式如下：當我們要把A1儲存格的內容定義為B2+B3時，那我們可以在A1中填入 = B2 + B3其中，前面的 = 是必要的！它用來表示這是一個用來運算的儲存格。若是我們要表示一個範圍的儲存格，可以這樣用：(A1:B10)，表示A1B10這個範圍的內容(A:A)，表示A1A65535這個範圍的內容(1:1)，表示A1ZZ1這個範圍的內容在這裡特別要提的是$的功能。在我們使用儲存格的時候，時常會有使用拖曳的方式來複製儲存格。但是這個功能，也會改變我們儲存內的座標！這時就需要用到$了它是用來鎖定座標用的。比方說：=$C1，表示C被鎖定了，所以變動時它只會在C1C65535的範圍內。=C$1，表示1被鎖定了，所以變動時它只會在A1ZZ1的範圍內。=$C$1，表示C跟1都被鎖定了，所以不論怎麼複製儲存格，它的公式都會留在C1。 計算Sum這是用來加總數值用的函數。舉個簡單的例子：=SUM(C13:F20)，它會加總C13F20這個區塊所有的數值=SUM(C13:F13,B3:F7) ，它會加總C13F13，以及B3F7這2個區塊所有的數值當我們有許多變數要作加總的時候，這是一個很實用的函數。Average跟sum 的功能很像但它計算的是平均數。舉例：=average(C13:F20)，它會平均C13F20這個區塊所有的數值= average (C13:F13,B3:F7) ，它會平均C13F13，以及B3F7這2個區塊所有的數值Int這是一個用來處理數值轉換的函數，作用是把任意數變成整數(去小數)。用法是這樣：=int(123.567)，會計算出123=int(B2)，如果B2=123.567，會計算出123round & rounddown & roundup這也是用來處理數值轉換的函數Round用來做四捨五入Rounddown用來做無條件捨去roundup用來做無條件進位用法是這樣的：=round(A,B)A填入指定的數值或儲存格B填入要四捨五入的小數位數，0表示小數點之後四捨五入rounddown & roundup 的用法跟round一樣。count & countA & countIF這用來計數的函數count用計算指定的範圍內有幾個包含數字的儲存格數量，用法跟sum是一樣的。countA用計算指定的範圍內有幾個非空白的儲存格數量，用法跟sum是一樣的。countIF用來計算符合指定條件的儲存格數量。用法是這樣的：=countif(A,B)A填入指定的數值或儲存格範圍B填入要比對的內容或儲存格。比方說我們要知道A1A10範圍中，內容等於”boss”的儲存數量，我們可以這樣填寫：=countif(A1:A10,”boss”)或是我們要知道A1A10範圍中，有幾個重複的內容，我們可以這樣填寫：AB1abc=COUNTIF(A:A,A1)2acd=COUNTIF(A:A,A2)3asdf=COUNTIF(A:A,A3)4sdf=COUNTIF(A:A,A4)5sdf=COUNTIF(A:A,A5)6abc=COUNTIF(A:A,A6)7acd=COUNTIF(A:A,A7)8acd=COUNTIF(A:A,A8)9sdf=COUNTIF(A:A,A9)10asdf=COUNTIF(A:A,A10)邏輯If這是很常被使用的式子，尤其是當我們需要透過excel來快速判斷儲存格內容的時候。用法是這樣的：=if(A=B,C,D)A=B，填入要用來判斷的式子，比方說A1=123，或是A1=B1C，如果A=B成立的話要顯示的內容，可以是數值或文字或運算式。若要填入運算式，前面的=不需要填。D，如果A=B不成立的話要顯示的內容，可以是數值或文字或運算式。若要填入運算式，前面的=不需要填。這裡額外要提的是運算符號，有=、=、=、幾種。其中的 =、=、=在數學中很常被使用，就不提了。而其中 代表的意思是不等於。And這個函數經常被拿來跟if 一起使用。當我們要判斷兩個以上的條件都要成立時，就需要用AND來把2個條件連結起來。用法是這樣的：=if( and(AB,CB)，或是(CB1,mid(A1,2,5),left(B1,2)。這意思是：如果A1B1，那就取出A1中間25個字元，否則就取出B1左邊2個字元。或是=concatenate(mid(A1,2,5),if(B2233,round(B2,0),rounddown(B2,0)這意思是取出A1第25個字元(假設為1010)；如果B2233，則四拾五入B2，否則無條件捨去B2(假設得出的結果是23)；之後再把A1算出來的結果跟B2算出來的結果前後接在一起。(結果為101023)自訂函數通常自訂函數，是用於我們需要的函數excel不提供時。但其實對擅用excel函數的企劃來說，這樣的狀況並不多見。不過有另一種情形就很常見了！像是我們寫在戰鬥公式時以上圖為例：n 如果怪的攻擊類型是物理，那麼u 攻擊力 = int(力量 * (1+敏/2/100) * (1+體/3/100)+武器物攻n 反之如果是魔法u 攻擊力 = int(智力 * (1+敏/2/100) * (1+體/3/100)+武器魔攻寫成公式就變成了：=IF(F2=物理,INT(I2*(1+J2/2/100)*(1+K2/3/100)+G2,INT(L2*(1+J2/2/100)*(1+K2/3/100)+H2)這式子，剛寫的時候還看得懂但是過了一陣子之後再讀，眼睛都花了。成長曲線及經濟平衡的驗算式可能還要更複雜！相信許多人在過了幾個月之後再去讀這公式，都會想要哭出來。這時候，我們就可以把自訂函式給用上！建立自訂函數順序如下：1 首先，按下alt+F8，就可以開啟巨集UI2 在巨集名稱中隨便打個英文字串(比方說abc)，再按建立。就可以進入巨集的編輯。這時會看到VBA編輯器中，有以下文字：Sub abc()End sub3 這時我們把內容給改掉，改為：Public Function abc()End Function就成了我們要的函數結構了。4 接下來，我們以上述的公式為例，建立物攻(p_atk)、魔攻(m_atk)2個函數Public Function p_atk(力量 As Integer, 敏捷 As Integer, 體質 As Integer, 武器物攻 As Integer, 註解 As String) p_atk = Int(力量 * (1 + 敏捷 / 2 / 100) * (1 + 體質 / 3 / 100) + 武器物攻End FunctionPublic Function m_atk(智力 As Integer, 敏捷 As Integer, 體質 As Integer, 武器魔攻 As Integer, 註解 As String) m_atk = Int(智力 * (1 + 敏捷 / 2 / 100) * (1 + 體質 / 3 / 100) + 武器魔攻End Function以上，這是2個函數。4.1 第1行的意思是，建立一個名字p_atk的函數。內容可以塞入4.1.1 力量(為整數Integer)4.1.2 敏捷(為整數Integer)4.1.3 體質(為整數Integer)4.1.4 武器物攻(為整數Integer)4.1.5 註解(為字串String)4.2 第2行為公式。我們告訴程式p_atk = ？。程式才能把算好的值給我們。4.3 第3行為函數的結尾。4.4 第46行內容為m_atk的函數建立。跟上面是一樣的。5 現在函數建好了，我們也可以使用它了。在A1中填入公式=P_ATK(I2,J2,K2,G2,物攻公式)程式就會自動把結果給算好。這時應該有人納悶，為什麼要加入一個註解字串？它又不在公式裡。因為，這是自訂函數，所以難免日後可能會看不懂。所以我們可以這樣子加入註解。甚至還可以把整個公式當註解打進去。註：存檔時，記得要存為xlsm的格式!建立更聰明的_自訂函數函數還能不能更聰明？要一個個的把參數塞進去，好麻煩啊！如果能有更懶的方法多好？比方說，在A1這樣填寫，讓程式自動去判斷武器類型，來填入參數及公式： =ATK(E2:K2,攻擊力計算)答案是：有的！可以這樣寫：Public Function atk(arr As range, 註解 As String)武器類型 = arr(1)武器物攻 = arr(2)武器魔攻 = arr(3)力量 = arr(4)敏捷 = arr(5)體質 = arr(6)智力 = arr(7) If 武器類型 = 物理 Then atk = Int(力量 * (1 + 敏捷 / 2 / 100) * (1 + 體質 / 3 / 100) + 武器物攻 Else atk = Int(智力 * (1 + 敏捷 / 2 / 100) * (1 + 體質 / 3 / 100) + 武器魔攻 End IfEnd Function1 先建立一個叫作atk的函數。裡頭塞入1.1 arr (為一個範圍 range)1.2 註解(為字串Strin