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ù)項7、數(shù)據(jù)透視表應(yīng)用8、保護工作簿和工作表9、把多個表格匯總求和成一個匯總表10、身份證號碼輸入的幾種解決方法11、日期格式的轉(zhuǎn)換12、去空格函數(shù)13、怎樣快速去除表中不同行和列的空格二、常用函數(shù)學(xué)習(xí)與應(yīng)用1、假設(shè)條件函數(shù)IF含義及運用2、RANK函數(shù)的應(yīng)用3、

VLOOKUP的運用4、函數(shù)公式出錯信息1、如何在EXCEL中快速定位最后一行數(shù)據(jù)如果“定位”就是選中的意思，可按CTRL+END鍵實現(xiàn)。CTRL+↓

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

3、在Excel中行列快速轉(zhuǎn)換在Excel默認(rèn)情況下，零值將顯示為0，這個值是一個比較特殊的數(shù)值。如果工作表中包含了大量的零值，會使整個工作表顯得十分凌亂。如果要隱藏工作表中所有的零值，可以這樣操作：選擇“工具”→“選項”，打開“選項”對話框，單擊“視圖”標(biāo)簽，在“窗口選項”里把“零值”復(fù)選框前面的對號去掉，單擊“確定”按鈕。此時，可以看到原來顯示有0的單元格全部變成了空白單元格。4、在工作表中隱藏所有零值小提示：若要在單元格里重新顯示0，用上述方法把“零值”復(fù)選框前面的打上對號即可。5、數(shù)據(jù)有效性功能（限制錄入數(shù)據(jù)范圍、重復(fù)值、創(chuàng)建下拉列表、快捷輸入數(shù)據(jù)等）6、條件格式功能（生日提醒、標(biāo)記重復(fù)值、填充不同色等）7、數(shù)據(jù)透視表應(yīng)用數(shù)據(jù)透視表是一種交互式的表，可以進行某些計算，如求和與計數(shù)等。所進行的計算與數(shù)據(jù)在數(shù)據(jù)透視表中的排列有關(guān)。操作步驟

8.保護工作簿和工作表保護工作簿

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

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

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

方法3：還有一種用函數(shù)來解決的方法：在A1：A100已經(jīng)輸入大量的號碼時，在B1單元中輸入：=trim(''a1),注意兩個’之間是空格，這個公式的意思:先在A1單元15位號碼前加個空格符號，然后用trim這個函數(shù)把空格符號去掉。輸完后，B1單元也是顯示出完整的15位數(shù)字了。10.身份證號碼輸入的幾種解決方法將“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ù)如何刪去單元格中的空格，如姓名前，中，后的空格，即單元格中是兩個字的人名中間有一個空格，想刪去有何方法。如：中

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

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

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

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

注意:符號為半角，IF與括弧之間不能有空格，而且最多嵌套7層。1、假設(shè)條件函數(shù)IF含義及運用二、常用函數(shù)學(xué)習(xí)與應(yīng)用IF函數(shù)含義：判斷，根據(jù)邏輯計算的真假值，返回不同結(jié)果?？梢允褂煤瘮?shù)IF對數(shù)值和公式進行條件檢測。RANK含義：返回一個數(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的運用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為FALSE，table_array不必進行排序。⑵Table_array的第一列中的數(shù)值可以為文本、數(shù)字或邏輯值。若為文本時，不區(qū)分文本的大小寫。

3.Col_index_num為table_array中待返回的匹配值的列序號。Col_index_num為1時，返回table_array第一列中的數(shù)值；Col_index_num為2時，返回table_array第二列中的數(shù)值，以此類推。如果Col_index_num小于1，函數(shù)VLOOKUP返回錯誤值#VALUE!；如果Col_index_num大于table_array的列數(shù)，函數(shù)VLOOKUP

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