《EXCEL讲义》PPT课件.ppt

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(row()/column(),2) 設定格式化條件/索引標籤色彩 針對大量資料，選定範 圍後設定條件，統一予 以某種格式，不過只能 設定三個格式化條件 索引標籤色彩：限 Office XP以上版本，當 有數十個工作表，或是 需要將某些屬性相關或 類似的工作表標記時， 是很好用的顏色管理工 具 EXCEL隱藏技巧 (1).隱藏單元格內容 設置格式-自定義-;-保護-隱藏 工具-保護保護工作表 取消:工具-保護-撤銷設置格式(or編輯-清除-格式) (2).隱藏行列 選擇要隱藏的列資料群組及大綱-群組 複製時加上“alt+;”即可複製被隱藏的行列 (3).隱藏工作表 工具-巨集-vb編輯器-visible=2 Ch.2資料分析 從篩選、排序到小計、驗證、樞 紐分析 自動篩選-自訂 關鍵：瞭解 且、或 的含意 1. 前10項(%)篩選 2. 篩選到別的地方 進階篩選 文字資料是沒有 所謂的前10項 (%)篩選法的， 只有數字資料才 可以 進階篩選-顯示在原有範圍 準則在同一列： OR 準則在不同列： AND 標題列也 要選取 進階篩選-顯示在其他地方 複製到指 定的標題 欄列位置 恢復原狀是全部顯 示或刪除？ 可篩選對 應特定欄 位，而非 全部顯示 只能將資料複 製到作用中的 工作表，不能 跨表 資料處理的組合：排序+小計 排序是為了將資料分類 ，如果沒有排序逕行小 計，所有資料將就現有 順序進行無效小計 小計若只進行一次，是 為單層，二次以上（取 代目前小計）是為多層 ；最好先欄位順序規劃 好再排，否則結果會很 奇怪（由左到右，層次 由高漸低） 四個大綱層級 ，請自行測試 每個的效果 小計意義不明，是因為 先排後面的欄位（單層 ），再排前面欄位（多 層）的結果 資料處理的組合：排序+篩選+小計 篩選後的小計是沒有層 次的，要讓哪些欄位呈 現意義，就要先排那些 欄位 操作說明： 先排序，進行分類 篩選，選出所要的資料 對篩選以外的欄位進 行小計 復原：此時資料已變更， 不能直接移除自動篩選 ，而且無法復原先移 除小計，再取消自動篩選 無效的小計 較有意義的小計： 排序(代表商+分類)篩 選(代表商)小計(分類) Ch.3合併彙算,群組與大綱 合併彙算 合併彙算是針對若干有共 同欄列資料的項目，進行 整合成一份彙整過的報表 。若設定動態連結，則會 產生群組大綱 進行彙算後，再一次彙算 ，則構成所謂的多層次 彙算 雖說資料欄列不一致仍可運 算，但仍必須有對應關係， 運算起來才有意義 如果格式完全固定，亦可透 過立體運算，例如： =SUM(1Q:4Q!G2) 群組與大綱 將某些具有相同屬性的資料合 併在一起，構成群組資料關係 ，則可以透過群組、大綱模式 ，予以彙整 某些資料本身不一定具有階 層關係（如：小計、合計等 ），但可能有從屬關係，因此 亦可用手動建立大綱 以自動建立大綱/設定模式 建立，但基本上仍須自行 建立計算項目，並非選擇 項目即會自己去判斷加總 清除大綱去除大綱模 式 ch4.決策分析輔助工具 目標搜尋 規劃求解 運算列表 分析藍本 目標搜尋 不是指找資料，而是設 定一結果（目標值）， 反推產生該結果的原因 （變數） 和一般自設公式不一樣 的地方，在於推論順序 相反(由Y求X) 其他變數必須固定為常 數，例如本例中的季節 因素固定為1.1，以及其 他已知的常數 第二列的公式仍要先 行輸入，並不會因為 設定目標儲存格、目 標值(Y)，就會自動 產生變數(X) 數值會自 動改變 規劃求解 以數值分析之技術 ，透過試誤（trial A2:D2中分別輸入 10,20,30,40,50,60,70,80 以及要在A5:D5中得到分別相加的結 果. 常用函數介紹 If Countif Sumproduct Vlookup Hlookup Year/month/date 設置提醒 Sub AUTO_OPEN() Application.OnTime TimeValue(“16:40:00“), “TX“ Application.OnTime TimeValue(“16:45:00“), “TX“ End