EXCEL常用功能与函数介绍--网优.doc

EXCEL常用功能与函数介绍A.EXCEL 简单介绍21.基站数据库的维护22.话统数据的分析23.数据修改2B.EXCEL常用操作24.SHIFT+CTRL+方向键25.鼠标左键拖动（复制单元格内容到指定范围）36.同时更改多个插页（worksheet）中同一位置的内容47.剪切，粘贴命令的鼠标操作58.状态栏的灵活使用59.条件格式的使用510.数据的组合与分组711.下拉列表选择框制作812.鼠标双击进行单元格内容的的复制913.批量清空单元格内容914.分页操作1015.相对引用和绝对引用1116.设置打印区域1217.公式审核14C.常用函数1418.Vlookup 函数1419.HEX2DEX/DEC2HEX 函数1520.MID 函数1521.LEFT/RIGHT 函数1522.MAX/MIN 函数1623.SUMIF / COUNTIF1624.Countif 函数使用和SUMIF 函数基本类似1625.if1726.Large1727.Sumproduct 函数17D.宏的使用1828.宏组成结构的简介19E.总结20 A. EXCEL 简单介绍Excel 对于网络规划优化工程师来说，是一个非常有用的工具。在网络规划过程中数据的采集与处理；在网络优化过程中网络数据的修改，话统数据的分析， 我们面对的都是巨大的数据量；没有Excel，有很多工作我们无从下手，Excel 在网规和网优过程中起到了非常重要的作用。总的说来，Excel 在网规网优中的应用可以分成以下三个大的方面：1. 基站数据库的维护通过使用Excel 建立包含：基站名称，所属BSC，所属区域，基站类型，小区配置，割接时间，BCCH，TCH，CGI 等常用信息的数据库以后，可以使我们无论对整个网络还是具体到每一个基站有一个全方面的了解。 2. 话统数据的分析通过使用Excel提供的函数和功能，可以定制一些常用的话统模版。 在进行日常的分析时，通过把原始数据导入到模版以后，Excel 将会自动的计算出相关指标；同时可以通过合理的使用Excel 提供的函数和报表的需求（切换成功率，掉话率等）制作报表模版。3. 数据修改在优化过程中，当需要对某些数据进行修改时，可以使用Excel 对DBF 进行处理，迅速查找到DBF 中需要修改的地方。B. EXCEL常用操作4. SHIFT+CTRL+方向键 很多情况下，我们需要选取表格中的数据，一般情况下都是通过使用拖动鼠标的方式，但是在数据量比较大的时候，比较难于控制，经常回出现多选或则少选的情况。使用以上组合键就可以避免这样的问题。它们实现的功能是：从当前单元格开始，按照选定的方向选取数据，遇到第一个空单元格时结束。 如下图：图 1：CTRL+SHIFT+方向键示意图5. 鼠标左键拖动（复制单元格内容到指定范围）操作：选取需要复制的单元格（可以为多个），按住CTRL 移动鼠标到单元格的右下角，当出现十字形状时开始拖动鼠标，在选定位置释放鼠标。对数值型单元格有递增和等值两种操作，对非数值型的单元格只有等值复制一种操作。 图 2：鼠标左键拖动（递增操作） 图 3：鼠标左键拖动（等值操作）6. 同时更改多个插页（worksheet）中同一位置的内容操作：选取需要更改的所有表格，在需要更改的单元格内输入新内容，如下图图 4：同时改变多个插页（worksheet）中统一位置的内容7. 剪切，粘贴命令的鼠标操作操作：选择需要剪切的范围，移动鼠标到选中范围的左上角，当鼠标变为 时，按住鼠标左键拖动到希望粘贴的地方。8. 状态栏的灵活使用在Excel 的左下角，有一个状态栏，其中提供了许多有用的功能与函数对选定的范围进行处理，如：记数，求和，求平均数，求最大/最小值等。图 5：状态栏的灵活使用9. 条件格式的使用 有时，我们需要根据数据的特征把它们区别开来，以便一目了然，如：基站是否开通等。如下图：图 6：条件格式的使用（标注）操作：首先选择想使用条件格式的单元格（也可以为一个范围），然后 选择 Format-Conditional format。 在如下窗口中进行设置：图 7：条件格式使用（设置）10. 数据的组合与分组在数据量比较大，表格中的列/行 相对较多时，可以把具有一定特征的数据组合在一起，需要时再展开。这样可以保证表格的整洁和信息的有效性。如下图：图 8：数据的组合与分组groupungroup操作：menu-Data-Group and outline。11. 下拉列表选择框制作 在Excel 中经常可看到下图所示的列表框图 9：下拉列表框制作在Excel 中要实现这样的 功能比较简单，具体步骤为：选择 Excel 菜单中的 data-validation 弹出下图窗口，按照下图进行设置：图 10：下拉列表框的制作Input the data sourceMust be list12. 鼠标双击进行单元格内容的的复制某一列已经具有数据，此时可以用这一列作为标志，通过鼠标双击（鼠标形状变为 + 时）来完成紧接着的下一列单元格的复制，复制结束的标志为前一列第一个空单元格。（对公式的复制特别有用）13. 批量清空单元格内容选择需要清空内容的单元格范围，然后按”Delete” 键。同时也可以一次清空表格中的所有数据，操作方式为：鼠标左键点击表格左上角的空白处，选中整个表格，如下图，然后按”Delete” 键清空整个表格。图 11：批量清空单元表格14. 分页操作有时我们需要把EXCEL 中的数据进行分页，方便打印。这时就会使用到EXCEL的分页功能，如下图图 12：分页操作移动鼠标到此处调整每一页大小。移动鼠标到此处调整每一页大小。操作：view-page break preview，然后用鼠标调整每一页面到合适的大小。15. 相对引用和绝对引用随着公式的位置变化，所引用单元格位置也是在变化的是相对引用；而随着公式位置的变化所引用单元格位置不变化的就是绝对引用。下面讲一下“C4”、“$C4”、“C$4”和“$C$4”之间的区别。在一个工作表中，在C4、C5中的数据分别是60、50。如果在D4单元格中输入“=C4”，那么将D4向下拖动到D5时，D5中的内容就变成了50，里面的公式是“=C5”，将D4向右拖动到E4，E4中的内容是60，里面的公式变成了“=D4”。现在在D4单元格中输入“=$C4”，将D4向右拖动到E4，E4中的公式还是“=$C4”，而向下拖动到D5时，D5中的公式就成了“=$C5”。如果在D4单元格中输入“=C$4”，那么将D4向右拖动到E4时，E4中的公式变为“=D$4”，而将D4向下拖动到D5时，D5中的公式还是“=C$4”。如果在D4单元格中输入“=$C$4”，那么不论你将D4向哪个方向拖动，自动填充的公式都是“=$C$4”。 原来谁前面带上了“$”号，在进行拖动时谁就不变。如果都带上了“$”，在拖动时两个位置都不能变。16. 设置打印区域在计算数据时经常会用到一些辅助的单元格，我们把这些单元格作为一个转接点，但是又不好删除，此时可以设置一个打印区域，只打印对我们有用的那一部分数据：选择要打印的部分，打开“文件”菜单，单击“打印区域”项，从子菜单中选择“设置打印区域”命令，在打印时就只能打印这些单元格了，单击“打印预览”按钮，可以看到打印出来的只有刚才选择的区域。 图 13：打印区域的设置单击“关闭”按钮回到普通视图，选择“文件”菜单“打印区域”子菜单中的“取消打印区域”命令就可以将设置的打印区域取消了。键盘浏览当工作表覆盖了多个窗口时，用户就会迫切地需要有一种快捷的方法来找到目标单元格，下面举几个简单的例子：表 1：键盘浏览移动到组合键注意事项当前单元格Ctrl+Backspace在当前单元格移动到屏幕之外的时候使用这个快捷方式当前行的起始位置Home工作表的起始位置Ctrl+Home向上或向下移动一个屏幕Page Up或Page Down向左或向右移动一个屏幕Alt+Page Up或Alt+Page Down窗口的左上角在滚动锁定时，按Home键窗口的或下角在滚动锁定时，按End键17. 公式审核使用公式审核功能，能够获取关于所使用公式的详细信息：包括参与公式计算的单元格，公式错误信息，公式求值等。图 14：公式审核公式审核工具条操作：要使用公式审核，首先需要打开公式审核工具条（具体的方法为：在EXCEL 工具栏上的空白处点击鼠标右键，选择公式审核）；然后选择包含公式的单元格，点击公式审核工具条上的对应按钮获取相关信息C. 常用函数Excel 提供了大量的函数对数据进行处理，根据功能的不同分为几个大类：统计函数，字符/字符串处理函数，查找函数，逻辑函数等。每一类函数都提供了丰富的功能。 此处就经常用到的函数进行简单的介绍。18. Vlookup 函数在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。这是一个查找并根据查找结果提取所需值的函数， 也是我们最常用/最重要的函数。 没有它，可以说我们就无法处理数据。可以在同一表格，不同表格，不同工作薄 之间进行查找。 函数格式：VLOOKUP(lookup_value , table_array , col_index_num,range_lookup, false/true)函数格式：=VLOOKUP(索引值, 数据表, 返回值,0)参数解释：Lookup_value 为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或数据清单。Col_index_num 为 table_array 中待返回的匹配值的列序号。False/true: 根据数组的第一列是否排序来决定。通常都为FALSEVlookup 可以在同一表格，不同表格，不同工作薄 之间进行查找。 但是不同的使用方式需要进行一些特殊的处理。19. HEX2DEX/DEC2HEX 函数这个两个函数完成 十进制，十六进制 之间的互相转化。 函数格式：=HEX2DEX（RIGHT(E2,4)） 将E2单元右4位由16进制转为十进制数。当进行双频网优化时非常的有用，因为NOKIA/ERICSSO CGI 使用十进制，而华为使用十六进制。（缺省不加载，需要手工加载函数库）20. MID 函数 从一个字符串的某个位置开始提取指定长度的字符串。经常用于提取基站名称，小区LAC/CI 等。函数格式：=MID(A2,2,3) 从A2单元第二个字符开始，连续取出3个字符。21. LEFT/RIGHT 函数从一个字符串的左/右边开始提取指定长度的字符串。 也经常用于提取基站名称。函数格式：=LEFT(A2,2) 从A2单元取出左边两个字符。 =RIGHT(A2,2) 从A2单元取出右边两个字符。数值合并：=A2&B2&C222. MAX/MIN 函数MAX/MIN 函数提取指定数据序列中的最大或最小值函数格式：=MAX(A2:A6,30) 取出A2到A6单元和30中的最大值。23. SUMIF / COUNTIF 对满足一定条件的数据进行求和或者记数SUMIF条件求和函数：SUMIF 格式SUMIF(range,criteria,sum_range)在A列中与A2单元内基站名相同的单元格所对应的B列中话务量进行求和，即统计该基站24小时的总话务量，通过复用公式，得到各个基站的24小时总话务量。类似VLOOKUP函数用法。 函数格式：=SUMIF(A:A,A2,B:B) Range 为用于条件判断的单元格区域。Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32 或 apples。Sum_range 是需要求和的实际单元格。24. Countif 函数使用和SUMIF 函数基本类似实例：根据基站是否开通统计割接TRX 数目（使用SUMIF），统计割接小区数目（使用COUNTIF）COUNTIF统计计数函数：=COUNTIF（A:A,”MS1”） 统计A列中MS1所在单元格的个数。=COUNTIF（A:A,A2） 统计A列中A2单元内容所在单元格的个数。=COUNTIF（A:A,”-85”） 统计A列中值大于-85的单元格的个数。=COUNTIF(A:A,1)-COUNTIF(A:A,=5) 统计A列中值大于1小于5的单元格个数。实例：根据基站是否开通统计割接TRX 数目（SUMIF），统计割接小区数目（COUNTIF）25. if IF条件判断函数：函数格式：=IF(A23,IF(A210,”中”，”大”),”小”)若A2单元值小于等于3，则返回“小”；若3A210,则返回“中”；否则返回“大”。26. Large 提取指定数组中的第N 大值。在实际工作中，通常不是一个函数的单独使用，某一项操作有可能需要多个函数结合在一起使用才能完成。同时公式的灵活使用对效率也有很大影响。这都要通过多用，多实践才能够提高。27. Sumproduct 函数在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。SUMPRODUCT(array1,array2,array3, .)Array1, array2, array3, . 为 2 到 30 个数组，其相应元素需要进行相乘并求和。 说明：数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。 函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。 如下图： 图 15：公式 EXCEL里面怎么合并2个单元格？就是中间条线去掉，其他内容不变提供二种方法，供选择：1、用橡皮擦将中间的横线擦掉，起到外观合并的效果。2、在表外任意单元格输入公式：=C2&C3 将数据合并，然后将数据复制，选择性粘贴到原来合并后的位置。D. 宏的使用Excel 还支持宏，通过编写宏就可以控制Excel，实现Excel 中没有提供的或者是我们需要的一些特殊功能，并且通过宏，也可以使一些常规处理自动化，从另外一个方面也提高了工作效率。宏可以分为两种：1 内嵌于EXCEL 中，通过ALT+F8 来运行。这样的宏没有用户界面，不能够接受用户输入和响应用户操作，不够灵活，与用户的交互性不够，一般为了特殊确定的用途编写。2 具有用户界面（也就是窗体）