1、1,Excel常用技巧 行政管理部,2,Excel常用技巧,一、常用格式設(shè)置 二、統(tǒng)計常用方法 三、Excel函數(shù),3,常用格式設(shè)置,1.邊框及底紋 在excel中，表格都是已經(jīng)給定的，我們所做的就是在輸入數(shù)據(jù)后，在給定的表格外繪制邊框。選定區(qū)域后，單擊右鍵-設(shè)置單元格格式-邊框。根據(jù)所需要的邊框添加，或者進入格式-自動套用格式，使用喜歡的類型。 圖表的背景色可以在單元格格式-圖案中選擇，或者點擊工具欄上的填充按鈕，選擇需要的顏色。,4,常用格式設(shè)置,2.合并及居中 當(dāng)需要對多個單元格進行合并時，可以將這些單元格選定后，點擊工具欄上的合并及居中按鈕，如需取消，再點擊一次即可。,5,常用格式設(shè)置

2、,3.對齊 在單元格設(shè)置-對齊里面，可以設(shè)置單元格的對齊方式、文字方向、自動換行等參數(shù)。（手動換行需要按Alt+Enter）,6,常用格式設(shè)置,4.數(shù)字格式 單元格格式-數(shù)字里面，可以設(shè)置單元格的格式，方便輸入各種數(shù)據(jù)。例如身份證號碼在單元格就會自動用科學(xué)計數(shù)法，最后幾位顯示不出來，這就需要把單元格設(shè)置成文本格式。,7,常用格式設(shè)置,5.頁面設(shè)置 頁面設(shè)置是對需要打印表格進行頁面、頁邊距、頁眉頁腳的設(shè)置，在文件-頁面設(shè)置里面，這些設(shè)置不用詳細講。對于打印范圍比較大，需要每頁設(shè)置標題的文檔，我們可以在頁面設(shè)置-工作表里點擊“頂端標題行”，將每頁需要添加的標題行選進去，打印出來的文檔每頁就自動加上

3、了標題行。,8,常用格式設(shè)置,6.數(shù)據(jù)有效性 點擊數(shù)據(jù)-有效性，可以為單元格設(shè)置有效的數(shù)據(jù)類型，默認的是任何值。我們可以將其進行改成我們所允許的值，在工作中就可以避免輸入錯誤。我們也可以對出錯提示自定義。,9,常用格式設(shè)置,7.條件格式 條件格式能夠在單元格條件滿足設(shè)定時，自動變化單元格格式。點擊格式-條件格式進入設(shè)置，設(shè)定條件，在格式中選定滿足條件時單元格格式的變化。使用條件格式能夠及時為數(shù)據(jù)變化做出提醒。,10,常用格式設(shè)置,8.格式刷 格式刷能夠非常迅速地將已設(shè)定好的單元格格式進行批量復(fù)制，減少重復(fù)工作，提高效率。格式刷在常用工具欄里，使用時先選定被復(fù)制格式的單元格，點擊格式刷，然后在需

4、要復(fù)制的單元格拖動即可。,11,常用格式設(shè)置,9.通配符（?、*、） 通配符一般使用在搜索中，?表示一個任意字符，例如搜索“d?f”表示查找以d開頭f結(jié)尾的任意一個三字母的詞；*表示任意個任意字符，例如搜索“d*f”表示查找以d開頭，f結(jié)尾的任意一個詞，不限于三個字母；則用在搜索?和*本身上面，例如搜索“d?f”表示搜索搜索d?f這個詞。,12,統(tǒng)計常用方法,1.篩選（自動篩選、高級篩選） 2.分類匯總 3.數(shù)據(jù)透視表 4.函數(shù)統(tǒng)計（略）,13,統(tǒng)計常用方法,1.篩選 自動篩選。在數(shù)據(jù)輸入完成后，選定需要篩選的項目一行，點擊數(shù)據(jù)-篩選-自動篩選，選定 的項目就會多出下拉菜單，在其中即可選擇需要

5、篩選的條件。取消自動篩選按上述重復(fù)。,14,統(tǒng)計常用方法,1.篩選 高級篩選。高級篩選中在原有區(qū)域顯示篩選結(jié)果和自動篩選差不多，一般用得較多的是將篩選結(jié)果復(fù)制到其他位置，這樣就能對篩選出來的結(jié)果進行下一步的處理。,15,統(tǒng)計常用方法,右邊就是對這些區(qū)域進行高級篩選，篩選的區(qū)域為左邊四列，篩選的條件為性別-男性。最后生成的結(jié)果如下：,16,統(tǒng)計常用方法,2.分類匯總 位置在數(shù)據(jù)-分類匯總里，前提條件是分類的項目必須經(jīng)過排序。例如下列數(shù)據(jù)里按性別分類排序，再做計數(shù)匯總：,17,統(tǒng)計常用方法,3.數(shù)據(jù)透視表 數(shù)據(jù)透視表為數(shù)據(jù)匯總及制表提供了一個極其方便的拖拉操作方式，所有需要統(tǒng)計的字段直接在數(shù)據(jù)透視

6、表的操作界面中操作。,18,統(tǒng)計常用方法,為了避免破壞原數(shù)據(jù)，一般選擇新建工作表，在布局中直接拖拉需要統(tǒng)計的項目。例如統(tǒng)計不同部門的性別統(tǒng)計。結(jié)果如下：,19,統(tǒng)計常用方法,4.函數(shù)統(tǒng)計 函數(shù)統(tǒng)計放在后面的excel函數(shù)里進行講解。,20,Excel函數(shù),Excel中所提的函數(shù)其實是一些預(yù)定義的公式，它們使用一些稱為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進行計算。Excel函數(shù)一共有11類，分別是數(shù)據(jù)庫函數(shù)、日期與時間函數(shù)、工程函數(shù)、財務(wù)函數(shù)、信息函數(shù)、邏輯函數(shù)、查詢和引用函數(shù)、數(shù)學(xué)和三角函數(shù)、統(tǒng)計函數(shù)、文本函數(shù)以及用戶自定義函數(shù)。,21,Excel函數(shù),因為在很多教材中的說法都說的很書面化，所以在

7、這里盡量用通俗的語言來解釋常用函數(shù)的含義和用法，并舉例來說明。 在日常工作中，我們不會用到所有分類的函數(shù)，而且在所用的分類的函數(shù)中，我們用到的也是很少的一部分，所以我們學(xué)習(xí)的目的是理解函數(shù)的思路。理解了函數(shù)的思路后，我們碰到新的函數(shù)也能迅速明白它的用法并運用到實際工作中去。,22,Excel函數(shù),算術(shù)運算符,23,Excel函數(shù),文本運算符“ 混合引用則是在一個單元格中，既有相對引用，也有絕對引用，如：$A1(行變列不變) A$1(列變行不變) 輸入函數(shù)的方法：1.使用函數(shù)編輯欄 2.直接在單元格中輸入 注意：函數(shù)中輸入的括號及標點都必須使用英文格式,26,Excel函數(shù),日常工作中常用的函數(shù)

8、有： 1.求和函數(shù) 2.統(tǒng)計函數(shù) 3.邏輯函數(shù) 4.文本、日期與時間函數(shù) 5.查詢和引用函數(shù) 6.多層函數(shù)嵌套,27,Excel函數(shù),1.求和函數(shù)sum()和sumif()函數(shù) 求和函數(shù)平時用到的主要是sum()和sumif()函數(shù)。 Sum()函數(shù)大家應(yīng)用的最多，這里就不多做介紹，它最多支持30組數(shù)據(jù)相加。如果超過了30組數(shù)據(jù)也有個小竅門，你可以在外面再加一個括號，那么就相當(dāng)于把這些數(shù)據(jù)變成了一個組，那就可以進行求和。,28,Excel函數(shù),Sumif()函數(shù)是條件求和函數(shù)，比如說你要對一個區(qū)域數(shù)據(jù)滿足條件的進行求和，那就可以用這個函數(shù)了。 Sumif(條件范圍,條件,求和范圍)，在括號中隔

9、開一定要用英文的逗號，否則無法識別。例如sumif(A1:A100,”1”,B1:B100)表示在A1到A100范圍內(nèi)大于1的條件下對應(yīng)的B1:B100范圍里求和，條件范圍和求和范圍一定要相互對應(yīng)。,29,Excel函數(shù),2.統(tǒng)計函數(shù)count()、counta()、countif()和sumproduct() Count()函數(shù)對數(shù)字型的數(shù)據(jù)進行統(tǒng)計，對文本、錯誤值、無法轉(zhuǎn)換成數(shù)字的數(shù)據(jù)進行忽略，使用方法為count(統(tǒng)計范圍)。例如count(A1:A100)表示對A1到A100范圍內(nèi)的所有數(shù)字型數(shù)據(jù)進行統(tǒng)計，如果中間有文本、錯誤值等，則不進行統(tǒng)計。,30,Excel函數(shù),Counta()

10、函數(shù)則是統(tǒng)計一個范圍內(nèi)的非空單元格，使用方法為counta(統(tǒng)計范圍)。例如counta(A1:A100)，表示統(tǒng)計A1到A100范圍內(nèi)的所有非空單元格，如果有單元格為空的，則不進行統(tǒng)計，如果單元格里沒有任何數(shù)字文本，但是打了一個空格，函數(shù)仍然會進行統(tǒng)計，所以這點一定要注意，很容易造成錯誤。,31,Excel函數(shù),Countif()函數(shù)是條件統(tǒng)計函數(shù)，應(yīng)用的非常廣泛，很多報表都可以用它來自動生成，當(dāng)然這是運用得比較熟練以后的事情，這里主要講基礎(chǔ)用法。 用法Countif(統(tǒng)計范圍,統(tǒng)計條件)，例如countif(A1:A100,”1”)表示對A1到A100范圍內(nèi)所有大于1的數(shù)據(jù)進行統(tǒng)計。,32

11、,Excel函數(shù),Sumproduct()函數(shù)是多條件統(tǒng)計函數(shù)，可以對需要同時滿足多個條件的結(jié)果進行統(tǒng)計，具體使用方式為： sumproduct(條件1)*(條件2)*(條件3),33,Excel函數(shù),3.邏輯函數(shù)if()、and()、or()和not() 這幾個是日常工作中用得比較多的邏輯函數(shù)，尤其是if()函數(shù)用得最多。 用法if(條件,滿足條件返回的結(jié)果,不滿足條件返回的結(jié)果)，例如if(A1=60,”及格”,”不及格”)，表示如果A1的數(shù)值大于等于60，則返回及格，否則就返回不及格。(注意，函數(shù)中使用中文字符的時候一定要用英文冒號隔起來，否則無法識別),34,Excel函數(shù),這就是使用

12、的實例,35,Excel函數(shù),and()函數(shù)表示“和”的意思，or()函數(shù)表示“或者”的意思，這兩個函數(shù)極少單獨運用，一般是和其他函數(shù)嵌套在一起，這里只做個大概的說明。例如if(and(A150,A160),”適合”,”不適合”)表示在A1的取值為50到60之間的情況下，返回”適合”，其他情況下返回”不適合”。 Not()函數(shù)表示對其他邏輯函數(shù)的值取反結(jié)果 Excel函數(shù)最多允許嵌套七層。,36,Excel函數(shù),4.文本、日期與時間函數(shù) 常用的文本函數(shù)有l(wèi)eft(),right(),mid(),len(),int()等 常用的日期與時間函數(shù)有now(),today(),year(),month

13、(),day(),date(),hour(),date(),datedif()等。,37,Excel函數(shù),Left()函數(shù)表示從單元格文本左邊開始取一定的位數(shù)。Left(取值的單元格,取值的位數(shù))，例如left(A1,2)表示從A1單元格中的文本中取前兩位。 Right()函數(shù)的用法和left()的用法類似，不過是從文本的右邊開始取值。,38,Excel函數(shù),Mid()函數(shù)表示的是從單元格文本中從指定的位數(shù)開始取指定的長度。 Mid(取值的單元格,開始的位數(shù),取值的長度)，例如mid(A1,3,2)表示從A1中文本的第三位開始，取兩位的文本。 Len()函數(shù)表示取單元格中數(shù)據(jù)的長度。 int(

14、)函數(shù)表示對單元格的數(shù)字取整數(shù),39,Excel函數(shù),Now()和today()表示的是電腦當(dāng)前的時間，兩者沒有太大的區(qū)別。 Year()函數(shù)是取一個時間里的年份，例如year(2011-5-19)得出的結(jié)果就是2011。 Month()函數(shù)是取一個時間里的月份，day()函數(shù)是取一個時間里的天，hour()函數(shù)是取一個時間里的小時，用法和year()類似。,40,Excel函數(shù),Date()函數(shù)是把幾個數(shù)字生成一個時間，用法為date(年,月,日)，例如date(2008,9,9)最后生成的結(jié)果是2008-9-9. Datedif()函數(shù)是求兩個時間之間的差值，用法為datedif(開始時間

15、,結(jié)束時間,時間計算方式)，其中時間計算方式有年、月、日、一年中相差的月份、一月中相差的天數(shù)這五種方式。,41,Excel函數(shù),例如datedif(2008-5-6,2010-5-8,”Y”)最后的結(jié)果就是2，表示兩個時間之間的差為兩年。 datedif(2008-5-6,2010-5-8,”m”)最后的結(jié)果為24，表示兩個時間之間的差為24個月。 datedif(2008-5-6,2010-5-8,”d”)最后的結(jié)果為732，表示兩個時間之間的差為732天。 datedif(2008-5-6,2010-5-8,”ym”)最后的結(jié)果為0，表示兩個時間在一年中的差為0個月。 datedif(2008-5-6,2010-5-8,”md”)最后的結(jié)果為2，表示兩個時間在一個月中的差為2天。,42,Excel函數(shù),5.查詢和引用函數(shù)。vlookup() 函數(shù)在實際運用中比較多，一般結(jié)合數(shù)據(jù)庫來使用。所謂數(shù)據(jù)庫就是把所有的數(shù)據(jù)分門別類，全部定好格式集中到一起的就算是一個數(shù)據(jù)庫了。 Vlookup(要查找的值,查找區(qū)域,返回區(qū)域?qū)?yīng)的欄數(shù),是否模糊匹配),43,Excel函數(shù),此處G2單元格根據(jù)F2單元格的姓名使用Vlookup函數(shù)自動匹配了部門，在函數(shù)中，F(xiàn)2表示要查找的值，A2:D7表示查找區(qū)域，