資料分析

Calc 使用手冊 第十三章資料分析 使用目標搜尋 解決方案等

內容目錄 介紹... 1 合併計算... 1 建立小計... 3 使用分析藍本... 4 建立分析藍本... 5 設定... 6 顯示邊框於... 6 複製回來... 6 複製整個試算表... 6 避免變更... 7 使用分析藍本... 7 提供其他版本的公式... 8 多重運算在欄或列... 8 一個公式和一個變數的計算... 9 多個公式和一個變數的計算... 10 欄與列的多重運算... 11 用兩個自變量計算... 11 目標搜尋... 12 使用解決程式... 15 i

介紹 一旦您熟悉了函數和公式的應用, 下一步就是學習如何自動完成和顯示經過分析的有用資料 Calc 包括幾個工具, 協助您操作函數和公式, 並且使用資料自動建立小計, 提供不同的資訊讓您得到需要的答案, 這些工具分佈於工具和資料的功能表中 若您是試算表的新手, 這些工具能夠在最開始時克服困難, 只要您記得, 所有的功能都是依儲存格或範圍儲存格內輸入的資料進行工作 您永遠都能手動輸人儲存格或範圍儲存格資料, 但在許多情況下, 當您以滑鼠選擇儲存格您時, 也可以使用欄位旁的縮小 / 放大圖示, 暫時更改工具視窗的大小 有時, 您也許有這個經驗 : 哪一個資料要放進哪一個欄位, 只要認知每一個工具基本的目的時, 使用 Calc 函數工具中就會減少很多麻煩 若您的試算表使用是熟悉的, 其實您不需要特別的學習這些工具的使用, 但當您操作的資料變的複雜時, 這些工具在計算時就可節省很多時間, 特別是當您開始處理假設的情況時, 另一個非常重要功能, 它們能讓您保護工作同時, 與其他人分享, 這個部分後面章節會提到 所有工具中, 只有其中一項 - 資料助理 ( 樞紐分析表 ) 不會在此章提到, 有需要請參考 Calc 使用手冊第 5 章 ( 資料助理 ) 合併計算 功能表 資料 合併計算 提供一個圖形化介面, 將一個範圍儲存格的資料, 經由函數執行計算整理, 複製至另一個工作表儲存格中 在合併計算功能中, 不同工作表中的儲存格內容能夠在一張工作表中合併 1) 開啟將被彙整計算, 含有儲存格範圍的文件 2) 選擇功能表 資料 合併計算 開啟合併計算對話方塊 圖 1: 合併計算對話方塊 1 合併計算

3) 若來源資料範圍清單中含有名稱範圍, 您可以選擇一個儲存格來源範圍, 與其他區域合併 若來源資料範圍並沒有名稱定義, 只要在右方範圍框中輸入範圍, 或是點擊清單框中右方的縮小鈕, 使用滑鼠於工作表中進行選取 4) 點擊增加鈕, 所選取的範圍將出現在合併計算範圍清單中 5) 選擇其他資料範圍, 每一個範圍選取完畢, 按增加鈕將範圍加入合併計算範圍中 6) 在複製結果到文字方框中選擇您想顯示結果的名稱範圍 若目標範圍並未定義名稱, 點擊複製結果到文字方框右方的欄位, 輸入目標範圍或按右方的縮小鈕, 至工作表中使用滑鼠點選目標位置 7) 在計算規則下拉式選單中選擇函數, 這個函數會指定合併範圍的數值進行什麼樣的動作, 預設值為小計的功能 8) 最常被使用函數是統計類的函數 ( 例如 : 平均值 最小值 最大值 標準差等 ), 合併計算這個工具最有用的地方是快速處理每個工作表間相似而且經常性的資料, 以範例而言 : 公司的費用支出每一季結算一次, 年底時再做每季總結, 即然每季已進行結算, 年底總結時, 只要在合併計算中指定每季支出範圍, 就可輕鬆進行加總, 以取代手動統計加總的工作 9) 點擊合併計算對話方塊右下方的其他鈕進行進一步的設定 : 1. 在選項區中勾選和來源資料連結, 在目標範圍中以公式產生結果, 而非直將結果插入, 若您連結資料時, 任何來源資料範圍中的數值變動時, 合併計算的的目標範圍會自動更新 2. 在合併按照區下, 如果來源資料範圍與合併計算範圍並不一致, 就可選擇按照列標題或是欄標題合併 進行合併計算時, 標題必須包含在選定的來源範圍中, 標題中的文字必須是相同的, 這樣的欄或列才可以精確匹配, 若欄或列的標題與目標範圍中的標題並不符合時, 將會新增一個新的欄或列 圖 2: 更多選項設定 10) 點擊確定鈕進行合併計算 當您儲存文件時, 合併計算範圍和來源資料範圍是同時被儲存的, 若您後來開啟一個已經定義為合併計算的文件, 這些資料仍可被使用 合併計算 2

建立小計 小計在使用函數精靈 ( 插入 函數 ) 時是一個被列為數學分類的函數, 因為它常常被使用到, 於是在功能表 資料 小計 中, 這個函數有一個圖形介面的使用方式 就如這個名稱建議的, 群組小計是所有的資料被安排在一個群組中 - 這是一個含有欄列標題的儲存格的集合, 使用小計對話方塊, 您可以選擇群組, 然後選擇套用一個統計函數, 更有效率的作法, 您可以同時選擇三個群組並套用函數, 當您點擊確定鈕時,Calc 加入小計和總計至選擇的群組中, 並且使用結果 1 和結果 2 的儲存格樣式顯示 插入小計的數值至工作表中 : 1) 確定包含欄標題 2) 選擇想要計算小計的儲存格範圍, 然後選擇功能表 資料 小計 3) 在小計對話方塊, 於依群組下拉式選單中選擇加入小計的欄位, 若被選擇的欄改變內容, 小計會自動的重新被加總 4) 在小計用於清單中, 選擇含有您想要進行小計的欄 5) 在計算規則清單中, 選擇使用於小計計算的函數 6) 點擊確定鈕 圖 3: 小計對話方塊 3 建立小計

若您使用不只一個群組, 您也可以在選項標籤頁中進行設定, 包括了向上 向下排序方式, 或是使功能表於 工具 選項 OpenOffice.org Calc 排序清單, 以使用者自訂的排序規則進行排序 圖 4: 小計選項對話方塊 使用分析藍本 OpenOffice.org Calc 分析藍本是用於計算的一組儲存格值 在相同試算表上可定義數個分析藍本, 每個分析藍本在儲存格中具有不同的值, 為試算表上的每個分析藍本指定一個名稱, 接著您可以輕鬆透過名稱, 在各組儲存格間切換, 並立即檢視結果 分析藍本是充分檢驗 假設 問題的工具 每一種藍本均可獨立的被命名 編輯和格式化, 並且在助手功能標題列的下拉式清單中被選取, 當您列印試算表時, 只有目前作業的藍本內容會被列印 新增一個藍本, 您可以快速的更改公式的參數和檢視新的結果, 例如 : 您想要計算不同投資的投資報酬率, 此時您可以為每一種利率加入一個藍本, 並且快速的檢視結果, 若您有另外的公式計算每年的收入, 而且其中包括了以利率做為計算公式參數的結果, 這個收入的計算也能夠被更新, 若所有您收入的來源均使用藍本分析的方式, 您可以很有效率的建立複雜的模型來估計可能的收入 使用分析藍本 4

建立分析藍本 想要建立一毎分析藍本, 選擇所有提供分析藍本的資料儲存格, 我們以範例來直接說明 假設您想要建立公司的預算, 但多不知道本年度公司的銷貨收入值, 此時, 就可利用不同的分析藍本, 來進行假設分析 假設 1: 最差分析藍本 假設 2: 最佳分析藍本 銷貨收入銷資成本毛利 NT$50,000 NT$35,000 NT$15,000 銷貨收入銷資成本毛利 NT$250,000 NT$35,000 NT$215,000 1) 選取所包含的值會隨方案而變更的儲存格, 若要重複選取儲存格, 可以選取選個儲存格時使用 Ctrl 鍵 2) 選擇功能表 工具 分析藍本 3) 在建立分析藍本對話方塊中, 輸入新藍本的名稱, 使用的名稱最好能夠清楚辨別藍本, 而不要使用預設的藍本名稱, 這個名稱將顯示在助手功能中, 和工作表標題列中 第一個分析藍本 : 圖 5: 建立最差分析藍本 5 使用分析藍本

第二個分析藍本 : 設定 圖 6: 最佳分析藍本 4) 選擇性的加入一些資訊至註釋中, 當您點擊分析藍本圖示和選取藍本時這個資料將會顯示在助手功能中 5) 在設定選項中勾選有需要的設定, 更多選項的資訊在下一節中介紹 6) 點擊確定鈕, 結束對話方塊, 新的藍本將自動作用 在建立分析藍圖對話方塊最下方包含了幾個選項, 大部分的情況下無需更改預設值 顯示邊框於 在表格中用邊框突出顯示分析藍本 邊框的顏色在此位置右邊的欄位中指定 邊框會有標題列顯示上個分析藍本的名稱 分析藍本邊框右側有一個按鈕, 如果定義了數個分析藍本, 使用該按鈕可以顯示此區域中所有分析藍本的摘要, 您可以從這個清單中, 任意選擇分析藍本 複製回來 複製您變更至使用中分析藍本的儲存格值 如果您未選取此選項, 變更儲存格值時分析藍本不會變更 複製回來設定的行為取決於儲存格保護 試算表保護與避免變更的設定 複製整個試算表 將整個試算表複製到其他分析藍本試算表中 使用分析藍本 6

避免變更 避免變更使用中的分析藍本 複製回來設定的行為取決於儲存格保護 試算表保護與避免變更的設定 如果避免變更選項未選取且試算表未受保護, 您才可以變更分析藍本特性 如果避免變更已選取 如果複製回來選項未選取, 且儲存格未受保護, 您才可以編輯儲存格值 如果避免變更未選取 如果複製回來選項已選取且儲存格未受保護, 您才可以變更分析藍本的儲存格值並將其寫回到分析藍本中 使用分析藍本 在分析藍本加入試算表後, 您可以使用助手功能移至某一個指定的分析藍本, 然後在清單中選取分析藍本 分析藍本可透過 助手 功能選取 : 1. 透過標準工具列上的助手圖示開啟助手視窗 2. 按一下助手功能中分析藍本圖示, 可以看到已定義的分析藍本以及在建立它們時所輸入的註解 圖 7: 助手功能分析藍本 在助手功能中雙擊分析藍本的名稱, 將會套用選取的分析藍本至目前的工作表中 刪除分析藍本, 只要在助手功能中選取欲刪除的分析藍本, 以滑鼠右鍵開啟快顯功能表, 選擇刪除選項 若要編輯分析藍本, 請在助手功能中的名稱上按一下滑鼠右鍵, 然後選擇特性 若要隱藏屬於某個分析藍本之一組儲存格的邊框, 請開啟影響儲存格之每個分析藍本的特性對話方塊, 並清除顯示邊框核取方塊 隱藏邊框也會移除您可從中選擇分析藍本的試算表清單方塊 7 使用分析藍本

追蹤前導參照 : 如果想知道一個數值如何直接影響另外一個數值, 請啟動功能表指令 工具 偵探 向後的追蹤箭號, 您就能夠看到指向受影響的儲存格的箭頭 提供其他版本的公式 就像是分析藍本, 功能表 資料 多重運算 也是一個規劃性工具, 但不像分析藍本, 多重運算工具在相同的儲存格不會呈現不同的版本 多重運算工具建立一個公式的陣列 : 獨立出來的儲存格依據不同的公式而產生不同的結果, 雖然這個工具未被列於函數中, 但這個函數的作用就和其他函數一樣, 讓您計算不同的結果, 而不用單獨的輸入和執行 使用多重運算工具, 您需要兩個陣列的儲存格, 第一個陣列包含了公式套用的原始或預設值, 而且公式必需包含在儲存格範圍中 第二個陣列是公式陣列, 由一個或二個原始替代數值所輸入的變數而被建立, 一旦變數被建立, 您使用多重運算工具指定使用的公式, 原始的數值就會被變數取代, 最後的結果是使用每一個變數的公式輸出值 多重運算工具的公式中可以使用任何數值, 但只能存在一個或二個變數 有二種情況 : 只有一個變數時, 可放在欄或列中, 然後在欄變數的右方, 或列變數的下方加入空白儲存格, 每個公式使用變數後的結果就會放置在這些儲存格中, 若是有二個變數時, 一個變數使用於欄, 另一個變數則使用於列 設定多重運算時一開始有可能感到混淆, 例如 : 當使用兩個變數時, 您的選擇需要非常小心, 這樣它們才能建立一個有意義的表格 即使只有一個變數時, 使用者很容易會犯錯或忘記原始陣列中的儲存格和公式列中的儲存格, 在這個情況下, 工具 偵測 功能能夠協助釐清關係 您也可以套用一個簡單的邏輯設計, 將原始和公式陣列中放在同一張工作表中相近的位置, 以及同時使用列和欄的標題, 使公式陣列容易作業 這些在組織設計中的小小練習, 將使公式陣列的作業不會那麼困難, 尤其是當您在校正錯誤或調整結果時 備註 若您匯出的試算表中含有多重運算至 Microsoft Excel, 儲存格的儲置包含了公式時, 必需完整定義資料來源的相對位置 多重運算在欄或列 在您的工作表中, 輸入一個公式, 計算結果的數值 ( 變數 ) 是儲存在其他儲存格中, 然後設定一個儲存格範圖輸入一些修正數值 ( 替代數值 ), 多重運算指令將依公式計算出結果 在公式欄中, 輸入儲存格的參考公式, 並套用至資料範圍中, 在欄輸入儲存格或列輸入儲存格欄中, 輸入相對應的參考儲存格, 而且這個儲存格是公式的一部分 舉個例子來說 : 您是玩具公司的經理, 生產的玩具每個銷售 $10(B1), 成本是個 $2(B2), 除了生產成本外, 固定費用為每年 $10000(B3), 不同的銷售量, 會產生多的的年盈利呢? 以公式來說 : 盈利 = 數量 *( 銷售金額 $10- 銷售成本 $2)- 固定費用 $10000, 因此我們可以知道盈利會隨著銷售數量來變動 提供其他版本的公式 8

圖 8: 範例一個公式和一個變數的計算 1) 要計算盈利, 先輸入一個銷售的數量, 本範例採用數值 2000, 因此盈利的計算方式為 : 盈利 = 數量 * ( 銷售價格 - 單件價格 ) - 固定費用, 現在將這個公式填入儲存格 B5 中 2) 在欄 C 中依序向下輸入預測的年銷售量, 例如, 以 500 為間隔, 從 500 一直輸入到 3000 3) 選取範圍 C2:D7, 也就是欄 C 中的數值和鄰近欄 D 中的空白儲存格 圖 9: 選取多重運算範圍 4) 選擇功能表 資料 多重運算 5) 將游標放置在公式欄位中, 按一下儲存格 B5 6) 將游標置於欄欄位中, 然後按一下儲存格 B4, 這表示公式中的變數 B4 ( 即數量 ), 會由選取的欄值 (C2:C7) 替代 圖 10: 多重運算對話方塊 7) 按確定鈕以關閉對話方塊, 您會看見欄 D 中不同數量的盈利 9 提供其他版本的公式

多個公式和一個變數的計算 如上例加入一個每個銷售量盈利的公式 : 圖 11: 加入另一個公式 1) 在儲存格 C24 輸入公式 : = B24/B23 這個公式計算每個已銷售玩具的盈利 2) 選取範圍 D21:F26, 也就是三欄 3) 啟動功能表 資料 多重運算 4) 將游標置於公式欄位中, 選取儲存格 B24 到 C24 5) 將游標置於欄欄位中, 然後按一下儲存格 B23 圖 12: 多重運算中二個公式 6) 現在按下確定鈕, 關閉這個對話方塊, 在欄 E 和 F 內就會顯示計算出的年盈利和單個年盈利的數值 圖 13: 二個公式一個變數計算結果 提供其他版本的公式 10

欄與列的多重運算 OpenOffice.org 可讓您在 交叉表格 中, 對欄和列執行多重運算, 公式儲存格必須同時參照按列排序的資料範圍和按欄排序的資料範圍 選取由這兩種資料範圍定義的範圍, 並開啟多重運算對話方塊, 在公式欄位中輸入公式參照, 在列與欄欄位中輸入公式之對應儲存格的參照 用兩個自變量計算 若您想知道的不只是年銷售數量的變化, 同時也想瞭解售價改變時, 每一個銷售量的利潤時, 可同時使用兩個變數, 套入一個公式中, 以得出銷售量和售價同時變動時的各種盈利 圖 14: 兩個自變量計算範例 假如您現在想知道在不同的銷售數量和銷售價格下, 其相對應的盈利 現在, 您在以上顯示的試算表內填入一些資料 在區域 C12 和 C15 之中填入銷售數量 500 1000 和 1500 2000 在區域 D11 和 G11 之內分別填入銷售價 8 10 15 和 20 1) 選取區域 C11:G15 圖 15: 兩個自變量的選取範圍 2) 透過功能表 資料 多重運算, 開啟多重運算對話方塊 3) 將游標放置在公式欄位中, 按一下儲存格 B15 4) 將游標置於列欄位中, 然後按一下儲存格 B11 這表示 B11 ( 即售價 ) 為水平輸入的變數 ( 包含 8 10 15 和 20 等值 ) 5) 將游標置於欄欄位中, 然後按一下 B14 這表示 B14 ( 即數量 ) 為垂直輸入的變數 11 提供其他版本的公式

圖 16: 兩個變量的多重運算對話方塊 6) 選取確定鈕以關閉對話方塊 您會看見範圍 D12:G15 中不同售價的利潤 圖 17: 多重運算結果 目標搜尋 工具 目標搜尋 的功能與公式而言, 剛好可以是順序相反 通常情況下, 我們使用公式輸入參數而得到最後的結果, 目標搜尋的工作剛好相反, 是由經由公式得出的結果, 反推回去需輸入什麼樣的參數才能得出這個結果 舉個簡單的例子, 想像一下公司的財務長做了一個未來每季銷售的預估表, 他知道公司每年定下的總營額和成長率, 而且也已經知道今年度前三季的銷售額, 但第四季還未有任何收入, 因此他必須事先預估第四季的營業額多少, 才能達成全年度的銷售目標? 其他使用目標搜尋的情況也許更複雜, 但方法是相同的 執行目標搜尋, 最少需要一個參數值儲存格, 在單一的目標搜尋中, 只有一個參數能被改變, 當您得目標搜尋結果時, 您以結果置換參考儲存格的數值或是記錄這個結果未來才使用 藉由 目標搜尋 可以計算出一個數值, 這個數值是一個公式中的一部分, 並引導至您先前已執行的公式結果, 也就是, 您以多個固定值 一個變量值以及該公式結果定義該公式 我們以多重運算中舉的例子加以說明 ( 圖 18): 在盈利目標為 $6000 下, 請問要銷售多少數量才能達成目標呢? 盈利 $6000(B5) 公式 = 數量 *( 銷售價 $10- 成本價 $2)- 固定費用 $10000 求數量? 目標搜尋 12

圖 18: 目標搜尋範例 使用功能表 工具 目標搜尋 開啟目標搜尋對話方塊, 公式儲存格, 就是盈利儲存格 (B5), 目標值以本例是希望盈利為 $6000, 因為我們要求的是銷售多少數量才有 $6000 的盈利, 因此數量 (B4) 為可變的儲格 圖 19: 目標搜尋對話方塊 執行的結果為銷售 2000 個才會有 $6000 的盈利 再舉個較複雜的例子 : 若要計算年收益 (I), 建立一個包含資本值 (C) 年數 (n) 以及利率 (i) 的表格 公式為 : I = C * n* i 圖 20: 搜尋目標結果 假定利率 i 為 7.5%, 而年數 n (1) 將保持不變 然而, 您想知道要如何變更投資資本 C 方可獲得特定的收益 I 就本例來說, 計算如果您想要年收益為 $15,000 需要多少資本 C 在儲存格 B2 輸入資本 C ( 任意值, 如 $100,000) 儲存格 B3 輸入年數 n (1), 以及儲存格 B4 輸入利率 i (7.5%), 以公式 I = C * n* i, 在儲存格 B5 算出年數益 13 目標搜尋

圖 21: 範例解釋 1) 將游標放在包含年收益 I 的儲存格 (B5) 中, 並選擇 工具 目標搜尋 螢幕上會顯示目標搜尋對話方塊 圖 22: 目標搜尋對話方塊 2) 在公式儲存格欄位中輸入正確的儲存格 (B5) 3) 將游標放在可變的儲存格欄位中, 在工作表中, 按一下包含要變更值的儲存格, 在本範例中是包含資本值 C 的儲存格 (B2), 我們要搜尋的目標就是資本值 4) 在目標值欄位中輸入公式的預期結果, 在本例中, 該值是 15,000, 按一下確定鈕 5) 螢幕上會顯示一個對話方塊通知您目標搜尋功, 按一下是鈕在儲存格中輸入帶變數值的結果 圖 23: 搜尋目標結果 6) 資本額由 100000 自動更改為 200000; 年收益更改為 15000 想要達成年收益為 15000 的目標, 資料額必需增加為 200000 目標搜尋 14

使用解決程式 圖 24: 代入目標搜尋結果 工具 解決程式 相當於目標搜尋更詳細的形式, 不同之處在於解決程式可以處多個不可知的變數, 依據您的定義計算最大值或最小值的結果 每一個設定的規則不管在公式中的參數是大於 小於或是等於您輸入的值, 如果你希望參數保持不變, 您輸入的規則, 即儲存格應等於其目前的輸入 對於想要更改參數, 您需要加入兩個規則來定義一個範圍可能的數值 : 限制條件 您可以定義一連串的限制條件為某些儲存格設限 例如, 您可以設定限制, 使其中一個變數或儲存格不得大於其他變數, 或不得大於指定的數值 您也可以定義限制, 使一或多個變數必須為整數 ( 沒有小數的數值 ) 或二進位值 ( 僅允許 0 與 1) 一旦您完成了規則的設定, 就可以調整參數和點擊解決鈕得到結果 使用解決程式的目標在於尋找能最佳化目標儲存格中數值之方程式變數值, 又稱為 目標 您可以選擇目標儲存格中的數值應為最大值 最小值或接近指定的值 您在變更儲存格方塊中輸入的矩形儲存格範圍內會插入初始變數值 圖 25: 解決程式對話方塊 15 使用解決程式