EXCEL常用功能與函數(shù)介紹--網(wǎng)優(yōu).doc

EXCEL常用功能與函數(shù)介紹A.EXCEL 簡(jiǎn)單介紹21.基站數(shù)據(jù)庫(kù)的維護(hù)22.話統(tǒng)數(shù)據(jù)的分析23.數(shù)據(jù)修改2B.EXCEL常用操作24.SHIFT+CTRL+方向鍵25.鼠標(biāo)左鍵拖動(dòng)（復(fù)制單元格內(nèi)容到指定范圍）36.同時(shí)更改多個(gè)插頁(yè)（worksheet）中同一位置的內(nèi)容47.剪切，粘貼命令的鼠標(biāo)操作58.狀態(tài)欄的靈活使用59.條件格式的使用510.數(shù)據(jù)的組合與分組711.下拉列表選擇框制作812.鼠標(biāo)雙擊進(jìn)行單元格內(nèi)容的的復(fù)制913.批量清空單元格內(nèi)容914.分頁(yè)操作1015.相對(duì)引用和絕對(duì)引用1116.設(shè)置打印區(qū)域1217.公式審核14C.常用函數(shù)1418.Vlookup 函數(shù)1419.HEX2DEX/DEC2HEX 函數(shù)1520.MID 函數(shù)1521.LEFT/RIGHT 函數(shù)1522.MAX/MIN 函數(shù)1623.SUMIF / COUNTIF1624.Countif 函數(shù)使用和SUMIF 函數(shù)基本類似1625.if1726.Large1727.Sumproduct 函數(shù)17D.宏的使用1828.宏組成結(jié)構(gòu)的簡(jiǎn)介19E.總結(jié)20 A. EXCEL 簡(jiǎn)單介紹Excel 對(duì)于網(wǎng)絡(luò)規(guī)劃優(yōu)化工程師來(lái)說(shuō)，是一個(gè)非常有用的工具。在網(wǎng)絡(luò)規(guī)劃過(guò)程中數(shù)據(jù)的采集與處理；在網(wǎng)絡(luò)優(yōu)化過(guò)程中網(wǎng)絡(luò)數(shù)據(jù)的修改，話統(tǒng)數(shù)據(jù)的分析， 我們面對(duì)的都是巨大的數(shù)據(jù)量；沒(méi)有Excel，有很多工作我們無(wú)從下手，Excel 在網(wǎng)規(guī)和網(wǎng)優(yōu)過(guò)程中起到了非常重要的作用?？偟恼f(shuō)來(lái)，Excel 在網(wǎng)規(guī)網(wǎng)優(yōu)中的應(yīng)用可以分成以下三個(gè)大的方面：1. 基站數(shù)據(jù)庫(kù)的維護(hù)通過(guò)使用Excel 建立包含：基站名稱，所屬BSC，所屬區(qū)域，基站類型，小區(qū)配置，割接時(shí)間，BCCH，TCH，CGI 等常用信息的數(shù)據(jù)庫(kù)以后，可以使我們無(wú)論對(duì)整個(gè)網(wǎng)絡(luò)還是具體到每一個(gè)基站有一個(gè)全方面的了解。 2. 話統(tǒng)數(shù)據(jù)的分析通過(guò)使用Excel提供的函數(shù)和功能，可以定制一些常用的話統(tǒng)模版。 在進(jìn)行日常的分析時(shí)，通過(guò)把原始數(shù)據(jù)導(dǎo)入到模版以后，Excel 將會(huì)自動(dòng)的計(jì)算出相關(guān)指標(biāo)；同時(shí)可以通過(guò)合理的使用Excel 提供的函數(shù)和報(bào)表的需求（切換成功率，掉話率等）制作報(bào)表模版。3. 數(shù)據(jù)修改在優(yōu)化過(guò)程中，當(dāng)需要對(duì)某些數(shù)據(jù)進(jìn)行修改時(shí)，可以使用Excel 對(duì)DBF 進(jìn)行處理，迅速查找到DBF 中需要修改的地方。B. EXCEL常用操作4. SHIFT+CTRL+方向鍵 很多情況下，我們需要選取表格中的數(shù)據(jù)，一般情況下都是通過(guò)使用拖動(dòng)鼠標(biāo)的方式，但是在數(shù)據(jù)量比較大的時(shí)候，比較難于控制，經(jīng)?；爻霈F(xiàn)多選或則少選的情況。使用以上組合鍵就可以避免這樣的問(wèn)題。它們實(shí)現(xiàn)的功能是：從當(dāng)前單元格開(kāi)始，按照選定的方向選取數(shù)據(jù)，遇到第一個(gè)空單元格時(shí)結(jié)束。 如下圖：圖 1：CTRL+SHIFT+方向鍵示意圖5. 鼠標(biāo)左鍵拖動(dòng)（復(fù)制單元格內(nèi)容到指定范圍）操作：選取需要復(fù)制的單元格（可以為多個(gè)），按住CTRL 移動(dòng)鼠標(biāo)到單元格的右下角，當(dāng)出現(xiàn)十字形狀時(shí)開(kāi)始拖動(dòng)鼠標(biāo)，在選定位置釋放鼠標(biāo)。對(duì)數(shù)值型單元格有遞增和等值兩種操作，對(duì)非數(shù)值型的單元格只有等值復(fù)制一種操作。 圖 2：鼠標(biāo)左鍵拖動(dòng)（遞增操作） 圖 3：鼠標(biāo)左鍵拖動(dòng)（等值操作）6. 同時(shí)更改多個(gè)插頁(yè)（worksheet）中同一位置的內(nèi)容操作：選取需要更改的所有表格，在需要更改的單元格內(nèi)輸入新內(nèi)容，如下圖圖 4：同時(shí)改變多個(gè)插頁(yè)（worksheet）中統(tǒng)一位置的內(nèi)容7. 剪切，粘貼命令的鼠標(biāo)操作操作：選擇需要剪切的范圍，移動(dòng)鼠標(biāo)到選中范圍的左上角，當(dāng)鼠標(biāo)變?yōu)?時(shí)，按住鼠標(biāo)左鍵拖動(dòng)到希望粘貼的地方。8. 狀態(tài)欄的靈活使用在Excel 的左下角，有一個(gè)狀態(tài)欄，其中提供了許多有用的功能與函數(shù)對(duì)選定的范圍進(jìn)行處理，如：記數(shù)，求和，求平均數(shù)，求最大/最小值等。圖 5：狀態(tài)欄的靈活使用9. 條件格式的使用 有時(shí)，我們需要根據(jù)數(shù)據(jù)的特征把它們區(qū)別開(kāi)來(lái)，以便一目了然，如：基站是否開(kāi)通等。如下圖：圖 6：條件格式的使用（標(biāo)注）操作：首先選擇想使用條件格式的單元格（也可以為一個(gè)范圍），然后 選擇 Format-Conditional format。 在如下窗口中進(jìn)行設(shè)置：圖 7：條件格式使用（設(shè)置）10. 數(shù)據(jù)的組合與分組在數(shù)據(jù)量比較大，表格中的列/行 相對(duì)較多時(shí)，可以把具有一定特征的數(shù)據(jù)組合在一起，需要時(shí)再展開(kāi)。這樣可以保證表格的整潔和信息的有效性。如下圖：圖 8：數(shù)據(jù)的組合與分組groupungroup操作：menu-Data-Group and outline。11. 下拉列表選擇框制作 在Excel 中經(jīng)?？煽吹较聢D所示的列表框圖 9：下拉列表框制作在Excel 中要實(shí)現(xiàn)這樣的 功能比較簡(jiǎn)單，具體步驟為：選擇 Excel 菜單中的 data-validation 彈出下圖窗口，按照下圖進(jìn)行設(shè)置：圖 10：下拉列表框的制作Input the data sourceMust be list12. 鼠標(biāo)雙擊進(jìn)行單元格內(nèi)容的的復(fù)制某一列已經(jīng)具有數(shù)據(jù)，此時(shí)可以用這一列作為標(biāo)志，通過(guò)鼠標(biāo)雙擊（鼠標(biāo)形狀變?yōu)?+ 時(shí)）來(lái)完成緊接著的下一列單元格的復(fù)制，復(fù)制結(jié)束的標(biāo)志為前一列第一個(gè)空單元格。（對(duì)公式的復(fù)制特別有用）13. 批量清空單元格內(nèi)容選擇需要清空內(nèi)容的單元格范圍，然后按”Delete” 鍵。同時(shí)也可以一次清空表格中的所有數(shù)據(jù)，操作方式為：鼠標(biāo)左鍵點(diǎn)擊表格左上角的空白處，選中整個(gè)表格，如下圖，然后按”Delete” 鍵清空整個(gè)表格。圖 11：批量清空單元表格14. 分頁(yè)操作有時(shí)我們需要把EXCEL 中的數(shù)據(jù)進(jìn)行分頁(yè)，方便打印。這時(shí)就會(huì)使用到EXCEL的分頁(yè)功能，如下圖圖 12：分頁(yè)操作移動(dòng)鼠標(biāo)到此處調(diào)整每一頁(yè)大小。移動(dòng)鼠標(biāo)到此處調(diào)整每一頁(yè)大小。操作：view-page break preview，然后用鼠標(biāo)調(diào)整每一頁(yè)面到合適的大小。15. 相對(duì)引用和絕對(duì)引用隨著公式的位置變化，所引用單元格位置也是在變化的是相對(duì)引用；而隨著公式位置的變化所引用單元格位置不變化的就是絕對(duì)引用。下面講一下“C4”、“$C4”、“C$4”和“$C$4”之間的區(qū)別。在一個(gè)工作表中，在C4、C5中的數(shù)據(jù)分別是60、50。如果在D4單元格中輸入“=C4”，那么將D4向下拖動(dòng)到D5時(shí)，D5中的內(nèi)容就變成了50，里面的公式是“=C5”，將D4向右拖動(dòng)到E4，E4中的內(nèi)容是60，里面的公式變成了“=D4”?，F(xiàn)在在D4單元格中輸入“=$C4”，將D4向右拖動(dòng)到E4，E4中的公式還是“=$C4”，而向下拖動(dòng)到D5時(shí)，D5中的公式就成了“=$C5”。如果在D4單元格中輸入“=C$4”，那么將D4向右拖動(dòng)到E4時(shí)，E4中的公式變?yōu)椤?D$4”，而將D4向下拖動(dòng)到D5時(shí)，D5中的公式還是“=C$4”。如果在D4單元格中輸入“=$C$4”，那么不論你將D4向哪個(gè)方向拖動(dòng)，自動(dòng)填充的公式都是“=$C$4”。 原來(lái)誰(shuí)前面帶上了“$”號(hào)，在進(jìn)行拖動(dòng)時(shí)誰(shuí)就不變。如果都帶上了“$”，在拖動(dòng)時(shí)兩個(gè)位置都不能變。16. 設(shè)置打印區(qū)域在計(jì)算數(shù)據(jù)時(shí)經(jīng)常會(huì)用到一些輔助的單元格，我們把這些單元格作為一個(gè)轉(zhuǎn)接點(diǎn)，但是又不好刪除，此時(shí)可以設(shè)置一個(gè)打印區(qū)域，只打印對(duì)我們有用的那一部分?jǐn)?shù)據(jù)：選擇要打印的部分，打開(kāi)“文件”菜單，單擊“打印區(qū)域”項(xiàng)，從子菜單中選擇“設(shè)置打印區(qū)域”命令，在打印時(shí)就只能打印這些單元格了，單擊“打印預(yù)覽”按鈕，可以看到打印出來(lái)的只有剛才選擇的區(qū)域。 圖 13：打印區(qū)域的設(shè)置單擊“關(guān)閉”按鈕回到普通視圖，選擇“文件”菜單“打印區(qū)域”子菜單中的“取消打印區(qū)域”命令就可以將設(shè)置的打印區(qū)域取消了。鍵盤(pán)瀏覽當(dāng)工作表覆蓋了多個(gè)窗口時(shí)，用戶就會(huì)迫切地需要有一種快捷的方法來(lái)找到目標(biāo)單元格，下面舉幾個(gè)簡(jiǎn)單的例子：表 1：鍵盤(pán)瀏覽移動(dòng)到組合鍵注意事項(xiàng)當(dāng)前單元格Ctrl+Backspace在當(dāng)前單元格移動(dòng)到屏幕之外的時(shí)候使用這個(gè)快捷方式當(dāng)前行的起始位置Home工作表的起始位置Ctrl+Home向上或向下移動(dòng)一個(gè)屏幕Page Up或Page Down向左或向右移動(dòng)一個(gè)屏幕Alt+Page Up或Alt+Page Down窗口的左上角在滾動(dòng)鎖定時(shí)，按Home鍵窗口的或下角在滾動(dòng)鎖定時(shí)，按End鍵17. 公式審核使用公式審核功能，能夠獲取關(guān)于所使用公式的詳細(xì)信息：包括參與公式計(jì)算的單元格，公式錯(cuò)誤信息，公式求值等。圖 14：公式審核公式審核工具條操作：要使用公式審核，首先需要打開(kāi)公式審核工具條（具體的方法為：在EXCEL 工具欄上的空白處點(diǎn)擊鼠標(biāo)右鍵，選擇公式審核）；然后選擇包含公式的單元格，點(diǎn)擊公式審核工具條上的對(duì)應(yīng)按鈕獲取相關(guān)信息C. 常用函數(shù)Excel 提供了大量的函數(shù)對(duì)數(shù)據(jù)進(jìn)行處理，根據(jù)功能的不同分為幾個(gè)大類：統(tǒng)計(jì)函數(shù)，字符/字符串處理函數(shù)，查找函數(shù)，邏輯函數(shù)等。每一類函數(shù)都提供了豐富的功能。 此處就經(jīng)常用到的函數(shù)進(jìn)行簡(jiǎn)單的介紹。18. Vlookup 函數(shù)在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。這是一個(gè)查找并根據(jù)查找結(jié)果提取所需值的函數(shù)， 也是我們最常用/最重要的函數(shù)。 沒(méi)有它，可以說(shuō)我們就無(wú)法處理數(shù)據(jù)。可以在同一表格，不同表格，不同工作薄 之間進(jìn)行查找。 函數(shù)格式：VLOOKUP(lookup_value , table_array , col_index_num,range_lookup, false/true)函數(shù)格式：=VLOOKUP(索引值, 數(shù)據(jù)表, 返回值,0)參數(shù)解釋：Lookup_value 為需要在數(shù)組第一列中查找的數(shù)值。Lookup_value 可以為數(shù)值、引用或文本字符串。Table_array 為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表?？梢允褂脤?duì)區(qū)域或區(qū)域名稱的引用，例如數(shù)據(jù)庫(kù)或數(shù)據(jù)清單。Col_index_num 為 table_array 中待返回的匹配值的列序號(hào)。False/true: 根據(jù)數(shù)組的第一列是否排序來(lái)決定。通常都為FALSEVlookup 可以在同一表格，不同表格，不同工作薄 之間進(jìn)行查找。 但是不同的使用方式需要進(jìn)行一些特殊的處理。19. HEX2DEX/DEC2HEX 函數(shù)這個(gè)兩個(gè)函數(shù)完成 十進(jìn)制，十六進(jìn)制 之間的互相轉(zhuǎn)化。 函數(shù)格式：=HEX2DEX（RIGHT(E2,4)） 將E2單元右4位由16進(jìn)制轉(zhuǎn)為十進(jìn)制數(shù)。當(dāng)進(jìn)行雙頻網(wǎng)優(yōu)化時(shí)非常的有用，因?yàn)镹OKIA/ERICSSO CGI 使用十進(jìn)制，而華為使用十六進(jìn)制。（缺省不加載，需要手工加載函數(shù)庫(kù)）20. MID 函數(shù) 從一個(gè)字符串的某個(gè)位置開(kāi)始提取指定長(zhǎng)度的字符串。經(jīng)常用于提取基站名稱，小區(qū)LAC/CI 等。函數(shù)格式：=MID(A2,2,3) 從A2單元第二個(gè)字符開(kāi)始，連續(xù)取出3個(gè)字符。21. LEFT/RIGHT 函數(shù)從一個(gè)字符串的左/右邊開(kāi)始提取指定長(zhǎng)度的字符串。 也經(jīng)常用于提取基站名稱。函數(shù)格式：=LEFT(A2,2) 從A2單元取出左邊兩個(gè)字符。 =RIGHT(A2,2) 從A2單元取出右邊兩個(gè)字符。數(shù)值合并：=A2&B2&C222. MAX/MIN 函數(shù)MAX/MIN 函數(shù)提取指定數(shù)據(jù)序列中的最大或最小值函數(shù)格式：=MAX(A2:A6,30) 取出A2到A6單元和30中的最大值。23. SUMIF / COUNTIF 對(duì)滿足一定條件的數(shù)據(jù)進(jìn)行求和或者記數(shù)SUMIF條件求和函數(shù)：SUMIF 格式SUMIF(range,criteria,sum_range)在A列中與A2單元內(nèi)基站名相同的單元格所對(duì)應(yīng)的B列中話務(wù)量進(jìn)行求和，即統(tǒng)計(jì)該基站24小時(shí)的總話務(wù)量，通過(guò)復(fù)用公式，得到各個(gè)基站的24小時(shí)總話務(wù)量。類似VLOOKUP函數(shù)用法。 函數(shù)格式：=SUMIF(A:A,A2,B:B) Range 為用于條件判斷的單元格區(qū)域。Criteria 為確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為 32、32、32 或 apples。Sum_range 是需要求和的實(shí)際單元格。24. Countif 函數(shù)使用和SUMIF 函數(shù)基本類似實(shí)例：根據(jù)基站是否開(kāi)通統(tǒng)計(jì)割接TRX 數(shù)目（使用SUMIF），統(tǒng)計(jì)割接小區(qū)數(shù)目（使用COUNTIF）COUNTIF統(tǒng)計(jì)計(jì)數(shù)函數(shù)：=COUNTIF（A:A,”MS1”） 統(tǒng)計(jì)A列中MS1所在單元格的個(gè)數(shù)。=COUNTIF（A:A,A2） 統(tǒng)計(jì)A列中A2單元內(nèi)容所在單元格的個(gè)數(shù)。=COUNTIF（A:A,”-85”） 統(tǒng)計(jì)A列中值大于-85的單元格的個(gè)數(shù)。=COUNTIF(A:A,1)-COUNTIF(A:A,=5) 統(tǒng)計(jì)A列中值大于1小于5的單元格個(gè)數(shù)。實(shí)例：根據(jù)基站是否開(kāi)通統(tǒng)計(jì)割接TRX 數(shù)目（SUMIF），統(tǒng)計(jì)割接小區(qū)數(shù)目（COUNTIF）25. if IF條件判斷函數(shù)：函數(shù)格式：=IF(A23,IF(A210,”中”，”大”),”小”)若A2單元值小于等于3，則返回“小”；若3A210,則返回“中”；否則返回“大”。26. Large 提取指定數(shù)組中的第N 大值。在實(shí)際工作中，通常不是一個(gè)函數(shù)的單獨(dú)使用，某一項(xiàng)操作有可能需要多個(gè)函數(shù)結(jié)合在一起使用才能完成。同時(shí)公式的靈活使用對(duì)效率也有很大影響。這都要通過(guò)多用，多實(shí)踐才能夠提高。27. Sumproduct 函數(shù)在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和。SUMPRODUCT(array1,array2,array3, .)Array1, array2, array3, . 為 2 到 30 個(gè)數(shù)組，其相應(yīng)元素需要進(jìn)行相乘并求和。 說(shuō)明：數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù) SUMPRODUCT 將返回錯(cuò)誤值 #VALUE!。 函數(shù) SUMPRODUCT 將非數(shù)值型的數(shù)組元素作為 0 處理。 如下圖： 圖 15：公式 EXCEL里面怎么合并2個(gè)單元格？就是中間條線去掉，其他內(nèi)容不變提供二種方法，供選擇：1、用橡皮擦將中間的橫線擦掉，起到外觀合并的效果。2、在表外任意單元格輸入公式：=C2&C3 將數(shù)據(jù)合并，然后將數(shù)據(jù)復(fù)制，選擇性粘貼到原來(lái)合并后的位置。D. 宏的使用Excel 還支持宏，通過(guò)編寫(xiě)宏就可以控制Excel，實(shí)現(xiàn)Excel 中沒(méi)有提供的或者是我們需要的一些特殊功能，并且通過(guò)宏，也可以使一些常規(guī)處理自動(dòng)化，從另外一個(gè)方面也提高了工作效率。宏可以分為兩種：1 內(nèi)嵌于EXCEL 中，通過(guò)ALT+F8 來(lái)運(yùn)行。這樣的宏沒(méi)有用戶界面，不能夠接受用戶輸入和響應(yīng)用戶操作，不夠靈活，與用戶的交互性不夠，一般為了特殊確定的用途編寫(xiě)。2 具有用戶界面（也就是窗體）