Korrespondierender Spaltenwert gesucht


tklustig

Erfahrenes Mitglied
#1
Hallo Leute,
meine Exceltabelle hat folgenden Aufbau
Spalte A Spalte B
2016 3,23
2017 5,56
2018 12,89
2019 8,79

Folgendes VBA Script gibt den minimalen Wert der Spalte B in einer Zelle aus. Wie bekomme ich den korrespondierenden Spaltenwert A?
Visual Basic:
Option Explicit
Sub Schaltfläche1_Klicken()
Dim max, min As Double
Dim jahr As Integer
MsgBox "Berechnet wurden die Extrema der Jahre " & Sheets("Tabelle1").Cells(3, 2).Value & " bis " & Sheets("Tabelle1").Cells(23, 2) & vbCrLf & "Die entsprechenden Zellen werden grün(Max) und rot(Min) unterwandert!"
max = WorksheetFunction.max(ActiveSheet.Range("C3:C23")) 'größter Wert von C3 bis C23
min = WorksheetFunction.min(ActiveSheet.Range("C3:C23")) 'kleinster Wert von C3 bis C23
Cells(52, 5) = min & "(im Jahr " & jahr & ")" 'Wie komme ich an den korrespondierenden Spaltenwert des Jahres?"
Cells(51, 5) = max ' Wie komme ich an den korrespondierenden Spaltenwert des Jahres?
Cells(51, 5).Interior.ColorIndex = 4 'grün
Cells(52, 5).Interior.ColorIndex = 3 'rot
End Sub
 

Zvoni

Erfahrenes Mitglied
#2
Es gibt einen Ersatz für den SVERWEIS, mit welchem man auch auf Spalten links vom Suchkriterium zugreifen kann.
Ich such es morgen im Büro raus
 

Zvoni

Erfahrenes Mitglied
#3
Habs gefunden. Schau mal ob du damit was anfangen kannst.

=INDEX(ZielBlatt!ErgebnisSpalte:ErgebnisSpalte;VERGLEICH(AktuellesBlatt!SuchZelle;ZielBlatt!SuchSpalte:SuchSpalte;0))

Kann man auf WorksheetFunction umbauen!

EDIT:
Ungetestet:
Code:
Cells(51, 5)=WorksheetFunction.Index(ActiveSheet.Range("A:A"), WorksheetFunction.Match(Max, ActiveSheet.Range("C:C")))
Dasselbe dann für Min
Ich gehe davon aus, dass die Jahreszahl in Spalte A steht (Dein Beispiel sagt, die Werte stehen in Spalte B, im Code benutzt du Spalte C)
 
Zuletzt bearbeitet:

Zvoni

Erfahrenes Mitglied
#4
Mir war langweilig
Visual Basic:
Dim max, min As Double
Dim jahr As Integer
MsgBox "Berechnet wurden die Extrema der Jahre " & Sheets("Tabelle1").Cells(3, 2).Value & " bis " & Sheets("Tabelle1").Cells(23, 2) & vbCrLf & "Die entsprechenden Zellen werden grün(Max) und rot(Min) unterwandert!"
max = WorksheetFunction.max(ActiveSheet.Range("C3:C23")) 'größter Wert von C3 bis C23
min = WorksheetFunction.min(ActiveSheet.Range("C3:C23")) 'kleinster Wert von C3 bis C23
'In Spalte A stehen die Jahreszahlen, ansonsten anpassen
jahr = WorksheetFunction.Index(ActiveSheet.Range("A:A"), WorksheetFunction.Match(min, ActiveSheet.Range("C:C")))
Cells(52, 5) = min & "(im Jahr " & jahr & ")" 'Wie komme ich an den korrespondierenden Spaltenwert des Jahres?"
'In Spalte A stehen die Jahreszahlen, ansonsten anpassen
jahr = WorksheetFunction.Index(ActiveSheet.Range("A:A"), WorksheetFunction.Match(max, ActiveSheet.Range("C:C")))
Cells(51, 5) = max & "(im Jahr " & jahr & ")"  ' Wie komme ich an den korrespondierenden Spaltenwert des Jahres?
Cells(51, 5).Interior.ColorIndex = 4 'grün
Cells(52, 5).Interior.ColorIndex = 3 'rot
 

tklustig

Erfahrenes Mitglied
#5
Funktioniert nicht. Dein Script liefert nur den korrekten Value, die Jahreszahl ist jedoch inkorrekt. Übrigens in beiden Fällen(jahrMax)!
Visual Basic:
Option Explicit
Sub Schaltfläche4_Klicken()
Dim max, min As Double
Dim jahrMin, jahrMax As Integer
'MsgBox "Berechnet wurden die Extrema der Jahre " & Sheets("Tabelle1").Cells(3, 2).Value & " bis " & Sheets("Tabelle1").Cells(23, 2) & vbCrLf & "Die entsprechenden Zellen werden grün(Max) und rot(Min) unterwandert!"
max = WorksheetFunction.max(ActiveSheet.Range("H3:H21")) 'größter Wert von H3 bis H21
'In Spalte G stehen die Jahreszahlen, in Spalte H die Values
jahrMax = WorksheetFunction.Index(ActiveSheet.Range("G3:G21"), WorksheetFunction.Match(max, ActiveSheet.Range("H3:H21")))
jahrMax = WorksheetFunction.Index(ActiveSheet.Range("G:G"), WorksheetFunction.Match(max, ActiveSheet.Range("H:H")))
'Ausgabe in J52
Cells(52, 10) = max & "(im Jahr " & jahrMax & ")"
End Sub
so ist die Tabelle aufgebaut
Code:
Jahr(G3-G27)    Preis(H3-H27)
   
2000    5,37
2001    4,88
2002    4,18
2003    4,31
2004    5,35
2005    5,89
2006    9,17
2007    9,77
2008    10,11
2009    10,47
2010    15,26
2011    25,29
2012    24,21
2013    17,99
2014    14,34
2015    14,15
2016    15,63
2017    15,15
2018    13,34
2019(Q1)    13,81
2019(Q2)    13,43
   
2020    20,86
2025    25,19
2030    29,39
 
Zuletzt bearbeitet: