只返回一行中的值 [英] return only values in a row
问题描述
是否有一种方法可以在单独的工作表/位置上按照他们显示的顺序(从左到右)只返回值?例如,我想要返回ID'1002'的值为35,32,44 ..而不是空白。我将把这些值转换成一个Sparkline。
我使用的是excel 2010,如果这有所作为。
谢谢!
Sam
尝试此公式:
= SUBSTITUTE(TRIM(B2&&C2&D2)& )
编辑:
_______________________________________________________________________________
希望这不是太晚才能回答您的问题。
假设您的数据位于 Sheet1
中,如下图所示:
现在,在单元格 B2
中 Sheet2
输入以下公式:
= IFERROR(INDEX(Sheet1!$ B2:Sheet1!$ F2,SMALL(IF(ISBLANK Sheet1!$ B2:Sheet1!$ F2),,COLUMN(Sheet1!$ B2:Sheet1!$ F2)-MIN(COLUMN(Sheet1!$ B2:Sheet1!$ F2))+ 1),COLUMN (A1))),)
这是一个数组公式,因此按 Ctrl + Shift + 输入
将此公式拖动到列F
,直到第4行
或根据需要。这将给你以下结果。
I猜猜这是你要找的。 p>
I have a list of unique ids and values on one sheet in excel as in the image below:
Is there a way to return only the values in the order they appear (left to right) on a separate sheet/location? For example, I would want to return for ID '1002' the values 35,32,44.. not the blanks. I am then going to turn those values into a Sparkline.
I am using excel 2010, if that makes a difference.
Thanks! Sam
Try this formula:
=SUBSTITUTE(TRIM(B2 & " " & C2 & " " & D2 & " " & E2 & " " & F2)," ",",")
EDIT:
_______________________________________________________________________________
Hope this is not too late to answer your question.
Lets assume your data is in Sheet1
as in the image below:
Now, in Cell B2
of Sheet2
enter the following formula:
=IFERROR(INDEX(Sheet1!$B2:Sheet1!$F2, SMALL(IF(ISBLANK(Sheet1!$B2:Sheet1!$F2), "", COLUMN(Sheet1!$B2:Sheet1!$F2)-MIN(COLUMN(Sheet1!$B2:Sheet1!$F2))+1), COLUMN(A1))),"")
This is an array formula so commit it by pressing Ctrl+Shift+Enter
Drag this formula across till Column F
and down till Row 4
or as required. This will give you following result.
I guess this is what you are looking for.
这篇关于只返回一行中的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!