PDA

Vollständige Version anzeigen : Mehrere Zeilen und Spalten: Wo steht der höchste Wert?


Cube108
27.06.2015, 16:30
Ich habe 6 Spalten zu je 100.000 Zeilen.

In diesen Zellen steht irgendwo ein höchster Wert (der allerdings öfter vorkommt – und das könnte der Hinweis auf mein Problem sein).

Ich versuche, diesen Wert zu ermitteln, bekomme aber #NV:

=ADRESSE(VERGLEICH(MAX(AZ3:BE100002);AZ3:BE100002;0)+ZEILE(AZ3:BE100002)-1;SPALTE(AZ3:BE100002))

Weiß da jemand Rat?

Hajo_Zi
27.06.2015, 16:33
vergleich geht nur in einer Spalte.
Kein Ansatz.

<img src="http://Hajo-Excel.de/images/grusz1.gif" align="middle" height="40" alt="Grußformel"><a href="http://Hajo-Excel.de/index.htm"><img border="0" src="http://Hajo-Excel.de/images/logo_hajo3.gif" align="middle" height="40" alt="Homepage"></a>

GMG-CC
27.06.2015, 17:49
Moin,

... und natürlich auch in 1 Zeile.
Aber davon mal abgesehen, was soll denn als Ergebnis herauskommen, wenn der Maximalwert mehrfach enthalten ist. Deiner Formel nach zu urteilen willst du die bzw. ein Zell-Adresse haben. Aber welche denn nun, die erste, irgendeine, die letzte, alle?

Das lässt sich wahrscheinlich mit einer Formel bewerkstelligen, ich würde da aber VBA vorziehen.

Beverly
27.06.2015, 18:16
Hi,

vielleicht hilft dir das hier weiter: Link zur Seite (http://www.excelformeln.de/formeln.html?welcher=213)

<hr width="20%" align="left"><img src="http://excel-inn.de/images/grusz.gif" height=35" align="left" alt="Grußformel"><a href="http://excel-inn.de/"><img border="0" src="http://excel-inn.de/images/logo1.gif" height=35" align="middle" alt="Beverly's Excel - Inn"></a>

Platipus
28.06.2015, 09:08
Hi,

wenn Du nicht ans Excel gekettet bist, hier ein paar Ansätze mit einem ANKHOR FlowSheet.
Meine Testtabelle hat eigentlich 10 Zeilen mit Zufallswerten die ich aber einfach gedoppelt habe.

http://i.imgur.com/Z4pXfXG.png

Wenn ich mit der Maus über den Ausgang von colmax fahre, bekomme ich die max. Werte der einzelnen Spalten angezeigt.

http://i.imgur.com/o0GStP6.png

Der rankrow gibt mir dann die Zeilennummern der Werte zurück. Allerdings nur die der ersten Fundstellen.

http://i.imgur.com/4m6tNR9.png

Ich kann mir aber auch ein Makro bauen.

Ich hole mir die Spalte 'o1' und den Maximalwert
http://i.imgur.com/gZRunCa.png

Mit dem eq schaue ich nach den Fundstellen in der Tabelle
http://i.imgur.com/pucuEFY.png

Der rowIndex liefert alle Zeilennummern.
http://i.imgur.com/Zj5dwDU.png

Der select erzeugt mit Hilfe der Ergenisse aus dem Vergleich eine Tabelle mit den Zeilennummern.
http://i.imgur.com/vnQzSBC.png

Und der joincols baut mir meine Tabelle mit den MaxWerten und deren Zeilennummern.
http://i.imgur.com/9BqIKjP.png

Ich kann das Makro auch für die ganze Tabelle wiederverwenden.

Zuerst hole ich mir die Namen der Spalten und lasse eine Schleife laufen.
Die Ergebnisse werden in eine Tag/Value Liste gepackt.

http://i.imgur.com/vs7TLqd.png

http://i.imgur.com/rlBV2nk.png

http://i.imgur.com/TFv40Y4.png

Platipus

CruelDevice
29.06.2015, 07:10
Hallo ???,

als Benutzerdefinierte Funktion:

Public Function Adresse_Maxwert(r As Range) As String
Dim m As Variant
Dim c As Range
m = Application.WorksheetFunction.Max(r)

For Each c In r
If c.Value2 = m Then
Adresse_Maxwert = c.Address
Exit For
End If
Next c
End Function

Viele Grüße,
Michael

EarlFred
29.06.2015, 08:26
@Michael,

Ich habe 6 Spalten zu je 100.000 Zeilen.
beim Prüfen von Einzelzellen, besonders in dieser Größenordnung, kann es eine Weile dauern, bis die Zelle gefunden wird.

Schneller dürfte es sein, ein Datenfeld zu durchlaufen:
Option Explicit


Public Function Adresse_Maxwert(r As Range) As String
Dim fValues As Variant, i As Long, j As Long
Dim dblMax As Double
Dim strAdresse As String

fValues = r.Value

For i = LBound(fValues, 1) To UBound(fValues, 1)
For j = LBound(fValues, 2) To UBound(fValues, 2)
If fValues(i, j) > dblMax Then dblMax = fValues(i, j)
Next j
Next i

For i = LBound(fValues, 1) To UBound(fValues, 1)
For j = LBound(fValues, 2) To UBound(fValues, 2)
If fValues(i, j) = dblMax Then
strAdresse = strAdresse & "; " & Cells(i, j).Address
End If
Next j
Next i

Adresse_Maxwert = Replace(strAdresse, "; ", "", , 1)

End Function

Grüße
EarlFred

CruelDevice
29.06.2015, 08:32
Schneller dürfte es sein, ein Datenfeld zu durchlaufen

Auf jeden Fall. Hätte ich auch so gemacht, wenn ich den Satz mit 600.000 Spalten bewusst wahr genommen hätte :rolleyes:

Vielen Dank für's aufpassen.

Viele Grüße,
Michael

GMG-CC
29.06.2015, 10:14
Moin,

ich habe schon seit einiger Zeit eile Lösung auf dem Rechner. Aber ich frage mich: Warum soll ich sie hier posten wenn der TE es nicht für nötig hält, auf eine IMHO wichtige Frage bezüglich des gewünschten Ergebnisses (Posting #3) einzugehen.

Ich glaube dass wir Helfer uns keinen Gefallen tun, wenn wir solch eine (in meinen Augen) Ignoranz des Fragestellers einfach tolerieren, hinnehmen. Zu oft hen wir alle doch erlebt, dass dann irgendwann ein "das meinte ich aber so ond so" vom TE kommt und das meiste von unserer Arbeit dann zumindest Großteils für di Tonne war …

Platipus
30.06.2015, 08:19
Na ja, vielleicht hat er ja vergessen die Benachrichtigungsfunktion einzustellen oder so :)

Würde mich interessieren, wie lange die Version von @EarlFred braucht, um durch die 600000 Zellen zu laufen.

Grüße
Platipus

Mc Santa
30.06.2015, 08:37
Na ja, vielleicht hat er ja vergessen die Benachrichtigungsfunktion einzustellen oder so :)

Würde mich interessieren, wie lange die Version von @EarlFred braucht, um durch die 600000 Zellen zu laufen.

Grüße
Platipus

Hallo,

bei mir dauert die Formel von EarlFred etwa 0,3sek zum Durchlaufen bei 600k Zellen.

VG
Santa

Platipus
30.06.2015, 08:58
Danke!

Also unter der Wahrnehmungsgrenze.

Kannst Du mir sagen, wieviele Kerne und welcher Takt Dein PC hat?
Dann kann man es besser einordnen.

Platipus

CruelDevice
30.06.2015, 09:09
Hallo,

ein kurzer Vergleich meiner Funktion mit der von EarlFred:

6 Spalten, komplett mit Zufallszahlen gefüllt (also 6 * 1.048.575 Zellen),
Der (erste) höchste Wert ist in Zelle D634465.

Meine Range-Variante: 9329 ms
Earlfreds Array-Variante: 2090 ms

Also locker um den Faktor 4 schneller.

Viele Grüße,
Michael

Platipus
30.06.2015, 10:03
Ok.

Ich habe mal mit dem FlowSheet gemessen.

Tabelle 6 * 1.000.000, Ergebnisse
http://i.imgur.com/sYm7Hfi.png

8 ms
http://i.imgur.com/Db3PruD.png

Das Ganze nochmal mit einer etwas größeren Tabelle.
6 * 100.000.000

1433 ms
http://i.imgur.com/Hscdjd0.png

Bei 140.000.000 braucht er dann auch ~2s
http://i.imgur.com/AuM0ubQ.png

Die ganze Sache läuft auf einem I7 3930K mit ~4.2 GHz.

Ich habe das FlowSheet hier angehängt. We Lust hat, kann es ja mal auf seinem Rechner laufen lassen.

Grüße
Platipus

CruelDevice
30.06.2015, 10:25
Hi Platipus,

in welcher Zelle steht denn die höchste Zahl? Und Benutzt Du auch double Werte? Damit die Werte vergleichbar sind.
Viele Grüße,
Michael

Platipus
30.06.2015, 11:02
Hier eine Ausgangstabelle mit Floats.
http://i.imgur.com/IEEwYQ0.png

Gefunden hat er die Max-Werte an folgenden Positionen:
http://i.imgur.com/AQ8KHG4.png

Nach 21 ms war die Sache durch.
http://i.imgur.com/bcLwEsx.png

OOPS. Ich hatte die ganze Zeit noch eine VM mitlaufen. Ohne den Störer geht's noch etwas schneller :)

2096 ms mit 6 * 190.000.000
http://i.imgur.com/H8rWZ4D.png

Gefunden hat er dabei lediglich 1084 Max-Werte.

Grüße
Platipus