如何计算一列中特定数字序列出现的次数?

如何计算一列中特定数字序列出现的次数?

这有点奇怪,我有一列 750 行,填充了从 1 到 10 的整数。我试图将这些数据视为3 行序列, 和数数每个序列出现的次数如下面的屏幕截图所示:

尝试在 Excel 中查找和计算 3 个单元格序列。A 列是观察列,具有 1-3 的整数值。I 列是所有观察到的 3 值序列的列表,J 列是观察到的每个序列的出现次数

A 列是观察列,在本例中为 1-3 之间的整数值。I 列列出了所有观察到的 3 值序列,J 列是观察到的每个序列的出现次数。I 列显示为文本值,但最好将该列分成 3 个单独的列;每个列对应序列中的每个值。

我正尝试将此作为创建二阶马尔可夫链观察矩阵的步骤。在之前的版本中,我只需要一个由 2 个值序列组成的一阶矩阵。我通过创建 100 列来实现这一点;每个可能的组合一列。然后在每一列的每一行中,我让单元格查看该行和其上方行的观察值(A 列),如果序列与该列的序列匹配,它将输出 1。最后,我对每一列求和,并使用该信息生成观察矩阵的计数。

我尝试使用单元格函数将其写成包含所有可能组合的大型网格,但很快发现这种方法行不通;1000 列 750 行会带来计算问题。我突然想到可能有一个简单的方法可以用 vba 来实现,但我不确定这是否可行。我已经开始自学了,但还有很多我不知道的东西。这是否可行,还是我在浪费时间?

我需要两个输出:我需要所有观察到的序列的列表。整数可能在 1-10 之间,但不是全部 10,或者所有 10 的组合都可能存在。我不需要未出现的组合。我还需要知道每个序列被观察到的次数。

我在 Windows 7 PC 上使用 Microsoft Excel 1010 运行此程序。我使用 Microsoft Excel 是因为它是我唯一的数学程序,也是我最喜欢的数学程序。

答案1

你不需要 Excel。首先,尝试一下这个在线 ngram 分析工具

在文本字段中,尝试输入8 3 4 3 1 7 8 3 8 3 8。选择Using Frequency,并显示trigrams至少发生one次。

提交后,您将获得一个三元组列表及其频率。只需忽略只有一个或两个数字的行即可。


如果您需要动态和以编程方式实现此行为,我可以帮助您编写一个脚本,根据用户输入精确地执行此计算。

答案2

我忍不住想找到一个解决方案。我改用 R,因为它很有意义。代码如下,也可以在这里找到R-Fiddle

请注意,下面的代码有一个用于生成模拟数据的部分。实际上,您必须将其替换为实际数据,这些数据将存储在x代码中解释的名为的向量中。

如果您不关心未发生的观察,那么代码非常非常简单:

x <- c("01", "02", "03", "01", "02", "03", "01", "02 ", "03") # your Column A
n <- 3 # number of elements in each combination. configurable.

# create a vector with n-sized sequences of characters. (e.g. n = 3 -> "XX-YY-ZZ")
mydata <- x
for (i in 2:n) {
    y <- c(x[-i], x[i])
    mydata <- paste(mydata, y, sep="-")
}

# calculate the frequency of each observation and save into data table
frequencies <- data.frame(table(mydata))
head(frequencies)

输出将会类似如下内容:

    mydata Freq
1 01-02-02    2
2 01-04-04    2
3 01-05-05    1
4 01-07-07    1
5 01-10-10    1
6 02-02-02    1

如果你确实想展示所有的可能性,代码就会有点混乱:

n <- 3 # number of elements in each combination. configurable.

# -----------------------------------------------------------------------------------#
#            THIS PART SIMPLY GENERATES MOCK DATA. REPLACE WITH ACTUAL DATA          #
# -----------------------------------------------------------------------------------#
universe <- 1:10 # your range of numbers
m <- 100 # number of rows in the mock data

# generate some mock data with a simple m-sized vector of numbers within 'universe'
set.seed(1337) # hardcode random seed so mock data can be reproduced
x <- sample(universe, m, replace=TRUE)
x <- formatC(x, width=nchar(max(universe)), flag=0) # pad our data with 0s as needed
# -----------------------------------------------------------------------------------#
#                               END OF MOCK DATA PART                                #
# -----------------------------------------------------------------------------------#
# At this point, you should have a variable x which contains a sequence of
# numbers stored as characters (text) e.g. "01" "04" "10" "04" "06"

# create a vector with n-sized sequences of characters. (e.g. n = 3 -> "XX-YY-ZZ")
mydata <- x
for (i in 2:n) {
    y <- c(x[-i], x[i])
    mydata <- paste(mydata, y, sep="-")
}

# calculate the frequency of each observation and save into data table
frequencies <- data.frame(table(mydata))

# generate all possible permutations and save them to a data table called
p <- as.matrix(expand.grid(replicate(n, universe, simplify=FALSE)))
p <- formatC(p, width=nchar(max(universe)), flag=0)
q <- apply(p, 1, paste, collapse="-")
permutations <- data.frame(q, stringsAsFactors=FALSE) # broken into separate   step for nicer variable name in df
permutations$Freq <- 0 # fill with zeroes 
permutations$Freq[match(frequencies$mydata, permutations$q)] <- frequencies$Freq

head(permutations)

输出结果如下:

         q Freq
1 01-01-01    0
2 02-01-01    0
3 03-01-01    2
4 04-01-01    0
5 05-01-01    1
6 06-01-01    0

答案3

使用辅助列将数据以 3 组的形式连接起来,然后 a) 使用 countif 来计数序列。或 b) 使用数据透视表。

在单元格 B2 中放置=CONCATENATE(A2,",",A3,",",A4)并向下拖动(双击右下角)

countif 方法

然后输入=COUNTIF(B:B,I2)J2,您将得到总数,如下所示。

计数

如果你不喜欢 0,那么就自动过滤。不过我猜你会使用比这更大的数据集,而且可能没有任何数据集。

在此处输入图片描述

数据透视表

更高级、更优雅的解决方案是使用数据透视表。在 B 列中使用相同的公式。

根据 A 列和 B 列中的表插入数据透视表。其中“行标签”作为 B 列,值作为 B 列的计数(不是总和)。

在此处输入图片描述

在此处输入图片描述

您不必输入要计算的序列,Excel 会自动找到 B 列中的所有内容。

此外,它是适用于任何长度的序列和任何数量的数字的通用解决方案(只需在 B 列中添加更多单元格即可)。此外,例如,在数据中查找 5 位数字序列:

1
2
3
4
5
5
4
3
2
1

重复 100 行得到:

在此处输入图片描述

小菜一碟。

相关内容