EXCEL讲义

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