[Excel, VBA] Ermitteln von Werten aus DatenMatrix (SVerweis advanced)

Gepostet am: Jun 16, 2011 6:40:9 PM

Für den Fall, dass man noch nicht weiß, wie der SVERWEIS funktioniert, hier mal eine kurze Zusammenfassung.

[Hier] noch ein ausführlicheres Video.

Für den schnellen Überblick noch mal ein Screenshot:

Soweit mal zu den Grundlagen, die mit Sicherheit fast jeder Excel-Benutzer kennt. Analoges gilt auch für den WVERWEIS.

Wie sieht es aber aus, wenn man nicht weiß, in welcher Spalte man nachschlagen muss. Zur Verdeutlichung ein anderes Beispiel:

Ich möchte jetzt in einem einerseits auswählen können, wessen Punkte ich ermitteln möchte, und andererseits, welche Art von Punkten (Zwischentest, Mitarbeit, Endklausur, Gesamt).zB:

Wenn man immer nach Gesamt suchen würde, könnte man einen SVERWEIS mit Spaltenindex = 5 angeben. Da dieser Wert aber auch variabel sein soll, kann man mit dem SVERWEIS nicht direkt arbeiten. Man muss sich zuerst überlegen, wie man auf den Spaltenindex für den jeweiligen Wert kommt. Dafür kann man die Funktion VERGLEICH bemühen. Dazu wieder ein kleines Video:

Wie das für unser konkretes Beispiel aussieht:

Wie man sieht, gibt es noch andere Möglichkeiten, die richtigen Werte zu ermitteln. 

Nun aber zu meiner VBA-Lösung, da sie - in meinen Augen - die verständlichste Syntax für dieses Problem bereitstellt. 

=WERTausTABELLE(Spaltenüberschrift;gesuchteSpaltealsText;ZeilenNamen;gesuchteZeileasText;Suchbereich)

und nun zum Quellcode (Anmerkung: die Funktion, die ich in der Praxis verwende ist um einiges kürzer, aber auch viel schwerer lesbar.

Funktion WertAusTabelle

Public Function WertausTabelle(Spaltenueberschrift As Range, gesuchteSpalte As String, _     Zeilennamen As Range, gesuchteZeile As String, Suchbereich As Range) As VariantIf Spaltenueberschrift.Rows.Count <> 1 Then     WertausTabelle = "#Fehler (#1)"     Exit FunctionEnd IfIf Zeilennamen.Columns.Count <> 1 Then     WertausTabelle = "#Fehler (#2)"     Exit FunctionEnd IfIf Zeilennamen.Rows.Count <> Suchbereich.Rows.Count Then     WertausTabelle = "#Fehler (#3)"     Exit FunctionEnd IfIf Spaltenueberschrift.Columns.Count <> Suchbereich.Columns.Count Then     WertausTabelle = "#Fehler (#4)"     Exit FunctionEnd IfDim myCell As Range Dim bfound As Boolean bfound = FalseDim iSpalte As Integer  iSpalte = 0For Each myCell In Spaltenueberschrift     iSpalte = iSpalte + 1     If myCell.Value = gesuchteSpalte Then         bfound = True         Exit For     End IfNextIf bfound = False Then     WertausTabelle = "#Fehler (#5)"     Exit FunctionEnd If  bfound = FalseDim iZeile As Integer iZeile = 0For Each myCell In Zeilennamen     iZeile = iZeile + 1     If myCell.Value = gesuchteZeile Then         bfound = True         Exit For     End IfNextIf bfound = False Then     WertausTabelle = "#Fehler (#6)"     Exit FunctionEnd If  WertausTabelle = Suchbereich.Cells(iZeile, iSpalte).Value End Function

Ähnliches kann man natürlich auf Tabellen anwenden, die keine Spaltenüberschriften und ZeilenNamen haben:

WertAusZelle

Public Function WertAusZelle(Spalte As Integer, Zeile As Integer, Optional Bereich As Variant) As VariantIf IsMissing(Bereich) Then     WertAusZelle = ActiveSheet.Cells(Zeile, Spalte).Value Else     Dim myBereich As range     If TypeName(Bereich) = "Range" Then         Set myBereich = Bereich         WertAusZelle = myBereich.Cells(Zeile, Spalte).Value     ElseIf TypeName(Bereich) = "String" Then         Set myBereich = range(Bereich)         WertAusZelle = myBereich.Cells(Zeile, Spalte).Value     Else         WertAusZelle = ActiveSheet.Cells(Zeile, Spalte).Value     End IfEnd IfEnd Function