查看: 917|回复: 12

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

  [复制链接]
  • TA的每日心情
    忙~
    2019.12.2 17:10
  • 签到天数: 1 天

    连续签到: 1 天

    [LV.1]初来乍到

    管理员

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

    主题
    50
    奥币
    130
    积分
    499
    注册时间
    2019.7.8
    在线时间
    102 小时

    发表于 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的每日心情
    no
    3 天前
  • 签到天数: 53 天

    连续签到: 2 天

    [LV.5]常住居民I

    钵水母

    Rank: 3Rank: 3

    主题
    0
    奥币
    498
    积分
    80
    注册时间
    2016.8.22
    在线时间
    22 小时

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

    使用道具 举报

  • TA的每日心情
    吃饭
    15 小时前
  • 签到天数: 44 天

    连续签到: 3 天

    [LV.5]常住居民I

    中华鲟

    Rank: 5Rank: 5

    主题
    2
    奥币
    561
    积分
    686
    注册时间
    2019.10.25
    在线时间
    25 小时

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

    使用道具 举报

  • TA的每日心情
    yes!
    15 小时前
  • 签到天数: 37 天

    连续签到: 3 天

    [LV.5]常住居民I

    帝王蝶

    Rank: 4

    主题
    0
    奥币
    582
    积分
    450
    注册时间
    2019.3.19
    在线时间
    7 小时

    活跃会员最佳新人


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

    使用道具 举报

  • TA的每日心情
    忙~
    8 小时前
  • 签到天数: 210 天

    连续签到: 22 天

    [LV.7]常住居民III

    帝王蝶

    Rank: 4

    主题
    0
    奥币
    559
    积分
    237
    注册时间
    2016.12.6
    在线时间
    90 小时

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

    使用道具 举报

  • TA的每日心情
    忙~
    8 小时前
  • 签到天数: 210 天

    连续签到: 22 天

    [LV.7]常住居民III

    帝王蝶

    Rank: 4

    主题
    0
    奥币
    559
    积分
    237
    注册时间
    2016.12.6
    在线时间
    90 小时

    发表于 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的每日心情
    吃饭
    15 小时前
  • 签到天数: 44 天

    连续签到: 3 天

    [LV.5]常住居民I

    中华鲟

    Rank: 5Rank: 5

    主题
    2
    奥币
    561
    积分
    686
    注册时间
    2019.10.25
    在线时间
    25 小时

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

    使用道具 举报

  • TA的每日心情
    吃饭
    15 小时前
  • 签到天数: 44 天

    连续签到: 3 天

    [LV.5]常住居民I

    中华鲟

    Rank: 5Rank: 5

    主题
    2
    奥币
    561
    积分
    686
    注册时间
    2019.10.25
    在线时间
    25 小时

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

    使用道具 举报

  • TA的每日心情
    好棒
    15 小时前
  • 签到天数: 14 天

    连续签到: 10 天

    [LV.3]偶尔看看II

    钵水母

    Rank: 3Rank: 3

    主题
    0
    奥币
    128
    积分
    58
    注册时间
    2019.11.28
    在线时间
    5 小时

    最佳新人


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

    使用道具 举报

  • TA的每日心情
    吃饭
    2019.11.2 21:41
  • 签到天数: 2 天

    连续签到: 1 天

    [LV.1]初来乍到

    钵水母

    Rank: 3Rank: 3

    主题
    2
    奥币
    212
    积分
    43
    注册时间
    2017.12.20
    在线时间
    14 小时

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

    使用道具 举报

  • TA的每日心情

    5 小时前
  • 签到天数: 52 天

    连续签到: 52 天

    [LV.5]常住居民I

    帝王蝶

    Rank: 4

    主题
    1
    奥币
    344
    积分
    388
    注册时间
    2019.10.24
    在线时间
    29 小时

    发表于 5 天前 | 显示全部楼层
    回复

    使用道具 举报

  • TA的每日心情
    吃饭
    13 小时前
  • 签到天数: 701 天

    连续签到: 4 天

    [LV.9]以坛为家II

    帝王蝶

    Rank: 4

    主题
    0
    奥币
    2799
    积分
    345
    注册时间
    2016.1.13
    在线时间
    150 小时

    发表于 昨天 08:18 | 显示全部楼层
    感谢分享
    回复

    使用道具 举报

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

    本版积分规则

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