Hvordan løse en ligning i Excel. Finne en MS EXCEL-løsning

Solver Excel-tillegget er et analytisk verktøy som lar oss raskt og enkelt bestemme når og hvilket resultat vi vil få under visse forhold. Mulighetene til løsningssøkeverktøyet er mye høyere enn hva "parametervalg" i Excel kan gi.

De viktigste forskjellene mellom å finne en løsning og velge en parameter:

Velge flere parametere i Excel.
Pålegge betingelser for å begrense endringer i celler som inneholder variable verdier.
Mulighet for bruk i tilfeller hvor det kan være mange løsninger på ett problem.

Eksempler og problemer for å finne løsninger i Excel

La oss se på de analytiske egenskapene til tillegget. For eksempel må du spare $14 000 på 10 år. I 10 år vil du sette $1000 inn på en bankkonto hvert år med 5% per år. Figuren nedenfor inneholder en tabell i Excel, som tydelig viser balansen av akkumulerte midler for hvert år. Som du kan se, under slike forhold med innskuddskonto og sparebidrag, vil ikke målet nås selv etter 10 år. Når du løser dette problemet, kan du gå på to måter:

Finn en bank som tilbyr høyere rente på innskudd.
Øk beløpet for årlige sparebidrag til bankkontoen din.

Vi kan endre variabelverdiene i cellene B1 og B2 for å velge de nødvendige betingelsene for å samle den nødvendige mengden penger.

«Solution Search»-tillegget lar oss samtidig bruke 2 av disse alternativene for raskt å simulere de mest optimale forholdene for å nå målet. For dette:

Som du kan se, økte programmet litt renten og mengden av årlige bidrag.

Begrensende parametere ved søk etter løsninger

La oss si at du går til banken med dette bordet, men banken nekter å øke renten din. I slike tilfeller må vi finne ut hvor mye vi vil ha for å øke mengden av årlige investeringer. Vi må sette en cellebegrensning med én variabelverdi. Men før du begynner, endre verdiene i variabelcellene til de opprinnelige: i B1 med 5%, og i B2 med -$1000. La oss nå gjøre følgende.

I denne artikkelen lærer du hvordan løse andregradsligningen iutmerke på et spesifikt eksempel. La oss analysere i detalj løsningen på et enkelt problem med bilder.

Fremdrift av beslutningen

La oss starte Microsoft Office Excel. Jeg bruker 2007-versjonen. La oss først kombinere cellene A1:A5 og skrive den kvadratiske formelen i dem på formen ax2+bx+c=0. Deretter må vi kvadratisk x, for å gjøre dette må vi gjøre tallet 2 til et hevet skrift. Velg de to og høyreklikk.

Vi får en formel på formen ax 2 +bx+c=0

I celle A2 skriver vi inn tekstverdien a=, i henholdsvis celle A3 b= og i celle A4 c=. Disse verdiene vil bli lagt inn fra tastaturet i følgende celler (B2,B3,B4).

La oss skrive inn tekst for verdiene som skal beregnes. I celle C2 d=, C3 x 1 = C4 x 2 =. La oss gjøre den sublineære avstanden for x lik avstanden hevet i x 2

La oss gå videre til å legge inn formler for løsningen

Diskriminanten til et kvadratisk trinomium er b 2 -4ac

I celle D2 skriver du inn den riktige formelen for å heve et tall til andre potens:

En andregradsligning har to røtter hvis diskriminanten er større enn null. I celle C3 skriver du inn formelen for x 1

HVIS(D2>0;(-B3+ROOT(D2))/(2*B2);”Ingen røtter”)

For å beregne x2 introduserer vi en lignende formel, men med et plusstegn

HVIS(D2>0;(-B3-ROOT(D2))/(2*B2);"Ingen røtter")

Følgelig, med de angitte verdiene a, b, c, beregnes først diskriminanten, hvis verdiene er mindre enn null, vises meldingen "Ingen røtter", ellers får vi verdiene x 1 og x 2.

Beskytte et ark i Excel

Vi må beskytte arket som vi gjorde beregningene på. Uten beskyttelse må du forlate celler der du kan legge inn verdier a, b, c, det vil si cellene B2 B3 B4. For å gjøre dette, velg dette området og gå til celleformatet, gå til fanen Anmeldelser, Beskytt ark og fjern merket for Beskyttet celle. Klikk OK for å bekrefte endringene som er gjort.

Dette celleområdet vil ikke være beskyttet når regnearket er beskyttet. La oss beskytte arket for å gjøre dette, gå til Review-fanen og velg Arkbeskyttelse. La oss skrive inn passordet 1234. Klikk OK.

Nå kan vi endre verdiene til cellene B2, B3, B4. Når vi prøver å endre andre celler, vil vi motta følgende melding: «Cellen eller diagrammet er beskyttet mot endringer. Og også råd om fjerning av beskyttelse.

Du kan også være interessert i materialet om hvordan du sikrer det.

Excel har et bredt utvalg av verktøy for å løse ulike typer ligninger ved hjelp av ulike metoder.

La oss se på noen løsninger ved å bruke eksempler.

Løse ligninger ved å velge Excel-parametere

Parametervalgverktøyet brukes i en situasjon der resultatet er kjent, men argumentene er ukjente. Excel justerer verdiene til beregningen gir ønsket total.

Sti til kommandoen: "Data" - "Arbeid med data" - "Hva hvis-analyse" - "Parametervalg".

La oss se på eksempelet på å løse den kvadratiske ligningen x 2 + 3x + 2 = 0. Prosedyren for å finne roten ved hjelp av Excel:

Programmet bruker en syklisk prosess for å velge en parameter. For å endre antall iterasjoner og feil, må du gå til Excel-alternativene. På "Formler"-fanen angir du maksimalt antall iterasjoner og relativ feil. Merk av for "aktiver iterative beregninger".

Hvordan løse et ligningssystem ved hjelp av matrisemetoden i Excel

Ligningssystemet er gitt:

Røttene til ligningene oppnås.

Løse et ligningssystem ved hjelp av Cramer-metoden i Excel

La oss ta ligningssystemet fra forrige eksempel:

For å løse dem ved hjelp av Cramers metode, beregner vi determinantene til matrisene oppnådd ved å erstatte en kolonne i matrise A med kolonnematrisen B.

For å beregne determinantene bruker vi MOPRED-funksjonen. Argumentet er et område med den tilsvarende matrisen.

La oss også beregne determinanten til matrise A (matrise - rekkevidde av matrise A).

Determinanten til systemet er større enn 0 – løsningen kan finnes ved å bruke Cramers formel (D x / |A|).

For å beregne X 1: =U2/$U$1, hvor U2 – D1. For å beregne X 2: =U3/$U$1. Etc. La oss finne røttene til ligningene:

Løse ligningssystemer ved hjelp av Gauss-metoden i Excel

La oss for eksempel ta det enkleste likningssystemet:

3a + 2b – 5c = -1
2a – b – 3c = 13
a + 2b – c = 9

Vi skriver koeffisientene i matrise A. Frie termer - i matrise B.

For klarhets skyld fremhever vi gratisvilkårene ved å fylle ut. Hvis den første cellen i matrise A inneholder 0, må du bytte radene slik at en annen verdi enn 0 vises her.

Eksempler på å løse ligninger ved hjelp av iterasjonsmetoden i Excel

Beregningene i arbeidsboken bør settes opp som følger:

Dette gjøres på "Formler"-fanen i "Excel-alternativer". La oss finne roten til ligningen x – x 3 + 1 = 0 (a = 1, b = 2) ved iterasjon ved hjelp av sykliske referanser. Formel:

Х n+1 = X n – F (X n) / M, n = 0, 1, 2, … .

M – maksimal verdi av modulo-deriverten. For å finne M, la oss utføre følgende beregninger:

f' (1) = -2 * f' (2) = -11.

Den resulterende verdien er mindre enn 0. Derfor vil funksjonen ha motsatt fortegn: f (x) = -x + x 3 – 1. M = 11.

I celle A3 legger vi inn verdien: a = 1. Nøyaktighet – tre desimaler. For å beregne gjeldende verdi av x i den tilstøtende cellen (B3), skriv inn formelen: =IF(B3=0;A3;B3-(-B3+POWER(B3;3)-1/11)).

I celle C3, la oss kontrollere verdien av f (x): ved å bruke formelen =B3-POWER(B3,3)+1.

Roten til ligningen er 1,179. La oss legge inn verdien 2 i celle A3. Vi får samme resultat:

Det er bare én rot på et gitt intervall.

Det er mange problemer som kan være betydelig enklere å løse ved å bruke Solution Finder-verktøyet. Men for å gjøre dette må du starte med å organisere regnearket etter en modell som er egnet for å finne løsninger, noe som krever god forståelse av sammenhengene mellom variabler og formler. Selv om formuleringen av problemet vanligvis utgjør hovedvanskeligheten, er tiden og kreftene brukt på å utarbeide modellen fullt ut berettiget, siden de oppnådde resultatene kan beskytte mot unødvendig sløsing med ressurser, i tilfelle feil planlegging, bidra til å øke fortjenesten gjennom optimal økonomistyring eller identifisere det beste forholdet mellom produksjonsvolumer, varelager og produktnavn.

Bak essensen din optimaliseringsproblem er en matematisk modell av en bestemt prosess for produktproduksjon, distribusjon, lagring, prosessering, transport, kjøp eller salg, ytelse av en rekke tjenester, etc. Dette er et vanlig matematisk problem av typen Gitt/Finn/Betingelse, men som har mange mulige løsninger. Derfor er optimeringsproblemet oppgaven med å velge den beste, optimale fra et sett med mulige alternativer. Løsningen på et slikt problem kalles plan eller program, for eksempel, sier de - en produksjonsplan eller et gjenoppbyggingsprogram. Dette er med andre ord de ukjente som vi trenger for å finne, for eksempel hvor mye produksjon som vil gi maksimal fortjeneste. Optimaliseringsproblemet er søket etter et ekstremum, det vil si maksimums- eller minimumsverdien til en bestemt funksjon, som kalles målfunksjon Dette kan for eksempel være en profittfunksjon – inntekter minus kostnader. Siden alt i verden er begrenset (tid, penger, naturressurser og menneskelige ressurser), har optimaliseringsproblemer alltid visse begrensninger, for eksempel mengden metall, arbeidere og maskiner i et deleproduksjonsanlegg. Følgende er et eksempel på utformingen av et veldig enkelt optimaliseringsproblem, men med dets hjelp kan du enkelt forstå organiseringen av å konstruere en tabell for effektiviteten av løsninger på praktiske optimaliseringsproblemer.

Vi har et klassisk problem når en bedrift produserer to typer produkter (produkt A og produkt B) til en bestemt pris, deres produksjon krever 4 typer ressurser (ressurs 1, ressurs 2, ressurs 3, ressurs 4), som er tilgjengelig på bedriften i en viss mengde (Inventory), er det også informasjon om hvor mye av hver ressurs som trengs for å produsere en produksjonsenhet, henholdsvis produkt A og produkt B. Vi må finne mengden av produkt A og produkt B som maksimerer inntekt (inntekt) (se figur).

Deretter må vi lage relasjoner mellom begrensninger, plan og objektiv funksjon. For å gjøre dette bygger vi en ekstra kolonne (Brukt), der vi skriver inn formelen SUMPRODUKT(Norm; Plan). Normen er kostnaden for en viss ressurs for å produsere en produksjonsenhet av varer A og B, og Planen er mengden produksjon vi ser etter. Skriv inn formelen i inntektscellene SUMPRODUKT(Pris; Plan). Dermed fylte vi ut Brukt-kolonnen og Inntektscellen med formler. Siden planen er variablene som mengden ressursbruk og inntekt avhenger av, avhenger cellene med formler direkte av dataene som dukker opp der som et resultat av å søke etter løsninger. Fra ovenstående kan vi trekke følgende konklusjoner om at hvert optimaliseringsproblem må ha tre komponenter:

ukjent(det vi ser etter, det vil si en plan);

begrensning for ukjente (søkeområde);

objektiv funksjon(målet som vi leter etter et ekstremum for).

Kraftig dataanalyseverktøy utmerke er et tillegg Løser (Søk etter en løsning). Med dens hjelp kan du bestemme hvilke verdier av de spesifiserte påvirkende cellene formelen i målcellen får ønsket verdi (minimum, maksimum eller lik en verdi). Du kan angi begrensninger for prosedyren for løsningssøk, og det er ikke nødvendig at de samme påvirkende cellene brukes. For å beregne en gitt verdi brukes ulike matematiske søkemetoder. Du kan angi en modus der de oppnådde variabelverdiene automatisk legges inn i tabellen. I tillegg kan resultatene av programmet presenteres i form av en rapport. Søk etter løsninger-programmet (i den originale Excel Solver) er et tillegg for MS Excel-regnearkprosessoren, som er designet for å løse visse ligningssystemer, lineære og ikke-lineære optimaliseringsproblemer, og har blitt brukt siden 1991. Størrelsen på problemet som kan løses ved å bruke den grunnleggende versjonen av dette programmet er begrenset av følgende grenser:

antall ukjente (beslutningsvariabel) – 200;

antall formeliske begrensninger på ukjente – 100;

antall begrensende betingelser (enkel begrensning) for ukjente er 400.

Utvikleren av Solver-programmet, Frontline System, har lenge spesialisert seg på å utvikle kraftige og praktiske optimaliseringsmetoder innebygd i miljøet til populære regnearkprosessorer fra ulike produsenter (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3). Den høye effektiviteten av bruken deres forklares av integrasjonen av optimaliseringsprogrammet og regnearkets forretningsdokument. Takket være den verdensomspennende populariteten til MS Excel-regnearkprosessoren, er Solver-programmet innebygd i miljøet det vanligste verktøyet for å finne optimale løsninger i moderne virksomhet. Som standard er Finn løsning-tillegget deaktivert i Excel. For å aktivere den i Excel 2007, klikk på ikonet Microsoft Office-knapp, klikk Excel-alternativer og velg deretter en kategori Tillegg. I felt Kontroll velg verdi Excel-tillegg og trykk på knappen Gå. I felt Tilgjengelige tillegg merk av i boksen ved siden av elementet Å finne en løsning og trykk på knappen OK.

I Excel 2003 og velg kommandoen nedenfor Service/Tillegg , i tilleggsdialogboksen som vises, merk av i avmerkingsboksen Å finne en løsning og klikk på OK-knappen. Hvis det da vises en dialogboks som ber deg bekrefte intensjonene dine, klikker du Ja. (Det kan hende du trenger en Office-installasjons-CD.)

Løsningssøkeprosedyre 1. Lag en tabell med formler som etablerer relasjoner mellom celler.

2. Velg målcellen som skal få den nødvendige verdien og velg kommandoen: - In Excel 2007 Dataanalyse/Å finne en løsning;

I Excel 2003 og under Verktøy > Løser (Verktøy > Søk etter en løsning). Angi målcelle-feltet i dialogboksen Solver-tillegg som åpnes, vil inneholde adressen til målcellen. 3. Sett Equal To-bryterne for å sette verdien til målcellen til Maks (maksimal verdi), Min (minimumsverdi) eller Verdi av (verdi). I sistnevnte tilfelle skriver du inn verdien i feltet til høyre. 4. Spesifiser i feltet By Changing Cells hvilke celler programmet skal endre verdier for å finne det optimale resultatet. 5. Opprett begrensninger i listen Subject to the Constraints. For å gjøre dette, klikk på Legg til-knappen og definer begrensningen i dialogboksen Legg til begrensning.

6. Klikk på knappen på Alternativer-knappen, og i vinduet som vises, velg alternativknappen Ikke-negative verdier (hvis variablene må være positive tall), Lineær modell (hvis problemet du løser gjelder lineær) modeller)

7. Klikk på Løser-knappen for å starte løsningssøkeprosessen.

8. Når dialogboksen Løserresultater vises, velger du alternativknappen Keep Solve Solution eller Gjenopprett opprinnelige verdier. 9. Klikk OK.

Alternativer for løsningsverktøy Maksimal tid- tjener til å begrense tiden som er tildelt for å søke etter en løsning på et problem. I dette feltet kan du angi en tid i sekunder opptil 32 767 (omtrent ni timer); Standardverdien på 100 er bra for de fleste enkle oppgaver.

Begrens antall iterasjoner- kontrollerer tiden som kreves for å løse et problem ved å begrense antall beregningssykluser (iterasjoner). Relativ feil- bestemmer nøyaktigheten av beregninger. Jo lavere verdien av denne parameteren er, desto høyere er nøyaktigheten av beregningene. Toleranse- er ment å sette toleransen for avvik fra den optimale løsningen hvis settet med verdier til den påvirkende cellen er begrenset av et sett med heltall. Jo større toleranseverdien er, jo mindre tid tar det å finne en løsning. Konvergens- gjelder kun for ikke-lineære problemer. Når den relative verdiendringen i målcellen over de siste fem iterasjonene blir mindre enn tallet som er angitt i Konvergens-feltet, stopper søket. Lineær modell- tjener til å fremskynde søket etter en løsning ved å bruke en lineær modell på optimaliseringsproblemet. Ikke-lineære modeller involverer bruk av ikke-lineære funksjoner, en vekstfaktor og eksponentiell utjevning, som bremser beregningene. Ikke-negative verdier- lar deg sette en null nedre grense for de påvirkende cellene som den tilsvarende grensen ikke ble satt for i dialogboksen Legg til begrensning. Automatisk skalering- brukes når tallene i cellene som endres og i målcellen er vesentlig forskjellige. Vis iterasjonsresultater- pauser søket etter en løsning for å se resultatene av individuelle iterasjoner. Last ned modell- etter å ha klikket på denne knappen, åpnes en dialogboks med samme navn, der du kan legge inn en lenke til celleområdet som inneholder optimaliseringsmodellen. Lagre modell- tjener til å vise en dialogboks med samme navn på skjermen, der du kan legge inn en lenke til celleområdet beregnet for lagring av optimaliseringsmodellen. Lineær evaluering- velg denne bryteren for å jobbe med en lineær modell. Kvadratisk estimat- velg denne bryteren for å fungere med en ikke-lineær modell. Direkte forskjeller- brukes i de fleste problemer der endringshastigheten av begrensninger er relativt lav. Øker hastigheten til Solution Search-verktøyet. Sentrale forskjeller- brukes for funksjoner som har en diskontinuerlig derivert. Denne metoden krever flere beregninger, men bruken kan være berettiget dersom det gis melding om at det ikke er mulig å få en mer nøyaktig løsning. Newtons søkemetode - krever mer minne, men utfører færre iterasjoner enn konjugert gradientmetoden. Metode for å finne konjugerte gradienter- implementerer konjugert gradientmetoden, som krever mindre minne, men utfører flere iterasjoner enn Newtons metode. Denne metoden bør brukes hvis problemet er stort nok til å spare minne, eller hvis iterasjoner gir for liten forskjell i påfølgende tilnærminger.

Løse ikke-lineære ligninger og systemer"

Målet med arbeidet: Studerer mulighetene til Ms Excel 2007-pakken ved løsning av ikke-lineære ligninger og systemer. Tilegne seg ferdigheter i å løse ikke-lineære ligninger og systemer ved hjelp av pakken.

Øvelse 1. Finn røttene til polynomet x 3 - 0,01x 2 - 0,7044x + 0,139104 = 0.

Først, la oss løse ligningen grafisk. Det er kjent at den grafiske løsningen av ligningen f(x)=0 er skjæringspunktet for grafen til funksjonen f(x) med abscisseaksen, dvs. verdien av x som funksjonen forsvinner ved.

La oss tabulere polynomet vårt på intervallet fra -1 til 1 med et trinn på 0,2. Beregningsresultatene er vist i fig., hvor formelen ble lagt inn i celle B2: = A2^3 - 0,01*A2^2 - 0,7044*A2 + 0,139104. Grafen viser at funksjonen skjærer okseaksen tre ganger, og siden et tredjegrads polynom ikke har mer enn tre reelle røtter, har man funnet en grafisk løsning på problemet. Røttene var med andre ord lokalisert, d.v.s. intervallene som røttene til dette polynomet befinner seg i, bestemmes: [-1,-0,8], og .

Nå kan du finne røttene til et polynom ved å bruke metoden for suksessive tilnærminger ved å bruke kommandoen Data→ Arbeide med data→ Hva-hvis-analyse → Parametervalg.

Etter å ha lagt inn de første tilnærmingene og funksjonsverdiene, kan du bruke kommandoen Data → Arbeide med data → Hva-hvis-analyse → Parametervalg og fyll ut dialogboksen som følger.

I felt Sett til celle det gis en lenke til cellen der det legges inn en formel som beregner verdien av venstre side av ligningen (ligningen må skrives slik at dens høyre side ikke inneholder en variabel). I felt Betydning skriv inn høyre side av ligningen, og i feltet Endre celleverdier en kobling gis til cellen som er tildelt for variabelen. Merk at å legge inn cellereferanser i feltene i dialogboksen Valg av parametere Det er mer praktisk ikke fra tastaturet, men ved å klikke på den tilsvarende cellen.

Etter å ha klikket på OK-knappen, vil dialogboksen Resultat av parametervalg vises med en melding om vellykket fullføring av søket etter en løsning, den omtrentlige verdien av roten vil bli plassert i celle A14.

Vi finner de resterende to røttene på samme måte. Beregningsresultatene vil bli plassert i cellene A15 og A16.

Oppgave 2. Løs ligning e x - (2x - 1) 2 = 0.

La oss lokalisere røttene til den ikke-lineære ligningen.

For å gjøre dette, la oss representere det i formen f(x) = g(x), dvs. e x = (2x - 1) 2 eller f(x) = e x , g(x) = (2x - 1) 2 , og løs grafisk.

Den grafiske løsningen til ligningen f(x) = g(x) vil være skjæringspunktet mellom linjene f(x) og g(x).

La oss bygge grafer av f(x) og g(x). For å gjøre dette, legger vi inn argumentverdiene i området A3:A18. I celle B3 legger vi inn en formel for å beregne verdiene til funksjonen f(x): = EXP(A3), og i C3 for å beregne g(x): = (2*A3-1)^2.

Beregningsresultater og plotting av f(x) og g(x):

Grafen viser at linjene f(x) og g(x) skjærer hverandre to ganger, dvs. Denne ligningen har to løsninger. En av dem er triviell og kan beregnes nøyaktig:

For det andre kan du bestemme rotisolasjonsintervallet: 1,5< x < 2.

Nå kan du finne roten til ligningen på et segment ved å bruke metoden for suksessive tilnærminger.

La oss legge inn den første tilnærmingen i celle H17 = 1,5, og selve ligningen, med referanse til den innledende tilnærmingen, i celle I17 = EXP(H17) - (2*H17-1)^2.

og fyll ut dialogboksen Parametervalg.

Resultatet av å søke etter en løsning vil vises i celle H17.

Trening3 . Løs ligningssystemet:

Før du bruker metodene beskrevet ovenfor for å løse ligningssystemer, la oss finne en grafisk løsning på dette systemet. Merk at begge likningene i systemet er spesifisert implisitt, og for å konstruere grafer av funksjoner som tilsvarer disse likningene, er det nødvendig å løse de gitte likningene med hensyn til variabelen y.

For den første ligningen av systemet har vi:

La oss finne ut OD til den resulterende funksjonen:

Den andre ligningen i dette systemet beskriver en sirkel.

Et fragment av et MS Excel-regneark med formler som må legges inn i celler for å konstruere linjer beskrevet av systemets ligninger. Skjæringspunktene til de viste linjene er en grafisk løsning på et system med ikke-lineære ligninger.

Det er ikke vanskelig å legge merke til at det gitte systemet har to løsninger. Derfor må prosedyren for å finne løsninger på systemet utføres to ganger, etter å ha bestemt intervallet for rotisolasjon langs Ox- og Oy-aksene. I vårt tilfelle ligger den første roten i intervallene (-0,5;0) x og (0,5;1) y, og den andre - (0;0,5) x og (-0,5;-1) y. Deretter fortsetter vi som følger. La oss introdusere startverdiene til variablene x og y, formler som representerer systemligningene og målfunksjonen.