iT邦幫忙

2021 iThome 鐵人賽

DAY 28
0
自我挑戰組

大學生必知的30個Excel技巧系列 第 28

Day-28 輕鬆使用Excel生成亂數並排名

今日練習檔 ԅ( ¯་། ¯ԅ)

今日的內容是要帶大家認識如何使用Excel判斷最大值和最小值,以及利用Excel來排名

首先最大值跟最小值是一組的,當然可以使用Day-8所教的排序功能,就可以很清楚的知道最大級最小值,但那樣就會弄亂你原有的資料。因此我們可以使用公式來判斷最大值及最小值,而它的公式也相當直觀,最大值就是MAX()最小值就是MIN()。接下來可以搭配今日練習檔來練習 ヽ(^Д^)ノ

今天的練習檔是我們昨天使用的成績單,在第13和14列要計算各科的最大和最低分,當然你可以用看的就看得出答案,但如果資料有100筆我就不信你還要用看的 XD

首先先點選B13,輸入「=MAX(」,並點選「插入函數」:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667EZ4a5GTkoR.png
這個公式也是相當的簡單,可以看到公式建立器中有兩格,但第二格是可以透過減號來刪掉的,因此我們可以只需要一格即可,當然你也可以透過下方的加號來新增更多的格子,而這些格子其實是讓你來新增進你要判斷的位置的,因此我們在這邊就可以直接選取 B2:B11:
https://ithelp.ithome.com.tw/upload/images/20210907/201276678MELMlLWxU.png
可以在建立器的下方看到結果確實為98,接著你可以點選下方的控點往右拖曳,就可以自動填滿後面的科目,Excel會自動更改你的範圍(詳情可以參考第20、21天的內容):
https://ithelp.ithome.com.tw/upload/images/20210907/20127667yNMjI5D2Cr.png
接著是下方的最低分,要判斷最小值是使用MIN函數,使用方法跟MAX一模一樣,因此我就不示範,歡迎大家自己嘗試,下方我也附上完成的結果,可以在下圖中的公式欄中看到公式:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667emKvKICeBF.png
可以看到最低分為1分,讓我想到這些數字其實不是我自己打的,而是我使用亂數公式自動套上的,不過由於亂數的使用時機比較少,因此我沒有打算要特別介紹,如果想要知道的捧油,我會將使用方式放在最後面唷。

接下來進到排名的環節,要將資料排名就是使用RANK函式,使用這個函式可以在不影響資料順序的狀況下,知道每筆資料的名次。首先一樣先設定第一格(F2),輸入「=RANK(」並點選插入函數:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667E0fDq9tWyb.png
這裡分成三格,第一格(Number)要輸入你要判斷的那「一」筆資料,因為我們目前這格式F2,對應到的總分是E2,因此這格就是要針對E2來去做排名:
https://ithelp.ithome.com.tw/upload/images/20210907/201276678yIx4i0SXA.png
第二格(Ref)是要做比較的全部資料,這個就很明顯是E2:E11:
https://ithelp.ithome.com.tw/upload/images/20210907/201276673Fr24tFAKU.png
最後一格(Order)要特別注意一下,這邊分成0和1。0是由大到小排1是由小到大排。預設為0,但我建議還是習慣給這個值會比較好。因此由於我們這邊是要用成績來排,成績是越高越好,因此就輸入0,並按下確認。
https://ithelp.ithome.com.tw/upload/images/20210907/201276673yDYpEeeYy.png
接著我們依照慣例使用控點往下拉,你會發現這個情況:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667lR6tapB3L2.png
你會發現排名幾乎都是錯的,甚至還有同名的,原因就是因為「位址」的問題,要來釐清這個問題,我們可以檢查F3的公式:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667WMwH3nrQ6G.png
在第一個位置判斷的是E3,沒錯,但是在範圍的部分竟然錯了,Excel自動往下了一格,變成了E3:E12,下方其他的排名也是如此,才會是錯的~

要改的話首先要先思考排名同樣在F欄,而總分同樣在E欄,只有列的數字有變,因此我們使用「$」來將列的數字鎖起來(這個概念很重要,我有在Day22Day23詳細的說明,歡迎不熟悉的朋友們可以去看一下)。要注意的是,要改公式的話記得回到第一格去改喔,在此範例中第一格為F2:
https://ithelp.ithome.com.tw/upload/images/20210907/201276671hDGqk7JWD.png
將列鎖起來後,雖然結果還是7,但自動填滿後結果就對了唷!當然你也可以將欄也鎖起來,也就是「$E$2:$E$11」,對結果並不會有影響:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667vnC4sQkcBK.png

加碼時間:

最後我針對亂數功能進行介紹,會放在最後是因為這個功能比較少用到。

使用方法很簡單,如果你是Excel 2007以上可以使用RANDBETWEEN(上限,下限),我就是使用這個方法來建立這個成績單的「=RANDWETWEEN(100,1)」。

那如果你是Excel 2003以下,那時還沒有這個函式,因此你必須使用int((數字上限 - 數字下限 + 1) * RAND() + 數字下限),這個其實有很多細節,包含RAND()是隨機生成0~1之間的數字,如0.6852121等,因此最後要在使用INT()這個函式將這個數變成整數。大家可以試試看,如果依照我的這個範例就必須要輸入,「=int(100 * RAND() + 1)」(最後面的1如果不打的話可能會有0分喔)。

軒軒的笑話時間:
小美走在路上遇到一隻兇殘的大烏龜把她攔了下來,
小美驚恐的說:
「你要劫財還是劫色我都給你,拜託饒我一命!!」
結果你知道那隻烏龜說了什麼嗎?




它說:「傑尼傑尼」
/images/emoticon/emoticon27.gif


上一篇
Day-27 Excel的count家族
下一篇
Day-29 快速學習Excel時間函式
系列文
大學生必知的30個Excel技巧30

尚未有邦友留言

立即登入留言