Sådan løses en ligning i Excel. At finde en MS EXCEL-løsning

Solver Excel-tilføjelsen er et analytisk værktøj, der giver os mulighed for hurtigt og nemt at bestemme, hvornår og hvilket resultat vi får under visse forhold. Løsningssøgningsværktøjets muligheder er meget højere end hvad "parametervalg" i Excel kan give.

De vigtigste forskelle mellem at finde en løsning og at vælge en parameter:

Valg af flere parametre i Excel.
At pålægge betingelser for at begrænse ændringer i celler, der indeholder variable værdier.
Mulighed for anvendelse i tilfælde, hvor der kan være mange løsninger på ét problem.

Eksempler og problemer til at finde løsninger i Excel

Lad os se på tilføjelsesprogrammets analytiske muligheder. For eksempel skal du spare 14.000 $ på 10 år. I 10 år vil du sætte $1.000 ind på en bankkonto hvert år med 5% om året. Nedenstående figur indeholder en tabel i Excel, som tydeligt viser saldoen på akkumulerede midler for hvert år. Som det ses, vil målet under sådanne betingelser med indlånskonto og opsparingsbidrag ikke være nået selv efter 10 år. Når du løser dette problem, kan du gå på to måder:

Find en bank, der tilbyder en højere rente på indlån.
Forøg mængden af årlige opsparingsbidrag til din bankkonto.

Vi kan ændre variabelværdierne i cellerne B1 og B2 for at vælge de nødvendige betingelser for at akkumulere den nødvendige mængde penge.

Tilføjelsen "Solution Search" giver os mulighed for samtidig at bruge 2 af disse muligheder til hurtigt at simulere de mest optimale betingelser for at nå målet. Sådan gør du:

Som du kan se, øgede programmet lidt renten og mængden af årlige bidrag.

Begrænsende parametre ved søgning efter løsninger

Lad os sige, at du går til banken med dette bord, men banken nægter at hæve din rente. I sådanne tilfælde skal vi finde ud af, hvor meget vi skal øge mængden af årlige investeringer. Vi skal indstille en cellebegrænsning med én variabelværdi. Men før du starter, skal du ændre værdierne i de variable celler til de originale: i B1 med 5% og i B2 med -$1000. Lad os nu gøre følgende.

I denne artikel lærer du hvordan løse andengradsligningen iExcel på et konkret eksempel. Lad os analysere i detaljer løsningen på et simpelt problem med billeder.

Beslutningens forløb

Lad os starte Microsoft Office Excel. Jeg bruger 2007-versionen. Lad os først kombinere cellerne A1:A5 og skrive andengradsligningsformlen i dem på formen ax2+bx+c=0. Dernæst skal vi kvadratisk x, for at gøre dette skal vi gøre tallet 2 til et hævet skrift. Vælg de to og højreklik.

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

I celle A2 indtaster vi henholdsvis tekstværdien a=, i celle A3 b= og i celle A4 c=. Disse værdier vil blive indtastet fra tastaturet i de følgende celler (B2,B3,B4).

Lad os indtaste tekst for de værdier, der vil blive beregnet. I celle C2 d=, C3 x 1 = C4 x 2 =. Vi vil lave sublineær afstand for x svarende til hævet mellemrum i x 2

Lad os gå videre til at indtaste formler for løsningen

Diskriminanten af et kvadratisk trinomium er b 2 -4ac

I celle D2 skal du indtaste den passende formel for at hæve et tal til anden potens:

En andengradsligning har to rødder, hvis diskriminanten er større end nul. Indtast formlen for x 1 i celle C3

IF(D2>0;(-B3+ROOT(D2))/(2*B2);"Ingen rødder")

For at beregne x2 introducerer vi en lignende formel, men med et plustegn

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

Følgelig, med de indtastede værdier a, b, c, beregnes diskriminanten først, hvis dens værdier er mindre end nul, vises meddelelsen "Ingen rødder", ellers får vi værdierne x 1 og x 2.

Beskyttelse af et ark i Excel

Vi skal beskytte arket, som vi lavede beregningerne på. Uden beskyttelse skal du forlade celler, hvor du kan indtaste værdierne a, b, c, det vil sige celler B2 B3 B4. For at gøre dette skal du vælge dette område og gå til celleformatet, gå til fanen Anmeldelser, Beskyt ark og fjerne markeringen i feltet Beskyttet celle. Klik på OK for at bekræfte ændringerne.

Dette celleområde vil ikke være beskyttet, når regnearket er beskyttet. Lad os beskytte arket for at gøre dette ved at gå til fanen Gennemse og vælge Arkbeskyttelse. Lad os indtaste adgangskoden 1234. Klik på OK.

Nu kan vi ændre værdierne af celler B2, B3, B4. Når vi forsøger at ændre andre celler, vil vi modtage følgende besked: "Cellen eller diagrammet er beskyttet mod ændringer. Og også råd om fjernelse af beskyttelse.

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

Excel har en lang række værktøjer til at løse forskellige typer ligninger ved hjælp af forskellige metoder.

Lad os se på nogle løsninger ved hjælp af eksempler.

Løsning af ligninger ved at vælge Excel-parametre

Værktøjet Parametervalg bruges i en situation, hvor resultatet er kendt, men argumenterne er ukendte. Excel justerer værdierne, indtil beregningen giver den ønskede total.

Sti til kommandoen: "Data" - "Arbejde med data" - "Hvad hvis-analyse" - "Parametervalg".

Lad os se på eksemplet med løsning af andengradsligningen x 2 + 3x + 2 = 0. Fremgangsmåden for at finde roden ved hjælp af Excel:

Programmet bruger en cyklisk proces til at vælge en parameter. For at ændre antallet af iterationer og fejl, skal du gå til Excel-indstillingerne. På fanen "Formler" skal du indstille det maksimale antal iterationer og relativ fejl. Marker afkrydsningsfeltet "aktiver iterative beregninger".

Sådan løses et ligningssystem ved hjælp af matrixmetoden i Excel

Ligningssystemet er givet:

Ligningernes rødder fås.

Løsning af et ligningssystem ved hjælp af Cramer-metoden i Excel

Lad os tage ligningssystemet fra det foregående eksempel:

For at løse dem ved hjælp af Cramers metode, beregner vi determinanterne for de opnåede matricer ved at erstatte en søjle i matrix A med søjle-matrix B.

For at beregne determinanterne bruger vi funktionen MOPRED. Argumentet er et interval med den tilsvarende matrix.

Lad os også beregne determinanten for matrix A (array - område af matrix A).

Systemets determinant er større end 0 - løsningen kan findes ved hjælp af Cramers formel (D x / |A|).

For at beregne X 1: =U2/$U$1, hvor U2 – D1. For at beregne X 2: =U3/$U$1. Osv. Lad os få rødderne til ligningerne:

Løsning af ligningssystemer ved hjælp af Gauss-metoden i Excel

Lad os for eksempel tage det enkleste ligningssystem:

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

Vi skriver koefficienterne i matrix A. Frie led - i matrix B.

For klarhedens skyld fremhæver vi de gratis vilkår ved at udfylde. Hvis den første celle i matrix A indeholder 0, skal du bytte rækkerne, så en anden værdi end 0 vises her.

Eksempler på løsning af ligninger ved hjælp af iterationsmetoden i Excel

Beregningerne i projektmappen skal opstilles som følger:

Dette gøres på fanen "Formler" i "Excel-indstillinger". Lad os finde roden af ligningen x – x 3 + 1 = 0 (a = 1, b = 2) ved iteration ved hjælp af cykliske referencer. Formel:

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

M – maksimal værdi af modulo-afledte. For at finde M, lad os lave beregningerne:

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

Den resulterende værdi er mindre end 0. Derfor vil funktionen have det modsatte fortegn: f (x) = -x + x 3 – 1. M = 11.

I celle A3 indtaster vi værdien: a = 1. Nøjagtighed – tre decimaler. For at beregne den aktuelle værdi af x i den tilstødende celle (B3), skal du indtaste formlen: =IF(B3=0;A3;B3-(-B3+POWER(B3;3)-1/11)).

Lad os i celle C3 kontrollere værdien af f (x): ved at bruge formlen =B3-POWER(B3,3)+1.

Roden af ligningen er 1,179. Lad os indtaste værdien 2 i celle A3. Vi får det samme resultat:

Der er kun én rod på et givet interval.

Der er mange problemer, som kan være væsentligt nemmere at løse ved hjælp af Solution Finder-værktøjet. Men for at gøre dette skal du starte med at organisere arbejdsarket efter en model, der er egnet til at finde løsninger, hvilket kræver en god forståelse af sammenhængen mellem variable og formler. Selvom formuleringen af problemet normalt udgør den største vanskelighed, er den tid og indsats, der bruges på at udarbejde modellen, fuldt ud berettiget, da de opnåede resultater kan beskytte mod unødvendigt spild af ressourcer, i tilfælde af forkert planlægning, hjælpe med at øge overskuddet gennem optimal økonomistyring eller identificere det bedste forhold mellem produktionsmængder, varebeholdninger og produktnavne.

Bag din essens optimeringsproblem er en matematisk model af en bestemt produktproduktionsproces, dens distribution, opbevaring, forarbejdning, transport, køb eller salg, udførelse af en række tjenester osv. Dette er et almindeligt matematisk problem af typen Given/Find/Condition, men som har mange mulige løsninger. Således er optimeringsproblemet opgaven med at vælge den bedste, optimale fra et sæt af mulige muligheder. Løsningen på et sådant problem kaldes plan eller program, for eksempel, siger de - en produktionsplan eller et genopbygningsprogram. Det er med andre ord de ubekendte, som vi skal finde, for eksempel mængden af produktion, der vil give den maksimale profit. Optimeringsproblemet er søgningen efter et ekstremum, det vil sige maksimum- eller minimumværdien af en bestemt funktion, som kaldes målfunktion Det kan for eksempel være en overskudsfunktion – omsætning minus omkostninger. Da alt i verden er begrænset (tid, penge, naturlige og menneskelige ressourcer), har optimeringsproblemer altid visse restriktioner for eksempel mængden af metal, arbejdere og maskiner i en virksomhed til fremstilling af dele. Det følgende er et eksempel på designet af et meget simpelt optimeringsproblem, men med dets hjælp kan du nemt forstå organiseringen af at konstruere en tabel for effektiviteten af løsninger på praktiske optimeringsproblemer.

Vi har et klassisk problem, når en virksomhed producerer to typer produkter (produkt A og produkt B) til en bestemt pris, deres produktion kræver 4 typer ressourcer (ressource 1, ressource 2, ressource 3, ressource 4), som er tilgængelige på virksomheden i en bestemt mængde (Inventory), er der også information om, hvor meget af hver ressource, der skal til for at producere en output-enhed, henholdsvis produkt A og produkt B. Vi skal finde mængden af produkt A og produkt B, der maksimerer indkomsten (omsætningen) (se figur).

Dernæst skal vi lave relationer mellem begrænsningerne, planen og den objektive funktion. For at gøre dette bygger vi en ekstra kolonne (Brugt), hvor vi indtaster formlen SUMPRODUKT(Norm; Plan). Normen er prisen på en bestemt ressource til produktion af en produktionsenhed af varer A og B, og planen er mængden af produktion, som vi leder efter. Indtast formlen i indkomstcellerne SUMPRODUKT(Pris; Plan). Således udfyldte vi kolonnen Brugt og cellen Indkomst med formler. Da planen er de variabler, som mængden af brugte ressourcer og indkomst afhænger af, afhænger cellerne med formler direkte af de data, der vises der som et resultat af at søge efter løsninger. Ud fra ovenstående kan vi drage følgende konklusioner om, at hvert optimeringsproblem skal have tre komponenter:

ukendt(det vi leder efter, det vil sige en plan);

begrænsning for ukendte (søgeområde);

objektiv funktion(det mål, som vi leder efter et ekstremum for).

Kraftfuldt dataanalyseværktøj Excel er en overbygning Løser (Søg efter en løsning). Med dens hjælp kan du bestemme, ved hvilke værdier af de angivne påvirkende celler formlen i målcellen får den ønskede værdi (minimum, maksimum eller lig med en eller anden værdi). Du kan indstille begrænsninger for løsningssøgningsproceduren, og det er ikke nødvendigt, at de samme påvirkende celler bruges. For at beregne en given værdi anvendes forskellige matematiske søgemetoder. Du kan indstille en tilstand, hvor de opnåede variabelværdier automatisk indtastes i tabellen. Derudover kan resultaterne af programmet præsenteres i form af en rapport. Search for Solutions-programmet (i den originale Excel Solver) er en ekstra tilføjelse til MS Excel-regnearksprocessoren, som er designet til at løse visse ligningssystemer, lineære og ikke-lineære optimeringsproblemer, der er blevet brugt siden 1991. Størrelsen af det problem, der kan løses ved hjælp af den grundlæggende version af dette program, er begrænset af følgende grænser:

antal ubekendte (beslutningsvariabel) – 200;

antal formelmæssige begrænsninger på ukendte – 100;

antallet af begrænsende betingelser (simpel begrænsning) for ukendte er 400.

Udvikleren af Solver-programmet, Frontline System, har længe specialiseret sig i at udvikle kraftfulde og bekvemme optimeringsmetoder indbygget i miljøet af populære regnearksprocessorer fra forskellige producenter (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3). Den høje effektivitet af deres brug forklares af integrationen af optimeringsprogrammet og regnearkets forretningsdokument. Takket være den verdensomspændende popularitet af MS Excel-regnearksprocessoren er Solver-programmet indbygget i dets miljø det mest almindelige værktøj til at finde optimale løsninger i moderne forretning. Som standard er tilføjelsesprogrammet Find løsning deaktiveret i Excel. For at aktivere den i Excel 2007, klik på ikonet Microsoft Office-knap, klik Excel-indstillinger og vælg derefter en kategori Tilføjelser. I marken Kontrollere vælg værdi Excel-tilføjelser og tryk på knappen Gå. I marken Tilgængelige tilføjelser marker afkrydsningsfeltet ud for varen At finde en løsning og tryk på knappen OK.

I Excel 2003 og vælg kommandoen nedenfor Service/tillæg , i dialogboksen Tilføjelser, der vises, skal du markere afkrydsningsfeltet At finde en løsning og klik på OK-knappen. Hvis der så vises en dialogboks, hvor du bliver bedt om at bekræfte dine hensigter, skal du klikke på Ja. (Du skal muligvis have en Office-installations-cd.)

Løsningssøgningsprocedure 1. Opret en tabel med formler, der etablerer relationer mellem celler.

2. Vælg den målcelle, der skal have den ønskede værdi, og vælg kommandoen: - In Excel 2007 Data/Analyse/At finde en løsning;

I Excel 2003 og nedenfor Værktøjer > Løser (Værktøjer > Søg efter en løsning). Feltet Indstil målcelle i dialogboksen Solver-tilføjelse, der åbnes, vil indeholde adressen på målcellen. 3. Indstil Equal To switches for at indstille værdien af målcellen til Max (maksimum værdi), Min (minimum værdi) eller Værdi af (værdi). I sidstnævnte tilfælde skal du indtaste værdien i feltet til højre. 4. Angiv i feltet Ved at ændre celler, hvilke celler programmet skal ændre værdier i søgen efter det optimale resultat. 5. Opret begrænsninger på listen Subject to the Constraints. For at gøre dette skal du klikke på knappen Tilføj og definere begrænsningen i dialogboksen Tilføj begrænsning.

6. Klik på knappen på knappen Indstillinger, og i vinduet, der vises, vælg alternativknappen Ikke-negative værdier (hvis variablerne skal være positive tal), Lineær model (hvis problemet du løser vedrører lineær modeller)

7. Klik på knappen Løser for at starte løsningssøgningsprocessen.

8. Når dialogboksen Løserresultater vises, skal du vælge alternativknappen Keep Solve Solution eller Restore Original Values. 9. Klik på OK.

Løsningsværktøjsindstillinger Maksimal tid- tjener til at begrænse den tid, der er afsat til at søge efter en løsning på et problem. I dette felt kan du indtaste en tid i sekunder op til 32.767 (ca. ni timer); Standardværdien på 100 er fin til de fleste simple opgaver.

Begræns antallet af iterationer- styrer tiden for løsning af et problem ved at begrænse antallet af beregningscyklusser (iterationer). Relativ fejl- bestemmer nøjagtigheden af beregninger. Jo lavere værdien af denne parameter er, jo højere er nøjagtigheden af beregningerne. Tolerance- er beregnet til at indstille tolerancen for afvigelse fra den optimale løsning, hvis værdisættet for den påvirkende celle er begrænset af et sæt heltal. Jo større toleranceværdien er, jo mindre tid tager det at finde en løsning. Konvergens- gælder kun for ikke-lineære problemer. Når den relative værdiændring i målcellen over de sidste fem iterationer bliver mindre end det tal, der er angivet i feltet Konvergens, stopper søgningen. Lineær model- tjener til at fremskynde søgningen efter en løsning ved at anvende en lineær model på optimeringsproblemet. Ikke-lineære modeller involverer brugen af ikke-lineære funktioner, en vækstfaktor og eksponentiel udjævning, som bremser beregningerne. Ikke-negative værdier- giver dig mulighed for at angive en nedre grænse på nul for de påvirkende celler, for hvilke den tilsvarende grænse ikke er angivet i dialogboksen Tilføj begrænsning. Automatisk skalering- bruges, når tallene i de celler, der ændres, og i målcellen er væsentligt forskellige. Vis iterationsresultater- pauser søgningen efter en løsning for at se resultaterne af individuelle iterationer. Download model- efter at du har klikket på denne knap, åbnes en dialogboks med samme navn, hvor du kan indtaste et link til det celleområde, der indeholder optimeringsmodellen. Gem model- tjener til at vise en dialogboks med samme navn på skærmen, hvor du kan indtaste et link til det celleområde, der er beregnet til lagring af optimeringsmodellen. Lineær evaluering- vælg denne kontakt for at arbejde med en lineær model. Kvadratisk skøn- vælg denne kontakt for at arbejde med en ikke-lineær model. Direkte forskelle- bruges i de fleste problemer, hvor hastigheden af ændring af begrænsninger er relativt lav. Øger hastigheden af Solution Search-værktøjet. Centrale forskelle- bruges til funktioner, der har en diskontinuerlig afledt. Denne metode kræver flere beregninger, men brugen kan være berettiget, hvis der udsendes besked om, at det ikke er muligt at opnå en mere præcis løsning. Newtons søgemetode - kræver mere hukommelse, men udfører færre iterationer end den konjugerede gradientmetode. Metode til at finde konjugerede gradienter- implementerer den konjugerede gradientmetode, som kræver mindre hukommelse, men udfører flere iterationer end Newtons metode. Denne metode bør bruges, hvis problemet er stort nok til at spare hukommelse, eller hvis iterationer giver for lidt forskel i successive tilnærmelser.

Løsning af ikke-lineære ligninger og systemer"

Formålet med arbejdet: Undersøgelse af Ms Excel 2007-pakkens muligheder ved løsning af ikke-lineære ligninger og systemer. At erhverve færdigheder i at løse ikke-lineære ligninger og systemer ved hjælp af pakken.

Opgave 1. Find rødderne af polynomiet x 3 - 0,01x 2 - 0,7044x + 0,139104 = 0.

Lad os først løse ligningen grafisk. Det er kendt, at den grafiske løsning af ligningen f(x)=0 er skæringspunktet for grafen for funktionen f(x) med abscisseaksen, dvs. værdien af x, hvor funktionen forsvinder.

Lad os tabulere vores polynomium på intervallet fra -1 til 1 med et trin på 0,2. Beregningsresultaterne er vist i fig., hvor formlen blev indtastet i celle B2: = A2^3 - 0,01*A2^2 - 0,7044*A2 + 0,139104. Grafen viser, at funktionen skærer Ox-aksen tre gange, og da et tredjegradspolynomium ikke har mere end tre reelle rødder, er der fundet en grafisk løsning på problemet. Rødderne var med andre ord lokaliseret, dvs. intervallerne, hvor rødderne af dette polynomium er placeret, bestemmes: [-1,-0,8] og .

Nu kan du finde rødderne af et polynomium ved hjælp af metoden med successive tilnærmelser ved hjælp af kommandoen Data → Arbejde med data → Hvad-hvis-analyse → Parametervalg.

Efter indtastning af de første tilnærmelser og funktionsværdier kan du bruge kommandoen Data → Arbejde med data → Hvad-hvis-analyse → Parametervalg og udfyld dialogboksen som følger.

I marken Indstil til celle der gives et link til den celle, hvori der indtastes en formel, der beregner værdien af venstre side af ligningen (ligningen skal skrives, så dens højre side ikke indeholder en variabel). I marken Mening indtast højre side af ligningen, og i feltet Ændring af celleværdier der gives et link til den celle, der er allokeret til variablen. Bemærk, at indtastning af cellereferencer i felterne i dialogboksen Valg af parametre Det er mere praktisk ikke fra tastaturet, men ved at klikke på den tilsvarende celle.

Efter at have klikket på knappen OK, vises dialogboksen Resultat af parametervalg med en meddelelse om vellykket afslutning af søgningen efter en løsning, den omtrentlige værdi af roden vil blive placeret i celle A14.

Vi finder de resterende to rødder på samme måde. Beregningsresultaterne vil blive placeret i cellerne A15 og A16.

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

Lad os lokalisere rødderne til den ikke-lineære ligning.

For at gøre dette, lad os repræsentere 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øsning af ligningen f(x) = g(x) vil være skæringspunktet for linjerne f(x) og g(x).

Lad os bygge grafer af f(x) og g(x). For at gøre dette indtaster vi argumentværdierne i området A3:A18. I celle B3 indtaster vi en formel til beregning af værdierne af funktionen f(x): = EXP(A3), og i C3 til beregning af g(x): = (2*A3-1)^2.

Beregningsresultater og plotning af f(x) og g(x):

Grafen viser, at linjerne f(x) og g(x) skærer hinanden to gange, dvs. Denne ligning har to løsninger. En af dem er triviel og kan beregnes nøjagtigt:

For det andet kan du bestemme rodisolationsintervallet: 1,5< x < 2.

Nu kan du finde roden af ligningen på et segment ved hjælp af metoden med successive tilnærmelser.

Lad os indtaste den indledende tilnærmelse i celle H17 = 1,5, og selve ligningen, med reference til den indledende tilnærmelse, i celle I17 = EXP(H17) - (2*H17-1)^2.

og udfyld dialogboksen Parametervalg.

Resultatet af søgningen efter en løsning vil blive vist i celle H17.

Øvelse3 . Løs ligningssystemet:

Før vi bruger de ovenfor beskrevne metoder til løsning af ligningssystemer, lad os finde en grafisk løsning til dette system. Bemærk, at begge systemets ligninger er specificeret implicit, og for at konstruere grafer af funktioner svarende til disse ligninger, er det nødvendigt at løse de givne ligninger med hensyn til variablen y.

Til den første ligning af systemet har vi:

Lad os finde ud af OD for den resulterende funktion:

Den anden ligning i dette system beskriver en cirkel.

Et fragment af et MS Excel regneark med formler, der skal indtastes i celler for at konstruere linjer beskrevet af systemets ligninger. Skæringspunkterne for de viste linjer er en grafisk løsning på et system af ikke-lineære ligninger.

Det er ikke svært at bemærke, at det givne system har to løsninger. Derfor skal proceduren for at finde løsninger til systemet udføres to gange efter forudgående at have bestemt intervallet for rodisolering langs Ox- og Oy-akserne. I vores tilfælde ligger den første rod i intervallerne (-0,5;0) x og (0,5;1) y, og den anden - (0;0,5) x og (-0,5;-1) y. Dernæst vil vi fortsætte som følger. Lad os introducere startværdierne af variablerne x og y, formler, der repræsenterer systemligningerne og målfunktionen.