Anzeige
Archiv - Navigation
996to1000
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Wert in Matrix finden
06.08.2008 14:43:00
John
Hallo Zusammen,
ich suche eine Lösung für folgendes Problem und komme mit den Standardformeln nicht weiter...
Ich habe eine Matrix mit z.B. 4 x 4 Zellen - also z.B. Bereich C1:F4 und die B1:B4-Spalte hat den Wert, den ich suche.
Jetzt habe ich also einen bestimmten Wert und der ist irgendwo in der Matrix - als nicht in einer festen Spalte - und den suche ich und möchte dann aus der betroffenen Zeile den Wert aus der ersten Spalte.
Die klassischen "Verweise" brauchen ja für den Suchbereich immer eine feste Spalte - das habe ich hier aber ja nicht. Kann ich das irgendwie anders abbilden?
Tausend Dank schonmal vorab.
CU
JC

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Wert in Matrix finden
06.08.2008 14:58:37
Daniel
Hi
wenn der Wert nur genau einmal in der Matrix vorkommt, dann so:
(B3:E8 ist die Matrix, 7 der Suchwert)
=SUMMENPRODUKT((B3:E8=7)*ZEILE(B3:E8))
=SUMMENPRODUKT((B3:E8=7)*SPALTE(B3:E8))
die gefundenen Werte sind die Excel- Zeilen- und Spaltennummern und nicht die Index-Werte innerhalb der Suchmatix wie sonst üblich bei der Verweis-funktion.
Gruß, Daniel

Anzeige
AW: Wert in Matrix finden
06.08.2008 15:35:34
John
Hallo Zusammen,
vielen Dank für Eure Hilfe.
Die Lösung von Daniel passt super - allerdings zieht das Summenprodukt schon ganz schön an der Performance - meine Datei ist schon relativ gross.
Eine Excel-eigene Formel gibt es da ja anscheinend leider nicht.
Also vielen Dank Euch Beiden für Eure Hilfe.
CU
JC

AW: Wert in Matrix finden
06.08.2008 17:03:00
Daniel
Hi
die könnte man sich ja per VBA schreiben.
obs allerdings schneller ist als die Summenprodukte musst du mal testen:

Public Function MatrixPos(Suchwert As Variant, Suchmatrix As Range, ZeileOderSpalte As Byte) As  _
Long
Dim Zelle As Range
Set Zelle = Suchmatrix.Find(what:=Suchwert, LookIn:=xlValues, lookat:=xlWhole)
If Zelle Is Nothing Then
MatrixPos = 0
Else
Select Case ZeileOderSpalte
Case 1
MatrixPos = Zelle.Row
Case 2
MatrixPos = Zelle.Column
End Select
End If
End Function


1. Parameter der Suchwert
2. Parameter die Matrix
3. Parameter der Rückgabewert: 1-Zeile 2-Spalte
zurückgegben wird wieder die Excel-Koordinate, nicht die Suchmatrix-Bezogene
Gruß. Daniel
PS diese Variante würde auch bei mehrfach vorkommenden Suchbegriffen einen Sinnvollen Rückgabewert geben (den ersten gefunden)

Anzeige
AW: Wert in Matrix finden
06.08.2008 17:50:00
Erich
Hallo Vorname,
was spricht gegen Davids Vorschlag, sich der Formeln in http://www.excelformeln.de/formeln.html?welcher=58
zu bedienen?
Hier ein Beispiel:
 CDE
1112211
2123321
3134431
4145541
5156651
6167761
7178871
8189981
91911091
1020121101
11   
12Suchwert61 
13Spalte3 
14Wert in C16 
15   
16oder  
17Zeile6 
18Wert in C16 

Formeln der Tabelle
ZelleFormel
D13{=MIN(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN($C$1:$C$10;;SPALTE(1:1)-3;;); $D$12)>0; SPALTE(1:1)-2))}
D14=INDEX(C1:C10;VERGLEICH($D$12;BEREICH.VERSCHIEBEN(C:C;;D13-1;;); 0))
D17=VERGLEICH($D$12;BEREICH.VERSCHIEBEN(C:C;;D13-1;;); 0)
D18=INDEX(C1:C10;D17)
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Du brauchst daraus nur die Formeln in D13 und D14.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Anzeige
AW: Wert in Matrix finden
06.08.2008 19:29:00
John
N'Abend,
die Lösungen führen schon zum Erfolg - sind nur in meinem Fall nicht praktikabel.
Meine Datei hat im gespeicherten Zustand bereits 25 MB - die sich in geöffneten Zustand auf ein RAM-Volumen vonn ca. 200 MB mit weiteren 3 Dateien ausweiten.
Wenn ich jetzt in dem betroffenen Datensatz nochmal 5000 Zellen mit so umfangreichen Formeln dicht mache, dann sinkt meine Performance in einen Bereich, der nicht mehr akzeptabel ist. Aus diesem Grund hatte ich gehofft, dass es eben eine Formel vom "Volumen" Verweis gibt, die das gleiche erreicht.
Aus meiner Erfahrung heraus brauchen die Verweis-Formeln auch weniger "Performance".
Ich schau jetzt mal, dass ich das über Zwischentabellen löse, die weniger Abfragelogik beinhalten.
Euch allen trotzdem vielen Dank.

Anzeige
AW: Wert in Matrix finden
06.08.2008 20:49:00
Daniel
HI

Aus meiner Erfahrung heraus brauchen die Verweis-Formeln auch weniger "Performance".


hi das hängt in erster Linie von der Art des Verweises ab:
- Verweis mit 3. Parameter = 0 in unsortierten Listen verbraucht sehr viel Performance
- Verweis mit 3. Parameter = 1 in sortierten Listen braucht sehr wenig Performance
dh wer mit grossen Datenmengen arbeitet, und verweise verwendet, sollte zwingend auf eine geschickte sortierung achten.
Gruß, Daniel

AW: Wert in Matrix finden
06.08.2008 22:50:00
John
Hallo Daniel,
jetzt noch ne Frage dazu... meinst du mit unsortiert dann eben A-Z... oder etwas Anderes?
Und - 3. Parameter = 0 - meinst du damit eben Falsch und bei = 1 - dann WAHR?
Also SVERWEIS(Suchkriterium; Bereich; Spalte; WAHR) ?
CU
JC

Anzeige
AW: Wert in Matrix finden
06.08.2008 23:29:00
Daniel
Hi
ja, genau das meine ich. 0 und 1 tippt sich halt einfacher als WAHR und FALSCH ;-)
btw. hast du meine alternative selbstgeschriebene Formel zur Matrix-Suche schon mal getestet?
Gruß, Daniel

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige