EXCLE使用技巧講解ppt課件

1、EXCELEXCEL簡單技巧的使用簡單技巧的使用;目錄OFFICE的定義學(xué)習(xí)EXCEL意義及目的EXCEL目錄的應(yīng)用EXCEL常用函數(shù)EXCEL其他技巧總結(jié);官方定義：Microsoft Office是微軟公司開發(fā)的一套基于 Windows 操作系統(tǒng)的辦公軟件套裝。常用組件有 Word、Excel、Powerpoint等。民間定義：方便辦公的應(yīng)用軟件，性質(zhì)相當(dāng)于鐵鍬。能替代紙質(zhì)化辦公、提高數(shù)據(jù)處理能力、提高視覺效果。俗話說：工欲善其事，必先利其器。熟練的掌握常用的技巧，能提高工作效率。本次學(xué)習(xí)目的：1、掌握常用的方法和技巧；2、掌握學(xué)習(xí)不常用的方法和技巧的學(xué)習(xí)方法；;目錄的制作就是使用EXCE

2、L內(nèi)部的超鏈接功能。快捷鍵：Ctrl + k。;常用的目錄使用基本是兩種：第一種是EXCEL的外部鏈接；第二種是EXCEL的內(nèi)部鏈接。1、在當(dāng)前文件夾內(nèi)新建一個文本類型的文件所謂的.txt文件）。2、打開文件輸入“DIR /S /B 1.TXT”； DIR是DOS命令符意思為生產(chǎn)目錄； /S /B 是生成該文件夾內(nèi)所有文件及子文件。如果只用/B只生成該文件夾下的目錄，不生成子文件； 1.txt是生成的目錄輸出在名為1的文本中。3、保存該文件，把文件后綴名從.txt改為.bat。雙擊運行，這時系統(tǒng)就會新建一個名字為1的文本文件。4、打開文本文件如左圖目錄已生成；;1、把txt文檔內(nèi)的數(shù)據(jù)全部復(fù)制

3、到新建的Excel表格內(nèi)，發(fā)現(xiàn)內(nèi)容在A列內(nèi)，需要把目錄的內(nèi)容分開輸出到各個列，方便管理；2、選擇A列，點擊數(shù)據(jù)的選項卡，選擇分列；3、點擊出現(xiàn)選項卡的下一步，在第二頁中分隔符號選擇其他，在里面輸入“”后點擊下一步。點擊完成。就出現(xiàn)最有側(cè)的效果了。4、把不需要的存儲文件名刪除。例如：A列、B列中的數(shù)據(jù)可以進(jìn)行刪除；5、優(yōu)化格式。EXCEL的外部鏈接;按左圖的編號順序進(jìn)行建立超鏈接。1、選擇想建立超鏈接的內(nèi)容；2、Ctrl + k打開超鏈接編輯器；3、選擇“現(xiàn)有文件或網(wǎng)頁” 的選項卡；4、選擇“當(dāng)前文件夾最近使用過的文件夾）” 選項卡；5、選擇需要建立鏈接的文件夾；6、選擇需要建立鏈接的文件；7、

4、確定；8、測試。建立目錄的時候最好把目錄名稱與鏈接的名稱弄成一致。EXCEL的外部鏈接;EXCEL的內(nèi)部鏈接內(nèi)部鏈接適用與一個表格內(nèi)有多個工作薄比如說有幾十個，用鼠標(biāo)選擇工作量大還容易出錯）。按左圖的編號順序進(jìn)行建立超鏈接。1、選擇想建立超鏈接的內(nèi)容；2、Ctrl + k打開超鏈接編輯器；3、選擇“本文當(dāng)中的位置” 的選項卡；4、選擇需要建立鏈接的文件；5、確定；6、測試。外部鏈接與內(nèi)部鏈接如果需要批量實現(xiàn)鏈接需要使用Excel的宏功能，用VBA編程實現(xiàn)。雖然碰上目錄內(nèi)容或工作簿比較多的表格此方法依然笨重，但一次建立好后可以一勞永逸。尤其經(jīng)常用的表格不需要每次在找文件所在位置或工作薄的位置。;

5、EXCEL常用函數(shù)巧SUM及其相關(guān)應(yīng)用數(shù)組的應(yīng)用COUNT及其相關(guān)的應(yīng)用ROUND的應(yīng)用判斷語句（IF語句）VLOOKUP的應(yīng)用;SUM是Excel最常用的函數(shù)，沒有之一。基本用過Excel的人可能別的不會用，但這個公式一定會用。SUM公式與E15+F15+O15不同的是，SUM公式的意義是把其括號內(nèi)的參數(shù)進(jìn)行求和，包括數(shù)組。應(yīng)用最直接的方式就是把想進(jìn)行求和的內(nèi)容放到括號里，像上圖想計算E15至O15連續(xù)的和用“：”連接兩個參數(shù)就可以，如果想跳著計算用SUM(E15，G15，I15，O15)用“，”隔開。;關(guān)于SUMIF與SUMIFS的應(yīng)用1、SUMIF與SUMIFS都是求和函數(shù)，與SUM的區(qū)

6、別是參數(shù)新增了判斷語句。表示如果*符合*那么求和2、SUMIF（ range，criteria，sum_range ）： SUMIF條件區(qū)域，求和條件，實際求和區(qū)域），第二個求和條件參數(shù)在第一個條件區(qū)域里。3、SUMIFS（ sum_range， range1，criteria1 ， range10，criteria10）： SUMIFS實際求和區(qū)域，條件區(qū)域1，求和條件1，條件區(qū)域10，求和條件10 ），求和條件參數(shù)在第一個條件區(qū)域里。 4、注意SUMIF與SUMIFS參數(shù)順序正好相反。;關(guān)于SUMIF與SUMIFS的應(yīng)用本卷須知：1、 criteria求和條件參數(shù)在第一個range條件區(qū)域

7、里；2、參數(shù)criteria如果是文本，要加引號。且引號為英文狀態(tài)下輸入。3、參數(shù)criteria還可以使用通配符*。;關(guān)于SUMPRODUCT的應(yīng)用SUMPRODUCT實際上是乘積求和運算，SUMPRODUCTarray1,array2,array3, .）Array為數(shù)組。當(dāng)sumproduct函數(shù)中的參數(shù)只有一個數(shù)組時，即對數(shù)組1;2;3;4;5;6;7進(jìn)行求和，1+2+3+4+5+6+7=28。;關(guān)于SUMPRODUCT的應(yīng)用當(dāng)sumproduct函數(shù)中參數(shù)為兩個數(shù)組時，兩個數(shù)組的所有元素對應(yīng)相乘。公式=sumproductA2:A8,B2:B8可轉(zhuǎn)化為=sumproduct數(shù)組1，數(shù)

8、組2）=sumproduct（1；2；3；4；5；6；7，1；2；3；4；5；6；7）=1*1+2*2+3*3+4*4+5*5+6*6+7*7=140。;關(guān)于SUMPRODUCT的應(yīng)用當(dāng)sumproduct函數(shù)中參數(shù)為三個數(shù)組時，三個數(shù)組的所有元素對應(yīng)相乘。;關(guān)于SUMPRODUCT的應(yīng)用單條件求和統(tǒng)計九洲集團(tuán)后的數(shù)字。=sumproduct(A:A=“九洲集團(tuán))*1，(B:B)，這種寫法好像與之前看的函數(shù)參數(shù)不一樣，之前的參數(shù)傳的全是數(shù)字，這個里面穿的是文本。其實A:A=“九洲集團(tuán)是一個判斷語句，A列中的所有數(shù)據(jù)如果是對的則結(jié)果為TRUE,如果是錯的結(jié)果為FALSE。在計算機語言中TRUE的

9、結(jié)果為1，F(xiàn)ALSE的結(jié)果為0。我們這個表格中如果用數(shù)組表示則可以表示為TURE,FALSE, FALSE, FALSE, FALSE, FALSE, TURE,FALSE, FALSE, FALSE, FALSE, FALSE, TURE,FALSE, FALSE, FALSE, FALSE, FALSE , TURE,FALSE, FALSE, FALSE, FALSE, FALSE*1之后轉(zhuǎn)化為1,0,0,0,0,0, 1,0,0,0,0,0, 1,0,0,0,0,0, 1,0,0,0,0,0,。這是就跟上頁所說的數(shù)組一樣了。最終也轉(zhuǎn)換為用B列的數(shù)組乘以A列的數(shù)組后想加。;關(guān)于SUMPR

10、ODUCT的應(yīng)用多條件求和一=sumproduct(A:A=“九洲集團(tuán)”)*1，(C:C=“殘友”)*1，(B:B)。其實了解單條件之后，多條件的也就是在加多個數(shù)組。注意：多條件求和這種寫法是需要滿足所有條件才能求和，如果多條件中有一個條件不滿足，那么該條件對應(yīng)的數(shù)據(jù)就不會作為計算值。;關(guān)于SUMPRODUCT的應(yīng)用多條件求和二=sumproduct(A:A=“九洲集團(tuán)”)+(C:C=“殘友”)，(B:B)。這種寫法是先把(A:A=“九洲集團(tuán)”)的數(shù)組與(C:C=“殘友”)數(shù)組想加，也就是1,0,0,0,0,0, 1,0,0,0,0,0, 1,0,0,0,0,0, 1,0,0,0,0,0,+1

11、,0,0,0,1,0,1,0,0,0,1,0,0,0,1,0,1,0,0,0,1,0,0,0=1,0,0,0,1,0, 1,0,0,0,1,0, 1,0,1,0,1,0, 1,0,1,0,0,0。之后在與(D:D)的數(shù)組相乘。多條件求和一中的意思就是滿足(A:A=“九洲集團(tuán)”)并且滿足 (C:C=“殘友”)。多條件求和二中的意思就是滿足(A:A=“九洲集團(tuán)”)或者滿足(C:C=“殘友”)。;關(guān)于SUMPRODUCT的應(yīng)用排名：=SUMPRODUCT(D2$D$2:$D$8)*1)+1數(shù)組公式D2$D$2:$D$8這里是由TRUE和FALSE組成的邏輯數(shù)組FALSE；TRUE；TRUE；FALS

12、E；TRUE；FALSE；TRUE轉(zhuǎn)化為1和0的數(shù)組0；1；1；0；1；0；1。這里利用的是該成績和所以成績進(jìn)行比較，通過測算高于該成績的成績，每次累加1。效果等同于RANK(criteria,VALUE1)函數(shù)注意：因為SUMPRODUCT內(nèi)部參數(shù)是數(shù)組，所以要求行與列的維度一樣。數(shù)組的長度要求相同。排名函數(shù)RANK:= RANK(criteria,VALUE1)criteria表示需要排名的條件，VALUE1表示排名的值。其中排名區(qū)域用$符號表示鎖定，下拉公式時公式中的參數(shù)不會因單元格的改變而改變，快捷鍵是F4。;數(shù)組的表示數(shù)組的民間定義：一組數(shù)的集合。EXCEL中基本數(shù)組的表示方式一行列

13、的數(shù)組用1,2,3,4,5,6,7表示；表示方式二豎列數(shù)組用1;2;3;4;6;7表示1,2,3,99,100行列長串?dāng)?shù)組可以用函數(shù)COLUMN(1：100)表示；1;2;3;99;100豎列長串?dāng)?shù)組可以用函數(shù)ROW(1：100)表示。1到100的所有整數(shù)求和公式為：=SUM(ROW(1：100)因為是把1到100的所有數(shù)相加，1到100是一個數(shù)組，所以公式輸入完成后用Ctrl + Shift + Enter確認(rèn)，表示為數(shù)組求和。如果不用Ctrl + Shift + Enter組合鍵直接用Enter那么結(jié)果為左側(cè)的結(jié)果，只輸出數(shù)組中的第一個數(shù)，也就是1。;關(guān)于COUNT、COUNTA和COUN

14、TBLANK的應(yīng)用COUNT是計數(shù)的函數(shù)=COUNT(VALUE1，VALUE2,)COUNT表示計算區(qū)域中包含數(shù)字的單元格的個數(shù)。VALUE1參數(shù)就要統(tǒng)計的范圍，VALUE2參數(shù)與VALUE1相同，可有可無。如有需求COUNT參數(shù)可以是多個。COUNTA表示計算區(qū)域中非空單元格的個數(shù)，用法與COUNT相同。COUNTBLANK表示計算某個區(qū)域中空單元格的個數(shù)，用法與COUNT相同。;數(shù)組應(yīng)用的實例正常計算公式為=12000*(1+0.05)0+12000*(1+0.05)1+12000*(1+0.05)19EXCEL上不用數(shù)組表示出每年的房租，再進(jìn)行求和。用數(shù)組就不需要列出每一列數(shù)，只需要把

15、該數(shù)組表示出來用組合鍵確認(rèn)就可以。該公式的設(shè)計是要找出數(shù)組的規(guī)律，從左側(cè)的正常計算公式可以看出數(shù)組的規(guī)律是(1+0.05)0,1,2,19。但ROW系數(shù)不能為0，那么可以表示為1+(1+0.05) 1,2,19。Excel表示N次方的函數(shù)為POWER(需計算的參數(shù)，N次方)。所以用POWER(1.05,ROW(1：19)*12000表示該數(shù)組，再用SUM函數(shù)進(jìn)行求和，最后再加上第一年的房租。;關(guān)于COUNTIF與COUNTIFS的應(yīng)用countifrange，criteria）參數(shù)：range 要計算其中非空單元格數(shù)目的區(qū)域參數(shù)：criteria 以數(shù)字、表達(dá)式或文本形式定義的條件counti

16、fs(range1,criteria1, range2,criteria2,)參數(shù)：range 要計算其中非空單元格數(shù)目的區(qū)域參數(shù)：criteria 以數(shù)字、表達(dá)式或文本形式定義的條件。countifs后面的括號可以加多個條件，但每個條件都需要有兩個參數(shù)，一個單元格選取，另外一個就是判斷條件。表示同時滿足的個數(shù)。;Round(number,num_digits)的參數(shù)：number是需要進(jìn)行四舍五入的數(shù)值。num_digits是需要進(jìn)行四舍五入的位數(shù)。number可以是其他函數(shù)。;IF函數(shù)有三個參數(shù)，語法如下：=IF(條件判斷, 結(jié)果為真返回值, 結(jié)果為假返回值);上表為IF語句的實例應(yīng)用，其

17、中結(jié)合了count，round兩個函數(shù)。該例子的設(shè)計思路就是，如果排名小于總共人數(shù)的個數(shù)*等級分配的比例則為A等等。屬于4重IF嵌套。;VLOOKUP查找與引用函數(shù)：=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)表述就是VLOOKUP(查找值，查找范圍，查找列數(shù)，精確匹配或者近似匹配）。從左圖可以看出表二中沒有袁總與俞總的信息，那么表一匹配不到就顯示#N/A。查找列數(shù)從引用的第一列開始計算。如左圖中想查找籍貫，那么從姓名開始算為1，籍貫列就為2。那么index_num參數(shù)的值就為2。 range_lookup表示

18、查找精度，我們一般都輸入0，表示精確查找凡是有#N/A顯示的第一說明需要引用的表中沒有這個人，第二可能是因為名字有誤包括名字輸入差異，格式差異等）。與VLOOKUP相關(guān)的函數(shù)還有HLOOKUP與LOOKUP，用法類似。VLOOKUP是查找列，HLOOKUP是查找行。;1、所謂復(fù)雜的公式就是多個函數(shù)的反復(fù)調(diào)用；2、沒有必要花大量的時間研究函數(shù)，把上述函數(shù)的使用方法和參數(shù)弄透記住基本可以滿足現(xiàn)階段的所用的應(yīng)用。3、遇到無法實現(xiàn)的功能直接百度，有了上述函數(shù)的基礎(chǔ)，不論遇到什么函數(shù)都可以快速的套用;查找相同項，為相同數(shù)據(jù)標(biāo)注顏色;到日期提前三天提醒，用AND(TODAY()=D5+3),(TODAY=D5)實現(xiàn)，D5表示實際日期，實際日期提前3天就是加3