Excel高級應用技巧

Excel培訓

—高級篇，杰出效率

版權所屬1培訓提綱

Excel高級使用技巧

Excel函數(shù)使用技巧

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

Excel圖表技巧2

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

Excel高級使用技巧

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

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

Excel函數(shù)使用技巧

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

Excel函數(shù)使用技巧

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

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

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

Excel函數(shù)使用技巧

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

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

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

Excel函數(shù)使用技巧

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

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

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

Excel函數(shù)使用技巧

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

Excel函數(shù)使用技巧

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

Excel函數(shù)使用技巧

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

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

-分級顯示及分類匯總部門工號身份證號碼姓名應納稅所得額稅率速算個人所得稅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總計

22,450.00675

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

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

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