Lös funktion i Excel. Aktivera, använd fall med skärmdumpar

"Sök efter en lösning" är ett Excel-tillägg, genom vilket det är möjligt att välja den bästa lösningen på problem baserat på de angivna begränsningarna. Funktionen gör det möjligt att schemalägga anställda, fördela kostnader eller investeringar. Att veta hur den här funktionen fungerar kommer att spara tid och ansträngning.

Vad är Sök efter lösningar

I kombination med olika andra alternativ i Excel finns det en mindre populär men extremt nödvändig funktion "Sök efter en lösning". Trots att det inte är lätt att hitta det, hjälper det att lära känna det och använda det för att lösa många problem. Alternativet bearbetar data och ger den optimala lösningen från de tillåtna. Artikeln beskriver hur Sök efter en lösning direkt fungerar.

Så här aktiverar du funktionen "Sök efter en lösning".

Trots effektiviteten är alternativet i fråga inte på en framträdande plats i verktygsfältet eller snabbmenyn. De flesta användare som arbetar i Excel är inte medvetna om dess närvaro. Som standard är den här funktionen inaktiverad, för att visa den, utför följande åtgärder:

1. Öppna "File" genom att klicka på lämpligt namn.
2. Klicka på avsnittet "Inställningar".
3. Välj sedan undersektionen "Tillägg". Alla tillägg till programmet kommer att visas här, inskriptionen "Management" kommer att visas nedan. På höger sida av den kommer det att finnas en popup-meny där du ska välja "Excel-tillägg". Klicka sedan på "Gå".
   

   

4. Ett ytterligare fönster "Tillägg" kommer att visas på monitorn. Markera rutan bredvid önskad funktion och klicka på OK.
5. Den önskade funktionen kommer att visas på menyfliksområdet till höger om avsnittet "Data".

Om modeller

Denna information kommer att vara extremt användbar för dem som precis har bekantat sig med konceptet "optimeringsmodell". Innan du använder "Sök efter en lösning" rekommenderas det att studera materialen om metoderna för att bygga modeller:

• alternativet som övervägs kommer att göra det möjligt att identifiera den bästa metoden för att allokera medel för investeringar, lastning av lokaler, leverans av varor eller andra åtgärder där det är nödvändigt för att hitta den bästa lösningen.
• Den "optimala metoden" i en sådan situation skulle innebära: ökade intäkter, sänkta kostnader, förbättrad kvalitet osv.

Typiska optimeringsuppgifter:

• Fastställande av en produktionsplan, under vilken vinsten från försäljning av frigjorda varor kommer att vara maximal.
• Fastställande av transportkartor, under vilka transportkostnaderna minimeras.
• Sök efter distribution av flera maskiner för olika typer av arbete, så att produktionskostnaderna minskar.
• Fastställande av den kortaste tiden för slutförande av arbetet.

Viktigt! För att formalisera uppgiften är det nödvändigt att skapa en modell som återspeglar ämnesområdets huvudparametrar. I Excel är en modell en uppsättning formler som använder variabler. Det övervägda alternativet letar efter sådana indikatorer att målfunktionen är större (mindre) eller lika med det angivna värdet.

Förberedande steg

Innan du placerar en funktion på bandet måste du förstå hur alternativet fungerar. Till exempel finns information om försäljning av varor som anges i tabellen. Uppgiften är att tilldela en rabatt för varje artikel, vilket skulle vara 4.5 miljoner rubel. Parametern visas i en cell som kallas mål. Baserat på det beräknas andra parametrar.

Vår uppgift blir att beräkna rabatten med vilken summorna för försäljning av olika produkter multipliceras. Dessa två element är sammankopplade med en formel skriven så här: =D13*$G$2. Där i D13 skrivs den totala kvantiteten för implementeringen, och $G$2 är adressen till det önskade elementet.

Använda funktionen och ställa in den

När formeln är klar måste du använda själva funktionen direkt:

1. Du måste byta till avsnittet "Data" och klicka på "Sök efter en lösning".

2. "Alternativ" öppnas, där de nödvändiga inställningarna ställs in. På raden "Optimera målfunktionen:" ska du ange cellen där summan av rabatterna visas. Det är möjligt att själv ordinera koordinaterna eller välja från dokumentet.

3. Därefter måste du gå till inställningarna för andra parametrar. I avsnittet "Till:" är det möjligt att ställa in max- och minimigränser eller ett exakt antal.

4. Då fylls fältet "Ändra värden för variabler:" i. Här anges data för den önskade cellen, som innehåller ett specifikt värde. Koordinater registreras oberoende eller så klickas motsvarande cell i dokumentet.

5. Därefter redigeras fliken "Enligt begränsningar:" där begränsningarna för tillämpad data ställs in. Till exempel är decimalbråk eller negativa tal exkluderade.

6. Efter det öppnas ett fönster som låter dig lägga till begränsningar i beräkningarna. Den initiala raden innehåller koordinaterna för en cell eller ett helt område. Efter villkoren för uppgiften indikeras data för den önskade cellen, där rabattindikatorn visas. Sedan bestäms jämförelsetecknet. Det är inställt på "större än eller lika med" så att det slutliga värdet inte är med ett minustecken. "Gränsen" på rad 3 är 0 i denna situation. Det är också möjligt att sätta en gräns med "Lägg till". Nästa steg är desamma.

7. När ovanstående steg är klara visas den inställda gränsen i den största raden. Listan kan vara stor och kommer att bero på komplexiteten i beräkningarna, men i en speciell situation är ett villkor tillräckligt.

8. Dessutom är det möjligt att välja andra avancerade inställningar. Längst ner till höger finns ett alternativ "Alternativ" som låter dig göra detta.

9. I inställningarna kan du ställa in "Begränsningsnoggrannhet" och "Lösningsgränser". I vår situation finns det inget behov av att använda dessa alternativ.

10. När inställningarna är klara startar själva funktionen – klicka på ”Hitta en lösning”.

11. Efter att programmet utför de nödvändiga beräkningarna och utfärdar de slutliga beräkningarna i de nödvändiga cellerna. Sedan öppnas ett fönster med resultaten, där resultaten sparas/avbryts, eller sökparametrarna konfigureras enligt en ny. När data uppfyller kraven sparas den hittade lösningen. Om du markerar rutan "Återgå till dialogrutan för lösningssökningsalternativ" i förväg öppnas ett fönster med funktionsinställningar.

12. Det finns en möjlighet att beräkningarna visade sig vara felaktiga eller att det finns ett behov av att ändra de initiala uppgifterna för att få andra indikatorer. I en sådan situation måste du öppna inställningsfönstret igen och dubbelkolla informationen.
13. När uppgifterna är korrekta kan en alternativ metod användas. För dessa ändamål måste du klicka på det aktuella alternativet och välja den mest lämpliga metoden från listan som visas:

• Hitta en lösning med hjälp av en generaliserad gradient för icke-linjära problem. Som standard används detta alternativ, men det är möjligt att använda andra.
• Att hitta lösningar på linjära problem baserat på simplexmetoden.
• Använda evolutionär sökning för att slutföra en uppgift.

Observera! När ovanstående alternativ inte klarade uppgiften, bör du kontrollera data i inställningarna igen, eftersom detta ofta är det största misstaget i sådana uppgifter.

14. När den önskade rabatten erhålls återstår att tillämpa den för att beräkna mängden rabatter för varje vara. För detta ändamål är det första elementet i kolumnen "Rabattbelopp" markerat, formeln skrivs «=D2*$G$2» och tryck på "Enter". Dollartecken sätts ner så att när formeln sträcks ut till intilliggande linjer ändras inte G2.

15. Rabattbeloppet för den ursprungliga artikeln kommer nu att erhållas. Sedan ska du flytta markören över hörnet av cellen, när det blir ett "plus", trycks LMB och formeln sträcks ut till de önskade linjerna.
16. Därefter är bordet äntligen klart.

Ladda/spara sökalternativ

Det här alternativet är användbart när du använder olika begränsningsalternativ.

1. I menyn Alternativ för Solution Finder klickar du på Ladda/spara.
2. Ange intervallet för modellområdet och klicka på Spara eller Ladda.

När modellen sparas skrivs en referens till 1 cell i en tom kolumn där optimeringsmodellen kommer att placeras. Vid modellladdning läggs en referens till hela sortimentet som innehåller optimeringsmodellen.

Viktigt! För att spara de senaste inställningarna i menyn Lösningsalternativ sparas en arbetsbok. Varje ark i den har sina egna Solver-tilläggsalternativ. Dessutom är det möjligt att ställa in mer än 1 uppgift för ett ark genom att klicka på knappen "Ladda eller spara" för att spara enskilda uppgifter.

Ett enkelt exempel på att använda Solver

Det är nödvändigt att ladda behållaren med behållare så att dess massa är maximal. Tanken har en volym på 32 kubikmeter. m. En fylld låda väger 20 kg, dess volym är 0,15 kubikmeter. m. Box – 80 kg och 0,5 cu. m. Det krävs att det totala antalet containrar är minst 110 st. Uppgifterna är organiserade så här:

Modellvariablerna är markerade med grönt. Objektivfunktionen är markerad i rött. Begränsningar: med det minsta antalet behållare (större än eller lika med 110) och efter vikt (=SUMMAPRODUKT(B8:C8;B6:C6) – total egenvikt i behållaren.

I analogi betraktar vi den totala volymen: =SUMMAPRODUKT(B7:C7;B8:C8). En sådan formel är nödvändig för att sätta en gräns för den totala volymen av behållare. Sedan, genom "Sök efter en lösning", läggs länkar till element med variabler, formler och själva indikatorerna (eller länkar till specifika celler). Naturligtvis är antalet behållare ett heltal (det är också en begränsning). Vi trycker på "Hitta en lösning", vilket gör att vi hittar ett sådant antal behållare när den totala massan är maximal och alla restriktioner beaktas.

Sök efter lösning kunde inte hitta lösningar

Ett sådant meddelande dyker upp när funktionen i fråga inte har hittat kombinationer av variabla poäng som uppfyller varje begränsning. När du använder Simplex-metoden är det mycket möjligt att det inte finns någon lösning.

När en metod för att lösa olinjära problem används, i alla fall med utgångspunkt från de initiala indikatorerna för variablerna, indikerar detta att den möjliga lösningen är långt ifrån sådana parametrar. Om du kör funktionen med andra initiala indikatorer för variablerna, så finns det förmodligen en lösning.

Till exempel, när man använder den icke-linjära metoden, fylldes inte elementen i tabellen med variabler, och funktionen hittade inga lösningar. Det betyder inte att det inte finns någon lösning. Nu, med hänsyn till resultaten av en viss bedömning, läggs andra data in i elementen med variabler som ligger nära de mottagna.

I alla situationer bör du först undersöka modellen för frånvaron av en begränsningskonflikt. Ofta är detta sammankopplat med felaktigt val av förhållandet eller begränsningsindikatorn.

I exemplet ovan är den maximala volymindikatorn 16 kubikmeter. m istället för 32, eftersom en sådan begränsning motsäger indikatorerna för det minsta antalet platser, eftersom det kommer att motsvara antalet 16,5 kubikmeter. m.

Slutsats

Baserat på detta kommer alternativet "Sök efter en lösning" i Excel att hjälpa till att lösa specifika problem som är ganska svåra eller omöjliga att lösa på de vanliga sätten. Svårigheten med att tillämpa denna metod är att det här alternativet initialt är dolt, vilket är anledningen till att de flesta användare inte är medvetna om dess närvaro. Dessutom är funktionen ganska svår att lära sig och använda, men med ordentlig forskning kommer den att ge stora fördelar och underlätta beräkningar.