EXCEL在日常工作中的

應(yīng)用與技巧蘇春莉二0一0年九月上海一、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、身份證號(hào)碼輸入的幾種解決方法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ò)信息1、如何在EXCEL中快速定位最后一行數(shù)據(jù)如果“定位”就是選中的意思，可按CTRL+END鍵實(shí)現(xiàn)。CTRL+↓

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

3、在Excel中行列快速轉(zhuǎn)換在Excel默認(rèn)情況下，零值將顯示為0，這個(gè)值是一個(gè)比較特殊的數(shù)值。如果工作表中包含了大量的零值，會(huì)使整個(gè)工作表顯得十分凌亂。如果要隱藏工作表中所有的零值，可以這樣操作：選擇“工具”→“選項(xiàng)”，打開“選項(xiàng)”對(duì)話框，單擊“視圖”標(biāo)簽，在“窗口選項(xiàng)”里把“零值”復(fù)選框前面的對(duì)號(hào)去掉，單擊“確定”按鈕。此時(shí)，可以看到原來顯示有0的單元格全部變成了空白單元格。4、在工作表中隱藏所有零值小提示：若要在單元格里重新顯示0，用上述方法把“零值”復(fù)選框前面的打上對(duì)號(hào)即可。5、數(shù)據(jù)有效性功能（限制錄入數(shù)據(jù)范圍、重復(fù)值、創(chuàng)建下拉列表、快捷輸入數(shù)據(jù)等）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)。操作步驟

8.保護(hù)工作簿和工作表保護(hù)工作簿

選定整個(gè)工作簿，右擊選擇“單元格設(shè)置”中“保護(hù)”，清空“鎖定”前的“√”。

選定要保護(hù)的單元格列，進(jìn)行鎖定。選擇“審閱”選項(xiàng)卡“更改”組的“保護(hù)工作簿”項(xiàng)選擇“保護(hù)結(jié)構(gòu)和窗口”。勾選“結(jié)構(gòu)”或“窗口”復(fù)選框保護(hù)結(jié)構(gòu):不能插入、刪除工作表、更改工作表名稱等保護(hù)窗口:保留窗口的大小及位置等保護(hù)工作表:

保護(hù)工作表中數(shù)據(jù)不被任意修改鎖定+保護(hù)工作表：保護(hù)所有被鎖定的單元格。選擇“審閱”選項(xiàng)卡的“更改”組的“保護(hù)工作表”項(xiàng)，在對(duì)話框里輸入密碼在“保護(hù)工作表”對(duì)話框勾選所需的保護(hù)內(nèi)容保護(hù)工作表命令只對(duì)本工作表起作用只允許用戶編輯指定單元格區(qū)域取消鎖定+保護(hù)工作表選擇指定單元格，取消單元格的保護(hù)鎖定此時(shí)只有可以被編輯，其它區(qū)域都被鎖住了9.把多個(gè)表格匯總求和成一個(gè)匯總表假設(shè)匯總的工作表叫Sheet1，而存放數(shù)據(jù)的工作表叫Sheet2,Sheet3,...,Sheet27，你可以設(shè)置公式，把各工作表A1的數(shù)值加起來:=SUM(Sheet2:Sheet27!A1)要注意的是，Sheet1不能在Sheet2和Sheet27中間，Excel不會(huì)管工作表的名字，只會(huì)把Sheet2和Sheet27中間的＊所有＊工作表中相關(guān)的儲(chǔ)存格（哪怕中間有一個(gè)工作表叫Sheet99）加起來。操作步驟：1、在匯總表單元格內(nèi)選擇“Σ”2、選擇需要疊加的第一個(gè)工作表3、按住shift同時(shí)用鼠標(biāo)選擇需要疊加的最后一個(gè)工作表4、選擇需要疊加的單元格5、Enter插入例表10.身份證號(hào)碼輸入的幾種解決方法方法1：在A1單元輸入號(hào)碼時(shí)，在前面先輸入’號(hào)，是：’123456789012345,這樣單元格會(huì)默認(rèn)為該單元為文本方式，會(huì)完整顯示出15個(gè)號(hào)碼來，而不會(huì)顯示那令人討厭的科學(xué)計(jì)數(shù)方式來了。在EXCEL中，輸入超過11位數(shù)字時(shí)會(huì)自動(dòng)轉(zhuǎn)為科學(xué)計(jì)數(shù)的方式，比如身份證號(hào)是：123456789012345，輸入后就變成了：1.23457E+14,現(xiàn)在為大家說幾種比較簡(jiǎn)單快速的方法：方法2：如果已經(jīng)輸入了大量的號(hào)碼，全部以科學(xué)計(jì)數(shù)顯示的話，而又懶的按照上面的方法一個(gè)一個(gè)的重新輸入的話，也有這個(gè)好方法哦，比如已在單元A1：A100輸入了號(hào)碼，按以下步驟做：》選擇單元A1：A100》單擊鼠標(biāo)右鍵，設(shè)置單元格式》選擇自定義，在‘類型’中輸入‘0’即可，輕松搞定！

方法3：還有一種用函數(shù)來解決的方法：在A1：A100已經(jīng)輸入大量的號(hào)碼時(shí)，在B1單元中輸入：=trim(''a1),注意兩個(gè)’之間是空格，這個(gè)公式的意思:先在A1單元15位號(hào)碼前加個(gè)空格符號(hào)，然后用trim這個(gè)函數(shù)把空格符號(hào)去掉。輸完后，B1單元也是顯示出完整的15位數(shù)字了。10.身份證號(hào)碼輸入的幾種解決方法將“1968年6月12日”轉(zhuǎn)換為“1968-6-12”格式方法1：=YEAR(A1)&"-"&MONTH(A1)&"-"&DAY(A1)

顯示：1968-6-12方法2：=TEXT(A1,"yyyy-mm-dd")

顯示：1968-06-1211.日期格式的轉(zhuǎn)換12.去空格函數(shù)如何刪去單元格中的空格，如姓名前，中，后的空格，即單元格中是兩個(gè)字的人名中間有一個(gè)空格，想刪去有何方法。如：中

國(guó)，改為：中國(guó)。1、用公式：=SUBSTITUTE(A2,"","")

注：第一對(duì)雙引號(hào)中有一空格。而第二個(gè)“”中是無(wú)空格的。2、利用查找－替換，一次性全部解決?！熬庉嫛保疤鎿Q”（或Ctrl+H），在“查找”欄內(nèi)輸入一空格，“替換”什么也不輸入（空白）。然后“全部替換”即可。編輯-定位-定位條件-空值,可選中所有空單元格,再刪除。13.怎樣快速去除表中不同行和列的空格示例：統(tǒng)計(jì)銷售人員月度出庫(kù)時(shí)，希望輸入10000元以下時(shí)，顯示為“不達(dá)標(biāo)”;輸入10000元以上的分?jǐn)?shù)時(shí)，顯示為“達(dá)標(biāo)”。這樣的效果，不妨稱之為“條件顯示”，用IF函數(shù)可以實(shí)現(xiàn)。假設(shè)分?jǐn)?shù)在B3單元格中，要將等級(jí)顯示在C3單元格中。那么在C3單元格中輸入以下公式實(shí)現(xiàn)不同的等級(jí):=if(b3<=10000,“不達(dá)標(biāo)”，“達(dá)標(biāo)”)

分“不達(dá)標(biāo)”和“達(dá)標(biāo)”2個(gè)等級(jí)

=if(b3<=10000,“不達(dá)標(biāo)”，if(b3<=35000,“達(dá)標(biāo)”，“優(yōu)秀”))

注意:符號(hào)為半角，IF與括弧之間不能有空格，而且最多嵌套7層。1、假設(shè)條件函數(shù)IF含義及運(yùn)用二、常用函數(shù)學(xué)習(xí)與應(yīng)用IF函數(shù)含義：判斷，根據(jù)邏輯計(jì)算的真假值，返回不同結(jié)果?？梢允褂煤瘮?shù)IF對(duì)數(shù)值和公式進(jìn)行條件檢測(cè)。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:$B$21)；示例：3、

VLOOKUP的運(yùn)用VLOOKUP(需在第一列中查找的數(shù)據(jù),需要在其中查找數(shù)據(jù)的數(shù)據(jù)表,需返回某列值的列號(hào),邏輯值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為FALSE，table_array不必進(jìn)行排序。⑵Table_array的第一列中的數(shù)值可以為文本、數(shù)字或邏輯值。若為文本時(shí)，不區(qū)分文本的大小寫。

3.Col_index_num為table_array中待返回的匹配值的列序號(hào)。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_num大于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。示例4、函數(shù)公式出錯(cuò)信息（7個(gè)）#DIV/0——零作除數(shù)#NAME?——在公式中使用了不能識(shí)別的名稱刪除了公式中使用的名稱，或者使用了不存在的名稱。函數(shù)名的拼寫錯(cuò)誤#VALUE！——使用了不正確的參數(shù)或運(yùn)算符在需要數(shù)字或邏輯值時(shí)輸入了文本#REF！——引用了無(wú)效的單元格地址刪除了由其它公式引用的單元格將移動(dòng)單元格粘貼到由其它公式引用的單元格中。#NULL！——指定了兩個(gè)并不相交的區(qū)域，故無(wú)效使用了不正確的區(qū)域運(yùn)算符或不正確的單元格引用。#N/A——當(dāng)在函數(shù)或公式中引