Auslesen von Namenswerten aus Tabelle und Listung

Forumthread: Auslesen von Namenswerten aus Tabelle und Listung

31.03.2015 08:27:39

Namenswerten

Hallo liebes Forum,
ich komme bei einem kniffligen Problem nicht weiter.
Folgende Aufgabe soll gelöst werden (anschauliches Bild dazu unten):
In einer Datei gibt es zwei Tabellen. Die erste mit stets aufsteigenden Werten (Dopplungen möglich) - B2:P31, die zweite mit zugehörigen Namen (für jeden Wert) - R2:AF31. Jede Zeile stellt einen Datensatz aus externer Quelle dar und enthält die Werte & Namen.
Per manueller Vorgabe gibt man einen Referenzwert vor (Zelle C15).
Es sollen nun
1. grün) alle Namen der zweiten Tabelle ausgegeben werden, wo deren zugehörigen Werte aus Tabelle 1 kleinergleich des Fixwertes sind.
Diese wurden von mir der Übersichtlichkeit halber orange markiert (sowohl Wert als auch Name).
Da es oft gleiche Namen geben wird, die bei unterschiedlichen Datensätzen die Bedingung erfüllen (mit unterschiedlichen Werten), soll der jeweilige Namenswert nur einmal ausgegeben werden (falls dessen Zahlenwert mehrfach unter den vorgegebenen Referenzwert fällt).
2. blau) Es sollen wieder die Namen ausgegeben werden, deren Zahlenwerte die Bedingung erfüllen, ABER deren Name in der rechten Tabelle in allen Datensätzen nur ein einziges Mal (!!) die Bedingung erfüllt. Also jeder Name, der bei 1. innerhalb des gesetzten Referenzkriteriums nur ein einziges Mal vertreten ist. Alle die mindestens zweimal vorkommen, sollen ignoriert werden.
Beispielgrafik:
Userbild

Musterdatei: https://www.herber.de/bbs/user/96745.xls
Hat jemand eine Idee, wie/ob das per Formel lösbar ist?
Danke vorab und liebe Grüße
Joerschi

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

mit AGGREGAT() ...

31.03.2015 08:57:55

der

Hallo Joerschi,
... auf die Schnelle weil ich gleich wieder offline gehe:

Name1

Name2

Name3

Name4

Name5

Name6

Name7

Name8

Name9

Name10

Name11

Name12

Name13

Name14

Name15

Name14

Formeln der Tabelle

Zelle	Formel
B17	=WENN(SPALTE(A1)>AGGREGAT(14;6;SPALTE($B2:$P13)/($B2:$P13<$C15); 1); "";INDEX($R1:$AF1;SPALTE(A1)))
B19	=WENN((AGGREGAT(14;6;SPALTE($B2:$P13)/($B2:$P13<$C15); SPALTE(A1))=AGGREGAT(14;6;SPALTE($B2:$P13)/($B2:$P13<$C15); SPALTE(B1)))+(A19="")*(SPALTE(A1)>2); "";INDEX($R1:$AF1;AGGREGAT(14;6;SPALTE($B2:$P13)/($B2:$P13<$C15); SPALTE(A1))-1))

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

AW: mit AGGREGAT() ...

31.03.2015 09:00:49

Joerschi

Hi Werner,
Danke Dir.
aber ich meinte nicht die Ausgabe der Überschriften, sondern die Namen in der zweiten Tabelle (z. B. F82, F132 usw). :-)
Sorry, falls ich mich missverständlich ausgedrückt hatte
Liebe Grüße
Joerschi

zunächst noch nachgetragen ...

31.03.2015 10:47:07

der

Hallo Joerschi,
... die zuvor ermittelten Werte gelten für ein von mir reduzierten Wert in C15 auf =17,6% und der Erhöhung des Wertes in O12 von 17,59% auf 17,69%, weil ansonsten in Zeile 19 für C15=18,5% kein Ergebnisname gestanden hätte.
Deine Angaben hatte ich so interpretiert, und nicht so, wie Du es jetzt eindeutig schreibst. Aber auch dafür gibt es eine Formellösung.
Gruß Werner
.. , - ...

AW: zunächst noch nachgetragen ...

31.03.2015 10:55:19

Joerschi

wie gesagt: Sorry für das missverständliche Ausdrücken :-(
Hättest Du einen Ansatz für die Formellösung?
Viele Grüße
Joerschi

mit einer zusätzlichen Hilfszeile ...

31.03.2015 10:56:31

der

Hallo Joerschi,
... dann werden die nun erforderlichen Ergebnisformeln nicht unnötig lang.
Die drei Formeln einfach alle nach rechts kopieren. Die in der Hilfszeile bis FY19, und die in den Ergebniszeilen 18 und 20 brauchen natürlich nicht ganz soweit kopiert werden.
Der besseren Übersichtlichkeit halber hab ich C15 hier mal auf 10% reduziert:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Datensatz-Nr.	Wert1	Wert2	Wert3	Wert4	Wert5	Wert6	Wert7	Wert8	Wert9	Wert10	Wert11	Wert12	Wert13	Wert14	Wert15		Name1	Name2	Name3	Name4	Name5	Name6
2	41	5,73%	7,38%	7,46%	9,22%	9,42%	11,19%	11,78%	12,64%	12,84%	13,78%	14,72%	16,06%	16,06%	17,87%	18,10%		F89	F86	F90	F91	F87	F88
3	58	1,92%	17,24%	18,61%	18,85%	18,97%	19,48%	19,56%	19,60%	19,75%	19,83%	20,15%	20,19%	20,23%	20,50%	20,58%		F106	F112	F91	F80	F135	F116
4	61	14,10%	16,06%	16,14%	17,40%	17,55%	17,63%	17,87%	18,10%	19,05%	19,16%	19,32%	19,56%	19,83%	20,34%	20,66%		F84	F139	F83	F82	F85	F93
5	131	12,64%	14,72%	16,06%	17,28%	17,87%	18,10%	18,18%	18,69%	18,85%	19,56%	19,60%	20,03%	20,42%	20,66%	20,85%		F137	F138	F139	F81	F140	F146
6	143	9,22%	11,78%	11,97%	12,84%	12,96%	12,96%	13,78%	15,08%	15,20%	15,82%	15,86%	16,06%	16,65%	17,28%	17,59%		F91	F92	F124	F85	F89	F131
7	166	5,65%	7,14%	9,22%	11,93%	12,01%	12,64%	13,78%	14,72%	15,31%	15,82%	16,06%	16,14%	17,24%	17,63%	17,87%		F94	F95	F96	F97	F109	F137
8	183	12,96%	15,08%	15,82%	17,24%	17,59%	18,61%	18,85%	18,93%	19,44%	19,60%	19,75%	19,83%	20,15%	20,19%	20,23%		F89	F86	F90	F112	F87	F91
9	328	14,84%	17,28%	17,63%	18,18%	18,69%	18,77%	18,85%	19,48%	19,56%	19,83%	20,03%	20,15%	20,23%	21,01%	21,21%		F144	F81	F115	F145	F82	F84
10	334	14,84%	17,24%	17,63%	18,85%	18,89%	18,93%	19,44%	19,48%	19,60%	19,75%	20,15%	20,19%	20,85%	20,89%	21,01%		F144	F112	F115	F80	F97	F119
11	348	9,73%	12,64%	14,72%	16,06%	16,61%	17,87%	18,18%	18,69%	18,85%	18,89%	19,05%	19,44%	19,75%	19,83%	20,03%		F146	F137	F138	F139	F98	F140
12	360	6,08%	7,46%	9,22%	9,62%	10,83%	11,78%	11,97%	12,84%	13,78%	15,08%	15,20%	16,06%	17,59%	17,59%	18,69%		F122	F90	F91	F123	F118	F92
13	371	12,64%	12,84%	14,72%	15,08%	15,98%	16,06%	16,14%	17,59%	17,87%	18,10%	18,18%	18,69%	18,77%	18,97%	19,56%		F137	F85	F138	F86	F134	F139
14
15	Vorgabe - alles <=:		10,00%
16
17	1.	F89	F86	F90	F91	F87	F106	F94	F95	F96	F146	F122	F123
18		F89	F86	F90	F91	F87	F106	F91	F94	F95	F96	F146	F122	F90	F91	F123
19	2.	F89	F86	F87	F106	F94	F95	F96	F146	F122	F123

Formeln der Tabelle

Zelle	Formel
B17	=WENNFEHLER(INDEX(18:18;AGGREGAT(15;6;SPALTE(1:1)/(VERGLEICH(18:18;18:18;)=SPALTE(1:1)); SPALTE()-1)); "")
B18	=WENNFEHLER(INDEX($Q:$AF;AGGREGAT(15;6;(ZEILE($B2:$P13)100+SPALTE($B2:$P13))/($B2:$P13<=$C15); SPALTE(A1))/100;REST(AGGREGAT(15;6;(ZEILE($B2:$P13)100+SPALTE($B2:$P13))/($B2:$P13<=$C15); SPALTE(A1)); 100)); "")
B19	=WENNFEHLER(INDEX(18:18;AGGREGAT(15;6;SPALTE(1:1)/(ZÄHLENWENN(18:18;18:18)=1); SPALTE()-1)); "")

Bedingte Formatierungen der Tabelle

Zelle

Nr.: / Bedingung

Format

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

AW: mit einer zusätzlichen Hilfszeile ...

31.03.2015 11:18:22

Joerschi

Mein Gott. Wie kommt man denn auf solche Formeln... :-)
Vielen lieben Dank. Auf den ersten Blick scheint es zu funktionieren, aber ich werde damit noch ein paar Tests durchführen. Ist ja nicht ganz trivial...
Hilfszeile ist auch kein Problem.
Ich melde mich, falls ich noch einen Bug entdecke.
Viele Grüße
Joerschi

Infobox / Tutorial

Namenswerte aus Tabellen in Excel auslesen

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 die Namen aus einer Tabelle in Excel auszulesen, kannst du folgende Schritte befolgen:

Datenvorbereitung: Stelle sicher, dass du zwei Tabellen hast. Die erste Tabelle (z.B. B2:P31) enthält die Werte, und die zweite Tabelle (z.B. R2:AF31) die zugehörigen Namen.
Referenzwert festlegen: Gib in Zelle C15 den Referenzwert ein, basierend auf dem du die Namen auslesen möchtest.
Formel zur Auslesung: Verwende die folgende Formel, um die Namen der Werte in der zweiten Tabelle auszulesen, die kleiner oder gleich dem Referenzwert sind:
```
=WENN(SPALTE(A1)>AGGREGAT(14;6;SPALTE($B$2:$P$31)/($B$2:$P$31<$C$15);1); ""; INDEX($R$1:$AF$1;SPALTE(A1)))
```

Formel anpassen: Für die zweite Bedingung, bei der jeder Name nur einmal ausgegeben werden soll, kannst du eine ähnliche Formel verwenden:

=WENN(AGGREGAT(14;6;SPALTE($B$2:$P$31)/($B$2:$P$31<$C$15);SPALTE(A1))=AGGREGAT(14;6;SPALTE($B$2:$P$31)/($B$2:$P$31<$C$15);SPALTE(B1))+ (A19="")*(SPALTE(A1)>2); ""; INDEX($R$1:$AF$1; AGGREGAT(14;6;SPALTE($B$2:$P$31)/($B$2:$P$31<$C$15);SPALTE(A1))-1))

Ergebnisse überprüfen: Kopiere die Formeln nach rechts und teste sie mit verschiedenen Referenzwerten, um sicherzustellen, dass sie korrekt arbeiten.

Häufige Fehler und Lösungen

Fehler: Keine Ausgaben: Stelle sicher, dass der Referenzwert in C15 korrekt eingegeben ist und dass die Werte in den Tabellen im richtigen Format vorliegen (z.B. Zahlen und nicht Text).
Fehler: Doppelte Namen angezeigt: Überprüfe, ob die Formel zur Überprüfung doppelter Werte korrekt ist. Achte darauf, dass du die richtigen Bedingungen in der Formel verwendest.

Alternative Methoden

Falls du nicht nur mit Formeln arbeiten möchtest, kannst du auch die Filterfunktion in Excel verwenden, um Werte und Namen basierend auf einem bestimmten Kriterium auszulesen:

Wähle die Datenreihe aus.
Gehe zu „Daten“ > „Filtern“.
Setze die gewünschten Filterkriterien, um die gewünschten Namen anzuzeigen.

Praktische Beispiele

Einfaches Beispiel: Du hast die Werte von 1 bis 10 in der ersten Tabelle und die entsprechenden Namen in der zweiten. Wenn du 5 in C15 eingibst, sollten nur die Namen ausgegeben werden, deren Werte 5 oder weniger sind.
Komplexes Beispiel: Nutze die oben genannten Formeln, um Namen aus einer großen Datenbank auszulesen, die durch verschiedene Kriterien gefiltert sind, z.B. nur Namen, die in einem bestimmten Bereich liegen und nicht mehrfach vorkommen.

Tipps für Profis

Nutze Bedingte Formatierungen, um die Zellen hervorzuheben, die die Kriterien erfüllen. Das hilft, die Übersichtlichkeit zu erhöhen.
Erstelle Pivot-Tabellen, um eine bessere Analyse der ausgelesenen Namen und deren zugehörigen Werte zu ermöglichen.
Experimentiere mit ARRAYFORMELN, um mehrere Bedingungen gleichzeitig zu prüfen.

FAQ: Häufige Fragen

1. Wie kann ich nur bestimmte Namen aus einer Liste auslesen? Du kannst die FILTER-Funktion in Excel verwenden, um gezielt nach bestimmten Namen zu suchen, die deinen Kriterien entsprechen.

2. Gibt es eine Möglichkeit, die Formeln zu vereinfachen? Ja, du kannst Hilfsspalten erstellen, um Zwischenergebnisse zu speichern, was die Hauptformeln vereinfacht und die Lesbarkeit erhöht.

Beliebteste Forumthreads (12 Monate)

Auslesen von Namenswerten aus Tabelle und Listung

Forumthread: Auslesen von Namenswerten aus Tabelle und Listung

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Namenswerte aus Tabellen in Excel auslesen

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)

Auslesen von Namenswerten aus Tabelle und Listung

Forumthread: Auslesen von Namenswerten aus Tabelle und Listung

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Namenswerte aus Tabellen in Excel auslesen

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)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread