Indhold
"Søg efter en løsning" er et Excel-tilføjelsesprogram, hvorigennem det er muligt at vælge den bedste løsning på problemer ud fra de angivne begrænsninger. Funktionen gør det muligt at planlægge medarbejdere, fordele omkostninger eller investeringer. Ved at vide, hvordan denne funktion fungerer, sparer du tid og kræfter.
Hvad er Søg efter løsninger
I kombination med forskellige andre muligheder i Excel er der en mindre populær, men yderst nødvendig funktion "Søg efter en løsning". På trods af at det ikke er let at finde det, hjælper det at lære det at kende og bruge det til at løse mange problemer. Muligheden behandler dataene og giver den optimale løsning fra de tilladte. Artiklen beskriver, hvordan Søg efter en løsning fungerer direkte.
Sådan slår du funktionen "Søg efter en løsning" til
På trods af effektiviteten er den pågældende mulighed ikke på et fremtrædende sted på værktøjslinjen eller kontekstmenuen. De fleste brugere, der arbejder i Excel, er ikke klar over dets tilstedeværelse. Som standard er denne funktion deaktiveret, for at vise den skal du udføre følgende handlinger:
- Åbn "File" ved at klikke på det relevante navn.
- Klik på sektionen "Indstillinger".
- Vælg derefter underafsnittet "Tilføjelser". Alle tilføjelser til programmet vil blive vist her, inskriptionen "Management" vises nedenfor. På højre side af den vil der være en pop-up menu, hvor du skal vælge "Excel Tilføjelser". Klik derefter på "Go".
1 - Et ekstra vindue "Add-ins" vil blive vist på skærmen. Marker afkrydsningsfeltet ud for den ønskede funktion, og klik på OK.
- Den ønskede funktion vises på båndet til højre for afsnittet "Data".
Om modeller
Denne information vil være yderst nyttig for dem, der lige er ved at stifte bekendtskab med begrebet "optimeringsmodel". Før du bruger "Søg efter en løsning", anbefales det at studere materialerne om metoderne til at bygge modeller:
- den overvejede mulighed vil gøre det muligt at identificere den bedste metode til at allokere midler til investeringer, lastning af lokalerne, levering af varer eller andre handlinger, hvor det er nødvendigt for at finde den bedste løsning.
- Den "optimale metode" i en sådan situation ville betyde: øge indkomsten, reducere omkostningerne, forbedre kvaliteten mv.
Typiske optimeringsopgaver:
- Fastlæggelse af en produktionsplan, hvor fortjenesten ved salg af frigivne varer vil være maksimal.
- Fastlæggelse af transportkort, hvor transportomkostningerne minimeres.
- Søg efter distribution af flere maskiner til forskellige typer arbejde, så produktionsomkostningerne reduceres.
- Fastlæggelse af den korteste tid til færdiggørelse af arbejdet.
Vigtigt! For at formalisere opgaven er det nødvendigt at lave en model, der afspejler hovedparametrene for fagområdet. I Excel er en model et sæt formler, der bruger variabler. Den overvejede mulighed leder efter sådanne indikatorer, at den objektive funktion er større (mindre) eller lig med den angivne værdi.
Forberedende fase
Før du placerer en funktion på båndet, skal du forstå, hvordan muligheden fungerer. For eksempel er der oplysninger om salg af varer angivet i tabellen. Opgaven er at tildele en rabat for hver vare, som ville være 4.5 millioner rubler. Parameteren vises inde i en celle kaldet mål. Baseret på det beregnes andre parametre.
Vores opgave bliver at beregne den rabat, som beløbene for salg af forskellige produkter ganges med. Disse 2 elementer er forbundet med en formel skrevet som denne: =D13*$G$2. Hvor i D13 er den samlede mængde for implementeringen skrevet, og $G$2 er adressen på det ønskede element.
Brug af funktionen og opsætning af den
Når formlen er klar, skal du bruge selve funktionen direkte:
- Du skal skifte til afsnittet "Data" og klikke på "Søg efter en løsning".
- "Valgmuligheder" åbnes, hvor de nødvendige indstillinger er indstillet. I linjen "Optimer målfunktionen:" skal du angive den celle, hvor summen af rabatterne vises. Det er muligt selv at ordinere koordinaterne eller vælge fra dokumentet.
- Dernæst skal du gå til indstillingerne for andre parametre. I afsnittet "Til:" er det muligt at indstille maksimum- og minimumsgrænser eller et nøjagtigt antal.
- Derefter er feltet "Ændring af værdier af variabler:" udfyldt. Her indtastes data for den ønskede celle, som indeholder en bestemt værdi. Koordinater registreres uafhængigt, eller der klikkes på den tilsvarende celle i dokumentet.
- Derefter redigeres fanen "Ifølge restriktioner:", hvor restriktionerne for de anvendte data er sat. For eksempel er decimalbrøker eller negative tal udelukket.
- Derefter åbnes et vindue, der giver dig mulighed for at tilføje begrænsninger i beregningerne. Den indledende linje indeholder koordinaterne for en celle eller et helt område. Efter betingelserne for opgaven er dataene for den ønskede celle angivet, hvor rabatindikatoren vises. Derefter bestemmes sammenligningstegnet. Den er sat til "større end eller lig med", så den endelige værdi ikke er med et minustegn. "Grænsen" sat i linje 3 er 0 i denne situation. Det er også muligt at sætte en grænse med "Tilføj". De næste trin er de samme.
- Når ovenstående trin er gennemført, vises den indstillede grænse i den største linje. Listen kan være stor og vil afhænge af kompleksiteten af beregningerne, men i en bestemt situation er 1 betingelse tilstrækkelig.
- Derudover er det muligt at vælge andre avancerede indstillinger. Nederst til højre er der en mulighed "Indstillinger", der giver dig mulighed for at gøre dette.
- I indstillingerne kan du indstille "Begrænsningsnøjagtighed" og "Løsningsgrænser". I vores situation er der ingen grund til at bruge disse muligheder.
- Når indstillingerne er gennemført, starter selve funktionen – klik på “Find en løsning”.
- Efter at programmet udfører de nødvendige beregninger og udsteder de endelige beregninger i de nødvendige celler. Derefter åbner et vindue med resultaterne, hvor resultaterne gemmes/annulleres, eller søgeparametrene konfigureres efter en ny. Når dataene opfylder kravene, gemmes den fundne løsning. Hvis du markerer boksen "Vend tilbage til dialogboksen for løsningssøgningsindstillinger" på forhånd, åbnes et vindue med funktionsindstillinger.
- Der er en mulighed for, at beregningerne viste sig at være fejlagtige, eller der er behov for at ændre de oprindelige data for at opnå andre indikatorer. I en sådan situation skal du genåbne indstillingsvinduet og dobbelttjekke oplysningerne.
- Når dataene er nøjagtige, kan en alternativ metode bruges. Til disse formål skal du klikke på den aktuelle mulighed og vælge den bedst egnede metode fra listen, der vises:
- At finde en løsning ved hjælp af en generaliseret gradient til ikke-lineære problemer. Som standard bruges denne mulighed, men det er muligt at bruge andre.
- At finde løsninger på lineære problemer baseret på simpleksmetoden.
- Brug af evolutionær søgning til at fuldføre en opgave.
OBS! Når ovenstående muligheder ikke klarede opgaven, bør du tjekke dataene i indstillingerne igen, da dette ofte er hovedfejlen i sådanne opgaver.
- Når den ønskede rabat er modtaget, er det tilbage at anvende den for at beregne mængden af rabatter for hver vare. Til dette formål er det indledende element i kolonnen "Rabatbeløb" fremhævet, formlen er skrevet «=D2*$G$2» og tryk på "Enter". Dollartegn sættes ned, så når formlen strækkes til tilstødende linjer, ændres G2 ikke.
- Rabatbeløbet for den oprindelige vare vil nu blive opnået. Derefter skal du flytte markøren over hjørnet af cellen, når det bliver et "plus", trykkes der på LMB, og formlen strækkes til de nødvendige linjer.
- Herefter er bordet endelig klar.
Indlæs/gem søgeindstillinger
Denne indstilling er nyttig, når du anvender forskellige begrænsningsindstillinger.
- I menuen Solution Finder Options skal du klikke på Indlæs/Gem.
- Indtast området for modelområdet, og klik på Gem eller Indlæs.
Når modellen gemmes, indtastes en reference til 1 celle i en tom kolonne, hvor optimeringsmodellen vil blive placeret. Under modelladning indtastes en reference til hele sortimentet, der indeholder optimeringsmodellen.
Vigtigt! For at gemme de sidste indstillinger i menuen Løsningsindstillinger gemmes en projektmappe. Hvert ark i det har sine egne Solver-tilføjelsesmuligheder. Derudover er det muligt at indstille mere end 1 opgave for et ark ved at klikke på knappen "Load or Save" for at gemme individuelle opgaver.
Et simpelt eksempel på brug af Solver
Det er nødvendigt at fylde beholderen med beholdere, så dens masse er maksimal. Tanken har et rumfang på 32 kubikmeter. m. En fyldt kasse har en vægt på 20 kg, dens volumen er 0,15 kubikmeter. m. Æske – 80 kg og 0,5 cu. m. Det kræves, at det samlede antal containere er mindst 110 stk. Dataene er organiseret således:
Modelvariablerne er markeret med grønt. Objektivfunktionen er fremhævet med rødt. Begrænsninger: efter det mindste antal beholdere (større end eller lig med 110) og efter vægt (=SUMPRODUKT(B8:C8;B6:C6) – total egenvægt i beholderen.
I analogi betragter vi det samlede volumen: =SUMPRODUKT(B7:C7;B8:C8). En sådan formel er nødvendig for at sætte en grænse for det samlede volumen af beholdere. Derefter, gennem "Søg efter en løsning", indtastes links til elementer med variabler, formler og selve indikatorerne (eller links til specifikke celler). Selvfølgelig er antallet af beholdere et heltal (det er også en begrænsning). Vi trykker på "Find en løsning", hvilket resulterer i, at vi finder et sådant antal beholdere, når den samlede masse er maksimal og alle restriktioner er taget i betragtning.
Søgning efter løsning kunne ikke finde løsninger
En sådan meddelelse dukker op, når den pågældende funktion ikke har fundet kombinationer af variable scores, der opfylder hver begrænsning. Når du bruger Simplex-metoden, er det meget muligt, at der ikke er nogen løsning.
Når der anvendes en metode til løsning af ikke-lineære problemer, i alle tilfælde med udgangspunkt i de indledende indikatorer for variablerne, indikerer dette, at den mulige løsning er langt fra sådanne parametre. Hvis du kører funktionen med andre indledende indikatorer for variablerne, så er der sandsynligvis en løsning.
For eksempel, ved brug af den ikke-lineære metode, blev elementerne i tabellen med variabler ikke udfyldt, og funktionen fandt ikke løsninger. Det betyder ikke, at der ikke er nogen løsning. Nu, under hensyntagen til resultaterne af en bestemt vurdering, indtastes andre data i elementerne med variabler, der er tæt på de modtagne.
I enhver situation bør du først undersøge modellen for fravær af en begrænsningskonflikt. Ofte er dette forbundet med forkert valg af forholdet eller den begrænsende indikator.
I ovenstående eksempel er den maksimale volumenindikator 16 kubikmeter. m i stedet for 32, fordi en sådan begrænsning er i modstrid med indikatorerne for minimumsantallet af sæder, da det vil svare til antallet af 16,5 kubikmeter. m.
Konklusion
Baseret på dette vil muligheden "Søg efter en løsning" i Excel hjælpe med at løse specifikke problemer, der er ret vanskelige eller umulige at løse på de sædvanlige måder. Vanskeligheden ved at anvende denne metode er, at denne mulighed oprindeligt er skjult, hvorfor de fleste brugere ikke er klar over dens tilstedeværelse. Derudover er funktionen ret svær at lære og bruge, men med ordentlig research vil den give store fordele og lette beregningerne.