Løs funksjon i Excel. Aktiver, bruk case med skjermbilder

"Søk etter en løsning" er et Excel-tillegg, der det er mulig å velge den beste løsningen på problemer basert på spesifiserte restriksjoner. Funksjonen gjør det mulig å planlegge ansatte, fordele kostnader eller investeringer. Å vite hvordan denne funksjonen fungerer vil spare deg for tid og krefter.

Hva er Search for Solutions

I kombinasjon med forskjellige andre alternativer i Excel, er det en mindre populær, men ekstremt nødvendig funksjon "Søk etter en løsning". Til tross for at det ikke er lett å finne det, hjelper det å bli kjent med det og bruke det til å løse mange problemer. Alternativet behandler dataene og gir den optimale løsningen fra de tillatte. Artikkelen beskriver hvordan Søk etter en løsning fungerer direkte.

Slik slår du på «Søk etter en løsning»-funksjonen

Til tross for effektiviteten, er det aktuelle alternativet ikke på et fremtredende sted på verktøylinjen eller kontekstmenyen. De fleste brukere som jobber i Excel er ikke klar over dets tilstedeværelse. Som standard er denne funksjonen deaktivert, for å vise den, utfør følgende handlinger:

1. Åpne "Fil" ved å klikke på det aktuelle navnet.
2. Klikk på "Innstillinger"-delen.
3. Velg deretter underseksjonen "Tillegg". Alle tilleggene til programmet vises her, inskripsjonen "Management" vises nedenfor. På høyre side av den vil det være en popup-meny der du skal velge "Excel-tillegg". Klikk deretter "Go".
   

   

4. Et ekstra vindu "Add-ins" vil vises på skjermen. Kryss av i boksen ved siden av ønsket funksjon og klikk OK.
5. Den ønskede funksjonen vises på båndet til høyre for "Data"-delen.

Om modeller

Denne informasjonen vil være ekstremt nyttig for de som nettopp har blitt kjent med konseptet "optimeringsmodell". Før du bruker "Søk etter en løsning", anbefales det å studere materialene på metodene for å bygge modeller:

• alternativet som vurderes vil gjøre det mulig å identifisere den beste metoden for å allokere midler til investeringer, lasting av lokalet, levering av varer eller andre handlinger der det er nødvendig for å finne den beste løsningen.
• Den "optimale metoden" i en slik situasjon vil bety: øke inntektene, redusere kostnader, forbedre kvaliteten osv.

Typiske optimaliseringsoppgaver:

• Fastsettelse av en produksjonsplan, hvor fortjenesten fra salg av frigjorte varer vil være maksimal.
• Bestemmelse av transportkart, hvor transportkostnadene minimeres.
• Søk etter fordeling av flere maskiner for ulike typer arbeid, slik at produksjonskostnadene reduseres.
• Fastsettelse av korteste tid for ferdigstillelse av arbeid.

Viktig! For å formalisere oppgaven er det nødvendig å lage en modell som gjenspeiler hovedparametrene til fagområdet. I Excel er en modell et sett med formler som bruker variabler. Det vurderte alternativet ser etter slike indikatorer at målfunksjonen er større (mindre) eller lik den angitte verdien.

Forberedende stadium

Før du plasserer en funksjon på båndet, må du forstå hvordan alternativet fungerer. For eksempel er det informasjon om salg av varer angitt i tabellen. Oppgaven er å tildele en rabatt for hver vare, som vil være 4.5 millioner rubler. Parameteren vises i en celle kalt mål. Basert på det beregnes andre parametere.

Vår oppgave blir å beregne rabatten som summene for salg av ulike produkter multipliseres med. Disse 2 elementene er forbundet med en formel skrevet slik: =D13*$G$2. Hvor i D13 er totalmengden for implementeringen skrevet, og $G$2 er adressen til ønsket element.

Bruke funksjonen og sette den opp

Når formelen er klar, må du bruke selve funksjonen direkte:

1. Du må bytte til "Data"-delen og klikke på "Søk etter en løsning".

2. "Alternativer" åpnes, der de nødvendige innstillingene er satt. I linjen "Optimaliser målfunksjonen:" bør du spesifisere cellen hvor summen av rabattene vises. Det er mulig å foreskrive koordinatene selv eller velge fra dokumentet.

3. Deretter må du gå til innstillingene for andre parametere. I «Til:»-delen er det mulig å angi maksimums- og minimumsgrenser eller et nøyaktig antall.

4. Deretter er feltet "Endre verdiene til variabler:" fylt ut. Her legges dataene til ønsket celle inn, som inneholder en bestemt verdi. Koordinater registreres uavhengig eller den tilsvarende cellen i dokumentet klikkes.

5. Deretter redigeres fanen "I henhold til restriksjoner:" der begrensningene for de brukte dataene er satt. For eksempel er desimalbrøker eller negative tall ekskludert.

6. Etter det åpnes et vindu som lar deg legge til begrensninger i beregningene. Startlinjen inneholder koordinatene til en celle eller et helt område. Etter betingelsene for oppgaven indikeres dataene til ønsket celle, hvor rabattindikatoren vises. Deretter bestemmes sammenligningstegnet. Den er satt til "større enn eller lik" slik at den endelige verdien ikke er med et minustegn. "Grensen" satt i linje 3 er 0 i denne situasjonen. Det er også mulig å sette en grense med "Legg til". De neste trinnene er de samme.

7. Når trinnene ovenfor er fullført, vises den angitte grensen i den største linjen. Listen kan være stor og vil avhenge av kompleksiteten til beregningene, men i en bestemt situasjon er 1 betingelse tilstrekkelig.

8. I tillegg er det mulig å velge andre avanserte innstillinger. Nederst til høyre er det et alternativ "Alternativer" som lar deg gjøre dette.

9. I innstillingene kan du angi "Begrensningsnøyaktighet" og "Løsningsgrenser". I vår situasjon er det ikke nødvendig å bruke disse alternativene.

10. Når innstillingene er fullført, starter selve funksjonen – klikk på "Finn en løsning".

11. Etter at programmet har utført de nødvendige beregningene og utsteder de endelige beregningene i de nødvendige cellene. Deretter åpnes et vindu med resultatene, hvor resultatene lagres/avbrytes, eller søkeparametrene konfigureres i henhold til en ny. Når dataene oppfyller kravene, lagres den funnet løsningen. Hvis du krysser av for «Gå tilbake til dialogboksen for løsningssøkalternativer» på forhånd, åpnes et vindu med funksjonsinnstillinger.

12. Det er en mulighet for at beregningene viste seg å være feil, eller det er behov for å endre de første dataene for å få andre indikatorer. I en slik situasjon må du åpne innstillingsvinduet på nytt og dobbeltsjekke informasjonen.
13. Når dataene er nøyaktige, kan en alternativ metode brukes. For disse formålene må du klikke på det gjeldende alternativet og velge den mest passende metoden fra listen som vises:

• Finne en løsning ved å bruke en generalisert gradient for ikke-lineære problemer. Som standard brukes dette alternativet, men det er mulig å bruke andre.
• Finne løsninger for lineære problemer basert på simpleksmetoden.
• Bruke evolusjonært søk for å fullføre en oppgave.

Oppmerksomhet! Når alternativene ovenfor ikke klarte å takle oppgaven, bør du sjekke dataene i innstillingene på nytt, da dette ofte er hovedfeilen i slike oppgaver.

14. Når ønsket rabatt er mottatt, gjenstår det å bruke den for å beregne antall rabatter for hver vare. For dette formålet er det første elementet i kolonnen "Rabattbeløp" uthevet, formelen er skrevet «=D2*$G$2» og trykk "Enter". Dollartegn settes ned slik at når formelen strekkes til tilstøtende linjer, endres ikke G2.

15. Rabattbeløpet for den første varen vil nå bli innhentet. Deretter bør du flytte markøren over hjørnet av cellen, når det blir et "pluss", trykkes LMB og formelen strekkes til de nødvendige linjene.
16. Etter det er bordet endelig klart.

Last inn/lagre søkealternativer

Dette alternativet er nyttig når du bruker ulike begrensningsalternativer.

1. I menyen Alternativer for Solution Finder klikker du på Last inn/Lagre.
2. Angi området for modellområdet og klikk på Lagre eller Last inn.

Når modellen lagres, legges det inn en referanse til 1 celle i en tom kolonne hvor optimaliseringsmodellen skal plasseres. Under modelllasting legges det inn en referanse til hele området som inneholder optimaliseringsmodellen.

Viktig! For å lagre de siste innstillingene i Løsningsalternativer-menyen, lagres en arbeidsbok. Hvert ark i det har sine egne Solver-tilleggsalternativer. I tillegg er det mulig å angi mer enn 1 oppgave for et ark ved å klikke på "Last inn eller lagre"-knappen for å lagre individuelle oppgaver.

Et enkelt eksempel på bruk av Solver

Det er nødvendig å laste beholderen med beholdere slik at massen er maksimal. Tanken har et volum på 32 kubikkmeter. m. En fylt boks har en vekt på 20 kg, volumet er 0,15 kubikkmeter. m. Eske – 80 kg og 0,5 kubikk. m. Det kreves at totalt antall beholdere er minst 110 stk. Dataene er organisert slik:

Modellvariablene er markert med grønt. Objektivfunksjonen er uthevet i rødt. Begrensninger: etter det minste antall beholdere (større enn eller lik 110) og etter vekt (=SUMPRODUKT(B8:C8;B6:C6) – total egenvekt i beholderen.

I analogi vurderer vi det totale volumet: =SUMPRODUKT(B7:C7;B8:C8). En slik formel er nødvendig for å sette en grense på det totale volumet av beholdere. Deretter, gjennom "Søk etter en løsning", legges lenker til elementer med variabler, formler og selve indikatorene (eller lenker til bestemte celler). Selvfølgelig er antall beholdere et heltall (det er også en begrensning). Vi trykker "Finn en løsning", som et resultat av at vi finner et slikt antall containere når totalmassen er maksimal og alle restriksjoner er tatt i betraktning.

Søk etter løsning klarte ikke å finne løsninger

Et slikt varsel dukker opp når den aktuelle funksjonen ikke har funnet kombinasjoner av variable skårer som tilfredsstiller hver begrensning. Når du bruker Simplex-metoden, er det ganske mulig at det ikke er noen løsning.

Når en metode for å løse ikke-lineære problemer brukes, i alle tilfeller med utgangspunkt i de innledende indikatorene til variablene, indikerer dette at den mulige løsningen er langt fra slike parametere. Hvis du kjører funksjonen med andre innledende indikatorer for variablene, så er det sannsynligvis en løsning.

For eksempel, ved bruk av den ikke-lineære metoden, ble ikke elementene i tabellen med variabler fylt ut, og funksjonen fant ikke løsninger. Dette betyr ikke at det ikke finnes noen løsning. Nå, med tanke på resultatene av en viss vurdering, legges andre data inn i elementene med variabler som er nær de mottatt.

I enhver situasjon bør du først undersøke modellen for fravær av en begrensningskonflikt. Ofte er dette forbundet med feil valg av forholdet eller begrensende indikator.

I eksemplet ovenfor er maksimal volumindikator 16 kubikkmeter. m i stedet for 32, fordi en slik begrensning er i strid med indikatorene for minimum antall seter, siden det vil tilsvare antall 16,5 kubikkmeter. m.

konklusjonen

Basert på dette vil alternativet "Søk etter en løsning" i Excel hjelpe deg med å løse spesifikke problemer som er ganske vanskelige eller umulige å løse på vanlige måter. Vanskeligheten med å bruke denne metoden er at dette alternativet i utgangspunktet er skjult, og det er grunnen til at de fleste brukere ikke er klar over dets tilstedeværelse. I tillegg er funksjonen ganske vanskelig å lære og bruke, men med skikkelig forskning vil den gi store fordeler og lette beregninger.