av Kardi Teknomo

Dela det här: I den här handledningen kommer du att lära dig steg för steg hur du använder MS Excel iteration med ett fungerande exempel för att göra en enkel räknare. Excel iteration är ett bra verktyg för att utföra många interaktiva enkla program i MS Excel utan VBA-makro. Se hur det fungerar i den här handledningen.

Varför använda iteration istället för VBA-makro?
Hur använder man iteration i MS Excel?
Arbetsexempel på iteration i MS Excel
Klicka här för att ladda ner kalkylbladet till den här handledningen .

Varför använda iteration istället för VBA-makro?

Vissa MS Excel-användare irriteras av varningen av det befintliga makroet som detta:

De kan inaktivera makrot men programmet fungerar inte. De är aldrig säkra på om arbetsbladet innehåller virus eller inte. Detta är ett ganska stort dilemma för människor som inte gillar att ta risker.

Finns det något sätt att göra ett program i Excel med iteration (liknande While Loop eller For Next) utan makro?

Ja, MS Excel iteration kan göra ett bra jobb av For-Next Loop för dig utan programmering alls (faktiskt kan du behöva programmering i kalkylbladet, men utan makro). Med Microsoft Excel kan du iterera utan VBA-makro. Dina användare behöver alltså inte oroa sig för den irriterande varningen om virus. Denna MS Excel-iteration är ett av de bästa verktygen om du vet hur du ska använda cirkulär referens till din fördel

Hur använder man iteration i MS Excel?

Den viktigaste idén om hur man använder MS Excel iteration är att göra en cirkulär länk i kalkylbladet för att ackumulera värdet och markera alternativet Iteration i menyn Verktyg-Optioner-Beräkningsfliken Iteration kryssrutan.

Microsoft excel stoppar beräkningen efter Maximal iteration (standard är 1000) eller efter att alla värden i formlerna ändras med mindre än Maximal förändring (standard är 0,0001) mellan iterationerna. Slingan avgränsas automatiskt antingen av maximala iterationer ELLER av maximal förändring mellan två på varandra följande iterationer. För att köra iterationen igen måste du trycka på F9.

Om du ställer in beräkningsalternativet som Automatisk , kommer alla beräkningar att göras som vanligt. Om du använder Beräkningsalternativet som Manuell måste du trycka på F9 varje steg för att ändra beräkningen.

Arbetsexempel på MS Excel iteration

Som exempel på MS iteration ska vi bygga ett enkelt räknarprogram. Här är proceduren steg för steg

1. Skriv 0 i B3 och 100 i B4. Skriv Start i A3, End i A4 och Counter i A5.

2. I B5 skriver du =IF(B5<B4, B5+1, B4). Det betyder att om räknaren är lägre än 100, ignorera varningen för cirkulär referens genom att klicka på OK för meddelandet nedan.

3. Klicka på menyn Verktyg > Alternativ > Fliken Beräkning. Ställ in beräkningsalternativet på Manuell , markera kryssrutan Iteration och ställ in Maximal iteration på 1 (se figuren nedan)

4. Den enkla räknaren börjar med 1. Tryck på funktionstangenten F9 för att öka räknaren en i taget tills 100

Iterationen fungerar mycket bra och räknaren kan göra jobbet manuellt. Varje gång användaren behöver öka räknaren måste han eller hon trycka på F9. Att trycka på F9 100 gånger manuellt är dock inte ett bra jobb för våra användare. Kan vi automatisera iterationen? Ja, du kan automatisera iterationen genom att ställa in Beräkningsalternativet till Automatisk och maximal iteration till 100 eller 1000 .

Denna räknare har dock fortfarande ett problem. Om du vill ändra iterationens startvärde måste du skriva in formeln igen varje gång du vill beräkna. Annars kommer den att använda det beräknade värdet som utgångsvärde. Ett visst startvärde kan ge ett instabilt resultat och #NUM som fel i beräkningen. Det är ganska irriterande att skriva (eller klistra in) formeln manuellt igen för att rätta till misstaget.

Hur startar vi om räknaren tillbaka till noll och undviker dessa problem med startvärdet?

1. Fortsätt vår räknare ovan, Stryk till Start i D3. Sätt särskild färg på teckensnittet och cellen för att särskilja den som en kontrollknapp

2. Skriv ytterligare en If-Then-regel i B5 som blir =IF(D3=””,B3, IF(B5<B4,B5+1,B4) )

3. Klicka på menyn Verktyg > Alternativ > Fliken Beräkning. Ställ in beräkningsalternativet på Automatisk, markera kryssrutan Iteration och ställ in Maximal iteration på 1000 (se figuren nedan)

4. Varje gång användaren raderar innehållet i kontrollcell D3 återställs räknaren till noll. Om användaren skriver en bokstav eller ett tal i kontrollcell D3, körs räknaren automatiskt tills den når det maximala värdet 100. (Du kan ändra värdet på B4 till 500 och se vad som händer)

Nu har du färdigställt den enkla räknaren med kontroll för att nollställa räknaren.

Särskild anmärkning för vetenskapliga förfaranden : Om du inte är säker på att iterationen konvergerar kan resultaten av vissa iterationer vara missvisande. Det du får som resultat är värdet efter maximal iteration. En annan svaghet med iteration: Du kan inte grafera värdet av iterationsprocessen (använd vanliga kalkylblad för att göra detta). Om det till exempel finns en cykel kan du inte tydligt se om iterationen är konvergent.

Kalkylbladsexemplet kan laddas ner här

Se även:
Newton Raphson Tutorial , MS Excel Tutorial index , Kardi Teknomo’s Tutorial
Bedöm den här handledningen och skicka feedback