網(wǎng)優(yōu)必備-EXCEL使用說明

1、Excel使用說明2009-6-16目錄一、Excel簡介3二、Excel 常用操作31、Shift+Ctrl+方向鍵32、快速選擇每列或每行的首位和末尾單元格。43、CTRL+鼠標左鍵拖動（復(fù)制單元格內(nèi)容到指定范圍）44、靈活使用狀態(tài)欄65、篩選功能66、條件格式功能78、選擇性粘貼89、數(shù)據(jù)分列功能910、凍結(jié)窗口功能911、自動換行功能1012、剪切，粘貼命令的鼠標操作1013、強制換行功能1014、自動設(shè)定合適行高列寬1115、查找及替換功能1116、設(shè)置數(shù)據(jù)有效性1117、排序功能1218、合并單元格中的數(shù)據(jù)1319、數(shù)據(jù)透視表14三、Excel常用函數(shù)151、數(shù)學(xué)函數(shù)151)Sum

2、函數(shù)152)Subtotal函數(shù)163)Sumif函數(shù)174)Average 函數(shù)175) Max函數(shù)186)Min函數(shù)187)ABS函數(shù)188)Hex2dec函數(shù)189)Dec2hex函數(shù)192、文本函數(shù)191)Lift函數(shù)192)Right函數(shù)193)Mid函數(shù)194)Substitute函數(shù)193、邏輯函數(shù)201)If函數(shù)202)And函數(shù)213)Or函數(shù)214、查找函數(shù)211)Vlookup函數(shù)21四、Excel中插入圖表22五、Excel中錄制宏26六、Excel常用快捷鍵27七、總結(jié)28一、 Excel簡介Excel 在網(wǎng)絡(luò)優(yōu)化工作中起的作用，是不言而喻的。比如利用Excel制作工

3、參、分析話統(tǒng)指標、統(tǒng)計數(shù)據(jù)等等。靈活的使用Excel可以極大的提高我們的工作效率，以下是我工作之余總結(jié)的Excel常用功能，希望對大家工作有所益處。二、 Excel 常用操作1、Shift+Ctrl+方向鍵 很多情況下，我們需要選取表格中的數(shù)據(jù)，一般情況下都是通過使用拖動鼠標的方式，但是在數(shù)據(jù)量比較大的時候，比較難于控制，經(jīng)常回出現(xiàn)多選或則少選的情況。使用以上組合鍵就可以避免這樣的問題。 它們實現(xiàn)的功能是：從當前單元格開始，按照選定的方向選取數(shù)據(jù)，遇到第一個空單元格時結(jié)束。 如下圖所示：2、快速選擇每列或每行的首位和末尾單元格。很多情況下，我們需要選取一列中末尾的單元格，或者首位的單元格，如果

4、數(shù)據(jù)少的話，我們可以滑動鼠標軸選擇，但是當數(shù)據(jù)量大時，這樣很費時間。我們可以選擇對應(yīng)列或者行中的任意單元格，然后雙擊此單元格的上邊框，就可以選擇此列最首位的單元格，同樣，雙擊此單元格的下邊框，就可以選擇最末尾的單元格，同理可以雙擊左邊框或者右邊框。如下圖所示：3、CTRL+鼠標左鍵拖動（復(fù)制單元格內(nèi)容到指定范圍）有時，需要把當前單元格內(nèi)容復(fù)制到其他單元格中，這時有兩種情況：數(shù)值單元格和非數(shù)值單元格，對于數(shù)值單元格，有兩種情況：一是值遞增復(fù)制，二是等值復(fù)制。使用 CTRL+鼠標左鍵拖動 就可以在這兩種情況之間切換。如下圖所示：數(shù)值型單元格遞增復(fù)制: 數(shù)值型單元格等值復(fù)制: 非數(shù)值單元格:同樣可以

5、選取多個單元格，按住CTRL 移動鼠標到單元格的右下角，當出現(xiàn)十字形狀時開始拖動鼠標，在選定位置釋放鼠標。有時我們可以直接雙單元格右下角的+字，這樣可以直接填充下面的單元格。然后單擊按鈕，可以選擇“復(fù)制單元格”或者“以序列方式填充”等方式。如下圖所示：4、靈活使用狀態(tài)欄在Excel 的左下角，有一個狀態(tài)欄，提供了許多有用的功能與函數(shù)對選定的范圍進行處理，如：記數(shù)，求和，求平均數(shù)，求最大/最小值等。在狀態(tài)欄上，單擊鼠標右鍵就可以調(diào)出這個功能。如下所示： 5、篩選功能EXSL可以對數(shù)據(jù)進行篩選，篩選分為自動篩選和高級篩選。我們經(jīng)常會使用這兩個功能用于篩選一些數(shù)據(jù)。自動篩選功能：快捷鍵：Alt+D+

6、F+F，選擇自動篩選后，會在選中的單元格或者最上面的一排顯示 按鈕，然后點擊此按鈕，會出現(xiàn)相應(yīng)的對話框，我們可以選擇相應(yīng)的選項，如下圖所示：高級篩選功能：快捷鍵：Alt+D+F+A。有些時候我們會從一些重復(fù)的數(shù)據(jù)中篩選出不重復(fù)的數(shù)據(jù)。比如我們統(tǒng)計某個BSC所有小區(qū)一天（按每小時為單位）的話務(wù)量，此時提取的數(shù)據(jù)中每小區(qū)會有24次每小時的話務(wù)量，但是我們需要的統(tǒng)計一天這個小區(qū)的話務(wù)量，此時我們需要將這些小區(qū)進行高級篩選，將其不重復(fù)的篩選出來，再利用SUMIF函數(shù)對其進行求和，如下圖所示：6、條件格式功能 有時候，我們需要根據(jù)數(shù)據(jù)的特征把它們區(qū)別開來，以便一目了然，比如某小區(qū)是否開通EDGE，或者有

7、無直放站等，操作方法：首先選擇你想使用條件格式的單元格（可以為一個范圍），然后 選擇 格式-條件格式，如下圖所示：8、選擇性粘貼我們經(jīng)常會使用一些函數(shù)，但是用完函授后，單元格中的數(shù)值會包含公式，此時我們會運用選擇性粘貼去除函數(shù)。具體操作：首先對含有函數(shù)的單元格進行復(fù)制，然后右擊選擇“選擇性粘貼”，選擇“數(shù)值”即可?？旖萱I：Alt+E+S+V，如下圖所示：有時候我們還可以使用這個方法進行少量數(shù)據(jù)進行轉(zhuǎn)置，只需在“選擇性粘貼”的窗口中選擇“轉(zhuǎn)置”即可。9、數(shù)據(jù)分列功能 在制作工參時我們需要把小區(qū)名進行分列，分離出基站名，快捷鍵：Alt+D+E，選中需要分列的數(shù)據(jù)后，具體操作如下圖所示： 同樣我們可

8、以使用分列功能，將CGI分離出LAC、CI。只是選擇固定寬度，手動再選擇寬度即可。（我們也可是使用LEFT、RIGHT、MID函數(shù)分離LAC、CI）具體步驟如下：10、凍結(jié)窗口功能有時候我們在看數(shù)據(jù)時，希望將某一行數(shù)據(jù)的凍結(jié)不動，此時我們會使用凍結(jié)窗口功能，快捷鍵：Alt+W+F。具體操作如下：11、自動換行功能 有時候，一個單元格中的文字太多，在此單元格中不能完全顯示，我們可以使用自動換行，讓其在單元格中完全顯示。首先選擇需要調(diào)整的單元格，右擊選擇“設(shè)置單元格格式”，選擇“對齊”窗口，選擇“自動換行”，快捷鍵：Ctrl+1，操作如下圖所示：同樣我們可以再單元格設(shè)置窗口中設(shè)置數(shù)值的格式，比如小

9、數(shù)點位、百分比、文本等格式。12、剪切，粘貼命令的鼠標操作 一般情況下，我們都是采用這兩個命令來完成復(fù)制和粘貼，但是使用鼠標可以一次完成。具體操作：選擇需要剪切的范圍，移動鼠標到選中范圍的右上角，當出現(xiàn)時，按住鼠標左鍵拖動到希望粘貼的地方。13、強制換行功能 有些時候我們需要在一個單元格中輸入較長的文字，此時我們就用到了強制換行功能。選擇Alt+Enter即可進行強制換行，沒必要再去用空格將文本擠到下一行了14、自動設(shè)定合適行高列寬如果某單元格中文字未完全顯示，鼠標放在該列列標題的右邊界，雙擊，即可以設(shè)置為“最適合的列寬”。如果某單元格分行后未完全顯示，鼠標放在該行行標題的下邊界，雙擊，即可設(shè)

10、置為“最適合的行高”。如果所有行及列需要調(diào)整，只需全部選定單元格，然后雙擊某列的右邊框及某行的右邊框，即可自動調(diào)整好所有單元格的行高及列寬。15、查找及替換功能 我們經(jīng)常在工參中查找某些信息，或者將某些值替換為別的值。此時我們會使用使用查找及替換功能，快捷鍵：Ctrl+F，彈出查找及替換窗口，輸入需要查找的值即可。有些時候我們需要將表格中的空格去掉，只需在查找內(nèi)容中輸入“空格”，替換為“不填”即可把表格中的空格去除掉。如下圖所示：16、設(shè)置數(shù)據(jù)有效性有些時候我們?yōu)榱舜_保數(shù)據(jù)的正確而設(shè)定條件驗證、限制單元格數(shù)據(jù)的輸入。常見的有效性條件有:整數(shù)、小數(shù)、序列、日期、時間、文本長度。以上有效性條件除“

11、序列”之外，其他的都可設(shè)定一個區(qū)間值。比如在某單元格中設(shè)置工作地區(qū)為“紹興，湖州，嘉興，舟山”，操作步驟：選擇“數(shù)據(jù)”-“有效性”，在設(shè)置對話框中選擇“序列”，輸入“紹興，湖州，嘉興，舟山”即可，如下圖所示：17、排序功能1) 簡單排序很多情況下我們?yōu)榱烁拥姆奖愕姆治鰯?shù)據(jù)，經(jīng)常會對數(shù)據(jù)進行排序，簡單排序只針對數(shù)據(jù)清單中的某一列進行排序，一般情況下我們只需點擊工具欄中的“升序”、“降序”進行排序。同時需要注意的是：如果對選定區(qū)域旁邊有數(shù)據(jù)，會提示是否“擴展選定區(qū)域”，或者只以“當前選定區(qū)域排序”，一般情況下我們選擇“擴展選定區(qū)域”，因為只對選定區(qū)域排序而未改變相鄰單元格的排序，會造成整個數(shù)據(jù)順

12、序變亂。具體區(qū)別如下圖所示：2) 復(fù)雜排序 如果在數(shù)據(jù)清單中首先被選擇的關(guān)鍵字段的值是相同的，往往要再按另一字段的值來排序，那么前者稱為主關(guān)鍵字，后者稱為此關(guān)鍵字，還可以有第三關(guān)鍵字，這樣的操作用菜單命令來實現(xiàn)排序操作。如下：18、合并單元格中的數(shù)據(jù)許多情況下我們需要合并兩個單元格中的數(shù)據(jù)，比如合并NCC和BCC，這樣我們會用到“&”（和號）運算符，如下所示：19、數(shù)據(jù)透視表數(shù)據(jù)透視表是一種自動提取、組織和匯總數(shù)據(jù)的交互表，利用它可以使我們能夠簡介的全面的對數(shù)據(jù)重新組織和統(tǒng)計數(shù)據(jù)。比如有一些重復(fù)的數(shù)據(jù)需要篩選出不重復(fù)的數(shù)據(jù)并統(tǒng)計各個數(shù)據(jù)出現(xiàn)的次數(shù)，具體操作如下：首先單擊“數(shù)據(jù)”-“數(shù)據(jù)透視表和

13、數(shù)據(jù)透視圖”命令，如下圖所示：點擊“下一步”選擇需要統(tǒng)計的數(shù)據(jù)：然后點擊“下一步”選擇“新建工作表”：點擊“完成”，會出現(xiàn)如下對話框：將需要統(tǒng)計的數(shù)據(jù)拖到相應(yīng)的位置：這樣就會篩選出不重復(fù)出現(xiàn)的數(shù)據(jù)，并且還統(tǒng)計出了各個數(shù)值出現(xiàn)的次數(shù)。比高級篩選方法還要簡單。以上只是數(shù)據(jù)透視表中的一小部分功能，其余的功能需要大家在實踐中不斷的摸索。三、 Excel常用函數(shù)1、數(shù)學(xué)函數(shù)1) Sum函數(shù)主要功能：計算所有參數(shù)數(shù)值的和。 使用格式：SUM（Number1,Number2） 參數(shù)說明：Number1、Number2代表需要計算的值，可以是具體的數(shù)值、引用的單元格（區(qū)域）、邏輯值等。 應(yīng)用舉例：如下圖所示，

14、在A10單元格中輸入公式：=SUM(A1:A9)，確認后即可求出以上單元格的和。 2) Subtotal函數(shù)主要功能：返回列表或數(shù)據(jù)庫中的分類匯總。 使用格式：SUBTOTAL(function_num, ref1, ref2, .) 參數(shù)說明：Function_num為1到11（包含隱藏值）或101到111（忽略隱藏值）之間的數(shù)字，用來指定使用什么函數(shù)在列表中進行分類匯總計算；ref1, ref2,代表要進行分類匯總區(qū)域或引用，不超過29個。應(yīng)用舉例：一般情況下我們利用這個函數(shù)對篩選的單元格進行求和，比如我們在B11單元格中輸入“=SUBTOTAL(9,A2:A10)”，就會求出當前篩選結(jié)果

15、值的和。如下圖所示：特別提醒：如果采取自動篩選，無論function_num參數(shù)選用什么類型，SUBTOTAL函數(shù)忽略任何不包括在篩選結(jié)果中的行；SUBTOTAL函數(shù)適用于數(shù)據(jù)列或垂直區(qū)域，不適用于數(shù)據(jù)行或水平區(qū)域。3) Sumif函數(shù)主要功能：計算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值和。 使用格式：SUMIF（Range,Criteria,Sum_Range） 參數(shù)說明：Range代表條件判斷的單元格區(qū)域；Criteria為指定條件表達式；Sum_Range代表需要計算的數(shù)值所在的單元格區(qū)域。 應(yīng)用舉例： 比如我們從某個BSC所有小區(qū)的話務(wù)量的數(shù)據(jù)中，提取某小區(qū)載頻級一周的話務(wù)量。如下圖所示：在

16、F3單元格中輸入“=SUMIF(C:C,F2,D:D)”即可，意思是：如果在C列中有值等于F2的，就對D列中的值進行求和。4) Average 函數(shù) 主要功能：求出所有參數(shù)的算術(shù)平均值。 使用格式：AVERAGE(number1,number2,) 參數(shù)說明：number1,number2,：需要求平均值的數(shù)值或引用單元格（區(qū)域），參數(shù)不超過30個。 應(yīng)用舉例：同SUM函數(shù)用法相同，不再舉例。 特別提醒：如果引用區(qū)域中包含“0”值單元格，則計算在內(nèi)；如果引用區(qū)域中包含空白或字符單元格，則不計算在內(nèi)。 5) Max函數(shù)主要功能：求出一組數(shù)中的最大值。 使用格式：MAX(number1,numbe

17、r2) 參數(shù)說明：number1,number2代表需要求最大值的數(shù)值或引用單元格（區(qū)域），參數(shù)不超過30個。 應(yīng)用舉例：同SUM函數(shù)用法相同，不再舉例。特別提醒：如果參數(shù)中有文本或邏輯值，則忽略。 6) Min函數(shù)主要功能：求出一組數(shù)中的最小值。 使用格式：MIN(number1,number2) 參數(shù)說明：number1,number2代表需要求最小值的數(shù)值或引用單元格（區(qū)域），參數(shù)不超過30個。 應(yīng)用舉例：同SUM函數(shù)用法相同，不再舉例。特別提醒：如果參數(shù)中有文本或邏輯值，則忽略。 7) ABS函數(shù)主要功能：求出相應(yīng)數(shù)字的絕對值。 使用格式：ABS(number) 參數(shù)說明：number

18、代表需要求絕對值的數(shù)值或引用的單元格。 應(yīng)用舉例：如果在B2單元格中輸入公式：=ABS(A2)，則在A2單元格中無論輸入正數(shù)（如100）還是負數(shù)（如-100），B2中均顯示出正數(shù)（如100）。特別提醒：如果number參數(shù)不是數(shù)值，而是一些字符（如A等），則B2中返回錯誤值“#VALUE！”8) Hex2dec函數(shù) 主要功能：將十六進制數(shù)轉(zhuǎn)換為十進制數(shù)。使用格式：HEX2DEC(number) 參數(shù)說明：number代表需轉(zhuǎn)化的數(shù)值或引用的單元格。應(yīng)用舉例：一般情況下，BSC32中CGI為十六進制的，這樣我們就利用此函數(shù)進行轉(zhuǎn)換，比如在B2單元格中輸入公式：= HEX2DEC (783)，確認

19、后就會輸出1923。9) Dec2hex函數(shù)將十進制數(shù)轉(zhuǎn)換為十六進制數(shù)，用法同Hex2dec函數(shù)2、文本函數(shù)1) Lift函數(shù)主要功能：從一個文本字符串的第一個字符開始，截取指定數(shù)目的字符。使用格式：LEFT(text,num_chars) 參數(shù)說明：text代表要截字符的字符串；num_chars代表給定的截取數(shù)目。應(yīng)用舉例：在制作工參時，我們會提取小區(qū)名左邊的幾個字作為地區(qū)名，例如：在B2中輸入“=LEFT(A2,2)”就會分離出地區(qū)名。如下所示：2) Right函數(shù) 主要功能：從一個文本字符串的最后一個字符開始，截取指定數(shù)目的字符。 使用格式：RIGHT(text,num_chars)

20、參數(shù)說明：text代表要截字符的字符串；num_chars代表給定的截取數(shù)目。 應(yīng)用舉例：使用方法同Left函數(shù)，但是只是提取的右邊的字符。3) Mid函數(shù) 主要功能：從一個文本字符串的指定位置開始，截取指定數(shù)目的字符。 使用格式：MID(text,start_num,num_chars) 參數(shù)說明：text代表一個文本字符串；start_num表示指定的起始位置；num_chars表示要截取的數(shù)目。 應(yīng)用舉例：在系統(tǒng)優(yōu)化時經(jīng)常會提取一段字符中的一段字符，比如提取A2單元格“我愛網(wǎng)優(yōu)”中間的兩位字符，只需要輸入“=MID（A2，2，2）”即可。4) Substitute函數(shù)主要功能：需要在某一

21、文本字符串中替換指定的文本。 使用格式：SUBSTITUTE(text,old_text,new_text,instance_num) 參數(shù)說明：Text 為需要替換其中字符的文本，或?qū)形谋镜膯卧竦囊?。Old_text 為需要替換的舊文本。New_text 用于替換 old_text 的文本。Instance_num 為一數(shù)值，用來指定以 new_text 替換第幾次出現(xiàn)的 old_text。如果指定了 instance_num，則只有滿足要求的 old_text 被替換；否則將用 new_text 替換 Text 中出現(xiàn)的所有 old_text。應(yīng)用舉例： 有些情況下在制作工參時，提

22、取的頻點信息中包含BCCH頻點，因此制作TCH頻點時需要將BCCH頻點替換掉，例如在C2中輸入“=SUBSTITUTE(A:A,;&B2,)”，即可把BCCH頻點替換掉，如下圖所示：3、邏輯函數(shù)1) If函數(shù)主要功能：根據(jù)對指定條件的邏輯判斷的真假結(jié)果，返回相對應(yīng)的內(nèi)容。 使用格式：=IF(Logical,Value_if_true,Value_if_false) 參數(shù)說明：Logical代表邏輯判斷表達式；Value_if_true表示當判斷條件為邏輯“真（TRUE）”時的顯示內(nèi)容，如果忽略返回“TRUE”；Value_if_false表示當判斷條件為邏輯“假（FALSE）”時的顯示內(nèi)容，如

23、果忽略返回“FALSE”。 應(yīng)用舉例：我們經(jīng)常利用IF函數(shù)進行數(shù)據(jù)的分類，如果分類較多的情況下我們會利用IF函數(shù)嵌套。比如，要篩選出E列中小于等于3的值為“小”，大于3小于等于9的為“大”，大于9的為“很大”，只需輸入公式“=IF(E23,IF(E2=60,B5=60)，確認。如果C5中返回TRUE，說明A5和B5中的數(shù)值均大于等于60，如果返回FALSE，說明A5和B5中的數(shù)值至少有一個小于60。 3) Or函數(shù)主要功能：返回邏輯值，僅當所有參數(shù)值均為邏輯“假（FALSE）”時返回函數(shù)結(jié)果邏輯“假（FALSE）”，否則都返回邏輯“真（TRUE）”。 使用格式：OR(logical1,logi

24、cal2, .) 參數(shù)說明：Logical1,Logical2,Logical3：表示待測試的條件值或表達式，最多這30個。 應(yīng)用舉例：在C62單元格輸入公式：=OR(A62=60,B62=60)，確認。如果C62中返回TRUE，說明A62和B62中的數(shù)值至少有一個大于或等于60，如果返回FALSE，說明A62和B62中的數(shù)值都小于60。 4、查找函數(shù)1) Vlookup函數(shù)主要功能：在數(shù)據(jù)表的首列查找指定的數(shù)值，并由此返回數(shù)據(jù)表當前行中指定列處的數(shù)值。 使用格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 參數(shù)說明

25、：Lookup_value代表需要查找的數(shù)值；Table_array代表需要在其中查找數(shù)據(jù)的單元格區(qū)域；Col_index_num為在table_array區(qū)域中待返回的匹配值的列序號（當Col_index_num為2時,返回table_array第2列中的數(shù)值，為3時，返回第3列的值）；Range_lookup為一邏輯值，如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果為FALSE，則返回精確匹配值，如果找不到，則返回錯誤值#N/A。 應(yīng)用舉例：Lookup函數(shù)是非常重要的，可以說無時無刻不再用他。比如通過CI來索引小區(qū)的BCCH，在I2單元格中輸入“=VLOOKUP(H2,E:F,2,0)”，表示在E列中查找需要的CI，返回F列中匹配值，下拉單元格直接填充單元格即可。如下所示：四、 Excel中插入圖表我們在寫測試報告的時候，經(jīng)常需要對DT的指標進行統(tǒng)計，然后制作成圖表，這樣看起來會更加的形象直觀。但是同樣的數(shù)據(jù)，有些人制作的圖標非常的漂亮，而有些人制作的圖表看起來非常的生硬，不美觀，下面將介紹如何制作出漂亮的圖表。首先在主菜單欄中選擇“插入”，接著選擇“