展开

关键词

Excel公式练习48: 比较字符串中字符

导语:继续研究来自于excelxor.com案例。建议结合本文阅读原文,会了解更多细节,会有更大收获。 本次练习是:在单元格A2中给定一个字符串值,仅由大写字母组成且字符长度至少为2。 在单元格B2中输入公式:如果A2中字符各个字符按字母升序排列(从左到右),则返回TRUE;否则返回FALSE。如图1所示。 ? 图1 先不看答案,自已动手试一试。 公式 在单元格B2中公式为: =AND(GESTEP(MMULT(CODE(MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)-1))+{0,1},1)),{-1;1} 公式解析 首先,让我们看一下公式用于生成要传递给MID函数参数数组结构: ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)-1)) 该构造与下面的标准结构返回相同数组: ;0;1;0;0;0},0)) 转换为: =AND({1;1;1;1;1;1;1}) 结果为: TRUE 小结 1.学习Excel怎么处理两个正交且元素数量不同数组运算。

34610

Excel经典公式:提取位于字符串右侧所有字符

标签:公式与函数,FIND函数 假设有一列数据,由左侧数字和右侧文本组成,我们需要移除其中数字而只提取其中文本。 示例数据如下图1所示。 图1 下面的公式将删除左侧所有数字,只获得单元格中文本部分。 图2 在公式中,使用FIND函数查找26个字母在字符串中位置,得到每个字母在单元格中首次出现位置。注意,由于FIND查找区分大小写,因此使用LOWER(A2)将单元格中字母转换为小写。 然后,使用IFERROR函数,这样,如果公式无法找到特定字母表位置,它将返回单元格中内容长度(由LEN公式给出),而不是返回值错误#VALUE!。 注意,在MID函数中使用了1000个字符进行提取,但如果单元格中字符数较少,则只会提取那么多字符。 欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。

63730
  • 广告
    关闭

    《云安全最佳实践-创作者计划》火热征稿中

    发布文章赢千元好礼!

  • 您找到你想要的搜索结果了吗?
    是的
    没有找到

    使用Word自带编辑数学公式工具输入公式遗漏点。

    1、点击[插入] 2、点击[公式] 3、点击[文本] 4、点击[开始] 5、点击[倾斜]

    7620

    Excel公式技巧24: Excel公式降维技术

    ,即两个都是单行数组或都是单列数组,Excel将一个数组元素与另一个数组中相应位置元素“配对”。 这样,Excel返回#N/A作为结果数组中第三个元素。 实际上,Excel为了解决传递两个大小不同数组问题,重新定义了两个中较小一个,使其匹配较大数组。 因此,可以构造公式: =MID(A1:A9,1,1) 返回一个9行1列一维数组,该数组由A1:A9中每个字符第一个字符组成,即: {"A";"E";"P";"C";"R";"B";"M";"A"; ;"M","o",#N/A;"A","i",#N/A;"A","i",#N/A} 可参考《Excel公式技巧06: COUNTIFS函数如何处理以数组方式提供条件》中讲解对数组解析内容。 根据其定义,列向量当然是一维。这里使用技术请参阅《Excel公式技巧03:INDEX函数,给公式提供数组》、《Excel公式练习44:从多列中返回唯一且按字母顺序排列列表》。

    58521

    Excel经典公式:提取位于字符串后面的数字

    标签:公式与函数 有时候,可能有一个数据集,单元格中包含着文本和数字,其中数字在字符右侧,如下图1所示,你希望删除所有文本,而只保留数字。 图1 下面的公式将删除数字左侧所有文本,因此只剩下字符串中数字部分。 图2 上面的公式移除了单元格中左侧文本部分,只剩下右侧数字。 公式中: FIND({0,1,2,3,4,5,6,7,8,9},A2) 将在单元格中查找这10位数字,并返回这些数字位置组成数组。 此时,IFERROR公式在单元格中找到数字时将返回一个数字,如果找不到数字,则将返回该单元格中字符长度(使用LEN公式完成)。 现在,知道了数字在单元格中起始位置,接着需要知道需要从左侧删除多少个字符。为此,使用LEN函数来计算单元格中字符总长度,并从中减去刚才得到结果,这样就可以知道左侧有多少个文本字符

    49740

    Excel公式练习86:找出字符串中最大数字

    本次练习是:从一个由文本和数字组成文本字符串中,提取出连续最长数字。如下图1所示,在单元格B2中包含一个由文本和数字组成字符串,很显然,其最长数字是9367。 ? 图1 如何使用公式获得结果? 解决方案 在单元格C2中输入数组公式: =MAX(IFERROR(MID(B2,ROW(A1:A50),COLUMN(A1:Z1))*1,"")) 结果如下图2所示。 图2 公式解析 在公式中,使用了MID函数,通常,我们使用MID函数如下: =MID(B2,5,2) 返回单元格B2中从第5个字符开始2个字符,在图1示例中,返回“2E”。 公式中共3个部分:单元格引用,起始位置,要提取字符数。 然而,现在要从单元格B2内每个起始位置提取多个值! 在上面解决方案公式中: ROW(A1:A50) 创建了50个起始位置。 COLUMN(A1:Z1) 对于每个起始位置,依次提取1个、2个、……最多到26个字符。 MID(B2,ROW(A1:A50),COLUMN(A1:Z1)) 结果如下图3所示。 ?

    40940

    Excel公式技巧32: 处理公式布尔值

    在我们编写公式时,特别是编写数组公式时,往往会生成由TRUE/FALSE值组成中间数组。 有些Excel函数可以忽略这些布尔值,例如SUM函数,但是很多函数不能处理这些布尔值,如果将它们传递给这些函数,就会导致错误。因此,在将这些布尔值传递给函数继续处理时,需要将它们转换成数字。 在Excel中,TRUE值等于1,FALSE值等于0,那么如何将TRUE/FALSE值转换成1/0呢?最常用方法是使用数学运算。 使用双减号: --{TRUE,FALSE}=(-1)*(-1)*{TRUE,FALSE}=1*{TRUE,FALSE}={1,0} 例如,在《Excel公式练习63:求数值中各个数字之和》中,我们可以使用下面的公式 有时候,公式本身就会与生成数字相乘,这样也会将TRUE/FALSE进行相应数字转换。至于如何使用,具体情况灵活使用相应方法。

    81110

    Excel公式技巧15: 探讨强制返回数组公式技术

    然而,并不是所有公式都能如此轻松地产生这样效果,有些公式很“顽强”地抵制任何试图强制让它们返回数组尝试。本文将探讨一些技术,除了数组形式输入外,可以帮助强制达到想要结果。 例如,下图1中单元格区域A1:A5是要使用数据,右侧数组公式并没有给出想要结果。(特别说明:示例纯粹是为了演示我们要讲解技术。) ? 幸运是,Excel为提供了一个与N函数具有相似功能T函数。与N函数一样,T函数同样具有强制返回数组特性。N函数用于数字,T函数用于文本值。 在《Excel公式技巧03:INDEX函数,给公式提供数组》中,讲解了从INDEX函数强制返回数组技术,在这里也可以使用。 所以可以说这个版本公式更优于OFFSET版本公式,尽管多使用了两个函数N和IF。

    71110

    Excel揭秘21:Excel是如何控制公式计算

    通常,Excel默认计算模式为“自动”计算,这可以从下图1所示功能区“公式”选项卡“计算”组中计算选项看出来。 ? 图2 这样,我们在Excel工作表单元格中修改值后,使用该值公式结果会自动更新,不需要我们进行任何其他操作,这是我们在操作Excel工作表时常见事情。 Excel在计算工作表时,遵循下列原则: 1.在修改单元格时,例如输入或编辑数据或公式时,Excel会根据新输入数据或者编辑过数据立即进行公式计算。 如果工作表中包含有成百上千个复杂公式,那么会导致Excel计算速度变得缓慢,此时我们希望控制Excel何时计算公式。 可以将Excel计算选项设置成“手动重算”,如上图2所示,或者直接在功能区选项卡“公式“计算”组中选取“计算选项——手动”,如上图1所示。让我们能够自已控制Excel何时来计算公式

    37810

    精通Excel数组公式023:使用数组公式条件格式

    excelperfect 条件格式是有趣,特别是使用公式并链接条件到单元格中时。下面是使用公式条件格式一些说明: 1.条件格式意味着如果条件满足应会应用设定格式。 4.评估为TRUE或FALSE逻辑公式可以用于创建条件格式。 5.条件格式可以使用非数组公式和数组公式。 6.条件格式是易失性:经常重新计算,减慢整个工作表计算时间。 7.使用公式创建条件格式步骤: (1)选择单元格区域。 (2)打开“条件格式规则管理器”对话框。 (按Alt+N键,或者单击“新建规则”按钮) (4)从“选择规则类型”列表中选取“使用公式确定要设置格式单元格”。 (5)单击“为符合此公式值设置格式”框。 (6)输入公式。 单元格D18中数组公式计算得到指定时间并作为条件格式辅助单元格。 ? 图2 欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。

    58030

    制作反转字符串函数,填补Excel内置函数空白

    标签:VBA 在《20211202,爱对称日,Excel也能判断》中,我们使用公式来判断单元格中内容是否为“回文”,公式稍有点复杂。然而,如果Excel中有一个反转函数,那就简单了。 其实,VBA中有一个内置函数可用来反转字符串:StrReverse函数。如果Excel工作表中能够使用这个函数,那么就可直接在Excel中反转字符串了。 这需要编写一个自定义函数,但很简单。 现在,我们再来解决判断单元格中内容是否为回文问题,如下图1所示。 图1 只需输入公式: =IF(C4=RevString(C4),"是回文","不是!") 就可以获得结果。 是不是比单独使用Excel内置函数简单了许多! 我们进一步看看RevString函数效果,如下图2所示。 图2 有了这个函数后,对于许多Excel问题,如果不想编写复杂公式,往往可以使公式更简洁。

    15530

    精通Excel数组公式009:提供多个值数组公式

    当你试图删除单元格区域A2:A6中某个单元格中内容、删除整行等时,会导致下图2所示错误。 ? 图2 2. 按Ctrl+/键可以选择当前数组公式所在区域。 3. 有两种方法删除数组公式区域内容。 如果需要编辑数组公式,则可以在该数组公式区域中编辑任一单元格中公式,然后按Ctrl+Shift+Enter键。 5. 选择数组公式区域任意单元格,在公式栏中都会看到相同公式。 6. 上例中数组公式可以归纳为一个求序号公式构造: ROW(单元格区域)-ROW(单元格区域中第一个单元格)+1 这个公式构造可以作为更高级数组公式元素。 图10 《Ctrl+Shift+Enter:MasteringExcel Array Formulas》学习笔记 完美Excel 欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。 欢迎到知识星球:完美Excel社群,进行技术交流和提问,获取更多电子资料。 完美Excel社群2020.9.2动态 #VBA# 增强CELL函数和INFO函数

    39650

    Excel公式技巧10: 从字符串中提取数字——数字位于字符串开头

    本文主要研究从字符串开头提取数字技术: 1. 这些数字是连续 2. 这些连续数字位于字符开头 3. 其实这并不困难,让lookup_value使用所谓“大数”(即9.99999999999999E+307,这是Excel中允许最大正数),确保这种公式构造有效。 当然,这不是唯一会出现这种情况字符串,实际上,任何可以被Excel解释为日期字母数字都会如此,例如30SEP、01FEB等,这也会导致不正确结果。 此外,公式1对于诸如123E3等形式字符串也无效,其结果将是123000。因为在通常情况下,将123E3输入单元格后,Excel会自动将其转换成科学计数格式。 原因是字符末尾有其他数字,因此COUNT函数统计数字个数大于字符串开头数字个数,这样LEFT取值仍是字母数字混合字符串。

    1.2K20

    Excel字符串中提取数字,不需要大神级公式

    有个二货给表里面有一列中英文、数字、标点符号全混在一起,我要把数字提出来! 大海:呵呵。网上不是有很多大神写过很牛B公式吗? 小勤:我找过了,找到一个,完全看不懂! 而且试了一下,结果还不是全对!你看: 大海:这个公式我也写不粗来! 小勤:那怎么办?难道非得用VBA吗? 大海:如果是以前,我会用VBA,现在,我用Power Query,而且很简单,大家都能学会。 小勤:真的?太好了,赶紧告诉我。 大海:看着啊。只需要Text.Remove一个函数就行了。 大括号里就是要删除内容,包括空格、-、从字母A到龥字,对吧? 大海:对,就是这个意思。要删啥就扔啥在大括号里,其中A到龥包含了所有大小写字母和中文汉字。 小勤:那如果只删汉字呢?

    17020

    Excel公式技巧11: 从字符串中提取数字——数字位于字符串末尾

    上篇文章讲解了提取位于字符串开头数字公式技术,本文研究从字符串开头提取数字技术: 1. 这些数字是连续 2. 这些连续数字位于字符末尾 3. “0123456789”效果相同,详见《Excel公式技巧08. 17分之一和其他全数字(pandigitals)》。 这样,上述公式转换为: =0+MID("ABC456",4,6) 得到结果: 456 下面我们看看如果单元格A1中字符串包含其它数字例如123ABC456,公式1是否有效。 LOOKUP与RIGHT 公式2: =-LOOKUP(0,-RIGHT(A1,ROW(INDIRECT("1:"& LEN(A1))))) 这个公式与上一篇文章《Excel公式技巧10:从字符串中提取数字 ——数字位于字符串开头》中公式1相似,只是使用RIGHT函数代替了LEFT函数。

    82520

    Excel】用公式提取Excel单元格中汉字

    昨天一个前端朋友找我帮忙用excel提取代码中汉字(字符串),可算费了劲儿了,他要提取内容均在单引号中,但问题是没有统一规律,同一个单元格可能存在多个要提取内容,而且汉字中间也夹杂其他字符。 所以总结了一下提取汉字几种情况。 一、用公式提取Excel单元格中汉字 对于一个混杂各种字母、数字及其他字符和汉字文本字符串,要提取其中汉字,在Excel中通常可用下面的公式。 说明:公式先用MID函数将字符串中每个字符分解到到一个字符数组中,然后用LENB函数返回各字符字符数,对于汉字会返回“2”。 返回Excel工作表界面,在B14单元格中输入公式: =提取汉字(A14) 即可取得A14单元格字符串中所有汉字。 二、用公式提取引号(某2个相同字符)之间内容 ? (A2,"'",""))))-FIND("'",A2)-1) 在excel中,如何查询字符第N次出现位置,或最后一次出现位置,使用公式: 最后一次出现位置 =FIND("这个不重复就行",SUBSTITUTE

    2.4K61

    Excel公式技巧95:统计文本单元格神秘公式

    图1 有一些附加条件: 排除包含数字任何单元格 排除空单元格 排除包括公式结果为空字符任何单元格 在图1所示示例中,满足条件文本单元格共5个。 理解Excel将什么考虑为“空” 需要弄明白是,空字符串与空单元格不同。空单元格中没有任何内容,空字符串是不显示任何结果公式结果。 如果使用ISBLANK函数测试包含空文本单元格,返回结果将是FALSE,这意味着Excel不会将空文本作为真正空单元格。 ,忽略了空单元格和空字符串单元格。 图3 也许,你可能会想使用LEN函数来测试单元格长度,从而忽略空单元格或空字符串单元格,如下所示: =SUMPRODUCT(--(LEN(B4:B14)>0)) 然而,对于图1所示示例来说,返回结果是

    31520

    Excel公式技巧34: 由公式中日期处理引发探索

    学习Excel技术,关注微信公众号: excelperfect 我们知道,在Excel中,日期是以序号数字来存储,虽然你在工作表中看到是“2020-3-31”,而Excel中存储实际上是“43921.00 这样方便了日期表示和存储,但也同样带来了一些问题,例如我们以为是“2020-3-31”,因此会将数据直接与之比较,导致错误结果。本文举一个案例来讲解公式中日期处理方式。 我们来看看正确公式: =AVERAGE(IF(A2:A20=DATE(2020,3,31),B2:B20)) 这也是一个数组公式。 等价于公式: =AVERAGE(B2:B7) 5. 我们注意到,上面的公式中我们没有提供IF函数参数value_if_false值,这是有原因。 其实,Excel 2007及以后版本中引入了一个函数AVERAGEIFS,可以很好地解决上述问题,其公式为: =AVERAGEIFS(B2:B20,A2:A20,DATE(2020,3,31)) 或者

    24530

    Excel公式技巧75:查清与公式相关单元格

    在使用Excel时,我们肯定会使用公式。这些公式要么会引用其他单元格,要么会被其他单元格引用。下面我们先看看这两种情况,如下图1所示。 ? 单击功能区“公式”选项卡“公式审核”组中“追踪引用单元格”命令。单击一次,Excel绘制出直接引用单元格箭头,再次单击,绘制出间接引用单元格箭头,如下图2所示。 ? 2. 按Ctrl+Shift+{键,选择当前工作表中公式单元格所有引用单元格(包括直接和间接引用单元格)。 4. 在公式单元格中按F2键,Excel会使用不同颜色显示该单元格直接引用单元格。 5. 单击功能区“公式”选项卡“公式审核”组中“追踪从属单元格”命令。单击一次,Excel绘制出直接从属单元格箭头,再次单击,绘制出间接从属单元格箭头,如下图4所示。 ? 2. 欢迎到知识星球:完美Excel社群,进行技术交流和提问,获取更多电子资料。

    40030

    精通Excel数组公式002:谈谈公式最基本那些事儿

    学习Excel技术,关注微信公众号: excelperfect 任何知识和技能都有最基本东西,熟知这些,是学习基础,也会使学习事半功倍。下面,我们就来看看Excel公式那些基础知识。 公式十大元素 Excel公式组成,不外乎下列元素: 1.等号。每个公式第一个字符一定是以等号开头。 2. 单元格引用。除了通常单元格外,还包括定义名称、工作表引用、工作簿引用和表。 3. Excel数学运算符如下表。 ? 图1 比较运算符 在公式中,使用比较运算符执行比较运算。Excel比较运算符如下表。 ? 图2 公式元素优先级 在创建具有许多元素公式时,理解Excel如何评估或计算公式是特别重要。下面列出了Excel评估或计算公式顺序。 ? 图3 从表象看到实质 在Excel中,有时在工作表中看到不一定是真实,有时Excel默认行为会给你提示,……。了解一些这方面的知识,会有助于公式学习。 数值格式假象 1.

    30820

    相关产品

    • 商业智能分析 BI

      商业智能分析 BI

      商业智能分析(BI)整合永洪科技产品能力,提供一站式云端自助分析功能和全面的企业级云分析服务支持自服务数据准备、探索式分析和企业级管控,是新一代的敏捷型商业智能分析服务平台。只需几分钟,您就可以在云端轻松自如地完成数据分析、业务数据探查、报表制作等一系列数据可视化操作……

    相关资讯

    热门标签

    活动推荐

    扫码关注腾讯云开发者

    领取腾讯云代金券