【工具分享】告別手動輸入！教師必備「簡明行事曆」自動產生器

我會簡化學校的行事曆，做一個自己需要的版本，除了傳給家長參考外，也放在 Line@ 裡隨時可以查詢。

但是做行事曆最討厭的就是第一欄：週次 & 日期的對應，一一手動輸入實在很煩。

這學期一直拖到昨天都還沒有把個人行事曆整理出來。

昨晚想：『呣，寫個程式來處理第一欄的問題吧！』所以就有了簡明行事曆產生器這個小程式。

教你一招：用 Excel 命名範圍，解決 Python in Excel 不能用變數的痛點

雖然 Excel 裡可以執行 Python 以取代 VBA，但使用起來還是滿不方便的。

首先，它的選取範圍不能直接用變數：

以下方式是正確的讀入資料方式：

all_data_df = xl("D1:KJ260", headers=False)

試圖利用變數讀入資料會失敗：

Data_Range = "D1:KJ260"
all_data_df = xl("Data_Range", headers=False)

所以每次資料變動，要改程式內容就很煩。

還有，它的報表繁中字體就只有三種，但只有 SimHei 字體能看。

不能用使用變數這個問題雖然讓人很困擾，但可以使用命名管理員定義一個新名稱，指定一個命名範圍，並將該命名範圍指向一個 index 函式來解決。

雖然定義名稱過程有點討厭，但至少不用一行一行去改 Python 內容，也還算可以接受。

matplotlib 畫圖中文字變方框怎麼辦？

在 Python 程式裡常會用 matplotlib 畫圖，但很困擾的是中文都會變成方框框。

圖、matplotlib 畫的圖中文字變方框框

這是因為 matplotlib 預設字體是 DejaVu Sans，所以無法顯示中文。既然是預設字體的問題，那我們就改一下字型。要改用字型顯示也不難，只要在畫圖指令前用 rcParams 指定中文字體即可。

import matplotlib.pyplot as plt
    
# 指定字型為 SimHei
plt.rcParams['font.family'] = ['SimHei']

# 這招在 google colab 行不通
# google colab 要用蔡炎龍老師提供的方法

指定中文字型後，matplotlib 就可以正確的顯示中文了。

圖、指定字型後 matplotlib 中文顯示正常

不過我們不滿足，希望除了 SimHei 字型外，還能使用更多自己喜歡的字體，要怎麼辦？

嗯，這就要看你想在哪個平台的 matplotlib 顯示中文了，方法都不太一樣。

• Excel
• Anaconda Jupyter Notebook
• Google Colab

如果評量總是強調平均分，你是否錯過了學生真正的努力軌跡？

期末考後，幾個老師聊到一位成績僅達及格邊緣的學生。有人說：「他的平均才 61，這樣也能算進步嗎？」

但如果我們拉出一張趨勢圖，會發現他期初成績是 38。短短三個月內，他整整提升了 23 分。而我們的評量系統——依舊只回報一個靜止的平均數字。

我們是不是過度信任平均值，以至於錯過了學生真正努力的軌跡？

一個「平均值」的幻覺

• 平均值會隱藏「變化趨勢」：
  兩位學生平均都是 70，但一位從 90 掉到 50，另一位從 50 漲到 90。你還覺得他們一樣嗎？
• 平均值不代表「典型」：
  在偏態分布的資料中，平均值往往並不接近大多數學生的真實表現。
• 平均值缺乏情境性：
  一個寒假輔導班讓學生進步 20 分，和另一位平穩維持 80 分，這兩者是否該用相同的標準評價？

轉向「成長曲線」思維

與其只看最終平均，不如也考慮「變化率」與「學習斜率」。你可以嘗試以下 Excel 技巧讓評量更有脈絡感：

在 Excel & Google Drive 中將日期轉為星期

很久以前我曾試著在 Excel 中將年月日三欄資料串成日期，但當時的方法祇能用在書面列印的資料，如果還要將日期用來計算，比方說算出當天是星期幾，那麼之前的方法就行不通了。所以後來我用 date & text 函數來轉換日期，轉換出來的結果還可以有不同的呈現方式，在實際應用上比較方便。

圖、各種不同的日期呈現方式

比方說這學期我跟學生玩一些遊戲，並把每日最高得分放在網頁讓學生隨時可以查看。網頁的最左邊 2/11 對應到星期一，2/12 對應到星期二，這難道是我自己慢慢輸入的嗎？當然不是啊，那會累死人，我是用公式自動轉換的。

我原本是把每日最高分記錄在 Excel，後來想要讓學生可以隨時查看，所以轉放到 Google Drive 裡。當我把 Excel 檔的公式貼到 Google Drive 去，哎，出錯了，日期 & 星期顯示出了問題。

看了說明書才發現原來 Excel 跟 Google Drive 在一些函數格式的細節是不同的，所以要同時在 Excel & Google Drive 上使用函數時要記得調整一下。我怕日後我要使用時又忘了這些細節差異，所以寫一篇文章記錄一下。 :)

Google Drive 的日期計算函數

因為 Google Drive 的公式有彩色，比較容易分辨參數內容，所以我先從 Google Drive 講起。同樣的，一開始都是使用三個欄位來記錄年月日。

圖、用三個欄位分別記錄年月日

計算分組競賽成績 -- Excel 樞紐分析表

為了提昇學生的學習，老師們有時候會將班上的學生做異質性分組，讓各組進行學習競賽。

異質性分組的目的是希望各組學習較佳的學生可以幫忙指導學習落後的同學，教師與學生的年紀不同、成長環境有異，老師解說了半天學生還不懂的部份，藉由同儕的說明可能反而容易瞭解。

另一方面，分組後讓各組進行學習競賽也可提供些許學習刺激，有些學生為了不輸給其他組別，學習時會較專心，對於這類不服輸的學生而言，分組學習可以讓他們得到較好的學習成效，所以讓學生分組學習是一個很常用的教學策略。

問題是，怎麼統計不同組別的學生成績並進行比較呢？

比方說我現在將班上的 35 位學生分成了 5 組進行學習競賽，這些學生的組別 & 各科成績均已輸入 Excel，其結果如下所示：

圖、學生分組競賽各科成績

上圖的成績登記表並不容易看出各組的學習成果，我們應該將同一組的成員排列在一起，算出這一組的平均；再把下一組的同學排列在一起，算出該組的平均。所有的組別個別處理完後，再來比較各組的成績優劣。

圖、將學生成績分組統計、跨組比較

『是啊，是啊！問題是怎麼做嘛！！』

嗯，要將學生成績分組統計的一個方法是，將學生的成績都輸入 Excel 後，手動選擇各組成員，將他們的成績手動加總後再算平均，最後再把各組的成績拿出來比較。

這麼一來我們可能就會把第一組的國文成績統計為 =Average(D2,D6,D11,D12,D34) ，總之就是要手動去尋找成員所在的位置，再把那一格的位置手動輸入計算。手動計算可以完成我們的工作，缺點是，祇要今天我們調整組別，比方說把一個學生從第一組調到第二組，公式就要再去修改，如果忘了修改得到的結果就是錯的。

偏偏啊，祇要有可能出錯的地方，以後就真的一定會出錯 (墨菲定律)，所以，把統計分組成績的工作交給電腦來做比較穩當，電腦出錯的機會比人出錯的機會小得多！:)

1. 選擇要產生樞紐分析表的範圍
2. 規畫樞紐分析表呈現的模樣
3. 將資料拉至合計欄
4. 改變組別的欄位設定至平均值
5. 調整欄位名稱

Excel 中有個樞紐分析表功能可以用來計算各個組別之間差異，利用樞紐分析表比較各組成績用不了 3 分鐘，你還沒點完一輪 =Average(D2,D6,D11,D12,D34) 公式，我已經用樞紐分析表算好了各組成績了，最重要的是，日後有學生被調整組別，它還是能正確計算，不勞我們費心。

使用 Excel 樞紐分析表 (Pivot Table)

要使用樞紐分析表，我們要從『資料 ==> 樞紐分析表及圖報表』叫出這個功能。

圖、從資料功能表叫出樞紐分析表

選擇樞紐分析表功能後，Excel 會問我們要分析哪裡的資料。因為成績都已經輸入 Excel 之中了，所以選擇分析 Excel 的資料就好。

圖、將目前 Excel 中的資料進行樞紐分析

然後選擇要分析 Excel 中的資料範圍，就把所有學生成績都選擇起來吧。

圖、選擇要分析的 Excel 資料範圍

然後把建立的樞紐分析表放在新工作表中，這樣比較不會與原本的成績檔案弄混。

圖、將產生的樞紐分析表放到新工作表

做好一般的設定後，我們就要告訴 Excel 要以哪些資料產生樞紐分析表，所以點選『版面配置』功能開始選擇資料。

圖、點選版面配置功能

然後就來規畫我們想要的表格吧！

我們目前的 Excel 檔案有座號、姓名、組別、國文第一次考試(國一)、國文第二次考試(國二) 等欄位，我們希望在表格的最左方依照組別列出學生姓名，而且在資料這一區呈現出該位學生國一、國二等考試成績，所以我們就試著把按鈕拉到適當的位置吧。

圖、將欄位按鈕拉至適當位置

依照先前的規畫，我把組別 & 姓名放在左邊列上，然後把各式學習測驗的成績放在資料部份，這樣就完成了。

圖、將欄位按鈕依規畫安置於適當地域

最後，按下完成，Excel 就會幫我們把分組的成績都統計完畢。

圖、按下完成開始統計分組成績

前一個步驟完成後，Excel 就幫我們把成績依照分組統計並列表。我們來看看……ㄟ，不對啊，這不是我要的結果。左邊的組別 & 姓名是對的，可是右邊的成績怎麼是排成直排呢？這樣子要觀看不太容易啊。

圖、初步完成的樞紐分析表，尚需要調整

左邊的分組成員已經正確了就好，右邊的資料呈現的方法不容易觀看沒關係，這祇要一個步驟就調整成功啦！

我們就來調整一下吧。

調整樞紐分析表的顯示方式

我們希望的是『資料』這一欄的資料應該是橫向展開的，把國一、國二、英一、英二等成績分欄呈現，這樣子才容易對照觀看。要把『資料』橫向展開，我們祇需用滑鼠將『資料』這一個按鈕拉到旁邊的『合計』就可以了。

圖、將『資料』按鈕拉至『合計』欄橫向展開資料

把『資料』拉至『合計』欄後，我們將資料橫向呈現了，這樣子可以容易的比較每個成員成績的不同。不過這樣子還是有問題，首先是欄位名稱太長，很多欄位都跑出畫面了，這樣子也是不易觀察，要縮短一點。

第二個問題是：應該呈現各組的平均值才對，以各組的總分來與其他組比較是沒有意義的，因為每一組的人數都不同，人多的那一組比較容易得高分，這是不公平的競賽。使用平均值來做比較就不會受到各組人數不均的影響，可以得到較為正確的結果，所以我們要將加總改為平均值。

圖、欄位名稱太長，並且應以平均值取代合計

好，我們來調整組別的平均值。

在組別這一欄的任一格按右鍵，就會跳出選單，選擇『欄位設定』功能就可以調整這一欄的內容了。

圖、從右鍵選單選擇『欄位設定』功能

在欄位設定中，我們可以看到組別這一欄原本是自動顯示加總的結果，點選一下『平均值』要求 Excel 以平均值顯示。

圖、將樞紐分析表的欄位設定顯示平均值

按下確定後，就會自動計算每個小組的成績平均值了。

圖、各小組的分數平均值由 Excel 自動計算

設定到這邊，如果不在意欄位名稱很長的話，其實就已經可以收工了，反正每一個科目、每一個小組的成績都能自動計算，再調整也祇是微調整體的美觀程度而已，不在意外觀的就這樣啦！

如果你希望顯示的漂亮一點，那我們就再繼續。

調整樞紐分析表的欄位名稱

要調整樞紐分析表中的欄位名稱有一點點小技巧。首先，你沒辦法直接在格子中修改欄位名稱，所以點選要修改的格子後，要將滑鼠移到 ABCD 上面的那一區修改欄位名稱。

再來，如果你直接將前面多出來的『加總 的』刪掉，Excel 會抗議說檔案中已經有另一個欄位叫做『國一』了，反正就是不給刪。所以我有個小小的變動方式，就是先用滑鼠把『加總 的』三個字標記起來，然後按一下空白鍵，把這三個字用一個空白替代，這樣就 OK 了。

圖、利用空白取代『加總 的』

將每一個欄位的名稱都修正後，我們可以在一個頁面之內看到所有科目的分組學習成果，除此之外我們也很容易就可以比較每個小組的學習平均值，樞紐分析表真是一個好用的功能！:)

圖、各小組的學習成果一目瞭然

樞紐分析表的重點整理

從剛剛到現在，我們好像做了許多事，要用樞紐分析表好像很難很難，但讓我們從頭再看一次，把重點整理一下，你會發現原來這麼簡單啊：

1. 選擇要產生樞紐分析表的範圍
2. 規畫樞紐分析表呈現的模樣
3. 將資料拉至合計欄
4. 改變組別的欄位設定至平均值
5. 調整欄位名稱

少少 5 個步驟就可以收工了。我做完這 5 個步驟你可能還在點第二組還是第三組的國一成績喔！;) 所以趕快學著用樞紐分析表吧！:)

調整各組組員結構

分組教學進行一段時間之後，會發現各組的組員需要稍微調整一下。有時候是組員不合，老師怎麼協調都無法收效，這時候還是重組組員會比較好；也有時候是不同組的能力差別太大，所以需要調整一下組員結構。無論原因為何，反正就是要調動人員就是了。

人員一旦調動，以傳統 =Average(D2,D6,D11,D12,D34) 方式統計小組成績的，就得一格一格重新點選一次，很煩。如果是用樞紐分析表做分組統計的呢？我們來看看吧。

比方說，我們發現第二組的成員太多了，而第一組的人太少，因此打算將陳小絡從第二組調到第一組來；另外，我也發現陳小絡的國一成績輸錯了，他是 89 分，不是 17 分。於是我在原始資料中，將陳小絡的組別改為 1，國一分數改為 89。

圖、在原始資料中修改組別 & 科目成績

回到樞紐分析表，按一下樞紐分析表工具列上的驚嘆號，然後，所有的資料就都更新了。陳小絡已經跑到第一組，他的國一成績也已經更正，輕鬆簡單就可以收工了。

圖、按一下驚嘆號，各組別馬上以新成員名單統計資料

Excel 提供的樞紐分析表提供了我們輕鬆進行分組比較的功能，很容易就可以把分組學習的成果做一個比較，事後的維護、調整也比較簡易，所以放棄以往的做法，改用樞紐分析表吧！:)

以前不知道樞紐分析表這麼好用，那時候是用 SPSS 來做比較的啊……現在知道這個好用的工具，就不用動用 SPSS 了。

小插曲

今天下午發 Email 給全校老師，教老師們怎麼用樞紐分析表。然後在信件中我說按！驚嘆號就能更新資料，還抓了一張大大的圖附在 Email 中給老師們參考。

圖、像在罵髒話的圖片說明

信件寄出去後才發現，哇咧，這樣好像在罵髒話耶！！XD 被我嚇到的老師抱歉啦，我不是故意的。

Technorati : Excel, MS Office, Office, 資訊化教育, 資訊教育, 資訊融入教學, 進階資訊檢測, 電腦密技

骰子、硬幣與多基因遺傳

學生在上數學課的時候，都會學到不斷拋擲公平骰子的話，最後骰子各個數字出現的次數會很相似。

一個公平的骰子共有六個面，每個面出現的機會都是 1/6。拋擲的次數不多時某個數字可能比較常出現，但是拋擲的次數變多了之後，每個面出現的次數就會很接近 1/6。

但是課本中講的到底是不是真的呢？老師上課時大概也沒有時間讓同學拋擲骰子做統計，所以很難確認課本內容的真假。

拋擲一枚公平的骰子

不過沒關係，我們可以利用 Excel 模擬出公平骰子，再讓 Excel 執行骰子拋擲的結果。因為是利用電腦模擬的，所以要拋擲個幾千、上萬次都沒問題。

我們先拿一顆骰子拋擲 6000 次，看看結果如何吧！

從底下的圖片裡可以看到，骰子投擲的前 12 次中，1 出現了 1 次；2 出現 2 次；3 出現 4 次；4 出現 2 次；5 出現 3 次；6 出現 0 次。除了 2 & 4 這兩個數字，其他數字出現的機率都不等於 1/6。不過當我們把骰子丟 6000 次時，每個數字出現的次數就都很接近 1/6 (1000 次)，(從這裡下載 Excel 原始檔進行測試，按一下 F9 鍵就會重新再丟 6000 次。可以看看是不是每次都有類似的結果。)

圖、公平骰子擲 6000 次時各數字出現次數

再來，我們拿兩個相同的骰子來投擲 9000 次(選擇 6000 次、9000 次沒什麼特別意義，祇是單純想到這兩個數字)，看看結果會如何吧！

兩顆骰子產生的組合變化

ㄟ，丟兩顆骰子的結果我們可以得到 2,3,4,5,6,7,8,9,10,11,12 等 11 種數字組合，將每種數字組合的出現次數畫個圖來表示的話，會發現兩端的 2 & 12 出現的次數最少；從兩端往中間靠隴時，出現的次數漸次增加，到最中間的 7 出現次數最多。

圖、兩顆骰子一起投擲 9000 次的次數統計

為什麼 2、12 出現的次數會最少，7 會最多呢？我們來看一下用兩顆骰子時哪些情況下會丟出 2；哪些情況會丟出 7 吧！

表、丟兩個骰子可以得到的數值清單

	１點	２點	３點	４點	５點	６點
１點	2	3	4	5	6	7
２點	3	4	5	6	7	8
３點	4	5	6	7	8	9
４點	5	6	7	8	9	10
５點	6	7	8	9	10	11
６點	7	8	9	10	11	12

從上表我們可以發現丟兩顆骰子時共有 36 種可能性，其中祇有兩顆骰子都丟出 1 點的情況才會得到 2。所以丟出 2 的情況祇佔 36 種可能性中的 1 種，機率 1/36。如果我們以 (第一顆骰子的值, 第二顆骰子的值) 來表示骰子丟出的值，要丟出 2 的情況就祇有 (1,1) 才會發生；要丟出 12 祇有 (6,6) 的這種狀況才會得到 12。

相反的，要得到 7 就簡單多了，(1,6) 可以得到 7；(2,5) & (5,2) 也都可以得到 7，所以丟兩顆骰子出現 7 的機會大的多。

也就是說，祇有一顆骰子時，六個面的出現機率都是一樣的；而有了兩顆骰子時，開始出現各種不同組合變化，而使某些情況的組合方式較多、比較容易出現。

那麼丟兩顆骰子時，總和為某個數字的機率有多少呢？我們來列表清算(*驚嚇*)一下：

表、丟兩顆骰子各種數字組合的出現機率

						(6,1)
					(5,1)	(5,2)	(6,2)
				(4,1)	(4,2)	(4,3)	(5,3)	(6,3)
			(3,1)	(3,2)	(3,3)	(3,4)	(4,4)	(5,4)	(6,4)
		(2,1)	(2,2)	(2,3)	(2,4)	(2,5)	(3,5)	(4,5)	(5,5)	(6,5)
	(1,1)	(1,2)	(1,3)	(1,4)	(1,5)	(1,6)	(2,6)	(3,6)	(4,6)	(5,6)	(6,6)
點數	2	3	4	5	6	7	8	9	10	11	12
機率	1/36	2/36	3/36	4/36	5/36	6/36	5/36	4/36	3/36	2/36	1/36

從上表可以發現，這個表格的樣子與前面把兩個骰子拋擲 9000 次的圖看起來很像。當我們把每個數字總和的可能情況列出來之後，就可以理解為什麼 2 & 12 的出現率最低，而 7 的出現率最高了：2 & 12 的出現率最低，是因為它們都祇擁有一種組合的可能；7 的可能組合種類最多 (六種)，因此它的出現機會也最高。

『老師，老師，你用電腦模擬的好沒感覺喔，我很想自己試著丟看看，驗證你說的是不是真的。可是我們家沒有骰子，我爸應該也不會讓我買骰子吧，那怎麼辦呢？』

沒有關係，沒有骰子，但你總會有硬幣吧？我們可以丟硬幣後統計正反面出現結果來看看是不是一樣得到上面的圖形。

不過，硬幣祇有正反兩面，祇丟兩個硬幣的話不夠有趣。要玩就玩大一點，我們一次丟個六枚硬幣，來看看硬幣的結果會怎樣！

六枚硬幣響叮噹，叮叮叮叮叮噹

我們先準備六枚硬幣，每枚硬幣上寫上編號，然後將出現正面的結果記為 1，出現反面記為 0，這樣子我們會比較好記錄。比方說第一枚硬幣出現正面，其他五枚都是反面的話，就記為 (1,0,0,0,0,0)；全都正面 (1,1,1,1,1,1)；全都反面 (0,0,0,0,0,0)。

因為六枚硬幣拋擲結果共有 64 種 ( 2⁶ )，記錄起來手會很痠 XD 記錄完後，我們將出現正面的結果統計一下，會發現在這 64 種結果中正面總數其實祇有 7 種，分別是出現 6 次正面、5 次、4 次、3、2、1，以及 0 次。

猜猜看丟六枚硬幣時，出現幾次正面的情形會最多？猜不到？來來來，我們把這 7 種狀況排成一列：

0123456

這樣的提示夠了嗎？你猜的出來了嗎？對，就像前面丟骰子的結果一樣，越中間的出現的機會越多，所以丟六枚硬幣時，出現三個正面的次數是最多的。因為我沒辦法在部落格上直接丟硬幣給你看，所以，我一樣是用 Excel 來模擬丟硬幣的結果。

圖、丟六枚硬幣 10000 次時正面出現的次數統計

常態分布與鐘形曲線

上圖是用 Excel 模擬丟六枚硬幣 10000 次得到的結果，我們再一次看到兩端出現的最少、越往中央靠隴出現的次數越多，且最中央的項目出現次數最多的圖形了。也就是說在這種狀況下，我們很難遇到六枚硬幣都是正面 (1,1,1,1,1,1) 或是六枚都是背面 (0,0,0,0,0,0) 的結果，我們最容易遇到的是丟出 6 枚硬幣，其中 3 枚正面、3 枚背面。

這種極端值很少而中間值很多，而且每個類別連續的情況，我們稱之為『常態分布』。如果我們把前圖的長條圖頂點用線條連接起來，連起來的線條外形很像一個鐘，所以又可稱為『鐘形曲線』。

圖、鐘形曲線

在常態分布 (鐘形曲線) 的情況下我們較少遇到極端值，比較容易遇到中庸的傢伙。這種常態分布的狀況普遍存在於各個地方，比方說我們人類的膚色 & 身高就都屬於常態分布。

人類膚色與身高的常態分布

為什麼人類的膚色 & 身高會是常態分布呢？為什麼豌豆的高矮不是連續的常態分布，而是祇有高、矮兩種狀況呢？

想不出來？提示你，想一想前面提到的骰子 & 硬幣吧！

是的，豌豆的莖高祇有高矮兩種性狀的原因，是因為它的性狀由一對基因控制；而人類的膚色 & 身高呈現常態分布的原因就是這兩種性狀都是由多對基因控制，所以會有各種組合變化。

人類的膚色由三對基因控制，ㄟ，假設是 A、B、C 這三對基因好了，這三對基因的顯性為深色膚色，隱性為淺膚色，而且，擁有越多的顯性遺傳因子，膚色就越深，比方說 AABBCC 就會比 AaBBCC 膚色深；AABBCC 的膚色最深，aabbcc 的膚色最淺。

如果我們不用 ABC 表示，而以 1 表示顯性，用 0 表示隱性，所以膚色最淺的 aabbcc 表示為 (0,0,0,0,0,0)，擁有 0 個顯性遺傳因子；膚色最深的 AABBCC 可以表示為 (1,1,1,1,1,1)，一共有 6 個顯性遺傳因子，人類膚色的等級由淺至深可以分為 0123456 等 7 個等級。

ㄟ，這不是與前面 6 枚硬幣的例子一樣了嗎？

是啊，沒錯啊！要不然為什麼要讓你用 6 枚硬幣做練習，而不用 3 枚、4 枚 或 5 枚呢？這就是要為現在鋪梗啊，夠心機吧？學生是一種神奇的生物，但老師是一種心機重的生物！！:D

圖、人類膚色分布

不過我們還是回到我們的 ABC 三對基因吧！為了更清楚人類膚色分布，請你把底下的表格完成，看看膚色基因可以有哪些排列組合，各造成什麼樣的膚色：

表、人類各種膚色基因組合

				AABbcc
				AabbCC
	aabbcc	aabbCc	aabbCC	aaBbCC	aaBBCC	AaBBCC	AABBCC
膚色	超白	白	中偏白	中	中偏黑	黑	超黑

　

外星來的訪客

因為人類的膚色是常態分布，所以如果今天有一個外星人來地球上做研究，他把地球人從 1 號編號到 60 億號，然後任意挑選其中幾個號碼的人出來觀察他們的膚色，這個外星人會發現他所抽中的人類中，黃皮膚的人會最多。而且運氣好的話，這些被抽中的人還可以一起在吧檯喝飲料聊天！

『等等，等等，等等！老師，你說的有問題！現在是黃種人最多沒錯，但是黑人的數量也很多啊！你到非洲去看，都是黑人比較多啊！而且他們的人口比歐美來的多，所以這個外星人如果從地球上任意抽選幾個人出來，可能黑人佔的比例也很高啊。』

呣，沒錯，雖然我們計算基因比例時發現黑人應該佔很低的人口比例，但是基因的表現會同時受到先天 & 後天的影響，而且一些環境條件也會對各種不同的膚色做出天擇而影響各地區的膚色人口比例。Nina Jablonski 說：『我們現在有 NASA (美國太空總署)，可以知道全球各地紫外線分布的情形，以及其對人類膚色分布的影響。』

所以，外星訪客會找到很多黑人，這不是我們計算錯誤，而是紫外線搞的鬼啊！:P

參考資料：

1. 圖解 Polya 計數法
2. 機率: 化永恆於須臾的資訊表示法
3. 從高矮看遺傳教學 -- 多基因遺傳教學記錄
4. 多對基因所決定的遺傳特徵

Technorati : Excel, MS Office, Office, TED, 創意, 教學影片, 教學設計, 教材, 演化, 生物教學, 科學教育, 資訊化教育, 資訊融入教學

用 Excel 模擬骰子拋擲結果

我想要用 Excel 的亂數函數 Rand() 來模擬骰子的拋擲結果。

依據 Rand() 的說明文件，如果想要得到 a~b 之間的亂數，就用 =Rand()*(b-a)+a 的方式計算。我想要模擬骰子拋出 1~6 的數字，因此就以公式 =Rand()*(6-1)+1 丟入 Excel 中做計算。

啊，對了，因為我祇想要整數，所以用四捨五入函數 Round() 來對產生的亂數處理一下，整個公式變成為 =Round( Rand()*5+1,0) 。

將公式輸入 Excel 果然得到 1~6 的亂數，太好了。

ㄟ，等一下，這個公式在拋擲次數少的時候看起來一切正常，但是模擬拋擲 1000 次、10000 次時，就會明顯發現 1 & 6 出現的機會祇有其他數字的一半。

呣，怎麼會這樣呢？將問題丟到網路上，沒多久，好友帆帆就來解答我的疑惑了。

我們仔細看看 =Round(Rand()*5+1,0) 這個公式，裡面的 Rand()*5 會產生 0~4.99999 的亂數。其中：

• 0~0.49999： +1 後為 1~1.49999，四捨五入為 1
• 0.5~1.49999： +1 後為 1.5~2.49999，四捨五入為 2
• 1.5~2.49999： +1 後為 2.5~3.49999，四捨五入為 3
• 2.5~3.49999： +1 後為 3.5~4.49999，四捨五入為 4
• 3.5~4.49999： +1 後為 4.5~5.49999，四捨五入為 5
• 4.5~4.99999： +1 後為 5.5~5.99999，四捨五入為 6

仔細看上述說明，要得到 1 或 6，原始的亂數必須介於 0~0.49999 & 4.5~4.99999 之間，大概都祇有 0.5 的區間。而要得到 2、3、4、5 這些數字，都有完整的 1 個區間可以得到這些數字。因此在拋擲量大時就會顯現 1 & 6 得到的次數祇有其他數字的一半。

如果要得到正確的骰子結果，應該將公式改為：=Int(Rand()*6+1) (Int 函數為取整數值的意思)。其中 Rand()*6 會得到 0~5.999999 的值：

• 0~0.99999：+1 為 1~1.99999，取整數值為 1
• 1~1.99999：+1 為 2~2.99999，取整數值為 2
• 2~2.99999：+1 為 3~3.99999，取整數值為 3
• 3~3.99999：+1 為 4~4.99999，取整數值為 4
• 4~4.99999：+1 為 5~5.99999，取整數值為 5
• 5~5.99999：+1 為 6~6.99999，取整數值為 6

圖、公平骰子擲 6000 次時各數字出現次數

利用這樣的公式，得到 1~6 的每個數字的區間都相同，因此出現 1~6 的機會就都一樣了。當我將這個公式執行 6000 次，每個數字出現的機會都趨近相同 (約略各 1000 次)。所以，要用 Excel 模擬骰子要很小心啊，一個沒注意就把出現機率弄錯了，變成一顆會作弊的骰子啊。

附註 -- 與帆帆的對話：

	內容
帆帆	因為你要用 continue 的機率函式模擬 discrete 機率函式，rand()*5 才只有五單位，要用五單位的機率密度函數來模擬 1,2,3,4,5,6 六單位的不連續機率密度函數，就分的不平均，又剛好四捨五入，所以 1,6 只分到 0.5 的機率密度。
Yukie	依照 Rand() 的說明是，要求 a-b 之間的數字，就用 rand()*(b-a)+a 所以我才這樣寫。因為我想求 1-6。
帆帆	它那個說明是只針對連續型的機率密度函數才能這樣做。 rand()*(b-a)+a, 如果用0,1(正反面)來算，rand*(1-0)+0，如果是 round，剛好四捨五入就可以用 round； 但是用 int 就只會出現 0，出現 1 的機會幾乎是 0。 所以要以連續機率密度函數去 mapping 不連續機率密度函數會需要注意轉換的計算方式。

Technorati : Excel, MS Office, Office, 資訊化教育, 資訊教育, 資訊融入教學, 進階資訊檢測, 電腦密技

Excel 基礎教學：簡介 & 定位方式

Excel 的主要功能是進行計算，小自簡單的加減乘除，大至多重計算均可勝任。

平常常見老師們利用 Excel 計算成績，但除此之外似乎較少見到老師應用 Excel。其實在算成績之外，老師們算學生的便當費用、補充教材印刷費用等工作若改用 Excel 進行，可以大大簡化整個過程。

比方說，本週有 35 位同學訂便當，每個便當 55 元；國文補充教材印刷費 10 元/人，扣掉資源班同學，有 32 個同學需繳國文補充教材費；數學補充教材印刷費 12 元/人，34 個同學需繳費；英文補充教材 11 元/人，33 位同學需繳費。

用計算機按的話，上述計算必須按 35*55*5(天) + 10*32 + 12*34 + 11*33 = 10716(元)，一個不小心按錯，就要整個重來；有時候是按完後發現當中有幾個數字弄錯了，國文教材應該是 12 元，英文教材才是 10 元，遇到這種狀況還是得整個重來。

但是，如果用 Excel 進行計算，我們祇需將輸入的那一格資料更新就資料更正完成，檔案存下來還可以下週繼續使用，可以節省許多額外的氣力。所以我平常就開著 Excel 在旁邊，有需要計算時就用它代替計算機。

也許有人會說：『用 Excel 做這麼簡單的計算，真是大材小用！』嗯，啊程式都買了，也灌進電腦裡面了，不用白不用啊！:P 既然都買了，就是要儘量使用啊！所以計算成績也好，計算學生要繳交的費用也好，都用 Excel 來計算吧！:)

Excel 座標定位

Excel 由許多格子所組成，每一個格子都以『英文-數字』進行座標定位。X 軸以英文表示，由左至右依照 A、B、C 的順序排列；Y 軸以數字表示，由上至下依照 1、2、3、4 的順序排列。因此最左上方的格子是 A1，A1 的右手邊是 B1，A1 的下方是 A2，依此類推。

圖、Excel 的格子以『英文-數字』進行定位

滑鼠點到格子上就可以開始輸入資料，如果沒有特別指定，則輸入的資料就直接呈現；若一開始先輸入 = (等於) 符號，則開始進行計算。

比方說，輸入 35*32，那麼在格子內呈現的就是 35*32 這 5 個字；如果輸入的是 =35*32，那麼格子內呈現的是計算結果： 1120。

圖、輸入等於 (=) 符號才能進行計算

資料的計算不限於數字的計算，也可以格子與格子之間做計算，比方說要讓 A1 這一格資料與 C1 這一格資料相乘，就祇需要輸入 =A1 * C1 即可 (別忘了一開始的 = 符號)。如果在 B1 這一格寫『=A1』，因為沒有使用其他運算符號，所以不做計算直接以 A1 的資料填入 B1。

參考座標改變的方式

因為 Excel 的功能是進行計算，在計算時我們常將格子內的資料資料複製至其他地方繼續進行計算。但是複製過去後參考的座標點也許需要跟著改變，為了節省我們的時間，Excel 會自動的變更參考點。

我們把格子內的資料往橫向複製時，定位座標『英文-數字』中的英文部份 (X 軸) 會改變；往縱向複製時，數字部份 (Y 軸) 會改變。如果希望複製的時候不要自動改變，就要加 $ 號。

$符號的意義：不改變

• 『英文-數字』：複製時 X 軸英文部份與 Y 軸數字部份都會改變
• 『$英文-數字』：不改變 X 軸英文部份，但會改變 Y 軸數字部份
• 『英文-$數字』：改變英文部份，但不改變數字部份
• 『$英文-$數字』：英文 & 數字部份都不會改變

例如我們在 A1 這一格中填入 5100，在 D1 這一格填入 =A1，則 D1 也會顯示 5100。接著我們把 D1 這一格分別往橫向、縱向複製，結果發現複製後的結果都是 0，為什麼會這樣呢？

圖、無特別指定時『英文-數字』均會變動

原因是這樣子的：我們在 D1 這一格填的是 =A1，所以複製時 X 軸、Y 軸的『英文-數字』都可以改變。往橫向複製到 E1、F1 這兩格時，內容分別變為 =B1、=C1 (英文部份變了)，因為 B1、C1 都沒有資料，因此 E1、F1 都顯示為 0。

縱向複製時，D2、D3 的內容分別是 =A2、 =A3 (數字部份變了)，而 A2、A3 都是空格無資料，因此 D2、D3 對應到 A2、A3 時也祇能得到 0。

現在我們把 D5 這一格填入 =$A1，要求英文部份不改變，則往橫向複製至 E5、F5 時，這兩格的內容仍然為 =$A1，可以得到 5100 這個數值；縱向複製時，D6、D7 的內容分別是 =A2、 =A3 (數字部份變了)，得到 0 的答案。

阿剛 補充：
要在 Excel 輸入 $ 符號不用辛苦的按 Shift-4，祇要輸完格子的座標後，按 F4 就可以自動切換了。

比方說格子內的資料本來是 =A1，按一次 F4 變成 =$A$1；按第二次 F4 之後變成 =A$1；按第三次 F4 鍵變成 =$A1。再按一次 F4 鍵就恢復 =A1。

圖、『$英文-數字』不改變 X 軸英文部份

再來我們把 D9 這一格填入 =A$1，要求數字部份不改變，則往橫向複製至 E9、F9 時，這兩格的內容分別變為 =B$1、=C$1 (英文部份變了)，得到 0；縱向複製時，D10、D11 的內容分別是 =A$1、 =A$1 (數字部份不變)，得到 5100。

圖、『英文-$數字』改變英文部份，但不改變數字部份

最後我們把 D13 這一格填入 =$A$1，要求英文、數字部份均不改變，則往橫向複製至 E13、F13 時，這兩格的內容均為 =$A$1 (英文部份不變)，得到 5100；縱向複製時，D14、D15 的內容也都是 =$A$1 (數字部份不變)，得到 5100。

圖、『$英文-$數字』：英文 & 數字部份都不會改變

$ 符號的應用：用加權數計算成績

我們計算學生成績時，有時候會將每週上課時數做為加權，讓上課節數多的科目得到比較高的權重。比方說臺中市進階資訊能力檢測的題目之一：

以「加權計分」方式計算「平時成績」：加權數標示於每計分單項名稱下方（紅色區塊），「平時成績」計算公式為「平時成績 ＝（作業一 × 作業一加權數＋ 作業二 × 作業二加權數）÷（加權數總和）；並且，在更動加權數時，系統會即時自動依照加權數計算平時成績。

這時候我們祇要將加權數列於一行，成績乘加權數時，利用 $ 符號把加權數所在的那一格固定住，將公式複製至其他格子時就不會把加權數給改掉到了。

圖、利用 $ 指定加權數的座標

在上圖中，我們將趙中華的平時成績設定為 =(E4*$E$3+F4*$F$3)/($E$3+$F$3) ，利用 $ 將加權數所在的格子固定住。接著我們將趙中華的平時成績複製到林台生的成績格，公式變為 =(E5*$E$3+F5*$F$3)/($E$3+$F$3) ，仍然正確的指向加權數所在的位置，這樣林台生的成績也 OK 了。

如果在上圖中忘了加上 $ 符號，那計算出來的成績就全都錯了，所以要指向某個固定位置時一定要記得加 $ 符號，切記！切記！

Technorati : Excel, MS Office, Office, 資訊化教育, 資訊教育, 資訊融入教學, 進階資訊檢測, 電腦密技

Excel 的數字前自動加 0 (放大絕)

有時我們會拿到一些已經輸入完成的 Excel 檔案，我們想將裡面月份、日期都修改為兩位數，甚至再加以合併。

但因為檔案已經輸入完成，所以將儲存格改為文字格式已經來不及了，那怎麼辦呢？

針對上述問題，基礎篇教大家怎麼亡羊補牢，進階篇教大家利用 REPT、LEN 兩個指令將分成三欄的年、月、日資料匯整在一起。

不過進階篇提到的指令雖然讓我們對於 Excel 有更多的瞭解，但每次要將資料匯整成一格時恐怕都忘記怎麼用了，得再查閱一下文章解說才會下指令，使用上不是那麼便利。

其實要將年、月、日資料整合在同一格有更快的方法：把檔案存成 TXT 檔再讀入 Excel 即可。

底下我們先用基礎篇教的方法，先將年、月、日的儲存格格式都設定好並存檔：

然後從檔案選單中選擇『另存新檔』

存檔時，檔案格式設定為 Unicode 文字 (.txt) 檔。

雖然說存為『文字檔 (Tab 字元分隔)(*.txt)』也是可以的，但是怕實際應用時 Excel 檔內有些學生的名字用到特殊字，貯存為普通文字檔的話，那些名字會變成亂碼，所以還是貯存為 Unicode 文字檔吧，這樣特殊字都可以保留。

設定存為文字檔後，Excel 會給你警告畫面，按下『確定』就好了。

之後還會再來一個警告畫面，一樣不管它，按『是』即可。

存好後，將檔案關掉，然後再重新開啟這個 TXT 檔。

這時候就會跳出一個畫面，問我們要對這個 TXT 檔做什麼樣的處理。因為我們之前存的 TXT 檔是以 TAB 鍵來區分欄位，所以就選擇第一項『分隔符號』吧！

再來，Excel 會問我們使用的分隔符號是什麼，勾選『Tab 鍵』及『逗號』兩個選項，再按下一步。

再來就是重點了，我們要在這邊設定每一個欄位的格式。原本 Excel 內定欄位格式為『一般』，我們要將它改成『文字』。

點選一下『文字』，將第一欄的格式設定為文字。

滑鼠點選一下第二欄，也將它設為文字格式。

第三欄的日期也是相同的處理。三個欄位都將格式設定為文字之後，按下完成鍵。

你可以發現匯入的資料這時候都是以『文字』的格式貯存 (在 Office2003 以後左上角有綠色小三角型)，表示這邊呈現的符號是像電話號碼一樣，是文字而不是數字，不能用來做加減乘除計算的。

因為貯存格裡面的資料是文字資料，所以要將它們匯整起來，祇要用 & 直接串接起來就可以了。

串接起來的結果完全正常：

之後利用複製的方式，將所有格子都複製好，再另存新檔為 Excel 檔案即可完工。

會有這樣的結果是因為，Excel 面對數字時，會將其前方的 0 刪除；但是面對文字時，就會把 0 保留 (總不能把電話號碼前面的 0 刪除吧？那就無法記錄了啊。) 所以我們就利用這個特性達成我們想要的結果。

那可不可以存成 CSV (*.csv) 檔再來開啟呢？

答案是不行。

因為 Excel 開啟 CSV 檔時，不會出現詢問欄位格式的畫面。而是直接將所有欄位設定為『一般』來開啟，所以前面的 0 都會被移除。

『可是，可是，別人交給我的檔案就是 .csv 啊，怎麼辦？』

沒關係，先將它的副檔名改為 .txt，這對檔案不會有影響的，祇是改副檔名而已。改好名稱後，再用 Excel 開啟它，就會出現詢問的畫面。然後依照上面的做法，將欄位都設定為『文字』就一切 OK 啦！！:D

這個方法比較取巧，但是速度比較快，實際應用上應該會比前一次介紹的更方便！大家試用看看吧！:)

Technorati : Excel, Office, 資訊課程, 電腦密技