excel實(shí)用技巧（鳳山書屋）

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

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

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

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

5、出下拉菜單，在其中即可選擇需要篩選的條件。取消自動篩選按上述重復(fù)。13陽山書屋c統(tǒng)計(jì)常用方法1.篩選 高級篩選。高級篩選中在原有區(qū)域顯示篩選結(jié)果和自動篩選差不多，一般用得較多的是將篩選結(jié)果復(fù)制到其他位置，這樣就能對篩選出來的結(jié)果進(jìn)行下一步的處理。14陽山書屋c統(tǒng)計(jì)常用方法右邊就是對這些區(qū)域進(jìn)行高級篩選，篩選的區(qū)域?yàn)樽筮吽牧?，篩選的條件為性別-男性。最后生成的結(jié)果如下：15陽山書屋c統(tǒng)計(jì)常用方法2.分類匯總 位置在數(shù)據(jù)-分類匯總里，前提條件是分類的項(xiàng)目必須經(jīng)過排序。例如下列數(shù)據(jù)里按性別分類排序，再做計(jì)數(shù)匯總：16陽山書屋c統(tǒng)計(jì)常用方法3.數(shù)據(jù)透視表 數(shù)據(jù)透視表為數(shù)據(jù)匯總及制表提供了一個(gè)極其方便的

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

7、書屋cExcel函數(shù)因?yàn)樵诤芏嘟滩闹械恼f法都說的很書面化，所以在這里盡量用通俗的語言來解釋常用函數(shù)的含義和用法，并舉例來說明。在日常工作中，我們不會用到所有分類的函數(shù)，而且在所用的分類的函數(shù)中，我們用到的也是很少的一部分，所以我們學(xué)習(xí)的目的是理解函數(shù)的思路。理解了函數(shù)的思路后，我們碰到新的函數(shù)也能迅速明白它的用法并運(yùn)用到實(shí)際工作中去。21陽山書屋cExcel函數(shù)算術(shù)運(yùn)算符算術(shù)運(yùn)算符含義示例+加2+3=5-減3-2=1*乘2*3=6/除10/2=5乘方43=64%百分比50%22陽山書屋cExcel函數(shù)文本運(yùn)算符“&” &可以把文本連接起來，例如A1&B1，可以把兩個(gè)單元格合并為一個(gè)文本。比較運(yùn)

8、算符比較運(yùn)算符含義示例=等于B1=C1，如果兩單元格相等，返回TURE，如果不相等，F(xiàn)ALSE大于略=大于等于略=小于等于略不等于略23陽山書屋cExcel函數(shù)引用運(yùn)算符引用運(yùn)算符含義示例：區(qū)域運(yùn)算符，計(jì)算一個(gè)區(qū)域之間的數(shù)據(jù)Sum(A1:D5),聯(lián)合運(yùn)算符，將多個(gè)引用結(jié)合起來Sum(A1:D5,E2:F7)空格交叉運(yùn)算符，計(jì)算多個(gè)引用的交叉區(qū)域Sum(A1:E5 B3:F7)運(yùn)算順序默認(rèn)的運(yùn)算順序是乘方乘除加減，相同優(yōu)先級的按從左到右的順序運(yùn)算，若要改變順序，可以加上圓括號。24陽山書屋cExcel函數(shù)單元格引用 一般常用的引用為相對引用，如=A1*2，優(yōu)點(diǎn)在于拖拉函數(shù)的時(shí)候?qū)?yīng)的引用能夠自動

9、變化； 絕對引用則是拖動函數(shù)填充時(shí)，不會自動變化：=$A$1*2; 混合引用則是在一個(gè)單元格中，既有相對引用，也有絕對引用，如：$A1(行變列不變) A$1(列變行不變) 輸入函數(shù)的方法：1.使用函數(shù)編輯欄 2.直接在單元格中輸入注意：函數(shù)中輸入的括號及標(biāo)點(diǎn)都必須使用英文格式25陽山書屋cExcel函數(shù)日常工作中常用的函數(shù)有：1.求和函數(shù)2.統(tǒng)計(jì)函數(shù)3.邏輯函數(shù)4.文本、日期與時(shí)間函數(shù)5.查詢和引用函數(shù)6.多層函數(shù)嵌套26陽山書屋cExcel函數(shù)1.求和函數(shù)sum()和sumif()函數(shù)求和函數(shù)平時(shí)用到的主要是sum()和sumif()函數(shù)。Sum()函數(shù)大家應(yīng)用的最多，這里就不多做介紹，它最

10、多支持30組數(shù)據(jù)相加。如果超過了30組數(shù)據(jù)也有個(gè)小竅門，你可以在外面再加一個(gè)括號，那么就相當(dāng)于把這些數(shù)據(jù)變成了一個(gè)組，那就可以進(jìn)行求和。27陽山書屋cExcel函數(shù)Sumif()函數(shù)是條件求和函數(shù)，比如說你要對一個(gè)區(qū)域數(shù)據(jù)滿足條件的進(jìn)行求和，那就可以用這個(gè)函數(shù)了。Sumif(條件范圍,條件,求和范圍)，在括號中隔開一定要用英文的逗號，否則無法識別。例如sumif(A1:A100,”1”,B1:B100)表示在A1到A100范圍內(nèi)大于1的條件下對應(yīng)的B1:B100范圍里求和，條件范圍和求和范圍一定要相互對應(yīng)。28陽山書屋cExcel函數(shù)2.統(tǒng)計(jì)函數(shù)count()、counta()、countif

11、()和sumproduct()Count()函數(shù)對數(shù)字型的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，對文本、錯(cuò)誤值、無法轉(zhuǎn)換成數(shù)字的數(shù)據(jù)進(jìn)行忽略，使用方法為count(統(tǒng)計(jì)范圍)。例如count(A1:A100)表示對A1到A100范圍內(nèi)的所有數(shù)字型數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，如果中間有文本、錯(cuò)誤值等，則不進(jìn)行統(tǒng)計(jì)。29陽山書屋cExcel函數(shù)Counta()函數(shù)則是統(tǒng)計(jì)一個(gè)范圍內(nèi)的非空單元格，使用方法為counta(統(tǒng)計(jì)范圍)。例如counta(A1:A100)，表示統(tǒng)計(jì)A1到A100范圍內(nèi)的所有非空單元格，如果有單元格為空的，則不進(jìn)行統(tǒng)計(jì)，如果單元格里沒有任何數(shù)字文本，但是打了一個(gè)空格，函數(shù)仍然會進(jìn)行統(tǒng)計(jì)，所以這點(diǎn)一定要注意，很容

12、易造成錯(cuò)誤。30陽山書屋cExcel函數(shù)Countif()函數(shù)是條件統(tǒng)計(jì)函數(shù)，應(yīng)用的非常廣泛，很多報(bào)表都可以用它來自動生成，當(dāng)然這是運(yùn)用得比較熟練以后的事情，這里主要講基礎(chǔ)用法。用法Countif(統(tǒng)計(jì)范圍,統(tǒng)計(jì)條件)，例如countif(A1:A100,”1”)表示對A1到A100范圍內(nèi)所有大于1的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)。31陽山書屋cExcel函數(shù)Sumproduct()函數(shù)是多條件統(tǒng)計(jì)函數(shù)，可以對需要同時(shí)滿足多個(gè)條件的結(jié)果進(jìn)行統(tǒng)計(jì)，具體使用方式為： sumproduct(條件1)*(條件2)*(條件3)32陽山書屋cExcel函數(shù)3.邏輯函數(shù)if()、and()、or()和not()這幾個(gè)是日常工

13、作中用得比較多的邏輯函數(shù)，尤其是if()函數(shù)用得最多。用法if(條件,滿足條件返回的結(jié)果,不滿足條件返回的結(jié)果)，例如if(A1=60,”及格”,”不及格”)，表示如果A1的數(shù)值大于等于60，則返回及格，否則就返回不及格。(注意，函數(shù)中使用中文字符的時(shí)候一定要用英文冒號隔起來，否則無法識別)33陽山書屋cExcel函數(shù)這就是使用的實(shí)例34陽山書屋cExcel函數(shù)and()函數(shù)表示“和”的意思，or()函數(shù)表示“或者”的意思，這兩個(gè)函數(shù)極少單獨(dú)運(yùn)用，一般是和其他函數(shù)嵌套在一起，這里只做個(gè)大概的說明。例如if(and(A150,A160),”適合”,”不適合”)表示在A1的取值為50到60之間的情

14、況下，返回”適合”，其他情況下返回”不適合”。Not()函數(shù)表示對其他邏輯函數(shù)的值取反結(jié)果Excel函數(shù)最多允許嵌套七層。35陽山書屋cExcel函數(shù)4.文本、日期與時(shí)間函數(shù)常用的文本函數(shù)有l(wèi)eft(),right(),mid(),len(),int()等常用的日期與時(shí)間函數(shù)有now(),today(),year(),month(),day(),date(),hour(),date(),datedif()等。36陽山書屋cExcel函數(shù)Left()函數(shù)表示從單元格文本左邊開始取一定的位數(shù)。Left(取值的單元格,取值的位數(shù))，例如left(A1,2)表示從A1單元格中的文本中取前兩位。Righ

15、t()函數(shù)的用法和left()的用法類似，不過是從文本的右邊開始取值。37陽山書屋cExcel函數(shù)Mid()函數(shù)表示的是從單元格文本中從指定的位數(shù)開始取指定的長度。Mid(取值的單元格,開始的位數(shù),取值的長度)，例如mid(A1,3,2)表示從A1中文本的第三位開始，取兩位的文本。Len()函數(shù)表示取單元格中數(shù)據(jù)的長度。int()函數(shù)表示對單元格的數(shù)字取整數(shù)38陽山書屋cExcel函數(shù)Now()和today()表示的是電腦當(dāng)前的時(shí)間，兩者沒有太大的區(qū)別。Year()函數(shù)是取一個(gè)時(shí)間里的年份，例如year(2011-5-19)得出的結(jié)果就是2011。Month()函數(shù)是取一個(gè)時(shí)間里的月份，day

16、()函數(shù)是取一個(gè)時(shí)間里的天，hour()函數(shù)是取一個(gè)時(shí)間里的小時(shí)，用法和year()類似。39陽山書屋cExcel函數(shù)Date()函數(shù)是把幾個(gè)數(shù)字生成一個(gè)時(shí)間，用法為date(年,月,日)，例如date(2008,9,9)最后生成的結(jié)果是2008-9-9.Datedif()函數(shù)是求兩個(gè)時(shí)間之間的差值，用法為datedif(開始時(shí)間,結(jié)束時(shí)間,時(shí)間計(jì)算方式)，其中時(shí)間計(jì)算方式有年、月、日、一年中相差的月份、一月中相差的天數(shù)這五種方式。40陽山書屋cExcel函數(shù)例如datedif(2008-5-6,2010-5-8,”Y”)最后的結(jié)果就是2，表示兩個(gè)時(shí)間之間的差為兩年。 datedif(2008

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