Microsoft Excel 詞匯表及有關(guān)操作

1、Microsoft Excel詞匯表及有關(guān)操作假設(shè)已經(jīng)建好了電子表格文件，其中“新生原始總表”數(shù)據(jù)表中有大學(xué)新 生入學(xué)成績數(shù)據(jù)，表格形式見下圖。從第16）19）步開始，用該表做例子學(xué)習(xí) Excel的一些常用操作，并最終完成分班和成績匯總的任務(wù)。從20） 步開始，對建立的“一班”工作表進行操作。ABCDEFGH1北京巾化工學(xué)校新生表2編號姓名性別出生日期語文數(shù)學(xué)英語總分3牛陽1986-10-190759626142李建琪1985-9-1264846421253宇祥1986-10-1584547221064張蓿1985-4-1746193228Microsoft Excel :是制作電子表格的軟件

2、，該軟件具有強大的數(shù)據(jù)計算功能，而且具有強 有力的圖表、圖形制作功能，廣泛應(yīng)用于辦公自動化領(lǐng)域。工作簿（bu）： Execel軟件將每一個工作簿作為一個文件保存，其擴展名為.XLS。工作表：工作表是用來存放數(shù)據(jù)的電子表格，一個工作簿由一個或多個工作表組成。單元格:工作表中所有行列交叉點處的方格稱為單元格，它是工作表的基本成分。單元格里 可以添加的數(shù)據(jù)類型是文本、數(shù)字、日期、時間、公式。活動單元格:用鼠標選中單元格，該單元格成為活動單元格，鼠標雙擊活動單元格，可以寫入 數(shù)據(jù)，刪除數(shù)據(jù)、查看數(shù)據(jù)操作。.0）若要輸入分數(shù)形式，如1/4，正確的輸入方法是：0 1/4 （0與分數(shù)之間有空格）。1）若想將

3、數(shù)字（如身份證號碼）作為文本數(shù)據(jù)處理，需要加單引號，例如要輸入001，則 需要輸入 001。2）年月日的輸入格式：年/月/日，或者年-月-日3）時分秒的輸入格式：時：分：秒4）在保存文件時候，可以設(shè)置文件的打開密碼和修改密碼，以防止非授權(quán)用戶修改文件。 具體操作步驟：在“另存為”對話框上選擇“工具”按鈕，在彈出的下拉菜單中選擇“常 規(guī)選項”，出現(xiàn)“保存選項”對話框，可以設(shè)置密碼。5）使用”插入”菜單，可以在選中的單元格的上方插入行、在選中的單元格的左方插入列、 對選中的單元格加批注。6）整行或整列單元格的選取：用鼠標單擊工作表相應(yīng)的行號或列標。7）在“編輯”菜單中，清除一行（或一列）里的內(nèi)容：

4、選定行或列，“編輯”菜單一 “清除”一“內(nèi)容”。8） 在“編輯”菜單中，刪除一行或一列：選定行或列，“編輯”一“刪除”。9）智能填充編號序列：相鄰單元格中有相同的數(shù)據(jù)或按某種規(guī)律變化的數(shù)據(jù)時，可以利 用智能填充功能實現(xiàn)編號序列的快速輸入：例如在單元格A3和A4輸入數(shù)字1、2,用 鼠標選定單元格A3和A4區(qū)域，然后移動鼠標到選定區(qū)域的右下角（右下角的小黑塊稱 為填充柄），當鼠標光標變成“+”時，按住鼠標左鍵不放，向下拖動光標，進行數(shù)據(jù)自 動填充。10）多個連續(xù)行、列、單元格的選取:按住鍵盤上的“Shift”鍵進行?；蛴檬髽送蟿酉鄳?yīng) 的行或列。11）多個不連續(xù)行、列、單元格的選取:按住鍵盤上的“C

5、trl”鍵進行。12）數(shù)據(jù)有效性的設(shè)置：當所錄用的數(shù)據(jù)有一定的取舍范圍，可以在錄用數(shù)據(jù)前，考慮設(shè) 置數(shù)據(jù)的有效性，可以降低錄入數(shù)據(jù)的錯誤率。例如，選擇語文成績列，選擇“數(shù)據(jù)” 菜單一 “有效性”。對整數(shù)限定范圍是010013）自動求和：“自動求和”功能具有一定的智能分析功能，如果選定選區(qū)的右端或下端 有一空列或空行，則能將自動求和結(jié)果放入相應(yīng)的空列或空行中。例如，選定數(shù)據(jù)列 區(qū)域，以及空白列區(qū)域，選擇自動求和按鈕 ，則求和數(shù)據(jù)放到空百列中。例如求出 每個學(xué)生的各科成績的總和。也可在需要放求和數(shù)據(jù)的單元格內(nèi)，直接輸入求和函數(shù)。例如在H3單元格輸入 “=SUM（E3:G3） ”，然后按回車鍵確認。

6、E3: G3”表示了 E3到G3的所有單元格， 是需要被求和的數(shù)據(jù)范圍。然后向下拖動H3單元格右下角的填充柄，可以自動實現(xiàn)公 式復(fù)制，求出其它學(xué)生的各科成績的總和。例如在 H4單元格中求和的公式會變?yōu)椤?SUM （E4： G4）”。14）工作表的操作：鼠標選取工作表，鼠標點擊右鍵彈出快捷方式菜單，可以對工作表進行改名字、移動、復(fù)制、刪除、插入等操作。也可以直接用鼠標操作工作表，例如雙 擊工作表修改名字、鼠標拖動工作表進行移動、按住鍵盤上的“ Ctrl ”鍵，并拖動工 作表則可以復(fù)制該工作表。15）工作表的“凍結(jié)”對于大的工作表，為了在瀏覽的過程中使表的標題一直處于可見 狀態(tài)，可以選擇工作表的“

7、凍結(jié)”操作?！皟鼋Y(jié)”是以當前活動單元格的左上邊框 為準進行凍結(jié)的。具體操作：選一個單元格為當前活動單元格，“窗口”菜單一 “凍 結(jié)窗格”命令。使用完畢凍結(jié)功能后，要取消凍結(jié)狀態(tài)。16）數(shù)據(jù)排序：若對工作表的某一列或多個列的值進行排序時，應(yīng)先單擊表中的任意單元 格，再選擇“數(shù)據(jù)”菜單提供的排序命令。例如數(shù)據(jù)先按所設(shè)置的“主關(guān)鍵字”排序， 當“主關(guān)鍵字”相同時，再按“次關(guān)鍵字”排序。例如對新生原始總表，設(shè)置主關(guān)鍵字 為“性別”，次關(guān)鍵字為“總分”，則先按男女升序排序，而對全體男生及全體女生再 按總分成績降序排序。若對某列排序，不要把整個列選上，應(yīng)單擊該列的某一單元格。17）創(chuàng)建和填充自定義序列為E

8、xecel增加按某種規(guī)律變化的數(shù)據(jù)序列，例如一班、二班、 三班、四班、四班、三班、二班、一班。創(chuàng)建自定義序列操作方法：“工具”菜單一 “選項”一“自定義序列”。在“自定義 序列”列表中選擇其中的新序列，然后在“輸入序列”框中輸入新序列名，如一班、 二班、三班、四班、四班、三班、二班、一班。等，數(shù)據(jù)用回車鍵換行，最后點擊“添 加”按鈕。自動填充已定義的序列數(shù)：在Execel工作表中，可以增加一個新的名為“班級”的列， 在該列名稱下的8個單元格中依次輸入“一班、二班、三班、四班、四班、三班、二班、 一班”，然后按住shift鍵，選中8個單元格子，然后拖動該8個單元格子的最后面單元 格右下角的填充柄

9、進行填充，則序列數(shù)按已經(jīng)定義的順序循環(huán)填充。經(jīng)過第16和17步驟，可以把原始學(xué)生成績表進行分班，分班采取了總分輪回制， 例如總分第14名分到14班，第58名分到41班，依次類推。注意：如果創(chuàng)建的自定義序列是一班、二班、三班，則當某一單元格內(nèi)輸入“一班” 時候，則可以拖動該單元格右下角的填充柄，則序列數(shù)按已定義的順序循環(huán)填充。18） 數(shù)據(jù)篩詵（shai xuan）：如果工作表中的數(shù)據(jù)太多，可以用“數(shù)據(jù)篩選”功能將滿足 指定條件的數(shù)據(jù)記錄顯示出來，不滿足條件的記錄則被隱藏。具體操作：單擊工作表中 的任意單元格，單擊“數(shù)據(jù)”菜單一 “篩選”一“自動篩選;則在工作表每個列標題 右側(cè)出現(xiàn)下拉箭頭，可以顯

10、示要篩選的條件，例如“一班”，選擇該條件，會篩選出一 班的學(xué)生數(shù)據(jù)，可以把篩選出來的數(shù)據(jù)復(fù)制到另一個工作表中，例如建立“一班“工作 表。用相同的方法可以篩選出其它班的學(xué)生數(shù)據(jù)。再次選擇Excel “數(shù)據(jù)”菜單一 “篩 選”一 “自動篩選”命令，將取消“篩選”狀態(tài)，把原來隱藏的數(shù)據(jù)都顯示出來。19） 分類匯總 能夠按照用戶指定的要求進行數(shù)據(jù)匯總。例如先將第17步分班的學(xué)生總表 數(shù)據(jù)按照“班級”降序排序，然后選擇Excel “數(shù)據(jù)”菜單一 “分類匯總”對話框，對 于“分類字段”，若選擇“班級”，就可以對相同班級的學(xué)生數(shù)據(jù)進行匯總。若選擇的“匯 總方式”為平均值，“選定匯總項目”為語文、數(shù)學(xué)和英語，

11、則可以把該班級學(xué)生的 語文、數(shù)學(xué)和英語成績的平均分統(tǒng)計出來。在“分類匯總”對話框中，單擊“全部刪除”按鈕，可以取消分類匯總的結(jié)果。 注意：分類匯總前，需要用數(shù)據(jù)排序功能對指定的分類字段，例如班級進行排序，然 后才能進行“分類匯總”。匯總的方式有求和、求平均值、統(tǒng)計個數(shù)等。20）計算每個學(xué)生平均分：在存放一班學(xué)生數(shù)據(jù)的“一班”工作表中，添加一列，在I2單 元格內(nèi)輸入“平均分”，如果某個學(xué)生的總分數(shù)所在的單元格用H3來標記，則在該學(xué) 生的平均分單元格I3中輸入公式“ =H3/3”，按回車鍵確認，就可以求出該學(xué)生的3科 成績的平均分。求出第1個學(xué)生的平均分后，拖動其單元格右下角的填充柄，算出每 個學(xué)

12、生的平均分。21） 計算單科平均分：在“一班”工作表中增加一行名為“單科平均分若要求出1班學(xué) 生語文科目的平均分，選定放平均分的單元格，例如E44,單擊“插入”菜單一 “函數(shù)”， 打開“插入函數(shù)”對話框，選擇函數(shù)“ AVERAGE，按“確定”按鈕，打開“函數(shù)參 考”對話框，在Numner 1的輸入框里，輸入函數(shù)所需要的參數(shù)。例如輸入E3： E43，表 明語文科目的第1個成績所在的單元格是E3，語文科目的最后成績所在的單元格是 E43。則可求出語文科目的平均分。用鼠標向右拖動E44單元格右下角的填充柄，算 出其它科目的平均分。也可以直接在放平均分的單元格內(nèi)輸入求平均分的函數(shù)，例如“=AVERAG

13、（E3：E43）” 輸入完畢，按回車鍵確認輸入。注意：公式和函數(shù)的輸入以等號“=”開頭。在單元格內(nèi)插入函數(shù)的方法一：插入”菜單一 “函數(shù)”，打開“插入函數(shù)”對話框。 在單元格內(nèi)插入函數(shù)的方法二：直接輸入以等號開始的函數(shù)。22）計算單科最高分、最低分：按照第21步驟相似的操作，可以求出單科最高分和最低 分，可以在相應(yīng)的單元格內(nèi)輸入函數(shù)，例如求最高分“=MAX （E3： E43）”，求最低分：=MIN （E3： E43）”。23） 總分排名 在“一班”工作表中添加1歹U，在J2單元格內(nèi)輸入名為“總分排名”。若放第1個學(xué)生的總分排名單元格為J3,在J3單元格內(nèi)輸入RANK函數(shù)， 例如“=RANK（H

14、3, $H$3： $H$43）”。其中H3是第1個學(xué)生的總分數(shù)所在的單元格， $h$3:$H$43表示H3到H43所有單元格，反映出排名范圍。用鼠標向下拖動J3單元格 右下角的填充柄，算出其他學(xué)生的排名情況。在該例子中，填充柄可以實現(xiàn)公式的復(fù) 制，但函數(shù)中的第二個參數(shù)不變，即J4單元格中的公式是“=RANK（H4，$H$3:$H$43）。在單元格內(nèi)插入RANK函數(shù)的方法：“插入”菜單一 “函數(shù)”，打開“插入函數(shù)”對話 框。選中上圖中的H3： H43，然后按鍵盤上的F4鍵，則單元格中公式的參數(shù)H3： H43將 改變?yōu)?H$3:$H$43，如下圖所示：24）相對引用和絕對引用的含義*相對引用：在引

15、用單元格或單元區(qū)域的數(shù)據(jù)時，直接使用列號、行號來表示，稱為 相對引用。當公式中的參數(shù)是相對引用形式的參數(shù)時，復(fù)制該公式時，將 自動調(diào)整參數(shù)，以保證公式中單元格的相對關(guān)系保持不變。例如在單元格F3 中輸入公式=D3+E3”，則復(fù)制該公式到F4單元格，則公式變?yōu)椤?D4+E4”， 即公式中單元格的相對關(guān)系是保持不變的。*絕對引用：如果在復(fù)制公式時，不希望參數(shù)發(fā)生變化，即不需要Excel調(diào)整引用，那 么就需要使用絕對引用形式的參數(shù)。絕對引用在引用單元格或單元區(qū)域的數(shù) 據(jù)時，在列標和行標的前面加$。如果選定相對引用H3： H43，則按鍵盤上 的F4鍵，可以把相對引用轉(zhuǎn)換為絕對引用$H$3:$H$43。

16、25）備注在“一班”工作表中再添加1歹L在K2單元格內(nèi)輸入名為“備注”。選定單元格K3, 插入函數(shù)IF，參數(shù)設(shè)置如下：用鼠標向下拖動K3單元格右下角的填充柄，可以自動寫出所有學(xué)生的學(xué)習(xí)情況備注。也可在單元格內(nèi)輸入函數(shù)=IF（1375, “好”，“一般”）”。舉例說明IF函數(shù)的功能：IF函數(shù)對邏輯條件“I375”進行檢查，如果條件成立，函數(shù)值 為“好”，否則為“一般”。經(jīng)過上述步驟處理后，“一班”工作表內(nèi)容形式如下:26）函數(shù)COUNTIF的使用函數(shù)COUNTIF的功能是計算區(qū)域中滿足給定條件的單元格的個數(shù)。語法：COUNTIF （range, criteria）Range :為需要計算其中滿足

17、條件的單元格數(shù)目的單元格區(qū)域。Criteria :為確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式或文本。例如，條件可以表示為32、32、32或apples。下面顯示一個例子： TOC o 1-5 h z AB教據(jù)教據(jù)蘋果32柑桔54桃75蘋果86公式說明皓果1COUNTIFtAS, Apples計聳第一列中蘋果所在單元格的個數(shù)C)=COUNTIF 2:B5? )55) 計算第二列中值大于55的單元格個數(shù)但)統(tǒng)計各分數(shù)段人數(shù)建立“一班平均分分布”工作表，表格框架如圖所示。在B3單元格內(nèi)，插入函數(shù)COUNTIF，參數(shù)設(shè)置如下:在 B4 單元格內(nèi)，插入公式：=COUNTIF(一班！I3

18、:I43, 70)-B3 ”在 B5 單元格內(nèi)，插入公式：=COUNTIF(一班！I3:I43, 80)-B3-B4 ”在 B6 單元格內(nèi)，插入公式：=COUNTIF(班!I3:I43, =90) ”數(shù)據(jù)處理結(jié)果形式如圖:AB1一班平均分分布表2分數(shù)段人數(shù)3=90)/COUNT(B4:B11)該函數(shù)計算出的值是小數(shù)類型的數(shù)據(jù)，改成以百分數(shù)形式顯示單元格的值。選中B15單元 格，選擇Excel的“格式”一“單元格”命令，設(shè)置如下：求學(xué)生成績的等級首先在當前工作表中做一個“查詢數(shù)據(jù)表”，該表有2行數(shù)據(jù)組成。第一行的數(shù)據(jù)是用數(shù)值 表示的等級，這些數(shù)據(jù)必須由小到大排好序，其值分別是：0、60、70、8

19、0、90 0 第二行數(shù)據(jù)是用漢文字表示的等級，與第一行的數(shù)據(jù)對應(yīng)，分別是：不及格、及格、中、 良、優(yōu)秀。在G4單元個內(nèi)插入水平查表函數(shù) =HLOOKUP( F4,$B$19:$F$20,2)第1個參數(shù)表示在查詢數(shù)據(jù)表的第1行范圍內(nèi)將要查找的數(shù)據(jù)，第2個參數(shù)表示在查詢數(shù)據(jù)表的哪些范圍內(nèi)查找，本例子是把查詢數(shù)據(jù)表的所有數(shù)據(jù)選上。 注意該參數(shù)是絕對引用形式的參數(shù)(按盤上的F4鍵，可以把相對引用參數(shù)改成絕 對引用參數(shù))。第3個參數(shù)是查詢數(shù)據(jù)表的行號，表明若查找成功，返回第幾行的數(shù)據(jù)。例如行號2表示 待匹配的數(shù)據(jù)在查詢數(shù)據(jù)表第1行匹配成功，則返回查詢數(shù)據(jù)表第2行對應(yīng)的數(shù) 據(jù)。若匹配失敗，則函數(shù)返回錯誤值

20、。第4個參數(shù)是指明查找時是精確匹配還是近似匹配。如果參數(shù)是TRUE或省略了參數(shù)，表 明是近似匹配，則匹配成功的值是比要查找的數(shù)據(jù)小的那些數(shù)據(jù)當中的最大數(shù)值。 如果參數(shù)是FALSE，則進行精確匹配，如果找不到，則返回錯誤。HL00KUFLookiip_v：alueT abl H_：=Lfr ay Row_i ndexrLijiTiR：=LTLgH_l o okupF4 $B$19:$F$20 2O9 = 一一 = 一一也 60? 70? 80? 90;函致參數(shù)-憂村搜索數(shù)組區(qū)域首行滿足條件的元素，確定待檢索單元格在區(qū)域中的列序號，再進一步 返回選定單元格的值Row_index_num滿足條件的單元格在數(shù)組區(qū)域table_array中的行序號口表格中第一行序號為1有