Häufig genutze Formeln für Excel

Kleiner Spickzettel zur Bearbeitung von EAN Listen

Excel Formelsammlung für JTL Ameise

von | Sep 11, 2017 | JTL Shop 4 | 0 Kommentare

Um Artikel in die JTL WaWi einzupflegen, ist es zu 99% nötig, die Lieferantenlisten umzuformatieren. Die meisten Listen enthalten nur Einzelartikel ohne Zuordnung Vater / Kind, so daß die Umformatierung einige Zeit in Anspruch nimmt… wenn noch kein EDI läuft. Um die Listen auf JTL „Ameisen-lesbare“ zu formatieren, ist Excel (bei mir aktuell Version 2013) oder Open Office (dann für CSV Listen) das Werkzeug der Wahl… oder manchmal auch Qual 😉

Anbei ist eine kleine Bibliothek der meistgenutzen Excel-Formeln, diese wird nach und nach erweitert.

Verketten von Zellen

Gerne genommen um Artikelnamen und -Farben bzw. -Größen aneinander zu fügen.

=VERKETTEN(WENN(A2<>„“;A2;““);WENN(B2<>„“;“ „&B2;““);WENN(C2<>„“;“ „&C2;““))

Die Formel übernimmt nur dann Zellen, wenn diese nicht leer sind. Zellen B2 und C2 werden wenn gefüllt mit Leerzeichen angefügt.

Aufteilen von Zelleninhalten

Wenn der Lieferant mehrere Werte in einer Zelle liefert, die eigentlich in getrennte Spalten gehören, lassen sich diese über die Excel Datentools splitten. Quasi das Gegenteil von Verketten 😉 Erklärung, wie es geht gibt´s besser als ich das hier jetzt machen könnte beim MS Support.

Substring

Um zB den Farbcode oder andere Info aus einer Zelle zu filtern.

Erstes Wort aus Zelle: =TEIL(A1;1;SUCHEN(“ „;A1)-1)
Zweites Wort aus Zelle: =TEIL(A1;FINDEN(“ „;A1)+1;FINDEN(“ „;A1;FINDEN(“ „;A1)+1)-FINDEN(“ „;A1)-1)

Auf/Abrunden von Preisen

Um in der Ameise Preise auf die nächste 9er Stelle zu runden (323->319, 326->329)

=AUFRUNDEN((C2*0,6);0)+WAHL(RECHTS(AUFRUNDEN((C2*0,6);0);1)+1; -1;-2;-3;-4;-5;-6;3;2;1;0)

C2= UVP Faktor=% (0,6 entspricht minus 40% vom UVP, 0,85 = 15% usw.)

SVERWEIS

Eigentlich kann man dem SVERWEIS (engl. VLOOKUP) fast ein eigenes Kapitel widmen… irgendwie wehren sich die Suchabfragen in Teilausschnitten oder Arbeitsblättern standhaft, ihren Zweck zu erfüllen. Dafür ist es, wenn man den Dreh erst raus hat, ein mächtiges Werkzeug bei der Erstellung von Importlisten.

=SVERWEIS($A1;Tabelle2!$A$1:$Z$1000;4;FALSCH())
  • $A1: Zelle, deren Inhalt zur Suche verwendet werden soll
  • Tabelle2!$A$1:$Z$1000: Suchmatrix (Felder, die durchsucht werden sollen)
  • 4: Rückgabewert, Index der Spalte, deren Wert gefunden werden soll (4. Spalte neben Spaltenbeginn der Suchmatrix)
  • FALSCH(): exakte Suche
Stolperfallen

Der SVERWEIS kann nur Werte rechts der zu durchsuchenden Spalte zurückliefern! Die zu durchsuchende Spalte muss sich IMMER ganz links in der Suchmatrix befinden. Heißt: wenn die Spalte, in der A1 gesucht werden soll, in Tabelle2 die Spalte D ist, muss die Suchmatrix auch mit D beginnen. Im Zweifelsfall empfiehlt es sich, die zu durchsuchende Spalte in Spalte A einzufügen. Bei Index = 4 würde dann Spalte D den Rückgabewert enthalten.

Der SVERWEIS ist extrem empfindlich bei unterschiedlichen Datentypen. Ist zB die Spalte A in Tabelle 1 eine EAN mit dem Feldtyp „Text“ und die EAN Spalte in Tabelle2.D vom Typ Zahl, wird Excel schlicht nichts finden! Da die Formatierung einer ganzen Spalte mit Headern meist nicht zum gewünschten Ergebnis führt, kann man in einer neuen Spalte einfach den Wert aus Tabelle1.A1 mit 1 multiplizieren, und (bei EANs) dann die Spalte zur Zahl ohne Kommastellen umformatieren. Dann finden die beiden numerischen Werte auch zueinander 😉

Mit leeren Feldern als Suchbegriff gibt´s eine Fehlermeldung. Mit „=WENN(A1 <> „“;SVERWEIS(…);““)“ lassen sich leere Zellen in Spalte A einfach überspringen.

0 Kommentare

Einen Kommentar abschicken

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.