文字比较长，大家懒得看也可以看视频课，我最近在知乎知学堂上线了一个数据分析课程，从基础入门，手把手带大家学习如何用Excel等数据分析工具提升工具效率，像VBA、甘特图、vlookup等都会涉及，主要讲解模型结构...”字样的所有单元格都搜索出来了（比如，岗位“数据分析师-CR2017-SH2909”中包含“数据分析师”字样，会被搜索出来）；如果勾选“单元格匹配”（表示查找值与单元格内容完全一致），只出现了2个结果，...查找功能中需要勾选“单元格匹配”的应用场景也是很广泛的。 比如这份职位数据表中，公司所在位置有“上海市”和“上海”两种值。我需要规范一下表格，查找“上海”，替换为“上海市”。...问：什么是通配符？ 答：通配符是一种特殊的计算机语言。在Excel操作过程中，如果不知道完整的名称，或者不愿意去输入很多字符去查找时，可以借助关键字加通配符的模式去模糊查找目标值。...代表的字符在单元格开头或者结尾时，则与星号*的使用效果一致。 现在，经过这么系统的介绍，你是不是对查找替换功能更熟悉了呢？
Excel 的几个基本常识 Excel 可以处理的数值有效位数最多为15位 公式中文本类型的常量必须写在半角双引号内 运算符包括算数运算符和比较运算符，其中比较运算符返回逻辑值 表示不等于 所有数据类型中...) Excel中只有两种通配符，分别是 ?...VLOOKUP 最常用函数，具体的用法就是(你找啥,在哪找,要找对应的那一列,精确查找还是模糊查找) 需要注意 第一个参数可以使用通配符进行模糊匹配 查找区域中匹配的内容必须位于第一列 有多个对应值只会返回第一个值...=VLOOKUP($E2,$A$2:$D$100,COLUMN(B:B),0) MATCH 可以在某一个范围内搜索特定的项 MATCH(要查找的内容，搜索的区域，匹配类型) 查找的内容可以是值，数字，...完全等于 MATCH返回的是位置而非值本身，匹配文本时不区分大小写 同样可以配合通配符使用 INDEX 返回所在区域交叉处的位置 INDEX(范围，行序号，列序号) 将 INDEX 和 MATCH 连用可以解决
第1个参数为判断条件，当返回TRUE时，则返回值1，否则返回值2 1.1.1 单条件判断 如下图所示，要根据D列的学生分数判断该学生某学科的分数是否及格。...比如，当有多张表时，如何将一个excel表格的数据匹配到另外一个表中？这时候就需要使用VLOOKUP函数。...第二个参数是需要查询的单元格区域，这个区域中的首列必须是要包含查询值，否则公式将会返回错误值。如果查询区域中包含多个符合条件的查询，VLOOKUP函数只能返回第一个查找到的结果。...如果找不到精确匹配值，则返回小于查询值的最大值。使用近似匹配时，查询区域的首列必须按升序排序，否则无法得到正确的结果。...通俗的来讲，就是返回指定值在数值的位置，如果在数组中没有找到该值则返回#N/A。
-1 - 完全匹配。 如果没有找到，则返回下一个较小的项。 1 - 完全匹配。 如果没有找到，则返回下一个较大的项。 2 - 通配符匹配，其中 *, ? 和 ~ 有特殊含义。...【注意】 1、如果 LOOKUP 函数找不到 lookup_value，则该函数会与 lookup_vector 中 小于或等于 lookup_value 的最大值进行匹配。...2、如果 lookup_value 小于 lookup_vector 中的最小值，则 LOOKUP 会返回 #N/A 错误值。...；vlookup本身不支持反向查找，所以需要借助if函数来构建一个新的查询区域，也就是把要查找的值所在列移到最前面，这里用到了excel中的数组{0,1}。...b xlookup支持一次返回多个匹配值，vlookup和lookup都只能返回一个匹配值。
图1 在单元格B6中输入公式： =A:A 并不会返回整列A，而是返回第6行与列A相交的单元格值f。...图2 然而，如果输入数组公式，就是告诉Excel想要获得多个值。...图3 如果在多个单元格中输入上述数组公式，则会获取多个值，如下图4所示，在单元格区域C5:C9输入上面的数组公式，会得到列A中的前5个数据。 ? 图4 那么，对于函数Excel又是怎么处理的呢？...例如，公式： =VLOOKUP(A4,$A:$C,3,false) 在列A至列C组成的区域中精确查找单元格A4中的内容，并返回列C中相应的值。...图7 如果使用在参数前添加+号的技巧，那么UDF参数必须是与数据类型匹配的Variant、Double、String或Boolean类型，而Range和Object不起作用，因为Excel总是传递结果值而不是引用
如果查询区域中包含多个符合条件的查询值，VLOOKUP函数只能返回第一个查找到的结果。...③ 用于指定返回查询区域中第几列的值 ④ 决定函数的查找方式，如果为0或FASLE，用精确匹配方式，而且支持无序查找；如果为TRUE或被省略，则使用近似匹配方式，同时要求查询区域的首列按升序排序。...Tips： 通配符“*”表示任意多个字符，VLOOKUP函数第一参数使用$H3&"*"，即在C列中查询以H2单元格内容开头的内容，并返回对应列的信息。...Tips： VLOOKUP函数第四参数被省略，在近似匹配模式下返回查询值的精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于查询值的最大值。...VLOOKUP函数使用1至N的递增序列作为查询值，使用A:C列作为查询区域，以精确匹配的方式返回与之相对应的B列的姓名。注意查找区域必须由辅助列A列开始。 最后将辅助列字体设置为白色或进行隐藏即可。
COUNTIF 计算满足一个条件的单元格数。 COUNTIFS 计算满足一组或多组条件的单元格数。 DPRODUCT 将列表或数据库中与指定的条件匹配的记录字段（列）中的值相乘。...DSTDEVP 通过使用列表或数据库中与指定的条件匹配的记录字段（列）中的数字，计算基于整个总体的总体标准差。 DSUM 在列表或数据库中与指定的条件匹配的记录字段（列）中的数字之和。...DVARP 通过使用列表或数据库中与指定的条件匹配的记录字段（列）中的数字，计算基于整个总体的总体方差。 HLOOKUP 在表或值数组的顶行中搜索值，然后在表或数组中指定的行返回同一列中的值。...SUMIF 在由一个条件指定的一个或多个行或列中的单元格之和。 SUMIFS 在由多个条件指定的一个或多个行或列中的单元格之和。...VLOOKUP 在表最左边的列中查找值，然后从指定的列中返回同一行中的值。 注：以上内容整理自exceluser.com，供学习参考。
工作中最常见的是空格，所以每次我拿到从数据库导出来的数据，在作分析之前，基本上第一步都是Ctrl+F，查找内容输入空格，替换成什么那里什么都不输入，这样可以把Excel表中的空格批量替换。...另一种更麻烦的错误就是乱码，换言之是Excel不可识别的乱码，此时我都会全选数据粘贴到UE中，然后把乱码替换掉再粘贴回Excel表。...之前的每一个文字描述区间都转化为一行数据，辅助表有两个条件：1、每个数字区间的下限（最小值）作为第一列的判断条件，对应的返回值作为第二列2、第一列的数字必须从小到大排序（否则会出现什么错误可以自己试一下...在Excel中，True等同于1，False等同于0。 用0除以True和False的数组，则0除以1的时候还是返回0，但是正常分母是不可以为0的，所以其他值返回的都是错误。...之前分享过，lookup在这里就是模糊匹配用法，所以电脑只能认为第三行数据是电脑找到的，则返回了评分数组中的第三位，也就是5。 以上就是Lookup函数和Find函数的基本用法！
在Excel中，标准的查找函数例如INDEX、MATCH、VLOOKUP等都非常好，但当存在重复值时就比较困难了。如下图1所示，提取满足3个条件的数据记录，可以看出有2条记录满足条件。...这些公式是独立的，不需要额外的列辅助。对于这些公式，必须在公式内为与条件相匹配的记录创建一个相对位置数组。 ? 图1：需要提取两条记录，标准的查找函数对于重复值有些困难。...注意，SUM函数将逻辑值转换成1或0，并且忽略文本值。 ? 图3：最终的辅助列公式使用SUM函数将AND函数的逻辑值与上方单元格中的值相加 单元格H6是一个辅助单元格。...在该单元格中，输入公式： =MAX(E8:E17) 得到匹配的记录的条数。 使用辅助单元格，可以帮助减小公式的计算时间。...图7：AND和OR条件，双向查找从日期和商品数列中获取数据 未完待续>>> 注：本文为电子书《精通Excel数组公式（学习笔记版）》中的一部分内容节选。
使用index_col参数可以操作数据框中的索引列，如果将值0设置为none，它将使用第一列作为index。 ?...8、筛选不在列表或Excel中的值 ? 9、用多个条件筛选多列数据 输入应为列一个表，此方法相当于excel中的高级过滤器功能： ? 10、根据数字条件过滤 ?...如果想要用特定值查看整个DataFrame，可以使用drop_duplicates函数： ? 15、排序 对特定列排序，默认升序： ?...9、多条件求和 ? 10、求算术平均值 ? 11、求最大值 ? 12、求最小值 ? 13、Groupby：即Excel中的小计函数 ?...由于Pandas中没有“Vlookup”函数，因此Merge用与SQL相同的备用函数。
，此技巧对VLOOKUP和LOOKUP而言算不上什么伤害，因为常规大家写完公式都会下拉公式填充，都一样可以查找出多个字段值。...案例2:查找姓名中包含“二”的年龄 第二回合是通配符查找的案例，VLOOKUP函数在面对通配符*和?查找出来的结果是不一样的，因为*是代表对个内容的通配符，而?...XLOOKUP函数也可以兼容通配符查找，不过XLOOKUP函数查找的通配符遇到多个结果时返回的是第一个结果值。...B4:B11),2,0) LOOKUP函数面对不管从左往右还是从右往左查找，匹配条件列是动态可调整的，这点LOOKUP较比VLOOKUP灵活度高。...最后给大家分享一下低版本Office的同学如果不想升级软件，可以使用VBA自定义一个XLOOKUP函数哦，方法如下： VBA自定义XLOOKUP函数
本题我们用星号通配符比较合适，选中抵押权人这一列里的内容，Ctrl+F 快捷键打开【查找和替换】对话框。...其语法是： image.png 在本例中，公式的第一个参数为加盟商=“蚂蚁**”，判断是否满足这一条件，如果满足就是TRUE，则第二参数为“客户金额乘以10000”，如果不满足为FALSE则第三参数为原来的客户金额值...【题目8】根据表一中的信息，将表二中的产品名称补充完整 image.png 按照某一条件查找匹配其他内容，通常用到 VLOOKUP公式，其语法如下： image.png 第一参数你想要查找的内容...用选择性粘贴合并表格，使用通配符进行模糊查找替换，进行多个关键字排序，利用ctrl+enter批量填充。 用条件格式，数据验证可以快速显示异常值及避免输入错误信息。...MATCH 查找出指定内容在第几行 VLOOKUP 根据指定值匹配内容 image.png
fx 图片 Count系列统计函数 图片 Countif 函数 图片 Countif 函数搭配通配符完成统计，excel的通配符有三个 图片 sum，average函数 对文本不会进行计算,average...可以 对函数单元格设置左对齐 sumif函数 实现条件求和 图片 sumproduct函数，例如有多个产品，单价和数量，相乘之后再求总和，可以用该函数。 图片 19-22节 查找和替换。...如何查找单元格完全是该内容而不是包含该内容。如下 图片 查找和替换不仅查找文本还可以查找格式。...例如求和黄色数据， shift 全选查到的列表，命名为yellow，最后summer（yellow） 图片 查找和替换可以搭配通配符，但是如果就要查找通配符 需要加~改变通配符。...除了一般符号，也可以用字符作为分隔符 筛选、排序，可以自定义序列 图片 Vlookup函数 模糊查找通过下图1实现，逻辑值：大致匹配用True或省略，精确匹配用False，但是 右侧表格要按照升序排序
range_lookup （可选）一个逻辑值，该值指定希望 VLOOKUP查找近似匹配还是精确匹配：近似匹配 - 1/TRUE假定表中的第一列按数字或字母顺序排序，然后搜索最接近的值。...这是未指定值时的默认方法。例如，=VLOOKUP (90，A1：B100，2，TRUE)。完全匹配 - 0/FALSE 搜索第一列中的确切值。...如果数组仅包含一行或一列，则相应的row_num 或column_num 参数是可选的。...选择数组中的某行，函数从该行返回数值。如果row_num ，column_num 参数。 column_num 可选。选择数组中的某列，函数从该列返回数值。...在Excel中0=FALSE，1=TRUE，我们把{1,0}放在IF函数的第一参数中，它实际上代表对和错的条件结果，又因为，{1,0}在大括号中，所以它是一个数组，它会跟每一个元素都发生运算，比如在IF
vlookup简介 四种查询方式 1.单条件查找 根据工号，将左边检索区域的“电脑销售额”匹配到右边对应位置，只需要使用VLOOKUP函数，结果存在则显示对应的“电脑销售额”；结果不存在则显示#N/A。...反向查找 反向查找的固定公式用法： =VLOOKUP(检索关键字，IF({1,0},检索关键字所在列，查找值所在列)，2，0) 注意：其实反向查找除了检索区域改成一个虚拟数组公式IF之外，其他和单条件查找没有区别...HLOOKUP =HLOOKUP(用谁去找, 匹配对象范围, 返回第几行, 匹配方式) 和VLOOKUP的区别：HLOOKUP返回的值与查找的值在同一列上，而VLOOKUP返回的值与查找的值在同一行上。...A1向下偏移一行，向右偏移一列 计算返回的两行两列的和，如果不求和，则会报错，因为一个cell不能填充四个cell的内容。 ?...插入超链接 逻辑运算函数 一般用于条件运算，在Excel中，True代表数值1，False代表0。 IF 如果满足判断条件，则返回“真值”，否则返回“假值”。
关联匹配类 经常性的，需要的数据不在同一个excel表或同一个excel表不同sheet中，数据太多，copy麻烦也不准确，如何整合呢？...VLOOKUP 功能：用于查找首列满足条件的元素 语法：=VLOOKUP（要查找的值，要在其中查找值的区域，区域中包含返回值的列号，精确匹配(0)或近似匹配(1) ） (1) 单表查找 ?...A:C,3,0)) 意思是，如果在销售表中查找不到(用IFERROR函数判断)，则去财务表中再查找。...(1) ） 区别：HLOOKUP按行查找，返回的值与需要查找的值在同一列上，VLOOKUP按列查找，返回的值与需要查找的值在同一行上。...INDEX 在Excel中，除了VLOOKUP函数常用来查找引用外，INDEX函数和MATCH函数组合也可用来做查找引用工作，这组函数有效弥补了VLOOKUP函数查找目标不在查找范围数据首列的缺陷。
文本提取替换函数清单 StringJoin函数 对标原生Excel2016中提供的TEXTJOIN函数 特别加上最后一个参数，可以包围每个拼接的字符串项，在写SQL语句时的IN条件时，经常会用到单引号包围着字符串项的效果...StringJoin函数参数 StringJoinIf函数 一般在各种文章、书籍中都有介绍类似VLOOKUP的查找引用功能，但需要一对多的查找方式，返回多个值。...提取替换函数示例 这里特别增加两个指定提取和指定替换功能，方便部分需要指定特定字符的功能时使用。 其中较为特别的是MatchString这个匹配文本。...中间用逗号可指定多个文本，逗号间又区分了只有一个字和多个字的情况。 只有一个字时，如下图中的 ”中“，”f“。...，如下图中的“美国” 仅匹配此多个字符的完整匹配，如ABC,BC的MatchString，仅对源文本中的ABC和BC两项匹配。
VLOOKUP、数据透视表、条件格式…你用这几个技巧做，80%的工作需求都能解决。今天特意整理了这些操作技巧，拯救同在“表海”中挣扎的你，让你的工作效率超乎想象。 ?...第一篇章 数据整理与保护 1.CTRL+E，截取填充部分文本 如何截取身份证号中的出生年月，一个快捷操作，截取、填充同时搞定。...因此要用VLOOKUP函数查找引用数据，这也是Excel中使用最频繁的操作。 VLOOKUP函数结构 ? VLOOKUP精确匹配 ?...Index+Match中，Match用以确定数据所在的行值和列值（查找姓名所在的行，查找身份证号所在的列，行列交汇的数据就是要匹配出来的数据），Index负责调出由Match确定的行值和列值交叉位置确定的唯一数据...操作方式：选中字段下的数据—右键单击选择【创建组】-创建“起始于”和“终止于”对应的数值，也可采用自带的年、季度、月等组合方式。 ? 切片器：一枚切片器，轻松控制多个数据透视表，数据展现随心而动。
测试方式 十万行数据的Excel文件，对每行分别使用Xlookup、Vlookup和Index/Match组合函数； 用VBA记录运算时间； 运行设备：MacBook Air刷Windows系统，算是比较低端的配置...Xlookup 的二级制搜索模式 Xlookup比Vlookup多了2个可选参数，其中一个是搜索模式。其中，如果搜索目标列是升序或降序排序，则可以选用二进制搜索模式。...在这种情况下，该函数不会马上进行全列搜索，而是先将列按值的大小，一分为二，如果查找值在目标列的上一半，则进一步将上一半的数据一分为二继续查找，依此类推。...但优势已经不如Vlookup对比Xlookup常规模式那么明显。 二维匹配 即行和列的值都要匹配，这是VLookup无法实现的。Index/Match需要再加一个Match。...未找到值 Xlookup比Vlookup多了另一个参数是：如未找到有效的匹配值，返回指定值。我们都知道，如果Vlookup查找不到对应值，会返回错误。这时需要外层套一个Iferror。
上期和大家分享了如何使用数组函数实现或关系求均值。 本期和大家分享进一步的应用，或关系模糊匹配求均值。 如果没看上期直接看本期会比较痛苦，来个传送门！ 点击我可以飞！！！...B列中是我随便构建的一列，我需要找到文字包含石原里美、裴秀智、李智恩销售金额均值 相对于上期有了新的挑战！...完全相等的函数简化如下：{=AVERAGE(IF(B2:B12="石原里美",C2:C12,""))} 那我们之前学过Vlookup的模糊匹配，增加一个通配符试一下？...函数中的某个参数输入的是文本的时候，可以使用通配符，但是当函数中用等号判断两个文本的时候，Excel会把星号当做普通文本来对待。换言之返回值是True或者False时，通配符无效。...为了方便理解我们在Average里面加个iferror吧，如果没有错误返回本身，如果有错误返回空的文本 最后就是在Search函数将一个人替换为多个人就好啦！ 就这样，继续放链接！
领取专属 10元无门槛券
手把手带您无忧上云
云服务器
即时通信 IM
ICP备案
对象存储
实时音视频
