Avatar billede h_s Forsker
30. marts 2014 - 19:45 Der er 27 kommentarer og
1 løsning

Rangering af tabeller

Rangering

Jeg har i et regneark ca 250 tabeller.
De er på 10 rækker hver og mellem 5-10 kolonner.
Kolonne 1 er afdelingsnavn
Efterfølgende kolonner er budget mål, opnået salg periode og salg åtd.
Den sidste kolonne er målopfyldelse.

Tal til tabellerne hentes via opslag fra en stor datafil.

Pt. er alle tabeller sorteret efter kolonne 1 = afdelingsnavn
Set ønsker jeg at lave om så så de er sorteret efter målopfyldelse.

Hvordan gør jeg det smartest, så jeg ikke skal til at flytte alle opslag til et nyt område i regnearket og så lave rangering med Plads() i den nuværende tabel?
Kan det gøres med en VBA kode eller lægge noget sortering henover det eksisterende?

Jeg har prøvet at stille spørgsmålet tidligere og her blev jeg opfordret til at finde en løsning med VBA:
http://www.eksperten.dk/spm/993184

På forhånd tak!
Avatar billede 50573433 Nybegynder
30. marts 2014 - 20:11 #1
Ring til mig (50573433).
Det kan være, at jeg kan hjælpe dig, men jeg bliver helt forvirret af din forklaring.
Peter
Avatar billede supertekst Ekspert
31. marts 2014 - 08:54 #2
Du er velkommen til at sende filen/model. @-adresse under min profil.
Avatar billede supertekst Ekspert
31. marts 2014 - 09:01 #3
Har set dit upload. Hvad mener du med følgende"" ""?:
"Løsningen med at sortere efter målopfyldelse er der, men da der er 250 tabeller og det ""sorteringen kan være forskellig fra gang til gang"" er det noget omstændigt."
Avatar billede h_s Forsker
31. marts 2014 - 09:29 #4
Supertekst> Jeg mener at løsningen skal håndtere 250 selvstændige tabeller. Havde der kun været 1 ville jeg lave det manuelt fra gang til gang. Men når der er 250, stå tager det meget tid.
Rangeringen ændres hver måned, så det er en løsning der skal håndteres løbende, så en manuel løsning vil betyde 250 * 12 = 3000 rangeringer pr. år.

Tabeller betyder små overblik på 10 rækker med 5-10 kolonner.
Dvs. der typisk er 15 tabeller på et regneark.
Avatar billede supertekst Ekspert
31. marts 2014 - 09:55 #5
Ok - det er ikke noget større problem at sortere et større antal via VBA - men det stiller følgende spørgsmål:

- er tabeller lige store - eller er der samme afstand mellem dem.
- d.v.s. at der er flere ark, der skal sorteres tabeller på.
Avatar billede h_s Forsker
31. marts 2014 - 10:21 #6
Nej tabellerne er ikke lige store og har heller ikke samme afstand.

De er vidt forskellige alle 250.

Jeg tænkte om man kan navngive hver tabel og sorteringsområde og så bruge det i makroen.

På den måde sikre jeg mig at bliver der ændret i tabellen skal jeg ikke ændre i makroen.

Men hvordan?
Avatar billede supertekst Ekspert
31. marts 2014 - 12:58 #7
Er i gang iflg. #6
Er målopfyldelsen altid sidste kolonne?
Avatar billede h_s Forsker
31. marts 2014 - 14:16 #8
Nej. Det er typisk den 2. sidste.
Den sidste kolonne er i de fleste tilfælde en point kolonne - Størst point = størst målopfyldelse.

I ca. 10 tabeller er der en ekstra kolonne efter målopfyldelse og Point.
Avatar billede supertekst Ekspert
31. marts 2014 - 14:55 #9
Tak - så må man kunne identificere målopfyldelse efter overskriften?

Jeg spurgte tidligere om de så skulle opfattes således, at tabellerne fordeler sig over flere ark? (#5)

PS: Det igangværende VBA ser lovende ud.
Avatar billede h_s Forsker
31. marts 2014 - 15:25 #10
#5, ja der er ca. 30 ark og der er ikke samme størrelse.
Overskrift vil være fint!
Avatar billede supertekst Ekspert
31. marts 2014 - 15:51 #11
Rem Anbringes i ThisWorkbook
Rem ========================
Public Sub sorteringAfTabeller()
Const sortOverskrift = "Målopfyldelse"

Dim nn As Name, arkNavn As String, ræk1 As Integer, rækX As Integer, antalKol As Integer, sortKol As Integer
    antalNavne = ActiveWorkbook.Names.Count
    Application.ScreenUpdating = False
   
    For Each nn In ActiveWorkbook.Names
Rem marker tabel
        arkNavn = isolerArkNavn(nn)
        ActiveWorkbook.Sheets(arkNavn).Select
       
        nn.RefersToRange.Select
        ræk1 = Selection.Row
        antalræk = Selection.Rows.Count
       
        rækX = ræk1 + antalræk - 1
        antalKol = Selection.Columns.Count
        sortKol = findSortKolonne(ræk1, antalKol, sortOverskrift)
       
        UdførSortering arkNavn, ræk1 + 1, rækX, antalKol, sortKol
    Next
End Sub
Private Sub UdførSortering(arkNavn, fraRæk, tilRæk, antalKol, sortKol)
    ActiveWorkbook.Worksheets(arkNavn).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(arkNavn).Sort.SortFields.Add Key:=Range(Cells(fraRæk + 1, sortKol), Cells(tilRæk, sortKol)), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
       
    With ActiveWorkbook.Worksheets(arkNavn).Sort
        .SetRange Range(Cells(fraRæk, 1), Cells(tilRæk, antalKol))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Private Function findSortKolonne(ræk1, antalKol, overskrift)
Dim kol As Integer
    For kol = antalKol To 1 Step -1
        If Cells(ræk1 + 1, kol) = overskrift Then
            findSortKolonne = kol
            Exit Function
        End If
    Next kol
Rem ** Fejl ** Overskrift kan ikke identificeres
    Stop
End Function
Private Function isolerArkNavn(nn)
Dim wt As Variant, arkNavn As String
    wt = Split(nn, "!")
    arkNavn = Replace(wt(0), "=", "")
    arkNavn = Replace(arkNavn, "'", "")
    isolerArkNavn = arkNavn
End Function
Avatar billede h_s Forsker
31. marts 2014 - 19:33 #12
Hejsa

Jeg har prøvet at teste og har følgende kommentarer:

- Jeg har svært ved at se om makroen sorterer alle tabellerne
- Den stopper ved "stop". Når jeg så prøver at starte den igen, så får jeg en fejl 400??
- Skal jeg indsætte den på alle ark? Kan den kører på alle ark på en gang, så jeg er fri for at starte den 30 gange?
- Jeg har i et andet spørgsmål uploaded en prøvefil: http://gupl.dk/709060/ - Her har jeg opslag. Jeg tror man bliver nødt til at indsætte hele arket som værdier for at få det til at virke.
- Kan du prøve at beskrive lidt mere hvad den gør - evt. som hjælpetekst i makroen
Avatar billede supertekst Ekspert
31. marts 2014 - 23:12 #13
Hej

- VBA-koden indsættes i ThisWorkbook i VBA-vinduet (Alt+F11)
- Alle tabeller skal navngives
- Kolonne med Målopfyldelse skal have denne overskrift - kan den overskrift ikke findes - stopper processen

Du kan prøve at åbne VBA-vinduet og så "steppe" igennem koden med F8. I den første linje efter erklæringerne øverst =
"antalNavne = ActiveWorkbook.Names.Count"
kan du ved at pege på antalNavne aflæse det antal oprettede navne, der findes i filen.
Processen skal blot være nået til linjen efter denne.


Har en model der har tabeller på 2 ark - den kan du få hvis du sender en mail. @-adresse under min profil.
Avatar billede h_s Forsker
01. april 2014 - 15:23 #14
Hejsa

Følgende kommentarer:
- Du har indsat afdelingsnummer ud for hver tabel. Det er der ikke i "virkeligheden". Der står de kun en gang og så laves der opslag ud fra dem.
- Det betyder også at makroen ikke virker hos mig, da opslagene hele tiden modvirker rangeringen.
- Derfor er det nok nødvendigt at "kopier" og "indsætte som vædier" på de ark, hvor der er tabeller der skal rangeres.

Kan du rette det eller har du en anden ide?
Avatar billede supertekst Ekspert
01. april 2014 - 15:27 #15
Ok - har ikke været opmærksom på dette.
Ser om der er en vej udenom..
Avatar billede supertekst Ekspert
01. april 2014 - 16:20 #16
Tror ikke jeg kommer videre på nuværende grundlag.
Avatar billede h_s Forsker
08. april 2014 - 16:06 #17
Jeg kan sagtens leve med denne løsning ved at man kopier arkene og indsætter værdier.

MEN Jeg får en fejl om at jeg er Out of range. Hvad kan det betyde?
Det virker som om makroen slet ikke starter på at gå i gang. Kan det være fordi der er angivet 2 områder (tabeller) navngivet som lapper over de tabeller der skal rangeres?
Avatar billede supertekst Ekspert
08. april 2014 - 17:53 #18
Hvis der kommer en Debug knap - så prøv at aktivere denne se hvilke linje i VBA-koden den peger på.
Avatar billede h_s Forsker
09. april 2014 - 08:44 #19
Fejlen opstår når denne linje køres:
ActiveWorkbook.Sheets(arkNavn).Select

Fejlen er:

Runtime error '9':
Subscript out of range.

Jeg har fået koden til at søge efter "point" i stedet for "målopfyldelse". Det er rettet øverst:

Rem Anbringes i ThisWorkbook
Rem ========================
Public Sub sorteringAfTabeller()
Const sortOverskrift = "Point"


Kan det være det? Er der andre steder jeg skal rette det?
Avatar billede supertekst Ekspert
09. april 2014 - 09:43 #20
Prøv ar erstatte begyndelsen af koden med nedenstående:

Rem Anbringes i ThisWorkbook
Rem ========================
Public Sub sorteringAfTabeller()
Const sortOverskrift = "Point"

Dim nn As Name, arkNavn As String, ræk1 As Integer, rækX As Integer, antalKol As Integer, sortKol As Integer
On Error GoTo fejl

    antalNavne = ActiveWorkbook.Names.Count
    Application.ScreenUpdating = False
   
    For Each nn In ActiveWorkbook.Names
Rem marker tabel
        arkNavn = isolerArkNavn(nn)
       
        ActiveWorkbook.Sheets(arkNavn).Select
       
        nn.RefersToRange.Select
        ræk1 = Selection.Row
        antalræk = Selection.Rows.Count
       
        rækX = ræk1 + antalræk - 1
        antalKol = Selection.Columns.Count
        sortKol = findSortKolonne(ræk1, antalKol, sortOverskrift)
       
        UdførSortering arkNavn, ræk1 + 1, rækX, antalKol, sortKol
    Next
   
    Exit Sub

fejl:
    MsgBox "Navn: " & nn & " Arknavn: " & arkNavn
    Stop
End Sub
Avatar billede supertekst Ekspert
09. april 2014 - 09:51 #21
Prøv at erstatte bestående VBA i begyndelsen af koden med dette:

Public Sub sorteringAfTabeller()
Const sortOverskrift = "Point"

Dim nn As Name, arkNavn As String, ræk1 As Integer, rækX As Integer, antalKol As Integer, sortKol As Integer
On Error GoTo fejl

    antalNavne = ActiveWorkbook.Names.Count
    Application.ScreenUpdating = False
   
    For Each nn In ActiveWorkbook.Names
Rem marker tabel
        arkNavn = isolerArkNavn(nn)
       
        ActiveWorkbook.Sheets(arkNavn).Select
       
        nn.RefersToRange.Select
        ræk1 = Selection.Row
        antalræk = Selection.Rows.Count
       
        rækX = ræk1 + antalræk - 1
        antalKol = Selection.Columns.Count
        sortKol = findSortKolonne(ræk1, antalKol, sortOverskrift)
       
        UdførSortering arkNavn, ræk1 + 1, rækX, antalKol, sortKol
    Next
   
    Exit Sub

fejl:
    MsgBox "Navn: " & nn & " Arknavn: " & arkNavn
    Stop
Avatar billede h_s Forsker
09. april 2014 - 10:08 #22
Den skriver følgende i den msgBox du har indsat:
Navn: =#REF!REF! Arknavn: #REF

Hvad betyder det?
Avatar billede supertekst Ekspert
09. april 2014 - 10:27 #23
Prøv at se på de navne, der er oprettet.
Koden udtrækker arknavn af for de navne der er oprettet - idet dette indgår i definitionen af navnet: Ark Navn & område.
Avatar billede h_s Forsker
09. april 2014 - 10:34 #24
I nogle af tabelnavnene indgår ÆØÅ. Har det betydning?

Der var mellemrum og "-" i arknavnet. Det har jeg slettet mellemrum og "-". Det hjælper ikke.
Avatar billede supertekst Ekspert
09. april 2014 - 13:32 #25
ÆØÅ skulle ikke have nogen betydning

Det er i den sidste funktion arknavn uddrages:
Private Function isolerArkNavn(nn)
Dim wt As Variant, arkNavn As String
    wt = Split(nn, "!")
    arkNavn = Replace(wt(0), "=", "")
    arkNavn = Replace(arkNavn, "'", "")
    isolerArkNavn = arkNavn
End Function

Opdeler udtrykket for navnet efter "!"
Arknavn udtrækkes fra det første element i opdelingen
"=" erstattes af ""
"'" erstattes af ""

Det er vist hvad jeg kan oplyse.
Avatar billede h_s Forsker
09. april 2014 - 14:56 #26
Forstår ikke hvad du mener.. :-(

Kan du forklare hvad jeg evt. kan gøre?
Avatar billede supertekst Ekspert
09. april 2014 - 15:53 #27
Har ikke noget forslag - ved ikke hvad det er for et navn.
Avatar billede h_s Forsker
30. juli 2014 - 16:19 #28
Opgave lukket - virker ikke
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Vi har et stort udvalg af Excel kurser. Find lige det kursus der passer dig lige her.

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester



IT-JOB

MAN Energy Solutions

Principal Expert DevOps

Cognizant Technology Solutions Denmark ApS

Test Manager

LB Forsikring

Salesforce udvikler