Waar is de trendlijn in Excel. Prognosetools in Microsoft Excel

Theoretische informatie

In de praktijk wordt bij het modelleren van verschillende processen - in het bijzonder economische, fysieke, technische, sociale - een of andere methode voor het berekenen van geschatte waarden van functies op basis van hun bekende waarden op bepaalde vaste punten op grote schaal gebruikt.

Dit soort functie-benaderingsproblemen doet zich vaak voor:

• bij het construeren van benaderende formules voor het berekenen van de waarden van karakteristieke grootheden van het onderzochte proces met behulp van tabelgegevens verkregen als resultaat van het experiment;
• in numerieke integratie, differentiatie, het oplossen van differentiaalvergelijkingen, enz.;
• als het nodig is om de waarden van functies op tussenliggende punten van het beschouwde interval te berekenen;
• bij het bepalen van de waarden van karakteristieke grootheden van een proces buiten het beschouwde interval, in het bijzonder bij het voorspellen.

Dit artikel bespreekt de mogelijkheden van het MS Excel-pakket voor het oplossen van dit soort problemen. Daarnaast biedt het methoden en technieken voor het construeren (creëren) van regressies voor tabelfuncties (wat de basis vormt van regressieanalyse).

Excel heeft twee opties voor het bouwen van regressies.

1. Het toevoegen van geselecteerde regressies (trendlijnen) aan een diagram dat is opgebouwd op basis van een gegevenstabel voor het onderzochte proceskenmerk (alleen beschikbaar als er een diagram is gemaakt);
2. Met behulp van de ingebouwde statistische functies van het Excel-werkblad, zodat u regressies (trendlijnen) rechtstreeks uit de brongegevenstabel kunt verkrijgen.

Trendlijnen aan een diagram toevoegen

Voor een tabel met gegevens die een proces beschrijft en wordt weergegeven door een diagram, beschikt Excel over een effectief hulpmiddel voor regressieanalyse waarmee u:

• bouwen op basis van de kleinste kwadratenmethode en vijf soorten regressies aan het diagram toevoegen, die het onderzochte proces met verschillende mate van nauwkeurigheid modelleren;
• voeg de geconstrueerde regressievergelijking toe aan het diagram;
• bepaal de mate van overeenstemming van de geselecteerde regressie met de gegevens die in de grafiek worden weergegeven.

y = y(x)

waarbij x een onafhankelijke variabele is die vaak de waarden aanneemt van een reeks natuurlijke getallen (1; 2; 3; ...) en bijvoorbeeld een aftelling produceert van de tijd van het onderzochte proces (kenmerken).

1 . Lineaire regressie is goed voor het modelleren van kenmerken waarvan de waarden met een constante snelheid stijgen of dalen. Dit is het eenvoudigste model om te construeren voor het onderzochte proces. Zij

y = mx + b

waarbij m de raaklijn is van de lineaire regressiehelling aan de x-as; b - coördinaat van het snijpunt van lineaire regressie met de ordinaatas.

2 . Een polynomiale trendlijn is nuttig voor het beschrijven van kenmerken die verschillende afzonderlijke uitersten hebben (maxima en minima). De keuze van de polynomiale graad wordt bepaald door het aantal extremen van het onderzochte kenmerk. Een polynoom van de tweede graad kan dus heel goed een proces beschrijven dat slechts één maximum of minimum heeft; polynoom van de derde graad - niet meer dan twee extremen; polynoom van de vierde graad - niet meer dan drie extremen, enz.

In dit geval wordt de trendlijn geconstrueerd in overeenstemming met de vergelijking:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

waarbij de coëfficiënten c0, c1, c2,... c6 constanten zijn waarvan de waarden tijdens de constructie worden bepaald.

3 . De logaritmische trendlijn wordt met succes gebruikt bij het modelleren van kenmerken waarvan de waarden aanvankelijk snel veranderen en vervolgens geleidelijk stabiliseren.

Gebouwd in overeenstemming met de vergelijking:

y = c ln(x) + b

4 . Een trendlijn volgens de machtswet geeft goede resultaten als de waarden van de onderzochte relatie worden gekenmerkt door een constante verandering in de groeisnelheid. Een voorbeeld van een dergelijke afhankelijkheid is de grafiek van de uniform versnelde beweging van een auto. Als er nul- of negatieve waarden in de gegevens voorkomen, kunt u geen machtstrendlijn gebruiken.

Gebouwd in overeenstemming met de vergelijking:

y = cxb

waarbij coëfficiënten b, c constanten zijn.

5 . Er moet een exponentiële trendlijn worden gebruikt wanneer de snelheid waarmee de gegevens veranderen voortdurend toeneemt. Voor gegevens die nul of negatieve waarden bevatten, is dit type benadering ook niet van toepassing.

Gebouwd in overeenstemming met de vergelijking:

y = c ebx

waarbij coëfficiënten b, c constanten zijn.

Bij het selecteren van een trendlijn berekent Excel automatisch de waarde van R2, wat de betrouwbaarheid van de benadering kenmerkt: hoe dichter de R2-waarde bij de eenheid ligt, des te betrouwbaarder benadert de trendlijn het onderzochte proces. Indien nodig kan de R2-waarde altijd op de kaart worden weergegeven.

Bepaald door de formule:

Een trendlijn aan een gegevensreeks toevoegen:

• activeer een diagram op basis van een reeks gegevens, d.w.z. klik binnen het diagramgebied. Het Diagram-item verschijnt in het hoofdmenu;
• nadat u op dit item heeft geklikt, verschijnt er een menu op het scherm waarin u het commando Trendlijn toevoegen selecteert.

Dezelfde acties kunnen eenvoudig worden geïmplementeerd door de muisaanwijzer over de grafiek te bewegen die overeenkomt met een van de gegevensreeksen en met de rechtermuisknop te klikken; In het contextmenu dat verschijnt, selecteert u de opdracht Trendlijn toevoegen. Het dialoogvenster Trendlijn verschijnt op het scherm met het tabblad Type geopend (Fig. 1).

Hierna heb je nodig:

Selecteer het gewenste trendlijntype op het tabblad Type (standaard is het lineaire type geselecteerd). Geef voor het type Polynoom in het veld Graad de graad van de geselecteerde polynoom op.

Indien nodig kunt u, door naar het tabblad Parameters (Fig. 2) te gaan, de volgende parameters voor de trendlijn instellen:

• wijzig de naam van de trendlijn in het veld Naam van de benaderende (afgevlakte) curve.
• stel het aantal perioden (vooruit of achteruit) in voor de prognose in het veld Prognose;
• geef de vergelijking van de trendlijn weer in het grafiekgebied, waarvoor u het selectievakje “toon vergelijking op grafiek” moet inschakelen;
• geef de geschatte betrouwbaarheidswaarde R2 weer in het diagramgebied, waarvoor u het selectievakje Plaats de geschatte betrouwbaarheidswaarde in het diagram (R^2) moet inschakelen;
• stel het snijpunt van de trendlijn met de Y-as in, waarvoor u het selectievakje voor het snijpunt van de curve met de Y-as op een punt moet inschakelen;
• Klik op de knop OK om het dialoogvenster te sluiten.

Het dialoogvenster Trendlijnopmaak verschijnt op het scherm (Fig. 3) en bevat drie tabbladen: Weergave, Type, Parameters, en de inhoud van de laatste twee komt volledig overeen met de vergelijkbare tabbladen van het dialoogvenster Trendlijn (Fig. 1). -2). Op het tabblad Weergave kunt u het lijntype, de kleur en de dikte instellen.

Om een ​​trendlijn te verwijderen die al is getekend, selecteert u de trendlijn die u wilt verwijderen en drukt u op de Delete-toets.

De voordelen van de weloverwogen regressieanalysetool zijn:

• het relatieve gemak van het construeren van een trendlijn in grafieken zonder er een gegevenstabel voor te maken;
• een vrij brede lijst met soorten voorgestelde trendlijnen, en deze lijst bevat de meest gebruikte soorten regressie;
• het vermogen om het gedrag van het onderzochte proces te voorspellen door een willekeurig (binnen de grenzen van het gezond verstand) aantal stappen vooruit en ook achteruit;
• het vermogen om de trendlijnvergelijking in analytische vorm te verkrijgen;
• de mogelijkheid om, indien nodig, een beoordeling te verkrijgen van de betrouwbaarheid van de benadering.

U kunt geen trendlijnen toevoegen aan gegevensreeksen in 3D-, genormaliseerde-, radar-, cirkel- en ringdiagrammen.

De ingebouwde functies van Excel gebruiken

Excel heeft ook een regressieanalysetool voor het uitzetten van trendlijnen buiten het grafiekgebied. Er zijn een aantal statistische werkbladfuncties die voor dit doel kunnen worden gebruikt, maar deze maken allemaal alleen lineaire of exponentiële regressies mogelijk.

Excel heeft verschillende functies voor het construeren van lineaire regressie, met name:

• TREND;
• LIJNSCH;
• HELLING en SNIJDEN.

Evenals verschillende functies voor het construeren van een exponentiële trendlijn, in het bijzonder:

• HOOGTE;
• LGRFPRIBL.

De voordelen van de ingebouwde functietool voor regressieanalyse zijn:

• een vrij eenvoudig, uniform proces voor het genereren van gegevensreeksen van het onderzochte kenmerk voor alle ingebouwde statistische functies die trendlijnen definiëren;
• standaardmethodologie voor het construeren van trendlijnen op basis van gegenereerde gegevensreeksen;
• het vermogen om het gedrag van het onderzochte proces te voorspellen door het vereiste aantal stappen vooruit of achteruit.

Opgemerkt moet worden dat het niet de bedoeling van de auteurs was om het verloop van de regressieanalyse met enige mate van volledigheid weer te geven. De belangrijkste taak ervan is om aan de hand van specifieke voorbeelden de mogelijkheden van het Excel-pakket te laten zien bij het oplossen van benaderingsproblemen; demonstreren welke effectieve tools Excel heeft voor het bouwen van regressies en prognoses; illustreren hoe dergelijke problemen relatief eenvoudig kunnen worden opgelost, zelfs door een gebruiker die geen uitgebreide kennis van regressieanalyse heeft.

Voorbeelden van het oplossen van specifieke problemen

Laten we eens kijken naar het oplossen van specifieke problemen met behulp van de genoemde Excel-hulpmiddelen.

Probleem 1

Met een tabel met gegevens over de winst van een autotransportbedrijf voor de periode 1995-2002. je moet het volgende doen:

1. Bouw een diagram.
2. Voeg lineaire en polynomiale (kwadratische en kubieke) trendlijnen toe aan het diagram.
3. Gebruik de vergelijkingen van de trendlijnen om tabelgegevens te verkrijgen over de bedrijfswinsten voor elke trendlijn voor de periode 1995-2004.
4. Maak een prognose van de winst van de onderneming voor 2003 en 2004.

Probleem oplossing

1. Voer in het celbereik A4:C11 van het Excel-werkblad het werkblad in dat wordt weergegeven in Afb. 4.
2. Nadat we het cellenbereik B4:C11 hebben geselecteerd, bouwen we een diagram.
3. We activeren het geconstrueerde diagram en voegen, volgens de hierboven beschreven methode, na het selecteren van het type trendlijn in het dialoogvenster Trendlijn (zie figuur 1), afwisselend lineaire, kwadratische en kubieke trendlijnen toe aan het diagram. Open in hetzelfde dialoogvenster het tabblad Parameters (zie figuur 2), voer in het veld Naam van de benaderende (afgevlakte) curve de naam in van de trend die wordt toegevoegd, en stel in het veld Voorspelling voor: perioden de waarde in waarde 2, aangezien het de bedoeling is om een ​​winstvoorspelling voor twee jaar vooruit te maken. Om de regressievergelijking en de betrouwbaarheidswaarde R2 van de benadering in het diagramgebied weer te geven, schakelt u de selectievakjes Vergelijking weergeven op het scherm in en plaatst u de betrouwbaarheidswaarde van de benadering (R^2) in het diagram. Voor een betere visuele waarneming veranderen we het type, de kleur en de dikte van de geconstrueerde trendlijnen, waarvoor we het tabblad Weergave van het dialoogvenster Trendlijnopmaak gebruiken (zie figuur 3). Het resulterende diagram met toegevoegde trendlijnen wordt getoond in Fig. 5.
4. Om tabelgegevens te verkrijgen over de bedrijfswinsten voor elke trendlijn voor 1995-2004. Laten we de trendlijnvergelijkingen gebruiken die in Fig. 5. Voer hiervoor in de cellen van het bereik D3:F3 tekstinformatie in over het type van de geselecteerde trendlijn: Lineaire trend, Kwadratische trend, Kubieke trend. Voer vervolgens de lineaire regressieformule in cel D4 in en kopieer deze formule met behulp van de vulmarkering met relatieve verwijzingen naar het celbereik D5:D13. Opgemerkt moet worden dat elke cel met een lineaire regressieformule uit het celbereik D4:D13 als argument een corresponderende cel uit het bereik A4:A13 heeft. Op dezelfde manier vult u voor kwadratische regressie het celbereik E4:E13 in, en voor kubieke regressie het celbereik F4:F13. Zo is een prognose voor de winst van de onderneming voor 2003 en 2004 opgesteld. gebruikmakend van drie trends. De resulterende waardentabel wordt getoond in Fig. 6.

Probleem 2

1. Bouw een diagram.
2. Voeg logaritmische, machts- en exponentiële trendlijnen toe aan de grafiek.
3. Leid de vergelijkingen af ​​van de verkregen trendlijnen, evenals de betrouwbaarheidswaarden van de benadering R2 voor elk ervan.
4. Gebruik de trendlijnvergelijkingen om tabelgegevens te verkrijgen over de winst van de onderneming voor elke trendlijn voor 1995-2002.
5. Maak aan de hand van deze trendlijnen een prognose van de winst van het bedrijf voor 2003 en 2004.

Probleem oplossing

Door de methodologie te volgen die is gegeven bij het oplossen van probleem 1, krijgen we een diagram waaraan logaritmische, machts- en exponentiële trendlijnen zijn toegevoegd (Fig. 7). Vervolgens vullen we met behulp van de verkregen trendlijnvergelijkingen een tabel met waarden voor de winst van de onderneming in, inclusief de voorspelde waarden voor 2003 en 2004. (Afb. 8).

In afb. 5 en afb. Het is duidelijk dat het model met een logaritmische trend overeenkomt met de laagste waarde van de betrouwbaarheid van de benadering

R2 = 0,8659

De hoogste waarden van R2 komen overeen met modellen met een polynomiale trend: kwadratisch (R2 = 0,9263) en kubisch (R2 = 0,933).

Probleem 3

Met de tabel met gegevens over de winst van een autovervoerbedrijf voor de periode 1995-2002, gegeven in taak 1, moet u de volgende stappen uitvoeren.

1. Verkrijg gegevensreeksen voor lineaire en exponentiële trendlijnen met behulp van de TREND- en GROW-functies.
2. Maak met behulp van de functies TREND en GROEI een voorspelling van de winst van de onderneming voor 2003 en 2004.
3. Maak een diagram voor de oorspronkelijke gegevens en de resulterende gegevensreeksen.

Probleem oplossing

Laten we het werkblad voor probleem 1 gebruiken (zie figuur 4). Laten we beginnen met de TREND-functie:

1. selecteer het cellenbereik D4:D11, dat moet worden gevuld met de waarden van de TREND-functie die overeenkomen met de bekende gegevens over de winst van de onderneming;
2. Roep de opdracht Functie op vanuit het menu Invoegen. In het dialoogvenster Functiewizard dat verschijnt, selecteert u de TREND-functie in de categorie Statistisch en klikt u vervolgens op de knop OK. Dezelfde bewerking kan worden uitgevoerd door op de knop (Functie invoegen) op de standaardwerkbalk te klikken.
3. Voer in het dialoogvenster Functieargumenten dat verschijnt het celbereik C4:C11 in het veld Bekende_waarden_y in; in het veld Known_values_x - het celbereik B4:B11;
4. Om van de ingevoerde formule een matrixformule te maken, gebruikt u de toetsencombinatie + + .

Als gevolg hiervan wordt het cellenbereik D4:D11 gevuld met de overeenkomstige waarden van de TREND-functie (Fig. 9).

1. selecteer het cellenbereik D12:D13 waar de waarden die worden voorspeld door de TREND-functie zullen worden ingevoerd.
2. roep de TREND-functie aan en voer in het dialoogvenster Functieargumenten dat verschijnt in het veld Known_values_y het celbereik C4:C11 in; in het veld Known_values_x - het celbereik B4:B11; en in het veld Nieuwe_waarden_x - het celbereik B12:B13.
3. verander deze formule in een matrixformule met de toetsencombinatie Ctrl + Shift + Enter.
4. De ingevoerde formule ziet er als volgt uit: =(TREND(C4:C11;B4:B11;B12:B13)), en het celbereik D12:D13 wordt gevuld met de voorspelde waarden van de TREND-functie (zie Fig. 9).

De gegevensreeks wordt op dezelfde manier ingevuld met behulp van de GROWTH-functie, die wordt gebruikt bij de analyse van niet-lineaire afhankelijkheden en op precies dezelfde manier werkt als zijn lineaire tegenhanger TREND.

Figuur 10 toont de tabel in de formuleweergavemodus.

Voor de initiële gegevens en de verkregen gegevensreeksen wordt het diagram weergegeven in Fig. 11.

Probleem 4

Met de gegevenstabel over de ontvangst van dienstenaanvragen door de expeditiedienst van een motortransportbedrijf voor de periode van de 1e tot de 11e van de lopende maand, moet u de volgende acties uitvoeren.

1. Gegevensreeksen ophalen voor lineaire regressie:gebruik van de SLOPE- en CUT-functies; met behulp van de LIJNSCH-functie.
2. Verkrijg een gegevensreeks voor exponentiële regressie met behulp van de LGRFPRIBL-functie.
3. Maak met behulp van de bovenstaande functies een voorspelling over de ontvangst van aanvragen bij de verzendservice voor de periode van de 12e tot de 14e van de huidige maand.
4. Maak een diagram voor de originele en ontvangen gegevensreeksen.

Probleem oplossing

Merk op dat, in tegenstelling tot de functies TREND en GROEI, geen van de hierboven genoemde functies (HELLING, INTERCEPT, LIJNSCH, LGRFPRIB) regressie is. Deze functies spelen slechts een ondersteunende rol en bepalen de noodzakelijke regressieparameters.

Voor lineaire en exponentiële regressies die zijn opgebouwd met behulp van de functies HELLING, INTERCEPT, LIJNSCH, LGRFPRIB, is het uiterlijk van hun vergelijkingen altijd bekend, in tegenstelling tot lineaire en exponentiële regressies die overeenkomen met de functies TREND en GROEI.

1 . Laten we een lineaire regressie bouwen met de vergelijking:

y = mx+b

Om dit te doen, voeren wij de volgende acties uit:

1. voer de originele tabel in het celbereik A4:B14 in;
2. de waarde van parameter m wordt bepaald in cel C19. Selecteer de functie Helling in de categorie Statistisch; voer het celbereik B4:B14 in het veld bekende_waarden_y in en het celbereik A4:A14 in het veld bekende_waarden_x. De formule wordt ingevoerd in cel C19: =HELLING(B4:B14,A4:A14);
3. Met behulp van een vergelijkbare techniek wordt de waarde van parameter b in cel D19 bepaald. En de inhoud zal er als volgt uitzien: =SEGMENT(B4:B14,A4:A14).De waarden van de parameters m en b die nodig zijn voor het construeren van een lineaire regressie zullen dus respectievelijk worden opgeslagen in de cellen C19, D19;
4. Voer vervolgens de lineaire regressieformule in cel C4 in de vorm in: =$C*A4+$D. In deze formule worden de cellen C19 en D19 geschreven met absolute verwijzingen (het celadres mag niet veranderen tijdens mogelijk kopiëren). Het absolute verwijzingsteken $ kan vanaf het toetsenbord worden getypt of met behulp van de F4-toets, nadat u de cursor op het celadres hebt geplaatst. Kopieer deze formule met behulp van de vulgreep naar het celbereik C4:C17. We verkrijgen de vereiste gegevensreeksen (Fig. 12). Omdat het aantal aanvragen een geheel getal is, moet u het getalformaat met het aantal decimalen op 0 instellen op het tabblad Getal van het venster Celformaat.

y = mx+b

Om dit te doen:

1. Voer de LIJNSCH-functie in als matrixformule in het celbereik C20:D20: =(LIJNSCH(B4:B14,A4:A14)). Als resultaat verkrijgen we de waarde van parameter m in cel C20, en de waarde van parameter b in cel D20;
2. voer de formule in cel D4 in: =$C*A4+$D;
3. kopieer deze formule met behulp van de vulmarkering naar het celbereik D4:D17 en verkrijg de gewenste gegevensreeks.

y = bmx

In afb. Figuur 13 toont een tabel waarin u de functies kunt zien die we gebruiken met de vereiste celbereiken, evenals formules.

Voor de initiële gegevens en de verkregen gegevensreeksen wordt het diagram weergegeven in Fig. 14.

Diagrammen en grafieken worden gebruikt om numerieke gegevens te analyseren, bijvoorbeeld om de relatie tussen twee soorten waarden te evalueren. Voor dit doel kunnen een trendlijn en de bijbehorende vergelijking, voorspelde waarden die voor meerdere perioden vooruit of achteruit zijn berekend, worden toegevoegd aan de gegevens in een diagram of grafiek.

Trendlijn vertegenwoordigt een rechte of gebogen lijn die de oorspronkelijke gegevens benadert (dichterbij brengt) op basis van een regressievergelijking of voortschrijdend gemiddelde. De benadering wordt bepaald met behulp van de kleinste kwadratenmethode. Afhankelijk van de aard van het gedrag van de brongegevens (dalend, stijgend, etc.) wordt een interpolatiemethode geselecteerd die moet worden gebruikt om een ​​trend op te bouwen.

Er zijn verschillende opties om een ​​trendlijn te vormen.

Lineaire functie: y=mx+b

waarbij m de raaklijn is van de hellingshoek van de rechte lijn, b de verplaatsing.

Een rechte trendlijn (lineaire trend) is het meest geschikt voor hoeveelheden die met een constante snelheid veranderen. Wordt gebruikt in gevallen waarin gegevenspunten zich dicht bij een rechte lijn bevinden.

Logaritmische functie: y=c*ln⁡x+b

waarbij c en b constanten zijn.

Een logaritmische trendlijn komt overeen met een gegevensreeks waarvan de waarden aanvankelijk snel stijgen of dalen en vervolgens geleidelijk stabiliseren. Kan worden gebruikt voor positieve en negatieve gegevens.

Polynoomfunctie (tot en met de 6e graad): y= b + c 1 *x + c 2 *x 2 + c 3 *x 3 + ...+ c 6* x 6

waarbij b, c 1, c 2, ... c 6 constanten zijn.

Een polynomiale trendlijn wordt gebruikt om afwisselend stijgende en dalende gegevens te beschrijven. De graad van de polynoom wordt zo gekozen dat deze één groter is dan het aantal extremen (maxima en minima) van de curve.

Vermogensfunctie: y = cxb

waarbij c en b constanten zijn.

Een trendlijn volgens de machtswet geeft goede resultaten voor positieve gegevens met constante versnelling. Voor reeksen met nul- of negatieve waarden is de constructie van de opgegeven trendlijn onmogelijk.

Exponentiële functie: y = cebx

waar c en b constanten zijn, is e de basis van de natuurlijke logaritme.

Exponentiële trend wordt gebruikt wanneer de verandering in gegevens voortdurend toeneemt. Het construeren van de aangegeven trend is onmogelijk als de reeks waarden van de reeksleden nul of negatieve gegevens bevat.

Lineair filteren volgens de formule: F t = (A t +A (t-1) +⋯+A (t-n+1))/n

waarbij n het totale aantal leden van de reeks is, is t het gegeven aantal punten (2 ≤ t< n).

Met een trend met lineaire filtering kunt u gegevensfluctuaties gladstrijken, waardoor de aard van de afhankelijkheden duidelijk wordt aangetoond. Om de opgegeven trendlijn op te bouwen, moet de gebruiker een getal opgeven: een filterparameter. Als het getal 2 is, wordt het eerste punt van de trendlijn gedefinieerd als het gemiddelde van de eerste twee gegevensitems, het tweede punt als het gemiddelde van de tweede en derde gegevensitems, enz.

Voor sommige typen diagrammen kan in principe geen trendlijn worden geconstrueerd: gestapelde diagrammen, volumetrische diagrammen, radardiagrammen, cirkeldiagrammen, oppervlaktediagrammen en ringdiagrammen. Indien mogelijk kunt u meerdere regels met verschillende parameters aan het diagram toevoegen. De overeenkomst van de trendlijn met de werkelijke waarden van de gegevensreeksen wordt vastgesteld met behulp van de betrouwbaarheidscoëfficiënt van de benadering:

De trendlijn en de bijbehorende parameters worden aan de grafiekgegevens toegevoegd met behulp van de volgende opdrachten:

Indien nodig kunt u de lijnparameters wijzigen door op een rij met grafiekgegevens of een trendlijn te klikken om het venster Trendlijnopmaak te openen. U kunt een regressievergelijking en een betrouwbaarheidscoëfficiënt voor de benadering toevoegen (of verwijderen), de richting en voorspelling van veranderingen in een gegevensreeks bepalen en ook de ontwerpelementen van de trendlijn corrigeren. De geselecteerde trendlijn kan ook worden verwijderd.

De figuur toont een tabel met gegevens over veranderingen in de waarde van een effect. Op basis van deze voorwaardelijke gegevens werd een spreidingsdiagram gemaakt, een polynomiale trendlijn van de derde orde (gespecificeerd door een stippellijn) en enkele andere parameters toegevoegd. De verkregen waarde van de betrouwbaarheidscoëfficiënt R2 van de benadering in het diagram ligt dicht bij de eenheid, wat aangeeft hoe dicht de berekende trendlijn bij de probleemgegevens ligt. De voorspelde waarde van veranderingen in de waarde van een effect is gericht op groei.

en een tabel als deze:

Instructies. Geef de hoeveelheid gegevens op (aantal rijen). De resulterende oplossing wordt opgeslagen in een Word- en Excel-bestand.

De trend van een tijdreeks karakteriseert een reeks factoren die een langetermijninvloed hebben en de algehele dynamiek vormen van de indicator die wordt bestudeerd.

Methode voor het tellen van de tijd vanaf een voorwaardelijke start

Voorbeeld. Statistische studie van bevolkingsdynamiek.

1. Gebruik ketting-, basis- en gemiddelde dynamiekindicatoren om de verandering in cijfers te evalueren en uw conclusies op te schrijven.
2. Met behulp van de methode van analytische uitlijning (rechte lijn en parabool, bepaling van de coëfficiënten met behulp van OLS), identificeert u de belangrijkste trend in de ontwikkeling van het fenomeen (bevolking van de Republiek Komi). Evalueer de kwaliteit van de resulterende modellen met behulp van fouten en benaderingscoëfficiënten.
3. Bepaal lineaire en parabolische trendcoëfficiënten met behulp van de Grafiekwizard. Geef punt- en intervalpopulatievoorspellingen voor 2010. Schrijf uw conclusies op.

a) De lineaire trendvergelijking heeft de vorm y = bt + a
1. Zoek de parameters van de vergelijking met behulp van de kleinste kwadratenmethode. We gebruiken de methode om de tijd te tellen vanaf een voorwaardelijk begin.
Het systeem van kleinste kwadratenvergelijkingen voor een lineaire trend heeft de vorm:
een 0 n + een 1 ∑t = ∑y
een 0 ∑t + een 1 ∑t 2 = ∑y t

Voor onze gegevens zal het stelsel vergelijkingen de vorm aannemen:
10a 0 + 0a 1 = 10400
0a 0 + 330a 1 = -4038
Uit de eerste vergelijking drukken we een 0 uit en vervangen deze door de tweede vergelijking
We krijgen een 0 = -12,236, een 1 = 1040
Trendvergelijking:
y = -12,236 t + 1040

Laten we de kwaliteit van de trendvergelijking evalueren met behulp van de absolute benaderingsfout.

De benaderingsfout binnen 5%-7% geeft aan dat de trendvergelijking goed aansluit bij de oorspronkelijke gegevens.

b) parabolische uitlijning
De trendvergelijking is y = bij 2 + bt + c
1. Zoek de parameters van de vergelijking met behulp van de kleinste kwadratenmethode.
Systeem van vergelijkingen van de kleinste kwadraten:
een 0 n + een 1 ∑t + een 2 ∑t 2 = ∑y
een 0 ∑t + een 1 ∑t 2 + een 2 ∑t 3 = ∑yt
een 0 ∑t 2 + een 1 ∑t 3 + een 2 ∑t 4 = ∑yt 2

Benaderingsfout voor de parabolische trendvergelijking.

Omdat de fout minder dan 7% bedraagt, kan deze vergelijking als trend worden gebruikt.

Minimale benaderingsfout voor parabolische uitlijning. Bovendien is de determinatiecoëfficiënt R2 hoger dan bij lineair. Daarom is het noodzakelijk om een ​​parabolische vergelijking te gebruiken voor het voorspellen.

Intervalvoorspelling.
Laten we de wortel van de gemiddelde kwadratische fout van de voorspelde indicator bepalen.

m = 1 - het aantal beïnvloedende factoren in de trendvergelijking.
Uy = y n+L ± K
Waar

L - doorloopperiode; y n+L - puntvoorspelling volgens het model op het (n + L)-de tijdstip; n is het aantal waarnemingen in de tijdreeks; Sy is de standaardfout van de voorspelde indicator; Tabblad T - tabelwaarde van de studententest voor het significantieniveau α en voor het aantal vrijheidsgraden gelijk aan n-2.
Met behulp van de studententabel vinden we Ttable
T-tabel (n-m-1;α/2) = (8;0,025) = 2,306
Puntvoorspelling, t = 10: y(10) = 1,26*10 2 -12,24*10 + 998,5 = 1001,89 duizend mensen.

1001.89 - 71.13 = 930.76 ; 1001.89 + 71.13 = 1073.02
Intervalvoorspelling:
t = 9+1 = 10: (930,76;1073,02)

Grafieken

Regressieanalyse

Regressievergelijking Y van X functionele afhankelijkheid genoemd y=f(x), en de grafiek is een regressielijn.

Met Excel kunt u diagrammen en grafieken van redelijk acceptabele kwaliteit maken. Excel heeft een speciaal hulpmiddel: de Grafiekwizard, onder wiens begeleiding de gebruiker alle vier fasen van het proces van het construeren van een diagram of grafiek doorloopt.

In de regel begint het plotten met het selecteren van een bereik dat de gegevens bevat waarop het moet worden geplot. Deze start vereenvoudigt het verdere verloop van het plotten. Het bereik met de originele gegevens kan echter in de tweede fase van de dialoog worden verdeeld DIAGRAMMEESTER. In Excel2003 DIAGRAMMEESTER bevindt zich in het menu als een knop of er kan een diagram worden gemaakt door op het tabblad te klikken INVOEGEN en zoek het item in de lijst die wordt geopend DIAGRAM. In Excel 2007 vinden we het tabblad ook INVOEGEN(Afb. 31).

Rijst. 31. DIAGRAMMEESTER in Excel2007

De eenvoudigste manier is om een ​​reeks brongegevens te selecteren waarin deze gegevens zich in aangrenzende rijen (kolommen of rijen) bevinden. U moet op de cel linksboven van het bereik klikken en vervolgens de muisaanwijzer naar de cel rechtsonder slepen. het bereik. Wanneer u gegevens selecteert die zich in niet-aangrenzende rijen bevinden, sleept u de muisaanwijzer langs de geselecteerde rijen terwijl u de Ctrl-toets ingedrukt houdt. Als een van de gegevensreeksen een cel met een titel heeft, moeten de overige geselecteerde reeksen ook een overeenkomstige cel hebben, zelfs als deze leeg is.

Voor het uitvoeren van een regressieanalyse kunt u het beste een spreidingsdiagram gebruiken (Fig. 30). Bij het bouwen ervan beschouwt Excel de eerste rij van het geselecteerde bereik met brongegevens als een reeks argumentwaarden van de functies waarvan de grafieken moeten worden geplot (dezelfde set voor alle functies). De volgende rijen worden gezien als sets waarden van de functies zelf (elke rij bevat de waarden van een van de functies die overeenkomen met de opgegeven argumentwaarden in de eerste rij van het geselecteerde bereik).

In Excel 2007 worden de asnamen op het menutabblad geplaatst INDELING(Afb. 32).

Rijst. 32. De namen van grafiekassen instellen in Excel 2007

Om een ​​wiskundig model te verkrijgen, is het noodzakelijk om een ​​trendlijn in de grafiek te tekenen. In Excel 2003 en 2007 moet u met de rechtermuisknop op de grafiekpunten klikken. Vervolgens verschijnt in Excel 2003 een tabblad met een lijst met items waaruit we selecteren TRENDLIJN TOEVOEGEN(Afb. 33).

Rijst. 33. TRENDLIJN TOEVOEGEN

Nadat u op het artikel heeft geklikt TRENDLIJN TOEVOEGEN er verschijnt een venster TRENDLIJN(Afb. 34). Op het tabblad TYPE kunt u de volgende lijntypen selecteren: lineair, logaritmisch, exponentieel, macht, polynoom, lineair filteren.

Rijst. 34. Venster TRENDLIJN in Excel2003

Op het tabblad PARAMETERS(Fig. 35) vink het vakje aan naast de items TOON VERGELIJKING IN DIAGRAM, waarna een wiskundig model van deze relatie in de grafiek verschijnt. We plaatsen ook een selectievakje naast het item TOON IN HET DIAGRAM DE WAARDE VAN DE BETROUWBAARHEID VAN DE BENADERING (R^2). Hoe dichter de betrouwbaarheidswaarde bij benadering bij 1 ligt, hoe dichter de geselecteerde curve de punten in de grafiek benadert. Klik vervolgens op de knop OK. Een trendlijn, de bijbehorende vergelijking en de betrouwbaarheidswaarde van de benadering verschijnen in de grafiek.

Rijst. 35. Tab PARAMETERS

Nadat we in Excel 2007 met de rechtermuisknop op de grafiekpunten hebben geklikt, verschijnt er een lijst met menu-items waaruit SELECTEER TRENDLIJN TOEVOEGEN(Afb. 36).

Rijst. 36. TRENDLIJN TOEVOEGEN

Rijst. 37. Tab TRENDLIJNPARAMETERS

Vink de gewenste vakjes aan en druk op de knop DICHTBIJ.

Een trendlijn, de bijbehorende vergelijking en de betrouwbaarheidswaarde van de benadering verschijnen in de grafiek.

Wat moet ik doen als er geen timingmetingen zijn voor bepaalde volumes/groottes van producten? Of is het aantal metingen onvoldoende en kunnen er op korte termijn geen aanvullende waarnemingen worden gedaan? De beste manier om dit probleem op te lossen is door berekende afhankelijkheden (regressievergelijkingen) op te bouwen met behulp van trendlijnen in MS Excel.

Laten we eens een reële situatie bekijken: in een magazijn werden timingwaarnemingen uitgevoerd om het bedrag van de arbeidskosten voor het verzamelen van dozen van een bestelling vast te stellen. De resultaten van deze waarnemingen worden weergegeven in Tabel 1 hieronder.

Vervolgens ontstond de behoefte om de tijd te bepalen die nodig was om 0,6 en 0,9 m3 goederen/order te selecteren. Vanwege de onmogelijkheid om aanvullende timingstudies uit te voeren, werd de tijd besteed aan het selecteren van deze ordervolumes berekend met behulp van regressievergelijkingen in MS Excel. Om dit te bereiken is Tabel 1 omgezet naar Tabel 2.

Een spreidingsdiagram selecteren, Afb. 1

Volgende stap: de muiscursor werd op een van de punten op de kaart geplaatst en met de rechtermuisknop werd het contextmenu opgeroepen, waarin het item werd geselecteerd: “trendlijn toevoegen” (Afb. 2).

Een trendlijn toevoegen, afb. 2

In het venster dat verschijnt voor het instellen van het trendlijnformaat (Afb. 3) werden opeenvolgend geselecteerd: lijntype lineair/vermogen en de volgende items werden gecontroleerd: "toon de vergelijking in het diagram" en "plaats de betrouwbaarheidswaarde van de benadering (R ^ 2) in het diagram" (determinatiecoëfficiënt).

Trendlijnformaat, afb. 3

Als resultaat werden de grafieken weergegeven in figuur 1 verkregen. 4 en 5.

Lineair berekende afhankelijkheid, Fig. 4

Machtswet berekende afhankelijkheid, Fig. 5

Een visuele analyse van de grafieken geeft duidelijk de nabijheid van de verkregen afhankelijkheden aan. Bovendien is de betrouwbaarheidswaarde van de benadering (R^2), ook wel de determinatiecoëfficiënt genoemd, bij beide afhankelijkheden dezelfde waarde van 0,97. Het is bekend dat hoe dichter de determinatiecoëfficiënt bij 1 ligt, hoe meer de trendlijn overeenkomt met de werkelijkheid. Ook kan worden gesteld dat 97% van de verandering in de tijd besteed aan orderverwerking wordt verklaard door een verandering in de hoeveelheid goederen. Daarom is het in dit geval niet belangrijk: welke berekende afhankelijkheid moet worden gekozen als de belangrijkste voor de daaropvolgende berekening van tijdkosten.

Laten we de lineair berekende afhankelijkheid als belangrijkste nemen. Vervolgens worden de waarden van de bestede tijd, afhankelijk van de hoeveelheid goederen, bepaald door de formule: y = 54,511x + 0,1489. De resultaten van deze berekeningen voor de hoeveelheid goederen waarvoor eerder timingwaarnemingen werden uitgevoerd, worden weergegeven in Tabel 3 hieronder.

Laten we de gemiddelde afwijking bepalen van de bestede tijd, berekend met behulp van de regressievergelijking, van de bestede tijd berekend op basis van tijdwaarnemingen: (-0,05+0,10-0,05+0,01)/4=0,0019. De tijdsbesteding berekend met behulp van de regressievergelijking verschilt dus slechts met de tijdsbesteding berekend op basis van tijdregistratiegegevens 0,19%. Het verschil tussen de gegevens is verwaarloosbaar.

Met behulp van de formule: y = 54,511x + 0,1489 bepalen we de tijdskosten voor de hoeveelheid goederen waarvoor nog niet eerder timingwaarnemingen zijn uitgevoerd (Tabel 4).

Dus berekende afhankelijkheden construeren met behulp van trendlijnen in MS Excel - Dit is een uitstekende manier om de tijd vast te stellen die is besteed aan handelingen die om verschillende redenen niet in de tijdwaarnemingen zijn opgenomen.