问题描述
我想知道是否可以创建一个计算列,该列将计算连续有多少个相同值的单元格(即彼此相邻),以便我可以将其除以该列中的单元格总数。 (我仅在该列中查看1和0。在此方面我一直for不休,任何帮助或建议都将不胜感激,
I am wondering if it is possible to created a calculated column that will count how many cells of the same value are successive (i.e., next to each other) so that I can divide it by the total number of cells in that column. (I'm only looking at 1's and 0's in the column. Any help or advice would be greatly appreciated as I've been scratching my head over this for a while,
列底部的百分比仅是=(1接触另外1的数量)/(总行数)->我的最终目标
The percentages at the bottom of the columns is simply = (the number of 1's touching another 1) / (total # of rows) --> my end goal
推荐答案
好吧,这必须分几步完成。
Ok this has to be done in a few steps.
- 插入计算列
RowId()
并将其命名为rid
- 使用此表达式插入计算列,并将其命名为[TouchingA]:
- Insert a calculated column
RowId()
and name itrid
- Insert a calculated column with this expression, and name it [TouchingA]:
If(([[Location A] = 1)和(([[Location A] = First([Location A])] over
(Next([rid]))))或([[Location A] = First([Location A])over
(Previous([rid]))))),1,0)
- 插入计算表达式,并将其命名为[TouchingB]:
If(([[Site B] = 1)和(([[Site B] = First([Site B])over(Next([rid]))))或
([Site B] = First([Site B])over(Previous([摆脱])))),1,0)
此时,您的数据将如下所示:
At this point, your data will look like this:
接下来,插入交叉表
并设置水平轴= (列名称)
,垂直轴= (无)
和单元格值= Sum([TouchingA])/ Max([rid])为[A Percent],
Sum([TouchingB])/ Max([rid])作为[B Percent]
Next, insert a CROSS TABLE
and set the Horizontal Axis = (Column Names)
, the Vertical Axis = (None)
, and the Cell Values = Sum([TouchingA]) / Max([rid]) as [A Percent], Sum([TouchingB]) / Max([rid]) as [B Percent]
这将向您显示所需的值,如下所示。当然,您可以使用适当的IF或CASE语句在文本区域或其他任何所需的位置将其设置为计算值。最后,您可以更改格式以将值设置为%对十进制。
This will show you the values you want, like below. Of course you can sett his in a calculated value in a text area or anywhere else you want with the proper IF or CASE statement. Lastly, you can change the formatting to make the value a % versus decimal.
这篇关于Spotfire中相同值的连续单元数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!