Live-Forum - Die aktuellen Beiträge
Datum
Titel
10.10.2025 15:11:39
Werte aus Zellen in Punktdiagramm, Beschriftung
10.10.2025 11:07:34
"Text in Spalten" ändert die Zahl von 21.1 in 45678
10.10.2025 10:11:05
geöffnete Excel-Datei mit variablen Namen finden
10.10.2025 09:38:53
Spalten aus 2 Tabellenblättern mit Array abgleichen
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1752to1756
1752to1756
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel transponieren mit mtrans

Forumthread: Formel transponieren mit mtrans

Formel transponieren mit mtrans
30.04.2020 18:15:28
Bernd
Die folgende Array-Formel zieht mir aus eine sehr großen Datei (150.000 Datensätze) die Daten in der Spalte
AR105 bis AR 160000 (Zahlenwerte), die jeweils einem Datum in der Spalte AD zugeordnet sind.
Hinweis: diese Formel gibt dann alle gefundenen Zahlenwerte zu einem bestimmten Daaum aus, welches ich
In die Zelle $BJ$99 von Hand eingebe.
Hier die Formel:
=INDEX($AR$105:$AR$160000;KGRÖSSTE(($AD$105:$AD$160000=$BJ$99)*(ZEILE($AD$105:$AD$160000)-104); ZÄHLENWENN($AD$105:$AD$160000;$BJ$99)+1-ZEILE(A1)))
Diese Array Formel gibt die Daten in der Spalte leider untereinander aus.
Nun wollte ich mit =MTRANS(Formel oben) die Ausgabe in einer Zeile erwirken - das funktioniert aber nicht.
Wer kann hier weiterhelfen?
Hier ist die Beispieldatei
https://www.herber.de/bbs/user/137158.xlsx
Besten Dank vorab
Bernd
Anzeige

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

Betreff
Datum
Anwender
Anzeige
AW: Formel transponieren mit mtrans
30.04.2020 18:38:48
onur
Hast du das denn probiert?
=MTRANS(DerBereichInDemDieFormelSteht)
AW: Formel transponieren mit mtrans
30.04.2020 19:10:17
Bernd
Hallo Onur,
nein, dass habe ich nicht probiert, weil mir das nicht wirklich weiterhilft.
Ich will die Daten direkt in eine Zeile (und nicht in eine Spalte) schreiben, damit ich das Datum in einer Spalte verändern kann und in den Zeilen die Zahlenwerte daneben schreibe.
Das Transponieren des Bereiches geht natürlich mit MTrans(DerBereichInDemDieFormelSteht)- dies hilft mir aber leider nicht wirklich weiter in diesem Fall.
Falls das nicht geht, muss ich ein vba programmieren, was aber recht zeitaufwändig wäre, da ich nicht so fit bin in vba.
Hoffe, du kannst mir trotzdem vielleicht weiterhelfen auf Basis der Beispieldatei...
Beste Grüße
Bernd
Anzeige
AW: Formel transponieren mit mtrans
30.04.2020 19:14:55
onur
Du willst wirklich ALLE Werte in EINER einzigen Zeile?
Deine sogenannte Beispielsdatei ist übrigens LEER.
AW: Formel transponieren mit mtrans
30.04.2020 19:27:04
Bernd
Hallo Onur,
die Beispieldatei ist nicht leer. Die Formel und Zahlen- bzw. Datumswerte sind genau dort, wie in der Formel angegeben (also ab Zeile 105 und dann nach rechts...)
Folgende Ausgabe soll passieren
Datum
xx.xx.xx und in der gleichen Zeile daneben die (30) Werte (also 30 Spalten)zu diesem Datmum
nächstes Datum und in der gleichen Zeile daneben die (30) Werte (also 30 Spalten)zu diesem Datum
Achtung, die Beispieldatei enthält natürlich nicht alle 30 Werte, da ich die Datei in dieser Größe nicht hochladen konnte. Daher müssten maximal zwei Werte ausgegeben werden in der Beispieldatei...
Hoffe, dass jetzt klarer ist, was ich will...
Beste Grüße
Bernd
Anzeige
AW: MTRANS() bedarf es nicht ...
30.04.2020 19:24:05
neopa
Hallo Bernd,
... wenn Du die Excelversion 2010 im Einsatz hast, reicht folgende Matrixfunktion(alität)sformel die keines spez. Formelabschluss wie eine klassische Matrixformel benötigt:
=WENNFEHLER(AGGREGAT(15;6;$AR105:$AR160000/($AD105:$AD160000=$BJ99);SPALTE(A99));"")
Gruß Werner
.. , - ...
Anzeige
AW: MTRANS() bedarf es nicht ...
30.04.2020 19:34:04
Bernd
Hallo Werner,
ja ich nutze Excel 2010. Die Formel ist super (alles was mit AGGREGAT gelöst werden kann, finde ich genial, ist aber nicht meine Liga :-)
Kann es sein, dass die Formel die Zahlen von unten nach oben ausgibt, also die Zahlen am Ende der Spalte zuerst ausgegeben werden?
Geht das ggf, noch anders herum?
Es ist kein größeres Problem, da du mein Problem gelöst hast, aber es interessiert mich einfach...
Besten Dank schon jetzt mal wieder!
Bernd
Anzeige
AW: dazu siehe untenstehender Beitrag owT
30.04.2020 19:41:06
neopa
Gruß Werner
.. , - ...
AW: Nachtrag hierzu ...
30.04.2020 19:40:04
neopa
Hallo Bern,
... mit dieser Formel werden die Werte vom Kleinsten zum größten sortiert gelistet. Anders herum musst Du in der Formel nur die 15 durch eine 14 ersetzen.
Wenn Du die ursprüngliche Reihenfolge beibehalten willst, wird die Formel etwas länger,. Dann bedarf es noch INDEX() und ZEILE().
Solltest Du eine ältere Version haben geht es auch, dann jedoch wieder als {}-Formel.
Dann äquivalent zur eingestellten AGGREGAT()-Formel so:
{=WENNFEHLER(KKLEINSTE(WENN($AD105:$AD160000=$BJ99;$AR105:$AR160000);SPALTE(A99));"")}
auch diese Formel dann natürlich nach rechts ziehend kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: Nachtrag hierzu ...
30.04.2020 19:50:03
Bernd
Hallo Werner,
verrätst du mir bitte noch das Geheimnis der ursprünglichen Reihenfolge von oben nach unten :-)
"Wenn Du die ursprüngliche Reihenfolge beibehalten willst, wird die Formel etwas länger,. Dann bedarf es noch INDEX() und ZEILE()."
Genau diese Formel suche ich...(eigentlich),weil die bisherige Formel auch diese bisherige Reihenfolge so ausgegeben hat...
BG
Bernd
Anzeige
AW: Nachtrag hierzu ...
30.04.2020 19:53:47
neopa
Hallo Bernd,
... dafür dann so:
=WENNFEHLER(INDEX($AR:$AR;AGGREGAT(15;6;ZEILE(AR105:AR160000)/($AD105:$AD160000=$BJ99);SPALTE(A99)));"")
und nach rechts kopieren.
Gruß Werner
.. , - ...
AW: Nachtrag hierzu ...
30.04.2020 19:55:47
Bernd
Hallo Werner,
echt "supi" mal wieder - tausend Dank - der Beitrag kann geschlossen werden...
Dir ein schönes verlängertes Wochenende trotz Corona...
Bernd
Anzeige
AW: danke, wünsche ich Dir auch owT
30.04.2020 20:03:44
neopa
Gruß Werner
.. , - ...
;
Anzeige
Anzeige

Infobox / Tutorial

Formel transponieren mit MTRANS und AGGREGAT

Schritt-für-Schritt-Anleitung

  1. Vorbereitung der Excel-Datei: Stelle sicher, dass Deine Excel-Datei die notwendigen Daten in den Spalten AD (Datum) und AR (Zahlenwerte) enthält. Die Daten sollten ab Zeile 105 bis zum Ende der Datei vorhanden sein.

  2. Eingabe der Suchkriterien: Gib das Datum, zu dem Du die Zahlenwerte abrufen möchtest, in die Zelle $BJ$99 ein.

  3. Verwendung der AGGREGAT-Funktion: Du kannst die folgende Formel verwenden, um die Daten zu transponieren:

    =WENNFEHLER(AGGREGAT(15;6;$AR$105:$AR$160000/($AD$105:$AD$160000=$BJ$99);SPALTE(A99));"")

    Diese Formel gibt Dir die Werte für das angegebene Datum in einer Zeile zurück.

  4. Formel nach rechts ziehen: Ziehe die Formel nach rechts, um alle gewünschten Werte in einer Zeile anzuzeigen.

  5. MTRANS-Funktion (optional): Wenn Du die Werte in einer anderen Anordnung benötigst, kannst Du die MTRANS-Funktion verwenden. Beachte jedoch, dass sie unter Umständen nicht die gewünschte Ausgabe liefert, wenn Du alle Werte in einer Zeile haben möchtest.

Häufige Fehler und Lösungen

  • MTRANS funktioniert nicht: Wenn die MTRANS-Funktion nicht wie gewünscht funktioniert, überprüfe, ob die Eingabewerte korrekt sind. MTRANS kann Schwierigkeiten haben, wenn Du versuchst, mehrere Werte in einer Zeile darzustellen.

  • Die Zahlenreihe wird nicht korrekt angezeigt: Stelle sicher, dass Du die Formel korrekt in die Zelle eingegeben hast und dass alle Daten im richtigen Format vorliegen.

  • Formel gibt leere Werte zurück: Überprüfe, ob das Datum in $BJ$99 korrekt eingegeben wurde und ob es übereinstimmende Werte in der Spalte AD gibt.

Alternative Methoden

Falls die MTRANS-Funktion nicht die gewünschten Ergebnisse liefert, kannst Du auch folgende Methoden ausprobieren:

  1. INDEX und AGGREGAT: 

    =WENNFEHLER(INDEX($AR:$AR;AGGREGAT(15;6;ZEILE($AR$105:$AR$160000)/($AD$105:$AD$160000=$BJ$99);SPALTE(A99)));"")

    Diese Methode gibt die Werte in der ursprünglichen Reihenfolge zurück.

  2. Matrixformel: Nutze die klassische Matrixformel, indem Du die Formel mit {} umschließt und sie mit STRG + SHIFT + ENTER bestätigst.

Praktische Beispiele

Beispiel 1: Angenommen, Du hast folgende Daten:

AD (Datum) AR (Werte)
01.01.2022 10
01.01.2022 20
02.01.2022 30

Wenn Du das Datum 01.01.2022 in $BJ$99 eingibst und die oben genannte AGGREGAT-Formel verwendest, erhältst Du:

Werte in Zeile
10, 20

Beispiel 2: Wenn Du die MTRANS-Funktion anwendest, kann das Ergebnis unerwartet sein, da sie die Werte möglicherweise nicht in der gewünschten Reihenfolge anzeigt.

Tipps für Profis

  • Verwende die AGGREGAT-Funktion: Diese Funktion ist besonders leistungsfähig, da sie leere Werte ignoriert und anpassbar ist.

  • Datenformatierung: Achte darauf, dass die Daten in den Spalten AD und AR korrekt formatiert sind, um Fehler bei der Berechnung zu vermeiden.

  • Excel-Version: Dieses Tutorial bezieht sich auf Excel 2010. Neuere Versionen bieten möglicherweise zusätzliche Funktionen, die Du nutzen kannst.

FAQ: Häufige Fragen

1. Funktioniert MTRANS auch in neueren Excel-Versionen?
Ja, die MTRANS-Funktion ist in allen modernen Excel-Versionen verfügbar, jedoch kann die Handhabung je nach Version variieren.

2. Wie kann ich sicherstellen, dass die Formel die Daten in der richtigen Reihenfolge anzeigt?
Verwende die INDEX- und AGGREGAT-Kombination, um die ursprüngliche Reihenfolge beizubehalten.

3. Was ist der Unterschied zwischen AGGREGAT und MTRANS?
AGGREGAT ermöglicht eine flexible Datenabfrage und -verarbeitung, während MTRANS hauptsächlich für das Transponieren von Daten zuständig ist.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige