De fleste problemer som løses ved hjelp av et regneark innebærer å finne ønsket resultat ved å bruke kjente kildedata. Men Excel har verktøy som lar deg løse det omvendte problemet: velg de første dataene for å oppnå ønsket resultat.

Et slikt verktøy erÅ finne en løsning, som er spesielt praktisk for å løse såkalte "optimaliseringsproblemer".

Hvis du ikke har brukt det førÅ finne en løsning, så må du installere det aktuelle tillegget.

Du kan gjøre det slik:

for versjoner eldre enn Excel 2007 via kommandoen meny Tjeneste --> tillegg;

siden Excel 2007 via dialogboksenExcel-alternativer

Siden Excel 2007knappen for å starteÅ finne en løsning vil vises på fanen Data.

I versjoner før Excel 2007 vil en lignende kommando vises i menyenService

La oss se på arbeidsprosedyren Å finne en løsning ved hjelp av et enkelt eksempel.

Eksempel 1. Bonusutdeling

La oss anta at du er leder for en produksjonsavdeling, og du må fordele en bonus på 100 000 rubler på en rettferdig måte. mellom avdelingsansatte i forhold til deres offisielle lønn. Du må med andre ord velge en proporsjonalitetskoeffisient for å beregne størrelsen på lønnsbonusen.

Først av alt lager vi en tabell med de første dataene og formlene som resultatet skal oppnås med. I vårt tilfelle er resultatet det totale premiebeløpet. Det er svært viktig at målcellen (C8) er koblet gjennom formler til ønsket celle som skal endres (E2). I eksemplet er de koblet sammen gjennom mellomformler som beregner bonusbeløpet for hver ansatt (C2:C7).

La oss nå lansere Å finne en løsning og angi de nødvendige parameterne i dialogboksen som åpnes. Utseende av dialogbokser i forskjellige versjoner litt annerledes:

Siden Excel 2010

Før Excel 2010

Etter å ha trykket på knappenFinn løsning (Kjør)Du kan allerede se resultatet oppnådd i tabellen. Samtidig vises en dialogboks på skjermenResultater av søket etter en løsning.

Siden Excel 2010

Før Excel 2010

Hvis resultatet du ser i tabellen passer deg, så i dialogboksen Løsningssøkeresultater pressen OK og noter resultatet i tabellen. Hvis resultatet ikke passer deg, klikk Kansellere og gå tilbake til forrige tilstand i tabellen.

Løsningen på dette problemet ser slik ut

Viktig: for eventuelle endringer i kildedataene for å få et nytt resultat Å finne en løsning må løpe igjen.

La oss se på et annet optimaliseringsproblem (maksimere fortjenesten)

Eksempel 2. Møbelproduksjon (profittmaksimering)

Selskapet produserer to modeller A og B av prefabrikkerte bokhyller.

Produksjonen deres er begrenset av tilgjengeligheten av råvarer (plater av høy kvalitet) og maskinbehandlingstid.

Hvert modell A-produkt krever 3 m²plater, og for produktmodell B - 4 m². Selskapet kan motta opptil 1700 m² med plater per uke fra sine leverandører.

Hvert modell A-produkt krever 12 minutters maskintid, og for produktmodell B - 30 min. 160 timers maskintid kan brukes per uke.

Hvor mange produkter av hver modell bør selskapet produsere per uke for å oppnå maksimal fortjeneste hvis hvert produkt av modell A gir 60 rubler. fortjeneste, og hvert produkt av modell B koster 120 rubler. fortjeneste?

Vi kjenner allerede fremgangsmåten.

Først lager vi tabeller med kildedata og formler. Arrangementet av celler på arket kan være helt vilkårlig, like praktisk for forfatteren. For eksempel som på bildet

La oss lansere Å finne en løsningog angi de nødvendige parameterne i dialogboksen

1. Målcelle B12 inneholder formelen for beregning av fortjeneste
2. Optimaliseringsparameter - maksimum
3. Modifiserte celler B9:C9
4. Begrensninger: de funnet verdiene må være heltall, ikke-negative; den totale mengden maskintid bør ikke overstige 160 timer (referanse til celle D16); den totale mengden råvarer bør ikke overstige 1700 m² (referansecelle D15). Her, i stedet for lenker til cellene D15 og D16, kan tall angis, men ved bruk av lenker kan eventuelle endringer i begrensninger gjøres direkte i tabellen
5. Trykk på knappen Finn løsning (Kjør) og etter bekreftelse får vi resultatet

Men selv om du opprettet formlene riktig og satte begrensningene, kan resultatet bli uventet. Når du for eksempel løser dette problemet, kan du se følgende resultat:

Og dette til tross for at begrensningen var satt hel. I slike tilfeller kan du prøve å justere innstillingene Å finne en løsning. For å gjøre dette i vinduet Å finne en løsning trykk på knappen Alternativer og vi kommer inn i dialogboksen med samme navn

Den første av de valgte parameterne er ansvarlig for nøyaktigheten av beregningene. Ved å redusere det kan du oppnå et mer nøyaktig resultat, i vårt tilfelle - heltallsverdier. Det andre av de uthevede alternativene (tilgjengelig fra Excel 2010) svarer på spørsmålet: hvordan kan brøkresultater oppnås når du begrenserhel? Det viser seg Å finne en løsningdenne begrensningen ble ganske enkelt ignorert i samsvar med det sjekkede flagget.

Eksempel 3. Transportproblem (kostnadsminimering)

På ordre fra et byggefirma transporteres sand fra tre leverandører (steinbrudd) til fem forbrukere (byggeplasser). Leveringskostnaden er derfor inkludert i prisen for objektet byggefirma er interessert i å møte sandbehovet på byggeplassene sine på en billigst mulig måte.

Gitt: sandreserver i steinbrudd; byggeplassen sand behov; transportkostnader mellom hvert leverandør-forbruker-par.

Det er nødvendig å finne en optimal transportordning for å tilfredsstille behov (fra hvor og til hvor), der de totale transportkostnadene vil være minimale.

Et eksempel på plasseringen av celler med kildedata og begrensninger, de ønskede cellene og målcellen er vist i figuren

I de grå cellene er det formler for summene etter rader og kolonner, og i målcellen er det en formel for å beregne de totale transportkostnadene.

Vi starter Søk etter en løsning og setter de nødvendige parameterne (se figur)

Klikk Finn løsning (Kjør) og få resultatet vist nedenfor

Avslutningsvis foreslår jeg at du prøver deg på å bruke Å finne en løsning og bruk den til å løse et gammelt problem:

En bonde kjøpte 100 storfe på markedet for 100 rubler. En okse koster 10 rubler, en ku 5 rubler, en kalv 50 kopek. Hvor mange okser, kyr og kalver kjøpte bonden?

Å finne en løsning er et tillegg Microsoft Excel, som du kan finne optimal løsning oppgaver som tar hensyn til brukerspesifiserte begrensninger.

Vi vil vurdere å finne en løsning i (dette tillegget har gjennomgått noen endringer i forhold til forrige versjon V .
I denne artikkelen skal vi se på:

• lage en optimaliseringsmodell på et MS EXCEL-ark
• innstilling Finne en løsning;
• enkelt eksempel (lineær modell).

Installasjon Søk etter en løsning

Team Å finne en løsning er i gruppen Analyse på fanen Data.

Hvis laget Å finne en løsning i gruppen Analyse ikke er tilgjengelig, må du aktivere tillegget med samme navn.
Slik gjør du dette:

• På fanen Fil velge lag Alternativer, og deretter kategorien Tillegg;
• I feltet Kontroll velg verdi Excel-tillegg og trykk på knappen Gå;
• I feltet Tilgjengelige tillegg merk av i boksen ved siden av elementet Å finne en løsning og klikk OK.

Note. Vindu Tillegg også tilgjengelig på fanen Utvikler. Slik aktiverer du denne fanen.

Etter å ha trykket på knappen Å finne en løsning i gruppen Analyse, dialogboksen åpnes .

Med hyppig bruk Å finne en løsning det er mer praktisk å starte det fra panelet rask tilgang, og ikke fra fanen Data. For å plassere en knapp på panelet, høyreklikk på den og velg Legg til hurtigtilgangsverktøylinjen.

Om modeller

Denne delen er for de som nettopp har blitt kjent med konseptet med en optimaliseringsmodell.

Råd. Før bruk Å finne en løsning Vi anbefaler på det sterkeste å studere litteraturen om løsning av optimaliseringsproblemer og bygging av modeller.

Nedenfor er et lite pedagogisk program om dette emnet.

Overbygg Å finne en løsning hjelper med å bestemme beste måten gjøre noe:

• "Noe" kan inkludere å bevilge penger til investeringer, laste et lager, levere varer eller annen fagaktivitet der det er nødvendig for å finne den optimale løsningen.
• Den "beste måten" eller optimale løsning i dette tilfellet betyr: maksimere fortjeneste, minimere kostnader, oppnå beste kvalitet osv.

Her er noen typiske eksempler optimeringsoppgaver:

• Bestem hvor inntekten fra salg av produserte produkter er maksimal;
• Bestem hvor de totale transportkostnadene vil være minimale;
• Finn ut at de totale produksjonskostnadene vil være minimale;
• Bestem minimumsfristen for å fullføre alt prosjektarbeid (kritisk vei).

For å formalisere oppgaven er det nødvendig å lage en modell som vil gjenspeile de vesentlige egenskapene til fagområdet (og som ikke inkluderer mindre detaljer). Det skal bemerkes at modellen er optimalisert Å finne en løsning med bare én indikator(denne optimaliserte indikatoren kalles målfunksjon).
I MS EXCEL er en modell en samling sammenkoblede formler som bruker variabler som argumenter. Vanligvis kan disse variablene bare akseptere gyldige verdier, underlagt brukerspesifiserte begrensninger.
Å finne en løsning velger slike verdier av disse variablene (med forbehold om spesifiserte restriksjoner) at objektivfunksjonen er maksimal (minimum) eller lik en gitt numerisk verdi.

Note. I det enkleste tilfellet kan modellen beskrives ved hjelp av en enkelt formel. Noen av disse modellene kan optimaliseres ved hjelp av verktøyet. Før møte for første gang Å finne en løsning Det er fornuftig å først forstå det relaterte verktøyet i detalj.
Hovedforskjeller Parametervalg fra Å finne en løsning:

• Parametervalg fungerer kun med enkeltvariable modeller;
• det er umulig å sette restriksjoner på variabler;
• det er ikke maksimum eller minimum av den objektive funksjonen som bestemmes, men dens likhet til en viss verdi;
• fungerer effektivt bare hvis lineære modeller, i det ikke-lineære tilfellet, finner det lokale optimumet (nærmest den opprinnelige verdien av variabelen).

Utarbeidelse av en optimaliseringsmodell i MS EXCEL

Å finne en løsning optimerer verdien av målfunksjonen. En objektivfunksjon er en formel som returnerer en enkelt verdi i en celle. Resultatet av formelen bør avhenge av modellvariablene (ikke nødvendigvis direkte, men gjennom resultatet av beregning av andre formler).
Modellbegrensninger kan pålegges både på variasjonsområdet til selve variablene, og på resultatene av beregning av andre modellformler som avhenger av disse variablene.
Alle celler som inneholder modellvariabler og begrensninger må være plassert på bare ett ark i arbeidsboken. Legge inn parametere i en dialogboks Å finne en løsning kun mulig fra dette arket.
Målfunksjonen (cellen) må også være plassert på dette arket. Men mellomberegninger (formler) kan plasseres på andre ark.

Råd. Organiser modelldataene slik at det kun er én modell på ett MS EXCEL-ark. Ellers, for å utføre beregninger, må du hele tiden lagre og laste inn innstillinger Å finne en løsning(se nedenfor).

La oss presentere en algoritme for å jobbe med Å finne en løsning, som anbefales av utviklerne selv (www.solver.com):

• Definer celler med modellvariabler (beslutningsvariabler);
• Lag en formel i en celle som vil beregne objektivfunksjonen til modellen din;
• Lag formler i celler som vil beregne verdier sammenlignet med begrensningene (venstre side av uttrykket);
• Bruke dialogboksen Å finne en løsning skriv inn lenker til celler som inneholder variabler, til objektivfunksjonen, til formler for begrensninger og verdiene til selve begrensningene;
• Løp Å finne en løsning for å finne den optimale løsningen.

La oss gå gjennom alle disse trinnene ved å bruke et enkelt eksempel.

Enkelt brukseksempel Å finne en løsning

Det er nødvendig å laste containeren med varer slik at vekten på containeren er maksimal. Containeren har et volum på 32 kubikkmeter. Gjenstander er i esker og kasser. Hver boks med varer veier 20 kg, volumet er 0,15 m3. Boks - henholdsvis 80kg og 0,5m3. Det er nødvendig at det totale antallet beholdere er minst 110 stykker.

Vi organiserer disse modellene som følger (se eksempelfil).

Modellvariabler (antall av hver type beholder) er uthevet med grønt.
Objektivfunksjonen (totalvekten av alle bokser og kasser) er i rødt.
Modellbegrensninger: minimumsmengde beholdere (>=110) og totalt volum (<=32) – синим.
Objektfunksjonen beregnes ved hjelp av formelen =SUMPRODUKT(B8:C8;B6:C6) er totalvekten av alle esker og kasser som er lastet inn i containeren.
På samme måte beregner vi det totale volumet - =SUMPRODUKT(B7:C7;B8:C8). Denne formelen er nødvendig for å sette en grense på det totale volumet av bokser og kasser (<=32).
For å angi modellbegrensningen, beregner vi det totale antallet beholdere =SUM(B8:C8) .
Bruker nå dialogboksen Å finne en løsning La oss legge inn lenker til celler som inneholder variabler, en objektiv funksjon, formler for begrensninger og verdiene til selve begrensningene (eller lenker til de tilsvarende cellene).
Det er klart at antall bokser og kasser må være et heltall - dette er en annen begrensning ved modellen.

Etter å ha trykket på knappen Finn en løsning antall bokser og kasser vil bli funnet der deres totalvekt (objektiv funksjon) er maksimal, og samtidig er alle spesifiserte restriksjoner oppfylt.

Gjenoppta

Faktisk er hovedproblemet når du løser optimaliseringsproblemer ved hjelp av Å finne en løsning Det er ikke finessene ved å sette opp dette analyseverktøyet som betyr noe, men riktigheten av å konstruere en modell som er tilstrekkelig til oppgaven. Derfor vil vi i andre artikler konsentrere oss spesifikt om å bygge modeller, fordi en «skjev» modell ofte er årsaken til manglende evne til å finne en løsning vha. Å finne en løsning.
Det er ofte lettere å se gjennom flere typiske problemer, finne et lignende blant dem, og deretter tilpasse denne modellen til din oppgave.
Løse klassiske optimaliseringsproblemer ved å bruke Å finne en løsning vurdert.

Solver kunne ikke finne en gjennomførbar løsning

Denne meldingen vises når Å finne en løsning kunne ikke finne kombinasjoner av variabelverdier som samtidig tilfredsstiller alle begrensningene.
Hvis du bruker Enkel metode for å løse lineære problemer, så kan du være sikker på at det virkelig ikke finnes noen løsning.
Hvis du bruker en metode for å løse ikke-lineære problemer som alltid starter med startverdier for variablene, kan dette også bety at den gjennomførbare løsningen er langt unna disse startverdiene. Hvis du løper Å finne en løsning med andre startverdier av variablene, så vil kanskje en løsning bli funnet.
La oss forestille oss at når du løser et problem ved hjelp av en ikke-lineær metode, ble cellene med variabler stående tomme (dvs. startverdiene er 0), og Å finne en løsning fant ikke en løsning. Dette betyr ikke at det egentlig ikke er en løsning (selv om det kan være tilfelle). Nå, basert på resultatene av en viss ekspertvurdering, vil vi legge inn et annet sett med verdier i cellene med variabler, som etter din mening er nær den optimale (søkte). I dette tilfellet Å finne en løsning kan finne en løsning (hvis en faktisk finnes).

Note. Du kan lese om påvirkningen av modellens ikke-linearitet på beregningsresultatene i den siste delen av artikkelen.

Uansett (lineær eller ikke-lineær) må du først analysere modellen for konsistens av begrensninger, det vil si betingelser som ikke kan tilfredsstilles samtidig. Oftest er dette på grunn av feil valg av forhold (f.eks.<= вместо >=) eller grenseverdi.
Hvis, for eksempel, i eksemplet diskutert ovenfor, verdien av maksimalt volum er satt til 16 m3 i stedet for 32 m3, vil denne begrensningen være i strid med begrensningen på minimum antall seter (110), fordi minimum antall plasser tilsvarer et volum lik 16,5 m3 (110 * 0,15, hvor 0,15 er volumet av boksen, dvs. den minste beholderen). Ved å sette den maksimale volumgrensen til 16 m3, Å finne en løsning vil ikke finne en løsning.

Med en grense på 17 m3 Å finne en løsning vil finne en løsning.

Noen innstillinger Å finne en løsning

Løsningsmetode
Modellen omtalt ovenfor er lineær, dvs. objektivfunksjonen (M er totalvekten som kan være maksimal) uttrykkes ved følgende ligning M=a1*x1+a2*x2, der x1 og x2 er modellvariablene (antall bokser og kasser), a1 og a2 er vektene deres. I en lineær modell må begrensningene også være lineære funksjoner til variablene. I vårt tilfelle er volumbegrensningen V=b1*x1+b2*x2 også uttrykt ved en lineær avhengighet. En annen begrensning - Maksimalt antall beholdere (n) - er selvsagt også lineær x1+x2 Lineære problemer løses vanligvis ved hjelp av Simplex-metoden. Ved å velge denne løsningsmetoden i vinduet Å finne en løsning Du kan også sjekke selve modellen for linearitet. Når det gjelder en ikke-lineær modell, vil du motta følgende melding:

I dette tilfellet er det nødvendig å velge en metode for å løse det ikke-lineære problemet. Eksempler på ikke-lineære avhengigheter: V=b1*x1*x1; V=b1*x1^0,9; V=b1*x1*x2, der x er en variabel og V er en objektiv funksjon.

Knapper Legg til, Rediger, Slett
Disse knappene lar deg legge til, redigere og slette modellbegrensninger.

Tilbakestill-knapp
For å fjerne alle innstillinger Å finne en løsning klikk på knappen Tilbakestill– dialogboksen vil tømmes.

Dette alternativet er praktisk når du bruker forskjellige restriksjonsalternativer. Når du lagrer modellparametere (knapp Last inn/lagre, klikk deretter på knappen Spare) Det foreslås å velge den øverste cellen i området (kolonnen) som skal plasseres i: en lenke til målfunksjonen, lenker til celler med variabler, begrensninger og parametere for løsningsmetoder (tilgjengelig via knappen Alternativer). Før du lagrer, sørg for at dette området ikke inneholder modelldata.
For å laste inn lagrede parametere, trykk først på knappen Last inn/lagre, deretter, i dialogboksen som vises, knappen Last ned, spesifiser deretter celleområdet som inneholder de tidligere lagrede innstillingene (du kan ikke spesifisere bare den øverste cellen). Klikk OK. Bekreft tilbakestilling av gjeldende verdier for oppgaveparametrene og erstatt dem med nye.

Nøyaktighet
Når man lager en modell, har forskeren i utgangspunktet et estimat av variasjonsområdene til målfunksjonen og variablene. Når man tar hensyn til beregninger i MS EXCEL, anbefales det at disse variasjonsområdene er betydelig høyere enn beregningsnøyaktigheten (den er vanligvis satt fra 0,001 til 0,000001). Som regel er dataene i modellen normalisert slik at variasjonsområdene til objektivfunksjonen og variablene er i området 0,1 - 100 000 Selvfølgelig avhenger alt av den spesifikke modellen, men om variablene dine endres med mer enn 5-6 størrelsesordener, så bør du kanskje "grå" modellen, for eksempel ved å bruke logaritmeoperasjonen.

Optimalisering av Excel-regnearkverdier som oppfyller visse kriterier kan være en kompleks prosess. Heldigvis tilbyr Microsoft et tillegg Problemløsning for numerisk optimalisering. Selv om denne tjenesten ikke kan løse alle problemer, kan den være et nyttig verktøy hva-hvis. Dette innlegget er dedikert til tillegget Problemløsning i Excel.

Overbygg Problemløsning tilgjengelig i alle versjoner av Excel. Vær oppmerksom på at skjermbildene kanskje ikke gjenspeiler din versjon. Selv om noen funksjoner kan endre plassering avhengig av tilleggsversjonen, forblir funksjonaliteten praktisk talt uendret.

Hva er Search for Solutions

Finne løsninger er et Excel-tillegg som hjelper deg med å finne en løsning ved å endre verdiene til målcellene. Målet kan være å minimere, maksimere eller oppnå en målverdi. Problemet løses ved å justere inputkriterier eller brukerdefinerte begrensninger.

Hvor finner du løsninger i Excel

Overbygg Finne løsninger leveres med Excel, men er deaktivert som standard. For å aktivere det, gå til fanen Fil til gruppen Alternativer. I dialogboksen som vises Parametere velge Tillegg -> Administrasjon: TilleggExcel -> Gå. I vinduet Tillegg merk av i boksen ved siden av feltet Å finne en løsning klikk OK.

Nå i fanen Data en ny gruppe har dukket opp Analyse med knapp Å finne en løsning.

Eksempel på bruk av løsningssøk

Dette innlegget er basert på en use case . Filen er kompatibel med alle versjoner av Excel.

Problemdefinisjon

La oss anta at vi har et datasett som består av 8 elementer, som hver har sin egen verdi.

... og vi må kombinere verdiene i to grupper slik at summene av verdiene til disse gruppene omtrentlig sammenfaller.

Først må du identifisere hvert element for en gruppe.

For å indikere at et element er knyttet til en gruppe, vil vi merke dem med en (1), ellers med null (0).

I neste kolonne vil vi oppsummere verdiene til hvert element i gruppen, og deretter oppsummere på slutten av kolonnen.

Vi må også behandle verdien av hver vare i hver gruppe for å gjøre dette, multipliserer vi vareverdien med gruppeverdien som tilsvarer denne varen.

Til slutt må vi summere gruppene og jobbe med forskjellene mellom dem.

Vår oppgave er å minimere forskjellen mellom summene av gruppene.

Nå kan vi tildele poeng til hver gruppe ved å manuelt legge inn enheter i kolonne C og D. Excel vil vise forskjellen i gruppesummer i celle G11.

Problemet er at antall mulige kombinasjoner er 2 8, dvs. 256 mulige svar på spørsmålet. Hvis vi bruker 5 sekunder på hver, vil det ta oss 21,3 minutter, forutsatt at vi kan holde tempoet oppe og huske den beste kombinasjonen.

Det er der Å finne en løsning finner søknad.

Finne den optimale løsningen i Excel

For å bruke tjenesten Å finne en løsning vi må definere et sett med krav, regler og begrensninger som gjør at tillegget kan finne det riktige svaret.

Våre regler

Vårt hovedkrav er å minimere forskjellen mellom de to gruppene. I vårt eksempel er det i celle G11 - Gruppe B minus Gruppe A. Vi vil at verdien i celle G11 skal være så liten som mulig, men større enn eller lik 0.

Vi vet også at en gjenstand kan være enten i gruppe A eller gruppe B, og den kan ikke være brøkdel. Så vi har to begrensninger for hvert element:

Først: Verdien av elementet i kolonnen Bunnlinjen må være lik en.

For det andre: Verdiene til elementer i grupper må være heltall.

Vi vet også at det totale antallet elementer er 8, som er en annen begrensning. Vi vil diskutere hvordan du bruker disse begrensningene i neste avsnitt.

Dialogboksen Finn løsning

Denne delen beskriver tilleggsvinduet Å finne en løsning og dens bruk for å definere problemet.

Tøm vindu for løsningssøk

Fullført løsningssøk-vindu

Optimaliser objektivfunksjonen

Dette er målcellen der vi prøver å løse problemet. Målcellen vår er G11 – Group Difference.

Til

Her angir vi hvilke resultater vi ønsker å oppnå fra målfunksjonen.

Vi ønsker at summene til begge grupper stemmer, dvs. slik at forskjellen i summene er 0. Dette kan virke rart, men vi trenger ikke å minimere forskjellen fordi dette vil sette alle elementene i gruppe A, noe som vil føre til at verdien av celle G11 blir mindre enn null.

En annen måte å pålegge en begrensning på er å endre G11 til =ABS(G10-F10). I dette tilfellet kan vi sette markøren til Minimum, som et resultat av å oppnå målfunksjonen.

Men foreløpig vil vi fokusere på formelen = G10-F10 og sett markøren til en verdi lik 0.

Endre variable celler

Modifiserte celler er cellene som tillegget vil prøve å endre for å løse problemet. I vårt tilfelle er dette å binde et element til en bestemt gruppe: $ C$2:$D$9.

Med forbehold om restriksjoner

Begrensninger er regler som begrenser mulige løsninger på et problem.

Vi må legge til noen begrensninger på listen vår:

1. I kolonnen Total hvert element må være lik 1
2. Gruppeelementer må være et heltall
3. Summen av kolonneverdier Total skal være 8

For å pålegge begrensninger, klikk på knappen Legge til

Du kan Endre eller Slett begrensning hvis du gjorde en feil ved å velge en spesifikk begrensning og klikke på de aktuelle knappene i dialogboksen.

Last inn/lagre parametere for løsningssøk

Løsningssøketjenesten lar deg lagre og laste inn tilleggsparametere. Det er en knapp for dette i vinduet. Last inn/lagre. Modellparameterne lagres i området du spesifiserte tidligere. Denne tilnærmingen lar deg raskt konfigurere og endre parametere Å finne en løsning.

Kjører et søk etter den optimale løsningen i Excel

Advarsel!!! Solver-tillegget er et komplekst beregningstillegg, så lagre arbeidsboken før du kjører den.

Før du kjører modellen, må du angi noen flere parametere for å sikre at tjenesten vil fungere riktig. I hoveddialogboksen, sørg for at det er en markør ved siden av feltet Gjør ubegrensede variabler til ikke-negative. Klikk på knappen i samme vindu Alternativer.

To parametere som må endres fra tid til annen:

Begrens nøyaktighet: verdi fra 0 til 1, der jo høyere tall, desto større er begrensningen

Heltallsoptimalitet: viser hvor langt fra et heltall begrensningen har rett til å være.

Kjører modellen

Klikk på knappen for å starte tillegget Finn en løsning i hovedvinduet.

I statuslinjen vil du se en serie statiske data som viser den interne funksjonen til tillegget. De endres vanligvis raskt og er vanskelige å lese. Hvis modellen er kompleks, kan det hende at arbeidet stopper i en stund.

Etter Å finne en løsning vil fullføre arbeidet, viser Excel en dialogboks Løsningssøkeresultater med litt informasjon. Det første du bør være oppmerksom på er inskripsjonen Løsningen ble funnet innenfor tillatt avvik. Hvis en løsning blir funnet, endres arbeidsbokcellene med den foreslåtte løsningen.

Du har nå 4 alternativer å velge mellom:

— Gjenopprett opprinnelige verdier

Kjør rapport

Du kan opprette en rapport ved å velge tilgjengelige fra listen over rapporter. Et nytt ark vil bli opprettet Resultatrapport 1.

Vær oppmerksom på at avhengig av begrensningene du angir, vil ulike rapporter være tilgjengelige.

Lagre skript

Der du må skrive inn navnet på modellscenarioet ditt og klikke på knappen OK.

Alle skript er tilgjengelige i Script Manager, som er i fanen Data i gruppen Arbeide med data -> Hva-hvis-analyse -> Scenariobehandling.

Verden forandrer seg i rasende fart, enten vi liker det eller ikke. Denne sannheten er spesielt godt kjent for PC-brukere. Tross alt programvare endringer og oppdateringer med utrolig hyppighet. Heldigvis lider ikke kontorsuiter av så mye, men det finnes noen unntak. Hva er viktigheten av kontorprogramvarepakker? Enhver kontorapplikasjon er hendig verktøy, opprettet for å jobbe med databaser. Antallet hjelpeelementer i dette systemet blir flere og flere.

Ved hjelp av visualiseringsverktøy som bare dukket opp i nye versjoner av slike programmer, har det blitt mye enklere å jobbe. Takket være det nye søkefilteret har arbeidet akselerert betydelig. Og selve Microsoft Excel 2010 fungerer raskere. Men det ser ut til at kontoransatte nylig mestret vanskelighetene ved å jobbe med Office 2007. Men plutselig var det en presentasjon av Office 2010, som bare ga de uheldige brukerne enda mer problemer. Et eksempel er "søk etter en løsning" i Microsoft Excel 2010.

Dette tillegget er ikke bare nyttig, det lar deg også gjøre arbeidet med regnearkredigereren mye mer produktivt, og lar deg dermed løse et stort antall komplekse problemer. Det er spesielt relevant fra et optimaliseringssynspunkt, som er relevant for mange bedrifter i dag. Men hvorfor Microsoft Excel 2010? Hvis vi snakker spesifikt om Excel i denne versjonen, har det vært betydelige endringer i den. Så det ble for eksempel fikset stort antall feil i formler, på grunn av at det i tidligere versjoner av programmet oppstod feil i beregninger ganske ofte. Men den minste feilberegning kan noen ganger føre til ganske ubehagelige konsekvenser.

Ved å bruke et båndgrensesnitt som lar deg forhåndsvise grafer og diagrammer før du setter dem inn i en tabell, blir det enklere for brukere å forberede komplekse, profesjonelle dokumenter. Også inkludert ny versjon Redaktøren har tatt med slike nye formler som kan være svært nyttige for økonomer og regnskapsførere. Denne omstendigheten understreker Microsofts fokus på bedriftsbrukere. Tatt i betraktning at alle situasjonene beskrevet nedenfor er typiske for dem, så er det ikke noe overraskende i dette.

Hvis du ikke har brukt «søk etter løsning»-tillegget, kan du installere det separat. Hvordan installere den? Dette gjøres ganske enkelt. Hvis du bruker Excel-regnearkredigering 2003 eller eldre, for å utføre denne handlingen må du gå til "Verktøy"-elementet og velge "Tillegg" der. Og hvor skal man lete etter "søk etter løsninger" hvis vi snakker om en mer moderne versjon? Hvis du bruker Excel 2007, kan du finne "søk etter løsning"-knappen i fanen "Data". Hvordan jobbe med dette? Kanskje alle disse forklaringene kan virke litt lange, men dette tillegget fungerer ganske logisk. Du trenger ikke være et datageni for å mestre det. For å fullt ut forstå prinsippet om bruken, la oss vurdere et enkelt eksempel.

Hvordan fungerer "søk etter en løsning" i Excel 2010?

Eksempel: du får i oppgave å dele ut bonuser i en organisasjon. For å forenkle løsningen, la oss anta at du må fordele bonusen mellom alle avdelingsansatte. Premiumbudsjett - 100 000 rubler. Bonusen kan fordeles i forhold til hver enkelt ansatts lønn. Hvor skal jeg begynne? Først av alt må du utvikle en tabell, gå inn i det hele nødvendig informasjon og formeluttrykk. Det totale premiebeløpet vil bli vurdert som resultatet. Det er verdt å vurdere at målcellen (for eksempel C8) er knyttet til delen som vil bli endret (for eksempel E2).

I området C2-C7 kan det være tilleggsformler som du kan beregne bonusbeløpet for hver ansatt med. Etter dette må du starte "søk etter løsning"-tillegget. Deretter angis de nødvendige verdiene i vinduet som åpnes. Spesiell oppmerksomhet bør rettes mot det faktum at utseende vinduer kan variere mye mellom versjoner kontorpakke. Så i denne situasjonen må du finne ut av det på egen hånd. Men det er ingen grunnleggende forskjeller, så å studere vil ikke ta mye tid.

Hvilke alternativer finnes i dialogboksen?

For å gjøre arbeidet ditt enklere, bør du vite om verdiene som vanligvis eksisterer i et bestemt driftsområde. Først av alt, målcellen. Vær oppmerksom på at i motsetning til andre operasjoner, som kan bruke flere datainntastingsfelt, her kan det bare være ett. I tillegg er det verdt å vurdere at det kan være flere optimaliseringsalternativer. Spesiell oppmerksomhet bør rettes mot minimum og størst mulig totalverdi. Vær også oppmerksom på det spesifikke resultatet. Hvis du trenger det siste alternativet, må det foretrukne resultatet spesifiseres nøyaktig i inndatafeltet. Det bør også tas i betraktning at enten individuelle felt eller et område kan fungere som utskiftbare celler. Det er for området programmet tar den endelige verdien ved å sammenligne den med de opprinnelige dataene.

Hvordan legges restriksjoner til?

Hvis du trenger å legge til noen begrensninger i programmet, må du bruke "Legg til"-knappen. Det er viktig å vurdere følgende punkt: når du angir slike verdier, må du være ekstremt forsiktig. Siden "løsningssøk"-tillegget i Excel brukes i ganske viktige operasjoner, er det viktig å oppnå de mest korrekte verdiene som et resultat. Resultatene i seg selv vil avhenge av restriksjonene. Du kan angi begrensninger både for individuelle celler og for hele områder.

Hvilke varianter av formler og symboler kan brukes i dette tilfellet? Følgende tegn kan brukes: =, >=,<=. Также допускаются формулы «Цел», «Бин» и «Раз». Важно учитывать, что последний вариант допускает использование различных значений. Это доступно в версиях Exel 2010 и выше. В данных пакетах офисного программного обеспечения надстройка «поиск решения» в Exel выполняется намного быстрее и качественнее. Если речь идет о расчете премии, то в данном случае коэффициент может быть только положительным. Для задания данного параметра можно использовать несколько методов. Чтобы легко выполнить данную операцию, необходимо использовать кнопку «Добавить». Также можно выставить флажок «Сделать переменные без ограничений неотрицательными».

Hvor finner du dette alternativet i eldre versjoner av programmet? Hvis du bruker Excel 2007 og eldre, kan du få tilgang til dette alternativet ved å klikke på "Alternativer"-knappen. Her kan du se elementet "Løsningssøkealternativer".

Søk etter et ferdig resultat

For å søke etter en ferdig løsning, må du klikke på "Kjør" -knappen. Som et resultat vises dialogboksen Løsningssøkeresultater. Hvis du er fornøyd med det endelige svaret, trenger du bare å klikke på "Ok"-knappen. Som et resultat vil svaret du liker, bli registrert i tabellen. I tilfelle den resulterende verdien ikke stemmer overens med ditt synspunkt, må du klikke på "Avbryt"-knappen. Tabellen vil etter hvert gå tilbake til sin opprinnelige verdi. Du kan fortsette å søke etter den optimale løsningen. Hvis du endret kildedataene, må denne avgjørelsen tas på nytt.

Hvor kan tillegget "løsningssøk" brukes i Excel?

Tenk på et annet eksempel - kostnadsminimering. Som nevnt ovenfor kan denne funksjonen brukes til å optimalisere produksjonsprosessene. La oss se hvordan vi kan redusere kostnadene til et selskap som driver med lavbygg. La oss anta at vi har en organisasjon selv og tre leverandører som leverer byggevarer. Byggekostnader vil være inkludert i kostnaden for anlegget, så det er i selskapets interesse å velge en leverandør som vil koste mindre.

Hvilken informasjon må legges inn i "løsningssøk" i MS Excel? Det er nødvendig å angi kostnadene for byggematerialer, behovet for dem på byggeplassen og kostnadene ved transport av byggematerialer. Hvert "leverandør-kjøper"-par må tas i betraktning. Målcellen skal angi summen av alle transportkostnader. Hvis alt er gjort riktig, vil funksjonen "løsningssøk" gjøre det mulig å lage den mest lønnsomme strategien som gir høyest mulig inntekt.

Microsoft Excel-tillegget "Solution Search" er et kraftig verktøy for å finne løsninger og brukes til å løse optimaliseringsproblemer. Løsningssøkeprosedyren lar deg finne den optimale verdien av formelen i cellen, som kalles målcellen. Prosedyren fungerer på en gruppe celler som er direkte eller indirekte relatert til en formel i målcellen.

Kort beskrivelse av tillegget

For å oppnå et gitt resultat ved hjelp av en formel, endrer prosedyren verdiene i de påvirkende cellene. Verdibegrensninger brukes for å redusere rekkevidden av verdier som brukes i modellen. Solution Finder-tillegget er et standard Microsoft Office Excel-tillegg og er tilgjengelig umiddelbart når du installerer Microsoft Office generelt eller Microsoft Excel spesielt.

Hvordan installerer jeg tillegget?

Finn løsning-tillegget kan installeres på to måter. Standard tillegg som "Solution Search" og "Analysis Package" er installert sammen med MS Office eller MS Excel. Hvis standardtillegget ikke ble installert under den første installasjonen, må du kjøre installasjonsprosessen på nytt. La oss se på installasjonen av "Search for Solution"-tillegget med Microsoft Office 2010 som eksempel. I versjon 2003 og 2007 gjøres alt på samme måte.

Så start installasjonsdisken med MS Office 2010-applikasjonspakken og velg alternativet "Legg til eller fjern komponenter".

Klikk deretter på "Fortsett"-knappen, finn Microsoft Excel-applikasjonen i installasjonsparameterne, finn delen "Add-Ins" i komponentene til denne applikasjonen, velg "Solution Search"-tillegget og sett "Kjør fra min datamaskin"-alternativet.

Igjen, klikk på "Fortsett"-knappen og vent til tillegget er installert.

Hvordan koble til tillegget?

Før bruk må du først aktivere tillegget ved å merke av i boksen foran navnet i listen over tilgjengelige tillegg i dialogboksen Tillegg.

Å påkalle dette vinduet varierer litt avhengig av versjonen av programmet. Dette er skrevet i detalj i en egen artikkel " Hvordan installerer jeg tillegget for Excel 2003/2007/2010? " med skjermbilder for hver av de tre versjonene av Excel-applikasjonen, så jeg vil ikke gjenta det. Ja, jeg vil bare legge til noen få ord om den andre metoden for å installere dette tillegget. Du kan finne en fil på Internett med navnet Solver.xla(dette er "Search for a solution"-tillegget) og installer det i samsvar med beskrivelsen i lenken ovenfor.

Gjennomgå