精华内容
下载资源
问答
  • 当遇到需要提取地址信息时,首先想到的应该是分列吧,虽然省/市每个地区都有这个字符,但【区/县】或者【街/道】确不同,分列方法不可取,因此函数的重要性就体现出来了。我们看看例题吧!同样都有省市,但区...

    当遇到需要提取地址信息时,首先想到的应该是分列吧,虽然省/市在每个地区都有这个字符,但【区/县】或者【街/道】确不同,分列方法不可取,因此函数的重要性就体现出来了。

    我们看看例题吧!

    312448deb723d0f79ebd2c3f668dcbc0.png

    同样都有省和市,但区和县不同,可能最初想到的函数就是FIND吧。

    但当【区/县】不同时,难度便上升了几个层次,如果需要一条公式完成,就得动态获取三个关键位置,FIND第一参数可以为数组,如果手动输入数组,肯定是无法使用一条公式完成,因此需要拆分C2:E2区域的标题字符。

    思路解析

    思路不难,使用SUBSTITUTE函数将“ / ”替换成空白,然后使用MID函数拆分字符,得到一组字符,IFERROR函数去错误值,MAX函数取最大值即可,整体效果如下:

    bc8818c3b8769bcec00c9165e9042ad5.png

    为什么会有4列呢,不应该是三列才对吗?下面再来说说提取的思路。

    提取省份:从第一个开始取省份的字符个数

    提取市区:从省份后一位开始取市区的字符个数

    提取区/县:从市区的后一位开始取区/县的的字符个数

    因此我们如果提取省份,则从第一位开始取当前省份位置的占位符个数,我们拿A3单元格的地址做例子。

    ec615009d4ae1f873d486e2e0544e516.png

    这样就能实现动态提取了,但有一个问题,提取出来的都包含关键字“省/市/区/县”,我们并不需要它,如果在用MID的第二参数+1,第三参数再减一位,那么就会错位了,比如提取省份时,第一位+1就是第二位,直接就错了,如果不加1,提取市区时则从第三位开始,包含了“省”这个字,也是错的。

    虽然可以最后嵌套SUBSTITUTE函数进行替换,但积木堆多了也是挺麻烦的,既然顺向思维比较麻烦,那我们可以试试逆向思维,拿地址区找我们的关键字。

    e7c9bef83233c42e25004d4aa302eb27.png

    公式:

    =IFERROR(MATCH(1,MATCH("*"&MID($A3,ROW($A$1:$A$30),1)&"*",B$2,0),0),0)

    我们先看看位置数字的结果,然后使用MID测试:

    783819408c796f0abd13f27892c7174a.png

    这样就能实现动态提取了,我们将MID提取直接在主位置测试。

    52c4b3ff58c5449ba5c3ab0ece94bb39.png

    这样就完全能达到我们的目的,最后只用合并公式即可。

    核心思路原理

    那么4列公式的原理是什么呢?我们一步步解剖。

    26ef80b2e00069b4404bb6f0ded710be.png

    上图中的思路就是利用了反向查找位置法,也就是将原地址文本每个字符都拆开,使用MATCH函数一个个去查找第二参数内容,即B7单元格(省),匹配上了,则返回第二参数所在位置,其实MATCH第二参数只有一个字符,所以匹配上时返回的是数字1

    查找到的省,就在第三位,我们返回到原始数据看结果。

    d7fdbb13d3588edc43e773f1a4c0a74a.png

    关键字是空白时,则返回0,也就是第一列;

    关键字是省时,则返回省所在位置,也就是第二;

    以此类推……

    这一段公式是核心思路,当位置全部出来后,只用按照文章开头所说的MID函数各就各位的取内容即可

    2f813fd8e75577199e69cfae3438a7b2.png

    最后合并公式

    b859636ace2628da7e8f1ef67f79a474.png

    公式:

    =MID($A3,IFERROR(MATCH(1,MATCH("*"&MID($A3,ROW($A$1:$A$30),1)&"*",B$2,0),0),0)+1,IFERROR(MATCH(1,MATCH("*"&MID($A3,ROW($A$1:$A$30),1)&"*",C$2,0),0),0)-IFERROR(MATCH(1,MATCH("*"&MID($A3,ROW($A$1:$A$30),1)&"*",B$2,0),0),0)-1)

    结语:

    ◆文中看似一长串天花乱坠的公式看着眼花缭乱,其实就是这么嵌套进去的。再者,公式的重点在于当基础扎实后,考验的是思维方式,或者说是逻辑关系,看到题时首先需要联想的是,使用什么函数组合才能更合适去解这道题,我看到很多基础不牢固的朋友直接参与做中高级题,在参与过程中因为不懂某些基础函数的用法,则会限制了想象,最合适函数无法利用,则只能搜寻自己了解的函数去不断的拼凑,最终就像传说中的64层IF一样,看着很牛,实际上拼凑之后自己都看不懂,一旦需要更改,直接傻眼了,所以在同是数组公式的情况下,公式字符个数越短,越能体现一个人水平,换而言之,当需要不断进阶函数的朋友来说,公式的长短就是自己学习的最终目标,希望本篇对大家有所帮助!

    ◆学而不用则殆,希望大家学习后利用到自己的工作中,哪怕是点滴的应用,也是一种进步。给自己的工作带来非常大的方便。

    Excel微信群:13628686713

    展开全文
  • 1上一期跟大家分享过数据透视表的方法来提取考勤表每天第一次最后一次的打卡时间,这一期我教大家用函数公式的方法来实现!下图,A:B两列数据就是自己从后台导出的打卡记录,我们需要将这2列数据提取成D:F...

    1

    上一期跟大家分享过用数据透视表的方法来提取考勤表中每天第一次和最后一次的打卡时间,这一期我教大家用函数公式的方法来实现!

    下图中,A:B两列数据就是自己从后台导出的打卡记录,我们需要将这2列数据提取成D:F中所示的单元格区域。

    136f3f85deaafba3e78ddf6000fdd4e0.png

    具体操作步骤如下:

    一、提取打卡日期。

    D2单元格中输入公式:=LOOKUP(,0/FREQUENCY(0,ISNA(MATCH(A$2:A$81,D$1:D1,))-1),A$2:A3),按回车键回车并将公式下拉填充至D22单元格即可。

    3904ef411bd0fc25c3442fd33c0f5dcc.png

    公式解析:

    ①MATCH(A$2:A$81,D$1:D1,):

    MATCH函数:返回指定数值在指定区域中的位置。

    该公式中:第一个参数A$2:A$81表示要查找的值,第二个参数D$1:D1表示要查找的区域,第三个参数省略,默认会查找小于或等于要查找值的最大值,该公式的意思是:A列的打卡日期在D列的打卡日期中首次出现的位置,如果有,就返回当前单元格所在的行号,否则返回错误值#N/A。

    842beea00771e0fbedb5aac5ed419f3d.png

    ②ISNA(MATCH(A$2:A$81,D$1:D1,))-1:

    ISNA函数:用于判断值是否为错误值#N/A,如果是,返回TRUE,否则,返回FALSE。在逻辑运算中,TRUE等于1,FALSE等于0,所以该公式后面减去1是把公式的结果转成以0和1显示的数组。

    793cb9f200519637f68ecf6479c6acd8.png

    ③FREQUENCY(0,ISNA(MATCH(A$2:A$81,D$1:D1,))-1):

    FREQUENCY函数:表示计算值在某个区域内出现的频率,然后返回一个垂直数组。第一个参数0表示要对其频率进行计数的一个数值,第二个参数由第②步可知,返回的是一组由0和1组成的数组,该公式的意识是:计算0在由0和1组成的数组中出现的频率。

    ④LOOKUP(,0/FREQUENCY(0,ISNA(MATCH(A$2:A$81,D$1:D1,))-1),A$2:A3):

    LOOKUP函数:LOOKUP函数有两种用法:向量形式和数组形式,这里用的是向量形式。该公式中,第2个参数是由1和0组成的数组,用0来除以这组数组,当分母为0的时候,计算结果是错误值#DIV/0!,所以返回的是一组由0和错误值#DIV/0!组成的数组,第一个参数省略,默认是0,表示在由0和#DIV/0!组成的数组中查找小于或等于0的值,最后根据0的位置得到第三个参数对应位置的数据。

    二、提取第一次打卡时间。

    E2单元格中输入公式:=SMALL(IF($A$2:$A$81=D2,$B$2:$B$81,9^9),1),按“Ctrl+Shift+Enter”三键结束数组公式的输入并将公式下拉填充至E22单元格即可。

    b0acc90f4fdf2b553ba82d559d79d112.png

    公式解析:

    ①IF($A$2:$A$81=D2,$B$2:$B$81,9^9):

    用IF函数来判断$A$2:$A$81的日期是否与D2单元格日期相等,如果相等,返回$B$2:$B$81对应单元格的值,否则返回一个很大的数据。

    ②SMALL(IF($A$2:$A$81=D2,$B$2:$B$81,9^9),1):

    因为“打卡日期”同一天的数据有多个,因此我们用IF函数求得的结果也有多个数据,此时我们用SMALL函数取出第1个小的值,也就是第一次打卡时间。

    三、提取最后一次打卡时间。

    F2单元格中输入公式:=MAX(IF($A$2:$A$81=D2,$B$2:$B$81,0)),按“Ctrl+Shift+Enter”三键结束数组公式的输入并将公式下拉填充至F22单元格即可。

    a6aa5ebbaa179739dfdc2e536b202ad2.png

    公式解析:

    ①IF($A$2:$A$81=D2,$B$2:$B$81,0):

    用IF函数来判断$A$2:$A$81的日期是否与D2单元格日期相等,如果相等,返回$B$2:$B$81对应单元格的值,否则返回0。

    ②MAX(IF($A$2:$A$81=D2,$B$2:$B$81,0)):

    因为“打卡日期”同一天的数据有多个,因此我们用IF函数求得的结果也有多个数据,此时我们用MAX函数可以在一组数据中取出最大的值,也就是最后一次打卡时间。

    展开全文
  • 大家知道,在Excel中可以利用VLOOKUP函数根据区域中第一列的某个数据查找出其他列中对应行的数据。例如,在下图中要查找对应“值班日期”为“初五”的“姓名”,可以用公式:  =VLOOKUP(A6,A1:D7,2,0) ...

    大家知道,在Excel中可以利用VLOOKUP函数根据区域中第一列的某个数据查找出其他列中对应行的数据。例如,在下图中要查找对应“值班日期”为“初五”的“姓名”,可以用公式:

      =VLOOKUP(A6,A1:D7,2,0)

    excel如何与VLOOKUP函数相反的左向查找公式  三联

      但VLOOKUP函数只能向右查找,如果要向左方查找数值,可以用INDEX函数和MATCH函数的组合公式。例如要查找上例中“科室”为“财务科”的“姓名”,即通过第4列中的数据在第2列中查找,可以用下面的公式:

      =INDEX(A2:D7,MATCH(D6,$D$2:$D$7,0),2)

      或:

      =INDEX(B2:B7,MATCH(C6,C2:C7,0))

      或用LOOKUP函数:

      =LOOKUP(1,0/(D2:D7=D6),B2:B7)

      还可以仍然使用VLOOKUP函数:

      =VLOOKUP(D6,IF({1,0},D2:D7,B2:B7),2,)

      上述几个公式都返回“周伯通”。最后一个公式,VLOOKUP函数的第二个参数“IF({1,0},D2:D7,B2:B7)”返回由一个二维数组,其中D2:D7为第一列,B2:B7为第二列。“IF({1,0},D2:D7,B2:B7)”可看作“{IF(1,D2:D7),IF(0,,B2:B7)}”。用TEXT函数也可返回这样的数组,如将公式的第二个参数改为“ TEXT({1,-1},D2:D7&";"&B2:B7)”:

      =VLOOKUP(D6,TEXT({1,-1},D2:D7&";"&B2:B7),2,)

      这个公式输入完毕后,需按Ctrl+Shift+Enter结束。

      如果要查找“科室”为“财务科”的“值班日期”,即通过第4列数据在第1列中查找,只需将第一个公式中最后一个参数“2”改为“1”,即可:

      =INDEX(A2:D7,MATCH(D6,$D$2:$D$7,0),1)

      将后几个公式中的“B2:B7”,改为“A2:A7”即可:

      =INDEX(A2:A7,MATCH(C6,C2:C7,0))

      =LOOKUP(1,0/(D2:D7=D6),A2:A7)

      =VLOOKUP(D6,IF({1,0},D2:D7,A2:A7),2,)

      使用下面的公式可以将“姓名”放到“科室”列左边的任意位置,公式都会返回正确的结果。

      =INDEX($A$1:$D$7,MATCH(D6,$D$1:$D$7,0),MATCH("姓名",$A$1:$D$1,0))

            注:更多精彩教程请关注三联电脑教程栏目,三联电脑办公群:189034526欢迎你的加入

    转自:http://wenda.so.com/q/1461344587723093

    展开全文
  • 大家好,前面的教程小编分享了5种提取数字的方法,没用函数公式,全部是用Excel技巧完成提取,适合初学者和函数不熟悉的。对于熟悉函数的小伙伴们,还是偏向于公式来提取数字,毕竟有些混合文本函数公式提取比...

    【温馨提示】亲爱的朋友,阅读之前请您点击【关注】,您的支持将是我最大的动力!

    dffff02577760ff1fe4089c42e27597b.png

    大家好,前面的教程中小编分享了5种提取数字的方法,没用函数公式,全部是用Excel技巧完成提取,适合初学者和函数不熟悉的。

    对于熟悉函数的小伙伴们,还是偏向于公式来提取数字,毕竟有些混合文本用函数公式提取比技巧要方便快捷,只要解决如何设置公式,再多的单元格复制公式就可以了

    情况一:数字固定长度、都在文本的右边

    f54fd0520749f02383ed63f1d25c9a38.png

    像上图表格中数字都在右边,而且手机号我们都知道是11位,这样我们可以直接用RIGHT函数来提取

    在B2单元格输入公式:=RIGHT(A2,11) 再下拉或双击填充公式即可完成全部提取手机号

    76b98ff3292ef0dff6851fdd324d7694.png

    情况二:数字位置固定(数字在左侧)、长度不同

    4cdc7f312b07cacf5376bc0b0db57769.png

    上图表格中不全是手机号,还有座机,虽然数字位置从头开始,但长度不一致,怎么办呢?

    在B2单元格输入公式:=LEFT(A2,2*LEN(A2)-LENB(A2)),再下拉或双击填充公式,即可完成整列数字提取。

    4e1228eeb4c4d83e2528f383d6478319.png

    情况三:数字位置固定(数字在右侧)、长度不同

    数字在右侧时公式:=RIGHT(A2,2*LEN(A2)-LENB(A2))

    68cf1bcb8a6442bdbfd7e6e6cc047d7d.png

    情况四:数字位置固定(数字在中间)、长度不同

    数字在中间公式:=MID(A2,3,2*LEN(A2)-LENB(A2))

    542da8724a95517d09f1a8db2f833b4e.png

    情况五:数字两端有特殊符号

    25e5235bb5654f5f36d34995568970f3.png

    上图表格中,数字位置、长度都不固定,唯一能看到的在数字两端有"-"符号

    公式:=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",99)),99,99))

    b65c502f8283677de7ceeb9a95e77388.png

    小伙伴们,在使用Excel中还碰到过哪些问题,评论区留言一起讨论学习,坚持原创不易,您的点赞转发就是对小编最大的支持,更多教程点击下方专栏学习。

    展开全文
  • EXCEL函数公式

    热门讨论 2010-03-16 03:26:38
    如何用公式求出最大值所在的行? 求多个最高分 如何求多条件的平均值 想求出第三大之数值 【查询查找引用】 查找顺序公式 怎样实现精确查询 查找及引用 查找函数的应用 怎么能方便的判断某个单元格包含多少个...
  • 享有“电子表格先生”美誉的国际知名作者John Walkenbach这种常用的公式和函数,并给出大量的实例帮读者理解这些公式和函数的运用及技巧。能过学习本书,读者可以掌握Excel的精华,制作出精美实用的电子表格,成为...
  • 享有“电子表格先生”美誉的国际知名作者John Walkenbach这种常用的公式和函数,并给出大量的实例帮读者理解这些公式和函数的运用及技巧。能过学习本书,读者可以掌握Excel的精华,制作出精美实用的电子表格,成为...
  • Vlookup函数,可以算是一个数据专员必须要会使用的基本函数了,确实很好。但是你可能会注意到,Excel一旦数据量过大,打开都费劲了,何况打开后,你还要输入...今天我就带着大家对比学习一下,怎么分别在Excel和P...
  • 乘法运算可以是乘积函数,也可以是...另外,如何在excel中进行划分?除法有两种计算方法,一种是使用乘积函数,另一种是使用斜杠(/),其差等于计算乘法。在乘法除法计算过程中,通常会遇到所有被一个数乘或除的数...
  • 前几天的教程我们通过学习公式求值F9键,看懂了统计不重复数据的第一种套路。今天我们接着上回所说,继续给大家分享第二种套路。赶紧来看看吧~*********数据源照旧,如下图所示,要求统计出不重复的...
  • 展开全部第一步:首先点2113击菜5261单栏的“公式”菜单。...扩展资料目前许多软件厂商借助Excel的友好界面强大的数据处理功能开始研究将其以更简单的方式应用到企业管理流程控制,比如ESSAP(Ex...
  • Excel如何删除公式保留数值

    千次阅读 2015-03-23 11:53:01
    Excel有的数据是用公式函数连接计算完成的,有时需要清除公式而保留结果值,或者将公式转化为数值。 1)快捷键F9 单元格或编辑栏输完公式后按下F9→Enter就可以把公式计算的结果转换为普通的数字,也...
  • 点击上方"Excel函数公式"免费订阅 求和,看似很简单,但在Excel的实际应用,也有很多技巧,除了Sum求和外,还可以Dsum、Sumproduct、Sumifs、Sumif以及快捷键、命令来求和。一、Dsum函数。(一)功能及语法结构...
  • 乘法运算可以是乘积函数,也可以是...另外,如何在excel中进行划分?除法有两种计算方法,一种是使用乘积函数,另一种是使用斜杠(/),其差等于计算乘法。在乘法除法计算过程中,通常会遇到所有被一个数乘或除的数...
  • 乘法运算可以是乘积函数,也可以是...另外,如何在excel中进行划分?除法有两种计算方法,一种是使用乘积函数,另一种是使用斜杠(/),其差等于计算乘法。在乘法除法计算过程中,通常会遇到所有被一个数乘或除的数...
  • 在Excel中用数据透视表按年、月、日、周进行小计比较方便,只需简单设置即可返回其小计结果;按年、月、日进行小计,可以直接显示在小计结果,但按周计算,周只能日期表示;使用公式进行小计可以将属于该周的...
  • 在Excel表格,有时候需要从很多数据里找出哪个数值最大,哪个数值最小。如果单纯肉眼人工去找,很显然不仅很麻烦还很容易出错。其实,Excel自带的函数公式可以快速找出其中的最大值最小值。下面举个例子,...
  • 如果是整数,在Excel表中超简单:设置单元格格式 - 特殊 - 中文大写数字如果你想用公式,有两个=TEXT(A3,"[DBNum2]G/通用格式")另一个是使用了在Excel函数查不到的隐藏函数=NUMBERSTRING(A3,2)如果数字含小数...
  • 今天老菜鸟针对上述日常工作经常会遇到的问题,总结了20个常用的关于日期时间的公式,赶紧来看看吧!学习更多技巧,请收藏关注部落窝教育excel图文教程。实际工作,经常需要进行日期时间...
  • EXCEL的TTEST求出t值,怎么算P?TTEST(array1,array2,tails,type) 返回与 t 检验相概率。Array1 为第一个数据集。Array2 为第二个数据集。Tails 指示分布曲线的尾数。如果 tails = 1, TTEST 使用单尾分布果 tails =...
  • 在Excel表格,有时候需要从很多数据里找出哪个数值最大,哪个数值最小。如果单纯肉眼人工去找,很显然不仅很麻烦还很容易出错。其实,Excel自带的函数公式可以快速找出其中的最大值最小值。下面举个例子,...
  • 今天老菜鸟针对上述日常工作经常会遇到的问题,总结了20个常用的关于日期时间的公式,赶紧来看看吧!学习更多技巧,请收藏关注部落窝教育excel图文教程。实际工作,经常需要进行日期时间...
  • 1, 第一个单元格 输入 公式 =C12, 选中剩余合并单元格,输入公式 =index(C:C,1+counta(A$1:A3))按Ctrl+Enter 即可(其中A$1:A3里的A3 根据第一个合并单元格的实际范围调整操作解释:index函数是以第一个参数的...
  • 大家好,前面的文章小编分享了使用快捷键【Ctrl+E】技巧完成混合文本提取数字,适合初学者和函数不熟悉的同学。 对于熟悉函数的小伙伴们,还是偏向于公式来提取数字,毕竟有些混合文本函数公式提取比技巧要方便...
  • 如下图所示,我们有不同年级不同班级的卫生评级,我们可以看到,年级字段是有重复值的, 班级字段也是有重复值的,现在我们需要给定年级班级的情况下,找出对应的卫生评级,这种情况下,普通的vlookup函数或者...
  • 最小二乘法是一种通过数值对曲线函数拟合的一种统计学方法,这里的最小是拟合误差达到最小。...生活,我们知道人的身高脚的大小是成正比的,这里我们假设身高脚的大小是成一元线性关系的。那么...
  • 如何在Excel中标识处理重复值

    千次阅读 2014-01-14 14:20:44
    首先,我们这是讨论单列的重复值标识、筛选、删除等处理方法,多列的重复值需要其他技巧或者函数公式,暂不此讨论。而这里,我们主要的COUNTIF函数,其他人有用MATCH的,但那有点绕所以没必要也不讲。   先来...
  • excel中如何计算两个日期之间的天数

    千次阅读 2019-01-14 17:56:44
    在excel中,两个日期直接相减就可以得到两个日期间隔的天数,如下图: 计算两个日期之间的天数,也可以隐藏函数DATEDIF函数来完成,公式如下: =DATEDIF(A2,B2,"d") DATEDIF()...
  • 之前跟大家分享了2个方法,数据透视表Power Query,今天我跟大家分享几个用函数公式实现的方法。下图,我们需要将A列的数据转成C:G多行多列的数据。方法一:使用INDEX+COLUMN+ROW函数组合。具体操作步骤如下:1...

空空如也

空空如也

1 2 3 4 5 ... 7
收藏数 137
精华内容 54
关键字:

在excel中如何用公式和函数