Excel電子表格的使用技巧ppt課件

1、Excel的使用技巧,快速填入相同的內(nèi)容 快速?gòu)?fù)制上一單元格 求字符串中某字符出現(xiàn)的次數(shù) 如何在EXCEL中設(shè)置單元格編輯權(quán)限（保護(hù)部分單元格） Excel中當(dāng)某一單元格符合特定條件，如何在另一單元格顯示特定的顏色 EXCEL中如何控制每列數(shù)據(jù)的長(zhǎng)度并避免重復(fù)錄入 去掉默認(rèn)的表格線 增加工作簿的頁(yè)數(shù),相對(duì)地址、絕對(duì)地址、混合地址 數(shù)據(jù)的輸入 輸入相同數(shù)據(jù)的兩種情況 數(shù)據(jù)的行列對(duì)調(diào) 跨表操作數(shù)據(jù) 兩次選定單元格 兩列數(shù)據(jù)巧合并 用快捷鍵設(shè)置單元格文本 自動(dòng)調(diào)整為合適的列寬 繪制斜線表頭,Excel做成績(jī)表時(shí)自動(dòng)實(shí)現(xiàn)同分同名次 按Shift鍵 把Excel單元格區(qū)域轉(zhuǎn)換為圖片 制作漂亮的工資條 圖

2、表編輯操作,1. 快速填入相同內(nèi)容,選中多個(gè)單元格后，輸入字符，然后按Ctrl+Enter組合鍵，即可在選中的每個(gè)單元格中填入上述相同的字符,2. 快速?gòu)?fù)制上一單元格,選中下面的單元格，按Ctrl+ （為西文的單引號(hào)）組合鍵，即可將上一單元格的內(nèi)容快速?gòu)?fù)制下來(lái),3. 求字符串中某字符出現(xiàn)的次數(shù),例如：求A1單元格中字符“a”出現(xiàn)的次數(shù)： =LEN(A1)-LEN(SUBSTITUTE(A1,“a” ,“ ”) LEN(text)：返回文本字符串的個(gè)數(shù) SUBSTITUTE（text,old_text,new_text,instance_num）: 將字符串中的部分字符串以新字符串替換,4. 如

3、何在EXCEL中設(shè)置單元格編輯權(quán)限（保護(hù)部分單元格,先選定所有單元格，點(diǎn)“格式”“單元格”“保護(hù)”，取消“鎖定”前面的“,再選定你要保護(hù)的單元格，點(diǎn)“格式”“單元格”“保護(hù)”，在“鎖定”前面打上“,3. 點(diǎn)“工具”“保護(hù)”“保護(hù)工作表”，輸入兩次密碼，點(diǎn)兩次“確定”即可,5. Excel中當(dāng)某一單元格符合特定條件，如何在另一單元格顯示特定的顏色,比如： A11時(shí)，C1顯示紅色 0A11時(shí)，C1顯示綠色 A10時(shí)，C1顯示黃色,方法如下： 單擊C1單元格，點(diǎn)“格式”“條件格式”， 條件1設(shè)為： 公式=A11,點(diǎn)“格式” “字體”“顏色”，點(diǎn)擊紅色后點(diǎn)“確定,2. 條件2設(shè)為：公式=AND（A10

4、,A11,點(diǎn)“格式” “字體”“顏色”，點(diǎn)擊綠色后點(diǎn)“確定,3. 條件3設(shè)為：公式=A10 點(diǎn)“格式”“字體”“顏色”，點(diǎn)擊黃色后點(diǎn)“確定,4. 三個(gè)條件設(shè)定好后，點(diǎn)“確定”即可,6. EXCEL中如何控制每列數(shù)據(jù)的長(zhǎng)度并避免重復(fù)錄入,1. 用數(shù)據(jù)有效性定義數(shù)據(jù)長(zhǎng)度。 用鼠標(biāo)選定你要輸入的數(shù)據(jù)范圍，點(diǎn)“數(shù)據(jù)”一“有效性”一“設(shè)置”，“有效性條件”設(shè)成“允許”“文本長(zhǎng)度”“等于”“5”（具體條件可根據(jù)你的需要改變）。還可以定義一些提示信息、出錯(cuò)警告信息和是否打開(kāi)中文輸入法等，定義好后點(diǎn)“確定,2. 用條件格式避免重復(fù)。 選定A列，點(diǎn)“格式”一“條件格式”，將條件設(shè)成 “公式=COUNTIF($A

5、：$A,$A1)1”,點(diǎn)“格式”一“字體”一“顏色”，選定紅色后點(diǎn)兩次“確定”。 COUNTIF(range,criteria)：計(jì)算某個(gè)區(qū)域中滿足給定條件的單元 格數(shù)目,這樣設(shè)定好后，輸入數(shù)據(jù)如果長(zhǎng)度不對(duì)會(huì)有提示，如果數(shù)據(jù)重復(fù)字體將會(huì)變成紅色,7. 去掉默認(rèn)的表格線,單擊“工具”菜單中的“選項(xiàng)”，再單擊對(duì)話框中的“視圖”，找到“網(wǎng)格線”，使之失效（將左邊的“”去掉,8. 增加工作簿的頁(yè)數(shù),單擊“插入”菜單下的“工作表”，即可在底部看到一個(gè)新名稱 的工作表。一個(gè)工作簿最多可以有255張工作表,9. 相對(duì)地址、絕對(duì)地址、混合地址,相對(duì)地址的形式是地址直接用列號(hào)行號(hào)組成。 絕對(duì)地址的形式為：$A$

6、1,$B$1。 有些公式引用單元格是用混合型地址,10. 數(shù)據(jù)的輸入,日期和時(shí)間的輸入 當(dāng)前系統(tǒng)日期的輸入：ctrl + ； 當(dāng)前系統(tǒng)時(shí)間的輸入：ctrl + shift +； 12小時(shí)制輸入時(shí)間，在時(shí)間數(shù)字后空一格，并鍵入 字母a或p，例如，8：00 a 改變?nèi)掌诟袷皆凇案袷?單元格”中修改,Word 里：Alt+Shift+D：當(dāng)前日期 Alt+Shift+T：當(dāng)前時(shí)間,數(shù)值的輸入,負(fù)數(shù)輸入：直接輸入負(fù)號(hào)和數(shù)字，或用圓括號(hào)輸入。 分?jǐn)?shù)輸入：先輸入0和一個(gè)空格，再輸入分?jǐn)?shù)。直接輸入分?jǐn)?shù) 則認(rèn)為是日期。 輸入據(jù)用自動(dòng)設(shè)置小數(shù)點(diǎn)或尾隨零的數(shù)字。在“工具一選項(xiàng)一 編輯”選項(xiàng)卡中設(shè)置自動(dòng)小數(shù)點(diǎn),公式

7、的輸入,公式以等號(hào)“=”開(kāi)頭，Excel有很多可利用的公式。如果想在工作簿中看到的是公式，而不是具體的值，則在“工具一選項(xiàng)一視圖”選項(xiàng)卡中，選中“窗口選項(xiàng)”下的“公式”復(fù)選框。注意，這樣在打印的時(shí)候打印的也是公式,11. 輸入相同數(shù)據(jù)的兩種情況,同時(shí)在多個(gè)單元格中輸入相同的數(shù)據(jù)。 方法：選擇要輸入數(shù)據(jù)的單元格，如果不連續(xù)可以使用Ctrl鍵 進(jìn)行選擇，然后輸入數(shù)據(jù)，再按下Ctrl+Enter鍵就可以了。 同時(shí)在多張工作表中輸入或編輯相同的數(shù)據(jù)選中多個(gè)工作表后 后在相應(yīng)單元格中輸入數(shù)據(jù)，選擇多個(gè)工作表的方法也是使用 Ctrl或Shift鍵,12. 數(shù)據(jù)的行列對(duì)調(diào),選取要對(duì)調(diào)的數(shù)據(jù)范圍，復(fù)制。 在目

8、的單元格上單擊鼠標(biāo)右鍵，選擇“選擇性粘貼”，在對(duì)話 框中選擇“轉(zhuǎn)置”，確定,13. 跨表操作數(shù)據(jù),設(shè)有名稱為sheet1、sheet2和sheet3的三張工作表，現(xiàn)要用sheet1的D8單元格的內(nèi)容乘以40%，再加上sheet2的B8單元格內(nèi)容乘以60%作為sheet3的A8單元格的內(nèi)容。則應(yīng)該在sheet3的A8單元格輸入以下算式：=sheet1！D8*40%+sheet2！B8*60,14. 兩次選定單元格,有時(shí)，我們需要在某個(gè)單元格內(nèi)連續(xù)輸入多個(gè)測(cè)試值，以查看引用此單元格的其他單元格的效果。但每次輸入一個(gè)值后按Enter鍵，活動(dòng)單元格默認(rèn)下移一個(gè)單元格，非常不便。單擊鼠標(biāo)選定單元格，然后

9、按住Ctrl鍵再次單擊鼠標(biāo)選定此單元格（此時(shí)，單元格周?chē)鷮⒊霈F(xiàn)實(shí)線框,15. 兩列數(shù)據(jù)巧合并,在EXCEL表格中，如果有兩列數(shù)據(jù)需要合并起來(lái)，如果是數(shù)字的話，一般情況下可以使用四則運(yùn)算的方法進(jìn)行計(jì)算；但是如果有一列是文字，另一列是數(shù)字（或者文字），那么可以先在這兩列后面的一個(gè)空列中單擊，并輸入=號(hào)，然后輸入第一列的第一個(gè)單元格的相對(duì)引用名，輸入如果總分已排過(guò)序，則數(shù)字的排位就是它當(dāng)前的位置。上述函數(shù)中最后的“0”指明排位的方式，當(dāng)其為 0或省略時(shí)，Excel 對(duì)數(shù)字的排位是按照降序排列的。 如果不為零， Excel 對(duì)數(shù)字的排位則是按照升序排列的。該函數(shù)對(duì)重復(fù)數(shù)的排位相同，而下一名次則將前面的

10、重復(fù)數(shù)計(jì)算在內(nèi),2. 使用計(jì)數(shù)函數(shù)COUNTIF,在L2單元格中輸入函數(shù) “=COUNTIF(K$2:K$1001,“”&K2)+1”， 然后向下自動(dòng)填充即可實(shí)現(xiàn)該操作,該函數(shù)的功能是計(jì)算區(qū)域中滿足給定條件的單元格個(gè)數(shù)。上述函數(shù)中采用絕對(duì)引用指定計(jì)算區(qū)域K$2:K$1001，名次所對(duì)應(yīng)的單元格K2采用相對(duì)引用。即求出在K2:K1001區(qū)域中大于名次所對(duì)應(yīng)總分的單元格的個(gè)數(shù)，然后加上1就可以得出該單元格中數(shù)值的名次。該操作不管有幾個(gè)重復(fù)的，大于它的個(gè)數(shù)卻是固定的，所以重復(fù)數(shù)據(jù)的名次也就是相同的了。同RANK函數(shù)一樣，該函數(shù)不管是否已經(jīng)按總分進(jìn)行排序均可進(jìn)行上述操作,3. 采用IF語(yǔ)句加COUNT

11、函數(shù),該操作與上述兩種方法不同，必須先將總分按照降序排列。然后將第一名的名次“1”輸入，再在L3單元格中輸入函數(shù)“=IF(K3=K2,L2,COUNT($K$2:K3)”，確定后向下自動(dòng)填充也可實(shí)現(xiàn)同分同名次的操作,COUNT函數(shù)的功能是返回指定區(qū)域中數(shù)字單元格的個(gè)數(shù)。因?yàn)镵列全是數(shù)字單元格，所以該總分處在第幾位上名次就是幾。上述語(yǔ)句的意思就是如果當(dāng)前總分和上一個(gè)總分相同，則輸入上一個(gè)總分的名次，否則執(zhí)行COUNT語(yǔ)句，統(tǒng)計(jì)個(gè)數(shù),20. 按Shift鍵 把Excel單元格區(qū)域轉(zhuǎn)換為圖片,Shift鍵在Excel里有這樣的妙處：在按下Shift鍵的同時(shí)點(diǎn)擊“編輯”菜單，原來(lái)的復(fù)制和粘貼選項(xiàng)就會(huì)變

12、成“復(fù)制圖片”和“粘貼圖片”。利用這一功能，我們就能把選定的單元格區(qū)域方便地轉(zhuǎn)換為圖片,具體操作方法如下： 首先選中需要復(fù)制成圖片的單元格區(qū)域，然后按住Shift鍵，依次選擇“編輯復(fù)制圖片”命令，接著彈出“復(fù)制圖片”窗口，選擇“圖片”單選項(xiàng)后點(diǎn)擊“確定”按鈕。這時(shí)選定的表格區(qū)域就已經(jīng)被復(fù)制成圖片了,再到需要使用這張圖片的地方選擇“粘貼”命令即可(或者按Shift鍵再選擇“編輯粘貼圖片”命令)，比如將其在Word中粘貼。 另外，如果選擇“如打印效果”單選項(xiàng)，那么被轉(zhuǎn)換為圖片的數(shù)據(jù)區(qū)域是按照打印效果來(lái)處理的，比如設(shè)置有粗邊框，那么粘貼出來(lái)的圖片上就會(huì)有粗邊框,21. 制作漂亮的工資條,新建一Exc

13、el文件，在sheet1中存放工資表的原始數(shù)據(jù)，假設(shè)有O列。第一行是工資項(xiàng)目，從第二行開(kāi)始是每個(gè)人的工資,在sheet2中我們來(lái)設(shè)置工資條。根據(jù)實(shí)際情況，工資條由三行構(gòu)成，一行對(duì)應(yīng)工資項(xiàng)目，一行對(duì)應(yīng)一個(gè)人的工資數(shù)據(jù)，然后是一個(gè)空行用來(lái)方便切割。這樣三行構(gòu)成一個(gè)工資條。工資項(xiàng)目處在行號(hào)除以3余數(shù)為1的行上；空行處在行號(hào)能整除3的行上。以上兩行不難設(shè)置，關(guān)鍵是工資數(shù)據(jù)行，牽扯到sheet1與 sheet2中數(shù)據(jù)的對(duì)應(yīng)，經(jīng)分析不難看出“sheet1中的數(shù)據(jù)行=INT(sheet2中的數(shù)據(jù)行+4)/3,這樣我們?cè)趕heet2的A1單元格中輸入公式“=IF(MOD(ROW(),3)=0,”“,IF(MO

14、D(ROW(),3)=1,Sheet1!A$1,INDEX(Sheet1!$A:$N,INT(ROW()+4)/3),COLUMN()”。確認(rèn)后選擇A1單元格，把鼠標(biāo)放在A1單元格的右下角，鼠標(biāo)變成“+”時(shí)，向右拖動(dòng)鼠標(biāo)自動(dòng)填充至O列，這樣工資條中的第一行就出來(lái)了。選定A1：O1，把鼠標(biāo)放在O1單元格的右下角，鼠標(biāo)再次變成“+”時(shí)，向下拖動(dòng)鼠標(biāo)自動(dòng)填充到數(shù)據(jù)的最后一行，工資條就全部制作完成了,IF(logical_test,value_if_ture,value_if_false)：判斷一個(gè)條件是否滿足，如果滿 足返回一個(gè)值，如果不滿足則返回另一個(gè)值。 MOD(number,divisor):

15、 返回兩數(shù)相除的余數(shù)。 INDEX(arraym,row_num,column_num):在給定的單元格區(qū)域中，返回特定行 列交叉處單元格的值或引用。Array：?jiǎn)卧駞^(qū)域或數(shù)組常量；row_num：數(shù) 組或引用中要返回值的行序號(hào)。如果忽略，則必須有column_num參數(shù)； column_num：數(shù)組或引用中要返回值的列序號(hào)。如果忽略，則必須有 row_num參數(shù)。 INT(number)：將數(shù)值向下取整數(shù)為最接近的整數(shù),該公式運(yùn)用IF函數(shù)，對(duì)MOD函數(shù)所取的引用行號(hào)與3的余數(shù)進(jìn)行判斷。如果余數(shù)為0，則產(chǎn)生一個(gè)空行；如果余數(shù)為1，則固定取sheet1中第一行的內(nèi)容；否則運(yùn)用INDEX函數(shù)和I

16、NT函數(shù)來(lái)取Sheet1對(duì)應(yīng)行上的數(shù)。 最后來(lái)設(shè)置一下格式，選定A1：O2設(shè)上表格線，空行不設(shè)。然后選定A1：O3，拖動(dòng)N3的填充柄向下自動(dòng)填充，這樣有數(shù)據(jù)的有表格線，沒(méi)有數(shù)據(jù)的沒(méi)有表格線。最后調(diào)整一下頁(yè)邊距，千萬(wàn)別把一個(gè)工資條打在兩頁(yè)上。怎么樣，還滿意吧,22. 圖表編輯操作,1.創(chuàng)建圖表 2.編輯圖表 3.圖表的保存,1. 創(chuàng)建圖表,創(chuàng)建圖表的操作步驟： (1) 在工作表中選擇數(shù)據(jù)區(qū)域。按住“Ctrl”鍵同時(shí)拖動(dòng)鼠標(biāo)， 可以選擇數(shù)據(jù)區(qū)域中不連續(xù)的單元格區(qū)域,2) 單擊常用工具欄的圖表向?qū)О粹o，屏幕出現(xiàn)圖表向?qū)У?步對(duì)話框,3) 選擇柱形圖中的“簇狀柱形圖”，單擊“下一步”按鈕，屏 幕出現(xiàn)圖表向?qū)У牡诓?4) 該對(duì)話框有“數(shù)據(jù)區(qū)域”和“系列”兩個(gè)選項(xiàng)卡。“數(shù)據(jù)區(qū)域”選項(xiàng) 卡用于確定圖表所包含的數(shù)據(jù)區(qū)域。在數(shù)據(jù)區(qū)域欄中顯示目前包 含在圖表中的所有數(shù)據(jù)單元格的范圍?！跋盗小边x項(xiàng)卡用于修改數(shù) 據(jù)系列的名稱、數(shù)值和分類(lèi)軸標(biāo)志。 (5) 選擇系列產(chǎn)生在