☰

Vorbehalt:

tag-icon • tag-icon google-spreadsheets
Vorbehalt:

Ich habe eine Reihe von Zellen, die einstellige Bewertungszahlen mit Text daneben enthalten (Bewertungen von 1 bis 9). Ich möchte für jede Zelle die Zahlen extrahieren und einen Durchschnitt berechnen. Ich kenne Funktionen wie Search und RegexExtract, aber ich verstehe nicht, wie ich sie verwenden kann, um alle Zahlen zu erhalten, da anscheinend nur die erste Übereinstimmung zurückgegeben wird.

Hier ist ein Beispiel für den Text in einer einzelnen Zelle:

A - 1
B - 5
C - 9
D -4
E -7
F -  2

Für den obigen Text möchte ich ein Array {1,5,9,4,7,2} erstellen und dieses an die Durchschnittsfunktion übergeben.

Ich verwende zwar Google Sheets, aber die meisten Standardfunktionen von Excel sollten genauso funktionieren. Danke.

Antwort1

Ich möchte eine Lösung in zwei Schritten vorschlagen, mit der sich das Problem beheben lässt:

Vorbehalt:

  • Die unten gezeigte Methode ist für Excel-Benutzer, da VBA für Google Sheet nicht funktioniert.
  • XLSXExcel-Dateien können in ein Dateiformat konvertiert werden , das für Google Sheet geeignet/bearbeitbar ist.
  • SUMPRODUCTfunktioniert auch mit Google Sheet.

Bildbeschreibung hier eingeben

Schritt 1:

  • Drücken Sie entwederAlt+F11 oderRKlicken Sie rechts aufSDrücken Sie die TAB-Taste und klicken Sie im eingeblendeten Menü aufVZeigen Sie den Code an, um den VB-Editor zu erhalten.

  • Copy undPaste diesen Code alsMModul, dann zurück zum Blatt.

    Public Function SplitNum(pWorkRng As Range, pIsNumber As Boolean) As String

Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
xStr = VBA.Mid(pWorkRng.Value, i, 1)
If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
    SplitNum = SplitNum + xStr
End If
 Next
End Function

  • Geben Sie diese Formel in die Zelle ein AI5:=SplitNum(AH1,TRUE)

Schritt 2:

  • Zu bekommenAGeben Sie diese Formel im VERAGE-Format in die Zelle ein AJ5.

    =SUMPRODUCT(--MID(AI5,ROW(INDIRECT("1:" & LEN(AI5))),1))/Len(AI5)

Benutzer von Google Sheets können diese Formel verwenden:

=AVERAGE(SPLIT(AH1,CONCATENATE(SPLIT(AH1,".0123456789"))))

Achtung -Passen Sie die Zellbezüge in der Formel nach Bedarf an.

Antwort2

Wenn Sie die Werte in Tabellen suchen AVERAGE, versuchen Sie (mit Ihren Daten in A1):

=average(split(trim(REGEXREPLACE(A1,"\D+"," "))," "))

Anstatt zu versuchen, jede einzelne Zahl zu extrahieren, entfernen wir lediglich die Nicht-Ziffern, teilen sie auf und berechnen den Durchschnitt. Manchmal ist es eine praktische Technik, Dinge zu entfernen, die wir nicht wollen, anstatt zu versuchen, das zu extrahieren, was wir wollen.

Bildbeschreibung hier eingeben

FürExcelBenutzer, wenn Sie Excel 2016+ mit der TEXTJOINFunktion haben, können Sie diese verwendenAnordnungFormel:

=AVERAGE(ABS(FILTERXML("<t><s>" & SUBSTITUTE(TEXTJOIN(" ",TRUE,A1:A6)," ","</s><s>") & "</s></t>","//s[number()=number()]")))

Da es sich um eine Matrixformel handelt, müssen Sie sie „bestätigen“, indem Sie ctrl+ gedrückt halten shiftund gleichzeitig drücken enter. Wenn Sie dies richtig machen, setzt Excel Klammern {...}um die Formel, wie in der Formelleiste zu sehen.

verwandte Informationen