Werk met cyclische referenties in Excel

Er wordt aangenomen dat cyclische referenties in Excel een foutieve uitdrukking zijn. Inderdaad, vaak is dit precies het geval, maar nog steeds niet altijd. Soms zijn ze behoorlijk bewust van toepassing. Laten we erachter komen welke cyclische koppelingen kunnen maken hoe ze kunnen vinden hoe je reeds in het document kunt vinden hoe je met ze kunt werken of hoe je het kunt verwijderen.

Gebruik van cyclische referenties

Lees eerst wat een cyclische link is. In essentie, deze uitdrukking, die door de formules in andere cellen, naar zichzelf verwijst. Het kan ook een link zijn in het bladelement waartoe het zelf verwijst.

Opgemerkt moet worden dat de moderne versies van Excel standaard het proces van het uitvoeren van een cyclische bewerking automatisch blokkeren. Dit komt door het feit dat dergelijke uitdrukkingen in de overweldigende meerderheid onjuist zijn, en de looping produceert een constant proces van herberekening en berekenen, wat een extra belasting op het systeem creëert.

Een cyclische link maken

Laten we nu eens kijken hoe je de eenvoudigste cyclische expressie kunt maken. Dit is een koppeling in dezelfde cel waarop het verwijst.

1. We markeren het element van het A1-vel en schrijven de volgende uitdrukking erin:

   = A1.

   Klik vervolgens op de knop Enter op het toetsenbord.



2. Daarna verschijnt een dialoogvenster met een cyclisch expressie-waarschuwing. Klik erin op de knop "OK".



3. We hebben dus een cyclische werking op een vel ontvangen waarin de cel naar zichzelf verwijst.

Een kleine complicerende taak en creëer een cyclische uitdrukking uit verschillende cellen.

1. Schrijf in een element van het vel een nummer. Laat het een cel A1 zijn en het nummer 5.



2. In een andere cel (B1) schrijf de uitdrukking:

   = C1.



3. In het volgende element (C1) nemen we een dergelijke formule op:

   = A1.



4. Daarna keren we terug naar de cel A1, waarin het nummer is ingesteld 5. Raadpleeg het naar het element B1:

   = B1.

   Klik op de knop Enter.



5. Dus de cyclus gesloten, en ontvingen we een klassieke cyclische link. Nadat het waarschuwingsvenster is gesloten, zien we dat het programma de cyclische band met blauwe pijlen op een vel markeerde, die Trace-pijlen wordt genoemd.

We draaien nu om een ​​cyclische uitdrukking op het voorbeeld van de tabel te creëren. We hebben een tafel implementatietabel. Het bestaat uit vier kolommen, die de naam van de goederen aangeven, het aantal verkochte producten, de prijs en het bedrag van de inkomsten uit de verkoop van het gehele volume. De tabel in de laatste kolom heeft al formules. Ze berekenen de omzet door het bedrag van de prijs te vermenigvuldigen.

1. Om de formule in de eerste regel los te maken, markeren we het bladelement met het nummer van het eerste product (B2). In plaats van statische waarde (6), voert u de formule daar in, die de hoeveelheid goederen zal overwegen door het totale bedrag (D2) voor de prijs (C2) te verdelen:

   = D2 / C2

   Klik op de knop Enter.



2. We hebben de eerste cyclische link uitgeschakeld, de relatie waarin bekend is met de pijl Trace. Maar zoals we zien, is het resultaat onjuist en gelijk aan nul, zoals al eerder gezegd is, blokkeert Excel de uitvoering van cyclische operaties.



3. Kopieer de uitdrukking in alle andere cellen van de kolom met het aantal producten. Om dit te doen, stelt u de cursor in op de rechterbenedenhoek van dat element dat al de formule bevat. De cursor wordt geconverteerd naar een kruis, dat wordt geroepen om een ​​vulmarkering te bellen. Verwijder de linkermuisknop en trek dit kruis naar het einde van de tabel.



4. Zoals je kunt zien, werd de uitdrukking gekopieerd naar alle elementen van de kolom. Maar slechts één relatie is gemarkeerd met de pijltrace. Let op het voor de toekomst.

Zoek naar cyclische links

Zoals we al hoger hebben gezien, niet in alle gevallen markeert het programma de relatie van de cyclische referentie met objecten, zelfs als deze op het vel staat. Gezien het feit dat in de overweldigende meerderheidscyclische operaties schadelijk zijn, moeten ze worden verwijderd. Maar hiervoor moeten ze het eerst vinden. Hoe dit te doen als de uitdrukkingen niet worden geëtiketteerd met de pijllijn? Laten we met deze taak afhandelen.

1. Dus, als u een Excel-bestand start, hebt u een informatievenster dat het een cyclische link bevat, het is wenselijk om het te vinden. Ga hiervoor naar het tabblad "Formules". Klik op het lint op de driehoek, die zich rechts van de knop "Inchecken voor fouten" bevindt, in het gereedschapsblok "Afhankelijkheidsafhankelijkheid" bevindt. Er wordt een menu geopend waarin de cursor moet worden gehost aan "cyclische links". Daarna opent het volgende menu een lijst met adressen van bladelementen waarin het programma cyclische uitdrukkingen heeft ontdekt.



2. Wanneer u op een specifiek adres klikt, wordt de bijbehorende cel op het vel geselecteerd.

Er is een andere manier om erachter te komen waar de cyclische band zich bevindt. De boodschap over dit probleem en het adres van het element dat een vergelijkbare uitdrukking bevat, bevindt zich aan de linkerkant van de statusstring, die onderaan het Excel-venster staat. WAAR, in tegenstelling tot de vorige versie, worden de adressen van niet alle elementen die cyclische referenties bevatten, op de statusbalk, als er veel van hen zijn, maar slechts één van hen, die vóór anderen verschenen.

Als u bovendien in een boek bevindt, dat een cyclische uitdrukking bevat, niet op het vel waar het zich bevindt, en aan de andere kant, wordt in dit geval alleen een bericht over de aanwezigheid van een fout weergegeven in de statusbalk.

LES: Hoe cyclische koppelingen te vinden naar Excel

Correctie van cyclische referenties

Zoals hierboven vermeld, in de overweldigende meerderheid van de gevallen, zijn cyclische bewerkingen slecht, waaruit het gemakkelijk moet worden geëlimineerd. Daarom is het natuurlijk dat nadat de cyclische verbinding wordt gedetecteerd, het nodig is om deze te corrigeren om de formule op de normale vorm te brengen.

Om de cyclische afhankelijkheid te corrigeren, moet u de volledige interconnectie van de cellen volgen. Zelfs als de cheque een specifieke cel heeft aangegeven, is de fout mogelijk niet in het zelf bedekt, maar in een ander element van de herketen van afhankelijkheid.

1. In ons geval, ondanks het feit dat het programma correct wees op een van de cycluscellen (D6), ligt de daadwerkelijke fout in een andere cel. Selecteer het D6-element om erachter te komen welke cellen het de waarde ophaalt. We kijken naar de uitdrukking in de Formule-string. Zoals we zien, wordt de waarde in dit bladelement gevormd door de inhoud van de B6- en C6-cellen te vermenigvuldigen.



2. Ga naar de C6-cel. We markeren het en kijken naar de formule-string. Zoals we zien, is het de gebruikelijke statische waarde (1000), wat geen product is die de formule berekent. Daarom is het veilig om te zeggen dat het opgegeven element geen fouten bevat die cyclische bewerkingen veroorzaken.



3. Ga naar de volgende cel (B6). Na selectie in de rij van de formule, zien we dat het een berekende expressie (= D6 / C6) bevat, die de gegevens van andere tabelelementen in het bijzonder, van de D6-cel trekt. De D6-cel verwijst dus naar de gegevens van het element B6 en vice versa, waardoor de lusheid wordt veroorzaakt.

   

   Hier berekenden we de relatie die we vrij snel berekenden, maar in werkelijkheid zijn er gevallen waarin veel cellen betrokken zijn bij het berekeningsproces, en niet drie elementen zoals we hebben. Dan kan de zoektocht vrij lang duren, omdat het elk cyclisch element moet bestuderen.

4. Nu moeten we begrijpen welke cel (B6 of D6) een fout bevat. Hoewel het formeel geen fout is, maar eenvoudigweg buitensporig gebruik van referenties die leidt tot looping. Tijdens het oplossen van het oplossen van welke cel moet worden bewerkt, moet u logica toepassen. Er is geen duidelijk algoritme voor actie. In elk geval zal deze logica zijn eigen zijn.

   Als onze tabel bijvoorbeeld het totale bedrag gedeeld, moet worden berekend door het aantal daadwerkelijk verkochte goederen aan de prijs te vermenigvuldigen, dan kunnen we zeggen dat de koppeling tellen van het bedrag van het totale verkoopbedrag duidelijk overbodig is. Daarom verwijderen we het en vervangen ze het met statisch belang.



5. Een dergelijke bewerking wordt uitgevoerd over alle andere cyclische uitdrukkingen als ze op het vel staan. Nadat absoluut alle cyclische koppelingen uit het boek zijn verwijderd, moet de boodschap over de aanwezigheid van dit probleem verdwijnen uit de statusstring.

   Bovendien zijn cyclische uitdrukkingen volledig verwijderd, u kunt achterhalen met behulp van de foutcontroletool. Ga naar het tabblad "Formules" en klik op de driehoek die al bekend is met ons rechts van de knop "Foutencontrole" in de instrumentengroep "Afhankelijk van formules" . Als het item "Cyclic Links" in het menu Running niet actief is, betekent dit dat we al dergelijke objecten uit het document hebben verwijderd. In het tegenovergestelde geval moet u de verwijderingsprocedure toepassen op de vermelde items, op dezelfde manier hetzelfde.

Toestemming van de uitvoering van cyclische operaties

In het vorige deel van de les vertelden we, vooral hoe te omgaan met cyclische referenties, of hoe ze ze te vinden. Maar eerder was het gesprek ook om dat in sommige gevallen, integendeel, nuttig en bewust door de gebruiker kunnen worden gebruikt. Bijvoorbeeld, vrij vaak wordt deze methode gebruikt voor iteratieve berekeningen bij het bouwen van economische modellen. Maar het probleem is dat, ongeacht of u bewust of onbewust bent, u een cyclische expressie gebruikt, bladerde Excel nog steeds de werking daarmee, om niet te leiden tot overmatige overbelasting van het systeem. In dit geval wordt de kwestie van gedwongen invaliderende een dergelijke blokkering relevant. Laten we eens kijken hoe het te doen.

1. Allereerst gaan we naar het tabblad "Bestand" van de Excel-toepassing.



2. Klik vervolgens op het item "Parameters" aan de linkerkant van het venster dat is geopend.



3. Het exile-parametervenster begint met actief. We moeten naar het tabblad "Formules" gaan.



4. Het zit in het venster dat wordt geopend, mag de uitvoering van cyclische bewerkingen uitvoeren. Ga naar het rechterblok van dit venster, waar de Excel-instellingen rechtstreeks zijn. We zullen werken met het instellingenblok "Computing-parameters", dat zich bovenaan bevindt.

   Om het gebruik van cyclische uitdrukkingen toe te staan, moet u een vinkje installeren over de parameter "ITERATIESE BEREKENINGEN". Bovendien kunt u in hetzelfde blok het limietnummer van iteraties en de relatieve fout configureren. Standaard zijn hun waarden respectievelijk 100 en 0,001. In de meeste gevallen hoeven deze parameters niet te worden gewijzigd, hoewel indien nodig, het mogelijk is om wijzigingen in de opgegeven velden aan te brengen. Maar hier is het noodzakelijk om te beschouwen dat te veel iteraties kunnen leiden tot een ernstige belasting op het programma en het systeem als geheel, vooral als u werkt met een bestand waarin veel cyclische uitdrukkingen worden geplaatst.

   We installeren dus een vinkje over de parameter "ITERATIESE BERKKINGEN" en vervolgens op de nieuwe instellingen in werking, klik op de knop "OK", aan de onderkant van het Excel-parametersvenster.



5. Daarna gaan we automatisch naar het blad van het huidige boek. Zoals we zien, in de cellen waarin cyclische formules zich bevinden, worden nu de waarden correct berekend. Het programma blokkeert de berekeningen daarin niet.

Maar toch is het vermeldenswaard dat de opname van cyclische bewerkingen niet mag worden misbruikt. Pas deze functie toe, volgt alleen wanneer de gebruiker volledig vertrouwen is in de behoefte. Onredelijke opname van cyclische operaties kan niet alleen leiden tot buitensporige belasting op het systeem en de berekeningen vertragen bij het werken met een document, maar de gebruiker kan onbedoeld een foutieve cyclische expressie maken, die het standaard zou worden geblokkeerd door het programma.

Zoals we zien, zijn in de overweldigende meerderheid van de gevallen cyclische referenties een fenomeen waarmee je moet vechten. Hiervoor moet u allereerst de cyclische relatie zelf detecteren en vervolgens de cel berekenen wanneer de fout is ingesloten en elimineert het uiteindelijk door de juiste aanpassingen te maken. Maar in sommige gevallen kunnen cyclische bewerkingen nuttig zijn bij het berekenen en gebruikt door de gebruiker bewust. Maar zelfs dan is het de moeite waard om hun gebruik met voorzichtigheid te gebruiken, de Excel correct te configureren en de maatregel te kennen in de toevoeging van dergelijke referenties, die, bij gebruik in massa-hoeveelheden, de werking van het systeem kunnen vertragen.