您的位置:新葡亰496net > 新葡亰496net > 查找员工信息只需3秒,excel怎么制作抽奖小程序

查找员工信息只需3秒,excel怎么制作抽奖小程序

发布时间:2019-09-29 11:19编辑:新葡亰496net浏览(55)

    问题:excel怎么制作抽奖小程序?

    公司马上就要开年会了,如果能够给公司做一个Excel抽奖小程序,是多么开心 装B的事情。

    VLOOKUP函数是工作中最常用的一种查找函数,掌握好VLOOKUP函数能够极大提高工作的效率。

     

    上次跟大家讲了,如何用VLOOKUP函数建立《员工档案管理表》。VLOOKUP函数虽然很强大,但其基本用法中只能作正向查找,而不能反向查找,即根据前列查找后列的内容,而不能根据后列查找前列内容。

    回答:年尾了,很多企业年会都有抽奖环节,你还在弱弱的使用把名字写好放入抽奖箱的原始方式吗?其实我们只要一台电脑上的EXCEL就可以实现随机抽奖功能,听起来高大上的样子,哈!先看动态效果图:

    效果如下

    VLOOKUP函数用于首列查找并返回指定列的值,字母“V”表示垂直方向。

    (Excel)常用函数公式及操作技巧之三:

    新葡亰496net 1

    新葡亰496net 2如上图,按住F9键就可以随机抽奖了,名字随机变化!

    新葡亰496net 3

    VLOOKUP函数的语法如下:

    排名及排序筛选(二)

    如我用VLOOKUP函数,可以根据工号来查姓名,但是反过来不行。但如果我用INDEX MATCH函数组合,可实现双向查找,可顺可逆。

    具体制作方法:

    image

    VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

    ——通过知识共享树立个人品牌。

    接下来,我们还是以《员工档案管理表》举例,如何根据姓名来查员工信息。

    1.首先把名字输入到表格当中,设定一个显示中奖员工名字的单元格(可以大一点方便显示),在单元格输入公式

    首先我们来讲解下面三个函数

    其中,第1参数lookup_value为要搜索的值,第2参数table_array为首列可能包含查找值的单元格区域或数组,第3参数col_index_num为需要从table_array中返回的匹配值的列号,第4参数range_lookup用于指定精确匹配或近似匹配模式。

     

    1、设置数据有效性

    新葡亰496net 4

    ① rand函数

    当range_lookup为TRUE、被省略或使用非零数值时,表示近似匹配模式,要求table_array第一列中的值必须按升序排列,并返回小于等于lookup_value的最大值对应列的数据。当参数为FALSE时(常用数字0或保留参数前的逗号代替),表示只查找精确匹配值,返回table_array的第一列中第一个找到的值,精确匹配模式不必对table_array第一列中的值进行排序。

     

    新葡亰496net 5

    2.选择B列名字,设置单元格条件格式

    =rand() 表示随机返回0到1之间的任何小数。当我们按下【F9】后,数字会发生变化。

    如果使用精确匹配模式且第1参数为文本,则可以在第1参数中使用通配符问号(?)和星号(*)。VLOOKUP函数不区分字母大小写。

    求最精简的自动排名公式

    关键步骤:

    新葡亰496net 63.最后按住F9键不放就可以实现随机抽奖功能了!也可把名字改变成奖品名称,那随机显示的就是奖品了,可以用于显示员工随机抽中的奖品是什么!

    具体如下图所示。

    案例一

    =RANK(E2,$E$2:$E$21)

    数据—数据有效性—允许(序列)—来源(框选数据源)

    公式解析:

    新葡亰496net 7

    A3:B7单元格区域为字母等级查询表,表示60分以下为E级、60~69分为D级、70~79分为C级、80~89分为B级、90分以上为A级。D:G列为初二年级1班语文测验成绩表,如何根据语文成绩返回其字母等级?

    =RANK(A2,$A$2:$A$9,0)

    2、INDEX函数

    由和两个函数综合运用合成,randbetween是返回一个介于指定数字的随机数,INDEX是返回给定单元格的值,randbetween显示的是一个数字,用index把这个数字代表的值(名字)显示出来!

    image

    在H3:H13单元格区域中输入=VLOOKUP(G3,$A$3:$B$7,2)

    =RANK(A2,$A$2:$A$10) COUNTIF($A$2:$A2,$A2)-1(如果数据列中数值有相同)

    新葡亰496net 8

    新葡亰496net 9新葡亰496net 10

    ② index函数

    新葡亰496net 11

    =RANK(F10,$F10:$Q10) COUNTIF($F10:F10,F10)-1

    INDEX翻译成中文的意思是“索引”,INDEX函数是指返回指定区域、指定位置的数值。

    回答:感谢邀请,excel制作抽奖程序有很多方法,在这里我给您提供三种。

    index函数为定位函数。

    案例二

    =INDEX(A:A,1/MOD(LARGE(E$2:E$21 1/ROW($2:$21),ROW(1:1)),1))

    INDEX的语法:

    1.INDEX RANDBETWEEN函数

    =index(数值范围,行号A,列号B)

    在Sheet1里面如何查找折旧明细表中对应编号下的月折旧额?(跨表查询)

    =LOOKUP(1,0/(($F$2:$F$21=A27)*(COUNTIF(D$26:D26,$A$2:$A$21)=0)),$A$2:$A$21)=INDIRECT("A"&RIGHT(LARGE(($E$2:$E$21*100 ROW($A$2:$A$21)),ROW(A1)),2))

    INDEX(array,row_num,column_num),指返回数组中指定的单元格或单元格数组的数值。

    如下图,在D2单元格输入公式:=INDEX($A$2:$A$11,RANDBETWEEN(1,10)) 按F9刷新可实现抽奖效果。RANDBETWEEN(1,10)表示姓名的个数,从1-10个姓名之间随机抽取。

    表示的是,在数值范围内,查找出第A行,第B列的数据。

    在Sheet1里面的C2:C4单元格输入=VLOOKUP(A2,折旧明细表!A$2:$G$12,7,0)

    =RANK(C2,OFFSET($C$1,MATCH(E2,$E$2:$E$768,),,COUNTIF($E$2:$E$768,E2)))

    INDEX(reference,row_num,column_num,area_num),指返回引用中指定单元格或单元格区域的引用。

    新葡亰496net 12

    如图所示, 需要查找出范围A1:A7中,第3行,第1列的数据。函数表达式如下图。

    新葡亰496net 13

    数组公式

    简单点说就是 INDEX(区域,第几行,第几列),返回区域第几行第几列的数值。

    2.OFFSET RANDBETWEEN函数

    新葡亰496net 14

    新葡亰496net 15

    {=INDEX(A:A,MOD(LARGE(E$2:E$21*100 ROW($2:$21),ROW(1:1)),100))}

    那么,如何用INDEX函数来找出李四的身份证号呢?

    如下图,在D2单元格输入公式:=OFFSET($A$1,RANDBETWEEN(1,10),,) 按F9刷新可实现抽奖效果。

    image

    案例三

    {=OFFSET($A$1,RIGHT(LARGE($E$2:$E$21*1000 ROW($E$2:$E$21),ROW()-25),3)-1,,)}

    新葡亰496net 16

    新葡亰496net 17

    ③ rank函数

    如何实现通配符查找?

    =OFFSET($A$1,RIGHT(LARGE(($E$2:$E$21*100 ROW($A$1:$A$20)),ROW(A3)),2),)

    关键步骤:

    3.同时抽取多个不重复中奖者

    语法:

    在B2:B7区域中输入公式=VLOOKUP(A2&"*",折旧明细表!$B$2:$G$12,6,0)

    =TEXT(SUMPRODUCT(($E$2:$E$21>=E2)/COUNTIF($E$2:$E$21,$E$2:$E$21)),"第[DBNUM1]G/通用格式名")

    输入INDEX—框选查找区域—李四的身份证号在第3行第3列

    首先在B2单元格输入:RAND()生成随机小数

    =rank(需要排序的数字, 排序范围,排序规则)

    新葡亰496net 18

    排序后排名

    做到这一步,很多人会直接往右边复制公式,结果看似和源数据一样,但是换个姓名后面的信息并没有随之变化,所以这样是不可取的。

    新葡亰496net 19

    函数就表示,需要排序的数字在排序范围内,排名第几名。

    新葡亰496net 20

    {=SUM(IF($B$2:$B$15>=B2,1/COUNTIF($B$2:$B$15,$B$2:$B$15)))}

    3、COLUMN函数

    然后在E2单元格输入公式:=INDEX($A$2:$A$11,MATCH(LARGE($B$2:$B$11,ROW(A1)),$B$2:$B$11,0)) 使用LARGE函数提取第1、第2、第3最大值,使中奖姓名不会出现重复。

    排序规则参数:0 表示降序,1表示升序。

    案例四

    =SUMPRODUCT((B$2:B$15>=B2)/COUNTIF(B$2:B$15,B$2:B$15))

    新葡亰496net 21

    新葡亰496net 22

    例如,降序排列中,求出23在第一列数据中,排名第几?

    如何实现模糊查找?

    位次排名

    身份证号是在源数据的第3列,出生日期是在源数据的第4列,以此类推……

    回答:

    方法:= rand(A1,A1:A6,0)

    在F1:F9区域中输入公式=VLOOKUP(E2,$A$2:$B$7,2,1)

    {=IF($B2:$O2>=0,RANK($B2:$O2,$B2:$O2,0),)}

    这个时候,我们可以用COLUMN函数来表达列标,即COLUMN()是指查看所选择的某一个单元格所在第几列,即它是第几列。

    制作简易抽奖小程序,需要用到如下技巧:

    新葡亰496net 23

    新葡亰496net 24

    根据双列成绩进行共同排名

    4、MATCH函数

    • F9,重新计算所有数据;

    • Counta单元格计数;

    • Randbetween生成一个随机数;

    • Index检索单元格

    image

    案例五

    =RANK(C345,($C$345:$C$356,$H$345:$H$356))

    新葡亰496net 25

    F9

    按下F9,Excel就会重新计算数据。

    若,你的表格数据量很大,公式很多,每次变动数据,Excel变得很卡时,可以将Excel设置为手动计算,然后使用F9来获取最新数据。

    新葡亰496net 26

    抽签思路:

    ①利用Rand函数,使得名单都有对应的一个数值

    ②利用rank函数进行排名

    ③利用INDEX函数,查找名单,注意行号是随机变化的。因此返回的结果是随机的

    因此,抽签函数的最终公式为

    =INDEX(A2:B7,RANK(B2,B2:B7,0),1), 其中B2输入公式为 = rand()

    具体步骤如下图

    第一步

    新葡亰496net 27

    image

    第二步

    新葡亰496net 28

    image

    美化后,最终的效果如下图

    新葡亰496net 29

    image

    Excel抽奖小程序你学会了吗?

    如果感觉有帮助,请点下“喜欢”吧!

    如何通过数值查找文本数据、通过文本查找数值数据、同时实现数值与文本数据混合查找?

    在双列间排名

    COLUMN函数解决了列标的问题,行号我们要用到MATCH函数来表示。

    抽奖公式

    • Counta获取总人数;Counta(区域)

    • 使用Randbetween函数生成指定范围的随机数;Randbetween(开始数字,结束数字)

    • 再使用Index函数获取中奖人员;INDEX(要查找的区域, 第几行, 第几列)

    新葡亰496net 30

    通过数值查找文本数据:在F3:F6区域中输入公式=VLOOKUP(E3&"",$A$2:$C$6,3,0)

    =RANK(B2,($B$2:$B$26,$E$2:$E$16))

    MATCH函数的意思是返回目标值在查找区域中的位置,语法为:

    制作简易抽奖小工具

    如下所示,按住F9再松开即可。

    新葡亰496net 31

    通过文本查找数值数据:在F11:F13区域中输入公式=VLOOKUP(--E11,$A$10:$C$14,3,0)

    等次排名

    MATCH(lookup_value,lookuparray,match-type)

    这个蛮好玩的,试试吧!

    回答:公司马上就要开年会了,如果能够给公司做一个Excel抽奖小程序,是多么开心 装B的事情。

    效果如下

    新葡亰496net 32

    首先我们来讲解下面三个函数

    同时实现数值与文本数据混合查找:在F19:F21区域中输入公式=IF(ISNA(VLOOKUP(E19*1,$A$18:$C$22,3,0)),VLOOKUP(E19&"",$A$18:$C$22,3,0),VLOOKUP(E19*1,$A$18:$C$22,3,0))

    由大到小排名

    lookup_value:表示查询的指定内容;

    rand函数

    =rand() 表示随机返回0到1之间的任何小数。当我们按下后,数字会发生变化。

    具体如下图所示。

    新葡亰496net 33

    新葡亰496net 34

    =RANK(B3,$B$3:$B$12)

    lookuparray:表示查询的指定区域;

    index函数

    index函数为定位函数。

    =index(数值范围,行号A,列号B)

    表示的是,在数值范围内,查找出第A行,第B列的数据。

    如图所示, 需要查找出范围A1:A7中,第3行,第1列的数据。函数表达式如下图。

    新葡亰496net 35

    案例六

    =SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25>B16)) 1

    match-type:表示查询的指定方式,用数字-1、0或者1。

    rank函数

    语法:

    =rank(需要排序的数字, 排序范围,排序规则)

    函数就表示,需要排序的数字在排序范围内,排名第几名。

    排序规则参数:0 表示降序,1表示升序。

    例如,降序排列中,求出23在第一列数据中,排名第几?

    方法:= rand(A1,A1:A6,0)

    新葡亰496net 36

    抽签思路:

    ①利用Rand函数,使得名单都有对应的一个数值

    ②利用rank函数进行排名

    ③利用INDEX函数,查找名单,注意行号是随机变化的。因此返回的结果是随机的

    因此,抽签函数的最终公式为

    =INDEX(A2:B7,RANK(B2,B2:B7,0),1), 其中B2输入公式为 = rand()

    具体步骤如下图

    第一步

    新葡亰496net 37

    第二步

    新葡亰496net 38

    美化后,最终的效果如下图

    新葡亰496net 39

    Excel抽奖小程序你学会了吗?

    欢迎关注,更多惊喜等你来。如果需要获取源文件,欢迎私信雷哥。

    回答:感谢诚邀!

    春节尾牙将至,如何用Excel制作抽奖器呢?

    小编分享2种类型的抽奖器

    在Excel中录入数据信息时,为了提高工作效率,用户希望通过输入数据的关键字后,自动显示该记录的其余信息,例如,输入员工工号自动显示该员工的信命,输入物料号就能自动显示该物料的品名、单价等。如图所示为某单位所有员工基本信息的数据源表,在“2010年3月员工请假统计表”工作表中,当在A列输入员工工号时,如何实现对应员工的姓名、身份证号、部门、职务、入职日期等信息的自动录入?

    由小到大排名

    match_type=0(精确查找)

    1. 简单、朴素且实用的抽奖器

    新葡亰496net 40

    新葡亰496net 41

    解决方案1:使用VLOOKUP MATCH函数

    =RANK(B3,$B$3:$B$12,1)

    查找精确等于lookup_value的第一个数值,lookup_array按任意顺序排列。一般只使用精确查找。

    2. 复杂、美观且专业的抽奖器

    新葡亰496net 42

    新葡亰496net 43

    鸣谢:需要源文件,请关注后,发私信给我噢!!发私信!!

    回答:查找员工信息只需3秒,excel怎么制作抽奖小程序。EXCEL可以抽奖,通过Randbetween、rand等函数来获取随机结果,也是可以的,但不直观,不明白的也不知道这个结果怎么来的,公不公平。

    现在给大家分享一个PPT EXCEL来实现的抽奖程序。

    到年底了,很多公司都在准备年会,但年会抽奖是少不了的,为了使抽奖程序更直观,通过VBA在PPT中调用后台EXCEL中的抽奖人员名实现随机滚动的效果,在滚动过程中按回车实现抽奖结果随机定位,中奖的人员将不再进行后续的抽奖,并将抽奖结果写入EXCEL的中奖人员中。

    功能如下:

    • 座位号循环随机滚动

    • 按回车键抽奖一次抽出一名员工,并将中奖的员工信息写入EXCEL中奖人员表中,同时此员工不再参加后续抽奖

    • 可以简单修改后设定一等奖、二等奖、三等奖等奖项

    • 抽奖结果在EXCEL抽奖人员中随机抽取,绝对公平,程序代码不控制抽奖结果,抽奖结果取决于抽奖时按回车时随时循环滚动的名单确定。

    新葡亰496net 44

    抽奖主界面,可以添加图片及动画效果

    新葡亰496net 45

    抽奖界面,可以添加图片及动画效果

    新葡亰496net 46

    抽奖后记录中奖人员名单

    新葡亰496net 47

    抽奖时,通过座位号随机滚动抽奖人员

    新葡亰496net 48

    部分VBA代码

    回答:有时候我们遇到一些重要的场合,有抽奖活动,那么怎么样才可以做出一个有趣的抽奖界面呢?用Excel就可以!今天教大家两种方法轻松制作出抽奖小程序。

    在“2010年3月员工请假统计表”工作表中选择B3:F8单元格区域,输入下列公式,按【Ctrl Enter】组合键结束。

    =SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25<B16)) 1

    match_type=1

    新葡亰496net,第一种方法:

    INDEX函数

    具体公式:在D2单元格输入

    PS:RANDBETWEEN(1,10)表示抽取的总数

    新葡亰496net 49

    输入完毕,按F9刷新,抽奖功能就完成了,看,一次一个名字哦!

    新葡亰496net 50

    =IF($A3="","",VLOOKUP($A3,员工基本信息!$A:$H,MATCH(B$2,员工基本信息!$2:$2,0),0))

    不等次排名(行小排先)

    查找小于或等于lookup_value(目标值)的最大数值在lookup_array(查找区域)中的位置,lookup_array必须按升序排列。

    第二种方法:

    查找员工信息只需3秒,excel怎么制作抽奖小程序。OFFSET函数

    具体公式:在D2单元格输入

    PS:RANDBETWEEN(1,10)同样表示抽取的总数

    新葡亰496net 51

    输入完毕,按F9刷新,抽奖功能就完成了!也很简单哟!

    新葡亰496net 52

    是不是很简单?是不是很想收藏?学会了这招,以后公司年会的抽奖你承包啦!让领导对你刮目相看!

    回答:新葡亰496net 53岁末年初,很多公司都会举办年度酒会。在年度酒会上一般都会进行抽奖。如果用传统的纸条抽奖,既繁琐又招人怀疑是否有内幕。很多人会选择编程来进行抽奖,其实我们可以用excel函数公式来制作抽奖小程序。今天就让我们一起来学习如何制作Excel 抽奖小程序。

    新葡亰496net 54

    大观园内举办一场年度酒会,准备从三十六钗中抽取5个幸运者。每个人不能重复上榜,重复中奖。

    1、先在B列插入随机数1-36,B2=RANDBETWEEN(1,36),双击下拉填充到底。

    公式解读:

    RANDBETWEEN函数:返回大于等于指定的最小值,小于等于指定最大值之间的一个随机整数。其函数语法为:RANDBETWEEN(bottom,top)Bottom参数: RANDBETWEEN将返回的最小整数。Top参数: RANDBETWEEN将返回的最大整数。RANDBETWEEN(1,36)是产生随机整数1至36。

    2、在D1中输入幸运名单,在D2输入公式:=INDEX($A$2:$A$37,RANK(B2,$B$2:$B$37)),往下拖动五行,就能产生五个幸运者。

    公式解读:

    Rank函数最常用的是求某一个数值在某一区域内的排名。rank函数语法形式:rank(number,ref,[order])函数名后面的参数中 number 为需要求排名的那个数值或者单元格名称(单元格内必须为数字),ref 为排名的参照数值区域,order的为0和1,默认不用输入,得到的就是从大到小的排名,若是想求倒数第几,order的值请使用1。RANK(B2,$B$2:$B$37)意思是B2在B2至B37中排名第几,B2至B37必须采用绝对引用,否则公式下拉之后会导致排名区域发生变动,导致出错。

    Index函数语法:INDEX(reference,row_num,column_num,area_num)Reference是对一个或多个单元格区域的引用。· 如果要对引用输入一个非连续区域,请使用括号将该引用括起来。· 如果引用中的每个区域都只包含一行或一列,则可以不使用相应的 row_num 或 column_num 参数。例如,对于单行引用,可以使用 INDEX(reference,,column_num)。Row_num是要从中返回引用的引用中的行编号。Column_num是要从中返回引用的引用中的列编号。当然index函数还有数组形式,在本公式中是采用引用形式,因此只谈它的引用形式。INDEX($A$2:$A$37,RANK(B2,$B$2:$B$37))意思是先求出B2在B2至B37中的位置,再算出相对应的A2至A37的单元格,最终产生出幸运者。

    因为是采用随机函数来产生,所以最终产生的幸运者都是随机的。

    如果要更新幸运者,只要按F5就能进行更新,产生新的幸运者名单。

    Gif动图如下:

    新葡亰496net 55

    回答:还在用excel制作抽奖你就out了,这里给你介绍一个兼具问卷调查、签到信息统计、抽奖于一体的软件——顷刻应用。

    抽奖制作,将签到所得表单或者已有签到信息收集完成的表单关联到抽奖里,简单设置字段以及奖项详情即可。此外,还有一项特色功能,可以自定义预设中奖人的哦,你懂得。

    新葡亰496net 56具体制作方法,可以登录东软平台云官网查看相关使用技巧。

    解决方案2:HLOOKUP MATCH函数。

    由大到小

    match_type=-1

    在“2010年3月员工请假统计表”工作表中选择B3:F8单元格区域,输入下列公式,按【Ctrl Enter】组合键结束

    =RANK(B3,$B$3:$B$12) COUNTIF($B$3:B3,B3)-1

    查找大于或等于lookup_value(目标值)的最小数值在lookup_array(查找区域)中的位置,lookup_array必须按降序排列。

    =IF($A3="","",HLOOKUP(B$2,员工基本信息!$A$2:$H$20,MATCH($A3,员工基本信息!$A$2:$A$20,0),0))

    =SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000>B16-ROW(B16)/10000)) 1

    如果我们要查找李四在源数据第几行,可以怎样表示?

    新葡亰496net 57

    由小到大

    新葡亰496net 58

    新葡亰496net 59

    =RANK(B3,$B$3:$B$12,1) COUNTIF($B$3:B3,B3)-1

    关键步骤:

    案例七

    =SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25 ROW($B$16:$B$25)/10000<B16 ROW(B16)/10000)) 1

    输入=INDEX—框选李四(需查询内容)—框选源数据姓名列(查询的区域)—0-(精确匹配)

    在使用Excel查询和引用数据时,经常需要将文本形式的单元格地址转换成对应应用,。如下图所示为某超市的商品采购清单,其中又两个供货商提供了报价表(如供货商A、供货商B工作表),如何根据品名和供货商自动查询对应的商品单价?

    不等次排名(行大排先)

    5绝对引用

    选择D3:D13单元格区域,输入下列公式,按【Ctrl Enter】组合键结束。

    由大到小

    新葡亰496net 60

    =VLOOKUP(B3,INDIRECT(C3&"!a:b"),2,0)

    =COUNT($B$3:$B$12)-RANK(B3,$B$3:$B$12,1)-COUNTIF($B$3:B3,B3) 2

    (错误做法)

    新葡亰496net 61

    =SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25 ROW($B$16:$B$25)/10000>B16 ROW(B16)/10000)) 1

    很多人以为这样就完了,直接向右复制发现公式不适用,那是因为我们没有采取绝对引用。关于绝对引用、相对引用和混合引用这三者的区别,我们举例来说明:

    由小到大

    1、相对引用,复制公式时地址跟着发生变化,如C1单元格有公式:=A1 B1

    新葡亰496net 62

    =COUNT($B$3:$B$12)-RANK(B3,$B$3:$B$12)-COUNTIF($B$3:B3,B3) 2

    当将公式复制到C2单元格时变为:=A2 B2

    案例八

    =SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000<B16-ROW(B16)/10000)) 1

    当将公式复制到D1单元格时变为:=B1 C1

    用VLOOKUP函数实现反向查找,如下图,如何实现通过工号来查找姓名?

    顺次排名

    2、绝对引用,复制公式时地址不会跟着发生变化,如C1单元格有公式:=$A$1 $B$1

    有三种实现方法:

    由大到小

    当将公式复制到C2单元格时仍为:=$A$1 $B$1

    方法一:在B8单元格输入=VLOOKUP(A8,CHOOSE({1,2},B1:B5,A1:A5),2,0),按ENTER键结束。

    =SUMPRODUCT((B$3:B$12>B3)*(1/COUNTIF(B$3:B$12,B$3:B$12))) 1

    当将公式复制到D1单元格时仍为:=$A$1 $B$1

    方法二:在B8单元格输入=VLOOKUP(A8,IF({1,0},B1:B5,A1:A5),2,0),按ENTER键结束。

    =SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25>B16)/COUNTIF($K$16:$K$25,$K$16:$K$25)) 1

    3、混合引用,复制公式时地址的部分内容跟着发生变化,如C1单元格有公式:=$A1 B$1

    方法三:在B8单元格输入=INDEX(A1:A5,MATCH(A8,B1:B5,)),按ENTER键结束。

    由小到大

    当将公式复制到C2单元格时变为:=$A2 B$1

    新葡亰496net 63

    =SUMPRODUCT((B$3:B$12<B3)*(1/COUNTIF(B$3:B$12,B$3:B$12))) 1

    当将公式复制到D1单元格时变为:=$A1 C$1

    案例九

    =SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25<B16)/COUNTIF($K$16:$K$25,$K$16:$K$25)) 1

    规律:加上了绝对地址符“$”的列标和行号为绝对地址,在公式向旁边复制时不会发生变化;没有加上绝对地址符号的列标和行号为相对地址,在公式向旁边复制时会跟着发生变化;混合引用时部分地址发生变化。注意:工作薄和工作表都是绝对引用,没有相对引用。

    用VLOOKUP函数实现多条件查找,如下图,如何实现通过姓名和工号来查找员工籍贯?

    有并列排名

    新葡亰496net 64

    在C16单元格里面输入=VLOOKUP(A16&B16,IF({1,0},A2:A5&B2:B5,D2:D5),2,0),按SHIFT CTRL ENTER键结束。

    =RANK(B2,$B$2:$B$20)

    具体操作很简单,选中后按一下F4即可切换到绝对引用。(其他几种引用木模式切换自己可以多按几下F4试试)

    新葡亰496net 65

    =SUMPRODUCT(1*($B$3:$B$21>B3)) 1

    6、最后效果

    案例十

    =COUNTIF($B$3:$B$21,">"&B3) 1

    新葡亰496net 66

    用VLOOKUP函数实现批量查找,VLOOKUP函数一般情况下只能查找一个,那么多项应该怎么查找呢?如下图,如何把张一的消费额全部列出?

    {=SUM(IF($B$3:$B$21>B3,1,0)) 1}

    图中可以看到,我们直接搜姓名,相关的员工信息就能显示出来。无论是VLOOKUP,还是INDEX MATACH函数组合,合适自己需求的才是最好的。技多不压身,多学也无妨!

    在C9:C11单元格里面输入公式=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT("b2:b"&ROW($2:$6)),B$9),$C$2:$C$6),2,),按SHIFT CTRL ENTER键结束。

    =19-FREQUENCY($B$3:$B$21,B3) 1

    新葡亰496net 67

    =SUMPRODUCT(($B$2:$B$20>=B2)/COUNTIF($B$2:$B$20,$B$2:$B$20))

    无并列排名

    =RANK(B3,$B$3:$B$21) COUNTIF($B$3:$B3,B3)-1

    =SUMPRODUCT((B3-ROW()/1000<$B$3:$B$21-ROW($B$3:$B$21)/1000)*1) 1

    =19-FREQUENCY($B$3:$B$21-ROW($B$3:$B$21)/1000,B3-ROW()/1000) 1

    {=SUM(IF($B$3:$B$21-ROW($B$3:$B$21)/1000>B3-ROW()/1000,1,0)) 1}

    有并列分段排名

    =SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21>C3)) 1

    =19-FREQUENCY(($A$3:$A$21=A3)*($C$3:$C$21),C3) 1

    {=MATCH(C3,LARGE(OFFSET($C$2,IF($A$3:$A$21=A3,ROW($A$3:$A$21)-2),),ROW(INDIRECT("1:"&COUNTIF($A$3:$A$21,A3)))),0)}

    {=MATCH(C3,LARGE(IF($A$3:$A$21=A3,$C$3:$C$21),ROW(INDIRECT("1:"&COUNTIF($A$3:$A$21,A3)))),0)}

    {=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21>C3)/COUNTIF($N$3:$N$21,$N$3:$N$21)) 1}(需辅助列)

    无并列分段排名

    {=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21-ROW($C$3:$C$21)/10000>C3-ROW(C3)/10000)) 1}

    =19-FREQUENCY(($A$3:$A$21=A3)*($C$3:$C$21-ROW($C$3:$C$21)/1000),C3-ROW()/1000) 1

    成绩排名

    序号

    姓名

    语文

    数学

    英语

    1

    杨增海

    135

    136

    146

    2

    郭爱玲

    138

    137

    141

    3

    华志锋

    134

    138

    141

    4

    袁文飞

    134

    143

    135

    能否用一个公式直接找出所用考生中语文成绩中第100名的成绩是多少?

    =LARGE(C2:C417,100)

    =PERCENTILE(C2:C417,(416-100)/416)

    =PERCENTILE($C$2:$C$417,(COUNTA($C$2:$C$417)-100)/COUNTA($C$2:$C$417))

    能否用一个公式直接找出所用考生中语文成绩中按与考人数的35%切线中位于第35%的成绩是多少?

    升冪

    =SMALL(C2:C417,416*0.35)

    =PERCENTILE($C$2:$C$417,0.35)

        降冪

    =LARGE(C2:C417,416*0.35)

    =PERCENTILE($C$2:$C$417,1-0.35)

    如何排名

    1、对英语进行排名,缺考不计算在内。
    2、对英语进行排名,缺考计算在内。

    英语

    英语排名

    42

    9

    62

    3

    72

    1

    48

    5

    48

    5

    72

    1

    54

    4

    42

    9

    缺考

     

    缺考

     

    45

    8

    46

    7

    缺考不计算在内

    b2=IF(A2="缺考","",RANK(A2,$A$2:$A$13)) 然后按照B列排序

    缺考计算在内

    =IF(A2="缺考",COUNTIF($A$2:$A$13,">=0") 1,RANK(A2,$A$2:$A$13))

    =IF(A2="缺考",COUNT($A$2:$A$13) 1,RANK(A2,$A$2:$A$13,0))

    数据排名(隔几行排名)

    =IF(A2="","",RANK(A2,$A$2:$A$11,0))

    如果隔几行排名,如下表,第五行、第九行和第十二行不参与排名。

    单位

    数据

    排名

    A

    1

    8

    A

    5

    7

    A

    6

    6

    小计

    12

     

    B

    8

    4

    B

    9

    3

    B

    7

    5

    小计

    24

     

    C

    18

    1

    C

    11

    2

    小计

    29

     

    =IF(A2="小计","",RANK(B2,(B$2:B$4,B$6:B$8,B$10:B$11)))    下拉

    根据分数进行倒排名

    =RANK($E3,$E$3:$E$22,1)

    =RANK(K60,$K$60:$K$83,1)

    =COUNTIF($K$60:$K$83,"<"&K60) 1

    倒数排名函数是什么

    1为正排序,0为逆排序。

    倒数排名=RANK(A2,$A$2:$A$5,0)

    正数排名=RANK(A2,$A$2:$A$5,1)

    如何实现每日各车间产量的排名

    =RANK(C2,OFFSET($C$2:$C$5,MATCH(A2,$A$2:$A$1000,0)-1,,,))

    =RANK(C2,OFFSET($C$2:$C$5,MATCH(A2,$A$2:$A$33,0)-1,,,))

    分数相同时按照一科的分数进行排名

    {=MATCH(K308*100 D308,LARGE($K$308:$K$331*100 $D$308:$D$331,ROW($K$308:$K$331)-307),)}

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    筛选后自动产生序列号并汇总

    自动产生序列号:在A1输入以下公式,往下拖。

    =SUBTOTAL(3,$B$2:B2)*1

    自动汇总,用以下公式:

    =SUBTOTAL(9,$B$2:B2)

    说明:汇总时,不要在“全选”状态下进行,先“筛选”出某一单位,自动求和∑。然后再恢复到“全选”或者选择任何单位,就能自动汇总了(在“筛选”出某一单位进行求和时,一般表格会自动产生以上汇总公式)。

    其它:如同时要在其它单元格显示人数,在“全选”状态下,选定单元格,点“fx”(用“sum”函数)再点击序列号最末尾数,即可。

    如何筛选奇数行

    公式=MOD(A1,2)=1

    函数筛选姓名

    如何把两列中只要包含A和A 的人员筛选出来

    =IF(ISNUMBER(FIND("A",C2)) ISNUMBER(FIND("A",B2))>0,"OK","")

    名次筛选

    名次=RANK(K5,K$2:K$435)

    班名次=RANK(K6,OFFSET(K$2,MATCH(A6,A:A,)-2,,COUNTIF(A$1:A$500,A6)))

    如何实现快速定位(筛选出不重复值)

    =IF(COUNTIF($A$2:A2,A2)=1,A2,"")

    =IF((COUNTIF($A$2:A2,A2)=1)=TRUE,A2,"")

    =INDEX(A:A,SMALL(IF(MATCH(A$1:A$20,A$1:A$20,)=ROW($1:$20),ROW(A$1:A$20),65536),ROW()))&""(数组公式)

    如何请在N列中列出A1:L9中每列都存在的数值

    {=IF(ROW()>SUM(--x),"",INDEX(A:A,SMALL(IF(x,ROW($A$1:$A$9)),ROW())))}

    自动为性别编号的问题

    有一个编码,5位,第1位,1为男,2为女,后面4位,代表他的编号,从0001-9999,如何达到下表:

    性别  编码

    男     10001

    男     10002

    女     20001

    男     10003

    女     20002

    男的也是从0001-9999

    女的也是从0001-9999

    如果你是已经输入了其它信息,仅仅为快速输入编码的话。用筛选可以实现吧。
    先以“男”为关键字进行排序,然后在第一个男的编码输入10001,下拉复制到最后一单即可。同理再以“女”排序。完成目标。

    用公式:=IF(A2="",TEXT(COUNTIF(A$2:A2,A2),"10000"),TEXT(COUNTIF(A$2:A2,A2),"20000"))向下拖

     

    本文由新葡亰496net发布于新葡亰496net,转载请注明出处:查找员工信息只需3秒,excel怎么制作抽奖小程序

    关键词: