Dem Ein­fall­sre­ich­tum unter­schiedlich­er Anwen­der (aber auch Pro­gram­men, die Dat­en exportieren) ist keine Gren­ze geset­zt. 🙄 So habe ich in einem Forum fol­gen­den Beitrag gefun­den, den ich auszugsweise zusät­zlich zu einem von mir erstell­ten Screen­shot hier ein­mal zitiere:

…die Tabelle umfasst ins­ge­samt 50 Namen und die jew­eili­gen Unter­felder jede Woche kommt ein Ein­trag in ein­er weit­eren Spalte hinzu.
Meine Frage wäre jet­zt kann ich die Namen irgend­wie von A‑Z Sortieren so das die Zahlen in den Spal­ten sich mit ver­schieben Aber Zeile 3–9 in Spalte A die Begriffe nicht betrof­fen sind?

Screen­shot aus dem Forum, noch unsortiert

Da rel­a­tiv schnell ein sehr fähiger Antworter-Kol­lege reagiert hat und dar­legte, dass die Struk­tur der Dat­en, so wie sie existiert aus­ge­sprochen sub­op­ti­mal ist, habe ich mich in diesem Thread im Hin­ter­grund gehal­ten und für mich und natür­lich auch für Sie drei Lösungsan­sätze erar­beit­et. Die endgültige (Zwis­chen-) Lösung bedarf anschließend noch ein­er Darstel­lung als Piv­ot­Table. Und die Dat­en kön­nen Sie hier von unserem Serv­er herun­ter­laden.

▲ nach oben …

Möglichkeit 1: In den Name-Zeilen sind alle Datum-Spalten garantiert leer

Bei solchen Her­aus­forderun­gen gilt es erst ein­mal ein Kri­teri­um her­auszufind­en, welch­es die Erken­nung der unter­schiedlichen Blöcke für den Com­put­er, für Excel möglich macht. Dass es in der Real­ität garantiert nicht das Wort Name mit ein­er ange­hängten Zif­fer ist, ver­ste­ht sich von alleine. Das wäre ja zu ein­fach. 😆 Aber es ist sehr wahrschein­lich, dass in den Spal­ten rechts des jew­eili­gen Namens nichts einge­tra­gen ist, die Datum-Spal­ten in dieser Zeile also wirk­lich leer sind. Und „wirk­lich leer” bedeutet, dass auch keine Formel in der Zelle ste­ht.

Auch wenn hier nur 3 von wahrschein­lich wesentlich mehr kalen­darischen Dat­en in Spal­ten erfasst sind ist davon auszuge­hen, dass in weit­eren einge­tra­ge­nen Tagen die Zelle jew­eils leer ist. Darum werde ich für eine Prü­fung, ob in Spalte A nun ein Name oder ein Attrib­ut (Pro­dukt) ste­ht, nur Spalte B:D auswerten. Und das werde ich (wie übri­gens auch bei den anderen bei­den Möglichkeit­en) mit Pow­er Query erledi­gen. Ich importiere also die Tabelle in den Pow­er Query Edi­tor und das stellt sich nun so dar:

Die ein­ge­le­se­nen Dat­en nach dem Import

Sie erken­nen, dass in den Zeilen mit den Namen alle weit­eren Felder leer sind, also den Wert null enthal­ten. Darauf baut auch die Logik dieser 1. Lösung auf. Um zu ver­mei­den, dass zufäl­lig eine der ersten 3 Datumss­pal­ten leer ist, obwohl dort in Spalte1 ein Nahrungsmit­tel einge­tra­gen ist, über­prüfe ich ein­fach ob alle 3 Spal­ten wirk­lich leer sind. (OK, ein Rest-Risiko bleibt den­noch, wenn auch ein geringes.)

Ein Klick auf den Menüpunkt Spalte hinzufü­gen | Benutzerdefinierte Spalte und Sie tra­gen im Dia­log bei Neuer Spal­tenname beispiel­sweise Name und bei Benutzerdefinierte Spal­tenformel fol­gende Formel ein:
if [15.02.2018]=null and [18.02.2018]=null and [25.02.2018]=null then [Spalte1] else null
wobei sie die Spal­tenna­men (das jew­eilige Datum) durch einen Dop­pelk­lick im Bere­ich Ver­füg­bare Spal­ten (rechter Kas­ten) ein­fü­gen kön­nen. Das stellt sich nun so dar:

Die im Dialogfen­ster einge­tra­gene Formel

Sie acht­en dabei auf exakt die gezeigte Groß- Klein­schrei­bung. Als Ergeb­nis wird in die neu erstellte Spalte Name entwed­er der Name oder der Wert null einge­tra­gen. Anschließend erstellen Sie eine weit­ere Benutzerdefinierte Spalte und erar­beit­en sich beispiel­sweise selb­st die Formel, wo sie die Spalte Name auf null prüfen und falls das zutrifft den Wert aus [Spalte1] ein­tra­gen lassen oder aber null. Der Name der neuen Spalte ist Attrib­ut. Alter­na­tiv wählen Sie Spalte hinzufü­gen | Bed­ingte Spalte und geben dort im Dia­log diese Werte ein:

Der Dia­log mit den aus­ge­füll­ten Werten

Allerd­ings wer­den sie keinen Erfolg haben, wenn sie bei Aus­gabe den Text Spalte1 eingeben. Um genau diesen Ein­trag dort einzufü­gen Klick­en Sie auf das Sym­bol ein­er Tabelle links des Kom­bi­na­tions­feldes, wählen dort Spalte auswählen aus und nach einem Klick in das Kom­bi­na­tions­feld wählen Sie dort den Ein­trag Spalte1.

In bei­den Fällen erre­ichen Sie das Ziel, dass in der neu erstell­ten Spalte entwed­er der Wert null ste­ht oder der Name des entsprechen­den Lebensmittels/Produkts. Damit sind sie fast am Ende des Zwis­chen­schritts ange­langt. Löschen Sie nun Spalte1, markieren sie die bei­den let­zten Spal­ten und ver­schieben diese an den Anfang. Das ist zwar funk­tionell nicht wichtig, dient aber der Optik. Markieren Sie die Über­schrift Name, Recht­sklick in die Über­schrift und im Kon­textmenü Aus­füllen | Nach unten. Ruck­zuck ste­ht in jed­er Zeile der Spalte Name der kor­rek­te Wert.

Jet­zt kommt wirk­lich der End­spurt … 😎  Fil­tern Sie die Spalte Attrib­ut dergestalt, dass die Zeilen mit dem Wert null ent­fer­nt wer­den. Markieren Sie die Spal­ten Name und Attrib­ut, Recht­sklick in eine der bei­den Über­schriften und Andere Spal­ten ent­piv­otieren. Ruck­zuck sind aus den bis­lang 21 Zeilen mit 5 Spal­ten nun 63 Zeilen in 4 Spal­ten gewor­den. Jet­zt nur noch die Über­schrift Attribut.1 zu Datum ändern.Und das ist nun (fast) eine geeignete Daten­ba­sis für die später zu erstel­lende Piv­ot­Table.

▲ nach oben …

Möglichkeit 2: Die Anzahl der Begriffe/Produkte ist bei jedem Namen gleich

In diesem Beispiel ist es ja so, dass unter­halb jedes Namens die gle­iche Anzahl von Argu­menten bzw. Pro­duk­ten aufge­führt ist. Wenn das gewährleis­tet ist, bietet sich eine aus der Excel-Sicht opti­male Möglichkeit. Da es stets 7 Pro­duk­te sind kann gesagt wer­den, dass jew­eils die 1., die 9., Die 17. Zeile, etc. den Namen in Spalte A enthält, alle anderen Zeilen des Daten­bere­ichs die Beze­ich­nung des Nahrungsmit­tels. Darauf auf­bauend lässt sich her­vor­ra­gend berech­nen, ob nun in der entsprechen­den Zeile ein Name ste­ht oder nicht.

Nach dem Import der Dat­en in Pow­er Query geht der Weg wie schon oben beschrieben über Spalte hinzufü­gen | Benutzerdefinierte Spalte und im 1. Schritt erzeu­gen Sie eine Indexspalte, in dem sie ein­fach auf die gle­ich­namige Schalt­fläche Klick­en. Danach eine weit­ere Benutzerdefinierte Spalte und als Neuer Spal­tenname geben Sie Name ein. Fügen Sie darunter fol­gende Formel ein:
if Number.Mod([Index],8)=0 then[Spalte1] else null
und umge­hend wird in der neu erzeugten Spalte der Name (aus der ersten Spalte) einge­tra­gen oder null. Acht­en Sie auch hier auf die Groß- Klein­schrei­bung, son­st han­deln Sie sich eine Fehler­mel­dung ein. – Eine weit­ere neue Spalte wird eine Bed­ingte Spalte sein, die sie mit diesen Werten aus­füllen bzw. auswählen:

Der Dia­log mit den aus­ge­füll­ten Werten

Wie bere­its im ersten Part erwäh­nt dür­fen Sie im Feld Aus­gabe nicht den Text Spalte1 ein­tra­gen son­dern Sie Klick­en auf das Sym­bol direkt links des Kom­bi­na­tions­feldes, wählen dort im Kon­textmenü Spalte auswählen, Klick­en dann in das leere Textfeld und wählen dort im Kon­textmenü den Namen der gewün­scht­en Spalte, hier: Spalte1.

Markieren Sie nun die Spalte Name durch einen Klick in die Über­schrift. Recht­sklick in die Über­schrift und im Kon­textmenü wählen Sie Aus­füllen | Nach unten. Nun ist jed­er Zeile der kor­rek­te Name zuge­ord­net.

Markieren Sie nun die Spal­ten Spalte1 und Index und löschen Sie diese auf beliebige Weise. Der besseren Optik wegen markieren Sie nun nacheinan­der die Spal­ten Name und Attrib­ut und ver­schieben diese gemein­sam an den Anfang; das geht entwed­er über das Menü Trans­formieren oder indem sie in eine der markierten Über­schriften einen Recht­sklick machen und die bei­den Spal­ten dann An den Anfang ver­schieben. Und ja, es geht auch mit der Maus …  😉 

Wie auch im 1. Durch­gang sind die Zeilen, wo nur der Name enthal­ten ist flüs­siger als flüs­sig (über­flüs­sig). 😉 Da durch die math­e­ma­tis­che Auswer­tung garantiert wird, dass die richti­gen Zeilen mit dem entsprechen­den Wert aus­ge­füllt wur­den, kön­nen Sie die Spalte Attrib­ut als Argu­ment für den Fil­ter zum löschen über­flüs­siger Zeilen ver­wen­den. Fil­tern Sie diese Spalte so, dass die null – Werte ent­fer­nt wer­den.

Markieren Sie die ersten bei­den Spal­ten (Name und Attrib­ut) und anschließend über einen Recht­sklick in eine der bei­den Über­schriften Andere Spal­ten ent­piv­otieren. Dann nur noch die Über­schrift Attribut.1 auf Datum ändern und sie sind erst ein­mal am Ziel, nach­dem sie Schließen & laden aus­ge­führt haben.

▲ nach oben …

Möglichkeit 3: Unterschiedliche Zahl der Einträge und mehr …

„Sauber” ist es gewiss nicht, wenn die Zahl der Ein­träge unter­halb der einzel­nen Namen unter­schiedlich ist. Zumin­d­est ist es über­sichtlich­er und sta­tis­tisch klar­er, wenn einem Namen stets die gle­iche Zahl an Argu­menten zuge­ord­net ist. Aber mal sind es vielle­icht nur 2 Pro­duk­te oder 7 oder 10 … Hier stellt sich die Frage, ob da mit vertret­baren Mit­teln eine Auswer­tung möglich ist. In solchen Fällen fällt dann automa­tisch der vorher geschilderte Weg der Berech­nung aus. Aber Pow­er Query ist in vie­len Lebensla­gen doch einen sehr fleißiges und dank des Kön­nens der Per­son vor dem Bild­schirm auch ein intel­li­gentes Helfer­lein.  💡 

Das Prinzip der gle­ich beschriebe­nen Vorge­hensweise ist sehr ähn­lich dem der im ersten Beispiel, allerd­ings hier mit mehr Aufwand ver­bun­den. In dieser Datei habe ich nicht nur sin­nvollere Namen ver­wen­det son­dern auch unter­halb der Namen Ein­träge ent­fer­nt und auch neue hinzuge­fügt. Nach dem Import der Tabelle wer­den Sie als erstes ein Dup­likat der Abfrage erstellen. Das geht beispiel­sweise im Menü Start | Ver­wal­ten | Duplizieren.

Im fol­gen­den Schritt über­prüfen Sie, ob in ein­er der ersten 3 Datum-Spal­ten jed­er Zeile min­destens ein Feld wirk­lich leer ist. Dazu nach bekan­ntem Muster eine Spalte hinzufü­gen | Benutzerdefinierte Spalte und dort diese Formel ein­tra­gen:
[15.02.2018]=null or [18.02.2018]=null or [25.02.2018]=null
und mit OK bestäti­gen. Das Ergeb­nis ist TRUE oder FALSE und sollte wiedergeben, ob in Spalte1 ein Name ste­ht oder nicht. Jet­zt fil­tern Sie diese neu erstellte Spalte, damit auss­chließlich der Wert FALSE erhal­ten bleibt.

Löschen Sie nun alle Spal­ten ausgenom­men der Spalte1 und ent­fer­nen Sie auf beliebige Weise alle Dup­likate. Ich würde mir die Liste aller verbliebe­nen Unikate nun entwed­er abschreiben oder vorzugsweise aus­druck­en, denn genau diese Beze­ich­nun­gen brauche ich (und natür­lich auch Sie)  im fol­gen­den Schritt. Danach kön­nen Sie auch diese Abfrage löschen, sie wird nicht mehr benötigt. Es schadet aber auch nicht, wenn sie aus Grün­den der Sicher­heit die Abfrage beste­hen lassen.

Wech­seln Sie zu Tabelle1 und fügen Sie dort eine Benutzerdefinierte Spalte ein. Die fol­gende Formel ist recht lang, jedoch vom Auf­bau logisch: Es wird abge­fragt, ob ein­er der in Tabelle1 (2) gefilterten Begriffe in Spalte1 dieser Abfrage enthal­ten ist. Wenn das der Fall ist, dann wird dieses Pro­dukt über­nom­men son­st kommt null in das Feld hinein:
= if [Spalte1]="Apfel" or [Spalte1]="Birne" or [Spalte1]="Kartoffel" or [Spalte1]="Nudel" or [Spalte1]="Erdbeere" or [Spalte1]="Heidelbeere" or [Spalte1]="Stachelbeere" or [Spalte1]="Erdnuss" or [Spalte1]="Karotte"
then [Spalte1] else null

Falls Sie der Spalte im Formel-Dia­log noch keine Namen für die neue Spalte vergeben haben, kön­nen Sie das jet­zt auch in der Abfrage nach­holen; das kön­nte hier beispiel­sweise Pro­duk­te sein.

Um die Namen in eine getren­nte Spalte zu bekom­men, wiederum Spalte hinzufü­gen | Benutzerdefinierte Spalte, Neuer Spal­tenname sollte Name sein und als Formel geben Sie ein:
if [Produkte]=null then [Spalte1] else null
Übri­gens: wenn Sie jet­zt bei Angewen­dete Schritte auf diesen zulet­zt aus­ge­führten Schritt einen Dop­pelk­lick durch­führen wer­den sie nicht den Dia­log mit der Formel sehen son­dern jenen Dia­log der sich auf tun, wenn sie auf Bed­ingte Spalte Klick­en.

Der Rest ist prinzip­iell wie gehabt: Löschen Sie Spalte1 und ver­schieben sie die Spal­ten Pro­duk­te und Name dergestalt an den Anfang, dass die Spalte mit den Namen zu Anfang ste­ht (das geht bei diesen weni­gen Spal­ten übri­gens auch recht gut per Hand). Das hat zwar keine Auswirkung auf die endgültige Auswer­tung, macht sich aber in meinen Augen bess­er so. Nun wieder die Spalte Name nach bekan­ntem Muster nach unten aus­füllen. Ent­piv­otieren und auch dieses Ziel ist erre­icht. Aber bevor sie Schließen & laden anklick­en wer­den sie natür­lich den Namen der vor­let­zten Spalte von Attrib­ut auf Datum ändern.

Und fällt da etwas auf? Ein kalen­darisches Datum, welch­es links aus­gerichtet ist bedeutet nor­maler­weise Text und kein Datum. So ist das auch jed­er Fall, darum ändern Sie den Daten­typ auf Datum. Und das mit dem Daten­typ gilt naturgemäß auch für die bei­den vorheri­gen Arbeitsabläufe.  💡 

Fer­tig. Oder doch nicht? Nein natür­lich nicht! Denn der Aus­gangspunkt, die eigentliche Auf­gabe war ja, dass die Namen alpha­betisch auf­steigend sortiert sein sollen. Und egal, welchen der 3 Wege sie beschrit­ten haben, dieser entschei­dende Schritt fehlte. Darum sortieren sie in der kün­ftig zu ver­wen­den­den Abfrage oder auch in allen anderen Queries die Spalte Name noch ein­mal auf­steigend. Jet­zt haben Sie wirk­lich fer­tig.  😎 

▲ nach oben …

Die Wunsch-Darstellung

Auch wenn in Excel ganz klar der Satz „Form fol­lows func­tion” gilt lässt sich manch­mal mit legit­i­men Bor­d­mit­teln und ohne bedeu­tende Ein­bußen der Per­for­mance das Ausse­hen der Dat­en dem Wun­schdenken mehr oder sog­ar prak­tisch vol­lkom­men erfüllen.

Egal welchen Weg Sie gegan­gen sind, die Dat­en sind so auf­bere­it­et, dass in 4 Spal­ten der Name der Per­son, des Pro­duk­ts, das Datum und der entsprechende Wert ste­hen. Und das ist die opti­male Basis für eine Piv­ot­Table. Und wenn Sie direkt nach dem Spe­ich­ern der Abfrage ins Menüband schauen wird Ihnen auf­fall­en, dass es dort den Punkt Mit Piv­ot­Table zusam­men­fassen bere­its gibt. Ein Klick darauf und sie übernehmen ein­fach erst ein­mal die Vor­gaben des Dialogs.

Ziehen Sie nun im recht­en Seit­en­fen­ster das Feld Name in den Bere­ich Zeilen, gle­icher­maßen Pro­duk­te. Das Feld Datum ziehen Sie in den Bere­ich Spal­ten und das verbleibende Feld Wert kommt in den Bere­ich Werte. Prinzip­iell ist die Auswer­tung nun fer­tig. Ich werde mit Ihnen nun noch einige kleine Kor­rek­turen vornehmen, damit das endgültige Bild weit­ge­hend den Vorstel­lun­gen des Fragestellers entspricht.

Klick­en Sie erst ein­mal auf den Menüpunkt Entwurf | Gesamtergeb­nisse | Für Zeilen und Spal­ten deak­tiviert. Damit ver­schwinden schon ein­mal die automa­tisch berech­neten Gesamt­sum­men. Nun Tei­l­ergeb­nisse | Tei­l­ergeb­nisse nicht anzeigen und das sieht schon richtig schick aus. Und das Tüpfelchen auf dem i ist gewiss, wenn sie Bericht­slay­out | Im Gliederungs­for­mat anzeigen wählen. Je nach Geschmack kön­nen Sie nun noch die Zeile 3 Aus­blenden und die Bre­ite der Spalte B auf das notwendi­ge Maß ver­ringern:

Das Endgültige Ergeb­nis als Piv­ot­Table

Ich bin fest davon überzeugt, dass dieses Ergeb­nis den Wün­schen des Fragestellers sehr, sehr nahe kommt, wenn nicht sog­ar seine Vorstel­lung kom­plett erfüllt.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrer­seits (z.B. 1,00€ bis 2,00€) freuen …