LETARAD och "slut på minnet" i Excel

[Stefan Elf]

Hej!

 

Sitter här och bråkar med två Excel arbetsböcker. Den ena boken innehåller en prislista från en grossist på ett enda blad. Det finns ca 29000 rader i bruk och informationen tar upp kolumnerna A till K. Första kolumnen innehåller artikelnumret.

 

Den andra arbetsboken innehåller också bara ett blad. Även det är en prislista, en s.k. aktiv prislista, som innehåller ca 670 av grossistens 29000 artiklar.

 

Två kolumner i aktiva prislistan hämtar sin information från grossistlistan med hjälp av funktionen LETARAD. Det finns alltså 2 x 670 st. formler i aktiva prislistan som skall leta genom 29000 rader i grossistlistan för att visa rätt information. I grossistlistan finns inte en endaste formel.

 

Problemet är att Excel säger ”Slut på minnet” varje gång man skall spara den aktiva prislistan. Sedan säger den att externa länkvärden inte kan sparas. Samma felmeddelande kommer även i andra situationer, t.ex. då en rad från aktiva prislista skall kopieras till en tredje arbetsbok.

 

Kalkylfunktionen =info(”anvminne”) ger:

372 856 bytes för nystartad Excel med tom arbetsbok

575 824 bytes för aktiv prislista

3 589 264 bytes för grossistlista

4 013 332 bytes för aktiv prislista och grossistlista öppnade samtidigt

 

Jag kör Excel 2000 för tillfället men dessa listor borde fungera i Excel 97 också.

 

Vad kan jag göra? Varför får Excel slut på minnet?

 

Hälsningar

 

Stefan Elf

stefan.elf@elftec.fi

[XL-Dennis]

Stefan,

 

XL har en alldeles egen minnehantering, som emellanåt ger oss märkliga felemeddelanden

 

Varken antal formler eller antal poster ska vålla några bekymmer.

 

Hur stora är arbetsböckerna?

Den ena borde vara ca 20 - 25 MB medan den andra rimligtvis är mindre.

 

Det kan hända att arbetsböcker går sönder, dvs blir trasiga. Mycket användande och sparande i kombination med en och annan krasch kan räcka...

 

Kan du ge oss ett exempel på LETAUPP-formeln du använder?

 

Vissa av dessa LETA-UPP funktioner kan vara minneskrävande per definition. Alternativt kan därför en annan formel-lösning frigöra minne.

 

Innan andra åtgärder vidtas rekommenderar jag dock att du lyfter över all data och formler till nya arbetsböcker.

 

 

Mvh

Dennis

http://www.xldennis.com

 

[Stefan Elf]

Hej Dennis!

Tack för snabbt svar.

Det där med att flytta över all data till nya arbetsböcker var bland det första jag gjorde och det är gjort ett par gånger nu utan effekt.

 

Du undrar hur stora arbetsböckerna är. Grossistlistan är 4 803 kB och aktiva prislistan är 2 096 kB, dvs. betydligt mindre än din uppskattning.

 

Här kommer ett exempel på de formler som finns i aktiva prislistan:

 

=LETARAD(A22;'[Grossist.xls]Sheet1'!$B$7:$K$28956;2;FALSKT)

 

 

A22 innehåller alltså artikelnumret, ett unikt nummer, för den produkt som skall hittas i grossistlistan. Priset hämtas från kolumn 2.

 

Mvh

Stefan

 

 

 

 

 

[XL-Dennis]

Stefan,

 

Tack för informationen.

 

LETARAD-funktionen är ingen större" bov så den låter vi i nuläget vara.

 

Då går vi vidare i vårt åtgärdspaket:

 

1) Namnge cellområdet B7:K28956 och ersätt cellområdesreferensen med namnet i alla formler.

 

2) Återställ sista använda cellen med följande procedur, som du temporärt infogar i din stora arbetsbok:

 

Sub Aterstall()

ActiveSheet.UsedRange

End Sub

 

3)Om inte hela tabellområdet enligt din formel är fylld med data - reducera antalet poster.

 

4) Minimera antalet typsnitt (=1) och eventuell formatering.

 

Hm, ska bli intressant att se utfallet av detta.

 

Mvh

Dennis

http://www.xldennis.com

 

[inlägget ändrat 2002-01-14 18:27:47 av XL-Dennis]

[Weyland]

Japp, klart fascinerande fall. Börjar fundera lite i vart excels begränsningar kan ligga? Borde det som diskuteras här lyftas över till access kanske?

 

--

.Weyland

 

2. Everything around us can be represented and understood through numbers.

 

[XL-Dennis]

Weyland,

 

Yes, MS Access är alltid ett alternativ när det gäller större datamängder.

 

XL:s begränsningar får betraktas som ungefärliga...

 

XL har en förmåga att orsaka s k minnesläckage, vilket jag misstänker är fallet här.

 

XL har sitt alldeles speciella sätt att hantera tillgänglig minnesmängd på och t ex vissa formler formligen slukar resurser.

 

XL förmåga att hantera större arbetsböcker har successivt ökat.

XL 95 - ca 16 MB

XL 97 /2000 - ca 80 MB

XL 2002 - ca 160 MB

 

Mvh

Dennis

http://www.xldennis.com

 

[Stefan Elf]

Dennis,

 

Så här gick det med åtgärdspaketet. Jag börjar bakifrån:

4) Det fanns inte mycket att minimera, allt i grossistlistan är skrivet med Arial 10pt.

 

3) Hela området B7:K28956 är fyllt med data

 

2) Jag infogade proceduren, sparade, öppnade, stängde, öppnade igen och tog bort proceduren.

 

1) Jag döpte cellområdet till Data och satte in namnet i alla LETARAD-formler som finns i den aktiva prislistan.

 

Allting är i princip som förut. Ett säkert sätt att få XL att meddela att minnet är slut är t.ex. att försöka infoga en ny rad i aktiva prislistan. Efter att man klickat bort felmeddelandet så har man nog fått sin nya rad i alla fall. Jag har inte heller märkt att någonting verkligen skulle ha blivit osparat fastän XL har klagat på att minnet är slut då man stänger och sparar aktiva prislistan.

 

Så en lite annan sak. XL beräknar om allting så fort minsta förändring sker(infogandet av rad, sortering, urval mm.). Finns det något sätt att få inställningen Manuell beräkning (Verktyg/Alternativ/fliken Beräkning)

att vara "arbetsboksberoende". Jag märkte att minnet inte tog slut lika lätt beträffande de här prislistorna om jag hade manuell beräkning påkopplad, men eftersom XL behandlade alla arbetsböcker lika så förlorade jag funktionalitet i andra böcker som jag måste ha öppna samtidigt som prislistan.

 

Mvh

Stefan

 

 

 

 

 

 

[XL-Dennis]

Stefan,

 

Tack för all information!

 

2) Jag utgår från att du körde proceduren!

 

Manuell beräkning gör viss skillnad men då formlerna inte utför beräkningar utan slår upp värdet erhålls ingen större effekt.

 

"Boven" i dramat är att leta upp området är stort.

 

Två alternativa åtgärdspaket finns att tillgå enligt min uppfattning:

 

VBA-lösning

Skapar en procedur som letar upp och infogar värdena i din mindre arbetsbok.

Då är det bara värdena som sparas och ej några formler.

 

Proceduren kan antingen vara manuell eller automatisk.

 

MS Query

Skapa en MS Query fråga som kan uppdateras.

 

Personligen föredrar jag den förstnämnda förslaget.

 

Att XL ger felmeddelanden indikerar på att stabiliteten inte är god och du kan därför riskera att arbetsboken blir korrupt. Att XL sparar trots felmeddelanden ger i sig ingen trygghet.

 

Mvh

Dennis

http://www.xldennis.com

 

[inlägget ändrat 2002-01-14 23:27:14 av XL-Dennis]

[Stefan Elf]

Dennis,

 

Jahapp, ja.

Då är jag liksom ute på den där berömda tunna isen. Jag känner mig lite borttappad på VBA sidan.

 

Du säger att boven i dramat är det "stora" leta upp området. Man kan ju tycka att ett dataområde som upptar knappt hälften av antalet möjliga rader och bråkdelen av möjliga kolumner inte är speciellt stort. Och det är ju bara ett ark (blad). Man kunde ju vänta sig att XL skulle kunna hantera betydligt större områden utan att hicka till.

Vad betyder det i praktiken att XL 2000 skall kunna hantera arbetsböcker på ca 80 MB? Den här som krånglar nu är ju bara 2 MB. Okej, okej det är XLs arbetsminne som tar slut, men om XL ska klara mycket större filer så borde så borde ju hanteringen av tillgängliga minnesresurser vara därefter också.

Blir min situation bättre om burken får mera minne? Den här prislistan körs på en maskin (PIII)som har 128 MB arbetsminne.

 

Tack för alla förslag och hjälp med problemet.

 

Mvh

Stefan

 

[XL-Dennis]

Stefan,

 

Kör privat e-post med ett exempelmaterial så arbetar vi vidare med VBA och presenterar lösningen här sedan, OK?

 

Mvh

Dennis

http://www.xldennis.com