substitute函数单元分行_如何分类汇总

Excel常用函数快键简介 　　一、 Excel常用函数 　　1、数学函数 　　1) Sum函数 　　主要功能：计算所有参数数值的和。 　　使用格式：SUM（Number1,Number2……） 　　参数说明：Number1、Number2……代表需要计算的值，可以是具体的数值、引用的单格（区域）、逻辑值等。 　　应用举例：如下图所示，在A10单格中输入公式：=SUM(A1:A9)，确认后即可求出以上单格的和。
　　2) Subtotal函数 　　主要功能：返回列表或数据库中的分类汇总。 　　使用格式：SUBTOTAL(function_num, ref1, ref2, …) 　　参数说明：Function_num为1到11（包含隐藏值）或101到111（忽略隐藏值）之间的数字，用来指定使用什么函数在列表中进行分类汇总计算；ref1, ref2,……代表要进行分类汇总区域或引用，不超过29个。
　　应用举例：一般情况下我们利用这个函数对筛选的单格进行求和，比如我们在B11单格中输入“=SUBTOTAL(9,A2:A10)”，就会求出当前筛选结果值的和。如下图所示：
　　特别提醒：如果采取自动筛选，无论function_num参数选用什么类型，SUBTOTAL函数忽略任何不包括在筛选结果中的行；SUBTOTAL函数适用于数据列或垂直区域，不适用于数据行或水平区域。 　　3) Sumif函数 　　主要功能：计算符合指定条件的单格区域内的数值和。 　　使用格式：SUMIF（Range,Criteria,Sum_Range） 　　参数说明：Range代表条件判断的单格区域；Criteria为指定条件表达式；Sum_Range代表需要计算的数值所在的单格区域。 　　应用举例： 比如我们从某个BSC所有小区的话务量的数据中，提取某小区载频级一周的话务量。如下图所示：在F3单格中输入“=SUMIF(C:C,F2,D:D)”即可，意思是：如果在C列中有值等于F2的，就对D列中的值进行求和。
　　4) Average 函数 　　主要功能：求出所有参数的算术平均值。 　　使用格式：AVERAGE(number1,number2,……) 　　参数说明：number1,number2,……：需要求平均值的数值或引用单格（区域），参数不超过30个。 　　应用举例：同SUM函数用法相同，不再举例。 　　特别提醒：如果引用区域中包含“0”值单格，则计算在内；如果引用区域中包含空白或字符单格，则不计算在内。 　　5) Max函数 　　主要功能：求出一组数中的最大值。 　　使用格式：MAX(number1,number2……) 　　参数说明：number1,number2……代表需要求最大值的数值或引用单格（区域），参数不超过30个。 　　应用举例：同SUM函数用法相同，不再举例。 　　特别提醒：如果参数中有文本或逻辑值，则忽略。 　　6) Min函数 　　主要功能：求出一组数中的最小值。 　　使用格式：MIN(number1,number2……) 　　参数说明：number1,number2……代表需要求最小值的数值或引用单格（区域），参数不超过30个。 　　应用举例：同SUM函数用法相同，不再举例。 　　特别提醒：如果参数中有文本或逻辑值，则忽略。 　　7) ABS函数 　　主要功能：求出相应数字的绝对值。 　　使用格式：ABS(number) 　　参数说明：number代表需要求绝对值的数值或引用的单格。 　　应用举例：如果在B2单格中输入公式：=ABS(A2)，则在A2单格中无论输入正数（如100）还是负数（如-100），B2中均显示出正数（如100）。 　　特别提醒：如果number参数不是数值，而是一些字符（如A等），则B2中返回错误值“#VALUE！” 　　8) Hex2dec函数 　　主要功能：将十六进制数转换为十进制数。 　　使用格式：HEX2DEC(number) 　　参数说明：number代表需转化的数值或引用的单格。 　　应用举例：一般情况下，BSC32中CGI为十六进制的，这样我们就利用此函数进行转换，比如在B2单格中输入公式：= HEX2DEC (783)，确认后就会输出1923。 　　9) Dec2hex函数 　　将十进制数转换为十六进制数，用法同Hex2dec函数 　　10) OCT2DEC函数 　　将八进制数转换为十进制数，用法同Hex2dec函数 　　11) OCT2HEX函数 　　将八进制数转换为十六进制数，用法同Hex2dec函数 　　12) Counta函数 　　计算区域中非空单格个数 　　13) CONCATENATE函数 　　将两个或多个文本字符串合并为一个文本字符串。 　　可使用连接符号 (&) 计算运算符代替 CONCATENATE 函数来连接文本项。例如 =A1&B1 与 =CONCATENATE(A1,B1) 返回相同的值。 　　14) TEXT函数 　　将数值格式转换为文本格式。 　　在C2单格中输入公式＝TEXT（B2，〃0〃），即可把B2单格数字转换为文本型数字。由于在单格中输入超过15位的数字时，超过的位数自动以0替换，所以上述公式只能转换位数不超过15位的数字，而无法正确还原18位的身份证号码。 　　2、文本函数 　　1) LEft函数 　　主要功能：从一个文本字符串的第一个字符开始，截取指定数目的字符。 　　使用格式：LEFT(text,num_chars) 　　参数说明：text代表要截字符的字符串；num_chars代表给定的截取数目。 　　应用举例：在制作工参时，我们会提取小区名左边的几个字作为地区名，例如：在B2中输入“=LEFT(A2,2)”就会分离出地区名。如下所示：
　　2) LEN函数 　　主要功能：文本字符串中的字符数 　　使用格式：LEN(text) 　　参数说明：Text 是要查找其长度的文本。空格将作为字符进行计数。 　　Left与len配合使用 　　=left(A1,len(A1)-3)解释： 　　在A列单格有一些数据， 　　在A1中有数据“123C30”， 　　那么如何在B1中写一个公式，使得A1 　　的后三位被删除，得到123 　　3) Right函数 　　主要功能：从一个文本字符串的最后一个字符开始，截取指定数目的字符。 　　使用格式：RIGHT(text,num_chars) 　　参数说明：text代表要截字符的字符串；num_chars代表给定的截取数目。 　　应用举例：使用方法同Left函数，但是只是提取的右边的字符。 　　4) Mid函数 　　主要功能：从一个文本字符串的指定位置开始，截取指定数目的字符。 　　使用格式：MID(text,start_num,num_chars) 　　参数说明：text代表一个文本字符串；start_num表示指定的起始位置；num_chars表示要截取的数目。 　　应用举例：在系统优化时经常会提取一段字符中的一段字符，比如提取A2单格“我很爱网优”中间的三位字符，只需要输入“=MID（A2，2，3）”即可。 　　5) Substitute函数 　　主要功能：需要在某一文本字符串中替换指定的文本。 　　使用格式：SUBSTITUTE(text,old_text,new_text,instance_num) 　　参数说明： 　　Text 为需要替换其中字符的文本，或对含有文本的单格的引用。 　　Old_text 为需要替换的旧文本。 　　New_text 用于替换 old_text 的文本。 　　Instance_num 为一数值，用来指定以 new_text 替换第几次出现的 old_text。如果指定了 instance_num，则只有满足要求的 old_text 被替换；否则将用 new_text 替换 Text 中出现的所有 old_text。 　　应用举例： 有些情况下在制作工参时，提取的频点信息中包含BCCH频点，因此制作TCH频点时需要将BCCH频点替换掉，例如在C2中输入“=SUBSTITUTE(A:A,’;’&B2,”)”，即可把BCCH频点替换掉，如下图所示：
　　3、逻辑函数 　　1) If函数 　　主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。 　　使用格式：=IF(Logical,Value_if_true,Value_if_false) 　　参数说明：Logical代表逻辑判断表达式；Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果忽略返回“TRUE”；Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”。 　　应用举例：我们经常利用IF函数进行数据的分类，如果分类较多的情况下我们会利用IF函数嵌套。比如，要筛选出E列中小于等于3的值为“小”，大于3小于等于9的为“大”，大于9的为“很大”，只需输入公式“=IF(E2>3,IF(E2<10,’大’,’很大’),’小’)”然后向下填充即可。如下图所示：
　　2) And函数 　　主要功能：返回逻辑值：如果所有参数值均为逻辑“真（TRUE）”，则返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。 　　使用格式：AND(logical1,logical2, …) 　　参数说明：Logical1,Logical2,Logical3……：表示待测试的条件值或表达式，最多这30个。 　　应用举例：在C5单格输入公式：=AND(A5>=60,B5>=60)，确认。如果C5中返回TRUE，说明A5和B5中的数值均大于等于60，如果返回FALSE，说明A5和B5中的数值至少有一个小于60。 　　3) Or函数 　　主要功能：返回逻辑值，仅当所有参数值均为逻辑“假（FALSE）”时返回函数结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”。 　　使用格式：OR(logical1,logical2, …) 　　参数说明：Logical1,Logical2,Logical3……：表示待测试的条件值或表达式，最多这30个。 　　应用举例：在C62单格输入公式：=OR(A62>=60,B62>=60)，确认。如果C62中返回TRUE，说明A62和B62中的数值至少有一个大于或等于60，如果返回FALSE，说明A62和B62中的数值都小于60。 　　4、查找函数 　　1) Vlookup函数 　　主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。 　　使用格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 　　参数说明：Lookup_value代表需要查找的数值；Table_array代表需要在其中查找数据的单格区域；Col_index_num为在table_array区域中待返回的匹配值的列序号（当Col_index_num为2时,返回table_array第2列中的数值，为3时，返回第3列的值……）；Range_lookup为一逻辑值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为FALSE，则返回精确匹配值，如果找不到，则返回错误值#N/A。 　　应用举例：Lookup函数是非常重要的，可以说无时无刻不再用它。比如通过CI来索引小区的BCCH，在I2单格中输入“=VLOOKUP(H2,E:F,2,0)”，表示在E列中查找需要的CI，返回F列中匹配值，下拉单格直接填充单格即可。如下所示：
　　二、 Excel中插入图表 　　我们在写测试报告的时候，经常需要对DT的指标进行统计，然后制作成图表，这样看起来会更加的形象直观。但是同样的数据，有些人制作的图标非常的漂亮，而有些人制作的图表看起来非常的生硬，不美观，下面将介绍如何制作出漂亮的图表。 　　首先在主菜单栏中选择“插入”，接着选择“图表”出现如下对话框：
　　第二步：
　　第三部：
　　第四步：
　　第五步：
　　第六步：对图表的各个区域进行编辑，这一可以使生成的图标更加的美观，下面仅列举了对图表区的编辑方法，其他区域编辑类似。
　　图表区的编辑方法：
　　各个区域编辑好后，就会生成漂亮的图表了：
　　三、 Excel中录制宏 　　有些情况下，需要对多个单格进行相同的操作，且操作步骤并不多。此时就建议录制个简单的宏，这样可以提高工作效率。但是有些情况下使用录制的宏时，会出现错误，因此建议录制的宏最好包含较少的函数，需要大家细细的体会。具体录制步骤如下： 　　单击“工具”->“宏”-> “录制新宏”，会出现如下对话框：
　　然后会出现如下对话框，
　　此标志用于停止录制宏。 　　接下来可以进行相关的操作，操作完后“停止录制”工具栏，就可以完成录制宏了。下一步就是运行宏了，打开个表格，“工具”->“宏”-> “宏”会出现如下对话框：
　　此时，可以“执行”，就可以运行宏了。 　　四、 Excel常用快捷键 　　1、Ctrl+箭头键 　　移动到当前数据区域的边缘。 　　2、Ctrl+Home 　　移动到工作表的开头。 　　3、Ctrl+End 　　移动到工作表的最后一个单格，该单格位于数据所占用的最右列的最下行中。 　　4、Alt+Enter 　　在单格中强制换行。 　　5、Crl+Z 　　撤消上一次操作。 　　6、Ctrl+C 　　复制选定的单格。 　　7、Ctrl+X 　　剪切选定的单格。 　　8、Ctrl+V 　　粘贴复制的单格。 　　9、Alt+E+D 　　强制换行 　　10、Ctrl+A 　　选定整张工作表。 　　11、Ctrl+1 　　显示’单格格式’对话框，对单格格式进行设置 　　12、Alt+D+F+F 　　自动筛选 　　13、Alt+W+F 　　冻结窗口 　　14、Alt+Tab 　　各个程序窗口 　　15、Delete 　　况选定单格内容 　　五、 总结 　　以上只是对EXCEL中最常用的一些功能进行简单的介绍，希望能对初学者起个引导作用。希望大家在使用EXCEL时多使用快捷键，灵活地使用函数，这样可以极大地提高我们的工作效率。 　　WORD 宏工具介绍 　　1.录制宏 　　在“工具”菜单上，指向“宏”，然后单击“录制新宏”。 　　在“宏名”框中，键入宏的名称。 　　在“将宏保存在”框中，单击将保存宏的模板 （模板：是指一个或多个文件，其中所包含的结构和工具构成了已完成文件的样式和页面布局等素。例如，Word 模板能够生成单个文档，而 FrontPage 模板可以形成整个网站。）或文档。 　　在“说明”框中，键入对宏的说明。 　　如果不希望将宏指定到工具栏 （工具栏：包含可用于执行命令的按钮和选项的栏。要显示工具栏，请按 Alt 然后按 Shift+F10。）、菜单 （菜单：单击菜单栏或其他工具栏上的菜单名时出现的命令的列表。）或快捷键 （快捷键：功能键或组合键，例如 F5 或 Ctrl+A，可用来执行菜单命令。存取键是另外一种组合键，例如 Alt+F，可以将焦点移至菜单、命令或控件。），请单击“确定”开始录制宏。 　　若要将宏指定到工具栏或菜单，请单击“工具栏”，然后单击“命令”选项卡。在“命令”框中，单击正在录制的宏，然后将其拖动到需指定到的工具栏或菜单。单击“关闭”，开始录制宏。 　　要给宏指定快捷键，请单击“键盘”，在“命令”框中单击正在录制的宏，在“请按新快捷键”框中键入所需的快捷键，然后单击“指定”。单击“关闭”，开始录制宏。 　　执行要包含在宏中的操作。 　　录制宏时，可以使用鼠标单击命令和选项，但不能选择文本。必须使用键盘记录这些操作。例如，可以使用 F8 来选择文本，并按 End 将光标移动到行的结尾处。 　　若要停止录制宏，请单击“停止录制”。 　　2.编辑宏 　　在“工具”菜单上，指向“宏”子菜单，再单击“宏”。 　　在“宏名”框中单击要编辑的宏的名称。 　　如果宏没有出现在列表中，请在“宏的位置”框中选择其他的文档、模板 （模板：是指一个或多个文件，其中所包含的结构和工具构成了已完成文件的样式和页面布局等素。例如，Word 模板能够生成单个文档，而 FrontPage 模板可以形成整个网站。）或列表。 　　单击“编辑”按钮。 　　3.运行宏 　　单击“工具”菜单中“宏”子菜单中的“宏”命令。 　　在“宏名”框中，单击要运行的宏 （宏：可用于自动执行任务的一项或一组操作。可用 Visual Basic for Applications 编程语言录制宏。）的名称。 　　如果该宏没有出现在列表中，请在“宏的位置”框中选择其他的文档、模板 （模板：是指一个或多个文件，其中所包含的结构和工具构成了已完成文件的样式和页面布局等素。例如，Word 模板能够生成单个文档，而 FrontPage 模板可以形成整个网站。）或列表。 　　单击“运行”按钮。 　　通过单格区域创建下拉列表 　　若要使数据输入更容易，或将输入限制到您定义的某些项，则可以创建一个从工作簿其他位置的单格进行编译的有效条目构成的下拉列表。创建单格的下拉列表时，它将在该单格上显示一个箭头。若要在该单格中输入信息，请单击箭头，然后单击所需的条目。 　　若要从单格区域创建下拉列表，请使用“数据”选项卡的“数据工具”组中的“数据有效性”命令。