基礎(chǔ)功能與運用PPT課件

1、1Excel基礎(chǔ)應用基礎(chǔ)應用編輯工作表編輯工作表設(shè)置工作表的格式設(shè)置工作表的格式簡單分析簡單分析1 1、選擇性粘貼、選擇性粘貼2 2、單元格輸入、單元格輸入3 3、相對、相對/ /絕對引用絕對引用1 1、數(shù)據(jù)分列、數(shù)據(jù)分列2 2、數(shù)據(jù)篩選、數(shù)據(jù)篩選3 3、數(shù)據(jù)排序、數(shù)據(jù)排序4 4、數(shù)據(jù)透視表、數(shù)據(jù)透視表1 1、工作表表頭設(shè)計、工作表表頭設(shè)計和凍結(jié)窗格使用和凍結(jié)窗格使用2 2、單元格格式設(shè)置、單元格格式設(shè)置3 3、條件格式、條件格式第1頁/共29頁2一、一、ExcelExcel基礎(chǔ)應用基礎(chǔ)應用編輯工作表編輯工作表1 1、選擇性粘貼、選擇性粘貼步驟：選擇菜單欄“編輯”“選擇性粘貼”進行選擇后點“確

2、定”多種數(shù)字格式可供選擇將被復制數(shù)據(jù)中的數(shù)值與原單元格中的數(shù)值進行運算將被復制數(shù)據(jù)的行列在粘貼時進行對換第2頁/共29頁3一、一、ExcelExcel基礎(chǔ)應用基礎(chǔ)應用編輯工作表編輯工作表2 2、單元格輸入、單元格輸入輸入單元格時要注意合理地利用自動填充功能，先輸入一個，然后把鼠標放到單元格右下角的方塊上，看鼠標變成一個黑色的十字時就按下左鍵向下拖動，到一定的數(shù)目就可以了。輸入序號時，先輸入1、2，然后選中1、2所在的單元格，把鼠標放到單元格右下角的方塊上，看鼠標變成一個黑色的十字時就按下左鍵向下拖動，到一定的數(shù)目就可以了。第3頁/共29頁4一、一、ExcelExcel基礎(chǔ)應用基礎(chǔ)應用編輯工作表

3、編輯工作表3 3、相對引用和絕對引用、相對引用和絕對引用通常通過復制或剪切來異動或復制公式時，公式中的單元格通常是會變化的，即“相對引用”；如果需要保持單元格固定不變，則需要采用“絕對引用”。通常用“$”來固定絕對引用的位置。舉例：A1單元格中的公式，復制到C3，應用不同的的引用形式所得到的對應內(nèi)容如下表所示；：在用“$”來固定絕對引用位置時，可采用快捷鍵F4。以A1單元格為例，按一下F4是固定行和列（$A$1），按兩下F4是固定行（A$1），按三下F4是固定列（$A1 ），按四下F4是取消固定（A1）。引用形式引用形式對應結(jié)果對應結(jié)果$A$1（絕對列和絕對行）$A$1A$1（相對列和絕對行）

4、C$1$A1（絕對列和相對行）$A3A1（相對列和相對行）C3第4頁/共29頁5二、二、ExcelExcel基礎(chǔ)應用基礎(chǔ)應用設(shè)置工作表的格式設(shè)置工作表的格式1 1、常用斜線表頭設(shè)計、常用斜線表頭設(shè)計 操作步驟：選中需要制作斜線表頭的單元格選擇菜單欄“格式”“單元格”“邊框”，選擇“左斜線”切換到“對齊”類別，將“水平對齊”選擇“靠左（縮進）”，“垂直對齊”選擇“居中”，點擊“確定”在制作斜線表頭的單元格中，雙擊鼠標左鍵，顯示輸入焦點使用空格鍵，將輸入焦點定位到單元格靠右的位置，輸入“指標”使用Alt+回車，使輸入焦點定位到單元格的下一行輸入“機構(gòu)”，單擊回車2 2、凍結(jié)窗格的使用、凍結(jié)窗格的使

5、用在屏幕上查看超過一頁的數(shù)據(jù)表，需要使用“凍結(jié)窗格”的功能，以保證在滾動屏幕時，可以一直保持數(shù)據(jù)表行列標題的位置不變。在操作“凍結(jié)窗格”時，需要將鼠標焦點置于需要凍結(jié)的行列標題交匯單元格，右下角的單元格選擇菜單欄“窗口”“凍結(jié)窗格”；如需要取消“凍結(jié)窗格”時，選擇菜單欄“窗口”“取消凍結(jié)窗格”。第5頁/共29頁6二、二、ExcelExcel基礎(chǔ)應用基礎(chǔ)應用設(shè)置工作表的格式設(shè)置工作表的格式3 3、單元格格式設(shè)置、單元格格式設(shè)置用途：使表格更規(guī)范，看起來更有條理、更清楚。步驟：首先選中需要設(shè)置單元格的數(shù)據(jù)范圍選擇菜單欄“格式”“單元格”“數(shù)字”第6頁/共29頁7二、二、ExcelExcel基礎(chǔ)應用

6、基礎(chǔ)應用設(shè)置工作表的格式設(shè)置工作表的格式4 4、條件格式、條件格式用途：根據(jù)單元格內(nèi)容不同顯示不同的格式。步驟：先選中所有要設(shè)置條件格式的單元格選擇菜單欄“格式”“條件格式”當達到條件時，將顯示的格式最多可添加3個條件 第7頁/共29頁8三、三、ExcelExcel基礎(chǔ)應用基礎(chǔ)應用簡單的數(shù)據(jù)分析和管理簡單的數(shù)據(jù)分析和管理1 1、數(shù)據(jù)分列、數(shù)據(jù)分列步驟：首先選中需要分列的那列數(shù)據(jù)選擇菜單欄“數(shù)據(jù)”“分列”文本分列向?qū)А边x擇“固定寬度”根據(jù)需求調(diào)節(jié)標尺注：如需要分列的數(shù)據(jù)中包含逗號、分號、括號等分隔符，可在文本分列向?qū)е羞x擇“分隔符號”。第8頁/共29頁9三、三、ExcelExcel基礎(chǔ)應用基礎(chǔ)應

7、用簡單的數(shù)據(jù)分析和管理簡單的數(shù)據(jù)分析和管理2 2、數(shù)據(jù)篩選、數(shù)據(jù)篩選 自動篩選自動篩選步驟：將鼠標焦點置于數(shù)據(jù)表的任一位置，選擇“數(shù)據(jù)”“篩選”“自動篩選”對篩選出來的結(jié)果進行統(tǒng)計時，可按照下面例子執(zhí)行相關(guān)操作。 高級篩選高級篩選在處理清單數(shù)據(jù)時如需要篩選大量重復數(shù)據(jù)時，可用Countif函數(shù)和自動篩選兩種方法結(jié)合實現(xiàn)。語法如下：Countif（range，criteria ）重復數(shù)據(jù)篩選舉例：第一步Countif(A$1:A2,A2) 第二步自動篩選所需結(jié)果。第9頁/共29頁10三、三、ExcelExcel基礎(chǔ)應用基礎(chǔ)應用簡單的數(shù)據(jù)分析和管理簡單的數(shù)據(jù)分析和管理3 3、數(shù)據(jù)排序、數(shù)據(jù)排序 步

8、驟：選中需要排序的數(shù)據(jù)范圍選擇菜單欄“數(shù)據(jù)”“排序”第10頁/共29頁11三、三、ExcelExcel基礎(chǔ)應用基礎(chǔ)應用簡單的數(shù)據(jù)分析和管理簡單的數(shù)據(jù)分析和管理4 4、數(shù)據(jù)透視表、數(shù)據(jù)透視表 創(chuàng)建數(shù)據(jù)透視表：創(chuàng)建數(shù)據(jù)透視表：選擇菜單欄中的“數(shù)據(jù)”“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命名，開啟向?qū)У谝徊剑褐贫〝?shù)據(jù)源位置第一步：制定數(shù)據(jù)源位置第二步：指定數(shù)據(jù)第二步：指定數(shù)據(jù) 單擊“下一步”按鈕，進入步驟2。此步驟用于指定引用的數(shù)據(jù)范圍。如圖所示：第11頁/共29頁12三、三、ExcelExcel基礎(chǔ)應用基礎(chǔ)應用簡單的數(shù)據(jù)分析和管理簡單的數(shù)據(jù)分析和管理4 4、數(shù)據(jù)透視表、數(shù)據(jù)透視表第三步：完成數(shù)據(jù)透視表。第三步

9、：完成數(shù)據(jù)透視表。 在此步驟中，指定數(shù)據(jù)透視表的位置，可以選擇新建一個工作表，或者在當前工作表中插入。（此例中選擇的是新建一個工作表） 完成向?qū)Ш螅瑪?shù)據(jù)透視表會出現(xiàn)在新建的工作表中，如圖所示：第12頁/共29頁13三、三、ExcelExcel基礎(chǔ)應用基礎(chǔ)應用簡單的數(shù)據(jù)分析和管理簡單的數(shù)據(jù)分析和管理4 4、數(shù)據(jù)透視表、數(shù)據(jù)透視表從“數(shù)據(jù)透視表字段列表”工具欄中拖放字段到模板上，即可得到匯總記錄。舉例：從“數(shù)據(jù)透視表字段列表”中選擇需要作為行的字段，本例中是“管理機構(gòu)代碼”，拖放到“將行字段拖至此處”框中選擇需要作為列的字段，本例中是“銷售渠道”，拖放到“將列字段拖至此處”框中選擇需要統(tǒng)計的字段，

10、本例中是“期交保費”拖放至“請將數(shù)據(jù)項拖放至此處”框中完成后的數(shù)據(jù)透視表如圖。如果想查詢匯總的明細情況，如想看管理機構(gòu)代碼為“8601”的“個險銷售”渠道的“807926.72”是哪些明細數(shù)據(jù)匯總的，可雙擊B5單元格。第13頁/共29頁14三、三、ExcelExcel基礎(chǔ)應用基礎(chǔ)應用簡單的數(shù)據(jù)分析和管理簡單的數(shù)據(jù)分析和管理4 4、數(shù)據(jù)透視表、數(shù)據(jù)透視表更改數(shù)據(jù)透視表更改數(shù)據(jù)透視表數(shù)據(jù)透視表的結(jié)構(gòu)更改，只需要拖不同的字段名稱到模板的相應位置上即可。當添加或移動字段時，Excel會更新數(shù)據(jù)透視表。如果錯了，只需要簡單的把字段拖出模板，放在工作表中，Excel會從數(shù)據(jù)透視表的模板中刪除它。當原始數(shù)據(jù)

11、表的數(shù)據(jù)更改后，數(shù)據(jù)透視表的匯總不會自動更新，需要點擊“數(shù)據(jù)透視表”工具欄上“刷新數(shù)據(jù)”按鈕，手動刷新數(shù)據(jù)?！皡R總項”的匯總方式，當匯總項字段是數(shù)字類型時，默認為“求和”；匯總項字段是文本類型時，默認為“計數(shù)”。可手動控制匯總方式。步驟：選擇“求和項：銷售額”，單擊右鍵，選擇“字段設(shè)置”出現(xiàn)的“數(shù)據(jù)透視表字段”對話框，如圖所示：在“名稱”框中，修改顯示的字段名稱在“匯總方式”列表中選擇指定的匯總運算方式第14頁/共29頁15三、三、ExcelExcel基礎(chǔ)應用基礎(chǔ)應用簡單的數(shù)據(jù)分析和管理簡單的數(shù)據(jù)分析和管理4 4、數(shù)據(jù)透視表、數(shù)據(jù)透視表組合數(shù)據(jù)透視表中的項目組合數(shù)據(jù)透視表中的項目選擇想要組合的

12、項目，單擊鼠標右鍵，從出現(xiàn)的快捷菜單中，選擇“組及顯示明細數(shù)據(jù)”組合命令。如果字段含有數(shù)據(jù)，Excel能自動創(chuàng)建組合。步驟：先制作出數(shù)據(jù)透視表將“應交日期”置于“行字段”框中，將“期交保費”置于“數(shù)據(jù)項中”，制作出數(shù)據(jù)透視表，如下方左側(cè)圖所示：選擇數(shù)據(jù)透視表中的“應交日期”，單擊鼠標右鍵，選擇“組及顯示明細數(shù)據(jù)”“組合”，出現(xiàn)“分組”對話框，如下方中間圖所示，選擇“月”和”季度“，點擊“確定”，完成數(shù)據(jù)分組，如下方右側(cè)圖所示。第15頁/共29頁16ExcelExcel函數(shù)應用函數(shù)應用Excel中的函數(shù)是指一些預先定義好的公式，用戶可以直接使用這些公式對某個區(qū)域內(nèi)的數(shù)值進行一系列運算，如Sum

13、函數(shù)可以對單元格或單元格區(qū)域進行加法運算等。Excel函數(shù)大致可分為：邏輯函數(shù)、數(shù)學和三角函數(shù)、統(tǒng)計函數(shù)、查找和引用函數(shù)、文本函數(shù)、信息函數(shù)、財務函數(shù)、工程函數(shù)、日期和時間函數(shù)、數(shù)據(jù)庫函數(shù)、兼容性函數(shù)、多維數(shù)據(jù)集函數(shù)、自定義函數(shù)等幾個大類。其中邏輯函數(shù)、數(shù)學函數(shù)、統(tǒng)計函數(shù)、查找和引用函數(shù)、文本函數(shù)中有一些函數(shù)是我們?nèi)粘＾k公中經(jīng)常用到的。這些常用函數(shù)如下表所列：第16頁/共29頁17一、一、ExcelExcel函數(shù)應用函數(shù)應用邏輯函數(shù)邏輯函數(shù)l函數(shù)說明函數(shù)說明函數(shù)函數(shù)表達式表達式描述描述AND 函數(shù)=AND(logical1,logical2, .)所有參數(shù)的計算結(jié)果為 TRUE 時，返回 TR

14、UE；只要有一個參數(shù)的計算結(jié)果為 FALSE，即返回 FALSE。OR 函數(shù)=OR(logical1,logical2, .)任何一個參數(shù)邏輯值為 TRUE，即返回 TRUE；任何一個參數(shù)的邏輯值為 FALSE，即返回 FALSEl函數(shù)示例函數(shù)示例=AND(ture,2+2=5) 等于 False=AND(true,true) 等于 True=OR(true,2+2=5) 等于 True=OR(true,true) 等于 True=OR(False,False) 等于 False注：注：AND、OR函數(shù)常用于多重條件判斷，可與IF、COUNTIF等函數(shù)組合使用，以達成多重判斷條件第17頁/共2

15、9頁18一、一、ExcelExcel函數(shù)應用函數(shù)應用邏輯函數(shù)邏輯函數(shù)l 函數(shù)說明函數(shù)說明l函數(shù)示例函數(shù)示例=IF(“13月繼續(xù)率”90%，”達標”，“不達標”） 公式表達的內(nèi)容：如果機構(gòu)的13月繼續(xù)率大于90%,則“達標”，否則“不達標”。=IF(and（“13月繼續(xù)率”90%）,（“25月繼續(xù)率”95%），“有獲獎資格”，“沒有獲獎資格”）公式表達的內(nèi)容：如果機構(gòu)的13月繼續(xù)率大于90%并且25月繼續(xù)率大于95%,則“有獲獎資格”，否則“沒有獲獎資格”。=IF（OR（“13月繼續(xù)率”90%），（“25月繼續(xù)率”95%），“優(yōu)秀”，“良好”）公式表達的內(nèi)容：如果機構(gòu)的13月繼續(xù)率大于90%或者

16、25月繼續(xù)率大于95%,則機構(gòu)達成“優(yōu)秀”，否則機構(gòu)達成“良好”。 注：注：1、IF函數(shù)的條件和返回值可以為具體數(shù)值、文字或函數(shù)；函數(shù)函數(shù)表達式表達式描述描述IF 函數(shù)=IF(logical_test, value_if_true, value_if_false)如果指定條件的計算結(jié)果為 TRUE，IF 函數(shù)將返回某個值；如果該條件的計算結(jié)果為 FALSE，則返回另一個值第18頁/共29頁19二、二、ExcelExcel函數(shù)應用函數(shù)應用數(shù)學函數(shù)數(shù)學函數(shù)l 函數(shù)說明函數(shù)說明l函數(shù)示例函數(shù)示例=SUM(A1，B2，C3） 等于 A1+B2+C3=SUM(A1:A3) 等于 A1+A2+A3=SUM

17、IF(條件區(qū)域，選擇條件，結(jié)果累計區(qū)域），舉例如下：函數(shù)函數(shù)表達式表達式描述描述SUM 函數(shù)=SUM(number1,number2,.)對指定參數(shù)的所有數(shù)字相加SUMIF 函數(shù)=SUMIF(range, criteria, sum_range)對區(qū)域（工作表的兩個或多個單元格，可相鄰或不相鄰）中符合指定條件的值求和第19頁/共29頁20二、二、ExcelExcel函數(shù)應用函數(shù)應用數(shù)學函數(shù)數(shù)學函數(shù)l 函數(shù)說明函數(shù)說明l函數(shù)示例函數(shù)示例 示例：95.4567% 保留1 1位位小數(shù)：=Round(95.4567%,3 3) 結(jié)果返回95.5% 保留3 3位位小數(shù)：=Round(95.4567%,5

18、 5)結(jié)果返回95.457%函數(shù)函數(shù)表達式表達式描述描述ROUND 函數(shù) Round(expression,numdecimalplaces) 返回按指定位數(shù)進行四舍五入的數(shù)值。第20頁/共29頁21二、二、ExcelExcel函數(shù)應用函數(shù)應用統(tǒng)計函數(shù)統(tǒng)計函數(shù)l 函數(shù)說明函數(shù)說明l函數(shù)示例函數(shù)示例函數(shù)函數(shù)表達式表達式描述描述COUNT 函數(shù)=COUNT(value1, value2, .)=COUNT(A1:A20)計算包含數(shù)字的單元格以及參數(shù)列表中數(shù)字的個數(shù)COUNTIF 函數(shù)=COUNTIF(range, criteria)對區(qū)域中滿足單個指定條件的單元格進行計數(shù)注：函數(shù)公式中用到的引號必

19、須是英文格式下的引號，否則公式會提示錯誤。第21頁/共29頁22二、二、ExcelExcel函數(shù)應用函數(shù)應用統(tǒng)計函數(shù)統(tǒng)計函數(shù)l 函數(shù)說明函數(shù)說明函數(shù)函數(shù)表達式表達式描述描述MAX函數(shù)=MAX(number1,number2)返回參數(shù)列表中的最大值MIN 函數(shù)=MIN(number1,number2)返回參數(shù)列表中的最小值l 函數(shù)示例函數(shù)示例第22頁/共29頁23二、二、ExcelExcel函數(shù)應用函數(shù)應用統(tǒng)計函數(shù)統(tǒng)計函數(shù)l 函數(shù)說明函數(shù)說明函數(shù)函數(shù)表達式表達式描述描述RANK函數(shù)=Rank(number, ref, order)排名函數(shù)，指定字段的值在指定范圍中的排名。l 函數(shù)示例函數(shù)示例 舉

20、例：=Rank(B2,$B$2:$B$5,0） 0表示從高到低排名 =Rank(B2,$B$2:$B$5,1） 1表示從低到高排名 23第23頁/共29頁24二、二、ExcelExcel函數(shù)應用函數(shù)應用查詢函數(shù)查詢函數(shù)l 函數(shù)說明函數(shù)說明l函數(shù)式列：函數(shù)函數(shù)表達式表達式描述描述VLOOKUP 函數(shù)=VLOOKUP(查找對象, 查找數(shù)據(jù)范圍, 返回值列數(shù),匹配邏輯)HLOOKUP 函數(shù)=HLOOKUP(查找對象, 查找數(shù)據(jù)范圍, 返回值行數(shù),匹配邏輯)在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并返回指定行中同一列的數(shù)值在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。

21、 查找對象：查找對象： 為需要在“查找數(shù)據(jù)范圍”的第一列中查找的對象。查找數(shù)據(jù)范圍：查找數(shù)據(jù)范圍： 為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表。返回值列數(shù)返回值列數(shù) ：在“數(shù)據(jù)范圍” 中需要查找的結(jié)果值在“查找對象”那列開始起算的列數(shù)。其中VLOOKUP中V代表垂直；HLOOKUP中H代表行。第24頁/共29頁25二、二、ExcelExcel函數(shù)應用函數(shù)應用文本函數(shù)文本函數(shù)l 函數(shù)說明函數(shù)說明l 函數(shù)示例函數(shù)示例以“86010009”字符串為例需要截取“8601”公式=M Midid( (8601000986010009, ,1 1, ,4 4) ) 或者是Left(86010009,Left(860100

22、09,4 4) )需要截取“09”公式=Mid(86010009,Mid(86010009,7 7, ,2 2) )或者是Right(86010009,Right(86010009,2 2) )需要截取“100”公式=MidMid（8601000986010009，4 4, ,3 3）函數(shù)函數(shù)表達式表達式描述描述Mid 函數(shù)=Mid(text,start_num,num_chars)從字符串中返回指定數(shù)目的字符Right函數(shù)Left函數(shù)Right ( string, n ) Left( string, n ) 從字符串右端取指定個數(shù)字符從字符串左端取指定個數(shù)字符第25頁/共29頁26二、二、ExcelExcel函數(shù)應用函數(shù)應用信息函數(shù)信息函數(shù)l 函數(shù)說明函數(shù)說明如果 expression 參數(shù)表示一個錯誤，則