Excel常用辦公技巧

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

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

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

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

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

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

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

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

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

VLOOKUP+COLUMN作用：用column代替vlookup第3個參數(shù)，使公式可向右拖動【COLUMN】查看所選擇的某一個單元格在第幾列如COLUMN(D3)查看第3行D列的單元格在第幾列，結(jié)果為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ū)域=條件),結(jié)果區(qū)域)語法：=LOOKUP(1,0/((條件區(qū)域1=條件1)*(條件區(qū)域2=條件2)),結(jié)果區(qū)域)5.

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

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

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

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

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

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

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

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

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

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

環(huán)比增長率

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

起始于：60

終止于：99