办公效率提升之EXCEL常用函数培训(市场口、县区)

1网络部唐桂琼6405办公效率提升—EXCEL常用函数讲解2EXCEL办公能力摸底测评以下问题，您是前者还是后者？前者：分分钟之内搞定后者：不知道要多长时间1、上万行用户号码，要判断后四位是否带“4”2、从成千上万个用户号码中找出重复的号码并把重复的号码剔除3、根据已有的上千行的用户号码、保底消费信息，得出现有表中用户号码的保底消费额；4、一个表是手工录入的参加活动的用户号码，一个表是系统导出的参加活动的用户号码，都是上千行记录，对比两者有无差异5、上万行用户号码，知道用户的入网时间，要判断出用户属于哪个网龄段6、根据表中每个营业厅的移动座机销售明细，要求统计每个营业厅销售了多少台，各种型号的移动座机分别销售了多少台，每个营业厅的座机采购总价是多少EXCEL函数可以帮助您3测试题1——常用字符处理函数应用•上万行用户号码，要判断后四位是否带“4”•解题步骤：1、因不清楚用户号码是否录入规范，可能有空格等，先用=TRIM()去除空格，然后用=LEN()统计用户号码长度，看是否是11位。2、用=RIGHT()截取后四位数。3、用=FIND()查找4所在的位置。4、根据查找结果，能找到4的位置数表示带“4”，不能找到4的位置结果为#VALUE!表示不带“4”。具体操作见附件4常用的字符处理函数=LEFT()从左往右取字符=RIGHT()从右往左取字符=MID()从中间指定位置开始往右取一定个数的字符=LEN()计算单元格字符的个数&连接号，将两串或以上字符连接在一起=FIND()查找指定的某个字符所在的位置=TRIM()去除空格，但如果字符与字符之间有空格，保留一个空格具体操作见附件5测试题2——IF函数剔重从成千上万行号码中找出重复的号码并把重复的号码剔除解题步骤：1、先将号码列排序2、用=IF()判断下一行和上一行是否相同，相同为0，不同为1，筛选出为0的，肯定是与为1的有重复的，把为0的删除。具体操作见附件6测试题3——VLOOK查询函数引用数据根据已有的上千行的用户号码、保底消费信息，得出现有表中用户号码的保底消费额；解题步骤：1、用=VLOOKUP（）将用户号码在已有信息表中查询，如果找到一样的，就把已有信息表中该号码对应的保底信息引用粘贴过来，如果找不到一样的，则反馈#N/A;具体操作见附件7测试题4——VLOOK查询函数比对应用一个表是手工录入的参加活动的用户号码，一个表是系统导出的参加活动的用户号码，都是上千行记录，对比两者有无差异解题步骤：1、如果都只有号码，需注明来源以示区分;2、用=VLOOKUP（）将系统中的号码与手工录入的号码查询比对，如果找到一样的，就把手工录入表中号码对应的信息引用过来，如果找不到一样的，就反馈#N/A;3、再用=VLOOKUP（）将手工录入的号码与系统导出的号码查询比对，如果找到一样的，就把系统导出表中号码对应的信息引用过来，如果找不到一样的，就反馈#N/A;4、两个表都筛选出#N/A，则是两个表存在差异的号码。具体操作见附件8VLOOKUP——最最最常用的函数•=VLOOKUP()以两个表中共同的字段为关键字段，以关键字段进行查询，找到和关键字段完全相同的就把它所在行对应的其他信息引用到特定的单元格中，其作用相当于“无数次重复的查找、复制、粘贴”。常用于两个表的数据进行比对、把一个表中的数据引用到另一个表中，无需考虑排序问题。•=VLOOKUP(参数1，参数2，参数3，0）参数1，进行查询的关键字段所在的单元格或所在列参数2，要在哪个表中查询就点到哪个表，从双方共有的关键字段所在列拖动到要引用信息所在的列参数3，从双方共有的关键字段所在列拖动到要引用信息所在的列，拖动了几列，就输入几，比如A到D列，就输入49测试题5——LOOKUP区间段查询函数应用上万行用户号码，知道用户的入网时间，要判断出用户属于哪个网龄段解题步骤：1、用=TODAY（）得出当前的日期2、用=DAYS360（）得出当前与入网日期相差的天数（即在网天数）;3、将在网天数折算为年;4、根据网龄段确定每个网龄段的起始值5、用=LOOKUP（）将得出的在网年限与每个网龄段的起始值进行比对，判断得出所属的网龄段。具体操作见附件=LOOKUP()根据某列数据，从一组区间段数据中查询，得出该列数据所属的对应区间段。其作用相当于“无数次设定筛选条件筛选，然后将结果填入”，或者相当于“将多个IF函数嵌套使用”。10LOOKUP区间段查询函数应用2成千上万用户号码，知道每个号码对应的编号，要根据编号段对应的营业厅名称，得出每个用户号码属于哪个营业厅解题方法1：1、先用=LEFT()得出编号段的起始值，然后“复制—选择性粘贴—数值—加”，将属性转换为数值；2、用=LOOKUP()得出每个号码的编号属于哪个编号段；3、用=VLOOKUP()将每个编号段对应的营业厅名字引用粘贴过来。解题方法2：1、先用=LEFT()得出编号段的起始值，并将属性转换为数值；2、用=LOOKUP()以B列编号为查询的关键字段，到原始信息表中查询，先判断属于哪个编号段，然后将该编号段对应的营业厅的名称引用粘贴过来。具体操作见附件11测试题6——常用统计函数应用•根据表中每个营业厅的移动座机销售明细，要求统计每个营业厅销售了多少台，各种型号的移动座机分别销售了多少台，每个营业厅的座机终端采购总价是多少•解题步骤：1、先以营业厅排序，用=IF()然后筛选得出不同的营业厅名称，然后用=COUNTIF()以不同的营业厅名称为统计条件，得出每个营业厅的销售台数。2、以座机型号排序，用=IF()然后筛选得出不同的座机型号，然后用=COUNTIF()以不同的座机型号为统计条件，得出每种型号座机的销售台数。3、用=SUMIF()以不同的营业厅为求和条件，得出每个营业厅的座机终端采购总价。具体操作见附件12如何自学函数如何自学：1、利用先前所学的函数能否解决，LEFT函数?不行，因为LEFT函数只能对文本字符进行操作，入网时间是日期格式，不可行。2、我们需要新学函数，点击fx,弹出“插入函数”的对话框，在搜索函数处输入想要实现功能的关键字，如“年份”，会显示出几个与之相关的函数，再一个个点击查看哪个最适合，这时会找到=YEAR()3、双击YEAR函数，弹出需输入参数的对话框，按说明操作即可。具体操作见附件：13查看帮助自学函数14多用、活用、妙用！网络部唐桂琼6405