Excel常用辦公技巧

EXCEL“滿載而歸”excel工作常用技巧1235目錄CONTENTS常用快捷鍵數據格式及轉換常用公式舉例常用工具6學習交流4數據透視表淺析一、常用快捷鍵第一部分常用快捷鍵電腦操作：Win+D顯示桌面Win+E打開文件資源管理器Alt+Tab切換任務視圖Ctrl+Alt+Delete打開任務管理器Ctrl+F/H打開“查找”/“替換”對話框Ctrl+G打開“定位”對話框Alt+D再+P打開“數據透視表向導”對話框工作簿操作：Ctrl+S保存工作簿Ctrl+Z撤銷上一步操作Ctrl+Y恢復撤銷的操作CtrL+拖動工作表標簽復制工作表的副本移動和選擇：雙擊單元格邊框移動到工作表中當前數據區(qū)域的邊緣Shift+雙擊單元格邊框選取連續(xù)的單元格區(qū)域Shift+單擊選擇相鄰區(qū)域Ctrl+單擊選擇不相鄰的區(qū)域Alt+；（分號）只選取顯示的行，不選取隱藏行單元格操作：Shift+拖動單元格互換單元格內容Alt+向下箭頭根據已輸入過的內容自動生成下拉列表F4公式中循環(huán)切換絕對和相對引用F9查看公式中局部表達式的結果單元格格式：Ctrl+Shift+1設置為數值格式Ctrl+Shift+3設置為日期格式Ctrl+Shift+5設置為百分比格式批量填充：Ctrl+Enter向選定的單元格區(qū)域填充Ctrl+D向下復制填充Ctrl+R向右復制填充Ctrl+E快速智能填充Alt+=用SUM函數插入“自動求和”公式二、數據格式及轉換第二部分EXCEL數據類型數據類型：1.EXCEL中數值<文本<邏輯值，2.邏輯值可參與計算：FALSE=0

TRUE=13.日期和時間是特殊的數值，數值格式可隨意轉換數字格式類型顯示效果短日期格式2018/6/9長日期格式2018年6月9日常規(guī)格式43260數值格式43260.00貨幣格式￥43,260.00百分比4326000.00%文本格式43260日期和時間格式常規(guī)格式2018/6/94326011:170.470138889數字和文本的區(qū)別：1.在默認狀態(tài)下輸入數據，文本自動靠左對齊，數字靠右對齊2.數字可以進行數學運算，文本不能數字格式可隨意轉換顯示：文本轉化為數值：1.單列數據："數據“、”分列“、F2.多列數據：復制空白單元格，選定需轉換的數據區(qū)域，右鍵，選擇性粘貼（S），加（D）自定義數字格式及常見應用效果預覽更多數字格式內置類型調配數字格式設置單元格格式僅改變數字顯示形式不改變數字本身自定義數字格式及常見應用1.讓日期變得整整齊齊自定義數字格式yyyy-mm-dd數字格式顯示效果yyyy/mm/dd2017/05/20yyyy-mm-dd2017-05-20dd-mm-yy20-05-20yyyy"年"m"月"d"日"2017年5月20日aaaa星期六aaa周六d-mmm20-May2.數字單位不用輸入，直接“變”出來直接輸入提至標題數字格式顯示0.00”元”自定義格式中文本字符要用英文雙引號括起來自定義數字格式及常見應用3.自動補齊編號前面的0（僅改變顯示形式）實際輸入顯示形式非實際變化0000自定義數字格式類型4.手機號碼自動分段000-0000-0000自定義數字格式類型5.巧用數字格式規(guī)則標示數值升降[藍色]0.00↑;[紅色]-0.00↓自定義數字格式類型自定義數字格式的原理核心規(guī)則一：四類數據分別設置格式分號按順序劃分了4種類型的數據，每一格式只對相應的類型起作用正值;負值;零值;文本[藍色]0.00↑;[綠色]-0.00↓;[黑色]-;[紅色]核心規(guī)則二：占位符0和#的區(qū)別0：代表強制顯示，不管前面的數值還是0；例：代碼：“00000”。1234567顯示為1234567；123顯示為00123例：代碼：“00.000”。100.14顯示為100.140；1.1顯示為01.100#：只顯示有效數字而不顯示無意義的零。例：代碼：“###.##”。12.1顯示為12.1;12.1263顯示為：12.13例：代碼：“#，###”?！?0000”顯示為“10，000”核心規(guī)則三：文本型字符加英文雙引號例：代碼：“0.00”元”。“10”顯示為“10.00元”核心規(guī)則四：附加條件用英文中括號[]例：[紅色][<=100];[藍色][>100]例：[綠色][>=60]"及格"例：[=1]"男";[=0]"女"核心原理01020304三、常用公式舉例第三部分（一）、數據提取1.

TEXT作用：將數值轉換為按指定數字格式表示的文本語法：=TEXT(需轉換的值或單元格，文本形式的數字格式)單元格格式數字值說明Format_textValueTEXT(A,B)0000-00-00198205061982-05-06按所示形式表示日期0000年00月00日1982年05月06日aaaa2014/3/1星期六顯示為中文星期幾全稱"000.0"10.25010.3小數點前面不夠三位以0補齊，保留1位小數，不足一位以0補齊####10.0010沒用的0一律不顯示00.##1.25301.25小數點前不足兩位以0補齊，保留兩位小數，不足兩位不補位[>=90]優(yōu)秀；[>=60]及格；不及格90優(yōu)秀大于等于90，顯示為“優(yōu)秀”60及格大于等于60，小于90，顯示為“及格”59不及格小于60，顯示為“不及格”#!.0,萬元1.3萬元以萬元為單位，保留1位小數（一）、數據提取2.

MID作用：從一個字符串中截取出指定數量的字符語法：=MID(要被截取的字符,從左起第幾位開始截取，要向右截取的長度)3.

LEN/LENB作用：返回文本字符串中的字符/字節(jié)數語法：=Len(要查找其長度的文本)注：空格將作為字符進行計數補齊工號前面的0（實際改變）語法：=TEXT（單元格，“00000000”）（二）、時間計算1.

DATEDIF作用：返回兩個日期之間的年\月\日間隔數語法：=DATEDIF(開始日期，結束日期，返回類型）2.

EOMONTH作用：返回某個月份最后一天的序列號語法：=EOMONTH(開始日期,之前或之后的月份數)月份數為正值將生成未來日期；為負值將生成過去日期。3.

NETWORKDAYS作用：返回兩參數之間完整的工作日數值（不包括周末和專門指定的假期）語法：=NETWORKDAYS(開始日期，結束日期，可選假日區(qū)域）返回類型說明Y整年數M整月數D天數YD同年間隔天數，忽略年份YM同年間隔月數，忽略年份MD同月間隔天數，忽略年份和月份（三）、查找1.

VLOOKUP/HLOOKUP作用：按列/行查找語法：=VLOOKUP(要查找的唯一值，要查找的區(qū)域，數據在查找區(qū)域的第幾列數，精確匹配/近似匹配)2.

VLOOKUP+COLUMN作用：用column代替vlookup第3個參數，使公式可向右拖動【COLUMN】查看所選擇的某一個單元格在第幾列如COLUMN(D3)查看第3行D列的單元格在第幾列，結果為43.

VLOOKUP+IF{1,0}作用：逆向查找語法：=VLOOKUP(要查找的值,IF({1,0},查找的區(qū)域首列,查找的區(qū)域末列),2,0)=VLOOKUP($G3,$A$2:$B$6,COLUMN(B1),0)（三）、查找4.

LOOKUP正向/逆向查找語法：=LOOKUP(1,0/(條件區(qū)域=條件),結果區(qū)域)語法：=LOOKUP(1,0/((條件區(qū)域1=條件1)*(條件區(qū)域2=條件2)),結果區(qū)域)5.

LOOKUP多條件查找（四）、求和1.

SUMIF語法：=SUMIF(條件區(qū)域,指定的條件,求和區(qū)域)3.

SUMIF指定條件求和快捷鍵功能大于某值SUMIF(條件區(qū)域,">某數值")小于平均數SUMIF(條件區(qū)域,"<"&AVERAGE())模糊文本SUMIF(條件區(qū)域,"*文本*",求和區(qū)域)忽略錯誤值SUMIF(條件區(qū)域,"<9e307")2.

SUMIFS語法：=SUMIFS(求和區(qū)域，條件區(qū)域1,指定的條件1,條件區(qū)域2,指定的條件2)（五）、計數1.

COUNTIF語法：=COUNTIF(統計區(qū)域,指定的條件)3.

COUNTIF指定條件計數快捷鍵功能大于某值COUNTIF(條件區(qū)域,">某數值")大于某單元格COUNTIF(條件區(qū)域,">"&單元格)身份證重復COUNTIF(條件區(qū)域,單元格&"*")某列不重復SUMPRODUCT(1/COUNTIF(條件區(qū)域,條件區(qū)域))2.

COUNTIFS語法：=COUNTIFS(統計區(qū)域1,指定的條件1，統計區(qū)域2,指定的條件2)（六）、序號填寫1.

Ctrl+單元格右下角的填充柄向下拖動2.

ROW函數作用：返回所選擇的某一個單元格的行數。如果省略單元格參數，則默認返回ROW函數所在單元格的行數。3.按部門單獨填寫序號COUNTIF(B$2:B2,B2)4.篩選后保持連續(xù)的序號SUBTOTAL(3,B$1:B2)-1第四部分四、數據透視表淺析1.數據透視表基本操作創(chuàng)建數據透視表字段列表和匯總方法字段列表數據源中的所有列標題分類區(qū)域(篩選、行區(qū)域、列區(qū)域)要作為分類標簽，就將字段拖入此三個區(qū)域統計區(qū)域(值區(qū)域)添加至此的字段，才會進行計算和統計2.調整數據表的布局和美化-快速改變布局結構數據透視表工具設計選項卡數據透視表工具分析選項卡調整布局和外觀相關的功能大多在設計選項卡；調整匯總選項和數據的功能大多在分析選項卡。顯示所有分類匯總在組的底部顯示所有分類匯總分離二級分類單獨放在一列中以表格形式顯示底部顯示匯總行的效果分離二級分類后的效果2.調整數據表的布局和美化-快速調整報表的外觀設置字段的數字格式以“元”為單位以“萬元”為單位透視表中的數據過大，以萬為單位顯示數值會更加簡潔.選中任一數值；單擊【字段設置】按鈕；打開【數字格式】窗口；切換至自定義；輸入代碼。0!.0,英文逗號為千位分隔符，嘆號為轉義字符。代碼的含義是，用轉義字符強制將小數點顯示在千位之前，千位分割符后面的數值不顯示。結果就是以萬為單位顯示。修改字段名稱和標簽透視表中的文字標簽、字段名稱均可以直接修改。例如將分類匯總標簽修改成小計。直接輸入一個【小計】，按Enter鍵就能將所有同一級別的匯總標簽全部修改好修改文字標簽無法雙擊進入標記狀態(tài)，但可以直接輸入覆蓋，或者在編輯欄修改。Enter2.調整數據表的布局和美化-技巧1.多級分類，怎樣只保留第一級的分類匯總行2.透視表如何調出普通表格的效果右鍵單擊制定的級別標簽，就可以取消/顯示該級別匯總取消分類匯總；以表格形式顯示；重復所有項目標簽；取消行標題樣式；取消折疊按鈕。3.同類標簽如何合并居中重復顯示的標簽選項中有更多關于布局、格式及顯示效果的屬性配置合并且居中排列帶標簽的單元格合并且居中的標簽3.透視表技巧演示1、篩選篩選器切片器2、如何查看某個匯總數據背后的明細數據？3、修改數據源中的數據，怎樣使匯總結果同步更新？右鍵單擊數據透視表區(qū)域任一位置，選擇刷新4、數據源增添記錄，如何添加到數據透視表的匯總結果中？更改數據源轉智能報表，變成動態(tài)數據源5、每次刷新，列寬都變回默認狀態(tài)，怎樣固定？打開透視表設置窗口取消勾選更新時自動調整列寬6、利用數據透視表進行報表拆分。將分類添加到篩選頁點擊雙擊匯總數據單元格故對外提供保密數據時，務必注意將透視表粘貼為數值4.計算分析-不重計數&多種匯總方式如何查看表中有多少個人物出場，并且統計各個人物的出場次數？原理：不重復項其實就是分類項目；值區(qū)域會根據拖入的數據類型自動選擇匯總方式。數據類型默認匯總方式含義文本計數非空單元格的數量數值求和所有數值之和不重復計數一個字段變出多種匯總方式透視表中的值標簽，雙擊無法進入編輯狀態(tài)，因為雙擊標簽名稱后，會直接打開值字段設置窗口，在該窗口中，可以切換計算類型，從而修改表匯總的方式。2個要點：同一個字段可以多次拖入至區(qū)域；雙擊字段名可以快速切換匯總方式修改標簽和顯示單位以后的結果5.計算分析-百分比計算占總數的百分比

打開值字段設置窗口；切換至值顯示方式；選擇總計的百分比類型；確定。

環(huán)比增長率

7月環(huán)比增長率算式因為環(huán)比增長率和同比增長率都屬于差額計算，所以在百分比選項中，應選擇差異百分比，對于比例中的環(huán)比增長：基本字段：月基本項：上一個基本字段和基本項的含義是以“月”為分類，計算當前數據和上一個“月”數據的差異百分比。正好和環(huán)比增長率吻合。6.日期分組、數值分段統計事實上，只要日期、時間列中的數據符合系統標準格式，在將標準日期拖入分類字段區(qū)域時，就會立即分組。按季度、年、月等周期等自動分組日期自動分組周期（跨度）可以自定義嗎？可以，只需要修改【分組選擇】中的選項就行。系統日期格式只有年、月、日、時、分、秒的分組方式，如果需要用到按周分組，則需要自定義分組天數。首先選中任一個時間字段，單擊分組選擇，打開字段組合窗口。選擇一個日期標簽點擊分組選擇然后在組合窗口中執(zhí)行以下操作：取消“步長”一欄中的月、季度、年的默認分組；單擊選中日分組方式，輸入7天；單擊確定，便按7天一組重新分組，即以【周】分組。單擊取消各個默認分組單擊選擇日分組，自定義天數6.日期分組、數值分段統計和日期自定義分組方法類似，數值同樣可以設定區(qū)間步長，實現自動分段統計。以右表為例，按照以下分數區(qū)間，自動統計各個分數段的學生人數。按數值區(qū)間自動分組統計分段要求：<60為一組60~99以10分為1組100單獨一組步驟一：統計每個成績的人數按照統計需求描述和字段需求分析、分別將成績、姓名字段拖入行和值區(qū)域，先得到按成績分類的人數統計結果。步驟二：配置分組區(qū)間和步長選中任一個行標簽，例如77；在透視表工具欄中單擊分組選擇；填寫組合屬性如下：

起始于：60

終止于：99