Inhoud
"Zoeken naar een oplossing" is een Excel-invoegtoepassing waarmee het mogelijk is om de beste oplossing voor problemen te kiezen op basis van de opgegeven beperkingen. De functie maakt het mogelijk om medewerkers in te plannen, kosten of investeringen te verdelen. Als u weet hoe deze functie werkt, bespaart u tijd en moeite.
Wat is Zoeken naar oplossingen
In combinatie met diverse andere opties in Excel is er één minder populaire, maar uiterst noodzakelijke functie “Zoeken naar een oplossing”. Ondanks het feit dat het niet gemakkelijk is om het te vinden, helpt het leren kennen en gebruiken ervan bij het oplossen van veel problemen. De optie verwerkt de gegevens en geeft de optimale oplossing van de toegestane. In het artikel wordt beschreven hoe de functie Zoeken naar een oplossing rechtstreeks werkt.
De functie "Zoeken naar een oplossing" inschakelen
Ondanks de effectiviteit staat de betreffende optie niet prominent op de werkbalk of het contextmenu. De meeste gebruikers die in Excel werken, zijn zich niet bewust van de aanwezigheid ervan. Standaard is deze functie uitgeschakeld, voer de volgende acties uit om deze weer te geven:
- Open het "Bestand" door op de juiste naam te klikken.
- Klik op het gedeelte "Instellingen".
- Selecteer vervolgens de subsectie "Add-ons". Alle add-ons van het programma worden hier weergegeven, het opschrift "Management" verschijnt hieronder. Aan de rechterkant is er een pop-upmenu waarin u "Excel-invoegtoepassingen" moet selecteren. Klik vervolgens op "Ga".
1 - Een extra venster "Add-ins" wordt weergegeven op de monitor. Vink het vakje naast de gewenste functie aan en klik op OK.
- De gewenste functie verschijnt op het lint rechts van het gedeelte "Gegevens".
Over modellen
Deze informatie zal uiterst nuttig zijn voor degenen die net kennis hebben gemaakt met het concept van "optimalisatiemodel". Voordat u "Zoeken naar een oplossing" gebruikt, wordt aanbevolen om de materialen te bestuderen over de methoden voor het bouwen van modellen:
- de overwogen optie zal het mogelijk maken om de beste methode te identificeren om middelen toe te wijzen voor investeringen, het laden van de gebouwen, het leveren van goederen of andere acties waar het nodig is om de beste oplossing te vinden.
- De “optimale methode” in zo’n situatie zou betekenen: het verhogen van de inkomsten, het verlagen van de kosten, het verbeteren van de kwaliteit, etc.
Typische optimalisatietaken:
- Vaststelling van een productieplan, waarbij de winst uit de verkoop van vrijgekomen goederen maximaal zal zijn.
- Bepaling van transportkaarten, waarbij transportkosten worden geminimaliseerd.
- Zoek de verdeling van meerdere machines voor verschillende soorten werk, zodat de productiekosten worden verlaagd.
- Bepaling van de kortste tijd voor voltooiing van het werk.
Belangrijk! Om de taak te formaliseren, is het noodzakelijk om een model te maken dat de belangrijkste parameters van het onderwerpgebied weerspiegelt. In Excel is een model een set formules die variabelen gebruiken. De overwogen optie zoekt naar zodanige indicatoren dat de doelfunctie groter (minder) of gelijk is aan de opgegeven waarde.
Voorbereidende fase
Voordat u een functie op het lint plaatst, moet u begrijpen hoe de optie werkt. In de tabel staat bijvoorbeeld informatie over de verkoop van goederen. De taak is om voor elk artikel een korting toe te kennen, die 4.5 miljoen roebel zou zijn. De parameter wordt weergegeven in een cel met de naam doel. Op basis hiervan worden andere parameters berekend.
Onze taak zal zijn om de korting te berekenen waarmee de bedragen voor de verkoop van verschillende producten worden vermenigvuldigd. Deze 2 elementen zijn verbonden door een formule die als volgt is geschreven: =D13*$G$2. Waar in D13 de totale hoeveelheid voor de implementatie is geschreven, en $G$2 het adres is van het gewenste element.
De functie gebruiken en instellen
Wanneer de formule klaar is, moet u de functie zelf rechtstreeks gebruiken:
- U moet overschakelen naar het gedeelte "Gegevens" en op "Zoeken naar een oplossing" klikken.
- De "Opties" worden geopend, waar de vereiste instellingen worden ingesteld. In de regel "Optimaliseer de doelfunctie:" moet u de cel specificeren waarin de som van de kortingen wordt weergegeven. Het is mogelijk om zelf de coördinaten voor te schrijven of uit het document te kiezen.
- Vervolgens moet u naar de instellingen van andere parameters gaan. In het gedeelte "Aan:" is het mogelijk om de maximum- en minimumlimieten of een exact aantal in te stellen.
- Vervolgens wordt het veld "De waarden van variabelen wijzigen:" gevuld. Hier worden de gegevens van de gewenste cel ingevoerd, die een bepaalde waarde bevat. Coördinaten worden onafhankelijk geregistreerd of er wordt op de corresponderende cel in het document geklikt.
- Vervolgens wordt het tabblad "Volgens beperkingen:" bewerkt, waar de beperkingen op de toegepaste gegevens worden ingesteld. Zo zijn decimale breuken of negatieve getallen uitgesloten.
- Daarna wordt een venster geopend waarin u beperkingen in de berekeningen kunt toevoegen. De eerste regel bevat de coördinaten van een cel of een heel bereik. Volgens de voorwaarden van de taak worden de gegevens van de gewenste cel aangegeven, waar de kortingsindicator wordt weergegeven. Vervolgens wordt het vergelijkingsteken bepaald. Het is ingesteld op "groter dan of gelijk aan", zodat de uiteindelijke waarde geen minteken heeft. De "Limiet" die in regel 3 is ingesteld, is in deze situatie 0. Het is ook mogelijk om een limiet in te stellen met “Toevoegen”. De volgende stappen zijn hetzelfde.
- Wanneer de bovenstaande stappen zijn voltooid, verschijnt de ingestelde limiet in de grootste regel. De lijst kan groot zijn en hangt af van de complexiteit van de berekeningen, maar in een bepaalde situatie is 1 voorwaarde voldoende.
- Daarnaast is het mogelijk om andere geavanceerde instellingen te selecteren. Rechtsonder is er een optie “Opties” waarmee u dit kunt doen.
- In de instellingen kunt u de "Beperkingsnauwkeurigheid" en "Oplossingslimieten" instellen. In onze situatie is het niet nodig om deze opties te gebruiken.
- Wanneer de instellingen zijn voltooid, start de functie zelf - klik op "Een oplossing zoeken".
- Daarna voert het programma de vereiste berekeningen uit en geeft de definitieve berekeningen uit in de vereiste cellen. Vervolgens wordt een venster met de resultaten geopend, waar de resultaten worden opgeslagen / geannuleerd, of de zoekparameters worden geconfigureerd volgens een nieuwe. Wanneer de gegevens aan de eisen voldoen, wordt de gevonden oplossing opgeslagen. Als u het vakje "Terug naar het dialoogvenster met zoekopties voor oplossingen" van tevoren aanvinkt, wordt een venster met functie-instellingen geopend.
- Het is mogelijk dat de berekeningen onjuist zijn gebleken of dat de oorspronkelijke gegevens moeten worden gewijzigd om andere indicatoren te verkrijgen. In een dergelijke situatie moet u het instellingenvenster opnieuw openen en de informatie nogmaals controleren.
- Wanneer de gegevens juist zijn, kan een alternatieve methode worden gebruikt. Voor deze doeleinden moet u op de huidige optie klikken en de meest geschikte methode selecteren in de lijst die verschijnt:
- Een oplossing vinden met behulp van een gegeneraliseerd verloop voor niet-lineaire problemen. Standaard wordt deze optie gebruikt, maar het is mogelijk om andere te gebruiken.
- Oplossingen vinden voor lineaire problemen op basis van de simplexmethode.
- Evolutionair zoeken gebruiken om een taak te voltooien.
Opgelet! Als de bovenstaande opties de taak niet aankunnen, moet u de gegevens in de instellingen opnieuw controleren, omdat dit vaak de grootste fout is bij dergelijke taken.
- Wanneer de gewenste korting is ontvangen, moet deze nog worden toegepast om het bedrag aan kortingen voor elk artikel te berekenen. Voor dit doel wordt het eerste element van de kolom "Kortingsbedrag" gemarkeerd, de formule wordt geschreven «=D2*$G$2» en druk op "Enter". Dollartekens worden zo neergezet dat wanneer de formule wordt uitgerekt tot aangrenzende lijnen, G2 niet verandert.
- Het kortingsbedrag voor het eerste artikel wordt nu verkregen. Vervolgens moet u de cursor over de hoek van de cel bewegen, wanneer deze een "plus" wordt, wordt de LMB ingedrukt en wordt de formule uitgerekt tot de vereiste regels.
- Daarna is de tafel eindelijk klaar.
Zoekopties laden/opslaan
Deze optie is handig bij het toepassen van verschillende beperkingsopties.
- Klik in het menu Solution Finder-opties op Laden/Opslaan.
- Voer het bereik voor het modelgebied in en klik op Opslaan of Laden.
Bij het opslaan van het model wordt een verwijzing ingevoerd naar 1 cel van een lege kolom waar het optimalisatiemodel zal worden geplaatst. Tijdens het laden van het model wordt een verwijzing ingevoerd naar het hele bereik dat het optimalisatiemodel bevat.
Belangrijk! Om de laatste instellingen in het menu Oplossingsopties op te slaan, wordt een werkmap opgeslagen. Elk blad erin heeft zijn eigen Oplosser-invoegtoepassingen. Bovendien is het mogelijk om meer dan 1 taak voor een werkblad in te stellen door op de knop "Laden of opslaan" te klikken om individuele taken op te slaan.
Een eenvoudig voorbeeld van het gebruik van Oplosser
Het is noodzakelijk om de container met containers te laden, zodat de massa maximaal is. De tank heeft een inhoud van 32 kubieke meter. m. Een gevulde doos heeft een gewicht van 20 kg, het volume is 0,15 kubieke meter. m. Doos – 80 kg en 0,5 cu. m. Het is vereist dat het totale aantal containers minimaal 110 stuks is. De gegevens zijn als volgt georganiseerd:
De modelvariabelen zijn groen gemarkeerd. De doelfunctie is rood gemarkeerd. Beperkingen: per het kleinste aantal containers (groter dan of gelijk aan 110) en per gewicht (=SOMPRODUCT(B8:C8,B6:C6) – totaal tarragewicht in de container.
Naar analogie beschouwen we het totale volume: =SOMPRODUCT(B7:C7,B8:C8). Een dergelijke formule is nodig om een limiet te stellen aan het totale volume aan containers. Vervolgens worden via "Zoeken naar een oplossing" koppelingen ingevoerd naar elementen met variabelen, formules en de indicatoren zelf (of koppelingen naar specifieke cellen). Het aantal containers is natuurlijk een geheel getal (het is ook een beperking). We drukken op "Zoek een oplossing", waardoor we een dergelijk aantal containers vinden wanneer de totale massa maximaal is en met alle beperkingen rekening wordt gehouden.
Zoeken naar oplossing kan geen oplossingen vinden
Een dergelijke melding verschijnt wanneer de betreffende functie geen combinaties van variabele scores heeft gevonden die aan elke beperking voldoen. Bij gebruik van de Simplex-methode is het goed mogelijk dat er geen oplossing is.
Wanneer een methode voor het oplossen van niet-lineaire problemen wordt gebruikt, in alle gevallen uitgaande van de initiële indicatoren van de variabelen, geeft dit aan dat de mogelijke oplossing verre van dergelijke parameters ligt. Als u de functie uitvoert met andere initiële indicatoren van de variabelen, is er waarschijnlijk een oplossing.
Bij gebruik van de niet-lineaire methode werden bijvoorbeeld de elementen van de tabel met variabelen niet gevuld en vond de functie geen oplossingen. Dit betekent niet dat er geen oplossing is. Nu, rekening houdend met de resultaten van een bepaalde beoordeling, worden andere gegevens ingevoerd in de elementen met variabelen die dicht bij de ontvangen liggen.
In elke situatie moet u het model in eerste instantie onderzoeken op de afwezigheid van een beperkingsconflict. Vaak hangt dit samen met een onjuiste selectie van de verhouding of de beperkende indicator.
In het bovenstaande voorbeeld is de maximale volume-indicator 16 kubieke meter. m in plaats van 32, omdat een dergelijke beperking in tegenspraak is met de indicatoren voor het minimumaantal zitplaatsen, omdat dit overeenkomt met het aantal van 16,5 kubieke meter. m.
Conclusie
Op basis hiervan zal de optie "Zoeken naar een oplossing" in Excel helpen bij het oplossen van specifieke problemen die nogal moeilijk of onmogelijk op de gebruikelijke manieren op te lossen zijn. De moeilijkheid bij het toepassen van deze methode is dat deze optie in eerste instantie verborgen is, waardoor de meeste gebruikers zich niet bewust zijn van de aanwezigheid ervan. Bovendien is de functie vrij moeilijk te leren en te gebruiken, maar met goed onderzoek zal het grote voordelen opleveren en berekeningen vergemakkelijken.