Wie zähle ich die Anzahl der Vorkommen einer bestimmten Zahlenfolge innerhalb einer Spalte?

Question

Das wird ein bisschen seltsam, ich habe eine Spalte mit 750 Zeilen, die mit ganzen Zahlen von 1-10 gefüllt sind. Ich versuche, diese Daten alsSerie von 3-reihigen Sequenzen, Undzählendie Anzahl der Vorkommen für jede Sequenz, wie im folgenden Screenshot gezeigt:

Versuch, 3 Zellsequenzen in Excel zu finden und zu zählen. Spalte A ist die Beobachtungsspalte mit ganzzahligen Werten von 1-3. Spalte I ist eine Auflistung aller beobachteten 3-Wert-Sequenzen und Spalte J ist die Anzahl der Vorkommen, bei denen jede dieser Sequenzen beobachtet wurde.

Spalte A ist die Beobachtungsspalte, mit ganzzahligen Werten von 1-3 für dieses Beispiel. Spalte I ist eine Auflistung aller beobachteten 3-Wert-Sequenzen und Spalte J ist die Anzahl der Vorkommen, die jede dieser Sequenzen beobachtet hat. Spalte I wird als Textwert angezeigt, aber es wäre besser, diese eine Spalte in 3 separate Spalten umzuwandeln; eine für jeden Wert in der Sequenz.

Ich versuche dies als Schritt, um die Beobachtungsmatrix einer Markow-Kette zweiter Ordnung zu erstellen. In einer früheren Version brauchte ich nur eine Matrix erster Ordnung, die aus zwei Wertesequenzen bestand. Dies habe ich erreicht, indem ich 100 Spalten erstellt habe; eine für jede mögliche Kombination. Dann habe ich in jeder Zeile jeder dieser Spalten die Zelle den beobachteten Wert (Spalte A) für diese Zeile und die Zeile darüber betrachten lassen, und wenn die Sequenz mit der Sequenz für diese Spalte übereinstimmte, gab sie eine 1 aus. Am Ende habe ich jede Spalte summiert und diese Informationen verwendet, um die Zählungen für die Beobachtungsmatrix zu generieren.

Ich habe versucht, dies mithilfe von Zellfunktionen als riesiges Raster aller möglichen Kombinationen zu schreiben, aber es wurde schnell klar, dass dieser Ansatz nicht funktionieren würde; 1000 Spalten mit 750 Zeilen stellen ein Rechenproblem dar. Mir fällt ein, dass es eine einfache Möglichkeit dafür mit VBA geben könnte, aber ich bin mir nicht sicher, ob das überhaupt möglich ist. Ich habe angefangen, mir das selbst beizubringen, aber es gibt immer noch vieles, was ich nicht weiß. Ist das überhaupt möglich oder verschwende ich meine Zeit?

Ich brauche zwei Ausgaben: Ich brauche die Liste aller beobachteten Sequenzen. Die Ganzzahlen können zwischen 1 und 10 liegen, aber nicht alle 10 oder alle Kombinationen von 10 dürfen vorhanden sein. Ich brauche keine Kombinationen, die nicht vorkommen. Ich muss auch wissen, wie oft jede Sequenz beobachtet wird.

Ich führe dies auf einem Windows 7-PC mit Microsoft Excel 1010 aus. Ich verwende Microsoft Excel, weil es das einzige Mathematikprogramm ist, das ich habe, und ich damit am besten zurechtkomme.

Answer 1

Sie brauchen Excel nicht. Versuchen Sie zunächstdieses Online-Ngram-Analysetool.

Versuchen Sie, in das Textfeld einzugeben 8 3 4 3 1 7 8 3 8 3 8. Wählen Sie aus Using Frequencyund zeigen Sie an, trigramsdass dies mindestens oneeinmal vorkommt.

Senden Sie es ab, dann erhalten Sie eine Liste von Trigrammen zusammen mit ihren Häufigkeiten. Ignorieren Sie einfach die Zeilen mit nur einer oder zwei Zahlen.

---

Wenn Sie dieses Verhalten dynamisch und programmgesteuert benötigen, kann ich Ihnen bei der Erstellung eines Skripts helfen, das genau diese Berechnung basierend auf der Benutzereingabe durchführt.

Answer 2

Ich konnte nicht anders, als eine Lösung dafür zu finden. Ich habe stattdessen R verwendet, weil es einfach Sinn macht. Der Code ist unten und auch hier verfügbarR-Geige

Bitte beachten Sie, dass der folgende Code einen Abschnitt zum Generieren von Mock-Daten enthält. In der Praxis müssen Sie diesen durch Ihre tatsächlichen Daten ersetzen, die in einem Vektor gespeichert werden, der xwie im Code erläutert aufgerufen wird.

Wenn Sie sich nicht für die Beobachtungen interessieren, die nicht auftreten, ist der Code sehr, sehr einfach:

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)

Die Ausgabe sieht dann ungefähr so ​​aus:

    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

Wenn Sie alle Möglichkeiten anzeigen möchten, ist der Code etwas chaotischer:

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)

Die Ausgabe sieht ungefähr so ​​aus:

         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

Verwenden Sie eine Hilfsspalte, die die Daten in Dreiergruppen verkettet, und verwenden Sie dann a) „Zählenwenn“, um die Sequenzen zu zählen, oder b) verwenden Sie eine Pivot-Tabelle.

In Zelle B2 einfügen =CONCATENATE(A2,",",A3,",",A4)und nach unten ziehen (Doppelklick auf die untere rechte Ecke)

Zählenwenn-Methode

Wenn Sie dann =COUNTIF(B:B,I2)J2 einsetzen, erhalten Sie die Gesamtsummen wie unten.

wenn Ihnen die Nullen nicht gefallen, verwenden Sie einfach den automatischen Filter. Ich kann mir allerdings vorstellen, dass Sie einen größeren Datensatz als diesen verwenden und wahrscheinlich keine haben werden.

Pivottabelle

Eine fortgeschrittenere und meiner Meinung nach elegantere Lösung wäre die Verwendung einer Pivot-Tabelle. Verwenden Sie in Spalte B dieselbe Formel.

Fügen Sie eine Pivot-Tabelle basierend auf der Tabelle in den Spalten A und B ein. Mit „ZEILENBESCHRIFTEN“ als Spalte B und Werten als ANZAHL (nicht Summe) von Spalte B.

Sie müssen die zu zählenden Sequenzen nicht eintippen, Excel findet alles automatisch in Spalte B.

Außerdem ist es eine verallgemeinerte Lösung für Sequenzen beliebiger Länge und beliebiger Anzahl verwendeter Ziffern (fügen Sie Ihrer Verkettung in Spalte B einfach weitere Zellen hinzu). Auch die Suche nach 5-stelligen Sequenzen in den Daten ist beispielsweise möglich:

1
2
3
4
5
5
4
3
2
1

100 Zeilen lang wiederholt ergibt:

Stück Kuchen.