列内の特定の数字のシーケンスの出現回数をカウントするにはどうすればよいですか?

Question

これはちょっと奇妙ですが、1から10までの整数が入力された750行の列があります。そのデータを3行のシーケンスのシリーズ、 そしてカウント次のスクリーンショットに示すように、各シーケンスの発生回数は次のとおりです。

Excel で 3 つのセル シーケンスを見つけて数えようとしています。列 A は観測列で、1 ～ 3 の整数値を持ちます。列 I は観測されたすべての 3 値シーケンスのリストで、列 J は各シーケンスが観測された発生回数です。

列 A は観測列で、この例では 1 ～ 3 の整数値です。列 I は観測されたすべての 3 値シーケンスのリストで、列 J は各シーケンスが観測された発生回数です。列 I はテキスト値として表示されますが、その 1 つの列を 3 つの個別の列 (シーケンス内の各値に 1 つずつ) にするとより適切です。

私はこれを、2 次マルコフ連鎖の観測マトリックスを作成するためのステップとして試みています。以前のバージョンでは、2 つの値シーケンスで構成される 1 次マトリックスのみが必要でした。私は、すべての可能な組み合わせに 1 つずつ、合計 100 列を作成することでこれを実現しました。次に、それらの各列のすべての行で、セルにその行とその上の行の観測値 (列 A) を調べさせ、シーケンスがその列のシーケンスと一致した場合は 1 を出力させました。最後に、各列を合計し、その情報を使用して観測マトリックスのカウントを生成しました。

セル関数を使用して、すべての可能な組み合わせの巨大なグリッドとしてこれを書き出そうとしましたが、このアプローチは機能しないことがすぐに明らかになりました。750 行の 1000 列では計算上の問題が発生します。これを行う簡単な方法は VBA である可能性があると思いますが、それが可能かどうかはわかりません。独学を始めましたが、まだわからないことがたくさんあります。それは可能なのでしょうか、それとも時間の無駄でしょうか?

必要な出力は 2 つあります。観測されたすべてのシーケンスのリストが必要です。整数は 1 から 10 までですが、10 個すべてではないか、10 個の組み合わせがすべて存在する可能性があります。発生しない組み合わせは必要ありません。また、各シーケンスが観測される回数も知る必要があります。

私はこれを、Microsoft Excel 1010 を使用して Windows 7 PC で実行しています。Microsoft Excel を使用するのは、これが私が持っている唯一の数学プログラムであり、最も使い慣れているプログラムだからです。

Answer 1

Excelは必要ありません。まずは試してみてくださいこのオンラインNグラム解析ツール。

テキスト フィールドに と入力してみます8 3 4 3 1 7 8 3 8 3 8。 を選択しUsing Frequency、trigramsが少なくともone回発生することを示します。

送信すると、トリグラムのリストとその頻度が表示されます。数字が 1 つまたは 2 つしかない行は無視してください。

---

この動作を動的かつプログラム的に実行する必要がある場合は、ユーザー入力に基づいてまさにこの計算を実行するスクリプトの作成をお手伝いできます。

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 が気に入らない場合は、自動フィルターを使用してください。ただし、これよりも大きなデータセットを使用することになり、おそらく 0 は存在しないと思います。

ピボットテーブル

より高度で、私見ではよりエレガントな解決策は、ピボットテーブルを使用することです。列 B で同じ数式を使用します。

列 A と列 B のテーブルに基づいてピボットテーブルを挿入します。列 B として「ROW LABELS」を使用し、列 B の COUNT (合計ではない) として値を使用します。

カウントするシーケンスを入力する必要はありません。Excel は列 B のすべてを自動的に検索します。

また、これは、シーケンスの長さや使用される桁数に関係なく、汎用的なソリューションです (列 B の連結にセルを追加するだけです)。また、たとえば、データ内の 5 桁のシーケンスを検索する場合は、次のようになります。

1
2
3
4
5
5
4
3
2
1

100 行繰り返すと次のようになります。

簡単ですよ。