Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1744to1748
1744to1748
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Matrix Formel: Datum zuordnen

Forumthread: Matrix Formel: Datum zuordnen

Matrix Formel: Datum zuordnen
11.03.2020 17:56:41
Patrick
Hallo liebe Community,
mein erster Auftritt in einem Forum und ich hoffe sehr, dass ihr mir helfen könnt.
Ich habe selbst schon viel mit Matrix-Formeln und Index Funktionen herumgebastelt aber bin nie auf das richtige Ergebnis gekommen :(
Folgende Situation:
Ich habe zwei definierte Tabellen.
Nun brauche ich in Spalte D von Tabelle 1 eine Formel die folgendes bewirkt:
1. Vergleiche Spalten "Artikelnummer" in Tabelle 1 & Tabelle 2
2. Wenn Artikelnummer identisch vergleiche Spalten "Datum" in Tabelle 1 & Tabelle 2
3. Das Datum in Tabelle 1 muss kleiner sein als in Tabelle 2 aber nicht größer als das nächstgrößere Datum in Tabelle 2.
4. Wenn dieses Datum gefunden ist den zugehörigen Wert aus Spalte "Preis" Tabelle 2 in Spalte "Preis" Tabelle 1 übertragen.
Ich habe mal eine Beispiel Datei angehangen. Damit wird es hoffentlich etwas verständlicher.
Ich bedanke mich schon einmal im voraus für mögliche Hilfestellungen!
LG
Patrick
https://www.herber.de/bbs/user/135787.xlsx
Anzeige

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Matrix Formel: Datum zuordnen
11.03.2020 18:03:25
SF
Hola,
als Matrixformel:
=VERWEIS(2;1/($G$5:$G$10&$H$5:$H$10=A2&MAX(WENN(($G$5:$G$10=A2)*($H$5:$H$10<B2);$H$5:$H$10))); $I$5:$I$10)
Gruß,
steve1da
AW: Matrix Formel: Datum zuordnen
11.03.2020 18:13:05
Patrick
Es funktioniert!
Vielen Dank für die super schnelle Antwort!
AW: mit INDEX() und AGGREGAT() und ...
11.03.2020 18:07:21
neopa
Hallo Patrick,
... zuvor die Daten in Tabelle2 aufwärts sortieren nach Datum.
Dann in D2;
=INDEX(Tabelle2[Preis];AGGREGAT(14;6;(ZEILE(Tabelle2)-ZEILE(Tabelle2[#Kopfzeilen]))/
(Tabelle2[Artikelnummer]=[@Artikelnummer])/(Tabelle2[Gültigkeitsdatum]&lt=[@Datum]);1))
Gruß Werner
.. , - ...
Anzeige
AW: mit INDEX() und AGGREGAT() und ...
11.03.2020 18:15:45
Patrick
Hallo Werner,
die Formel deines Vorgängers funktioniert auch ohne die Sortierung daher würde ich diese vorziehen.
Trotzdem vielen Danke für die Mühe und die schnelle Rückmeldung!
Grüße
Patrick
;
Anzeige
Anzeige

Infobox / Tutorial

Matrix Formel: Datum zuordnen in Excel

Schritt-für-Schritt-Anleitung

  1. Tabellenstruktur erstellen:

    • Erstelle zwei Tabellen in Excel: Tabelle 1 und Tabelle 2. Tabelle 1 sollte die Spalten "Artikelnummer", "Datum" und "Preis" haben. Tabelle 2 sollte die Spalten "Artikelnummer", "Gültigkeitsdatum" und "Preis" enthalten.

  2. Formel in Tabelle 1 einfügen:

    • Gehe zu Zelle D2 in Tabelle 1 und füge folgende Matrix-Formel ein: 
      =VERWEIS(2;1/($G$5:$G$10*$H$5:$H$10=A2*MAX(WENN(($G$5:$G$10=A2)*($H$5:$H$10<B2);$H$5:$H$10))); $I$5:$I$10)
    • Achte darauf, die Zellenbereiche entsprechend deiner Daten anzupassen.

  3. Formel als Matrixformel eingeben:

    • Drücke STRG + SHIFT + ENTER, um die Formel als Matrixformel einzugeben.

  4. Preise in Tabelle 1 aktualisieren:

    • Die Formel wird die zugehörigen Preise aus Tabelle 2 in Tabelle 1 übertragen, vorausgesetzt die Bedingungen sind erfüllt.

Häufige Fehler und Lösungen

  • Fehler: #NV oder #WERT!

    • Lösung: Überprüfe, ob die Artikelnummern und Daten korrekt in beiden Tabellen eingegeben sind. Stelle sicher, dass die Matrixformel korrekt eingegeben wurde.

  • Fehler: Falsche Preise angezeigt

    • Lösung: Stelle sicher, dass die Daten in Tabelle 2 nach Datum aufsteigend sortiert sind, um genaue Ergebnisse zu gewährleisten.

Alternative Methoden

Eine alternative Möglichkeit zur Verwendung von Matrix-Formeln ist die Kombination von INDEX() und AGGREGAT(). Die Formel könnte wie folgt aussehen:

=INDEX(Tabelle2[Preis];AGGREGAT(14;6;(ZEILE(Tabelle2)-ZEILE(Tabelle2[#Kopfzeilen]))/(Tabelle2[Artikelnummer]=[@Artikelnummer])/(Tabelle2[Gültigkeitsdatum]<=[@Datum]);1))

Diese Methode benötigt keine Sortierung der Daten in Tabelle 2, bietet jedoch ähnliche Ergebnisse.

Praktische Beispiele

Angenommen, du hast folgende Daten:

Tabelle 1:

Artikelnummer Datum Preis
1001 01.01.2023
1002 15.01.2023

Tabelle 2:

Artikelnummer Gültigkeitsdatum Preis
1001 10.01.2023 20€
1001 20.01.2023 25€
1002 12.01.2023 30€

Die Formel in Tabelle 1 wird den Preis von 20€ in die entsprechende Zelle in Spalte "Preis" einfügen.

Tipps für Profis

  • Verwende benannte Bereiche: Benannte Bereiche können die Lesbarkeit deiner Formeln erhöhen und die Wartung erleichtern.
  • Überprüfe die Datentypen: Achte darauf, dass die Artikelnummern und Daten in beiden Tabellen im gleichen Format vorliegen (z. B. als Text oder Datum).
  • Testen mit kleineren Datensätzen: Wenn du mit großen Datenmengen arbeitest, teste deine Formeln zunächst mit kleineren Datensätzen, um sicherzustellen, dass sie wie gewünscht funktionieren.

FAQ: Häufige Fragen

1. Wie kann ich die Formel anpassen, wenn sich die Anzahl der Zeilen ändert?
Du kannst die Bereiche in der Formel dynamisch gestalten, indem du die gesamte Spalte angibst, z. B. $G:$G anstelle von $G$5:$G$10.

2. Funktioniert diese Methode in älteren Excel-Versionen?
Ja, die genannten Formeln funktionieren in Excel 2010 und neueren Versionen. Beachte jedoch, dass die Verwendung von Matrixformeln in älteren Versionen etwas anders sein kann.

Beliebteste Forumthreads (12 Monate)

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