Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema ListBox
BildScreenshot zu ListBox ListBox-Seite mit Beispielarbeitsmappe aufrufen

Wert in Matrix finden

Betrifft: Wert in Matrix finden von: John Clark
Geschrieben am: 06.08.2008 14:43:03

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

  

Betrifft: AW: Wert in Matrix finden von: David
Geschrieben am: 06.08.2008 14:50:12

schau mal hier:

http://www.excelformeln.de/formeln.html?welcher=58

Kannst du sicher für deine Zwecke anpassen.

Gruß

David


  

Betrifft: AW: Wert in Matrix finden von: Daniel
Geschrieben am: 06.08.2008 14:58:37

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


  

Betrifft: AW: Wert in Matrix finden von: John Clark
Geschrieben am: 06.08.2008 15:35:34

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


  

Betrifft: AW: Wert in Matrix finden von: Daniel
Geschrieben am: 06.08.2008 17:03:10

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)


  

Betrifft: AW: Wert in Matrix finden von: Erich G.
Geschrieben am: 06.08.2008 17:50:31

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


  

Betrifft: AW: Wert in Matrix finden von: John Clark
Geschrieben am: 06.08.2008 19:29:47

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.


  

Betrifft: AW: Wert in Matrix finden von: Daniel
Geschrieben am: 06.08.2008 20:49:00

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


  

Betrifft: AW: Wert in Matrix finden von: John Clark
Geschrieben am: 06.08.2008 22:50:52

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


  

Betrifft: AW: Wert in Matrix finden von: Daniel
Geschrieben am: 06.08.2008 23:29:55

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


 

Beiträge aus den Excel-Beispielen zum Thema "Wert in Matrix finden"