Excel原来还可以这么用,你不曾知道的Excel条件格

2019-08-28 09:26 来源:未知

如下图所示,大家在依靠“姓名”和“科目”查询到相应分数之后,还亟需以热门特效的样式展示当前分数在原表中的地方,那样在面临海量数据筛选的时候再一次不怕看花眼了!

图片 1

大家先来看下二种借助扶助列举行“一对多询问”的点子,一种是用VLOOKUP函数,一种是用INDEX MATCH函数组合。用VLOOKUP函数达成“一对多询问”

图片 2

图文 | 安伟星    来源 | 精进Excel

一般来讲图所示,首先在A列创立协理列,在A2单元格输入如下公式,然后向下填充至A10单元格。= N

技能中央

本人在《Excel条件格式使用手册》一文中,从0到1截然讲明了Excel条件格式的前生今生,在颇具条件法则中,用公式调节格式是最灵敏、功效最有力的,因而笔者再开一篇教程,深刻讲解用公式调整格式的游戏的方法!

这一个公式是用来计算H2单元格的内容第一遍面世在B列,再次回到1、2、3...n体系值。

数量注明

坐好,开车!

图片 3

VLOOKUP MATCH准确查找

001 **契约到期提示**

图1

原则格式姓名、科目下拉菜单的炮制

举例:有一项协议管理的干活,法规是只要7天内到期:则设置栗色底纹填充、浅橙加粗字体展现;要是公约已经到期:则设置为白色底纹填充、石榴红加粗字体呈现。

下一场在I2单元格输入如下公式,然后向右、再向下填充至L10单元格。=IFEOdysseyRO本田CR-V(VLOOKUP,$A:$F,COLUMN,"")

首推,选中I2单元格,然后选拔菜单,找到命令,在弹出的对话框中,采用“类别”,填写“=$A$2:$A$9”,然后鲜明就可以。

图片 4

图片 5

下一场,J2单元格选择一样的秘诀,在对话框的填充“=$B$1:$G$1”,最后鲜明就能够。如此“姓名”和“科目”的多少个下拉菜单就制作达成了。

▍步骤:

图2用INDEX MATCH函数组合完结“一对多询问”

图片 6

Step1:选中E3:E8单元格区域,新建格式准绳

用INDEX MATCH进行“一对多询问”的规律和VLOOKUP一样,都是将对班级音讯的查询,转变到对数字体系1、2、3....n的询问。由此只须求将图第22中学的公式改为如下格局,就可以实现查询。=IFEXC60ROXC60(INDEX(C:C,MATCH,$A:$A,0)),"")

分数查询

Step2:设置公式1:=$E3-TODAY()<7

图片 7

然后须求基于I2和J2单元格的内容,查询出相应的分数,这里会用到VLOOKUP和MATCH函数。大家一向在K2单元格输入公式:=VLOOKUP(I2,A1:G9,MATCH(J2,A1:G1,0),FALSE)

Step3:设置金红底纹填充、樱草黄加粗字体魄式

图3

公式的意思是先用MATCH函数查找J2单元格对应的教程在A1:G1范围的索引值。然后用VLOOKUP函数在A1:G9范围内查找I2单元格对应的笔录,并赶回MATCH函数相配的目录对应的分数。多少个函数都以规范查找!

Step4:重复Step1-Step2,设置公式=$E3<TODAY(),然后设置煤黑底纹填充、品蓝加粗字体魄式

以上三种办法的详尽解说及注意事项,能够参照在此之前的一篇小说:Excel中二种广泛的“一对多”查询办法横向相比,孰优孰劣?用INDEX SMALL IF函数组合达成“一对多询问”

图片 8

图片 9

这种措施的最大利润是无需协助列,直接输入公式就能够获取结果,比前三种方法越发简便易行!如图4所示,大家在H2单元格输入如下公式,然后按住Ctrl Shift Enter,然后向右、向下填充至K10单元格。=IFEENVISIONROCR-V(INDEX(B:B,SMALL(IF($A$1:$A$10=$G$2,ROW,ROW

要害特效的造作

以此案例相比轻便,大家在初级教程中已经学过,其实正是多法则并存的原则格式设置。

最后的搜索结果如下图所示:

率先入选B2:G9区域,然后在菜单找到命令,在下拉菜单中甄选。

002 **报表美化之突显底纹**

图片 10

图片 11

当表格数据行非常多,大家为了让呈现效果尤其旗帜显然,能够让劳作表间隔固定行展现底纹,即每隔一行增添二个底色。

图4

在弹出的对话框中挑选最后的【使用公式明确要设置格式的单元格】选项,并输入公式“= ”,然后点击下方的开关设置自定义的字体颜色和背景颜色。

要隔行扩大底纹,最佳的艺术是运用「套用表格格式」,套用表格格式之后,会将区域转化为智能表格(Excel table),假若不想将单元格区域转化为表格,使用标准格式设置隔行底纹是个不错的挑三拣四。

大家来一步步深入分析这么些公式的运营原理。 IF($A$1:$A$10=$G$2,ROW

图片 12

▍步骤:

这有的公式用来将A1:A10限制内的值与G2单元格的值举办逐项比对,假设一致,则赶回A1:A10对应的行号。大家得以选中那有些公式,然后按下F9键查看最终结果。

在设置条件格式时,公式会自动将准绳应用到当选的区域中,公式中的“ ”意思是代表七个原则满意那一个,正是“恐怕”的意思。

Step1:选中需求安装格式的单元格区域,新建格式准绳

{FALSE;2;3;FALSE;5;6;FALSE;FALSE;FALSE;FALSE}

Step2:设置公式:=MOD(ROW(),2)=0

察觉重临的数组中只有2、3、5、6三个值,别的均为FALSE,也等于说在A1:A10限制的第2、3、5、6行找到了索要研究的多少!SMALL(IF($A$1:$A$10=$G$2,ROW,ROW

Step3:设置葡萄紫底纹填充

SMALL函数对上一步获得的数据结果开展取数,随着公式向下填充,依次提取第1、2、3...n个小小值,这一个数字对应的是符合条件班级的行号。例如我们选中H2单元格中的那部分公式,按F9显示查找结果{2},即获得了第三个地位十一分结果的行号为2。INDEX(B:B,SMALL(IF($A$1:$A$10=$G$2,ROW,ROW

MOD(ROW(),2)是判别当前行数是或不是能被2整除,满意整除的行数被安装为古铜黑填充,所以最后效果为偶数行填充为紫蓝。

这一步,是用INDEX定位对应B列数据的职分,结果为B2。

GIF>>

末尾的IFEPAJEROROQX56函数是为着挡住多余行中的错误。因为,若无IFE凯雷德RO卡宴函数,查询结果是其一样子的:

图片 13

图片 14

提示:函数MOD(number,divisor)重返两数相除的余数,个中Number为被除数,Divisor为除数。函数ROW(reference)重回引用的行号。个中Reference为急需获得其行号的单元格或单元格区域,假诺简单reference,则只借使对函数 ROW 所在单元格的援用。

图5总结

003 询问柔光灯效果

本文通过座谈了两种“一对多询问”的法子,引导大家进一步纯熟了多少个函数组合在查询中的规范用法。大家轻易看出三种方式各有上下,在其实的专门的学业实行中终归选择哪一类办法,要依附它们的性子,以及任务量大小等综合考量。

此处的焦点光灯指的是查询球后视神经炎灯,和WPS中的时时柔光灯(鼠标点在哪些单元格,对应的单元格的行和列会高亮)不太同样。Excel中也得以兑现时时强光灯,思路是均等的,只不过须要依附VBA达成再计算。

二种格局的优短处相比:

此处的询问高光灯的情趣是,根据已知的法则,查找到多少,会在原本是数码表高亮查询到的数目,以便于核对。

  1. VLOOKUP函数

图片 15

优点:公式相对简便易行,轻巧通晓。劣势:须求借助协助列,并供给扶助列必需是查询范围的第1列。

Step1:在G1、G2单元格分别安装姓名和学科的下拉菜单

  1. INDEX MATCH函数组合

(方法很简短,小编也写过特地的教程)

可取:灵活多变,不用考虑扶助列顺序。缺点:供给依据支持列,不太有利。

版权声明:本文由365bet官网娱乐发布于关于我们,转载请注明出处:Excel原来还可以这么用,你不曾知道的Excel条件格