2012/08/31

解決學務系統學號重覆問題:修改資料表主鍵值

前天,我們註冊組長跟我說了一件慘案。

學務系統當初設計時以年份加上流水號的方式組成學生的學號,要新建一筆學生資料時以民國年份的個位數做為學號的第一位數,然後再加上四位數的流水序號 (目前還沒有學校能夠一個年級就超過 9999 位學生吧?),這樣就可以組成一個學生的學號了。

比方說今年是 101 年,個位數是 1,所以學號會是在 10001 ~ 19999 這個範圍;明年 102 年,個位數是 2,學號就是 20001 ~ 29999。

這樣的想法很簡單明瞭,但麻煩的是,學務系統已經設計十年了,所以包含我們學校在內,許多用學務系統的學校都發生 101 年新生資料覆蓋 91 年學生資料的慘劇。因為 91 年與 101 年的學生學號都是 10001 ~ 19999,結果新的資料就把舊資料給覆蓋掉了。

啊 啊 啊 啊 啊 *抱頭大叫*

舊生資料被覆蓋掉?那很嚴重耶!

還好立人國中湯慧玲組長與教網中心的高手討論後得到解決的方法。請各位先檢查是不是發生了資料覆蓋的情形,如果已經發生慘劇了,就請依照下列步驟修改資料吧!

  1. 將資料回復至舊學年資料。我們學校註冊組是在 8/14 將學務系統升級為 101 學年度,所以我就必須回復至 8/13 日的資料。是的,8/14 ~ 今天的所有更動都必須重做一次。
  2. 修改 stud_base 資料表的主鍵值

如果比較少用 mysql 的話,修改主鍵值的方法如下:

mysql -u root -p
mysql>use sfs3;
mysql>show create table stud_base;
mysql>ALTER TABLE stud_base DROP PRIMARY KEY;
mysql>ALTER TABLE stud_base ADD PRIMARY KEY (`stud_id`, `stud_study_year`);

修改後再請註冊組將學期初該做的設定重新設定一次吧!可憐的註冊組長!!

Technorati :

2012/08/28

高美濕地、香山豎琴橋 & 竹圍彩虹橋

颱風天因為空氣中富涵水氣,太陽光中波長比較短的光線被水氣折射,祇留下波長較長,較不易折射的紅色光,所以在颱風來臨前天邊容易出現紅色的彩霞。

這幾天受到天秤颱風影響,空氣中的水氣相當豐厚,很有可能有美麗的彩霞出現,所以趁著週末假日跑一下幾個地方,希望能拍到美麗的彩霞。

高美濕地日出

我與幾個朋友在 8/26 凌晨跑到高美濕地拍日出,那天沒讓我們失望,日出前的天色非常豔麗。

高美濕地日出
圖、高美濕地日出

隨著太陽逐漸昇起,天色越來越紅了。

高美濕地日出
圖、高美濕地日出

這天很巧合的是有片烏雲擋住了太陽,整個天色雖然都亮起來了,但是不會刺眼。

高美濕地日出
圖、高美濕地日出

後來,整個海面都染紅了,非常的壯觀。

高美濕地日出時染紅的海面
圖、高美濕地日出時染紅的海面

在日出光線的照映下,風車與堤防也被染上了色彩。

高美濕地日出彩霞與風車
圖、高美濕地日出彩霞與風車

難得有機會看到這麼美麗的日出,實在是太滿足了。拍完日出要離開時,有好心的路人說彰濱工業區的日出會更美。嗯,無論如何,我已經很開心了。:)

豎琴橋晚霞

早上拍完日出,朋友又提議去新竹拍豎琴橋。放假日在家其實也都是睡覺、上網而已,倒不如趁著這次機會去碰碰運氣,看看能不能拍到美麗的豎琴橋。

所以下午我們就出發到新竹香山去拍豎琴橋了。

新竹香山豎琴橋
圖、新竹香山豎琴橋

拍了一張,嗯,車子的軌跡不明顯,再拍一張,結果設定的不好,橋太亮了。

比較特別的是,因為忽然飄來一片烏雲,這片烏雲豐沛的水氣反射了地面的光線而呈現紅色,所以天空還滿特別、滿漂亮的。

新竹香山豎琴橋與紅雲
圖、新竹香山豎琴橋與紅雲

這片烏雲雖然漂亮,但是也帶來了雨水,讓我們沒辦法繼續拍下去。

從下午四點到達豎琴橋邊,等到六點半開始要拍照,一個多小時的交通時間與兩個半小時的等待,結果拍照時間不到兩分鐘,真是有些遺憾。

竹圍漁港彩虹橋晚霞

隔天我們再去桃園竹圍漁港附近的彩虹橋拍照,不過這天的晚霞就比較平淡一些,祇將天空淡淡的渲染上一層色彩,幾秒鐘後就歸於平淡。

桃園竹圍漁港彩虹橋晚霞
圖、桃園竹圍漁港彩虹橋晚霞

我看到晚霞消褪就打算收工回家。我朋友告訴我,等橋上的燈亮起來那畫面也很美,我才知道我耍笨了。

桃園竹圍漁港彩虹橋燈光
圖、桃園竹圍漁港彩虹橋燈光

等到燈光全亮起來,照亮了橋面也倒映在海面上,果然是座美麗的橋。

桃園竹圍漁港彩虹橋燈光
圖、桃園竹圍漁港彩虹橋燈光

這週末接連著兩天四處拍照,也許沒拍出什麼好作品,但是出去走走總是比宅在家中睡覺、上網好的多。

這些相片如果有老師要做教材,歡迎使用,全部以創用 CC 授權。

參考連結:

Technorati :

2012/08/24

颱風前夕的高美濕地

今天下班後開車在國道一號上,看到台中港方向天色不錯,除了清澈的藍天外還有些白雲。

這麼好的天氣,待會也許會出現俗稱火燒雲的美麗晚霞吧?所以暫且不管咕嚕咕嚕叫的肚子,直接殺到高美濕地去。

到了高美濕地已經 18:15 左右,岸邊擠滿了同樣是來攝影的同好。等到我架好相機,拍第一張相片時,已經是 18:22 了。不過這正好,代表再過幾分鐘就太陽西下並可看到滿天彩霞了。

高美濕地的風車與夕陽
圖、高美濕地的風車與夕陽

幾分鐘後,太陽完全落至地平線以下。雖然已經看不到太陽,但是因為大氣層中折射了一些光線的原因,因此,雖然太陽已經落下,但是天空卻佈滿了紅色的雲彩。

高美濕地的晚霞
圖、高美濕地的晚霞

18:39,太陽已經落下海面好幾分鐘了,晚霞的範圍也逐漸縮小。不過今天還滿特別的,晚霞將暗之前,竟從太陽落下處發出數道光芒,很有意思。祇是跟旁邊的晚霞相較起來,這幾道光芒似乎就不是那麼顯眼了。

光芒四射的高美濕地彩霞
圖、光芒四射的高美濕地彩霞

雖然今天的彩霞很美,但是上個月蘇拉颱風來臨前夕的晚霞更美,整片天空都是美麗的雲彩。祇不過那時我站錯地方,風車小小一支,也沒有海水映射晚霞,拍出來的結果扣分不少。

蘇拉颱風前夕的高美濕地晚霞
圖、蘇拉颱風前夕的高美濕地晚霞

這幾次的颱風前夕都有令人滿意的美景出現,上回拍 101 大樓也是在杜蘇芮颱風前夕,坐在象山六巨石上拍照時,一面拍照,一面跟身旁的陌生人討論:『哇,今天的天空真的好美!』如果能有美景出現,但是不要有隨之而來的風雨破壞那就好了 (蘇拉颱風我家滲水好嚴重,事後清理好久 Orz )。

參考連結:

Technorati :

2012/08/23

昏倒羊

剛剛寫了一篇好動的小羊之後,阿簡建議可以連昏倒羊一起討論。

哎啊,我平常是會播放昏倒羊的影片給學生看,然後問學生幾個問題,但是剛剛寫好動的小羊時卻沒有把這兩件事兜起來。阿簡的建議太好了,事不宜遲,趕緊再將昏倒羊的影片放上來。

所謂的昏倒羊,是一種擁有特別基因的羊,祇要受到驚嚇,牠們就會全身僵硬倒地,樣子十分有趣。

在影片中有提到,這些羊其實是得了先天性肌肉僵直病,才會一受到驚嚇就四腳朝天的倒下來。

給學生的問題思考

  1. 這些羊生活在大自然時,牠們這種特性對於生殖演化有什麼影響?
  2. 農場主人為什麼會想要養這樣的羊?

深入探究

其實農場主人養這些羊是要利用牠們一受驚就倒下的特性,讓入侵的狼隻吃掉這些不會跑的羊,並藉以保護其他經濟價值更高的動物 (如綿羊),也就是說,這些羊一出生就是要做為犧牲打之用。

這與《姊姊的守護者》一書的情況非常相似啊!

姐姐凱特罹患了白血病,需要進行移植手術,才能夠繼續存活下去。凱特的父母親決定再生一個孩子,希望這個新生兒能夠成為移植手術的捐贈者,幫助姐姐延續生命。

因此這個新生兒,她叫安娜,一出生就是為了要延續姐姐的生命,那麼,對於安娜自己而言,生存的意義又是什麼呢?

也許你會說,農場裡的雞、鴨、豬,都是一出生就是要延續人類的生命,所以昏倒羊有什麼好討論的?

ㄟ,這還是有一些不同。昏倒羊的例子不是羊對人,而是羊對羊的關係。

當昏倒羊的存在是為了讓其他的羊生存下去,是不是代表,在農場主人的心目中,這些昏倒羊比不上其他的羊?當安娜的出生,是為了要讓姐姐凱特存活,那麼在她的父母心中,姐姐是更重要的。那麼對安娜而言是不是傷害?(還好昏倒羊不會思考這些問題。)

從昏倒羊的生物現象導入閱讀,再導向生命意義的探討,這樣的生物課應該會比較有趣吧?:)

當然,我們的課程時間可能無法讓學生在班上閱讀這一本書 (or 影片),但是可以建議學生去圖書館借閱。在課堂上提點一下,有興趣的學生就會去借來閱讀,之後再跑來與老師討論,這是我很喜歡的授課方式。

這樣的教學方式讓我成功的引誘不少學生去閱讀《銀河英雄傳說》等書,《姊姊的守護者》是我下一個引誘的目標。:D

參考連結:

  1. 姊姊的守護者》原著小說
  2. 姊姊的守護者》DVD
  3. 銀河英雄傳說》:極權主義與民主社會陣營的戰爭

Technorati : , ,

好動的小羊

之前在網路上看到一隻好動的小羊。別隻羊都是慢慢走,祇有牠完全靜不下來一直跳來跳去。

如果祇是這樣不停的跳來蹦去倒也就罷了,但是牠還去踢倒同伴,真是有夠皮!

看了影片之後想一想,如果這一隻好動小羊長大後也依然如此,那麼你覺得這一隻小羊的好動行為在自然界將有助於演化,讓牠未來擁有比較多的後代;還是對於演化不利 (擁有比較少的後代)?為什麼?

你可以嘗試的思考點:

  1. 生活棲地的特徵
  2. 能量轉移過程
  3. 生物間的關係 (捕食 v.s. 被捕食、寄生 v.s. 被寄生)

考慮多種因素之後,你覺得是有利演化還是不利於演化呢?你的理由為何?

Technorati : , ,

2012/08/16

在 Excel 中自動排序資料 (下)

上一次利用 Row()、Large()、Match()、Index() 這四個函數,我們讓 Excel 也能夠自動排序。

祇不過 Match() 與 Index() 並不是那麼直覺,如果比較少用的話,臨時使用時會想不起來要怎麼寫公式。

後來我想到,利用 Right() & Indirect() 函數來代替 Match() 和 Index() 應該會更容易一些。

原理說明

上回我們把 E 欄原始資料複製到 G 欄時,為了讓日後得以反查回去,所以在複製時特地加上了 ROW()*0.000001 做為特徵碼,排序後再用 Match() 反查資料。

因為後面加的特徵碼不一樣 (每個學生擁有獨立的一列,既然所在的列不相同,那麼 ROW()*0.000001 的結果一定不一樣),就算是遇到了總分相同的情況,我們還是能夠正確反查。

如果偷懶沒加特徵碼,遇到同分狀況,我們的反查就永遠祇能查到第一筆資料。比方說小黑 & 小昕都是 159 分,不加特徵碼的情況下,我們會看到 Excel 列了兩筆小黑的資料,但是沒有小昕的資料,祇有加了特徵碼才能免除這個錯誤。

我們利用 Match() 反查時,因為加了特徵碼後的每筆資料都是獨一無二的,可以查到排序後的資料在 G 欄的位置,然後再從這個位置找出最原始資料。

但是何必這麼麻煩呢?

既然我們加的特徵碼是原始資料所在的列號,那麼我祇需看一下後面的特徵碼,就知道這筆資料原本是在哪裡了啊。比方說我看到其中一筆資料是 166.000009,最後面加的數字是 .000009,表示它來自於第 9 列,馬上可以找出原始資料。就是這麼簡單,不必再用複雜的 Match() 來比對了。

Excel 自動排序更新版

雖然新的方法比較簡便,但是一開始的資料複製 & Large() 排序還是得做,所以前兩個步驟與上一回的教學是相同的。

複製資料並用 Large 函數排序
圖、複製資料並用 Large 函數排序

排序後,我們要看看這些資料原本是來自於哪裡。這時我們可以用 Right( ) 函數抓出最後的幾位數,就可以知道資料來源。

Right( ) 函數:

功能:找出字串最右邊數回來的 N 個字元
用法:Right(字串, N)

因為我們加的特徵碼是整個數值的最右邊 6 碼,所以我下 =Right(H2, 6) 這樣的指令就能把 H2 這一格資料的最後 6 位數截取下來。

利用 Right( ) 函數截取最後 N 個字元
圖、利用 Right( ) 函數截取最後 N 個字元

其實用 =Right(H2, 2) 截取 2 個字元就可以了,因為一個班級的學生頂多就是 30~40 位,所以祇截取最後 2 位數就好了。截取 6 位數完全祇是做一下示範而已。

截取下來的 6 位數字是 000007,表示 H2 這個總分排名第一的資料是來自於第 7 列。

以 Right( ) 截取的 6 個字元
圖、以 Right( ) 截取的 6 個字元

OK,我已經知道第一名的學生是第 7 列的學生,所以我看 B 這一欄的第 7 列,就可以知道考第一名的是小健。所以我輸入 ="B"&I2 ,因為 I2 現在是 000007 ,我這樣寫希望是希望 Excel 幫我顯示 B7 的資料,就像我們一開始在 G2 這一格寫 =E2 一樣。

但是我錯了,我輸入 ="B"&I2 之後顯示的是 B000007 ,變成了一串文字,這跟我要的答案完全不一樣啊!

& 符號:

功能:將 & 符號前後的字串連接起來
用法:"字串"&"字串" ,字串前後要加上引號
範例:="32"&"45"&"13" 會串成 324513 這樣的字串;="你"&"好" 會變成你好

後來找了一下,Indirect( ) 函數可以達到我想要的結果。

Indirect( ) 函數:

功能:顯示文字串所對應的儲存格
用法:Indirect(字串)

既然 Indirect( ) 可以把一串文字解讀要對應的儲存格,那麼我就把剛剛的 "B"&I2 寫在 Indirect 的括號裡面,讓 Indirect 顯示 & 符號組合出來的字串所對應的儲存格資料吧。

利用 Indirect( ) 函數顯示其他儲存格的資料
圖、利用 Indirect( ) 函數顯示其他儲存格的資料

好,正確的找到總分 175 分的考生小健了!再來是要顯示小健的總分。

因為在原始資料中總分是放在 E 這一欄,所以我祇要用 "E" 串上 Right( ) 找到的列號,再用 Indirect( ) 就可以得到總分資料。

再利用 Indirect( ) 函數查總分
圖、再利用 Indirect( ) 函數查總分

結果相當讓人開心,我們用簡單的 Right( ) 與 Indirect( ) 同樣能找到排名第一的學生以及他的考試分數。

利用 Right( ) 與 Indirect( ) 查得原始資料
圖、利用 Right( ) 與 Indirect( ) 查得原始資料

既然公式正確的幫我們找到排名第一的學生了,那麼就把公式複製到其他格,依序排列第二名、第三名的學生吧。

將公式複製至其他儲存格得到完整的排序結果
圖、將公式複製至其他儲存格得到完整的排序結果

這次我們利用 Right( )、Indirect( ) 得到了與上回使用 Match( )、Index( ) 時相同的結果。換用 Right( ) 與 Indirect( ) 讓我們的做法變的比較直覺,很容易理解。

更重要的是,我們要輸入的公式變得簡短很多,不用擔心儲存格要不要加 $ 的問題,所以用新的公式出錯的機會應該會大大降低。希望這個新的方法能讓你更快速的解決問題。

Technorati : , , , ,

2012/08/14

在 Excel 中自動排序資料 (上)

我們利用 Excel 的排序功能時,每一次想要看到排序資料,就得手動點選排序功能 Excel 才會幫忙排序。

如果希望 Excel 自動排序,每打一筆資料就顯示即時的排序結果,目前 Excel 並沒有提供直接的工具讓我們使用。要嘛就是得寫 VBA 程式,要嘛就是得轉幾個彎來達成這個目的。

以下的方法是我在 How to Sort with Formula 這個網頁所學到的。不過原作者寫的比較簡略,可能不是那麼容易就看懂,所以我試著從他的基礎出發再寫得更詳細一些,希望能幫助理解。

Excel 自動排序的原理解說

假設現在有三個數字 77、32、58,請問一下,這三個數字當中最大的是哪個數字?77?很好。那第二大的數字呢?58?不錯。第三大是哪個呢?32?很好。

現在我們把最大的數字、第二的數字以及第三大的數字連著一起看,77、58、32,ㄟ,這樣是不是等於已經將前面這三個數字由大至小排序排好了?

在 Excel 中,我們可以用 Large(N) 這個函數幫我們找出排行第 N 大的數字;所以上述的例子,我們可以用 Large(1) 找出最大的數字;Large(2) 找出排行第二的數字;Large(3) 找到第三大的數字,間接幫我們把數字由大排到小。

有 Large 就有 Small,Small(1) 會找出最小的數字 32;Small(2) 找出排行第二小的數字 58;Small(3) 找到第三小的數字 77,把這三個數字連著一起看,32、58、77,這就完成從小到大的排序。

因此利用 Large & Small 這兩個會自動更新的函數我們可以做出自動排序功能,解決 Excel 沒有提供自動排序功能的困擾。

加工原始資料

在開工之前我們得要做一些設定。為了不要動到原始資料,我們將資料移往其他的格子再繼續處理。我在 G2 這一格輸入 =E2,之後祇針對 G 這一欄做處理,這樣就不會改動到原始的資料了。

不過我們把資料移到其他地方去排序,順序亂了之後想要與原始資料對照的話可能會有點麻煩。為了方便日後與原始資料相對照,我們要在原始資料的最後方加上一個值,讓所有的資料都是唯一的,日後要尋找原始資料比較簡單。

我們可以在原始資料的最後方加上 ROW( )*0.000001 ,這樣加的值很小,不會影響排序的結果,但是加上它之後,日後要找原始資料會容易的多。

ROW( ) 函數說明

功能:傳回括號中格子位於第幾列。
用法:ROW(儲存格)
例子:ROW(E3) 會顯示 3;ROW(D44) 會顯示 44。括號中不填任何資料時,顯示目前位於第幾列。

小幫手:搞不清楚什麼是行?什麼是列嗎?喵凡告訴你

因為要加上 ROW() * 0.000001 這個特徵碼,所以 G2 這一格要輸入的是 =E2 + ROW() * 0.000001

利用 ROW( ) 函數將原始資料加上特徵碼
圖、利用 ROW( ) 函數將原始資料加上特徵碼

按下 Enter 鍵之後,G2 這一格顯示的數值……ㄟ,與原本 E2 沒有兩樣啊?!

不用擔心,那是因為我們加的數字實在是太小了,所以沒有顯示出來。

新資料看起來與原始資料完全相同
圖、新資料看起來與原始資料完全相同

雖然我們加上的微小值沒有顯示出來,不過完全不影響 Excel 的計算,所以不用管它。

如果你堅持要看到完整的資料才能安心,那麼也很簡單,祇要設定一下儲存格格式就可以讓它顯示出來了。

首先,將 G 這一整欄都標記起來,然後按右鍵,選擇『儲存格格式』,就可以設定這一整欄的顯示方式。

設定儲存格格式
圖、設定儲存格格式

在儲存格格式的設定畫面中,設定這一整欄的資料都是數值,而且要顯示出 6 位小數。

設定儲存格格式為 6 位小數
圖、設定儲存格格式為 6 位小數

按下確定鍵之後,畫面上顯示的就是完整具有 6 位小數的數值了。

儲存格格式已設定顯示 6 位小數
圖、儲存格格式已設定顯示 6 位小數

把 G2 的公式複製好之後我們要開始來排序了,先假設我們要從大排到小好了,所以這就要用到 Large( ) 功能。

Large( ) 函數:

功能:找出在某一範圍中第 N 大的數值
用法:Large(範圍, N)

假設我們要排序 G2:G9 這個範圍的資料,所以就把這個範圍填入 Large 函數中。但是如果祇寫 G2:G9,那麼公式複製到下一格時範圍會自動變成 G3:G10,這樣就錯了。為了避免 Excel 自動改變範圍,記得要加 $ 字號,變成 $G$2:$G$9,這樣複製公式時資料就不會錯了 (不懂為什麼要加 $ 字號?請看 Excel 定位:$ 字號的功用)。

解決了範圍,我們還要提供 N 值給 Large 函數。你要手動輸入 1、2、3、4 也是可以,不過我們還可以應用一下 ROW()。

我在 H2 這一格輸入的完整公式是: =Large($G$2:$G$9, ROW()-ROW($H$1))

H1 是標題,輸入 ROW(H1) 會得到 1;而我現在位在 H2 這一格,輸入 ROW()-ROW(H1) 會變成 2-1 = 1;在 H3 這一格輸入 ROW()-ROW(H1) 變成 3-1 = 2。

利用 $ 字號固定住 $H$1,再把 ROW()-ROW($H$1) 複製到底下的格子就可以依序查詢第 1 大、第 2 大……的資料了。

利用 Large & Row 函數找排名第 N 大的數值
圖、利用 Large & Row 函數找排名第 N 大的數值

輸完公式,按下 Enter 就立即找到最大值 175。

立即找到第 1 大的數值
圖、立即找到第 1 大的數值

把公式往下複製,馬上就依照數值大小依序排好。

利用 Large 函數排序數字
圖、利用 Large 函數排序數字

現在已經利用 Large 函數將 G 欄數字排好次序了,但是有個小問題,就是我怎麼知道這些數字原本在哪邊呢?比方說,現在我知道總分 175 是最高分,但是這個 175 原本是在 G 欄的哪個位置啊?

當然利用工人智慧,以肉眼一一比對還是找的到,但這太辛苦了,有沒有快一點的方法?

有,我們可以利用 Match 函數找出它原本的位置。

Match( ) 函數:

功能:找出在特定範圍中是否有我們想要的值
用法:Match(想要查詢的值, 查詢範圍, 比較的方式)

比較方式:
1:找比較小的值;
0:找完全相同的值;
-1:找比較大的值

我想找的是 H2 這一格現在的 175 到底原本排在 G2:G9 的哪邊,利用 =Match(H2, $G$2:$G$9,0) 就可以得到 6 這個答案。表示說 175 這個數字排列在 G2:G9 這個範圍的第 6 個位置。

G 欄的資料是從 E 這一欄一對一的複製過來的,所以在 G2:G9 這個範圍的第 6 個位置,那麼也會在 E 這一欄的第 6 個位置。知道這一點,我們就可以更進一步的利用 Index 函數查詢原始資料。

Index( ) 函數:

功能:找出在某一範圍中第 N 列,第 M 欄的數值
用法:Index(範圍, N 列, M 欄)

剛剛找到 175 是第 6 列的資料,所以我輸入 =Index($B$2:$E$9, 6, 1) 就會去找 B2:E9 這個範圍的第 6 列,第 1 欄的資料,得到『小健』。好,把 Match() 及 Index() 組合起來,輸入:

=Index($B$2:$E$9, Match(H2, $G$2:$G$9,0), 1) 可以得到最高分學生的名字

利用 Index() 及 Match() 查詢原始資料
圖、利用 Index() 及 Match() 查詢原始資料

按下 Enter 鍵,果然把第一名的榮耀歸給了小健。

利用 Index() 及 Match() 回推原始資料姓名
圖、利用 Index() 及 Match() 回推原始資料姓名

好,現在公式都不變,祇把最後的 1 改成 4,代表我要查詢的是 $B$2:$E$9 這個範圍的第 4 欄,也就是總分的資料

利用 Index() 及 Match() 查詢原始資料的特定欄位
圖、利用 Index() 及 Match() 查詢原始資料的特定欄位

按下 Enter 鍵,我們把第 1 名的小健的總分給查出來了。

利用 Index() 及 Match() 查詢原始資料的特定欄位
圖、利用 Index() 及 Match() 查詢原始資料的特定欄位

接著把公式複製下來就完成了。

複製公式至其他儲存格
圖、複製公式至其他儲存格

隱藏不需要的欄位

截至目前為止,我們已經可以讓 Excel 依據原始資料自動排序了,不過中間有幾個輔助欄,讓我們要看排序結果時不太方便。其實這是小問題,我們把這些輔助欄隱藏掉就是了。

假設你想要把 G & H 這兩欄隱藏起來,利用滑鼠在 G 上面按一下,滑鼠不放,拉動至 H 欄再放開滑鼠,這樣就把這兩欄都選取起來了。接著按滑鼠右鍵,選擇隱藏,這兩欄資料就暫時看不到了。

標記欄位後選擇隱藏
圖、標記欄位後選擇隱藏

執行後 G & H 兩欄就不見了。把這兩欄隱藏後,我們可以一眼就看到原始資料以及排序後的資料,方便多了。

輔助欄位被隱藏看不見了
圖、輔助欄位被隱藏看不見了

終於全部完工。從此之後,我們祇要更動了左邊的原始資料,右邊就會立即排序,可以看到即時的排序結果。

比方說小王的國文成績輸錯了,應該 99 分才對;小芬的國文、英文分別是 74、86,我們之前也都輸入錯誤。把資料更正過來後,右邊的排序資料馬上幫小王 & 小芬的排名往上提,太棒了!

改變原始資料,Excel 立即更新排序
圖、改變原始資料,Excel 立即更新排序

利用 Row()Large()Match()Index() 這四個函數,我們達成了 Excel 沒有提供的自動排序功能,讓我們可以知道一群資料間的『即時戰況』,解決了我們一些困擾。

『不不不,一點也沒有解決!這四個函數好複雜,我記不起來!每次都還要查,真不方便。』

ㄟ,如果你有這樣的困擾的話,那,我還有個稍微簡便一點點的方法啦!我下次再教你,期待一下吧!:)

參考資料:

Technorati : , , , ,

2012/08/05

使用 Excel 的排序功能為資料排序

寫這篇 Excel 的排序功能其實是為了更進一步的自動排序鋪路。

雖然不知道怎麼使用 Excel 排序的人可能很少了,但是為了確保每個人都在相同的起點,還是先寫一篇文章介紹一下。

利用 Excel 做簡單的排序

常利用 Excel 登錄資料的人大概都會有將資料排序的需求吧?比方說要底下的 8 個數字要將它們從小到大依序排好,這就是很常見需求。

Excel 原始資料
圖、Excel 原始資料

你當然可以很豪氣的說:『ㄘㄟˊ,才 8 個數字,手動排序一下就好了啊,這有什麼困難的!』

話是沒錯,不過如果今天是 80 筆、800 筆、8 萬筆資料,那光是靠手動排序可就吃不消了吧?而且一定會有錯誤。其實 Excel 本身就能排序,而且做的又快又好。既然人工處理又慢又容易出錯,那麼能夠讓電腦自動做的事就儘量不要手動。

要讓 Excel 排序很簡單,祇要選擇排序功能,再選擇要從小排到大 (A 到 Z) 或是要由大排到小 (由 Z 到 A) 即可。讓我們試試由小排到大吧。

在 Excel 中選擇圖示為漏斗的排序功能
圖、在 Excel 中選擇圖示為漏斗的排序功能

按鈕按下去的瞬間,排序已經完成。就算 8 筆資料可以用手排,但是讓 Excel 幫我們排序還是快的多。


圖、Excel 已將數字由小至大排序完成

Excel 排序的實際應用

Excel 的排序功能在學校中最常的應用大概就是排列學生成績。其實要將學生的成績排序用 Rank 會更好,不過不管,那個以後有機會再說,我們先說排序功能就好。

未排序的原始成績
圖、未排序的原始成績

學生的成績最上面一行通常是座號、姓名等資訊,這一行不要排序。剛剛選擇 A 排到 Z 或是 Z 排到 A 的排序功能沒有這功能,所以我們要選擇『自訂排序』,告訴 Excel 要把第一行忽略掉。

使用 Excel 的自訂排序功能
圖、使用 Excel 的自訂排序功能

進入自訂排序功能畫面,我們設定使用總分來做為排序依據,還有,剛剛提到的,不要把標題也排下去了。

設定標題不排序
圖、設定標題不排序

按下確定,學生就依照總分大小排序整齊嘍。

以總分排序完成
圖、以總分排序完成

設定 Excel 的第二個排序條件

不過且慢,有兩個學生總分相同,哎啊,我想要總分相同時再依照國文分數來排列耶,這樣行不行?

當然可以,祇要在自訂排序的畫面中再加設定就可以了。在自訂排序畫面中有個新增層級的按鈕,這就是你所需要的功能,按下它吧。

新增次要的排序需求
圖、新增次要的排序需求

次要的排序方式設定為依照國文分數排列,這樣子,一旦總分相同,就會再依國文來排列次序了。

設定次要排序方式
圖、設定次要排序方式

按下確定看看結果,嗯,果然同分的學生會再依國文分數來排列,符合我們的需要。

總分相同再依國文排序
圖、總分相同再依國文排序

利用 Excel 的排序功能可以很快速的幫我們把一些雜亂的數據排列整齊,方便我們理解,是一個很好用的功能。

『不過,每次都要手動去按排序也很討厭,有沒有辦法讓 Excel 自動把成績排列好啊?』

有的,利用 Excel 的一些功能函數是可以做到自動排列。不過,如果這一篇介紹的等級是幼稚園級的,自動排序可能是高中程度,所以給我一點時間想想怎麼介紹比較好。

放心,不用等很久,我圖已經抓好,也加註圖說了,祇是文章內容還要再讓我想一下。請稍候嘿!:D

Technorati : , , , ,