In Spalte mehrfach vorkommende Werte suchen

Forumthread: In Spalte mehrfach vorkommende Werte suchen

19.01.2016 10:47:39

Uwe

Hallo zusammen,
ich habe eine Tabelle, in der in der ersten Spalte eine ID steht. Darauf folgen verschiedene gleichartige Informationen (Quelle, Bewertung, Name).
Zu jeder ID gibt es nun mehrere Einträge, die ich jetzt zeilenweise zusammenfassen möchte.
https://www.herber.de/bbs/user/102875.xlsx
D.h. ich möchte pro ID nur noch eine Zeile haben, in der der Reihe nach alle Namen, Quellen, Bewertungen stehen habe - beispielsweise
1 Verband SO NR SA NR AM NR
oder auch
9 Swiss Re Frankona SA AA- AM A
Leider habe ich keine Ahnung wie ich dies elegant bewerkstelligen kann da das Suchkriterium mehrfach auftritt und zu allem Elend verschiedene IDs eine unterschiedliche Anzahl Quellen haben können.
Wer hat denn eine gute Idee? Bin für jede Hilfe dankbar!

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Formellösung auf Basis AGGREGAT() ...

19.01.2016 12:18:01

...

Hallo Uwe,
... in Kombination mit anderen Funktionen. Dazu ist eine einfache Formel (in H2) und nur zwei Matrixfunktion(alität)sformeln (wovon die Formel I2 doch schon recht komplex ist) notwendig, die kopiert werden.
Die Formeln G2:H2 weit genug nach unten ziehend kopieren und die Formel I2 entsprechend sowohl nach rechts und unten.

	G	H	I	J	K	L	M	N
2	1	Verband	SO	SA	AM	NR
3	2	DEUTSCHER LUFTPOOL	SO	SA	AM	NR
4	3	Münchener Rückversicherungs-	SA	AM	AA-	A+
5	4	Pharma-Rückversicherungs-Gemeinschaft	SO	SA	AM	NR
6	6	LANDSCHAFTLICHE BRANDKASSE	SO	SA	AM	NR	A
7	7	Deutsche Rückversicherung AG	SA	AM	A+	NR
8	8	General Reinsurance AG	SA	AM	AA+	A++
9	9	Swiss Re Frankona	SA	AM	AA-	A
10	10	R+V Versicherung AG	SO	SA	AM	A	AA-	NR
11	11	Globale Rückversicherungs-AG	SO	SA	AM	BBB	NR
12	12	Gerling-Konzern Allgemeine	SO	SA	AM	NR	BBB+	A-
13	13	HOLDING	SO	SA	AM	NR
14	15	NORDSTERN VERSICHERUNGEN	SO	SA	AM	AA-	NR
15	16	Euler Hermes	SO	SA	AM	NR	A+
16

Formeln der Tabelle

Zelle	Formel
G2	=WENNFEHLER(AGGREGAT(15;6;A$1:A$99/(A$1:A$99>MAX(G$1:G1)); 1); "")
H2	=WENN(G2="";"";SVERWEIS(G2;A:B;2;))
I2	=WENN(SPALTE(A1)>ZÄHLENWENN($A:$A;$G2); WENNFEHLER(INDEX($D:$D;AGGREGAT(15;6;ZEILE(A$1:A$99)/($A$1:$A$99=$G2)/ISTNV(VERGLEICH($D$1:$D$99;$H2:H2;)); 1)); ""); INDEX($C:$C;AGGREGAT(15;6;ZEILE(A$1:A$99)/($A$1:$A$99=$G2); SPALTE(A1))))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

AW: Formellösung auf Basis AGGREGAT() ...

19.01.2016 14:01:09

Uwe

danke Werner,
das ist schon sehr gut.
Mit der Möglichkeit auf AGGREGAT zurückzugreifen habe ich noch gar nicht gespielt. Danke für diesen Tipp!
Leider entspricht das Ergebnis noch nicht ganz genau dem, was ich will, da zuerst alle Quellen und dann alle Bewertungen genannt werden, die eine ID hat. Dies führt dann dazu, dass teilweise unterschiedliche Quellen / Bewertungen untereinander stehen (zur Erklärung - es handelt sich um Ratings und die Quelle dieser Ratings).
Eine gute Alternative der Darstellung wäre für mich auch eine Spaltenansicht - also 3 neue Spalten (Rating SO; Rating SA; Rating AM), was ich natürlich auch sehr einfach durch filtern nach Quelle, kopieren in 3 neue Tabellenblätter (je eines pro Quelle) und entsprechenden SVERWEIS realisieren könnte.
Ich hätte dennoch Interesse eine elegantere Lösung zu finden um dieses Vorgehen automatisiert ablaufen lassen zu können.

AW: wenn ich richtig gelesen hätte ...

19.01.2016 14:22:31

...

Hallo Chris,
... aber das hab ich leider nicht :-(
Ich schau dann später noch einmal, die Formel auf Deine Vorgaben "umzustricken".
Gruß Werner
.. , - ...

AW: nun, dann wird es Formel I2 viel einfacher ...

19.01.2016 17:42:36

...

Hallo Chris,
... so: =WENNFEHLER(INDEX($C:$D;AGGREGAT(15;6;ZEILE(A$1:A$99)/($A$1:$A$99=$G2);KÜRZEN(SPALTE(B1)/2;));2-REST(SPALTE(A1);2));"")&""
Gruß Werner
.. , - ...

Infobox / Tutorial

Mehrfach vorkommende Werte in Excel suchen und zusammenfassen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um mehrfach vorkommende Werte in einer Excel-Spalte zu suchen und zusammenzufassen, kannst du die Funktion AGGREGAT() nutzen, die in Excel 2010 und neueren Versionen verfügbar ist. Hier ist eine einfache Schritt-für-Schritt-Anleitung:

Daten vorbereiten: Stelle sicher, dass deine Tabelle die IDs und die zugehörigen Werte (z.B. Quellen, Bewertungen) enthält.
Formel in die erste Zelle einfügen:
- Gehe zur Zelle G2 und füge folgende Formel ein:
```
=WENNFEHLER(AGGREGAT(15;6;A$1:A$99/(A$1:A$99>MAX(G$1:G1));1);"")
```
- Diese Formel findet die nächste ID und ignoriert bereits gefundene ID-Werte.

Weitere Formeln einfügen:

In Zelle H2 fügst du die folgende Formel ein:
```
=WENN(G2="";"";SVERWEIS(G2;A:B;2;))
```

In Zelle I2 kannst du diese komplexere Formel eingeben:

=WENN(SPALTE(A1)>ZÄHLENWENN($A:$A;$G2); WENNFEHLER(INDEX($D:$D;AGGREGAT(15;6;ZEILE(A$1:A$99)/($A$1:$A$99=$G2);SPALTE(A1))); ""); INDEX($C:$C;AGGREGAT(15;6;ZEILE(A$1:A$99)/($A$1:$A$99=$G2);SPALTE(A1))))

Formeln nach unten ziehen: Ziehe die Formeln in den Zellen G2 bis I2 nach unten, um sie auf die restlichen Zeilen anzuwenden.
Ergebnisse überprüfen: Du solltest jetzt alle IDs in einer Zeile zusammengefasst haben, mit den jeweiligen Quellen und Bewertungen.

Häufige Fehler und Lösungen

Fehler: #BEZUG! oder #DIV/0!
- Lösung: Überprüfe deine Daten auf leere Zellen oder sicherstellen, dass die Bereichsangaben korrekt sind.
Fehler: Falsche Ergebnisse oder Duplikate
- Lösung: Stelle sicher, dass die Formeln korrekt eingegeben wurden und dass du die richtigen Zellbereiche verwendest.

Alternative Methoden

Falls die oben genannte Methode nicht deinen Anforderungen entspricht, kannst du auch folgende Alternativen ausprobieren:

PivotTables: Eine PivotTable ist eine leistungsstarke Möglichkeit, um Daten zu aggregieren und zu analysieren. Du kannst IDs als Zeilen und die entsprechenden Werte als Werte festlegen, um eine zusammenfassende Ansicht zu erhalten.
Power Query: In Excel 2010 und neueren Versionen kannst du Power Query verwenden, um Daten zu transformieren und zusammenzufassen. Hiermit kannst du duplizierte Einträge einfach entfernen und die Daten in der gewünschten Form darstellen.

Praktische Beispiele

Hier sind einige Beispiele, wie die obigen Formeln in einer Excel-Tabelle aussehen könnten:

Beispiel 1:
- ID: 1
- Quelle: Verband
- Bewertung: SO, SA, NR
Beispiel 2:
- ID: 9
- Quelle: Swiss Re Frankona
- Bewertung: AA-, AM, A

Durch die Umsetzung dieser Beispiele kannst du deine Daten effizient zusammenfassen und analysieren.

Tipps für Profis

Verwende die Funktion AGGREGAT(): Diese Funktion ist extrem nützlich, um Daten zu filtern und gleichzeitig Fehler zu vermeiden. Nutze die verschiedenen Optionen, um deine Datensätze optimal abzufragen.
Verknüpfe Formeln: Stelle sicher, dass deine Formeln gut miteinander verknüpft sind, um redundante Berechnungen zu vermeiden und die Übersichtlichkeit zu erhöhen.
Etabliere eine klare Struktur: Halte deine Daten gut strukturiert, damit du leichter neue Formeln hinzufügen oder bestehende Formeln anpassen kannst.

FAQ: Häufige Fragen

1. Wie kann ich mehrere Spalten gleichzeitig zusammenfassen?
Du kannst die oben beschriebenen Formeln anpassen, um mehrere Spalten zu berücksichtigen, indem du die SVERWEIS- und INDEX-Funktionen entsprechend modifizierst.

2. Funktioniert dies in älteren Excel-Versionen?
Die Funktionen AGGREGAT() und WENNFEHLER() sind in Excel 2010 und neueren Versionen verfügbar. In älteren Versionen musst du möglicherweise alternative Methoden verwenden, wie z.B. manuelle Filterung oder einfache SVERWEIS-Funktionen.

Beliebteste Forumthreads (12 Monate)

In Spalte mehrfach vorkommende Werte suchen

Forumthread: In Spalte mehrfach vorkommende Werte suchen

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Mehrfach vorkommende Werte in Excel suchen und zusammenfassen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

In Spalte mehrfach vorkommende Werte suchen

Forumthread: In Spalte mehrfach vorkommende Werte suchen

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

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Mehrfach vorkommende Werte in Excel suchen und zusammenfassen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread