Resultatlistor

Automatisk uppdatering av resultatlistor

I det här mer omfattande tipset visas hur vi kan dra nytta av att kombinera avancerade kalkylbladsfunktioner och programmering i VBA-miljö för att skapa automatisk uppdatering av resultatlistor.

Detta kan komma till nytta i många sammanhang, inte minst inom idrottsrörelsen och när vi hämtar och förädlar större datamängder av annat slag i XL.

Vill du få reda på hur vi:

Skapar ett spelsystem - Klicka här.

Skapar slumpmässig spelordning - Klicka här.

Exemplet bygger på följande förutsättningar:

Antal lag = 4 st

Antal omgångar = 4 st

Antal gjorda / insläppta mål: 0 - 999 st

Vid vunnen match erhålls 2 poäng, vid oavgjort 1 poäng och vid förlust 0 poäng.

Vid rangordning i resultatlistan sker sorteringen enligt följande förutsättningar:

1. Poäng

2. Målskillnad

3. Antal vunna matcher

Automatisk uppdatering av resultatlistan

Skapa matchprogrammet
Tabellen nedan visar matchprogrammet i sin helhet:

I kolumnen E räknar XL fram vinnaren eller vid oavgjort visas tecknet "o". Denna information ligger till grund för resultatlistan men kan med fördel döljas i en färdig modell.

Följande matrisformel ligger till grund för det och kopieras nedåt i kolumnen:

{=OM(C3:D3<>"";OM(C3>D3;A3;OM(D3>C3;B3;OM(C3=D3;"o")));"")}

Skapa den automatiska uppdaterade resultatlistan

Tabellen nedan visar resultatlistan i sin helhet:

Vid uppbyggnad av resultatlistan skrivs samtliga namnen in manuellt i listan.

Rubriken med antal omgångar genereras av följande formel:

{="Resultatlista efter "&SUMMA(N(E3:E6<>""))&" omgångar"}

Antal vunna matcher räknas fram mha följande matrisformel:

=PRODUKTSUMMA(($E$3:$E$6=B10)*1)

Antal oavgjorda matcher erhålls genom följande matrisformel:

=PRODUKTSUMMA(($A$3:$B$6=B10)*($E$3:$E$6="o"))

Antal förlorade matcher identifieras mha följande formel:

=PRODUKTSUMMA(($A$3:$B$6=B10)*($E$3:$E$6<>B10)*($E$3:$E$6<>"o"))

Antal gjorda respektive insläppta mål erhålls den mer komplexa (!) matrisformeln:

=PRODUKTSUMMA(($A$3:$A$6=B10)*($C$3:$C$6))+
PRODUKTSUMMA(($B$3:$B$6=B10)*($D$3:$D$6))&"-"
&PRODUKTSUMMA(($A$3:$A$6=B10)*($B$3:$B$6<>B10)*
($D$3:$D$6))+PRODUKTSUMMA(($B$3:$B$6=B10)*
($A$3:$A$6<>B10)*($C$3:$C$6))

Antal poäng summeras ihop mha formeln:

=SUMMA(D10*2;E10*1)

Räkna målskillnad och sortera listan
Dessa två aktiviteter sker varje gång det matas in tecken i arbetsbladet genom nedanstående VBA-lösningar.

I det berörda arbetsbladets modul placeras följande kod:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call MalSkillnad
Call Resultat_Sortering
End Sub

I en vanlig modul placeras följande procedurer:

Sub MalSkillnad()
Dim rnOmrade As Range
Dim rnCell As Range
Dim iAntalTecken As Integer
Dim iTextLangd As Integer

'I exemplet innefattas namnet "Mål" av cellområdet G10:G13
Set rnOmrade = Range("Mål")

'Här loopas cellområdet igenom och evaluering sker av
'innehållet
For Each rnCell In rnOmrade
'Här identifieras "-"-tecknets position och textlängden
      iAntalTecken = Application.WorksheetFunction.Find("-", rnCell)
      iTextLangd = Len(rnCell)

'Textlängden är den styrande faktorn förr vad som ska ske därav
'Select-satsen
Select Case iTextLangd
Case 3
      rnCell.Offset(0, 1).Value = CInt(Left(rnCell, 1)) - CInt(Right(rnCell, 1))
Case 4
If iAntalTecken = 2 Then
      rnCell.Offset(0, 1).Value = CInt(Left(rnCell, 1)) - CInt(Right(rnCell, 2))
ElseIf iAntalTecken = 3 Then
      rnCell.Offset(0, 1).Value = CInt(Left(rnCell, 2)) - CInt(Right(rnCell, 1))
Else
      rnCell.Offset(0, 1).Value = CInt(Left(rnCell, 1)) - CInt(Right(rnCell, 2))
End If
Case 5
If iAntalTecken = 3 Then
      rnCell.Offset(0, 1).Value = CInt(Left(rnCell, 2)) - CInt(Right(rnCell, 2))
ElseIf iAntalTecken = 4 Then
      rnCell.Offset(0, 1).Value = CInt(Left(rnCell, 3)) - CInt(Right(rnCell, 1))
Else
      rnCell.Offset(0, 1).Value = CInt(Left(rnCell, 1)) - CInt(Right(rnCell, 3))
End If
Case 6
If iAntalTecken = 4 Then
     rnCell.Offset(0, 1).Value = CInt(Left(rnCell, 3)) - CInt(Right(rnCell, 2))
Else
     rnCell.Offset(0, 1).Value = CInt(Left(rnCell, 2)) - CInt(Right(rnCell, 3))
End If

Case 7
     rnCell.Offset(0, 1).Value = CInt(Left(rnCell, 3)) - CInt(Right(rnCell, 3))
End Select
Next rnCell

End Sub

Avslutningsvis sker en sortering, vilket sker med följande procedur:

Sub Resultat_Sortering()
Dim rnSortering As Range
'Namnområdet Resultatlista omfattas i exemplet av
'B10:I13 - Ej placeringkolumnen!
Set rnSortering = Range("ResultatLista")

rnSortering.Sort _
          Key1:=Range("I10"), Order1:=xlDescending, _
          Key2:=Range("H10"), Order2:=xlDescending, _
          Key3:=Range("D10"), Order3:=xlDescending, _
          Orientation:=xlTopToBottom
End Sub