Excel培訓(xùn)

—高級(jí)篇，杰出效率

版權(quán)所屬1培訓(xùn)提綱

Excel高級(jí)使用技巧

Excel函數(shù)使用技巧

Excel數(shù)據(jù)分析技巧

Excel圖表技巧2

Excel高級(jí)使用技巧構(gòu)思和設(shè)置表結(jié)構(gòu)建立和輸入數(shù)據(jù)編輯和修飾表格簡(jiǎn)單使用和分析保存表格數(shù)據(jù)打印輸出數(shù)據(jù)增加3

Excel高級(jí)使用技巧

-構(gòu)思及表結(jié)構(gòu)設(shè)置制作過程重點(diǎn)技巧構(gòu)思和設(shè)置結(jié)構(gòu)控制表格結(jié)構(gòu)的合理性數(shù)據(jù)項(xiàng)目拆（獨(dú)立列）與邏輯關(guān)系建立和輸入數(shù)據(jù)把握表格數(shù)據(jù)的準(zhǔn)確性輸入技巧與建立和復(fù)制公式關(guān)系編輯和修飾表格增強(qiáng)表格數(shù)據(jù)的可讀性編輯對(duì)象、數(shù)字、對(duì)齊條件格式等簡(jiǎn)單使用和分析有效運(yùn)用數(shù)據(jù)的分析性組織數(shù)據(jù)、創(chuàng)建、編輯和修飾圖表保存表格數(shù)據(jù)正確進(jìn)行文件歸檔保存保存、另存為及安全性打印輸出數(shù)據(jù)掌握表格數(shù)據(jù)打印輸出小表格、大表格及超表格的打印技巧4構(gòu)思及表結(jié)構(gòu)設(shè)置表結(jié)構(gòu)設(shè)置指北針凡須獨(dú)立管理的項(xiàng)目，必須單獨(dú)設(shè)置項(xiàng)目名稱，獨(dú)占一列，避免混合設(shè)置管理項(xiàng)目的獨(dú)立性必須與擴(kuò)展性結(jié)合考慮，以方便統(tǒng)計(jì)分析表格結(jié)構(gòu)設(shè)計(jì)必須考慮后期統(tǒng)計(jì)分析的應(yīng)用狀態(tài)。控制原始數(shù)據(jù)輸入準(zhǔn)確性的解決方法用調(diào)用法控制原始數(shù)據(jù)的準(zhǔn)確性用計(jì)算法控制加數(shù)據(jù)的準(zhǔn)確性使用校對(duì)工具5其他技巧-錄入技巧下拉菜單一、直接輸入：1.選擇要設(shè)置的單元格，譬如A1單元格；2.選擇菜單欄的“數(shù)據(jù)”→“有效性”→出現(xiàn)“數(shù)據(jù)有效性”彈出窗口；3.在“設(shè)置”選項(xiàng)中→“有效性條件”→“允許”中選擇“序列”→右邊的“忽略空值”和“提供下拉菜單”全部打勾→在“來源”下面輸入數(shù)據(jù)，譬如“1,2,3,4,5,6,7,8,9”(不包括雙引號(hào)，分割符號(hào)“,”必須為半角模式)→按“確定”就OK了，再次選擇該A1單元格，就出現(xiàn)了下拉菜單。6其他技巧-錄入技巧下拉菜單如果同一工作表的某列就是下拉菜單想要的數(shù)據(jù)，譬如引用工作表Sheet1的B2：B5，B2：B5分別有以下數(shù)據(jù)：1、2、3、4，操作如下：1.選擇要設(shè)置的單元格，譬如A1單元格；2.選擇菜單欄的“數(shù)據(jù)”→“有效性”→出現(xiàn)“數(shù)據(jù)有效性”彈出窗口；3.在“設(shè)置”選項(xiàng)中→“有效性條件”→“允許”中選擇“序列”→右邊的“忽略空值”和“提供下拉菜單”全部打勾→在“來源”下面輸入數(shù)據(jù)“=$B$2:$B$5”，也可以按右邊帶紅色箭頭的直接選擇B2：B5區(qū)域→按“確定”就OK了，再次選擇該A1單元格，就出現(xiàn)了下拉菜單。7其他技巧-定義工作表名稱1.CTRL+F32.=OFFSET(費(fèi)用明細(xì)!$A$1,,,COUNTA(費(fèi)用明細(xì)!$A:$A),COUNTA(費(fèi)用明細(xì)!$1:$1))8其他高級(jí)技巧功能命令選擇整個(gè)單元格范圍Ctrl+Shift+*快速關(guān)閉多個(gè)文件按住Shift鍵，打開“文件”菜單，單擊“全部關(guān)閉”選定多個(gè)工作表一組相鄰的工作表，可先選第一個(gè)表，按住Shift鍵，再單擊最后一個(gè)表的標(biāo)簽；選不相鄰的工作表，要按住Ctrl鍵對(duì)多個(gè)工作表快速編輯選定多個(gè)工作表，可在相同位置設(shè)置1.頁(yè)面設(shè)置2.輸入相同的數(shù)據(jù)3.快速設(shè)置4.輸入公式快速瀏覽長(zhǎng)工作簿按下“Ctri+Home”鍵可以回到當(dāng)前工作表的左上角(即A1單元格)，按下“Ctrl+End”鍵可以跳到工作表含有數(shù)據(jù)部分的右下角。同一個(gè)單元格換行Alt+回車鍵巧變文本為數(shù)字在空白的單元格中填人數(shù)字1，然后選中這個(gè)單元格，執(zhí)行“復(fù)制”命令，然后再選中所要轉(zhuǎn)換的范圍，選擇“選擇性粘貼”中的“乘”在單元格中輸入0值設(shè)置單元格格式”，在接著出現(xiàn)的界面中選“數(shù)字”標(biāo)簽頁(yè)，在列表框中選擇“文本”分散對(duì)齊在Excel表格中輸入人名時(shí)為了美觀，我們一般要在兩個(gè)字的人名中間空出一個(gè)字的間距,選中該列，點(diǎn)擊“格式→單元格→對(duì)齊”，在“水平對(duì)齊”中選擇“分散對(duì)齊文件加密另存新檔/工具/一般選項(xiàng)部分加密選取整篇文檔/右鍵儲(chǔ)存格格式/保護(hù)/解鎖/選區(qū)需要加密區(qū)域/右鍵儲(chǔ)存格格式/保護(hù)/加鎖/菜單工具/保護(hù)文檔9

Excel函數(shù)使用技巧Excel數(shù)學(xué)函數(shù)Excel邏輯函數(shù)Excel文本函數(shù)Excel日期時(shí)間函數(shù)Excel查詢與引用10函數(shù)類函數(shù)函數(shù)名稱常用否Excel的數(shù)學(xué)函數(shù)ABS返回參數(shù)的絕對(duì)值NCOUNTIF計(jì)算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目YINT返回實(shí)數(shù)舍入后的整數(shù)值NROUND返回某個(gè)數(shù)字按指定位數(shù)舍入后的數(shù)字YSUBTOTAL返回?cái)?shù)據(jù)清單或數(shù)據(jù)庫(kù)中的分類匯總NSUM返回某一單元格區(qū)域中所有數(shù)字之和NSUMIF根據(jù)指定條件對(duì)若干單元格求和Y

Excel函數(shù)使用技巧

-數(shù)學(xué)函數(shù)函數(shù)函數(shù)函數(shù)名稱顯示常用Excel邏輯函數(shù)COUNTIF根據(jù)指定條件對(duì)若干單元格次數(shù)COUNTIF(B4:B10,">90")YAND其中Logical1,logical2,...表示待檢測(cè)的1到30個(gè)條件值，各條件值可能為TRUE，可能為FALSE。參數(shù)必須是邏輯值，或者包含邏輯值的數(shù)組或引用AND(logical1,logical2,...)，YOROR函數(shù)指在其參數(shù)組中，任何一個(gè)參數(shù)邏輯值為TRUE，即返回TRUEOR(B1:B3)YIFIF函數(shù)用于執(zhí)行真假值判斷后，根據(jù)邏輯測(cè)試的真假值返回不同的結(jié)果，因此If函數(shù)也稱之為條件函數(shù)。它的應(yīng)用很廣泛，可以使用函數(shù)IF對(duì)數(shù)值和公式進(jìn)行條件檢測(cè)。IF(logical_test,value_if_true,value_if_false)。Y

Excel函數(shù)使用技巧

-邏輯函數(shù)函數(shù)類函數(shù)函數(shù)名稱顯示常用Excel函數(shù)引用之文本函數(shù)CONCATENATE將若干文字串合并到一個(gè)文字串中。CONCATENATE(text1,text2,...)YDOLLAR依照貨幣格式將小數(shù)四舍五入到指定的位數(shù)并轉(zhuǎn)換成文字。DOLLAR或RMB(number,decimals)

EXACT該函數(shù)測(cè)試兩個(gè)字符串是否完全相同。如果它們完全相同，則返回TRUE；否則，返回FALSE。函數(shù)EXACT能區(qū)分大小寫，但忽略格式上的差異。利用函數(shù)EXACT可以測(cè)試輸入文檔內(nèi)的文字。EXACT(text1,text2)

LEFTLEFT基于所指定的字符數(shù)返回文本串中的第一個(gè)或前幾個(gè)字符。LEFT(text,num_chars)YMIDMID返回文本串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。MID(text,start_num,num_chars)YRIGHTRIGHT根據(jù)所指定的字符數(shù)返回文本串中最后一個(gè)或多個(gè)字符。RIGHT(text,num_chars)YTRIM除了單詞之間的單個(gè)空格外，清除文本中所有的空格。在從其他應(yīng)用程序中獲取帶有不規(guī)則空格的文本時(shí)，可以使用函數(shù)TRIM。TRIM(text)YVALUE將代表數(shù)字的文字串轉(zhuǎn)換成數(shù)字。VALUE(text)

Excel函數(shù)使用技巧

-引用及文本函數(shù)函數(shù)類函數(shù)函數(shù)名稱顯示常用否Excel時(shí)間函數(shù)

MONTH返回以系列數(shù)表示的日期中的月份。月份是介于1（一月）和12（十二月）之間的整數(shù)。MONTH(serial_number)YTODAY返回當(dāng)前日期的系列數(shù)，系列數(shù)是MicrosoftExcel用于日期和時(shí)間計(jì)算的日期-時(shí)間代碼。TODAY()

YEAR返回某日期的年份。返回值為1900到9999之間的整數(shù)。YEAR(serial_number)Y

Excel函數(shù)使用技巧

-時(shí)間函數(shù)函數(shù)類函數(shù)函數(shù)名稱顯示常用否Excel查詢與引用函數(shù)VLOOKUP用于在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

YHLOOKUP用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

YTRANSPOSETRANSPOSE用于返回區(qū)域的轉(zhuǎn)置。函數(shù)TRANSPOSE必須在某個(gè)區(qū)域中以數(shù)組公式的形式輸入，該區(qū)域的行數(shù)和列數(shù)分別與array的列數(shù)和行數(shù)相同。使用函數(shù)TRANSPOSE可以改變工作表或宏表中數(shù)組的垂直或水平走向。由于此處是以數(shù)組公式輸入，因此需要按CRTL+SHIFT+ENTER組合鍵來確定為數(shù)組公式，此時(shí)會(huì)在公式中顯示"{}"。隨即轉(zhuǎn)置成功TRANSPOSE(array)

Excel函數(shù)使用技巧

-查詢及引用函數(shù)

Excel函數(shù)使用技巧

-函數(shù)案例講解1案例詳見插入EXCEL文檔函數(shù)類函數(shù)應(yīng)用常用否語法格式隱藏函數(shù)DATEDIF返回兩個(gè)日期之間的年\月\日YDATEDIF(起始日期,終止日期,"Y/M/D")NUMBERSTRING返回?cái)?shù)字的大小寫NNUMBERSTRING(數(shù)字,1/2)DATESTRING返回日期的中文NDATESTRING(日期)數(shù)學(xué)函數(shù)ABS返回參數(shù)的絕對(duì)值NABA(數(shù)字)COUNTIF計(jì)算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目YCOUNTIF(選擇列,篩選規(guī)則)ROUND返回某個(gè)數(shù)字按指定位數(shù)舍入后的數(shù)字YROUND(數(shù)字,保留位數(shù))SUMIF根據(jù)指定條件對(duì)若干單元格求和YSUMIF(選擇列,篩選規(guī)則,匯總計(jì)算列)邏輯引用AND同時(shí)設(shè)定條件，需滿足其中所有條件YAND(logical1,logical2,...)，OR同時(shí)設(shè)定條件，只滿足其中一項(xiàng)條件即可YIF條件函數(shù)YIF(條件,符合條件返回值,不符合條件返回值)。VLOOKUP用于在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。YVLOOKUP(參照列,選擇區(qū)域,返回第幾列的值，0/FALSE)16

Excel函數(shù)使用技巧

-函數(shù)案例講解2案例詳見插入EXCEL文檔函數(shù)類函數(shù)應(yīng)用常用否語法格式邏輯引用HLOOKUP用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。YHLOOKUP(參照行,選擇區(qū)域,返回第幾行的值，1/FALSE)RANK返回某一數(shù)值在一列數(shù)值中的相對(duì)于其他數(shù)值的排位YRANK（Number,ref,order）(ORDER0升序，order1降序）文本函數(shù)CONCATENATE將若干文字串合并到一個(gè)文字串中。NCONCATENATE(text1,text2,...)LEFTLEFT基于所指定的字符數(shù)返回文本串中的第一個(gè)或前幾個(gè)字符。YLEFT(text,num_chars)MIDMID返回文本串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。YMID(text,start_num,num_chars)RIGHTRIGHT根據(jù)所指定的字符數(shù)返回文本串中最后一個(gè)或多個(gè)字符。YRIGHT(text,num_chars)TRIM除了單詞之間的單個(gè)空格外，清除文本中所有的空格。在從其他應(yīng)用程序中獲取帶有不規(guī)則空格的文本時(shí)，可以使用函數(shù)TRIM。YTRIM(text)時(shí)間函數(shù)MONTH返回以系列數(shù)表示的日期中的月份。月份是介于1（一月）和12（十二月）之間的整數(shù)。YMONTH(serial_number)TODAY返回當(dāng)前日期的系列數(shù)，系列數(shù)是MicrosoftExcel用于日期和時(shí)間計(jì)算的日期-時(shí)間代碼。TODAY()YEAR返回某日期的年份。返回值為1900到9999之間的整數(shù)。YYEAR(serial_number)17Excel數(shù)據(jù)分析技巧Excel分級(jí)顯示及分類匯總Excel數(shù)據(jù)透析表18

Excel數(shù)據(jù)分析技巧

-分級(jí)顯示及分類匯總部門工號(hào)身份證號(hào)碼姓名應(yīng)納稅所得額稅率速算個(gè)人所得稅DSA00011AAA

10,000.000.20325

1,675.00DSA00012BBB

500.000.050

25.00DSA00013CCC

1,500.000.1025

125.00DSA00014DDD

3,800.000.15125

445.00DSA0匯總

15,800.00475

2,270.00GSA00015EEE

4,000.000.15125

475.00GSA00016FFF

800.000.1025

55.00GSA00017GGG

1,200.000.1025

95.00GSA00018HHH

650.000.1025

40.00GSA0匯總

6,650.00200

665.00總計(jì)

22,450.00675

2,935.0019數(shù)據(jù)透視表是Excel中最具技術(shù)性的復(fù)雜組件之一。數(shù)據(jù)透視表的本質(zhì)是將表格數(shù)據(jù)庫(kù)變?yōu)閯?dòng)態(tài)數(shù)據(jù)匯總報(bào)告。數(shù)據(jù)透視表的優(yōu)勢(shì)：人性化：方便的操作和界面靈活性：可以方便的得到任何類別的數(shù)據(jù)

Excel數(shù)據(jù)分析

-數(shù)據(jù)透析表-使用數(shù)據(jù)透視表分析數(shù)據(jù)20如何生成數(shù)據(jù)透視表下面我們以此數(shù)據(jù)來進(jìn)行數(shù)據(jù)透視表的分析。21為數(shù)據(jù)透視表選擇合適的數(shù)據(jù)雖然Excel可以對(duì)任何數(shù)據(jù)生成數(shù)據(jù)透視表，但并不是所有的數(shù)據(jù)資料都可以從中受益。一般而言，數(shù)據(jù)資料應(yīng)為數(shù)據(jù)庫(kù)格式，包含下面兩種類型的字段：數(shù)據(jù)（包括數(shù)字或文本）類別：對(duì)數(shù)據(jù)的描述數(shù)據(jù)的第一行要求為項(xiàng)目標(biāo)題。當(dāng)數(shù)據(jù)列不包含數(shù)字時(shí)數(shù)據(jù)透視表默認(rèn)為以計(jì)數(shù)的方式使用數(shù)據(jù)；當(dāng)數(shù)據(jù)列為數(shù)字時(shí)默認(rèn)為使用求和方式。22如何生成數(shù)據(jù)透視表Step1:選擇工具欄中的：數(shù)據(jù)數(shù)據(jù)透視表和數(shù)據(jù)透視圖。23如何生成數(shù)據(jù)透視表Step2:一般情況按照默認(rèn)的選項(xiàng)即可，單擊下一步。上部分選項(xiàng)選擇數(shù)據(jù)來自本Excel文件，或其它數(shù)據(jù)類型，或多個(gè)Excel數(shù)據(jù)文件。24如何生成數(shù)據(jù)透視表Step3:彈出對(duì)話框，選擇將要生成數(shù)據(jù)分析表的數(shù)據(jù)源。當(dāng)活動(dòng)單元格的位置在數(shù)據(jù)區(qū)域時(shí)，透視表會(huì)自動(dòng)選擇數(shù)據(jù)區(qū)域。25如何生成數(shù)據(jù)透視表Step4:選擇新建工作表，使數(shù)據(jù)透視表生成在一張新的工作表中，點(diǎn)擊“完成”。選擇“布局”和“選項(xiàng)”可以設(shè)置一些數(shù)據(jù)透視表的模式，但不是必需的，所以如果是新手入門就當(dāng)作沒有這些鍵好了，但是我們也分別解釋一下。26布局在“布局”對(duì)話框里，可以將透視表的各字段安排在不同的位置。但是這個(gè)步驟也可以在數(shù)據(jù)透視表建立后進(jìn)行。27選項(xiàng)數(shù)據(jù)透視表提供了豐富的“選項(xiàng)”。列總計(jì)：以列匯總；行總計(jì)：以行匯總；打開時(shí)刷新：打開文件時(shí)自動(dòng)進(jìn)行數(shù)據(jù)刷新；顯示明細(xì)數(shù)據(jù)：可以顯示每個(gè)數(shù)字對(duì)應(yīng)的數(shù)據(jù)庫(kù)中的數(shù)據(jù)；28如何生成數(shù)據(jù)透視表Step5:生成新的一頁(yè)工作表，并會(huì)彈出包含所有項(xiàng)目標(biāo)題的對(duì)話框。29如何生成數(shù)據(jù)透視表Step6:將項(xiàng)目標(biāo)題拖到左側(cè)表格的對(duì)應(yīng)位置，一張數(shù)據(jù)透視表就完成了。30如何讀取數(shù)據(jù)透視表項(xiàng)目選項(xiàng)：選擇不同組合下的數(shù)據(jù)。點(diǎn)擊右鍵，對(duì)交叉表進(jìn)行字段分析，如求和，計(jì)數(shù)，最大值等31數(shù)據(jù)透視表的組成部分列字段。如除主詳細(xì)特征；行字段。如time；數(shù)據(jù)區(qū)域。中間的數(shù)字；總數(shù)求和。如合計(jì)；元素。如個(gè)人；條件。如city;字段列表。32移動(dòng)在數(shù)據(jù)透視表中無法進(jìn)行普通的操作，例如插入或刪除一行或一列，也不能對(duì)數(shù)字進(jìn)行硬性修改。如何移動(dòng)數(shù)據(jù)透視表中的項(xiàng)目？將鼠標(biāo)移到想要移動(dòng)的項(xiàng)目，點(diǎn)右鍵，選擇“順序”，然后選擇想要移動(dòng)方向即可。注意只能移動(dòng)項(xiàng)目，而不能僅僅移動(dòng)數(shù)據(jù)。33手工組合組合項(xiàng)目是數(shù)據(jù)透視表的一個(gè)有用特性。如何將選項(xiàng)進(jìn)行組合？選擇要組合的元素，點(diǎn)擊右鍵，選擇“組合顯示明細(xì)數(shù)據(jù)”“組合”，出現(xiàn)新的項(xiàng)目名稱“city2”，將生成的“數(shù)據(jù)組1”改為“重點(diǎn)城市”。這樣就可以得到分“重點(diǎn)城市”和“其他城市”的數(shù)據(jù)。34手工組合35自動(dòng)組合當(dāng)被組合項(xiàng)為數(shù)字時(shí)，可以進(jìn)行自動(dòng)組合。例如日期可以自動(dòng)選擇按照月、季度、年等組合；收入，年齡等都可以按照自己指定的區(qū)間段進(jìn)行組合；當(dāng)進(jìn)行手工組合時(shí)會(huì)生成新的元素，如對(duì)“收入”進(jìn)行組合會(huì)生成“收入2”的元素；在進(jìn)行自動(dòng)組合時(shí)沒有新的元素生成，組合數(shù)據(jù)替代了詳細(xì)數(shù)據(jù)；因此建議在原數(shù)據(jù)庫(kù)中復(fù)制一列數(shù)據(jù)。36插入計(jì)算字段數(shù)據(jù)透視表有自我進(jìn)行計(jì)算的功能，這種功能叫做“插入計(jì)算字段”。例如在數(shù)據(jù)透視表中有銷售額和銷量的數(shù)據(jù)，則可以通過計(jì)算來得到單價(jià)的數(shù)據(jù)。步驟：“數(shù)據(jù)透視表工具欄””公式””計(jì)算字段”在“名稱”寫入新建數(shù)據(jù)的名稱；在“公式”內(nèi)鍵入計(jì)算公式；確定。37數(shù)據(jù)透視表的其他閱讀詳細(xì)數(shù)據(jù)對(duì)于數(shù)據(jù)透視表產(chǎn)生的數(shù)據(jù)可以查看詳細(xì)的數(shù)據(jù)產(chǎn)生來源，具體的方式是雙擊此數(shù)。例如數(shù)據(jù)透視表計(jì)算出庫(kù)中有3個(gè)樣本符合某條件，那么通過雙擊此數(shù)可以得到具體是哪3個(gè)樣本符合這個(gè)條件，并在新的工作表中顯示。在雙擊前請(qǐng)保證“表格選項(xiàng)”中的“顯示明細(xì)數(shù)據(jù)”已被選擇。設(shè)置報(bào)告格式如果需要直接把數(shù)據(jù)透視表作為報(bào)告提交，那么一個(gè)正規(guī)的格式是必須的，請(qǐng)?jiān)凇皵?shù)據(jù)透視表工作欄”中選擇“設(shè)置報(bào)告格式”，選擇合適的個(gè)是顯示數(shù)據(jù)透視表選擇顯示元素點(diǎn)擊數(shù)據(jù)透視表