Ich habe eine Tabelle, die sowohl eine Zahl als auch einen Text in einer Zelle speichert, und ich möchte eine Rechenoperation basierend auf den Zahlen durchführen. Meine Tabelle sieht also folgendermaßen aus:
Number1 | Number2 | Product
2 | 3 | 6
3 (some text) | 40 | 120
4 | 5 (here too) | 20
Angesichts dieser hässlichen Texte möchte ich trotzdem in der Lage sein, Spalte 3 automatisch zu erstellen. Ich glaube, ich brauche eine Funktion, die die Teilzeichenfolge bis zum ersten nicht numerischen Zeichen in eine Zahl umwandelt. Kann ich das mit Excel machen?
PS:Die Zahlen können mehr als 1 Ziffern haben. Man kann davon ausgehen, dass es sich um Ganzzahlen handelt, aber es wäre schön, eine Lösung zu sehen, die auch für Gleitkommazahlen gilt :)
Danke,
Antwort1
Um die Zahlen abzurufen, wenn sie am Anfang der Zeichenfolge stehen, können wir Folgendes verwenden:
=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))
als unsere Basisformel. Dies findet das Ende der Zahl und gibt es als Ende der MID()-Funktion zurück.
Hier ist einiges los:
SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))Während dieser Teil die Zahlen durchläuft, ersetzt er die letzte Instanz jeder Zahl durch }}}.
Das dritte Kriterium von SUBSTITUTE ist die Instanz. Wir finden die Anzahl der Instanzen mit LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")). Es durchläuft die Zahlen und ersetzt jede nach der anderen durch nichts. Anschließend findet es den Längenunterschied zwischen der ursprünglichen Zeichenfolge und der neuen.
Wenn es also im Fall von A2 zu iteriert, 2findet es 2 und der äußere Ersatz ersetzt den letzten durch }}}. Dies ist nur ein temporärer Platzhalter.
Die Aggregatfunktion ist eine Multifunktionsfunktion. Die 14 sagt den Funktionen, dass wir die Large()Funktion verwenden. Die 6sagt der Funktion, dass Fehler ignoriert werden sollen. Das ist wichtig, da viele Iterationen nichts finden und einen Fehler zurückgeben.
Mit dem 1am Ende wird der Funktion mitgeteilt, dass wir die höchste Rendite von der Suchfunktion wünschen, die nach den temporären Werten sucht, }}}die durch Iteration auf der letzten Instanz jeder Zahl platziert werden.
Das Aggregat gibt also die maximal gefundene Zahl zurück. Diese übergeben wir an das Längenkriterium in der Mid-Funktion.
Damit haben wir nun die Zahl am Anfang der Zeichenfolge gefunden.
Wir können also zwei davon miteinander multiplizieren, um die gewünschte Ausgabe zu erhalten (jede mathematische Funktion wandelt die zurückgegebene Zeichenfolge in eine Zahl um):
=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))*MID(B2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))),1))
Eine EinschränkungDie Aggregatfunktion wurde in Excel 2010 eingeführt. Sie funktioniert möglicherweise nicht mit älteren Versionen.
Wenn Sie eine ältere Version haben, müssen Sie diese längere Formel verwenden:
=MID(A2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))))))*MID(B2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))))))
Es macht ungefähr dasselbe wie das oben genannte, außer dass es zuerst auf Fehler testen muss, bevor das Maximum ermittelt wird.
Antwort2
Vorausgesetzt, die Zahl ist immer durch ein Leerzeichen von jedem Text getrennt, könnten Sie so etwas verwenden. Angenommen, Ihr Beispiel enthält Daten, die mit A2 beginnen, dann würde Ihr erstes Ergebnis in C2 normalerweise so aussehen:
=A2*B2
Wir müssen die einfachen Zellbezüge durch eine Formel ersetzen, die die Quellzelle als Zahl behandelt, wenn es sich nur um eine Zahl handelt, die Zahl jedoch extrahiert, wenn auch Text vorhanden ist. Aus C2 würde also Folgendes werden:
=IF(ISNUMBER(A2),A2,LEFT(A2,FIND(" ",A2)-1)) * IF(ISNUMBER(B2),B2,LEFT(B2,FIND(" ",B2)-1))
Wenn die Quellzelle keine Zahl ist, sucht es nach dem ersten Leerzeichen und gibt die Zeichen davor zurück. Technisch gesehen wird ein Textwert zurückgegeben, der Ziffern enthält, aber Excel ist intelligent genug, um ihn bei der Berechnung des Produkts als Zahl zu behandeln (es ist nicht nötig, ihn in eine andere Funktion einzubetten, um ihn in eine Zahl umzuwandeln). Und beachten Sie, dass dies auch funktioniert, wenn es sich bei der Zahl um eine Gleitkommazahl handelt.
Vorbehalt: Dies setzt „saubere“ Quelldaten voraus, also entweder einen numerischen Eintrag oder einen Texteintrag, der mit einer Zahl beginnt, auf die ein Leerzeichen folgt. Wenn eine Zelle vorhanden ist, die dem nicht entspricht, müssen Sie entweder die Daten bereinigen oder eine Art Fehlerprüfung einbauen. Die beste Lösung wäre, die beiden Datenspalten aufzuteilen, sodass sich jeglicher Text in einer separaten Zelle befindet.