Office: SVERWEIS mit Zahlen und Texten

Liebes Forum,

ich verzweifle mit Excel.

Ich arbeite mit großen Excel Files für meine Frühbestellung.
Dabei liegen mir verschiedene Artikelnummern vor, leider auch welche, die eine führende 0 haben, also zB. 9411 genauso wie 0814.
Um das bewerkstelligen zu können muss ich meine Spalte Artikelnr. als Zahlentyp Text führen, sonst klappt das nicht.
Jetzt möchte ich den Lagerstand der Inventur in meine Frühbestellung einfügen, um zu sehen ob ich diesen Artikel bestellen muss oder nicht.
Ich mache das mit SVERWEIS und dem Ausschluss ISTNV um 0 zu erhalten, wenn der Artikel in der Inventurtabelle nicht vorhanden ist.

Die ArtNr. in der Inventurliste sind auch als Text formatiert.

Das funktioniert eigentlich sehr gut.

Jedoch
Excel erkennt als Text eingegebene Zahlen und markiert sie mit dem grünen Pfeil links oben.
Wenn jetzt jemand da hin klickt und Excel sagt, er möge das in eine Zahl umwandeln, hat man für immer verloren. Selbst wenn man dann nochmal drüberfährt und als Zahlenformat wieder Text zuweist, funktioniert der SVERWEIS nicht mehr.

Gibt es eine Funktion die den Inhalt einer Zelle vergleicht, unabhängig vom Format, einfach nur den Inhalt Punkt für Punkt, und dann etwas zurückgibt was mir hilft den aktuellen Lagerstand zwei Spalten weiter einzufügen?

Zur Verdeutlichung eine Testtabelle angehängt die genau diesen Fehler macht.

Der Artikel in der 4ten Zeile ist in der Inventurliste nicht vorhanden -> korrekte Ausgabe des LagerIST somit 0
Die Artikel in den Zeilen 6 & 7 sind in der Inventurliste vorhanden, beide Zahlenformate sind Text, die Eingaben sind identisch, dennoch kommt fälschlicherweise in der Spalte LagerIST ein 0 statt des korrekt eingetragenen Lagerstands.

Excel Pro2016, sollte das relevant sein.

Bitte um Hilfe
Lg daBrain

 

Hallo,

wenn alles Text ist, das geht eigentlich schon. (s. Mappe)
Es könnte an der Art der Eingabe liegen. Wo kommen deine Eingaben denn her? Eingabe per Hand, Kopie & Paste, Internet oder sonst wo her?

Gruß Sigi

 

Bei mir geht es nicht...siehe Screenshot:


Screenshot Tab Inventur:


Die Daten stammen aus verschiedenen Quellen. Händische Eingabe, Copy/Paste aus vom Großhändler beigestellten Listen etc.
In der Testtab habe ich die Daten Copy/Paste aus meinen Arbeitssheets genommen, mit Werte / Format beibehalten um den Fehler reproduzieren zu können.

In Inventur!A4 erkennt Excel eine als Text eingegebene Zahl (grüner Pfeil links oben)
In In Inventur!A5 hat schon jemand den Vorschlag "In eine Zahl umwandeln" angewendet.
Trotzdem beide Zellen das Format Text haben, ist das Format für Excel unterschiedlich und der SVERWEIS funktioniert nicht.

Wenn ich jetzt in Inventur!A5 zur bestehenden Nr. hinten dran ein kleines "a" schreibe, Enter drücke und dann das "a" wieder lösche ist der grüne Pfeil in dieser Zelle auf einmal wieder da und in Frühbestellung! ist der richtige Inventurwert erschienen der vorher fälschlicherweise mit 0 weil #NV angegeben war.







Ich habe keinerlei Zahlenformate geändert...nur 1 a hinzugefügt und wieder gelöscht.
Excel dürfte hier etwas abspeichern was man nicht durch Änderung des Formats wegbekommt und daher der SVERWEIS nicht funktioniert.

LG

 

Hallo,

du hast viele Textformate in deiner Tabelle (Frühbestellung, s. Spalten J bis evtl. letzte Spalte,Zeilen 1 bis 1.048.576). Woher kommt das denn? Du musst es ja so formatiert haben, oder wer sonst?
Wenn deine Artikel-Nr alle aus Ziffern (0-9) bestehen, würde ich immer numerisch bei SVerweis() arbeiten. Dafür reicht es bei Kopieren anschließend immer "Inhalte einfügen", "Werte" zu benutzen, damit die Textformate außen vor bleiben.

Gruß Sigi

 

Ja, ich habe das so formatiert. Wobei diese ganzen Spalten nicht als Quelle für Verweise dienen, somit dessen Format irrelevant sein müsste.

Wenn ich Inhalte einfüge, dann immer nur mit Übernahme der Werte.
Ich habe das mit Werte & Formatierung jetzt nur gemacht, um den Fehler reproduzieren zu können.
Tritt der Fehler bei dir auch auf, wenn du meine Testtab öffnest?

Meine Artikelnrn. bestehen je Großhändler mal aus nur Ziffern, mal aus einer Kombination mit Buchstaben.
Da führende Nullen auch dabei sind, bleibt mir nur die Option des Zahlenformats Text, sonst ist die Null weg und die Artikelnr. somit falsch.

Fakt ist aber, dass sowohl in Frühbestellung!D6 als auch in Inventur!A6 1:1 die selbe Zeichenfolge steht. Offensichtlich ist auch das Zellformat gleich. Dennoch schafft es Excel nicht, den SVERWEIS korrekt auszuführen.

Der Fehler ist reproduzierbar und liegt offensichtlich am Formattyp, den ich aber nicht ändern kann - Text ist ja schon beides, Zahl geht nicht. Auch wenn ich das Format auf Standard ändere, bleibt das falsche Ergebnis des SVERWEIS.

Excel schreibt in seiner Hilfe dass SVERWEIS Probleme mit Ziffern & Text hat.
Darum meine Frage, gibt es einen anderen Befehl der wirklich 1:1 vergleicht, unabhängig des Formats.
Oder gibt es eine Formatierung, die führende 0en nicht löscht und mit der der SVerweis dauerhaft funktioniert? Ohne das Excel darauf hinweist dass es eine in Text formatierte Zahl ist und man das in eine Zahl umwandeln kann?

Lg

 

Hallo daBrain,

wie Sigi schon schrieb, Dein Problem sind die als Text formatierte Zellen im Blatt Frühbestellung und teils auch im Blatt Inventur.

Zunächst formatiere Deine gesamte Tabellenblatt Frühbestellung in das Standardformat um. (Deine vorhandenen Textformate bleiben dabei trotzdem erhalten; dies könnte man auch noch ändern, ist nachfolgend aber nicht notwendig und würde "Nacharbeit" in D2:D4 erfordern)
Dann folgender Formel in H2: =WENNFEHLER(SVERWEIS(D2&"";Inventur!$A$1:$C$6&"";3;0);0 )*1 Diese ist mit [Strg]+[Shift]+[Enter] abschließend einzugeben und nach unten zu kopieren.

Alternativ ohne den spez. Formeleingabeabschluss (in der standardformatierten Zelle H2):
=WENNFEHLER(AGGREGAT(15;6;Inventur!C$2:C$99/(Inventur!A$2:A$99&""=D2&"");1);"")

 

Hallo Werner!

Danke für deine Antwort.
Habe deine Lösung probiert, funktioniert in der Testtabelle.

Ist aber nicht praktikabel :-(

Das Thema warum ich als Format Text habe ist, dass ich Artikelnrn. mit führender 0 habe.
Wenn ich die Tabelle auf Standardformatierung umstelle und in einer neuen Zeile einen weiteren Artikel zur Inventur hinzufüge, mit händischer Eingabe, wird mir die Artikelnr. wieder um eine ev. vorhandene führende 0 gekürzt.

Bei Anlage von 20 Artikeln nacheinander denkt man nicht an die führende 0 sondern will das abarbeiten und sucht dann den Fehler wenn LagerIST nicht stimmt - wenn es denn auffällt. Es ist leider auch schon passiert dass der Artikel nachbestellt wurde obwohl noch ausreichend vorhanden weil eben die Artikelnr. um die 0 gekürzt wurde und deswegen die Abfrage 0 ergab weil die Artikelnrn. nicht übereinstimmten.

 

Hallo da Brain,

auch dafür gäbe es Lösungen. Aber das einfachste in Deinem Fall wäre, dass Du nur die Datenspalte im Textformat lässt.

 

Hallo Werner,

das habe ich prinzipiell so. Es gibt in der großen Tabelle auch Währungsfelder etc.
Das ist vielleicht in der Testtab falsch rübergekommen.

Ich habe es jetzt nochmal mit einer neuen Testtabelle probiert.
Nur Tabelle1!Spalte D sowie Tabelle2!Spalte A als Text, Rest blieb unangetastet Standard.
Copy/Paste aus der bestehenden Testtab -> nur Werte eingefügt.
Selbes Ergebnis, SVERWEIS gibt #NV zurück weil für Excel 425309 als Textformat ungleich ist wie 425309 als als Zahl erkannter Text.

Das gilt übrigens z.B. auch für die Funktion VERGLEICH

Ich habe noch etwas anderes probiert:

Ich habe in beiden Blättern die Artikelnrn-Spalte in eine nebenstehende kopiert. Die neue Spalte vor dem Kopieren als Standard formatiert, beim Einfügen nur Werte übernommen.
Die beiden alten Artnrn. Spalten gelöscht, den SVERWEIS wieder korrigiert - et voila - der Fehler bleibt bestehen.

Ich vermute ja, Excel markiert die Zelle mit einer als Text erkannten Zahl im Hintergrund mit irgendeinem Symbol, dass nicht da ist wenn es in eine Zahl umgewandelt ist - oder umgekehrt - (auch wenn das Format Text ist) und daher scheitert der SVERWEIS oder der VERGLEICH.

Was wäre denn z.B. eine Lösung?
In der Inventurliste stehen Artikelnrn verschiedener Großhändler. Somit kein gleiches Format - mal Ziffern/Buchstaben-Kombination, mal 6 stellig mit führender 0 wenn kürzer, mal 8 stellig...
Sogar innerhalb des Großhändlers gibt es 6-stellige Artikelnrn. ohne führende 0 und 5 stellige mit führender 0.

Lg

 

Hallo da Brain,

wie ich schon schrieb, das Textformat nur für die Artekelbezeichnung anwenden. Da aber zumindest in Deiner eingestellten Datei, auch andere Spalten als Textformat definiert waren, musst Du zumindest in der Formelspalten vor deren Eingabe eine Nicht-Textformat haben. Und dafür dann die aufgezeigten Formeln.

 

Hallo Werner,

die Artikelbezeichnung kann auch Standard sein, da ist mir das egal. Reinen Text greift Excel ja nicht an.
Die Artikelnr kann aber nicht Standard sein weil Excel mir sonst die führenden 0en streicht, dort wo es welche gibt.
Die Spalte LagerIST hat in der Originaltabelle das Format Standard.

Ich habe versucht deine =WENNFEHLER(AGGREGAT(15;6;Inventur!C$2:C$99/(Inventur!A$2:A$99&""=D2&"");1);"") auf meine Tabelle umzubauen, scheitere aber leider.
Vermutlich weil mir dieser Teil unklar ist: Inventur!C$2:C$99/(Inventur!A$2:A$99&""=D2&""
Kannst du mir den erläutern?

Ich habe jetzt meine Original-Tabellen so weit zusammengekürzt dass man halbwegs einen Überblick hat und hänge sie nochmal an. Darin auch alle Original Zellenformate.
Frühbestellung ist die Liste in der ich arbeite.
Inventurliste wird in Spalte C ausgefüllt und dann von SVERWEIS aus Frühbestellung durchsucht
Preisliste wird ebenfalls von einem SVERWEIS aus Frühbestellung durchsucht

Inventurliste hat mehrere Blätter. Im Blatt DATA werden die Felder der anderen Blätter gesammelt und dort von SVERWEIS ausgelesen. Ich habe mich dabei des Hints mit INDIREKT bedienen müssen, da hin und wieder zwischen den schon bestehenden Zeilen neue eingefügt werden und die dadurch automatisch in DATA übernommen werden. Alle Felder in Data haben das Format Standard, in den einzelnen Gruppenblättern sind die Artikelnrn als Text formatiert.
Man hätte das sicher eleganter lösen können, dazu fehlt mir allerdings das Wissen. Funktioniert aber zumindest.

In Frühbestellung hab ich die Artikelnrn in Spalte D gelb markiert die auch in der Inventurliste vorhanden sind und deren ausgelesener Wert in Lager IST korrekt ausgelesen wurde.
Rot markiert sind jene Zellen die zwar in der Inventurliste vorhanden sind, deren Wert in LagerIST aber nicht stimmt weil die Artikelnr nicht gefunden wurde obwohl vorhanden.

Er übernimmt in jedem Fall aber den Wert aus der Preisliste richtig.
Das Problem liegt also einzig und allein im File Inventurliste.
Ich habe auch schon testweise probiert direkt im richtigen Blatt zu suchen, also DATA mit der INDIREKT-Funktion auszuschließen, das Ergebnis ist leider das selbe.

Ich arbeite schon seit einigen Jahren mit diesen Listen - die sind halt dadurch immer weiter gewachsen. Das hat bisher auch immer funktioniert. Aber zwischen letztem Frühjahr und heuer muss irgendein Update da einen Fehler reingeschossen haben.

 

Hallo daBrain,

kann Dein Problem mit der Anpassung meiner Formel auf Deinen Dateien nicht nachvollziehen.
In I7:
=WENNFEHLER(AGGREGAT(15;6;[Inventurliste.xlsx]DATA!$C$2:$C$999/([Inventurliste.xlsx]DATA!$A$2:$A$999&""=D7&"");1);"")

wobei Du darin das abschließende ...1;"") auch durch ...1;0) ersetzen kannst. Dann solltest Du aber die Formel noch klammern mit =WENN(D7="";"";[Formel])

 

Hallo Werner,

Danke für deine Rückmeldung.
Warum auch immer, jetzt hats geklappt
Vielen vielen Dank.

Ich habe die Formel am Nachmittag ebenso angepasst, vermutlich war aber wo ein Tippfehler.

Was macht denn dieser Formelteil: Inventur!C$2:C$99/(Inventur!A$2:A$99&""=D2&"" ?
Kannst du mir den erläutern?

 

Hallo daBrain,

dem Formelteil, den Du wiedergegeben hast, benötigt noch eine abschließende Klammer, um diesen separat zu korrekt zu betrachten/auszuwerten.

Markiere diesen Formelteil in der Excel-Eingabezeile, betätige dazu [F9] und sieh Dir danach den Inhalt in der Eingabezeile an. Es wird eine Quotientenmatrix gezeigt, die teils die Datenwerte aus Inventur!C$2:C$99 anzeigen aber auch meistens Fehlerwerte beinhaltet (natürlich da, wo keine Übereinstimmung im Vergleichsformelteil vorliegt).

Die Fehlerwerte werden durch das 2. Argument von AGGREGAT() der 6 für dessen Auswertung einfach ignoriert übrig bleibt der Ergebniswert.

Die im Bedingungsfomelteilvergleich angefügten &"" sorgen dazu, dass die in Deinen Quelldaten teilweise doch vorhandenen echten Excelzahlenwerte in Textwerte mit gleichen Ziffernfolgen wie die der Zahlenwerte gewandelt werden, damit auch echte Gleichheit festgestellt werden kann.