查看: 5777|回复: 16

[软件使用] 如何用Excel批量查找数据?

  [复制链接]
  • TA的每日心情

    2020.6.3 17:02
  • 签到天数: 28 天

    连续签到: 1 天

    [LV.4]偶尔看看III

    管理员

    Rank: 15Rank: 15Rank: 15Rank: 15Rank: 15

    主题
    153
    奥币
    1465
    积分
    1328
    注册时间
    2019.7.8
    在线时间
    243 小时

    发表于 2019.11.18 10:30:53 | 显示全部楼层 |阅读模式
    经常在基迪奥生信交流群见到下图这样的问答,似乎只要问到用Excel查找和筛选数据,都会有这样的回答:试试vlookup。

    图1 基迪奥生信交流群的问答

    那么,vlookup到底是何方神圣?它到底有没有像各位大佬说的那么神奇,我一直很好奇。至于原因,说来惭愧,我在准备写这篇推文之前其实一直没用过这个函数。 那么,今天就和大家一起学习下这个函数吧。

    vlookup函数简介

    首先,我们先了解一下vlookup函数的用法,通过Excel的帮助菜单可以查到vlookup函数4个参数的详细说明。


    英文参数:
    =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

    翻译得像人话一些:
    =VLOOKUP(你想要查找的关键词,查找区域,返回值在查找区域中对应列的序号,近似或精确匹配)

    帮助文档可能是由英文直接翻译过来的,理解起来感觉很“抽象”,上面的公式是按我的理解重新整理的,4个参数的说明如下:

    1.查找的“关键词”

    2.查找区域。查找的关键词应该始终位于查找区域的第一列,这样 VLOOKUP 才能正常工作,例如,查找目标位于单元格 B2 内,那么查找区域应该以B 开头。

    3.查找区域中“返回值”对应的列号。例如,B2: F11 作为区域, 那么要返回B列的数据,则输入列号为1,如果返回 C列的数据则输入2,依此类推。

    4.匹配精度(可选)。如果需要近似匹配,可以输入 TRUE(或者输入1);如果需要精确匹配,则输入FALSE(或者输入0)。如果没有输入,默认值为 TRUE,即近似匹配。

    通过上面的参数我们可以看出,vlookup的主要用法是回答图1中的第一个问题,通过表格中某行中的一个字段,找出这一行的其他字段。

    范例演示

    这里使用的范例数据为一个只有56行的一个基因表达量表,如下图,我们的目的是从中查找12感兴趣个基因(图3)的表达量、描述信息。


    图2 表达量总表

    图3 感兴趣的基因列表

    我这里的第1个单元格输入的函数如下,$ 符号表示绝对引用。在单元格输入计算公式后,只需“双击”单元格右下角的黑色“+”,即可快速完成下面的计算,其他列的数据只需拷贝公式,修改第3个参数即可。我这里把最后一个gene id改成总表中不存在的gene。

    =VLOOKUP($A2,Sheet1!$A$2:$L$56,2,FALSE)

    图4 公式输入

    最终,查找的结果如下,不存在的基因对应的数据Excel用“#N/A”表示。

    图5 查找结果

    另外,同样的思路,也可以使用vlookup函数合并表格。我把上文的范例数据拆分成两个行数不等表格,我接下来要做的是把描述信息表格(图6)追加到感兴趣基因的表达量表(图7)中。


    图6 描述信息表格

    图7 感兴趣基因的表达量表

    于是我在sheet4表的E2单元格中输入下面的公式,即可将描述信息表格中第2列中对应的基因描述信息追加进来。“双击”单元格右下角的黑色“+”,即可快速完成其他行的填充。

    =VLOOKUP(A2,Sheet3!$A$1:$B$56,2,FALSE)

    合并后的表格如下:

    图8 合并结果

    最后,说到表格筛选和合并,还是推荐大家使用更加强大的OmicShare tools,两表格取并集、取交集或联集都可以。除了上述的基本用法,你还可以结合其他函数(比如IF函数)设计一些更个性化的函数,当然也可学一下在行方向上查找的hlookup函数。


    今天的内容就到这里啦~

    本文作者:基迪奥-莫北

    本帖子中包含更多资源

    您需要 登录 才可以下载或查看,没有帐号?立即注册

    x
    新的一天加油!
    回复

    使用道具 举报

  • TA的每日心情

    2020.6.19 21:24
  • 签到天数: 76 天

    连续签到: 1 天

    [LV.6]常住居民II

    钵水母

    Rank: 3Rank: 3

    主题
    0
    奥币
    385
    积分
    85
    注册时间
    2016.8.22
    在线时间
    27 小时

    发表于 2019.11.19 08:29:22 | 显示全部楼层
    谢谢楼主分享!
    啊啊啊
    回复 支持 反对

    使用道具 举报

  • TA的每日心情
    吃饭
    2020.4.27 21:55
  • 签到天数: 64 天

    连续签到: 1 天

    [LV.6]常住居民II

    中华鲟

    Rank: 5Rank: 5

    主题
    2
    奥币
    828
    积分
    871
    注册时间
    2019.10.25
    在线时间
    30 小时

    发表于 2019.11.19 08:33:09 | 显示全部楼层
    我不客气的收藏了~~
    回复 支持 反对

    使用道具 举报

  • TA的每日心情
    吃饭
    2020.3.17 16:06
  • 签到天数: 54 天

    连续签到: 1 天

    [LV.5]常住居民I

    中华鲟

    Rank: 5Rank: 5

    主题
    0
    奥币
    797
    积分
    598
    注册时间
    2019.3.19
    在线时间
    11 小时

    发表于 2019.11.19 08:38:48 | 显示全部楼层
    感谢楼主~~~。
    回复

    使用道具 举报

  • TA的每日心情
    吃饭
    昨天 08:53
  • 签到天数: 334 天

    连续签到: 16 天

    [LV.8]以坛为家I

    帝王蝶

    Rank: 4

    主题
    0
    奥币
    856
    积分
    312
    注册时间
    2016.12.6
    在线时间
    125 小时

    发表于 2019.11.19 10:12:20 | 显示全部楼层
    感谢分享,,
    回复

    使用道具 举报

  • TA的每日心情
    吃饭
    昨天 08:53
  • 签到天数: 334 天

    连续签到: 16 天

    [LV.8]以坛为家I

    帝王蝶

    Rank: 4

    主题
    0
    奥币
    856
    积分
    312
    注册时间
    2016.12.6
    在线时间
    125 小时

    发表于 2019.11.19 10:13:22 | 显示全部楼层
    感谢分享,,,,
    回复

    使用道具 举报

  • TA的每日心情
    yes!
    2019.11.19 10:21
  • 签到天数: 1 天

    连续签到: 1 天

    [LV.1]初来乍到

    草履虫

    Rank: 2

    主题
    0
    奥币
    39
    积分
    2
    注册时间
    2019.11.19
    在线时间
    0 小时

    发表于 2019.11.19 10:14:35 | 显示全部楼层
    厉害呀··········
    加油呀呀呀
    回复 支持 反对

    使用道具 举报

  • TA的每日心情
    吃饭
    2020.4.27 21:55
  • 签到天数: 64 天

    连续签到: 1 天

    [LV.6]常住居民II

    中华鲟

    Rank: 5Rank: 5

    主题
    2
    奥币
    828
    积分
    871
    注册时间
    2019.10.25
    在线时间
    30 小时

    发表于 2019.11.20 08:03:35 | 显示全部楼层
    太实用了
    回复

    使用道具 举报

  • TA的每日心情
    吃饭
    2020.4.27 21:55
  • 签到天数: 64 天

    连续签到: 1 天

    [LV.6]常住居民II

    中华鲟

    Rank: 5Rank: 5

    主题
    2
    奥币
    828
    积分
    871
    注册时间
    2019.10.25
    在线时间
    30 小时

    发表于 2019.11.27 08:35:33 | 显示全部楼层
    感谢楼主,亲测,有效。是有时候会出现NA错误。但明明有那个数据
    回复 支持 反对

    使用道具 举报

  • TA的每日心情
    忙~
    2020.6.20 08:49
  • 签到天数: 160 天

    连续签到: 1 天

    [LV.7]常住居民III

    钵水母

    Rank: 3Rank: 3

    主题
    0
    奥币
    891
    积分
    88
    注册时间
    2019.11.28
    在线时间
    35 小时

    发表于 2019.11.28 19:52:48 | 显示全部楼层
    长知识了,又学到了不得了的方法
    新的一天加油!
    回复 支持 反对

    使用道具 举报

  • TA的每日心情
    yes!
    2020.5.29 19:39
  • 签到天数: 49 天

    连续签到: 1 天

    [LV.5]常住居民I

    中华鲟

    Rank: 5Rank: 5

    主题
    58
    奥币
    429
    积分
    741
    注册时间
    2017.12.20
    在线时间
    45 小时

    发表于 2019.12.1 09:52:32 | 显示全部楼层
    vlookup函数在我分析数据的时候可是帮了我不少的忙,节省了很多时间,快快上手吧!谢谢大神分享啊!
    回复 支持 反对

    使用道具 举报

  • TA的每日心情

    昨天 08:44
  • 签到天数: 240 天

    连续签到: 48 天

    [LV.8]以坛为家I

    帝王蝶

    Rank: 4

    主题
    1
    奥币
    1004
    积分
    474
    注册时间
    2019.10.24
    在线时间
    67 小时

    发表于 2019.12.9 11:54:20 | 显示全部楼层
    周一啦
    回复

    使用道具 举报

  • TA的每日心情
    吃饭
    昨天 08:08
  • 签到天数: 852 天

    连续签到: 9 天

    [LV.10]以坛为家III

    帝王蝶

    Rank: 4

    主题
    0
    奥币
    3217
    积分
    387
    注册时间
    2016.1.13
    在线时间
    178 小时

    发表于 2019.12.13 08:18:52 | 显示全部楼层
    感谢分享
    早上好。。
    回复

    使用道具 举报

  • TA的每日心情
    yes!
    2020.1.10 11:08
  • 签到天数: 2 天

    连续签到: 1 天

    [LV.1]初来乍到

    帝王蝶

    Rank: 4

    主题
    0
    奥币
    202
    积分
    323
    注册时间
    2019.11.21
    在线时间
    11 小时

    发表于 2019.12.15 18:34:11 | 显示全部楼层
    学习
    回复

    使用道具 举报

    该用户从未签到

    钵水母

    Rank: 3Rank: 3

    主题
    2
    奥币
    3
    积分
    91
    注册时间
    2019.11.27
    在线时间
    5 小时

    发表于 2019.12.16 11:01:35 | 显示全部楼层
    回复

    使用道具 举报

  • TA的每日心情

    2017.7.7 17:07
  • 签到天数: 2 天

    连续签到: 1 天

    [LV.1]初来乍到

    钵水母

    Rank: 3Rank: 3

    主题
    0
    奥币
    278
    积分
    12
    注册时间
    2016.10.12
    在线时间
    2 小时

    发表于 2019.12.18 11:19:30 | 显示全部楼层
    感谢分享
    回复

    使用道具 举报

  • TA的每日心情
    吃饭
    昨天 08:53
  • 签到天数: 334 天

    连续签到: 16 天

    [LV.8]以坛为家I

    帝王蝶

    Rank: 4

    主题
    0
    奥币
    856
    积分
    312
    注册时间
    2016.12.6
    在线时间
    125 小时

    发表于 2020.6.4 11:21:37 | 显示全部楼层
    神技能,,,
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    快速回复 返回顶部 返回列表