Liebe/-r Experte/-in,
ich möchte eine Art Rechnungsprogramm schreiben. Dazu habe ich ein eigenes Tabellenblatt (Blatt 2) mit Artikelnummern, den Artikeln, dem dazugehörigen MwSt-Satz (7/19%) und dem Grundpreis erstellt. In dieser Tabelle werden nun alle Artikel eingetragen.
Nun komme ich nicht weiter: Das Tabellenblatt (Blatt 1)mit der Rechnung enthält die Spalten Artikelnummer (A1)/Anzahl(B1)/Artikel(C1)/MwSt-Satz(D1)/Grundpreis(E1)
Ich würde nun gerne folgendes hinbekommen: Wenn ich in der in Blatt 1 in A1 die Artikelnummer eingebe, soll automatisch in A1 nicht die Artikelnummer, sondern der zugehörige Artikel erscheinen und natürlich die dazugehörigen Daten. Die passende Formel dafür bekomme ich einfach nicht hin.
Können Sie mir helfen?
Herzlichen Dank und viele Grüße!
Gerd Wagner
Hallo,
das ist eigentlich recht einfach lösbar.
Können Sie mir einfach eine entsprechende Datei per Mail senden (an [email protected] ).
Ich schaue mal, was ich machen kann…
Viele Grüße,
Alex
Verstehe ich Sie richtig ? Sie wollen in der gleichen Zelle (A1) in der Sie die Artikelnummer eingeben, dann statt der Arikelnummer den Klartext haben ?
Das würde nicht gehen, ausserdem erkenne ich den Sinn nicht.
Gruss
ED
Ich würde nun gerne folgendes hinbekommen: Wenn ich in der in
Blatt 1 in A1 die Artikelnummer eingebe, soll automatisch in
A1 nicht die Artikelnummer, sondern der zugehörige Artikel
erscheinen und natürlich die dazugehörigen Daten.
Guten Morgen Herr Wagner,
ich denke daß Ihnen die Funktion „SVERWEIS“ weiterhelfen könnte. Ich würde vorschlagen, daß Sie die Excel-Hilfe dazu lesen und prüfen ob es das ist was Sie suchen. Sollte es das nicht sein, oder es Probleme damit geben, dann schreiben Sie mir und ich helfe Ihnen sehr gerne weiter.
Mit freundlichen Grüßen
Hans Bartelme
Hallo Gerd,
die Lösung für Dein Problem ist die Formel „SVERWEIS“.
Blatt 2, wie von Dir beschrieben. Auf Blatt 1 sind die Spalten: A: ArtNr, B:Anzahl, C:Artikel, D:MwSt, E: Grundpreis, F: Preis
In A2 gibst Du die Artikelnummer ein, in B2 die Anzahl.
Hier die Formeln für die restlichen zellen:
C2: =SVERWEIS(A2;Tabelle2!A2:smiley:6;2;WAHR)
D2: =SVERWEIS(A2;Tabelle2!A2:smiley:6;3;WAHR)
E2: =SVERWEIS(A2;Tabelle2!A2:smiley:6;4;WAHR)
F2: =B2*E2*D2
(Formeln dann entsprechend „runterziehen“.)
Die Spalte A sollte dann nicht mit gedruckt werden (also entsprechenden Druckbereich festlegen.)
Wenn Du es wirklich so möchtest, dass eine Eingabe in die Spalte A durch die Artikelnummer ersetzt wird, dann müsste man die Formel als Makro hinterlegen, das den Inhalt dann austasucht. Wird wesentlich aufwendiger.
Ich hoffes, das ist so erst einmal okay.
Gruß aus Berlin
Torsten
Sehr geehrter Herr Wagner,
kam meine Antwort an?
Mit freundlichen Grüßen
Hans Bartelme
Hallo Gerd,
in Blatt 2 markierst Du die gesamte Liste, klickst in das Namensfeld (direkt über der Spalte A) und gibst dort ein „Stammdaten“, danach Enter-Taste.
Dann gehst Du auf Blatt 1. Ich nehme an , daß in Zeile 1 die Spaltenüberschriften „Artikelnummer“, „Anzahl“, „Artikel“ usw. stehen. In Zelle C2 gibst Du die Funktion ein:
=SVERWEIS(A2;Stammdaten;2;0)
Zu den Argumenten der Funktion:
( A2 ;Stammdaten;2;0) --> verweist auf das Suchkriterium, also die in Zelle A2 eingegebene Artieklnummer. Wichtig ist, dass in Deinen Stammdaten die Artikelnummer in der ersten Spalte steht.
(A2; Stammdaten ;2;0) --> bezeichnet den Bereich, in welchem nach den Artikeldaten gesucht werden soll. Den hast Du ja vorhin als „Stammdaten“ benannt. (Du kannst bei der Formeleingabe mit der Taste F3 aus den bereits vergebenen Namen auswählen.)
(A2;Stammdaten; 2 ;0) --> dies ist die Nummer der Spalte in Deinen Stammdaten auf Blatt 2, aus welcher die Information rausgesucht werden soll. Also: Zweite Spalte, nämlich die Artikelbezeichnung.
(A2;Stammdaten;2; 0 ) --> wichtig! Die Null gibt an, dass nur genaue Übereinstimmungen der ArtikelNr. akzeptiert werden sollen. Wenn Du die Null vergißt, kommt es bei unsortierten Stammdaten zu falschen Ergebnissen.
Anschließend die Formel aus C2 für die gesamte Spalte C runterkopieren. Für Zelle D2 (MwSt) einfach die Formel aus C2 kopieren und den Spaltenindex anpassen:
=SVERWEIS(A2;Stammdaten;3;0)
War das die Lösung, die Du suchst?
Hinweis: Wenn neue Artikel zu den Stammdaten dazukommen, fügst Du diese in Blatt 2 irgendwo in die Mitte der Liste als neue Zeile ein (Zellen einfügen), dadurch verschiebt sich der Bereich „Stammdaten“ automatisch eine Zeile nach unten und alle Formeln greifen weiterhin. (Wenn Du neue Artikel unten an die Liste anfügst, ist das nicht so!) Nach dem Einfügen kannst Du die Liste ja wieder sortieren, falls nötig.
Hallo,
vielen, vielen Dank! das hat mit der Formel SVERWWEIS hat funktioniert. Noch viel wichtuger war der Hinweis, neue Daten in die Tabelle einzufügen! ich habe nämlich mit genau diesem Problem gekämpft, dass ich Daten angehängt habe und dann die Formeln ändern musste!
Danke!
Gerd
Sher geehrter Herr Bartelme,
vielen Dank für den Hinweis. Er war goldrichtig: nach einigem Experimentieren ist mir damit auch eine Lösung gelungen!
Herzlichen Dank!
Gerd Wagner
Hallo Gerd,
die Lösung für Dein Problem ist die Formel „SVERWEIS“.
Blatt 2, wie von Dir beschrieben. Auf Blatt 1 sind die Spalten: A: ArtNr, B:Anzahl, C:Artikel, D:MwSt, E: Grundpreis, F: Preis
In A2 gibst Du die Artikelnummer ein, in B2 die Anzahl.
Hier die Formeln für die restlichen zellen:
C2: =SVERWEIS(A2;Tabelle2!A2:smiley:6;2;WAHR)
D2: =SVERWEIS(A2;Tabelle2!A2:smiley:6;3;WAHR)
E2: =SVERWEIS(A2;Tabelle2!A2:smiley:6;4;WAHR)
F2: =B2*E2*D2
(Formeln dann entsprechend „runterziehen“.)
Die Spalte A sollte dann nicht mit gedruckt werden (also entsprechenden Druckbereich festlegen.)
Wenn Du es wirklich so möchtest, dass eine Eingabe in die Spalte A durch die Artikelnummer ersetzt wird, dann müsste man die Formel als Makro hinterlegen, das den Inhalt dann austasucht. Wird wesentlich aufwendiger.
Ich hoffes, das ist so erst einmal okay.
Gruß aus Berlin
Torsten.
Hallo Torsten,
vielen Dank für den wichtigen Hinweis. Ich musste mich erst einmal in die Formel eindenken und eine Menge experimentieren, aber dank deines Hinweises konnte ich mir dann auch gezielte Info im Netz suchen. Damit hat es dann wunderbar funktioniert!
Herzlichen Dank!
Gerd
Hallo Herr Wagner,
müssen die Eingabe (A1) und das Ergebnis (A1) in ein und derselben Zelle stehen?
Wenn ja, hilft hier nur ein Makro!
Wenn nein und der Artikel kann dann auch z.B. in B1 stehen, hilft hier sehr einfach eine Excelfunktion mit dem Namen „SVERWEIS“.
Schreiben Sie mir nochmal, wenn Ihnen die Info bislang noch nicht gereicht hat.
Ansonsten… viel Erfolg
dieter emonts
Hallo 
Das ist mal eine sehr einfache Frage mit einer einfachen Lösung.
Die gesuchte Formel heißt: SVERWEIS
Die Hilfe zu dieser Formel sollte ihr übriges tun.
Gruß
inkasso
Tagchen
hoffe dass ich Ihr Problem richtig verstanden habe.
Ich würde in Blatt1:
A1 frei lassen für die Artikelnummer Eintragung
B1 =WENN(ZÄHLENWENN(Blatt2!A1:E100;A1)=0;"";SVERWEIS
(A1;Blatt2!A1:E100;2))
C1 =WENN(ZÄHLENWENN(Blatt2!A1:E100;A1)=0;"";SVERWEIS
(A1;Blatt2!A1:E100;3))
D1 =WENN(ZÄHLENWENN(Blatt2!A1:E100;A1)=0;"";SVERWEIS
(A1;Blatt2!A1:E100;4))
E1 =WENN(ZÄHLENWENN(Blatt2!A1:E100;A1)=0;"";SVERWEIS
(A1;Blatt2!A1:E100;5))
(Zählenwenn) möchte identische Artikelnummer Zählen
(wenn) prüfe wenn eingegebene Artikelnummer in Blatt 2 zu finden ist, soll Sverweis angewendet werden, sonst Leer lassen ("")
(Sverweis) sucht Matrix nach Artikelnummer, falls Artikelnummer falsch geschrieben wurde, nimmt Sverweis den ähnlichsten Wert, also falsche Artikelnummer, darum wird Sverweis nur bei Zählenwenn Fund genutzt durch die Wenn abfrage.
Falls die Lösung nicht ganz zutreffend in Ihren Fall ist, lassen Sies mich wissen.
Hallo,
das Problem wird mit der Formel SVFERWEIS() gelöst.
Zur genauen Verwendung die Hilfe in Anspruch nehmen oder noch mal zurück fragen
Grüße
Rainer Nowaczyk
Hallo Herr Wagner,
die Frage verstehe ich nicht genau. Wenn Sie in A1 etwas eintragen, dann kann nicht in der gleichen Zelle, also A1 anschließend etwas anderes stehen. Meinten Sie vielleicht, dass wenn Sie in der Rechnung in A1 die Nummer eingeben, dass dann in C1 der Artikelname, in D1 der MwStSatz und in E1 der Preis stehen soll?
Falls ja, würde das mit dem Sverweis gehen. Der Sverweis ist als Funktion hinterlegt. Suchkriterium wäre dann A1 der Rechnung, die Matrix wäre die Tabelle in Blatt 2, der Spaltenindex zeigt das Ergebnis der Spaltennummer, also „2“ für den Artikelnamen oder „3“ für den Mehrwertsteuersatz, der Wahrheitswert sollte hier 0 sein.
Freundliche Grüße
Martina Hanke
Hallo Herr Wagner,
erst mal entschuldigung, weil Sie so lange warten mussten, aber ich war im Urlaub…
Die Formel, die Sie benötigen lautet „SVERWEIS“. Hier können Sie bestimmen, welche Angaben in den Zellen stehen sollen, wenn Sie in besagter Zelle die Artikel-Nr. eingeben. Allerdings müssten Sie eine zusätzliche Spalte einfügen, damit auch der Artikel-Name angezeigt wird, denn wenn Sie in A1 die Artikel-Nr. eingeben, kann Excel, meines Wissens nach, nicht in die selbe Zelle die Artikel-Nr. eintragen. Die Funktion müssen Sie in die Zellen eintragen, in denen Sie die „automatischen Angaben“ stehen haben wollen.
Wenn Sie auf den Button „Funktion einfügen“ links neben der Bearbeitungsliste gehen, müssen sie in das Feld „SVERWEIS“ eingeben. Bei „Suchkriterium“ klicken Sie die Zelle an, in der Sie die Artikel-Nr. eingeben wollen. Bei „Matrix“ markieren Sie den gesamten Datenbereich im Blatt 2 (außer die Überschriften, also nur die Zahlen) und setzten ggf. die Zellen mit dem $-Zeichen fest, da Sie die Formel später sonst nicht „runterziehen“ können. Bei Spaltenindex müssen Sie nun einfach Zählen, in welcher Spalte die Information steht, die Sie haben wollen in der Zelle, in der Sie die Formel eingebaut haben (z. B. „3“). Ich hoffe ich konnte es einigermaßen verständlich erklären. Mit meiner Muster-Tabelle wäre es für Sie sicherlich einfacher gewesen.
Viele Grüße
Spiderwomangirl
Hallo Gerd, ist ja schon ein wenig her - Deine Frage, aber vieleicht hilft die Antwort ja noch:
in einem beliebigen Excel-Blatt angelegt:
Fenster „Stammdaten“ und Fenster „manuelle erfassung“
in der Zelle A3 wird die Artikelnummer erfasst
im Blatt Stammdaten stehen in der Spalte A alle artikelnummern
in der Spalte B stehen alle Bezeichnungen
Die Formel
=WENN(A3>0;SVERWEIS(A3;Stammdaten!A:B;2;FALSCH);" ")
steht im Blattmanuelle Erfassung in der zelle B3
die wenn-Abfrage prüft nur dann, wenn in der Zelle A3 was drin steht - sonst würde immer „#NV“ als Ergebnis ausgegeben werden.
Eine kleine Beispieltabelle kann ich Dir zuschicken - brauche dann Deine E-Mail-Adresse.
Sende diese ann an [email protected] - aber vieleicht reicht dieses ja schon.
Viel Spass
Jürgen
Hallo,
das geht mir SVERWEIS,
=SVERWEIS(A9;BLATT02!$B$2:blush:H$20040;4;FALSCH)
A9 = Artikelnummer aus Blatt 1
Blattname (Blatt 2)
B2:H2000: Spalten in Blatt 2(zu suchender Bereich inkl. Artikelnummer und Artikeltext).
4 = Indexfeld von Blatt 2 (wo Artikeltext steht = Rückgabefeld zum Ausgeben),
4 = 4te Spalte in Blatt 2
FALSCH = genaue Übereinstimmung
Achtung: Beim SVERWEIS ist auf die Formatierung zu achten: Zahl muss als Zahl formatiert sein;
SVERWEIS mit Text:
Abfragespalten müssen als Text formatiert sein, Ergebnisspalte als Standard
Abfrage-Matrix (BLatt2) muss aufsteigend sortierr sein.
Ich hoffe, die Info hilft Ihnen weiter.
Vielen Dankj! Damit hate’s funktioniert!
Grüße!
G. Wagner