EXCEL在日常工作中的應(yīng)用與技巧課件

1、EXCEL在日常工作中的應(yīng)用與技巧 蘇春莉 二0一0年九月 上 海EXCEL在日常工作中的應(yīng)用與技巧 一、EXCEL技巧及應(yīng)用1、如何在EXCEL中快速定位最后一行數(shù)據(jù)2、如何用SUN函數(shù)快速求和3、在Excel中行列快速轉(zhuǎn)換4、在工作表中隱藏所有零值5、數(shù)據(jù)有效性的應(yīng)用6、如何用條件格式找出重復(fù)項(xiàng)7、數(shù)據(jù)透視表應(yīng)用8、保護(hù)工作簿和工作表9、把多個(gè)表格匯總求和成一個(gè)匯總表10、身份證號碼輸入的幾種解決方法11、日期格式的轉(zhuǎn)換12、去空格函數(shù)13、怎樣快速去除表中不同行和列的空格二、常用函數(shù)學(xué)習(xí)與應(yīng)用1、假設(shè)條件函數(shù)IF含義及運(yùn)用2、 RANK函數(shù)的應(yīng)用3、 VLOOKUP的運(yùn)用4、函數(shù)公式出錯(cuò)

2、信息一、EXCEL技巧及應(yīng)用1、如何在EXCEL中快速定位最后一行數(shù)據(jù)如果“定位”就是選中的意思，可按CTRL+END鍵實(shí)現(xiàn)。CTRL+ 雙擊選取單元格之下框線操作：將光標(biāo)移到欲要求和的列或行，直按Alt+=，最后按一下“enter鍵就可以。這樣我們不用輸入?yún)?shù)就可以快速求和。2、如何用SUN函數(shù)快速求和一、EXCEL技巧及應(yīng)用1、如何在EXCEL中快速定位最后一行數(shù)據(jù)如果“定位”就是選 如果需要要將Excel按行（列）排列的數(shù)據(jù)，轉(zhuǎn)換為按列（行）排列，可以通過“選擇性粘貼”來實(shí)現(xiàn)。 選中需要轉(zhuǎn)換的數(shù)據(jù)區(qū)域，執(zhí)行一下“復(fù)制”操作；選中保存數(shù)據(jù)的第一個(gè)單元格，執(zhí)行“編輯選擇性粘貼”命令，打開“選

3、擇性粘貼”對話框，選中其中的“轉(zhuǎn)置”選項(xiàng)，確定返回即可。 3、在Excel中行列快速轉(zhuǎn)換 在Excel默認(rèn)情況下，零值將顯示為0，這個(gè)值是一個(gè)比較特殊的數(shù)值。如果工作表中包含了大量的零值，會使整個(gè)工作表顯得十分凌亂。如果要隱藏工作表中所有的零值，可以這樣操作：選擇“工具”“選項(xiàng)”，打開“選項(xiàng)”對話框，單擊“視圖”標(biāo)簽，在“窗口選項(xiàng)”里把“零值”復(fù)選框前面的對號去掉，單擊“確定”按鈕。此時(shí)，可以看到原來顯示有0的單元格全部變成了空白單元格。4、在工作表中隱藏所有零值小提示：若要在單元格里重新顯示0，用上述方法把“零值”復(fù)選框前面的打上對號即可。 如果需要要將Excel按行（列）排列的數(shù)據(jù)，轉(zhuǎn)5、

4、數(shù)據(jù)有效性功能（限制錄入數(shù)據(jù)范圍、重復(fù)值、創(chuàng)建下拉列表、快捷輸入數(shù)據(jù)等）5、數(shù)據(jù)有效性功能（限制錄入數(shù)據(jù)范圍、重復(fù)值、創(chuàng)建下拉列表、6、條件格式功能（生日提醒、標(biāo)記重復(fù)值、填充不同色等）6、條件格式功能（生日提醒、標(biāo)記重復(fù)值、填充不同色等）7、數(shù)據(jù)透視表應(yīng)用 數(shù)據(jù)透視表是一種交互式的表，可以進(jìn)行某些計(jì)算，如求和與計(jì)數(shù)等。所進(jìn)行的計(jì)算與數(shù)據(jù)在數(shù)據(jù)透視表中的排列有關(guān)。 操作步驟 7、數(shù)據(jù)透視表應(yīng)用8. 保護(hù)工作簿和工作表保護(hù)工作簿 選定整個(gè)工作簿，右擊選擇“單元格設(shè)置”中“保護(hù)”，清空“鎖定”前的“”。 選定要保護(hù)的單元格列，進(jìn)行鎖定。選擇“審閱”選項(xiàng)卡“更改”組的“保護(hù)工作簿”項(xiàng)選擇“保護(hù)結(jié)構(gòu)和

5、窗口”。勾選“結(jié)構(gòu)”或“窗口”復(fù)選框保護(hù)結(jié)構(gòu): 不能插入、刪除工作表、更改工作表名稱等保護(hù)窗口: 保留窗口的大小及位置等8. 保護(hù)工作簿和工作表保護(hù)工作表: 保護(hù)工作表中數(shù)據(jù)不被任意修改鎖定+保護(hù)工作表：保護(hù)所有被鎖定的單元格。選擇“審閱”選項(xiàng)卡的“更改”組的“保護(hù)工作表”項(xiàng)，在對話框里輸入密碼在“保護(hù)工作表”對話框勾選所需的保護(hù)內(nèi)容保護(hù)工作表命令只對本工作表起作用只允許用戶編輯指定單元格區(qū)域取消鎖定+保護(hù)工作表 選擇指定單元格，取消單元格的保護(hù)鎖定 此時(shí)只有可以被編輯，其它區(qū)域都被鎖住了保護(hù)工作表: 保護(hù)工作表中數(shù)據(jù)不被任意修改9.把多個(gè)表格匯總求和成一個(gè)匯總表 假設(shè)匯總的工作表叫Sheet

6、1，而存放數(shù)據(jù)的工作表叫Sheet2, Sheet3, ., Sheet27，你可以設(shè)置公式，把各工作表A1的數(shù)值加起來:=SUM(Sheet2:Sheet27!A1) 要注意的是，Sheet1不能在Sheet2和Sheet27中間，Excel不會管工作表的名字，只會把Sheet2和Sheet27中間的所有工作表中相關(guān)的儲存格（哪怕中間有一個(gè)工作表叫Sheet99）加起來。 操作步驟：1、在匯總表單元格內(nèi)選擇“” 2、選擇需要疊加的第一個(gè)工作表 3、按住shift同時(shí)用鼠標(biāo)選擇需要疊加的最后一個(gè)工作表 4、選擇需要疊加的單元格 5、Enter插入例表 9.把多個(gè)表格匯總求和成一個(gè)匯總表 假設(shè)匯

7、總的10.身份證號碼輸入的幾種解決方法 方法1：在A1單元輸入號碼時(shí)，在前面先輸入號，是：123456789012345,這樣單元格會默認(rèn)為該單元為文本方式，會完整顯示出15個(gè)號碼來，而不會顯示那令人討厭的科學(xué)計(jì)數(shù)方式來了。 在EXCEL中，輸入超過11位數(shù)字時(shí)會自動轉(zhuǎn)為科學(xué)計(jì)數(shù)的方式，比如身份證號是：123456789012345，輸入后就變成了：1.23457E+14,現(xiàn)在為大家說幾種比較簡單快速的方法： 方法 2：如果已經(jīng)輸入了大量的號碼，全部以科學(xué)計(jì)數(shù)顯示的話，而又懶的按照上面的方法一個(gè)一個(gè)的重新輸入的話，也有這個(gè)好方法哦，比如已在單元A1：A100輸入了號碼，按以下步驟做：選擇單元A

8、1：A100單擊鼠標(biāo)右鍵，設(shè)置單元格式選擇自定義，在類型中輸入0即可，輕松搞定！ 10.身份證號碼輸入的幾種解決方法 方法1：在A1 方法3：還有一種用函數(shù)來解決的方法：在A1：A100已經(jīng)輸入大量的號碼時(shí)，在B1單元中輸入：=trim( a1),注意兩個(gè)之間是空格，這個(gè)公式的意思:先在A1單元15位號碼前加個(gè)空格符號，然后用trim這個(gè)函數(shù)把空格符號去掉。輸完后，B1單元也是顯示出完整的15位數(shù)字了。10.身份證號碼輸入的幾種解決方法 方法3：還有一種用函數(shù)來解決的方法：在A1：A100已經(jīng)將“1968年6月12日”轉(zhuǎn)換為“1968-6-12”格式方法1：=YEAR(A1)&-&MONTH(

9、A1)&-&DAY(A1) 顯示：1968-6-12方法2：=TEXT(A1,yyyy-mm-dd)顯示：1968-06-1211.日期格式的轉(zhuǎn)換將“1968年6月12日”轉(zhuǎn)換為“1968-6-12”格式112.去空格函數(shù) 如何刪去單元格中的空格，如姓名前，中，后的空格，即單元格中是兩個(gè)字的人名中間有一個(gè)空格，想刪去有何方法。如：中國，改為：中國。1、用公式：=SUBSTITUTE(A2, ,)注：第一對雙引號中有一空格。而第二個(gè)“”中是無空格的。2、利用查找替換，一次性全部解決。“編輯”“替換”（或Ctrl+H），在“查找”欄內(nèi)輸入一空格，“替換”什么也不輸入（空白）。然后“全部替換”即可。

10、編輯-定位-定位條件-空值,可選中所有空單元格, 再刪除。13.怎樣快速去除表中不同行和列的空格12.去空格函數(shù) 如何刪去單元格中的空格，如姓名前 示例： 統(tǒng)計(jì)銷售人員月度出庫時(shí)，希望輸入10000元以下時(shí)，顯示為“不達(dá)標(biāo)”;輸入10000元以上的分?jǐn)?shù)時(shí)，顯示為“達(dá)標(biāo)”。這樣的效果，不妨稱之為“條件顯示”，用IF函數(shù)可以實(shí)現(xiàn)。 假設(shè)分?jǐn)?shù)在B3單元格中，要將等級顯示在C3單元格中。那么在C3單元格中輸入以下公式實(shí)現(xiàn)不同的等級: =if(b3=10000,“不達(dá)標(biāo)”，“達(dá)標(biāo)”) 分“不達(dá)標(biāo)”和“達(dá)標(biāo)”2個(gè)等級 =if(b3=10000,“不達(dá)標(biāo)”，if(b3=35000,“達(dá)標(biāo)”，“優(yōu)秀”) 注意

11、:符號為半角，IF與括弧之間不能有空格，而且最多嵌套7層。1、假設(shè)條件函數(shù)IF含義及運(yùn)用二、常用函數(shù)學(xué)習(xí)與應(yīng)用 IF函數(shù)含義：判斷，根據(jù)邏輯計(jì)算的真假值，返回不同結(jié)果。可以使用函數(shù) IF 對數(shù)值和公式進(jìn)行條件檢測。 示例： 1、假設(shè)條件函數(shù)IF含義及運(yùn)用二、 RANK含義：返回一個(gè)數(shù)字在數(shù)字列表中的排位。數(shù)字的排位是其大小與列表中其他值的比值（如果列表已排過序，則數(shù)字的排位就是它當(dāng)前的位置）。將影響后續(xù)數(shù)值的排位。例如，在一列按升序排列的整數(shù)中，如果整數(shù) 10 出現(xiàn)兩次，其排位為 5，則 11 的排位為 7（沒有排位為 6 的數(shù)值）。2、 RANK函數(shù)的應(yīng)用排名公式：=RANK(B2,$B$2

12、:$B$21)；示例： RANK含義：返回一個(gè)數(shù)字在數(shù)字列表中的排位。數(shù)3、 VLOOKUP的運(yùn)用 VLOOKUP(需在第一列中查找的數(shù)據(jù),需要在其中查找數(shù)據(jù)的數(shù)據(jù)表,需返回某列值的列號,邏輯值True或False) 1.Lookup_value為“需在數(shù)據(jù)表第一列中查找的數(shù)據(jù)”，可以是數(shù)值、文本字符串或引用。 2.Table_array 為“需要在其中查找數(shù)據(jù)的數(shù)據(jù)表”，可以使用單元格區(qū)域或區(qū)域名稱等。 如果 range_lookup 為 TRUE或省略，則 table_array 的第一列中的數(shù)值必須按升序排列，否則，函數(shù) VLOOKUP 不能返回正確的數(shù)值。 如果range_lookup

13、 為 FALSE，table_array 不必進(jìn)行排序。 Table_array 的第一列中的數(shù)值可以為文本、數(shù)字或邏輯值。若為文本時(shí)，不區(qū)分文本的大小寫。 3、 VLOOKUP的運(yùn)用 VLOOKUP(需 3.Col_index_num 為table_array 中待返回的匹配值的列序號。 Col_index_num 為 1 時(shí)，返回 table_array 第一列中的數(shù)值； Col_index_num 為 2 時(shí)，返回 table_array 第二列中的數(shù)值，以此類推。 如果Col_index_num 小于 1，函數(shù) VLOOKUP 返回錯(cuò)誤值 #VALUE!； 如果Col_index_nu

14、m 大于 table_array 的列數(shù)，函數(shù) VLOOKUP 返回錯(cuò)誤值 #REF!。 4.Range_lookup 為一邏輯值，指明函數(shù) VLOOKUP 返回時(shí)是精確匹配還是近似匹配。如果為 TRUE 或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value 的最大數(shù)值；如果 range_value 為 FALSE，函數(shù) VLOOKUP 將返回精確匹配值。如果找不到，則返回錯(cuò)誤值 #N/A。示例 3.Col_index_num 為table_a4、函數(shù)公式出錯(cuò)信息 （7個(gè)）#DIV/0零作除數(shù)#NAME?在公式中使用了不能識別的名稱刪除了公式中使用的名稱，或者使用了不存在的名稱。函數(shù)名的拼寫錯(cuò)誤#VALUE！使用了不正確的參數(shù)或運(yùn)算符在需要數(shù)字或邏輯值時(shí)輸入了文本#REF！引用了無效的單元格地址刪除了由其它公式引用的單元格將移動單元格粘貼到由其它公式引用的單元格中。#NULL！指定了兩個(gè)并不相交的區(qū)域，故無效使用了不正確的區(qū)域運(yùn)算符或不正確的單元格引用。4、函數(shù)公式出錯(cuò)信息 （7個(gè)）#DIV/0零作除數(shù)#N/A 當(dāng)在函數(shù)或公式中引用了無法使用的數(shù)值內(nèi)部函數(shù)或自定義