excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表

  • 需求: 从数据源A 中查到对应信息填入B表

类似的案例有:

  • 成绩录入

excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表

如图,我们需要根据下方三张成绩单的数据,来填写汇总表;
excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表

excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
选中b表数据,点击 excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表

点击 excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表,然后点击下一步
excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表确认后得到
excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
其他科目分数也做同样处理
excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表

  • 根据手机号查询归属地

1、首先准备数据源表,即手机号段对应区号信息表,如下
excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
2、准备需要填充信息的数据源表

  • 这里我们使用以下公式来生成随机的手机号
    (注意:使用公式时,单元格格式需要设定为常规模式,不能是文本模式。否则可能不生效)
    ① RAND() 随机生成 0 ~ 10 的数字,所得0 <= num < 10
    ② INT() 取整数
    ③ & 拼接
    由此我们得到的公式为: 1&INT(RAND() * 900000000+1000000000)
    其中&右边得到的 0 + 1,000,000,000 <= num < 10*900,000,000+ 1,000,000,000
    即 1,000,000,000 <= num < 10,000,000,000 保证了1之后是10位自然数
  • 根据公式得到一组手机号数据
    excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
  • 这里顺带讲下多行自动填充
    excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
    像这样就能多行选中了,即使几十万行也没关系,一样选中第一个单元格后,滑动鼠标滚轮(鼠标左右键中间那个键)或者直接拖动滚动条拉至你想要填充的最后一个单元格;然后按下shift键不松开,再点击目标单元格,即如下图
    excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
  • 然后选择 数据 --> 填充 —> 向下填充 excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
  • 结果如下:
    excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
    3、 接着填写下图表格数据
    excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
  • 公式:VLOOKUP(VALUE(LEFT(C2,7)),手机号归属地!A:B,2,0)
  • 解析:查找函数(取手机号前7位,选中数据源,数据源列数,匹配条件 (0是模糊匹配,1是精确匹配))
  • 分解:
    excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
    excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
    excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
    excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
    接着选中数据表
    excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
    切换到手机归属地的工作表,选中所需俩列数据,即数据表
    excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
    excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表

excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表

注意符号要使用英文字符
excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
完成,回车
excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表
接着使用上述说到的,多行自动填充,就完成了,效果图如下
excel-批量在指定列中以关键词查找对应信息-示例:从数据源A 中查到对应信息填入B表

  • 根据身份证号码查询归属地

  • 同手机号查询归属地,这里不做详细讲解,补充说明下身份证号可按前6位识别地区
    
上一篇:000博客园在线编辑器tinymce简明教程


下一篇:tableau考试指南大纲(官网)