電子表格制作(Excel 2002)
一.參考資料
二.基本概念
三.單元格數(shù)據(jù)的輸入和編輯
四.使用圖表表達(dá)數(shù)據(jù)信息
五.美化工作表
六.使用公式和函數(shù)計算數(shù)據(jù)
七.管理數(shù)據(jù)信息
一.參考資料
1)參考網(wǎng)站: http://www.excelhome.net/ http://www.etcell.com/
2)Office Excel自帶的幫助文檔
3)參考書目:信息化辦公――電子表格(人民郵電出版社)
4)Office Excel自帶的兩個電子表格文件:SAMPLES.XLS和SOLVSAMP.XLS(在Office安裝目錄下的Samples目錄里)
二.基本概念
1)工作簿:一個工作簿就是一個電子表格文件,文件后綴是".xls", 一個工作簿可以由若干個工作表組成.系統(tǒng)默認(rèn)新建的工作簿會有3個工作表,分別為Sheet1,Sheet2和Sheet3.
2)工作表:由眾多的單元格組成
3)單元格:是工作表中的小方格,是工作表的基本單元.單元格的位置由列號和行號組合表示,如"A10"表示位于第A列第10行的單元格.單元格全部內(nèi)容是指單元格中的數(shù)據(jù)內(nèi)容,格式和批注等在內(nèi)的所有內(nèi)容.
4)單元格區(qū)域:是一組被選中的相鄰或分離的單元格.
三.單元格數(shù)據(jù)的輸入和編輯
有兩種方法:1)在單元格中輸入和修改.2)在編輯欄中輸入和修改.
1.單元格中可以使用的數(shù)字類型
常規(guī):不包含任何特定的數(shù)字格式
數(shù)值:有一個可供選擇的千位分隔符和小數(shù)位數(shù)
貨幣:有可供選擇的小數(shù)位數(shù)和貨幣符號
會計專用:可使一列數(shù)值符號和小數(shù)點對齊
日期:具有多種的顯示方式
時間:具有多種的時間類型
百分比:以百分?jǐn)?shù)的形式顯示,小數(shù)數(shù)位可選
分?jǐn)?shù):有9種顯示方式
科學(xué)計數(shù):以科學(xué)計數(shù)的形式顯示
文本:以文本的形式顯示數(shù)字.在輸入電話號碼,郵政編碼等數(shù)字時,如果希望Excel把輸入的數(shù)字看作是文本,就應(yīng)設(shè)為該格式.
特殊:如郵政編碼等
自定義:用戶可以建立自己的數(shù)據(jù)類型
對[格式]/[單元格]/[數(shù)字]命令的說明:該命令的適用對象是數(shù)字,如果單元格中輸入的不是數(shù)字內(nèi)容,如輸入"姓名","用了30分鐘",就不需要使用該命令設(shè)置格式.
2.選擇單元格
選取單元格:單擊該單元格.
編輯單元格:雙擊該單元格.
選取整行(列):單擊行號或列標(biāo).
選取整個工作表:單擊"全選"按鈕.
選取連續(xù)區(qū)域:拖動鼠標(biāo);或單擊起始單元格后按住Shift單擊結(jié)束單元格.
選取不連續(xù)的區(qū)域:按住Ctrl鍵,然后單擊所要選取的單元格即可.
3.
輸入文本時換行的方法:ALT鍵+ENTER鍵.
要想輸入分?jǐn)?shù),可先輸入小數(shù),然后再應(yīng)用分?jǐn)?shù)格式,也可以在分?jǐn)?shù)前加一個零和空格.
默認(rèn)情況下,單元格中的文本是右對齊,數(shù)字是左對齊.
單元格中過長的文本,會覆蓋右邊相鄰的空白單元格,若相鄰的單元格有內(nèi)容,則過長的文本將被擋住而不能顯示,選定該單元格,在編輯欄中可看到該單元格的全部內(nèi)容.
插入多行的方法:選定與需要插入的新行下方相鄰的若干行,選定的行數(shù)應(yīng)與要插入的行數(shù)相等,在選定的單元格區(qū)域上單擊鼠標(biāo)右鍵,在彈出的命令中選擇"插入".
清除單元格內(nèi)容的快捷方法:在選定的單元格或單元格區(qū)域上單擊鼠標(biāo)右鍵,在彈出的命令中選擇"清除內(nèi)容".注意,它與"刪除"命令的區(qū)別.
菜單欄中[編輯]/[清除]命令的用法.
菜單欄中[編輯]/[刪除]命令,[插入]/[單元格]命令的用法.
要善于利用鼠標(biāo)右鍵,在選定對象上單擊鼠標(biāo)右鍵,會彈出與選定對象有關(guān)的快捷菜單.例如,對著某行號單擊右鍵,對著某列號單擊右鍵,對著某單元格單擊右鍵,對著工作表名單擊右鍵等等.
如何移動單元格 如何復(fù)制單元格 如何復(fù)制單元格的數(shù)據(jù)內(nèi)容
4.數(shù)據(jù)的自動填充
如何在相鄰的7個單元格中輸入"星期一","星期二",… ,"星期日"
圖1 按預(yù)設(shè)序列自動填充效果
圖2 初值選兩個單元格時的自動填充
1)填充相同數(shù)據(jù)或數(shù)據(jù)序列的步驟:
a.選中已經(jīng)輸入數(shù)據(jù)的單元格.
b.在當(dāng)前單元格黑框的右下角有一個小黑方塊,稱為填充柄,將鼠標(biāo)指針移到填充柄上,這時鼠標(biāo)指針變?yōu)楹谑中螤?
c.按住鼠標(biāo)左鍵并拖動.
2)自定義數(shù)據(jù)序列的步驟:
a.單擊[工具]/[選項]/[自定義序列].
b.在"自定義序列"列表框中選擇"新序列"選項.
c.在"輸入序列"文本區(qū)中輸入要自定義的數(shù)據(jù)序列,如"春","夏","秋","冬",每個數(shù)據(jù)要單獨在一行.
d.單擊"添加","確定"按鈕.
5.如何用下拉列表輸入數(shù)據(jù)
人事部經(jīng)常要在Excel表格里錄入部門,性別,城市等數(shù)據(jù),這些數(shù)據(jù)重復(fù)性很高,比如,在【部門】這一列,可能需要多次輸入【財務(wù)部】.如下圖2所示,用下拉列表輸入這些數(shù)據(jù)會方便很多,還不容易出錯.
方法:單擊[數(shù)據(jù)]/[有效性],再按下面右圖所示進(jìn)行設(shè)置即可.
6.給單元格加批注.
四.使用圖表表達(dá)數(shù)據(jù)信息
1.繪制圖形
Excel 2002的"繪圖"工具欄提供了幾十種常見的基本形狀圖形,可以根據(jù)需要來繪制這些圖形.繪圖方法與Word中的相似.
2.創(chuàng)建圖表
圖表就是將電子表格中的數(shù)據(jù)以圖形方式顯示出來,并與生成它們的工作表數(shù)據(jù)相鏈接.當(dāng)修改工作表數(shù)據(jù)時,圖表也會被更新.
創(chuàng)建圖表的兩種方法:
1)利用"圖表"工具欄創(chuàng)建,只可創(chuàng)建嵌入式圖表,步驟如下:
a.執(zhí)行[視圖]/[工具欄]/[圖表]命令,顯示"圖表"工具欄;
b.選擇要包含在圖表中的數(shù)據(jù)單元格區(qū)域;
c.在"圖表"工具欄的"圖表類型"的下拉列表中選擇一種圖表.
2)利用"圖表向?qū)? 創(chuàng)建,既可以創(chuàng)建嵌入式圖表,也可以創(chuàng)建圖表工作表,步驟如下:
a.選擇要包含在圖表中的數(shù)據(jù)單元格區(qū)域;
b.單擊"常用"工具欄中的[圖表向?qū)按鈕.或執(zhí)行[插入]/[圖表]命令,屏幕上將出現(xiàn)圖表對話框;
c.按向?qū)崾疽徊揭徊酵瓿刹僮?
注:如果要在圖表中顯示行列標(biāo)志,選擇數(shù)據(jù)單元時需包含標(biāo)志所在單元格.
五.美化工作表
一個完整的工作表通常由數(shù)據(jù)和格式兩部分組成.設(shè)置單元格格式使得工作表更美觀大方.格式設(shè)置包括數(shù)據(jù)的對齊方式,字體,顏色等.
[格式]菜單中包含了美化工作表的絕大部分命令
工具欄上有關(guān)的格式命令
邊框的繪制
斜線表頭的繪制
套用固定格式
使用格式刷:在"常用"工具欄中有一個"格式刷"按鈕,用于復(fù)制選擇的單元格的格式到其他單元格.
使用條件格式:對于滿足一定條件的單元格,可以設(shè)置條件格式.設(shè)置后,只有當(dāng)值符合設(shè)置的條件時,單元格的值才以設(shè)置的格式顯示.
例如,在輸入學(xué)生成績表時,如果希望突出顯示低于60分的成績,就可以用條件格式一次設(shè)定.
六.使用公式和函數(shù)計算數(shù)據(jù)
1.在單元格中使用公式
公式:以"="號開頭,它可以是簡單的數(shù)學(xué)式,也可以是包含Excel函數(shù)的式子.例如"=100/2+5"
公式中可以包含的元素:
運算符:例如"+"或者"*"號.
單元格引用:例如"C57:I57"
數(shù)值或文本:例如,"100"或"新悅國際教育集團(tuán)".
函數(shù):如SUM或MAX,
括號:即"("和")".用來控制公式中各表達(dá)式被處理的優(yōu)先權(quán).
通常在輸入公式后,單元格中反映的不是公式本身,而是由公式計算的結(jié)果,公式本身則在編輯欄中的輸入框中反映.
公式輸入完成后,要記住按"Enter"鍵確認(rèn)完成輸入.
2.單元格引用
在公式中可以用單元格引用來代替單元格中的實際數(shù)據(jù).
1)引用運算符:
引用運算符
示例
引用的單元格
:
(冒號)
C2:I4
表示從C2到I4單元格
,
(逗號)
C2,E4
表示C2和E4單元格
冒號和逗號混合使用
C2:D4,F5:H7
表示從C2到D4單元格,以及從F5到H7單元格
2)引用方式:相對引用,絕對引用,混合引用,引用名字
相對引用是指當(dāng)把一個含有單元格地址的公式復(fù)制到一個新的位置或者用一個公式填入一個區(qū)域時,公式中的單元格地址會隨著改變.
例如,在J2中輸入公式 "=SUM(C2:I2)",然后復(fù)制J2單元格到J3單元格,J3單元格就變成了"=SUM(C3:I3)"
絕對引用是指在把公式復(fù)制或填入到新位置時,其中的單元格地址保持不變.
設(shè)置絕對地址需在行號和列號前加 "$", 例如, "=SUM($C$2: $I$2)"
混合引用是指在一個單元格地址中,既有絕對地址引用又有相對地址引用, 例如, "=SUM(C$2: I2)"
引用名字:就是給單元格區(qū)域起一個名字,然后可以用該名字反復(fù)使用這個單元格區(qū)域.單元格命名的方法有如下三種:
用菜單命令:選定要命名的單元格區(qū)域,單擊[插入]/[名稱]/[定義]
用自動命名:選定文本和單元格區(qū)域,單擊[插入]/[名稱]/[指定]
使用"名稱框"
建立了多個名稱后,為了便于記錄和跟蹤錯誤,可以建立名稱表:選定一個空白單元格,單擊[插入]/[名稱]/[粘貼]
3.在公式中使用函數(shù)
Excel的內(nèi)置函數(shù)有幾百個,在公式中靈活使用這些函數(shù),可以極大地提高公式解決問題的能力,輕松勝任各種復(fù)雜的任務(wù).
如果能記住函數(shù)的名稱和參數(shù),可以直接在單元格中輸入函數(shù),如果不能確定函數(shù)的拼寫和參數(shù),可以使用函數(shù)向?qū)д迟N函數(shù).
從函數(shù)列表中選擇函數(shù)粘貼:
Excel中常用的函數(shù):
(1)SUM:求和函數(shù)
(2)AVERAGE:求平均值函數(shù)
(3)MAX:求最大值函數(shù)
(4)MIN:求最小值函數(shù)
(5)ROUND:四舍五入函數(shù)
(6)SQRT:求平方根函數(shù)
(7)ABS:求絕對值函數(shù)
(8)LEN:測試字符串長度函數(shù)
(9)INT:取整函數(shù)
(10)COUNT:計數(shù)函數(shù)
(11)MOD:求模函數(shù)
(12)COUNTIF:統(tǒng)計某個單元格區(qū)域中符合指定條件的單元格數(shù)目.
(13)FREQUENCY:以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布.
(14)IF:根據(jù)對指定條件的邏輯判斷的真假結(jié)果,返回相對應(yīng)條件觸發(fā)的計算結(jié)果.
(15)RANK:返回某一數(shù)值在一列數(shù)值中的相對于其他數(shù)值的排位.
七.管理數(shù)據(jù)信息
1.排序
2.分類匯總
應(yīng)先選定分類列,按分類列排序后再進(jìn)行分類匯總.
分類匯總完成后,工作表左邊將出現(xiàn)分類匯總標(biāo)志,如下圖所示.單擊標(biāo)志可以顯示或隱藏明細(xì)數(shù)據(jù).
3.篩選數(shù)據(jù)
篩選是查找和處理工作表中數(shù)據(jù)子集的快捷方法.篩選數(shù)據(jù)后僅顯示滿足條件的行.Excel 提供了兩種篩選的命令:
自動篩選,包括按選定內(nèi)容篩選,它適用于簡單條件
高級篩選,適用于復(fù)雜條件,是在工作表上單獨的條件區(qū)域中鍵入篩選條件.條件區(qū)域允許根據(jù)復(fù)雜的條件進(jìn)行篩選.
與排序不同,篩選并不重排數(shù)據(jù).篩選只是暫時隱藏不必顯示的行.
"高級篩選"命令:
單列上具有多個條件
如果對于某一列具有兩個或多個篩選條件,那么可直接在各行中從上到下依次鍵入各個條件.例如,下面的條件區(qū)域顯示"銷售人員"列中包含"Davolio",或"Buchanan",或"Suyama"的行.
銷售人員
Davolio
Buchanan
Suyama
多列上具有單個條件
若要在兩列或多列中查找滿足單個條件的數(shù)據(jù),請在條件區(qū)域的同一行中輸入所有條件.例如,下面的條件區(qū)域?qū)@示所有在"類型"列中包含"農(nóng)產(chǎn)品",在"銷售人員"列中包含"Davolio"且"銷售額"大于 $1,000 的數(shù)據(jù)行.
類型
銷售人員
銷售
農(nóng)產(chǎn)品
Davolio
>1000
某一列或另一列上具有單個條件
若要找到滿足一列條件或另一列條件的數(shù)據(jù),請在條件區(qū)域的不同行中輸入條件.例如,下面的條件區(qū)域?qū)@示所有在"類型"列中包含"農(nóng)產(chǎn)品",或在"銷售人員"列中包含"Davolio",或銷售額大于 $1,000 的行.
類型
銷售人員
銷售
農(nóng)產(chǎn)品



Davolio



>1000
練 習(xí)
打開 練習(xí).xls 文件,進(jìn)入"圖表"表,將5位同學(xué)的成績以圖形方式顯示在該表中.
打開 練習(xí).xls 文件,進(jìn)入 "教師信息"表,進(jìn)行如下練習(xí):
用下拉列表輸入5位教師的職稱,職稱填寫范圍:教授,副教授,講師,組教,無.使用命令:[數(shù)據(jù)]/[有效性],然后在"允許"下拉列表里選擇"序列",在"來源"文本框里輸入"教授,副教授,講師,組教,無",注意每項間用逗號分隔.
按"學(xué)院"字段,以"求和"的方式分類匯總"加班天數(shù)".分類匯總前應(yīng)先按"學(xué)院"字段進(jìn)行排序.
打開 練習(xí).xls 文件,進(jìn)入 "成績統(tǒng)計"表,進(jìn)行如下練習(xí):
鎖定1-2行的顯示位置,使我們在拖動右邊的縱向滾動條時,1-2行始終顯示在屏幕上.使用命令:[窗口]/[凍結(jié)窗格]
將低于60的分?jǐn)?shù)顯示為紅色.使用命令:[格式]/[條件格式]
計算各位學(xué)生的總分.使用函數(shù):SUM(C3:I3)
計算各位學(xué)生的名次.使用函數(shù):RANK(J3,$J$3:$J$42).注意什么時候用絕對地址,什么時候用相對地址.
計算語文0-59分?jǐn)?shù)段人數(shù).使用函數(shù):COUNTIF(C3:C42,"=90")
計算數(shù)學(xué)各個分?jǐn)?shù)段人數(shù).使用"填充柄"拖拉填充
計算英語各個分?jǐn)?shù)段人數(shù).使用函數(shù):FREQUENCY(E3:E42,$O$44:$O$47)
步驟:
①分別選中O44至O47單元格,輸入分?jǐn)?shù)段的分隔數(shù)值(如下圖所示).
②同時選中E43至E47單元格區(qū)域,在"編輯欄"中輸入公式:=FREQUENCY(E3:E42,$O$44:$O$47),輸入完成后,按下"Ctrl+Shift+Enter"組合鍵進(jìn)行確認(rèn),即可一次性統(tǒng)計出"英語"學(xué)科各分?jǐn)?shù)段的學(xué)生人數(shù).
注意:
①實際上此處輸入的是一個數(shù)組公式,數(shù)組公式輸入完成后,不能按"Enter"鍵進(jìn)行確認(rèn),而是要按"Ctrl+Shift+Enter"組合鍵進(jìn)行確認(rèn).確認(rèn)完成后,在公式兩端出現(xiàn)一個數(shù)組公式的標(biāo)志"{}"(該標(biāo)志不能用鍵盤直接輸入).
②數(shù)組公式也支持用"填充柄"拖拉填充:同時選中E43至E47單元格區(qū)域,將鼠標(biāo)移至E47單元格右下角,成細(xì)十字線狀時,按住左鍵向右拖拉,就可以統(tǒng)計出其它學(xué)科各分?jǐn)?shù)段的學(xué)生數(shù).
按名次排序(降序).注意:必須先在43行前插入一空行,再進(jìn)行排序.
將"0"列隱藏起來,再取消隱藏.使用命令:[格式]/[列]/[隱藏],[格式]/[列]/[取消隱藏]
使工作表只顯示出語文和歷史成績都小于60分的學(xué)生信息.
使用命令:[數(shù)據(jù)]/[篩選]/[高級篩選]
廣東工業(yè)大學(xué)第五期教育技術(shù)培訓(xùn) 2007.5.27
網(wǎng)絡(luò)信息與現(xiàn)代教育技術(shù)中心 巫桂梅