excel實(shí)用技巧-文檔資料

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

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

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

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

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

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

7、言來(lái)解釋常用函數(shù)的含義和用法，并舉例來(lái)說(shuō)明。o 在日常工作中，我們不會(huì)用到所有分類的函數(shù)，而且在所用的分類的函數(shù)中，我們用到的也是很少的一部分，所以我們學(xué)習(xí)的目的是理解函數(shù)的思路。理解了函數(shù)的思路后，我們碰到新的函數(shù)也能迅速明白它的用法并運(yùn)用到實(shí)際工作中去。22Excel函數(shù)算術(shù)運(yùn)算符算術(shù)運(yùn)算符算術(shù)運(yùn)算符含義示例+加2+3=5-減3-2=1*乘2*3=6/除10/2=5乘方43=64%百分比50%23Excel函數(shù)o 文本運(yùn)算符文本運(yùn)算符“&” &可以把文本連接起來(lái)，例如A1&B1，可以把兩個(gè)單元格合并為一個(gè)文本。o 比較運(yùn)算符比較運(yùn)算符比較運(yùn)算符比較運(yùn)算符含義含義示例

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

9、函數(shù)的，優(yōu)點(diǎn)在于拖拉函數(shù)的時(shí)候?qū)?yīng)的引用能夠自動(dòng)變化；時(shí)候?qū)?yīng)的引用能夠自動(dòng)變化； 絕對(duì)引用則是拖動(dòng)函數(shù)填充時(shí)，不會(huì)自動(dòng)變化：絕對(duì)引用則是拖動(dòng)函數(shù)填充時(shí)，不會(huì)自動(dòng)變化：=$A$1*2; 混合引用則是在一個(gè)單元格中，既有相對(duì)引用，也有絕對(duì)引用，混合引用則是在一個(gè)單元格中，既有相對(duì)引用，也有絕對(duì)引用，如：如：$A1(行變列不變行變列不變) A$1(列變行不變列變行不變) 輸入函數(shù)的方法：輸入函數(shù)的方法：1.使用函數(shù)編輯欄使用函數(shù)編輯欄 2.直接在單元格中輸入直接在單元格中輸入注意：函數(shù)中輸入的括號(hào)及標(biāo)點(diǎn)都必須使用英文格式注意：函數(shù)中輸入的括號(hào)及標(biāo)點(diǎn)都必須使用英文格式26Excel函數(shù)日常工作中常用

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

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

12、ounta()函數(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ì)，如果單元格里沒(méi)有任何數(shù)字文本，但是打了一個(gè)空格，函數(shù)仍然會(huì)進(jìn)行統(tǒng)計(jì)，所以這點(diǎn)一定要注意，很容易造成錯(cuò)誤。31Excel函數(shù)o Countif()函數(shù)是條件統(tǒng)計(jì)函數(shù)，應(yīng)用的非常廣泛，很多報(bào)表都可以用它來(lái)自動(dòng)生成，當(dāng)然這是運(yùn)用得比較熟練以后的事情，這里主要講基礎(chǔ)用法。o 用法Countif(統(tǒng)計(jì)范圍,統(tǒng)計(jì)條件)，例如countif(A1:A100,”1”)表示對(duì)A1到A100范圍內(nèi)所有大于1的數(shù)據(jù)進(jìn)

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

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

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

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

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