Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Auslesen von Namenswerten aus Tabelle und Listung

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

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

Betreff
Datum
Anwender
Anzeige
mit AGGREGAT() ...
31.03.2015 08:57:55
der
Hallo Joerschi,
... auf die Schnelle weil ich gleich wieder offline gehe:
 BCDEFGHIJKLMNOP
17Name1Name2Name3Name4Name5Name6Name7Name8Name9Name10Name11Name12Name13Name14Name15
18               
19Name15Name14             

Formeln der Tabelle
ZelleFormel
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
.. , - ...

Anzeige
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
.. , - ...

Anzeige
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:
 ABCDEFGHIJKLMNOPQRSTUVW
1Datensatz-Nr.Wert1Wert2Wert3Wert4Wert5Wert6Wert7Wert8Wert9Wert10Wert11Wert12Wert13Wert14Wert15 Name1Name2Name3Name4Name5Name6
2415,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
3581,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
46114,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
513112,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
61439,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
71665,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
818312,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
932814,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
1033414,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
113489,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
123606,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
1337112,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                       
15Vorgabe - alles <=:10,00%                    
16                       
171. 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        
192. F89 F86 F87 F106 F94 F95 F96 F146 F122 F123             

Formeln der Tabelle
ZelleFormel
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
ZelleNr.: / BedingungFormat


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

Anzeige
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

320 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige