« Ett litet april-pyssel | Main | Sammanfattning av Bloggareträff uti Malmö, söndagen 4 maj 2008 »

april 27, 2008

Mitt OpenOffice Calc-/Excel-skov

Som tidigare antytts har jag tidigare kollat in OpenOffice Calc och Excel. IRL har detta till mina vänner beskrivits som mitt OpenOffice/Excel-skov och det har i vissa fall även utlovats en rapport om detta.

Här är en reseskildring över förloppet, kompletterad med några exempel som gjordes då. Samtliga exempel utnyttjar Solver/Problemlösaren eftersom det är den finessen som jag uppskattade mest.


Excel/OpenOffice Calc


Av lite olika anledningar började jag ordentligt kolla in Excel/OpenOffice Calc under hösten 2007 och blev riktigt överraskad hur kompetent det är. Jag hade kollat in det för många år sedan men blev då inte speciellt road, troligen för att själva spreadsheet-metaforen inte passade mig då.

Dataanalys har istället gjorts i främst R eller rena standardprogramspråk (C, C++, Java, Perl, Python, Ruby etc) med eller utan numeriska tillägg, eller med matematiska system såsom Maple/Matlab (eller dess kloner såsom MuPAD, Octave) etc etc.

I och för sig har (eller snarare hade eftersom Windowsmaskinen är trött numera) jag tillgång till Excel 2003, men eftersom jag främst är en Linux-människa kollade jag mest in OpenOffice Calc som är valdigt kompatibel med Excel 2003.

Som av en slump sträcklästes först Bill Jelens praktverk Special Edition Using Microsoft Office Excel 2007 (>1000 sidor). Trots att boken är för Excel 2007 är det väldigt mycket som är applicerbart för Calc, framförallt formler och sätt att tänka. (Jag läste dock kursivt de första 300-400 sidorna som pratar om det nya GUI-t för Excel 2007).

Sedan införskaffades Microsoft Office Excel 2007 - Data Analysis and Business Modeling skriven av Wayne Winston. Denna bok innehåller många fler fullständiga och kommenterade exempel (än Jelens bok). De mest intressanta var avsnitten kring planering, simulering etc, framförallt modellerna med linjära-/heltals-modeller.

En asidekommentar i ett av dessa avsnitt hänvisade till Winstons egen bok Operations Research - Applications and Algorithms, och den införskaffades därför (ibland är bokbeställningsbenägenhetströskeln väldigt låg). Det är en alldeles förträfflig introduktion inom området Operations Research (OR, operationsanalys på svenska). För böcker som därefter lästes inom OR, se litteraturlistan i Applikationer med constraint programming, lite om operations research samt nya MiniZinc-modeller.

Ungefär i samband med testningen av OR-exemplen började jag fundera på hur man implementerade problem såsom SEND + MORE = MONEY och liknande småpyssel i Excel/Calc. En del andra problem implementerades också, vara några presenteras nedan.

Det gemensamma med dessa problem är att de använder Solver/Problemlösaren, en av de mest avancerade funktionerna i systemen. Se vidare nedan under Solver/Problemlösaren.


Ett tag var allting smör och solsken (eller vad det nu heter), men efter ett tag blev begränsningarna i systemet mer och mer tydliga: Excels Solver kan inte hantera fler an 200 beslutsvariabler; den Solver som användes i OpenOffice Calc kan vara långsam och hanterar inte icke-linjära problem (ibland kan man modellera runt denna begränsning men det är ofta rätt trixigt). Det var då jag gick över till att modellera i andra modellspråk, något som beskrivs i Applikationer med constraint programming, lite om operations research samt nya MiniZinc-modeller.


Solver/Problemlösaren


Som ovan nämndes är OpenOffice Calc/Excel väldigt kompetenta och kan göra en massa saker. Det enda jag tänker att beröra här är en av de mest kraftfulla funktionerna: Solver (OpenOffice Calc, eller engelskspråkiga Excel)/Problemlösaren (svenskspråkig Excel).

Kortfattat kan säga att Solver möjliggör lösningar av vissa typ av problem genom att optimera vissa värden på ett smart sätt. Man kan säga att Solver testar en massa olika lösningar och väljer den (eller en av flera) som är bäst givet det kriterium man angivit.

OpenOffice har ingen inbyggd solver, men det ska komma en sådan i version 3. Tills vidare kan rekommenderas det Solver-paket (.oxt-paket) som Kohei Yoshida skapat: Calc Optimization Solver. Tyvärr hanterar det endast linjära problem.

Istället för att förklara detaljerna hur man gör för att använda Solver, länkas här till några bra introduktioner. Jag rekommenderar även att någon av ovan nämnda Excel-böcker studeras (där står mycket annat matnyttigt om Excel). Samtliga beskriver Excels Solver, men det funkar i princip på samma sätt i OpenOffice Calc.

* Teaching Linear Programming using Microsoft Excel Solver. Introduktion i linjär programmering, med instruktion hur man använder Solver.

* Microsofts egen dokumentation: Introduction to optimization with the Excel Solver tool

* Learn to Use the Excel Solver to Make Money-Saving Decisions: Build Models to Allocate Scarce Resources

* (Bok) Paul Cornell: Beginning Excel What-if Data Analysis Tools - Getting Stated with Goal Seek, Data Tables, Scenarios, and Solver.

Några exempel med Solver/Problemlösaren

Här är några exempel på hur man använder Solver/Problemlösaren. Samtliga är skrivna i OpenOffice Calc version 2.4, och de borde fungera även i Excel 2003 eller senare. Tyvärr är min Excel 2003 inte tillgänglig för tillfället så de kan inte testas där, men vid modelleringen i höstas funkade samtliga exempel i båda systemen.

I dokumenten beskrivs vilka celler/områden som är relevanta för Solver.

För jämförelsens skull länkas även till motsvarande MiniZinc-modell. (Om någon skulle vara intresserad av motsvarande modeller i AMPL är det bara att hojta.)

Not: Inga macros har använts.

Exempel Calc/Excel MiniZinc
Schemaläggning (från Winstons OR-bok) Scheduling_Winston.xls post_office_problem.mzn, mer generell modell: post_office_problem2.mzn
Produktplanering (chess set) chess_sets.xls chessset.mzn
Diet-problemet diet.xls diet1.mzn
Knapsack knapsack.xls knapsack2.mzn
Set covering (Winstons brandstationsexempel) set_covering_winston.xls set_covering.mzn
SEND + MORE = MONEY send_more_money.xls send_more_money.mzn
Växling av pengar (knapsack-variant) money_changing.xls money_change.mzn
Seseman-problemet seseman.xls seseman.mzn, mer generell modell: seseman2.mzn

Slutord

Jag har även kollat in Gnumeric som är en annan Excel-kompatibel öppenkod-produkt. Där finns en solver men den blev jag aldrig vän med.

Posted by hakank at april 27, 2008 07:45 EM Posted to Constraint Programming | OpenOffice Calc/Excel | Operations research

Comments

Jag har lärt min lilla älskling Thebe Excels problemslösare. Ni kan eventuellt diskutera detta på kommande blogglunch.

Apropos vad som står precis här ovanför ("Comments: (you may use HTML tags for style)") så kan jag inte använda HTML taggar för stil; det blir helt stillöst när jag försöker. Vad detta beror på är möjligen en fråga om smak?

Posted by: Henrik Sundström at april 29, 2008 07:21 EM

Henrik: Det ska bli skoj att torrprogrammera Excel/OpenOffice Calc.

Vad gäller stilar i kommentarerna så ser jag i felkonsolen att det är något stök med stylesheeten (som är standard från Movable Type-fabriken). Jag kollar lite...

Posted by: hakank [TypeKey Profile Page] at april 29, 2008 07:55 EM

Har nu kollat lite om HTML-taggarna.

Följande HTML-taggar funkar i alla fall

bold

italic

emfas

Däremot strippar systemet automatiskt bort sådant style, font etc, och behåller texten. E.g.
<font color="red">xxx<font>
till endast strängen
xxx

och

<p style='color: red;'>style</p> strippas till style

Texten "HTML tags for style" är förledande och jag ändrar den.

Tack för påpekandet.

Posted by: hakank [TypeKey Profile Page] at april 29, 2008 08:27 EM