Vanligtvis förknippas cirkelreferenser med att vi har gjort fel när vi bygger upp formler och XL påtalar det på sitt vänliga sätt.

Här demonstreras hur vi kan använda oss av cirkelreferenser i positiv bemärkelse.

Principen med cirkelreferenser
För att få till positiva cirkelreferenser måste vi ha ett väldefinierat problem där lösningen bygger på att låta XL göra arbetet. Vi måste också skapa förut- sättningarna för XL att göra beräkningsarbetet.

Vi överlåter till XL att utföra iterativ beräkning till dess att den har fått fram en lösning. Följande anpassning måste göras:

• Välj kommandot Verktyg | Alternativ
• Aktivera fliken "Beräkning" i den dialogruta som visas.
• Bocka för "Iterationer" och om så önskas justera värdena för "Max antal iterationer" och "Max förändring". Antalet iterationer bör helst vara > 100.

Som en bekräftelse på anpassningen visas "Beräkna" längst ned på status- raden på skärmen.

Följande exempel (se bild nedan) är en s k klassiker för cirkelreferenser och belyser hur vi kan dra nytta av XL:s inbyggda verktyg.

Vi har en intäkt med tillhörande kostnad. Därutöver finns det en kommission på 10 %, vilken beräknas på nettobeloppet. För att erhålla nettobeloppet reduceras intäkten med kostnaderna och kommissionen.

För att erhålla nettobeloppet måste vi veta beloppet för kommissionen är - För att veta storleken på kommissionen måste vi veta nettobeloppet. Här föreligger ett konkret exempel på cirkelreferens!

Utöver anpassningen enligt ovan används följande formler:

• Kommission: =10%*B4
   
• Netto: =B1-SUMMA(B2:B3)

Den iterativa beräkningen skötts helt och hållet av XL.

Sätta intäktsbegränsningar
Antag att vi i en verksamhet har en situation där intäktssidan för respektive enhet är förenad med viss kommission.

Bilden nedan visar exemplet i sin helhet:

Så här ser villkoren ut:

• Om Intäkter (exklusive kommission) * Kommission (%) är större än Summa Intäkter * Kommission (%) så ska kommissionen beräknas utifrån Summa Intäkter * Kommission (%) annars sker beräkning enligt den förstnämnda beräkningen.
   
• Om Ersättningsnivån (Avtalsförsäljning * Ersättning (%)) är större än Summa Intäkter * Kommission (%) så ska kommissionen beräknas utifrån Summa Intäkter * Kommission (%) annars sker beräkningen utifrån Intäkter (exklusive kommission) * Kommission (%).

Utöver anpassningen enligt ovan så ligger följande formel till grund för lösningen:

• =OM(OCH(SUMMA(B2:B5)*E1>(B6*E1);(E2*E3)>
  (B6*E1));B6*E1;SUMMA(B2:B5)*E1)

Slumpa fram unika tal
Ibland kan det vara önskvärt att slumpa fram unika tal ur en förutbestämd talserie.

Exemplet (se bild nedan) ska slumpa fram 5 unika tal ur en serie mellan 1 - 10.

För att få denna funktion att fungera behöver vi skapa en kontrollista i kolumn B.

Formeln i cell B2, som kopieras nedåt i kolumnen, är:

• =ANTAL.OM($A$2:$A$6;A2)

I kolumn A används följande formel:

• =OM(SUMMA($B$2:$B$6)<>5;HELTAL(SLUMP()*10+1);A2)

För att XL ska slumpa fram nya unika tal behöver vi aktivera en cell i kontrollistan. Det sker enklast genom att trycka på F2-tangenten och därefter på ENTER-tangenten.

Skapa ackumulerade värden i celler
Antag att vi håller på med att bygga upp en lista där vi matar in värden i en eller flera celler och vi vill att XL ska löpande summera de inmatade värdena i en eller flera celler.

Frågan som (kanske) uppstår är:
Går det att lösa utan att ta hjälp av programmering?

XL-Dennis hävdar bestämt att så är möjligt! Här demonstreras en teknik som löser det på ett smidigt sätt.

Utgångspunkten för exemplet visas i nedanstående uppställning:

I B-kolumnen matas nya värden in och i C-kolumnen sker en summering (ackumulering) av de inmatade värdena.

När inmatning av nya värden i respektive rad sker uppdateras motsvarande rad i C-kolumnen automatiskt, vilket nedanstående tabell försöker belysa:

Om vi nu vill "nollställa" C-kolumnen kan det ske genom att vi anger (här i exemplet) ett s i cellen A2 (se nedanstående bild).

Den tekniska lösningen består av följande delar:

• Aktivera iterationer men ändra inte på grundinställningarna
   
• I C-kolumnen används följande formel:
  =OM($A$2="s";0;C2+OM(B2<>D2;B2;0))
  
  (B2 kommer vid inmatning av värde att alltid ha större värde än cellen D2)
   
• I D-kolumnen används följande formel - (Kolumnen i sin helhet kan med fördel döljas i den färdiga lösningen.):
  =OM($A$2="s";0;D2*0+B2)
   
  XL räknar cirkelreferenser från vänster till höger. Formeln har en cirkelreferens (D2*0) vilket tvingar XL att även räkna denna kolumn. Detta är viktigt att ha i åtanke när vi skapar förutsättningarna. I exemplet räknar XL alltid C-kolumnen före D-kolumnen.
   
• Om vi inte vill "nollställa" kan vi istället använda oss av följande formler:
  i C-kolumnen: =C2+OM(B2<>D2;B2;0)
  i D-kolumnen: =D2*0+B2

Upp