Excel：數(shù)據(jù)篩選、排序、公式、函數(shù)簡單入門

1、Excel：數(shù)據(jù)篩選、排序、公式、函數(shù)的簡單入門前言：版本之爭1上篇：數(shù)據(jù)篩選、排序與制表規(guī)范2下篇：公式與函數(shù)3后記：上網(wǎng)求助4版本之爭Excel 2003：手動擋轎車：手動擋轎車Excel 2007、2010：自動擋轎車：自動擋轎車版本之爭 Excel 2003的現(xiàn)狀：的現(xiàn)狀：用戶依然占大多數(shù)用戶依然占大多數(shù)2007版或版或2010版的某些功能的確強(qiáng)版的某些功能的確強(qiáng)過過2003版，但基礎(chǔ)功能卻大同小異，甚至版，但基礎(chǔ)功能卻大同小異，甚至沒有變化。沒有變化。 本報告立足基礎(chǔ)，示例用本報告立足基礎(chǔ)，示例用Excel 2003版，但盡量不涉及版本功能。版，但盡量不涉及版本功能。數(shù)據(jù)篩選、排序與

2、制表規(guī)范引例下表是下表是來訪來訪表的一部分，有問題嗎表的一部分，有問題嗎第一宗罪日期錄入不規(guī)范日期該如何錄入？格式統(tǒng)一就行嗎？日期該如何錄入？格式統(tǒng)一就行嗎？日期常見的錯誤錄入方式“點點”型型以以“.”為分隔符錄入的為分隔符錄入的日期。日期。比如：2011.1.10；13.05.08?！翱崭窨崭瘛毙托鸵砸浴?”為分隔符錄入的為分隔符錄入的日期。日期。比如：2011 1 10；13 05 08。日期常見的正確錄入方式“減號減號”型型以以“-”為分隔符錄入為分隔符錄入的日期。的日期。比如：2011-1-10；2013-5-8?！罢备苷备堋毙托鸵砸浴?”為分隔符錄入為分隔符錄入的日期。的日期。比

3、如：2011/1/10；2013/5/8。示例檢驗日期是否正確方法：將單元格格式設(shè)置為方法：將單元格格式設(shè)置為“常規(guī)常規(guī)”，如果日期變?yōu)橐唤M數(shù)字，就正確。如果日期變?yōu)橐唤M數(shù)字，就正確。日期的本質(zhì)Excel默認(rèn)的起始日期為默認(rèn)的起始日期為1900-1-1，代表第一天，用數(shù)字代表第一天，用數(shù)字1表示。之后每增表示。之后每增加一天，數(shù)字就增加加一天，數(shù)字就增加1。2011-1-10距離1900-1-1有40552天，40552+1=40553，因此40553就代表了2011-1-10。 兩個日期可以做減法，相減的結(jié)果兩個日期可以做減法，相減的結(jié)果為它們相隔的天數(shù)。為它們相隔的天數(shù)。示例小技巧快捷鍵輸

4、入當(dāng)前日期示例整改方案日期按正確的方式錄入。日期按正確的方式錄入。整改過程整改過程整改結(jié)果整改結(jié)果示例第二宗罪合并單元格嚴(yán)重破壞了數(shù)據(jù)結(jié)構(gòu)合并單元格很工整，有什么問題嗎？合并單元格很工整，有什么問題嗎？往2行表頭的危害問題1：無法使用的排序合并單元格會造成整個數(shù)據(jù)區(qū)域的合并單元格會造成整個數(shù)據(jù)區(qū)域的單元格大小不一。所以排序時，會提單元格大小不一。所以排序時，會提示錯誤。示錯誤。示例問題2：篩選會出錯示例為什么會出錯是因為合并單元格以后，只有首個單元是因為合并單元格以后，只有首個單元格有數(shù)據(jù)，其他的都是空白單元格。格有數(shù)據(jù)，其他的都是空白單元格。如右圖，看起來F5：F6的數(shù)據(jù)內(nèi)容是“ * ”。但

5、其實只有F5有數(shù)據(jù)，F(xiàn)6對于Excel來說，值為空。所以，篩選所有售價為空白的數(shù)據(jù)明細(xì)，能得到三條記錄。小技巧“合并及居中”何時使用？主要是用于要打印的表格，可以使主要是用于要打印的表格，可以使表格看上去更加明晰。表格看上去更加明晰。整改方案取消取消“合并及居中合并及居中”，錄入數(shù)據(jù)在，錄入數(shù)據(jù)在一行內(nèi)完成。一行內(nèi)完成。整改過程整改過程整改結(jié)果整改結(jié)果示例第三宗罪數(shù)據(jù)殘缺有空白單元格沒數(shù)據(jù)就不填寫，不行嗎？單元格沒數(shù)據(jù)就不填寫，不行嗎？真空白VS假空白單元格有真空白和假空白之分。單元格有真空白和假空白之分。真空白是這個單元格真空白是這個單元格“真沒有真沒有”數(shù)據(jù)：數(shù)據(jù)：該單元格從未被填寫過該單

6、元格從未被填寫過曾經(jīng)填寫的數(shù)據(jù)已經(jīng)被完全刪除曾經(jīng)填寫的數(shù)據(jù)已經(jīng)被完全刪除真空白會影響數(shù)據(jù)分析結(jié)果，尤其是做計數(shù)統(tǒng)計。真空白VS假空白 假空白是這個單元格假空白是這個單元格“可以有可以有”數(shù)據(jù)。數(shù)據(jù)。假空白的單元格僅用肉眼看，格假空白的單元格僅用肉眼看，格內(nèi)什么都沒有。但是選中它再看編輯內(nèi)什么都沒有。但是選中它再看編輯欄，卻會發(fā)現(xiàn)編輯欄里有數(shù)據(jù)，內(nèi)容欄，卻會發(fā)現(xiàn)編輯欄里有數(shù)據(jù)，內(nèi)容為為=“”（英文的雙引號）。（英文的雙引號）。 這是一種空文本的概念。這是一種空文本的概念。示例空文本與0值空文本在空文本在Excel看來也是數(shù)據(jù)?？磥硪彩菙?shù)據(jù)。而在我們眼中，它是空白。這和這和0值一樣。值一樣。當(dāng)單元

7、格數(shù)值為0時，Excel也認(rèn)可該單元格里有數(shù)據(jù)，只不過數(shù)值為0罷了。 即：不管什么數(shù)值，只要有值就是即：不管什么數(shù)值，只要有值就是“非空單元格非空單元格”。最嚴(yán)謹(jǐn)?shù)臄?shù)據(jù)記錄方式在數(shù)值部分的空白單元格里填寫0值值在文本部分的空白單元格里填寫相應(yīng)的文本相應(yīng)的文本數(shù)據(jù)，數(shù)據(jù)，“無無”或或“空空”等等整改方案空白單元格都是文本性質(zhì)，均填寫空白單元格都是文本性質(zhì)，均填寫“無無”。方案一：逐個單元格錄入方案二：成片的復(fù)制、粘貼方案三：單元格一鍵批量錄入整改過程整改過程示例整改結(jié)果整改結(jié)果小技巧單元格一鍵批量錄入目的：在已經(jīng)選定的多個單元格中目的：在已經(jīng)選定的多個單元格中錄入相同的數(shù)據(jù)。錄入相同的數(shù)據(jù)。 方

8、法：方法：Ctrl+Enter。原理：Ctrl鍵有復(fù)制的功能； Enter代表單元格錄入完畢。把它們組合起來，即：將錄入完畢的數(shù)據(jù)同時復(fù)制到所有選定單元格。小技巧單元格一鍵批量錄入操作很簡單，僅需三步。操作很簡單，僅需三步。 第一步：選定多個單元格。第一步：選定多個單元格。小技巧單元格一鍵批量錄入 第二步：什么都不要動，直接敲鍵第二步：什么都不要動，直接敲鍵盤輸入內(nèi)容。盤輸入內(nèi)容。小技巧單元格一鍵批量錄入 第三步：壓住第三步：壓住Ctrl同時按同時按Enter，神奇效果馬上顯現(xiàn)！神奇效果馬上顯現(xiàn)！回整改方案整改結(jié)果整改結(jié)果整改結(jié)果第四宗罪多余的表頭該表頭占了該表頭占了2行，有什么問題嗎？行，有

9、什么問題嗎？2行表頭的實質(zhì)第2行（表頭第一行）只有兩項內(nèi)容：“性別”與“詢問重點”它們對Excel識別某列數(shù)據(jù)的屬性沒有任何幫助實際上僅僅是文字說明！實際上僅僅是文字說明！ 而其他部分，則用了合并單元格而其他部分，則用了合并單元格2行表頭的危害奇葩的篩選！奇葩的篩選！ 示例整改方案取消所有的合并單元格。如果文取消所有的合并單元格。如果文字說明必須保留，同時為了美觀，可字說明必須保留，同時為了美觀，可設(shè)置設(shè)置“跨列居中跨列居中”。整改過程整改過程示例整改結(jié)果整改結(jié)果小技巧單元格格式之跨列居中方法：在首個單元格輸入標(biāo)題，然方法：在首個單元格輸入標(biāo)題，然后選中區(qū)域，調(diào)出單元格格式設(shè)置，后選中區(qū)域，調(diào)

10、出單元格格式設(shè)置，再切換到再切換到“對齊對齊”標(biāo)簽，將標(biāo)簽，將“水平對水平對齊齊”設(shè)置為設(shè)置為“跨列居中跨列居中”。小技巧單元格格式之跨列居中示例回整改方案整改結(jié)果整改結(jié)果整改結(jié)果制表規(guī)范正確的數(shù)據(jù)表應(yīng)滿足以下條件：正確的數(shù)據(jù)表應(yīng)滿足以下條件：一個標(biāo)題行；一個標(biāo)題行；無合并單元格；無合并單元格；數(shù)據(jù)錄入統(tǒng)一、規(guī)范；數(shù)據(jù)錄入統(tǒng)一、規(guī)范；數(shù)據(jù)區(qū)域中無空白單元格。數(shù)據(jù)區(qū)域中無空白單元格。數(shù)據(jù)篩選要求：篩選出要求：篩選出A置業(yè)顧問的所有來置業(yè)顧問的所有來訪客戶。訪客戶。示例數(shù)據(jù)排序要求：按能承受的總價，由高到低要求：按能承受的總價，由高到低對來訪客戶排序。對來訪客戶排序。示例數(shù)據(jù)排序要求：按能承受的總

11、價，由高到低要求：按能承受的總價，由高到低對來訪客戶排序。對來訪客戶排序。公式與函數(shù)公式的概念和原則公式：指對工作表中的數(shù)值執(zhí)行計公式：指對工作表中的數(shù)值執(zhí)行計算和操作的等式。算和操作的等式。公式必須遵循以下原則：以等號“=”作為開始。等號“=”之后緊接運算數(shù)和運算符。 公式由一系列數(shù)據(jù)和運算符組成。公式由一系列數(shù)據(jù)和運算符組成。公式運算數(shù)運算數(shù)即參加運算的參數(shù)，可以是運算數(shù)即參加運算的參數(shù)，可以是數(shù)值常量、單元格引用或函數(shù)等。數(shù)值常量、單元格引用或函數(shù)等。常量：直接輸入的數(shù)字或文本，是不用計算的值，如89.5，”#樓”等。單元格引用：引用某一單元格或單元格區(qū)域中的數(shù)據(jù)。函數(shù)：包括函數(shù)及它們的

12、參數(shù)。單元格引用分為兩種：引用某個單元格分為兩種：引用某個單元格A1；引用由一組單元格組成的數(shù)據(jù)區(qū)域引用由一組單元格組成的數(shù)據(jù)區(qū)域A1:B5。A1:B5是指以A1為左上角，B5為右下角的單元格集合去往文本連接運算符表格的智慧你是否遇到過，無論刪除行或列還是插入行或列，Excel都能準(zhǔn)確無誤的計算，似乎表格的變動早在它的意料之中。 這歸功于單元格引用的相對性。這歸功于單元格引用的相對性。相對引用和絕對引用根據(jù)單元格引用的相對性，可分為相對根據(jù)單元格引用的相對性，可分為相對引用引用(A1)、行絕對引用、行絕對引用(A$1)、列絕對引用、列絕對引用($A1)和絕對引用和絕對引用($A$1)四種情況。

13、四種情況。 它們之間的差別在于是否有它們之間的差別在于是否有“$”符符號，以及號，以及“$”符號在哪里。符號在哪里?！?”符號的意義可以理解為可以理解為“鎖定鎖定”，即在公式復(fù)，即在公式復(fù)制的過程中，不允許引用隨公式所在制的過程中，不允許引用隨公式所在位置的變化而變化。位置的變化而變化。小技巧一鍵切換引用類型示例相對引用復(fù)制或填充公式時，根據(jù)目標(biāo)位置復(fù)制或填充公式時，根據(jù)目標(biāo)位置自動調(diào)節(jié)公式中的相對地址。自動調(diào)節(jié)公式中的相對地址。向右向下復(fù)制示例絕對引用復(fù)制或填充公式時，不改變公式中復(fù)制或填充公式時，不改變公式中的絕對地址。的絕對地址。向右向下復(fù)制示例行絕對引用復(fù)制或填充公式時，只改變公式中復(fù)

14、制或填充公式時，只改變公式中的列地址，不改變行地址。的列地址，不改變行地址。向右向下復(fù)制示例列絕對引用復(fù)制或填充公式時，只改變公式中復(fù)制或填充公式時，只改變公式中的行地址，不改變列地址。的行地址，不改變列地址。向右向下復(fù)制示例公式運算符是連接公式中的基本元素并完成特是連接公式中的基本元素并完成特定計算的符號。定計算的符號。例如：=A1 + A2 * 10000運算符分為四類：算術(shù)運算符比較運算符引用運算符文本連接運算符算術(shù)運算符用于簡單的數(shù)學(xué)計算。用于簡單的數(shù)學(xué)計算。常用：+加法 -減法 *乘法 /除法不常用：幾次方 %百分比 -負(fù)數(shù)注意：注意：=A1+A2，A1、A2分別指的是分別指的是A1

15、、A2單單元格中的數(shù)據(jù)。元格中的數(shù)據(jù)。示例比較運算符用于比較兩個數(shù)值的大小，并產(chǎn)生用于比較兩個數(shù)值的大小，并產(chǎn)生邏輯值邏輯值TRUE(真真)或或FALSE(假假)。例如：=A1 A2 比較運算符包括等于“=”、大于“”、小于“=”、小于等于“=”、不等于“”六種示例引用運算符用來引用單元格區(qū)域。用來引用單元格區(qū)域。最常用的就是：區(qū)域運算符： （冒號）表示引用相鄰的單元格區(qū)域。例如A1：B5文本連接運算符&，用于連接文本字符串。，用于連接文本字符串。例如：=C3 & D3注意：文本常量需要參注意：文本常量需要參與公式運算，必須將其與公式運算，必須將其包含在半角雙引號之間，包含在半

16、角雙引號之間，否則會報錯。否則會報錯。文本連接運算符示例函數(shù)的概念與結(jié)構(gòu)函數(shù)是在公式中使用的一種內(nèi)部工函數(shù)是在公式中使用的一種內(nèi)部工具，是預(yù)先編寫好的，按特定的順序具，是預(yù)先編寫好的，按特定的順序或結(jié)構(gòu)執(zhí)行計算的特殊公式?；蚪Y(jié)構(gòu)執(zhí)行計算的特殊公式。函數(shù)的一般結(jié)構(gòu)是：函數(shù)名稱（參數(shù)1，參數(shù)2，）參數(shù)是函數(shù)的輸入值，可以是常量、單元格引用、邏輯值或其他的函數(shù)等。函數(shù)公式的結(jié)構(gòu)= IF (A10, SUM (B1:BG), 0)函數(shù)結(jié)構(gòu)嵌套函數(shù)函數(shù)名稱參數(shù)函數(shù)中的括號與逗號都應(yīng)該是半角符函數(shù)中的括號與逗號都應(yīng)該是半角符號，即英文輸入法下的括號與逗號。號，即英文輸入法下的括號與逗號。函數(shù)的輸入對話框方

17、法一：使用方法一：使用“插入函數(shù)插入函數(shù)”、“函函數(shù)參數(shù)數(shù)參數(shù)”對話框。對話框。函數(shù)的輸入對話框方法一：使用方法一：使用“插入函數(shù)插入函數(shù)”、“函函數(shù)參數(shù)數(shù)參數(shù)”對話框。對話框。函數(shù)的輸入對話框方法一：使用方法一：使用“插入函數(shù)插入函數(shù)”、“函函數(shù)參數(shù)數(shù)參數(shù)”對話框。對話框。示例函數(shù)的輸入手工輸入方法二：直接在單元格中依次輸入。方法二：直接在單元格中依次輸入。正在編輯的參數(shù)會正在編輯的參數(shù)會加粗顯示加粗顯示函數(shù)的輸入手工輸入方法二：直接在單元格中依次輸入。方法二：直接在單元格中依次輸入。示例函數(shù)示例按指定數(shù)位四舍五入使用使用ROUND函數(shù)按指定位數(shù)對數(shù)值進(jìn)函數(shù)按指定位數(shù)對數(shù)值進(jìn)行四舍五入。行四舍五入。語法為：ROUND(number,num_digits)參數(shù)number是需要進(jìn)行四舍五入的數(shù)字；num_digits為指定的位數(shù)。如果參數(shù) num_digits 大于 0，則四舍五入到指定的小數(shù)位；如果等于 0，則四舍五入到最接近的整數(shù)；如果小于 0，則四舍五入到小數(shù)點左側(cè)指定位數(shù)。去往尾頁函數(shù)示例按指定數(shù)位四舍五入例如對數(shù)值12.13、3.75、-3.75、-12.13四舍五入。示例應(yīng)用應(yīng)用房款=房屋單價*建筑面積。房款在合同中是需要四舍五入取整的。示例上網(wǎng)求助上網(wǎng)求助網(wǎng)絡(luò)時代的好處：遇到疑難問題，可以在網(wǎng)上搜索到各種答案。 上網(wǎng)求助的關(guān)鍵在于能提出合適的上