识别发生特定模式更改的行 [英] Identify rows where a specific pattern change occurs

问题描述

我需要分析spss数据表中的响应者数据，但是对于我将要描述的特定分析，我认为最好将这些数据复制到excel或google工作表中.

I am required to analyse respondent data in an spss data sheet but i believe it may be best to copy this data into excel or google sheets for this particular analysis I am about to describe.

600多名参与者回答100个问题.参与者将在数据中各占一行，并且他们对问题的回答将在其各自行中从左到右的100列中.每个响应将被编码为1或2(这是一个二进制响应，每个列/单元格中仅提供这两个选项之一).

600+ participants have been asked to respond to 100 questions. The participants will each have their own row in the data and their response to the questions will be in 100 columns from left to right in their respective row. Each response will either be coded as 1 or 2 (it is a binary response with only one of these two options to be provided in each column/cell).

数据中应该有一个趋势(从左到右)，所有参与者将从该点开始从严格响应1变为严格响应2的一个点(即列/单元格).如果参与者注意并遵循逻辑，那么他们将在其余回答中仅回答2.

There should be a trend in the data (left to right) where all participants will reach a point (i.e. column/cell) where they change from strictly responding with a 1 to strictly responding with a 2 from that point onwards. If the participants are paying attention and following logic, they will then only answer 2 for the remainder of their responses.

我需要确定所有未未严格回答的参与者，切换到首先提供2个回答而不是严格1个回答之后.

I need to identify all participants that did not strictly continue answering with a 2 response after switching over to first providing a 2 response instead of strictly 1 responses.

即如果有人回答1、1、1、1、1、1，然后切换为2、2、2、2、2，但又切换回1，而不是针对其余问题继续回答2，我需要确定这些离群值，以便可以将其与其余数据集隔离.

i.e. if someone answers 1, 1, 1, 1, 1, 1 then switches to 2, 2, 2, 2, 2, but switches back to 1 instead of continuing to answer 2 for the remainder of the questions, i need to identify these outliers so that they can be isolated from the rest of the data-set.

有人知道某个功能(在excel，google工作表中还是在您选择的平台中)，该功能可以让我批量确定哪些参与者在将其回答更改为2后错误地输入了1.

Does anyone know of a function (either in excel, google sheets or a platform of your choice) that will allow me to bulk identify which participants erroneously entered a 1 after first changing their response to 2's.

我希望所有这一切都是有意义的，如果这有点儿混乱的话，我对此感到抱歉.

I hope all that makes sense and sorry if it was a bit of a ramble.

推荐答案

您可以为每行执行 TEXTJOIN()并查找模式"21"(即，'2').如果找到，则为大纲轮廓.

You can do a TEXTJOIN() for each row and look for the pattern "21" (i.e. an '1' after a '2'). If found, this is an outliner.

类似于以下内容:

=IF(ISERROR(FIND("21",TEXTJOIN("",TRUE,[data range of a row]))),FALSE,TRUE)

TRUE将是轮廓线

这篇关于识别发生特定模式更改的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！