열 내에서 특정 숫자 시퀀스의 발생 횟수를 어떻게 계산합니까?

Question

이것은 약간 이상할 것입니다. 1-10 범위의 정수로 채워진 750개 행의 열이 있습니다. 나는 그 데이터를 다음과 같이 보려고 노력하고 있습니다.일련의 3개 행 시퀀스, 그리고세다다음 스크린샷에 표시된 것처럼 각 시퀀스의 발생 횟수입니다.

Excel에서 3개의 셀 시퀀스를 찾고 계산하려고 합니다. A 열은 1~3의 정수 값을 포함하는 관찰 열입니다. I열은 관찰된 모든 3개 값 시퀀스의 목록이고, J열은 해당 시퀀스 각각이 관찰된 발생 횟수입니다.

A열은 이 예에서 1~3의 정수 값을 포함하는 관찰 열입니다. I열은 관찰된 모든 3개 값 시퀀스의 목록이고, J열은 각 시퀀스가 ​​관찰된 발생 횟수입니다. 열 I은 텍스트 값으로 표시되지만 해당 열 하나를 3개의 별도 열로 바꾸는 것이 더 좋습니다. 시퀀스의 각 값에 대해 하나씩.

2차 마르코프 체인의 관찰 행렬을 만들기 위한 단계로 이를 시도하고 있습니다. 이전 버전에서는 2개의 값 시퀀스로 구성된 1차 행렬만 필요했습니다. 나는 100개의 열을 만들어 이를 달성했습니다. 가능한 모든 조합에 대해 하나씩. 그런 다음 각 열의 모든 행에서 셀이 해당 행과 그 위의 행에 대해 관찰된 값(열 A)을 살펴보고 시퀀스가 ​​해당 열의 시퀀스와 일치하면 1을 출력하도록 했습니다. 결국 나는 각 열을 합산하고 해당 정보를 사용하여 관측 행렬의 개수를 생성했습니다.

나는 이것을 셀 함수를 사용하여 가능한 모든 조합의 대규모 그리드로 작성하려고 시도했지만 이 접근 방식이 작동하지 않는다는 것이 곧 명백해졌습니다. 750개 행의 1000개 열은 계산 문제를 야기합니다. VBA를 사용하여 이를 수행하는 간단한 방법이 있을 수 있다는 생각이 들었습니다. 그러나 그것이 가능한지 확실하지 않습니다. 저도 독학을 시작했지만 아직 모르는 것이 많습니다. 가능합니까, 아니면 시간을 낭비하고 있습니까?

필요한 출력은 두 가지입니다. 관찰된 모든 시퀀스 목록이 필요합니다. 정수는 1~10일 수 있지만 10이 전부는 아니거나 10의 모든 조합이 존재할 수 있습니다. 발생하지 않는 조합은 필요하지 않습니다. 또한 각 시퀀스가 ​​관찰된 횟수도 알아야 합니다.

나는 Microsoft Excel 1010을 사용하는 Windows 7 PC에서 이 프로그램을 실행하고 있습니다. Microsoft Excel은 제가 가지고 있는 유일한 수학 프로그램이고 가장 편안하기 때문에 사용하고 있습니다.

Answer 1

엑셀이 필요하지 않습니다. 시작하려면 다음을 시도하십시오.이 온라인 ngram 분석기 도구.

텍스트 필드에 를 입력해 보세요 8 3 4 3 1 7 8 3 8 3 8. 을 선택 Using Frequency하고 trigrams최소 one횟수 이상 발생하는 것을 표시합니다.

제출하면 주파수와 함께 트라이그램 목록이 제공됩니다. 하나 또는 두 개의 숫자만 포함된 줄은 무시하세요.

---

동적으로 프로그래밍 방식으로 이 동작이 필요한 경우 사용자 입력을 기반으로 정확하게 이 계산을 수행하는 스크립트를 만드는 데 도움을 드릴 수 있습니다.

Answer 2

이에 대한 해결책을 찾지 않을 수 없었습니다. 대신에 R을 사용했는데, 그 이유는 그것이 의미가 있기 때문입니다. 코드는 아래에 있으며 이 페이지에서도 사용할 수 있습니다.R-바이올린

아래 코드에는 모의 데이터를 생성하는 섹션이 있습니다. 실제로는 이를 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

Answer 3

3개의 그룹으로 데이터를 연결하는 도우미 열을 사용한 다음 a) countif를 사용하여 시퀀스 수를 계산합니다. 또는 b) 피벗 테이블을 사용합니다.

B2 셀에 놓고 =CONCATENATE(A2,",",A3,",",A4)아래로 드래그합니다(오른쪽 하단 모서리를 두 번 클릭).

countif 방법

그런 다음 =COUNTIF(B:B,I2)J2를 넣으면 아래와 같이 합계가 나옵니다.

0이 마음에 들지 않으면 자동 필터링하면 됩니다. 나는 당신이 이보다 더 큰 데이터 세트를 사용할 것이라고 상상하지만 아마도 아무 것도 없을 것입니다.

피벗 테이블

더 발전되고 더 우아한 솔루션은 피벗 테이블을 사용하는 것입니다. B열에 동일한 수식을 사용합니다.

열 A와 B의 테이블을 기반으로 피벗 테이블을 삽입합니다. "ROW LABELS"를 열 B로, 값을 열 B의 COUNT(합계 아님)로 사용합니다.

계산할 시퀀스를 입력할 필요가 없습니다. Excel은 B열의 모든 항목을 자동으로 찾습니다.

또한 이는 모든 길이의 시퀀스와 사용된 자릿수에 대한 일반화된 솔루션입니다(B열의 연결에 더 많은 셀을 추가하기만 하면 됩니다). 또한 예를 들어 데이터에서 5자리 시퀀스를 찾는 경우는 다음과 같습니다.

1
2
3
4
5
5
4
3
2
1

100개 행에 대해 반복하면 다음이 제공됩니다.

케이크 조각.