Excel SUMPRODUCT -如何处理#值！如果数组包含文本，则出错 - 腾讯云开发者社区 - 腾讯云

开发者社区

文档建议反馈控制台

最新优惠活动

文章/答案/技术大牛

发布

精通Excel数组公式011：令人惊叹的SUMPRODUCT函数

由于SUMPRODUCT函数将非数字数据视为0，因此如果要在数组计算中使用TRUE和FALSE值，则必须将它们转换成1和0。可使用任何的数学运算来进行转换，但使用双减号通常是最快的计算方法。 9....SUMPRODUCT函数不需要Ctrl+Shift+Enter，并且将0、空单元格和文本视为数字0；而SUM函数的数组公式结果是错误值#VALUE!，因为数字和文本不能直接相乘。 ?...图5：4个单元格区域相乘后的结果相加得到总压力注意，如果SUMPRODUCT函数接受两个单元格作为其参数，但这两个单元格都为空，则结果是错误值，如下图6所示。 ?...图7 注意，虽然示例公式使用数组相乘作为SUMPRODUCT函数的参数array1的值，但是由于相乘操作不能处理文件，因此要注意用于相乘的数组中不能含有文本值，否则公式会导致错误#VALUE!。...而SUMPRODUCT函数则不会受到影响。因此，如果公式中含有对外部工作簿的数据引用或者遇到上述函数不能处理数组的情形时，则最好使用SUMPRODUCT函数。

6.2K1 0

错误不可怕，就看你如何使用ISNA函数

使用ISNA函数的基本公式示例： =ISNA(A2) 如果单元格A2包含#N/A错误，则返回TRUE，如果出现任何其他错误、值或空白单元格，则返回FALSE。如下图1所示。...要将A2中的值与列D中的每个值进行比较，公式为： =MATCH(A2,D2:D9,0) 如果找到查找值，MATCH函数将返回其在查找数组中的相对位置，否则将发生#N/A错误。...带有VLOOKUP函数的ISNA函数的语法如下： IF(ISNA(VLOOKUP(…),“自定义文本”,VLOOKUP(…)) 也就是说：如果VLOOKUP导致一个#N/A错误，则返回自定义文本，否则返回...)) 在这里，ISNA返回一个TRUE值和FALSE值数组，双否定（--）将逻辑值强制转换为数值1和0，然后SUMPRODUCT将结果相加。...图6 这就是如何在Excel中创建和使用ISNA公式，希望对你有所帮助。

9.8K2 0

您找到你想要的搜索结果了吗？

是的

没有找到

Excel基础入门——常用特殊函数用法详解（八）

时间过得很快，本节课已经到了Excel基础入门课程的最后一课，上节课我们给大家介绍了Excel常用的字符串/文本/日期函数，具体可回顾Excel基础入门——字符串/文本/日期函数详解（七），本节课我们准备给大家介绍...Excel中常用的特殊函数，重点介绍substitute/sumproduct/sumproduct+countif函数的使用方法。...注意：日常工作中对客户电话号码/身份证号进行加密处理都可以使用这种方法。...因此，SUMPRODUCT函数是把指定几个数组元素对应相乘然后返回乘积之和。公式=SUMPRODUCT (array1， [array2]， [array3]， ...)...当然还有一些Excel的一些常用技巧，例如，如何对数据中的重复值去重，如何设置条件格式，如何使用Excel中的透视表/透视图等等我们都没有介绍，因为这些基础内容不在我们本次课程的介绍范围，我们本系列的内容只对部分数据分析中常用到的

1.5K1 0

精通Excel数组公式020：MMULT数组函数

在前面的系列中，我们学习了通过乘以单元格区域来避开SUMPRODUCT函数对区域都要具有相同尺寸的要求。然而，如果单元格区域C3:D5中含有文本，那么乘法操作将产生错误。...可以使用MMULT函数创建与单元格区域C3:D5中预计收益数组相同大小的数组，然后传递组SUMPRODUCT函数，利用其忽略文本的特性。 ?...：（1）任意单元格为空或包含文本（2）数组1中的列数不等于数组2中的行数（3）MMULT函数返回的输出值超过5460个单元格 MINVERSE函数和MUNIT函数 MINVERSE函数和MUNIT...图13 Excel中数组乘法的3种方法在Excel中，进行数组相乘操作有3种方法：使用乘法符号直接相乘、使用SUMPRODUCT函数、使用MMULT函数。...接下来按照合并公式的步骤，最后得到的计算偏差的公式为： =SQRT(SUMPRODUCT((MMULT(C1:D1*C3:D5,{1;1})-D6)^2*B3:B5)) 如果不想在公式中使用硬编码，可以将公式修改为数组公式

2.4K2 0

【收藏】数据分析必会的Excel高频函数合集

第二个参数是需要查询的单元格区域，这个区域中的首列必须是要包含查询值，否则公式将会返回错误值。如果查询区域中包含多个符合条件的查询，VLOOKUP函数只能返回第一个查找到的结果。...提示：VLOOKUP函数第四参数为TRUE时，在近似匹配模式下返回查询之的精确匹配值或者近似匹配值。如果找不到精确匹配值，则返回小于查询值的最大值。...假如两个数组全部是数值，两种方法返回的结果是一致的，当有包含文本数据时，第一种方法可以返回正确结果，第二种方法会返回错误值#VALUE。...通俗的来讲，就是返回指定值在数值的位置，如果在数组中没有找到该值则返回#N/A。...4.2 MATCH与VLOOKUP函数组合如何根据姓名和月份查找相应的销售量？

3.7K2 0

精通Excel数组公式008：数组常量

列数组常量（垂直数组常量）如下图1和图2所示，如果使用公式引用一列中的项目，当按F9评估其值时，会看到：在花括号内放置了一组项目，文本被添加上了引号，分号意味着跨行，且项目列使用分号。 ?...行数组常量（水平数组常量）如下图3和图4所示，如果使用公式引用一行中的项目，当按F9评估其值时，会看到：在花括号内放置了一组项目，文本被添加上了引号，数字仍保留原形式，逗号意味着跨列，且项目行使用逗号...3.表数组常量（双向数组常量）如下图5和图6所示，如果使用公式引用行列组成的表，当按F9评估其值时，会看到：在花括号内放置了一组项目，文本被添加上了引号，数字仍保留原形式，分号意味着跨行，逗号意味着跨列...数组中的文本放置在双引号中 5. 数字、逻辑值和错误值不需要双引号 6....，如果单元格D3中的值为3，则数组为{1;2;3}，如果是2，则为{1;2}。

2.9K2 0

36条常用Excel技巧收藏备用！

一、单元格设置为文本；二、在输入数字前先输入' 5、如果隐藏了B列，如果让它显示出来？...) =INDEX(C1：C7，MATCH(H1&I1，A1：A7&B1：B7，0)) 16、如何隐藏单元格中的0 单元格格式自定义0；-0；；@ 或选项》视图》零值去勾。...(--RIGHT(A1，1))，"数字"，"字母") =IF(ISERR(RIGHT(A1)*1)，"字母"，"数字") 26、如何设置单元格，令其不接受包含空格的字符选定A列数据——有效性——...&"E1") A1为工作表名奇数行求和 =SUMPRODUCT((A1：A1000)*MOD(ROW(A1：A1000)，2)) 偶数行求和 =SUMPRODUCT((A1：A1000)*NOT(MOD...里的数字转换到Excel 方法有多种，选中》复制》设置输入单元格为文本》选择性粘贴》值选中》表格转换为文本》粘贴》分列》对分列选项设置为文本另存为文本文件》EXCEL中打开文本文件》对导入文本对话框进行对应设置

3.4K5 0

Excel公式技巧：颠倒单元格区域数组

如下图1所示，我想使用公式： =SUMPRODUCT(A1:G1,G2:A2) 但是，Excel总是将其修改为从左到右的单元格区域： =SUMPRODUCT(A1:G1,A2:G2) 图1 如何实现自己的目的呢...可以使用公式： =SUMPRODUCT(A1:G1,N(OFFSET(A2:G2,0,7-COLUMN(A2:G2),1,1))) 然而，公式使用了硬编码7，如果区域大小变了，这个数字也要手动更改。...这是因为OFFSET函数只返回单元格区域引用，而不返回值。OFFSET函数使用第1个、第2个或第3个参数为数组的调用，返回一组单元格区域引用，当用于算术操作数或大多数函数的参数时，Excel无法处理。...例如，我想求单元格A1+A3+A5之和，如果使用公式： =SUM(OFFSET(A1,{0;2;4},0,1,1)) 无论是否以数组公式输入，返回的值都是单元格A1中的值。...然而，如果使用公式： =SUM(N(OFFSET(A1,{0;2;4},0,1,1))) 则返回正确结果。

9015 0

精通Excel数组公式013：什么时候真正需要数组公式？

数组公式中的数组运算有时会显著增加公式计算时间。下面列举两个例子，看看与数组公式相比，选择非数组公式如何明显减少公式计算时间。...示例1：条件是文本时的日期统计，使用TEXT和SUMPRODUCT还是COUNTIFS？如下图1所示，条件区域是单元格E8中的年和F8中的月。...图3：多个SUMPRODUCT和TEXT函数的公式，更长的计算时间。注意，数组运算中使用不同函数的计算速度也不同。本示例中，使用MONTH和YEAR函数组合比使用TEXT函数的速度更快。...而且，如果你必须使用数组公式的话，选择特定的函数也可以缩短公式计算时间。例如在涉及日期格式的公式中使用YEAR和MONTH函数比使用TEXT函数会缩短公式计算时间。...注：本文为《精通Excel数组公式（学习笔记版）》中的一部分内容节选。你可以到知识星球App的完美Excel社群下载这本电子书的完整中文版。

1.5K2 0

Excel公式技巧25：使SUMIFSCOUNTIFS函数内的间接列引用变化

使用Excel朋友都知道，将包含相对列引用的公式复制到其他列时，这些引用也会相应地更新。...B:B"),"Y") 其中，A1包含要引用的工作表名称（例如“Sheet1”）。当我们向右拖动此公式时，间接引用的单元格区域不会改变。...当然，这是完全可以预料的，因为那些“单元格区域”根本不是真正的单元格区域，而只是伪装成单元格区域的文本字符串，只有通过将它们传递给INDIRECT函数才能将其解释为实际的单元格区域。...如果A1中的值是“Sheet2”，则： INDEX(INDIRECT("'"&$A$1&"'!A:XFD"),,COLUMNS($A:C)) 转换为： =INDEX(Sheet2!...C:C 然而，如果间接引用的不是一个工作表，而是多个工作表，如何处理？例如公式： =SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!

2.8K2 0

Excel公式技巧28： SUMPRODUCT函数基本原理详解

在Excel中，创造性地使用函数，往往能够收到令人意想不到的效果。应用技巧下面，我们使用SUMPRODUCT函数来实现条件求和。...图4 此时，我们可以添加一个包含逻辑值的数组，过滤掉不需要求和的数据，其原理如下图5所示。 ?...这样，相乘的三个数组如下图6所示： ? 图6 然而，我们不可能每次在公式中添加一个确定值的数组，因为如果要求和的条件发生改变的话，我们必须将这个数组进行相应的更改。...实际上，如果我们将要查找的值放置在某个单元格（例如G3）中，那么可以将公式修改为： =SUMPRODUCT(D4:D8,E4:E8,1*(C4:C8=G3)) 这样，直接修改该单元格中的值，就可以得到相应的结果...A2:A21="洗手液")=1×(A2:A21="洗手液") 其作用是强制将包含TRUE/FALSE值的数组转换成包含1/0的数组。

2.1K2 0

这个示例太典型，忍不住再拿出来秀一下

标签：Excel公式练习单元格中包含着一个数字，要求组成这个数字的各位数之和，例如，单元格中的数字是“123”，那么其各位数之和=1+2+3=6。...也可以使用公式2： =SUMPRODUCT(MID(B3,ROW(OFFSET(A1,,,LEN(B3))),1)+0) 公式中，使用的是： ROW(OFFSET(A1,,,LEN(B3))) 得到数组...： {1;2;3;4;5;6;7;8;9} 加上0，将MID函数得到的数字文本转换为数字，让SUMPRODUCT函数对它们进行求和。...还可以使用公式3： =SUMPRODUCT(MID(B3,ROW(A1:INDEX(A:A,LEN(B3))),1)+0) 公式中，使用的是： ROW(A1:INDEX(A:A,LEN(B3))) 得到数组...注：你可以到知识星球完美Excel社群下载本文配套示例工作簿，方便对照研习。

1.1K2 0

Excel公式练习：真真假假，假假真真——有多少是真？

标签：Excel公式练习看清楚这个世界，并不能让这个世界变得更好。但可能让你在看清楚这个世界是个怎样的世界后，把自己变得更好。...图1 最简单直接的方式就是： =A1+B1+C1 因为Excel将TRUE值转换为1，FALSE值转换为0，所以上述式子结果如果为2，则表明该区域仅包含两个TRUE值。...还可以使用求和的数组公式： =SUM(A1:C1*{1,1,1}) 或者： =SUM(A1:C1*{TRUE,TRUE,TRUE})=2 或者直接使用数组函数SUMPRODUCT： =SUMPRODUCT...在单元格区域A1:C100中包含着TRUE/FALSE值，求各行中仅包含两个TRUE值的行数。...注：可以到知识星球完美Excel社群下载本文配套示例工作簿。

7302 0

Excel公式技巧32：处理公式中的布尔值

在我们编写公式时，特别是编写数组公式时，往往会生成由TRUE/FALSE值组成的中间数组。...有些Excel函数可以忽略这些布尔值，例如SUM函数，但是很多函数不能处理这些布尔值，如果将它们传递给这些函数，就会导致错误。因此，在将这些布尔值传递给函数继续处理时，需要将它们转换成数字。...在Excel中，TRUE值等于1，FALSE值等于0，那么如何将TRUE/FALSE值转换成1/0呢？最常用的方法是使用数学运算。...： =SUMPRODUCT((MID(B3,ROW(OFFSET($A$1,,,LEN(B3))),1)*1)) =SUMPRODUCT(MID(B3,ROW(OFFSET($A$1,,,LEN(B3)...至于如何使用，具体情况灵活使用相应的方法。

2.8K1 0

Excel的sum相关函数使用方法

说明 • 直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算。 • 如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。 ...￭函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。 SUMSQ 返回所有参数的平方和。语法 SUMSQ (number1, number2, ...) ...说明参数可以是数值、数组、名称、或者是数组的引用。若数组或引用参数包含文本、逻辑值以及空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。...说明参数可以是数值、数组、名称、或者是数组的引用。若数组或引用参数包含文本、逻辑值以及空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。...说明参数可以是数值、数组、名称、或者是数组的引用。若数组或引用参数包含文本、逻辑值以及空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。

1051 0

八种方式实现多条件匹配

第一列变成了如果两者均相等才显示为1，如果有其中任意一个不等都是0，则最终结果就是0 第二列就是心中评分。...然后Vlookup根据1查找，则新的辅助表只有两个条件都相等的时候才是1，否则是0 那只有一个返回值就是6啦！本案例的精髓在于深刻理解数组是如何重构及重构后的表是什么样子的！...重要说明一个第二个参数0/(B2:B9=G2)*(C2:C9=H2) 某列等于某个单元格得到的是True、False数组，两个数组相乘是1、0数组。因为数字0不可以作为分母，如果是分母会报错！...大大的提醒：下面的方法只适用于返回值是数字的！如果是返回值是文本，只能用上面的哦！...公式：=SUMPRODUCT((B2:B9=G2)*(C2:C9=H2)*D2:D9) Sumproduct是数组乘积求和，也不解释啦，其实看我上面的那个文章就理解了，不懂的留言交流！

12.9K4 1

Excel公式练习70：求最近一次活动的日期

本次的练习是：如何使用公式求得最近的日期？例如，下图1所示，x表示该日期开展了一次活动，在列G中求出对应的最近一次活动日期。 ? 图1 先不看答案，自已动手试一试。...解决方案公式1：使用LOOKUP函数 =LOOKUP("y",C4:F4,F3) 由于示例中采用“x”表示开展活动对应的日期，使用其随后的字母“y”来查找，显示在对应区域找不到该值，这样LOOKUP函数会返回与查找值最接近的值...公式2：使用MAX/SUMPRODUCT函数 =SUMPRODUCT(MAX((C3:F3)*(C4:F4="x"))) 由于日期在Excel中是以数字形式存储的，因此可以将它们与TRUE/FALSE值组成的数组相乘...{41091,41092,0,0})) 得到： 41092 即该日期对应的序数，设置适当的格式后在Excel中显示相应的日期。...我们使用了SUMPRODUCT函数强制进行数组运算，如果不使用SUMPRODUCT函数，则在输入公式： =MAX((C3:F3)*(C4:F4="x")) 需按Ctrl+Shift+Enter组合键。

2K1 0

常用的10个 Excel 万能公式套路

:C9) 注：和Sumifs相比速度虽然慢了点，但Sumproduct可以对数组进行处理后再设置条件，同时也可以对文本型数字进行计算，而Sumifs函数则不可。...Enter三键输入) 示例：如下图所示，提示A列中字符串中的数字 =LOOKUP(9^9,MID(A2,MATCH(1,MID(A1,ROW(1:99),1)^0,0),ROW(1:9))*1) 注：如果字符串超过...6、计算不重复值个数的公式 =SUMPRODUCT(1/COUNTIF(区域,区域)) 示例：统计B列的客户数量 =SUMPRODUCT(1/COUNTIF(B2:B19,B2:B19)) ?...8、个人所得税计算公式假如A2中是应税工资，则计算个税公式为： =5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},) 9、用QQ聊天的...Excel的公式 =HYPERLINK("tencent://message/?

1.2K2 0

数据分析常用的Excel函数合集（下）

Excel函数分类：关联匹配类、清洗处理类、逻辑运算类、计算统计类、时间序列类上篇已经给大家分享过关联匹配类和清洗处理类，今天将继续分享其余三类：逻辑运算类、计算统计类、时间序列类。...IF 功能：使用逻辑函数 IF 函数时，如果条件为真，该函数将返回一个值；如果条件为假，函数将返回另一个值。语法：=IF(条件, true时返回值, false返回值) ? 2....COUNTIFS函数：统计一组给定条件所指定的单元格数 SUM函数：计算单元格区域中所有数值的和 SUMIF函数：对满足条件的单元格求和 SUMPRODUCT函数：返回相应的数组或区域乘积的和 STDEV...SUMPRODUCT 功能：返回相应的数组或区域乘积的和语法：=SUMPRODUCT(单元格1: 单元格2 ,单元格3: 单元格4) ? 10. Stdev 统计型函数，求标准差，衡量离散程度。...为 1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。

3K2 0

SUMIFS函数，很有用

4.SUMIFS函数在求和时，会忽略参数sum_range中的空和文本值。 5.参数criteria可以是数字、日期、表达式、单元格引用、文本或公式，可以使用通配符（*,?）...如果有文本、数学符号、逻辑符号（例如=、+、-、*、/），则应添加双引号。如果要查找?或*号，在前面添加波浪符（~），例如~?和~*。...7.由于SUMIFS函数构造条件的内部语法，参数sum_range需要指定为单元格区域，不能使用数组。 8.SUMIFS函数可以处理单元格区域，但不能处理数组。...这意味着不能在条件区域内使用其他函数（值得商榷，见拓展资料：Excel公式技巧05和06），如YEAR，因为结果是一个数组。如果需要此功能，可使用SUMPRODUCT函数。...9.如果参数criteria测试文本值，应使用双引号将文本值括起来，否则结果会显示0。 10.如果参数sum_range中包含TRUE和FALSE时，结果可能会不正确。

1.8K3 0

点击加载更多

扫码

添加站长进交流群

领取专属 10元无门槛券

手把手带您无忧上云

扫码加入开发者社群

相关资讯

热门标签

活动推荐

运营活动

活动名称

广告关闭