当前位置:首页 >> >>

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

EXCEL常用功能与函数介绍
A. 1. 2. 3. B. EXCEL 简单介绍 ........................................................................................................... 2 基站数据库的维护................................................................................................................ 2 话统数据的分析.................................................................................................................... 2 数据修改 ............................................................................................................................... 2 EXCEL常用操作 ............................................................................................................. 2

4. SHIFT+CTRL+方向键............................................................................................................... 2 5. 鼠标左键拖动(复制单元格内容到指定范围) ................................................................ 3 6. 同时更改多个插页(worksheet)中同一位置的内容 ....................................................... 4 7. 剪切,粘贴命令的鼠标操作................................................................................................ 5 8. 状态栏的灵活使用................................................................................................................ 5 9. 条件格式的使用.................................................................................................................... 5 10. 数据的组合与分组............................................................................................................ 7 11. 下拉列表选择框制作........................................................................................................ 8 12. 鼠标双击进行单元格内容的的复制 ................................................................................ 9 13. 批量清空单元格内容........................................................................................................ 9 14. 分页操作 ......................................................................................................................... 10 15. 相对引用和绝对引用...................................................................................................... 11 16. 设置打印区域.................................................................................................................. 12 17. 公式审核 ......................................................................................................................... 14 C. 常用函数 ....................................................................................................................... 14 18. Vlookup 函数 .................................................................................................................. 14 19. HEX2DEX/DEC2HEX 函数 ................................................................................................ 15 20. MID 函数......................................................................................................................... 15 21. LEFT/RIGHT 函数............................................................................................................. 15 22. MAX/MIN 函数 ............................................................................................................... 16 23. SUMIF / COUNTIF ............................................................................................................. 16 24. Countif 函数使用和 SUMIF 函数基本类似 .................................................................. 16 25. if ....................................................................................................................................... 17 26. Large................................................................................................................................. 17 27. Sumproduct 函数............................................................................................................ 17 D. 宏的使用 ....................................................................................................................... 18 28. 宏组成结构的简介.......................................................................................................... 19 E. 总结 .............................................................................................................................. 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:数据的组合与分组

ungroup

group

操作:menu->Data->Group and outline。

11. 下拉列表选择框制作
在Excel 中经常可看到下图所示的列表框
图 9:下拉列表框制作

在 Excel 中 要 实 现 这 样 的 功 能 比 较 简 单 , 具 体 步 骤 为 : 选 择 Excel 菜 单 中 的 data-?validation 弹出下图窗口,按照下图进行设置: 图 10:下拉列表框的制作

Must be list

Input the data source

12. 鼠标双击进行单元格内容的的复制
某一列已经具有数据, 此时可以用这一列作为标志, 通过鼠标双击 (鼠标形状变为 + 时) 来完成紧接着的下一列单元格的复制,复制结束的标志为前一列第一个空单元格。 (对公式 的复制特别有用)

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: 根据数组的第一列是否排序来决定。通常都为 FALSE Vlookup 可以在同一表格,不同表格,不同工作薄 之间进行查找。 但是不同的使用方 式需要进行一些特殊的处理。

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&C2

22. 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(A2>3,IF(A2<10,”中”,”大”),”小”) 若 A2 单元值小于等于 3,则返回“小”;若 3<A2<10,则返回“中”;否则返回“大”。

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 具有用户界面(也就是窗体) 这样的宏能够接受用户,输入,根据用户的不同行为 。 执行不同的代码,展示给用户不同的结果,运行灵活,具有较强的交互性和可定制性。如下 图:
图 16:带用户界面的宏

28. 宏组成结构的简介
宏是用 VBA 来编写,也就是说它的语法完全是 VB 的语法。简单的说,一段宏就是一 个过程或者函数, 由变量,表达示,语句构成。如下 'Determinte whether to allow Update cell Info Private Sub CheckBox2_Click() Dim c as integer If c = 0 Then CommandButton4.Enabled = True c=1 ElseIf c = 1 Then c=0 CommandButton4.Enabled = False End If End Sub 所以要学习使用宏,必须要知道 VBA 支持那些变量,如何声明变量,如何组成表达示, 有那些语句,每个语句的语法与规则是什么。

如何编写宏 1 使用录制宏的方式,然后根据实际需要做一些改动。 2 使用 EXCEL 提供的 VBA 开发环境从空白开始编写 VBA 的开发环境如下图:
图 17:VBA 的开发环境

对于开始学习宏的人来说,可以在已经熟悉了 VBA 相关语法的基础上,自己录制一些 简单的宏,然后在 VBA 开发环境中打开这些宏,看看系统是如何通过代码来实现一些特殊 功能的。

E. 总结
以上只是针对EXCEL 的一些常用操作技巧,常用函数 进行了简单的介绍。EXCEL是一个实践性很强的工具,需要 多使用,多总结才能提高。