Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

SVERWEIS / INDEX sammeln von unterschiedl. Werten

SVERWEIS / INDEX sammeln von unterschiedl. Werten
09.11.2017 18:18:41
unterschiedl.
Ich habe mich von diesem uralten Vorschlag inspirieren lassen
https://www.herber.de/forum/archiv/384to388/386802_SVERWEIS_bei_mehrfach_vorkommendem_Suchkriterium.html#386802
der auch technisch funktioniert, nur leider komplett falsche Ergebnisse liefert. Hier ist ein Auszug aus meiner Datei:
https://www.herber.de/bbs/user/117572.xlsx
Wo liegt das Problem? Ich habe keinen Schimmer. (übrigens: Mac OS, falls es wichtig ist)

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mit einer Matrixfunktion(alität)sformel ...
09.11.2017 20:12:28
...
Hallo Stefan,
... die keines spez. Formelabschluss wie eine klassische Matrixformel (wie in älteren Excelversion noch erforderlich benötigt.
In H2:

=WENNFEHLER(INDEX(Codingfortschritt!$G:$G;AGGREGAT(15;6;ZEILE(A$2:A$99)/FINDEN($A2;
Codingfortschritt!$D$2:$D$99)^0/(ZÄHLENWENN($G2:G2;Codingfortschritt!$G$2:$G$99)=0);SPALTE(A1)));"-")

und Formel nach rechts und unten kopieren.
Gruß Werner
.. , - ...
AW: mit einer Matrixfunktion(alität)sformel ...
10.11.2017 10:44:54
Stefan
Hallo Werner,
klasse formel, funktioniert. Jetzt noch verstehen: Ich verstehe dass Aggregat mit 15 ein ersatz für KKLEINSTE ist und 6 die Fehler ignorieren soll. Was dann folgt, ist ein Array-Argument, oder?
Aber was bedeutet es? Kannst du mir die Formal mal in Worte zerlegen: Mit "Zeile" machst du hier dies und das, mit finden dann jenes, mit ^0 dann noch das etc.?
ZEILE(A$2:A$99)/FINDEN($A2;Codingfortschritt!$D$2:$D$99)^0/(ZÄHLENWENN($G2:G2;Codingfortschritt!$G$2:$G$99)=0"
Ich versuche es selber einmal: INDEX gibt mir den Wert aus der Matrix (Codingfortschritt-Spalte G) und dem Ergebnis der Aggregatfunktion aus. Die Aggregatfunktion nun sucht den k-kleinste Wert (15), ohne Fehler zu beachten (6) aus der Zeile A$2:a$99 oder aus der "Spalte A1" ist? Eigentlich müsste ja zunächst die Suchematrix kommen, dann der Wert den es zu beachten gilt. Also scheint es Spalte A1 zu sein, was irgendwie im meinem Kopf keinen Sinn ergibt, vor allem, weil das Kopieren nach rechts ja dazu führt, dass dann Spalte A2, Spalte A3 etc. eingesetzt wird.
Während der gesuchte k-Wert das 4 Argument ist, ist das dritte Argument die Matrix. Sie besteht aus drei Teilen: ZEILE, FINDEN, ZÄHLENWENN. Von hinten:
- Die Zählenwenn-Funktion verstehe ich so, dass nur gezählt wird ( " =0 "), wenn der Inhalt aus Codingfortschritt-Spalte g nicht schon in $G2:G2 (dann weiter hin $G2:H2 in der spalte daneben) zu finden ist.
- Dies wird dividiert (?) vom Ergebnis der Finden-Funktion, die nach dem Inhalt von $A2 in Codingfortschritt-Spalte G sucht. Die Wurzel aus Null sichert irgendetwas ab? verstehe ich nicht ganz.
- Die Zeilenfunktion verstehe ich schlicht nicht.
Schließlich: Die Wennfehler-Fkt fügt hinzu, dass Fehlerwerte mit dem "-" ausgegeben werden.
Das habe ich mir durchs Lesen und tutorial schauen angeeignet, steige aber noch nicht durch.
Erstmal danke für die schnell Hilfe! und ich bin sehr dankbar, wenn ich nicht nur nachmachen kann, sondern verstehen darf.
Gruß,
Stefan
Anzeige
AW: bitte, gern und ...
10.11.2017 12:26:00
...
Hallo Stefan,
... das erklären ist nicht meine starke Seite :-( Hab allerdings schon ein paar Mal ähnliche Konstrukte etwas erklärt, finde diese aber momentan nicht, aber Du hast ja schon gut vorgearbeitet.
Aber zunächst muss ich bekennen, dass die Formel leider noch einen entscheidenden Fehler beinhaltet :-(
Also, zu: "... Was dann folgt, ist ein Array-Argument ... Ja
Und "Die Aggregatfunktion nun sucht den k-kleinste Wert (15), ohne Fehler zu beachten (6) aus der Zeile A$2:A$99" Genau.
SPALTE(A1) ist 1 und nur diese 1 wird auch in der Formel benötigt, wenn diese nach rechts rechst kopiert wird. Schreibe ich sonst auch immer, war wohl etwas abgelenkt. SPALTE(A1) dagegen nach rechts kopiert ergibt natürlich, 2, 3 ... und somit gehen Ergebniswerte verloren.
Die 1 steht für den 1. dupkitkatfreien Ergebniswert unter Berücksichtigung der links von diesen bereits stehenden/ermittelten Ergebniswerte.
Mit dem ZÄHLENWENN()-Formelteil werden die vorhandenen Datenwerte mit den bereits ermittelten Ergebniswerten "abgeglichen". Nur da wo sich ein 0 (also noch nicht vorhanden) ergibt, wird der an entsprechender Position in der Datenmatrix stehende Wert als duplikatfreier Wert erkannt.
Der FINDEN()-Formelteil sucht in der entsprechenden ;Codingfortschritt!$D$2:$D$99 nach den in Spalte A der Zeile stehenden Wert. Wird er nicht gefunden, wird aus dem entsprechenden Datensatz der Autor auch nicht berücksichtigt. Wird er gefunden ergibt kann sich ein Fehlerwert oder ein Zahlenwert (gefundene Positionsstelle) ergeben der 1 aber auch größer 1 sein kann. Als Divisoren werden aber in der in komplexen Teilformel für das 3. Argument von AGGREGAT() nur 1enn, 0en oder Fehlerwerte benötigt. Die Potenzierung mit 0 ergibt genau das.
Dadurch ermittelt AGGREGAT() die Position (Zeilennummer) des duplikatfreien Ergebniswertes aus der Datenbereich von Codingfortschritt!$G$2:$G$99 und übergibt dieses als Positionsargument der INDEX()-Funktion.
Und Die Wennfehler-Fkt fügt hinzu, dass Fehlerwerte mit dem "-" ausgegeben werden. Natürlich.
Ich hoffe, ich konnte Dir damit die spez. Formelart, die wie eine klassischen Matrixformel intern "arbeitet" aber nicht des spez. Formelabschlusses etwas näher bringen.
Gruß Werner
.. , - ...
Anzeige
AW: bitte, gern und ...
10.11.2017 17:42:06
Stefan
Lieber Werner,
ok, gut, das kann ich ungefähr nachvollziehen. Ich hätte noch ein kleine Anschlussfrage, die dieses etwas weiter treibt. Du siehst in meiner Beispieldatei, dass manch ein Spalteneintrag zwei Namen enthält, weil es sich um ein Autorenpaar handelt. Da ich jedoch einzelne Autoren haben möchte, entstehen damit unnötige Doppelung. Aus Gründen der Datenstruktur (also wo diese Daten herkommen, das sind keine selbst eingegebenen Daten und es sind noch längst nicht alle) bekomme ich die Namen aber genauso.
Variante 1: Gibt es eine Möglichkeit innerhalb deiner Formel eine Trennung herbeizuführen?
Variante 2: Natürlich kann ich die Namen auch auf mehrere Spalten aufteilen - beispielsweise mit einem Kommatrenner, den ich bei der Datenerhebung einbauen kann - und die Matrix deiner Formel daraufhin anpassen. Hier wären dann das Probleme, - und damit Frage an dich, ob, die leeren Zellen in den Spalten "Autor 2", "Autor 3" etc. dann zu Fehlern führen und ich irgendwo in der Formel noch "zähle, wenn nicht leer" einfügen muss. (oder ist das schon drin?)
Tausend Dank!
Anzeige
AW: dazu ... und nachgefragt ...
10.11.2017 18:36:45
...
Hallo Stefan,
... die Variante 1 ist mittels Formel nicht wirklich vertretbar zu bewältigen. Zur Variante 2 solltest Du mal einen Beispielauszug einstellen, wie Du diesen datenmäßig erfassen würdest. Wahrscheinlich meinst Du eine Aufsplitten Deiner Autoren in Codingfortschritt!G:G in Codingfortschritt!G:XX oder?. Ob und wie man eine derartige Datenstruktur für Deine geplante Auswertung mit entsprechend erweiterten Formel raalsieren kann, würde ich mir am WE mal anschauen.
Gruß Werner
.. , - ...
AW: Formel für Variante 2 ...
11.11.2017 09:35:09
...
Hallo Stefan,
... und wie gestern geschrieben, bei einer Aufsplitten Deiner Autoren aus Codingfortschritt!G:G in Codingfortschritt!G:I (ich bin jetzt erst einmal von max 3 Autoren je Titel ausgegangen), dann in H2:
=WENNFEHLER(INDEX(Codingfortschritt!$A:$I;AGGREGAT(15;6;
(ZEILE(A$2:A$99)+SPALTE($G$1:$I$2)%)/FINDEN($A2;Codingfortschritt!$D$2:$D$99)^0/
(ZÄHLENWENN($G2:G2;Codingfortschritt!$G$2:$I$99)=0)/(Codingfortschritt!$G$2:$I$99"");1);
100*REST(AGGREGAT(15;6;(ZEILE(A$2:A$99)+SPALTE($G$1:$I$2)%)/FINDEN($A2;Codingfortschritt!$D$2:$D$99)^0/
(ZÄHLENWENN($G2:G2;Codingfortschritt!$G$2:$I$99)=0)/(Codingfortschritt!$G$2:$I$99"");1);1));"-")

und Formel nach rechts und unten kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: vorsichtshalber noch mit GLÄTTEN() ...
11.11.2017 10:15:49
...
Hallo,
... falls beim Aufsplitten der Autoren noch Leerzeichen "hängen bleiben" hab ich in die Formel noch GLÄTTEN() eingebaut.
=WENNFEHLER(GLÄTTEN(INDEX(Codingfortschritt!$A:$I;AGGREGAT(15;6;
(ZEILE(A$2:A$99)+SPALTE($G$1:$I$2)%)/FINDEN($A2;Codingfortschritt!$D$2:$D$99)^0/
(ZÄHLENWENN($G2:G2;GLÄTTEN(Codingfortschritt!$G$2:$I$99))=0)/(Codingfortschritt!$G$2:$I$99"");1);
100*REST(AGGREGAT(15;6;(ZEILE(A$2:A$99)+SPALTE($G$1:$I$2)%)/FINDEN($A2;Codingfortschritt!$D$2:$D$99)^0/
(ZÄHLENWENN($G2:G2;GLÄTTEN(Codingfortschritt!$G$2:$I$99))=0)/(Codingfortschritt!$G$2:$I$99"");1);1)));"-")
Natürlich kann auch diese Formel ansonsten nur identische Schreibweisen der Autoren als "Duplikate" erkennen.
Gruß Werner
.. , - ...
Anzeige
AW: vorsichtshalber noch mit GLÄTTEN() ...
11.11.2017 11:07:46
Stefan
Lieber Werner,
vielen herzlichen Dank! Ich kam gar nicht so schnell dazu, aber du hast es ganz richtig erfasst.
Die Datenstruktur sähe ungefähr so aus: Vorname Name.Vorname Name.Vorname Name oder ein anderes beliebiges Zeichen. (Die Namen werden aus einem Dateinamen extrahiert, etwa so 2011.01.01 - Titel des Dokuments _ Vorname Name.Vorname Name - weiteres weiteres.txt. (Bisher waren die Vornamen nur durch Leerzeichen getrennt) Dies wird von einem anderem Programm eingelesen und in dessen Kategorien aufgeteilt (via regex-suchen). Von dort als csv ausgegeben, so dass die Tabelle entsteht, die du dort gesehen hast. (mit mehr Informationen).
Noch einmal vielen herzlichen Dank!
Ich probiere deine Formel später aus, jetzt schaffe ich es gerade nicht.
Anzeige
AW: das Aufsplitten dieser Datenwerte ...
12.11.2017 14:02:16
...
Hallo Stefan,
... kannst Du dann ganz einfach mit der Funktion Text in Spalten und der Option "Getrennt" und mit dem Trennzeichen "Andere" und dafür den "." zuweisen vornehmen.
Gruß Werner
.. , - ...

314 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige