如何计算excel中的特定单元格 [英] how to calculate specific cells in excel
问题描述
要获得8和8 / 4b和8b / 4的4个值我使用这个功能:
= IF(LEN(L27)= 4, IF(ISNUMBER(SEARCH(b,LEFT(L27,2))),RIGHT(L27,1),LEFT(L27,1)),L27)
此功能很好,但
1)它返回8和4的值作为char,而不是数字。所以SUM函数给出错误。
2)我该怎么做这个函数来检查范围内的单元格,并在这些条件下对它们进行SUM(在上面的函数中的条件)
提前感谢
这样做如何:
= IF(LEN(L27)= 4,IF(ISNUMBER(SEARCH( b,LEFT(L27,2))),VALUE(RIGHT(L27,1)),VALUE(LEFT( L27,1))),L27)
编辑:
将这一切都放在一个公式中,我只能想出这个怪物:
{= SUMPRODUCT - (LEN(I27:N27)= 4), - (ISNUMBER(SEARCH( b,LEFT(I27:N27,2)))),IF(ISNUMBER(VALUE(RIGHT(I27:N27,1) )),VALUE(RIGHT(I27:N27,1)),0))+ SUMPRODUCT( - (LEN(I27:N27)= 4), - (ISNUMBER(SEARCH( b,RIGHT(I27:N27 ,2)))),IF(ISNUMBER(LEFT(I27:N27,1))),VALUE(LEFT(I27:N27,1)),0))+ SUM(I27:N27)}
这是一个数组公式,所以你需要用Ctrl + Shift + Enter输入。这些括号 {}
将自动出现。不要手动输入。
对于范围,将 I27:N27
更改到您的范围。 p>
I have excel sheet which cells contain both string and numeric values. I have a problem calculating numeric values. I need a function that will check cells in given range and calculate(sum) numeric values. Result have to be : 8+4+10+12+12+5=51
To get 8 and 4 values from 8/4b and 8b/4 I use this function:
=IF(LEN(L27)=4,IF(ISNUMBER(SEARCH("b",LEFT(L27,2))),RIGHT(L27,1),LEFT(L27,1)),L27)
This function works well, but
1) it returns 8 and 4 values as char, not as number. So SUM function gives error.
2) how can I do the function to check cells in range and SUM them under those conditions (conditions in my function above)
thanks in advance
How about this:
=IF(LEN(L27)=4,IF(ISNUMBER(SEARCH("b",LEFT(L27,2))),VALUE(RIGHT(L27,1)),VALUE(LEFT(L27,1))),L27)
Edit:
To put this all into one formula, I could only come up with this monster:
{=SUMPRODUCT(--(LEN(I27:N27)=4),--(ISNUMBER(SEARCH("b",LEFT(I27:N27,2)))),IF(ISNUMBER(VALUE(RIGHT(I27:N27,1))),VALUE(RIGHT(I27:N27,1)),0))+SUMPRODUCT(--(LEN(I27:N27)=4),--(ISNUMBER(SEARCH("b",RIGHT(I27:N27,2)))),IF(ISNUMBER(VALUE(LEFT(I27:N27,1))),VALUE(LEFT(I27:N27,1)),0))+SUM(I27:N27)}
This is an array formula, so you need to enter it with Ctrl+Shift+Enter. These brackets {}
will then appear automatically. Do not enter them by hand.
As for the range, change I27:N27
to your range.
这篇关于如何计算excel中的特定单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!