Microsoft Excel

Herbers Excel/VBA-Archiv

INDEX und VERGLEICH neueste Änderung anzeigen


Betrifft: INDEX und VERGLEICH neueste Änderung anzeigen von: Kevin
Geschrieben am: 19.06.2017 14:38:58

Hallo zusammen,

ich möchte eine Adressliste erstellen, bei der Änderungen automatisch übernommen werden. Hierzu gibt es ein Sheet "Änderungen". Hier wird in Spalte A die Mitglieds-ID eingetragen, in Spalte D das Änderungsdatum, in Spalte E das zu ändernde Feld (aus DropDown-Liste), in Spalte F der alte Wert (nur für die spätere Nachvollziehbarkeit) und in Spalte G der neue Wert.
In einem anderen Sheet "gesamt" soll dann mittels der Mitglieds-ID und dem jeweiligen Feld (Spaltenüberschrift) der aktuellste Wert aus "Änderungen" ermittelt werden, also der bei dem ID und Feldname übereinstimmen und in dieser Teil-Matrix das größte Änderungsdatum.

Mit folgender Formel funktioniert die Sache zwar, aber nur wenn das Sheet "Änderungen" nach Änderungsdatum immer richtig sortiert ist:
=INDEX(Änderungen!$A:$G;VERGLEICH(Gesamt!$A7&Gesamt!B$6;Änderungen!$A$7:$A$20&Änderungen!$E$7:$E$20; 0)+6;7)

Das ganze soll jedoch auch unsortiert richtige Ergebnisse liefern.
Im Beispiel soll Werner Müller also im Gleisweg 4 wohnen, und Petra Maier soll Petra Huber heißen.

Ich habe im Internet schon gesucht und folgende Seiten gefunden, die mir bei diesem Problem aber leider nicht helfen konnten:
http://www.tabellenexperte.de/besser-als-sverweis-alle-werte-finden/
https://www.herber.de/forum/archiv/788to792/788845_SVERWEIS_nicht_nur_den_erstgefundenen_ausgeben.html

Ich hoffe ich hab mich einigermaßen verständlich ausgedrückt, und ihr könnt mir helfen.
Hier noch meine Beispieldatei:
https://www.herber.de/bbs/user/114359.xlsx

Viele Grüße,
Kevin

  

Betrifft: AW: INDEX und VERGLEICH neueste Änderung anzeigen von: Christian
Geschrieben am: 19.06.2017 16:00:23

Hallo,
schau mal hier:
http://www.excelformeln.de/formeln.html?welcher=286

Gruß
Christian


  

Betrifft: AW: dazu nutze besser VERWEIS() und ... von: ... neopa C
Geschrieben am: 20.06.2017 09:37:22

Hallo Kevin,

... für die automatische Erfassung der ID-Nummern die Funktion AGGREGAT(). Diese gibt es zwar erst ab Version 2010, aber ich vermute Du hast mit Deiner Versions-Angabe "10 - Office XP", diese auch gemeint, denn mit Office XP hättest Du keine XLSX-datei zur Verfügung stellen können.

In Gesamt!A7:

=WENNFEHLER(AGGREGAT(15;6;Änderungen!A$7:A$99/(Änderungen!A$7:A$99>--WECHSELN(A6;"ID";0));1);"") 
und nach unten kopieren.

Dann in Gesamt!B7:

=WENNFEHLER(VERWEIS(9;1/(Änderungen!$A$1:$A$99=$A7)/(Änderungen!$E$1:$E$99=B$6);Änderungen!$G:$G);"")

und nach unten und rechts kopieren.

Gruß Werner
.. , - ...


  

Betrifft: AW: dazu nutze besser VERWEIS() und ... von: Kevin
Geschrieben am: 20.06.2017 13:39:36

Hallo Christian, hallo Werner,

vielen Dank für eure gute und schnelle Hilfe.
Ja stimmt bei der Excel-Version hab ich was falsches ausgewählt, ich nutze Office 2010 unter Win7.

@Christian: Die Formel aus dem Link sieht auf den ersten Blick zielführend aus, da im Beispiel aber nur nach einem Kriterium (der ID) gesucht wurde und bei mir ID und Feldname überprüft werden müssen, hab ich Werners Formel genommen. Bestimmt könnte man die Index-Formel auch noch entsprechend anpassen.

@Werner: kannst du mir deine Formeln etwas erklären? Sie funktionieren super, genau was ich brauche. Aber ich verstehe insbesondere die AGGREGAT-Funktion noch nicht. (Hab mich mal kurz eingelesen, aber die scheint ja wirklich extrem mächtig zu sein).

Also nochmal vielen Dank, mein Verein wird begeistert sein;)
Gruß,
Kevin


  

Betrifft: AW: das hast Du richtig erkannt ... von: ... neopa C
Geschrieben am: 20.06.2017 14:47:28

Hallo Kevin,

... die Funktion ist sehr mächtig (wenn auch noch teils unvollkommen) aber in der Form, wie ich sie seit Jahren nutze, noch immer nicht richtig dokumentiert. Ich müsste jetzt ein vielfaches an Zeit investieren, um diese Dir so erklären, dass nicht nur Du sondern auch User die noch nicht mit den Excel-Level "gut" haben. Voraussetzung ist zum einen das man Erfahrung im Umgang und der Wirkungsweise von klassischen Matrixformeln hat. Dazu verweise ich stets auf http://www.online-excel.de/excel/singsel.php?f=26 und ff.

Dann ist bei derartigen Formeln entscheidend, dass ich bewusst Fehlerwerte in der Matrix erzeuge (infolge des Teilens durch 0, da wo die Bedingung nicht eingehalten ist) und die Funktion AGGREGAT() infolge des 2. Argumentes (der 6) in der Lage ist, diese Fehlerwerte einfach zu ignorieren und nur die restlichen Zahlenwerten der Matrix auszuwerten.

Wenn Du in Deinem Beispiel und meiner Formel, in der Du die 99 z.B. durch eine 11 ersetzt, die Funktion Formelauswertung nutzt, wird es vielleicht auch schon noch etwas mehr nachvollziehbar.

Gruß Werner
.. , - ...


Beiträge aus den Excel-Beispielen zum Thema "INDEX und VERGLEICH neueste Änderung anzeigen"