Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 00:20:14
Bestimmte Spalten als xls exportieren per Makro
23.04.2024 22:08:10
Auflisten in Spalte VBA
23.04.2024 21:33:46
Nur farbige/nicht farbige Zellen markieren
23.04.2024 21:29:56
Bestimmte Zellen auf andere Arbeitsblätter kopieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1860to1864
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1860to1864
1860to1864
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel -> Spalten untereinander

Formel -> Spalten untereinander
08.12.2021 14:30:04
Kulo
Liebe Excelianer,
ich versuche mich mal wieder an einer Exceltabelle um einen Kalender mit Terminen zu erstellen.
Der Kalender hat pro Tag mehrere Spalten. Und da fangen meine Probleme an:
Ich habe es geschafft, alle Termine mit gleichem Text in jeweiles eine einzelne Spalte zu platzieren.
Es gibt fünf Kategorien und damit habe ich jetzt fünf Ergebnisspalten. Jetzt hätte ich aber gern diese fünf Spalten in einer Spalte untereinander dargestellt. Ohne Sortierung und ohne Leerstellen.
https://www.herber.de/bbs/user/149679.xlsx
Könnte sich das von Euch mal jemand anschauen?
Ich würde mich sehr freuen.
Bis dahin
Viele Grüße
Kulo

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: da gibt es verschiedene Möglichkeiten ...
08.12.2021 14:59:47
neopa
Hallo Kulo,
... da wäre zunächst eine Formellösung. Da ich Deine XL-Version nicht im Einsatz habe, kann ich die dort vorhandenen neueren neueren Funktionen nicht nutzen, womit vielleicht eine leichtere Methode möglich wäre. Ohne diese würde ich so mit Hilfsspalte arbeiten.
Aber es geht auch ganz ohne Formeln und zwar mit der Funktionalität, die Du unter dem Menü Daten und da unter Abrufen und transformieren findest. Mehr dazu findest Du im Internet unter dem Begriff Power Query. Sieh z.B. mal hier: https://www.youtube.com/watch?v=hEI5xckBKs8
Für Dein Beispiel würde ich damit 5 kleine Abfragen erstellen (eine je Terminspalte, in der nur die Zellen ohne Datumswert ausgefiltert werden) und diese dann durch anfügen zum angestrebten Ergebnis zusammenfügen.
Gruß Werner
.. , - ...
Anzeige
AW: Formel -> Spalten untereinander
08.12.2021 18:19:44
Piet
Hallo
die nach meiner Ansicht einfachste Lösung ist eine VBA Lösung. Da steht das Datum als Wert in der Spalte M, ohne Formeln.
https://www.herber.de/bbs/user/149689.xlsm
mfg Piet
AW: Formel -> Spalten untereinander
08.12.2021 18:38:58
Kulo
Hallo Werner, hallo Piet,
vielen Dank für Eure Unterstützung.
Ich habe den Link bzw. die Datei angeschaut.
Trotzdem wollte ich mein Anliegen absichtlich mit Excelformeln lösen.
Mit Pivot habe ich bis jetzt überhaupt keine Erfahrungen und mit VBA würde ich auch eine Schleife bauen.
Mit Excelformeln habe ich aber meinen Hirnschmalz zum kochen gebracht... ;-) Da fehlt mir noch EINIGES! an Grundlagen.
Ich hänge mal meine Datei an. Sie macht, was ich mir vorgestellt habe und eventuell kann man da ja weitere Ideen entwickeln.
https://www.herber.de/bbs/user/149690.xlsx
Nochmals Vielen Dank für Eure Hilfe und Anregung.
Viele Grüße und noch einen schönen Abend.
Kulo
Anzeige
AW: es geht einfacher ...
08.12.2021 19:56:34
neopa
Hallo Kulu,
... Deine Hilfsspaltenformel in Q2 so: =SUMME(Q1;ANZAHL(INDEX(G:K;;ZEILE(P1)))) und diese nach unten kopieren.
Und vor allem viel einfacher geht es mit PQ (Power Query, nicht Pivot) als ich heute Nachmittag aufgezeigt hatte.
6 Schritte in nur einer Abfrage reichen.
- Daten einlesen
- alle Spalten entpivotieren,
- zunächst Spalte "Atrribut" sortieren und danach "Wert"
- "Wert" als "Datum" formatieren
- Überschriften umbenennen in "Termin" und Datum
- Ergebnis in Tabelle abspeichern
Gruß Werner
.. , - ...
AW: es geht einfacher ...
09.12.2021 11:51:31
Kulo
Hallo Werner,
vielen Dank nochmal für Deine Ergänzung.
Wie kommt man auf solche Formeln? Genial!
Hab ich gleich in meiner Originaldatei umgesetzt.
Würdest Du Dir nochmal meine Datei anschauen. Ich hab da wieder einen Wunsch für eine Formel.
https://www.herber.de/bbs/user/149707.xlsx
Ich würde mich sehr freuen, besonders, wenn Du Zeit für eine kurze Erläuterung dazu hättest.
Viele Grüße
Kulo
Anzeige
AW: die von Dir gesuchte Formel ...
09.12.2021 15:34:47
Dir
Hallo Kulo,
... würde ich wie untenstehend definieren, wenn in Tabelle 1 auch ältere Datumswerte stehen können und in Zeile4 nicht die Überschriften mit Deiner Formel aus der Überschriftenzeile in Tabelle1 übernimmt. Denn dann reicht ja einfach in Zeile 6 eine analog in Zeile 4
Allgemein also z.B. in B6 so:
=WENNFEHLER(AGGREGAT(15;6;Tabelle1!$G$2:$K$99/(Tabelle1!$G$2:$K$99&gtHEUTE())/(SPALTE(Tabelle1!$G$2:$K$99)=VERGLEICH(B4;Tabelle1!1:1;0));1);"") und nach rechts kopieren.
Gruß Werner
.. , - ...
AW: die von Dir gesuchte Formel ...
09.12.2021 18:13:13
Dir
Hallo Werner,
ich bin noch dabei, die Formel aufzudröseln. Funktioniert aber perfekt. ;-)
Vielen Dank.
Ich hätte aber noch eine Frage zu der Formel:
"=VERGLEICH(B4;Tabelle1!1:1;0)" scheint eine Matrix über alle Zellen der ersten Zeile aufzuspannen. Wie weit reicht die Spanne nach rechts? Wirklich bis zur maximalen Spaltenanzahl eines Tabellenblattes? (konnte bei F9 nicht alle Werte zählen)
Diese Formel hat den Vorteil, dass sie alle Spalten im Blatt zählt und mann braucht keine Berichtigung beim Startwert.
Aber ist dass nicht "rechenintensiv"?
Wieso weiß Excel, dass es sich hierbei um die Zeile handelt? Wie würde es aussehen, wenn man die Matrix über alle Zellen in der ersten Spalte spannen möchte?
Viele Grüße und einen schönen Abend.
Kulo
Anzeige
AW: zu Deinen Nachfragen ...
09.12.2021 19:02:38
neopa
Hallo Kulo,
... hier in Kürze:
- zu: 

=VERGLEICH(B4;Tabelle1!1:1;0)  scheint eine Matrix über alle Zellen der ersten Zeile aufzuspannen. Wie weit reicht die Spanne nach rechts? Wirklich bis zur maximalen Spaltenanzahl eines Tabellenblattes?
Ja
- zu: 

ist dass nicht "rechenintensiv"
ja und nein. Hier nicht wirklich, weil der Suchwert schon in einen der ersten Zellen gefunden wird. Anders sieht es aus, wenn sehr viele Daten in der Zeile stehen und evtl. sogar "unsortiert" und der Suchwert nicht wirklich gefunden werden kann. Dann muss mehr "gerechnet" werden. Allerdings fällt auch das bei nur nur einer zu untersuchenden Zeile nicht wirklich ins Gewicht.
-zu: 

Wieso weiß Excel, dass es sich hierbei um die Zeile handelt?
das geht aus der Definition der Suchmatrix, hier: Tabelle1!1:1 hervor. Bei Tabelle1!54:54 würde die Zeile 54 durchsucht.
-zu: 

Wie würde es aussehen, wenn man die Matrix über alle Zellen in der ersten Spalte spannen möchte?

Dann einfach so: =VERGLEICH([Dein Suchwert];A:A;0) wenn Du exakt den angegeben Suchwert finden möchtest.
Gruß Werner
.. , - ...
Anzeige
AW: die von Dir gesuchte Formel ...Zusatzfrage
09.12.2021 18:19:24
Dir
...und wieso ist Aggregat 15 zu verwenden, wenn ich bei meiner Formel mit Kgrösste zum Ziel kam?
AW: nun dazu noch ...
09.12.2021 19:12:40
neopa
Hallo noch einmal,
... mit dem Argument 15 wird ja, wie Du gesehen hast, der Kleinste Wert aus dem 3. Argument von AGGREGAT() ermittelt. Durch den Formelteil für das 3. Argument wird nur der Kleinste durch die Ermittlung des 1. größeren Wertes als HEUTE() ermittelt.
In der von Dir eingesetzten Formel, werden dagegen alle Daten die größer gleich dem von HEUTE() sind, zunächst gezählt (und das sind viel mehr als 1) und dessen Teilergebniswert der Funktion KGRÖSSTE() als 2. Argument übergeben. Das Ergebnis ist kein MAXwert. Es so eben nur der x.-größte Wert (oder anders gesagt von z.B. 15 Werten ist es der 15. Wert).
Gruß Werner
.. , - ...
Anzeige
AW: nun dazu noch ...
10.12.2021 10:07:38
Kulo
Hallo Werner,
vielen vielen Dank für Deine Mühe und Erläuterungen.
Jetzt hab ich wieder zu tun.... ;-)
Meine Originaltabelle hab ich übrigens soweit fertig.
Nichts aufregendes: es ist ein Müllkalender geworden. :-)))
Dinge, die die Welt nicht braucht. Aber zum Lernen reichts aus...
Also Danke nochmal und einen schönen Tag.
Kulo
AW: gerne und wünsche ich auch owT
10.12.2021 10:24:39
neopa
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige