INDEX/VERGLEICH - Bestimmte Zeilen ignorieren

Forumthread: INDEX/VERGLEICH - Bestimmte Zeilen ignorieren

07.07.2016 17:07:38

Simon

Hallo zusammen,
ich möchte mir eine Top 5 - Auswertung für eine Datentabelle bauen. Mein Problem liegt dabei in dem Aufbau der Daten-Tabelle (siehe dazu die angehängte Beispiel-Datei):
https://www.herber.de/bbs/user/106843.xlsx
Leider gibt es in der Daten-Tabelle mehrere Zwischenergebnis-Zeilen. Durch die Festlegung eines Namens lediglich für alle zu berücksichtigende Teilbereiche (siehe Namensmanager) werden diese Zwischenergebnis-Zeilen ignoriert (siehe Tabellenblatt Auswertung, Spalte C).
Jetzt möchte ich mir für diese Werte den Namen des Teilbereiches links neben den Werten anzeigen lassen. Dazu nutze ich folgende Formel, die eigentlich (!) auch wunderbar funktioniert:
=INDEX(Datenbasis!B:B;VERGLEICH(Auswertung!C4;Datenbasis!C:C;0))
Nun habe ich jedoch das Problem, dass z.B. in Zeile 7 der Name der Zwischenergebnis-Zeile ausgegeben wird (da der Wert identisch ist mit dem Teilbereichs-Wert und - leider nicht veränderbar - oberhalb des Teilbereichs-Wertes). Hier sehe ich zwei potenzielle Lösungen - bekomme die Umsetzung aber leider noch nicht hin ...
Lösungsvariante 1)
Gibt es hier eventuell eine Möglichkeit, die Namen bei der Index-Formel von unten nach oben prüfen zu lassen (also das erst der Teilbereichs-Name verwendet wird)?
Lösungsvariante 2)
Im originalen Datensatz gibt es im Tabellenblatt Datenbasis für alle Teilbereiche - nicht jedoch bei den Zwischensummen - in Spalte A eine Bereichsnummer (siehe Tabellenblatt Lösungsvariante_B). Könnte man diese ggfs. dazu nutzen, dass nur die Teilbereiche für die Top5 Namensgebung verwendet werden?
Hat hier jemand vielleicht eine Lösungsidee (oder einen ganz anderen Ansatz)?
Bin für jeglichen Input dankbar.
Vielen Dank!

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Mit Matrixformel

07.07.2016 18:45:58

Michael

Hi Simon,
in Auswertung!B4 die Formel: {=WENNFEHLER(INDEX(Datenbasis!$B$1:$B$20; KGRÖSSTE(ZEILE(Datenbasis!$B$1:$B$20)*(LINKS(Datenbasis!$B$1:$B$20;5)="Teilb")* (Datenbasis!$C$1:$C$20=C4);1));"")}
und bis B8 kopieren.
Es handelt sich um eine Matrixformel, d.h. die {} werden nicht händisch eingegeben, sondern erscheinen automatisch, wenn Du Formel mit Strg+Umschalt+Enter abschließt.
Eine ähnliche Geschichte war eben hier, falls Du spielen willst:
https://www.herber.de/forum/messages/1502751.html
Schöne Grüße,
Michael

AW: auch hier einfacher mit AGGREGAT() ...

08.07.2016 20:49:07

...

hallo Michael,
... Dein Formelvorschlag funktioniert nur, wenn die Werte in Spalte C alle unterschiedlich sind. Um dem abzuhelfen, habe ich mal unten stehenden Beispiel die Originaldaten entsprechend modifiziert
@ Simon
für die Ermittlung der TOP5 Werte bedarf es in Deinem Fall auch keiner entsprechende Bereichsdefinition, wie von Dir gewählt. Dazu kannst Du auch einfacher AGGREGAT() einsetzen:
Auswertung

	B	C
2	Top 5
3	Teilbereich	Wert
4	Teilbereich 2	2500
5	Teilbereich 6	1000
6	Teilbereich 4	800
7	Teilbereich 7	800
8	Teilbereich 9	800

Formeln der Tabelle

Zelle	Formel
B4	=INDEX(Datenbasis!B:B;AGGREGAT(15;6;ZEILE(B$1:B$99)/(Datenbasis!C$1:C$99=C4); ZÄHLENWENN(C$4:C4;C4)))
C4	=AGGREGAT(14;6;Datenbasis!C$1:C$99/(LINKS(Datenbasis!B$1:B$99;1)="T"); ZEILE(A1))

Datenbasis

	B	C
2	Summe A	4850
3	Teilbereich 1	500
4	Teilbereich 2	2500
5	Teilbereich 3	750
6	Teilbereich 4	800
7	Teilbereich 5	300
8	Summe B	2800
9	Teilbereich 6	1000
10	Teilbereich 7	800
11	Teilbereich 8	50
12	Teilbereich 9	800
13	Teilbereich 10	150
14	Summe C	800
15	Teilbereich 11	800

Formeln der Tabelle

Zelle	Formel
C2	=SUMME(C3:C7)
C8	=SUMME(C9:C13)
C14	=SUMME(C15:C19)

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

AW: auch hier einfacher mit AGGREGAT() ...

09.07.2016 12:55:25

Michael

Hi Werner,
stimmt, da war ja noch was: das ist mir irgendwann vor dem Einschlafen noch durch den Kopf gegangen, dann hab ich's aber auch wieder vergessen. Klar, in der anderen Spalte muß man auch nach "Txxx" sehen, damit die Summen draußen sind...
Hatte die XL2007-Kiste an (ohne Aggregat).
Schöne Grüße,
Michael

jetzt weiß ich wieder, was gefehlt hat: AGGREGAT !

09.07.2016 13:18:43

MB12

Gruß, Margarete

AW: jetzt weiß ich wieder, was gefehlt hat: AGGREGAT !

14.07.2016 15:39:06

Simon

Hallo zusammen,
entschuldigt bitte die späte Antwort. Mit eurer Hilfe konnte ich mein Problem mittlerweile lösen!
Viele Grüße

Infobox / Tutorial

INDEX/VERGLEICH: Bestimmte Zeilen ignorieren und Top 5 ermitteln

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 das Problem zu lösen, dass beim Verwenden der Funktion INDEX und VERGLEICH in Excel bestimmte Zeilen ignoriert werden, kannst du die folgende Schritt-für-Schritt-Anleitung nutzen:

Daten vorbereiten: Stelle sicher, dass deine Daten in einer Tabelle strukturiert sind. Achte darauf, dass leere Zeilen vorhanden sein können, die ignoriert werden sollen.
Formel in Zelle eingeben: Nutze die Formel, um die gewünschten Werte zu extrahieren. Zum Beispiel:
```
=INDEX(Datenbasis!B:B;VERGLEICH(Auswertung!C4;Datenbasis!C:C;0))
```
Anpassungen vornehmen: Um leere Zeilen zu ignorieren, kannst du die AGGREGAT-Funktion verwenden, um sicherzustellen, dass nur die relevanten Werte berücksichtigt werden.

Matrixformel anwenden (optional): Wenn du eine Matrixformel verwenden möchtest, gehe wie folgt vor:

{=WENNFEHLER(INDEX(Datenbasis!$B$1:$B$20; KGRÖSSTE(ZEILE(Datenbasis!$B$1:$B$20)*(LINKS(Datenbasis!$B$1:$B$20;5)="Teilb")*(Datenbasis!$C$1:$C$20=C4);1));"")}

Formel bestätigen: Vergiss nicht, die Formel mit Strg+Umschalt+Enter abzuschließen, um sie als Matrixformel zu aktivieren.

Häufige Fehler und Lösungen

Problem: Formel gibt unerwartete Zwischenergebnisse zurück.
- Lösung: Achte darauf, dass du die richtige Anpassung in der Formel vornimmst, um nur die relevanten Teilbereiche zu berücksichtigen. Verwende die AGGREGAT-Funktion, um die Werte zu filtern.
Problem: Zeilen mit identischen Werten werden nicht korrekt behandelt.
- Lösung: Überprüfe deine Kriterien in der VERGLEICH-Funktion und stelle sicher, dass du die richtige Spalte für die Suche angibst.

Alternative Methoden

Eine alternative Methode zur Verwendung von INDEX und VERGLEICH ist die Nutzung der AGGREGAT-Funktion. Diese Funktion ermöglicht es, die Top 5-Werte zu ermitteln und dabei leere Zeilen automatisch zu ignorieren. Hier ein Beispiel:

=AGGREGAT(14;6;Datenbasis!C$1:C$99/(Datenbasis!C$1:C$99<>""*LINKS(Datenbasis!B$1:B$99;1)="T");ZEILE(A1))

Diese Formel hilft dir, nur die relevanten Werte zu extrahieren und dabei leere Zeilen zu ignorieren.

Praktische Beispiele

Hier sind einige praktische Beispiele für die Anwendung der oben genannten Methoden:

Top 5 aus einem Bereich:
- Angenommen, du möchtest die Top 5 Werte aus dem Bereich Datenbasis!C1:C20 extrahieren, ohne leere Zeilen zu berücksichtigen. Verwende die AGGREGAT-Formel, um dies zu erreichen.
Namen der Teilbereiche extrahieren:
- Verwende die INDEX-Formel in Kombination mit VERGLEICH, um die Namen der Teilbereiche neben den Werten anzuzeigen.

Tipps für Profis

Nutze die FILTER-Funktion (verfügbar in neueren Excel-Versionen), um leere Zeilen schnell zu ignorieren und die Datenbasis dynamisch zu filtern.
Experimentiere mit SORTIEREN und FILTER in Kombination, um deine Daten effektiver zu analysieren.
Wenn du mit großen Datenmengen arbeitest, achte darauf, die Berechnungseinstellungen von Excel auf manuell zu setzen, um die Leistung zu verbessern.

FAQ: Häufige Fragen

1. Wie kann ich leere Zeilen in meiner Datenbasis ignorieren?
Du kannst die AGGREGAT-Funktion verwenden, um die Filterung von leeren Zeilen zu ermöglichen.

2. Ist es möglich, mit INDEX und VERGLEICH von unten nach oben zu suchen?
Ja, das ist möglich, indem du die Reihenfolge der Daten umkehrst oder die AGGREGAT-Funktion in Kombination mit einer Zeilenreferenz verwendest, um die gewünschten Ergebnisse zu erzielen.

Beliebteste Forumthreads (12 Monate)

INDEX/VERGLEICH - Bestimmte Zeilen ignorieren

Forumthread: INDEX/VERGLEICH - Bestimmte Zeilen ignorieren

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

INDEX/VERGLEICH: Bestimmte Zeilen ignorieren und Top 5 ermitteln

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)

INDEX/VERGLEICH - Bestimmte Zeilen ignorieren

Forumthread: INDEX/VERGLEICH - Bestimmte Zeilen ignorieren

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

INDEX/VERGLEICH: Bestimmte Zeilen ignorieren und Top 5 ermitteln

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)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread