In ein­er Liste, welche als Daten­ba­sis für einen Serien­druck mit Word dienen wird, sind ver­schiedene Pro­duk­te aufge­führt; in ein­er definierten Spalte ist die Anzahl ver­merkt, wie viele Etiket­ten jedes Pro­duk­ts erstellt wer­den sollen. Da eine andere Abteilung mit dem Druck beauf­tragt wurde, soll je Etikett 1 Zeile in der Excel-Liste vorhan­den sein.

Die Tabelle, mit welch­er ich arbeite

Dass einzelne Pro­duk­te mehrfach aufge­führt sind ist dem Umstand geschuldet, dass bei gewerblichen Kun­den der Net­to- und bei pri­vat­en der Brut­to-Preis auf das Etikett geschrieben wer­den soll. Das aber soll im End­ef­fekt in MS Word aus­gew­ertet wer­den.

Um das Ziel zu erre­ichen gibt es unter­schiedliche Wege. Der schein­bar sim­pel­ste, näm­lich die entsprechende Anzahl von erforder­lichen Leerzeilen in die Liste einzufü­gen und dann die jew­eils obere Zeile nach unten zu kopieren mag ich Ihnen nicht anbi­eten bzw. antun. Darum gab es hier­für keine extra Über­schrift. 😉

▲ nach oben …

Per Makro / VBA

Eine Möglichkeit, die in prak­tisch jed­er Excel-Ver­sion der let­zten 15 Jahre funk­tion­iert ist der Ein­satz eines Makros, also eines VBA-Skripts. – Der fol­gende Code ist bezüglich der ver­wen­de­ten Begriffe exakt auf dieses Arbeits­blatt abges­timmt. Er ist wed­er über­mäßig ges­traft noch an allen erden­klichen Posi­tio­nen kom­men­tiert. Fügen Sie ihn in einem all­ge­meinen Mod­ul (beispiel­sweise Modul1) oder im Mod­ul DieseAr­beitsmappe ein. Wenn Sie das Makro anschließend beispiel­sweise per AltF8 aufrufen und aus­führen lassen, wird für jedes Pro­dukt die erforder­liche Zahl von Zeilen automa­tisch erstellt. Der PC von Hewlett-Packard wird natür­lich nicht vervielfacht, da ja nur ein Etikett gebraucht wird. Hier nun der Code:

Option Explicit
Option Base 1

Sub Makro1()
   Dim aData, aMehrData, i As Long, k As Long, Anz As Long
   Dim lRow As Long, lCol As Long
   
   With Tabelle1
      lRow = .Cells(Rows.Count, 1).End(xlUp).Row
      lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
      .ListObjects("tbl_Etiketten").Resize Range(.Cells(1, 1), .Cells(lRow, lCol + 1))
      lCol = lCol + 1
      .Cells(1, lCol) = "Index"
      .Cells(2, lCol) = 1
      .Cells(3, lCol).FormulaR1C1 = "=R[-1]C+1"
      .Cells(3, lCol).AutoFill Destination:=Range(.Cells(3, lCol), .Cells(lRow, lCol))
      'In Werte umwandeln, um Fehler nach dem auffüllen zu vermeiden
      With Range("tbl_Etiketten[Preis Brutto]")
         .Copy
         .PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
      End With
      aData = .Range(.Cells(2, 1), .Cells(lRow, lCol))
      '###
      For i = 1 To UBound(aData)
         Anz = aData(i, 4)
         If Anz > 1 Then
            ReDim aMehrData(Anz - 1, lCol)
            For k = 1 To Anz - 1 'Eigentlich auch per Schleife, 1 .. lCol
               aMehrData(k, 1) = aData(i, 1)
               aMehrData(k, 2) = aData(i, 2)
               aMehrData(k, 3) = aData(i, 3)
               aMehrData(k, 4) = aData(i, 4)
               aMehrData(k, 5) = aData(i, 5)
               aMehrData(k, 6) = aData(i, 6)
               aMehrData(k, 7) = aData(i, 7)
            Next k
            lRow = Cells(Rows.Count, 1).End(xlUp).Row
            Cells(lRow + 1, 1).Resize(Anz - 1, lCol) = aMehrData
         End If
      Next i
      '##
      .ListObjects("tbl_Etiketten").Resize .UsedRange
      .ListObjects("tbl_Etiketten").Sort. _
          SortFields.Clear
      .ListObjects("tbl_Etiketten").Sort. _
         SortFields.Add Key:=Range("tbl_Etiketten[[#All],[Index]]"), _
         SortOn:=xlSortOnValues, _
         Order:=xlAscending, _
         DataOption:=xlSortTextAsNumbers
      With .ListObjects("tbl_Etiketten").Sort
         .Header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .Apply
      End With
         
      .Columns("G:G").Delete Shift:=xlToLeft  'Index wird nicht mehr gebraucht
      .Cells(1, 1).Select  '(Der Form halber)
   End With
End Sub

Hier habe ich den reinen Text des Codes für Sie als *.zip hin­ter­legt. Nach dem ent­pack­en wird Ihnen das kopieren vielle­icht leichter fall­en.

In ihrem eige­nen Pro­jekt wer­den Sie gewiss einen anderen Namen für die Tabelle ver­wen­den und wahrschein­lich auch die Spalte mit der Anzahl an ein­er anderen Posi­tion haben. Sie kön­nen auch jed­erzeit statt der Lis­tO­b­ject-Namen für die Spal­ten auch die stan­dard­mäßi­gen Bere­ichs-Adressen in A1-Schreib­weise ver­wen­den.

Hin­weis: Wenn Sie vol­lkom­men oder so gut wie unbe­darft sind in Sachen VBA, dann wird Ihnen eine Anpas­sung des Codes gewiss schw­er fall­en. Wir (das Blog-Team) haben im Laufe der let­zten Jahre die Erfahrung gemacht, dass Nutzer dieses Blocks auch direk­t­bei uns um Hil­fe, um eine direk­te Anpas­sung für das eigene Pro­jekt nach­fra­gen. Sor­ry, da müssen wir auf unseren Spon­sor GMG-CC ver­weisen. Wir investieren in diese Veröf­fentlichung bere­its sehr viel Zeit und kön­nen nicht (auch noch) solche Dien­stleis­tun­gen gratis erbrin­gen. Bitte haben Sie dafür Ver­ständ­nis.

▲ nach oben …

Mit Power Query

Begin­nend mit der Ver­sion Excel 2010 kön­nen Sie Pow­er Query als Add-In bei Microsoft herun­ter­laden, in ²⁰¹⁶⁄₃₆₅ ist dieses Tool bere­its fest inte­gri­ert. Es geht hier zwar auch nicht ganz ohne Code, aber kom­plett ohne VBA und somit ist das auch bei Rech­n­ern durch­führbar, wo aus Grün­den der Sicher­heit Makros nicht aus­ge­führt wer­den kön­nen. Und ver­sprochen, es ist nur eine einzige Formel. 😎 

Die fol­gen­den Hinweise/Anleitungen sind nicht wirk­lich an Null-Ein­steiger in Sachen Pow­er Query gerichtet. Das bedeutet, dass sie sich vielle­icht ein wenig in die Materie des PQ einar­beit­en müssen. Den­noch beschreibe ich hier die wichtig­sten Schritte …

Nach dem Import der Tabelle in den Abfrage-Edi­tor stellt sich das so dar:

Direkt nach dem Import der Dat­en in den Abfrage-Edi­tor

Ide­al­er­weise wer­den sie die Abfrage sofort per Schließen & laden in ein getren­ntes Tabel­len­blatt schreiben, damit Sie prak­tisch einen sta­tus quo schaf­fen und sich­ern. Den­noch möchte ich Ihnen anhand dieses Beispiels einige mein­er Über­legun­gen nicht voren­thal­ten, denn so ein­fach wie es auf den ersten Blick zu sein scheint ist es nun doch wirk­lich nicht. Dass null keineswegs der Zahl 0 gle­ichzuset­zen ist sollte Ihnen bekan­nt sein. Und dass in der Spalte Preis brut­to keine Formeln, son­dern nur die berech­neten Werte ste­hen, sollte Ihnen bewusst sein.

Mit den typ­is­chen Mit­teln des Pow­er Query, näm­lich mit eini­gen Mausklicks wer­den sie hier nicht zum Ziel gelan­gen. Da gibt es keine Möglichkeit, Zeilen in eine beste­hende Abfrage oder Liste einzufü­gen. Und ich musste auch einige Zeit damit ver­brin­gen, mich durch die Funk­tio­nen der Sprache M „durchzuwühlen” und diese Funk­tion dann auch vernün­ftig anzuwen­den.

Fügen Sie als erstes eine Benutzerdefinierte Spalte hinzu und belassen Sie es bei dem vorgegebe­nen Namen oder ver­wen­den Sie (wie auch ich) den Namen Liste. Als Benutzerdefinierte Spal­tenformel geben Sie fol­gen­des ein:

Diese Formel exakt so eingeben

Der Klarheit wegen hier noch ein­mal die Formel in geschrieben­er Form, die sie auch direkt kopieren und in ihre Datei ein­fü­gen kön­nen:
List.Generate(()=>[Anzahl], each _ >0, each _ - 1)
und bei der Gele­gen­heit sei ange­merkt, dass ich diese Formel fast genau­so aus der Hil­fe  zur Sprache M über­nom­men und nur min­i­mal angepasst habe. Das war auch schon der schwierig­ste Teil der Auf­gabe.

Erweit­ern Sie nun die neu erstellte Spalte mit dem immer gle­ichen Ein­trag List durch einen Klick auf den Dop­pelpfeil und wählen Sie die Möglichkeit Auf neue Zeilen ausweit­en. Umge­hend wird bei jed­er Zeile, wo der Werte in der Spalte Anzahl größer ist als 1 um die entsprechen­den Kopi­en erweit­ert. Die Sortierung in dieser let­zten Spalte ist zwar absteigend, aber das spielt keine Rolle. Wichtig ist nur, dass die einzel­nen Pro­duk­te nicht „zer­ris­sen” wer­den.

In jedem Fall kön­nen Sie nun die let­zte, die eben erstellte Spalte löschen; sie wird nicht mehr gebraucht. Die Spal­ten Anzahl und Gewerblich kön­nen Sie je nach Geschmack bzw. Erforder­nis in der Liste belassen oder löschen. Für die Serienetiket­ten an sich wer­den die Spal­ten nicht benötigt, wenn aber eine Liste in der Form, wie sie derzeit auf dem Bild­schirm sicht­bar ist, aus­ge­druckt wer­den soll, kann das ganz hil­fre­ich sein. Jet­zt noch ein­mal Schließen & laden und sie haben das Wun­schziel erre­icht. Die bei­den Spal­ten mit den Preisen kön­nen Sie ja noch als Währung for­matieren, aber auch das kann Word beim Aus­druck für Sie erledi­gen.

▲ nach oben …