Häufig genutze Formeln für Excel
Kleiner Spickzettel zur Bearbeitung von EAN ListenExcel Formelsammlung für JTL Ameise
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.
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, Größe oder andere Info aus einer Zelle zu filtern.
Gegenteil: 1234567890: =SUMME((TEIL(0&A1;KGRÖSSTE(WENN(ISTZAHL(TEIL(0&A1;ZEILE($1:$256);1)*1);ZEILE($1:$256);1);ZEILE($1:$256));1)*1)*10^(ZEILE($1:$256)-1))
Auf/Abrunden von Preisen
Um in der Ameise Preise auf die nächste 9er Stelle zu runden (323->319, 326->329)
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.
- $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.
Leerzellen auffüllen
Leider werden beim Export über die Ameise die Kategorien nur beim Vaterartikel ausgegeben – anscheinend haben die Kinderartikel keine Kategoriezuweisung. Da ich aber zB das Artikelgewicht, falls nicht vom Hersteller angegeben, pauschal für die gesamte Kategorie über die Ameise importiere, fehlt mit die Kategoriezuweisung bei den Kindern.
Mit diesem Macro für Excel ist es ganz einfach, die Kategorie „zu vererben“, um dann über einen SVERWEIS das Artikelgewicht zu bestimmen.
Sub Leerzellen()
Dim Bereich As Range, Zelle As Range
Set Bereich = Range("A4:A18000")
For Each Zelle In Bereich.SpecialCells(xlCellTypeBlanks)
Zelle = Zelle.Offset(-1, 0)
Next Zelle
End Sub
Wenn zB Zelle A1 = Foo, und Zelle A8 = Bar ist, werden A2…A7 mit Foo aufgefüllt.
Doppelte Werte farblich hervorheben
Um zB Durchläufer oder NOS-Artikel in EAN-Listen der Lieferanten zu finden und zu markieren, exportiere ich erst alle Artikel des Lieferanten, und füge diese als neues Tabellenblatt in die neue EAN-Liste ein. Dann markiere ich die EAN-Spalte, und gehe über „Bedingte Formatierung -> Neue Regel -> Formel zur Ermittung der zu formatierenden Zellen verwenden“. Mit der Formel
lasse ich die Zellen zB rot auffüllen. Tabelle2!A ist die Spalte, die ich durchsuchen will, ;A1 die EAN-Spalte, in der die Doubletten markiert werden sollen.
Erstes Wort aus Zelle entfernen
0 Kommentare