AW: Auslesen der Jahreszahlen aus Textzellen
19.04.2021 22:10:19
Yal
Hallo Merlin,
trotz bereits gegebene Antwort von Lupo, hier eine VBA-Variante (vielleicht leichter zu erklären, falls Du die Lösung dein Lehrer vortragen möchtest ;-)
Erst die Version "wir nehmen den JSON schön brav auseinander". Schöner wäre mit der Verwendung einer JSON-Library.
Die rabiatte Version unten.
Const T = "{""birthyear"":2001,""zipcode"":""69123"",""city"":""Heidelberg"",""income"":3000},{""birthyear"":1994,"" _
zipcode"":""10318"",""city"":""berlin""},{""birthyear"":1986,""zipcode"":""12203"",""city"":""Berlin""},{""birthyear"":1997,""zipcode"":""12109"",""city"":""Berlin"",""income"":1200}"
Private Type Bewerber
Geburtsjahr As Integer
PLZ As String
Stadt As String
Einkommen As Integer
End Type
Public Sub Test()
Debug.Print "Durchschnittsalter ist: " & Durchschnittsalter(T)
End Sub
Private Function Durchschnittsalter(T As String) As Single
Dim E
Dim A, i
Dim B() As Bewerber
Dim S As Single
A = Splitter(T, "{", "}")
ReDim B(UBound(A))
For i = 0 To UBound(A)
For Each E In Split(A(i), ",")
Select Case Split(E, ":")(0)
Case """birthyear""": B(i).Geburtsjahr = Split(E, ":")(1)
Case """zipcode""": B(i).PLZ = Split(E, ":")(1)
Case """city""": B(i).Stadt = Split(E, ":")(1)
Case """income""": B(i).Einkommen = Split(E, ":")(1)
End Select
Next
Next
For i = 0 To UBound(B)
S = S + Year(Now) - B(i).Geburtsjahr
Next
Durchschnittsalter = S / (UBound(B) + 1)
End Function
Private Function Splitter(Text As String, Lefter As String, Righter As String)
Dim A(), i
Dim Anfang, Ende
Dim An As Boolean
Do
If Not An Then
Anfang = InStr(i + 1, Text, Lefter)
An = True
Else
Ende = InStr(i + 1, Text, Righter)
An = False
End If
If Not An Then
ReDim Preserve A(Ubound0(A) + 1)
A(UBound(A)) = Mid(Text, Anfang + 1, Ende - Anfang - 1)
i = Ende
End If
i = i + 1
Loop While i
Weniger zimperlich geht es mit.
Const T = "{""birthyear"":2001,""zipcode"":""69123"",""city"":""Heidelberg"",""income"":3000},{""birthyear"":1994,"" _
zipcode"":""10318"",""city"":""berlin""},{""birthyear"":1986,""zipcode"":""12203"",""city"":""Berlin""},{""birthyear"":1997,""zipcode"":""12109"",""city"":""Berlin"",""income"":1200}"
Sub rechne()
Dim S, C, pos
Do
pos = InStr(pos + 1, T, "birthyear")
If pos > 0 Then
S = S + 2021 - CInt(Mid(T, pos + 11, 4))
C = C + 1
End If
Loop While pos > 0
Debug.Print "Durchschnittsalter ist : " & S / C
End Sub
VG
Yal