引言：本文的练习整理自chandoo.org。多一些练习，想想自己会怎么解决这个问题，看看别人又是怎样解决的，这样能够快速提高Excel公式编写水平。 本次的练习是：示例数据如下图1所示。...因此，如果要确定层次结构，可以计算点的数量，或者提取列A中数据右侧的数值。 注意，最顶层的层次结构没有价格值，因为这是最高级别。顺便说一下，列B与我们的问题无关。 列D是公式的输出。...不能使用辅助列，请写下你的公式。 解决方案 公式1：数组公式。....",""))<VALUE(SUBSTITUTE(A3:C20)) …… 公式并没有给出详细的解析，有兴趣的朋友可以参照前面推送的有关分析公式的文章对这些公式进行解析，相信对理解Excel函数，编写公式解决问题会有很大的帮助...注：公式练习的示例可能有用，也可能没有多大实际用处，主要目的是通过编写公式解决Excel问题来磨练公式与函数应用技能，也让研究Excel的大脑时刻保持良好的状态。
标签：Excel公式，SUMPRODUCT函数 基于列中的条件求和通常使用SUMIF函数或者SUMIFS函数，特别是涉及到多条件求和时。然而，随着条件的增多，公式将会变得很长，难以理解。...而使用SUMPRODUCT函数，可以判断同一列中的多个条件且公式简洁。 如下图1所示的示例。...也可以使用下面更简洁的公式： =SUMPRODUCT(($A$2:$A$12="东区")*(($B$2:$B$12={"超市1","超市2"}))*($C$2:$C$12)) 公式中，使用了花括号，允许在其中放置多个条件...，因此，如果需要满足的条件更多的话，就可以通过逗号分隔符将它们放置在花括号中，公式更简洁。...小结 在花括号中放置判断条件，从而使公式更简洁，是本文讲解的重点技巧。
但是，由于需要使用的Excel函数不能处理多维数组，因此必须首先将原数组转换为一维数组。 以示例来说明，如下图1所示的工作表。 ?...，即两个都是单行数组或都是单列数组，Excel将一个数组的元素与另一个数组中相应位置的元素“配对”。...这样，Excel返回#N/A作为结果数组中的第三个元素。 实际上，Excel为了解决传递两个大小不同的数组的问题，重新定义了两个中较小的一个，使其匹配较大的数组。...;"M","o",#N/A;"A","i",#N/A;"A","i",#N/A} 可参考《Excel公式技巧06： COUNTIFS函数如何处理以数组方式提供的条件》中讲解的对数组的解析的内容。...根据其定义，列向量当然是一维的。这里使用的技术请参阅《Excel公式技巧03：INDEX函数，给公式提供数组》、《Excel公式练习44：从多列中返回唯一且按字母顺序排列的列表》。
这个数字：9.9999999E+307，我们经常会在公式中用得上它。这是一个神奇的数字，在查找数值时能够发挥很大的作用。本文介绍的就是这个数字的使用技巧。...9.9999999E+307是Excel能够识别的最大数字，它通常与LOOKUP函数或MATCH函数结合使用，以查找列/行中的最后一个数字条目，或分别查找行/列中数值的最后一行行号/列号。...在本示例中，LOOKUP函数查找最后一个小于或等于查找值9.9999999E+307的条目，类似的规则或MATCH函数返回行号。...要获取数字条目的最后一行，使用公式： =MATCH(9.99999E+307,A:A) 或者，对于行，使用公式： =MATCH(9.99999E+307,1:1) 有兴趣的朋友，可以仔细研究，在需要时可以将此技术进行灵活运用...，一定会取得不错的效果。
表示内容如果包含√,则为真，为红色 表示内容开头为低,则为真 表示选中全部
Excel提供了排序功能，可以方便地对选中的列表进行排序。本文给出一个基于公式的排序解决方案，将指定区域内的数据按字母顺序排序。...A$11) 与单元格B2中的公式相比，唯一的变化是ROWS函数内由1改成了7。...因此，上述公式转换为： {3;4;9;5;7;2;1;6;10;8} 例如，所得到的数组中的第7个元素是1，是单元格B8中的字符串“Belinda”比较后的结果：按字母顺序，在区域内只有一个字符串在该字符串之前或等于该字符串...现在，将这个数组作为参数bins_array的值传递给FREQUENCY函数，将公式所在单元格对应行的相对行号（此处为7，由ROWS（$ 1：7）给出）作为参数data_array的值。...($1:7),COUNTIF($A$2:$A$11,"<="&$A$2:$A$11)),0)) 小结 虽然可以使用Excel的排序功能，但使用公式可以实时更新数据。
学习Excel技术，关注微信公众号： excelperfect 一个项目从2013年开始投资，相关数据如下图1所示，求该项目的投资回收期？也就是说，累计现金流等于0的那个时间点。 ?...可以以时间为横坐标，现金流数据为纵坐标，绘制图表后，测量横坐标上的时间来近似求出；也可以使用三角形等比公式来精确求得。这里使用公式来计算。...公式的关键在FORECAST函数，包含有3个参数。...,2,1,2) 得到单元格区域：E4:F4 同理，公式中的： OFFSET(C6,,MATCH(0,C6:G6)-1,1,2) 得到单元格区域E6:F6 因此，公式中的FORECAST函数转换为： =FORECAST...欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。 ?
还有整个公式的执行时怎么样过程？...---- 下面，就来说说我解读神难公式经常使用的三个知识点： 一、公式涉及计算逻辑较为复杂，用公式求值看过程 这种情况下，可以使用公式求值的功能一步步查看公式每次的关键计算结果，这样就很容易理解公式的计算过程了...，然后在每行的开头添加一定数量的空格实现缩进，达到公式层次结构分明的目的，如下图所示： 三、理解但不要使用公式的省略或省写参数 公式内省略或省写参数这种情况其实非常不好，所以我一般建议不要在公式里省略参数...公式不仅是自己用的，很多时候是要给别人看的！ 当然，有时候是因为别人写了这样的公式，那我们也要学会看得懂。...如下图所示： 接下来，下面再对比一下省略和省写参数的差别，方便大家加深印象： 以上是对一些复杂公式的解读办法，同时也说明了一些写公式时应该注意的问题，希望大家以后写公式时都能遵守一定的规范，不仅方便自己
通常，Excel默认的计算模式为“自动”计算，这可以从下图1所示的功能区“公式”选项卡“计算”组中的计算选项看出来。 ?...图2 这样，我们在Excel工作表单元格中修改值后，使用该值的公式结果会自动更新，不需要我们进行任何其他操作，这是我们在操作Excel工作表时常见的事情。...Excel在计算工作表时，遵循下列原则： 1.在修改单元格时，例如输入或编辑数据或公式时，Excel会根据新输入的数据或者编辑过的数据立即进行公式计算。...如果工作表中包含有成百上千个复杂公式，那么会导致Excel的计算速度变得缓慢，此时我们希望控制Excel何时计算公式。...可以将Excel的计算选项设置成“手动重算”，如上图2所示，或者直接在功能区选项卡“公式”的“计算”组中选取“计算选项——手动”，如上图1所示。让我们能够自已控制Excel何时来计算公式。
然而，并不是所有公式都能如此轻松地产生这样的效果，有些公式很“顽强”地抵制任何试图强制让它们返回数组的尝试。本文将探讨一些技术，除了数组形式的输入外，可以帮助强制达到想要的结果。...例如，下图1中单元格区域A1:A5是要使用的数据，右侧的数组公式并没有给出想要的结果。（特别说明：示例纯粹是为了演示我们要讲解的技术。） ?...幸运的是，Excel为提供了一个与N函数具有相似功能的T函数。与N函数一样，T函数同样具有强制返回数组的特性。N函数用于数字，T函数用于文本值。...在《Excel公式技巧03：INDEX函数，给公式提供数组》中，讲解了从INDEX函数强制返回数组的技术，在这里也可以使用。...所以可以说这个版本的公式更优于OFFSET版本的公式，尽管多使用了两个函数N和IF。
在我们编写公式时，特别是编写数组公式时，往往会生成由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进行相应的数字转换。至于如何使用，具体情况灵活使用相应的方法。
当你试图删除单元格区域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函数
标签：公式练习 在使用Excel时，经常会遇到根据多个条件求相应的和的问题。 示例数据如下图1所示。工作表中有两个表，一个是活动的全部数据，另一个列出了其中暂停活动的列表。...我首先想到使用经典的MATCH/INDEX函数组合的数组公式，但没有成功，接着转向使用矩阵公式。...第2种解法，更简单一些的公式，不过仍然是数组公式。...经过上述公式的编写后，对问题的理解更深入了，可以得出第6种解法。...注：在知识星球完美Excel社群可以下载本文配套示例工作簿。
SERIES公式控制着绘制Excel图表的数据，并且只在图表中有效，它不是真正的公式但可以像Excel公式一样在公式栏对其进行编辑。...认识SERIES公式 当你选取图表中的系列时，不仅工作表中该系列的数据会高亮显示，而且在公式栏中会显示一个公式，这个公式是你创建图表或添加系列时，Excel自动为你编写的。...$C$2，也可以是用双引号括起来的文本，如示例中的“A部门”，也可以省略为空。如果为空，则系列名称将由Excel创建为“系列N”，其中N是系列顺序号。...Y值不能为空，否则Excel会提示系列必须至少包含一个值。Y值中的文本被视为0。 绘制顺序 绘制顺序是图表的系列号，是介于1和图表系列数之间的数字。...编辑SERIES公式 可以像任意Excel公式一样，在公式栏中编辑SERIES公式，并且修改后的结果会立即使图表进行更新。
excelperfect 条件格式是有趣的，特别是使用公式并链接条件到单元格中时。下面是使用公式的条件格式的一些说明： 1.条件格式意味着如果条件满足应会应用设定的格式。...4.评估为TRUE或FALSE的逻辑公式可以用于创建条件格式。 5.条件格式可以使用非数组公式和数组公式。 6.条件格式是易失性的：经常重新计算，减慢整个工作表的计算时间。...7.使用公式创建条件格式的步骤： （1）选择单元格区域。 （2）打开“条件格式规则管理器”对话框。...（按Alt+N键，或者单击“新建规则”按钮） （4）从“选择规则类型”列表中选取“使用公式确定要设置格式的单元格”。 （5）单击“为符合此公式的值设置格式”框。 （6）输入公式。...单元格D18中的数组公式计算得到指定的时间并作为条件格式的辅助单元格。 ? 图2 欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。
标签：Excel公式 在某些情况下，尤其是编程时，需要能够进行二进制位操作。...例如，如果单元格A1中的值是13，B1中的值是7，则逻辑与（AND）操作结果如下（或十进制数5）： 定义位数 这些函数对固定的位数进行操作。...根据所需的位数，使用功能区“公式”选项卡“定义的名称”组中的“定义名称”来定义常量位。...位：=2^(8-ROW(INDIRECT("1:8"))) 16位：=2^(16-ROW(INDIRECT("1:16"))) 32位：=2^(32-ROW(INDIRECT("1:32"))) 定义的名称如下图...逻辑函数 对于整数x和y，或者引用包含整数的单元格： NOT x： 如果x位是0则结果位是1： =SUMPRODUCT(1-MOD(INT(x/Bits),2),Bits) x AND y： 如果x位和
大家好，又见面了，我是你们的朋友全栈君。 excel模板设置好公式即可。...在下面这行代码：workbook.write(out);// 输出Excel内容，生成Excel文件 “之前”， 添加这个语句：workbook.setForceFormulaRecalculation...(true);// 执行公式。...workbook.setForceFormulaRecalculation(true);// 执行公式 workbook.write(out);// 输出Excel内容，生成Excel文件 注意：...当cell.setCellValue(data.toString()) 参数为字符串时，如果进行数值的公式计算，则需要进行类型转换，否则失效，导出后结果为0 cell.setCellValue(Double.parseDouble
昨天一个前端的朋友找我帮忙用excel提取代码中的汉字（字符串），可算费了劲儿了，他要提取的内容均在单引号中，但问题是没有统一的规律，同一个单元格可能存在多个要提取的内容，而且汉字中间也夹杂其他字符。...所以总结了一下提取汉字的几种情况。 一、用公式提取Excel单元格中的汉字 对于一个混杂各种字母、数字及其他字符和汉字的文本字符串，要提取其中的汉字，在Excel中通常可用下面的公式。...说明：公式先用MID函数将字符串中的每个字符分解到到一个字符数组中，然后用LENB函数返回各字符的的字符数，对于汉字会返回“2”。...返回Excel工作表界面，在B14单元格中输入公式： =提取汉字(A14) 即可取得A14单元格字符串中的所有汉字。 二、用公式提取引号（某2个相同字符）之间的内容 ?...(A2,"'",""))))-FIND("'",A2)-1) 在excel中，如何查询字符串的第N次出现位置，或最后一次出现位置，使用公式： 最后一次出现位置 =FIND("这个不重复就行",SUBSTITUTE
学习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)) 或者
学习Excel技术，关注微信公众号： excelperfect 任何知识和技能都有最基本的东西，熟知这些，是学习的基础，也会使学习事半功倍。下面，我们就来看看Excel公式的那些基础知识。...公式的十大元素 Excel公式的组成，不外乎下列元素： 1.等号。每个公式的第一个字符一定是以等号开头。 2. 单元格引用。除了通常的单元格外，还包括定义的名称、工作表引用、工作簿引用和表。 3....Excel中的数学运算符如下表。 ? 图1 比较运算符 在公式中，使用比较运算符执行比较运算。Excel中的比较运算符如下表。 ?...图2 公式元素的优先级 在创建具有许多元素的大公式时，理解Excel如何评估或计算公式是特别重要的。下面列出了Excel评估或计算公式的顺序。 ?...图3 从表象看到实质 在Excel中，有时在工作表中看到的不一定是真实的，有时Excel默认的行为会给你提示，……。了解一些这方面的知识，会有助于公式的学习。 数值格式的假象 1.
领取专属 10元无门槛券
手把手带您无忧上云
云服务器
ICP备案
对象存储
腾讯会议
云直播
