EXCEL基本操作方法
- 啟動:
1.開啟 Microsoft office 群組。
2.雙按
圖示。
- 輸入資料方式:
- 基本組成:儲存格(cell)→工作表(Worksheet,如同表格)→活頁簿(立體工作表=16
Sheets)
- 儲存格(cell)位址:如B12為第2行(欄)第12之儲存格位址工作表=255(行:A..IV)*16384(列)
- 儲存格可存放之資料型式:
型式 |
例如 |
內設對齊方式 |
文字(無法計算) |
′127
′cat |
靠左 |
數字 |
127 |
靠右 |
公式 |
=(B2+C2)/2
=sum(B2:C2) |
顯示結果
靠右 |
- 操作:
A .
移動(空心)十字游標至欲輸入資料之儲存格,按滑鼠左鍵一次
B .
輸入資料可用鍵盤,滑鼠將游標定位,以進行插入;編修時可用Del
鍵及Backspace 鍵。
C . 輸入完成:按Enter
或移動游標至其他儲存格。
- 修改:
(1)在欲修改之儲存格上雙按或按功能鍵
F2。
(2)與輸入雷同。
- 刪除某一或某段存格之內容:
(一)設定範圍:
- 單一儲存格:Click欲刪除之儲存格。
- 連續儲存格:二種方法。
(1)在起始儲存格Click,最末儲存格上同時按
Shift+Click滑鼠。
(2)在起始儲存格Click,並拖曳(Drag)至最後之儲存格,鬆開滑鼠。
- 不連續儲存格:
在第一儲存格上按Click。
其他儲存格上同時按Ctrl及滑鼠。
(二)進行刪除:
- 刪除後其他儲存格會上移或左移:
選「編輯」→「刪除」→
![](ex-2.jpg)
(四選一的Option Button)→按確定。
- 搬移儲存格內容:二種方式
(1).拖曳方式:
- 設定欲搬移之儲存格範圍。
- 移動滑鼠至反白區邊框,在呈現左上箭頭之游標時,拖曳滑鼠至目的地。
(2).運用
:
- 設定欲搬移之儲存格範圍。
- 點選
圖示
- 標移至目的地,按Enter。
- 插入空白儲存格:
(1).游標移至起點後,Click一次。
(2).選「插入」選單:再選
- 「儲存格」:
![](ex-4.jpg)
- 「列」:(下面儲存格下移)
- 「欄」:(左邊儲存格右移)
- 複製:
(1).運用剪貼簿:
- 設定來源區域為範圍。
- 將來源資料放入剪貼簿中:「編輯」→「複製」
- 游標移至目的地,設定目的地範圍(不設範圍,只複製一份)
- 將剪貼簿內容貼到目的地:「編輯」→「貼上去」將游標移至目的地,設目的地的範圍。
(2)運用
圖示
- 設來源範圍。
- 在工具列之
圖示上Click一次(選取「複製」功能)。
- 選取目的地範圍(會出現虛框)。
- 按Enter確定,完成複製。
成績處理實例
例一:成績處理
操作步驟:
- 輸入標題:如A1至I11。
- 調整各欄之寬位:
(1).指定方式
- 將A1:I11設定為處理範圍。
- 「格式」→「欄」→「欄寬」→
![](ex-7.jpg)
(2).位別欄寬:在二欄位名稱間(出現雙向游標)調整。
- 將標題「置中」對齊,加「粗體」及「斜體」:
A.將標題(A1:I11)設定為處理範圍。
B.在
圖示上Click一次(「置中」)。
C.在
圖示上Click一次(「粗體」)。
D.在
圖示上Click一次(「斜體」)。
- 填入學號:
A.在A2儲存格上輸入823001(起始號碼)。
B.將A2至A11設定為處理範圍(含起始號碼之儲存格)。
C.點選「編輯」→「填滿」→「數列」→出現交談窗,直接按Enter或在『確定』鈕上Click。
- 輸入成績:(分數>=40)
- 輸入時切勿先按Space,再輸入數字(會被視為文字)
- 計算個人總分:三種方式,以F2儲存格為例
- 游標移至F2,Click一次(定位),(下列方式任選一種即可):
A.直接輸入=C2+D2+E2或=SUM(C2:E2)
B.點選
圖示(加總),設定虛框範圍(加總範圍)為C2:E2,接著按Enter。
C.(1)選「插入」→「函數」或
圖示上Click一次
(2)在左邊串列盒選「全部」→在右邊串列盒選「SUM」函數→出現交談窗,在<下一步>鈕Click→在Number1輸入盒中Click且輸入C2:E2,或用滑鼠選取C2:E2範圍→在<完成>鈕上Click一次或直接按
Enter。
![](ex-13.jpg)
![](ex-14.jpg)
- 將F2公式複製給其他人(F3:F11)
- 在F2儲存格上Click(設來源範圍)
- 在工具列之
圖示上Click一次(選取「複製」功能)
- 選取F3:F11為目的地範圍(會出現虛框)
- 按Enter確定,完成複製。(觀察F3:F11公式如何調整?以相對位址方式:F3=SUM(C3:E3)
F4=SUM(C4:E4)
欄位向下(或向上複製時,)Excel主動調整列位址,(C2→C3,E2→E3,….)
- 計算個人平均、名次、平均之等第
(一).平均:三種方式,以G2為例,在G2儲存格輸入下列公式:
(1)=F2/3
(2)=(C2+D2+E2)/3
(3)=AVERAGE(C2:E2),可直接輸入或依下列步驟操作:
- (A)「插入」→「函數」,或在圖示上Click一次。
(B)出現交談窗,點選「常用」函數→在「AVERAGE」上Click→<下一步>鈕上Click
→在Number1輸入盒上Click ,再輸入(C2:E2),或圈選C2:E2
→在<完成>鈕上Click或按Enter。
(二)名次(F2為例)
(A).H2=RANK(G2,G2:G11)
RANK函數可求G2(學號81001之平均)內容在G2:G11(全部學生之平均)範圍之次序值(即名次)
(B)將相對位址G2:G11改為絕對位址$G$2:$G$11:
![](ex-15.jpg)
![](ex-16.jpg)
- 在H2儲存格Click
- 將游標移至函號參數G2前,按功能鍵F4
- 同樣移至冒號後,按功能鍵F4
- 將G2:G11改為絕對位址,以使往後(在複製時,Excel對於絕對位址不會做任何更改)複製之結果完全正確。
(三)平均之等笫
- (A).分數(0~100)劃分為0至6等級,6等第:
G2分數範圍
|
Y=(G2-40)/10
|
INT(Y)
|
對應等第
|
100 |
6.X
|
6
|
優
|
99-90
|
5.X
|
5
|
優
|
89-80
|
4.X
|
4
|
甲
|
79-70
|
3.X
|
3
|
乙
|
69-60
|
2.X
|
2
|
丙
|
59-50
|
1.X
|
1
|
丁
|
40以下
|
0.X
|
0
|
戊
|
(INT函數可取Y之整數值)
(B)在I2輸入公式
- 若分數皆大於49分,則在I2儲存格輸入公式:
=CHOOSE(int(G2-40)/10),”丁”,”丙”,”乙”,”甲”,”優”,”優”)
- CHOOSE函數先計算Y值並根據Y值(整數),以選擇取(CHOOSE)對應值,若Y=1,則I2得
”丁”;Y=2,I2得”丙”;若Y=5或6,I2得”優”。
- 若分數可能<=49,則在I2儲存格輸入公式:
=if(G2<=49,”戊”,CHOOSE(int((G2-40)/10),”丁”,”丙”,”乙”,”甲”,”優”,”優”))
如果G2<=49,則I2得”戊”,否則利用上述CHOOSE函數求等第。
- 將G2:I2公式拷貝給G3:I11:
A.將G2:I2設定為來源範圍。
B.在工具列
圖示上Click一次。
C.選取G3:I11設定為目的地範圍。
D.按Enter確定。
- 計算國文科全班平均數、標準差、最高分、最低分:
儲存格 |
公式 |
用途 |
C13 |
=AVERAGE(C2:C11) |
平均數 |
C14 |
STDEV(C2:C11) |
標準差 |
C15 |
MAX(C2:C11) |
最高分 |
C16 |
MIN(C2:C11) |
最低分 |
- 將C13:C16公式複製至D13:G16範圍。
- 將工作表存檔:點選工具列
圖示,修改檔名後按確定。
- 將工作表印於印表機:1.設定儲存格範圍
2.點選工具列
圖示
成績總表如下:
例二:各科目成績分佈情形
(依據例一之工作表分析各科目分數分佈人數統計)
製作次數分配,需要注意如下事項:
- 使用 FREQUENCY() 函數,其格式如下。FREQUENCY (來源參考資料範圍,區間參考資料範圍)。
- 來源參考資料範圍:欲進行次數分配的來源資料範圍。
- 區間參考資料範圍:把來源資料分成若干區間的分界點。
- 傳回值為一陣列,內容為區間次數分配。須設定輸出範圍以放置處
理完成之次數分佈陣列值。
國文科成績分佈處理之操作步驟:
- 將儲存格游標移至 j2 位址。
- 依序往下鍵入 100, 99, 89, 79, 69,59 如下圖:
- 將儲存格游標移至 L2 位址
- 點選工具列上的「函數精靈」圖示
- 於「函數類別」方塊內點選「統計」項目
- 於「函數名稱」方塊內點選「FREQUENCY」,再按<下一步>
- 於 data-array 文字對話方塊內鍵入 c2:c11 (或用滑鼠設定範圍)
。(即設定來源資料範圍)
- 於 bins-array 文字對話方塊內鍵入 j2:j7 (即設定區間範圍)
- 按完成,此時叫公式為 Frequency (c2:c11, j2:j7)
- 以滑鼠拖曳將 L2:L7 設定為輸出範圍 (輸出次數分佈範圍):此步驟
最容易被忽略。
- 游標移到 L2 按 F2 進行編修 (只按 F2
鍵即可,不必為公 式加上 {}}: 目的將
=FREQUENCY (E4:E13, L4:L8) (一般公式),轉換為
{=FREQUENCY (E4:E13, L4:L8)} (陣列公式)
- 按Ctrl+Shift+ENTER (轉換一般公式為陣列公式)
同時輸出範圍亦出現各成績範圍之公佈次數
(註:1.陣列這名詞對於一般初學者而言是個抽象的名詞,您可把它
視為一組連續相同性質的資料集合。
2.步驟 10∼12
是顯示陣列內資料的技巧所在,請務必熟悉
3. 修正 O4 公式時,須重做 10 至 12 之步驟。)
習作:請完成其它科目之成績分佈?
例三: 依總分排序之處理
排序步驟:
- 將A1:I11設定為處理範圍
- 點選「資料」-->「排序」-->
- 「確定」CLICK一下
製作巨集鈕
1.「工具」-->「錄寫巨集」-->「錄寫新巨集」-->巨集名稱:-->「確定」CLICK一下
2.依排序步驟操作
3.「工具」-->「錄寫巨集」-->「停止錄寫」
4.點選繪圖工具箱之圖示
5.拖曳滑鼠以定按鈕大小-->出現「指定巨集」視窗,選「巨集1」
6.修改按鈕名稱:1. 2.在此鈕上CLICK,將『按鈕1』改為『排序』,按ENTER(如下圖)
刪除巨集鈕
1.點選繪圖工具箱之圖示(選取物件)
2.拖曳滑鼠以框選按鈕
3.按DEL鍵
例四、簡易薪資處理
檢視與參照函數之應用
一、VLOOKUP
語法:VLOOKUP(欲搜尋之特定值,資料陣列,欄位索引值)
功能:在陣列的最左欄搜尋使用者指定的特定值,接著根據此值所在橫列,傳回陣欄位之內容
說明:
- 搜尋特定值:此引數可為數字、字串或位址,它是欲在陣列搜尋之指定內容,EXCEL會依此內容在最左欄中進行搜尋。在下例之C2公式之搜尋值為位址型態,實質內容為「職等」。
- 資料陣列:此引數可為範圍名稱或位址範圍,
它是由多個欄位所組成的陣列,相當於一個對照表,而陣列的最左欄位(查詢索引欄)相當於查詢之索引關鍵值,最左欄內容可以是文字或數值但務必依遞增順序排列。如下例中陣列為J2:L11即薪資等級對照表。
- 欄位索引值:此引數指定欄位之索引值(第幾欄),VLOOKUP將傳回陣列(對照表
)該欄位內容(即欲取得資料之欄位編號);
如下例中C2公式中欄位索引值為2,即欲取得薪資對照表中「本薪」;C3公式中欄位索引值為3,即欲取得薪資對照表之「加給」,由於該員之職等為6,故VLOOKUP以等級6進行搜尋並傳回該員本薪為35500,加給為7200,您可試著將該員之等級改為其他,看本薪及加給是否產生變化。
[工作表二]利用垂直對照表進行薪資處理
(一)薪資總表
(二)薪資對照表(資料陣列)
&127 欄位索引值=1 欄位索引值=2 欄位索引值=3
(三)說明:
- 使用公式: 以C2:G2五個儲存格為例
位址 |
公式 |
用途 |
C2 |
=VLOOKUP($B2,$J$2:$L$11,2) |
求「本薪」 |
D2 |
=VLOOKUP($B2,$J$2:$L$11,3) |
求「加給」 |
E2 |
=C2+D2 |
求「薪資總額」 |
F2 |
=F2(E2>=4000C,E2*0.15,E2*0.1) |
求「所得稅」 |
G2 |
=E2-F2 |
求「實領金額」 |
其中資料陣列以絕對位址表示之,如此可便於拷貝至下面各筆紀錄
- 應用: 在每一年度更動時,只要更改薪資對照表及個人等級即能進行薪資處理
二、HLOOKUP
語法:HLOOKUP(欲搜尋之特定值,資料陣列,欄位索引值)
功能:在陣列的最上面資料列搜尋使用者指定的特定值,接著傳回特定資料列之內容
說明:本函數之引數與VLOOKUP(垂直對照表查詢)相似,資料陣列之最上方資料列為查詢索引欄,它的內容須依由右而左遞增方式排列。
以下例說明:
搜尋特定值:在C21公式中搜尋特定值為位址B21之內容(即職等)
ゝ
資料陣列:在例中為K20:T22,即薪資等級對照表 ゞ
欄位索引值:即欲取得資料之欄位編號。在C21公式中資料列索引值為2,故
可利用HLOOPUP取得薪資對照表中「本薪」;在C23公式中資料列索引值為3,故可取得薪資對照表中「加給」。
[工作表三]:利用水平對照表進行薪資處理
- 薪資總表:同上例,但放在A30:G30儲存格
- 薪資等級對照表(資料陣列)
資料列索引值=1
資料列索引值=2
資料列索引值=3
- (三)說明:以C21:G21五儲存格公式為例:
位址 |
公 式 |
用 途 |
C21 |
=HLOOKUP($B21,$K$2:$T$22,2) |
求「本薪」 |
D21 |
=HLOOKUP($B21,$K$2:$T$22,3) |
求「加給」 |
E21 |
=C21+D21 |
求「薪資總額」 |
F21 |
=F2(E21>=4000C,E21*0.15,E21*0.1) |
求「所得稅」 |
G21 |
=E21-F21 |
求「實領金額」 |