在学习、使用Excel的过程中,很多人容易走入一个误区,也就是一味地追求“技术”。很多人往往能写出复杂的公式,多个函数嵌套、数组公式、多维引用等都不在话下,遇到问题都试图用一个公式来解决。其实,我想说的是,复杂的不一定是最好的。如果有简便一点的方法,为什么还要舍本逐末、舍近求远呢?所谓退一步海阔天空,今天举几个使用辅助列的例子,谈谈辅助列的用法。
说到多条件查找,有人用if({1,0},,)的用法,有人用Index Match数组,或者Offset、Indirect等配合Match数组函数,还有人用Dget函数...总之是各显神通。但是说到查找函数,我们首先想到的是Vlookup,只需要加个辅助列,不需要绞尽脑汁去记住其他复杂的方法。
比如,以下示例。
添加辅助列如下,设置公式:
=VLOOKUP(F2&'|'&G2,C:D,2,0)
这里需要特别指出一点,我习惯在链接两个字段时加上“|”这个分隔符,因为有些情况下,如果不加这个分隔符可能会导致一些错误。如果你确信两个字段不会有相似的内容,则不加也可以。
在这个示例中,我们只用了大家最熟悉的Vlookup函数和辅助列,简单而不费力,对于初学者来讲是一个不错的选择。
现在多条件求和一般直接使用Sumifs,但是,我相信,还有的人没学习过Sumifs函数,不会使用。但条件求和的Sumif更好理解一些。
请看下图示例,要按照两个条件进行数据汇总。
添加辅助列,将两列链接成一列作为Sumif函数的第一个参数,设置公式如下:
=SUMIF(C:C,G2&'|'&H2,E:E)
这个功能可以用于抽奖环节,也可以用于人员的随机分组、分配等。我们知道,生成随机数的函数有Rand和RandBetween。Rand生成不重复的随机小数;RandBetween生成指定范围内的随机整数,这个生成的结果可能会有重复的。但是抽奖、人员安排时都要求一个人不重复出现,这就要求随机生成的数是唯一的。
下面我们就演示用辅助列生成1~30范围内的不重复整数来生成获奖人员,共抽取5个人获奖。
A列共有30个人员姓名,B列使用公式=Rand()生成不重复的随机数,C列用Rank对B列的结果进行排序。C列公式如下:
=RANK(B2,$B$2:$B$31)
由于B列是不重复的随机数,所以C列排序的结果也就没有重复值。
E、G列使用了两种方式来生成获奖名单,两个公式不一样,所以结果也不一样,取任意一个公式即可。
公式1:=INDEX($A$2:$A$31,C2)
公式2:=INDEX($A$2:$A$31,MATCH(ROW()-1,$C$2:$C$31,0))
在Excel中目前没有按颜色求和或计数的函数,大家要使用时一般是自己编写自定义函数。除了自定义函数,我们还可以用宏表函数Get.Cell配合辅助列来进行颜色求和或计数。
获取单元格填充色的公式:
=Get.Cell(63,单元格地址)
获取单元格字体颜色的公式:
=Get.Cell(24,单元格地址)
注意,这里的单元格地址需要使用正确的引用类型。
当你需要固定地获取某列或某行单元格的颜色时,可以使用混合引用。比如你要获取B列单元格的颜色,把鼠标放到第一行的任意一个单元格,然后创建以下名称。
BackColor=GET.CELL(63,按颜色统计!$B1)
FontColor=GET.CELL(24,按颜色统计!$B1)
这里$B1是混合引用,表示列固定,行随公式所在的单元格变化而变化。
如下示例,辅助列可以放在任意一个地方,因为我们定义名称中固定取B列的单元格。H列公式为=SUMIF(E:E,G2,D:D)。这里在G列直接添上了颜色对应的代码,所以Sumif公式的第二个参数取G列单元格的值。
当你需要取活动单元格相对位置的颜色时,就在定义名称的单元格地址中使用相对引用。比如我们需要取左侧单元格的颜色,选中B1单元格,创建定义名称如下:
BackColorLeft=GET.CELL(63,按颜色统计2!A1)
FontColorLeft=GET.CELL(24,按颜色统计2!A1)
这时需要注意一点,辅助列也应该放到取颜色的单元格右侧。
如下图所示。
C列公式=BackColorLeft
H列公式=SUMIF(C:C,BackColorLeft,E:E)
辅助列用好了可以节省很多时间,可以解决一些复杂的问题。这里仅列举几个示例,希望对大家有所启发,关键的地方在于有使用辅助列的思维。条条大路通罗马,简单才是硬道理。
--End--
联系客服