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

mehrfache Werte aus einem Bereich

mehrfache Werte aus einem Bereich
07.01.2018 09:25:34
erichm
Hallo,
ich habe eine umfangreiche Tabelle, bei denen in den Spalten C bis IS, Zeilen 2 bis 151 viele unterschiedliche, aber auch viele doppelte / mehrfache Werte stehen.
Daraus will ich eine Liste in einer Spalte erstellen, die jeden vorkommenden Wert (egal ob einfach oder mehrfach) nur einmal auflistet.
Ich habe grundsätzlich bereits eine Formellösung gefunden:
http://www.excelformeln.de/formeln.html?welcher=194
Diese Formel ist aber nur für zwei Spalten geschrieben (in meinem Fall für die Spalten C und D). Soweit kann ich die Formel für mich übertragen.
Jetzt scheitere ich aber daran, die Formel so zu erweitern, dass auch die Spalten ab E berücksichtigt werden.
Evtl. gibt es ja mit AGGREGAT eine Lösung?
Vielen Dank.
mfg

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mehrfache Werte aus einem Bereich
07.01.2018 09:39:48
Hajo_Zi

Tabelle2
 J
131538
229820
3330
4290
5252
6216
7182
80
90
100
110
120
130

verwendete Formeln
Zelle Formel Bereich N/A
J1=MAX(C2:I151)  
J2:J13{=MAX(WENN(C2:I151<J1;C2:I151))}$J$2 
{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 25.14 einschl. 64 Bit



Ich gebe keinen Dank für eine Rückmeldung, da ich durch solche Beiträge nicht meine Beitragszahl erhöhen muss.
Also ich schreibe keine Beiträge mit dem Betreff "Gerne u. Danke für die Rückmeldung....."
Rückmeldung ist ja in der Heutigen Zeit nicht üblich und die wenigen die eine Rückmeldung geben,
mögen mir das verzeihen, das kein Danke für eine Rückmeldung kommt.
Beiträge von Werner, Luc, robert und folgende lese ich nicht.
Anzeige
AW: mehrfache Werte aus einem Bereich
07.01.2018 09:44:55
Sepp
Hallo Erich,
wenn es sich nur um Zahlen handelt, dann so.
Tabelle1

 A
1Werte
21
32
43
54
65
76
87
98

Formeln der Tabelle
ZelleFormel
A2=MIN($C$2:$IS$151)
A3=WENNFEHLER(AGGREGAT(15;6;$C$2:$IS$151/($C$2:$IS$151>A2); 1); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Formel aus A3 runterziehen.
Tabelle1

 A
1Werte
21
32
43
54
65
76
87
98

Formeln der Tabelle
ZelleFormel
A2=MIN($C$2:$IS$151)
A3=WENNFEHLER(AGGREGAT(15;6;$C$2:$IS$151/($C$2:$IS$151>A2); 1); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp

Anzeige
AW: mehrfache Werte aus einem Bereich
07.01.2018 10:23:50
erichm
Hallo Sepp,
danke - aber es sind wie in dem Formelbeispiel "gemischte Inhalte", also nicht nur Zahlen.
Da das Formelbeispiel den erstmals vorkommenden Wert schön durchnumeriert hat, scheitere ich einfach daran, die jeweiligen Werte in den linken Spalten (im Formelbeispiel A + B) durch die aufsteigenden Zahlen in den rechten Spalten (im Formelbeispiel D + E) zu finden.
mfg
AW: mit einer AGGREGAT()-Formel möglich, aber ...
07.01.2018 10:59:18
...
Hallo Erich,
... bei Deiner auszuwertenden Datenmenge dürfte der PC zumindest arg ins "Schwitzen" kommen, wenn er nicht gar schon "in die Knie" geht. Teste mal, denn meine nachfolgende Lösungsformel ist bestenfalls bei einer eher einmaligen Lösung zu empfehlen.
Angenommen Du willst Die Daten in Spalte A ermitteln, dann in A2:

=WENNFEHLER(INDEX(A:IS;AGGREGAT(15;6;(ZEILE(C$2:IS$151)+SPALTE(C1:IS151)%%)/(C$2:IS$151"") /(ZÄHLENWENN(A$1:A1;C$2:IS$151)=0);1);REST(AGGREGAT(15;6;(ZEILE(C$2:IS$151)+SPALTE(C1:IS1)%%) /(C$2:IS$151"")/(ZÄHLENWENN(A$1:A1;C$2:IS$151)=0);1);1)*10000);"")

und die Formel weit genug nach unten kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: mit einer AGGREGAT()-Formel möglich, aber ...
07.01.2018 15:34:31
erichm
Hallo Werner,
danke für die Lösung. Stimmt, die Datenmenge ist zu groß. Ist aber kein Problem
- diese Ermittlung benötige ich selten im Projekt
- ich kann die Spaltenanzahl in Häppchen aufteilen
Somit klappt es!!
PS: Mein "VBA-Problem"
https://www.herber.de/cgi-bin/callthread.pl?index=1596321#1596668
besteht leider immer noch. Nutze derzeit nur sehr sporadisch und wenn notwendig .xlsm-Dateien. Ich warte mal ab, vielleicht bringt ein neues Update von Windows wieder eine Berichtigung. Der Support von MS konnte noch nicht helfen.
Habe gerade gesehen, dass meine aktuelle Anfrage von Werner mit einem Makro gelöst wurde - das konnte ich nutzen.
Besten Dank!
mfg
Anzeige
per Makro eine Alternative
07.01.2018 11:21:07
Werner
Hallo Erich,
mittels ScriptingDictionary.
Ausgabe der Liste im gleichen Blatt in Spalte A (anpassen). Option Explicit Sub keine_Doppelten() Dim raZelle As Range, scrDic As Object Application.ScreenUpdating = False Set scrDic = CreateObject("Scripting.Dictionary") With Sheets("Tabelle1") 'Blattname anpassen For Each raZelle In .Range(.Cells(2, 3), .Cells(151, 253)) scrDic(raZelle.Value) = 0 Next raZelle 'hier Zieladresse anpassen .Range("A1").Resize(scrDic.Count) = WorksheetFunction.Transpose(scrDic.keys) End With Set scrDic = Nothing Application.ScreenUpdating = True End Sub Gruß Werner
Anzeige
AW: per Makro eine Alternative
07.01.2018 15:35:26
erichm
Hallo Werner,
besten Dank - hat hervorragend funktioniert!!
mfg
Gerne u. Danke für die Rückmeldung
07.01.2018 19:28:46
Werner

308 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige