網頁

2016年11月22日 星期二

Excel 實作 (1)- 基本操作、格式設定、公式與函數、資料驗證

請先下載資料檔:
01王小明_段考成績.xlsx
對下載的檔案,按右鍵/重新命名,改成自己的座號和姓名。使用 Excel,檔案/開啟舊檔,打開此檔案。或者直接對此檔連續按二下,呼叫 Excel 程式來打開。

如何讓姓名欄出現(隱藏欄列)?
選B欄和 D欄,按右鍵,取消隱藏
說明: 欄或列皆可隱藏。

跨欄置中
(1)選取 儲存格 A1:M1 此範圍。
(2)按 「常用」功能索引標籤。
(3)按「跨欄置中」 鈕。


如何設定儲存格框線和顏色?
選 A18:M22 , 按右鍵/儲存格格式 ,
(1)選 「外框」
(2)樣式,請選比較粗的框線
(3)在下面點一下,加上底框線

如何設定儲存格的顏色?

有二種方法:
 選 A18:A22
一、
(1)選「常用」
(2)選 「填滿色彩」此圖示右邊的倒三角形
(3)自選一個淺的顏色
二、
選 A18:A22,按右鍵/儲存格格式,
(1)選 「填滿」
(2)選一個淺的顏色



在 L2儲存格輸入 =today() 函數,顯示電腦系統日期

編號此欄 如何以數列填滿?
有二種方式?
(1)選A4 儲存格,按 住 Ctrl 鍵不放, 將右下方的填滿控點,往下拉曳到 A16。

如果 不按 住 Ctrl鍵 , 直接將右下方的填滿控點往下拉,會怎樣?
結果儲存格的內容全部都為1。

(2)A4 輸入1,A5 輸入2,選A4:A5 , 直接將右下方的填滿控點向下拉曳到 A16,結果會如何?
ANS : 數列填滿,公差為1,變成 1,2,3,4,5,6.....

資料驗證
要設定 班級此欄祇允許輸入 資二1、資二2、資二3,怎麼做?
(1)選B4:B16,選 資料/資料驗證 /資料驗證

(2)儲存格內允許,選「清單」,來源選  右邊的 資二1到資二3 ,即 $S$4:$S$6

輸入的成績祇能為 0 ~100的整數,如何設定?
(1)選D4:H16,選 資料/資料驗證 /資料驗證,「儲存格內允許」選「 整數」,資料 介於0  ~100。
(2)錯誤訊息 
標題 :錯誤  。訊息內容 : 對不起,祇能輸入0~100的整數。按「確定」。


如何固定標題列(凍結窗格)?
當你把游標一直往下移或右移時,會看不到標題欄列,怎麼辦?
利用凍結窗格功能將工作表中的部分欄列固定起來。

選儲存格  D4, 檢視/ 凍結窗格, 選 「 凍結窗格」。以後要取消,就選 「取消凍結窗格」。
當你往下移時,D4上面的列,會被凍結,不會不見。當你往右移時,D4左邊的欄,也是被凍結,不會不見。

如何求總和(Sum)和平均(Average)?
移到 J2 此儲存格,輸入 =SUM(D2:H2),求總學分數,結果為 15。
說明 :  SUM :計算總和。
移到 I4 此儲存格,輸入 =sum(D4:H4),求總分,結果為 465。

移到 J4 此儲存格,輸入=AVERAGE(D4:H4), 求平均。
說明 :  AVERAGE :計算平均值。

如何算加權總分(SUMPRODUCT函數)?
目前的總分 要考量 學分數,加權總分是 先求  各 學科成績 * 學分  ,再全部加總。
第一位同學的加權總分 =  93*4 + 88 *4 + 98 *3 + 90 *2 + 96 *2
(1)移到 K4 此儲存格,輸入=SUMPRODUCT(D4:H4,$D$2:$H$2)
移到 上面的資料編輯列,移到公式中的D2,按 F4 鍵, 可切換絕對參照和相對參照。


SUMPRODUCT: SUM (和),Product(乘積)。先相乘,再求總和。
計算特定範圍中各相對應儲存格的乘積總和

有$表示不會隨儲存格的移動而移動,如學分此範圍,不管是那位學生,每科的學分都一樣,都是用 $D$2:$H$2 此範圍,不因學生的不同而變。所以用 絕對參照。
但下一位學生的 成績範圍應該是 D5:H5。儲存格往下移表示列加1,所以原來公式 D4:H4,其中的 列都加1,變成 D5:H5。
 加權總分的公式就變成  =SUMPRODUCT(D5:H5,$D$2:$H$2)

如何算名次(RANK函數)?
(1)選 L4, 再移到上面的資料編輯列,輸入公式=RANK(K4,$K$4:$K$16)

(2)輸入方式:
先輸入 =rank( , 按 K4 此儲存格,   再輸入半形逗號 ,再選 k4:k16 此範圍,再移到公式中的 K4 ,按 F4鍵 ,變成絕對參照 : $K$4。再移到 公式中的 K16,按 F4鍵 ,變成絕對參照 : $K$16
不管是那位學生,要算名次,都是比同樣的範圍,所以 RANK的第2個參數,要為絕對參照。

RANK:等級,排列出某數值在一串數值清單中的大小順序。第3個參數是 設定排序規則(省略或0代表遞減;其他數值代表遞增)
上面的公式,我們省略 第3個參數,表示是遞減排序,因為加權總分最高,名次就是第1名。

如何設定評等(IF 函數)?
(1) 假設條件為 : 如果平均大於等於 85,評等是 「優異」。否則再判斷,如果大於等於70, 評等 是 「中等」, 小於 70 則是「不佳」。

(2)選 L4儲存格,再移到上面的資料編輯列,輸入公式 =IF(J3>=85,"優異",IF(J3>=70,"中等","不佳"))


 IF:判斷條件是否成立,並傳回條件成立或條件不成立所對應的設定值。

選 I4:M4, 填滿控點往下拉到M16,,就可自動把每位同學的總分、平均、加權總分、名次、評等全部求出來了。


如何把 加權總分的二位小數去掉,即設為整數?
有二種方法:
(1)選K4:K16, 按右鍵/儲存格格式 /數值 ,小數位數設為0,在範例中會看到沒有小數位數 1390。

(2)也可 按 常用/減少小數位數 圖示, 二次,讓顯示的結果沒有小數。


上面這二種方式,祇是看起來沒有小數位數,並不會影響儲存格的值,也就是如果是 4.5, 看來變成5,其實儲存格的值仍為  4.5,這可以從資料編輯列看到儲存格實際的值。

Question:
A4 : = SUM(A2:A3), 5+4=9 ,怎麼會是8呢?
C2: =A2*B2,5*2 =10,怎麼會是 9呢?

ANS:
A2 儲存格的值為4.5, 按 「減少小數位數 」鈕,顯示的值為 5。但從資料編輯列看出,儲存格的值仍為4.5。


A3 儲存格的值為3.5, 按 右鍵/儲存格格式/數值,小數位數設為 0,顯示的值為 4。但從資料編輯列看出,儲存格的值仍為3.5。



所以
A4 : = SUM(A2:A3) ,4.5 +3.5 = 8。
C2: =A2*B2, 4.5 *2 = 9

就好像,用化粧品把青春痘遮起來,讓別人從外觀來看,臉上沒有青春痘。但實際上,皮膚上的青春痘並沒有消失啊!
要解決這種錯誤,應該對儲存格用 round函數,實際把儲存格的值四捨五入到整數。

如何 將 平均 < 60 分的儲存格,文字設為紅色(設定格式化的條件)?
(1)選取儲存格  J4:J16,常用/設定格式化的條件/醒目提示儲存格規則/小於


(2)輸入 : 60,顯示為 選 : 自訂格式 (若選紅色文字,結果不是鮮紅色)。
(3)選字型/色彩,選 紅色,按「確定」。

如何讓資料列的網底顏色一深一淺,也就是帶狀列(MOD函數的應用)?
(1)選取儲存格  A4:M16,常用/設定格式化的條件/新增規則
選 「使用公式來決定要格式化哪些儲存格」,再 輸入如下公式: =MOD(ROW( ),2)=1
說明 : ROW() 是目前的列數。  MOD是求餘數 ,如 MOD(4,2)=0, 4除以2餘數為0。
MOD(ROW( ),2)=1 ,列編號除以2的餘數等於1,也就是 奇數列。
將奇數列的網底,設為 淺紫 色,偶數列不變色。
選 「格式」鈕

(2)切換到「 填滿」標籤 ,自己選一個比較淡的顏色,如 淺紫色,按「確定」鈕。

如何以「資料橫條」來表示 加權總分 ?
(1)選取儲存格  K4:K16,常用/設定格式化的條件/資料橫條


如何將平均,四捨五入到個位數(即取整數)(ROUND函數)?
(1)選取儲存格  J4, 原來的公式 =AVERAGE(D4:H4),前面加上  ROUND函數,變成 =ROUND(AVERAGE(D4:H4), 0)
說明 :
ROUND :大概的。將數值進位(或捨去)至指定的位數,方法是用四捨五入。 ROUND(4.51,0) , 對 4.51四捨五入到小數位數0位,變成整數。結果為 5。ROUND(4.51,1), 對 4.51四捨五入到小數位數1位,結果為 4.5。

選取儲存格  J4,將填滿控點,往下拉到 J16。每一位學生的平均都四捨五入到整數。
但你會發現到,平均還是有二位小數,如 93.00,這是怎麼一回事?

選取儲存格  J4, 按右鍵 / 儲存格格式 /數值 ,小數位數:2。 雖然儲存格的值沒有小數位,但顯示出來的格式有小 數位數2位,所以會多補二個0。

如何不要顯示小數位呢?
參考前面 加權總分 的做法,對儲存格,按右鍵 / 儲存格格式 /數值,將小數位數改成0 。或者直接按 減少小數位數 圖示 二次。

選取儲存格  J4,將填滿控點,往下拉到 J16。每一位學生的平均都變成整數,沒有小數。
填滿控點除了可以複製公式,也可複製格式。

如何移除資料驗證?
假設編號8此位同學缺考
選取儲存格  D11, 輸入缺考,會發現有錯誤訊息,因為前面有設定資料驗證,要先拿掉.。
選取儲存格  D11:H11, 選 資料/資料驗證 /資料驗證,,設定 ,按「全部清除」鈕。

選取儲存格  D11, 輸入 : 缺考
選取儲存格  D11,將右下方的填滿控點,拉曳到 H11。  D11:H11,全部變成缺考。但J11(平均)、M11(評等)出現  #DIV/0! 的錯誤訊息。

如何使用自動加總鈕()?
(1)選取儲存格  D4:D17
(2)選 「常用」
(3)按自動加總鈕旁的倒三角形,選「平均值」。

選D17,按「減少小數位數」圖示鈕三次,變成整數。
選D17,按填滿控點,拉曳到 H17。

如何求應考人數(COUNTA函數)?
選取儲存格  D18,先輸入 =COUNTA( , 移到上面資料編輯列,再選 取 D4:D16此範圍,再 輸入  ),按 ENTER 鍵。
結果為 13,應到人數 是 連缺考人數也要算進去。
註 : 輸入公式,請在資料編輯列輸入比較方便。


說明:  COUNTA :計算 非空白的儲存格個數。COUNT:計數,A:All(全部)。

如何求到考人數(COUNT函數)?
選取儲存格  D19,輸入  =COUNT(D4:D16),按 ENTER 鍵,結果為 12。
說明:  COUNT : 含有數值的儲存格個數。

Question :如果打 '10(儲存格 變成 文字資料的10 )或者輸入1/3(日期 : 1月3日),count 有算進去嗎?

如何求不及格人數(COUNTIF函數)?
選取儲存格  D20,輸入  =COUNTIF(D4:D16, "<60"),按 ENTER 鍵。結果為 1。
D4:D16此範圍內,如果儲存格的值小於 60就算其個數。
註:  <60 前後若沒加 "" ,會有錯誤。如果寫成    =COUNTIF(D4:D16, 60),分數等於60分才要計算個數。

說明:   COUNTIF : 符合條件式的儲存格個數,不一定是數值性的儲存格。IF :如果,判斷的意思。
COUNTIF(G3:G7,"是")  : G3:G7 儲存格為 是 有幾個 。
criteria :標準

如何求最大值(MAX函數) ?
選取儲存格  D21,輸入  =MAX(D4:D16),按 ENTER 鍵。
說明: MAX : 最大值。 Maximum。

如何求最小值(Min函數)?
選取儲存格  D22,輸入  =Min(D4:D16),按 ENTER 鍵。
說明: MIN : 最小值 。Minimum。

選取 D18:D22, 將填滿控點 向右拉曳到  H22,以複製公式內容,讓每科公式都有了。

如何算資二1有多少人?
選取儲存格  D23,輸入  =COUNTIF(B4:B16,"資二1"),按 ENTER 鍵。
註: 如果寫成  =COUNTIF(B4:B16,資二1),結果為0,所以字串前後要加雙引號

如何算名字內含有「李」的人數?
選取儲存格  D24,輸入 =COUNTIF(C4:C16,"*李*"),按 ENTER 鍵。
說明:
*、? 萬用字元的用法:

* : 字串,有或沒有都可以。"*李*" 前面允許任何字或沒字,後面允許任何字或沒字,中間有 李,都算符合條件,要計算個數。

問題: 如果寫成 =COUNTIF(C4:C16,"?李*"),答案為何?
? : 表示一個字元。

如何求資二2全班國文的總分(SUMIF函數)?
先選取儲存格  I23,
(1)輸入 =SUMIF(
(2) 再選 B4:B16此範圍
(3)輸入半形 逗號
(4)再輸入 "資二2"
(5)輸入半形 逗號
(6)再選 D4:D16此範圍
(7) 再輸入
(8)按 Enter鍵

說明:
SUMIF: 將符合條件的儲存格進行加總。如上圖 在儲存格 I23 輸入上述的公式,設定當儲存格 B4:B16 的值為 "資二2"  時,加總其國文成績。

Question:
1. =sumif(D4:D16,"<=65") , 答案是多少?
2. =sumif(D4:D16,80) , 答案是多少?


VLOOKUP函數
切換至「個人成績查詢」工作表。
選取儲存格H3, 按插入函數    fx   鈕 ,以開啟「插入函數」交談窗。


按「或選取類別」下拉式方塊,選「檢視與參照」,在選取函數列示窗中點選「VLOOKUP」函數,按確定鈕,開啟「函數引數」交談窗。

在Lookup_value 欄(要搜尋的值)。輸入 "E3",按F4鍵,將儲存格位址轉換成絕對參照位址。從搜尋範圍內 找 E3儲存格的值。

在Table_array 欄(搜尋的範圍)。按此欄右邊的 折疊鈕 ,以選取整份成績單的內容。
切換至「成績總表」工作表,選取儲存格A3~M16,按F4鍵,將儲存格位址轉換成絕對參照位址。再按折疊鈕 ,返回函數引數交談窗
在Col_index_num 欄,輸入3,如果找到儲存格E3 的值( 即編號:3) 後, 傳回與該值同
一列第3欄的值(即編號為3同學的姓名) , 按確定鈕。你會看到下面的計算結果 為 張育酩



VLOOKUP : Vertical (垂直的)。LOOKUP(查找)。垂直(由上往下)搜尋。
是一種具有對照查詢功能的函數。=VLOOKUP(A30,$A$4:$F$27,6,FALSE),
Excel便會在指定範圍($A$4:$F$27)最左欄中由上往下尋找與儲存格A30資料完全相符的儲存格,並傳回與該儲存格同一列中第6欄的值。
第4個引數的意思 :省略或TRUE代表尋找最相近的值(必須先將搜尋範圍進行遞增排序);FALSE代表找尋完全相符的值。

HLOOKUP函數 與VLOOKUP函數的使用功能相近。H: horizontal( KK[͵hɑrəˋzɑnt!] ), 水平的,橫的。
差異在於HLOOKUP是在指定範圍第一列(由左往右)中找尋特定值,傳回與特定值同一欄第N列儲存格的值。


Question:  如下圖,公式為 : =VLOOKUP(D5,C1:E3,2,FALSE),結果 為何 是 #N/A?
N/A : Non  available。  不能獲得的。
available:    adj. 1.有用的,可利用的。 2.可以得到的,可以買到的

切換至「個人成績查詢」工作表。
選取儲存格H3, 按右鍵 /複製  ,或按  Ctrl+ C。
選取儲存格B5~ I5, 按住 Ctrl 鍵不放,再選取儲存格H6, 以選取多個不連續的儲存格。

在選取的範圍按右鍵, 按『選擇性貼上/ 公式』選項,在儲存格B5~I5及H6貼上儲存格H3的函數。

選取儲存格B5 , 在資料編輯列中,將引數 "3",更改為 "4",按 Enter 鍵,以傳回編號為3學生的國文成績。

參考上面步驟,將儲存格C5~H5中的引數 "3",分別更改為5~10;再將儲存格I5中的引數 "3" , 更改為"12"。再將儲存格H6中的引數 "3" , 更改為"13"。
最後結果如下:

存檔,檔名為:  01王小明_段考成績.xlsx,請務必用自己的座號和姓名。上傳到老師的classroom。

沒有留言 :

張貼留言