Office: SVERWEIS mit Zahlen und Texten

Helfe beim Thema SVERWEIS mit Zahlen und Texten in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Liebes Forum, ich verzweifle mit Excel. Ich arbeite mit großen Excel Files für meine Frühbestellung. Dabei liegen mir verschiedene Artikelnummern... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von daBrain, 3. Februar 2023.

  1. daBrain Neuer User

    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
     
    daBrain, 3. Februar 2023
    #1
  2. Sigi.21 hat Ahnung
    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
     
    Sigi.21, 3. Februar 2023
    #2
  3. daBrain Neuer User
    Bei mir geht es nicht...siehe Screenshot:
    SVERWEIS mit Zahlen und Texten upload_2023-2-3_11-38-51.png

    Screenshot Tab Inventur:
    SVERWEIS mit Zahlen und Texten upload_2023-2-3_11-39-24.png

    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.

    SVERWEIS mit Zahlen und Texten upload_2023-2-3_11-45-18.png

    SVERWEIS mit Zahlen und Texten upload_2023-2-3_11-46-7.png

    SVERWEIS mit Zahlen und Texten upload_2023-2-3_11-46-27.png

    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
     
    daBrain, 3. Februar 2023
    #3
  4. Sigi.21 hat Ahnung

    SVERWEIS mit Zahlen und Texten

    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
     
    Sigi.21, 3. Februar 2023
    #4
  5. daBrain Neuer User
    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
     
    daBrain, 3. Februar 2023
    #5
  6. neopa C Erfahrener User
    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);"")
     
    neopa C, 3. Februar 2023
    #6
  7. daBrain Neuer User
    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.
     
    daBrain, 3. Februar 2023
    #7
  8. neopa C Erfahrener User

    SVERWEIS mit Zahlen und Texten

    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.
     
    neopa C, 3. Februar 2023
    #8
  9. daBrain Neuer User
    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
     
    daBrain, 3. Februar 2023
    #9
  10. neopa C Erfahrener User
    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.
     
    neopa C, 3. Februar 2023
    #10
  11. daBrain Neuer User
    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.
     
    daBrain, 3. Februar 2023
    #11
  12. neopa C Erfahrener User
    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])
     
    neopa C, 3. Februar 2023
    #12
  13. daBrain Neuer User

    SVERWEIS mit Zahlen und Texten

    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?
     
    daBrain, 3. Februar 2023
    #13
  14. neopa C Erfahrener User
    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.
     
    neopa C, 4. Februar 2023
    #14
Thema:

SVERWEIS mit Zahlen und Texten

Die Seite wird geladen...
  1. SVERWEIS mit Zahlen und Texten - Similar Threads - SVERWEIS Zahlen Texten

  2. Werte in 2 Tabellen vergleichen und Zählen wenn Suchbegriff zutrifft

    in Microsoft Excel Hilfe
    Werte in 2 Tabellen vergleichen und Zählen wenn Suchbegriff zutrifft: Hallo, vielleicht kann mir jemand helfen: Ich habe zwei Tabellenblätter und in beiden steht in Spalte A der Nachnahme Es sollen nun alle Namen in den beiden Tabellenblätter miteinander...
  3. Werte in einer bestimmten Zeile einer Tabelle zählen

    in Microsoft Excel Hilfe
    Werte in einer bestimmten Zeile einer Tabelle zählen: Guten Tag, in der Lasche 1 habe ich in der ersten Spalte eine Liste von eindeutigen Nummern. In Lasche 2 ist die gleiche Liste von Nummern (andere Reihenfolge) und neben jeder Nummer folgen in...
  4. SVerweis, WennDann oder Makro

    in Microsoft Excel Hilfe
    SVerweis, WennDann oder Makro: Hallo zusammen, Ich habe zwar ähnliche Themen gefunden aber komme trotzdem bei meinem Problem nicht weiter. Anbei meine Beispieldatei, da die echten Daten viel umfangreicher sind. Ich würde...
  5. Zahlen ungleich 0 aus einer Spalte auflisten (SVerweis?)

    in Microsoft Excel Hilfe
    Zahlen ungleich 0 aus einer Spalte auflisten (SVerweis?): Hallo, ganz kurz: Ich möchte, das alle Werte aus einer Spalte, die nicht = 0 sind aufgelistet werden. Mir wurde empfohlen mir die Dokumentation von SVerweis anzuschauen, weil mir diese Funktion...
  6. Mit SVERWEIS nächst größere Zahl filtern

    in Microsoft Excel Hilfe
    Mit SVERWEIS nächst größere Zahl filtern: Hallo Leute, Ich habe eine Tabelle mit 3 Spalten. In der 1. steht ein Preis, in der 2. auch und in der 3. Spalte ein weiterer Wert. Jetzt nehm ich einen Preis aus der 1. Spalte und möchte in der...
  7. Dropdown Sverweis Zählen

    in Microsoft Excel Hilfe
    Dropdown Sverweis Zählen: Hallo ich habe eine Liste mit merheren(30 Dropdown feldern, links daneben ist die Spalte Menge und die Spalte Preis. Nun soll auf dem ersten Arbeitsblatt die Menge zusammengezählt werden , für...
  8. Sverweis - Formatierung von Zahlen/Text

    in Microsoft Excel Hilfe
    Sverweis - Formatierung von Zahlen/Text: Hallo, mein Problem hat glaub ich nur bedingt mit dem Sverweis zu tun. Ich versuche mal zu erklären... Ich möchte aus einer Tabelle 1 Werte in eine Tabelle 2 mittels Sverweis übergeben....
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden