EXCEL實(shí)用技巧培訓(xùn)課件.ppt

實(shí)用技巧培訓(xùn),EXCEL2013,技巧一,排版篇,Excel中的顏色要怎么搭配，視覺(jué)上更能接受且區(qū)分度高？,首行、首列最深間行留白,三色原則,有必須重復(fù)信息的行，那么如果沒(méi)有制作色差的話很容易看走眼，而且會(huì)較快的眼睛疲勞。所以很簡(jiǎn)單的改動(dòng)就能讓你的表單顯得更為友好。,為什么是三色？,1、三色原則能最大范圍地適用“對(duì)比色配色方案“、”互補(bǔ)色配色方案“或者”鄰近色配色方案“。2、最主要的原因是省油墨。辦公室絕大部分企業(yè)都是黑白打印+雙面利用以節(jié)省成本。如果只有三色，即白色+2主色，另外2個(gè)主色會(huì)以不同灰度的形式印刷出來(lái)。,為什么要錯(cuò)行配色？,*以上內(nèi)容引用自知乎用戶ChaoticGood,上圖為蘋果iwork的numbers自帶樣式，餅圖一類的統(tǒng)計(jì)圖用色系根據(jù)明度遞減做配色；表單依舊是三色配色方案。,1、全選（Ctrl+A）需要處理的數(shù)據(jù),2、開始-套用表格格式,強(qiáng)烈建使用EXCEL的最新版本，目前最新是2013版,（因?yàn)樗哪０逭娴暮軓?qiáng)大、很省事?。?做完兩行請(qǐng)使用格式刷,不使用模板？你想要自己做？也可以！,【樣式】-【套用表格格式】-【新建表格樣式】-設(shè)置【第一行條紋】【第二行條紋】顏色-【套用表格樣式】其中，【第一行條紋】為偶數(shù)行（包含標(biāo)題行算起，即第二行），【第二行條紋】為奇數(shù)行。如果不設(shè)置標(biāo)題行則反之。點(diǎn)擊后選【格式】，然后設(shè)置填充顏色即可。,方法一,方法二,技巧二,操作篇,Alt+Enter，表格內(nèi)換行,1,有時(shí)收到同事的工作總結(jié),我們手賤，調(diào)整了下列寬,錯(cuò)誤案例,正確案例,使用“空格”調(diào)整格式的,使用“Alt+Enter”調(diào)整格式的,F4，重復(fù)上一步操作,2,插入行,神技能！,節(jié)省操作步驟,刪除列,字體加粗,填充背景色,（分號(hào)后面那個(gè)）,3,主要解決的問(wèn)題,錄入身份證號(hào)，變成科學(xué)計(jì)算法錄入“0”開頭的編號(hào)，自動(dòng)省略0,當(dāng)然，也可以通過(guò)設(shè)置單元格格式來(lái)解決這個(gè)問(wèn)題,注意要使用英文狀態(tài)下的輸入法，否則EXCEL無(wú)法識(shí)別,復(fù)制，選擇性粘貼,4,方式一,方式二,復(fù)制完后，點(diǎn)左上角粘貼,粘貼后，點(diǎn)右下角圖標(biāo),分列,5,做微信運(yùn)營(yíng)的同事后臺(tái)導(dǎo)出的活動(dòng)數(shù)據(jù)經(jīng)常是這樣的,為什么分隔符號(hào)里明明有逗號(hào)，EXCEL卻無(wú)法識(shí)別，而需要在其他那里輸，才可以呢？答案是：逗號(hào)也有英文和中文的區(qū)別，EXCEl只能識(shí)別英文狀態(tài)下的逗號(hào)。,經(jīng)過(guò)分列處理后,凍結(jié)單元格,6,主要用途就是在移動(dòng)表格時(shí)，方便能看到行表頭或列表頭,技巧三,公式篇,想要固定行或列，就在公式中給“$”！按F4也有同樣效果（此符號(hào)僅在填充單元格復(fù)制公式時(shí)適用）,$單元格絕對(duì)引用,1,未設(shè)置單元格絕對(duì)引用時(shí)，設(shè)置D2=B2+C2，往下填充公式，結(jié)果默認(rèn)：D3=B3+C3D4=B4+C4,加“$”后再進(jìn)行填充,D3=B3+CD4=B4+C,想要固定加同一個(gè)數(shù)作為獎(jiǎng)金,這幾個(gè)公式完全可以一起學(xué)！,Ifcountifsumifcountifssumifs,2,if,countif,countifs,sumif,sumifs,條件計(jì)數(shù),條件求和,邏輯判斷,判斷是否滿足某個(gè)條件，如果滿足返回一個(gè)值，如果不滿足則返回另一個(gè)值,計(jì)算某個(gè)區(qū)域中滿足給定條件的單元格數(shù)目,統(tǒng)計(jì)一組給定條件所指定的單元格數(shù),對(duì)滿足條件的單元格求和,對(duì)一組給定指定的單元格求和,在D2設(shè)置公式，然后進(jìn)行填充后，即可以獲得下列結(jié)果,實(shí)際案例：遲到3次以下，即可獲得全勤獎(jiǎng)；否則，只有0元,if,公式含義：統(tǒng)計(jì)C列不為0的數(shù)量。注意：如果是不等于某個(gè)數(shù)字，或表示大于、小于等關(guān)系時(shí)，公式中需要加“”,實(shí)際案例：統(tǒng)計(jì)遲到人數(shù),countif,公式含義：統(tǒng)計(jì)C列不為0，且B列大于5000的行的數(shù)量。,實(shí)際案例：統(tǒng)計(jì)工資5000元以上的遲到人數(shù),countifs,公式含義：如果C列的值為0，則將B列的值進(jìn)行求和,實(shí)際案例：統(tǒng)計(jì)未遲到人員的工資總額,sumif,以上的例子舉得很無(wú)聊，誰(shuí)有事沒(méi)事拿工資說(shuō)事？！目的是為了幫助大家理解公式,下面介紹的這個(gè)函數(shù)，簡(jiǎn)直就是神器！,Vlookup，數(shù)據(jù)匹配,3,表一,行政部MM給了我兩張表，說(shuō)要將離職人員從通訊錄中刪除,行政部MM給了我兩張表，說(shuō)要將離職人員從通訊錄中刪除,表二,選中單元格,公式,插入函數(shù),vlookup,確定,5,1,2,3,4,不理解，沒(méi)關(guān)系，記住最后兩項(xiàng)，填1和0就行了！,需要查詢和匹配的內(nèi)容（例如表1通訊錄的A列）,需要?jiǎng)h除的值（例如表2離職人員的A列）,數(shù)字1表示返回表2中第“1”列的值,數(shù)字0表示精確匹配,最終得出來(lái)的結(jié)果就是這樣子的！再應(yīng)用“篩選”或是“排序”就能夠很輕松地將離職人員刪除,最典型的應(yīng)用場(chǎng)景：從表A的數(shù)據(jù)中找出表B的數(shù)據(jù),&，可在目標(biāo)單元格后面增加某些字符,4,假設(shè)我們公司會(huì)給員工下發(fā)個(gè)工資短信（實(shí)際不會(huì)發(fā)），內(nèi)容是：*，你本月的工資是*元，全勤獎(jiǎng)是*元，總計(jì)*元。,技巧四,數(shù)據(jù)透視表,數(shù)據(jù)透視表最適合做這種“分類匯總計(jì)算、求和”的活,這是典型應(yīng)用場(chǎng)景,為了簡(jiǎn)單說(shuō)明問(wèn)題，拿出一小部分?jǐn)?shù)據(jù)來(lái)做演示,這才是原始數(shù)據(jù),勾選需要分析的字段，并扔到區(qū)域中,注意勾選相應(yīng)類別，及調(diào)整行、列、值的選項(xiàng),更改計(jì)數(shù)類別，可以右鍵單擊計(jì)數(shù)項(xiàng)-值匯總依據(jù),除了兩個(gè)維度的數(shù)據(jù)透視表，還可以做三個(gè)維度、四個(gè)維度的數(shù)據(jù)透視表。大家感興趣可以自己試一試，原理類似。,一個(gè)數(shù)據(jù)透視表頂很多個(gè)公式?。?技巧五,下拉菜單設(shè)計(jì),有時(shí)，我們提供表格給別的同事填寫的時(shí)候，希望限定單元格內(nèi)可填寫的內(nèi)容，例如只能按我們規(guī)定的選項(xiàng)來(lái)填?；蛘呤亲约禾顚憰r(shí)，圖省事只希望有那么幾個(gè)選項(xiàng)。這時(shí)候下拉菜單就非常有用了！,記住這里如果是手輸文字選項(xiàng)的話，一定要用英文狀態(tài)下的逗號(hào)分隔開,還有一些高階用法，大家可以自己鉆研一下，篇幅關(guān)系不展開講了。,在用戶輸入內(nèi)容時(shí)，給出提示或警告，可以在以下兩個(gè)選項(xiàng)中進(jìn)行設(shè)置,三級(jí)下拉菜單：最典型的用法莫過(guò)于，在一級(jí)菜單“省”選了省后，二級(jí)菜單“市”的選項(xiàng)也能根據(jù)省的情況進(jìn)行相應(yīng)的改變，需要用到Indirect函數(shù),技巧六,“宏”的使用,當(dāng)你把機(jī)械化的一套操作通過(guò)錄制宏實(shí)現(xiàn)，眼看表格自動(dòng)化操作，在兩秒內(nèi)給你返回原來(lái)每天固定要做十幾分鐘的數(shù)據(jù)分析結(jié)果時(shí)，那個(gè)雞皮疙瘩,當(dāng)你學(xué)會(huì)了前面介紹的所有的基本操作，仍不滿足。當(dāng)你每天有很多重復(fù)的、機(jī)械的、簡(jiǎn)單的操作。恭喜你！宏是可以滿足你的。,宏的應(yīng)用場(chǎng)景,宏的定義,Excel中的宏，指的就是實(shí)現(xiàn)單一功能或復(fù)雜功能的VBA代碼的集合。使用宏功能，可以讓我們?cè)谔幚黼娮颖砀竦臅r(shí)候，僅通過(guò)一步的操作，就能實(shí)現(xiàn)需要通過(guò)多步才能完成的操作。,還是以一個(gè)實(shí)際例子好理解一點(diǎn)：假設(shè)我們要處理多個(gè)類似的表格，例如對(duì)表格的數(shù)據(jù)進(jìn)行美化，我們一般拿到的未經(jīng)處理的數(shù)據(jù)是這樣子的,1,錄制宏路徑：視圖宏錄制宏,2,定義宏的內(nèi)容,這里要選擇保留在個(gè)人宏工作簿,假設(shè)我們固定需要做的操作是：1、調(diào)整列寬至自動(dòng)適合；2、調(diào)整字體為微軟雅黑；3、設(shè)置表頭；4、去掉網(wǎng)絡(luò)線；5、設(shè)置灰色邊框,3,開始正常的操作在表1中，做以下操作1、調(diào)整列寬至自動(dòng)適合；2、調(diào)整字體為微軟雅黑；3、設(shè)置表頭；4