Ich habe eine Tabelle mit Pflanzen, Mengen und Preisen. Aus irgendeinem Grund haben die meisten Preise führende Leerzeichen, die meiner Meinung nach den Fehler #VALUE verursachen, wenn ich versuche, einen Aufschlagsmultiplikator anzuwenden.
Ich habe Trimmen, Trim(Clean), Inhalte kopieren/einfügen und Suchen/Ersetzen ausprobiert, aber nichts funktioniert – am Ende habe ich immer noch Zahlen mit führenden Leerzeichen.
Ich habe sogar in eine CSV-Datei exportiert und dann wieder in Excel importiert, weil ich dachte, dass irgendeine Formatierung Probleme verursacht.
Was zum Teufel? Und gibt es eine Möglichkeit, einen Abschnitt meines Blattes zur Überprüfung zu veröffentlichen?
Antwort1
Ihre Zahlen enthalten verschiedene Arten von Leerzeichen (geschützte Leerzeichen). Normalerweise sind dies CHAR(160). Weder TRIM noch CLEAN entfernen diese, da Excel sie als gültige Zeichen betrachtet. Dadurch wird jedoch die Textzeichenfolge „Zahlen“ erstellt, die als solche nicht multipliziert werden kann.
Daher verwenden wir SUBSTITUTE, um diese Zeichen zu entfernen, und fügen sicherheitshalber TRIM und CLEAN hinzu:
=TRIM(CLEAN(SUBSTITUTE(C2,CHAR(160),"")))*2.5
Antwort2
Sie könnten auf eine Menge "geschützte Leerzeichen", die nicht von verarbeitet werden CLEAN(). Dies ist sehr häufig der Fall, wenn Daten von Webseiten entnommen werden, da es hier, wie bei PDFs, nur um die visuelle Präsentation geht und nicht darum, dass jemand Daten für die weitere Verwendung abruft. Aber es kommt sehr häufig bei Programmen vor, die Daten ausgeben, insbesondere wenn sie nicht für die allgemeine Verwendung der Daten, sondern für eine begrenzte Anzahl von Benutzern gedacht sind. Oder es war vor 28 Jahren so, als die CSV-Ausgabefunktion geschrieben wurde, die nie aktualisiert wurde.
Das Problem lässt sich in Ihrer Tabelle leichter handhaben, da Sie buchstäblich nur den numerischen Teil benötigen und nichts, was keine numerische Ziffer ist.
Wenn Sie Indikatoren für negative Werte beibehalten müssten, wäre das schwieriger. Oder Zeichen, die alles andere als diese Leerzeichen sind. Oder, wie ich, Zeichenfolgen erhalten, deren Ausgabe diskrete Zahlen sein müssen. In einer Zeichenfolge wie „90.02 45.55 .062“, aber beibehalten, damit sie extrahiert werden können.
Sie benötigen jedoch lediglich die Ziffern in einer einzigen Zeichenfolge und die von Excel als Zahlen verarbeiteten Zahlen. Die folgende Formel erledigt das:
=VALUE(TEXTJOIN(,TRUE,IFERROR(MID(C1,SEQUENCE(1,LEN(C1)),1)*1,"")))
Es verwendet SEQUENCE(), um eine Reihe von Werten zu erhalten, die um 1 erhöht wird und so lang ist wie die Länge Ihres Ziels. Dann verwendet es diese, um den Startpunkt für die MID()Funktion anzugeben, die eine Matrix der Zeichen im Ziel ausgibt. Dann führt es eine mathematische Operation ( *1) darauf aus, die für die Ziffern in der Matrix funktioniert, aber Fehler für alles ausgibt, was keine Ziffern (0-9) ist. IFERROR()wandelt diese Zeichenfolge in Ziffern um, mit "" an allen Stellen, an denen die Fehler aufgetaucht sind (überall dort, wo das Ziel KEINE Ziffern hatte). TEXTJOIN()Dann wandelt es diese Matrix in eine einfache Ausgabezeichenfolge um und ignoriert diese ""-Elemente, sodass Sie jetzt eine brauchbare Zeichenfolge haben, die nur aus den Ziffern besteht, die im Ziel vorhanden waren. Sie wird als Text betrachtet und so angezeigt.
Im Moment ist die Formel für Sie nützlich, wenn Sie eine einigermaßen aktuelle Version von Excel haben. Das liegt daran, dass Excel eine Ziffernfolge, auch wenn sie als Text formatiert ist (sie sind schließlich nur formatiert, nicht grundlegend geändert), als genau das erkennt, nämlich Ziffern, wenn sie einer Funktion präsentiert wird, die nur numerische Daten als Eingabe ERFORDERT oder vernünftigerweise erwartet. Daher wird „C1+1“ diesen Text sogar als Zahl betrachten und die Addition durchführen, anstatt einen Fehler auszugeben oder 0+1=1 zu liefern.JEDOCH,nicht alle Funktionen behandeln die Ausgabe auf diese Weise. VLOOKUP()behandelt sie als das, was sie zu sein scheint, in diesem Fall als Text, und wenn man in einer Zahlentabelle nachschaut, findet man keine Übereinstimmung, was zu einem Fehler führt. Also habe ich das Obige VALUE()zum Abschluss so verpackt, dass das Ergebnis als Zahl betrachtet wird und es funktioniert, wenn man das Ergebnis als Eingabe für eine Nachschlagefunktion verwendet, wie es anscheinend jemand anderes tun würde.
Sie könnten diese Funktion also weglassen und die Markup-Multiplikation würde trotzdem wie gewünscht durchgeführt.
Wenn Sie jedoch nicht über die neuen SPILLFunktionen verfügen oder insbesondere eine relativ alte Excel-Version haben, funktioniert die folgende Formel in grundsätzlich allen Versionen von Excel für Windows und erzeugt immer eine numerische Ausgabe:
{=ROUND(NPV(-0.9,0,IFERROR(MID(L1,LEN(C1)-ROW(INDIRECT("C1:I"&LEN(C1)))+1,1)/100,"")),3)}
Beachten Sie, dass es sich um eineCSEFormel. Wird sie als normale Formel verwendet, entfernt sie manchmal die letzten beiden Ziffern ... und die Ausgabe wird als Währung formatiert. (Excel ist hilfreich ... denn es handelt sich NPV()um eine Finanzfunktion, die Sie UNBEDINGT als Währung formatiert haben möchten, oder?)
Die obige Formel stammt „irgendwo im Internet vor ziemlich langer Zeit“ … Ich wünschte, ich könnte die Quelle dafür angeben, aber ich erinnere mich nicht an die Quelle. Entschuldigung an einen hilfreichen Guru.
Ich bin froh, dass Sie das gefragt haben, denn ich habe das an vielen Stellen und musste es aktualisieren, um zu erfahren, wie man SEQUENCE()einem helfen kann. Ganz zu schweigen davon, die Änderung von der internen Matrix in der Funktion in die nützliche Zeichenfolge zu ersetzen, die ausgegeben wird, TEXTJOIN()da diese jetzt auch verfügbar ist! Sofortiger Nutzen! Es ist eine wunderbare Sache!