Excel常用技能炫酷培訓(xùn)ppt課件

1、Excel Art圖表改動(dòng)任務(wù)生活陜西黃馬甲物流配送股份運(yùn)營(yíng)中心：楊莉2021-11.-XX1目錄EXCEL根本功能演示EXCEL圖形圖標(biāo)演示公式的運(yùn)用鍵盤的運(yùn)用技巧表間的鏈接運(yùn)用數(shù)據(jù)透視表/圖的制造2EXCEL 根本功能演示圖形圖標(biāo)演示3EXCEL公式的運(yùn)用常用公式演示4COUNT統(tǒng)計(jì)函數(shù)，可以計(jì)算數(shù)組或單元格區(qū)域中數(shù)字項(xiàng)的個(gè)數(shù)。語法COUNT(value1,value2, .)56函數(shù) COUNT 在計(jì)數(shù)時(shí)，將把數(shù)字、空值、邏輯值、日期或以文字代表的數(shù)計(jì)算進(jìn)去；但是錯(cuò)誤值或其他無法轉(zhuǎn)化成數(shù)字的文字那么被忽略。所以，COUNT的單純運(yùn)用很少，普通都會(huì)選擇COUNTIF公式，并和IF套用。CO

2、UNT例子=Count(data)COUNTIF數(shù)字和三角函數(shù)，計(jì)算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目。語法COUNTIF(range,criteria) Range 為需求計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria 為確定哪些單元格將被計(jì)算在內(nèi)的條件，其方式可以為數(shù)字、表達(dá)式或文本78上述公式，我們可以看出，COUNTIF是作為IF語句的判別條件出現(xiàn)的。它表示計(jì)算C2到F2區(qū)域內(nèi)值為“1的單元格個(gè)數(shù)。COUNTIF例子=IF(COUNTIF(C2:F2,1)=0,0,(ROUND(B2/COUNTIF(C2:F2,1),2)公式IFIf是邏輯函數(shù)，執(zhí)行真假值判別，指定要執(zhí)行

3、的邏輯檢測(cè)，根據(jù)邏輯測(cè)試的真假值前往不同的結(jié)果。語法 IF(logical_test,value_if_true,value_if_false) 函數(shù) IF 可以嵌套七層假設(shè)要計(jì)算單元格區(qū)域中某個(gè)文本串或數(shù)字出現(xiàn)的次數(shù)，那么可運(yùn)用COUNTIF任務(wù)表函數(shù)。 910IF(B2C2,超越預(yù)算,OK) 判別當(dāng)B2單元格的值大于C2單元格時(shí)，提示超出預(yù)算。IF(B260,“及格,IF(70B280,“中,IF(80B2=90,“優(yōu),“不及格) 三層判別，第二個(gè)IF語句是第一個(gè)IF語句的“假值。實(shí)例如下：IF例子：普通及嵌套的運(yùn)用11如上兩公式：公式一選擇了嵌套SUM公式；公式二選擇了嵌套COUNTIF

4、及ROUND兩個(gè)公式。公式一是經(jīng)過判別王一在3天里每天能否有消費(fèi)，消費(fèi)的總額是多少；而公式二是經(jīng)過判別在某一天能否有人消費(fèi)，假設(shè)有，算出人均消費(fèi)額度，并取小數(shù)點(diǎn)后兩位保管。其中，值“1表示該人能否消費(fèi)，表格從A1單元格開場(chǎng)陳列。IF例子：以其他公式為參數(shù)=IF(C2,B2/SUM(C2:F2),0)+IF(C3,B3/SUM(C3:F3),0)+IF(C4,B4/SUM(C4:F4),0)=IF(COUNTIF(C2:F2,1)=0,0,(ROUND(B2/COUNTIF(C2:F2,1),2)公式一公式二HLOOKUP在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此前往表格或數(shù)組當(dāng)前列中指定行處

5、的數(shù)值。語法構(gòu)造：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) lookup_value：需求在數(shù)據(jù)表第一行中進(jìn)展查找的數(shù)值 。table_array ：為需求在其中查找數(shù)據(jù)的數(shù)據(jù)表 。row_index_num：為 table_array中待前往的匹配值的行序號(hào)。 range_lookup：為一邏輯值，指明函數(shù) HLOOKUP 查找時(shí)是準(zhǔn)確匹配，還是近似匹配。假設(shè)為 TRUE 或省略，那么前往近似匹配值。 1213上述公式是指針對(duì)B2單元格的數(shù)值$21566,在E1：J3區(qū)域范圍內(nèi)搜索出近似的值由于range_loo

6、kup 參數(shù)被忽略，找到后顯示E1：J3區(qū)域表中第三行的對(duì)應(yīng)值。HLOOKUP例子=HLOOKUPB2，E1：J3，3VLOOKUP在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此前往表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。語法構(gòu)造：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) lookup_value：需求在數(shù)據(jù)表第一列中進(jìn)展查找的數(shù)值 。table_array ：為需求在其中查找數(shù)據(jù)的數(shù)據(jù)表 。col_index_num：為 table_array中待前往的匹配值的列序號(hào)。 range_lookup：為一邏輯值，指明函數(shù) V

7、LOOKUP 查找時(shí)是準(zhǔn)確匹配，還是近似匹配。假設(shè)為 TRUE 或省略，那么前往近似匹配值。 1415上述例子是一個(gè)收入稅率表。可以看到不同的收入等級(jí)的收入稅率。公式是指針對(duì)B2單元格的數(shù)值$21566,在D2：F7區(qū)域范圍內(nèi)搜索出近似的值由于range_lookup 參數(shù)被忽略，找到后顯示D2：F7區(qū)域表中第三列的對(duì)應(yīng)值VLOOKUP例子=VLOOKUPB2，D2：F7，3HLOOKUP及VLOOKUP當(dāng)比較值位于數(shù)據(jù)表的首行，并且要查找下面給定行中的數(shù)據(jù)時(shí)，請(qǐng)運(yùn)用函數(shù) HLOOKUP。當(dāng)比較值位于要進(jìn)展數(shù)據(jù)查找的左邊一列時(shí)，請(qǐng)運(yùn)用函數(shù)VLOOKUP。假設(shè) (row)col_index_nu

8、m 小于 1，函數(shù) (H)VLOOKUP 前往錯(cuò)誤值 #VALUE!；假設(shè) (row)col_index_num 大于 table_array 的列數(shù)，函數(shù) (H)VLOOKUP 前往錯(cuò)誤值 #REF!。 16本卷須知及文件間的操作以下案例均直接由EXCEL中復(fù)制粘貼，可在PPT中雙擊調(diào)用EXCEL格式編輯，相關(guān)公式可由編輯欄直接查看。由于WORD、EXCEL及PPT是OFFICE組件，它們之間的調(diào)用很方便，兩種方式：同上所說，直接復(fù)制粘貼就可以了，還能堅(jiān)持用原格式再次嵌套編輯；選擇“插入-“對(duì)象，在對(duì)話框中選擇“由文件創(chuàng)建，不選中“顯示為圖標(biāo)選項(xiàng)即可。方法可適用于上述工具任一種。17公式運(yùn)用

9、小技巧當(dāng)同一列行一切單元格運(yùn)用的公式一樣，可以運(yùn)用以下兩種快捷方式：運(yùn)用拖動(dòng)柄，在完成首行列單元格公式后選中右下角的拖動(dòng)柄，填充所要運(yùn)用公式的一切單元格。運(yùn)用EXCEL自帶的記憶功能。在第一個(gè)單元格完成公式后，填充第二個(gè)單元格，再以第二個(gè)單元格填充第三個(gè)，以第三個(gè)填充第四個(gè)，到第五個(gè)開場(chǎng)就無須做任何操作，公式對(duì)應(yīng)的值一出現(xiàn)，第五個(gè)單元格自動(dòng)填充。這種方式適用于復(fù)雜公式及大數(shù)據(jù)量。以免上種方式由于操作失誤呵斥漏項(xiàng)。18 EXCEL鍵盤運(yùn)用技巧快捷鍵19快捷鍵箭頭鍵+ CTRL - “選擇框挪動(dòng)到當(dāng)前數(shù)據(jù)區(qū)域的邊緣F11 或 ALT+F1 -“運(yùn)用當(dāng)前區(qū)域的數(shù)據(jù)創(chuàng)建圖表CTRL+END - “挪動(dòng)

10、到任務(wù)表的最后一個(gè)單元格，這個(gè)單元格位于數(shù)據(jù)區(qū)的最右列和最底行的交叉處右下角；或是與首單元格普通為 A1相對(duì)的單元格。 ALT+ENTER - “在單元格中折行CTRL+ENTER - “用當(dāng)前輸入項(xiàng)填充選定的單元格區(qū)域,適用于在選定區(qū)域或單元格內(nèi)需求輸入同一值時(shí)運(yùn)用。SHIFT+ENTER - “完成單元格輸入并在選定區(qū)域中上移 SHIFT+TAB - “完成單元格輸入并在選定區(qū)域中左移 CTRL+SHIFT+A -“在公式中鍵入函數(shù)名之后，無須翻開函數(shù)面板，自動(dòng)為該函數(shù)插入變量名和括號(hào)F4 -“在編寫公式中運(yùn)用F4可以為所選單元格添加固定符號(hào)$點(diǎn)擊單元格右下角的十字可以將當(dāng)前單元格的內(nèi)容延

11、續(xù)下去20EXCEL表間的鏈接運(yùn)用數(shù)據(jù)源鏈接等21方法要使多張表同時(shí)關(guān)聯(lián)，首先要確定源數(shù)據(jù)的位置；源數(shù)據(jù)位置一定，在其他任何需求和該數(shù)據(jù)值相關(guān)的位置上全部運(yùn)用調(diào)用公式。即用“=來直接調(diào)用該源數(shù)據(jù)或加上函數(shù)及參數(shù)，使該位置的數(shù)據(jù)可以針對(duì)源數(shù)據(jù)進(jìn)展相關(guān)處置。2223費(fèi)用總表中“本周已消費(fèi)列的值是從費(fèi)用明細(xì)表中“本周消費(fèi)小計(jì)行中讀取的。例如：王一的“本周已消費(fèi)公式為：=費(fèi)用明細(xì)表.xls費(fèi)用明細(xì)!$C$5可以把上兩表拷貝到不同EXCEL表中，留意都從A1單元格開場(chǎng)。當(dāng)翻開相關(guān)聯(lián)的任何一張表時(shí)可以同時(shí)關(guān)聯(lián)很多張表，會(huì)自動(dòng)彈出以下對(duì)話框。選擇是，表間的鏈接就建立了。例子費(fèi)用明細(xì)表費(fèi)用總表A1EXCEL

12、數(shù)據(jù)透視表的制造透視24透視表的特點(diǎn)一個(gè)數(shù)據(jù)透視表實(shí)踐上是一個(gè)產(chǎn)生于數(shù)據(jù)庫的動(dòng)態(tài)總結(jié)報(bào)告。數(shù)據(jù)透視表的最新概念是它的交互特性。最大的缺陷是在修正原數(shù)據(jù)時(shí)，數(shù)據(jù)透視表不會(huì)自動(dòng)更新。但可以單擊數(shù)據(jù)透視表工具欄上的“刷新按鈕迫使透視表運(yùn)用最新數(shù)據(jù)。25本卷須知不是一切的數(shù)據(jù)都可以創(chuàng)建數(shù)據(jù)透視表，所要總結(jié)的數(shù)據(jù)必需以數(shù)據(jù)庫的方式存在。當(dāng)然，我們可以把數(shù)據(jù)庫存儲(chǔ)在任務(wù)表中，即常說的列表。一個(gè)單獨(dú)的數(shù)據(jù)庫表格普通應(yīng)包含數(shù)據(jù)字段和類別字段。數(shù)據(jù)字段用于總結(jié)，類別字段的數(shù)值可以作為行、列或頁出如今數(shù)據(jù)透視表上。數(shù)據(jù)透視表的特性對(duì)不包含實(shí)踐值字段的數(shù)據(jù)庫也有效，可經(jīng)過方法而不是求和來總結(jié)數(shù)據(jù)透視表上的信息。26

13、制造步驟第一步：定義數(shù)據(jù)位置第二步：定義數(shù)據(jù)第三步：實(shí)現(xiàn)數(shù)據(jù)透視表運(yùn)用對(duì)話框規(guī)劃數(shù)據(jù)透視表運(yùn)用透視表字段列表工具欄規(guī)劃數(shù)據(jù)透視表完成數(shù)據(jù)透視表的定義2728 用于創(chuàng)建透視表的數(shù)據(jù)列表29選擇“數(shù)據(jù)-“數(shù)據(jù)透視表和數(shù)據(jù)透視圖第一步：定義數(shù)據(jù)位置30Excel列表或數(shù)據(jù)庫：存儲(chǔ)在任務(wù)表中的數(shù)據(jù)庫受不超越65535個(gè)記錄和256個(gè)字段的限制，第一行必包含字段稱號(hào)，數(shù)據(jù)可以是數(shù)值、文本或公式。外部數(shù)據(jù)源：運(yùn)用Query獲得數(shù)據(jù)。可以運(yùn)用dBASE文件、SQL Server數(shù)據(jù)或其他系統(tǒng)可以建立訪問的數(shù)據(jù)。多重合并計(jì)算數(shù)據(jù)區(qū)域：可以從多表格創(chuàng)建數(shù)據(jù)透視表，等價(jià)于合并表格中的信息。另一數(shù)據(jù)透視表或圖：從曾

14、經(jīng)存在的透視表運(yùn)用過的數(shù)據(jù)創(chuàng)建新透視表。注：從內(nèi)存運(yùn)用方面思索，如希望從同一組數(shù)據(jù)中創(chuàng)建多個(gè)透視表，最好先建立第一個(gè)透視表，然后把該透視表當(dāng)作后續(xù)透視表的數(shù)據(jù)源。導(dǎo)游之步驟一第二步：定義數(shù)據(jù)要轉(zhuǎn)移到導(dǎo)游下一步，單擊“下一步按鈕。導(dǎo)游二提示我們需求數(shù)據(jù)。但留意，對(duì)話框依賴于他在導(dǎo)游一上的選擇有所變化。從如今開場(chǎng)，我們分別經(jīng)過三個(gè)完好的創(chuàng)建案例來詳細(xì)了解選擇不同類型的效果及特點(diǎn)。3132在步驟一選擇該類型，步驟二為下面的對(duì)話框。注：上圖中選定區(qū)域已存在。緣由是在選擇創(chuàng)建數(shù)據(jù)透視表時(shí)，單元格指針指在了數(shù)據(jù)列表內(nèi)的任一位置，Excel自動(dòng)識(shí)別了原數(shù)據(jù)范圍。也可以運(yùn)用“閱讀按鈕翻開不同任務(wù)表并且選擇范圍

15、。例1：運(yùn)用Excel列表或數(shù)據(jù)庫33 第三步：實(shí)現(xiàn)數(shù)據(jù)透視表選擇“現(xiàn)有任務(wù)表選項(xiàng)，可以在下面的選擇范圍內(nèi)定義起始單元格位置；選擇“新建任務(wù)表選項(xiàng)，自動(dòng)插入一個(gè)新的任務(wù)表。注：從步驟2到步驟3時(shí)，假設(shè)該任務(wù)薄已存在數(shù)據(jù)透視表，會(huì)自動(dòng)彈出下面的提示框。選擇“是，自動(dòng)彈出透視表列表，選擇后確定才會(huì)出現(xiàn)步驟3的對(duì)話框。34 規(guī)劃數(shù)據(jù)透視表選擇“版式，出現(xiàn)版式對(duì)話框。關(guān)于規(guī)劃“版式對(duì)話框中右下角的選項(xiàng)是表單中的標(biāo)題欄稱號(hào)，可以用鼠標(biāo)點(diǎn)擊選中拖動(dòng)到左邊的圖示相應(yīng)位置，表單中各標(biāo)題下的數(shù)據(jù)就會(huì)自動(dòng)統(tǒng)計(jì)到該圖示對(duì)應(yīng)的單元格中。假設(shè)想改動(dòng)選項(xiàng)位置，用鼠標(biāo)在圖示中選中拖到圖示框外即可取消該項(xiàng)設(shè)置，然后把需求的選

16、項(xiàng)再次拖入就可以了。假設(shè)不想在該處調(diào)整版式，可以在確定數(shù)據(jù)透視表建立位置后直接點(diǎn)“完成，出現(xiàn)下頁中的工具欄。3536調(diào)整數(shù)據(jù)透視表設(shè)置同版式里方法一樣，用鼠標(biāo)選中工具欄上的各稱號(hào)，拖動(dòng)到相應(yīng)位置就可以了。假設(shè)生成表后還想調(diào)整，屏幕上又沒有顯示“數(shù)據(jù)透視表的工具欄，可以單擊“視圖-“工具欄-“數(shù)據(jù)透視表，即可。37根據(jù)需求確定選項(xiàng)后，點(diǎn)擊完成，就可以生成下面的數(shù)據(jù)透視表了。例：運(yùn)用上頁信息的透視表簡(jiǎn)單數(shù)據(jù)透視表38在步驟一選擇該類型，步驟二為下面的對(duì)話框。例2：運(yùn)用另一數(shù)據(jù)透視表注：上圖對(duì)話框中出現(xiàn)了兩個(gè)數(shù)據(jù)表項(xiàng)是EXCEL自動(dòng)記錄了當(dāng)前任務(wù)薄中曾經(jīng)存在的一切數(shù)據(jù)透視表稱號(hào)，并提供選擇。39 第

17、三步：實(shí)現(xiàn)數(shù)據(jù)透視表這里我們選擇數(shù)據(jù)透視表1做為參照對(duì)象，選擇“下一步。選擇“現(xiàn)有任務(wù)表選項(xiàng)，可以在下面的選擇范圍內(nèi)定義起始單元格位置；選擇“新建任務(wù)表選項(xiàng)，自動(dòng)插入一個(gè)新的任務(wù)表。40根據(jù)需求確定選項(xiàng)后，點(diǎn)擊完成，就可以生成下面的數(shù)據(jù)透視表了。例：運(yùn)用上頁信息的透視表運(yùn)用頁字段的數(shù)據(jù)透視表這里我們添加了頁字段。運(yùn)用這種方式對(duì)于想對(duì)同一表單建立多張數(shù)據(jù)透視表很有益處，可以節(jié)約內(nèi)存資源，提高數(shù)據(jù)透視表分析的效率。41在步驟一選擇該類型，步驟二為下面的對(duì)話框。例3：多重合并計(jì)算數(shù)據(jù)區(qū)域缺省選擇，下一步出現(xiàn)右圖對(duì)話框，每選定一個(gè)區(qū)域后選擇添加，在所以區(qū)域里可以看見選擇了的區(qū)域位置及區(qū)域范圍。假設(shè)選錯(cuò)了，可以選擇后刪除。42第三步：實(shí)現(xiàn)數(shù)據(jù)透視表這步選擇同其他兩個(gè)例子一樣，我們都選擇新建任務(wù)表，然后完成。這里留意，多重合并最好不要在版式里調(diào)整選項(xiàng)，到數(shù)據(jù)透視表的工具欄中再做調(diào)整。43 數(shù)據(jù)透視表工具欄設(shè)置44上圖中出現(xiàn)的不再是單表單時(shí)的標(biāo)題欄稱號(hào)，而是行、列、值、頁。行和列指的是兩張表單中的一切標(biāo)題行稱號(hào)及第