EXCEL中用数组公式下拉填充单一符合条件的查询结果右拖填充多个符合条件的查询结果.doc

本文主要内容“EXCEL中用数组公式下拉填充单一符合条件的查询结果，右拖填充多个符合条件的查询结果和如何设置列中有重名的自动显红和当单元格不等于0或不等于1或空白时就显示红色底纹和IF函数中参数省略或为空的执行结果和提取特定字符之间字符串和如何判断(统计)某一个单元格内字符串中包含特定字符(串)的个数和LOOKUP函数的几种经典用法和下乡排表方法和让筛选后的序号自动连续和实现图片分页打印和PPT演示文稿打印讲义随意在幻灯片左右边添加或去掉横线（每页多少张不限）的方法和Word插入图片显示不全的解决办法和最后行与下页第一行重复打印的解决方法”E3=INDEX($B:$B,SMALL(IF($A$3:$A$9=$D3,ROW($A$3:$A$9),48),COLUMN(A1)&F3=IF(COUNTIF($A:$A,$E3)1-单元格格式为图案，最主要的是公式中的A:A表示A列（需操作的区域,如A1：A9），必须是你有数值的区域。EXCEL2007：选定需操作区域-开始-条件格式-新建规则-使用公式确定要设置格式的单元格-=COUNTIF(A:A,A1)1-单元格格式为填充，最主要的是公式中的A:A表示A列（需操作的区域,如A1：A9），必须是你有数值的区域。用红圈圈圈画Excel表格中的数据格公司统计时也许想把特别的数据突出显示，考试后老师也可能会想把Excel表格中高分或者容易错误的地方用红圈突出显示出来。这样在Excel中如何实现呢？下面我以成绩表为例讲解一下如何实现这一要求，本例以语文成绩在120分以上的用红圈圈画出来突出显示。屏显操作步骤：第一步：打开成绩表选择语文成绩单击菜单栏上的“数据”按钮选择“有效性”。第二步：在弹出的“数据有效性”对话框中，在“允许”一栏中选择“整数”在“数据”下面框中选择 “介于”最小值输入0最大值输入120确定。第三步：单击菜单栏上的“工具”按钮选择“公式审核”选择显示“公式审核”工具栏。第四步：单击“圈释无效数据”按钮。这样语文分数在120分以上的就被圈上了一个红圈，起到了突出显示的作用。但这些圈只能在电脑中显示出来并不能打印出来。有的老师想把它打印出来，笔者经过探索，找到一种可 行的办法，下面把这种方法介绍一下。打印操作步骤：第一步：选择表格按住Shift键不放单击菜单栏上的“编辑”按钮选择“复制图片”确定。第二步：单击菜单栏上的“编辑”按钮选择粘贴图片把原数据表删除调整一下复制的数据表的位置 就行了。注：在进行第一步和第二步的操作过程中要始终按住Shift键不放，否则操作不成功。当单元格不等于0，或不等于1，或空白时就显示红色底纹在第一个图中：选定需操作区域-格式-条件格式-单元格数值-单元格格式为图案-红色-然后按“添加”设置条件2、3，所得的结果却全部显示红色，这是因为三个条件之间没有明确并集或者交集的关系，所以要用第二个图的方法选定需操作区域-格式-条件格式-公式-=AND(A21,A20,A2)-单元格格式为图案-红色IF函数中参数省略或为空的执行结果IF(logical_test,value_if_true,value_if_false)其中有三个参数：第一个固定为逻辑参数，第二个固定为TRUE参数，第三个固定为FALSE参数。TRUE参数任何情况下都不能省略，只能为空，而FALSE参数可以出现省略或为空的情况。两个（TRUE参数和FALSE参数）都省略的话就没什么意义了（此时函数会出现错误无法运行），而两个都为空时，不管逻辑为TRUE或FALSE，执行结果都为0。当逻辑为TRUE时，TRUE参数为空，执行结果为0。 TRUE参数为非空值，执行结果为非空值。当逻辑为FALSE时，FALSE参数被省略，执行结果为FALSE。 FALSE参数为空，执行结果为0。 FALSE参数为非空值，执行结果为非空值。特别说明：TRUE参数为空即logical_test后有逗号但没有值，如“=IF(A20,)”、“ =IF(A20,)”、“ =IF(A20, 正数)”FALSE参数为空即 value_if_true 后有逗号但没有值并紧跟着右括号，如“=IF(A10,负数,)”、 “=IF(A10,)”FALSE参数被省略即 value_if_true 后没有逗号，如“=IF(A30,负数)”、“ =IF(A3=2,(MID(A2,SEARCH(.,A2,1)+1,SEARCH(.,A2,SEARCH(.,A2,1)+1)-SEARCH(.,A2,1)-1),(IF(LEN(A2)-LEN(SUBSTITUTE(A2,.,)=1,(MID(A2,SEARCH(.,A2,1)+1,LEN(A2)-SEARCH(.,A2,1),A2)分隔符的个数达到2个：(LEN(A4)-LEN(SUBSTITUTE(A4,.,)=2取第二个关键词的内容：(MID(A2,SEARCH(.,A2,1)+1,SEARCH(.,A2,SEARCH(.,A2,1)+1)-SEARCH(.,A2,1)-1)其中SEARCH(.,A2,1)+1是所取关键词的开始位置。其中SEARCH(.,A2,SEARCH(.,A2,1)+1)-SEARCH(.,A2,1)-1)是第二个关键词的长度。分隔符的个数是1个：(LEN(A2)-LEN(SUBSTITUTE(A2,.,)=1取第二个字符串的内容：(MID(A2,SEARCH(.,A2,1)+1,LEN(A2)-SEARCH(.,A2,1)其中SEARCH(.,A2,1)+1是所取关键词的开始位置。其中LEN(A2)-SEARCH(.,A2,1)是第二个关键词的长度。其他情况（分隔符的个数是0个）时直接用单元格的字符串：A2提取特定字符*与#之间字符串的方法：B1=MID(A1,FIND(8,A1)+1,FIND(#,A1)-FIND(*,A1)-2)SUBSTITUTE函数编辑在文本字符串中用 new_text 替代 old_text。如果需要在某一文本字符串中替换指定的文本，请使用函数 SUBSTITUTE；如果需要在某一文本字符串中替换指定位置处的任意文本，请使用函数 REPLACE。1语法编辑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。2示例编辑如果您将示例复制到空白工作表中，可能会更易于理解该示例。A1数据2销售数据32008年第一季度42011年第一季度公式说明（结果）=SUBSTITUTE(A2,销售,成本)“成本”替代“销售”（成本数据）=SUBSTITUTE(A3,一,二,1)用“二”代替示例中第一次出现的“一”（2008年第二季度）=SUBSTITUTE(A4,1,2,2)用“2”代替示例中第二次出现的“1”（2012年第一季度）提取特定字符*与#之间字符串的方法：B1=MID(A1,FIND(8,A1)+1,FIND(#,A1)-FIND(*,A1)-2)excel如何提取特定字符前后的字符串本例介绍如何提取文本字符串中特定字符前或后的内容。步骤阅读 操作步骤： 如下图，要提取#后面的字符，也即红色的“SDK”到B列。步骤阅读 首先，在B2中输入公式： =FIND(#,A2)返回#在字符串中的位置，#在A2单元格文本中是第6个字符。步骤阅读 知识点说明：FIND()函数查找第一参数在第二参数中的位置。如下图，查找“B”在“ABCD”中是第几个字符。第一参数是要查找的字符“B”，第二参数是被查找的字符串。最终返回“B”在“ABCD”中是第2个字符。1. 步骤阅读 109743.3524然后，在B2中输入公式：=MID(A2,FIND(#,A2)+1,99)这样，就提取出了#后的字符。步骤阅读 知识点说明：MID()函数返回从字符串中制定字符开始若干个字符的字符串。如下图，MID()函数返回“ABCDE”字符串中从第2个字符开始的连续3个字符，也就是返回“BCD”。2. 步骤阅读 综上，=MID(A2,FIND(#,A2)+1,99)的意思就是从A2单元格#字符后面的一个字符起，取长度为99的字符串。其中的99是一个较大的数字，能涵盖#后字符的最大长度即可。而要提取#前面的字符，可以用公式：=MID(A2,1,FIND(#,A2)-1)考勤表中每天下乡人员统计：=IF(OR(c7=,c8=),梁,)&IF(OR(c9=,c10=),黄,)&IF(OR(c11=,c12=),覃,)&IF(OR(c13=,c14=),刘,)&IF(OR(c15=,c16=),韦,)&IF(OR(c17=,c18=),阮,)&IF(OR(c19=,c20=),陈,)按顺序排列：=IF(ISNUMBER(SEARCH(梁,A1),梁,)&IF(ISNUMBER(SEARCH(黄,A1),黄,)&IF(ISNUMBER(SEARCH(覃,A1),覃,)&IF(ISNUMBER(SEARCH(刘,A1),刘,)&IF(ISNUMBER(SEARCH(韦,A1),韦,)&IF(ISNUMBER(SEARCH(阮,A1),阮,)&IF(ISNUMBER(SEARCH(陈,A1),陈,)excel如何判断(统计)某一个单元格内字符串中包含特定字符(串)的个数假设需要统计A1单元格TJfkFfkFG中字符串“fk”的个数公式=(LEN(A1)-LEN(SUBSTITUTE(A1,fk,)/2公式中函数LEN是计算字符串中有几个字符；SUBSTITITE函数是将字符串中的指定字符替换成新的字符，语法结构如下：SUBSTITUTE(text, old_text, new_text, instance_num)SUBSTITUTE 函数语法具有下列参数 （参数：为操作、事件、方法、属性、函数或过程提供信息的值。）：Text 必需。需要替换其中字符的文本，或对含有文本（需要替换其中字符）的单元格的引用。Old_text 必需。需要替换的旧文本。New_text 必需。用于替换 old_text 的文本。Instance_num 可选。用来指定要以 new_text 替换第几次出现的 old_text。如果指定了 instance_num，则只有满足要求的 old_text 被替换；否则会将 Text 中出现的每一处 old_text 都更改为 new_text如果new_text为空(或省略)的话，就是将原指定字符用空符代替，也就是将指定字符删除。整个公式=(LEN(A1)-LEN(SUBSTITUTE(A1,fk,)的意思就是原字符串长度减去删除指定字符(串)长度，结果就是特定字符(串)的长度，再除以2(/2)就得字符串fk个数(若要统计包含特定单个字符的个数则不用除以2)。尊享无限量精美信纸！立即LOOKUP函数的几种经典用法篇外话：对于lookup的计算原理，可以自行百度搜索Excel二分法原理了解，这方面的内容要细说比较复杂，不过要想用好lookup也不是非得掌握这个原理不可，好比很多数学公式，我们都是直接拿来用，但是要去证明或者推导公式，就需要更多的专业理论才行。当然，如果以后有机会的话，会用比较通俗好理解的言辞来专题讨论二分法了。本帖目的在于将lookup函数的常用写法进行整理后方便使用者随时查找套用，用的多了自然会达到熟能生巧的效果，更重要的是，lookup的计算效率和功能之强大的确是令人称赞的。一、逆向查找、多条件查找：先看例题：一般这样的查找可以使用vlookup或者index+match这些函数和数组配合使用来实现需要，但是使用lookup的话只要记得查找公式的标准格式，然后不管多少个条件，都可以自己套写公式了。例一公式为：=LOOKUP(1,0/(B2:B9=G2),A2:A9)例二公式为：=LOOKUP(1,0/(A2:A9=G6)*(C2:C9=H6),B2:B9)这两个公式就是LOOKUP函数最典型用法。可以归纳：=LOOKUP(1,0/(条件),目标区域或数组)其中，条件可以是多个逻辑判断相乘组成的多条件数组，=LOOKUP(1,0/(条件1)*( 条件2)* ( 条件N),目标区域或数组)使用要求：能够理解条件的写法以及目标区域的写法，记准公式格式，注意条件之间的括号，切不可配错哦。二、要查询A列中的最后一个文本，也非LOOKUP函数莫属，用到的公式是：=LOOKUP(,A:A )通常被看做是一个编码较大的字符，它的输入方法为组合键。如果感觉每次写这个符号有点费事儿，也可以写成：=LOOKUP(座,A:A )一般情况下，第一参数写成“座”也可以返回一列或一行中的最后一个文本。三、要查询A列中的最后一个数值，用到的公式是：=LOOKUP(9E307,A:A )9E307被认为是接近Excel规范与限制允许键入最大数值的数，用它做查询值，可以返回一列或一行中的最后一个数值。很多时候9E307也可以99取代，也即9个9相乘的结果387420489，不过，如果你的数据中有超过这个数字的，还是用9E307稳妥一些。四、根据简称查询全称的问题相信大家都会经常遇到吧？如下面这个图中所示，A列是客户的简称，要求根据E列的客户全称对照表，在C列写出客户的全称。C2单元格输入以下公式，可得到“上海沛发”的客户全称“上海沛发包装材料有限公司”。=IFERROR(LOOKUP(1,0/FIND(A2,E$2:E$13),E$2:E$13),)公式中“0/FIND(A2,E$2:E$13)”部分，首先用FIND函数查询A2单元格“上海沛发”在E$2:E$13的起始位置，得到一个由错误值和数值组成的数组。余下部分的计算过程就和咱们前面说过的一样了，使用IFERROR函数来屏蔽公式查询不到对应结果时返回的错误值。五、多个区间的条件判断。例题如图，50分以下的为“很差”，50-59分的为“差”，60-74分的为“一般”，75-85分的为“较好”，86-95分的为“优秀”，96分及以上的为“能手”。这种多个区间的判断，如果需要判断的条件和区间都很多，如果使用IF函数来计算，估计会把自己都转晕了。而使用LOOKUP函数来解决，不过是小菜一碟而已。C2单元格输入以下公式，向下复制即可。=LOOKUP(B2,0,50,60,75,86,96;很差,差,一般,较好,优秀,能手)EXCEL LOOKUP公式第一个参数等于第二个参数为什么会这样？ =LOOKUP(1,1/($A$2:A11),$A$2:A11)故意让第二个参数的分子为1/($A$2:A11)，为什么会出现这种情况，不是返回等于参数2数组中等于1且最后的一个值吗？如图2中A11不是应该返回 辽A-10005 吗？求解啊，难道必须第一个参数大于第二个参数的分子吗？等于为什么会这样？提问者采纳=LOOKUP(1,0/($A$2:A11),$A$2:A11) 是0/若是1/的话，就要=LOOKUP(2,1/($A$2:A11),$A$2:A11)解读：1、$A$2:A11会返回false或true。逻辑值参与计算时false=0,true=12、用某数/上述逻辑值(0或1）会得到错误值和原数。3、lookup查询时，会略去错误值。4、lookup在找不到与查询目标一致的结果时，就返回比查找值小的最接近的数据。5、你这是向量用法。根据你的数据环境，要填充空白区域为最接近的数据。所以，你的查询数组中不能有和查询目标相等的数据。补充：lookup第一个参数省略是什么意思？是不是表示0？是表示0，通常情况下，很多函数中的参数为0时，可以简写0，但需要用半角逗号占位。excel包含函数 如果A中包含“cbjf”则显示“本草” “az_jydb”则显示“胶原” “myjn”则显示“免疫” “szsp”则显示“水蛭” “lm”则显示“蓝莓”提问者采纳B1单元格输入公式=LOOKUP(,0/FIND(cbjf,az_jydb,szsp,myjn,lm,A1),本草,胶原,免疫,水蛭,蓝莓)下拉填充公式如果有其它项目需要纠错增加一个函数=iferror(LOOKUP(,0/FIND(cbjf,az_jydb,szsp,myjn,lm,A1),本草,胶原,免疫,水蛭,蓝莓),)提问者评价按照你说的，真的成功了，好开心，谢谢你！第三个参数省略情况：这里的“省略参数”有两个含义。1、省略参数函数中只可见两个参数，像下面这样：=match(0,1;1;1;0;0;0;0;1;1;0;0)这种情况下，不存在第三个参数，计算中，第三个参数默认为1，函数相当于：=match(0,1;1;1;0;0;0;0;1;1;0;0,1)2、参数省略不写函数中存在第三个参数，只是一个位置，没有具体的值：=match(0,1;1;1;0;0;0;0;1;1;0;0,)这种情况下，如上面提到第一个参数一样，省略不写的参数相当于0：=match(0,1;1;1;0;0;0;0;1;1;0;0,0)其它见帮助。提取单元格特定字符：若单元格A2字符长度5，则辅助单元格显示第1&3&5个字符，否则显示第1&3个字符=IF(LEN(A2)=5,MID(A2,1,1)&MID(A2,3,1)&MID(A2,5,1),MID(A2,1,1)&MID(A2,3,1)下乡排表方法用排课软件导出excel表格后，经过删除、合并单元格；用公式提取姓氏到同一单元格，若姓氏单元格中有空格，在辅助列中用以下函数提取只含姓氏的值：=IF(LEN(A2)=5,MID(A2,1,1)&MID(A2,3,1)&MID(A2,5,1),MID(A2,1,1)&MID(A2,3,1)然后用以下函数把各单元格中姓氏按规律排列：=IF(ISNUMBER(SEARCH(梁,A1),梁,)&IF(ISNUMBER(SEARCH(黄,A1),黄,)&IF(ISNUMBER(SEARCH(覃,A1),覃,)&IF(ISNUMBER(SEARCH(刘,A1),刘,)&IF(ISNUMBER(SEARCH(韦,A1),韦,)&IF(ISNUMBER(SEARCH(阮,A1),阮,)&IF(ISNUMBER(SEARCH(陈,A1),陈,)再用数组公式下拉填充单一符合条件的查询结果，右拖填充多个符合条件的查询结果即可知道相同人员共同下乡日期。最后用以下函数来验证各人的天数是否符合：=COUNTIF($B$2:$B$21,*单姓氏*)或=COUNTIF($B$2:$B$21,*&单姓氏单元格&*)让Excel筛选后的序号自动连续，用以下公式：=SUBTOTAL(3,$B$1:B1)&或=SUBTOTAL(3,$B$1:B1)*1若不加上*1或者&自动筛选时，最后一行不能隐藏，即筛选一班的时候，最后还出现三班。这是由于有了subtotal且在自动范围的最后一行，数据范围就会排除走这一行，表现出来就是很多时候它会把最后一行当成汇总行，所以在筛选、汇总的时候经常有这样的问题，当*1或者&之后，excel便把A列当成subtotal产生的数据（序号），就不会出现问题用EXCEL实现图片分页打印超级实用用EXCEL实现图片分页打印大图片分页打印方法方法一、新建一个EXCEL文档，插入图片，然后设置合适的列宽、列高(在EXCEL里47行正好是A4纸的标准1页，所以如果多于47行，就自动分成下一页了)设置列宽、列高，只需选中该图片，把光标放到图片边角的小圆圈上，直接拉伸大小，这样就能自动分页打印了。或者选中该图片，打开打印预览选择页面设置缩放选项第2个选项，调整为几页宽(一般都是1页了)几页高，预览里能更方便的看到共有几页，图片的每部分是在第几页显示了，设置好页数，打印就OK了。如果追求高清晰度，可以选择按百分比缩放，宽屏的专题页面图设为75%正好，但是比较费纸张了。方法二、这个方法也是用EXCEL软件来打印，但方法不一样：新建一个EXCEL文档，插入图片，然后在该图片上右键设置图片格式大小比例中的高度和宽度，我这里设置成20%，这个根据自己的需要调整，可以用“打印预览”来查看共有几页。方法三、分页打印软件最真实无误的办法有些软件能自动把大于A4纸的图片分割成几页来打印的，例如windows的画图软件。经道友测试，用开始程序附件画图，打开一个超高图片，打开打印预览，即可看到一共有几页，当前是第几页，如果图片太宽，也会自动分页的，最后可以完美的拼凑起来。windows的画图软件可以人调整。需要在页面调整中进行设置。关键项是：页面设置-缩放比例-适合于，我填的是1*3，然后再调整页边距。调整结束后，使用打印预览看看设置是否合适。此外,PM也可用于分页打印大图，全称就是大名鼎鼎的Adobe PageMake，专业的一款平面排版软件。先把要打印的图存为tif图(分色印刷的标准格式)， 用PM打开，打印时设置成自动拼贴，就能很方便的打印分页图了。具体方法是：先选中打印，然后在打印里有个打印选项拼贴，可以选手动或自动，接着输入你要拼凑的尺寸(即相交的尺寸)，最后确定。你就只需要放入足够的纸就OK了，其他的完全可以交给电脑为你计算了。PPT演示文稿打印讲义随意在幻灯片左右边添加或去掉横线（每页多少张不限）的方法先把PPT文档发送到Word中，然后在Word中适当编辑一下。以Powerpoint2003为例：1、打开需要处理的PPT演示文稿，工具栏依次点【文件】-【发送】-【MicrosoftOfficeWord】。2、在弹出的对话框中，按照默认的设置，确定，就可以了。之后，会自动打开Word，稍等片刻，发送就完成了。3、点击任务栏出现的Word图标，你会发现，PPT演示文稿已经全部转换的Word中了。但这种自作多情的转换似乎不是你提问中需要达到的目的。4、接下来在Word中做简单的编辑，就可以实现（其实这些对你来说应该轻而易举的，本着中规中矩的准则，还是啰嗦一下下）。去掉编号列，将幻灯片列移动到右侧（剪切粘贴列，我是这么操作的），根据需要调整调整布局，打开表格编辑对话框，再对行高、PPT图片对齐方式进行适当调整。这样，基本就OK了。Word插入图片显示不全的解决办法如上Word文档插入图片只显示一部分，这是因为段落设置了固定值的行距而导致。解决办法：方法一：A、Word2007单击选中图片，然后在“开始”选项卡“样式”组，单击“正文”。Word2003则在如右图样式的小三角下拉菜单中选“正文”。B、现在即可显示全图了，如下图所示。方法二：A、将光标移动到插入图片的地方，然后右击，在快捷菜单中选择“段落”。、打开“段落”对话框，将“缩进和间距”选项卡中“行距”设为“单倍行距”或“1.5倍行距”或“2倍行距”或“多倍行距”（这里以选择单倍行距为例），然后单击“确定”。C、现在即可显示全图了，如下图所示。excel打印时某一页的最后行与下一页的第一行重复打印的解决方法将某张图片粘贴到Excel中，因为图片很长(如下图有从1到200多行的图片)，需要打印成多页，但是在打印出来发现某页的最后一行与下一页的第一行重复打印。例如63在第三页、第四页都会出现，有没有什么办法解决这样的问题?解决方法，也分享给大家:打印之前，在页面设置的-工作表-行号列标前打个勾，这样打印出来的内容就不会重复了。打开excel文件时显示“文件正在使用提示框，xx.xls正处于锁定状态，“”正在编辑”，有“只读”、“通知”、“取消”三个选项，点击“只读”和“通知”两项，就进入只读状态。这是因为EXCEL非正常退出或关闭造成的。这