Hej igen Jokern,
Här kommer nu del 2 av ”Joker-utmaningen” på Excel 2007.
Utmaning
Hitta positionen i en lång serie med värden, där summan av de största värdena - efter sortering från högsta till lägsta - är lika stor som summan av övriga, dvs. jämviktspunkten.
Frågeställningen grundar sig på ett tipssystem där det finns tre möjliga utfall i 13 matcher. Eftersom den matchen man ska analysera inte behöver ingå i systemet blir antalet 3^12 =531’441 möjliga kombinationer, multiplicerat med 12 kolumner = 6’377’292 celler.
Med hjälpkolumner - för att snabba upp beräkningarna - och annat så kommer lösningen kräva att man använder sig av cirka 8 miljoner celler.
Initialt var det mycket svårt att få minnet att räcka till eftersom Excel 2007 (finns bara 32-bitars) kan i teorin använda max 2GB RAM. I praktiken ”krashar” dock Excel redan någonstans runt 1750MB minnesutnyttjande. Då får man antingen en varning om att resurserna är slut, eller så får man ”spinning wheel of death”, eller så kan man spara filen, men den går inte att öppna igen eftersom Excel måste tillfälligt allokera buffertminne och annat när filer öppnas.
”Joker-projektet” började precis där, med minnesproblematiken för en dryg vecka sen. Den första ”fungerande” version jag fick till blev den nedan. Som man kan utläsa av filnamnet tog det 20 min och 13 sek att öppna filen i Excel och minnesåtgången var 1513MB.
Jag övergick till att börja använda Excels binära filformat xlsb. Med xlsb-formatet blev filerna väsentligt mindre - och gick betydligt snabbare att öppna – men minnesåtgången förblev densamma, vilket också var väntat.
Jag analyserade ett antal olika scenario för att förstå hur Excels minnesåtgång påverkas. Min slutsats blev entydig. Det är primärt antalet använda celler samt storleken på formlerna som påverkar filstorleken samt minnesåtgången. Eftersom systemet kräver runt 8 miljoner celler blev jag tvungen att hitta en lösning för att minska storleken på formlerna i varje enskild cell i Excel.
Det var då jag kom på det!
Vi har ju inte tillgång till LAMBDA() i Excel 2007, men vi har något som är nästan lika kraftfullt, nämligen Namnhanteraren!
Namnhanteraren kan inte bara skapa ”genvägar” till celler och cellområden utan den kan även skapa alias för formler.
När jag hade kommit på det ”tricket” så minskade filstorlekarna, inläsningstiderna samt minnesåtgången ordentligt, till följande för xlsx-formatet.
Och för xlsb-formatet.
Jag tyckte fortfarande att filerna var lite väl stora och ”sega” att läsa in så jag bestämde mig för att subotimera lösningen.
Eftersom alla formler finns definierade i Namnhanteraren behöver man inte spara dem i varje enskild cell. Således skapade jag en mall där allting finns skapat, men utan att varje cell har någon formel.
En ”tom” lösning är helt optimal. Den går supersnabbt att öppna och eftersom man ändå varje gång man öppnar arket antagligen vill mata in nya procentsatser, gränsvärden och välja match att analysera blir det mycket smidigare att börja med en tom mall. Med mallen klar så krympte filerna riktigt drastiskt till följande.
Och för xlsb-formatet.
Nu börjar det likna något!
Under en minut för att öppna Exceldokumenten på min gamla Acer med endast 4GB RAM och en Dual Core 1.2GHz från 2009.
Så här ser mallen ut när man läser in den på 30 sekunder med xlsb-formatet.
Konstruera ovan struktur i Excel och använd sen namnhanteraren för att skapa följande.
Nu börjar det här inlägget också bli för långt, så när ovan är klart kan du höra av dig Jokern, så ska vi ta del 3 av ”Joker-utmaningen”. ;-)
Trevlig kväll!