Begrænsing i Excel 2010

Hej Cicci,

Dette er et problem jeg tit støder på.
Excel har store problemer med og håndtere mange formler. Jeg har en tommelfingerregel, der hedder max 100.000 formler, og hvis det er opslags formler eller andre med mange parameter, så er det max 10.000 formler der kan bruges.

Jeg har været ude for og få en ny computer, for og kunne give Excel bedre arbejdsbetingelser, og dette gav ikke et betydligt bedre resultat.

En løsning der dog virker er og reducere dine formler. Slå dem sammen til en der hvor det kan lade sig gøre.
Jeg skal ofte skrive en tekst dato om til en formel, og brugte tidligere 4 steps for og gøre dette. 3 kolonner hvor jeg havde Dag, måned og år hentet med Left() right() og mid() formler, for så i 4. kolonne og samle dem igen YEAR(a1;b1;c1). men den sidst nævnte formel kan skrives direkte YEAR(left;right;mid).
Dertil så er VBA en bedre mulighed for og få dine ting beregnet.

Jeg må hellere skrive jeg ingen teknisk forstand har på dette, eller uddannelse. Dette er mine erfarringer, jeg håber de kan give lisdt indput til din opgave.

Vedrørende problemet med manglende beregning af formlerne. Har du checket, at der under Beregningsindstillinger er sat prik ved Automatisk? Hvis du eventuelt har mulighed for at dele filen (eller en annonymiseret udgave heraf) så skal jeg gerne forsøge at hjælpe.

Hans

Hej Martin.

Jeg kan desværre ikke reducere mine formler mere end de allerede er. Det virker meget forvirrende at tælle formlerne i arket. Jeg har en fx. 3 HVIS formler i en osv.
Det er også meget svært at se hvilken del af arket, excel vælger ikke at beregne. Hvordan kan du sikre dig at alt er beregnet/virker i dit?
Nu var det et tilfældigt dobbelt-tjek, jeg fik lavet, og det er ikke optimalt for mig at tjekke op hver gang der loades nyt data ind. Udover at det mængden af data, der loades, der afgør antal formler der beregnes i arket. Og her varierer mængden en del gang til gang.

Hej Hans
Ja, der er sat prik ved auto. Den har beregnet det før i tidligere versioner. Jeg har desværre ikke mulighed for at oploade det da alt data i arket er konfidentielt og det vil tage rigtig meget tid at annonymisere det.
På et eller andet tidspunkt er den stoppet med at beregne en del af formlerne i nogle dele af arket. Det kan fx være at den i række 33 beregner alt rigtigt ved at hente tallene fra det andet sted og række 34  beregner den intet og så står der bare "0" og det passer ikke da data er det samme og i de tidligere versioner har der stået et tal forskelligt fra 0.

Jeg ved godt at det er lidt svært at hjælpe uden at se det men håber alligevel at du har et forslag.

Ja det er svært at hjælpe uden at få noget at se. Enkelte skud:

Prøv eventuelt gemme filen som type xlsb.

I alle ark - prøv at trykke på F5, Special og sæt prik ved Last cell (oversæt selv til dansk). Er last cell meget længere nede og meget længere til højre end nødvendigt. Hvis JA, slet overflødige rækker og kolonner. Gem filen når du har slettet. Ændrede det noget på filstørrelsen?

Hvis dine SUM.HVIS-formler refererer til andre ark end der hvor formlen findes, så tilstræb at ændre din struktur,så der kun refereres til samme ark. Hvis du skal have resultaterne samlet på ét ark kan du herefter hente resultatet af SUM.HVIS-formlerne ved simple referencer.

I din indledende post taler du både om SUM.HVIS og HVIS-formler. Hvor mange i alt (af hver) har du mere præcist. Hvordan ser formlerne ud. Har du kun de to typer formler?

Hans

Filstørrelsen er blevet en smule mindre hvis jeg sletter de ekstra celler med formler. Hvilken betydning har det for formatet hvis jeg gemmer den som xlsb?

En hurtig optælling giver:

Mindst 100.000 SUM.HVIS formler
Mindst 200.000 HVIS formler
Mindst 20.000 LOPSLAG formler
Mindst 1.000 VOPSLAG formler

I det fleste celler er der blevet anvendt mere end en SUM.HVIS formel.
Og så har jeg et par enkelte andre formler.

Jeg var ikke klar over, at der var så mange og kan nok godt se at det er meget tungt men havde dog regnet med at excel 2010 kunne klare vægten.

Vil det også være hensigtmæssigt at minimere tabelmatrix'en i alle LOPSLAG'ene, så de istedet for at referere til hele kolonnen (fx. "D:D") referede til det aktuelle ("D200). Eller vil det have en mindre betydning?

Vedrørende xlsb. Du kan selv google (eksempelvis den her):
http://www.mrexcel.com/forum/excel-questions/659235-excel-2007-xlsb-vs-xlsx.html

Mindst 321.000 formler!
Er du sikker på du anvender det rigtige arbejdsvæktøj?

Er du sikker på du har struktureret din model hensigtsmæssigt. Hvad er det lige der gør det absolut nødvendigt at have 100.000 SUM.HVIS-formler og 20.000 LOPSLAG-formler? Du har jo sikkert dine grunde, men jeg er ikke overbevist om, at det ikke kan gøres anderledes og bedre. Mere konkret kan jeg ikke være uden at se opbygningen.

"Full-column references" som D:D er måske en bekvem skriveform, men det er en dårlig vane. Det er ikke lige problematisk for alle funktioner,men generelt er det langt bedre at begrænse referenceområdet til det der er nødvendigt.

L(V)OPSLAG-formler er beregningstunge, især hvis du bruger FALSK eller 0 som sidste argumnet.

Hans

Du kan bruge denne til at optælle antal formler i et ark

Sub CountFormulas()
    MsgBox ActiveCell.SpecialCells(xlCellTypeFormulas, 23).Count
End Sub

Mange tak for det. Det startede småt men blev hurtigt udviklet til noget større dertil alle formelerne. Jeg har valgt at slette en del formeler og lave en ny fil for dernæst at samle det til sidst.

Jeg er ikke med på hvordan jeg skal tælle antal formlerne? Hvor skal din formel indsættes?

Det er ikke en formel. Det er en stump VBA-kode som skal indsættes i et modul. Tryk på Alt+F11, Insert, Module. Her indsættes de tre linjer kode (Sub CountFormulas). Gå tilbage til Excel ved igen at trykke Alt+F11. Vælg det ark, hvor du vil tælle formlerne. Kør proceduren Sub CountFormulas.

Hans