Excel-Präfixe oder Suffixe

Question

Ich habe eine Spalte in Excel, die eine durch Kommas getrennte Liste enthält:

Header
1, 61
61
1, 61, 161
5, 55

Ich möchte diese Daten extrahieren, damit ich die Vorkommen der einzelnen Elemente zählen kann und so die folgenden Ergebnisse erhalte:

Count of Items
1    |    2
5    |    1
55   |    1
61   |    3
161  |    1

Ich habe countif mit „*“ versucht, aber es ist ein Chaos, weil ich in diesem Fall Präfixe oder Suffixe habe (1,61,161).

BITTE HELFEN Sie!

Answer 1

Option 1:

Ich möchte UDF (User Defined Function) vorschlagen, die nicht nur das Vorkommen von durch Kommas getrennten Zahlen zählt, sondern auch Text.

Wie es funktioniert:

• Drücken SieAlt+F11um VB-Editor dann zu bekommenKopierenundPastedieser Code alsModul.

  Option Explicit
  
  Function CountOccurrence(SearchRange As Range, Phrase As String) As Long
  
  Dim RE As Object, MC As Object
  Dim sPat As String
  Dim V As Variant
  Dim I As Long, J As Long
  
  V = SearchRange
  
  Set RE = CreateObject("vbscript.regexp")
  With RE
  .Global = True
  .MultiLine = True
  .ignorecase = True
  .Pattern = "(?:^|,\s*)" & Phrase & "(?:\s*,|$)"
  End With
  
  For I = 1 To UBound(V, 1)
  If RE.test(V(I, 1)) Then J = J + 1
  Next I
  
  CountOccurrence = J
  
  End Function
  

• Geben Sie die Kriterien im Bereich ein H18:H26, geben Sie dann diese Formel in die Zelle ein I18und füllen Sie sie aus.

=CountOccurrence($G$18:$G$24,H18)

Option 2:

Geben Sie diese Formel in die Zelle ein I18und füllen Sie sie aus.

=SUMPRODUCT(--ISNUMBER(FIND(H18,$G$18:$G$24)))

Passen Sie die Zellbezüge nach Bedarf an.

Answer 2

1. Zuerst müssen Sie Ihre Zahlen auf einzelne Zellen aufteilen:
   • Wählen Sie Ihre Daten aus
   • Wählen Sie auf der Registerkarte „Daten“ „In Spalten aufteilen“ aus.
   • Wählen Sie "getrennt", dann
   • Datenauswahl erfolgt durch "Komma" getrennt, Fertig
2. jetzt können Sie ZÄHLENWENN verwenden, z. B.
   =COUNTIF($A$2:$C$5,E2)

Answer 3

Es sieht so aus, als hätten Sie bereits eine Lösung, aber ich werde eine Nicht-VBA-Lösung einbauen, die dynamische Daten verarbeitet. Sie verwendet einige Hilfsspalten, die Sie bis zu einem beliebig großen Bereich vorab auffüllen können. Wenn keine zugehörigen Daten vorhanden sind, sind die Zellen leer. Einige Hilfsspalten könnten weggelassen werden; sie sind enthalten, um Wiederholungen zu minimieren, aber alle Hilfsspalten können ausgeblendet werden.

Ihre Daten stehen in Spalte A. Spalte B bestimmt anhand der Anzahl der Kommas die Anzahl der Werte in jedem Eintrag. Die Formel in B3 lautet:

=IF(ISBLANK(A3),"",LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1)

Spalte C enthält lediglich die kumulierte Komponentenanzahl aus Spalte B. C2 wird als eingegeben 0. Die Formel in C3 lautet:

=IF(ISBLANK(A3),"",SUM(B3:B$3))

Füllen Sie die Spalten B und C mit so vielen Zeilen, wie Sie Daten benötigen. Sie können diese Spalten bei Bedarf jederzeit erweitern.

Spalte E dient nur der Vereinfachung. Sie stellt einen Index für die analysierten Werte bereit. Sie könnten die hart codieren 1und dann für jede nachfolgende Zeile 1 hinzufügen. Ohne guten Grund habe ich sie auf der Zeilennummer basieren lassen (die -2in der folgenden Formel dient dazu, die Werte so anzupassen, dass sie bei beginnen 1). Zellen, die über die Gesamtzahl der Werte hinausgehen, werden leer gelassen. Meine Formel in E3:

=IF(ROW()-2>MAX($C$2:$C$10),"",ROW()-2)

Spalte F dient lediglich dazu, Formelwiederholungen zu vermeiden. Sie zieht den relevanten Eintrag in Spalte A, aus dem die aktuelle Komponente analysiert wird. Die Formel in F3 lautet:

=IFERROR(OFFSET($A$2,MATCH(E3-1,$C$2:$C$10,1),0),"")

Der entsprechende Eintrag wird ermittelt, indem die Komponentennummer in Spalte E mit der kumulierten Komponentenanzahl in Spalte C verglichen wird.

Spalte G enthält die analysierten Komponentenwerte, alle in einer einzigen zusammenhängenden Spalte, mit der man leicht arbeiten kann. Die Formel in G3 lautet:

=IFERROR(TRIM(MID(SUBSTITUTE(F3,",",REPT(" ",LEN(F3))),(E3-INDEX($C$2:$C$10,MATCH(E3-1,$C$2:$C$10,1))-1)*LEN(F3)+1,LEN(F3))),"")

Dadurch wird bestimmt, welches Element aus dem Eintrag in Spalte F analysiert werden soll, indem die kumulierte Elementanzahl des letzten „abgeschlossenen“ Eingabedatensatzes von der aktuellen Elementnummer abgezogen wird.

Die Spalten E bis G sollten auf genügend Zeilen verteilt werden, um die erwartete Anzahl von Komponentenwerten abzudecken (mindestens ein Mehrfaches der Anzahl der Datenzeilen). Beachten Sie, dass alle oben aufgeführten Formeln, die sich auf den Bereich $C$2:$C$10 beziehen, angepasst werden sollten, um den gesamten Bereich Ihrer Daten abzudecken.

Jetzt, da Sie alle analysierten Elemente in einer schönen Spalte haben, gibt es verschiedene Möglichkeiten, sie zu aggregieren und die Anzahl zu ermitteln. Ich habe eine Pivot-Tabelle verwendet, die Ihnen gleichzeitig auch die Liste der eindeutigen Werte liefert.

Wählen Sie den vollständigen vorab ausgefüllten Bereich der Spalte G für die Pivot-Tabelle aus. Verwenden Sie dieses Feld für das Zeilenfenster und das Wertefenster (wählen Sie „Anzahl“ als Aggregation aus). Der Bereich enthält Leerzeichen in den nicht verwendeten Zeilen. Verwenden Sie daher den integrierten Filter, um Leerzeichen abzuwählen.

Wenn sich die Daten ändern, aktualisieren Sie einfach die Pivot-Tabelle und überprüfen Sie, ob im Filter alle neuen Elementwerte ausgewählt sind.