OFFICE-EXCEL培訓(xùn)講義.ppt

1、EXCEL基礎(chǔ)與實用技巧,主要內(nèi)容,基本概念 基本操作 公式與函數(shù) 數(shù)據(jù)管理 頁面打印與設(shè)置,EXCEL基本概念,工作?。阂粋€Excel文件就是一個工作薄，擴(kuò)展名“.xls”，可以包括若干個工作表； 工作表：由若干行和列組成，對應(yīng)一個二維表格，最大行65536行，最大列255列； 單元格：行列交叉的小格子。相當(dāng)于表格中的一格； 單元格名稱：由列標(biāo)題和行號組成的符號，如“A10”表示第一列第10行那個單元格； 單元格區(qū)域：由左上角單元格名稱和右下角單元格名稱標(biāo)識，中間用冒號（:）隔開，如“B3:D10”表示從B3單元格到D10單元格之間的所有單元格組成的區(qū)域。,EXCEL基本操作,工作薄 工作薄

2、的建立 1.選擇文件 |“新建”命令，彈出新建工作簿的任務(wù)窗口，如圖所示，單擊“空白工作簿”，建立。 2.使用快捷鍵Ctrl+N建立工作簿. 3.單擊“常用工具欄”|“新建”按鈕，建立默認(rèn)模板的工作簿。,工作薄的保存 1.選擇“文件”|“保存”命令，彈出“另存為”對話框，如圖所示。 2.選擇“文件”|“另存為”命令，可以對保存過的文檔重新命名、更改路 徑等操作。 3.使用快捷鍵Ctrl+S保存工作簿。 4.使用“常用工具欄”的保存按鈕。,EXCEL基本操作,工作薄的打開 1.選擇“文件”|“打開”命令，彈出“打開”對話框，選擇要打開的文檔即可。如圖所示。 2.使用“常用工具欄”的打開按鈕。,E

3、XCEL基本操作,工作表 插入與刪除工作表 1.插入工作表 1) 選擇插入工作表位置，單擊“插入”|“工作表”命令 2) 選擇插入工作表位置，右鍵單擊，彈出快捷菜單中選“插入”命令，彈出“插入”對話框，選擇工作表。 2.刪除工作表 1)選擇要刪除的工作表，選擇“編輯”|“刪除工作表”命令，或右鍵單擊要刪除的工作表標(biāo)簽，彈出快捷菜單中選則“刪除”命令即可。,EXCEL基本操作,工作表的命名與著色 1.工作表命名 1)雙擊工作表標(biāo)簽名，輸入新的名稱 2.工作表著色 1)右鍵單擊工作表名稱，在彈出的快捷菜單中選擇“工作表標(biāo)簽顏色”命令，設(shè)置其顏色即可。,EXCEL基本操作,復(fù)制與移動工作表 1.在同

4、一工作簿內(nèi)復(fù)制或移動工作表 1)選擇要移動的工作表，按住鼠標(biāo)左鍵；沿工作表標(biāo)簽拖 動鼠標(biāo)，然后釋放鼠標(biāo)。 2) 如果要復(fù)制工作表，則在拖動鼠標(biāo)時，按住Ctrl鍵操作。 2.在不同工作簿內(nèi)復(fù)制或移動工作表 1)打開用于接收工作表的目標(biāo)工作簿；再打開需要移動或復(fù)制的工作表,選擇“編輯”|“移動或復(fù)制工作表”命令，或右鍵單擊工作表標(biāo)簽,在彈出的快捷菜單中選擇“移動或復(fù)制工作表”命令，彈出移動或復(fù)制對話框.,EXCEL基本操作,拆分與凍結(jié)工作表,EXCEL基本操作,工作表的行與列 1.插入行或列 1)選擇“插入”|“行或列”命令，或右鍵單擊行號或列標(biāo)，在 彈出的快捷菜單中選擇“插入”命令。 2.刪除行

5、或列 1)右鍵單擊要刪除的行號或列標(biāo)，在彈出的快捷菜單中選擇“刪除”命名即可刪除。 3.調(diào)整行高或列寬 1)選擇要調(diào)整的行或列，單擊“格式”|“行或列”，在其子菜單中設(shè)置行高或列寬，或直接右鍵單擊行號或列標(biāo)，在彈出的快捷菜單中選“行高或列寬”設(shè)置。,EXCEL基本操作,單元格 選定單元格 1.選定一個單元格 單擊相應(yīng)的單元格，或按四個方向鍵移動到相應(yīng)的單元格。 按Enter是向下移動一個單元格，按Shift+Enter則是向上移動一個單元格。按Tab鍵是向右移動一個單元格，按Shift+Tab則是向左移動一個單元格。 2.選定多個連續(xù)的單元格區(qū)域 單擊區(qū)域的第一個單元格，按住鼠標(biāo)左鍵拖動到區(qū)域

6、的最后一個單元格。如果區(qū)域較大也可以單擊區(qū)域第一個單元格，按住Shift鍵再單擊區(qū)域 最后一個單元格。,EXCEL基本操作,EXCEL基本操作,3.選定不連續(xù)的單元格區(qū)域 選定區(qū)域的第一個單元格，再按住Ctrl鍵，單擊選中其它單元格。 4.選定行或列 單擊行號或列標(biāo)就可選定一行或一列。如果選定相鄰的多行或多列，則 可以直接在行號或列標(biāo)上拖動鼠標(biāo)左鍵。不相鄰的多行或多列，則可按 住Ctrl鍵再選中其它行號或列標(biāo)。 5.全選所有單元格 單擊“全選”按鈕，所有的行和所有的列都被選中 。,移動或復(fù)制單元格 單元格的移動和復(fù)制實際上就是單元格內(nèi)容及格式等一起移動和復(fù)制，包括單元的移動、復(fù)制和插入等。利用

7、復(fù)制，重復(fù)的數(shù)據(jù)只需輸入一次，從而提高工作效率，操作方法如下： 1.選定需要移動或復(fù)制的單元格區(qū)域； 2.右鍵單擊，在彈出的快捷菜單中，要移動選擇“剪切”命名。要復(fù)制則選擇“復(fù)制”命令； 3.選擇目標(biāo)位置，右鍵單擊，在彈出的快捷菜單中選“粘貼”命令。,EXCEL基本操作,數(shù)據(jù)的清除與刪除 1.清除 1)如果選“清除”中的“全部”命令，則單元格中的內(nèi)容、格式、批注等都刪除。 2)如果選“清除”中的“內(nèi)容”命令，則清除了單元格的內(nèi)容，保留了單元格的格式、批注。與按鍵盤上Delete功能相同。 3)如果選“清除”中的“批注”命令，則清除了單元格的批注，保留了單元格的格式、內(nèi)容。 4)如果選“清除”中

8、的“格式”命令，則清除了單元格的格式，保留了單元格的內(nèi)容、批注。 2.刪除 1)而“編輯”|“刪除”命令是針對單元格為對象的操作。在刪除單元格時，可以設(shè)置右側(cè)單元格左移或下方單元格上移；也可以整行、整列刪除。,EXCEL基本操作,表格格式化 基本格式化 數(shù)字 對齊 字體 邊框 圖案 保護(hù),EXCEL基本操作,數(shù)字,EXCEL基本操作,對齊,EXCEL基本操作,字體,EXCEL基本操作,邊框,EXCEL基本操作,圖案,EXCEL基本操作,保護(hù),EXCEL基本操作,高級格式化 快速復(fù)制格式 條件格式 自動套用格式,EXCEL基本操作,快速復(fù)制格式 快速復(fù)制單元格的格式的最簡單方法是利用“格式刷”功

9、能按 鈕，選擇被復(fù)制格式的單元格，單擊格式工具欄里的格式刷 按鈕， 然后依次單擊目標(biāo)單元格。也可以選中被復(fù)制的格式后，按F4鍵,EXCEL基本操作,條件格式 選擇“格式”|“條件格式”命令，彈出“條件格式”對話框，如圖,EXCEL基本操作,自動套用格式 自動套用格式是指將Excel 2003內(nèi)部提供的格式自動套用到 指定的區(qū)域。Excel提供了一套精美的制表樣式，使用者可 以輕松地套用到自己的工作表中。 選擇“格式”菜單的“自動套用格式”命令， 彈出如圖對話框。,EXCEL基本操作,公式 單元格的引用 函數(shù) 常用函數(shù)應(yīng)用技巧,EXCEL公式與函數(shù),公式組成 算術(shù)運算 關(guān)系運算 字符運算 自動求

10、和,EXCEL公式與函數(shù),算術(shù)運算 加（+）、減（-）、乘（*）、除（/）、百分號（%）、指 數(shù)（）。當(dāng)多個運算符同時出現(xiàn)在公式中時，Excel則先算 百分號和乘，再算乘除，最后算加減。,EXCEL公式與函數(shù),關(guān)系運算 關(guān)系運算主要是進(jìn)行一些比較運算，其結(jié)果是邏輯型的值真 （TRUE）與假（FALSE）。主要的運算符有：等于 （=）、大于（）、小于（=）、不等于()。 輸入公式：“=100200”，其結(jié)果應(yīng)該為：False。 如果在C9單元格中輸入公式：“=C2C3”，其結(jié)果應(yīng)該為：True。,EXCEL公式與函數(shù),字符運算 字符運算，主要就是文字的連接運算，其運算符為：連接符 （&），用于兩

11、個文本的連接。 輸入公式：“=”中國”&”人民”,其結(jié)果應(yīng)該為：”中國人民”,EXCEL公式與函數(shù),單元格的引用 公式的引用就是對單元格進(jìn)行標(biāo)志，以用來在公式中使用所標(biāo) 志的單元格。標(biāo)識單元格最準(zhǔn)確、最有效的方法就是使用鼠 指向單元格地址后單擊。 相對引用 絕對引用 混合引用,EXCEL公式與函數(shù),相對引用 基于公式引用單元格的相對位置。相對引用公式中的參數(shù)用行、列地址作為它的名字。當(dāng)該公式被復(fù)制到其它位置時，系統(tǒng)能根據(jù)移動的位置調(diào)節(jié)引用單元格的地址 。,EXCEL公式與函數(shù),絕對引用 指在公式復(fù)制時不發(fā)生變化的地址引用。因為在某些操作中，常常需要引用固定單元格地址中的內(nèi)容來進(jìn)行計算，這個不變

12、的單元格地址的引用稱為絕對引用。Excel 2003是通過對單元格地址的“凍結(jié)”來達(dá)到此目的，即在列標(biāo)和行號前面加入符號“”，如A2，B1等。,EXCEL公式與函數(shù),混合引用 指公式中參數(shù)的行、列地址各采用不同的引用方式。也就是說，在一個單元格地址引用中，既有絕對地址引用，又有相對單元格地址引用。混合引用適合于復(fù)制公式時只有行或只有列保持不變的情形。絕對引用列采用C2、E1等形式，絕對引用行采用C2、E1等形式。,EXCEL公式與函數(shù),函數(shù) 函數(shù)的格式 函數(shù)的輸入,EXCEL公式與函數(shù),函數(shù)的格式 一個函數(shù)包含兩部分：函數(shù)名和參數(shù)表列表。 函數(shù)名表示進(jìn)行什么樣的操作，一般較短，是英文單詞的縮寫

13、； 參數(shù)指在函數(shù)中要用到的值和單元格。根據(jù)函數(shù)的不同，有的函數(shù)沒有參數(shù)，有的函數(shù)則需要輸入多個參數(shù)，形成參數(shù)表。參數(shù)列表總是用括號“（）”括起來，括號內(nèi)包括函數(shù)計算所需要的數(shù)據(jù)。在有參數(shù)時要按照正確的次序和格式輸入?yún)?shù)。 如：=Sum(A1:B3) 注意：使用函數(shù)時，括號應(yīng)成對出現(xiàn)，括號和函數(shù)名及參數(shù)間無空格和其它字符。同使用公式一樣，使用函數(shù)也以“=”符號開始。,EXCEL公式與函數(shù),函數(shù)的輸入 鍵盤輸入 使用函數(shù)向?qū)?EXCEL公式與函數(shù),常用函數(shù)應(yīng)用技巧 SUM&SUBTOTAL應(yīng)用 IF&SUMIF應(yīng)用 VLOOKUP&HLOOKUP應(yīng)用 LEFTRIGHTMID應(yīng)用 AND/OR應(yīng)用

14、 ISERROR應(yīng)用 函數(shù)嵌套使用,EXCEL公式與函數(shù),SUM&SUBTOTAL應(yīng)用 SUM:用于計算單元格區(qū)域中所有數(shù)值的和. 關(guān)鍵點:區(qū)域、數(shù)值、和 格式:=SUM(A1:A100) Example:,EXCEL公式與函數(shù),SUM&SUBTOTAL應(yīng)用 SUBTOTAL:返回一個數(shù)據(jù)列表或數(shù)據(jù)庫的分類匯總. 關(guān)鍵點:列表、數(shù)據(jù)庫、分類匯總 格式:=SUBTOTAL(9,A1:A100) 注：常用的匯總函數(shù)值是2和9. 2匯總不為空的單元格數(shù)目,9匯總區(qū)域里的數(shù) 值和. Example:,EXCEL公式與函數(shù),SUM&SUBTOTAL區(qū)別 1.SUM是匯總一個區(qū)域里的數(shù)值的和，非數(shù)值是無法

15、匯總出和值.同時, SUM的區(qū)域一旦被設(shè)定,其和值將會限定在固定的區(qū)域內(nèi). 2.SUBTOTAL是匯總出一個區(qū)域或數(shù)據(jù)庫里的指定值,比如,選擇函數(shù) 值2，匯總出的是非空白的單元格數(shù)目.同時,區(qū)域雖然被設(shè)定,但會隨 著區(qū)域顯示的不同匯總出不同的值. 演示:,EXCEL公式與函數(shù),IF&SUMIF應(yīng)用 IF:判斷一個條件是否滿足,如果滿足返回一個值,如果不滿足返回另一個值. 關(guān)鍵點:條件、滿足或不滿足、返回值 格式:=IF(A2=“大米”, “糧食”, “非糧食”) Example:,EXCEL公式與函數(shù),IF&SUMIF應(yīng)用 SUMIF:對滿足條件的單元格求和. 關(guān)鍵點:滿足條件、求和 格式:=

16、SUMIF(A:A,條件,B:B) Example:,EXCEL公式與函數(shù),IF&SUMIF區(qū)別 IF:單純的去判斷某個單元格的值是否滿足一定的條件，滿足時或不滿足時返回自己指定的標(biāo)示值. SUMIF:除去IF單純的判斷之外,還會把符合條件范圍內(nèi)的數(shù)值匯總求和. 演示:,EXCEL公式與函數(shù),VLOOKUP&HLOOKUP應(yīng)用 VLOOKUP:搜索某個表區(qū)域內(nèi)首列滿足條件的元素, 指定單元格在區(qū)域中的 行序號,再返回指定單元格的值. 格式:=VLOOKUP(A1,B1:C8,5,0) Example:,EXCEL公式與函數(shù),VLOOKUP&HLOOKUP應(yīng)用 HLOOKUP:搜索某個表區(qū)域內(nèi)首

17、行滿足條件的元素, 指定單元格在區(qū)域中的列序號,再返回指定單元格的值. 格式:=HLOOKUP(A1,B1:C8,5,0) Example: VLOOKUP&HLOOKUP區(qū)別 V是以首列開始尋找所需要的值， H是以首行開始尋找所需要的值.,EXCEL公式與函數(shù),LEFTRIGHTMID應(yīng)用 LEFT:從一個文本字符串的左邊第一個字符開始返回指定個數(shù)的字符. 關(guān)鍵點:左邊、第一個 格式:=LEFT(A3,4) RIGHT:從一個文本字符串的右邊第一個字符開始返回指定個數(shù)的字符. 關(guān)鍵點:右邊,第一個 格式:=RIGHT(A3,4) MID:從文本字符串中指定的位置起返回指定長度的字符. 關(guān)鍵點

18、:指定位置、指定長度 格式:=MID(A3,2,6) LEFTRIGHTMID區(qū)別 每個函數(shù)取值的起點不一樣,以上三個函數(shù)分別是從左邊往右邊取值、從右邊 往左邊取值、從任意點取值. 演示:,EXCEL公式與函數(shù),AND/OR應(yīng)用 AND:檢查所有參數(shù)是否均為TRUE，如果所有參數(shù)均為TRUE則返回TRUE. 關(guān)鍵點:檢查、均為(并且) 格式:=AND(A45) OR:檢查任一參數(shù)值為TRUE,則返回TRUE,只有當(dāng)所有參數(shù)均為FALSE時才返回FALSE. 關(guān)鍵點:檢查、任一、所有均為FALSE 格式:=OR(A45) AND/OR區(qū)別 AND在檢查參數(shù)的時候是一種并列關(guān)系,如果含用的條件有二

19、個以上，則二個以上的條件都必須同時滿足才會返回TRUE.而OR則不一樣,只要有一個值符合條件則就會顯示TURE.只有當(dāng)所有參數(shù)都為FALSE時才會顯示FALSE。 演示:,EXCEL公式與函數(shù),ISERROR應(yīng)用 ISERROR:檢查一個值是否為錯誤值(#N/A,#VALUE,#DIV/O,#NUM ,#NAME?或#NULL)返回TRUE或FALSE. 關(guān)鍵點:檢查、錯誤值 格式:=ISERROR(A12) Example:,EXCEL公式與函數(shù),函數(shù)嵌套使用 指的是在日常工作中,很多函數(shù)都不是單獨使用,而是相互摻合在一起 使用,從而得出某一些自己想要的結(jié)果. 1.常被用于嵌套的函數(shù): LE

20、FT、RIGHT、MID、ISERROR、OR、AND 2.常會嵌套其它函數(shù)的函數(shù): IF、HLOOKUP、VLOOKUP、SUMIF 3.嵌套規(guī)律一般如下: A. IF與OR、AND、LEFT、RIGHT、MID、ISERROR、 HLOOKUP/VLOOKUP、SUMIF B. HLOOKUP/VLOOKUP與LEFT、RITHG、MID、SUMIF C. SUMIF與HLOOKUP/VLOOKUP、LEFT、RIGHT、MID 演示:,EXCEL公式與函數(shù),數(shù)據(jù)排序 篩選數(shù)據(jù) 數(shù)據(jù)分類匯總 數(shù)據(jù)透視表與透視圖 數(shù)據(jù)合并,EXCEL數(shù)據(jù)管理,數(shù)據(jù)排序 簡單排序 復(fù)雜數(shù)據(jù)排序,EXCEL數(shù)據(jù)

21、管理,簡單排序 簡單排序是指對一個字段進(jìn)行升序或降序排序，一般采用常用工具欄上的按鈕來快速排序。,EXCEL數(shù)據(jù)管理,復(fù)雜數(shù)據(jù)排序 當(dāng)按一個字段進(jìn)行排序時，如果出現(xiàn)相同值，這時就可以采用第二個字段進(jìn)行排序，Excel 2003最多支持三個字段級的復(fù)雜排序。此時必須通過“數(shù)據(jù)”|“排序”命令實現(xiàn)。,EXCEL數(shù)據(jù)管理,篩選數(shù)據(jù) 自動篩選 自定義自動篩選 撤消自動篩選 高級篩選,EXCEL數(shù)據(jù)管理,自動篩選 自動篩選是按選定內(nèi)容篩選，它適用于簡單條件。該命令在數(shù)據(jù)清單的各字段名的右下角顯示一個下拉箭頭，單擊該箭頭就出現(xiàn)一個一列中所有的不同值的下拉列表（如果有相同的值只顯示一個），以便選擇篩選的數(shù)據(jù)

22、項。,EXCEL數(shù)據(jù)管理,高級篩選 “高級篩選”可以查找需要匹配計算條件或匹配較為復(fù)雜的“與”、“或”條件數(shù)據(jù)，突破了“自動篩選”對一列數(shù)據(jù)最多只能應(yīng)用兩個條件的限制，可以對一列數(shù)據(jù)應(yīng)用3個或更多的條件?？梢詫l件區(qū)域設(shè)置在同一工作表中，但必須與數(shù)據(jù)清單相距一個空白行或列。 如圖盤點表中篩選出倉管為姚瑤、庫存大于5的產(chǎn)品記錄。,EXCEL數(shù)據(jù)管理,Step1:操作,EXCEL數(shù)據(jù)管理,Step1:設(shè)置篩選條件,單擊數(shù)據(jù)清單的任意單元格，選擇“數(shù)據(jù)”|“篩選”|“高級篩選”命令， （4）在“條件區(qū)域”編輯框中，鍵入條件區(qū)域的引用（不包括條件標(biāo)志） （5）不顯示重復(fù)記錄，則選中“選擇不重復(fù)的記錄”

23、復(fù)選框；,EXCEL數(shù)據(jù)管理,Step1:設(shè)置篩選,EXCEL數(shù)據(jù)管理,Step1:篩選結(jié)果,數(shù)據(jù)分類匯總 分類匯總是對數(shù)據(jù)清單中指定的字段進(jìn)行分類，然后統(tǒng)計同一類記錄的有關(guān)信息。 單個字段分類匯總 多個字段分類匯總,EXCEL數(shù)據(jù)管理,單個字段分類匯總 按產(chǎn)品狀態(tài)統(tǒng)計每種狀態(tài)的最大實際庫存,EXCEL數(shù)據(jù)管理,對匯總字段“狀態(tài)”，進(jìn)行排序，把相同班級的記錄排在一起，如按班級升序； 單擊數(shù)據(jù)清單任意單元格，選擇“數(shù)據(jù)”|“分類匯總”命令，彈出如圖所示對話框； 在“分類字段”下拉列表框中，單擊需要用來分類匯總的數(shù)據(jù)列。選定的數(shù)據(jù)列應(yīng)與步驟（1）中排序的列相同。本例選“班級”字段； “匯總方式”

24、下拉列表框中選中用于計算分類匯總的函數(shù)，本例中選擇“最大值”；,EXCEL數(shù)據(jù)管理,Step1:操作,EXCEL數(shù)據(jù)管理,Step1:匯總結(jié)果,多個字段分類匯總 1.多個字段分類匯總是指按2個或2個以上字段進(jìn)行排序，并根據(jù)排序的主次順序進(jìn)行多個分類匯總，如在盤點統(tǒng)計表中統(tǒng)計各個倉管下每種狀態(tài)的最大值產(chǎn)品明細(xì)。具體操作步驟如下： 2.排序操作，選擇“數(shù)據(jù)”|“排序”命令，“主要關(guān)鍵字” 處選“倉管”，順序選“升序”，“次要關(guān)鍵字處”選“狀 態(tài)”，順序選“升序”，如圖所示；,EXCEL數(shù)據(jù)管理,按“院系”建立第一次分類匯總，選擇“數(shù)據(jù)”|“分類匯總”命令，分類處選“倉管”，匯總方式選“最大值”，選定匯總項處選“實際庫存”，其余默認(rèn)，結(jié)果如圖所示；,EXCEL數(shù)據(jù)管理,Step1:第一次匯總,按“狀態(tài)”建立第二次分類匯總，選擇“數(shù)據(jù)”|“分類匯總”命令，分類處選 “狀態(tài)