EXCEL實用技巧培訓課件.ppt

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