《《EXCEL讲义》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《EXCEL讲义》PPT课件.ppt（28页珍藏版）》请在金锄头文库上搜索。
1、EXCEL中階應用NG312 葉娟HannStar 2007總覽EXCEL常用小技巧EXCEL資料分析EXCEL合併彙算,群組與大綱決策分析輔助工具EXCEL常用函數介紹EXCEL 製作小遊戲EXCEL 生成可執行文件Ch 1.常用小技巧EXCEL常用小技巧1.快速輸入平方和立方符號2.快速輸入分數3.自動換行4. 祿入固定數劇(資料驗證-設定,以逗號隔開)5.避免輸入重複數劇(資料驗證,自定義即可)6.中級聯菜單的實現7.巧用用條件格式8.隱藏小技巧中級聯菜單的實現1)建立分類-插入-名稱-定義2)在要分類的表單里 資料-驗證-允許值 =INDIRECT(B1)用條件格式讓行列更清晰Mod(
2、row()/column(),2)設定格式化條件/索引標籤色彩針對大量資料，選定範圍後設定條件，統一予以某種格式，不過只能設定三個格式化條件索引標籤色彩：限Office XP以上版本，當有數十個工作表，或是需要將某些屬性相關或類似的工作表標記時，是很好用的顏色管理工具EXCEL隱藏技巧(1).隱藏單元格內容 設置格式-自定義-;-保護-隱藏 工具-保護保護工作表 取消:工具-保護-撤銷設置格式(or編輯-清除-格式)(2).隱藏行列 選擇要隱藏的列資料群組及大綱-群組 複製時加上“alt+;”即可複製被隱藏的行列(3).隱藏工作表 工具-巨集-vb編輯器-visible=2Ch.2資料分析從篩
3、選、排序到小計、驗證、樞紐分析自動篩選-自訂關鍵：瞭解且、或的含意1.前10項(%)篩選2.篩選到別的地方進階篩選文字資料是沒有所謂的前10項(%)篩選法的，只有數字資料才可以進階篩選-顯示在原有範圍準則在同一列： OR準則在不同列： AND標題列也要選取進階篩選-顯示在其他地方複製到指定的標題欄列位置恢復原狀是全部顯示或刪除？可篩選對應特定欄位，而非全部顯示只能將資料複製到作用中的工作表，不能跨表資料處理的組合：排序+小計排序是為了將資料分類，如果沒有排序逕行小計，所有資料將就現有順序進行無效小計小計若只進行一次，是為單層，二次以上（取代目前小計）是為多層；最好先欄位順序規劃好再排，否則結果
4、會很奇怪（由左到右，層次由高漸低）四個大綱層級，請自行測試每個的效果小計意義不明，是因為先排後面的欄位（單層），再排前面欄位（多層）的結果資料處理的組合：排序+篩選+小計篩選後的小計是沒有層次的，要讓哪些欄位呈現意義，就要先排那些欄位操作說明：先排序，進行分類篩選，選出所要的資料對篩選以外的欄位進行小計復原：此時資料已變更，不能直接移除自動篩選，而且無法復原先移除小計，再取消自動篩選無效的小計較有意義的小計：排序(代表商+分類)篩選(代表商)小計(分類)Ch.3合併彙算,群組與大綱合併彙算合併彙算是針對若干有共同欄列資料的項目，進行整合成一份彙整過的報表。若設定動態連結，則會產生群組大綱進行彙
5、算後，再一次彙算，則構成所謂的多層次彙算雖說資料欄列不一致仍可運算，但仍必須有對應關係，運算起來才有意義如果格式完全固定，亦可透過立體運算，例如：=SUM(1Q:4Q!G2)群組與大綱將某些具有相同屬性的資料合併在一起，構成群組資料關係，則可以透過群組、大綱模式，予以彙整某些資料本身不一定具有階層關係（如：小計、合計等），但可能有從屬關係，因此亦可用手動建立大綱以自動建立大綱/設定模式建立，但基本上仍須自行建立計算項目，並非選擇項目即會自己去判斷加總清除大綱去除大綱模式ch4.決策分析輔助工具目標搜尋規劃求解運算列表分析藍本目標搜尋不是指找資料，而是設定一結果（目標值），），反推產生該結果的原
6、因（變數）和一般自設公式不一樣的地方，在於推論順序相反(由Y求X)其他變數必須固定為常數，例如本例中的季節因素固定為1.1，以及其他已知的常數第二列的公式仍要先行輸入，並不會因為設定目標儲存格、目標值(Y)，就會自動產生變數(X)數值會自動改變規劃求解以數值分析之技術，透過試誤（trial & error）去解線性規劃、不等式，適合在有多變數、限制式的情況下解答規劃求解的解答未必精準，或可能非最佳解，必須進行多項條件、起始值的測試，才可能出現最佳解兩個限制式，本例中因為加班費與獎金是有額度限制的與目標搜尋同，規劃求解的目標儲存格也要先輸入公式雖然目標是60000，但因有限制式，規劃出來可能是一
7、近似解運算列表意義與目標搜尋相反，運算列表是已有明確的變數，推算出可能的目標結果（X1, X2已知，推算新的Yn值）用法：假設變數已知，並且配合某一公式/函數去推論各變數代入公式/函數後的值操作說明：擬處理的資料均要選取指定欄變數、列變數先在已知變數值（月存款、年利率）計算得十年本利和（以FV計算）運算列表就是在代入各變數值後，取得一系列的目標值目標資料呈陣列方式儲存，不能針對單一資料修改分析藍本藍本：限制式的組合運算列表適合兩個以下變數的運算，分析藍本用以處理三個以上變數的問題Ch5.常用函數數組函數選中需要輸入數組公式的區域,輸入公式,按shift+ctrl+enter即可Ex:要想在A1
8、:D1中分別輸入10,20,30,40.或要在A1:D1;A2:D2中分別輸入10,20,30,40,50,60,70,80以及要在A5:D5中得到分別相加的結果.常用函數介紹IfCountifSumproductVlookupHlookupYear/month/date設置提醒Sub AUTO_OPEN()Sub AUTO_OPEN()ApplicationApplication.OnTime TimeValue.OnTime TimeValue(16:40:00), TX(16:40:00), TXApplicationApplication.OnTime TimeValue.OnTime TimeValue(16:45:00), TX(16:45:00), TXEnd SubEnd SubSub TX()Sub TX()MSG =MSG = MsgBox MsgBox( (趕快寫日誌趕快寫日誌, , vbInformationvbInformation, , 日誌提醒日誌提醒) )End SubEnd Sub製作小遊戲以用excel製作萬年歷為例Thank You!
