電腦網路與作業系統 電腦網路與作業系統

電腦網路與作業系統上課教材

電腦網路與作業系統 > 課程內容 > 第 03 章 - 應用生產力軟體 - 電子試算表與 spotfire

第 03 章 - 應用生產力軟體 - 電子試算表與 spotfire

上次更新日期 2020/02/04

在大家還沒有接觸資料庫系統,以及透過網頁程式與來進行資料的解析前,或者是在僅有單機非網路的情境下,想要處理一堆數據, 最簡單的方法,大概就是使用電子試算表的功能。電子試算表透過將資料一格一格的紀錄,然後透過列或行的方式,加上內建函數, 就可以輕鬆愉快的幫大家計算、統計、處理一堆數值變成有意義的圖、表資料。底下我們來玩一玩資傳系常見的 Excel 2016 電子試算表吧! 基本上,只要是試算表,界面與設計概念都差不多啦!

學習目標

  1. 認識電子試算表的界面、工作表、欄位的設計方式
  2. 學習如何使用 excel 內建函數
  3. 使用圖示:如直方圖、XY散佈圖等基礎圖示
  4. 使用巨集的錄製與重製
  5. 電子試算表加上密碼

3.1: 認識 Excel 電子試算表界面

不論是公務還是私人的收支計算資料等,大部分的朋友都會接觸到需要進行計算與統計的數據資料,無論這數據資料多大, 總是需要有軟體來進行計算、平均、加總、製圖等等的功能,這些時刻都有可能會用到所謂的電子試算表!當然需要有資料儲存與保留的功能! 應該不會單純使用小算盤的吧!呵呵~

  • Excel 界面介紹

在公務與企業單位常常會接觸到數值統計方面的資料,這些資料很可能需要進行格式化輸出或者是進行部份的圖表製作。 在校園單位,最常見的當然就是老師們用來統計學生成績的資料了,這種情況下,電子試算表就是一個很重要的工具。 以底下的成績為例,我們可以用來計算學生的學期成績!

請下載上述的檔案後,直接點擊兩下該檔案,或者直接使用 excel 打開該檔案之後,一開始的界面是有點像這樣:

Excel 界面說明

事實上,整體的界面跟前一章的 word 也沒有差很多,就是工具列在正上方,然後每個工具列有許多的子項目。至於底下的資料欄位中, 橫列的數值以英文 A, B, C 作為編號,直行的以數字 1, 2, 3 作為編號,因此如果是座標在 (3, 3) 的位置上, Excel 就視為 ( C3 ) 的位置囉! 在該位置裡面就能夠填寫文字或數字,欄位會自動判斷內容的。

其實 MS Office 的界面,都不會差太多!包括稍後會介紹的 power pint 也是同樣差不多的版面配置。
  • 工作表的應用

另外,在左下方的頁面標籤指的是每個『工作表!』一個 Excel 檔案可以擁有多個工作表,每個工作表都可以是獨立的,也能夠進行資料交互運算。 在本章的範例中,我們大概都是使用獨立的工作表來處理而已。並且盡量不要更動到原始的資料為宜喔!

現在請將滑鼠移動到該檔案左下方的『成績-原始資料』上面按下右鍵,選擇『移動或複製』,在出現的視窗中,需要點選『建立副本』,整個流程如下所示:

工作表的複製與更名

最後副本就出現了!如下圖所示。請自行更改你喜歡的名稱~不要用原本的名稱囉!

工作表的複製與更名
鳥哥個人的習慣,原始資料會保留,方便未來的參考!所以,工作表的應用就很重要了!
  • 統計函數 average 的用法

現在來觀察一下該表格,我們可以發現作業有 9 次成績,9 次作業的平均值,就是同學們的平時成績,因此,我們需要對這幾次成績進行平均值計算才可以。 然後又觀察一下目前的配置,目前的 L, M, N 欄位預計作為期中、期末、學期成績~而假設老師一開始就宣佈平時、期中、期末成績各佔 30, 30, 40 百分比, 那麼我們就可以在『期中』的左手邊新增一行,加入 9 次作業的平均即可!整體流程大致是這樣的:

  1. 先讓滑鼠移動到 L 上面,點一下左鍵勾選整個 L 直行,之後按下右鍵,選擇『插入』就能夠新增一個直行
  2. 再到 L2 去新增文字為『作業平均』
  3. 到 L3 去使用『 =average(C3:K9) 』其中當你寫入小括號之後,就可以用滑鼠去挑選你要進行平均的欄位了!
  4. 最後按下 [Enter] 之後,系統就能進行平均計算!
  5. 回到 L3 上面,看欄位的右下角會有個 + 字號,按滑鼠左鍵兩下,就能夠複製到全部的同學的作業平均了!
  6. 最後點選工作列『常用』有個 .00 -> .0 的小圖示,點選它,就能夠讓小數點位數縮小!請選擇 1 位數即可!

那就先來增加一個直行吧,先來到 L 這個欄位處理一下:

平均值的函數

再來處理 Excel 的函數設計,記得要輸入等號 (=) 之後才可以輸入函數,否則 average 就只會是個文字功能而已。 現在請 (1)在 L2 輸入『作業平均』後,然後 (2)在 L3 輸入『 =average( 』這個函數的功能!記得要勾選需要的欄位啊!

平均值的函數

如上所示將函數輸入,而且勾選了所需要平均的範圍之後,再輸入右側的小括號,就可以按下 enter 來處理好函數功能。 然後,因為平均值不會剛剛好是整數,但老師們登記成績,很可能被要求只能有整數,所以,此時請將游標點擊在 L3 的儲存格上面, 然後依據底下的方式,點選小數點位數降低的功能,就可以取得整數 (四者五入) 的功能了!

平均值的函數

如上所示,格式處理完畢後,你可以看到儲存格右下角有個加號 (+),你可以雙擊滑鼠左鍵,Excel 會根據左側的相鄰儲存格的長度, 自動的幫你計算後續的儲存格內容,很快就可以處理好所有同學的作業平均了!如下所示:

平均值的函數
上面的實做流程要注意,因為大部分的 Excel 運算流程都與上述的程序差不多!大概就是運算的內容不同而已! 所以,上面的流程真的要自己做過才好喔!此外,函數運算非常的多!你可以點選畫面中的 [fx] 那個小框框,就可以看到很多的可用函數了!
  • 固定欄位的計算功能 ==> [F4] 按鈕的應用

雖然老師們預計使用 30%, 30%, 40% 的平時、期中、期末成績總和,不過,總有特殊的情況需要處理!舉例來說, 或許會有科目以作業及期末為主,因此可能是 40%, 20%, 40% 這樣的分配。為了方便老師們觀察分配的情況,所以,我們會在平時、期中、期末的欄位上方, 加上各別的百分比例,讓未來進行成績的查詢時,可以很快的知道各項成績所佔的比重。

  1. 先輸入所佔的比例,使用小數點來處理即可,如下所示:
    平均值的函數
  2. 在 O3 按下等號 (=) 之後才可以進行計算式的填寫!依序填入正確的儲存格!通常這個動作可以使用滑鼠去點擊儲存格, 以方便計算功能的執行,比較不容易因為手動輸入儲存格欄位的填寫錯誤而出問題!
    平均值的函數
  3. 依照之前的方法,處理小數點到變成整數,同時點擊儲存格右下角的 + 號,自動讓底下的資料自己計算。結果如下: 平均值的函數

要命了!怎麼結果會是錯誤的?很簡單,這是預設的計算功能,欄位的處理,都是使用相對位置,而不是固定欄位的絕對位置所致。 因為我們的運算比例位置是固定的 (就是所謂的絕對位置),所以在計算式的填寫時,就得要加上特殊符號才行!重新回到 O3 的儲存格欄位上面, 點選上方的儲存格內容位置,在 L1, M1, N1 的字元上面,按下 [F4] 多次,看看會有什麼情況發生?最終保持有 $ 符號的樣式來處理!如下所示:

平均值的函數

再次使用相同功能來進行複製的行為,很快就可以得到全部同學的學期成績了!

為什麼我們要將各種成績的比例放置到特別的位置上呢?這是因為計算上面的方便所致。 舉例來說,未來你要調整學期成績的分配比例,只要在 L1, M1, N1 這三個儲存格調整,就可以很快的幫你重新計算好全部的學期成績! 快樂的不得了!

3.2: 使用 Excel 函數功能

要玩弄 excel,最重要的部份大概就兩個,一個是函數的使用,一個則是圖示的處理。上一個小節我們我談到 average 這個簡易的平均函數, 那有沒有其他的函數可供處理?底下來測試測試。

  • 使用邏輯函數 if 的功能

前一小節我們談到了 =average 的平均功能,那是個超級簡單的函數。有沒有什麼特別的函數可以介紹使用的呢? 有個很有趣的函數,就是『如果, if 』這個函數!基本功能是這樣:

  • if(判斷式, 成功的顯示, 錯誤的顯示)

現在,假設我們要找出不及格的同學,不及格是指 60 分啊~不過,碩士班的不及格則是 70 分...不太一樣耶!沒關係, 現在,讓我們在 P 這個直行上面增加一個及格與否的判斷式,看看能不能有個好的處理方式!

  1. 先在 P2 上面輸入『及格否』的字樣,並在 P1 上面輸入 60 這個數值,如下所示:
    平均值的函數
  2. 接下來開始處理邏輯判斷函數資料,(1)先將滑鼠移動到 P3 儲存格上面; (2)點選 fx 小按鈕: (3)點選『邏輯』方面的函數; (4)點選 if 項目,然後就可以按下確定了!如下所示:
    平均值的函數
  3. 在彈出式視窗裡面,勾選判斷式,然後填上需要處理的資料,如下所示:
    平均值的函數
  4. 再以大量複製的方法重複複製,就可以得到如下的結果,可以測試出是否及格的樣式囉!
    平均值的函數

很快的,就可以抓出不及格的同學了!

資傳系大一的基礎程式設計是有其必要存在的!最簡單的選擇 (if) 功能,在比較重要的資料處理軟體當中, 都可以看得到的!
  • 使用統計函數 countif 的功能

我們已經知道誰不及格了,但是,到底有幾個不及格?是否需要手動一個一個計算呢?其實也不需要, excel 有提供『計數』的函數功能。 基本上有底下這幾個簡易的計數函數:

  • count():計算範圍內的資料是數值格式的儲存格數量
  • counta():計算範圍內資料不是空白 (包含文字) 的儲存格數量
  • countif():計算範圍內符合某種條件的儲存格數量

因為是否及格與分數有關,所以我們需要的當然就是第三種 countif 這個函數了!讓我們來玩玩看囉:

  1. 先到 Q2, Q3, Q4 這三個儲存格,分別填入『總人數』、『不及格人數』與『及格人數』的文字,然後在 R2 的儲存格上面, 按下 fx 小按鈕,選擇『統計』裡面的『 COUNT 』函數,如下所示:
    平均值的函數
  2. 在出現的視窗中,選擇好你要計算個數的範圍,如下所示,最終就可以得到總人數囉!
    平均值的函數
  3. 來到 R3 的儲存格,同樣按下 fx 的項目,同樣選擇統計,不過這次挑選 countif 的項目,範圍依舊選擇 O3 到 O52 之間, 要處理的效果則是小於 P1 的結果,示意如下:
    平均值的函數
  4. 最後,將兩者相減,就是及格的人數啊:
    平均值的函數

輕輕鬆鬆的完成許多統計的資料!還有許多的函數可以使用~就看大家的使用情況囉!另外,三種 count 的計數方式都有使用的時機! 有的時候需要計算『出現文字的儲存格個數』時,就得要使用 counta 這個函數!

Excel 的函數功能非常多,鳥哥曾經用來作為許多的統計計算之用,包括計算相關係數 R 值、標準差、大於某個數值才進行計算等, 相當有用!還不太需要寫程式~就看你未來如何學習囉!
  • 分割視窗與凍結視窗方便查詢

你可能會發現到,學生的學號是在 B 欄位,總成績是在 O 與 P 欄位,這兩者之間也差太遠了!有沒有辦法讓我們的資料可以方便觀察? 可以的,使用『分割視窗』即可!現在,請 (1)點選『檢視』頁面; (2)點選『視窗』項目內的『分割』,你就會看到如下的畫面了:

分割與凍結視窗

多出來的線段上面,你可以將滑鼠移動到該線段,然後就可以開始挪動該線段了。如果不想要某個分割線, 那麼可以將該線段拉到最角落,該線段就會自然消失。一般來說,我們會將上面標題與左側標題的部份,只保留標題而已, 大部分的資料內容都在右下角的視窗上,有點像底下的模樣。然後,如果你不想要不小心動到分割線, 那麼可以將分割線凍結~有點像底下的模樣,點選『凍結視窗』,如下所示:

分割與凍結視窗

最後的凍結視窗結果像底下這樣,是挺有趣的功能!也很方便我們對照著資料,而不用移來移去的去看標題列囉!

分割與凍結視窗
  • 排序功能

如果你想要知道學生們的成績排序,該如何是好?可以使用 Excel 內建的排序功能即可!現在,另外開一個新的工作表, 工作表的名稱為『成績排序』,然後將兩個主要欄位:學生名字與學生成績複製,複製完畢後,請使用純文字的方式貼上。 流程有點像這樣:

  1. 先在新的工作表上面,將工作表名稱輸入為『成績排序』,之後,到『成績-統計』的工作表上面,先點一下 B 欄位, 之後按著 [ctrl] 不放,再點一下 O 欄位,就可以同時點選兩直行,有點像底下這樣:
    成績排序功能
  2. 貼上的時候,如果使用預設貼上,那麼貼上的資料會有函式的計算式在裡面。問題是,我們只需要數值以及整數格式, 所以,反覆貼上兩次,一次貼上純文字,一次貼上格式,就可以取得僅需要的資料了!如下所示:
    成績排序功能
  3. 之後請選擇所有的資料,第一橫列不要選 (因為空白之故),之後按下『資料』內的『排序』,並且選擇成績的『學期』直行, 成績從大到小排列,就可以順利取得成績從高到低的結果了!過程如下:
    成績排序功能

這樣排序就完成了!如果有需要用到雙重排序時,你可以在上面出現排序的圖示內,按下『新增層級』的按鈕,就可以有第二個排列的數值或文字資料, 對於大量資料的排序,也是很有幫助的喔!

3.3: 使用 Excel 圖表功能

前面說過,除了函數之外,Excel 的圖表製作也是很重要的。我們依舊拿上面的 Excel 檔案為例,如果老師想要知道每 10 分的間距裡面, 每個間距的學生數量時,難道需要一個一個計算嘛?好像是不需要的。那怎辦?沒關係,可以使用一個名為直方圖方式來處理即可。 不過,在開始之前,得要先增加分析工具箱才可以!

  • 增加增益集工具箱

底下要介紹的資料分析工具,需要你額外載入分析工具箱的項目才可以處理!否則 excel 預設不存在這個資料。處理的方法如下:

  1. 從 excel 的『檔案』,按下左側最下方的『選項』按鈕,就會出現一個彈出式視窗
  2. 選擇左側的『增益集』按鈕,就會出現增益集管理項目,畫面中你會發現到作用中的增益集並不包含分析工具箱,所以,請點選『執行』,如下所示:
    成績排序功能
  3. 在出現的視窗中,勾選『分析工具箱』,並按下確定即可。如下所示:
    成績排序功能

這樣你就擁有分析工具箱的功能,你可以在『資料』頁面下的『分析』分項當中找到正確的『資料分析』按鈕喔!

  • 成績統計直方圖

大家偶而會看到所謂的『直方圖』,那是透過數值分段『計數』的圖示,以了解每個區間的『個數』資料。 舉例來說,我們需要了解 0~10, 11~20, 21~30... 共 10 個區間的成績中,每個區間的學生人數有多少? 這時就得要使用所謂的直方圖了。不過,首先,我們需要告訴 Excel 我們所需要的區間有多少,所以,請在 E2 的欄位輸入『組距』文字, 然後在 E3 之後分別輸入 0, 10 到 100 的數字,如下所示:

直方圖的處置

之後按下『資料分析』,就會出現如下的圖示:

直方圖的處置

請選擇直方圖,並且按下確定,就可以開始處理直方圖的資料。要注意的是,我們需要的有兩個東西, 一個是組距,一個是成績範圍,兩者圈選完畢後,直方圖我們需要在本地工作表處理,所以,請依據底下的圖示依序點選所有需要的資料:

直方圖的處置

最後按下確定之後,就可以得到如下的直方圖,我們可以透過圖示,很清楚的知道了成績的顯示結果!

直方圖的處置
直方圖真的很常用在某個組距的分析裡面,例如我們的學生成績的範例!如果人數太多,使用這個方法,確實可以很有效的分析出成績的分佈! 是個不可多得的好方法啊!
  • 資料剖析的方法

某些時刻,我們從網路上面下載,或者是從某些資料庫內容下載的純文字資料,想要轉成 Excel 儲存格的內容時,部份資料會出現問題! 這是因為預設的情況下, Excel 只會分析出使用 [tab] 按鈕分隔的資料而已。舉例來說,例如 2020 年的全國政黨得票數,如下所示:

號次|政黨|得票數
1|合一行動聯盟|17515
2|中華統一促進黨|32966
3|親民黨|518921
4|安定力量|94563
5|台灣基進|447286
6|時代力量|1098100
7|新黨|147373
8|喜樂島聯盟|29324
9|中國國民黨|4723504
10|一邊一國行動黨|143617
11|勞動黨|19941
12|綠黨|341465
13|宗教聯盟|31117
14|民主進步黨|4811241
15|台灣民眾黨|1588806
16|台灣維新|11952
17|台澎黨|11681
18|國會政黨聯盟|40331
19|台灣團結聯盟|50435

每個分隔都是『 | 』這個符號耶~你可以將上述方塊內的資料放置到正確的儲存格嗎?首先,將剛剛的檔案儲存,然後請點選 Excel 的『檔案』, 按下『新增』之後點選『空白活頁簿』,就可以打開一個新的 Excel 檔案了。之後將上面方塊的內容複製,貼到新檔案內的 B2 位置上, 就會得到如下的圖示:

資料剖析的方法

你會發現到貼進來的資料並不是我們預想的模樣!此時,請勾選如下的『資料』、『資料剖析』的小按鈕,會出現如下的圖示喔:

資料剖析的方法

請先保留『分隔符號』的分隔功能,然後按下『下一步』進入選擇分隔符號的頁面中:

資料剖析的方法

如上所示,因為我們的分隔符號使用的是 | 這個字元,所以請在上面的『其他』位置上,輸入這個字元,然後看一下預設的分隔情況,看看是否正確? 如果正確無誤,就按下『下一步』,開始來處理資料格式的內容:

資料剖析的方法

如上所示,基本上, Excel 會主動的幫你找一下該資料的格式。不過,有時候 Excel 可能會誤判,所以,你可以看一下資料格式正確與否。 若沒有問題,再按下『完成』即可。最終的結果如下圖所示,就會是每個政黨的得票率了!這個資料剖析很好用! 尤其是你撰寫程式碼輸出的資訊,可以透過這個方法,將他轉成儲存格的內容喔!

資料剖析的方法

最後,請調整一下儲存格的數值格式: (1)先將得票數的數值內容全部圈選起來; (2)滑鼠移動到上方後,按下右鍵,選擇儲存格格式; (3)選擇『數值』,然後小數位數填寫為 0,並且勾選『使用千分位符號』,就可以確定了。如下所示:

資料剖析的方法
因為鳥哥經常使用一些數值程式在處理數據,因此經常會有純文字的數值資料呈現。 為了與政府部門或企業部門的夥伴們處理數據的內容介紹,就得要使用這樣的方法,將他轉成電子試算表的內容才行啊!
  • 簡易的直條圖或圓餅圖或折線圖應用

首先,將政黨得票率計算一下,這包括

  1. 在 D22 處,增加一個總和『 =sum() 』的函數
  2. 在 E2 增加『得票率』的項目
  3. 在 E3 開始輸入『 =D3/$D$22 』的計算式,最後使用下拉式複製功能,將底下的政黨全部計算起來
  4. 最後在『常用』的頁面上,選擇『%』的顯示結果,並將小數點設定為 2 位

結果如下所示:

圓餅圖的設計

現在,請將『政黨』與『得票率』的欄位圈選起來,然後到『插入』的頁面,選擇『建議圖表』的項目,就會出現許多的圖示~ 請依據你的需求來點選需要的圖示!如下圖所示:

圓餅圖的設計

最終呈現的結果會有點像這樣:

圓餅圖的設計

不過,偺們台灣的政黨具有不分區的門檻為 5% 的結果,所以,讓我們將上述的工作表再次進行一些處理:

  1. 在『工作表1』上面按下滑鼠右鍵,選擇『移動或複製』,勾選『建立副本』之後,按下『確定』;
  2. 在『工作表1 (2)』內容,將該內容的圖示刪除
  3. 將 B2 ~ E21 的內容圈選,並且排序,使用的排序為得票數,從大到小

上述的動作結束後,會得到如下的圖示喔:

圓餅圖的設計

之後請繼續如下的動作:

  1. 在 F2 的儲存格加上『第二階段得票率』
  2. 在 F8 的儲存格上,使用『 =sum(D3:D6) 』的方式加總
  3. 在 F3 的儲存格上,使用『 =D3/$F$8 』的方式計算得票率
  4. 將 F3 到 F6 使用百分比的方式,增加到小數點下第二位的處理

最終的結果有點像底下這樣:

圓餅圖的設計

現在,請圈選政黨到時代力量的區間,以及第二階段得票率的區間,然後按下『插入』選擇圓餅圖,如下所示:

圓餅圖的設計

此時就可以得到相關圓餅圖的資訊了!之後挑選樣式,選擇你想要的樣式,就有一張漂漂亮亮的圓餅圖出現囉!如下所示:

圓餅圖的設計

最後,這張圖表就可以放置到你的簡報內容,或者是網頁上,作為一個重要的圖表參考依據囉!

  • XY 散佈圖

上面的圖表內容,大部分的 X 軸與 Y 軸並沒有絕對的相關性,大家要看的只是在 Y 軸上面的數值而已。 如果 X 與 Y 是有相關性的呢?舉例來說,鳥哥有進行空氣品質數值模擬,模擬的結果必須要和實際環保署的觀測值進行比對, 如果比對的結果合乎規範,這樣才能算是合理的模擬結果,這個模式才能夠進一步進行其他研究。下表是某一次模擬的結果數據:

上述的 Excel 檔案內共有兩個工作表,一個是數值模式的模擬結果,一個則是環保署的實測值。那如何確認模擬的結果是可信的呢? 這當然得要一項一項工作進行分析。不過,我們簡化一下分析的流程,先確認一下我們台南地區的模擬結果是否正確就好! 下載上述的檔案後,進行如下的圖表來測試看看即可:

  1. 打開上述檔案後,先新增一個工作表,取名為『台南測站』的工作表名稱
  2. 新增兩個直行,左邊的直行主要為 X 軸,基本上就是基礎值,因此,使用『EPA實測PM2.5』工作表內的台南測站數據, 右邊的直行主要為 Y 軸,取用『模擬PM2.5』工作表內的台南測站數值,做好之後大概像這樣:
    XY 散佈圖的設計
  3. 請將上述兩直行圈選起來,然後在『插入』的頁面上,選擇『XY 散佈圖』的圖示來處理,如下所示:
    XY 散佈圖的設計
  4. 移動到『+』號的地方,按下『座標軸標題』的部份,然後分別填寫『EPA觀測值』與『M3模擬值』兩個參數, 然後將上方的標題改為『PM2.5 濃度相關性』,有點像這樣:
    XY 散佈圖的設計
  5. 因為得要了解到兩者的關係,所以這時請先 (1)點一下圖中的資料點 (2)在資料點上面按下滑鼠右鍵 (3)選擇『加上趨勢線』, 之後在出現的視窗上,選擇『線性』,並勾選『圖表上顯示公式』與『圖表上顯示R平方值』,有點像底下這樣:
    XY 散佈圖的設計 XY 散佈圖的設計

最後,再稍微調整一下格式,就可以得到不錯的圖示結果了!將圖示複製,貼到你的簡報上,就能夠提供一份還 OK 的報告圖表了喔!

XY 散佈圖的設計
還有很多圖示與可以調整的項目,就讓讀著們自己慢慢測試自己慢慢玩囉!

3.4: 使用 Excel 的巨集錄製功能與保護功能

某些時刻,我們希望將『重複的動作複製起來,然後按下某個按鈕,它就可以完整的複製剛剛的動作』,如此一來,要反覆進行的工作, 就可以輕鬆愉快的處理完畢。舉例來說,我們需要知道每次作業的直方圖!如果每次都得要重複的處理直方圖動作,可能你會覺得很煩~ 那有沒有快速的方法可以處置?其實是有的,那就是執行巨集的錄製。

所謂的巨集錄製,基本的想法就是,Excel 會紀錄你在某個欄位進行某些動作,將這些動作記載下來,然後當你有需要的時候, 便重複幫你執行這些記錄下來的動作,如此而已。不過,由於這些動作基本上與你的『欄位』位置有關,所以, 你的欄位資料就不能隨便更動!多說無益,直接來進行一次整個作法,你就知道這個巨集的基本功能了。

  • 巨集的錄製

首先,讓我們先來處理一下前驅資料,再次打開 excel2016-1.xlsx 這個檔案,新增一個名為 hw01 的工作表,並將『成績-原始』裡面的第一個作業成績複製過來, 假設放置到 B1 開始的欄位好了,然後,在 C1 填寫組距範圍,之後將游標聚焦在 A1 欄位上,準備好的工作表有點像這樣:

巨集的錄製

準備完畢之後,按下『檢視』頁面裡面的『巨集』,裡面有個『錄製巨集』的項目,按下去,然後填寫我們巨集的名稱與相關的資訊, 大致上寫完就好,不用寫的密密麻麻~有點像底下這樣:

巨集的錄製 巨集的錄製

如上所示,最終按下『確定』之後,就可以開始錄製我們的巨集!請依據前面談到的『直方圖』的作法,一步一步建立起這次作業的直方圖資料, 從 (1)資料頁面 (2)資料分析 (3)直方圖 的步驟,一個一個完成即可!完成圖表之後,再按下『檢視』的『巨集』的『停止錄製』即可。 最後你就可以從『檢視』裡的『巨集』裡的『檢視巨集』得到類似底下的畫面喔!

巨集的錄製

這樣,你就有了一個制定好工作方式的巨集流程了!

  • 重複進行巨集的工作

現在,請製作一個名為 hw02 的工作表,並依據剛剛 hw01 的三個欄位,將成績放在 B1、組距放在 C1 而最終游標放在 A1 上面。 當你完成上述的動作之後,再由剛剛的圖示裡面那個『執行』按鈕,按下去,看看你會發現什麼?

巨集的錄製
因為我們有需要用到自動直方圖製作的功能,所以,在前面 3.3 要增加『增益集』的部份,一定要勾選『分析工具箱』與『分析工具箱 - VBA 』才行! 不然可能會報錯!

你會發現到,直方圖立刻依據你的需求完成了!這只是個很簡單的範本,未來你的工作如果太複雜,就可以將你的所有工作錄製下來, 然後重複執行,就可以簡化非常多的工作了!另外,如果需要將這個巨集儲存在檔案中,以作為未來的使用,那麼就得要更換你的儲存格式, 由 (1)檔案頁面 (2)另存新檔 (3)目前工作目錄 (4)下方的『存檔類型』請挑選『Excel 啟用巨集的活頁簿 (*.xlsm) 』,然後按下『儲存』, 你的巨集才可以被順利的紀錄下來喔!

如果要將巨集存入 excel 檔案中,需要重新另存新檔成為特殊格式 (.xlsm) 的檔名才行~否則該巨集會消失喔!這裡特別強調一下! 不然巨集錄製消失就可惜了!
  • 加上密碼保護

某些時刻,你的電子試算表檔案內容可能不想要讓人家隨便開啟~所以,想要自己增加一個簡易的密碼,讓開啟這個檔案時, 得要輸入密碼才能夠順利開啟,那可以透過 excel 的內建功能來達成。簡單的作法是: (1)檔案頁面 (2)選擇『資訊』 (3)選擇『保護活頁簿』 (4)選擇『以密碼加密』 此時畫面有點像這樣:

以密碼保護

然後出現如下的畫面,請繼續輸入你的密碼,要注意一下畫面中的資訊喔!它有提到,如果忘記密碼的話,這個檔案可能就無法開啟了! 另外,也要注意密碼的大小寫喔~大小寫是視為不同的字元喔!重複輸入兩次 (避免用戶輸入到錯誤的字元),然後就可以存檔離開囉!

以密碼保護

等到下次你要重新開啟這個檔案時,就會出現如下的畫面,畫面中一定要輸入正確的密碼,否則該檔案就無法開啟囉!

以密碼保護

對於否些比較重要的檔案,透過密碼保護是有好處~只是,你自己得要記得相關的密碼,否則,未來密碼忘記,可就麻煩了。 另外,某些研討會或者是需要繳交資料的時候,你就不能設定密碼~設定了密碼,反而會讓主辦者或者是老師無法幫你評定成績, 那就慘了!

那如何取消密碼呢?你需要先用舊的密碼開啟該檔案,然後同樣的,透過『檔案』頁面,在『資訊』欄位,選擇『保護活頁簿』裡面的『以密碼加密』, 之後的視窗就會出現你之前輸入密碼的欄位,請將該欄位內的密碼通通取消,然後再按下確定,就取消密碼囉!就這麼簡單!

3.5: 進階用戶 - TIBCO Spotfire 的使用

如果你想要有更進階的圖示製作與使用,可以嘗試透過 TIBCO 公司推出的 Spotfire 軟體來處理看看! 這個軟體有線上版本,也有桌機版本,只是需要付費而已。測試時,可以申請一個測試用帳號來處置,測試帳號需要有 email 才行! 每個 email 可以有 30 天的測試時間,如果確定這個軟體值得使用,你就可以付費開始應用囉!

  • 線上註冊

如上所示,你可以透過底下的連結來進行註冊,如果已經註冊過,就可以直接登入。基本上,註冊與 email 帳號有關, 你可以使用你的慣用 email 帳號來註冊即可。要注意的是,註冊的時候,輸入 email 之後,TIBCO 會寄一封啟動信到你的信箱, 請前往你的 email 去填寫正確的密碼後,才能夠登入喔!

進入上述網址,然後填寫好你的 email 與相關資料,如下兩張圖示所示,處理處理:

TIBCO spotfire TIBCO spotfire

按下上圖的『Get my Spotfire trial』按鈕之後,官網就會寄出一封信件到你的信箱中,所以,此時請登入你的信箱, 你應該會看到 TIBCO 寄給你的信件,內容有點像這樣:

TIBCO spotfire

點選上圖中的『Activate your account』按鈕,就會出現讓你填寫密碼的表格,有點像底下這樣:

TIBCO spotfire

在上表當中填寫兩次你的密碼,並且勾選確認,就可以按下『ACTIVATE』的按鈕,就能夠準備開始進行 spotfire 的線上資料分析工具了!

  • 線上直接進行資料分析

完成註冊之後,再來則是登入 TIBCO 官網直接線上處理資料看看~你可以由底下的連結來登入 spotfire 的線上版本:

上面的網站中,先輸入你剛剛註冊的 email 之後,按下確定,如果該 email 是正確的,就會開始進入密碼的填寫。 密碼填寫也正確之後,就可以登入系統了!登入系統之後,你會看到如下的畫面,在該畫面的右上方,點選方塊, 然後點選『Spotfire Analytics』就可以進入 spotfire 軟體的功能!

TIBCO spotfire

如下所示,就是 spotfire 的預設畫面,預設什麼都沒有,你可以點選加號 (+) 來取得額外的功能,包括加入檔案來分析。

TIBCO spotfire

如下面的圖示中,按下『Browse local file..』的按鈕,就可以選擇檔案~請將剛剛上面提供的測試範本, sportfire-1.xlsx 檔案匯入!

TIBCO spotfire

匯入之後就會有點像底下的模樣,很快速的確認匯入,按下 OK 即可!

TIBCO spotfire
  • 強大的 AI 繪圖選擇

事實上, spotfire 會嘗試先幫你處理可能的資料分析圖表,雖然不見得是你需要的圖示,但至少也是一個可以參考的依據喔! 現在,請點選 M3PM2.5 的這個 PM2.5 濃度值,看看 spotfire 建議什麼圖表吧!先如下圖所示,挑選左側的表格圖示,然後選擇 M3PM2.5 的資料欄位, 你就會發現右手邊出現額外的可挑選圖示!你可以嘗試看看最右側的圖示中,隨便點選某個圖示,該圖示就會顯示到主畫面中。如果不喜歡,再將它 X 即可。

TIBCO spotfire

我們目標很明確,就是要建立 XY 散佈圖,這種圖示在 spotfire 當中,被稱為 scatter plot 喔!所以,如下圖所示, 按下圖按鈕,選擇 Scatter plot 按鈕,如下所示:

TIBCO spotfire

不過, spotfire 也可能會挑選到錯誤的項目,例如下圖所示,是預設的 XY 散佈圖狀態!結果顯示的竟然是經緯度!這不對啊!那怎辦?

TIBCO spotfire

沒關係,如上圖所示,你滑鼠移進圖示中,左側的『緯度』就會出現類似按鈕的符號,給他點下去,就可以選擇到正確的『M3PM2.5』的欄位, 如下圖所示,同理,也請將『經度』改成『EPAPM2.5』的欄位。

TIBCO spotfire

最終就可以看到我們的 XY 散佈圖示囉!如下圖所示。不過比較可惜的是,網頁版並沒有提供趨勢線,如果你需要使用到趨勢線, 就得要下載桌面版本,我們這邊暫時不介紹這個功能~有興趣的,自己前往 google 查詢一下囉!

TIBCO spotfire
  • 特殊功能:地圖顯示

你可能會覺得好像也沒什麼了不起~不過,有個了不起的項目來了!那就是地圖功能!過去我們要用地圖顯示某些數值,總是非常非常麻煩~ 這個 spotfire 則可以快速的將地圖顯示到我們的畫面中!相當好用!現在,請依據底下的圖示,挑選『Map chart』的圖表製作方式:

TIBCO spotfire

點選上圖的資料之後,竟然出現如下的世界地圖~這是因為我們的活頁簿資料裡面填寫了中文的經緯度,而不是英文的經緯度提示, 因此 spotfire 誤判了!那怎辦?沒關係,如下所示,在圖示中按下滑鼠右鍵,選擇如下的屬性『Properties』項目:

TIBCO spotfire

在出現的畫面中,先點我們的資料『spotfire-1』,右邊會出現更多的視窗,請找到『Geographic location』項目,點擊它, 然後按下『Coordinate-based』這個經緯度座標基準值,之後分別點選 X 與 Y 軸的項目,並點選正確的經度與緯度項目, 一邊點擊,正中央圖示就會依據你的選擇正確的顯示出台灣地圖喔!如下圖所示:

TIBCO spotfire

預設的資料中,圓形大小與數值對應不太正確,如下圖所示,請點選『 Size by 』這個項目,然後點選『M3PM2.5』或者是其他項目, 你就可以發現到圓形大小的差異了!我們就可以看到,南部的圈圈比較大,這是因為該地的污染濃度比較高的原因喔!

TIBCO spotfire

如果覺得圓形大小不容易判斷,那麼使用顏色也是不錯的思考方向,如下圖所示,測試一下你的圖示,就可以發現到可以挑選不同顏色來顯示污染的強度!

TIBCO spotfire

如上圖所示,我們可以發現到南部的許多圓圈都重疊了,如果想要知道每個測站的資料,就可以使用畫面右上方的放大功能, 移動功能,來進行圖示的放大,如下圖所示,可以知道台南、高雄、屏東地區的各測站資料喔!

TIBCO spotfire

更多的功能就請自己發揮創意處理囉!

3.6: 課後練習

作業上傳時的注意事項:

  1. 伺服器的詳細資料,以及相關傳輸軟體設定,請參考第一章的 1.5 節的內容。
  2. 可以使用 MS office 的 word 或者是 libreoffice 的 writer 等軟體來撰寫你的作業,作業檔名可以使用 .doc, .docx 或者是 .odt 或者是 pdf 等格式,均可接受。
  3. 若作業中有圖檔,請將圖檔降低水平解析度到 800 像素以下再貼到文章中,檔案容量以不超過 2Mbytes 為限。
  4. 檔名請設定為: os_4XXXCYYY_unit03.doc (4XXXCYYY 是你的學號,請填正確,有分大小寫,請確認)
  5. 請上傳到你家目錄底下的 os 目錄中 (若不存在,請自行建立該目錄)

開始本章節題目:

  1. 從 excel2016-3.xlsx 檔案中,挑選台南市的四個測站 (新營、善化、安南、台南),將這四個測站的環保署監測值與模擬值的 PM2.5 進行:
    1. 在一個名為『台南市測站』的工作表當中,先將這四個測站的資料彙整一下 (注意,可能需要整併成為 2 個直行, 這樣才能夠有 X 與 Y 軸);
    2. 在上述的工作表中,做成 XY 散佈圖,並且加上趨勢線與秀出 R 平方值;
  2. 從 excel2016-3.xlsx 檔案中,分別以台北市、新北市、高雄市這三個都會區,我需要計算出三都會區的 R 值 (相關係數), R 值得計算請參考 correl 這個函數,製作一個名為『計算 R』的工作表。至於各縣市空氣品質測站名稱,可以參考底下的網站來查詢:
  3. 請將 excel2016-3.xlsx 檔案上傳。